-
Notifications
You must be signed in to change notification settings - Fork 0
/
usp_ci_upload_mdc_data_job.sql
332 lines (277 loc) · 9.31 KB
/
usp_ci_upload_mdc_data_job.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
DROP PROCEDURE if exists `usp_ci_upload_mdc_data_job`;
CREATE PROCEDURE `usp_ci_upload_mdc_data_job`(
strPathFileName varchar(500),
int_eperiod int,
int_eyear int
)
BEGIN
declare recordcount integer;
declare uniquerecordcount integer;
drop temporary table if exists temp;
create temporary table temp(
rowid int AUTO_INCREMENT NOT NULL,
eyear varchar(100),
eperiod varchar(100),
mdccode varchar(100),
branchcode varchar(100),
productcode varchar(100),
quantity varchar(100),
clientid varchar (100),
clientcodeid varchar (100),
skuid varchar (100),
skucodeid varchar (100),
customerid varchar(100),
channelid varchar(100),
distributorid varchar(100),
PRIMARY KEY (`rowid`));
insert into temp (mdccode, branchcode, productcode, quantity ,eyear, eperiod)
select mdccode, concat(branchcode,'-',branchcode),
concat(productcode,'-','MDC'), quantity, 2018, 3 from tbl_upload_mdc_data;
/*truncate table tbl_upload_mdc_data;
select * from tbl_upload_mdc_data;*/
delete from temp
where mdccode in ('1','3','4','5') or quantity = 'QUANTITY';
update temp a
left join clientcodes b on a.branchcode = b.clientcode
left join clientcodesdefn c on b.clientcodeid = c.clientcodeid
left join clients d on c.clientid = d.clientid
set a.clientid = d.clientid, a.clientcodeid = b.clientcodeid;
update temp a
left join skucodes b on a.productcode = b.skucode
left join skucodesdefn c on b.skucodeid = c.skucodeid
left join skus d on c.skuid = d.skuid
set a.skuid = d.skuid, a.skucodeid = c.skucodeid;
update temp a
left join clients b on a.clientid = b.clientid
left join datasourceoffilesclientdefn c on b.clientid = c.clientid
left join datasourceoffiles d on c.datasourceoffilesid = d.datasourceoffilesid
set a.distributorid = d.datasourceoffilesid;
update temp a
left join clients b ON a.clientid = b.clientid
left join channels c on b.channel = c.channelname
set a.channelid = c.channelid;
update temp a
left join clients b on a.clientid = b.clientid
left join customers c on b.customerid = c.customerid
set a.customerid = c.customerid;
/*
select * from transferdata where clientid = 73484 and skuid = 256
select * from skuhistoricalprice
select * from clientcodesdefn where clientcodeid = 84602
select * from clientcodes where clientcode = 1108
select * from clients where icc = 1108
select * from temp where clientid = 73484
select * from customercodes;
select * from clientcodes where clientcodeid = 84602;
select * from skucodes where skucodeid = 48037
select * from customers;*/
drop temporary table if exists temp_validation;
create temporary table temp_validation(
rowid int,
eyear varchar(100) default '',
eperiod varchar(100) default '',
mdccode varchar(100) default '',
branchcode varchar(100) default '',
productcode varchar(100) default '',
quantity varchar(100) default '',
errordescription varchar(100) default '',
clientid varchar (100),
clientcodeid varchar (100),
skuid varchar (100),
skucodeid varchar (100),
customerid varchar(100),
channelid varchar(100),
distributorid varchar(100)
);
insert into temp_validation(rowid, mdccode, branchcode, productcode, quantity, eyear, eperiod,clientid, clientcodeid,skuid, skucodeid,customerid, channelid, distributorid )
select rowid, mdccode, branchcode, productcode, quantity, eyear, eperiod,clientid, clientcodeid,skuid, skucodeid,customerid, channelid, distributorid from temp;
update temp_validation
set mdccode = replace(mdccode, '"', '');
update temp_validation
set branchcode = replace(branchcode, '"', '');
update temp_validation
set productcode = replace(productcode, '"', '');
update temp_validation
set quantity = replace(quantity, '"', '');
drop temporary table if exists tmpReturn;
create temporary table tmpReturn
(
ireturn1 int default 0,
ireturn2 int default 0,
ireturn3 int default 0,
ireturn4 int default 0,
ireturn5 int default 0,
ireturn6 int default 0,
ireturn7 int default 0,
ireturn8 int default 0,
ireturn10 int default 0,
rowid varchar(100) default '',
eyear varchar(100) default '',
eperiod varchar(100) default '',
mdccode varchar(100) default '',
branchcode varchar(100) default '',
productcode varchar(100) default '',
quantity varchar(100) default '',
errordescription varchar(100) default '',
clientid varchar (100) default '',
clientcodeid varchar (100) default '',
skuid varchar (100) default '',
skucodeid varchar (100) default '',
customerid varchar(100) default '',
channelid varchar(100) default '',
distributorid varchar(100) default ''
);
if exists(select 1 from temp where branchcode like '%,%') then
insert into tmpReturn (ireturn1)
values (-1);
end if;
if exists(select * from temp_validation where branchcode = "-") then
insert into tmpReturn(ireturn2, rowid, branchcode, errordescription)
select distinct -2, rowid, branchcode, 'Branch Code does not exist' from temp_validation where branchcode = "-";
end if;
if exists(select * from temp_validation where productcode = "-MDC") then
insert into tmpReturn(ireturn3, rowid, productcode, errordescription)
select distinct -3, rowid, productcode, 'Product Code does not exist' from temp_validation where productcode = "-MDC";
end if;
if exists(select * from temp_validation where quantity = "" and quantity is null) then
insert into tmpReturn(ireturn4, rowid, quantity, errordescription)
select distinct -4, rowid, quantity, 'Quantity does not exist' from temp_validation where quantity = "" and quantity is null;
end if;
if exists(select * from clientcodes a right join temp_validation b on a.clientcode = b.branchcode
where a.clientcode is null and b.branchcode <> "") then
insert into tmpReturn(ireturn5, rowid, branchcode, errordescription)
select distinct -5, rowid , b.branchcode, 'Branch Code not yet enrolled'
from clientcodes a right join temp_validation b on a.clientcode = b.branchcode
where a.clientcode is null and b.branchcode <> "";
end if;
select count(1)
into recordcount
from temp_validation
where branchcode IS NOT NULL and productcode IS NOT NULL;
select count(distinct branchcode, productcode)
into uniquerecordcount
from temp_validation
where branchcode IS NOT NULL and productcode IS NOT NULL;
if recordcount <> uniquerecordcount then
insert into tmpReturn(ireturn6, rowid, branchcode, productcode, errordescription)
select
distinct -6,
rowid, branchcode,productcode, 'Duplicate row'
from temp_validation
where branchname is not null
group by branchname
having count(1) > 1;
end if;
if exists(select * from skucodes a right join temp_validation b on a.skucode = b.productcode
where a.skucode is null and b.productcode <> "") then
insert into tmpReturn(ireturn7, rowid, productcode, errordescription)
select distinct -7, rowid , b.productcode, 'Product Code not yet enrolled'
from skucodes a right join temp_validation b on a.skucode = b.productcode
where a.skucode is null and b.productcode <> "";
end if;
if exists(select * from clientcodes a inner join temp_validation b on a.clientcode = b.branchcode
where a.channelid = "" or a.customerid = "" or a.channelid = 0 or a.customerid = 0) then
insert into tmpReturn(ireturn8, rowid, productcode, errordescription)
select distinct -8, b.rowid, b.branchcode, 'Product code not yet enrolled'
from clientcodes a inner join temp_validation b on a.clientcode = b.branchcode
where a.channelid = "" or a.customerid = "" or a.channelid = 0 or a.customerid = 0;
end if;
truncate table tbl_mdc_errorlist;
if not exists(select * from tbl_mdc_list) then
insert
into tbl_mdc_errorlist(
eyear,
eperiod,
mdccode,
branchcode,
productcode,
quantity,
errordescription,
clientid,
clientcodeid,
skuid,
skucodeid,
customerid,
channelid,
distributorid)
select distinct
eyear,
eperiod,
mdccode,
branchcode,
productcode,
quantity,
errordescription,
clientid,
clientcodeid,
skuid,
skucodeid,
customerid,
channelid,
distributorid
from tmpReturn;
end if;
if not exists (select * from tmpReturn) then
truncate table tbl_mdc_list ;
insert
into tbl_mdc_list
(eyear,
eperiod,
mdccode,
branchcode,
productcode,
quantity,
clientid,
clientcodeid,
skuid,
skucodeid,
customerid,
channelid,
distributorid)
select distinct
eyear,
eperiod,
mdccode,
branchcode,
productcode,
quantity,
clientid,
clientcodeid,
skuid,
skucodeid,
customerid,
channelid,
distributorid
from temp_validation;
insert into tmpReturn (ireturn10)
values (1);
end if;
truncate table tmp_mdc;
INSERT INTO tmp_mdc
select
ireturn1,
ireturn2,
ireturn3,
ireturn4,
ireturn5,
ireturn6,
ireturn7,
ireturn8,
ireturn10,
rowid,
eyear,
eperiod,
mdccode,
branchcode,
productcode,
quantity,
errordescription,
clientid,
clientcodeid ,
skuid,
skucodeid,
customerid,
channelid,
distributorid
from tmpReturn;
end;