/// <summary>
/// 更新一条数据
/// </summary>
public void Update(ProductExtModel model)
{
DbCommand dbCommand = dbw.GetStoredProcCommand("UP_pdProductExt_Update");
dbw.AddInParameter(dbCommand, "ProductId", DbType.Int32, model.ProductId);
dbw.AddInParameter(dbCommand, "ProductDesc", DbType.String, model.ProductDesc);
dbw.ExecuteNonQuery(dbCommand);
}
/// <summary>
/// 对象实体绑定数据
/// </summary>
public ProductExtModel ReaderBind(IDataReader dataReader)
{
ProductExtModel model = new ProductExtModel();
object ojb;
ojb = dataReader["ProductId"];
if (ojb != null && ojb != DBNull.Value)
{
model.ProductId = (int)ojb;
}
model.ProductDesc = dataReader["ProductDesc"].ToString();
return(model);
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public ProductExtModel GetModel(int ProductId)
{
DbCommand dbCommand = dbr.GetStoredProcCommand("UP_pdProductExt_GetModel");
dbr.AddInParameter(dbCommand, "ProductId", DbType.Int32, ProductId);
ProductExtModel model = null;
using (IDataReader dataReader = dbr.ExecuteReader(dbCommand))
{
if (dataReader.Read())
{
model = ReaderBind(dataReader);
}
}
return(model);
}
/// <summary>
/// 获得数据列表
/// </summary>
public List <ProductExtModel> GetModelList(string strWhere)
{
DataSet ds = dal.GetList(strWhere);
List <ProductExtModel> modelList = new List <ProductExtModel>();
int rowsCount = ds.Tables[0].Rows.Count;
if (rowsCount > 0)
{
ProductExtModel model;
for (int n = 0; n < rowsCount; n++)
{
model = new ProductExtModel();
if (ds.Tables[0].Rows[n]["ProductId"].ToString() != "")
{
model.ProductId = int.Parse(ds.Tables[0].Rows[n]["ProductId"].ToString());
}
model.ProductDesc = ds.Tables[0].Rows[n]["ProductDesc"].ToString();
modelList.Add(model);
}
}
return(modelList);
}
/// <summary>
/// 更新一条数据
/// </summary>
public void Update(ProductExtModel model)
{
dal.Update(model);
}
/// <summary>
/// 增加一条数据
/// </summary>
public void Add(ProductExtModel model)
{
dal.Add(model);
}
public IViewComponentResult Invoke(int id, int display)
{
var rgp = _rgpRepository.TableNoTracking.OrderBy(x => x.Id).Where(x => !x.Deleted && x.ItemId == id && x.Direction == "B").Select(x => x.GroupId);
var ic = _iRepository.TableNoTracking.OrderBy(x => x.Id).Where(x => !x.Deleted && x.ItemIdPart == id).Select(x => x.ItemId);;
var gi = _gpiRepository.TableNoTracking.OrderBy(x => x.Id).Where(x => !x.Deleted && rgp.Contains(x.GroupId)).Select(x => x.ItemId);
var gi1 = _gpiRepository.TableNoTracking.OrderBy(x => x.Id).Where(x => !x.Deleted && x.ItemId == id).Select(x => x.GroupId);
var rgp1 = _rgpRepository.TableNoTracking.OrderBy(x => x.Id).Where(x => !x.Deleted && x.Direction == "A" && gi1.Contains(x.GroupId)).Select(x => x.ItemId);;
var tmp = ic.Concat(gi).Concat(rgp1).Distinct().ToArray();
var sqlString = string.Format(
"SELECT " +
"ItemsCompatability.ItemID, [product].Name " +
"FROM ItemsCompatability INNER JOIN " +
"[product] ON ItemsCompatability.ItemID = product.Id " +
"WHERE(ItemsCompatability.ItemIDPart = {0}) " +
"UNION " +
"SELECT[Groups-Items].ItemID, product_1.Name " +
"FROM[Groups-Items] INNER JOIN " +
"[product] AS product_1 ON[Groups-Items].ItemID = product_1.Id " +
"WHERE([Groups-Items].GroupID IN " +
"(SELECT GroupID " +
"FROM [Relations-Groups-Items] " +
" WHERE (ItemID = {0}) AND(Direction = 'B'))) " +
" UNION SELECT[Relations-Groups-Items].ItemID, [product].Name " +
"FROM[product] INNER JOIN[Relations-Groups-Items] ON product.Id = [Relations-Groups-Items].ItemID " +
"WHERE ([Relations-Groups-Items].Direction = 'A') AND([Relations-Groups-Items].GroupID IN " +
"(SELECT GroupID " +
"FROM [Groups-Items] " +
" WHERE (ItemID = {0}))) ", id);
// var relatedProducts = _dbContext.SqlQuery<List<ProductNameModel>>(sqlString).ToList();
var legacy = _lRepository.TableNoTracking.Where(x => x.ItemId == id);
var product = _productService.GetProductById(id);
var model = new ProductExtModel();
model.Id = id;
model.LegacyIdModel = _lRepository.TableNoTracking.Where(x => x.ItemId == id).Select(x => new LegacyIdModel {
itemId = x.ItemId, LegacyCode = x.LegacyCode, LegacyName = x.LegacyCode
}).ToList();
if (display == 0)
{
model.PartNumber = product.Sku;
var usedIn = _productService.GetProductsByIds(tmp).Select(x => new UsedInModel {
ProductName = _productService.GetNameRid(x, 0), Id = x.Id, SeName = _productService.GetUrlRid(x)
}).ToList();
var productCategories = string.Empty;
foreach (var c in product.ProductCategories.ToList())
{
model.CategoryModel.Add(new CategoryModel {
CatergoryName = c.Category.Name
});
}
model.UsedInModel = usedIn;
return(View("~/Plugins/Misc.ProductWizard/Views/UsedIn.cshtml", model));
}
else
{
string sqlPartForItem = string.Format(
"SELECT ItemsCompatability.ItemIDPart, [product].Name as ProductName " +
"FROM ItemsCompatability INNER JOIN[product] " +
"ON ItemsCompatability.ItemIDPart = [product].Id " +
"WHERE(ItemsCompatability.ItemID = {0}) " +
"UNION " +
"SELECT[Groups-Items].ItemID, [product_1].Name " +
"FROM[Groups-Items] INNER JOIN[product] AS[product_1] " +
"ON[Groups-Items].ItemID = [product_1].id " +
"WHERE([Groups-Items].GroupID IN " +
"(SELECT GroupID " +
"FROM[Relations-Groups-Items] " +
"WHERE(ItemID = {0}) AND(Direction = 'A'))) " +
"UNION " +
"SELECT[Relations-Groups-Items].ItemID, [product].Name " +
"FROM[product] INNER JOIN[Relations-Groups-Items] " +
"ON[product].id = [Relations-Groups-Items].ItemID " +
"WHERE([Relations-Groups-Items].Direction = 'B') " +
"AND([Relations-Groups-Items].GroupID IN " +
"(SELECT GroupID " +
"FROM [Groups-Items] " +
"WHERE (ItemID = {0}))) ", id);
var partForItem = _dbContext.SqlQuery <ProductNameModel>(sqlPartForItem).Select(x => x.ItemIDPart).ToList();
var isCopier = false;
foreach (var c in product.ProductCategories.ToList())
{
if (c.Category.Name.ToLower() == "copier" || c.Category.Name.ToLower() == "Accessories")
{
isCopier = true;
}
}
model.IsCopier = isCopier;
model.FullDescription = product.FullDescription;
if (!isCopier)
{
model.PartForItem = _productService.GetProductsByIds(partForItem.ToArray()).Select(x => new UsedInModel {
ProductName = _productService.GetNameRid(x, 0), Id = x.Id, SeName = _productService.GetUrlRid(x)
}).ToList();
}
else
{
string sqlPartForItem1 = string.Format(
"SELECT ItemsCompatability.ItemIDPart, [product].Name as ProductName " +
"FROM ItemsCompatability INNER JOIN[product] " +
"ON ItemsCompatability.ItemIDPart = [product].Id " +
"WHERE(ItemsCompatability.ItemID = {0}) " +
"UNION " +
"SELECT[Groups-Items].ItemID, [product_1].Name " +
"FROM[Groups-Items] INNER JOIN[product] AS[product_1] " +
"ON[Groups-Items].ItemID = [product_1].id " +
"WHERE([Groups-Items].GroupID IN " +
"(SELECT GroupID " +
"FROM[Relations-Groups-Items] " +
"WHERE(ItemID = {0}) AND(Direction = 'A'))) " +
"UNION " +
"SELECT[Relations-Groups-Items].ItemID, [product].Name " +
"FROM[product] INNER JOIN[Relations-Groups-Items] " +
"ON[product].id = [Relations-Groups-Items].ItemID " +
"WHERE([Relations-Groups-Items].Direction = 'B') " +
"AND([Relations-Groups-Items].GroupID IN " +
"(SELECT GroupID " +
"FROM [Groups-Items] " +
"WHERE (ItemID = {0}))) ", id);
var partForItem1 = _dbContext.SqlQuery <ProductNameModel>(sqlPartForItem1).Select(x => x.ItemIDPart).ToList();
var query = _productService.GetProductsByIds(partForItem.ToArray());
var modelCtegory = query.Select(x => new CategoryModel
{
CatergorySeo = x.ProductCategories.Select(y => y.Category.GetSeName()).FirstOrDefault(),
CatergoryName = x.ProductCategories.Select(y => y.Category.Name).FirstOrDefault()
});
var groupedBy = modelCtegory.GroupBy(x => new { x.CatergoryName, x.CatergorySeo })
.Select(y => new CategoryModel
{
CatergorySeo = y.Key.CatergorySeo,
CatergoryName = y.Key.CatergoryName
})
.OrderBy(z => z.CatergoryName)
.ToList();
model.CategoryModel = groupedBy;
}
return(View("~/Plugins/Misc.ProductWizard/Views/UsedInAdditional.cshtml", model));
}
}
