public static BigDecimal calculateAverageInv(MProduct product,int M_AttributeSetInstance_ID,MAcctSchema as,int AD_Org_ID){
  String sql="SELECT t.MovementQty, mi.Qty, il.QtyInvoiced, il.PriceActual," + " i.C_Currency_ID, i.DateAcct, i.C_ConversionType_ID, i.AD_Client_ID, i.AD_Org_ID, t.M_Transaction_ID " + "FROM M_Transaction t"+ " INNER JOIN M_MatchInv mi ON (t.M_InOutLine_ID=mi.M_InOutLine_ID)"+ " INNER JOIN C_InvoiceLine il ON (mi.C_InvoiceLine_ID=il.C_InvoiceLine_ID)"+ " INNER JOIN C_Invoice i ON (il.C_Invoice_ID=i.C_Invoice_ID) "+ "WHERE t.M_Product_ID=?";
  if (AD_Org_ID != 0)   sql+=" AND t.AD_Org_ID=?";
 else   if (M_AttributeSetInstance_ID != 0)   sql+=" AND t.M_AttributeSetInstance_ID=?";
  sql+=" ORDER BY t.M_Transaction_ID";
  PreparedStatement pstmt=null;
  ResultSet rs=null;
  BigDecimal newStockQty=Env.ZERO;
  BigDecimal newAverageAmt=Env.ZERO;
  int oldTransaction_ID=0;
  try {
    pstmt=DB.prepareStatement(sql,null);
    pstmt.setInt(1,product.getM_Product_ID());
    if (AD_Org_ID != 0)     pstmt.setInt(2,AD_Org_ID);
 else     if (M_AttributeSetInstance_ID != 0)     pstmt.setInt(2,M_AttributeSetInstance_ID);
    rs=pstmt.executeQuery();
    while (rs.next()) {
      BigDecimal oldStockQty=newStockQty;
      BigDecimal movementQty=rs.getBigDecimal(1);
      int M_Transaction_ID=rs.getInt(10);
      if (M_Transaction_ID != oldTransaction_ID)       newStockQty=oldStockQty.add(movementQty);
      M_Transaction_ID=oldTransaction_ID;
      BigDecimal matchQty=rs.getBigDecimal(2);
      if (matchQty == null) {
        s_log.finer("Movement=" + movementQty + ", StockQty="+ newStockQty);
        continue;
      }
      BigDecimal price=rs.getBigDecimal(4);
      int C_Currency_ID=rs.getInt(5);
      Timestamp DateAcct=rs.getTimestamp(6);
      int C_ConversionType_ID=rs.getInt(7);
      int Client_ID=rs.getInt(8);
      int Org_ID=rs.getInt(9);
      BigDecimal cost=MConversionRate.convert(product.getCtx(),price,C_Currency_ID,as.getC_Currency_ID(),DateAcct,C_ConversionType_ID,Client_ID,Org_ID);
      BigDecimal oldAverageAmt=newAverageAmt;
      BigDecimal averageCurrent=oldStockQty.multiply(oldAverageAmt);
      BigDecimal averageIncrease=matchQty.multiply(cost);
      BigDecimal newAmt=averageCurrent.add(averageIncrease);
      newAmt=newAmt.setScale(as.getCostingPrecision(),BigDecimal.ROUND_HALF_UP);
      newAverageAmt=newAmt.divide(newStockQty,as.getCostingPrecision(),BigDecimal.ROUND_HALF_UP);
      s_log.finer("Movement=" + movementQty + ", StockQty="+ newStockQty+ ", Match="+ matchQty+ ", Cost="+ cost+ ", NewAvg="+ newAverageAmt);
    }
  }
 catch (  SQLException e) {
    throw new DBException(e,sql);
  }
 finally {
    DB.close(rs,pstmt);
    rs=null;
    pstmt=null;
  }
  if (newAverageAmt != null && newAverageAmt.signum() != 0) {
    s_log.finer(product.getName() + " = " + newAverageAmt);
    return newAverageAmt;
  }
  return null;
}

public static BigDecimal calculateAverageInv ( MProduct product , int M_AttributeSetInstance_ID , MAcctSchema as , int AD_Org_ID ) { String sql = "SELECT t.MovementQty, mi.Qty, il.QtyInvoiced, il.PriceActual," + " i.C_Currency_ID, i.DateAcct, i.C_ConversionType_ID, i.AD_Client_ID, i.AD_Org_ID, t.M_Transaction_ID " + "FROM M_Transaction t" + " INNER JOIN M_MatchInv mi ON (t.M_InOutLine_ID=mi.M_InOutLine_ID)" + " INNER JOIN C_InvoiceLine il ON (mi.C_InvoiceLine_ID=il.C_InvoiceLine_ID)" + " INNER JOIN C_Invoice i ON (il.C_Invoice_ID=i.C_Invoice_ID) " + "WHERE t.M_Product_ID=?" ; if ( AD_Org_ID != 0 ) sql += " AND t.AD_Org_ID=?" ; else if ( M_AttributeSetInstance_ID != 0 ) sql += " AND t.M_AttributeSetInstance_ID=?" ; sql += " ORDER BY t.M_Transaction_ID" ; PreparedStatement pstmt = null ; ResultSet rs = null ; BigDecimal newStockQty = Env . ZERO ; BigDecimal newAverageAmt = Env . ZERO ; int oldTransaction_ID = 0 ; try { pstmt = DB . prepareStatement ( sql , null ) ; pstmt . setInt ( 1 , product . getM_Product_ID ( ) ) ; if ( AD_Org_ID != 0 ) pstmt . setInt ( 2 , AD_Org_ID ) ; else if ( M_AttributeSetInstance_ID != 0 ) pstmt . setInt ( 2 , M_AttributeSetInstance_ID ) ; rs = pstmt . executeQuery ( ) ; while ( rs . next ( ) ) { BigDecimal oldStockQty = newStockQty ; BigDecimal movementQty = rs . getBigDecimal ( 1 ) ; int M_Transaction_ID = rs . getInt ( 10 ) ; if ( M_Transaction_ID != oldTransaction_ID ) newStockQty = oldStockQty . add ( movementQty ) ; M_Transaction_ID = oldTransaction_ID ; BigDecimal matchQty = rs . getBigDecimal ( 2 ) ; if ( matchQty == null ) { s_log . finer ( "Movement=" + movementQty + ", StockQty=" + newStockQty ) ; continue ; } BigDecimal price = rs . getBigDecimal ( 4 ) ; int C_Currency_ID = rs . getInt ( 5 ) ; Timestamp DateAcct = rs . getTimestamp ( 6 ) ; int C_ConversionType_ID = rs . getInt ( 7 ) ; int Client_ID = rs . getInt ( 8 ) ; int Org_ID = rs . getInt ( 9 ) ; BigDecimal cost = MConversionRate . convert ( product . getCtx ( ) , price , C_Currency_ID , as . getC_Currency_ID ( ) , DateAcct , C_ConversionType_ID , Client_ID , Org_ID ) ; BigDecimal oldAverageAmt = newAverageAmt ; BigDecimal averageCurrent = oldStockQty . multiply ( oldAverageAmt ) ; BigDecimal averageIncrease = matchQty . multiply ( cost ) ; BigDecimal newAmt = averageCurrent . add ( averageIncrease ) ; newAmt = newAmt . setScale ( as . getCostingPrecision ( ) , BigDecimal . ROUND_HALF_UP ) ; newAverageAmt = newAmt . divide ( newStockQty , as . getCostingPrecision ( ) , BigDecimal . ROUND_HALF_UP ) ; s_log . finer ( "Movement=" + movementQty + ", StockQty=" + newStockQty + ", Match=" + matchQty + ", Cost=" + cost + ", NewAvg=" + newAverageAmt ) ; } } catch ( SQLException e ) { throw new DBException ( e , sql ) ; } finally { DB . close ( rs , pstmt ) ; rs = null ; pstmt = null ; } if ( newAverageAmt != null && newAverageAmt . signum ( ) != 0 ) { s_log . finer ( product . getName ( ) + " = " + newAverageAmt ) ; return newAverageAmt ; } return null ; }
calculate average invoice from trx
