inserisci nella tabella "c_bilancio_bp" una nuova riga per l'anno 2017 con tutti i valori a 0.00

INSERT INTO c_bilancio (idcontatto, annocontabile, idsottoconto, residui_presunti, previsione_precedente, previsione_esercizio, ultimo_consuntivo)
SELECT 'PA_SPEZIA' AS idcontatto
, '2017' AS annocontabile
, b.idsottoconto AS idsottoconto
, '0.00' AS residui_presunti
, b.previsione_esercizio AS previsione_precedente
, '0.00' AS previsione_esercizio
, b.ultimo_consuntivo
FROM c_bilancio AS b
WHERE b.idcontatto='PA_SPEZIA' AND b.annocontabile='2016';

-------------------------------------------------------------------------------
SET @tmp=0.00;
SET @tmp2=0.00;
SET @tmp3=0.00;
DROP TEMPORARY TABLE IF EXISTS _ultimo_consuntivo;

CREATE TEMPORARY TABLE IF NOT EXISTS _ultimo_consuntivo
SELECT sc.idcontatto, sc.id AS idsottoconto, bp.annocontabile, IF(avan_disav.avanzo_disavanzo_esercizio>0,avan_disav.avanzo_disavanzo_esercizio,'0.00') AS avanzo_disavanzo_esercizio, g.type, g.numero AS gruppo_num, g.nome AS gruppo, c.type AS ord_stra, c.numero AS conto_num, c.nome AS conto, sc.numero AS sottoconto_num, sc.nome AS sottoconto, bp.previsione_esercizio
, @tmp:=CAST((SELECT IFNULL(SUM(IF(c_bp_sv.importo>0,c_bp_sv.importo,0)),0) FROM c_bp_sv INNER JOIN c_bp_stornivariazioni AS stovar ON (c_bp_sv.idbp_stornovariazione=stovar.id) WHERE stovar.idcontatto=bp.idcontatto AND stovar.annocontabile=bp.annocontabile AND c_bp_sv.idsottoconto=bp.idsottoconto) AS DECIMAL(15,2)) AS sto_var_piu
, @tmp2:=CAST((SELECT IFNULL(SUM(IF(c_bp_sv.importo<0,c_bp_sv.importo,0)),0) FROM c_bp_sv INNER JOIN c_bp_stornivariazioni AS stovar ON (c_bp_sv.idbp_stornovariazione=stovar.id) WHERE stovar.idcontatto=bp.idcontatto AND stovar.annocontabile=bp.annocontabile AND c_bp_sv.idsottoconto=bp.idsottoconto) AS DECIMAL(15,2)) AS sto_var_meno
, CAST((@tmp3:=(bp.previsione_esercizio+@tmp+@tmp2)) AS DECIMAL(15,2)) AS previsione_definitiva
, @tmp:=CAST((SELECT IFNULL(SUM(sca.importo),0) FROM c_movimenti_scadenze AS sca INNER JOIN c_movimenti AS mov ON (sca.idmovimento=mov.id) WHERE sca.idcontatto=bp.idcontatto AND mov.annocontabile=bp.annocontabile AND mov.annoriferimento=bp.annocontabile AND sca.idsottoconto=bp.idsottoconto AND sca.insussistenza=0 AND sca.idreversale_mandato IS NULL AND sca.idcassa IS NULL AND sca.idcassa_u IS NULL) AS DECIMAL(15,2)) AS da_pagare
, @tmp2:=CAST((SELECT IFNULL(SUM(sca.importo),0) FROM c_movimenti_scadenze AS sca INNER JOIN c_movimenti AS mov ON (sca.idmovimento=mov.id) WHERE sca.idcontatto=bp.idcontatto AND mov.annocontabile=bp.annocontabile AND mov.annoriferimento=bp.annocontabile AND sca.idsottoconto=bp.idsottoconto AND sca.insussistenza=0 AND (sca.idreversale_mandato IS NOT NULL OR sca.idcassa IS NOT NULL OR sca.idcassa_u IS NOT NULL)) AS DECIMAL(15,2)) AS pagato
, @tmp:=CAST((@tmp+@tmp2) AS DECIMAL(15,2)) AS tot_mov
, CAST(IF((@tmp3-@tmp)<0,((@tmp3-@tmp)*-1),0) AS DECIMAL(15,2)) AS diff_piu
, CAST(IF((@tmp3-@tmp)>0,(@tmp3-@tmp),0) AS DECIMAL(15,2)) AS diff_meno
 FROM c_bilancio AS bp LEFT JOIN c_bilancio_bp AS avan_disav ON (bp.idcontatto=avan_disav.idcontatto AND bp.annocontabile=avan_disav.annocontabile)
LEFT JOIN c_pc_sottoconto AS sc ON (bp.idsottoconto=sc.id) LEFT JOIN c_pc_conto AS c ON (sc.idconto=c.id) LEFT JOIN c_pc_gruppo AS g ON (c.idgruppo=g.id)
 WHERE bp.idcontatto='PA_SPEZIA' AND bp.annocontabile='2015' AND g.type='E'
 ORDER BY g.order, c.numero, sc.numero;

UPDATE c_bilancio AS bil
INNER JOIN _ultimo_consuntivo AS uc ON (bil.idsottoconto=uc.idsottoconto AND bil.idcontatto=uc.idcontatto)
SET bil.ultimo_consuntivo=uc.tot_mov
WHERE bil.idcontatto='PA_SPEZIA' AND bil.annocontabile='2017';

-------------------------------------------------------------------------------
SET @tmp=0.00;
SET @tmp2=0.00;
SET @tmp3=0.00;
DROP TEMPORARY TABLE IF EXISTS _ultimo_consuntivo;

CREATE TEMPORARY TABLE IF NOT EXISTS _ultimo_consuntivo
SELECT sc.idcontatto, sc.id AS idsottoconto, bp.annocontabile, IF(avan_disav.avanzo_disavanzo_esercizio<0,avan_disav.avanzo_disavanzo_esercizio,'0.00') AS avanzo_disavanzo_esercizio, g.type, g.numero AS gruppo_num, g.nome AS gruppo, c.type AS ord_stra, c.numero AS conto_num, c.nome AS conto, sc.numero AS sottoconto_num, sc.nome AS sottoconto, bp.previsione_esercizio
, @tmp:=CAST((SELECT IFNULL(SUM(IF(c_bp_sv.importo>0,c_bp_sv.importo,0)),0) FROM c_bp_sv INNER JOIN c_bp_stornivariazioni AS stovar ON (c_bp_sv.idbp_stornovariazione=stovar.id) WHERE stovar.idcontatto=bp.idcontatto AND stovar.annocontabile=bp.annocontabile AND c_bp_sv.idsottoconto=bp.idsottoconto) AS DECIMAL(15,2)) AS sto_var_piu
, @tmp2:=CAST((SELECT IFNULL(SUM(IF(c_bp_sv.importo<0,c_bp_sv.importo,0)),0) FROM c_bp_sv INNER JOIN c_bp_stornivariazioni AS stovar ON (c_bp_sv.idbp_stornovariazione=stovar.id) WHERE stovar.idcontatto=bp.idcontatto AND stovar.annocontabile=bp.annocontabile AND c_bp_sv.idsottoconto=bp.idsottoconto) AS DECIMAL(15,2)) AS sto_var_meno
, CAST((@tmp3:=(bp.previsione_esercizio+@tmp+@tmp2)) AS DECIMAL(15,2)) AS previsione_definitiva
, @tmp:=CAST((SELECT IFNULL(SUM(sca.importo),0) FROM c_movimenti_scadenze AS sca INNER JOIN c_movimenti AS mov ON (sca.idmovimento=mov.id) WHERE sca.idcontatto=bp.idcontatto AND mov.annocontabile=bp.annocontabile AND mov.annoriferimento=bp.annocontabile AND sca.idsottoconto=bp.idsottoconto AND sca.insussistenza=0 AND sca.idreversale_mandato IS NULL AND sca.idcassa IS NULL AND sca.idcassa_u IS NULL) AS DECIMAL(15,2)) AS da_pagare
, @tmp2:=CAST((SELECT IFNULL(SUM(sca.importo),0) FROM c_movimenti_scadenze AS sca INNER JOIN c_movimenti AS mov ON (sca.idmovimento=mov.id) WHERE sca.idcontatto=bp.idcontatto AND mov.annocontabile=bp.annocontabile AND mov.annoriferimento=bp.annocontabile AND sca.idsottoconto=bp.idsottoconto AND sca.insussistenza=0 AND (sca.idreversale_mandato IS NOT NULL OR sca.idcassa IS NOT NULL OR sca.idcassa_u IS NOT NULL)) AS DECIMAL(15,2)) AS pagato
, @tmp:=CAST((@tmp+@tmp2) AS DECIMAL(15,2)) AS tot_mov
, CAST(IF((@tmp3-@tmp)<0,((@tmp3-@tmp)*-1),0) AS DECIMAL(15,2)) AS diff_piu
, CAST(IF((@tmp3-@tmp)>0,(@tmp3-@tmp),0) AS DECIMAL(15,2)) AS diff_meno
 FROM c_bilancio AS bp LEFT JOIN c_bilancio_bp AS avan_disav ON (bp.idcontatto=avan_disav.idcontatto AND bp.annocontabile=avan_disav.annocontabile) LEFT JOIN c_pc_sottoconto AS sc ON (bp.idsottoconto=sc.id) LEFT JOIN c_pc_conto AS c ON (sc.idconto=c.id) LEFT JOIN c_pc_gruppo AS g ON (c.idgruppo=g.id)
 WHERE bp.idcontatto='PA_SPEZIA' AND bp.annocontabile='2015' AND g.type='U'
 ORDER BY g.order, c.numero, sc.numero;

UPDATE c_bilancio AS bil
INNER JOIN _ultimo_consuntivo AS uc ON (bil.idsottoconto=uc.idsottoconto AND bil.idcontatto=uc.idcontatto)
SET bil.ultimo_consuntivo=uc.tot_mov
WHERE bil.idcontatto='PA_SPEZIA' AND bil.annocontabile='2017';