private async Task <List <CourierSql> > ReadXlsxFile(MemoryStream stream, int createBy)
{
var workBook = WorkbookFactory.Create(stream);
var sheet = workBook.GetSheetAt(0);
var rows = sheet.GetRowEnumerator();
var hasData = rows.MoveNext();
var param = new DynamicParameters();
var pars = new List <DynamicParameters>();
int count = 0;
var hosos = new List <CourierSql>();
for (int i = 1; i < sheet.PhysicalNumberOfRows; i++)
{
try
{
var row = sheet.GetRow(i);
if (row != null)
{
if (row.Cells.Count > 1)
{
bool isNullRow = row.Cells.Count < 3 ? true : false;
}
var hoso = new CourierSql()
{
CustomerName = row.Cells[0] != null ? row.Cells[0].ToString() : "",
Phone = row.Cells[1] != null ? row.Cells[1].ToString() : "",
Cmnd = row.Cells[2] != null ? row.Cells[2].ToString() : "",
LastNote = row.Cells[4] != null ? row.Cells[4].ToString() : "",
ProvinceId = row.Cells[5] != null?Convert.ToInt32(row.Cells[5].ToString()) : 0,
DistrictId = row.Cells[6] != null?Convert.ToInt32(row.Cells[6].ToString()) : 0,
SaleCode = row.Cells[7] != null ? row.Cells[7].ToString().Trim().ToLower() : string.Empty,
Status = (int)ProfileStatus.New,
CreatedBy = createBy
};
var strAssignee = row.Cells[3] != null ? row.Cells[3].ToString() : "";
var assigneeIdsStr = string.IsNullOrWhiteSpace(strAssignee) ? new List <string>() : strAssignee.Split(',').ToList();
var assigneeIds = (assigneeIdsStr != null && assigneeIdsStr.Any()) ? assigneeIdsStr.Select(s => Convert.ToInt32(s)).ToList() : new List <int>();
hoso.AssigneeIds = assigneeIds;
hoso.AssignId = assigneeIds.FirstOrDefault();
hoso.GroupId = await _rpGroup.GetGroupIdByLeaderIdAsync(hoso.AssignId);
hosos.Add(hoso);
count++;
}
}
catch
{
return(hosos);
}
}
return(hosos);
}
public async Task <BaseResponse <int> > CreateAsync(CourierSql model, int groupId = 0)
{
try
{
using (var con = GetConnection())
{
var param = GetParams(model, ignoreKey: new string[] {
nameof(model.CreatedTime),
nameof(model.UpdatedTime),
nameof(model.UpdatedBy),
nameof(model.AssigneeIds)
}, outputParam: "Id");
await con.ExecuteAsync("sp_InsertHosoCourrier", param, commandType : CommandType.StoredProcedure);
return(BaseResponse <int> .Create(param.Get <int>("Id")));
}
}
catch (Exception e)
{
return(BaseResponse <int> .Create(0, GetException(e)));
}
}
public async Task <BaseResponse <bool> > UpdateAsync(CourierSql model)
{
try
{
using (var con = GetConnection())
{
var param = GetParams(model, ignoreKey: new string[] {
nameof(model.CreatedTime),
nameof(model.UpdatedTime),
nameof(model.AssigneeIds),
nameof(model.CreatedBy)
});
await con.ExecuteAsync("sp_UpdateHosoCourier", param, commandType : CommandType.StoredProcedure);
return(BaseResponse <bool> .Create(true));
}
}
catch (Exception e)
{
return(BaseResponse <bool> .Create(false, GetException(e)));
}
}