#!/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 "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_listsale_list sd_payments_listsale_group_list sd_payments_listsale \
 sd_user_list 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_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_addinfo utranz.sj_ppcards \
 utranz.sj_prg_loyalty utranz.sj_tranz_addinfo utranz.sj_tranz utranz.sj_shifts_sub utranz.sj_shifts utranz.sj_shifts_addinfo_nl \
 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"

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 sd_payments_listsale sd_payments_listsale_group_list sd_payments_listsale_list"

TABLES_LOAD_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_history pd_products_local_history gd_cafe_keys_local_history pd_tanks_list \
 sj_shifts sj_tranz sj_tranz_addinfo pj_preset pj_td \
 sj_checks sj_checks_ext sj_checks_text sj_log_article sj_log_discount gj_articles \
 gj_log_m gj_log_d gj_log_d_addinfo gj_log_a gj_log_section \
 gj_log_beg gj_log_beg_addinfo \
 pj_tankop pj_tankop_addinfo pj_tankop_tankinfo pj_ttn pj_ttn_ext pj_sliw \
 sj_comment sj_opers sj_mifare_tranz sj_moneyinsert sj_spasibo \
 sj_ppcards sj_ppcards_addinfo pj_tanks pj_tanksbook pj_tdext pj_trksum pj_ac \
 pj_tshdata pd_um4 \
 sj_bonds sj_cards sj_kassareg sj_kassareg_addinfo sj_state sj_taloninp sd_kassalist pd_prodprices pj_newprodprices\
 pd_pipeline sd_group_user sd_user_list"
 
### очистка логов 
for i in $(echo $TABLES_DEL_J); do
  SQL=$(echo "DELETE from $i ;")
  echo $SQL >> $OFILE0
done

### очистка справочников
for i in $(echo $TABLES_DEL_D); do
  SQL=$(echo "DELETE from $i ;")
  echo $SQL >> $OFILE0
done

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

##загрузка справочника
for i in $(echo $TABLES_LOAD_D); do
  echo "COPY $i FROM stdin;" >> $OFILE0
  SQL=$(echo "select * from $i ;")
  $psql -Atq -P null='\N' -F $'\t' -c "$SQL" >> $OFILE0
  echo "\." >> $OFILE0
done

##загрузка логов
for i in $(echo $TABLES_LOAD_J); do
  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
done

##Загрузка параметров
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

##Восстанавление текущих переменных
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(num)+1 as b from 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 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 gj_log_d, (  SELECT id_list, max(trannum) as trannum FROM sj_tranz, gj_log_d WHERE sj_tranz.id_shop=gj_log_d.id_shop AND sj_tranz.trannum=gj_log_d.id_tranz 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.id_tranz=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_intranz, 
                    CASE WHEN oid_intranz IS NOT NULL THEN 1 ELSE NULL END
            FROM gf_get_ost_full( $IDSHOP, now()::timestamp without time zone));")
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

exit 0
