#!/bin/ksh
set -x
###########
#FUNCTIONS#
###########

function extractData
{
perl -ne 'BEGIN { $/="\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*" } chomp; print if /error_field/i' $recent_log | \
awk '(match($1,/^cccardcode|^date/)) {printf "%s;",$3};(match($1,/^file_amount/)) {printf "%s\n",$3}' | tr -d " " | tr -d "\015" | \
awk -F";" '{
        printf "%s;", $1
        printf "%s;", $2
}
$3 ~ /[0-9]+\.00+/ {
        printf "%.0f\n", $3
}
$3 ~ /[0-9]+\.[1-9]0+/ {
        printf "%.1f\n", $3
}
$3 ~ /[0-9]+\.[0-9][1-9]0+/ {
        printf "%.2f\n", $3
}'
}

function doCSVSQL
{
sqlplus -s user/pass@SID >>report_dettaglio_$(date +"%Y%m%d").csv << EOF
@do_csv.sql
exit;
EOF
}


##########
#PRECHECK#
##########

cd /LOGPATH
recent_log=$(ls PIHCCARD*.LOG | tail -1)
go=$(cat $recent_log | grep error_field | wc -l)

if [ $go -eq 0 ]
then
echo "Nessuno scarto trovato nei LOG. Esco."
break
else

######
#MAIN#
######

rm -f tmp_data.txt
extractData > tmp_data.txt

cat tmp_data.txt | while read line
do

cdc="'$(echo $line|awk -F";" '{print $1}')'"
emiss="'$(echo $line|awk -F";" '{print $2}')'"
importo="'$(echo $line|awk -F";" '{print $3}')'"
cdc_cut=$(echo $cdc | cut -c14-17)


sqlplus -s user/pass@SID >result.txt << EOF
set pagesize 32000
set linesize 500
set heading off
set feedback off
select /*+ PARALLEL(customer 2) PARALLEL(orderhdr 2) PARALLEL(tickler_records 2) USE_HASH(customer) */
c.customer_id
from customer c,orderhdr o,tickler_records t
where c.customer_id=o.customer_id
and o.customer_id=t.customer_id
and o.ohinvamt=$importo
and o.ohentdate=to_date($emiss,'DDMMYY')
and (t.long_description like '%Carta di credito%$cdc_cut%'
or long_description like '%Cambio Metodo di Pagamento%$cdc_cut%'
or long_description like '%Carta di Credito%$cdc_cut%');
exit;
EOF

cat result.txt | while read customer_id
do doCSVSQL
rm -f result.txt
done

done