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

OUTPREFIX="u"

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

### сохраняемые таблицы журналов
TABLES10="utranz.sj_shifts utranz.sj_shifts_sub"

TABLES20="utranz.sj_tranz \
utranz.sj_tranz_addinfo \
utranz.sj_prg_loyalty \
utranz.sj_checks \
utranz.sj_checks_pay \
utranz.sj_checks_text \
utranz.sj_ppcards \
utranz.sj_ppcards_addinfo \
utranz.pj_preset \
utranz.pj_td \
utranz.pj_tdext \
utranz.pj_log_d \
public.sj_spasibo \
utranz.sj_log_article \
utranz.sj_log_discount \
utranz.sj_ppcards_text"

TABLES30="utranz.sj_kassareg \
utranz.sj_kassareg_addinfo \
utranz.pj_trksum \
utranz.pj_tanks \
utranz.pj_tanksbook \
utranz.gj_log_beg \
public.pj_tshdata \
public.sd_conf_local \
public.sj_messages \
utranz.sj_opers \
utranz.sj_bonds \
public.pd_prodprices \
ldict.sd_kassalist \
public.pj_newprodprices \
public.pj_newprodprices_time \
public.sj_events \
utranz.sj_shifts_ppcards_text"

TABLES40="utranz.gj_log_m \
utranz.gj_log_d \
utranz.gj_log_d_pay \
utranz.gj_log_a \
utranz.gj_log_c \
utranz.gj_log_section"


TABLES60="utranz.pj_tankop \
utranz.pj_tankop_addinfo \
utranz.pj_tankop_tankinfo"

TABLES70="ldict.gd_instore_history \
ldict.sd_placelist_history \
ldict.sd_cfg_history \
ldict.sd_place_units_history \
ldict.sd_local_payments_history \
ldict.pd_trk_history \
ldict.pd_tanks_list_history \
ldict.pd_products_local_history \
ldict.sd_relay_history \
ldict.gd_cafe_keys_local_history"
TABLES80="public.gd_count_global"

### не архивируются:
# sj_state sj_cur_num gj_send
# tj_got tj_sent tj_got_description

## сформировать имя 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  ;;
#    -raw)
#        RAW="yes" ;;
    -mini)
        MINI="yes" ;;
    esac
    shift
    if [ $is == "yes" ]; then
        shift
        i=`expr $i - 2`
    else
        i=`expr $i - 1`
    fi
done

if [ ! -z "$MINI" ]; then
  #OUTPREFIX="j"
  TABLES10="utranz.sj_shifts"
  TABLES20=""
  TABLES30=""
  TABLES40=""
  TABLES60=""
  TABLES70="ldict.gd_instore_history ldict.sd_placelist_history"
  TABLES80="public.gd_count_global"
  
  echo "$(date +%H:%M:%S) mini" >> $LOG
fi

if [ -z "$SED" ]; then
  SED=sed
fi
echo "Using sed $SED" >> $LOG
### определение вспомогательной переменной, для удобства последующих записей
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(shift) from utranz.sj_shifts where id_shop=$IDSHOP limit 1"
    SHIFT=$($psql -Atq -c "$SQL")
elif [ "$SHIFT" == "last" ]; then
    SQL="select max(shift) from utranz.sj_shifts where id_shop=$IDSHOP and timeend 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 timebeg, timeend from utranz.sj_shifts where id_shop=$IDSHOP and shift=$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/$OUTPREFIX.$IDSHOP.$SHIFT.$SHIFT_STAT
OFILE=${OFILE}.sql

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

SHIFT_MD5=
### вывести в выходной файл необходимую информацию
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 utranz.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

### получить номера первой и последней ТТН (TTN1, TTN2)
#SQL="select min(ttn_npp), max(ttn_npp) from pj_sliw where id_shop=$IDSHOP and shift = $SHIFT"
#TTNS=$($psql -Atq -F' ' -c "$SQL")
#TTN1=$(echo $TTNS | cut -d' ' -f1)
#TTN2=$(echo $TTNS | cut -d' ' -f2)
#echo "--TTNs:$TTN1,$TTN2" >> $OFILE

### получить номера первой и последней операции по резервуару (TANKIN1, TANKIN2)
SQL="select min(id_tankop), max(id_tankop) from utranz.pj_tankop where id_shop=$IDSHOP and shift = $SHIFT"
TANKINS=$($psql -Atq -F' ' -c "$SQL")
TANKIN1=$(echo $TANKINS | cut -d' ' -f1)
TANKIN2=$(echo $TANKINS | cut -d' ' -f2)
echo "--TANKINs:$TANKIN1,$TANKIN2" >> $OFILE


### пересчитать md5sum смены
if [ -z "$MINI" -a "$SHIFT_STAT" == "closed" ]; then
  SQL="SELECT utranz.sf_get_shift_md5($IDSHOP,$SHIFT,0)"
  SHIFT_MD5=$($psql -Aqt -c "$SQL")
  echo "--MD5:$SHIFT_MD5" >> $OFILE;
  #SQL="update utranz.sj_shifts set md5sum=utranz.sf_get_shift_md5($IDSHOP,$SHIFT,0) where id_shop=$IDSHOP and shift = $SHIFT and sf_get_cur_id_shop()=$IDSHOP"
  SQL="update utranz.sj_shifts set md5sum='$SHIFT_MD5' where id_shop=$IDSHOP and shift = $SHIFT and sf_get_cur_id_shop()=$IDSHOP"
  $psql -c "$SQL" > /dev/null
fi


####
#
# select 'CREATE TABLE temp_sj_checks (' || array_to_string(array_agg (r), ', ')||');' FROM (
# select column_name||' '||data_type as r from information_schema.columns where table_schema = 'utranz' and table_name = 'sj_checks' order by ordinal_position
# ) t
#
if [ -z "$MINI" ]
then
  echo "SELECT utranz.sf_do_shift_clear($IDSHOP,$SHIFT);" >> $OFILE
fi
###$psql -c "update gj_log_m set document_num=' ' where document_num=''" >> $OFILE
# получение из БД таблиц, перечисленных в TABLES1
for tables in 00 10 20 30 40 50 60 70 80; do
  case $tables in
  10) TABLES=$TABLES10 ;;
  20) TABLES=$TABLES20 ;;
  30) TABLES=$TABLES30 ;;
  40) TABLES=$TABLES40 ;;
  50) TABLES=$TABLES50 ;;
  60) TABLES=$TABLES60 ;;
  70) TABLES=$TABLES70 ;;
  80) TABLES=$TABLES80 ;;
  esac

  if [ -z "$TABLES" ]; then
    continue
  fi

  for i in `echo $TABLES`; do
    echo "" >> $OFILE
    echo "-- ==========================" >> $OFILE
    echo "--TABLE:$i" >> $OFILE
    _scheme_name=$(echo $i | cut -d '.' -f1)
    _table_name=$(echo $i | cut -d '.' -f2)
    _out_copy_name="tmp_${_scheme_name}_${_table_name}"
    #SQLCREATE=$(echo "select 'CREATE TEMPORARY TABLE tmp_"${_scheme_name}"_"${_table_name}" ('||array_to_string(array_agg (r),', ')||');' FROM ( SELECT column_name||' '||data_type as r FROM information_schema.columns WHERE table_schema = '"${_scheme_name}"' and table_name = '"${_table_name}"' order by ordinal_position) t;")
    #SQLCREATE=$(echo "CREATE TEMPORARY TABLE tmp_${_scheme_name}_${_table_name} AS SELECT * FROM $i WHERE 1=0;"
    #$psql -Aqt -c "$SQLCREATE" >> $OFILE
    echo "CREATE TEMPORARY TABLE tmp_${_scheme_name}_${_table_name} AS SELECT * FROM $i WHERE 1=0;" >> $OFILE
  if [ $tables -eq 10 ]; then

    #echo "COPY $i (" >> $OFILE
    echo -n "COPY $_out_copy_name (" >> $OFILE
    SQL=$(echo 'select * from'" $i where id_shop = $IDSHOP and \
            shift = $SHIFT order by shift")
    if [ $i == "utranz.sj_shifts_sub" ]; then
      SQL=$SQL", subshift"
    fi
#"sj_tranz sj_cards sj_checks sj_ppcards pj_preset pj_td pj_tdext gj_articles"
  elif [ $tables -eq 20 ]; then

    # эти таблицы могут быть пустыми (TRAN1 не оперделена)
    if [ -z "$TRAN1" ]; then continue; fi
    #echo "COPY $i (" >> $OFILE
    echo -n "COPY $_out_copy_name (" >> $OFILE
    SQL=$(echo 'select * from'" $i where id_shop = $IDSHOP and \
          trannum >= $TRAN1 and trannum <= $TRAN2 order by trannum")

    if [ $i == "utranz.sj_tranz_addinfo" ]; then SQL=$SQL", typ, subtyp";
    elif [ $i == "utranz.sj_prg_loyalty" ]; then SQL=$SQL", timeop";
    elif [ $i == "utranz.sj_checks" ];      then SQL=$SQL", rn_ks, timeprn";
    elif [ $i == "utranz.sj_checks_pay" ];  then SQL=$SQL", rn_ks, rn";
    elif [ $i == "utranz.sj_checks_text" ]; then SQL=$SQL", rn_ks";
    elif [ $i == "utranz.sj_ppcards" ];     then SQL=$SQL", rn";
    elif [ $i == "utranz.sj_ppcards_addinfo" ]; then SQL=$SQL", rn, typ, subtyp";
    elif [ $i == "utranz.sj_ppcards_text" ]; then SQL=$SQL", timeop";
    elif [ $i == "utranz.pj_td" ];          then SQL=$SQL", rn";
    elif [ $i == "utranz.pj_tdext" ];       then SQL=$SQL", rn";
    elif [ $i == "utranz.pj_log_d" ];       then SQL=$SQL", rn";
    elif [ $i == "public.sj_spasibo" ];     then SQL=$SQL", optime";
    elif [ $i == "utranz.sj_log_article" ]; then SQL=$SQL", rn_ks, pos_ks";
    elif [ $i == "utranz.sj_log_discount" ];then SQL=$SQL", rn_ks, pos_ks, rn";
    fi

  elif [ $tables -eq 30 ]; then
    SQL=$(echo 'select * from'" $i where id_shop = $IDSHOP and \
            shift = $SHIFT")

    if [ $i == "ldict.sd_kassalist" ];             then SQL=$SQL" order by shift, timebeg, place, num";
    elif [ $i == "utranz.sj_kassareg" ];           then SQL=$SQL" order by shift, timeop, place, kasnum, typ";
    elif [ $i == "utranz.sj_kassareg_addinfo" ];   then SQL=$SQL" order by shift, timeop, place, kasnum, typ, sybtype, param";
    elif [ $i == "utranz.pj_trksum" ];             then SQL=$SQL" order by shift, trk, sch, timeop";
    elif [ $i == "utranz.pj_tanks" ];              then SQL=$SQL" order by shift, tanknum, typ, timeop";
    elif [ $i == "utranz.pj_tanksbook" ];          then SQL=$SQL" order by shift, tanknum, timeop";
    # !!!
    elif [ $i == "utranz.gj_log_beg" ];            then SQL=$SQL" order by shift, id_list, id_instore, rn";
    elif [ $i == "utranz.sj_opers" ];              then SQL=$SQL" order by shift, subshift, place, timereg";
    elif [ $i == "utranz.sj_bonds" ];              then SQL=$SQL" order by shift, place, timeop";
    elif [ $i == "utranz.sj_shifts_ppcards_text" ];then SQL=$SQL" order by shift, timeop, place, typ";
    elif [ $i == "public.pd_prodprices" ];         then SQL=$SQL" order by shift, prod, settime";
    elif [ $i == "public.pj_newprodprices" ];      then SQL=$SQL" order by shift, prod, optime";
    elif [ $i == "public.pj_newprodprices_time" ]; then SQL=$SQL" order by shift_parent, prod, timeop_parent, typ";
    elif [ $i == "public.sj_events" ];             then SQL=$SQL" order by shift, timeop, id";
    elif [ $i == "public.pj_tshdata" ];            then SQL=$SQL" order by shift, optime, trk";
    elif [ $i == "public.sd_conf_local" ];         then SQL=$SQL" order by shift, op_time, id_local_conf_type";
    elif [ $i == "public.sj_messages" ];           then SQL=$SQL" order by shift, optime, nplace, unitid";
    fi
    #echo "COPY $i (" >> $OFILE
    echo -n "COPY $_out_copy_name (" >> $OFILE
# gj_log_m gj_log_d
  elif [ $tables -eq 40 ]; then
    if [ -z "$TRAN1" ]; then continue; fi
    #echo "COPY $i (" >> $OFILE
    echo -n "COPY $_out_copy_name (" >> $OFILE
    SQL=$(echo 'select * from'" $i where id_shop = $IDSHOP and \
          trannum >= $TRAN1 and trannum <= $TRAN2 order by trannum")

    #if [ $i == "utranz.gj_log_m" ];          then SQL=$SQL"";
    if [ $i == "utranz.gj_log_d" ];          then SQL=$SQL", id_instore, id_list, num";
    elif [ $i == "utranz.gj_log_d_pay" ];    then SQL=$SQL", id_instore, id_list, num, paycod";
    elif [ $i == "utranz.gj_log_a" ];        then SQL=$SQL", id_instore, id_list, num, typ, subtyp";
    elif [ $i == "utranz.gj_log_c" ];        then SQL=$SQL", id_list, id_instore, num";
    elif [ $i == "utranz.gj_log_section" ];  then SQL=$SQL", id_list, id_sections";
    fi
# sj_cur_num
  elif [ $tables -eq 60 ]; then
    if [ -z "$TANKIN1" -o -z "$TANKIN2" ]; 
      then continue; 
    fi
    #echo "COPY $i (" >> $OFILE
    echo -n "COPY $_out_copy_name (" >> $OFILE
    SQL=$(echo 'select * from'" $i where id_shop = $IDSHOP and id_tankop >= $TANKIN1 and id_tankop <= $TANKIN2 order by id_tankop")
    if [ $i == "utranz.pj_tankop_addinfo" ];  then SQL=$SQL", subtyp";
    elif [ $i == "utranz.pj_tankop_tankinfo" ]; then SQL=$SQL", typ";
    fi

  elif [ $tables -eq 70 ]; then
    #echo "COPY $i (" >> $OFILE
    echo -n "COPY $_out_copy_name (" >> $OFILE
    SQL=$(echo 'select * from'" $i where id_shop = $IDSHOP and shift = $SHIFT order by timeop")
  elif [ $tables -eq 80 ]; then
    #echo "COPY public.gd_count_global (" >> $OFILE
    #SQL=$(echo "select $IDSHOP as id_shop, id_list, max(price) as price, sum(count) as count, id_instore from public.gd_count GROUP BY id_list, id_instore")
    #echo "COPY $i (" >> $OFILE
    tt=$i
    if [ "$tt" = "public.gd_count_global" ]
    then
       tt="public.gd_count"
    fi
    echo -n "COPY $_out_copy_name (" >> $OFILE
    SQL=$(echo "select $IDSHOP as id_shop, id_list, max(price) as price, sum(count) as count, id_instore from $tt GROUP BY id_list, id_instore")
  else # 50
    #echo "COPY $i (" >> $OFILE
    echo -n "COPY $_out_copy_name (" >> $OFILE
    SQL=$(echo 'select * from'" $i")
  fi
  echo "$(date +%H:%M:%S) RUNNING $SQL" >> $LOG
  $psql -Aq -P null='null' -c "$SQL" >> $OFILE
  echo ""  >> $OFILE
  echo "SELECT utranz.sf_do_load_from_temp('"$i"', '"$_out_copy_name"');" >> $OFILE
  echo "DROP TABLE $_out_copy_name;"  >> $OFILE
  echo "-- END OF $i" >> $OFILE
  done
done

if [ -z "$MINI" -a "$SHIFT_STAT" == "closed" ]; then
  echo "-- Перерасчет md5sum по смене " >>$OFILE
  echo "SELECT utranz.sf_do_shift_md5($IDSHOP, $SHIFT, 0);" >> $OFILE
fi

#        -e "/id_shop/a\) FROM stdin;" \


#if [ "$RAW" != "yes" ]; then
    $SED -i \
        -e "/id_shop/s/|/,/g"  \
        -e "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 "/^COPY/a\) FROM stdin;" \
        -e "/^(/a\\\\\." \
        -e "/^(/d" \
        $OFILE
    if [ $? -ne 0 ]; then
        MSG="Ошибка при преобразовании формата файла"
        echo $MSG >& 2
        echo "$(date +%H:%M:%S) $MSG" >> $LOG
        exit 1
    fi
#fi

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