MProductPricing


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 Smart 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.model;
15  
16  import java.util.*;
17  import java.sql.*;
18  import java.math.*;
19  
20  import org.compiere.util.*;
21  
22  /**
23   *  Product Price Calculations
24   *
25   *  @author Jorg Janke
26   *  @version $Id: MProductPricing.java,v 1.1 2003/10/11 05:20:33 jjanke Exp $
27   */
28  public class MProductPricing
29  {
30      /**
31       *  Constructor
32       *  @param M_Product_ID product
33       */
34      public MProductPricing (int M_Product_ID)
35      {
36          m_M_Product_ID = M_Product_ID;
37      }
38  
39      private int m_M_Product_ID;
40      private int m_M_PriceList_ID = 0;
41      private int m_M_PriceList_Version_ID = 0;
42      private int m_C_UOM_ID = 0;
43      private Timestamp m_PriceDate;
44      private BigDecimal m_PriceList = Env.ZERO;
45      private BigDecimal m_PriceStd = Env.ZERO;
46      private BigDecimal m_PriceLimit = Env.ZERO;
47      private int m_C_Currency_ID = 0;
48  
49      private boolean m_calculated = false;
50  
51      /**
52       *  Calculate Price
53       *  @return true if calculated
54       */
55      public boolean calculatePrice ()
56      {
57          if (m_M_Product_ID == 0)
58              return false;
59          m_calculated = calculatePLV ();
60          if (!m_calculated)
61              m_calculated = calculatePL();
62          if (!m_calculated)
63              m_calculated = calculateBPL();
64          if (!m_calculated)
65              setBaseInfo();
66          return m_calculated;
67      }   //  calculatePrice
68  
69      /**
70       *  Calculate Price based on Price List Version
71       *  @return true if calculated
72       */
73      public boolean calculatePLV()
74      {
75          if (m_M_Product_ID == 0 || m_M_PriceList_Version_ID == 0)
76              return false;
77          //
78          String   sql = "SELECT BOM_PriceStd(p.M_Product_ID,pv.M_PriceList_Version_ID) AS PriceStd,"   //  1
79              + " BOM_PriceList(p.M_Product_ID,pv.M_PriceList_Version_ID) AS PriceList,"      //  2
80              + " BOM_PriceLimit(p.M_Product_ID,pv.M_PriceList_Version_ID) AS PriceLimit,"    //  3
81              + " p.C_UOM_ID,pv.ValidFrom,pl.C_Currency_ID "          // 4..6
82              + "FROM M_Product p"
83              + " INNER JOIN M_ProductPrice pp ON (p.M_Product_ID=pp.M_Product_ID)"
84              + " INNER JOIN  M_PriceList_Version pv ON (pp.M_PriceList_Version_ID=pv.M_PriceList_Version_ID)"
85              + " INNER JOIN M_Pricelist pl ON (pv.M_PriceList_ID=pl.M_PriceList_ID) "
86              + "WHERE pv.IsActive='Y'"
87              + " AND p.M_Product_ID=?"               //  #1
88              + " AND pv.M_PriceList_Version_ID=?";   //  #2
89          m_calculated = false;
90          try
91          {
92              PreparedStatement pstmt = DB.prepareStatement(sql);
93              pstmt.setInt(1, m_M_Product_ID);
94              pstmt.setInt(2, m_M_PriceList_Version_ID);
95              ResultSet rs = pstmt.executeQuery();
96              if (rs.next())
97              {
98                  //  Prices
99                  m_PriceStd = rs.getBigDecimal(1);
100                 if (rs.wasNull())
101                     m_PriceStd = Env.ZERO;
102                 m_PriceList = rs.getBigDecimal(2);
103                 if (rs.wasNull())
104                     m_PriceList = Env.ZERO;
105                 m_PriceLimit = rs.getBigDecimal(3);
106                 if (rs.wasNull())
107                     m_PriceLimit = Env.ZERO;
108                 //
109                 m_C_UOM_ID = rs.getInt(4);
110                 m_C_Currency_ID = rs.getInt(6);
111                 //
112                 m_calculated = true;
113             }
114             rs.close();
115             pstmt.close();
116         }
117         catch (Exception   e)
118         {
119             Log.error("MProductPrice.calculatePLV", e);
120             m_calculated = false;
121         }
122         return m_calculated;
123     }   //  calculatePLV
124 
125     /**
126      *  Calculate Price based on Price List
127      *  @return true if calculated
128      */
129     public boolean calculatePL()
130     {
131         if (m_M_Product_ID == 0)
132             return false;
133 
134         //  Get Price List
135         if (m_M_PriceList_ID == 0)
136         {
137             String   sql = "SELECT M_PriceList_ID "
138                 + "FROM M_PriceList pl"
139                 + " INNER JOIN M_Product p ON (pl.AD_Client_ID=p.AD_Client_ID) "
140                 + "WHERE M_Product_ID=? "
141                 + "ORDER BY IsDefault DESC";
142             PreparedStatement pstmt = null;
143             try
144             {
145                 pstmt = DB.prepareStatement(sql);
146                 pstmt.setInt(1, m_M_Product_ID);
147                 ResultSet rs = pstmt.executeQuery();
148                 if (rs.next())
149                     m_M_PriceList_ID = rs.getInt(1);
150                 rs.close();
151                 pstmt.close();
152                 pstmt = null;
153             }
154             catch (Exception   e)
155             {
156                 Log.error("MProductPrice.calculatePL (PL)", e);
157             }
158             finally
159             {
160                 try
161                 {
162                     if (pstmt != null)
163                         pstmt.close ();
164                 }
165                 catch (Exception   e)
166                 {}
167                 pstmt = null;
168             }
169         }
170         if (m_M_PriceList_ID == 0)
171         {
172             Log.error("MProductPrice.calculatePL - No PriceList found for M_Product_ID=" + m_M_Product_ID);
173             return false;
174         }
175 
176         //  Get Prices
177         String   sql = "SELECT BOM_PriceStd(p.M_Product_ID,pv.M_PriceList_Version_ID) AS PriceStd,"   //  1
178             + " BOM_PriceList(p.M_Product_ID,pv.M_PriceList_Version_ID) AS PriceList,"      //  2
179             + " BOM_PriceLimit(p.M_Product_ID,pv.M_PriceList_Version_ID) AS PriceLimit,"    //  3
180             + " p.C_UOM_ID,pv.ValidFrom,pl.C_Currency_ID "          // 4..6
181             + "FROM M_Product p"
182             + " INNER JOIN M_ProductPrice pp ON (p.M_Product_ID=pp.M_Product_ID)"
183             + " INNER JOIN  M_PriceList_Version pv ON (pp.M_PriceList_Version_ID=pv.M_PriceList_Version_ID)"
184             + " INNER JOIN M_Pricelist pl ON (pv.M_PriceList_ID=pl.M_PriceList_ID) "
185             + "WHERE pv.IsActive='Y'"
186             + " AND p.M_Product_ID=?"               //  #1
187             + " AND pv.M_PriceList_ID=?"            //  #2
188             + "ORDER BY pv.ValidFrom DESC";
189         m_calculated = false;
190         if (m_PriceDate == null)
191             m_PriceDate = new Timestamp (System.currentTimeMillis());
192         try
193         {
194             PreparedStatement pstmt = DB.prepareStatement(sql);
195             pstmt.setInt(1, m_M_Product_ID);
196             pstmt.setInt(2, m_M_PriceList_ID);
197             ResultSet rs = pstmt.executeQuery();
198             while (!m_calculated && rs.next())
199             {
200                 Timestamp plDate = rs.getTimestamp(5);
201                 //  we have the price list
202                 //  if order date is after or equal PriceList validFrom
203                 if (plDate == null || !m_PriceDate.before(plDate))
204                 {
205                     //  Prices
206                     m_PriceStd = rs.getBigDecimal (1);
207                     if (rs.wasNull ())
208                         m_PriceStd = Env.ZERO;
209                     m_PriceList = rs.getBigDecimal (2);
210                     if (rs.wasNull ())
211                         m_PriceList = Env.ZERO;
212                     m_PriceLimit = rs.getBigDecimal (3);
213                     if (rs.wasNull ())
214                         m_PriceLimit = Env.ZERO;
215                         //
216                     m_C_UOM_ID = rs.getInt (4);
217                     m_C_Currency_ID = rs.getInt (6);
218                     //
219                     m_calculated = true;
220                     break;
221                 }
222             }
223             rs.close();
224             pstmt.close();
225         }
226         catch (Exception   e)
227         {
228             Log.error("MProductPrice.calculatePL", e);
229             m_calculated = false;
230         }
231         return m_calculated;
232     }   //  calculatePL
233 
234     /**
235      *  Calculate Price based on Base Price List
236      *  @return true if calculated
237      */
238     public boolean calculateBPL()
239     {
240         if (m_M_Product_ID == 0 || m_M_PriceList_ID == 0)
241             return false;
242         //
243         String   sql = "SELECT BOM_PriceStd(p.M_Product_ID,pv.M_PriceList_Version_ID) AS PriceStd,"   //  1
244             + " BOM_PriceList(p.M_Product_ID,pv.M_PriceList_Version_ID) AS PriceList,"      //  2
245             + " BOM_PriceLimit(p.M_Product_ID,pv.M_PriceList_Version_ID) AS PriceLimit,"    //  3
246             + " p.C_UOM_ID,pv.ValidFrom,pl.C_Currency_ID "          // 4..6
247             + "FROM M_Product p"
248             + " INNER JOIN M_ProductPrice pp ON (p.M_Product_ID=pp.M_Product_ID)"
249             + " INNER JOIN  M_PriceList_Version pv ON (pp.M_PriceList_Version_ID=pv.M_PriceList_Version_ID)"
250             + " INNER JOIN M_Pricelist bpl ON (pv.M_PriceList_ID=bpl.M_PriceList_ID)"
251             + " INNER JOIN M_Pricelist pl ON (bpl.M_PriceList_ID=pl.BasePriceList_ID) "
252             + "WHERE pv.IsActive='Y'"
253             + " AND p.M_Product_ID=?"               //  #1
254             + " AND pl.M_PriceList_ID=?"            //  #2
255             + "ORDER BY pv.ValidFrom DESC";
256         m_calculated = false;
257         if (m_PriceDate == null)
258             m_PriceDate = new Timestamp (System.currentTimeMillis());
259         try
260         {
261             PreparedStatement pstmt = DB.prepareStatement(sql);
262             pstmt.setInt(1, m_M_Product_ID);
263             pstmt.setInt(2, m_M_PriceList_ID);
264             ResultSet rs = pstmt.executeQuery();
265             while (!m_calculated && rs.next())
266             {
267                 Timestamp plDate = rs.getTimestamp(5);
268                 //  we have the price list
269                 //  if order date is after or equal PriceList validFrom
270                 if (plDate == null || !m_PriceDate.before(plDate))
271                 {
272                     //  Prices
273                     m_PriceStd = rs.getBigDecimal (1);
274                     if (rs.wasNull ())
275                         m_PriceStd = Env.ZERO;
276                     m_PriceList = rs.getBigDecimal (2);
277                     if (rs.wasNull ())
278                         m_PriceList = Env.ZERO;
279                     m_PriceLimit = rs.getBigDecimal (3);
280                     if (rs.wasNull ())
281                         m_PriceLimit = Env.ZERO;
282                         //
283                     m_C_UOM_ID = rs.getInt (4);
284                     m_C_Currency_ID = rs.getInt (6);
285                     //
286                     m_calculated = true;
287                     break;
288                 }
289             }
290             rs.close();
291             pstmt.close();
292         }
293         catch (Exception   e)
294         {
295             Log.error("MProductPrice.calculateBPL", e);
296             m_calculated = false;
297         }
298         return m_calculated;
299     }   //  calculateBPL
300 
301     /**
302      *  Set Base Info (UOM)
303      */
304     public void setBaseInfo()
305     {
306         if (m_M_Product_ID == 0)
307             return;
308         //
309         String   sql = "SELECT C_UOM_ID FROM M_Product WHERE M_Product_ID=?";
310         try
311         {
312             PreparedStatement pstmt = DB.prepareStatement(sql);
313             pstmt.setInt(1, m_M_Product_ID);
314             ResultSet rs = pstmt.executeQuery();
315             if (rs.next())
316             {
317                 m_C_UOM_ID = rs.getInt (1);
318             }
319             rs.close();
320             pstmt.close();
321         }
322         catch (Exception   e)
323         {
324             Log.error("MProductPrice.setBaseInfo", e);
325         }
326         return;
327     }   //  setBaseInfo
328 
329     /**
330      *  Calculate Discount based on Standard/List Price
331      *  @return Discount
332      */
333     public BigDecimal getDiscount()
334     {
335         BigDecimal Discount = Env.ZERO;
336         if (m_PriceList.intValue() != 0)
337             Discount = new BigDecimal ((m_PriceList.doubleValue() - m_PriceStd.doubleValue())
338                 / m_PriceList.doubleValue() * 100.0);
339         if (Discount.scale() > 2)
340             Discount = Discount.setScale(2, BigDecimal.ROUND_HALF_UP);
341         return Discount;
342     }   //  getDiscount
343 
344 
345 
346 
347     public int getM_Product_ID()
348     {
349         return m_M_Product_ID;
350     }
351     public int getM_PriceList_ID()
352     {
353         return m_M_PriceList_ID;
354     }
355     public void setM_PriceList_ID( int M_PriceList_ID)
356     {
357         m_M_PriceList_ID = M_PriceList_ID;
358     }
359     public int getM_PriceList_Version_ID()
360     {
361         return m_M_PriceList_Version_ID;
362     }
363     public void setM_PriceList_Version_ID (int M_PriceList_Version_ID)
364     {
365         m_M_PriceList_Version_ID = M_PriceList_Version_ID;
366     }
367     public int getC_UOM_ID()
368     {
369         if (!m_calculated)
370             calculatePrice();
371         return m_C_UOM_ID;
372     }
373     public void setC_UOM_ID(int c_UOM_ID)
374     {
375         m_C_UOM_ID = c_UOM_ID;
376     }
377     public Timestamp getPriceDate()
378     {
379         return m_PriceDate;
380     }
381     public void setPriceDate(Timestamp priceDate)
382     {
383         m_PriceDate = priceDate;
384     }
385     public BigDecimal getPriceList()
386     {
387         if (!m_calculated)
388             calculatePrice();
389         return m_PriceList;
390     }
391     public BigDecimal getPriceStd()
392     {
393         if (!m_calculated)
394             calculatePrice();
395         return m_PriceStd;
396     }
397     public BigDecimal getPriceLimit()
398     {
399         if (!m_calculated)
400             calculatePrice();
401         return m_PriceLimit;
402     }
403     public int getC_Currency_ID()
404     {
405         if (!m_calculated)
406             calculatePrice();
407         return m_C_Currency_ID;
408     }
409 
410 }   //  MProductPrice
411
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags