SQLCODE: -727, SQLSTATE: 56098

package tgmc.servlets;
 
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 tgmc.beans.WardBean;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.util.Calendar;
import java.util.GregorianCalendar;
 
 
/**
 * Servlet implementation class for Servlet: Wardtransfer
 *
 */
 public class Ward2 extends javax.servlet.http.HttpServlet implements javax.servlet.Servlet {
    /**
	 * 
	 */
	private static final long serialVersionUID = 1L;
 
	/* (non-Java-doc)
	 * @see javax.servlet.http.HttpServlet#HttpServlet()
	 */
	public Ward2() {
		super();
	}   	
	
	/* (non-Java-doc)
	 * @see javax.servlet.http.HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
 
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
	    PrintWriter out=response.getWriter();
		//New ward bean insitialization
		WardBean WB=new WardBean();
		
		//some string insitialization
		String bedno=new String();
		String wardno=new String();
		
		String bed1=new String("");
		String ward2=new String("");
		
		//double variable declarations
		double wardamount=0.0;
	    double totalwardamount=0.0;
		
		//Declaring another string
		String wardcheck=new String("");
        
		HttpSession session=request.getSession(true);
		//session.getAttribute("WTB");
		String 	wardtype=request.getParameter("Ward");
		WB.setWardtype(wardtype);
		
	//	PrintWriter out = null;
		
		String check = (String)session.getAttribute("check");
		WB.setCaseno(check);
		//out.println("hello");
		
		
		//Setting the database connection parameters
		PreparedStatement psmt=null;
		ResultSet rs=null;
		Connection cn=null;
		String connectionURL = "jdbc:db2://localhost:50000/PBILL";
		String query=null;
		String query1=null;
		String query2=null;
		String query3=null;
		String query4=null;
		String query5=null;
		String isclosed=new String("");
	
		//response.sendRedirect("./orgprofile.jsp");
		try
		{
			
		//Declaring database connection	
		Class.forName("com.ibm.db2.jcc.DB2Driver");
	    //Get a Connection to the database
	    cn = DriverManager.getConnection(connectionURL, "db2admin", "admin");
	    
	    
	    query="SELECT PATIENT_CASEDETAILS.PATIENTWARDNO, PATIENT_CASEDETAILS.ISCLOSED FROM KOJ.PATIENT_CASEDETAILS AS PATIENT_CASEDETAILS WHERE PATIENT_CASEDETAILS.PATIENTCASENO = INTEGER('"+WB.getCaseno()+"')";
	    psmt=cn.prepareStatement(query);
	    rs=psmt.executeQuery();		
	    while (rs.next ())
	    {
	    wardcheck = rs.getString("PATIENTWARDNO");
	    isclosed = rs.getString("ISCLOSED");
	    }
	    rs.close();
	    psmt.close();
	    //out.println(wardcheck);
	    if(isclosed.equals("1"))
	    {
	    	response.sendRedirect("./wardtransfer.jsp");
	    }
	    else
	    {
	    	
	    
	    if(wardcheck.equals(wardtype))
	    {
	    	
	    	    
	    	response.sendRedirect("./errorwardtransfer2.jsp");
          	//RequestDispatcher rd = request.getRequestDispatcher("/Wardtransfer2.jsp");
	    	//rd.forward(request, response);
	    
	    
	    }	
		    
	    
	    else
	    {  	
		   	//java.sql.Date hi=null,hi1=null;
	    	String regcheck = (String)session.getAttribute("regcheck");
	    	WB.setRegno(regcheck);
	    	//System.out.println(regcheck);
	    	
	    	query="SELECT WARD_MASTER.BEDNO, WARD_MASTER.WARDNUMBER FROM KOJ.WARD_MASTER AS WARD_MASTER WHERE WARD_MASTER.WARDNUMBER='"+WB.getWardtype()+"' AND WARD_MASTER.STATUS = '0'";
	  	    psmt=cn.prepareStatement(query);
	  	    rs=psmt.executeQuery();		
	  	    while (rs.next ())
	  	    {
	  	     bed1 = rs.getString("BEDNO");
	  	     ward2 = rs.getString("WARDNUMBER");
	  		}
	  	    rs.close();
	  	    psmt.close();
	  	    //System.out.println(bed1);
	  	    WB.setNewbedno(bed1);
	  	    //out.println(bed1);
	  	    if(bed1.equals(""))
	  	    {
	  	    	//System.out.println("Sorry there are no beds available in this war ");
	  	    	//RequestDispatcher rd = request.getRequestDispatcher("/Wardtransfer2.jsp");
		    	//rd.forward(request, response);
	  	    	response.sendRedirect("./errorwardtransfer2.jsp");
	  	    		    
	  	    }
	  	    else
	  	    {
	  	    	
	  	    
//	    query="SELECT WARD_MASTER.STARTDATE,WARD_MASTER.WARDAMOUNT FROM KOJ.WARD_MASTER AS WARD_MASTER WHERE WARD_MASTER.REGNO=regcheck AND WARD_MASTER.WARDNUMBER=wardtype";
	    query="SELECT  WARD_MASTER.WARDNUMBER, WARD_MASTER.WARDAMOUNT, WARD_MASTER.BEDNO FROM KOJ.WARD_MASTER AS WARD_MASTER WHERE WARD_MASTER.REGNO = INTEGER('"+WB.getRegno()+"') ";
	    psmt=cn.prepareStatement(query);
	    rs=psmt.executeQuery();		
	    while (rs.next ())
	    {
	     bedno = rs.getString("BEDNO");
	     wardno=rs.getString("WARDNUMBER");
	     wardamount=rs.getDouble("WARDAMOUNT");
	    }
	    rs.close();
	    psmt.close();
	    WB.setBedno(bedno);
	    //out.println(bedno);
	    //out.println("hello"); 
	    query1="UPDATE KOJ.WARD_MASTER WARD_MASTER SET ENDDATE = CURRENT DATE WHERE WARD_MASTER.REGNO = INTEGER('"+WB.getRegno()+"')  AND WARD_MASTER.BEDNO = INTEGER('"+WB.getBedno()+"')";
		   psmt = cn.prepareStatement(query1);
		   psmt.clearParameters();
		   psmt.executeUpdate();
		   psmt.close();
		   
	   //out.println("hi you");
		   
	   //out.println("hello"); 
	    query="UPDATE KOJ.PATIENT_CASEDETAILS PATIENT_CASEDETAILS SET RELEASEDATE = CURRENT DATE WHERE PATIENT_CASEDETAILS.PATIENTID = INTEGER('"+WB.getRegno()+"')";
	    psmt=cn.prepareStatement(query);
	    psmt.clearParameters();
	    psmt.executeUpdate();		
	    //rs.close();
	    psmt.close();
        
	    java.sql.Date hi=null,hi1=null;
        //out.println("hello"); 
	    query = "SELECT WARD_MASTER.STARTDATE, WARD_MASTER.ENDDATE, WARD_MASTER.WARDAMOUNT, WARD_MASTER.TOTALWARDAMNT FROM KOJ.WARD_MASTER AS WARD_MASTER WHERE WARD_MASTER.REGNO = INTEGER('"+WB.getRegno()+"')";  
	    psmt=cn.prepareStatement(query);
	    rs=psmt.executeQuery();
	    while (rs.next())
	    {
	      hi = rs.getDate("STARTDATE");
	     hi1 = rs.getDate("ENDDATE");
	     wardamount =rs.getFloat("WARDAMOUNT");
	     totalwardamount=rs.getFloat("TOTALWARDAMNT");
	    }
	     
		   rs.close();
		    psmt.close();
		//out.println("hello");
         //out.println(wardamount);
	    //out.println(hi);
	     //out.println(hi1);
	     //long diffInMilleseconds = hi.getTime() - hi1.getTime();
	     //long diffInSeconds = diffInMilleseconds/1000;
	     //long diffInMinutes = diffInSeconds/60;
	     //long diffInHours = diffInMinutes/60;
	     //long diffInDays = diffInHours/24;
	     //out.println(diffInDays);
	     //Date hi1 = rs.getDate("ENDDATE");
	     //int x = hi - hi1;
	     //out.println(hi1);
	     //out.println(x);
	     //Calendar cal1 = Calendar.getInstance();
	     
	     double diff = 0;
	     //Calendar then = Calendar.getInstance();    
	     Calendar then = new GregorianCalendar();
	     then.setTime(hi);
	     //boolean b = false;
		//boolean b = hi1.after(then.getTime());
	     //out.print(b);
	     while(hi1.after(then.getTime()) == true)
	      {
	       then.add(Calendar.DAY_OF_MONTH,1);
	       diff=diff+1;
	      }
 
 
	     
	     //long differenceInMillis = now.getTimeInMillis() - then.getTimeInMillis();
	     //long differenceInDays = differenceInMillis /(24*60*60*1000);
	     out.println(diff);
	     out.println("you sexy thing");
	     totalwardamount=totalwardamount+(wardamount*diff);
 
	    
	    
	    }
	     
	   rs.close();
	   psmt.close();
        //out.println(totalwardamount);
	    WB.setTotalwardamount(totalwardamount);
 
	     query2="UPDATE KOJ.WARD_MASTER WARD_MASTER SET TOTALWARDAMNT = DOUBLE('"+WB.getTotalwardamount()+"') WHERE WARD_MASTER.WARDNUMBER = '"+WB.getWardtype()+"' AND WARD_MASTER.REGNO = INTEGER('"+WB.getRegno()+"')";
	     psmt = cn.prepareStatement(query2);
 	   	psmt.clearParameters();
 	   	psmt.executeUpdate();
 		psmt.close();
		    
 
//	    query="SELECT WARD_MASTER.STARTDATE, WARD_MASTER.WARDNUMBER,WARD_MASTER.WARDAMOUNT FROM KOJ.WARD_MASTER AS WARD_MASTER WHERE WARD_MASTER.STATUS = 0 AND WARD_MASTER.WARDNUMBER=wardtype";
	    query3="UPDATE KOJ.WARD_MASTER WARD_MASTER SET STARTDATE = NULL, STATUS = '0', REGNO = NULL WHERE WARD_MASTER.BEDNO = '"+WB.getBedno()+"'";
	    psmt = cn.prepareStatement(query3);
	   	psmt.clearParameters();
	   	psmt.executeUpdate();
		psmt.close();
//WB.setBedno(bed1);	    
	    query4="UPDATE KOJ.WARD_MASTER WARD_MASTER SET STARTDATE = CURRENT DATE, STATUS = '1', REGNO = INTEGER('"+WB.getRegno()+"') WHERE WARD_MASTER.BEDNO = INTEGER('"+WB.getNewbedno()+"')";
	    psmt = cn.prepareStatement(query4);
	   	psmt.clearParameters();
	   	psmt.executeUpdate();
		psmt.close();
	    query5="UPDATE KOJ.PATIENT_CASEDETAILS PATIENT_CASEDETAILS SET ADMISSIONDATE = CURRENT DATE, PATIENTWARDNO = '"+WB.getWardtype()+"' WHERE PATIENT_CASEDETAILS.PATIENTID = INTEGER('"+WB.getRegno()+"')";
	    psmt = cn.prepareStatement(query5);
	   	psmt.clearParameters();
	   	psmt.executeUpdate();
		psmt.close();
	    
	    //out.println(bed1);
	    //session.setAttribute("bed1", bed1);
 
	    }
	    }
	   
		}
	    /*query="SELECT PATIENT_CASEDETAILS.PATIENTWARDNO FROM KOJ.PATIENT_CASEDETAILS AS PATIENT_CASEDETAILS WHERE PATIENT_CASEDETAILS.PATIENTCASENO = INTEGER(check)";
	    
	    query="UPDATE KOJ.PATIENT_CASEDETAILS PATIENT_CASEDETAILS SET  = '1', DATE = CURRENT DATE, REGNO = INTEGER() WHERE WARD_MASTER.BEDNO =  AND WARD_MASTER.WARDNUMBER = ;
	    
	    	session.setAttribute("wardcheck",wardcheck);
	       	RequestDispatcher rd = request.getRequestDispatcher("/Wardtransfer.jsp");
	    	rd.forward(request, response);
	    	
	    }
	        	    
		}*/
	catch(Exception e) { System.out.println(e); }
	
   //response.sendRedirect("./wardtransfer3.jsp");		
		
		
/*		request.setAttribute("s",s);
		request.setAttribute("r",r);
		request.setAttribute("w",w);
		request.setAttribute("Transferbean",Wardbean);
		RequestDispatcher rd = request.getRequestDispatcher("/wardtransfer2.jsp");
    	rd.forward(request, response);
*/
		
		}
 }