Creating an Invoice Form in the cloud

[kuau]

I am trying to create an invoice form to allow sales to be entered to a database. The online invoice form would look a lot like the paper form with a section at the bottom for entering the payments.

I am having trouble figuring out how to add multiple items to the form, ie. the invoice number and date would have a one-to-many relationship to the items purchased and the payments. How do you insert multiple rows to an item table while inserting only one row to the invoice table? And insert possibly more than one row to a payments table at the same time, using the one form?

I was able to do this no problem in MS Access but it appears to be a lot more difficult in php and mySQL. Is this why they invented shopping carts? Am I thinking about this incorrectly?

Thanks. :)

[traq]

You've got one invoice with one (or more) line-items, correct? Use two tables:


**Invoice**
|---id---|---customer---|---shipto---|---payment---|---etc---|

**LineItems**
|---id---|---invoice_id---|---item---|---cost---|---qty---|---etc---|
you can use joins to select from both tables (as if it were a single table), though I'm not completely familiar with how the queries would be built.

You could also use comma-delineated lists or an XML file, but I think the two-table method would be best.

[ggalan]

the logic behind that relies more on SQL language as apposed to php no?


SELECT * FROM \TableOne INNER JOIN Row1 ON Row1.rowID = \TableOne.rowID WHERE Row1.rowID='$variable' AND Row2 NOT LIKE 'discontinued%' ORDER BY Row3

[kuau]

Thanks. There are actually 4 tables: client, invoice, invoice_detail. and payment. The question is how to write the form so that it pulls together the 4 tables so that it can load multiple rows into the invoice_detail table and payment table while adding only one row to the client table (or linking to an existing client) and the invoice table. Once I have the data loaded, displaying it will be less of a problem.

How do other people handle invoices? This must be a common problem. Can you use a while loop to load a varying number of items to the invoice detail table? Or do I create a tab that opens a page within a page that deals with only the one table. Or do I load the invoice table first to establish the invoice number and the date and client, and then use a separate form to load the items purchased against that invoice number? Do I add payments totally separately on a separate form after the purchases are entered?

Are there any examples of working invoice forms? All I could find was shopping carts which I thought were for clients. I need something for the sales people to use to record the sales in a more efficient way with more data (eg. tracking numbers) than the client would have at the time of the sale. Kind of an internal shopping cart. Is there no such thing? Would rather not reinvent the wheel here if avoidable.

Thanks for any ideas. e :)

[djr33]

The basic method here is that you share an id from one table ("order id" probably) then attach that to all other components.

Your "orders" table has: id, user id, date, price, status (for example).
Your "purchased items" table has: id, order id, ....

Then it's just a matter of doing a query for purchased items matching the order id, looping through those to gather all instances then generating the full "shopping cart".

You can't store all of this in a single table because the number of items purchased varies per query.

This structure is very similar to a forum's database: the most important table is "members", then various other tables contain rows for what those members do: for example posts which would have: id, user id, topic id, etc.
Then that topic id also is another way to cross reference, in that case to generate a topic.
It's easy to imagine after that creating a search to find "users who posted in this topic"-- find all posts, for which you find the users, then gather a list of unique users and output it. It's multiple steps and involves PHP in addition to several MySQL queries, but it's relatively easy when you consider the utility.

[kuau]

I really appreciate your efforts to help but I don't have a problem creating the tables and displaying the data. My problem is in LOADING THE DATA USING THE FORM. All three of you jumped to after the fact and ignored the issue I was asking about. HOW DO YOU LOAD MULTIPLE ROWS to one of the tables while loading only one row to the main table with the order ID that ties the tables together? You keep talking about after the data is in the tables, but I am asking how you get it in there in the first place.

The sales agents will not want to submit the form once for each item purchased, which would probably add multiple rows to the order table. The form should be submitted once after all items and payments have been recorded. The final result of submitting the order form should be something like this:

order table
orderID SaleDate AgentID ClientID Total Sale TotalTax
12345 2010-7-23 JK 5567 $1302.08 $52.08

order details table
OrderID ItemID Price Commission
12345 34 $ 400.00 10%
12345 55 $ 800.00 11%
12345 22 $ 50.00 7%

payment table
PayDate OrderID PaidBy TotalPaid
2010-7-23 12345 Cash $ 300.00
2010-7-23 12345 VISA $1,002.08

How do I get the form to add muliple rows to the order detail and payment tables but only one row to the order table by submitting the form only once? Imagine that the agents have a pile of orders at the end of the day that they want to enter into the database as quickly as possible so that at the end of the week they can print a sales report that will tell them how much commission they have earned.

Does this make sense now?

Thanks, e :)

[traq]

ah.

You just have to arrange all the data in your script before you submit it to the database. You could use $_SESSION for this, if there are multiple steps / scripts. You could also submit each query as it becomes ready, with another column called "final" (or something like) set to false, then change it to true when the invoice is complete.

There are Relational Database Management Systems that deal with issues like that (ACID (http://en.wikipedia.org/wiki/ACID)ity), though I don't have much experience with them.

[djr33]

The simple answer is: step by step. First you will generate the order id then you will create any other necessary components. It's not difficult, just complex, because you need to create many processes to do this. You'll need to account for all different types of input then create loops (probably 'while') to go through all relevant rows to insert for the order.

The idea above of storing it first in $_SESSION is a good one if you plan to have a multi-page form.
In fact, that's basically what a shopping cart is: a session storing various items. In the session, you can store arrays of arrays (as many layers as you need) so you end up with: $_SESSION['cart']['item#']['somevalue'], etc. ("Cart" holds an array of all items and each item holds an array of info.) Then you can do whatever you'd like with this. Inserting into the database at this early point is a bad idea. Wait until it's something to store permanently (an order, not a cart) and if you already have it formatted as an array this should be easy (at least relatively so). Use a foreach loop (or several) and go through all of the data, inserting as needed.

[kuau]

I've never used Sessions before. Is there a good tutorial I can start with?

So a session would be started each time an agent began to enter an invoice and then it would be cleared after the form is submitted? I'm just not sure what it would look like in the code. I tried adding a link to display a new row if necessary and when it loaded, even though the extra row was not used, it loaded all blanks to the database.

Any time an agent would be entering an invoice it is safe to assume it is already an order and not just a cart. There are two things going on: clients can place an order online themselves, and an agent can enter actual sales from the gallery. I think I'd like to keep what clients do as a cart that needs to be confirmed by an agent before it gets entered to the order table. I need to sleep now. Thanks so much for the guidance.

[djr33]

Sessions are very easy (really). Here's a tutorial:
At the very beginning of all of your pages add:
<?php session_start(); ?>

Now use $_SESSION just like any other array. But magically, it will be shared throughout all pages for a single user (never between multiple users).

One page 1:
$_SESSION['var'] = 'value';

One page 2:
echo $_SESSION['var']; //"value"

The technical explanation of sessions is a lot more complex, and they can be confusing when you need to deal with security, but more or less that's all you need to know to use them.

A few more notes:
Sessions expire after not being used, closing the browser window, etc. If the user continues to do things on your site the session will stay active and after a reasonable amount of time it will reset itself for security. Nothing to worry about in most cases. It's something like 15 minutes of inactivity, but it can be more or less...
The session is always started so you can always use it. This also keeps it active so that if a user continues to browse but not add things to the cart their cart will not reset and they can still buy the cart's items (several hours later, assuming constant activity).
There is also only one session at a time and that's why it's an array. So just store $_SESSION['myvar'] as a sub-array and do whatever you'd like with it such as 'cart' or 'login', etc.
If you must reset $_SESSION, the easiest way to do this is to use: $_SESSION = array(); and the values will be reset. That's all. If you want to reset only a part, just do that using unset($_SESSION['part']);.


You don't necessarily need to use sessions, because you could also just store the active order id and process each page. For example, you could click "edit order" (that would store a hidden value in the field for the order id) then add a product. And do that as many times as you'd like, each time expanding the order by adding a new product with the relevant order id to the products table.


I was under the impression you needed this for customers in addition to agents. For agents the approach above seems logical since the order ID will already exist. But for customers using a session to store the shopping cart seems the logical way because you don't yet have an order id to attach anything to.

[kuau]

Dear Daniel: Thanks for the great info. This is exactly what I need to solve possibly a few of my dilemmas. I need to try one at a time. Starting with the concept of Sessions, could that be used to pass a client ID# from page to page? Or is it better to use $_GET variables? I made a page with several tabs with the idea that an agent could do all things related to a particular client from the one page. I tried to upload a screenshot as an attachment so you could see what I am talking about, so hopefully you can see it.

Because there are over 35,000 clients, it takes a few seconds to locate the client ID# so I don't want the agent to have to keep choosing the client from a drop-down list for each function. What is the best way to handle this?

Mahalo! e :)

[djr33]

That's a very difficult question to answer.

A session is the only tool in PHP that will NOT change a variable when the page reloads. But the downside of this is that it also then will not be dependent on which page you access. Therefore, sometimes it is confusing.

If the ID is part of the "session" (general timespan of accessing the site) then that is a good idea. If it is dependent on single pages (even many single pages), it probably should not be stored in a session in this way-- the session is a value across an entire site.

For example, a shopping cart is perfect for sessions because you only have one. If you needed to have several shopping carts, you might need to find another approach based on what part of the site you were accessing.


However, since you are using a dropdown list, I suggest pre-setting that to the last accessed value in $_SESSION. Loop through your values to generate it and if it is equal to the last used value (store this in $_SESSION first!), then append select="selected" to the end of the select option.

[kuau]

Oh, it seems Sessions would not work as the agent would be accessing many different clients from the time they first log in to the end of their shift.

I'm not sure how to do this...


However, since you are using a dropdown list, I suggest pre-setting that to the last accessed value in $_SESSION. Loop through your values to generate it and if it is equal to the last used value (store this in $_SESSION first!), then append select="selected" to the end of the select option.

The dropdown list is created dynamically from the database of 35K clients and causes a delay. This is what I want to avoid. Is there a better way to pass the variable between the tabs? Thanks. :)

[djr33]

What I suggested will automatically pick out the right entry but not force that entry. That way, it will save time but not limit the user.

You could also use sessions. You CAN change it, but you can't have multiple values at one time. So from the beginning to the end of the shift, they'd start with one, then another, etc. But they wouldn't be able to work on more than one at a time. Rarely is this a problem, but sometimes it causes confusion if they take a break and start again, etc.

The easiest way may be just using a hidden field in the form generated from the last page.

Some combination of all of this should work, but it's hard to know without understanding exactly what the users will be doing. And often that's a little unpredictable.

When in doubt, leave them control, so pre-set the value using sessions and then let them change if needed. For example, you could even skip the select menu and just show plain text (from the session). Then have a Javascript link to "select other user" which brings up the select menu. Obviously this gets a little more complex, but it will be more complex if it's able to handle every possible user's method, and, probably, mistakes.