Foros Club Delphi - Cálculo de horas laborales

Buenas noches amigos,
dándole una vuelta de tuerca a este procedimiento, he realizado una nueva versión. Ahora permite tener en cuenta diferentes horarios, y podríamos configurar cualquier combinación que seamos capaces de soportar.
Para ello, he cambiado el paradigma del cálculo.
Me gustaría que si alguien tiene un rato, pudiera hacer pruebas para comprobar su correcto
funcionamiento.

Os pongo en cuestión.
Tenemos que medir un servicio que tenemos contratado, y que debe resolvernos los problemas que le reportemos en un tiempo máximo de imaginemos X horas. Para medir cómo nos lo ejecutan, es decir, si cumplen lo estipulado, miramos los avisos cerrados por ejemplo en el mes, y si el tiempo transcurrido hasta cerrar el problema cumple el contrato, perfecto, si no, tendríamos que reclamar, pagar menos, o lo que sea.

Pero nuestra empresa tiene distintas oficinas, y cada una de ellas tiene un horario diferente; bueno, cada una de ellas no, todas tienen el mismo horario, y algunas sólo abren ciertos días a la semana, ya que donde se ubican, no es necesario tener todos los días abierto o es la misma persona la que atiende dos oficinas, una de lunes a miercoles y otra de jueves a viernes.
Pero esta circunstancia debemos tenerla en cuenta, ya que nuestro proveedor no podrá darnos el servicio en el tiempo acordado por no poder acceder a la oficina cerrada. Si aplicáramos el mismo horario
para todas las oficinas, le penalizaríamos, y claro, se quejará y con razón.

Este planteamiento también servirá para medir un servicio que ofrezcamos en nuestra empresa. Así
podremos discutir con nuestro cliente disponiendo de datos fiables.

Espero que hayais entendido la problemática.

Al tema:
En la versión anterior me hacía un lío, y al final era un engorro. En este caso, cambio totalmente
la perspectiva y la verdad, es que en pocas líneas queda solucionado elegantemente.

- necesitamos una tabla de horarios

Código SQL [-]CREATE TABLE HORARIOS (
    ID      INTEGER NOT NULL,
    NOMBRE  VARCHAR(25)
);
INSERT INTO HORARIOS (ID, NOMBRE) VALUES (1, 'L_a_V');
INSERT INTO HORARIOS (ID, NOMBRE) VALUES (8, 'L_X_V');
INSERT INTO HORARIOS (ID, NOMBRE) VALUES (9, 'M_J');

- Necesitamos una tabla de detalle de los horarios. Para cada horario almacenaremos el día de la semana, el horario de ese día ( contempla mañana y tarde exclusivamente y las horas totales de cada día.
por ejemplo:
horario normal: de lunes a jueves de 8:00 a 17:00 y el viernes de 8:00 a 15:00
horario de lunes a miercoles de 8:00 a 13:30 y de 15:30 a 18:00 , jueves y viernes cerrado.
horario de jueves y viernes de 8:00 a 13:30 y de 15:30 a 18:00, lunes a viernes cerrado.
Si no abro a la tarde, pongo la misma hora que el fin de la mañana.
Para indicar los días que no abro, pongo la misma hora de inicio y de fin de apertura, y
las horas totales de apertura, a cero.

Código SQL [-]CREATE TABLE DETALLE_HORARIOS (
    ID                INTEGER NOT NULL,
    IDHORARIO         INTEGER NOT NULL,  -> foreign key con la tabla horarios.ID
    DIASEMANA         VARCHAR(10) NOT NULL,
    HORARIO_INICIO_M  TIME NOT NULL,
    HORARIO_FIN_M     TIME NOT NULL,
    HORARIO_INICIO_T  TIME NOT NULL,
    HORARIO_FIN_T     TIME NOT NULL,
    HORAS_JORNADA     FLOAT NOT NULL
);
Sólo pongo un horario
INSERT INTO DETALLE_HORARIOS (ID, IDHORARIO, DIASEMANA, HORARIO_INICIO_M, HORARIO_FIN_M, HORARIO_INICIO_T, HORARIO_FIN_T, HORAS_JORNADA)
         VALUES (1, 1, 'L', '08:00:00', '14:00:00', '14:00:00', '14:00:00', 6);
INSERT INTO DETALLE_HORARIOS (ID, IDHORARIO, DIASEMANA, HORARIO_INICIO_M, HORARIO_FIN_M, HORARIO_INICIO_T, HORARIO_FIN_T, HORAS_JORNADA)
         VALUES (2, 1, 'M', '08:00:00', '14:00:00', '14:00:00', '14:00:00', 6);
INSERT INTO DETALLE_HORARIOS (ID, IDHORARIO, DIASEMANA, HORARIO_INICIO_M, HORARIO_FIN_M, HORARIO_INICIO_T, HORARIO_FIN_T, HORAS_JORNADA)
         VALUES (3, 1, 'X', '08:00:00', '14:00:00', '14:00:00', '14:00:00', 6);
INSERT INTO DETALLE_HORARIOS (ID, IDHORARIO, DIASEMANA, HORARIO_INICIO_M, HORARIO_FIN_M, HORARIO_INICIO_T, HORARIO_FIN_T, HORAS_JORNADA)
        VALUES (4, 1, 'J', '08:00:00', '14:00:00', '17:00:00', '20:00:00', 9);
INSERT INTO DETALLE_HORARIOS (ID, IDHORARIO, DIASEMANA, HORARIO_INICIO_M, HORARIO_FIN_M, HORARIO_INICIO_T, HORARIO_FIN_T, HORAS_JORNADA)
        VALUES (5, 1, 'V', '08:00:00', '14:00:00', '14:00:00', '14:00:00', 6);
INSERT INTO DETALLE_HORARIOS (ID, IDHORARIO, DIASEMANA, HORARIO_INICIO_M, HORARIO_FIN_M, HORARIO_INICIO_T, HORARIO_FIN_T, HORAS_JORNADA)
        VALUES (6, 1, 'S', '08:00:00', '08:00:00', '08:00:00', '08:00:00', 0);
INSERT INTO DETALLE_HORARIOS (ID, IDHORARIO, DIASEMANA, HORARIO_INICIO_M, HORARIO_FIN_M, HORARIO_INICIO_T, HORARIO_FIN_T, HORAS_JORNADA)
        VALUES (7, 1, 'D', '08:00:00', '08:00:00', '08:00:00', '08:00:00', 0);

- Necesitamos una tabla de festivos, ya que si es fiesta no podemos imputar las horas de ese día.
En ella incluiremos sólamente los días festivos del año. El día 1 de enero, el 6 de enero, ...

Código SQL [-]CREATE TABLE FESTIVOS (
    DIA  DATE NOT NULL
);
Festivos del 2014
INSERT INTO FESTIVOS (DIA) VALUES ('2014-01-01');
INSERT INTO FESTIVOS (DIA) VALUES ('2014-01-06');
INSERT INTO FESTIVOS (DIA) VALUES ('2014-04-18');
INSERT INTO FESTIVOS (DIA) VALUES ('2014-05-01');
INSERT INTO FESTIVOS (DIA) VALUES ('2014-08-15');
INSERT INTO FESTIVOS (DIA) VALUES ('2014-11-01');
INSERT INTO FESTIVOS (DIA) VALUES ('2014-12-06');
INSERT INTO FESTIVOS (DIA) VALUES ('2014-12-08');
INSERT INTO FESTIVOS (DIA) VALUES ('2014-12-25');

El truco del almendruco... es la store procedure con la que saco un registro por día desde que se abre el aviso hasta que se cierra. Esta tontada consigue que la lógica se simplifique de manera importante. Lo que hago simplemente es sacar un registro por cada día indicando además qué día de la semana es (lunes, martes...).

Esto me permite hacer un inner join con la tabla detalle_horarios por el campo "día de la semana" en la que consigo los siguientes datos:
dia, dia de la semana, horarios de mañana y tarde y horas totales.
Si además, dado que en la tabla de detalle_horarios tengo un campo que me indica las horas de apertura
diarias, con un simple filtro ( and horas_jornada <> 0), elimino los días que no son hábiles. Los que no trabajo entre semana y los que son fin de semana ( ya que los he incluido en la tabla ).

Si además meto en la coctelera la tabla de festivos, eliminando los días que puedan caer en festivo en el intervalo de esa tabla, ya sólo me quedan los días que debo tratar. Lo que antes para mí era un infierno calcular, ahora es una cosa "sencilla".

Código SQL [-]CREATE OR ALTER PROCEDURE TABLA_DIAS (
    f_inicio date,
    f_fin date)
returns (
    fechasalida date,
    diasemana char(1))
as
declare variable fecha date;
begin
  /* Procedure Text */
  FECHA = f_inicio ;
  while  (FECHA <= F_FIN ) DO
  BEGIN
    FECHASALIDA=FECHA;
    DIASEMANA='-';
    if (EXTRACT(weekday FROM FECHASALIDA ) = 1 ) then DIASEMANA='L';
    if (EXTRACT(weekday FROM FECHASALIDA ) = 2 ) then DIASEMANA='M';
    if (EXTRACT(weekday FROM FECHASALIDA ) = 3 ) then  DIASEMANA='X';
    if (EXTRACT(weekday FROM FECHASALIDA ) = 4 ) then DIASEMANA='J';
    if (EXTRACT(weekday FROM FECHASALIDA ) = 5 ) then DIASEMANA='V';
    if (EXTRACT(weekday FROM FECHASALIDA ) = 6 ) then DIASEMANA='S';
    if (EXTRACT(weekday FROM FECHASALIDA ) = 0 ) then DIASEMANA='D';
    suspend;
    FECHA = FECHA+1;
  END

end

El último detalle es ver cómo lo calculo. Saco la consulta, y por cada registro, voy sumando los tiempos.
1.- si los días no son ni el primero ni el último, simplemente sumo las horas_jornada del día y a otra
cosa.
2. Si el día que trato es el primero, cojo la hora de inicio siempre y cuando no sea anterior a la apertura.
Si es anterior, lógicamente, pondré la hora de apertura.
3.- Si el día que trato es el último, y la hora de cierre del aviso es posterior a la hora de cierre, pongo
la hora de cierre de la avería como la hora de cierre de la oficina.
4.- Si el día de apertura y el de cierre no son el mismo, cojo según el caso la hora de inicio o la
hora de cierre de la oficina las horas de apertura y de cierre de la avería.
5.- Un detalle final. Si tenemos mañana y tarde, y la hora de inicio del aviso es por la mañana, y la
hora de cierre de la avería es por la tarde, le resto el tiempo del mediodía, ya que la oficina ha
estado cerrada y no puedo atender a los técnicos.
6. -Ah, se me olvidaba, si el tiempo es negativo por algún casual, no hago nada.

Código SQL [-]CREATE OR ALTER PROCEDURE HORAS_CON_HORARIOS (
    horario varchar(25),
    f_inicio timestamp,
    f_fin timestamp)
returns (horas float)
as
declare variable wdiasemana char(1);
declare variable wdia date; /* dia tratado */
declare variable whi_m time; /* horario_inicio_Mañana */
declare variable whf_m time; /* horario_fin_Mañana */
declare variable whi_t time; /* horario_inicio_Tarde */
declare variable whf_t time; /* horario_fin_Tarde */
declare variable whoras_jornada float;
declare variable tiempototal float;
declare variable hora_inicio time;
declare variable hora_fin time;
begin
  /* Procedure Text */
TIEMPOTOTAL=0.0;
for  SELECT D.fechasalida AS DIA, D.DIASEMANA,
            DH.horario_inicio_m, DH.horario_fin_m,
            DH.horario_inicio_t, DH.horario_fin_t,
            DH.horas_jornada
            from tabla_dias(cast(:f_inicio as date),cast(:f_fin as date))  D
            inner join detalle_horarios DH on (DH.diasemana=D.DIASEMANA)
            inner join horarios H on (h.id = DH.idhorario)
            where
            /* cogemos el horario */
            h.nombre=:HORARIO
            /* los dias que no hay servicio no los cogemos. Festivos y horarios especiales */
            and DH.horas_jornada <> 0
            /* los festivos nacionales tampoco los cogemos */
            and D.fechasalida not in (select  DIA from FESTIVOS)
            order by D.fechasalida
            INTO
            :WDIA,:WDIASEMANA,:WHI_M,:WHF_M,:WHI_T,:WHF_T,:WHORAS_JORNADA
DO
  BEGIN
    if (WDIA=CAST(f_inicio AS DATE)) then
         HORA_INICIO = CAST( F_INICIO AS time);
    ELSE
         HORA_INICIO = WHI_M;
    if (wDIA=CAST(f_FIN AS DATE) ) then
         HORA_FIN = CAST( F_FIN AS time);
    ELSE
         HORA_FIN = WHF_T;
    /* los dias intermedios, simplemente sumo las horas totales */
    if (WDIA<>CAST(f_inicio AS DATE) AND WDIA <> CAST(F_FIN AS DATE)) then
       TIEMPOTOTAL=TIEMPOTOTAL+WHORAS_JORNADA;
    ELSE
       BEGIN
         if (HORA_INICIO < WHI_M) then
            HORA_INICIO = whi_m;
         if (HORA_INICIO >= whf_M AND HORA_INICIO < whi_t ) then
            HORA_INICIO = WHI_T;
         if (HORA_FIN > WHF_T) then
            HORA_FIN= WHF_T ;
         if (hora_fin >= WHF_M AND hora_fin < WHI_T ) then
            HORA_FIN= WHF_M;
         if (HORA_FIN-hora_inicio > 0) then
         BEGIN
            TIEMPOTOTAL=TIEMPOTOTAL+(HORA_FIN-hora_inicio );
            if ((HORA_INICIO >= whi_m AND hora_inicio < WHF_M)
               AND
               (hora_fin >= WHI_T AND HORA_FIN < WHF_T)) then
               TIEMPOTOTAL=TIEMPOTOTAL-(WHI_T-WHF_M);
          END
       END
  END
  HORAS=(TIEMPOTOTAL/60)/60;
suspend;
end

Llamadas a la store procedure:

Código SQL [-]SELECT * FROM HORAS_CON_HORARIOS('L_a_V', '21-NOV-2014 13:30:00', '24-NOV-2014 08:00:00')

devuelve 0,5 (horas)

Código SQL [-]SELECT * FROM HORAS_CON_HORARIOS('L_a_V', '21-NOV-2014 11:30:00', '24-NOV-2014 19:00:00')

Devuelve 8,5 ( horas)

Espero no haberos aburrido. La verdad es que es un tema que me ha dado muchos dolores de cabeza, y espero que no vuelva a tenerlos.
Respecto al trabajo con fechas, días treanscurridos, horas , etc, ese store procedure de los días es el que me ha hecho ver la luz.

Un saludo a todos.
Espero su retroalimentación...