Fair-Price Shop Stock, Entitlement & Distribution Register for a PDS Outlet (Java Servlets/JSP + MySQL)

Fair-Price Shop Stock, Entitlement & Distribution Register (FPSR) is a web application for the fair-price (ration) shop that most Indian neighbourhoods depend on under the Targeted Public Distribution System. In a typical urban outlet serving 900–1,500 ration-card households, the dealer still runs the month on paper: a godown receipt book, an issue register signed per family, a wastage note and a stock book reconciled by hand at month end — with mismatches discovered only when supplies run out mid-month.

The system gives the shop two roles — the dealer (admin) and the sales clerk — one shared register built on Java Servlets, JSP and MySQL, exactly the stack the AKTU NEP syllabus teaches (BCS552 Web Technology Lab: Servlet/JSP, HTML/CSS/JS; BCS551 DBMS Lab: MySQL). Cardholders are recorded with an internal registry code and a category (Antyodaya AAY or Priority Household PHH); monthly per-person entitlements for wheat, rice, sugar and other commodities are configured per category as per NFSA norms. The clerk records daily issues against a cardholder's remaining entitlement; the system refuses any issue that would exceed the month's cap or the available stock, inside a single database transaction. Godown receipts and wastage adjustments keep the stock ledger current, and a month-end reconciliation report shows opening stock, receipts, issues, adjustments and closing stock per commodity.

It is deliberately scoped for a first major project — mostly CRUD with one real concurrency rule — but every screen defends a genuine operational need, which is what makes it viva-defensible.

A fair-price shop serving over a thousand card households runs on four hand-written ledgers — godown receipts, the daily issue register, wastage notes and the month-end stock book. Because the ledgers are separate, the shop cannot tell at the counter how much of a household's monthly entitlement remains, and it cannot tell how much stock is truly on hand until the books are tallied. Over-issues are discovered late, stock-outs arrive mid-month, variances between the register and the godown invoice are explained on loose sheets, and preparing a report for the inspector means rewriting figures by hand.

There is a need for a single, role-based register that stores entitlement rules per card category, records every receipt, issue and adjustment as a database transaction, refuses any issue that breaks a stock or entitlement rule, and produces a month-end reconciliation and printable reports — built with the Servlets/JSP and MySQL skills the curriculum already teaches.

Abstract

Fair-price shops distribute subsidised foodgrains to ration-card households every day, yet the shop's own records are mostly hand-written. This project builds FPSR, a three-tier web application using Java Servlets, JSP and MySQL that maintains the shop's stock ledger, per-category monthly entitlements and the daily distribution register in one normalised database, with month-end reconciliation and CSV reports.

Introduction

A fair-price shop receives its monthly allocation from the FCI godown against an invoice, then issues fixed per-person quantities to AAY and Priority Household cardholders across the month. The dealer must answer three questions at any moment: how much stock is on hand, how much of a household's monthly entitlement has been used, and whether the two ledgers still agree. With paper registers, each answer needs manual arithmetic across four books, and a mid-month stock-out or an over-issue is found only after the damage.

Existing System

• Godown receipts are pasted into a notebook; issues are entered in a register that each family signs.
• Entitlement quantities for the month circulate on a notice or a messaging group and are applied from memory.
• The stock book is tallied by hand at month end; variances are explained on a separate sheet.
• Limitations: no single source of truth, arithmetic errors carried silently forward, no per-household usage history, no early warning before a stock-out, and no report the dealer can show an inspector without an evening of writing.

Proposed System

• A normalised MySQL database for users, card categories, commodities, entitlements, cardholders, receipts, adjustments, distributions and month-end closings.
• Issue transactions that atomically verify (a) the commodity's current stock and (b) the household's remaining monthly entitlement before inserting the issue.
• A monthly entitlement matrix per category that the dealer edits when government norms change — no code change.
• Month-end reconciliation (opening + receipts − issues − adjustments = closing) with a variance flag, and CSV exports for the inspector's visit.
• Role-based access: dealer (admin) and clerk, with an application-level audit log of every insert and edit.

Feasibility Study

• Technical: Servlets/JSP on Tomcat and MySQL are open-source, run on any laptop with 4 GB RAM, and are the exact tools of BCS552/BCS551, so the team already knows them. The only non-trivial logic — transactional issue checks — is well documented in the Connector/J manual.
• Economic: zero licence cost; hosting is unnecessary because the shop's register is a local machine with a backup.
• Operational: the issue screen is a single form (registry code → remaining entitlements → tick quantities → save) designed for a counter that serves a household in under two minutes. The dealer needs one 30-minute walkthrough.

In scope

• One fair-price shop with about 1,200 cardholders in two NFSA categories (AAY, Priority Household).
• Four to eight commodities (wheat, rice, sugar, pulses, etc.) with unit and subsidised unit price.
• Roles: Dealer (administrator) and Sales Clerk; every write audited.
• Cardholder registry with internal registry code, category, member count and status; monthly entitlement matrix per category per commodity; receipt, wastage and issue entry; month-end reconciliation and variance report; CSV exports.
• Demo data for two full months of receipts and issues.

Out of scope (v1)

• Biometric/ePoS device integration or OTP-based beneficiary authentication.
• Storage of any government identity numbers — the design deliberately uses an internal registry code instead of Aadhaar or ration-card numbers.
• Multi-shop (ward- or district-level) deployment and godown-side stock transfer between shops.
• Online payments or pricing beyond the subsidised unit price.
• SMS/WhatsApp notifications to cardholders.

The project follows the Incremental SDLC model: the register is built in three working increments, each passing through design, coding and testing, so a demonstrable version exists from week 5 onward — important because BCS753 sessional reviews happen mid-semester.

Testing approach: JUnit 5 for the arithmetic core (entitlement remaining, reconciliation, variance flag) and DAO methods against a dedicated test database; black-box test cases (ID, input, expected, actual, status) for each screen, including the double-clerk concurrency case; and a two-month simulated month with seeded data whose totals are hand-computed first.

FPSR follows the classic MVC three-tier architecture that BCS552 teaches, made concrete:

1. Presentation (View) — JSP pages with JSTL and Expression Language render every screen; no business logic lives in JSPs. Plain HTML/CSS with a little JavaScript for form pre-checks keeps it phone-friendly.
2. Controller — Jakarta Servlets map one URL pattern per use case (e.g. /issue, /receipts, /reconciliation). An AuthFilter runs before protected paths, checks the session and role, and forwards to login when absent.
3. Model — a DAO layer over JDBC PreparedStatements performs all SQL, including the transactional issue check, so SQL never leaks into servlets or JSPs.
4. Database — MySQL 8 with InnoDB tables and foreign keys; the issue transaction relies on InnoDB row locking to survive two clerks working at once.

flowchart TD
  B["Browser (JSP + JSTL views)"] -->|HTTP GET/POST| F["AuthFilter: session & role check"]
  F --> S["Servlet controllers (jakarta.servlet)"]
  S --> V["Form validation & business rules"]
  V --> DAO["DAO layer: JDBC PreparedStatement"]
  DAO --> DB[("MySQL 8: fps_ration")]
  S --> J["JSP views (JSTL, EL)"]
  J --> B
  DAO --> A[("audit_log table")]

ER diagram

erDiagram
  USER ||--o{ DISTRIBUTION : records
  CARD_CATEGORY ||--o{ CARDHOLDER : classifies
  CARD_CATEGORY ||--o{ ENTITLEMENT : "sets monthly limits for"
  COMMODITY ||--o{ ENTITLEMENT : "is limited by"
  CARDHOLDER ||--o{ DISTRIBUTION : receives
  DISTRIBUTION ||--|{ DISTRIBUTION_ITEM : contains
  COMMODITY ||--o{ DISTRIBUTION_ITEM : "is issued as"
  COMMODITY ||--o{ STOCK_RECEIPT : "is received as"
  COMMODITY ||--o{ STOCK_ADJUSTMENT : "is adjusted in"
  COMMODITY ||--o{ MONTH_CLOSING : "is reconciled in"
  USER ||--o{ AUDIT_LOG : writes

  USER {
    int id PK
    string username UK
    string password_hash
    string role
  }
  CARD_CATEGORY {
    string code PK
    string description
  }
  COMMODITY {
    int id PK
    string name
    string unit
    decimal unit_price
  }
  ENTITLEMENT {
    int id PK
    string category_code FK
    int commodity_id FK
    decimal qty_per_person_per_month
  }
  CARDHOLDER {
    int id PK
    string registry_code UK
    string category_code FK
    string head_of_household
    int members_count
    string address_note
    string status
  }
  STOCK_RECEIPT {
    int id PK
    int commodity_id FK
    decimal qty_received
    date received_on
    string invoice_ref
  }
  STOCK_ADJUSTMENT {
    int id PK
    int commodity_id FK
    decimal qty
    string reason
    date adjusted_on
  }
  DISTRIBUTION {
    int id PK
    int cardholder_id FK
    int issued_by FK
    datetime issued_at
  }
  DISTRIBUTION_ITEM {
    int id PK
    int distribution_id FK
    int commodity_id FK
    decimal qty_issued
  }
  MONTH_CLOSING {
    int id PK
    string month
    int commodity_id FK
    decimal opening
    decimal received
    decimal issued
    decimal adjusted
    decimal closing
  }
  AUDIT_LOG {
    int id PK
    int user_id FK
    string action
    datetime acted_at
  }

DFD — Level 0 (context diagram)

flowchart LR
  D([Dealer]) -- "entitlement rules, godown receipts, wastage" --> SYS(("Fair-Price Shop<br/>Register (FPSR)"))
  SYS -- "reconciliation, variance & stock reports" --> D
  C([Clerk]) -- "daily issue requests against cards" --> SYS
  SYS -- "remaining entitlement & stock position" --> C

Use-case diagram

flowchart LR
  D([Dealer])
  C([Clerk])
  subgraph FPSR["Fair-Price Shop Register"]
    UC1(Login)
    UC2(Manage cardholders & entitlements)
    UC3(Record godown receipts)
    UC4(Record wastage / adjustment)
    UC5(Issue commodities to a cardholder)
    UC6(View month-end reconciliation)
    UC7(Export CSV reports)
    UC8(View audit log)
  end
  D --- UC1 & UC2 & UC3 & UC4 & UC6 & UC7 & UC8
  C --- UC1 & UC5 & UC7

• ePoS-style counter: offline-first issue screen with an OTP-based beneficiary acknowledgement, so the register matches how state ePoS devices behave without storing any identity number.
• SMS/WhatsApp notices to cardholders when their category's entitlement changes or stock arrives.
• Multi-shop mode: ward- or district-level deployment where the district supply office sees stock positions across shops and reallocates before a stock-out.
• Hindi/Devanagari interface with a toggle, since counter staff and cardholders often prefer it.
• Consumption analytics: monthly off-take per category and commodity to forecast next month's godown indents.
• Mobile app for the dealer to approve adjustments and view stock from outside the shop.