/*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*/
/* Populate_Datalogger.pc                              */
/* 	Load to the NCEDC database the datalogger      */
/*      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;
  char	type[81];	/* Datalogger type */
  char	sn[81];		/* Serial number */
  char	firm[81];	/* Firmware number */
  char	s_dat[81];	/* Start date */
  char	e_dat[81];	/* End date */
  int	nb_ndm;		/* Number of boards */

  char	msn[81];	/* Board serial number */
  int	nb_nch;		/* Number of modules */
  char	firmb[81];	/* Board Firmware number */

  double	sens;		/* Sensitivity */
  char		firmm[81];	/* Module Firmware 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);
}
	


						  /*-*-*-*-*-*-*-*-*/
						 /* Main function */
						/*-*-*-*-*-*-*-*-*/
main (int argc, char* argv[])
{
 FILE*	f_datlog; 	/* Datalogger 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 <Datalogger .tbl file>\n\n", argv[0]);

	 exit (0);
	}

 /* Opening the datalogger file */
 if ((f_datlog = 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 datalogger file */
 fgets (line, 1024, f_datlog);

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

	 if (!strcmp (header, "DATLOG"))
		{
		 /* Reading datalogger information */
		 sscanf (line, "%*s %s %s %s %s %s %d", type, sn, firm, sdat, edat, &nb_ndm);

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

		 /* Inserting datalogger information into the database */
		 EXEC SQL INSERT INTO Datalogger (data_id, data_type, serial_nb, firmware_nb, ondate, offdate, nb_board, word_32, word_16, lddate)
		 VALUES (dataseq.NEXTVAL, :type, :sn, :firm, :s_dat, :e_dat, :nb_ndm, 3210, 10, SYSDATE);

		 printf ("\n Datalogger : %s\t%s\t%s\t%s\t%s\t%d", type, sn, firm, sdat, edat, nb_ndm);

		 for (i=1;i<=nb_ndm;i++)
			{
			 fgets (line, 1024, f_datlog);

			 /* Reading board information */
			 strcpy (firmb, "");
			 sscanf (line, "%*s %*s %s %d %s", msn, &nb_nch, firmb);

			 /* Firmware Nb? */
			 if (!strcmp (firmb, ""))
				strcpy (firmb, firm);

			 /* Inserting board information into the database */
			 EXEC SQL INSERT INTO Datalogger_Board (data_id, board_nb, serial_nb, firmware_nb, nb_module, lddate)
			 VALUES (dataseq.CURRVAL, :i, :msn, :firmb, :nb_nch, SYSDATE);

			 printf ("\n\t Board : %s\t%d", msn, nb_nch);

			 for (j=1;j<=nb_nch;j++)
				{
				 fgets (line, 1024, f_datlog);
				 
				 /* Reading module information */
				 strcpy (firmm, "");
				 sscanf (line, "%*s %*s %lf %s", &sens, firmm);

				 /* Firmware Nb? */
				 if (!strcmp (firmm, ""))
					strcpy (firmm, firmb);

	 			 /* Inserting board information into the database */
			 	 EXEC SQL INSERT INTO Datalogger_Module (data_id, board_nb, module_nb, serial_nb, firmware_nb, sensitivity, lddate)
			 	 VALUES (dataseq.CURRVAL, :i, :j, NULL, :firmm, :sens, SYSDATE);

			 	 printf ("\n\t Module : %lf", sens);
				}
			}
		}

	 fgets (line, 1024, f_datlog);
	}


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

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

 /* Closing datalogger file */
 fclose (f_datlog);


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