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

#include <qlib2.h>

#define VERSION "1.3 (2002.017)"

char *syntax[] = {
"%s version " VERSION,
"%s  [-b] [-f date] [-t date | -s interval] [-F netdc | breq_fast]",
"    [-S station] [-C channel] [-N network] [-L location]",
"    [-U dbuserid] [-D j|m] [-h] [-d n] [-a] [-q]",
"    where:",
"	-b	    Bounds. Search for first and last time.",
"	-f date	    From date - ignore data before this date.",
"	-t date	    To date - ignore data from this date on.",
"		    Date can be in formats:",
"			yyyy/mm/dd/hh:mm:ss.ffff",
"			yyyy/mm/dd.hh:mm:ss.ffff",
"			yyyy/mm/dd,hh:mm:ss.ffff",
"			yyyy.ddd,hh:mm:ss.ffff",
"			yyyy,ddd,hh:mm:ss.ffff",
"		    You may leave off any trailing 0 components of the time.",
"	-s interval span interval.  Alternate way of specifying end time.",
"		    Interval can be an integer followed immediately by",
"		    S, M, H, d, m, y (Y) for seconds, minutes, hours, days,",
"		    month, or years.",
"	-F format   Output format:  default, netdc, breq_fast.",
"	-v 	    Verbose (detail) info about all time intervals.",
"	-S station  Set the 5 char station name to the specified station name.",
"	-C channel  Set the 3 char channel name to the specified channel name.",
"	-N network  Set the 2 char network code (eg BK).",
"	-L location Set the 2 char location code (normally blank).",
"	-U dbuserid Specify the dbuserid to use for connection to the database.",
"	-D j | m    Display dates in julian or month and day format.",
"	-A	    Aggregate consecutive waveform entries together.",
"	-h	    Help - prints syntax message.",
"	-d n	    Debug output (OR of any of the following values:)",
"			1 = print sql cmd.",
"	-a	    Show ALL database entries satisfying query, not just distinct ones.",
"	-q	    Qmerge compatible.  Show waveform time to next expected sample.",
"Note:  3 modes of execution:",
"1.  %s -b [stream_specifiers]",
"    returns time of first and last waveform for specified streams in database.",
"2.  %s -f start -t end [stream_specifiers]",
"    returns availability of data by day from start to end time",
"    for specified streams.",
"3.  %s -v -f start -t end [stream_specifiers]",
"    returns detailed timespans of available data for the specified streams.",
"[stream_specifiers] are:",
"	[-S stations] [-N networks] [-C channels] [-L locations]",
"Any component of the stream specifier not specified is assumed to be '*'.",
NULL };

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];

double	twstime;	/* Start time */
double	twetime;	/* End time */
double	samprate;	/* Sample rate (samples/second) */
char	sta[7];
char	chan[4];
char 	loc[3];
char	net[3];

char 	SQL_Command[65536];

typedef struct _wfinfo {	double	twstime;	/* Start time */
	double	twetime;	/* End time */
	double	samprate;	/* Sample rate (samples/second) */
	double	samptime;	/* Time of 1 sample */
	int	output_now;	/* Output flag */
	char	sta[7];
	char	chan[4];
	char 	loc[3];
	char	net[3];
} WFINFO;

WFINFO curr, prev;

EXEC SQL END DECLARE SECTION;

EXEC SQL DECLARE sql_stmt    STATEMENT;
EXEC SQL DECLARE sql_stmt2    STATEMENT;
EXEC SQL DECLARE sql_stmt3    STATEMENT;

long      SQLCODE;

void	sql_error();	/* Handles unrecoverable errors */

#ifndef	DEFAULT_DBUSERID
#define	DEFAULT_DBUSERID	"netdc/dB2Usr@ncedc"
#endif
#ifndef	DEFAULT_NEWORK
#define	DEFAULT_NETWORK		"BK"
#endif

#define	END_OF_QUERY	1403

/*  TWINDOW is defined to be longer (in seconds) than any waveform	*/
/*  segment in the database.  It must be longer than 1 day, since we	*/
/*  break broadband data into daily records.				*/
/*  TWINDOW has been changed to a month due to the UL Network           */
#define	TWINDOWDAILY		86401
#define TWINDOWMONTHLY		2678401
#define	DEFAULT_FMT	0
#define	NETDC_FMT	1
#define	BREQ_FAST_FMT	2

#define	DEBUG
#define	DEBUG_SQL	1
#define	DEBUG_ANY	\
    (DEBUG_SQL)
#define	    debug(val)  (debug_option & (val))

typedef struct _listhead {
    char **name;
    int n;
} LISTHEAD;
LISTHEAD station;
LISTHEAD channel;
LISTHEAD location;
LISTHEAD network;

char *cmdname;
int aggregate = 0;
int date_fmt = 0;
char	stime[40];	/* Start time */
char	etime[40];	/* End time */
int	qmerge_flag;

INT_TIME start_day (INT_TIME it)
{
    EXT_TIME et;
    et = int_to_ext(it);
    et.hour = et.minute = et.second = et.usec = 0;
    et = normalize_ext(et);
    it = ext_to_int(et);
    return (it);
}

INT_TIME next_day (INT_TIME it)
{
    EXT_TIME et;
    et = int_to_ext(it);
    et.hour = et.minute = et.second = et.usec = 0;
    ++et.doy;
    et = normalize_ext(et);
    it = ext_to_int(et);
    return (it);
}

LISTHEAD add_to_list (LISTHEAD l, char *clist)
{
    char *str = strdup(clist);
    char *p = str;
    char *pc;
    char *e;
    while (p != NULL && *p != 0) {
	e = strchr(p,',');
	if (e != NULL) *e = '\0';
	if (l.name == NULL) l.name = (char **)malloc((l.n+1) * sizeof(char **));
	else l.name = (char **)realloc(l.name, (l.n+1) * sizeof(char **));
	l.name[l.n] = strdup(p);
	for (pc=l.name[l.n]; *pc; pc++) {
	    /* 1.  Replace "-" with blank (for location code). */
	    if (*pc == '-') *pc = ' ';
	    /* 2.  Replace "*" with "%" for unix->sql 0-n char wildcard. */
	    if (*pc == '*') *pc = '%';
	    /* 3.  Replace "?" with "_" for unix->sql 1 char wildcard. */
	    if (*pc == '?') *pc = '_';
	}
	l.n++;
	p = (e!=NULL) ? e+1 : NULL;
    }
    free(str);
    return(l);
}

#define SAMPRATE_IS(sr)	((d=samprate*(double)sr) > .99 && d < 1.01)
double samptime_from_samprate (double samprate)
{
    double samptime, d;
    if (samprate >= 1.0) return (1.0/samprate);
    /* Look for common sample rates in seconds per sample.	*/
    if (SAMPRATE_IS(10.0)) return (10.0);
    if (SAMPRATE_IS(30.0)) return (30.0);
    if (SAMPRATE_IS(60.0)) return (60.0);
    if (SAMPRATE_IS(100.0)) return (100.0);
    if (SAMPRATE_IS(120.0)) return (120.0);
    if (SAMPRATE_IS(300.0)) return (300.0);
    if (SAMPRATE_IS(600.0)) return (600.0);
    if (SAMPRATE_IS(900.0)) return (900.0);
    if (SAMPRATE_IS(1080.0)) return (1080.0);
    if (SAMPRATE_IS(1200.0)) return (1200.0);
    if (SAMPRATE_IS(1800.0)) return (1800.0);
    if (SAMPRATE_IS(3600.0)) return (3600.0);
    if (SAMPRATE_IS(14400.0)) return (14400.0);
    return (1.0/samprate);
}

void output_entry (WFINFO curr, int output_fmt, double tstart_time, double tend_time)
{
    EXT_TIME et1, et2;
    double samptime;

	    if (output_fmt == NETDC_FMT) {
		/* NETDC output format.		*/
		/* Limit start and end times to requested time period. */
		if (curr.twstime < tstart_time) curr.twstime = tstart_time;
		if (curr.twetime > tend_time)   curr.twetime = tend_time;
		samptime = (qmerge_flag) ? samptime_from_samprate(curr.samprate) : 0;
		et1 = int_to_ext(tepoch_to_int(curr.twstime));
		et2 = int_to_ext(tepoch_to_int(curr.twetime+samptime));

		/* Converting location code */
		if (!strcmp (curr.loc, "  "))
			strcpy (curr.loc, "--");

		printf (".DATA * %-2s %-5s \"%-2s\" %-3s "
			"\"%04d %02d %02d %02d %02d %02d.%04d\" "
			"\"%04d %02d %02d %02d %02d %02d.%04d\"\n", 
			curr.net, curr.sta, curr.loc, curr.chan,
			et1.year, et1.month, et1.day, et1.hour, et1.minute, et1.second, 
			roundoff((double)et1.usec/USECS_PER_TICK),
			et2.year, et2.month, et2.day, et2.hour, et2.minute, et2.second,
			roundoff((double)et1.usec/USECS_PER_TICK));
	    }
	    else if (output_fmt == BREQ_FAST_FMT) {
		/* BREQ_FAST output format.	*/
		/* Limit start and end times to requested time period. */
		if (curr.twstime < tstart_time) curr.twstime = tstart_time;
		if (curr.twetime > tend_time)   curr.twetime = tend_time;
		samptime = (qmerge_flag) ? samptime_from_samprate(curr.samprate) : 0;
		et1 = int_to_ext(tepoch_to_int(curr.twstime));
		et2 = int_to_ext(tepoch_to_int(curr.twetime+samptime));
		printf ("%-5s %-2s %04d %02d %02d %02d %02d %02d.%01d %04d %02d %02d %02d %02d %02d.%01d 1 %s\n", 
			curr.sta, curr.net,
			et1.year, et1.month, et1.day, et1.hour, et1.minute, et1.second,
			roundoff((double)et1.usec/USECS_PER_SEC/10),
			et2.year, et2.month, et2.day, et2.hour, et2.minute, et2.second,
			roundoff((double)et2.usec/USECS_PER_SEC/10),
			curr.chan);
	    }
	    else {
		/* Generic output format.	*/
		/* Do NOT Limit start and end times to requested time period. */
		double twetime;
		/* Look for specific known sample rates. */
		samptime = (qmerge_flag) ? samptime_from_samprate(curr.samprate) : 0;
/*::		printf ("samprate = %.15lf, samptime = %.15lf\n", curr.samprate, samptime); */
		strcpy(stime, time_to_str(tepoch_to_int(curr.twstime),date_fmt));
		strcpy(etime, time_to_str(tepoch_to_int(curr.twetime+samptime),date_fmt));
		printf ("%-5s %-2s %-3s %-2s %s %s\n", 
			curr.sta, curr.net, curr.chan, curr.loc, stime, etime);
	    }
    curr.output_now = 0;
    return;
}

/************************************************************************/
/*	Main procedure.							*/
/************************************************************************/
main (int argc, char* argv[])
{
 int	i, j, k, l;
 char 	c_temp[32768];	/* Temporary string */
 char 	c_temp2[32768];	/* Temporary string */

 int	Flag_bound	= 0;
 int	Flag_detail	= 0;
 int	output_fmt	= DEFAULT_FMT;

    INT_TIME start_time;
    INT_TIME end_time;
    INT_TIME duration;
    INT_TIME it;
    INT_TIME *pt;
    EXT_TIME et;
    EXT_TIME et1, et2;

    int start_flag = 0;
    int end_flag = 0;
    int span_flag = 0;
    int debug_option = 0;
    char *span = NULL;
    char *output_fmt_string = NULL;
    char *dbuserid = DEFAULT_DBUSERID;
    char *next_keywd;
    char *distinct = "DISTINCT";

    /* Variables needed for getopt. */
    extern char	*optarg;
    extern int	optind, opterr;
    int		c;

    cmdname = tail(argv[0]);
    /*	Parse command line options.					*/
    while ( (c = getopt(argc,argv,"hvbaAqd:S:C:N:L:F:U:D:f:t:s:")) != -1)
	switch (c) {
	case '?':
	case 'h':   print_syntax(cmdname,syntax,stdout); exit(0); break;
	case 'v':   ++Flag_detail; break;
	case 'b':   ++Flag_bound; break;
	case 'a':   distinct = ""; break;
	case 'A':   aggregate = 1; break;
	case 'q':   ++qmerge_flag; break;
	case 'S':   station = add_to_list(station,optarg); break;
	case 'C':   channel = add_to_list(channel,optarg); break;
	case 'N':   network = add_to_list(network,optarg); break;
	case 'L':   location = add_to_list(location,optarg); break;
	case 'd':   debug_option = atoi(optarg); break;
	case 'F':   output_fmt_string = optarg; break;
	case 'U':   dbuserid = optarg; break;
	case 'f':
	    if ((pt = parse_date (optarg)) == NULL) {
		fprintf (stderr, "Error: invalid date: %s\n", optarg);
		exit(1);
	    }
	    start_time = *pt;
	    ++start_flag;
	    break;
	case 't':
	    if ((pt = parse_date (optarg)) == NULL) {
		fprintf (stderr, "Error: invalid date: %s\n", optarg);
		exit(1);
	    }
	    end_time = *pt;
	    ++end_flag;
	    break;
	case 's':
	    span = optarg; ++span_flag; break;
	case 'D':
	    /* Date display format.   */
	    if (strcmp (optarg, "j") == 0) date_fmt = JULIAN_FMT;
	    else if (strcmp (optarg, "j,") == 0) date_fmt = JULIAN_FMT_1;
	    else if (strcmp (optarg, "m") == 0) date_fmt = MONTHS_FMT;
	    else if (strcmp (optarg, "m,") == 0) date_fmt = MONTHS_FMT_1;
	    else {
		fprintf (stderr, "Error: invalid format for date display: %s\n", optarg);
		exit(1);
	    }
	    break;
	default:
	    fprintf (stderr, "Error: Unknown option: -%c\n", c);
	    exit(1);
	}
    if (strlen(DEFAULT_NETWORK) > 0 && network.n == 0) {
	network = add_to_list(network,DEFAULT_NETWORK);
    }
    if (start_flag && Flag_bound) {
	fprintf (stderr, "Error: options -s and -b are mutually exclusive\n");
	exit(1);
    }
    if (! (start_flag || Flag_bound)) {
	fprintf (stderr, "Error: missing option -s or -b\n");
	exit(1);
    }
    if (end_flag && span_flag) {
	fprintf (stderr, "Error: span and endtime mutually exclusive\n");
	exit(1);
    }
    if (span_flag && !start_flag) {
	fprintf (stderr, "Error: span not valid without start time\n");
	exit(1);
    }
    if (span_flag && ! valid_span(span)) {
	    fprintf (stderr, "Error: invalid span specification: %s\n", span);
	    exit(1);
	}
    if (start_flag && end_flag==0 && span_flag==0) {
	end_time = next_day(start_time);
	++end_flag;
    }
    if (span_flag) {
	end_time = end_of_span (start_time, span);
	++end_flag;
    }
    if (start_flag) {
	et = int_to_ext (start_time);
    }
    if (end_flag) {
	et = int_to_ext (end_time);
    }
    if (output_fmt_string) {
	if (strcasecmp(output_fmt_string,"NETDC")==0) output_fmt = NETDC_FMT;
	else if (strcasecmp(output_fmt_string,"BREQ_FAST")==0) output_fmt = BREQ_FAST_FMT;
    }

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

    strcpy (user_pwd, dbuserid);
    if (debug(DEBUG_SQL)) printf ("connecting as %s\n", dbuserid);

    EXEC SQL CONNECT :user_pwd;

    /********************************************************************/
    /* Construct SQL commands. */
    /********************************************************************/
    if (Flag_bound == 1) {
	/****************************************************************/
	/* List first and last times of database entries for specified	*/
	/* data streams.						*/
	/* If no specific streams specified, report for	all streams.	*/
	/****************************************************************/

	if ((network.n == 1) && (!strcmp (network.name[0], "UL")))
		sprintf (SQL_Command, 
			 "SELECT "
			 "DISTINCT sta, net, seedchan, location, "
			 "min (datetime_on), max(datetime_off) "
			 "FROM NCEDCDBA.Waveform ");

	else	sprintf (SQL_Command, 
			 "SELECT DISTINCT sta, net, seedchan, location, "
			 "min (datetime_on), max(datetime_off) "
			 "FROM NCEDCDBA.Waveform ");

	next_keywd = "WHERE";

	if (network.n > 0) {
	    sprintf (c_temp, "%s (net LIKE '%s'", next_keywd, network.name[0]);
	    for (j=1;j<network.n;j++) {
		sprintf (c_temp2, " OR net LIKE '%s'", network.name[j]);
		strcat (c_temp, c_temp2);
	    }
	    strcat (c_temp, ") ");
	    strcat (SQL_Command, c_temp);
	    next_keywd = "AND";
	}
	if (station.n > 0) {
	    sprintf (c_temp, "%s (sta LIKE '%s'", next_keywd, station.name[0]);
	    for (j=1;j<station.n;j++) {
		sprintf (c_temp2, " OR sta LIKE '%s'", station.name[j]);
		strcat (c_temp, c_temp2);
	    }
	    strcat (c_temp, ") ");
	    strcat (SQL_Command, c_temp);
	    next_keywd = "AND";
	}
	if (channel.n > 0) {
	    sprintf (c_temp, "%s (seedchan LIKE '%s'", next_keywd, channel.name[0]);
	    for (j=1;j<channel.n;j++) {
		sprintf (c_temp2, " OR seedchan LIKE '%s'", channel.name[j]);
		strcat (c_temp, c_temp2);
	    }
	    strcat (c_temp, ") ");
	    strcat (SQL_Command, c_temp);
	    next_keywd = "AND";
	}
	if (location.n > 0) {
	    sprintf (c_temp, "%s (location LIKE '%s'", next_keywd, location.name[0]);
	    for (j=1;j<location.n;j++) {
		sprintf (c_temp2, " OR location LIKE '%s'", location.name[j]);
		strcat (c_temp, c_temp2);
	    }
	    strcat (c_temp, ") ");
	    strcat (SQL_Command, c_temp);
	    next_keywd = "AND";
	}
	strcat (SQL_Command, "GROUP BY sta, net, seedchan, location");
	if (debug(DEBUG_SQL)) printf ("%s\n",SQL_Command);

	/* Declaring cursor */
	EXEC SQL PREPARE sql_stmt3 FROM :SQL_Command;
	EXEC SQL DECLARE db_cursor3 CURSOR FOR sql_stmt3;

	/* Opening cursor */
	EXEC SQL OPEN db_cursor3;

	EXEC SQL FETCH db_cursor3 INTO :sta, :net, :chan, :loc, :twstime, :twetime;

	while (sqlca.sqlcode != END_OF_QUERY) {
	    trim (sta);
	    trim (chan);
	    trim (net);
	    trim (loc);
	    if (loc[0] == '\0') strcpy (loc, "--");
	    strcpy(stime, time_to_str(tepoch_to_int(twstime),date_fmt));
	    strcpy(etime, time_to_str(tepoch_to_int(twetime),date_fmt));
	    printf ("%-5s %-2s %-3s %-2s %24s %24s\n", sta, net, chan, loc, stime, etime);
	    EXEC SQL FETCH db_cursor3 INTO :sta, :net, :chan, :loc, :twstime, :twetime;
	}
  
	/* Close the cursor */
	 EXEC SQL CLOSE db_cursor3;
    }

    else if (Flag_detail == 0) {
	/****************************************************************/
	/* Non-Detail database query for specified time span.		*/
	/* If no specific streams specified, report for all streams.	*/
	/****************************************************************/
	/* Non-detail query over a time span. */
	/* Converting dates */

	/* Iterate over each day within the timespan. */
	/* Round start and end times to the nearest day boundary. */
	start_time = start_day(start_time);
	while (tdiff(end_time,start_time) > 0.0) {
	    INT_TIME eod_time;

	    /* Determine end time for this day. */
	    eod_time = next_day(start_time);
	    if (tdiff(end_time,eod_time) < 0.0) eod_time = end_time;

	    if (network.n == 1)
			{
			 if (!strcmp (network.name[0], "UL"))
				{
				 if ((int_to_tepoch(eod_time) - int_to_tepoch(start_time)) < TWINDOWMONTHLY)
					sprintf (SQL_Command, 
					"SELECT "
					"DISTINCT sta FROM NCEDCDBA.Waveform "
					"WHERE "
					"((datetime_on >= %.0lf AND datetime_on < %.0lf) "
					"  OR (datetime_off >= %.0lf AND datetime_off < %.0lf) "
					"  OR ((datetime_on >= %.0lf-%d AND datetime_on < %.0lf) "
					"       AND (datetime_off >= %.0lf AND datetime_off < %.0lf+%d))) ",
					int_to_tepoch(start_time), int_to_tepoch(eod_time),
					int_to_tepoch(start_time), int_to_tepoch(eod_time),
					int_to_tepoch(eod_time), TWINDOWMONTHLY, int_to_tepoch(start_time),
					int_to_tepoch(eod_time), int_to_tepoch(start_time), TWINDOWMONTHLY);
				 else	sprintf (SQL_Command, 
					"SELECT "
					"DISTINCT sta FROM NCEDCDBA.Waveform "
					"WHERE "
					"((datetime_on >= %.0lf AND datetime_on < %.0lf) "
					"  OR (datetime_off >= %.0lf AND datetime_off < %.0lf)) ",
					int_to_tepoch(start_time), int_to_tepoch(eod_time),
					int_to_tepoch(start_time), int_to_tepoch(eod_time));
				}
			 else if ((!strcmp (network.name[0], "BK")) || (!strcmp (network.name[0], "BP")) || (!strcmp (network.name[0], "NN")))
				{
				 if ((int_to_tepoch(eod_time) - int_to_tepoch(start_time)) < TWINDOWDAILY)
					sprintf (SQL_Command, 
					"SELECT "
					"DISTINCT sta FROM NCEDCDBA.Waveform "
					"WHERE "
					"((datetime_on >= %.0lf AND datetime_on < %.0lf) "
					"  OR (datetime_off >= %.0lf AND datetime_off < %.0lf) "
					"  OR ((datetime_on >= %.0lf-%d AND datetime_on < %.0lf) "
					"       AND (datetime_off >= %.0lf AND datetime_off < %.0lf+%d))) ",
					int_to_tepoch(start_time), int_to_tepoch(eod_time),
					int_to_tepoch(start_time), int_to_tepoch(eod_time),
					int_to_tepoch(eod_time), TWINDOWDAILY, int_to_tepoch(start_time),
					int_to_tepoch(eod_time), int_to_tepoch(start_time), TWINDOWDAILY);
				 else	sprintf (SQL_Command, 
					"SELECT "
					"DISTINCT sta FROM NCEDCDBA.Waveform "
					"WHERE "
					"((datetime_on >= %.0lf AND datetime_on < %.0lf) "
					"  OR (datetime_off >= %.0lf AND datetime_off < %.0lf)) ",
					int_to_tepoch(start_time), int_to_tepoch(eod_time),
					int_to_tepoch(start_time), int_to_tepoch(eod_time));
				}
			 else
				{
				 if ((int_to_tepoch(eod_time) - int_to_tepoch(start_time)) < TWINDOWMONTHLY)
					sprintf (SQL_Command, 
					"SELECT "
					"DISTINCT sta FROM NCEDCDBA.Waveform "
					"WHERE "
					"((datetime_on >= %.0lf AND datetime_on < %.0lf) "
					"  OR (datetime_off >= %.0lf AND datetime_off < %.0lf) "
					"  OR ((datetime_on >= %.0lf-%d AND datetime_on < %.0lf) "
					"       AND (datetime_off >= %.0lf AND datetime_off < %.0lf+%d))) ",
					int_to_tepoch(start_time), int_to_tepoch(eod_time),
					int_to_tepoch(start_time), int_to_tepoch(eod_time),
					int_to_tepoch(eod_time), TWINDOWMONTHLY, int_to_tepoch(start_time),
					int_to_tepoch(eod_time), int_to_tepoch(start_time), TWINDOWMONTHLY);
				 else	sprintf (SQL_Command, 
					"SELECT "
					"DISTINCT sta FROM NCEDCDBA.Waveform "
					"WHERE "
					"((datetime_on >= %.0lf AND datetime_on < %.0lf) "
					"  OR (datetime_off >= %.0lf AND datetime_off < %.0lf)) ",
					int_to_tepoch(start_time), int_to_tepoch(eod_time),
					int_to_tepoch(start_time), int_to_tepoch(eod_time));
				}

			}
	    else
			{
			 if ((int_to_tepoch(eod_time) - int_to_tepoch(start_time)) < TWINDOWMONTHLY)
					sprintf (SQL_Command, 
					"SELECT "
					"DISTINCT sta FROM NCEDCDBA.Waveform "
					"WHERE "
					"((datetime_on >= %.0lf AND datetime_on < %.0lf) "
					"  OR (datetime_off >= %.0lf AND datetime_off < %.0lf) "
					"  OR ((datetime_on >= %.0lf-%d AND datetime_on < %.0lf) "
					"       AND (datetime_off >= %.0lf AND datetime_off < %.0lf+%d))) ",
					int_to_tepoch(start_time), int_to_tepoch(eod_time),
					int_to_tepoch(start_time), int_to_tepoch(eod_time),
					int_to_tepoch(eod_time), TWINDOWMONTHLY, int_to_tepoch(start_time),
					int_to_tepoch(eod_time), int_to_tepoch(start_time), TWINDOWMONTHLY);
			 else	sprintf (SQL_Command, 
					"SELECT "
					"DISTINCT sta FROM NCEDCDBA.Waveform "
					"WHERE "
					"((datetime_on >= %.0lf AND datetime_on < %.0lf) "
					"  OR (datetime_off >= %.0lf AND datetime_off < %.0lf)) ",
					int_to_tepoch(start_time), int_to_tepoch(eod_time),
					int_to_tepoch(start_time), int_to_tepoch(eod_time));
			}

	next_keywd = "AND";

	if (network.n > 0) {
		sprintf (c_temp, "%s (net LIKE '%s'", next_keywd, network.name[0]);
		for (j=1;j<network.n;j++) {
		    sprintf (c_temp2, " OR net LIKE '%s'", network.name[j]);
		    strcat (c_temp, c_temp2);
		    }
		strcat (c_temp, ") ");
		strcat (SQL_Command, c_temp);
		next_keywd = "AND";
	    }
	    if (station.n > 0) {
		sprintf (c_temp, "%s (sta LIKE '%s'", next_keywd, station.name[0]);
		for (j=1;j<station.n;j++) {
		    sprintf (c_temp2, " OR sta LIKE '%s'", station.name[j]);
		    strcat (c_temp, c_temp2);
		    }
		strcat (c_temp, ") ");
		strcat (SQL_Command, c_temp);
		next_keywd = "AND";
	    }
	    if (channel.n > 0) {
		sprintf (c_temp, "%s (seedchan LIKE '%s'", next_keywd, channel.name[0]);
		for (j=1;j<channel.n;j++) {
		    sprintf (c_temp2, " OR seedchan LIKE '%s'", channel.name[j]);
		    strcat (c_temp, c_temp2);
		    }
		strcat (c_temp, ") ");
		strcat (SQL_Command, c_temp);
		next_keywd = "AND";
	    }
	    if (location.n > 0) {
		sprintf (c_temp, "%s (location LIKE '%s'", next_keywd, location.name[0]);
		for (j=1;j<location.n;j++) {
		    sprintf (c_temp2, " OR location LIKE '%s'", location.name[j]);
		    strcat (c_temp, c_temp2);
		    }
		strcat (c_temp, ") ");
		strcat (SQL_Command, c_temp);
		next_keywd = "AND";
	    }
	    strcat (SQL_Command, "ORDER BY sta "); 
	    if (debug(DEBUG_SQL)) printf ("%s\n",SQL_Command);

	    /* Declaring cursor */
	    EXEC SQL PREPARE sql_stmt FROM :SQL_Command;
	    EXEC SQL DECLARE db_cursor CURSOR FOR sql_stmt;

	    /* Opening cursor */
	    EXEC SQL OPEN db_cursor;

	    printf ("%s : ", time_to_str(start_time,date_fmt));

	    EXEC SQL FETCH db_cursor INTO :sta;

	    while (sqlca.sqlcode != END_OF_QUERY) {
		trim (sta);
		printf ("%-5s ", sta);
		EXEC SQL FETCH db_cursor INTO :sta;
	    }
	    printf ("\n");
  
	    /* Close the cursor */
	    EXEC SQL CLOSE db_cursor;

	    start_time = eod_time;
	 }
    }

    else {
	/****************************************************************/
	/* Detailed database query for specified time span.		*/
	/* If no specific streams specified, report for all streams.	*/
	/* Output data in one of several format.			*/
	/****************************************************************/
	double tstart_time = int_to_tepoch(start_time);
	double tend_time =   int_to_tepoch(end_time);

	if (network.n == 1)
		{
		 if (!strcmp (network.name[0], "UL"))
			{
			 if ((tend_time - tstart_time) < TWINDOWMONTHLY)
				sprintf (SQL_Command, 
				"SELECT "
				"%s sta, net, seedchan, location, samprate, "
				"datetime_on, datetime_off "
				"FROM NCEDCDBA.Waveform "
				"WHERE "
				"((datetime_on >= %.0lf AND datetime_on < %.0lf) "
				"  OR (datetime_off >= %.0lf AND datetime_off < %.0lf) "
				"  OR ((datetime_on >= %.0lf-%d AND datetime_on < %.0lf) "
				"       AND (datetime_off >= %.0lf AND datetime_off < %.0lf+%d))) ",
				distinct,
				tstart_time, tend_time,
				tstart_time, tend_time,
				tend_time, TWINDOWMONTHLY, tstart_time,
				tend_time, tstart_time, TWINDOWMONTHLY);
			 else	sprintf (SQL_Command, 
				"SELECT "
				"%s sta, net, seedchan, location, samprate, "
				"datetime_on, datetime_off "
				"FROM NCEDCDBA.Waveform "
				"WHERE "
				"((datetime_on >= %.0lf AND datetime_on < %.0lf) "
				"  OR (datetime_off >= %.0lf AND datetime_off < %.0lf)) ",
				distinct,
				tstart_time, tend_time,
				tstart_time, tend_time);
			}
		 else if ((!strcmp (network.name[0], "BK")) || (!strcmp (network.name[0], "BP")) || (!strcmp (network.name[0], "NN")))
			{
			 if ((tend_time - tstart_time) < TWINDOWDAILY)
				sprintf (SQL_Command, 
				"SELECT "
				"%s sta, net, seedchan, location, samprate, "
				"datetime_on, datetime_off "
				"FROM NCEDCDBA.Waveform "
				"WHERE "
				"((datetime_on >= %.0lf AND datetime_on < %.0lf) "
				"  OR (datetime_off >= %.0lf AND datetime_off < %.0lf) "
				"  OR ((datetime_on >= %.0lf-%d AND datetime_on < %.0lf) "
				"       AND (datetime_off >= %.0lf AND datetime_off < %.0lf+%d))) ",
				distinct,
				tstart_time, tend_time,
				tstart_time, tend_time,
				tend_time, TWINDOWDAILY, tstart_time,
				tend_time, tstart_time, TWINDOWDAILY);
			 else	sprintf (SQL_Command, 
				"SELECT "
				"%s sta, net, seedchan, location, samprate, "
				"datetime_on, datetime_off "
				"FROM NCEDCDBA.Waveform "
				"WHERE "
				"((datetime_on >= %.0lf AND datetime_on < %.0lf) "
				"  OR (datetime_off >= %.0lf AND datetime_off < %.0lf)) ",
				distinct,
				tstart_time, tend_time,
				tstart_time, tend_time);
			}
		 else
			{
			 if ((tend_time - tstart_time) < TWINDOWMONTHLY)
				sprintf (SQL_Command, 
				"SELECT "
				"%s sta, net, seedchan, location, samprate, "
				"datetime_on, datetime_off "
				"FROM NCEDCDBA.Waveform "
				"WHERE "
				"((datetime_on >= %.0lf AND datetime_on < %.0lf) "
				"  OR (datetime_off >= %.0lf AND datetime_off < %.0lf) "
				"  OR ((datetime_on >= %.0lf-%d AND datetime_on < %.0lf) "
				"       AND (datetime_off >= %.0lf AND datetime_off < %.0lf+%d))) ",
				distinct,
				tstart_time, tend_time,
				tstart_time, tend_time,
				tend_time, TWINDOWMONTHLY, tstart_time,
				tend_time, tstart_time, TWINDOWMONTHLY);
			 else	sprintf (SQL_Command, 
				"SELECT "
				"%s sta, net, seedchan, location, samprate, "
				"datetime_on, datetime_off "
				"FROM NCEDCDBA.Waveform "
				"WHERE "
				"((datetime_on >= %.0lf AND datetime_on < %.0lf) "
				"  OR (datetime_off >= %.0lf AND datetime_off < %.0lf)) ",
				distinct,
				tstart_time, tend_time,
				tstart_time, tend_time);
			}
		}
	else
		{
		 if ((tend_time - tstart_time) < TWINDOWMONTHLY)
			sprintf (SQL_Command, 
				"SELECT "
				"%s sta, net, seedchan, location, samprate, "
				"datetime_on, datetime_off "
				"FROM NCEDCDBA.Waveform "
				"WHERE "
				"((datetime_on >= %.0lf AND datetime_on < %.0lf) "
				"  OR (datetime_off >= %.0lf AND datetime_off < %.0lf) "
				"  OR ((datetime_on >= %.0lf-%d AND datetime_on < %.0lf) "
				"       AND (datetime_off >= %.0lf AND datetime_off < %.0lf+%d))) ",
				distinct,
				tstart_time, tend_time,
				tstart_time, tend_time,
				tend_time, TWINDOWMONTHLY, tstart_time,
				tend_time, tstart_time, TWINDOWMONTHLY);
		  else	sprintf (SQL_Command, 
				"SELECT "
				"%s sta, net, seedchan, location, samprate, "
				"datetime_on, datetime_off "
				"FROM NCEDCDBA.Waveform "
				"WHERE "
				"((datetime_on >= %.0lf AND datetime_on < %.0lf) "
				"  OR (datetime_off >= %.0lf AND datetime_off < %.0lf)) ",
				distinct,
				tstart_time, tend_time,
				tstart_time, tend_time);
		}

	next_keywd = "AND";

	if (network.n > 0) {
	    sprintf (c_temp, "%s (net LIKE '%s'", next_keywd, network.name[0]);
	    for (j=1;j<network.n;j++) {
		sprintf (c_temp2, " OR net LIKE '%s'", network.name[j]);
		strcat (c_temp, c_temp2);
	    }
	    strcat (c_temp, ") ");
	    strcat (SQL_Command, c_temp);
	    next_keywd = "AND";
	}
	if (station.n > 0) {
	    sprintf (c_temp, "%s (sta LIKE '%s'", next_keywd, station.name[0]);
	    for (j=1;j<station.n;j++) {
		sprintf (c_temp2, " OR sta LIKE '%s'", station.name[j]);
		strcat (c_temp, c_temp2);
	    }
	    strcat (c_temp, ") ");
	    strcat (SQL_Command, c_temp);
	    next_keywd = "AND";
	}
	if (channel.n > 0) {
	    sprintf (c_temp, "%s (seedchan LIKE '%s'", next_keywd, channel.name[0]);
	    for (j=1;j<channel.n;j++) {
		sprintf (c_temp2, " OR seedchan LIKE '%s'", channel.name[j]);
		strcat (c_temp, c_temp2);
	    }
	    strcat (c_temp, ") ");
	    strcat (SQL_Command, c_temp);
	    next_keywd = "AND";
	}
	if (location.n > 0) {
	    sprintf (c_temp, "%s (location LIKE '%s'", next_keywd, location.name[0]);
	    for (j=1;j<location.n;j++) {
		sprintf (c_temp2, " OR location LIKE '%s'", location.name[j]);
		strcat (c_temp, c_temp2);
	    }
	    strcat (c_temp, ") ");
	    strcat (SQL_Command, c_temp);
	    next_keywd = "AND";
	}
	strcat (SQL_Command, "ORDER BY sta"); 
	if (debug(DEBUG_SQL)) printf ("%s\n",SQL_Command);

	/* Declaring cursor */
	EXEC SQL PREPARE sql_stmt2 FROM :SQL_Command;
	EXEC SQL DECLARE db_cursor2 CURSOR FOR sql_stmt2;

	/* Opening cursor */
	EXEC SQL OPEN db_cursor2;

	memset (&prev, 0, sizeof(prev));
	memset (&curr, 0, sizeof(curr));

	EXEC SQL FETCH db_cursor2 INTO :curr.sta, :curr.net, :curr.chan, :curr.loc, 
	    :curr.samprate, :curr.twstime, :curr.twetime;
	curr.samptime = 0;
	curr.output_now = 0;

	while (sqlca.sqlcode != END_OF_QUERY) {
	    trim (curr.sta);
	    trim (curr.chan);
	    trim (curr.net);
	    trim (curr.loc);
	    if (curr.loc[0] == '\0') strcpy (curr.loc, "  ");

	    if (aggregate) {
		double samptime;
		samptime = (1./curr.samprate);
		if (strcmp(curr.sta,prev.sta)==0 &&
		    strcmp(curr.net,prev.net)==0 &&
		    strcmp(curr.chan,prev.chan)==0 &&
		    strcmp(curr.loc,prev.loc)==0 &&
		    prev.samprate == curr.samprate &&
		    fabs(curr.twstime-(prev.twetime+samptime)) <= .5*samptime) {
		    /* Aggregate these entries together. */
		    prev.twetime = curr.twetime;
		}
		else {
		    if (prev.sta[0] == '\0') {
			/* No previous entry.  Save this one. */
			prev = curr;
		    }
		    else {
			/* No aggregation with previous entry - print it. */
			prev.output_now = 1;
		    }
		}
	    }
	    else {
		curr.output_now = 1;
	    }

	    if (prev.output_now) {
		output_entry (prev, output_fmt, tstart_time, tend_time);
		prev = curr;
		memset (&curr, 0, sizeof(curr));
	    }
	    if (curr.output_now) {
		output_entry (curr, output_fmt, tstart_time, tend_time);
		memset (&curr, 0, sizeof(curr));
	    }

	    EXEC SQL FETCH db_cursor2 INTO :curr.sta, :curr.net, :curr.chan, :curr.loc, 
		:curr.samprate, :curr.twstime, :curr.twetime;
	}
  
	if (aggregate && prev.sta[0]) 
		output_entry (prev, output_fmt, tstart_time, tend_time);

	/* Close the cursor */
	EXEC SQL CLOSE db_cursor2;
    }
/*::    printf ("\n");*/

    /* Disconnect from the database. */
    EXEC SQL COMMIT WORK RELEASE;

    return(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);

 printf("\nORACLE error detected:");
 printf("\n%.*s \n", msg_len, msg);

 EXEC SQL ROLLBACK WORK RELEASE;

 exit(1);
}