DROP TABLE curso;
CREATE TABLE curso (
  id 			int(10)	unsigned NOT NULL AUTO_INCREMENT,
  i3l_id 		char(10),
  centro 		smallint(4) unsigned NOT NULL,
  localidad 		char(5),
  nombre 		varchar(100) NOT NULL DEFAULT '',
  id_sector_act 	char(1),
  id_subsector_act 	char(2),
  fecha 		date NOT NULL DEFAULT '0000-00-00',
  fecha_fin 		date NOT NULL DEFAULT '0000-00-00',
  duracion 		smallint(4) unsigned NOT NULL DEFAULT '0',
  practicas 		smallint(4) unsigned NOT NULL DEFAULT '0',
  mod_ma 		tinyint(1) unsigned NOT NULL DEFAULT '0',
  mod_si 		tinyint(1) unsigned NOT NULL DEFAULT '0',
  mod_lab 		tinyint(1) DEFAULT '0',
  mod_ig 		tinyint(1) DEFAULT '0',
  proyecto 		smallint(6) DEFAULT NULL,
			/* Timestamp de la modificación */
  modificado    	timestamp DEFAULT CURRENT_TIMESTAMP 
  			ON UPDATE CURRENT_TIMESTAMP, 

  time_carga    	datetime,  /* Timestamp de la ultima carga*/
  PRIMARY KEY (id)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Itinerario. Cursos de Formación' ;

DROP TABLE prefor;
CREATE TABLE prefor(
  id int(10) 	unsigned NOT NULL AUTO_INCREMENT,
  i3l_id 	char(10),
  centro 	smallint(4) unsigned NOT NULL,
  id_tipo 	tinyint(3) unsigned NOT NULL DEFAULT '0',
  fecha 	date NOT NULL ,
  duracion 	smallint(4) unsigned NOT NULL DEFAULT '0',
  idioma	char(2) , /*CS Castellano, CT Catalan,EU Eusquera, GL Gallego*/
  descripcion	varchar(100),
  proyecto 	smallint(6) DEFAULT NULL,
  modificado    timestamp, /* Timestamp de la modificación */
  time_carga    timestamp,  /* Timestamp de la ultima carga*/
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Preformaciones' ;

alter table usuarios_cepaim.ins_form_ocup drop id_curso;
alter table usuarios_cepaim.ins_form_ocup add id_curso int(10) unsigned ;
alter table usuarios_cepaim.ins_form_ocup add CONSTRAINT fk_id_curso FOREIGN KEY (id_curso) REFERENCES  i3l.curso(id);

create unique index cursos_formacion on usuarios_cepaim.ins_form_ocup(idusuario, centro_origen,id_curso)

alter table usuarios_cepaim.ins_form_ocup add UNIQUE KEY cursos_formacion (idusuario,centro_origen,id_curso,proyecto); 


alter table familias.form_ocup add id_curso int(10) unsigned ;
alter table familias.form_ocup add CONSTRAINT fk_id_curso FOREIGN KEY (id_curso) REFERENCES  i3l.curso(id);
alter table familias.form_ocup add UNIQUE KEY cursos_formacion (idusuario,centro_origen,id_curso,proyecto);


/* Tabla de preformacion para familias */
alter table familias.formacion add id_curso int(10) unsigned ;
alter table familias.formacion add CONSTRAINT fk_id_curso3 FOREIGN KEY (id_curso) REFERENCES  i3l.prefor(id);
alter table familias.formacion add UNIQUE KEY cursos_preformacion (idusuario,centro_origen,id_curso,proyecto);


/* Tabla de preformacion para autoempleo individuales y diver */
alter table usuarios_cepaim.ins_prefor add id_curso int(10) unsigned;
alter table usuarios_cepaim.ins_prefor add CONSTRAINT fk_id_curso4 FOREIGN KEY (id_curso) REFERENCES  i3l.prefor(id);
alter table usuarios_cepaim.ins_prefor add UNIQUE KEY cursos_preformacion (idusuario,centro_origen,id_curso,proyecto);


/* Llenado de curso y prefor */
/* Individuales */
insert into i3l.curso (id_sector_act,id_subsector_act,nombre,centro, fecha, fecha_fin, duracion, practicas,proyecto) select distinct 'S','TH',nombre,centro_origen,fecha, fin_formacion,duracion, practicas,proyecto FROM  usuarios_cepaim.ins_form_ocup where year(fecha)=2014 and proyecto=2; 

/*autoempleo*/
insert into i3l.curso (id_sector_act,id_subsector_act,nombre,centro, fecha, fecha_fin, duracion, practicas,proyecto) select distinct 'S','TH',nombre,centro_origen,fecha, fin_formacion,duracion, practicas,proyecto FROM  usuarios_cepaim.ins_form_ocup where year(fecha)=2014 and proyecto=33; 

/*diversifica*/
insert into i3l.curso (id_sector_act,id_subsector_act,nombre,centro, fecha, fecha_fin, duracion, practicas,proyecto) select distinct 'S','TH',nombre,centro_origen,fecha, fin_formacion,duracion, practicas,proyecto FROM  usuarios_cepaim.ins_form_ocup where year(fecha)=2014 and proyecto=87; 


/*familias*/
insert into i3l.curso (id_sector_act,id_subsector_act,nombre,centro, fecha, fecha_fin, duracion, practicas,proyecto) select distinct 'S','TH',nombre,centro_origen,fecha, fin_formacion,duracion, practicas,55 FROM  familias.form_ocup where year(fecha)=2014; 

insert into i3l.curso (id_sector_act,id_subsector_act,nombre,centro, fecha, fecha_fin, duracion, practicas,proyecto) select distinct 'S','TH',nombre,centro_origen,fecha, fin_formacion,duracion, practicas,55 FROM  familias.form_ocup where year(fecha)<2014; 

/*Actualización inicial de identificadores de curso*/
update usuarios_cepaim.ins_form_ocup f,i3l.curso c 
set f.id_curso=c.id  
where 
f.nombre=c.nombre and
f.centro_origen=c.centro and
f.fecha=c.fecha and
f.fin_formacion=c.fecha_fin and
f.duracion=c.duracion and
f.proyecto=c.proyecto;

/*Consulta de comprobación. Chequeo de nombre de curso*/ 
select f.nombre, c.nombre from ins_form_ocup f, i3l.curso c where f.id_curso=c.id;

/*Actualización de codigos de curso en ocupacionales de familias*/
update familias.form_ocup f, i3l.curso c
set f.id_curso=c.id
where
f.centro_origen=c.centro and
f.fecha=c.fecha and
f.fin_formacion=c.fecha_fin and
f.duracion=c.duracion and  
c.proyecto=55;


/*PREFORMACIONES*/
update usuarios_cepaim.ins_prefor set id_curso=NULL;
update familias.formacion set id_curso=NULL;

/*individuales*/
insert into i3l.prefor (centro,fecha,id_tipo,duracion,proyecto,idioma,descripcion) select distinct centro_origen, fecha, id_tipo, duracion,proyecto,'CS',l.nombre from usuarios_cepaim.ins_prefor p, usuarios_cepaim.li_ins_prefor l where p.id_tipo=l.id and year(fecha)=2014 and proyecto in (33,2,87);

/*familias*/
 insert into i3l.prefor (centro,fecha,id_tipo,duracion,proyecto,idioma,descripcion) select distinct centro_origen,fecha, id_tipo, duracion,55,'CS',l.nombre from familias.formacion f, familias.li_formacion l  where f.id_tipo=l.id and year(fecha)=2014;

 insert into i3l.prefor (centro,fecha,id_tipo,duracion,proyecto,idioma,descripcion) select distinct centro_origen,fecha, id_tipo, duracion,55,'CS',l.nombre from familias.formacion f, familias.li_formacion l  where f.id_tipo=l.id and year(fecha)<2014;


/*Actualización de códigos de preformaciones*/
select distinct * from usuarios_cepaim.ins_prefor p, i3l.prefor l
where
p.centro_origen=l.centro and
p.fecha=l.fecha and
p.id_tipo=l.id_tipo and
p.duracion=l.duracion and
year(p.fecha)<2014 and
p.proyecto in (33,2,87);

update  usuarios_cepaim.ins_prefor p, i3l.prefor l
set p.id_curso = l.id 
where
p.centro_origen=l.centro and
p.fecha=l.fecha and
p.id_tipo=l.id_tipo and
p.duracion=l.duracion and
year(p.fecha)<2014 and
p.proyecto in (33,2,87);

select * from familias.formacion p, i3l.prefor l
where
p.centro_origen=l.centro and
p.fecha=l.fecha and
p.id_tipo=l.id_tipo and
p.duracion=l.duracion ;

update familias.formacion p, i3l.prefor l
set p.id_curso = l.id
where
p.centro_origen=l.centro and
p.fecha=l.fecha and
p.id_tipo=l.id_tipo and
year(p.fecha)<2014 and
p.duracion=l.duracion ;

/*QUERIES DE AYUDA*/
/*Usuarios del centro 2 con cursos de formacion */
 select count(*), idusuario,centro_origen from usuarios_cepaim.ins_form_ocup where year(fecha)=2014 group by idusuario,centro_origen having centro_origen=2;

/*Usuarios del centro 2 con preformacion */
 select count(centro_origen),idusuario,centro_origen from usuarios_cepaim.ins_prefor group by idusuario,centro_origen having centro_origen=2;
