/// <summary>
///
/// </summary>
/// <returns></returns>
public async Task <object> GetAll()
{
var commonData = await _commonDataRepository.GetAll();
//users
var listUsers = await _accountRepository.GetAll();
return(new
{
CommonData = commonData,
Users = listUsers,
Roles = new RolesList().GetListSort()
});
}
/// <summary>
///
/// </summary>
/// <param name="filename"></param>
/// <param name="companyId"></param>
/// <returns></returns>
public async Task <string> Download(string filename, string companyId)
{
var sourcePath = Path.Combine(
Directory.GetCurrentDirectory(),
"wwwroot/ImportTemplate", filename);
var targetPath = Path.Combine(
Directory.GetCurrentDirectory(),
"wwwroot/ImportTemplateData", $"{Path.GetFileNameWithoutExtension(filename)}_{DateTime.Now.ToString("yyyyMMddHHmmss")}.{Path.GetExtension(filename)}");
File.Copy(sourcePath, targetPath, true);
FileInfo file = new FileInfo(targetPath);
using (ExcelPackage excelPackage = new ExcelPackage(file))
{
ExcelWorkbook excelWorkBook = excelPackage.Workbook;
ExcelWorksheet excelWorksheet = excelWorkBook.Worksheets["Master"];
//insert lead status for excel
int row = 2;
int column = 1;
var commonData = await _commonDataRepository.GetAll(companyId);
var leadStatus = commonData.Where(w => w.DataType == CommonDataType.Status && w.IsDelete == false);
foreach (var status in leadStatus)
{
excelWorksheet.Cells[row, column].Value = $"{status.DataKey}-{status.DataValue}";
row++;
}
//insert defintion source for excel
row = 2;
column = 7;
var definitionSources = commonData.Where(w => w.DataType == CommonDataType.Source && w.IsDelete == false);
foreach (var source in definitionSources)
{
excelWorksheet.Cells[row, column].Value = $"{source.DataKey}-{source.DataValue}";
row++;
}
//insert definition channel for excel
row = 2;
column = 9;
var definitionChannels = commonData.Where(w => w.DataType == CommonDataType.Channel && w.IsDelete == false);
foreach (var channel in definitionChannels)
{
excelWorksheet.Cells[row, column].Value = $"{channel.DataKey}-{channel.DataValue}";
row++;
}
//insert gender
row = 2;
column = 3;
var genders = commonData.Where(w => w.DataType == CommonDataType.Gender && w.IsDelete == false);
foreach (var gender in genders)
{
excelWorksheet.Cells[row, column].Value = $"{gender.DataKey}-{gender.DataValue}";
row++;
}
//insert vocative
row = 2;
column = 5;
var vocatives = commonData.Where(w => w.DataType == CommonDataType.Vocative && w.IsDelete == false);
foreach (var vocative in vocatives)
{
excelWorksheet.Cells[row, column].Value = $"{vocative.DataKey}-{vocative.DataValue}";
row++;
}
//insert Marital Status
row = 2;
column = 11;
var maritalStatus = commonData.Where(w => w.DataType == CommonDataType.MaritalStatus && w.IsDelete == false);
foreach (var status in maritalStatus)
{
excelWorksheet.Cells[row, column].Value = $"{status.DataKey}-{status.DataValue}";
row++;
}
//insert Relationship
row = 2;
column = 13;
var relationships = commonData.Where(w => w.DataType == CommonDataType.Relationship && w.IsDelete == false);
foreach (var relationship in relationships)
{
excelWorksheet.Cells[row, column].Value = $"{relationship.DataKey}-{relationship.DataValue}";
row++;
}
excelPackage.Save();
}
return(targetPath);
}
public async Task <IActionResult> Import([FromForm] ImportLead data, [FromHeader] string companyId)
{
if (ModelState.IsValid)
{
data.UserId = User.Claims.FirstOrDefault(s => s.Type == "sub").Value;
data.CompanyId = companyId;
data.UserName = User.Claims.FirstOrDefault(s => s.Type == "userName").Value;
List <CreateLead> listLeads = new List <CreateLead>();
using (var memoryStream = new MemoryStream())
{
await data.File.CopyToAsync(memoryStream).ConfigureAwait(false);
using (var package = new ExcelPackage(memoryStream))
{
var worksheet = package.Workbook.Worksheets["Data"];
int totalRows = worksheet.Dimension.Rows;
int totalCols = worksheet.Dimension.Columns;
var commonData = await _commonDataRepository.GetAll(data.CompanyId);
var leadStatus = commonData.Where(w => w.DataType == CommonDataType.Status && w.IsDelete == false);
var definitionSources = commonData.Where(w => w.DataType == CommonDataType.Source && w.IsDelete == false);
var definitionChannels = commonData.Where(w => w.DataType == CommonDataType.Channel && w.IsDelete == false);
var genders = commonData.Where(w => w.DataType == CommonDataType.Gender && w.IsDelete == false);
var vocatives = commonData.Where(w => w.DataType == CommonDataType.Vocative && w.IsDelete == false);
var maritalStatus = commonData.Where(w => w.DataType == CommonDataType.MaritalStatus && w.IsDelete == false);
var relationships = commonData.Where(w => w.DataType == CommonDataType.Relationship && w.IsDelete == false);
for (int i = 2; i <= totalRows; i++)
{
var status = worksheet.Cells[i, 6].Value?.ToString();
var source = worksheet.Cells[i, 9].Value?.ToString();
var channel = worksheet.Cells[i, 10].Value?.ToString();
var gender = worksheet.Cells[i, 4].Value?.ToString();
var vocative = worksheet.Cells[i, 5].Value?.ToString();
var marital = worksheet.Cells[i, 17].Value?.ToString();
var relationship = worksheet.Cells[i, 18].Value?.ToString();
var haveStatus = string.IsNullOrEmpty(status) ? "" : leadStatus.Where(a => a.DataKey == status.Split('-').FirstOrDefault()).Select(s => s.Id).FirstOrDefault();
var haveSource = string.IsNullOrEmpty(source) ? "" : definitionSources.Where(a => a.DataKey == source.Split('-').FirstOrDefault()).Select(s => s.Id).FirstOrDefault();
var haveChannel = string.IsNullOrEmpty(channel) ? "" : definitionChannels.Where(a => a.DataKey == channel.Split('-').FirstOrDefault()).Select(s => s.Id).FirstOrDefault();
var haveGender = string.IsNullOrEmpty(gender) ? "" : genders.Where(a => a.DataKey == gender.Split('-').FirstOrDefault()).Select(s => s.Id).FirstOrDefault();
var haveVocative = string.IsNullOrEmpty(vocative) ? "" : vocatives.Where(a => a.DataKey == vocative.Split('-').FirstOrDefault()).Select(s => s.Id).FirstOrDefault();
var haveMaritalStatus = string.IsNullOrEmpty(marital) ? "" : maritalStatus.Where(a => a.DataKey == marital.Split('-').FirstOrDefault()).Select(s => s.Id).FirstOrDefault();
var haveRelationship = string.IsNullOrEmpty(relationship) ? "" : relationships.Where(a => a.DataKey == relationship.Split('-').FirstOrDefault()).Select(s => s.Id).FirstOrDefault();
if (haveStatus != null && haveSource != null & haveChannel != null && haveGender != null && haveVocative != null && haveMaritalStatus != null && haveRelationship != null)
{
listLeads.Add(new CreateLead
{
FullName = worksheet.Cells[i, 1].Value?.ToString(),
Phone = worksheet.Cells[i, 2].Value?.ToString(),
Email = worksheet.Cells[i, 3].Value?.ToString(),
Gender = haveGender,
Vocative = haveVocative,
Status = haveStatus,
Interest = worksheet.Cells[i, 7].Value?.ToString().Split(',').ToList(),
Note = worksheet.Cells[i, 8].Value?.ToString(),
Source = haveSource,
Channel = haveChannel,
Campaign = worksheet.Cells[i, 11].Value?.ToString(),
Address = worksheet.Cells[i, 12].Value?.ToString(),
IdentityCard = worksheet.Cells[i, 13].Value?.ToString(),
DateOfIssue = (DateTime.TryParse(worksheet.Cells[i, 14].Value?.ToString(), out DateTime dateValue)) ? DateTime.Parse(worksheet.Cells[i, 14].Value.ToString()) : (DateTime?)null,
PlaceOfIssue = worksheet.Cells[i, 15].Value?.ToString(),
CreatedBy = data.UserName,
CompanyId = data.CompanyId,
StaffInCharge = data.StaffInCharge,
SupportStaff = data.SupportStaff,
SocialId = worksheet.Cells[i, 16].Value?.ToString(),
MaritalStatus = haveMaritalStatus,
Relationship = haveRelationship,
Birthday = (DateTime.TryParse(worksheet.Cells[i, 19].Value?.ToString(), out DateTime birthdayValue)) ? DateTime.Parse(worksheet.Cells[i, 19].Value.ToString()) : (DateTime?)null,
});
/// <summary>
///
/// </summary>
/// <param name="query"></param>
/// <param name="language"></param>
/// <param name="userId"></param>
/// <returns></returns>
public async Task <string> Export(ElasticSearchQuery query, string language, string userId)
{
query.From = 0;
query.Size = QueryConstant.MaxLimit;
var listLeads = await GetByQuery(query);
var leads = listLeads.Data;
var users = await _accountRepository.GetAll();
var commonData = await _commonDataRepository.GetAll();
var leadStatus = commonData.Where(w => w.DataType == CommonDataType.Status);
var definitionSources = commonData.Where(w => w.DataType == CommonDataType.Source);
var definitionChannels = commonData.Where(w => w.DataType == CommonDataType.Channel);
var listVocative = commonData.Where(w => w.DataType == CommonDataType.Vocative);
var listMaritalStatus = commonData.Where(w => w.DataType == CommonDataType.MaritalStatus);
var listRelationship = commonData.Where(w => w.DataType == CommonDataType.Relationship);
var listGender = commonData.Where(w => w.DataType == CommonDataType.Gender);
string excelName = $"lead_export_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx";
var path = Path.Combine(
Directory.GetCurrentDirectory(),
"wwwroot/Export", excelName);
FileInfo newFile = new FileInfo(path);
ExcelPackage excel = new ExcelPackage();
var workSheet = excel.Workbook.Worksheets.Add("Lead");
workSheet.TabColor = Color.Black;
workSheet.DefaultRowHeight = 12;
//Header of table
if (language == "vi")
{
workSheet.Row(1).Height = 20;
workSheet.Row(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
workSheet.Row(1).Style.Font.Bold = true;
workSheet.Cells[1, 1].Value = "Họ tên";
workSheet.Cells[1, 2].Value = "Điện thoại";
workSheet.Cells[1, 3].Value = "Email";
workSheet.Cells[1, 4].Value = "Social Id";
workSheet.Cells[1, 5].Value = "Người phụ trách";
workSheet.Cells[1, 6].Value = "Người hỗ trợ";
workSheet.Cells[1, 7].Value = "Mối quan hệ";
workSheet.Cells[1, 8].Value = "Xưng hô";
workSheet.Cells[1, 9].Value = "Giới tính";
workSheet.Cells[1, 10].Value = "Ngày sinh";
workSheet.Cells[1, 11].Value = "Tình trạng hôn nhân";
workSheet.Cells[1, 12].Value = "Trạng thái";
workSheet.Cells[1, 13].Value = "Sản phẩm quan tâm";
workSheet.Cells[1, 14].Value = "Ghi chú";
workSheet.Cells[1, 15].Value = "Nguồn";
workSheet.Cells[1, 16].Value = "Kênh";
workSheet.Cells[1, 17].Value = "Chiến dịch";
workSheet.Cells[1, 18].Value = "Địa chỉ";
workSheet.Cells[1, 19].Value = "Số CMND";
workSheet.Cells[1, 20].Value = "Ngày cấp";
workSheet.Cells[1, 21].Value = "Nơi cấp";
}
else
{
workSheet.Row(1).Height = 20;
workSheet.Row(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
workSheet.Row(1).Style.Font.Bold = true;
workSheet.Cells[1, 1].Value = "Fullname";
workSheet.Cells[1, 2].Value = "Phone";
workSheet.Cells[1, 3].Value = "Email";
workSheet.Cells[1, 4].Value = "Social Id";
workSheet.Cells[1, 5].Value = "Staff in charge";
workSheet.Cells[1, 6].Value = "Support";
workSheet.Cells[1, 7].Value = "Relationship";
workSheet.Cells[1, 8].Value = "Title";
workSheet.Cells[1, 9].Value = "Gender";
workSheet.Cells[1, 10].Value = "Birthday";
workSheet.Cells[1, 11].Value = "Marriage";
workSheet.Cells[1, 12].Value = "Lead care status";
workSheet.Cells[1, 13].Value = "Product interesting";
workSheet.Cells[1, 14].Value = "Note";
workSheet.Cells[1, 15].Value = "Source";
workSheet.Cells[1, 16].Value = "Source channel";
workSheet.Cells[1, 17].Value = "Campaign";
workSheet.Cells[1, 18].Value = "Address";
workSheet.Cells[1, 19].Value = "Identify";
workSheet.Cells[1, 20].Value = "Date of issue";
workSheet.Cells[1, 21].Value = "Place of issue";
}
//Body of table
int recordIndex = 2;
foreach (var lead in leads)
{
var status = leadStatus.FirstOrDefault(f => f.Id == lead.Status);
var source = definitionSources.FirstOrDefault(f => f.Id == lead.Source);
var channel = definitionChannels.FirstOrDefault(f => f.Id == lead.Channel);
var staffInCharge = users.FirstOrDefault(f => f.Id == lead.StaffInCharge);
var supportStaff = string.Join(", ", users.Where(w => lead.SupportStaff.Contains(w.Id)).Distinct().Select(s => s.FirstName + " " + s.LastName).ToList());
var vocative = listVocative.FirstOrDefault(f => f.Id == lead.Vocative);
var maritalStatus = listMaritalStatus.FirstOrDefault(f => f.Id == lead.MaritalStatus);
var relationship = listRelationship.FirstOrDefault(f => f.Id == lead.Relationship);
var gender = listGender.FirstOrDefault(f => f.Id == lead.Gender);
workSheet.Cells[recordIndex, 1].Value = lead.FullName;
workSheet.Cells[recordIndex, 2].Value = lead.Phone;
workSheet.Cells[recordIndex, 3].Value = lead.Email;
workSheet.Cells[recordIndex, 4].Value = lead.SocialId;
workSheet.Cells[recordIndex, 5].Value = staffInCharge != null ? $"{staffInCharge.FirstName} {staffInCharge.LastName}" : string.Empty;
workSheet.Cells[recordIndex, 6].Value = supportStaff;
workSheet.Cells[recordIndex, 7].Value = relationship != null ? relationship.DataValue : string.Empty;
workSheet.Cells[recordIndex, 8].Value = vocative != null ? vocative.DataValue : string.Empty;
workSheet.Cells[recordIndex, 9].Value = gender != null ? gender.DataValue : string.Empty;
workSheet.Cells[recordIndex, 10].Value = lead.Birthday != null?lead.Birthday.Value.ToString("MM/dd/yyyy") : "";
workSheet.Cells[recordIndex, 11].Value = maritalStatus != null ? maritalStatus.DataValue : string.Empty;
workSheet.Cells[recordIndex, 12].Value = status != null ? status.DataValue : string.Empty;
workSheet.Cells[recordIndex, 13].Value = lead.Interest;
workSheet.Cells[recordIndex, 14].Value = lead.Note;
workSheet.Cells[recordIndex, 15].Value = source != null ? source.DataValue : string.Empty;
workSheet.Cells[recordIndex, 16].Value = channel != null ? channel.DataValue : string.Empty;
workSheet.Cells[recordIndex, 17].Value = lead.Campaign;
workSheet.Cells[recordIndex, 18].Value = lead.Address;
workSheet.Cells[recordIndex, 19].Value = lead.IdentityCard;
workSheet.Cells[recordIndex, 20].Value = lead.DateOfIssue != null?lead.DateOfIssue.Value.ToString("MM/dd/yyyy") : "";
workSheet.Cells[recordIndex, 21].Value = lead.PlaceOfIssue;
recordIndex++;
}
//auto fit
//for (int i = 1; i <= 24; i++)
//{
// workSheet.Column(i).AutoFit();
//}
//color header
Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#1e88e5");
workSheet.Cells["A1:U1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
workSheet.Cells["A1:U1"].Style.Fill.BackgroundColor.SetColor(colFromHex);
workSheet.Cells["A1:U1"].Style.Font.Color.SetColor(Color.White);
//border
string modelRange = "A1:U" + (--recordIndex).ToString();
var modelTable = workSheet.Cells[modelRange];
modelTable.Style.Border.Top.Style = ExcelBorderStyle.Thin;
modelTable.Style.Border.Left.Style = ExcelBorderStyle.Thin;
modelTable.Style.Border.Right.Style = ExcelBorderStyle.Thin;
modelTable.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
excel.SaveAs(newFile);
return(excelName);
}