Re: [Emerald] Daily Online Time Limited store procedure

( dan@dmn.com.au )
Thu, 18 Nov 1999 12:27:01 +0800

Find out the problem, RadiusNT will check the RadGetConfigs first, if it
return nothing then it will check RadGetATConfigs, I was thinking they will
check both, did not have enough attribute in the individual subaccount, so
the user can never login.

I modify the stored procedure as following, I can login and disconnect user
as desired.

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 */
select @Totaltime=isnull(@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(Rtrim(SUBSTRING(@AccountType,4,datalength(@AccountType)-3))
as numeric)*3600

/** Start of getting Session-Time-Out attribute **/
Declare @SessionTime numeric
select @SessionTime=24 * 3600-datepart(hh,getdate())*3600 +
datepart(mi,getdate())*60 + datepart(ss,getdate())

/* If have some time left today */
If @DayLimited >= @TotalTime
/** If it is midnight cross over and left over time is longer than
the time of day left **/
If @DayLimited-@TotalTime>@SessionTime
select @Sessiontime=@SessionTime+@DayLimited
else
select @SessionTime=@DayLimited-@TotalTime
Else
/** If user already over limited give it 2 minutes **/
select @SessionTime=120

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

/*This is the original statement */
select 27,'Session-Timeout',Cast(@SessionTime as
varchar(100)),cast(@SessionTime as int),1,0,0,0
UNION
/** Other Attributes for the account type, need to modified for every
ISP to the RadGetATConfigs **/
Select ra.RadAttributeID, Name, Data, Value, Type, rc.RadVendorID,
rc.RadVendorType, rc.RadCheck
From RadATConfigs rc, RadAttributes ra
Where ra.RadAttributeID=rc.RadAttributeID
AND rc.AccountType=@AccountType

End
Else
/*This is the original RadGetATConfigs 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

Regards,

Dan Tang
Network Operation
Domain Technology

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