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

GO

CREATE VIEW v_Services(Account, ServiceDate, Service,
                       GuestName, Country, Agent,
                       PayedSum, Balance, AdminName,
                       Room, Currency, ExplanationColumn)

AS SELECT 
  g.GuestNum as Account,
  sc.DateCreditDt as ServiceDate,
//DATEFORMAT(sc.DateCreditDt,'DD.MM.YYYY') as ServiceDate,
  s.ServiceName as Service,
  g."Name" as GuestName,
  Countries.Description as Country,
  Contracts.ShortName as Agent,
  sc.PayedServices*sc.SumCredit as PayedSum,
  (sc.PayedServices-sc.NumberServices)*sc.SumCredit as Balance,
  Admin.ShortName as AdminName,
  ASL.LastRoom as Room,
  cur.Symbol as Currency,
  if(sc.PBXLogN is not null or sc.KodAdmin=1002) then 'PBX'
    else if(select count(A.AdminNo) from "dba".Admin as A where A.AdminGroup=0 and A.AdminNo=sc.KodAdmin)>0 then 'POS'
         else if (sc.SPaReservationFk<>-1 and sc.SpaTransferFlag=1) then 'ÑÏÀ'
              else null
              endif
         endif
  endif as ExplanationColumn
FROM "dba".ServiceCredits as sc,
      Services as s,
      Contracts LEFT OUTER JOIN Guests as g on Contracts.ContractNo=g.Contract,
      Countries,
      Admin,
      Currencies as cur,
      v_ArriveStayLeaveGuestBook as ASL
WHERE sc.Service=s.ServiceNo
      and sc.GuestNo=g.GuestNum
      and Contracts.GroupNo=any(select distinct(showtype) from "dba".GrContracts where GrNo=-1)
      and g.CountryArrive=Countries.CountryKod
      and sc.KodAdmin=Admin.AdminNo
      and ASL.GuestNum=g.GuestNum
      and sc.KodCurrency=cur.KodCurrency
      and ((sc.SPaReservationFk=-1) or (sc.SPaReservationFk<>-1 and sc.SpaTransferFlag=1))
/*Opravia filtriraneto na data na spravkata*/
//[17.03.2005] Safa, optimizatsia

//[25.07.2006] Yanko, mahnah DATEFORMAT za filtrirane po ServiceData