ALTER TABLE avcp_lotto DROP PRIMARY KEY;

ALTER TABLE `avcp_lotto` ADD `id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

ALTER TABLE `avcp_lotto` CHANGE `cig` `cig` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0000000000';

ALTER TABLE `avcp_ld` ADD `id` INT( 11 ) UNSIGNED NULL DEFAULT NULL FIRST;

DROP INDEX `cig` ON `avcp_ld`;

UPDATE `avcp_ld` SET avcp_ld.id=(SELECT avcp_lotto.id FROM avcp_lotto WHERE avcp_lotto.cig = avcp_ld.cig);


DROP VIEW `avcp_xml`;

CREATE VIEW `avcp_xml` AS select `avcp_lotto`.`id` AS `id`,`avcp_lotto`.`anno` AS `anno`,`avcp_lotto`.`cig` AS `cig`,`avcp_ditta`.`codiceFiscale` AS `codiceFiscale`,`avcp_ld`.`ruolo` AS `ruolo`,`avcp_ld`.`funzione` AS `funzione`,`avcp_ld`.`raggruppamento` AS `raggruppamento`,`avcp_lotto`.`codiceFiscaleProp` AS `codiceFiscaleProp`,`avcp_lotto`.`denominazione` AS `denominazione`,`avcp_lotto`.`oggetto` AS `oggetto`,`avcp_lotto`.`sceltaContraente` AS `sceltaContraente`,`avcp_lotto`.`importoAggiudicazione` AS `importoAggiudicazione`,`avcp_lotto`.`dataInizio` AS `dataInizio`,`avcp_lotto`.`dataUltimazione` AS `dataUltimazione`,`avcp_lotto`.`importoSommeLiquidate` AS `importoSommeLiquidate`,`avcp_ditta`.`ragioneSociale` AS `ragioneSociale`,`avcp_ditta`.`estero` AS `estero` from ((`avcp_ld` join `avcp_lotto`) join `avcp_ditta`) where ((`avcp_ld`.`id` = `avcp_lotto`.`id`) and (`avcp_ld`.`codiceFiscale` = `avcp_ditta`.`codiceFiscale`)) order by `avcp_lotto`.`cig`,`avcp_ld`.`funzione`,`avcp_ld`.`raggruppamento`;

DROP VIEW `avcp_xml_ditte`;

CREATE VIEW `avcp_xml_ditte` AS select `avcp_ld`.`id` AS `id`,`avcp_ld`.`cig` AS `cig`,`avcp_ditta`.`codiceFiscale` AS `codiceFiscale`,`avcp_ditta`.`estero` AS `estero`,`avcp_ld`.`ruolo` AS `ruolo`,`avcp_ld`.`funzione` AS `funzione`,`avcp_ld`.`raggruppamento` AS `raggruppamento`,`avcp_ditta`.`ragioneSociale` AS `ragioneSociale` from (`avcp_ld` join `avcp_ditta`) where (`avcp_ld`.`codiceFiscale` = `avcp_ditta`.`codiceFiscale`) order by `avcp_ld`.`funzione`,`avcp_ld`.`raggruppamento`;