def execute(filters=None):
	if not filters: filters = {}

	columns = get_columns(filters)
	entries = get_entries(filters)
	invoice_posting_date_map = get_invoice_posting_date_map(filters)
	against_date = ""
	outstanding_amount = 0.0

	data = []
	for d in entries:
		if d.against_voucher:
			against_date = d.against_voucher and invoice_posting_date_map[d.against_voucher] or ""
			outstanding_amount = flt(d.debit) or -1*flt(d.credit)
		else:
			against_date = d.against_invoice and invoice_posting_date_map[d.against_invoice] or ""
			outstanding_amount = flt(d.credit) or -1*flt(d.debit)

		row = [d.posting_date, d.name, d.cheque_no, d.cheque_date, d.account, d.against_voucher or d.against_invoice,
			against_date, d.debit, d.credit,  d.remark]

		if d.against_voucher or d.against_invoice:
			row += get_ageing_data(d.posting_date, against_date, outstanding_amount)
		else:
			row += ["", "", "", "", ""]

		data.append(row)

	return columns, data
Beispiel #2
0
def execute(filters=None):
	if not filters: filters = {}
	validate_filters(filters)
			
	columns = get_columns(filters)
	entries = get_entries(filters)
	invoice_posting_date_map = get_invoice_posting_date_map(filters)
	against_date = ""
	outstanding_amount = 0.0

	data = []
	for d in entries:
		if d.against_voucher:
			against_date = d.against_voucher and invoice_posting_date_map[d.against_voucher] or ""
			payment_amount = flt(d.debit) or -1 * flt(d.credit)
		else:
			against_date = d.against_invoice and invoice_posting_date_map[d.against_invoice] or ""
			payment_amount = flt(d.credit) or -1 * flt(d.debit)

		row = [d.name, d.party_type, d.party, d.posting_date, d.against_voucher or d.against_invoice,
			against_date, d.debit, d.credit, d.cheque_no, d.cheque_date, d.remark]

		if d.against_voucher or d.against_invoice:
			row += get_ageing_data(30, 60, 90, d.posting_date, against_date, payment_amount)
		else:
			row += ["", "", "", "", ""]

		data.append(row)

	return columns, data
Beispiel #3
0
def execute(filters=None):
    if not filters: filters = {}

    columns = get_columns()
    entries = get_entries(filters)
    invoice_posting_date_map = get_invoice_posting_date_map(filters)
    against_date = ""
    outstanding_amount = 0.0

    data = []
    for d in entries:
        if d.against_voucher:
            against_date = d.against_voucher and invoice_posting_date_map[
                d.against_voucher] or ""
            outstanding_amount = d.debit or -1 * d.credit
        else:
            against_date = d.against_invoice and invoice_posting_date_map[
                d.against_invoice] or ""
            outstanding_amount = d.credit or -1 * d.debit

        row = [
            d.name, d.account, d.posting_date, d.against_voucher
            or d.against_invoice, against_date, d.debit, d.credit, d.cheque_no,
            d.cheque_date, d.remark
        ]

        if d.against_voucher or d.against_invoice:
            row += get_ageing_data(d.posting_date, against_date,
                                   outstanding_amount)
        else:
            row += ["", "", "", "", ""]

        data.append(row)

    return columns, data
def execute(filters=None):
	if not filters: filters = {}
	validate_filters(filters)

	columns = get_columns(filters)
	entries = get_entries(filters)
	invoice_posting_date_map = get_invoice_posting_date_map(filters)
	against_date = ""

	data = []
	for d in entries:
		against_date = invoice_posting_date_map.get(d.reference_name) or ""
		if d.reference_type=="Purchase Invoice":
			payment_amount = flt(d.debit) or -1 * flt(d.credit)
		else:
			payment_amount = flt(d.credit) or -1 * flt(d.debit)

		row = [d.name, d.party_type, d.party, d.posting_date, d.reference_name,
			against_date, d.debit, d.credit, d.cheque_no, d.cheque_date, d.remark]

		if d.reference_name:
			row += get_ageing_data(30, 60, 90, d.posting_date, against_date, payment_amount)
		else:
			row += ["", "", "", "", ""]

		data.append(row)

	return columns, data
Beispiel #5
0
def execute(filters=None):
    if not filters: filters = {}
    validate_filters(filters)

    columns = get_columns(filters)
    entries = get_entries(filters)
    invoice_posting_date_map = get_invoice_posting_date_map(filters)
    against_date = ""

    data = []
    for d in entries:
        against_date = invoice_posting_date_map.get(d.reference_name) or ""
        if d.reference_type == "Purchase Invoice":
            payment_amount = flt(d.debit) or -1 * flt(d.credit)
        else:
            payment_amount = flt(d.credit) or -1 * flt(d.debit)

        row = [
            d.name, d.party_type, d.party, d.posting_date, d.reference_name,
            against_date, d.debit, d.credit, d.cheque_no, d.cheque_date,
            d.remark
        ]

        if d.reference_name:
            row += get_ageing_data(30, 60, 90, d.posting_date, against_date,
                                   payment_amount)
        else:
            row += ["", "", "", "", ""]

        data.append(row)

    return columns, data
def execute(filters=None):
	if not filters: filters = {}
	validate_filters(filters)

	columns = get_columns(filters)
	entries = get_entries(filters)
	invoice_details = get_invoice_posting_date_map(filters)

	data = []
	for d in entries:
		invoice = invoice_details.get(d.against_voucher) or frappe._dict()
		
		if d.reference_type=="Purchase Invoice":
			payment_amount = flt(d.debit) or -1 * flt(d.credit)
		else:
			payment_amount = flt(d.credit) or -1 * flt(d.debit)

		row = [d.voucher_type, d.voucher_no, d.party_type, d.party, d.posting_date, d.against_voucher, 
			invoice.posting_date, invoice.due_date, d.debit, d.credit, d.remarks]

		if d.against_voucher:
			row += get_ageing_data(30, 60, 90, d.posting_date, invoice.posting_date, payment_amount)
		else:
			row += ["", "", "", "", ""]
		if invoice.due_date:
			row.append((getdate(d.posting_date) - getdate(invoice.due_date)).days or 0)
		
		data.append(row)

	return columns, data
def execute(filters=None):
	if not filters: filters = {}
	supplier_naming_by = frappe.db.get_value("Buying Settings", None, "supp_master_name")
	columns = get_columns(supplier_naming_by)
	entries = get_gl_entries(filters)
	account_map = dict(((r.name, r) for r in frappe.db.sql("""select acc.name, 
		supp.supplier_name, supp.name as supplier 
		from `tabAccount` acc, `tabSupplier` supp 
		where acc.master_type="Supplier" and supp.name=acc.master_name""", as_dict=1)))

	entries_after_report_date = [[gle.voucher_type, gle.voucher_no] 
		for gle in get_gl_entries(filters, before_report_date=False)]

	account_supplier_type_map = get_account_supplier_type_map()
	voucher_detail_map = get_voucher_details()

	# Age of the invoice on this date
	age_on = getdate(filters.get("report_date")) > getdate(nowdate()) \
		and nowdate() or filters.get("report_date")

	data = []
	for gle in entries:
		if cstr(gle.against_voucher) == gle.voucher_no or not gle.against_voucher \
				or [gle.against_voucher_type, gle.against_voucher] in entries_after_report_date \
				or (gle.against_voucher_type == "Purchase Order"):
			voucher_details = voucher_detail_map.get(gle.voucher_type, {}).get(gle.voucher_no, {})
			
			invoiced_amount = gle.credit > 0 and gle.credit or 0
			outstanding_amount = get_outstanding_amount(gle, 
				filters.get("report_date") or nowdate())

			if abs(flt(outstanding_amount)) > 0.01:
				paid_amount = invoiced_amount - outstanding_amount
				row = [gle.posting_date, gle.account, gle.voucher_type, gle.voucher_no, 
					voucher_details.get("due_date", ""), voucher_details.get("bill_no", ""), 
					voucher_details.get("bill_date", ""), invoiced_amount, 
					paid_amount, outstanding_amount]
				
				# Ageing
				if filters.get("ageing_based_on") == "Due Date":
					ageing_based_on_date = voucher_details.get("due_date", "")
				else:
					ageing_based_on_date = gle.posting_date
					
				row += get_ageing_data(age_on, ageing_based_on_date, outstanding_amount) + \
					[account_map.get(gle.account, {}).get("supplier") or ""]

				if supplier_naming_by == "Naming Series":
					row += [account_map.get(gle.account, {}).get("supplier_name") or ""]

				row += [account_supplier_type_map.get(gle.account), gle.remarks]
				data.append(row)

	for i in range(0, len(data)):
		data[i].insert(4, """<a href="%s"><i class="icon icon-share" style="cursor: pointer;"></i></a>""" \
			% ("/".join(["#Form", data[i][2], data[i][3]]),))

	return columns, data
Beispiel #8
0
def execute(filters=None):
	if not filters: filters = {}
	supplier_naming_by = frappe.db.get_value("Buying Settings", None, "supp_master_name")
	columns = get_columns(supplier_naming_by)
	entries = get_gl_entries(filters)
	account_map = dict(((r.name, r) for r in frappe.db.sql("""select acc.name, 
		supp.supplier_name, supp.name as supplier 
		from `tabAccount` acc, `tabSupplier` supp 
		where acc.master_type="Supplier" and supp.name=acc.master_name""", as_dict=1)))

	entries_after_report_date = [[gle.voucher_type, gle.voucher_no] 
		for gle in get_gl_entries(filters, before_report_date=False)]

	account_supplier_type_map = get_account_supplier_type_map()
	voucher_detail_map = get_voucher_details()

	# Age of the invoice on this date
	age_on = getdate(filters.get("report_date")) > getdate(nowdate()) \
		and nowdate() or filters.get("report_date")

	data = []
	for gle in entries:
		if cstr(gle.against_voucher) == gle.voucher_no or not gle.against_voucher \
				or [gle.against_voucher_type, gle.against_voucher] in entries_after_report_date:
			voucher_details = voucher_detail_map.get(gle.voucher_type, {}).get(gle.voucher_no, {})
			
			invoiced_amount = gle.credit > 0 and gle.credit or 0
			outstanding_amount = get_outstanding_amount(gle, 
				filters.get("report_date") or nowdate())

			if abs(flt(outstanding_amount)) > 0.01:
				paid_amount = invoiced_amount - outstanding_amount
				row = [gle.posting_date, gle.account, gle.voucher_type, gle.voucher_no, 
					voucher_details.get("due_date", ""), voucher_details.get("bill_no", ""), 
					voucher_details.get("bill_date", ""), invoiced_amount, 
					paid_amount, outstanding_amount]
				
				# Ageing
				if filters.get("ageing_based_on") == "Due Date":
					ageing_based_on_date = voucher_details.get("due_date", "")
				else:
					ageing_based_on_date = gle.posting_date
					
				row += get_ageing_data(age_on, ageing_based_on_date, outstanding_amount) + \
					[account_map.get(gle.account, {}).get("supplier") or ""]

				if supplier_naming_by == "Naming Series":
					row += [account_map.get(gle.account, {}).get("supplier_name") or ""]

				row += [account_supplier_type_map.get(gle.account), gle.remarks]
				data.append(row)

	for i in range(0, len(data)):
		data[i].insert(4, """<a href="%s"><i class="icon icon-share" style="cursor: pointer;"></i></a>""" \
			% ("/".join(["#Form", data[i][2], data[i][3]]),))

	return columns, data