CREATE OR REPLACE PACKAGE BODY ALEXMONITOR.PKG_OPERATOR
 AS
    PROCEDURE  GetStationDynamicState(lineID In  AlexMonitor.ProduceLine.PL_ID%type, out_var out cursorref)
      IS
        BEGIN
            OPEN out_var for select
--              Station_ID  ,
--              max(STATION_STATE) ,
--              extract(hour from (sysdate-max(TestTime))) *3600 + extract(minute from (sysdate-max(TestTime)))*60   +  round(extract(second from (sysdate-max(TestTime)))) T_SEC from ALEXMONITOR.StationRealTime where  regexp_like(Station_ID, '' || lineID || '.+' )  group by Station_ID;
                Station_ID,
                STATION_STATE,
                extract(hour from (sysdate-TestTime)) *3600 + extract(minute from (sysdate-TestTime))*60   +  round(extract(second from (sysdate-TestTime))) T_SEC from ALEXMONITOR.StationRealTime A where TestTime=(select max(TestTime) from ALEXMONITOR.StationRealTime where Station_ID=A.Station_ID and regexp_like(Station_ID, '' || lineID || '.+' ) );
             
    end GetStationDynamicState;
   
   
    PROCEDURE  InsertStationDynamicData(stationID In  AlexMonitor.StationRealTime.STATION_ID%type, stationState IN
                               AlexMonitor.StationRealTime.STATION_STATE%type,fixState in  AlexMonitor.StationRealTime.FIXTURE_STATE%type,internet In  AlexMonitor.StationRealTime.STATION_INTENET_STATE%type,out_var out number)
      IS
     
      BEGIN INSERT INTO AlexMonitor.StationRealTime(STATION_ID,STATION_STATE,TESTTIME,FIXTURE_STATE,STATION_INTENET_STATE) VALUES(
                            stationID,
                            stationState,
                            sysdate,
                            fixState,
                            internet);
          COMMIT;
        out_var:=1;
       
  end InsertStationDynamicData;
   
  PROCEDURE  GetSNMessage(SnInfo In  AlexMonitor.MessageData.SN%type, out_var out cursorref)
     IS
  BEGIN
            OPEN out_var for select
            LINENAME,
             PNP_NAME,
             STATIONNAME,
             LOCATIONINDEX,
             SN ,
             RESULT,
             STATTIME,
             STOPTIME,
             SYMPTOM_CODE,
             SYMPTOM_DESC  from  MessageData where  STOPTIME = (SELECT MAX(STOPTIME) FROM MessageData where  SN = SnInfo)  and SN = SnInfo;
           
       
    end GetSNMessage;
 
 
end;
/