set search_path to "_00000003";

EXPLAIN (ANALYZE , BUFFERS , VERBOSE )

WITH
      cteMyOfficeDocs AS (
         -- Области видимости
         SELECT unnest(current_setting('sbis3.rights.myOfficeDocs')::integer[]) deptId
      ),

      -- Иерархия по отделам с учетом областей видимости (см. startDeptFilter)
      dept AS (
         WITH RECURSIVE sp AS (
            SELECT
               x."@Лицо" "Лицо",
               x."Раздел",
               x."Название",
               x."Раздел@",
               x."Тип"
            FROM
               "СтруктураПредприятия" x
            WHERE
               x."Тип" = 0
                AND "@Лицо" = 14466736
            UNION ALL
            SELECT
               z."@Лицо" "Лицо",
               z."Раздел",
               z."Название",
               z."Раздел@",
               z."Тип"
            FROM
               "СтруктураПредприятия" z
            INNER JOIN sp y ON z."Раздел" = y."Лицо" AND z."Тип" = y."Тип"
            WHERE
               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."@Лицо"
               ) "Название",
               (sv)."Пользователь"
            FROM (
               SELECT unnest(array(
                  SELECT
                     sv
                  FROM "СвязиПользователя" sv
                     INNER JOIN "ЧастноеЛицо" p ON sv."ЧастноеЛицо" = p."@Лицо"

                  WHERE
                     sv."ЧастноеЛицо" IS NOT NULL AND
                     sv."УправленческаяСтруктура" AND
                     sv."$Черновик" is null AND
                     p."Тип" = 0
                      AND d."Лицо" = sv."СтруктураПредприятия"
               )) sv
               FROM dept d
            ) as foo
            WHERE (SELECT COUNT(1) FROM dept) <= 4
            UNION ALL
            SELECT
               sv."ЧастноеЛицо" "Лицо",
               sv."СтруктураПредприятия",
               coalesce(p."Фамилия" || ' ', '') || coalesce(substr(p."Имя", 1, 1) || '.', '') || coalesce(substr(p."Отчество", 1, 1)  || '.', '' ) "Название",
               sv."Пользователь"
            FROM "СвязиПользователя" sv
               INNER JOIN "ЧастноеЛицо" p ON sv."ЧастноеЛицо" = p."@Лицо"

            WHERE
               (SELECT COUNT(1) FROM dept) > 4 AND
               sv."ЧастноеЛицо" IS NOT NULL AND
               sv."УправленческаяСтруктура" AND
               sv."$Черновик" is null AND
               p."Тип" = 0
                AND EXISTS (
                  SELECT NULL
                  FROM dept d
                  WHERE d."Лицо" = sv."СтруктураПредприятия"
               )

         )

      ,
      -- Темы отношений
      theme AS (
         WITH RECURSIVE regs AS (
            SELECT
               x."@Регламент" "Регламент",
               x."Раздел",
               x."Раздел@",
               x."Название",
               x."Флаги",
               x."Лимит",
               x."Порядок",
               x."Норма",
               x."Флаги"[3] "Удален"
            FROM "Регламент" x
            WHERE
               x."Идентификатор" = '8992e41a-a499-11e1-8c02-63ba74c41964'
            UNION ALL
            SELECT
               y."@Регламент" "Регламент",
               y."Раздел",
               y."Раздел@",
               y."Название",
               y."Флаги",
               y."Лимит",
               y."Порядок",
               y."Норма",
               y."Флаги"[3] "Удален"
            FROM regs z
               INNER JOIN "Регламент" y on z."Регламент" = y."Раздел"
         )
         SELECT * FROM regs
          WHERE  "Удален" IS NOT TRUE  AND  "Раздел@" IS NOT TRUE
      )

      , themeFiltered AS (
         SELECT t."Регламент"
             , t."БезКонтактов"
             , t."ВРаботе"
             , t."ЗавершеноОтрицательно"
             , t."ЗавершеноПоложительно"
         FROM "ТемаСостояние" t
         WHERE "Регламент" IS NOT NULL AND
               EXISTS (
                  SELECT NULL
                  FROM deptUsers u
                  WHERE t."Лицо" = u."Лицо"
               )

            AND EXISTS(SELECT NULL FROM theme r WHERE r."Регламент" = t."Регламент")
      )
      , themeStat1 AS (
         SELECT "Регламент" AS id, 'CRMTheme'::text AS tip,
            COALESCE(SUM("БезКонтактов"), 0) AS "БезКонтактов",
            COALESCE(SUM("ВРаботе"), 0) AS "ВРаботе",
            COALESCE(SUM("ЗавершеноПоложительно"), 0) AS "ЗавершеноПоложительно",
            COALESCE(SUM("ЗавершеноОтрицательно"), 0) AS "ЗавершеноОтрицательно"
         FROM themeFiltered t
         GROUP BY "Регламент"
      )
      , themeStat2 AS (
         SELECT t.id, t.tip, t."БезКонтактов", t."ВРаботе", t."ЗавершеноПоложительно", t."ЗавершеноОтрицательно",
            ("БезКонтактов"+"ВРаботе"+"ЗавершеноПоложительно"+"ЗавершеноОтрицательно") AS "Всего",
            r."Название",
            r."Порядок"
         FROM themeStat1 t
            INNER JOIN theme r ON r."Регламент" = t.id
      )
      , leads_prosr AS (
          SELECT count((a)) kol  --(a).*
             FROM (
                SELECT unnest(ARRAY(
                   SELECT unnest(ARRAY(
							SELECT ld
							FROM "Лид" ld
							WHERE
								ld."Черновик" IS NULL AND
								ld."ПоследнийКонтакт" IS NULL AND
								ld."Регламент" = t."Регламент" and
								ld."Подразделение" = d."Лицо" and

								ld."Фаза" IS NULL AND
								t."Лимит" IS NOT NULL AND
								ld."СозданиеДата" >= (date_trunc('day', now()) - '2 years'::INTERVAL)::DATE AND
								ld."СозданиеДата" < (date_trunc('day', now())::date - t."Лимит")::DATE
							ORDER BY "ДатаСоздания" DESC NULLS LAST
							LIMIT 100
                   )) a
                   FROM theme t
                )) a
                FROM dept d
             ) t
      )

		-- для лидов новых и просроченных
      SELECT -1 AS id, 'Lead'::text AS tip, 'Непрочитанные' AS "Название", (/*(SELECT COUNT(1) FROM leads)::text*/ ''  || ';' || (SELECT kol FROM leads_prosr)::text) AS "Всего", ''::text AS "ЗавершеноПоложительно"