/*Създава таблица Owners,която съдържа OwnersN собственика и др. информация*/
IF (EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('Owners'))) THEN
  Drop Table Owners
END IF

GO

create table Owners
(
  OwnerN integer primary key,
  Username varchar(30) not null unique,
  "Password" varchar(30) not null,
  ContractN integer not null
)

GO

/*Създаване на тригер, който да update-ва Owners при update на Contracts*/
IF (EXISTS (select trigname from SYSTRIGGERS where LCase(trigname)=LCase('TrigContractsBeforeUpdate'))) THEN
drop trigger TrigContractsBeforeUpdate
END IF

GO

CREATE TRIGGER "TrigContractsBeforeUpdate" before update order 1 on
DBA.Contracts
REFERENCING OLD AS Contr_old NEW AS Contr_new
for each row
begin
      IF (EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('Owners'))) THEN
        if Contr_new.GroupNo = 2 then
           update Owners set ContractN = Contr_new.ContractNo where ContractN=Contr_old.ContractNo
        end if;
      END IF
end

GO

/*Добавя тригер на Contracts, който да insert-ва в Owners при insert в Contracts*/
IF (EXISTS (select trigname from SYSTRIGGERS where LCase(trigname)=LCase('Trig_After_Insert_Into_Contracts'))) THEN
drop trigger Trig_After_Insert_Into_Contracts
END IF

GO

CREATE TRIGGER "Trig_After_Insert_Into_Contracts" after insert order 1 on
DBA.Contracts
REFERENCING NEW AS ContractsNew
for each row
begin
declare username_ varchar(30);
IF (EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('Owners'))) THEN
  if ContractsNew.GroupNo = 2 then
     set username_ = 'u'||ContractsNew.ContractNo;
     if (select count(*) from Owners where Username = username_ ) > 0  then
        set username_ = username_ || '1';
		   end if;
     insert into Owners values( (select max(OwnerN)+1 from Owners), username_, 'p'||ContractsNew.ContractNo, ContractsNew.ContractNo );
  end if;
END IF
end


/*Край Owners*/

GO

/*Създава таблица OwnersRooms,която определя кои стаи има всеки собственик и дали е активна връзката или не*/
IF (EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('OwnersRooms'))) THEN
  Drop Table OwnersRooms
END IF

GO

create table OwnersRooms
(
  OwnerN integer,
  Room varchar(10) not null,
  Active integer not null default 1
)

GO

ALTER TABLE OwnersRooms
  ADD FOREIGN KEY "FK_OwnerN" ("OwnerN")
  REFERENCES Owners ("OwnerN") on delete cascade on update cascade
/*Край OwnersRooms*/

GO

/*Създава таблица OwnersRelations, подобна на Relations но със справки специфични за собствениците*/
IF (EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('OwnersRelations'))) THEN
  Drop Table OwnersRelations;
END IF

GO

-------------------------------------------------
--   Create tables
-------------------------------------------------

CREATE TABLE "DBA"."OwnersRelations"
(
	"TableName"     		varchar(50) NOT NULL ,
	"ColName"       		char(100)   NOT NULL ,
	"BGTableName"   		varchar(50) NOT NULL ,
	"BGColName"     		varchar(30) NOT NULL ,
	"RelationTable" 		varchar(50)     NULL ,
	"RelationCol"   		varchar(30)     NULL ,
	"SelectCol"     		integer         NULL ,
	"N"     		    	integer     NOT NULL ,
	"Function"      		varchar(100)    NULL ,
	"Suma"  		  	    varchar(100)    NULL ,
	"Mask"  			    varchar(30)     NULL ,
	"Align" 			    integer         NULL ,
	"Sortirane"     		integer         NULL ,
	"Zadaljitelna"  		varchar(1)      NULL ,
	"Promenliva"    		varchar(1)      NULL ,
	"FilterList"    		long varchar    NULL ,
	"DftFilter"     		long varchar    NULL ,
	"Shirina"       		smallint        NULL ,
	"Opisanie"      		varchar(100)    NULL ,
	"DftLike"       		tinyint         NULL ,
	"AutoStretch"   		tinyint         NULL ,
	"Spravka"       		integer         NULL ,
	"PoslData"      		timestamp       NULL ,
	 PRIMARY KEY ("N"),
	
)
go
COMMENT ON COLUMN "DBA"."OwnersRelations"."TableName" IS 'име на view'
go
COMMENT ON COLUMN "DBA"."OwnersRelations"."ColName" IS 'име на колона'
go
COMMENT ON COLUMN "DBA"."OwnersRelations"."BGTableName" IS 'име на справката в Репортера'
go
COMMENT ON COLUMN "DBA"."OwnersRelations"."BGColName" IS 'име на колоната в справката в Репортера'
go
COMMENT ON COLUMN "DBA"."OwnersRelations"."RelationTable" IS 'име на английски на view'
go
COMMENT ON COLUMN "DBA"."OwnersRelations"."RelationCol" IS 'име на английски на колона от view'
go
COMMENT ON COLUMN "DBA"."OwnersRelations"."SelectCol" IS 'ако  колоната присъства в справката стойността  е 2 , ако 0 - озн че не е маркирана и не участва в справката '
go
COMMENT ON COLUMN "DBA"."OwnersRelations"."N" IS 'уникален номер'
go
COMMENT ON COLUMN "DBA"."OwnersRelations"."Function" IS 'показва по кое поле има агрегираща функция, използва се най-вече за групиране , сумиране и т.н.'
go
COMMENT ON COLUMN "DBA"."OwnersRelations"."Suma" IS 'по кое поле може да се прави сума'
go
COMMENT ON COLUMN "DBA"."OwnersRelations"."Mask" IS 'до кой знак може да се закръгля'
go
COMMENT ON COLUMN "DBA"."OwnersRelations"."Align" IS 'ако стойността е 0 се подравнява надясно , ако 1 - наляво'
go
COMMENT ON COLUMN "DBA"."OwnersRelations"."Sortirane" IS 'по кое поле може да се прави сортиране '
go
COMMENT ON COLUMN "DBA"."OwnersRelations"."Zadaljitelna" IS 'кое поле от справката да се появява задължително  '
go
COMMENT ON COLUMN "DBA"."OwnersRelations"."Promenliva" IS '1 - дали има променлива, ако полето е празно е нормална колона в таблица'
go
COMMENT ON COLUMN "DBA"."OwnersRelations"."FilterList" IS 'дали има зададен  default филтър'
go
COMMENT ON COLUMN "DBA"."OwnersRelations"."DftFilter" IS 'Дали има default-но зададен филтър по тази колона за съответната справка'
go
COMMENT ON COLUMN "DBA"."OwnersRelations"."Shirina" IS 'ширина на колоната в пиксели'
go
COMMENT ON COLUMN "DBA"."OwnersRelations"."DftLike" IS 'дали има зададен филтър по шаблон'
go
COMMENT ON COLUMN "DBA"."OwnersRelations"."PoslData" IS 'информация за колоната кога е добавяна или променяна последно'
go
COMMENT ON TABLE "DBA"."OwnersRelations" IS 'Съдържа информация за справките от Reportera'
go

-------------------------------------------------
--   Add foreign key definitions
-------------------------------------------------


CREATE UNIQUE INDEX "OwnersRelations UNIQUE _N_" ON "DBA"."OwnersRelations"
(
	"N" ASC
)
GO

-------------------------------------------------
--   Create triggers
-------------------------------------------------

create trigger TRIG_OwnersRelations_Ins after insert order 1 on
DBA.OwnersRelations
referencing new as RLNEW
for each row
begin
  if RLNEW.PoslData is null then
    update OwnersRelations set PoslData = "date"(current date) where
      N = RLNEW.N
  end if
end

GO

create trigger TRIG_OwnersRelations_Upd after update order 2 on
DBA.OwnersRelations
referencing old as RLOLD new as RLNEW
for each row
begin
  if RLOLD.PoslData = RLNEW.PoslData and RLOLD.SelectCol = RLNEW.SelectCol then
    update OwnersRelations set PoslData = "date"(current date) where
      N = RLNEW.N
  end if
end

/*Край OwnersRelations*/

GO

/*///////////////////////////////////////////////////////////////////////////////// CREATE VIEWS*/

IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('MoveRelations'))) THEN
Drop procedure MoveRelations
END IF
GO

IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('MoveRelations'))) THEN
Drop procedure MoveRelations
END IF
GO

CREATE PROCEDURE MoveRelations(IN @ViewName Varchar(200), IN @NewName Varchar(200), IN @RemoveBvr integer)

// @RemoveBvr = 1 - ne insertva zapisite s bvrData1 ili bvrData2

BEGIN
  declare CheckNum    integer;
  declare NewStartNum integer;
  declare FinalPos    Integer;
  set CheckNum = 0;
  set NewStartNum = (select isnull(Max(N), 10) from OwnersRelations);
  if NewStartNum>10 then
    set NewStartNum=NewStartNum+100;
  End IF;
if (select count(*) from OwnersRelations where OwnersRelations.TableName=@NewName)=0 then
  f1: for f1 as curs scroll cursor for
      SELECT
      TableName as @TableName,
      ColName as @ColName,
      BGTableName as @BGTableName,
      BGColName as @BGColName,
      RelationTable as @RelationTable,
      RelationCol as @RelationCol,
      SelectCol as @SelectCol,
      N as @N,
      "Function" as @Function,
      Suma as @Suma,
      Mask as @Mask,
      Align as @Align,
      Sortirane as @Sortirane,
      Zadaljitelna as @Zadaljitelna,
      Promenliva as @Promenliva,
      FilterList as @FilterList,
      DftFilter as @DftFilter,
      Shirina as @Shirina,
      Opisanie as @Opisanie,
      DftLike as @DftLike,
      AutoStretch as @AutoStretch,
      Spravka as @Spravka,
      PoslData as @PoslData
  FROM RELATIONS
  WHERE (TableName = @ViewName)
  Order by N asc do
    if CheckNum = 0 then
      set FinalPos = NewStartNum-10;
      set CheckNum = 1;
    End if;
    if @removebvr='0' then
    set FinalPos= FinalPos+10; /*@N-StartNum+NewStartNum;*/
    Insert into OwnersRelations(TableName, ColName, BGTableName,
                               BGColName, RelationTable, RelationCol,
                               SelectCol, N, "Function", Suma,
                               Mask, Align, Sortirane,
                               Zadaljitelna, Promenliva, FilterList,
                               DftFilter, Shirina, Opisanie,
                               DftLike, AutoStretch, Spravka,
                               PoslData)
                         values(@NewName, @ColName, @BGTableName,
                               @BGColName, @RelationTable, @RelationCol,
                               @SelectCol, FinalPos, @Function, @Suma,
                               @Mask, @Align, @Sortirane,
                               @Zadaljitelna, @Promenliva, @FilterList,
                               @DftFilter, @Shirina, @Opisanie,
                               @DftLike, @AutoStretch, @Spravka,
                               @PoslData)
    else
     if (@RelationCol<>'bvrData1')and(@RelationCol<>'bvrData2')then
     set FinalPos= FinalPos+10; /*@N-StartNum+NewStartNum;*/
     Insert into OwnersRelations(TableName, ColName, BGTableName,
                               BGColName, RelationTable, RelationCol,
                               SelectCol, N, "Function", Suma,
                               Mask, Align, Sortirane,
                               Zadaljitelna, Promenliva, FilterList,
                               DftFilter, Shirina, Opisanie,
                               DftLike, AutoStretch, Spravka,
                               PoslData)
                         values(@NewName, @ColName, @BGTableName,
                               @BGColName, @RelationTable, @RelationCol,
                               @SelectCol, FinalPos, @Function, @Suma,
                               @Mask, @Align, @Sortirane,
                               @Zadaljitelna, @Promenliva, @FilterList,
                               @DftFilter, @Shirina, @Opisanie,
                               @DftLike, @AutoStretch, @Spravka,
                               @PoslData)
     end if
    End if;


  end for;
END IF;
END

//[v.1 19.07.2006] - Created by Koceto, Yanko
//[v.2 07.08.2006] - Edited by Angel, Yanko
GO

/*{Spravki(view) bez bvr... za Galaxy}*/
IF (EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('v_Web_Water'))) THEN
Drop View v_Web_Water
END IF

GO

create view
  DBA.v_Web_Water
  as select W."Date" as Data, Month(W."Date") as Mon,WA.ApartName,WA.UserName,W.AbonatNo,W.OldValue,W.NewValue,
    W.NewValue-W.OldValue as Kol,(select RealStojnost from DBA.RealRegisters where RegisterName = 'Water') as SinglePrice,
    Kol*SinglePrice as Suma
	from DBA.Water as W,DBA.WaterAbonats as WA
	where WA.WaterGauge = W.AbonatNo

GO

call MoveRelations('v_Water', 'v_Web_Water', 1)

GO

IF (EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('v_Web_HotWater'))) THEN
Drop View v_Web_HotWater
END IF

GO

create view DBA.v_Web_HotWater
  as select W."Date" as Data,Month(W."Date") as Mon,WA.ApartName,WA.UserName,W.AbonatNo,W.OldValue,W.NewValue,
    W.NewValue-W.OldValue as Kol,(select RealStojnost from DBA.RealRegisters where RegisterName = 'HotWater') as SinglePrice,
    Kol*SinglePrice as Suma from
    DBA.HotWater as W,DBA.WaterAbonats as WA where
    WA.WaterGauge = W.AbonatNo

GO

call MoveRelations('v_HotWater', 'v_Web_HotWater', 1)

GO

IF (EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('v_Web_UsedServicesByContract'))) THEN
Drop View v_Web_UsedServicesByContract
END IF

GO

IF (Exists(select Table_name from SysTable where LCase(Table_name)=LCase('v_Web_UsedServicesByContract'))) THEN
  Drop view v_Web_UsedServicesByContract
END IF
GO

CREATE VIEW v_Web_UsedServicesByContract(GNum, Guest, DateCredit, Service, Quantity, PrWithoutDisc,
            curKod, SumWithoutDisc, Disc, Discount, PrWithDisc, SumWithDisc, BgSumWithDisc, Dogovor,
            BgPayedServiceWhitDisc, RoomNum, N)
AS SELECT Distinct
   g.GuestNum as GNum,
   g."Name" as Guest,
   b.DateRegDt as DateCredit,
   (if b.IsRest=0 then 'Нощувка в стая '+cast(b.Room as varchar) else 'Почивка в стая '+cast(b.Room as varchar) endif) as Service,
   b.NumLodgings as Quantity,
   b.Price as PrWithoutDisc,
   cur.Symbol as curKod,
   Quantity*PrWithoutDisc as SumWithoutDisc,
   scr.Discount as Disc,
   scr.Discount as Discount,
   CalcPriceWithDiscount(Discount,PrWithoutDisc) as PrWithDisc,
   Quantity*PrWithDisc as SumWithDisc,
   SumWithDisc*GetCurrencyRate(DateCredit,cur.KodCurrency) as BGSumWithDisc,
   c.FullName as Dogovor,
   (if b.PayedLodgings=0 then 0 else ((b.PayedLodgings*PrWithDisc)*GetCurrencyRate(DateCredit,cur.KodCurrency)) endif) as BgPayedServiceWhitDisc,
   b.Room as RoomNum,
   b.Regnum as N
from "dba".Book as b,"dba".Guests as g,"dba".Contracts as c,"dba".Currencies as cur,"dba".ServicesForContract as scr
where b.ContractPays=c.ContractNo
      and b.Guest=g.GuestNum
      and b.ContractPays=scr.ContractNo
      and scr.ServiceNo in(-1,-2)
      and b.KodCurrency=cur.KodCurrency
      and c.ContractNo<>0


UNION ALL

SELECT Distinct
  g.GuestNum as GNum,
  g."Name" as Guest,
  sc.DateCreditDt as DateCredit,
  s.ServiceName,
  (select Sum(NumberServices) from "dba".ServiceCredits
  where Service=s.ServiceNo and GuestNo=g.GuestNum
  and KodCurrency=cur.KodCurrency
  and CreditNo=sc.CreditNo) as Quantity,
  sc.SumCredit as PrWithoutDisc,
  cur.Symbol as curKod,
  (Quantity*PrWithoutDisc) as SumWithoutDisc,
  (select Discount from "dba".ServicesForContract where ServiceNo=s.ServiceNo and ContractNo=c.ContractNo) as Disc,
  Discount=if Disc is null then 0 else Disc endif,
  CalcPriceWithDiscount(Discount,PrWithoutDisc) as PrWithDisc,
  Quantity*PrWithDisc as SumWithDisc,
  SumWithDisc*GetCurrencyRate(DateCredit,cur.KodCurrency) as BGSumWithDisc,
  c.FullName as Dogovor,
  (if sc.PayedServices=0 then 0 else ((sc.PayedServices*PrWithDisc)*GetCurrencyRate(DateCredit,cur.KodCurrency)) endif) as  BgPayedServiceWhitDisc,
  sc.RoomNum,
  sc.CreditNo as N
  from "dba".ServiceCredits as sc,"dba".Contracts as c,"dba".Guests as g,"dba".Services as s,"dba".Currencies as cur
     where sc.ContractPays=c.ContractNo
and sc.GuestNo=g.GuestNum
and sc.ContractPays<>0
and s.ServiceNo=sc.Service
and sc.KodCurrency=cur.KodCurrency
and g.GuestNum<>0
GO

call MoveRelations('v_UsedServicesByContract', 'v_Web_UsedServicesByContract', 1)

//[v.1 14.07.2006] - Edited by Koceto

/*///////////////////////////////////////////////////////////////////////////////// CREATE VIEWS*/

GO

/*Създава таблица OwnersPrava,която определя кои стравки може да вижда собственикът*/
IF (EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('OwnersPrava'))) THEN
  Drop Table OwnersPrava;
END IF

GO

create table OwnersPrava
(
  N integer primary key NOT NULL DEFAULT autoincrement,
  OwnerN integer,
  SpravkaN integer
)

GO

ALTER TABLE OwnersPrava
  ADD FOREIGN KEY "FK_OwnerN" ("OwnerN")
  REFERENCES Owners ("OwnerN") on delete cascade on update cascade
  
GO

ALTER TABLE OwnersPrava
  ADD FOREIGN KEY "FK_SpravkaN" ("SpravkaN")
  REFERENCES OwnersRelations ("N") on delete cascade on update cascade
  
GO

IF (EXISTS (select * from SYSTRIGGERS where LCase(trigname)=LCase('Trig_on_Owners_after_Insert'))) THEN
drop trigger Trig_on_Owners_after_Insert
END IF

GO

CREATE TRIGGER "Trig_on_Owners_after_Insert" after insert order 1 on
DBA.Owners
REFERENCING NEW AS Owners_new
for each row
begin
  IF (EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('OwnersPrava'))) THEN
     f1: for f1 as curs scroll cursor for
         select N as SprN
         from OwnersRelations
         where LCase(ColName) = LCase('__Options__')
      do
         insert into OwnersPrava (OwnerN, SpravkaN) values( Owners_new.OwnerN, SprN )
      end for;
  END IF
end
/*Край OwnersPrava*/

GO

insert into Owners values( 0, 'admin', 'admin', 0 )

GO

/*Налива в Owners всички договори на собственици*/

create variable N integer;

GO

set N = (select max(OwnerN)+1 from Owners);

GO

f1: for f1 as curs scroll cursor for
   select ContractNo
   from Contracts
   where GroupNo = 2 AND ContractNo<>0 AND ContractNo not in (select ContractN from Owners where OwnerN<>0)
   AND Active = 1
   order by ContractNo
do
   insert into Owners(OwnerN, Username, Password, ContractN)
               values( N, 'u'||ContractNo, 'p'||ContractNo, ContractNo );
   set N = N + 1;
end for;


GO

drop variable N;


//v.1 [14.07.2006] Created by Angel, EDIT [19.07.2006]