public async Task <CommonLookupRespObj> Handle(GetAllCountriesQuery request, CancellationToken cancellationToken)
{
return(await _serverRequest.GetAllCountryAsync());
}
public async Task <byte[]> Handle(DownloadSupplierInformationQuery request, CancellationToken cancellationToken)
{
try
{
Byte[] File = new Byte[0];
var _DomainList = await _repo.GetAllSupplierAsync();
var _SupplierType = await _repo.GetAllSupplierTypeAsync();
var country = await _serverRequest.GetAllCountryAsync();
DataTable dt = new DataTable();
dt.Columns.Add("SupplierType");
dt.Columns.Add("Name");
dt.Columns.Add("Address");
dt.Columns.Add("PhoneNo");
dt.Columns.Add("Email");
dt.Columns.Add("RegistrationNo");
dt.Columns.Add("SupplierNumber");
dt.Columns.Add("Country");
dt.Columns.Add("Status");
dt.Columns.Add("Website");
dt.Columns.Add("PostalAddress");
dt.Columns.Add("TaxID_VatID");
dt.Columns.Add("WorkPermit");
dt.Columns.Add("Particulars");
var _ContractList = _DomainList.Select(a => new SupplierObj
{
SupplierTypeName = _SupplierType.FirstOrDefault(d => d.SupplierTypeId == a.SupplierTypeId)?.SupplierTypeName,
Name = a.Name,
Address = a.Address,
PhoneNo = a.PhoneNo,
Email = a.Email,
RegistrationNo = a.RegistrationNo,
SupplierNumber = a.SupplierNumber,
CountryName = country.commonLookups.FirstOrDefault(w => w.LookupId == a.CountryId)?.LookupName,
Website = a.Website,
PostalAddress = a.PostalAddress,
TaxIDorVATID = a.TaxIDorVATID,
WorkPermitName = a.HaveWorkPrintPermit ? "Yes" : "No",
ParticularsName = a.Particulars == 2 ? "Corporate" : "Individual",
StatusName = Convert.ToString((ApprovalStatus)a.ApprovalStatusId),
}).ToList();
if (_ContractList.Count() > 0)
{
foreach (var itemRow in _ContractList)
{
var row = dt.NewRow();
row["SupplierType"] = itemRow.SupplierTypeName;
row["Name"] = itemRow.Name;
row["Address"] = itemRow.Address;
row["PhoneNo"] = itemRow.PhoneNo;
row["Email"] = itemRow.Email;
row["RegistrationNo"] = itemRow.RegistrationNo;
row["SupplierNumber"] = itemRow.SupplierNumber;
row["Country"] = itemRow.CountryName;
row["Status"] = itemRow.StatusName;
row["Website"] = itemRow.Website;
row["PostalAddress"] = itemRow.PostalAddress;
row["TaxID_VatID"] = itemRow.TaxIDorVATID;
row["WorkPermit"] = itemRow.WorkPermitName;
row["Particulars"] = itemRow.ParticularsName;
dt.Rows.Add(row);
}
if (_ContractList != null)
{
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (ExcelPackage pck = new ExcelPackage())
{
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("SupplierInformation");
ws.DefaultColWidth = 20;
ws.Cells["A1"].LoadFromDataTable(dt, true, OfficeOpenXml.Table.TableStyles.None);
File = pck.GetAsByteArray();
}
}
}
return(File);
}
catch (Exception ex)
{
throw ex;
}
}
public async Task <FileUploadRespObj> Handle(UploadSupplierInformationCommand request, CancellationToken cancellationToken)
{
try
{
var apiResponse = new FileUploadRespObj {
Status = new APIResponseStatus {
IsSuccessful = false, Message = new APIResponseMessage()
}
};
var files = _accessor.HttpContext.Request.Form.Files;
var byteList = new List <byte[]>();
foreach (var fileBit in files)
{
if (fileBit.Length > 0)
{
using (var ms = new MemoryStream())
{
await fileBit.CopyToAsync(ms);
byteList.Add(ms.ToArray());
}
}
}
var userId = _accessor.HttpContext.User?.FindFirst(x => x.Type == "userId").Value;
var uploadedRecord = new List <SupplierObj>();
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
if (byteList.Count() > 0)
{
foreach (var byteItem in byteList)
{
using (MemoryStream stream = new MemoryStream(byteItem))
using (ExcelPackage excelPackage = new ExcelPackage(stream))
{
ExcelWorksheet workSheet = excelPackage.Workbook.Worksheets[0];
int totalRows = workSheet.Dimension.Rows;
int totalColumns = workSheet.Dimension.Columns;
if (totalColumns != 14)
{
apiResponse.Status.Message.FriendlyMessage = "14 Columns Expected";
return(apiResponse);
}
var item = new SupplierObj();
for (int i = 2; i <= totalRows; i++)
{
item.ExcelLineNumber = i;
item.SupplierTypeName = workSheet.Cells[i, 1]?.Value != null ? workSheet.Cells[i, 1]?.Value.ToString() : string.Empty;
item.Name = workSheet.Cells[i, 2]?.Value != null ? workSheet.Cells[i, 2]?.Value.ToString() : string.Empty;
item.Address = workSheet.Cells[i, 3]?.Value != null ? workSheet.Cells[i, 3]?.Value.ToString() : string.Empty;
item.PhoneNo = workSheet.Cells[i, 4]?.Value != null ? workSheet.Cells[i, 4]?.Value.ToString() : string.Empty;
item.Email = workSheet.Cells[i, 5]?.Value != null ? workSheet.Cells[i, 5]?.Value.ToString() : string.Empty;
item.RegistrationNo = workSheet.Cells[i, 6]?.Value != null ? workSheet.Cells[i, 6]?.Value.ToString() : string.Empty;
item.SupplierNumber = workSheet.Cells[i, 7]?.Value != null ? workSheet.Cells[i, 7]?.Value.ToString() : string.Empty;
item.CountryName = workSheet.Cells[i, 8]?.Value != null ? workSheet.Cells[i, 8]?.Value.ToString() : string.Empty;
item.StatusName = workSheet.Cells[i, 9]?.Value != null ? workSheet.Cells[i, 9]?.Value.ToString() : string.Empty;
item.Website = workSheet.Cells[i, 10]?.Value != null ? workSheet.Cells[i, 10]?.Value.ToString() : string.Empty;
item.PostalAddress = workSheet.Cells[i, 11]?.Value != null ? workSheet.Cells[i, 11]?.Value.ToString() : string.Empty;
item.TaxIDorVATID = workSheet.Cells[i, 12]?.Value != null ? workSheet.Cells[i, 12]?.Value.ToString() : string.Empty;
item.WorkPermitName = workSheet.Cells[i, 13]?.Value != null ? workSheet.Cells[i, 13]?.Value.ToString() : string.Empty;
item.ParticularsName = workSheet.Cells[i, 14]?.Value != null ? workSheet.Cells[i, 14]?.Value.ToString() : string.Empty;
uploadedRecord.Add(item);
}
}
}
}
var ItemList = await _repo.GetAllSupplierAsync();
var _SupplierType = await _repo.GetAllSupplierTypeAsync();
var _country = await _serverRequest.GetAllCountryAsync();
cor_supplier itemFrmRepo = new cor_supplier();
if (uploadedRecord.Count > 0)
{
foreach (var item in uploadedRecord)
{
if (string.IsNullOrEmpty(item.Name))
{
apiResponse.Status.Message.FriendlyMessage = $"Empty Supplier name detected on line {item.ExcelLineNumber}";
return(apiResponse);
}
else
{
if (item.Name.Split(" ").Length == 1)
{
apiResponse.Status.Message.FriendlyMessage = $"Invalid Supplier name detected on line {item.ExcelLineNumber}";
return(apiResponse);
}
}
itemFrmRepo = ItemList.FirstOrDefault(c => c.Name.ToLower().Trim() == item.Name.ToLower().Trim());
if (string.IsNullOrEmpty(item.SupplierTypeName))
{
apiResponse.Status.Message.FriendlyMessage = $"Empty Supplier type detected on line {item.ExcelLineNumber}";
return(apiResponse);
}
else
{
var type = _SupplierType.FirstOrDefault(w => w.SupplierTypeName.Trim().ToLower() == item.SupplierTypeName.Trim().ToLower());
if (type == null)
{
apiResponse.Status.Message.FriendlyMessage = $"Undentified Supplier type detected on line {item.ExcelLineNumber}";
return(apiResponse);
}
item.SupplierTypeId = type.SupplierTypeId;
}
if (string.IsNullOrEmpty(item.Email))
{
apiResponse.Status.Message.FriendlyMessage = $"Empty cell on Email Column Detected on line {item.ExcelLineNumber}";
return(apiResponse);
}
else
{
try
{
MailAddress m = new MailAddress(item.Email);
}
catch (FormatException)
{
apiResponse.Status.Message.FriendlyMessage = $"Invalid Email Detected on line {item.ExcelLineNumber}";
return(apiResponse);
}
var userEmailExist = _userManager.Users?.Where(w => w.Email.Trim().ToLower() == item.Email.Trim()).ToList();
if (userEmailExist.Count() > 1)
{
apiResponse.Status.Message.FriendlyMessage = $"Duplicate Supplier Email Detected on line {item.ExcelLineNumber}";
return(apiResponse);
}
}
if (string.IsNullOrEmpty(item.Address))
{
apiResponse.Status.Message.FriendlyMessage = $"Empty cell on Address Column Detected on line {item.ExcelLineNumber}";
return(apiResponse);
}
if (string.IsNullOrEmpty(item.RegistrationNo))
{
apiResponse.Status.Message.FriendlyMessage = $"Empty cell on Reg No Column Detected on line {item.ExcelLineNumber}";
return(apiResponse);
}
if (string.IsNullOrEmpty(item.StatusName))
{
apiResponse.Status.Message.FriendlyMessage = $"Empty cell on Status name Column Detected on line {item.ExcelLineNumber}";
return(apiResponse);
}
else
{
if (!Enum.IsDefined(typeof(ApprovalStatus), item.StatusName))
{
apiResponse.Status.Message.FriendlyMessage = $"Invalid Status Detected on line {item.ExcelLineNumber}";
return(apiResponse);
}
}
if (string.IsNullOrEmpty(item.SupplierNumber) && item.StatusName.Trim().ToLower() != "pending")
{
apiResponse.Status.Message.FriendlyMessage = $"Status field of a Supplier without Supplier Number should be pending Detected on line {item.ExcelLineNumber}";
return(apiResponse);
}
if (!string.IsNullOrEmpty(item.SupplierNumber) && item.StatusName.Trim().ToLower() == "pending")
{
apiResponse.Status.Message.FriendlyMessage = $"Status field of a Supplier with Supplier Number should not be pending Detected on line {item.ExcelLineNumber}";
return(apiResponse);
}
if (!string.IsNullOrEmpty(item.SupplierNumber))
{
var supNumExist = await _repo.GetAllSupplierAsync();
if (supNumExist.Count() > 0)
{
if (supNumExist?.Where(q => q.SupplierNumber.Trim().ToLower() == item.SupplierNumber.Trim().ToLower()).Count() > 1)
{
apiResponse.Status.Message.FriendlyMessage = $"Supplier with same supplier number already exist Detected on line {item.ExcelLineNumber}";
return(apiResponse);
}
}
}
if (string.IsNullOrEmpty(item.Address))
{
apiResponse.Status.Message.FriendlyMessage = $"Empty cell on Email Column Detected on line {item.ExcelLineNumber}";
return(apiResponse);
}
if (string.IsNullOrEmpty(item.PhoneNo))
{
apiResponse.Status.Message.FriendlyMessage = $"Empty cell on PhoneNo Column Detected on line {item.ExcelLineNumber}";
return(apiResponse);
}
if (string.IsNullOrEmpty(item.Website))
{
apiResponse.Status.Message.FriendlyMessage = $"Empty cell on Wwebsite Column Detected on line {item.ExcelLineNumber}";
return(apiResponse);
}
if (string.IsNullOrEmpty(item.PostalAddress))
{
apiResponse.Status.Message.FriendlyMessage = $"Empty cell on PostalAddress Column Detected on line {item.ExcelLineNumber}";
return(apiResponse);
}
if (string.IsNullOrEmpty(item.TaxIDorVATID))
{
apiResponse.Status.Message.FriendlyMessage = $"Empty cell on Tax Vat Column Detected on line {item.ExcelLineNumber}";
return(apiResponse);
}
if (string.IsNullOrEmpty(item.WorkPermitName))
{
apiResponse.Status.Message.FriendlyMessage = $"Empty cell on Work permit Column Detected on line {item.ExcelLineNumber}";
return(apiResponse);
}
else
{
item.HaveWorkPrintPermit = item.WorkPermitName.Trim().ToLower() == "Yes".Trim().ToLower() ? true : false;
}
if (string.IsNullOrEmpty(item.ParticularsName))
{
apiResponse.Status.Message.FriendlyMessage = $"Empty cell on Particulars Column Detected on line {item.ExcelLineNumber}";
return(apiResponse);
}
else
{
item.Particulars = item.ParticularsName.Trim().ToLower() == "Corporate".ToLower().Trim() ? 2 : 1;
}
if (string.IsNullOrEmpty(item.CountryName))
{
apiResponse.Status.Message.FriendlyMessage = $"Empty cell on Country Column Detected on line {item.ExcelLineNumber}";
return(apiResponse);
}
else
{
var country = _country.commonLookups.FirstOrDefault(w => w.LookupName.Trim().ToLower() == item.CountryName.Trim().ToLower());
if (country == null)
{
apiResponse.Status.Message.FriendlyMessage = $"Undentified Country name Detected on line {item.ExcelLineNumber}";
return(apiResponse);
}
item.CountryId = country.LookupId;
}
if (itemFrmRepo != null)
{
itemFrmRepo.Address = item.Address;
itemFrmRepo.SupplierTypeId = item.SupplierTypeId;
itemFrmRepo.Name = item.Name;
itemFrmRepo.Email = item.Email;
itemFrmRepo.PhoneNo = item.PhoneNo;
itemFrmRepo.RegistrationNo = item.RegistrationNo;
itemFrmRepo.CountryId = itemFrmRepo.CountryId;
itemFrmRepo.UpdatedOn = DateTime.Now;
itemFrmRepo.Website = item.Website;
itemFrmRepo.PostalAddress = item.PostalAddress;
itemFrmRepo.TaxIDorVATID = item.TaxIDorVATID;
itemFrmRepo.SupplierNumber = itemFrmRepo.SupplierNumber;
itemFrmRepo.SupplierId = itemFrmRepo.SupplierId;
itemFrmRepo.HaveWorkPrintPermit = item.HaveWorkPrintPermit;
itemFrmRepo.Active = true;
itemFrmRepo.ApprovalStatusId = item.ApprovalStatusId;
await _repo.UpdateSupplierAsync(itemFrmRepo);
}
else
{
var newItem = new cor_supplier
{
Name = item.Name,
Address = item.Address,
PhoneNo = item.PhoneNo,
Email = item.Email,
RegistrationNo = item.RegistrationNo,
SupplierTypeId = item.SupplierTypeId,
Passport = item.Passport,
CountryId = item.CountryId,
ApprovalStatusId = (int)ApprovalStatus.Pending,
Active = true,
Deleted = false,
CreatedBy = item.CreatedBy,
CreatedOn = DateTime.Now,
UpdatedBy = item.CreatedBy,
UpdatedOn = DateTime.Now,
Website = item.Website,
PostalAddress = item.PostalAddress,
TaxIDorVATID = item.TaxIDorVATID,
SupplierNumber = item.SupplierNumber,
HaveWorkPrintPermit = item.HaveWorkPrintPermit,
Particulars = item.Particulars,
};
await _repo.AddNewSupplierAsync(newItem);
}
}
}
apiResponse.Status.IsSuccessful = true;
apiResponse.Status.Message.FriendlyMessage = "Successful";
return(apiResponse);
}
catch (Exception ex)
{
throw ex;
}
}