Small database application

We urgently need a small database application writing, to run on XP machines, connecting to a mysql database also hosted on an xp machine on a local network It has to import a product file from excel, filter out the required fields, and post them into the database.? We also need to create a "box" database, search through the product file for matching records, then add the selected quantity of the matched product into the created "box". We also need to be able to export the "box" stock that has been added and a few other maintenance functions - nothing that is complicated. Full details in the requirements. Most urgent of all is the ability for someone to grasp the requirements, code it asap and deliver the working application asap ## Deliverables Functional Business Requirements for the application Firstly, needs to connect to a mysql server database, either on the local machine or on the local network - needs to have a method of how to change the database name, and connection details such as user and password for each machine. We will need to install this application on multiple XP PCs. **Pre-logon** ----------- There needs to be a administrator function to change the connection settings should the sql database move - this will apply to the local machine, so settings need to be saved on each local instance of the application. Protection for this can must be done by a password? stored on the mysql server. The admin function must also be used to create or change users, and allow/deny them access to the application? - we need userid, name, password (protected on the sql database), and if the user is active or not or administrator or not. ? There should always be a user "admin" that cannot be made inactive. We do need a mechanism to reset the admin password to a default value (a fixed strong password that we can store offsite should someone mess up the admin password) Admin function needs and exit key to leave and a "test database connection" button to verify that any changes made to the mysql database settings work. The admin function also need? "maximum number of records to return"? and "max adjustment" values when performing searches - this should be stored in the sql database and changeable by the administrator A "field"? table equipment type (where admin can create more), showing equipment type, with 2 boolean values - "OS" and "Supplies" for each record - initally we just need "PC", "Laptop", "Server", "Printer" **Main application** **Logon** Before accessing the application, users need to logon. Therefore the system needs a logon screen, with username and (asterixed out) password. The app needs to connect to the database and verify the userid and password, and check that the user is active (if inactive, it shouldn't allow access to the database). Once logged on, the user name needs to be shown somewhere on the screen - possible above any menu, or next to it.. **System menu** ***[login to view URL] product file ***We need to import a product file from either and excel spreadsheet or xml ? regularly, which effectively needs to drop the product file, and reimport it. There is no need to maintain any relationships to this file, it is simply used for lookups and subsequent posting based on the data selected.? (actual file formats attached) - all the records need to be read in, and the relavent fields written to the product file. The fields needed are a)StockNumber b)InventoryTitle c)InventoryOwner d)StorePrice e)Notes Every piece of data needs to be capitalized ***[login to view URL] Box Data ***This is used to export "finished goods" to the local "my documents" folder - in any usable format, tab delimited or excel etc - something we can use later. The Box export needs to contain the following data a)boxnumber b)userid c)datetimestamp d)sourcemachine e)stocknumber f)inventorytitle g)inventoryowner h)storeprice i)quantity j)adjustnotes Once exported, a datetime stamp needs to be put on each record, to prevent reuse.? This happens for all? boxes in the complete status. Also the box status needs to be set to "exported" ***[login to view URL] Status Change*** Ability to change the status of a box from "complete", back to "ready" in case it is inadvertantly completed - a press "F6" type command to confirm, and also remove the completed time/date stamp **Stock Processing Menu *[login to view URL] a new box ***This function creates a new box number in an internal table - and gives it a status of "ready" -? needs to check for duplicates in the table, not allow any duplicates, can be free-form, any alphanumeric, up to 6 characters - we will use preprinted labels, so no real need to check anything, just not allow the same id to be created twice. If box already exists, just display a message to tell the user "Box already created, please use different number". ***[login to view URL] stock to box*** This is the most complicated part of the application. On first entering the screen, the user needs to enter the boxnumber - this should be "remembered" as a local variable on the pc (not user) and stored on the local machine -- this number should be suggested UNLESS the status is complete. At any time the user should be able to change the box number - I would suggest? something like "F2" to change box number rather than? tabbing? through to it - the message should be displayed next to the box number "Press? F2 to change the box number" - only boxes in the "ready" status should be allowed - any other? attempt should revert back to the stored number. Next, the user also needs to enter some basic? data about what they are? working on - these are free text fields showing basic information - "machine" (alphanumeric up to 30), "serial number" (alphanumberic to 20) and "Owner" (alphanumeric to 20) - these three basic fields need to be displayed always on the screen, along with the box number - once entered,? there needs to be a "press F6" to change type of system a bit like the box numbers, where they can be re-entered - with an "F7" to confirm or ESC to quit - to change these values, the user needs to press F7, if they press ESCape, the old values remain. There now needs to be a part number search box - a free form search with a "search button" that is accessed either my a mouse click or by pressing Enter. The search text box needs to support up to 20 characters - including spaces and non-alphanumeric. Once the user performs the search - the system needs to search through all the product file matching the characters within the product file with the same search - e.g. if I search for "dell", the system capitalizes all requests- so will search the entire product file for "DELL" - this would match anything beginning with DELL, or having DELL as part of the description - so "DELL 8G668 POWEREDGE 3250 FAN FFB0612EHE" would match, so would "ABC 123 MEMORY STICK 128MB FOR DELL GX270"? as would "THISITEMISMADEBYDELLANDNOTIBM" Once all the matches are found the user needs to be shown all the selections in a scroll box Stock Number, Inventory Title, Store Price, Inventory Owner There could be thousands of matches if they select a search like "A" for example - so only show the first? *n* matches.[where *n* is value set in the admin mode for the number of records to return]. The user needs to select the appropriate match (many will just be a single record) from the search value. Once selected the search part of the screen needs to clear, and the "notes" element be displayed - mainly this is a blank field, but on the occasion that we need to put a note reminder there, then this is essential. There should be and "quantity" field - where the user can enter a quantity (up to the "max adjustment" quantity set by the administrator). and a notes input, should there be any comment. The user needs to enter the quantity and press? F6 to? confirm, or ESCape to abort.? Either? way, it goes back to the search function,? keeping the? settings from the top of the screen.? Once adjusted the system needs to write an inventory record - this is a new table that has all the "box data" information? - see first section -? ? a)boxnumber b)userid c)datetimestamp d)sourcemachine e)stocknumber f)inventorytitle g)inventoryowner h)storeprice i)quantity j)adjustnotes Once written, the screen should revert back to the way it was before the item was selected, ie. same box, same source item/serial/owner screen. For clarity, the last adjusted item should be shown? "greyed out"? somewhere on the screen, showing? the quantity adjusted and the inventory title. The user? can then keep looping around the screen, until he? leaves the screen? for any reason.? Leaving the screen should be a? press? "ESC" function or similar with confirmation? "are you sure" type message. ***[login to view URL] Box Contents***? This screen should allow the user to view the contents of a box, where they enter the box number, and the system shows the? box number, status, and a scrolling box, with inventory title, quantity. If anything needs to be changed, there should be the option to change the quantities - if something is wrong, then the quantity could be set to zero. There should be a confirmation press "F6" to confirm the contents and update the box status to "Complete", or "press? F4" to adjust the quantites and go back to the "add stock to box screen". **Refurbishment Menu** ***1. Refurbish Equipment ***A simple screen where the user enters the following (none of which is from the main product database) - all free text Equipment type (From admin screen lookup equipmenttype) Make Model Serial Number Inventory Owner Cleaned Notes (free text field up to 160 characters) If equipment type has the boolean value to true for OS, then a box needs to be shown to enter the operating system loaded - free text, just not accept? a blank record) If? equipment type has boolean value true for "Supplies" then again,? a box needs to be shown for "toner or ink" present for a printer or similar - again just no blank value The screen just needs to write into the table the userid of the person writing the? record and a timedate stamp. The system should not allow duplicate entries for make and serial number. The input screen for this function should just be make and serial number - if it already? exists, then the record and? current values should be shown - if not, then prompt "F6"? to? create a new record - make model serialnumber? and inventoryowner must be populated to save the record. **Reports? Menu *[login to view URL] Completed ***A basic report that shows the boxes completed, along with the total quantity of items in each for a given date range (date is from completion time stamp on box) - shown to screen with option to print using windows print spooler ***[login to view URL] Adjustments by User*** another report that shows line by line all the items, and quantity adjusted for a given user (needs a box to select user) for a given date range - totalled with quantity and value (if quantity is 10 then value is 10*storeprice from item file!). Printed to screen, with "print" button to send to windows spooler ***[login to view URL] Report ***All the data from the refurbished data by an input date range, printed to screen with "print" button to sent to windows spooler ------------------------------------------------------------------end----------- I cannot see the need for any backup, purging or maintenance functions for the database. It will just add complication to what should be a simple system. Lots of words, but a really simple system! I just want no misunderstandings, as we have a really tight timeframe -