comm.ActiveConnection = ds.cn
comm.CommandType = adCmdText
comm.CommandTimeout =  0 

comm.CommandText = "exec GetBonus_New 'bonus" + mag + "', '" + id_grups + "', '" + CStr(Format(Me.DatePicker1.value, "yyyy-mm-dd")) + "', '" + CStr(Format(Me.DatePicker2.value, "yyyy-mm-dd")) + "'"

Set rs = comm.Execute

ALTER   procedure GetBonus_New
@bonus as nvarchar( 100 ),
@id_gr as nvarchar( 200 ),
@d1 as nvarchar( 10 ),
@d2 as nvarchar( 10 ) 
as

declare @intb as nvarchar( 4000 )
declare @tb as nvarchar( 20 )
declare @pos as int



create table #SelectGruppa 
(
id_grup int
)


set @pos= 1 
while @pos<> 0 
begin
   set @pos=charindex(',',@id_gr)
   if @pos<> 0 
	begin
	   set @tb=left(@id_gr,@pos- 1 )
	   insert into #SelectGruppa select id from GetChild(@tb) 	
	   set @id_gr=right(@id_gr,len(@id_gr)-@pos)
	end
   else
	begin
	   	set @tb=@id_gr
		insert into #SelectGruppa select id from GetChild(@tb) 	
        end
end



set @pos= 1 
create table #Tab 
(
id_tov int,
naim varchar( 50 ),
prodsum real,
prodkol real,
prib_rub real,
senz_prkol real,
senr_prkol real,
id_post int,
id_proizv int,
senz real,
senr real,
ost real,
dat datetime
)


while @pos<> 0 
begin
set @pos=charindex(',',@bonus)
if @pos<> 0 
  begin
   set @tb=left(@bonus,@pos- 1 )
   set @bonus=right(@bonus,len(@bonus)-@pos)
  end
else
  begin
    set @tb=@bonus
  end
set @intb=
'insert into #Tab (id_tov, naim, prodsum, prodkol, prib_rub, Senz_prkol, Senr_prkol, Id_post, Id_proizv, senz, senr, ost, dat)
    select a.id_tov, 
    naim, 
    prodsum, 
    prodkol,
    prib_rub,
    senz*prodkol as Senz_prkol, 
    senr*prodkol as Senr_prkol,
    (select id_post from tovar where id=a.id_tov) as Id_post,
    (select id_proizv from tovar where id=a.id_tov) as Id_proizv,
     senz,
     senr,
     ost,
     dat from '+@tb+' a
         where (dat between '''+@d1+''' and '''+@d2+''')
            and id_tov in (select id from tovar where id_grup in (select * from #SelectGruppa ))' 
exec (@intb)
end

select b.id_tov,
    naim,
    sum(prodsum) as Prodsum, 
    sum(prodkol) as Prodkol,
    sum(prib_rub) as Prib,
    sum(Senz_prkol) as Sum_senz,
    sum(Senr_prkol) as Sum_senr,
    Id_post, Id_proizv, 
    (select top  1  senz from #Tab where id_tov=b.id_tov order by dat desc) as Max_Senz,
    (select top  1  senr from #Tab where id_tov=b.id_tov order by dat desc) as Max_Senr,
    (select sum(ost) from #Tab where id_tov=b.id_tov and dat=@d2) as ost
    from #Tab
b group by id_tov, naim, Id_post, Id_proizv



ALTER  FUNCTION GetChild (@Id int) -- Идентификатор элемента дерева об'ектов
RETURNS 
@Tree Table (
	Id int,
	IdParent int,
	Name varchar( 50 ),
	Level int)
AS BEGIN 

Declare 
@Level int
if @Id is NULL RETURN select @Level= 0 
	insert into @Tree
	select @Id,  0 , Naim, @Level
		from Gruppa 
			where Id = @Id
	while  1  =  1  
	begin
	  insert into @Tree
		select 
			a.Id,
			b.Id,
			a.Naim,
			@Level+ 1 
		from Gruppa a, @Tree b
			 where a.Id_count = b.Id and
			 Level = @Level
if @@ROWCOUNT =  0 
	 break 
	select @Level=@Level+ 1 
end
RETURN END