131 lines
3.8 KiB
Bash
131 lines
3.8 KiB
Bash
#!/bin/bash
|
|
|
|
|
|
echo -e " \n----------- ACCEPTING NUMBER OF DAYS BEFORE THE RUN DATE FOR WHICH THE REPORT IS TO BE RUN --------------\n"
|
|
|
|
backDay=$1
|
|
|
|
echo $backDay
|
|
|
|
echo -e " \n------------- DATE IN THE REQUIRED FORMAT --------------\n"
|
|
reportDate=$(date -d"$backDay day ago" "+%Y-%m-%d")
|
|
date
|
|
echo 'reportDate'=$reportDate
|
|
|
|
|
|
echo "
|
|
--------------------------Rdash -------------------------
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.om_mongo_brand_category;
|
|
|
|
|
|
CREATE TABLE raena_analytics.om_mongo_brand_category AS
|
|
SELECT rsellviewid,
|
|
brand::json->> 'name' AS Brand_name,
|
|
arr.item_object::json ->> 'hasChild' AS has_child,
|
|
arr.item_object::json ->> 'id' AS actual_categoryid,
|
|
arr.item_object::json ->> 'name' AS actual_category_name
|
|
FROM raena_analytics.mongo_baseproducts,
|
|
jsonb_array_elements(replace(categorypath,'''','')::jsonb) WITH
|
|
ORDINALITY arr(item_object, POSITION);
|
|
|
|
DROP TABLE IF exists raena_analytics.om_mongo_Order_item_details;
|
|
|
|
|
|
CREATE TABLE raena_analytics.om_mongo_Order_item_details AS
|
|
SELECT orderid,
|
|
orderitemid,
|
|
quantity,
|
|
originalprice,
|
|
rsellviewid,
|
|
productsource,
|
|
sku
|
|
FROM raena_analytics.mongo_orderitems mo
|
|
ORDER BY orderid ;
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.om_mongo_Order_details;
|
|
|
|
|
|
CREATE TABLE raena_analytics.om_mongo_Order_details AS
|
|
SELECT createdat,
|
|
orderid,
|
|
storeinfo::json->> 'name' AS store,
|
|
storeinfo::json->> 'storeOwner' AS sellername,
|
|
storeinfo::json->> 'storeOwnerPhone' AS Sellerphone,
|
|
storename,
|
|
marketplaceorderid,
|
|
totalamount ,
|
|
orderStatus
|
|
FROM raena_analytics.mongo_orders;
|
|
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.om_mongo_orders_summary_metabase;
|
|
|
|
|
|
CREATE TABLE raena_analytics.om_mongo_orders_summary_metabase AS
|
|
SELECT DISTINCT mod.*,
|
|
moid.orderitemid,
|
|
moid.quantity,
|
|
moid.originalprice,
|
|
moid.rsellviewid,
|
|
moid.productsource,
|
|
moid.sku,
|
|
mbc.brand_name,
|
|
mbc.has_child,
|
|
mbc.actual_categoryid,
|
|
mbc.actual_category_name,
|
|
row_number() over (partition BY mod.orderid,orderitemid,sku
|
|
ORDER BY originalprice DESC) AS R
|
|
FROM raena_analytics.om_mongo_Order_details MOD
|
|
LEFT JOIN raena_analytics.om_mongo_Order_item_details moid ON MOD.orderid=moid.orderid
|
|
LEFT JOIN raena_analytics.om_mongo_brand_category mbc ON moid.rsellviewid=mbc.rsellviewid;
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.OM_Mongo_orders_dump_metabase;
|
|
|
|
|
|
CREATE TABLE raena_analytics.OM_Mongo_orders_dump_metabase AS
|
|
SELECT orderid,
|
|
status,
|
|
shippingamount,
|
|
paymenttype,
|
|
createdat,
|
|
isarchived,
|
|
invoicedata,
|
|
estimatedeliverydate,
|
|
delivereddate,
|
|
pickupdonedate,
|
|
actualshippingdate,
|
|
daystoship,
|
|
currency,
|
|
cancelreason,
|
|
cancelby,
|
|
buyercancelreason,
|
|
orderupdatedat,
|
|
ordercreatedat,
|
|
totalamount,
|
|
orderstatus,
|
|
paymenttime,
|
|
paymentstatus,
|
|
storename,
|
|
marketplacestoreid,
|
|
marketplaceorderid,
|
|
sellerbusinessprostoreid,
|
|
sellerbusinessproid,
|
|
sellerraenaemail,
|
|
sellerraenaphonenumber,
|
|
sellerraenausername,
|
|
sellerraenauserid,
|
|
fulfillmentstatus,
|
|
marketplaceid,
|
|
storeid,
|
|
orderref,
|
|
storeinfo::json->> 'storeOwner' AS sellername,
|
|
storeinfo::json->> 'storeOwnerPhone' AS Sellerphone
|
|
FROM raena_analytics.mongo_orders;
|
|
|
|
|
|
|
|
" > /home/ec2-user/cronjob/postgresql/360rdash/360_rdash_etl.sql
|
|
|
|
psql "host=analytics-db-instance-1.cd7qipz3esdx.ap-southeast-1.rds.amazonaws.com user=dbadmin dbname=analytics port=5432 password=5qCif6eyY3Kmg4z" -f /home/ec2-user/cronjob/postgresql/360rdash/360_rdash_etl.sql
|