SET TERM ^ ;

create or alter procedure BUSSINES_DAYS (
    START_DATE date,
    END_DATE date,
    WORKDAYS integer)
returns (
    RET integer)
as
declare variable TMP_DATE date;
declare variable DIFF1 integer;
declare variable DIFF2 integer;
begin
tmp_date = '08.01.1900';
diff2 = datediff(day from tmp_date to start_date);
diff1 = datediff(day from tmp_date to end_date);
ret = (workdays*floor(diff1/7)  + MINVALUE(workdays, MOD( diff1, 7 )+1))
    - (workdays*floor(diff2/7)  + MINVALUE(workdays, MOD( diff2, 7 )));
    if(workdays = 6) then
     diff1 = 7;
    else
     diff1 = 6;
-- Ищем праздничные и невыходные.
   select count(*) from prod_calendar where data between :start_date and :end_date and type = 0 and week_day < :diff1 into :diff2;
   ret = ret - diff2;
   if(workdays = 5) then
    begin
   -- Ищем переносы выходных.
     select count(*) from prod_calendar where data between :start_date and :end_date and type = 1 and week_day < :diff1 into :diff2;
     ret = ret - diff2;
   -- Ищем переносы рабочих дней.
     select count(*) from prod_calendar where data between :start_date and :end_date and type = 2 and week_day >= :diff1 into :diff2;
     ret = ret + diff2;
    end
  suspend;
end^

SET TERM ; ^

/******************************************************************************/
/***               Generated by IBExpert 28.02.2016 12:11:18                ***/
/******************************************************************************/

/******************************************************************************/
/***      Following SET SQL DIALECT is just for the Database Comparer       ***/
/******************************************************************************/
SET SQL DIALECT 3;



/******************************************************************************/
/***                                 Tables                                 ***/
/******************************************************************************/


CREATE GENERATOR GEN_PROD_CALENDAR_ID;

CREATE TABLE PROD_CALENDAR (
    DID       BIGINT NOT NULL,
    DATA      DATE NOT NULL,
    "TYPE"    SMALLINT NOT NULL,
    WEEK_DAY  SMALLINT
);




/******************************************************************************/
/***                              Primary keys                              ***/
/******************************************************************************/

ALTER TABLE PROD_CALENDAR ADD CONSTRAINT PK_PROD_CALENDAR PRIMARY KEY (DID);


/******************************************************************************/
/***                                Indices                                 ***/
/******************************************************************************/

CREATE INDEX PROD_CALENDAR_IDX1 ON PROD_CALENDAR (DATA);


/******************************************************************************/
/***                                Triggers                                ***/
/******************************************************************************/



SET TERM ^ ;



/******************************************************************************/
/***                          Triggers for tables                           ***/
/******************************************************************************/



/* Trigger: PROD_CALENDAR_BI */
CREATE OR ALTER TRIGGER PROD_CALENDAR_BI FOR PROD_CALENDAR
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
as
begin
  if (new.did is null) then
    new.did = gen_id(gen_prod_calendar_id,1);
  new.week_day = extract(weekday from new.data);
  if (new.week_day = 0) then new.week_day = 7;
end
^

SET TERM ; ^



/******************************************************************************/
/***                          Fields descriptions                           ***/
/******************************************************************************/

COMMENT ON COLUMN PROD_CALENDAR.DID IS 
'Первичный ключ';

COMMENT ON COLUMN PROD_CALENDAR.DATA IS 
'Дата праздника или переноса';

COMMENT ON COLUMN PROD_CALENDAR."TYPE" IS 
'Тип даты
0 - праздник
1 - день стал выходным после переноса
2- день стал рабочим после переноса';

COMMENT ON COLUMN PROD_CALENDAR.WEEK_DAY IS 
'День недели - считается автоматом в триггере
1- понедельник, 2- вторник,...., 7- воскресенье';