#!/bin/bash

SQL="mysql -Bs --force -uroot -peuro2000 eurotest"
#SQL="cat"
export LANG=en_US.UTF-8
export PATH=`pwd`:$PATH
TITLE='Import PLU'
HOST=`hostname`
WAITING_PID=0

DEVEL_HOST=0
if [ $HOST == 'sunshine.spintec.it' ]
then
  DEVEL_HOST=1
  SQL="mysql -uroot eurotest"
fi

err_report() 
{
  echo "#### TRAP #### Want to kill $WAITING_PID"
  echo "Error on line $1"
  if [ $WAITING_PID -gt 0 ]
  then
    kill $WAITING_PID
  fi
  sysmsg "$TITLE" "Procedura terminata in modo anomalo. LINE_ID:$1" & 
}
    
trap 'err_report $LINENO' EXIT
    


function escape_sql                                                                                                                                                                      
{                                                                                                                                                                                        
  echo "$1" | sed 's/\\/\\\\/g; s/'"'"'/\\'"'"'/g; s/"/\\"/g'                                                                                                                            
}                                                                                                                                                                                        

function import2newplu
{
    case "$1" in
    DEP) echo "dept" ;; 
    CAT) echo "category" ;;
    VAR) echo "varprice" ;;
    DESCR_1) echo "descr" ;; 
    DESCR) echo "descr" ;; 
    PRICE_1) echo "price" ;; 
    PRICE1) echo "price" ;; 
    HHTKEY) echo "hhtkey" ;; 
    HHTDESCR) echo "hhtdescr" ;;
    INGR) echo "ingredient" ;; 
    BARCODE) echo "barcode" ;; 
    ACT_STOCK) echo "stock" ;; 
    MIN_STOCK) echo "min_stock" ;; 
    PURCHASE) echo "purchase_price" ;; 
    LINKED) echo "linked" ;;
    QRORDER) echo "qrorder" ;;
    *) echo "" 
    esac    
}


function import2dept
{
    case "$1" in
    DEP) echo "dept_id" ;;
    DESCR_1) echo "dept_descr" ;;
    DESCR) echo "dept_descr" ;;
    *) echo "" 
    esac    
}


function newplu2import
{
    case "$1" in
    dept) echo "DEP" ;; 
    category) echo "CAT" ;;
    varprice) echo "VAR" ;;
    descr) echo "DESCR_1" ;; 
    price) echo "PRICE_1" ;; 
    hhtkey) echo "HHTKEY" ;;
    hhtdescr) echo "HHTDESCR" ;;
    ingredient) echo "INGR" ;; 
    barcode) echo "BARCODE" ;; 
    stock) echo "ACT_STOCK" ;; 
    min_stock) echo "MIN_STOCK" ;; 
    purchase_price) echo "PURCHASE" ;; 
    linked) echo "LINKED" ;;
    qrorder) echo "QRORDER" ;;
    *) echo "" 
    esac    
}



function dept2import
{
    case "$1" in
    dept_id) echo "DEP" ;;
    dept_descr) echo "DESCR_1" ;;
    *) echo "" 
    esac    
}



FULL_NEWPLU_FIELD_SET="plu_id,plucode,descr,dept,hhtkey,category,linked,ingredient,stock,hhtdescr,catplusprefix,catminusprefix,tot_sale,tot_qty,dept_prn,prn_options,print_qty,varprice,varweight,print_zeroprice,cat_msg,cat_neg_price,rem_prn_descr,barcode,min_stock,purchase_price,gain,price,stockwatch,max_price,qrorder"
FULL_DEPT_FIELD_SET="dept_id,dept_descr,dept_filter,vat_id,print_qty,list_pos,nonfiscal,exclude_linuxterm,article_prefix,delivery_monitor,options"
		      
IMPORT_ERROR=0
echo "<pre>" > /tmp/CSV_RESULT

systools/utf
sysmsg "$TITLE" "<pre>Inserire dispositivo di salvataggio con i dati da convertire<br>(file 'eurotest.csv') e premere il bottone OK.<br><br>    La conversione durera' alcuni secondi...</pre>"
systools/mtf

pushd /mnt/floppy >/dev/null

sysmsg "$TITLE" "<pre>Controllo del file in corso<br>   Attendere ......</pre>" &
WAITING_PID=$!


if [ $# -gt 0 ]
then
  INFILE=$1
else
  INFILE='-'
fi
#cat $INFILE  >/tmp/importfile1
cat $INFILE | 
sed "s/è/e'/g; s/é/e'/g;  s/ò/o'/g; s/ó/o'/g; s/à/a'/g; s/á/a'/g; s/ì/i'/g; s/í/i'/g; s/ù/u'/g; s/ú/u'/g; s/À/A'/g; s/Á/A'/g; s/È/E'/g; s/É/E'/g; s/Ì/I'/g; s/Í/I'/g; s/Ò/O'/g; s/Ó/O'/g; s/Ù/U'/g; s/Ú/U'/g; s/½/1\\/2/g; s/–/-/g; s/¼/1\\/4/g; s/¾/3\\/4/g; s/­/-/g; s/’/'/g; s/ / /g; s/\\\\/\\\\\\\\/g;" >/tmp/importfile1
#sed 's/\\/\\\\/g' |
#sed -E ':a;/^([^"]*"[^"]*")*[^"]*"[^"]*$/{N;s/\n/\\n/;ta}' \


popd > /dev/null


LINES=`cat /tmp/importfile1 | wc -l`
#echo lines=$LINES, lines-1=$(($LINES-1))
tail -$(($LINES-1)) /tmp/importfile1 > /tmp/importfile 
L=`head -1 /tmp/importfile1`

#if echo $L | grep \" >/dev/null
#then
  ENCLOSED="OPTIONALLY ENCLOSED BY '\"'"
#fi
TRM="\t"
if echo $L | grep \; >/dev/null
then
  TRM=";"
fi
if echo $L | grep \, >/dev/null
then
  TRM=","
fi

if [ $WAITING_PID -gt 0 ]
then
  kill $WAITING_PID
fi

if [ $LINES = 0 ] 
then
  systools/utf
  sysmsg "$TITLE" "Non sono stati trovati i dati da convertire ($INFILE)"  
  trap - EXIT
  exit 1 
fi


CLEAN_IMPORT=0

sysquery "$TITLE" "<b>Volete cancellare i dati del magazzino del Euro3G attuale ?</b>" "Si, cancellare" "No, aggiungere/modificare senza cancellare"
if [ $? == 0 ] ;
then
  CLEAN_IMPORT=1

  ./systools/clear_store eurotest
fi

CONVERSION_START_SECS=$SECONDS

sysmsg "$TITLE" "<pre>Conversione in corso, attendere ......</pre>" &
WAITING_PID=$!

rm -f /tmp/CSV_IMPORT_CONFLICTS
rm -f /tmp/CSV_IMPORT_FIELD_LIST
rm -f /tmp/CSV_IMPORT_PRICEID_LIST
rm -f /tmp/CSV_IMPORT_LANGID_LIST
rm -f /tmp/CSV_IMPORT_CONFLICT_PLUID_LIST
rm -f /tmp/CSV_IMPORT_CONFLICT_DEPID_LIST

L=`echo $L | tr ',\\t' ';;' | sed 's/^;/blank;/g' | sed 's/;;/;blank;/g' | sed 's/;;/;blank;/g'`

{
echo "DROP TABLE IF EXISTS import;"
echo "CREATE TABLE import ("

EMPTY_DESCR_SQL="( 1"
N=1
for i in `echo $L | tr ';"' '  '`
do
  if [ "$i" = blank ]
  then 
    echo "XXX$N VARCHAR(10) NOT NULL,"
    N=$(($N+1))
  elif [ "$i" != " " ]
  then
    echo $i >> /tmp/CSV_IMPORT_FIELD_LIST
    echo "$i VARCHAR(205) NOT NULL,"
    if echo $i | egrep -q "^PRICE_"
    then
      echo $i | cut -d'_' -f 2 >> /tmp/CSV_IMPORT_PRICEID_LIST
    fi  
    if echo $i | egrep -q "^DESCR_"
    then
      echo $i | cut -d'_' -f 2 >> /tmp/CSV_IMPORT_LANGID_LIST
      EMPTY_DESCR_SQL=$EMPTY_DESCR_SQL" AND $i=''"
    fi
  fi
#  if [ "$i" = NOCATSIGN ]
#  then
#    break
#  fi
done

EMPTY_DESCR_SQL=$EMPTY_DESCR_SQL" )"
if [ "$EMPTY_DESCR_SQL" = "( 1 )" ] && cat /tmp/CSV_IMPORT_FIELD_LIST | grep -q "DESCR"
then
  EMPTY_DESCR_SQL="( DESCR = '' )"
fi
echo "XXX999 int"
echo ");"

echo "LOAD DATA INFILE '/tmp/importfile' INTO TABLE import FIELDS TERMINATED BY '$TRM' $ENCLOSED ;"                          
echo "ALTER TABLE import ADD EXPORTED VARCHAR(205) NOT NULL DEFAULT '0';"
echo "ALTER TABLE import ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY;"



if [ $CLEAN_IMPORT -gt 0 ]
then
  echo "DELETE FROM vat;"
  echo "INSERT INTO vat SET id=1, vat_val=10;"
  for db_table in department deptdescr pludescr langname dept_btn_grid dept_printer_config dept_group_config linkplu newplu price plu_grid specplu offers article_groups plu_optinfo btn_color_config "kbd_grid where isplu!=0"
  do
     echo "DELETE FROM $db_table;"
  done 
  if cat /tmp/CSV_IMPORT_FIELD_LIST | grep -q "EXPORTED"
  then
    echo "UPDATE import SET EXPORTED='0';"
  else
    false
#    echo "ALTER TABLE import ADD EXPORTED VARCHAR(205) NOT NULL;"
  fi    
  if cat /tmp/CSV_IMPORT_FIELD_LIST | grep -q "PLU_ID"
  then
    echo "UPDATE import SET PLU_ID=0;"
  fi    
fi

for FLD in HHTKEY CAT VAR PRICE1 PRICE2 PRICE3 PRICE4 PRICE5 PRICE6 PRICE7 PRICE8 PRICE9 
do
  if ! cat /tmp/CSV_IMPORT_FIELD_LIST | grep -q "$FLD"
  then
    echo "ALTER TABLE import ADD $FLD VARCHAR(1) NOT NULL DEFAULT '';"
  fi    
done

} > /tmp/CSV_IMPORT_1_SQLGEN
cat /tmp/CSV_IMPORT_1_SQLGEN | $SQL  >> /tmp/CSV_RESULT 2>/tmp/CSV_IMPORT_ERROR

#trap - EXIT
#exit 3

{

echo "CREATE TEMPORARY TABLE tmp_csv_import SELECT * FROM import LIMIT 0;"
echo "DROP TABLE IF EXISTS tmp_newplu;"
echo "CREATE TEMPORARY TABLE tmp_newplu SELECT * FROM newplu LIMIT 0;"
echo "DROP TABLE IF EXISTS tmp_department;"
echo "CREATE TEMPORARY TABLE tmp_department SELECT * FROM department LIMIT 0;"
echo "DROP TABLE IF EXISTS tmp_price;"
echo "CREATE TEMPORARY TABLE tmp_price SELECT * FROM price LIMIT 0;"
echo "DROP TABLE IF EXISTS tmp_pludescr;"
echo "CREATE TEMPORARY TABLE tmp_pludescr SELECT * FROM pludescr LIMIT 0;"
echo "DROP TABLE IF EXISTS tmp_deptdescr;"
echo "CREATE TEMPORARY TABLE tmp_deptdescr SELECT * FROM deptdescr LIMIT 0;"


echo "DROP TABLE IF EXISTS tmp_imp;"
echo "CREATE TABLE tmp_imp (id INT PRIMARY KEY, mult INT);"
echo "INSERT INTO tmp_imp (id, mult) VALUES (1, 1);";



for lid in `cat /tmp/CSV_IMPORT_PRICEID_LIST`
do
  echo "REPLACE INTO tmp_imp (id, mult) SELECT 1, 100 FROM import WHERE PRICE_$lid LIKE '%.%' OR PRICE_$lid LIKE '%,%' LIMIT 1;"
done  
  

echo "DELETE FROM import WHERE $EMPTY_DESCR_SQL;" 
echo "DELETE FROM import WHERE DEP=0 AND PLU=0;"


NEWPLU_IMPORT_SET="(plucode,dept,hhtkey,category,varprice";
CSV_EXPORT_SET="PLU,DEP,HHTKEY,CAT,VAR";
BASE_IMPORT_DESCR_FIELD="DESCR_1"
BASE_IMPORT_PRICE_FIELD="PRICE_1"

if echo $L | grep "DESCR_1" >/dev/null
then
  NEWPLU_IMPORT_SET=$NEWPLU_IMPORT_SET",descr";
  CSV_EXPORT_SET=$CSV_EXPORT_SET",DESCR_1"; 
else
  if echo $L | grep "DESCR" >/dev/null
  then
    BASE_IMPORT_DESCR_FIELD="DESCR"
    NEWPLU_IMPORT_SET=$NEWPLU_IMPORT_SET",descr";
    CSV_EXPORT_SET=$CSV_EXPORT_SET",DESCR"; 
  fi
fi


if echo $L | grep "PRICE_1" >/dev/null
then
  NEWPLU_IMPORT_SET=$NEWPLU_IMPORT_SET",price";
  CSV_EXPORT_SET=$CSV_EXPORT_SET",PRICE_1"; 
else
  if echo $L | grep "PRICE1" >/dev/null
  then
    BASE_IMPORT_PRICE_FIELD="PRICE1"
    NEWPLU_IMPORT_SET=$NEWPLU_IMPORT_SET",price";
    CSV_EXPORT_SET=$CSV_EXPORT_SET",PRICE1"; 
    echo "REPLACE INTO tmp_imp (id, mult) SELECT 1, 100 FROM import WHERE price1 LIKE '%.%' OR price1 LIKE '%,%' LIMIT 1;"
  fi
fi

if echo $L | grep "HHTDESCR" >/dev/null
then
  NEWPLU_IMPORT_SET=$NEWPLU_IMPORT_SET",hhtdescr";
  CSV_EXPORT_SET=$CSV_EXPORT_SET",HHTDESCR"; 
fi

if echo $L | grep "BARCODE" >/dev/null
then
  NEWPLU_IMPORT_SET=$NEWPLU_IMPORT_SET",barcode";
  CSV_EXPORT_SET=$CSV_EXPORT_SET",IF(BARCODE='',NULL,BARCODE)"; 
fi

if echo $L | grep "ACT_STOCK" >/dev/null
then
  NEWPLU_IMPORT_SET=$NEWPLU_IMPORT_SET",stock";
  CSV_EXPORT_SET=$CSV_EXPORT_SET",ACT_STOCK"; 
fi

if echo $L | grep "MIN_STOCK" >/dev/null
then
  NEWPLU_IMPORT_SET=$NEWPLU_IMPORT_SET",min_stock";
  CSV_EXPORT_SET=$CSV_EXPORT_SET",MIN_STOCK"; 
fi

if echo $L | grep "INGR" >/dev/null
then
  NEWPLU_IMPORT_SET=$NEWPLU_IMPORT_SET",ingredient";
  CSV_EXPORT_SET=$CSV_EXPORT_SET",INGR"; 
fi

if echo $L | grep "PURCHASE" >/dev/null
then
  NEWPLU_IMPORT_SET=$NEWPLU_IMPORT_SET",purchase_price";
  CSV_EXPORT_SET=$CSV_EXPORT_SET",PURCHASE"; 
fi

if echo $L | grep "QRORDER" >/dev/null
then
  NEWPLU_IMPORT_SET=$NEWPLU_IMPORT_SET",qrorder";
  CSV_EXPORT_SET=$CSV_EXPORT_SET",QRORDER"; 
fi

NEWPLU_IMPORT_SET=$NEWPLU_IMPORT_SET")";

if [ $CLEAN_IMPORT -eq 0 ]
then
##  echo "SELECT '#### DEBUG ####  Check PLU / DEPT for conflicts during update .....';"

  echo "SELECT import.PLU, CONCAT('\"',import.DESCR_1,'\"'), CONCAT('\"',newplu.descr,'\"')  FROM import LEFT JOIN newplu ON import.PLU=newplu.plucode WHERE import.PLU>0 AND import.DEP>0 AND import.EXPORTED!=1 AND NOT ISNULL(newplu.plucode)" | $SQL | tr ' ' '~' | while read PLUID NEWPLUDESCR OLDPLUDESCR
  do 
    touch /tmp/CSV_IMPORT_CONFLICTS
    echo " $PLUID" >> /tmp/CSV_IMPORT_CONFLICT_PLUID_LIST
    NEWPLUDESCR=`echo $NEWPLUDESCR | tr '~' ' '`
    OLDPLUDESCR=`echo $OLDPLUDESCR | tr '~' ' '`
    echo "Nuovo PLU $PLUID [$NEWPLUDESCR] in conflitto con quello esistente [$OLDPLUDESCR]" >> /tmp/CSV_RESULT
    echo "DELETE FROM import WHERE EXPORTED!=1 AND PLU=$PLUID;"  
  done  

  echo "SELECT import.PLU, CONCAT('\"',import.DESCR_1,'\"')  FROM import LEFT JOIN newplu ON import.PLU=newplu.plucode WHERE import.PLU>0 AND import.DEP>0 AND import.EXPORTED=1 AND ISNULL(newplu.plucode)" | $SQL | tr ' ' '~' | while read PLUID NEWPLUDESCR
  do 
    touch /tmp/CSV_IMPORT_CONFLICTS
    echo " $PLUID" >> /tmp/CSV_IMPORT_CONFLICT_PLUID_LIST
    NEWPLUDESCR=`echo $NEWPLUDESCR | tr '~' ' '`
    echo "PLU $PLUID [$NEWPLUDESCR] segnalato come da aggiornare ma non esiste in magazzino attuale" >> /tmp/CSV_RESULT
    echo "DELETE FROM import WHERE EXPORTED=1 AND PLU=$PLUID;"  
  done

  echo "SELECT import.DEP, CONCAT('\"',import.DESCR_1,'\"'), CONCAT('\"',department.dept_descr,'\"') FROM import LEFT JOIN department ON import.DEP=department.dept_id WHERE import.PLU=0 AND import.DEP>0 AND import.EXPORTED!=1 AND NOT ISNULL(department.dept_id)" | $SQL | tr ' ' '~'  | while read DEPID NEWDEPDESCR OLDDEPDESCR
  do 
    touch /tmp/CSV_IMPORT_CONFLICTS
    echo " $DEPID" >> /tmp/CSV_IMPORT_CONFLICT_DEPID_LIST
    NEWDEPDESCR=`echo $NEWDEPDESCR | tr '~' ' '`
    OLDDEPDESCR=`echo $OLDDEPDESCR | tr '~' ' '`
    echo "Nuovo Reparto $DEPID [$NEWDEPDESCR] in conflitto con quello esistente [$OLDDEPDESCR]" >> /tmp/CSV_RESULT
    echo "DELETE FROM import WHERE EXPORTED!=1 AND DEP=$DEPID AND PLU=0;"  
  done

  echo "SELECT import.DEP, CONCAT('\"',import.DESCR_1,'\"')  FROM import LEFT JOIN department ON import.DEP=department.dept_id WHERE import.PLU=0 AND import.DEP>0 AND import.EXPORTED=1 AND ISNULL(department.dept_id)" | $SQL | tr ' ' '~'  | while read DEPID NEWDEPDESCR
  do 
    touch /tmp/CSV_IMPORT_CONFLICTS
    echo " $DEPID" >> /tmp/CSV_IMPORT_CONFLICT_DEPID_LIST
    NEWDEPDESCR=`echo $NEWDEPDESCR | tr '~' ' '`
    echo "Reparto $DEPID [$NEWDEPDESCR] segnalato come da aggiornare ma non esiste nel database attuale" >> /tmp/CSV_RESULT
    echo "DELETE FROM import WHERE EXPORTED=1 AND DEP=$DEPID AND PLU=0;"  
  done  

  for pid in `cat /tmp/CSV_IMPORT_CONFLICT_DEPID_LIST`
  do
    echo "SELECT import.PLU, CONCAT('\"',import.DESCR_1,'\"') FROM import WHERE import.DEP = $pid AND import.PLU>0" | $SQL | tr ' ' '~'  | while read PLUID NEWPLUDESCR
    do 
      NEWPLUDESCR=`echo $NEWPLUDESCR | tr '~' ' '`
      echo "PLU $PLUID [$NEWPLUDESCR] appartiene al reparto in conflitto [$pid]" >> /tmp/CSV_RESULT
      echo "DELETE FROM import WHERE PLU=$PLUID;"
    done  
  done
fi
## End of conflicts check made when CLEAN_IMPORT is not choosen

if [ -f /tmp/CSV_IMPORT_CONFLICTS ]
then
  echo "<br>" >> /tmp/CSV_RESULT
fi


echo "UPDATE import SET $BASE_IMPORT_PRICE_FIELD='0' WHERE $BASE_IMPORT_PRICE_FIELD='';"

for lid in `cat /tmp/CSV_IMPORT_LANGID_LIST`
do
  echo "REPLACE INTO langname (lang_id,lang_name) SELECT $lid,IF(langname.lang_name!='',langname.lang_name,IF(1=$lid,'Italiano','Lingua_$lid')) FROM import LEFT JOIN langname ON langname.lang_id=$lid LIMIT 1;"
done

echo "SELECT IFNULL(MAX(list_pos),0) FROM department" | $SQL | while read DMAXPOS
do 
  echo " $DMAXPOS" > /tmp/CSV_IMPORT_DEPT_MAXPOS
done  

#rm -f /tmp/sql_escape_trace
#echo "SELECT '#### DEBUG ####  Inserting new departments .....';"

## INSERT NEW DEPARTMENTS ....

DMAX_POS=`cat /tmp/CSV_IMPORT_DEPT_MAXPOS`

#ps auxwf > /tmp/process_list

echo "SELECT DEP, $BASE_IMPORT_DESCR_FIELD FROM import WHERE EXPORTED!=1 AND PLU=0 AND DEP>0" | tee /tmp/tmp_sql_in | $SQL | tee /tmp/tmp_sql_out | tr ' ' '~'  | while read DEPID DEPDESCR
#echo "SELECT 1,'zatracenej script'" | $SQL | tee /tmp/tmp_sql | tr ' ' '~'  | while read DEPID DEPDESCR
do
  DMAX_POS=$((DMAX_POS+1))
 # echo -n " orig [$DEPDESCR]" >> /tmp/sql_escape_trace
  DEPDESCR=`echo $DEPDESCR | tr '~' ' '`
 # echo -n " unscrambled [$DEPDESCR]" >> /tmp/sql_escape_trace
  DEPDESCR=`escape_sql "$DEPDESCR"`
 # echo " escaped [$DEPDESCR]" >> /tmp/sql_escape_trace
  echo "REPLACE INTO department (dept_id, list_pos, dept_descr, vat_id) VALUES ( $DEPID, $DMAX_POS, '$DEPDESCR', 1 );"

# for lid in `cat /tmp/CSV_EXPORT_LANGID_LIST`
# do  
#   if [ $lid -gt 1 ]
#   then
#     echo "DELETE FROM tmp_deptdescr;" 
#     echo "INSERT INTO tmp_deptdescr (lang_id,dept_id,descr) SELECT deptdescr.lang_id,deptdescr.dept_id,IF(import.DESCR_$lid!='',import.DESCR_$lid,deptdescr.descr) FROM import LEFT JOIN deptdescr ON deptdescr.dept_id=import.DEP AND import.PLU=0 AND deptdescr.lang_id=$lid;"
#     echo "DELETE FROM tmp_deptdescr WHERE lang_id=$lid AND dept_id=$DEPID AND descr='';"
#     echo "REPLACE INTO deptdescr SELECT * from tmp_deptdescr;"  
#   fi    
# done
done


#echo "SELECT '#### DEBUG ####  Inserting new PLUs .....';"

## INSERT NEW PLUS ....
echo "REPLACE INTO newplu $NEWPLU_IMPORT_SET SELECT $CSV_EXPORT_SET FROM import WHERE PLU>0 AND DEP>0 AND EXPORTED!=1;"
#echo "SELECT newplu.plu_id FROM import LEFT JOIN newplu ON newplu.plucode=import,PLU WHERE EXPORTED!=1 AND PLU=0 AND DEP>0" | $SQL | while read PLUID
#do
#  for lid in `cat /tmp/CSV_EXPORT_PRICEID_LIST`
#  do
#    echo "DELETE FROM tmp_price;"
#    echo "INSERT INTO tmp_price (price_id,uprice,saleqty,plu_id) SELECT price.price_id,IF(import.PRICE_$lid!='',import.PRICE_$lid,price.uprice),price.saleqty,price.plu_id FROM price LEFT JOIN import ON price.plu_id=import.PLU_ID AND price.price_id=$lid;"
#    echo "REPLACE INTO price SELECT * from tmp_price;"
#  done
#done

## UPDATE DB table newplu ....

##echo "SELECT '#### DEBUG ####  Updating existing PLUs .....';"


TARGET_FIELD_LIST=""
for tid in `echo $FULL_NEWPLU_FIELD_SET | tr ',' '\n'`
do
  if [ -n "$TARGET_FIELD_LIST" ]
  then 
    TARGET_FIELD_LIST=$TARGET_FIELD_LIST","
  fi
  IMPORT_FIELD=`newplu2import $tid`
  if [ -n "$IMPORT_FIELD" ] && cat /tmp/CSV_IMPORT_FIELD_LIST | grep -q "$IMPORT_FIELD"
  then
    TARGET_FIELD_LIST=$TARGET_FIELD_LIST"IF(import.$IMPORT_FIELD!='',import.$IMPORT_FIELD,newplu.$tid)"
  else
    TARGET_FIELD_LIST=$TARGET_FIELD_LIST"newplu.$tid"
  fi
done
echo "DELETE FROM tmp_newplu;"
echo "INSERT INTO tmp_newplu ($FULL_NEWPLU_FIELD_SET) SELECT $TARGET_FIELD_LIST FROM newplu LEFT JOIN import ON newplu.plucode=import.PLU;"
echo "REPLACE INTO newplu SELECT * from tmp_newplu;"

if echo $L | grep "ART_INFO" >/dev/null
then
  echo "REPLACE INTO plu_optinfo (plucode, opt_descr) SELECT PLU, ART_INFO FROM import WHERE PLU>0 AND DEP>0 AND EXPORTED!=1 AND ART_INFO!=\"\";"
fi

##echo "SELECT '#### DEBUG ####  Updating existing departments .....';"

## UPDATE DB table department ....

TARGET_FIELD_LIST=""
for tid in `echo $FULL_DEPT_FIELD_SET | tr ',' '\n'`
do
  if [ -n "$TARGET_FIELD_LIST" ]
  then 
    TARGET_FIELD_LIST=$TARGET_FIELD_LIST","
  fi  
  IMPORT_FIELD=`dept2import $tid`
  if [ -n "$IMPORT_FIELD" ] && cat /tmp/CSV_IMPORT_FIELD_LIST | grep -q $IMPORT_FIELD
  then
    TARGET_FIELD_LIST=$TARGET_FIELD_LIST"IF(import.$IMPORT_FIELD!='',import.$IMPORT_FIELD,department.$tid)"
  else
    TARGET_FIELD_LIST=$TARGET_FIELD_LIST"department.$tid"
  fi
done
echo "DELETE FROM tmp_department;"
echo "INSERT INTO tmp_department ($FULL_DEPT_FIELD_SET) SELECT $TARGET_FIELD_LIST FROM department LEFT JOIN import ON department.dept_id=import.DEP AND import.PLU=0 AND import.DEP>0;"
echo "REPLACE INTO department SELECT * from tmp_department;"


##echo "SELECT '#### DEBUG ####  Updating prices .....';"


## UPDATE DB table price ....

if [ -f /tmp/CSV_IMPORT_PRICEID_LIST ]
then
  for lid in `cat /tmp/CSV_IMPORT_PRICEID_LIST`
  do
    echo "UPDATE import SET PRICE_$lid=REPLACE(PRICE_$lid, ',', '.');"
    echo "DELETE FROM tmp_price;"
    echo "INSERT INTO tmp_price (price_id,uprice,saleqty,plu_id) SELECT $lid,IF(import.PRICE_$lid!='',import.PRICE_$lid*mult/100,price.uprice),price.saleqty,IF(price.plu_id,price.plu_id,newplu.plu_id) FROM import LEFT JOIN newplu on newplu.plucode=import.PLU LEFT JOIN price ON price.plu_id=newplu.plu_id AND price.price_id=$lid LEFT JOIN tmp_imp ON tmp_imp.mult!='' WHERE import.PRICE_$lid!='' AND import.PLU>0;"
    echo "REPLACE INTO price SELECT * from tmp_price;"
  done
else
  for((i=1; i<10; i++))
  {
    echo "UPDATE import SET price$i=REPLACE(price$i, ',', '.');"
    echo "DELETE FROM tmp_price;"
    echo "INSERT INTO tmp_price (price_id,uprice,saleqty,plu_id) SELECT $i,IF(import.PRICE$i!='',import.PRICE$i*mult/100,price.uprice),price.saleqty,IF(price.plu_id,price.plu_id,newplu.plu_id) FROM import LEFT JOIN newplu on newplu.plucode=import.PLU LEFT JOIN price ON price.plu_id=newplu.plu_id AND price.price_id=$i LEFT JOIN tmp_imp ON tmp_imp.mult!='' WHERE import.PRICE$i!='' AND import.PLU>0;"
    echo "REPLACE INTO price SELECT * from tmp_price;"
  }
fi

##echo "SELECT '#### DEBUG ####  Updating descriptions .....';"

## UPDATE DB tables pludescr and deptdescr ....

if [ -f /tmp/CSV_IMPORT_LANGID_LIST ]
then
  for lid in `cat /tmp/CSV_IMPORT_LANGID_LIST`
  do
    echo "DELETE FROM tmp_pludescr;"
    echo "INSERT INTO tmp_pludescr (lang_id,plu_id,descr) SELECT $lid,IF(pludescr.plu_id,pludescr.plu_id,newplu.plu_id),IF(import.DESCR_$lid!='',import.DESCR_$lid,pludescr.descr) FROM import LEFT JOIN newplu on newplu.plucode=import.PLU LEFT JOIN pludescr ON pludescr.plu_id=newplu.plu_id AND pludescr.lang_id=$lid  WHERE import.DESCR_$lid!='' AND import.PLU>0;"
    echo "REPLACE INTO pludescr SELECT * from tmp_pludescr;"
    if [ $lid -gt 1 ]
    then
      echo "DELETE FROM tmp_deptdescr;"
      echo "INSERT INTO tmp_deptdescr (lang_id,dept_id,descr) SELECT $lid,IFNULL(deptdescr.dept_id,import.DEP),IF(import.DESCR_$lid!='',import.DESCR_$lid,deptdescr.descr) FROM import LEFT JOIN deptdescr ON deptdescr.dept_id=import.DEP AND import.PLU=0 AND deptdescr.lang_id=$lid WHERE import.DESCR_$lid!='';"
      echo "REPLACE INTO deptdescr SELECT * from tmp_deptdescr;"  
    fi
  done
else
  echo "DELETE FROM tmp_pludescr;"
  echo "INSERT INTO tmp_pludescr (lang_id,plu_id,descr) SELECT 1,IF(pludescr.plu_id,pludescr.plu_id,newplu.plu_id),IF(import.$BASE_IMPORT_DESCR_FIELD!='',import.$BASE_IMPORT_DESCR_FIELD,pludescr.descr) FROM import LEFT JOIN newplu on newplu.plucode=import.PLU LEFT JOIN pludescr ON pludescr.plu_id=newplu.plu_id AND pludescr.lang_id=1  WHERE import.$BASE_IMPORT_DESCR_FIELD!='' AND import.PLU>0;"
  echo "REPLACE INTO pludescr SELECT * from tmp_pludescr;"
fi

#rm -f /tmp/CSV_IMPORT_SQLGEN
#
#echo "SELECT PLU FROM import WHERE PLU>0 AND DEP>0 AND EXPORTED=1" | $SQL | while read PLUCODE
#do
#  echo ${NEWPLU_UPDATE_SQL}${PLUCODE} >> /tmp/CSV_IMPORT_SQLGEN 
#done
#
#cat /tmp/CSV_IMPORT_SQLGEN | $SQL > /tmp/CSV_IMPORT_SQLGEN_RESULT 
    
#echo "INSERT INTO newplu $NEWPLU_IMPORT_SET SELECT $CSV_EXPORT_SET FROM import WHERE plu>0 AND dep>0;"
#for((i=1; i<10; i++))
#{
#  echo "UPDATE import SET price$i=REPLACE(price$i, ',', '.');"
#  echo "INSERT INTO price (price_id,plu_id,uprice) SELECT $i,newplu.plu_id,PRICE$i*mult/100 FROM import,newplu,tmp_imp WHERE newplu.plucode=import.PLU AND ($i=1 OR price$i != '');" 
#}
#echo "DROP TABLE tmp_imp;"
#echo "UPDATE newplu SET hhtdescr=descr WHERE hhtdescr='';"
#echo "DELETE FROM pludescr;"
#echo "REPLACE INTO pludescr (lang_id, plu_id, descr) SELECT 1, plu_id, descr FROM newplu;"
#echo "SELECT CONCAT(COUNT(*), ' PLU importati') FROM import;"


echo "SELECT CONCAT(COUNT(*),IF(COUNT(*)!=1,' reparti aggiornati',' reparto aggiornato')) FROM import WHERE EXPORTED=1 AND DEP>0 AND PLU=0;"
echo "SELECT CONCAT(COUNT(*),IF(COUNT(*)!=1,' reparti inseriti o sovrascritti',' reparto inserito o sovrasritto')) FROM import WHERE EXPORTED!=1 AND DEP>0 AND PLU=0;"
echo "SELECT CONCAT(COUNT(*),' PLU ',IF(COUNT(*)!=1,'aggiornati','aggiornato')) FROM import WHERE EXPORTED=1 AND PLU>0;"
echo "SELECT CONCAT(COUNT(*),' PLU ',IF(COUNT(*)!=1,'inseriti o sovrascritti','inserito o sovrascritto')) FROM import WHERE EXPORTED!=1 AND PLU>0;"
echo "SELECT CONCAT('Numero di reparti nel magazzino: ',COUNT(department.dept_id)) FROM department;"
echo "SELECT CONCAT('Numero di PLU nel magazzino: ',COUNT(newplu.plu_id)) FROM newplu;"
} > /tmp/CSV_IMPORT_2_SQLGEN
cat /tmp/CSV_IMPORT_2_SQLGEN | $SQL  >> /tmp/CSV_RESULT 2>>/tmp/CSV_IMPORT_ERROR

if [ $? -gt 0 ]
then
  IMPORT_ERROR=1
fi


if [ $WAITING_PID -gt 0 ]
then
  kill $WAITING_PID
fi

echo "</pre>" >> /tmp/CSV_RESULT


CSV_RESULT_COUNT=`cat /tmp/CSV_RESULT | wc -l`
if [ $CSV_RESULT_COUNT -gt 16 ]
then
  CSV_RESULT=`head -7 /tmp/CSV_RESULT`
  CSV_RESULT=$CSV_RESULT`tail -8 /tmp/CSV_RESULT`
  CSV_RESULT=$CSV_RESULT"<pre><br>.... ulteriori "$(($CSV_RESULT_COUNT-15))" segnalazioni soppresse ....</pre>"
else
  CSV_RESULT=`cat /tmp/CSV_RESULT`
fi

CONVERSION_END_SECS=$SECONDS


if [ $IMPORT_ERROR -gt 0 ]
then
  sysmsg "$TITLE" "Errore durante inserimento nel database. Contattare l'assistenza tecnica  $CSV_RESULT"
else
  if [ -f /tmp/CSV_IMPORT_CONFLICTS ]
  then
    sysquery "$TITLE" "<pre><b>Conversione terminata con seguenti problemi:<b><br><br></pre>$CSV_RESULT<pre><br><br>Volete salvare il report completo dei problemi nel file e3g_import.err ?</b></pre>" "Si, salvare" "No, non fare niente"
    if [ $? == 0 ] ;
    then
      cat /tmp/CSV_RESULT | sed "s/<pre>//g; s/<\/pre>//g; s/<br>/\n/g; s/<p>//g; s/<\/p>//g; s/<b>//g; s/<\/b>//g;" > /mnt/floppy/e3g_import.err
    fi
#   sysmsg "$TITLE" "<b>Conversione terminata con seguenti problemi:<b><br><br>$CSV_RESULT"
  else
    sysmsg "$TITLE" "<pre><b>Conversione terminata con successo<b><br><br></pre>$CSV_RESULT"
  fi   
fi
systools/utf

QUERIES_COUNT=`cat /tmp/CSV_IMPORT_SQLGEN | wc -l`
#sysmsg "$TITLE" "<pre><b>"$(($CONVERSION_END_SECS-$CONVERSION_START_SECS))" seconds elapsed, $QUERIES_COUNT queries executed<b></pre>"
cat /tmp/CSV_RESULT | sed "s/<pre>//g; s/<\/pre>//g; s/<br>/\n/g; s/<p>//g; s/<\/p>//g; s/<b>//g; s/<\/b>//g;" > /tmp/LAST_CSV_IMPORT_INFO
echo -n "Import finished" >> /tmp/LAST_CSV_IMPORT_INFO
if [ -f /tmp/CSV_IMPORT_CONFLICTS ]
then
  echo -n " with error(s). " >> /tmp/LAST_CSV_IMPORT_INFO
else
  echo -n ". " >> /tmp/LAST_CSV_IMPORT_INFO
fi  
echo $(($CONVERSION_END_SECS-$CONVERSION_START_SECS))" seconds elapsed, $QUERIES_COUNT queries executed" >> /tmp/LAST_CSV_IMPORT_INFO

trap - EXIT


