DECLARE @TIPO_DOCUMENTO_PRIVATI int;
SET @TIPO_DOCUMENTO_PRIVATI = 6;       --- Tipo documento associato alla causale fatture fittizia privati ---

--- DECLARE @ESERCIZI_ANALISI int;
--- SET @ESERCIZI_ANALISI = 8;          --- numero di esercizi gestiti (compreso quello in corso) ---

-----------------------------
--- PULIZIA ESERCIZI VECCHI
-----------------------------
 
--- sega gli anni vecchi su base contabile ---
--- SET @DATA_INIZIALE = DATE_ADD(CONCAT((YEAR(NOW()) - @ESERCIZI_ANALISI), "-", @MESE_CHIUSURA_ESERCIZIO, "-", "01"), INTERVAL 1 MONTH);
 
--- sega gli anni vecchi su base solare 
--- DECLARE @DATA_INIZIALE date;
--- SET @DATA_INIZIALE = CONCAT((YEAR(GetDate()) - @ESERCIZI_ANALISI), '-', '01' , '-', '01');
--- INSERT INTO _cdm_ VALUES (CONCAT('Sega documenti antecedenti ', @DATA_INIZIALE), GetDate());
 
--- Fatture 
 
INSERT INTO _cdm_ VALUES ('Elimina fatture non stampate', GetDate());

DELETE AVALRF_01
FROM AVALRF_01
LEFT JOIN AFATTZ_01 ON AVALRF_01.ID_AZI = AFATTZ_01.ID_AZI AND AVALRF_01.NUMERO = AFATTZ_01.NUMFAT AND AVALRF_01.NUMRIG = AFATTZ_01.NUMRIG AND AVALRF_01.NUMCOM = AFATTZ_01.NUMCOM
LEFT JOIN AFATT1_01 ON AFATTZ_01.ID_AZI = AFATT1_01.ID_AZI AND AFATTZ_01.NUMFAT = AFATT1_01.NUMFAT
WHERE AFATT1_01.AA_IVA = 0;

DELETE AFATT1_01
FROM AFATT1_01
WHERE AFATT1_01.AA_IVA = 0;
 
INSERT INTO _cdm_ VALUES ('Elimina fatture proforma', GetDate());

DELETE AVALRF_01
FROM AVALRF_01
LEFT JOIN AFATTZ_01 ON AVALRF_01.ID_AZI = AFATTZ_01.ID_AZI AND AVALRF_01.NUMERO = AFATTZ_01.NUMFAT AND AVALRF_01.NUMRIG = AFATTZ_01.NUMRIG AND AVALRF_01.NUMCOM = AFATTZ_01.NUMCOM
LEFT JOIN AFATT1_01 ON AFATTZ_01.ID_AZI = AFATT1_01.ID_AZI AND AFATTZ_01.NUMFAT = AFATT1_01.NUMFAT
LEFT JOIN ATBACF_01 ON ATBACF_01.ID_AZI = AFATT1_01.ID_AZI AND ATBACF_01.CODICE = AFATT1_01.CAUFAT
WHERE ATBACF_01.TIPFAT = '5';

DELETE AFATTZ_01
FROM AFATTZ_01
LEFT JOIN AFATT1_01 ON AFATTZ_01.ID_AZI = AFATT1_01.ID_AZI AND AFATTZ_01.NUMFAT = AFATT1_01.NUMFAT
LEFT JOIN ATBACF_01 ON ATBACF_01.ID_AZI = AFATT1_01.ID_AZI AND ATBACF_01.CODICE = AFATT1_01.CAUFAT
WHERE ATBACF_01.TIPFAT = '5';

DELETE AFATT1_01
FROM AFATT1_01
LEFT JOIN ATBACF_01 ON ATBACF_01.ID_AZI = AFATT1_01.ID_AZI AND ATBACF_01.CODICE = AFATT1_01.CAUFAT
WHERE ATBACF_01.TIPFAT = '5';
 
/*Agente statistico*/
/*alter table AFATT1_01
add column COD_AGENTE_STATISTICO char(7);
 
update AFATT1_01 , ASTAAG_01 set COD_AGENTE_STATISTICO = if(ASTAAG_01.ASTAAG_CODAGE is null ,AFATT1_FK1_ATBAAG, concat(AFATT1_01.AFATT1_ID_AZI,right (concat("00000",ASTAAG_01.ASTAAG_CODSTA),5)))
where AFATT1_01.AFATT1_FK1_ATBAAG = ASTAAG_01.ASTAAG_ID;
update AFATT1_01 set COD_AGENTE_STATISTICO = AFATT1_FK1_ATBAAG where COD_AGENTE_STATISTICO is null;*/
 
/* Punto vendita */
 
/*INSERT INTO _cdm_ VALUES ('Elimina vecchie bollette punto vendita', GetDate());
 
DELETE APVEN1_01, AVALRP_01, APVENZ_01
FROM APVEN1_01
LEFT JOIN APVENZ_01 ON APVENZ_FK1_APVEN1 = APVEN1_ID
LEFT JOIN AVALRP_01 ON AVALRP_FK1_APVENZ = APVENZ_ID
WHERE APVEN1_DTABOL < @DATA_INIZIALE;*/

/***********************************************
  CLIENTI
|***********************************************/
 
/* INSERT INTO _cdm_ VALUES ('Elimina clienti senza fatture', GetDate()); */
 
/* DELETE CLI FROM AANCL1_01 CLI */
/* LEFT JOIN AFATT1_01 TF ON TF.ID_AZI = CLI.ID_AZI AND TF.CODCLI = CLI.CODICE */
/* WHERE TF.CODCLI IS NULL; */
/* go */
 
INSERT INTO _cdm_ VALUES ('Elimina destinatari di clienti inesistenti', GetDate());
 
DELETE DES FROM AANCLF_01 DES
LEFT JOIN AANCL1_01 CLI ON CLI.ID_AZI = DES.ID_AZI AND CLI.CODICE = DES.CODCLI
WHERE CLI.CODICE IS NULL;
go
 
/*Clienti Statistici*/
 
/*alter table AFATT1_01
add column COD_CLIENTE_STATISTICO char(8);
 
update AFATT1_01 , ASTACD_01 set COD_CLIENTE_STATISTICO = if(ASTACD_01.ASTACD_CODCLI is null ,AFATT1_FK1_AANCL1, concat(AFATT1_01.AFATT1_ID_AZI,ASTACD_01.ASTACD_STACLI))
where AFATT1_01.AFATT1_FK1_AANCL1 = ASTACD_01.ASTACD_CODCLI;
update AFATT1_01 set COD_CLIENTE_STATISTICO = AFATT1_FK1_AANCL1 where COD_CLIENTE_STATISTICO is null;
 
INSERT INTO _cdm_ VALUES ('Aggiunte alla tabella clienti', GetDate());
go*/ 

-------------------------------------------------
---  ARTICOLI
-------------------------------------------------
 
/*
INSERT INTO _cdm_ VALUES ('Elimina articoli senza fatture ne bollette', GetDate());
 
DELETE AARTI1_01 FROM AARTI1_01
LEFT JOIN AFATTZ_01 ON AFATTZ_01.ID_AZI = AARTI1_01.ID_AZI AND AFATTZ_01.CODART = AARTI1_01.CODICE
LEFT JOIN APVENZ_01 ON APVENZ_01.ID_AZI = AARTI1_01.ID_AZI AND APVENZ_01.CODART = AARTI1_01.CODICE
WHERE AFATTZ_01.CODART IS NULL AND APVENZ_01.CODART IS NULL;
go*/ 
 
/*Articoli statistici*/
 
/*alter table AFATTZ_01
add column COD_ARTICOLO_STATISTICO char(17);
 
update AFATTZ_01 , ASTABA_01 set COD_ARTICOLO_STATISTICO = if(ASTABA_01.ASTABA_CODART is null ,AFATTZ_FK1_AARTI1, concat(AFATTZ_01.AFATTZ_ID_AZI, right(concat('__________',ASTABA_01.ASTABA_CODSTA),15)))
where AFATTZ_01.AFATTZ_FK1_AARTI1 = ASTABA_01.ASTABA_ID;
 
update AFATTZ_01 set COD_ARTICOLO_STATISTICO = AFATTZ_FK1_AARTI1 where COD_ARTICOLO_STATISTICO is null;
 
go*/ 

/* articoli movimentati */
INSERT INTO _cdm_ VALUES ('Costruzione tabella ARTICOLI_MOVIMENTATI', GetDate());

if exists(select table_name from av2000_alyante.information_schema.tables where table_name = 'ARTICOLI_MOVIMENTATI') drop table dbo.ARTICOLI_MOVIMENTATI
select A.ID_AZI, A.CODICE  
into dbo.ARTICOLI_MOVIMENTATI from AARTI1_01 A
where A.CODICE in (select CODART from AMMAG1_01 M where M.ID_AZI = A.ID_AZI)
or A.CODICE in (select CODART from ASALDM_01 S where S.ID_AZI = A.ID_AZI)
or A.CODICE in (select CODART from AFATTZ_01 S where S.ID_AZI = A.ID_AZI)
or A.CODICE in (select CODART from ABOLLZ_01 S where S.ID_AZI = A.ID_AZI)
or A.CODICE in (select CODART from AORDIZ_01 S where S.ID_AZI = A.ID_AZI)
go

-------------------------------------------------
---  FATTURE
-------------------------------------------------
 
/* Elimina righe vuoti da fatture e punto vendita */
INSERT INTO _cdm_ VALUES ('Elimina righe vuoti', GetDate());
 
DELETE FROM AFATTZ_01 WHERE AFATTZ_01.TIPRIG = 'V';
DELETE FROM APVENZ_01 WHERE APVENZ_01.TIPRIG = 'V';
go 

----------------------------------------------------
--- FLAGS CLIENTI
----------------------------------------------------
INSERT INTO dbo._cdm_ (EVENTO, DATA_ORA) VALUES ('Generazione tabella FLAGS_CLIENTI', GetDate());
go

IF EXISTS(SELECT TABLE_NAME FROM AV2000_ALYANTE.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'FLAGS_CLIENTI') DROP TABLE dbo.FLAGS_CLIENTI;
GO

SELECT C.ID_AZI, C.CODICE CODCLI 
,(SELECT IIF(F.CODECC IS NULL, P.FATRIE, IIF(P.FATRIE = 'S', 'N', 'S')) FATRIE FROM APAZDC_01 P LEFT JOIN AANCL4_01 F ON F.ID_AZI = C.ID_AZI AND F.CODICE = C.CODICE AND F.CODECC = 1 WHERE P.ID_AZI = C.ID_AZI) FATRIE
,(SELECT IIF(F.CODECC IS NULL, P.STAAGE, IIF(P.STAAGE = 'S', 'N', 'S')) STAAGE FROM APAZDC_01 P LEFT JOIN AANCL4_01 F ON F.ID_AZI = C.ID_AZI AND F.CODICE = C.CODICE AND F.CODECC = 2 WHERE P.ID_AZI = C.ID_AZI) STAAGE
,(SELECT IIF(F.CODECC IS NULL, P.SPEINC, IIF(P.SPEINC = 'S', 'N', 'S')) SPEINC FROM APAZDC_01 P LEFT JOIN AANCL4_01 F ON F.ID_AZI = C.ID_AZI AND F.CODICE = C.CODICE AND F.CODECC = 3 WHERE P.ID_AZI = C.ID_AZI) SPEINC
,(SELECT IIF(F.CODECC IS NULL, P.INVSOL, IIF(P.INVSOL = 'S', 'N', 'S')) INVSOL FROM APAZDC_01 P LEFT JOIN AANCL4_01 F ON F.ID_AZI = C.ID_AZI AND F.CODICE = C.CODICE AND F.CODECC = 4 WHERE P.ID_AZI = C.ID_AZI) INVSOL
,(SELECT IIF(F.CODECC IS NULL, P.SOGCAU, IIF(P.SOGCAU = 'S', 'N', 'S')) SOGCAU FROM APAZDC_01 P LEFT JOIN AANCL4_01 F ON F.ID_AZI = C.ID_AZI AND F.CODICE = C.CODICE AND F.CODECC = 5 WHERE P.ID_AZI = C.ID_AZI) SOGCAU
,(SELECT IIF(F.CODECC IS NULL, P.RIFORD, IIF(P.RIFORD = 'S', 'N', 'S')) RIFORD FROM APAZDC_01 P LEFT JOIN AANCL4_01 F ON F.ID_AZI = C.ID_AZI AND F.CODICE = C.CODICE AND F.CODECC = 6 WHERE P.ID_AZI = C.ID_AZI) RIFORD
,(SELECT IIF(F.CODECC IS NULL, P.BLOFAT, IIF(P.BLOFAT = 'S', 'N', 'S')) BLOFAT FROM APAZDC_01 P LEFT JOIN AANCL4_01 F ON F.ID_AZI = C.ID_AZI AND F.CODICE = C.CODICE AND F.CODECC = 7 WHERE P.ID_AZI = C.ID_AZI) BLOFAT
,(SELECT IIF(F.CODECC IS NULL, P.STAALL, IIF(P.STAALL = 'S', 'N', 'S')) STAALL FROM APAZDC_01 P LEFT JOIN AANCL4_01 F ON F.ID_AZI = C.ID_AZI AND F.CODICE = C.CODICE AND F.CODECC = 8 WHERE P.ID_AZI = C.ID_AZI) STAALL
,(SELECT IIF(F.CODECC IS NULL, P.LISPVE, IIF(P.LISPVE = 'S', 'N', 'S')) LISPVE FROM APAZDC_01 P LEFT JOIN AANCL4_01 F ON F.ID_AZI = C.ID_AZI AND F.CODICE = C.CODICE AND F.CODECC = 9 WHERE P.ID_AZI = C.ID_AZI) LISPVE
,(SELECT IIF(F.CODECC IS NULL, P.DESFAT, IIF(P.DESFAT = 'S', 'N', 'S')) DESFAT FROM APAZDC_01 P LEFT JOIN AANCL4_01 F ON F.ID_AZI = C.ID_AZI AND F.CODICE = C.CODICE AND F.CODECC = 10	WHERE P.ID_AZI = C.ID_AZI) DESFAT
,(SELECT IIF(F.CODECC IS NULL, P.PRZBOL, IIF(P.PRZBOL = 'S', 'N', 'S')) PRZBOL FROM APAZDC_01 P LEFT JOIN AANCL4_01 F ON F.ID_AZI = C.ID_AZI AND F.CODICE = C.CODICE AND F.CODECC = 11	WHERE P.ID_AZI = C.ID_AZI) PRZBOL
,(SELECT IIF(F.CODECC IS NULL, P.DESISF, IIF(P.DESISF = 'S', 'N', 'S')) DESISF FROM APAZDC_01 P LEFT JOIN AANCL4_01 F ON F.ID_AZI = C.ID_AZI AND F.CODICE = C.CODICE AND F.CODECC = 12	WHERE P.ID_AZI = C.ID_AZI) DESISF
,(SELECT IIF(F.CODECC IS NULL, P.FATMEN, IIF(P.FATMEN = 'S', 'N', 'S')) FATMEN FROM APAZDC_01 P LEFT JOIN AANCL4_01 F ON F.ID_AZI = C.ID_AZI AND F.CODICE = C.CODICE AND F.CODECC = 13	WHERE P.ID_AZI = C.ID_AZI) FATMEN
,(SELECT IIF(F.CODECC IS NULL, P.BOLCAU, IIF(P.BOLCAU = 'S', 'N', 'S')) BOLCAU FROM APAZDC_01 P LEFT JOIN AANCL4_01 F ON F.ID_AZI = C.ID_AZI AND F.CODICE = C.CODICE AND F.CODECC = 14	WHERE P.ID_AZI = C.ID_AZI) BOLCAU
,(SELECT IIF(F.CODECC IS NULL, P.SOGVUO, IIF(P.SOGVUO = 'S', 'N', 'S')) SOGVUO FROM APAZDC_01 P LEFT JOIN AANCL4_01 F ON F.ID_AZI = C.ID_AZI AND F.CODICE = C.CODICE AND F.CODECC = 15	WHERE P.ID_AZI = C.ID_AZI) SOGVUO
,(SELECT IIF(F.CODECC IS NULL, P.BLOCOB, IIF(P.BLOCOB = 'S', 'N', 'S')) BLOCOB FROM APAZDC_01 P LEFT JOIN AANCL4_01 F ON F.ID_AZI = C.ID_AZI AND F.CODICE = C.CODICE AND F.CODECC = 16	WHERE P.ID_AZI = C.ID_AZI) BLOCOB
,(SELECT IIF(F.CODECC IS NULL, P.STALOT, IIF(P.STALOT = 'S', 'N', 'S')) STALOT FROM APAZDC_01 P LEFT JOIN AANCL4_01 F ON F.ID_AZI = C.ID_AZI AND F.CODICE = C.CODICE AND F.CODECC = 17	WHERE P.ID_AZI = C.ID_AZI) STALOT
,(SELECT IIF(F.CODECC IS NULL, P.LOTFAT, IIF(P.LOTFAT = 'S', 'N', 'S')) LOTFAT FROM APAZDC_01 P LEFT JOIN AANCL4_01 F ON F.ID_AZI = C.ID_AZI AND F.CODICE = C.CODICE AND F.CODECC = 18	WHERE P.ID_AZI = C.ID_AZI) LOTFAT
,(SELECT IIF(F.CODECC IS NULL, P.STAEAB, IIF(P.STAEAB = 'S', 'N', 'S')) STAEAB FROM APAZDC_01 P LEFT JOIN AANCL4_01 F ON F.ID_AZI = C.ID_AZI AND F.CODICE = C.CODICE AND F.CODECC = 19	WHERE P.ID_AZI = C.ID_AZI) STAEAB
,(SELECT IIF(F.CODECC IS NULL, P.STAEAC, IIF(P.STAEAC = 'S', 'N', 'S')) STAEAC FROM APAZDC_01 P LEFT JOIN AANCL4_01 F ON F.ID_AZI = C.ID_AZI AND F.CODICE = C.CODICE AND F.CODECC = 20	WHERE P.ID_AZI = C.ID_AZI) STAEAC
,(SELECT IIF(F.CODECC IS NULL, P.RFCFAT, IIF(P.RFCFAT = 'S', 'N', 'S')) RFCFAT FROM APAZDC_01 P LEFT JOIN AANCL4_01 F ON F.ID_AZI = C.ID_AZI AND F.CODICE = C.CODICE AND F.CODECC = 21	WHERE P.ID_AZI = C.ID_AZI) RFCFAT
,(SELECT IIF(F.CODECC IS NULL, P.[2BOLLE], IIF(P.[2BOLLE] = 'S', 'N', 'S')) [2BOLLE ] FROM APAZDC_01 P LEFT JOIN AANCL4_01 F ON F.ID_AZI = C.ID_AZI AND F.CODICE = C.CODICE AND F.CODECC = 22 WHERE P.ID_AZI = C.ID_AZI) [2BOLLE]
,(SELECT IIF(F.CODECC IS NULL, P.STNCFA, IIF(P.STNCFA = 'S', 'N', 'S')) STNCFA FROM APAZDC_01 P LEFT JOIN AANCL4_01 F ON F.ID_AZI = C.ID_AZI AND F.CODICE = C.CODICE AND F.CODECC = 23	WHERE P.ID_AZI = C.ID_AZI) STNCFA
,(SELECT IIF(F.CODECC IS NULL, P.LUCISF, IIF(P.LUCISF = 'S', 'N', 'S')) LUCISF FROM APAZDC_01 P LEFT JOIN AANCL4_01 F ON F.ID_AZI = C.ID_AZI AND F.CODICE = C.CODICE AND F.CODECC = 24	WHERE P.ID_AZI = C.ID_AZI) LUCISF
,(SELECT IIF(F.CODECC IS NULL, P.CSAISD, IIF(P.CSAISD = 'S', 'N', 'S')) CSAISD FROM APAZDC_01 P LEFT JOIN AANCL4_01 F ON F.ID_AZI = C.ID_AZI AND F.CODICE = C.CODICE AND F.CODECC = 25	WHERE P.ID_AZI = C.ID_AZI) CSAISD
INTO FLAGS_CLIENTI
FROM AANCL1_01 C
GO

-------------------------------------------------
---  PULIZIA TABELLA AZIENDE
-------------------------------------------------
 
/* Elimina aziende non esportate (non hanno nemmeno un cliente sul database) */
INSERT INTO _cdm_ VALUES ('Elimina aziende non interessanti', GetDate());

DELETE FROM XAZIE1_01 WHERE CODICE NOT IN (select DISTINCT ID_AZI from AANCL1_01 )
GO
---------------------------
--- fine
---------------------------
 
INSERT INTO dbo._cdm_ (EVENTO, DATA_ORA) VALUES ('Fine esecuzione alyante_end', GetDate());
go
