report_form_order

CREATE view report_form_order as
select
creator_seller.creator_name as 'BDS Creator Seller',
creator_buyer.creator_name as 'BDS Creator Buyer',
bds_seller.sales_name as 'BDS Update Seller',
bds_buyer.sales_name as 'BDS Update Buyer',
ifnull(bds_seller.sales_name, creator_seller.creator_name) as 'BDS Seller',
ifnull(bds_buyer.sales_name, creator_buyer.creator_name) as 'BDS Buyer',
ifnull(bds_seller.region_name, creator_seller.region_name) as 'Wilayah Seller',
ifnull(bds_buyer.region_name, creator_buyer.region_name) as 'Wilayah Buyer',
ifnull(bds_seller.area_locality, creator_seller.area_locality) as 'Area Seller',
ifnull(bds_buyer.area_locality, creator_buyer.area_locality) as 'Area Buyer',
DATE_FORMAT(FROM_UNIXTIME(co.placed) + interval 7 hour, '%Y/%m/%d') as 'Date Order',
DATE_FORMAT(FROM_UNIXTIME(co.completed) + interval 7 hour, '%Y/%m/%d') as 'Date Completed',
co.order_number as 'No Order',
csfd.name as 'Store',
csfd.code as 'Store Code',
ufd_customer.name as 'Customer',
co.state as 'Order State',
shipment.state as 'Delivery State',
product.title as 'Title',
coi.unit_price__number as 'Unit Price',
coi.quantity as 'QTY Order',
(coi.unit_price__number * coi.quantity) as 'Rp Order',
product.type as 'Type',
product.brand as 'Brand',
product.category as 'Category',
coi.fee__number as 'Fee GADA',
if((FROM_UNIXTIME(co.placed) + interval 7 hour) < '2019-10-8', coi.fee__number, 0.02 * coi.unit_price__number * coi.quantity) as 'Gross Fee',
MONTH(FROM_UNIXTIME(co.placed) + interval 7 hour) as 'Month Order',
MONTH(FROM_UNIXTIME(co.completed) + interval 7 hour) as 'Month Delivery',
HOUR(FROM_UNIXTIME(co.placed) + interval 7 hour) as 'Transaction Time',
payment.pstate as 'Status Payment',
payment.pgateway as 'Metode Pembayaran',
csfd.uid as 'UID Seller',
co.uid as 'UID Buyer',
product.UOM as 'UOM',
if(upns.entity_id is not null, 'Private Network', 'Wholesaler') as 'Status Transaksi',
product.variation_id as 'Variation ID',
product.product_id as 'Product ID'
from commerce_order co
left join commerce_order__order_items cooi
on co.order_number = cooi.entity_id
inner join commerce_order_item coi
on cooi.order_items_target_id = coi.order_item_id
left join users_field_data ufd_customer
on co.uid = ufd_customer.uid
left join (select uc.entity_id, ufd_creator.name as 'creator_name', sr.name as 'region_name', sa.name as 'area_locality'
    from user__creator uc
    left join users_field_data ufd_creator
    on uc.creator_target_id = ufd_creator.uid
    left join sales_region__supervisor_id srsi
    on ufd_creator.uid = srsi.supervisor_id_target_id
    left join sales_region sr
    on srsi.entity_id = sr.id
    left join sales_area__sales_region sasr
    on sr.id = sasr.sales_region_target_id
    left join sales_area sa
    on sasr.entity_id = sa.id) creator_buyer
on co.uid = creator_buyer.entity_id
left join (select us.entity_id, ufd_sales.name as 'sales_name', sr.name as 'region_name', sa.name as 'area_locality'
    from user__sales us
    left join users_field_data ufd_sales
    on us.sales_target_id = ufd_sales.uid
    left join sales_region__supervisor_id srsi
    on ufd_sales.uid = srsi.supervisor_id_target_id
    left join sales_region sr
    on srsi.entity_id = sr.id
    left join sales_area__sales_region sasr
    on sr.id = sasr.sales_region_target_id
    left join sales_area sa
    on sasr.entity_id = sa.id) bds_buyer
on co.uid = bds_buyer.entity_id
left join (select
        co.order_id,
        group_concat(DISTINCT cpayment.state) as pstate,
        group_concat(DISTINCT if(cpayment.type = 'payment_manual', 'Bank Transfer', if(cpayment.type = 'payment_default', 'Virtual Account', ''))) as pgateway
    from commerce_order co
    left join commerce_payment cpayment
    on co.order_id = cpayment.order_id
    group by co.order_id) payment
on co.order_id = payment.order_id
left join (select
        cpvfd.variation_id,
        cpvfd.product_id,
        cpfd.`type`,
        c.name as brand,
        cpvfd.title,
        sub.category,
        cpavfd.name as 'UOM'
    from commerce_product_variation_field_data cpvfd
    left join commerce_product_field_data cpfd
    on cpvfd.product_id = cpfd.product_id
    left join taxonomy_term_field_data c
    on cpfd.brand = c.tid and c.vid = 'brand'
    left join commerce_product_variation__attribute_uom cpvuom
    on cpvfd.variation_id = cpvuom.entity_id
    left join commerce_product_attribute_value_field_data cpavfd
    on cpvuom.attribute_uom_target_id = cpavfd.attribute_value_id
    left join (select
            cpc.entity_id,
            group_concat(DISTINCT ttfdcategory.name) as 'category'
        from commerce_product__categories cpc
        left join taxonomy_term_field_data ttfdcategory
        on ttfdcategory.tid = cpc.categories_target_id and ttfdcategory.vid = 'product_category'
        group by cpc.entity_id) sub
    on cpvfd.product_id = sub.entity_id
    ) product
on coi.purchased_entity = product.variation_id
left join (select
        co.order_id,
        group_concat(DISTINCT cs.state) as state
    from commerce_order co
    left join commerce_shipment cs
    on co.order_id = cs.order_id
    group by co.order_id) shipment
on co.order_id = shipment.order_id
left join commerce_store_field_data as csfd
on co.store_id = csfd.store_id
left join (select uc.entity_id, ufd_creator.name as 'creator_name', sr.name as 'region_name', sa.name as 'area_locality'
    from user__creator uc
    left join users_field_data ufd_creator
    on uc.creator_target_id = ufd_creator.uid
    left join sales_region__supervisor_id srsi
    on ufd_creator.uid = srsi.supervisor_id_target_id
    left join sales_region sr
    on srsi.entity_id = sr.id
    left join sales_area__sales_region sasr
    on sr.id = sasr.sales_region_target_id
    left join sales_area sa
    on sasr.entity_id = sa.id) creator_seller
on csfd.uid = creator_seller.entity_id
left join (select us.entity_id, ufd_sales.name as 'sales_name', sr.name as 'region_name', sa.name as 'area_locality'
    from user__sales us
    left join users_field_data ufd_sales
    on us.sales_target_id = ufd_sales.uid
    left join sales_region__supervisor_id srsi
    on ufd_sales.uid = srsi.supervisor_id_target_id
    left join sales_region sr
    on srsi.entity_id = sr.id
    left join sales_area__sales_region sasr
    on sr.id = sasr.sales_region_target_id
    left join sales_area sa
    on sasr.entity_id = sa.id) bds_seller
on csfd.uid = bds_seller.entity_id
left join user__private_network_stores upns
on co.uid = upns.entity_id and co.store_id = upns.private_network_stores_target_id
left join (select u_roles.entity_id from user__roles u_roles
    where u_roles.roles_target_id in('administrator', 'ops', 'bd', 'customer_service', 'tester')
) x_roles
on co.uid = x_roles.entity_id
where x_roles.entity_id is null
and (creator_buyer.creator_name is not null
    or bds_buyer.sales_name is not null
    or creator_seller.creator_name is not null
    or bds_seller.sales_name is not null)
and co.state not in ('draft');