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

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

#define	CONNECT_STRING	"user/passwd@db"
#define	SENSOR_TYPE	"/data/09/stephane/DBMS_Population/Instrument_Responses/HT/Sensor/sensor_type.txt"

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;

  char		user_pwd[80];

  int		i;
  int		j;
  int		k;
  char		sid[51];	/* 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 */
  double	sens;		/* Sensitivity */
  double	freq;		/* Frequency */
  int		nb_line;	/* Number of response lines */

  char		ftype[32];	/* Filter type */
  int		nb_pole;	/* Number of poles */
  double	corner;		/* Corner frequency */
  double	damping;	/* Damping value */
  double	r_value;	/* Real part of a complex pole/zero */
  double	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 (char d[16])
{
 EXEC SQL BEGIN DECLARE SECTION;
 static 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);
}

				   /*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*/
				  /*  Function that searches the sensor detailled description      */
				 /*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*/
char*	SearchType (FILE *f, char s[51])
{
 char	line[256];
 char	t1[51];
 char	t2[51];

 /* Rewinding file */
 rewind (f);

 fgets (line, 255, f);
 while (!feof (f))
	{
	 sscanf (line, "%s %s", t1, t2);

	 /* Comparing sensor types */
	 if (!strcmp (t1, s))
		return (strstr (line+strlen (t1), t2));

	 fgets (line, 255, f);
	}

 return (s);
}



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

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

 
 if (argc != 2)
	{
	 printf ("\n %s <Sensor .tbl file>\n\n", argv[0]);

	 exit (0);
	}

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

	 exit (0);
	}

 /* 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 type file */
 if ((f_type = fopen (SENSOR_TYPE, "rt")) == NULL)
	{
	 printf ("\n Error [File (%s) could not be opened].\n\n", SENSOR_TYPE);

	 exit (0);
	}

 /* Connect to ORACLE. */
 EXEC SQL WHENEVER SQLERROR DO sql_error();
 strcpy (user_pwd, CONNECT_STRING);
 EXEC SQL CONNECT :user_pwd;

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

		 /* Translating sensor type */
		 strcpy (sid, SearchType (f_type, sid));
		 if (sid[strlen (sid) - 1] == '\n')
			sid[strlen (sid) - 1] = '\0';

		 /* 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 %lf %lf %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%lf\t%lf\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 (sqlca.sqlcode == (-2112))
				{
				 fprintf (f_err, "\nError: Multiple entries for unit 'name=%s' in table D_Unit.\n\n", 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);
				}

			 /* Digital Sensor? */
			 if (!strcmp (stype, "X"))
				{
			 	 EXEC SQL SELECT id INTO :unitout FROM D_Unit WHERE name = 'COUNTS';

				 if (sqlca.sqlcode == (-2112))
					{
					 fprintf (f_err, "\nError: Multiple entries for unit 'name=COUNTS' in table D_Unit.\n\n");
					}


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

				 	 EXEC SQL INSERT INTO D_Unit (id, name, description)
				 	 VALUES (:unitout, 'COUNTS', NULL);
					}
				}
			 else
                                { 
                                 EXEC SQL SELECT id INTO :unitout FROM D_Unit WHERE name = 'V';

				 if (sqlca.sqlcode == (-2112))
					{
					 fprintf (f_err, "\nError: Multiple entries for unit 'name=V' in table D_Unit.\n\n");
					}

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

			 if (nb_line == 0)
				{
				 /* 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, 1, 'N', 0, :unitin, :unitout, 'A', SYSDATE);

				}
			 else	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 %lf %lf", 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%lf\t%lf", ftype, nb_pole, corner, damping);
					}
				 else if (!strcmp (header, "LP"))
					{
					 respnb++;

					 /* Reading low-pass filter information */
					 sscanf (line, "%*s %s %d %lf %lf", 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%lf\t%lf", ftype, nb_pole, corner, damping);
					}
				 else if (!strcmp (header, "CP"))
					{
					 /* Reading complex pole information */
					 sscanf (line, "%*s %lf %lf", &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 : %lf\t%lf", r_value, i_value);
					}
				 else if (!strcmp (header, "CZ"))
					{
					 /* Reading complex zero information */
					 sscanf (line, "%*s %lf %lf", &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 : %lf\t%lf", 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 (sqlca.sqlcode == (-2112))
						{
						 fprintf (f_err, "\nError: Multiple entries for Polynomial 'name=%s' in table Response_PN.\n\n", 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);

 /* Closing sensor type file */
 fclose (f_type);


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

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