Milk Collection & Quality-Based Payment Ledger for a Village Dairy Co-operative Society (PHP + MySQL)

Milk Collection & Quality-Based Payment Ledger (MCPL) is a PHP and MySQL web application for a village-level Dairy Co-operative Society (DCS) — in Kerala, a primary society affiliated to Milma, the Kerala Co-operative Milk Marketing Federation, under the Anand Pattern. A typical society has 200–400 member families who bring milk twice a day, in the morning and evening shifts. At the society counter the milk is weighed and tested for fat and SNF (solids-not-fat), and the member's payment depends on those quality readings — yet most small societies still record this in a bound notebook, a calculator and carbon-copy payment slips.

MCPL gives the society secretary, the collection operator and each member one accurate record. The operator enters weight, fat and SNF per member per shift; the system computes kilograms of fat and SNF, applies the effective-dated rate table (separate rates for cow and buffalo milk, revisable for flush and lean seasons) and posts the amount to the member's ledger. Deductions at source — cattle-feed credit instalments, society loan recovery, share capital and the Dairy Farmers' Fund contribution — are applied automatically with caps. Each member gets a passbook view, and the system produces the fortnightly payment register, the society's milk pool summary and the reconciliation sheet against the union's receipt.

The project is sized for a final-year FYUGP BCA research project: a normalised database, temporal rate data, transaction-safe shift closing, role-based access and a documented test suite make every design decision defensible in the viva.

A village dairy co-operative society collects milk from hundreds of member families twice a day, tests fat and SNF at the counter, and must pay every member correctly at each settlement after deducting feed credit, loan instalments, share capital and welfare-fund contributions. When this runs on a handwritten register and a calculator, fat or weight entries are miscopied, seasonal rate revisions are applied inconsistently, the same member is sometimes entered twice in one shift, deductions over-recover from small suppliers, and members have no way to verify what they were paid for. The secretary spends days compiling fortnightly payment registers and union reports, and disputes at the settlement counter are common.

The problem is to build a web-based ledger that makes each collection entry unique and traceable, computes payments transparently from effective-dated rates, applies deductions safely, and gives the secretary, operator and member reliable registers and reports — without storing any government identity numbers.

Abstract

Village dairy co-operative societies are the first link of India's three-tier Anand Pattern: members pour milk at the society, the society pays them by quality (fat and SNF), and pooled milk moves to the district union and state federation — Milma in Kerala, with 3,113 primary societies and 10.7 lakh member-farmers. Payment is the society's most sensitive daily operation, yet in many societies it runs on notebooks and hand calculators, producing calculation errors, untraceable deductions and disputes. This project builds the Milk Collection & Quality-Based Payment Ledger (MCPL), a PHP/MySQL web application that records shift-wise collections, computes quality-based payments from effective-dated rates, manages deductions at source and prints passbooks, payment registers and settlement reports.

Introduction

I studied a 300-member society in Kottayam district (name changed). Members queue at the counter in the morning and evening; the operator weighs the milk, tests fat with an electronic milk tester, notes weight, fat and can number in a register, and reads SNF from a lactometer table. Payment is announced only at the fortnightly settlement, and a member disputing a figure has no record to check. The secretary separately tracks feed-credit instalments, loans, share capital and the Dairy Farmers' Fund, all subtracted from milk payment in the same notebook.

Existing system and its limitations

• Handwritten collection register — illegible entries, no duplicate check, no running totals.
• Payment calculated at settlement time, so errors surface weeks later.
• Rate revisions (per kg fat, seasonal) applied from memory or loose paper slips.
• Deductions applied by hand with no cap check, occasionally over-recovering from a small supplier.
• No member-facing record — every query is answered by re-reading the register.
• Union reports (quantity, quality average, amount payable) are compiled manually.

Proposed system

• Shift-wise collection entry (weight, fat, SNF) with one entry per member per shift enforced by the database.
• Payment engine: kg fat = weight × fat ÷ 100; amount = kg fat × fat rate + kg SNF × SNF rate (two-axis) or kg fat × per-kg-fat rate (single-axis), using an effective-dated rate table per milk type.
• Deduction engine: standing recoveries (feed credit EMI, loan instalment, share capital, Dairy Farmers' Fund) applied in fixed order, capped so the net payable never goes negative.
• Member passbook, fortnightly settlement register, shift-close pool totals and a union reconciliation sheet.
• Role-based access (Secretary, Collection Operator, Member) and an audit log of every rate change and settlement.

Feasibility study

• Technical: PHP 8 with PDO and MySQL 8 are taught in the syllabus (UK5DSCCAP300, UK3DSCCAP201); all software is open-source. Transactions and unique keys give the needed safety.
• Economic: zero licence cost; a shared office PC or a small VPS is enough for one society.
• Operational: screens mirror the counter workflow — weigh, test, enter — so an operator trains in under an hour; members read the passbook on a phone.
• Schedule: 16 weeks cover analysis, design, four build increments, testing and report writing.

In scope

• One primary dairy co-operative society with 200–500 member families and one collection counter (two shifts a day).
• Three roles: Society Secretary (administrator), Collection Operator and Member (read-only passbook).
• Member registry with member number, milk type (cow/buffalo/mixed), bank or co-op account reference and feed-credit account; no Aadhaar, PAN or government ID numbers stored.
• Collection entry (weight, fat, SNF) for morning and evening shifts; regrade of sour or rejected milk as a flagged entry.
• Effective-dated rate management (per milk type, seasonal), payment computation in single-axis or two-axis mode.
• Standing deductions with instalment schedules, caps and a per-member deduction ledger.
• Fortnightly settlement, member passbook, daily collection register, pool summary and union reconciliation report, all exportable as CSV.

Out of scope (v1)

• Automatic import from electronic milk testers or AMCU machines (readings are typed in manually; an import is future scope).
• Payment gateway or UPI disbursement; settlement amounts are paid in cash or by bank transfer outside the system.
• Multi-society (union-level) deployment and cold-chain or logistics tracking.
• A native mobile app (the web UI is responsive).

The project follows the Incremental (iterative) SDLC model: the collection entry core is delivered first, and payment, deduction and settlement layers are added as working increments, each reviewed by the guide. This suits a 16-week FYUGP semester-8 project because a demonstrable system exists from week 6 onward.

Member-wise work split (team of 3). Member 1: database design, member registry, authentication and audit log. Member 2: collection entry, shift closing and rate management. Member 3: payment engine, deductions, settlement, reports and the test suite. All three jointly prepared the DFDs and the report.

Payment rule. For a collection of weight W kg with fat F% and SNF S%: kg fat = W × F ÷ 100 and kg SNF = W × S ÷ 100. In two-axis mode the amount is (kg fat × fat rate) + (kg SNF × SNF rate); in single-axis mode it is kg fat × per-kg-fat rate. Rates come from the rate table row effective on the collection date for the member's milk type. Amounts are computed to two decimals and rounded to the rupee only at settlement; deductions are applied in the order feed credit → loan → share capital → welfare fund, each capped at the remaining net.

MCPL follows a three-tier architecture: browser → PHP application (page controllers, service classes for payment, deduction and settlement) → MySQL 8, with PDO prepared statements for every query and server-side sessions for authentication.

DFD Level 0 (context diagram)

flowchart TD
  SEC["Society Secretary"] -->|"rates, deduction setups"| P0(("Milk Collection and Payment Ledger"))
  OPR["Collection Operator"] -->|"weight, fat, SNF readings"| P0
  MEM["Member"] -->|"login, passbook view"| P0
  P0 -->|"rate change alerts, settlement register"| SEC
  P0 -->|"shift totals, collection list"| OPR
  P0 -->|"passbook, net payable"| MEM

DFD Level 1

flowchart TD
  SEC["Secretary"] --> P1(("1.0 Manage members and rates"))
  P1 --> D1[("D1 Members")]
  P1 --> D2[("D2 Rate table")]
  OPR["Operator"] --> P2(("2.0 Record collection"))
  D1 --> P2
  D2 --> P3(("3.0 Compute payment"))
  P2 --> D3[("D3 Collections")]
  D3 --> P3
  P3 --> D4[("D4 Payment entries")]
  SEC --> P4(("4.0 Manage deductions"))
  P4 --> D5[("D5 Recovery schedule")]
  D4 --> P5(("5.0 Fortnightly settlement"))
  D5 --> P5
  P5 --> D6[("D6 Settlements")]
  D6 --> P6(("6.0 Registers and reconciliation"))
  D3 --> P6
  P6 --> SEC2["Secretary"]
  D6 --> P7(("7.0 Member passbook"))
  P7 --> MEM["Member"]

ER diagram

erDiagram
  APP_USER ||--o{ AUDIT_LOG : writes
  MEMBER ||--o{ COLLECTION : supplies
  MILK_TYPE ||--o{ RATE : "is priced by"
  RATE ||--o{ COLLECTION : applies
  COLLECTION ||--|| PAYMENT_ENTRY : earns
  MEMBER ||--o{ RECOVERY_SCHEDULE : carries
  MEMBER ||--o{ DEDUCTION : "is charged"
  MEMBER ||--o{ SETTLEMENT_LINE : "is settled in"
  SETTLEMENT ||--|{ SETTLEMENT_LINE : contains

  APP_USER {
    int id PK
    string username UK
    string password_hash
    string role
  }
  MEMBER {
    int id PK
    string member_no UK
    string full_name
    string phone
    int milk_type_id FK
    date joined_on
    boolean active
  }
  MILK_TYPE {
    int id PK
    string name
  }
  RATE {
    int id PK
    int milk_type_id FK
    decimal fat_rate
    decimal snf_rate
    date effective_from
    date effective_to
  }
  COLLECTION {
    int id PK
    int member_id FK
    date coll_date
    string shift
    decimal weight_kg
    decimal fat_pct
    decimal snf_pct
    string quality_flag
  }
  PAYMENT_ENTRY {
    int id PK
    int collection_id FK
    decimal kg_fat
    decimal kg_snf
    decimal gross_amount
  }
  RECOVERY_SCHEDULE {
    int id PK
    int member_id FK
    string recovery_type
    decimal instalment
    int instalments_left
    string status
  }
  DEDUCTION {
    int id PK
    int member_id FK
    string recovery_type
    decimal amount
    date applied_on
  }
  SETTLEMENT {
    int id PK
    date period_from
    date period_to
    boolean closed
  }
  SETTLEMENT_LINE {
    int id PK
    int settlement_id FK
    int member_id FK
    decimal gross
    decimal deductions
    decimal net_payable
  }
  AUDIT_LOG {
    int id PK
    int user_id FK
    string action
    string entity
    datetime created_at
  }

Key design decisions. Rate history is temporal: each RATE row has an effective-from and effective-to window, so a rate revision never rewrites past collections — the payment engine joins the collection date against the window. COLLECTION carries a unique key on (member_id, coll_date, shift), which makes duplicate shift entries impossible at the database level, not just in the UI. PAYMENT_ENTRY stores the computed kg fat, kg SNF and gross amount so a later rate change cannot silently alter history. Every state-changing action by Secretary or Operator writes an AUDIT_LOG row, and audit rows have no update or delete path in the application. Shift close runs inside a transaction that recomputes the pool totals from D3 and marks the shift read-only.

• Import from electronic milk testers / AMCU units via serial or CSV output, removing manual entry of fat and SNF readings.
• UPI or bank-file disbursement of the net settlement amount with an automatic payment-advice slip per member.
• Member mobile app or SMS passbook showing dues, payments and rate notices in Malayalam.
• Union-level multi-society deployment with consolidated procurement reports for the district union.
• Analytics — per-member yield trends, seasonality of fat content and feed-credit repayment behaviour — to help the committee plan input services.
• Offline tolerance at the counter: a local entry queue that syncs when connectivity returns.