How to age out subscribers accounts?
-
I have a web classifieds system that I wrote for a client and I am having some trouble with the aging out of accounts. THis system is built with ASP and interfaces with MSSQL Server 7.0. What happens is this, a person subscribes for a period of 90 days. They pay their fee and get the run of the system for 90 days. When the 90 days are up they are no longer allowed access. They can still log in, but they are only given access to the pay area so they can re-up their subscription. If they do not re-up then after a while their account goes away entirely. So I have three states of user: Non-Subscriber Subscriber Expired Subscriber Non-subscribers become subscribers by entering their user info and paying, that is simple enough and it works fine. Subscribers last for 90 days and then they become Expired Subscribers. (They get flagged in the database as expired) Expired Subscribers last for another 30 days and then they go away. (They are deleted from the database) So my question: What is the most efficient way to test for the age of the accounts? (I am comfortable with databases, but I do not claim to be an expert so I am curious about what you guys think SQL wise) Also, how would you guys get the system to run the check routine regularly? Say once a day. This is an ASP based system and it is hosted on a virtual hosting provider so I cannot just set up a scheduled job to run on the server like I normally would. I am going to have to find a tricky way to do it. TIA! Jason Jystad Cito Technologies www.citotech.net >------------------------------------------------< "Luckily," he went on, "you have come to exactly the right place with your interesting problem, for there is no such word as 'impossible' in my dictionary. In fact," he added, brandishing the abused book, "everything between 'herring' and 'marmalade' seems to be missing." -- Dirk Gently (Douglas Adams) >------------------------------------------------<
-
I have a web classifieds system that I wrote for a client and I am having some trouble with the aging out of accounts. THis system is built with ASP and interfaces with MSSQL Server 7.0. What happens is this, a person subscribes for a period of 90 days. They pay their fee and get the run of the system for 90 days. When the 90 days are up they are no longer allowed access. They can still log in, but they are only given access to the pay area so they can re-up their subscription. If they do not re-up then after a while their account goes away entirely. So I have three states of user: Non-Subscriber Subscriber Expired Subscriber Non-subscribers become subscribers by entering their user info and paying, that is simple enough and it works fine. Subscribers last for 90 days and then they become Expired Subscribers. (They get flagged in the database as expired) Expired Subscribers last for another 30 days and then they go away. (They are deleted from the database) So my question: What is the most efficient way to test for the age of the accounts? (I am comfortable with databases, but I do not claim to be an expert so I am curious about what you guys think SQL wise) Also, how would you guys get the system to run the check routine regularly? Say once a day. This is an ASP based system and it is hosted on a virtual hosting provider so I cannot just set up a scheduled job to run on the server like I normally would. I am going to have to find a tricky way to do it. TIA! Jason Jystad Cito Technologies www.citotech.net >------------------------------------------------< "Luckily," he went on, "you have come to exactly the right place with your interesting problem, for there is no such word as 'impossible' in my dictionary. In fact," he added, brandishing the abused book, "everything between 'herring' and 'marmalade' seems to be missing." -- Dirk Gently (Douglas Adams) >------------------------------------------------<
Hi I just store the date of my last check in a simple text file. When a user logs in, I compare the dates. If my check is out of date, I run some stored procedures and update the text file. Not the best solution ever, but it works and you don't have to update your db manually. :) HTH Regards, Wanderley
-
Hi I just store the date of my last check in a simple text file. When a user logs in, I compare the dates. If my check is out of date, I run some stored procedures and update the text file. Not the best solution ever, but it works and you don't have to update your db manually. :) HTH Regards, Wanderley
Does running the stored procedures slow down the user's login at all? In other words, does the stored procedure have to finish before the asp continues? Or can I just trigger the stored procedure and then continue on with login and browsing, from the script perspective. I have done only limited stored procedure work, so fogive me if I am being dense. I assume the db server would happily go off and run the stored proc while the user continued with his session, but I am unsure. If the user can continue browsing the site after logging in without any percieved delay, then this would seem to be the solution I am looking for. Thanks Jason Jason Jystad Cito Technologies www.citotech.net >------------------------------------------------< "Luckily," he went on, "you have come to exactly the right place with your interesting problem, for there is no such word as 'impossible' in my dictionary. In fact," he added, brandishing the abused book, "everything between 'herring' and 'marmalade' seems to be missing." -- Dirk Gently (Douglas Adams) >------------------------------------------------<
-
Does running the stored procedures slow down the user's login at all? In other words, does the stored procedure have to finish before the asp continues? Or can I just trigger the stored procedure and then continue on with login and browsing, from the script perspective. I have done only limited stored procedure work, so fogive me if I am being dense. I assume the db server would happily go off and run the stored proc while the user continued with his session, but I am unsure. If the user can continue browsing the site after logging in without any percieved delay, then this would seem to be the solution I am looking for. Thanks Jason Jason Jystad Cito Technologies www.citotech.net >------------------------------------------------< "Luckily," he went on, "you have come to exactly the right place with your interesting problem, for there is no such word as 'impossible' in my dictionary. In fact," he added, brandishing the abused book, "everything between 'herring' and 'marmalade' seems to be missing." -- Dirk Gently (Douglas Adams) >------------------------------------------------<
Hi Does running the stored procedures slow down the user's login at all? In my case, no - it will depend on how many users you have and if your table is properly indexed. You can run some tests against your db to check how long does it take. Remember: the SP will run only once a day. Even if there's a little delay, it will be just for the first user of the day (and it could be you, if you wake up early :)) does the stored procedure have to finish before the asp continues? I would say yes, because you may check your data against an out of date table, in case the SP is still running in background. Regards, Wanderley
-
Hi Does running the stored procedures slow down the user's login at all? In my case, no - it will depend on how many users you have and if your table is properly indexed. You can run some tests against your db to check how long does it take. Remember: the SP will run only once a day. Even if there's a little delay, it will be just for the first user of the day (and it could be you, if you wake up early :)) does the stored procedure have to finish before the asp continues? I would say yes, because you may check your data against an out of date table, in case the SP is still running in background. Regards, Wanderley
Thanks for the help! You gave me a couple of ideas I hadn't thought of before. That was what I needed, some fresh angles on the problem. I wanted to do the check on session start, but I didn't want to slow down all the logins too much. It totally hadn't occured to me to flag myself with a file so it didn't always run. (Slaps self on forehead :rolleyes: ) Sometimes you just get too involved in a problem and forget the simple solutions. That coupled with moving the code into an SP should do the trick nicely. Thanks again, Jason Jystad Cito Technologies www.citotech.net >------------------------------------------------< "Luckily," he went on, "you have come to exactly the right place with your interesting problem, for there is no such word as 'impossible' in my dictionary. In fact," he added, brandishing the abused book, "everything between 'herring' and 'marmalade' seems to be missing." -- Dirk Gently (Douglas Adams) >------------------------------------------------<