private async Task ExecuteLoadHolidayDataCommand()
{
IsBusy = true;
try
{
if (IsNetworkConnected)
{
var items = await ServiceHandler.ProcessRequestCollectionAsync <HolidayDetails>(Constants.HolidayDetails);
foreach (var item in items)
{
HolidayDetail.Add(item);
} //Server Call
IsBusy = false;
}
else
{
HolidayDetail = App.DataBase.GetItems <HolidayDetails>();
//App.DataBase.GetItems<HolidayDetails>(); //From Local DB
IsBusy = false;
}
}
catch (Exception ex)
{
Console.WriteLine("An Exception Occured During getting the Record {0}", ex.Message);
}
}
public string HolidayDataConvert2Html(HolidayDetail holiday)
{
StringBuilder result = new StringBuilder();
string name = holiday.ChineseName;
string contentRespect = string.Format(_contentRespect, name);
//数据结构转化为HTML表格
//数据结构转化为HTML表格
DataTable detail = JsonConvert.DeserializeObject <DataTable>(
JsonConvert.SerializeObject(new List <object> {
new {
name = holiday.ChineseName,
begin = holiday.HolidayStartDate.ToString("yyyy-MM-dd"),
end = holiday.HolidayEndDate.ToString("yyyy-MM-dd"),
before = holiday.BeforePaidLeaveRemainingHours,
current = holiday.CurrentPaidLeaveTotalHours,
total = holiday.PaidLeaveRemainingHours,
available = holiday.CurrentAvailableRemainingHours
}
}));
string data = HolidayTableConvertHtml(detail);
result.Append(contentRespect).Append(newLine).Append(newLine)
.Append(_holidayContentComment).Append(newLine).Append(newLine)
.Append(data).Append(newLine).Append(_holidayTagComment).Append(newLine)
.Append(_mailSenderSignature);
return(result.ToString());
}
public Holiday ConvertToDataModel(HolidayModel model)
{
if (model == null)
{
return(null);
}
var dataModel = new Holiday();
dataModel.Id = model.Id;
dataModel.Name = model.Name;
dataModel.StartDate = model.StartDate;
dataModel.EndDate = model.EndDate;
dataModel.HolidayDetails = new List <HolidayDetail>();
if (model.ToClient != null)
{
dataModel.HolidayDetails.Add(new HolidayDetail()
{
ToClient = model.ToClient
});
}
if (model.ToPlace != null)
{
foreach (var place in model.ToPlace)
{
var data = new HolidayDetail();
data.ToPlace = place;
dataModel.HolidayDetails.Add(data);
}
}
if (model.ToDepartment != null)
{
foreach (var department in model.ToDepartment)
{
var data = new HolidayDetail();
data.ToDepartment = department;
dataModel.HolidayDetails.Add(data);
}
}
if (model.ToEmployee != null)
{
foreach (var employee in model.ToEmployee)
{
var data = new HolidayDetail();
data.ToEmployee = employee;
dataModel.HolidayDetails.Add(data);
}
}
return(dataModel);
}
/// <summary>
/// Merupakan fungsi yang untuk menambahkan Holiday Details dari sebuah data holiday
/// </summary>
/// <param name="holiday">sebuah object class holiday yang merupakan data holiday yang akan ditambahkan holiday details nya.</param>
/// <returns>holiday yang sudah di tambahkan holiday detailsnya</returns>
public Holiday SetHolidayDetailList(Holiday holiday)
{
DateTime startDate = holiday.StartDate;
var username = User.Identity.Name;
DateTime now = DateTime.Now;
do
{
HolidayDetail dataDetail = new HolidayDetail();
dataDetail.HolidayDate = startDate;
dataDetail.Created = dataDetail.LastModified = now;
dataDetail.CreatedBy = dataDetail.LastModifiedBy = username;
holiday.HolidayDetails.Add(dataDetail);
startDate = startDate.AddDays(1);
} while (startDate.Date <= holiday.EndDate.Date);
return(holiday);
}
/// <summary>
/// 读取当前年假，上一年年假结余的excel信息
/// </summary>
public HolidayResult ReadHolidayFile()
{
var sheet = this._workBook.GetSheetAt(0);
if (sheet == null)
{
return(new HolidayResult()
{
hasError = true,
ErrorMsg = "当前文件无工作表数据"
});
}
int titleRowIdx = 0;
for (int i = 0; i < sheet.LastRowNum; i++)
{
if (i > 10)
{
return(new HolidayResult()
{
hasError = true,
ErrorMsg = "当前文件数据格式不正确，未在规定范围内找到起始数据列'序号'"
});
}
var flag = sheet.GetRow(i).GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString().Trim();
if (flag.Equals("序号"))
{
titleRowIdx = i;
break;
}
}
List <HolidayDetail> result = new List <HolidayDetail>();
if (titleRowIdx > 0)
{
IRow title = sheet.GetRow(titleRowIdx);
//获取最后剩余年假的列
int totalRemainingIdx = 0;
for (int row = 0; row < title.LastCellNum + 1; row++)
{
if (title.GetCell(row, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString().Contains("剩余年假"))
{
totalRemainingIdx = row;
break;
}
}
if (totalRemainingIdx == 0)
{
return(new HolidayResult()
{
hasError = true,
ErrorMsg = "当前文件数据格式不正确，未在规定范围内找到数据列'剩余年假'"
});
}
//读取特定的列数据，包括姓名，年假区间，上期/本期剩余年假时间，总共剩余年假
int nameCol = 0, beforeRegionCol = 0, curRegionCol = 0, usedCol = 0;
foreach (var item in title.Cells)
{
string val = item.ToString().Trim();
if (val.Equals("姓名"))
{
nameCol = item.ColumnIndex;
}
else if (val.Equals("年假区间") && item.ColumnIndex < 4)
{
beforeRegionCol = item.ColumnIndex;
}
else if (val.Equals("年假区间") && item.ColumnIndex > 4)
{
curRegionCol = item.ColumnIndex;
}
else if (val.StartsWith("已使用年假"))
{
usedCol = item.ColumnIndex;
}
}
if (nameCol == 0 || beforeRegionCol == 0 || curRegionCol == 0 || usedCol == 0)
{
return(new HolidayResult()
{
hasError = true,
ErrorMsg = "当前文件数据格式不正确，请确保标题行含有'姓名'，'年假区间'，'剩余年假'列"
});
}
//读取所有年假信息，默认title为两行数据
int cols = title.LastCellNum;
var users = new UserService().GetAllUsers().ToList();
for (int row = titleRowIdx + 2; row < sheet.LastRowNum + 1; row++)
{
string name = string.Empty;
try
{
IRow sRow = sheet.GetRow(row);
ICell sCell = sRow.GetCell(nameCol);
if (sCell != null)
{
name = sRow.GetCell(nameCol, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString().Trim();
if (string.IsNullOrWhiteSpace(name))
{
break;
}
if (users.Where(r => r.ChineseName == name).Count() == 0)
{
throw new InvalidDataException("user chinese name not match the special data in database");
}
}
//读取所有基本信息详情
var remainingHolidayCol = sRow.GetCell(totalRemainingIdx, MissingCellPolicy.CREATE_NULL_AS_BLANK);
var usedHolidayCol = sRow.GetCell(usedCol, MissingCellPolicy.CREATE_NULL_AS_BLANK);
double remaining = remainingHolidayCol.CellType == CellType.Formula ?
remainingHolidayCol.NumericCellValue : double.Parse(remainingHolidayCol.ToString().Trim());
double used = usedHolidayCol.CellType == CellType.Formula ?
usedHolidayCol.NumericCellValue : double.Parse(usedHolidayCol.ToString().Trim());
var holiday = new HolidayDetail()
{
ChineseName = name,
Email = users.Where(r => r.ChineseName == name).Select(r => r.EmailAddress).First(),
HolidayStartDate = DateTime.Parse(sRow.GetCell(curRegionCol).ToString().Trim()),
HolidayEndDate = DateTime.Parse(sRow.GetCell(curRegionCol + 1).ToString().Trim()),
BeforePaidLeaveRemainingHours = double.Parse(sRow.GetCell(curRegionCol + 2, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString().Trim()),
CurrentPaidLeaveTotalHours = double.Parse(sRow.GetCell(curRegionCol + 3, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString().Trim()),
CurrentUsedPaidLeaveHours = used,
PaidLeaveRemainingHours = remaining
};
if (holiday.BeforePaidLeaveRemainingHours > 0)
{
holiday.PreviousStartDate = DateTime.Parse(sRow.GetCell(beforeRegionCol).ToString().Trim());
holiday.PreviousEndDate = DateTime.Parse(sRow.GetCell(beforeRegionCol + 1).ToString().Trim());
}
result.Add(holiday);
}
catch (Exception ex)
{
return(new HolidayResult()
{
hasError = true,
ErrorMsg = $"数据记录有误，定位：姓名=>{name}, 错误信息：{ex.Message}",
});
}
}
}
if (result.Count > 0)
{
return(new HolidayResult()
{
hasError = false,
ErrorMsg = "",
result = result
});
}
return(new HolidayResult()
{
hasError = true,
ErrorMsg = "无法读取相关数据"
});
}
