SET @DateFrom = '2023-12-01 00:00:00';
SET @DateTo = '2023-12-31 23:59:59';
-- SELECT @StoreNum := 56;
SELECT t.store_name AS "Магазин возврата",
t.date AS "Дата",
CONCAT(t.pos, '.', t.shift_open, '.', t.local_number) AS "Чек",
t.article AS "Артикул",
t.name AS "Наименование",
t.i_total_quantity AS "Количество",
t.i_total AS "Сумма",
t.l_store_name AS "Магазин продажи",
t.l_date AS "П. дата",
CONCAT(t.l_pos, '.', t.l_shift_open, '.', t.l_local_number) AS "П. чек",
t.l_total_quantity AS "П. количество",
t.l_total AS "П. сумма"
FROM (
SELECT s.store_id, s.name store_name,
h.date, h.pos, h.shift_open, h.local_number,
i.item AS article, i.name AS name, i.total_quantity AS i_total_quantity, (i.total+i.discount) AS i_total,
ls.store_id l_store_id, ls.name l_store_name,
lh.date l_date, lh.pos l_pos, lh.shift_open l_shift_open, lh.local_number l_local_number,
li.total_quantity AS l_total_quantity, (li.total+li.discount) AS l_total
FROM trm_out_receipt_header h
INNER JOIN trm_out_receipt_footer f
ON h.cash_id = f.cash_id AND h.id = f.id
INNER JOIN trm_in_pos p
ON p.cash_id = h.cash_id
INNER JOIN trm_in_store s
ON s.store_id = p.store_id
INNER JOIN trm_out_receipt_item i
ON h.cash_id = i.cash_id AND h.id = i.receipt_header
LEFT JOIN trm_out_receipt_link ln
ON ln.cash_id = h.cash_id AND ln.id = h.id
LEFT JOIN trm_out_receipt_header lh
ON lh.cash_id = ln.link_cash_id AND lh.id = ln.receipt_link_header
INNER JOIN trm_in_pos lp
ON lp.cash_id = lh.cash_id
INNER JOIN trm_in_store ls
ON ls.store_id = lp.store_id
INNER JOIN trm_out_receipt_item li
ON li.cash_id = lh.cash_id AND li.receipt_header = lh.id AND li.id = i.link_item
WHERE h.date BETWEEN @DateFrom AND @DateTo
AND h.type IN (1, 4)
AND f.result = 0
AND i.type = 0
AND li.type = 0
-- AND s.number = @StoreNum
) t
ORDER BY t.date, t.store_id, t.store_name
;