Project Description
I need to completely re-engineer our current door-access controller so it speaks MQTT, then wrap that functionality inside a Python-based web application backed by an SQL database. The hardware vendor has supplied only raw documentation; no existing code can be reused, so everything—from the MQTT communication layer to the UI—must be rewritten.
Core scope
The finished system must let us issue open/close commands in real time, store every transaction, and deliver extensive reporting. Report generation, filtering options, and door control are all mandatory. The client will periodically push raw data straight into the database; the application should pick it up automatically and fold it into dashboards and downloadable PDFs that can easily exceed 50 numbered pages when required.
Reporting details
Daily activity, error logs, and access logs are standard. On top of that, the most critical outputs are department-wise, company-wise, and total daily pass counts, with a monthly summary. Filters should allow narrowing by date range, department, company, door, and error type.
proper Dashboard with charts required.
Preferred stack & tools (you may propose equivalents)
• Python 3.x with a framework such as Django or Flask
• paho-mqtt (or similar) for broker communications
• Microsoft SQL database required
• PDF/Excel export via ReportLab, WeasyPrint, or Pandas
Expected deliverables
1. Clean, well-commented Python source that connects to the MQTT broker, issues door commands, and listens for acknowledgements.
2. Web front-end with secure login, live status panel, rich filters, and report viewer.
3. Normalised SQL schema plus migration scripts.
4. Report engine producing paginated PDFs/Excel files that satisfy the >100-page requirement without manual intervention.
5. Unit/integration tests covering critical paths.
6. Deployment guide and user manual.
Acceptance criteria
• Door opens/closes within two seconds of command and logs the event.
• All specified reports generate correctly and reflect real-time data.
• Filters return accurate results in under one second on a million-record dataset.
• Code passes provided test suite and want workable solution which runs on windows PC.