DROP TRIGGER "GROUND_PATROL_OBSERVATIONS_BI"

DROP TRIGGER "RESEARCH_OBSERVATIONS_DATA_BI"

DROP PROCEDURE "GROUND_PATROLS_INSERT"

DROP TRIGGER "AIR_PATROL_OBSERVATIONS_BI"


ALTER PROCEDURE "GET_OBS_RELATION_POSITION"
(
  "PA_ID" VARCHAR(6)
)
RETURNS
(
  "OBSERVATION_RELATION" INTEGER
)
AS
  DECLARE VARIABLE OBS_CODE_RELATION  INTEGER;
  DECLARE VARIABLE XINT INTEGER;
BEGIN
  IF ((PA_ID IS NOT NULL) OR (PA_ID<>'')) THEN BEGIN
    SELECT b.OBSERVATION_RELATION
    FROM LK_OBSERVATIONS a
    LEFT JOIN LK_OBS_CODES b
      ON(a.OBS_CODE_RELATION=b.OBS_CODE_RELATION)
    WHERE a.PA_ID=:PA_ID AND a.OBSERVATION_GROUP='Position' AND a.OBSERVATION='Position' AND b.OBS_CODE='Position'
    INTO :OBSERVATION_RELATION;
    IF (OBSERVATION_RELATION IS NULL) THEN BEGIN
      OBSERVATION_RELATION = Null;
      SELECT 1
      FROM LK_OBSERVATION_GROUPS a
      WHERE a.OBSERVATION_GROUP='Position'
      INTO :XINT;
      IF (XINT IS NULL) THEN BEGIN
        INSERT INTO LK_OBSERVATION_GROUPS (OBSERVATION_GROUP)
        VALUES ('Position');
      END
      OBSERVATION_RELATION = Null;
      SELECT 1
      FROM LK_MIST_OBSERVATIONS a
      WHERE a.OBSERVATION_GROUP='Position' AND a.MIST_OBSERVATION='Position'
      INTO :XINT;
      IF (XINT IS NULL) THEN BEGIN
        INSERT INTO LK_MIST_OBSERVATIONS (OBSERVATION_GROUP, MIST_OBSERVATION, REFERENCE_NAME)
        VALUES ('Position', 'Position', 'Position');
      END
      SELECT a.OBS_CODE_RELATION
      FROM LK_OBSERVATIONS a
      WHERE a.PA_ID=:PA_ID AND a.OBSERVATION_GROUP='Position' AND a.OBSERVATION='Position'
      INTO :OBS_CODE_RELATION;
      IF (OBSERVATION_RELATION IS NULL) THEN BEGIN
        EXECUTE PROCEDURE GET_UNIQUE_ID(PA_ID) RETURNING_VALUES OBS_CODE_RELATION;
        INSERT INTO LK_OBSERVATIONS (PA_ID, OBSERVATION_GROUP, OBSERVATION, OBS_CODE_RELATION, KEY_OBSERVATION, AIR_PATROLS)
        VALUES (:PA_ID, 'Position', 'Position', :OBS_CODE_RELATION, 0, 1);
      END
      OBSERVATION_RELATION = Null;
      SELECT a.OBSERVATION_RELATION
      FROM LK_OBS_CODES a
      WHERE a.OBS_CODE_RELATION=:OBS_CODE_RELATION
      INTO :OBSERVATION_RELATION;
      IF (OBSERVATION_RELATION IS NULL) THEN BEGIN
        INSERT INTO LK_OBS_CODES (OBS_CODE_RELATION, OBS_CODE, OBSERVATION_REMARKS, AIR_PATROLS)
        VALUES (:OBS_CODE_RELATION, 'Position', NULL, 1);
        SELECT b.OBSERVATION_RELATION
        FROM LK_OBS_CODES b
        WHERE b.OBS_CODE_RELATION=:OBS_CODE_RELATION AND b.OBS_CODE='Position'
        INTO :OBSERVATION_RELATION;
      END
    END
  END
END


CREATE PROCEDURE "GROUND_PATROLS_INSERT"
(
  "PA_ID" VARCHAR(6),
  "GP_ID" INTEGER,
  "GPTYPE" CHAR(1),
  "PATROL_DAYS" INTEGER,
  "PATROL_DAY" INTEGER,
  "START_DATETIME" TIMESTAMP,
  "END_DATETIME" TIMESTAMP,
  "REST_TIME" INTEGER,
  "WAYPOINT" INTEGER,
  "WAYPOINT_EASTING" FLOAT,
  "WAYPOINT_NORTHING" FLOAT,
  "DATE_TIME" TIMESTAMP,
  "OBSERVATION_DISTANCE" INTEGER,
  "OBSERVATION_AZIMUTH" INTEGER,
  "MGTSECTOR" VARCHAR(20),
  "OBSERVATION_RELATION" INTEGER,
  "TOTAL" INTEGER,
  "ADULT_MALES" INTEGER,
  "ADULT_FEMALES" INTEGER,
  "TOTAL_YOUNG" INTEGER,
  "OBS_REMARKS_RELATION" VARCHAR(20)
)
AS
BEGIN
  IF ((OBSERVATION_DISTANCE<0) OR (OBSERVATION_DISTANCE IS NULL)) THEN
    OBSERVATION_DISTANCE=0;
  INSERT INTO GROUND_PATROL_OBSERVATIONS (PA_ID, GP_ID, 
         PATROL_DAY, WAYPOINT, WAYPOINT_EASTING, WAYPOINT_NORTHING,
         DATE_TIME, OBSERVATION_DISTANCE,
         OBSERVATION_AZIMUTH, MGTSECTOR, OBSERVATION_RELATION,
         TOTAL, ADULT_MALES, ADULT_FEMALES, TOTAL_YOUNG, OBS_REMARKS_RELATION)
    VALUES (:PA_ID, :GP_ID, :PATROL_DAY, :WAYPOINT,
            CAST(:WAYPOINT_EASTING AS INTEGER), CAST(:WAYPOINT_NORTHING AS INTEGER),
            :DATE_TIME, :OBSERVATION_DISTANCE,
            :OBSERVATION_AZIMUTH, :MGTSECTOR, :OBSERVATION_RELATION,
            :TOTAL, :ADULT_MALES, :ADULT_FEMALES, :TOTAL_YOUNG, :OBS_REMARKS_RELATION);
  UPDATE GROUND_PATROLS
    SET GPTYPE=:GPTYPE, PATROL_DAYS=:PATROL_DAYS
    WHERE PA_ID=:PA_ID AND GP_ID=:GP_ID;
  UPDATE GROUND_PATROL
    SET START_DATETIME=:START_DATETIME,
        END_DATETIME=:END_DATETIME, REST_TIME=:REST_TIME
    WHERE PA_ID=:PA_ID AND GP_ID=:GP_ID AND PATROL_DAY=:PATROL_DAY;
END


CREATE TRIGGER "AIR_PATROL_OBSERVATIONS_BI" FOR "AIR_PATROL_OBSERVATIONS"
ACTIVE BEFORE INSERT POSITION 1
AS
  DECLARE VARIABLE XAP_ID INTEGER;
  DECLARE VARIABLE XWAYPOINT INTEGER;
BEGIN
  IF (NEW.OBSERVATION_RELATION IS NULL) THEN
    EXECUTE PROCEDURE GET_OBS_RELATION_POSITION NEW.PA_ID RETURNING_VALUES NEW.OBSERVATION_RELATION;
  SELECT MAX(AP_ID)
  FROM AIR_PATROLS
  WHERE PA_ID=NEW.PA_ID AND AP_ID=NEW.AP_ID
  INTO :XAP_ID;
  IF ((XAP_ID IS NULL) OR (XAP_ID < NEW.AP_ID)) THEN
    BEGIN
      INSERT INTO AIR_PATROLS (PA_ID, AP_ID, START_DATETIME, END_DATETIME, APTYPE)
      VALUES (NEW.PA_ID, NEW.AP_ID, 'NOW', 'NOW', 'A');
    END
  SELECT MAX(WAYPOINT)
  FROM AIR_PATROL_OBSERVATIONS G
  WHERE G.PA_ID=NEW.PA_ID AND G.AP_ID=NEW.AP_ID
  INTO :XWAYPOINT;
  WHILE (XWAYPOINT>NEW.WAYPOINT) DO BEGIN
    UPDATE AIR_PATROL_OBSERVATIONS G
    SET WAYPOINT=:XWAYPOINT + 1
    WHERE G.PA_ID=NEW.PA_ID AND G.AP_ID=NEW.AP_ID AND G.WAYPOINT=:XWAYPOINT;
    XWAYPOINT=XWAYPOINT-1;
  END
  IF (NEW.MGTSECTOR IS NULL) THEN
    NEW.MGTSECTOR='No Sector';
END


CREATE TRIGGER "GROUND_PATROL_OBSERVATIONS_BI" FOR "GROUND_PATROL_OBSERVATIONS"
ACTIVE BEFORE INSERT POSITION 1
AS
  DECLARE VARIABLE XGP_ID INTEGER;
  DECLARE VARIABLE XPATROL_DAY INTEGER;
  DECLARE VARIABLE XWAYPOINT INTEGER;
BEGIN
  IF (NEW.OBSERVATION_RELATION IS NULL) THEN
    EXECUTE PROCEDURE GET_OBS_RELATION_POSITION NEW.PA_ID RETURNING_VALUES NEW.OBSERVATION_RELATION;
  SELECT MAX(GP_ID)
  FROM GROUND_PATROL G
  WHERE G.PA_ID=NEW.PA_ID AND G.GP_ID=NEW.GP_ID 
  INTO :XGP_ID;
  SELECT PATROL_DAY
  FROM GROUND_PATROL G
  WHERE G.PA_ID=NEW.PA_ID AND G.GP_ID=NEW.GP_ID AND G.PATROL_DAY=NEW.PATROL_DAY 
  INTO :XPATROL_DAY;
  IF ((XGP_ID IS NULL) OR (XGP_ID < NEW.GP_ID) OR (XPATROL_DAY IS NULL)) THEN
    BEGIN
      INSERT INTO GROUND_PATROL (PA_ID, GP_ID, PATROL_DAY, START_DATETIME, END_DATETIME)
      VALUES (NEW.PA_ID, NEW.GP_ID, NEW.PATROL_DAY, NEW.DATE_TIME, NEW.DATE_TIME);
    END
  SELECT MAX(WAYPOINT)
  FROM GROUND_PATROL_OBSERVATIONS G
  WHERE G.PA_ID=NEW.PA_ID AND G.GP_ID=NEW.GP_ID AND G.PATROL_DAY=NEW.PATROL_DAY
  INTO :XWAYPOINT;
  WHILE (XWAYPOINT>=NEW.WAYPOINT) DO BEGIN
    UPDATE GROUND_PATROL_OBSERVATIONS G
    SET WAYPOINT=:XWAYPOINT + 1
    WHERE G.PA_ID=NEW.PA_ID AND G.GP_ID=NEW.GP_ID AND G.PATROL_DAY=NEW.PATROL_DAY AND WAYPOINT=:XWAYPOINT;
    XWAYPOINT=XWAYPOINT-1;
  END
END                                          


CREATE TRIGGER "RESEARCH_OBSERVATIONS_DATA_BI" FOR "RESEARCH_OBSERVATIONS_DATA"
ACTIVE BEFORE INSERT POSITION 1
AS
  DECLARE VARIABLE XR_ID INTEGER;
  DECLARE VARIABLE XWAYPOINT INTEGER;
BEGIN
  IF (NEW.OBSERVATION_RELATION IS NULL) THEN
    EXECUTE PROCEDURE GET_OBS_RELATION_POSITION NEW.PA_ID RETURNING_VALUES NEW.OBSERVATION_RELATION;
  SELECT MAX(R_ID)
  FROM RESEARCH_OBSERVATIONS G
  WHERE G.PA_ID=NEW.PA_ID AND G.R_ID=NEW.R_ID AND G.RESEARCHER=NEW.RESEARCHER
  INTO :XR_ID;
  IF ((XR_ID IS NULL) OR (XR_ID<NEW.R_ID)) THEN
    BEGIN
      INSERT INTO RESEARCH_OBSERVATIONS (PA_ID, RESEARCHER, R_ID)
      VALUES (NEW.PA_ID, NEW.RESEARCHER, NEW.R_ID);
    END
  SELECT MAX(WAYPOINT)
  FROM RESEARCH_OBSERVATIONS_DATA G
  WHERE G.PA_ID=NEW.PA_ID AND G.RESEARCHER=NEW.RESEARCHER AND G.R_ID=NEW.R_ID
  INTO :XWAYPOINT;
  WHILE (XWAYPOINT>NEW.WAYPOINT) DO BEGIN
    UPDATE RESEARCH_OBSERVATIONS_DATA G
    SET WAYPOINT=:XWAYPOINT + 1
    WHERE G.PA_ID=NEW.PA_ID AND G.R_ID=NEW.R_ID AND G.RESEARCHER=NEW.RESEARCHER AND WAYPOINT=:XWAYPOINT;
    XWAYPOINT=XWAYPOINT-1;
  END
END