set search_path = _00000003, public;

explain(analyze, buffers)

WITH
kpi_params as (
    SELECT "@KPI"
         , "SysName"
      FROM "KPI"
     WHERE "SysName"   IN ('Счета', 'Реализации', 'Оплаты')
       AND "ItemType"   = 2
       AND "$Черновик" IS NULL
       AND "Flags"[4]
     ORDER BY "SysName"
)

, empl_cat_filter AS (

SELECT UNNEST(ARRAY[21018598, 22087335, 2062745, 2298629, 23428910]) AS "Лицо",
UNNEST(ARRAY[21608304, 21608304,21608304, 21608304, NULL]::INTEGER[]) AS "Раздел",
UNNEST(ARRAY[TRUE, TRUE, NULL, NULL, NULL]) AS "Раздел@"

       )

    -- Иерархия по отделам с учетом областей видимости (см. startDeptFilter)
   , dept AS (
        WITH RECURSIVE sp AS (
            SELECT x."@Лицо" "Лицо"
                 , x."Раздел"
                 , x."Название"
                 , x."Раздел@"
                 , x."Тип"
              FROM "СтруктураПредприятия" x
             WHERE x."Флаги"[1] is false AND
                   (x."Тип" = 0 OR x."Тип" = 5)
                    AND "Раздел" IS NULL
             UNION ALL
            SELECT z."@Лицо" "Лицо"
                 , z."Раздел"
                 , z."Название"
                 , z."Раздел@"
                 , z."Тип"
              FROM "СтруктураПредприятия" z
             INNER JOIN sp y ON z."Раздел" = y."Лицо" AND z."Тип" = y."Тип"
             WHERE z."Флаги"[1] is false AND
                   z."$Черновик" IS NULL AND z."ДатаЗакрытия" IS NULL)
        SELECT "Лицо", "Раздел", "Название", "Раздел@" FROM sp),

    -- Сотрудники отделов
    deptUsers AS (
        SELECT
            (sv)."ЧастноеЛицо" "Лицо"
            ,
                (sv)."СтруктураПредприятия",
                (
                    SELECT coalesce(p."Фамилия" || ' ', '') || coalesce(substr(p."Имя", 1, 1) || '.', '') || coalesce(substr(p."Отчество", 1, 1)  || '.', '' )
                    FROM
                        "ЧастноеЛицо" p
                    WHERE
                        (sv)."ЧастноеЛицо" = p."@Лицо"
                ) "Название"

        FROM (
            SELECT "Лицо" "ЧастноеЛицо", "Раздел" "СтруктураПредприятия" from
                empl_cat_filter
        ) as sv




    )

-- Сотрудники и отделы
, clientDeptAndUsers AS ( -- не preson
    SELECT "Лицо"
         , "Раздел"
         , "Название"
         , "Раздел@"

      FROM dept
     UNION ALL
    SELECT "Лицо"
         , "СтруктураПредприятия" "Раздел"
         , "Название"
         , false "Раздел@"
      FROM deptUsers)


,  kpi_data AS ( --userStat
    SELECT s."Person"                                                           "Лицо"
         , s."Department"                                                       "Раздел"
         , SUM(CASE WHEN kp."SysName" = 'Реализации' THEN s."Value" ELSE 0 END) "ОтгрузкиСумма"
         , SUM(CASE WHEN kp."SysName" = 'Реализации' THEN s."Base"  ELSE 0 END) "ОтгрузкиКоличество"
         , SUM(CASE WHEN kp."SysName" = 'Счета'      THEN s."Value" ELSE 0 END) "СчетаСумма"
         , SUM(CASE WHEN kp."SysName" = 'Счета'      THEN s."Base"  ELSE 0 END) "СчетаКоличество"
         , SUM(CASE WHEN kp."SysName" = 'Оплаты'     THEN s."Value" ELSE 0 END) "ОплатыСумма"
         , SUM(CASE WHEN kp."SysName" = 'Оплаты'     THEN s."Base"  ELSE 0 END) "ОплатыКоличество"
      FROM
      "KPIAgg" s
     INNER JOIN kpi_params kp ON kp."@KPI" = s."KPI"
     WHERE s."Date" BETWEEN '2018-03-01'::DATE AND '2018-03-31'::DATE
       AND s."Person"     IS NOT NULL
     GROUP BY s."Person", s."Department"
     )

--select * from kpi_data


, person_kpi_data AS ( ---personStat
    SELECT DISTINCT
           p."Лицо"
         , p."Раздел"
         , p."Название"
         , p."Раздел@"

         , s."ОплатыКоличество"
         , s."ОплатыСумма"
         , s."ОтгрузкиКоличество"
         , s."ОтгрузкиСумма"
         , s."СчетаСумма"
         , s."СчетаКоличество"
      FROM clientDeptAndUsers p
      LEFT JOIN kpi_data s
             ON COALESCE(p."Лицо"  , 0) = COALESCE(s."Лицо"  , 0)
            AND COALESCE(p."Раздел", 0) = COALESCE(s."Раздел", 0))
--select * from person_kpi_data



, stat AS (
    WITH RECURSIVE data AS (
        SELECT x."Лицо"
             , x."Раздел"
             , x."Название"
             , x."Раздел@"
             , x."ОплатыКоличество"
             , x."ОплатыСумма"
             , x."ОтгрузкиКоличество"
             , x."ОтгрузкиСумма"
             , x."СчетаСумма"
             , x."СчетаКоличество"
          FROM person_kpi_data x
         where coalesce("ОплатыКоличество", 0) + coalesce("ОтгрузкиКоличество", 0) + coalesce("СчетаКоличество", 0) > 0
         UNION ALL
        SELECT z."Лицо"
             , z."Раздел"
             , z."Название"
             , z."Раздел@"
             , y."ОплатыКоличество"
             , y."ОплатыСумма"
             , y."ОтгрузкиКоличество"
             , y."ОтгрузкиСумма"
             , y."СчетаСумма"
             , y."СчетаКоличество"
          FROM data y
         INNER JOIN person_kpi_data z
                 ON y."Раздел" = z."Лицо")
    SELECT g."Лицо" "@Лицо"
         , g."Лицо"
         , g."Раздел"
         , g."Название"
         , g."Раздел@"
         , sum("ОплатыКоличество")  ::integer "ОплатыКоличество"
         , sum("ОплатыСумма")       ::float   "ОплатыСумма"
         , sum("ОтгрузкиКоличество")::integer "ОтгрузкиКоличество"
         , sum("ОтгрузкиСумма")     ::float   "ОтгрузкиСумма"
         , sum("СчетаКоличество")   ::integer "СчетаКоличество"
         , sum("СчетаСумма")        ::float   "СчетаСумма"
         , CASE
             WHEN g."Раздел" IS NOT NULL THEN TRUE
             ELSE FALSE
           END hasParent
         , CASE
             WHEN (g."Раздел" IS NULL AND NOT g."Раздел@") THEN 3
             WHEN g."Раздел@" THEN 1
             ELSE 2
           END sort_order
      FROM data g
     WHERE g."Лицо" IS NOT NULL
     GROUP BY g."Лицо", g."Раздел", g."Название", g."Раздел@"
        )
--select * from stat

, result AS (
    SELECT CASE
             WHEN p."@Лицо" IS NOT NULL THEN uuid_generate_v4()::TEXT
             WHEN sp."@Лицо" IS NOT NULL THEN a."@Лицо"::TEXT
             ELSE a."@Лицо"::TEXT
           END           "@Лицо"
         , a."@Лицо"     "Лицо"
         , CASE
             WHEN sp."@Лицо" IS NOT NULL THEN a."@Лицо"
             WHEN p."@Лицо" IS NOT NULL THEN a."Раздел"
           END "СтруктураПредприятия"
         , a."Раздел" "Раздел"
         , a."Раздел@"
         , CASE
             WHEN a."@Лицо" = '-1' THEN a."Название"
             WHEN p."@Лицо" IS NOT NULL THEN TRIM(TRIM(COALESCE(p."Фамилия", '') || ' ' || COALESCE(p."Имя", '')) || ' ' || COALESCE(p."Отчество", ''))
             WHEN sp."@Лицо" IS NOT NULL THEN sp."Название"
           END           "Название"
         , CASE
             WHEN a."@Лицо" = '-1' THEN NULL
             WHEN sp2."@Лицо" IS NULL THEN 'Без подразделения'
             WHEN COALESCE(sp2."Название", '') = '' THEN 'Подразделение без названия'
             ELSE sp2."Название"
           END "НазваниеСП"
         , CASE
             WHEN a."@Лицо" = '-1' THEN NULL
             WHEN sv."@СвязиПользователя" IS NOT NULL AND COALESCE(sv."СтруктураПредприятия", 0) = COALESCE(a."Раздел", 0)
             THEN true ELSE false
           END "ТекущаяСП"
         , a."ОплатыКоличество"
         , a."ОплатыСумма"
         , a."ОтгрузкиКоличество"
         , a."ОтгрузкиСумма"
         , a."СчетаКоличество"
         , a."СчетаСумма"
         , a.sort_order
      FROM stat a
      LEFT JOIN "ЧастноеЛицо"            p ON p."@Лицо"        = a."@Лицо"
      LEFT JOIN "СтруктураПредприятия"  sp ON sp."@Лицо"       = a."@Лицо"
      LEFT JOIN "СвязиПользователя"     sv ON sv."ЧастноеЛицо" = a."@Лицо"
      LEFT JOIN "СтруктураПредприятия" sp2 ON sp2."@Лицо"      = a."Раздел"
     WHERE a."Раздел" IS NULL
       AND a."@Лицо" IS NOT NULL
       AND
         coalesce(a."ОплатыКоличество", 0) + coalesce(a."ОтгрузкиКоличество", 0) + coalesce(a."СчетаКоличество", 0)  > 0
     UNION ALL
    SELECT '-2'                           "@Лицо"
         , -2                             "Лицо"
         , null::integer                  "СтруктураПредприятия"
         , NULL::INTEGER            "Раздел"
         , false                          "Раздел@"
         , null::text                     "Название"
         , null::text                     "НазваниеСП"
         , null::boolean                  "ТекущаяСП"
         , SUM(a."ОплатыКоличество")      "ОплатыКоличество"
         , SUM(a."ОплатыСумма")           "ОплатыСумма"
         , SUM(a."ОтгрузкиКоличество")    "ОтгрузкиКоличество"
         , SUM(a."ОтгрузкиСумма")         "ОтгрузкиСумма"
         , SUM(a."СчетаКоличество")       "СчетаКоличество"
         , SUM(a."СчетаСумма")            "СчетаСумма"
         , 4 sort_order
      FROM stat a
     WHERE a."Раздел" IS NULL
       AND a."@Лицо" IS NOT NULL)

select * from result ORDER BY sort_order, "Раздел@", "Название"