USE [efs]
GO
/****** Object:  StoredProcedure [dbo].[USP_REPORT_RF_RECIPE_COUNT_R34]    Script Date: 09.04.2020 13:16:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[USP_REPORT_RF_RECIPE_COUNT_R34](
    @DATE_BEGIN DATE,
    @DATE_END DATE,
    @USE_HIERARCHY BIT
) AS

declare @ALL_LPU bit = IsNull((select top 1 0 from #LPU_PARAM), 1)

DECLARE @PARAMS UDT_REPORT_UTIL_PARAM_LIST; INSERT INTO @PARAMS EXEC [USP_REPORT_UTIL_PARAM_LIST]

DECLARE @HOME_REGION varchar(8) = (SELECT REGION_CODE FROM OKATO WHERE IS_HOME_REGION = 1)

DECLARE @RECIPE_SERIA_NOT_BLANK TABLE
(
    NAME varchar(20)
)

IF @HOME_REGION = '34'
BEGIN
    INSERT INTO @RECIPE_SERIA_NOT_BLANK (NAME)
    SELECT DISTINCT
        NAME
    FROM RECIPE_SERIA RS
    WHERE
        --rs.DELETED is NULL AND
        ISNUMERIC(SUBSTRING(RS.NAME, 4, 1)) = 1 AND
        ISNUMERIC(SUBSTRING(RS.NAME, 5, 1)) = 1
END
ELSE
BEGIN
    INSERT INTO @RECIPE_SERIA_NOT_BLANK (NAME)
    SELECT DISTINCT
        NAME
    FROM RECIPE_SERIA RS
    WHERE
        RS.DELETED is NULL AND
        RS.IS_BLANK = 0
END

;with _PARENTS as
(
    select
        c.GUID,
        PARENT_GUID = c.GUID,
        LVL = 1
    from CONTRACTOR (nolock) c

    union all

    select
        p.GUID,
        c.PARENT_GUID,
        LVL = p.LVL + 1
    from _PARENTS p
    join CONTRACTOR (nolock) c on c.GUID = p.PARENT_GUID
    where
        c.PARENT_GUID is not NULL
),
_TAB as
(
    select
        p.GUID,
        p.PARENT_GUID,
        p.LVL,
        NEXT_GUID = lead(GUID) over (order by p.GUID, p.LVL)
    from _PARENTS p
    where
        (
            @ALL_LPU = 1
            or
            p.GUID in (select VALUE from #LPU_PARAM)
            or
            p.PARENT_GUID in (select VALUE from #LPU_PARAM)
        )
),
_CONTRACTORS as
(
    select
        c.GUID,
        c.PARENT_GUID
    from _TAB c
    where
        NEXT_GUID is NULL
        or
        NEXT_GUID <> GUID
),
_DATA as
(
    select
        cr.RECIPE_SERIA,
        PARENT_MO_GUID = c.PARENT_GUID,
        MO_GUID = case when @USE_HIERARCHY = 0 then cr.LPU_GUID end,
        CNT = count(*)
    from CHACHE_RECIPES (nolock) cr
    join _CONTRACTORS c on c.GUID = cr.LPU_GUID
    where
        cr.DOC_STATUS not in ('SAVE', 'DEL', 'ANNULLED') and
        cast(cr.DATE_ISSUE as date) between @DATE_BEGIN and @DATE_END
    group by
        cr.RECIPE_SERIA,
        c.PARENT_GUID,
        case when @USE_HIERARCHY = 0 then cr.LPU_GUID end
)
select
    PARENT_MO_CODE = pmo.MCOD,
    PARENT_MO_NAME = pmo.NAME,
    MO_CODE = mo.MCOD,
    MO_NAME = mo.FULLNAME,
    CNT_ALL = sum(d.CNT),
    CNT_PC = sum(case when rs.NAME is not NULL then d.CNT else 0 end)
from _DATA d
join CONTRACTOR pmo (nolock) on pmo.GUID = d.PARENT_MO_GUID
left join CONTRACTOR (nolock) mo on mo.GUID = d.MO_GUID
left join @RECIPE_SERIA_NOT_BLANK rs on rs.NAME = d.RECIPE_SERIA
group by
    pmo.MCOD,
    pmo.NAME,
    mo.MCOD,
    mo.FULLNAME
order by
    pmo.NAME,
    mo.FULLNAME

SELECT
    [LPU] = [dbo].[FN_REPORT_UTIL_PARAM_DISPLAY]('LPU_PARAM', @PARAMS),
    [DATE] = [dbo].[FN_FORMAT_DATE_RANGE](@DATE_BEGIN,@DATE_END,NULL,0),
    [USE_HIERARCHY] = @USE_HIERARCHY

RETURN 0