----------------------------------- Velandil Wed Nov 02, 2005 1:17 am Inventory Management Etc. ----------------------------------- Currently working for a company who uses quick books to manage all their inventory and I frankly think it sucks :). We have to enter in serial numbers for everything sold and shipped etc. which isn't very friendly in quick books. Basically, what I want to do is the following: Have a publically viewable sort of "shopping cart" that shows things by model number and how many we have in stock, with the ability to "order" some products and have an order form sent to us Have a back where I can enter in new product types Have another back end where I can pick a product type from a list, enter in it's serial number, where it's being stored, if there are any problems with it, etc. Have a page not publically viewable where we can add, remove, manage and what not current database items And, finally, be able to save and print a PDF or the like with an order form created by us and after the products have been ordered to have them removed from stock (example, if 10 are in stock and I print off an order for 2, it changes the stock to 8, and removes the serial numbers of the 2 I put on order from the list). I also want it to be secure (doesn't have to be military-base secure, just so people can't get in and goof around). Is this possible? Are there any resources for similar things? Thanks, Velandil (former comp sci student, now evolved to shipping / recieving at a company that sells networking equipment) ----------------------------------- rdrake Wed Nov 02, 2005 7:47 am ----------------------------------- Yes, this can be done. Most of what you want can easily be accomplished using a PHP/MySQL setup. Use PHP to modify the MySQL database which contains all product information. If you don't want to use MySQL, other databases can be used as well. As for security, you could build a login script which only allows certain people in to modify information. Also don't forget to use session variables which will ensure nobody tries accessing one of the other pages without logging in first. This information can still be viewed by another person though. To prevent this, you'll need a SSL certificate which will send only encrypted information to the person's browser. The only difficult thing to create for your project would be the PDF exporting feature. There are libraries available to create PDF files, but it's still not as easy as a simple command. Check out [url=http://www.sourceforge.net/]SourceForge to find a nice one. You might even be able to find a pre-existing project which'll do what you want. Just search around for product management systems. ----------------------------------- Velandil Wed Nov 02, 2005 9:54 pm ----------------------------------- Awesome, thanks for the help. The PDF thing really is I guess the last thing I'm worried about. I think one of the things I need most help with is organizing the MySQL database. I don't have a whole ton of experiance with either to start, but I'm still much more comfortable with PHP than MySQL. I'm also a quick learner and I'm very set on getting something like this done. I poked around, and all the current IMS (Inventory Management Systems) seem to work okay, but they all also didn't have a good handful of the features I want. The only one that was easily customizable ran about 6 grand US for a 10 user license, which didn't interest me at all (looking to make something that makes my job easier, not cost me a fortune at the same time). This is more specifics of what I want: 1 Main Public Page > User Viewable Page (organized list of brand {I.E. Cisco, 3web, etc.} type of hardware {I.E. Switches, Routers, Etc.}, then by model {I.E. under Switches, have a 2950T, 2950G, 2950-48, etc.}, then after clicking on that specific model, display an image of the item, a quick summery, and how many we have in stock via method listed below). It should calculate stock by number of serial numbers in the database. Several Main Backend Pages, viewable only by registered users.Guess I'll have to use SSL or something for that, will have to research it. For Products: > One form that allows users to enter in Manufacturers (Cisco, Bay, Etc.) > One form that allows users to enter in Items (Routers, Switches, Etc.) > One form that allows users to enter in Model Numbers (2950T, 2950G Etc.) > One form that allows users to enter in Company Names and info (For who we buy from) > And one form that allows users to enter in a company name and info for customers > One form that allows users to enter in several things after. I want this form to have drop down menu's of Manufacturer, Item, and model number. After you pick those on the form, it should have a text box to enter in the serial number, a check box stating whether or not the device is working, a text box allowing user to enter in either the problem with the device if it's not working or any extra comments if it is working (I.E. Item doesn't power on if check box is for not working, or Item has a small scratch on the top left if box is checked working), and then a drop down menu of where it is located (which would be really cool if it could default to a section when the manufacturer, item and model is chosen, and I would probably just set this manually, as once we set sections of shelves, like A1, B3, etc., they won't change). I would also like there to be an Add button, so I can either enter in one item, or if we happen to get say 20 that day, then I can just press the Add button to add another row of the serial number, working, and all the stuff above, kind of like a multiple product at a time using the same manufacturer, item and model number (example, I want to add 5 Cisco 2950T Switches, and one at a time would take forever, where as if I could just pick Cisco, Switch, and 2950T, then add 4 more rows to the original first row to enter in a total of 5 serial numbers and what not, it would save a ton of time). And that's pretty much it for raw products. From there, I would like 3 more main back end pages. The most important being for ordering. > This is where I'd want the PDF bit. I'd essentially like it to look like this (hxxp://www.pcwaterloo.com/makequote.php), copy and paste, change xx to tt, it allows you to add products one by one, sums up price, and I'd also want it to allow us to chose tax (we don't have to charge anyone but those in ontario PST, and we don't have to charge anyone outside of Canada GST) and have it automatically calculate. After saving that order to the database, it throws it into a PDF file that already contains our letterhead, policies, date, etc. so that if we want to later we can pull up an order number and view that order made and change it, re print it, whatever. I also want the actual order form to remove items from the database. For example, if I want to ship 10 Cisco 2950T Switches, I want to be able to just enter in 10 serial numbers, have it search the database, tally it up (quality 10 of whatever switch or whatever), and then add the price etc. and list all the serial numbers after that. After saving the order form, it should remove those 10 serial numbers from the database and essentially remove 10 items from stock. The second one would be for RMA. I'd like to be able to give an RMA number to a customer and enter in the serial numbers for the RMA and the problem and company and what not, store it for record keeping etc. and have a page to view it. The third one would be for searching, quite basic, I want to type in a serial number, have it search the database and result to me what it is. I want it to show all the information, manufacturer, item, model, serial number, if there was a problem, all the fun stuff. Just want the ability to see where it came from and if there was a problem really so we can better track end user problems. That's essentially everything I want so far. I would love to expand it but I want the basics (although that's alot of typing up there) for now. Thanks a ton for any help here, I really aprecieate it. ----------------------------------- rdrake Wed Nov 02, 2005 10:49 pm ----------------------------------- If you prefer an easier way to set up the database initially, I'd highly recommend Employee Number Last Name First Name Permission Level The first few are pretty standard ones, you could even add things like DOB or whatever other information you need. The permission level is something like either disabled (0), readonly (1), readwrite(2). This way you can ensure that employees only do what they're suppost to and nothing to damage the system. For the inventory table, you'd probably want the following columns: SKU Name Price Quantity You can easily add/remove columns as desired. That should just about do it for the database. You'll probably need several pages in order to achieve this and keep things organized. Something like the following should do: View inventory (view.php) Login page (login.php) Index (index.php) CRUD actions (create, read, update, delete) (modify.php) The index page would have a link to the inventory page if the user is still logged in, or would redirect the user to the login page if not. Once logged in, a person with the permission level of 1 could view the inventory items. A person with level 2 access could both view the items and add/modify/delete the items as well. I think that's about it for the planning. Just take what I've said and modify it to fit your needs, and you shouldn't have problems. If you have any more questions either about the design or coding, don't hesitate to ask. ----------------------------------- Velandil Thu Nov 03, 2005 12:31 am ----------------------------------- Blah I was typing this big long thing then power outage >.