public List <Attendance> getUnExistItemList(List <Attendance> listID, int headerID)
{
if (listID.Count == 0)
{
return(new List <Attendance>());
}
else
{
using (var db = new DIEMDANHAPIEntities())
{
var listIDString = $"{listID[0].MaNhanVien}";
for (int index = 1; index < listID.Count; index++)
{
listIDString += $",{listID[index].MaNhanVien}";
}
string sqlQuery = "select MaNV from DiemDanh_NangSuatLaoDong " +
"where HeaderID = @headerID and MaNV in (@listMaNV)";
try
{
List <String> IDs = db.Database.SqlQuery <String>(sqlQuery, new SqlParameter("headerID", headerID), new SqlParameter("listMaNV", listIDString)).ToList();
var filterIDList = listID.Where(x => !IDs.Contains(x.MaNhanVien)).ToList();
return(filterIDList);
}
catch (Exception ex)
{
throw ex;
}
}
}
}
//
public void Insert(List <DiemDanh_NangSuatLaoDong> listAttendance)
{
using (var db = new DIEMDANHAPIEntities())
{
try
{
db.DiemDanh_NangSuatLaoDong.AddRange(listAttendance);
db.SaveChanges();
}
catch (Exception ex)
{
throw ex;
}
}
}
public void Insert(int headerID)
{
using (var db = new DIEMDANHAPIEntities())
{
Header_DiemDanh_NangSuat_LaoDong_Detail header_detail = new Header_DiemDanh_NangSuat_LaoDong_Detail();
header_detail.HeaderID = headerID;
try
{
db.Header_DiemDanh_NangSuat_LaoDong_Detail.Add(header_detail);
db.SaveChanges();
}
catch (Exception ex)
{
throw ex;
}
}
}
//
public int getHeader(DateTime date, int session, DateTime timefetching)
{
using (var db = new DIEMDANHAPIEntities())
{
try
{
string sqlQuery = @"select headerId from Header_DiemDanh_NangSuat_LaoDong where (NgayDiemDanh = @date and Ca = @session and FetchDataTime = @fetchDataTime)";
var headerID = db.Database.SqlQuery <int>(sqlQuery, new SqlParameter("date", date), new SqlParameter("session", session), new SqlParameter("fetchDataTime", timefetching)).FirstOrDefault();
//
return(headerID);
}
catch (Exception ex)
{
throw ex;
}
}
}
public void Insert(Result data)
{
Header_DiemDanh_NangSuat_LaoDong header = new Header_DiemDanh_NangSuat_LaoDong();
header.NgayDiemDanh = data.dateFetching;
header.Ca = data.Session;
header.Status = data.success;
header.Message = data.message;
header.VERSION = data.VERSION;
header.FetchDataTime = data.actualTimeFetching;
using (var db = new DIEMDANHAPIEntities())
{
try
{
db.Header_DiemDanh_NangSuat_LaoDong.Add(header);
db.SaveChanges();
}
catch (Exception ex)
{
throw ex;
}
}
}
// get the first successful fetch API
public int getFirstSuccessfullyFetch(DateTime date, int session)
{
//string sqlPhongBanTieuChi = "select a.MaPhongBan,a.MaTieuChi,b.TenTieuChi from PhongBan_TieuChi a left join TieuChi b on a.MaTieuChi = b.MaTieuChi\n" +
// "where MaPhongBan = @maphongban and Thang = @thang and Nam = @nam";
//list = db.Database.SqlQuery<TieuChiABC>(sqlPhongBanTieuChi, new SqlParameter("maphongban", departmentID),
// new SqlParameter("thang", month),
// new SqlParameter("nam", year)).ToList<TieuChiABC>();
using (var db = new DIEMDANHAPIEntities())
{
string sqlQuery = @"select headerId from Header_DiemDanh_NangSuat_LaoDong
where FetchDataTime = (Select Min(FetchDataTime) from Header_DiemDanh_NangSuat_LaoDong where NgayDiemDanh = @date and Ca = @session and (Status = 1 or isCreatedManually =1)) ";
try
{
var minHeaderIDNull = db.Database.SqlQuery <int?>(sqlQuery, new SqlParameter("date", date), new SqlParameter("session", session)).FirstOrDefault();
int minHeaderIDResult = minHeaderIDNull.HasValue ? minHeaderIDNull.Value : -1;
return(minHeaderIDResult);
}
catch (Exception ex)
{
throw ex;
}
}
}
public async Task FetchAndUpdateDatabase(DateTime time)
{
using (DIEMDANHAPIEntities db = new DIEMDANHAPIEntities()) {
using (var transaction = db.Database.BeginTransaction())
{
try
{
// IDENTIFY THE NEXT TIME POINT IF SUCCESSS
int timePoint = handler.getTimeLines(time);
// FETCH API
Result dataReceived = await handler.fetchData(time);
//
int headerIDMin = headerDAO.getFirstSuccessfullyFetch(dataReceived.dateFetching, dataReceived.Session);
//
headerDAO.Insert(dataReceived);
//
if (dataReceived.success)
{
int currenHeaderID = headerDAO.getHeader(dataReceived.dateFetching, dataReceived.Session, dataReceived.actualTimeFetching);
if (headerIDMin == -1)
{
headerIDMin = currenHeaderID;
headerDetailDAO.Insert(headerIDMin);
}
//
var listHaveNotAdded = attendanceDAO.getUnExistItemList(dataReceived.data, headerIDMin);
List <DiemDanh_NangSuatLaoDong> attendanceList = new List <DiemDanh_NangSuatLaoDong>();
foreach (var item in listHaveNotAdded)
{
DiemDanh_NangSuatLaoDong ddEntity = new DiemDanh_NangSuatLaoDong();
ddEntity.HeaderID = headerIDMin;
ddEntity.MaNV = item.MaNhanVien;
ddEntity.ActualHeaderFetched = currenHeaderID;
ddEntity.DiLam = true;
ddEntity.isFilledFromAPI = true;
ddEntity.isChangedManually = false;
ddEntity.ThoiGianXuongLo = item.startTime;
ddEntity.ThoiGianLenLo = item.endTime;
attendanceList.Add(ddEntity);
}
attendanceDAO.Insert(attendanceList);
DateTime nextTimePoint;
if (timePoint == 5)
{
// next 2 AM morning
nextTimePoint = new DateTime(time.Year, time.Month, time.Day + 1, 2, 0, 0);
}
else
{
nextTimePoint = new DateTime(time.Year, time.Month, time.Day, handler.timelinesHours[timePoint], handler.timelinesMinutes[timePoint], 0);
}
timer.Interval = nextTimePoint.Subtract(time).Seconds;
}
else
{
// 10 minutes each until successfully fetch
timer.Interval = 10 * 1000 * 60;
}
transaction.Commit();
}
catch (Exception ex)
{
WriteToFile(ex.Message);
transaction.Rollback();
}
}
}
}
