/*
 * Created on 01-Aug-2011
 *
 * TODO 
 */
package uk.ac.roe.wfau;

import java.sql.Connection;
import java.sql.PreparedStatement;

/**
 * @author mar
 * class to return SQL snippets based on archive
 */
public class VDFSSQLStatements {
    public static PreparedStatement getDeleteUserPrepareStmt(Connection con,int archiveID, String remoteUser, String userName, String community) {
        try {
        if (archiveID==VDFSSchema.WSAARCHIVEID){
            PreparedStatement pstmt=con.prepareStatement("delete from wsa_users where username=? and community=?");
            pstmt.setString(1,userName);
            pstmt.setString(2,community);
            return pstmt;

        }
        else if (archiveID==VDFSSchema.VSAARCHIVEID) {
            PreparedStatement pstmt=con.prepareStatement("delete from wsa_nonSurveyUsers where username=? and community=? and archiveID="+VDFSSchema.VSAARCHIVEID);
            pstmt.setString(1,userName);
            pstmt.setString(2,VSASchema.getProprietaryCommunity(VSASchema.getProgIDFromString(remoteUser),remoteUser));
            return pstmt;
            
        }
        else if (archiveID==VDFSSchema.GESARCHIVEID && remoteUser.toLowerCase().trim().startsWith("ges")) {
            PreparedStatement pstmt=con.prepareStatement("delete from wsa_nonSurveyUsers where username=? and community=? and archiveID="+VDFSSchema.GESARCHIVEID);
            pstmt.setString(1,userName);
            pstmt.setString(2,GESSchema.GESCOMMUNITYPREFIX+community);
            return pstmt;
            
        }
        else if (archiveID==VDFSSchema.OSAARCHIVEID && OSASchema.isCommunityValidForUser(community, remoteUser)) {
            PreparedStatement pstmt=con.prepareStatement("delete from wsa_nonSurveyUsers where username=? and community=? and archiveID="+VDFSSchema.OSAARCHIVEID);
            pstmt.setString(1,userName);
            pstmt.setString(2,OSASchema.OSACOMMUNITYPREFIX+community);
            return pstmt;
            
        }
        else {
        return null;
        }
        }
        catch (Exception e) {
            System.out.println(e); 
            return null;
        }
    }
    
    public static PreparedStatement setDeleteUserPrepareStmt(PreparedStatement delPstmt,int archiveID,String remoteUser, String userName, String community) {
        try {
        if (archiveID==VDFSSchema.WSAARCHIVEID){
            delPstmt.setString(1,userName);
            delPstmt.setString(2,community);
            delPstmt.addBatch();      
            return delPstmt;

        }
        else if (archiveID==VDFSSchema.VSAARCHIVEID) {
            delPstmt.setString(1,userName);
            delPstmt.setString(2,VSASchema.getProprietaryCommunity(VSASchema.getProgIDFromString(remoteUser),remoteUser));
            delPstmt.addBatch();
            return delPstmt;
            
            
        }
        else {
        return null;
        }
        }
        catch (Exception e) {
            System.out.println(e); 
            return null;
        }
    }
    
    
    public static PreparedStatement getDeleteUserPrepareStmt(Connection con,int archiveID) {
        try {
        if (archiveID==VDFSSchema.WSAARCHIVEID){
            PreparedStatement delPstmt=con.prepareStatement("delete from wsa_users where username=? and community=?");
      
            return delPstmt;

        }
        else if (archiveID==VDFSSchema.VSAARCHIVEID) {
            PreparedStatement delPstmt=con.prepareStatement("delete from wsa_nonSurveyUsers where username=? and community=?");
            return delPstmt;
            
        }
        else {
        return null;
        }
        }
        catch (Exception e) {
            System.out.println(e); 
            return null;
        }
    }
    
    
    
    
    public static PreparedStatement getAddUserPrepareStmt(Connection con,int archiveID, String remoteUser, String userName, String community,String lastName,String firstName,String passwd1,String email,String IPAddress) {
        try {
        if (archiveID==VDFSSchema.WSAARCHIVEID){
            PreparedStatement pstmt=con.prepareStatement("insert into wsa_users (lastName,firstName,username,community,password,email,ipaddress) values (?,?,?,?,?,?,?)");            
            pstmt.setString(1,lastName);
            pstmt.setString(2,firstName);
            pstmt.setString(3,userName);
            pstmt.setString(4,community);
            pstmt.setString(5,passwd1);
            pstmt.setString(6,email);
            pstmt.setString(7,IPAddress);
            return pstmt;

        }
        else if (archiveID==VDFSSchema.VSAARCHIVEID) {
            PreparedStatement pstmt=con.prepareStatement("insert into wsa_nonSurveyUsers " +
            		"(firstName,lastName,username,password,community,email,programmeid,databasename,sourcetable," +
            		"numfilterid1,numfilterid2,numfilterid3,numfilterid4,numfilterid5,numfilterid6,numfilterid7," +
            		"numfilterid8,numfilterid9,numfilterid10,schemaversion,archiveID) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); 
            pstmt.setString(1,firstName);
            pstmt.setString(2,lastName);
            pstmt.setString(3,userName);
            pstmt.setString(4,passwd1);
            pstmt.setString(5,VSASchema.getProprietaryCommunity(VSASchema.getProgIDFromString(remoteUser),remoteUser));
            pstmt.setString(6,email);
            pstmt.setInt(7,VSASchema.getProgIDFromString(remoteUser));
            pstmt.setString(8,"na");
            pstmt.setString(9,"y");
            for (int i=0; i<10; i++) {
                pstmt.setInt(10+i,0);
            }
            pstmt.setNull(20, java.sql.Types.VARCHAR);
            pstmt.setInt(21,archiveID);
            return pstmt;
            
        }
        else if (archiveID==VDFSSchema.GESARCHIVEID && remoteUser.toLowerCase().trim().startsWith("ges")) {
            PreparedStatement pstmt=con.prepareStatement("insert into wsa_nonSurveyUsers " +
            		"(firstName,lastName,username,password,community,email,programmeid,databasename,sourcetable," +
            		"numfilterid1,numfilterid2,numfilterid3,numfilterid4,numfilterid5,numfilterid6,numfilterid7," +
            		"numfilterid8,numfilterid9,numfilterid10,schemaversion,archiveID) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); 
            pstmt.setString(1,firstName);
            pstmt.setString(2,lastName);
            pstmt.setString(3,userName);
            pstmt.setString(4,passwd1);
            pstmt.setString(5,GESSchema.GESCOMMUNITYPREFIX+community);
            pstmt.setString(6,email);
            pstmt.setInt(7,GESSchema.GESPROGRAMMEID);
            pstmt.setString(8,"na");
            pstmt.setString(9,"y");
            for (int i=0; i<10; i++) {
                pstmt.setInt(10+i,0);
            }
            pstmt.setNull(20, java.sql.Types.VARCHAR);
            pstmt.setInt(21,archiveID);
            return pstmt;
           
        }
        else if (archiveID==VDFSSchema.OSAARCHIVEID  && OSASchema.isCommunityValidForUser(community, remoteUser)) {
            PreparedStatement pstmt=con.prepareStatement("insert into wsa_nonSurveyUsers " +
            		"(firstName,lastName,username,password,community,email,programmeid,databasename,sourcetable," +
            		"numfilterid1,numfilterid2,numfilterid3,numfilterid4,numfilterid5,numfilterid6,numfilterid7," +
            		"numfilterid8,numfilterid9,numfilterid10,schemaversion,archiveID) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); 
            pstmt.setString(1,firstName);
            pstmt.setString(2,lastName);
            pstmt.setString(3,userName);
            pstmt.setString(4,passwd1);
            pstmt.setString(5,OSASchema.OSACOMMUNITYPREFIX+community);
            pstmt.setString(6,email);
            pstmt.setInt(7,OSASchema.getProgIDFromString(remoteUser));
            pstmt.setString(8,"na");
            pstmt.setString(9,"y");
            for (int i=0; i<10; i++) {
                pstmt.setInt(10+i,0);
            }
            pstmt.setNull(20, java.sql.Types.VARCHAR);
            pstmt.setInt(21,archiveID);
            return pstmt;
          
        }
        else {
        return null;
        }
        }
        catch (Exception e) {
            System.out.println(e); 
            return null;
        }
    }
    
    public static PreparedStatement setAddUserPrepareStmt(PreparedStatement addPstmt,int archiveID, String remoteUser, String userName, String community,String lastName,String firstName,String passwd1,String email,String IPAddress) {
        try {
        if (archiveID==VDFSSchema.WSAARCHIVEID){
            addPstmt.setString(1,lastName);
            addPstmt.setString(2,firstName);
            addPstmt.setString(3,userName);
            addPstmt.setString(4,community);
            addPstmt.setString(5,passwd1);
            addPstmt.setString(6,email);
            addPstmt.setString(7,IPAddress);
            addPstmt.addBatch();
            return addPstmt;

        }
        else if (archiveID==VDFSSchema.VSAARCHIVEID) {
            addPstmt.setString(1,firstName);
            addPstmt.setString(2,lastName);
            addPstmt.setString(3,userName);
            addPstmt.setString(4,passwd1);
            addPstmt.setString(5,VSASchema.getProprietaryCommunity(VSASchema.getProgIDFromString(remoteUser),remoteUser));
            addPstmt.setString(6,email);
            addPstmt.setInt(7,VSASchema.getProgIDFromString(remoteUser));
            addPstmt.setString(8,"na");
            addPstmt.setString(9,"y");
            for (int i=0; i<10; i++) {
                addPstmt.setInt(10+i,0);
            }
            addPstmt.setNull(20, java.sql.Types.VARCHAR);
            addPstmt.addBatch();
            return addPstmt;
            
        }
        else {
        return null;
        }
        }
        catch (Exception e) {
            System.out.println(e); 
            return null;
        }
    }
    
    public static PreparedStatement getAddUserPrepareStmt(Connection con,int archiveID) {
        try {
        if (archiveID==VDFSSchema.WSAARCHIVEID){
            PreparedStatement addPstmt=con.prepareStatement("insert into wsa_users (lastName,firstName,username,community,password,email,ipaddress) values (?,?,?,?,?,?,?)");            
            return addPstmt;

        }
        else if (archiveID==VDFSSchema.VSAARCHIVEID) {
            PreparedStatement addPstmt=con.prepareStatement("insert into wsa_nonSurveyUsers " +
            		"(firstName,lastName,username,password,community,email,programmeid,databasename,sourcetable," +
            		"numfilterid1,numfilterid2,numfilterid3,numfilterid4,numfilterid5,numfilterid6,numfilterid7," +
            		"numfilterid8,numfilterid9,numfilterid10,schemaversion,archiveID) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); 
            return addPstmt;
            
        }
        else {
        return null;
        }
        }
        catch (Exception e) {
            System.out.println(e); 
            return null;
        }
    }
    
    
    public static PreparedStatement getListUserPrepareStmt(Connection con,int archiveID, String remoteUser, String community, String userName) {
        try {
            if (archiveID==VDFSSchema.WSAARCHIVEID){
                PreparedStatement pstmt=con.prepareStatement("select top 500 lastName,firstName,username,email from wsa_users where community=? and (username like ? or firstname like ? or lastname like ? )");            
                pstmt.setString(1,community);
                pstmt.setString(2,"%"+userName+"%");
                pstmt.setString(3,"%"+userName+"%");
                pstmt.setString(4,"%"+userName+"%");
                return pstmt;

            }
            else if (archiveID==VDFSSchema.VSAARCHIVEID) {
                PreparedStatement pstmt=con.prepareStatement("select top 500 lastName,firstName,username,email from wsa_nonSurveyUsers where community=? and (username like ? or firstname like ? or lastname like ? )");
                pstmt.setString(1,VSASchema.getProprietaryCommunity(VSASchema.getProgIDFromString(remoteUser),remoteUser));
                pstmt.setString(2,"%"+userName+"%");
                pstmt.setString(3,"%"+userName+"%");
                pstmt.setString(4,"%"+userName+"%");
                return pstmt;	                
            }
            else if (archiveID==VDFSSchema.GESARCHIVEID && remoteUser.toLowerCase().trim().startsWith("ges")) {
                PreparedStatement pstmt=con.prepareStatement("select top 500 lastName,firstName,username,email from wsa_nonSurveyUsers where archiveID="+VDFSSchema.GESARCHIVEID+" and community=? and (username like ? or firstname like ? or lastname like ? )");
              //  pstmt.setString(1,VSASchema.getProprietaryCommunity(VSASchema.getProgIDFromString(remoteUser),remoteUser));
                pstmt.setString(1,GESSchema.GESCOMMUNITYPREFIX+community);
                pstmt.setString(2,"%"+userName+"%");
                pstmt.setString(3,"%"+userName+"%");
                pstmt.setString(4,"%"+userName+"%");
                return pstmt;	                
            }
            else if (archiveID==VDFSSchema.OSAARCHIVEID  && OSASchema.isCommunityValidForUser(community, remoteUser)) {
                PreparedStatement pstmt=con.prepareStatement("select top 500 lastName,firstName,username,email from wsa_nonSurveyUsers where archiveID="+VDFSSchema.OSAARCHIVEID+" and community=? and (username like ? or firstname like ? or lastname like ? )");
              //  pstmt.setString(1,VSASchema.getProprietaryCommunity(VSASchema.getProgIDFromString(remoteUser),remoteUser));
                pstmt.setString(1,OSASchema.OSACOMMUNITYPREFIX+community);
                pstmt.setString(2,"%"+userName+"%");
                pstmt.setString(3,"%"+userName+"%");
                pstmt.setString(4,"%"+userName+"%");
                return pstmt;	                
            }
            else {
            return null;
            }
            }
            catch (Exception e) {
                System.out.println(e); 
                return null;
            } 
    }
    
    public static PreparedStatement getCheckCommunityPrepareStmt(Connection con,int archiveID, String remoteUser, String community) {
        try {
        if (archiveID==VDFSSchema.WSAARCHIVEID){
            PreparedStatement pstmt=con.prepareStatement("select * from wsa_community where community=?");
            pstmt.setString(1,community);
            return pstmt;
        }
        else if (archiveID==VDFSSchema.VSAARCHIVEID) {
            //PreparedStatement pstmt=con.prepareStatement("select top 1 * from wsa_nonSurveyUsers where community=?");
            PreparedStatement pstmt=con.prepareStatement("select top 1 * from wsa_community where community=?");
            pstmt.setString(1,VSASchema.getProprietaryCommunity(VSASchema.getProgIDFromString(remoteUser),remoteUser));
            return pstmt;
      
        }
        else if (archiveID==VDFSSchema.GESARCHIVEID && remoteUser.toLowerCase().trim().startsWith("ges")) {
            //PreparedStatement pstmt=con.prepareStatement("select top 1 * from wsa_nonSurveyUsers where community=?");
            PreparedStatement pstmt=con.prepareStatement("select top 1 * from wsa_community where community=?");
            pstmt.setString(1,GESSchema.GESCOMMUNITYPREFIX+community);
            return pstmt;
      
        }
        else if (archiveID==VDFSSchema.OSAARCHIVEID  && OSASchema.isCommunityValidForUser(community, remoteUser)) {
            //PreparedStatement pstmt=con.prepareStatement("select top 1 * from wsa_nonSurveyUsers where community=?");
            PreparedStatement pstmt=con.prepareStatement("select top 1 * from wsa_community where community=?");
            pstmt.setString(1,OSASchema.OSACOMMUNITYPREFIX+community);
            return pstmt;
      
        }
        else {
        return null;
        }
        }
        catch (Exception e) {
            System.out.println(e); 
            return null;
        } 
        
        
    }
    
    
    public static PreparedStatement getAddCommunityPrepareStmt(Connection con,int archiveID,String community) {
        try {
        if (archiveID==VDFSSchema.GESARCHIVEID ){
            PreparedStatement pstmt=con.prepareStatement("insert into wsa_community (community) values (?)");            
            pstmt.setString(1,GESSchema.GESCOMMUNITYPREFIX+community);
            return pstmt;
        }
        else if (archiveID==VDFSSchema.WSAARCHIVEID) {
        	PreparedStatement pstmt=con.prepareStatement("insert into wsa_community (community) values (?)");
            pstmt.setString(1,community);
            return pstmt;            
        }
        else {
        return null;
        }
        }
        catch (Exception e) {
            System.out.println(e); 
            return null;
        }
    }
    public static String getCheckCommunitySQL(int archiveID, String remoteUser, String community) {        
        if (archiveID==VDFSSchema.WSAARCHIVEID){
            return "select * from wsa_community where community='"
                                        + community + "'";
        }
        else if (archiveID==VDFSSchema.VSAARCHIVEID) {
            return "select top 1 * from wsa_nonSurveyUsers where community='" + community + "'";
      
        }
        else {
        return null;
        }
    }

}