
SELECT
dcb_card,
dcb_id,
dscc_scan,
dscc_value,
IFNUll(dcb_datetime, sls_datetime) datetime,
IFNUll(sls_id, 0) sls_id,
CASE WHEN dcb_document<0 AND dcb_positive = 1 THEN "Зачисление бонусов" WHEN
dcb_document<0 AND dcb_positive = 0 THEN "Списание бонусов" ELSE oprt_name END AS oprt_name,


(SELECT 
COALESCE(SUM(opr_quantity *oap_price1 ),0.0)
FROM  operations
JOIN operations_additional_prop  ON oap_operation = opr_id
WHERE (opr_type=2 OR opr_type=6) AND (opr_document=dcb_document OR opr_document = sls_id))
AS oap_price1,


(SELECT 
COALESCE(SUM(opr_quantity *oap_price2 ),0.0)
FROM operations
JOIN operations_additional_prop  ON oap_operation = opr_id
WHERE (opr_type=2 OR opr_type=6) AND (opr_document=dcb_document OR opr_document = sls_id))
AS oap_price2,


If(dcb_positive=1,dcb_value,0) bon_add,
If(dcb_positive=0,dcb_value,0) bon_min,

(SELECT 
COALESCE(SUM(CASE WHEN opr_type=2 THEN opr_quantity * oap_price1 ELSE 0 END),0.0) -
COALESCE(SUM(CASE WHEN opr_type=6 THEN opr_quantity * oap_price1 ELSE 0 END),0.0)
FROM operations
JOIN operations_additional_prop ON oap_operation = opr_id
JOIN doc_sales  ON opr_document = sls_id
WHERE sls_discount_card=  :pCardID AND sls_id <= sls.sls_id)  AS dscc_summ,

(SELECT COALESCE(SUM(CASE WHEN dcb_positive=1 THEN dcb_value ELSE 0 END),0.0)
 - COALESCE(  SUM(CASE WHEN dcb_positive=0 THEN dcb_value ELSE 0 END),0.0) 
FROM discount_cards_bonuses WHERE dcb_card=  :pCardID AND (dcb_document <= dcb1.dcb_document) AND (dcb_id <= dcb1.dcb_id) ) AS dscc_bonuses 



FROM
doc_sales sls
RIGHT JOIN discount_cards_bonuses dcb1 ON  sls_id = dcb1.dcb_document
LEFT JOIN operations_types ot ON ot.oprt_id = sls.sls_type
JOIN dir_discount_cards dc ON dc.dscc_id = dcb1.dcb_card
WHERE  dcb1.dcb_card=  :pCardID
GROUP BY sls.sls_id,dcb1.dcb_id

UNION


SELECT
dcb_card,
dcb_id,
dscc_scan,
dscc_value,
IFNUll(dcb_datetime, sls_datetime) datetime,
IFNUll(sls_id, 0) sls_id,
CASE WHEN dcb_document<0 AND dcb_positive = 1 THEN "Зачисление бонусов" WHEN
dcb_document<0 AND dcb_positive = 0 THEN "Списание бонусов" ELSE oprt_name END AS oprt_name,

(SELECT 
COALESCE(SUM(opr_quantity *oap_price1 ),0.0)
FROM  operations
JOIN operations_additional_prop  ON oap_operation = opr_id
WHERE (opr_type=2 OR opr_type=6) AND (opr_document=dcb_document OR opr_document = sls_id))
AS oap_price1,


(SELECT 
COALESCE(SUM(opr_quantity *oap_price2 ),0.0)
FROM operations
JOIN operations_additional_prop  ON oap_operation = opr_id
WHERE (opr_type=2 OR opr_type=6) AND (opr_document=dcb_document OR opr_document = sls_id))
AS oap_price2,



If(dcb_positive=1,dcb_value,0) bon_add,
If(dcb_positive=0,dcb_value,0) bon_min,


(SELECT 
COALESCE(SUM(CASE WHEN opr_type=2 THEN opr_quantity * oap_price1 ELSE 0 END),0.0) -
COALESCE(SUM(CASE WHEN opr_type=6 THEN opr_quantity * oap_price1 ELSE 0 END),0.0)
FROM operations
JOIN operations_additional_prop ON oap_operation = opr_id
JOIN doc_sales ON opr_document = sls_id
WHERE sls_discount_card=  :pCardID AND sls_id <= sls.sls_id)  AS dscc_summ,

(SELECT COALESCE(SUM(CASE WHEN dcb_positive=1 THEN dcb_value ELSE 0 END),0.0)
 - COALESCE(  SUM(CASE WHEN dcb_positive=0 THEN dcb_value ELSE 0 END),0.0) 
FROM discount_cards_bonuses WHERE dcb_card=  :pCardID AND (dcb_document <= dcb1.dcb_document)  AND (dcb_id <= dcb1.dcb_id)) AS dscc_bonuses 
 


FROM
doc_sales sls
LEFT JOIN discount_cards_bonuses dcb1 ON  sls_id = dcb1.dcb_document
LEFT JOIN operations_types ot ON ot.oprt_id = sls.sls_type
JOIN dir_discount_cards dc ON dc.dscc_id = sls.sls_discount_card
WHERE  sls.sls_discount_card=  :pCardID
GROUP BY sls.sls_id,dcb1.dcb_id
ORDER BY datetime