if exists (select 1 from sysobjects where type = 'P' and name = 'get_out')
drop proc get_out
go

/*
	get_out procedure
*/

create proc get_out 
	@limit int = 1000 -- max count of rows expected
as
begin
begin transaction

create table #tmp (
	id int identity,
	mid integer,
	Status bit,                      
	Phone varchar(50),                     
	Message varbinary(255),                    
	SN varchar(50),                     
	Validity datetime,
	Customer numeric(18,0),                     
	SMSCId numeric(18,0),
	SMSCSubmitTime datetime,
	Condition int,
	nrows int,
	SMSClientID integer
	)
if @@error <> 0
begin
	raiserror ('get_out: Error while creating temporary table', 16, 1)
	goto FAILURE
end

declare
	@quan int,	
	@weight_sum numeric(10, 5),
	@coeff numeric(10, 5)

select @quan = count(*)
from 
	MESSAGEPOOL om,
	SMSClients s,
	Customers c
where 
	s.is_xon = 1
and	c.smsclient_id = s.smsclient_id
and	om.customer_id = c.customer_id
and	om.status = 0

if @quan > @limit select @quan = @limit
	
select @weight_sum = sum(c.weight)
from 
	Customers c,
	SMSClients s
where 
	s.is_xon = 1
and	c.smsclient_id = s.smsclient_id
and	exists (select 1 from MESSAGEPOOL om where om.customer_id = c.customer_id and Status = 0)

select @coeff = convert(numeric(10,5), @quan) / @weight_sum

insert into #tmp ( 
		mid,
		Status,                      
		Phone,                     
		Message,                    
		SN,                     
		Validity,
		Customer,                     
		SMSCId,
		SMSCSubmitTime,
		Condition,
		nrows,
		SMSClientID
	) select 
		om.Id,
		om.Status,
		om.Phone,
		om.Message,
		c.ServiceNumber,
		dateadd(mi, convert(integer, om.Validity), om.Timestamp),
		c.customer_id,
		null,
		null,
		om.condition,
		c.weight * @coeff,
		c.smsclient_id
	from 
		MESSAGEPOOL om,
		SMSCLients s,
		Customers c
	where 
		om.status = 0
	and	om.customer_id = c.customer_id
	and	c.smsclient_id = s.smsclient_id
	and	s.is_xon = 1
	order by c.customer_id, om.TimeStamp
if @@error <> 0
begin
	raiserror ('get_out: Error while inserting into temporary table', 16, 1)
	goto FAILURE
end

set rowcount @quan

insert into wgClient_Loaded_Records
select 
	mid,
	Status,                      
	Phone,                     
	Message,                    
	SN,                     
	Validity,
	SMSClientID as Customer,
	SMSCId,
	SMSCSubmitTime,
	Condition  
from 
	#tmp t
where
	t.id between 
	(select min(t1.id) from #tmp t1 where t.Customer = t1.Customer) and
	(select min(t1.id) + min(t1.nrows) from #tmp t1 where t.Customer = t1.Customer) - 1
	order by t.nrows desc, t.Validity

select @quan = (@quan - @@rowcount)

if @quan > 0
begin
	set rowcount @quan

	insert into wgClient_Loaded_Records
	select 
		mid,
		Status,                      
		Phone,                     
		Message,                    
		SN,                     
		Validity,
		Customer,                     
		SMSCId,
		SMSCSubmitTime,
		Condition  
	from 
		#tmp t
	where
		not exists (select 1 from wgClient_Loaded_Records lr where lr.Id = t.mid)
		order by t.nrows
end

set rowcount 0

update MESSAGEPOOL set status = 1 from MESSAGEPOOL om, wgClient_Loaded_Records lr where om.Id = lr.Id

drop table #tmp

OK:
commit transaction
return 0

FAILURE:
rollback transaction
return 1
end