Zb1ALYBQ

1#!/bin/bash
2#
3cd /home/smb/WILLREP
4echo "Starting File Insertion Process..."
5date
6
7#cd SUMMARY
8cd ALL
9
10ls -1atr *-O-*.tsv | while read tsv
11do
12date +%F\|%T
13echo "Converting $tsv to less-satanic format..."
14
15echo "Grokking Data from Original Willow Crapfile..."
16 cat $tsv | grep -vi topaziquote | grep -v DigitalCockpit | grep -v 42337 \
17    |sed -e '1,4d' -e"s/\\\/\//g" -e"s/\t/\|/g" -e's/,/./g' \
18    -e"s/\([0-9][0-9]\)-\([0-9][0-9]\)-\([0-9][0-9][0-9][0-9]\)/\3-\2-\1/g" |tr -d \" > $tsv.temp
19date +%F\|%T
20
21echo "Extracting Quote Data..."
22 cat $tsv.temp |grep -v \|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\| \
23    | cut -f1-143,150-152 -d\|> $tsv.quot
24date +%F\|%T
25
26echo "Extracting Option and Accesory Quote Data..."
27 cat $tsv.temp |grep \|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\| \
28     | cut -f1-2,134- -d\| > $tsv.opt
29date +%F\|%T
30
31echo "Importing Extracted Quote Data..."
32QLINES=`wc -l $tsv.quot| cut -f1 | sed -e 's/ / lines - /g'`
33echo "Importing $tsv.quot ($QLINES)..."
34cp $tsv.quot temp_willow_quotes
35cat temp_willow_quotes | sed -e's/||/|\\N|/g' -e's/||/|\\N|/g' -e's/|$/|\\N/g'> temp_willow_quotes.2
36iconv --from-code=ISO-8859-1 --to-code=UTF-8 ./temp_willow_quotes.2 > temp_willow_quotes.3
37mysqlimport -d --local --fields-terminated-by=\| --user=$DBUSER --password=$DBPASS willreps temp_willow_quotes.3
38date +%F\|%T
39rm temp_willow_quotes temp_willow_quotes.2
40
41echo "Importing Extracted Quote Options and Accesories Data..."
42OLINES=`wc -l $tsv.opt| cut -f1 | sed -e 's/ / lines - /g'`
43echo "Importing $tsv.opt ($OLINES)..."
44cp $tsv.opt temp_willow_extras
45cat temp_willow_extras | sed -e's/||/|\\N|/g' -e's/||/|\\N|/g' -e's/|$/|\\N/g'> temp_willow_extras.2
46iconv --from-code=ISO-8859-1 --to-code=UTF-8 ./temp_willow_extras.2 > temp_willow_extras.3
47mysqlimport -d --local --fields-terminated-by=\| --user=$DBUSER --password=$DBPASS willreps temp_willow_extras.3
48date +%F\|%T
49#rm temp_willow_extras temp_willow_extras.2 temp_willow_extras.3
50rm temp_willow_extras temp_willow_extras.2
51
52echo "[ $tsv ] Cross-referencing with MySQL absolutely no cleanup made..."
53/usr/bin/mysql -u$DBUSER -p$DBPASS willreps < /home/smb/WILLREP/willow_import_O.sql
54echo "[ $tsv ] Data processed, temptables nukes..."
55date +%F\|%T
56
57mkdir done_details 2>/dev/null
58mv $tsv.* done_options/logfiles/.
59# echo "Deleting files: $tsv.*"
60#rm $tsv.*
61echo "Moving: $tsv to done_options/."
62mv $tsv* done_options/.
63done
64
65ls -1atr *-D-*.tsv | while read tsv
66do
67echo "Converting $tsv..."
68
69echo "Filtering Element Data from Original Willow Crapfile..."
70 cat $tsv \
71    |sed -e '1,5d' -e"s/\t/\|/g" -e's/,/./g' |tr -d \" > $tsv.elmts
72
73echo "Importing Extracted Quote Elements Data..."
74DLINES=`wc -l $tsv.elmts| cut -f1`
75echo "Importing $tsv.elmts ($DLINES)..."
76#../phpimport.php localhost $DBUSER $DBPASS willreps temp_willow_elmts `pwd`$tsv.elmts 0 >>$tsv.elmts.log
77cp $tsv.elmts temp_willow_elmts
78cat temp_willow_elmts | sed -e's/||/|\\N|/g' -e's/||/|\\N|/g' -e's/|$/|\\N/g'> temp_willow_elmts.2
79iconv --from-code=ISO-8859-1 --to-code=UTF-8 ./temp_willow_elmts.2 > temp_willow_elmts.3
80mysqlimport -d --local --fields-terminated-by=\| --user=$DBUSER --password=$DBPASS willreps temp_willow_elmts.3
81date +%F\|%T
82#rm temp_willow_elmts temp_willow_elmts.2 temp_willow_elmts.3
83rm temp_willow_elmts temp_willow_elmts.2
84rm temp_willow_elmts temp_willow_elmts.2
85
86echo "[ $tsv ] Cross-referencing with MySQL..."
87/usr/bin/mysql -u$DBUSER -p$DBPASS willreps < /home/smb/WILLREP/willow_import_D.sql
88echo "[ $tsv ] Tables temp_willow_elmts nuked..."
89date +%F\|%T
90
91mkdir done_details 2>/dev/null
92mv $tsv.* done_details/logfiles/.
93#rm $tsv.*
94mv $tsv* done_details/.
95done
96
97# /usr/bin/mysql -u$DBUSER -p$DBPASS VOD_TRACKING < /home/smb/WILLREP/willow_reservas.sql
98# /usr/bin/mysql -u$DBUSER -p$DBPASS VOD_TRACKING_UAT < /home/smb/WILLREP/willow_reservas.sql
99
100cd /home/smb/WILLREP
101echo "Starting File Insertion Process..."
102date
103
104cd SUMMARY_N
105#cd ALL_N
106
107echo "Starting process..."
108mkdir -p done 2>/dev/null
109mkdir -p sqldone 2>/dev/null
110
111ls -1 *QRD*.tsv | \
112while read line
113do
114
115date +%F\|%T
116echo "Processing $line - First pass - Converting to Unicode"
117cat $line | \
118iconv -f ISO-8859-1 -t UTF-8 > $line.sql
119# | grep -vi topaziquote | grep -v DigitalCockpit | grep -v 42337 > $line.sql
120
121echo "Storing original TSV file..."
122mv $line done/.
123mv $line.sql $line.sql.2
124
125echo "Processing $line - First pass - Initial SQL fix"
126cat $line.sql.2| \
127grep -v '\/\*' | grep -v '\*\/'| sed \
128\
129 -e's/DROP TABLE/DROP TABLE IF EXISTS/g' \
130 -e"s|TO_DATE(|str_to_date(|g" \
131 -e"s|DD-MM-RRRR|%d-%m-%Y|g" \
132 -e"s|DD/MM/RRRR|%d/%m/%Y|g" \
133 -e"s|HH24:MI:SS|%T|g" \
134 -e's/VARCHAR2/VARCHAR/g' \
135 -e"s/NUMBER/DECIMAL/g" > $line.sql
136
137# -e's/COMMIT;//g' \
138
139
140echo "Completed fix of $line"
141
142rm $line.sql.2
143
144done
145
146mysql -f -u$DBUSER -p$DBPASS willreps_test < ../willow_import_INIT.sql
147
148ls -1 LIST*-QRD-*.sql | while read line
149do
150
151echo "importing $line"
152mysql -f -u$DBUSER -p$DBPASS willreps_test < "$line"
153mysql -f -u$DBUSER -p$DBPASS willreps_test < ../willow_import_LIST.sql
154mv $line sqldone/.
155
156done
157
158ls -1 COMPONENT*-QRD-*.sql | while read line
159do
160
161echo "importing $line"
162mysql -f -u$DBUSER -p$DBPASS willreps_test < "$line"
163mysql -f -u$DBUSER -p$DBPASS willreps_test < ../willow_import_COMPONENTS.sql
164mv $line sqldone/.
165
166done
167
168ls -1 EXTRA*-QRD-*.sql | while read line
169do
170
171echo "importing $line"
172mysql -f -u$DBUSER -p$DBPASS willreps_test < "$line"
173mysql -f -u$DBUSER -p$DBPASS willreps_test < ../willow_import_EXTRAS.sql
174mv $line sqldone/.
175
176done
177
178mysql -f -u$DBUSER -p$DBPASS willreps_test < ../willow_import_END.sql
179
180DATEHUMAN=`date +%d/%m/%Y`
181DATES=`date +%Y%m`
182DATEL=`date +%Y%m%d`
183TIME=`date +%R`
184MAINDIR="/home/smb"
185FAXDIR="$MAINDIR/fax"
186FAXDONE="$MAINDIR/faxdone"
187
188cd "$FAXDIR"
189
190ls -1d 91* | while read FAXN
191
192do
193
194# echo "Estoy en $FAXDIR -> $FAXN"
195
196cd "$FAXN"
197
198if [ -d "$DATES" ]
199then
200
201# echo "Veo $FAXN/fax -> $DATES"
202
203if [ -e "./filelist"  ]; then
204rm filelist
205fi
206
207pdfcount=`find . -name "*[PDF,pdf]" -print | wc -l`
208
209if [ $pdfcount -gt 0 ]
210then
211
212find . -name "*[PDF,pdf]" -print | while read pdf
213do
214pdf2=`echo $pdf | sed -e's/.PDF/_PDF/g' -e's|\./||g' |tr -d .| sed -e's/_PDF/.PDF/g'`
215# echo $pdf2 | sed -e's|/|-|g' -e"s/$DATES-/$DATEL-/g"
216pdf3=`echo $pdf2 | sed -e's|/|-|g' -e"s/$DATES-/$DATEL-/g"`
217pdf4=`echo $pdf | sed -e's|\./|/|g'`
218echo "$pdf3 $pdf4" >> filelist
219# echo $pdf3
220# echo $pdf4
221done
222
223fi
224
225if [ -e "./filelist"  ]; then
226cat filelist | while read line
227do filename=`echo $line | awk '{ print $1 }'`
228path=`echo $line| awk '{ print $2}'`
229mkdir -p $FAXDONE/$FAXN/$DATEL
230if [ $FAXN -eq "914903677" ]
231then
232        chmod 777 $FAXDONE/$FAXN/$DATEL
233fi
234mv $FAXDIR/$FAXN"$path" $FAXDONE/$FAXN/$DATEL/$filename
235# echo $filename
236SENDER=`echo $filename| cut -f2 -d-`
237# echo $SENDER
238cd /var/www/rf/
239export MAILPARAMS="fax=$FAXN&date=$DATEL&hour=$TIME&path=/$DATEL&file=$filename&sender=$SENDER&filepath=$FAXDONE/$FAXN/$DATEL/$filename&datehuman=$DATEHUMAN"
240# echo "fax=$FAXN
241# date=$DATEL
242# hour=$TIME
243# path=/$DATEL
244# file=$filename
245# sender=$SENDER
246# filepath=$FAXDONE/$FAXN/$DATEL/$filename
247# datehuman=$DATEHUMAN"
248
249# echo "Testing PHP script"
250# echo $MAILPARAMS
251# echo "/usr/bin/php -f /var/www/rf/mail.php"
252/usr/bin/php -f /var/www/rf/mail.php
253done
254rm ./filelist
255fi
256
257fi
258
259cd ..
260
261done
262
263export CD1="/home/ftp/pricing"
264export CD2="/opt/backup/pricing"
265
266cd $CD1
267
268DATE=`date +%Y%m%d`
269DATES=`date +%Y%m%d%H%M%S`
270DB=phx_data
271export FILEDIR=vhc_es_prc_$DATE
272export FILE=$FILEDIR.tar.zip
273export OKFILE=PHX_TRANSFER_$DATE.OK
274
275# echo $OKFILE
276
277if [ -e $OKFILE ]
278then
279rm $OKFILE
280
281mkdir $DATE
282mv *.in.$DATE.gz $DATE/.
283mv *.out.$DATE.gz $DATE/.
284mv *.dat.$DATE.gz $DATE/.
285
286cd $DATE
287
288time ls -1 *.in.$DATE.gz | while read file
289do
290echo "  --- Processing table creation from $file... ..."
291TABLE=`echo $file | cut -f1 -d\.`
292echo "Removing table $TABLE"
293echo "DROP TABLE $TABLE;" | mysql -u$DBUSER -p$DBPASS phx_data_2
294echo "Creating new table from $file..."
295zcat $file | \
296sed -n -e '/create table/,/with /p' | \
297sed -e'/with noduplicates/d' -e'/with duplicates/d' \
298 -e "s/ not null default ' '//g" \
299 -e 's/ not null default 0//g' \
300 -e 's/ not null not default//g' \
301 -e's/ char(/ varchar(/g' \
302 -e's/_dat_id integer/_dat_id date/g' \
303 -e's/_dat integer/_dat date/g' \
304 -e's/ integer/ int(11)/g' \
305 -e's/ i1/ smallint(6)/g' \
306 -e's/ f4/ decimal(10,4)/g' \
307 -e's/ float/ decimal(10,4)/g' \
308 | mysql -u$DBUSER -p$DBPASS phx_data_2
309
310echo "Adding indices from $file..."
311zcat $file | \
312sed -n -e '/create index/,/with struct/p'| \
313sed -e's/with structure.*/;/g' \
314 | mysql -u$DBUSER -p$DBPASS phx_data_2
315echo "  --- Done table creation from $file... ..."
316done
317
318time ls -1 *.dat.$DATE.gz | while read file
319do
320echo "  --- Importing $file... ---
321"
322TABLE=`echo "$file"| cut -f1 -d.`
323echo "Importing $file into $TABLE...
324"
325zcat $file| iconv  --from-code=ISO-8859-1  --to-code=UTF-8 | tr -s ' ' |\
326 sed -e's/\t /\t/g' -e's/ \t/\t/g' -e's/ $//g' -e's/^ //g' |\
327 sed -e 's/\([[:space:]]\)\([[:digit:]]\{4\}\)\([[:digit:]]\{2\}\)\([[:space:]]\)\([[:digit:]]\{2\}\)/\1\2-\3-\4\5/g' \
328 > $TABLE
329
330echo "TRUNCATE TABLE $TABLE;" | mysql -u$DBUSER -p$DBPASS phx_data_2
331
332 filelist=filelist$$
333        split -500 $TABLE $TABLE
334        ls $TABLE?? > $filelist
335        cat $filelist | while read line
336        do
337
338echo "Processing Sub File $line..."
339
340# mysqlimport -d --local --fields-terminated-by='\t' --user=$DBUSER --password=$DBPASS phx_data_2 $TABLE
341mv $TABLE $TABLE.tempfile
342mv $line $TABLE
343#mysqlimport -d --local --fields-terminated-by='\t' --user=$DBUSER --password=$DBPASS phx_data_2 $line
344
345echo "Importing $TABLE $line"
346mysqlimport -rf --local --fields-terminated-by='\t' --user=$DBUSER --password=$DBPASS "phx_data_2" "$TABLE"
347
348echo "SHOW WARNINGS;" | mysql -u$DBUSER -p$DBPASS > $TABLE.err
349#rm $line
350mv $TABLE.tempfile $TABLE
351done
352rm $TABLE
353echo "  --- Done importing $file... ---
354"
355done
356
357fi
358
359export CD1="/home/ftp/vhc_pnd"
360export CD2="/opt/backup/pricing"
361
362cd $CD1
363
364DATE=`date +%Y%m%d`
365DATES=`date +%Y%m%d%H%M%S`
366DB=phx_data
367export FILEDIR=vhc_es_prc_$DATE
368export FILE=$FILEDIR.tar.zip
369export OKFILE=$FILE.ok
370
371if [ -e $OKFILE ]
372then
373rm $OKFILE
374mkdir $CD2/prc_$DATE 2>/dev/null
375cd $CD2/prc_$DATE
376# cp ../$FILE ./.
377mv $CD1/$FILE ./.
378tar zxvf $FILE >/dev/null
379echo "Making Dir csv_$DATE"
380mkdir csv_$DATE 2>/dev/null
381pwd
382cd csv_$DATE
383pwd
384mv ../$FILEDIR/*csv .
385
386#/usr/bin/mysql -u$DBUSER -p$DBPASS VOD_TEST < ../../VOD_TABLES_1
387# rm -rf done_$DATE
388mkdir done_$DATES 2>/dev/null
389mv done_$DATE done_$DATES 2>/dev/null
390mkdir done_$DATE 2>/dev/null
391
392mv sq_vhei.csv sls_qttn_vhc_extr_item.csv
393mv visl.csv vhc_invt_srce_link.csv
394mv eaccl.csv eltc_adrs_cstm_cnct_link.csv
395mv passwd.csv done_$DATE/passwd.csv.back
396
397date
398ls -1 *csv | while read csv
399do
400csvdb=`echo "$csv" | cut -f1 -d.`
401# Arreglo de los ficheros por lineas duplicadas.
402mv $csv tabletemp.0
403iconv --from-code=ISO-8859-1 --to-code=UTF-8 ./tabletemp.0 > tabletemp
404cat tabletemp | sort | uniq > tabletemp2
405cat tabletemp2 | sed -e 's/\|\([[:digit:]]\{4\}\)\([[:digit:]]\{2\}\)\([[:digit:]]\{2\}\)\|/\1-\2-\3/g' > tabletemp3
406cat tabletemp > $csv
407# echo "csv:   $csv"
408# echo "csvdb: $csvdb"
409mysqlimport -d --local --fields-terminated-by=\| --user=$DBUSER --password=$DBPASS $DB $csv
410
411mv $csv done_$DATE/.
412done
413
414date
415
416echo "Creando tabla de vehiculos vivos"
417echo "
418DROP TABLE IF EXISTS cstm_live_flt;
419
420CREATE TABLE cstm_live_flt AS
421SELECT count(*) AS fleet_size,cmpy_or_prsn_id
422FROM vhc_cntr
423WHERE dsct_dat = 20991231
424GROUP BY cmpy_or_prsn_id;
425" | mysql -u$DBUSER -p$DBPASS phx_data
426
427date
428
429/usr/bin/mysqldump -u$DBUSER -p$DBPASS --all $DB > $DB.$DATE.sql
430gzip -f $DB.$DATE.sql
431
432fi
433
434# Sets the II_DBMS_SERVER to the batch server
435# ii_dbms_server=`$II_SYSTEM/ingres/bin/ingprenv | grep SVR_BATCH | cut -d'=' -f2`
436# II_DBMS_SERVER=$ii_dbms_server
437# export II_DBMS_SERVER
438
439DATES=`date  +\%Y\%m\%d\%H\%M\%S`
440YEAR=`date +\%Y`
441DATE=`date +\%Y\%m\%d`
442BATCH=$ELS_CTRY'_prc'
443echo "BATCH: $BATCH"
444BATCH_1=$BATCH'_1'
445echo "BATCH_1: $BATCH_1"
446BATCH_0=$BATCH'_0'
447echo "BATCH_0: $BATCH_0"
448
449echo "
450SELECT table_name, table_owner
451FROM iitables
452WHERE table_name like '%$BATCH_1%'
453ORDER BY table_name;
454\g"| sql $ELS_DBNAME | grep $LOGNAME| cut -f2 -d\||while read table
455do
456echo "
457DROP TABLE $table;\g;" | $II_SYSTEM/ingres/bin/sql -s $ELS_DBNAME >/dev/null
458echo 'Table '$table' deleted...'
459done
460
461export ELS_DATA="/tmp"
462
463mkdir -p "$ELS_DATA/transfer_es/$DATE" 2>/dev/null
464echo "Creating $ELS_DATA/transfer_es/$DATE..."
465
466cd "$ELS_DATA/transfer_es/"
467cd "$DATE"
468
469echo "Creating Max-Sequence Number Contract"
470
471echo "
472
473CREATE TABLE vhc_cntr_max AS
474SELECT flt_id,vhc_cntr_id,
475MAX(vhc_cntr_seq_nbr) as vhc_cntr_seq_nbr,
476prpl_type_cod
477FROM vhc_cntr v
478GROUP BY flt_id,prpl_type_cod,vhc_cntr_id
479;
480\g;
481
482"|sql -s $ELS_DBNAME -uingres
483
484
485#
486##################################################
487# Classification of Vehicles Pending Instalation #
488##################################################
489
490echo "( $2 )Starting data gathering. This may take a few minutes. Please wait..."
491
492USERTABLE="vhc_cntr_max
493cost_cent
494cstm
495cstm_cnct
496cstm_hrch
497cstm_invc_hdr
498cstm_invc_line
499cstm_invc_dtls
500cstm_ord_hdr
501cstm_ord_sta
502dept
503drv
504drv_grp_cmpt_prmt
505eltc_adrs
506eltc_adrs_cstm_cnct_link
507eltc_adrs_cstm_cnct_qlfc
508empl
509fnct
510fnct_type
511ord_vhc
512ord_vhc_srce
513prpl
514rlf_srvc_type
515rlf_srvc_type_rsn
516vhc_type
517vhc_type_cost
518vhc_type_xref"
519
520if [[ $# -eq 2 ]]
521then
522
523   USERTABLE="$2"
524        echo "Single table mode. Extracting $USERTABLE from $ELS_DBNAME)"
525
526fi
527
528echo "$USERTABLE" | while read table
529do
530
531echo "Extracting table $table..."
532copydb -c $ELS_DBNAME -uingres -infile=$table.in -outfile=$table.out $table >/dev/null
533mv $table.out table
534cat table | sed -e's/= .*tab,/= c0tab,/g' -e's/= .*nl.*,/= c0nl)/g' -e'/nl=/d' -e's/\\p\\g/\\g/g' | gzip -c > $table.out.$DATE.gz
535# ls -l $table.out.$DATE.gz
536rm table
537gunzip -c $table.out.$DATE.gz | sql $ELS_DBNAME -uingres >/dev/null
538cat $table.ingres | tr -s ' ' |sed -e's/\t /\t/g' -e's/ \t/\t/g' -e's/ $//g' -e's/^ //g'| gzip -c > $table.dat.$DATE.gz
539rm $table.ingres
540gzip -c $table.in > $table.in.$DATE.gz
541rm $table.in
542
543ls -1 $table*.gz | while read file
544do
545$HOME/send2lin $file >/dev/null &
546#rm $file
547done
548
549done
550
551echo "Complete SQL processing..."
552echo "
553DROP vhc_cntr_max;
554\g;
555"|sql -s $ELS_DBNAME -uingres
556
557touch PHX_TRANSFER_$DATE.OK
558$HOME/send2lin PHX_TRANSFER_$DATE.OK > /dev/null &