-- Скрипт 0586
-- Съдържание:
-- [1] - добавяне на колона PayType към таблица FArticles (за начин на плащане - с кредитна карта към свободен
--       фискален бон)
-- [2] - Автоматична смяна на сезон
-- [3] - Права за меню-елемента Начисляване услуга на договор
-- [4] - Права за меню-елементите "Бързи справки"
-- [5] - Тригер за СПА - ако при нова номенклатура за СПА услуга е пропуснато да се въведе брой клетки от схемата,
--       автоматично да се попълва 1


-- [1] --------------------------------------------------------------------------------------

if (select count(*) from systable as st, syscolumn as sc
  where st.table_id = sc.table_id 
  and LCase(st.table_name)=LCase('FArticles')
  and LCase(sc.column_name)=LCase('PayType')) = 0
then
  alter table FArticles add PayType integer default 1;
end if;

Go

-- [2] --------------------------------------------------------------------------------------

IF (EXISTS(Select * from sysprocedure where LCase(proc_name) = LCase('CalcAutoSeasonDate')))
  THEN Drop procedure CalcAutoSeasonDate
END IF;

GO

create function CalcAutoSeasonDate()
returns Date
begin
  -- Изчислява следващата дата, на която трябва да се смени сезон
  declare @Year varchar(4);
  declare @AutoDate Date;
  -- -------------------
  set @Year = (select Year(Current Date));
  set @AutoDate = (select cast(EndDate||@Year as Date) from PriceSeasons, Registers
    where RegisterName = 'ActivePriceSeason' and IntStojnost = SeasonNo) + 1;
  if (select count(*) from PriceSeasons where cast(StartDate||@Year as Date) = @AutoDate) = 0
  then
    set @AutoDate = (select Min(cast(StartDate||@Year as Date)) from PriceSeasons
      where cast(StartDate||@Year as Date) > @AutoDate);
  end if;
  if @AutoDate is null
  then
    set @Year = (select Year(Current Date) + 1);
    set @AutoDate = (select Min(cast(StartDate||@Year as Date)) from PriceSeasons);
  end if;
  Return @AutoDate;
end;
// v.1 [08.Aug.2007] - Yanko

GO

IF (EXISTS(Select * from sysprocedure where LCase(proc_name) = LCase('SeasonByDate')))
  THEN Drop procedure SeasonByDate
END IF

GO

create function SeasonByDate(in @ForDate Date)
returns Integer
begin
  -- Връща номер на сезон от ценоразписа на хотела за подадената дата
  declare @Season integer;
  declare @Year varchar(4);
  -- ---------------------
  set @Year = (select Year(Current Date));
  set @Season = 1;
  for f as curs scroll cursor for
    select SeasonNo as @SN, cast(StartDate||@Year as Date) as @FromDate, cast (EndDate||@Year as Date) as @ToDate
    from PriceSeasons
  do
    if (@ForDate >= @FromDate) and (@ForDate <= @ToDate)
    then
      set @Season = @SN;
    end if;
  end for;
  Return(@Season);
end;

// v.1 [09.Aug.2007] - Yanko

GO

IF (EXISTS(Select * from systrigger where LCase(trigger_name) = LCase('TRIG_Registers_Upd')))
  THEN Drop trigger TRIG_Registers_Upd
END IF

GO

create trigger TRIG_Registers_Upd after update order 1 on
DBA.Registers
Referencing NEW as RGNEW
for each row
begin
  if LCase(RGNEW.RegisterName) = LCase('ActivePriceSeason') then
    update Registers set IntStojnost = cast((select CalcAutoSeasonDate()) as Integer)
      where RegisterName='AutoSeasonDate';
  end if;
end
// v.1 [09.Aug.2007] - Yanko

GO

if (select count(*) from sys.systable as st, sys.syscolumn as sc
where st.table_id = sc.table_id and LCase(st.table_name)='priceseasons'
and LCase(sc.column_name)='startdate') = 0
then
  alter table PriceSeasons add StartDate Varchar(20) not null default '01.Jan';
  alter table PriceSeasons add EndDate Varchar(20) not null default '31.Dec';
  insert into Registers values('AutoSeasonDate', cast((select CalcAutoSeasonDate()) as Integer),'Дата за следваща смяна на сезона');
end if;

Go

-- [3] --------------------------------------------------------------------------------------

if (Select Count(*) from "DBA".PermissionTags where TagNo=5400) = 0 then
  Insert Into "DBA".PermissionTypes Values(5400,'Начислява услуги на договор',1);
  Insert Into "DBA".PermissionTags Values(5400,5400,'Начислява услуги на договор');
  for f1 as curs scroll cursor for Select N from AdminGroups where N>0
  do
    if N=1 then Insert Into "DBA".Permissions Values(N,5400,0) //Admin
      else Insert Into "DBA".Permissions Values(N,5400,1) //ostanalite niamat prava
    end if;
  end for;
end if;

Go

-- [4] --------------------------------------------------------------------------------------

if (Select Count(*) from "DBA".PermissionTags where TagNo=5500) = 0 then
  Insert Into "DBA".PermissionTypes Values(5500,'Записани бързи справки',1);
  Insert Into "DBA".PermissionTags Values(5500,5500,'Записани бързи справки');
  for f1 as curs scroll cursor for Select N from AdminGroups where N>0
  do
    if N=1 then Insert Into "DBA".Permissions Values(N,5500,0) //Admin
      else Insert Into "DBA".Permissions Values(N,5500,1) //ostanalite niamat prava
    end if;
  end for;
  -- -----
  create variable RCnt integer;
  set RCnt = 5501;
  while RCnt <= 5520 loop
    Insert Into "DBA".PermissionTypes Values(RCnt,'Бърза справка '||(RCnt-5500),1);
    Insert Into "DBA".PermissionTags Values(RCnt,RCnt,'Бърза справка '||(RCnt-5500));
    for f2 as curs2 scroll cursor for Select N from AdminGroups where N>0
    do
      if N=1 then Insert Into "DBA".Permissions Values(N,RCnt,0) //Admin
        else Insert Into "DBA".Permissions Values(N,RCnt,1) //ostanalite niamat prava
      end if;
    end for;
    set RCnt = RCnt + 1;
  end loop;
  drop variable RCnt;
end if;

Go

-- [5] --------------------------------------------------------------------------------------

IF (EXISTS(Select * from systrigger where LCase(trigger_name) = LCase('TrigServecesBefInsert')))
  THEN Drop trigger TrigServecesBefInsert
END IF;

GO

create trigger
TrigServecesBefInsert
  before insert order 1 
  on DBA.Services
  referencing new as SNew
  for each row
begin
  if (SNew.ServiceType = 2) and (SNew.TimeSteps < 1)
  then
    set SNew.TimeSteps = 1;
  end if;
end