public async Task <Justification> ModifyJustification([Service] DBAttendanceContext dBAttendanceContext, JustificationInput input)
{
try
{
var justification = await dBAttendanceContext.Justification.FindAsync(input.Id);
if (justification != null)
{
justification.Date = input.Date;
justification.Motive = input.Motive;
justification.State = input.State;
await dBAttendanceContext.SaveChangesAsync();
return(justification);
}
else
{
throw new QueryException("No se encontró la justificación.");
}
}
catch (System.Exception e)
{
throw new QueryException(e.Message);
}
}
public async Task <Employee> ModifyEmployee([Service] DBAttendanceContext dBAttendanceContext, EmployeeInput input)
{
try
{
var employee = await dBAttendanceContext.Employee.FindAsync(input.CardId);
if (employee != null)
{
employee.Name = input.Name;
employee.Lastname = input.Lastname;
employee.Genre = input.Genre;
employee.Birthdate = input.BirthDate;
employee.Address = input.Address;
employee.Phone = input.Phone;
employee.Email = input.Email;
employee.PhotoName = input.PhotoName;
employee.State = input.State;
await dBAttendanceContext.SaveChangesAsync();
return(employee);
}
else
{
throw new QueryException("No se encontró al empleado.");
}
}
catch (System.Exception e)
{
throw new QueryException(e.Message);
}
}
public async Task <Schedule> AddSchedule([Service] DBAttendanceContext dBAttendanceContext, ScheduleInput input)
{
try
{
var schedule = new Schedule
{
StartDate = input.StartDate,
FinishDate = input.FinishDate,
EmployeeCardId = input.EmployeeCardId
};
input.ScheduleDetail.ForEach(sd =>
{
var scheduleDetail = new ScheduleDetail
{
Day = sd.Day,
InHour = sd.InHour,
OutHour = sd.OutHour
};
schedule.ScheduleDetail.Add(scheduleDetail);
});
dBAttendanceContext.Schedule.Add(schedule);
await dBAttendanceContext.SaveChangesAsync();
return(schedule);
}
catch (System.Exception e)
{
throw new QueryException(e.Message);
}
}
public async Task <Employee> DownEmployee([Service] DBAttendanceContext dBAttendanceContext, string employeeCardId)
{
try
{
var employee = await dBAttendanceContext.Employee.FindAsync(employeeCardId);
if (employee != null)
{
employee.State = false;
var contract = employee.Contract.FirstOrDefault(c => c.State);
if (contract != null)
{
contract.State = false;
}
var schedule = employee.Schedule.FirstOrDefault(s => s.State);
if (schedule != null)
{
contract.State = false;
}
await dBAttendanceContext.SaveChangesAsync();
return(employee);
}
else
{
throw new QueryException("No se encontró al empleado.");
}
}
catch (System.Exception e)
{
throw new QueryException(e.Message);
}
}
public async Task <Permission> ModifyPermission([Service] DBAttendanceContext dBAttendanceContext, PermissionInput input)
{
try
{
var permission = await dBAttendanceContext.Permission.FindAsync(input.Id);
if (permission != null)
{
permission.Date = input.Date;
permission.Motive = input.Motive;
permission.State = input.State;
await dBAttendanceContext.SaveChangesAsync();
return(permission);
}
else
{
throw new QueryException("No se encontró el permiso.");
}
}
catch (System.Exception e)
{
throw new QueryException(e.Message);
}
}
public async Task <License> ModifyLicense([Service] DBAttendanceContext dBAttendanceContext, LicenseInput input)
{
try
{
var license = await dBAttendanceContext.License.FindAsync(input.Id);
if (license != null)
{
license.StartDate = input.StartDate;
license.FinishDate = input.FinishDate;
license.Document = input.Document;
license.DocumentName = input.DocumentName;
license.LicenseTypeId = input.LicenseTypeId;
license.State = input.State;
await dBAttendanceContext.SaveChangesAsync();
return(license);
}
else
{
throw new QueryException("No se encontró la licencia.");
}
}
catch (System.Exception e)
{
throw new QueryException(e.Message);
}
}
public async Task <Contract> ModifyContract([Service] DBAttendanceContext dBAttendanceContext, ContractInput input)
{
try
{
var contract = await dBAttendanceContext.Contract.FindAsync(input.Id);
if (contract != null)
{
contract.StartDate = input.StartDate;
contract.FinishDate = input.FinishDate;
contract.Mount = input.Mount;
contract.State = input.State;
contract.ExtraHours = input.ExtraHours;
await dBAttendanceContext.SaveChangesAsync();
return(contract);
}
else
{
throw new QueryException("Contrato no encontrado.");
}
}
catch (System.Exception e)
{
throw new QueryException(e.Message);
}
}
public async Task <Attendance> OutAttendance([Service] DBAttendanceContext dBAttendanceContext, int attendanceId)
{
try
{
var attendance = await dBAttendanceContext.Attendance.FindAsync(attendanceId);
if (attendance != null)
{
if (attendance.OutHour == null)
{
attendance.OutHour = DateTime.Now.TimeOfDay;
await dBAttendanceContext.SaveChangesAsync();
return(attendance);
}
else
{
throw new QueryException("Ya marcó salida.");
}
}
else
{
throw new QueryException("Asistencia no encontrada.");
}
}
catch (System.Exception e)
{
throw new QueryException(e.Message);
}
}
public async Task <User> ModifyUser([Service] DBAttendanceContext dBAttendanceContext, UserInput input)
{
try
{
var user = await dBAttendanceContext.User.FindAsync(input.Id);
if (user != null)
{
user.Name = input.Name;
user.Password = input.Password;
user.State = input.State;
await dBAttendanceContext.SaveChangesAsync();
return(user);
}
else
{
throw new QueryException("No se encontró el usuario.");
}
}
catch (System.Exception e)
{
throw new QueryException(e.Message);
}
}
public async Task <Attendance> GetAttendancesDate([Service] DBAttendanceContext dBAttendanceContext, string employeeCardId, DateTime dateAttendance)
{
DateTime firstSunday = new DateTime(1753, 1, 7);
var DbF = Microsoft.EntityFrameworkCore.EF.Functions;
var date = DateTime.Today;
try
{
var attendance = await(from a in dBAttendanceContext.Attendance
join s in dBAttendanceContext.Schedule on a.EmployeeCardId equals s.EmployeeCardId
join sd in dBAttendanceContext.ScheduleDetail on s.Id equals sd.ScheduleId
where (DbF.DateDiffDay(firstSunday, a.Date) % 7 == sd.Day) && a.InHour > sd.InHour &&
a.EmployeeCardId == employeeCardId && a.Date == dateAttendance
select a).FirstOrDefaultAsync();
if (attendance != null)
{
return(attendance);
}
else
{
throw new QueryException("No se encontró tardanza");
}
}
catch (System.Exception e)
{
throw new QueryException(e.Message);
}
}
public async Task <IReadOnlyList <ScheduleDetail> > GetNonAttendance([Service] DBAttendanceContext dBAttendanceContext)
{
DateTime firstSunday = new DateTime(1753, 1, 7);
var DbF = Microsoft.EntityFrameworkCore.EF.Functions;
var date = DateTime.Today;
try
{
// return await dBAttendanceContext.Attendance
// .Where(a => DbF.DateDiffDay(firstSunday, a.Date) % 7 == 1 ).ToListAsync();
var stringSQL = "select * from ((select * from (select dateadd(dd, value, dateadd(month, DATEPART(m,GETDATE())-1, dateadd(year, DATEPART(YEAR,GETDATE())-1900, '1900.01.01'))) as dt_val " +
"from(select (v2 * 4 + v1) * 4 + v0 as value from(select 0 as v0 union select 1 union select 2 union select 3) as rs0 cross join " +
"(select 0 as v1 union select 1 union select 2 union select 3) as rs1 cross join " +
"(select 0 as v2 union select 1 union select 2 union select 3) as rs2) as rs) as rs2 inner join (select S.EmployeeCardId as CardID,SD.Day from Schedule S " +
"inner join ScheduleDetail SD on S.Id = SD.ScheduleId) " +
"as SDJ on SDJ.Day = DATEPART(dw,dt_val)-1 where month(dt_val) = DATEPART(m,GETDATE()))) As X left join Attendance as A " +
"on X.dt_val = A.Date and X.CardID = A.EmployeeCardId where X.dt_val<GETDATE() and A.Id is null order by X.dt_val ";
return(await dBAttendanceContext.ScheduleDetail.FromSqlRaw(stringSQL).ToListAsync());
}
catch (System.Exception e)
{
throw new QueryException(e.Message);
}
}
public async Task <IReadOnlyList <Attendance> > GetDelay([Service] DBAttendanceContext dBAttendanceContext, string employeeCardId)
{
DateTime firstSunday = new DateTime(1753, 1, 7);
var DbF = Microsoft.EntityFrameworkCore.EF.Functions;
try
{
// return await dBAttendanceContext.Attendance
// .Where(a => DbF.DateDiffDay(firstSunday, a.Date) % 7 == 1 ).ToListAsync();
if (employeeCardId != null)
{
return(await(from a in dBAttendanceContext.Attendance
join s in dBAttendanceContext.Schedule on a.EmployeeCardId equals s.EmployeeCardId
join sd in dBAttendanceContext.ScheduleDetail on s.Id equals sd.ScheduleId
where (DbF.DateDiffDay(firstSunday, a.Date) % 7 == sd.Day) && a.InHour > sd.InHour && a.EmployeeCardId == employeeCardId
select a).ToListAsync());
}
else
{
return(await(from a in dBAttendanceContext.Attendance
join s in dBAttendanceContext.Schedule on a.EmployeeCardId equals s.EmployeeCardId
join sd in dBAttendanceContext.ScheduleDetail on s.Id equals sd.ScheduleId
where (DbF.DateDiffDay(firstSunday, a.Date) % 7 == sd.Day) && a.InHour > sd.InHour
select a).ToListAsync());
}
}
catch (System.Exception e)
{
throw new QueryException(e.Message);
}
}
public async Task <IReadOnlyList <Contract> > GetContracts([Service] DBAttendanceContext dBAttendanceContext, string employeeCardId)
{
if (employeeCardId != null)
{
return(await(from c in dBAttendanceContext.Contract where c.EmployeeCardId == employeeCardId select c).ToListAsync());
}
else
{
return(await dBAttendanceContext.Contract.Where(c => c.State).ToListAsync());
}
}
public async Task <IReadOnlyList <Schedule> > GetSchedules([Service] DBAttendanceContext dBAttendanceContext, string employeeCardId)
{
if (employeeCardId != null)
{
return(await(from s in dBAttendanceContext.Schedule where s.EmployeeCardId == employeeCardId select s).ToListAsync());
}
else
{
return(await dBAttendanceContext.Schedule.ToListAsync());
}
}
public async Task <IReadOnlyList <License> > GetLicenses([Service] DBAttendanceContext dBAttendanceContext, string employeeCardId)
{
if (employeeCardId != null)
{
return(await(from l in dBAttendanceContext.License where l.EmployeeCardId == employeeCardId select l).ToListAsync());
}
else
{
return(await dBAttendanceContext.License.ToListAsync());
}
}
public async Task <IReadOnlyList <Attendance> > GetAttendanceId([Service] DBAttendanceContext dBAttendanceContext, int id)
{
if (id != null)
{
return(await(from a in dBAttendanceContext.Attendance where a.Id == id select a).ToListAsync());
}
else
{
return(null);
}
}
public async Task <IReadOnlyList <Attendance> > GetAttendances([Service] DBAttendanceContext dBAttendanceContext, string employeeCardId)
{
if (employeeCardId != null)
{
return(await(from a in dBAttendanceContext.Attendance where a.EmployeeCardId == employeeCardId select a).ToListAsync());
}
else
{
return(await dBAttendanceContext.Attendance.ToListAsync());
}
}
public async Task <IReadOnlyList <User> > GetUsers([Service] DBAttendanceContext dBAttendanceContext, string employeeCardId)
{
if (employeeCardId != null)
{
return(await(from u in dBAttendanceContext.User where u.EmployeeCardId == employeeCardId select u).ToListAsync());
}
else
{
return(await dBAttendanceContext.User.ToListAsync());
}
}
public async Task <IReadOnlyList <Permission> > GetPermissions([Service] DBAttendanceContext dBAttendanceContext, string employeeCardId)
{
if (employeeCardId != null)
{
return(await(from p in dBAttendanceContext.Permission where p.EmployeeCardId == employeeCardId select p).ToListAsync());
}
else
{
return(await dBAttendanceContext.Permission.ToListAsync());
}
}
public async Task <Schedule> ModifySchedule([Service] DBAttendanceContext dBAttendanceContext, ScheduleInput input)
{
try
{
var schedule = await dBAttendanceContext.Schedule.FindAsync(input.Id);
if (schedule != null)
{
schedule.StartDate = input.StartDate;
schedule.FinishDate = input.FinishDate;
schedule.State = input.State;
input.ScheduleDetail.ForEach(sd =>
{
switch (sd.Action)
{
case 0:
var scheduleDetail = new ScheduleDetail
{
Day = sd.Day,
InHour = sd.InHour,
OutHour = sd.OutHour
};
schedule.ScheduleDetail.Add(scheduleDetail);
break;
case 1:
var scheduleDetail1 = schedule.ScheduleDetail.SingleOrDefault(sd1 => sd1.Id == sd.Id);
scheduleDetail1.Day = sd.Day;
scheduleDetail1.InHour = sd.InHour;
scheduleDetail1.OutHour = sd.OutHour;
break;
case 2:
var scheduleDetail2 = schedule.ScheduleDetail.SingleOrDefault(sd1 => sd1.Id == sd.Id);
schedule.ScheduleDetail.Remove(scheduleDetail2);
break;
}
});
await dBAttendanceContext.SaveChangesAsync();
return(schedule);
}
else
{
throw new QueryException("No se encontró el horario.");
}
}
catch (System.Exception e)
{
throw new QueryException(e.Message);
}
}
public async Task <Employee> AddEmployee([Service] DBAttendanceContext dBAttendanceContext, EmployeeInput input)
{
try
{
var employee = new Employee
{
CardId = input.CardId,
Name = input.Name,
Lastname = input.Lastname,
Genre = input.Genre,
Birthdate = input.BirthDate,
Address = input.Address,
Phone = input.Phone,
Email = input.Email,
PhotoName = input.PhotoName
};
var contractInput = input.Contract;
employee.Contract.Add(new Contract
{
StartDate = contractInput.StartDate,
FinishDate = contractInput.FinishDate,
Mount = contractInput.Mount,
ExtraHours = contractInput.ExtraHours
});
var scheduleInput = input.Schedule;
var schedule = new Schedule
{
StartDate = scheduleInput.StartDate,
FinishDate = scheduleInput.FinishDate
};
scheduleInput.ScheduleDetail.ForEach(sd =>
{
var scheduleDetail = new ScheduleDetail
{
Day = sd.Day,
InHour = sd.InHour,
OutHour = sd.OutHour
};
schedule.ScheduleDetail.Add(scheduleDetail);
});
employee.Schedule.Add(schedule);
await dBAttendanceContext.SaveChangesAsync();
return(employee);
}
catch (System.Exception e)
{
throw new QueryException(e.Message);
}
}
public async Task <IReadOnlyList <Justification> > GetJustifications([Service] DBAttendanceContext dBAttendanceContext, string employeeCardId)
{
if (employeeCardId != null)
{
return(await(from j in dBAttendanceContext.Justification
join a in dBAttendanceContext.Attendance on j.AttendanceId equals a.Id
where a.EmployeeCardId == employeeCardId
select j)
.ToListAsync());
}
else
{
return(await dBAttendanceContext.Justification.ToListAsync());
}
}
public async Task <License> DownLicense([Service] DBAttendanceContext dBAttendanceContext, int licenseId)
{
try
{
var license = await dBAttendanceContext.License.FindAsync(licenseId);
license.State = false;
await dBAttendanceContext.SaveChangesAsync();
return(license);
}
catch (System.Exception e)
{
throw new QueryException(e.Message);
}
}
public async Task <Justification> DownJustification([Service] DBAttendanceContext dBAttendanceContext, int justificationId)
{
try
{
var justification = await dBAttendanceContext.Justification.FindAsync(justificationId);
justification.State = false;
await dBAttendanceContext.SaveChangesAsync();
return(justification);
}
catch (System.Exception e)
{
throw new QueryException(e.Message);
}
}
public async Task <Permission> DownPermission([Service] DBAttendanceContext dBAttendanceContext, int permissionId)
{
try
{
var permission = await dBAttendanceContext.Permission.FindAsync(permissionId);
permission.State = false;
await dBAttendanceContext.SaveChangesAsync();
return(permission);
}
catch (System.Exception e)
{
throw new QueryException(e.Message);
}
}
public async Task <IReadOnlyList <ScheduleDetail> > GetSchedulesDetailHour([Service] DBAttendanceContext dBAttendanceContext, string employeeCardId)
{
DateTime firstSunday = new DateTime(1753, 1, 7);
var DbF = Microsoft.EntityFrameworkCore.EF.Functions;
var date = DateTime.Today;
if (employeeCardId != null)
{
return(await(from sd in dBAttendanceContext.ScheduleDetail select sd).ToListAsync());
}
else
{
return(await(from s in dBAttendanceContext.Schedule join sd in dBAttendanceContext.ScheduleDetail on s.Id equals
sd.ScheduleId where s.State == true && (DbF.DateDiffDay(firstSunday, date) % 7 == sd.Day) select sd).ToListAsync());
}
}
public async Task <Schedule> DownSchedule([Service] DBAttendanceContext dBAttendanceContext, int scheduleId)
{
try
{
var schedule = await dBAttendanceContext.Schedule.FindAsync(scheduleId);
schedule.State = false;
await dBAttendanceContext.SaveChangesAsync();
return(schedule);
}
catch (System.Exception e)
{
throw new QueryException(e.Message);
}
}
public async Task <Models.LicenseType> DeleteLicenseType([Service] DBAttendanceContext dBAttendanceContext, byte licenseTypeId)
{
try
{
var licenseType = await dBAttendanceContext.LicenseType.FindAsync(licenseTypeId);
dBAttendanceContext.LicenseType.Remove(licenseType);
await dBAttendanceContext.SaveChangesAsync();
return(licenseType);
}
catch (System.Exception e)
{
throw new QueryException(e.Message);
}
}
public async Task <Attendance> AddAttendance([Service] DBAttendanceContext dBAttendanceContext, string employeeCardId)
{
try
{
var currentDayOfWeek = (byte)DateTime.Today.DayOfWeek;
var currentToday = DateTime.Today;
var currentSchedule = await dBAttendanceContext.Schedule.SingleOrDefaultAsync(s => s.EmployeeCardId == employeeCardId && s.State == true);
// await (from s in dBAttendanceContext.Schedule where s.EmployeeCardId == 'employeeCardId' && s.State==true select s ).SingleOrDefaultAsync();
if (currentSchedule != null)
{
var currentDay = currentSchedule.ScheduleDetail.FirstOrDefault(sd => (currentDayOfWeek == 0 ? 7 : currentDayOfWeek) == sd.Day);
if (currentDay != null)
{
var existingAttendance = await dBAttendanceContext.Attendance.SingleOrDefaultAsync(a => a.Date == currentToday && a.EmployeeCardId == employeeCardId);
if (null == null)
{
var attendance = new Attendance
{
EmployeeCardId = employeeCardId
};
dBAttendanceContext.Attendance.Add(attendance);
await dBAttendanceContext.SaveChangesAsync();
return(attendance);
}
else
{
throw new QueryException("Ya marcó asistencia");
}
}
else
{
throw new QueryException("Hoy no le toca marcar asistencia.");
}
}
else
{
throw new QueryException("Horario no encontrado.");
}
}
catch (System.Exception e)
{
throw new QueryException(e.Message);
}
}
public async Task <User> AddUser([Service] DBAttendanceContext dBAttendanceContext, UserInput input)
{
try
{
var user = new User
{
Name = input.Name,
Password = input.Password,
EmployeeCardId = input.EmployeeCardId
};
dBAttendanceContext.Add(user);
await dBAttendanceContext.SaveChangesAsync();
return(user);
}
catch (System.Exception e)
{
throw new QueryException(e.Message);
}
}
