PHILADELPHIA TOBACCO RETAILER DATABASE
Statement of Work
City of Philadelphia
Department of Public Health
OPEN: 10/12/2016 CLOSED 10/20/2016
Due to a Typo in the Scope of Work section, the Timeline for this BID has been extended 3 days.
Please note that we are looking primarily for a quote to (1) Convert our existing MS access to the Web, (2) Add additional functionality, and (3) provide the ability to interface with external agencies via Forms, APIs or other data exchange methods.
The Philadelphia Tobacco Retailers Database (PTRD) is currently hosted by the Division of Environmental Health Services as a SQL database using MS Access for front end management. The Division of Chronic Disease Prevention maintains the data and runs analyses. The PTRD stores data on the status of tobacco permits, compliance with tobacco sales laws, and tickets for sales violations. The database is small (<10,000 records).
Convert the current MS Access Frontend PTRD Database to a Web Application using a SQL Server Backend. Add additional functionality to the new Web Application. The City is looking for proposals that offer a cloud-based solution using standard BI tools in which the city can utilize for suture development and enhancements. The proposal can offer a hosted or on-premise solution.
SCOPE OF WORK
REVISED 10/15/2016: Scope of Work Description was the wrong description, this Bid will now be open until 10/20/2016.
Functionality Phase 1 – Porting of the Current MS Access Database to the Web
- Establishment Table: records on establishments that sell tobacco including permit number, address, store type, and other business owner and store information. A unique establishment ID is assigned and is linked to the business owner. Note: data entered manually by EHS using a web form when the tobacco permit is issued
- Compliance Check Table: records on compliance checks and their outcomes as recorded by the contractor who does the checks (Health Promotion Council). Linked using establishment ID.
- The list of compliance checks to be done are generated by pulling a subset of retailers in the Establishment Table and emailing an excel file to be uploaded to the contractor’s database
- After the checks are completed, data on the results of the checks are uploaded from an excel file emailed back by the contractor
- Ticket Table: records on tickets that were issued as a result of failed compliance checks. Data linked with a unique ticket number associated with the establishment (which may receive multiple tickets)
- Data manually entered by EHS after automatic emails are generated from any failed compliance check
- Data from the Office of Administrative Review on ticket outcomes is also manually merged to this table
Functionality Phase 2 – Enhanced Features to the new Web Application
- Integrate additional data sources
- Food and Drug Administration publically available data on tobacco sales compliance available here: http://www.accessdata.fda.gov/scripts/oce/inspections/oce_insp_searching.cfm
- Data on state issued tobacco permits
- Access and data editing capabilities in the field via tablets
- Develop online tobacco permit application system (currently done with paper), which is linked to the database and can be integrated with the existing online permit renewal system or a reworked online permit renewal system.
- Would need to reference existing and additional data sources to determine eligibility criteria
- Integrate data from an outside contractor (for the Compliance Check Table)
- Build in additional checks on data quality and management
- Better automate many data updating tasks and common reporting queries
- Be able to interface or download data for use with popular statistical analysis programs including R, SPSS, and GIS
- Incorporate location-based decision making factors into the permit application system (eg, to flag any retailers that are within 500 ft of a school)
- Develop Detailed requirements analysis including reporting requirements
- Development of a Web application for phases 1 and 2 according to specs.
- Enhance Website with security features to protect the privacy of data as well as implement administrative and user level security functionality
- Perform unit and system testing on the Complete Web Application
- Create System Documentation
- Create User Guide and Training Materials
DELIVERABLES and Time Frame
- Project Kick-off, project start
- Detailed Requirements Document
- Detailed Report Layouts
- Within 10 days of the start of the project
- Test Ready Application for Phase 1
- Within 15 days of the start of the project
- Production ready application for Phase 1 (including reports)
- Test ready application for Phase 2
- Within 20 days of the start of the project
- Production ready application for Phase 2
- Online availability of all system reports for review
- Within 30 days of the start of the project
- Completed Production Ready Application Phases 1 and 2
- Completed System Reports
- Within 35 days of the start of the project
- System documentation including architectural diagram and data flow diagrams
- User guide/training document
INSTRUCTIONS FOR SUBMISSION
Your proposal should include a short description of your qualifications, experience with similar projects, an overview of your firm or organization, and a brief description of your solution. You may include high level architectural diagrams of the proposed solution and URLs for any online examples you may have. With regards to pricing we are not looking for a flat fee for this project but rather a detailed breakdown which will reflect an hourly rate and the number of hours associated with each task to be performed as well as other costs related to subscriptions, licensing or cloud services if applicable. Please email all documents to Meagan.Pharis@Phila.gov and firstname.lastname@example.org . Your submissions must be received on or before 10/20/2016. Late submissions will not be considered. Please limit the size of the proposal document to a maximum of 12 pages.
Please post any questions as a comment below this solicitation on bigideasphl.com so that additional information provided in any Q&A will be available for all potential submitters to review. Any questions sent via email will be directed back to the comment section below the solicitation on bigideasphl.com.
OPEN: 10/12/2016 CLOSED 10/20/2016