blob: 76202ed7b008e2e84d81e78f2f11e60948c8a921 [file] [log] [blame]
Rohit Waghchauref704eb72023-03-30 11:32:39 +05301from collections import defaultdict
2
Rohit Waghchauree6143ab2023-03-13 14:51:43 +05303import frappe
4from frappe.query_builder.functions import CombineDatetime, Sum
5from frappe.utils import flt
Rohit Waghchaurec1132d12023-03-24 10:14:09 +05306from frappe.utils.deprecations import deprecated
Rohit Waghchauref4cfc582023-04-11 13:22:15 +05307from pypika import Order
Rohit Waghchauree6143ab2023-03-13 14:51:43 +05308
9
10class DeprecatedSerialNoValuation:
Rohit Waghchaurec1132d12023-03-24 10:14:09 +053011 @deprecated
Rohit Waghchauree6143ab2023-03-13 14:51:43 +053012 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 Waghchauree6143ab2023-03-13 14:51:43 +053021 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 Waghchauree6143ab2023-03-13 14:51:43 +053024
25 self.stock_value_change += stock_value_change
26
Rohit Waghchaurec1132d12023-03-24 10:14:09 +053027 @deprecated
Rohit Waghchauree6143ab2023-03-13 14:51:43 +053028 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 Waghchaured3ceb072023-03-31 09:03:54 +053037 self.serial_no_incoming_rate[d.name] += flt(d.purchase_rate)
Rohit Waghchauree6143ab2023-03-13 14:51:43 +053038 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 Waghchauref4cfc582023-04-11 13:22:15 +053043 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 Waghchauree6143ab2023-03-13 14:51:43 +053053 )
Rohit Waghchauref4cfc582023-04-11 13:22:15 +053054 & (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 Waghchauree6143ab2023-03-13 14:51:43 +053062
Rohit Waghchaured3ceb072023-03-31 09:03:54 +053063 self.serial_no_incoming_rate[serial_no] += flt(incoming_rate[0][0]) if incoming_rate else 0
Rohit Waghchauree6143ab2023-03-13 14:51:43 +053064 incoming_values += self.serial_no_incoming_rate[serial_no]
65
66 return incoming_values
67
68
69class DeprecatedBatchNoValuation:
Rohit Waghchaurec1132d12023-03-24 10:14:09 +053070 @deprecated
Rohit Waghchauree6143ab2023-03-13 14:51:43 +053071 def calculate_avg_rate_from_deprecarated_ledgers(self):
Rohit Waghchaure5bb31732023-03-21 10:54:41 +053072 entries = self.get_sle_for_batches()
73 for ledger in entries:
Rohit Waghchauref704eb72023-03-30 11:32:39 +053074 self.stock_value_differece[ledger.batch_no] += flt(ledger.batch_value)
Rohit Waghchaure86da3062023-03-20 14:15:34 +053075 self.available_qty[ledger.batch_no] += flt(ledger.batch_qty)
Rohit Waghchauree6143ab2023-03-13 14:51:43 +053076
Rohit Waghchaurec1132d12023-03-24 10:14:09 +053077 @deprecated
Rohit Waghchauree6143ab2023-03-13 14:51:43 +053078 def get_sle_for_batches(self):
Rohit Waghchauref704eb72023-03-30 11:32:39 +053079 if not self.batchwise_valuation_batches:
80 return []
81
Rohit Waghchauree6143ab2023-03-13 14:51:43 +053082 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 Waghchaurec2d74612023-03-29 11:40:36 +053093 query = (
Rohit Waghchauree6143ab2023-03-13 14:51:43 +053094 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 Waghchauree6143ab2023-03-13 14:51:43 +0530102 & (sle.warehouse == self.sle.warehouse)
Rohit Waghchauref704eb72023-03-30 11:32:39 +0530103 & (sle.batch_no.isin(self.batchwise_valuation_batches))
104 & (sle.batch_no.isnotnull())
Rohit Waghchauree6143ab2023-03-13 14:51:43 +0530105 & (sle.is_cancelled == 0)
106 )
107 .where(timestamp_condition)
108 .groupby(sle.batch_no)
Rohit Waghchaurec2d74612023-03-29 11:40:36 +0530109 )
110
111 if self.sle.name:
112 query = query.where(sle.name != self.sle.name)
113
114 return query.run(as_dict=True)
Rohit Waghchauref704eb72023-03-30 11:32:39 +0530115
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 Waghchauref8bf4aa2023-04-02 13:13:42 +0530211 & (bundle.type_of_transaction.isin(["Inward", "Outward"]))
Rohit Waghchauref704eb72023-03-30 11:32:39 +0530212 )
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)