#!/bin/bash
#
cd /home/smb/WILLREP
echo "Starting File Insertion Process..."
date

#cd SUMMARY
cd ALL

ls -1atr *-O-*.tsv | while read tsv
do
date +%F\|%T
echo "Converting $tsv to less-satanic format..."

echo "Grokking Data from Original Willow Crapfile..."
 cat $tsv | grep -vi topaziquote | grep -v DigitalCockpit | grep -v 42337 \
    |sed -e '1,4d' -e"s/\\\/\//g" -e"s/\t/\|/g" -e's/,/./g' \
    -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
date +%F\|%T

echo "Extracting Quote Data..."
 cat $tsv.temp |grep -v \|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\| \
    | cut -f1-143,150-152 -d\|> $tsv.quot
date +%F\|%T

echo "Extracting Option and Accesory Quote Data..."
 cat $tsv.temp |grep \|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\|\| \
     | cut -f1-2,134- -d\| > $tsv.opt
date +%F\|%T

echo "Importing Extracted Quote Data..."
QLINES=`wc -l $tsv.quot| cut -f1 | sed -e 's/ / lines - /g'`
echo "Importing $tsv.quot ($QLINES)..."
cp $tsv.quot temp_willow_quotes
cat temp_willow_quotes | sed -e's/||/|\\N|/g' -e's/||/|\\N|/g' -e's/|$/|\\N/g'> temp_willow_quotes.2
iconv --from-code=ISO-8859-1 --to-code=UTF-8 ./temp_willow_quotes.2 > temp_willow_quotes.3
mysqlimport -d --local --fields-terminated-by=\| --user=$DBUSER --password=$DBPASS willreps temp_willow_quotes.3
date +%F\|%T
rm temp_willow_quotes temp_willow_quotes.2

echo "Importing Extracted Quote Options and Accesories Data..."
OLINES=`wc -l $tsv.opt| cut -f1 | sed -e 's/ / lines - /g'`
echo "Importing $tsv.opt ($OLINES)..."
cp $tsv.opt temp_willow_extras
cat temp_willow_extras | sed -e's/||/|\\N|/g' -e's/||/|\\N|/g' -e's/|$/|\\N/g'> temp_willow_extras.2
iconv --from-code=ISO-8859-1 --to-code=UTF-8 ./temp_willow_extras.2 > temp_willow_extras.3
mysqlimport -d --local --fields-terminated-by=\| --user=$DBUSER --password=$DBPASS willreps temp_willow_extras.3
date +%F\|%T
#rm temp_willow_extras temp_willow_extras.2 temp_willow_extras.3
rm temp_willow_extras temp_willow_extras.2

echo "[ $tsv ] Cross-referencing with MySQL absolutely no cleanup made..."
/usr/bin/mysql -u$DBUSER -p$DBPASS willreps < /home/smb/WILLREP/willow_import_O.sql
echo "[ $tsv ] Data processed, temptables nukes..."
date +%F\|%T

mkdir done_details 2>/dev/null
mv $tsv.* done_options/logfiles/.
# echo "Deleting files: $tsv.*"
#rm $tsv.*
echo "Moving: $tsv to done_options/."
mv $tsv* done_options/.
done

ls -1atr *-D-*.tsv | while read tsv
do
echo "Converting $tsv..."

echo "Filtering Element Data from Original Willow Crapfile..."
 cat $tsv \
    |sed -e '1,5d' -e"s/\t/\|/g" -e's/,/./g' |tr -d \" > $tsv.elmts

echo "Importing Extracted Quote Elements Data..."
DLINES=`wc -l $tsv.elmts| cut -f1`
echo "Importing $tsv.elmts ($DLINES)..."
#../phpimport.php localhost $DBUSER $DBPASS willreps temp_willow_elmts `pwd`$tsv.elmts 0 >>$tsv.elmts.log
cp $tsv.elmts temp_willow_elmts
cat temp_willow_elmts | sed -e's/||/|\\N|/g' -e's/||/|\\N|/g' -e's/|$/|\\N/g'> temp_willow_elmts.2
iconv --from-code=ISO-8859-1 --to-code=UTF-8 ./temp_willow_elmts.2 > temp_willow_elmts.3
mysqlimport -d --local --fields-terminated-by=\| --user=$DBUSER --password=$DBPASS willreps temp_willow_elmts.3
date +%F\|%T
#rm temp_willow_elmts temp_willow_elmts.2 temp_willow_elmts.3
rm temp_willow_elmts temp_willow_elmts.2
rm temp_willow_elmts temp_willow_elmts.2

echo "[ $tsv ] Cross-referencing with MySQL..."
/usr/bin/mysql -u$DBUSER -p$DBPASS willreps < /home/smb/WILLREP/willow_import_D.sql
echo "[ $tsv ] Tables temp_willow_elmts nuked..."
date +%F\|%T

mkdir done_details 2>/dev/null
mv $tsv.* done_details/logfiles/.
#rm $tsv.*
mv $tsv* done_details/.
done

# /usr/bin/mysql -u$DBUSER -p$DBPASS VOD_TRACKING < /home/smb/WILLREP/willow_reservas.sql
# /usr/bin/mysql -u$DBUSER -p$DBPASS VOD_TRACKING_UAT < /home/smb/WILLREP/willow_reservas.sql

cd /home/smb/WILLREP
echo "Starting File Insertion Process..."
date

cd SUMMARY_N
#cd ALL_N

echo "Starting process..."
mkdir -p done 2>/dev/null
mkdir -p sqldone 2>/dev/null

ls -1 *QRD*.tsv | \
while read line
do

date +%F\|%T
echo "Processing $line - First pass - Converting to Unicode"
cat $line | \
iconv -f ISO-8859-1 -t UTF-8 > $line.sql
# | grep -vi topaziquote | grep -v DigitalCockpit | grep -v 42337 > $line.sql

echo "Storing original TSV file..."
mv $line done/.
mv $line.sql $line.sql.2

echo "Processing $line - First pass - Initial SQL fix"
cat $line.sql.2| \
grep -v '\/\*' | grep -v '\*\/'| sed \
\
 -e's/DROP TABLE/DROP TABLE IF EXISTS/g' \
 -e"s|TO_DATE(|str_to_date(|g" \
 -e"s|DD-MM-RRRR|%d-%m-%Y|g" \
 -e"s|DD/MM/RRRR|%d/%m/%Y|g" \
 -e"s|HH24:MI:SS|%T|g" \
 -e's/VARCHAR2/VARCHAR/g' \
 -e"s/NUMBER/DECIMAL/g" > $line.sql

# -e's/COMMIT;//g' \


echo "Completed fix of $line"

rm $line.sql.2

done

mysql -f -u$DBUSER -p$DBPASS willreps_test < ../willow_import_INIT.sql

ls -1 LIST*-QRD-*.sql | while read line
do

echo "importing $line"
mysql -f -u$DBUSER -p$DBPASS willreps_test < "$line"
mysql -f -u$DBUSER -p$DBPASS willreps_test < ../willow_import_LIST.sql
mv $line sqldone/.

done

ls -1 COMPONENT*-QRD-*.sql | while read line
do

echo "importing $line"
mysql -f -u$DBUSER -p$DBPASS willreps_test < "$line"
mysql -f -u$DBUSER -p$DBPASS willreps_test < ../willow_import_COMPONENTS.sql
mv $line sqldone/.

done

ls -1 EXTRA*-QRD-*.sql | while read line
do

echo "importing $line"
mysql -f -u$DBUSER -p$DBPASS willreps_test < "$line"
mysql -f -u$DBUSER -p$DBPASS willreps_test < ../willow_import_EXTRAS.sql
mv $line sqldone/.

done

mysql -f -u$DBUSER -p$DBPASS willreps_test < ../willow_import_END.sql

DATEHUMAN=`date +%d/%m/%Y`
DATES=`date +%Y%m`
DATEL=`date +%Y%m%d`
TIME=`date +%R`
MAINDIR="/home/smb"
FAXDIR="$MAINDIR/fax"
FAXDONE="$MAINDIR/faxdone"

cd "$FAXDIR"

ls -1d 91* | while read FAXN

do

# echo "Estoy en $FAXDIR -> $FAXN"

cd "$FAXN"

if [ -d "$DATES" ]
then

# echo "Veo $FAXN/fax -> $DATES"

if [ -e "./filelist"  ]; then
rm filelist
fi

pdfcount=`find . -name "*[PDF,pdf]" -print | wc -l`

if [ $pdfcount -gt 0 ]
then

find . -name "*[PDF,pdf]" -print | while read pdf
do
pdf2=`echo $pdf | sed -e's/.PDF/_PDF/g' -e's|\./||g' |tr -d .| sed -e's/_PDF/.PDF/g'`
# echo $pdf2 | sed -e's|/|-|g' -e"s/$DATES-/$DATEL-/g"
pdf3=`echo $pdf2 | sed -e's|/|-|g' -e"s/$DATES-/$DATEL-/g"`
pdf4=`echo $pdf | sed -e's|\./|/|g'`
echo "$pdf3 $pdf4" >> filelist
# echo $pdf3
# echo $pdf4
done

fi

if [ -e "./filelist"  ]; then
cat filelist | while read line
do filename=`echo $line | awk '{ print $1 }'`
path=`echo $line| awk '{ print $2}'`
mkdir -p $FAXDONE/$FAXN/$DATEL
if [ $FAXN -eq "914903677" ]
then
        chmod 777 $FAXDONE/$FAXN/$DATEL
fi
mv $FAXDIR/$FAXN"$path" $FAXDONE/$FAXN/$DATEL/$filename
# echo $filename
SENDER=`echo $filename| cut -f2 -d-`
# echo $SENDER
cd /var/www/rf/
export MAILPARAMS="fax=$FAXN&date=$DATEL&hour=$TIME&path=/$DATEL&file=$filename&sender=$SENDER&filepath=$FAXDONE/$FAXN/$DATEL/$filename&datehuman=$DATEHUMAN"
# echo "fax=$FAXN
# date=$DATEL
# hour=$TIME
# path=/$DATEL
# file=$filename
# sender=$SENDER
# filepath=$FAXDONE/$FAXN/$DATEL/$filename
# datehuman=$DATEHUMAN"

# echo "Testing PHP script"
# echo $MAILPARAMS
# echo "/usr/bin/php -f /var/www/rf/mail.php"
/usr/bin/php -f /var/www/rf/mail.php
done
rm ./filelist
fi

fi

cd ..

done

export CD1="/home/ftp/pricing"
export CD2="/opt/backup/pricing"

cd $CD1

DATE=`date +%Y%m%d`
DATES=`date +%Y%m%d%H%M%S`
DB=phx_data
export FILEDIR=vhc_es_prc_$DATE
export FILE=$FILEDIR.tar.zip
export OKFILE=PHX_TRANSFER_$DATE.OK

# echo $OKFILE

if [ -e $OKFILE ]
then
rm $OKFILE

mkdir $DATE
mv *.in.$DATE.gz $DATE/.
mv *.out.$DATE.gz $DATE/.
mv *.dat.$DATE.gz $DATE/.

cd $DATE

time ls -1 *.in.$DATE.gz | while read file
do
echo "  --- Processing table creation from $file... ..."
TABLE=`echo $file | cut -f1 -d\.`
echo "Removing table $TABLE"
echo "DROP TABLE $TABLE;" | mysql -u$DBUSER -p$DBPASS phx_data_2
echo "Creating new table from $file..."
zcat $file | \
sed -n -e '/create table/,/with /p' | \
sed -e'/with noduplicates/d' -e'/with duplicates/d' \
 -e "s/ not null default ' '//g" \
 -e 's/ not null default 0//g' \
 -e 's/ not null not default//g' \
 -e's/ char(/ varchar(/g' \
 -e's/_dat_id integer/_dat_id date/g' \
 -e's/_dat integer/_dat date/g' \
 -e's/ integer/ int(11)/g' \
 -e's/ i1/ smallint(6)/g' \
 -e's/ f4/ decimal(10,4)/g' \
 -e's/ float/ decimal(10,4)/g' \
 | mysql -u$DBUSER -p$DBPASS phx_data_2

echo "Adding indices from $file..."
zcat $file | \
sed -n -e '/create index/,/with struct/p'| \
sed -e's/with structure.*/;/g' \
 | mysql -u$DBUSER -p$DBPASS phx_data_2
echo "  --- Done table creation from $file... ..."
done

time ls -1 *.dat.$DATE.gz | while read file
do
echo "  --- Importing $file... ---
"
TABLE=`echo "$file"| cut -f1 -d.`
echo "Importing $file into $TABLE...
"
zcat $file| iconv  --from-code=ISO-8859-1  --to-code=UTF-8 | tr -s ' ' |\
 sed -e's/\t /\t/g' -e's/ \t/\t/g' -e's/ $//g' -e's/^ //g' |\
 sed -e 's/\([[:space:]]\)\([[:digit:]]\{4\}\)\([[:digit:]]\{2\}\)\([[:space:]]\)\([[:digit:]]\{2\}\)/\1\2-\3-\4\5/g' \
 > $TABLE

echo "TRUNCATE TABLE $TABLE;" | mysql -u$DBUSER -p$DBPASS phx_data_2

 filelist=filelist$$
        split -500 $TABLE $TABLE
        ls $TABLE?? > $filelist
        cat $filelist | while read line
        do

echo "Processing Sub File $line..."

# mysqlimport -d --local --fields-terminated-by='\t' --user=$DBUSER --password=$DBPASS phx_data_2 $TABLE
mv $TABLE $TABLE.tempfile
mv $line $TABLE
#mysqlimport -d --local --fields-terminated-by='\t' --user=$DBUSER --password=$DBPASS phx_data_2 $line

echo "Importing $TABLE $line"
mysqlimport -rf --local --fields-terminated-by='\t' --user=$DBUSER --password=$DBPASS "phx_data_2" "$TABLE"

echo "SHOW WARNINGS;" | mysql -u$DBUSER -p$DBPASS > $TABLE.err
#rm $line
mv $TABLE.tempfile $TABLE
done
rm $TABLE
echo "  --- Done importing $file... ---
"
done

fi

export CD1="/home/ftp/vhc_pnd"
export CD2="/opt/backup/pricing"

cd $CD1

DATE=`date +%Y%m%d`
DATES=`date +%Y%m%d%H%M%S`
DB=phx_data
export FILEDIR=vhc_es_prc_$DATE
export FILE=$FILEDIR.tar.zip
export OKFILE=$FILE.ok

if [ -e $OKFILE ]
then
rm $OKFILE
mkdir $CD2/prc_$DATE 2>/dev/null
cd $CD2/prc_$DATE
# cp ../$FILE ./.
mv $CD1/$FILE ./.
tar zxvf $FILE >/dev/null
echo "Making Dir csv_$DATE"
mkdir csv_$DATE 2>/dev/null
pwd
cd csv_$DATE
pwd
mv ../$FILEDIR/*csv .

#/usr/bin/mysql -u$DBUSER -p$DBPASS VOD_TEST < ../../VOD_TABLES_1
# rm -rf done_$DATE
mkdir done_$DATES 2>/dev/null
mv done_$DATE done_$DATES 2>/dev/null
mkdir done_$DATE 2>/dev/null

mv sq_vhei.csv sls_qttn_vhc_extr_item.csv
mv visl.csv vhc_invt_srce_link.csv
mv eaccl.csv eltc_adrs_cstm_cnct_link.csv
mv passwd.csv done_$DATE/passwd.csv.back

date
ls -1 *csv | while read csv
do
csvdb=`echo "$csv" | cut -f1 -d.`
# Arreglo de los ficheros por lineas duplicadas.
mv $csv tabletemp.0
iconv --from-code=ISO-8859-1 --to-code=UTF-8 ./tabletemp.0 > tabletemp
cat tabletemp | sort | uniq > tabletemp2
cat tabletemp2 | sed -e 's/\|\([[:digit:]]\{4\}\)\([[:digit:]]\{2\}\)\([[:digit:]]\{2\}\)\|/\1-\2-\3/g' > tabletemp3
cat tabletemp > $csv
# echo "csv:   $csv"
# echo "csvdb: $csvdb"
mysqlimport -d --local --fields-terminated-by=\| --user=$DBUSER --password=$DBPASS $DB $csv

mv $csv done_$DATE/.
done

date

echo "Creando tabla de vehiculos vivos"
echo "
DROP TABLE IF EXISTS cstm_live_flt;

CREATE TABLE cstm_live_flt AS
SELECT count(*) AS fleet_size,cmpy_or_prsn_id
FROM vhc_cntr
WHERE dsct_dat = 20991231
GROUP BY cmpy_or_prsn_id;
" | mysql -u$DBUSER -p$DBPASS phx_data

date

/usr/bin/mysqldump -u$DBUSER -p$DBPASS --all $DB > $DB.$DATE.sql
gzip -f $DB.$DATE.sql

fi

# Sets the II_DBMS_SERVER to the batch server
# ii_dbms_server=`$II_SYSTEM/ingres/bin/ingprenv | grep SVR_BATCH | cut -d'=' -f2`
# II_DBMS_SERVER=$ii_dbms_server
# export II_DBMS_SERVER

DATES=`date  +\%Y\%m\%d\%H\%M\%S`
YEAR=`date +\%Y`
DATE=`date +\%Y\%m\%d`
BATCH=$ELS_CTRY'_prc'
echo "BATCH: $BATCH"
BATCH_1=$BATCH'_1'
echo "BATCH_1: $BATCH_1"
BATCH_0=$BATCH'_0'
echo "BATCH_0: $BATCH_0"

echo "
SELECT table_name, table_owner
FROM iitables
WHERE table_name like '%$BATCH_1%'
ORDER BY table_name;
\g"| sql $ELS_DBNAME | grep $LOGNAME| cut -f2 -d\||while read table
do
echo "
DROP TABLE $table;\g;" | $II_SYSTEM/ingres/bin/sql -s $ELS_DBNAME >/dev/null
echo 'Table '$table' deleted...'
done

export ELS_DATA="/tmp"

mkdir -p "$ELS_DATA/transfer_es/$DATE" 2>/dev/null
echo "Creating $ELS_DATA/transfer_es/$DATE..."

cd "$ELS_DATA/transfer_es/"
cd "$DATE"

echo "Creating Max-Sequence Number Contract"

echo "

CREATE TABLE vhc_cntr_max AS
SELECT flt_id,vhc_cntr_id,
MAX(vhc_cntr_seq_nbr) as vhc_cntr_seq_nbr,
prpl_type_cod
FROM vhc_cntr v
GROUP BY flt_id,prpl_type_cod,vhc_cntr_id
;
\g;

"|sql -s $ELS_DBNAME -uingres


#
##################################################
# Classification of Vehicles Pending Instalation #
##################################################

echo "( $2 )Starting data gathering. This may take a few minutes. Please wait..."

USERTABLE="vhc_cntr_max
cost_cent
cstm
cstm_cnct
cstm_hrch
cstm_invc_hdr
cstm_invc_line
cstm_invc_dtls
cstm_ord_hdr
cstm_ord_sta
dept
drv
drv_grp_cmpt_prmt
eltc_adrs
eltc_adrs_cstm_cnct_link
eltc_adrs_cstm_cnct_qlfc
empl
fnct
fnct_type
ord_vhc
ord_vhc_srce
prpl
rlf_srvc_type
rlf_srvc_type_rsn
vhc_type
vhc_type_cost
vhc_type_xref"

if [[ $# -eq 2 ]]
then

   USERTABLE="$2"
        echo "Single table mode. Extracting $USERTABLE from $ELS_DBNAME)"

fi

echo "$USERTABLE" | while read table
do

echo "Extracting table $table..."
copydb -c $ELS_DBNAME -uingres -infile=$table.in -outfile=$table.out $table >/dev/null
mv $table.out table
cat 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
# ls -l $table.out.$DATE.gz
rm table
gunzip -c $table.out.$DATE.gz | sql $ELS_DBNAME -uingres >/dev/null
cat $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
rm $table.ingres
gzip -c $table.in > $table.in.$DATE.gz
rm $table.in

ls -1 $table*.gz | while read file
do
$HOME/send2lin $file >/dev/null &
#rm $file
done

done

echo "Complete SQL processing..."
echo "
DROP vhc_cntr_max;
\g;
"|sql -s $ELS_DBNAME -uingres

touch PHX_TRANSFER_$DATE.OK
$HOME/send2lin PHX_TRANSFER_$DATE.OK > /dev/null &