public async Task <ApiResponse> UpdatePackagingList(List <PackagingList> lists, int productId)
{
try
{
List <PackagingList> originalList = await _packagingListDataRepository.Queryable.Where(pl => pl.ProductId == productId).GetListAsync();
_packagingListDataRepository.DeleteRange(originalList);
await _packagingListDataRepository.SaveChangesAsync();
_packagingListDataRepository.CreateRange(lists);
await _packagingListDataRepository.SaveChangesAsync();
return(new ApiResponse()
{
Status = "success",
Message = "Successfully update packaging"
});
}
catch (Exception ex)
{
return(new ApiResponse()
{
Status = "fail",
Message = ex.Message
});
}
}
public async Task <IActionResult> UploadProducts()
{
try
{
var httpRequest = HttpContext.Request;
if (httpRequest.Form.Files.Count > 0)
{
var uploadFile = httpRequest.Form.Files[0]; // get the uploaded file
if (uploadFile != null && uploadFile.Length > 0)
{
var path = Path.Combine(Directory.GetCurrentDirectory(), "wwwroot", "ImportProducts.xlsx");
if (System.IO.File.Exists(path))
{
System.IO.File.Delete(path);
}
using (var stream = new FileStream(path, FileMode.Create))
{
uploadFile.CopyTo(stream);
stream.Close();
}
FileInfo file = new FileInfo(path);
using (ExcelPackage package = new ExcelPackage(file))
{
ExcelWorksheet workSheet = package.Workbook.Worksheets["Trojan Product items as at 2407"];
int totalRows = workSheet.Dimension.Rows;
List <Product> productList = new List <Product>();
List <Product> updateProductList = new List <Product>();
for (int i = 2; i <= totalRows; i++)
{
if (workSheet.Cells[i, 2].Value != null)
{
if (_productDataRepository.Queryable.Where(x => x.Name == workSheet.Cells[i, 3].Value.ToString()).Count() < 1)
{
productList.Add(new Product()
{
ItemCode = workSheet.Cells[i, 2].Value == null ? "" : workSheet.Cells[i, 2].Value.ToString(),
Name = workSheet.Cells[i, 3].Value == null ? "" : workSheet.Cells[i, 3].Value.ToString(),
Category = workSheet.Cells[i, 4].Value == null ? "" : workSheet.Cells[i, 4].Value.ToString().Trim().Replace(' ', '-'),
OriginalPrice = workSheet.Cells[i, 6].Value == null ? 0 : double.Parse(workSheet.Cells[i, 6].Value.ToString().Replace("$", "").Trim()),
AgentPrice = workSheet.Cells[i, 7].Value == null ? 0 : double.Parse(workSheet.Cells[i, 7].Value.ToString().Replace("$", "").Trim()),
WholesalerPrice = workSheet.Cells[i, 8].Value == null ? 0 : double.Parse(workSheet.Cells[i, 8].Value.ToString().Replace("$", "").Trim()),
PrepaymentDiscount = workSheet.Cells[i, 9].Value == null ? 0 : double.Parse(workSheet.Cells[i, 9].Value.ToString().Replace("$", "").Trim()),
Status = workSheet.Cells[i, 10].Value == null ? "" : workSheet.Cells[i, 10].Value.ToString()
});
}
else
{
var product = _productDataRepository.Queryable.Where(x => x.Name == workSheet.Cells[i, 3].Value.ToString()).FirstOrDefault();
product.ItemCode = workSheet.Cells[i, 2].Value == null ? "" : workSheet.Cells[i, 2].Value.ToString();
product.Name = workSheet.Cells[i, 3].Value == null ? "" : workSheet.Cells[i, 3].Value.ToString();
product.Category = workSheet.Cells[i, 4].Value == null ? "" : workSheet.Cells[i, 4].Value.ToString().Trim().Replace(' ', '-');
product.OriginalPrice = workSheet.Cells[i, 6].Value == null ? 0 : double.Parse(workSheet.Cells[i, 6].Value.ToString().Replace("$", "").Trim());
product.AgentPrice = workSheet.Cells[i, 7].Value == null ? 0 : double.Parse(workSheet.Cells[i, 7].Value.ToString().Replace("$", "").Trim());
product.WholesalerPrice = workSheet.Cells[i, 8].Value == null ? 0 : double.Parse(workSheet.Cells[i, 8].Value.ToString().Replace("$", "").Trim());
product.PrepaymentDiscount = workSheet.Cells[i, 9].Value == null ? 0 : double.Parse(workSheet.Cells[i, 9].Value.ToString().Replace("$", "").Trim());
product.Status = workSheet.Cells[i, 10].Value == null ? "" : workSheet.Cells[i, 10].Value.ToString();
_productDataRepository.Update(product);
await _productDataRepository.SaveChangesAsync();
}
}
}
if (productList.Count > 0)
{
_productDataRepository.CreateRange(productList);
await _productDataRepository.SaveChangesAsync();
}
for (int i = 2; i <= totalRows; i++)
{
if (workSheet.Cells[i, 2].Value != null)
{
if (_packagingListDataRepository.Queryable.Where(x => x.ProductId == _productDataRepository.Queryable.Where(y => y.Name == workSheet.Cells[i, 3].Value.ToString()).FirstOrDefault().Id).Count() < 1)
{
List <PackagingList> PackageNames = new List <PackagingList>();
string PackageName = workSheet.Cells[i, 5].Value == null ? "" : workSheet.Cells[i, 5].Value.ToString();
int productId = _productDataRepository.Queryable.Where(x => x.Name == workSheet.Cells[i, 3].Value.ToString()).FirstOrDefault().Id;
if (PackageName.ToLower().Contains("op"))
{
PackageNames.Add(new PackagingList()
{
ProductId = productId,
PackageName = "OP"
});
}
if (PackageName.ToLower().Contains("pp"))
{
PackageNames.Add(new PackagingList()
{
ProductId = productId,
PackageName = "PP"
});
}
_packagingListDataRepository.CreateRange(PackageNames);
await _packagingListDataRepository.SaveChangesAsync();
}
else
{
var updatePackageNames = _packagingListDataRepository.Queryable.Where(x => x.ProductId == _productDataRepository.Queryable.Where(y => y.Name == workSheet.Cells[i, 3].Value.ToString()).FirstOrDefault().Id).FirstOrDefault();
string PackageName = workSheet.Cells[i, 5].Value == null ? "" : workSheet.Cells[i, 5].Value.ToString();
int productId = _productDataRepository.Queryable.Where(x => x.Name == workSheet.Cells[i, 3].Value.ToString()).FirstOrDefault().Id;
if (PackageName.ToLower().Contains("op"))
{
updatePackageNames.ProductId = productId;
updatePackageNames.PackageName = "OP";
}
else if (PackageName.ToLower().Contains("pp"))
{
updatePackageNames.ProductId = productId;
updatePackageNames.PackageName = "PP";
}
else
{
_packagingListDataRepository.Delete(updatePackageNames);
await _packagingListDataRepository.SaveChangesAsync();
}
_packagingListDataRepository.Update(updatePackageNames);
await _packagingListDataRepository.SaveChangesAsync();
}
}
}
package.Stream.Close();
package.Dispose();
}
return(Ok(new ApiResponse
{
Status = "success",
Message = "Products uploaded."
}));
}
}
return(Ok(new ApiResponse
{
Status = "fail",
Message = "Upload file is empty"
}));
}
catch (Exception ex)
{
return(Ok(new ApiResponse
{
Status = "fail",
Message = ex.Message
}));
}
}
public async Task <IActionResult> UploadUsers()
{
try
{
var httpRequest = HttpContext.Request;
if (httpRequest.Form.Files.Count > 0)
{
var uploadFile = httpRequest.Form.Files[0]; // get the uploaded file
if (uploadFile != null && uploadFile.Length > 0)
{
var path = Path.Combine(Directory.GetCurrentDirectory(), "wwwroot", "ImportUsers.xlsx");
if (System.IO.File.Exists(path))
{
System.IO.File.Delete(path);
}
using (var stream = new FileStream(path, FileMode.Create))
{
uploadFile.CopyTo(stream);
stream.Close();
}
FileInfo file = new FileInfo(path);
using (ExcelPackage package = new ExcelPackage(file))
{
ExcelWorksheet workSheet = package.Workbook.Worksheets["Sheet1"];
int totalRows = workSheet.Dimension.Rows;
List <User> userList = new List <User>();
for (int i = 3; i <= totalRows; i++)
{
if (workSheet.Cells[i, 1].Value != null)
{
StringBuilder builder = new StringBuilder();
builder.Append(share.RandomString(4, true));
builder.Append(share.RandomNumber(1000, 9999));
builder.Append(share.RandomString(2, false));
userList.Add(new User()
{
Account = workSheet.Cells[i, 1].Value == null ? "" : workSheet.Cells[i, 1].Value.ToString(),
Password = builder.ToString(),
BussinessName = workSheet.Cells[i, 2].Value == null ? "" : workSheet.Cells[i, 2].Value.ToString(),
BillingStreetNumber = workSheet.Cells[i, 3].Value == null ? "" : workSheet.Cells[i, 3].Value.ToString(),
BillingAddressLine = workSheet.Cells[i, 4].Value == null ? "" : workSheet.Cells[i, 4].Value.ToString(),
BillingSuburb = workSheet.Cells[i, 5].Value == null ? "" : workSheet.Cells[i, 5].Value.ToString(),
BillingState = workSheet.Cells[i, 6].Value == null ? "" : workSheet.Cells[i, 6].Value.ToString(),
BillingPostCode = workSheet.Cells[i, 7].Value == null ? "" : workSheet.Cells[i, 7].Value.ToString(),
ShippingStreetNumber = workSheet.Cells[i, 8].Value == null ? "" : workSheet.Cells[i, 8].Value.ToString(),
ShippingAddressLine = workSheet.Cells[i, 9].Value == null ? "" : workSheet.Cells[i, 9].Value.ToString(),
ShippingSuburb = workSheet.Cells[i, 10].Value == null ? "" : workSheet.Cells[i, 10].Value.ToString(),
ShippingState = workSheet.Cells[i, 11].Value == null ? "" : workSheet.Cells[i, 11].Value.ToString(),
ShippingPostCode = workSheet.Cells[i, 12].Value == null ? "" : workSheet.Cells[i, 12].Value.ToString(),
Phone = workSheet.Cells[i, 13].Value == null ? "" : workSheet.Cells[i, 13].Value.ToString(),
CompanyPhone = workSheet.Cells[i, 14].Value == null ? "" : workSheet.Cells[i, 14].Value.ToString(),
Mobile = workSheet.Cells[i, 15].Value == null ? "" : workSheet.Cells[i, 15].Value.ToString(),
Email = workSheet.Cells[i, 16].Value == null ? "" : workSheet.Cells[i, 16].Value.ToString(),
Role = workSheet.Cells[i, 17].Value == null ? "" : workSheet.Cells[i, 17].Value.ToString()
});
}
}
package.Stream.Close();
package.Dispose();
_userDataRepository.CreateRange(userList);
await _userDataRepository.SaveChangesAsync();
}
return(Ok(new ApiResponse
{
Status = "success",
Message = "Users uploaded."
}));
}
}
return(Ok(new ApiResponse
{
Status = "fail",
Message = "Upload file is empty"
}));
}
catch (Exception ex)
{
return(Ok(new ApiResponse
{
Status = "fail",
Message = ex.Message
}));
}
}