report_settlement_seller

CREATE VIEW report_settlement_seller as
select
    seller_sales.area_name as 'Area',
    seller_sales.sales_name as 'BDS',
    co.order_id as 'Order No',
    FROM_UNIXTIME(co.placed) + interval 7 hour as 'Order Date',
    csfd.name as 'Store',
    ufd_buyer.name as 'Customer',
    item_default.total as 'Total Order',
    ifnull(-1 * item_return.total, 0) as 'Total Refund',
    (0 * co.total_price__number) as 'Biaya Pengiriman',
    co.total_price__number as 'Total',
    co.fee__number as 'Fee GADA',
    co.total_price__number - co.fee__number + ifnull(item_return.total,0) as 'Total Bersih',
    co.state as 'Status',
    pbank.bank_name as 'Bank Name Seller',
    pbank.account_no as 'Account No Seller',
    pbank.account_name as 'Registered Name Seller'
from commerce_order co
left join commerce_store_field_data csfd on co.store_id = csfd.store_id
left join users_field_data ufd_buyer on co.uid = ufd_buyer.uid
left join (select coi.order_id, sum(coi.total_price__number) as total
    from commerce_order__order_items cooi
    inner join commerce_order_item coi
    on cooi.order_items_target_id = coi.order_item_id
    where coi.type = 'default'
    group by coi.order_id
) item_default
on co.order_number = item_default.order_id
left join (select coi.order_id, sum(coi.total_price__number) as total
    from commerce_order__order_items cooi
    inner join commerce_order_item coi
    on cooi.order_items_target_id = coi.order_item_id
    where coi.type = 'return'
    group by coi.order_id
) item_return
on co.order_number = item_return.order_id
left join (select us.entity_id, ufd_sales.name as 'sales_name', sr.name as 'region_name', sa.name as 'area_name'
    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) seller_sales
on csfd.uid = seller_sales.entity_id
left join (
    select
        p.uid,
        group_concat(pba.bank_account_account_number) as account_no,
        group_concat(pba.bank_account_account_name) as account_name,
        group_concat(b.name) as bank_name
    from profile p
    left join profile__bank_account pba
    on p.profile_id = pba.entity_id
    left join bank b
    on pba.bank_account_bank_id = b.id
    where p.status = 1 and p.type = 'finance'
    group by p.uid) pbank
on csfd.uid = pbank.uid
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 csfd.uid = x_roles.entity_id
where co.state in('completed','validation','fulfillment')
and x_roles.entity_id is null
and seller_sales.entity_id is not null