right here goes,

automated invoicing? i need a bit advice or ideas on which is the best way to go for automated billing.

Basically i would like to invoice account holders per record every 30 days. So, generate a list of records details every 30 days for each account holder and display them on printable page table or similar.

Ive thought of using a archive script to move the records from db when 30 days old cos they only get charged when record expires, but im just not sure this would be efficient enough

Any ideas, tips, or direction pointing would be appreciated greatly <img src=../images/dmxzone/forum/icon_smile_big.gif border=0 align=middle>

Thanks again people

Could you explain a little further please?
What happens to the records after 30 days?

Just thinking you could set a lastPaymentDate field, run a job in the database every night that checks for records
WHERE lastPaymentDate = DATEADD(d, 30, GETDATE())
which would pick up all account holders whose records were last paid for 30 days ago - email them, or whatever, to remind them that their record now requires repayment and update their records when you get confirmation back from your payment gateway (SET lastPaymentDate = GETDATE())
Then the flag lastPaymentDate will let you exclude all records that haven't been paid for, but you don't need to delete the records so your account holders can renew the record at a later date if required.

But I'm making assumptions about what you're doing, what the records are and how you're taking payments - so if I'm talking rubbish that's not relevant... ignore me

HTH


Live the life you love
Love the life you live

Simon

[DWMX 2004]|[SQL]|[ASP/VBScript]|[XP-Pro]

Quick reply simon cheers,

right, account holders have access to upload page via login, they can add as little or as many records as they like. They want to be billed monthly through invoice depending on number of adverts placed.
All ads are valid for 30 days then expire (well, they are moved to new table at moment away from
visitors eyes) so im looking for a simple way to show all their ads on an invoice table added since last invoice ?????????

This getting hard to explain <img src=../images/dmxzone/forum/icon_smile_approve.gif border=0 align=middle>

Im thinking of setting up a invoice date on account sign up, then every 30 days or calender month create a list of active records, show all in table, then it is gonna get printed and posted like telephone bill for example..... something along them lines anyway

Just trying to think if there is a better way to go about this or just run away quickly

<img src=../images/dmxzone/forum/icon_smile_big.gif border=0 align=middle>

Yeah I think you're right; date stamping your records seems like the best way to go.

On INSERT add the current date (GETDATE()) as a default value for the dateAdded field, (good for auditing purposes too)
At the same time SET their validTo field (DATEADD(d, 30, GETDATE()), so in 30 days the records automatically disable themselves. I think its worthwhile having both fields, the 1st so you always know when they added the record in case of any disputes and the 2nd as a rolling value.

When building the recordset to show the public exclude those records that are no longer valid with
WHERE validTo &gt;= GETDATE()
Or you might stick in a flag on the records isActive and toggle that with a routine job in the database that runs every night and turns off any records that are no longer valid - which might be a quicker way to code that... you'd have to test based on your set up though.

To show records added since their last invoice then WHERE dateAdded &gt; DATEADD(d, -30, GETDATE()) will give you all records added in the last 30 days, which should be after their previous invoice. Though you might also wish to consider updating the records to show they've been invoiced so you don't invoice them twice for the same record.

That any help?

Live the life you love
Love the life you live

Simon

[DWMX 2004]|[SQL]|[ASP/VBScript]|[XP-Pro]

yep, thanks alot

i think thats the best way to go given the requirements i have for the ways and means of paying etc.
Thanks again, best get crackin.

Cheers simon much appreciated <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>