I am creating a shopping cart and this question is about invoicing logic in general:

If multiple products are chosen on cart at once, the total price should be in one invoice. but on re-occuring invoices, there should be seperate invoice for each product because they might have different term however ordered once on a cart. also it should be known that each invoice belongs to which order. so I am confused how to relate invoices with orders.

for first order however with multiple products I could have one row for that order and in another table multiple rows for multiple products related to that one-row order. but how about invoice? I could create one invoice for that order row on first time. but on renewal may be one invoice for one of those products, so if I relate this invoice to that order and then get products of that order we will get two products but that invoice is for one of them. so how to solve this problem? please advice how is the best table dependency and logic for the purpose? somehow I think that order table is useless, why not having just products table with a row per each product? in this case, then how to invoice more than one product at once because of multiple products choice on cart? please give me directives.

I am not talking about invoice recurring via cron, but I am talking about table dependency. I hope my post is enough clear what I am trying to ask?

Logically each invoice is made up of line items, correct? And each line item is related to a single product, right? So why not this:

(note: PRIMARY key, key)

invoices table:

invoice_id, user_id, invoice_created_date, invoice_due_date


line items table:

lineitem_id, invoice_id, service_id, description


products table:

product_id, product_name


product terms table

product_id, product_term, product_price


service table:

service_id, user_id, product_id, product_term, startdate, renewdate



Each invoice line item is related to a service line item, which is associated with a product. I think this should work for what you're asking.

Okay I try that.

May I ask you to read this thread also and advice more?
http://www.phpbuilder.com/board/showthread.php?t=10355096 (http://www.phpbuilder.com/board/showthread.php?t=10355096)

Read up on "order orderline" information.

How Codelphious discusses is exactly this you might just need to read around the subject.

Remember you can always plan it on paper or even access then it's easy to keep editing your database until everything works.

Let's check if I understood codelphious correctly, and let me know if I am wrong:
We have a table for invoices which contains major info about that particular invoice. each invoice might have one or multiple items/charges on it, these items/charges are stored in another table called lineitems, this table might have one or multiple rows which might be related to ONE invoice, because that invoice might have multiple products, so each product pricing info is a separate row in lineitems, this table should have a field 'price' which shows price of that item, so the total items of that invoice will be added all together to calculate the total price of that invoice.

but on renewal, we should be able to invoice those multiple items separately due to term on cart.

Imagine on first invoice we have two rows one for domain registering /yr and one for hosting account / mo. on first invoice we pay the yearly fee of domain and monthly fee of hosting on one invoice, so for that invoice we have two rows in lineitems table which are 1.domain and 2.hosting.

now after one month we need to invoice the hosting account separately and not domain at this time.

on lineitems we have service_id. imagine the service_id of first lineitem (e.g. domain) is 1 and service_id of second lineitem (e.g. hosting) is 2 (like auto_incrementing id). but these two lineitems still relates to ONE invoice row because they were chosen on one cart/order. so the cron will check the renewdate of service table and the term. now we have two rows in service table: 1.domain and 2.hosting. when the cron sees now is the time of invoice recurring according to renewdate of service table, it can find the product details using product_id and invoice it for renewal....

so if I understood correctly until here, then I assume no new row should be added to service table for this renewal invoice but the renewdate of that same row should be updated for next time.

please let me know if I got it correctly?

A renewal should be treated just as an invoice, its just an invoice which has a link to a previous invoice. There shouldn't be any other logic for it otherwise you are complicating matters yourself; in fact, a renewal for webhosting is just like an invoice, with one line item, that has a property expire date; and perhaps a field that links it to the primary key of the invoice for which it is renewed.

Andre,

I think you've got it, for the most part. Maybe it can be better explained with an example.


Let's say you offer web hosting and domain registration services. These would be added in the products table like so:

products table:

product_id, product_name
1, Web Hosting - Plan 1
2, Web Hosting - Plan 2
3, Domain Registration


product terms table

product_id, product_term, product_price
1, 1, 4.99
1, 3, 6.99
1, 6, 9.99
1, 12, 14.95
2, 1, 6.99
2, 3, 9.99
2, 6, 12.99
2, 12, 19.95
3, 12, 8.95

Notice product_term is in months (12 months = 1 year). Now when someone orders one of these products we add a row in the services table, then create an invoice for it like this:


service table:

service_id, user_id, product_id, product_term, startdate, renewdate
1, 1, 1, 3, 2008-06-12, 2008-09-12
2, 1, 3, 12, 2008-06-12, 2009-06-12

Notice that this service is for user 1, and they ordered product 1 (Web Hosting - Plan 1) for a 3 month term starting on Jun 12, 2008 and renewing on Sept 12, 2008. They also ordered 1 year of domain registration. Now let's create the invoice (I forget prices on each line item as well, that would be useful):


invoices table:

invoice_id, user_id, invoice_created_date, invoice_due_date
1, 1, 2008-06-12, 2008-06-12

line items table:

lineitem_id, invoice_id, service_id, description, price
1, 1, 1, Web Hosting - Plan 1 (website.com), 6.99
2, 1, 2, Domain Registration (website.com), 8.95


Now to answer your question about renewing the services. On Sept 12, 2008 we need to create another invoice, because it's time for our web hosting service to renew, so let's do that:

invoices table:

invoice_id, user_id, invoice_created_date, invoice_due_date
2, 1, 2008-09-12, 2008-10-12

line items table:

lineitem_id, invoice_id, service_id, description, price
3, 2, 1, Web Hosting - Plan 1 (website.com), 6.99

Don't forget to update the renewal date now... (this is current renewdate + term months)


service table:

service_id, user_id, product_id, product_term, startdate, renewdate
1, 1, 1, 3, 2008-06-12, 2008-12-12


Our next invoice would be created Jun 12, 2009 for the domain registration and that will work exactly the same as above.

I hope now you can see how easy it is to bill recurringly for separate service that renew at different terms. Another advantage of this design is that all you have to do in order to change the price a customer's service renews at is by changing the price in the products table.

Good luck!

Codelphious, thanks a lot for the kind help.
With your explanation I see I had understood your first post correctly. In fact, I had a service table however called with another name and the same purpose, but my mistake was that if a muti-order was submitted on cart, I did add everything in one row, because it was on ONE cart, but I see I have to have separate rows for each product however they are on ONE product.

Now another question, to issue an invoice, you need an InvoiceID and an OrderID. no problem to create a unique number as those ID. But how should be managed? I guess I should have an OrderID on service table and all rows which related to ONE cart should have the same number and still the same number for renewal invoices? correct?

Two more questions in addition to my last post:

2. I think another advantage of this design is that if both items on an invoice have the same term, they are still recurred on AN invoice instead of two? correct?

3. In user area we have $_SESSION['user_id'] to make sure if a user is logged in. Now in user area we need to know if that user has an unpaid invoice or not, we have a field 'paid' on invoice table, 1 means paid, 0 is unpaid, now we should search the service rows belonging to that logged in user and from that service, find lineitem and from lineitem we can find invoice and check if paid status of any invoice belonging to that user is 0 or not, something like this:

SELECT i.paid FROM invoice_table i JOIN lineitem_table l on l.invoice_id = i.invoice_id JOIN service_table s on s.service_id = l.service_id WHERE s.user_id = '$_SESSION[user_id]'

correct?
PS. oops. I forgot on invoice table we have user_id, now just search that table for user_id to search if paid field is 0 or not.. please ignore second question and just answer the first two questions.

Two more questions in addition to my last post:

1. Now another question, to issue an invoice, you need an InvoiceID and an OrderID. no problem to create a unique number as those ID. But how should be managed? I guess I should have an OrderID on service table and all rows which related to ONE cart should have the same number and still the same number for renewal invoices? correct?


It usually isn't necessary to remember which items were ordered together, but if you need to just add "orderid" on to the services table. No need to add the orderid to the invoices at all.


2. I think another advantage of this design is that if both items on an invoice have the same term, they are still recurred on AN invoice instead of two? correct?
That is correct. In fact, all service that renew on the same date can be grouped together and added to a single invoice.


3. In user area we have $_SESSION['user_id'] to make sure if a user is logged in. Now in user area we need to know if that user has an unpaid invoice or not, we have a field 'paid' on invoice table, 1 means paid, 0 is unpaid, now we should search the service rows belonging to that logged in user and from that service, find lineitem and from lineitem we can find invoice and check if paid status of any invoice belonging to that user is 0 or not, something like this:

SELECT i.paid FROM invoice_table i JOIN lineitem_table l on l.invoice_id = i.invoice_id JOIN service_table s on s.service_id = l.service_id WHERE s.user_id = '$_SESSION[user_id]'

correct?
PS. oops. I forgot on invoice table we have user_id, now just search that table for user_id to search if paid field is 0 or not.. please ignore second question and just answer the first two questions.No! DO NOT put a 'paid' field on the invoice table. That is a bad design, and I've seen many billing applications use it... it's horrible.

What you need is another table called transactions:

trans_id, user_id, datetime, payment_type, payment_amount


And payment application table:

id, trans_id, invoice_id, amount


When a user makes a payment it is added to the transactions table. They may pay only $10 for a $15 invoice (it happens sometimes)... therefore we need to have an additional payment application table to track how much of each transaction was applied to each invoice. Does that make sense?

Now, to calculate if an invoice (#10) is paid all you have to do is...

SELECT SUM(amount) AS paid FROM payment_application WHERE invoice_id='10' GROUP BY invoice_id

SELECT SUM(price) AS total FROM lineitems WHERE invoice_id='10' GROUP BY invoice_id

And if paid == total then the invoice has been paid in full.

I hope that helps... if I go into any more detail I'm going to have to charge you :stickout:

and payment_amount field in transactions table is the total they should pay or is what they paid although partitialy?

please explain why it should be there as paid amount is in payment_application and total amount could be calculated from lineitems belonging to that invoice?

I hope this is my last question! :)

Sorry just one more thing:

2) why should there be two separate tables for transaction and payment_application? Why not having a row in transaction table with type "charge" which is total and when they pay a new row will be added per payment with type "payment" and we need to calculate the rows belonging to an invoice with "payment" type and and if that is equal to the amount of transaction row belonging to that invoice with "charge" type? what is the advantage of having these two tables rather than one?

Multiple payments may be required to pay off an invoice, therefore you need to have two separate tables to represent this data.

In the tables I posted, "payment_amount" is the TOTAL amount of the payment. And "amount" is the amount applied to each invoice. Like I said, a payment could be applied to multiple invoice, or an invoice may take multiple payments to be paid off.

Okay understood, but could you suggest a better name for payment_application table? I don't like that name much.

How about "applied_transactions"?

Also, I think I would rename the "transactions" table to "received_transactions", so then you have "applied_transactions" and "received_transactions".

Why are you trying to make the things congested , making serparate tables for transaction and payment is the right way.

paynhost, I was not trying to congest tables, that was a question and after Codelphious explanation I know I should go that way! :)

Codelphious,
Let's check if I understood you correctly:
if a new/due invoice is created, at the same time, a row will be added to transactions table. let's say user_id 1, got a $15 invoice and did choose paypal to pay it, so we will have:

trans_id, user_id, datetime, payment_type, payment_amount
1 , 1 , time() , paypal , 15.00

whenever he pays $10 of it, a new row will be added to payment_application like this:
id, trans_id, invoice_id, amount
1, 1 , 1001 , 10.00

and if he pays the rest $5, a new row will be added to table above.

Am I correct until here? if so, how about if he pays the first $10 with paypal and the rest $5 with e-gold? as we have already paypal for payment_type? and that transaction table has only one row per invoice and we cannot add another row. so what about payment_type?

Try this:

(Two entries to track to payments)

1,1,time(),paypal,15.00
1,1,time(),e-gold,10.00

(Two entries to link two payments to the same invoice)

1,1,1001,15.00
1,2,1001,10.00

To calculate the balance, just add all line items in the invoice, compare that with the total from your payment_application (or whatever you call it) table:

SELECT SUM(`amount`) AS `amount_paid` FROM payment_application WHERE invoice_id = 1001;

If amount_paid == total_invoice_amount
print "Thank you for settling your invoice"
else
print " Your balance is: " + total_invoice_amount - amount_paid

Adding to what Burhan wrote,

Let's say user 1 had an invoice #1001 due for $15, but he only had $10 in his paypal account and wanted to use all of that first, then pay the rest with e-gold. He would then make 2 transactions, 1 with paypal ($10) and 1 with e-gold ($5). That would look like this:

received_transactions:
trans_id, user_id, datetime, payment_type, payment_amount
1, 1, date('Y-m-d H:i:s'), paypal, 10.00
2, 1, date('Y-m-d H:i:s'), e-gold, 5.00

applied_transactions:
id, trans_id, invoice_id, amount
1, 1, 1001, 10.00
2, 2, 1001, 15.00

We can see that invoice #1001 is now paid in full using 2 separate transactions. Also notice how I used the date/time stamp rather than unix time. The reasons for this is because the date/time stamp is easier to read and to work with.

Now, let's assume that the user overpaid on invoice #1002. Remember, only payments that are applied to invoices are added to the applied_transactions table... so an overpayment would look something like this:

received_transactions:
trans_id, user_id, datetime, payment_type, payment_amount
3, 1, date('Y-m-d H:i:s'), paypal, 20.00

applied_transactions:
id, trans_id, invoice_id, amount
3, 3, 1002, 15.00

Right now the user has a $5 credit... so let's say they have another invoice that was recently created (#1003)... so we apply this credit to the new invoice.

applied_transactions:
id, trans_id, invoice_id, amount
4, 3, 1003, 5.00


The above examples demonstrate how to use multiple payments to pay a single invoice, how to handle over payments, as well as how to use a single payment to pay multiple invoices.

Burhan and Codelphious, thanks a lot for all kind helps, I was lucky to meet you helpful people here. If you weren't here my script would stopped!

Hello,

Just a quick question please:
It is important for my script to distinguish/differentiate the initial invoices from recurred invoices. How can I do that? I need that because I want to integrate jam affiliate thing with my software and whenever a recurred invoice is paid AND recurring JAM commission option is ON in my admin panel, then recurre the commission otherwise nothing to do.... and an initial commission is already made for the initial invoice. Please advice me.

Regards,

And what will you do with 'received_' and 'applied_transactions' data if you refund or void an invoice? Do you delete all data of that invoice from both these two tables?