import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.ResultSet;
import DateConvert.DateConversion;
import Bean.LetterDetails;


public class DataBase {
   
    
public Connection setConnection() throws SQLException, ClassNotFoundException
       {
            /*   Class.forName("oracle.jdbc.driver.OracleDriver");
               Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "system", "admin");
            */
			
	
	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
 Connection connection = DriverManager.getConnection("jdbc:odbc:letter");
			   return connection;
		   
		   
       } //connection

public int letterEntry(LetterDetails ld ) throws SQLException, ClassNotFoundException
{
   int t=0 ;
           Connection connection = setConnection(); 
           PreparedStatement pst= connection.prepareStatement("insert into tappal_number values(?,?)");
           pst.setInt(1,Integer.parseInt(ld.getSerial()));
           pst.setString(2,ld.getLettertype());
           t= pst.executeUpdate();
		   String lettertype=ld.getLettertype();

		   if(lettertype.equals("incoming"))
	{
		   DateConversion date=new DateConversion();
           Timestamp entrydate=date.convertToDate(ld.getEntrydate());
           Timestamp letterdate = date.convertToDate(ld.getLetterdate());
           PreparedStatement ps = connection.prepareStatement("insert into i_tappals values(?,?,?,?,?,?,?,?,?,?,?)");
           ps.setInt(1,Integer.parseInt(ld.getSerial()));
           ps.setString(2,ld.getLetterno());
           ps.setTimestamp(3,letterdate);
           ps.setString(4,ld.getReceivedfrom());
           ps.setString(5,ld.getSubject());
           ps.setString(6,ld.getMarkedto());
           ps.setString(7,ld.getAction());
           ps.setString(8,ld.getDemiofficial());
           
		 ps.setString(9,ld.getLettername());
		   ps.setTimestamp(10,entrydate);
		   ps.setString(11,ld.getEntrytime());
         
          t= ps.executeUpdate();
		 
          
}      

   if(lettertype.equals("outgoing"))
	{         
        
     	     DateConversion date=new DateConversion();
           Timestamp entrydate=date.convertToDate(ld.getEntrydate());
            PreparedStatement ps = connection.prepareStatement("insert into o_tappals values(?,?,?,?,?,?,?,?)");
           ps.setInt(1,Integer.parseInt(ld.getSerial()));
           ps.setString(2,ld.getLetterno());
           ps.setTimestamp(3,entrydate);
           ps.setString(4,ld.getEntrytime());
           ps.setString(5,ld.getSubject());
           ps.setString(6,ld.getSendto());
           ps.setString(7,ld.getLettername());
          ps.setString(8,ld.getDemiofficial());
          
		   
           t= ps.executeUpdate();
          
            
}      

return t; 

}
    
	   
	   
	   
public int updateEntry(LetterDetails ld ) throws SQLException, ClassNotFoundException
{
   int t=0 ;
           Connection connection = setConnection(); 
           		   String lettertype=ld.getLettertype();

		   if(lettertype.equals("incoming"))
	{
		   DateConversion date=new DateConversion();
           Timestamp entrydate=date.convertToDate(ld.getEntrydate());
           Timestamp letterdate = date.convertToDate(ld.getLetterdate());
           PreparedStatement ps = connection.prepareStatement("update i_tappals set letterno=?,letterdate=?,receivedfrom=?,subject=?,markedto=?,action=?,demiofficial=?,lettername=?,entrydate=?,entrytime=? where serial=?");
           ps.setString(1,ld.getLetterno());
           ps.setTimestamp(2,letterdate);
           ps.setString(3,ld.getReceivedfrom());
           ps.setString(4,ld.getSubject());
           ps.setString(5,ld.getMarkedto());
           ps.setString(6,ld.getAction());
           ps.setString(7,ld.getDemiofficial());
           
		 ps.setString(8,ld.getLettername());
		   ps.setTimestamp(9,entrydate);
		   ps.setString(10,ld.getEntrytime());
          ps.setInt(11,Integer.parseInt(ld.getSerial()));
          
          t= ps.executeUpdate();
		 
          
}      

   if(lettertype.equals("outgoing"))
	{         
        
     	     DateConversion date=new DateConversion();
           Timestamp entrydate=date.convertToDate(ld.getEntrydate());
            PreparedStatement ps = connection.prepareStatement("update o_tappals set letterno=?,entrydate=?,entrytime=?,subject=?,sendto=?,lettername=?,demiofficial=? where serial=?");
          
           ps.setString(1,ld.getLetterno());
           ps.setTimestamp(2,entrydate);
           ps.setString(3,ld.getEntrytime());
           ps.setString(4,ld.getSubject());
           ps.setString(5,ld.getSendto());
           ps.setString(6,ld.getLettername());
          ps.setString(7,ld.getDemiofficial());
           ps.setInt(8,Integer.parseInt(ld.getSerial()));
		   
           t= ps.executeUpdate();
          
            
}      

return t; 

}
    
	   
	   
	   
	   
	   
	   
	   
	   public boolean validateUser(String uname, String pword) throws ClassNotFoundException, SQLException
   {
       boolean isvalid=false;
       Connection connection = setConnection(); 
            PreparedStatement ps = connection.prepareStatement("select * from validate where username=? and password=?");
       ps.setString(1, uname);
       ps.setString(2, pword); 
       ResultSet resultset = ps.executeQuery();
       while(resultset.next())
       {
           isvalid=true;
       }
       return isvalid;
   }

public boolean delete(int serial ) throws SQLException, ClassNotFoundException
{
          boolean isvalid=false;
           Connection connection = setConnection(); 
    	  
            PreparedStatement ps1 = connection.prepareStatement("delete from  i_tappals where serial="+serial+"");
           PreparedStatement ps2 =  connection.prepareStatement("delete from  o_tappals where serial="+serial+"");
           PreparedStatement ps3=  connection.prepareStatement("delete  from tappal_number where sno="+serial+"");
 int  i= ps1.executeUpdate();
 int  j= ps2.executeUpdate();
int  k= ps3.executeUpdate();

if(i>0||j>0||k>0)
	{
	isvalid=true;
	return isvalid;
 }
else
 		  return isvalid;
  
			   }	   
		   
		     

}



















