001: /*
002: Copyright (C) 2003 Know Gate S.L. All rights reserved.
003: C/Oņa, 107 1š2 28050 Madrid (Spain)
004:
005: Redistribution and use in source and binary forms, with or without
006: modification, are permitted provided that the following conditions
007: are met:
008:
009: 1. Redistributions of source code must retain the above copyright
010: notice, this list of conditions and the following disclaimer.
011:
012: 2. The end-user documentation included with the redistribution,
013: if any, must include the following acknowledgment:
014: "This product includes software parts from hipergate
015: (http://www.hipergate.org/)."
016: Alternately, this acknowledgment may appear in the software itself,
017: if and wherever such third-party acknowledgments normally appear.
018:
019: 3. The name hipergate must not be used to endorse or promote products
020: derived from this software without prior written permission.
021: Products derived from this software may not be called hipergate,
022: nor may hipergate appear in their name, without prior written
023: permission.
024:
025: This library is distributed in the hope that it will be useful,
026: but WITHOUT ANY WARRANTY; without even the implied warranty of
027: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
028:
029: You should have received a copy of hipergate License with this code;
030: if not, visit http://www.hipergate.org or mail to info@hipergate.org
031: */
032:
033: package com.knowgate.crm;
034:
035: import java.util.HashMap;
036:
037: import java.sql.Connection;
038: import java.sql.SQLException;
039: import java.sql.CallableStatement;
040: import java.sql.Statement;
041: import java.sql.PreparedStatement;
042: import java.sql.ResultSet;
043:
044: import com.knowgate.debug.DebugFile;
045: import com.knowgate.misc.Gadgets;
046: import com.knowgate.jdc.JDCConnection;
047: import com.knowgate.dataobjs.DB;
048: import com.knowgate.dataobjs.DBBind;
049: import com.knowgate.dataobjs.DBSubset;
050: import com.knowgate.dataobjs.DBPersist;
051: import com.knowgate.hipergate.Address;
052: import com.knowgate.hipergate.DBLanguages;
053:
054: /**
055: * <p>Company</p>
056: * <p>Copyright: Copyright (c) KnowGate 2003</p>
057: * @author Sergio Montoro Ten
058: * @version 3.0
059: */
060:
061: public class Company extends DBPersist {
062:
063: /**
064: * Create Empty Company.
065: */
066: public Company() {
067: super (DB.k_companies, "Company");
068: }
069:
070: /**
071: * Create Company and set gu_company field.
072: * Does not load other fields from database.
073: * @param sCompanyId Company GUID
074: */
075: public Company(String sCompanyId) {
076: super (DB.k_companies, "Company");
077:
078: put(DB.gu_company, sCompanyId);
079: }
080:
081: // ----------------------------------------------------------
082:
083: /**
084: * Create Company and load fields from database.
085: * @param oConn JDCConnection
086: * @param sCompanyId Company GUID
087: * @throws SQLException
088: */
089: public Company(JDCConnection oConn, String sCompanyId)
090: throws SQLException {
091: super (DB.k_companies, "Company");
092: load(oConn, sCompanyId);
093: }
094:
095: // ----------------------------------------------------------
096:
097: /**
098: * <P>Add a bank account to this Company</P>
099: * If company is already associated to the given bank account then a foreign key violation SQLException is thrown
100: * @param oConn Database Connection
101: * @throws SQLException
102: * @since 3.0
103: */
104: public boolean addBankAccount(JDCConnection oConn,
105: String sFullBankAccount) throws SQLException {
106: PreparedStatement oStmt = null;
107: boolean bRetVal;
108:
109: try {
110: oStmt = oConn.prepareStatement("INSERT INTO "
111: + DB.k_x_company_bank + " (" + DB.gu_company + ","
112: + DB.nu_bank_acc + "," + DB.gu_workarea
113: + ") VALUES (?,?,?)");
114: oStmt.setString(1, getStringNull(DB.gu_company, null));
115: oStmt.setString(2, sFullBankAccount);
116: oStmt.setString(3, getStringNull(DB.gu_workarea, null));
117: int iAffected = oStmt.executeUpdate();
118: oStmt.close();
119: oStmt = null;
120: bRetVal = (iAffected > 0);
121: } catch (SQLException sqle) {
122: bRetVal = false;
123: try {
124: if (oStmt != null)
125: oStmt.close();
126: } catch (Exception ignore) {
127: }
128: }
129: return bRetVal;
130: } // addBankAccount
131:
132: // ----------------------------------------------------------
133:
134: /**
135: * Get all bank accounts associated with Company
136: * @param oConn JDCConnection
137: * @return DBSubset nu_bank_acc,dt_created,bo_active,tp_account,nm_bank,tx_addr,nm_cardholder,nu_card,tp_card,tx_expire,nu_pin,nu_cvv2,im_credit_limit,de_bank_acc
138: * @throws SQLException
139: * @throws IllegalStateException if gu_company or gu_workarea are not set
140: * @since 3.0
141: */
142: public DBSubset getAllBankAccounts(JDCConnection oConn)
143: throws SQLException, IllegalStateException {
144: if (isNull(DB.gu_company))
145: throw new IllegalStateException(
146: "Company.getAllBankAccounts() gu_company property is not set");
147: if (isNull(DB.gu_workarea))
148: throw new IllegalStateException(
149: "Company.getAllBankAccounts() gu_workarea property is not set");
150:
151: DBSubset oAccs = new DBSubset(DB.k_bank_accounts,
152: DB.nu_bank_acc + "," + DB.dt_created + ","
153: + DB.bo_active + "," + DB.tp_account + ","
154: + DB.nm_bank + "," + DB.tx_addr + ","
155: + DB.nm_cardholder + "," + DB.nu_card + ","
156: + DB.tp_card + "," + DB.tx_expire + ","
157: + DB.nu_pin + "," + DB.nu_cvv2 + ","
158: + DB.im_credit_limit + "," + DB.de_bank_acc,
159: DB.gu_workarea + "=? AND " + DB.nu_bank_acc
160: + " IN (SELECT " + DB.nu_bank_acc + " FROM "
161: + DB.k_x_company_bank + " WHERE "
162: + DB.gu_workarea + "=? AND " + DB.gu_company
163: + "=?)", 10);
164:
165: oAccs.load(oConn, new Object[] { get(DB.gu_workarea),
166: get(DB.gu_workarea), get(DB.gu_company) });
167: return oAccs;
168: } // getAllBankAccounts
169:
170: // ----------------------------------------------------------
171:
172: /**
173: * Get active bank accounts for this Company
174: * @param oConn JDCConnection
175: * @return DBSubset nu_bank_acc,dt_created,tp_account,nm_bank,tx_addr,nm_cardholder,nu_card,tp_card,tx_expire,nu_pin,nu_cvv2,im_credit_limit,de_bank_acc
176: * @throws SQLException
177: * @throws IllegalStateException if gu_company or gu_workarea are not set
178: * @since 3.0
179: */
180: public DBSubset getActiveBankAccounts(JDCConnection oConn)
181: throws SQLException, IllegalStateException {
182: if (isNull(DB.gu_company))
183: throw new IllegalStateException(
184: "Company.getActiveBankAccounts() gu_company property is not set");
185: if (isNull(DB.gu_workarea))
186: throw new IllegalStateException(
187: "Company.getActiveBankAccounts() gu_workarea property is not set");
188:
189: DBSubset oAccs = new DBSubset(DB.k_bank_accounts,
190: DB.nu_bank_acc + "," + DB.dt_created + ","
191: + DB.tp_account + "," + DB.nm_bank + ","
192: + DB.tx_addr + "," + DB.nm_cardholder + ","
193: + DB.nu_card + "," + DB.tp_card + ","
194: + DB.tx_expire + "," + DB.nu_pin + ","
195: + DB.nu_cvv2 + "," + DB.im_credit_limit + ","
196: + DB.de_bank_acc, DB.gu_workarea + "=? AND "
197: + DB.bo_active + "<>0 AND " + DB.nu_bank_acc
198: + " IN (SELECT " + DB.nu_bank_acc + " FROM "
199: + DB.k_x_company_bank + " WHERE "
200: + DB.gu_workarea + "=? AND " + DB.gu_company
201: + "=?)", 10);
202:
203: oAccs.load(oConn, new Object[] { get(DB.gu_workarea),
204: get(DB.gu_workarea), get(DB.gu_company) });
205: return oAccs;
206: } // getActiveBankAccounts
207:
208: // ----------------------------------------------------------
209:
210: /**
211: * Get unactive bank accounts for this Company
212: * @param oConn JDCConnection
213: * @return DBSubset nu_bank_acc,dt_created,tp_account,nm_bank,tx_addr,nm_cardholder,nu_card,tp_card,tx_expire,nu_pin,nu_cvv2,im_credit_limit,de_bank_acc
214: * @throws SQLException
215: * @throws IllegalStateException if gu_company or gu_workarea are not set
216: * @since 3.0
217: */
218: public DBSubset getUnactiveBankAccounts(JDCConnection oConn)
219: throws SQLException, IllegalStateException {
220: if (isNull(DB.gu_company))
221: throw new IllegalStateException(
222: "Company.getUnactiveBankAccounts() gu_company property is not set");
223: if (isNull(DB.gu_workarea))
224: throw new IllegalStateException(
225: "Company.getUnactiveBankAccounts() gu_workarea property is not set");
226:
227: DBSubset oAccs = new DBSubset(DB.k_bank_accounts,
228: DB.nu_bank_acc + "," + DB.dt_created + ","
229: + DB.tp_account + "," + DB.nm_bank + ","
230: + DB.tx_addr + "," + DB.nm_cardholder + ","
231: + DB.nu_card + "," + DB.tp_card + ","
232: + DB.tx_expire + "," + DB.nu_pin + ","
233: + DB.nu_cvv2 + "," + DB.im_credit_limit + ","
234: + DB.de_bank_acc, DB.gu_workarea + "=? AND "
235: + DB.bo_active + "=0 AND " + DB.nu_bank_acc
236: + " IN (SELECT " + DB.nu_bank_acc + " FROM "
237: + DB.k_x_company_bank + " WHERE "
238: + DB.gu_workarea + "=? AND " + DB.gu_company
239: + "=?)", 10);
240:
241: oAccs.load(oConn, new Object[] { get(DB.gu_workarea),
242: get(DB.gu_workarea), get(DB.gu_company) });
243: return oAccs;
244: } // getUnactiveBankAccounts
245:
246: // ----------------------------------------------------------
247:
248: /**
249: * Store Company
250: * Automatically generates gu_company GUID and dt_modified DATE if not explicitly set.
251: * @param oConn Database Connection
252: * @throws SQLException
253: */
254: public boolean store(JDCConnection oConn) throws SQLException {
255: java.sql.Timestamp dtNow = new java.sql.Timestamp(DBBind
256: .getTime());
257:
258: if (!AllVals.containsKey(DB.gu_company))
259: put(DB.gu_company, Gadgets.generateUUID());
260:
261: replace(DB.dt_modified, dtNow);
262:
263: return super .store(oConn);
264: } // store
265:
266: // ----------------------------------------------------------
267:
268: /**
269: * Delete Company
270: * @param oConn Database Connection
271: * @throws SQLException
272: */
273: public boolean delete(JDCConnection oConn) throws SQLException {
274: return Company.delete(oConn, getString(DB.gu_company));
275: }
276:
277: // ----------------------------------------------------------
278:
279: /**
280: * <p>Find out whether or not a company exists at database</p>
281: * Look up company by GUID or by legal name and work area.
282: * @param oConn database connection
283: * @return <b>true</b> if a company with such GUID or legal name+work area is found.
284: * @throws SQLException
285: */
286:
287: public boolean exists(JDCConnection oConn) throws SQLException {
288: PreparedStatement oStmt = oConn
289: .prepareStatement("SELECT NULL FROM " + DB.k_companies
290: + " WHERE " + DB.gu_company + "=? OR ("
291: + DB.nm_legal + "=? AND " + DB.gu_workarea
292: + "=?)", ResultSet.TYPE_FORWARD_ONLY,
293: ResultSet.CONCUR_READ_ONLY);
294: oStmt.setString(1, getStringNull(DB.gu_company, null));
295: oStmt.setString(2, getStringNull(DB.nm_legal, null));
296: oStmt.setString(3, getStringNull(DB.gu_workarea, null));
297: ResultSet oRSet = oStmt.executeQuery();
298: boolean bExists = oRSet.next();
299: oRSet.close();
300: oStmt.close();
301: return bExists;
302: } // exists
303:
304: // ----------------------------------------------------------
305:
306: /**
307: * <P>Add an Address to this Company</P>
308: * If contact is already associated to the given address a foreign key violation
309: * SQLExceception is raised.
310: * @param oConn Database Connection
311: * @throws SQLException
312: */
313: public boolean addAddress(JDCConnection oConn, String sAddrGUID)
314: throws SQLException {
315: PreparedStatement oStmt = null;
316: boolean bRetVal;
317:
318: try {
319: oStmt = oConn.prepareStatement("INSERT INTO "
320: + DB.k_x_company_addr + " (" + DB.gu_company + ","
321: + DB.gu_address + ") VALUES (?,?)");
322: oStmt.setString(1, getStringNull(DB.gu_company, null));
323: oStmt.setString(2, sAddrGUID);
324: int iAffected = oStmt.executeUpdate();
325: oStmt.close();
326: oStmt = null;
327: bRetVal = (iAffected > 0);
328: } catch (SQLException sqle) {
329: bRetVal = false;
330: try {
331: if (oStmt != null)
332: oStmt.close();
333: } catch (Exception ignore) {
334: }
335: }
336: return bRetVal;
337: } // addAddress
338:
339: // ----------------------------------------------------------
340:
341: /**
342: * Get address by location type
343: * @param oConn JDCConnection
344: * @param sTpLocation String Value for column tp_location from k_addresses table
345: * @return Address or <b>null</b> is no address with such location type was found
346: * @throws SQLException
347: * @throws IllegalStateException if gu_company property is not set
348: * @since 3.0
349: */
350: public Address getAddress(JDCConnection oConn, String sTpLocation)
351: throws SQLException, IllegalStateException {
352:
353: Address oRetAdr;
354:
355: if (isNull(DB.gu_company))
356: throw new IllegalStateException(
357: "Company.getAddress([Connection]," + sTpLocation
358: + ") gu_company property is not set");
359:
360: if (DebugFile.trace) {
361: DebugFile.writeln("Begin Company.getAddress([Connection],"
362: + sTpLocation + ")");
363: DebugFile.incIdent();
364: }
365:
366: PreparedStatement oStmt = oConn.prepareStatement("SELECT x."
367: + DB.gu_address + " FROM " + DB.k_x_company_addr
368: + " x," + DB.k_addresses + " a WHERE " + "x."
369: + DB.gu_address + "=a." + DB.gu_address + " AND x."
370: + DB.gu_company + "=?" + " AND a." + DB.tp_location
371: + "=?", ResultSet.TYPE_FORWARD_ONLY,
372: ResultSet.CONCUR_READ_ONLY);
373: oStmt.setString(1, getString(DB.gu_company));
374: oStmt.setString(2, sTpLocation);
375: ResultSet oRSet = oStmt.executeQuery();
376: if (oRSet.next())
377: oRetAdr = new Address(oConn, oRSet.getString(1));
378: else
379: oRetAdr = null;
380: oRSet.close();
381: oStmt.close();
382:
383: if (DebugFile.trace) {
384: DebugFile.decIdent();
385: DebugFile.writeln("End Company.getAddress()");
386: }
387: return oRetAdr;
388: } // getAddress
389:
390: // ----------------------------------------------------------
391:
392: /**
393: * <p>Get Company Addresses</p>
394: * @param oConn Database Connection
395: * @return A DBSubset with all columns from k_addresses for Company
396: * @throws SQLException
397: * @throws IllegalStateException if gu_company property is not set
398: */
399: public DBSubset getAddresses(JDCConnection oConn)
400: throws SQLException, IllegalStateException {
401:
402: if (isNull(DB.gu_company))
403: throw new IllegalStateException(
404: "Company.getAddresses() gu_company property is not set");
405:
406: if (DebugFile.trace) {
407: DebugFile
408: .writeln("Begin Company.getAddresses([Connection])");
409: DebugFile.incIdent();
410: }
411:
412: Address oAddr = new Address();
413:
414: DBSubset oAddrs = new DBSubset(DB.k_addresses, oAddr.getTable(
415: oConn).getColumnsStr(), DB.gu_address + " IN (SELECT "
416: + DB.gu_address + " FROM " + DB.k_x_company_addr
417: + " WHERE " + DB.gu_company + "=?)", 10);
418: int iAddrs = oAddrs.load(oConn,
419: new Object[] { getString(DB.gu_company) });
420:
421: oAddr = null;
422:
423: if (DebugFile.trace) {
424: DebugFile.decIdent();
425: DebugFile.writeln("End Company.getAddresses() : "
426: + String.valueOf(iAddrs));
427: }
428:
429: return oAddrs;
430: } // getAddresses
431:
432: // ----------------------------------------------------------
433:
434: // **********************************************************
435: // Static Methods
436:
437: /**
438: * <p>Delete Company.</p>
439: * Delete all associated contacts and call k_sp_del_company stored procedure.<br>
440: * If k_orders table exists, then Orders for this Company are deleted.<br>
441: * If k_projects table exists, then Projects for this Company are deleted.<br>
442: * @param oConn Database Connection
443: * @param sCompanyGUID Company GUID
444: * @throws SQLException
445: */
446: public static boolean delete(JDCConnection oConn,
447: String sCompanyGUID) throws SQLException {
448: boolean bRetVal;
449: Statement oStmt;
450:
451: if (DebugFile.trace) {
452: DebugFile.writeln("Begin Company.delete([Connection], "
453: + sCompanyGUID + ")");
454: DebugFile.incIdent();
455: }
456:
457: /* Desasociar los e-mails */
458: if (DBBind.exists(oConn, DB.k_inet_addrs, "U")) {
459: oStmt = oConn.createStatement();
460:
461: if (DebugFile.trace)
462: DebugFile.writeln("UPDATE " + DB.k_inet_addrs + " SET "
463: + DB.gu_company + "=NULL WHERE "
464: + DB.gu_company + "='" + sCompanyGUID + "'");
465:
466: oStmt.executeUpdate("UPDATE " + DB.k_inet_addrs + " SET "
467: + DB.gu_company + "=NULL WHERE " + DB.gu_company
468: + "='" + sCompanyGUID + "'");
469:
470: oStmt.close();
471: }
472:
473: if (DBBind.exists(oConn, DB.k_projects, "U")) {
474: DBSubset oProjs = new DBSubset(DB.k_projects,
475: DB.gu_project, DB.gu_company + "='" + sCompanyGUID
476: + "'", 10);
477:
478: int iProjs = oProjs.load(oConn);
479:
480: for (int p = 0; p < iProjs; p++)
481: com.knowgate.projtrack.Project.delete(oConn, oProjs
482: .getString(0, p));
483: }
484:
485: if (DBBind.exists(oConn, DB.k_orders, "U")) {
486: DBSubset oOrders = new DBSubset(DB.k_orders, DB.gu_order,
487: DB.gu_company + "='" + sCompanyGUID + "'", 1000);
488:
489: int iOrders = oOrders.load(oConn);
490:
491: for (int o = 0; o < iOrders; o++)
492: com.knowgate.hipergate.Order.delete(oConn, oOrders
493: .getString(0, o));
494: } // fi (exists(DB.k_orders))
495:
496: DBSubset oContacts = new DBSubset(DB.k_contacts, DB.gu_contact,
497: DB.gu_company + "='" + sCompanyGUID + "'", 1000);
498: int iContacts = oContacts.load(oConn);
499:
500: for (int c = 0; c < iContacts; c++)
501: Contact.delete(oConn, oContacts.getString(0, c));
502:
503: oContacts = null;
504:
505: if (oConn.getDataBaseProduct() == JDCConnection.DBMS_POSTGRESQL) {
506: oStmt = oConn.createStatement();
507: if (DebugFile.trace)
508: DebugFile
509: .writeln("Statement.executeQuery(SELECT k_sp_del_company ('"
510: + sCompanyGUID + "')");
511: oStmt.executeQuery("SELECT k_sp_del_company ('"
512: + sCompanyGUID + "')");
513: oStmt.close();
514: bRetVal = true;
515: } else {
516:
517: if (DebugFile.trace)
518: DebugFile
519: .writeln("Conenction.prepareCall({call k_sp_del_company ('"
520: + sCompanyGUID + "')}");
521:
522: CallableStatement oCall = oConn
523: .prepareCall("{call k_sp_del_company ('"
524: + sCompanyGUID + "')}");
525: bRetVal = oCall.execute();
526: oCall.close();
527: }
528:
529: if (DebugFile.trace) {
530: DebugFile.decIdent();
531: DebugFile.writeln("End Company.delete() : "
532: + String.valueOf(bRetVal));
533: }
534:
535: return bRetVal;
536: } // delete
537:
538: /**
539: * <p>Add a Company Sector lookup value</a>
540: * @param oConn Connection
541: * @param sGuWorkArea String GUID of WorkArea
542: * @param sDeTitle String Sector Internal Identifier
543: * @param oTranslations HashMap with one entry for each language
544: * @return boolean <b>true</b> if new sector was added, <b>false</b> if it already existed
545: * @throws SQLException
546: * @since 3.0
547: */
548: public static boolean addLookupSector(Connection oConn,
549: String sGuWorkArea, String sIdSector, HashMap oTranslations)
550: throws SQLException {
551: return DBLanguages.addLookup(oConn, DB.k_companies_lookup,
552: sGuWorkArea, DB.id_sector, sIdSector, oTranslations);
553: }
554:
555: /**
556: * <p>Add a Company Type lookup value</a>
557: * @param oConn Connection
558: * @param sGuWorkArea String GUID of WorkArea
559: * @param sDeTitle String Company Type Internal Identifier
560: * @param oTranslations HashMap with one entry for each language
561: * @return boolean <b>true</b> if new sector was added, <b>false</b> if it already existed
562: * @throws SQLException
563: * @since 3.0
564: */
565: public static boolean addLookupCompanyType(Connection oConn,
566: String sGuWorkArea, String sIdType, HashMap oTranslations)
567: throws SQLException {
568: return DBLanguages.addLookup(oConn, DB.k_companies_lookup,
569: sGuWorkArea, DB.tp_company, sIdType, oTranslations);
570: }
571:
572: /**
573: * <p>Get company GUID given its legal name</p>
574: * @param oConn Connection
575: * @param sLegalName String Legal name of sought Company
576: * @param sWorkArea String GUID of WorkArea where to search
577: * @return String Company GUID or <b>null</b> if no company with such legal name was found at given work area
578: * @throws SQLException
579: * @since 3.0
580: */
581: public static String getIdFromName(Connection oConn,
582: String sLegalName, String sWorkArea) throws SQLException {
583: String sRetVal;
584: PreparedStatement oStmt = oConn
585: .prepareStatement("SELECT " + DB.gu_company + " FROM "
586: + DB.k_companies + " WHERE " + DB.nm_legal
587: + "=? AND " + DB.gu_workarea + "=?",
588: ResultSet.TYPE_FORWARD_ONLY,
589: ResultSet.CONCUR_READ_ONLY);
590: oStmt.setString(1, sLegalName);
591: oStmt.setString(2, sWorkArea);
592: ResultSet oRSet = oStmt.executeQuery();
593: if (oRSet.next())
594: sRetVal = oRSet.getString(1);
595: else
596: sRetVal = null;
597: oRSet.close();
598: oStmt.close();
599: return sRetVal;
600: } // getIdFromName
601:
602: /**
603: * <p>Get company GUID given its external reference</p>
604: * @param oConn Connection
605: * @param sLegalName String External reference of sought Company
606: * @param sWorkArea String GUID of WorkArea where to search
607: * @return String Company GUID or <b>null</b> if no company with such reference was found at given work area
608: * @throws SQLException
609: * @since 3.0
610: */
611: public static String getIdFromRef(Connection oConn,
612: String sReference, String sWorkArea) throws SQLException {
613: String sRetVal;
614: PreparedStatement oStmt = oConn
615: .prepareStatement("SELECT " + DB.gu_company + " FROM "
616: + DB.k_companies + " WHERE " + DB.id_ref
617: + "=? AND " + DB.gu_workarea + "=?",
618: ResultSet.TYPE_FORWARD_ONLY,
619: ResultSet.CONCUR_READ_ONLY);
620: oStmt.setString(1, sReference);
621: oStmt.setString(2, sWorkArea);
622: ResultSet oRSet = oStmt.executeQuery();
623: if (oRSet.next())
624: sRetVal = oRSet.getString(1);
625: else
626: sRetVal = null;
627: oRSet.close();
628: oStmt.close();
629: return sRetVal;
630: } // getIdFromRef
631:
632: /**
633: * <p>Get company GUID given its legal number</p>
634: * @param oConn Connection
635: * @param sLegalName String Legal Number of sought Company
636: * @param sWorkArea String GUID of WorkArea where to search
637: * @return String Company GUID or <b>null</b> if no company with such legal number was found at given work area
638: * @throws SQLException
639: * @since 3.0
640: */
641: public static String getIdFromLegalNum(Connection oConn,
642: String sLegalId, String sWorkArea) throws SQLException {
643: String sRetVal;
644: PreparedStatement oStmt = oConn
645: .prepareStatement("SELECT " + DB.gu_company + " FROM "
646: + DB.k_companies + " WHERE " + DB.id_legal
647: + "=? AND " + DB.gu_workarea + "=?",
648: ResultSet.TYPE_FORWARD_ONLY,
649: ResultSet.CONCUR_READ_ONLY);
650: oStmt.setString(1, sLegalId);
651: oStmt.setString(2, sWorkArea);
652: ResultSet oRSet = oStmt.executeQuery();
653: if (oRSet.next())
654: sRetVal = oRSet.getString(1);
655: else
656: sRetVal = null;
657: oRSet.close();
658: oStmt.close();
659: return sRetVal;
660: } // getIdFromLegalNum
661:
662: // **********************************************************
663: // Public Constants
664:
665: public static final short ClassId = 91;
666: }
|