/*
 * 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.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 VSASchema extends WSASchema {
 
    /**
     * Offset between extension numbers held in database and what cfitsio
     * expects.
     */
    public static final String LATESTUKIDSSDATABASE = "ukidssdr5plus";// "ukidssedr";
    public static final String LATESTWORLDDATABASE = "ukidssdr3plus"; 
    public static final String [] SCEHMAVALIDDATABASES={"ukidssdr5plus","ukidssdr4plus","ukidssdr3plus"};//,"ukidssdr2plus","ukidssdr1plus","ukidssdr1","ukidssedrplus","ukidssedr","ukidssr2"}; // array of databases compatible with latest schema for source & detection
    public static final String LATESTUKIDSSSCHEMA = "ukidssdr5plus"; // "ukidssedr";
    public static final String LATESTWORLDSCHEMA = "ukidssdr3plus"; // 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="ramses2";//"ahmose";
    public static final String PRERELEASEDATABASE="VSA";//"WSA";"WSA copy"
    public static final String PRERELEASECOMMUNITY="prerelease";
    public static final String NONSURVEYCOMMUNITY="nonSurvey";
    
    public static String NODBAVAILABLE="No database available";
    
    public static String COMMDB []={};
    public static String VHSDB []={};
    public static String VVVDB []={};
    public static String VMCDB []={};
    public static String VIKINGDB []={};
    public static String VIDEODB []={};
    public static String ULTRAVISTADB []={};
    public static String SVORIONDB []={};
    public static String SVNGC253DB []={};
    public static String GCSDB []={"UKIDSSDR5PLUS","UKIDSSDR3PLUS","UKIDSSDR2PLUS"};
    
    public static String WORLDCOMMDB []={};
    public static String WORLDVHSDB []={};
    public static String WORLDVVVDB []={};
    public static String WORLDVMCDB []={};
    public static String WORLDVIKINGDB []={};
    public static String WORLDVIDEODB []={};
    public static String WORLDULTRAVISTADB []={};
    public static String WORLDSVORIONDB []={};
    public static String WORLDSVNGC253DB []={};
    public static String WORLDGCSDB []={"UKIDSSDR3PLUS","UKIDSSDR2PLUS"};
    
    
    
    public static final String [] ALLUKIDSSRDB={"UKIDSSDR5PLUS","UKIDSSDR4PLUS","UKIDSSDR3PLUS","UKIDSSDR2PLUS","UKIDSSDR1PLUS","UKIDSSDR1","UKIDSSEDRPLUS","UKIDSSEDR","UKIDSSSV","UKIDSSR1"}; // names for UKIDSS releaase
    public static final int [] UKIDSSVERSIONS={7,6,5,4,3,3,2,2,1,0}; // version numbers for UKIDSS releaase
    public static final String [] UKIDSSTESTERDB=ALLUKIDSSRDB;
    public static final int VERSIONOFSOURCEVIEWS=4;
    public static final String PTSDB="Transit";
    public static final String CALDB="Calibration";
    public static final String OPENDB="WFCAMOPENTIME";
    public static final String [] extraDB={PTSDB};
    public static final String [] moreDB={OPENDB};
    public static final boolean doExtraDB=true;
    public static final String [] UKIDSSDB={"UKIDSSDR5PLUS","UKIDSSDR4PLUS","UKIDSSDR3PLUS","UKIDSSDR2PLUS","UKIDSSDR1PLUS","UKIDSSDR1","UKIDSSEDRPLUS","UKIDSSEDR","UKIDSSSV"};
    
    public static final String [] WORLDDB={"UKIDSSDR3PLUS","UKIDSSDR2PLUS","UKIDSSDR1PLUS","UKIDSSDR1","UKIDSSEDRPLUS","UKIDSSEDR","UKIDSSSV"}; //"WORLDR2" names for world releaase
    public static final int [] WORLDVERSIONS={5,4,3,3,2,2,1}; // version numbers for WORLD releaase


  
    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="VSA";//"WSA";"WSA copy"
    public static final String [] rollingDBs={rollingDB};
    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 int COMMISSIONINGPROGRAMMEID=1;
    public static final int CURATIONTESTPROGRAMMEID=99;
    public static final int ALLPROGRAMMEID=0;
    public static final int VHSPROGRAMMEID=110;
    public static final int VVVPROGRAMMEID=120;
    public static final int VMCPROGRAMMEID=130;
    public static final int VIKINGPROGRAMMEID=140;
    public static final int VIDEOPROGRAMMEID=150;
    public static final int ULTRAVISTAPROGRAMMEID=160;
    public static final int SVORIONPROGRAMMEID=100;
    public static final int SVNGC253PROGRAMMEID=101;
    
    public static final String actualRollingDB="VISTAPROPRIETY";//"VSAPROPRIETY";//"WFCAMPROPRIETY";//"WSA";"TestWSArecal"
    public static final String ALLSURVEYS="All VISTA 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 VISTANONEFILTERID=0;
    public static final int VISTAZFILTERID=1;
    public static final int VISTAYFILTERID=2;
    public static final int VISTAJFILTERID=3;
    public static final int VISTAHFILTERID=4;
    public static final int VISTAKFILTERID=5;
    public static final int VISTAH2FILTERID=6;
    public static final int VISTABRFILTERID=7;
    public static final int VISTABLANKFILTERID=8;
    public static final int VISTANB118FILTERID=9;
    public static final int VISTANB980985FILTERID=10;
    public static final int VISTANSUNBLINDFILTERID=11;
    

    
    public static final int [] ALLFILTERID= {VISTANONEFILTERID,VISTAZFILTERID,VISTAYFILTERID, VISTAJFILTERID, VISTAHFILTERID,
            VISTAKFILTERID,  VISTABLANKFILTERID, VISTANB118FILTERID,VISTANB980985FILTERID,VISTANSUNBLINDFILTERID};
    
 
    

    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 int [] UKIDSSTESTERPROGS={UKIDSSLASPROGRAMMEID,UKIDSSGPSPROGRAMMEID,UKIDSSGCSPROGRAMMEID,UKIDSSDXSPROGRAMMEID,UKIDSSUDSPROGRAMMEID,
            UKIDSSLASSVPROGRAMMEID,UKIDSSGPSSVPROGRAMMEID,UKIDSSGCSSVPROGRAMMEID,UKIDSSDXSSVPROGRAMMEID,UKIDSSUDSSVPROGRAMMEID,COMMISSIONINGPROGRAMMEID};
    public static final int [] UKIDSSPROGS={UKIDSSLASPROGRAMMEID,UKIDSSGPSPROGRAMMEID,UKIDSSGCSPROGRAMMEID,UKIDSSDXSPROGRAMMEID,UKIDSSUDSPROGRAMMEID};
    public static final int [] WORLDPROGS=UKIDSSPROGS;
    public static final int [] PRERELEASEPROGS=UKIDSSTESTERPROGS;
    
    public static final int [] VISTAPROGS={SVORIONPROGRAMMEID,SVNGC253PROGRAMMEID,VHSPROGRAMMEID,VVVPROGRAMMEID,VMCPROGRAMMEID,VIKINGPROGRAMMEID,VIDEOPROGRAMMEID,ULTRAVISTAPROGRAMMEID};
    
    public static final int [] VISTATESTERPROGS= new int[VISTAPROGS.length+1];
    
    static {
        VISTATESTERPROGS[0]=COMMISSIONINGPROGRAMMEID;
        for (int i=0; i<VISTAPROGS.length; i++) {
            VISTATESTERPROGS[i+1]=VISTAPROGS[i];
        }
    }
   // public static final int [] VISTATESTERPROGS={COMMISSIONINGPROGRAMMEID,VISTAPROGS};
    public static final String COMMISSIONINGDETECTIONTABLENAME = "commDetection";
    public static final String VHSDETECTIONTABLENAME = "vhsDetection";
    public static final String VVVDETECTIONTABLENAME = "vvvDetection";
    public static final String VMCDETECTIONTABLENAME = "vmcDetection";
    public static final String VIKINGDETECTIONTABLENAME = "vikingDetection";
    public static final String VIDEODETECTIONTABLENAME = "videoDetection";
    public static final String ULTRAVISTADETECTIONTABLENAME = "ultravistaDetection";
    public static final String SVORIONDETECTIONTABLENAME = "svOrionDetection";
    public static final String SVNGC253DETECTIONTABLENAME = "svNgc253Detection";
    
    public static final String COMMISSIONINGSOURCETABLENAME = "commSource";
    public static final String VHSSOURCETABLENAME = "vhsSource";
    public static final String VVVSOURCETABLENAME = "vvvSource";
    public static final String VMCSOURCETABLENAME = "vmcSource";
    public static final String VIKINGSOURCETABLENAME = "vikingSource";
    public static final String VIDEOSOURCETABLENAME = "videoSource";
    public static final String ULTRAVISTASOURCETABLENAME = "ultravistaSource";
    public static final String SVORIONSOURCETABLENAME = "svOrionSource";
    public static final String SVNGC253SOURCETABLENAME = "svNgc253Source";
   
 
    
    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 PTSSOURCETABLENAME = "NONE";
    public static final String CALSOURCETABLENAME = "NONE";
    
    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 UKIDSSPTSMERGETABLENAME = "NONE";
    
    public static final String VHSMERGETABLENAME = "vhsMergeLog";
    public static final String VVVMERGETABLENAME = "vvvMergeLog";
    public static final String VMCMERGETABLENAME = "vmcMergeLog";
    public static final String VIKINGMERGETABLENAME = "vikingMergeLog";
    public static final String VIDEOMERGETABLENAME = "videoMergeLog";
    public static final String ULTRAVISTAMERGETABLENAME = "'ultravistaMergeLog";
    public static final String SVORIONMERGETABLENAME = "svOrionMergeLog";
    public static final String SVORION253MERGETABLENAME = "'svNgc253MergeLog";
    
    
    
    
    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 [] VISTAFILTERS = {"NONE","Z","Y","J","H","Ks","H2","Br","BLNK","NB118","NB980","SUNBLIND"};
    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 [] VHSBands={"Y","J","H","K"};
    public static final int [] VHSREQFILTERS={0,0,1,1,1,1,0,0,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 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  VSASchema.COMMISSIONINGPROGRAMMEID:
		    return COMMISSIONINGREQFILTERS;
		case  VSASchema.UKIDSSLASPROGRAMMEID:
		    return LASREQFILTERS;
		case  VSASchema.UKIDSSLASSVPROGRAMMEID:
		    return LASREQFILTERS;
		case  VSASchema.UKIDSSGPSPROGRAMMEID:
		    if (getVersionNoOfRelease(database) >= getVersionNoOfRelease("ukidssdr3plus") || database.equals("") || database == null) {
		        return GPSREQFILTERS;
		    }
		    else {
		        return GPSREQFILTERSDR2;
		    }
		case  VSASchema.UKIDSSGPSSVPROGRAMMEID:
		    if (getVersionNoOfRelease(database) >= getVersionNoOfRelease("ukidssdr3plus")) {
		        return GPSREQFILTERS;
		    }
		    else {
		        return GPSREQFILTERSDR2;
		    }
		case  VSASchema.UKIDSSGCSPROGRAMMEID:
		    return GCSREQFILTERS;
		case  VSASchema.UKIDSSGCSSVPROGRAMMEID:
		    return GCSREQFILTERS;
		case  VSASchema.UKIDSSDXSPROGRAMMEID:
		    return DXSREQFILTERS;
		case  VSASchema.UKIDSSDXSSVPROGRAMMEID:
		    return DXSREQFILTERS;
		case  VSASchema.UKIDSSUDSPROGRAMMEID:
		    return UDSREQFILTERS;
		case  VSASchema.UKIDSSUDSSVPROGRAMMEID:
		    return UDSREQFILTERS;
		default :
		return null;
    } 
    }
    public static int [] getReqFilters(int surveyID){
        switch(surveyID){
		case  VSASchema.COMMISSIONINGPROGRAMMEID:
		    return COMMISSIONINGREQFILTERS;
		case  VSASchema.UKIDSSLASPROGRAMMEID:
		    return LASREQFILTERS;
		case  VSASchema.UKIDSSLASSVPROGRAMMEID:
		    return LASREQFILTERS;
		case  VSASchema.UKIDSSGPSPROGRAMMEID:
		    return GPSREQFILTERS;
		case  VSASchema.UKIDSSGPSSVPROGRAMMEID:
		    return GPSREQFILTERS;
		case  VSASchema.UKIDSSGCSPROGRAMMEID:
		    return GCSREQFILTERS;
		case  VSASchema.UKIDSSGCSSVPROGRAMMEID:
		    return GCSREQFILTERS;
		case  VSASchema.UKIDSSDXSPROGRAMMEID:
		    return DXSREQFILTERS;
		case  VSASchema.UKIDSSDXSSVPROGRAMMEID:
		    return DXSREQFILTERS;
		case  VSASchema.UKIDSSUDSPROGRAMMEID:
		    return UDSREQFILTERS;
		case  VSASchema.UKIDSSUDSSVPROGRAMMEID:
		    return UDSREQFILTERS;
		default :
		return null;
    } 
    }

   
    
   /*
    *   public static final int VHSPROGRAMMEID=110;
    public static final int VVVPROGRAMMEID=120;
    public static final int VMCPROGRAMMEID=120;
    public static final int VIKINGPROGRAMMEID=140;
    public static final int VIDEOPROGRAMMEID=150;
    public static final int ULTRAVISTAPROGRAMMEID=160;
    public static final int SVORIONPROGRAMMEID=100;
    public static final int SVNGC253PROGRAMMEID=101;
    */ 
    public static String [] getDBs(int programmeid,boolean world) {
        switch(programmeid){
        case ALLPROGRAMMEID:
		    if (world) {
		        return null;
		    }
		    else {
		        return PRERELEASEDB;
		    }
		case  VSASchema.COMMISSIONINGPROGRAMMEID:
		    if (world) {
		        return WORLDCOMMDB;
		    }
		    else {
		        return COMMDB;
		    }
		case  VSASchema.SVORIONPROGRAMMEID:
		    if (world) {
		        return WORLDSVORIONDB;
		    }
		    else {
		        return SVORIONDB;
		    }
		case  VSASchema.SVNGC253PROGRAMMEID:
		    if (world) {
		        return WORLDSVNGC253DB;
		    }
		    else {
		        return SVNGC253DB;
		    }
		case  VSASchema.VHSPROGRAMMEID:
		    if (world) {
		        return WORLDVHSDB;
		    }
		    else {
		        return VHSDB;
		    }
		case  VSASchema.VVVPROGRAMMEID:
		    if (world) {
		        return WORLDVVVDB;
		    }
		    else {
		        return VVVDB;
		    }
		case  VSASchema.VMCPROGRAMMEID:
		    if (world) {
		        return WORLDVMCDB;
		    }
		    else {
		        return VMCDB;
		    }
		case  VSASchema.VIKINGPROGRAMMEID:
		    if (world) {
		        return WORLDVIKINGDB;
		    }
		    else {
		        return VIKINGDB;
		    }
		case  VSASchema.VIDEOPROGRAMMEID:
		    if (world) {
		        return WORLDVIDEODB;
		    }
		    else {
		        return VIDEODB;
		    }
		case  VSASchema.ULTRAVISTAPROGRAMMEID:
		    if (world) {
		        return WORLDULTRAVISTADB;
		    }
		    else {
		        return ULTRAVISTADB;
		    }
		case  WSASchema.UKIDSSGCSPROGRAMMEID:
		    if (world) {
		        return WORLDGCSDB;
		    }
		    else {
		        return GCSDB;
		    }
		
		default :
		return null;
        }
  
    }
    public static String [] getDBsPlusRollingDB(String [] inDBs) {
        if (inDBs == null) {
            return null;
        }
        String [] retDBs=new String [inDBs.length+1];
        for (int i=0;i<inDBs.length;i++) {
            retDBs[i]=inDBs[i];
        }
        retDBs[retDBs.length-1]=VSASchema.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  VSASchema.COMMISSIONINGPROGRAMMEID:
		    return COMMISSIONINGBands;
		case  VSASchema.UKIDSSLASPROGRAMMEID:
		    return LASBands;
		case  VSASchema.UKIDSSLASSVPROGRAMMEID:
		    return LASBands;
		case  VSASchema.UKIDSSGPSPROGRAMMEID:
		    return GPSBands;
		case  VSASchema.UKIDSSGPSSVPROGRAMMEID:
		    return GPSBands;
		case  VSASchema.UKIDSSGCSPROGRAMMEID:
		    return GCSBands;
		case  VSASchema.UKIDSSGCSSVPROGRAMMEID:
		    return GCSBands;
		case  VSASchema.UKIDSSDXSPROGRAMMEID:
		    return DXSBands;
		case  VSASchema.UKIDSSDXSSVPROGRAMMEID:
		    return DXSBands;
		case  VSASchema.UKIDSSUDSPROGRAMMEID:
		    return UDSBands;
		case  VSASchema.UKIDSSUDSSVPROGRAMMEID:
		    return UDSBands;
		default :
		return null;
    }
    }
    
    
    
    public static String getMergeTableName(int surveyID){
        String mergeTableName;
        switch(surveyID){
		case  VSASchema.COMMISSIONINGPROGRAMMEID:
			mergeTableName=COMMISSIONINGMERGETABLENAME;
			break;
		case  VSASchema.VHSPROGRAMMEID:
	    mergeTableName=VHSMERGETABLENAME;
	    break;
		case  VSASchema.VVVPROGRAMMEID:
		    mergeTableName=VVVMERGETABLENAME;
		    break;
		case  VSASchema.VMCPROGRAMMEID:
		    mergeTableName=VMCMERGETABLENAME;
		    break;
		case  VSASchema.VIKINGPROGRAMMEID:
		    mergeTableName=VIKINGMERGETABLENAME;
		    break;
		case  VSASchema.VIDEOPROGRAMMEID:
		    mergeTableName=VIDEOMERGETABLENAME;
		    break;
		case  VSASchema.ULTRAVISTAPROGRAMMEID:
		    mergeTableName=ULTRAVISTAMERGETABLENAME;
		    break;
		case  VSASchema.SVORIONPROGRAMMEID:
		    mergeTableName=SVORIONMERGETABLENAME;
		    break;
		case  VSASchema.SVNGC253PROGRAMMEID:
		    mergeTableName=SVORION253MERGETABLENAME;
		    break;
		default :
		mergeTableName=null;  
        break;
    }
        return mergeTableName;
    }

    public static int getSourceSchemaVersion(int surveyID){
        int schemaVersion=-1;
        switch(surveyID){
		case  VSASchema.PTSPROGRAMMEID:
			schemaVersion=99999; // change when sourec available
			break;
		case  VSASchema.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  VSASchema.PTSPROGRAMMEID:
			schemaVersion=-1; // set high as not to be released
			break;
		case  VSASchema.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  VSASchema.COMMISSIONINGPROGRAMMEID:
			sourceTableName=COMMISSIONINGSOURCETABLENAME;
			break;
		case  VSASchema.UKIDSSLASPROGRAMMEID:
	    sourceTableName=UKIDSSLASSOURCETABLENAME;
	    break;
		case  VSASchema.UKIDSSGPSPROGRAMMEID:
		sourceTableName=UKIDSSGPSSOURCETABLENAME;
		break;
		case  VSASchema.UKIDSSGCSPROGRAMMEID:
			sourceTableName=UKIDSSGCSSOURCETABLENAME;
			break;
		case  VSASchema.UKIDSSDXSPROGRAMMEID:
			sourceTableName=UKIDSSDXSSOURCETABLENAME;
			break;
		case  VSASchema.UKIDSSUDSPROGRAMMEID:
			sourceTableName=UKIDSSUDSSOURCETABLENAME;
			break;
		case  VSASchema.UKIDSSLASSVPROGRAMMEID:
		    sourceTableName=UKIDSSLASSOURCETABLENAME;
		    break;
			case  VSASchema.UKIDSSGPSSVPROGRAMMEID:
			sourceTableName=UKIDSSGPSSOURCETABLENAME;
			break;
			case  VSASchema.UKIDSSGCSSVPROGRAMMEID:
				sourceTableName=UKIDSSGCSSOURCETABLENAME;
				break;
			case  VSASchema.UKIDSSDXSSVPROGRAMMEID:
				sourceTableName=UKIDSSDXSSOURCETABLENAME;
				break;
			case  VSASchema.UKIDSSUDSSVPROGRAMMEID:
				sourceTableName=UKIDSSUDSSOURCETABLENAME;
				break;
		default :
		sourceTableName=null;  
        break;
    }
        return sourceTableName;
    }
    
    
    public static String [] getDefaultRGB(int surveyID) {
        String [] RGB=null;
        switch(surveyID){
		case  VSASchema.UKIDSSLASPROGRAMMEID:
	    RGB = LASRGB;
	    break;
		case  VSASchema.UKIDSSGPSPROGRAMMEID:
		RGB = GPSRGB;
		break;
		case  VSASchema.UKIDSSGCSPROGRAMMEID:
		RGB = GCSRGB;
		break;
		case  VSASchema.UKIDSSDXSPROGRAMMEID:
		RGB = DXSRGB;
		break;
		case  VSASchema.UKIDSSUDSPROGRAMMEID:
		RGB = UDSRGB;
	    break;	
		default :  
        break;
        }
        return RGB;
    }
    public static String getSourceViewName(int surveyID){
        String sourceViewName;
        switch(surveyID){
		case  VSASchema.UKIDSSLASPROGRAMMEID:
	    sourceViewName=UKIDSSLASSOURCEVIEWNAME;
	    break;
		case  VSASchema.UKIDSSGPSPROGRAMMEID:
		sourceViewName=UKIDSSGPSSOURCEVIEWNAME;
		break;
		case  VSASchema.UKIDSSGCSPROGRAMMEID:
	    sourceViewName=UKIDSSGCSSOURCEVIEWNAME;
		break;
		case  VSASchema.UKIDSSDXSPROGRAMMEID:
		sourceViewName=UKIDSSDXSSOURCEVIEWNAME;
		break;
		case  VSASchema.UKIDSSUDSPROGRAMMEID:
			sourceViewName=UKIDSSUDSSOURCEVIEWNAME;
			break;
				
		default :
		sourceViewName=null;  
        break;
    }
        return sourceViewName;
    }
    public static String getDetectionTableName(int surveyID){
        String detectionTableName;
        switch(surveyID){
		case  VSASchema.COMMISSIONINGPROGRAMMEID:
			detectionTableName=COMMISSIONINGDETECTIONTABLENAME;
			break;
		case  VSASchema.UKIDSSLASPROGRAMMEID:
	    detectionTableName=UKIDSSLASDETECTIONTABLENAME;
	    break;
		case  VSASchema.UKIDSSGPSPROGRAMMEID:
		detectionTableName=UKIDSSGPSDETECTIONTABLENAME;
		break;
		case  VSASchema.UKIDSSGCSPROGRAMMEID:
			detectionTableName=UKIDSSGCSDETECTIONTABLENAME;
			break;
		case  VSASchema.UKIDSSDXSPROGRAMMEID:
			detectionTableName=UKIDSSDXSDETECTIONTABLENAME;
			break;
		case  VSASchema.UKIDSSUDSPROGRAMMEID:
			detectionTableName=UKIDSSUDSDETECTIONTABLENAME;
			break;
		case  VSASchema.PTSPROGRAMMEID:
			detectionTableName=PTSDETECTIONTABLENAME;
			break;
		case  VSASchema.CALPROGRAMMEID:
			detectionTableName=CALDETECTIONTABLENAME;
			break;
		case  VSASchema.UKIDSSLASSVPROGRAMMEID:
		    detectionTableName=UKIDSSLASDETECTIONTABLENAME;
		    break;
			case  VSASchema.UKIDSSGPSSVPROGRAMMEID:
			detectionTableName=UKIDSSGPSDETECTIONTABLENAME;
			break;
			case  VSASchema.UKIDSSGCSSVPROGRAMMEID:
				detectionTableName=UKIDSSGCSDETECTIONTABLENAME;
				break;
			case  VSASchema.UKIDSSDXSSVPROGRAMMEID:
				detectionTableName=UKIDSSDXSDETECTIONTABLENAME;
				break;
			case  VSASchema.UKIDSSUDSSVPROGRAMMEID:
				detectionTableName=UKIDSSUDSDETECTIONTABLENAME;
				break;	
				
			
			
		default :
		detectionTableName=null;  
        break;
    }
        return detectionTableName;
    }

    public static String getSurveyName(int surveyID){
        String surveyName;
        switch(surveyID){
    	case  VSASchema.COMMISSIONINGPROGRAMMEID:
			surveyName="Commissioning programme";
			break;
		case  VSASchema.VHSPROGRAMMEID:
			surveyName="VHS: VISTA Hemisphere Survey";
			break;
			
		case  VSASchema.VVVPROGRAMMEID:
		surveyName="VVV: VISTA Variables in the Via Lactea";
		break;
		case  VSASchema.VMCPROGRAMMEID:
	    surveyName="VMC: VISTA Magellanic Clouds Survey";
	    break;
		case  VSASchema.VIKINGPROGRAMMEID:
		surveyName="VIKING: VISTA Kilo-degree Infrared Galaxy Survey";
		break;
		case  VSASchema.VIDEOPROGRAMMEID:
			surveyName="VIDEO: VISTA Deep Extragalactic Observations";
			break;
		case  VSASchema.ULTRAVISTAPROGRAMMEID:
			surveyName="UltraVISTA: an ultra-deep survey with VISTA";
			break;
		case  VSASchema.SVORIONPROGRAMMEID:
			surveyName="ORION Science Verification data";
			break;
		case  VSASchema.SVNGC253PROGRAMMEID:
			surveyName="NGC253 Science Verification data";
			break;
		case  VSASchema.UKIDSSGCSPROGRAMMEID:
			surveyName="GCS TEST";
			break;
		default :
		surveyName="Survey name not found in lookup";  
        break;
    }
        return surveyName;
    }
    public static String getFilterName(int filterID){
        if (filterID >=0 && filterID <= VISTAFILTERS.length && VISTAFILTERS[filterID] != null ) {
            return VISTAFILTERS[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=VSASchema.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=VSASchema.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 VSASchema.getSourceViewName(intProgrammeID);
            }
            else if (baseTable.equalsIgnoreCase("source")) {
            return VSASchema.getSourceTableName(intProgrammeID);
        }
            else if (baseTable.equalsIgnoreCase("detection")) {
                return VSASchema.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 VSASchema.getDefaultList(reqFilters,tableName,WSASession.getSchemaVersion(session));
        }
        else
        {
            return VSASchema.getDefaultList(VSASchema.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=VSASchema.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=VSASchema.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;
        }
        
    }
   

}