#!/bin/bash

#INIT	
	HOST='******'
	DB='******\TEST SOLO.FDB'
	USER='******'
	PASS='******'
	
	EXP_FNAME_ORDERS='fb-export-orders.sql'
	EXP_FNAME_USERS='fb-export-users.sql'
	IMPORTSQL="fb-import.sql"
	
	TESTMODE="first 1" # чтобы быстрее работало в процессе отладки

	SQL_EXPORT_ORDERS="select $TESTMODE
        o.id,
        o.itm_ordernum,
        Coalesce(cast( o.plan_date_firststage as date),'01.01.2000') as StartDate,
        ls.status_description,
        coalesce( o.order_total_cost,0) as TotalCost,
        o.order_pay,
        iif ( o.order_status <7,
            iif(
                COALESCE (o.order_pay,0)>COALESCE (o.order_total_cost,0)*0.5,
                0,
                COALESCE (o.order_total_cost,0)/2-COALESCE (o.order_pay,0)
                ),
            COALESCE (o.order_total_cost,0)-COALESCE (o.order_pay,0)
        ) as Debt,
        ju.date_pack as date_pack,
        jo.fact_date_out as date_out,
        ju.box_count as box_packed,
        jo.box_count as box_out,
        jo.driver_name as driver_name,
        FILEPATH_CALC_CLIENT,
        FILEPATH_CALC_MANAGER,
        FILEPATH_BILL,
        o.client as clientname
    from orders o
            left join journal_upack ju on ju.order_id=o.id
            left join journal_out jo on jo.order_id=o.id
            left join list_statuses ls on ls.status_num=o.order_status
            left join clients c on o.client = c.clientname
    where o.order_status > 0;"

	SQL_EXPORT_USERS="select $TESTMODE
		ID,
		upper(USERNAME) as USERNAME,
		USERROLE,
		PASSWD,
		CLIENTID
    FROM USERS
    WHERE USERROLE = 'client'"

echo ">>>> Export data "
echo "> Export orders table.."

# Orders
fbexport \
	-Si \
	-A "UTF8" \
	-H $HOST \
	-D "$DB"  \
	-U $USER \
	-P $PASS \
	-F $EXP_FNAME_ORDERS \
	-Q "$SQL_EXPORT_ORDERS"

# Users
fbexport \
	-Si \
	-A "UTF8" \
	-H $HOST \
	-D "$DB"  \
	-U $USER \
	-P $PASS \
	-F $EXP_FNAME_USERS \
	-Q "$SQL_EXPORT_USERS"


	echo "Delete from orders;" > $IMPORTSQL
	cat $EXP_FNAME_ORDERS      >> $IMPORTSQL
	cat $EXP_FNAME_USERS       >> $IMPORTSQL
echo ">> OK."


#----------------------------------------	
echo ">>>> Send fbimport file via  scp "

# Import data
	HOST='******'
	USER='******'
	REMOTEFILE='/var/docker/firebird-exchangedir/'
	
	 scp ./$IMPORTSQL  $USER@$HOST:$REMOTEFILE
	 echo ">> OK."
echo ">>>> Import fb-import.sql"
	 ssh  $USER@$HOST sudo docker exec solo-firebird bash /firebird/fb-import.sh
echo "-- DONE --"