DROP VIEW IF EXISTS "view_nwm_cancellation_quote";

CREATE VIEW "view_nwm_cancellation_quote" AS
SELECT 
(YEAR(COM.valdate) * 100000000000 + COM.adressid) AS id,
COM.adressid as adressid,
(SELECT TRIM(CONCAT(companyname, ' ', "NAME", ' ', surname)) FROM adresses WHERE id = COM.adressid) AS selling_partner_name,
YEAR(COM.valdate) AS period_year,

SUM(COM.amount) AS period_sum,
SUM(IF(COM.amount > 0, COM.amount, 0)) AS period_sum_prov,
SUM(IF(COM.amount < 0, COM.amount, 0)) AS period_sum_storno,
SUM(IF(COM.amount < 0, COM.amount, 0)) * 100 / SUM(IF(COM.amount > 0, COM.amount, 0)) AS period_sum_storno_quote,

(SELECT SUM(COM2.amount) FROM nwm_accountrecords AS COM2 WHERE COM2.adressid = COM.adressid AND COM2.productid > 0 AND COM2.amount > 0 AND YEAR(COM2.valdate) <= YEAR(COM.valdate)) AS total_sum_prov,
(SELECT SUM(COM2.amount) FROM nwm_accountrecords AS COM2 WHERE COM2.adressid = COM.adressid AND COM2.productid > 0 AND COM2.amount < 0 AND YEAR(COM2.valdate) <= YEAR(COM.valdate)) AS total_sum_storno,

SUM(IF(COM.amount < 0, COM.amount, 0)) * 100 / (SELECT SUM(COM2.amount) FROM nwm_accountrecords AS COM2 WHERE COM2.adressid = COM.adressid AND COM2.productid > 0 AND COM2.amount > 0 AND YEAR(COM2.valdate) <= YEAR(COM.valdate)) AS period_total_sum_storno_quote,

(SELECT SUM(COM2.amount) FROM nwm_accountrecords AS COM2 WHERE COM2.adressid = COM.adressid AND COM2.productid > 0 AND COM2.amount < 0 AND YEAR(COM2.valdate) <= YEAR(COM.valdate)) * 100 / (SELECT SUM(COM2.amount) FROM nwm_accountrecords AS COM2 WHERE COM2.adressid = COM.adressid AND COM2.productid > 0 AND COM2.amount > 0 AND YEAR(COM2.valdate) <= YEAR(COM.valdate)) AS total_sum_storno_quote

FROM nwm_accountrecords AS COM
WHERE COM.productid > 0
GROUP BY COM.adressid, YEAR(COM.valdate)
ORDER BY selling_partner_name, period_year;
