Tur rapor.sql

SELECT 	
	GEN_DATE.GEN_DATE, 
	LEFT(CUSTOMER.name, 29) AS CUSTOMER_NAME,
	FORMAT(IFNULL(TGL.bakiye, 0), 2, 'tr_TR') AS BAKIYE,
	/*PAYMENT_ENTRY.posting_date,
	SALES_ORDER.transaction_date,
	DELIVERY_NOTE.posting_date,*/
	#COALESCE(PAYMENT_ENTRY.posting_date, SALES_ORDER.transaction_date, DELIVERY_NOTE.posting_date) AS TRANSACTION_DATE,
	FORMAT(SUM(CASE PAYMENT_ENTRY.mode_of_payment WHEN CONCAT('Kadir Kızıl', ' Nakit') THEN PAYMENT_ENTRY.paid_amount ELSE 0 END), 2, 'tr_TR') AS CASH,
	FORMAT(SUM(CASE PAYMENT_ENTRY.mode_of_payment WHEN 'İskonto Kasa' THEN PAYMENT_ENTRY.paid_amount ELSE 0 END), 2, 'tr_TR') AS DISCOUNT,
	FORMAT(SUM(CASE PAYMENT_ENTRY.mode_of_payment WHEN CONCAT('Kadir Kızıl', ' Kredi Kartı') THEN PAYMENT_ENTRY.paid_amount ELSE 0 END), 2, 'tr_TR') AS CREDIT_CARD,
	FORMAT(SUM(CASE PAYMENT_ENTRY.mode_of_payment WHEN CONCAT('Kadir Kızıl', ' Çek') THEN PAYMENT_ENTRY.paid_amount ELSE 0 END), 2, 'tr_TR') AS CHEQUE,
	FORMAT(SUM(CASE PAYMENT_ENTRY.mode_of_payment WHEN 'Havale EFT' THEN PAYMENT_ENTRY.paid_amount ELSE 0 END), 2, 'tr_TR') AS BANK_TRANSFER,
	FORMAT(SALES_ORDER.net_total, 2, 'tr_TR') AS NEW_ORDER,
	FORMAT(DELIVERY_NOTE.net_total, 2, 'tr_TR') AS NEW_SALES,
	FORMAT(DELIVERY_NOTE_RETURNED.net_total, 2, 'tr_TR') AS RETURNED_DN_total
FROM `tabCustomer` AS CUSTOMER
	LEFT JOIN (
		SELECT GEN_DATE
		FROM
			(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_date from
			(select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
			(select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
			(select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
			(select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
			(select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
		WHERE gen_date BETWEEN '2021-02-01' AND '2021-02-19'
	) AS GEN_DATE ON 1=1

	LEFT JOIN `tabSales Team` AS SALES_TEAM ON SALES_TEAM.parent = CUSTOMER.name
		AND SALES_TEAM.parenttype = 'Customer'
		AND SALES_TEAM.parentfield = 'sales_team'
	LEFT JOIN `tabPayment Entry` AS PAYMENT_ENTRY ON PAYMENT_ENTRY.party = CUSTOMER.name
		AND PAYMENT_ENTRY.party_type = "Customer"
		AND PAYMENT_ENTRY.docstatus = 1
		AND PAYMENT_ENTRY.posting_date = GEN_DATE.GEN_DATE #BETWEEN '2021-02-19' AND '2021-02-26'
		AND PAYMENT_ENTRY.company = 'Yonca Tekstil'
	LEFT JOIN #GET NEW SALE ORDERS
	(
		SELECT SUM(SALES_ORDER.net_total) AS net_total, SALES_ORDER.customer, SALES_ORDER.transaction_date
		FROM `tabSales Order` AS SALES_ORDER
		WHERE
			SALES_ORDER.docstatus = 1
			#AND SALES_ORDER.transaction_date BETWEEN '2021-02-19' AND '2021-02-26'
			AND SALES_ORDER.company = 'Yonca Tekstil'
		GROUP BY SALES_ORDER.customer, SALES_ORDER.transaction_date
	) AS SALES_ORDER ON SALES_ORDER.customer = CUSTOMER.name
		AND SALES_ORDER.transaction_date = GEN_DATE.GEN_DATE
	LEFT JOIN #GET NEW SALES FROM SALES PERSON CAR
	(
		SELECT 
			DELIVERY_NOTE.customer, SUM(DELIVERY_NOTE_ITEM.amount) AS net_total, DELIVERY_NOTE.posting_date
		FROM
			`tabDelivery Note` AS DELIVERY_NOTE
			INNER JOIN `tabDelivery Note Item` AS DELIVERY_NOTE_ITEM ON DELIVERY_NOTE_ITEM.parent = DELIVERY_NOTE.name
			INNER JOIN `tabWarehouse` AS WAREHOUSE ON DELIVERY_NOTE_ITEM.warehouse = WAREHOUSE.name
				AND WAREHOUSE.company = DELIVERY_NOTE.company
		WHERE
			DELIVERY_NOTE.docstatus = 1
			#AND DELIVERY_NOTE.posting_date BETWEEN '2021-02-01' AND '2021-02-26'
			AND WAREHOUSE.warehouse_name = 'Kadir Kızıl'
			AND DELIVERY_NOTE.company = 'Yonca Tekstil'
			AND DELIVERY_NOTE.is_return = 0
		GROUP BY 
			DELIVERY_NOTE.customer, DELIVERY_NOTE.posting_date
	) AS DELIVERY_NOTE ON DELIVERY_NOTE.customer = CUSTOMER.name
		AND DELIVERY_NOTE.posting_date = GEN_DATE.GEN_DATE
	LEFT JOIN #GET RETURNED ITEMS FROM DELIVERY NOTE
	(
		SELECT 
			DELIVERY_NOTE.customer, SUM(DELIVERY_NOTE_ITEM.amount) AS net_total, DELIVERY_NOTE.posting_date
		FROM
			`tabDelivery Note` AS DELIVERY_NOTE
			INNER JOIN `tabDelivery Note Item` AS DELIVERY_NOTE_ITEM ON DELIVERY_NOTE_ITEM.parent = DELIVERY_NOTE.name
			INNER JOIN `tabWarehouse` AS WAREHOUSE ON DELIVERY_NOTE_ITEM.warehouse = WAREHOUSE.name
				AND WAREHOUSE.company = DELIVERY_NOTE.company
		WHERE
			DELIVERY_NOTE.docstatus = 1
			#AND DELIVERY_NOTE.posting_date BETWEEN '2021-02-01' AND '2021-02-26'
			#AND WAREHOUSE.warehouse_name = 'Kadir Kızıl'
			AND DELIVERY_NOTE.company = 'Yonca Tekstil'
			AND DELIVERY_NOTE.is_return = 1
		GROUP BY 
			DELIVERY_NOTE.customer, DELIVERY_NOTE.posting_date
	) AS DELIVERY_NOTE_RETURNED ON DELIVERY_NOTE_RETURNED.customer = CUSTOMER.name
		AND DELIVERY_NOTE_RETURNED.posting_date = GEN_DATE.GEN_DATE
	#LEFT JOIN `tabGL Entry` AS TGL ON TGL.party = CUSTOMER.name AND TGL.docstatus=1 AND TGL.company='Yonca Tekstil'
	LEFT JOIN
	(
		SELECT SUM(debit)-SUM(credit) AS bakiye, party
		FROM `tabGL Entry`
		WHERE party_type='Customer' AND docstatus=1 AND company='Yonca Tekstil'
		AND docstatus=1
		AND party_type='Customer' 
		AND company='Yonca Tekstil'
		GROUP BY party
	) AS TGL ON TGL.party = CUSTOMER.name AND CUSTOMER.territory = 'KADİR 1' AND CUSTOMER.disabled = 0
	
WHERE
	SALES_TEAM.sales_person = 'Kadir Kızıl'
	AND CUSTOMER.territory = 'KADİR 1'
	AND CUSTOMER.disabled = 0
	
GROUP BY
	SALES_TEAM.sales_person,
	CUSTOMER.name,
	CUSTOMER.territory,
	PAYMENT_ENTRY.posting_date,
	SALES_ORDER.transaction_date,
	DELIVERY_NOTE.posting_date
ORDER BY
	CUSTOMER.name,
	GEN_DATE.GEN_DATE
	#SALES_TEAM.sales_person