/* Program: alarm_processor.sql       		                        */
/* Type   : Oracle stored prcedure                                      */
/* Purpose: Process alarm messages into active alarm and alarm history	*/
/*	    states							*/
/* Parameters: rec_no                                    		*/
/*									*/
/*--------------------------------------------------------------------  */
/* History: Michael R. Miller, 06/15/96 -  Original Coding              */
/*====================================================================  */

CREATE OR REPLACE PROCEDURE alarm_processor(ap_region VARCHAR2(2),ap_trans_id NUMBER(8),ap_type_code VARCHAR2(2),ap_status VARCHAR2(1),ap_severity VARCHAR2(1),ap_date VARCHAR2(8),ap_time VARCHAR2(8),ap_location VARCHAR2(40), ap_process_status IN OUT VARCHAR2(1))

DECLARE

  row_count NUMBER(8);
  db_link_name VARCHAR2(20);
  ap_structure_id NUMBER(12);
  ap_node_name VARCHAR2(6);
  ap_node_status VARCHAR2(1);
  ap_device_name VARCHAR2(12);
  ap_x NUMBER(16,3);
  ap_y NUMBER(16,3);
  char_datetime VARCHAR2(17);
  datetime DATE;
  ap_alarm_count NUMBER(5);
  err_msg VARCHAR2(80);

CURSOR alarm_cursor IS
    SELECT status	
	FROM alarm
 	WHERE status = 'A' AND trans_id = ap_trans_id AND type_code = ap_code;
	FOR UPDATE OF status;	

BEGIN
	/* Assign name of DB-Able database link */
	db_link_name := "dbable_" + ap_region + "test";

	/* If alarm message is a new alarm then process new alarm */
	If ap_status = "A" Then

		/* format database name/table in query string*/

		/* Select the data required from the DB-Able equipment table */
		SELECT count(*) FROM equipment@db_link_name INTO row_count
			WHERE trans_id = ap_trans_id;
		SELECT  structure_id, name,design_node FROM equipment@db_link_name INTO ap_structure_id,ap_device_name,ap_node_name
			WHERE trans_id = ap_trans_id;

		If row_count <> 0 Then

			/* Select the data required from the DB-Able structure table */
			SELECT count(*) FROM structure@db_link_name INTO row_count
				WHERE structure_id = ap_structure_id;
			SELECT geo_x, geo_y FROM structure@db_link_name INTO ap_x,ap_y
				WHERE structure_id = ap_structure_id;

			If row_count <> 0 Then
				/* shift x & y for NAD27 -> NAD83 */
				If  ap_region = 'ba' Then
ap_x := ap_x -3000000	/* Strip off zone code */
ap_x = ap_x + 4561375	/* Add x offset */
ap_y = ap_y + 1640410	/* Add x offset */
				Else
ap_x := ap_x -3000000	/* Strip off zone code */
ap_x = ap_x + 4561375	/* Add x offset */
ap_y = ap_y + 1640410	/* Add x offset */
				EndIf;
			Else 
				char_trans_id := TO_CHAR(ap_trans_id,999999)
err_msg := 'Matching structure record not found for Trans_ID = ' + char_trans_id
INSERT INTO alarm_processor_error VALUES = (err_msg);
exit;
			EndIf;

			/* Format the date and time into a date data type */
			char_datetime := ap_date + ap_time;
			datetime = ToDate(char_datetime,'MM/DD/YY-HH24:MI:SS');

			/* If alarm processsing is enabled for this alarm node then add the alarm record to the alarm table */
 			SELECT count(*) FROM node_attribute INTO row_count
				WHERE node_attribute.node = ap_node;
			SELECT status FROM node_attribute INTO ap_node_status
				WHERE node_attribute.node = ap_node;

			If row_count <> 0 Then
				If ap_node_status <> 'I' Then
					INSERT INTO alarm VALUES (db_link_name,ap_node_name,ap_node_status,ap_trans_id,ap_device_name,ap_x,ap_y,ap_type_code,ap_severity,"A",datetime,'',ap_location);
				Else
					Exit
				EndIf;
			Else 
err_msg := 'Node Attribute record not found for Node = ' + ap_node;
INSERT INTO alarm_processor_error VALUES = (err_msg);
exit;
			EndIf;
			
			/* Increment the active alarm count for the node matching the alarm restore event */
			SELECT count(*) FROM node_attribute INTO row_count
				WHERE node_attribute.node_name = ap_node_name;
			SELECT alarm_count FROM node_attribute INTO ap_alarm_count
				WHERE node_attribute.node_name= ap_node_name;

			If row_count <> 0 Then
				ap_alarm_count =: ap_alarm_count + 1;
				UPDATE node_attribute SET alarm_count = ap_alarm_count
					WHERE node_attribute.node_name = ap_node_name;
Else 
err_msg := 'Node Attribute record not found for Node = ' + ap_node;
INSERT INTO alarm_processor_error VALUES = (err_msg);
				exit;
			EndIf;

			/* Assign alarm_log status update variable (active alarm processing was successful) */
			ap_process_status := 'P';

		Else
			/* Assign alarm_log status update variable (active alarm processing was not successful - matching transponder ID not found in DB-Able) */
			ap_process_status := 'T';

		EndIf;

		Exit;

	/* If alarm message is an alarm restore then process alarm restore */
	ElseIf ap_status = "R" Then

		/* Format the date and time into a date data type */
		char_datetime := ap_date + ap_time;
		datetime = ToDate(char_datetime,'MM/DD/YY-HH24:MI:SS');
		
  		/* Initialize alarm counter */
		alarm_counter = 0
		
  		/* Initialize alarm_log status update variable (assume no matching active alarm will be found */
		ap_process_status := 'T';

		/* Update status and assign restore date/time for any active alarms matching alarm restore event */
		/* Increment alarm counter to count each matching active alarm event */
		OPEN alarm_cursor;
  		LOOP
			
FETCH status INTO old_status;
			EXIT WHEN alarm_cursor%NOTFOUND
			
			alarm_counter =: alarm_counter + 1;

			ap_process_status := 'P';

			UPDATE alarm SET status = 'H', restore_datetime = datetime
				WHERE CURRENT OF alarm_cursor;
      			GOTO ActiveAlarmNotFound WHEN alarm_cursor%NOTFOUND;
        
  		END LOOP;
  
  		CLOSE alarm_cursor;

		/* Decrement the active alarm count for the node matching the alarm restore event */
		SELECT count(*) FROM node_attribute INTO row_count
			WHERE node_attribute.node_name = ap_node_name;
		SELECT alarm_count FROM node_attribute INTO ap_alarm_count
			WHERE node_attribute.node_name = ap_node;

		If row_count <> 0 Then
			ap_alarm_count =: ap_alarm_count - alarm_counter;
			UPDATE node_attribute SET alarm_count = ap_alarm_count
				WHERE node_attribute.node_name = ap_node_name;
		Else 
err_msg := 'Node Attribute record not found for Node = ' + ap_node;
INSERT INTO alarm_processor_error VALUES = (err_msg);
			exit;
		EndIf;

		Exit;

	Else Error

	EndIf;
	
END;
/