Rohit Waghchaure | f704eb7 | 2023-03-30 11:32:39 +0530 | [diff] [blame] | 1 | from collections import defaultdict |
| 2 | |
Rohit Waghchaure | e6143ab | 2023-03-13 14:51:43 +0530 | [diff] [blame] | 3 | import frappe |
| 4 | from frappe.query_builder.functions import CombineDatetime, Sum |
| 5 | from frappe.utils import flt |
Rohit Waghchaure | c1132d1 | 2023-03-24 10:14:09 +0530 | [diff] [blame] | 6 | from frappe.utils.deprecations import deprecated |
Rohit Waghchaure | f4cfc58 | 2023-04-11 13:22:15 +0530 | [diff] [blame] | 7 | from pypika import Order |
Rohit Waghchaure | e6143ab | 2023-03-13 14:51:43 +0530 | [diff] [blame] | 8 | |
| 9 | |
| 10 | class DeprecatedSerialNoValuation: |
Rohit Waghchaure | c1132d1 | 2023-03-24 10:14:09 +0530 | [diff] [blame] | 11 | @deprecated |
Rohit Waghchaure | e6143ab | 2023-03-13 14:51:43 +0530 | [diff] [blame] | 12 | def calculate_stock_value_from_deprecarated_ledgers(self): |
| 13 | serial_nos = list( |
| 14 | filter(lambda x: x not in self.serial_no_incoming_rate and x, self.get_serial_nos()) |
| 15 | ) |
| 16 | |
| 17 | actual_qty = flt(self.sle.actual_qty) |
| 18 | |
| 19 | stock_value_change = 0 |
| 20 | if actual_qty < 0: |
Rohit Waghchaure | e6143ab | 2023-03-13 14:51:43 +0530 | [diff] [blame] | 21 | if not self.sle.is_cancelled: |
| 22 | outgoing_value = self.get_incoming_value_for_serial_nos(serial_nos) |
| 23 | stock_value_change = -1 * outgoing_value |
Rohit Waghchaure | e6143ab | 2023-03-13 14:51:43 +0530 | [diff] [blame] | 24 | |
| 25 | self.stock_value_change += stock_value_change |
| 26 | |
Rohit Waghchaure | c1132d1 | 2023-03-24 10:14:09 +0530 | [diff] [blame] | 27 | @deprecated |
Rohit Waghchaure | e6143ab | 2023-03-13 14:51:43 +0530 | [diff] [blame] | 28 | def get_incoming_value_for_serial_nos(self, serial_nos): |
| 29 | # get rate from serial nos within same company |
| 30 | all_serial_nos = frappe.get_all( |
| 31 | "Serial No", fields=["purchase_rate", "name", "company"], filters={"name": ("in", serial_nos)} |
| 32 | ) |
| 33 | |
| 34 | incoming_values = 0.0 |
| 35 | for d in all_serial_nos: |
| 36 | if d.company == self.sle.company: |
Rohit Waghchaure | d3ceb07 | 2023-03-31 09:03:54 +0530 | [diff] [blame] | 37 | self.serial_no_incoming_rate[d.name] += flt(d.purchase_rate) |
Rohit Waghchaure | e6143ab | 2023-03-13 14:51:43 +0530 | [diff] [blame] | 38 | incoming_values += flt(d.purchase_rate) |
| 39 | |
| 40 | # Get rate for serial nos which has been transferred to other company |
| 41 | invalid_serial_nos = [d.name for d in all_serial_nos if d.company != self.sle.company] |
| 42 | for serial_no in invalid_serial_nos: |
Rohit Waghchaure | f4cfc58 | 2023-04-11 13:22:15 +0530 | [diff] [blame] | 43 | table = frappe.qb.DocType("Stock Ledger Entry") |
| 44 | incoming_rate = ( |
| 45 | frappe.qb.from_(table) |
| 46 | .select(table.incoming_rate) |
| 47 | .where( |
| 48 | ( |
| 49 | (table.serial_no == serial_no) |
| 50 | | (table.serial_no.like(serial_no + "\n%")) |
| 51 | | (table.serial_no.like("%\n" + serial_no)) |
| 52 | | (table.serial_no.like("%\n" + serial_no + "\n%")) |
Rohit Waghchaure | e6143ab | 2023-03-13 14:51:43 +0530 | [diff] [blame] | 53 | ) |
Rohit Waghchaure | f4cfc58 | 2023-04-11 13:22:15 +0530 | [diff] [blame] | 54 | & (table.company == self.sle.company) |
| 55 | & (table.serial_and_batch_bundle.isnull()) |
| 56 | & (table.actual_qty > 0) |
| 57 | & (table.is_cancelled == 0) |
| 58 | ) |
| 59 | .orderby(table.posting_date, order=Order.desc) |
| 60 | .limit(1) |
| 61 | ).run() |
Rohit Waghchaure | e6143ab | 2023-03-13 14:51:43 +0530 | [diff] [blame] | 62 | |
Rohit Waghchaure | d3ceb07 | 2023-03-31 09:03:54 +0530 | [diff] [blame] | 63 | self.serial_no_incoming_rate[serial_no] += flt(incoming_rate[0][0]) if incoming_rate else 0 |
Rohit Waghchaure | e6143ab | 2023-03-13 14:51:43 +0530 | [diff] [blame] | 64 | incoming_values += self.serial_no_incoming_rate[serial_no] |
| 65 | |
| 66 | return incoming_values |
| 67 | |
| 68 | |
| 69 | class DeprecatedBatchNoValuation: |
Rohit Waghchaure | c1132d1 | 2023-03-24 10:14:09 +0530 | [diff] [blame] | 70 | @deprecated |
Rohit Waghchaure | e6143ab | 2023-03-13 14:51:43 +0530 | [diff] [blame] | 71 | def calculate_avg_rate_from_deprecarated_ledgers(self): |
Rohit Waghchaure | 5bb3173 | 2023-03-21 10:54:41 +0530 | [diff] [blame] | 72 | entries = self.get_sle_for_batches() |
| 73 | for ledger in entries: |
Rohit Waghchaure | f704eb7 | 2023-03-30 11:32:39 +0530 | [diff] [blame] | 74 | self.stock_value_differece[ledger.batch_no] += flt(ledger.batch_value) |
Rohit Waghchaure | 86da306 | 2023-03-20 14:15:34 +0530 | [diff] [blame] | 75 | self.available_qty[ledger.batch_no] += flt(ledger.batch_qty) |
Rohit Waghchaure | e6143ab | 2023-03-13 14:51:43 +0530 | [diff] [blame] | 76 | |
Rohit Waghchaure | c1132d1 | 2023-03-24 10:14:09 +0530 | [diff] [blame] | 77 | @deprecated |
Rohit Waghchaure | e6143ab | 2023-03-13 14:51:43 +0530 | [diff] [blame] | 78 | def get_sle_for_batches(self): |
Rohit Waghchaure | f704eb7 | 2023-03-30 11:32:39 +0530 | [diff] [blame] | 79 | if not self.batchwise_valuation_batches: |
| 80 | return [] |
| 81 | |
Rohit Waghchaure | e6143ab | 2023-03-13 14:51:43 +0530 | [diff] [blame] | 82 | sle = frappe.qb.DocType("Stock Ledger Entry") |
| 83 | |
| 84 | timestamp_condition = CombineDatetime(sle.posting_date, sle.posting_time) < CombineDatetime( |
| 85 | self.sle.posting_date, self.sle.posting_time |
| 86 | ) |
| 87 | if self.sle.creation: |
| 88 | timestamp_condition |= ( |
| 89 | CombineDatetime(sle.posting_date, sle.posting_time) |
| 90 | == CombineDatetime(self.sle.posting_date, self.sle.posting_time) |
| 91 | ) & (sle.creation < self.sle.creation) |
| 92 | |
Rohit Waghchaure | c2d7461 | 2023-03-29 11:40:36 +0530 | [diff] [blame] | 93 | query = ( |
Rohit Waghchaure | e6143ab | 2023-03-13 14:51:43 +0530 | [diff] [blame] | 94 | frappe.qb.from_(sle) |
| 95 | .select( |
| 96 | sle.batch_no, |
| 97 | Sum(sle.stock_value_difference).as_("batch_value"), |
| 98 | Sum(sle.actual_qty).as_("batch_qty"), |
| 99 | ) |
| 100 | .where( |
| 101 | (sle.item_code == self.sle.item_code) |
Rohit Waghchaure | e6143ab | 2023-03-13 14:51:43 +0530 | [diff] [blame] | 102 | & (sle.warehouse == self.sle.warehouse) |
Rohit Waghchaure | f704eb7 | 2023-03-30 11:32:39 +0530 | [diff] [blame] | 103 | & (sle.batch_no.isin(self.batchwise_valuation_batches)) |
| 104 | & (sle.batch_no.isnotnull()) |
Rohit Waghchaure | e6143ab | 2023-03-13 14:51:43 +0530 | [diff] [blame] | 105 | & (sle.is_cancelled == 0) |
| 106 | ) |
| 107 | .where(timestamp_condition) |
| 108 | .groupby(sle.batch_no) |
Rohit Waghchaure | c2d7461 | 2023-03-29 11:40:36 +0530 | [diff] [blame] | 109 | ) |
| 110 | |
| 111 | if self.sle.name: |
| 112 | query = query.where(sle.name != self.sle.name) |
| 113 | |
| 114 | return query.run(as_dict=True) |
Rohit Waghchaure | f704eb7 | 2023-03-30 11:32:39 +0530 | [diff] [blame] | 115 | |
| 116 | @deprecated |
| 117 | def calculate_avg_rate_for_non_batchwise_valuation(self): |
| 118 | if not self.non_batchwise_valuation_batches: |
| 119 | return |
| 120 | |
| 121 | avg_rate = self.get_avg_rate_for_non_batchwise_valuation_batches() |
| 122 | avilable_qty = self.get_available_qty_for_non_batchwise_valuation_batches() |
| 123 | |
| 124 | for batch_no in self.non_batchwise_valuation_batches: |
| 125 | self.stock_value_differece[batch_no] = avg_rate |
| 126 | self.available_qty[batch_no] = avilable_qty.get(batch_no, 0) |
| 127 | |
| 128 | @deprecated |
| 129 | def get_avg_rate_for_non_batchwise_valuation_batches(self): |
| 130 | stock_value, qty = self.get_balance_value_and_qty_from_sl_entries() |
| 131 | stock_value, qty = self.get_balance_value_and_qty_from_bundle(stock_value, qty) |
| 132 | |
| 133 | return stock_value / qty if qty else 0 |
| 134 | |
| 135 | @deprecated |
| 136 | def get_balance_value_and_qty_from_sl_entries(self): |
| 137 | stock_value_difference = 0.0 |
| 138 | available_qty = 0.0 |
| 139 | |
| 140 | sle = frappe.qb.DocType("Stock Ledger Entry") |
| 141 | batch = frappe.qb.DocType("Batch") |
| 142 | |
| 143 | timestamp_condition = CombineDatetime(sle.posting_date, sle.posting_time) < CombineDatetime( |
| 144 | self.sle.posting_date, self.sle.posting_time |
| 145 | ) |
| 146 | if self.sle.creation: |
| 147 | timestamp_condition |= ( |
| 148 | CombineDatetime(sle.posting_date, sle.posting_time) |
| 149 | == CombineDatetime(self.sle.posting_date, self.sle.posting_time) |
| 150 | ) & (sle.creation < self.sle.creation) |
| 151 | |
| 152 | query = ( |
| 153 | frappe.qb.from_(sle) |
| 154 | .inner_join(batch) |
| 155 | .on(sle.batch_no == batch.name) |
| 156 | .select( |
| 157 | Sum(sle.stock_value_difference).as_("batch_value"), |
| 158 | Sum(sle.actual_qty).as_("batch_qty"), |
| 159 | ) |
| 160 | .where( |
| 161 | (sle.item_code == self.sle.item_code) |
| 162 | & (sle.warehouse == self.sle.warehouse) |
| 163 | & (sle.batch_no.isnotnull()) |
| 164 | & (batch.use_batchwise_valuation == 0) |
| 165 | & (sle.is_cancelled == 0) |
| 166 | ) |
| 167 | .where(timestamp_condition) |
| 168 | ) |
| 169 | |
| 170 | if self.sle.name: |
| 171 | query = query.where(sle.name != self.sle.name) |
| 172 | |
| 173 | for d in query.run(as_dict=True): |
| 174 | stock_value_difference += flt(d.batch_value) |
| 175 | available_qty += flt(d.batch_qty) |
| 176 | |
| 177 | return stock_value_difference, available_qty |
| 178 | |
| 179 | @deprecated |
| 180 | def get_balance_value_and_qty_from_bundle(self, stock_value, qty): |
| 181 | bundle = frappe.qb.DocType("Serial and Batch Bundle") |
| 182 | bundle_child = frappe.qb.DocType("Serial and Batch Entry") |
| 183 | batch = frappe.qb.DocType("Batch") |
| 184 | |
| 185 | timestamp_condition = CombineDatetime( |
| 186 | bundle.posting_date, bundle.posting_time |
| 187 | ) < CombineDatetime(self.sle.posting_date, self.sle.posting_time) |
| 188 | |
| 189 | if self.sle.creation: |
| 190 | timestamp_condition |= ( |
| 191 | CombineDatetime(bundle.posting_date, bundle.posting_time) |
| 192 | == CombineDatetime(self.sle.posting_date, self.sle.posting_time) |
| 193 | ) & (bundle.creation < self.sle.creation) |
| 194 | |
| 195 | query = ( |
| 196 | frappe.qb.from_(bundle) |
| 197 | .inner_join(bundle_child) |
| 198 | .on(bundle.name == bundle_child.parent) |
| 199 | .inner_join(batch) |
| 200 | .on(bundle_child.batch_no == batch.name) |
| 201 | .select( |
| 202 | Sum(bundle_child.stock_value_difference).as_("batch_value"), |
| 203 | Sum(bundle_child.qty).as_("batch_qty"), |
| 204 | ) |
| 205 | .where( |
| 206 | (bundle.item_code == self.sle.item_code) |
| 207 | & (bundle.warehouse == self.sle.warehouse) |
| 208 | & (bundle_child.batch_no.isnotnull()) |
| 209 | & (batch.use_batchwise_valuation == 0) |
| 210 | & (bundle.is_cancelled == 0) |
Rohit Waghchaure | f8bf4aa | 2023-04-02 13:13:42 +0530 | [diff] [blame] | 211 | & (bundle.type_of_transaction.isin(["Inward", "Outward"])) |
Rohit Waghchaure | f704eb7 | 2023-03-30 11:32:39 +0530 | [diff] [blame] | 212 | ) |
| 213 | .where(timestamp_condition) |
| 214 | ) |
| 215 | |
| 216 | if self.sle.serial_and_batch_bundle: |
| 217 | query = query.where(bundle.name != self.sle.serial_and_batch_bundle) |
| 218 | |
| 219 | for d in query.run(as_dict=True): |
| 220 | stock_value += flt(d.batch_value) |
| 221 | qty += flt(d.batch_qty) |
| 222 | |
| 223 | return stock_value, qty |
| 224 | |
| 225 | @deprecated |
| 226 | def get_available_qty_for_non_batchwise_valuation_batches(self): |
| 227 | available_qty = defaultdict(float) |
| 228 | self.set_available_qty_for_non_batchwise_valuation_batches_from_sle(available_qty) |
| 229 | self.set_available_qty_for_non_batchwise_valuation_batches_from_bundle(available_qty) |
| 230 | |
| 231 | return available_qty |
| 232 | |
| 233 | @deprecated |
| 234 | def set_available_qty_for_non_batchwise_valuation_batches_from_sle(self, available_qty): |
| 235 | sle = frappe.qb.DocType("Stock Ledger Entry") |
| 236 | |
| 237 | timestamp_condition = CombineDatetime(sle.posting_date, sle.posting_time) < CombineDatetime( |
| 238 | self.sle.posting_date, self.sle.posting_time |
| 239 | ) |
| 240 | if self.sle.creation: |
| 241 | timestamp_condition |= ( |
| 242 | CombineDatetime(sle.posting_date, sle.posting_time) |
| 243 | == CombineDatetime(self.sle.posting_date, self.sle.posting_time) |
| 244 | ) & (sle.creation < self.sle.creation) |
| 245 | |
| 246 | query = ( |
| 247 | frappe.qb.from_(sle) |
| 248 | .select( |
| 249 | sle.batch_no, |
| 250 | Sum(sle.actual_qty).as_("batch_qty"), |
| 251 | ) |
| 252 | .where( |
| 253 | (sle.item_code == self.sle.item_code) |
| 254 | & (sle.warehouse == self.sle.warehouse) |
| 255 | & (sle.batch_no.isin(self.non_batchwise_valuation_batches)) |
| 256 | & (sle.is_cancelled == 0) |
| 257 | ) |
| 258 | .where(timestamp_condition) |
| 259 | .groupby(sle.batch_no) |
| 260 | ) |
| 261 | |
| 262 | if self.sle.name: |
| 263 | query = query.where(sle.name != self.sle.name) |
| 264 | |
| 265 | for d in query.run(as_dict=True): |
| 266 | available_qty[d.batch_no] += flt(d.batch_qty) |
| 267 | |
| 268 | @deprecated |
| 269 | def set_available_qty_for_non_batchwise_valuation_batches_from_bundle(self, available_qty): |
| 270 | bundle = frappe.qb.DocType("Serial and Batch Bundle") |
| 271 | bundle_child = frappe.qb.DocType("Serial and Batch Entry") |
| 272 | |
| 273 | timestamp_condition = CombineDatetime( |
| 274 | bundle.posting_date, bundle.posting_time |
| 275 | ) < CombineDatetime(self.sle.posting_date, self.sle.posting_time) |
| 276 | |
| 277 | if self.sle.creation: |
| 278 | timestamp_condition |= ( |
| 279 | CombineDatetime(bundle.posting_date, bundle.posting_time) |
| 280 | == CombineDatetime(self.sle.posting_date, self.sle.posting_time) |
| 281 | ) & (bundle.creation < self.sle.creation) |
| 282 | |
| 283 | query = ( |
| 284 | frappe.qb.from_(bundle) |
| 285 | .inner_join(bundle_child) |
| 286 | .on(bundle.name == bundle_child.parent) |
| 287 | .select( |
| 288 | bundle_child.batch_no, |
| 289 | Sum(bundle_child.qty).as_("batch_qty"), |
| 290 | ) |
| 291 | .where( |
| 292 | (bundle.item_code == self.sle.item_code) |
| 293 | & (bundle.warehouse == self.sle.warehouse) |
| 294 | & (bundle_child.batch_no.isin(self.non_batchwise_valuation_batches)) |
| 295 | & (bundle.is_cancelled == 0) |
| 296 | ) |
| 297 | .where(timestamp_condition) |
| 298 | .groupby(bundle_child.batch_no) |
| 299 | ) |
| 300 | |
| 301 | if self.sle.serial_and_batch_bundle: |
| 302 | query = query.where(bundle.name != self.sle.serial_and_batch_bundle) |
| 303 | |
| 304 | for d in query.run(as_dict=True): |
| 305 | available_qty[d.batch_no] += flt(d.batch_qty) |