#!/bin/bash

PLUEXPORT_FNAME='/tmp/pluexport.csv'

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

if [ $# > 0 ]
then
  OUTFILE=$1
else
  OUTFILE='pluexport.csv'
fi
    

systools/utf
sysmsg "$VOKNO_NAME" "Inserire dispositivo di salvataggio sul quale salvare i dati esportati (file 'eurotest.csv') e premere il bottone OK.<br><br>La procedura durera' alcuni secondi..."
systools/mtf


rm $PLUEXPORT_FNAME
rm ${PLUEXPORT_FNAME}.data


rm -f /tmp/CSV_EXPORT_PRICEID_LIST
rm -f /tmp/CSV_EXPORT_LANGID_LIST
echo "DEP CAT VAR PLU" > /tmp/CSV_EXPORT_FIELD_LIST
echo "SELECT lang_id FROM langname" | $SQL | while read LANGID
do 
  echo " $LANGID" >> /tmp/CSV_EXPORT_LANGID_LIST
  echo " DESCR_$LANGID" >> /tmp/CSV_EXPORT_FIELD_LIST
done

if [ ! -f /tmp/CSV_EXPORT_LANGID_LIST ]
then
  echo " 1" >> /tmp/CSV_EXPORT_LANGID_LIST
  echo " DESCR_1" >> /tmp/CSV_EXPORT_FIELD_LIST
fi

echo "SELECT price_id FROM pricename" | $SQL | while read PRICEID
do 
  echo " $PRICEID" >> /tmp/CSV_EXPORT_PRICEID_LIST
  echo " PRICE_$PRICEID" >> /tmp/CSV_EXPORT_FIELD_LIST
done  

echo " HHTKEY INGR BARCODE ACT_STOCK MIN_STOCK PURCHASE MEAL LINKED QRORDER EXPORTED ALT_IDX NOCATSIGN" >> /tmp/CSV_EXPORT_FIELD_LIST

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

HEADER_SEP=''
for fid in `cat /tmp/CSV_EXPORT_FIELD_LIST`
do
  FIELDLEN=205
  case "$fid" in
  DEP) FIELDLEN=25 ;;
  CAT) FIELDLEN=25 ;;
  VAR) FIELDLEN=25 ;;
  PLU) FIELDLEN=25 ;;
  HHTKEY) FIELDLEN=25 ;;
  INGR) FIELDLEN=25 ;;
  ACT_STOCK) FIELDLEN=25 ;;
  MIN_STOCK) FIELDLEN=25 ;;
  PURCHASE) FIELDLEN=25 ;;
  MEAL) FIELDLEN=25 ;;
  LINKED) FIELDLEN=25 ;;
  QRORDER) FIELDLEN=25 ;;
  EXPORTED) FIELDLEN=25 ;;
  ALT_IDX) FIELDLEN=25 ;;
  NOCATSIGN) FIELDLEN=25 ;;
  *) FIELDLEN=205
  esac
  
  echo "$fid VARCHAR($FIELDLEN) NOT NULL,"
  echo -n $HEADER_SEP'"'$fid'"' >> $PLUEXPORT_FNAME
  HEADER_SEP=';'
done  

#echo "id SMALLINT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY"
echo "PLU_ID mediumint(8) NOT NULL "
echo ");"

echo '"PLU_ID"' >> /tmp/CSV_EXPORT_FIELD_LIST
#echo '"PLU_ID"' >> $PLUEXPORT_FNAME
echo  >> $PLUEXPORT_FNAME

echo "DROP TABLE IF EXISTS tmp_csv_export;"
echo "ALTER TABLE csv_export ADD UNIQUE KEY plu_dept_idx (DEP,PLU);"
#echo "ALTER TABLE csv_export ADD plu_id mediumint(8) NOT NULL;" 
#echo "ALTER TABLE csv_export ADD PRIMARY KEY  (`plu_id`);"
echo "CREATE TEMPORARY TABLE tmp_csv_export SELECT * FROM csv_export LIMIT 0;"
echo "INSERT INTO csv_export (DEP,DESCR_1) SELECT dept_id,dept_descr FROM department;"


DESCR_FIELD_LIST=""
TMP_DESCR_FIELD_LIST=""
SELECT_DESCR_FIELD_LIST=""
for lid in `cat /tmp/CSV_EXPORT_LANGID_LIST`
do
  DESCR_FIELD_LIST=$DESCR_FIELD_LIST",DESCR_$lid"
  TMP_DESCR_FIELD_LIST=$TMP_DESCR_FIELD_LIST",tmp_csv_export.DESCR_$lid"
  SELECT_DESCR_FIELD_LIST=$SELECT_DESCR_FIELD_LIST",''"
done

PRICE_FIELD_LIST=""
TMP_PRICE_FIELD_LIST=""
SELECT_PRICE_FIELD_LIST=""
for lid in `cat /tmp/CSV_EXPORT_PRICEID_LIST`
do
  PRICE_FIELD_LIST=$PRICE_FIELD_LIST",PRICE_$lid"
  TMP_PRICE_FIELD_LIST=$TMP_PRICE_FIELD_LIST",tmp_csv_export.PRICE_$lid"
  SELECT_PRICE_FIELD_LIST=$SELECT_PRICE_FIELD_LIST",''"
done

BASE_QUERY="REPLACE INTO csv_export (DEP"$DESCR_FIELD_LIST
MID_QUERY=") SELECT tmp_csv_export.DEP"$TMP_DESCR_FIELD_LIST


for lid in `cat /tmp/CSV_EXPORT_LANGID_LIST`
do
  if [ $lid -gt 1 ]
  then
    echo "REPLACE INTO tmp_csv_export select * from csv_export;"
    PATCHED_MID_QUERY=`echo $MID_QUERY | sed "s/tmp_csv_export.DESCR_$lid/deptdescr.descr/"`    
    echo ${BASE_QUERY}${PATCHED_MID_QUERY}" FROM tmp_csv_export LEFT JOIN deptdescr ON deptdescr.dept_id=tmp_csv_export.DEP WHERE deptdescr.lang_id=$lid;"
#    echo "REPLACE INTO csv_export (DEP,DESCR_1,DESCR_2,DESCR_3) SELECT tmp_csv_export.DEP,tmp_csv_export.DESCR_1,deptdescr.descr,tmp_csv_export.DESCR_3 FROM tmp_csv_export LEFT JOIN deptdescr ON deptdescr.dept_id=tmp_csv_export.DEP WHERE deptdescr.lang_id=2;"
  fi    
done  







BASE_QUERY="REPLACE INTO csv_export (DEP,CAT,VAR,PLU"${DESCR_FIELD_LIST}${PRICE_FIELD_LIST}",HHTKEY,INGR,BARCODE,ACT_STOCK,MIN_STOCK,PURCHASE,MEAL,LINKED,QRORDER,EXPORTED,ALT_IDX,NOCATSIGN,PLU_ID) "
MID_QUERY=" SELECT tmp_csv_export.DEP,tmp_csv_export.CAT,tmp_csv_export.VAR,tmp_csv_export.PLU"${TMP_DESCR_FIELD_LIST}${TMP_PRICE_FIELD_LIST}"
	    ,tmp_csv_export.HHTKEY,tmp_csv_export.INGR,tmp_csv_export.BARCODE,tmp_csv_export.ACT_STOCK,tmp_csv_export.MIN_STOCK,tmp_csv_export.PURCHASE
	    ,tmp_csv_export.MEAL,tmp_csv_export.LINKED,tmp_csv_export.QRORDER,tmp_csv_export.EXPORTED,tmp_csv_export.ALT_IDX,tmp_csv_export.NOCATSIGN,tmp_csv_export.PLU_ID"
	    
	    
echo ${BASE_QUERY}" SELECT dept,category,varprice,plucode"${SELECT_DESCR_FIELD_LIST}${SELECT_PRICE_FIELD_LIST}",hhtkey,ingredient,barcode,stock,min_stock,purchase_price,'',linked,qrorder,1,'','',plu_id FROM newplu;"
	    
for lid in `cat /tmp/CSV_EXPORT_LANGID_LIST`
do
  echo "REPLACE INTO tmp_csv_export select * from csv_export;"
  PATCHED_MID_QUERY=`echo $MID_QUERY | sed "s/tmp_csv_export.DESCR_$lid/pludescr.descr/"`    
  echo ${BASE_QUERY}${PATCHED_MID_QUERY}" FROM tmp_csv_export LEFT JOIN pludescr ON pludescr.plu_id=tmp_csv_export.PLU_ID WHERE pludescr.lang_id=$lid;"
done  


for lid in `cat /tmp/CSV_EXPORT_PRICEID_LIST`
do
  echo "REPLACE INTO tmp_csv_export select * from csv_export;"
  PATCHED_MID_QUERY=`echo $MID_QUERY | sed "s/tmp_csv_export.PRICE_$lid/FORMAT(0.0+price.uprice,2)/"`    
  echo ${BASE_QUERY}${PATCHED_MID_QUERY}" FROM tmp_csv_export LEFT JOIN price ON price.plu_id=tmp_csv_export.PLU_ID WHERE price.price_id=$lid;"
done  


for lid in `cat /tmp/CSV_EXPORT_PRICEID_LIST`
do
  echo "UPDATE csv_export SET PRICE_$lid='' WHERE PLU=0;"
done  


#echo "REPLACE INTO csv_export (DEP,CAT,VAR,PLU,DESCR_1,DESCR_2,DESCR_3,PRICE_1,PRICE_2,PRICE_3,PRICE_4,PRICE_5,PRICE_6,HHTKEY,INGR,BARCODE,ACT_STOCK,MIN_STOCK,PURCHASE,MEAL,LINKED,QRORDER,EXPORTED,ALT_IDX,NOCATSIGN) SELECT dept,category,0,plucode,descr,'','',price,'','','','','',hhtkey,ingredient,barcode,stock,min_stock,purchase_price,'',linked,qrorder,1,'','' FROM newplu;"

echo "UPDATE csv_export SET EXPORTED='1';"
#echo "INSERT INTO cvs_export (DEP,DESCR_1) SELECT dept_id,dept_descr FROM department;"

echo "REPLACE INTO tmp_csv_export select * from csv_export;"
echo "ALTER TABLE csv_export DROP PLU_ID;"

echo "SELECT * INTO OUTFILE '${PLUEXPORT_FNAME}.data' FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n' FROM csv_export ORDER BY DEP,CAT,DESCR_1;" 

echo "SELECT CONCAT(COUNT(*),IF(COUNT(*)!=1,' reparti esportati',' reparto esportato')) FROM csv_export WHERE DEP>0 AND PLU=0;"
echo "SELECT CONCAT(COUNT(*),IF(COUNT(*)!=1,' PLU esportati','PLU esportato')) FROM csv_export WHERE PLU>0;"

} | tee /tmp/CSV_EXPORT_SQLGEN | $SQL  > /tmp/CSV_EXPORT_RESULT 2>/tmp/CSV_EXPORT_ERROR

SQL_RETVAL=$?

cat ${PLUEXPORT_FNAME}.data >> ${PLUEXPORT_FNAME}
EXPORT_COUNT=`cat ${PLUEXPORT_FNAME}.data | wc -l`
rm ${PLUEXPORT_FNAME}.data


if [ $SQL_RETVAL -eq 0 ]
then 
  cp ${PLUEXPORT_FNAME} /mnt/floppy/$OUTFILE 2>>/tmp/CSV_EXPORT_ERROR
  SQL_RETVAL=$?
fi
 
sync

systools/utf

if [ $SQL_RETVAL -gt 0 ]
then
 CSV_RESULT=`cat /tmp/CSV_EXPORT_ERROR`
 sysmsg "$VOKNO_NAME" "<pre>Errore durante export dei dati. Contattare l'assistenza tecnica <br>Messaggio diagnostico:<br><br>$CSV_RESULT</pre>"
 exit 1
else
 CSV_RESULT=`cat /tmp/CSV_EXPORT_RESULT`
 sysmsg "$VOKNO_NAME" "<pre>Procedura terminata con sucesso.<br><br>$CSV_RESULT</pre>"
fi


exit 0


