/*
 *  sample2.pc
 *
 *  This program connects to ORACLE, declares and opens a cursor, 
 *  fetches the names, salaries, and commissions of all
 *  salespeople, displays the results, then closes the cursor. 
 */ 

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

#define UNAME_LEN      20 
#define PWD_LEN        40 
 
/*
 * Use the precompiler typedef'ing capability to create
 * null-terminated strings for the authentication host
 * variables. (This isn't really necessary--plain char *'s
 * would work as well. This is just for illustration.)
 */
typedef char asciiz[PWD_LEN]; 

EXEC SQL TYPE asciiz IS STRING(40) REFERENCE; 
asciiz     username; 
asciiz     password; 

struct element_info 
{ 
    char      rec_mnth[3]; 
    char      lvl_type[3];
    char      matemp[6];
}; 



/* Declare function to handle unrecoverable errors. */ 
void sql_error(); 


main() 
{ 
    FILE *parg;
    FILE *pout;
    FILE *pin;
    char fsize[10];
    char *c;

    char fname[50];
    struct element_info *emp_rec_ptr; 
    int rec_cnt=0;
    int x=0;
    static char query[100];
    char wmo_no_arg[7];
    int Iwmo_no=0;
    char rec_year_arg[5];
    int Irec_year=0;
    char hr_group_arg[3];
    int Ihr_group=0;
    char element_arg[10];
    char climvis_tab[30];

/* Pressure level array */
    char pres_array[6][15];
    char *parray;

    int Iyr=0;
    int Imnth=0;
    char jan[6];
    char feb[6];
    char mar[6];
    char apr[6];
    char may[6];
    char jun[6];
    char jul[6];
    char aug[6];
    char sep[6];
    char oct[6];
    char nov[6];
    char dec[6];

/* Set month values to null*/
        strcpy(jan,"");
        strcpy(feb,"");
        strcpy(mar,"");
        strcpy(apr,"");
        strcpy(may,"");
        strcpy(jun,"");
        strcpy(jul,"");
        strcpy(aug,"");
        strcpy(sep,"");
        strcpy(oct,"");
        strcpy(nov,"");
        strcpy(dec,"");

/* Open Arguments file */
        if( (parg=fopen("get_climvis_year.dat","r+"))==NULL)
        {
           printf("Cannot open Arguments file get_climvis_year.dat\n");
        }

/* Read Arguments File */
        fscanf(parg,"%s %s %s", rec_year_arg, hr_group_arg, element_arg);
	fclose(parg);

	Irec_year=atoi(rec_year_arg);
	Ihr_group=atoi(hr_group_arg);

/* Open Output file */
        sprintf(fname,"/cards2/CLIMVIS/CV%s_%s.%s",rec_year_arg, hr_group_arg, element_arg);
        if( (pout=fopen(fname,"w+"))==NULL)
        {
           printf("Cannot open output file %s\n", fname);
        }
        else
        {
           printf("Output file %s is open\n", fname);
        }

/* Set Climvis Table name based upon Element Type */
	if(strcmp(element_arg,"MATEMP")==0)
	   sprintf(climvis_tab,"UA_TEMP_MAXDATA");
	if(strcmp(element_arg,"MITEMP")==0)
	   sprintf(climvis_tab,"UA_TEMP_MINDATA");
	if(strcmp(element_arg,"MARHUM")==0)
	   sprintf(climvis_tab,"UA_RHUM_MAXDATA");
	if(strcmp(element_arg,"MIRHUM")==0)
	   sprintf(climvis_tab,"UA_RHUM_MINDATA");
	if(strcmp(element_arg,"MAWSPD")==0)
	   sprintf(climvis_tab,"UA_WSPD_MAXDATA");
	if(strcmp(element_arg,"MIWSPD")==0)
	   sprintf(climvis_tab,"UA_WSPD_MINDATA");
	if(strcmp(element_arg,"MADPTEMP")==0)
	   sprintf(climvis_tab,"UA_DPTEMP_MAXDATA");
	if(strcmp(element_arg,"MIDPTEMP")==0)
	   sprintf(climvis_tab,"UA_DPTEMP_MINDATA");

fprintf(pout,"LOAD DATA INFILE * APPEND INTO TABLE %s FIELDS TERMINATED BY \"|\"\n", climvis_tab);
fprintf(pout,"(wmo_no, year, pres_lvl, lvl_type, hr_group,\n");
fprintf(pout,"jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec)\nBEGINDATA\n");

/* Connect to ORACLE.*/ 
    strcpy(username, "cards");
    strcpy(password, "upperair");

    EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--");

    EXEC SQL CONNECT :username IDENTIFIED BY :password;
    printf("\nConnected to ORACLE as user: %s\n", username);


/* open station file */
/* Read Data from Input file */
        pin = fopen("wmo_no_list","r+");
        if(pin == NULL)
        {
                printf("Cannot open Input file %s\n", fname);
        }
        else
        {
           do
           {
           c=fgets(fsize,7,pin);
           if(c != NULL)
              {
                strncpy(wmo_no_arg, &fsize[0], 5);
                wmo_no_arg[5] = '\0';

	        Iwmo_no=atoi(wmo_no_arg);
printf("Processing: %s\n", wmo_no_arg);

/* Allocate memory for element_info struct. */ 
    if ((emp_rec_ptr = 
        (struct element_info *) malloc(sizeof(struct element_info))) == 0)
    { 
        fprintf(stderr, "Memory allocation error.\n"); 
        exit(1); 
    } 
 
        strcpy(jan,"-9999");
        strcpy(feb,"-9999");
        strcpy(mar,"-9999");
        strcpy(apr,"-9999");
        strcpy(may,"-9999");
        strcpy(jun,"-9999");
        strcpy(jul,"-9999");
        strcpy(aug,"-9999");
        strcpy(sep,"-9999");
        strcpy(oct,"-9999");
        strcpy(nov,"-9999");
        strcpy(dec,"-9999");

/* Connect to ORACLE. 
    strcpy(username, "cards"); 
    strcpy(password, "upperair"); 
 
    EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--");
 
    EXEC SQL CONNECT :username IDENTIFIED BY :password; 
    printf("\nConnected to ORACLE as user: %s\n", username); 
*/
 
/* Declare the cursor. All static SQL explicit cursors
 * contain SELECT commands. 'salespeople' is a SQL identifier,
 * not a (C) host variable.
 */
      for(x=0;x<=14;x++)
      {
/* Set Pressure Array Values */
        strcpy(pres_array[0],"00100");
        strcpy(pres_array[1],"00200");
        strcpy(pres_array[2],"00300");
        strcpy(pres_array[3],"00500");
        strcpy(pres_array[4],"00700");
        strcpy(pres_array[5],"01000");
        strcpy(pres_array[6],"01500");
        strcpy(pres_array[7],"02000");
        strcpy(pres_array[8],"02500");
        strcpy(pres_array[9],"03000");
        strcpy(pres_array[10],"04000");
        strcpy(pres_array[11],"05000");
        strcpy(pres_array[12],"07000");
        strcpy(pres_array[13],"08500");
        strcpy(pres_array[14],"10000");

	strcpy(emp_rec_ptr->rec_mnth,"99");
	strcpy(emp_rec_ptr->lvl_type,"99");
   	strcpy(query,"");

/*
    	EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--");
	 
    	EXEC SQL CONNECT :username IDENTIFIED BY :password; 
    	printf("\nConnected to ORACLE as user: %s\n", username); 
*/

   	sprintf(query,"SELECT REC_MNTH, LVL_TYPE, %s FROM MONADS_%d WHERE WMO_NO = %d and PRES = '%s' and STTIME = %d ORDER BY REC_MNTH", element_arg, Irec_year, Iwmo_no, pres_array[x], Ihr_group); 

/* printf("%s\n", query); */

/* prepare the sql query using the string above (the "query" string).*/
  	EXEC SQL PREPARE data_select FROM :query;
 
/* delcare a cursor for the prepared query */
  	EXEC SQL DECLARE data_cursor CURSOR FOR data_select;

/* Open the cursor. */
    	EXEC SQL OPEN data_cursor; 
 
/* Loop, fetching all salesperson's statistics.
 * Cause the program to break the loop when no more
 * data can be retrieved on the cursor.
 */
    	EXEC SQL WHENEVER NOT FOUND DO break; 

        for (;;) 
        { 
        EXEC SQL FETCH data_cursor INTO :emp_rec_ptr; 

	   Imnth=atoi(emp_rec_ptr->rec_mnth);

           if(Imnth==1)
           {
              strcpy(jan,emp_rec_ptr->matemp);
           }
   
           if(Imnth==2)
           {
              strcpy(feb,emp_rec_ptr->matemp);
           }
   
           if(Imnth==3)
           {
              strcpy(mar,emp_rec_ptr->matemp);
           }
   
           if(Imnth==4)
           {
              strcpy(apr,emp_rec_ptr->matemp);
           }
   
           if(Imnth==5)
           {
              strcpy(may,emp_rec_ptr->matemp);
           }
   
           if(Imnth==6)
           {
              strcpy(jun,emp_rec_ptr->matemp);
           }
   
           if(Imnth==7)
           {
              strcpy(jul,emp_rec_ptr->matemp);
           }
   
           if(Imnth==8)
           {
              strcpy(aug,emp_rec_ptr->matemp);
           }
   
           if(Imnth==9)
           {
              strcpy(sep,emp_rec_ptr->matemp);
           }

           if(Imnth==10)
           {
              strcpy(oct,emp_rec_ptr->matemp);
           }
   
           if(Imnth==11)
           {
              strcpy(nov,emp_rec_ptr->matemp);
           }
   
           if(Imnth==12)
           {
              strcpy(dec,emp_rec_ptr->matemp);
           }

       } 
/* Close the cursor. */
       EXEC SQL CLOSE data_cursor; 

/* set Values to missing where null */
	if(strcmp(emp_rec_ptr->lvl_type,"")==0)
	   strcpy(emp_rec_ptr->lvl_type,"99");

/* set Values to missing where null */
	if( (strcmp(jan,"9999 ")==0) || (strcmp(jan,"0999 ")==0) || (strcmp(jan,"09999")==0) || (strcmp(jan,"99999")==0) )
	   strcpy(jan,"-9999");
	if( (strcmp(feb,"9999 ")==0) || (strcmp(feb,"0999 ")==0) || (strcmp(feb,"09999")==0) || (strcmp(feb,"99999")==0) )
	   strcpy(feb,"-9999");
	if( (strcmp(mar,"9999 ")==0) || (strcmp(mar,"0999 ")==0) || (strcmp(mar,"09999")==0) || (strcmp(mar,"99999")==0) )
	   strcpy(mar,"-9999");
	if( (strcmp(apr,"9999 ")==0) || (strcmp(apr,"0999 ")==0) || (strcmp(apr,"09999")==0) || (strcmp(apr,"99999")==0) )
	   strcpy(apr,"-9999");
	if( (strcmp(may,"9999 ")==0) || (strcmp(may,"0999 ")==0) || (strcmp(may,"09999")==0) || (strcmp(may,"99999")==0) )
	   strcpy(may,"-9999");
	if( (strcmp(jun,"9999 ")==0) || (strcmp(jun,"0999 ")==0) || (strcmp(jun,"09999")==0) || (strcmp(jun,"99999")==0) )
	   strcpy(jun,"-9999");
	if( (strcmp(jul,"9999 ")==0) || (strcmp(jul,"0999 ")==0) || (strcmp(jul,"09999")==0) || (strcmp(jul,"99999")==0) )
	   strcpy(jul,"-9999");
	if( (strcmp(aug,"9999 ")==0) || (strcmp(aug,"0999 ")==0) || (strcmp(aug,"09999")==0) || (strcmp(aug,"99999")==0) )
	   strcpy(aug,"-9999");
	if( (strcmp(sep,"9999 ")==0) || (strcmp(sep,"0999 ")==0) || (strcmp(sep,"09999")==0) || (strcmp(sep,"99999")==0) )
	   strcpy(sep,"-9999");
	if( (strcmp(oct,"9999 ")==0) || (strcmp(oct,"0999 ")==0) || (strcmp(oct,"09999")==0) || (strcmp(oct,"99999")==0) )
	   strcpy(oct,"-9999");
	if( (strcmp(nov,"9999 ")==0) || (strcmp(nov,"0999 ")==0) || (strcmp(nov,"09999")==0) || (strcmp(nov,"99999")==0) )
	   strcpy(nov,"-9999");
	if( (strcmp(dec,"9999 ")==0) || (strcmp(dec,"0999 ")==0) || (strcmp(dec,"09999")==0) || (strcmp(dec,"99999")==0) )
	   strcpy(dec,"-9999");

           fprintf(pout,"%s|%d|%s|%s|%s|%4s|%4s|%4s|%4s|%4s|%4s|%4s|%4s|%4s|%4s|%4s|%4s\n", wmo_no_arg, Irec_year, pres_array[x], emp_rec_ptr->lvl_type, hr_group_arg, jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec); 

/* Set month values to missing */
        strcpy(jan,"-9999");
        strcpy(feb,"-9999");
        strcpy(mar,"-9999");
        strcpy(apr,"-9999");
        strcpy(may,"-9999");
        strcpy(jun,"-9999");
        strcpy(jul,"-9999");
        strcpy(aug,"-9999");
        strcpy(sep,"-9999");
        strcpy(oct,"-9999");
        strcpy(nov,"-9999");
        strcpy(dec,"-9999");
   
/*
	EXEC SQL COMMIT WORK RELEASE;
*/

/* end pressure FOR loop */
       }

/* End c not Null IF */
           }

/* End DO */
        } while(c != NULL);

/* End IF file open */
   }


printf("done\n");
 
    EXEC SQL COMMIT WORK RELEASE; 
    exit(0); 
} 



void 
sql_error(msg) 
char *msg;
{ 
    char err_msg[512];
    size_t buf_len, msg_len;

    EXEC SQL WHENEVER SQLERROR CONTINUE;

    printf("\n%s\n", msg);

/* Call sqlglm() to get the complete text of the
 * error message.
 */
    buf_len = sizeof (err_msg);
    sqlglm(err_msg, &buf_len, &msg_len);
    printf("%.*s\n", msg_len, err_msg);

    EXEC SQL ROLLBACK RELEASE;
    exit(1);
}