# Dozar & Tripper Application - Technical Specification

## 1. Project Overview

**Project Name:** Dozar & Tripper Management System  
**Client:** Kathmandu Municipality Environment Department  
**Technology Stack:** PHP 8.x, MySQL 8.x, HTML5, CSS3, JavaScript, OpenStreetMap API  
**Server:** XAMPP (Apache/MySQL)  
**External APIs:** OpenStreetMap Nominatim (free, no API key required)  

---

## 2. System Overview

A web-based fleet management and work tracking system for municipal solid waste management operations. The system tracks:

- **Dozar (Excavator):** Heavy machinery work tracking with odometer readings, time logging, and photo documentation
- **Tripper (Loading/Unloading):** Vehicle loading and unloading operations with dual-collector verification

---

## 3. User Roles & Permissions

| Role | Description | Permissions |
|------|-------------|-------------|
| **Admin** | System Administrator | Full access - user management, all projects, all data, reports, settings |
| **Collector** | Field Collector (configurable) | Access assigned by admin: Dozar only OR All modules (Dozar + Loading + Unloading) |
| **Viewer/Manager** | Department Head | View all data across projects, generate reports, no editing |

---

## 4. Database Schema

### 4.1 Projects/Locations Table

```sql
CREATE TABLE projects (
    id INT PRIMARY KEY AUTO_INCREMENT,
    project_name VARCHAR(100) NOT NULL,
    project_code VARCHAR(20) UNIQUE NOT NULL,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Default projects:
-- 1. Bishnumati
-- 2. Bancharre
-- (More can be added later)
```

### 4.2 Drivers Table

```sql
CREATE TABLE drivers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(100) NOT NULL,
    phone_number VARCHAR(20),
    address VARCHAR(255),
    license_number VARCHAR(50),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
```

### 4.3 Vehicles Table

```sql
CREATE TABLE vehicles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    vehicle_number VARCHAR(20) UNIQUE NOT NULL,
    vehicle_type VARCHAR(50) NOT NULL,
    vehicle_model VARCHAR(50),
    capacity VARCHAR(50),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
```

### 4.4 Deployed From Table (Source Entity)

```sql
CREATE TABLE deployed_from (
    id INT PRIMARY KEY AUTO_INCREMENT,
    entity_name VARCHAR(100) NOT NULL, -- KMC, Private Company, etc.
    entity_type ENUM('kmc', 'private', 'other') DEFAULT 'other',
    contact_info VARCHAR(100),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Default entries:
-- 1. KMC (Kathmandu Metropolitan City)
-- 2. Private Entity
-- 3. Other
```

### 4.5 Users Table

```sql
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    role ENUM('admin', 'collector', 'viewer') NOT NULL,
    collector_access ENUM('dozar_only', 'tripper_loading_only', 'tripper_unloading_only', 'all_modules') DEFAULT 'dozar_only', -- Only applies when role='collector'
    assigned_projects VARCHAR(255), -- Comma-separated project IDs, NULL for admin/viewer (all)
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
```

### 4.6 Dozar (Excavator) Records Table

```sql
CREATE TABLE dozar_records (
    id INT PRIMARY KEY AUTO_INCREMENT,
    project_id INT NOT NULL,
    deployed_from_id INT NOT NULL, -- KMC or Private Entity
    vehicle_id INT NOT NULL, -- From vehicles table
    driver_id INT NOT NULL, -- From drivers table
    created_date DATE NOT NULL,
    collector_name VARCHAR(100) NOT NULL,
    work_location VARCHAR(200) NOT NULL,
    work_length_hours DECIMAL(10,2) NOT NULL, -- Work length in HOURS
    
    -- Self-declared location (collector enters manually)
    collector_entered_address VARCHAR(255),
    
    -- GPS captured location (auto, hidden from collector view)
    gps_latitude DECIMAL(10,8),
    gps_longitude DECIMAL(11,8),
    gps_address VARCHAR(255), -- Reverse geocoded from OpenStreetMap
    gps_captured_at TIMESTAMP NULL,
    
    start_odometer DECIMAL(10,2) NOT NULL, -- In HOURS
    end_odometer DECIMAL(10,2) DEFAULT NULL, -- In HOURS
    start_time TIME NOT NULL,
    end_time TIME DEFAULT NULL,
    entry_location VARCHAR(200),
    group_name VARCHAR(50),
    remarks TEXT,
    photo_path VARCHAR(255),
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id),
    FOREIGN KEY (deployed_from_id) REFERENCES deployed_from(id),
    FOREIGN KEY (vehicle_id) REFERENCES vehicles(id),
    FOREIGN KEY (driver_id) REFERENCES drivers(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);
```

### 4.7 Tripper Records Table

```sql
CREATE TABLE tripper_records (
    id INT PRIMARY KEY AUTO_INCREMENT,
    project_id INT NOT NULL,
    deployed_from_id INT NOT NULL, -- KMC or Private Entity
    vehicle_id INT NOT NULL, -- From vehicles table
    driver_id INT NOT NULL, -- From drivers table
    created_date DATE NOT NULL,
    work_location VARCHAR(200) NOT NULL,
    entry_location VARCHAR(200),
    group_name VARCHAR(50),
    material_type VARCHAR(100),
    quantity_weight DECIMAL(10,2),
    
    -- Loading Information (entered by loading collector)
    loading_collector_id INT,
    loading_location VARCHAR(200),
    loading_collector_address VARCHAR(255), -- Self-declared by loading collector
    loading_gps_latitude DECIMAL(10,8),
    loading_gps_longitude DECIMAL(11,8),
    loading_gps_address VARCHAR(255), -- Reverse geocoded
    loading_gps_captured_at TIMESTAMP NULL,
    loading_start_odometer DECIMAL(10,2) NOT NULL, -- In KM
    loading_time TIME,
    loading_photo_path VARCHAR(255),
    loading_remarks TEXT,
    
    -- Unloading Information (only these fields - entered by unloading collector)
    unloading_collector_id INT,
    unloading_address VARCHAR(255), -- Unloading location address
    unloading_place VARCHAR(200), -- Unloading place
    unloading_end_odometer DECIMAL(10,2), -- In KM
    unloading_gps_latitude DECIMAL(10,8),
    unloading_gps_longitude DECIMAL(11,8),
    unloading_gps_address VARCHAR(255), -- Reverse geocoded
    unloading_gps_captured_at TIMESTAMP NULL,
    unloading_time TIME,
    unloading_photo_path VARCHAR(255),
    unloading_remarks TEXT,
    
    -- Status
    status ENUM('pending_unloading', 'completed', 'cancelled') DEFAULT 'pending_unloading',
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id),
    FOREIGN KEY (deployed_from_id) REFERENCES deployed_from(id),
    FOREIGN KEY (vehicle_id) REFERENCES vehicles(id),
    FOREIGN KEY (driver_id) REFERENCES drivers(id),
    FOREIGN KEY (loading_collector_id) REFERENCES users(id),
    FOREIGN KEY (unloading_collector_id) REFERENCES users(id)
);
```

---

## 5. Application Architecture

### 5.1 Directory Structure

```
dozartripper/
├── config/
│   ├── db.php           # Database connection
│   └── config.php       # Application settings
├── includes/
│   ├── header.php       # Common header
│   ├── footer.php       # Common footer
│   ├── auth.php         # Authentication check
│   └── functions.php    # Helper functions
├── css/
│   └── style.css        # Main stylesheet
├── js/
│   └── main.js          # JavaScript functions
├── uploads/
│   ├── dozar/           # Dozar photos
│   └── tripper/         # Tripper photos
├── login.php            # Login page
├── dashboard.php        # Main dashboard
├── dozar/
│   ├── index.php        # Dozar records list
│   ├── add.php          # Add new record
│   ├── edit.php         # Edit record
│   └── view.php         # View record details
├── tripper/
│   ├── index.php        # Tripper records list
│   ├── add_loading.php  # Add loading entry
│   ├── add_unloading.php # Complete unloading
│   └── view.php         # View record details
├── users/
│   ├── index.php        # User management
│   ├── add.php          # Add user
│   └── edit.php         # Edit user
├── reports/
│   └── index.php        # Reports dashboard
└── logout.php           # Logout handler
```

### 5.2 Authentication Flow

```
[Login Page]
    ↓
[Validate Credentials]
    ↓
[Create Session] → Store user_id, role, collector_access, full_name
    ↓
[Role-based Redirect]
    ├── admin → Dashboard (full menu)
    ├── collector (dozar_only) → Dozar module only
    ├── collector (tripper_loading_only) → Tripper Loading only
    ├── collector (tripper_unloading_only) → Tripper Unloading only
    ├── collector (all_modules) → Dozar + Tripper (Loading + Unloading)
    └── viewer → Reports/Dashboard only
```

### 5.3 Tripper Workflow

```
[Loading Collector Login]
    ↓
[Create New Trip]
    → Enter: Vehicle Info, Driver, Loading Location, Material, Photo
    ↓
[Save as "Pending Unloading"]
    ↓
[Unloading Collector Login]
    → View "Pending Unloading" trips
    → Select trip to complete
    → Enter: Unloading Location, Odometer, Photo, Remarks
    ↓
[Save as "Completed"]
```

---

## 6. Key Features

### 6.1 Dozar Module
- **Smart Odometer:** When selecting a vehicle, auto-fill start odometer from last end reading
- **Auto Work Length:** When start & end odometer entered → auto-calculate work length (end - start)
- **Auto Time:** Current time auto-fills for start time
- **Auto Vehicle Details:** When vehicle selected → auto-fill vehicle type/model from vehicle table
- **Auto Driver Details:** When driver selected → auto-fill phone/address from driver table
- Record start/end odometer readings (**in HOURS**)
- **Work Length** field (in hours) - auto-calculated or manual entry
- Track working hours (start/end time)
- Capture and store work photos
- Assign work locations and groups
- **Data Validation:** Warn if end odometer is less than start
- View/Edit own records (based on permissions)

### 6.2 GPS Location Tracking
- **One-Click GPS Capture:** Collector clicks button to get current GPS coordinates
- **Auto Time:** GPS capture timestamp automatically recorded
- **Address Resolution:** System uses OpenStreetMap Nominatim API to reverse geocode GPS to address
- **Dual Address System:**
  - Collector enters their self-declared location (visible to them)
  - System secretly captures GPS + resolved address (hidden from collector, for admin verification)
- **Admin Verification:** Admin can compare self-declared address vs GPS-derived address to verify correctness
- **Timestamp:** GPS capture time is recorded
- **No Map Display to Collector:** GPS address is captured but NOT shown to collector (prevents manipulation)

### 6.3 Project/Location Management (Admin only)
- Create/Edit/Delete project areas (Bishnumati, Bancharre, etc.)
- Assign users to specific projects
- Each project has its own Dozar and Tripper records
- Filter records by project on dashboard and reports

### 6.4 Driver Management (Admin and Collector)
- **Admin:** Add/Edit/Delete driver records
- **Collector:** Can also add NEW driver if not in list
- Fields: Full Name, Phone Number, Address, License Number
- **Flexible Dropdown:** Select existing OR add new driver
- Search and filter drivers

### 6.5 Vehicle Management (Admin and Collector)
- **Admin:** Add/Edit/Delete vehicle records
- **Collector:** Can also add NEW vehicle if not in list
- Fields: Vehicle Number, Type, Model, Capacity
- **Flexible Dropdown:** Select existing OR add new vehicle
- Search and filter vehicles

### 6.6 Deployed From Management (Admin and Collector)
- **Admin:** Add/Edit/Delete deployment sources
- **Collector:** Can also add NEW deployed source if not in list
- Default options: KMC, Private Entity, Other
- **Flexible Dropdown:** Select existing OR add new source

### 6.7 User Management (Admin only)

### 6.8 Tripper Module
- **Smart Odometer:** When selecting a vehicle for loading, auto-fill start odometer from last end reading
- **Auto Vehicle Details:** When vehicle selected → auto-fill vehicle type from vehicle table
- **Auto Driver Details:** When driver selected → auto-fill phone/address from driver table
- **Trip Continuation:** After loading saved → auto-creates "Pending Unloading" list
- **Loading Entry:** Loading collector creates trip record with:
  - Vehicle info, driver, material
  - Loading location, start odometer (KM), photo
  - GPS capture for loading location
  - Status: "Pending Unloading"
  
- **Unloading Entry:** Unloading collector completes with:
  - Unloading address
  - End odometer (KM)
  - Unloading place
  - GPS capture for unloading location
  - Status: "Completed"
  
- **Simplified Unloading:** No need to re-enter vehicle info (already in loading)
- **Status Tracking:** Shows pending vs completed trips
- **Verification:** Admin can compare GPS addresses vs self-declared addresses

### 6.5 Project/Location Management (Admin only)
- Create/Edit/Delete project areas (Bishnumati, Bancharre, etc.)
- Assign users to specific projects
- Each project has its own Dozar and Tripper records
- Filter records by project on dashboard and reports

### 6.6 User Management (Admin only)
- Create/Edit/Delete user accounts
- Assign roles to users
- Assign users to specific project(s)
- Reset passwords
- Enable/Disable user accounts

### 6.10 Reports (Viewer/Manager+)
- **Custom Date Range:** Select any start and end date for reports
- **Preset Buttons:** Daily, Weekly, Monthly quick filters
- **Government Format:** Nepal government-style report layout
- **One-Click Print:** Print-optimized CSS, print directly from browser
- **Signature Fields:** 
  - Prepared by (collector signature)
  - Verified by (supervisor signature)
  - Approved by (head signature)
  - Date fields for signatures
- **Report Types:**
  - Dozar daily work summary
  - Tripper loading/unloading summary
  - Vehicle usage report
  - Collector productivity report
  - GPS verification report (self-declared vs actual addresses)
- **Project Filter:** Generate reports per project or all projects combined
- **Export Options:** Print directly, optional PDF/Excel export

---

## 7. Security Considerations

- Password hashing using `password_hash()` / `password_verify()`
- Session-based authentication
- Role-based access control (RBAC)
- SQL injection prevention using prepared statements
- XSS prevention using `htmlspecialchars()`
- File upload validation (image types only)
- Secure file naming to prevent overwrites

---

## 8. UI/UX Design

### Color Scheme
- Primary: #2E7D32 (Green - Environment Department)
- Secondary: #1565C0 (Blue)
- Accent: #FF6F00 (Orange)
- Background: #F5F5F5
- Text: #212121

### Layout
- Responsive design (mobile-friendly for field collectors)
- Fixed sidebar navigation
- Clean data tables with search/filter
- Modal forms for quick data entry
- Photo preview before upload
- **Print-Optimized CSS** - Special stylesheet for government report printing

---

## 9. Implementation Phases

### Phase 1: Core Infrastructure
- Database setup (including projects table)
- Authentication system
- Project management (Admin can add/edit projects)
- User management with project assignment

### Phase 2: Dozar Module
- CRUD operations for dozar records with project selection
- Photo upload functionality
- Listing and search (filter by project)

### Phase 3: GPS Location Tracking
- JavaScript Geolocation API integration
- One-click GPS capture button
- OpenStreetMap Nominatim API integration for reverse geocoding
- Hidden GPS fields (captured but not shown to collector)
- Admin view showing self-declared vs GPS addresses for verification

### Phase 4: Tripper Module
- Loading entry system with GPS capture
- Unloading completion system with GPS capture
- Status tracking per project

### Phase 5: Reports & Dashboard
- Summary dashboard (per project or all)
- Project-specific reports
- Data export

---

## 10. Next Steps

1. Approve this specification
2. Set up XAMPP environment
3. Create database tables (including projects)
4. Implement authentication
5. Build Project management module
6. Build Dozar module
7. Add GPS location tracking
8. Build Tripper module
9. Add reports
10. Testing and deployment
