/// <summary> /// Getting Related Product Data /// </summary> /// <param name="ctx">Context</param> /// <param name="M_Product_ID">Product</param> /// <param name="M_AttributeSetInstance_ID">Attribute</param> /// <param name="M_PriceList_ID">Price List</param> /// <param name="Table_ID">Table ID</param> /// <param name="Record_ID">Record ID</param> /// <returns></returns> public List <RelatedProductData> GetRelatedProduct(Ctx ctx, int M_Product_ID, int M_AttributeSetInstance_ID, int C_UOM_ID, int M_PriceList_ID, string Relatedtype, int Table_ID, int Record_ID) { List <RelatedProductData> retData = null; RelatedProductData res; DateTime?validFrom = null; if (MOrder.Table_ID.Equals(Table_ID)) { validFrom = Util.GetValueOfDateTime(DB.ExecuteScalar("SELECT DateOrdered FROM C_Order WHERE C_Order_ID = " + Record_ID)); } if (validFrom == null) { validFrom = DateTime.Now.Date; } int M_PriceList_Version_ID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT M_PriceList_Version_ID FROM M_PriceList_Version WHERE IsActive = 'Y' AND M_PriceList_ID = " + M_PriceList_ID + @" AND ValidFrom <= " + DB.TO_DATE(validFrom, true) + " ORDER BY ValidFrom DESC")); string sql = @"SELECT P.M_Product_ID, " + (C_UOM_ID > 0 ? C_UOM_ID.ToString() : "P.C_UOM_ID") + @" AS C_UOM_ID, P.Value, R.Name, 1 AS QtyEntered, R.RelatedProductType, (SELECT Name FROM AD_Ref_List WHERE AD_Reference_ID=(SELECT AD_Reference_ID FROM AD_Reference WHERE Name='M_RelatedProduct Type') AND Value = R.RelatedProductType) AS RelatedType, BOMPRICELISTUOM(P.M_PRODUCT_ID, PR.M_PRICELIST_VERSION_ID, PR.M_ATTRIBUTESETINSTANCE_ID, PR.C_UOM_ID) AS PriceList, BOMPRICESTDUOM(P.M_PRODUCT_ID, PR.M_PRICELIST_VERSION_ID, PR.M_ATTRIBUTESETINSTANCE_ID, PR.C_UOM_ID) AS UnitPrice, BOMPRICELIMITUOM(P.M_PRODUCT_ID, PR.M_PRICELIST_VERSION_ID, PR.M_ATTRIBUTESETINSTANCE_ID, PR.C_UOM_ID) AS LimitPrice FROM M_RelatedProduct R INNER JOIN M_PRODUCT P ON (p.M_Product_ID = R.RelatedProduct_ID) LEFT JOIN M_PRODUCTPRICE PR ON ( P.M_PRODUCT_ID = PR.M_PRODUCT_ID AND PR.ISACTIVE = 'Y' AND NVL(PR.M_AttributeSetInstance_ID, 0) = " + M_AttributeSetInstance_ID + " AND PR.M_PRICELIST_VERSION_ID = " + M_PriceList_Version_ID + "AND PR.C_UOM_ID = " + (C_UOM_ID > 0 ? C_UOM_ID.ToString() : "P.C_UOM_ID") + @") WHERE R.M_Product_ID = " + M_Product_ID; if (!String.IsNullOrEmpty(Relatedtype)) { sql += " AND R.RelatedProductType = " + DB.TO_STRING(Relatedtype); } DataSet _ds = DB.ExecuteDataset(sql, null, null); if (_ds != null && _ds.Tables[0].Rows.Count > 0) { retData = new List <RelatedProductData>(); for (int i = 0; i < _ds.Tables[0].Rows.Count; i++) { res = new RelatedProductData(); res.Product_ID = Util.GetValueOfInt(_ds.Tables[0].Rows[i]["M_Product_ID"]); res.Product = Util.GetValueOfString(_ds.Tables[0].Rows[i]["Name"]); res.RelatedProductType = Util.GetValueOfString(_ds.Tables[0].Rows[i]["RelatedProductType"]); res.RelatedType = Util.GetValueOfString(_ds.Tables[0].Rows[i]["RelatedType"]); res.AttributeSetInstance_ID = M_AttributeSetInstance_ID; res.Value = Util.GetValueOfString(_ds.Tables[0].Rows[i]["Value"]); //res.UOM = Util.GetValueOfString(_ds.Tables[0].Rows[i]["UName"]); res.UOM_ID = Util.GetValueOfInt(_ds.Tables[0].Rows[i]["C_UOM_ID"]); res.QtyEntered = Util.GetValueOfDecimal(_ds.Tables[0].Rows[i]["QtyEntered"]); res.PriceStd = Util.GetValueOfDecimal(_ds.Tables[0].Rows[i]["UnitPrice"]); res.PriceList = Util.GetValueOfDecimal(_ds.Tables[0].Rows[i]["PriceList"]); retData.Add(res); } } return(retData); }
/// <summary> /// Getting Product Data /// </summary> /// <param name="ctx">Context</param> /// <param name="M_Product_ID">Product</param> /// <param name="M_AttributeSetInstance_ID">Attribute</param> /// <param name="M_PriceList_ID">Price List</param> /// <param name="Table_ID">Table ID</param> /// <param name="Record_ID">Record ID</param> /// <returns>Product Details</returns> public RelatedProductData GetProductData(Ctx ctx, int M_Product_ID, int M_AttributeSetInstance_ID, int C_UOM_ID, int M_PriceList_ID, int Table_ID, int Record_ID) { RelatedProductData res = null; DateTime? validFrom = null; if (MOrder.Table_ID.Equals(Table_ID)) { validFrom = Util.GetValueOfDateTime(DB.ExecuteScalar("SELECT DateOrdered FROM C_Order WHERE C_Order_ID = " + Record_ID)); } if (validFrom == null) { validFrom = DateTime.Now.Date; } int M_PriceList_Version_ID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT M_PriceList_Version_ID FROM M_PriceList_Version WHERE IsActive = 'Y' AND M_PriceList_ID = " + M_PriceList_ID + @" AND ValidFrom <= " + DB.TO_DATE(validFrom, true) + " ORDER BY ValidFrom DESC")); string sql = "SELECT P.M_Product_ID, " + (C_UOM_ID > 0 ? C_UOM_ID.ToString() : "P.C_UOM_ID") + @" AS C_UOM_ID, P.Value, P.Name, 1 AS QtyEntered, BOMPRICELISTUOM(P.M_PRODUCT_ID, PR.M_PRICELIST_VERSION_ID, PR.M_ATTRIBUTESETINSTANCE_ID, PR.C_UOM_ID) AS PriceList, BOMPRICESTDUOM(P.M_PRODUCT_ID, PR.M_PRICELIST_VERSION_ID, PR.M_ATTRIBUTESETINSTANCE_ID, PR.C_UOM_ID) AS UnitPrice, BOMPRICELIMITUOM(P.M_PRODUCT_ID, PR.M_PRICELIST_VERSION_ID, PR.M_ATTRIBUTESETINSTANCE_ID, PR.C_UOM_ID) AS LimitPrice FROM M_PRODUCT P LEFT JOIN M_PRODUCTPRICE PR ON ( P.M_PRODUCT_ID = PR.M_PRODUCT_ID AND PR.ISACTIVE = 'Y' AND NVL(PR.M_AttributeSetInstance_ID, 0) = " + M_AttributeSetInstance_ID + " AND PR.M_PRICELIST_VERSION_ID = " + M_PriceList_Version_ID + " AND PR.C_UOM_ID = " + (C_UOM_ID > 0 ? C_UOM_ID.ToString() : "P.C_UOM_ID") + @") WHERE P.M_Product_ID = " + M_Product_ID; DataSet _ds = DB.ExecuteDataset(sql, null, null); if (_ds != null && _ds.Tables[0].Rows.Count > 0) { res = new RelatedProductData(); res.Product_ID = Util.GetValueOfInt(_ds.Tables[0].Rows[0]["M_Product_ID"]); res.Product = Util.GetValueOfString(_ds.Tables[0].Rows[0]["Name"]); res.AttributeSetInstance_ID = M_AttributeSetInstance_ID; res.Value = Util.GetValueOfString(_ds.Tables[0].Rows[0]["Value"]); //res.UOM = Util.GetValueOfString(_ds.Tables[0].Rows[0]["UName"]); res.UOM_ID = Util.GetValueOfInt(_ds.Tables[0].Rows[0]["C_UOM_ID"]); res.QtyEntered = Util.GetValueOfDecimal(_ds.Tables[0].Rows[0]["QtyEntered"]); res.PriceStd = Util.GetValueOfDecimal(_ds.Tables[0].Rows[0]["UnitPrice"]); res.PriceList = Util.GetValueOfDecimal(_ds.Tables[0].Rows[0]["PriceList"]); } return(res); }