public async Task <FileResult> ExportTemplate([FromBody] TicketIssueLevel_TicketIssueLevelFilterDTO TicketIssueLevel_TicketIssueLevelFilterDTO)
{
if (!ModelState.IsValid)
{
throw new BindException(ModelState);
}
MemoryStream memoryStream = new MemoryStream();
using (ExcelPackage excel = new ExcelPackage(memoryStream))
{
#region TicketIssueLevel
var TicketIssueLevelHeaders = new List <string[]>()
{
new string[] {
"Id",
"Name",
"OrderNumber",
"TicketGroupId",
"StatusId",
"SLA",
"Used",
}
};
List <object[]> TicketIssueLevelData = new List <object[]>();
excel.GenerateWorksheet("TicketIssueLevel", TicketIssueLevelHeaders, TicketIssueLevelData);
#endregion
#region Status
var StatusFilter = new StatusFilter();
StatusFilter.Selects = StatusSelect.ALL;
StatusFilter.OrderBy = StatusOrder.Id;
StatusFilter.OrderType = OrderType.ASC;
StatusFilter.Skip = 0;
StatusFilter.Take = int.MaxValue;
List <Status> Statuses = await StatusService.List(StatusFilter);
var StatusHeaders = new List <string[]>()
{
new string[] {
"Id",
"Code",
"Name",
}
};
List <object[]> StatusData = new List <object[]>();
for (int i = 0; i < Statuses.Count; i++)
{
var Status = Statuses[i];
StatusData.Add(new Object[]
{
Status.Id,
Status.Code,
Status.Name,
});
}
excel.GenerateWorksheet("Status", StatusHeaders, StatusData);
#endregion
#region TicketGroup
var TicketGroupFilter = new TicketGroupFilter();
TicketGroupFilter.Selects = TicketGroupSelect.ALL;
TicketGroupFilter.OrderBy = TicketGroupOrder.Id;
TicketGroupFilter.OrderType = OrderType.ASC;
TicketGroupFilter.Skip = 0;
TicketGroupFilter.Take = int.MaxValue;
List <TicketGroup> TicketGroups = await TicketGroupService.List(TicketGroupFilter);
var TicketGroupHeaders = new List <string[]>()
{
new string[] {
"Id",
"Name",
"OrderNumber",
"StatusId",
"TicketTypeId",
"Used",
}
};
List <object[]> TicketGroupData = new List <object[]>();
for (int i = 0; i < TicketGroups.Count; i++)
{
var TicketGroup = TicketGroups[i];
TicketGroupData.Add(new Object[]
{
TicketGroup.Id,
TicketGroup.Name,
TicketGroup.OrderNumber,
TicketGroup.StatusId,
TicketGroup.TicketTypeId,
TicketGroup.Used,
});
}
excel.GenerateWorksheet("TicketGroup", TicketGroupHeaders, TicketGroupData);
#endregion
excel.Save();
}
return(File(memoryStream.ToArray(), "application/octet-stream", "TicketIssueLevel.xlsx"));
}
public async Task <ActionResult> Import(IFormFile file)
{
if (!ModelState.IsValid)
{
throw new BindException(ModelState);
}
StatusFilter StatusFilter = new StatusFilter
{
Skip = 0,
Take = int.MaxValue,
Selects = StatusSelect.ALL
};
List <Status> Statuses = await StatusService.List(StatusFilter);
TicketGroupFilter TicketGroupFilter = new TicketGroupFilter
{
Skip = 0,
Take = int.MaxValue,
Selects = TicketGroupSelect.ALL
};
List <TicketGroup> TicketGroups = await TicketGroupService.List(TicketGroupFilter);
List <TicketIssueLevel> TicketIssueLevels = new List <TicketIssueLevel>();
using (ExcelPackage excelPackage = new ExcelPackage(file.OpenReadStream()))
{
ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.FirstOrDefault();
if (worksheet == null)
{
return(Ok(TicketIssueLevels));
}
int StartColumn = 1;
int StartRow = 1;
int IdColumn = 0 + StartColumn;
int NameColumn = 1 + StartColumn;
int OrderNumberColumn = 2 + StartColumn;
int TicketGroupIdColumn = 3 + StartColumn;
int StatusIdColumn = 4 + StartColumn;
int SLAColumn = 5 + StartColumn;
int UsedColumn = 9 + StartColumn;
for (int i = StartRow; i <= worksheet.Dimension.End.Row; i++)
{
if (string.IsNullOrEmpty(worksheet.Cells[i + StartRow, StartColumn].Value?.ToString()))
{
break;
}
string IdValue = worksheet.Cells[i + StartRow, IdColumn].Value?.ToString();
string NameValue = worksheet.Cells[i + StartRow, NameColumn].Value?.ToString();
string OrderNumberValue = worksheet.Cells[i + StartRow, OrderNumberColumn].Value?.ToString();
string TicketGroupIdValue = worksheet.Cells[i + StartRow, TicketGroupIdColumn].Value?.ToString();
string StatusIdValue = worksheet.Cells[i + StartRow, StatusIdColumn].Value?.ToString();
string SLAValue = worksheet.Cells[i + StartRow, SLAColumn].Value?.ToString();
string UsedValue = worksheet.Cells[i + StartRow, UsedColumn].Value?.ToString();
TicketIssueLevel TicketIssueLevel = new TicketIssueLevel();
TicketIssueLevel.Name = NameValue;
TicketIssueLevel.OrderNumber = long.TryParse(OrderNumberValue, out long OrderNumber) ? OrderNumber : 0;
TicketIssueLevel.SLA = long.TryParse(SLAValue, out long SLA) ? SLA : 0;
Status Status = Statuses.Where(x => x.Id.ToString() == StatusIdValue).FirstOrDefault();
TicketIssueLevel.StatusId = Status == null ? 0 : Status.Id;
TicketIssueLevel.Status = Status;
TicketGroup TicketGroup = TicketGroups.Where(x => x.Id.ToString() == TicketGroupIdValue).FirstOrDefault();
TicketIssueLevel.TicketGroupId = TicketGroup == null ? 0 : TicketGroup.Id;
TicketIssueLevel.TicketGroup = TicketGroup;
TicketIssueLevels.Add(TicketIssueLevel);
}
}
TicketIssueLevels = await TicketIssueLevelService.Import(TicketIssueLevels);
if (TicketIssueLevels.All(x => x.IsValidated))
{
return(Ok(true));
}
else
{
List <string> Errors = new List <string>();
for (int i = 0; i < TicketIssueLevels.Count; i++)
{
TicketIssueLevel TicketIssueLevel = TicketIssueLevels[i];
if (!TicketIssueLevel.IsValidated)
{
string Error = $"Dòng {i + 2} có lỗi:";
if (TicketIssueLevel.Errors.ContainsKey(nameof(TicketIssueLevel.Id)))
{
Error += TicketIssueLevel.Errors[nameof(TicketIssueLevel.Id)];
}
if (TicketIssueLevel.Errors.ContainsKey(nameof(TicketIssueLevel.Name)))
{
Error += TicketIssueLevel.Errors[nameof(TicketIssueLevel.Name)];
}
if (TicketIssueLevel.Errors.ContainsKey(nameof(TicketIssueLevel.OrderNumber)))
{
Error += TicketIssueLevel.Errors[nameof(TicketIssueLevel.OrderNumber)];
}
if (TicketIssueLevel.Errors.ContainsKey(nameof(TicketIssueLevel.TicketGroupId)))
{
Error += TicketIssueLevel.Errors[nameof(TicketIssueLevel.TicketGroupId)];
}
if (TicketIssueLevel.Errors.ContainsKey(nameof(TicketIssueLevel.StatusId)))
{
Error += TicketIssueLevel.Errors[nameof(TicketIssueLevel.StatusId)];
}
if (TicketIssueLevel.Errors.ContainsKey(nameof(TicketIssueLevel.SLA)))
{
Error += TicketIssueLevel.Errors[nameof(TicketIssueLevel.SLA)];
}
if (TicketIssueLevel.Errors.ContainsKey(nameof(TicketIssueLevel.Used)))
{
Error += TicketIssueLevel.Errors[nameof(TicketIssueLevel.Used)];
}
Errors.Add(Error);
}
}
return(BadRequest(Errors));
}
}
