DELIMITER $$


DROP PROCEDURE IF EXISTS `prepareLocalUpdate`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `prepareLocalUpdate`(IN localdb VARCHAR(50), IN centraldb VARCHAR(50))
BEGIN
    
    SET @getStmnt = CONCAT('update ',localdb,'.germplsm a set a.lgid=a.gid');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    DROP TABLE IF EXISTS maxtable;
    CREATE TABLE maxtable(gid INT, nid INT, locid INT, aid INT, cid INT, ldid INT, listid INT, instalid INT, uid INT, admin INT, refid INT, lrecid INT, fldno INT);
    
    SET @getStmnt = CONCAT(
	'insert into ',localdb,'.maxtable(gid) 
		select MAX(b.gid) from ',centraldb,'.germplsm b');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT('update ',localdb,'.maxtable 
	set nid=(select MAX(b.nid) from ',centraldb,'.names b)');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT('update ',localdb,'.maxtable 
	set locid=(select MAX(b.locid) from ',centraldb,'.location b)');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT('update ',localdb,'.maxtable 
	set aid=(select MAX(b.aid) from ',centraldb,'.atributs b)');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT('update ',localdb,'.maxtable 
	set cid=(select MAX(b.cid) from ',centraldb,'.changes b)');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT('update ',localdb,'.maxtable 
	set ldid=(select MAX(b.ldid) from ',centraldb,'.locdes b)');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT('update ',localdb,'.maxtable 
	set listid=(select MAX(b.listid) from ',centraldb,'.listnms b)');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT('update ',localdb,'.maxtable 
	set instalid=(select MAX(b.instalid) from ',centraldb,'.instln b)');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT('SELECT MIN(userid) INTO @min1 FROM ',centraldb,'.users 
	WHERE userid >= (SELECT MAX(userid) FROM ',centraldb,'.users 
		WHERE userid < (SELECT MAX(userid) FROM ',centraldb,'.users))');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT('SELECT MAX(userid) INTO @max1 FROM ',centraldb,'.users 
	WHERE userid >= (SELECT MAX(userid) FROM ',centraldb,'.users 
		WHERE userid < (SELECT MAX(userid) FROM ',centraldb,'.users))');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;

    IF (@min1 IS NOT NULL AND @max1 IS NOT NULL) THEN
         IF (@min1+1)<@max1 THEN
		SET @getStmnt = CONCAT('update ',localdb,'.maxtable set uid=',@min1);
		PREPARE stmnt1 FROM @getStmnt;
		EXECUTE stmnt1;
		DEALLOCATE PREPARE stmnt1;    
         ELSE
		SET @getStmnt = CONCAT('update ',localdb,'.maxtable set uid=',@max1);
		PREPARE stmnt1 FROM @getStmnt;
		EXECUTE stmnt1;
		DEALLOCATE PREPARE stmnt1;    
         END IF;
    END IF;
    
    SET @getStmnt = CONCAT('update ',localdb,'.maxtable 
	set admin=(select MAX(b.admin) from ',centraldb,'.instln b)');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT('update ',localdb,'.maxtable 
	set refid=(select MAX(b.refid) from ',centraldb,'.bibrefs b)');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;    
    
    SET @getStmnt = CONCAT('update ',localdb,'.maxtable 
	set lrecid=(select MAX(b.lrecid) from ',centraldb,'.listdata b)');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;    
  
    SET @getStmnt = CONCAT('update ',localdb,'.maxtable 
	set fldno=(select MAX(b.fldno) from ',centraldb,'.udflds b)');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;    
      
    DROP TABLE IF EXISTS updategid; 
    CREATE TABLE updategid (gid INT NOT NULL AUTO_INCREMENT, lgid INT, PRIMARY KEY (gid));
    
    DROP TABLE IF EXISTS updateloc; 
    CREATE TABLE updateloc (locid INT NOT NULL AUTO_INCREMENT, llocid INT, PRIMARY KEY (locid));

    DROP TABLE IF EXISTS updatechng;
    CREATE TABLE updatechng (cid INT NOT NULL AUTO_INCREMENT, lcid INT, PRIMARY KEY (cid));

    DROP TABLE IF EXISTS updatenames;
    CREATE TABLE updatenames (nid INT NOT NULL AUTO_INCREMENT, lnid INT, PRIMARY KEY (nid));

    DROP TABLE IF EXISTS updateatributs;
    CREATE TABLE updateatributs (aid INT NOT NULL AUTO_INCREMENT, laid INT, PRIMARY KEY (aid));

    DROP TABLE IF EXISTS updatelistnms;
    CREATE TABLE updatelistnms (listid INT NOT NULL AUTO_INCREMENT, llistid INT, PRIMARY KEY (listid));

    DROP TABLE IF EXISTS updateinstln;
    CREATE TABLE updateinstln (instalid INT NOT NULL AUTO_INCREMENT, linstalid INT, PRIMARY KEY (instalid));

    DROP TABLE IF EXISTS updateusers;
    CREATE TABLE updateusers (userid INT NOT NULL AUTO_INCREMENT, luserid INT, PRIMARY KEY (userid));

    DROP TABLE IF EXISTS updateadmin;
    CREATE TABLE updateadmin (admin INT NOT NULL AUTO_INCREMENT, ladmin INT, PRIMARY KEY (admin));

    DROP TABLE IF EXISTS updatebibref;
    CREATE TABLE updatebibref (refid INT NOT NULL AUTO_INCREMENT, lrefid INT, PRIMARY KEY (refid));

    DROP TABLE IF EXISTS updatelistdata;
    CREATE TABLE updatelistdata (lrecid INT NOT NULL AUTO_INCREMENT, llrecid INT, PRIMARY KEY (lrecid));

    DROP TABLE IF EXISTS updateudflds;
    CREATE TABLE updateudflds (fldno INT NOT NULL AUTO_INCREMENT, lfldno INT, PRIMARY KEY (fldno));

    SET @getStmnt = CONCAT('select a.gid INTO @id FROM ',localdb,'.maxtable a');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;

    IF @id > 0 THEN
	SET @getStmnt = CONCAT('insert into ',localdb,'.updategid (gid, lgid) select a.gid, a.gid from ',localdb,'.maxtable a');
	PREPARE stmnt1 FROM @getStmnt;
	EXECUTE stmnt1;
	DEALLOCATE PREPARE stmnt1;
    END IF;    

    SET @getStmnt = CONCAT('select a.locid INTO @id FROM ',localdb,'.maxtable a');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;

    DEALLOCATE PREPARE stmnt1;
    IF @id > 0 THEN
	SET @getStmnt = CONCAT('insert into ',localdb,'.updateloc (locid, llocid) select a.locid, a.locid from ',localdb,'.maxtable a');
	PREPARE stmnt1 FROM @getStmnt;
	EXECUTE stmnt1;
	DEALLOCATE PREPARE stmnt1;
    END IF;    
    
    SET @getStmnt = CONCAT('select a.cid INTO @id FROM ',localdb,'.maxtable a'); 
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;

    IF @id > 0 THEN
	SET @getStmnt = CONCAT('insert into ',localdb,'.updatechng (cid, lcid) select a.cid, a.cid from ',localdb,'.maxtable a');             
	PREPARE stmnt1 FROM @getStmnt;
	EXECUTE stmnt1;
	DEALLOCATE PREPARE stmnt1;
    END IF;    
    
    SET @getStmnt = CONCAT('select a.nid INTO @id FROM ',localdb,'.maxtable a'); 
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;

    IF @id > 0 THEN
        SET @getStmnt = CONCAT('insert into ',localdb,'.updatenames (nid, lnid) select a.nid, a.nid from ',localdb,'.maxtable a');
	PREPARE stmnt1 FROM @getStmnt;
	EXECUTE stmnt1;
	DEALLOCATE PREPARE stmnt1;
    END IF;    
    
    SET @getStmnt = CONCAT('select a.aid INTO @id FROM ',localdb,'.maxtable a'); 
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;

    IF @id > 0 THEN
        SET @getStmnt = CONCAT('insert into ',localdb,'.updateatributs (aid, laid) select a.aid, a.aid from ',localdb,'.maxtable a');
        PREPARE stmnt1 FROM @getStmnt;
	EXECUTE stmnt1;
	DEALLOCATE PREPARE stmnt1;
    END IF;    
    
    SET @getStmnt = CONCAT('select a.listid INTO @id FROM ',localdb,'.maxtable a'); 
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;

    IF @id > 0 THEN
        SET @getStmnt = CONCAT('insert into ',localdb,'.updatelistnms (listid, llistid) select a.listid, a.listid from ',localdb,'.maxtable a');
	PREPARE stmnt1 FROM @getStmnt;
	EXECUTE stmnt1;
	DEALLOCATE PREPARE stmnt1;
    END IF;    
    
    SET @getStmnt = CONCAT('select a.instalid INTO @id FROM ',localdb,'.maxtable a'); 
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;

    IF @id > 0 THEN
        SET @getStmnt = CONCAT('insert into ',localdb,'.updateinstln (instalid, linstalid) select a.instalid, a.instalid from ',localdb,'.maxtable a');
	PREPARE stmnt1 FROM @getStmnt;
	EXECUTE stmnt1;
	DEALLOCATE PREPARE stmnt1;
    END IF;    
    
    SET @getStmnt = CONCAT('select a.uid INTO @id FROM ',localdb,'.maxtable a'); 
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;

    IF @id > 0 THEN
        SET @getStmnt = CONCAT('insert into ',localdb,'.updateusers (userid, luserid) select a.uid, a.uid from ',localdb,'.maxtable a');
	PREPARE stmnt1 FROM @getStmnt;
	EXECUTE stmnt1;
	DEALLOCATE PREPARE stmnt1;
    END IF;    
    
    SET @getStmnt = CONCAT('select a.admin INTO @id FROM ',localdb,'.maxtable a'); 
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;

    IF @id > 0 THEN
        SET @getStmnt = CONCAT('insert into ',localdb,'.updateadmin (admin, ladmin) select a.admin, a.admin from ',localdb,'.maxtable a');
	PREPARE stmnt1 FROM @getStmnt;
	EXECUTE stmnt1;
	DEALLOCATE PREPARE stmnt1;
    END IF;    
    
    SET @getStmnt = CONCAT('select a.refid INTO @id FROM ',localdb,'.maxtable a'); 
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;

    IF @id > 0 THEN
        SET @getStmnt = CONCAT('insert into ',localdb,'.updatebibref (refid, lrefid) select a.refid, a.refid from ',localdb,'.maxtable a');
	PREPARE stmnt1 FROM @getStmnt;
	EXECUTE stmnt1;
	DEALLOCATE PREPARE stmnt1;
    END IF;    
    
    SET @getStmnt = CONCAT('select a.lrecid INTO @id FROM ',localdb,'.maxtable a'); 
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;

    IF @id > 0 THEN
        SET @getStmnt = CONCAT('insert into ',localdb,'.updatelistdata (lrecid, llrecid) select a.lrecid, a.lrecid from ',localdb,'.maxtable a');
	PREPARE stmnt1 FROM @getStmnt;
	EXECUTE stmnt1;
	DEALLOCATE PREPARE stmnt1;
    END IF;    
    
    SET @getStmnt = CONCAT('select a.fldno INTO @id FROM ',localdb,'.maxtable a'); 
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;

    IF @id > 0 THEN
        SET @getStmnt = CONCAT('insert into ',localdb,'.updateudflds (fldno, lfldno) select a.fldno, a.fldno from ',localdb,'.maxtable a');
	PREPARE stmnt1 FROM @getStmnt;
	EXECUTE stmnt1;
	DEALLOCATE PREPARE stmnt1;
    END IF; 
    
    END$$

DELIMITER ;