Water-Can Delivery and Billing System for a Neighbourhood Supplier (Java Servlets/JSP + MySQL)

CanBill is a small but complete web application for the 20-litre drinking-water can supplier every Indian neighbourhood depends on — the one who still runs the business from a bahi-khata register, a ballpoint pen and a wall of returned empties. Every morning the supplier's staff deliver jars to regular customers, collect used cans, note everything in the register, and at month-end the whole book gets totalled by hand into bills that are disputed, lost and re-written.

CanBill replaces that register with three screens backed by one database. The owner (admin) manages customers — name, phone, area, monthly rate, security deposit and the standing daily order ("2 cans every day"). The delivery staff open a mobile-friendly delivery page each morning where every active customer's standing order is pre-filled; they tap to confirm cans delivered and empties returned. At month-end the system generates each customer's bill automatically from the delivery log (cans delivered × rate + adjustments), records part or full payments with a UPI reference, and prints a clean bill. Reports answer the supplier's daily questions: who owes money, which customers are holding empties beyond their deposit, and what today's route looks like.

The application is built exactly as the B.Sc IT syllabus teaches a small system: Java Servlets as controllers, JSP views, plain DAO classes with JDBC PreparedStatements, and MySQL — a textbook MVC three-tier design on Apache Tomcat. It is deliberately dependency-light, so every layer of it can be explained, modified and defended by a beginner in the viva.

Local drinking-water can suppliers manage hundreds of daily deliveries, refundable deposits and monthly collections entirely through a paper register and memory. Because deliveries, empties and payments are interleaved in one book with no dates or totals, month-end billing takes a full day of manual arithmetic, deposit disputes are common (customers frequently hold more empties than their deposit covers), dues are tracked from recollection, and payment history is lost when bill copies go missing. The owner cannot produce a trustworthy dues list, and staff changes wipe out operational knowledge. There is a need for a small, role-based web application that records every delivery and empty-can movement by date, computes monthly bills automatically from the delivery log, tracks payments against bills, keeps deposit liability accurate, and gives the owner a one-screen answer to "who owes what, and who is holding how many cans" — built with the basic Java web stack a B.Sc IT student is expected to master.

Abstract

Neighbourhood drinking-water suppliers run a surprisingly complex micro-business — daily standing orders, per-customer rates, refundable deposits, empty-can exchange and month-end billing — entirely on paper. CanBill is a Java Servlets/JSP web application with a MySQL database that digitises this workflow end to end: customer master with deposits and standing orders, a daily delivery log, automatic monthly bill generation, payment tracking and dues reports. It follows the MVC pattern on Apache Tomcat and uses only techniques from the B.Sc IT syllabus.

Introduction

In most Indian localities, the water-can supplier serves 100–500 households and offices. Customers place a standing order ("two cans daily"), get jars on a fixed rate, and pay a refundable deposit per can held. Deliveries, empties and payments are recorded in a register; bills are written by hand once a month; and when the register does not tally, both parties lose. Summer months make it worse — demand doubles, staff change, and disputes over "how many cans are pending" are routine.

Existing system

• The register records deliveries against customer names, with no dates in many entries.
• Deposits are remembered, not computed — disputes arise when a customer holds more cans than paid for.
• Month-end billing takes the owner a full day of manual totalling, with arithmetic errors.
• Payments (cash and UPI) are noted on bill copies that get lost; dues are tracked from memory.
• Limitations: no history, no totals, no accountability, and the owner cannot answer "what does customer X owe?" without flipping pages.

Proposed system

• A customer master with area, rate, deposit per can and a standing daily order; inactive customers are kept but never billed.
• A daily delivery log: staff confirm pre-filled standing orders with one tap, record cans delivered and empties returned, per date — the single source of truth for everything downstream.
• Automatic monthly billing: for each active customer, cans delivered in the month × rate + adjustments; bills marked Unpaid, Partial or Paid as payments (cash/UPI with reference number) are recorded.
• Reports: dues report, empties-pending report (cans held vs deposit), daily delivery sheet, and a printable bill per customer.
• Two roles — admin (owner) and staff — enforced by a server-side authentication filter, so staff can mark deliveries but never touch rates or delete bills.

Feasibility

• Technical: Servlets/JSP, JDBC and MySQL are taught in the programme; Tomcat runs on any laptop. No frameworks beyond Bootstrap and jBCrypt, so debugging is within a beginner's reach.
• Economic: zero licence cost; the supplier scenario needs no hosting — the owner's laptop or a low-cost VPS suffices.
• Operational: the delivery screen is one thumb-tap per customer on a phone; the owner needs a 15-minute walkthrough. Data entry is less work than the register it replaces, which is the real adoption test for a small-business system.

In scope

• Single supplier business; two roles (admin/owner, delivery staff).
• Customer master with rate, deposit and standing order; customer activate/deactivate.
• Daily delivery log with cans delivered and empties returned; edit allowed only for the current date.
• Monthly bill generation, payment recording (cash/UPI with reference), bill printing.
• Reports: dues, empties pending vs deposit, daily sheet, customer ledger.

Out of scope (v1)

• Customer-facing app or login — customers receive printed/SMS bills; a read-only customer page is future scope.
• Online payment gateway — payments are recorded with a UPI reference, not collected in-app.
• Automatic SMS/WhatsApp notifications and route optimisation for delivery staff.
• Multi-supplier (SaaS) deployment and accounting/GST integration.

The project follows the Incremental SDLC model: the system is built and demonstrated in three working increments, each adding a layer over the previous one. This suits a 12-week student timeline and the Mumbai two-phase project pattern, because the Sem-5 dissertation documents design while Sem-6 delivers the implementation.

Testing approach: JUnit tests for business rules (bill amount calculation, empties-pending computation, deposit comparison); manual black-box test cases in a table (test ID, input, expected, actual, status) for every screen; and role-based security checks — every protected URL tested while logged out and as the wrong role.

CanBill is a classic MVC three-tier web application, deliberately free of frameworks so that every request's path can be traced on a whiteboard in the viva.

flowchart LR
  B["Browser: owner or delivery staff"] -->|HTTPS| F["AuthFilter: session and role check"]
  F --> S["Servlet controllers:<br/>CustomerServlet, DeliveryServlet, BillServlet"]
  S --> V["Server-side validation"]
  V --> D["DAO classes with<br/>JDBC PreparedStatements"]
  D --> DB[("MySQL: customers,<br/>delivery_log, bills, payments, users")]
  S --> R["Request attributes"]
  R --> J["JSP + JSTL views<br/>Bootstrap pages"]
  J --> B

Presentation tier: JSP pages with JSTL tags render lists and forms; Bootstrap keeps the delivery screen usable on a staff member's phone. No business logic sits in JSPs — they only display what the servlet placed in request scope.

Application tier: each feature has one servlet that parses input, validates it server-side, calls the relevant DAO methods inside a transaction where needed (bill generation touches delivery_log, bills and payments atomically), and forwards to a JSP. A single AuthFilter guards every URL under /admin/* and /staff/*, checking the session and role before any servlet runs.

Data tier: five tables — USER, CUSTOMER, DELIVERY_LOG, BILL, PAYMENT — with foreign keys and a unique constraint on (customer, bill_month). All access uses JDBC PreparedStatement with parameter binding, which both prevents SQL injection and lets prepared plans be reused. The ER model:

erDiagram
  CUSTOMER ||--o{ DELIVERY_LOG : receives
  CUSTOMER ||--o{ BILL : "is billed"
  BILL ||--o{ PAYMENT : "is settled by"
  USER ||--o{ DELIVERY_LOG : "marks deliveries"

  CUSTOMER {
    int id PK
    string name
    string phone UK
    string area
    decimal monthly_rate
    int standing_cans
    decimal deposit
    string status
  }
  DELIVERY_LOG {
    int id PK
    int customer_id FK
    date delivery_date
    int cans_delivered
    int empties_returned
    int entered_by FK
  }
  BILL {
    int id PK
    int customer_id FK
    string bill_month
    int cans_total
    decimal amount
    decimal adjustments
    string status
  }
  PAYMENT {
    int id PK
    int bill_id FK
    decimal amount
    date paid_on
    string mode
    string reference_no
  }
  USER {
    int id PK
    string username UK
    string password_hash
    string role
  }

The empties position is never stored — it is computed as SUM(cans_delivered) − SUM(empties_returned) per customer, so it can never contradict the delivery log, and the deposit comparison in the empties report is a single query.

• A read-only customer page: enter registered phone number, see current month's deliveries, bill and dues — no passwords needed since nothing personal beyond the owner's own bill is shown.
• UPI deep-link on printed bills and payment-gateway reconciliation against reference numbers.
• SMS/WhatsApp delivery confirmations and a route sheet ordered by area to cut the staff's travel.
• Deposit management upgrades: per-can deposit receipts and automatic deposit deduction when a customer exits with cans outstanding.
• A lightweight accounting view for the owner: monthly revenue, cans sold per area, and staff-wise delivery counts.