DELIMITER $$


DROP PROCEDURE IF EXISTS `appendLocalGMS`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `appendLocalGMS`(IN localdb VARCHAR(50), IN centraldb VARCHAR(50))
BEGIN
    
    SET @getStmnt = CONCAT(
        'insert into ',centraldb,'.location (LOCID, LTYPE, NLLP, LNAME, LABBR, SNL3ID, SNL2ID, SNL1ID, CNTRYID, LRPLCE)
		select distinct a.LOCID, a.LTYPE, a.NLLP, a.LNAME, a.LABBR, a.SNL3ID, a.SNL2ID, a.SNL1ID, a.CNTRYID, a.LRPLCE
		from ',localdb,'.location a where (a.locid>0)');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
        'insert into ',centraldb,'.locdes (LOCID, DTYPE, DUID, DVAL, DDATE, DREF) 
		select distinct a.LOCID, a.DTYPE, a.DUID, a.DVAL, a.DDATE, a.DREF
		from ',localdb,'.locdes a');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
        'insert into ',centraldb,'.names (GID, NTYPE, NSTAT, NUID, NVAL, NLOCN, NDATE, NREF)
		select distinct a.GID, a.NTYPE, a.NSTAT, a.NUID, a.NVAL, a.NLOCN, a.NDATE, a.NREF
		from ',localdb,'.names a where (a.gid>0)');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
        'insert into ',centraldb,'.atributs (GID, ATYPE, AUID, AVAL, ALOCN, AREF, ADATE)
		select distinct a.GID, a.ATYPE, a.AUID, a.AVAL, a.ALOCN, a.AREF, a.ADATE
		from ',localdb,'.atributs a 
		where (((a.gid>0) and (a.atype<>101) and (a.atype<>102)))
		order by a.aid desc');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
        'insert into ',centraldb,'.germplsm (GID, METHN, GNPGS, GPID1, GPID2, GERMUID, LGID, GLOCN, GDATE, GREF, GRPLCE) 
		select distinct a.GID, a.METHN, a.GNPGS, a.GPID1, a.GPID2, a.GERMUID, a.LGID, a.GLOCN, a.GDATE, a.GREF, a.GRPLCE from ',localdb,'.germplsm a where (a.gid>0) and (a.grplce=0)');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
        'insert into ',centraldb,'.progntrs (GID, PNO, PID) 
		select distinct a.GID, a.PNO, a.PID
		from ',localdb,'.progntrs a where (a.gid>0) and (a.pid>0)');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
        'insert into ',centraldb,'.listdata (LISTID, GID, ENTRYID, ENTRYCD, SOURCE, DESIG, GRPNAME, LRECID, LRSTATUS)
		select distinct a.LISTID, a.GID, a.ENTRYID, a.ENTRYCD, a.SOURCE, a.DESIG, a.GRPNAME, a.LRECID, a.LRSTATUS
		from ',localdb,'.listdata a where (a.gid>0)');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
        'insert into ',centraldb,'.listnms (LISTID, LISTNAME, LISTDATE, LISTTYPE, listuid, LISTDESC, LHIERARCHY, LISTSTATUS)
		select distinct a.LISTID, a.LISTNAME, a.LISTDATE, a.LISTTYPE, a.listuid, a.LISTDESC, a.LHIERARCHY, a.LISTSTATUS
		from ',localdb,'.listnms a where (a.listid>0)');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
        'insert into ',centraldb,'.instln (INSTALID, ADMIN, UDATE, UGID, ULOCN, UCID, UNID, UAID, ULDID, umethn, ufldno, UREFNO, UPID, ULISTID, ULRECID, IDESC, DMS_STATUS) 
		select distinct a.INSTALID, a.ADMIN, a.UDATE, a.UGID, a.ULOCN, a.UCID, a.UNID, a.UAID, a.ULDID, a.umethn, a.ufldno, a.UREFNO, a.UPID, a.ULISTID, a.ULRECID, a.IDESC, a.DMS_STATUS
		from ',localdb,'.instln a where a.instalid>0');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
        'insert into ',centraldb,'.users (userid, instalid, ustatus, uaccess, utype, UNAME, UPSWD, PERSONID, ADATE, CDATE)
		select distinct a.userid, a.instalid, a.ustatus, a.uaccess, a.utype, a.UNAME, a.UPSWD, a.PERSONID, a.ADATE, a.CDATE
		from ',localdb,'.users a where a.userid>0');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
        'update ',localdb,'.changes a, ',localdb,'.updategid b
		set a.cto = b.gid
		where (a.cto=b.lgid)
			and ((a.cfield="gid") or (a.cfield="gpid1") or (a.cfield="gpid2") or (a.cfield="pid"))
			and ((a.crecord>0) and (a.cstatus=0))');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
        'update ',localdb,'.changes a inner join ',centraldb,'.germplsm b on a.crecord = b.gid
		set b.grplce = a.cto, a.cstatus = 2
		where ((a.ctable="germplsm") and (a.cfield="gid") and (a.cto>0) and (b.grplce=0))');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
        'update ',localdb,'.changes a inner join ',centraldb,'.germplsm b on a.crecord = b.gid 
		set b.grplce = a.crecord, a.cstatus = 2
		where ((b.grplce=0) and (a.ctable="germplsm") and (a.cfield="gid") and (a.cto=0))');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
        'update ',localdb,'.changes a inner join ',centraldb,'.germplsm b on a.crecord = b.gid
		set b.gpid1 = a.cto, a.cstatus = 2
		where ((a.cstatus=0) and (b.gpid1=a.cfrom) and (a.ctable="germplsm") and (a.cfield="gpid1") and (a.cto>=0))');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
        'update ',localdb,'.changes a inner join ',centraldb,'.germplsm b on a.crecord = b.gid 
		set b.gpid2 = a.cto, a.cstatus = 2
		where ((a.cstatus=0) and (b.gpid2=a.cfrom) and (a.ctable="germplsm") and (a.cfield="gpid2") and (a.cto>=0))');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
        'update ',localdb,'.changes a inner join ',centraldb,'.germplsm b on a.crecord = b.gid
		set b.gdate = a.cto, a.cstatus = 2
		where ((a.cstatus=0) and (b.gdate=a.cfrom) and (a.ctable="germplsm") and (a.cfield="gdate") and (a.cto>=0))');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
        'update ',localdb,'.changes a inner join ',centraldb,'.germplsm b on a.crecord = b.gid
		set b.glocn = a.cto, a.cstatus = 2
		where ((a.cstatus=0) and (b.glocn=a.cfrom) and (a.ctable="germplsm") and (a.cfield="glocn") and (a.cto>=0))');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
        'update ',localdb,'.changes a inner join ',centraldb,'.germplsm b on a.crecord = b.gid
		set b.methn = a.cto, a.cstatus = 2
		where ((a.cstatus=0) and (b.methn=a.cfrom) and (a.ctable="germplsm") and (a.cfield="methn") and (a.cto>=0))');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
        'update ',localdb,'.changes a inner join ',centraldb,'.germplsm b on a.crecord = b.gid
		set b.gnpgs = a.cto, a.cstatus = 2
		where ((a.cstatus=0) and (b.gnpgs=a.cfrom) and (a.ctable="germplsm") and (a.cfield="gnpgs") and (a.cto>=-1))');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
        'update ',localdb,'.changes a inner join ',centraldb,'.names b on a.crecord = b.nid
		set b.nstat = a.cto, a.cstatus = 2
		where ((a.cstatus=0) and (b.nstat=a.cfrom) and (a.ctable="names") and (a.cfield="nstat"))');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
        'update ',localdb,'.changes a inner join ',centraldb,'.names b on a.crecord = b.nid
		set b.ntype = a.cto, a.cstatus = 2
		where ((a.cstatus=0) and (b.ndate=a.cfrom) and (a.ctable="names") and (a.cfield="ntype"))');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;

    SET @getStmnt = CONCAT(
        'update ',localdb,'.changes a inner join ',centraldb,'.names b on a.crecord = b.nid
		set b.nlocn = a.cto, a.cstatus = 2
		where ((a.cstatus=0) and (b.nlocn=a.cfrom) and (a.ctable="names") and (a.cfield="nlocn"))');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
        'update ',localdb,'.changes a inner join ',centraldb,'.atributs b on a.crecord = b.aid
		set b.atype = a.cto, a.cstatus = 2
		where ((b.atype=a.cfrom) and (a.cstatus=0) and (a.ctable="atributs") and (a.cfield="atype"))');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
        'update ',localdb,'.changes a inner join ',centraldb,'.atributs b on a.crecord = b.aid
		set b.alocn = a.cto, a.cstatus = 2
		where ((b.alocn=a.cfrom) and (a.cstatus=0) and (a.ctable="atributs") and (a.cfield="alocn"))');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
        'insert into ',localdb,'.updatechng (lcid) 
		select a.cid from ',localdb,'.changes a 
		where ((cstatus=2) and (crecord>0)) 
		order by cid desc');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
        'update ',localdb,'.changes a inner join ',localdb,'.updatechng b on a.cid = b.lcid 
		set a.cid = b.cid, a.cstatus = b.lcid');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
        'insert into ',centraldb,'.changes (CID, CTABLE, CFIELD, CRECORD, CFROM, CTO, CDATE, CTIME, CGROUP, CREF, CSTATUS, CDESC)
		select distinct a.CID, a.CTABLE, a.CFIELD, a.CRECORD, a.CFROM, a.CTO, a.CDATE, a.CTIME, a.CGROUP, a.CREF, a.CSTATUS, a.CDESC
		from ',localdb,'.changes a where (a.cid>0)');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    set @getStmnt = CONCAT(
        'select a.gid, b.nval, a.methn, a.gnpgs, a.gpid1, a.gpid2, a.grplce, a.glocn, b.nlocn, a.gdate, b.ndate, a.gref, b.nref
		from ',centraldb,'.germplsm a, ',centraldb,'.names b
		where ((a.grplce=0) and (b.nstat=1))
		order by a.gid');
    PREPARE stmnt1 from @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;

    set @getStmnt = CONCAT(
        'select a.gid, b.nval, a.methn, a.gnpgs, a.gpid1, a.gpid2, a.grplce, a.glocn, b.nlocn, a.gdate, b.ndate, a.gref, b.nref, a.mgid
		from ',localdb,'.germplsm a
		inner join ',localdb,'.names b ON a.gid = b.gid
		where ((a.grplce=0) and (b.nstat=1))
		order by a.gid');
    PREPARE stmnt1 from @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    

END$$

DELIMITER ;