124 lines
3.7 KiB
Bash
124 lines
3.7 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.monthly_sold_product;
|
|
|
|
create table raena_analytics.monthly_sold_product
|
|
as
|
|
select brand_name , brand_page_url ,
|
|
sum(product_sold) product_sold
|
|
from
|
|
(
|
|
select *
|
|
from raena_spider_management.rce_brand
|
|
) rceb
|
|
left join
|
|
(
|
|
select *
|
|
from raena_spider_management.rce_product
|
|
) rcep on rcep.rce_brand_id = rceb.id
|
|
where product_sold is not null and brand_name is not null and brand_name != ''
|
|
group by brand_name, brand_page_url
|
|
order by sum(product_sold) desc;
|
|
|
|
|
|
drop table if exists raena_analytics.avg_sold_product;
|
|
|
|
create table raena_analytics.avg_sold_product
|
|
as
|
|
select distinct brand_name , brand_page_url ,
|
|
avg(ratings) avg_ratings
|
|
from
|
|
(
|
|
select *
|
|
from raena_spider_management.rce_brand
|
|
) rceb
|
|
left join
|
|
(
|
|
select *
|
|
from raena_spider_management.rce_product
|
|
) rcep on rcep.rce_brand_id = rceb.id
|
|
where ratings is not null and brand_name is not null and brand_name != ''
|
|
group by brand_name, brand_page_url
|
|
order by avg(ratings) desc;
|
|
|
|
drop table if exists raena_analytics.total_sold_product;
|
|
|
|
create table raena_analytics.total_sold_product
|
|
as
|
|
select brand_name , brand_page_url ,
|
|
rceb.rce_source_id,
|
|
sum(product_sold_total) product_sold_total,
|
|
sum(product_sold) product_sold,
|
|
avg(ratings) avg_ratings,
|
|
count(distinct rcers.id) number_of_reseller_store_selling,
|
|
CASE
|
|
WHEN LOWER(brand_name) = LOWER(b.name) then true
|
|
ELSE false
|
|
END ss_present_in_Raena,
|
|
b.name raena_brand_name,
|
|
number_of_products_in_raena
|
|
from
|
|
(
|
|
select *
|
|
from raena_spider_management.rce_brand
|
|
) rceb
|
|
left join
|
|
(
|
|
select *
|
|
from raena_spider_management.rce_product
|
|
) rcep on rcep.rce_brand_id = rceb.id and rceb.rce_source_id= rcep.rce_source_id
|
|
left join
|
|
(
|
|
select A.name , count(distinct B.sku) number_of_products_in_raena
|
|
from raena_catalog_management.brand A
|
|
left join raena_catalog_management.product B on A.id = B.brand_id
|
|
group by 1
|
|
) b on LOWER(b.name) = LOWER(rceb.brand_name)
|
|
left join
|
|
(
|
|
select *
|
|
from raena_spider_management.rce_reseller_store
|
|
) rcers on rcers.id = rcep.rce_store_id
|
|
where product_sold_total is not null and brand_name is not null and brand_name != ''
|
|
group by brand_name, brand_page_url,rceb.rce_source_id,CASE
|
|
WHEN LOWER(brand_name) = LOWER(b.name) then true
|
|
ELSE false
|
|
end,b.name,number_of_products_in_raena
|
|
order by sum(product_sold_total) desc;
|
|
|
|
drop table if exists raena_analytics.inventory_snapshot_dashboard_table ;
|
|
|
|
create table raena_analytics.inventory_snapshot_dashboard_table
|
|
as
|
|
SELECT snp_date, sku_code,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 warehouse , min(inventory) inventory_remaining
|
|
FROM raena_analytics.inventory_snapshot
|
|
GROUP BY 1,2,3
|
|
ORDER BY 1,2,3;
|
|
|
|
|
|
" > /home/ec2-user/cronjob/postgresql/sold_product/sold_product.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/sold_product/sold_product.sql
|
|
|
|
|