create or replace
PROCEDURE YXQVALID
( WARN_DAYS IN NUMBER --提前提醒天数
, ADMIN_ID IN NUMBER --管理员ID
, TXNR1 IN VARCHAR2 --即将过期的提醒内容
, TXNR2 IN VARCHAR2 --已经过期的提醒内容
, TXNR3 IN VARCHAR2 --附证第一次年审的提醒内容
, TXNR4 IN VARCHAR2 --附证第二次年审的提醒内容
, BUS_TYPE_JYR IN VARCHAR2 --附证过期提醒类型
, BUS_TYPE_NS IN VARCHAR2 --年审提醒类型
, BUS_TYPE_ZC IN VARCHAR2 --自查提醒类型
, BUS_TYPE_DC IN VARCHAR2 --督查提醒类型
, BUS_TYPE_JYRZYCS_DC IN VARCHAR2 --经营人作业场所提醒类型
, ROLE_TYPE_DC IN VARCHAR2 --督查人员类型
, SZD IN NUMBER --所在地
) AS
BEGIN
  DECLARE
    strsql VARCHAR2(500); --执行语句
    JYR_ID NUMBER; --经营人ID
    JYR_BAFZRQ DATE; --经营人附证到期日期
    ZC_ID NUMBER; --自查ID
    DC_ID NUMBER; --督查ID
    GKJYR VARCHAR2(200); --港口经营人名称
    DCDW VARCHAR2(100); --港口经营人名称
    JCSJ VARCHAR2(20); --检查时间
    USER_ID NUMBER; --用户ID
    GZTX_SIZE NUMBER; --工作提醒条数
    TXNR VARCHAR2(500); --提醒内容
    CURSOR CUR_EXPIRED_FZ IS
      SELECT ID,BAFZRQ FROM T_YJPT_JYRJCXX WHERE BAFZRQ<TRUNC(SYSDATE, 'DD')-WARN_DAYS AND RECORD_STATUS<>9; --附证过期的经营人
    CURSOR CUR_NS_JYR_1 IS
     SELECT ID FROM T_YJPT_JYRJCXX jyr WHERE   jyr.RECORD_STATUS<>9    
   	AND  EXISTS (SELECT 1 FROM  T_YJPT_GKWXHWZYFZBLHHZ hz  WHERE hz.JYR=jyr.ID AND  hz.RECORD_STATUS =2 AND hz.FZSJ IS NOT NULL AND ADD_MONTHS(hz.FZSJ,13)>SYSDATE AND ADD_MONTHS(hz.FZSJ,12)<SYSDATE 
	AND HZ.BYZD1 NOT IN (SELECT NS.FZBH FROM T_YJPT_JYRXX_NSB NS WHERE  NS.JYR=jyr.ID AND NS.NSNS=1 AND NS.RECORD_STATUS<>9));--需要进行第一次年审的经营人
    CURSOR CUR_NS_JYR_2 IS
     SELECT ID FROM T_YJPT_JYRJCXX jyr WHERE   jyr.RECORD_STATUS<>9    
   	AND  EXISTS (SELECT 1 FROM  T_YJPT_GKWXHWZYFZBLHHZ hz  WHERE hz.JYR=jyr.ID AND  hz.RECORD_STATUS =2 AND hz.FZSJ IS NOT NULL AND ADD_MONTHS(hz.FZSJ,25)>SYSDATE AND ADD_MONTHS(hz.FZSJ,24)<SYSDATE 
	AND HZ.BYZD1 NOT IN (SELECT NS.FZBH FROM T_YJPT_JYRXX_NSB NS WHERE  NS.JYR=jyr.ID AND NS.NSNS=2AND NS.RECORD_STATUS<>9)); --需要进行第二次年审的经营人
    CURSOR CUR_EXPIRED_ZC IS
      SELECT zc.ID,jyr.ID,jyr.GKJYR,TO_CHAR(zc.JCSJ,'yyyy-mm-dd') FROM T_YJPT_ZCYH yh JOIN T_YJPT_ZC zc ON yh.SSZC= zc.ID JOIN T_YJPT_JYRJCXX jyr ON zc.QYMC=jyr.ID
      WHERE yh.JHWCSJ<=TRUNC(SYSDATE, 'DD') AND yh.MQZT='-1' AND yh.RECORD_STATUS<>9 AND zc.RECORD_STATUS<>9 AND jyr.RECORD_STATUS<>9; --到期的自查隐患
    CURSOR CUR_EXPIRED_DC IS
      SELECT dc.ID,jyr.ID,jyr.GKJYR,TO_CHAR(dc.SJ,'yyyy-mm-dd'),dc.DCDW FROM T_YJPT_DC dc JOIN T_YJPT_JYRJCXX jyr ON dc.BDCDW=jyr.ID
      WHERE dc.JHWCSJ<=TRUNC(SYSDATE, 'DD') AND dc.MQZT='-1' AND dc.RECORD_STATUS<>9 AND jyr.RECORD_STATUS<>9; --到期的督查
    CURSOR CUR_EXPIRED_ZYCS IS
      SELECT distinct jyr.ID,jyr.GKJYR FROM T_YJPT_DC dc JOIN T_YJPT_JYRJCXX jyr ON dc.BDCDW=jyr.ID
      WHERE dc.JHWCSJ<=TRUNC(SYSDATE, 'DD') AND dc.MQZT='-1' AND dc.RECORD_STATUS<>9 AND jyr.RECORD_STATUS<>9; --到期的督查
    CURSOR CUR_JYR_ADMIN(JYRID IN NUMBER) IS
      SELECT DISTINCT u.ID FROM T_YJPT_USER u WHERE RECORD_STATUS<>9 AND JYRRYXXID=JYRID AND EXISTS (SELECT 1 FROM T_YJPT_ROLE_USER ru WHERE ru.USERID= u.ID AND ru.ROLEID=2); --经营人管理员
    CURSOR CUR_ROLE_USER(R_TYPE IN VARCHAR2) IS
      SELECT DISTINCT u.ID FROM T_YJPT_USER u WHERE RECORD_STATUS<>9
      AND EXISTS (SELECT 1 FROM T_YJPT_ROLE_USER ru JOIN T_YJPT_ROLE r ON ru.ROLEID=r.ID WHERE ru.USERID= u.ID AND r.ROLE_TYPE=R_TYPE); --督查类型人员
    
  BEGIN
    --附证到期提醒
    OPEN CUR_EXPIRED_FZ;
      LOOP
        FETCH CUR_EXPIRED_FZ
          INTO JYR_ID,JYR_BAFZRQ;
        EXIT WHEN CUR_EXPIRED_FZ%NOTFOUND;
          OPEN CUR_JYR_ADMIN(JYR_ID);
            LOOP
              FETCH CUR_JYR_ADMIN
                INTO USER_ID;
              EXIT WHEN CUR_JYR_ADMIN%NOTFOUND;
                --附证到期提醒是否存在
                strsql := 'SELECT COUNT(*) FROM T_YJPT_GZTX WHERE BDID='||JYR_ID||' AND BUSTYPE='''||BUS_TYPE_JYR||''' AND JSR='||USER_ID||' AND TXZT=''Y''';
                --DBMS_OUTPUT.PUT_LINE(strsql);
                EXECUTE IMMEDIATE strsql into GZTX_SIZE;
                IF(GZTX_SIZE=0) THEN
                  IF(JYR_BAFZRQ<SYSDATE) THEN
                    TXNR := TXNR1;
                  ELSE
                    TXNR := TXNR2;
                  END IF;
                  --插入附证到期提醒
                  strsql := 'INSERT INTO T_YJPT_GZTX (ID,TXSJ,TXZT,TXNR,BDID,JSR,FSR,ADD_USER,ADD_DATE,RECORD_STATUS,BYZD5,BUSTYPE,SZD)'
                            ||'VALUES ('||SYS_SEQUENCE.nextval||',SYSDATE,''Y'','''||TXNR||''','||JYR_ID||','||USER_ID||','||ADMIN_ID||','||ADMIN_ID||',SYSDATE,1,''N'','''||BUS_TYPE_JYR||''','||SZD||')';
                  --DBMS_OUTPUT.PUT_LINE(strsql);
                  EXECUTE IMMEDIATE strsql;
                END IF;
            END LOOP;
          CLOSE CUR_JYR_ADMIN;
      END LOOP;
    CLOSE CUR_EXPIRED_FZ;
    COMMIT;
    
    --年审到期提醒
    OPEN CUR_NS_JYR_1;
      LOOP
        FETCH CUR_NS_JYR_1
          INTO JYR_ID;
        EXIT WHEN CUR_NS_JYR_1%NOTFOUND;
          OPEN CUR_JYR_ADMIN(JYR_ID);
            LOOP
              FETCH CUR_JYR_ADMIN
                INTO USER_ID;
              EXIT WHEN CUR_JYR_ADMIN%NOTFOUND;
                --第一次年审到期提醒是否存在
                strsql := 'SELECT COUNT(*) FROM T_YJPT_GZTX WHERE BDID='||JYR_ID||' AND BUSTYPE='''||BUS_TYPE_NS||''' AND TXNR='''||TXNR3||''' AND JSR='||USER_ID||' AND TXZT=''Y''';
                --DBMS_OUTPUT.PUT_LINE(strsql);
                EXECUTE IMMEDIATE strsql into GZTX_SIZE;
                IF(GZTX_SIZE=0) THEN
                  --插入第一次年审到期提醒
                  strsql := 'INSERT INTO T_YJPT_GZTX (ID,TXSJ,TXZT,TXNR,BDID,JSR,FSR,ADD_USER,ADD_DATE,RECORD_STATUS,BYZD5,BUSTYPE,SZD)'
                            ||'VALUES ('||SYS_SEQUENCE.nextval||',SYSDATE,''Y'','''||TXNR3||''','||JYR_ID||','||USER_ID||','||ADMIN_ID||','||ADMIN_ID||',SYSDATE,1,''N'','''||BUS_TYPE_NS||''','||SZD||')';
                  --DBMS_OUTPUT.PUT_LINE(strsql);
                  EXECUTE IMMEDIATE strsql;
                END IF;
            END LOOP;
          CLOSE CUR_JYR_ADMIN;
      END LOOP;
    CLOSE CUR_NS_JYR_1;
    OPEN CUR_NS_JYR_2;
      LOOP
        FETCH CUR_NS_JYR_2
          INTO JYR_ID;
        EXIT WHEN CUR_NS_JYR_2%NOTFOUND;
          OPEN CUR_JYR_ADMIN(JYR_ID);
            LOOP
              FETCH CUR_JYR_ADMIN
                INTO USER_ID;
              EXIT WHEN CUR_JYR_ADMIN%NOTFOUND;
                --第二次年审到期提醒是否存在
                strsql := 'SELECT COUNT(*) FROM T_YJPT_GZTX WHERE BDID='||JYR_ID||' AND BUSTYPE='''||BUS_TYPE_NS||''' AND TXNR='''||TXNR4||''' AND JSR='||USER_ID||' AND TXZT=''Y''';
                --DBMS_OUTPUT.PUT_LINE(strsql);
                EXECUTE IMMEDIATE strsql into GZTX_SIZE;
                IF(GZTX_SIZE=0) THEN
                  --插入第二次年审到期提醒
                  strsql := 'INSERT INTO T_YJPT_GZTX (ID,TXSJ,TXZT,TXNR,BDID,JSR,FSR,ADD_USER,ADD_DATE,RECORD_STATUS,BYZD5,BUSTYPE,SZD)'
                            ||'VALUES ('||SYS_SEQUENCE.nextval||',SYSDATE,''Y'','''||TXNR4||''','||JYR_ID||','||USER_ID||','||ADMIN_ID||','||ADMIN_ID||',SYSDATE,1,''N'','''||BUS_TYPE_NS||''','||SZD||')';
                  --DBMS_OUTPUT.PUT_LINE(strsql);
                  EXECUTE IMMEDIATE strsql;
                END IF;
            END LOOP;
          CLOSE CUR_JYR_ADMIN;
      END LOOP;
    CLOSE CUR_NS_JYR_2;
    COMMIT;
    
    --自查隐患到期提醒
    OPEN CUR_EXPIRED_ZC;
      LOOP
        FETCH CUR_EXPIRED_ZC
          INTO ZC_ID,JYR_ID,GKJYR,JCSJ;
        EXIT WHEN CUR_EXPIRED_ZC%NOTFOUND;
          OPEN CUR_JYR_ADMIN(JYR_ID);
            LOOP
              FETCH CUR_JYR_ADMIN
                INTO USER_ID;
              EXIT WHEN CUR_JYR_ADMIN%NOTFOUND;
                --自查隐患到期提醒是否存在
                strsql := 'SELECT COUNT(*) FROM T_YJPT_GZTX WHERE BDID='||ZC_ID||' AND BUSTYPE='''||BUS_TYPE_ZC||''' AND JSR='||USER_ID||' AND TXZT=''Y''';
                --DBMS_OUTPUT.PUT_LINE(strsql);
                EXECUTE IMMEDIATE strsql into GZTX_SIZE;
                IF(GZTX_SIZE=0) THEN
                  --插入自查隐患到期提醒
                  strsql := 'INSERT INTO T_YJPT_GZTX (ID,TXSJ,TXZT,TXNR,BDID,JSR,FSR,ADD_USER,ADD_DATE,RECORD_STATUS,BYZD5,BUSTYPE,SZD)'
                            ||'VALUES ('||SYS_SEQUENCE.nextval||',SYSDATE,''Y'','''||GKJYR||'于'||JCSJ||'查出的隐患未整改'','
                            ||ZC_ID||','||USER_ID||','||ADMIN_ID||','||ADMIN_ID||',SYSDATE,1,''N'','''||BUS_TYPE_ZC||''','||SZD||')';
                  --DBMS_OUTPUT.PUT_LINE(strsql);
                  EXECUTE IMMEDIATE strsql;
                END IF;
            END LOOP;
          CLOSE CUR_JYR_ADMIN;
      END LOOP;
    CLOSE CUR_EXPIRED_ZC;
    COMMIT;
    
    --督查隐患到期提醒
    OPEN CUR_EXPIRED_DC;
      LOOP
        FETCH CUR_EXPIRED_DC
          INTO DC_ID,JYR_ID,GKJYR,JCSJ,DCDW;
        EXIT WHEN CUR_EXPIRED_DC%NOTFOUND;
          --删除督查隐患到期提醒
          strsql := 'DELETE FROM T_YJPT_GZTX WHERE BDID='||DC_ID||' AND BUSTYPE='''||BUS_TYPE_DC||''' AND TXZT=''Y''';
          --DBMS_OUTPUT.PUT_LINE(strsql);
          EXECUTE IMMEDIATE strsql;
          OPEN CUR_JYR_ADMIN(JYR_ID);
            LOOP
              FETCH CUR_JYR_ADMIN
                INTO USER_ID;
              EXIT WHEN CUR_JYR_ADMIN%NOTFOUND;
                --插入督查隐患到期提醒
                strsql := 'INSERT INTO T_YJPT_GZTX (ID,TXSJ,TXZT,TXNR,BDID,JSR,FSR,ADD_USER,ADD_DATE,RECORD_STATUS,BYZD5,BUSTYPE,SZD)'
                          ||'VALUES ('||SYS_SEQUENCE.nextval||',SYSDATE,''Y'','''||DCDW||'于'||JCSJ||'对'||GKJYR||'进行监督检查，所查隐患记录未整改'','
                          ||DC_ID||','||USER_ID||','||ADMIN_ID||','||ADMIN_ID||',SYSDATE,1,''N'','''||BUS_TYPE_DC||''','||SZD||')';
                --DBMS_OUTPUT.PUT_LINE(strsql);
                EXECUTE IMMEDIATE strsql;
            END LOOP;
          CLOSE CUR_JYR_ADMIN;
      END LOOP;
    CLOSE CUR_EXPIRED_DC;
    COMMIT;
    
    --禁止作业-督查隐患
    OPEN CUR_EXPIRED_ZYCS;
      LOOP
        FETCH CUR_EXPIRED_ZYCS
          INTO JYR_ID,GKJYR;
        EXIT WHEN CUR_EXPIRED_ZYCS%NOTFOUND;
          --删除禁止作业-督查隐患提醒
          strsql := 'DELETE FROM T_YJPT_GZTX WHERE BDID='||JYR_ID||' AND BUSTYPE='''||BUS_TYPE_JYRZYCS_DC||''' AND TXZT=''Y''';
          --DBMS_OUTPUT.PUT_LINE(strsql);
          EXECUTE IMMEDIATE strsql;
          OPEN CUR_ROLE_USER(ROLE_TYPE_DC);
            LOOP
              FETCH CUR_ROLE_USER
                INTO USER_ID;
              EXIT WHEN CUR_ROLE_USER%NOTFOUND;
                --插入禁止作业-督查隐患提醒
                strsql := 'INSERT INTO T_YJPT_GZTX (ID,TXSJ,TXZT,TXNR,BDID,JSR,FSR,ADD_USER,ADD_DATE,RECORD_STATUS,BYZD5,BUSTYPE,SZD)'
                          ||'VALUES ('||SYS_SEQUENCE.nextval||',SYSDATE,''Y'','''||GKJYR||'的企业隐患在限期整改'||TO_CHAR(SYSDATE,'yyyy-mm-dd')||'前未完成，请设置该企业的作业场所禁止进行危货作业申报'','
                          ||JYR_ID||','||USER_ID||','||ADMIN_ID||','||ADMIN_ID||',SYSDATE,1,''N'','''||BUS_TYPE_JYRZYCS_DC||''','||SZD||')';
                --DBMS_OUTPUT.PUT_LINE(strsql);
                EXECUTE IMMEDIATE strsql;
            END LOOP;
          CLOSE CUR_ROLE_USER;
      END LOOP;
    CLOSE CUR_EXPIRED_ZYCS;
    COMMIT;
    
  END;
END YXQVALID;