Java API By Example, From Geeks To Geeks.

1 /****************************************************************************** 2  * The contents of this file are subject to the Compiere License Version 1.1 3  * ("License"); You may not use this file except in compliance with the License 4  * You may obtain a copy of the License at http://www.compiere.org/license.html 5  * Software distributed under the License is distributed on an "AS IS" basis, 6  * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for 7  * the specific language governing rights and limitations under the License. 8  * The Original Code is Compiere ERP & CRM Business Solution 9  * The Initial Developer of the Original Code is Jorg Janke and ComPiere, Inc. 10  * Portions created by Jorg Janke are Copyright (C) 1999-2001 Jorg Janke, parts 11  * created by ComPiere are Copyright (C) ComPiere, Inc.; All Rights Reserved. 12  * Contributor(s): ______________________________________. 13  *****************************************************************************/ 14 package org.compiere.util; 15 16 import java.math.*; 17 import java.util.*; 18 import java.text.*; 19 import java.security.*; 20 import javax.swing.*; 21 22 import java.sql.*; 23 import javax.sql.*; 24 import oracle.jdbc.*; 25 26 import org.compiere.Compiere; 27 import org.compiere.db.*; 28 import org.compiere.model.MRole; 29 30 /** 31  * General Database Interface 32  * 33  * @author Jorg Janke 34  * @version $Id: DB.java,v 1.34 2003/11/06 07:09:09 jjanke Exp $ 35  */ 36 public final class DB 37 { 38     /** Connection Descriptor */ 39     private static CConnection s_cc = null; 40     /** Connection Cache r/o */ 41     private static Connection[] s_connections = null; 42     /** Connection Cache Size */ 43     private static int s_conCacheSize = 2; // client 44 /** Connection counter */ 45     private static int s_conCount = 0; 46     /** Connection r/w */ 47     private static Connection s_connectionRW = null; 48     /** Logger */ 49     private static Logger s_log = Logger.getCLogger (DB.class); 50 51     /** 52      * Client Login. 53      * <p> 54      * - Get Connection 55      * - Compare User info 56      * <p> 57      * Sets Conext with login info 58      * 59      * @param ctx context 60      * @param cc connection 61      * @param app_user user 62      * @param app_pwd pwd 63      * @param force ignore pwd 64      * @return Array of Role KeyNamePair or null if error 65      * The error (NoDatabase, UserPwdError, DBLogin) is saved in the log 66      */ 67     protected static KeyNamePair[] login (Properties ctx, 68         CConnection cc, 69         String app_user, String app_pwd, boolean force) 70     { 71         if (ctx == null) 72             throw new IllegalArgumentException ("DB.login - required parameter missing"); 73         // Establish connection 74 closeTarget(); 75         setDBTarget(cc); 76         Env.setContext(ctx, "#Host", s_cc.getAppsHost()); 77         Env.setContext(ctx, "#Database", s_cc.getDbName()); 78         if (getConnectionRO() == null) 79         { 80             Log.saveError("NoDatabase", ""); 81             return null; 82         } 83         if (app_pwd == null) 84             return null; 85         // 86 return loginDB (ctx, app_user, app_pwd, force); 87     } // login 88 89     /** 90      * Client Login. 91      * <p> 92      * Compare User Info 93      * <p> 94      * Sets Conext with login info 95      * 96      * @param ctx context 97      * @param app_user Principal 98      * @return role array or null if in error. 99      * The error (NoDatabase, UserPwdError, DBLogin) is saved in the log 100      */ 101     public static KeyNamePair[] login (Properties ctx, Principal app_user) 102     { 103         if (app_user == null) 104             return null; 105         // login w/o password as previously authorized 106 return loginDB (ctx, app_user.getName(), null, false); 107     } // app_user 108 109     /** 110      * Client Login. 111      * <p> 112      * Compare User Info 113      * <p> 114      * Sets Conext with login info 115      * 116      * @param ctx context 117      * @param app_user user id 118      * @param app_pwd password 119      * @return role array or null if in error. 120      * The error (NoDatabase, UserPwdError, DBLogin) is saved in the log 121      */ 122     public static KeyNamePair[] login (Properties ctx, 123         String app_user, String app_pwd) 124     { 125         if (app_pwd == null) 126             return null; 127         return loginDB (ctx, app_user, app_pwd, false); 128     } // login 129 130     /** 131      * Actual DB login procedure. 132      * 133      * @param ctx context 134      * @param app_user user 135      * @param app_pwd pwd 136      * @param force ignore pwd 137      * @return role array or null if in error. 138      * The error (NoDatabase, UserPwdError, DBLogin) is saved in the log 139      */ 140     private static KeyNamePair[] loginDB (Properties ctx, 141         String app_user, String app_pwd, boolean force) 142     { 143         s_log.info("login - User=" + app_user); 144 145         if (ctx == null) 146             throw new IllegalArgumentException ("DB.login - required parameter missing"); 147         if (app_user == null) 148             return null; 149 150         KeyNamePair[] retValue = null; 151         ArrayList list = new ArrayList(); 152         // 153 String sql = "SELECT AD_User.AD_User_ID, AD_User.Description," 154             + " AD_Role.AD_Role_ID, AD_Role.Name " 155             + "FROM AD_User, AD_User_Roles, AD_Role " 156             + "WHERE AD_User.AD_User_ID=AD_User_Roles.AD_User_ID" 157             + " AND AD_User_Roles.AD_Role_ID=AD_Role.AD_Role_ID" 158             + " AND AD_User.Name=?" // #1 159 + " AND AD_User.IsActive='Y' AND AD_Role.IsActive='Y' AND AD_User_Roles.IsActive='Y'"; 160         if (app_pwd != null) 161             sql += " AND (AD_User.Password=? OR AD_User.Password=?)"; // #2/3 162 try 163         { 164             PreparedStatement pstmt = prepareStatement(sql); 165             pstmt.setString(1, app_user); 166             if (app_pwd != null) 167             { 168                 pstmt.setString(2, app_pwd); 169                 pstmt.setString(3, Secure.getDigest(app_pwd)); 170             } 171             // execute a query 172 ResultSet rs = pstmt.executeQuery(); 173 174             if (!rs.next()) // no record found 175 if (force) 176                 { 177                     Env.setContext(ctx, "#AD_User_Name", "System"); 178                     Env.setContext(ctx, "#AD_User_ID", "0"); 179                     Env.setContext(ctx, "#AD_User_Description", "System Forced Login"); 180                     Env.setContext(ctx, "#User_Level", "S "); // Format 'SCO' 181 Env.setContext(ctx, "#User_Client", "0"); // Format c1, c2, ... 182 Env.setContext(ctx, "#User_Org", "0"); // Format o1, o2, ... 183 rs.close(); 184                     pstmt.close(); 185                     retValue = new KeyNamePair[] {new KeyNamePair(0, "System Administrator")}; 186                     return retValue; 187                 } 188                 else 189                 { 190                     rs.close(); 191                     pstmt.close(); 192                     Log.saveError("UserPwdError", app_user, false); 193                     return null; 194                 } 195 196             Env.setContext(ctx, "#AD_User_Name", app_user); 197             Env.setContext(ctx, "#AD_User_ID", rs.getInt("AD_User_ID")); 198             Env.setContext(ctx, "#SalesRep_ID", rs.getInt("AD_User_ID")); 199             Env.setContext(ctx, "#AD_User_Description", rs.getString("Description")); 200             // 201 Ini.setProperty(Ini.P_UID, app_user); 202             if (Ini.getPropertyBool(Ini.P_STORE_PWD)) 203                 Ini.setProperty(Ini.P_PWD, app_pwd); 204 205             do // read all roles 206 { 207                 int AD_Role_ID = rs.getInt("AD_Role_ID"); 208                 String Name = rs.getString("Name"); 209                 KeyNamePair p = new KeyNamePair(AD_Role_ID, Name); 210                 list.add(p); 211             } 212             while (rs.next()); 213 214             rs.close(); 215             pstmt.close(); 216         } 217         catch (SQLException ex) 218         { 219             s_log.error("login", ex); 220             Log.saveError("DBLogin", ex.getLocalizedMessage()); 221             return null; 222         } 223 224         // Change via SQL detection comes here 225 Env.setContext(ctx, "#User_SecurityID", "85263"); 226         // 227 retValue = new KeyNamePair[list.size()]; 228         list.toArray(retValue); 229         s_log.debug("# roles = " + retValue.length); 230         return retValue; 231     } // login 232 233     /** 234      * Load Clients. 235      * <p> 236      * Sets Role info in context and loads its clients 237      * 238      * @param ctx context 239      * @param role role information 240      * @return list of valid client KeyNodePairs or null if in error 241      */ 242     public static KeyNamePair[] loadClients (Properties ctx, KeyNamePair role) 243     { 244         if (ctx == null || role == null) 245             throw new IllegalArgumentException ("DB.loadClients - required parameter missing"); 246 247         s_log.debug("loadClients - Role=" + role.toString()); 248 249         ArrayList list = new ArrayList(); 250         // get Role details 251 try 252         { 253             String sql = "SELECT DISTINCT r.UserLevel,r.ClientList,r.OrgList," 254                 + " r.C_Currency_ID,r.AmtApproval, oa.AD_Client_ID,c.Name " 255                 + "FROM AD_Role r" 256                 + " INNER JOIN AD_Role_OrgAccess oa ON (r.AD_Role_ID=oa.AD_Role_ID)" 257                 + " INNER JOIN AD_Client c ON (oa.AD_Client_ID=c.AD_Client_ID) " 258                 + "WHERE r.AD_Role_ID=?" // #1 259 + " AND r.IsActive='Y' AND c.IsActive='Y'"; 260 261             PreparedStatement pstmt = prepareStatement(sql); 262             pstmt.setInt(1, role.getKey()); 263             ResultSet rs = pstmt.executeQuery(); 264 265             if (!rs.next()) 266             { 267                 rs.close(); 268                 pstmt.close(); 269                 s_log.error("loadClients - No Clients for Role=" + role.getKey()); 270                 return null; 271             } 272 273             // Role Info 274 Env.setContext(ctx, "#AD_Role_ID", role.getKey()); 275             Env.setContext(ctx, "#AD_Role_Name", role.getName()); 276             Ini.setProperty(Ini.P_ROLE, role.getName()); 277 278             // User Level 279 Env.setContext(ctx, "#User_Level", rs.getString(1)); // Format 'SCO' 280 // ClientList 281 Env.setContext(ctx, "#User_Client", rs.getString(2)); // Format c1, c2, ... 282 // OrgList 283 Env.setContext(ctx, "#User_Org", rs.getString(3)); // Format o1, o2, ... 284 // Approval Currency / Amount 285 Env.setContext(ctx, "#Approval_C_Currency_ID", rs.getInt(4)); 286             BigDecimal approval = rs.getBigDecimal(5); 287             String approvalStr = "0"; 288             if (approval != null) 289                 approvalStr = approval.toString(); 290             Env.setContext(ctx, "#Approval_Amt", approvalStr); 291 292 293             // load Clients 294 do 295             { 296                 int AD_Client_ID = rs.getInt(6); 297                 String Name = rs.getString(7); 298                 KeyNamePair p = new KeyNamePair(AD_Client_ID, Name); 299                 list.add(p); 300             } 301             while (rs.next()); 302 303             rs.close(); 304             pstmt.close(); 305         } 306         catch (SQLException ex) 307         { 308             s_log.error("loadClients", ex); 309             return null; 310         } 311         // 312 KeyNamePair[] retValue = new KeyNamePair[list.size()]; 313         list.toArray(retValue); 314         s_log.debug("# clients = " + retValue.length); 315         return retValue; 316     } // loadClients 317 318     /** 319      * Load Organizations. 320      * <p> 321      * Sets Client info in context and loads its organization, the role has access to 322      * 323      * @param ctx context 324      * @param client client information 325      * @return list of valid Org KeyNodePairs or null if in error 326      */ 327     public static KeyNamePair[] loadOrgs (Properties ctx, KeyNamePair client) 328     { 329         if (ctx == null || client == null) 330             throw new IllegalArgumentException ("DB.loadOrgs - required parameter missing"); 331 332         s_log.debug("loadOrgs - Client=" + client.toString()); 333 334         if (Env.getContext(ctx,"#AD_Role_ID").length() == 0) 335             throw new UnsupportedOperationException ("DB.loadOrgs - Missing Comtext #AD_Role_ID"); 336 337         ArrayList list = new ArrayList(); 338         int AD_Role_ID = Env.getContextAsInt(ctx,"#AD_Role_ID"); 339         s_log.debug("AD_Role_ID=" + AD_Role_ID); 340         //Begin e-evolution vpj-cd 05/09/2003 ************* 341 int AD_User_ID = Env.getContextAsInt(ctx,"#AD_User_ID"); 342         s_log.debug("AD_User_ID=" + AD_User_ID); 343         //end e-evolution vpj-cd 05/09/2003 ************* 344 345         // get Client details for role 346 try 347         { 348             String sql = "SELECT c.Value,c.SMTPHost,c.IsMultiLingualDocument,c.AD_Language," // 1..4 349 + " o.AD_Org_ID,o.Name " // 5..6 350 + "FROM AD_Client c" 351                 + " INNER JOIN AD_Org o ON (o.AD_Client_ID=c.AD_Client_ID) " 352                 + "WHERE o.AD_Client_ID=?" // #1 353 + " AND o.IsSummary='N' AND o.IsActive='Y'" 354                 + " AND o.AD_Org_ID IN " 355                 //Begin e-evolution vpj-cd 05/09/2003 ************* 356 //+ "(SELECT AD_Org_ID FROM AD_Role_OrgAccess WHERE AD_Role_ID=?)"; // #2 357 + "(SELECT AD_Org_ID FROM AD_Role_OrgAccess WHERE AD_Role_ID=?)" // #2 358 + " AND (o.AD_Org_ID IN (SELECT AD_Org_ID FROM AD_User WHERE AD_User_ID=?) OR 0 IN (SELECT AD_Org_ID FROM AD_User WHERE AD_User_ID=?)) "; // #3 , #4 359 //end e-evolution vpj-cd 05/09/2003 ************* 360 361             PreparedStatement pstmt = prepareStatement(sql); 362             pstmt.setInt(1, client.getKey()); 363             pstmt.setInt(2, AD_Role_ID); 364             //Begin e-evolution vpj-cd 05/09/2003 ************* 365 pstmt.setInt(3, AD_User_ID); 366             pstmt.setInt(4, AD_User_ID); 367             //end e-evolution vpj-cd 05/09/2003 ************* 368 ResultSet rs = pstmt.executeQuery(); 369 370             if (!rs.next()) 371             { 372                 rs.close(); 373                 pstmt.close(); 374                 s_log.error("loadOrgs - No Org for Client=" + client.getKey()); 375                 return null; 376             } 377 378             // Client Info 379 Env.setContext(ctx, "#AD_Client_ID", client.getKey()); 380             Env.setContext(ctx, "#AD_Client_Name", client.getName()); 381             Ini.setProperty(Ini.P_CLIENT, client.getName()); 382             // 383 Env.setContext(ctx, "#Client_Value", rs.getString(1)); 384             Env.setContext(ctx, "#Client_SMTP", rs.getString(2)); 385             Env.setContext(ctx, "#IsMultiLingualDocument", rs.getString(3)); 386 387             // load Orgs 388 do 389             { 390                 int AD_Org_ID = rs.getInt(5); 391                 String Name = rs.getString(6); 392                 KeyNamePair p = new KeyNamePair(AD_Org_ID, Name); 393                 list.add(p); 394             } 395             while (rs.next()); 396 397             rs.close(); 398             pstmt.close(); 399         } 400         catch (SQLException ex) 401         { 402             s_log.error("loadOrgs", ex); 403             return null; 404         } 405         // 406 KeyNamePair[] retValue = new KeyNamePair[list.size()]; 407         list.toArray(retValue); 408         s_log.debug("# orgs = " + retValue.length); 409         return retValue; 410     } // loadOrgs 411 412     /** 413      * Load Preferences into Context for selected client. 414      * <p> 415      * Sets Org info in context and loads relevant field from 416      * - AD_Client/Info, 417      * - C_AcctSchema, 418      * - C_AcctSchema_Elements 419      * - AD_Preference 420      * <p> 421      * Assumes that the context is set for #AD_Client_ID, #AD_User_ID, #AD_Role_ID 422      * 423      * @param ctx context 424      * @param org org information 425      * @param warehouse optional warehouse information 426      * @param timestamp optional date 427      * @param printerName optional printer info 428      * @returns AD_Message of error (NoValidAcctInfo) or "" 429      */ 430     public static String loadPreferences (Properties ctx, 431         KeyNamePair org, KeyNamePair warehouse, Timestamp timestamp, String printerName) 432     { 433         s_log.info("loadPreferences - Org=" + org); 434 435         if (ctx == null || org == null) 436             throw new IllegalArgumentException ("DB.loadPreferences - required parameter missing"); 437         if (Env.getContext(ctx,"#AD_Client_ID").length() == 0) 438             throw new UnsupportedOperationException ("DB.loadPreferences - Missing Comtext #AD_Client_ID"); 439         if (Env.getContext(ctx,"#AD_User_ID").length() == 0) 440             throw new UnsupportedOperationException ("DB.loadPreferences - Missing Comtext #AD_User_ID"); 441         if (Env.getContext(ctx,"#AD_Role_ID").length() == 0) 442             throw new UnsupportedOperationException ("DB.loadPreferences - Missing Comtext #AD_Role_ID"); 443 444 445         // Org Info - assumes that it is valid 446 Env.setContext(ctx, "#AD_Org_ID", org.getKey()); 447         Env.setContext(ctx, "#AD_Org_Name", org.getName()); 448         Ini.setProperty(Ini.P_ORG, org.getName()); 449 450         // Warehouse Info 451 if (warehouse != null) 452         { 453             Env.setContext(ctx, "#M_Warehouse_ID", warehouse.getKey()); 454             Ini.setProperty(Ini.P_WAREHOUSE, warehouse.getName()); 455         } 456 457         // Date (default today) 458 long today = System.currentTimeMillis(); 459         if (timestamp != null) 460             today = timestamp.getTime(); 461         java.sql.Date sd = new java.sql.Date (today); 462         Env.setContext(ctx, "#Date", sd.toString()); // YYYY-MM-DD 463 464         // Optional Printer 465 if (printerName == null) 466             printerName = ""; 467         Env.setContext(ctx, "#Printer", printerName); 468         Ini.setProperty(Ini.P_PRINTER, printerName); 469          470         // Load Role Info 471 MRole.getDefault(ctx, true); 472 473         // Other 474 Env.setAutoCommit(ctx, Ini.getPropertyBool(Ini.P_A_COMMIT)); 475         if (MRole.getDefault(ctx, false).isShowAcct()) 476             Env.setContext(ctx, "#ShowAcct", Ini.getProperty(Ini.P_SHOW_ACCT)); 477         else 478             Env.setContext(ctx, "#ShowAcct", "N"); 479         Env.setContext(ctx, "#ShowTrl", Ini.getProperty(Ini.P_SHOW_TRL)); 480 481         String retValue = ""; 482         int AD_Client_ID = Env.getContextAsInt(ctx, "#AD_Client_ID"); 483         int AD_Org_ID = org.getKey(); 484         int AD_User_ID = Env.getContextAsInt(ctx, "#AD_User_ID"); 485         int AD_Role_ID = Env.getContextAsInt(ctx, "#AD_Role_ID"); 486 487         // Other Settings 488 Env.setContext(ctx, "#YYYY", "Y"); 489         Env.setContext(ctx, "#StdPrecision", 2); 490 491         // AccountSchema Info (first) 492 String sql = "SELECT * " 493             + "FROM C_AcctSchema a, AD_ClientInfo c " 494             + "WHERE a.C_AcctSchema_ID=c.C_AcctSchema1_ID " 495             + "AND c.AD_Client_ID=?"; 496         try 497         { 498             int C_AcctSchema_ID = 0; 499             PreparedStatement pstmt = prepareStatement(sql); 500             pstmt.setInt(1, AD_Client_ID); 501             ResultSet rs = pstmt.executeQuery(); 502 503             if (!rs.next()) 504             { 505                 // No Warning for System 506 if (AD_Role_ID != 0) 507                     retValue = "NoValidAcctInfo"; 508             } 509             else 510             { 511                 // Accounting Info 512 C_AcctSchema_ID = rs.getInt("C_AcctSchema_ID"); 513                 Env.setContext(ctx, "$C_AcctSchema_ID", C_AcctSchema_ID); 514                 Env.setContext(ctx, "$C_Currency_ID", rs.getInt("C_Currency_ID")); 515                 Env.setContext(ctx, "$HasAlias", rs.getString("HasAlias")); 516             } 517             rs.close(); 518             pstmt.close(); 519 520             // Accounting Elements 521 sql = "SELECT ElementType " 522                 + "FROM C_AcctSchema_Element " 523                 + "WHERE C_AcctSchema_ID=?" 524                 + " AND IsActive='Y'"; 525             pstmt = prepareStatement(sql); 526             pstmt.setInt(1, C_AcctSchema_ID); 527             rs = pstmt.executeQuery(); 528             while (rs.next()) 529                 Env.setContext(ctx, "$Element_" + rs.getString("ElementType"), "Y"); 530             rs.close(); 531             pstmt.close(); 532 533             // This reads all relevant window neutral defaults 534 // overwriting superseeded ones. Window specific is read in Mainain 535 sql = "SELECT Attribute, Value, AD_Window_ID " 536                 + "FROM AD_Preference " 537                 + "WHERE AD_Client_ID IN (0, @#AD_Client_ID@)" 538                 + " AND AD_Org_ID IN (0, @#AD_Org_ID@)" 539                 + " AND (AD_User_ID IS NULL OR AD_User_ID=0 OR AD_User_ID=@#AD_User_ID@)" 540                 + " AND IsActive='Y' " 541                 + "ORDER BY Attribute, AD_Client_ID, AD_User_ID DESC, AD_Org_ID"; 542                 // the last one overwrites - System - Client - User - Org - Window 543 sql = Env.parseContext(ctx, 0, sql, false); 544             if (sql.length() == 0) 545                 s_log.error("loadPreferences - Missing Environment"); 546             else 547             { 548                 pstmt = prepareStatement(sql); 549                 rs = pstmt.executeQuery(); 550                 while (rs.next()) 551                 { 552                     int AD_Window_ID = rs.getInt(3); 553                     String at = ""; 554                     if (rs.wasNull()) 555                         at = "P|" + rs.getString(1); 556                     else 557                         at = "P" + AD_Window_ID + "|" + rs.getString(1); 558                     String va = rs.getString(2); 559                     Env.setContext(ctx, at, va); 560                 } 561                 rs.close(); 562                 pstmt.close(); 563             } 564         } 565         catch (SQLException ex) 566         { 567             s_log.error("loadPreferences (" + sql + ")", ex); 568         } 569 570         // Default Values 571 s_log.info("Default Values ..."); 572         sql = "SELECT t.TableName, c.ColumnName " 573             + "FROM AD_Column c " 574             + " INNER JOIN AD_Table t ON (c.AD_Table_ID=t.AD_Table_ID) " 575             + "WHERE c.IsKey='Y' AND t.IsActive='Y'" 576             + " AND EXISTS (SELECT * FROM AD_Column cc " 577             + " WHERE ColumnName = 'IsDefault' AND t.AD_Table_ID=cc.AD_Table_ID AND cc.IsActive='Y')"; 578         try 579         { 580             PreparedStatement pstmt = prepareStatement(sql); 581             ResultSet rs = pstmt.executeQuery(); 582             while (rs.next()) 583                 loadDefault (ctx, rs.getString(1), rs.getString(2)); 584             rs.close(); 585             pstmt.close(); 586         } 587         catch (SQLException e) 588         { 589             s_log.error("loadPreferences", e); 590         } 591         // 592 Ini.saveProperties(Ini.isClient()); 593         // 594 return retValue; 595     } // loadPreferences 596 597     /** 598      * Load Default Value for Table into Context. 599      * 600      * @param ctx context 601      * @param TableName table name 602      * @param ColumnName column name 603      */ 604     private static void loadDefault (Properties ctx, String TableName, String ColumnName) 605     { 606         String value = null; 607         // 608 String sql = "SELECT " + ColumnName + " FROM " + TableName 609             + " WHERE IsDefault='Y' AND IsActive='Y' ORDER BY AD_Client_ID"; 610         sql = MRole.getDefault(ctx, false).addAccessSQL(sql, 611             TableName, MRole.SQL_NOTQUALIFIED, MRole.SQL_RO); 612         try 613         { 614             PreparedStatement pstmt = prepareStatement(sql); 615             ResultSet rs = pstmt.executeQuery(); 616             while (rs.next()) // overwrites system defaults 617 value = rs.getString(1); 618             rs.close(); 619             pstmt.close(); 620         } 621         catch (SQLException e) 622         { 623             s_log.error("loadDefault - " + TableName + " (" + sql + ")", e); 624             return; 625         } 626         // Set Context Value 627 if (value != null && value.length() != 0) 628         { 629             if (TableName.equals("C_DocType")) 630                 Env.setContext(ctx, "#C_DocTypeTarget_ID", value); 631             else 632                 Env.setContext(ctx, "#" + ColumnName, value); 633         } 634     } // loadDefault 635 636     /** 637      * Load Warehouses 638      * 639      * @param ctx context 640      * @param client client 641      * @return Array of Warehouse Info 642      */ 643     public static KeyNamePair[] loadWarehouses(Properties ctx, KeyNamePair client) 644     { 645         if (ctx == null || client == null) 646             throw new IllegalArgumentException ("DB.loadWarehouses - required parameter missing"); 647 648         s_log.info("loadWarehouses - Client=" + client.toString()); 649         //Begin e-evolution vpj-cd 05/09/2003 ************* 650 int AD_User_ID = Env.getContextAsInt(ctx,"#AD_User_ID"); 651         s_log.debug("AD_User_ID=" + AD_User_ID); 652         //end e-evolution vpj-cd 05/09/2003 ************* 653 654         ArrayList list = new ArrayList(); 655         try 656         { 657             String sql = "SELECT M_Warehouse_ID, Name FROM M_Warehouse " 658                 //Begin e-evolution vpj-cd 05/09/2003 ************* 659 // + "WHERE AD_Client_ID=? AND IsActive='Y'"; // #1 660 + "WHERE AD_Client_ID=? AND IsActive='Y' " 661                 + " AND (AD_Org_ID IN (SELECT AD_Org_ID FROM AD_User WHERE AD_User_ID=?) OR 0 IN (SELECT AD_Org_ID FROM AD_User WHERE AD_User_ID=?))"; //#2,#3 662 //end e-evolution vpj-cd 05/09/2003 ************* 663 PreparedStatement pstmt = prepareStatement(sql); 664             pstmt.setInt(1, client.getKey()); 665             //Begin e-evolution vpj-cd 05/09/2003 ************* 666 pstmt.setInt(2, AD_User_ID); 667             pstmt.setInt(3, AD_User_ID); 668             //end e-evolution vpj-cd 05/09/2003 ************* 669 ResultSet rs = pstmt.executeQuery(); 670 671             if (!rs.next()) 672             { 673                 rs.close(); 674                 pstmt.close(); 675                 s_log.warn("loadWarehouses - No Warehouses for Client=" + client.getKey()); 676                 return null; 677             } 678 679             // load Warehousess 680 do 681             { 682                 int AD_Warehouse_ID = rs.getInt(1); 683                 String Name = rs.getString(2); 684                 KeyNamePair p = new KeyNamePair(AD_Warehouse_ID, Name); 685                 list.add(p); 686             } 687             while (rs.next()); 688 689             rs.close(); 690             pstmt.close(); 691         } 692         catch (SQLException ex) 693         { 694             s_log.error("loadWarehouses", ex); 695             return null; 696         } 697         // 698 KeyNamePair[] retValue = new KeyNamePair[list.size()]; 699         list.toArray(retValue); 700         s_log.info("# warehouses = " + retValue.length); 701         return retValue; 702     } // loadWarehouses 703 704     /*************************************************************************/ 705 706     /** 707      * Set connection 708      * @param cc connection 709      */ 710     public static void setDBTarget (CConnection cc) 711     { 712         if (cc == null) 713             throw new IllegalArgumentException ("DB.setDBTarget connection is NULL"); 714 715         if (s_cc == null) 716             s_cc = cc; 717         synchronized (s_cc) // use as mutex 718 { 719             s_cc = cc; 720             // Closing existing 721 if (s_connections != null) 722             { 723                 for (int i = 0; i < s_connections.length; i++) 724                 { 725                     try { 726                         s_connections[i].close(); 727                     } catch (Exception e) {} 728                 } 729             } 730             s_connections = null; 731         } 732     } // setDBTarget 733 734     /** 735      * Is there a connection to the database ? 736      * @return true, if connected to database 737      */ 738     public static boolean isConnected() 739     { 740         try 741         { 742             getConnectionRW(); // try to get a connection 743 return true; 744         } 745         catch (Exception e) 746         { 747         } 748         return false; 749     } // isConnected 750 751     /** 752      * Return (pooled) r/w AutoCommit, read committed connection 753      * 754      * @return Connection (r/w) 755      */ 756     public static Connection getConnectionRW () 757     { 758         // check health of connection 759 try 760         { 761             if (s_connectionRW == null) 762                 ; 763             else if (s_connectionRW.isClosed()) 764                 s_connectionRW = null; 765             else if (s_connectionRW instanceof OracleConnection && ((OracleConnection)s_connectionRW).pingDatabase(1) < 0) 766                 s_connectionRW = null; 767             else 768                  s_connectionRW.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); 769         } 770         catch (Exception e) 771         { 772             s_connectionRW = null; 773         } 774         if (s_connectionRW == null) 775             s_connectionRW = s_cc.getConnection (true, Connection.TRANSACTION_READ_COMMITTED); 776         if (s_connectionRW == null) 777             throw new UnsupportedOperationException ("DB.getConnectionRW - @NoDBConnection@"); 778         return s_connectionRW; 779     } // getConnectionRW 780 781     /** 782      * Return read committed, read/only connection with AutoCommit from pool 783      * @return Connection (r/o) 784      */ 785     public static Connection getConnectionRO () 786     { 787         try 788         { 789             synchronized (s_cc) // use as mutex as s_connection is null the first time 790 { 791                 if (s_connections == null) 792                     s_connections = createConnections (Connection.TRANSACTION_READ_COMMITTED); // see below 793 } 794         } 795         catch (Exception e) 796         { 797             s_log.error("getConnectionRO", e); 798         } 799 800         // check health of connection 801 int pos = s_conCount++; 802         Connection connection = s_connections[pos % s_conCacheSize]; 803         try 804         { 805             if (connection == null) 806                 ; 807             else if (connection.isClosed()) 808                 connection = null; 809             else if (connection instanceof OracleConnection && ((OracleConnection)connection).pingDatabase(1) < 0) 810                 connection = null; 811             else 812                  connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); 813         } 814         catch (Exception e) 815         { 816             connection = null; 817         } 818         // Get new 819 if (connection == null) 820         { 821             System.out.println("DB.getConnectionRO - replacing connection #" + pos % s_conCacheSize); 822         // s_log.error("getConnectionRO - replacing connection #" + pos % s_conCacheSize); 823 connection = s_cc.getConnection (true, Connection.TRANSACTION_READ_COMMITTED); // see above 824 /* try 825             { 826                 retValue.setReadOnly(true); // not supported by Oracle 827             } 828             catch (Exception e) 829             { 830                 System.err.println("DB.getConnectionRO - Cannot set to R/O - " + e); 831             } */ 832             s_connections[pos % s_conCacheSize] = connection; 833         } 834         if (connection == null) 835             throw new UnsupportedOperationException ("DB.getConnectionRO - @NoDBConnection@"); 836         return connection; 837     } // getConnectionRO 838 839     /** 840      * Create new Connection. 841      * The connection must be closed explicitly by the application 842      * 843      * @param autoCommit auto commit 844      * @param trxLevel - Connection.TRANSACTION_READ_UNCOMMITTED, Connection.TRANSACTION_READ_COMMITTED, Connection.TRANSACTION_REPEATABLE_READ, or Connection.TRANSACTION_SERIALIZABLE. 845      * @return Connection connection 846      */ 847     public static Connection createConnection (boolean autoCommit, int trxLevel) 848     { 849         s_log.debug("createConnection " + s_cc.getConnectionURL() 850             + ", UserID=" + s_cc.getDbUid() + ", AutoCommit=" + autoCommit + ", TrxLevel=" + trxLevel); 851         return s_cc.getConnection (autoCommit, trxLevel); 852     } // createConnection 853 854     /** 855      * Create new set of r/o Connections. 856      * R/O connection might not be supported by DB 857      * 858      * @param trxLevel - Connection.TRANSACTION_READ_UNCOMMITTED, Connection.TRANSACTION_READ_COMMITTED, Connection.TRANSACTION_REPEATABLE_READ, or Connection.TRANSACTION_SERIALIZABLE. 859      * @return Array of Connections (size based on s_conCacheSize) 860      */ 861     private static Connection[] createConnections (int trxLevel) 862     { 863         s_log.debug("createConnections (" + s_conCacheSize + ") " + s_cc.getConnectionURL() 864             + ", UserID=" + s_cc.getDbUid() + ", TrxLevel=" + trxLevel); 865         Connection cons[] = new Connection[s_conCacheSize]; 866         try 867         { 868             for (int i = 0; i < s_conCacheSize; i++) 869             { 870                 cons[i] = s_cc.getConnection (true, trxLevel); // auto commit 871 if (cons[i] == null) 872                     System.err.println("createConnections - connection is NULL"); // don't use log 873 } 874         } 875         catch (Exception e) 876         { 877             // Don't use Log 878 System.err.println("DB.createConnections - " + e.getMessage()); 879         } 880         return cons; 881     } // createConnections 882 883     /** 884      * Get Database Driver. 885      * Access to database specific functionality. 886      * @return Compiere Database Driver 887      */ 888     public static CompiereDatabase getDatabase() 889     { 890         if (s_cc != null) 891             return s_cc.getDatabase(); 892         return null; 893     } // getDatabase 894 895     /*************************************************************************/ 896 897     /** 898      * Check database Version with Code version 899      * @param ctx context 900      * @return true if Database version (date) is the same 901      */ 902     public static boolean isDatabaseOK (Properties ctx) 903     { 904         // Check Version 905 String version = "?"; 906         String sql = "SELECT Version FROM AD_System"; 907         try 908         { 909             PreparedStatement pstmt = prepareStatement(sql); 910             ResultSet rs = pstmt.executeQuery(); 911             if (rs.next()) 912                 version = rs.getString(1); 913             rs.close(); 914             pstmt.close(); 915         } 916         catch (SQLException e) 917         { 918             s_log.error("Problem with AD_System Table - Run system.sql script - " + e.toString()); 919             return false; 920         } 921         s_log.info("isDatabaseOK - DB_Version=" + version); 922         // Identical DB version 923 if (Compiere.DB_VERSION.equals(version)) 924             return true; 925 926         String AD_Message = "DatabaseVersionError"; 927         String title = org.compiere.Compiere.getName() + " " + Msg.getMsg(ctx, AD_Message, true); 928         // Code assumes Database version {0}, but Database has Version {1}. 929 String msg = Msg.getMsg(ctx, AD_Message); // complete message 930 msg = MessageFormat.format(msg, new Object [] {Compiere.DB_VERSION, version}); 931         Object [] options = { UIManager.get("OptionPane.noButtonText"), "Migrate" }; 932         int no = JOptionPane.showOptionDialog (null, msg, 933             title, JOptionPane.DEFAULT_OPTION, JOptionPane.ERROR_MESSAGE, 934             UIManager.getIcon("OptionPane.errorIcon"), options, options[0]); 935         if (no == 1) 936         { 937             try 938             { 939                 Class.forName("com.compiere.client.StartMaintain").newInstance(); 940             } 941             catch (Exception ex) 942             { 943                 JOptionPane.showMessageDialog (null, 944                     ex.getMessage() + "\nSee: http://www.compiere.com/maintain", 945                     title, JOptionPane.ERROR_MESSAGE); 946                 s_log.error("isDatabaseOK - " + ex.getMessage()); 947             } 948         } 949         return false; 950     } // isDatabaseOK 951 952     /*************************************************************************/ 953 954     /** 955      * Secure Context Login. 956      * @param uid uid 957      * @param pwd pwd 958      * @param role role 959      * @return true if connected 960      */ 961     public static boolean login_context (String uid, String pwd, String role) 962     { 963         if (uid == null || pwd == null || role == null || uid.length() == 0 || pwd.length() == 0 || role.length() == 0) 964             throw new IllegalArgumentException ("DB.login_context - required parameter missing"); 965         s_log.info("login_context uid=" + uid); 966         if (uid == null || pwd == null || role == null) 967             return false; 968         // 969 String SQL = "{CALL Compiere_Context.Login(?,?,?,?)}"; 970         boolean result = true; 971         try 972         { 973             CallableStatement cstmt = getConnectionRO().prepareCall(SQL); 974             cstmt.setString(1, uid); 975             cstmt.setString(2, pwd); 976             cstmt.setString(3, role); 977             cstmt.setString(4, Language.getBaseAD_Language()); 978             result = cstmt.execute(); 979             cstmt.close(); 980         } 981         catch (SQLException e) 982         { 983             s_log.error("login_context", e); 984             result = false; 985         } 986         return result; 987     } // login_context 988 989     /*************************************************************************/ 990 991     /** 992      * Close Target 993      */ 994     public static void closeTarget() 995     { 996         if (s_connections != null) 997         { 998             for (int i = 0; i < s_conCacheSize; i++) 999             { 1000                try 1001                { 1002                    if (s_connections[i] != null) 1003                        s_connections[i].close(); 1004                } 1005                catch (SQLException e) 1006                { 1007                    s_log.warn("close connection #" + i + " - " + e.getMessage()); 1008                } 1009                s_connections[i] = null; 1010            } 1011            try 1012            { 1013                if (s_connectionRW != null) 1014                    s_connectionRW.close(); 1015            } 1016            catch (SQLException e) 1017            { 1018                s_log.error("close R/W connection", e); 1019            } 1020            s_connectionRW = null; 1021        } 1022        s_connections = null; 1023// EJB.close(); 1024 } // closeTarget 1025 1026    /*************************************************************************/ 1027 1028    /** 1029     * Prepare Forward Read Only Call 1030     * @param RO_SQL sql (RO) 1031     * @return Callable Statement 1032     */ 1033    public static CallableStatement prepareCall(String RO_SQL) 1034    { 1035        if (RO_SQL == null || RO_SQL.length() == 0) 1036            throw new IllegalArgumentException ("DB.prepareCall - required parameter missing - " + RO_SQL); 1037        // 1038 String sql = getDatabase().convertStatement(RO_SQL); 1039        try 1040        { 1041            return getConnectionRO().prepareCall 1042                (sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); 1043        } 1044        catch (SQLException e) 1045        { 1046            s_log.error("prepareCall (" + sql + ")", e); 1047        } 1048        return null; 1049    } // prepareCall 1050 1051    /*************************************************************************/ 1052 1053    /** 1054     * Prepare Read Only Statement 1055     * @param RO_SQL sql (RO) 1056     * @return Prepared Statement 1057     */ 1058    public static PreparedStatement prepareStatement(String RO_SQL) 1059    { 1060        return prepareStatement(RO_SQL, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); 1061    } // prepareStatement 1062 1063    /** 1064     * Prepare Statement 1065     * 1066     * @param SQL sql statement 1067     * @param resultSetType - ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.TYPE_SCROLL_SENSITIVE 1068     * @param resultSetConcurrency - ResultSet.CONCUR_READ_ONLY or ResultSet.CONCUR_UPDATABLE 1069     * @return Prepared Statement r/o or r/w depending on concur 1070     */ 1071    public static PreparedStatement prepareStatement(String SQL, int resultSetType, int resultSetConcurrency) 1072    { 1073        if (SQL == null || SQL.length() == 0) 1074            throw new IllegalArgumentException ("DB.prepareStatement - required parameter missing - " + SQL); 1075        // 1076 return new CompiereStatement(SQL, resultSetType, resultSetConcurrency); 1077        /** 1078        try 1079        { 1080            Connection conn = null; 1081            if (resultSetConcurrency == ResultSet.CONCUR_UPDATABLE) 1082                conn = getConnectionRW(); 1083            else 1084                conn = getConnectionRO(); 1085            return conn.prepareStatement (sql, resultSetType, resultSetConcurrency); 1086        } 1087        catch (SQLException e) 1088        { 1089            s_log.error("prepareStatement (" + sql + ")", e); 1090        } 1091        return null; 1092        **/ 1093    } // prepareStatement 1094 1095    /** 1096     * Create Read Only Statement 1097     * @return Statement 1098     */ 1099    public static Statement createStatement() 1100    { 1101        return createStatement (ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); 1102    } // createStatement 1103 1104    /** 1105     * Create Statement 1106     * 1107     * @param type - ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.TYPE_SCROLL_SENSITIVE 1108     * @param concur - ResultSet.CONCUR_READ_ONLY or ResultSet.CONCUR_UPDATABLE 1109     * @return Statement - either r/w ir r/o depending on concur 1110     */ 1111    public static Statement createStatement(int type, int concur) 1112    { 1113        try 1114        { 1115            Connection conn = null; 1116            if (concur == ResultSet.CONCUR_UPDATABLE) 1117                conn = getConnectionRW(); 1118            else 1119                conn = getConnectionRO(); 1120            return conn.createStatement(type, concur); 1121        } 1122        catch (SQLException e) 1123        { 1124            s_log.error("createStatement", e); 1125        } 1126        return null; 1127    } // createStatement 1128 1129    /** 1130     * Execute Update. 1131     * saves "DBExecuteError" in Log 1132     * @param SQL sql 1133     * @return number of rows updated or -1 if error 1134     */ 1135    public static int executeUpdate (String SQL) 1136    { 1137        return executeUpdate(SQL, false); 1138    } // executeUpdate 1139 1140    /** 1141     * Execute Update. 1142     * saves "DBExecuteError" in Log 1143     * @param SQL sql 1144     * @param ignoreError if true, no execution error is reported 1145     * @return number of rows updated or -1 if error 1146     */ 1147    public static int executeUpdate (String SQL, boolean ignoreError) 1148    { 1149        if (SQL == null || SQL.length() == 0) 1150            throw new IllegalArgumentException ("DB.executeUpdate - required parameter missing - " + SQL); 1151        // 1152 String sql = getDatabase().convertStatement(SQL); 1153        int no = -1; 1154        Statement stmt = null; 1155        try 1156        { 1157            Connection conn = getConnectionRW(); 1158            stmt = conn.createStatement(); 1159            no = stmt.executeUpdate(sql); 1160        } 1161        catch (SQLException e) 1162        { 1163            if (!ignoreError) 1164            { 1165                s_log.error ("executeUpdate - " + sql, e); 1166                Log.saveError ("DBExecuteError", e.getLocalizedMessage ()); 1167            } 1168        } 1169        finally 1170        { 1171            // Always close cursor 1172 try 1173            { 1174                stmt.close(); 1175            } 1176            catch (SQLException e2) 1177            { 1178                s_log.error("executeUpdate - cannot close statement"); 1179            } 1180        } 1181        return no; 1182    } // executeUpdate 1183 1184    /** 1185     * Execute Update and throw exxeption. 1186     * @param SQL sql 1187     * @return number of rows updated or -1 if error 1188     * @throws SQLException 1189     */ 1190    public static int executeUpdateEx (String SQL) throws SQLException 1191    { 1192        if (SQL == null || SQL.length() == 0) 1193            throw new IllegalArgumentException ("DB.executeUpdate - required parameter missing - " + SQL); 1194        // 1195 String sql = getDatabase().convertStatement(SQL); 1196        int no = -1; 1197        SQLException ex = null; 1198        Statement stmt = null; 1199        try 1200        { 1201            Connection conn = getConnectionRW(); 1202            stmt = conn.createStatement(); 1203            no = stmt.executeUpdate(sql); 1204        } 1205        catch (SQLException e) 1206        { 1207            ex = e; 1208        } 1209        finally 1210        { 1211            // Always close cursor 1212 try 1213            { 1214                stmt.close(); 1215            } 1216            catch (SQLException e2) 1217            { 1218                s_log.error("executeUpdate - cannot close statement"); 1219            } 1220        } 1221        if (ex != null) 1222            throw new SQLException(ex.getMessage(), ex.getSQLState(), ex.getErrorCode()); 1223        return no; 1224    } // execute Update 1225 1226    /** 1227     * Commit - commit on RW connection. 1228     * Is not required as RW connection is AutoCommit 1229     * @param throwException if true, re-throws exception 1230     * @return true if not needed or success 1231     * @throws SQLException 1232     */ 1233    public static boolean commit (boolean throwException) throws SQLException 1234    { 1235        try 1236        { 1237            Connection conn = getConnectionRW(); 1238        // if (!conn.getAutoCommit()) 1239 conn.commit(); 1240        } 1241        catch (SQLException e) 1242        { 1243            s_log.error("commit", e); 1244            if (throwException) 1245                throw e; 1246            return false; 1247        } 1248        return true; 1249    } // commit 1250 1251 1252    /** 1253     * Get Row Set 1254     * @param sql sql 1255     * @return row set or null 1256     */ 1257    public static RowSet getRowSet (String sql) 1258    { 1259        RowSet retValue = null; 1260        CompiereStatementVO info = new CompiereStatementVO(sql, RowSet.TYPE_SCROLL_INSENSITIVE, RowSet.CONCUR_READ_ONLY); 1261        CompiereStatement stmt = new CompiereStatement(info); 1262        retValue = stmt.remote_getRowSet(); 1263        return retValue; 1264    } // getRowSet 1265 1266    /************************************************************************* 1267 1268    /** 1269     * Get next number for Key column = 0 is Error. 1270     * @param ctx context 1271     * @param WindowNo window 1272     * @param TableName table 1273     * @return next no 1274     */ 1275    public static int getKeyNextNo (Properties ctx, int WindowNo, String TableName) 1276    { 1277        if (ctx == null || TableName == null || TableName.length() == 0) 1278            throw new IllegalArgumentException ("DB.getKeyNextNo - required parameter missing"); 1279        int AD_Client_ID = Env.getAD_Client_ID(ctx); 1280        // 1281 return getKeyNextNo (AD_Client_ID, TableName); 1282    } // getKeyNextNo 1283 1284    /** 1285     * Get next number for Key column = 0 is Error. 1286     * @param AD_Client_ID client 1287     * @param TableName table name 1288     * @return next no 1289     */ 1290    public static int getKeyNextNo (int AD_Client_ID, String TableName) 1291    { 1292        if (TableName == null || TableName.length() == 0) 1293            throw new IllegalArgumentException ("DB.getKeyNextNo - required parameter missing"); 1294        int retValue = 0; 1295 1296        // Check CompiereSys 1297 boolean compiereSys = Ini.getPropertyBool(Ini.P_COMPIERESYS); 1298        if (!compiereSys && AD_Client_ID < 1000000) 1299            AD_Client_ID = 1000000; 1300        // 1301 try 1302        { 1303            String SQL = "{CALL AD_Sequence_Next(?,?,?)}"; 1304            CallableStatement cstmt = prepareCall(SQL); 1305            cstmt.setString(1, TableName); 1306            cstmt.setInt(2, AD_Client_ID); 1307            cstmt.registerOutParameter(3, Types.NUMERIC); 1308            cstmt.executeUpdate(); 1309            retValue = cstmt.getInt(3); 1310            cstmt.close(); 1311        } 1312        catch(SQLException e) 1313        { 1314            s_log.error("getKeyNextNo - Table=" + TableName + ")", e); 1315        } 1316        return retValue; 1317    } // getKeyNextNo 1318 1319    /** 1320     * Get Document Number for current document. 1321     * <br> 1322     * - first search for DocType based Document No 1323     * - then Search for DocumentNo based on TableName 1324     * @param ctx context 1325     * @param WindowNo window 1326     * @param TableName table 1327     * @param onlyDocType Do not search for document no based on TableName 1328     * @return DocumentNo or null, if no doc number defined 1329     */ 1330    public static String getDocumentNo (Properties ctx, int WindowNo, String TableName, boolean onlyDocType) 1331    { 1332        if (ctx == null || TableName == null || TableName.length() == 0) 1333            throw new IllegalArgumentException ("DB.getDocumentNo - required parameter missing"); 1334        String CompiereSys = Env.getContext(ctx, "#CompiereSys"); 1335        int AD_Client_ID = Env.getContextAsInt(ctx, WindowNo, "AD_Client_ID"); 1336 1337        // Get C_DocType_ID from context - NO Defaults - 1338 int C_DocType_ID = Env.getContextAsInt(ctx, WindowNo + "|C_DocTypeTarget_ID"); 1339        if (C_DocType_ID == 0) 1340            C_DocType_ID = Env.getContextAsInt(ctx, WindowNo + "|C_DocType_ID"); 1341        if (C_DocType_ID == 0) 1342        { 1343            s_log.debug("getDocumentNo - for Window=" + WindowNo 1344                + " - Target=" + Env.getContextAsInt(ctx, WindowNo + "|C_DocTypeTarget_ID") + "/" + Env.getContextAsInt(ctx, WindowNo, "C_DocTypeTarget_ID") 1345                + " - Actual=" + Env.getContextAsInt(ctx, WindowNo + "|C_DocType_ID") + "/" + Env.getContextAsInt(ctx, WindowNo, "C_DocType_ID")); 1346            return getDocumentNo (AD_Client_ID, CompiereSys, TableName); 1347        } 1348 1349        // Check CompiereSys 1350 if (AD_Client_ID == 0 && !CompiereSys.equals("Y")) 1351            throw new UnsupportedOperationException ("DB.getDocumentNo - Cannot add System records"); 1352        // 1353 String retValue = getDocumentNo(AD_Client_ID, C_DocType_ID); 1354        if (!onlyDocType && retValue == null) 1355            return getDocumentNo (AD_Client_ID, CompiereSys, TableName); 1356        return retValue; 1357    } // getDocumentNo 1358 1359    /** 1360     * Get Document Number for current document. 1361     * @param AD_Client_ID client 1362     * @param C_DocType_ID (target) document type 1363     * @return DocumentNo or null, if no doc number defined 1364     */ 1365    public static String getDocumentNo (int AD_Client_ID, int C_DocType_ID) 1366    { 1367        String retValue = null; 1368        try 1369        { 1370            String SQL = "{CALL AD_Sequence_DocType(?,?,?)}"; 1371            CallableStatement cstmt = prepareCall(SQL); 1372            cstmt.setInt(1, C_DocType_ID); 1373            cstmt.setInt(2, AD_Client_ID); 1374            cstmt.registerOutParameter(3, Types.VARCHAR); 1375            cstmt.executeUpdate(); 1376            retValue = cstmt.getString(3); 1377            cstmt.close(); 1378        } 1379        catch(SQLException e) 1380        { 1381            s_log.error("getDocumentNo - DocType=" + C_DocType_ID, e); 1382        } 1383        s_log.info("getDocumentNo - DocType=" + C_DocType_ID + " -> " + retValue); 1384        return retValue; 1385    } // getDocumentNo 1386 1387 1388    /** 1389     * Get Next Document No 1390     * @param AD_Client_ID client 1391     * @param CompiereSys system 1392     * @param TableName table name 1393     * @return DocumentNo 1394     */ 1395    public static String getDocumentNo (int AD_Client_ID, String CompiereSys, String TableName) 1396    { 1397        if (CompiereSys == null || TableName == null || TableName.length() == 0) 1398            throw new IllegalArgumentException ("DB.getDocumentNo - required parameter missing"); 1399        // Check CompiereSys 1400 // if (AD_Client_ID == 0 && !CompiereSys.equals("Y")) 1401 // throw new UnsupportedOperationException("DB.getDocumentNo - Cannot add System records"); 1402 // 1403 String retValue = null; 1404        try 1405        { 1406            String SQL = "{CALL AD_Sequence_Doc(?,?,?)}"; 1407            CallableStatement cstmt = prepareCall(SQL); 1408            cstmt.setString(1, "DocumentNo_" + TableName); 1409            cstmt.setInt(2, AD_Client_ID); 1410            cstmt.registerOutParameter(3, Types.VARCHAR); 1411            cstmt.executeUpdate(); 1412            retValue = cstmt.getString(3); 1413            cstmt.close(); 1414        } 1415        catch(SQLException e) 1416        { 1417            s_log.error("getDocumentNo TableName=" + TableName, e); 1418        } 1419        s_log.info("getDocumentNo - TableName=" + TableName + " -> " + retValue); 1420        return retValue; 1421    } // getDocumentNo 1422 1423 1424    /** 1425     * Get Value from sql 1426     * @param sql sql 1427     * @return first value or -1 1428     */ 1429    public static int getSQLValue (String sql) 1430    { 1431        int retValue = -1; 1432        PreparedStatement pstmt = null; 1433        try 1434        { 1435            pstmt = prepareStatement(sql); 1436            ResultSet rs = pstmt.executeQuery(); 1437            if (rs.next()) 1438                retValue = rs.getInt(1); 1439            else 1440                s_log.warn("getSQLValue - No Value " + sql); 1441            rs.close(); 1442            pstmt.close(); 1443            pstmt = null; 1444        } 1445        catch (Exception e) 1446        { 1447            s_log.error("getSQLValue - " + sql, e); 1448        } 1449        finally 1450        { 1451            try 1452            { 1453                if (pstmt != null) 1454                    pstmt.close (); 1455            } 1456            catch (Exception e) 1457            {} 1458            pstmt = null; 1459        } 1460        return retValue; 1461    } // getSQLValue 1462 1463    /** 1464     * Get Value from sql 1465     * @param sql sql 1466     * @param int_param1 parameter 1 1467     * @return first value or -1 1468     */ 1469    public static int getSQLValue (String sql, int int_param1) 1470    { 1471        int retValue = -1; 1472        PreparedStatement pstmt = null; 1473        try 1474        { 1475            pstmt = prepareStatement(sql); 1476            pstmt.setInt(1, int_param1); 1477            ResultSet rs = pstmt.executeQuery(); 1478            if (rs.next()) 1479                retValue = rs.getInt(1); 1480            else 1481                s_log.warn("getSQLValue - No Value " + sql + " - Param1=" + int_param1); 1482            rs.close(); 1483            pstmt.close(); 1484            pstmt = null; 1485        } 1486        catch (Exception e) 1487        { 1488            s_log.error("getSQLValue - " + sql + " - Param1=" + int_param1, e); 1489        } 1490        finally 1491        { 1492            try 1493            { 1494                if (pstmt != null) 1495                    pstmt.close (); 1496            } 1497            catch (Exception e) 1498            {} 1499            pstmt = null; 1500        } 1501        return retValue; 1502    } // getSQLValue 1503 1504    /** 1505     * Get Value from sql 1506     * @param sql sql 1507     * @param int_param1 parameter 1 1508     * @return first value or -1 1509     */ 1510    public static int getSQLValue (String sql, String int_param1) 1511    { 1512        int retValue = -1; 1513        PreparedStatement pstmt = null; 1514        try 1515        { 1516            pstmt = prepareStatement(sql); 1517            pstmt.setString(1, int_param1); 1518            ResultSet rs = pstmt.executeQuery(); 1519            if (rs.next()) 1520                retValue = rs.getInt(1); 1521            else 1522                s_log.warn("getSQLValue - No Value " + sql + " - Param1=" + int_param1); 1523            rs.close(); 1524            pstmt.close(); 1525            pstmt = null; 1526        } 1527        catch (Exception e) 1528        { 1529            s_log.error("getSQLValue - " + sql + " - Param1=" + int_param1, e); 1530        } 1531        finally 1532        { 1533            try 1534            { 1535                if (pstmt != null) 1536                    pstmt.close (); 1537            } 1538            catch (Exception e) 1539            {} 1540            pstmt = null; 1541        } 1542        return retValue; 1543    } // getSQLValue 1544 1545    /** 1546     * Get Value from sql 1547     * @param sql sql 1548     * @param int_param1 parameter 1 1549     * @param s_param2 parameter 2 1550     * @return first value or -1 1551     */ 1552    public static int getSQLValue (String sql, int int_param1, String s_param2) 1553    { 1554        int retValue = -1; 1555        PreparedStatement pstmt = null; 1556        try 1557        { 1558            pstmt = prepareStatement(sql); 1559            pstmt.setInt(1, int_param1); 1560            pstmt.setString(2, s_param2); 1561            ResultSet rs = pstmt.executeQuery(); 1562            if (rs.next()) 1563                retValue = rs.getInt(1); 1564            else 1565                s_log.warn("getSQLValue - No Value: " + sql + " - Param1=" + int_param1 + ",Param2=" + s_param2); 1566            rs.close(); 1567            pstmt.close(); 1568            pstmt = null; 1569        } 1570        catch (Exception e) 1571        { 1572            s_log.error("getSQLValue - " + sql + " - Param1=" + int_param1 + ",Param2=" + s_param2, e); 1573        } 1574        finally 1575        { 1576            try 1577            { 1578                if (pstmt != null) 1579                    pstmt.close (); 1580            } 1581            catch (Exception e) 1582            {} 1583            pstmt = null; 1584        } 1585        return retValue; 1586    } // getSQLValue 1587 1588    /** 1589     * Get Array of Key Name Pairs 1590     * @param sql select with id and name as first and second column 1591     * @return array of key name pairs 1592     */ 1593    public static KeyNamePair[] getKeyNamePairs(String sql) 1594    { 1595        PreparedStatement pstmt = null; 1596        ArrayList list = new ArrayList(); 1597        try 1598        { 1599            pstmt = DB.prepareCall(sql); 1600            ResultSet rs = pstmt.executeQuery(); 1601            while (rs.next()) 1602                list.add(new KeyNamePair(rs.getInt(1), rs.getString(2))); 1603            rs.close(); 1604            pstmt.close(); 1605            pstmt = null; 1606        } 1607        catch (Exception e) 1608        { 1609            Log.error("getKeyNamePairs " + sql, e); 1610        } 1611        try 1612        { 1613            if (pstmt != null) 1614                pstmt.close(); 1615            pstmt = null; 1616        } 1617        catch (Exception e) 1618        { 1619            pstmt = null; 1620        } 1621        KeyNamePair[] retValue = new KeyNamePair[list.size()]; 1622        list.toArray(retValue); 1623    // s_log.debug("getKeyNamePairs #" + retValue.length); 1624 return retValue; 1625    } // getKeyNamePairs 1626 1627    /** 1628     * Convert an amount with today's spot rate 1629     * @param CurFrom_ID The C_Currency_ID FROM 1630     * @param CurTo_ID The C_Currency_ID TO 1631     * @param Amt The amount to be converted 1632     * @param AD_Client_ID client 1633     * @param AD_Org_ID organization 1634     * @return converted amount 1635     */ 1636    public static BigDecimal getConvertedAmt (BigDecimal Amt, int CurFrom_ID, int CurTo_ID, int AD_Client_ID, int AD_Org_ID) 1637    { 1638        return getConvertedAmt (Amt, CurFrom_ID, CurTo_ID, null, null, AD_Client_ID, AD_Org_ID); 1639    } // getConvertedAmt 1640 1641    /** 1642     * Convert an amount 1643     * @param CurFrom_ID The C_Currency_ID FROM 1644     * @param CurTo_ID The C_Currency_ID TO 1645     * @param ConvDate The Conversion date - if null - use current date 1646     * @param RateType The Conversion rate type - if null/empty - use Spot 1647     * @param Amt The amount to be converted 1648     * @param AD_Client_ID client 1649     * @param AD_Org_ID organization 1650     * @return converted amount 1651     */ 1652    public static BigDecimal getConvertedAmt (BigDecimal Amt, int CurFrom_ID, int CurTo_ID, 1653        Timestamp ConvDate, String RateType, int AD_Client_ID, int AD_Org_ID) 1654    { 1655        if (Amt == null) 1656            throw new IllegalArgumentException ("DB.getConvertedAmt - required parameter missing - Amt"); 1657        BigDecimal retValue = null; 1658        if (CurFrom_ID == CurTo_ID || Amt.equals(Env.ZERO)) 1659            return Amt; 1660        // 1661 try 1662        { 1663            String sql = "{? = call C_Currency_Convert(?,?,?,?,?, ?,?) }"; 1664            CallableStatement cstmt = prepareCall(sql); 1665            // 1666 cstmt.registerOutParameter(1, Types.NUMERIC); 1667            // 1668 cstmt.setBigDecimal(2, Amt); // Amount IN NUMBER 1669 cstmt.setInt(3, CurFrom_ID); // CurFrom IN NUMBER 1670 cstmt.setInt(4, CurTo_ID); // CurTo IN NUMBER 1671 if (ConvDate == null) 1672                ConvDate = new Timestamp (System.currentTimeMillis()); 1673            cstmt.setTimestamp(5, ConvDate); // ConvDate IN DATE 1674 if (RateType == null || RateType.equals("")) 1675                RateType = "S"; 1676            cstmt.setString(6, RateType); // RateType IN CHAR 1677 cstmt.setInt(7, AD_Client_ID); 1678            cstmt.setInt(8, AD_Org_ID); 1679            // 1680 cstmt.executeUpdate(); 1681            retValue = cstmt.getBigDecimal(1); 1682            cstmt.close(); 1683        } 1684        catch(SQLException e) 1685        { 1686            s_log.error("getConvertedAmt", e); 1687        } 1688        if (retValue == null) 1689            s_log.info("getConvertedAmt - not found - CurFrom=" + CurFrom_ID + ", CurTo=" + CurTo_ID 1690                + ", " + ConvDate + ", " + RateType + ", Client=" + AD_Client_ID + ", Org=" + AD_Org_ID); 1691        return retValue; 1692    } // getConvertedAmt 1693 1694    /** 1695     * Get Currency Rate 1696     * @param CurFrom_ID The C_Currency_ID FROM 1697     * @param CurTo_ID The C_Currency_ID TO 1698     * @param ConvDate The Conversion date - if null - use current date 1699     * @param RateType The Conversion rate type - if null/empty - use Spot 1700     * @param AD_Client_ID client 1701     * @param AD_Org_ID organization 1702     * @return currency Rate or null 1703     */ 1704    public static BigDecimal getConvesionRate (int CurFrom_ID, int CurTo_ID, 1705        Timestamp ConvDate, String RateType, int AD_Client_ID, int AD_Org_ID) 1706    { 1707        BigDecimal retValue = null; 1708        if (CurFrom_ID == CurTo_ID) 1709            return new BigDecimal(1); 1710        // 1711 try 1712        { 1713            String sql = "{? = call C_Currency_Rate(?,?,?,?,?,?)}"; 1714            CallableStatement cstmt = prepareCall(sql); 1715            // 1716 cstmt.registerOutParameter(1, Types.NUMERIC); 1717            // 1718 cstmt.setInt(2, CurFrom_ID); // CurFrom IN NUMBER 1719 cstmt.setInt(3, CurTo_ID); // CurTo IN NUMBER 1720 if (ConvDate == null) 1721                ConvDate = new Timestamp (System.currentTimeMillis()); 1722            cstmt.setTimestamp(4, ConvDate); // ConvDate IN DATE 1723 if (RateType == null || RateType.equals("")) 1724                RateType = "S"; 1725            cstmt.setString(5, RateType); // RateType IN CHAR 1726 cstmt.setInt(6, AD_Client_ID); 1727            cstmt.setInt(7, AD_Org_ID); 1728            // 1729 cstmt.executeUpdate(); 1730            retValue = cstmt.getBigDecimal(1); 1731            cstmt.close(); 1732        } 1733        catch(SQLException e) 1734        { 1735            s_log.error("getConvesionRate", e); 1736        } 1737        if (retValue == null) 1738            s_log.info ("getConversionRate - not found - CurFrom=" + CurFrom_ID + ", CurTo=" + CurTo_ID 1739              + ", " + ConvDate + ", " + RateType + ", Client=" + AD_Client_ID + ", Org=" + AD_Org_ID); 1740        return retValue; 1741    } // getConvesionRate 1742 1743 1744    /** 1745     * Get fully qualified Account Combination 1746     * 1747     * @param AD_Client_ID client 1748     * @param AD_Org_ID org 1749     * @param C_AcctSchema_ID acct schema 1750     * @param Account_ID natural account 1751     * @param base_ValidCombination_ID optional base combination to be specified 1752     * @param Alias aloas 1753     * @param AD_User_ID user 1754     * @param M_Product_ID product 1755     * @param C_BPartner_ID partner 1756     * @param AD_OrgTrx_ID trx org 1757     * @param C_LocFrom_ID loc from 1758     * @param C_LocTo_ID loc to 1759     * @param C_SRegion_ID sales region 1760     * @param C_Project_ID project 1761     * @param C_Campaign_ID campaign 1762     * @param C_Activity_ID activity 1763     * @param User1_ID user1 1764     * @param User2_ID user2 1765     * @return C_ValidCombination_ID of existing or new Combination 1766     */ 1767    public static int getValidCombination (int AD_Client_ID, int AD_Org_ID, 1768        int C_AcctSchema_ID, int Account_ID, int base_ValidCombination_ID, String Alias, int AD_User_ID, 1769        int M_Product_ID, int C_BPartner_ID, int AD_OrgTrx_ID, 1770        int C_LocFrom_ID, int C_LocTo_ID, int C_SRegion_ID, int C_Project_ID, 1771        int C_Campaign_ID, int C_Activity_ID, int User1_ID, int User2_ID) 1772    { 1773        int retValue = 0; 1774        StringBuffer sb = new StringBuffer ("C_ValidCombination_Get["); 1775        try 1776        { 1777            String sql = "{CALL C_ValidCombination_Get(?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?)}"; 1778            CallableStatement cstmt = prepareCall(sql); 1779            // 1 - ID 1780 cstmt.registerOutParameter(1, Types.NUMERIC); 1781            sb.append("v,"); 1782            // -- Mandatory Accounting fields 1783 // 2 - AD_Client_ID 1784 cstmt.setInt(2, AD_Client_ID); 1785            sb.append("AD_Client_ID=").append(AD_Client_ID).append(","); 1786            // 3 - AD_Org_ID 1787 cstmt.setInt(3, AD_Org_ID); 1788            sb.append("AD_Org_ID=").append(AD_Org_ID).append(","); 1789            // 4- C_AcctSchema_ID 1790 cstmt.setInt(4, C_AcctSchema_ID); 1791            sb.append("C_AcctSchema_ID=").append(C_AcctSchema_ID).append(","); 1792            // 5 - Account_ID 1793 cstmt.setInt(5, Account_ID); 1794            sb.append("Account_ID=").append(Account_ID).append(", "); 1795 1796            // -- Optional 1797 // 6 - Base C_ValidCombination_ID 1798 if (base_ValidCombination_ID != 0) 1799            { 1800                cstmt.setInt (6, base_ValidCombination_ID); 1801                sb.append("BaseValidCombination_ID=").append(base_ValidCombination_ID).append(","); 1802            } 1803            else 1804            { 1805                cstmt.setNull(6, Types.NUMERIC); 1806                sb.append("BaseValidCombination_ID=").append("NULL,"); 1807            } 1808            // 7 - MustBeFullyQualified 1809 cstmt.setString(7, "Y"); 1810            sb.append("MustBeFullyQualified='Y',"); 1811            // 8 - Alias 1812 if (Alias != null && Alias.length() > 0) 1813            { 1814                cstmt.setString(8, Alias); 1815                sb.append("Alias='").append(Alias).append("',"); 1816            } 1817            else 1818            { 1819                cstmt.setNull(8, Types.VARCHAR); 1820                sb.append("Alias=NULL,"); 1821            } 1822            // 9 - CreatedBy 1823 cstmt.setInt(9, AD_User_ID); 1824            sb.append("AD_User_ID=").append(AD_User_ID).append(", "); 1825 1826            // -- Optional Accounting fields 1827 // 10 - M_Product_ID 1828 if (M_Product_ID != 0) 1829            { 1830                cstmt.setInt(10, M_Product_ID); 1831                sb.append("M_Product_ID=").append(M_Product_ID).append(","); 1832            } 1833            else 1834            { 1835                cstmt.setNull(10, Types.NUMERIC); 1836                sb.append("M_Product_ID=NULL,"); 1837            } 1838            // 11 - C_BPartner_ID 1839 if (C_BPartner_ID != 0) 1840            { 1841                cstmt.setInt(11, C_BPartner_ID); 1842                sb.append("C_BPartner_ID=").append(C_BPartner_ID).append(","); 1843            } 1844            else 1845            { 1846                cstmt.setNull(11, Types.NUMERIC); 1847                sb.append("C_BPartner_ID=NULL,"); 1848            } 1849            // 12 - AD_OrgTrx_ID 1850 if (AD_OrgTrx_ID != 0) 1851            { 1852                cstmt.setInt(12, AD_OrgTrx_ID); 1853                sb.append("AD_OrgTrx_ID=").append(AD_OrgTrx_ID).append(","); 1854            } 1855            else 1856            { 1857                cstmt.setNull(12, Types.NUMERIC); 1858                sb.append("AD_OrgTrx_ID=NULL,"); 1859            } 1860            // 13 - C_LocFrom_ID 1861 if (C_LocFrom_ID != 0) 1862            { 1863                cstmt.setInt(13, C_LocFrom_ID); 1864                sb.append("C_LocFrom_ID=").append(C_LocFrom_ID).append(","); 1865            } 1866            else 1867            { 1868                cstmt.setNull(13, Types.NUMERIC); 1869                sb.append("C_LocFrom=NULL,"); 1870            } 1871            // 14 - C_LocTo_ID 1872 if (C_LocTo_ID != 0) 1873            { 1874                cstmt.setInt(14, (C_LocTo_ID)); 1875                sb.append("C_LocTo_ID=").append(C_LocTo_ID).append(", "); 1876            } 1877            else 1878            { 1879                cstmt.setNull(14, Types.NUMERIC); 1880                sb.append("C_LocTo_ID=NULL, "); 1881            } 1882            // 15 - C_SalesRegion_ID 1883 if (C_SRegion_ID != 0) 1884            { 1885                cstmt.setInt(15, (C_SRegion_ID)); 1886                sb.append("C_SalesRegion_ID=").append(C_SRegion_ID).append(","); 1887            } 1888            else 1889            { 1890                cstmt.setNull(15, Types.NUMERIC); 1891                sb.append("C_SalesRegion_ID=NULL,"); 1892            } 1893            // 16 - C_Project_ID 1894 if (C_Project_ID != 0) 1895            { 1896                cstmt.setInt(16, (C_Project_ID)); 1897                sb.append("C_Project_ID=").append(C_Project_ID).append(","); 1898            } 1899            else 1900            { 1901                cstmt.setNull(16, Types.NUMERIC); 1902                sb.append("C_Project_ID=NULL,"); 1903            } 1904            // 17 - C_Campaign_ID 1905 if (C_Campaign_ID != 0) 1906            { 1907                cstmt.setInt(17, (C_Campaign_ID)); 1908                sb.append("C_Campaign_ID=").append(C_Campaign_ID).append(","); 1909            } 1910            else 1911            { 1912                cstmt.setNull(17, Types.NUMERIC); 1913                sb.append("C_Campaign_ID=NULL,"); 1914            } 1915            // 18 - C_Activity_ID 1916 if (C_Activity_ID != 0) 1917            { 1918                cstmt.setInt(18, (C_Activity_ID)); 1919                sb.append("C_Activity_ID=").append(C_Activity_ID).append(","); 1920            } 1921            else 1922            { 1923                cstmt.setNull(18, Types.NUMERIC); 1924                sb.append("C_Activity_ID=NULL,"); 1925            } 1926            // 19 - User1_ID 1927 if (User1_ID != 0) 1928            { 1929                cstmt.setInt(19, (User1_ID)); 1930                sb.append("User1_ID=").append(User1_ID).append(","); 1931            } 1932            else 1933            { 1934                cstmt.setNull(19, Types.NUMERIC); 1935                sb.append("User1_ID=NULL,"); 1936            } 1937            // 20 - User2_ID 1938 if (User2_ID != 0) 1939            { 1940                cstmt.setInt(20, (User2_ID)); 1941                sb.append("User2_ID=").append(User2_ID).append(")"); 1942            } 1943            else 1944            { 1945                cstmt.setNull(20, Types.NUMERIC); 1946                sb.append("User2_ID=NULL)"); 1947            } 1948 1949            // 1950 cstmt.executeUpdate(); 1951            retValue = cstmt.getInt(1); // 1 - ID 1952 cstmt.close(); 1953            s_log.debug("getValidCombination " + sb.toString()); 1954        } 1955        catch(SQLException e) 1956        { 1957            s_log.error("getValidCombination " + sb.toString(), e); 1958        } 1959        return retValue; 1960    } // getValidCombination 1961 1962    /** 1963     * Insert Note 1964     * @param AD_Client_ID client 1965     * @param AD_Org_ID org 1966     * @param AD_User_ID user 1967     * @param AD_Table_ID table 1968     * @param Record_ID record 1969     * @param AD_MessageValue message 1970     * @param Text text 1971     * @param Reference subject 1972     * @return true if note was inserted 1973     */ 1974    public static boolean insertNote (int AD_Client_ID, int AD_Org_ID, int AD_User_ID, 1975        int AD_Table_ID, int Record_ID, 1976        String AD_MessageValue, String Text, String Reference) 1977    { 1978        if (AD_MessageValue == null || AD_MessageValue.length() == 0) 1979            throw new IllegalArgumentException ("DB.insertNote - required parameter missing - AD_Message"); 1980 1981        // Database limits 1982 if (Text == null) 1983            Text = ""; 1984        if (Reference == null) 1985            Reference = ""; 1986        // 1987 s_log.info("insertNote - " + AD_MessageValue + " - " + Reference); 1988        // 1989 StringBuffer sql = new StringBuffer ("INSERT INTO AD_Note (AD_Note_ID,"); 1990        sql.append("AD_Client_ID,AD_Org_ID,IsActive, Created,CreatedBy,Updated,UpdatedBy, ") 1991            .append("AD_Message_ID,Text,Reference, ") 1992            .append("AD_User_ID,AD_Table_ID,Record_ID,Processed) ") 1993            .append("SELECT "); 1994        // 1995 String CompiereSys = "N"; 1996        int AD_Note_ID = getKeyNextNo(AD_Client_ID, "AD_Note"); 1997        sql.append(AD_Note_ID).append(","); 1998        // 1999 sql.append(AD_Client_ID).append(",") 2000            .append(AD_Org_ID).append(", 'Y',SysDate,") 2001            .append(AD_User_ID).append(",SysDate,0,"); 2002        // AD_Message_ID,Text,Reference, 2003 sql.append(" AD_Message_ID,").append(DB.TO_STRING(Text, 2000)).append(",") 2004            .append(DB.TO_STRING(Reference, 60)).append(", "); 2005        // AD_User_ID,AD_Table_ID,Record_ID,Processed 2006 sql.append(AD_User_ID).append(",").append(AD_Table_ID).append(",") 2007            .append(Record_ID).append(",'N' "); 2008        // 2009 sql.append("FROM AD_Message WHERE Value='").append(AD_MessageValue).append("'"); 2010        // Create Entry 2011 int no = executeUpdate(sql.toString()); 2012 2013        // AD_Message must exist, so if not created, it is probably 2014 // due to non-existing AD_Message 2015 if (no == 0) 2016        { 2017            sql = new StringBuffer ("INSERT INTO AD_Note (AD_Note_ID,"); 2018            sql.append("AD_Client_ID,AD_Org_ID,IsActive, Created,CreatedBy,Updated,UpdatedBy, ") 2019                .append("AD_Message_ID,Text,Reference, ") 2020                .append("AD_User_ID,AD_Table_ID,Record_ID,Processed) ") 2021                .append("SELECT "); 2022            // - use AD_Note_ID from above 2023 sql.append(AD_Note_ID).append(","); 2024            // 2025 sql.append(AD_Client_ID).append(",") 2026                .append(AD_Org_ID).append(", 'Y',SysDate,") 2027                .append(AD_User_ID).append(",SysDate,0, "); 2028            // AD_Message_ID,Text,Reference, 2029 sql.append("AD_Message_ID,").append(TO_STRING (AD_MessageValue + ": " + Text, 2000)).append(",") 2030                .append(TO_STRING(Reference,60)).append(", "); 2031            // AD_User_ID,AD_Table_ID,Record_ID,Processed 2032 sql.append(AD_User_ID).append(",").append(AD_Table_ID).append(",") 2033                .append(Record_ID).append(",'N' "); 2034            // Hardcoded AD_Message - AD_Message is in Text 2035 sql.append("FROM AD_Message WHERE Value='NoMessageFound'"); 2036            // Try again 2037 no = executeUpdate(sql.toString()); 2038        } 2039 2040        return no == 1; 2041    } // insertNote 2042 2043    /*************************************************************************/ 2044 2045    /** 2046     * Print SQL Warnings. 2047     * <br> 2048     * Usage: DB.printWarning(rs.getWarnings(), "xx"); 2049     * @param comment comment 2050     * @param warning warning 2051     */ 2052    public static void printWarning(String comment, SQLWarning warning) 2053    { 2054        if (comment == null || warning == null || comment.length() == 0) 2055            throw new IllegalArgumentException ("DB.printException - required parameter missing"); 2056        s_log.warn("SQL Warning: " + comment); 2057        if (warning == null) 2058            return; 2059        // 2060 SQLWarning warn = warning; 2061        while (warn != null) 2062        { 2063            StringBuffer buffer = new StringBuffer (); 2064            buffer.append(warn.getMessage()); 2065            buffer.append("; State=").append(warn.getSQLState()).append("; ErrorCode=").append(warn.getErrorCode()); 2066            s_log.warn(buffer.toString()); 2067            warn = warn.getNextWarning(); 2068        } 2069    } // printWarning 2070 2071    /** 2072     * Create SQL TO Date String from Timestamp 2073     * 2074     * @param time Date to be converted 2075     * @param dayOnly true if time set to 00:00:00 2076     * 2077     * @return TO_DATE('2001-01-30 18:10:20',''YYYY-MM-DD HH24:MI:SS') 2078     * or TO_DATE('2001-01-30',''YYYY-MM-DD') 2079     */ 2080    public static String TO_DATE (Timestamp time, boolean dayOnly) 2081    { 2082        if (time == null) 2083        { 2084            if (dayOnly) 2085                return "TRUNC(SysDate)"; 2086            return "SysDate"; 2087        } 2088 2089        StringBuffer dateString = new StringBuffer ("TO_DATE('"); 2090        // YYYY-MM-DD HH24:MI:SS.mmmm JDBC Timestamp format 2091 String myDate = time.toString(); 2092        if (dayOnly) 2093        { 2094            dateString.append(myDate.substring(0,10)); 2095            dateString.append("','YYYY-MM-DD')"); 2096        } 2097        else 2098        { 2099            dateString.append(myDate.substring(0, myDate.indexOf("."))); // cut off miliseconds 2100 dateString.append("','YYYY-MM-DD HH24:MI:SS')"); 2101        } 2102        return dateString.toString(); 2103    } // TO_DATE 2104 2105    /** 2106     * Create SQL TO Date String from Timestamp 2107     * @param time time 2108     * @return TO_DATE String 2109     */ 2110    public static String TO_DATE (Timestamp time) 2111    { 2112        return TO_DATE(time, true); 2113    } // TO_DATE 2114 2115    /** 2116     * Create SQL for formatted Date, Number 2117     * 2118     * @see org.compiere.util.DisplayType 2119     * @see org.compiere.util.Env 2120     * 2121     * @param columnName the column name in the SQL 2122     * @param displayType Display Type 2123     * @param AD_Language 6 character language setting (from Env.LANG_*) 2124     * 2125     * @return TRIM(TO_CHAR(columnName,'9G999G990D00','NLS_NUMERIC_CHARACTERS='',.''')) 2126     * or TRIM(TO_CHAR(columnName,'TM9')) depending on DisplayType and Language 2127     */ 2128    public static String TO_CHAR (String columnName, int displayType, String AD_Language) 2129    { 2130        if (columnName == null || AD_Language == null || columnName.length() == 0) 2131            throw new IllegalArgumentException ("DB.TO_CHAR - required parameter missing"); 2132 2133        StringBuffer retValue = new StringBuffer ("TRIM(TO_CHAR("); 2134        retValue.append(columnName); 2135 2136        // Numbers 2137 if (DisplayType.isNumeric(displayType)) 2138        { 2139            if (displayType == DisplayType.Amount) 2140                retValue.append(",'9G999G990D00'"); 2141            else 2142                retValue.append(",'TM9'"); 2143            // TO_CHAR(GrandTotal,'9G999G990D00','NLS_NUMERIC_CHARACTERS='',.''') 2144 if (!Language.isDecimalPoint(AD_Language)) // reversed 2145 retValue.append(",'NLS_NUMERIC_CHARACTERS='',.'''"); 2146        } 2147        else if (DisplayType.isDate(displayType)) 2148        { 2149            retValue.append(",'") 2150                .append(Language.getLanguage(AD_Language).getDBdatePattern()) 2151                .append("'"); 2152        } 2153 2154        retValue.append("))"); 2155        // 2156 return retValue.toString(); 2157    } // TO_CHAR_Number 2158 2159 2160    /** 2161     * Package Strings for SQL command 2162     * @param txt String with text 2163     * @return escaped string for insert statement (NULL if null) 2164     */ 2165    public static String TO_STRING (String txt) 2166    { 2167        return TO_STRING (txt, 0); 2168    } // TO_STRING 2169 2170    /** 2171     * Package Strings for SQL command. 2172     * <pre> 2173     * - include in ' (single quotes) 2174     * - replace ' with '' 2175     * </pre> 2176     * @param txt String with text 2177     * @param maxLength Maximum Length of content or 0 to ignore 2178     * @return escaped string for insert statement (NULL if null) 2179     */ 2180    public static String TO_STRING (String txt, int maxLength) 2181    { 2182        if (txt == null) 2183            return "NULL"; 2184 2185        // Length 2186 String text = txt; 2187        if (maxLength != 0 && text.length() > maxLength) 2188            text = txt.substring(0, maxLength); 2189 2190        char quote = '\''; 2191        // copy characters (wee need to look through anyway) 2192 StringBuffer out = new StringBuffer (); 2193        out.append(quote); // ' 2194 for (int i = 0; i < text.length(); i++) 2195        { 2196            char c = text.charAt(i); 2197            if (c == quote) 2198                out.append("''"); 2199            else 2200                out.append(c); 2201        } 2202        out.append(quote); // ' 2203 // 2204 return out.toString(); 2205    } // TO_STRING 2206 2207} // DB 2208 2209

A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame Free Books   Free Magazines   Popular Tags