#!/bin/bash
BINPATH=/opt/bukdbase/bin
O_PATH=/var/opt/bukdbase/backup
LOGDIR=/var/opt/bukdbase/log
### значения по умолчанию (переопределяются в cmdline)
DBNAME=ubuk
DBUSER=bukts
if [ ! -d $LOGDIR ]; then
    LOGDIR=$HOME
fi

psql="psql $DBNAME -U $DBUSER -h 127.0.0.1"
pgdump="pg_dump $DBNAME -U $DBUSER -h 127.0.0.1 --insert --data-only"

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

### получить номер магазина (IDSHOP)
if [ -z "$IDSHOP" ]; then
    MSG="Не указан параметр идентификатора магазина -shop"
    echo $MSG >& 2
    echo "$(date +%H:%M:%S) $MSG" >> $LOG
    exit 1
fi

rm -f $O_PATH/dmp.$IDSHOP.sql 2>/dev/null
OFILE0=$O_PATH/dmp.$IDSHOP.sql

MSG="Создание файла $OFILE0"
echo $MSG
echo "$(date +%H:%M:%S) $MSG" >> $LOG

SQL=$(echo "\\pset tuples_only")
echo $SQL >> $OFILE0

#SQL=$(echo "ALTER TABLE gj_send DISABLE TRIGGER tt_send; \
#ALTER TABLE j_soldi DISABLE TRIGGER t_soldi; \
#ALTER TABLE pd_pipeline DISABLE TRIGGER tt_pipeline_list; \
#ALTER TABLE pd_tanks_list DISABLE TRIGGER tt_tanks_list; \
#ALTER TABLE pj_sliw DISABLE TRIGGER tt_conv_sliw_db5; \
#ALTER TABLE pj_tankop DISABLE TRIGGER tt_conv_tankop_db5; \
#ALTER TABLE pj_tankop_addinfo DISABLE TRIGGER tt_conv_tankop_addinfo_db5; \
#ALTER TABLE pj_tankop_tankinfo DISABLE TRIGGER tt_conv_tankop_tankinfo_db5; \
#ALTER TABLE pj_tanks DISABLE TRIGGER tt_conv_tanks_db5; \
#ALTER TABLE pj_tanks DISABLE TRIGGER  tt_tanks; \
#ALTER TABLE pj_tanksbook DISABLE TRIGGER tt_conv_tanksbook_db5; \
#ALTER TABLE pj_td DISABLE TRIGGER tt_conv_td_db5; \
#ALTER TABLE pj_td DISABLE TRIGGER tt_td; \
#ALTER TABLE pj_trksum DISABLE TRIGGER tt_conv_trksum_db5; \
#ALTER TABLE sd_group_user DISABLE TRIGGER tt_group_user; \
#ALTER TABLE sd_payments DISABLE TRIGGER tt_payments; \
#ALTER TABLE sj_bonds DISABLE TRIGGER tt_conv_bonds_db5; \
#ALTER TABLE sj_checks DISABLE TRIGGER tt_conv_checks_db5; \
#ALTER TABLE sj_kassareg DISABLE TRIGGER tt_conv_kassareg_db5; \
#ALTER TABLE sj_kassareg_addinfo DISABLE TRIGGER tt_conv_kassareg_addinfo_db5; \
#ALTER TABLE sj_opers DISABLE TRIGGER tt_conv_opers_db5; \
#ALTER TABLE sj_shifts DISABLE TRIGGER tt_shifts; \
#ALTER TABLE sj_tranz DISABLE TRIGGER tt_conv_tranz_db5; \
#ALTER TABLE sj_tranz DISABLE TRIGGER tt_tranz;")
#echo $SQL >> $OFILE0

TABLES_DEL_D="gd_count td_tosend td_get \
 gd_instore pd_pipeline \
 gd_identification gd_list gd_group_list gd_tax  \
 gd_manager gd_store gd_supplier gd_section \
 gd_list_addinfo \
 gd_make_list gd_make \
 gd_list_section \
 pd_tanks_list pd_products pd_loyalty pd_supplier \
 pd_talon_list pd_talon_discount_list pd_talon_discount_category_list pd_um4 \
 sd_payments sd_payments_merge sd_payments_loyalty sd_payments_main \
 sd_payments_listsale_list sd_payments_listsale_group_list sd_payments_listsale \
 sd_user_list sd_group_rule sd_group_user"

TABLES_DEL_J="gd_instore_addinfo sd_placelist_history sd_relay_history sd_cfg_history sd_place_units_history \
 sd_local_payments_history pd_trk_history \
 pd_tanks_list pd_tanks_list_history pd_products_local_history sd_kassalist tj_got \
 tj_got_description tj_sent gj_send gj_send_old \
 gj_log_beg_addinfo gj_log_beg utranz.gj_log_beg \
 pj_sliw pj_ttn_ext pj_ttn pj_tankop_tankinfo pj_tankop_addinfo pj_tankop  \
 gj_articles sj_log_article sj_log_discount sj_checks_text sj_checks_ext sj_checks \
 gj_log_section utranz.gj_log_section \
 gj_log_a utranz.gj_log_a utranz.gj_log_c gj_log_d_addinfo gj_log_d utranz.gj_log_d utranz.gj_log_d_pay \
 gj_log_m utranz.gj_log_m \
 utranz.pj_log_d utranz.pj_preset utranz.pj_tankop utranz.pj_tankop_addinfo utranz.pj_tankop_tankinfo utranz.pj_tanks \
 utranz.pj_tanksbook utranz.pj_td utranz.pj_tdext utranz.pj_trksum utranz.sj_bonds \
 utranz.sj_checks_text utranz.sj_checks_pay utranz.sj_checks utranz.sj_kassareg_addinfo utranz.sj_kassareg \
 utranz.sj_log_article utranz.sj_log_discount utranz.sj_opers utranz.sj_ppcards_text utranz.sj_ppcards_addinfo utranz.sj_ppcards \
 utranz.sj_prg_loyalty utranz.sj_tranz_addinfo utranz.sj_shifts_ppcards_text utranz.sj_tranz utranz.sj_shifts_sub \
 utranz.sj_shifts utranz.sj_shifts_addinfo_nl \
 pj_newprodprices_time pj_newprodprices pj_ttn_received pj_ac pj_preset \
 pj_tanks pj_tanksbook pj_td pj_tdext pj_trksum pj_tshdata sj_moneyinsert \
 pd_prodprices sj_log sj_messages sj_bonds sj_cards sj_mifare_tranz sj_spasibo \
 sj_comment sj_kassareg_addinfo sj_kassareg sj_opers sj_ppcards_addinfo sj_ppcards \
 sj_state sj_taloninp sj_tranz_addinfo sj_tranz sj_shifts gd_cafe_keys_local_history pd_products_local_limit_history \
gd_instore_history "

TABLES_LOAD_D="gd_tax gd_group_list gd_list gd_identification \
 pd_products \
 gd_make gd_make_list gd_store gd_supplier gd_section gd_list_section gd_list_addinfo \
 gd_manager pd_supplier \
 pd_talon_list pd_talon_discount_category_list pd_talon_discount_list \
 sd_payments_main sd_payments_loyalty sd_payments_merge sd_payments \
 sd_payments_listsale sd_payments_listsale_group_list sd_payments_listsale_list"


TABLES_LOAD_J="ldict.gd_cafe_keys_local_history \
ldict.sd_relay_history \
ldict.pd_products_local_limit_history \
ldict.pd_products_local_history \
public.pd_tanks_list \
ldict.pd_tanks_list_history \
ldict.pd_trk_history \
ldict.sd_local_payments_history \
ldict.sd_place_units_history \
ldict.sd_cfg_history \
ldict.sd_placelist_history \
ldict.gd_instore_history \
utranz.sj_shifts \
utranz.sj_shifts_sub \
utranz.sj_opers \
utranz.sj_tranz \
utranz.sj_tranz_addinfo \
utranz.pj_preset \
utranz.pj_td \
utranz.pj_tdext \
utranz.pj_log_d \
utranz.sj_checks \
utranz.sj_checks_pay \
utranz.sj_checks_text \
utranz.sj_log_article \
utranz.sj_log_discount \
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 \
utranz.gj_log_beg \
utranz.pj_tankop \
utranz.pj_tankop_addinfo \
utranz.pj_tankop_tankinfo \
public.sj_spasibo \
utranz.sj_ppcards \
utranz.sj_ppcards_addinfo \
utranz.sj_ppcards_text \
utranz.sj_prg_loyalty \
utranz.pj_tanks \
utranz.pj_tanksbook \
utranz.pj_trksum \
public.pj_tshdata \
utranz.sj_bonds \
utranz.sj_kassareg \
utranz.sj_kassareg_addinfo \
ldict.sd_kassalist \
public.pd_prodprices \
public.pj_newprodprices \
public.pj_newprodprices_time \
public.sj_events \
utranz.sj_shifts_ppcards_text \
public.sj_messages \
public.sd_group_user \
public.sd_group_rule \
public.sd_user_list \
public.pd_pipeline"


### очистка логов
_pc=0
_pc_step=24
_n_step=0
echo "SELECT '[ ${_pc}% ] : Шаг 1 из 6 : Очистка логов';" >> $OFILE0
_arr=($TABLES_DEL_J);
_sz=${#_arr[@]}
_k=0;
for i in $(echo $TABLES_DEL_J); do
  _k=$((_k+1))
  echo "SELECT '[ ${_pc}% ] : [ ${_k} / ${_sz} ] Удаление данных логов из таблицы $i';" >> $OFILE0
  SQL=$(echo "DELETE from $i ;")
  echo $SQL >> $OFILE0
  _n1=$((_pc_step * _k))
  _n2=$((_n1/_sz))
  _pc=$((_n2 + _n_step))
done

_n_step=$((_n_step + _pc_step))
_pc=$((_n_step))
### очистка справочников
echo "SELECT '[ ${_pc}% ] : Шаг 2 из 6 : Очистка справочников';" >> $OFILE0
_arr=($TABLES_DEL_D);
_sz=${#_arr[@]}
_k=0;
for i in $(echo $TABLES_DEL_D); do
  _k=$((_k+1))
  echo "SELECT '[ ${_pc}% ] : [ ${_k} / ${_sz} ] Удаление справочных данных из таблицы $i';" >> $OFILE0
  SQL=$(echo "DELETE from $i ;")
  echo $SQL >> $OFILE0
  _n1=$((_pc_step * _k))
  _n2=$((_n1/_sz))
  _pc=$((_n2 + _n_step))
done

###Смена номера
SQL=$(echo "UPDATE sd_ident SET id=$IDSHOP WHERE id=sf_get_cur_id_shop();")
echo $SQL >> $OFILE0

##загрузка справочника
_n_step=$((_n_step + _pc_step))
_pc=$((_n_step))
_pc_dump=0;
echo "SELECT '[ ${_pc}% ] : Шаг 3 из 5 : Загрузка справочников';" >> $OFILE0
_arr=($TABLES_LOAD_D);
_sz=${#_arr[@]}
_k=0;
for i in $(echo $TABLES_LOAD_D); do
  _k=$((_k+1))
  echo "[ ${_pc_dump}% ] : [ ${_k} / ${_sz} ] : Выгрузка справочных данных из таблицы $i"
  echo "SELECT '[ ${_pc}% ] : [ ${_k} / ${_sz} ] Загрузка справочных данных в таблицу $i';" >> $OFILE0
  echo "COPY $i FROM stdin;" >> $OFILE0
  SQL=$(echo "select * from $i ;")
  $psql -Atq -P null='\N' -F $'\t' -c "$SQL" >> $OFILE0
  echo "\." >> $OFILE0
  _n1=$((_pc_step * _k))
  _n2=$((_n1/_sz))
  _pc=$((_n2 + _n_step))
  _n_dump=$((50 * _k))
  _pc_dump=$((_n_dump/_sz))
done

##загрузка логов
_n_step=$((_n_step + _pc_step))
_pc=$((_n_step))
_pc_dump=50
echo "SELECT '[ ${_pc}% ] : Шаг 4 из 5 : Загрузка данных логов';" >> $OFILE0
_arr=($TABLES_LOAD_J);
_sz=${#_arr[@]}
_k=0;
for i in $(echo $TABLES_LOAD_J); do
  _k=$((_k+1))
  echo "[ ${_pc_dump}% ] : [ ${_k} / ${_sz} ] : Выгрузка данных логов из таблицы $i"
  echo "SELECT '[ ${_pc}% ] : [ ${_k} / ${_sz} ] Загрузка данных логов в таблицу $i';" >> $OFILE0
  echo "COPY $i FROM stdin;" >> $OFILE0
  SQL=$(echo "select * from $i WHERE id_shop=$IDSHOP;")
  $psql -Atq -P null='\N' -F $'\t' -c "$SQL" >> $OFILE0
  echo "\." >> $OFILE0
  _n1=$((_pc_step * _k))
  _n2=$((_n1/_sz))
  _pc=$((_n2 + _n_step))
  _n_dump=$((49 * _k))
  _n2_dump=$((_n_dump/_sz))
  _pc_dump=$((_n2_dump + 50))
done

##Загрузка параметров
_n_step=$((_n_step + _pc_step))
_pc=$((_n_step))
echo "SELECT '[ ${_pc}% ] : Шаг 5 из 6 : Загрузка параметров';" >> $OFILE0

SQL=$(echo "DELETE FROM sd_conf_local WHERE id_local_conf_type NOT IN (28,47,146);")
echo $SQL >> $OFILE0
echo "COPY sd_conf_local FROM stdin;" >> $OFILE0
SQL=$(echo "select * from sd_conf_local WHERE id_local_conf_type NOT IN (28,47,146) AND id_shop=$IDSHOP;")
$psql -Atq -P null='\N' -F $'\t' -c "$SQL" >> $OFILE0
echo "\." >> $OFILE0

##Восстанавление текущих переменных
_pc=$((_pc + 1))
echo "SELECT '[ ${_pc}% ] : Шаг 6 из 6 : Восстанавление текущих переменных';" >> $OFILE0

SQL=$(echo "DELETE from sj_cur_num;")
echo $SQL >> $OFILE0

SQL=$(echo "SELECT 'INSERT INTO sj_cur_num VALUES (''sj_shifts_num'', '''||(SELECT CASE WHEN b IS NULL THEN '1' ELSE b::text END FROM (select max(shift)+1 as b from utranz.sj_shifts WHERE id_shop=$IDSHOP) as a)||''');';")
$psql -Atq -P null='\N' -F $'\t' -c "$SQL" >> $OFILE0

SQL=$(echo "SELECT 'INSERT INTO sj_cur_num VALUES (''gj_send_id'', '''||(SELECT CASE WHEN b IS NULL THEN '1' ELSE b::text END FROM (select max(id)+1 as b from gj_send WHERE id_this_shop=$IDSHOP) as a)||''');';")
$psql -Atq -P null='\N' -F $'\t' -c "$SQL" >> $OFILE0

SQL=$(echo "SELECT 'INSERT INTO sj_cur_num VALUES (''td_tosend'', '''||(SELECT CASE WHEN b IS NULL THEN '1' ELSE b::text END FROM (select max(id)+1 as b from td_tosend WHERE id_shop=$IDSHOP) as a)||''');';")
$psql -Atq -P null='\N' -F $'\t' -c "$SQL" >> $OFILE0

SQL=$(echo "SELECT 'INSERT INTO sj_cur_num VALUES (''sj_tranz_trannum'', '''||(SELECT CASE WHEN b IS NULL THEN '1' ELSE b::text END FROM (select max(trannum)+1 as b from utranz.sj_tranz WHERE id_shop=$IDSHOP) as a)||''');';")
$psql -Atq -P null='\N' -F $'\t' -c "$SQL" >> $OFILE0

SQL=$(echo "SELECT 'INSERT INTO sj_cur_num VALUES (''pj_ttn_ttn_npp'', '''||(SELECT CASE WHEN b IS NULL THEN '1' ELSE b::text END FROM (select max(ttn_npp)+1 as b from pj_ttn WHERE id_shop=$IDSHOP) as a)||''');';")
$psql -Atq -P null='\N' -F $'\t' -c "$SQL" >> $OFILE0

SQL=$(echo "SELECT 'INSERT INTO sj_cur_num VALUES (''sj_events'', '''||(SELECT COALESCE(b::text,'1') FROM (select max(id)+1 as b from sj_events WHERE id_shop=$IDSHOP) as a)||''');';")
$psql -Atq -P null='\N' -F $'\t' -c "$SQL" >> $OFILE0

SQL=$(echo "UPDATE gd_manager SET purc_price=m.p2, tax = m.t2 FROM (SELECT gj_log_d.id_list, gj_log_d.purc_price as p2, ROUND(gj_log_d.tax/gj_log_d.quantity,2) as t2 FROM utranz.gj_log_d, (  SELECT id_list, max(sj_tranz.trannum) as trannum FROM utranz.sj_tranz, utranz.gj_log_d WHERE sj_tranz.id_shop=gj_log_d.id_shop AND sj_tranz.trannum=gj_log_d.trannum AND sj_tranz.id_shop=$IDSHOP AND trantyp IN (10,11) GROUP BY id_list) as b WHERE gj_log_d.id_shop=$IDSHOP AND gj_log_d.id_list=b.id_list AND gj_log_d.trannum=b.trannum AND gj_log_d.quantity>0) as m WHERE gd_manager.id_list=m.id_list;")
echo $SQL >> $OFILE0

SQL=$(echo "INSERT INTO gd_count (SELECT oid_list, oprice, ocount, row_number() over (partition by oid_list), oid_tranz_income, onum_income, oid_instore, opurc_price, opurc_summ, otax, opurc_midlsumm FROM utranz.gf_get_ost_full( $IDSHOP, now()::timestamp without time zone, NULL));")
echo $SQL >> $OFILE0

SQL=$(echo "UPDATE gd_list set uses=true WHERE id IN (SELECT id_list FROM gd_count);")
echo $SQL >> $OFILE0

SQL=$(echo "SELECT sf_do_all_restore_from_history( $IDSHOP, NULL);")
echo $SQL >> $OFILE0

#SQL=$(echo "ALTER TABLE gj_send ENABLE TRIGGER tt_send; \
#ALTER TABLE j_soldi ENABLE TRIGGER t_soldi; \
#ALTER TABLE pd_pipeline ENABLE TRIGGER tt_pipeline_list; \
#ALTER TABLE pd_tanks_list ENABLE TRIGGER tt_tanks_list; \
#ALTER TABLE pj_sliw ENABLE TRIGGER tt_conv_sliw_db5; \
#ALTER TABLE pj_tankop ENABLE TRIGGER tt_conv_tankop_db5; \
#ALTER TABLE pj_tankop_addinfo ENABLE TRIGGER tt_conv_tankop_addinfo_db5; \
#ALTER TABLE pj_tankop_tankinfo ENABLE TRIGGER tt_conv_tankop_tankinfo_db5; \
#ALTER TABLE pj_tanks ENABLE TRIGGER tt_conv_tanks_db5; \
#ALTER TABLE pj_tanks ENABLE TRIGGER  tt_tanks; \
#ALTER TABLE pj_tanksbook ENABLE TRIGGER tt_conv_tanksbook_db5; \
#ALTER TABLE pj_td ENABLE TRIGGER tt_conv_td_db5; \
#ALTER TABLE pj_td ENABLE TRIGGER tt_td; \
#ALTER TABLE pj_trksum ENABLE TRIGGER tt_conv_trksum_db5; \
#ALTER TABLE sd_group_user ENABLE TRIGGER tt_group_user; \
#ALTER TABLE sd_payments ENABLE TRIGGER tt_payments; \
#ALTER TABLE sj_bonds ENABLE TRIGGER tt_conv_bonds_db5; \
#ALTER TABLE sj_checks ENABLE TRIGGER tt_conv_checks_db5; \
#ALTER TABLE sj_kassareg ENABLE TRIGGER tt_conv_kassareg_db5; \
#ALTER TABLE sj_kassareg_addinfo ENABLE TRIGGER tt_conv_kassareg_addinfo_db5; \
#ALTER TABLE sj_opers ENABLE TRIGGER tt_conv_opers_db5; \
#ALTER TABLE sj_shifts ENABLE TRIGGER tt_shifts; \
#ALTER TABLE sj_tranz ENABLE TRIGGER tt_conv_tranz_db5; \
#ALTER TABLE sj_tranz ENABLE TRIGGER tt_tranz;")
#echo $SQL >> $OFILE0

SQL=$(echo "ALTER TABLE sj_shifts DISABLE TRIGGER tt_shifts;")
echo $SQL >> $OFILE0

SQL=$(echo "UPDATE utranz.sj_shifts SET timeend = now() WHERE id_shop = ${IDSHOP} AND shift = ( SELECT shift FROM utranz.sj_shifts WHERE id_shop = ${IDSHOP} ORDER BY shift desc limit 1 ) AND timeend IS NULL;")
echo $SQL >> $OFILE0
SQL=$(echo "INSERT INTO public.sj_shifts(id_shop, num, time_beg, time_end) SELECT id_shop, shift, timebeg, timeend FROM utranz.sj_shifts WHERE id_shop = ${IDSHOP} ORDER BY shift desc limit 1;")
echo $SQL >> $OFILE0

SQL=$(echo "ALTER TABLE sj_shifts ENABLE TRIGGER tt_shifts;")
echo $SQL >> $OFILE0

_pc=$((_pc + 3))
_pc_dump=$((_pc_dump + 1))

echo "[ ${_pc_dump}% ] : Выгрузка завершена"
echo "SELECT '[ ${_pc}% ] : Восстанавление завершено';" >> $OFILE0


exit 0
