DROP VIEW IF EXISTS "view_nwm_contractprod_signdate";

CREATE VIEW "view_nwm_contractprod_signdate" AS

SELECT
MAX(Contract.id) AS id,

YEAR(IF(YEAR(Contract.signdate) < 1900, Contract.creadate, Contract.signdate)) AS period_year,
MONTH(IF(YEAR(Contract.signdate) < 1900, Contract.creadate, Contract.signdate)) 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 contract_branch,
COUNT(Contract.id) AS contract_count,

IF(CR.id IS NULL, CRDef.shortname, CR.shortname) AS contract_currency,

\{DatabaseGetSchemaFieldList('nwm_view_contractprod_signdate_fields')\}

SUM(IF(PriodData.contractamount IS NULL, Contract.coastnet, PriodData.contractamount)) AS contract_firstpremiumnet,
SUM(PriodData.contractamount) as constract_firstperiod_contractamount,
SUM(PriodData.contractamount2) as constract_firstperiod_contractamount2,
SUM(PriodData.contractamount3) as constract_firstperiod_contractamount3,
SUM(PriodData.contractamount4) as constract_firstperiod_contractamount4

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

LEFT JOIN (
	SELECT 
		Contr.id as ContractID,
		MIN(PriodData.id) as PriodDataID
	FROM
		nwm_products As Contr INNER JOIN nwm_contractpaymodeperioddata AS PriodData
		ON Contr.id = PriodData.contractid
	GROUP BY Contr.id
) AS Items ON Contract.id = Items.ContractID
LEFT JOIN nwm_contractpaymodeperioddata AS PriodData
ON Items.PriodDataID = PriodData.id

GROUP BY YEAR(IF(YEAR(Contract.signdate) < 1900, Contract.creadate, Contract.signdate)), MONTH(IF(YEAR(Contract.signdate) < 1900, Contract.creadate, Contract.signdate)), SellPartner.id, ComInContractType.descr, IF(CR.id IS NULL, CRDef.shortname, CR.shortname)
