Report & SQL Formats - ashish-greycube/help GitHub Wiki

1. Using '.format()'

def get_data(filters):
	conditions = get_conditions(filters)
	si_list = frappe.db.sql("""
					SELECT
			name,
			DATE_FORMAT(posting_date, '%b') AS month,
			YEAR(posting_date) AS year,
			customer,
			SUM(base_grand_total) as sales_amount,
			(SUM(base_grand_total)- SUM(outstanding_amount)) as paid_amount
		FROM
			`tabSales Invoice`
		WHERE
			docstatus = 1 
			{0}
		GROUP BY month
		ORDER BY posting_date ASC
			""".format(conditions),as_dict=1,debug=1)
	return si_list

def get_conditions(filters):
	conditions = ""

	if filters.get("fiscal_year"):
		conditions += " and YEAR(posting_date) = '{0}'".format(filters.get("fiscal_year"))

	if filters.get("customer"):
		conditions += " and customer = '{0}'".format(filters.get("customer"))

	if filters.get("month"):
		conditions += " and DATE_FORMAT(posting_date, '%b') = '{0}'".format(filters.get("month"))

	return conditions

2. Using '%()s'

Type - 1

def get_data(filters):
	conditions = get_conditions(filters)
	query = frappe.db.sql(
		""" SELECT
					vd.parent vessel,
					vd.customer_name customer ,
					vd.item customer_item,
					vd.tonnage_mt received_qty,
					rrd.commercial_destination_item commercial_destination_item,
					IFNULL(sum(rrd.rr_item_weight_mt), 0) dispatch_qty
			FROM
					`tabVessel Details` vd
			left outer join `tabRailway Receipt Item Details` rrd on
					vd.parent = rrd.vessel
				and vd.item = rrd.commercial_destination_item
				and rrd.docstatus < 2
				{0}
			group by
				vd.parent,rrd.commercial_destination_item
				
""".format(conditions),filters,as_dict=1,debug=1)

def get_conditions(filters):
	conditions = ""
	if filters.customer:
		conditions += " and vd.customer_name = %(customer)s"
	
	return conditions

Type - 2

delivered_via_si = frappe.db.sql(
			"""select sum(si_item.qty)
			from `tabSales Invoice Item` si_item, `tabSales Invoice` si
			where si_item.parent = si.name and si.update_stock = 1
			and si_item.so_detail = %s and si.docstatus = 1
			and si_item.sales_order = %s
			and si.name != %s""",
			(so_detail, so, current_docname),
		)

Type - 3

def get_requested_qty(project_name,item_code):
    values = {'project_name': project_name,'item_code':item_code}
    data = frappe.db.sql("""
SELECT
	sum(mri.qty) as mri_qty
FROM
	`tabMaterial Request Item` as mri
where
	mri.project =  %(project_name)s
    and mri.item_code=%(item_code)s
	and mri.docstatus = 1
group by
	mri.item_code
    """, values=values, as_dict=1,debug=0)
    print('data',data)
    return data    

Add Column as per Filter Dates Range:

Ref: https://github.com/ashish-greycube/farki/blob/main/farki/farki/report/petpooja_log_summary/petpooja_log_summary.py#L73

def get_data(filters, columns):
	data = []

	conditions = get_conditions(filters)
	data = frappe.db.sql(
		"""SELECT
		ppl.branch as branch, ppl.business_date as business_date, ppl.invoice_status as invoice_status, 1 as count_log
		From `tabPet Pooja Log` as ppl
		Where {0}""".format(conditions),filters,as_dict=1,debug=1)

	result = make_report(data, filters, columns)
	# print(result)

	return result



def make_report(data, filters, columns):
        from itertools import groupby
        from frappe.utils import flt, date_diff, getdate, cstr, cint
        import datetime

	result = []

	from_date, to_date = filters.get("from_date"), filters.get("to_date")
	numdays = date_diff(to_date, from_date)

	dates = [
		(getdate(to_date) - datetime.timedelta(days=x)).strftime("%Y-%m-%d")
		for x in range(numdays)
	]

	dates.append(filters.get("from_date"))

	for dt in dates:
		columns.append(
				{
					"fieldname": dt,
					"label": dt,
					"fieldtype": "Data",
					"width": 150,
				}
			)

	grouping_key = lambda o: (o["branch"], o["invoice_status"])
	for (branch, invoice_status), rows in groupby(
		sorted(data, key=grouping_key), key=grouping_key
	):
		_rows = list(rows)

		row = {
			"branch": branch,
			"invoice_status": invoice_status,
		}

		result.append(row)
			
	return result