#!/bin/bash

SQL="mysql -uroot -peuro2000 eurotest"
#SQL="cat"
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
#SQL="cat"

sysmsg "$VOKNO_NAME" "Inserire pendrive con i dati da convertire e premere il bottone OK. La conversione durera' alcuni secondi..."

pushd /mnt/backup >/dev/null
if ! explu -q import /tmp/importdata 
then
  sysmsg "$VOKNO_NAME" "Non sono stati trovati i dati da convertire (europlu.bin, pokusplu.bin, dep_vat.dat)"  
  exit 1 
fi
popd > /dev/null

./systools/clear_store eurotest
# per sicurezza - se e' rimasto dopo il crash precedente
echo "alter table plu_grid drop code_insert;" | $SQL -s -f 

{
 cat /tmp/importdata
 echo "DELETE FROM department;"
 for ((i=1; i<17; i++))
 do
   echo "INSERT INTO department SET dept_descr=\"Reparto $i\";"
 done  
 echo "REPLACE INTO department (dept_id, dept_descr) SELECT dep, descr FROM import WHERE plu=0;"

 echo "DELETE FROM import WHERE plu=0;"
 echo "DELETE FROM newplu;"
 echo "DELETE FROM price;"
 echo "INSERT INTO newplu (plucode,descr,dept,hhtkey,category,varprice) SELECT PLU,DESCR,DEP,HHTKEY,CAT,VAR from import;"
 echo "INSERT INTO price (price_id,plu_id,uprice) SELECT 1,newplu.plu_id,PRICE1 FROM import,newplu WHERE newplu.plucode=import.PLU;" 
 echo "INSERT INTO price (price_id,plu_id,uprice) SELECT 2,newplu.plu_id,PRICE2 FROM import,newplu WHERE newplu.plucode=import.PLU and PRICE2!=0;" 
 echo "INSERT INTO price (price_id,plu_id,uprice) SELECT 3,newplu.plu_id,PRICE3 FROM import,newplu WHERE newplu.plucode=import.PLU and PRICE3!=0;" 
 echo "INSERT INTO price (price_id,plu_id,uprice) SELECT 4,newplu.plu_id,PRICE4 FROM import,newplu WHERE newplu.plucode=import.PLU and PRICE4!=0;" 
 echo "DROP TABLE import;"

# generovani tabulek
 echo "DELETE FROM plu_grid;"
 if [ $DEVEL_HOST -eq 0 ]
 then
   echo "DELETE FROM kbd_grid;"
 fi
 echo "alter table plu_grid add column code_insert smallint(5);"
 echo "alter table plu_grid add index (code_insert);"
 echo "alter table plu_grid modify column code_insert smallint(5) not null auto_increment;"
 echo "set @last_cid:=0;"
 for ((i=1; i<17; i++))
 do
   echo "insert into plu_grid (descr,plucode,dept,category,btn_idx,fg_rgb,bg_rgb) select descr,plucode,dept,category,0,0xffffff,IF(category=0,0x557f,0x55aa7f) from newplu where dept=$i and category=0;"
   echo "update plu_grid set btn_idx=MOD(code_insert-1-@last_cid,27) WHERE code_insert>@last_cid+0;"
   echo "SELECT @max_cid:=MAX(code_insert) FROM plu_grid;" 
#   echo "SELECT * FROM plu_grid;"
   echo "delete from plu_grid where code_insert>(27+@last_cid);"
#   echo "SELECT @last_cid,@max_cid;" 
   echo "SELECT @last_cid:=IF(@max_cid+0>@last_cid+0,@max_cid+0,@last_cid+0);" 
#   echo "SELECT * FROM plu_grid;"
   
   echo "insert into plu_grid (descr,plucode,dept,category,btn_idx,fg_rgb,bg_rgb) select descr,plucode,dept,category,0,0xffffff,IF(category=0,0x557f,0x55aa7f) from newplu where dept=$i and category=1;"
   echo "update plu_grid set btn_idx=MOD(code_insert-1-@last_cid,27) WHERE code_insert>@last_cid+0;"
   echo "SELECT @max_cid:=MAX(code_insert) FROM plu_grid;" 
#   echo "SELECT * FROM plu_grid;"
   echo "delete from plu_grid where code_insert>(27+@last_cid);"
#   echo "SELECT @last_cid,@max_cid;" 
   echo "SELECT @last_cid:=IF(@max_cid+0>@last_cid+0,@max_cid+0,@last_cid+0);" 
#   echo "SELECT * FROM plu_grid;"
   
 done
 echo "alter table plu_grid drop code_insert;"

} | $SQL 2>&1 > /dev/null
if [ $? -gt 0 ]
then
 sysmsg "$VOKNO_NAME" "Errore durante inserimento nel database. Contattare l'assistenza tecnica"
else
 sysmsg "$VOKNO_NAME" "Conversione terminata con sucesso"
fi

