/*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*/
/* Populate_Sensor.pc                                  */
/* 	Load to the NCEDC database the sensor info.    */
/*      Errors can be found on Error.log               */
/*                                                     */
/* Zuzlewski Stephane @1998/99                         */
/*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*/

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

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;
  int	j;
  int	k;
  char	sid[32];	/* Sensor identification */
  char	sn[32];		/* Serial number */
  char	s_dat[32];	/* Start date */
  char	e_dat[32];	/* End date */
  int	nb_comp;	/* Number of components */

  char	chan[32];	/* Sensor channel */
  char	stype[32];	/* Sensor type */
  char	units[32];	/* Units */
  int	unitin;		/* Input units */
  int	unitout;	/* Output units */
  float	sens;		/* Sensitivity */
  float	freq;		/* Frequency */
  int	nb_line;	/* Number of response lines */

  char	ftype[32];	/* Filter type */
  int	nb_pole;	/* Number of poles */
  float	corner;		/* Corner frequency */
  float	damping;	/* Damping value */
  float	r_value;	/* Real part of a complex pole/zero */
  float	i_value;	/* Imaginary part of a complex pole/zero */
  int	Flag_PZ;	/* Flag for poles & zeros */
  int	pnid;		/* Polynomial identifier */
  char	pname[81];	/* Polynomial name */
  int	respnb;		/* Response number */

  EXEC SQL END DECLARE SECTION;


long		SQLCODE;
FILE*		f_err;		/* Error File descriptor */

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



				   /*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*/
				  /*  Function to convert a julian date to a Oracle date           */
				 /*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*/

char*	Convert_Date (d)
char	d[16];
{
 EXEC SQL BEGIN DECLARE SECTION;
 char	nd[32];
 char 	day[16];
 EXEC SQL END DECLARE SECTION;

 char	hour[5];
 char	year[5];
 char	sday[4];


 sprintf (year, "%.4s", d);
 sprintf (sday,  "%.3s", d+5);
 sprintf (hour, "%.4s", d+9);

 if (!strcmp (year, "2599"))
	{
	 strcpy (nd, "3000/01/01 00:00:00");

	 return (nd);
	}
 else if (!strcmp (year, "????"))
	{
	 strcpy (nd, "0001/01/01 00:00:00");

	 return (nd);
	}

 strcpy (day, d);
 day[8] = '\0';
 
 EXEC SQL SELECT TO_CHAR (TO_DATE (:day, 'YYYY.DDD'), 'YYYY/MM/DD HH24:MI:SS') INTO :nd FROM DUAL;

 sprintf (nd, "%.4s/%.2s/%.2s %.2s:%.2s:00", year, nd+5, nd+8, hour, hour+2);

 return (nd);
}


						  /*-*-*-*-*-*-*-*-*/
						 /* Main function */
						/*-*-*-*-*-*-*-*-*/
main (argc, argv)
int	argc;
char*	argv[];
{
 FILE*	f_sensor; 	/* Sensor file descriptor */
 char	line[1025]; 	/* Line information */
 char	header[7];	/* Header */

 char	sdat[16];	/* Start date */
 char	edat[16];	/* End date */

 
 /* Opening the error file */
 if ((f_err = fopen ("Error.log", "w+t")) == NULL)
	{
	 printf ("\n Error [File (Error.log) could not be opened].\n\n");

	 exit (0);
	}

 /* Opening the sensor file */
 if ((f_sensor = fopen ("sensor.tbl", "rt")) == NULL)
	{
	 printf ("\n Error [File (sensor.tbl) could not be opened].\n\n");

	 exit (0);
	}


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

 strcpy (username, "ncedcdba");
 strcpy (password, "passwd");
 strcpy (user_pwd, "ncedcdba/passwd@ncedc");

 EXEC SQL CONNECT :user_pwd;

 printf ("\n Connected to ORACLE as user: %s\n", username);


 /* Parsing sensor file */
 fgets (line, 1024, f_sensor);

 while (!feof (f_sensor))
	{
	 strcpy (header, "");
	 sscanf (line, "%s", header);

	 if (!strcmp (header, "SENSOR"))
		{
		 /* Reading sensor information */
		 sscanf (line, "%*s %s %s %s %s %d", sid, sn, sdat, edat, &nb_comp);

		 /* Converting dates format */
		 strcpy (s_dat, Convert_Date (sdat));
		 strcpy (e_dat, Convert_Date (edat));

		 /* Inserting sensor information into the database */
		 EXEC SQL INSERT INTO Sensor (sensor_id, name, serial_nb, ondate, offdate, nb_component, lddate)
		 VALUES (sensorseq.NEXTVAL, :sid, :sn, :s_dat, :e_dat, :nb_comp, SYSDATE);

		 printf ("\n Sensor : %s\t%s\t%s\t%s\t%d", sid, sn, s_dat, e_dat, nb_comp);

		 for (i=1;i<=nb_comp;i++)
			{
			 Flag_PZ = 0;
			 respnb = 0;

			 fgets (line, 1024, f_sensor);

			 /* Reading component information */
			 sscanf (line, "%*s %s %s %s %f %f %d", chan, stype, units, &sens, &freq, &nb_line);

			 /* Inserting component information into the database */
			 EXEC SQL INSERT INTO Sensor_Component (sensor_id, component_nb, channel_comp, component_type, sensitivity, frequency, seqresp_id, lddate)
			 VALUES (sensorseq.CURRVAL, :i, :chan, :stype, :sens, :freq, respseq.NEXTVAL, SYSDATE);

			 printf ("\n\t Component : %s\t%s\t%s\t%f\t%f\t%d", chan, stype, units, sens, freq, nb_line);

			 /* Searching units information */
			 unitin = 0;
			 unitout = 0;

			 EXEC SQL SELECT id INTO :unitin FROM D_Unit WHERE name = :units;

			 if (unitin == 0)
				{
				 EXEC SQL SELECT uniseq.NEXTVAL INTO :unitin FROM DUAL;

				 EXEC SQL INSERT INTO D_Unit (id, name, description)
				 VALUES (:unitin, :units, NULL);
				}

			 EXEC SQL SELECT id INTO :unitout FROM D_Unit WHERE name = 'V';

			 if (unitout == 0)
				{
				 EXEC SQL SELECT uniseq.NEXTVAL INTO :unitout FROM DUAL;

				 EXEC SQL INSERT INTO D_Unit (id, name, description)
				 VALUES (:unitout, 'V', NULL);
				}

			 for (j=1;j<=nb_line;j++)
				{
				 fgets (line, 1024, f_sensor);
				 
				 /* Reading response type */
				 sscanf (line, "%s", header);

	 			if (!strcmp (header, "HP"))
					{
					 respnb++;

					 /* Reading high-pass filter information */
					 sscanf (line, "%*s %s %d %f %f", ftype, &nb_pole, &corner, &damping);

					 /* Inserting response information into the database */
					 EXEC SQL INSERT INTO Response (seqresp_id, resp_nb, resp_type, resp_id, unit_in, unit_out, r_type, lddate)
					 VALUES (respseq.CURRVAL, :respnb, 'H', hpseq.NEXTVAL, :unitin, :unitout, 'A', SYSDATE);

					 EXEC SQL INSERT INTO Response_HP (hp_id, filter_type, nb_pole, corner_freq, damping_value, lddate)
					 VALUES (hpseq.CURRVAL, :ftype, :nb_pole, :corner, :damping, SYSDATE);

					 printf ("\n\t\t HP : %s\t%d\t%f\t%f", ftype, nb_pole, corner, damping);
					}
				 else if (!strcmp (header, "LP"))
					{
					 respnb++;

					 /* Reading low-pass filter information */
					 sscanf (line, "%*s %s %d %f %f", ftype, &nb_pole, &corner, &damping);

					 /* Inserting response information into the database */
					 EXEC SQL INSERT INTO Response (seqresp_id, resp_nb, resp_type, resp_id, unit_in, unit_out, r_type, lddate)
					 VALUES (respseq.CURRVAL, :respnb, 'L', lpseq.NEXTVAL, :unitin, :unitout, 'A', SYSDATE);

					 EXEC SQL INSERT INTO Response_LP (lp_id, filter_type, nb_pole, corner_freq, damping_value, lddate)
					 VALUES (lpseq.CURRVAL, :ftype, :nb_pole, :corner, :damping, SYSDATE);

					 printf ("\n\t\t LP : %s\t%d\t%f\t%f", ftype, nb_pole, corner, damping);
					}
				 else if (!strcmp (header, "CP"))
					{
					 /* Reading complex pole information */
					 sscanf (line, "%*s %f %f", &r_value, &i_value);

					 if (Flag_PZ == 0)
						{
						 respnb++;

					 	 EXEC SQL INSERT INTO Response (seqresp_id, resp_nb, resp_type, resp_id, unit_in, unit_out, r_type, lddate)
					 	 VALUES (respseq.CURRVAL, :respnb, 'Z', pzseq.NEXTVAL, :unitin, :unitout, 'A', SYSDATE);
						}
						 
					 Flag_PZ++;

					 EXEC SQL INSERT INTO Response_PZ (pz_id, pz_nb, type, r_value, r_error, i_value, i_error, lddate)
					 VALUES (pzseq.CURRVAL, :Flag_PZ, 'P', :r_value, NULL, :i_value, NULL, SYSDATE);

					 printf ("\n\t\t CP : %f\t%f", r_value, i_value);
					}
				 else if (!strcmp (header, "CZ"))
					{
					 /* Reading complex zero information */
					 sscanf (line, "%*s %f %f", &r_value, &i_value);

					 if (Flag_PZ == 0)
						{
						 respnb++;

					 	 EXEC SQL INSERT INTO Response (seqresp_id, resp_nb, resp_type, resp_id, unit_in, unit_out, r_type, lddate)
					 	 VALUES (respseq.CURRVAL, :respnb, 'Z', pzseq.NEXTVAL, :unitin, :unitout, 'A', SYSDATE);
						}
						 
					 Flag_PZ++;

					 EXEC SQL INSERT INTO Response_PZ (pz_id, pz_nb, type, r_value, r_error, i_value, i_error, lddate)
					 VALUES (pzseq.CURRVAL, :Flag_PZ, 'Z', :r_value, NULL, :i_value, NULL, SYSDATE);

					 printf ("\n\t\t CZ : %f\t%f", r_value, i_value);
					}
				 else if (!strcmp (header, "PN"))
					{
					 respnb++;

					 /* Reading polynomial information */
					 sscanf (line, "%*s %s", pname);

					 printf ("\n\t\t Polynomial: %s", pname);

					 /* Retrieving polynomial identifier */
					 pnid = (-1);
					 EXEC SQL SELECT pn_id INTO :pnid
						  FROM	 Response_PN
						  WHERE	 name = :pname;

					 if (pnid == (-1))
						{
						 fprintf (f_err, "\nError: Polynomial [%s] not found.", pname);

						 EXEC SQL ROLLBACK WORK RELEASE;
						 exit(1);
						}
					 else
						{
						 EXEC SQL INSERT INTO Response (seqresp_id, resp_nb, resp_type, resp_id, unit_in, unit_out, r_type, lddate)
						 VALUES (respseq.CURRVAL, :respnb, 'P', :pnid, :unitin, :unitout, 'P', SYSDATE);
						}
					}
				}
			}
		}

	 fgets (line, 1024, f_sensor);
	}


 fprintf (f_err, "\n");
 printf ("\n\n");

 /* Closing error file */
 fclose (f_err);

 /* Closing sensor file */
 fclose (f_sensor);


 /* Disconnect from the database */
 printf ("\n Disconnecting from ORACLE ...\n\n");

 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);

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

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

 /*EXEC SQL ROLLBACK WORK RELEASE;
 exit(1);*/
}