1 14 package org.compiere.process; 15 16 import java.sql.*; 17 import java.math.*; 18 19 import org.compiere.util.*; 20 import org.compiere.model.*; 21 22 28 public class ImportAccount extends SvrProcess 29 { 30 33 public ImportAccount() 34 { 35 super(); 36 Log.trace(Log.l1_User, "ImportAccount"); 37 } 39 40 private int m_AD_Client_ID = 0; 41 42 private int m_C_Element_ID = 0; 43 44 private boolean m_updateDefaultAccounts = false; 45 46 private boolean m_createNewCombination = true; 47 48 49 private boolean m_deleteOldImported = false; 50 51 52 private int m_AD_Org_ID = 0; 53 54 55 private Timestamp m_DateValue = null; 56 57 60 protected void prepare() 61 { 62 ProcessInfoParameter[] para = getParameter(); 63 for (int i = 0; i < para.length; i++) 64 { 65 String name = para[i].getParameterName(); 66 if (para[i].getParameter() == null) 67 ; 68 else if (name.equals("AD_Client_ID")) 69 m_AD_Client_ID = ((BigDecimal)para[i].getParameter()).intValue(); 70 else if (name.equals("C_Element_ID")) 71 m_C_Element_ID = ((BigDecimal)para[i].getParameter()).intValue(); 72 else if (name.equals("UpdateDefaultAccounts")) 73 m_updateDefaultAccounts = "Y".equals(para[i].getParameter()); 74 else if (name.equals("CreateNewCombination")) 75 m_createNewCombination = "Y".equals(para[i].getParameter()); 76 else if (name.equals("DeleteOldImported")) 77 m_deleteOldImported = "Y".equals(para[i].getParameter()); 78 else 79 Log.error("ImportAccount.prepare - Unknown Parameter: " + name); 80 } 81 if (m_DateValue == null) 82 m_DateValue = new Timestamp (System.currentTimeMillis()); 83 } 85 86 91 protected String doIt() throws java.lang.Exception 92 { 93 StringBuffer sql = null; 94 int no = 0; 95 String clientCheck = " AND AD_Client_ID=" + m_AD_Client_ID; 96 97 99 if (m_deleteOldImported) 101 { 102 sql = new StringBuffer ("DELETE I_ElementValue " 103 + "WHERE I_IsImported='Y'").append(clientCheck); 104 no = DB.executeUpdate(sql.toString()); 105 Log.trace(Log.l5_DData, "ImportAccount.doIt", "Delete Old Impored =" + no); 106 } 107 108 sql = new StringBuffer ("UPDATE I_ElementValue " 110 + "SET AD_Client_ID = COALESCE (AD_Client_ID, ").append(m_AD_Client_ID).append(")," 111 + " AD_Org_ID = COALESCE (AD_Org_ID, 0)," 112 + " IsActive = COALESCE (IsActive, 'Y')," 113 + " Created = COALESCE (Created, SysDate)," 114 + " CreatedBy = COALESCE (CreatedBy, 0)," 115 + " Updated = COALESCE (Updated, SysDate)," 116 + " UpdatedBy = COALESCE (UpdatedBy, 0)," 117 + " I_ErrorMsg = NULL," 118 + " I_IsImported = 'N' " 119 + "WHERE I_IsImported<>'Y' OR I_IsImported IS NULL"); 120 no = DB.executeUpdate(sql.toString()); 121 Log.trace(Log.l5_DData, "ImportAccount.doIt", "Reset=" + no); 122 123 125 if (m_C_Element_ID != 0) 127 { 128 sql = new StringBuffer ("UPDATE I_ElementValue " 129 + "SET ElementName=(SELECT Name FROM C_Element WHERE C_Element_ID=").append(m_C_Element_ID).append(") " 130 + "WHERE ElementName IS NULL AND C_Element_ID IS NULL" 131 + " AND I_IsImported<>'Y'").append(clientCheck); 132 no = DB.executeUpdate(sql.toString()); 133 Log.trace(Log.l5_DData, "ImportAccount.doIt", "Set Element Default=" + no); 134 } 135 sql = new StringBuffer ("UPDATE I_ElementValue i " 137 + "SET C_Element_ID = (SELECT C_Element_ID FROM C_Element e" 138 + " WHERE i.ElementName=e.Name AND i.AD_Client_ID=e.AD_Client_ID)" 139 + "WHERE C_Element_ID IS NULL" 140 + " AND I_IsImported<>'Y'").append(clientCheck); 141 no = DB.executeUpdate(sql.toString()); 142 Log.trace(Log.l5_DData, "ImportAccount.doIt", "Set Element=" + no); 143 sql = new StringBuffer ("UPDATE I_ElementValue " 145 + "SET I_IsImported='E', I_ErrorMsg='ERR=Invalid Element, ' " 146 + "WHERE C_Element_ID IS NULL" 147 + " AND I_IsImported<>'Y'").append(clientCheck); 148 no = DB.executeUpdate(sql.toString()); 149 Log.trace(Log.l3_Util, "ImportAccount.doIt", "Invalid Element=" + no); 150 151 sql = new StringBuffer ("UPDATE I_ElementValue i " 153 + "SET AD_Column_ID = (SELECT AD_Column_ID FROM AD_Column c" 154 + " WHERE UPPER(i.Default_Account)=UPPER(c.ColumnName)" 155 + " AND c.AD_Table_ID IN (315,266) AND AD_Reference_ID=25) " 156 + "WHERE Default_Account IS NOT NULL AND AD_Column_ID IS NULL" 157 + " AND I_IsImported<>'Y'").append(clientCheck); 158 no = DB.executeUpdate(sql.toString()); 159 Log.trace(Log.l5_DData, "ImportAccount.doIt", "Set Column=" + no); 160 sql = new StringBuffer ("UPDATE I_ElementValue " 162 + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Column, ' " 163 + "WHERE AD_Column_ID IS NULL AND Default_Account IS NOT NULL" 164 + " AND UPPER(Default_Account)<>'DEFAULT_ACCT'" + " AND I_IsImported<>'Y'").append(clientCheck); 166 no = DB.executeUpdate(sql.toString()); 167 Log.trace(Log.l3_Util, "ImportAccount.doIt", "Invalid Column=" + no); 168 169 String [] yColumns = new String [] {"PostActual", "PostBudget", "PostStatistical", "PostEncumbrance"}; 171 for (int i = 0; i < yColumns.length; i++) 172 { 173 sql = new StringBuffer ("UPDATE I_ElementValue SET ") 174 .append(yColumns[i]).append("='Y' WHERE ") 175 .append(yColumns[i]).append(" IS NULL OR ") 176 .append(yColumns[i]).append(" NOT IN ('Y','N')" 177 + " AND I_IsImported<>'Y'").append(clientCheck); 178 no = DB.executeUpdate(sql.toString()); 179 Log.trace(Log.l5_DData, "ImportAccount.doIt", "Set " + yColumns[i] + " Default=" + no); 180 } 181 sql = new StringBuffer ("UPDATE I_ElementValue " 183 + "SET IsSummary='N' " 184 + "WHERE IsSummary IS NULL OR IsSummary NOT IN ('Y','N')" 185 + " AND I_IsImported<>'Y'").append(clientCheck); 186 no = DB.executeUpdate(sql.toString()); 187 Log.trace(Log.l5_DData, "ImportAccount.doIt", "Set IsSummary Default=" + no); 188 189 sql = new StringBuffer ("UPDATE I_ElementValue " 191 + "SET IsDocControlled = CASE WHEN AD_Column_ID IS NOT NULL THEN 'Y' ELSE 'N' END " 192 + "WHERE IsDocControlled IS NULL OR IsDocControlled NOT IN ('Y','N')" 193 + " AND I_IsImported='N'").append(clientCheck); 194 no = DB.executeUpdate(sql.toString()); 195 Log.trace(Log.l5_DData, "ImportAccount.doIt", "Set IsDocumentControlled Default=" + no); 196 197 sql = new StringBuffer ("UPDATE I_ElementValue " 199 + "SET AccountType='E' " 200 + "WHERE AccountType IS NULL" 201 + " AND I_IsImported<>'Y'").append(clientCheck); 202 no = DB.executeUpdate(sql.toString()); 203 Log.trace(Log.l5_DData, "ImportAccount.doIt", "Set AccountType Default=" + no); 204 sql = new StringBuffer ("UPDATE I_ElementValue " 206 + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid AccountType, ' " 207 + "WHERE AccountType NOT IN ('A','E','L','M','O','R')" 208 + " AND I_IsImported<>'Y'").append(clientCheck); 209 no = DB.executeUpdate(sql.toString()); 210 Log.trace(Log.l3_Util, "ImportAccount.doIt", "Invalid AccountType=" + no); 211 212 sql = new StringBuffer ("UPDATE I_ElementValue " 214 + "SET AccountSign='N' " 215 + "WHERE AccountSign IS NULL" 216 + " AND I_IsImported<>'Y'").append(clientCheck); 217 no = DB.executeUpdate(sql.toString()); 218 Log.trace(Log.l5_DData, "ImportAccount.doIt", "Set AccountSign Default=" + no); 219 sql = new StringBuffer ("UPDATE I_ElementValue " 221 + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid AccountSign, ' " 222 + "WHERE AccountSign NOT IN ('N','C','B')" 223 + " AND I_IsImported<>'Y'").append(clientCheck); 224 no = DB.executeUpdate(sql.toString()); 225 Log.trace(Log.l3_Util, "ImportAccount.doIt", "Invalid AccountSign=" + no); 226 227 228 sql = new StringBuffer ("UPDATE I_ElementValue i " 230 + "SET C_ElementValue_ID=(SELECT C_ElementValue_ID FROM C_ElementValue ev" 231 + " INNER JOIN C_Element e ON (ev.C_Element_ID=e.C_Element_ID)" 232 + " WHERE i.C_Element_ID=e.C_Element_ID AND i.AD_Client_ID=e.AD_Client_ID" 233 + " AND i.Value=ev.Value) " 234 + "WHERE C_ElementValue_ID IS NULL" 235 + " AND I_IsImported='N'").append(clientCheck); 236 no = DB.executeUpdate(sql.toString()); 237 Log.trace(Log.l5_DData, "ImportAccount.doIt", "Found ElementValue=" + no); 238 239 240 no = DB.executeUpdate("ALTER TABLE C_ValidCombination DISABLE ALL TRIGGERS"); 242 Log.trace(Log.l5_DData, "ImportAccount.doIt", "Disable Description Update =" + no); 243 244 int noInsert = 0; 246 int noUpdate = 0; 247 248 sql = new StringBuffer ("SELECT I_ElementValue_ID, C_ElementValue_ID " 250 + "FROM I_ElementValue " 251 + "WHERE I_IsImported='N'").append(clientCheck) 252 .append(" ORDER BY I_ElementValue_ID"); 253 Connection conn = DB.createConnection(false, Connection.TRANSACTION_READ_COMMITTED); 254 try 255 { 256 PreparedStatement pstmt_insertElementValue = conn.prepareStatement 258 ("INSERT INTO C_ElementValue (C_ElementValue_ID,C_Element_ID," 259 + "AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy," 260 + "Value,Name,Description, AccountType,AccountSign,IsDocControlled," 261 + "IsSummary,PostActual,PostBudget,PostEncumbrance,PostStatistical) " 262 + "SELECT ?,C_Element_ID," 263 + "AD_Client_ID,AD_Org_ID,'Y',SysDate,CreatedBy,SysDate,UpdatedBy," 264 + "Value,Name,Description, AccountType,AccountSign,IsDocControlled," 265 + "IsSummary,PostActual,PostBudget,PostEncumbrance,PostStatistical " 266 + "FROM I_ElementValue " 267 + "WHERE I_ElementValue_ID=?"); 268 269 PreparedStatement pstmt_updateElementValue = conn.prepareStatement 271 ("UPDATE C_ElementValue " 272 + "SET (Value,Name,Description, AccountType,AccountSign,IsDocControlled," 273 + "IsSummary,PostActual,PostBudget,PostEncumbrance,PostStatistical,Updated,UpdatedBy)=" 274 + " (SELECT Value,Name,Description, AccountType,AccountSign,IsDocControlled," 275 + " IsSummary,PostActual,PostBudget,PostEncumbrance,PostStatistical,SysDate,UpdatedBy" 276 + " FROM I_ElementValue" 277 + " WHERE I_ElementValue_ID=?) " 278 + "WHERE C_ElementValue_ID=?"); 279 280 PreparedStatement pstmt_setImported = conn.prepareStatement 282 ("UPDATE I_ElementValue SET I_IsImported='Y'," 283 + " C_ElementValue_ID=?, " 284 + " Updated=SysDate, Processed='Y', Processing='Y' WHERE I_ElementValue_ID=?"); 285 PreparedStatement pstmt = DB.prepareStatement(sql.toString()); 287 ResultSet rs = pstmt.executeQuery(); 288 while (rs.next()) 289 { 290 int I_ElementValue_ID = rs.getInt(1); 291 int C_ElementValue_ID = rs.getInt(2); 292 Log.trace(Log.l6_Database, "I_ElementValue_ID=" + I_ElementValue_ID 293 + ", C_ElementValue_ID=" + C_ElementValue_ID); 294 295 if (C_ElementValue_ID == 0) { 298 C_ElementValue_ID = DB.getKeyNextNo(m_AD_Client_ID, "C_ElementValue"); 299 pstmt_insertElementValue.setInt(1, C_ElementValue_ID); 300 pstmt_insertElementValue.setInt(2, I_ElementValue_ID); 301 try 302 { 303 no = pstmt_insertElementValue.executeUpdate(); 304 Log.trace(10, "Insert ElementValue = " + no); 305 noInsert++; 306 } 307 catch (SQLException ex) 308 { 309 Log.trace(10, "Insert ElementValue - " + ex.toString()); 310 sql = new StringBuffer ("UPDATE I_ElementValue i " 311 + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append(DB.TO_STRING("Insert ElementValue: " + ex.toString())) 312 .append("WHERE I_ElementValue_ID=").append(I_ElementValue_ID); 313 DB.executeUpdate(sql.toString()); 314 continue; 315 } 316 } 317 else { 319 pstmt_updateElementValue.setInt(1, I_ElementValue_ID); 320 pstmt_updateElementValue.setInt(2, C_ElementValue_ID); 321 try 322 { 323 no = pstmt_updateElementValue.executeUpdate(); 324 Log.trace(10, "Update ElementValue = " + no); 325 noUpdate++; 326 } 327 catch (SQLException ex) 328 { 329 Log.trace(10, "Update ElementValue - " + ex.toString()); 330 sql = new StringBuffer ("UPDATE I_ElementValue i " 331 + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append(DB.TO_STRING("Update ElementValue: " + ex.toString())) 332 .append("WHERE I_ElementValue_ID=").append(I_ElementValue_ID); 333 DB.executeUpdate(sql.toString()); 334 continue; 335 } 336 } 337 338 pstmt_setImported.setInt(1, C_ElementValue_ID); 340 pstmt_setImported.setInt(2, I_ElementValue_ID); 341 no = pstmt_setImported.executeUpdate(); 342 if (no != 1) 343 Log.error("ImportAccount.doIt - Update Element Count=" + no); 344 conn.commit(); 346 } rs.close(); 348 pstmt.close(); 349 pstmt_insertElementValue.close(); 351 pstmt_updateElementValue.close(); 352 pstmt_setImported.close(); 353 conn.close(); 355 conn = null; 356 } 357 catch (SQLException e) 358 { 359 try 360 { 361 if (conn != null) 362 conn.close(); 363 conn = null; 364 } 365 catch (SQLException ex) 366 { 367 } 368 throw new Exception ("ImportAccount.doIt", e); 369 } 370 finally 371 { 372 if (conn != null) 373 conn.close(); 374 conn = null; 375 } 376 377 sql = new StringBuffer ("UPDATE I_ElementValue " 379 + "SET I_IsImported='N', Updated=SysDate " 380 + "WHERE I_IsImported<>'Y'").append(clientCheck); 381 no = DB.executeUpdate(sql.toString()); 382 addLog (0, null, new BigDecimal (no), "@Errors@"); 383 addLog (0, null, new BigDecimal (noInsert), "@C_ElementValue_ID@: @Inserted@"); 384 addLog (0, null, new BigDecimal (noUpdate), "@C_ElementValue_ID@: @Updated@"); 385 386 387 sql = new StringBuffer ("UPDATE I_ElementValue i " 389 + "SET ParentElementValue_ID=(SELECT C_ElementValue_ID" 390 + " FROM C_ElementValue ev WHERE i.C_Element_ID=ev.C_Element_ID" 391 + " AND i.ParentValue=ev.Value AND i.AD_Client_ID=ev.AD_Client_ID) " 392 + "WHERE ParentElementValue_ID IS NULL" 393 + " AND I_IsImported='Y'").append(clientCheck); 394 no = DB.executeUpdate(sql.toString()); 395 Log.trace(Log.l5_DData, "ImportAccount.doIt", "Found Parent ElementValue=" + no); 396 sql = new StringBuffer ("UPDATE I_ElementValue " 398 + "SET I_ErrorMsg=I_ErrorMsg||'Info=ParentNotFound, ' " 399 + "WHERE ParentElementValue_ID IS NULL AND ParentValue IS NOT NULL" 400 + " AND I_IsImported='Y'").append(clientCheck); 401 no = DB.executeUpdate(sql.toString()); 402 Log.trace(Log.l3_Util, "ImportAccount.doIt", "Not Found Patent ElementValue=" + no); 403 sql = new StringBuffer ("SELECT i.ParentElementValue_ID, i.I_ElementValue_ID," 405 + " e.AD_Tree_ID, i.C_ElementValue_ID, i.Value||'-'||i.Name AS Info " 406 + "FROM I_ElementValue i" 407 + " INNER JOIN C_Element e ON (i.C_Element_ID=e.C_Element_ID) " 408 + "WHERE i.C_ElementValue_ID IS NOT NULL AND e.AD_Tree_ID IS NOT NULL" 409 + " AND i.ParentElementValue_ID IS NOT NULL" 410 + " AND i.I_IsImported='Y' AND i.AD_Client_ID=").append(m_AD_Client_ID); 411 int noParentUpdate = 0; 412 try 413 { 414 Statement stmt = DB.createStatement(); 415 ResultSet rs = stmt.executeQuery(sql.toString()); 416 String updateSQL = "UPDATE AD_TreeNode SET Parent_ID=?, SeqNo=? " 418 + "WHERE AD_Tree_ID=? AND Node_ID=?"; 419 PreparedStatement updateStmt = DB.prepareStatement(updateSQL); 420 while (rs.next()) 422 { 423 updateStmt.setInt(1, rs.getInt(1)); updateStmt.setInt(2, rs.getInt(2)); updateStmt.setInt(3, rs.getInt(3)); updateStmt.setInt(4, rs.getInt(4)); try 428 { 429 no = updateStmt.executeUpdate(); 430 noParentUpdate += no; 431 } 432 catch (SQLException ex) 433 { 434 Log.error("ImportAccount.doIt (ParentUpdate)", ex); 435 no = 0; 436 } 437 if (no == 0) 438 Log.trace(Log.l1_User, "ImportAccount.doIt", "Parent not found for " + rs.getString(5)); 439 } 440 rs.close(); 441 stmt.close(); 442 } 443 catch (SQLException e) 444 { 445 Log.error("ImportAccount.doIt (ParentUpdateLoop) " + sql.toString(), e); 446 } 447 addLog (0, null, new BigDecimal (noParentUpdate), "@ParentElementValue_ID@: @Updated@"); 448 449 sql = new StringBuffer ("UPDATE I_ElementValue " 451 + "SET Processing='-'" 452 + "WHERE I_IsImported='Y' AND Processed='Y' AND Processing='Y'" 453 + " AND C_ElementValue_ID IS NOT NULL") 454 .append(clientCheck); 455 if (m_updateDefaultAccounts) 456 sql.append(" AND AD_Column_ID IS NULL"); 457 no = DB.executeUpdate(sql.toString()); 458 Log.trace(Log.l5_DData, "ImportAccount.doIt", "Reset Processing Flag=" + no); 459 460 if (m_updateDefaultAccounts) 461 updateDefaults(clientCheck); 462 463 no = DB.executeUpdate("ALTER TABLE C_ValidCombination ENABLE ALL TRIGGERS"); 465 Log.trace(Log.l5_DData, "ImportAccount.doIt", "Enable Description Update =" + no); 466 no = DB.executeUpdate("UPDATE C_ValidCombination SET Updated=SysDate WHERE AD_Client_ID=" + m_AD_Client_ID); 468 Log.trace(Log.l5_DData, "ImportAccount.doIt", "Update Account Description =" + no); 469 470 return ""; 471 } 473 474 475 479 private void updateDefaults (String clientCheck) 480 { 481 Log.trace(Log.l3_Util, "ImportAccount.updateDefaults - CreateNewCombination=" + m_createNewCombination); 482 483 StringBuffer sql = new StringBuffer ("SELECT C_AcctSchema_ID FROM C_AcctSchema_Element " 485 + "WHERE C_Element_ID=?").append(clientCheck); 486 try 487 { 488 PreparedStatement pstmt = DB.prepareStatement(sql.toString()); 489 pstmt.setInt(1, m_C_Element_ID); 490 ResultSet rs = pstmt.executeQuery(); 491 while (rs.next()) 492 updateDefaultAccounts (rs.getInt(1)); 493 rs.close(); 494 pstmt.close(); 495 } 496 catch (SQLException e) 497 { 498 Log.error("ImportAccount.updateDefaults", e); 499 } 500 501 sql = new StringBuffer ("UPDATE C_AcctSchema_Element e " 503 + "SET C_ElementValue_ID=(SELECT C_ElementValue_ID FROM I_ElementValue i" 504 + " WHERE e.C_Element_ID=i.C_Element_ID AND i.C_ElementValue_ID IS NOT NULL" 505 + " AND UPPER(i.Default_Account)='DEFAULT_ACCT') " 506 + "WHERE EXISTS (SELECT * FROM I_ElementValue i" 507 + " WHERE e.C_Element_ID=i.C_Element_ID AND i.C_ElementValue_ID IS NOT NULL" 508 + " AND UPPER(i.Default_Account)='DEFAULT_ACCT' " 509 + " AND i.I_IsImported='Y')") 510 .append(clientCheck); 511 int no = DB.executeUpdate(sql.toString()); 512 addLog (0, null, new BigDecimal (no), "@C_AcctSchema_Element_ID@: @Updated@"); 513 } 515 520 private void updateDefaultAccounts (int C_AcctSchema_ID) 521 { 522 Log.trace(Log.l3_Util, "ImportAccount.updateDefaultAccounts", "C_AcctSchema_ID=" + C_AcctSchema_ID); 523 524 AcctSchema as = new AcctSchema (C_AcctSchema_ID); 525 if (as.getAcctSchemaElement("AC").getC_Element_ID() != m_C_Element_ID) 526 { 527 Log.error("ImportAccount.updateDefaultAccounts - C_Element_ID=" + m_C_Element_ID + " not in AcctSchema=" + as); 528 return; 529 } 530 531 int[] counts = new int[] {0, 0, 0}; 532 533 String sql = "SELECT i.C_ElementValue_ID, t.TableName, c.ColumnName, i.I_ElementValue_ID " 534 + "FROM I_ElementValue i" 535 + " INNER JOIN AD_Column c ON (i.AD_Column_ID=c.AD_Column_ID)" 536 + " INNER JOIN AD_Table t ON (c.AD_Table_ID=t.AD_Table_ID) " 537 + "WHERE i.I_IsImported='Y' AND i.Processed='Y' AND Processing='Y'" 538 + " AND i.C_ElementValue_ID IS NOT NULL AND C_Element_ID=?"; 539 try 540 { 541 PreparedStatement pstmt = DB.prepareStatement(sql); 542 pstmt.setInt(1, m_C_Element_ID); 543 ResultSet rs = pstmt.executeQuery(); 544 while (rs.next()) 545 { 546 int C_ElementValue_ID = rs.getInt(1); 547 String TableName = rs.getString(2); 548 String ColumnName = rs.getString(3); 549 int I_ElementValue_ID = rs.getInt(4); 550 int u = updateDefaultAccount(TableName, ColumnName, C_AcctSchema_ID, C_ElementValue_ID); 552 counts[u]++; 553 if (u != UPDATE_ERROR) 554 { 555 sql = "UPDATE I_ElementValue SET Processing='N' " 556 + "WHERE I_ElementValue_ID=" + I_ElementValue_ID; 557 int no = DB.executeUpdate(sql.toString()); 558 if (no != 1) 559 Log.error("ImportAccount.updateDefaultAccounts - Updated=" + no); 560 } 561 } 562 rs.close(); 563 pstmt.close(); 564 } 565 catch (SQLException e) 566 { 567 Log.error("ImportAccount.createDefaultAccounts", e); 568 } 569 addLog (0, null, new BigDecimal (counts[UPDATE_ERROR]), as.toString() + ": @Errors@"); 570 addLog (0, null, new BigDecimal (counts[UPDATE_YES]), as.toString() + ": @Updated@"); 571 addLog (0, null, new BigDecimal (counts[UPDATE_SAME]), as.toString() + ": OK"); 572 573 } 575 576 private static final int UPDATE_ERROR = 0; 577 private static final int UPDATE_YES = 1; 578 private static final int UPDATE_SAME = 2; 579 580 593 private int updateDefaultAccount (String TableName, String ColumnName, int C_AcctSchema_ID, int C_ElementValue_ID) 594 { 595 Log.trace(Log.l5_DData, "ImportAccount.updateDefaultAccount - " 596 + TableName + "." + ColumnName + " - " + C_ElementValue_ID); 597 int retValue = UPDATE_ERROR; 598 StringBuffer sql = new StringBuffer ("SELECT x.") 599 .append(ColumnName).append(",Account_ID FROM ") 600 .append(TableName).append(" x INNER JOIN C_ValidCombination vc ON (x.") 601 .append(ColumnName).append("=vc.C_ValidCombination_ID) ") 602 .append("WHERE x.C_AcctSchema_ID=").append(C_AcctSchema_ID); 603 try 604 { 605 PreparedStatement pstmt = DB.prepareStatement(sql.toString()); 606 ResultSet rs = pstmt.executeQuery(); 607 if (rs.next()) 608 { 609 int C_ValidCombination_ID = rs.getInt(1); 610 int Account_ID = rs.getInt(2); 611 if (Account_ID == C_ElementValue_ID) 613 { 614 retValue = UPDATE_SAME; 615 Log.trace(Log.l6_Database, "Account_ID same as new value"); 616 } 617 else 619 { 620 if (m_createNewCombination) 621 { 622 Account acct = Account.getAccount(C_ValidCombination_ID); 623 acct.setAccount_ID(C_ElementValue_ID); 624 if (acct.save()) 625 { 626 int newC_ValidCombination_ID = acct.getC_ValidCombination_ID(); 627 if (C_ValidCombination_ID != newC_ValidCombination_ID) 628 { 629 sql = new StringBuffer ("UPDATE ").append(TableName) 630 .append(" SET ").append(ColumnName).append("=").append(newC_ValidCombination_ID) 631 .append(" WHERE C_AcctSchema_ID=").append(C_AcctSchema_ID); 632 int no = DB.executeUpdate(sql.toString()); 633 Log.trace(no == 1 ? Log.l5_DData : 0, "ImportAccount.updateDefaultAccount - #" + no + " - " 634 + TableName + "." + ColumnName + " - " + C_ElementValue_ID 635 + " -- " + C_ValidCombination_ID + " -> " + newC_ValidCombination_ID); 636 if (no == 1) 637 retValue = UPDATE_YES; 638 } 639 } 640 else 641 Log.error("ImportAccount.updateDefaultAccount - Account not saved - " + acct); 642 } 643 else { 645 sql = new StringBuffer ("UPDATE C_ValidCombination SET Account_ID=") 647 .append(C_ElementValue_ID).append(" WHERE C_ValidCombination_ID=").append(C_ValidCombination_ID); 648 int no = DB.executeUpdate(sql.toString()); 649 Log.trace(no == 1 ? Log.l5_DData : 0, "ImportAccount.updateDefaultAccount - Replace #" + no + " - " 650 + "C_ValidCombination_ID=" + C_ValidCombination_ID + ", New Account_ID=" + C_ElementValue_ID); 651 if (no == 1) 652 { 653 retValue = UPDATE_YES; 654 sql = new StringBuffer ("UPDATE C_ValidCombination SET Account_ID=") 656 .append(C_ElementValue_ID).append(" WHERE Account_ID=").append(Account_ID); 657 no = DB.executeUpdate(sql.toString()); 658 Log.trace(no == 1 ? Log.l5_DData : 0, "ImportAccount.updateDefaultAccount - Replace VC #" + no + " - " 659 + "Account_ID=" + Account_ID + ", New Account_ID=" + C_ElementValue_ID); 660 sql = new StringBuffer ("UPDATE Fact_Acct SET Account_ID=") 661 .append(C_ElementValue_ID).append(" WHERE Account_ID=").append(Account_ID); 662 no = DB.executeUpdate(sql.toString()); 663 Log.trace(no == 1 ? Log.l5_DData : 0, "ImportAccount.updateDefaultAccount - Replace Fact #" + no + " - " 664 + "Account_ID=" + Account_ID + ", New Account_ID=" + C_ElementValue_ID); 665 } 666 } } } else 670 Log.error("ImportAccount.updateDefaultAccount - Account not found " + sql); 671 rs.close(); 672 pstmt.close(); 673 } 674 catch (SQLException e) 675 { 676 Log.error("ImportAccount.updateDefaultAccount " + sql, e); 677 } 678 679 return retValue; 680 } 682 } | Popular Tags |