/// <summary>
/// 插入商品价格
/// </summary>
/// <param name="price">历史价格对象</param>
public void WarePriceInsert(ProductPriceHistory price)
{
if (price != null)
{
WarePriceInsert(price.PID, price.Price, price.PriceDate, price.PriceType);
}
}
protected void gvProductPriceHistorys_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
ProductPriceHistory productPriceHistory = (ProductPriceHistory)e.Row.DataItem;
Label lbProductType = (Label)e.Row.FindControl("lbProductType");
Label lbManufacturer = (Label)e.Row.FindControl("lbManufacturer");
Label lbProductName = (Label)e.Row.FindControl("lbProductName");
if (productPriceHistory.Product != null)
{
if (lbProductType != null)
{
if (productPriceHistory.Product.ProductType != null)
{
lbProductType.Text = productPriceHistory.Product.ProductType.Name;
}
}
if (lbManufacturer != null)
{
if (productPriceHistory.Product.Manufacturer != null)
{
lbManufacturer.Text = productPriceHistory.Product.Manufacturer.Name;
}
}
if (lbProductName != null)
{
lbProductName.Text = ToSQL.ShortString(productPriceHistory.Product.Name, 32);
lbProductName.ToolTip = productPriceHistory.Product.Name;
}
}
}
}
/// <summary>
/// 判断某个商品当天是否有相同价格存在
/// </summary>
/// <param name="pid">商品编号</param>
/// <param name="dt">日期</param>
/// <param name="price">价格</param>
/// <returns></returns>
public bool WarePriceHistoryGetOne(string pid, DateTime dt, double price)
{
bool rtnJudge = false;
//开始日期
DateTime dtBegin = dt.Date;
//结束日期
DateTime dtEnd = dt.AddDays(1).Date;
ProductPriceHistory rtnObj = WarePriceHistoryGetOne(pid, dtBegin, dtEnd, price);
rtnJudge = rtnObj != null;
return(rtnJudge);
}
public int CreateProductPriceHistory(ProductPriceHistory P)
{
try
{
db.ProductPriceHistories.Add(P);
return(db.SaveChanges());
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
public int UpdateProductPriceHistory(ProductPriceHistory P)
{
try
{
db.Entry(P).State = EntityState.Modified;
return db.SaveChanges();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
public int CreateProductPriceHistory(ProductPriceHistory P)
{
try
{
db.ProductPriceHistories.Add(P);
return db.SaveChanges();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
public int UpdateProductPriceHistory(ProductPriceHistory P)
{
try
{
db.Entry(P).State = EntityState.Modified;
return(db.SaveChanges());
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
Product p = new Product();
p.Name = txtName.Text;
p.Price = ToSQL.SQLToDecimal(txtPriceNew.Text);
p.ProductType_ID = ToSQL.SQLToIntNull(ddlProductType.SelectedValue);
p.Manufacuturer_ID = ToSQL.SQLToIntNull(ddlManufacturer.SelectedValue);
p.Description = CKEditorControlDescription.Text;
p.IsActive = true;
p.IsBestSelling = chkBestSelling.Checked;
p.IsNew = chkNew.Checked;
p.IsSpecial = chkSpecial.Checked;
p.DateCreated = DateTime.Now;
if (fulImageDefault.HasFile)
{
ProductImage image = new ProductImage();
string url = CheckFileShared.UploadAndRsizeImage(fulImageDefault.PostedFile);
if (url != "")
{
image.Image = url;
image.IsDefault = true;
p.ProductImages.Add(image);
}
}
HttpFileCollection uploads = Request.Files;
//for (int fileCount = 0; fileCount < uploads.Count; fileCount++)
foreach (HttpPostedFile uploadedFile in FileUploadJquery.PostedFiles)
{
ProductImage image = new ProductImage();
string url = CheckFileShared.UploadAndRsizeImage(uploadedFile);
if (url != "")
{
image.Image = url;
image.IsDefault = false;
p.ProductImages.Add(image);
}
}
if (productRepo.CreateProduct(p) > 0)
{
ProductPriceHistory productPriceHistory = new ProductPriceHistory();
productPriceHistory.Product_ID = p.ID;
productPriceHistory.Price = p.Price;
productPriceHistory.DateCreated = DateTime.Now;
new ProductPriceHistoryRepo().CreateProductPriceHistory(productPriceHistory);
Response.Redirect("~/Admincp/Management-Products.aspx");
}
else
{
lbMessage.Text = "Please check input data! Try again!";
}
}
private void menuDelPrice_Click(object sender, EventArgs e)
{
ProductPriceHistory del = _pricesView.GetCurData <ProductPriceHistory>();
if (del != null)
{
if (MessageBox.Show("是否删除当前价格？\r\n数据删除后将不能恢复，请慎重！", "系统提示",
MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.Yes)
{
{
DBHelper.GetInstance().WarePriceDel(del.RID);
RefreshPriceGrid();
}
}
}
}
public async Task <IActionResult> Put([FromBody] IList <ProductPriceItemForm> productPriceItemForms)
{
var currentUser = await _workContext.GetCurrentUser();
foreach (var item in productPriceItemForms)
{
if (!item.NewOldPrice.HasValue && !item.NewPrice.HasValue)
{
continue;
}
var product = _productRepository.Query().FirstOrDefault(x => x.Id == item.Id);
if (product != null && (User.IsInRole("admin") || product.VendorId == currentUser.VendorId))
{
var productPriceHistory = new ProductPriceHistory
{
Product = product,
CreatedBy = currentUser,
OldPrice = product.OldPrice,
Price = product.Price,
SpecialPrice = product.SpecialPrice,
SpecialPriceStart = product.SpecialPriceStart,
SpecialPriceEnd = product.SpecialPriceEnd
};
if (item.NewOldPrice.HasValue)
{
product.OldPrice = productPriceHistory.OldPrice = item.NewOldPrice.Value;
}
if (item.NewPrice.HasValue)
{
product.Price = item.NewPrice.Value;
productPriceHistory.Price = item.NewPrice.Value;
}
product.PriceHistories.Add(productPriceHistory);
}
}
await _productRepository.SaveChangesAsync();
return(Accepted());
}
public void WarePriceInsert(ProductPriceHistory price)
{
OtDB db = GetDb();
try
{
db.Begin();
//sqlite用的全球时间UTC,要用datetime()函数转换若干 localtime UTC
//用data_time.ToString("s");这种方法转换成 iso 8601标准字符串格式
string sql = string.Format("insert into ProductPriceHistory (PID,Price,PriceDate,PriceType) select '{0}',{1},'{2}','{3}'", price.PID, price.Price, price.PriceDate.ToString("s"), price.PriceType);
db.Exec(sql);
db.Commit();
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
//db.Rollback();
}
}
private void btnPriceAdd_Click(object sender, EventArgs e)
{
try
{
double myPrice = double.Parse(txtPrice.Text);
ProductPriceHistory priceObj = new ProductPriceHistory()
{
PID = _myProduct.ProductID,
PriceDate = datePrice.DateTime,
Price = myPrice,
PriceType = txtPriceType.Text
};
DBHelper.GetInstance().WarePriceInsert(priceObj);
RefreshPriceGrid();
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
}
}
public void DeleteProductPriceHistory(ProductPriceHistory productPriceHistory)
{
productPriceHistoryRepository.Delete(productPriceHistory);
}
public void UpdateProductPriceHistory(ProductPriceHistory productPriceHistory)
{
productPriceHistoryRepository.Update(productPriceHistory);
}
public void AddProductPriceHistory(ProductPriceHistory productPriceHistory)
{
productPriceHistoryRepository.Insert(productPriceHistory);
}
protected void btnSave_Click(object sender, EventArgs e)
{
Product p = productRepo.GetById(ToSQL.SQLToInt(Request.QueryString["ID"]));
if (p != null)
{
p.Name = ToSQL.EmptyNull(txtName.Text);
decimal priceOld = p.Price;
p.Price = ToSQL.SQLToDecimal(txtPrice.Text);
p.ProductType_ID = ToSQL.SQLToIntNull(ddlProductType.SelectedValue);
p.Manufacuturer_ID = ToSQL.SQLToIntNull(ddlManufacturer.SelectedValue);
p.Description = CKEditorControlDescription.Text;
p.IsActive = ToSQL.SQLToBool(chkActive.Checked);
p.IsBestSelling = ToSQL.SQLToBool(chkBestSelling.Checked);
p.IsNew = ToSQL.SQLToBool(chkNew.Checked);
p.IsSpecial = ToSQL.SQLToBool(chkSpecial.Checked);
if (fulImageDefault.HasFile)
{
ProductImage image = new ProductImage();
string url = CheckFileShared.UploadAndRsizeImage(fulImageDefault.PostedFile);
if (url != "")
{
var pi = p.ProductImages.FirstOrDefault(u => u.IsDefault.Value == true); if (pi != null)
{
pi.IsDefault = false;
}
image.Image = url;
image.IsDefault = true;
p.ProductImages.Add(image);
}
}
foreach (HttpPostedFile uploadedFile in FileUploadJquery.PostedFiles)
{
ProductImage image = new ProductImage();
string url = CheckFileShared.UploadAndRsizeImage(uploadedFile);
if (url != "")
{
image.Image = url;
image.IsDefault = false;
p.ProductImages.Add(image);
}
}
if (productRepo.UpdateProduct(p) > 0)
{
if (priceOld != p.Price)
{
ProductPriceHistory productPriceHistory = new ProductPriceHistory();
productPriceHistory.Product_ID = p.ID;
productPriceHistory.Price = p.Price;
productPriceHistory.DateCreated = DateTime.Now;
new ProductPriceHistoryRepo().CreateProductPriceHistory(productPriceHistory);
}
Response.Redirect("~/Admincp/Management-Products.aspx");
}
else
{
lbMessage.Text = "Please check input data! Try again!";
}
}
else
{
Response.Redirect("Management-Products.aspx");
}
}
public IEnumerable <ProductPriceHistory> GetCurrentProductPriceHistories()
{
var pphList = new List <ProductPriceHistory>();
var psList = new List <ProductSite>();
var pList = new List <Product>();
var sList = new List <Site>();
using (var connection = new SQLiteConnection(this.ConnectionString))
{
var sql = @"SELECT ps.ProductId,
p.Name as ProductName,
ps.SiteId,
s.Name as SiteName,
s.UrlFormat,
s.XPath,
ps.Id as ProductSiteId,
ps.Url,
ps.LastCheck,
pph.Id as ProductPriceHistoryId,
pph.Price,
MAX(pph.Occurance)
FROM Product p,
Site s,
ProductSite ps,
ProductPriceHistory pph
WHERE p.Id = ps.ProductId
AND s.Id = ps.SiteId
AND ps.Id = pph.ProductSiteId
GROUP BY ps.Id";
using (var command = new SQLiteCommand(sql, connection))
{
connection.Open();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var site = sList.Where(s => s.Id == reader.GetInt32(2)).FirstOrDefault();
if (site == null)
{
site = new Site
{
Id = reader.GetInt32(2),
Name = reader.GetString(3),
UrlFormat = reader.GetString(4),
XPath = reader.GetString(5),
ProductSites = new List <ProductSite>()
};
sList.Add(site);
}
var product = pList.Where(s => s.Id == reader.GetInt32(0)).FirstOrDefault();
if (product == null)
{
product = new Product
{
Id = reader.GetInt32(0),
Name = reader.GetString(1),
ProductSites = new List <ProductSite>()
};
pList.Add(product);
}
var productSite = psList.Where(ps => ps.Id == reader.GetInt32(6)).FirstOrDefault();
if (productSite == null)
{
productSite = new ProductSite
{
Id = reader.GetInt32(6),
Url = reader.GetString(7),
ProductPriceHistories = new List <ProductPriceHistory>(),
LastCheck = this.UnixEpochToDateTime(reader.GetDouble(8)),
Site = site,
Product = product
};
psList.Add(productSite);
}
var productPriceHistory = new ProductPriceHistory
{
Id = reader.GetInt32(9),
Price = reader.GetDecimal(10),
Occurance = this.UnixEpochToDateTime(reader.GetDouble(11)),
ProductSite = productSite
};
pphList.Add(productPriceHistory);
if (!productSite.ProductPriceHistories.Any(pph => pph.Id == productPriceHistory.Id))
{
productSite.ProductPriceHistories.Add(productPriceHistory);
}
if (!product.ProductSites.Any(ps => ps.Id == productSite.Id))
{
product.ProductSites.Add(productSite);
}
if (!site.ProductSites.Any(ps => ps.Id == productSite.Id))
{
site.ProductSites.Add(productSite);
}
}
}
connection.Close();
}
}
return(pphList);
}
public int DeleteProductPriceHistory(int Id)
{
ProductPriceHistory productPriceHistory = db.ProductPriceHistories.Where(u => u.ID == Id).FirstOrDefault();
return(DeleteProductPriceHistory(productPriceHistory));
}
public static async Task <ProductPriceHistory> GetProductPriceHistoryAsync(this StoreDbContext dbContext, ProductPriceHistory entity) => await dbContext.ProductPriceHistory.FirstOrDefaultAsync(item => item.ProductPriceHistoryID == entity.ProductPriceHistoryID);
public IEnumerable <ProductPriceHistory> GetCurrentProductPriceHistories()
{
var now = DateTime.Now;
var productOnSites = this.Database.GetSitesPerProduct();
using (IWebDriver driver = this.WebQueryable.GetWebDriver())
{
foreach (var productOnSite in productOnSites)
{
driver.Url = string.Format(productOnSite.Site.UrlFormat, productOnSite.Url);
string text;
try
{
var element = driver.FindElement(By.XPath(productOnSite.Site.XPath));
text = !string.IsNullOrWhiteSpace(element.Text) ? element.Text.Trim() : element.GetAttribute("content").Trim();
}
catch
{
// Notifications!
Console.WriteLine(string.Format("{0} @ {1}: Price not found!", productOnSite.Product.Name, productOnSite.Site.Name));
continue;
}
finally
{
driver.Close();
}
var match = Regex.Match(text, @"\d+(\.\d{2})?");
if (!match.Success)
{
// Notifications!
Console.WriteLine(string.Format("{0} @ {1}: Price not found!", productOnSite.Product.Name, productOnSite.Site.Name));
continue;
}
var price = decimal.Parse(match.Value);
/* Console.WriteLine(string.Format("{0} @ {1}: {2}", productOnSite.Product.Name, productOnSite.Site.Name, price)); */
var productPriceHistory = new ProductPriceHistory
{
Occurance = now,
Price = price,
ProductSite = productOnSite
};
productOnSite.ProductPriceHistories.Add(productPriceHistory);
yield return(productPriceHistory);
/*var bestPrice = this.Database.GetBestPrice(productOnSite.Product.Name);
* if (bestPrice != null && price >= bestPrice.Price)
* {
* continue;
* }
*
* // Notifications!
* Console.WriteLine(string.Format("Best Current Price!"));
*
* var bestHistoricalPrice = this.Database.GetBestHistoricalPrice(productOnSite.Product.Name);
* if (bestHistoricalPrice != null && price >= bestHistoricalPrice.Price)
* {
* continue;
* }
*
* // Notifications!!
* Console.WriteLine(string.Format("BEST PRICE EVER!"));*/
}
}
}
