/***********************************************************/
/*                                                         */
/* hard_search:                                            */
/*          Retrieves information on hardware pieces based */
/*          on name or serial number.                      */
/*                                                         */
/* Stephane Zuzlewski @1999                                */
/*                                                         */
/***********************************************************/

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <math.h>

#define	CONNECT_STRING	"netdc/dB2Usr@ncedc"


EXEC SQL INCLUDE sqlca.h;


typedef char asciz[20];
typedef char vc2_arr[11];

EXEC SQL BEGIN DECLARE SECTION;
/* User-defined type for null-terminated strings */
EXEC SQL TYPE asciz  IS STRING(20) REFERENCE;

/* User-defined type for a VARCHAR array element. */
EXEC SQL TYPE vc2_arr IS VARCHAR2(11) REFERENCE;

asciz	username;
asciz	password;
char	user_pwd[80];

int	i, j, k;
char	Param[32];
short	ind;

char	sta[7];
char	net[9];
char	sondate[32];
char	soffdate[32];

int	sensor_id;
int	filamp_id;
int	data_id;
char	name[81];
char	serial_nb[81];
char	ondate[32];
char	offdate[32];
int	nb_component;
int	nb_pchannel;
double	gain;

char	channel_comp[3];/* Channel */
char	component_type;	/* Component type */
double	sensitivity;	/* Sensitivity */
double	frequency;	/* Frequency */
int	seqresp_id;	/* Sequence of responses identifier */

int	resp_nb;	/* Response number */
char	resp_type;	/* Response type */
int	resp_id;	/* Response identifier */
int	unit_in;	/* Input units */
int	unit_out;	/* Output units */
char	unitin[81];	/* Input units */
char	unitout[81];	/* Output units */
char	r_type;		/* Response type */

char	filter_type[3];	/* Filter type */
int	nb_pole;	/* Number of poles */
double	corner_freq;	/* Corner frequency */
double	damping_value;	/* Damping value */

char	type;
double	r_value;	/* Real value */
double	i_value;	/* Imaginary value */

char	poly_type;	/* Type of polynomial approximation */
double	lower_bound;	/* Lower bound */
double	upper_bound;	/* Upper bound */
double	max_error;	/* Maximum error */
int	nb_coeff;	/* Number of coefficients */

int	pn_nb;		/* Coefficient number */
double	pn_value;	/* Coefficient value */

char	next_hard_type;	/* Next hardware type */
int	next_hard_nb;	/* Next hardware number */
int	next_hard_pchannel;	/* Next hardware channel */
double	azimuth;	/* Azimuth */
double	dip;		/* Dip */

char	data_type[81];	/* Datalogger type */
char	firmware_nb[81];/* Firmware number */
char	software[81];	/* Software */
char	software_nb[81];/* Software version number */
int	nb_board;	/* Number of boards */
int	nb_module;	/* Number of modules */
int	word_32;	/* 32 bit word order */
int	word_16;	/* 16 bit word order */
int	board_nb;	/* Board number */
int	module_nb;	/* Module number */


char      SC_Command[512];
char      FC_Command[512];
char      DC_Command[512];
EXEC SQL END DECLARE SECTION;

EXEC SQL DECLARE sc_stmt STATEMENT;
EXEC SQL DECLARE fc_stmt STATEMENT;
EXEC SQL DECLARE dc_stmt STATEMENT;


long      SQLCODE;


void	sql_error();	/* Handles unrecoverable errors */


				  /*****************/
				 /* Main function */
				/*****************/

main (int argc, char* argv[])
{
 int	Flag_Search = 0;
 int	Flag_Installed = 0;

 /* Test of parameters */
 if (argc != 3)
	{
	 printf ("\n\n hard_search [-n <name>] [-s <serial number>]\n\n");

	 exit (0);
	}

 if (!strcmp (argv[1], "-n"))
	{
	 Flag_Search = 1;

	 strcpy (Param, argv[2]);

	 for (i=0;i<strlen (Param);i++)
		{
		 if (Param[i] == '?')
			Param[i] = '_';
		 else if (Param[i] == '*')
			Param[i] = '%';
		}
	}
 else if (!strcmp (argv[1], "-s"))
	{
	 Flag_Search = 2;

	 strcpy (Param, argv[2]);

	 for (i=0;i<strlen (Param);i++)
		{
		 if (Param[i] == '?')
			Param[i] = '_';
		 else if (Param[i] == '*')
			Param[i] = '%';
		}
	}
 else
	{
	 printf ("\n Error: [%s] Unknown option.\n\n", argv[1]);

	 exit (1);
	}
 
 /* Connect to ORACLE. */
 EXEC SQL WHENEVER SQLERROR DO sql_error();

 strcpy (user_pwd, CONNECT_STRING);

 EXEC SQL CONNECT :user_pwd;


 /* Looking in Sensor table */
 if (Flag_Search == 1)
	{
	 sprintf (SC_Command,
	 "SELECT sensor_id, name, serial_nb, ondate, offdate, nb_component FROM ncedcdba.Sensor WHERE name LIKE '%s' ORDER BY ondate", Param);
	}
 else
	{
	 sprintf (SC_Command, "SELECT sensor_id, name, serial_nb, ondate, offdate, nb_component FROM ncedcdba.Sensor WHERE serial_nb LIKE '%s' ORDER BY ondate", Param);
	}

 EXEC SQL PREPARE sc_stmt FROM :SC_Command;
 EXEC SQL DECLARE sensor_cursor CURSOR FOR sc_stmt;

 EXEC SQL OPEN sensor_cursor;

 EXEC SQL WHENEVER not found DO break;

 while (1)
	{
	 EXEC SQL FETCH sensor_cursor INTO :sensor_id:ind, :name:ind, :serial_nb:ind, :ondate:ind, :offdate:ind, :nb_component:ind;

	 printf ("\n\n\n Found Sensor ...\n");
	 printf ("\n %.10s %.8s -- Nb. component: %d -- %.20s %.20s", name, serial_nb, nb_component, ondate, offdate);

	 for (j=1;j<=nb_component;j++)
		{
		 EXEC SQL SELECT channel_comp, component_type, sensitivity, frequency, seqresp_id
			  INTO	 :channel_comp, :component_type, :sensitivity, :frequency, :seqresp_id
			  FROM 	 ncedcdba.sensor_component
			  WHERE	 sensor_id = :sensor_id AND component_nb = :j;

		 printf ("\n\t Channel: %s -- Type: %c -- Sens: %5.2f -- Freq: %5.2f Hz", channel_comp, component_type, sensitivity, frequency);

		 EXEC SQL DECLARE resp_cursor CURSOR FOR
		 SELECT resp_nb, resp_type, resp_id, unit_in, unit_out, r_type
		 FROM	ncedcdba.response
		 WHERE	seqresp_id = :seqresp_id
		 ORDER BY	resp_nb;

		 EXEC SQL OPEN resp_cursor;

		 EXEC SQL WHENEVER not found DO break;

		 while (1)
			{
			 EXEC SQL FETCH resp_cursor INTO :resp_nb, :resp_type, :resp_id, :unit_in, :unit_out, :r_type;

			 EXEC SQL SELECT name INTO :unitin  FROM ncedcdba.D_Unit WHERE id = :unit_in;
			 EXEC SQL SELECT name INTO :unitout FROM ncedcdba.D_Unit WHERE id = :unit_out;

			 printf ("\n\t\t Type: %c -- In: %.12s Out: %.12s", r_type, unitin, unitout);

			 switch (resp_type)
				{
				 case 'H':
					  EXEC SQL SELECT filter_type, nb_pole, corner_freq, damping_value
						   INTO	  :filter_type, :nb_pole, :corner_freq, :damping_value
						   FROM	  ncedcdba.response_hp
						   WHERE  hp_id = :resp_id;

					  printf ("\n\t\t High-Pass filter: Type: %s -- Nb. Pole: %d -- Corner Freq: %5.2f -- Damping: %5.2f", filter_type, nb_pole, corner_freq, damping_value);
					  break;

				 case 'L':
					  EXEC SQL SELECT filter_type, nb_pole, corner_freq, damping_value
						   INTO	  :filter_type, :nb_pole, :corner_freq, :damping_value
						   FROM	  ncedcdba.response_lp
						   WHERE  lp_id = :resp_id;

					  printf ("\n\t\t Low-Pass filter: Type: %s -- Nb. Pole: %d -- Corner Freq: %5.2f -- Damping: %5.2f", filter_type, nb_pole, corner_freq, damping_value);
					  break;

				 case 'Z':
					  EXEC SQL DECLARE pz_cursor CURSOR FOR
					  SELECT type, r_value, i_value
					  FROM	ncedcdba.response_pz
					  WHERE pz_id = :resp_id
					  ORDER BY	pz_nb;

					  EXEC SQL OPEN pz_cursor;

					  EXEC SQL WHENEVER not found DO break;

					  while (1)
						{
						 EXEC SQL FETCH pz_cursor INTO :type, :r_value, :i_value;

						 printf ("\n\t\t Type: %c -- Real: %5.2f -- Imag: %5.2f", type, r_value, i_value);
						}

					  EXEC SQL CLOSE pz_cursor;
					  break;

				 case 'P':
					  EXEC SQL SELECT poly_type, lower_bound, upper_bound, max_error, nb_coeff
					  INTO	:poly_type, :lower_bound, :upper_bound, :max_error, :nb_coeff
					  FROM	ncedcdba.response_pn
					  WHERE	pn_id = :resp_id;

					  printf ("\n\t\t Polynomial: Type: %c -- Lower: %5.2f -- Upper: %5.2f -- Max. Error: %5.2f -- Nb Coeff: %d", poly_type, lower_bound, upper_bound, max_error, nb_coeff);

					  EXEC SQL DECLARE pn_cursor CURSOR FOR
					  SELECT pn_nb, pn_value
					  FROM 	ncedcdba.response_pn_data
					  WHERE	pn_id = :resp_id
					  ORDER BY	pn_nb;

					  EXEC SQL OPEN pn_cursor;

					  EXEC SQL WHENEVER not found DO break;

					  while (1)
						{
						 EXEC SQL FETCH pn_cursor INTO :pn_nb, :pn_value;

						 printf ("\n\t\t Coeff: %d -- Value: %5.2f", pn_nb, pn_value);
						}

					  EXEC SQL CLOSE  pn_cursor;
					  break;
				}
			}

		 EXEC SQL CLOSE resp_cursor;
		}

	 /* Sensor installed ? */
	 Flag_Installed = 0;

	 EXEC SQL DECLARE si_cursor CURSOR FOR
	 SELECT sta, net, ondate, offdate
	 FROM	ncedcdba.Station_Sensor
	 WHERE sensor_id = :sensor_id;

	 EXEC SQL OPEN si_cursor;

	 EXEC SQL WHENEVER not found DO break;

	 while (1)
		{
		 EXEC SQL FETCH si_cursor INTO :sta, :net, :sondate:ind, :soffdate:ind;

		 Flag_Installed = 1;

	 	 printf ("\n --> Sensor installed at %s %.3s -- %.20s %.20s.", sta, net, sondate, soffdate);
		}

	 if (Flag_Installed == 0)
		printf ("\n\n --> Sensor not installed.");

	 EXEC SQL CLOSE si_cursor;
	}

 EXEC SQL CLOSE sensor_cursor;

/* Looking in Filamp table */
 if (Flag_Search == 1)
	{
	 sprintf (FC_Command,
	 "SELECT filamp_id, name, serial_nb, ondate, offdate, nb_pchannel FROM ncedcdba.Filamp WHERE name LIKE '%s' ORDER BY ondate", Param);
	}
 else
	{
	 sprintf (FC_Command, "SELECT filamp_id, name, serial_nb, ondate, offdate, nb_pchannel FROM ncedcdba.Filamp WHERE serial_nb LIKE '%s' ORDER BY ondate", Param);
	}

 EXEC SQL PREPARE fc_stmt FROM :FC_Command;
 EXEC SQL DECLARE filamp_cursor CURSOR FOR fc_stmt;

 EXEC SQL OPEN filamp_cursor;

 EXEC SQL WHENEVER not found DO break;

 while (1)
	{
	 EXEC SQL FETCH filamp_cursor INTO :filamp_id:ind, :name:ind, :serial_nb:ind, :ondate:ind, :offdate:ind, :nb_pchannel:ind;

	 printf ("\n\n\n Found Filter-Amplifier ...\n");

printf ("\n\t %.26s %.26s -- Nb. channel: %d", name, serial_nb, nb_pchannel);

	 for (j=1;j<=nb_pchannel;j++)
		{
		 EXEC SQL SELECT gain, frequency, seqresp_id
			  INTO	 :gain, :frequency, :seqresp_id:ind
			  FROM 	 ncedcdba.filamp_pchannel
			  WHERE	 filamp_id = :filamp_id AND pchannel_nb = :j;

		 printf ("\n\t\t Gain: %5.2f -- Freq: %5.2f Hz", gain, frequency);

		 if (ind == (-1))
			 printf ("\n\t\t\t No response");
		 else
			{
		 	 EXEC SQL DECLARE resp_cursor2 CURSOR FOR
		 	 SELECT resp_nb, resp_type, resp_id, unit_in, unit_out, r_type
		 	 FROM	ncedcdba.response
		 	 WHERE	seqresp_id = :seqresp_id
		 	 ORDER BY	resp_nb;

		 	 EXEC SQL OPEN resp_cursor2;

		 	 EXEC SQL WHENEVER not found DO break;

		 	 while (1)
				{
			 	 EXEC SQL FETCH resp_cursor2 INTO :resp_nb, :resp_type, :resp_id, :unit_in, :unit_out, :r_type;

			 	 EXEC SQL SELECT name INTO :unitin  FROM ncedcdba.D_Unit WHERE id = :unit_in;
			 	 EXEC SQL SELECT name INTO :unitout FROM ncedcdba.D_Unit WHERE id = :unit_out;

			 	 printf ("\n\t\t\t Type: %c -- In: %.12s Out: %.12s", r_type, unitin, unitout);

			 	 switch (resp_type)
					{
				 	 case 'H':
					  	  EXEC SQL SELECT filter_type, nb_pole, corner_freq, damping_value
						   	  INTO	  :filter_type, :nb_pole, :corner_freq, :damping_value
						   	  FROM	  ncedcdba.response_hp
						   	  WHERE  hp_id = :resp_id;

					  	 printf ("\n\t\t\t High-Pass filter: Type: %s -- Nb. Pole: %d -- Corner Freq: %5.2f -- Damping: %5.2f", filter_type, nb_pole, corner_freq, damping_value);
					  	 break;


				 	 case 'L':
					 	  EXEC SQL SELECT filter_type, nb_pole, corner_freq, damping_value
							   INTO	  :filter_type, :nb_pole, :corner_freq, :damping_value
							   FROM	  ncedcdba.response_lp
							   WHERE  lp_id = :resp_id;

						  printf ("\n\t\t\t Low-Pass filter: Type: %s -- Nb. Pole: %d -- Corner Freq: %5.2f -- Damping: %5.2f", filter_type, nb_pole, corner_freq, damping_value);
						  break;

					 case 'Z':
						  EXEC SQL DECLARE pz_cursor2 CURSOR FOR
						  SELECT type, r_value, i_value
						  FROM	ncedcdba.response_pz
						  WHERE pz_id = :resp_id
						  ORDER BY	pz_nb;

						  EXEC SQL OPEN pz_cursor2;

						  EXEC SQL WHENEVER not found DO break;

						  while (1)
							{
							 EXEC SQL FETCH pz_cursor2 INTO :type, :r_value, :i_value;

							 printf ("\n\t\t\t Type: %c -- Real: %5.2f -- Imag: %5.2f", type, r_value, i_value);
							}

						  EXEC SQL CLOSE pz_cursor2;
						  break;

					 case 'P':
						  EXEC SQL SELECT poly_type, lower_bound, upper_bound, max_error, nb_coeff
						  INTO	:poly_type, :lower_bound, :upper_bound, :max_error, :nb_coeff
						  FROM	ncedcdba.response_pn
						  WHERE	pn_id = :resp_id;

						  printf ("\n\t\t\t Polynomial: Type: %c -- Lower: %5.2f -- Upper: %5.2f -- Max. Error: %5.2f -- Nb Coeff: %d", poly_type, lower_bound, upper_bound, max_error, nb_coeff);

						  EXEC SQL DECLARE pn_cursor2 CURSOR FOR
						  SELECT pn_nb, pn_value
						  FROM 	ncedcdba.response_pn_data
						  WHERE	pn_id = :resp_id
						  ORDER BY	pn_nb;

						  EXEC SQL OPEN pn_cursor2;

						  EXEC SQL WHENEVER not found DO break;

						  while (1)
							{
							 EXEC SQL FETCH pn_cursor2 INTO :pn_nb, :pn_value;

							 printf ("\n\t\t\t Coeff: %d -- Value: %5.2f", pn_nb, pn_value);
							}

						  EXEC SQL CLOSE  pn_cursor2;
						  break;
					}
				}

			 EXEC SQL CLOSE resp_cursor2;
			}
		}
	 
	 /* Filter-Amplifier installed ? */
	 Flag_Installed = 0;

	 EXEC SQL DECLARE fi_cursor CURSOR FOR
	 SELECT sta, net, ondate, offdate
	 FROM	ncedcdba.Station_Filamp
	 WHERE filamp_id = :filamp_id;

	 EXEC SQL OPEN fi_cursor;

	 EXEC SQL WHENEVER not found DO break;

	 while (1)
		{
		 EXEC SQL FETCH fi_cursor INTO :sta, :net, :sondate:ind, :soffdate:ind;

		 Flag_Installed = 1;

	 	 printf ("\n --> Filter-Amplifier installed at %s %.3s -- %.20s %.20s.", sta, net, sondate, soffdate);
		}

	 if (Flag_Installed == 0)
		printf ("\n\n --> Filter-Amplifier not installed.");

	 EXEC SQL CLOSE fi_cursor;
	}

 EXEC SQL CLOSE filamp_cursor;

 /* Looking in Datalogger table */
 if (Flag_Search == 1)
	{
	 sprintf (DC_Command,
	 "SELECT data_id, data_type, serial_nb, firmware_nb, ondate, offdate, nb_board, word_32, word_16, software, software_nb FROM ncedcdba.Datalogger WHERE data_type LIKE '%s' ORDER BY ondate", Param);
	}
 else
	{
	 sprintf (DC_Command, "SELECT data_id, data_type, serial_nb, firmware_nb, ondate, offdate, nb_board, word_32, word_16, software, software_nb FROM ncedcdba.Datalogger WHERE serial_nb LIKE '%s' ORDER BY ondate", Param);
	}

 EXEC SQL PREPARE dc_stmt FROM :DC_Command;
 EXEC SQL DECLARE data_cursor CURSOR FOR dc_stmt;

 EXEC SQL OPEN data_cursor;

 EXEC SQL WHENEVER not found DO break;

 while (1)
	{
	 EXEC SQL FETCH data_cursor INTO :data_id:ind, :data_type:ind, :serial_nb:ind, :firmware_nb:ind, :ondate:ind, :offdate:ind, :nb_board:ind, :word_32:ind, :word_16:ind, :software:ind, :software_nb:ind;

	 printf ("\n\n\n Found Datalogger ...\n");

	 printf ("\n\t Type: %.12s -- Serial: %.12s Firmware: %.12s Soft: %.12s Version: %.12s\n\t Nb Board: %d -- Word 32: %d Word 16: %d", data_type, serial_nb, firmware_nb, software, software_nb, nb_board, word_32, word_16);

	 for (j=1;j<=nb_board;j++)
		{
		 strcpy (serial_nb, "");
		 EXEC SQL SELECT serial_nb, nb_module
			  INTO	 :serial_nb:ind, :nb_module
			  FROM	 ncedcdba.datalogger_board
			  WHERE	 data_id = :data_id AND board_nb = :j;

		 printf ("\n\t\t Serial: %.12s -- Nb Module: %d", serial_nb, nb_module);

		 for (k=1;k<=nb_module;k++)
			{
			 strcpy (serial_nb, "");
			 EXEC SQL SELECT serial_nb, sensitivity
				  INTO	 :serial_nb:ind, :sensitivity
				  FROM	 ncedcdba.datalogger_module
				  WHERE	 data_id = :data_id AND board_nb = :j AND module_nb = :k;

			 printf ("\n\t\t\t Serial: %.12s -- Sensitivity: %f", serial_nb, sensitivity);
			}
		}
	 	 
	 /* Datalogger installed ? */
	 Flag_Installed = 0;

	 EXEC SQL DECLARE di_cursor CURSOR FOR
	 SELECT sta, net, ondate, offdate
	 FROM	ncedcdba.Station_Datalogger
	 WHERE data_id = :data_id;

	 EXEC SQL OPEN di_cursor;

	 EXEC SQL WHENEVER not found DO break;

	 while (1)
		{
		 EXEC SQL FETCH di_cursor INTO :sta, :net, :sondate:ind, :soffdate:ind;

		 Flag_Installed = 1;

	 	 printf ("\n --> Datalogger installed at %s %.3s -- %.20s %.20s.", sta, net, sondate, soffdate);
		}

	 if (Flag_Installed == 0)
		printf ("\n\n --> Datalogger not installed.");

	 EXEC SQL CLOSE di_cursor;
	}

 EXEC SQL CLOSE data_cursor;

 

 printf ("\n\n");
  
 /* Disconnect from the database */
 EXEC SQL COMMIT WORK RELEASE;

 exit(0);
}

/* Handle errors. Exit on any error. */
void	sql_error()
{
 char msg[512];
 size_t buf_len, msg_len;


 EXEC SQL WHENEVER SQLERROR CONTINUE;

 buf_len = sizeof(msg);
 sqlglm (msg, &buf_len, &msg_len);

 printf ("\nORACLE error detected:");
 printf ("\n%.*s \n", msg_len, msg);

 EXEC SQL ROLLBACK WORK RELEASE;

 exit(1);
}