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
  • 20,543
  • 29
  • 115
  • 306
Fabian
  • 133

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,793
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,956
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