DROP VIEW IF EXISTS "view_nwm_commissions_in";

CREATE VIEW "view_nwm_commissions_in" AS
SELECT
MAX(ComIn.id) AS id,
YEAR(ComIn.valdate) AS period_year,
MONTH(ComIn.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,
ComInContractType.descr AS comin_branch,
IF(CR.id IS NULL, CRDef.shortname, CR.shortname) AS comin_currency,

SUM(ComIn.amountincome) AS comincalc_sum,
SUM(ComIn.amountincome) AS comin_sum,

SUM(IF(ComIn.amountincome > 0, ComIn.amountincome, 0)) AS comincalc_prov,
SUM(IF(ComIn.amountincome < 0, ComIn.amountincome, 0)) AS comincalc_storno,

SUM(IF(ComIn.amountincome > 0, ComIn.amountincome, 0)) AS comin_prov,
SUM(IF(ComIn.amountincome < 0, ComIn.amountincome, 0)) AS comin_storno,

COUNT(DISTINCT ComInContract.id) AS comin_contractcount,

COUNT(DISTINCT IF(ComIn.amountincome > 0, ComInContract.id, NULL)) AS comincalc_contractcount_prov,
COUNT(DISTINCT IF(ComIn.amountincome < 0, ComInContract.id, NULL)) AS comincalc_contractcount_storno,

COUNT(DISTINCT IF(ComIn.amountincome > 0, ComInContract.id, NULL)) AS comin_contractcount_prov,
COUNT(DISTINCT IF(ComIn.amountincome < 0, ComInContract.id, NULL)) AS comin_contractcount_storno

FROM
nwmgc_commissions AS ComIn
LEFT JOIN nwm_products AS ComInContract ON ComInContract.id = ComIn.contractid
LEFT JOIN nwm_producttypes AS ComInContractType ON ComInContractType.id = ComInContract.prtypeid
LEFT JOIN adresses AS SellPartner ON SellPartner.id = ComInContract.ownerid
LEFT JOIN curr_currencies AS CR ON CR.id = ComIn.currencyid
LEFT JOIN curr_currencies AS CRDef ON CRDef.shortname = 'CHF'

GROUP BY YEAR(ComIn.valdate), MONTH(ComIn.valdate), SellPartner.id, ComInContractType.descr, IF(CR.id IS NULL, CRDef.shortname, CR.shortname);
