/*******************************************************************************
 * Copyright (c) 2011 IRIS/DMC.
 * All rights reserved. This program and the accompanying materials
 * are made available under the terms of the GNU Lesser Public License v2.1
 * which accompanies this distribution, and is available at
 * http://www.gnu.org/licenses/old-licenses/gpl-2.0.html
 * 
 * Contributors:
 *     IRIS/DMC- initial API and implementation
 * 
 * ACKNOWLEDGEMENT
 * This software was developed as part of a project supported by 
 *  Cooperative Agreement Number G10AC00533 from the United States 
 *  Geological Survey.  Its contents are solely the responsibility of 
 * the authors and the USGS is not responsible for the efficacy,
 * safety, or suitability of this software.
 ******************************************************************************/
public class SQL {
	public static String sql = "select network, station, station_start_time, station_end_time, station_latitude, station_longitude, "
	                + "station_elevation, word, long_word, site, ARCHIVEDT, channel, location, channel_start_time, channel_end_time, "
	                + "azimuth, channel_latitude, channel_longitude, channel_dip, channel_depth, channel_elevation, sample_rate, "
	                + "clock_drift, flags, channel_instrument, SENS_GAIN, FREQUENCY, response_units_in_name, "
	                + "unit_description, stage from "
	                + "((SELECT distinct s.net network,s.sta station,s.ondate station_start_time,"
			+ " s.offdate station_end_time,s.lat station_latitude,"
			+ " s.lon station_longitude,s.elev station_elevation,s.word_16 word,s.word_32 long_word,"
			+ " s.staname site,s.lddate ARCHIVEDT,c.seedchan channel,c.location,c.ondate channel_start_time,"
			+ " c.offdate channel_end_time,c.azimuth,c.lat channel_latitude,c.lon channel_longitude,"
			+ " c.dip channel_dip,c.edepth channel_depth,"
			+ " c.elev channel_elevation,c.samprate sample_rate,c.clock_drift, c.flags, b33.description channel_instrument,"
			+ " chanresp.sensitivity SENS_GAIN,"
			+ " chanresp.FREQUENCY,"
			+ " b341.name response_units_in_name,b341.description unit_description,chanresp.stage_seq stage"
			+ " FROM station_data s "
			+ " LEFT JOIN channel_data c "
			+ " ON s.net        = c.net "
			+ " AND s.sta       = c.sta "
			+ " AND s.ondate   <= c.ondate "
			+ " AND s.offdate  >  c.ondate "
			+ " LEFT JOIN d_abbreviation b33 "
			+ " ON c.inid = b33.id "
			+ " LEFT JOIN sensitivity chanresp "
			+ " ON c.net   = chanresp.net "
			+ " AND c.sta  = chanresp.sta "
			+ " AND c.seedchan = chanresp.seedchan "
			+ " AND c.location = chanresp.location "
			+ " AND c.ondate= chanresp.ondate "
			+ " LEFT JOIN d_unit b341 "
			+ " ON c.unit_signal = b341.id "
			+ " WHERE (chanresp.stage_seq = 0) "
			+ " AND s.net IS NOT NULL "
			+ " AND s.sta IS NOT NULL "
			+ " UNION "
			+ " SELECT distinct s.net network,s.sta station,s.ondate station_start_time,"
			+ " s.offdate station_end_time,s.lat station_latitude,"
			+ " s.lon station_longitude,s.elev station_elevation,s.word_16 word,s.word_32 long_word,"
			+ " s.staname site,s.lddate ARCHIVEDT,c.seedchan channel,c.location,c.ondate channel_start_time,"
			+ " c.offdate channel_end_time,c.azimuth,c.lat channel_latitude,c.lon channel_longitude,"
			+ " c.dip channel_dip,c.edepth channel_depth,"
			+ " c.elev channel_elevation,c.samprate sample_rate,c.clock_drift, c.flags, b33.description channel_instrument,"
			+ " NULL SENS_GAIN,"
			+ " NULL FREQUENCY,"
			+ " b341.name response_units_in_name,b341.description unit_description,p.stage_seq stage"
			+ " FROM station_data s, channel_data c, polynomial p , d_abbreviation b33, d_unit b341"
			+ " WHERE p.stage_seq = 1 "
			+ " AND s.net      = c.net"
			+ " AND s.sta      = c.sta"
			+ " AND s.ondate   <= c.ondate"
			+ " AND s.offdate  >  c.ondate"
			+ " AND c.unit_signal = b341.id"
			+ " AND c.inid = b33.id"
			+ " AND c.net      = p.net"
			+ " AND c.sta      = p.sta"
			+ " AND c.seedchan = p.seedchan"
			+ " AND c.location = p.location"
			+ " AND c.ondate   = p.ondate"
			+ " AND s.net IS NOT NULL"
			+ " AND s.sta IS NOT NULL)"
	    + " ORDER BY network,station,station_start_time,station_end_time,channel,location,channel_start_time,channel_end_time) ";


	public static String responseSql = "(SELECT net network, sta station,"
			+ " seedchan channel, location,"
			+ " ondate starttime, offdate endtime, stage_seq stage,"
			+ " NULL cseq, NULL rsec, NULL blen, '58' btype, NULL TRANSFER_TYPE, NULL REPEAT1, NULL REPEAT2,"
			+ " NULL N_FACTOR, NULL N_FREQ, NULL SAMPLE_RATE, NULL D_FACTOR, NULL D_OFFSET, NULL EST_DELAY,"
			+ " NULL CORRECTION, sensitivity SENS_GAIN, FREQUENCY, NULL NAME,"
			+ " NULL SYMMETRY, NULL response_units_in_name, NULL response_units_in_text,"
			+ " NULL response_units_out_name, NULL response_units_out_text, NULL rtype, NULL value1,"
			+ " NULL value2, NULL value3, NULL value4, NULL value5, NULL above_calib"
			+ " FROM sensitivity"
			+ " WHERE stage_seq > 0 AND net =:net"
			+ " AND sta =:sta AND seedchan = :chan AND location =:loc"
			+ " AND ondate = :start AND offdate =:end"
			+ " UNION "
			+ " SELECT poles_zeros.net network, poles_zeros.sta station, poles_zeros.seedchan channel,"
			+ " poles_zeros.location, poles_zeros.ondate starttime, poles_zeros.offdate endtime,"
			+ " poles_zeros.stage_seq stage, NULL cseq, pz_data.row_key rsec, NULL blen, '53' btype,"
			+ " poles_zeros.tf_type TRANSFER_TYPE, "
			+ " (select count(*) from pz_data where key=poles_zeros.pz_key)  REPEAT1,"
			+ " 0 REPEAT2, poles_zeros.AO N_FACTOR, poles_zeros.AF N_FREQ, NULL SAMPLE_RATE,"
			+ " NULL D_FACTOR, NULL D_OFFSET, NULL EST_DELAY, NULL CORRECTION, NULL SENS_GAIN,"
			+ " NULL FREQUENCY, NULL NAME, NULL SYMMETRY, d1.name response_units_in_name,"
			+ " d1.description response_units_in_text, d2.name response_units_out_name,"
			+ " d2.description response_units_out_text, pz_data.type rtype,"
			+ " pz_data.r_value value1, pz_data.i_value value2, NULL value3, NULL value4,"
			+ " NULL value5, NULL above_calib FROM poles_zeros, pz_data, d_unit d1, d_unit d2"
			+ " WHERE poles_zeros.unit_in=d1.id AND poles_zeros.unit_out=d2.id"
			+ " AND poles_zeros.pz_key=pz_data.key AND poles_zeros.stage_seq > 0"
			+ " AND poles_zeros.net =:net AND poles_zeros.sta =:sta AND poles_zeros.seedchan = :chan"
			+ " AND poles_zeros.location =:loc AND poles_zeros.ondate = :start"
			+ " AND poles_zeros.offdate =:end"
			+ " UNION "
			+ " SELECT decimation.net network, decimation.sta station, decimation.seedchan channel,"
			+ " decimation.location, decimation.ondate starttime, decimation.offdate endtime,"
			+ " decimation.stage_seq stage, NULL cseq, NULL rsec, NULL blen, '57' btype,"
			+ " NULL TRANSFER_TYPE, "
			+ " NULL REPEAT1, NULL REPEAT2, NULL N_FACTOR, NULL N_FREQ, dm.samprate SAMPLE_RATE,"
			+ " dm.factor D_FACTOR, dm.offset D_OFFSET,"
			+ " dm.delay EST_DELAY, dm.CORRECTION, NULL SENS_GAIN,"
			+ " NULL FREQUENCY, dm.NAME, NULL SYMMETRY, 'V' response_units_in_name,"
			+ " NULL response_units_in_text, NULL response_units_out_name,"
			+ " NULL response_units_out_text, NULL rtype,"
			+ " NULL value1, NULL value2, NULL value3, NULL value4,"
			+ " NULL value5, NULL above_calib FROM decimation, dm"
			+ " WHERE decimation.dm_key=dm.key AND decimation.stage_seq > 0"
			+ " AND decimation.net =:net AND decimation.sta =:sta AND decimation.seedchan = :chan"
			+ " AND decimation.location =:loc AND decimation.ondate = :start"
			+ " AND decimation.offdate =:end"
			+ " UNION "
			+ " SELECT coefficients.net network, coefficients.sta station, coefficients.seedchan channel,"
			+ " coefficients.location, coefficients.ondate starttime, coefficients.offdate endtime,"
			+ " coefficients.stage_seq stage, NULL cseq, dc_data.row_key rsec, NULL blen, '61' btype,"
			+ " coefficients.tf_type TRANSFER_TYPE, "
			+ " (select count(*) from dc_data where key=coefficients.dc_key)  REPEAT1,"
			+ " 0 REPEAT2, NULL N_FACTOR, NULL N_FREQ, NULL SAMPLE_RATE,"
			+ " NULL D_FACTOR, NULL D_OFFSET, NULL EST_DELAY, NULL CORRECTION, NULL SENS_GAIN,"
			+ " NULL FREQUENCY, dc.NAME, dc.SYMMETRY, d1.name response_units_in_name,"
			+ " d1.description response_units_in_text, d2.name response_units_out_name,"
			+ " d2.description response_units_out_text, dc_data.type rtype,"
			+ " dc_data.coefficient value1, NULL value2, NULL value3, NULL value4,"
			+ " NULL value5, NULL above_calib FROM coefficients, dc, dc_data, d_unit d1, d_unit d2"
			+ " WHERE coefficients.unit_in=d1.id AND coefficients.unit_out=d2.id"
			+ " AND coefficients.dc_key=dc.key AND coefficients.dc_key=dc_data.key AND coefficients.stage_seq > 0"
			+ " AND coefficients.net =:net AND coefficients.sta =:sta AND coefficients.seedchan = :chan"
			+ " AND coefficients.location =:loc AND coefficients.ondate = :start"
			+ " AND coefficients.offdate =:end"
                        + " UNION "
                        + " SELECT coefficients.net network, coefficients.sta station, coefficients.seedchan channel,"
                        + " coefficients.location, coefficients.ondate starttime, coefficients.offdate endtime,"
                        + " coefficients.stage_seq stage, NULL cseq, NULL rsec, NULL blen, '54' btype,"
                        + " coefficients.tf_type TRANSFER_TYPE, "
                        + " 0 REPEAT1,"
                        + " 0 REPEAT2, NULL N_FACTOR, NULL N_FREQ, NULL SAMPLE_RATE,"
                        + " NULL D_FACTOR, NULL D_OFFSET, NULL EST_DELAY, NULL CORRECTION, NULL SENS_GAIN,"
                        + " NULL FREQUENCY, NULL NAME, NULL SYMMETRY, d1.name response_units_in_name,"
                        + " d1.description response_units_in_text, d2.name response_units_out_name,"
                        + " d2.description response_units_out_text, NULL rtype,"
                        + " NULL value1, NULL value2, NULL value3, NULL value4,"
                        + " NULL value5, NULL above_calib FROM coefficients, d_unit d1, d_unit d2"
                        + " WHERE coefficients.dc_key IS NULL AND coefficients.unit_in=d1.id AND coefficients.unit_out=d2.id"
                        + " AND coefficients.stage_seq > 0"
                        + " AND coefficients.net =:net AND coefficients.sta =:sta AND coefficients.seedchan = :chan"
                        + " AND coefficients.location =:loc AND coefficients.ondate = :start"
                        + " AND coefficients.offdate =:end"
			+ " UNION "
			+ " SELECT polynomial.net network, polynomial.sta station, polynomial.seedchan channel,"
			+ " polynomial.location, polynomial.ondate starttime, polynomial.offdate endtime,"
			+ " polynomial.stage_seq stage, NULL cseq, pn_data.row_key rsec, NULL blen, '62' btype,"
			+ " polynomial.tf_type TRANSFER_TYPE, "
			+ " (select count(*) from pn_data where key=polynomial.pn_key)  REPEAT1,"
			+ " 0 REPEAT2, NULL N_FACTOR, NULL N_FREQ, NULL SAMPLE_RATE,"
			+ " NULL D_FACTOR, NULL D_OFFSET, NULL EST_DELAY, NULL CORRECTION, NULL SENS_GAIN,"
			+ " NULL FREQUENCY, pn.NAME, pn.poly_type SYMMETRY, d1.name response_units_in_name,"
			+ " d1.description response_units_in_text, d2.name response_units_out_name,"
			+ " d2.description response_units_out_text, NULL rtype,"
			+ " pn_data.pn_value value1, 1 value2, pn.lower_bound value3, pn.upper_bound value4,"
			+ " pn.max_error value5, NULL above_calib FROM polynomial, pn, pn_data, d_unit d1, d_unit d2"
			+ " WHERE polynomial.unit_in=d1.id AND polynomial.unit_out=d2.id"
			+ " AND polynomial.pn_key=pn.key AND polynomial.pn_key=pn_data.key AND polynomial.stage_seq > 0"
			+ " AND polynomial.net =:net AND polynomial.sta =:sta AND polynomial.seedchan = :chan"
			+ " AND polynomial.location =:loc AND polynomial.ondate = :start"
			+ " AND polynomial.offdate =:end)"
			+ " ORDER BY stage, rsec";

	public static String networkSql = "SELECT s.net, MIN(s.ondate) net_sdate, MAX(s.offdate) net_edate, d.description net_desc, COUNT(distinct s.sta) sta_count FROM Station_Data s, D_Abbreviation d WHERE s.net_id = d.id AND s.net =:net GROUP BY s.net, d.description";

	public static String dataSql = "SELECT TO_DATE(TRUETIME.TRUE2STRING(\"Earliest_Waveform\"),'yyyy/mm/dd hh24:mi:ss') data_sdate, TO_DATE(TRUETIME.TRUE2STRING(\"Latest_Waveform\"),'yyyy/mm/dd hh24:mi:ss') data_edate FROM GDV_Channel_Summary WHERE \"Network_Code\"=:net AND \"Station_Code\"=:sta AND \"Channel_Code\"=:cha AND \"Location_Code\"=:loc";

	public static String responseSqlPoly = "(SELECT polynomial.net network, polynomial.sta station, polynomial.seedchan channel,"
			+ " polynomial.location, polynomial.ondate starttime, polynomial.offdate endtime,"
			+ " polynomial.stage_seq stage, NULL cseq, pn_data.row_key rsec, NULL blen, '62' btype,"
			+ " polynomial.tf_type TRANSFER_TYPE, "
			+ " (select count(*) from pn_data where key=polynomial.pn_key)  REPEAT1,"
			+ " 0 REPEAT2, NULL N_FACTOR, NULL N_FREQ, NULL SAMPLE_RATE,"
			+ " NULL D_FACTOR, NULL D_OFFSET, NULL EST_DELAY, NULL CORRECTION, NULL SENS_GAIN,"
			+ " NULL FREQUENCY, pn.NAME, pn.poly_type SYMMETRY, d1.name response_units_in_name,"
			+ " d1.description response_units_in_text, d2.name response_units_out_name,"
			+ " d2.description response_units_out_text, NULL rtype,"
			+ " pn_data.pn_value value1, 1 value2, pn.lower_bound value3, pn.upper_bound value4,"
			+ " pn.max_error value5, NULL above_calib FROM polynomial, pn, pn_data, d_unit d1, d_unit d2"
			+ " WHERE polynomial.unit_in=d1.id AND polynomial.unit_out=d2.id"
			+ " AND polynomial.pn_key=pn.key AND polynomial.pn_key=pn_data.key AND polynomial.stage_seq = 0"
			+ " AND polynomial.net =:net AND polynomial.sta =:sta AND polynomial.seedchan = :chan"
			+ " AND polynomial.location =:loc AND polynomial.ondate = :start"
			+ " AND polynomial.offdate =:end)"
			+ " ORDER BY stage, rsec";


	public static final int NETWORK_LEVEL = 0;
	public static final int STATION_LEVEL = 1;
	public static final int CHANNEL_LEVEL = 2;
	public static final int RESPONSE_LEVEL = 3;
	public static final String NETWORK = "NETWORK";
	public static final String STATION = "STATION";
	public static final String STATION_START_TIME = "STATION_START_TIME";
	public static final String STATION_END_TIME = "STATION_END_TIME";
	public static final String CHANNELS = "CHANNELS";
	public static final String CHANNEL = "CHANNEL";
	public static final String LOCATION = "LOCATION";
	public static final String CHANNEL_START_TIME = "CHANNEL_START_TIME";
	public static final String CHANNEL_END_TIME = "CHANNEL_END_TIME";
	public static final String STATION_LAT = "STATION_LAT";
	public static final String STATION_LON = "STATION_LON";
	public static final String STATION_ELEVATION = "STATION_ELEVATION";
	public static final String STATION_COUNTRY = "COUNTRY";
	public static final String STATION_WORD = "WORD";
	public static final String STATION_LONG_WORD = "LONG_WORD";
	public static final String CHANNEL_INSTRUMENT = "CHANNEL_INSTRUMENT";
	public static final String CHANNEL_LAT = "CHANNEL_LAT";
	public static final String CHANNEL_LON = "CHANNEL_LON";
	public static final String CHANNEL_ELEVATION = "CHANNEL_ELEVATION";
	public static final String CHANNEL_AZIMUTH = "CHANNEL_AZZIMUTH";
	public static final String CHANNEL_DEPTH = "CHANNEL_DEPTH";
	public static final String CHANNEL_DIP = "CHANNEL_DIP";
	public static final String CHANNEL_SAMPLE_RATE = "CHANNEL_SAMPLE_RATE";
	public static final String STAGE = "STAGE";
	public static final String SEQ = "SEQ";
	public static final String BLEN = "BLEN";
	public static final String BTYPE = "BTYPE";
	public static final String TRANSFER_TYPE = "TRANSFER_TYPE";
	public static final String REPEAT1 = "REPEAT1";
	public static final String REPEAT2 = "REPEAT2";
	public static final String N_FACTOR = "N_FACTOR";
	public static final String N_FREQ = "N_FREQ";
	public static final String RESPONSE_SAMPLE_RATE = "SAMPLE_RATE";
	public static final String D_FACTOR = "D_FACTOR";
	public static final String D_OFFSET = "D_OFFSET";
	public static final String EST_DELAY = "EST_DELAY";
	public static final String CORRECTION = "CORRECTION";
	public static final String SENS_GAIN = "SENS_GAIN";
	public static final String FREQUENCY = "FREQUENCY";
	public static final String NAME = "NAME";
	public static final String SYMMETRY = "SYMMETRY";
	public static final String RESPONSE_UNITS_IN_NAME = "RESPONSE_UNITS_IN_NAME";
	public static final String RESPONSE_UNITS_IN_TEXT = "RESPONSE_UNITS_IN_TEXT";
	public static final String RESPONSE_UNITS_OUT_NAME = "RESPONSE_UNITS_OUT_NAME";
	public static final String RESPONSE_UNITS_OUT_TEXT = "RESPONSE_UNITS_OUT_TEXT";
	public static final String RTYPE = "RTYPE";
	public static final String VALUE1 = "VALUE1";
	public static final String VALUE2 = "VALUE2";
	public static final String VALUE3 = "VALUE3";
	public static final String VALUE4 = "VALUE4";
	public static final String VALUE5 = "VALUE5";
	public static final String ABOVE_CALIB = "ABOVE_CALIB";
	public static final String net = "net";
	public static final String net_sdate = "net_sdate";
	public static final String net_edate = "net_edate";
	public static final String net_desc = "net_desc";
	public static final String sta_count = "sta_count";
	public static final String data_sdate = "data_sdate";
	public static final String data_edate = "data_edate";
}