report_form_id

CREATE VIEW report_form_id as
select
ifnull(ufd.uid, '') as 'UID',
ifnull(ufd.name, '') as 'Customer',
ifnull(csfd.name, '') as 'Store',
ifnull(csfd.code, '') as 'Kode Toko Apps',
ifnull(pcontact.handphone_value, '') as 'HP',
ifnull(pcontact.phone_value, '') as 'Telepon',
ifnull(creator.creator_name, '') as 'BDS Creator',
ifnull(sapi.sales_name, '') as 'BDS Update',
ifnull(ifnull(sapi.sales_name, creator.creator_name), '') as 'BDS',
ifnull(ifnull(sapi.region_name, creator.region_name), '') as 'Wilayah',
ifnull(ifnull(sapi.area_locality, creator.area_locality), '') as 'Area',
ifnull(ur.roles_target_id, 'buyer') as 'Roles',
ifnull(paddress.address_administrative_area, '') as 'Provinsi Profile',
ifnull(paddress.address_locality, '') as 'Kab/Kota Profile',
ifnull(csfd.address__administrative_area, '') as 'Provinsi Store',
ifnull(csfd.address__locality, '') as 'Kab/Kota Store',
ifnull(cil.address__administrative_area, '') as 'Provinsi Gudang',
ifnull(cil.address__locality, '') as 'Kab/Kota Gudang',
ifnull(csfd.minimum_order_amount__number, '') as 'Minimal Kirim',
ifnull(csmwd.max_wheel_distance_number, '') as 'Maksimal Jarak Kirim',
ifnull(csmwd.max_wheel_distance_unit, '') as 'Unit Jarak',
ifnull(ufd.mail, '') as 'Email User', ifnull(csfd.mail, '') as 'Email Toko',
ifnull(pbank.bank_name, '') as 'Bank Name',
ifnull(pbank.account_number, '') as 'Account No',
ifnull(pbank.account_name, '') as 'Registered Name',
ifnull(pplace_new.place_of_birth_value, '') as 'Tempat Lahir',
ifnull(DATE_FORMAT(STR_TO_DATE(pdob_new.date_of_birth_value, '%Y-%m-%d'), '%Y/%m/%d'), '') as 'Tanggal Lahir',
ifnull(preligion_new.religion_value, '') as 'Agama',
ifnull(paddress.address_address_line1, '') as 'Alamat Customer',
ifnull(csfd.address__address_line1, '') as 'Alamat Store',
ifnull(pcompany.full_name_value, '') as 'Company Name',
ifnull(pcompany.company_type_value, '') as 'Company Type',
ifnull(pcompany.tax_status_value, '') as 'Tax Status',
MONTH(from_unixtime(LEAST(
    COALESCE(seller_table.seller_completed_min,buyer_table.buyer_completed_min,ufd.created),
    COALESCE(buyer_table.buyer_completed_min,ufd.created,seller_table.seller_completed_min),
    COALESCE(ufd.created,seller_table.seller_completed_min,buyer_table.buyer_completed_min))) + interval 7 hour) as 'Joint Month',
ifnull(paddress.location_lon, '') as 'Longitude Profile',
ifnull(paddress.location_lat, '') as 'Latitude Profile',
ifnull(cil.location__lon, '') as 'Longitude Gudang',
ifnull(cil.location__lat, '') as 'Latitude Gudang',
ifnull(pktp.id_number_value, '') as 'ID KTP',
ifnull(pnpwp.id_number_value, '') as 'NPWP',
ifnull(ptransport.name, '') as 'Transportation Type',
ifnull(ud.value, '') as 'Wallet Balance',
if (seller_table.seller_id is not null, 'Pernah Jual', 'Belum Pernah Jual') as 'Status Jual',
if (buyer_table.buyer_id is not null, 'Pernah Beli', 'Belum Pernah Beli') as 'Status Beli',
if (buyer_table.buyer_id is not null || seller_table.seller_id is not null, 'Pernah Transaksi', 'Belum Pernah Transaksi') as 'Status Transaksi',
ifnull(DATE_FORMAT(from_unixtime(seller_table.seller_completed) + interval 7 hour, '%Y/%m/%d'), '') as 'Seller Last Date Order',
ifnull(DATE_FORMAT(from_unixtime(buyer_table.buyer_completed) + interval 7 hour, '%Y/%m/%d'), '') as 'Buyer Last Date Order',
ifnull(if ((seller_table.seller_completed >= buyer_table.buyer_completed || buyer_table.buyer_completed is null), DATE_FORMAT(from_unixtime(seller_table.seller_completed) + interval 7 hour, '%Y/%m/%d'), DATE_FORMAT(from_unixtime(buyer_table.buyer_completed) + interval 7 hour, '%Y/%m/%d')), '') as 'Last Date Transaksi',
if (ufd.login > 0, DATE_FORMAT(FROM_UNIXTIME(ufd.login) + interval 7 hour, '%Y/%m/%d'), '') as 'Last Login',
ifnull(DATE_FORMAT(from_unixtime(LEAST(ifnull(seller_table.seller_completed_min,buyer_table.buyer_completed_min),ifnull(buyer_table.buyer_completed_min,seller_table.seller_completed_min))) + interval 7 hour, '%Y/%m/%d'), '') as 'Tanggal Transaksi Pertama',
DATE_FORMAT(from_unixtime(LEAST(
    COALESCE(seller_table.seller_completed_min,buyer_table.buyer_completed_min,ufd.created),
    COALESCE(buyer_table.buyer_completed_min,ufd.created,seller_table.seller_completed_min),
    COALESCE(ufd.created,seller_table.seller_completed_min,buyer_table.buyer_completed_min))) + interval 7 hour, '%Y/%m/%d') as 'Member From',
ifnull(new_pn.seller_private_network, '') as 'Seller Private Network'
from
users_field_data ufd
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
on ufd.uid = creator.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) sapi
on ufd.uid = sapi.entity_id
left join commerce_store_field_data csfd
on ufd.uid = csfd.uid
left join commerce_store__commerce_inventory_locations cscil
on csfd.store_id = cscil.entity_id
left join commerce_inventory_location cil
on cscil.commerce_inventory_locations_target_id = cil.id
left join (select csfd_seller.uid as 'seller_id', csfd_seller.store_id as 'seller_store_id', co_seller.state as 'status', max(co_seller.completed) as 'seller_completed', min(co_seller.completed) as 'seller_completed_min'
    from
    commerce_store_field_data csfd_seller
    left join commerce_order co_seller
    on csfd_seller.store_id = co_seller.store_id
    where co_seller.state = 'completed'
    group by csfd_seller.uid) seller_table
on ufd.uid = seller_table.seller_id
left join (select co_buyer.uid as 'buyer_id', csfd_buyer.store_id as 'buyer_store_id', co_buyer.state as 'status', max(co_buyer.completed) as 'buyer_completed', min(co_buyer.completed) as 'buyer_completed_min'
    from
    commerce_store_field_data csfd_buyer
    left join commerce_order co_buyer
    on csfd_buyer.store_id = co_buyer.store_id
    where co_buyer.state = 'completed'
    group by co_buyer.uid) buyer_table
on ufd.uid = buyer_table.buyer_id
left join user__roles ur
on ufd.uid = ur.entity_id
left join commerce_store__max_wheel_distance csmwd
on csfd.store_id = csmwd.entity_id
left join (select p.uid, pa.address_administrative_area, pa.address_locality, pa.address_address_line1,
    pl.location_value, pl.location_lat, pl.location_lon
    from
    profile p
    left join profile__address pa
    on p.profile_id = pa.entity_id
    left join profile__location pl
    on p.profile_id = pl.entity_id where p.status = 1 AND p.is_default = 1 and p.type = 'customer') paddress
on ufd.uid = paddress.uid
left join (select
        p.uid,
        group_concat(b.name SEPARATOR '|') as bank_name,
        group_concat(pba.bank_account_account_number SEPARATOR '|') as account_number,
        group_concat(pba.bank_account_account_name SEPARATOR '|') as account_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 ufd.uid = pbank.uid
left join (select p.uid, pfn.full_name_value, pct.company_type_value, pts.tax_status_value
    from profile p
    left join profile__full_name pfn
    on p.profile_id = pfn.entity_id
    left join profile__company_type pct
    on p.profile_id = pct.entity_id
    left join profile__tax_status pts
    on p.profile_id = pts.entity_id
    where p.status = 1 AND p.is_default = 1 and p.type = 'company') pcompany
on ufd.uid = pcompany.uid
left join (select p.uid, pin.id_number_value
    from profile p
    left join profile__id_type pit
    on p.profile_id = pit.entity_id
    left join profile__id_number pin
    on p.profile_id = pin.entity_id
    where p.status = 1 and pit.id_type_value = 'ktp' and pin.id_number_value is not null
    limit 1) pktp
on ufd.uid = pktp.uid
left join (select p.uid, pin.id_number_value
    from profile p
    left join profile__id_type pit
    on p.profile_id = pit.entity_id
    left join profile__id_number pin
    on p.profile_id = pin.entity_id
    where p.status = 1 and pit.id_type_value = 'npwp' and pin.id_number_value is not null
    limit 1) pnpwp
on ufd.uid = pnpwp.uid
left join (select p.uid, ph.handphone_value, pp.phone_value
    from profile p
    left join profile__handphone ph
    on p.profile_id = ph.entity_id
    left join profile__phone pp
    on p.profile_id = pp.entity_id
    where p.status = 1 AND p.is_default = 1 and p.type = 'customer'
    order by p.uid) pcontact
on ufd.uid = pcontact.uid
left join (select p.uid, pplace.place_of_birth_value
    from profile p
    left join profile__place_of_birth pplace
    on p.profile_id = pplace.entity_id
    where p.status = 1 AND p.is_default = 1 and p.type = 'customer') pplace_new
on ufd.uid = pplace_new.uid
left join (select p.uid, preligion.religion_value
    from profile p
    left join profile__religion preligion
    on p.profile_id = preligion.entity_id
    where p.type = 'customer' AND p.status = 1 AND p.is_default = 1) preligion_new
on ufd.uid = preligion_new.uid
left join (select p.uid, pdob.date_of_birth_value
    from profile p
    left join profile__date_of_birth pdob
    on p.profile_id = pdob.entity_id
    where p.type = 'customer' AND p.status = 1 AND p.is_default = 1) pdob_new
on ufd.uid = pdob_new.uid
left join (select group_concat(distinct csfd.name) as 'seller_private_network', upns.entity_id as 'private_network_seller_id'
    from commerce_store_field_data csfd
    left join user__private_network_stores upns
    on csfd.store_id = upns.private_network_stores_target_id
    group by upns.entity_id) new_pn
on ufd.uid = new_pn.private_network_seller_id
left join (select p.uid, group_concat(distinct ttfd.name) as name
    from profile p
    left join profile__transportation_type ptt
    on p.profile_id = ptt.entity_id
    left join taxonomy_term_field_data ttfd
    on ptt.transportation_type_target_id = ttfd.tid
    where p.status = 1 and ptt.bundle = 'customer'
    group by p.uid) ptransport
on ufd.uid = ptransport.uid
left join users_data ud
on ufd.uid = ud.uid and ud.name = 'wallet_balance'
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 ufd.uid = x_roles.entity_id
where x_roles.entity_id is null
and ufd.uid != 0
and (creator.creator_name is not null
    or sapi.sales_name is not null);