IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_CanceledReservations')))
THEN Drop view v_CanceledReservations
END IF

GO

create view DBA.v_CanceledReservations as select R.ReserveNo,
    DateReserveDt=max(RP.FromDateDt),
    ReserveName=max(R.Name),
    Hora=(select COUNT(GuestNum) from DBA.Guests where Reservation = R.ReserveNo and Status = 0),
    Days=max(R.Days),
    ReserveNote=max("Left"(R.Note,200)),
    BusyCount=convert(varchar(250),DBA.GetResrTypesTxt(R.ReserveNo,RP.FromDateDt)),
    BusyRooms=DBA.GetResrRoomsTxt(R.ReserveNo,RP.FromDateDt),
    Phone=max(R.Phone),
    BoardPlanName=max(BP.BoardPlanName),
    ContractName=max(C.FullName),
    StatusName=RS.StatusName,
    CanceledKodAdmin=Admin.ShortName from
    dba.ReservePlan as RP,dba.Reserve as R,dba.BoardPlan as BP,dba.Contracts as C,dba.ReserveStatuses as RS,DBA.Admin where
    R.BoardPlanDef = BP.N and R.Status = 2 and RP.ReserveNo = R.ReserveNo and
    RP.FromDateDt >= bvrData1 and RP.FromDateDt <= bvrData2 and
    C.ContractNo = R.ContractNo and
    RS.ReserveStatusesN =* R.StatusType and
    Admin.AdminNo = R.CanceledKodAdmin
    group by R.ReserveNo,RP.FromDateDt,C.FullName,RS.StatusName,Admin.ShortName

	
GO

delete from relations where TableName='v_CanceledReservations'
go

insert into Relations
Values('v_CanceledReservations','__OPTIONS__','Анулирани резервации','__OPTIONS__','CanceledReservations','__OPTIONS__',1,(select max(N)+10 from Relations),'__OPTIONS__','','',0,0,'','','Printer.Orientation=1','',80,'',0,0,'',today());

Go

insert into Relations
Values('v_CanceledReservations','bvrData1','Анулирани резервации','От дата','CanceledReservations','bvrData1',2,(select max(N)+10 from Relations),'bvrData1','','',0,0,1,1,'','select Date(Now())',80,'',0,0,1,today());

Go

insert into Relations
Values('v_CanceledReservations','bvrData2','Анулирани резервации','До дата','CanceledReservations','bvrData2',2,(select max(N)+10 from Relations),'bvrData2','','',0,0,1,1,'','select Date(Now())',80,'',0,0,1,today());

Go

insert into Relations
Values('v_CanceledReservations','ReserveNo','Анулирани резервации','Номер','CanceledReservations','ReserveNo',2,(select max(N)+10 from Relations),'','','',2,2,'','','','',43,'',0,0,1,today());

Go

insert into Relations
Values('v_CanceledReservations','DateReserveDt','Анулирани резервации','Дата','CanceledReservations','DateReserveDt',2,(select max(N)+10 from Relations),'','','',0,1,'','','','',72,'',0,0,1,today());

Go

insert into Relations
Values('v_CanceledReservations','ReserveName','Анулирани резервации','Име','CanceledReservations','TipSdelka',2,(select max(N)+10 from Relations),'','','',0,0,'','','','',160,'',0,0,1,today());

Go

insert into Relations
Values('v_CanceledReservations','Hora','Анулирани резервации','Души','CanceledReservations','Hora',2,(select max(N)+10 from Relations),'','SUM(qrData.Души)','',2,0,'','','','',32,'',0,0,1,today());

Go

insert into Relations
Values('v_CanceledReservations','Days','Анулирани резервации','Дни','CanceledReservations','Days',2,(select max(N)+10 from Relations),'','','',2,0,'','','','',32,'',0,0,1,today());

Go

insert into Relations
Values('v_CanceledReservations','BoardPlanName','Анулирани резервации','Борд','CanceledReservations','BoardPlanName',2,(select max(N)+10 from Relations),'','','',2,0,'','','','',32,'',0,0,1,today());

Go

insert into Relations
Values('v_CanceledReservations','BusyCount','Анулирани резервации','Типове стаи','CanceledReservations','BusyCount',2,(select max(N)+10 from Relations),'','','',0,0,'','','','',100,'',0,1,1,today());

Go

insert into Relations
Values('v_CanceledReservations','BusyRooms','Анулирани резервации','Номера стаи','CanceledReservations','BusyRooms',2,(select max(N)+10 from Relations),'','','',0,0,'','','','',150,'',0,1,1,today());

Go

insert into Relations
Values('v_CanceledReservations','ReserveNote','Анулирани резервации','Бележка','CanceledReservations','ReserveNote',0,(select max(N)+10 from Relations),'','','',0,0,'','','','',200,'',0,1,1,today());

Go

insert into Relations
Values('v_CanceledReservations','Phone','Анулирани резервации','Телефон','CanceledReservations','Phone',0,(select max(N)+10 from Relations),'','','',0,0,'','','','',120,'',0,0,1,today());

Go

insert into Relations
Values('v_CanceledReservations','ContractName','Анулирани резервации','Договор','CanceledReservations','ContractName',2,(select max(N)+10 from Relations),'ContractName','','',0,0,'','','','',200,'',0,0,1,today());

Go

insert into Relations
Values('v_CanceledReservations','StatusName','Анулирани резервации','Статус резевация','CanceledReservations','StatusName',0,(select max(N)+10 from Relations),'','','',0,0,'','','','',100,'',0,0,1,today());

Go

insert into Relations
Values('v_CanceledReservations','CanceledKodAdmin','Анулирани резервации','Админ/амул.','CanceledReservations','CanceledKodAdmin',2,(select max(N)+10 from Relations),'','','',0,0,'','','','',100,'',0,0,1,today());

GO

for f1 as curs1 scroll cursor for
  select a.N as @Num, a.Name as @GrName
         from AdminGroups a do
    for f2 as curs2 scroll cursor for
      select Min(r.N) as @RelationsN, r.BgTableName as @Spravka
      from Relations r
      Group By r.BgTableName do
        if (not Exists(Select RelationN from InformPermission where RelationN=@RelationsN and AdminGroupNo=@Num)) then
          Insert into InformPermission (N, RelationN, AdminGroupNo, Stoinost) values((select max(N)+1 from InformPermission ), @RelationsN, @Num, 1);
        end if
    end for
end for;

//Keti - syzdava spravka Anulirani rezervacii,26.06.2008