RE: [Emerald] problem with over limit field

wilson@dagupan.com
Sat, 8 Jul 2000 14:59:22 +0800

Peter, it still didn't work. But since you pointed me in the right
direction, i took a look at the radgetcacheusers stored procedure.

Can you verify if what i did is correct:

the original line states:

overlimit=case when (ma.overlimit> 0 and ma.overlimit > ma.balance) then 1
else 0 end

i think should be:

overlimit=case when (ma.overlimit > 0 and ma.overlimit < ma.balance) then 1
else 0 end

i modified my stored procedure using the latest radgetcacheusers.sql , and
if my hunch is right, this should be the final procedure:

CREATE PROCEDURE RadGetCacheUsers @date DATETIME, @flag TINYINT AS
IF (@flag = 1)
BEGIN
SELECT sa.AccountID, sa.Login, sa.Password, d.MailDomain, sa.AccountType,
sa.LoginLimit, sa.TimeLeft,
MasterExpire=CASE WHEN maExpireDate IS NULL THEN NULL ELSE
DATEDIFF(Day,'19700101',DATEADD(Day,ma.Extension+ma.OverDue,maExpireDate))
END,
SubExpire=CASE WHEN saExpireDate IS NULL THEN NULL ELSE
DATEDIFF(Day,'19700101',DATEADD(Day,sa.Extension,saExpireDate)) END,
OverLimit=CASE WHEN (ma.OverLimit > 0 AND ma.OverLimit < ma.Balance) THEN 1
ELSE 0 END
FROM SubAccounts sa, MasterAccounts ma, Domains d, Groups g
WHERE sa.CustomerID = ma.CustomerID
AND d.DomainID = g.DomainID
AND ma.GroupID = g.GroupID
AND sa.Active <> 0
AND ma.Active <> 0
AND sa.Login <> ''
AND (sa.LastModifyDate > @date OR ma.LastModifyDate > @date)
UNION ALL
SELECT sa.AccountID, sa.Email, sa.Password, d.MailDomain, sa.AccountType,
sa.LoginLimit, sa.TimeLeft,
MasterExpire=CASE WHEN maExpireDate IS NULL THEN NULL ELSE
DATEDIFF(Day,'19700101',DATEADD(Day,ma.Extension+ma.OverDue,maExpireDate))
END,
SubExpire=CASE WHEN saExpireDate IS NULL THEN NULL ELSE
DATEDIFF(Day,'19700101',DATEADD(Day,sa.Extension,saExpireDate)) END,
OverLimit=CASE WHEN (ma.OverLimit > 0 AND ma.OverLimit < ma.Balance) THEN 1
ELSE 0 END
FROM SubAccounts sa, MasterAccounts ma, Domains d, Groups g
WHERE sa.CustomerID = ma.CustomerID
AND d.DomainID = g.DomainID
AND ma.GroupID = g.GroupID
AND sa.Active <> 0
AND ma.Active <> 0
AND sa.Email <> ''
AND (sa.LastModifyDate > @date OR ma.LastModifyDate > @date)
END ELSE IF (@flag = 2)
BEGIN
SELECT sa.AccountID, sa.Login, sa.Password, d.MailDomain, sa.AccountType,
sa.LoginLimit, sa.TimeLeft,
MasterExpire=CASE WHEN maExpireDate IS NULL THEN NULL ELSE
DATEDIFF(Day,'19700101',DATEADD(Day,ma.Extension+ma.OverDue,maExpireDate))
END,
SubExpire=CASE WHEN saExpireDate IS NULL THEN NULL ELSE
DATEDIFF(Day,'19700101',DATEADD(Day,sa.Extension,saExpireDate)) END,
OverLimit=CASE WHEN (ma.OverLimit > 0 AND ma.OverLimit < ma.Balance) THEN 1
ELSE 0 END
FROM SubAccounts sa, MasterAccounts ma, Domains d, Groups g
WHERE sa.CustomerID = ma.CustomerID
AND d.DomainID = g.DomainID
AND ma.GroupID = g.GroupID
AND sa.Active <> 0
AND ma.Active <> 0
AND sa.Login <> ''
AND (sa.LastUsed > @date OR sa.LastUsed IS NULL)
UNION ALL
SELECT sa.AccountID, sa.Email, sa.Password, d.MailDomain, sa.AccountType,
sa.LoginLimit, sa.TimeLeft,
MasterExpire=CASE WHEN maExpireDate IS NULL THEN NULL ELSE
DATEDIFF(Day,'19700101',DATEADD(Day,ma.Extension+ma.OverDue,maExpireDate))
END,
SubExpire=CASE WHEN saExpireDate IS NULL THEN NULL ELSE
DATEDIFF(Day,'19700101',DATEADD(Day,sa.Extension,saExpireDate)) END,
OverLimit=CASE WHEN (ma.OverLimit > 0 AND ma.OverLimit < ma.Balance) THEN 1
ELSE 0 END
FROM SubAccounts sa, MasterAccounts ma, Domains d, Groups g
WHERE sa.CustomerID = ma.CustomerID
AND d.DomainID = g.DomainID
AND ma.GroupID = g.GroupID
AND sa.Active <> 0
AND ma.Active <> 0
AND sa.Email <> ''
AND (sa.LastUsed > @date OR sa.LastUsed IS NULL)
END

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