DROP VIEW IF EXISTS "view_nwm_commissions_payout";

CREATE VIEW "view_nwm_commissions_payout" AS

SELECT
MAX(ComPayOut.id) AS id,
YEAR(ComPayOut.valdate) AS period_year,
MONTH(ComPayOut.valdate) AS period_month,
TRIM(CONCAT(SellPartner.companyname, ' ', SellPartner.name, ' ', SellPartner.surname)) AS selling_partner_name,
SellPartner.id AS selling_partner_id,
SellPartner.agencyid AS selling_partner_agencyid,
IF(CR.id IS NULL, IF(CRSP.id IS NULL, CRDef.shortname, CRSP.shortname), CR.shortname) AS comout_currency,
SUM(ComPayOut.amount) AS compayout_sum

FROM
nwm_payoutrecords AS ComPayOut
LEFT JOIN adresses AS SellPartner ON SellPartner.id = ComPayOut.addressid
LEFT JOIN curr_currencies AS CR ON CR.id = ComPayOut.currencyid
LEFT JOIN curr_currencies AS CRSP ON CRSP.id = SellPartner.currencyid
LEFT JOIN curr_currencies AS CRDef ON CRDef.shortname = 'CHF'


GROUP BY YEAR(ComPayOut.valdate), MONTH(ComPayOut.valdate), SellPartner.id, IF(CR.id IS NULL, IF(CRSP.id IS NULL, CRDef.shortname, CRSP.shortname), CR.shortname)