Water-Can Delivery & Subscription Manager for a Local RO Water Supplier (Python/Flask + MySQL)

Water-Can Delivery & Subscription Manager (WCDM) is a web application for the 20-litre RO water-can businesses found on almost every Indian street: one small plant, a few delivery staff, and 200–400 households that take one, two or three cans a day on standing subscription. The owner currently runs this on a pocket diary and a stack of carbon receipt books — and every month ends with disputed bills and lost deposits.

The application keeps a customer and subscription registry (address, area, plan frequency, per-can rate, refundable can deposit), generates a daily route sheet per delivery person and area, and lets the delivery person mark each house as delivered or skipped for that day. At month end the system computes each customer's bill from the delivery log — deliveries × rate, plus deposit collected or refunded — records cash and UPI payments with reference numbers, and maintains a running dues ledger and a can balance (cans at the plant versus cans sitting in customers' homes).

Two roles cover the whole business: Owner/Admin (customers, plans, rates, bills, reports) and Delivery (route sheet and delivery marking, restricted to what that person delivers). The stack is deliberately beginner-sized — Python/Flask with server-rendered Jinja2 templates and MySQL — with a normalised schema, hashed passwords, parameterised SQL and a documented test-case set, which gives the project exactly what the IGNOU BCSP-064 evaluation looks for: a level-2 DFD, an ER diagram, a security section and a system you can fully demo from your own laptop.

Water-can suppliers bill 200–400 households a month from hand-tallied delivery marks kept in a diary and on chalkboards. Because the delivery log, the subscription terms and the can deposits live in three different places (diary, chalkboard, deposit notebook), monthly bills are slow to prepare and frequently wrong at plan changes; customers dispute can counts; deposits are lost track of when cans are returned; and there is no dues list, so collection depends entirely on the owner's memory. If the diary is lost, the entire business record disappears with it.

The supplier needs a single, secure, phone-friendly web application that holds customers, subscriptions and deposits in one database, generates the daily delivery round automatically, captures delivered/skipped status at the doorstep, computes monthly bills from that log instead of by hand, tracks payments and outstanding dues, and keeps a running can balance between the plant and the customers' homes.

Abstract

Packaged 20-litre drinking-water delivery is a large, informal micro-business in Indian cities. The supplier's records live in a pocket diary: who takes how many cans, whose deposit is pending, who has not paid since March. Monthly billing means adding up tick marks by hand, and deposits are remembered, not recorded. This project builds a small Flask + MySQL web application that digitises the subscription, the daily delivery round, the can-deposit ledger and the monthly bill, demonstrating the complete design-and-build cycle an IGNOU BCA project demands.

Introduction

The fictional organisation, Jal-Sudha Aqua Suppliers, serves about 300 households across five areas of a tier-2 city with two delivery persons. Most customers hold standing subscriptions — one can daily, or two on alternate days — at a per-can rate fixed per plan, and every new customer pays a refundable deposit per can. The owner knows roughly what is owed, not exactly; the staff know today's round, but it is chalked on a board each morning.

Existing System

• Subscriptions and rates are noted in a diary; a plan change means a struck-out line.
• The morning round is chalked on a board: areas, houses, expected cans.
• Deliveries are tallied from the delivery person's memory, and the monthly bill is summed by hand.
• Deposits live in a separate notebook that is frequently out of date; refunds cause arguments.
• Limitations: no single source of truth, no dues visibility, wrong bills at plan changes, no can-level inventory, and nothing survives the loss of the diary.

Proposed System

• A customer registry with area, plan (daily / alternate / twice-weekly / on-call), per-can rate, deposit record and phone number — no government ID numbers are stored.
• A daily delivery log: the system generates the expected round from each subscription, the delivery person marks Delivered / Skipped per house, and skips need a reason (nobody home, can not returned).
• Monthly billing computed from the delivery log: cans delivered × rate, adjusted for deposit collected or refunded, with bills kept as immutable rows and payments recorded against them (cash or UPI reference).
• A can inventory that tracks cans issued to customers and returned to the plant, so the owner knows where every can is.
• Reports: route sheets per area and day, dues list, monthly statements and can-balance summary.

Feasibility Study

• Technical: Flask and MySQL are taught in the curriculum and run on any laptop; the interface is ordinary forms and tables usable on a phone from the plant's Android handset. Nothing requires internet hosting — the demo runs on localhost.
• Economic: only free, open-source software is used, so the study reports a zero-licence deployment.
• Operational: the owner already uses a phone and WhatsApp; a two-hour walkthrough covers all screens. Delivery staff only need the route-sheet screen, which is four taps per house. Data entry is reduced compared with the diary, not added: marking the round replaces the tally, and the bill builds itself.

In scope

• One supplier, up to ~500 customers, five areas, two delivery staff; roles Owner/Admin and Delivery.
• Plans: daily, alternate days, twice weekly, on-call; plan changes take effect from a chosen date with history retained.
• Daily delivery marking (delivered, skipped with reason); monthly billing from the log; deposits on issue and refund on return.
• Can inventory: issued to customers vs at plant; simple SMS-free notices printed on statements.
• Reports: daily route sheet, dues list, monthly statement, can balance; all printable.

Out of scope (v1)

• Online payment gateway integration (payments are recorded with a UPI/cash reference).
• GPS tracking of delivery staff or a customer-facing mobile app.
• Multi-branch or franchise support; automatic SMS/WhatsApp billing messages.
• Water-quality testing records and BIS compliance paperwork.

The project follows the waterfall SDLC model with a tightly frozen requirement set, chosen deliberately for an individual, beginner-level project: the business process at Jal-Sudha is small, stable and fully observable, so the usual risk of late-discovered requirements is low, and a disciplined stage-by-stage plan fits a 16-week solo schedule best. Each stage was signed off against a checklist before the next began.

Testing approach: unit tests for the pieces that touch money — plan-to-expected-deliveries expansion, bill computation with deposit adjustments, dues after partial payment — using pytest; black-box test cases for every screen recorded as ID, input, expected, actual, status; and a seeded-dataset acceptance run where a manually calculated month from the real diary is compared with the system's bill for the same data.

WCDM is a three-tier web application. The presentation tier is server-rendered Jinja2 templates styled with Bootstrap, usable on a phone. The application tier is Flask: blueprints per module (registry, delivery, billing, reports), a service layer holding the business rules (subscription expansion, bill arithmetic, can balance), and a before_request role guard on every route. The data tier is MySQL 8 accessed through a thin data-access module that uses parameterised queries exclusively.

flowchart TD
  O([Owner / Admin]) -->|forms and reports| F[Flask app]
  D([Delivery staff]) -->|route sheet marking| F
  F --> RG[Role guard and sessions]
  RG --> BL[Blueprints: registry, delivery, billing, reports]
  BL --> SL[Service layer: bill and can logic]
  SL --> DAO[Data access with parameterised SQL]
  DAO --> DB[(MySQL 8)]
  DB --- BK[Nightly mysqldump to backup folder]

ER diagram

erDiagram
  USER ||--o{ DELIVERY : "is assigned"
  AREA ||--o{ CUSTOMER : contains
  PLAN ||--o{ SUBSCRIPTION : "is taken as"
  CUSTOMER ||--o{ SUBSCRIPTION : holds
  CUSTOMER ||--o{ DELIVERY : receives
  CUSTOMER ||--o{ CAN_TXN : "deposits with"
  CUSTOMER ||--o{ BILL : "is billed"
  BILL ||--o{ PAYMENT : "is settled by"
  USER ||--o{ BILL : issues

  USER {
    int id PK
    string username UK
    string passwordHash
    string role
  }
  AREA {
    int id PK
    string name
  }
  PLAN {
    int id PK
    string name
    int cansPerWeek
    decimal ratePerCan
  }
  CUSTOMER {
    int id PK
    int areaId FK
    string name
    string phone
    string address
    string status
  }
  SUBSCRIPTION {
    int id PK
    int customerId FK
    int planId FK
    date startDate
    date endDate
  }
  DELIVERY {
    bigint id PK
    int customerId FK
    int userId FK
    date deliveryDate
    int cans
    string status
    string reason
  }
  CAN_TXN {
    int id PK
    int customerId FK
    string kind
    int cans
    decimal depositAmount
    date txnDate
  }
  BILL {
    int id PK
    int customerId FK
    string month
    int cansDelivered
    decimal amount
    string status
  }
  PAYMENT {
    int id PK
    int billId FK
    decimal amount
    string mode
    string reference
  }

DFD — Level 0 (context diagram)

flowchart LR
  O([Owner]) -- customers, plans, payments --> SYS((Water-Can Delivery<br/>and Subscription Manager))
  SYS -- bills, dues, can balance --> O
  DS([Delivery staff]) -- delivered or skipped --> SYS
  SYS -- daily route sheet --> DS
  CAL([Calendar date]) -- new day trigger --> SYS

DFD — Level 1

flowchart TD
  O([Owner]) --> P1[1.0 Authenticate]
  DS([Delivery staff]) --> P1
  P1 <--> D1[(D1 Users)]
  O --> P2[2.0 Manage customers and plans]
  P2 <--> D2[(D2 Customers, Plans, Areas)]
  CAL([Calendar]) --> P3[3.0 Build daily round]
  P3 <--> D3[(D3 Subscriptions)]
  P3 -- route sheet --> DS
  DS --> P4[4.0 Record deliveries]
  P4 <--> D4[(D4 Delivery log)]
  O --> P5[5.0 Generate monthly bills]
  P5 <--> D4
  P5 <--> D5[(D5 Bills and Payments)]
  O --> P6[6.0 Record payments and can moves]
  P6 <--> D5
  P6 <--> D6[(D6 Can transactions)]

DFD — Level 2 (process 5.0 exploded — the level the BCSP-064 synopsis must reach)

flowchart TD
  CAL([Month end]) --> P51[5.1 Fetch month delivery log]
  P51 --> D4[(D4 Delivery log)]
  P51 --> P52[5.2 Sum cans per customer]
  P52 --> P53{Plan changed<br/>mid month?}
  P53 -->|yes| P54[5.4 Split bill by plan periods]
  P53 -->|no| P55[5.3 Compute amount at plan rate]
  P54 --> P55
  P55 --> P56[5.5 Apply deposit adjustments]
  P56 <--> D6[(D6 Can transactions)]
  P56 --> P57[5.7 Write immutable bill row]
  P57 --> D5[(D5 Bills)]
  P57 -- statement --> O([Owner])

Each flow on these diagrams corresponds to one implemented route or table, so the examiner can walk from diagram to schema to screen without gaps.

• WhatsApp bill delivery through the WhatsApp Business API so statements reach customers the evening they are generated.
• Online payments via UPI deep links with automatic reconciliation against the dues ledger.
• Delivery-person mobile view optimised further with geolocation ordering of the route sheet.
• Dynamic route sequencing — ordering houses per area to minimise backtracking, a small travelling-salesman twist.
• Consumption analytics — seasonal demand curves per area to plan can inventory and plant capacity.
• Multi-supplier SaaS version with per-supplier branding, logins and rates.