PHP/MYSQL Customer Support Ticketing System

IN PROGRESS
Bids
66
Avg Bid (USD)
$3861
Project Budget (USD)
$3000 - $5000

Project Description:
NOTE: PLEASE READ THE ENTIRE PROJECT SCOPE BEFORE BIDDING, AND PLEASE ONLY BID IF YOU CAN COMPLETE THE PROJECT!
This project does not need to look pretty, we just want good functionality. See attached files for an idea of what we’re looking for (not exact!)
This is a customer support ticket project. Our company takes care of support of customers from many different retailers. For purposes of understanding...A customer purchases a product from a retail store. The retailer gives us all of the customer and sale information from that purchase. We enter that into our database. We then automatically follow up with the customer 7 days, 30 days, 90 days, 6 months and 1 year from the date their information is put in. If a customer has an issue with their product, they call us. We then open a ticket, email the retailer so they know, and we contact the customer’s attached service company to resolve the issue. We then follow up with them to make sure it is fixed. This means the retailer does not have to manage service issues for their customers.

***Customer Management***
The customer management feature of the web site will allow the user to view, add, modify and
delete customers. Initially when the customer is added the user will enter basic information about the
customer. Once the customer has been added the user will be able to update all related customer
information or delete the customer from the database. In addition to updating basic information the
user will also be able to add and modify the following items related to the customer; each item will
have its own form to add/update information:
- Product information (including equipment attached to the product)
- Trouble tickets
- Payment information
- Notes

On the main customer management page the user will be able to search customers by first and/or
last name, or email, or phone number and filter results by retailer.

Trouble tickets will have the additional functionality that will allow them to be emailed to a service
Vendor as well as the retailer they are attached to.

--The following details the information that the user will be able to add and update for each customer.
Customer basic information will include the following fields and will be entered by the user upon
adding a new customer:

- Associated retailer; this will be a list generated automatically as retailers are added to the system
- Associated technician (assigned when customer is added, is customers main contact)
- Associated service company; this will pull from the same data as the retailer drop-down. Service companies will be attached to Retailers.
- Customer Representative; This is an employee of the retail company who sold the customer their spa or pool. Employees are attached to retail companies.
- Member #
- First Name
- Last Name
- Member Birthday
- Spouses First Name
- Spouses Birthday
- Children’s Names
- Home Phone #
- Cell Phone #
- Fax #
- Email Address
- Billing Address 1
- Billing Address 2
- Billing City
- Billing State
- Billing Zipcode
- Service Address 1
- Service Address 2
- Service City
- Service State
- Service Zipcode

--Trouble tickets will include the following information and will be entered by the user when managing customer information. When a ticket is being updated, the technician can change info already in the ticket as well as add new notes per update:
- Customer (name, address, email, phone number)
- Ticket Number (auto generated)
- Retailer
- Service Company (Name, address, phone, email)
- Main Technician
- Created by
- Created Date
- Product
- Suspected Equipment
- Problem Type
- Problem Sub-type
- Problem Start Date
- Problem Frequency
- Problem Codes Displayed
- Description
- Customer Actions
- Follow Up Date (default is 5 days from ticket update/create date)
- Ticket Status
- Send email to retailer (popup, send email of ticket but be able to add notes)
- Send email to service (popup, send email of ticket but be able to add notes)

-- Automatic follow ups as mentioned early in this scope will be a special ticket with a ticket number (such as FLWUP-XXXX) and only 5 text fields, one for each follow up. These are to make sure customers are happy with their initial purchase over time. These tickets are due for every customer 7 days, 30 days, 90 days, 6 months and 1 year after their information is put into the system. If the customer mentions a problem during one of these calls, the technician should be able to click a link to create a regular ticket for the issue.

--Product information will include the following information and will be entered by the user when managing customer information (customers can have more than one product):
- Product Type
- Manufacturer
- Model
- Serial #
- Used/New
- Indoor or Outdoor
- Purchase Date
- Install Date
• Seating
- Shape
• Gallons
• Placement
• Chemical Program
• Average Usage
• Total time used
• Equipment (popup, unlimited equipment can be added to each product)

--Equipment Information is part of the product information. The Product page will have the ability
to have unlimited equipments attached to a product. Equipment information consists of:
- Type
- Manufacturer (manufacturers of the selected item types here)
- Part#
- Serial#
- Used/New
- Purchase Date (defaults to purchase date of product attached to)
- Notes

--Notes will include the following information and will be entered by the user when managing customer information.
- Date
- Notes

***Retailer Management***
The retailer management feature of the web site will allow the user to view, add, modify and delete
retailers. The user will be able to add the retailer company name and contact information for each
retailer. Once a retailer has been added they will appear on the list of retailers that can be associated
to a customer (from the customer management section).
The following details the information that the user will be able to add and update for each retailer.
- Retailer Name
- Address
- City
- State
- Zip
- Billing Address
- Billing City
- Billing State
- Billing Zip
- Formation Date
- Business Type; this is a pre-defined list user will chose from
- Legal Status; this is a pre-defined list user will chose from
- Primary Contact Name
- Primary Contact Title
- Primary Contact Phone
- Primary Contact Fax
- Primary Contact Email
- Secondary Contact Name
- Secondary Contact Title
- Secondary Contact Phone
- Secondary Contact Fax
- Secondary Contact Email
- Staff Member Name and Type (popup to add unlimited staff to a retailer, first and last name, and email address for each)
***Manufacturer Management***
The manufacturer management feature of the web site will allow the user to view, add, modify and
delete manufactures. Once a manufacturer has been added they will appear on the list of
manufacturers that can be associated to a product (in the product information section under
customer management).
The manufacturer object will utilize the same fields and attributes as the retailer object.

***Dashboard***
The dashboard feature will be the home page of the website. The user will be able to search for
customers, view a list of customers due for a follow-up within the next seven days, and the most
recent trouble tickets that have been submitted. The user will be able to click on the customer from
the follow-up list to view customer’s full information. Likewise the user will be able to click on the
trouble ticket to view the trouble ticket’s full information.

***Reports***
We need to be able to run the following reports, to download in excel or show in the browser in html format. If the report is generated

- Retailer with most tickets in x days
- Retailer tickets in x days (choose retailer)
- Manufacturer with most tickets in x days
- Manufacturer tickets in x days (choose manufacturer)
- Summary of tickets for x retailer, in x days
- Summary of tickets for x manufacturer, in x days
- Customer list by manufacturer (choose manufacturer)
- Customer list by retailer (choose retailer)

***User Management***
A web based user management system will be implemented. Users will log into a form before viewing anything else. All users (technicians) will be created by an administrator. The administrator account will show an additional link in the application to add or edit technicians/users. The administrator will be able to reset the passwords on other user’s accounts.

There will be three levels of users.
- Administrator (has access to add or edit anything, and has the extra link to add/edit users
- Technician (can add/edit customers, tickets, customer products, and customer equipment)
- Sales Rep (can add/edit customer info only if the customer is attached to the sales rep’s retailer, can view tickets created for customers attached to their retail store, CANNOT run reports, edit/create/delete tickets, add/edit/delete manufacturers, view tickets not of their retailer, or run any email functions.

***Email Notification***
Every customer created in the system will be associated with a technician. Every
morning, a scheduled process will check the database to see if any customers have scheduled
follow-ups on that day. If a customer has a scheduled follow-up, the system will send an email to
the associated technician informing them of the follow-up along with a link to the ticket.

***Assumptions***
Application interface will be designed to have a fluid width, minimum of 900px, maximum of 1100px (or similar).
The finished product must be cross-browser compatible with IE 7, IE 8, Firefox 3.5, and Safari 4
Source code must be provided to us when finished, to be installed on our own web server by us. We need to be able to edit the source code, so the code must be clear of any security techniques to only allow the programmer to edit.

Key:
Retailer = retail Store
Sales Rep = salesperson attached to a specific retailer
Customer = customer attached to a retailer, purchased a product from store
Manufacturer = manufacturer of a product a customer purchased from retailer
Technician = support person who handles tickets and follow ups
Product = purchased by a customer, can be more than one per customer
equipment = items attached to a product, can be more than one to a product
Service Company = attached to a retailer, can be attached to multiple retailers. retailers can have more than one service company. customers can only have one service company per product

Additional Project Description:
06/10/2010 at 19:34 CEST
We like jquery, so the winner of this project must be able to use it...thanks!

Skills required:
AJAX, Customer Support, Javascript, MySQL, PHP
Additional Files: aquatic_product_screen.jpg aquatic_item_add_edit_screen.jpg aquatic_ticket_screen.jpg
About the employer:
Verified
Public Clarification Board
Bids are hidden by the project creator. Log in as the employer to view bids or to bid on this project.
You will not be able to bid on this project if you are not qualified in one of the job categories. To see your qualifications click here.


$ 7500
in 60 days
$ 4000
in 35 days
Hire VALUEONWEB
$ 3500
in 45 days
$ 3000
in 30 days
$ 4450
in 65 days
$ 3000
in 60 days
$ 4285
in 35 days
$ 5000
in 45 days
$ 3000
in 45 days
Hire Georgia
$ 3200
in 25 days