Scope for the GRA Custom Components
Background
We currently have a site that is used as a way for our customers to access their
records. We handle things like their commissions and supplies etc. We compile this
data from a secure database and upload the files to this portal.
The site is currently built in phpNuke and continues to give problems. It is also
hosted on an expensive Windows server and runs on MSSql. We are tired of dealing
with the problems of windows and Nuke and want to move to LAMP Joomla
environment.
What we’re looking for
We need a Joomla Component built that handles accessing a database and printing
to the screen reports based on the data.
1. There are three separate reports that need to be generated. Each one will
have slightly different specifics, and will be built from different tables.
2. The reports will be mainly sorted by the name of the Customer.
3. There will need to be some sort of profile page built for when a customer’s
name is clicked.
4. There will be a fourth report that will be built at a later phase, and that job
will be handled by the winning bidder as well (but as a separate project of
course)
5. We also need to create a series of permissions that will give users different
levels of access.
6. The data that these reports are created from are uploaded via CSV files
(either daily, weekly or monthly) – so there needs to be some sort of upload
feature. I will send examples of these files to the winning bidder.
7. When the new commission data is uploaded, because it appends, there needs
some way to remove this data if it turns out to be bad. Currently we can
delete any month of commission data from a table… a more elegant solution
would be nice.
8. Our current user list would need to be moved and would need to work with
the new permissions.
What you’ll have
The existing site is mainly written in php. This should make it pretty easy (if
youre familiar with nuke) to simply grab a lot of the SQL queries and
translate them
You will also be given access to the live site, so that you can see how the
reports are formatted and sorted, everything. So there shouldn’t be any
worry about how to lay things out or how to organize data.
You will have access to a project manager via phone and AIM
(voodoomagiccrab).
Concepts
Our company supports other companies that sell insurance. This means that there
are a few concepts in the data that you should understand:
Agencies – these are insurance agencies (companies.)
Agents – Agents are the employees of insurance agencies – the people who
sell the insurance.
Customers – these are the companies that agencies sell insurance to.
Groups – These are groups of people who work for the customers who all
share a plan.
Plans – These are the individual types of insurance policies that agencies sell.
Breakdown of database hierarchy
1. CID (Customer Identification Number) – this is the code that identifies a
company. For example, MyCompany Inc, has a CID of 02932.
2. Group Number – this is an id linking an insurance plan and a set of
employees. So for example, MyCompany Inc, has three divisions, and each
division has a different set of benefits, that means that each division has it’s
own Group Number. For the health care groups, everyone in a group has the
SAME benefits.
3. There are other types of insurance that we offer (ancillary services); they
each have their own group number (vision may be 00932 and dental may be
09382) and individuals in the company are associated with them for sorting
purposes. But each employee may have different benefits for these other
services:
For example:
Everyone in the company is in one health care group. Five employees
also have vision benefits. There has to be a group number for the
vision package. HOWEVER, each individual may have choose a
different set of vision benefits – one may pick glasses coverage, and
another may pick doctor’s visits.
Please also see the attached permissions document.
The next three sections describe the reports.
Rate Renewal
1. This is a page for agents to see which groups (clients) they have are in the
process of renewing (this is a quarterly process.) We upload this data via a
csv file. We generally upload the file once a week.
2. When the name of the group (or client) is clicked on, a pdf is generated and
begins to download. We would like to change this to a little PDF icon that the
user can click on and download the file (this is based on CID.)
3. Because a CID can have multiple Group Numbers, we would like it so that
when a group number is clicked the, it generates an information page for that
group. The information page will pull up – the same as item 4 in Client Data.
4. The PDF is form that we have that is filled out automatically.
a. PDF displays:
i. Group Name
ii. CID
iii. Renewal Period
iv. The Agent’s Name
v. The Agent’s Phone Number
5. Every time a new CSV file is uploaded, the data for Rate Renewals is cleared,
and the new data loaded over it. It is one file.
6. Rate Renewal is sorted by CID.
7. Permissions
a. Support Staff
b. Agent
c. Agency Support
d. Agency
e. Admin and higher
Client Data
1. This is a listing of all a user’s clients and some information pulled from the
database (which is updated weekly from those csv files.) We generally
upload the file once a week.
a. Master Group File – this is the file that has contact info and the
primary key is CID.
b. Subscriber Listing – This is a list by group number of each individual
contract.
c. Paid to date file – a list by group number that displays a date – this
updates the master group’s paid date. This will update every day – we
would like to automate this process eventually.
2. On the list view we need to display:
Group Name
Group Number
CID
Paid to Date
Renewal Date
New Hire Agreement
SIC Code
Cluster Code
3. When you click on the name of the client, it takes you to a details page about
the client – this page is built from the custom database we upload in csv
format.
a. It lists basic information about the group (client – the clients are
businesses) – pretty much every field from the master group file.
b. It lists the insurance plans they are using (Basically a key/legend that
shows all the plans being used by individuals in the group)
c. It lists the employees who have insurance under the group plan.
4. Every time a new CSV file is uploaded, the data for Client Data is cleared, and
the new data loaded over it.
5. Search feature
6. Order by feature
7. Paging feature
8. Permissions
a. Support Staff
b. Agents
c. Agency Support
d. Agency
Commissions
This is a similar listing to client data; only it shows an agent how much commission
they have been paid for signing up the client over a period of time.
1. It need storable by
a. Carrier
b. Statement Month:
c. Agent List:
2. It needs to be searchable by
a. A Group Number
b. A Group Name
c. An Agent Number
d. An Agent Name
3. It needs to be downloadable as a PDF or a CSV or HTML
4. Every time a new CSV file is uploaded, the data for Commissions is kept the
same, and the new data APPENEDED to the data.
5. When the new commission data is uploaded, because it appends, there needs
some way to remove this data if it turns out to be bad. Currently we can
delete any month of commission data from a table… a more elegant solution
would be great, but we could do that later.
6. Permissions
a. Agent
b. Agency
c. Accountant
d. Admin