· 5 years ago · Jul 23, 2020, 11:36 AM
1#!/usr/bin/perl
2
3use strict;
4use utf8;
5require DBI;
6
7
8#-----------------------------------------------------------
9#-----------------------------------------------------------
10
11sub createProdselTable($$)
12{
13 my ($dbh, $part) = @_;
14 my $sql;
15 if ($part eq 'create' or $part eq 'all')
16 {
17 $dbh->do('drop table if exists mb_prodSelectLog');
18 $sql = qq{
19 CREATE TABLE `mb_prodSelectLog` (
20 `id` int(11) NOT NULL,
21 `date` date NOT NULL,
22 `userid` varchar(12) NOT NULL,
23 `company` varchar(3) NOT NULL,
24 `article` varchar(64) NOT NULL,
25 `country` int(2) NOT NULL,
26 `source` int(2) NOT NULL
27 ) ENGINE=MyIsam DEFAULT CHARSET=utf8;
28 };
29 $dbh->do($sql);
30 $sql = qq{
31 ALTER TABLE `mb_prodSelectLog`
32 ADD PRIMARY KEY (`id`);
33 };
34 $dbh->do($sql);
35 $sql = qq{
36 ALTER TABLE `mb_prodSelectLog`
37 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
38 };
39 $dbh->do($sql);
40 }
41 if ($part eq 'index' or $part eq 'all')
42 {
43 $sql = qq{
44 ALTER TABLE `mb_prodSelectLog`
45 ADD KEY `date` (`date`),
46 ADD KEY `company` (`company`);
47 };
48 $dbh->do($sql);
49 }
50}
51
52
53#-----------------------------------------------------------
54#-----------------------------------------------------------
55
56sub createUserTempTable($$)
57{
58 my ($dbh, $part) = @_;
59 my $sql;
60 if ($part eq 'create' or $part eq 'all')
61 {
62 $dbh->do('drop table if exists tmp_user');
63 my $sql = qq{
64 CREATE TABLE `tmp_user` (
65 `id` int(11) NOT NULL,
66 `userid` varchar(12),
67 `userid_usm` varchar(42) NOT NULL
68 ) ENGINE=MyIsam DEFAULT CHARSET=utf8
69 };
70 $dbh->do($sql);
71 $sql = qq{
72 ALTER TABLE `tmp_user`
73 ADD PRIMARY KEY (`id`)
74 };
75 $dbh->do($sql);
76 $sql = qq{
77 ALTER TABLE `tmp_user`
78 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1
79 };
80 $dbh->do($sql);
81 }
82 if ($part eq 'index' or $part eq 'all')
83 {
84 $sql = qq{
85 ALTER TABLE `tmp_user`
86 ADD UNIQUE KEY `userid` (`userid`),
87 ADD UNIQUE KEY `userid_usm` (`userid_usm`)
88 };
89 $dbh->do($sql);
90 }
91}
92
93
94#-----------------------------------------------------------
95#-----------------------------------------------------------
96
97sub dropUserTempTable($)
98{
99 my ($dbh) = @_;
100 $dbh->do('drop table tmp_user');
101}
102
103
104#-----------------------------------------------------------
105#-----------------------------------------------------------
106
107sub fillUserTempTableUidabc($)
108{
109 my ($dbh) = @_;
110 my $sql = qq{
111 insert into tmp_user (userid_usm)
112 select userId
113 from usm_userInfo
114 where currentGlobProgId > 17000000 and licnum like '%-UIDABC'
115 order by userId
116 };
117 $dbh->do($sql);
118}
119
120
121#-----------------------------------------------------------
122#-----------------------------------------------------------
123
124sub enumerateUserTempTableUidabc($)
125{
126 my ($dbh) = @_;
127 my $sql = qq{
128 update tmp_user
129 set userid = concat('U-', lpad(id, 6, 0))
130 };
131 $dbh->do($sql);
132}
133
134
135#-----------------------------------------------------------
136#-----------------------------------------------------------
137
138sub fillUserTempTableSuite($)
139{
140 my ($dbh) = @_;
141 my $sql = qq{
142 insert into tmp_user (userid_usm)
143 select userId
144 from usm_userInfo
145 where licnum not like '%UIDABC' and licnum not like 'C-%' and currentGlobProgId < 17000000
146 order by userId
147 };
148 $dbh->do($sql);
149}
150
151
152#-----------------------------------------------------------
153#-----------------------------------------------------------
154
155sub enumerateUserTempTableSuite($)
156{
157 my ($dbh) = @_;
158 my $sql = qq{
159 update tmp_user
160 set userid = concat('S-', lpad(id, 6, 0))
161 };
162 $dbh->do($sql);
163}
164
165
166#-----------------------------------------------------------
167#-----------------------------------------------------------
168
169sub getMemberStrings($$$)
170{
171 my($dbh, $memNormal_ref, $memReass_ref) = @_;
172
173 # get list of members
174 # CAUTION: the memberstring may only contain such members, which are not listed in companyReassignment
175 my $sql = qq{
176 select distinct abbr, companyReassignment.from
177 from companyNameLookup
178 left join companyReassignment on companyNameLookup.abbr = companyReassignment.from
179 where abbr is not null and companyReassignment.from is null
180 order by abbr
181 };
182 my $sth = $dbh->prepare($sql);
183 $sth->execute();
184 my $membersString = '';
185 while (my $ref = $sth->fetchrow_arrayref())
186 {
187 $membersString .= ', ' if $membersString ne '';
188 $membersString .= "'$ref->[0]'";
189 }
190 $sth->finish();
191 #print $membersString, "\n";
192
193 # get list of reassigned members
194 $sql = qq{
195 select distinct companyReassignment.from
196 from companyReassignment
197 order by companyReassignment.from
198 };
199 $sth = $dbh->prepare($sql);
200 $sth->execute();
201 my $membersReassignedString = '';
202 while (my $ref = $sth->fetchrow_arrayref())
203 {
204 $membersReassignedString .= ', ' if $membersReassignedString ne '';
205 $membersReassignedString .= "'$ref->[0]'";
206 }
207 $sth->finish();
208 #print $membersReassignedString, "\n";
209 $$memNormal_ref = $membersString;
210 $$memReass_ref = $membersReassignedString;
211}
212
213
214#-----------------------------------------------------------
215#-----------------------------------------------------------
216
217sub fillProdselTableReluxnet($$$)
218{
219 my($dbh, $membersString, $membersReassignedString) = @_;
220
221 # subset of products which are already connected to a member
222 my $returnValue = 0;
223 my $sql = qq{
224 insert into mb_prodSelectLog (userid, company, article, country, source, date)
225 select licnum, company, article, usm_prodSelectLog.country, source, date
226 from usm_prodSelectLog
227 inner join usm_userInfo on usm_userInfo.userId = usm_prodSelectLog.userId
228 where company in ($membersString) and licnum like 'C-%'
229 };
230 $returnValue = $dbh->do($sql);
231 print '$dbh->do($sql) returned ', $returnValue, "\n";
232
233 # subset of products which are connected via companyReassignment to a member
234 $sql = qq{
235 insert into mb_prodSelectLog (userid, company, article, country, source, date)
236 select licnum, companyReassignment.to, article, usm_prodSelectLog.country, source, date
237 from usm_prodSelectLog
238 inner join usm_userInfo on usm_userInfo.userId = usm_prodSelectLog.userId
239 inner join companyReassignment on companyReassignment.from = usm_prodSelectLog.company
240 where company in ($membersReassignedString) and licnum like 'C-%'
241 };
242 $returnValue = $dbh->do($sql);
243 print '$dbh->do($sql) returned ', $returnValue, "\n";
244
245 # subset of products which are imported and have different brand name in the photometry files
246 $sql = qq{
247 insert into mb_prodSelectLog (userid, company, article, country, source, date)
248 select licnum, companyNameLookup.abbr, article, usm_prodSelectLog.country, source, date
249 from usm_prodSelectLog
250 inner join usm_userInfo on usm_userInfo.userId = usm_prodSelectLog.userId
251 inner join companyNameLookup on usm_prodSelectLog.company = companyNameLookup.company
252 where companyNameLookup.abbr in ($membersString) and source > 3 and licnum like 'C-%'
253 };
254 $returnValue = $dbh->do($sql);
255 print '$dbh->do($sql) returned ', $returnValue, "\n";
256
257 # subset of products which are imported and connected via companyReassignment to a member
258 $sql = qq{
259 insert into mb_prodSelectLog (userid, company, article, country, source, date)
260 select licnum, companyReassignment.to, article, usm_prodSelectLog.country, source, date
261 from usm_prodSelectLog
262 inner join usm_userInfo on usm_userInfo.userId = usm_prodSelectLog.userId
263 inner join companyNameLookup on usm_prodSelectLog.company = companyNameLookup.company
264 inner join companyReassignment on companyReassignment.from = companyNameLookup.company
265 where companyNameLookup.abbr in ($membersReassignedString) and source > 3 and licnum like 'C-%'
266 };
267 $returnValue = $dbh->do($sql);
268 print '$dbh->do($sql) returned ', $returnValue, "\n";
269}
270
271
272#-----------------------------------------------------------
273#-----------------------------------------------------------
274
275sub fillProdselTableOther($$$)
276{
277 my($dbh, $membersString, $membersReassignedString) = @_;
278
279 # subset of products which are already connected to a member
280 my $returnValue = 0;
281 my $sql = qq{
282 insert into mb_prodSelectLog (userid, company, article, country, source, date)
283 select tmp_user.userid, company, article, usm_prodSelectLog.country, source, date
284 from usm_prodSelectLog
285 inner join usm_userInfo on usm_userInfo.userId = usm_prodSelectLog.userId
286 inner join tmp_user on usm_userInfo.userId = tmp_user.userid_usm
287 where company in ($membersString)
288 };
289 $returnValue = $dbh->do($sql);
290 print '$dbh->do($sql) returned ', $returnValue, "\n";
291
292 # subset of products which are connected via companyReassignment to a member
293 $sql = qq{
294 insert into mb_prodSelectLog (userid, company, article, country, source, date)
295 select tmp_user.userid, companyReassignment.to, article, usm_prodSelectLog.country, source, date
296 from usm_prodSelectLog
297 inner join usm_userInfo on usm_userInfo.userId = usm_prodSelectLog.userId
298 inner join tmp_user on usm_userInfo.userId = tmp_user.userid_usm
299 inner join companyReassignment on companyReassignment.from = usm_prodSelectLog.company
300 where company in ($membersReassignedString)
301 };
302 $returnValue = $dbh->do($sql);
303 print '$dbh->do($sql) returned ', $returnValue, "\n";
304
305 # subset of products which are imported and have different brand name in the photometry files
306 $sql = qq{
307 insert into mb_prodSelectLog (userid, company, article, country, source, date)
308 select tmp_user.userid, companyNameLookup.abbr, article, usm_prodSelectLog.country, source, date
309 from usm_prodSelectLog
310 inner join usm_userInfo on usm_userInfo.userId = usm_prodSelectLog.userId
311 inner join tmp_user on usm_userInfo.userId = tmp_user.userid_usm
312 inner join companyNameLookup on usm_prodSelectLog.company = companyNameLookup.company
313 where companyNameLookup.abbr in ($membersString) and source > 3
314 };
315 $returnValue = $dbh->do($sql);
316 print '$dbh->do($sql) returned ', $returnValue, "\n";
317
318 # subset of products which are imported and connected via companyReassignment to a member
319 $sql = qq{
320 insert into mb_prodSelectLog (userid, company, article, country, source, date)
321 select tmp_user.userid, companyReassignment.to, article, usm_prodSelectLog.country, source, date
322 from usm_prodSelectLog
323 inner join usm_userInfo on usm_userInfo.userId = usm_prodSelectLog.userId
324 inner join tmp_user on usm_userInfo.userId = tmp_user.userid_usm
325 inner join companyNameLookup on usm_prodSelectLog.company = companyNameLookup.company
326 inner join companyReassignment on companyReassignment.from = companyNameLookup.company
327 where companyNameLookup.abbr in ($membersReassignedString) and source > 3
328 };
329 $returnValue = $dbh->do($sql);
330 print '$dbh->do($sql) returned ', $returnValue, "\n";
331}
332
333
334#-----------------------------------------------------------
335#-----------------------------------------------------------
336
337sub fillProdselTable($)
338{
339 my($dbh) = @_;
340
341 my $membersString = '';
342 my $membersReassignedString = '';
343 getMemberStrings($dbh, \$membersString, \$membersReassignedString);
344
345 my $start_time;
346 my $elapsed;
347
348 # process entries which have a valid ReluxNet userid
349 $start_time = time;
350 print 'processing ReluxNet users', "\n";
351 fillProdselTableReluxnet($dbh, $membersString, $membersReassignedString);
352 $elapsed = time - $start_time;
353 print "task ended after $elapsed seconds\n";
354
355 # process the fake ReluxNet user with userid like U-UIDABC or T-UIDABC
356 # enumerate them as U-012345
357 $start_time = time;
358 print 'processing UIDABC users', "\n";
359 createUserTempTable($dbh, 'create');
360 fillUserTempTableUidabc($dbh);
361 createUserTempTable($dbh, 'index');
362 enumerateUserTempTableUidabc($dbh);
363 $elapsed = time - $start_time;
364 print "task ended after $elapsed seconds\n";
365 $start_time = time;
366 fillProdselTableOther($dbh, $membersString, $membersReassignedString);
367 $elapsed = time - $start_time;
368 print "task ended after $elapsed seconds\n";
369
370 # process the ReluxSuite users
371 # enumerate them as S-012345
372 $start_time = time;
373 print 'processing Suite users', "\n";
374 createUserTempTable($dbh, 'create');
375 fillUserTempTableSuite($dbh);
376 createUserTempTable($dbh, 'index');
377 enumerateUserTempTableSuite($dbh);
378 $elapsed = time - $start_time;
379 print "task ended after $elapsed seconds\n";
380 $start_time = time;
381 fillProdselTableOther($dbh, $membersString, $membersReassignedString);
382 $elapsed = time - $start_time;
383 print "task ended after $elapsed seconds\n";
384
385 dropUserTempTable($dbh)
386}
387
388
389#-----------------------------------------------------------
390#-----------------------------------------------------------
391
392sub main()
393{
394 # Manolis: Use USM2 for the database.
395 my $dbh = DBI->connect("DBI:mysql:usm2:localhost", 'vaggelis', 'db!@#$%!!!!',{'RaiseError' => 1});
396 createProdselTable($dbh, 'create');
397 fillProdselTable($dbh);
398 createProdselTable($dbh, 'index');
399 $dbh->disconnect();
400}
401
402
403#-----------------------------------------------------------
404#-----------------------------------------------------------
405
406main();