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)); }