public async Task <IHttpActionResult> GetUsuarios(ServidorFiltroModel filtro)
{
StringBuilder sb = new StringBuilder();
string where = "";
if (!string.IsNullOrEmpty(filtro.Nome))
{
where += " AND A.DSUsuario like '%" + Util.TratarString(filtro.Nome) + "%'";
}
if (!string.IsNullOrEmpty(filtro.Matricula))
{
where += " AND B.Matricula like '" + Util.TratarString(filtro.Matricula) + "'";
}
if (!string.IsNullOrEmpty(filtro.IdSetor))
{
where += " AND B.IDSetor = " + Util.TratarString(filtro.IdSetor);
}
sb.AppendLine("SELECT DISTINCT B.IDVinculoUsuario Codigo, A.DSUsuario, isNull(B.Matricula, '') Matricula, B.IDRegimeHora");
sb.AppendLine("FROM TBUsuario A");
sb.AppendLine("JOIN TBVinculoUsuario B ON (A.IDUsuario = B.IDUsuario)");
//sb.AppendLine("LEFT JOIN TbRegimeHorario_Vinculo C ON (C.IDVinculoUsuario = B.IDVinculoUsuario AND C.Situacao = 1)");
sb.AppendLine("WHERE B.IDEmpresa = " + filtro.usu.IdEmpresa);
sb.AppendLine(where);
List <object> list = new List <object>();
SqlDataReader dr = Util.getDataReader(sb.ToString());
while (dr.Read())
{
list.Add(new
{
Codigo = dr["Codigo"].ToString(),
Nome = dr["DSUsuario"].ToString(),
Matricula = dr["Matricula"].ToString(),
IdRegimeHora = dr["IDRegimeHora"].ToString(),
});
}
dr.Close();
return(Ok(list));
}
public async Task <IHttpActionResult> GetEscalasUsuarios(ServidorFiltroModel filtro)
{
int RowInicial = (((filtro.NumeroPagina) * 50) - 50);
int RowFinal = (((filtro.NumeroPagina) * 50));
string where = "", where2 = "";
if (!string.IsNullOrEmpty(filtro.Nome))
{
where += " AND U.DSUsuario like '%" + Util.TratarString(filtro.Nome) + "%'";
where2 += " AND U.DSUsuario like '%" + Util.TratarString(filtro.Nome) + "%'";
}
if (!string.IsNullOrEmpty(filtro.Matricula))
{
where += " AND VU.Matricula like '" + Util.TratarString(filtro.Matricula) + "'";
where2 += " AND VU.Matricula like '" + Util.TratarString(filtro.Matricula) + "'";
}
if (!string.IsNullOrEmpty(filtro.IdSetor))
{
where += " AND VU.IDSetor = " + Util.TratarString(filtro.IdSetor);
where2 += " AND VU.IDSetor = " + Util.TratarString(filtro.IdSetor);
}
if (!string.IsNullOrEmpty(filtro.Mes))
{
where += " AND isNull(datepart(MM, RE.DataEscala)," + Util.TratarString(filtro.Mes) + ") = " + Util.TratarString(filtro.Mes);
}
if (!string.IsNullOrEmpty(filtro.IdTipoRegime))
{
where += " AND VU.IDRegimeHora = " + Util.TratarString(filtro.IdTipoRegime);
where2 += " AND VU.IDRegimeHora = " + Util.TratarString(filtro.IdTipoRegime);
}
string idRegime = "", idUsuario = "";
bool loopRegime = true;
List <RegimesEscalaModal> list = new List <RegimesEscalaModal>();
StringBuilder sb = new StringBuilder();
sb.AppendLine("SELECT datepart(DAY, RE.DataEscala) DiaMes, RE.IdEscala, RE.DataEscala, CONVERT(VARCHAR(12),RE.DataHoraEntrada,103) DataEntrada, CONVERT(VARCHAR(5),CONVERT(TIME(4),RE.DataHoraEntrada)) HoraEntrada, ");
sb.AppendLine("CONVERT(VARCHAR(12),RE.DataHorasSaida,103) DataSaida, CONVERT(VARCHAR(5),CONVERT(TIME(4),RE.DataHorasSaida)) HoraSaida, VU.IdVinculoUsuario IdUsuario, VU.IDVinculoUsuario IdVinculoRegime,R.IDRegimeHora,R.DSRegimeHora Regime,");
sb.AppendLine("U.DSUsuario");
sb.AppendLine("INTO #TEMP");
sb.AppendLine("FROM dbo.TBVinculoUsuario VU");
sb.AppendLine("JOIN dbo.TBRegimeHora R ON (R.IDRegimeHora = VU.IDRegimeHora)");
sb.AppendLine("JOIN dbo.TBUsuario U ON (U.IdUsuario = VU.IdUsuario)");
sb.AppendLine("LEFT JOIN TbRegimeHorario_Escala RE ON(VU.IDVinculoUsuario = RE.IdVinculoUsuario)");
sb.AppendLine("WHERE VU.IDStatus = 1 AND VU.IdEmpresa = " + filtro.usu.IdEmpresa);
sb.AppendLine(where);
sb.AppendLine("SELECT *");
sb.AppendLine("INTO #TEMP2");
sb.AppendLine("FROM(");
sb.AppendLine("SELECT * FROM #TEMP");
sb.AppendLine("UNION");
sb.AppendLine("SELECT NULL DiaMes, NULL IdEscala, NULL DataEscala, NULL DataEntrada, NULL HoraEntrada, NULL DataSaida, NULL HoraSaida, ");
sb.AppendLine("VU.IdVinculoUsuario IdUsuario, VU.IdVinculoUsuario IdVinculoRegime,R.IDRegimeHora,R.DSRegimeHora Regime,");
sb.AppendLine("U.DSUsuario");
sb.AppendLine("FROM dbo.TBVinculoUsuario VU WITH(NOLOCK)");
sb.AppendLine("JOIN dbo.TBRegimeHora R WITH(NOLOCK) ON (R.IDRegimeHora = VU.IDRegimeHora)");
sb.AppendLine("JOIN dbo.TBUsuario U WITH(NOLOCK) ON (U.IdUsuario = VU.IdUsuario)");
sb.AppendLine("WHERE VU.IDStatus = 1 AND VU.IdEmpresa = " + filtro.usu.IdEmpresa);
sb.AppendLine("AND VU.IDVinculoUsuario NOT IN(SELECT IdVinculoRegime FROM #TEMP)");
sb.AppendLine(where2);
sb.AppendLine(") A");
sb.AppendLine("DECLARE @CountRows int");
sb.AppendLine("SET @CountRows = (SELECT COUNT(*) FROM (SELECT DISTINCT IdVinculoRegime FROM #TEMP2) A)");
sb.AppendLine("SELECT DiaMes, IdEscala, DataEscala, DataEntrada, HoraEntrada, DataSaida, HoraSaida, IdUsuario, IdVinculoRegime, IDRegimeHora, Regime, @CountRows NumeroLinhas,");
sb.AppendLine("CONVERT(VARCHAR(20), DSUsuario) DSUsuario");
sb.AppendLine("FROM #TEMP2");
sb.AppendLine("WHERE IdVinculoRegime IN");
sb.AppendLine("(");
sb.AppendLine(" SELECT DISTINCT IdVinculoRegime");
sb.AppendLine(" FROM #TEMP2");
sb.AppendLine(" ORDER BY IdVinculoRegime");
sb.AppendLine(" OFFSET " + RowInicial + " ROWS FETCH NEXT " + RowFinal + " ROWS ONLY");
sb.AppendLine(")");
sb.AppendLine("ORDER BY IDRegimeHora, DSUsuario, IdUsuario, DiaMes");
//sb.AppendLine("ORDER BY IDRegimeHora, DSUsuario, DiaMes");
#region OLD
//sb.AppendLine("SELECT datepart(DAY, RE.DataEscala) DiaMes, RE.IdEscala, RE.DataEscala, CONVERT(VARCHAR(12),RE.DataHoraEntrada,103) DataEntrada, CONVERT(VARCHAR(5),CONVERT(TIME(4),RE.DataHoraEntrada)) HoraEntrada, ");
//sb.AppendLine("CONVERT(VARCHAR(12),RE.DataHorasSaida,103) DataSaida, CONVERT(VARCHAR(5),CONVERT(TIME(4),RE.DataHorasSaida)) HoraSaida, VU.IdVinculoUsuario IdUsuario, RV.IdVinculoRegime,R.IDRegimeHora,R.DSRegimeHora Regime,");
//sb.AppendLine("CASE WHEN SUBSTRING(U.DSUsuario, 0, CHARINDEX(' ', U.DSUsuario, CHARINDEX(' ', U.DSUsuario, 0) + 1)) <> '' THEN SUBSTRING(U.DSUsuario, 0, CHARINDEX(' ', U.DSUsuario, CHARINDEX(' ', U.DSUsuario, 0) + 1)) ELSE U.DSUsuario END DSUsuario");
//sb.AppendLine("INTO #TEMP");
//sb.AppendLine("FROM dbo.TbRegimeHorario_Vinculo RV");
//sb.AppendLine("JOIN dbo.TBRegimeHora R ON (R.IDRegimeHora = RV.IDRegimeHora)");
//sb.AppendLine("JOIN dbo.TBVinculoUsuario VU ON (VU.IDVinculoUsuario = RV.IDVinculoUsuario)");
//sb.AppendLine("JOIN dbo.TBUsuario U ON (U.IdUsuario = VU.IdUsuario)");
//sb.AppendLine("LEFT JOIN TbRegimeHorario_Escala RE ON(RV.IdVinculoRegime = RE.IdVinculoRegime)");
//sb.AppendLine("WHERE VU.IdEmpresa = " + filtro.usu.IdEmpresa);
//sb.AppendLine(where);
//sb.AppendLine("SELECT * FROM #TEMP");
//sb.AppendLine("UNION");
//sb.AppendLine("SELECT NULL DiaMes, NULL IdEscala, NULL DataEscala, NULL DataEntrada, NULL HoraEntrada, NULL DataSaida, NULL HoraSaida, ");
//sb.AppendLine("VU.IdVinculoUsuario IdUsuario, RV.IdVinculoRegime,R.IDRegimeHora,R.DSRegimeHora Regime,");
//sb.AppendLine("CASE WHEN SUBSTRING(U.DSUsuario, 0, CHARINDEX(' ', U.DSUsuario, CHARINDEX(' ', U.DSUsuario, 0) + 1)) <> '' THEN SUBSTRING(U.DSUsuario, 0, CHARINDEX(' ', U.DSUsuario, CHARINDEX(' ', U.DSUsuario, 0) + 1)) ELSE U.DSUsuario END DSUsuario");
//sb.AppendLine("FROM dbo.TbRegimeHorario_Vinculo RV");
//sb.AppendLine("JOIN dbo.TBRegimeHora R ON (R.IDRegimeHora = RV.IDRegimeHora)");
//sb.AppendLine("JOIN dbo.TBVinculoUsuario VU ON (VU.IDVinculoUsuario = RV.IDVinculoUsuario)");
//sb.AppendLine("JOIN dbo.TBUsuario U ON (U.IdUsuario = VU.IdUsuario)");
//sb.AppendLine("WHERE VU.IdEmpresa = " + filtro.usu.IdEmpresa);
//sb.AppendLine(where2);
//sb.AppendLine("ORDER BY IDRegimeHora, DSUsuario, DiaMes");
#endregion OLD
SqlDataReader dr = Util.getDataReader(sb.ToString());
int i = 0;
int indUs = 0;
while (dr.Read())
{
if (idRegime != dr["IDRegimeHora"].ToString())
{
loopRegime = true;
}
if (loopRegime)
{
list.Add(new RegimesEscalaModal()
{
IdRegime = dr["IDRegimeHora"].ToString(),
Regime = dr["Regime"].ToString(),
NumeroLinhas = dr["NumeroLinhas"].ToString(),
Usuarios = new List <UsuariosEscalaModal>()
});
loopRegime = false;
}
i = list.Count - 1;
if ((idUsuario != dr["IdUsuario"].ToString()) || (idRegime != dr["IDRegimeHora"].ToString()))
{
list[i].Usuarios.Add(new UsuariosEscalaModal
{
IdUsuario = dr["IdUsuario"].ToString(),
Nome = dr["DSUsuario"].ToString(),
IdVinculoRegime = dr["IdVinculoRegime"].ToString(),
Datas = new List <DataEscalaModal>()
});
}
indUs = list[i].Usuarios.Count - 1;
list[i].Usuarios[indUs].Datas.Add(new DataEscalaModal
{
IdEscala = dr["IdEscala"].ToString(),
DataEscala = dr["DataEscala"].ToString(),
DataEntrada = dr["DataEntrada"].ToString(),
HoraEntrada = dr["HoraEntrada"].ToString(),
DataSaida = dr["DataSaida"].ToString(),
HoraSaida = dr["HoraSaida"].ToString(),
DiaMes = dr["DiaMes"].ToString(),
});
idRegime = dr["IDRegimeHora"].ToString();
idUsuario = dr["IdUsuario"].ToString();
}
dr.Close();
return(Ok(list));
}