BusinessRules xref

View Javadoc

1   package org.opensync.tools;
2   
3   import java.sql.Connection;
4   import java.sql.ResultSet;
5   import java.sql.SQLException;
6   import java.sql.SQLWarning;
7   import java.sql.Statement;
8   import java.text.ParseException;
9   import java.util.Date;
10  
11  /***
12   *	This class provides some methods that implements some business rules
13   */
14  
15  public class BusinessRules
16  {
17  
18  public static void applyJobRules(String jobNo)
19  {
20  	PoolManager poolMgr = PoolManager.getInstance();
21  	Connection conn = null;
22  	boolean anyAction = false;
23  	String table = "EN_COURS";
24  	// etablish the connection and execute query Insert
25  	try {
26  		conn = poolMgr.getConnection("sophieConnection");
27  		String date_max = null, date_min = null;
28  		String tmpStr2=null, tmpStr1=null;
29  
30  		int external=0,internal=0;
31  		String fourni="",code_four="",int_cm="",code_techn="";
32  
33  		float /*internalHours = 0,*/internalDeptCost=1,/*travelHrs = 0,*/internalTotalTravelCost = 0,piec = 0,travelCost = 0,misc = 0 ;
34  		float sumInternalHours=0,sumTravelHrs=0,sumInternalTotalTravelCost=0,sumPiec=0,sumTravelCost=0,sumMisc=0 ;
35  		float deptCost =0;
36  		float costOfTheDept =0;
37  		float costOfInternalTech =0;
38  		// FOR EXTERNAL TECHNICIANS
39  
40  		float ExtSumNoOfHrsWorked = 0, ExtSumAmountLabour = 0,ExtSumAmountTravel = 0,ExtSumPart1 = 0, ExtSumPart2 = 0, extSumMisc = 0;
41  		float costOfExternalTech = 0;
42  		float ExtNoOfHrsWorked = 0, ExtAmountLabour = 0,ExtAmountTravel = 0;
43  		float ExtPart1=0,ExtPart2 = 0,extMisc = 0;
44  
45  		Date sysDate = new Date();
46  		Date earliestDate = null, latestDate = null;
47  		APDateFormatter dtFormatter = new APDateFormatter();
48  
49  		float VAT_Rate1 = 0,VAT_Rate2 = 0;
50  
51  		String query     = null;
52  
53  		query = "select REM_A_DISP, INT_CONTRA  from "+table+" where nu_int = '"+jobNo+"'";
54  		Statement stmt = conn.createStatement();
55  		ResultSet rs = stmt.executeQuery(query);
56  		if(rs.next())
57  		{
58  			tmpStr1 = rs.getString(1);
59  			tmpStr2 = rs.getString(2);
60  		}
61  		rs.close();
62  		//stmt2.close();
63  
64  		if (tmpStr1 != null && tmpStr1.trim().length()!=0)
65  		{
66  
67  			query = "select t_tva from tva where c_tva = '"+tmpStr1.trim()+"'";
68  			//stmt3 = conn.createStatement();
69  			rs = stmt.executeQuery(query);
70  			//rs.next();
71  
72  			if (rs.next() && (tmpStr1=getValue(rs.getString(1))) != null)
73  				VAT_Rate1 = Float.parseFloat(tmpStr1);
74  			rs.close();
75  		}
76  
77  		if (tmpStr2 != null && tmpStr2.trim().length()!=0)
78  		{
79  
80  			query = "select t_tva from tva where c_tva = '"+tmpStr2.trim()+"'";
81  			rs = stmt.executeQuery(query);
82  			//rs.next();
83  
84  			if (rs.next() && (tmpStr1=getValue(rs.getString(1))) != null)
85  				VAT_Rate2 = Float.parseFloat(tmpStr1);
86  			rs.close();
87  		}
88  
89  		String selectQueryForOldestDate ="SELECT DA_INT,HE_INT,DA_FIN,HE_FIN,NHE,C_NHE,HINT,C_DEP,PIEC,DIV_INT,INT_CM, "+
90  		" NHEE, MO_HT, DEP_HT, PD1_HT, PD2_HT, DIV_EXT, CODE_FOUR, FOURNI, CODE_TECHN, TYPE_INT from in_venan WHERE NU_INT = '"+ jobNo + "'";
91  
92  		//System.out.println("select query "+selectQueryForOldestDate);
93  
94  		rs = stmt.executeQuery(selectQueryForOldestDate);
95  
96  		while (rs.next())
97  		{
98  			Date startDate, finalDate;
99  			float internalHours = 0, travelHrs = 0;
100 			String tempCode_four = "", tempFourni ="", tempCode_techn="", techDept ="";
101 			anyAction = true;
102 			try
103 			{
104 				tmpStr1 = getValue(rs.getString(1));
105 				tmpStr2 = getValue(rs.getString(2));
106 				if(tmpStr1!= null)
107 				{
108 					startDate = dtFormatter.parse(tmpStr1, tmpStr2);
109 				}
110 				else
111 					startDate = sysDate;
112 			}catch(ParseException ex)
113 			{
114 				startDate = sysDate;
115 			}
116 
117 			try
118 			{
119 				tmpStr1 = getValue(rs.getString(3));
120 				tmpStr2 = getValue(rs.getString(4));
121 				//finalDate = dtFormatter.parse(rs.getString(3).trim(), rs.getString(4).trim());
122 				if(tmpStr1 != null)
123 				{
124 					finalDate = dtFormatter.parse(tmpStr1, tmpStr2);
125 				}
126 				else
127 					finalDate = sysDate;
128 			}catch(ParseException ex)
129 			{	finalDate = sysDate;	}
130 
131 
132 			if(earliestDate== null || earliestDate.after(startDate))
133 				earliestDate = startDate;
134 
135 			if(latestDate == null || latestDate.before(finalDate))
136 				latestDate = finalDate;
137 
138 			if ( (tmpStr1=getValue(rs.getString(5))) !=null)
139 			{
140 				internalHours = Float.parseFloat(tmpStr1);
141 				sumInternalHours += internalHours;
142 			}
143 
144 			tmpStr1 = rs.getString(6);
145 			if ( (tmpStr1=getValue(rs.getString(7))) !=null)
146 			{
147 				travelHrs = Float.parseFloat(tmpStr1);
148 				sumTravelHrs += travelHrs;
149 			}
150 
151 			if( (tmpStr1=getValue(rs.getString(8))) !=null)
152 				sumInternalTotalTravelCost += Float.parseFloat(tmpStr1);
153 
154 			tmpStr1 = rs.getString(9);
155 			if( (tmpStr1=getValue(rs.getString(10))) !=null)
156 				sumMisc += Float.parseFloat(tmpStr1.trim());
157 
158 			if( (tmpStr1=getValue(rs.getString(11))) !=null)
159 			{
160 				techDept = tmpStr1;
161 				Connection conn2; Statement stmt2; ResultSet rs2;
162 				conn2 = poolMgr.getConnection("sophieConnection");
163 				try
164 				{
165 					query = "select CMNT from metiers where c_metier = '"+techDept+"'";
166 					stmt2 = conn2.createStatement();
167 					rs2 = stmt2.executeQuery(query);
168 
169 					System.out.println("Internat Cost :" + costOfInternalTech);
170 					if (rs2.next() && ( (tmpStr1=getValue(rs2.getString(1))) != null) )
171 						costOfInternalTech +=  (Float.parseFloat(tmpStr1)) * (travelHrs + internalHours);
172 					travelHrs = internalHours =(float)0.0;
173 					rs2.close();
174 					stmt2.close();
175 				}catch(SQLException ex)
176 				{
177 					System.out.println("SQL Exception: Amount for dept " + techDept+ " was not added");
178 				}finally
179 				{
180 					poolMgr.freeConnection("sophieConnection", conn2);
181 				}
182 			}
183 
184 
185 			//System.out.println("starting of external techs");
186 			if( (tmpStr1=getValue(rs.getString(12))) !=null)
187 				ExtSumNoOfHrsWorked += Float.parseFloat(tmpStr1) ;
188 
189 			if( (tmpStr1=getValue(rs.getString(13))) !=null)
190 				ExtSumAmountLabour +=  Float.parseFloat(tmpStr1);
191 
192 			if( (tmpStr1=getValue(rs.getString(14))) !=null)
193 				ExtSumAmountTravel +=  Float.parseFloat(tmpStr1);
194 
195 			if( (tmpStr1=getValue(rs.getString(15))) !=null)
196 				ExtSumPart1 +=  Float.parseFloat(tmpStr1);
197 
198 			if( (tmpStr1=getValue(rs.getString(16))) !=null)
199 				ExtSumPart2 +=  Float.parseFloat(tmpStr1);
200 
201 			if( (tmpStr1=getValue(rs.getString(17))) !=null)
202 				extSumMisc +=  Float.parseFloat(tmpStr1);
203 
204 			if( (tmpStr1=getValue(rs.getString(18))) !=null)
205 				tempCode_four = tmpStr1;
206 
207 			if( (tmpStr1=getValue(rs.getString(19))) !=null)
208 				tempFourni = tmpStr1;
209 
210 			if( (tmpStr1=getValue(rs.getString(20))) !=null)
211 				tempCode_techn = tmpStr1;
212 
213 			tmpStr1=getValue(rs.getString(21));
214 			if(tmpStr1!=null && tmpStr1.equals("2"))
215 			{
216 				code_four = tempCode_four;
217 				fourni = tempFourni;
218 				external++;
219 			}else
220 			{
221 				internal++;
222 				code_techn = tempCode_techn;
223 				int_cm = techDept;
224 			}
225 
226 			costOfExternalTech += ExtAmountLabour *(1+(VAT_Rate1/100))
227 				+ExtAmountTravel*(1+(VAT_Rate1/100))+ExtPart1*(1+(VAT_Rate1/100))
228 				+ExtPart2*(1+(VAT_Rate2/100))+ extMisc;
229 			ExtAmountLabour = ExtAmountTravel=ExtPart1=ExtPart2=extMisc=0;
230 			//System.out.println("total count "+ costOfExternalTech);
231 		}
232 
233 		rs.close();
234 
235 		if(anyAction)
236 		{
237 			String ana_def="1",cint="Y",e_refer="1";
238 			if (internal!=0 && external!=0) {ana_def="2";cint="Y";e_refer="2";}
239 			if (internal!=0 && external==0)
240 			{
241 				ana_def="1";cint="Y";
242 				if (internal>1) e_refer="2";
243 				else e_refer="1";
244 			}
245 			if (internal==0 && external!=0)
246 			{
247 				ana_def="1";cint="N";
248 				if (external>1) e_refer="2";
249 				else e_refer="1";
250 			}
251 
252 			dtFormatter = new APDateFormatter(APDateFormatter.AP_DB2);
253 			dtFormatter.format(earliestDate);
254 			String startDateStr = dtFormatter.getDateString();
255 			String startTimeStr = dtFormatter.getTimeString();
256 			dtFormatter.format(latestDate);
257 			String finalDateStr = dtFormatter.getDateString();
258 			String finalTimeStr = dtFormatter.getTimeString();
259 
260 			//System.out.println("Final Internat Cost :" + costOfInternalTech);
261 			sumPiec = getInternalPartCost(jobNo);
262 			float total_cost = costOfInternalTech + sumInternalTotalTravelCost + sumPiec + sumMisc;
263 			float ext_Total_Cost = costOfExternalTech ;
264 
265 			String queryUpdate = "update "+table+" set da_int = '"+startDateStr+"', he_int = '"+startTimeStr+"',"+
266 			" da_hs = '"+startDateStr+"', he_hs = '"+startTimeStr+"',"+
267 			" da_fin = '"+finalDateStr+"', he_fin = '"+finalTimeStr+"',"+
268 			" da_dis = '"+finalDateStr+"', he_dis = '"+finalTimeStr+"', NHE = '"+
269 			sumInternalHours+"', HINT = '"+sumTravelHrs+"', cdep ='"+sumInternalTotalTravelCost+"', "+
270 			"piec = '"+sumPiec+"',div_int = '"+sumMisc+"',tot_int = '"+total_cost+"' ,"+
271 			"NHEE = "+ExtSumNoOfHrsWorked+", MO_HT = "+ExtSumAmountLabour+" ,DEP_HT = "+ExtSumAmountTravel+
272 			", PD1_HT = "+ExtSumPart1+",PD2_HT = "+ExtSumPart2+",TOT_TTC="+costOfExternalTech+",DIV_EXT="+extSumMisc+
273 			", ANA_DEF='"+ana_def+"', CINT='"+cint+"', E_REFER='"+e_refer+"', CODE_FOUR='"+code_four+
274 			"', FOURNI='"+fourni+"', CODE_TECHN='"+code_techn+"', INT_CM='"+int_cm+
275 			"' where nu_int ='"+jobNo + "'";
276 
277 			System.out.println("udpate query = "+queryUpdate);
278 			stmt.executeUpdate(queryUpdate);
279 		}
280 		stmt.close();
281 
282 		if (Utils.debug) System.out.println(" executeInsert OK " );
283 	}
284 	catch(SQLWarning e1)
285 	{
286 		Utils.SqlLogToFile("SQLWarning: " + e1.getMessage());
287 		e1.printStackTrace();
288 		//this.errorRecord = 1;
289 		//throw e1;
290 		//TBD -- Error Handling mechanism
291 	}
292 	catch(SQLException e)
293 	{
294 		Utils.SqlLogToFile("SQLException: " + e.getMessage());
295 		e.printStackTrace();
296 		//this.errorRecord = 1;
297 		//throw e;
298 		//TBD -- Error Handling mechanism
299 	}
300 	finally
301 	{
302 		poolMgr.freeConnection("sophieConnection",conn);
303 	}
304 
305 }
306 
307 public static void moveClosedJob(String jobNo) throws SQLException
308 {
309 	String query,status=null;
310 	int retValue,i;
311 	String[] values;
312 	PoolManager poolMgr = PoolManager.getInstance();
313 	Connection conn1 = poolMgr.getConnection("sophieConnection");
314 	Statement stmt1 = conn1.createStatement();
315 
316 	try
317 	{
318 		query = "SELECT INT_STATUT FROM EN_COURS WHERE NU_INT = '" + jobNo +"'";
319 		ResultSet rs1 = stmt1.executeQuery(query);
320 		if(rs1.next())
321 			status = getValue(rs1.getString(1));
322 		rs1.close();
323 
324 		if (status != null && status.equals("4"))
325 		{
326 			String[] colNames = {"NU_INT","NU_BON_C","NU_IMM","N_UF","N_EF","DA_AP","HE_AP","DA_INT","HE_INT",
327 				"DA_HS","HE_HS","DA_DIS","HE_DIS","DA_FIN","HE_FIN","URG","DISP_EF_AT","DISP_AP_AT","COMPT",
328 				"CADRE","NHE","C_FAC","COD_SAIS","PD1_HT","PD2_HT","MO_HT","DEP_HT","TOT_TTC","HINT","CDEP",
329 				"PIEC","CINT","OBSERV","ANA_DEF","REM_A_DISP","R_A_D_SUIT","CODE_FOUR","CODE_TECHN","FOURNI",
330 				"N_MARCHE","M_AN_EFFET","OBSERV2","E_REFER","DIT_TCMN","INT_STATUT","INT_CM","INT_CAUSE",
331 				"INT_REMED","INT_CONTRA","INTERLOC","PHONE","HLIMIT","W_NATURE","NHEE","TECHN_EXT","TOT_INT",
332 				/*"DM_DII","DM_DI",*/"NU_COMPTE","DATE_SYST","DA_REC","HE_REC","DELAI","CODE_DELAI","N_FACTURE",
333 				"MT_ENGAG","DA_RECP","DIV_INT","DIV_EXT","LIB_CADRE","LIB_STATUT","PAR1","DATE_OF_NU_BON_C"};
334 			String colNameString = "";
335 
336 			for(i = 0; i<(colNames.length-1); i++)
337 			{
338 				colNameString += colNames[i] + ",";
339 			}
340 			colNameString += colNames[i];
341 			query = "SELECT " + colNameString + " from EN_COURS where NU_INT = '" + jobNo + "'";
342 			System.out.println("MOVE QUERY :" + query);
343 			rs1 = stmt1.executeQuery(query);
344 
345 			if(rs1.next())
346 			{
347 				String tmpStr;
348 				query = "INSERT INTO B_FT1996 ( " + colNameString + ") values (";
349 				for(i = 0; i<(colNames.length-1); i++)
350 				{
351 					tmpStr = getValue(rs1.getString(i+1));
352 					tmpStr = (tmpStr == null)?"":tmpStr;
353 					query += "'" + tmpStr + "',";
354 				}
355 				tmpStr = getValue(rs1.getString(i+1));
356 				tmpStr = (tmpStr == null)?"":tmpStr;
357 				query += "'" + tmpStr + "')";
358 				rs1.close();
359 
360 				System.out.println("MOVE QUERY :" + query);
361 				retValue = stmt1.executeUpdate(query);
362 
363 				query = "DELETE EN_COURS WHERE NU_INT = '" + jobNo + "'";
364 				System.out.println("MOVE QUERY :" + query);
365 				retValue = stmt1.executeUpdate(query);
366 			}
367 		}
368 	}catch(SQLException ex)
369 	{
370 		ex.printStackTrace();
371 		throw(ex);
372 	}finally
373 	{
374 		poolMgr.freeConnection("sophieConnection",conn1);
375 	}
376 }
377 
378 public static float getInternalPartCost(String jobNo) throws SQLException
379 {
380 	float internalPartCost=0;
381 	//System.out.println("What's going on");
382 	PoolManager poolMgr = PoolManager.getInstance();
383 	Connection conn1 = poolMgr.getConnection("sophieConnection");
384 	Statement stmt1 = conn1.createStatement();
385 	Connection conn2 = poolMgr.getConnection("sophieConnection");
386 	Statement stmt2 = conn2.createStatement();
387 
388 	try
389 	{
390 		String query ="SELECT CODE_FOUR, C_REFER, QTE, C_TVA, C_PRIX_UNI from PIECES WHERE NU_INT = '"+ jobNo + "'";
391 		//System.out.println("Part query : " + query);
392 
393 		ResultSet rs1 = stmt1.executeQuery(query);
394 
395 		while (rs1.next())
396 		{
397 			float qte = 0, price = 0, tax = 0, tot_ttc;
398 			String code_four = "", c_refer = "";
399 			String tmpStr1, tmpTax, tmpPrice;
400 			//stmt2 = conn.createStatement();
401 
402 			if( (tmpStr1=getValue(rs1.getString(1))) !=null)
403 				code_four = tmpStr1;
404 			c_refer=getValue(rs1.getString(2));
405 			if( (tmpStr1=getValue(rs1.getString(3))) !=null)
406 				qte = Float.parseFloat(tmpStr1);
407 			tmpTax=getValue(rs1.getString(4));
408 			tmpPrice=getValue(rs1.getString(5));
409 			//System.out.println("1 Tax and Price " + tmpTax + " " + tmpPrice + " " + c_refer);
410 			if(tmpTax == null || tmpPrice == null)
411 			{
412 			//	System.out.println("Getting from IDEPIECE");
413 				ResultSet rs2 = stmt2.executeQuery("SELECT C_TVA, C_PRIX_UNI from IDEPIECE where C_REFER = '" + c_refer + "' and CODE_FOUR ='" + code_four + "'");
414 				if(rs2.next())
415 				{
416 					if( ((tmpStr1=getValue(rs2.getString(1)))!=null) && tmpTax == null)
417 						tmpTax = tmpStr1;
418 					if( ((tmpStr1=getValue(rs2.getString(2)))!=null) && tmpPrice == null)
419 						tmpPrice = tmpStr1;
420 				}
421 				rs2.close();
422 			}
423 			if(tmpPrice!=null)
424 				price = Float.parseFloat(tmpPrice);
425 
426 			if(tmpTax!=null)
427 			{
428 			//	System.out.println("Getting from TVA");
429 				ResultSet rs2 = stmt2.executeQuery("SELECT T_TVA from TVA where C_TVA = '" + tmpTax + "'");
430 				if(rs2.next() && ((tmpStr1=getValue(rs2.getString(1)))!=null) )
431 					tax = Float.parseFloat(tmpStr1);
432 				rs2.close();
433 			}else
434 				tmpTax ="";
435 
436 			//System.out.println("2 Tax and Price " + tmpTax + " " + tmpPrice + " " + c_refer);
437 
438 			tot_ttc = qte * price * (1 + tax/100);
439 			//System.out.println("Parameters : " + qte + " " + price);
440 			internalPartCost += tot_ttc;
441 
442 			//Statement stmt2 = conn.createStatement();
443 			query = "UPDATE PIECES set TOT_TTC = '" + tot_ttc + "', C_PRIX_UNI = '" + price
444 				+ "', C_TVA = '" + tmpTax + "', EXTERNAL_CONSUMPTION = '2' where CODE_FOUR = '"
445 				+ code_four + "' and C_REFER = '" + c_refer + "' and NU_INT = '" + jobNo + "'";
446 			//System.out.println("Part Cost Query : " + query);
447 			stmt2.executeUpdate(query);
448 			//stmt2.close();
449 		}
450 	}catch(SQLException ex)
451 	{
452 		ex.printStackTrace();
453 	}finally
454 	{
455 		stmt1.close();
456 		stmt2.close();
457 		poolMgr.freeConnection("sophieConnection",conn1);
458 		poolMgr.freeConnection("sophieConnection",conn2);
459 	}
460 	return internalPartCost;
461 }
462 
463 public static float getPartCost(String jobNo, String Mode) throws SQLException
464 {
465 	float internalPartCost=0;
466 	//System.out.println("What's going on");
467 	PoolManager poolMgr = PoolManager.getInstance();
468 	Connection conn1 = poolMgr.getConnection("sophieConnection");
469 	Statement stmt1 = conn1.createStatement();
470 	Connection conn2 = poolMgr.getConnection("sophieConnection");
471 	Statement stmt2 = conn2.createStatement();
472 
473 	try
474 	{
475 		String query ="SELECT CODE_FOUR, C_REFER, QTE, C_TVA, C_PRIX_UNI from PIECES WHERE NU_INT = '"+ jobNo + "' and EXTERNAL_CONSUMPTION = '" + Mode + "'";
476 		//System.out.println("Part query : " + query);
477 
478 		ResultSet rs1 = stmt1.executeQuery(query);
479 
480 		while (rs1.next())
481 		{
482 			float qte = 0, price = 0, tax = 0, tot_ttc;
483 			String code_four = "", c_refer = "";
484 			String tmpStr1, tmpTax, tmpPrice;
485 			//stmt2 = conn.createStatement();
486 
487 			if( (tmpStr1=getValue(rs1.getString(1))) !=null)
488 				code_four = tmpStr1;
489 			c_refer=getValue(rs1.getString(2));
490 			if( (tmpStr1=getValue(rs1.getString(3))) !=null)
491 				qte = Float.parseFloat(tmpStr1);
492 			tmpTax=getValue(rs1.getString(4));
493 			tmpPrice=getValue(rs1.getString(5));
494 			//System.out.println("1 Tax and Price " + tmpTax + " " + tmpPrice + " " + c_refer);
495 			if(tmpTax == null || tmpPrice == null)
496 			{
497 			//	System.out.println("Getting from IDEPIECE");
498 				ResultSet rs2 = stmt2.executeQuery("SELECT C_TVA, C_PRIX_UNI from IDEPIECE where C_REFER = '" + c_refer + "' and CODE_FOUR ='" + code_four + "'");
499 				if(rs2.next())
500 				{
501 					if( ((tmpStr1=getValue(rs2.getString(1)))!=null) && tmpTax == null)
502 						tmpTax = tmpStr1;
503 					if( ((tmpStr1=getValue(rs2.getString(2)))!=null) && tmpPrice == null)
504 						tmpPrice = tmpStr1;
505 				}
506 				rs2.close();
507 			}
508 			if(tmpPrice!=null)
509 				price = Float.parseFloat(tmpPrice);
510 
511 			if(tmpTax!=null)
512 			{
513 			//	System.out.println("Getting from TVA");
514 				ResultSet rs2 = stmt2.executeQuery("SELECT T_TVA from TVA where C_TVA = '" + tmpTax + "'");
515 				if(rs2.next() && ((tmpStr1=getValue(rs2.getString(1)))!=null) )
516 					tax = Float.parseFloat(tmpStr1);
517 				rs2.close();
518 			}else
519 				tmpTax ="";
520 
521 			//System.out.println("2 Tax and Price " + tmpTax + " " + tmpPrice + " " + c_refer);
522 
523 			tot_ttc = qte * price * (1 + tax/100);
524 			//System.out.println("Parameters : " + qte + " " + price);
525 			internalPartCost += tot_ttc;
526 
527 			//Statement stmt2 = conn.createStatement();
528 			query = "UPDATE PIECES set TOT_TTC = '" + tot_ttc + "', C_PRIX_UNI = '" + price
529 				+ "', C_TVA = '" + tmpTax + "' where CODE_FOUR = '"
530 				+ code_four + "' and EXTERNAL_CONSUMPTION = '" + Mode + "' and C_REFER = '" + c_refer + "' and NU_INT = '" + jobNo + "'";
531 			//System.out.println("Part Cost Query : " + query);
532 			stmt2.executeUpdate(query);
533 			//stmt2.close();
534 		}
535 	}catch(SQLException ex)
536 	{
537 		ex.printStackTrace();
538 	}finally
539 	{
540 		stmt1.close();
541 		stmt2.close();
542 		poolMgr.freeConnection("sophieConnection",conn1);
543 		poolMgr.freeConnection("sophieConnection",conn2);
544 	}
545 	return internalPartCost;
546 }
547 
548 private static String getValue(String str)
549 {
550 	if(str!=null && str.trim().length()!=0)
551 		return str.trim();
552 	return null;
553 }
554 
555 }
556