/*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*/
/* Populate_Filamp.pc                                  */
/* 	Load to the NCEDC database the filter-         */
/*      amplifier information.			       */
/*      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"

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		paid[32];	/* Filter-Amplifier identification */
  char		sn[32];		/* Serial number */
  char		s_dat[32];	/* Start date */
  char		e_dat[32];	/* End date */
  int		nb_pchan;	/* Number of physical channels */

  double	gain;		/* Gain */
  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 */
  char		ptype[32];	/* Polynomial type */
  double	lower;		/* Polynomial lower bound */
  double	upper;		/* Polynomial upper bound */
  double	maxerror;	/* Maximum error of polynomial approximation */
  int		nb_coeff;	/* Number of coefficients */
  double	pncoeff;	/* Polynomial coefficients */
  int		respnb;		/* Response number */
  int		unitin;		/* Input units */
  int		unitout;	/* Output units */

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

	


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

 char	sdat[16];	/* Start date */
 char	edat[16];	/* End date */
 double	pn_coeff[21];	/* Polynomial coefficients */

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

	 exit (0);
	}

 /* Opening the filter-amplifier file */
 if ((f_filamp = 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);
	}

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

 strcpy (user_pwd, CONNECT_STRING);

 EXEC SQL CONNECT :user_pwd;

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


 /* Parsing filter-amplifier file */
 fgets (line, 1024, f_filamp);

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

	 if (!strcmp (header, "PREAMP"))
		{
		 /* Reading filter-amplifier information */
		 sscanf (line, "%*s %s %s %s %s %d", paid, sn, sdat, edat, &nb_pchan);

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

		 /* Inserting filter-amplifier information into the database */
		 EXEC SQL INSERT INTO Filamp (filamp_id, name, serial_nb, ondate, offdate, nb_pchannel, lddate)
		 VALUES (filampseq.NEXTVAL, :paid, :sn, :s_dat, :e_dat, :nb_pchan, SYSDATE);

		 printf ("\n Filter-Amplifier : %s\t%s\t%s\t%s\t%d", paid, sn, s_dat, e_dat, nb_pchan);

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

			 fgets (line, 1024, f_filamp);

			 /* Reading channel information */
			 sscanf (line, "%*s %*s %lf %lf %d", &gain, &freq, &nb_line);

			 printf ("\n\t Channel : %lf\t%lf\t%d", gain, freq, nb_line);

			 if (freq < 0.)
				freq = (-1.)/freq;

			 /* Inserting channel information into the database */
			 if (nb_line == 0)
				{
			 	 EXEC SQL INSERT INTO Filamp_PChannel (filamp_id, pchannel_nb, gain, frequency, seqresp_id, lddate)
			 	 VALUES (filampseq.CURRVAL, :i, :gain, :freq, NULL, SYSDATE);
				}
			 else
				{
				 EXEC SQL INSERT INTO Filamp_PChannel (filamp_id, pchannel_nb, gain, frequency, seqresp_id, lddate)
			 	 VALUES (filampseq.CURRVAL, :i, :gain, :freq, respseq.NEXTVAL, SYSDATE);

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

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

			 	 unitin = unitout;
				}

			 for (j=1;j<=nb_line;j++)
				{
				 fgets (line, 1024, f_filamp);
				 
				 /* 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 %lf %lf %lf %d", ptype, &lower, &upper, &maxerror, &nb_coeff);

					 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', pnseq.NEXTVAL, :unitin, :unitout, 'A', SYSDATE);

					 EXEC SQL INSERT INTO Response_PN (pn_id, poly_type, lower_bound, upper_bound, max_error, nb_coeff, lddate)
					 VALUES (pnseq.CURRVAL, :ptype, :lower, :upper, :maxerror, :nb_coeff, SYSDATE);

					 printf ("\n\t\t PN : %s\t%lf\t%lf\t%lf\t%d", ptype, lower, upper, maxerror, nb_coeff);

					 /* Reading polynomial coefficients */
					 sscanf (line, "%*s %*s %*s %*s %*s %*s %lf %lf %lf %lf %lf %lf %lf %lf %lf %lf %lf %lf %lf %lf %lf %lf %lf %lf %lf %lf", &pn_coeff[0], &pn_coeff[1], &pn_coeff[2], &pn_coeff[3], &pn_coeff[4], &pn_coeff[5], &pn_coeff[6], &pn_coeff[7], &pn_coeff[8], &pn_coeff[9], &pn_coeff[10], &pn_coeff[11], &pn_coeff[12], &pn_coeff[13], &pn_coeff[14], &pn_coeff[15], &pn_coeff[16], &pn_coeff[17], &pn_coeff[18], &pn_coeff[19]);

					 printf ("\n\t\t\t Coefficients : ");
					 for (k=0;k<nb_coeff;k++)
						{
						 pncoeff = pn_coeff[k];

						 EXEC SQL INSERT INTO Response_PN_Data (pn_id, pn_nb, pn_value)
						 VALUES (pnseq.CURRVAL, :k+1, :pncoeff);
	
						 printf ("%lf\t", pn_coeff[k]);
						}
					}
				}
			}
		}

	 fgets (line, 1024, f_filamp);
	}


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

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

 /* Closing filter-amplifier file */
 fclose (f_filamp);


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