import java.io.FileInputStream;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;

import javax.activation.FileTypeMap;
import javax.print.attribute.standard.Severity;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.Cookie;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.log4j.Logger;
import org.springframework.jms.connection.SessionProxy;

import uk.ac.roe.wfau.DatabaseConnection;
import uk.ac.roe.wfau.FormatLines;
import uk.ac.roe.wfau.GESSchema;
import uk.ac.roe.wfau.OSASchema;
import uk.ac.roe.wfau.SQLMethods;
import uk.ac.roe.wfau.VDFSJsp;
import uk.ac.roe.wfau.VDFSSchema;
import uk.ac.roe.wfau.VSASchema;
import uk.ac.roe.wfau.WSASchema;
import uk.ac.roe.wfau.stack2string;
import uk.ac.roe.wfau.Archive;
import javax.mail.*;

import javax.mail.internet.*;

/**
 * Login servlet to verfiy valid users of the WSA
 * Initially did this via a simple text file, but is now
 * verifying against SQL server. Eventually will likely verify
 * against astrogrid communities.
 * 
 * Once verified session attributes are set which can be picke up by other
 * servlets/jsps
 * 
 * @author MAR
 *
 * @version 1.1 woking version using text file
 * 
 */
public class DBLogin extends HttpServlet {
	String sessionPrefix="";
    /**
     * Log4j logger for record keeping
     */
    static Logger logger;
    /**
     * database connection
     */
    private Connection con=null;
    /**
     * database server
     */
    static String server;
    /**
     * database servers
     */
    static String[] servers;
    
    /** database VSA proprietary/nonsurvey servers
    */
   static String[] VSAPROPRIETARYSERVERS=null;
   static String[] OSAPROPRIETARYSERVERS=null;
   static String[] GESPROPRIETARYSERVERS=null;
    /**
     * database name
     */
    static String database;
    /**
     * database login
     */
    static String dbuser;
    /**
     * database login password
     */
    static String dbpasswd;
    /**
     * pre-release username
     */
    static String preReleaseUser;
    /**
     * pre-release passwd
     */
    static String preReleasePasswd;
    
    static String MASTERNSPASSWD;
    /**
    /**
     * 
     * Properties from file (used to load up parametsr and
     * hold simple text file of usernames/passwds
     */
    Properties prop;
    
    static HashMap userMap=new HashMap();
    
    /** 
     * Loads initialization parameters
     *
     * @see javax.servlet.GenericServlet#init()
     */
    
    String [] releasedDBs;
    
    
    //String archive=null;
    
    
    public void init() throws ServletException {
        // not needed anymore
        //super.init(conf);
        logger=Logger.getLogger("wsa.simple");
        logger.info("initializing DBLogin servlet");
        // read in global properties
        prop = new Properties();
        String path = getServletContext().getRealPath("/");
        try {
            prop.load(new FileInputStream(path
                    + "WEB-INF/WSA.properties"));
                    //+ "WEB-INF/DBLogin.properties"));
            
        } catch (IOException e) {
            logger.error("DBLogin,Can't find properties file ");
            return;
        }
        // assign properties to database connection
        server = prop.getProperty("loginServer");
        servers=FormatLines.commaSeparatedStringToStringArray(prop.getProperty("loginServers").trim());
        VSAPROPRIETARYSERVERS=FormatLines.commaSeparatedStringToStringArray(prop.getProperty("VSAPROPRIETARYSERVERS").trim());

        OSAPROPRIETARYSERVERS=FormatLines.commaSeparatedStringToStringArray(prop.getProperty("OSAPROPRIETARYSERVERS").trim());
        try {
        	GESPROPRIETARYSERVERS=FormatLines.commaSeparatedStringToStringArray(prop.getProperty("GESPROPRIETARYSERVERS").trim());
        }
        catch (Exception e) {
        	logger.info("GESPROPRIETARYSERVERS "+e);

        }
        dbuser = prop.getProperty("DBLoginUser");
        dbpasswd = prop.getProperty("DBLoginPasswd");
        database = prop.getProperty("DBLoginDatabase"); 
        preReleaseUser=prop.getProperty("preReleaseUser"); 
        preReleasePasswd=prop.getProperty("preReleasePasswd"); 
        
        MASTERNSPASSWD=prop.getProperty("MASTERNSPASSWD"); 
    }

    /**
     * Load SQL server driver
     */
    static {
        try {
           // Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            // A class not found error means the SQL driver class could not be
            // found.
            // Which means that this driver would not be available until this is
            // remedied. This flag variable will signal this to the class!
            logger.error("DBLogin, SQLRetrieverThread::static(): Could not load SQLServerDriver class! "
                            + stack2string.getString(e));
        }
    }


    /**
     * Main method that does the verification and sets session attributes
     * 
     * @see javax.servlet.http.HttpServlet#doGet(javax.servlet.http.HttpServletRequest,
     *      javax.servlet.http.HttpServletResponse)
     */
    public void doGet(HttpServletRequest req, HttpServletResponse res)
            throws ServletException, IOException {
        HttpSession session = req.getSession();
        res.setContentType("text/html");
        PrintWriter out = res.getWriter();
        String emailAddress=null;
        String archive="WSA";
        int archiveID=VDFSSchema.WSAARCHIVEID;
        if (req.getParameter("archive")!= null) {
            archive=req.getParameter("archive");
            archiveID=VDFSSchema.getArchiveID(archive);
            
        }
        boolean gesTOC=false;
        
        if (archiveID==VDFSSchema.GESARCHIVEID && req.getParameter("toc") != null) {
        logger.info(req.getParameter("user")+"::"+req.getParameter("community")+"::TOC="+req.getParameter("toc")+"::"+req.getRemoteAddr());
        if (req.getParameter("toc").equalsIgnoreCase("accept")) {
        	gesTOC=true;
        //Cookie userCookie = new Cookie("gesTOC", "Accept");
        //userCookie.setMaxAge(63072000);
        //res.addCookie(userCookie);
        }
        }
        
        
        // insert html page into output
        RequestDispatcher dispatcher = req.getRequestDispatcher("top.html");
        
        
        dispatcher.include(req, res);
        // add some spacing
        out.println("<h3>&nbsp;</h3>");
        
        if (archiveID==VDFSSchema.GESARCHIVEID && req.getParameter("toc") == null) {
          	String cookieName = "gesTOC";
        	Cookie cookies [] = req.getCookies ();
        	Cookie myCookie = null;
        	if (cookies != null)
        	{
        	for (int i = 0; i < cookies.length; i++) 
        	{
        	if (cookies[i].getName().equals(cookieName))
        	{
        	if (cookies[i].getValue().equalsIgnoreCase("accept")) {
        		gesTOC=true;
        	}
        	break;
        	}
        	}
        	}
        }
        	if (archiveID==VDFSSchema.GESARCHIVEID && !gesTOC){
        	out.print("Please go back and accept the publication and data policies");	

              dispatcher = req.getRequestDispatcher("bottom_end.html");
              dispatcher.include(req, res);
              System.gc();
              return;
        	}
        	
        // read in user supplied parameters
     
        sessionPrefix=archive.trim().toLowerCase();
        if (req.getParameter("logout") != null) {
        	Enumeration e= session.getAttributeNames();
        	while (e.hasMoreElements()) {
        		String name = (String) e.nextElement();
        	    logger.info(name + ": " + session.getAttribute(name));
        	}
        	session.invalidate();
        	out.println("<b>Logged out</b>");
            dispatcher = req.getRequestDispatcher("bottom_end.html");
            dispatcher.include(req, res);
            dispatcher=null;
            return;
        }
        if (req.getParameter("emailAddress") == null) {
            
        
        
        String user = null;
        String passwd = null;
        String community = null;
        String community2 = "";
        if (req.getParameter("community2") != null) {
            community2 = req.getParameter("community2").trim();
            if (community2.indexOf("=") > 0 || community2.indexOf(" ") >0 ){
                community2="";
            }
        }
        try {
       
        user = req.getParameter("user").trim();
        
        passwd = req.getParameter("passwd").trim();
        
        
        if (req.getParameter("community") != null) {
        community = req.getParameter("community").trim();
        if (community.indexOf("=") > 0 || community.indexOf(" ") >0 ){
            community="";
        }
        }
        else {
            community="";
        }
        
        
        if (!community2.equals("") && community2.length() > 0 ) {
            community=community2;
        }
        }
        catch (NullPointerException npe) {
            out.println("<p><b>Error null value(s) supplied for user and/or password and/or community");
            npe.printStackTrace();
            
            return;
        }
 
 
        // pass parameters to verfiy method, put in try/catch in
        // case of null parameters
        logger.info("DBLogin,"+server+" "+user+","+"******"+","+community+","+req.getRemoteAddr());
        try {
            boolean valid=false;
            if (!community.equalsIgnoreCase("preRelease") ) {
                valid=VerfiyUserSQL(user, passwd, community,session,archiveID);
            }
            else {
                if (user.equalsIgnoreCase(preReleaseUser) && passwd.equalsIgnoreCase(preReleasePasswd)) {
                    valid=true;
                    session.setAttribute(sessionPrefix+"programmeID",null);
                    session.setAttribute(sessionPrefix+"dbName",null);
                    session.setAttribute(sessionPrefix+"releasedDBs",null);
                    session.setAttribute(sessionPrefix+"reqFilters",null);
                    switch (archiveID) {
					case VDFSSchema.GESARCHIVEID:
						session.setAttribute(GESSchema.SESSIONPROPRIETARYCOMMUNITY, Boolean.FALSE);
						break;
					case VDFSSchema.OSAARCHIVEID:
						session.setAttribute(OSASchema.SESSIONPROPRIETARYCOMMUNITY, Boolean.FALSE);
						break;
					case VDFSSchema.VSAARCHIVEID:
						session.setAttribute(VSASchema.SESSIONPROPRIETARYCOMMUNITY, Boolean.FALSE);
						break;

					default:
						break;
					}
                    
                }
            }
            session.setAttribute("archive",archive);
            
            if (valid) {
                //VerfiyUserSQL(user, passwd, community,session)
                out.println("Logged in as <p> Username: " + user
                        + " <p>Community: " + community + "<p>");
                // set session parameters 
                if (archive.equalsIgnoreCase("vsa")) {
                    session.setAttribute("vsalogin", Boolean.TRUE); 
                    //if (!community.equalsIgnoreCase(VSASchema.PROPRIETARYCOMMUNITY)) {
                    if (!VSASchema.isProprietary(community)) {
                    session.setAttribute("vsauser", user.toLowerCase().trim());
                    }
                    else {
                        session.setAttribute("vsauser", user.trim());
                    }
                    session.setAttribute("vsacommunity", community.toUpperCase());
                }
                else if (archive.equalsIgnoreCase("ges")) {
                	logger.info("ges");
                   
                	session.setAttribute("geslogin", Boolean.TRUE); 
                	logger.info("ges:: "+session.getAttribute("geslogin"));
                    if (community.equalsIgnoreCase("prerelease") || !GESSchema.isProprietary(GESSchema.GESCOMMUNITYPREFIX+community)) {
                    session.setAttribute("gesuser", user.toLowerCase().trim());
                    session.setAttribute("gescommunity",community.toUpperCase());
                    }
                    else {
                        session.setAttribute("gesuser", user.trim());
                        session.setAttribute("gescommunity", GESSchema.GESCOMMUNITYPREFIX+community.toUpperCase());
                    }
                  
                    logger.info("gesc");
                }
                else if (archive.equalsIgnoreCase("osa")) {
                	logger.info("osa");
                	session.setAttribute("osalogin", Boolean.TRUE); 
                	logger.info("osa:: "+session.getAttribute("osalogin"));
                    if (community.equals("prerelease") || !OSASchema.isProprietary(OSASchema.OSACOMMUNITYPREFIX+community)) {
                    session.setAttribute("osauser", user.toLowerCase().trim());
                    session.setAttribute("osacommunity", community.toUpperCase());
                    }
                    else {
                        session.setAttribute("osauser", user.trim());
                        session.setAttribute("osacommunity", OSASchema.OSACOMMUNITYPREFIX+community.toUpperCase());
                    }
                   
                    logger.info("osac");
                }
                else {
                session.setAttribute("login", Boolean.TRUE);
                session.setAttribute("user", user.toLowerCase().trim());
                session.setAttribute("community", community.toLowerCase());
                }
  
             
                if (community.equalsIgnoreCase("nonsurvey")){
                    session.setAttribute("nonsurvey", Boolean.TRUE);
                    
                }
                //else if (community.equalsIgnoreCase(VSASchema.PROPRIETARYCOMMUNITY)){
                else if (archive.equalsIgnoreCase("vsa") && VSASchema.isProprietary(community)){
                    session.setAttribute(VSASchema.SESSIONPROPRIETARYCOMMUNITY, Boolean.TRUE);
                    
                }
                else if (archive.equalsIgnoreCase("ges") && !community.equalsIgnoreCase("prerelease") && GESSchema.isProprietary(GESSchema.GESCOMMUNITYPREFIX+community)){
                    session.setAttribute(GESSchema.SESSIONPROPRIETARYCOMMUNITY, Boolean.TRUE);
                    
                }
                else if (archive.equalsIgnoreCase("osa") && !community.equalsIgnoreCase("prerelease") && OSASchema.isProprietary(OSASchema.OSACOMMUNITYPREFIX+community)){
                    session.setAttribute(OSASchema.SESSIONPROPRIETARYCOMMUNITY, Boolean.TRUE);
                    
                }
                else {
                    session.setAttribute("nonsurvey", Boolean.FALSE);
                    if (archive.equalsIgnoreCase("vsa")) {
                    session.setAttribute(VSASchema.SESSIONPROPRIETARYCOMMUNITY, Boolean.FALSE);
                    }
                    session.setAttribute(sessionPrefix+"programmeID","-999");
                    session.setAttribute(sessionPrefix+"source", Boolean.TRUE);
                    
                }
                
            } else {
                if (archive.equalsIgnoreCase("vsa")) {
                    session.setAttribute("vsalogin", Boolean.FALSE);
                    session.setAttribute("vsauser", null);
                    session.setAttribute("vsacommunity", null);
                    session.setAttribute(sessionPrefix+"programmeID",null);
                    session.setAttribute(VSASchema.SESSIONPROPRIETARYCOMMUNITY,null);
                }
                else if (archive.equalsIgnoreCase("ges")) {
                    session.setAttribute("geslogin", Boolean.FALSE);
                    session.setAttribute("gesuser", null);
                    session.setAttribute("gescommunity", null);
                    session.setAttribute(sessionPrefix+"programmeID",null);
                    session.setAttribute(GESSchema.SESSIONPROPRIETARYCOMMUNITY,null);
                }
                else if (archive.equalsIgnoreCase("osa")) {
                    session.setAttribute("osalogin", Boolean.FALSE);
                    session.setAttribute("osauser", null);
                    session.setAttribute("osacommunity", null);
                    session.setAttribute(sessionPrefix+"programmeID",null);
                    session.setAttribute(OSASchema.SESSIONPROPRIETARYCOMMUNITY,null);
                }
                else {            
                session.setAttribute("login", Boolean.FALSE);                
                session.setAttribute("user", null);
                session.setAttribute("community", null);
                }
                session.setAttribute(sessionPrefix+"source", Boolean.FALSE);
                out.println("FAILED to log in.<p> Please go BACK and check your have entered your correct username, password <b>AND</b> community.<p>");
            }
            session.setMaxInactiveInterval(14400);
        } catch (Exception e) {
            e.printStackTrace();
            out.println("Problem verfiying <p>"+e+"<p> please try again.<br>"
                            + "If the problem persists send an email to "+VDFSSchema.getSupportEmail(archiveID));
        }
        finally {
            try {
                if (con != null)
                    con.close();
            } catch (SQLException ignore) {
                // do nothing
            }
        // you never know it might do something
 

        }
        } // end of non-email bit
        else {
            try {
                String category=req.getParameter("category");
                emailAddress=req.getParameter("emailAddress");
                out.print(category+" "+emailAddress+" "+archive);
                String sqlStmt=SQLMethods.getLoginDetailsSQL(category,emailAddress,archive);
            out.print("<p>Checking "+category+" category for email address: "+emailAddress);
            con=getCon(database,dbuser,dbpasswd);
            
            out.print("<p>"+sqlStmt);
            Statement stmt=con.createStatement();
            ResultSet rs=stmt.executeQuery(sqlStmt);
            StringBuffer emailMessage=new StringBuffer("The "+Archive.getArchive(archive)+" "+category+
                    " login details matching "+emailAddress+" are:\n\n");
            int rows=0;
            while (rs.next()) {
                rows++;
                emailMessage.append("\n username:  "+rs.getString("username"));
                emailMessage.append("\n password:  "+rs.getString("password"));
                emailMessage.append("\n community: "+rs.getString("community"));
            }
            out.print("<p>"+emailMessage);
            if (rows == 0) {
                out.print("<p>No records found for "+emailAddress);
                out.print("<p>This might be because you are not registered or the registration is incorrect, please contact "+
                        Archive.getArchiveEmail(archive));
            }
            else {
                //String sender,String recipient,String subject,String contents,String cc, String bcc)
                Archive.sendEmail(Archive.getArchiveEmail(archive),"mar@roe.ac.uk","login details",emailMessage.toString(),null,null);
            }
            }
         catch (Exception e) {
            out.println("Problem verfiying email<p>"+e+"<p> please try again.<br>"
                            + "If the problem persists send an email to "+Archive.getArchiveEmail(archive));
        }
        finally {
            try {
                if (con != null)
                    con.close();
            } catch (SQLException ignore) {
                // do nothing
            }
        }
        
        }
        System.gc();

        dispatcher = req.getRequestDispatcher("bottom_end.html");
        dispatcher.include(req, res);
    }
  
    /**
     * Clean up
     * 
     * @see javax.servlet.GenericServlet#destroy()
     */
    public void destroy() {
        try {
            if (con != null)
                con.close();
            con=null;
        } catch (SQLException ignore) {
            // do nothing
        }
        userMap=null;
    }

  protected void doPost(HttpServletRequest req, HttpServletResponse res)
    throws ServletException, IOException
  {
    // Data received by an HTTP POST is handled the same way
    // as data sent with a HTTP GET request... simply pass on to above...
    doGet(req, res);
  }; // END of doPost()
  
  /**
   * Simple user verication against user/passwds/communities held in
   * the  properties file
   * 
   * @param user
   * username
   * @param passwd
   * password
   * @param community
   * community
   * @return
   * true is user has been verified
   * @throws Exception
   */
  private synchronized boolean VerfiyUserSimple(String user,String passwd,String community) throws Exception{
      if (user != null && passwd != null && community != null){ 
          // read in user from properties
          if (prop.getProperty(user)!=null){
              String passCommunity=prop.getProperty(user);
              String passwdBit=
                  passCommunity.substring(0,passCommunity.lastIndexOf(":"));
              String communityBit=
                  passCommunity.substring(1+passCommunity.lastIndexOf(":"));
              System.out.println(passwdBit);
              System.out.println(communityBit);
              if (passwdBit.equals(passwd) && communityBit.equals(community)){
                  return true;
              }
              else {
                  return false;
              }
          }
          if(prop.getProperty(user) == null){
              return false;
          }
      }   
      return false;
  }
  
  
  private Connection getCon(String database,String dbuser,String dbpasswd) throws SQLException {
      //logger.info("DBLOGIN "+server+" "+database+" "+dbuser+" "+dbpasswd);
      //logger.info("DBLOGIN "+servers+" "+database+" "+dbuser+" "+dbpasswd);
      return DatabaseConnection.getConnection(servers,database,dbuser,dbpasswd,servers[0]);
      /*
      return DriverManager.getConnection("jdbc:jtds:sqlserver://"
      + server + ":1433/" + database,dbuser, dbpasswd);
      */
   
  }
  /**
   * Placeholder method to do verification against SQL server
   * 
   * @param user
   * username
   * @param passwd
   * password
   * @param community
   * community
   * @param session
   * session object
   * @return
   * true is user has been verified
   * @throws Exception
   */
public synchronized boolean VerfiyUserSQL(String user,String passwd,String community,HttpSession session,int archiveID) throws Exception{
      boolean valid=false;
      
      if (user != null && passwd != null && community != null){ 
          logger.debug(database+" "+dbuser+" "+dbpasswd);
      if (con == null || con.isClosed()){
          //logger.info("null");
          try {
              con=getCon(database,dbuser, dbpasswd);
              /*
              con = DriverManager.getConnection("jdbc:jtds:sqlserver://"
                      + server + ":1433/" + database,dbuser, dbpasswd);
                      */
              
                     // + server + ":1433/" + database+";wsid=111",dbuser, dbpasswd);
              
          /*con=DriverManager.getConnection("jdbc:microsoft:sqlserver://"+
                  server+":1433;DatabaseName="+database, dbuser, dbpasswd);
                  */
          }
          catch(Exception e){
              logger.error("DBLogin, "+e);
              throw(e);             
          }
      }
      ResultSet rs=null;
      Statement stmt=null;
      String SQLQuery=null;
      PreparedStatement pstmt=null;
      try {
      stmt = con.createStatement();
      
      stmt.setQueryTimeout(90);
      logger.info("DBLOGIN archiveID "+archiveID);
      if (archiveID==VDFSSchema.VSAARCHIVEID) {
          //if (!community.trim().equalsIgnoreCase(VSASchema.PROPRIETARYCOMMUNITY)){
          if (!VSASchema.isProprietary(community.trim())){
          SQLQuery="select * from vsa_usersView where username='"+
          user+"' and password='"+passwd+"'and community='"+community+"'";
          
          pstmt=con.prepareStatement("select * from vsa_usersView where username=? and password=? and community=?");
          pstmt.setString(1, user);
          pstmt.setString(2, passwd);
          pstmt.setString(3, community);

          }
          else {
              if (!passwd.equalsIgnoreCase(MASTERNSPASSWD)) {
                  pstmt=con.prepareStatement("select sourcetable,programmeid,numFilterID1,numFilterID2,numFilterID3,numFilterID4,numFilterID5,numFilterID6,numFilterID7,numFilterID8,schemaVersion,databasename " +
                  		"from wsa_nonSurveyUsers where username=? and password=? and community=? ");
                  pstmt.setString(1, user);
                  pstmt.setString(2, passwd);
                  pstmt.setString(3, community);
              }
              else {
                  pstmt=con.prepareStatement("select sourcetable,programmeid,numFilterID1,numFilterID2,numFilterID3,numFilterID4,numFilterID5,numFilterID6,numFilterID7,numFilterID8,schemaVersion,databasename " +
            		"from wsa_nonSurveyUsers where username=? and community=? ");
            pstmt.setString(1, user);
            pstmt.setString(2, community);
              }
              SQLQuery="select sourcetable,programmeid,numFilterID1,numFilterID2,numFilterID3,numFilterID4,numFilterID5," +
        		"numFilterID6,numFilterID7,numFilterID8,schemaVersion,databasename from wsa_nonSurveyUsers where username='"+
        		user+"' and community='"+community+"'";
              if (!passwd.equalsIgnoreCase(MASTERNSPASSWD)) {
                  SQLQuery=SQLQuery+" and password='"+passwd+"'";
              }
        //user+"' and password='"+passwd+"'and community='"+community+"'";   
          }
      } // end VSA archive
      
      else if (archiveID==VDFSSchema.OSAARCHIVEID) {
          //if (!OSASchema.isProprietary(community.trim())){
    	  if (!OSASchema.isProprietary(OSASchema.OSACOMMUNITYPREFIX+community.trim())){
          SQLQuery="select * from osa_usersView where username='"+
          user+"' and password='"+passwd+"'and community='"+community+"'";
          
          pstmt=con.prepareStatement("select * from osa_usersView where username=? and password=? and community=? and archiveid="+VDFSSchema.OSAARCHIVEID);
          pstmt.setString(1, user);
          pstmt.setString(2, passwd);
          pstmt.setString(3, OSASchema.OSACOMMUNITYPREFIX+community);

          }
          else {
              if (!passwd.equalsIgnoreCase(MASTERNSPASSWD)) {
                  pstmt=con.prepareStatement("select sourcetable,programmeid,numFilterID1,numFilterID2,numFilterID3,numFilterID4,numFilterID5,numFilterID6,numFilterID7,numFilterID8,schemaVersion,databasename " +
                  		"from wsa_nonSurveyUsers where username=? and password=? and community=? and archiveid="+VDFSSchema.OSAARCHIVEID);
                  pstmt.setString(1, user);
                  pstmt.setString(2, passwd);
                  pstmt.setString(3, OSASchema.OSACOMMUNITYPREFIX+community);
              }
              else {
                  pstmt=con.prepareStatement("select sourcetable,programmeid,numFilterID1,numFilterID2,numFilterID3,numFilterID4,numFilterID5,numFilterID6,numFilterID7,numFilterID8,schemaVersion,databasename " +
            		"from wsa_nonSurveyUsers where username=? and community=? and archiveid="+VDFSSchema.OSAARCHIVEID);
            pstmt.setString(1, user);
            pstmt.setString(2, OSASchema.OSACOMMUNITYPREFIX+community);
              }
              SQLQuery="select sourcetable,programmeid,numFilterID1,numFilterID2,numFilterID3,numFilterID4,numFilterID5," +
        		"numFilterID6,numFilterID7,numFilterID8,schemaVersion,databasename from wsa_nonSurveyUsers where username='"+
        		user+"' and community='"+community+"'";
              if (!passwd.equalsIgnoreCase(MASTERNSPASSWD)) {
                  SQLQuery=SQLQuery+" and password='"+passwd+"'";
              }
        //user+"' and password='"+passwd+"'and community='"+community+"'";   
          }
      } // end OSA archive
      else if (archiveID==VDFSSchema.GESARCHIVEID) {
    	  if (!GESSchema.isProprietary(GESSchema.GESCOMMUNITYPREFIX+community.trim())){
              SQLQuery="select * from ges_usersView where username='"+
              user+"' and password='"+passwd+"'and community='"+community+"'";
              
              pstmt=con.prepareStatement("select * from ges_usersView where username=? and password=? and community=? and archiveid="+VDFSSchema.GESARCHIVEID);
              pstmt.setString(1, user);
              pstmt.setString(2, passwd);
              pstmt.setString(3, GESSchema.GESCOMMUNITYPREFIX+community);

              }
              else {
                  if (!passwd.equalsIgnoreCase(MASTERNSPASSWD)) {
                      pstmt=con.prepareStatement("select sourcetable,programmeid,numFilterID1,numFilterID2,numFilterID3,numFilterID4,numFilterID5,numFilterID6,numFilterID7,numFilterID8,schemaVersion,databasename " +
                      		"from wsa_nonSurveyUsers where username=? and password=? and community=? and archiveid="+VDFSSchema.GESARCHIVEID);
                      pstmt.setString(1, user);
                      pstmt.setString(2, passwd);
                      pstmt.setString(3, GESSchema.GESCOMMUNITYPREFIX+community);
                  }
                  else {
                      pstmt=con.prepareStatement("select sourcetable,programmeid,numFilterID1,numFilterID2,numFilterID3,numFilterID4,numFilterID5,numFilterID6,numFilterID7,numFilterID8,schemaVersion,databasename " +
                		"from wsa_nonSurveyUsers where username=? and community=? and archiveid="+VDFSSchema.GESARCHIVEID);
                pstmt.setString(1, user);
                pstmt.setString(2, GESSchema.GESCOMMUNITYPREFIX+community);
                  }
                  SQLQuery="select sourcetable,programmeid,numFilterID1,numFilterID2,numFilterID3,numFilterID4,numFilterID5," +
            		"numFilterID6,numFilterID7,numFilterID8,schemaVersion,databasename from wsa_nonSurveyUsers where username='"+
            		user+"' and community='"+community+"'";
                  if (!passwd.equalsIgnoreCase(MASTERNSPASSWD)) {
                      SQLQuery=SQLQuery+" and password='"+passwd+"'";
                  }
            //user+"' and password='"+passwd+"'and community='"+community+"'";   
              }
      } // GES archive
      
      
      
      else {
      if (!community.trim().equalsIgnoreCase("nonSurvey")){
          SQLQuery="select w.*,u.userID from wsa_users as w left outer join userIDs as U on w.username=u.username and w.community=u.community where w.username='"+
          user+"' and w.password='"+passwd+"'and w.community='"+community+"'";
          pstmt=con.prepareStatement("select w.*,u.userID from wsa_users as w left outer join userIDs as U on w.username=u.username and w.community=u.community  " +
          		"where w.username=? and w.password=? and w.community=?");
          pstmt.setString(1, user);
          pstmt.setString(2, passwd);
          pstmt.setString(3, community);
          
      }
      else {
          if (!passwd.equalsIgnoreCase(MASTERNSPASSWD)) {
              pstmt=con.prepareStatement("select sourcetable,programmeid,numFilterID1,numFilterID2,numFilterID3,numFilterID4,numFilterID5,numFilterID6,numFilterID7,numFilterID8,schemaVersion,databasename " +
              		"from wsa_nonSurveyUsers where username=? and password=? and community=? ");
              pstmt.setString(1, user);
              pstmt.setString(2, passwd);
              pstmt.setString(3, community);
          }
          else {
              pstmt=con.prepareStatement("select sourcetable,programmeid,numFilterID1,numFilterID2,numFilterID3,numFilterID4,numFilterID5,numFilterID6,numFilterID7,numFilterID8,schemaVersion,databasename " +
        		"from wsa_nonSurveyUsers where username=? and community=? ");
        pstmt.setString(1, user);
        pstmt.setString(2, community);
          }
          
          
          
          SQLQuery="select sourcetable,programmeid,numFilterID1,numFilterID2,numFilterID3,numFilterID4,numFilterID5," +
          		"numFilterID6,numFilterID7,numFilterID8,schemaVersion,databasename from wsa_nonSurveyUsers where username='"+
          user+"' and community='"+community+"'";
          if (!passwd.equalsIgnoreCase(MASTERNSPASSWD)) {
              SQLQuery=SQLQuery+" and password='"+passwd+"'";
          }
          
      }
      }
      logger.info("DBLOGIN stmt "+pstmt.toString());
      //rs = stmt.executeQuery(SQLQuery);
      rs=pstmt.executeQuery();
      
      logger.info("DBLOGIN pstmt");
      }
      catch (SQLException se){
          logger.error("DBLOGIN "+se);
      ;
      }
      String sourceTable=null;
      String progID=null;
      if (rs.next() ){            
            valid=true;
            
            if (session != null) {
               
            //if (community.trim().equalsIgnoreCase("nonSurvey") || community.trim().equalsIgnoreCase(VSASchema.PROPRIETARYCOMMUNITY)){
             if (community.trim().equalsIgnoreCase("nonSurvey") || VSASchema.isProprietary(community.trim()) || OSASchema.isProprietary(OSASchema.OSACOMMUNITYPREFIX+community.trim()) || GESSchema.isProprietary(GESSchema.GESCOMMUNITYPREFIX+community.trim())){
                session.removeAttribute(sessionPrefix+"releasedDBs");
                session.removeAttribute(sessionPrefix+"dbName");
                sourceTable=rs.getString(1);
                progID=rs.getString(2);
                String schemaVersion=rs.getString(11);
                String dbName=rs.getString("databasename");
                if (sourceTable == null) {
                    sourceTable="n";
                }
                if (progID == null) {
                    progID="-999";
                }
                // place holder
                int [] iArray={rs.getShort(3),rs.getShort(4),rs.getShort(5),rs.getShort(6),rs.getShort(7),rs.getShort(8),rs.getShort(9),rs.getShort(10)};
                session.setAttribute(sessionPrefix+"reqFilters",iArray);
                session.setAttribute(sessionPrefix+"programmeID",progID);
                logger.info("DBLOGIN progID "+progID);
                session.setAttribute(sessionPrefix+"schemaVersion",schemaVersion);
                session.setAttribute(sessionPrefix+"dbName",dbName);
                if (sourceTable.equalsIgnoreCase("y")){
                    session.setAttribute(sessionPrefix+"source", Boolean.TRUE);
                }
                else {
                    session.setAttribute(sessionPrefix+"source", Boolean.FALSE);
                }               
            }
            else {
                session.setAttribute(sessionPrefix+"reqFilters",null);
                session.setAttribute(sessionPrefix+"programmeID",null);
                session.setAttribute(sessionPrefix+"schemaVersion",null);
                session.setAttribute(sessionPrefix+"source", Boolean.TRUE);
            }
            try {
            if (rs.getString("userID") != null) {
                session.setAttribute("userID",rs.getString("userID"));
                logger.info("DBLOGIN: userID "+rs.getString("userID"));
            }
            }
            catch (SQLException se) {
             
            }
                
            
            } // end of if session != null
            else {
                userMap.put(user+passwd+community,new Long(System.currentTimeMillis()));
                List keys=new ArrayList(userMap.keySet());
                Iterator it = keys.iterator();
                long timeDiff=0;
                Object key;
                while (it.hasNext() ) {
                    key=it.next();
                    timeDiff=System.currentTimeMillis()-((Long)userMap.get(key)).longValue();
                    logger.debug("tdiff:"+timeDiff);
                    if (timeDiff > 5000) {
                        logger.debug("removing"+userMap.get(key)+(String)key);
                        userMap.remove(key);
                        
                    }
                    }
                }
      }
      int iProgID=0;
     // if (valid==true && (community.equalsIgnoreCase("nonSurvey") || community.equalsIgnoreCase(VSASchema.PROPRIETARYCOMMUNITY))){
       if (valid==true && (community.equalsIgnoreCase("nonSurvey") || VSASchema.isProprietary(community) || OSASchema.isProprietary(OSASchema.OSACOMMUNITYPREFIX+community) || GESSchema.isProprietary(GESSchema.GESCOMMUNITYPREFIX+community) ) ){
         //if (community.equalsIgnoreCase(VSASchema.PROPRIETARYCOMMUNITY)) {
           if (VSASchema.isProprietary(community) && archiveID==VDFSSchema.VSAARCHIVEID) {
              logger.info("DBLogin switching");
              try {
                  rs.close();
                  stmt.close();
                  con.close();
                  con=null;
                  stmt=null;
                  }
                  catch (Exception e){
                  logger.error("DBLogin switching,"+e);
                  throw(e);
                  }
                  con=DatabaseConnection.getConnection(VSAPROPRIETARYSERVERS,"master",dbuser,dbpasswd,VSAPROPRIETARYSERVERS[0]);
                  stmt=con.createStatement();
                  if (progID !=null) {
                  iProgID=Integer.parseInt(progID);
                  if (iProgID > 0 && iProgID < 1000) {
                      user=VSASchema.getShortSurveyName(iProgID);
                  }
                  }
          }
           if ( archiveID==VDFSSchema.OSAARCHIVEID && OSASchema.isProprietary(OSASchema.OSACOMMUNITYPREFIX+community) ) {
               logger.info("DBLogin switching");
               try {
                   rs.close();
                   stmt.close();
                   con.close();
                   con=null;
                   stmt=null;
                   }
                   catch (Exception e){
                   logger.error("DBLogin switching,"+e);
                   throw(e);
                   }
                   con=DatabaseConnection.getConnection(OSAPROPRIETARYSERVERS,"master",dbuser,dbpasswd,OSAPROPRIETARYSERVERS[0]);
                   stmt=con.createStatement();
                   if (progID !=null) {
                   iProgID=Integer.parseInt(progID);
                   if (iProgID > 0 && iProgID < 1000) {
                       user=OSASchema.getShortSurveyName(iProgID);
                   }
                   }
           }
           
           if (archiveID==VDFSSchema.GESARCHIVEID && GESSchema.isProprietary(GESSchema.GESCOMMUNITYPREFIX+community) ) {
               logger.info("DBLogin switching");

               try {
                   rs.close();
                   stmt.close();
                   con.close();
                   con=null;
                   stmt=null;
                   }
                   catch (Exception e){
                   logger.error("DBLogin switching,"+e);
                   throw(e);
                   }
                   con=DatabaseConnection.getConnection(GESPROPRIETARYSERVERS,"master",dbuser,dbpasswd,GESPROPRIETARYSERVERS[0]);
                   stmt=con.createStatement();
                   if (progID !=null) {
                   iProgID=Integer.parseInt(progID);
                   if (iProgID > 0 && iProgID < 1000) {
                       user=GESSchema.getShortSurveyName(iProgID);
                   }
                   }
           }
           
          if (archiveID==VDFSSchema.GESARCHIVEID) {
        	  logger.info("GESCOMM: "+community+" "+community.toLowerCase().indexOf("beta"));
        	  rs = stmt.executeQuery(SQLMethods.getNSDBs(user,archiveID,community));  
          }
          else {
          rs = stmt.executeQuery(SQLMethods.getNSDBs(user,archiveID));
          }
          ArrayList listDB = new ArrayList();
          while (rs.next()){
              listDB.add(rs.getString(1));
          }
          if (archiveID==VDFSSchema.VSAARCHIVEID && VSASchema.getWorldDBs(iProgID)!=null) {
              for (int d=0; d<VSASchema.getWorldDBs(iProgID).length;d++){
            	  if (!listDB.contains(VSASchema.getWorldDBs(iProgID)[d])) {
                  listDB.add(VSASchema.getWorldDBs(iProgID)[d]); 
            	  }
              }
          }
          if (!listDB.isEmpty()) {
              
          releasedDBs=((String[]) listDB.toArray(new String[listDB.size()]));
          if (archiveID==VDFSSchema.OSAARCHIVEID) {
          session.setAttribute(sessionPrefix+"releasedDBs",OSASchema.getDBs(iProgID, false));  
          } 
          else if (archiveID==VDFSSchema.VSAARCHIVEID) 
          {
        	  if (iProgID==VSASchema.VVVPROGRAMMEID || iProgID==VSASchema.VIDEOPROGRAMMEID || iProgID==VSASchema.VHSPROGRAMMEID) {
        		  session.setAttribute(sessionPrefix+"releasedDBs",VSASchema.getDBs(iProgID, false)); 
        	  }
        	  else {
        		  session.setAttribute(sessionPrefix+"releasedDBs",releasedDBs); 
        	  }
     		}
          else {
          session.setAttribute(sessionPrefix+"releasedDBs",releasedDBs);
          }
          
          String version=releasedDBs[0].toLowerCase().replaceAll(user.toLowerCase(),"");
          if (version.indexOf("v2") >= 0 ){
          session.setAttribute(sessionPrefix+"source", Boolean.TRUE);
          }
          listDB=null;
          if (releasedDBs[0] != null ){
              session.setAttribute(sessionPrefix+"dbName",releasedDBs[0]);
              try {
          rs=stmt.executeQuery(SQLMethods.getNSFilterIDSQL(releasedDBs[0],user));
          
          int [] filterIDs = new int [WSASchema.ALLFILTERID.length];
         
          while (rs.next()){
              filterIDs[rs.getInt("filterID")]=rs.getInt("npass");
          }
          session.setAttribute(sessionPrefix+"filterIDs",filterIDs);
          session.setAttribute(sessionPrefix+"reqFilters",filterIDs);
          }
              catch(SQLException se ){
                  logger.error("DBLOGIN "+se);
              }
          }
          } // list empty
 
      }
     
      
      try {
      rs.close();
      stmt.close();
      con.close();
      con=null;
      }
      catch (Exception e){
      logger.error("DBLogin,"+e);
      throw(e);
      }
      }
      return valid;
  }

   
}