USE [NREMIS]
GO
/****** Object:  StoredProcedure [PROFILE].[RPR_GET_VDCWISE_COUNT]    Script Date: 2019-11-18 2:26:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [PROFILE].[RPR_GET_VDCWISE_COUNT]--'121','Domestic Biogas Plant'
(
@P_USERID       VARCHAR(30),
@P_TECHNAME     VARCHAR(100)
)
AS
Declare @V_TechId int = (select TechId from common.CLK_TECHNOLOGY where TechNameEng = @P_TECHNAME)
DECLARE @V_StateId varchar(30) = (select StateID from common.SEC_PROFILE_USERS_WORKING where UserId = @P_USERID and ToDate is null)
BEGIN
     SELECT  CatId,FiscalYear,District,LocalBodyCD,Municipality, sum(TotalCount) as TotalCount from
    (
        (select 'PC' as DSource,2 as CatId,H.FiscalYear as FiscalYear,D.NameEng as District, LocalBodyCD as LocalBodyCD, VDC.NameEng as Municipality, count(CommProfileId) as TotalCount
        from COMMON.CTB_COMMUNITY_PROFILE_HEAD H
        join COMMON.CLK_DISTRICT_VDC VDC on H.LocalBodyCD = VDC.VdcCd
        left outer join COMMON.CLK_DISTRICTS D on D.DistrictCd = VDC.DistrictCd
        where StateCd = @V_StateId and H.TechId = @V_TechId
        group by LocalBodyCD,VDC.NameEng,H.FiscalYear, D.NameEng)
        UNION
        (select 'PH' as DSource,1 as CatId,H.FiscalYear as FiscalYear,D.NameEng as District, LocalBodyCD as LocalBodyCD, VDC.NameEng as Municipality, count(HHProfileId) as TotalCount
        from COMMON.CTB_HOUSEHOLD_PROFILE_HEAD H
        left outer join COMMON.CLK_DISTRICT_VDC VDC on H.LocalBodyCD = VDC.VdcCd
        join COMMON.CLK_DISTRICTS D on D.DistrictCd = VDC.DistrictCd
        where StateCd = @V_StateId and H.TechId = @V_TechId
        group by LocalBodyCD, VDC.NameEng,H.FiscalYear,D.NameEng)
        UNION
        (select 'AC' as DSource,2 as CatId,P.FISCALYEAR as FiscalYear,D.NameEng as District, VDC.VdcCd as LocalBodyCD, VDC.NameEng as Municipality, Count(P.ProId) as TotalCount
        from COMMON.CLK_STATE S
        left outer join COMMON.CLK_DISTRICTS D on S.StateId = D.StateId
        left outer join COMMON.CLK_DISTRICT_VDC VDC on VDC.DistrictCd = D.DistrictCd
        left outer join COMMON.CTB_PROJECT P on VDC.VdcCd = P.LocalBodyCd
        where S.StateId = @V_StateId and P.TechId = @V_TechId
        GROUP BY VDC.VdcCd, VDC.NameEng,P.FISCALYEAR, D.NameEng)
        UNION
        (select 'AH' as DSource,1 as CatId,SS.FiscalYear as FiscalYear, D.NameEng as District,VDC.VdcCd as LocalBodyCD, VDC.NameEng as Municipality, count(SS.Id) as TotalCount
        from COMMON.CLK_STATE S
        left outer join COMMON.CLK_DISTRICTS D on S.StateId = D.StateId
        join COMMON.CLK_DISTRICT_VDC VDC on VDC.DistrictCd = D.DistrictCd
        join (select Id, A.StateID, A.VdcCd, SH.TECHID,SH.FiscalYear from FSAMS.CTB_SUBSIDY_CARD_HEADER SH
        join FSAMS.CTB_SUBSIDY_CARD_ADDRESS SA on SA.SubsidyId = SH.Id and SA.AdtypeId = 1
        join COMMON.CTB_ADDRESS A on A.AddressID = SA.AddressId) SS on SS.VdcCd = VDC.VdcCd
        where S.StateId = @V_StateId and SS.TECHID = @V_TechId
        GROUP BY VDC.VdcCd, VDC.NameEng,SS.FiscalYear,D.NameEng
        )
    ) tbl
    group by CatId,FiscalYear,District,LocalBodyCD,Municipality
END