DROP VIEW IF EXISTS "view_nwm_provision_accounts";

CREATE VIEW "view_nwm_provision_accounts" AS

SELECT
MAX(ProvAccount.id) AS id,
YEAR(ProvAccount.valdate) AS period_year,
MONTH(ProvAccount.valdate) AS period_month,
ProvAccountType.descr AS provaccount_name,
IF(CR.id IS NULL, IF(CRSP.id IS NULL, CRDef.shortname, CRSP.shortname), CR.shortname) AS provaccount_currency,
TRIM(CONCAT(SellPartner.companyname, ' ', SellPartner.name, ' ', SellPartner.surname)) AS selling_partner_name,
SellPartner.id AS selling_partner_id,
SellPartner.agencyid AS selling_partner_agencyid,
SUM(ProvAccount.amount) AS provaccount_tr_sum,
SUM(IF(ProvAccount.amount > 0, ProvAccount.amount, 0)) AS provaccount_tr_sum_plus,
SUM(IF(ProvAccount.amount < 0, ProvAccount.amount, 0)) AS provaccount_tr_sum_minus,

(SELECT SUM(AccBal.amount) FROM nwm_accountrecords AS AccBal WHERE AccBal.adressid = SellPartner.id AND AccBal.accounttypeid = ProvAccountType.id 
  AND (IF(IFNULL(AccBal.currencyid, 0) < 1, IF(CRSP.id IS NULL, CRDef.id, CRSP.id), AccBal.currencyid) = IF(CR.id IS NULL, IF(CRSP.id IS NULL, CRDef.id, CRSP.id), CR.id))
	
  AND ((YEAR(AccBal.valdate) = YEAR(ProvAccount.valdate) AND MONTH(AccBal.valdate) <= MONTH(ProvAccount.valdate))
	  OR (YEAR(AccBal.valdate) < YEAR(ProvAccount.valdate)))
) AS provaccount_balance

FROM
nwm_accountrecords AS ProvAccount
LEFT JOIN nwm_accounttypes AS ProvAccountType ON ProvAccountType.id = ProvAccount.accounttypeid
LEFT JOIN adresses AS SellPartner ON SellPartner.id = ProvAccount.adressid
LEFT JOIN curr_currencies AS CR ON CR.id = ProvAccount.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(ProvAccount.valdate), MONTH(ProvAccount.valdate), SellPartner.id, ProvAccountType.descr, IF(CR.id IS NULL, IF(CRSP.id IS NULL, CRDef.shortname, CRSP.shortname), CR.shortname)