th-sdp-script-sync-product-catalog

#!/bin/bash

dbhost=192.168.0.100
dbport=5434
dbname=erp_paloma_20180514
dbuser="sts"
dbpass="Awesome123!"
fileNamePrefix="product_"
fileNameSuffix=".csv"
datetime=`date +%Y%m%d%H%M%S`
targetDir="/home/sts/sdp/th-sdp-intgr/productcatalog/sync"
catalogCode="$1"

if [ -z "$catalogCode" ]; then
   echo "Please specify catalog code"
   exit 0
fi

#TRANSFER DATA CALCULATOR

queryTransferCalculatorCatalogPrice=`PGPASSWORD=$dbpass psql -h $dbhost -p $dbport -U $dbuser -d $dbname -c "SELECT f_transfer_calculator_catalog_price( 11, -1, '$datetime', '$catalogCode' )"`


#GENERATE CSV

tempFile=`mktemp`
targetFile="$targetDir/$fileNamePrefix$datetime$fileNameSuffix"

PGPASSWORD=$dbpass psql -h $dbhost -p $dbport -U $dbuser -d $dbname -c "Copy (SELECT A.product_code AS product_code, A.product_name, A.ctgr_product_code AS ctgr_code, A.sub_ctgr_product_code AS sub_ctgr_code, A.barcode AS product_barcode, COALESCE(A.weight,0) AS weight, A.dimension_length AS length, A.dimension_width AS width, A.dimension_height AS height, A.available_stock AS available_stock, A.style_product, A.size, A.color, A.class_product, A.category_name_lv0, A.category_name_lv1, A.category_name_lv2, A.category_name_lv3, A.brand_name, REGEXP_REPLACE(ENCODE(CONVERT_TO(A.product_description, 'UTF-8'), 'base64'), E'[\\n\\r]+', '', 'g' ) AS product_description, A.online_price, A.online_sale_prices FROM vw_product_catalog_for_sdp A WHERE A.catalog_code = '$catalogCode' AND (NOT EXISTS( SELECT 1 FROM sdp_product_sync Z WHERE A.product_id = Z.product_id ) OR NOT EXISTS ( SELECT 1 FROM sdp_product_sync Z WHERE A.product_id = Z.product_id AND A.online_price = Z.online_price AND A.online_sale_prices = Z.online_sale_prices) )) To STDOUT With CSV HEADER DELIMITER E'\t' QUOTE '\"' NULL '';" > $tempFile && \

mkdir -p "$targetDir"
cp "$tempFile" "$targetFile"
rm -f "$tempFile"


#UPDATE sdp_product_sync

queryUpdateSdpProductSync=`PGPASSWORD=$dbpass psql -h $dbhost -p $dbport -U $dbuser -d $dbname -c "WITH tt_unsynced_product_catalog_for_sdp AS ( SELECT A.product_id, A.tenant_id, A.product_code, A.product_name, A.barcode, A.ctgr_product_code, A.sub_ctgr_product_code, A.weight, A.dimension_length, A.dimension_width, A.dimension_height, A.available_stock, A.style_product, A.size, A.color, A.class_product, A.category_name_lv0, A.category_name_lv1, A.category_name_lv2, A.category_name_lv3, A.brand_name,  A.product_description, A.online_price, A.online_sale_prices FROM vw_product_catalog_for_sdp A WHERE A.catalog_code = '$catalogCode' AND ( NOT EXISTS( SELECT 1 FROM sdp_product_sync Z WHERE A.product_id = Z.product_id ) OR NOT EXISTS ( SELECT 1 FROM sdp_product_sync Z WHERE A.product_id = Z.product_id AND A.online_price = Z.online_price AND A.online_sale_prices = Z.online_sale_prices ) ) ), updated_product_sync AS ( UPDATE sdp_product_sync Z SET online_price = A.online_price, online_sale_prices = A.online_sale_prices, update_datetime = '$datetime', update_user_id = -1, version = Z.version+1 FROM tt_unsynced_product_catalog_for_sdp A WHERE EXISTS ( SELECT 1 FROM sdp_product_sync B WHERE A.product_id = B.product_id ) ) INSERT INTO sdp_product_sync( product_id, tenant_id, product_code, product_name, product_barcode, ctgr_product_code, sub_ctgr_product_code, weight, length, width, height, available_stock, style_product, size, color, class_product, category_name_lv0, category_name_lv1, category_name_lv2, category_name_lv3, brand_name, product_description, online_price, online_sale_prices, create_datetime, update_datetime, create_user_id, update_user_id, version) SELECT A.product_id, A.tenant_id, A.product_code, A.product_name, A.barcode, A.ctgr_product_code, A.sub_ctgr_product_code, A.weight, A.dimension_length, A.dimension_width, A.dimension_height, A.available_stock, A.style_product, A.size, A.color, A.class_product, A.category_name_lv0, A.category_name_lv1, A.category_name_lv2, A.category_name_lv3, A.brand_name,  A.product_description, A.online_price, A.online_sale_prices, '$datetime' AS create_datetime, '$datetime' AS update_datetime, -1 AS create_user_id, -1 AS update_user_id, 0 AS version FROM tt_unsynced_product_catalog_for_sdp A WHERE NOT EXISTS ( SELECT 1 FROM sdp_product_sync B WHERE A.product_id = B.product_id )"`

#UPLOAD


BASE_DIR="/home/sts/sdp/th-sdp-intgr"
SOURCE_SYNC_DIR="$BASE_DIR/productcatalog/sync"
SOURCE_DONE_DIR="$BASE_DIR/productcatalog/done"
INPUT_EXTS=".csv"

TARGET_DIR="/home/sts/sdp/th-sdp-intgr/product/input"
TARGET_HOST="192.168.0.100"
TARGET_PORT="10061"
TARGET_PEM="/home/sts/.ssh/character_land.pem"
TARGET_USERNAME="ubuntu"
TARGET_FILE_OWN="sts:sts"
TARGET_TEMP_DIR="/tmp"

echo "================================== "
echo "* SDP Processed * "
echo "* Start : "$(date +"%m-%d-%Y %T")
cd $BASE_DIR
echo ">> Get list file from $SOURCE_SYNC_DIR"

FILES=( `ls -1 $SOURCE_SYNC_DIR | grep $INPUT_EXTS` )

for FILE in "${FILES[@]}"; do

    echo ">> Retrieved  $SOURCE_SYNC_DIR/$FILE"
    MOVED_FILE=$(scp -P $TARGET_PORT -i "$TARGET_PEM" "$SOURCE_SYNC_DIR/$FILE" "$TARGET_USERNAME@$TARGET_HOST:$TARGET_TEMP_DIR" && echo "PROCESS_DONE")

    if [ $MOVED_FILE="PROCESS_DONE" ]; then
        MOVE_FROM_TEMP=$(ssh -p $TARGET_PORT -i "$TARGET_PEM" "$TARGET_USERNAME@$TARGET_HOST" "sudo chmod 666 \"$TARGET_TEMP_DIR/$FILE\" && sudo chown $TARGET_FILE_OWN \"$TARGET_TEMP_DIR/$FILE\" && sudo mv \"$TARGET_TEMP_DIR/$FILE\" \"$TARGET_DIR/\" && echo \"PROCESS_DONE\"")
        if [ $MOVE_FROM_TEMP="PROCESS_DONE" ]; then
            echo ">> Moving server file to success $FILE"
            mv $SOURCE_SYNC_DIR/$FILE $SOURCE_DONE_DIR/
        else
            echo ">> Moving file $FILE from temp to failed"
        fi
    else
        echo ">> Moving server file to failed $FILE"
    fi
done
echo "* End Processed : "$(date +"%m-%d-%Y %T")