private string createExportVendorProductSqlQuery(ExportVendorProduct model)
{
var sqlQuery = string.Empty;
if (model.IsAllProductItems)
{
var whereSb = new StringBuilder();
if (!string.IsNullOrEmpty(model.SearchString))
{
whereSb.AppendFormat("(vendorproducts.EisSupplierSKU LIKE \'%{0}%\' OR vendorproducts.SupplierSKU LIKE \'%{0}%\' OR vendorproducts.Category LIKE \'%{0}%\' OR vendorproducts.Name LIKE \'%{0}%\') AND ", model.SearchString.Replace("_", "\\_"));
}
whereSb.Append("(@VendorId = -1 OR vendorproducts.VendorId = @VendorId) ");
whereSb.Append("AND (@CompanyId = -1 OR vendors.CompanyId = @CompanyId) ");
whereSb.Append("AND ((@QuantityFrom = -1 OR @QuantityTo = -1) OR (vendorproducts.Quantity >= @QuantityFrom AND vendorproducts.Quantity <= @QuantityTo)) ");
// add criteria if we want to include EisSKU links
if (model.IsWithEisSKULink)
{
whereSb.AppendFormat("AND vendorproducts.EisSupplierSKU {0} IN (SELECT DISTINCT EisSupplierSKU FROM vendorproductlinks WHERE IsActive = 1) ", (model.WithEisSKULink == 1 ? "" : "NOT"));
}
// add criteria if it's we want to include images
if (model.IsWithImages)
{
whereSb.Append(string.Format(" AND vendorproducts.EisSupplierSKU {0} IN (SELECT DISTINCT EisSupplierSKU FROM vendorproductimages) ", model.WithImages == 1 ? "" : "NOT"));
}
sqlQuery = string.Format(@"SELECT vendorproducts.EisSupplierSKU,vendorproducts.VendorId, {0}
FROM vendorproducts vendorproducts
INNER JOIN vendors vendors ON vendors.Id = vendorproducts.VendorId
WHERE {1} {2}",
string.Join(",", model.ProductFieldsArr),
whereSb.ToString(),
(string.IsNullOrEmpty(model.SortBy) ? string.Empty : ("ORDER BY " + model.SortBy)));
}
else // only selected vendor product's EisSupplierSKUs
{
sqlQuery = string.Format(@"SELECT vendorproducts.EisSupplierSKU,vendorproducts.VendorId,{0}
FROM vendorproducts vendorproducts
INNER JOIN vendors vendors ON vendors.Id = vendorproducts.VendorId
WHERE vendorproducts.EisSupplierSKU IN ('{1}') {2}",
string.Join(",", model.ProductFieldsArr),
string.Join("','", model.SelectedEisSKUsArr),
(string.IsNullOrEmpty(model.SortBy) ? string.Empty : ("ORDER BY " + model.SortBy)));
}
return(sqlQuery);
}
public ActionResult CustomExportVendorProducts(ExportVendorProduct model)
{
var fileName = _exportDataService.CustomExportVendorProducts(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}_ExportVendorProducts.csv\"", model.RequestedDate));
Response.TransmitFile(fileInfo.FullName);
Response.Flush();
Response.End();
return(null);
}
private List <vendorproductlink> getVendorProductLinks(ExportVendorProduct model)
{
if (!model.IsIncludeEisSKULinks)
{
return(null);
}
if (model.IsAllProductItems)
{
return(_context.vendorproductlinks.Where(x => x.IsActive).ToList());
}
else
{
return(_context.vendorproductlinks
.Where(x => model.SelectedEisSKUsArr.Contains(x.EisSupplierSKU) && x.IsActive)
.ToList());
}
}
private List <vendorproductimage> getVendorProductImages(ExportVendorProduct model)
{
var images = new List <vendorproductimage>();
//if (!model.IsIncludeImages)
// return null;
if (model.IsAllProductItems)
{
images = _context.vendorproductimages.ToList();
}
else
{
images = _context.vendorproductimages.Where(x => model.SelectedEisSKUsArr.Contains(x.EisSupplierSKU)).ToList();
}
return(images);
}
public string CustomExportVendorProducts(ExportVendorProduct model)
{
var parameters = new Dictionary <string, object>
{
{ "@VendorId", model.VendorId == null ? -1 : model.VendorId },
{ "@CompanyId", model.CompanyId == null ? -1 : model.CompanyId },
{ "@QuantityFrom", model.QuantityFrom == null ? -1 : model.QuantityFrom },
{ "@QuantityTo", model.QuantityTo == null ? -1 : model.QuantityTo },
};
var filePath = string.Format("{1}\\{0:MM}{0:dd}{0:yyyy}-{0:HH}{0:mm}{0:ss}_ExportVendorProducts.csv", model.RequestedDate, _exportFolder);
try
{
using (var streamWriter = new StreamWriter(filePath))
{
using (var conn = new MySqlConnection(_connectionString))
{
var reader = MySqlHelper.ExecuteReader(conn, CommandType.Text, createExportVendorProductSqlQuery(model), parameters);
var eisSKULinks = getVendorProductLinks(model);
var productImages = getVendorProductImages(model);
var config = new CsvConfiguration {
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, true);
csvWriter.WriteField(headerName.FileHeaderName);
}
// write the column headers for the EisSKU links; ATLEAST 5 - NEED TO REFACTOR THIS ONE
if (model.IsIncludeEisSKULinks)
{
for (var i = 1; i <= 5; i++)
{
csvWriter.WriteField(string.Format("EisSKU_{0}", i));
}
}
// 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())
{
// write the vendor product results
foreach (var field in model.ProductFieldsArr)
{
var headerName = removePrefixTable(field);
csvWriter.WriteField(reader[headerName.DbColumnName]);
}
// write the EisSKU links
if (model.IsIncludeEisSKULinks)
{
// get the EIS SKU
var eisSupplierSKU = reader["EisSupplierSKU"].ToString();
var links = eisSKULinks.Where(x => x.EisSupplierSKU == eisSupplierSKU).ToList();
foreach (var link in links)
{
csvWriter.WriteField(link.EisSKU);
}
}
if (model.IsIncludeImages)
{
var eisSku = reader["EisSupplierSKU"].ToString();
var images = productImages.Where(x => x.EisSupplierSKU == eisSku).ToList();
for (var i = 0; i < images.Count && i < 5; i++)
{
csvWriter.WriteField(_imageHelper.GetVendorProductImageUri(eisSku, images[i].FileName));
}
}
csvWriter.NextRecord();
}
}
}
_logger.Add(LogEntrySeverity.Information, LogEntryType.ExportDataService, "Vendor products have been successfully exported -> " + filePath);
return(filePath);
}
catch (Exception ex)
{
_logger.Add(LogEntrySeverity.Error, LogEntryType.ExportDataService,
string.Format("Error in custom export vendor product file. <br/> Error message: {0}", EisHelper.GetExceptionMessage(ex)),
ex.StackTrace);
throw ex;
}
}
