The task is to create a web based CRM system, using MySQL database and PHP. Suggested MVC framework are Codeignitor, CakePHP.
There are five main database tables (Company, Contact, Project, and Quote, handled by two tables), and 5 corresponding pages (Company, contact, project, view quote/invoice, and edit / new quote/invoice page). Please see attached excel file showing the 5 pages, and the four main database tables, and some other small tables (mainly used to provide drop down combo list source list.).
Pages: (page layout see attachement)
1. Project page
2. Company page
3. Contact page
4. Quote / invoice View page
5. New / Edit Quote / invoice page
Main database tables:
1. Project (list of project that has multiple companies and contact involved)
3. Contact (list of contact person in a company)
4. Quote (list of quote for project. Only contain general quote information, detailed product info is stored in the quotelines table.
5. Quotelines (List of number of different product for each quote)
2. Type (company type)
3. Diameter (diameter of the product)
4. Model (model of the product)
5. Pricelist (price list for different product)
7. Shape (shape of the product)
8. Source (project lead source)
9. Stage (Project stage)
10. Suspension (product accessory type)
11. Userright (User access control table)
12. Users (User information)
1. First 3 pages have search section at top. Multiple search criteria allowed. Partial match allowed. Click on each search criteria title result in sort based on that field.
2. Below the search section is the result display section.
3. When “Add new” button pressed, The first line item become empty, used to add new record. (another option is to leave the first line always empty for new record, if this is too time consuming to implement.) All existing records needs to be able to accept inline editing. However, all data are not stored to database table until “Save” button is pressed.
4. Page 4 and 5 are almost identical, (page 4 for view only, 5 for edit / new), other than page 5 has extra control and data entry field. Both page has all fields populated from record or user input, and can be modified. But only page 5 has the capacity to save to database table, and calculate results.
1. User account has user name and password protection. One user can only access record created by himself or by his employees (listed in table “UserRight”, that he has right to, in the example, user ID 2 “ABL” has right to User ID 1 and 5, so ABL can access all information belongs to all three users).
2. When a user searches anything (company, contact, project, quote, etc.) only record that has his own user ID, or the record with user ID that he has user right to access, will show up in result. (This includes the initial full list, too).
There are some other detail that's listed in the attachement word document.
The excel file has all 5 page layout and the last work sheet contains all the database tables (some with sample data, too)
The SQL file is an export of the actual database from mySql. that you can use and modify.