alter procedure [FSAMS].[CPR_GET_HH_TOT_DASHBOARD_PORTAL]  --null,null,null,null,null,'Ram'
(
    @P_StateID          int,
    @P_DistrictCd       int,
    @P_LocalBodyCd      int,
    @P_FiscalYear       varchar(8),
    @P_ActivityId       int,
    @P_TechID           int,
    @P_UserId       varchar(200)
)
AS
    DECLARE @V_Cond varchar(max) = ''
    DECLARE @V_Sql varchar(max) = ''
BEGIN
    IF @P_StateID is not null
        set @V_Cond = @V_Cond + ' and A.StateID = ' +  cast(@P_StateID as varchar(5))
    IF @P_DistrictCd is not null
        set @V_Cond = @V_Cond + ' and A.DistrictCd = ' +  cast(@P_DistrictCd as varchar(5))
    IF @P_LocalBodyCd is not null
        set @V_Cond = @V_Cond + ' and A.LocalBodyCd = ' +  cast(@P_LocalBodyCd as varchar(5))
    IF @P_FiscalYear is not null
        set @V_Cond = @V_Cond + ' and SH.FiscalYear = ''' +  @P_FiscalYear + ''''
    IF @P_ActivityId is not null
        set @V_Cond = @V_Cond + ' and SH.Activity = ' +  cast(@P_ActivityId as varchar(5))

    IF @P_TechID is not null
        set @V_Cond = @V_Cond + ' and SH.TechId = ' + cast(@P_TechID as varchar(5));

    set @V_Sql = 'with tbl as(
        select (
            case
                when sh.RStatus = ''I'' then ''I2''
                when sh.RStatus = ''R'' then ''R2''
                when sh.RStatus = ''X'' then ''X2''
                when sh.RStatus = ''P'' then ''P2''
                else ''F2''
            end
        ) as RStatus,
        count(*) as TotalCount, SH.TechId
        from fsams.ctb_subsidy_card_header SH
        left outer join common.CTB_TRAN_AUTHENTICATIONS ta on ta.PaymentLOtNo = SH.LotNumber
        left outer join COMMON.CTB_ACTIVITY ca on ca.ActivityId = SH.Activity
        join common.CTB_MRCOMPANY MR on MR.MrCompanyId = SH.CompanyID
        left outer join fsams.CTB_SUBSIDY_CARD_ADDRESS SA on  SA.SubsidyId = SH.Id and SA.AdTypeId = 1
        left outer join COMMON.CTB_ADDRESS A on SA.AddressId = A.AddressID
        where SH.Hide is null and MR.UserName = ''' + @P_UserId + ''' ' + @V_Cond + '
        group by SH.RStatus, ta.LevelDone, SH.TechId
    )
    select RStatus,sum(TotalCount) as TotalCount from tbl
    group by RStatus'

    exec (@V_Sql)

END