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