invoicing logic for cart

[AndreF]

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.

[sneakyimp]

Your post is pretty confusing. It sounds like you are struggling with the idea of putting simple one-time cost items (like a book or something) in the same shopping cart as recurring charges (like a subscription or payment plan for expensive stuff). The concept of an order is a pretty fundamental one and you should probably create a table for ORDERS. An order has things like an order date, total amount quoted, payment method, shipping address, etc.

You should also be able to assign items to an order. I'm imagining a table for SIMPLE_PRODUCTS which just go in your cart, have a one-time cost, and are simple. You can connect them to a particular order with another table SIMPLE_PRODUCTS_ORDER_ASSOC which just has a product ID, an order ID, and a quantity. If your products are customizable (like an engraved charm bracelet or something) then you'll need additional information in this table.

I'm also imagining something like RECURRING_COST_PRODUCTS which are different. This might be something like 'Yearly support subscription'.

If you want recurring invoices, you will probably need to set up a cron job to check the db every so often to send out recurring invoices.

I don't really know what you're after so you'll need to be clearer.

[AndreF]

invoice table would have the order_item id and anything you
need for an invoice. with the order_id you would know from what
order it is, any discounts applied to it and the whom customer it's
from.

with the order_item.id we know from what order this invoce is....
The problem:
That order might have several products because of multiple choice on cart. say 2 products.
You did choose monthly for one of them and yearly for the second one. This initial invoice is for both of them because they are chosen once on cart. But how about renewal invoice? after one month you will get an invoixce for that product which has monthly term but if you relate it with order, that order will fetch two products for you but the renewal invoice does not belong to second product because it is yearly. How to solve this issue?

I hope you are understanding what I am trying to ask.

[sneakyimp]

yes, your question is about recurring invoices. like I said, you'll need to set up a cron job which runs a script every day or so and processes your recurring invoices. you'll probably need some table like recurring_invoices and the script will look in that table and send an invoice for any monthly or yearly purchases until they expire. That would suggest that you'll probably need a date field in recurring_invoices and some logic to determine whether or not to send an invoice.

[AndreF]

No my question is not about how to recure invoice, I know how to setup a cron and recure an invoice. My question is about table dependcy.
Listen to me carefully please:

I can have a table 'order' and it contains one row per order on cart. Another table is 'oder_items' and it might have several rows for only one order row in that another table because of multiple choice on cart. If you buy two diffferent products at once on cart, order row is one but items table has two rows for those two products. I can add a field to relates 'order_items' to 'orders' to ses which items belong to which order, if you buy different products at once on cart, there might be several rows on items tables to only one order row in another table because you ordered it once so one row.

Now question:
I can have another table for invoices and a field to relate invoices to order. if you buy two productcs at once, all total price of those two items will be in an invoice so a field to relate invoice to order and order itself has a field to find which products belong to that order.
so until here we have a field in invoice table to relate it to orders. as I said that order may have two products but as you bought them once on a cart so you got all prices on ONE invoice. but later on recurring invoices (again I am not asking how to recure it via cron I am talking about table dependency, please listen here carefully🙂 each of those two products should have a separate invoice not ONE like the initial one, because you may choose monthly for first prod and yearly for second prod. you got ONE invoice on intial order, but now you will get separate on recurring, so question: how to relate this recurring invoices on order? if we relkateb it to that order, that order might have second or multiple products, but this second invoice is for one of those products. as I said on invoice we have a field to relate it to order and this is no problem for initial invoice but for recurred invoice if we can't relate it to that order but we should relate it to one of products of that order. I am asking how is the best table dependency to solve this problem?

[sneakyimp]

I simply cannot understand your textual description of these tables. Perhaps you could supply some SQL?

[leatherback]

hm.. tricky. .I am not sure how to do it..

When do you generate the records for the invoices? In itself you do not need to put items into the invoice: you run a query to produce the invoice, say daily. You select from order_item_id where paid_in_full == 1 && next_payment_due == now().

Obviously, when you have a non-recurring payment item, you set the paid_in_full to true as soon as the payment has come through, and you could set a colum for invoice_sent?

Then you generate the invoice based on that query.

Would that work?

J.