/*
 * Created on 14-Sep-2004
 *
 * Class to hold various WSA schema related objects and SQL
 */
package uk.ac.roe.wfau;

import java.io.File;
import java.io.FileInputStream;
import java.io.FilenameFilter;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.HashMap;
import java.util.Properties;

import javax.naming.directory.SchemaViolationException;
import javax.servlet.http.HttpSession;

import net.mar.Format;

/**
 * Class to hold various WSA schema related objects and SQL
 * 
 * @author mar
 * @version 1.0
 * 
 */
public class WSASchema {
 
    /**
     * Offset between extension numbers held in database and what cfitsio
     * expects.
     */
    
//    public static final String [] ALLUKIDSSRDB={"UKIDSSDR7PLUS","UKIDSSDR6PLUS","UKIDSSDR5PLUS","UKIDSSDR4PLUS","UKIDSSDR3PLUS","UKIDSSDR2PLUS","UKIDSSDR1PLUS","UKIDSSDR1","UKIDSSEDRPLUS","UKIDSSEDR","UKIDSSSV","UKIDSSR1"}; // names for UKIDSS releaase
    public static String [] ALLUKIDSSRDB;

    //    public static final int [] UKIDSSVERSIONS={9,8,7,6,5,4,3,3,2,2,1,0}; // version numbers for UKIDSS releaase
    public static int [] UKIDSSVERSIONS;
    
//   public static final String [] UKIDSSDB={"UKIDSSDR7PLUS","UKIDSSDR6PLUS","UKIDSSDR5PLUS","UKIDSSDR4PLUS","UKIDSSDR3PLUS","UKIDSSDR2PLUS","UKIDSSDR1PLUS","UKIDSSDR1","UKIDSSEDRPLUS","UKIDSSEDR","UKIDSSSV"};
    public static String [] UKIDSSDB;
    
//    public static final String [] WORLDDB={"UKIDSSDR4PLUS","UKIDSSDR3PLUS","UKIDSSDR2PLUS","UKIDSSDR1PLUS","UKIDSSDR1","UKIDSSEDRPLUS","UKIDSSEDR","UKIDSSSV"}; //"WORLDR2" names for world releaase
    public static String [] WORLDDB;
    
//    public static final int [] WORLDVERSIONS={6,5,4,3,3,2,2,1}; // version numbers for WORLD releaase
    public static int [] WORLDVERSIONS;
    
//    public static final String [] SCEHMAVALIDDATABASES={"ukidssdr7plus","ukidssdr6plus","ukidssdr5plus","ukidssdr4plus","ukidssdr3plus"};//,"ukidssdr2plus","ukidssdr1plus","ukidssdr1","ukidssedrplus","ukidssedr","ukidssr2"}; // array of databases compatible with latest schema for source & detection

    public static String [] SCEHMAVALIDDATABASES;
        
    public static HashMap colMap;
    
    // loads the parameters listed above here
    static {
        LoadWSAProperties.getSingletonObject();
    }
    
    static {
        LoadWSAColumns.getSingletonObject();
    }
    
    
    
    public static final String LATESTUKIDSSDATABASE = UKIDSSDB[0];//"ukidssdr7plus";// "ukidssedr";
    public static final String LATESTWORLDDATABASE = WORLDDB[0];//"ukidssdr4plus"; 
    public static final String LATESTUKIDSSSCHEMA = UKIDSSDB[0];//"ukidssdr7plus"; // "ukidssedr";
    public static final String LATESTWORLDSCHEMA = WORLDDB[0];//"ukidssdr4plus"; // latest world release have the same schema as this release

    public static final String LATESTNONSURVEYSCHEMA = "ukidssr2"; // all non survey progs have the same schema as this release
    public static final String PRERELEASESERVER="ahmose";//"ahmose";
    public static final String PRERELEASEDATABASE="WSA";//"WSA";"WSA copy"
    public static final String PRERELEASECOMMUNITY="prerelease";
    public static final String NONSURVEYCOMMUNITY="nonSurvey";
    

    public static final String [] UKIDSSTESTERDB=ALLUKIDSSRDB;
    public static final int VERSIONOFSOURCEVIEWS=4;
    public static final String PTSDB="Transit";
    public static final String CALDB="WFCAMCAL08B";
    public static final String OPENDB="WFCAMOPENTIME";
    public static final String [] extraDB={PTSDB};
    public static final String [] calDBs={CALDB};
    public static final String [] worldCalDBs={CALDB};
    public static final String [] moreDB={OPENDB};
    public static final boolean doExtraDB=true;


  
    public static final String [] PRERELEASEDB={PRERELEASEDATABASE};
    public static final boolean accessToRollingDB=true; // should the archive listing etc give the option of querying a rolling DB 
    public static final String rollingDB="WSA";//"WSA";"WSA copy"
    public static final String rollingServer="amenhotep";//"amenhotep";//"ahmose";
    public static final String rollingDBUser="wsaro";
    public static final String rollingDBPasswd="wsaropw";
    public static final String noAccessDB="sorry no access";

    public static final String actualRollingDB="WFCAMPROPRIETY";//"WFCAMPROPRIETY";//"WSA";"TestWSArecal"
    public static final String ALLSURVEYS="All UKIDSS surveys";
    public static final int MAXIMUMROWSTOFILE=15000000; // maximum number of rows written to file by Queries not used yet
    public static  int FITS_OFFSET=1;
    public static final int WFCAMNONEFILTERID=0;
    public static final int WFCAMZFILTERID=1;
    public static final int WFCAMYFILTERID=2;
    public static final int WFCAMJFILTERID=3;
    public static final int WFCAMHFILTERID=4;
    public static final int WFCAMKFILTERID=5;
    public static final int WFCAMH2FILTERID=6;
    public static final int WFCAMBRFILTERID=7;
    public static final int WFCAMBLANKFILTERID=8;
    public static final int WFCAMNBJFILTERID=9;
    public static final int WFCAMNBHFILTERID=10;
    public static final int WFCAMNBKFILTERID=11;
    public static final int [] ALLFILTERID= {WFCAMNONEFILTERID,WFCAMZFILTERID,WFCAMYFILTERID, WFCAMJFILTERID, WFCAMHFILTERID,
            WFCAMKFILTERID, WFCAMH2FILTERID, WFCAMBRFILTERID, WFCAMBLANKFILTERID, WFCAMNBJFILTERID,WFCAMNBHFILTERID,WFCAMNBKFILTERID};
    
    public static final int COMMISSIONINGPROGRAMMEID=1;
    public static final int CURATIONTESTPROGRAMMEID=99;
    public static final int UKIDSSLASPROGRAMMEID=101;
    public static final int UKIDSSGPSPROGRAMMEID=102;
    public static final int UKIDSSGCSPROGRAMMEID=103;
    public static final int UKIDSSDXSPROGRAMMEID=104;
    public static final int UKIDSSUDSPROGRAMMEID=105;
    public static final int PTSPROGRAMMEID=106;
    public static final int UHSPROGRAMMEID=107;
    public static final int CALPROGRAMMEID=200;
    public static final int OTHERCALPROGRAMMEID=201;
    public static final int UKIDSSLASSVPROGRAMMEID=11;
    public static final int UKIDSSGPSSVPROGRAMMEID=12;
    public static final int UKIDSSGCSSVPROGRAMMEID=13;
    public static final int UKIDSSDXSSVPROGRAMMEID=14;
    public static final int UKIDSSUDSSVPROGRAMMEID=15;
    public static final String [] LASRGB={"K","H","Y"};
    public static final String [] GPSRGB={"K","H","J"};
    public static final String [] GCSRGB={"K","H","Z"};
    public static final String [] DXSRGB={"K","H","J"};
    public static final String [] UDSRGB={"K","H","J"};
    public static final String [] CALRGB={"K","H","Z"};
    
    public static final int [] UKIDSSTESTERPROGS={UKIDSSLASPROGRAMMEID,UKIDSSGPSPROGRAMMEID,UKIDSSGCSPROGRAMMEID,UKIDSSDXSPROGRAMMEID,UKIDSSUDSPROGRAMMEID,UHSPROGRAMMEID,
            UKIDSSLASSVPROGRAMMEID,UKIDSSGPSSVPROGRAMMEID,UKIDSSGCSSVPROGRAMMEID,UKIDSSDXSSVPROGRAMMEID,UKIDSSUDSSVPROGRAMMEID,COMMISSIONINGPROGRAMMEID};
    public static final int [] UKIDSSPROGS={UKIDSSLASPROGRAMMEID,UKIDSSGPSPROGRAMMEID,UKIDSSGCSPROGRAMMEID,UKIDSSDXSPROGRAMMEID,UKIDSSUDSPROGRAMMEID};
    public static final int [] UKIDSSUHSPROGS={UKIDSSLASPROGRAMMEID,UKIDSSGPSPROGRAMMEID,UKIDSSGCSPROGRAMMEID,UKIDSSDXSPROGRAMMEID,UKIDSSUDSPROGRAMMEID,UHSPROGRAMMEID};

    public static final int [] WORLDPROGS=UKIDSSPROGS;
    public static final int [] PRERELEASEPROGS=UKIDSSTESTERPROGS;
    
    
    public static final String COMMISSIONINGDETECTIONTABLENAME = "commDetection";
    public static final String UKIDSSLASDETECTIONTABLENAME = "lasDetection";
    public static final String UKIDSSGPSDETECTIONTABLENAME = "gpsDetection";
    public static final String UKIDSSGCSDETECTIONTABLENAME = "gcsDetection";
    public static final String UKIDSSDXSDETECTIONTABLENAME = "dxsDetection";
    public static final String UKIDSSUDSDETECTIONTABLENAME = "udsDetection";
    public static final String UHSDETECTIONTABLENAME = "uhsDetection";
    public static final String PTSDETECTIONTABLENAME = "ptsDetection";
    public static final String CALDETECTIONTABLENAME = "calDetection";
   
    public static final String COMMISSIONINGSOURCETABLENAME = "commSource";
    public static final String UKIDSSLASSOURCETABLENAME = "lasSource";
    public static final String UKIDSSGPSSOURCETABLENAME = "gpsSource";
    public static final String UKIDSSGCSSOURCETABLENAME = "gcsSource";
    public static final String UKIDSSDXSSOURCETABLENAME = "dxsSource";
    public static final String UKIDSSUDSSOURCETABLENAME = "udsSource";
    public static final String UHSSOURCETABLENAME = "uhsSource";
    
    public static final String UKIDSSLASSOURCEVIEWNAME = "lasYJHKsource";
    public static final String UKIDSSGPSSOURCEVIEWNAME = "gpsJHKsource";
    public static final String UKIDSSGCSSOURCEVIEWNAME = "gcsZYJHKsource";
    public static final String UKIDSSDXSSOURCEVIEWNAME = "dxsJKSource";
    public static final String UKIDSSUDSSOURCEVIEWNAME = null;
    public static final String UHSSOURCEVIEWNAME = null;
    
    public static final String PTSSOURCETABLENAME = "NONE";
    public static final String CALSOURCETABLENAME = "calSource";
    
    public static final String CALMERGETABLENAME = "calMergeLog";
    public static final String COMMISSIONINGMERGETABLENAME = "commMergeLog";
    public static final String UKIDSSLASMERGETABLENAME = "lasMergeLog";
    public static final String UKIDSSGPSMERGETABLENAME = "gpsMergeLog";
    public static final String UKIDSSGCSMERGETABLENAME = "gcsMergeLog";
    public static final String UKIDSSDXSMERGETABLENAME = "dxsMergeLog";
    public static final String UKIDSSUDSMERGETABLENAME = "udsMergeLog";
    public static final String UHSMERGETABLENAME = "uhsMergeLog";
    public static final String UKIDSSPTSMERGETABLENAME = "NONE";
    
    
    
    public static final String DEFAULTSQLSERVER = "amenhotep";
    public static final String UKIDSSLOGIN = "wsaro";
    public static final String WORLDLOGIN = "worldwsaro";
    public static final String [] WFCAMFILTERS = {"NONE","Z","Y","J","H","K","H2","Br","BLNK","NBJ","NBH","NBK"};
    public static final String [] filters = WFCAMFILTERS; //deprecated
    public static final String [] COMMISSIONINGBands={"Z","Y","J","H","K","H2","Br"};
    public static final int [] COMMISSIONINGREQFILTERS={0,1,1,1,1,1,1,1,0};
    public static final String [] LASBands={"Y","J_1","J_2","H","K"};
    public static final int [] LASREQFILTERS={0,0,1,2,1,1,0,0,0};
    public static final String [] GPSBands={"J","H","K_1","K_2","H2"};//{"J","H","K_1","K_2","K_3","H2_1","H2_2","H2_3"};
    public static final int [] GPSREQFILTERS={0,0,0,1,1,2,1,0,0};//{0,0,1,1,3,3,0,0};
    public static final int [] GPSREQFILTERSDR2={0,0,1,1,3,3,0,0};
    public static final String [] GCSBands={"Z","Y","J","H","K_1","K_2"};
    public static final int [] GCSREQFILTERS={0,1,1,1,1,2,0,0,0};
    public static final String [] DXSBands={"J","H","K"};
    public static final int [] DXSREQFILTERS={0,0,0,1,1,1,0,0,0};    
    public static final String [] UDSBands={"J","H","K"};
    public static final int [] UDSREQFILTERS={0,0,0,1,1,1,0,0,0};
    public static final String [] UHSBands={"J"};
    public static final int [] UHSREQFILTERS={0,0,0,1,0,0,0,0,0};   
    public static final String [] CALBands={"Z","Y","J","H","K","H2","Br","NBJ"};
    public static final int [] CALREQFILTERS={0,1,1,1,1,1,1,1,0,1};

   
    public static String getPropertyFromFile(String property,String filename){
      
        Properties properties = new Properties();
        try {
             properties.load(new FileInputStream(filename));
             if(properties.getProperty(property) != null) {
                 return properties.getProperty(property);
             }
             return "";
             
        } catch (IOException e) {
          return "";
         }
        }   
    
    public static boolean getDoRollingDBFromFile(String filename) {
    Properties properties = new Properties();
    try {
         properties.load(new FileInputStream(filename));
         if(properties.getProperty("doRollingDB").equalsIgnoreCase("true")) {
             return true;
         }
         return false;
         
    } catch (IOException e) {
      return false;
     }
    }
    public static boolean isProgInDB (String db, int prog) {
        if (getVersionNoOfRelease(db) >= getDetectionSchemaVersion(prog) && getVersionNoOfRelease(db) <= getLastSchemaVersion(prog)) {
            return true;
        }
        else {
            return false;
        }
    }
    public static boolean isSourceInDB (String db, int prog) {
        if (getVersionNoOfRelease(db) >= getSourceSchemaVersion(prog)) {
            return true;
        }
        else {
            return false;
        }
    }
    public static int getVersionNoOfRelease(String schema) {
        for (int i=0;i < ALLUKIDSSRDB.length; i++) {
            if (schema.equalsIgnoreCase(ALLUKIDSSRDB[i])) {
                return UKIDSSVERSIONS[i];
            }
            }
        if (schema.equalsIgnoreCase(rollingDB)) {
            return 9999;
        }
        for (int i=0;i < WORLDDB.length; i++) {
            if (schema.equalsIgnoreCase(WORLDDB[i])) {
                return WORLDVERSIONS[i];
            }
            }
        return -1;
        }
  
    public static int [] getReqFilters(String database,int surveyID){
        switch(surveyID){
		case  WSASchema.COMMISSIONINGPROGRAMMEID:
		    return COMMISSIONINGREQFILTERS;
		case  WSASchema.CALPROGRAMMEID:
		    return CALREQFILTERS;
		case  WSASchema.UKIDSSLASPROGRAMMEID:
		    return LASREQFILTERS;
		case  WSASchema.UHSPROGRAMMEID:
		    return UHSREQFILTERS;
		case  WSASchema.UKIDSSLASSVPROGRAMMEID:
		    return LASREQFILTERS;
		case  WSASchema.UKIDSSGPSPROGRAMMEID:
		    if (getVersionNoOfRelease(database) >= getVersionNoOfRelease("ukidssdr3plus") || database.equals("") || database == null) {
		        return GPSREQFILTERS;
		    }
		    else {
		        return GPSREQFILTERSDR2;
		    }
		case  WSASchema.UKIDSSGPSSVPROGRAMMEID:
		    if (getVersionNoOfRelease(database) >= getVersionNoOfRelease("ukidssdr3plus")) {
		        return GPSREQFILTERS;
		    }
		    else {
		        return GPSREQFILTERSDR2;
		    }
		case  WSASchema.UKIDSSGCSPROGRAMMEID:
		    return GCSREQFILTERS;
		case  WSASchema.UKIDSSGCSSVPROGRAMMEID:
		    return GCSREQFILTERS;
		case  WSASchema.UKIDSSDXSPROGRAMMEID:
		    return DXSREQFILTERS;
		case  WSASchema.UKIDSSDXSSVPROGRAMMEID:
		    return DXSREQFILTERS;
		case  WSASchema.UKIDSSUDSPROGRAMMEID:
		    return UDSREQFILTERS;
		case  WSASchema.UKIDSSUDSSVPROGRAMMEID:
		    return UDSREQFILTERS;
		default :
		return null;
    } 
    }
    public static int [] getReqFilters(int surveyID){
        switch(surveyID){
		case  WSASchema.COMMISSIONINGPROGRAMMEID:
		    return COMMISSIONINGREQFILTERS;
		case  WSASchema.CALPROGRAMMEID:
		    return CALREQFILTERS;
		case  WSASchema.UKIDSSLASPROGRAMMEID:
		    return LASREQFILTERS;
		case  WSASchema.UHSPROGRAMMEID:
		    return UHSREQFILTERS;
		case  WSASchema.UKIDSSLASSVPROGRAMMEID:
		    return LASREQFILTERS;
		case  WSASchema.UKIDSSGPSPROGRAMMEID:
		    return GPSREQFILTERS;
		case  WSASchema.UKIDSSGPSSVPROGRAMMEID:
		    return GPSREQFILTERS;
		case  WSASchema.UKIDSSGCSPROGRAMMEID:
		    return GCSREQFILTERS;
		case  WSASchema.UKIDSSGCSSVPROGRAMMEID:
		    return GCSREQFILTERS;
		case  WSASchema.UKIDSSDXSPROGRAMMEID:
		    return DXSREQFILTERS;
		case  WSASchema.UKIDSSDXSSVPROGRAMMEID:
		    return DXSREQFILTERS;
		case  WSASchema.UKIDSSUDSPROGRAMMEID:
		    return UDSREQFILTERS;
		case  WSASchema.UKIDSSUDSSVPROGRAMMEID:
		    return UDSREQFILTERS;
		default :
		return null;
    } 
    }

   
    public static String [] getDBsPlusRollingDB(String [] inDBs) {
        String [] retDBs=new String [inDBs.length+1];
        for (int i=0;i<inDBs.length;i++) {
            retDBs[i]=inDBs[i];
        }
        retDBs[retDBs.length-1]=WSASchema.rollingDB;
        return retDBs;
    }
    
    public static String [] getDBsPlusExtraDB(String [] inDBs, String [] extraDBs) {
        if (doExtraDB) {
        String [] retDBs=new String [inDBs.length+extraDBs.length];
        for (int i=0;i<inDBs.length;i++) {
            retDBs[i]=inDBs[i];
        }
        for (int i=0;i<extraDBs.length;i++) {
            retDBs[inDBs.length+i]=extraDBs[i];
        }
        
        return retDBs;
        } else {
            return inDBs;
        }
    }
    
    public static String getDefaultList(int[] reqFilters, String table,
            String database) {
        boolean validSchema=false;
        if (database.equalsIgnoreCase(PTSDB)) {
            database=LATESTUKIDSSDATABASE;
        }
        database=database.replaceAll("(?i)world","ukidss");
        for (int i=0;i< SCEHMAVALIDDATABASES.length;i++){
            if (database.equalsIgnoreCase(SCEHMAVALIDDATABASES[i])) {
                validSchema=true;
            }
        }
        if (table.toLowerCase().indexOf("detection") >= 0) {
            if (validSchema) {
                return "objID, multiframeID, filterID, RA, Dec, ell, pa, class, psfMag, hallMag,isoMag, petroMag, aperMag2, aperMag3"; //
            } else {
                return table+".*";
            }

        } else if (table.toLowerCase().indexOf("source") >= 0) {
            if (validSchema) {
                StringBuffer sb = new StringBuffer(
                        "sourceID, framesetID, RA, Dec, mergedClass, priOrSec");
                if (reqFilters != null) {
                    sb.append(getReqMags(reqFilters));
                }
                return sb.toString();
            } else {
                return table+".*";
            }
        } else {
            return table+".*";
        }
    }
    
    
    public static String getDefaultList(int [] reqFilters, String table){
       return getDefaultList(reqFilters,table,LATESTUKIDSSSCHEMA);
   
    }
    
        public static String getReqMags(int [] reqFilters){
            StringBuffer sb1=new StringBuffer("");
            for (int i=0;i<reqFilters.length;i++){
                if (reqFilters[i] ==1 ){
                    //sb1.append(", "+getFilterName(i+1)+"HallMag"); 
                    //sb1.append(", "+getFilterName(i+1)+"PetroMag");
                   // sb1.append(", "+getFilterName(i+1)+"AperMag3");
                    sb1.append(", "+getFilterName(i)+"AperMag3");
                    
                    //sb1.append(", "+getFilterName(i+1)+"HallMagErr"); 
                    //sb1.append(", "+getFilterName(i+1)+"PetroMagErr");
                    //sb1.append(", "+getFilterName(i+1)+"AperMag3Err");
                    sb1.append(", "+getFilterName(i)+"AperMag3Err");
                    
                }
                if (reqFilters[i] > 1) {
                    for (int j=0;j<reqFilters[i];j++){
                      //  sb1.append(", "+getFilterName(i+1)+"_"+(j+1)+"HallMag");
                      //  sb1.append(", "+getFilterName(i+1)+"_"+(j+1)+"PetroMag");
                      //  sb1.append(", "+getFilterName(i+1)+"_"+(j+1)+"AperMag3");
                        sb1.append(", "+getFilterName(i)+"_"+(j+1)+"AperMag3");
                      //  sb1.append(", "+getFilterName(i+1)+"_"+(j+1)+"HallMagErr");
                      //  sb1.append(", "+getFilterName(i+1)+"_"+(j+1)+"PetroMagErr");
                       // sb1.append(", "+getFilterName(i+1)+"_"+(j+1)+"AperMag3Err");
                        sb1.append(", "+getFilterName(i)+"_"+(j+1)+"AperMag3Err");
                        
                    }
                }
                
            }
            return sb1.toString();
        }
    
        public static String [] getBands(int [] reqBands){
            int noBands=0;
            for (int i=0;i<reqBands.length;i++){
                noBands=noBands+reqBands[i];
            }
            String [] bands= new String[noBands];
            int bandCount=0;
            for (int i=0;i<reqBands.length;i++){
            if (reqBands[i] ==1 ){
               bands[bandCount]=getFilterName(i);
               bandCount++;
            }
            if (reqBands[i] > 1) {
                for (int j=0; j<reqBands[i];j++){
                    bands[bandCount]=getFilterName(i)+"_"+(j+1);
                    bandCount++;
                }
                
            }
            }
            return bands;
        }
        public static String [] getBands(String database,int surveyID){
            int [] reqBands=getReqFilters(database,surveyID);
            int noBands=0;
            for (int i=0;i<reqBands.length;i++){
                noBands=noBands+reqBands[i];
            }
            String [] bands= new String[noBands];
            int bandCount=0;
            for (int i=0;i<reqBands.length;i++){
            if (reqBands[i] ==1 ){
              // bands[bandCount]=getFilterName(i+1);
               bands[bandCount]=getFilterName(i);
               bandCount++;
            }
            if (reqBands[i] > 1) {
                for (int j=0; j<reqBands[i];j++){
                   // bands[bandCount]=getFilterName(i+1)+"_"+(j+1);
                    bands[bandCount]=getFilterName(i)+"_"+(j+1);
                    bandCount++;
                }
                
            }
            }
            return bands;
        }
    public static String [] getBands(int surveyID){
        switch(surveyID){
		case  WSASchema.COMMISSIONINGPROGRAMMEID:
		    return COMMISSIONINGBands;
		case  WSASchema.UKIDSSLASPROGRAMMEID:
		    return LASBands;
		case  WSASchema.UKIDSSLASSVPROGRAMMEID:
		    return LASBands;
		case  WSASchema.UKIDSSGPSPROGRAMMEID:
		    return GPSBands;
		case  WSASchema.UKIDSSGPSSVPROGRAMMEID:
		    return GPSBands;
		case  WSASchema.UKIDSSGCSPROGRAMMEID:
		    return GCSBands;
		case  WSASchema.UKIDSSGCSSVPROGRAMMEID:
		    return GCSBands;
		case  WSASchema.UKIDSSDXSPROGRAMMEID:
		    return DXSBands;
		case  WSASchema.UKIDSSDXSSVPROGRAMMEID:
		    return DXSBands;
		case  WSASchema.UKIDSSUDSPROGRAMMEID:
		    return UDSBands;
		case  WSASchema.UKIDSSUDSSVPROGRAMMEID:
		    return UDSBands;
		case  WSASchema.UHSPROGRAMMEID:
		    return UHSBands;
		default :
		return null;
    }
    }
    
    
    
    public static String getMergeTableName(int surveyID){
        String mergeTableName;
        switch(surveyID){
		case  WSASchema.COMMISSIONINGPROGRAMMEID:
			mergeTableName=COMMISSIONINGMERGETABLENAME;
			break;
		case  WSASchema.CALPROGRAMMEID:
			mergeTableName=CALMERGETABLENAME;
			break;
		case  WSASchema.UKIDSSLASPROGRAMMEID:
	    mergeTableName=UKIDSSLASMERGETABLENAME;
	    break;
		case  WSASchema.UKIDSSGPSPROGRAMMEID:
		mergeTableName=UKIDSSGPSMERGETABLENAME;
		break;
		case  WSASchema.UKIDSSGCSPROGRAMMEID:
			mergeTableName=UKIDSSGCSMERGETABLENAME;
			break;
		case  WSASchema.UKIDSSDXSPROGRAMMEID:
			mergeTableName=UKIDSSDXSMERGETABLENAME;
			break;
		case  WSASchema.UKIDSSUDSPROGRAMMEID:
			mergeTableName=UKIDSSUDSMERGETABLENAME;
			break;
		case  WSASchema.UHSPROGRAMMEID:
			mergeTableName=UHSMERGETABLENAME;
			break;
		case  WSASchema.UKIDSSLASSVPROGRAMMEID:
		    mergeTableName=UKIDSSLASMERGETABLENAME;
		    break;
			case  WSASchema.UKIDSSGPSSVPROGRAMMEID:
			mergeTableName=UKIDSSGPSMERGETABLENAME;
			break;
			case  WSASchema.UKIDSSGCSSVPROGRAMMEID:
				mergeTableName=UKIDSSGCSMERGETABLENAME;
				break;
			case  WSASchema.UKIDSSDXSSVPROGRAMMEID:
				mergeTableName=UKIDSSDXSMERGETABLENAME;
				break;
			case  WSASchema.UKIDSSUDSSVPROGRAMMEID:
				mergeTableName=UKIDSSUDSMERGETABLENAME;
				break;		
		default :
		mergeTableName=null;  
        break;
    }
        return mergeTableName;
    }

    public static int getSourceSchemaVersion(int surveyID){
        int schemaVersion=-1;
        switch(surveyID){
		case  WSASchema.PTSPROGRAMMEID:
			schemaVersion=99999; // change when sourec available
			break;
		case  WSASchema.CALPROGRAMMEID:
			schemaVersion=99999; // change when sourec available
			break;
		default : // all the others have been in from the start
			schemaVersion=-1;  
	        break;
        }
	        return schemaVersion;
    }
    public static int getDetectionSchemaVersion(int surveyID){
        int schemaVersion=-1;
        switch(surveyID){
		case  WSASchema.PTSPROGRAMMEID:
			schemaVersion=-1; // set high as not to be released
			break;
		case  WSASchema.CALPROGRAMMEID:
			schemaVersion=-1; // set high as not to be released
			break;
		default : // all the others have been in from the start
			schemaVersion=-1;  
	        break;
        }
	        return schemaVersion;
    }
    
    public static int getLastSchemaVersion(int surveyID){
        int schemaVersion=99999999;
        switch(surveyID){
/*		case  WSASchema.UKIDSSGPSPROGRAMMEID:
			schemaVersion=5; 
			break;
		case  WSASchema.UKIDSSUDSPROGRAMMEID:
			schemaVersion=5; 
			break;
			*/
		default : // all the others have been in from the start
			schemaVersion=99999999;  
	        break;
        }
	        return schemaVersion;
    }
    
    
    public static String getSourceTableName(int surveyID){
        String sourceTableName;
        switch(surveyID){
		case  WSASchema.COMMISSIONINGPROGRAMMEID:
			sourceTableName=COMMISSIONINGSOURCETABLENAME;
			break;
		case  WSASchema.CALPROGRAMMEID:
			sourceTableName=CALSOURCETABLENAME;
			break;
		case  WSASchema.UKIDSSLASPROGRAMMEID:
	    sourceTableName=UKIDSSLASSOURCETABLENAME;
	    break;
		case  WSASchema.UKIDSSGPSPROGRAMMEID:
		sourceTableName=UKIDSSGPSSOURCETABLENAME;
		break;
		case  WSASchema.UKIDSSGCSPROGRAMMEID:
			sourceTableName=UKIDSSGCSSOURCETABLENAME;
			break;
		case  WSASchema.UKIDSSDXSPROGRAMMEID:
			sourceTableName=UKIDSSDXSSOURCETABLENAME;
			break;
		case  WSASchema.UKIDSSUDSPROGRAMMEID:
			sourceTableName=UKIDSSUDSSOURCETABLENAME;
			break;
		case  WSASchema.UHSPROGRAMMEID:
			sourceTableName=UHSSOURCETABLENAME;
			break;
		case  WSASchema.UKIDSSLASSVPROGRAMMEID:
		    sourceTableName=UKIDSSLASSOURCETABLENAME;
		    break;
			case  WSASchema.UKIDSSGPSSVPROGRAMMEID:
			sourceTableName=UKIDSSGPSSOURCETABLENAME;
			break;
			case  WSASchema.UKIDSSGCSSVPROGRAMMEID:
				sourceTableName=UKIDSSGCSSOURCETABLENAME;
				break;
			case  WSASchema.UKIDSSDXSSVPROGRAMMEID:
				sourceTableName=UKIDSSDXSSOURCETABLENAME;
				break;
			case  WSASchema.UKIDSSUDSSVPROGRAMMEID:
				sourceTableName=UKIDSSUDSSOURCETABLENAME;
				break;
		default :
		sourceTableName=null;  
        break;
    }
        return sourceTableName;
    }
    
    
    public static String [] getDefaultRGB(int surveyID) {
        String [] RGB=null;
        switch(surveyID){
		case  WSASchema.UKIDSSLASPROGRAMMEID:
	    RGB = LASRGB;
	    break;
		case  WSASchema.UKIDSSGPSPROGRAMMEID:
		RGB = GPSRGB;
		break;
		case  WSASchema.UKIDSSGCSPROGRAMMEID:
		RGB = GCSRGB;
		break;
		case  WSASchema.UKIDSSDXSPROGRAMMEID:
		RGB = DXSRGB;
		break;
		case  WSASchema.UKIDSSUDSPROGRAMMEID:
		RGB = UDSRGB;
	    break;	
		case  WSASchema.CALPROGRAMMEID:
			RGB = CALRGB;
		    break;	
		default :  
        break;
        }
        return RGB;
    }
    public static String getSourceViewName(int surveyID){
        String sourceViewName;
        switch(surveyID){
		case  WSASchema.UKIDSSLASPROGRAMMEID:
	    sourceViewName=UKIDSSLASSOURCEVIEWNAME;
	    break;
		case  WSASchema.UKIDSSGPSPROGRAMMEID:
		sourceViewName=UKIDSSGPSSOURCEVIEWNAME;
		break;
		case  WSASchema.UKIDSSGCSPROGRAMMEID:
	    sourceViewName=UKIDSSGCSSOURCEVIEWNAME;
		break;
		case  WSASchema.UKIDSSDXSPROGRAMMEID:
		sourceViewName=UKIDSSDXSSOURCEVIEWNAME;
		break;
		case  WSASchema.UKIDSSUDSPROGRAMMEID:
			sourceViewName=UKIDSSUDSSOURCEVIEWNAME;
			break;
				
		default :
		sourceViewName=null;  
        break;
    }
        return sourceViewName;
    }
    public static String getDetectionTableName(int surveyID){
        String detectionTableName;
        switch(surveyID){
		case  WSASchema.COMMISSIONINGPROGRAMMEID:
			detectionTableName=COMMISSIONINGDETECTIONTABLENAME;
			break;
		case  WSASchema.UKIDSSLASPROGRAMMEID:
	    detectionTableName=UKIDSSLASDETECTIONTABLENAME;
	    break;
		case  WSASchema.UKIDSSGPSPROGRAMMEID:
		detectionTableName=UKIDSSGPSDETECTIONTABLENAME;
		break;
		case  WSASchema.UKIDSSGCSPROGRAMMEID:
			detectionTableName=UKIDSSGCSDETECTIONTABLENAME;
			break;
		case  WSASchema.UKIDSSDXSPROGRAMMEID:
			detectionTableName=UKIDSSDXSDETECTIONTABLENAME;
			break;
		case  WSASchema.UKIDSSUDSPROGRAMMEID:
			detectionTableName=UKIDSSUDSDETECTIONTABLENAME;
			break;
		case  WSASchema.UHSPROGRAMMEID:
			detectionTableName=UHSDETECTIONTABLENAME;
			break;
		case  WSASchema.PTSPROGRAMMEID:
			detectionTableName=PTSDETECTIONTABLENAME;
			break;
		case  WSASchema.CALPROGRAMMEID:
			detectionTableName=CALDETECTIONTABLENAME;
			break;
		case  WSASchema.UKIDSSLASSVPROGRAMMEID:
		    detectionTableName=UKIDSSLASDETECTIONTABLENAME;
		    break;
			case  WSASchema.UKIDSSGPSSVPROGRAMMEID:
			detectionTableName=UKIDSSGPSDETECTIONTABLENAME;
			break;
			case  WSASchema.UKIDSSGCSSVPROGRAMMEID:
				detectionTableName=UKIDSSGCSDETECTIONTABLENAME;
				break;
			case  WSASchema.UKIDSSDXSSVPROGRAMMEID:
				detectionTableName=UKIDSSDXSDETECTIONTABLENAME;
				break;
			case  WSASchema.UKIDSSUDSSVPROGRAMMEID:
				detectionTableName=UKIDSSUDSDETECTIONTABLENAME;
				break;	
				
			
			
		default :
		detectionTableName=null;  
        break;
    }
        return detectionTableName;
    }

    public static String getSurveyName(int surveyID){
        String surveyName;
        switch(surveyID){
		case  WSASchema.COMMISSIONINGPROGRAMMEID:
			surveyName="Commissioning programme";
			break;
			
		case  WSASchema.CURATIONTESTPROGRAMMEID:
		surveyName="Curation test programme";
		break;
		case  WSASchema.UKIDSSLASPROGRAMMEID:
	    surveyName="UKIDSS Large Area Survey, LAS";
	    break;
		case  WSASchema.UKIDSSGPSPROGRAMMEID:
		surveyName="UKIDSS Galactic Plane Survey, GPS";
		break;
		case  WSASchema.UKIDSSGCSPROGRAMMEID:
			surveyName="UKIDSS Galactic Clusters Survey, GCS";
			break;
		case  WSASchema.UKIDSSDXSPROGRAMMEID:
			surveyName="UKIDSS Deep Extragalactic Survey, DXS";
			break;
		case  WSASchema.UKIDSSUDSPROGRAMMEID:
			surveyName="UKIDSS Ultra Deep Survey, UDS";
			break;
		case  WSASchema.UHSPROGRAMMEID:
			surveyName="UKIRT Hemisphere Survey, UHS";
			break;
		case  WSASchema.PTSPROGRAMMEID:
			surveyName="Planetary Transit Survey, PTS";
			break;
		case  WSASchema.CALPROGRAMMEID:
			//surveyName="Calibration programme";
		    surveyName="UKIRT Faint Standard Observations";
			break;			
		case  WSASchema.UKIDSSLASSVPROGRAMMEID:
		    surveyName="UKIDSS Large Area Survey, LAS, Science Verification";
		    break;
			case  WSASchema.UKIDSSGPSSVPROGRAMMEID:
			surveyName="UKIDSS Galactic Plane Survey, GPS, Science Verification";
			break;
			case  WSASchema.UKIDSSGCSSVPROGRAMMEID:
				surveyName="UKIDSS Galactic Clusters Survey, GCS, Science Verification";
				break;
			case  WSASchema.UKIDSSDXSSVPROGRAMMEID:
				surveyName="UKIDSS Deep Extragalactic Survey, DXS, Science Verification";
				break;
			case  WSASchema.UKIDSSUDSSVPROGRAMMEID:
				surveyName="UKIDSS Ultra Deep Survey, UDS, Science Verification";
				break;
		default :
		surveyName="Survey name not found in lookup";  
        break;
    }
        return surveyName;
    }
    public static String getFilterName(int filterID){
        if (filterID >=0 && filterID <= WFCAMFILTERS.length && WFCAMFILTERS[filterID] != null ) {
            return WFCAMFILTERS[filterID];
        }
        else {
            return "NULL";
        }
  /*      String filterName;
        switch(filterID){        
		case  WSASchema.WFCAMZFILTERID:
		filterName="Z";
		break;     
		case  WSASchema.WFCAMYFILTERID:
		filterName="Y";
		break;
		case  WSASchema.WFCAMJFILTERID:
	    filterName="J";
	    break;
		case  WSASchema.WFCAMHFILTERID:
		filterName="H";
		break;
		case  WSASchema.WFCAMKFILTERID:
			filterName="K";
			break;
		case  WSASchema.WFCAMH2FILTERID:
			filterName="H2";
			break;
		case  WSASchema.WFCAMBRFILTERID:
			filterName="Br";
			break;
		case  WSASchema.WFCAMBLANKFILTERID:
			filterName="BLNK";
			break;
		case  WSASchema.WFCAMNBJFILTERID:
			filterName="BLNK";
			break;
		default :
		filterName="NULL";  
        break;
    }
        return filterName;*/
    }   

    public static String getRequiredFiltersSQL(){
     return "select filterid,npass from requiredfilters";   
    }
    
    public static StringBuffer getFramesetSQL(int [] fID,String mergelogTable,String multiframeTable) {
        StringBuffer sbSQL=new StringBuffer("");
        String mfidName=null;
        int npass;
        int filterid;
        int row=0;
        boolean first=true;
        for (int j=0; j<fID.length;j++) {
            filterid=j;
            npass=fID[j];
            row=row+npass;
            if (npass==1) {
                mfidName=WSASchema.getFilterName(filterid)+"mfid";
                if (!first) {
                    sbSQL.append(" or ");
                }
                first=false;
                sbSQL.append(mergelogTable+"."+mfidName+"="+multiframeTable+".multiframeid");
    
            }
            else if (npass > 1){
                for (int i=0; i < npass; i++){
                    mfidName=WSASchema.getFilterName(filterid)+"_"+(i+1)+"mfid";
                    if (!first) {
                        sbSQL.append(" or ");
                    }
                    first=false;
                    sbSQL.append(mergelogTable+"."+mfidName+"="+multiframeTable+".multiframeid");  
                }
            }
           }
        if ( row > 0 ){
            return sbSQL;
            }
            else {
                return null;
            }
        
    }
    
    public static String getCrossIDTableTable(String community, String user, String baseTable, int intProgrammeID) {
        if (community.equalsIgnoreCase("nonSurvey")) {
            return user.trim()+baseTable;
        }
        else
        {
            if (baseTable.equalsIgnoreCase("sourceView")) {
                return WSASchema.getSourceViewName(intProgrammeID);
            }
            else if (baseTable.equalsIgnoreCase("source")) {
            return WSASchema.getSourceTableName(intProgrammeID);
        }
            else if (baseTable.equalsIgnoreCase("detection")) {
                return WSASchema.getDetectionTableName(intProgrammeID);
            }
            return "";
        }          
    }
    public static String getCrossIDDefaultSelect(String community, String tableName, HttpSession session ,int programmeID, String database) {
        if (community.equalsIgnoreCase("nonSurvey")) {
            int [] reqFilters=null;
            if (session.getAttribute("reqFilters") !=null){
                reqFilters=((int [])session.getAttribute("reqFilters"));
                }
           return WSASchema.getDefaultList(reqFilters,tableName,WSASession.getSchemaVersion(session));
        }
        else
        {
            return WSASchema.getDefaultList(WSASchema.getReqFilters(programmeID),tableName,database);
        }
    }
    
    
    public static StringBuffer getFramesetSQL(ResultSet rset,String mergelogTable,String multiframeTable){
        StringBuffer sbSQL=new StringBuffer("");
        String mfidName=null;
        int npass;
        int filterid;
        int row=0;
        boolean first=true;
       
        try {
        while (rset.next()){
            row++;
           filterid=rset.getInt("filterid");
           npass=rset.getInt("npass");
           if (npass==1) {
               mfidName=WSASchema.getFilterName(filterid)+"mfid";
               if (!first) {
                   sbSQL.append(" or ");
               }
               first=false;
               sbSQL.append(mergelogTable+"."+mfidName+"="+multiframeTable+".multiframeid");
   
           }
           else if (npass > 1){
               for (int i=0; i < npass; i++){
                   mfidName=WSASchema.getFilterName(filterid)+"_"+(i+1)+"mfid";
                   if (!first) {
                       sbSQL.append(" or ");
                   }
                   first=false;
                   sbSQL.append(mergelogTable+"."+mfidName+"="+multiframeTable+".multiframeid");  
               }
           }
        }
        if ( row > 0 ){
        return sbSQL;
        }
        else {
            return null;
        }
        }
        catch (SQLException se){
            return null;
        }
        
    }
    public static String getUCDofCol(String col) {
    	if (colMap != null && colMap.containsKey(col.toUpperCase())) {
    		String info=(String)colMap.get(col.toUpperCase());
    		String [] bits=info.split("###");
    		return bits[0];
    		
    	}
    	else {
    		return "unknown";
    	}
    }
    
    public static String getDescofCol(String col) {
    	if (colMap != null && colMap.containsKey(col.toUpperCase())) {
    		String info=(String)colMap.get(col.toUpperCase());
    		String [] dBits=info.split("###");
    		return dBits[1];
    		
    	}
    	else {
    		return "unknown";
    	}
    }
   

}