#!/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 -h 127.0.0.1"
# используемые ниже ключи 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 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 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/p.$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

SQL="SELECT utranz.sf_get_shift_md5( $IDSHOP, $SHIFT, 300);"
MD5=$($psql -Atq -c "$SQL")

SQL="SELECT 'INSERT INTO utranz.sj_shifts VALUES ($IDSHOP, $SHIFT, TO_TIMESTAMP('''||TO_CHAR(timebeg,'YYYY-MM-DD HH24:MI:SS')||''',''YYYY-MM-DD HH24:MI:SS''),'||CASE WHEN timeend IS NULL THEN 'null' ELSE 'TO_TIMESTAMP('''||TO_CHAR(timeend,'YYYY-MM-DD HH24:MI:SS')||''',''YYYY-MM-DD HH24:MI:SS'')' END||',''$MD5'',0) ON CONFLICT (id_shop,shift) DO UPDATE SET timebeg=TO_TIMESTAMP('''||TO_CHAR(timebeg,'YYYY-MM-DD HH24:MI:SS')||''',''YYYY-MM-DD HH24:MI:SS''), timeend='||CASE WHEN timeend IS NULL THEN 'null' ELSE 'TO_TIMESTAMP('''||TO_CHAR(timeend,'YYYY-MM-DD HH24:MI:SS')||''',''YYYY-MM-DD HH24:MI:SS'')' END||',md5sum=''$MD5'';' FROM utranz.sj_shifts WHERE id_shop = $IDSHOP and shift = $SHIFT ORDER BY shift"
$psql -Atq -P null='null' -F $'\t' -c "$SQL" >> $OFILE

echo "CREATE TEMP TABLE tmp_pj_tanks AS SELECT * FROM utranz.pj_tanks WHERE 0=1 WITH NO DATA;" >> $OFILE
echo "--TABLE:pj_tanks" >> $OFILE
echo "COPY tmp_pj_tanks (" >> $OFILE
SQL=$(echo 'SELECT * FROM'" utranz.pj_tanks WHERE id_shop = $IDSHOP AND shift = $SHIFT AND (typ<10 OR typ=21) AND lvl>0 AND volume>0 AND density>0 ORDER BY id_shop, shift, tanknum, typ, timeop")
$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 "INSERT INTO utranz.pj_tanks (SELECT * FROM tmp_pj_tanks) ON CONFLICT ON CONSTRAINT pj_tanks_pkey DO NOTHING;" >>  $OFILE 
echo "DROP TABLE tmp_pj_tanks;" >> $OFILE
echo "" >> $OFILE

echo "CREATE TEMP TABLE tmp_pj_tankop AS SELECT * FROM utranz.pj_tankop WHERE 0=1 WITH NO DATA;" >> $OFILE
echo "--TABLE:pj_tankop" >> $OFILE
echo "COPY tmp_pj_tankop (" >> $OFILE

SQL=$(echo "WITH tank_changes AS (SELECT 
    timeop, 
    id_shop, 
    tanknum, 
    volume, 
    CASE WHEN weight=0 THEN round(volume*density,2) ELSE weight END as weight,
    prodcod,
    COALESCE(volume - LAG(volume) OVER (PARTITION BY tanknum, prodcod ORDER BY timeop), 0) AS volume_increase,
    max(timeop) OVER (PARTITION BY tanknum, prodcod) as lasttime,
    typ
  FROM utranz.pj_tanks 
 WHERE id_shop=$IDSHOP AND shift=$SHIFT AND (typ<10 OR typ=21) AND lvl>0 AND volume>0 AND density>0
),
start_sluice AS (
  SELECT 
    t1.timeop,
    t1.id_shop,
    t1.tanknum,
    t1.prodcod,
    t1.volume,
    t1.weight,
    'start' AS event
  FROM tank_changes t1
  LEFT JOIN tank_changes t2 ON t1.tanknum = t2.tanknum AND t1.prodcod=t2.prodcod
      AND t1.timeop BETWEEN t2.timeop - INTERVAL '10 minutes' AND t2.timeop
  WHERE t1.timeop<t1.lasttime - INTERVAL '10 minutes'
  GROUP BY t1.timeop, t1.id_shop, t1.tanknum, t1.prodcod, t1.volume, t1.weight
  HAVING 
    SUM(t2.volume_increase) > 50 AND MIN(t2.volume_increase)>-1
),
end_sluice AS (
  SELECT 
    t1.timeop,
    t1.id_shop,
    t1.tanknum,
    t1.prodcod,
    t1.volume,
    t1.weight,
    'end' AS event
  FROM tank_changes t1
  LEFT JOIN tank_changes t2 ON t1.tanknum = t2.tanknum
      AND t1.timeop BETWEEN t2.timeop - INTERVAL '10 minutes' AND t2.timeop 
  WHERE t1.timeop<t1.lasttime - INTERVAL '10 minutes'
  GROUP BY t1.timeop, t1.id_shop, t1.tanknum, t1.prodcod, t1.volume, t1.weight
  HAVING 
    SUM(t2.volume_increase) <= -30
    OR MAX(t2.volume_increase) < 0.5
),
all_events AS (
  SELECT * FROM start_sluice
  UNION ALL
  SELECT * FROM end_sluice
  UNION ALL
  SELECT timeop, id_shop, tanknum, prodcod, volume, weight, 'end' AS event
    FROM tank_changes WHERE typ=2
),
ranked_events AS (
  SELECT *,
    COALESCE(LAG(event) OVER (PARTITION BY tanknum, prodcod ORDER BY timeop),'end') AS event_prev
  FROM all_events
),
r AS (SELECT *,
             LAG(timeop) OVER (PARTITION BY tanknum, prodcod ORDER BY timeop) AS timebeg,
             LAG(volume) OVER (PARTITION BY tanknum, prodcod ORDER BY timeop) AS volume_beg,
             LAG(weight) OVER (PARTITION BY tanknum, prodcod ORDER BY timeop) AS weight_beg,
             row_number() OVER (PARTITION BY 1 ORDER BY timeop, tanknum, event) as rn
        FROM ranked_events WHERE event_prev!=event)
SELECT $IDSHOP as id_shop, $SHIFT*100+row_number() OVER (PARTITION BY 1 ORDER BY timeop, tanknum) as id_tankop, 
       $SHIFT as shift, 1 as place, 8001 as typ, 
        prodcod, tanknum, volume-volume_beg as volume, weight - weight_beg as weight, 
        timebeg as timebeg, timeop as timeend, 0 as id_user
  FROM r WHERE event='end' AND rn<100;")
$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 "INSERT INTO utranz.pj_tankop (SELECT * FROM tmp_pj_tankop) ON CONFLICT ON CONSTRAINT pj_tanpop_pkey DO NOTHING;" >>  $OFILE 
echo "DROP TABLE tmp_pj_tankop;" >> $OFILE
echo "" >> $OFILE

echo "CREATE TEMP TABLE tmp_pj_tankop_tankinfo AS SELECT * FROM utranz.pj_tankop_tankinfo WHERE 0=1 WITH NO DATA;" >> $OFILE
echo "--TABLE:pj_tankop_tankinfo" >> $OFILE
echo "COPY tmp_pj_tankop_tankinfo (" >> $OFILE

SQL=$(echo "WITH tank_changes AS (SELECT 
    timeop, 
    id_shop, 
    tanknum, 
    sondmsk, lvl, volume, density, 
    CASE WHEN weight=0 THEN round(volume*density,2) ELSE weight END as weight,
    waterl, waterv, t, x,
    prodcod,
    COALESCE(volume - LAG(volume) OVER (PARTITION BY tanknum, prodcod ORDER BY timeop), 0) AS volume_increase,
    max(timeop) OVER (PARTITION BY tanknum, prodcod) as lasttime,
    typ
  FROM utranz.pj_tanks 
 WHERE id_shop=$IDSHOP AND shift=$SHIFT AND (typ<10 OR typ=21) AND lvl>0 AND volume>0 AND density>0
),
start_sluice AS (
  SELECT 
    t1.timeop,
    t1.id_shop,
    t1.tanknum,
    t1.prodcod,
    t1.sondmsk, t1.lvl, t1.volume, t1.density, t1.weight, t1.waterl, t1.waterv, t1.t, t1.x,
    'start' AS event
  FROM tank_changes t1
  LEFT JOIN tank_changes t2 ON t1.tanknum = t2.tanknum AND t1.prodcod=t2.prodcod
      AND t1.timeop BETWEEN t2.timeop - INTERVAL '10 minutes' AND t2.timeop
  WHERE t1.timeop<t1.lasttime - INTERVAL '10 minutes'
  GROUP BY t1.timeop, t1.id_shop, t1.tanknum, t1.prodcod, 
           t1.sondmsk, t1.lvl, t1.volume, t1.density, t1.weight, t1.waterl, t1.waterv, t1.t, t1.x
  HAVING 
    SUM(t2.volume_increase) > 50 AND MIN(t2.volume_increase)>-1
),
end_sluice AS (
  SELECT 
    t1.timeop,
    t1.id_shop,
    t1.tanknum,
    t1.prodcod,
    t1.sondmsk, t1.lvl, t1.volume, t1.density, t1.weight, t1.waterl, t1.waterv, t1.t, t1.x,
    'end' AS event
  FROM tank_changes t1
  LEFT JOIN tank_changes t2 ON t1.tanknum = t2.tanknum
      AND t1.timeop BETWEEN t2.timeop - INTERVAL '10 minutes' AND t2.timeop 
  WHERE t1.timeop<t1.lasttime - INTERVAL '10 minutes'
  GROUP BY t1.timeop, t1.id_shop, t1.tanknum, t1.prodcod, 
           t1.sondmsk, t1.lvl, t1.volume, t1.density, t1.weight, t1.waterl, t1.waterv, t1.t, t1.x
  HAVING 
    SUM(t2.volume_increase) <= -30
    OR MAX(t2.volume_increase) < 0.5
),
all_events AS (
  SELECT * FROM start_sluice
  UNION ALL
  SELECT * FROM end_sluice
  UNION ALL
  SELECT timeop, id_shop, tanknum, prodcod, sondmsk, lvl, volume, 
  density, weight, waterl, waterv, t, x, 'end' AS event
    FROM tank_changes WHERE typ=2
),
ranked_events AS (
  SELECT *,
    COALESCE(LAG(event) OVER (PARTITION BY tanknum, prodcod ORDER BY timeop),'end') AS event_prev
  FROM all_events
),
r AS (SELECT *,
             LAG(timeop) OVER (PARTITION BY tanknum, prodcod ORDER BY timeop) AS timebeg,
             LAG(sondmsk) OVER (PARTITION BY tanknum, prodcod ORDER BY timeop) AS sondmsk_beg,
             LAG(lvl) OVER (PARTITION BY tanknum, prodcod ORDER BY timeop) AS lvl_beg,
             LAG(volume) OVER (PARTITION BY tanknum, prodcod ORDER BY timeop) AS volume_beg,
             LAG(density) OVER (PARTITION BY tanknum, prodcod ORDER BY timeop) AS density_beg,
             LAG(weight) OVER (PARTITION BY tanknum, prodcod ORDER BY timeop) AS weight_beg,
             LAG(waterl) OVER (PARTITION BY tanknum, prodcod ORDER BY timeop) AS waterl_beg,
             LAG(waterv) OVER (PARTITION BY tanknum, prodcod ORDER BY timeop) AS waterv_beg,
             LAG(t) OVER (PARTITION BY tanknum, prodcod ORDER BY timeop) AS t_beg,
             LAG(x) OVER (PARTITION BY tanknum, prodcod ORDER BY timeop) AS x_beg,
             row_number() OVER (PARTITION BY 1 ORDER BY timeop, tanknum, event) as rn
        FROM ranked_events WHERE event_prev!=event)
SELECT $IDSHOP as id_shop, $SHIFT*100+row_number() OVER (PARTITION BY 1 ORDER BY timeop, tanknum, prodcod) as id_tankop, 
       1 as typ, tanknum, prodcod,
       sondmsk_beg as sondmsk, lvl_beg as lvl, volume_beg as volume, density_beg as density, 
       weight_beg as weight, waterl_beg as waterl, waterv_beg as waterv, t_beg as t, x_beg as x
  FROM r WHERE event='end' AND rn<100
UNION ALL
SELECT $IDSHOP as id_shop, $SHIFT*100+row_number() OVER (PARTITION BY 1 ORDER BY timeop, tanknum, prodcod) as id_tankop, 
       2 as typ, tanknum, prodcod,
       sondmsk, lvl, volume, density, weight, waterl, waterv, t, x
  FROM r WHERE event='end' AND rn<100")
$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 "INSERT INTO utranz.pj_tankop_tankinfo (SELECT * FROM tmp_pj_tankop_tankinfo) ON CONFLICT ON CONSTRAINT pj_tanpop_tankinfo_pkey DO NOTHING;" >>  $OFILE 
echo "DROP TABLE tmp_pj_tankop_tankinfo;" >> $OFILE
echo "" >> $OFILE

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