/// <summary> /// Get PO Price from PriceList - and convert it to AcctSchema Currency /// </summary> /// <param name="as1">accounting schema</param> /// <param name="onlyPOPriceList">use only PO price list</param> /// <returns>po price</returns> private Decimal?GetPriceList(MAcctSchema as1, bool onlyPOPriceList) { StringBuilder sql = new StringBuilder( "SELECT pl.C_Currency_ID, pp.PriceList, pp.PriceStd, pp.PriceLimit " + "FROM M_PriceList pl, M_PriceList_Version plv, M_ProductPrice pp " + "WHERE pl.M_PriceList_ID = plv.M_PriceList_ID" + " AND plv.M_PriceList_Version_ID = pp.M_PriceList_Version_ID" + " AND pp.M_Product_ID=" + _M_Product_ID); if (onlyPOPriceList) { sql.Append(" AND pl.IsSOPriceList='N'"); } sql.Append(" ORDER BY pl.IsSOPriceList ASC, plv.ValidFrom DESC"); int C_Currency_ID = 0; Decimal? PriceList = null; Decimal? PriceStd = null; Decimal? PriceLimit = null; IDataReader idr = null; try { idr = DataBase.DB.ExecuteReader(sql.ToString(), null, null); if (idr.Read()) { C_Currency_ID = Utility.Util.GetValueOfInt(idr[0]); //.getInt(1); PriceList = Utility.Util.GetValueOfDecimal(idr[1]); //.getBigDecimal(2); PriceStd = Utility.Util.GetValueOfDecimal(idr[2]); //.getBigDecimal(3); PriceLimit = Utility.Util.GetValueOfDecimal(idr[3]); //.getBigDecimal(4); } idr.Close(); } catch (Exception e) { log.Log(Level.SEVERE, sql.ToString(), e); } // nothing found if (C_Currency_ID == 0) { return(null); } Decimal?price = PriceLimit; // best bet if (price == null || price.Equals(Env.ZERO)) { price = PriceStd; } if (price == null || price.Equals(Env.ZERO)) { price = PriceList; } // Convert if (price != null && !price.Equals(Env.ZERO)) { price = MConversionRate.Convert(as1.GetCtx(), Utility.Util.GetValueOfDecimal(price), C_Currency_ID, as1.GetC_Currency_ID(), as1.GetAD_Client_ID(), 0); } return(price); }
/// <summary> /// Get PO Cost from Purchase Info - and convert it to AcctSchema Currency /// </summary> /// <param name="as1">accounting schema</param> /// <returns>po cost</returns> private Decimal?GetPOCost(MAcctSchema as1) { String sql = "SELECT C_Currency_ID, PriceList,PricePO,PriceLastPO " + "FROM M_Product_PO WHERE M_Product_ID=" + _M_Product_ID + "ORDER BY IsCurrentVendor DESC"; int C_Currency_ID = 0; Decimal? PriceList = null; Decimal? PricePO = null; Decimal? PriceLastPO = null; IDataReader idr = null; try { idr = DataBase.DB.ExecuteReader(sql, null, null); if (idr.Read()) { C_Currency_ID = Utility.Util.GetValueOfInt(idr[0]); //.getInt(1); PriceList = Utility.Util.GetValueOfDecimal(idr[1]); //.getBigDecimal(2); PricePO = Utility.Util.GetValueOfDecimal(idr[2]); //.getBigDecimal(3); PriceLastPO = Utility.Util.GetValueOfDecimal(idr[3]); //.getBigDecimal(4); } idr.Close(); } catch (Exception e) { if (idr != null) { idr.Close(); idr = null; } log.Log(Level.SEVERE, sql, e); } // nothing found if (C_Currency_ID == 0) { return(null); } Decimal?cost = PriceLastPO; // best bet if (cost == null || cost.Equals(Env.ZERO)) { cost = PricePO; } if (cost == null || cost.Equals(Env.ZERO)) { cost = PriceList; } // Convert - standard precision!! - should be costing precision if (cost != null && !cost.Equals(Env.ZERO)) { cost = MConversionRate.Convert(as1.GetCtx(), Utility.Util.GetValueOfDecimal(cost), C_Currency_ID, as1.GetC_Currency_ID(), as1.GetAD_Client_ID(), as1.GetAD_Org_ID()); } return(cost); }
/// <summary> /// Parent Constructor /// </summary> /// <param name="as1">accounting schema</param> public MAcctSchemaElement(MAcctSchema as1) : this(as1.GetCtx(), 0, as1.Get_TrxName()) { SetClientOrg(as1); SetC_AcctSchema_ID(as1.GetC_AcctSchema_ID()); // setC_Element_ID (0); // setElementType (null); // setName (null); // setSeqNo (0); }
/// <summary> /// Factory: Return ArrayList of Account Schema Elements /// </summary> /// <param name="as1">Accounting Schema</param> /// <returns>ArrayList with Elements</returns> public static MAcctSchemaElement[] GetAcctSchemaElements(MAcctSchema as1) { int key = as1.GetC_AcctSchema_ID(); MAcctSchemaElement[] retValue = (MAcctSchemaElement[])s_cache[key]; if (retValue != null) { return(retValue); } _log.Fine("C_AcctSchema_ID=" + as1.GetC_AcctSchema_ID()); List <MAcctSchemaElement> list = new List <MAcctSchemaElement>(); // String sql = "SELECT * FROM C_AcctSchema_Element " + "WHERE C_AcctSchema_ID=" + as1.GetC_AcctSchema_ID() + " AND IsActive='Y' ORDER BY SeqNo"; try { DataSet ds = DataBase.DB.ExecuteDataset(sql, null, as1.Get_TrxName()); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { DataRow dr = ds.Tables[0].Rows[i]; MAcctSchemaElement ase = new MAcctSchemaElement(as1.GetCtx(), dr, as1.Get_TrxName()); _log.Fine(" - " + ase); if (ase.IsMandatory() && ase.GetDefaultValue() == 0) { _log.Log(Level.SEVERE, "No default value for " + ase.GetName()); } list.Add(ase); } } catch (Exception e) { _log.Log(Level.SEVERE, sql, e); } retValue = new MAcctSchemaElement[list.Count]; retValue = list.ToArray(); s_cache.Add(key, retValue); return(retValue); }
/** * Get Charge Account * @param C_Charge_ID charge * @param as account schema * @param amount amount for expense(+)/revenue(-) * @return Charge Account or null */ public static MAccount GetAccount(int C_Charge_ID, MAcctSchema aSchema, Decimal amount) { if (C_Charge_ID == 0 || aSchema == null) { return(null); } int acct_index = 1; // Expense (positive amt) if (amount < 0) { acct_index = 2; // Revenue (negative amt) } String sql = "SELECT CH_Expense_Acct, CH_Revenue_Acct FROM C_Charge_Acct WHERE C_Charge_ID=" + C_Charge_ID + " AND C_AcctSchema_ID=" + aSchema.GetC_AcctSchema_ID(); int Account_ID = 0; IDataReader dr = null; try { // PreparedStatement pstmt = DataBase.prepareStatement(sql, null); // pstmt.setInt (1, C_Charge_ID); // pstmt.setInt (2, aSchema.getC_AcctSchema_ID()); // ResultSet dr = pstmt.executeQuery(); dr = DataBase.DB.ExecuteReader(sql, null, null); if (dr.Read()) { Account_ID = Utility.Util.GetValueOfInt(dr[acct_index - 1].ToString()); } dr.Close(); //pstmt.close(); } catch (SqlException e) { if (dr != null) { dr.Close(); } _log.Log(Level.SEVERE, sql, e); return(null); } finally { if (dr != null) { dr.Close(); } } // No account if (Account_ID == 0) { _log.Severe("NO account for C_Charge_ID=" + C_Charge_ID); return(null); } // Return Account MAccount acct = MAccount.Get(aSchema.GetCtx(), Account_ID); return(acct); } // getAccount
/// <summary> /// Account from Default Product Category /// </summary> /// <param name="AcctType"></param> /// <param name="as1">accounting schema</param> /// <returns> Requested Product Account</returns> public MAccount GetAccountDefault(int AcctType, MAcctSchema as1) { // if (AcctType < 1 || AcctType > 10) //Updated By raghu 7,jun,2011 if (AcctType < 1 || AcctType > 12) { return(null); } //String sql = "SELECT P_Revenue_Acct, P_Expense_Acct, P_Asset_Acct, P_Cogs_Acct, " // + "P_PurchasePriceVariance_Acct, P_InvoicePriceVariance_Acct, " // + "P_TradeDiscountRec_Acct, P_TradeDiscountGrant_Acct, " // + "P_CostAdjustment_Acct, P_InventoryClearing_Acct " // + "FROM M_Product_Category pc, M_Product_Category_Acct pca " // + "WHERE pc.M_Product_Category_ID=pca.M_Product_Category_ID" // + " AND pca.C_AcctSchema_ID=" + as1.GetC_AcctSchema_ID() // + "ORDER BY pc.IsDefault DESC, pc.Created"; //Updated By raghu 7,jun,2011 /*****************Manfacturing*********************/ String sql = "SELECT COALESCE(a.P_Revenue_Acct, b.P_Revenue_Acct), " //1 + " COALESCE(a.P_Expense_Acct, b.P_Expense_Acct), " //2 + " COALESCE(a.P_Asset_Acct, b.P_Asset_Acct), " //3 + " COALESCE(a.P_Cogs_Acct, b.P_Cogs_Acct), " //4 + " COALESCE(a.P_PurchasePriceVariance_Acct, b.P_PurchasePriceVariance_Acct), " //5 + " COALESCE(a.P_InvoicePriceVariance_Acct, b.P_InvoicePriceVariance_Acct), " //6 + " COALESCE(a.P_TradeDiscountRec_Acct, b.P_TradeDiscountRec_Acct), " //7 + " COALESCE(a.P_TradeDiscountGrant_Acct, b.P_TradeDiscountGrant_Acct), " //8 + " COALESCE(a.P_CostAdjustment_Acct, b.P_CostAdjustment_Acct), " //9 + " COALESCE(a.P_InventoryClearing_Acct, b.P_InventoryClearing_Acct), " //10 + " COALESCE(a.P_Resource_Absorption_Acct, b.P_Resource_Absorption_Acct), " //11 + " COALESCE(a.P_MaterialOverhd_Acct, b.P_MaterialOverhd_Acct) " //12 + " FROM C_AcctSchema_Default b " + " LEFT OUTER JOIN M_Product_Category_Acct a ON (a.C_AcctSchema_ID = b.C_AcctSchema_ID " + " AND a.IsActive = 'Y' )," + " M_Product_Category pc " + " WHERE pc.M_Product_Category_ID=a.M_Product_Category_ID" + " AND b.C_AcctSchema_ID =" + as1.GetC_AcctSchema_ID() + " ORDER BY pc.IsDefault DESC, pc.Created"; /*****************Manfacturing*********************/ int validCombination_ID = 0; IDataReader idr = null; try { idr = DataBase.DB.ExecuteReader(sql, null, null); if (idr.Read()) { validCombination_ID = Utility.Util.GetValueOfInt(idr[AcctType - 1]); } idr.Close(); } catch (Exception e) { if (idr != null) { idr.Close(); idr = null; } log.Log(Level.SEVERE, sql, e); } if (validCombination_ID == 0) { return(null); } return(MAccount.Get(as1.GetCtx(), validCombination_ID)); }
/// <summary> /// Parent Constructor /// </summary> /// <param name="as1">as account schema</param> public MAccount(MAcctSchema as1) : this(as1.GetCtx(), 0, as1.Get_TrxName()) { SetClientOrg(as1); SetC_AcctSchema_ID(as1.GetC_AcctSchema_ID()); }