#--------------------------------------------------------------------------------------------------------------------
TRUNCATE `gmcropdatabase_traits`;
TRUNCATE `gmcropdatabase_hosts`;
TRUNCATE `gmcropdatabase_companies`;
TRUNCATE `gmcropdatabase_varieties`;
TRUNCATE `gmcropdatabase_usages`;
TRUNCATE `gmcropdatabase_countries`;
TRUNCATE `gmcropdatabase_tims`;
TRUNCATE `gmcropdatabase_donors`;
TRUNCATE `gmcropdatabase_countries_authorities`;
TRUNCATE `gmcropdatabase_events`;
TRUNCATE `gmcropdatabase_events_descriptions`;
TRUNCATE `gmcropdatabase_events_esafeties`;
TRUNCATE `gmcropdatabase_events_fsafeties`;
TRUNCATE `gmcropdatabase_events_execsummaries`;
TRUNCATE `gmcropdatabase_events_introductions`;
TRUNCATE `gmcropdatabase_events_modifications`;
TRUNCATE `gmcropdatabase_events_properties`;
TRUNCATE `gmcropdatabase_events_authorizations`;
TRUNCATE `gmcropdatabase_events_links`;
TRUNCATE `gmcropdatabase_events_documents`;
TRUNCATE `gmcropdatabase_events_genes`;
#--------------------------------------------------------------------------------------------------------------------
UPDATE `companies` SET `coCountry` = 'US' WHERE  `coCountry` = 'USA';
UPDATE `companies` SET `coCountry` = 'CA' WHERE  `coCountry` = 'Canada';
UPDATE `companies` SET `coCountry` = 'DE' WHERE  `coCountry` = 'Germany';
UPDATE `companies` SET `coCountry` = 'IN' WHERE  `coCountry` = 'India';

UPDATE `links` SET `licountry` = 'US' WHERE  `licountry` = 'USA';
UPDATE `links` SET `licountry` = 'CA' WHERE  `licountry` = 'Canada';
UPDATE `links` SET `licountry` = 'DE' WHERE  `licountry` = 'Germany';
UPDATE `links` SET `licountry` = 'IN' WHERE  `licountry` = 'India';

UPDATE `links` SET `licountry` = 'PH' WHERE  `licountry` = 'FP';
UPDATE `links` SET `licountry` = 'GB' WHERE  `licountry` = 'EN';
UPDATE `links` SET `licountry` = 'GB' WHERE  `licountry` = 'UK';

UPDATE `links` SET `licountry` = 'CA' WHERE  `licountry` = '1';
UPDATE `links` SET `licountry` = 'US' WHERE  `licountry` = '2';
UPDATE `links` SET `licountry` = 'EU' WHERE  `licountry` = '3';
UPDATE `links` SET `licountry` = 'JP' WHERE  `licountry` = '4';
UPDATE `links` SET `licountry` = 'GB' WHERE  `licountry` = '5';
UPDATE `links` SET `licountry` = 'NL' WHERE  `licountry` = '6';
UPDATE `links` SET `licountry` = 'SZ' WHERE  `licountry` = '7';
UPDATE `links` SET `licountry` = 'DN' WHERE  `licountry` = '8';
UPDATE `links` SET `licountry` = 'AU' WHERE  `licountry` = '9';
UPDATE `links` SET `licountry` = 'CH' WHERE  `licountry` = '10';
UPDATE `links` SET `licountry` = 'AR' WHERE  `licountry` = '11';
UPDATE `links` SET `licountry` = 'MX' WHERE  `licountry` = '12';
UPDATE `links` SET `licountry` = 'BR' WHERE  `licountry` = '13';
UPDATE `links` SET `licountry` = 'UR' WHERE  `licountry` = '14';
UPDATE `links` SET `licountry` = 'FR' WHERE  `licountry` = '15';
UPDATE `links` SET `licountry` = 'RS' WHERE  `licountry` = '16';
UPDATE `links` SET `licountry` = 'ZA' WHERE  `licountry` = '17';
UPDATE `links` SET `licountry` = 'DE' WHERE  `licountry` = '18';
UPDATE `links` SET `licountry` = 'IN' WHERE  `licountry` = '19';
UPDATE `links` SET `licountry` = 'KR' WHERE  `licountry` = '20';
UPDATE `links` SET `licountry` = 'FL' WHERE  `licountry` = '21';
UPDATE `links` SET `licountry` = 'AT' WHERE  `licountry` = '22';
UPDATE `links` SET `licountry` = 'EY' WHERE  `licountry` = '23';
UPDATE `links` SET `licountry` = 'NG' WHERE  `licountry` = '24';
#--------------------------------------------------------------------------------------------------------------------

INSERT INTO `gmcropdatabase_countries` ( `code`, `name` )
SELECT `cn`.`c`, `cn`.`n`
FROM (
			SELECT `statescountries`.`stcode` as `c`, `statescountries`.`stName` as `n` FROM `statescountries`			
		UNION ALL
			SELECT `companies`.`coCountry` as `c`, `companies`.`coCountry` as `n` FROM `companies`			
		UNION ALL
			SELECT `stcode` as `c`,`stName` as `n` FROM `statescountries`
		UNION ALL
			SELECT `links`.`licountry` as `c`,`links`.`licountry` as `n` FROM `links`
		UNION ALL
			SELECT `regCountry` as `c`, `regCountry` as `n` FROM `reginfo`
	) `cn`
WHERE `cn`.`c` IS NOT NULL AND `cn`.`c` NOT LIKE ''
GROUP BY `cn`.`c`;

UPDATE `gmcropdatabase_countries` SET `name` = 'Afghanistan' WHERE  `code` = 'AF';
UPDATE `gmcropdatabase_countries` SET `name` = 'Papua New Guinea' WHERE  `code` = 'PG';
UPDATE `gmcropdatabase_countries` SET `name` = 'Philippines' WHERE  `code` = 'PH';
UPDATE `gmcropdatabase_countries` SET `name` = 'Saudi Arabia' WHERE  `code` = 'SA';

INSERT INTO `gmcropdatabase_countries_authorities` (`countryId`,`name`,`address1`,`address2`,`city`,`state`,`zip`,`telephone`,`facsimile`,`contact`,`web`, `email`) 
SELECT 
  `gmcropdatabase_countries`.`id`, 
  `reginfo`.`regName`, 
  `reginfo`.`regAddr1`, 
  `reginfo`.`regAddr2`, 
  `reginfo`.`regCity`, 
  `reginfo`.`regStat`, 
  `reginfo`.`regZip`, 
  `reginfo`.`regTelephone`, 
  `reginfo`.`regFacsimile`, 
  `reginfo`.`regContact`, 
  `reginfo`.`regWeb`, 
  `reginfo`.`regEMail` 
FROM `reginfo` 
 LEFT JOIN `gmcropdatabase_countries` 
   ON BINARY `gmcropdatabase_countries`.`code`=`reginfo`.`regCountry`
WHERE 
 	`reginfo`.`regLink`  IS NULL;

INSERT INTO `gmcropdatabase_companies` (`countryId`, `code`, `name`, `address1`, `address2`, `city`, `state`, `zip`, `telephone`, `facsimile`, `contact`, `web`)
SELECT 
    `gmcropdatabase_countries`.`id`, 
    `companies`.`coidcode`, 
    `companies`.`coName`, 
    `companies`.`coAddress1`, 
    `companies`.`coAddress2`, 
    `companies`.`coCity`, 
    `companies`.`coProvStat`, 
    `companies`.`coZipPC`, 
    `companies`.`coTelephone`, 
    `companies`.`coFacsimile`, 
    `companies`.`coContactName`, 
    `companies`.`coWeb` 
FROM `companies`
 LEFT JOIN `gmcropdatabase_countries` 
   ON BINARY `gmcropdatabase_countries`.`code`=`companies`.`coCountry`;

INSERT INTO `gmcropdatabase_traits` (`code`,`description`,`shortDesc`)
SELECT `trCode`, `trDescription`, `trShortDesc` FROM `traits`;  

INSERT INTO `gmcropdatabase_tims` (`code`,`description`)
SELECT `timCode`,`timDescription` FROM `tims`; 

INSERT INTO `gmcropdatabase_usages` (`code`,`description`)
SELECT `usCode`,`usDescription` FROM `usages`;

INSERT INTO `gmcropdatabase_varieties` (`code`, `name`)
SELECT `vaCode`, `vaName` FROM `varieties`;

INSERT INTO `gmcropdatabase_hosts` (`id`,`code`,`latin`,`common`,`biology`,`origin`,`diversity`,`toxins`,`allergens`,`about`)
SELECT `hstIDXCode`, `hstCode`, `hstLatin`, `hstCommon`,`hstBiology`,`hstOrigin`,`hstDiversity`,`hstToxins`,`hstAllergens`,`hstAbout` FROM `hosts`;

update `gmcropdatabase_hosts` set `latin` = replace(`latin`, '</I> L.','');
update `gmcropdatabase_hosts` set `latin` = replace(`latin`,'</I>','');
update `gmcropdatabase_hosts` set `latin` = replace(`latin`,'<I>', '');
update `gmcropdatabase_hosts` set `latin` = replace(`latin`, '</i> L.','');
update `gmcropdatabase_hosts` set `latin` = replace(`latin`,'</i>','');
update `gmcropdatabase_hosts` set `latin` = replace(`latin`,'<i>', '');

INSERT INTO `gmcropdatabase_events` (`code`,`uniqueCode`,`trialCode`,`hostId`,`varietyId`,`traitId`,`timId`,`usageId`,`showAbstract`,`updated`, `image`) 
SELECT 
	`events`.`evidcode`, 
	`events`.`evuniqueid`, 
	`events`.`evtrialcode`, 
	`gmcropdatabase_hosts`.`id`, 
	`gmcropdatabase_varieties`.`id`, 
	`gmcropdatabase_traits`.`id`, 
	`gmcropdatabase_tims`.`id`, 
	`gmcropdatabase_usages`.`id`, 
	IF ( ''=`events`.`showabstract`, 'Y', 'N') as `showabstract`,  
	`events`.`updated`, 
	`sidebars`.`sbContent` as `image` 
FROM `events`  
	LEFT JOIN `sidebars`  
		ON BINARY `events`.`evidcode`=`sidebars`.`sbevCode` AND `sidebars`.`sbType` LIKE 'IMG'  
	LEFT JOIN `gmcropdatabase_varieties`  
		ON BINARY `gmcropdatabase_varieties`.`code`=`events`.`evvarietycode`  
	LEFT JOIN `gmcropdatabase_traits`  
		ON BINARY `gmcropdatabase_traits`.`code`=`events`.`evtraitcode` 
	LEFT JOIN `gmcropdatabase_tims`  
		ON BINARY`gmcropdatabase_tims`.`code`=`events`.`evtimcode`  
	LEFT JOIN `gmcropdatabase_usages`  
		ON BINARY `gmcropdatabase_usages`.`code`=`events`.`evusage`  
	LEFT JOIN `gmcropdatabase_hosts`  
		ON BINARY `gmcropdatabase_hosts`.`id`=`events`.`evhstidx`;

UPDATE `gmcropdatabase_events` SET `image` = replace( image, 'images/dbimages/', '/files/default/GmCropDatabase/crops/' ) ;

INSERT INTO `gmcropdatabase_events_links` (`eventId`,`countryId`,`category`,`description`,`link`) 
SELECT `gmcropdatabase_events`.`id`, 
	`gmcropdatabase_countries`.`id`, 
	`links`.`liDescription`, 
	`links`.`liLong`, 
	`links`.`liLink` 
FROM `links`  
INNER JOIN `gmcropdatabase_events` ON BINARY `gmcropdatabase_events`.`code`=`links`.`lievCode`  
LEFT JOIN `gmcropdatabase_countries` ON BINARY `gmcropdatabase_countries`.`code`=`links`.`licountry` 
UNION ALL 
SELECT  `gmcropdatabase_events`.`id`, 
	NULL, 
	NULL, 
	`sidebars`.`sbDescription`, 
	`sidebars`.`sbContent` 
FROM `sidebars`  
INNER JOIN `gmcropdatabase_events` ON BINARY `gmcropdatabase_events`.`code`=`sidebars`.`sbevCode` 
WHERE `sidebars`.`sbType` LIKE 'LNK' OR `sidebars`.`sbType` LIKE 'NET';


INSERT INTO `gmcropdatabase_events_links` (`eventId`,`countryId`,`category`,`description`,`link`) 
SELECT 
	NULL,
  `gmcropdatabase_countries`.`id`, 
  `reginfo`.`regName`, 
  `reginfo`.`regDoc`, 
  `reginfo`.`regLink`
FROM `reginfo` 
 LEFT JOIN `gmcropdatabase_countries` 
   ON BINARY `gmcropdatabase_countries`.`code`=`reginfo`.`regCountry`
WHERE 
 	`reginfo`.`regLink`  IS NOT NULL;

UPDATE `gmcropdatabase_events_links` SET `link` = replace( `link`, './images/PNTFigs/', '/files/default/GmCropDatabase/docs/dbimages/' ) ;
UPDATE `gmcropdatabase_events_links` SET `link` = replace( `link`, '/decdocs/', '$MFT!@#' ) ;
UPDATE `gmcropdatabase_events_links` SET `link` = replace( `link`, 'decdocs/', '/files/default/GmCropDatabase/docs/decdocs/' ) ;
UPDATE `gmcropdatabase_events_links` SET `link` = replace( `link`, '$MFT!@#', '/files/default/GmCropDatabase/docs/decdocs/' ) ;
UPDATE `gmcropdatabase_events_links` SET `link` = replace( `link`, '/htmfiles/', '/files/default/GmCropDatabase/docs/htmfiles/' ) ;
UPDATE `gmcropdatabase_events_links` SET `link` = replace( `link`, '/articles/', '/files/default/GmCropDatabase/docs/articles/' ) ;

INSERT INTO `gmcropdatabase_events_genes` (`eventId`,`code`,`abbrCode`,`name`,`type`,`lType`,`source`,`promoter`,`effector`,`terminator`, `copies`,`form`,`srcCode`,`eCLink`,`description`)
SELECT
	`gmcropdatabase_events`.`id`,
	`genes`.`Code`,
	`genes`.`AbbrCode`,
	`genes`.`Name`,
	`genes`.`Type`,
	`genes`.`LType`,
	`genes`.`Source`,
	`genes`.`Promoter`,
	`genes`.`Effector`,
	`genes`.`Terminator`,
	`genes`.`Copies`,
	`genes`.`Form`,
	`genes`.`geSrcCode`,
	`genes`.`geECLink`,
	`genes`.`geDescription`
FROM `genes`
	INNER JOIN `gmcropdatabase_events`
		ON BINARY `gmcropdatabase_events`.`code`=`genes`.`geevCode`;

INSERT INTO `gmcropdatabase_donors` (`code`,`name`,`geneCode`, `pathogenicity`) 
    SELECT  
            `donors`.`doCode`, 
            `donors`.`doName`, 
            `donors`.`doGene`, 
            `donors`.`doPathogenicity` 
    FROM `donors`;

INSERT INTO `gmcropdatabase_events_documents` (`eventId`,`type`,`description`,`content`)
SELECT
	`gmcropdatabase_events`.`id` as `eventId`,
	'Figure' as `type`,
	`figures`.`fgCaption` as `description`,
	`figures`.`fgSource` as `content`
FROM `figures`
	INNER JOIN `gmcropdatabase_events`
		ON BINARY `gmcropdatabase_events`.`code`=`figures`.`fgEventCode`
UNION ALL
SELECT
	`gmcropdatabase_events`.`id`,
	'Figure' as `type`,
	`sidebars`.`sbDescription`,
	`sidebars`.`sbContent`
FROM `sidebars`
	INNER JOIN `gmcropdatabase_events`
		ON BINARY `gmcropdatabase_events`.`code`=`sidebars`.`sbevCode`
WHERE `sidebars`.`sbType` LIKE 'FIG'
UNION ALL
SELECT
	`gmcropdatabase_events`.`id`,
	'Document' as `type`,
	`sidebars`.`sbDescription`,
	`sidebars`.`sbContent`
FROM `sidebars`
	INNER JOIN `gmcropdatabase_events`
		ON BINARY `gmcropdatabase_events`.`code`=`sidebars`.`sbevCode`
WHERE `sidebars`.`sbType` LIKE 'FIL'
UNION ALL
SELECT
	`gmcropdatabase_events`.`id`,
	'Detection' as `type`,
	`sidebars`.`sbDescription`,
	`sidebars`.`sbContent`
FROM `sidebars`
	INNER JOIN `gmcropdatabase_events`
		ON BINARY `gmcropdatabase_events`.`code`=`sidebars`.`sbevCode`
WHERE `sidebars`.`sbType` LIKE 'DETECT'
UNION ALL
SELECT
	`gmcropdatabase_events`.`id`,
	'Biological' as `type`,
	`sidebars`.`sbDescription`,
	`sidebars`.`sbContent`
FROM `sidebars`
	INNER JOIN `gmcropdatabase_events`
		ON BINARY `gmcropdatabase_events`.`code`=`sidebars`.`sbevCode`
WHERE `sidebars`.`sbType` LIKE 'BIO';

UPDATE `gmcropdatabase_events_documents` SET `content` = replace( `content`, './images/PNTFigs/', '/files/default/GmCropDatabase/docs/dbimages/' ) ;
UPDATE `gmcropdatabase_events_documents` SET `content` = replace( `content`, '/decdocs/', '$MFT!@#' ) ;
UPDATE `gmcropdatabase_events_documents` SET `content` = replace( `content`, 'decdocs/', '/files/default/GmCropDatabase/docs/decdocs/' ) ;
UPDATE `gmcropdatabase_events_documents` SET `content` = replace( `content`, '$MFT!@#', '/files/default/GmCropDatabase/docs/decdocs/' ) ;
UPDATE `gmcropdatabase_events_documents` SET `content` = replace( `content`, '/htmfiles/', '/files/default/GmCropDatabase/docs/htmfiles/' ) ;
UPDATE `gmcropdatabase_events_documents` SET `content` = replace( `content`, '/articles/', '/files/default/GmCropDatabase/docs/articles/' ) ;
UPDATE `gmcropdatabase_events_documents` SET `content` = replace( `content`, '/dbimages/', '/files/default/GmCropDatabase/docs/dbimages/' ) WHERE `type` = 'Figure';

INSERT INTO `gmcropdatabase_events_descriptions` (`eventId`,`short`,`long`,`source`,`trialCode`) 
SELECT `gmcropdatabase_events`.`id`, `descriptions`.`deShort`, `descriptions`.`deLong`, `descriptions`.`deSource`, `descriptions`.`deevTrialCode` FROM `gmcropdatabase_events`  LEFT JOIN `descriptions` ON `gmcropdatabase_events`.`code`=`descriptions`.`deevIDCode` WHERE `descriptions`.`deShort` IS NOT NULL OR `descriptions`.`deLong` IS NOT NULL OR `descriptions`.`deSource` IS NOT NULL; 

INSERT INTO `gmcropdatabase_events_esafeties` (`eventId`,`description`) 
SELECT `gmcropdatabase_events`.`id`, `esafety`.`esDescription` FROM `gmcropdatabase_events` LEFT JOIN `esafety` ON `gmcropdatabase_events`.`code`=`esafety`.`esevCode` WHERE `esafety`.`esDescription` IS NOT NULL; 

INSERT INTO `gmcropdatabase_events_fsafeties` (`eventId`,`description`) 
SELECT `gmcropdatabase_events`.`id`, `fsafety`.`fsDescription` FROM `gmcropdatabase_events` LEFT JOIN `fsafety` ON `gmcropdatabase_events`.`code`=`fsafety`.`fsevCode` WHERE `fsafety`.`fsDescription` IS NOT NULL;

INSERT INTO `gmcropdatabase_events_execsummaries` (`eventId`,`description`) 
SELECT `gmcropdatabase_events`.`id`, `execsummary`.`exdescription` FROM `gmcropdatabase_events` LEFT JOIN `execsummary` ON `gmcropdatabase_events`.`code`=`execsummary`.`exevcode` WHERE `execsummary`.`exdescription` IS NOT NULL;

INSERT INTO `gmcropdatabase_events_introductions` (`eventId`,`description`) 
SELECT `gmcropdatabase_events`.`id`, `introduction`.`iDescription` FROM `gmcropdatabase_events` LEFT JOIN `introduction` ON `gmcropdatabase_events`.`code`=`introduction`.`ievCode` WHERE `introduction`.`iDescription` IS NOT NULL;
 	
INSERT INTO `gmcropdatabase_events_modifications` (`eventId`,`description`) 
SELECT `gmcropdatabase_events`.`id`, `modification`.`mDescription` FROM `gmcropdatabase_events` LEFT JOIN `modification` ON `gmcropdatabase_events`.`code`=`modification`.`mevCode` WHERE `modification`.`mDescription` IS NOT NULL;

INSERT INTO `gmcropdatabase_events_properties` (`eventId`,`description`) 
SELECT `gmcropdatabase_events`.`id`, `properties`.`pDescription` FROM `gmcropdatabase_events` LEFT JOIN `properties` ON `gmcropdatabase_events`.`code`=`properties`.`pevCode` WHERE `properties`.`pDescription` IS NOT NULL;


INSERT INTO `gmcropdatabase_events_companies` (`eventId`,`companyId`) 
SELECT 
  `gmcropdatabase_events`.`id`, 
  `gmcropdatabase_companies`.`id` 
FROM `events`, `companies`, `gmcropdatabase_events`, `gmcropdatabase_companies` 
WHERE `events`.`evcompanycode`=`companies`.`coidcode` 
    AND BINARY `gmcropdatabase_companies`.`code`=`companies`.`coidcode` 
    AND BINARY `gmcropdatabase_events`.`code`=`events`.`evidcode`;

INSERT INTO `gmcropdatabase_events_authorizations` (`eventId`,`countryId`,`environment`,`foodfeed`,`food`,`feed`,`marketing`,`notes`) 
SELECT 
  `gmcropdatabase_events`.`id`, 
  `gmcropdatabase_countries`.`id`, 
  `a2`.`auDate`, 
  `a3`.`auDate`, 
  `a4`.`auDate`, 
  `a5`.`auDate`, 
  `a6`.`auDate`, 
  ( 
      SELECT group_concat( `aucomments`.`aucComment` separator '; ' ) 
      FROM `aucomments` 
      WHERE BINARY `aucomments`.`aucevCode`=`a1`.`auevCode` AND `aucomments`.`aucstCode`=`a1`.`auState`
  ) 
FROM `authorizations` `a1` 
  INNER JOIN `gmcropdatabase_events` 
    ON BINARY `gmcropdatabase_events`.`code`=`a1`.`auevCode` 
  INNER JOIN `gmcropdatabase_countries` 
   ON BINARY `gmcropdatabase_countries`.`code`=`a1`.`auState` 
  LEFT JOIN ( 
      SELECT `auDate`, `auevCode`, `auState` FROM `authorizations` WHERE `authorizations`.`auTypeCode` LIKE 'ENV' 
    ) `a2` 
    ON `a1`.`auevCode`=`a2`.`auevCode` AND `a1`.`auState`=`a2`.`auState` 
  LEFT JOIN ( 
      SELECT `auDate`, `auevCode`, `auState` FROM `authorizations` WHERE `authorizations`.`auTypeCode` LIKE 'FOFE' 
    ) `a3` 
    ON `a1`.`auevCode`=`a3`.`auevCode` AND `a1`.`auState`=`a3`.`auState` 
  LEFT JOIN ( 
      SELECT `auDate`, `auevCode`, `auState` FROM `authorizations` WHERE `authorizations`.`auTypeCode` LIKE 'FOO' 
    ) `a4` 
    ON `a1`.`auevCode`=`a4`.`auevCode` AND `a1`.`auState`=`a4`.`auState` 
  LEFT JOIN ( 
      SELECT `auDate`, `auevCode`, `auState` FROM `authorizations` WHERE `authorizations`.`auTypeCode` LIKE 'FEE' 
    ) `a5` 
    ON `a1`.`auevCode`=`a5`.`auevCode` AND `a1`.`auState`=`a5`.`auState` 
  LEFT JOIN ( 
      SELECT `auDate`, `auevCode`, `auState` FROM `authorizations` WHERE `authorizations`.`auTypeCode` LIKE 'MKT' 
    ) `a6` 
    ON `a1`.`auevCode`=`a6`.`auevCode` AND `a1`.`auState`=`a5`.`auState` 
GROUP BY `a1`.`auevCode`, `a1`.`auState`;


UPDATE `gmcropdatabase_events_genes` SET `type` = NULL WHERE `type` = 'NULL';
UPDATE `gmcropdatabase_events_genes` SET `lType` = NULL WHERE `lType` = 'NULL';
UPDATE `gmcropdatabase_events_genes` SET `source` = NULL WHERE `source` = 'NULL';
UPDATE `gmcropdatabase_events_genes` SET `promoter` = NULL WHERE `promoter` = 'NULL';
UPDATE `gmcropdatabase_events_genes` SET `effector` = NULL WHERE `effector` = 'NULL';
UPDATE `gmcropdatabase_events_genes` SET `terminator` = NULL WHERE `terminator` = 'NULL';
UPDATE `gmcropdatabase_events_genes` SET `copies` = NULL WHERE `copies` = 'NULL';
UPDATE `gmcropdatabase_events_genes` SET `srcCode` = NULL WHERE `srcCode` = 'NULL';
UPDATE `gmcropdatabase_events_genes` SET `eCLink` = NULL WHERE `eCLink` = 'NULL';
UPDATE `gmcropdatabase_events_genes` SET `description` = NULL WHERE `description` = 'NULL';

UPDATE `gmcropdatabase_hosts` SET `latin` = NULL WHERE `latin` = 'NULL';
UPDATE `gmcropdatabase_hosts` SET `common` = NULL WHERE `common` = 'NULL';
UPDATE `gmcropdatabase_hosts` SET `biology` = NULL WHERE `biology` = 'NULL';
UPDATE `gmcropdatabase_hosts` SET `origin` = NULL WHERE `origin` = 'NULL';
UPDATE `gmcropdatabase_hosts` SET `diversity` = NULL WHERE `diversity` = 'NULL';
UPDATE `gmcropdatabase_hosts` SET `toxins` = NULL WHERE `toxins` = 'NULL';
UPDATE `gmcropdatabase_hosts` SET `allergens` = NULL WHERE `allergens` = 'NULL';
UPDATE `gmcropdatabase_hosts` SET `about` = NULL WHERE `about` = 'NULL';

UPDATE `gmcropdatabase_companies` SET `code` = NULL WHERE `code` = 'NULL';
UPDATE `gmcropdatabase_companies` SET `address1` = NULL WHERE `address1` = 'NULL';
UPDATE `gmcropdatabase_companies` SET `address2` = NULL WHERE `address2` = 'NULL';
UPDATE `gmcropdatabase_companies` SET `city` = NULL WHERE `city` = 'NULL';
UPDATE `gmcropdatabase_companies` SET `state` = NULL WHERE `state` = 'NULL';
UPDATE `gmcropdatabase_companies` SET `zip` = NULL WHERE `zip` = 'NULL';
UPDATE `gmcropdatabase_companies` SET `telephone` = NULL WHERE `telephone` = 'NULL';
UPDATE `gmcropdatabase_companies` SET `facsimile` = NULL WHERE `facsimile` = 'NULL';
UPDATE `gmcropdatabase_companies` SET `contact` = NULL WHERE `contact` = 'NULL';
UPDATE `gmcropdatabase_companies` SET `email` = NULL WHERE `email` = 'NULL';
UPDATE `gmcropdatabase_companies` SET `web` = NULL WHERE `web` = 'NULL';


#-- UPDATE `gmcropdatabase_tims` SET `description` = strip_tags(`gmcropdatabase_tims`.`description`);
#-- UPDATE `gmcropdatabase_traits` SET `description` = strip_tags(`gmcropdatabase_traits`.`description`);
#-- UPDATE `gmcropdatabase_traits` SET `shortDesc` = strip_tags(`gmcropdatabase_traits`.`shortDesc`);
#-- UPDATE `gmcropdatabase_usages` SET `description` = strip_tags(`gmcropdatabase_usages`.`description`);
#--------------------------------------------------------------------------------------------------------------------


 SET GLOBAL log_bin_trust_function_creators=1;
 DROP FUNCTION IF EXISTS `strip_tags`;
 DELIMITER $$
 CREATE FUNCTION `strip_tags`($str text) RETURNS text
 BEGIN
     DECLARE $start, $end INT DEFAULT 1;
     LOOP
         SET $start = LOCATE("<", $str, $start);
         IF (!$start) THEN RETURN $str; END IF;
         SET $end = LOCATE(">", $str, $start);
         IF (!$end) THEN SET $end = $start; END IF;
        SET $str = INSERT($str, $start, $end - $start + 1, "");
     END LOOP;
 END;
 $$
 DELIMITER ;
 
UPDATE `gmcropdatabase_events_genes` SET `code` = strip_tags(`gmcropdatabase_events_genes`.`code`);
UPDATE `gmcropdatabase_events_genes` SET `source` = strip_tags(`gmcropdatabase_events_genes`.`source`);
UPDATE `gmcropdatabase_donors` SET `geneCode` = strip_tags(`gmcropdatabase_donors`.`geneCode`);

UPDATE `gmcropdatabase_events_authorizations` SET `food` =  `foodfeed`, `feed` = `foodfeed` WHERE `foodfeed` IS NOT NULL AND `food` IS NULL AND `feed` IS NULL;


UPDATE `gmcropdatabase_events_descriptions` SET `short`=REPLACE(`short`,"\n","<br>");
UPDATE `gmcropdatabase_events_descriptions` SET `long`=REPLACE(`long`,"\n","<br>");
UPDATE `gmcropdatabase_events_esafeties` SET `description`=REPLACE(`description`,"\n","<br>");
UPDATE `gmcropdatabase_events_fsafeties` SET `description`=REPLACE(`description`,"\n","<br>");
UPDATE `gmcropdatabase_events_execsummaries` SET `description`=REPLACE(`description`,"\n","<br>");
UPDATE `gmcropdatabase_events_introductions` SET `description`=REPLACE(`description`,"\n","<br>");
UPDATE `gmcropdatabase_events_links` SET `description`=REPLACE(`description`,"\n","<br>");
UPDATE `gmcropdatabase_events_modifications` SET `description`=REPLACE(`description`,"\n","<br>");
UPDATE `gmcropdatabase_events_properties` SET `description`=REPLACE(`description`,"\n","<br>");

UPDATE `gmcropdatabase_events_descriptions` SET `short`=REPLACE(`short`,"\r\n","<br>");
UPDATE `gmcropdatabase_events_descriptions` SET `long`=REPLACE(`long`,"\r\n","<br>");
UPDATE `gmcropdatabase_events_esafeties` SET `description`=REPLACE(`description`,"\r\n","<br>");
UPDATE `gmcropdatabase_events_fsafeties` SET `description`=REPLACE(`description`,"\r\n","<br>");
UPDATE `gmcropdatabase_events_execsummaries` SET `description`=REPLACE(`description`,"\r\n","<br>");
UPDATE `gmcropdatabase_events_introductions` SET `description`=REPLACE(`description`,"\r\n","<br>");
UPDATE `gmcropdatabase_events_links` SET `description`=REPLACE(`description`,"\r\n","<br>");
UPDATE `gmcropdatabase_events_modifications` SET `description`=REPLACE(`description`,"\r\n","<br>");
UPDATE `gmcropdatabase_events_properties` SET `description`=REPLACE(`description`,"\r\n","<br>");

UPDATE `gmcropdatabase_events_descriptions` SET `short`=REPLACE(`short`,"\r","<br>");
UPDATE `gmcropdatabase_events_descriptions` SET `long`=REPLACE(`long`,"\r","<br>");
UPDATE `gmcropdatabase_events_esafeties` SET `description`=REPLACE(`description`,"\r","<br>");
UPDATE `gmcropdatabase_events_fsafeties` SET `description`=REPLACE(`description`,"\r","<br>");
UPDATE `gmcropdatabase_events_execsummaries` SET `description`=REPLACE(`description`,"\r","<br>");
UPDATE `gmcropdatabase_events_introductions` SET `description`=REPLACE(`description`,"\r","<br>");
UPDATE `gmcropdatabase_events_links` SET `description`=REPLACE(`description`,"\r","<br>");
UPDATE `gmcropdatabase_events_modifications` SET `description`=REPLACE(`description`,"\r","<br>");
UPDATE `gmcropdatabase_events_properties` SET `description`=REPLACE(`description`,"\r","<br>");

UPDATE `gmcropdatabase_events_descriptions` SET `long`=REPLACE(`long`,"<br />","<br>");
UPDATE `gmcropdatabase_events_esafeties` SET `description`=REPLACE(`description`,"<br />","<br>");
UPDATE `gmcropdatabase_events_fsafeties` SET `description`=REPLACE(`description`,"<br />","<br>");
UPDATE `gmcropdatabase_events_execsummaries` SET `description`=REPLACE(`description`,"<br />","<br>");
UPDATE `gmcropdatabase_events_introductions` SET `description`=REPLACE(`description`,"<br /","<br>");
UPDATE `gmcropdatabase_events_links` SET `description`=REPLACE(`description`,"<br />","<br>");
UPDATE `gmcropdatabase_events_modifications` SET `description`=REPLACE(`description`,"<br />","<br>");
UPDATE `gmcropdatabase_events_properties` SET `description`=REPLACE(`description`,"<br />","<br>");

UPDATE `gmcropdatabase_events_descriptions` SET `short`=REPLACE(`short`,"<br>\r<br>","<br>");
UPDATE `gmcropdatabase_events_descriptions` SET `long`=REPLACE(`long`,"<br>\r<br>","<br>");
UPDATE `gmcropdatabase_events_esafeties` SET `description`=REPLACE(`description`,"<br>\r<br>","<br>");
UPDATE `gmcropdatabase_events_fsafeties` SET `description`=REPLACE(`description`,"<br>\r<br>","<br>");
UPDATE `gmcropdatabase_events_execsummaries` SET `description`=REPLACE(`description`,"<br>\r<br>","<br>");
UPDATE `gmcropdatabase_events_introductions` SET `description`=REPLACE(`description`,"<br>\r<br>","<br>");
UPDATE `gmcropdatabase_events_links` SET `description`=REPLACE(`description`,"<br>\r<br>","<br>");
UPDATE `gmcropdatabase_events_modifications` SET `description`=REPLACE(`description`,"<br>\r<br>","<br>");
UPDATE `gmcropdatabase_events_properties` SET `description`=REPLACE(`description`,"<br>\r<br>","<br>");

UPDATE `gmcropdatabase_events_descriptions` SET `short`=REPLACE(`short`,"<br><br>","<br>");
UPDATE `gmcropdatabase_events_descriptions` SET `long`=REPLACE(`long`,"<br><br>","<br>");
UPDATE `gmcropdatabase_events_esafeties` SET `description`=REPLACE(`description`,"<br><br>","<br>");
UPDATE `gmcropdatabase_events_fsafeties` SET `description`=REPLACE(`description`,"<br><br>","<br>");
UPDATE `gmcropdatabase_events_execsummaries` SET `description`=REPLACE(`description`,"<br><br>","<br>");
UPDATE `gmcropdatabase_events_introductions` SET `description`=REPLACE(`description`,"<br><br>","<br>");
UPDATE `gmcropdatabase_events_links` SET `description`=REPLACE(`description`,"<br><br>","<br>");
UPDATE `gmcropdatabase_events_modifications` SET `description`=REPLACE(`description`,"<br><br>","<br>");
UPDATE `gmcropdatabase_events_properties` SET `description`=REPLACE(`description`,"<br><br>","<br>");

UPDATE `gmcropdatabase_events_descriptions` SET `short`=REPLACE(`short`,"<br><br>","<br>");
UPDATE `gmcropdatabase_events_descriptions` SET `long`=REPLACE(`long`,"<br><br>","<br>");
UPDATE `gmcropdatabase_events_esafeties` SET `description`=REPLACE(`description`,"<br><br>","<br>");
UPDATE `gmcropdatabase_events_fsafeties` SET `description`=REPLACE(`description`,"<br><br>","<br>");
UPDATE `gmcropdatabase_events_execsummaries` SET `description`=REPLACE(`description`,"<br><br>","<br>");
UPDATE `gmcropdatabase_events_introductions` SET `description`=REPLACE(`description`,"<br><br>","<br>");
UPDATE `gmcropdatabase_events_links` SET `description`=REPLACE(`description`,"<br><br>","<br>");
UPDATE `gmcropdatabase_events_modifications` SET `description`=REPLACE(`description`,"<br><br>","<br>");
UPDATE `gmcropdatabase_events_properties` SET `description`=REPLACE(`description`,"<br><br>","<br>");


UPDATE `gmcropdatabase_events_descriptions` SET `short`=REPLACE(`short`,"Â’","’");
UPDATE `gmcropdatabase_events_descriptions` SET `long`=REPLACE(`long`,"Â’","’");
UPDATE `gmcropdatabase_events_esafeties` SET `description`=REPLACE(`description`,"Â’","’");
UPDATE `gmcropdatabase_events_fsafeties` SET `description`=REPLACE(`description`,"Â’","’");
UPDATE `gmcropdatabase_events_execsummaries` SET `description`=REPLACE(`description`,"Â’","’");
UPDATE `gmcropdatabase_events_introductions` SET `description`=REPLACE(`description`,"Â’","’");
UPDATE `gmcropdatabase_events_links` SET `description`=REPLACE(`description`,"Â’","’");
UPDATE `gmcropdatabase_events_modifications` SET `description`=REPLACE(`description`,"Â’","’");
UPDATE `gmcropdatabase_events_properties` SET `description`=REPLACE(`description`,"Â’","’");

UPDATE `gmcropdatabase_events_descriptions` SET `short`=REPLACE(`short`," < "," &lt; ");
UPDATE `gmcropdatabase_events_descriptions` SET `long`=REPLACE(`long`," < "," &lt; ");
UPDATE `gmcropdatabase_events_esafeties` SET `description`=REPLACE(`description`," < "," &lt; ");
UPDATE `gmcropdatabase_events_fsafeties` SET `description`=REPLACE(`description`," < "," &lt; ");
UPDATE `gmcropdatabase_events_execsummaries` SET `description`=REPLACE(`description`," < "," &lt; ");
UPDATE `gmcropdatabase_events_introductions` SET `description`=REPLACE(`description`," < "," &lt; ");
UPDATE `gmcropdatabase_events_links` SET `description`=REPLACE(`description`," < "," &lt; ");
UPDATE `gmcropdatabase_events_modifications` SET `description`=REPLACE(`description`," < "," &lt; ");
UPDATE `gmcropdatabase_events_properties` SET `description`=REPLACE(`description`," < "," &lt; ");

UPDATE `gmcropdatabase_events_descriptions` SET `short`=REPLACE(`short`," <= "," &lt;= ");
UPDATE `gmcropdatabase_events_descriptions` SET `long`=REPLACE(`long`," <= "," &lt;= ");
UPDATE `gmcropdatabase_events_esafeties` SET `description`=REPLACE(`description`," <= "," &lt;= ");
UPDATE `gmcropdatabase_events_fsafeties` SET `description`=REPLACE(`description`," <= "," &lt;= ");
UPDATE `gmcropdatabase_events_execsummaries` SET `description`=REPLACE(`description`," <= "," &lt;= ");
UPDATE `gmcropdatabase_events_introductions` SET `description`=REPLACE(`description`," <= "," &lt;= ");
UPDATE `gmcropdatabase_events_links` SET `description`=REPLACE(`description`," <= "," &lt;= ");
UPDATE `gmcropdatabase_events_modifications` SET `description`=REPLACE(`description`," <= "," &lt;= ");
UPDATE `gmcropdatabase_events_properties` SET `description`=REPLACE(`description`," <= "," &lt;= ");

UPDATE `gmcropdatabase_events_descriptions` SET `short`=REPLACE(`short`," > "," &gt; ");
UPDATE `gmcropdatabase_events_descriptions` SET `long`=REPLACE(`long`," > "," &gt; ");
UPDATE `gmcropdatabase_events_esafeties` SET `description`=REPLACE(`description`," > "," &gt; ");
UPDATE `gmcropdatabase_events_fsafeties` SET `description`=REPLACE(`description`," > "," &gt; ");
UPDATE `gmcropdatabase_events_execsummaries` SET `description`=REPLACE(`description`," > "," &gt; ");
UPDATE `gmcropdatabase_events_introductions` SET `description`=REPLACE(`description`," > "," &gt; ");
UPDATE `gmcropdatabase_events_links` SET `description`=REPLACE(`description`," > "," &gt; ");
UPDATE `gmcropdatabase_events_modifications` SET `description`=REPLACE(`description`," > "," &gt; ");
UPDATE `gmcropdatabase_events_properties` SET `description`=REPLACE(`description`," > "," &gt; ");

UPDATE `gmcropdatabase_events_descriptions` SET `short`=REPLACE(`short`," => "," =&gt; ");
UPDATE `gmcropdatabase_events_descriptions` SET `long`=REPLACE(`long`," => "," =&gt; ");
UPDATE `gmcropdatabase_events_esafeties` SET `description`=REPLACE(`description`," => "," =&gt; ");
UPDATE `gmcropdatabase_events_fsafeties` SET `description`=REPLACE(`description`," => "," =&gt; ");
UPDATE `gmcropdatabase_events_execsummaries` SET `description`=REPLACE(`description`," => "," =&gt; ");
UPDATE `gmcropdatabase_events_introductions` SET `description`=REPLACE(`description`," => "," =&gt; ");
UPDATE `gmcropdatabase_events_links` SET `description`=REPLACE(`description`," => "," =&gt; ");
UPDATE `gmcropdatabase_events_modifications` SET `description`=REPLACE(`description`," => "," =&gt; ");
UPDATE `gmcropdatabase_events_properties` SET `description`=REPLACE(`description`," => "," =&gt; ");

UPDATE `gmcropdatabase_events_descriptions` SET `short`=REPLACE(`short`," (< "," (&lt; ");
UPDATE `gmcropdatabase_events_descriptions` SET `long`=REPLACE(`long`," (< "," (&lt; ");
UPDATE `gmcropdatabase_events_esafeties` SET `description`=REPLACE(`description`," (< "," (&lt; ");
UPDATE `gmcropdatabase_events_fsafeties` SET `description`=REPLACE(`description`," (< "," (&lt; ");
UPDATE `gmcropdatabase_events_execsummaries` SET `description`=REPLACE(`description`," (< "," (&lt; ");
UPDATE `gmcropdatabase_events_introductions` SET `description`=REPLACE(`description`," (< "," (&lt; ");
UPDATE `gmcropdatabase_events_links` SET `description`=REPLACE(`description`," (< "," (&lt; ");
UPDATE `gmcropdatabase_events_modifications` SET `description`=REPLACE(`description`," (< "," (&lt; ");
UPDATE `gmcropdatabase_events_properties` SET `description`=REPLACE(`description`," (< "," (&lt; ");

UPDATE `gmcropdatabase_events_documents` SET `content` = replace( `content`, '/files/default/', '/files/cera/' ) ;
UPDATE `gmcropdatabase_events_links` SET `link` = replace( `link`, '/files/default/', '/files/cera/' ) ;
UPDATE `gmcropdatabase_events` SET `image` = replace( image, '/files/default/', '/files/cera/' ) ;
UPDATE `bibliography_bibliography` SET `file` = replace( file, '/files/default/', '/files/cera/' ) ;
UPDATE `widget_widgets_i18n` SET `image` = replace( image, '/files/default/', '/files/cera/' ) ;
UPDATE `widget_widgets_i18n` SET `content` = replace( content, '/files/default/', '/files/cera/' ) ;
UPDATE `page_pages_i18n` SET `content` = replace( content, '/files/default/', '/files/cera/' ) ;