Last active
February 8, 2023 07:41
-
-
Save itsyosefali/ae1b5f5ce2fe1932b1588077bc6c334a to your computer and use it in GitHub Desktop.
the stock ledeger of sponge shape only
This file contains hidden or 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
| 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"And`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 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 posting_date >= {date_from}""" | |
| elif filters.get("date_to"): | |
| date_to = frappe.db.escape(filters.get("date_to")) | |
| sql_date_cond = f"""and posting_date <= {date_to} """ | |
| res = frappe.db.sql(f"""SELECT | |
| `tabStock Ledger Entry`.item_code, | |
| `tabStock Ledger Entry`.sponge_shape, | |
| actual_qty, | |
| posting_date, | |
| warehouse, | |
| shape_related_factor | |
| FROM `tabStock Ledger Entry` | |
| INNER JOIN `tabSponge Shape` | |
| ON `tabStock Ledger Entry`.sponge_shape = `tabSponge Shape`.name | |
| Where `tabStock Ledger Entry`.is_cancelled = 0 | |
| and `tabStock Ledger Entry`.posting_date between '2022-12-30' AND CURDATE() | |
| {sql_item_code_cond_filter} | |
| {sql_date_cond} | |
| {sql_warehouse_cond_filter} | |
| order by | |
| sponge_shape desc | |
| """, as_dict=True) | |
| sum = 0 | |
| total = 0 | |
| prev_sponge_shape = None | |
| for item in res: | |
| if item.shape_related_factor and item.actual_qty: | |
| div_res = float(item.actual_qty) / float(item.shape_related_factor) | |
| item.sponge = div_res | |
| if(item.sponge > 0): | |
| item.positive = item.sponge | |
| else: | |
| item.negative = item.sponge | |
| if(item.positive): | |
| item.negative=0 | |
| if prev_sponge_shape is not None and prev_sponge_shape == item.sponge_shape : | |
| total = total + item.positive + item.negative | |
| else: | |
| total = item.positive + item.negative | |
| item.sum = total | |
| if(item.negative): | |
| item.positive=0 | |
| if prev_sponge_shape is not None and prev_sponge_shape == item.sponge_shape : | |
| total = total + item.positive + item.negative | |
| else: | |
| total = item.positive + item.negative | |
| item.sum = total | |
| prev_sponge_shape = item.sponge_shape | |
| result = res |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment