1006 lines
33 KiB
Bash
1006 lines
33 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"0 day" "+%Y-%m-%d")
|
|
echo 'reportDate'=$reportDate
|
|
|
|
echo "
|
|
|
|
DROP TABLE IF EXISTS public.warehouse_analysis_stage1 ;
|
|
|
|
|
|
CREATE TABLE public.warehouse_analysis_stage1 AS WITH basetable AS
|
|
(SELECT A.id ,
|
|
CASE
|
|
WHEN origin_area_id= '12416' THEN 'Cikarang'
|
|
WHEN origin_area_id= '21599' THEN 'Semarang'
|
|
WHEN origin_area_id= '30110' THEN 'Surabaya'
|
|
WHEN origin_area_id= '64985' THEN 'Makassar'
|
|
WHEN origin_area_id= '81092' THEN 'Medan'
|
|
WHEN origin_area_id= '36700' THEN 'Samarinda'
|
|
END origin_warehouse ,
|
|
destination_area_id ,
|
|
min(price) price ,
|
|
B.name partner_name,
|
|
C.name Service_name
|
|
FROM raena_transport_management.logistic_rate A
|
|
LEFT JOIN raena_transport_management.logistic_partner B ON A.partner_id = B.id
|
|
LEFT JOIN raena_transport_management.logistic_service C ON A.service_id = C.id
|
|
WHERE A.active_status ='true'
|
|
GROUP BY A.id ,
|
|
CASE
|
|
WHEN origin_area_id= '12416' THEN 'Cikarang'
|
|
WHEN origin_area_id= '21599' THEN 'Semarang'
|
|
WHEN origin_area_id= '30110' THEN 'Surabaya'
|
|
WHEN origin_area_id= '64985' THEN 'Makassar'
|
|
WHEN origin_area_id= '81092' THEN 'Medan'
|
|
WHEN origin_area_id= '36700' THEN 'Samarinda'
|
|
END ,
|
|
destination_area_id ,
|
|
B.name ,
|
|
C.name)
|
|
SELECT AA.*,
|
|
BB.origin_warehouse expected_warehouse
|
|
FROM
|
|
(SELECT A.*,
|
|
B.min_price
|
|
FROM basetable A
|
|
LEFT JOIN
|
|
(SELECT destination_area_id ,
|
|
min(price) min_price,
|
|
partner_name,
|
|
Service_name
|
|
FROM basetable
|
|
GROUP BY destination_area_id ,
|
|
partner_name,
|
|
Service_name) B ON A.destination_area_id=B.destination_area_id
|
|
AND A.partner_name =B.partner_name
|
|
AND A.Service_name= B.Service_name) AA
|
|
LEFT JOIN
|
|
(SELECT DISTINCT min_price,
|
|
service_name,
|
|
destination_area_id ,
|
|
origin_warehouse,
|
|
row_number () over (partition BY min_price,service_name,destination_area_id
|
|
ORDER BY min_price ,service_name ,destination_area_id) rnk
|
|
FROM
|
|
(SELECT DISTINCT A.destination_area_id,
|
|
A.origin_warehouse ,
|
|
A.service_name,
|
|
min_price --A.*,B.min_price
|
|
FROM basetable A
|
|
INNER JOIN
|
|
(SELECT destination_area_id ,
|
|
min(price) min_price,
|
|
partner_name,
|
|
Service_name
|
|
FROM basetable
|
|
GROUP BY destination_area_id ,
|
|
partner_name,
|
|
Service_name) B ON A.destination_area_id=B.destination_area_id
|
|
AND A.partner_name =B.partner_name
|
|
AND A.Service_name= B.Service_name
|
|
AND min_price = Price)AA) BB ON AA.service_name = BB.service_name
|
|
AND AA.min_price = BB.min_price
|
|
AND AA.destination_area_id= BB.destination_area_id
|
|
AND rnk = 1 ;
|
|
|
|
|
|
|
|
DROP TABLE IF EXISTS public.warehouse_analysis_stage3;
|
|
|
|
CREATE TABLE public.warehouse_analysis_stage3 AS
|
|
SELECT DISTINCT coalesce(B.order_id) order_id ,
|
|
A.reference_id,
|
|
E.name service_name ,
|
|
CASE
|
|
WHEN warehouse_code='WH-CGK45' THEN 'Cikarang'
|
|
WHEN warehouse_code='WH-SRG18' THEN 'Semarang'
|
|
WHEN warehouse_code='WH-SUB51' THEN 'Surabaya'
|
|
WHEN warehouse_code='WH-UPG04' THEN 'Makassar'
|
|
WHEN warehouse_code='WH-MES07' THEN 'Medan'
|
|
WHEN warehouse_code='WH-AAP02' THEN 'Samarinda'
|
|
END actual_warehouse,
|
|
B.rate_id,
|
|
sku,
|
|
C.quantity quantity,
|
|
B.shipping_amount shipping_amount,
|
|
C.payment_amount payment_amount
|
|
FROM raena_transport_management.logistic_order A
|
|
LEFT JOIN raena_order_management.sales_sub_order_shipment B ON A.reference_id = B.id
|
|
LEFT JOIN raena_transport_management.logistic_service E ON A.service_id = E.id
|
|
LEFT JOIN ( select order_id , sales_sub_order_shipment_id, coalesce( sku ,parent_sku) sku, sum(coalesce(BB.quantity , AA.quantity)) Quantity ,
|
|
sum(coalesce(BB.payment_amount,AA.payment_amount)) payment_amount from raena_order_management.sales_sub_order AA
|
|
left join raena_order_management.sales_sub_order_parent_child BB
|
|
ON AA.id = BB.sales_sub_order_id
|
|
group by 1,2,3) C ON A.reference_id = C.sales_sub_order_shipment_id
|
|
WHERE cast(A.created_at AS date)>='2022-04-01' and A.status in ('RETURNED','DELIVERED','PICKED_UP','CREATED');
|
|
|
|
DROP TABLE public.warehouse_analysis_stage3_final;
|
|
|
|
CREATE TABLE public.warehouse_analysis_stage3_final AS
|
|
SELECT A.reference_id AS Order_id ,
|
|
reference_id ,
|
|
service_name ,
|
|
'Cikarang' actual_warehouse ,
|
|
-1 rate_id ,
|
|
B.sku ,
|
|
B.quantity ,
|
|
B.shipping_cost shipping_amount ,
|
|
B.discounted_price*B.quantity payment_amount
|
|
FROM public.warehouse_analysis_stage3 A
|
|
LEFT JOIN gm_dashboard B ON A.reference_id = B.external_id
|
|
WHERE A.order_id IS NULL
|
|
UNION
|
|
SELECT *
|
|
FROM public.warehouse_analysis_stage3
|
|
WHERE order_id IS NOT NULL;
|
|
|
|
|
|
DROP TABLE IF EXISTS public.warehouse_analysis_stage1_1;
|
|
|
|
|
|
CREATE TABLE public.warehouse_analysis_stage1_1 AS
|
|
SELECT DISTINCT Z.id order_id ,
|
|
A.reference_id,
|
|
service_name ,
|
|
actual_warehouse,
|
|
A.rate_id,
|
|
A.sku,
|
|
A.quantity,
|
|
A.shipping_amount,
|
|
A.payment_amount,
|
|
payment_status ,
|
|
Z.status order_status,
|
|
Z.order_placed_by,
|
|
Z.created_at,
|
|
z.is_campaign,
|
|
Z.created_by
|
|
FROM raena_order_management.order Z
|
|
INNER JOIN public.warehouse_analysis_stage3_final A ON Z.id=A.order_id
|
|
WHERE Z.payment_status ='Paid'
|
|
AND cast(Z.created_at AS date)>='2022-04-01';
|
|
|
|
DROP TABLE IF EXISTS public.warehouse_analysis_stage1_2;
|
|
|
|
|
|
CREATE TABLE public.warehouse_analysis_stage1_2 AS
|
|
SELECT DISTINCT Z.id order_id ,
|
|
BB.sku sku,
|
|
BB.quantity quantity,
|
|
BB.shipping_cost shipping_amount,
|
|
BB.Brand_name AS Brand_name ,
|
|
shipping_to order_type,
|
|
BB.discounted_price*BB.quantity payment_amount,
|
|
payment_status ,
|
|
Z.status order_status,
|
|
Z.order_placed_by,
|
|
Z.created_at,
|
|
z.is_campaign,
|
|
Z.created_by
|
|
FROM raena_order_management.order Z
|
|
INNER JOIN public.gm_dashboard BB ON Z.id = BB.external_id
|
|
WHERE Z.payment_status ='Paid'
|
|
AND cast(Z.created_at AS date)>='2022-04-01'
|
|
AND BB.external_id NOT IN
|
|
(SELECT order_id
|
|
FROM public.warehouse_analysis_stage3_final) ;
|
|
|
|
;
|
|
|
|
|
|
DROP TABLE IF EXISTS public.warehouse_analysis_stage1_3;
|
|
|
|
|
|
CREATE TABLE public.warehouse_analysis_stage1_3 AS
|
|
SELECT Z.id AS order_id ,
|
|
A.reference_id,
|
|
A.service_name ,
|
|
A.actual_warehouse,
|
|
A.rate_id,
|
|
coalesce(A.sku,B.sku) sku,
|
|
coalesce(A.quantity,B.quantity) quantity,
|
|
coalesce(A.shipping_amount,B.shipping_amount) shipping_amount,
|
|
Z.shipping_to order_type,
|
|
coalesce(A.payment_amount,B.payment_amount) payment_amount,
|
|
Z.payment_status ,
|
|
Z.status order_status,
|
|
Z.order_placed_by,
|
|
Z.created_at,
|
|
z.is_campaign,
|
|
Z.created_by
|
|
FROM raena_order_management.order Z
|
|
LEFT JOIN public.warehouse_analysis_stage1_1 A ON Z.id = A.order_id
|
|
LEFT JOIN public.warehouse_analysis_stage1_2 B ON Z.id = B.order_id
|
|
WHERE Z.payment_status ='Paid'
|
|
AND cast(Z.created_at AS date)>='2022-04-01';
|
|
|
|
|
|
DROP TABLE IF EXISTS public.warehouse_analysis_stage2;
|
|
|
|
|
|
CREATE TABLE public.warehouse_analysis_stage2 AS
|
|
SELECT A.*,
|
|
B.brand_name
|
|
FROM public.warehouse_analysis_stage1_3 A
|
|
LEFT JOIN
|
|
(SELECT sku ,
|
|
B.name brand_name
|
|
FROM raena_catalog_management.product A
|
|
LEFT JOIN raena_catalog_management.brand B ON A.brand_id = B.id) B ON A.sku = B.sku;
|
|
|
|
|
|
|
|
/*
|
|
|
|
CREATE TABLE public.warehouse_analysis_stage2 AS
|
|
SELECT DISTINCT coalesce(A.order_id,Z.id) order_id ,
|
|
A.reference_id,
|
|
service_name ,
|
|
actual_warehouse,
|
|
A.rate_id,
|
|
coalesce(A.sku,BB.sku) sku,
|
|
coalesce(A.quantity,BB.quantity) quantity,
|
|
coalesce(A.shipping_amount,Z.shipping_amount) shipping_amount,
|
|
BB.Brand_name AS Brand_name ,
|
|
shipping_to order_type,
|
|
coalesce(A.payment_amount,BB.discounted_price*BB.quantity) payment_amount,
|
|
payment_status ,
|
|
Z.status order_status,
|
|
Z.order_placed_by,
|
|
Z.created_at,
|
|
z.is_campaign,
|
|
Z.created_by
|
|
FROM raena_order_management.order Z
|
|
LEFT JOIN public.warehouse_analysis_stage3 A ON Z.id=case when A.order_id is null then A.reference_id else A.order_id end
|
|
LEFT JOIN public.gm_dashboard BB ON Z.reference_id = BB.external_id
|
|
WHERE Z.payment_status ='Paid' AND cast(Z.created_at AS date)>='2022-04-01';
|
|
*/
|
|
DROP TABLE IF EXISTS public.shipping_fee_coupon_purpose;
|
|
|
|
|
|
CREATE TABLE public.shipping_fee_coupon_purpose AS
|
|
SELECT O.order_id ,
|
|
C.sub_order_id,
|
|
payment_status,
|
|
order_status,
|
|
sub_order_status,
|
|
sub_order_shipment_status,
|
|
tierName,
|
|
payment_amount,
|
|
shipping_amount AS shipping_fee,
|
|
applied_shipping_amount AS applied_shipping_fee,
|
|
shipping_to order_type,
|
|
order_placed_by,
|
|
email,
|
|
mobile,
|
|
CASE
|
|
WHEN D.min_date = O.Created_at
|
|
AND O.reseller_id = D.reseller_id THEN 'New'
|
|
ELSE 'Old'
|
|
END User_type ,
|
|
province,
|
|
created_at
|
|
FROM
|
|
(SELECT id AS order_id ,
|
|
reseller_id,
|
|
json_extract_path_text(A.reseller_info,'tierName',TRUE) tierName,
|
|
json_extract_path_text(A.reseller_info,'email',TRUE) email,
|
|
json_extract_path_text(A.reseller_info,'mobile',TRUE) mobile,
|
|
lower(shipping_province) province,
|
|
payment_status,
|
|
status order_status,
|
|
shipping_to ,
|
|
order_placed_by ,
|
|
created_at
|
|
FROM raena_order_management.order A
|
|
WHERE is_archived = 'false'
|
|
AND cast(created_at AS date)>='2022-04-01'
|
|
ORDER BY created_at DESC) O
|
|
LEFT JOIN
|
|
(SELECT id sub_order_shipping_id,
|
|
order_id,
|
|
shipping_amount,
|
|
applied_shipping_amount,
|
|
status sub_order_shipment_status
|
|
FROM raena_order_management.sales_sub_order_shipment
|
|
ORDER BY 2) B ON O.order_id = B.order_id
|
|
LEFT JOIN
|
|
(SELECT id sub_order_id ,
|
|
order_id,
|
|
A.status sub_order_status,
|
|
sales_sub_order_shipment_id ,
|
|
B.discounted_price*B.quantity payment_amount
|
|
FROM raena_order_management.sales_sub_order A
|
|
INNER JOIN public.business_report B ON A.order_id = B.external_id
|
|
AND A.parent_sku = B.sku
|
|
ORDER BY 2,
|
|
4 DESC) C ON B.sub_order_shipping_id= C.sales_sub_order_shipment_id
|
|
LEFT JOIN
|
|
(SELECT reseller_id,
|
|
min(created_at) min_date
|
|
FROM raena_order_management.
|
|
ORDER
|
|
WHERE payment_status= 'Paid'
|
|
AND is_archived = 'false'
|
|
GROUP BY reseller_id) D ON O.reseller_id = D.reseller_id
|
|
ORDER BY o.created_at DESC;
|
|
|
|
|
|
DROP TABLE IF EXISTS public.rate_sheet_wise_expected_warehouse ;
|
|
|
|
|
|
|
|
CREATE TABLE public.rate_sheet_wise_expected_warehouse AS
|
|
SELECT order_id ,
|
|
reference_id Sub_order_shipping_id,
|
|
sku ,
|
|
actual_warehouse ,
|
|
expected_warehouse,
|
|
CASE
|
|
WHEN actual_warehouse <>expected_warehouse THEN 'Yes'
|
|
ELSE 'No'
|
|
END AS missed_Inventory,
|
|
service_name ,
|
|
order_type,
|
|
payment_amount,
|
|
payment_status ,
|
|
order_status,
|
|
order_placed_by,
|
|
am_name,
|
|
am_email,
|
|
sum(quantity)quantity,
|
|
sum(shipping_amount) shipping_amount,
|
|
count(order_id) number_of_order,
|
|
brand_name,
|
|
tierName,
|
|
email,
|
|
mobile ,
|
|
province ,
|
|
User_type,
|
|
is_campaign,
|
|
cast((created_at+interval'7 Hours') AS date) AS created_date
|
|
FROM
|
|
(SELECT DISTINCT A.order_id ,
|
|
reference_id,
|
|
A.service_name ,
|
|
actual_warehouse,
|
|
rate_id,
|
|
A.sku,
|
|
quantity,
|
|
shipping_amount,
|
|
expected_warehouse,
|
|
coalesce(Y.Name,A.Brand_name) AS Brand_name ,
|
|
order_type,
|
|
payment_amount,
|
|
payment_status ,
|
|
order_status,
|
|
order_placed_by,
|
|
AA.name am_name,
|
|
AA.email am_email,
|
|
tierName,
|
|
DD.email,
|
|
mobile ,
|
|
province ,
|
|
User_type,
|
|
is_campaign,
|
|
A.created_at
|
|
FROM public.warehouse_analysis_stage2 A
|
|
LEFT JOIN
|
|
(SELECT DISTINCT order_id ,
|
|
tierName,
|
|
email,
|
|
mobile ,
|
|
province ,
|
|
User_type
|
|
FROM public.shipping_fee_coupon_purpose) DD ON A.order_id = DD.order_id
|
|
LEFT JOIN raena_user_management.admin_user AA ON cast(A.created_by AS varchar) = cast(AA.id AS varchar)
|
|
LEFT JOIN raena_catalog_management.product X ON A.sku = X.sku
|
|
LEFT JOIN raena_catalog_management.brand Y ON X.brand_id =Y.id
|
|
LEFT JOIN public.warehouse_analysis_stage1 D ON A.rate_id = D.id
|
|
AND A.service_name = D.service_name)AA
|
|
WHERE sku IS NOT NULL
|
|
GROUP BY order_id ,
|
|
reference_id,
|
|
sku ,
|
|
actual_warehouse ,
|
|
expected_warehouse,
|
|
CASE
|
|
WHEN actual_warehouse <>expected_warehouse THEN 'yes'
|
|
ELSE 'No'
|
|
END ,
|
|
service_name ,
|
|
order_type,
|
|
payment_amount,
|
|
payment_status ,
|
|
order_status,
|
|
order_placed_by,
|
|
am_name,
|
|
am_email,
|
|
brand_name,
|
|
tierName,
|
|
email,
|
|
mobile ,
|
|
province ,
|
|
User_type,
|
|
is_campaign,
|
|
cast((created_at+interval'7 Hours') AS date) ;
|
|
|
|
|
|
DROP TABLE quantity_trend_new;
|
|
CREATE TABLE quantity_trend_new AS
|
|
SELECT is_campaign ,
|
|
frequency ,
|
|
transaction_date ,
|
|
child_sku ,
|
|
warehouse ,
|
|
quantity
|
|
FROM quantity_trend_v1
|
|
WHERE transaction_date <'2022-04-01'
|
|
UNION
|
|
SELECT is_campaign ,
|
|
'Month',
|
|
date_trunc('Month',created_date) :: date ,
|
|
sku ,
|
|
actual_warehouse,
|
|
sum(quantity)
|
|
FROM public.rate_sheet_wise_expected_warehouse
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,
|
|
5;
|
|
|
|
DROP TABLE quantity_trend;
|
|
|
|
|
|
CREATE TABLE quantity_trend AS
|
|
SELECT *
|
|
FROM quantity_trend_new;
|
|
|
|
DROP TABLE IF EXISTS public.rate_sheet_wise_expected_warehouse_expected_shipping;
|
|
|
|
|
|
CREATE TABLE public.rate_sheet_wise_expected_warehouse_expected_shipping AS
|
|
SELECT order_id ,
|
|
reference_id Sub_order_shipping_id,
|
|
sku ,
|
|
actual_warehouse ,
|
|
expected_warehouse,
|
|
CASE
|
|
WHEN actual_warehouse <>expected_warehouse THEN 'Yes'
|
|
ELSE 'No'
|
|
END AS missed_Inventory,
|
|
service_name ,
|
|
order_type,
|
|
payment_amount,
|
|
payment_status ,
|
|
order_status,
|
|
order_placed_by,
|
|
am_name,
|
|
am_email,
|
|
sum(quantity)quantity,
|
|
sum(shipping_amount) shipping_amount,
|
|
sum(price) Per_kg_cost,
|
|
count(order_id) number_of_order,
|
|
brand_name,
|
|
tierName,
|
|
email,
|
|
mobile ,
|
|
province ,
|
|
User_type,
|
|
cast(created_at AS date) AS created_date
|
|
FROM
|
|
(SELECT DISTINCT A.order_id ,
|
|
reference_id,
|
|
A.service_name ,
|
|
actual_warehouse,
|
|
rate_id,
|
|
A.sku,
|
|
quantity,
|
|
shipping_amount,
|
|
expected_warehouse,
|
|
coalesce(Y.Name,A.Brand_name) AS Brand_name ,
|
|
order_type,
|
|
payment_amount,
|
|
D.price,
|
|
payment_status ,
|
|
order_status,
|
|
order_placed_by,
|
|
AA.name am_name,
|
|
AA.email am_email,
|
|
tierName,
|
|
DD.email,
|
|
mobile ,
|
|
province ,
|
|
User_type,
|
|
A.created_at
|
|
FROM public.warehouse_analysis_stage2 A
|
|
LEFT JOIN
|
|
(SELECT DISTINCT order_id ,
|
|
tierName,
|
|
email,
|
|
mobile ,
|
|
province ,
|
|
User_type
|
|
FROM public.shipping_fee_coupon_purpose) DD ON A.order_id = DD.order_id
|
|
LEFT JOIN raena_user_management.admin_user AA ON cast(A.created_by AS varchar) = cast(AA.id AS varchar)
|
|
LEFT JOIN raena_catalog_management.product X ON A.sku = X.sku
|
|
LEFT JOIN raena_catalog_management.brand Y ON X.brand_id =Y.id
|
|
LEFT JOIN public.warehouse_analysis_stage1 D ON A.rate_id = D.id
|
|
AND A.service_name = D.service_name)AA
|
|
WHERE sku IS NOT NULL
|
|
GROUP BY order_id ,
|
|
reference_id,
|
|
sku ,
|
|
actual_warehouse ,
|
|
expected_warehouse,
|
|
CASE
|
|
WHEN actual_warehouse <>expected_warehouse THEN 'yes'
|
|
ELSE 'No'
|
|
END ,
|
|
service_name ,
|
|
order_type,
|
|
payment_amount,
|
|
payment_status ,
|
|
order_status,
|
|
order_placed_by,
|
|
am_name,
|
|
am_email,
|
|
brand_name,
|
|
tierName,
|
|
email,
|
|
mobile ,
|
|
province ,
|
|
User_type,
|
|
cast(created_at AS date);
|
|
|
|
|
|
DROP TABLE IF EXISTS om_distr_actaul_origin;
|
|
|
|
|
|
CREATE TABLE om_distr_actaul_origin AS
|
|
SELECT DISTINCT sub_order_shipping_id,
|
|
service_name,
|
|
actual_warehouse,
|
|
expected_warehouse,
|
|
order_weight,
|
|
rate_id,
|
|
destination_area_id,
|
|
origin_area_id AS actual_origin,
|
|
CASE
|
|
WHEN expected_warehouse='Cikarang' THEN 12416
|
|
WHEN expected_warehouse='Semarang' THEN 21599
|
|
WHEN expected_warehouse='Surabaya' THEN 30110
|
|
WHEN expected_warehouse='Makassar' THEN 64985
|
|
WHEN expected_warehouse='Medan' THEN 81092
|
|
WHEN expected_warehouse='Samarinda' THEN 36700
|
|
END AS expected_origin
|
|
FROM public.rate_sheet_wise_expected_warehouse_expected_shipping a
|
|
INNER JOIN raena_transport_management.logistic_order b ON a.sub_order_shipping_id=b.reference_id
|
|
LEFT JOIN raena_transport_management.logistic_rate c ON b.rate_id=c.id;
|
|
|
|
|
|
DROP TABLE IF EXISTS om_distr_actaul_origin_2;
|
|
|
|
|
|
CREATE TABLE om_distr_actaul_origin_2 AS
|
|
SELECT DISTINCT sub_order_shipping_id,
|
|
actual_warehouse,
|
|
expected_warehouse,
|
|
c.price,
|
|
c.eta_min
|
|
FROM om_distr_actaul_origin b
|
|
INNER JOIN raena_transport_management.logistic_rate c ON b.expected_origin=c.origin_area_id
|
|
AND b.destination_area_id=c.destination_area_id
|
|
INNER JOIN raena_transport_management.logistic_service s ON s.id=c.service_id
|
|
AND s.name=b.service_name
|
|
WHERE rate_id NOTNULL
|
|
AND actual_warehouse!=expected_warehouse;
|
|
|
|
|
|
DROP TABLE IF EXISTS public.OM_Logistic_base_exepected_warehouse;
|
|
|
|
|
|
CREATE TABLE public.OM_Logistic_base_exepected_warehouse AS
|
|
SELECT DISTINCT cast(lo.created_at AS date) AS created_at,
|
|
reference_id,
|
|
order_weight,
|
|
lo.shipping_amount,
|
|
rate_id,
|
|
lr.price AS Shipping_fee_per_kg_weight,
|
|
lr.eta_min AS expected_eta_min
|
|
FROM raena_transport_management.logistic_order lo
|
|
INNER JOIN om_distr_actaul_origin_2 lr ON lo.reference_id=lr.sub_order_shipping_id
|
|
WHERE lo.created_at >= '2022-04-01'
|
|
AND rate_id is NOT NULL;
|
|
|
|
--order_id for suborders
|
|
|
|
DROP TABLE IF EXISTS public.OM_Logistic_base_exepected_warehouse_2;
|
|
|
|
|
|
CREATE TABLE public.OM_Logistic_base_exepected_warehouse_2 AS
|
|
SELECT created_at,
|
|
reference_id,
|
|
order_weight,
|
|
shipping_amount,
|
|
rate_id,
|
|
shipping_fee_per_kg_weight,
|
|
expected_eta_min,
|
|
coalesce(order_id,reference_id) AS order_id,
|
|
sku,
|
|
payment_amount,
|
|
sales_sub_order_id,
|
|
quantity
|
|
FROM
|
|
(SELECT a.*,
|
|
b.order_id,
|
|
b.parent_sku AS sku,
|
|
b.payment_amount ,
|
|
b.id AS sales_sub_order_id,
|
|
b.quantity
|
|
FROM public.OM_Logistic_base_exepected_warehouse a
|
|
LEFT JOIN raena_order_management.sales_sub_order b ON a.reference_id=b.sales_sub_order_shipment_id) A;
|
|
|
|
---sku tagging
|
|
|
|
DROP TABLE IF EXISTS public.OM_Logistic_base_exepected_warehouse_3;
|
|
|
|
|
|
CREATE TABLE public.OM_Logistic_base_exepected_warehouse_3 AS
|
|
SELECT DISTINCT a.*,
|
|
coalesce(b.sku,a.sku) AS new_sku,
|
|
coalesce(b.quantity,a.quantity) AS new_quantity
|
|
FROM public.OM_Logistic_base_exepected_warehouse_2 a
|
|
LEFT JOIN raena_order_management.sales_sub_order_parent_child b ON a.sales_sub_order_id=b.sales_sub_order_id;
|
|
|
|
|
|
DROP TABLE IF EXISTS public.OM_Logistic_base_exepected_warehouse_4;
|
|
|
|
|
|
CREATE TABLE public.OM_Logistic_base_exepected_warehouse_4 AS
|
|
SELECT Created_at,
|
|
order_id,
|
|
reference_id,
|
|
sales_sub_order_id,
|
|
new_quantity AS quantity,
|
|
order_weight,
|
|
shipping_amount,
|
|
shipping_fee_per_kg_weight,
|
|
expected_eta_min,
|
|
new_sku,
|
|
payment_amount,
|
|
b.weight AS actual_weight,
|
|
b.volume_weight
|
|
FROM public.OM_Logistic_base_exepected_warehouse_3 a
|
|
LEFT JOIN
|
|
(SELECT sku,
|
|
id,
|
|
weight,
|
|
(height*width*LENGTH)/6000 AS volume_weight
|
|
FROM raena_catalog_management.product) b ON a.new_sku=b.sku
|
|
WHERE reference_id LIKE 'SH%'
|
|
ORDER BY 2;
|
|
|
|
--final dataset
|
|
|
|
DROP TABLE IF EXISTS public.OM_Logistic_final_shipping_exepected_warehouse_base;
|
|
|
|
|
|
CREATE TABLE public.OM_Logistic_final_shipping_exepected_warehouse_base AS
|
|
SELECT Created_at,
|
|
order_id,
|
|
reference_id,
|
|
paymnet_amount,
|
|
shipping_amount,
|
|
Shipping_fee_per_kg_weight,
|
|
expected_eta_min,
|
|
CASE
|
|
WHEN order_weight<1.3 THEN 1
|
|
WHEN order_weight>=1.3
|
|
AND (ABS(order_weight) - FLOOR(ABS(order_weight))) BETWEEN 0.3 AND 0.999999 THEN FLOOR(order_weight)+1
|
|
ELSE FLOOR(order_weight)
|
|
END AS order_weight,
|
|
CASE
|
|
WHEN compared_weight<1.3 THEN 1
|
|
WHEN compared_weight>=1.3
|
|
AND (ABS(compared_weight) - FLOOR(ABS(compared_weight))) BETWEEN 0.3 AND 0.999999 THEN FLOOR(compared_weight)+1
|
|
ELSE FLOOR(compared_weight)
|
|
END AS final_weight
|
|
FROM
|
|
(SELECT *,
|
|
CASE
|
|
WHEN volume_weight>actual_weight THEN volume_weight
|
|
WHEN volume_weight<actual_weight THEN actual_weight
|
|
WHEN volume_weight=actual_weight THEN actual_weight
|
|
END AS compared_weight
|
|
FROM
|
|
(SELECT Created_at,
|
|
order_id,
|
|
reference_id,
|
|
shipping_amount,
|
|
order_weight,
|
|
Shipping_fee_per_kg_weight,
|
|
expected_eta_min,
|
|
(sum(quantity*actual_weight)) AS actual_weight,
|
|
(sum(quantity*volume_weight)) AS volume_weight,
|
|
sum(payment_amount) AS paymnet_amount
|
|
FROM public.OM_Logistic_base_exepected_warehouse_4
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,
|
|
5,
|
|
6,
|
|
7))
|
|
ORDER BY final_weight DESC;
|
|
|
|
|
|
DROP TABLE IF EXISTS public.OM_Logistic_final_shipping_exepected_warehouse;
|
|
|
|
|
|
CREATE TABLE public.OM_Logistic_final_shipping_exepected_warehouse AS
|
|
SELECT DISTINCT reference_id,
|
|
final_weight AS expected_warehouse_weight,
|
|
Shipping_fee_per_kg_weight*final_weight AS Expected_shipping_fee,
|
|
expected_eta_min
|
|
FROM public.OM_Logistic_final_shipping_exepected_warehouse_base a;
|
|
|
|
--select count(*),count(distinct reference_id) from public.OM_Logistic_final_shipping_exepected_warehouse
|
|
|
|
DROP TABLE IF EXISTS public.rate_sheet_wise_expected_warehouse_expected_shipping_final;
|
|
|
|
|
|
CREATE TABLE public.rate_sheet_wise_expected_warehouse_expected_shipping_final AS
|
|
SELECT DISTINCT a.Sub_order_shipping_id,
|
|
(b.Expected_shipping_fee+insurance_fee) AS Expected_shipping_fee,
|
|
expected_eta_min,
|
|
lr.eta_min AS actual_eta_min,
|
|
o.order_weight
|
|
FROM public.rate_sheet_wise_expected_warehouse_expected_shipping a
|
|
LEFT JOIN public.OM_Logistic_final_shipping_exepected_warehouse b ON a.Sub_order_shipping_id=b.reference_id
|
|
INNER JOIN raena_transport_management.logistic_order o ON a.Sub_order_shipping_id=o.reference_id
|
|
INNER JOIN raena_transport_management.logistic_rate lr ON o.rate_id=lr.id
|
|
WHERE Sub_order_shipping_id is NOT NULL;
|
|
|
|
|
|
DROP TABLE public.om_shipment_jtr_shipping_fee_base_2;
|
|
|
|
|
|
CREATE TABLE public.om_shipment_jtr_shipping_fee_base_2 AS
|
|
SELECT sub_order_shipping_id,
|
|
actual_warehouse,
|
|
expected_warehouse,
|
|
b.eta_min AS actual__JTR_min_eta,
|
|
b.price AS actual_JTR_per_kg_price
|
|
FROM public.om_shipment_jtr_shipping_fee_base_1 a
|
|
INNER JOIN raena_transport_management.logistic_rate b ON a.actual_origin=b.origin_area_id
|
|
AND a.destination_area_id=b.destination_area_id
|
|
WHERE b.service_id=2;
|
|
|
|
|
|
DROP TABLE public.om_shipment_jtr_shipping_fee_base_3;
|
|
|
|
|
|
CREATE TABLE public.om_shipment_jtr_shipping_fee_base_3 AS
|
|
SELECT sub_order_shipping_id,
|
|
actual_warehouse,
|
|
expected_warehouse,
|
|
b.eta_min AS expected__JTR_min_eta,
|
|
b.price AS expected_JTR_per_kg_price
|
|
FROM public.om_shipment_jtr_shipping_fee_base_1 a
|
|
INNER JOIN raena_transport_management.logistic_rate b ON a.expected_origin=b.origin_area_id
|
|
AND a.destination_area_id=b.destination_area_id
|
|
WHERE b.service_id=2;
|
|
|
|
|
|
DROP TABLE public.om_shipment_jtr_shipping_fee_base_4;
|
|
|
|
|
|
CREATE TABLE public.om_shipment_jtr_shipping_fee_base_4 AS
|
|
SELECT a.*,
|
|
b.actual__JTR_min_eta,
|
|
b.actual_JTR_per_kg_price,
|
|
c.expected__JTR_min_eta,
|
|
c.expected_JTR_per_kg_price
|
|
FROM om_shipment_jtr_shipping_fee_base_1 a
|
|
INNER JOIN om_shipment_jtr_shipping_fee_base_2 b ON a.sub_order_shipping_id=b.sub_order_shipping_id
|
|
INNER JOIN om_shipment_jtr_shipping_fee_base_3 c ON a.sub_order_shipping_id=c.sub_order_shipping_id;
|
|
|
|
|
|
DROP TABLE IF EXISTS public.om_shipment_jtr_shipping_fee_base_5;
|
|
|
|
|
|
CREATE TABLE public.om_shipment_jtr_shipping_fee_base_5 AS
|
|
SELECT DISTINCT cast(lo.created_at AS date) AS created_at,
|
|
reference_id,
|
|
lo.order_weight,
|
|
lo.shipping_amount,
|
|
lr.actual__JTR_min_eta,
|
|
lr.actual_JTR_per_kg_price,
|
|
lr.expected__JTR_min_eta,
|
|
lr.expected_JTR_per_kg_price
|
|
FROM raena_transport_management.logistic_order lo
|
|
INNER JOIN om_shipment_jtr_shipping_fee_base_4 lr ON lo.reference_id=lr.sub_order_shipping_id
|
|
WHERE lo.created_at >= '2022-04-01'
|
|
AND lo.rate_id IS NOT NULL;
|
|
|
|
|
|
--order_id for suborders
|
|
|
|
DROP TABLE IF EXISTS public.om_shipment_jtr_shipping_fee_base_6;
|
|
|
|
|
|
CREATE TABLE public.om_shipment_jtr_shipping_fee_base_6 AS
|
|
SELECT created_at,
|
|
reference_id,
|
|
order_weight,
|
|
shipping_amount,
|
|
actual__JTR_min_eta,
|
|
actual_JTR_per_kg_price,
|
|
expected__JTR_min_eta,
|
|
expected_JTR_per_kg_price,
|
|
coalesce(order_id,reference_id) AS order_id,
|
|
sku,
|
|
payment_amount,
|
|
sales_sub_order_id,
|
|
quantity
|
|
FROM
|
|
(SELECT a.*,
|
|
b.order_id,
|
|
b.parent_sku AS sku,
|
|
b.payment_amount ,
|
|
b.id AS sales_sub_order_id,
|
|
b.quantity
|
|
FROM public.om_shipment_jtr_shipping_fee_base_5 a
|
|
LEFT JOIN raena_order_management.sales_sub_order b ON a.reference_id=b.sales_sub_order_shipment_id);
|
|
|
|
---sku tagging
|
|
|
|
DROP TABLE IF EXISTS public.om_shipment_jtr_shipping_fee_base_7;
|
|
|
|
|
|
CREATE TABLE public.om_shipment_jtr_shipping_fee_base_7 AS
|
|
SELECT DISTINCT a.*,
|
|
coalesce(b.sku,a.sku) AS new_sku,
|
|
coalesce(b.quantity,a.quantity) AS new_quantity
|
|
FROM public.om_shipment_jtr_shipping_fee_base_6 a
|
|
LEFT JOIN raena_order_management.sales_sub_order_parent_child b ON a.sales_sub_order_id=b.sales_sub_order_id;
|
|
|
|
|
|
DROP TABLE IF EXISTS public.om_shipment_jtr_shipping_fee_base_8;
|
|
|
|
|
|
CREATE TABLE public.om_shipment_jtr_shipping_fee_base_8 AS
|
|
SELECT Created_at,
|
|
order_id,
|
|
reference_id,
|
|
sales_sub_order_id,
|
|
new_quantity AS quantity,
|
|
order_weight,
|
|
shipping_amount,
|
|
actual__JTR_min_eta,
|
|
actual_JTR_per_kg_price,
|
|
expected__JTR_min_eta,
|
|
expected_JTR_per_kg_price,
|
|
new_sku,
|
|
payment_amount,
|
|
b.weight AS actual_weight,
|
|
b.volume_weight
|
|
FROM public.om_shipment_jtr_shipping_fee_base_7 a
|
|
LEFT JOIN
|
|
(SELECT sku,
|
|
id,
|
|
weight,
|
|
(height*width*LENGTH)/6000 AS volume_weight
|
|
FROM raena_catalog_management.product) b ON a.new_sku=b.sku
|
|
WHERE reference_id LIKE 'SH%'
|
|
ORDER BY 2;
|
|
|
|
--final dataset
|
|
|
|
DROP TABLE IF EXISTS public.om_shipment_jtr_shipping_fee_base_9;
|
|
|
|
|
|
CREATE TABLE public.om_shipment_jtr_shipping_fee_base_9 AS
|
|
SELECT Created_at,
|
|
order_id,
|
|
reference_id,
|
|
paymnet_amount,
|
|
shipping_amount,
|
|
actual__JTR_min_eta,
|
|
actual_JTR_per_kg_price,
|
|
expected__JTR_min_eta,
|
|
expected_JTR_per_kg_price,
|
|
CASE
|
|
WHEN order_weight<1.3 THEN 1
|
|
WHEN order_weight>=1.3
|
|
AND (ABS(order_weight) - FLOOR(ABS(order_weight))) BETWEEN 0.3 AND 0.999999 THEN FLOOR(order_weight)+1
|
|
ELSE FLOOR(order_weight)
|
|
END AS order_weight,
|
|
CASE
|
|
WHEN compared_weight<1.3 THEN 1
|
|
WHEN compared_weight>=1.3
|
|
AND (ABS(compared_weight) - FLOOR(ABS(compared_weight))) BETWEEN 0.3 AND 0.999999 THEN FLOOR(compared_weight)+1
|
|
ELSE FLOOR(compared_weight)
|
|
END AS final_weight
|
|
FROM
|
|
(SELECT *,
|
|
CASE
|
|
WHEN volume_weight>actual_weight THEN volume_weight
|
|
WHEN volume_weight<actual_weight THEN actual_weight
|
|
WHEN volume_weight=actual_weight THEN actual_weight
|
|
END AS compared_weight
|
|
FROM
|
|
(SELECT Created_at,
|
|
order_id,
|
|
reference_id,
|
|
shipping_amount,
|
|
order_weight,
|
|
actual__JTR_min_eta,
|
|
actual_JTR_per_kg_price,
|
|
expected__JTR_min_eta,
|
|
expected_JTR_per_kg_price,
|
|
(sum(quantity*actual_weight)) AS actual_weight,
|
|
(sum(quantity*volume_weight)) AS volume_weight,
|
|
sum(payment_amount) AS paymnet_amount
|
|
FROM public.om_shipment_jtr_shipping_fee_base_8
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,
|
|
5,
|
|
6,
|
|
7,
|
|
8,
|
|
9))
|
|
ORDER BY final_weight DESC;
|
|
|
|
|
|
DROP TABLE IF EXISTS public.om_shipment_jtr_shipping_fee_base_10;
|
|
|
|
|
|
CREATE TABLE public.om_shipment_jtr_shipping_fee_base_10 AS
|
|
SELECT DISTINCT reference_id,
|
|
final_weight ,
|
|
actual__JTR_min_eta,
|
|
actual_JTR_per_kg_price*final_weight AS actual_JTR_shipping_fee,
|
|
expected__JTR_min_eta,
|
|
expected_JTR_per_kg_price*final_weight AS expected_JTR_shipping_fee
|
|
FROM public.om_shipment_jtr_shipping_fee_base_9 a
|
|
WHERE final_weight>10;
|
|
|
|
|
|
DROP TABLE IF EXISTS public.om_shipment_jtr_shipping_fee_base_11;
|
|
|
|
|
|
CREATE TABLE public.om_shipment_jtr_shipping_fee_base_11 AS
|
|
SELECT DISTINCT a.Sub_order_shipping_id,
|
|
(b.actual_JTR_shipping_fee+insurance_fee) AS actual_JTR_shipping_fee,
|
|
actual__JTR_min_eta,
|
|
(b.expected_JTR_shipping_fee+insurance_fee) AS expected_JTR_shipping_fee,
|
|
expected__JTR_min_eta
|
|
FROM public.rate_sheet_wise_expected_warehouse_expected_shipping a
|
|
LEFT JOIN public.om_shipment_jtr_shipping_fee_base_10 b ON a.Sub_order_shipping_id=b.reference_id
|
|
INNER JOIN raena_transport_management.logistic_order o ON a.Sub_order_shipping_id=o.reference_id
|
|
WHERE Sub_order_shipping_id IS NOT NULL;
|
|
|
|
drop table if exists public.om_shipment_jtr_shipping_fee_base_12;
|
|
create table public.om_shipment_jtr_shipping_fee_base_12
|
|
as
|
|
select b.*,a.expected_eta_min
|
|
from public.rate_sheet_wise_expected_warehouse_expected_shipping_final a
|
|
inner join public.om_shipment_jtr_shipping_fee_base_11 b on a.Sub_order_shipping_id=b.Sub_order_shipping_id and expected__JTR_min_eta-a.expected_eta_min<=3;
|
|
|
|
" > /home/ec2-user/cronjob/warehouseAnalysis/warehouseAnalysis.sql
|
|
|
|
psql "host=raen-prd-sg-redshift-cluster.cdqj58hfx4p7.ap-southeast-1.redshift.amazonaws.com user=dbadmin dbname=analytics port=5439 password=5qCif6eyY3Kmg4z" -f/home/ec2-user/cronjob/warehouseAnalysis/warehouseAnalysis.sql > etlwarehouseAnalysis.log
|