public IEnumerable <OperatorRanking> GetOperatorsRanking(DateTime desde, DateTime hasta, List <int> choferes) { OperatorRanking or = null; DetachedCriteria dc = GetDatamartDetachedCriteria().FilterBeginBetween(desde, hasta); if (choferes.Count <= SQLParameterLimit) { dc.Add(Restrictions.In(Projections.Property <Datamart>(p => p.Employee.Id), choferes)); } ProjectionList pl = Projections.ProjectionList(); pl.Add(Projections.Sum <Datamart>(dm => dm.Kilometers).WithAlias(() => or.Kilometros)); pl.Add(Projections.Sum <Datamart>(dm => dm.MovementHours).WithAlias(() => or.Hours)); pl.Add(Projections.Group <Datamart>(dm => dm.Employee.Id).WithAlias(() => or.IdOperador)); ICriteria crit = GetDatamartCriteria(0, dc, pl, null, typeof(OperatorRanking)); IList <OperatorRanking> results = crit.List <OperatorRanking>(); var empleadoDAO = new EmpleadoDAO(); foreach (OperatorRanking r in results) { Empleado empleado = empleadoDAO.FindById(r.IdOperador); r.Operador = empleado.Entidad.Apellido; r.Legajo = empleado.Legajo; } IEnumerable <OperatorRanking> result = (choferes.Count > SQLParameterLimit ? results.Where(c => choferes.Contains(c.IdOperador)) : results); return(result); }
public IEnumerable <Tarjeta> GetTarjetasLibres(IEnumerable <int> empresas, IEnumerable <int> lineas) { var empleadoDao = new EmpleadoDAO(); var todas = GetList(empresas, lineas); var asignadas = empleadoDao.FindActivosConTarjetas(empresas, lineas).Select(x => x.Tarjeta.Id); return(todas.Where(card => !asignadas.Contains(card.Id)).OrderBy(tarjeta => tarjeta.Numero)); }
/// <summary> /// Gets aggregate operator activities for the current timespan. /// </summary> /// <param name="desde"></param> /// <param name="hasta"></param> /// <param name="distrito"></param> /// <param name="planta"></param> /// <param name="ids"></param> /// <param name="km"></param> /// <returns></returns> public IEnumerable <OperatorActivity> GetOperatorActivities(DateTime desde, DateTime hasta, Int32 distrito, Int32 planta, List <Int32> ids, Double km) { OperatorActivity oa = null; DetachedCriteria dc = GetDatamartDetachedCriteria().FilterBeginBetween(desde, hasta).CreateAlias("Employee", "ch", JoinType.InnerJoin); if (distrito != -1) { dc.Add(Restrictions.Or(Restrictions.IsNull("ch.Empresa"), Restrictions.Eq("ch.Empresa.Id", distrito))); } if (planta != -1) { dc.Add(Restrictions.Or(Restrictions.IsNull("ch.Linea"), Restrictions.Eq("ch.Linea.Id", distrito))); } if (ids.Count <= SQLParameterLimit) { dc.Add(Restrictions.In("ch.Id", ids)); } ProjectionList pl = Projections.ProjectionList(); pl.Add(Projections.Sum <Datamart>(dm => dm.StoppedHours).WithAlias(() => oa.StoppedHours)); pl.Add(Projections.Sum <Datamart>(dm => dm.MovementHours).WithAlias(() => oa.MovementHours)); pl.Add(Projections.Sum <Datamart>(dm => dm.InfractionMinutes).WithAlias(() => oa.InfractionsMinutes)); pl.Add(Projections.Sum <Datamart>(dm => dm.Infractions).WithAlias(() => oa.Infractions)); IProjection kilometersProjection = Projections.Sum <Datamart>(dm => dm.Kilometers).WithAlias(() => oa.Kilometers); pl.Add(kilometersProjection); pl.Add(Projections.Max <Datamart>(dm => dm.MaxSpeed).WithAlias(() => oa.MaxSpeed)); pl.Add(Projections.Group <Datamart>(dm => dm.Employee.Id).WithAlias(() => oa.OperatorId)); ICriteria crit = GetDatamartCriteria(0, dc, pl, null, typeof(OperatorActivity)); crit.Add(Restrictions.Ge(kilometersProjection, km)); IList <OperatorActivity> results = crit.List <OperatorActivity>(); var empleadoDAO = new EmpleadoDAO(); foreach (OperatorActivity r in results) { Empleado empleado = empleadoDAO.FindById(r.OperatorId); r.Operator = empleado.Entidad != null ? empleado.Entidad.Descripcion : null; } foreach (OperatorActivity r in results.Where(filteredResult => filteredResult.Kilometers > 0 && filteredResult.MovementHours > 0)) { r.AverageSpeed = Convert.ToInt32(r.Kilometers / r.MovementHours); } IEnumerable <OperatorActivity> result = (ids.Count > SQLParameterLimit ? results.Where(r => ids.Contains(r.OperatorId)) : results); return(result); }