DROP AGGREGATE IF EXISTS array_cat_agg(anyarray);
CREATE AGGREGATE array_cat_agg(anyarray) (
  SFUNC=array_cat,
  STYPE=anyarray
);
--- Запрос дополняющий аналитику текстовой расшифровкой
WITH data AS (
SELECT

    z.*,
    -- Остаток на конец = остаток на начало + обороты
    CASE z.activity_type
      WHEN 2 THEN 0
      ELSE z.summa_begin_debet + z.summa_turn_debet - z.summa_turn_credit
    END AS summa_end_debet,

    -- так же по кредиту, остаток на конец = остаток на начало + обороты
    CASE z.activity_type
      WHEN 2 THEN z.summa_begin_credit - z.summa_turn_debet + z.summa_turn_credit
      ELSE 0
    END AS summa_end_credit,

    -- Ещё нужны итоговые суммы дебет - кредит
    CASE z.activity_type
        -- Для пассивных счетов кредит
        WHEN 2 THEN z.summa_turn_credit - z.summa_turn_debet
        -- Для активных счетов дебет
        ELSE z.summa_turn_debet - z.summa_turn_credit
    END AS summa_turn,
    CASE z.activity_type
        -- Для пассивных счетов кредит
        WHEN 2 THEN z.summa_begin_credit - z.summa_begin_debet
        -- Для активных счетов дебет
        ELSE z.summa_begin_debet - z.summa_begin_credit
    END AS summa_begin,

    CASE z.activity_type
        -- Для пассивных счетов кредит
        WHEN 2 THEN (z.summa_begin_credit - z.summa_turn_debet + z.summa_turn_credit - z.summa_begin_debet)
        -- Для активных счетов дебет
        ELSE (z.summa_begin_debet + z.summa_turn_debet - z.summa_turn_credit - z.summa_begin_credit)
    END AS summa_end
    -- Расшифровка аналитик

    ,ava1.dict_id AS analytic1_dict_id
    ,ava1.name AS analytic1_name
    ,ava1.code AS analytic1_code

    ,ava2.dict_id AS analytic2_dict_id
    ,ava2.name AS analytic2_name
    ,ava2.code AS analytic2_code


    -- Расшифровка обязательных аналитик
       ,account_alias.code AS account_name


    ,kbk_alias.name AS kbk_name
    ,kbk_alias.code AS kbk_code
    ,kbk_alias.fullname AS kbk_fullname
    ,kbk_alias.kbk_type AS kbk_type
    ,kbk_analitycgroup_alias.type_admission_disposal AS kbk_type_admission_disposal


       ,kbk_activity_type_alias.code AS kvd_name
     ,kbk_kosgu_alias.code AS kosgu_name

    -- Расшифровка аналитики по КВД


FROM
(

--- Основной запрос по оборотам и остаткам
    SELECT
        '' AS empty,
        bool_or(t.transition) as transition,
           t.account_id,

           t.kbk_id,

           t.kvd_id,
         t.kosgu_id,

          array_to_string(array_agg(
            distinct case when ('2018-04-30 23:59:58' >= t.date
              and '2018-04-01 00:00:00' <= t.date)
              then t.journal
              else null end), ', ') as journal,
          array_to_string(array_agg(distinct t.journal_id), ', ') as journals_ids,



         t.document_type_id,

        --- Аналитика по КВД


        --- Аналитики

            t.analytic1,

            t.analytic2,


        -- признак активности счета
        ach.activity_type,

        --- Обороты
        SUM( (CASE ('2018-04-30 23:59:58' >= t.date AND '2018-04-01 00:00:00' <= t.date) WHEN true THEN t.summa_turn_debet ELSE 0 END) ) AS summa_turn_debet,
        SUM( (CASE ('2018-04-30 23:59:58' >= t.date AND '2018-04-01 00:00:00' <= t.date) WHEN true THEN t.summa_turn_credit ELSE 0 END) ) AS summa_turn_credit,

        --- Остаток на начало
        SUM(
            CASE ('2018-04-01 00:00:00' <= t.date_next AND '2018-04-01 00:00:00' > t.date)
                WHEN true THEN
                    CASE ach.activity_type
                        -- Для пассивных счетов кредит всегда 0
                        WHEN 2 THEN 0
                        -- Для активных счетов сальдо будет только по дебету
                        ELSE t.summa_total_debet - t.summa_total_credit
                    END
                ELSE 0
            END
        ) AS summa_begin_debet,

        SUM(
            CASE ('2018-04-01 00:00:00' <= t.date_next AND '2018-04-01 00:00:00' > t.date)
               WHEN true THEN
                   CASE ach.activity_type
                       -- Для пассивных сальдо будет только по кредиту
                       WHEN 2 THEN t.summa_total_credit - t.summa_total_debet
                       -- Для активных счетов кредит всегда 0
                       ELSE 0
                   END
               ELSE 0
            END
        ) AS summa_begin_credit,
        -- Количество проводок для оборотов
        SUM( (CASE ('2018-04-30 23:59:58' >= t.date AND '2018-04-01 00:00:00' <= t.date) WHEN true THEN t.count_of_entries ELSE 0 END) ) AS count_of_entries,
        array_cat_agg(sources) as sources
        FROM
            (

                SELECT
                        analytic1 AS analytic1,

                        -1 AS analytic2,
                     a_e_cumreg.kbk_id,
                       a_e_cumreg.kvd_id,
                     a_e_cumreg.kosgu_id,
                     a_e_cumreg.journal, c_o_j.id as journal_id,



                     a_e_cumreg.document_type_id,

                    a_e_cumreg.transition,
                    a_e_cumreg.account_id,
                    a_e_cumreg.date,
                    a_e_cumreg.date_next,
                    -- Если итоги нужны только для одной стороны счета, то противоположная сторона обнуляется

                        a_e_cumreg.summa_turn_debet,
                        a_e_cumreg.summa_turn_credit,
                        a_e_cumreg.summa_total_debet,
                        a_e_cumreg.summa_total_credit,



                    a_e_cumreg.count_of_entries,
                    a_e_cumreg.sources as sources
                FROM account_entry_cumreg a_e_cumreg

                LEFT JOIN (
                  select config_oper_journal.id, config_oper_journal.number
                  from config_oper_journal) as c_o_j on a_e_cumreg.journal = c_o_j.number

                WHERE



                    --- Условия по аналитикам

                        a_e_cumreg.analytic1 IN (49347,49936,49973,50658,50659,50660,50656,50363,50654,50754,51008,50564,50663,50753,85098,87383,94526) AND



                     a_e_cumreg.kbk_id IN (54147,51268,23939,20406,31530,32331) AND
                     a_e_cumreg.kvd_id IN (3779) AND
                     a_e_cumreg.kosgu_id IN (87293,87972,88045,88171,87815,88207,87054) AND

                    --- Условия по аналитике КВД



                    a_e_cumreg.account_id in (302942,304042) AND
                    a_e_cumreg.ent_id  =  1453

                    AND (
                            ('2018-04-01 00:00:00' <= a_e_cumreg.date_next AND '2018-04-01 00:00:00' > a_e_cumreg.date) or -- остаток на начало
                            (
                                ('2018-04-30 23:59:58' >= a_e_cumreg.date AND '2018-04-01 00:00:00' <= a_e_cumreg.date)  -- обороты

                                  --- Собираем КОВ без учёта межотчётного периода
                                  AND a_e_cumreg.transition IS FALSE

                                 AND a_e_cumreg.document_type_id IN (56833,56945,57048,57101,57146,57199,57252,57304,51418,56989,72729,57416,69210,77051,100393,71623,58680,58158,69508,105163,98622,53611,63957,54016,54678,69766,98930,56623,70023,71884,60347,58056,57772,56229,57894,104491,59762,56512,59964,60018,52270,65074,65343,51776,52800,65612,52326,56414,56568,56121,107814,107285,58726,58217,59629,53924,59517,59459,59573,58896,57949,87532,58397,75348,105394,53760,79757,53869,102959,54420,62000,53704,51886,64805,51830,55672,57474,55776,61769,72987,56680,67897,55017,55890,57592,63508,57660,57702,68923,58820,56175,78940,58749,57363,105835,107028,80028,51995,97628,67952,68688,68007,68060,73773,92317,52051,80298,52216,65902,62352,85180,54713,68117,59345,84189,78636,82366,81416,77953,52633,101299,61472,85768,104199,85497,88944,59402,81890,58303,73248,58346,58616,58571,58451,58936,66610,66335,107593,106804,67404,106577,51594,56887,84905,78292,60244,60188,59802,59855,72196,68357,52106,80568,102309,58116,67116,80839,52161,100590,103918,103691,102683,53814,56736,57836,73547,58003,53979,53050,81112,56775,51542,70280,55724,68227,68172,67668,60592,56308,56456,106090,57528,55214,51460,59910,60133,51487,55328,70536,59144,63187,61203,53414,88653,56361,58778,54307,84578,68282,52688,53658,87778,60930,79440,66921,55577,79204,70794,72458,51721,99167,62625,74804,75108,62897,71051,71308,99807,100094,99500,104696,104912,94595,59694,101546,74308,75588,101886,74045,74527,51674,54616,53243,53298,51941,54111)
                            )
                        )


        ) AS t
      INNER JOIN account_chart AS ach ON t.account_id=ach.id

    GROUP BY
           t.account_id,

           t.kbk_id,

           t.kvd_id,
         t.kosgu_id,


         document_type_id,



        --- Аналитики

            t.analytic1,

            t.analytic2,


        ach.activity_type,

        -- Чтобы запрос не отваливался с пустой группировкой
        1

    ) AS z

        -- Расшифровка аналитик

            LEFT JOIN account_view_accelerator AS ava1 ON ava1.id = z.analytic1

            LEFT JOIN account_view_accelerator AS ava2 ON ava2.id = z.analytic2


        --- Расшифровка обязательных аналитик
           LEFT JOIN account_chart AS account_alias ON account_alias.id = z.account_id


            LEFT JOIN kbk AS kbk_alias ON kbk_alias.id = z.kbk_id
            LEFT JOIN kbk_analitycgroup AS kbk_analitycgroup_alias ON (kbk_analitycgroup_alias.id = kbk_alias.analytic_group_id)

           LEFT JOIN kbk_activity_type AS kbk_activity_type_alias ON kbk_activity_type_alias.id = z.kvd_id
         LEFT JOIN kbk_kosgu AS kbk_kosgu_alias ON kbk_kosgu_alias.id = z.kosgu_id

    )
    SELECT unnest(sources) FROM data
    WHERE
        -- Не выводим строки c нулевыми оборотами или остатками
        (
          summa_begin_debet <> 0 OR
          summa_begin_credit <> 0 OR

          summa_end_debet <> 0 OR
          summa_end_credit <> 0 OR

          -- Только честные обороты, т.е. когда проводка была -1 +1 включительно
          count_of_entries > 0
        )

-- select* from account_entry_cumreg limit 1

-- select * from enterprise where