3

I'm currently going to write something to automatically create invoices with cronjobs by using PHP and timestamps.

I have a, for me, well-considered idea of how to solve it, but I want to ask the community if someone may see errors in reasoning and possible problems with my solution.

I'm trying to describe my idea as detailed as possible so everyone can follow my way of thinking:

In General there are 4 types of invoices:

  1. Paid yearly
  2. Paid semiyearly
  3. Paid quarterly
  4. Paid monthly

Purchased products are saved in a SQL database with the billing cycle:

  • ID of User
  • Product ID
  • Billing Cycle
  • Last Due Date

Now there is a cronjob that runs once a day to check if it should create a new invoice for each purchased product. In the row Last Due Date I save the timestamp of the first date to pay when it's created.

A code I already wrote calculates the time that has gone by since the Last Due Date timestamp and outputs something like this:

  • Timestamp is in past or in future
  • Month gone by
  • Days gone by

Now my rules for creating a new invoice are:

  1. Paid yearly

    if ( Timestamp is in past = true AND Month gone by = 11 AND Days gone by >= 20 )

    then ( create a new invoice and set "Last Due Date" to time() )

  2. Paid semiyearly

    if ( Timestamp is in past = true AND Month gone by = 5 AND Days gone by >= 20 )

    then ( create a new invoice and set "Last Due Date" to time() )

  3. Paid quarterly

    if ( Timestamp is in past = true AND Month gone by = 3 AND Days gone by >= 20 )

    then ( create a new invoice and set "Last Due Date" to time() )

  4. Paid monthly

    if ( Timestamp is in past = true AND Month gone by = 0 AND Days gone by >= 20 )

    then ( create a new invoice and set "Last Due Date" to time() )

As you can see a new invoice would be created ~10 days before date of payment and the timestamp in Last Due Date is set to the current time, so when the cronjob checks back the next day no invoice will be created for this purchased product.

My question is if this is an appropriate way of solving this and if you can see any errors in reasoning or problems that may occur?

gnat
  • 21,442
  • 29
  • 112
  • 288
Fabian
  • 133
  • 6

3 Answers3

4

The biggest thing that I see is missing is keeping track of what the user has actually paid. Some people pay an amount more or less than the amount due, so some may have a balance due from the last invoice while others may have pre-paid for several periods ahead.

EDIT: Based upon your comment, I see this is handled seperately, great!

The other thing I see is the way you are handling the TIMESTAMP field. For example:

Paid monthly

if ( Timestamp is in past = true AND Month gone by = 0 AND Days gone by >= 20 )

then ( create a new invoice and set "Last Due Date" to time() )

Suppose I initially signed up on 1/1/2012, then Timestamp starts off with that date. Assuming I pay monthly, this will mean you generate an invoice on 1/20/2012 and set the Timestamp to 1/20/2012. Does this mean you generate an invoice every 20 days rather than once per month? In other words will you generate an invoice on 2/9/2012 (20 days after 1/20/2012)?

The point is that the next invoice should be generated based upon the end date of the current billing period, not the date the invoice was generated.

Currently you "set "Last Due Date" to time()", perhaps you want to set Last Due Date to the first day of the next billing period? So for example when generating monthly invoices on 1/20/2012 you would change 1/1/2012 (current value in DB) to 2/1/2012. You don't state which database you are using, but many have built-in functions to add 1 month, quarter, year etc.

JonnyBoats
  • 1,783
  • 13
  • 11
  • In general the payment cycle is fixed, so people can't pay less or more. But how to solve this would be no problem for me. I would check what the user has to pay against the user's balance. I just wanted to know if handeling timestamps and invoices this way is an appropriate way of solving this? – Fabian Jan 20 '12 at 13:44
  • Thanks for your edit! I see that there's a mistake how I wanted to handle this. You're right in my case an invoice will be created every 20 days, and that shouldn't be. I want to create the invoice ~10 days before payment date but the payment date should be (in case of monthly payment) 1/1/2012, 2/1/2012, 3/1/2012 and so on.. Do you have any advice for me how to manage this? – Fabian Jan 20 '12 at 14:25
  • I could check how much days the current month has and update "Last Due Date" as "time() + (current month days - 20 days)". Then the next time the invoice will create is 1 month later (if billing cycle is monthly) or 4 month later (if billing cycle is quarterly) but ~10 days before due date. This should work as I'm checking that "Last Due Date" is in past or future. Or am I missing something? – Fabian Jan 20 '12 at 14:52
  • Fabian - yes, you are missing something. Better to just add 1 month, quarter or year to "Last Due Date". – JonnyBoats Jan 20 '12 at 15:02
  • Just noticed your edit! So I should just set "Last Due Date" to "current value + 1 month" (monthly) or "current value + 1 quarter" (quarterly) and so on and then invoices will be created in the right circle but ~10 days before payment date, right? – Fabian Jan 20 '12 at 15:04
  • 1
    Fabian - Yes, that is how I see it. This guarantees that the dates always fall on the correct anniversary of initial signup, even if you are delayed in running the invoices because of a holiday, system problem or whatever. Also better to use standard date routines for this rather that coding your own. – JonnyBoats Jan 20 '12 at 15:08
  • Just one more thing: now I have to use: if ( Timestamp is in **future** = true AND **Month left** = 0 AND **Days left <=** 10 ) right? – Fabian Jan 20 '12 at 15:24
  • @Fabian May I know where the product quantity is stored. Is it always going to be one? – Ubermensch Jan 20 '12 at 15:28
  • @Ubermensch Every purchased product has it's own entry. Every user may have about 1 - 10 products (1 main account + some addons) and each product has it's "own billing cycle". Or have you got any suggestions for me? – Fabian Jan 20 '12 at 15:35
  • 1
    @Fabian Is this a real world project and if it is, I would give you an update tomorrow – Ubermensch Jan 20 '12 at 15:41
  • @Ubermensch Yes, it's a real world project I'm writing for me. – Fabian Jan 20 '12 at 15:45
  • @Ubermensch Thank you, I'll check back tomorrow!:) – Fabian Jan 20 '12 at 15:57
1

If your goal is to bill daily, not sure you have a choice. You have 365 billing periods per year. With automation, this isn't a problem.

Seems like your method for creating invoices should work (good catch by @jonnyboats to not use the date the bills are created as the bill date). Since you're saving the Last Due Date, if you ever have to make a correction to an invoice (especially the date), you have to make sure this date is updated after any adjustment. You could query this date based on the last invoice date for this client's item in your invoices, so any adjustment would be considered. Your method improves performance.

Give yourself the ability to set a Run Date parameter (default to time() ). If last night's run failed, you want the ability to run it manually by setting the time to yesterday. Weekends and holidays have their own set of issues.

JeffO
  • 36,816
  • 2
  • 57
  • 124
1

Problem Definition

Invoices are to generated based on a time cycle. Data is stored in a table,say To Invoice,with the fields UserID, ProductID, Billing Cycle and Last Due Date. Based on the billing cycle field and the last due date timestamp, invoices are to be generated daily and in advance(10 days).

My assumptions

  • Last Due Date is a timestamp that's generated at the time of puchase of a product based on the billing cycle. Thus if the time when the data is generated is 20th JAN 2012 and the billing cycle is monthly then last due date would be ~20th FEB 2012
  • For each row in the To Invoice table, there is a corresponding table linked to this table that contains product qty and value, say Product. Hopefully, there must also be tables that contains billing address and other information, say Supplier.
  • Invoices are to generated daily
  • Invoices are to be generated 10 days before the due date
  • Each row in the To Invoice table is to have a separate invoice
  • We aren't going to talk about payments

Proposed Solution

  • A cron job is run.
  • This job scans the To Invoice table, compares the Last due date with the present date and outputs timestamp status(past or future), months and days and based on this data and the billing cycle,decides whether invoice is to be generated or not
  • Invoice is then generated based on the data in Product and Supplier tables

This is my understanding of your question. If this is the actual requirement, then there is a major flaw in the design. There is no explicit way to track whether a product has been invoiced or not. Of course by principle, we can always compare the due date with the present time to get a list of products not invoiced but practically it isn't possible because to make this possible, the cron job must be run everyday at the specified time without a failure and the job must create all invoices without interruption. Both of this are virtually impossible. Also, a second flaw is that the invoices cannot be altered easily with this design. A lot of invoices can be canceled(in real world) and with the existing design, you need to manually update the timestamp of those products that need to be invoiced again. Thirdly, though not a flaw, this design doesn't seem to be easily extensible. Lets assume that an another cycle bimonthly is added and a few of the products that are to be invoiced quartely are to be invoiced bimonthly. Then, a whole lot of calculations need to de done to make changes.

Based on the above observations, I suggest the following solution

  • Instead of saving the due date in the To Invoice table, save the time at which the entry is generated (the time of purchase), lets call this product timestamp
  • Add a Boolean Filed isInvoiced to keep track of whether the product is invoiced or not with the default value False
  • Now, the present time would be compared with the product timestamp and together with the billing cycle and if has to be invoiced, then it would be invoiced and the field isInvoiced is updated as True.
  • In case, the invoice is canceled just change the isInvoiced field to False, the program would automatically generate an invoice the next time it runs the cron job.

To illustrate, lets say a product is purchased on 1st JAN 2012 with a billing cycle of 1 month. It falls due on 31st JAN for which we need to raise an invoice on 21st JAN (10 days in advance). If the cron job fails to run on the 21st and is run only on 22nd, it would still generate an invoice because it will know that the product is not invoiced by checking the isInvoiced field and since the billing cycle is 30 days and the difference between the due date(product timestamp+billing cycle time)and the product timestamp is less than 10 days. The same technique works for canceled invoices as well provided the isInvoiced field is set to zero.I would also suggest to maintain the cycle time in days as it is easier to understand and calculate.

By maintaining the above structure, the cron job could be easily automated with a SQL query and almost all databases provide this kind of query

Ubermensch
  • 1,349
  • 9
  • 15
  • Thank you for your detailed answer and to point out to keep track whether a product has been invoiced or not!! But regarding timestamp calculation: with the solution worked out in the comments above it will also create invoices if the cronjob fails: **if ( Timestamp is in future = true AND Month left = 0 AND Days left <= 10 )** because of the **<= 10**. So if the cronjob fails 10 days before payment date, it will still be created the next day, or am I missing something? – Fabian Jan 21 '12 at 10:11
  • @Fabian Not exactly. The cron would fail in the following scenario: Assume the cron isn't run for 10 days then the timestamp wouldn't be in the future since the latest time would be greater than the timestamp and invoices wouldn't be generated. It may seem to be rather unlikely but in if invoices are to be generated only 2 days in advance, then there is every chance this could happen. – Ubermensch Jan 21 '12 at 14:30
  • Okay, I see your point. So when I add something to track whether a product has been invoiced in a billing cycle or not (as you suggested) it will work fine and also will work if the cronjob fails for (in my example) 10 days in a row. The only problem I see for me is, how to reset the isInvoiced field to false after the invoice is created. When I set it to true on invoice creation it will not be invoiced at next due date? – Fabian Jan 21 '12 at 15:18
  • It shouldn't be reset unless the invoice is canceled. Why should it be invoiced at the next due date? Its already invoiced and it stands due for payment. There must only be one invoice for one product – Ubermensch Jan 21 '12 at 15:25
  • But every product is invoiced (e.g. monthly billing cycle) once a month or (e.g. quarterly billing cycle) every 4 month, like a newspaper subscription. For example **Product A** is a **weekly newspaper** and costs **120USD/year** and the customer chooses to **pay monthly**. So a monthly invoice is created from the cronjob that checks the database for: **Purchased Product**, **Billing Cycle**, **Timestamp**,... and if the time until next due date is <= 10 it will create the invoice. So i cannot set this Purchased Product to isInvoiced because the customer should also get billed next month. – Fabian Jan 21 '12 at 15:40
  • This is a case of recurring invoices. So instead of isinvoiced, we would keep an integer field that increments every time an invoice is made. So if a customer has subscribed on 1st JAN 2012 and his billing cycle is monthly, then after 3 months he must have been invoiced thrice and our count should show 3. If it shows a lesser count, then it must be invoiced. When a customer, changes his billing cycle, then the product timestamp must be changed. An ideal solution would be to create a table of invoices to keep references. Hope you would have done it, else older invoices can't be tracked – Ubermensch Jan 21 '12 at 16:07
  • Ah, I see! this would be a great solution! I just don't get how to deal with changing a billing cycle yet.. Yes, i would have created a table with all invoices so a user has an overview over all invoices he ever paid. – Fabian Jan 21 '12 at 17:18