ImportInventory


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.model.*;
20  import org.compiere.util.*;
21  
22  /**
23   *  Import Physical Inventory fom I_Inventory
24   *
25   *  @author     Jorg Janke
26   *  @version    $Id: ImportInventory.java,v 1.2 2003/08/25 02:31:57 jjanke Exp $
27   */
28  public class ImportInventory extends SvrProcess
29  {
30      /** Client to be imported to        */
31      private int             m_AD_Client_ID = 0;
32      /** Organization to be imported to  */
33      private int             m_AD_Org_ID = 0;
34      /** Location to be imported to      */
35      private int             m_M_Locator_ID = 0;
36      /** Default Date                    */
37      private Timestamp       m_MovementDate = null;
38      /** Delete old Imported             */
39      private boolean         m_deleteOldImported = false;
40  
41      /**
42       *  Prepare - e.g., get Parameters.
43       */
44      protected void prepare()
45      {
46          ProcessInfoParameter[] para = getParameter();
47          for (int i = 0; i < para.length; i++)
48          {
49              String   name = para[i].getParameterName();
50              if (para[i].getParameter() == null)
51                  ;
52              else if (name.equals("AD_Client_ID"))
53                  m_AD_Client_ID = ((BigDecimal)para[i].getParameter()).intValue();
54              else if (name.equals("AD_Org_ID"))
55                  m_AD_Org_ID = ((BigDecimal)para[i].getParameter()).intValue();
56              else if (name.equals("M_Locator_ID"))
57                  m_M_Locator_ID = ((BigDecimal)para[i].getParameter()).intValue();
58              else if (name.equals("MovementDate"))
59                  m_MovementDate = (Timestamp)para[i].getParameter();
60              else if (name.equals("DeleteOldImported"))
61                  m_deleteOldImported = "Y".equals(para[i].getParameter());
62              else
63                  log.error("prepare - Unknown Parameter: " + name);
64          }
65      }   //  prepare
66  
67  
68      /**
69       *  Perrform process.
70       *  @return Message
71       *  @throws Exception
72       */
73      protected String   doIt() throws java.lang.Exception  
74      {
75          StringBuffer   sql = null;
76          int no = 0;
77          String   clientCheck = " AND AD_Client_ID=" + m_AD_Client_ID;
78  
79          //  ****    Prepare ****
80  
81          //  Delete Old Imported
82          if (m_deleteOldImported)
83          {
84              sql = new StringBuffer   ("DELETE I_Inventory "
85                    + "WHERE I_IsImported='Y'").append (clientCheck);
86              no = DB.executeUpdate (sql.toString ());
87              log.debug ("doIt - Delete Old Impored =" + no);
88          }
89  
90          //  Set Client, Org, Location, IsActive, Created/Updated
91          sql = new StringBuffer   ("UPDATE I_Inventory "
92                + "SET AD_Client_ID = COALESCE (AD_Client_ID,").append (m_AD_Client_ID).append ("),"
93                + " AD_Org_ID = COALESCE (AD_Org_ID,").append (m_AD_Org_ID).append ("),");
94          if (m_M_Locator_ID != 0)
95              sql.append(" M_Locator_ID = COALESCE (M_Locator_ID,").append (m_M_Locator_ID).append ("),");
96          if (m_MovementDate != null)
97              sql.append(" MovementDate = COALESCE (MovementDate,").append (DB.TO_DATE(m_MovementDate)).append ("),");
98          sql.append(" IsActive = COALESCE (IsActive, 'Y'),"
99                + " Created = COALESCE (Created, SysDate),"
100               + " CreatedBy = COALESCE (CreatedBy, 0),"
101               + " Updated = COALESCE (Updated, SysDate),"
102               + " UpdatedBy = COALESCE (UpdatedBy, 0),"
103               + " I_ErrorMsg = NULL,"
104               + " M_Warehouse_ID = NULL,"   //  reset
105               + " I_IsImported = 'N' "
106               + "WHERE I_IsImported<>'Y' OR I_IsImported IS NULL");
107         no = DB.executeUpdate (sql.toString ());
108         log.info ("doIt - Reset=" + no);
109 
110         sql = new StringBuffer   ("UPDATE I_Inventory o "
111             + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Org, '"
112             + "WHERE (AD_Org_ID IS NULL OR AD_Org_ID=0"
113             + " OR EXISTS (SELECT * FROM AD_Org oo WHERE o.AD_Org_ID=oo.AD_Org_ID AND (oo.IsSummary='Y' OR oo.IsActive='N')))"
114             + " AND I_IsImported<>'Y'").append (clientCheck);
115         no = DB.executeUpdate (sql.toString ());
116         if (no != 0)
117             log.warn ("doIt - Invalid Org=" + no);
118 
119 
120         //  Location
121         sql = new StringBuffer   ("UPDATE I_Inventory i "
122             + "SET M_Locator_ID=(SELECT M_Locator_ID FROM M_Locator l"
123             + " WHERE i.LocatorValue=l.Value AND i.AD_Client_ID=l.AD_Client_ID AND ROWNUM=1) "
124             + "WHERE M_Locator_ID IS NULL AND LocatorValue IS NOT NULL"
125             + " AND I_IsImported<>'Y'").append (clientCheck);
126         no = DB.executeUpdate (sql.toString ());
127         log.debug("doIt - Set Locator from Value =" + no);
128         sql = new StringBuffer   ("UPDATE I_Inventory i "
129             + "SET M_Locator_ID=(SELECT M_Locator_ID FROM M_Locator l"
130             + " WHERE i.X=l.X AND i.Y=l.Y AND i.Z=l.Z AND i.AD_Client_ID=l.AD_Client_ID AND ROWNUM=1) "
131             + "WHERE M_Locator_ID IS NULL AND X IS NOT NULL AND Y IS NOT NULL AND Z IS NOT NULL"
132             + " AND I_IsImported<>'Y'").append (clientCheck);
133         no = DB.executeUpdate (sql.toString ());
134         log.debug("doIt - Set Locator from X,Y,Z =" + no);
135         sql = new StringBuffer   ("UPDATE I_Inventory "
136             + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=No Location, ' "
137             + "WHERE M_Locator_ID IS NULL"
138             + " AND I_IsImported<>'Y'").append (clientCheck);
139         no = DB.executeUpdate (sql.toString ());
140         if (no != 0)
141             log.warn ("doIt - No Location=" + no);
142 
143 
144         //  Set M_Warehouse_ID
145         sql = new StringBuffer   ("UPDATE I_Inventory i "
146             + "SET M_Warehouse_ID=(SELECT M_Warehouse_ID FROM M_Locator l WHERE i.M_Locator_ID=l.M_Locator_ID) "
147             + "WHERE M_Locator_ID IS NOT NULL"
148             + " AND I_IsImported<>'Y'").append (clientCheck);
149         no = DB.executeUpdate (sql.toString ());
150         log.debug("doIt - Set Warehouse from Locator =" + no);
151         sql = new StringBuffer   ("UPDATE I_Inventory "
152             + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=No Warehouse, ' "
153             + "WHERE M_Warehouse_ID IS NULL"
154             + " AND I_IsImported<>'Y'").append (clientCheck);
155         no = DB.executeUpdate (sql.toString ());
156         if (no != 0)
157             log.warn ("doIt - No Warehouse=" + no);
158 
159 
160         //  Product
161         sql = new StringBuffer   ("UPDATE I_Inventory i "
162               + "SET M_Product_ID=(SELECT M_Product_ID FROM M_Product p"
163               + " WHERE i.Value=p.Value AND i.AD_Client_ID=p.AD_Client_ID AND ROWNUM=1) "
164               + "WHERE M_Product_ID IS NULL AND Value IS NOT NULL"
165               + " AND I_IsImported<>'Y'").append (clientCheck);
166         no = DB.executeUpdate (sql.toString ());
167         log.debug("doIt - Set Product from Value=" + no);
168         sql = new StringBuffer   ("UPDATE I_Inventory i "
169               + "SET M_Product_ID=(SELECT M_Product_ID FROM M_Product p"
170               + " WHERE i.UPC=p.UPC AND i.AD_Client_ID=p.AD_Client_ID AND ROWNUM=1) "
171               + "WHERE M_Product_ID IS NULL AND UPC IS NOT NULL"
172               + " AND I_IsImported<>'Y'").append (clientCheck);
173         no = DB.executeUpdate (sql.toString ());
174         log.debug("doIt - Set Product from UPC=" + no);
175         sql = new StringBuffer   ("UPDATE I_Inventory "
176             + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=No Product, ' "
177             + "WHERE M_Product_ID IS NULL"
178             + " AND I_IsImported<>'Y'").append (clientCheck);
179         no = DB.executeUpdate (sql.toString ());
180         if (no != 0)
181             log.warn ("doIt - No Product=" + no);
182 
183         //  No QtyCount
184         sql = new StringBuffer   ("UPDATE I_Inventory "
185             + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=No Qty Count, ' "
186             + "WHERE QtyCount IS NULL"
187             + " AND I_IsImported<>'Y'").append (clientCheck);
188         no = DB.executeUpdate (sql.toString ());
189         if (no != 0)
190             log.warn ("doIt - No QtyCount=" + no);
191 
192         /*********************************************************************/
193 
194         MInventory inventory = null;
195 
196         int noInsert = 0;
197         int noInsertLine = 0;
198 
199         //  Go through Inventory Records
200         sql = new StringBuffer   ("SELECT * FROM I_Inventory "
201             + "WHERE I_IsImported='N'").append (clientCheck)
202             .append(" ORDER BY M_Warehouse_ID, TRUNC(MovementDate), I_Inventory_ID");
203         try
204         {
205             PreparedStatement pstmt = DB.prepareStatement (sql.toString ());
206             ResultSet rs = pstmt.executeQuery ();
207             //
208             int x_M_Warehouse_ID = -1;
209             Timestamp x_MovementDate = null;
210             while (rs.next())
211             {
212                 X_I_Inventory imp = new X_I_Inventory (getCtx (), rs);
213                 Timestamp MovementDate = TimeUtil.getDay(imp.getMovementDate());
214 
215                 if (inventory == null
216                     || imp.getM_Warehouse_ID() != x_M_Warehouse_ID
217                     || !MovementDate.equals(x_MovementDate))
218                 {
219                     inventory = new MInventory (getCtx(), 0);
220                     inventory.setClientOrg(imp.getAD_Client_ID(), imp.getAD_Org_ID());
221                     inventory.setName("I " + imp.getM_Warehouse_ID() + " " + MovementDate);
222                     inventory.setM_Warehouse_ID(imp.getM_Warehouse_ID());
223                     inventory.setMovementDate(MovementDate);
224                     if (!inventory.save())
225                     {
226                         log.error("doIt - Inventory not saved");
227                         break;
228                     }
229                     x_M_Warehouse_ID = imp.getM_Warehouse_ID();
230                     x_MovementDate = MovementDate;
231                     noInsert++;
232                 }
233 
234                 //  Line
235                 MInventoryLine line = new MInventoryLine (inventory, imp.getM_Locator_ID(), imp.getM_Product_ID(),
236                     imp.getQtyBook(), imp.getQtyCount());
237                 if (line.save())
238                 {
239                     imp.setI_IsImported(true);
240                     imp.setM_Inventory_ID(line.getM_Inventory_ID());
241                     imp.setM_InventoryLine_ID(line.getM_InventoryLine_ID());
242                     imp.setProcessed(true);
243                     if (imp.save())
244                         noInsertLine++;
245                 }
246             }
247             rs.close();
248             pstmt.close();
249         }
250         catch (Exception   e)
251         {
252             log.error("doIt", e);
253         }
254 
255         //  Set Error to indicator to not imported
256         sql = new StringBuffer   ("UPDATE I_Inventory "
257             + "SET I_IsImported='N', Updated=SysDate "
258             + "WHERE I_IsImported<>'Y'").append(clientCheck);
259         no = DB.executeUpdate(sql.toString());
260         addLog (0, null, new BigDecimal (no), "@Errors@");
261         //
262         addLog (0, null, new BigDecimal (noInsert), "@M_Inventory_ID@: @Inserted@");
263         addLog (0, null, new BigDecimal (noInsertLine), "@M_InventoryLine_ID@: @Inserted@");
264         return "";
265     }   //  doIt
266 
267 }   //  ImportInventory
268
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags