Financial Reporting for LXHub

FinancialReports.sql

CREATE  OR REPLACE VIEW `vw_ExpReporting` AS
select  distinctrow tik.activity_id as ActivityID
, pm1.meta_value as RootActivityID
 , p.post_title as ActivityTitle
, tik.sale_ID as SaleID
, tik.sale_Date as SaleDate
, tik.ID as TicketID
, tik.activity_timestamp as ActivityTimestamp
, tik.destination as Destination
, tik.act_ticket_amt as TicketAmount
, tik.act_curr as ActualCurrency
, tik.sale_type as SaleType
, tik.ticket_status as TicketStatus
, tik.ticket_status_label as TicketStatuslabel
, ap.amount as NetSupplierDue
, usr.user_login as GCOAgent
, pm.meta_value as Language
FROM arez_tickets tik
LEFT JOIN arez_accounts_payable ap on tik.Sale_ID = ap.sale_ID AND ap.type = 'net_due'
LEFT JOIN arez_users usr on usr.ID = tik.reseller1_userID
LEFT JOIN arez_postmeta pm ON pm.post_id = tik.activity_ID AND pm.meta_key ='i18n'
LEFT JOIN arez_postmeta pm1 ON pm1.post_id = tik.activity_ID AND pm1.meta_key ='activityID'
LEFT JOIN arez_posts p on p.ID = pm1.meta_value
ORDER BY  tik.ID ASC ;

CREATE OR REPLACE VIEW vw_transactionmapping
AS
( SELECT
trx.`sale_ID`,
tkt.ID as TicketID,
trx.`locationType`,
trx.`pos_ID`,
trx.`pos_name`,
trx.`pos_misc_3` as POSa_Misc3_CompanyCode ,
trx.`pos_misc_2` as Agency_Misc2_MangementUnitCode,
trx.`pos_misc_1` as Agency_Misc_1_AffiliateID

FROM arez_transactions trx
JOIN arez_tickets tkt on trx.sale_ID = tkt.Sale_ID
where trx.locationType = 'POS' )

UNION

(
SELECT
trx.`sale_ID`,
tkt.ID as TicketID,
trx.`locationType`,
trx.`pos_ID`,
trx.`pos_name`,
trx.`pos_misc_3` as POSa_Misc3_CompanyCode ,
tkt.`reseller2_misc_2` as Agency_Misc2_MangementUnitCode,
tkt.`reseller2_misc_1` as Agency_Misc_1_AffiliateID

FROM arez_transactions trx
JOIN arez_tickets tkt on trx.sale_ID = tkt.Sale_ID
where trx.locationType = 'WB' )

ORDER BY sale_ID ;


CREATE OR REPLACE VIEW vw_ap_vendornetdue
AS
( SELECT
trx.`sale_ID`,
tkt.ID as TicketID,
trx.`locationType`,
trx.`pos_ID`,
trx.`pos_name`,
tkt.`provider_misc_3` as Supplier_misc3_CompanyCode ,
trx.`pos_misc_2` as Agency_Misc2_MangementUnitCode,
tkt.`provider_misc_1` as Supplier_misc1_vendorID

FROM arez_transactions trx
JOIN arez_tickets tkt on trx.sale_ID = tkt.Sale_ID
where trx.locationType = 'POS' )

UNION

(
SELECT
trx.`sale_ID`,
tkt.ID as TicketID,
trx.`locationType`,
trx.`pos_ID`,
trx.`pos_name`,
tkt.`provider_misc_3` as Supplier_misc3_CompanyCode ,
tkt.`reseller2_misc_2` as Agency_Misc2_MangementUnitCode,
tkt.`provider_misc_1` as Supplier_misc1_vendorID

FROM arez_transactions trx
JOIN arez_tickets tkt on trx.sale_ID = tkt.Sale_ID
where trx.locationType = 'WB' )

ORDER BY sale_ID ;

CREATE OR REPLACE VIEW vw_ap_affiliate_r2
AS
( SELECT
trx.`sale_ID`,
tkt.ID as TicketID,
trx.`locationType`,
trx.`pos_ID`,
trx.`pos_name`,
trx.`pos_misc_3` as POSa_Misc3_CompanyCode ,
trx.`pos_misc_2` as Agency_Misc2_MangementUnitCode,
trx.`pos_misc_1` as Agency_Misc_1_AffiliateID

FROM arez_transactions trx
JOIN arez_tickets tkt on trx.sale_ID = tkt.Sale_ID
where trx.locationType = 'POS' )

UNION

(
SELECT
trx.`sale_ID`,
tkt.ID as TicketID,
trx.`locationType`,
trx.`pos_ID`,
trx.`pos_name`,
trx.`pos_misc_3` as POSa_Misc3_CompanyCode ,
tkt.`reseller2_misc_2` as Agency_Misc2_MangementUnitCode,
tkt.`reseller2_misc_1` as Agency_Misc_1_AffiliateID

FROM arez_transactions trx
JOIN arez_tickets tkt on trx.sale_ID = tkt.Sale_ID
where trx.locationType = 'WB' )

ORDER BY sale_ID ;