SWAM Accounting System Development

We propose the development of a mobile and desktop application integrated with an online database (either cloud-based or hosted on a local server in Akkar Region – Jurd AlKaytee). This system will support the management of a long-term solid waste management project (5+ years) for the Union of Municipalities (covering 15 villages and towns).

1. Key Functionalities & Data Management

A. Revenue Management

1. Fee Collection:
   • Monthly fee collection from 20,000 households and 1,000 commercial/industrial institutions.
   • Institutions will be categorized based on size and activity type to implement a dynamic pricing model.
2. Donations Management:
   • Tracking and managing third-party donations received for the project.

B. Expense Management

1. Employee Salaries:
   • Monthly expense tracking for employees, including collectors and technical staff.
2. Machinery & Equipment Maintenance:
   • Logging maintenance costs for heavy machinery.
   • Tracking fuel consumption for operational efficiency.
   • Depreciation calculation for machinery and other assets.

2. Fee Collection Details & Multi-Currency Support

• Fees will be predefined in USD, but collections may occur in USD or Lebanese Pounds (LBP).
• The system must support real-time currency conversion and maintain records of the exact amounts collected in each currency.

3. User Management & Access Control

• Total Users: 22
• Role-Based Access:
  • Users will have restricted access based on their designated geographical area and role (e.g., fee collection, administration, reporting).
• User Distribution:
  • Each village will have one collector, except:
    • Mishmish: Three collectors
    • Fnaidek: Five collectors
• Superuser/Administrator:
  • Full system access for managing users, data integrity, and reporting.

4. Database Growth & Scalability

• Estimated Data Volume:
  • 21,000 transactions per month → ~1,400,000 records in 5 years (considering 5% annual growth).
• Key Tables & Relationships:
  1. Collection Table: Records all fee collections, linked to client records.
  2. Clients Table: Stores demographic and institutional data (estimated 21,000+ records growing annually).
  3. Employees Table: Stores information on all personnel involved in the project.
  4. Employee Salaries Table: Tracks salary payments linked to the Employees Table.
  5. Machinery Table: Stores machine details and job assignments.
  6. Fueling & Maintenance Tables: Track machinery-related expenses via a one-to-many relationship with the Machinery Table.

5. Integration & Data Import

• Existing Data Source: Client data is available on Esri 123 (ArcGIS) and can be exported to Excel.
• Integration Requirement: The system should allow importing client data from ArcGIS/Excel, reducing manual data entry time.

6. Offline Data Entry & Synchronization

• Given the poor mobile network and internet coverage in Lebanon, the system should:
  • Store data offline when no connection is available.
  • Automatically sync with the central database once an internet connection is restored.

7. Reporting & Analytics

The system should support basic queries and reports, including:

1. Non-Compliant Clients Report:
   • List of households/institutions by village and collector.
   • Display non-compliance rates (number & percentage).
2. Revenue Tracking:
   • Monthly revenue vs. expected revenue.
3. Aggregated Reports:
   • Total collections and expenses summarized by geographical area and date.
   • Revenue vs. Expense balance sheet.
4. Training on the system for the technical unit