SELECT  imns_code,
    dictGetString('tables_fields_dictionaries_ids', 'dictionary_ids',
                tuple('imns_code')) AS imns_code_dictionary_id,
        unp,
        short_name,
        date_registration,
        state_code,
        dictGetString('tables_fields_dictionaries_ids', 'dictionary_ids',
                tuple('payer_state_code')) AS state_code_dictionary_id,
        date_begin_state,
        dictGetUInt8('addresses_info', 'is_mass_registration',
                tuple('address_code')) AS is_mass_registration,
        address,

        -- БП 81
        date_from,
        date_to,
        reasons,

        -- БП 135
        period_halfyear,

        -- БП 201
        indicator,

        payer_unp_is_quarter,

        -- БП 8
        oked_code_1,
        oked_code_dictionary_id AS oked_code_1_dictionary_id,
        oked_code_2,
        oked_code_dictionary_id AS oked_code_2_dictionary_id,
        oked_codes_declarations_codes,
        declarations_codes_dictionary_id AS oked_codes_declarations_codes_dictionary_id,
        oked_codes_period_codes,
        period_codes_dictionary_id AS oked_codes_period_codes_dictionary_id,

        -- БП 5
        sum_revenue_declarations_codes,
        declarations_codes_dictionary_id AS sum_revenue_declarations_codes_dictionary_id,
        sum_revenue_period_codes,
        period_codes_dictionary_id AS sum_revenue_period_codes_dictionary_id,
        sum_revenue,

        -- БП 6
        count_employees_declarations_codes,
        declarations_codes_dictionary_id AS count_employees_declarations_codes_dictionary_id,
        count_employees_period_codes,
        period_codes_dictionary_id AS count_employees_period_codes_dictionary_id,
        count_employees,

        -- БП 9
        provider_tax_burden,

        sum_total_cost_vat,
        sum_total_vat,
        sum_signed_total_vat,

        sum_signed_by_monthly_counterparty_total_vat,
        sum_signed_by_monthly_counterparty_after_declaration_period_end_total_vat,
        sum_signed_by_quarter_counterparty_after_declaration_period_end_total_vat,
        sum_accepted_by_monthly_counterparty_after_declaration_period_end_vat_amount,
        sum_accepted_by_monthly_counterparty_vat_amount,
        sum_not_signed_total_vat,
        sum_not_signed_by_monthly_counterparty_total_vat,

        --БП 134(БП 200(3 поля), БП 284 (1 поле), БП 134(1 поле), БП5, БП6, БП125)
        sale_turnover,
        sale_turnover_period_code,
        sale_turnover_period_number,
        accrued_nds,
        tax_deduction,
        nds_period_number,
        nds_period_code,
            period_codes_dictionary_id AS nds_period_code_dictionary_id,
        nds,

        --БП 116
        credited_from_1001_nds_amount,
        sum_total_vat - accrued_nds AS deviation_vat_amount
        deviation_vat_amount - sum_not_signed_total_vat - sum_accepted_by_monthly_counterparty_after_declaration_period_end_vat_amount + credited_from_1001_nds_amount AS tax_gap_vat_amount, --46A
        tax_gap_amount,

        risk_group_code,
        dictGetString('tables_fields_dictionaries_ids', 'dictionary_ids',
                     tuple('risk_group_code')) AS risk_group_dictionary_id,
        total_score,
        DATE

FROM (
    SELECT *,
         dictGetString('tables_fields_dictionaries_ids', 'dictionary_ids',
                tuple('oked_code')) AS oked_code_dictionary_id,
         dictGetString('tables_fields_dictionaries_ids', 'dictionary_ids',
                tuple('rn_declaration_type_code')) AS declarations_codes_dictionary_id,
         dictGetString('tables_fields_dictionaries_ids', 'dictionary_ids',
                tuple('rn_declaration_period_type_code')) AS period_codes_dictionary_id
    FROM(
        SELECT*
        FROM(
            SELECT *
            FROM(
                SELECT *
                FROM(
                    SELECT *
                    FROM(
                        SELECT *
                        FROM(
                            SELECT *
                            FROM(
                                SELECT *
                                FROM (
                                    SELECT *
                                    FROM(

                                        SELECT tax.*
FROM (SELECT declaration_month_number,
             payer_unp,
             argMax(payer_unp_is_quarter, date_calculated)               AS payer_unp_is_quarter,
             argMax(sum_total_cost_vat, date_calculated)                 AS sum_total_cost_vat,
             argMax(sum_total_vat, date_calculated)                      AS sum_total_vat,
             argMax(tuple(sum_signed_total_vat), date_calculated).1      AS sum_signed_total_vat,
             argMax(tuple(sum_signed_by_monthly_counterparty_total_vat),
                    date_calculated).1                                   AS sum_signed_by_monthly_counterparty_total_vat,
             argMax(tuple(sum_signed_by_monthly_counterparty_after_declaration_period_end_total_vat),
                    date_calculated).1                                   AS sum_signed_by_monthly_counterparty_after_declaration_period_end_total_vat,
             argMax(tuple(sum_accepted_by_monthly_counterparty_after_declaration_period_end_vat_amount),
                    date_calculated).1                                   AS sum_accepted_by_monthly_counterparty_after_declaration_period_end_vat_amount,
             argMax(tuple(sum_signed_by_quarter_counterparty_after_declaration_period_end_total_vat),
                    date_calculated).1                                   AS sum_signed_by_quarter_counterparty_after_declaration_period_end_total_vat,
             argMax(tuple(sum_accepted_by_monthly_counterparty_vat_amount),
                    date_calculated).1                                   AS sum_accepted_by_monthly_counterparty_vat_amount,
             argMax(tuple(sum_not_signed_total_vat), date_calculated).1  AS sum_not_signed_total_vat,
             argMax(tuple(sum_not_signed_by_monthly_counterparty_total_vat),
                    date_calculated).1                                   AS sum_not_signed_by_monthly_counterparty_total_vat
      FROM distributed_usf_exhibited_taxes
          prewhere YEAR = 2023 AND last_month = 9
      GROUP BY YEAR, last_month, declaration_month_number, payer_unp
      HAVING argMax(sign, date_calculated) = 1) AS tax
         LEFT OUTER JOIN (SELECT unp,
                                 MAX(month_number)                                                           AS max_month_number,
                                 IF(max_month_number % 3 = 0 AND max_month_number = 9, 0,
                                    max_month_number)                                                        AS declaration_month_number
                          FROM distributed_rn_actual_declarations
                              prewhere YEAR = 2023 AND month_number <= 9
                          WHERE type_code = 'NDS'
                            AND period_type_code = '2'
                            AND have_part_1 = 1
                          GROUP BY unp) AS decl ON tax.payer_unp = decl.unp AND tax.payer_unp_is_quarter = 1 AND
                                                   tax.declaration_month_number = decl.declaration_month_number;
                                    )   ALL INNER JOIN (

                                                    -- GetGRPPayers with Typology1 filters
SELECT  unp,
        argMax(tuple(imns_code), date_load).1         AS imns_code,
        argMax(tuple(short_name), date_load).1        AS short_name,
        argMax(tuple(address), date_load).1           AS address,
        argMax(tuple(date_registration), date_load).1 AS date_registration,
        argMax(tuple(state_code), date_load).1        AS state_code,
        argMax(tuple(date_begin_state), date_load).1  AS date_begin_state,
        argMax(tuple(address_code), date_load).1      AS address_code,
        argMax(tuple(sign), date_load).1              AS sign
FROM distributed_grp_actual_payers

    -- UnpCodes filter
--

GROUP BY unp
HAVING sign = 1 -- постоянный фильтр, без него будут грязные данные!!!
-- ImnsCodes filter
        AND imns_code IN ('000', '001', '100', '101', '102', '103', '104', '105', '106', '107', '108', '109', '111', '112', '200', '201', '202', '203', '204', '205', '206', '207', '208', '209', '211', '213', '214', '215',
'216', '217', '218', '232', '233', '241', '251', '271', '291', '292', '293', '294', '295', '300', '301', '302', '303', '304', '305', '306', '307', '308', '309', '311', '312', '313', '314', '315', '316', '317', '318', '319'
, '321', '322', '323', '341', '351', '362', '363', '364', '371', '381', '391', '392', '393', '394', '395', '397', '399', '400', '401', '402', '403', '404', '405', '406', '407', '408', '409', '411', '412', '413', '414', '41
5', '416', '417', '418', '419', '421', '422', '423', '424', '432', '433', '434', '435', '491', '492', '493', '494', '495', '496', '497', '498', '499', '500', '501', '502', '503', '504', '505', '506', '507', '508', '509', '
511', '512', '513', '514', '515', '516', '518', '519', '542', '543', '571', '591', '592', '593', '594', '595', '596', '600', '601', '602', '603', '604', '605', '606', '607', '608', '609', '611', '612', '613', '614', '615',
 '616', '617', '618', '619', '621', '622', '623', '624', '625', '661', '671', '691', '692', '693', '694', '695', '696', '697', '700', '701', '702', '703', '704', '705', '707', '708', '709', '711', '712', '713', '715', '716', '717', '718', '719', '721', '722', '723', '724', '741', '751', '761', '772', '773', '774', '791', '792', '793', '794', '795', '796', '797', '800', '801', '000')
--

    -- RegistrationDate filters
--

--
                                            ) USING unp
                                )   ALL LEFT JOIN  (

                                                                -- GetRiskGroups
SELECT  unp,
                argMax(tuple(risk_group_code), date_calculated).1 AS risk_group_code,
                argMax(tuple(total_score), date_calculated).1 AS total_score,
                argMax(tuple(DATE), date_calculated).1 AS DATE
FROM (
        SELECT  unp,
                        risk_group_code,
                        total_score,
                        dictGetInt64('finished_risk_calculation_history', 'date', tuple(calculation_code)) AS DATE,
                        date_calculated
        FROM distributed_risk_groups

                        prewhere YEAR * 12 + MONTH IN (24277, 24278, 24279, 24280, 24281, 24282, 24283, 24284, 24285)


                        -- UnpCodes filter
--


        WHERE dictGetUInt8('finished_risk_calculation_history', 'is_finished', tuple(calculation_code)) --обязательный фильтр


                AND DATE BETWEEN 1672520400 AND 1696107599


)
GROUP BY unp

--
                                                        ) USING unp
                            ) ALL LEFT JOIN (

                                                        WITH 100 AS coins_factor
SELECT unp,
        YEAR,
        last_month,
        --БП125
        argMax(tuple(nds), date_calculated).1                                                    AS nds,
        argMax(tuple(nds_period_number), date_calculated).1                                      AS nds_period_number,
        argMax(nds_period_code, date_calculated)                                                 AS nds_period_code,
        --БП5
        argMax(tuple(sum_revenue), date_calculated).1                                            AS sum_revenue,
        argMax(sum_revenue_declarations_codes, date_calculated)                                  AS sum_revenue_declarations_codes,
        argMax(sum_revenue_period_codes, date_calculated)                                        AS sum_revenue_period_codes,
        --БП6
        argMax(tuple(count_employees), date_calculated).1                                        AS count_employees,
        argMax(count_employees_declarations_codes, date_calculated)                              AS count_employees_declarations_codes,
        argMax(count_employees_period_codes, date_calculated)                                    AS count_employees_period_codes,

        --БП134
        argMax(tuple(tax_deduction), date_calculated).1                                          AS tax_deduction,
        --БП284
        argMax(tuple(accrued_nds), date_calculated).1                                            AS accrued_nds,
        --БП200
        argMax(tuple(sale_turnover), date_calculated).1                                          AS sale_turnover,
        argMax(sale_turnover_period_code, date_calculated)                                       AS sale_turnover_period_code,
        argMax(tuple(sale_turnover_period_number), date_calculated).1                            AS sale_turnover_period_number,
FROM distributed_rn_actual_declarations_to_br_info
WHERE YEAR = 2023
  AND last_month = 9
GROUP BY YEAR, last_month, unp;
                                                ) USING unp
                        ) ALL LEFT JOIN (

                                                -- GetLastVOPCriteria_5
SELECT  unp,
        argMax(tuple(group_id), KEY).1                 AS group_id,
        argMax(tuple(hrc), KEY).1                      AS hrc,
        argMax(tuple(criterion_weight), KEY).1         AS criterion_weight,
        argMax(tuple(criterion_list), KEY).1           AS criterion_list,
        argMax(tuple(YEAR), KEY).1                     AS YEAR,
        argMax(tuple(half_year), KEY).1                AS half_year,
        argMax(criterion_weight, KEY) > argMax(hrc, KEY) AS indicator
FROM distributed_kr_vop_criteria

    -- UnpCodes filter
--

AND YEAR = 2023
AND halfyear = 1
    OR halfyear = 2

GROUP BY unp
--
                                        ) USING unp
                    ) ALL LEFT JOIN (
                                        -- GetOKEDCodesInfo Head 1
SELECT  YEAR,
        last_month,
        unp,
        oked_code_1,
        oked_code_2,
        oked_codes_declarations_codes,
        oked_codes_period_codes
FROM (
    SELECT  YEAR,
            unp,
            last_month,
            groupArray(oked_code)                   AS oked_codes,
            oked_codes[1]                           AS oked_code_1,
            oked_codes[2]                           AS oked_code_2,
            groupArrayArray(oked_code_declarations_code) AS oked_codes_declarations_codes,
            groupArrayArray(oked_code_period_code)       AS oked_codes_period_codes
    FROM (
        SELECT  YEAR,
                unp,
                last_month,
                info.1            AS oked_code,
                info.2            AS oked_code_declarations_code,
                info.3            AS oked_code_period_code
        FROM (
            SELECT  YEAR,
                    last_month,
                    unp,
                    arrayJoin(arraySlice(groupArray(info), 1, 2)) AS info
            FROM (
                SELECT  YEAR,
                        last_month,
                        unp,
                        (oked_code,
                         groupArray(type_code)          AS oked_code_type_codes,
                         groupArray(period_type_code)   AS oked_code_period_type_codes) AS info
                FROM (
                    SELECT  YEAR,
                            last_month,
                            unp,
                            type_code,
                            argMax(tuple(oked_code), date_calculated).1        AS oked_code,
                            argMax(tuple(period_type_code), date_calculated).1 AS period_type_code
                    FROM distributed_rn_actual_declarations_by_periods_info
--

                                ) ON unp = counterparty_unp
                ) ALL LEFT JOIN (

                                --GetKrFalseCompanies
SELECT unp,
       argMax(tuple(date_from), date_load).1 AS date_from,
       argMax(tuple(date_to), date_load).1   AS date_to,
       argMax(tuple(reasons), date_load).1   AS reasons
FROM distributed_kr_false_companies
GROUP BY unp
--
                        ) ON fc.unp = counterparty_unp
            ) ALL LEFT JOIN (

                        -- GetTaxBurden
SELECT unp, argMax(tuple(tax_burden), period).1 AS tax_burden
FROM (
        SELECT  period,
                unp,
                argMax(tuple(tax_burden), date_calculated).1 AS tax_burden
        FROM distributed_tax_burdens
        WHERE  YEAR = 2023
        AND  period <= round((9-1)/3, 0)

        -- UnpCodes filter
--

        GROUP BY period, unp
        HAVING isNotNull(tax_burden) = 1
)
GROUP BY unp

--
                    ) USING unp
        ) ALL LEFT JOIN (

                    SELECT payer_unp,
       argMax(full_exhibited_total_val, date_load)              AS full_exhibited_total_val,
       argMax(full_signed_total_vat, date_load)                 AS full_signed_total_vat,
       counterparty_unp,
       argMax(exhibited_total_val, date_load)                   AS exhibited_total_val,
       argMax(signed_total_vat, date_load)                      AS signed_total_vat,
       argMax(signed_total_vat_rate, date_load)                 AS signed_total_vat_rate,
       argMax(tuple(kls_balance), date_load).1                  AS kls_balance,
       argMax(tuple(kls_balance_per_counterparty), date_load).1 AS kls_balance_per_counterparty,
       argMax(tax_gap_amount, date_load)                        AS tax_gap_amount
FROM distributed_tax_gaps
WHERE YEAR = 2023
  AND last_month = 9
GROUP BY payer_unp, counterparty_unp
HAVING argMax(sign, date_load) = 1;
                ) USING unp
    ) ALL LEFT JOIN (

                --GetKlsNds55
SELECT unp,
       budget_code,
       argMax(tuple(credited_from_1001_nds_amount), date_calculated).1     AS credited_from_1001_nds_amount,
FROM distributed_rn_kls_nds
WHERE budget_code = '1001'
    AND YEAR = 2023
    AND operation_month = 9
GROUP BY unp, budget_code
--
            ) USING unp
)