I only know the basics about this kind of stuff so I am sorry if I have left out and important information about what I need.
Briefly - I need a MySQL data base to hold transaction informatiand from my customers. Currently it is s pread sheet that a few of the staff in the office use and we want it to be a data base on-line so that we can all access it simultaneously. It is quite simple and is really just mainly used to keep track of an orders status eg. wether they are open, or closed, or wether more information is required etc.
Currently, I download some excel files from my internet banking each day, then add the days data to a spreadsheet and manually copy down formulas etc. I simply have not had time to do the basic VBA to automate it.
What I need.
I need to be able to download the files from my internet banking, and then upload them to the data base over a web page. There are three files that come from the bank, each a different currency of that days transactions. So there are all the transactions for USD, EUR, and GBP. I am guessing each will need its own table.
Each of the files from the bank "does not" only have the current days data. So if I download the file today it may have about 100 records in the file covering a few days, and only 20 to 30 may be new. Each record is unique so when the file is uploaded only the 20 or 30 new files should be appended to the tables.
Once the new data is uploaded, I need to be able to view all of the pages together as 1 whole page will all of the transactions. I also need to be able to view all deposits, all withdrawals etc.
Each transaction is associated with an Order number which does not come over with the downloaded banking information so I need to manually enter in an Order number for each transaction. So there needs to be a feature where I can add an order number for each record.
I also need to be able to add a comment next to each transaction. The comments should be editable, and it would be good if a time stamp was automatically added when a comment was posted.
And I need to be able to change the status of each transaction and I should be able to rename any of the statuses and add more statuses if I need to. The statuses are sensitive so for now just make them status a), b), c), d), e), f).
Once there are records in there I need to be able to view the information filtered by status a), b) etc and also all together.
To access the data base there should be a sign on page which can have multiple users so I need the ability to create new users and passwords, and also to have a guest account with limited access.
Also some transactions do not have enough information to be able to work out what the order number is so I need to be able to view a page with all of the transactions that do not yet have an order number.
There also needs to be a summary on the pages that calculates the total amount outstanding on orders that have not yet been filled. This should be by currency. I also need another page that has the same thing but also allowes me to enter an exchange rate to convert all the currencies to USD, and also allowes me to enter in values of amounts we have in other accounts etc and then calculates what the surplus is.
Soon we will be getting data from other bank accounts as well and the excel files downloaded will have a different format in terms of the colums data is in etc so you should begin knowing that new pages will need to be uploaded and different colums will be used.
Oh, and 1 more thing, not all of the data is used from the pages now. Only some of the columms need to be uploaded.
This is financial information so needless to say it needs to be very secure and have no holes in it. Before payment is made a 3rd party will will check the code and the data base to make sure it is secure. If any holes are found you will need to fix it all before I release the escrowed money.
I think that just about covers it. I hope that is enough info, if not please let me know.
I should add that this data base is going to be on the back end of a web site that is all displyed in HTML, and the backend coding is all perl/cgi so this project also needs to be perl/cgi.
In other words, no php - sorry