public async Task <IHttpActionResult> AlterarEscala(string IdEmpresa, string IdUsuario, DataEscalaModal obj)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("UPDATE TbRegimeHorario_Escala SET");
sb.AppendLine("IdVinculoUsuario = " + obj.IdVinculoRegime);
sb.AppendLine(",DataEscala = CONVERT(DATETIME, '" + obj.DataEscala + "')");
sb.AppendLine(",DataHoraEntrada = CONVERT(DATETIME, '" + obj.DataEntrada + " " + obj.HoraEntrada + "')");
sb.AppendLine(",DataHorasSaida = CONVERT(DATETIME, '" + obj.DataSaida + " " + obj.HoraSaida + "')");
sb.AppendLine(",IdOperadorAlteracao = " + IdUsuario);
sb.AppendLine(" WHERE IdEscala = " + obj.IdEscala);
Util.ExecuteNonQuery(sb.ToString());
return(Ok(obj));
}
public async Task <IHttpActionResult> SalvarEscala(string IdEmpresa, string IdUsuario, int CriarAutomatico, DataEscalaModal obj)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("DECLARE @IdVinculoUsuario bigint, @DataEscala datetime, @DataHoraEntrada datetime, @DataHorasSaida datetime, @IdOperadorInclusao int, @Mes int, @HorasFolga int, @TotalJornada int, @IDRegimeHora INT");
sb.AppendLine("SET @IdVinculoUsuario = " + obj.IdVinculoRegime);
sb.AppendLine("SET @DataEscala = CONVERT(DATETIME, '" + obj.DataEscala + "')");
sb.AppendLine("SET @DataHoraEntrada = CONVERT(DATETIME, '" + obj.DataEntrada + " " + obj.HoraEntrada + "')");
sb.AppendLine("SET @DataHorasSaida = CONVERT(DATETIME, '" + obj.DataSaida + " " + obj.HoraSaida + "') ");
sb.AppendLine("SET @IdOperadorInclusao = " + IdUsuario);
sb.AppendLine("SET @IDRegimeHora = (SELECT IdRegimeHora FROM TBVinculoUsuario WHERE IdVinculoUsuario = @IdVinculoUsuario)");
sb.AppendLine("CREATE TABLE #TEMP(IdVinculoUsuario bigint NULL,DataEscala datetime NOT NULL,DataHoraEntrada datetime NULL,DataHorasSaida datetime NULL,IdOperadorInclusao int NOT NULL)");
sb.AppendLine("SET @Mes = DATEPART(MM, @DataEscala)");
sb.AppendLine("SELECT @HorasFolga = TotalHorasFolgaPlantonista, @TotalJornada = TotalHoraDia");
sb.AppendLine("FROM TBRegimeHora ");
sb.AppendLine("WHERE IDRegimeHora = @IDRegimeHora");
sb.AppendLine("IF(@TotalJornada = 10 AND @IDRegimeHora = 22)");
sb.AppendLine("BEGIN");
sb.AppendLine(" SET @TotalJornada = @TotalJornada+2");
sb.AppendLine("END ");
sb.AppendLine("INSERT INTO #TEMP");
sb.AppendLine("(IdVinculoUsuario, DataEscala, DataHoraEntrada, DataHorasSaida, IdOperadorInclusao)");
sb.AppendLine("VALUES");
sb.AppendLine("(@IdVinculoUsuario, @DataEscala, @DataHoraEntrada, @DataHorasSaida, @IdOperadorInclusao)");
//PREENCHE OS OUTROS DIAS, CASO AUTORIZADO
sb.AppendLine("IF(1 = " + CriarAutomatico + " AND @HorasFolga > 0 AND @IDRegimeHora <> 21)");
sb.AppendLine("BEGIN");
sb.AppendLine(" WHILE(@Mes = DATEPART(MM, @DataHorasSaida))");
sb.AppendLine(" BEGIN");
sb.AppendLine(" SET @DataHoraEntrada = DATEADD(HOUR, @HorasFolga,@DataHorasSaida)");
sb.AppendLine(" SET @DataHorasSaida = DATEADD(HOUR, @HorasFolga+@TotalJornada,@DataHorasSaida)");
sb.AppendLine(" SET @DataEscala = CONVERT(DATETIME, CONVERT(VARCHAR(12),@DataHoraEntrada,103), 103)");
sb.AppendLine(" INSERT INTO #TEMP");
sb.AppendLine(" (IdVinculoUsuario, DataEscala, DataHoraEntrada, DataHorasSaida, IdOperadorInclusao)");
sb.AppendLine(" VALUES");
sb.AppendLine(" (@IdVinculoUsuario, @DataEscala, @DataHoraEntrada, @DataHorasSaida, @IdOperadorInclusao)");
sb.AppendLine(" END");
sb.AppendLine("END");
sb.AppendLine("ELSE IF(1 = " + CriarAutomatico + " AND @HorasFolga > 0 AND @IDRegimeHora = 21)");
sb.AppendLine("BEGIN");
sb.AppendLine(" INSERT INTO #TEMP");
sb.AppendLine(" (IdVinculoUsuario, DataEscala, DataHoraEntrada, DataHorasSaida, IdOperadorInclusao)");
sb.AppendLine(" SELECT @IdVinculoUsuario,DTDiasAno,");
sb.AppendLine(" CAST(CAST(DTDiasAno AS DATE) AS VARCHAR(10)) + ' ' + CAST(CAST(@DataHoraEntrada AS TIME) AS varchar(8)),");
sb.AppendLine(" CAST(CAST(DTDiasAno AS DATE) AS VARCHAR(10)) + ' ' + CAST(CAST(@DataHorasSaida AS TIME) AS varchar(8)),");
sb.AppendLine(" @IdOperadorInclusao");
sb.AppendLine(" FROM [PontoFrequenciaPMC].[dbo].[TBDiasAno]");
sb.AppendLine(" WHERE DATEPART(YYYY,DTDiasAno) = DATEPART(YYYY,GETDATE()) ");
sb.AppendLine(" AND ( FeriadoPontoFacultativo = 1 OR ");
sb.AppendLine(" DATEPART(WEEKDAY,DTDiasAno) = 7 OR DATEPART(WEEKDAY,DTDiasAno) = 1)");
sb.AppendLine(" AND IDEmpresa = "+ IdEmpresa);
sb.AppendLine(" AND DATEPART(MM,DTDiasAno) = @Mes");
sb.AppendLine(" AND DTDiasAno NOT IN (@DataEscala)");
sb.AppendLine(" ORDER BY DTDiasAno");
sb.AppendLine("END");
sb.AppendLine("INSERT INTO TbRegimeHorario_Escala");
sb.AppendLine("(IdVinculoUsuario, DataEscala, DataHoraEntrada, DataHorasSaida, IdOperadorInclusao, IdVinculoRegime)");
sb.AppendLine("SELECT IdVinculoUsuario, DataEscala, DataHoraEntrada, DataHorasSaida, IdOperadorInclusao, 1 FROM #TEMP");
sb.AppendLine("WHERE DataEscala NOT IN(SELECT DataEscala FROM TbRegimeHorario_Escala WHERE DATEPART(MM, DataEscala) = @Mes AND IdVinculoUsuario = @IdVinculoUsuario)");
Util.ExecuteNonQuery(sb.ToString());
return(Ok(obj));
}
