blob: 2f1270e958aaa31b7d59db11de35287451c494e6 [file] [log] [blame]
Rohit Waghchauree6143ab2023-03-13 14:51:43 +05301import frappe
2from frappe.query_builder.functions import CombineDatetime, Sum
3from frappe.utils import flt
Rohit Waghchaurec1132d12023-03-24 10:14:09 +05304from frappe.utils.deprecations import deprecated
Rohit Waghchauref4cfc582023-04-11 13:22:15 +05305from pypika import Order
Rohit Waghchauree6143ab2023-03-13 14:51:43 +05306
7
8class DeprecatedSerialNoValuation:
Rohit Waghchaurec1132d12023-03-24 10:14:09 +05309 @deprecated
Rohit Waghchauree6143ab2023-03-13 14:51:43 +053010 def calculate_stock_value_from_deprecarated_ledgers(self):
11 serial_nos = list(
12 filter(lambda x: x not in self.serial_no_incoming_rate and x, self.get_serial_nos())
13 )
14
15 actual_qty = flt(self.sle.actual_qty)
16
17 stock_value_change = 0
18 if actual_qty < 0:
Rohit Waghchauree6143ab2023-03-13 14:51:43 +053019 if not self.sle.is_cancelled:
20 outgoing_value = self.get_incoming_value_for_serial_nos(serial_nos)
21 stock_value_change = -1 * outgoing_value
Rohit Waghchauree6143ab2023-03-13 14:51:43 +053022
23 self.stock_value_change += stock_value_change
24
Rohit Waghchaurec1132d12023-03-24 10:14:09 +053025 @deprecated
Rohit Waghchauree6143ab2023-03-13 14:51:43 +053026 def get_incoming_value_for_serial_nos(self, serial_nos):
27 # get rate from serial nos within same company
28 all_serial_nos = frappe.get_all(
29 "Serial No", fields=["purchase_rate", "name", "company"], filters={"name": ("in", serial_nos)}
30 )
31
32 incoming_values = 0.0
33 for d in all_serial_nos:
34 if d.company == self.sle.company:
Rohit Waghchaured3ceb072023-03-31 09:03:54 +053035 self.serial_no_incoming_rate[d.name] += flt(d.purchase_rate)
Rohit Waghchauree6143ab2023-03-13 14:51:43 +053036 incoming_values += flt(d.purchase_rate)
37
38 # Get rate for serial nos which has been transferred to other company
39 invalid_serial_nos = [d.name for d in all_serial_nos if d.company != self.sle.company]
40 for serial_no in invalid_serial_nos:
Rohit Waghchauref4cfc582023-04-11 13:22:15 +053041 table = frappe.qb.DocType("Stock Ledger Entry")
42 incoming_rate = (
43 frappe.qb.from_(table)
44 .select(table.incoming_rate)
45 .where(
46 (
47 (table.serial_no == serial_no)
48 | (table.serial_no.like(serial_no + "\n%"))
49 | (table.serial_no.like("%\n" + serial_no))
50 | (table.serial_no.like("%\n" + serial_no + "\n%"))
Rohit Waghchauree6143ab2023-03-13 14:51:43 +053051 )
Rohit Waghchauref4cfc582023-04-11 13:22:15 +053052 & (table.company == self.sle.company)
53 & (table.serial_and_batch_bundle.isnull())
54 & (table.actual_qty > 0)
55 & (table.is_cancelled == 0)
56 )
57 .orderby(table.posting_date, order=Order.desc)
58 .limit(1)
59 ).run()
Rohit Waghchauree6143ab2023-03-13 14:51:43 +053060
Rohit Waghchaured3ceb072023-03-31 09:03:54 +053061 self.serial_no_incoming_rate[serial_no] += flt(incoming_rate[0][0]) if incoming_rate else 0
Rohit Waghchauree6143ab2023-03-13 14:51:43 +053062 incoming_values += self.serial_no_incoming_rate[serial_no]
63
64 return incoming_values
65
66
67class DeprecatedBatchNoValuation:
Rohit Waghchaurec1132d12023-03-24 10:14:09 +053068 @deprecated
Rohit Waghchauree6143ab2023-03-13 14:51:43 +053069 def calculate_avg_rate_from_deprecarated_ledgers(self):
Rohit Waghchaure5bb31732023-03-21 10:54:41 +053070 entries = self.get_sle_for_batches()
71 for ledger in entries:
Rohit Waghchauref704eb72023-03-30 11:32:39 +053072 self.stock_value_differece[ledger.batch_no] += flt(ledger.batch_value)
Rohit Waghchaure86da3062023-03-20 14:15:34 +053073 self.available_qty[ledger.batch_no] += flt(ledger.batch_qty)
Rohit Waghchauree6143ab2023-03-13 14:51:43 +053074
Rohit Waghchaurec1132d12023-03-24 10:14:09 +053075 @deprecated
Rohit Waghchauree6143ab2023-03-13 14:51:43 +053076 def get_sle_for_batches(self):
Rohit Waghchauref704eb72023-03-30 11:32:39 +053077 if not self.batchwise_valuation_batches:
78 return []
79
Rohit Waghchauree6143ab2023-03-13 14:51:43 +053080 sle = frappe.qb.DocType("Stock Ledger Entry")
81
82 timestamp_condition = CombineDatetime(sle.posting_date, sle.posting_time) < CombineDatetime(
83 self.sle.posting_date, self.sle.posting_time
84 )
85 if self.sle.creation:
86 timestamp_condition |= (
87 CombineDatetime(sle.posting_date, sle.posting_time)
88 == CombineDatetime(self.sle.posting_date, self.sle.posting_time)
89 ) & (sle.creation < self.sle.creation)
90
Rohit Waghchaurec2d74612023-03-29 11:40:36 +053091 query = (
Rohit Waghchauree6143ab2023-03-13 14:51:43 +053092 frappe.qb.from_(sle)
93 .select(
94 sle.batch_no,
95 Sum(sle.stock_value_difference).as_("batch_value"),
96 Sum(sle.actual_qty).as_("batch_qty"),
97 )
98 .where(
99 (sle.item_code == self.sle.item_code)
Rohit Waghchauree6143ab2023-03-13 14:51:43 +0530100 & (sle.warehouse == self.sle.warehouse)
Rohit Waghchauref704eb72023-03-30 11:32:39 +0530101 & (sle.batch_no.isin(self.batchwise_valuation_batches))
102 & (sle.batch_no.isnotnull())
Rohit Waghchauree6143ab2023-03-13 14:51:43 +0530103 & (sle.is_cancelled == 0)
104 )
105 .where(timestamp_condition)
106 .groupby(sle.batch_no)
Rohit Waghchaurec2d74612023-03-29 11:40:36 +0530107 )
108
109 if self.sle.name:
110 query = query.where(sle.name != self.sle.name)
111
112 return query.run(as_dict=True)
Rohit Waghchauref704eb72023-03-30 11:32:39 +0530113
114 @deprecated
115 def calculate_avg_rate_for_non_batchwise_valuation(self):
116 if not self.non_batchwise_valuation_batches:
117 return
118
Rohit Waghchaure40ab3bd2023-06-01 16:08:49 +0530119 self.non_batchwise_balance_value = 0.0
120 self.non_batchwise_balance_qty = 0.0
Rohit Waghchauref704eb72023-03-30 11:32:39 +0530121
Rohit Waghchaure40ab3bd2023-06-01 16:08:49 +0530122 self.set_balance_value_for_non_batchwise_valuation_batches()
123
124 for batch_no, ledger in self.batch_nos.items():
125 if batch_no not in self.non_batchwise_valuation_batches:
126 continue
127
Rohit Waghchauref968f0f2023-06-14 23:22:22 +0530128 if not self.non_batchwise_balance_qty:
129 continue
130
Rohit Waghchaure40ab3bd2023-06-01 16:08:49 +0530131 self.batch_avg_rate[batch_no] = (
132 self.non_batchwise_balance_value / self.non_batchwise_balance_qty
133 )
134
135 stock_value_change = self.batch_avg_rate[batch_no] * ledger.qty
136 self.stock_value_change += stock_value_change
137
138 frappe.db.set_value(
139 "Serial and Batch Entry",
140 ledger.name,
141 {
142 "stock_value_difference": stock_value_change,
143 "incoming_rate": self.batch_avg_rate[batch_no],
144 },
145 )
Rohit Waghchauref704eb72023-03-30 11:32:39 +0530146
147 @deprecated
Rohit Waghchaure40ab3bd2023-06-01 16:08:49 +0530148 def set_balance_value_for_non_batchwise_valuation_batches(self):
149 self.set_balance_value_from_sl_entries()
150 self.set_balance_value_from_bundle()
Rohit Waghchauref704eb72023-03-30 11:32:39 +0530151
152 @deprecated
Rohit Waghchaure40ab3bd2023-06-01 16:08:49 +0530153 def set_balance_value_from_sl_entries(self) -> None:
Rohit Waghchauref704eb72023-03-30 11:32:39 +0530154 sle = frappe.qb.DocType("Stock Ledger Entry")
155 batch = frappe.qb.DocType("Batch")
156
157 timestamp_condition = CombineDatetime(sle.posting_date, sle.posting_time) < CombineDatetime(
158 self.sle.posting_date, self.sle.posting_time
159 )
160 if self.sle.creation:
161 timestamp_condition |= (
162 CombineDatetime(sle.posting_date, sle.posting_time)
163 == CombineDatetime(self.sle.posting_date, self.sle.posting_time)
164 ) & (sle.creation < self.sle.creation)
165
166 query = (
167 frappe.qb.from_(sle)
168 .inner_join(batch)
169 .on(sle.batch_no == batch.name)
170 .select(
Rohit Waghchaure40ab3bd2023-06-01 16:08:49 +0530171 sle.batch_no,
Rohit Waghchauref704eb72023-03-30 11:32:39 +0530172 Sum(sle.actual_qty).as_("batch_qty"),
Rohit Waghchaure40ab3bd2023-06-01 16:08:49 +0530173 Sum(sle.stock_value_difference).as_("batch_value"),
Rohit Waghchauref704eb72023-03-30 11:32:39 +0530174 )
175 .where(
176 (sle.item_code == self.sle.item_code)
177 & (sle.warehouse == self.sle.warehouse)
178 & (sle.batch_no.isnotnull())
179 & (batch.use_batchwise_valuation == 0)
180 & (sle.is_cancelled == 0)
181 )
182 .where(timestamp_condition)
Rohit Waghchaure40ab3bd2023-06-01 16:08:49 +0530183 .groupby(sle.batch_no)
Rohit Waghchauref704eb72023-03-30 11:32:39 +0530184 )
185
186 if self.sle.name:
187 query = query.where(sle.name != self.sle.name)
188
189 for d in query.run(as_dict=True):
Rohit Waghchaure40ab3bd2023-06-01 16:08:49 +0530190 self.non_batchwise_balance_value += flt(d.batch_value)
191 self.non_batchwise_balance_qty += flt(d.batch_qty)
192 self.available_qty[d.batch_no] += flt(d.batch_qty)
Rohit Waghchauref704eb72023-03-30 11:32:39 +0530193
194 @deprecated
Rohit Waghchaure40ab3bd2023-06-01 16:08:49 +0530195 def set_balance_value_from_bundle(self) -> None:
Rohit Waghchauref704eb72023-03-30 11:32:39 +0530196 bundle = frappe.qb.DocType("Serial and Batch Bundle")
197 bundle_child = frappe.qb.DocType("Serial and Batch Entry")
198 batch = frappe.qb.DocType("Batch")
199
200 timestamp_condition = CombineDatetime(
201 bundle.posting_date, bundle.posting_time
202 ) < CombineDatetime(self.sle.posting_date, self.sle.posting_time)
203
204 if self.sle.creation:
205 timestamp_condition |= (
206 CombineDatetime(bundle.posting_date, bundle.posting_time)
207 == CombineDatetime(self.sle.posting_date, self.sle.posting_time)
208 ) & (bundle.creation < self.sle.creation)
209
210 query = (
211 frappe.qb.from_(bundle)
212 .inner_join(bundle_child)
213 .on(bundle.name == bundle_child.parent)
214 .inner_join(batch)
215 .on(bundle_child.batch_no == batch.name)
216 .select(
Rohit Waghchaure40ab3bd2023-06-01 16:08:49 +0530217 bundle_child.batch_no,
Rohit Waghchauref704eb72023-03-30 11:32:39 +0530218 Sum(bundle_child.qty).as_("batch_qty"),
Rohit Waghchaure40ab3bd2023-06-01 16:08:49 +0530219 Sum(bundle_child.stock_value_difference).as_("batch_value"),
Rohit Waghchauref704eb72023-03-30 11:32:39 +0530220 )
221 .where(
222 (bundle.item_code == self.sle.item_code)
223 & (bundle.warehouse == self.sle.warehouse)
224 & (bundle_child.batch_no.isnotnull())
225 & (batch.use_batchwise_valuation == 0)
226 & (bundle.is_cancelled == 0)
Rohit Waghchaure40ab3bd2023-06-01 16:08:49 +0530227 & (bundle.docstatus == 1)
Rohit Waghchauref8bf4aa2023-04-02 13:13:42 +0530228 & (bundle.type_of_transaction.isin(["Inward", "Outward"]))
Rohit Waghchauref704eb72023-03-30 11:32:39 +0530229 )
230 .where(timestamp_condition)
Rohit Waghchauref704eb72023-03-30 11:32:39 +0530231 .groupby(bundle_child.batch_no)
232 )
233
234 if self.sle.serial_and_batch_bundle:
235 query = query.where(bundle.name != self.sle.serial_and_batch_bundle)
236
237 for d in query.run(as_dict=True):
Rohit Waghchaure40ab3bd2023-06-01 16:08:49 +0530238 self.non_batchwise_balance_value += flt(d.batch_value)
239 self.non_batchwise_balance_qty += flt(d.batch_qty)
240 self.available_qty[d.batch_no] += flt(d.batch_qty)