/// <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());
}