#!/bin/bash
LOGDIR=/var/opt/bukdbase/log
### значения по умолчанию (переопределяются ключами в командной строке)
DBNAME=ubuk
DBUSER=bukts
IDSHOP=
SHIFT=
SED=

if [ ! -d $LOGDIR ]; then
    LOGDIR=$HOME
fi

## сформировать имя log-файла из имени программы
LOG=$LOGDIR/$(basename $0).$(date +%y%m%d).log
echo >> $LOG
echo "$(date +%H:%M:%S) start $@" >> $LOG

### обработать командную строку
i=$#;  # число параметров в командной строке
while [ $i -ge "0" ]; do
    is="no"
    case $1 in
    -db) # имя БД
        is="yes"; DBNAME=$2 ;;
    -user|-U) # пользователь БД
        is="yes"; DBUSER=$2 ;;
    -shop) 
        is="yes"; IDSHOP=$2 ;;
    -sh|-shift) # номер смены (может быть "last" и "cur")
        is="yes"; SHIFT=$2  ;;
    -op)
        is="yes"; O_PATH=$2  ;;
    -sed)
        is="yes"; SED=$2  ;;
    esac
    shift
    if [ $is == "yes" ]; then
        shift
        i=`expr $i - 2`
    else
        i=`expr $i - 1`
    fi
done

if [ -z "$SED" ]; then
    SED=sed
fi
### определение вспомогательной переменной, для удобства последующих записей
psql="psql $DBNAME -U $DBUSER"
# используемые ниже ключи psql:
# -A - неформатированный вывод
# -t - выводить только строки
# -q - выводить только результат запроса
# -F - задать разделитель полей
# -P null='null' - выводить поля null, как указано в кавычках

### получить номер магазина (IDSHOP)
if [ -z "$IDSHOP" ]; then
    SQL="select id from sd_ident where id_shop_type=0 order by id desc limit 1"
    IDSHOP=$($psql -Atq -c "$SQL")
    if [ -z "$IDSHOP" ]; then
        MSG="Ошибка при определении номера магазина"
        echo $MSG >& 2
        echo "$(date +%H:%M:%S) $MSG" >> $LOG
        exit 1
    fi
fi

### определить номер смены
if [ -z "$SHIFT" -o "$SHIFT" == "cur" ]; then
    SQL="select max(num) from sj_shifts where id_shop=$IDSHOP limit 1"
    SHIFT=$($psql -Atq -c "$SQL")
elif [ "$SHIFT" == "last" ]; then
    SQL="select max(num) from sj_shifts where id_shop=$IDSHOP and time_end is not null limit 1"
    SHIFT=$($psql -Atq -c "$SQL")
fi

if [ -z "$SHIFT" ]; then
    MSG="Не задан номер смены"
    echo $MSG >& 2
    echo "$(date +%H:%M:%S) $MSG" >> $LOG
    exit 1
fi

# определить, существует ли смена, и ее статус (открыта или закрыта)
SQL="select time_beg, time_end from sj_shifts where id_shop=$IDSHOP and num=$SHIFT"
TMP=$($psql -Atq -c "$SQL")

if [ -z "$(echo $TMP | cut -d'|' -f1)" ]; then
    MSG="Нет ответа на запрос смены $SHIFT."
    echo $MSG >& 2
    echo "$(date +%H:%M:%S) $MSG" >> $LOG
    exit 1
fi

if [ -z "$(echo $TMP | cut -d'|' -f2)" ]; then
    SHIFT_STAT="open"
else
    SHIFT_STAT="closed"
fi

OFILE=$O_PATH/t.$IDSHOP.$SHIFT.$SHIFT_STAT
OFILE=${OFILE}.sql

## удалить если он существует
rm -f ${OFILE}* 2>/dev/null

### вывести в выходной файл необходимую информацию
echo "--DBNAME:$DBNAME" >> $OFILE
echo "--IDSHOP:$IDSHOP" >> $OFILE
echo "--SHIFT:$SHIFT" >> $OFILE
echo "--SHSTAT:$SHIFT_STAT" >> $OFILE

### получить номера первой и последней транзакций заданной смены (TRAN1, TRAN2)
SQL="select min(trannum), max(trannum) from sj_tranz \
 where id_shop=$IDSHOP and shift = $SHIFT"
TRANS=$($psql -Atq -F' ' -c "$SQL")
TRAN1=$(echo $TRANS | cut -d' ' -f1)
TRAN2=$(echo $TRANS | cut -d' ' -f2)
echo "--TRANs:$TRAN1,$TRAN2" >> $OFILE

###$psql -c "update gj_log_m set document_num=' ' where document_num=''" >> $OFILE
# получение из БД таблиц, перечисленных в TABLES1
echo "DELETE FROM credit2.sj_shifts WHERE id_shop=$IDSHOP AND num=$SHIFT;" >> $OFILE
SQL="select 'INSERT INTO credit2.sj_shifts VALUES ($IDSHOP, $SHIFT, TO_TIMESTAMP('''||TO_CHAR(time_beg,'YYYY-MM-DD HH24:MI:SS')||''',''YYYY-MM-DD HH24:MI:SS''),'||CASE WHEN time_end IS NULL then 'null' ELSE 'TO_TIMESTAMP('''||TO_CHAR(time_beg,'YYYY-MM-DD HH24:MI:SS')||''',''YYYY-MM-DD HH24:MI:SS'')' END||',null,'''',6);' FROM sj_shifts where id_shop = $IDSHOP and  num = $SHIFT order by num"
$psql -Atq -P null='null' -F $'\t' -c "$SQL" >> $OFILE

SQL="SELECT 'SELECT credit2.load_transaction(''01|'||TO_CHAR(sj_tranz.timebeg,'DD.MM.YYYY')||'|'||TO_CHAR(sj_tranz.timebeg,'HH24:MI:SS')||'|'||pj_preset.product||'|'|| 
      tr.vl||'|'||CASE WHEN tr.vl =0 THEN 0 ELSE ch.sm/tr.vl END||'|'||sj_ppcards.snum||
      '|'||TO_CHAR(sj_shifts.time_beg,'DD.MM.YYYY')||'|'||sj_tranz.id_shop||
      '|'||pj_preset.pump||'|'||pj_preset.volume||'|'||TO_CHAR(sj_shifts.time_beg,'HH24:MI:SS')||'|0|'||
      sj_tranz.trannum||'|'||ch.sm||'|'||sj_tranz.shift||'|08|'||pj_preset.price||'|'||pj_preset.price*tr.vl||'|'||tr.vl||'|'||trm.discount||'|'||trm.cnt_discount||''');'
  FROM sj_tranz, sj_shifts, pj_preset, sj_ppcards, 
       (SELECT id_shop, trannum, sum(volume) as vl FROM pj_td WHERE trannum>=$TRAN1 AND trannum<=$TRAN2 and id_shop=$IDSHOP GROUP BY id_shop, trannum) as tr,
       (SELECT id_shop, trannum, max(price) as price, sum(summa) as sm FROM sj_checks WHERE trannum>=$TRAN1 AND trannum<=$TRAN2 and id_shop=$IDSHOP GROUP BY id_shop, trannum) as ch,
       (SELECT id_shop, buktrannum as trannum, round(sum(sumdiscount),2) as discount, round(sum(coalesce(cnt_discount,'0')),2) as cnt_discount
          FROM sj_mifare_tranz WHERE cardoperation=2 AND buktrannum>=$TRAN1 AND buktrannum<=$TRAN2 and id_shop=$IDSHOP GROUP BY id_shop, buktrannum) as trm
 WHERE sj_shifts.id_shop=sj_tranz.id_shop AND sj_shifts.num=sj_tranz.shift AND
       pj_preset.id_shop=sj_tranz.id_shop AND pj_preset.trannum=sj_tranz.trannum AND 
       sj_ppcards.id_shop=sj_tranz.id_shop AND sj_ppcards.trannum=sj_tranz.trannum AND
       tr.id_shop=sj_tranz.id_shop AND tr.trannum=sj_tranz.trannum AND
       ch.id_shop=sj_tranz.id_shop AND ch.trannum=sj_tranz.trannum AND
       sj_tranz.id_shop=trm.id_shop AND sj_tranz.trannum=trm.trannum AND
       sj_ppcards.typ=10020;"

$psql -Atq -P null='null' -F $'\t' -c "$SQL" >> $OFILE


i="public.sj_mifare_tranz"
echo "--TABLE:$i" >> $OFILE
echo "DELETE FROM $i WHERE id_shop = $IDSHOP and shift = $SHIFT ;" >> $OFILE
echo "COPY $i (" >> $OFILE
SQL=$(echo 'select * from'" $i where id_shop = $IDSHOP and \
            shift = $SHIFT order by mftrannum")
$psql -Aq -P null='null' -c "$SQL" | $SED \
        -e "/shop/s/|/,/g"  \
        -e "s/|$/| /g"  \
        -e "s/^null|/\\\\N|/g" \
        -e "s/|null$/|\\\\N/g" \
        -e "s/|null|/|\\\\N|/g" \
        -e "s/|null|/|\\\\N|/g" \
        -e "s/|null|/|\\\\N|/g" \
        -e "s/|null|/|\\\\N|/g" \
        -e "s/|null|/|\\\\N|/g" \
        -e "s/||/| |/g" \
        -e "s/||/| |/g" \
        -e "s/||/| |/g" \
        -e "s/||/| |/g" \
        -e "s/||/| |/g" \
        -e "s/|/\t/g"       \
        -e "/shop/a\) FROM stdin;" \
        -e "/^(/a\\\\\." \
        -e "/^(/d" \
        -e "s/||/| |/g"  >>  $OFILE 

echo "$(date +%H:%M:%S) end" >> $LOG
echo $(basename $OFILE)
exit 0
