public int getUserDialing(String idUser)
{
try
{
ModelDAL ModelDAL = new ModelDAL();
StringBuilder Query = new StringBuilder();
UsersDAL UsersDAL = new UsersDAL();
UsersEmployeeDAL UsersEmployeeDAL = new UsersEmployeeDAL();
Query.AppendLine("select ");
Query.AppendFormat("{0} ", UsersEmployeeML.DataBase.IdEmployee);
Query.AppendLine("from ");
Query.AppendFormat("{0} ", UsersDAL.TableName);
Query.AppendFormat("left join {0} on ", UsersEmployeeDAL.TableName);
Query.AppendFormat("{0}.{1} = {2}.{3} ", UsersDAL.TableName, UsersML.DataBase.Id, UsersEmployeeDAL.TableName, UsersEmployeeML.DataBase.IdEmployee);
Query.AppendLine("where ");
Query.AppendFormat("{0}.{1} = {2}", UsersEmployeeDAL.TableName, UsersEmployeeML.DataBase.IdUser, idUser);
DataTable Response = ModelDAL.DataTableRecord(Query.ToString(), ConnectionString);
if (Response.Rows.Count > 0)
{
return((int)Response.Rows[0][UsersEmployeeML.DataBase.IdEmployee]);
}
else
{
return(0);
}
}
catch (Exception ex)
{
throw new Exception(String.Format("{0}.getUserDialing: {1}", core, ex.Message));
}
}
/// <summary>
/// Obtiene la ultima Marcacion del empleado
/// </summary>
/// <param name="IdUser"></param>
/// <returns></returns>
public DataTable DataTableOldRecord(int IdUser)
{
try
{
ModelDAL ModelDAL = new ModelDAL();
String Query = String.Format("SELECT TOP 1 dateTimeRecord FROM checkInHours WHERE idEmployee = {0} ORDER BY id DESC", IdUser);
return(ModelDAL.DataTableRecord(Query.ToString(), ConnectionString));
}
catch (Exception ex)
{
throw new Exception(String.Format("{0}.DataTableOldRecord: {1}", core, ex.Message));
}
}
public DataTable TimeOutCheck(String Name)
{
try
{
StringBuilder Query = new StringBuilder();
Query.AppendFormat("SELECT * FROM timeOutCheck WHERE _registry = 1 AND name = '{0}'", Name);
return(ModelDAL.DataTableRecord(Query.ToString(), ConnectionString));
}
catch (Exception ex)
{
throw new Exception(String.Format("TimeOutCheck: {0}", ex.Message));
}
}
public DataTable GetIdEntity(int id)
{
try
{
String Query = String.Format("SELECT * FROM {0} WHERE _registry = 1 AND id={1}", TableName, id);
ModelDAL ModelDAL = new ModelDAL();
return(ModelDAL.DataTableRecord(Query, ConnectionString));
}
catch (Exception ex)
{
throw new Exception(String.Format("{0}.GetIdEntity : {1}", core, ex));
}
}
public TurnML GetTurnUser(DateTime Record, int IdUser)
{
try
{
if (DayOfWeekRecord(Record, IdUser) > 0)
{
StringBuilder Query = new StringBuilder();
Query.AppendFormat("DECLARE @DATETIMEHOURS DATETIME = '{0}' ", Record.ToString("yyyy-MM-dd HH:mm:ss"));
Query.AppendLine("DECLARE @TIMEHOURS TIME = CONVERT(time, @DATETIMEHOURS) ");
Query.AppendLine("SELECT turn.* FROM turnsOfEmployee ");
Query.AppendLine("LEFT OUTER JOIN turn ON turn.id = turnsOfEmployee.idTurn ");
Query.AppendLine("WHERE turn._registry = 1 ");
Query.AppendLine("AND CONVERT(datetime,turn.startEntry) <= CONVERT(datetime, @TIMEHOURS) ");
//Query.AppendLine("AND CONVERT(datetime,@TIMEHOURS) <= CONVERT(datetime,turn.limitDeparture) ");
Query.AppendLine("AND CONVERT(datetime,@TIMEHOURS) <= dateadd(HOUR,DATEPART(HOUR,turn.hoursJornada ), cast( dateadd(minute, DATEPART(MINUTE,turn.hoursJornada ), cast( (DATEADD(SECOND,DATEPART(SECOND,turn.hoursJornada ),cast(turn.timeEntry as datetime) )) as datetime)) as datetime)) ");
Query.AppendFormat("AND turnsOfEmployee.idEmployee ={0} ", IdUser);
DataTable dtTurnos = ModelDAL.DataTableRecord(Query.ToString(), ConnectionString);
if (dtTurnos.Rows.Count > 0)
{
return(GetEntity(dtTurnos.Rows[0]));
}
else
{
return(null);
}
}
else
{
return(null);
}
}
catch (Exception ex)
{
throw new Exception(String.Format("{0}.GetTurnUser: {1}", core, ex.Message));
}
}
public DataTable AdmissionDateEmployee(DateTime value1, DateTime value2)
{
try
{
StringBuilder Query = new StringBuilder();
Query.AppendLine("SELECT ");
Query.AppendLine("employee.id, ");
Query.AppendLine("CONCAT(employee.name, ' ', employee.lastname) as Nombre, ");
Query.AppendLine("departament.name as Departamento, ");
Query.AppendLine("employee.birthdate as Cumpleanios,");
Query.AppendLine("employee.admissionDate as FechaIngreso");
Query.AppendFormat("FROM {0} ", "employee");
Query.AppendLine("JOIN departament ON departament.id = employee.idDepartament ");
Query.AppendLine(" WHERE ");
Query.AppendFormat("employee._registry = {0} ", 1);
Query.AppendFormat("and admissionDate BETWEEN '{0}' AND '{1}'", value1.ToString("yyyy-MM-dd"), value2.ToString("yyyy-MM-dd"));
return(ModelDAL.DataTableRecord(Query.ToString(), ConnectionString));
}
catch (Exception ex)
{
throw new Exception(String.Format("AdmissionDateEmployee: {0}", ex.Message));
}
}
public DataTable All(String Type)
{
try
{
ModelDAL ModelDAL = new ModelDAL();
String Query = "";
if (Type == "All")
{
Query = String.Format("SELECT * FROM {0} WHERE _registry = 1", TableName);
}
else
{
Query = String.Format("SELECT {0}.[id] ,{0}.[name] as Turno,{0}.[Description] as Descripcion,{0}.[TimeEntry] as HoraEntrada,{0}.[StartEntry] as IniciaEntrada,{0}.[LimitEntry] as LimiteEntrada,{0}.[Departuretime] as HoraSalida,{0}.[LimitDeparture] as LimiteSalida, {0}.[HoursJornada] as HorasJornada FROM {0} where {0}._registry = 1", TableName);
}
DataTable dtTurnos = ModelDAL.DataTableRecord(Query, ConnectionString);
return(dtTurnos);
}
catch (Exception ex)
{
throw new Exception(String.Format("{0}.All : {1}", core, ex));
}
}