#!/bin/bash
PATH=/usr/bin:/bin:/usr/sbin:/sbin:/usr/bin/X11:/usr/games:/usr/local/bin:/root/bin

cd /opt/euro-beta
SERVER=`systools/get_eurodb_config_val BizerbaSyncServer`
USER=`systools/get_eurodb_config_val BizerbaSyncUser`
PSWD=`systools/get_eurodb_config_val BizerbaSyncPassword`

#SERVER=192.168.1.100
#USER=gestore
if [ ! "$SERVER" ]; then  exit; fi

cd /tmp

DATFILE=BZ00VARP.DAT
DESTFILE='c:/bizerba/import/'$DATFILE

DST="$USER@$SERVER"
MYSQL='mysql -BN -ueuro3g -peuro3g eurotest'


function update_barcodes
{
X=`echo '
SELECT
  newplu.plu_id
 FROM newplu
 LEFT JOIN dept_group_config ON dept_group_config.dept_id=newplu.dept 
 LEFT JOIN dept_group ON dept_group.id=dept_group_config.dgrp_id 
 WHERE dept_group.descr="*BILANCIA"
  AND newplu.barcode IS NULL
 LIMIT 100;
' | $MYSQL`

PLUS=`echo $X | sed s'/ /,/g'`
if [ "$PLUS" ]; then
  echo 'UPDATE newplu SET barcode=CONCAT("2",LPAD(newplu.plucode,6,"0")) WHERE plu_id IN ('"$PLUS"');' | $MYSQL
fi
}

function update_varprice
{
X=`echo '
SELECT
  newplu.plu_id
 FROM newplu
 LEFT JOIN dept_group_config ON dept_group_config.dept_id=newplu.dept 
 LEFT JOIN dept_group ON dept_group.id=dept_group_config.dgrp_id 
 WHERE dept_group.descr="*BILANCIA"
  AND newplu.varprice=0
 LIMIT 100;
' | $MYSQL`

PLUS=`echo $X | sed s'/ /,/g'`
if [ "$PLUS" ]; then
  echo 'UPDATE newplu SET varprice=1 WHERE plu_id IN ('"$PLUS"');' | $MYSQL
fi
}


echo '
SELECT
 CONCAT(
#  LPAD(department.list_pos,2,"0"),LPAD(newplu.plucode,4,"0"),
  "01",LPAD(newplu.plucode,4,"0"),
        "0005",
        LPAD(FLOOR(price.uprice*100),6, " "),
        "2",LPAD(newplu.plucode,6,"0"),"000000",
        "55", RPAD(pludescr.descr,20," "), "|", RPAD(   REGEXP_REPLACE( IFNULL(plu_optinfo.opt_descr,""), "\\n.*","")   ,20," "),
        "   0",
        "  0000",
        "000",
        "0",
        "01",
        "0",
        "0",
        "0",
        "@",
        "000000",
        "A"
 ) AS HDR
 FROM newplu
 LEFT JOIN department ON department.dept_id=newplu.dept
 LEFT JOIN pludescr ON pludescr.plu_id=newplu.plu_id AND lang_id=1
 LEFT JOIN plu_optinfo ON plu_optinfo.plucode=newplu.plucode
 LEFT JOIN price ON price.plu_id=newplu.plu_id AND price.price_id=1
 LEFT JOIN dept_group_config ON dept_group_config.dept_id=newplu.dept 
 LEFT JOIN dept_group ON dept_group.id=dept_group_config.dgrp_id 
 WHERE dept_group.descr="*BILANCIA";

' | $MYSQL | sed 's/$/\r/' > $DATFILE

TO_DELETE=`(
  echo '
  SELECT
    newplu.plucode
  FROM newplu
  LEFT JOIN department ON department.dept_id=newplu.dept
  LEFT JOIN dept_group_config ON dept_group_config.dept_id=newplu.dept 
  LEFT JOIN dept_group ON dept_group.id=dept_group_config.dgrp_id 
  WHERE dept_group.descr="*BILANCIA"
  ;
  ' | $MYSQL

  ssh $DST 'sqlcmd -S .\BIZERBA -U bizerba -P desio172 -Q "SELECT plu,1 FROM MultiTraceConnect.dbo.PLU"' | sed 's/^ *//; s/\r//; s/^(.*//' | ( read; read; cat ) 
) | sort -n | awk '/^[0-9]+$/ {last=$1; next} // {if (last!=$1) print $1}' | head -20` 

if [ `stat -c %s $DATFILE` -gt 0 ] 
then 
  update_barcodes
  update_varprice

  for D in $TO_DELETE
  do
    ssh $DST 'sqlcmd -S .\BIZERBA -U bizerba -P desio172 -Q "UPDATE MultiTraceConnect.dbo.PLU SET WALO = 3, FLAG_VAR = 1 WHERE plu = '$D'"' >/dev/null
  done
  scp $DATFILE $DST:$DESTFILE
fi


