/**Drop database rmscheduler_orkincr;**/
create database rmscheduler_orkincr
Select * from rmscheduler_orkincr.eventsPermanent;
set sql_safe_updates = 0;
/**  Series **/

Select * from rmscheduler_orkincr.eventsPermanent as e Inner Join rmscheduler_orkincr.scheduleSeriesDefinitions as s On e.seriesId =  s.serieId ;

Select * from rmscheduler_orkincr.eventsPermanent where id not in (Select e.id from rmscheduler_orkincr.eventsPermanent as e Inner Join rmscheduler_orkincr.scheduleSeriesDefinitions as s On e.seriesId =  s.serieId);


Update rmscheduler_orkincr.eventsPermanent as e Inner Join rmscheduler_orkincr.scheduleSeriesDefinitions as s On e.seriesId =  s.serieId  set e.seriesId = s.id;

ALTER TABLE rmscheduler_orkincr.eventsPermanent  ADD FOREIGN KEY (seriesId) REFERENCES scheduleSeriesDefinitions(id); 


/**and concat('',customer * 1) != customer;**/

set sql_safe_updates = 0; 
/** Organizaciones **/

alter table rmscheduler_orkincr.organizations add column `idPrimary` int  primary KEY AUTO_INCREMENT First;

SELECT * FROM rmscheduler_orkincr.organizations;

Update rmscheduler_orkincr.eventsPermanent as e Inner Join rmscheduler_orkincr.organizations as o On (e.customer =  CONCAT("|",o.Name,":",o.Id,"|") OR e.customer =  CONCAT("|",o.Name," :",o.Id,"|")
OR e.customer =  CONCAT("|",TRIM(o.Name),":",o.Id,"|")) set customer = o.idPrimary;

Select e.id from rmscheduler_orkincr.eventsPermanent as e Inner Join rmscheduler_orkincr.organizations as o On e.customer =  CONCAT("|",o.Name,":",o.Id,"|")  ;

Select * from rmscheduler_orkincr.eventsPermanent  where id not in (Select e.id from rmscheduler_orkincr.eventsPermanent as e Inner Join rmscheduler_orkincr.organizations as o On e.customer =  CONCAT("|",o.Name,":",o.Id,"|") 
OR e.customer =  CONCAT("|",o.Name," :",o.Id,"|")) and concat('',customer * 1) != customer;

ALTER TABLE rmscheduler_orkincr.eventsPermanent  ADD FOREIGN KEY (customer) REFERENCES rmscheduler_orkincr.organizations(Id); 

Update rmscheduler_orkincr.scheduleSeriesDefinitions as e Inner Join rmscheduler_orkincr.organizations as o On (e.organization =  o.Name OR CONCAT(e.organization,' ') =  o.Name 
OR e.organization =  CONCAT(o.Name,' ')) set organization = o.idPrimary;


/** Location **/


SELECT * FROM rmscheduler_orkincr.organizationLocations;

Update rmscheduler_orkincr.eventsPermanent as e Inner Join rmscheduler_orkincr.organizationLocations as l On (e.location =  CONCAT("|",l.Name,":",l.Id,"|") OR e.location =  CONCAT("|",l.Name,":",l.Id,"|")
OR e.location =  CONCAT("|",TRIM(l.Name),":",l.Id,"|")) set location = l.Id;

Select * from rmscheduler_orkincr.eventsPermanent as e Inner Join rmscheduler_orkincr.organizationLocations as l On e.location =  CONCAT("|",l.Name,":",l.Id,"|")  ;

Select * from rmscheduler_orkincr.eventsPermanent  where id not in (Select e.id from rmscheduler_orkincr.eventsPermanent as e Inner Join rmscheduler_orkincr.organizationLocations as l On e.location =  CONCAT("|",l.Name,":",l.Id,"|") );

ALTER TABLE rmscheduler_orkincr.eventsPermanent  ADD FOREIGN KEY (location) REFERENCES rmscheduler_orkincr.organizationLocations(Id); 


Update rmscheduler_orkincr.scheduleSeriesDefinitions as e Inner Join rmscheduler_orkincr.organizationLocations as I On  (e.location =  CONCAT(I.Name,":",I.Id) OR e.location =  CONCAT(I.Name," :",I.Id) OR
e.location =  CONCAT(TRIM(I.Name),":",I.Id)) set location = I.Id;

/**  Persons **/

Update rmscheduler_orkincr.eventsPermanent set personnel = "|Gomez Sanchez, Jose [E]:202|Gomez Sandi, Erick [A]:861|" where personnel = "|Gomez Sanchez, Jose [E]:202|G�mez Sand�, Erick [A]:861|";
Update rmscheduler_orkincr.eventsPermanent set personnel = "|Cordoba Salmeron, Cesar [E]:767|Gomez Sandi, Erick [A]:861|" where personnel = "|Cordoba Salmeron, Cesar [E]:767|G�mez Sand�, Erick [A]:861|";

/*Equipment*/
Update rmscheduler_orkincr.equipment e Inner Join rmscheduler_orkincr.organizations s On e.organizationId =  s.Id  set e.organizationId = s.idPrimary;



/*Sche*/ncr_test.eventsPermanent set personnel = "|Gomez Sanchez, Jose [E]:202|Gomez Sandi, Erick [A]:861|" where personnel = "|Gomez Sanchez, Jose [E]:202|G�mez Sand�, Erick [A]:861|";
Update rmscheduler_orkincr.eventsPermanent set personnel = "|Cordoba Salmeron, Cesar [E]:767|Gomez Sandi, Erick [A]:861|" where personnel = "|Cordoba Salmeron, Cesar [E]:767|G�mez Sand�, Erick [A]:861|";

/** OrganizationPersons **/


alter table rmscheduler_orkincr.organizationPersons add column `id` int  primary KEY AUTO_INCREMENT First;



/** OrganizationPersons **/


alter table rmscheduler_orkincr.organizationPersons add column `id` int  primary KEY AUTO_INCREMENT First;


/** EventsPersons **/

Create table eventPerson (
	id int primary key auto_increment,
    idPerson int,
    idEvent int
);


/**EventsEquipment **/

Create table eventEquipment (
	id int primary key auto_increment,
    idEquipment int,
    idEvent int
);

/*EventActivity*/

Create table eventActivity (
	id int primary key auto_increment,
    idActivity int,
    idEvent int
);


/** SeriesPersons **/

Create table seriePerson (
	id int primary key auto_increment,
    idPerson int,
    idSerie int
);


/**SeriesEquipment **/

Create table serieEquipment (
	id int primary key auto_increment,
    idEquipment int,
    idSerie int
);

/*SeriesActivity*/

Create table serieActivity (
	id int primary key auto_increment,
    idActivity int,
    idSerie int
);

/*OrganizationHolidays*/
Update rmscheduler_orkincr.organizationHolidays e Inner Join rmscheduler_orkincr.organizations s On e.OrganizationId =  s.Id  set e.OrganizationId = s.idPrimary;


/*OrganizationTraining*/
	Update rmscheduler_orkincr.organizationTraining e Inner Join rmscheduler_orkincr.organizations s On e.organizationId =  s.Id  set e.organizationId = s.idPrimary;

/*OrganizationPersons*/
Update rmscheduler_orkincr.organizationPersons e Inner Join rmscheduler_orkincr.organizations s On e.organizationId =  s.Id  set e.organizationId = s.idPrimary;

/*AutomaticReportDefinitions*/
Update rmscheduler_orkincr.automaticReportDefinitions e Inner Join rmscheduler_orkincr.organizations s On e.identifier COLLATE utf8_general_ci =  s.Id COLLATE utf8_general_ci  set e.identifier = s.idPrimary;

/*OrganizationLocations */
Update rmscheduler_orkincr.organizationLocations e
 Inner Join rmscheduler_orkincr.organizations s On e.OrganizationId =  s.Id  set e.OrganizationId = s.idPrimary;


 
 
 
 
