# Direct Receipt Insert Documentation

This document explains how to insert loan receipts directly into the database with all fields specified, including company_id.

## Overview

Three methods are available for direct receipt insertion:

1. **Admin API Endpoints** - REST API endpoints for programmatic insertion
2. **Node.js Script** - Standalone script for command-line insertion
3. **Direct SQL** - Raw SQL file for MySQL insertion

## Method 1: Admin API Endpoints

Two endpoints are available for direct receipt insertion:

### Endpoint 1: Using Sequelize Model
**POST** `/admin/insert_receipt_direct`
- Requires admin authentication
- Uses Sequelize ORM model for insertion
- Returns full receipt object with all fields

### Endpoint 2: Using Raw SQL
**POST** `/admin/insert_receipt_sql`
- Requires admin authentication
- Uses raw SQL query for insertion
- Returns insert ID

### Authentication
Both endpoints require admin authentication:
- Get admin token by logging in: `POST /admin/login`
- Include token in header: `Authorization: Bearer <admin_token>`

### Request Body
```json
{
  "loan_id": "DL2025001",
  "com_id": 8,
  "rec_no": "001",
  "rec_date": "2025-12-04",
  "cust_id": 534687676476,
  "cust_name": "Pavithra",
  "cust_mobile": "5346876764",
  "num_dues": 60,
  "due_amnt": 92.00,
  "paid_amnt": 2208.00,
  "paid_dues": 24,
  "balance_dues": 60,
  "pending_dues": 24,
  "next_date": "2025-12-05",
  "remark": "hvhjfgh",
  "agent": "Raja"
}
```

### Example Usage (using HTTP file)
See `Collection_Agent_API.http` for complete examples.

## Method 2: Node.js Script

A standalone Node.js script is available: `insert_receipt_direct.js`

### Usage

**Using Sequelize model (default):**
```bash
node insert_receipt_direct.js
```

**Using raw SQL:**
```bash
node insert_receipt_direct.js sql
```

### Configuration

Edit `insert_receipt_direct.js` and modify the `receiptData` object with your values:

```javascript
const receiptData = {
  loan_id: 'DL2025001',
  com_id: 8,
  rec_no: '001',
  rec_date: '2025-12-04',
  cust_id: 534687676476,
  cust_name: 'Pavithra',
  cust_mobile: '5346876764',
  num_dues: 60,
  due_amnt: 92.00,
  paid_amnt: 2208.00,
  paid_dues: 24,
  balance_dues: 60,
  pending_dues: 24,
  next_date: '2025-12-05',
  remark: 'hvhjfgh',
  agent: 'Raja'
};
```

## Method 3: Direct SQL

A SQL file is available: `insert_receipt_direct.sql`

### Usage

**Option 1: Using MySQL command line:**
```bash
mysql -u root -p dayloan3 < insert_receipt_direct.sql
```

**Option 2: Using MySQL Workbench or phpMyAdmin:**
1. Open `insert_receipt_direct.sql`
2. Modify the values as needed
3. Execute the SQL

**Option 3: Copy-paste into MySQL:**
```sql
INSERT INTO loan_receipt (
    loan_id, com_id, rec_no, rec_date, cust_id,
    cust_name, cust_mobile, num_dues, due_amnt,
    paid_amnt, paid_dues, balance_dues, pending_dues,
    next_date, remark, agent
) VALUES (
    'DL2025001',
    8,
    '001',
    '2025-12-04',
    534687676476,
    'Pavithra',
    '5346876764',
    60,
    92.00,
    2208.00,
    24,
    60,
    24,
    '2025-12-05',
    'hvhjfgh',
    'Raja'
);
```

## Required Fields

All methods require these fields:

- `loan_id` - String (loan identifier)
- `com_id` - Integer (company ID)
- `rec_no` - String (receipt number)
- `rec_date` - Date (YYYY-MM-DD format)
- `cust_id` - Integer/BigInt (customer ID)
- `cust_name` - String (customer name)
- `cust_mobile` - String (customer mobile number)
- `num_dues` - Integer (number of dues)
- `due_amnt` - Decimal (due amount)
- `paid_amnt` - Decimal (paid amount)
- `paid_dues` - Integer (paid dues)
- `balance_dues` - Integer (balance dues)
- `pending_dues` - Integer (pending dues)
- `next_date` - Date (YYYY-MM-DD format)
- `remark` - String (optional, defaults to empty string)
- `agent` - String (agent name)

## Differences from Regular Receipt Insertion

The regular `/insert_receipt` endpoint:
- Requires collection agent authentication
- Automatically gets `com_id` from authenticated agent
- Calculates dues from loan_entry
- Fetches customer details from loan_entry

The direct insertion methods:
- Require admin authentication (for API) or direct access (for scripts/SQL)
- Allow specifying `com_id` directly
- Require all fields to be provided
- No automatic calculations

## Notes

- Date format: `YYYY-MM-DD` (e.g., `2025-12-04`)
- Amounts should be decimal numbers
- `cust_id` can be a large integer (e.g., 534687676476) - requires BIGINT column type
- The `created_at` timestamp is automatically set by the database

## Important: Fix cust_id Column Type

If you encounter the error "Out of range value for column 'cust_id'", the database column needs to be changed from INT to BIGINT.

Run the migration script:
```bash
node fix_cust_id_bigint.js
```

This will alter the `cust_id` column in the `loan_receipt` table to support large customer ID values.

## Files Created

1. `controllers/AdminController.js` - Added two new methods:
   - `insertReceiptDirect()` - Using Sequelize model
   - `insertReceiptDirectSQL()` - Using raw SQL

2. `routes/auth.js` - Added two new routes:
   - `POST /admin/insert_receipt_direct`
   - `POST /admin/insert_receipt_sql`

3. `insert_receipt_direct.js` - Standalone Node.js script

4. `insert_receipt_direct.sql` - Direct SQL file

5. `Collection_Agent_API.http` - Updated with example requests

## Error Handling

All methods include error handling and will return appropriate error messages if:
- Required fields are missing
- Database connection fails
- Data type mismatches occur
- Duplicate keys exist