HomeMy WebLinkAbout05 - Orange County Integrated Law & Justice ProjectCITY OF NEWPORT BEACH
CITY COUNCIL STAFF REPORT
Agenda Item No. 5
March 14, 2006
TO: HONORABLE MAYOR AND MEMBERS OF THE CITY COUNCIL
FROM: John Desmond, Support Services Division Commander
Bob McDonell, Chief of Police
SUBJECT: APPROVAL OF CONTRACT WITH COMPUTER DEDUCTIONS,
INCORPORATED, FOR THE ORANGE COUNTY INTEGRATED
LAW AND JUSTICE PROJECT
RECOMMENDATION:
1. Approve the attached scope of work and sole source award to Computer
Deductions, Incorporated (CDI), in the amount of $49,680 from COPS
Technology 2002 Grant Account #7017- C1820747 to improve and
enhance the Orange County Integrated Law and Justice (OCILJ)
Automated Subpoena Processing Application.
2. Authorize the City Manager to execute a standard Professional Services
Agreement with CDI as approved by the City Attorney.
DISCUSSION:
Background:
On March 9, 2004, Council approved the acceptance of approximately $1.9
million in grant funds to administer on behalf of the Orange County Integrated
Law and Justice Project (comprised of the entire Criminal Justice System in
Orange County). In that prior communication, Council was advised that the City
of Newport Beach was requested to administer the grants for the Project by the
Orange County Chiefs' and Sheriffs Association.
Under a prior contract, CDI completed the first version of the Automated
Subpoena Processing Application. In February 2005, Council approved another
contract with CDI to complete a number of enhancements and improvements that
had been identified to make the system more effective and more user friendly for
all the Orange County Criminal Justice Agencies. Since the completion of that
phase of the Project, additional improvements have been identified by the users,
which will further enhance the Automated Subpoena Processing Application.
These improvements include the following items:
Approval of CDI Contract
March 14, 2006
Page 2
1. Create a login based security system for the OCILJ Web site that will allow
for the updating of subpoena data.
2. Create an Audit Trail that will record all user - initiated updates to the
database.
3. Add a "View Subpoenas" section to the OCILJ Web site.
4. Create a process to trail subpoenas and make notifications via e-mail
directly to the officers.
5. Add the ability to export subpoena data directly from the OCILJ Web site
to Microsoft Excel spreadsheets.
6. Create the ability for authorized users (Court Liaison Officer, DA, Court, or
Public Defender) to resend electronic subpoenas or cancellations directly
from the OCILJ Web site.
CDI has been involved in several technology projects with the OCILJ and serves
as the technology vendor to the Orange County Sheriffs Department, a key
agency in this Project. Due to CDI's ongoing involvement with these projects and
their specific ongoing work on the Electronic Subpoena Processing Application, it
is appropriate for this contract to be approved on a sole source basis.
This report recommends approval of this contract with CDI in the amount of
$49,680 to be funded from COPS Technology 2002 Grant Account #7017 -
C1820747.
Environmental Review:
None required.
Funding Availability:
All funds required for this Project are Federal grant funds, authorized for
expenditure under the 2002 COPS Technology Grant Program.
Prepared and Submitted by:
4�i vj�
John Desmond, Captain
Support Services Division Commander
Attachment: CDI Scope of Work
Approved by:
G't 4�/
Bob McDonell
CHIEF OF POLICE
COMPUTER DEDUCTIONS, INC.
8680 Greenback Lane • Suite 210 e Orangevale, CA 95662
Proposal to Add Enhanced Security and Reporting Capabilities to the ILJ Automated
Subpoena Processing Application
This document describes the following proposed enhancements to the ILJ Automated Subpoena Processing
Application: (All of these enhancements will be developed with Microsoft's Visual Basic.Net, ASP.Net, ASP,
and SQL Server 2000 T -SQL.)
1. Create a login based security system for the ILJHome web site. This only applies to pages on the ILJ
web site that allow for the updating of subpoena data, such as the trailing, resending, and cancelling of
subpoenas. The Update page for the Officer Name Index will also be password protected. All of the
other read -only pages on the site will remain security -free.
2. Create an Audit Trail that will record all user - initiated updates to the database. These updates will
include all database Inserts, Updates, and Deletes.
3. Add new filtering options to the "View Subpoenas" section of the ILJHome web site.
4. Create a process to trail subpoenas. All trailed subpoenas will be displayed in green on the web site.
Also, all emails sent out for trailed subpoenas will be indicated as such. This will let the officer know
that changes have been made to the original subpoena. Included with this will be a process that will
allow authorized users to issue Miscellaneous (In- House) Subpoena Cancellation Notices.
5. Add the ability to export subpoena data directly from the ILJHome web site to Microsoft Excel
spreadsheets.
6. Create the ability for authorized users, such as Court Liaisons, to resend email subpoenas directly
from the ILJHome web site. Authorized users will be able to send cancellation notices via email to
their officers for any DA, Court, or Public Defender subpoena.
1.) Log -in Security:
Description:
Create a username /password based security system that will be managed by an administrator from each
agency. This system will allow each agency to limit access to various portions of the ILJHome web site to
authorized users. It will also be able to be expanded to accommodate future ILJ applications. Initially, the
system will be used to limit access to the "Send Subpoenas" (in- house), "Officer Names ", and "Subpoena
Trailing" (new) portions of the ILJHome web site. Passwords will have a length of 3 to 8 characters.
Note: there will be no login security requirements to simply view the subpoenas as they currently exist on the
web site.
Intro'
This will be a user signup and authentication system which will allow authorized users to create members only
areas via a website. The system will use Microsoft's SQL 2000 database to store user information and the
date /time of user logins as well as their login counts.
Feature Summary:
1. Basic pages include signup, login, forgotten password, & edit profile
2. Simple, adjustable, well commented User Signup and Authentication system
3. Simple configuration & setup
4. Uses SQL Server Database for user information and settings
5. Logs date and time of user logins, as well as login counts, all of which are stored in the SQL file for
ease of reference
6. User data entry checking to enforce valid information at signup
7. Super Admin can create other admin accounts
web: http: / /-www.cdi- hq.com • phone: 916.987.3600 • fax: 916.987.3606 • e -mail: corp @cdi- hq.com
8. Admin can only work with its agency users
Database Fields:
1.
Information stored includes:
2.
Username
3.
Password
4.
Name
5.
Agency
6.
User level
7.
Email
8.
Address
9.
Phone number
10.
Birth date (optional)
11.
Gender (optional)
12.
Signup date & time
13.
Last login date & time
14.
Disable flag
15.
Login counter
Required Steps:
• Design a database to manage all user accounts. (Estimated time: 60 hours)
• Design a series of web pages that will allow administrators to manage these accounts. (Estimated
time: 70 hours)
Write the backend programs /web services that will provide the interface between the database and the
new web pages. Also, integrate the security system into the current ILJHome web site. (Estimated
time: 90 hours)
Time: 220 Hours
2.) Audit Trail
Description:
Create an Audit Trail that will record all user - initiated modifications to the database. The transactions that will
be recorded are all updates to the ILJ Officer Name Index, all Miscellaneous (In- House) Subpoenas that are
issued (or cancelled), all subpoenas that are trailed, and all subpoenas that are resent (see item 5). The User
Name, as well as the date and time of the modification, will be saved to the Audit Trail. Where database
Updates are concerned, the original data will also be saved. This applies specifically to updates to the Officer
Name Index. The Audit Trail data will be stored in the same ILJ SOL Server database, which is located on the
clustered ILJ SOL servers (Corbina and Perch).
A new web page will be created that will allow authorized users to view the Audit Trail data. Audit Trail records
will be able to be selected by User Name, Subpoena Key, or Officer Badge ID, depending on which audited
data is being viewed.
Required Steps:
• Create the Audit Trail tables in SOL Server. (Estimated time: 8 hours)
• Modify the exiting ILJ web -based update transactions to write to the Audit Trail tables whenever a
database modification takes place. (Estimated time: 24 hours)
• Create a web page that will allow authorized users to view Audit Trail data. (Estimated time: 40
hours)
Time: 72 Hours
3.) New Subpoena Filtering Options:
Description:
Add the ability to select and display subpoenas by Appearance Date, DR Number, Agency Case Number,
Court Case Number, and Defendant Name. This will be done by adding a new dropdown listbox to the top of
the "View Subpoenas" page that will contain each of these fields. There will also be a corresponding input box
next to the dropdown list, which will be where users enter the specific data that will be used to filter the
subpoenas by. The resulting list will contain all matching subpoenas. A new column for the defendant's
name(s) will be added to the main "View Subpoenas" data grid. To accommodate the size this new column will
require, the sidebar links ("View Subpoenas ", "Send Subpoenas ", and "Officer Names ") will be moved to the
top of the site.
Required Steps:
• Modify the "View Subpoenas" web site by adding the new filtering options (dropdown listbox and input
box). Estimated time: 16 hours)
• Write the backend code and SOL stored procedures that will be needed to do the filtering. (Estimated
time: 40 hours)
Time: 56 Hours
4.) Subpoena Trailing /In -House Cancellation Notices:
Description:
Create the ability for authorized users to "trail" subpoenas. This process will be accomplished by having the
user retrieve a copy of the original subpoena data in "update mode ", allowing them to modify it accordingly, and
then resubmitting it to the officer by email. (All modifications to the subpoena will be saved in a separate
database. None of the original data will be lost, and both the original and the trailed subpoena data will be
viewable from the ILJHome website.) Subpoenas can be trailed as many times as is needed. During the
trailing process, all data elements may be changed except for the Court Case Number, Agency Case Number,
DR Number, and Defendant Names(s).
This process also includes the ability for authorized users to send cancellation notices to officers that have
received in -house (Miscellaneous) subpoenas.
Required Steps:
• Design a web site (with the backend programming) that will allow users to retrieve, modify, and
resubmit subpoenas, or issue In -House subpoena cancellation notices. (Estimated time: 80 hours)
• Create a database to hold and maintain trailed subpoenas. (Estimated time: 20 hours)
Time: 100 Hours
5.) Subpoena Export to Microsoft Excel:
Description:
Add the ability to export subpoena listings from the ILJHome web site to Excel spreadsheets. The data to be
exported will include all data for the listed subpoenas; not just what is shown on the site. (This enhancement
requires the user to have a valid copy of Microsoft Excel installed on their PC.)
Required Steps:
• Write the programming required to perform the export to Excel. (Estimated time: 40 hours)
Time: 40 Hours
6.) Resend Subpoenas:
Description:
Create a process that will allow authorized users to resend individual subpoenas directly from the ILJHome
web site. To resend a subpoena, the user will pull it up from the "View Subpoenas" web page by clicking on
the subpoena Key link. At the bottom of this page, authorized users will see a new button that, when clicked,
will resend the subpoena to the officer. When this happens, the "Issue Date ", which is stored in the database
and displayed on the web site, will be updated with the current date. (The date /time of the when the subpoena
was originally added to the database will not be modified.) An Audit Trail record will also be created for this
transaction.
Required Steps:
• Modify the "Display Subpoena" web page by adding a "Resend Button ". This button will only be
viewable to authorized users. (Estimated time: 8 hours)
• Write the programming required to resend the subpoenas and update the database, including the Audit
Trail table. (Estimated time: 32 hours)
Time: 40 Hours
An additional 24 hours will be required for user testing and implementation.
Total Time: 552 Hours
Hourly Rate: $90.00
Total Cost: $49,680.00