private bool CreateAttendanceResult(Staff staff, DatetimeRange dr, List <string> readers)
{
ShiftArrangeSearchCondition con1 = new ShiftArrangeSearchCondition();
con1.StaffID = staff.ID;
con1.ShiftDate = dr;
List <ShiftArrange> sas = (new ShiftArrangeBLL(AppSettings.CurrentSetting.ConnectUri)).GetItems(con1).QueryObjects;
TASheetSearchCondition con2 = new TASheetSearchCondition();
con2.StaffID = staff.ID;
List <TASheet> sheets = (new TASheetBLL(AppSettings.CurrentSetting.ConnectUri)).GetItems(con2).QueryObjects;
AttendanceLogSearchCondition con3 = new AttendanceLogSearchCondition();
con3.Staff = new List <int>();
con3.Staff.Add(staff.ID);
con3.Readers = readers;
con3.ReadDateTime = dr;
con3.ContainManualLogs = true;
List <AttendanceLog> records = (new AttendanceLogBLL(AppSettings.CurrentSetting.ConnectUri)).GetItems(con3).QueryObjects;
List <AttendanceResult> results = (new AttendanceAnalyst()).Analist(staff, sas, records, sheets, dr);
CommandResult ret = (new AttendanceResultBLL(AppSettings.CurrentSetting.ConnectUri)).Add(staff.ID, dr, results);
return(ret.Result == ResultCode.Successful);
}
public AttendanceLogSearchResultObject GetListAttendanceLog(AttendanceLogSearchCondition model)
{
model.DateFrom = DateTime.ParseExact(model.DateFrom.ToString("dd/MM/yyyy") + " " + model.TimeFrom + ":00.000", "dd/MM/yyyy HH:mm:ss.fff", CultureInfo.InvariantCulture);
model.DateTo = DateTime.ParseExact(model.DateTo.ToString("dd/MM/yyyy") + " " + model.TimeTo + ":59.999", "dd/MM/yyyy HH:mm:ss.fff", CultureInfo.InvariantCulture);
try
{
var listAttendanceLog = (from a in db.AttendanceLog.AsNoTracking()
join e in db.Employee.AsNoTracking() on a.EmployeeId equals e.EmployeeId into g
from ae in g.DefaultIfEmpty()
join d in db.Department.AsNoTracking() on ae.DepartmentId equals d.DepartmentId into gr
from aed in gr.DefaultIfEmpty()
join f in db.JobTitle.AsNoTracking() on ae.JobTitleId equals f.JobTitleId into af
from af1 in af.DefaultIfEmpty()
where (string.IsNullOrEmpty(model.EmployeeCode) || ae.Code.Contains(model.EmployeeCode)) &&
(string.IsNullOrEmpty(model.EmployeeName) || ae.Name.Contains(model.EmployeeName)) &&
(string.IsNullOrEmpty(model.CameraIPAddress) || a.CameraIPAdress.Contains(model.CameraIPAddress)) &&
(string.IsNullOrEmpty(model.DepartmentId) || ae.DepartmentId.Equals(model.DepartmentId)) &&
(model.FaceCount == null || a.FaceCount == model.FaceCount)
orderby a.Date descending
select new AttendanceLogSearchResult()
{
AttendanceLogId = a.AttendanceLogId,
EmployeeName = ae.Name,
EmployeeId = a.EmployeeId,
EmployeeCode = ae.Code,
DepartmentName = aed.Name,
JobTitleName = af1.Name,
Date = (DateTime)a.Date,
Confidence = a.Confidence,
CameraIPAddress = a.CameraIPAdress,
ImageLink = a.ImageLink,
ObjSelect = false,
FaceCount = a.FaceCount,
ImageFace = a.ImageFace,
}).AsQueryable();
listAttendanceLog = listAttendanceLog.Where(r => r.Date <= model.DateTo);
listAttendanceLog = listAttendanceLog.Where(r => r.Date >= model.DateFrom);
listAttendanceLog = listAttendanceLog.Where(r => r.Confidence >= model.ConfidenceFrom);
listAttendanceLog = listAttendanceLog.Where(r => r.Confidence <= model.ConfidenceTo);
var count = listAttendanceLog.Select(u => u.AttendanceLogId).ToList().Count;
var listResult = SQLHelpper.OrderBy(listAttendanceLog, model.OrderBy, model.OrderType).Skip((model.PageNumber - 1) * model.PageSize).Take(model.PageSize).ToList();
AttendanceLogSearchResultObject result = new AttendanceLogSearchResultObject()
{
ListResult = listResult,
TotalItem = count
};
return(result);
}
catch (Exception ex)
{
throw new ErrorException(ErrorMessage.ERR001, ex.InnerException);
}
}
public HttpResponseMessage ExportExcel(AttendanceLogSearchCondition model)
{
try
{
AttendanceLogSearchResultObject result = attendanceLogBusiness.ExportExcel(model);
return(Request.CreateResponse(HttpStatusCode.OK, result));
}
catch (Exception ex)
{
return(Request.CreateResponse(HttpStatusCode.InternalServerError, ex.Message));
}
}
public HttpResponseMessage GetListAttendanceLog(AttendanceLogSearchCondition model)
{
try
{
var listResult = attendanceLogBusiness.GetListAttendanceLog(model);
listResult.PathFile = System.Configuration.ConfigurationManager.AppSettings["UrlHostImage"] + System.Configuration.ConfigurationManager.AppSettings["StorageContainer"] + "/";
return(Request.CreateResponse(HttpStatusCode.OK, listResult));
}
catch (Exception ex)
{
return(Request.CreateResponse(HttpStatusCode.InternalServerError, ex.Message));
}
}
protected override List <object> GetDataSource()
{
List <DeviceInfo> attendanceReaders = (new DeviceInfoBLL(AppSettings.CurrentSetting.ConnectUri)).GetAttendanceReaders().QueryObjects;
if (attendanceReaders == null || attendanceReaders.Count == 0)
{
MessageBox.Show("还没有指定考勤点，请先在 \"考勤点选择\" 上指定考勤点");
return(null);
}
List <string> readers = attendanceReaders.Select(it => it.ID).ToList();
List <Staff> users = departmentTreeview1.SelectedStaff;
if (users != null && users.Count > 0)
{
List <int> staff = users.Select(item => item.ID).ToList();
AttendanceLogSearchCondition con = new AttendanceLogSearchCondition();
con.Staff = staff;
con.ContainManualLogs = true;
con.Readers = readers;
con.ReadDateTime = new DatetimeRange(ucDateTimeInterval1.StartDateTime, ucDateTimeInterval1.EndDateTime.Date.AddDays(1).AddSeconds(-1));
List <AttendanceLog> arranges = (new AttendanceLogBLL(AppSettings.CurrentSetting.ConnectUri)).GetItems(con).QueryObjects;
List <object> items = new List <object>();
foreach (Staff s in users)
{
List <AttendanceLog> rets = (from it in arranges where it.StaffID == s.ID orderby it.ReadDateTime ascending select it).ToList();
if (rets != null && rets.Count > 0)
{
rets.ForEach(it => it.StaffName = s.Name);
List <IGrouping <string, AttendanceLog> > groups = rets.GroupBy(item => item.ReadDateTime.ToString("yyyy-MM-dd")).ToList();
List <object> sas = (from g in groups select(object) g).ToList();
items.AddRange(sas);
}
}
return(items);
}
return(null);
}
protected override List <AttendanceLog> GetingItems(AttendanceDataContext attendance, SearchCondition search)
{
IQueryable <AttendanceLog> ret = attendance.GetTable <AttendanceLog>();
if (search is AttendanceLogSearchCondition)
{
AttendanceLogSearchCondition con = search as AttendanceLogSearchCondition;
if (con.ReadDateTime != null)
{
ret = ret.Where(item => item.ReadDateTime >= con.ReadDateTime.Begin && item.ReadDateTime <= con.ReadDateTime.End);
}
if (con.Staff != null && con.Staff.Count > 0)
{
ret = ret.Where(item => con.Staff.Contains(item.StaffID));
}
if (con.Readers != null && con.Readers.Count > 0)
{
ret = ret.Where(item => con.Readers.Contains(item.ReaderID) || item.IsManual == true); //人工签卡记录可能没有读卡器ID
}
}
List <AttendanceLog> items = ret.ToList();
return(items);
}
public AttendanceLogSearchResultObject ExportExcel(AttendanceLogSearchCondition model)
{
//Khởi tạo Excel
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
string pathClient = HttpContext.Current.Server.MapPath("/Template/ThongkeLuotVaoRa.xlsx");
IWorkbook workbook = application.Workbooks.Open(pathClient);
IWorksheet sheet = workbook.Worksheets[0];
//Khởi tạo dữ liệu Model
try
{
int index = 1;
AttendanceLogSearchResultObject result = GetListAttendanceLog(model);
var list = result.ListResult;
foreach (var e in list)
{
if (string.IsNullOrEmpty(e.EmployeeCode))
{
e.EmployeeCode = "Unknow";
}
if (string.IsNullOrEmpty(e.EmployeeName))
{
e.EmployeeName = "Unknow";
}
if (string.IsNullOrEmpty(e.DepartmentName))
{
e.DepartmentName = "Unknow";
}
e.DateString = e.Date.ToString("dd/MM/yyyy HH:mm:ss");
}
var listExport = (from p in list
select new
{
Index = index++,
p.EmployeeCode,
p.EmployeeName,
p.DepartmentName,
p.DateString,
p.Confidence,
p.CameraIPAddress,
}).ToList();
if (listExport.Count > 0)
{
sheet.Range[6, 2, 6 + listExport.Count, 8].Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
sheet.Range[6, 2, 6 + listExport.Count, 8].Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
sheet.Range[6, 2, 6 + listExport.Count, 8].Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
sheet.Range[6, 2, 6 + listExport.Count, 8].Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
sheet.ImportData(listExport, 7, 2, false);
}
//Xuất Excel
string pathExport = "/Template/Export/" + DateTime.Now.ToString("dd-MM-yyyy-HH-mm-ss") + "ThongkeVaoRa.xlsx";
workbook.SaveAs(HttpContext.Current.Server.MapPath(pathExport));
workbook.Close();
excelEngine.Dispose();
result.PathExport = pathExport;
return(result);
}
catch (Exception ex)
{
throw new ErrorException(ErrorMessage.ERR001, ex.InnerException);
}
}
