员工卡PL/SQL

CREATE OR REPLACE PROCEDURE TO_IODATAMRC IS
in_t VARCHAR2(8);
out_t VARCHAR2(8);
tmp_date VARCHAR2(8);
num integer;
tmp_empno VARCHAR2(8);

 

CURSOR Cur1(THISDATE VARCHAR2 default TO_CHAR(trunc(sysdate))) IS

SELECT A.staffno,A."DataDate",(case when B.outtime is not null then B.outtime else A.outtime end) outtime,intime  FROM (
SELECT     substr("StaffNo",instr("StaffNo",'R')+1,7) StaffNo,
               "DataDate",
      max("DataTime") outtime,
        min("DataTime") intime
    FROM     IODataDetail@SQLSERVER
    WHERE   "ControllerNo"
      in ('27','28','29','31')
        and to_number("DataDate")=THISDATE
      and "DataTime">'060000'
        and "StaffNo"
      is not null          
       GROUP BY   substr("StaffNo",instr("StaffNo",'R')+1,7),"DataDate" ) A LEFT JOIN (
   
   
    SELECT     substr("StaffNo",instr("StaffNo",'R')+1,7) StaffNo,
               "DataDate",
        min("DataTime") outtime
    FROM     IODataDetail@SQLSERVER
    WHERE   "ControllerNo"
      in ('27','28','29','31')
        and to_number("DataDate")=to_char(to_date(THISDATE,'yyyymmdd')+1,'yyyymmdd')
      and "DataTime"<'060000'
        and "StaffNo"
      is not null          
       GROUP BY   substr("StaffNo",instr("StaffNo",'R')+1,7),"DataDate" ) B ON A.STAFFNO=B.STAFFNO;
   
CURSOR CurCD(THISDATE2 VARCHAR2 default TO_CHAR(trunc(sysdate),'yyyymmdd')) IS

select x.empno,x.intime,(case when y.outtime is not null then y.outtime else x.outtime end) outtime from (

 select * from (select a.outtime,
          a.intime,
    b."emploID" EMPNO
   from
   (select max("dtGetID") outtime,
           min("dtGetID") intime,
     "cardID" 
   from    accessevent@mcdsql
      where   to_char("dtGetID",'yyyymmdd')=THISDATE2
   and to_char("dtGetID",'hh24:mi:ss')>'060000'
   group by "cardID") a
               left join
   (select "emploID",
           "cardID"
   from      employe@mcdsql) b 
   on       a."cardID"=b."cardID") ) x left join (

  
   select * from (select a.outtime,
    b."emploID" EMPNO
   from
   (select max("dtGetID") outtime,          
     "cardID"
   from    accessevent@mcdsql
      where   to_char("dtGetID",'yyyymmdd')=to_char(to_date(THISDATE2,'yyyymmdd')+1,'yyyymmdd')
   and to_char("dtGetID",'hh24:mi:ss')<'060000'
   group by "cardID") a
               left join
   (select "emploID",
           "cardID"
   from      employe@mcdsql) b
   on       a."cardID"=b."cardID") ) y on x.empno=y.empno ;
    

begin
   FOR NUM IN 0..1 LOOP

   DELETE
   FROM        IOData_mrs 
   WHERE       flag='1'
   AND         TO_CHAR(IODate,'yyyymmdd') =
         TO_CHAR(SYSDATE-NUM,'yyyymmdd');
    
               tmp_date:=TO_CHAR(SYSDATE-NUM,'yyyymmdd');

     FOR       V_DATA IN Cur1(tmp_date) LOOP
  
         IF (LENGTH(V_DATA.StaffNo)<=5 OR lower(substr(V_DATA.StaffNo,1,2))='ts')
  
         THEN
  
       in_t := substr(V_DATA.intime,1,2)
            ||':'||
         substr(V_DATA.intime,3,2)
         ||':'||
         substr(V_DATA.intime,5,2);
       
       out_t := substr(V_DATA.outtime,1,2)
            ||':'||
         substr(V_DATA.outtime,3,2)
         ||':'||
         substr(V_DATA.outtime,5,2);
  
   
INSERT INTO     IOData_mrs(
                        empid,
         iodate,
         intime,
         outtime,
         flag
         ) 
         values
         (
         V_DATA.StaffNo,
         TO_DATE(V_DATA."DataDate",'yyyymmdd'),
         in_t,out_t,
         '1'
         );
 
          END IF;
   
--臨時卡  
    IF (V_DATA.StaffNo like 'STAFF%')
  
             THEN
   
    FOR   V_EMPNO IN (SELECT  substr(empno,instr(empno,'R')+1,5) empno  FROM TEMP_CARD WHERE CARDNO=V_DATA.StaffNo and used_date=TO_DATE(V_DATA."DataDate",'yyyymmdd')) LOOP
  
       in_t := substr(V_DATA.intime,1,2)
            ||':'||
         substr(V_DATA.intime,3,2)
         ||':'||
         substr(V_DATA.intime,5,2);
       
       out_t := substr(V_DATA.outtime,1,2)
            ||':'||
         substr(V_DATA.outtime,3,2)
         ||':'||
         substr(V_DATA.outtime,5,2);
   
INSERT INTO     IOData_mrs(
                        empid,
         iodate,
         intime,
         outtime,
         flag
         ) 
         values
         (
         V_EMPNO.empno,
         TO_DATE(V_DATA."DataDate",'yyyymmdd'),
         in_t,out_t,
         '1'
         );
   end loop;     
 
END IF;
--臨時卡結束   

 
       END LOOP;
   
    FOR CD_DATA IN CurCD(tmp_date) LOOP
    tmp_empno := CD_DATA.EMPNO;
    IF(substr(CD_DATA.EMPNO,1,1)='R')
    THEN
    tmp_empno := substr(CD_DATA.EMPNO,2,4);
    END IF;

   

   
    in_t := to_char(CD_DATA.INTIME,'hh24')
            ||':'||
         to_char(CD_DATA.INTIME,'mi')
         ||':'||
         to_char(CD_DATA.INTIME,'ss');
       
       out_t :=to_char(CD_DATA.OUTTIME,'hh24')
            ||':'||
         to_char(CD_DATA.OUTTIME,'mi')
         ||':'||
         to_char(CD_DATA.OUTTIME,'ss');
      
    INSERT INTO     IOData_mrs(
                        empid,
         iodate,
         intime,
         outtime,
         flag
         ) 
         values
         (
         tmp_empno,
         to_date(tmp_date,'yyyymmdd'),
         in_t,out_t,
         '1'
         );
   
   
    END LOOP;
 
 END LOOP;
 DELETE    iodata_mrs
 WHERE
           trunc(IODATE)>=
        TO_CHAR(trunc(sysdate-1))
    AND
         flag='0';
              
   INSERT INTO iodata_mrs
             (IODATE,EMPID,OUTTIME,INTIME,flag)(
 SELECT      IDATE,substr(EMPNO,instr(EMPNO,'R')+1,length(EMPNO)),
                to_char(max(to_date(to_char(IDATE,'yyyy-mm-dd')||' '||ITIME_H||':'||ITIME_M,'yyyy-mm-dd hh24:mi')),'hh24:mi:ss') outtime,
                to_char(min(to_date(to_char(IDATE,'yyyy-mm-dd')||' '||ITIME_H||':'||ITIME_M,'yyyy-mm-dd hh24:mi')),'hh24:mi:ss') intime,'0'
    FROM        ps_card_mrc@MRCPAPRHR
    WHERE       trunc(IDATE)>=trunc(sysdate-1)
 AND         (empno like 'R%'
    OR          empno in (
 SELECT      empno
 FROM        ps_employee
 WHERE       site='MRC'
 AND         (salary_type='1'
 OR           salary_type='7')
 AND         status='1'
 AND         empno
 NOT LIKE   'R%'))
    AND         (empno,trunc(IDATE))
 NOT IN      (
 SELECT      'R'||empid,trunc(IODATE)
 FROM        iodata_mrs
 WHERE       flag='1')
    GROUP BY    empno,idate);    
  
   FOR Cur2 IN (SELECT IDATE,substr(EMPNO,instr(EMPNO,'R')+1,length(EMPNO)) EMPNO,
                to_char(max(to_date(to_char(IDATE,'yyyy-mm-dd')||' '||ITIME_H||':'||ITIME_M,'yyyy-mm-dd hh24:mi')),'hh24:mi:ss') outtime,
                to_char(min(to_date(to_char(IDATE,'yyyy-mm-dd')||' '||ITIME_H||':'||ITIME_M,'yyyy-mm-dd hh24:mi')),'hh24:mi:ss') intime
    FROM        ps_card_mrc@MRCPAPRHR
 WHERE       trunc(IDATE)>=trunc(sysdate-1)
 AND         (empno like 'R%'
 OR          empno in (
 SELECT      empno
 FROM        ps_employee
 WHERE       site='MRC'
 AND         (salary_type='1'
 OR           salary_type='7')
 AND         status='1'
 AND         empno
 NOT LIKE   'R%'))
    AND         (empno,trunc(IDATE))
  IN      (
 SELECT      'R'||empid,trunc(IODATE)
 FROM        iodata_mrs
 WHERE       flag='1')
    GROUP BY    empno,idate)  LOOP
 
  FOR Cur3 IN(SELECT * FROM IODATA_MRS WHERE IODATE=Cur2.IDATE AND EMPID=Cur2.EMPNO) LOOP
 
   IF Cur3.OUTTIME>Cur2.outtime THEN
      out_t:=Cur3.OUTTIME;
   END IF;
   IF Cur3.OUTTIME<Cur2.outtime THEN
    out_t:=Cur2.outtime;
   END IF;
   IF Cur3.INTIME<Cur2.outtime THEN
      in_t:=Cur3.INTIME;
   END IF;
   IF Cur3.INTIME>Cur2.outtime THEN
     in_t:=Cur2.intime;
   END IF;
  END LOOP;
 
  UPDATE IODATA_MRS SET OUTTIME=out_t, INTIME=in_t where IODATE=Cur2.IDATE AND EMPID=Cur2.EMPNO;
 
 END LOOP;
   
    INSERT INTO get_data_info
        values (sysdate);
 
 Commit;
    select count(1) into num from iodata_mrs where iodate=trunc(sysdate);
    send_email ('MRSOAAdmin@mic.com.tw','torkey.dai@mic.com.tw',
                      'Work_Attendance_Record_Num',num,'','nesta.jiang@mic.com.tw;luming.lu@mic.com.tw');
      EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
     -- DBMS_OUTPUT.PUT_LINE('Wrong');
   Raise;
END TO_IODATAMRC;
/