/*
 *  This C program will read an DSPJRN outfile of *TYPE1 and return files which
 *  do only puts, and not updates or deletes (could have SEQONLY(*YES) enabled),
 *  as well as the number of puts (PT, PX) they do.
 *
 *  The following is an example of a DSPJRN command with the options
 *     required:
 *       DSPJRN JRN(LIB/JRN) OUTPUT(*OUTFILE) OUTFILFMT(*TYPE1)
 *            OUTFILE(LIB/OUTFILE) INCHIDENT(*YES)
 *
 *  Syntax:
 *     SEQONLY lib file
 *          lib - library
 *          outfile - dspjrn outfile
 *	      viewlib - view library
 *	      view - view
 *
 *  Compile Statement:
        CRTSQLCI OBJ(LIB/SEQONLY)
                 SRCFILE(LIB/SEQONLY)
                 COMMIT(*NONE) OBJTYPE(*PGM)
 *
 *  Run Statement:
	  CALL LIB/SEQONLY PARM('LIB' 'OUTFILE' 'VIEWLIB' 'VIEW' 'OUTLIB' 'OUTPUT')
 *
 *  Sample SQL query to retrieve all results, ordered from greatest number of
 *  puts to least:
	  SELECT * FROM OUTLIB/OUTPUT ORDER BY NUMPTS DESC
 */


/* include the necessary C header files */
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <ctype.h>

/* Statements required for embedded SQL */
EXEC sql include SQLCA;
EXEC sql include SQLDA;

/* Begin main function */
int main(int argc, char *argv[])
{

  EXEC SQL BEGIN DECLARE SECTION;

  int i;   /* a simple counter */

  long int numents = 0;  /* number of entries in outfile */
  long int numobjs = 0;  /* number of obj/lib/mbr/job types in outfile */
  long int numupd = 0;	 /* number of updates (joentt = 'UB, UP, DL') */
  long int numpts = 0;	 /* number of puts (joentt = 'PT, PX') */
  long int curr_ent = 1; /* current entry being processed */

  char selstmt[200];     /* character arrays to hold our select */
  char sel2stmt[200];	 /* statements */
  char sel3stmt[200];
  char sel4stmt[200];
  char statement[200];
  char lib[11];       /* character array to hold input library name */
  char outfile[11];   /* character array to hold outfile name */
  char viewlib[11];   /* character array to hold the view lib name */
  char view[11];	    /* character array to hold the view name */
  char outlib[11];    /* character array to hold the output file library name */
  char output[11];    /* character array to hold the output file name */
  char objname[11];   /* character array to hold the object name */
  char libname[11];   /* character array to hold the library name */
  char mbrname[11];   /* character array to hold the member name */
  char jobname[11];   /* character array to hold the job name */
  char enttype[3];    /* character array to hold the entry type */

  EXEC SQL
    DECLARE c1 CURSOR FOR exestmt;   /* declare SQL cursor C1 */
  EXEC SQL
    DECLARE c2 CURSOR FOR actstmt;   /* declare SQL cursor C2 */
  EXEC SQL
    DECLARE c3 CURSOR FOR exestmt1;   /* declare SQL cursor C3 */

  EXEC SQL END DECLARE SECTION;

  /******************************************
   *   Parsing command line / error checking
   ******************************************/

  if (argc != 7)
  {
    printf("ERROR - proper syntax is: ");
    printf("SEQONLY lib outfile viewlib view outlib outputfile");
  }
  else
  {
    /* extract the library from the first argument */
    sprintf(lib, "%s", argv[1]);
    /* extract the outfile from the second argument */
    sprintf(outfile, "%s", argv[2]);
    /* extract the view library from the third argument */
    sprintf(viewlib, "%s", argv[3]);
    /* extract the view from the fourth argument */
    sprintf(view, "%s", argv[4]);
    /* extract the output library from the fifth argument */
    sprintf(outlib, "%s", argv[5]);
    /* extract the output file from the sixth argument */
    sprintf(output, "%s", argv[6]);

    /* convert the lib, outfile, view lib, and view to upper case */
    for (i = 0; i < 10; i++)
    {
       lib[i] = toupper(lib[i]);
       outfile[i] = toupper(outfile[i]);
       viewlib[i] = toupper(viewlib[i]);
       view[i] = toupper(view[i]);
       outlib[i] = toupper(outlib[i]);
       output[i] = toupper(output[i]);
    }

    /* create the view with the obj/lib/mbr/job quads */
    sprintf(selstmt, "CREATE VIEW %s/%s AS SELECT JOOBJ, ",
	viewlib, view);
    sprintf(sel2stmt, "JOLIB, JOMBR, JOJOB FROM %s/%s ",
	lib, outfile);
    sprintf(sel3stmt, " GROUP BY JOOBJ, JOLIB, JOMBR, JOJOB");
    sprintf(statement, "%s%s%s", selstmt, sel2stmt, sel3stmt);
    EXEC SQL
	EXECUTE IMMEDIATE :statement;

    /* count the number of obj/file pairs in the view */
    sprintf(selstmt, "SELECT COUNT(*) FROM %s/%s", viewlib, view);
    EXEC SQL
      PREPARE actstmt FROM :selstmt;
    EXEC SQL
      OPEN c2;                      /* open the SQL view (cursor) */
    EXEC SQL
      FETCH c2 INTO :numobjs;       /* set the number of obj/lib/mbr/job objects
    EXEC SQL
      CLOSE c2;                     /* close the SQL cursor */

    /* output the number of Entries to the screen */
    printf("\n\n\n\n\n\n\n");
    printf("Number of obj/lib/mbr/jobs = %d\n", numobjs);

    /* Create SQL query which will return the obj/lib/mbr/job names and number
     * of puts if the obj/lib/mbr/job has only 'PT' or 'PX' entries.
     */
    sprintf(selstmt, "SELECT JOOBJ, JOLIB, JOMBR, JOJOB FROM %s/%s",
	 viewlib, view);
    EXEC SQL
      PREPARE exestmt FROM :selstmt;
    EXEC SQL
      OPEN c1;                      /* open the SQL view (cursor) */

    /* create the table in which output will be placed */
    sprintf(selstmt, "CREATE TABLE %s/%s (OBJ CHAR(10), ",
	outlib, output);
    sprintf(sel2stmt, "LIB CHAR(10), MBR CHAR(10), JOB ");
    sprintf(sel3stmt, "CHAR(10), NUMPTS INT)");
    sprintf(statement, "%s%s%s", selstmt, sel2stmt,
	 sel3stmt);
    EXEC SQL
	EXECUTE IMMEDIATE :statement;

    /* loop through each entry in the outfile */
    while(curr_ent <= numobjs)
    {
	numupd = 0;
	numpts = 0;
      /* extract the obj/lib/mbr/job names */
      EXEC SQL
        FETCH c1 INTO :objname, :libname, :mbrname, :jobname;

	/* count the number of entries in a obj/lib/job/mbr that */
        /*     are updates or deletes */
      sprintf(selstmt, "SELECT COUNT(*) FROM %s/%s WHERE ", lib, outfile);
      sprintf(sel2stmt, "JOOBJ='%s' AND JOLIB='%s' AND JOMBR",
	   objname, libname);
      sprintf(sel3stmt, "='%s' AND JOJOB='%s' AND (JOENTT", mbrname, jobname);
      sprintf(sel4stmt, "='UB' or JOENTT='UP' or JOENTT='DL')");
      sprintf(statement, "%s%s%s%s", selstmt, sel2stmt, sel3stmt, sel4stmt);

	EXEC SQL
	  PREPARE exestmt1 FROM :statement;
	EXEC SQL
	  OPEN c3;
	EXEC SQL
	  FETCH c3 INTO :numupd;
      EXEC SQL
	  CLOSE c3;

	/* count the number of entries in a obj/lib/job/mbr that are puts */
	sprintf(selstmt, "SELECT COUNT(*) FROM %s/%s ", lib, outfile);
	sprintf(sel2stmt, "WHERE (JOENTT='PT' OR JOENTT='PX') AND ");
	sprintf(sel3stmt, "JOOBJ='%s' AND JOLIB='%s' AND JOJOB='%s'",
	   objname, libname, jobname);
        sprintf(sel4stmt, " AND JOMBR='%s'", mbrname);
	sprintf(statement, "%s%s%s%s", selstmt, sel2stmt, sel3stmt, sel4stmt);

	EXEC SQL
	  PREPARE exestmt1 FROM :statement;
	EXEC SQL
	  OPEN c3;
	EXEC SQL
	  FETCH c3 INTO :numpts;
      EXEC SQL
        CLOSE c3;

	/* if there are puts, but no updates/deletes,
	 * print the objname, library, and numpts
	 */
	if(numpts > 0 && numupd == 0)
	{
	  sprintf(selstmt, "INSERT INTO %s/%s ", outlib, output);
	  sprintf(sel2stmt, "VALUES('%s', '%s', '%s', '%s', %d)",
	    objname, libname, mbrname, jobname, numpts);
	  sprintf(statement, "%s%s", selstmt, sel2stmt);
	  EXEC SQL
	    EXECUTE IMMEDIATE :statement;
	}
	
	curr_ent++;
    }
    EXEC SQL
      CLOSE c1;
  }
  return 0;
}