Last active
July 21, 2020 16:18
-
-
Save TurkerTunali/8ac33b190d63f87b9aa2160fa2f29e13 to your computer and use it in GitHub Desktop.
Running balance report for customer and suppliers combined by tax id
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 | |
#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 |
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
/*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(); | |
}); | |
} | |
}); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment