DELIMITER $$


DROP PROCEDURE IF EXISTS `chkLocalGMS`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `chkLocalGMS`(IN localdb VARCHAR(50), IN centraldb VARCHAR(50))
BEGIN

    SET @getStmnt = CONCAT(
	'select a.gid, a.methn, b.mtype, a.gnpgs, a.grplce 
		from ',localdb,'.germplsm a inner join ',centraldb,'.methods b on a.gnpgs = b.mid
		where ((a.gnpgs<>-1) and (b.mtype="DER"))');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
	'select a.gid, a.methn, b.mtype, a.gnpgs, a.grplce
		from ',localdb,'.germplsm a inner join ',centraldb,'.methods b on a.methn = b.mid
		where ((b.mtype="GEN") and (a.gnpgs<0))');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
	'select a.gid from ',localdb,'.germplsm a where (a.gid=a.gpid1)');    
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
	'select a.gid from ',localdb,'.germplsm a where (a.gid=a.gpid2)');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
	'select a.gid, a.gnpgs, a.gpid1, a.gpid2 
		from ',localdb,'.germplsm a 
		where ((a.gnpgs=-1) and (a.gpid1=0) and (a.gpid2<>0))');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
	'select a.gid, a.gpid1, a.gpid2
		from ',localdb,'.germplsm a
		where ((a.gnpgs=-1) and (a.grplce=0))');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
	'select CHK01F1.gid, a.gid, a.gpid1, a.gpid2
		from (select a.gid, a.gpid1, a.gpid2 from ',localdb,'.germplsm a where ((a.gnpgs=-1) and (a.grplce=0))) CHK01F1 
		inner join ',localdb,'.germplsm a on CHK01F1.gpid2=a.gid
		where (CHK01F1.gid=a.gid)');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
	'select CHK01F1.gid, a.gid, a.gpid1, a.gpid2
		from (select a.gid, a.gpid1, a.gpid2 
			from ',localdb,'.germplsm a 
			where ((a.gnpgs=-1) and (a.grplce=0))) CHK01F1 
		inner join ',localdb,'.germplsm a on CHK01F1.gpid2=a.gid');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
	'select CHK01F3.gid, a.gid AS germplasm_gid, a.gpid1, a.gpid2 
		from (select CHK01F1.gid, a.gid AS germplasm_gid, a.gpid1, a.gpid2
			from (select a.gid, a.gpid1, a.gpid2 
				from ',localdb,'.germplsm a 
				where (a.gnpgs=-1) and (a.grplce=0)) CHK01F1 
			inner join ',localdb,'.germplsm a on CHK01F1.gpid2=a.gid
		) CHK01F3 
		inner join ',localdb,'.germplsm a on CHK01F3.gpid2=a.gid
		where (CHK01F3.gid = a.gid)');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
	'select a.gid, b.gid, a.grplce
		from ',localdb,'.germplsm a  
		inner join ',centraldb,'.germplsm b on a.gpid1=b.gid
		where (b.grplce<>0)');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
	'select a.gid, b.gid, b.grplce
		from ',localdb,'.germplsm a
		inner join ',centraldb,'.germplsm b on a.gpid2=b.gid
		where (b.grplce<>0)');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
	'select a.ctable, a.cfield, b.grplce, a.cto, a.cstatus
		from ',localdb,'.changes a
		inner join ',centraldb,'.germplsm b on a.crecord=b.gid
		where ((a.ctable="germplsm") 
			and (a.cfield="gid") 
			and (b.grplce<>0) 
			and (a.cstatus=0))');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
	'select a.ctable, a.cfield, a.cto, a.cstatus 
		from ',localdb,'.changes a
		inner join ',localdb,'.germplsm b on a.crecord=b.gid
		where ((a.ctable="germplsm") 
			and (a.cfield="gid")
			and (a.cto<>b.grplce) 
			and (a.cstatus=0))');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
	'select a.ctable, a.cfield, a.cto, a.cstatus
		from ',localdb,'.changes a
		inner join ',localdb,'.germplsm b on a.crecord=b.gid
		where ((a.ctable="germplsm") 
			and (a.cfield="gpid1")
			and (a.cto<>b.gpid1)
			and (a.cstatus=0))');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
	'select a.ctable, a.cfield, a.cto, a.cstatus
		from ',localdb,'.changes a
		inner join ',localdb,'.germplsm b on a.crecord=b.gid
		where ((a.ctable="germplsm")
			and (a.cfield="gpid2")
			and (a.cto<>b.gpid2)
			and (a.cstatus=0))');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
	'select a.ctable, a.cfield, a.cto, a.cstatus
		from ',localdb,'.changes a
		inner join ',localdb,'.germplsm b on a.crecord=b.gid
		where a.ctable="germplsm" 
			and a.cfield="gdate"
			and a.cto<>b.gdate
			and a.cstatus=0');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
	'select a.ctable, a.cfield, a.cto, a.cstatus
		from ',localdb,'.changes a
		inner join ',localdb,'.germplsm b on a.crecord=b.gid
		where ((a.ctable="germplsm")
			and (a.cfield="gdate")
			and (a.cto<>b.gdate)
			and (a.cstatus=0))');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
	'select a.ctable, a.cfield, a.cto, a.cstatus
		from ',localdb,'.changes a
		inner join ',localdb,'.germplsm b on a.crecord=b.gid
		where ((a.ctable="germplsm")
			and (a.cfield="methn")
			and (a.cto<>b.methn)
			and (a.cstatus=0))');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
	'select a.ctable, a.cfield, a.cto, a.cstatus
		from ',localdb,'.changes a
		inner join ',localdb,'.germplsm b on a.crecord=b.gid
		where ((a.ctable="germplsm")
			and (a.cfield="gnpgs")
			and (a.cto<>b.gnpgs)
			and (a.cstatus=0))');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
	'select a.ctable, a.cfield, a.cto, a.cstatus
		from ',localdb,'.changes a
		inner join ',localdb,'.germplsm b on a.crecord=b.gid
		where ((a.ctable="germplsm")
			and (a.cfield="gref")
			and (a.cto<>b.gref)
			and (a.cstatus=0))');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
	'select a.gid, a.gpid1, a.mgid, b.gpid1, b.grplce
		from ',localdb,'.germplsm a 
		inner join ',centraldb,'.germplsm b on a.mgid = b.gid
		where ((a.grplce=0)
			and (a.gpid1<>b.gpid1)
			and (b.gpid1<>0)
			and (b.gnpgs=-1))');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
	'select a.gid, a.gpid1, a.mgid, b.gpid1, b.grplce
		from ',localdb,'.germplsm a 
		inner join ',centraldb,'.germplsm b on a.mgid = b.gid
		where ((a.grplce=0)
			and (a.gpid1<>b.gid)
			and (b.gpid1=0)
			and (b.gnpgs=-1))'); 
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
	'select a.gid, a.gpid1, a.mgid, b.gnpgs, b.grplce
		from ',localdb,'.germplsm a 
		inner join ',centraldb,'.germplsm b on a.mgid = b.gid
		where ((a.grplce=0)
			and (a.gpid1<>b.gid)
			and (b.gnpgs>0))');    
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
	
    END$$

DELIMITER ;