SELECT
	daf.Tgl_Kirim,
	cust.nama AS pembeli,
	@SUBTOTAL_1 := SUM(
	IF(	det.NAMA LIKE 'LOCO Ekonomis Merah %' 
			AND daf.KoCus = cust.kode_cust,
			COALESCE ( det.jml_per_ball, 0 ) * COALESCE ( det.Harga_Akhir, 0 )* COALESCE ( det.Jml, 0 ),
			0 
		)) AS HARGA_1,
		
	 @TOTAL_1 := SUM(	IF( det.Nama LIKE 'LOCO Ekonomis Merah %' AND daf.KoCus = cust.kode_cust, det.Jml, 0 )) AS 'LOCO Ekonomis Merah',
	
	CASE
		WHEN SUM(	IF( det.Nama LIKE 'LOCO Ekonomis Merah %' AND daf.KoCus = cust.kode_cust, det.Jml, 0 )) >= IF(daf.KOCUS = '0010',350,150) THEN (SUM(
	IF(	det.NAMA LIKE 'LOCO Ekonomis Merah %' 
			AND daf.KoCus = cust.kode_cust,
			COALESCE ( det.jml_per_ball, 0 ) * COALESCE ( det.Harga_Akhir, 0 )* COALESCE ( det.Jml, 0 ),
			0 
		)) * 2.5 / 100)
		ELSE 0
	END CASHBACK_1,
-- -------------------------------------------

	@SUBTOTAL_2 := SUM(
	IF(	det.NAMA LIKE 'LOCO Ekonomis Putih %' 
			AND daf.KoCus = cust.kode_cust,
			COALESCE ( det.jml_per_ball, 0 ) * COALESCE ( det.Harga_Akhir, 0 )* COALESCE ( det.Jml, 0 ),
			0 
		)) AS HARGA_2,
		
	@TOTAL_2 := SUM(	IF( det.Nama LIKE 'LOCO Ekonomis Putih %' AND daf.KoCus = cust.kode_cust, det.Jml, 0 )) AS 'LOCO Ekonomis Putih',
	
	CASE
		WHEN SUM(	IF( det.Nama LIKE 'LOCO Ekonomis Putih %' AND daf.KoCus = cust.kode_cust, det.Jml, 0 )) >= IF(daf.KOCUS = '0010',350,150) THEN (SUM(
	IF(	det.NAMA LIKE 'LOCO Ekonomis Putih %' 
			AND daf.KoCus = cust.kode_cust,
			COALESCE ( det.jml_per_ball, 0 ) * COALESCE ( det.Harga_Akhir, 0 )* COALESCE ( det.Jml, 0 ),
			0 
		)) * 7.5 / 100)
		ELSE 0
	END CASHBACK_2,
-- 	----------------------------------------------------------------

	@SUBTOTAL_3 := SUM(
	IF(	det.NAMA LIKE 'LOCO Ekonomis Natural %' 
			AND daf.KoCus = cust.kode_cust,
			COALESCE ( det.jml_per_ball, 0 ) * COALESCE ( det.Harga_Akhir, 0 )* COALESCE ( det.Jml, 0 ),
			0 
		)) AS HARGA_3,
		
	@TOTAL_3 := SUM(IF( det.Nama LIKE 'LOCO Ekonomis Natural %' AND daf.KoCus = cust.kode_cust, det.Jml, 0 )) AS 'LOCO Ekonomis Natural',
	
	CASE
		WHEN SUM(	IF( det.Nama LIKE 'LOCO Ekonomis Natural %' AND daf.KoCus = cust.kode_cust, det.Jml, 0 )) >= IF(daf.KOCUS = '0010',250,50) THEN (SUM(
	IF(	det.NAMA LIKE 'LOCO Ekonomis Natural %' 
			AND daf.KoCus = cust.kode_cust,
			COALESCE ( det.jml_per_ball, 0 ) * COALESCE ( det.Harga_Akhir, 0 )* COALESCE ( det.Jml, 0 ),
			0 
		)) * 7.5 / 100)
		ELSE 0
	END CASHBACK_3,
-- 	---------------------------------------------------------------------------------
	@SUBTOTAL_4 := SUM(
	IF(	det.NAMA LIKE 'LOCO Anti Panas %' 
			AND daf.KoCus = cust.kode_cust,
			COALESCE ( det.jml_per_ball, 0 ) * COALESCE ( det.Harga_Akhir, 0 )* COALESCE ( det.Jml, 0 ),
			0 
		)) AS HARGA_4,
	@TOTAL_4 := SUM(	IF( det.Nama LIKE 'LOCO Anti Panas %' AND daf.KoCus = cust.kode_cust, det.Jml, 0 )) AS 'LOCO Anti Panas',
	
	CASE
		WHEN SUM(	IF( det.Nama LIKE 'LOCO Anti Panas %' AND daf.KoCus = cust.kode_cust, det.Jml, 0 )) >= IF(daf.KOCUS = '0010',70,50) THEN (SUM(
	IF(	det.NAMA LIKE 'LOCO Anti Panas %' 
			AND daf.KoCus = cust.kode_cust,
			COALESCE ( det.jml_per_ball, 0 ) * COALESCE ( det.Harga_Akhir, 0 )* COALESCE ( det.Jml, 0 ),
			0 
		)) * 7.5 / 100)
		ELSE 0
	END CASHBACK_4
	
	
FROM
	customer AS cust
	LEFT JOIN daf_pesanan_pengiriman daf ON daf.KoCus = cust.kode_cust 	AND daf.STATUS = 5
	LEFT JOIN det_pesanan_pengiriman det ON det.Notrans = daf.Notrans 
WHERE
	daf.Tgl_Kirim >= '2021-11-29' 
	AND daf.Tgl_Kirim <= '2021-12-23' 
GROUP BY
	daf.KoCus 
ORDER BY
	daf.KoCus