Created
February 27, 2021 07:56
-
-
Save TurkerTunali/8f35c99119e123a367247109d5747a3b to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment