88 lines
2.8 KiB
Bash
88 lines
2.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 "
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS raena_analytics.loyalty_reseller_stage1;
|
||
|
|
|
||
|
|
CREATE TABLE raena_analytics.loyalty_reseller_stage1 AS
|
||
|
|
SELECT cast(createdat+interval'7 Hours' as date) created_date,
|
||
|
|
transactionid,
|
||
|
|
eventtype,
|
||
|
|
resellerid ,
|
||
|
|
email,
|
||
|
|
mobile,
|
||
|
|
C.name tier_name,
|
||
|
|
A.status ,
|
||
|
|
points,
|
||
|
|
CASE
|
||
|
|
WHEN lms_orderid LIKE 'OD%' THEN lms_orderid
|
||
|
|
END order_id,
|
||
|
|
totalamount
|
||
|
|
FROM (select distinct * from raena_analytics.lms_transactions) A
|
||
|
|
LEFT JOIN raena_user_management.user B ON A.resellerid = cast(B.id as varchar)
|
||
|
|
LEFT JOIN raena_user_management.tier C ON A.tierid = cast(C.id as varchar) ;
|
||
|
|
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS raena_analytics.loyalty_total_orders;
|
||
|
|
|
||
|
|
CREATE TABLE raena_analytics.loyalty_total_orders AS
|
||
|
|
SELECT cast(A.created_at+interval'7 Hours' as date) AS created_date ,
|
||
|
|
A.reseller_id ,
|
||
|
|
json_extract_path_text(A.reseller_info,'name',true) reseller_name,
|
||
|
|
json_extract_path_text(A.reseller_info,'email',true) reseller_email,
|
||
|
|
json_extract_path_text(A.reseller_info,'mobile',true) reseller_mobile,
|
||
|
|
json_extract_path_text(A.reseller_info,'tierName',true) reseller_tier_name,
|
||
|
|
A.payment_status,
|
||
|
|
A.id order_id,
|
||
|
|
A.status order_status,
|
||
|
|
A.payment_amount payment_price
|
||
|
|
FROM raena_order_management.order A
|
||
|
|
where A.created_at::date >='2021-01-01';
|
||
|
|
|
||
|
|
|
||
|
|
drop table if exists raena_analytics.loyalty_base_1;
|
||
|
|
|
||
|
|
|
||
|
|
create table raena_analytics.loyalty_base_1
|
||
|
|
as
|
||
|
|
select B.created_date,
|
||
|
|
coalesce(B.resellerid,A.reseller_id) reseller_id,
|
||
|
|
A.reseller_name,
|
||
|
|
coalesce(B.email,A.reseller_email) reseller_email,
|
||
|
|
coalesce(B.mobile,A.reseller_mobile) reseller_mobile,
|
||
|
|
coalesce(B.tier_name,A.reseller_tier_name) reseller_tier_name,
|
||
|
|
A.payment_status,
|
||
|
|
A.order_id,
|
||
|
|
A.payment_price,
|
||
|
|
A.order_status,
|
||
|
|
B.transactionid,
|
||
|
|
B.eventtype,
|
||
|
|
B.resellerid lm_reseller,
|
||
|
|
B.email lm_email,
|
||
|
|
B.mobile lm_mobile,
|
||
|
|
B.tier_name,
|
||
|
|
B.status ,
|
||
|
|
B.order_id lm_orders,
|
||
|
|
totalamount totalamount,
|
||
|
|
points loyalty_point
|
||
|
|
from raena_analytics.loyalty_reseller_stage1 B left join raena_analytics.loyalty_total_orders A
|
||
|
|
on A.order_id = B.order_id;
|
||
|
|
|
||
|
|
" > /home/ec2-user/cronjob/redshift/sql_code/loyalty_point_etl.sql
|
||
|
|
|
||
|
|
psql "host=redshift-cluster-1.cdqj58hfx4p7.ap-southeast-1.redshift.amazonaws.com user=dbadmin dbname=analytics port=5439 password=5qCif6eyY3Kmg4z" -f /home/ec2-user/cronjob/redshift/sql_code/loyalty_point_etl.sql
|
||
|
|
|