
/*
 * 若通过交换同步组织机构、帐号数据的，须执行该数据库脚本
 */

use user;

/*

delete from TB_B_ACCOUNT_ORGANIZATION where ADD_ACCOUNT='trans';

delete from TB_B_SAFETY where ADD_ACCOUNT='trans';
delete from TB_B_ACCOUNT where ADD_ACCOUNT='trans';
delete from TB_B_USER where ADD_ACCOUNT='trans';

delete from TMP_ACCOUNT_TRANS;

update TMP_ACCOUNT_ORIGIN set UID=UID;
*/


DROP TRIGGER IF EXISTS after_update_organization_origin;

delimiter //
create trigger after_update_organization_origin after update on TMP_ORGANIZATION_ORIGIN for each row
begin
  declare ID1 varchar(100);
  declare ID2 varchar(100);

  -- new 代表 表中新增的数据
  set ID1 = (select ID from TMP_ORGANIZATION_TRANS
  where ((ID is null and new.ID is null) or ID=new.ID)
    and ((PARENT_ORGANIZATION_ID is null and new.PARENT_ORGANIZATION_ID is null) or PARENT_ORGANIZATION_ID=new.PARENT_ORGANIZATION_ID)
    and ((CODE is null and new.CODE is null) or CODE=new.CODE)
    and ((NAME is null and new.NAME is null) or NAME=new.NAME)
    and ((DESCRIPTION is null and new.DESCRIPTION is null) or DESCRIPTION=new.DESCRIPTION)
    and ((TYPE_ID is null and new.TYPE_ID is null) or TYPE_ID=new.TYPE_ID)
    and ((STATE is null and new.STATE is null) or STATE=new.STATE)
    and ((ENABLE is null and new.ENABLE is null) or ENABLE=new.ENABLE)
    and ((IS_DATA_CENTER is null and new.IS_DATA_CENTER is null) or IS_DATA_CENTER=new.IS_DATA_CENTER)
  );
  -- into @ID1;

  if ID1 is null then
      set ID2 = (select ID from TMP_ORGANIZATION_TRANS where ID=new.ID); -- into @ID2;

      if ID2 is null then
        insert into TMP_ORGANIZATION_TRANS(TRANS_STATUS, TRANS_TIME, PROC_STATUS, PROC_TIME,
          ID, PARENT_ORGANIZATION_ID,
          CODE, NAME, DESCRIPTION,
          TYPE_ID, 
          STATE, ENABLE, 
          IS_DATA_CENTER
        )
        values ('1', now(), '0', null,
          new.ID, new.PARENT_ORGANIZATION_ID,
          new.CODE, new.NAME, new.DESCRIPTION,
          new.TYPE_ID, 
          new.STATE, new.ENABLE, 
          new.IS_DATA_CENTER
        )
        ;

      else
        update TMP_ORGANIZATION_TRANS set
          TRANS_STATUS='2',
          TRANS_TIME=now(),
          PROC_STATUS='0',
          PARENT_ORGANIZATION_ID=new.PARENT_ORGANIZATION_ID,
          CODE=new.CODE,
          NAME=new.NAME,
          DESCRIPTION=new.DESCRIPTION,
          TYPE_ID=new.TYPE_ID,
          STATE=new.STATE,
          ENABLE=new.ENABLE,
          IS_DATA_CENTER=new.IS_DATA_CENTER
        where ID=new.ID
        ;

      end if;

    else

      -- 如果数据没变化，但存在记录，且被处理，则标记未 不更新、不处理
      update TMP_ORGANIZATION_TRANS set
        TRANS_STATUS='0',
        TRANS_TIME=now(),
        PROC_STATUS='0'
      where ID=new.ID
        and PROC_RESULT!='0'
      ;

    end if;

end //
delimiter ;


DROP TRIGGER IF EXISTS after_update_account_origin;

delimiter //
create trigger after_update_account_origin after update on TMP_ACCOUNT_ORIGIN for each row
begin
  declare ID1 varchar(100);
  declare ID2 varchar(100);

  -- new 代表 表中新增的数据
  set ID1 = (select ID from TMP_ACCOUNT_TRANS
  where ((ID is null and new.ID is null) or ID=new.ID)
    and ((UID is null and new.UID is null) or UID=new.UID)
    and ((NAME is null and new.NAME is null) or NAME=new.NAME)
    and ((NAME_SPELLING is null and new.NAME_SPELLING is null) or NAME_SPELLING=new.NAME_SPELLING)
    and ((FULL_NAME_SPELLING is null and new.FULL_NAME_SPELLING is null) or FULL_NAME_SPELLING=new.FULL_NAME_SPELLING)
    and ((CERTIFICATE_TYPE_ID is null and new.CERTIFICATE_TYPE_ID is null) or CERTIFICATE_TYPE_ID=new.CERTIFICATE_TYPE_ID)
    and ((CERTIFICATE_NUMBER is null and new.CERTIFICATE_NUMBER is null) or CERTIFICATE_NUMBER=new.CERTIFICATE_NUMBER)
    and ((PHONE_NUMBER is null and new.PHONE_NUMBER is null) or PHONE_NUMBER=new.PHONE_NUMBER)
    and ((EMAIL is null and new.EMAIL is null) or EMAIL=new.EMAIL)
    and ((IMAGE_URL is null and new.IMAGE_URL is null) or IMAGE_URL=new.IMAGE_URL)
    and ((GENDER_ID is null and new.GENDER_ID is null) or GENDER_ID=new.GENDER_ID)
    and ((NATION_ID is null and new.NATION_ID is null) or NATION_ID=new.NATION_ID)
    and ((COUNTRY_ID is null and new.COUNTRY_ID is null) or COUNTRY_ID=new.COUNTRY_ID)
    and ((ADDRESS_ID is null and new.ADDRESS_ID is null) or ADDRESS_ID=new.ADDRESS_ID)
    and ((ACCOUNT_NAME is null and new.ACCOUNT_NAME is null) or ACCOUNT_NAME=new.ACCOUNT_NAME)
    and ((ACCOUNT_EXPIRY_DATE is null and new.ACCOUNT_EXPIRY_DATE is null) or ACCOUNT_EXPIRY_DATE=new.ACCOUNT_EXPIRY_DATE)
    and ((ORGANIZATION_ID is null and new.ORGANIZATION_ID is null) or ORGANIZATION_ID=new.ORGANIZATION_ID)
    and ((IDENTITY_TYPE_ID is null and new.IDENTITY_TYPE_ID is null) or IDENTITY_TYPE_ID=new.IDENTITY_TYPE_ID)
    and ((ACTIVATION is null and new.ACTIVATION is null) or ACTIVATION=new.ACTIVATION)
    and ((STATE is null and new.STATE is null) or STATE=new.STATE)
    and ((IS_DATA_CENTER is null and new.IS_DATA_CENTER is null) or IS_DATA_CENTER=new.IS_DATA_CENTER)
  );
  -- into @ID1;

  if ID1 is null then
      set ID2 = (select ID from TMP_ACCOUNT_TRANS where ID=new.ID); -- into @ID2;

      if ID2 is null then
        insert into TMP_ACCOUNT_TRANS(TRANS_STATUS, TRANS_TIME, PROC_STATUS, PROC_TIME,
          ID, UID, 
          NAME, NAME_SPELLING, FULL_NAME_SPELLING,
          CERTIFICATE_TYPE_ID, CERTIFICATE_NUMBER, 
          PHONE_NUMBER, EMAIL, 
          IMAGE_URL,
          GENDER_ID, NATION_ID, COUNTRY_ID, ADDRESS_ID, 
          ACCOUNT_NAME, ACCOUNT_EXPIRY_DATE, ORGANIZATION_ID, IDENTITY_TYPE_ID, 
          ACTIVATION, STATE, 
          IS_DATA_CENTER
        )
        values ('1', now(), '0', null,
          new.ID, new.UID, 
          new.NAME, new.NAME_SPELLING, new.FULL_NAME_SPELLING,
          new.CERTIFICATE_TYPE_ID, new.CERTIFICATE_NUMBER, 
          new.PHONE_NUMBER, new.EMAIL, 
          new.IMAGE_URL, 
          new.GENDER_ID, new.NATION_ID, new.COUNTRY_ID, new.ADDRESS_ID, 
          new.ACCOUNT_NAME, new.ACCOUNT_EXPIRY_DATE, new.ORGANIZATION_ID, new.IDENTITY_TYPE_ID, 
          new.ACTIVATION, new.STATE, 
          new.IS_DATA_CENTER
        )
        ;

      else
        update TMP_ACCOUNT_TRANS set
          TRANS_STATUS='2',
          TRANS_TIME=now(),
          PROC_STATUS='0',
          UID=new.UID,
          NAME=new.NAME,
          NAME_SPELLING=new.NAME_SPELLING,
          FULL_NAME_SPELLING=new.FULL_NAME_SPELLING,
          CERTIFICATE_TYPE_ID=new.CERTIFICATE_TYPE_ID,
          CERTIFICATE_NUMBER=new.CERTIFICATE_NUMBER,
          PHONE_NUMBER=new.PHONE_NUMBER,
          EMAIL=new.EMAIL,
          IMAGE_URL=new.IMAGE_URL,
          GENDER_ID=new.GENDER_ID,
          NATION_ID=new.NATION_ID,
          COUNTRY_ID=new.COUNTRY_ID,
          ADDRESS_ID=new.ADDRESS_ID,
          ACCOUNT_NAME=new.ACCOUNT_NAME,
          ACCOUNT_EXPIRY_DATE=new.ACCOUNT_EXPIRY_DATE,
          ORGANIZATION_ID=new.ORGANIZATION_ID,
          IDENTITY_TYPE_ID=new.IDENTITY_TYPE_ID,
          ACTIVATION=new.ACTIVATION,
          STATE=new.STATE,
          IS_DATA_CENTER=new.IS_DATA_CENTER
        where ID=new.ID
        ;

      end if;

    else

      -- 如果数据没变化，但存在记录，且被处理，则标记未 不更新、不处理
      update TMP_ACCOUNT_TRANS set
        TRANS_STATUS='0',
        TRANS_TIME=now(),
        PROC_STATUS='0'
      where ID=new.ID
        and PROC_RESULT!='0'
      ;

    end if;

end //
delimiter ;