Because outside USA, the local call is not free(25 cents in Australia for
every local calls.), and we do not want the user to assume that they drop
out at midnight because our service is not reliable.
I redo the stored procedure and ran in QueryAnalyzer, it returns the same
result set as if I added Rad attribute to individual subaccount but user
will not getting authenticated and no RadLog been insert.
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
/** 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 it is midnight cross over **/
If @DayLimited-@TotalTime>@SessionTime
select @Sessiontime=@SessionTime+@DayLimited-@TotalTime
else
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