aadddrr

th-sdp-script-sync-product-catalog.sh

Jul 25th, 2018
124
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Bash 6.57 KB | None | 0 0
  1. #!/bin/bash
  2.  
  3. #Setting generate CSV
  4. dbhost=localhost
  5. dbport=5432
  6. dbname=erp
  7. dbuser="sts"
  8. dbpass="Awesome123!"
  9. fileNamePrefix="product_catalog_"
  10. fileNameSuffix=".csv"
  11. datetime=`date +%Y%m%d%H%M%S`
  12. targetDir="/home/sts/sdp/th-sdp-intgr/productcatalog/sync"
  13. catalogCode="$1"
  14. concatenator="_"
  15.  
  16. #Setting upload file
  17. BASE_DIR="/home/sts/sdp/th-sdp-intgr"
  18. SOURCE_SYNC_DIR="$BASE_DIR/productcatalog/sync"
  19. SOURCE_DONE_DIR="$BASE_DIR/productcatalog/done"
  20.  
  21. TARGET_DIR="/home/sts/sdp/th-sdp-intgr/product/input"
  22. TARGET_HOST="35.229.170.154"
  23. TARGET_PORT="22"
  24. TARGET_PEM=""
  25. TARGET_USERNAME="sts"
  26. TARGET_FILE_OWN="sts:sts"
  27. TARGET_TEMP_DIR="/tmp"
  28.  
  29. FILE=$fileNamePrefix$catalogCode$concatenator$datetime$fileNameSuffix
  30.  
  31.  
  32.  
  33. if [ -z "$catalogCode" ]; then
  34.    echo "Please specify a catalog code"
  35.    exit 0
  36. fi
  37.  
  38. #TRANSFER DATA CALCULATOR
  39.  
  40. #queryTransferCalculatorCatalogPrice=`PGPASSWORD=$dbpass psql -h $dbhost -p $dbport -U $dbuser -d $dbname -c "SELECT f_transfer_calculator_catalog_price( 11, -1, '$datetime', '$catalogCode' )"`
  41.  
  42.  
  43. #GENERATE CSV
  44.  
  45. tempFile=`mktemp`
  46. targetFile="$targetDir/$fileNamePrefix$catalogCode$concatenator$datetime$fileNameSuffix"
  47.    
  48. 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, A.flg_manage_stock 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 && \
  49.    
  50. mkdir -p "$targetDir"
  51. mkdir -p "$SOURCE_DONE_DIR"
  52. cp "$tempFile" "$targetFile"
  53. rm -f "$tempFile"
  54.  
  55. csvRowNumberInclHeader=$( cat "$targetFile" | wc -l )
  56. csvRowNumber=$(($csvRowNumberInclHeader-1))
  57.  
  58.  
  59. if (( $csvRowNumber > 0 )); then
  60.     echo "$csvRowNumber product data will be processed"
  61.  
  62.     #UPDATE sdp_product_sync
  63.  
  64.     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, A.flg_manage_stock 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 Z.product_id = A.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, flg_manage_stock, create_datetime, update_datetime, create_user_id, update_user_id, version, flg_process) 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, A.flg_manage_stock, '$datetime' AS create_datetime, '$datetime' AS update_datetime, -1 AS create_user_id, -1 AS update_user_id, 0 AS version, 'Y' 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 )"`
  65.  
  66.     #UPLOAD
  67.  
  68.     echo "================================== "
  69.     echo "* SDP Processed * "
  70.     echo "* Start : "$(date +"%m-%d-%Y %T")
  71.     cd $BASE_DIR
  72.  
  73.     echo ">> Retrieved  $SOURCE_SYNC_DIR/$FILE"
  74.     #MOVED_FILE=$(scp -P $TARGET_PORT -i "$TARGET_PEM" "$SOURCE_SYNC_DIR/$FILE" "$TARGET_USERNAME@$TARGET_HOST:$TARGET_TEMP_DIR" && echo "PROCESS_DONE")
  75.     MOVED_FILE=$(scp -P $TARGET_PORT "$SOURCE_SYNC_DIR/$FILE" "$TARGET_USERNAME@$TARGET_HOST:$TARGET_TEMP_DIR" && echo "PROCESS_DONE")
  76.    
  77.     if [ $MOVED_FILE="PROCESS_DONE" ]; then
  78.  
  79.     CREATE_TARGET_DIR=$(ssh -p $TARGET_PORT "$TARGET_USERNAME@$TARGET_HOST" "mkdir -p \"$TARGET_DIR\" && echo \"TARGET_DIR_CREATED\"")
  80.  
  81.     #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\"")
  82.     MOVE_FROM_TEMP=$(ssh -p $TARGET_PORT "$TARGET_USERNAME@$TARGET_HOST" "chmod 666 \"$TARGET_TEMP_DIR/$FILE\" &&  chown $TARGET_FILE_OWN \"$TARGET_TEMP_DIR/$FILE\" && mv \"$TARGET_TEMP_DIR/$FILE\" \"$TARGET_DIR/\" && echo \"PROCESS_DONE\"")
  83.     if [ $MOVE_FROM_TEMP="PROCESS_DONE" ]; then
  84.         echo ">> Moving server file to success $FILE"  
  85.         mv $SOURCE_SYNC_DIR/$FILE $SOURCE_DONE_DIR/
  86.     else
  87.         echo ">> Moving file $FILE from temp to failed"    
  88.     fi
  89.     else
  90.     echo ">> Moving server file to failed $FILE"    
  91.     fi
  92.     echo "* End Processed : "$(date +"%m-%d-%Y %T")
  93.  
  94. else
  95.  
  96.     echo "No data to be processed"
  97.     echo ">> Moving file $FILE"    
  98.     mv $SOURCE_SYNC_DIR/$FILE $SOURCE_DONE_DIR/    
  99.  
  100. fi
Add Comment
Please, Sign In to add comment