public ActionResult CustomExportProducts(ExportProduct model)
{
var fileName = _exportDataService.CustomExportProducts(model);
var fileInfo = new FileInfo(fileName);
Response.Clear();
Response.ContentType = "text/csv";
Response.AddHeader("Content-Length", fileInfo.Length.ToString());
Response.AddHeader("Content-disposition", string.Format("attachment; filename=\"{0:MM}{0:dd}{0:yyyy}-{0:HH}{0:mm}{0:ss}_ExportProducts.csv\"", model.RequestedDate));
Response.TransmitFile(fileInfo.FullName);
Response.Flush();
Response.End();
return(null);
}
public IActionResult RemoveExport(int id)
{
try
{
ExportProduct export = context.ExportProduct.Find(id);
context.ExportProduct.Remove(export);
context.SaveChanges();
}
catch (Exception e)
{
return(Json(false));
}
return(Json(true));
}
//[TestMethod]
public void ShouldFilter_CustomExport_ASIN()
{
//Arrange
ExportDataService service = new ExportDataService(null, null);
ExportProduct param = new ExportProduct();
param.HasASIN = true;
param.IsAllProductItems = true;
//Act
var result = service.CustomExportProducts(param).ToList();
//Assert
Debug.WriteLine(result.Count);
// Assert.IsTrue(result.Count > 0, "No Items Found.");
}
public ActionResult Export(string id)
{
if (id == "1")
{
var formatter = new XmlSerializer(typeof(ExportProduct[]));
var stream = new MemoryStream();
ExportProduct[] products = new ExportProduct[db.Products.Count(x => x.ID > 0)];
int i = 0;
foreach (var item in db.Products)
{
ExportProduct ex = new ExportProduct();
ex.ID = item.ID;
ex.Picture = item.Picture != null ? item.Picture : "";
ex.Price = item.Price != null ? (decimal)item.Price : 0;
ex.ProductName = item.ProductName != null ? item.ProductName : "";
ex.ReceivedTime = item.ReceivedTime != null ? (DateTime)item.ReceivedTime : DateTime.Now;
ex.Sale = item.Sale != null ? (int)item.Sale : 0;
ex.SalePrice = item.SalePrice != null ? (decimal)item.SalePrice : 0;
ex.SaleTimeProduct = item.SaleTimeProduct != null ? (int)item.SaleTimeProduct : 0;
ex.FeaturedProduct = item.FeaturedProduct != null ? (int)item.FeaturedProduct : 0;
ex.ExpiredDate = item.ExpiredDate != null ? (DateTime)item.ExpiredDate : DateTime.Now;
ex.Description = item.Description != null ? item.Description : "";
ex.CategoryID = item.CategoryID != null ? (int)item.CategoryID : 0;
ex.language = item.language != null ? item.language : "";
products[i] = ex;
i++;
}
formatter.Serialize(stream, products);
stream.Position = 0;
ViewBag.message = "Products are exported";
return(File(stream, "application/xml", "allitemlist.xml"));
}
else
{
ViewBag.message = "Products are not exported";
return(View(db.Products.ToList()));
}
}
public IActionResult ExportProduct(ExportProduct model)
{
try
{
context.ExportProduct.Add(new ExportProduct {
IdTrip = model.IdTrip,
NameCategorize = model.NameCategorize,
IdProductInTrip = model.IdProductInTrip,
DateCreate = DateTime.Now,
Unit = model.Unit,
TotalPrice = model.TotalPrice,
UnitPrice = model.TotalPrice / model.Unit,
Note = model.Note,
});
context.SaveChanges();
}
catch (Exception e)
{
return(View());
}
return(RedirectToAction("Index"));
}
public List <ExportProduct> ExportProducts()
{
var products = _productService.GetList();
var exportProducts = new List <ExportProduct>();
string pathImages = Path.Combine(_hostingEnvironment.WebRootPath, "images");
foreach (var product in products)
{
var exportProduct = new ExportProduct();
exportProduct.Name = product.Name;
exportProduct.ImageFile = product.ImageFile;
exportProduct.ImageBase64 = FormHelper.GetBase64(Path.Combine(pathImages, exportProduct.ImageFile));
exportProduct.Price = product.Price;
exportProduct.Count = product.Count;
exportProducts.Add(exportProduct);
}
return(exportProducts);
}
private List <productimage> getProductImages(ExportProduct model)
{
var images = new List <productimage>();
if (!model.IsIncludeImages)
{
return(null);
}
if (model.IsAllProductItems)
{
images = _context.productimages
.Where(x => x.ImageType == "CUSTOM" || x.ImageType == "LARGE")
.ToList();
}
else
{
images = _context.productimages.Where(x => model.SelectedEisSKUsArr.Contains(x.EisSKU) &&
(x.ImageType == "CUSTOM" || x.ImageType == "LARGE"))
.ToList();
}
return(images);
}
public JsonResult SaveProduct(string userid, string ProductCode, string commonname, string formulation, string chinesespecification, string productenterprise)
{
int UserId = Int32.Parse(userid);
Hashtable htparm = new Hashtable();
htparm["uid"] = Convert.ToInt32(userid);
htparm["pc"] = ProductCode;
ExportProduct Obj = ProjectManagementMapper.Get().QueryForObject<ExportProduct>("ExportProduct.Query", htparm);
if (Obj != null)
{
htparm["commonname"] = commonname;
htparm["formulation"] = formulation;
htparm["chinesespecification"] = chinesespecification;
htparm["productenterprise"] = productenterprise;
ProjectManagementMapper.Get().Update("ExportProduct.MyUpdate", htparm);
}
else
{
Obj = new ExportProduct();
htparm["commonname"] = commonname;
htparm["formulation"] = formulation;
htparm["chinesespecification"] = chinesespecification;
htparm["productenterprise"] = productenterprise;
ProjectManagementMapper.Get().Insert("ExportProduct.MyInsert", htparm);
}
return Json("s");
}
public ActionResult MakeProductDoc_Save(ExportProduct ep)
{
int flag = 0;
Dictionary<string, string> sitemaster = GetSiteMaster();
ViewData["SiteMaster"] = sitemaster;
int uid = Convert.ToInt32(sitemaster["userid"]);
int maxcount = 0;
if (sitemaster["Memberlevel"] == "定制")
maxcount = ProjectManagementMapper.Get().QueryForObject<int>("MakeBidDocZJ.findmaxcount", uid);
else
maxcount = 999;
ViewData["maxcount"] = maxcount;
//判断是否超过指定记录数
int count = ProjectManagementMapper.Get().QueryForObject<int>("ExportProduct.QueryCount", uid);
if (count < maxcount)
{
//找到此用户此产品的记录
Hashtable htparm = new Hashtable();
htparm["uid"] = uid;
htparm["pc"] = ep.ProductCode;
ExportProduct Obj = ProjectManagementMapper.Get().QueryForObject<ExportProduct>("ExportProduct.Query", htparm);
//
//如果已经保存了产品基本信息，就继续
if (Obj != null)
{
Obj.Label1 = ep.Label1;
Obj.Label2 = ep.Label2;
Obj.Label3 = ep.Label3;
Obj.Label4 = ep.Label4;
Obj.Label37 = ep.Label37;
Obj.Label42 = ep.Label42;
Obj.Label5 = ep.Label5;
Obj.Label6 = ep.Label6;
Obj.Label43 = ep.Label43;
Obj.Label7 = ep.Label7;
Obj.Label8 = ep.Label8;
new ExportProductDao().Update(Obj);
}
else
{
flag = 2;
}
}
else
flag = 1;
ViewData["flag"] = flag;
return new RedirectResult("/ProjectManagement/MakeProductDoc.mvc/" + ep.ProductCode);
}
public ActionResult MakeProductDoc(string ProductCode)
{
Dictionary<string, string> sitemaster = GetSiteMaster();
ViewData["SiteMaster"] = sitemaster;
int UserId = Convert.ToInt32(sitemaster["userid"]);
ProjectManagementMapper.Get().QueryForObject<IDictionary>("ExportProduct.GetProducts", UserId);
//得到产品信息
CompareData cd = new CompareDataDao().FindByProductCode(ProductCode).First();
Hashtable htparm = new Hashtable();
htparm["uid"] = Convert.ToInt32(sitemaster["userid"]);
htparm["pc"] = ProductCode;
ExportProduct Obj = ProjectManagementMapper.Get().QueryForObject<ExportProduct>("ExportProduct.Query", htparm);
if (Obj == null)
{
Obj = new ExportProduct();
ViewData["IsExist"] = 0;
}
else
{
ViewData["IsExist"] = 1;
}
ViewData["PC"] = ProductCode;
ViewData["CommonName"] = cd.ChineseProductName;
ViewData["Formulation"] = cd.Formulation;
ViewData["ChineseSpecification"] = cd.ChineseSpecification;
ViewData["ChineseManufactureName"] = cd.ChineseManufactureName;
return View(Obj);
}
private string createProductQuery(ExportProduct model)
{
var sb = new StringBuilder(@"
SELECT
{0}
FROM products p
LEFT JOIN shadows s ON s.ShadowSKU = p.EisSKU AND s.IsConnected = 1
LEFT JOIN productamazons pa ON pa.EisSKU = p.EisSKU
LEFT JOIN productebays pe ON pe.EisSKU = p.EisSKU
LEFT JOIN productbigcommerces pbc ON pbc.EisSKU = p.EisSKU
WHERE 1 = 1 ");
if (!string.IsNullOrEmpty(model.SearchString))
{
sb.AppendFormat(@" AND (p.EisSKU LIKE '%{0}%' OR p.UPC LIKE '%{0}%' OR p.Name LIKE '%{0}%' OR p.Category LIKE '%{0}%')", model.SearchString.Replace("_", "\\_"));
}
if (model.ProductGroupId != -1 && model.ProductGroupId != null)
{
sb.AppendFormat(" AND p.EisSKU IN (SELECT EisSKU FROM productgroups WHERE Id = {0})", model.ProductGroupId);
}
if (model.VendorId != -1 && model.VendorId != null)
{
sb.AppendFormat(" AND p.EisSKU IN (SELECT DISTINCT EisSKU FROM vw_availablevendorproducts WHERE VendorId = {0})", model.VendorId);
}
if (model.IsQuantityFromSet && model.IsQuantityToSet)
{
sb.AppendFormat(" AND p.EisSKU IN (SELECT DISTINCT EisSKU FROM vw_availablevendorproducts WHERE Quantity >= {0} AND Quantity <= {1})", model.QuantityFrom ?? -1, model.QuantityTo ?? -1);
}
if (model.IsWithImages)
{
sb.AppendFormat(" AND p.EisSKU {0} IN (SELECT DISTINCT EisSKU FROM productimages)", model.WithImages == 1 ? "" : "NOT");
}
if (model.IsSKULinked.HasValue)
{
sb.AppendFormat(" AND p.EisSKU {0} IN (SELECT DISTINCT EisSKU FROM vendorproductlinks)", model.IsSKULinked.Value ? "" : "NOT");
}
if (model.CompanyId != -1 && model.CompanyId != null)
{
sb.AppendFormat(" AND p.CompanyId = {0}", model.CompanyId);
}
if (model.SkuType.HasValue)
{
sb.AppendFormat(" AND p.SkuType = {0}", (int)model.SkuType);
}
if (model.IsKit.HasValue)
{
sb.AppendFormat(" AND p.IsKit = {0}", model.IsKit.Value ? 1 : 0);
}
if (model.IsAmazonEnabled.HasValue)
{
sb.AppendFormat(" AND pa.IsEnabled = {0}", model.IsAmazonEnabled.Value ? 1 : 0);
}
if (model.HasASIN != null)
{
sb.AppendFormat(" AND pa.ASIN IS {0} NULL", model.HasASIN.Value ? "NOT" : "");
}
// apply the order if there's any
if (!string.IsNullOrEmpty(model.SortBy))
{
sb.AppendFormat(" ORDER BY {0}", getParsedOrderBy(model.SortBy));
}
return(string.Format(sb.ToString(), getParsedProductColumns(model.ProductFieldsArr)));
}
public string CustomExportProducts(ExportProduct model)
{
var filePath = string.Format("{1}\\{0:MM}{0:dd}{0:yyyy}-{0:HH}{0:mm}{0:ss}_ExportProducts.csv", model.RequestedDate, _exportFolder);
try
{
using (var streamWriter = new StreamWriter(filePath))
{
using (var conn = new MySqlConnection(_connectionString))
{
var reader = MySqlHelper.ExecuteReader(conn, CommandType.Text, createProductQuery(model), null);
var productImages = getProductImages(model);
var config = new CsvConfiguration();
config.Delimiter = model.Delimiter;
var csvWriter = new CsvWriter(streamWriter, config);
// write the header text for the CSV files
foreach (var field in model.ProductFieldsArr)
{
var headerName = removePrefixTable(field);
csvWriter.WriteField(headerName.FileHeaderName);
}
// write the column headers for the product's images
if (model.IsIncludeImages)
{
for (var i = 1; i <= 5; i++)
{
csvWriter.WriteField(string.Format("ImageUrl{0}", i));
}
}
csvWriter.NextRecord();
while (reader.Read())
{
foreach (var field in model.ProductFieldsArr)
{
var headerName = removePrefixTable(field);
csvWriter.WriteField(reader[headerName.DbColumnName]);
}
// write the image URLs if selected
if (model.IsIncludeImages)
{
// get the EIS SKU
var eisSku = reader["EisSKU"].ToString();
var images = productImages.Where(x => x.EisSKU == eisSku).ToList();
for (var i = 0; i < images.Count && i < 5; i++)
{
csvWriter.WriteField(_imageHelper.GetProductImageUri(eisSku, images[i].FileName));
}
}
csvWriter.NextRecord();
}
}
}
_logger.Add(LogEntrySeverity.Information, LogEntryType.ExportDataService, "Products have been successfully exported -> " + filePath);
return(filePath);
}
catch (Exception ex)
{
_logger.Add(LogEntrySeverity.Error, LogEntryType.ExportDataService,
string.Format("Error in custom export product file. <br/> Error message: {0}", EisHelper.GetExceptionMessage(ex)),
ex.StackTrace);
throw ex;
}
}
