تقرير لل سبونج شايب لي شركة الربيع

scriptreport.py

sql_item_code_cond_filter = ""
if filters.get("item_code"):
    escaped_input = frappe.db.escape(filters.get("item_code"))
    sql_item_code_cond_filter = f"AND `tabStock Ledger Entry`.item_code = {escaped_input}"
    
sql_sponge_shape=""
if filters.get("sponge_shape"):
    escaped_input = frappe.db.escape(filters.get("sponge_shape"))
    sql_item_code_cond_filter = f"AND `tabStock Ledger Entry`.sponge_shape = {escaped_input}"
    
sql_warehouse_cond_filter = ""
if filters.get("warehouse"):
    escaped_input = frappe.db.escape(filters.get("warehouse"))
    sql_warehouse_cond_filter = f"Having`tabStock Ledger Entry`.warehouse = {escaped_input}"

sql_date_cond = ""
if filters.get("date_from") and filters.get("date_to"):
    date_from = frappe.db.escape(filters.get("date_from"))
    date_to = frappe.db.escape(filters.get("date_to"))
    
    sql_date_cond = f""" and `tabStock Ledger Entry`.posting_date between {date_from} and {date_to} """
    
elif filters.get("date_from"):
    date_from = frappe.db.escape(filters.get("date_from"))
    sql_date_cond = f""" and `tabStock Ledger Entry`.posting_date >= {date_from}"""

elif filters.get("date_to"):
    date_to = frappe.db.escape(filters.get("date_to"))
    
    sql_date_cond = f""" and `tabStock Ledger Entry`.posting_date <= {date_to} """
    
res = frappe.db.sql(f"""SELECT `tabStock Ledger Entry`.item_code,
       `tabStock Ledger Entry`.posting_date,
       `tabStock Ledger Entry`.warehouse,
       `tabStock Ledger Entry`.sponge_shape,
       `tabSponge Shape`.shape_related_factor, 
        `tabStock Ledger Entry`.actual_qty AS 'qty',
       sum(`tabStock Ledger Entry`.actual_qty) as 'qty_after_transaction'
  FROM `tabStock Ledger Entry`
 
INNER JOIN `tabSponge Shape`
ON `tabStock Ledger Entry`.sponge_shape = `tabSponge Shape`.name
 Where  `tabStock Ledger Entry`.is_cancelled = 0
 {sql_item_code_cond_filter}
    {sql_date_cond}
   Group By
            `tabStock Ledger Entry`.sponge_shape
    {sql_warehouse_cond_filter}
   """, as_dict=True)
for item in res:
        div_res1 = item.qty + item.qty
        item.qty = div_res1     
        
for item in res:
    if item.shape_related_factor and item.qty:
        div_res = float(item.qty) / float(item.shape_related_factor)
        item.sponge = div_res    




result = res