/*Create package for line*/

CREATE OR REPLACE  PACKAGE AlexMonitor.PKG_PRODUCTLINE
AS
   TYPE cursorref is ref cursor;
   PROCEDURE GetAllLineByFloorName(floorName in AlexMonitor.ProduceLine.PL_ADDR%type, out_var out cursorref);
   PROCEDURE GetAllServerByLineID(lineID in AlexMonitor.ProduceLine.PL_ID%type, out_var out cursorref);
   PROCEDURE GetAllRobotByLineID(lineID in AlexMonitor.ProduceLine.PL_ID%type, out_var out cursorref);
   PROCEDURE GetAllStationByLineID(lineID in AlexMonitor.ProduceLine.PL_ID%type, out_var out cursorref); 
   PROCEDURE GetLineTestSquenceByLineID(lineID in AlexMonitor.ProduceLine.PL_ID%type, out_var out cursorref);   
  
 
end pkg_ProductLine;

CREATE OR REPLACE  PACKAGE BODY AlexMonitor.PKG_PRODUCTLINE
 AS
    PROCEDURE GetAllLineByFloorName(floorName in AlexMonitor.ProduceLine.PL_ADDR%type, out_var out cursorref)
        IS
        BEGIN
            OPEN out_var for select
              PL_ID,
              PL_Name,
              PL_TYPE,
              PL_ADDR,
             --PL_CreateTime,
             TO_CHAR(PL_CreateTime,'YYYY-MM-DD HH24:MI:SS.FF') StartTime,
             TO_CHAR(PL_ReleaseTime,'YYYY-MM-DD HH24:MI:SS.FF') ReleaseTime,
            -- PL_ReleaseTime,
             PL_Expect_UPH ,
             PL_Expect_Yield,
             PL_Monitor_Name,
             PL_Monitor_Tel
            FROM AlexMonitor.ProduceLine WHERE ProduceLine.PL_ADDR = floorName;
        EXCEPTION WHEN NO_DATA_FOUND THEN
            RAISE_APPLICATION_ERROR(-10000,'No data found, Please change the condition!');
    end GetAllLineByFloorName;
   
    PROCEDURE GetAllServerByLineID(lineID in AlexMonitor.ProduceLine.PL_ID%type, out_var out cursorref)
        IS
       BEGIN
            OPEN out_var for select
              Server_ID,
              Server_Name,
              Server_IP,
              Server_Mac_Address
            FROM AlexMonitor.Server WHERE Server.PL_ID= lineID;
        EXCEPTION WHEN NO_DATA_FOUND THEN
            RAISE_APPLICATION_ERROR(-10000,'No data found, Please change the condition!');
    end GetAllServerByLineID;
   
     PROCEDURE GetAllRobotByLineID(lineID in AlexMonitor.ProduceLine.PL_ID%type, out_var out cursorref)
         IS
        BEGIN
           OPEN out_var for select
           Robot_ID,
           RT_ID,
           RM.SERVER_ID,
           Robot_Name,
           Robot_IP,
           Robot_Use_State,
           Robot_Port,
           Robot_Expect_Yield,
           Robot_Expect_UPH
         FROM AlexMonitor.Server JOIN AlexMonitor.RobotManagement  RM on  SERVER.SERVER_ID = RM.SERVER_ID  where SERVER.PL_ID = lineID;
        EXCEPTION WHEN NO_DATA_FOUND THEN
            RAISE_APPLICATION_ERROR(-10000,'No data found, Please change the condition!');
       END GetAllRobotByLineID;
      
        PROCEDURE GetAllStationByLineID(lineID in AlexMonitor.ProduceLine.PL_ID%type, out_var out cursorref)
           IS
        BEGIN
            OPEN out_var for select
            Station_ID,
            RM.Robot_ID,
            TestStation_ID,
            Station_Name,
            Station_IP,
            Station_Mac_Addr,
            Staion_Type
           
        From AlexMonitor.Station JOIN AlexMonitor.RobotManagement RM ON STATION.ROBOT_ID = RM.ROBOT_ID  JOIN
        AlexMonitor.SERVER ON RM.SERVER_ID = SERVER.SERVER_ID  where SERVER.PL_ID = lineID;
         EXCEPTION WHEN NO_DATA_FOUND THEN
            RAISE_APPLICATION_ERROR(-10000,'No data found, Please change the condition!');
       END GetAllStationByLineID;
      
       PROCEDURE GetLineTestSquenceByLineID(lineID in AlexMonitor.ProduceLine.PL_ID%type, out_var out cursorref)
          IS
          V_ProduceFlow_ID number(8);
          V_TestStationSquence_ID number(3);
     
       BEGIN
          select  ProduceFlow_ID Into V_ProduceFlow_ID  from AlexMonitor.ProduceLine where PRODUCELINE.PL_ID = lineID;
          select  TestStationSquence_ID  Into V_TestStationSquence_ID from AlexMonitor.ProduceFlow where PRODUCEFLOW.PRODUCEFLOW_ID =  V_ProduceFlow_ID;
         
          Open out_var for select
             TestStation_Name,
             TestStation_Index
          From AlexMonitor.TestStation where  TESTSTATION.TESTSTATIONSQUENCE_ID = V_TestStationSquence_ID  order by  TestStation_Index;
       END GetLineTestSquenceByLineID;
      
  end pkg_ProductLine;