KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > compiere > process > ImportAccount


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-2003 Jorg Janke, parts
11  * created by ComPiere are Copyright (C) ComPiere, Inc.; All Rights Reserved.
12  * Contributor(s): ______________________________________.
13  *****************************************************************************/

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 /**
23  * Import Accounts from I_ElementValue
24  *
25  * @author Jorg Janke
26  * @version $Id: ImportAccount.java,v 1.11 2003/11/06 07:08:06 jjanke Exp $
27  */

28 public class ImportAccount extends SvrProcess
29 {
30     /**
31      * Import Account Constructor
32      */

33     public ImportAccount()
34     {
35         super();
36         Log.trace(Log.l1_User, "ImportAccount");
37     } // ImportAccount
38

39     /** Client to be imported to */
40     private int m_AD_Client_ID = 0;
41     /** Default Element */
42     private int m_C_Element_ID = 0;
43     /** Update Default Accounts */
44     private boolean m_updateDefaultAccounts = false;
45     /** Create New Combination */
46     private boolean m_createNewCombination = true;
47
48     /** Delete old Imported */
49     private boolean m_deleteOldImported = false;
50
51     /** Organization to be imported to */
52     private int m_AD_Org_ID = 0;
53
54     /** Effective */
55     private Timestamp m_DateValue = null;
56
57     /**
58      * Prepare - e.g., get Parameters.
59      */

60     protected void prepare()
61     {
62         ProcessInfoParameter[] para = getParameter();
63         for (int i = 0; i < para.length; i++)
64         {
65             String JavaDoc 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     } // prepare
84

85
86     /**
87      * Perrform process.
88      * @return Message
89      * @throws Exception
90      */

91     protected String JavaDoc doIt() throws java.lang.Exception JavaDoc
92     {
93         StringBuffer JavaDoc sql = null;
94         int no = 0;
95         String JavaDoc clientCheck = " AND AD_Client_ID=" + m_AD_Client_ID;
96
97         // **** Prepare ****
98

99         // Delete Old Imported
100
if (m_deleteOldImported)
101         {
102             sql = new StringBuffer JavaDoc ("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         // Set Client, Org, IsActive, Created/Updated
109
sql = new StringBuffer JavaDoc ("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         // **** Prepare ****
124

125         // Set Element
126
if (m_C_Element_ID != 0)
127         {
128             sql = new StringBuffer JavaDoc ("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         //
136
sql = new StringBuffer JavaDoc ("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         //
144
sql = new StringBuffer JavaDoc ("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         // Set Column
152
sql = new StringBuffer JavaDoc ("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         //
161
sql = new StringBuffer JavaDoc ("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'" // ignore default account
165
+ " 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         // Set Post* Defaults (ignore errors)
170
String JavaDoc[] yColumns = new String JavaDoc[] {"PostActual", "PostBudget", "PostStatistical", "PostEncumbrance"};
171         for (int i = 0; i < yColumns.length; i++)
172         {
173             sql = new StringBuffer JavaDoc ("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         // Summary
182
sql = new StringBuffer JavaDoc ("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         // Doc Controlled
190
sql = new StringBuffer JavaDoc ("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         // Check Account Type A (E) L M O R
198
sql = new StringBuffer JavaDoc ("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         //
205
sql = new StringBuffer JavaDoc ("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         // Check Account Sign (N) C B
213
sql = new StringBuffer JavaDoc ("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         //
220
sql = new StringBuffer JavaDoc ("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         // **** Update ElementValue from existing
229
sql = new StringBuffer JavaDoc ("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         // Disable Trigger Updateing Description
241
no = DB.executeUpdate("ALTER TABLE C_ValidCombination DISABLE ALL TRIGGERS");
242         Log.trace(Log.l5_DData, "ImportAccount.doIt", "Disable Description Update =" + no);
243
244         // -------------------------------------------------------------------
245
int noInsert = 0;
246         int noUpdate = 0;
247
248         // Go through Records
249
sql = new StringBuffer JavaDoc ("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             // Insert ElementValue
257
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             // Update ElementValue
270
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             // Set Imported = Y & Processing = 'Y'
281
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             //
286
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                 // **** Create/Update ElementValue
296
if (C_ElementValue_ID == 0) // New
297
{
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 JavaDoc ("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 // Update existing
318
{
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 JavaDoc ("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                 // Update Element
339
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                 //
345
conn.commit();
346             } // for all I_Product
347
rs.close();
348             pstmt.close();
349             //
350
pstmt_insertElementValue.close();
351             pstmt_updateElementValue.close();
352             pstmt_setImported.close();
353             //
354
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 JavaDoc ("ImportAccount.doIt", e);
369         }
370         finally
371         {
372             if (conn != null)
373                 conn.close();
374             conn = null;
375         }
376
377         // Set Error to indicator to not imported
378
sql = new StringBuffer JavaDoc ("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         // ***** Set Parent
388
sql = new StringBuffer JavaDoc ("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         //
397
sql = new StringBuffer JavaDoc ("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         //
404
sql = new StringBuffer JavaDoc ("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             //
417
String JavaDoc updateSQL = "UPDATE AD_TreeNode SET Parent_ID=?, SeqNo=? "
418                 + "WHERE AD_Tree_ID=? AND Node_ID=?";
419             PreparedStatement updateStmt = DB.prepareStatement(updateSQL);
420             //
421
while (rs.next())
422             {
423                 updateStmt.setInt(1, rs.getInt(1)); // Parent
424
updateStmt.setInt(2, rs.getInt(2)); // SeqNo (assume sequenec in import is the same)
425
updateStmt.setInt(3, rs.getInt(3)); // Tree
426
updateStmt.setInt(4, rs.getInt(4)); // Node
427
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         // Reset Processing Flag
450
sql = new StringBuffer JavaDoc ("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         // Re-Enable Triggers
464
no = DB.executeUpdate("ALTER TABLE C_ValidCombination ENABLE ALL TRIGGERS");
465         Log.trace(Log.l5_DData, "ImportAccount.doIt", "Enable Description Update =" + no);
466         // Update Description
467
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     } // doIt
472

473     /*************************************************************************/
474
475     /**
476      * Update Default Accounts
477      * @param clientCheck client where cluase
478      */

479     private void updateDefaults (String JavaDoc clientCheck)
480     {
481         Log.trace(Log.l3_Util, "ImportAccount.updateDefaults - CreateNewCombination=" + m_createNewCombination);
482
483         // **** Update Defaults
484
StringBuffer JavaDoc sql = new StringBuffer JavaDoc ("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         // Default Account DEFAULT_ACCT
502
sql = new StringBuffer JavaDoc ("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     } // updateDefaults
514

515     /**
516      * Update Default Accounts.
517      * _Default.xxxx = C_ValidCombination_ID => Account_ID=C_ElementValue_ID
518      * @param C_AcctSchema_ID Accounting Schema
519      */

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 JavaDoc 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 JavaDoc TableName = rs.getString(2);
548                 String JavaDoc ColumnName = rs.getString(3);
549                 int I_ElementValue_ID = rs.getInt(4);
550                 // Update it
551
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     } // createDefaultAccounts
574

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     /**
581      * Update Default Account.
582      * This is the sql to delete unused accounts - with the import still in the table(!):
583         DELETE C_ElementValue e
584         WHERE NOT EXISTS (SELECT * FROM Fact_Acct f WHERE f.Account_ID=e.C_ElementValue_ID)
585          AND NOT EXISTS (SELECT * FROM C_ValidCombination vc WHERE vc.Account_ID=e.C_ElementValue_ID)
586          AND NOT EXISTS (SELECT * FROM I_ElementValue i WHERE i.C_ElementValue_ID=e.C_ElementValue_ID);
587      * @param TableName Table Name
588      * @param ColumnName Column Name
589      * @param C_AcctSchema_ID Account Schema
590      * @param C_ElementValue_ID new Account
591      * @return UPDATE_* status
592      */

593     private int updateDefaultAccount (String JavaDoc TableName, String JavaDoc 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 JavaDoc sql = new StringBuffer JavaDoc ("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                 // The current account value is the same
612
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                 // We need to update the Account Value
618
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 JavaDoc ("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 // Replace Combination
644
{
645                         // Only Acct Combination directly
646
sql = new StringBuffer JavaDoc ("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                             // Where Acct was used
655
sql = new StringBuffer JavaDoc ("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 JavaDoc ("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                     } // replace combination
667
} // need to update
668
} // for all default accounts
669
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     } // updateDefaultAccount
681

682 } // ImportAccount
683
Popular Tags