· 5 years ago · Aug 27, 2020, 03:04 AM
1CREATE DEFINER=`dsDBus3r`@`%` PROCEDURE `dbo_ds`.`sp_penjualan`(
2IN
3AgentID VARCHAR(255),
4date_start VARCHAR(255),
5date_end VARCHAR(255),
6Status VARCHAR(255),
7ProductID VARCHAR(255),
8ProductAgentID VARCHAR(255),
9StoreID VARCHAR(255),
10SalesmanID VARCHAR(255),
11Persistance VARCHAR(255),
12CategoryID VARCHAR(255),
13BrandID VARCHAR(255)
14)
15BEGIN
16
17 SET @persistance = Persistance;
18 SET @ordtl = CONCAT("ordtl", @persistance);
19 SET @dodtl = CONCAT("dodtl", @persistance);
20 SET @clctl = CONCAT("clctl", @persistance);
21 SET @q11 = CONCAT("q11", @persistance);
22 SET @storetl = CONCAT("store", @persistance);
23 SET @storesp = "";
24
25
26
27SET @sql = CONCAT("DROP TEMPORARY TABLE IF EXISTS ", @clctl);
28CALL dbo_ds.exec_query(@sql);
29SET @sql = CONCAT("DROP TEMPORARY TABLE IF EXISTS ", @storetl);
30CALL dbo_ds.exec_query(@sql);
31SET @sql = CONCAT("DROP TEMPORARY TABLE IF EXISTS ", @q11);
32CALL dbo_ds.exec_query(@sql);
33SET @sql = CONCAT("DROP TEMPORARY TABLE IF EXISTS ", @dodtl);
34CALL dbo_ds.exec_query(@sql);
35
36
37 SET @agentparam1 = "";
38 SET @agentparam2 = "";
39 SET @agentparam3 = "";
40 IF (AgentID <> "-") THEN
41 SET @agentparam1 = CONCAT(" AND t1.agent_id in (", AgentID, ")");
42 SET @agentparam2 = CONCAT(" AND do.agent_id in (", AgentID, ")");
43 SET @agentparam3 = CONCAT(" AND o2.AgentID in (", AgentID, ")");
44 END IF;
45
46
47
48 SET @storeparam1 = "";
49 SET @storeparam2 = "";
50 SET @storeparam3 = "";
51 IF (StoreID <> "-") THEN
52 SET @storesp = CONCAT("CREATE TEMPORARY TABLE ", @storetl, " select store_id from dbo_db_prod.mapping_store WHERE FIND_IN_SET(agent_id, ", agent_id, ") AND FIND_IN_SET(store_id, ", StoreID, ") GROUP BY store_id");
53 SET @storeparam1 = CONCAT(" AND t1.store_id in (SELECT store_id FROM ", @storetl, ")");
54 SET @storeparam2 = CONCAT(" AND orddd.store_id in (SELECT store_id FROM ", @storetl, ")");
55 SET @storeparam3 = CONCAT(" AND or2.store_id in (SELECT store_id FROM ", @storetl, ")");
56 CALL dbo_ds.exec_query(@storesp);
57-- CALL dbo_ds.exec_query(concat('SELECT * FROM ', @storetl));
58-- CALL dbo_ds.exec_query(concat('DROP TEMPORARY TABLE IF EXISTS ', @storetl));
59 END IF;
60
61 SET @slsparam1 = "";
62 SET @slsparam2 = "";
63 SET @slsparam3 = "";
64 IF (SalesmanID <> "-") THEN
65 SET @slsparam1 = CONCAT(" AND t1.salesman_id in (", SalesmanID, ")");
66 SET @slsparam2 = CONCAT(" AND do.salesman_id in (", SalesmanID, ")");
67 SET @slsparam3 = CONCAT(" AND o2.SalesmanID in (", SalesmanID, ")");
68 END IF;
69
70 SET @stsparam1 = "";
71 SET @stsparam2 = "";
72 SET @stsparam3 = "";
73 IF (Status <> "-") THEN
74 SET @stsparam1 = CONCAT(" AND t1.Status in (", Status, ")");
75 SET @stsparam2 = CONCAT(" AND do.salesman_id in (", Status, ")");
76 SET @stsparam3 = CONCAT(" AND o2.Status in (", Status, ")");
77 END IF;
78
79 SET @prdparam1 = "";
80 SET @prdparam2 = "";
81 SET @prdparam3 = "";
82 IF (ProductID <> "-") THEN
83 SET @prdparam1 = CONCAT(" AND t5.id in (", ProductID, ")");
84 SET @prdparam2 = CONCAT(" AND p.id in (", ProductID, ")");
85 SET @prdparam3 = CONCAT(" AND p.id in (", ProductID, ")");
86 END IF;
87
88 SET @prdaparam1 = "";
89 SET @prdaparam2 = "";
90 SET @prdaparam3 = "";
91 IF (ProductAgentID <> "-") THEN
92 SET @prdaparam1 = CONCAT(" AND t4.id in (", ProductAgentID, ")");
93 SET @prdaparam2 = CONCAT(" AND pa.id in (", ProductAgentID, ")");
94 SET @prdaparam3 = CONCAT(" AND pa.id in (", ProductAgentID, ")");
95 END IF;
96
97
98 SET @ctgparam1 = "";
99 SET @ctgparam2 = "";
100 SET @ctgparam3 = "";
101 IF (CategoryID <> "-") THEN
102 SET @ctgparam1 = CONCAT(" AND t7.id in (", CategoryID, ")");
103 SET @ctgparam2 = CONCAT(" AND p.category_id in (", CategoryID, ")");
104 SET @ctgparam3 = CONCAT(" AND o2.CategoryID in (", CategoryID, ")");
105 END IF;
106
107 SET @brnparam1 = "";
108 SET @brnparam2 = "";
109 SET @brnparam3 = "";
110 IF (BrandID <> "-") THEN
111 SET @brnparam1 = CONCAT(" AND t1.brand_id in (", BrandID, ")");
112 SET @brnparam2 = CONCAT(" AND do.brand_id in (", BrandID, ")");
113 SET @brnparam3 = CONCAT(" AND o2.BrandID in (", BrandID, ")");
114 END IF;
115
116-- SET @tmp = CONCAT('CREATE TEMPORARY TABLE ', @ordtl);
117
118 SET @sql = CONCAT('CREATE TEMPORARY TABLE ', @ordtl,
119'
120SELECT
121t1.id as OrderID,
122t1.brand_id as BrandID,
123t1.status as OrderStatus,
124t1.date as OrderDate,
125t1.salesman_id as SalesmanID,
126t1.store_id,
127t1.agent_id as AgentID,
128t2.*,
129CASE
130WHEN t2.unit_type = "small" THEN (t2.qty * t5.nettWeight / 1000 )
131WHEN t2.unit_type = "medium" THEN (t2.qty * t5.nettWeight * t5.smallInMediumAmount / 1000 )
132WHEN t2.unit_type = "big" THEN (t2.qty * t5.nettWeight * t5.smallInMediumAmount * t5.mediumInBigAmount / 1000 )
133END netWeight,
134t5.productName as ProductName,
135t3.name as BrandName,
136t5.id as ProductID,
137t7.id as CategoryID,
138t7.name as CategoryName
139FROM
140dbo_db_prod.orders t1
141INNER JOIN dbo_db_prod.order_detail t2 ON t2.order_id = t1.id
142INNER JOIN dbo_db_prod.brands t3 ON t1.brand_id = t3.id
143INNER JOIN dbo_db_prod.product_agent t4 ON t2.product_id = t4.id
144INNER JOIN dbo_db_prod.products t5 ON t4.master_product_id = t5.id
145INNER JOIN dbo_db_prod.categories t7 ON t5.category_id = t7.id
146WHERE
147isnull( t1.deleted_at )
148AND DATE_FORMAT(t1.date,"%Y-%m-%d") >= "', date_start,'"
149AND DATE_FORMAT(t1.date,"%Y-%m-%d") <= "', date_end,'"',
150@stsparam1,
151@agentparam1,
152@prdparam1,
153@prdaparam1,
154@storeparam1,
155@ctgparam1,
156@slsparam1,
157@brnparam1, ';');
158
159CALL dbo_ds.exec_query(@sql);
160-- SET @sql = CONCAT("SELECT * FROM ", @ordtl);
161-- CALL dbo_ds.exec_query(@sql);
162
163SET @sql = CONCAT('CREATE TEMPORARY TABLE ', @q11,
164 '
165 SELECT
166 tmp1.BrandName as BrandName,
167 tmp1.ProductID as ProductID,
168 tmp1.productName AS ProductName,
169 tmp1.CategoryID as CategoryID,
170 tmp1.CategoryName as CategoryName,
171 SUM(tmp1.subtotal_price) AS SOAmount,
172 SUM(tmp1.netWeight) AS SOWeight
173 FROM
174 ', @ordtl, ' tmp1
175 GROUP BY
176 tmp1.ProductID,
177 tmp1.BrandName');
178
179CALL dbo_ds.exec_query(@sql);
180
181
182SET @sql = CONCAT('CREATE TEMPORARY TABLE ', @dodtl,'
183
184 SELECT
185 do.brand_name as BrandName,
186 c.name as CategoryName,
187 p.id as ProductID,
188 p.productName as ProductName,
189 SUM((dod.qty * od.price_at_time)) AS DOAmount,
190 SUM(
191 CASE
192 WHEN dod.unit_type = "small" THEN (dod.qty * p.nettWeight / 1000 )
193 WHEN dod.unit_type = "medium" THEN (dod.qty * p.nettWeight * p.smallInMediumAmount / 1000 )
194 WHEN dod.unit_type = "big" THEN (dod.qty * p.nettWeight * p.smallInMediumAmount * p.mediumInBigAmount / 1000 )
195 END
196 ) AS DOWeight
197 from dbo_db_prod.delivery_order_detail_view_grid do
198 inner join dbo_db_prod.delivery_order_detail dod on do.id = dod.delivery_order_id
199 inner join dbo_db_prod.order_detail od on od.id = dod.order_detail_id
200 inner join dbo_db_prod.product_agent pa ON pa.id = dod.product_agent_id
201 INNER JOIN dbo_db_prod.products p ON pa.master_product_id = p.id
202 INNER JOIN dbo_db_prod.categories c ON c.id = p.category_id
203 where
204 DATE_FORMAT(do.sj_ref_date,"%Y-%m-%d") >= "', date_start, '"
205 AND DATE_FORMAT(do.sj_ref_date,"%Y-%m-%d") <= "', date_END, '"',
206 @agentparam2,
207 @prdparam2,
208 @prdaparam2,
209 @storeparam2,
210 @ctgparam2,
211 @brnparam2,
212 @slsparam2, '
213 GROUP BY p.id;');
214
215CALL dbo_ds.exec_query(@sql);
216-- SET @sql = CONCAT("SELECT * FROM ", @dodtl);
217-- CALL dbo_ds.exec_query(@sql);
218
219 SET @sql = CONCAT(' CREATE TEMPORARY TABLE ', @clctl,
220 '
221 SELECT
222 f1.BrandName,
223 f1.CategoryName,
224 f1.ProductID,
225 f1.ProductName,
226 sum(f1.CancelAmount) as CancelAmount,
227 sum(f1.CancelWeight) as CancelWeight,
228 sum(f1.CancelQty) as CancelQty
229 FROM(
230 select
231 br.name as BrandName,
232 o2.CategoryName,
233 p.id as ProductID,
234 p.productName as ProductName,
235 CASE
236 WHEN o2.status = "close" AND (o2.residual_qty) > 0 THEN (o2.residual_qty)
237 WHEN o2.status = "canceled" THEN o2.residual_qty
238 ELSE 0
239 END as CancelQty,
240 CASE
241 WHEN o2.status = "close" AND (o2.residual_qty) > 0 THEN (o2.residual_qty * o2.price_at_time)
242 WHEN o2.status = "canceled" THEN (o2.residual_qty * o2.price_at_time)
243 ELSE 0
244 END AS CancelAmount,
245 CASE
246 WHEN o2.status = "close" AND (o2.residual_qty) > 0 THEN
247 CASE
248 WHEN o2.unit_type = "small" THEN (o2.residual_qty * p.nettWeight / 1000 )
249 WHEN o2.unit_type = "medium" THEN (o2.residual_qty * p.nettWeight * p.smallInMediumAmount / 1000 )
250 WHEN o2.unit_type = "big" THEN (o2.residual_qty * p.nettWeight * p.smallInMediumAmount * p.mediumInBigAmount / 1000 )
251 END
252 WHEN o2.status = "canceled" THEN
253 CASE
254 WHEN o2.unit_type = "small" THEN (o2.residual_qty * p.nettWeight / 1000 )
255 WHEN o2.unit_type = "medium" THEN (o2.residual_qty * p.nettWeight * p.smallInMediumAmount / 1000 )
256 WHEN o2.unit_type = "big" THEN (o2.residual_qty * p.nettWeight * p.smallInMediumAmount * p.mediumInBigAmount / 1000 )
257 END
258 ELSE 0
259 END as CancelWeight
260 from ', @ordtl, ' o2
261 inner join dbo_db_prod.product_agent pa on o2.product_id = pa.id
262 inner join dbo_db_prod.products p on pa.master_product_id = p.id
263 INNER JOIN dbo_db_prod.brands br on br.id = o2.BrandID
264 where
265 DATE_FORMAT(o2.OrderDate, "%Y-%m-%d") >= "', date_start, '"
266 AND DATE_FORMAT(o2.OrderDate, "%Y-%m-%d") <= "', date_end, '" ',
267 @agentparam3,
268 @prdparam3,
269 @prdaparam3,
270 @storeparam3,
271 @slsparam3,
272 @ctgparam3,
273 @brnparam3,
274 'AND o2.status in ("canceled", "close")
275 AND o2.status not in ("reject")
276 GROUP BY o2.id, p.id
277 ) f1
278 WHERE
279 f1.CancelQty > 0
280 GROUP BY f1.productID');
281
282-- SELECT @sql;
283 CALL dbo_ds.exec_query(@sql);
284-- SET @sql = CONCAT("SELECT * FROM ", @dodtl);
285-- CALL dbo_ds.exec_query(@sql);
286
287
288SET @sql = CONCAT('
289SELECT
290 um.BrandName,
291 um.CategoryName,
292 um.ProductID,
293 um.ProductName,
294 sum(um.SOAmount) as SOAmount,
295 FORMAT(sum(um.SOWeight),5) as SOWeight,
296 sum(um.DOAmount) as DOAmount,
297 FORMAT(sum(um.DOWeight),5) as DOWeight,
298 sum(um.CancelAmount) as CancelAmount,
299 FORMAT(sum(um.CancelWeight),5) as CancelWeight
300 FROM(
301 SELECT
302 q11.BrandName,
303 q11.CategoryName,
304 q11.ProductID,
305 q11.ProductName,
306 q11.SOAmount,
307 q11.SOWeight,
308 0 as DOAmount,
309 0 as DOWeight,
310 0 as CancelAmount,
311 0 as CancelWeight
312 FROM ', @q11, ' q11
313 UNION ALL
314 SELECT
315 dodd.BrandName,
316 dodd.CategoryName,
317 dodd.ProductID,
318 dodd.ProductName,
319 0 as SOAmount,
320 0 as SOWeight,
321 dodd.DOAmount as DOAmount,
322 dodd.DOWeight as DOWeight,
323 0 as CancelAmount,
324 0 as CancelWeight
325 from ', @dodtl, ' dodd
326 UNION ALL
327 SELECT
328 cancelOrder.BrandName,
329 cancelOrder.CategoryName,
330 cancelOrder.ProductID,
331 cancelOrder.ProductName,
332 0 as SOAmount,
333 0 as SOWeight,
334 0 as DOAmount,
335 0 as DOWeight,
336 cancelOrder.CancelAmount as CancelAmount,
337 cancelOrder.CancelWeight as CancelWeight
338 FROM ', @clctl, ' cancelOrder
339 ) um
340 GROUP BY um.BrandName, um.ProductID');
341
342CALL dbo_ds.exec_query(@sql);
343
344
345
346
347END