public void SaveProductMasterData(DBSite site, ProductMasterEntity pme)
{
string qry = "INSERT INTO tblProductMaster( ProductID, ProductName, OpeningBalance, ProductDate, CostPrice, SellingPrice, UOM, CategoryID";
qry += ", AccountId, LocationId, ReOrderQty ,ProductDescription, UserID, SubuserId , FYear) VALUES(";
qry += "'" + pme.ProductId + "'";
qry += ",'" + pme.ProductName + "'";
qry += ", " + pme.OpeningBalance;
qry += ", '" + pme.productDate + "'";
qry += ", " + pme.CostPrice;
qry += ", " + pme.SellingPrice;
qry += ", " + pme.UOM;
qry += ", " + pme.CategoryID;
qry += ", " + pme.AccountId;
qry += ", " + pme.LocationId;
qry += ", " + pme.ReOrderQty;
qry += ", '" + pme.ProductDescription + "'";
qry += ", " + util.GetUserInsertQry(Util_BLL.User);
//qry += ", " + Util_BLL.User.UserId;
//qry += ", " + Util_BLL.User.fYear;
qry += ")";
site.Execute(qry);
}
public void EditProductMasterData(DBSite site, ProductMasterEntity pme)
{
string qry = " UPDATE tblProductMaster SET ";
qry += " ProductID='" + pme.ProductId + "'";
qry += ", ProductName='" + pme.ProductName + "'";
qry += ", OpeningBalance=" + pme.OpeningBalance;
qry += ", ProductDate='" + pme.productDate + "'";
qry += ", CostPrice=" + pme.CostPrice;
qry += ", SellingPrice=" + pme.SellingPrice;
qry += ", UOM=" + pme.UOM + "";
qry += ", CategoryID=" + pme.CategoryID;
qry += ", AccountId=" + pme.AccountId;
qry += ", LocationId=" + pme.LocationId;
qry += ", SubuserId=" + Util_BLL.SubUser.SubuserId;
qry += ", ReOrderQty=" + pme.ReOrderQty;
qry += ", ProductDescription='" + pme.ProductDescription + "'";
qry += " WHERE ProductMasterId=" + pme.ProductMasterId;
site.Execute(qry);
}
public List <ProductMasterBLL.ProductMasterEntity> GetMatchedRecords(DBSite site, string value_to_search)
{
List <ProductMasterBLL.ProductMasterEntity> selected_product_list = new List <ProductMasterBLL.ProductMasterEntity>();
string qry = "";
qry += " SELECT ";
qry += " ProductMasterId, ProductID, ProductName, OpeningBalance, CostPrice, SellingPrice ";
qry += " , ReOrderQty, ProductDescription, product.UserID, FYear, product.CreatedAt ";
qry += ", category.CategoryId CategoryId ";
qry += ", CategoryName ";
qry += ", uom.UOMId UOMId ";
qry += ", product.LocationId LocationId ";
qry += ", product.AccountId AccountId ";
qry += ", UnitName ";
qry += " FROM ( tblProductMaster as product ";
qry += " LEFT OUTER JOIN tblProductCategory as category ON ";
qry += " product.CategoryId = category.CategoryId )";
qry += " LEFT OUTER JOIN tblUOM as uom ON ";
qry += " product.UOM= uom.UOMId ";
//------------ selection condition ----------------------
qry += " WHERE product.UserID = " + Util_BLL.User.UserId + " AND ";
qry += " (( ProductID LIKE '%" + value_to_search + "%' ) OR";
qry += " ( ProductName LIKE '%" + value_to_search + "%' ) OR";
qry += " ( UnitName LIKE '%" + value_to_search + "%' ) OR";
qry += " ( CategoryName LIKE '%" + value_to_search + "%' ) ";
if (util.IsNumber(value_to_search))
{
qry += "OR ( OpeningBalance = '" + value_to_search + "' ) OR";
qry += " ( CostPrice = '" + value_to_search + "' ) OR";
qry += " ( SellingPrice = '" + value_to_search + "' ) ";
}
qry += " ) ";
DataTable dt = site.ExecuteSelect(qry);
ProductMasterEntity pme;
foreach (DataRow dr in dt.Rows)
{
pme = new ProductMasterEntity();
pme.ProductMasterId = util.CheckNullInt(dr["ProductMasterId"]);
pme.ProductId = util.CheckNull(dr["ProductID"]);
pme.ProductName = util.CheckNull(dr["ProductName"]);
pme.OpeningBalance = util.ToDecimal(util.CheckNullDecimal(dr["OpeningBalance"]));
pme.CostPrice = util.ToDecimal(util.CheckNullDecimal(dr["CostPrice"]));
pme.SellingPrice = util.ToDecimal(util.CheckNullDecimal(dr["SellingPrice"]));
pme.UOM = util.CheckNullInt(dr["UOMId"]);
pme.UOMName = util.CheckNull(dr["UnitName"]);
pme.CategoryID = util.CheckNullInt(dr["CategoryID"]);
pme.CategoryName = util.CheckNull(dr["CategoryName"]);
pme.AccountId = util.CheckNullInt(dr["AccountId"]);
pme.LocationId = util.CheckNullInt(dr["LocationId"]);
pme.ReOrderQty = util.CheckNullDecimal(dr["ReOrderQty"]);
pme.ProductDescription = util.CheckNull(dr["ProductDescription"]);
pme.UserID = util.CheckNullInt(dr["UserID"]);
pme.FYear = util.CheckNullInt(dr["FYear"]);
selected_product_list.Add(pme);
}
return(selected_product_list);
}
public List <ProductMasterEntity> GetProductMasterData(DBSite site, string id = "")
{
List <ProductMasterEntity> productMasterList = new List <ProductMasterEntity>();
string qry = "";
qry += " SELECT ";
qry += " ProductMasterId, ProductID, ProductName, OpeningBalance, ProductDate, CostPrice, SellingPrice ";
qry += " , ReOrderQty , ProductDescription, product.UserID, FYear, product.CreatedAt ";
qry += ", category.CategoryId CategoryId ";
qry += ", CategoryName ";
qry += ", uom.UOMId UOMId ";
qry += ", product.LocationId LocationId ";
qry += ", product.AccountId AccountId ";
qry += ", UnitName ";
qry += " FROM ( tblProductMaster as product ";
qry += " LEFT OUTER JOIN tblProductCategory as category ON ";
qry += " product.CategoryId = category.CategoryId )";
qry += " LEFT OUTER JOIN tblUOM as uom ON ";
qry += " product.UOM= uom.UOMId ";
qry += " WHERE product.UserID = " + Util_BLL.User.UserId;
if (id != string.Empty)
{
qry += " AND ProductMasterId = " + id;
}
DataTable dt = site.ExecuteSelect(qry);
ProductMasterEntity pme;
foreach (DataRow dr in dt.Rows)
{
pme = new ProductMasterEntity();
pme.ProductMasterId = util.CheckNullInt(dr["ProductMasterId"]);
pme.ProductId = util.CheckNull(dr["ProductID"]);
pme.ProductName = util.CheckNull(dr["ProductName"]);
pme.OpeningBalance = util.ToDecimal(util.CheckNullDecimal(dr["OpeningBalance"]));
DateTime date = Convert.ToDateTime(dr["ProductDate"]);
pme.productDate = date;
pme.CostPrice = util.ToDecimal(util.CheckNullDecimal(dr["CostPrice"]));
pme.SellingPrice = util.ToDecimal(util.CheckNullDecimal(dr["SellingPrice"]));
pme.UOM = util.CheckNullInt(dr["UOMId"]);
pme.UOMName = util.CheckNull(dr["UnitName"]);
pme.CategoryID = util.CheckNullInt(dr["CategoryID"]);
pme.CategoryName = util.CheckNull(dr["CategoryName"]);
pme.AccountId = util.CheckNullInt(dr["AccountId"]);
pme.LocationId = util.CheckNullInt(dr["LocationId"]);
pme.ReOrderQty = util.CheckNullDecimal(dr["ReOrderQty"]);
pme.ProductDescription = util.CheckNull(dr["ProductDescription"]);
pme.UserID = util.CheckNullInt(dr["UserID"]);
pme.FYear = util.CheckNullInt(dr["FYear"]);
productMasterList.Add(pme);
}
return(productMasterList);
}
