TOTVS CRM | SFA - 12.1.2501

Parameters

Name	Type	Mode
iddarota	bigint	IN
diasemanaparam	character	IN
semana	bigint	IN
proximasdatas	integer	IN

Definition

BEGIN
	RETURN (
			SELECT string_agg(DISTINCT to_char(data, 'DD/MM/YYYY'), ', ') AS dataFim
			FROM (
				WITH DiasSemana AS (
						SELECT data
							,ROW_NUMBER() OVER (
								ORDER BY data
								) AS linha
							,diasemana
						FROM (
							SELECT DISTINCT data
								,CASE 
									WHEN upper(dia_da_semana_nome) = upper('domingo')
										THEN 'DOM'
									WHEN upper(dia_da_semana_nome) = upper('segunda')
										THEN 'SEG'
									WHEN upper(dia_da_semana_nome) = upper('terça')
										THEN 'TER'
									WHEN upper(dia_da_semana_nome) = upper('quarta')
										THEN 'QUA'
									WHEN upper(dia_da_semana_nome) = upper('quinta')
										THEN 'QUI'
									WHEN upper(dia_da_semana_nome) = upper('sexta')
										THEN 'SEX'
									WHEN upper(dia_da_semana_nome) = upper('sábado')
										THEN 'SAB'
									ELSE 'N/D'
									END AS diasemana
							FROM tempo
							WHERE data BETWEEN (
											SELECT datainiciovigencia
											FROM rota
											WHERE idrota = iddarota
											)
									AND (
											SELECT datafimvigencia
											FROM rota
											WHERE idrota = iddarota
											)
							) ExtraiDias
						)
				SELECT data
					,diasemana
					,DiaSemanaNumero
					,SemanaPeriodo
					,semanaRota
				FROM (
					SELECT data
						,linha
						,diasemana
						,MOD(linha, 7) AS DiaSemanaNumero
						,((linha - 1) / 7) + 1 AS SemanaPeriodo
						,MOD((linha - 1) / 7, (
								SELECT intervalorecorrencia
								FROM rota
								WHERE idrota = iddarota
								)) + 1 AS semanaRota
					FROM diassemana
					) ExtruturaRota
				WHERE diasemana = diasemanaparam
					AND semanaRota = semana
				ORDER BY data ASC limit proximasdatas
				) FIM
			);
END;

rotadata(bigint, character, bigint, integer)

Parameters

Definition