public void CalculatePriceRange(int productId)
{
using (var context = new SmartBuyEntities())
{
// Calculate min, max price for today
var priceList = context.SellProducts
.Where(x => x.ProductId == productId)
.ToList();
priceList = priceList
.Where(x => x.LastUpdatedTime.Date == DateTime.Today)
.ToList();
var minPrice = priceList.Min(x => x.SellPrice);
var maxPrice = priceList.Max(x => x.SellPrice);
if (minPrice != null && maxPrice != null)
{
var attribute = new ProductAttribute
{
ProductId = productId,
MinPrice = minPrice,
MaxPrice = maxPrice,
LastUpdatedTime = DateTime.Now
};
context.ProductAttributes.Add(attribute);
context.SaveChanges();
}
}
}
public static List<string> GetProductName(string pre)
{
SmartBuyEntities context = new SmartBuyEntities();
List<string> allCompanyName = new List<string>();
allCompanyName = (from a in context.Products
where a.Name.StartsWith(pre)
select a.Name).ToList();
return allCompanyName;
}
public override bool ValidateUser(string username, string password)
{
using (var context = new SmartBuyEntities())
{
var user = context.Users.FirstOrDefault(x => x.Username == username && x.Password == password);
if (user != null)
{
return true;
}
return false;
}
}
public override string[] GetRolesForUser(string username)
{
var result = new string[1];
using (var context = new SmartBuyEntities())
{
var user = context.Users
.Include(x => x.Role)
.FirstOrDefault(x => x.Username == username);
if (user != null)
{
result[0] = user.Role.Name;
}
}
return result;
}
public static void GenerateLogFile(List<LogInfo> infos)
{
string path = ConstantManager.LogPath;
int max = infos.Max(x => x.Link.Length);
max++;
string link = "Link".PadRight(max);
string fileName = Path.GetRandomFileName() + ".txt";
string horizontalLine = HorizontalLine(max);
string content = "SMART BUY LOG FILE\n" +
"Tạo file lúc: " + DateTime.Now.ToShortDateString() + ", " +
DateTime.Now.ToShortTimeString() + "\n";
content += horizontalLine;
content += string.Format("|{0,-3}|{1}|{2,-15}|{3,-13}|{4,-19}|\n", "STT", link, "Thời gian parse",
"Tổng sản phẩm", "Insert vào database");
content += horizontalLine;
for (int i = 0; i < infos.Count; i++)
{
content += string.Format("|{0,-3}|{1}|{2,-15}|{3,-13}|{4,-19}|\n",
i + 1, infos[i].Link.PadRight(max), infos[i].ElapsedTime, infos[i].TotalItems,
infos[i].ToDatabase);
content += horizontalLine;
}
int totalTime = infos.Sum(x => x.ElapsedTime);
int totalParsedItems = infos.Sum(x => x.TotalItems);
content += "Tổng thời gian parse: " + totalTime + " mili giây\n";
content += "Tổng sản phẩm parse được: " + totalParsedItems;
File.WriteAllText(path + fileName, content, new UnicodeEncoding());
using (var context = new SmartBuyEntities())
{
var log = new LogFile
{
FileName = fileName,
CreatedTime = DateTime.Now,
IsActive = true
};
context.LogFiles.Add(log);
context.SaveChanges();
}
}
public override bool IsUserInRole(string username, string roleName)
{
bool result = false;
using (var context = new SmartBuyEntities())
{
var user = context.Users
.Include(x => x.Role)
.FirstOrDefault(x => x.Username == username);
if (user != null)
{
if (user.Role.Name == roleName)
{
result = true;
}
}
}
return result;
}
public static void CalculateDistance()
{
var math = new MathHelper();
using (var context = new SmartBuyEntities())
{
var markets = context.Markets.Where(x => x.IsActive).ToList();
for (int i = 0; i < markets.Count - 1; i++)
{
double lat1 = markets[i].Latitude.Value;
double lng1 = markets[i].Longitude.Value;
for (int j = i + 1; j < markets.Count; j++)
{
if (markets[j].Latitude == null || markets[j].Longitude == null)
{
continue;
}
double lat2 = markets[j].Latitude.Value;
double lng2 = markets[j].Longitude.Value;
double distance = math.CalculateDistance(lat1, lng1, lat2, lng2);
int fromId = markets[i].Id;
int toId = markets[j].Id;
var mDis = context.MarketDistances
.FirstOrDefault(x => x.FromMarket == fromId && x.ToMarket == toId);
if (mDis == null)
{
var dis = new MarketDistance
{
FromMarket = markets[i].Id,
ToMarket = markets[j].Id,
Distance = distance
};
context.MarketDistances.Add(dis);
}
}
}
context.SaveChanges();
}
}
public static void ParseData()
{
ConstantManager.IsParserRunning = true;
// Stopwatch to measure elapsed time
var stopwatch = new Stopwatch();
// Store parsing info
var logInfos = new List<LogInfo>();
// Create Firefox browser
var web = new HtmlWeb { UserAgent = "Mozilla/5.0 (Windows NT 6.1; rv:26.0) Gecko/20100101 Firefox/26.0" };
using (var context = new SmartBuyEntities())
{
var info = context.ParseInfoes.Where(x => x.IsActive);
foreach (var parseInfo in info)
{
stopwatch.Start();
var data = GetData(web, parseInfo);
stopwatch.Stop();
var log = new LogInfo
{
Link = parseInfo.ParseLink,
ElapsedTime = stopwatch.Elapsed.Milliseconds,
TotalItems = data.Count,
ToDatabase = InsertProductToDb(data, parseInfo.MarketId.Value)
};
logInfos.Add(log);
stopwatch.Reset();
}
}
LogFileHelper.GenerateLogFile(logInfos);
ConstantManager.IsParserRunning = false;
}
public void CreateAccount(RegisterModel model)
{
using (var context = new SmartBuyEntities())
{
// Duplicate account
var account = context.Users.FirstOrDefault(x => x.Username == model.UserName);
if (account != null)
{
throw new MembershipCreateUserException(MembershipCreateStatus.DuplicateUserName);
}
var newUser = new User
{
Username = model.UserName,
Password = model.Password,
RoleId = model.RoleId,
IsActive = true
};
context.Users.Add(newUser);
context.SaveChanges();
}
}
public static void CalculateDistance()
{
var model = DistanceConfigHelper.GetData();
if (model.Status == (int)DistanceStatus.Finish)
{
return;
}
int i = model.From;
int j = model.To;
var math = new MathHelper();
using (var context = new SmartBuyEntities())
{
var markets = context.Markets
.Where(x => x.IsActive && x.Latitude != null && x.Longitude != null)
.ToList();
while (true)
{
var lat1 = markets[i].Latitude.Value;
var lng1 = markets[i].Longitude.Value;
var lat2 = markets[j].Latitude.Value;
var lng2 = markets[j].Longitude.Value;
var distance = math.TravelDistance(lat1, lng1, lat2, lng2);
// OK
if (distance != -1)
{
var m1 = markets[i];
var m2 = markets[j];
var tmp = context.MarketDistances
.FirstOrDefault(x => x.FromMarket == m1.Id && x.ToMarket == m2.Id);
if (tmp != null)
{
tmp.Distance = distance;
}
else
{
var mDistance = new MarketDistance
{
FromMarket = markets[i].Id,
ToMarket = markets[j].Id,
Distance = distance
};
context.MarketDistances.Add(mDistance);
}
j++;
if (j == markets.Count)
{
i++;
// Finish
if (i == markets.Count - 1)
{
DistanceConfigHelper.Finish();
context.SaveChanges();
return;
}
j = i + 1;
}
}
else
{
// Something's wrong, out of quota
var savePoint = new DistanceConfigModel
{
From = i,
To = j,
Status = (int) DistanceStatus.Going
};
// Save
DistanceConfigHelper.SetData(savePoint);
context.SaveChanges();
return;
}
}
}
}
private static int InsertProductToDb(IEnumerable<KeyValuePair<string, string>> data, int marketId)
{
int success = 0;
var helper = new PriceHelper();
using (var context = new SmartBuyEntities())
{
foreach (var pair in data)
{
// Convert price to integer
int price = ConvertPrice(pair.Value);
// Check product existent
var goodMatch = new List<int>();
var averageMatch = new List<int>();
int pId = -1;
bool wholeMatch = false;
foreach (var dictionary in context.Dictionaries)
{
if (pair.Key == dictionary.Name)
{
wholeMatch = true;
pId = dictionary.ProductId.Value;
break;
}
double match = CompareStringHelper.CompareString(pair.Key, dictionary.Name);
if (match > 0.9)
{
// Good match
goodMatch.Add(dictionary.ProductId.Value);
}
else if (match > 0.7)
{
// Average match
averageMatch.Add(dictionary.ProductId.Value);
}
}
if (!wholeMatch)
{
if (goodMatch.Count == 1)
{
// Match well with only 1 product, take it
pId = goodMatch[0];
}
else if (goodMatch.Count > 1)
{
// Match well with more than 1 product, admin decide
ExportTrainingFile(goodMatch, pair.Key);
continue;
}
else if (averageMatch.Count > 0 && pId == -1)
{
// Only average match, admin decide
ExportTrainingFile(averageMatch, pair.Key);
continue;
}
}
// Already existed?
if (pId != -1)
{
// Insert new price
var sell = new SellProduct
{
MarketId = marketId,
ProductId = pId,
SellPrice = price,
LastUpdatedTime = DateTime.Now
};
context.SellProducts.Add(sell);
try
{
context.SaveChanges();
helper.CalculatePriceRange(pId);
success++;
}
catch (DbUpdateException)
{
// Do nothing
}
}
else
{
// Add new product to database
var newProduct = new Product {Name = pair.Key, IsActive = true};
// Create new attribute
var attribute = new ProductAttribute
{
MinPrice = price,
MaxPrice = price,
LastUpdatedTime = DateTime.Now
};
newProduct.ProductAttributes.Add(attribute);
// Add product selling information
var sell = new SellProduct
{
MarketId = marketId,
SellPrice = price,
LastUpdatedTime = DateTime.Now
};
newProduct.SellProducts.Add(sell);
context.Products.Add(newProduct);
try
{
context.SaveChanges();
success++;
// Add to dictionary
var dictionary = new Dictionary
{
Name = newProduct.Name,
ProductId = newProduct.Id
};
context.Dictionaries.Add(dictionary);
context.SaveChanges();
}
catch (DbUpdateException)
{
// Do nothing
}
}
}
}
return success;
}
private static void ExportTrainingFile(List<int> match, string name)
{
List<string> data = ReadDataFromFile();
string path = ConstantManager.TrainingFilePath;
string content = "";
using (var context = new SmartBuyEntities())
{
foreach (int id in match)
{
var product = context.Products.FirstOrDefault(x => x.Id == id);
if (product != null)
{
content += product.Name + ";";
}
}
}
content += name;
bool isExisted = false;
foreach (var item in data)
{
if (item == content)
{
isExisted = true;
break;
}
}
if (!isExisted)
{
using (StreamWriter writer = File.AppendText(path))
{
writer.WriteLine(content);
}
}
}
/// <summary>
/// Create price matrix.
/// If a market doesn't sell a product, its value is maximum.
/// </summary>
/// <returns>Matrix</returns>
private double[,] CreateMatrix()
{
int row = CanBuyProducts.Count;
int col = Markets.Count;
var matrix = new double[row, col];
// Initialize matrix
for (int i = 0; i < row; i++)
{
for (int j = 0; j < col; j++)
{
matrix[i, j] = 100000;
}
}
using (var context = new SmartBuyEntities())
{
// With each product
for (int i = 0; i < CanBuyProducts.Count; i++)
{
// In each market
for (int j = 0; j < Markets.Count; j++)
{
int pid = CanBuyProducts[i].Id;
int mid = Markets[j].Id;
var sell = context.SellProducts
.OrderByDescending(x => x.LastUpdatedTime)
.FirstOrDefault(x => x.ProductId == pid && x.MarketId == mid);
if (sell != null)
{
matrix[i, j] = sell.SellPrice.Value;
}
}
}
}
return matrix;
}
/// <summary>
/// Create moving cost matrix
/// </summary>
/// <returns>It costs matrix[i,j] to move from market[i] to market[j]</returns>
private double[,] CreateDistanceMatrix()
{
double fuel = CalculateFuelPrice();
int row = Markets.Count;
int col = row;
var matrix = new double[row, col];
using (var context = new SmartBuyEntities())
{
for (int i = 0; i < Markets.Count - 1; i++)
{
matrix[i, i] = 0;
for (int j = i + 1; j < Markets.Count; j++)
{
int fromId = Markets[i].Id;
int toId = Markets[j].Id;
var mDis = context.MarketDistances
.FirstOrDefault(x => x.FromMarket == fromId && x.ToMarket == toId);
if (mDis != null && mDis.Distance.HasValue)
{
matrix[i, j] = matrix[j, i] = mDis.Distance.Value * fuel;
}
else
{
matrix[i, j] = matrix[j, i] = LargeNumber;
}
}
}
matrix[Markets.Count - 1, Markets.Count - 1] = 0;
}
return matrix;
}
private int AddNewProduct(int countInsert, SellProductModel product, SmartBuyEntities db, string productNameFirst, Dictionary dupProductDictionary)
{
#region Comments
// Check excell với Dictionary
//List<SellProduct> newCorrectProduct = (List<SellProduct>)Session["CorrectProducts"];
//List<SellProduct> sellProductCompare = db.SellProducts.ToList();
//List<List<SellProduct>> results = new List<List<SellProduct>>();
//for (int i = 0; i < newCorrectProduct.Count; i++)
//{
// List<SellProduct> result = new List<SellProduct>();
// for (int j = i + 1; j < sellProductCompare.Count; j++)
// {
// // var sellProMarket = db.SellProducts.Where(m => m.Market.Equals(sellProductCompare[j].MarketId))
// if (newCorrectProduct[i].Market.Name == sellProductCompare[j].Market.Name)
// {
// var percentage =
// CompareStringHelper.CompareString(newCorrectProduct[i].Product.Name.Split(';').First(), sellProductCompare[j].Product.Name);
// if (percentage > 0.7 && percentage < 1)
// {
// // var productSimilarDB = db.Products.Where(p => p.Dictionaries.Equals(dictionaryCompare[j]));
// if (result.Count() == 0)
// {
// result.Add(newCorrectProduct[i]);
// }
// result.Add(sellProductCompare[j]);
// newCorrectProduct.Remove(newCorrectProduct[i]);
// }
// }
// }
// if (result.Count() != 0)
// {
// i = i - 1;
// results.Add(result);
// }
//}
#endregion
var market = new Market
{
Name = product.MarketName,
IsActive = true,
};
var newMarket = db.Markets.Add(market); //add market
var newProduct = new SmartB.UI.Models.EntityFramework.Product
{
Name = productNameFirst,
IsActive = true,
};
var addedProduct = db.Products.Add(newProduct); // add product
var sellProduct = new SmartB.UI.Models.EntityFramework.SellProduct
{
Market = newMarket,
Product = addedProduct,
SellPrice = product.Price,
LastUpdatedTime = DateTime.Now
};
var addedSellProduct = db.SellProducts.Add(sellProduct); // add sellProduct
db.SaveChanges(); // Save to database
//add product Attribute
PriceHelper helper = new PriceHelper();
helper.CalculatePriceRange(addedProduct.Id);
countInsert++;
// add Product Dictionary
var dictionaries = product.Name.Split(';').ToList();
foreach (string dictionary in dictionaries)
{
if (dupProductDictionary == null && dictionary != "")
{
var ProductDic = new SmartB.UI.Models.EntityFramework.Dictionary
{
Name = dictionary,
ProductId = addedProduct.Id
};
var addProductDic = db.Dictionaries.Add(ProductDic);
}
}
db.SaveChanges(); // Save to database
return countInsert;
}
private static List<SellProductModel> TrungMarket(ref int countInsert, SellProductModel product, SmartBuyEntities db, string productNameFirst, Market dupMarket, Dictionary dupProductDictionary)
{
//Kiem tra productName voi dictionary
List<SellProductModel> sellProducts = null;
List<Product> listProduct = CheckProductNameWithDictionary(productNameFirst, db.Dictionaries);
if (listProduct != null)
{
sellProducts = new List<SellProductModel>();
foreach (var productId in listProduct)
{
var existedSellProduct = db.SellProducts.FirstOrDefault(x => x.ProductId == productId.Id && x.MarketId == dupMarket.Id);
var existedSellProductModel = SellProductModel.MapToSellProductEntity(existedSellProduct);
sellProducts.Add(existedSellProductModel);
}
sellProducts.Add(product);
}
else
{
var newProduct = new SmartB.UI.Models.EntityFramework.Product // add Product
{
Name = productNameFirst,
IsActive = true,
};
var addedProduct = db.Products.Add(newProduct);
var sellProduct = new SmartB.UI.Models.EntityFramework.SellProduct //add SellProduct
{
Market = dupMarket,
Product = addedProduct,
SellPrice = product.Price,
LastUpdatedTime = DateTime.Now
};
var addedSellProduct = db.SellProducts.Add(sellProduct);
countInsert++;
db.SaveChanges(); // Save to database
//add new product Attribute
var productAttribute = new SmartB.UI.Models.EntityFramework.ProductAttribute
{
ProductId = addedProduct.Id,
MinPrice = product.Price,
MaxPrice = product.Price,
LastUpdatedTime = DateTime.Now,
};
var addedProductAtt = db.ProductAttributes.Add(productAttribute);
db.SaveChanges(); // Save to database
// add Product Dictionary
var dictionaries = product.Name.Split(';').ToList();
foreach (string dictionary in dictionaries)
{
if (dupProductDictionary == null && dictionary != "")
{
var ProductDic = new SmartB.UI.Models.EntityFramework.Dictionary
{
Name = dictionary,
ProductId = addedProduct.Id
};
var addProductDic = db.Dictionaries.Add(ProductDic);
}
}
db.SaveChanges(); // Save to database
}
return sellProducts;
}
private static void TrungHoanToan(ref int countUpdate, ref int countInsert, SellProductModel product, SmartBuyEntities db, Market dupMarket, Product dupProduct)
{
var sellProduct = db.SellProducts.Where(s => s.ProductId == dupProduct.Id && s.MarketId == dupMarket.Id).FirstOrDefault();
var sellProduct1 = new SmartB.UI.Models.EntityFramework.SellProduct //add SellProduct
{
Market = dupMarket,
Product = dupProduct,
SellPrice = product.Price,
LastUpdatedTime = DateTime.Now
};
var addedSellProduct = db.SellProducts.Add(sellProduct1);
db.SaveChanges(); // Save to database
// Check sellProduct có trùng không??
if (sellProduct == null)
{
countInsert++;
}
else
{
countUpdate++;
}
// Cập nhật giá Min, Max ProductAttribute
PriceHelper helper = new PriceHelper();
helper.CalculatePriceRange(dupProduct.Id);
}
private static List<Product> CheckProductNameWithDictionary(string productNameFirst, DbSet<Dictionary> dictionaries)
{
List<Product> products = new List<Product>();
SmartBuyEntities db = new SmartBuyEntities();
List<Dictionary> listDictionaryDB = dictionaries.ToList();
var status = false;
for (int i = 0; i < listDictionaryDB.Count(); i++)
{
if (status == true)
{
break;
}
if (products.Count == 0)
{
var dupDictionary = db.Dictionaries.Where(d => d.Name == productNameFirst).FirstOrDefault();
if (dupDictionary != null)
{
var existProduct = db.Products.Where(p => p.Id == dupDictionary.ProductId).FirstOrDefault();
products.Add(existProduct);
status = true;
}
}
if (CompareStringHelper.CompareString(listDictionaryDB[i].Name, productNameFirst) > 0.85)
{
var id = listDictionaryDB[i].ProductId;
var product = db.Products.Where(p => p.Id == id).FirstOrDefault();
if (products != null)
{
var result = products.Where(p => p.Id == product.Id).FirstOrDefault();
if (result == null)
{
products.Add(product);
}
}
else
{
products.Add(product);
}
}
}
return products;
}
public ActionResult SaveProducts(ListSellProductModel model)
{
model.CorrectSellProducts = (List<SellProductModel>)Session["CorrectProducts"];
var errors = ModelState.Values.Where(x => x.Errors.Count > 0);
//Trạng thái khi lưu xuống db
int countUpdate = 0;
int countInsert = 0;
List<List<SellProductModel>> dupSellProduct = new List<List<SellProductModel>>();
foreach (var product in model.CorrectSellProducts)
{
SmartBuyEntities db = new SmartBuyEntities();
//Trùng data
var productNameFirst = product.Name.Split(';').First(); // Cắt chuỗi
var dupMarket = db.Markets.Where(m => m.Name.Equals(product.MarketName)).FirstOrDefault();
var dupProduct = db.Products.Where(p => p.Name.Equals(productNameFirst)).FirstOrDefault();
var dupProductDictionary = db.Dictionaries.Where(p => p.Name.Equals(productNameFirst)).FirstOrDefault();
//Trung hoan toan
if (dupMarket != null & dupProduct != null)
{
TrungHoanToan(ref countUpdate, ref countInsert, product, db, dupMarket, dupProduct);
}
else if (dupMarket == null & dupProduct != null) // Trùng Tên sản phẩm
{
countInsert = TrungTenSanPham(countInsert, product, db, dupProduct);
}
else if (dupMarket != null & dupProduct == null) // Trùng Market
{
var results = TrungMarket(ref countInsert, product, db, productNameFirst, dupMarket, dupProductDictionary);
if (results != null)
{
dupSellProduct.Add(results);
}
}
else //Insert sellProduct mới
{
countInsert = AddNewProduct(countInsert, product, db, productNameFirst, dupProductDictionary);
}
}
if (dupSellProduct.Count() > 0)
{
TempData["DictionaryProduct"] = dupSellProduct;
}
Session.Remove("CorrectProducts");
Session.Remove("duplicateProducts");
Session["duplicateProducts"] = dupSellProduct;
TempData["UpdateMessage"] = "Có " + countUpdate + " sản phẩm được cập nhật giá.";
TempData["InsertMessage"] = "Có " + countInsert + " sản phẩm được lưu mới.";
return RedirectToAction("UploadProduct");
}
/// <summary>
/// Can we buy all products with given markets?
/// </summary>
/// <returns>List of products which cannot buy</returns>
private List<Product> CannotBuy()
{
var result = new List<Product>();
using (var context = new SmartBuyEntities())
{
// Check each product
foreach (var product in AllProducts)
{
bool found = false;
// In each market
foreach (var market in Markets)
{
var sell = context.SellProducts
.OrderByDescending(x => x.LastUpdatedTime)
.FirstOrDefault(x => x.ProductId == product.Id && x.MarketId == market.Id);
if (sell != null)
{
found = true;
break;
}
}
// Cannot buy?
if (!found)
{
result.Add(product);
}
}
}
return result;
}
private static int TrungTenSanPham(int countInsert, SellProductModel product, SmartBuyEntities db, Product dupProduct)
{
var dupProductAtt = db.ProductAttributes.Where(p => p.ProductId.Equals(dupProduct.Id)).FirstOrDefault();
// Cập nhật giá Min, Max ProductAttribute
PriceHelper helper = new PriceHelper();
helper.CalculatePriceRange(dupProductAtt.Product.Id);
var market = new Market
{
Name = product.MarketName,
IsActive = true,
};
var newMarket = db.Markets.Add(market); //add market
var sellProduct = new SmartB.UI.Models.EntityFramework.SellProduct
{
Market = newMarket,
Product = dupProduct,
SellPrice = product.Price,
LastUpdatedTime = DateTime.Now
};
var addedSellProduct = db.SellProducts.Add(sellProduct); // Add SellProduct
countInsert++;
db.SaveChanges(); // Save to database
return countInsert;
}
public static List<double> DistanceToAllMarket(string route, string[] ids, string type)
{
var math = new MathHelper();
var result = new List<double>();
var markets = new List<Market>();
var model = JsonConvert.DeserializeObject<RouteModel>(route);
using (var context = new SmartBuyEntities())
{
// Construct a market list
foreach (string id in ids)
{
int tmp = Int32.Parse(id);
var market = context.Markets.FirstOrDefault(x => x.Id == tmp);
if (market != null)
{
markets.Add(market);
}
}
}
Coordinate point = new Coordinate();
switch (type)
{
case "start":
point = model.Start;
break;
case "end":
point = model.End;
break;
}
foreach (Market market in markets)
{
double distance = math.TravelDistance(point.Lat, point.Lng, market.Latitude.Value, market.Longitude.Value);
result.Add(distance);
}
return result;
}