-- CREAMOS UNA TABLA TEMPORAL PARA ALMACENAR LOS REGISTROS 
CREATE TABLE tmp_document (
  iddocument int NOT NULL AUTO_INCREMENT,
  campo1 		varchar(300)  NULL ,
  campo2 		varchar(300) 	NULL,
  campo3 		varchar(300) 	NULL,
  campo4 		varchar(300) 	NULL,
  campo5 		varchar(300) 	NULL,
  campo6 		varchar(300) 	NULL,
  campo7 		varchar(300) 	NULL,
  campo8 		varchar(300) 	NULL,
  campo9 		varchar(300) 	NULL,
  campo10 	varchar(300) 	NULL,
  campo11 	varchar(600) 	NULL,
  campo12 	varchar(300) 	NULL,
  campo13 	varchar(300) 	NULL,
  campo14 	varchar(300) 	NULL,
  campo15 	varchar(300) 	NULL,
  campo16 	varchar(300) 	NULL,
	
  CONSTRAINT pk_iddocument_tmpdocument PRIMARY KEY (iddocument) 
) ENGINE = INNODB;

-- AHORA CARGAMOS LA VISTA PARA VISUALIZAR LOS DATOS 

CREATE VIEW view_document_tmp
AS 
SELECT tmp_document.iddocument AS 'IDDOCUMENT',tmp_document.campo11 AS 'DOCUMENTO',tmp_document.campo12 AS 'PRIVADO',
IFNULL((SELECT users.id FROM vtiger_users users WHERE (users.description = tmp_document.campo2)),'1') AS 'USER_ASIGNATED',tmp_document.campo2,
(CASE 

	WHEN ((SELECT COUNT(0) FROM vtiger_contactscf WHERE (vtiger_contactscf.cf_918 = tmp_document.campo8)) > 0) THEN 'Contacts'
	WHEN ((SELECT COUNT(0) FROM vtiger_accountscf WHERE (vtiger_accountscf.cf_894 = tmp_document.campo8)) > 0) THEN 'Accounts' 
	WHEN ((SELECT COUNT(0) FROM vtiger_potentialscf WHERE (vtiger_potentialscf.cf_1052 = tmp_document.campo8)) > 0) THEN 'Potentials' 
	WHEN ((SELECT COUNT(0) FROM vtiger_proycf WHERE (vtiger_proycf.cf_1166 = tmp_document.campo8)) > 0) THEN 'Proy' 
	ELSE 'Ninguno' END) AS 'MODULO_DOCUMENT',
	(CASE 
	
	WHEN ((SELECT COUNT(0) FROM vtiger_contactscf WHERE (vtiger_contactscf.cf_918 = tmp_document.campo8)) > 0) THEN (SELECT vtiger_contactscf.contactid FROM vtiger_contactscf WHERE (vtiger_contactscf.cf_918 = tmp_document.campo8) LIMIT 1) 
	WHEN ((SELECT COUNT(0) FROM vtiger_accountscf WHERE (vtiger_accountscf.cf_894 = tmp_document.campo8)) > 0) THEN (SELECT vtiger_accountscf.accountid FROM vtiger_accountscf WHERE (vtiger_accountscf.cf_894 = tmp_document.campo8) LIMIT 1) 
	WHEN ((SELECT COUNT(0) FROM vtiger_potentialscf WHERE (vtiger_potentialscf.cf_1052 = tmp_document.campo8)) > 0) THEN (SELECT vtiger_potentialscf.potentialid FROM vtiger_potentialscf WHERE (vtiger_potentialscf.cf_1052 = tmp_document.campo8) LIMIT 1) 
	WHEN ((SELECT COUNT(0) FROM vtiger_proycf WHERE (vtiger_proycf.cf_1166 = tmp_document.campo8)) > 0) THEN (SELECT vtiger_proycf.proyid FROM vtiger_proycf WHERE (vtiger_proycf.cf_1166 = tmp_document.campo8) LIMIT 1) 
	ELSE '' END) AS 'ID_MODULE_DOCUMENT',
	
	tmp_document.campo5 AS 'FECHA_CREACION', tmp_document.campo9 AS 'SIZE', tmp_document.campo15 AS 'MIGRADO', tmp_document.campo16	AS 'CARPETA'
FROM tmp_document;


-- CARGAMOS LOS SCRIPT DEL EXCEL 

-- CREAMOS UNA TABLA MAS QUE ALMACENARA TODOS LOS REGISTROS PARA EL REPORTE 
CREATE TABLE tmp_document_report (
  iddocument int NOT NULL AUTO_INCREMENT,
  campo1 		varchar(300)  NULL ,
  campo2 		varchar(300) 	NULL,
  campo3 		varchar(300) 	NULL,
  campo4 		varchar(300) 	NULL,
  campo5 		varchar(300) 	NULL,
  campo6 		varchar(300) 	NULL,
  campo7 		varchar(300) 	NULL,
  campo8 		varchar(300) 	NULL,
  campo9 		varchar(300) 	NULL,
  campo10 	varchar(300) 	NULL,
  campo11 	varchar(600) 	NULL,
  campo12 	varchar(300) 	NULL,
  campo13 	varchar(300) 	NULL,
  campo14 	varchar(300) 	NULL,
  campo15 	varchar(300) 	NULL,
  campo16 	varchar(300) 	NULL,
	
  CONSTRAINT pk_iddocument_tmpdocument PRIMARY KEY (iddocument) 
) ENGINE = INNODB;



