SELECT
    sh.[gln]
    , sh.[franch_cheque_ref] AS [main_doc_ref]
    , sl.[products_ref]
    , sl.[size_ref]
    , SUM(sl.[product_line_qty]) AS [sales_qty]
    , SUM(sl.[retail_price] * sl.[product_line_qty]) AS [sales_retail_amount]
    , SUM(ISNULL(sl.[discount_nomenclature_amount], 0)) AS [sales_md_amount]
    , SUM(ISNULL(sl.[bonuses_spent_amount], 0)) AS [sales_loyalty_amount]
    , SUM(sl.[product_line_amount]) AS [sales_fact_amount]
    , SUM(ISNULL(sl.[unit_cost_franch] * sl.[product_line_qty], 0)) AS [sales_costs]
    , 0 AS [returns_qty]
    , 0 AS [returns_retail_amount]
    , 0 AS [returns_md_amount]
    , 0 AS [returns_loyalty_amount]
    , 0 AS [returns_fact_amount]
    , 0 AS [returns_costs]
INTO #franch_sales_data
FROM [TomBI].[dbo].[tbl_franch_sales_lines] AS sl
JOIN [TomBI].[dbo].[tbl_franch_sales_headers] AS sh ON sl.[franch_cheque_ref] = sh.[franch_cheque_ref]
LEFT JOIN [TomBI].[dbo].[tbl_products_gift_cards] AS pgc ON sl.[products_ref_fb] = pgc.[products_ref]
WHERE sh.[franch_cheque_posted] = 1
  AND sh.[franch_cheque_marked] = 0
  AND sh.[franch_cheque_status_ref] IN (0xBFFAD8772E16574442B5749FF1771AEE      -- Архивный
                                        , 0xA33EC418293B7D2D437BDA758499827E    -- Пробитый
                                    )
  AND pgc.[products_ref] IS NULL
  AND sl.[franch_cheque_date] >= '2024-07-01'

GROUP BY
    sh.[gln]
    , sh.[franch_cheque_ref]
    , sl.[products_ref]
    , sl.[size_ref]

UNION ALL

SELECT
    sl.[header_return_gln]
    , sh.[franch_cheque_ref] AS [main_doc_ref]
    , sl.[products_ref]
    , sl.[size_ref]
    , 0 AS [sales_qty]
    , 0 AS [sales_retail_amount]
    , 0 AS [sales_md_amount]
    , 0 AS [sales_loyalty_amount]
    , 0 AS [sales_fact_amount]
    , 0 AS [sales_costs]
    , SUM(sl.[return_product_line_qty] * ISNULL(sl.[return_retail_price], 0)) AS [returns_retail_amount]
    , SUM(ISNULL(sl.[discount_nomenclature_amount], 0)) AS [returns_md_amount]
    , SUM(ISNULL(sl.[bonuses_spent_amount], 0)) AS [returns_loyalty_amount]
    , SUM(sl.[return_product_line_amount]) AS [returns_fact_amount]
    , SUM(ISNULL(sl.[unit_cost_franch] * sl.[return_product_line_qty], 0)) AS [returns_costs]
FROM [TomBI].[dbo].[tbl_franch_sales_lines] AS sl
JOIN [TomBI].[dbo].[tbl_franch_sales_headers] AS sh ON sl.[franch_cheque_ref] = sh.[franch_cheque_ref]
LEFT JOIN [TomBI].[dbo].[tbl_products_gift_cards] AS pgc ON sl.[products_ref_fb] = pgc.[products_ref]
WHERE sl.[is_return] = 1
  AND sh.[franch_cheque_posted] = 1
  AND sh.[franch_cheque_marked] = 0
  AND sh.[franch_cheque_status_ref] IN (0xBFFAD8772E16574442B5749FF1771AEE      -- Архивный
                                        , 0xA33EC418293B7D2D437BDA758499827E    -- Пробитый
                                    )
  AND pgc.[products_ref] IS NULL
  AND sl.[franch_return_date] >= '2024-07-01' -- возвраты относим на дату возврата, не зависимо от того, когда была продажа.
  -- Уточните у заказчика: такую же логику надо сохранить или для целей CRM дашбордов надо возвраты относить на дату продажи, т.е. уменьшать продажу "задним" числом.
  -- В каком-то CRM отчете именно так и делали и те чеки, по которым в результате получалась нулевая покупка не учитывали, т.к. они не дали эффект
  -- В нашей стандартной отчетности относим по дате операции, как в этом скрипте.
GROUP BY
    sl.[header_return_gln]
    , sh.[franch_cheque_ref]
    , sl.[products_ref]
    , sl.[size_ref]
    ;



-- Этот запрос корректирует сумму МД:
SELECT  s.*
        , CASE
            WHEN s.[sales_retail_amount] - s.[sales_fact_amount] > 0
            THEN
                CASE
                    WHEN s.[sales_retail_amount] - s.[sales_fact_amount] >= s.[sales_md_amount]
                    THEN s.[sales_md_amount]
                    ELSE s.[sales_retail_amount] - s.[sales_fact_amount]
                END
            ELSE 0
          END AS [sales_markdown_amount]
        , CASE
            WHEN s.[returns_retail_amount] - s.[returns_fact_amount] > 0
            THEN
                CASE
                    WHEN s.[returns_retail_amount] - s.[returns_fact_amount] >= s.[returns_md_amount]
                    THEN s.[returns_md_amount]
                    ELSE s.[returns_retail_amount] - s.[returns_fact_amount]
                END
            ELSE 0
          END AS [returns_markdown_amount]
INTO #franch_md
FROM #franch_sales_data AS s
WHERE LEN([gln]) >= 13 -- так убираем ненужные записи. ИД реального магазина содержит 13 символов
;