Project Description:
We wish to develop a cakephp (http://cakephp.org/) or codeigniter (http://codeigniter.com/) web app...either platform is ok for us. You should produce high quality, logical and modular code. Please produce logical class names and locations, and code should be placed into the right places (e.g. models/views/controllers)
>TECHNICAL DESCRIPTION (more general info provided below this section)
The app should provide a secure administration area and, when successfully logged in, allows user to:
1) Select two CSV files for upload from the local pc:
a) 'Payment processor monthly report'
b) 'Website sales report'
2) Enter currency conversion rate for sterling/dollar against the euro
3) Click Go, and the system will upload both spreadsheets
a) Consideration should be given to what will happen if, while processing, an error is encountered (e.g. if error, cancel upload and present report stating which rows caused an error)
4) When uploaded, the system should read in the data from the two uploaded CSV files, and generate two new CSV's for download:
a) 'sales report' -> detailed breakdown of all the sales, showing total amount invoiced (consolidated in Euros) and the total of VAT collected [default: sorted by currency/intracom/vat]
b) 'bank reconciliation report' -> pulls together the website and payment processor data to highlight any irregularities [default: sorted by currency and batch id]
NOTE: we will provide sample files for the two input files, and the desired two output files.
> GENERAL DESCRIPTION
The goal is to create an application which allows us to do our bank reconciliation at the end of each month.
At the end of each month we can export 2 type of files:
Payment processor Realex Payment Report: This is an excel file that we manually download at the end of each
month from Realex Website (we can convert to CSV before upload to system). It contains all individual transactions that can be identified with a "Transaction ID". All transactions placed in one day are gathered in one single payment called Batch which also has an identification number.
Please note RE the payment processor monthly report file:
> amount are shown in Cents. (for example 18900 equals 189.00 euros or Sterling/Dollar)
> when you see "rebate" in the transaction column, it means that the transaction has been partially or entirely refunded.
> In the batch column, when you see -1 and not a Batch ID, it means that the transaction failed (card/payment was refused by the bank)
Webstore Sales Report: This is an .csv file that we export from Moviesoft at the end of each month.
Each transaction contains all order information (amount paid inclusive and exclusive of vat, date, name, invoice #,email,...) but also the "Transaction ID" which is used to do the link with the Realex Payement Report.
At the end of the processing we need to issue 2 reports:
1 ) Bank reconciliation report which allows us to check that all transactions placed on moviesoft.com have been correctly paid. We can also use this report to notice a problem with a payment (or double payment). For example a payment received but which doesn’t have any corresponding invoice on Moviesoft.com. In this report, transactions are sorted out by CURRENCY and BATCH ID (payment of the day)
2) Sales Report which is a detailed breakdown of all the sales sorted out by:
Currency
Intracom Sales
VAT Sales
This report also includes a total by currency and type of sales but also a general total in Euros. Therefore it is necessary to be able to type in before the treatment the currency exchange rate.
With this report, we are able to see the total amount invoiced (consolidated in Euros) and the total of VAT collected.
Thanks for reading.
