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);
*/
		
		}
 }

                                  

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:
206:
207:
208:
209:
210:
211:
212:
213:
214:
215:
216:
217:
218:
219:
220:
221:
222:
223:
224:
225:
226:
227:
228:
229:
230:
231:
232:
233:
234:
235:
236:
237:
238:
239:
240:
241:
242:
243:
244:
245:
246:
247:
248:
249:
250:
251:
252:
253:
254:
255:
256:
257:
258:
259:
260:
261:
262:
263:
264:
265:
266:
267:
268:
269:
270:
271:
272:
273:
274:
275:
276:
277:
278:
279:
280:
281:
282:
283:
284:
285:
286:
287:
288:
289:
290:
291:
292:
293:
294:
295:
296:
297:
298:
299:
300:
301:
302:
303:
304:
305: