Running balance report for customer and suppliers combined by tax id

ConsolidatedRunningBalance.sql

SELECT
#LOGEDOSOFT-2019
#Amac: Tedarici ve Satiscilari vergi numarasina gore gruplayarak cari ekstre gosterir
#Versiyonlar:
#21.12.2019:Alim fatura tarihi ellge girilen tedarikci fatura tarihinden getirildi. Tahsilat yapildiginda kapatilan fatura rakamlari getiriliyordu, direk tahsilat tutarinin getirilmesi saglandi
#27.12.2019:Fis turu alani duzgun getirildi. Gereksiz kolonlar kapatildi.
#27.12.2019:Satis fatura aciklamasi ozel alan olan aciklama alanindan getirildi(remarks alani kaldirildi)
#18.02.2020:Alim fatura aciklamasi getirildi (remarks kaldirildi)
#18.02.2020:Ceklerin vade tarihi aciklama alanina getirildi.
#25.02.2020:DÜZELTME: Belge Tarihi olarak posting_date, Açıklama da ki tarihe reference_date gelmeli.
#02.03.2020:Cari hesap adi yerine kartlarin vergi numaralarina gore eslestirme yapilmasi saglandi.

	#TMP_GLENTRY.CARI_ADI AS "Cari Adı",
	TMP_GLENTRY.BELGE_TARIHI AS "Belge Tarihi",
	TMP_GLENTRY.BELGE_NO AS "Belge No",
	TMP_GLENTRY.TIP AS "Fiş Tipi:Data:100", 
	REPLACE(TMP_GLENTRY.remarks, 'No Remarks', '') AS "Notlar:Data:300",
	ROUND(SUM(TMP_GLENTRY.debit), 2) AS "Borç:Currency:100",
	ROUND(SUM(TMP_GLENTRY.credit), 2) AS "Alacak:Currency:100", 
	ROUND(SUM(TMP_GLENTRY.DEBIT - TMP_GLENTRY.CREDIT) OVER (
		PARTITION BY
			TMP_GLENTRY.VERGI_NO
		ORDER BY
            TMP_GLENTRY.BELGE_TARIHI, TMP_GLENTRY.posting_date, TMP_GLENTRY.account,
            TMP_GLENTRY.CARI_ADI, 
            TMP_GLENTRY.TIP, 
            #TMP_GLENTRY.name, 
            TMP_GLENTRY.voucher_no,
            TMP_GLENTRY.fiscal_year, 
            TMP_GLENTRY.voucher_type, 
            TMP_GLENTRY.party,
            TMP_GLENTRY.party_type
    ), 2) AS "Bakiye:Currency:150"
    
FROM
(
    #MUSTERILERIN SATIS FATURA BILGILERINI GETIRELIM
    SELECT
        CUSTOMER.name AS CARI_KODU, CUSTOMER.customer_name AS CARI_ADI, CUSTOMER.tax_id AS VERGI_NO, "Satış Faturası" AS TIP,
        SALES_INVOICE.posting_date AS BELGE_TARIHI, 'SALES_INVOICE.belge_no' AS BELGE_NO, SALES_INVOICE.posting_date AS VADE_TARIHI,
        #GL_ENTRY.*
		  GL_ENTRY.debit, GL_ENTRY.credit credit, GL_ENTRY.posting_date, GL_ENTRY.account, 
		  #GL_ENTRY.name,  
		  GL_ENTRY.voucher_no, GL_ENTRY.fiscal_year, GL_ENTRY.voucher_type, GL_ENTRY.party, GL_ENTRY.party_type, 'SALES_INVOICE.aciklama' AS remarks #GL_ENTRY.remarks
    FROM `tabGL Entry` AS GL_ENTRY
    INNER JOIN `tabCustomer` as CUSTOMER ON GL_ENTRY.PARTY = CUSTOMER.NAME
    INNER JOIN `tabSales Invoice` SALES_INVOICE ON GL_ENTRY.voucher_no = SALES_INVOICE.name
    WHERE
        GL_ENTRY.voucher_type = 'Sales Invoice'
        AND SALES_INVOICE.docstatus = 1
        AND GL_ENTRY.docstatus = 1
 
    UNION ALL
 
    #TEDARIKCILERIN ALIM FATURA BILGILERINI GETIRELIM
    SELECT
        SUPPLIER.name AS CARI_KODU, SUPPLIER.supplier_name AS CARI_ADI, SUPPLIER.tax_id AS VERGI_NO,  "Alım Faturası" AS TIP, 
        PURCHASE_INVOICE.bill_date AS BELGE_TARIHI, PURCHASE_INVOICE.bill_no AS BELGE_NO, PURCHASE_INVOICE.bill_date AS VADE_TARIHI,
        #GL_ENTRY.*
		  GL_ENTRY.debit, GL_ENTRY.credit credit, GL_ENTRY.posting_date, GL_ENTRY.account, 
		  #GL_ENTRY.name,  
		  GL_ENTRY.voucher_no, GL_ENTRY.fiscal_year, GL_ENTRY.voucher_type, GL_ENTRY.party, GL_ENTRY.party_type, 'PURCHASE_INVOICE.aciklama' as remarks #GL_ENTRY.remarks
    FROM `tabGL Entry` AS GL_ENTRY
    INNER JOIN `tabSupplier` as SUPPLIER ON GL_ENTRY.PARTY = SUPPLIER.NAME
    INNER JOIN `tabPurchase Invoice` PURCHASE_INVOICE ON GL_ENTRY.voucher_no = PURCHASE_INVOICE.name
    WHERE
        GL_ENTRY.voucher_type = 'Purchase Invoice'
        AND PURCHASE_INVOICE.docstatus = 1
        AND GL_ENTRY.docstatus = 1
         
    UNION ALL
     
    #ODEME HAREKETLERINI GETIRELIM
    SELECT
        COALESCE(CUSTOMER.name, SUPPLIER.name) AS CARI_KODU, COALESCE(CUSTOMER.customer_name, SUPPLIER.supplier_name) AS CARI_ADI, COALESCE(CUSTOMER.tax_id, SUPPLIER.tax_id) AS VERGI_NO, CONCAT('', CASE PAYMENT_ENTRY.payment_type WHEN 'Pay' THEN 'Ödeme' WHEN 'Receive' THEN 'Tahsilat' ELSE 'BELİRSİZ' END, ' (', IFNULL(PAYMENT_ENTRY.mode_of_payment, ''), ')') AS TIP,#"3-Ödeme/Tahsilat" AS TIP,
        #PAYMENT_ENTRY.posting_date AS BELGE_TARIHI, PAYMENT_ENTRY.reference_no AS BELGE_NO, PAYMENT_ENTRY.reference_date AS VADE_TARIHI, 
		  PAYMENT_ENTRY.posting_date AS BELGE_TARIHI, PAYMENT_ENTRY.reference_no AS BELGE_NO, PAYMENT_ENTRY.posting_date AS VADE_TARIHI, 
        SUM(GL_ENTRY.debit) AS debit, SUM(GL_ENTRY.credit) AS credit, 
		  GL_ENTRY.posting_date, GL_ENTRY.account, 
		  #GL_ENTRY.name,  
		  GL_ENTRY.voucher_no, GL_ENTRY.fiscal_year, GL_ENTRY.voucher_type, GL_ENTRY.party, GL_ENTRY.party_type, 
		  CONCAT('', CASE PAYMENT_ENTRY.mode_of_payment WHEN 'Çek' THEN CONCAT('Vade:', DATE_FORMAT(PAYMENT_ENTRY.reference_date, '%%d.%%m.%%Y'), '. ') END, GL_ENTRY.remarks) AS remarks
		  #GL_ENTRY.remarks
		  #GL_ENTRY.*
    FROM `tabGL Entry` GL_ENTRY
    INNER JOIN `tabPayment Entry` PAYMENT_ENTRY ON GL_ENTRY.voucher_no = PAYMENT_ENTRY.name
    LEFT JOIN `tabSupplier` as SUPPLIER ON GL_ENTRY.PARTY = SUPPLIER.NAME AND GL_ENTRY.party_type = "Supplier"
    LEFT JOIN `tabCustomer` as CUSTOMER ON GL_ENTRY.PARTY = CUSTOMER.NAME AND GL_ENTRY.party_type = "Customer"
    WHERE
        GL_ENTRY.voucher_type = 'Payment Entry'
        AND GL_ENTRY.party IS NOT NULL
        AND PAYMENT_ENTRY.docstatus = 1
        AND GL_ENTRY.docstatus = 1
   GROUP BY
		COALESCE(CUSTOMER.name, SUPPLIER.name), COALESCE(CUSTOMER.customer_name, SUPPLIER.supplier_name), COALESCE(CUSTOMER.tax_id, SUPPLIER.tax_id),
		PAYMENT_ENTRY.reference_date, PAYMENT_ENTRY.reference_no, PAYMENT_ENTRY.posting_date,
		GL_ENTRY.posting_date, GL_ENTRY.account, 
		GL_ENTRY.voucher_no, GL_ENTRY.fiscal_year, GL_ENTRY.voucher_type, GL_ENTRY.party, GL_ENTRY.party_type
         
    UNION ALL
     
    #CARI HAREKETLERI (JOURNAL ENTRY) GETIRELIM
    SELECT
        COALESCE(CUSTOMER.name, SUPPLIER.name) AS CARI_KODU, COALESCE(CUSTOMER.customer_name, SUPPLIER.supplier_name) AS CARI_ADI, COALESCE(CUSTOMER.tax_id, SUPPLIER.tax_id) AS VERGI_NO, "Muhasebe Fişi" AS TIP,
        JOURNAL_ENTRY.posting_date AS BELGE_TARIHI, JOURNAL_ENTRY.title AS BELGE_NO, JOURNAL_ENTRY.posting_date AS VADE_TARIHI, 
		  GL_ENTRY.debit, GL_ENTRY.credit credit, GL_ENTRY.posting_date, GL_ENTRY.account, 
		  #GL_ENTRY.name,
		  GL_ENTRY.voucher_no, GL_ENTRY.fiscal_year, GL_ENTRY.voucher_type, GL_ENTRY.party, GL_ENTRY.party_type, GL_ENTRY.remarks
		  #GL_ENTRY.*
    FROM `tabGL Entry` GL_ENTRY
    INNER JOIN `tabJournal Entry` JOURNAL_ENTRY ON GL_ENTRY.voucher_no = JOURNAL_ENTRY.name
    LEFT JOIN `tabSupplier` as SUPPLIER ON GL_ENTRY.PARTY = SUPPLIER.NAME AND GL_ENTRY.party_type = "Supplier"
    LEFT JOIN `tabCustomer` as CUSTOMER ON GL_ENTRY.PARTY = CUSTOMER.NAME AND GL_ENTRY.party_type = "Customer"
    WHERE
        GL_ENTRY.voucher_type = 'Journal Entry'
        AND JOURNAL_ENTRY.docstatus = 1
        AND GL_ENTRY.docstatus = 1
 
) AS TMP_GLENTRY

#WHERE
	#TMP_GLENTRY.VERGI_NO = ''#%(tax_id)s

GROUP BY
    TMP_GLENTRY.VERGI_NO,
    TMP_GLENTRY.CARI_KODU, TMP_GLENTRY.CARI_ADI, TMP_GLENTRY.TIP, 
	 #TMP_GLENTRY.name, 
	 TMP_GLENTRY.voucher_no,
	 TMP_GLENTRY.fiscal_year, TMP_GLENTRY.voucher_type, 
    TMP_GLENTRY.party,
    TMP_GLENTRY.party_type,
    TMP_GLENTRY.remarks
ORDER BY
    TMP_GLENTRY.BELGE_TARIHI, TMP_GLENTRY.posting_date, TMP_GLENTRY.account

CustomScript-Supplier.js

/*LOGEDOSOFT 2020
Amac: Cari Hesap Ekstre butonu ile ekstre raporunun gosterilmesi.
*/
frappe.ui.form.on('Supplier', {
	refresh(frm) {
		frm.add_custom_button(__("Cari Hesap Ekstresi"), function() {
			frappe.set_route("query-report", "Cari Hesap Ekstresi", {"tax_id": frm.doc.tax_id, "customer": frm.doc.supplier_name});
			frappe.query_reports["Cari Hesap Ekstresi"] = {
            	"filters": [
            		{
            			fieldname:"tax_id",
            			label: __("Vergi No"),
            			fieldtype: "Data",
            			options: ""
            		},
            	]
			}
			
			frappe.query_report.load();
    	});
	}
});