#!/bin/bash
DBUSER=$USER

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

if [ -z "$DBNAME" ]; then
    echo "Не задано имя БД." >& 2
    exit 1
fi

if [ -z "$SHIFT" ]; then
    echo "Не задан номер смены." >& 2
    exit 1
fi

### получить номер магазина (IDSHOP)
if [ "$IDSHOP" == "cur" ]; then
    SQL="select id from sd_ident where id_shop_type=0 order by id desc limit 1"
    IDSHOP=$(psql $DBNAME -U $DBUSER -Atq -c "$SQL" -h localhost)
fi

if [ -z "$IDSHOP" ]; then
    echo "Не задан идентификатор магазина." >& 2
    exit 1
fi

### определить номер смены
if [ "$SHIFT" == "cur" ]; then
    SQL="select max(num) from sj_shifts where id_shop=$IDSHOP limit 1"
    SHIFT=$(psql $DBNAME -U $DBUSER -Atq -c "$SQL" -h localhost)
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 $DBNAME -U $DBUSER -Atq -c "$SQL" -h localhost)
fi

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

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

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


### отчистка ТТН без слива
SQL="delete from pj_ttn where ttn_npp NOT IN (SELECT ttn_npp FROM pj_sliw where id_shop=$IDSHOP) and id_shop=$IDSHOP"
echo "$SQL"
psql $DBNAME -U $DBUSER -c "$SQL" -h localhost
SQL="delete from pj_ttn_ext \
 where ttn_npp NOT IN (SELECT ttn_npp FROM pj_sliw where id_shop=$IDSHOP) and id_shop=$IDSHOP"
echo "$SQL"
psql $DBNAME -U $DBUSER -c "$SQL" -h localhost

#### удаляемые таблицы
TABLES10="sj_shifts"
TABLES20="j_gretn j_soldi gj_articles pj_tdext pj_td pj_preset sj_ppcards_addinfo sj_spasibo sj_log_discount sj_log_article sj_checks_text \
    sj_ppcards sj_checks_ext sj_checks sj_cards sj_spasibo sj_tranz_addinfo sj_ppcards_text sj_tranz"
TABLES30="sj_opers sj_messages sj_bonds pj_sliw pj_ttn pj_ttn_ext \
    pj_tshdata sd_conf_local pj_tanksbook pj_trksum pj_tanks sj_kassareg sj_shifts_ppcards_text \
    gj_log_beg gj_log_beg_addinfo pd_prodprices pj_newprodprices_time pj_newprodprices sd_kassalist sj_events \
    gd_instore_history sd_placelist_history sd_cfg_history sd_place_units_history sd_local_payments_history pd_trk_history pd_products_local_history gd_cafe_keys_local_history sd_relay_history pd_tanks_list_history "
TABLES40="sj_comment gj_log_a gj_log_d gj_log_d_addinfo gj_log_m gj_log_section"
#TABLES50="gj_send"
TABLES60="pj_tankop_addinfo pj_tankop_tankinfo pj_tankop"
TABLES70="gd_count_global"

# получение из БД таблиц, перечисленных в TABLES1
for tables in 60 40 30 20 10 70; 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 ;;
  esac
  
  for i in `echo $TABLES`; do
    if [ $tables -eq 10 ]; then
      SQL=`echo 'delete from'" $i where id_shop = $IDSHOP and num = $SHIFT"`
    elif [ $tables -eq 20 ]; then
      if [ -z "$TRAN1" ]; then continue; fi
      SQL=`echo 'delete from'" $i where id_shop = $IDSHOP and trannum >= $TRAN1 and trannum <= $TRAN2"`
    elif [ $tables -eq 30 ]; then 
      if [ $i == "pj_ttn" ]; then
        if [ -z "$TTN1" ]; then continue; fi
        SQL=`echo 'delete from'" $i where id_shop = $IDSHOP and ttn_npp >= $TTN1 and ttn_npp <= $TTN2"`
      elif [ $i == "pj_ttn_ext" ]; then
        if [ -z "$TTN1" ]; then continue; fi
        SQL=`echo 'delete from'" $i where id_shop = $IDSHOP and ttn_npp >= $TTN1 and ttn_npp <= $TTN2"`
      else
        SQL=`echo 'delete from'" $i where id_shop = $IDSHOP and shift=$SHIFT"`
      fi
    elif [ $tables -eq 40 ]; then
      if [ -z "$TRAN1" ]; then continue; fi
      SQL=`echo 'delete from'" $i where id_shop = $IDSHOP and id_tranz >= $TRAN1 and id_tranz <= $TRAN2"`
    elif [ $tables -eq 60 ]; then
      if [ -z "$TANKIN1" ]; then continue; fi
      SQL=`echo 'delete from'" $i where id_shop = $IDSHOP and id_tankop >= $TANKIN1 and id_tankop <= $TANKIN2"`
    elif [ $tables -eq 70 ]; then
      SQL=`echo 'delete from'" $i where id_shop = $IDSHOP"`
    fi
    
    echo "$SQL"
    psql $DBNAME -U $DBUSER -c "$SQL" -h localhost
  done
done
