public Dictionary <String, String> GetDiscount(Ctx ctx, string fields)
{
string[] paramValue = fields.Split(',');
int M_DiscountSchema_ID;
M_DiscountSchema_ID = Util.GetValueOfInt(paramValue[0].ToString());
MDiscountSchema discountschema = new MDiscountSchema(ctx, M_DiscountSchema_ID, null);
Dictionary <String, String> retDic = new Dictionary <string, string>();
retDic["AD_Org_ID"] = Util.GetValueOfString(discountschema.GetAD_Org_ID());
retDic["DiscountType"] = discountschema.GetDiscountType();
if (discountschema.GetFlatDiscount() > 0)
{
retDic["FlatDiscount"] = Util.GetValueOfString(discountschema.GetFlatDiscount());
}
else
{
retDic["FlatDiscount"] = "0";
}
retDic["IsBPartnerFlatDiscount"] = Util.GetValueOfString(discountschema.IsBPartnerFlatDiscount());
retDic["IsQuantityBased"] = Util.GetValueOfString(discountschema.IsQuantityBased());
retDic["M_DiscountSchema_ID"] = Util.GetValueOfString(discountschema.GetM_DiscountSchema_ID());
retDic["Name"] = discountschema.GetName();
return(retDic);
}
public string GetDiscountType(Ctx ctx, string fields)
{
string[] paramValue = fields.Split(',');
int M_DiscountSchema_ID;
M_DiscountSchema_ID = Util.GetValueOfInt(paramValue[0].ToString());
MDiscountSchema discountschema = new MDiscountSchema(ctx, M_DiscountSchema_ID, null);
return(discountschema.GetDiscountType());
}
/// <summary>
/// Execute
/// </summary>
/// <returns>info</returns>
protected override String DoIt()
{
log.Info("M_DiscountSchema_ID=" + _M_DiscountSchema_ID);
if (_M_DiscountSchema_ID == 0)
{
throw new Exception("@M_DiscountSchema_ID@ = 0");
}
MDiscountSchema ds = new MDiscountSchema(GetCtx(), _M_DiscountSchema_ID, Get_TrxName());
if (ds.Get_ID() == 0)
{
throw new Exception("@NotFound@ M_DiscountSchema_ID=" + _M_DiscountSchema_ID);
}
//
int updated = ds.ReSeq();
return("@Updated@ #" + updated);
}
/// <summary>
/// Create Price List
/// </summary>
/// <returns>info message</returns>
private String Create()
{
StringBuilder info = new StringBuilder();
// Delete Old Data
if (_deleteOld)
{
int no = DataBase.DB.ExecuteQuery(
"DELETE M_ProductPrice "
+ "WHERE M_PriceList_Version_ID=" + _M_PriceList_Version_ID, null,
Get_TrxName());
log.Info("Deleted=" + no);
info.Append("@Deleted@=").Append(no).Append(" - ");
}
int M_Pricelist_Version_Base_ID = _plv.GetM_Pricelist_Version_Base_ID();
MPriceList pl = _plv.GetPriceList();
int curPrecision = pl.GetStandardPrecision();
/**
* For All Discount Lines in Sequence
*/
MDiscountSchema ds = new MDiscountSchema(GetCtx(), _plv.GetM_DiscountSchema_ID(), Get_TrxName());
MDiscountSchemaLine[] dsl = ds.GetLines(false);
for (int i = 0; i < dsl.Length; i++)
{
MDiscountSchemaLine dsLine = dsl[i];
String message = "#" + dsLine.GetSeqNo();
String dd = dsLine.GetDescription();
if (dd != null && dd.Length > 0)
{
message += " " + dd;
}
// Clear Temporary Table
int noDeleted = DataBase.DB.ExecuteQuery("DELETE FROM T_Selection", null, Get_TrxName());
// Create Selection in Temporary Table
String sql = null;
int M_DiscountSchemaLine_ID = dsLine.GetM_DiscountSchemaLine_ID();
int p2 = M_Pricelist_Version_Base_ID;
if (p2 == 0) // Create from PO
{
p2 = dsLine.GetAD_Client_ID();
sql = "INSERT INTO T_Selection (T_Selection_ID) "
+ "SELECT DISTINCT po.M_Product_ID "
+ "FROM M_Product_PO po "
+ " INNER JOIN M_Product p ON (p.M_Product_ID=po.M_Product_ID)"
+ " INNER JOIN M_DiscountSchemaLine dl ON (dl.M_DiscountSchemaLine_ID=" + M_DiscountSchemaLine_ID + ") " // #1
+ "WHERE p.AD_Client_ID IN (" + p2 + ", 0)" // #2
+ " AND p.IsActive='Y' AND po.IsActive='Y' AND po.IsCurrentVendor='Y'"
// Optional Restrictions
+ " AND (dl.M_Product_Category_ID IS NULL OR p.M_Product_Category_ID=dl.M_Product_Category_ID)"
+ " AND (dl.C_BPartner_ID IS NULL OR po.C_BPartner_ID=dl.C_BPartner_ID)"
+ " AND (dl.M_Product_ID IS NULL OR p.M_Product_ID=dl.M_Product_ID)";
}
else // Create from Price List **
{
sql = "INSERT INTO T_Selection (T_Selection_ID) "
+ "SELECT DISTINCT p.M_Product_ID "
+ "FROM M_ProductPrice pp"
+ " INNER JOIN M_Product p ON (p.M_Product_ID=pp.M_Product_ID)"
+ " INNER JOIN M_DiscountSchemaLine dl ON (dl.M_DiscountSchemaLine_ID=" + M_DiscountSchemaLine_ID + ") " // #1
+ "WHERE pp.M_PriceList_Version_ID=" + p2 //#2 PriceList_Version_Base_ID
+ " AND p.IsActive='Y' AND pp.IsActive='Y'"
// Optional Restrictions
+ " AND (dl.M_Product_Category_ID IS NULL OR p.M_Product_Category_ID=dl.M_Product_Category_ID)"
+ " AND (dl.C_BPartner_ID IS NULL OR EXISTS "
+ "(SELECT * FROM M_Product_PO po "
+ "WHERE po.M_Product_ID=p.M_Product_ID AND po.C_BPartner_ID=dl.C_BPartner_ID))"
+ " AND (dl.M_Product_ID IS NULL OR p.M_Product_ID=dl.M_Product_ID)";
}
//idr = DataBase.prepareStatement(sql, get_TrxName());
int noSelected = DataBase.DB.ExecuteQuery(sql, null, Get_TrxName());
message += ": @Selected@=" + noSelected;
// Delete Prices in Selection, so that we can insert
if (M_Pricelist_Version_Base_ID == 0 ||
M_Pricelist_Version_Base_ID != _M_PriceList_Version_ID)
{
sql = "DELETE FROM M_ProductPrice pp "
+ "WHERE pp.M_PriceList_Version_ID=" + _M_PriceList_Version_ID
+ " AND EXISTS (SELECT * FROM T_Selection s WHERE pp.M_Product_ID=s.T_Selection_ID)";
noDeleted = DataBase.DB.ExecuteQuery(sql, null, Get_TrxName());
message += ", @Deleted@=" + noDeleted;
}
// Copy (Insert) Prices
int noInserted = 0;
sql = "INSERT INTO M_ProductPrice "
+ "(M_PriceList_Version_ID, M_Product_ID,"
+ " AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,"
+ " PriceList, PriceStd, PriceLimit) ";
//
if (M_Pricelist_Version_Base_ID == _M_PriceList_Version_ID)
{
sql = null; // We have Prices already
}
else if (M_Pricelist_Version_Base_ID == 0)
{
/** Copy and Convert from Product_PO */
sql += "SELECT plv.M_PriceList_Version_ID, po.M_Product_ID,"
+ " plv.AD_Client_ID, plv.AD_Org_ID, 'Y', SysDate, plv.UpdatedBy, SysDate, plv.UpdatedBy,"
// Price List
+ " COALESCE(currencyConvert(po.PriceList,"
+ " po.C_Currency_ID, pl.C_Currency_ID, dl.ConversionDate, dl.C_ConversionType_ID, plv.AD_Client_ID, plv.AD_Org_ID), -po.PriceList),"
// Price Std
+ " COALESCE(currencyConvert(po.PriceList,"
+ " po.C_Currency_ID, pl.C_Currency_ID, dl.ConversionDate, dl.C_ConversionType_ID, plv.AD_Client_ID, plv.AD_Org_ID), -po.PriceList),"
// Price Limit
+ " COALESCE(currencyConvert(po.PricePO,"
+ " po.C_Currency_ID, pl.C_Currency_ID, dl.ConversionDate, dl.C_ConversionType_ID, plv.AD_Client_ID, plv.AD_Org_ID), -po.PricePO) "
//
+ "FROM M_Product_PO po"
+ " INNER JOIN M_PriceList_Version plv ON (plv.M_PriceList_Version_ID=" + _M_PriceList_Version_ID + ")" // #1
+ " INNER JOIN M_PriceList pl ON (pl.M_PriceList_ID=plv.M_PriceList_ID)"
+ " INNER JOIN M_DiscountSchemaLine dl ON (dl.M_DiscountSchemaLine_ID=" + M_DiscountSchemaLine_ID + ") " // #2
//
+ "WHERE EXISTS (SELECT * FROM T_Selection s WHERE po.M_Product_ID=s.T_Selection_ID)"
+ " AND po.IsCurrentVendor='Y' AND po.IsActive='Y'";
}
else
{
/** Copy and Convert from other PriceList_Version */
sql += "SELECT plv.M_PriceList_Version_ID, pp.M_Product_ID,"
+ " plv.AD_Client_ID, plv.AD_Org_ID, 'Y', SysDate, plv.UpdatedBy, SysDate, plv.UpdatedBy,"
// Price List
+ " COALESCE(currencyConvert(pp.PriceList,"
+ " bpl.C_Currency_ID, pl.C_Currency_ID, dl.ConversionDate, dl.C_ConversionType_ID, plv.AD_Client_ID, plv.AD_Org_ID), -pp.PriceList),"
// Price Std
+ " COALESCE(currencyConvert(pp.PriceStd,"
+ " bpl.C_Currency_ID, pl.C_Currency_ID, dl.ConversionDate, dl.C_ConversionType_ID, plv.AD_Client_ID, plv.AD_Org_ID), -pp.PriceStd),"
// Price Limit
+ " COALESCE(currencyConvert(pp.PriceLimit,"
+ " bpl.C_Currency_ID, pl.C_Currency_ID, dl.ConversionDate, dl.C_ConversionType_ID, plv.AD_Client_ID, plv.AD_Org_ID), -pp.PriceLimit) "
//
+ "FROM M_ProductPrice pp"
+ " INNER JOIN M_PriceList_Version plv ON (plv.M_PriceList_Version_ID=" + _M_PriceList_Version_ID + ")" // #1
+ " INNER JOIN M_PriceList pl ON (pl.M_PriceList_ID=plv.M_PriceList_ID)"
+ " INNER JOIN M_PriceList_Version bplv ON (pp.M_PriceList_Version_ID=bplv.M_PriceList_Version_ID)"
+ " INNER JOIN M_PriceList bpl ON (bplv.M_PriceList_ID=bpl.M_PriceList_ID)"
+ " INNER JOIN M_DiscountSchemaLine dl ON (dl.M_DiscountSchemaLine_ID=" + M_DiscountSchemaLine_ID + ") " // #2
//
+ "WHERE ";
if (M_Pricelist_Version_Base_ID != 0)
{
sql += "pp.M_PriceList_Version_ID=" + M_Pricelist_Version_Base_ID + " AND"; // #3 M_PriceList_Version_Base_ID
}
sql += " EXISTS (SELECT * FROM T_Selection s WHERE pp.M_Product_ID=s.T_Selection_ID)"
+ " AND pp.IsActive='Y'";
}
if (sql != null)
{
//pstmt = DataBase.prepareStatement(sql, get_TrxName());
//pstmt.setInt(1, _M_PriceList_Version_ID);
// pstmt.setInt(2, M_DiscountSchemaLine_ID);
//if (M_Pricelist_Version_Base_ID != 0)
//{
// pstmt.setInt(3, M_Pricelist_Version_Base_ID);
// }
noInserted = DataBase.DB.ExecuteQuery(sql, null, Get_TrxName());
message += " @Inserted@=" + noInserted;
}
/** Calculations **/
MProductPrice[] pp = _plv.GetProductPrice(
"AND EXISTS (SELECT * FROM T_Selection s "
+ "WHERE s.T_Selection_ID=M_ProductPrice.M_Product_ID)");
for (int j = 0; j < pp.Length; j++)
{
MProductPrice price = pp[j];
Decimal priceList = price.GetPriceList();
Decimal priceStd = price.GetPriceStd();
Decimal priceLimit = price.GetPriceLimit();
//
price.SetPriceList(Calculate(dsLine.GetList_Base(),
priceList, priceStd, priceLimit, dsLine.GetList_Fixed(),
dsLine.GetList_AddAmt(), dsLine.GetList_Discount(),
dsLine.GetList_Rounding(), curPrecision));
price.SetPriceStd(Calculate(dsLine.GetStd_Base(),
priceList, priceStd, priceLimit, dsLine.GetStd_Fixed(),
dsLine.GetStd_AddAmt(), dsLine.GetStd_Discount(),
dsLine.GetStd_Rounding(), curPrecision));
price.SetPriceLimit(Calculate(dsLine.GetLimit_Base(),
priceList, priceStd, priceLimit, dsLine.GetLimit_Fixed(),
dsLine.GetLimit_AddAmt(), dsLine.GetLimit_Discount(),
dsLine.GetLimit_Rounding(), curPrecision));
price.Save();
} // for all products
// Clear Temporary Table
noDeleted = DataBase.DB.ExecuteQuery("DELETE FROM T_Selection", null, Get_TrxName());
//
AddLog(message);
} // for all lines
MProductPrice[] ppl = _plv.GetProductPrice(true);
info.Append(" - @Records@=").Append(ppl.Length);
return(info.ToString());
}
