def get_items(filters, additional_query_columns): conditions = get_conditions(filters) match_conditions = frappe.build_match_conditions("Sales Invoice") if match_conditions: match_conditions = " and {0} ".format(match_conditions) if additional_query_columns: additional_query_columns = ', ' + ', '.join(additional_query_columns) return frappe.db.sql(""" select `tabSales Invoice Item`.name, `tabSales Invoice Item`.parent, `tabSales Invoice`.posting_date, `tabSales Invoice`.debit_to, `tabSales Invoice`.project, `tabSales Invoice`.customer, `tabSales Invoice`.remarks, `tabSales Invoice`.territory, `tabSales Invoice`.company, `tabSales Invoice`.base_net_total, `tabSales Invoice Item`.item_code, `tabSales Invoice Item`.item_name, `tabSales Invoice Item`.item_group, `tabSales Invoice Item`.sales_order, `tabSales Invoice Item`.delivery_note, `tabSales Invoice Item`.income_account, `tabSales Invoice Item`.cost_center, `tabSales Invoice Item`.stock_qty, `tabSales Invoice Item`.stock_uom, `tabSales Invoice Item`.base_net_rate, `tabSales Invoice Item`.base_net_amount, `tabSales Invoice`.customer_name, `tabSales Invoice`.customer_group, `tabSales Invoice Item`.so_detail, `tabSales Invoice`.update_stock, `tabSales Invoice Item`.uom, `tabSales Invoice Item`.qty {0} from `tabSales Invoice`, `tabSales Invoice Item` where `tabSales Invoice`.name = `tabSales Invoice Item`.parent and `tabSales Invoice`.docstatus = 1 %s %s order by `tabSales Invoice`.posting_date desc, `tabSales Invoice Item`.item_code desc """.format(additional_query_columns or '') % (conditions, match_conditions), filters, as_dict=1)
def get_items(filters, additional_query_columns): conditions = get_conditions(filters) match_conditions = frappe.build_match_conditions("Purchase Invoice") if match_conditions: match_conditions = " and {0} ".format(match_conditions) if additional_query_columns: additional_query_columns = ', ' + ', '.join(additional_query_columns) return frappe.db.sql(""" select `tabPurchase Invoice Item`.`name`, `tabPurchase Invoice Item`.`parent`, `tabPurchase Invoice`.posting_date, `tabPurchase Invoice`.credit_to, `tabPurchase Invoice`.company, `tabPurchase Invoice`.supplier, `tabPurchase Invoice`.remarks, `tabPurchase Invoice`.base_net_total, `tabPurchase Invoice Item`.`item_code`, `tabPurchase Invoice Item`.`item_name`, `tabPurchase Invoice Item`.`item_group`, `tabPurchase Invoice Item`.`project`, `tabPurchase Invoice Item`.`purchase_order`, `tabPurchase Invoice Item`.`purchase_receipt`, `tabPurchase Invoice Item`.`po_detail`, `tabPurchase Invoice Item`.`expense_account`, `tabPurchase Invoice Item`.`stock_qty`, `tabPurchase Invoice Item`.`stock_uom`, `tabPurchase Invoice Item`.`base_net_rate`, `tabPurchase Invoice Item`.`base_net_amount`, `tabPurchase Invoice`.supplier_name, `tabPurchase Invoice`.mode_of_payment {0} from `tabPurchase Invoice`, `tabPurchase Invoice Item` where `tabPurchase Invoice`.name = `tabPurchase Invoice Item`.`parent` and `tabPurchase Invoice`.docstatus = 1 %s %s order by `tabPurchase Invoice`.posting_date desc, `tabPurchase Invoice Item`.item_code desc """.format(additional_query_columns) % (conditions, match_conditions), filters, as_dict=1)
def get_items(filters, additional_query_columns): conditions = get_conditions(filters) match_conditions = frappe.build_match_conditions("Sales Invoice") if match_conditions: match_conditions = " and {0} ".format(match_conditions) if additional_query_columns: additional_query_columns = ', ' + ', '.join(additional_query_columns) return frappe.db.sql(""" select `tabSales Invoice Item`.name, `tabSales Invoice Item`.parent, `tabSales Invoice`.posting_date, `tabSales Invoice`.debit_to, `tabSales Invoice`.project, `tabSales Invoice`.customer, `tabSales Invoice`.remarks, `tabSales Invoice`.territory, `tabSales Invoice`.company, `tabSales Invoice`.base_net_total, `tabSales Invoice Item`.item_code, `tabSales Invoice Item`.item_name, `tabSales Invoice Item`.item_group, `tabSales Invoice Item`.sales_order, `tabSales Invoice Item`.delivery_note, `tabSales Invoice Item`.income_account, `tabSales Invoice Item`.cost_center, `tabSales Invoice Item`.stock_qty, `tabSales Invoice Item`.stock_uom, `tabSales Invoice Item`.base_net_rate, `tabSales Invoice Item`.base_net_amount, `tabSales Invoice`.customer_name, `tabSales Invoice`.customer_group, `tabSales Invoice Item`.so_detail, `tabSales Invoice`.update_stock {0} from `tabSales Invoice`, `tabSales Invoice Item` where `tabSales Invoice`.name = `tabSales Invoice Item`.parent and `tabSales Invoice`.docstatus = 1 %s %s order by `tabSales Invoice`.posting_date desc, `tabSales Invoice Item`.item_code desc """.format(additional_query_columns or '') % (conditions, match_conditions), filters, as_dict=1)
def get_items(filters, additional_query_columns): conditions = get_conditions(filters) match_conditions = frappe.build_match_conditions("Purchase Invoice") if match_conditions: match_conditions = " and {0} ".format(match_conditions) if additional_query_columns: additional_query_columns = ', ' + ', '.join(additional_query_columns) return frappe.db.sql(""" select `tabPurchase Invoice Item`.`name`, `tabPurchase Invoice Item`.`parent`, `tabPurchase Invoice`.posting_date, `tabPurchase Invoice`.credit_to, `tabPurchase Invoice`.company, `tabPurchase Invoice`.supplier, `tabPurchase Invoice`.remarks, `tabPurchase Invoice`.base_net_total, `tabPurchase Invoice Item`.`item_code`, `tabPurchase Invoice Item`.`item_name`, `tabPurchase Invoice Item`.`item_group`, `tabPurchase Invoice Item`.description, `tabPurchase Invoice Item`.`project`, `tabPurchase Invoice Item`.`purchase_order`, `tabPurchase Invoice Item`.`purchase_receipt`, `tabPurchase Invoice Item`.`po_detail`, `tabPurchase Invoice Item`.`expense_account`, `tabPurchase Invoice Item`.`stock_qty`, `tabPurchase Invoice Item`.`stock_uom`, `tabPurchase Invoice Item`.`base_net_amount`, `tabPurchase Invoice`.supplier_name, `tabPurchase Invoice`.mode_of_payment {0} from `tabPurchase Invoice`, `tabPurchase Invoice Item` where `tabPurchase Invoice`.name = `tabPurchase Invoice Item`.`parent` and `tabPurchase Invoice`.docstatus = 1 %s %s order by `tabPurchase Invoice`.posting_date desc, `tabPurchase Invoice Item`.item_code desc """.format(additional_query_columns) % (conditions, match_conditions), filters, as_dict=1)
def get_items(filters, additional_query_columns): conditions = get_conditions(filters) match_conditions = frappe.build_match_conditions("Sales Invoice") if match_conditions: match_conditions = " and {0} ".format(match_conditions) if additional_query_columns: additional_query_columns = ', ' + ', '.join(additional_query_columns) return frappe.db.sql(""" select `tabSales Invoice`.posting_date, `tabSales Invoice Item`.cost_center, `tabSales Invoice Item`.name, `tabSales Invoice Item`.parent, `tabSales Invoice`.posting_date, `tabSales Invoice Item`.item_code, `tabSales Invoice Item`.item_name, `tabSales Invoice Item`.item_group, sum(`tabSales Invoice Item`.stock_qty) as stock_qty, `tabSales Invoice Item`.stock_uom, sum(`tabSales Invoice Item`.base_net_amount) as base_net_amount, `tabSales Invoice Item`.base_net_rate, `tabSales Invoice Item`.uom from `tabSales Invoice`, `tabSales Invoice Item` where `tabSales Invoice`.name = `tabSales Invoice Item`.parent and `tabSales Invoice`.docstatus = 1 %s %s group by `tabSales Invoice Item`.cost_center, `tabSales Invoice Item`.item_code order by `tabSales Invoice`.posting_date desc, `tabSales Invoice Item`.cost_center desc,`tabSales Invoice Item`.item_code desc """.format(additional_query_columns or '') % (conditions, match_conditions), filters, as_dict=1)
def get_items(filters): conditions = get_conditions(filters) match_conditions = frappe.build_match_conditions("Purchase Invoice") return frappe.db.sql("""select pi_item.parent, pi.posting_date, pi.credit_to, pi.company, pi.supplier, pi.remarks, pi.base_net_total, pi_item.item_code, pi_item.item_name, pi_item.item_group, pi_item.project, pi_item.purchase_order, pi_item.purchase_receipt, pi_item.po_detail, pi_item.expense_account, pi_item.qty, pi_item.base_net_rate, pi_item.base_net_amount, pi.supplier_name from `tabPurchase Invoice` pi, `tabPurchase Invoice Item` pi_item where pi.name = pi_item.parent and pi.docstatus = 1 %s %s order by pi.posting_date desc, pi_item.item_code desc""" % (conditions, match_conditions), filters, as_dict=1)
def get_items(filters): conditions = get_conditions(filters) match_conditions = frappe.build_match_conditions("Purchase Invoice") return frappe.db.sql("""select pi_item.parent, pi.posting_date, pi.credit_to, pi.company, pi.supplier, pi.remarks, pi.base_net_total, pi_item.item_code, pi_item.item_name, pi_item.item_group, pi_item.project_name, pi_item.purchase_order, pi_item.purchase_receipt, pi_item.po_detail pi_item.expense_account, pi_item.qty, pi_item.base_net_rate, pi_item.base_net_amount, pi.supplier_name from `tabPurchase Invoice` pi, `tabPurchase Invoice Item` pi_item where pi.name = pi_item.parent and pi.docstatus = 1 %s %s order by pi.posting_date desc, pi_item.item_code desc""" % (conditions, match_conditions), filters, as_dict=1)
def get_items(filters, additional_query_columns): conditions = get_conditions(filters) match_conditions = frappe.build_match_conditions("Purchase Invoice") if additional_query_columns: additional_query_columns = ', ' + ', '.join(additional_query_columns) return frappe.db.sql(""" select pi_item.name, pi_item.parent, pi.posting_date, pi.credit_to, pi.company, pi.supplier, pi.remarks, pi.base_net_total, pi_item.item_code, pi_item.item_name, pi_item.item_group, pi_item.project, pi_item.purchase_order, pi_item.purchase_receipt, pi_item.po_detail, pi_item.expense_account, pi_item.qty, pi_item.stock_uom, pi_item.base_net_rate, pi_item.base_net_amount, pi.supplier_name, pi.mode_of_payment {0} from `tabPurchase Invoice` pi, `tabPurchase Invoice Item` pi_item where pi.name = pi_item.parent and pi.docstatus = 1 %s %s order by pi.posting_date desc, pi_item.item_code desc """.format(additional_query_columns) % (conditions, match_conditions), filters, as_dict=1)
def get_items(filters): conditions = get_conditions(filters) match_conditions = frappe.build_match_conditions("Purchase Invoice") if match_conditions: match_conditions = " and {0} ".format(match_conditions) return frappe.db.sql(""" select `tabPurchase Invoice Item`.name, `tabPurchase Invoice Item`.parent, `tabPurchase Invoice`.posting_date, `tabPurchase Invoice`.credit_to, `tabPurchase Invoice`.company, `tabPurchase Invoice`.supplier, `tabPurchase Invoice`.remarks, `tabPurchase Invoice`.base_net_total, `tabPurchase Invoice Item`.item_code, `tabPurchase Invoice Item`.item_name, `tabPurchase Invoice Item`.item_group, `tabPurchase Invoice Item`.description, `tabPurchase Invoice Item`.project, `tabPurchase Invoice Item`.purchase_order, `tabPurchase Invoice Item`.purchase_receipt, `tabPurchase Invoice Item`.po_detail, `tabPurchase Invoice Item`.expense_account, `tabPurchase Invoice Item`.qty, `tabPurchase Invoice Item`.base_net_rate, `tabPurchase Invoice Item`.base_net_amount, `tabPurchase Invoice`.supplier_name, `tabPurchase Invoice`.mode_of_payment from `tabPurchase Invoice`, `tabPurchase Invoice Item` where `tabPurchase Invoice`.name = `tabPurchase Invoice Item`.parent and `tabPurchase Invoice`.docstatus = 1 %s %s order by `tabPurchase Invoice`.posting_date desc, `tabPurchase Invoice Item`.item_code desc """ % (conditions, match_conditions), filters, as_dict=1)
def get_items(filters): conditions = get_conditions(filters) match_conditions = frappe.build_match_conditions("Sales Invoice") if match_conditions: match_conditions = " and {0} ".format(match_conditions) return frappe.db.sql(""" select `tabSales Invoice Item`.name, `tabSales Invoice Item`.base_price_list_rate, `tabSales Invoice Item`.gst_hsn_code, `tabSales Invoice Item`.stock_qty, `tabSales Invoice Item`.stock_uom, `tabSales Invoice Item`.base_net_amount, `tabSales Invoice Item`.parent, `tabSales Invoice Item`.item_code, `tabGST HSN Code`.description from `tabSales Invoice`, `tabSales Invoice Item`, `tabGST HSN Code` where `tabSales Invoice`.name = `tabSales Invoice Item`.parent and `tabSales Invoice`.docstatus = 1 and `tabSales Invoice Item`.gst_hsn_code is not NULL and `tabSales Invoice Item`.gst_hsn_code = `tabGST HSN Code`.name %s %s """ % (conditions, match_conditions), filters, as_dict=1)
def get_items(filters, additional_query_columns): conditions = get_conditions(filters) match_conditions = frappe.build_match_conditions("Job Card") if match_conditions: match_conditions = " and {0} ".format(match_conditions) if additional_query_columns: additional_query_columns = ', ' + ', '.join(additional_query_columns) return frappe.db.sql(""" select `tabJob Card`.name, `tabMaintenance Services Table`.start_date, `tabMaintenance Services Table`.end_date, `tabMaintenance Services Table`.start_time, `tabMaintenance Services Table`.end_time, `tabJob Card`.name, `tabJob Card`.customer, `tabJob Card`.status, `tabJob Card`.warehouse, `tabEmployee`.employee_name, `tabMaintenance Services Table`.service, `tabMaintenance Services Table`.billable_hours, `tabMaintenance Services Table`.standard_hours, `tabMaintenance Services Table`.technician, `tabMaintenance Services Table`.standard_cost from `tabJob Card`, `tabMaintenance Services Table` left join `tabEmployee` on `tabEmployee`.name = `tabMaintenance Services Table`.technician where `tabJob Card`.name = `tabMaintenance Services Table`.parent and `tabJob Card`.docstatus = 1 %s %s order by `tabMaintenance Services Table`.start_date """.format(additional_query_columns or '') % (conditions, match_conditions), filters, as_dict=1)