DROP VIEW IF EXISTS "view_nwm_commissions_out";

CREATE VIEW "view_nwm_commissions_out" AS
SELECT
MAX(ComOut.id) AS id,
YEAR(ComOut.valdate) AS period_year,
MONTH(ComOut.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,
ComOutContractType.descr AS comout_branch,
IF(CR.id IS NULL, IF(CRSP.id IS NULL, CRDef.shortname, CRSP.shortname), CR.shortname) AS comout_currency,
SUM(ComOut.amount) AS comout_sum,
SUM(IF(ComOut.amount > 0, ComOut.amount, 0)) AS comout_prov,
SUM(IF(ComOut.amount < 0, ComOut.amount, 0)) AS comout_storno,

COUNT(DISTINCT ComOutContract.id) AS comout_contractcount,
COUNT(DISTINCT IF(ComOut.amount > 0, ComOutContract.id, NULL)) AS comout_contractcount_prov,
COUNT(DISTINCT IF(ComOut.amount < 0, ComOutContract.id, NULL)) AS comout_contractcount_storno

FROM
nwm_accountrecords AS ComOut
LEFT JOIN nwm_products AS ComOutContract ON ComOutContract.id = ComOut.productid
LEFT JOIN nwm_producttypes AS ComOutContractType ON ComOutContractType.id = ComOutContract.prtypeid
LEFT JOIN adresses AS SellPartner ON SellPartner.id = ComOut.adressid
LEFT JOIN curr_currencies AS CR ON CR.id = ComOut.currencyid
LEFT JOIN curr_currencies AS CRSP ON CRSP.id = SellPartner.currencyid
LEFT JOIN curr_currencies AS CRDef ON CRDef.shortname = 'CHF'

WHERE ComOut.ispayout = 0

GROUP BY YEAR(ComOut.valdate), MONTH(ComOut.valdate), SellPartner.id, ComOutContractType.descr, IF(CR.id IS NULL, IF(CRSP.id IS NULL, CRDef.shortname, CRSP.shortname), CR.shortname);
