IF (EXISTS (select proc_name from sysprocedure where proc_name='FillSpaPayedServices')) THEN
  DROP procedure FillSpaPayedServices
END IF

GO

CREATE PROCEDURE "DBA"."FillSpaPayedServices"(in year integer,in month integer)
begin
declare @CData integer;
declare @Broi integer;
declare @Total decimal(10,4);
declare @SqlStr varchar(300);
declare @SqlColumns varchar(300);
declare @SqlValues varchar(300);
declare @SqlHeader varchar(300);
declare @SqlHeaderValues varchar(300);
declare @Day integer;
declare @RowNumber integer;
declare @Otmeneni integer;
message '---FillSpaPayedServices---';
delete from SpaServByDays;
set @RowNumber=1;
for f as curs scroll cursor for
select ServiceNo as @ServiceNo,ServiceName as @ServiceName from Services where ServiceType=2
  and ServiceNo in
(select distinct MedicalService from SpaReservations/* where ReservetionType=2 and IfPayed=1*/) do
set @CData=ymd(year,month,1);
set @Total=0;
set @SqlColumns='';
set @SqlHeaderValues='';
set @Day=0;
set @SqlStr='insert into SpaServByDays(RowNumber,Procedures';
set @SqlValues='';
while month(@CData)=month loop
select count(*) into @Broi from SpaReservations where MedicalService=@ServiceNo AND BeginDate=@CData;
select count(*) into @Otmeneni from SpaReservations where MedicalService=@ServiceNo
  AND BeginDate=@CData and ReservetionType=3;
set @Broi=@Broi-@Otmeneni;
set @CData=days(date(@CData),1);
set @Day=@Day+1;
set @Total=@Total+@Broi;
set @SqlHeaderValues=@SqlHeaderValues||','||CAST(@Day as varchar);
set @SqlColumns=@SqlColumns||','||'A'+CAST(@Day as varchar);
set @SqlValues=@SqlValues||','||CAST(@Broi as varchar);
end loop;
set @RowNumber=@RowNumber+1;
set @SqlStr=@SqlStr||@SqlColumns;
set @SqlHeader=@SqlStr||',Total) values(1,''Процедури'''||@SqlHeaderValues||',''Общо'')';
set @SqlValues=CAST(@RowNumber as varchar)+','''||@ServiceName||''''||@SqlValues;
set @SqlStr=@SqlStr||',Total) values('||@SqlValues||','||@Total||')';
execute immediate @SqlStr;
end for;
end
//[v 11.11.2005] Jorko New procedure