Datanory Production » Datanory - GOLDCREST

1) go to web_spMstRouteSummaryMasterDataRetriveList --> sp

2) select member_cd,* from mst_sales_team_member a
left join member b on a.member_id = b.member_id
where a.member_id in ('70fe0d960d204e86b862f88a8038fd10','7f8c3d45b01d4b948c1372e2c5644762')

select member_cd,* from mst_sales_team_member a
left join member b on a.member_id = b.member_id
where a.org_id = 'GOLDCREST'

-- Dont have the BRY user

--USE [DMS_EcoSystem]
--GO
--/****** Object: StoredProcedure [dbo].[web_spMstRouteSummaryMasterDataRetriveList] Script Date: 6/6/2024 5:43:38 PM **/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO
---- =============================================
---- Author: <Author,,Name>
---- Create date: <Create Date,,>
---- Description: <Description,,>
---- =============================================
--ALTER PROCEDURE [dbo].[web_spMstRouteSummaryMasterDataRetriveList]

--exec sp_executesql N'web_spMstRouteSummaryMasterDataRetriveList @org_id, 'GOLDCREST-admin'',N'@org_id nvarchar(9),'GOLDCREST-admin' nvarchar(15)'
--,@org_id=N'GOLDCREST'
--,'GOLDCREST-admin'=N'GOLDCREST-admin'
--declare
-- 'GOLDCREST' varchar(50), 'GOLDCREST-admin' varchar(50)

Create table #tempMemberBranch(
    branch_id varchar(50),
    member_id varchar(50)
    )

insert into #tempMemberBranch 
    select branch_id, member_id from fn_getMemberBranchByMemberId('GOLDCREST-admin')
    select distinct member_id from #tempMemberBranch 
    where member_id= '70fe0d960d204e86b862f88a8038fd10'

select
--DISTINCT x.member_id as Id,
-- x., isnull(t.total,0) as total_customer, isnull(r.total,0) as total_planned_route,
-- isnull(t.total,0) isnull(r.total,0) as total_not_in_route, isnull(b.branch_desc,'') as [BranchDesc], b.branch_code from(
- select --c.customer_id,
-- count(c.customer_id) as TotalCustomer , ISNULL + '' + ISNULL as Name , m.member_cd as MemberCd,
-- mh.sales_team_desc,m.member_id, c.org_id

from customer c
    inner join customer_to_user_relation cr on cr.customer_id = c.customer_id
    inner join member m on m.member_id = cr.user_id
    left join mst_sales_team_member ms on ms.member_id = m.member_id
    left join mst_sales_team_header mh on mh.hdr_id = ms.hdr_id
    where c.org_id = 'GOLDCREST'
    and  exists (select 1 from #tempMemberBranch mx where mx.member_id =m.member_id) and  member_cd in ('bry')

group by m.first_name, mh.sales_team_desc, m.member_id, m.last_name, c.org_id, m.member_cd--,c.customer_id
    )x
    left join 
    --territory
    (select user_id, count(1) as total from customer_to_user_relation r
    where org_id = 'GOLDCREST'
    and  exists (select 1 from #tempMemberBranch mx where mx.member_id = r.user_id)
    group by user_id) t on x.member_id = t.user_id
    left join 
    --planned route
    (select member_id,count(1) as total from (
        select member_id, customer_id from mst_route r
        where tenant_id = 'GOLDCREST'
        and  exists (select 1 from #tempMemberBranch mx where mx.member_id = r.member_id)
        group by member_id, customer_id
        ) x 
        group by member_id
    ) r on x.member_id= r.member_id

left join (
        SELECT member_id, 
        STRING_AGG(branch_code,',') as branch_code ,
        STRING_AGG(branch_desc,',') as branch_desc 
              from mst_branch b
    inner join member_branch mab on b.branch_id = mab.branch_id
    where b.org_id = 'GOLDCREST'
    and  exists (select 1 from #tempMemberBranch mx where mx.member_id = mab.member_id)
group by member_id
    ) b on x.member_id = b.member_id
    where exists ( select 1 from #tempMemberBranch mx, mst_customer_branch cb  where
    mx.branch_id = cb.branch_id --and cb.customer_id = x.customer_id
    )

--drop table #tempMemberBranch