[Emerald] Daily Online Time Limited store procedure

( dan@dmn.com.au )
Wed, 17 Nov 1999 19:04:18 +0800

Dale,
We want to implement a daily online limited for our user. RadGetConfigs is
for additional Radius attributes for particular subaccounts, if we modify it
as follow, will it work? (Can not use RadGetATConfigs, because it does not
pass username).

In our Database the particular account type would be DAYx, the x is the hour
limit in a day, any body should be able to change to monthly time limit type
of account as well.

CREATE PROCEDURE RadGetConfigs @AccountID int AS
declare @Day varchar(10)
select @Day='Day'

/* Get user account type */
Declare @AccountType varchar(10)
declare curDayLimited cursor
for select AccountType
from SubAccounts where AccountID=@AccountID
open curDayLimited
Fetch next from curDayLimited into @AccountType
close curdaylimited
deallocate curdaylimited

If CharIndex(@Day,@AccountTYpe)> 0
Begin

/* Start of getting the user's total online time today */
declare curTime cursor
for select Sum(Case
when datepart(hh,ca.calldate)*3600 +
datepart(mi,ca.calldate)*60 + datepart(ss,ca.calldate)> ca.acctsessiontime
then ca.acctsessiontime
else datepart(hh,ca.calldate)*3600 +
datepart(mi,ca.calldate)*60 + datepart(ss,ca.calldate)
end)
from calls ca, subaccounts sub
where ca.username=sub.login
and Sub.AccountID=@AccountID
and ca.acctStatusType=2
and Convert(char(8),ca.calldate,112)=Convert(char(8),getdate(),112)

declare @TotalTime numeric
open curtime
fetch next from curtime into @totaltime
/* If there is no record for today set it to 0 */
If @@Fetch_Status<>0
select @Totaltime=0
close curtime
deallocate curtime
/*** End of find the users total online time today ****/

declare @Daylimited numeric
/** The Name of account type will be DAY2, DAY4,etc, the number at the end
means the hour limit for the day **/
Select
@Daylimited=Cast(Ltrim(Rtrim(SUBSTRING(@AccountType,4,datalength(@AccountTyp
e)-3))) as numeric)*60*60

Declare @SessionTime numeric
If @DayLimited >= @TotalTime
select @SessionTime=@DayLimited-@TotalTime
Else
/** If user already over limited give it 10 minutes **/
select @SessionTime=600

/** End of getting the session-time out attribute */

/*This is the original statement */
Select ra.RadAttributeID, Name, Data, Value, Type, rc.RadVendorID,
rc.RadVendorType, rc.RadCheck
From RadConfigs rc, RadAttributes ra
Where ra.RadAttributeID=rc.RadAttributeID AND rc.AccountID=@AccountID
UNION
select 46,'Acct-Session-Time',Cast(@SessionTime as
varchar(100)),@SessionTime,1,0,0,0
End

Else
/*This is the original statement */
Select ra.RadAttributeID, Name, Data, Value, Type, rc.RadVendorID,
rc.RadVendorType, rc.RadCheck
From RadConfigs rc, RadAttributes ra
Where ra.RadAttributeID=rc.RadAttributeID AND rc.AccountID=@AccountID

For more information about this list (including removal) go to:
http://www.iea-software.com/support/maillists/liststart