drop table sodcafe;

create table sodcafe
(
  cia         char(3) not null,
  empleado    varchar2(15) not null,
  entrada     date not null,
  salida      date,
  constraint sodcafe_pk 
     primary key(cia, empleado, entrada)
);

create index sodcafe_entrada_idx on SODCAFE(CIA, ENTRADA);

create or replace VIEW SODCAFE_DET
   as
   select CIA, EMPLEADO, ENTRADA, SALIDA, (SALIDA-ENTRADA)*1440 as TIEMPOT, 
          DECODE(SIGN((SALIDA-ENTRADA)*1440-15),1,(SALIDA-ENTRADA)*1440-15,0) as TIEMPOE
   from SODCAFE;

-- Por Horas
create or replace VIEW SODCAFE_DET
   as
   select CIA, EMPLEADO, ENTRADA, SALIDA, (SALIDA-ENTRADA)*24 as TIEMPOT, 
          DECODE(SIGN((SALIDA-ENTRADA)*24-8),1,(SALIDA-ENTRADA)*24-8,0) as TIEMPOE
   from SODCAFE;


create or replace VIEW SODCAFE_DIA
   as
   select CIA, EMPLEADO, min(C.ENTRADA) as ENTRADA,
          max(Decode(C.SALIDA, NULL, C.ENTRADA, C.SALIDA)) as SALIDA,
          sum(C.TIEMPOT) as TIEMPOT, sum(C.TIEMPOE) as TIEMPOE
   from SODCAFE_DET C
   group by CIA, EMPLEADO, TRUNC(C.ENTRADA);

create or replace view SODCAFE_RES
   as
   select CIA, EMPLEADO, min(C.ENTRADA) as ENTRADA, max(C.SALIDA) as SALIDA,
          sum(C.TIEMPOT) as TIEMPOT, SUM(C.TIEMPOE) as TIEMPOE
   from SODCAFE_DET C
   group by CIA, EMPLEADO, trunc(C.ENTRADA,'MONTH');


create or replace view SODCAFE_DEMP
   as
   select d.cia, d.empleado, d.entrada, d.salida, d.tiempot, d.tiempoe,
       c.entrada as dent, c.salida as dsal
   from sodcafe_dia d, sodcafe c
   where d.cia=c.cia
     and d.empleado=c.empleado
     and c.entrada >= d.entrada
     and c.entrada <= d.salida;
