#!/bin/bash

SQL="mysql -Bs --force -uroot -peuro2000 eurotest"
#SQL="cat"
export LANG=en_US.UTF-8
export PATH=`pwd`:$PATH
VOKNO_NAME='Import PLU'
HOST=`hostname`
DEVEL_HOST=0
if [ $HOST == 'sunshine.spintec.it' ]
then
  DEVEL_HOST=1
  SQL="mysql -uroot eurotest"
fi

sysmsg "$VOKNO_NAME" "Inserire dispositivo di salvataggio con i dati da convertire (file 'eurotest.csv')  e premere il bottone OK. La conversione durera' alcuni secondi..."

pushd /mnt/backup >/dev/null

if [ $# > 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; " >/tmp/importfile1

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="ENCLOSED BY '\"'"
fi
TRM="\t"
if echo $L | grep \; >/dev/null
then
  TRM=";"
fi
if echo $L | grep \, >/dev/null
then
  TRM=","
fi



if [ $LINES = 0 ] 
then
  sysmsg "$VOKNO_NAME" "Non sono stati trovati i dati da convertire ($INFILE)"  
  exit 1 
fi

./systools/clear_store eurotest

{
echo "DROP TABLE IF EXISTS import;"
echo "CREATE TABLE import ("
N=1
L=`echo $L | tr ',\\t' ';;' | sed 's/^;/blank;/g' | sed 's/;;/;blank;/g' | sed 's/;;/;blank;/g'`
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 VARCHAR(255) NOT NULL,"
  fi
  if [ "$i" = NOCATSIGN ]
  then
    break
  fi
done
echo "id SMALLINT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY"
echo ");"

echo "LOAD DATA INFILE '/tmp/importfile' INTO TABLE import FIELDS TERMINATED BY '$TRM' $ENCLOSED ;"                          
echo "DROP TABLE tmp_imp;"
echo "CREATE TABLE tmp_imp (id INT PRIMARY KEY, mult INT);"
echo "INSERT INTO tmp_imp (id, mult) VALUES (1, 1);";
echo "REPLACE INTO tmp_imp (id, mult) SELECT 1, 100 FROM import WHERE price1 LIKE '%.%' OR price1 LIKE '%,%' LIMIT 1;"

echo "DELETE FROM import WHERE descr='';" 
echo "DELETE FROM vat;"
echo "INSERT INTO vat SET id=1, vat_val=10;"
echo "DELETE FROM department;"
echo "DELETE FROM btn_color_config;"
echo "INSERT INTO department (dept_id, list_pos, dept_descr, vat_id) SELECT dep, dep, descr, 1 FROM import WHERE plu=0 AND dep>0;"

echo "DELETE FROM import WHERE PLU=0;"

#echo "DELETE FROM newplu;"
#echo "DELETE FROM price;"
#echo "DELETE FROM linkplu;"
#echo "DELETE FROM kbd_grid;"
echo "DELETE FROM dept_btn_grid;"
#echo "DELETE FROM plu_grid;"
echo "DELETE FROM dept_printer_config;"
echo "DELETE FROM dept_group_config;"


NEWPLU_IMPORT_SET="(plucode,descr,dept,hhtkey,category,varprice";
CSV_EXPORT_SET="PLU,DESCR,DEP,HHTKEY,CAT,VAR";

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

NEWPLU_IMPORT_SET=$NEWPLU_IMPORT_SET")";

#echo "insert into newplu (plucode,descr,dept,hhtkey,category,varprice) select PLU,DESCR,DEP,HHTKEY,CAT,VAR from import WHERE plu>0 AND dep>0;"

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 "UPDATE import SET price$i=price$i*100 WHERE price$i LIKE '%.%';"
  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 "drop table import;"
} | $SQL  > /tmp/CSV_RESULT 


if [ $? -gt 0 ]
then
 sysmsg "$VOKNO_NAME" "Errore durante inserimento nel database. Contattare l'assistenza tecnica  $CSV_RESULT"
else
 CSV_RESULT=`cat /tmp/CSV_RESULT` 
 sysmsg "$VOKNO_NAME" "Conversione terminata con sucesso   $CSV_RESULT"
fi

