# Centrex Sales Reports API

Read-only sales reports for the Admin App. The endpoints use the existing
JSON envelope and `requireAuthToken()` authentication. First obtain a token
from `POST /login_admin.php`, then send:

```http
Authorization: Bearer <token>
```

Every endpoint below is `GET`. They only run `SELECT` statements; they never
insert, update, or delete data. All success responses use
`{"error":0,"error_msg":"...","data":...}`. Errors use
`{"error":1,"error_msg":"...","error_code":...}`.

The existing `DB_SID` TNS/easy-connect setting remains supported. For an
OCI8 runtime without a TNS alias, configure `DB_HOST`, `DB_PORT` (optional,
defaults to `1521`), and `DB_SID`, or set `DB_SERVICE_NAME` instead of
`DB_SID`. `DB_CONNECT_STRING`, when provided, takes precedence. Keep
credentials and local connection settings out of source control.

## CC and Paani endpoints

The original `/sales_*.php` endpoints are combined reports: their bill
aggregates do not filter by business. The salesperson list on the original
salesperson endpoint is limited to `SALE` and `CCSALE` users, but the other
report totals can include bills created by other user groups.

The new business-specific endpoints use the bill creator's department and
`UM_USERS_GROUP.GROUP_TYPE` as the business boundary. A qualifying bill must
have a creator linked through `UM_USERS.EMP#` to `EDIR.EMP#`, with
`EDIR.DEPT#` joining to `DEPT.SR#` and `DEPT.DNAME` equal to `Sales`
case-insensitively, and the creator must belong to the matching group:

- **CC:** `CCSALE` (the database group is named `CC SALE USER`).
- **Paani:** `SALE` (the database group is named `SALE N STORE USER`).

This mapping scopes bill lines, salesperson lists, dashboard rankings and
trends, client membership, and client ledger rows. Client lists include
entities with at least one non-cancelled bill created by a Sales-department
user in that business group. Shop ID is not used as the boundary because
shops are shared between the groups. External order bookers are still read
from each client's current `ENTITY.SALEMANEMPNO` assignment; booker summaries
are scoped through the business's external-client list.

All business-specific endpoints use the existing authentication, read-only
access, pagination conventions, and accounting rules:

| Report | CC endpoint | Paani endpoint |
|---|---|---|
| Internal clients monthly sales | `GET /sales_cc_internal_clients.php` | `GET /sales_paani_internal_clients.php` |
| External clients and order bookers | `GET /sales_cc_external_bookers.php` | `GET /sales_paani_external_bookers.php` |
| Client-wise SKU sales | `GET /sales_cc_client_skus.php` | `GET /sales_paani_client_skus.php` |
| Salesperson monthly performance | `GET /sales_cc_salespersons.php` | `GET /sales_paani_salespersons.php` |
| Monthly sales dashboard | `GET /sales_cc_dashboard.php` | `GET /sales_paani_dashboard.php` |
| Client monthly/daily ledger | `GET /sales_cc_client_ledger.php` | `GET /sales_paani_client_ledger.php` |
| Salesperson/order-booker directory | `GET /sales_cc_directory.php` | `GET /sales_paani_directory.php` |

For the existing reports, common filters include
`month`, `year`, `date`, `date_from`, `date_to`, `salesperson_id`,
`orderbooker_id`, `client_type`, `client_id`, `sku`, `per_page`, and `page`.
The client-ledger and directory endpoints have the period/filter requirements
documented in their sections below. For example, a CC internal-client request
is:

```http
GET /sales_cc_internal_clients.php?month=5&year=2026&per_page=25&page=1
Authorization: ******
```

Its response has the same internal-client response shape shown below, with
`filters` also containing the selected month and year. Substitute the Paani
endpoint to get a Paani-only response.

## Sales accounting and source mappings

- Bill headers and lines are joined on bill number, bill date, and shop ID.
  This avoids matching a repeated bill number to another date or shop.
- `BILL_MST.CANCELST = 'Y'` bills are excluded.
- `ORD_TYPE = 'SALE'` and `ORD_TYPE = 'RETURN'` contribute to sales.
  Return quantities and amounts retain their signed values from the source.
- Blank or other `ORD_TYPE` values are reported separately as unclassified;
  `SECURITY` is also reported separately. Neither contributes to sales totals.
- Gross line amount is `BILL_DTL.QTY * BILL_DTL.RATE`. Net sales subtract the
  line discount and the bill-header discount. A header discount is allocated
  across its lines in proportion to each line's absolute gross amount, so it
  is counted once in aggregate and is attributable in SKU reports.
- Bill-line `STX` is returned separately as tax; sales amounts are before tax.
  Extra expenses and service charges are not included in line sales amounts.
- Internal/external client types come from `ENTITY.ENTITYTYPE`; client reports
  include all entities of that type and return `client_status`. Dashboard
  active-client metrics separately use `ENTITY.STATUS = 1`.
- External order booker assignment comes from `ENTITY.SALEMANEMPNO`, matched
  to the employee master. Unassigned clients are retained under an
  `Unassigned` booker.
- Salesperson attribution uses `BILL_MST.E_USER` joined to `UM_USERS.USER_ID`.
  For CC/Paani reports, the bill creator must also be linked through `EMP#` to
  an `EDIR` record whose `DEPT#` resolves to `DEPT.DNAME = 'Sales'`. The
  salesperson list includes active and inactive users in that department and
  business group, and returns `department`, `user_status`, and
  `linked_orderbooker_id`. Order-booker links use the employee code.
- No salesperson target/quota fields or target table were available in the
  inspected schema. The salesperson response marks target data unavailable.

All money and quantity fields are rounded to two decimals. IDs are returned
as strings except numeric salesperson IDs. Month/year filters default to the
current calendar month. Use either `month` and `year`, a single `date`, or
both `date_from` and `date_to`; do not combine these forms. Dates use
`YYYY-MM-DD`. Paginated lists accept `per_page` (1–100, default 25) and
`page` (default 1).

Common filters, where relevant:

| Parameter | Meaning |
|---|---|
| `month`, `year` | Selected calendar month; defaults to current month/year |
| `date` | One-day report; cannot be combined with other date filters |
| `date_from`, `date_to` | Inclusive date range; both are required |
| `salesperson_id` | Numeric `UM_USERS.USER_ID` who created the bill |
| `orderbooker_id` | Employee code assigned to an external client |
| `client_type` | `INTERNAL` or `EXTERNAL` |
| `client_id` | `ENTITY.ENTITY_NO` |
| `sku` | `BILL_DTL.ITEM_ID` |
| `per_page`, `page` | Page size and 1-based page number |

## 1. Internal client monthly sales

**Endpoints:** `GET /sales_cc_internal_clients.php` (CC) and
`GET /sales_paani_internal_clients.php` (Paani). The legacy combined
endpoint remains `GET /sales_internal_clients.php`.

Returns every internal client, including inactive clients and clients with no
sales in the selected period. Each client has signed net sales, quantity,
discounts, tax, SALE/RETURN subtotals, bill count, and a comparison with the preceding month
(or preceding equal-length date range).

Additional filters: `client_id`, `salesperson_id`, `sku`, `per_page`, `page`.

```json
{
  "error": 0,
  "error_msg": "Internal-client sales report retrieved successfully",
  "data": [{
    "client_id": "INT-001",
    "client_name": "Example Internal Client",
    "client_status": 1,
    "sales_amount": 18000,
    "sales_quantity": 12,
    "gross_sales_amount": 20000,
    "discount_amount": 2000,
    "tax_amount": 0,
    "sale_amount": 19000,
    "return_amount": -1000,
    "order_count": 8,
    "comparison": {
      "current_sales_amount": 18000,
      "previous_sales_amount": 15000,
      "growth_amount": 3000,
      "growth_percent": 20
    }
  }],
  "summary": {
    "total_clients": 1,
    "sales_amount": 18000,
    "sales_quantity": 12,
    "sale_amount": 19000,
    "return_amount": -1000,
    "comparison": {
      "current_sales_amount": 18000,
      "previous_sales_amount": 15000,
      "growth_amount": 3000,
      "growth_percent": 20
    }
  },
  "meta": {"current_page": 1, "per_page": 25, "total": 1, "last_page": 1}
}
```

## 2. External clients and order-booker monthly sales

**Endpoints:** `GET /sales_cc_external_bookers.php` (CC) and
`GET /sales_paani_external_bookers.php` (Paani). The legacy combined
endpoint remains `GET /sales_external_bookers.php`.

`data` is the paginated external-client list with each client's
assigned order booker, sales, and previous-period comparison.
`order_bookers` contains a complete grouped summary, including the unassigned
group. Additional filters: `client_id`, `orderbooker_id`, `salesperson_id`,
`sku`, `per_page`, `page`.

The order booker is the client's current `SALEMANEMPNO` assignment; historical
bills are grouped using that current assignment because the bill schema does
not record an order booker separately.

```json
{
  "error": 0,
  "error_msg": "External-client sales report retrieved successfully",
  "data": [{
    "client_id": "CUS-0001",
    "client_name": "Example Customer",
    "client_status": 1,
    "orderbooker_id": "42",
    "orderbooker_name": "Example Booker",
    "sales_amount": 18000,
    "sales_quantity": 12,
    "gross_sales_amount": 20000,
    "discount_amount": 2000,
    "tax_amount": 0,
    "sale_amount": 18000,
    "return_amount": 0,
    "order_count": 8,
    "comparison": {
      "current_sales_amount": 18000,
      "previous_sales_amount": 15000,
      "growth_amount": 3000,
      "growth_percent": 20
    }
  }],
  "order_bookers": [{
    "orderbooker_id": "42",
    "orderbooker_name": "Example Booker",
    "client_count": 12,
    "sales_amount": 18000,
    "sales_quantity": 12,
    "order_count": 8
  }],
  "summary": {"total_clients": 1, "sales_amount": 18000, "sales_quantity": 12},
  "meta": {"current_page": 1, "per_page": 25, "total": 1, "last_page": 1}
}
```

## 3. Client-wise SKU sales

**Endpoints:** `GET /sales_cc_client_skus.php` (CC) and
`GET /sales_paani_client_skus.php` (Paani). The legacy combined
endpoint remains `GET /sales_client_skus.php`.

Requires `client_id`. Returns products sold to that client in the selected
period, with SKU quantity, gross/net sales, discounts, tax, bill count, and
the previous-period amount/quantity comparison. Additional filters:
`client_type`, `salesperson_id`, `orderbooker_id`, `sku`, `per_page`, `page`.

```json
{
  "error": 0,
  "error_msg": "Client SKU sales report retrieved successfully",
  "client": {
    "client_id": "CUS-0001",
    "client_name": "Example Customer",
    "client_type": "EXTERNAL"
  },
  "data": [{
    "sku": "SKU-001",
    "product_name": "Example Product",
    "item_type_id": 4,
    "item_type": "GROCERY",
    "sales_amount": 9000,
    "sales_quantity": 6,
    "gross_sales_amount": 10000,
    "discount_amount": 1000,
    "tax_amount": 0,
    "sale_amount": 9000,
    "return_amount": 0,
    "order_count": 4,
    "previous_sales_amount": 7500,
    "previous_sales_quantity": 5,
    "comparison": {
      "current_sales_amount": 9000,
      "previous_sales_amount": 7500,
      "growth_amount": 1500,
      "growth_percent": 20
    }
  }],
  "summary": {"total_skus": 1, "sales_amount": 9000, "sales_quantity": 6},
  "meta": {"current_page": 1, "per_page": 25, "total": 1, "last_page": 1}
}
```

## 4. Salesperson monthly performance

**Endpoints:** `GET /sales_cc_salespersons.php` (CC) and
`GET /sales_paani_salespersons.php` (Paani). The legacy combined
endpoint remains `GET /sales_salespersons.php`.

Returns Sales-department users in the business group, including inactive users
and users with no sales for the period. The department is resolved using
`UM_USERS.EMP#` → `EDIR.EMP#` → `EDIR.DEPT#` → `DEPT.SR#`; `DEPT.DNAME` is
matched to `Sales` case-insensitively. Each row includes department,
`linked_orderbooker_id`, current/previous sales, signed quantity, client
coverage, and bill count. Optional filters: `salesperson_id`, `client_id`,
`client_type`, `orderbooker_id`, `sku`, `per_page`, `page`.

```json
{
  "error": 0,
  "error_msg": "Salesperson performance retrieved successfully",
  "data": [{
    "salesperson_id": 25,
    "employee_code": "42",
    "salesperson_name": "Example Salesperson",
    "department": "Sales",
    "sales_group": "SALE",
    "user_status": "A",
    "linked_orderbooker_id": "42",
    "sales_amount": 18000,
    "sales_quantity": 12,
    "client_coverage": 5,
    "order_count": 8,
    "target_amount": null,
    "target_available": false,
    "comparison": {
      "current_sales_amount": 18000,
      "previous_sales_amount": 15000,
      "growth_amount": 3000,
      "growth_percent": 20
    }
  }],
  "summary": {"total_salespersons": 1, "sales_amount": 18000, "order_count": 8},
  "targets_available": false,
  "meta": {"current_page": 1, "per_page": 25, "total": 1, "last_page": 1}
}
```

## 5. Overall sales dashboard

**Endpoints:** `GET /sales_cc_dashboard.php` (CC) and
`GET /sales_paani_dashboard.php` (Paani). The legacy combined
endpoint remains `GET /sales_dashboard.php`.

Returns period totals, comparison to the preceding period, internal/external
sales comparison, active clients and clients with no qualifying sales,
top-ten salesperson/client/SKU lists, daily sales dates in the selected
period, and a 12-month sales trend ending in the selected period.

Optional filters: `salesperson_id`, `orderbooker_id`, `client_type`,
`client_id`, `sku`.

The top-ten arrays `top_salespersons`, `top_clients`, and `top_skus` each use
the common item shape `{"id":"...","name":"...","sales_amount":0,
"sales_quantity":0}`. IDs identify the salesperson, client, or SKU
respectively. The three arrays respect all dashboard filters.

```json
{
  "error": 0,
  "error_msg": "Monthly sales dashboard retrieved successfully",
  "data": {
    "kpis": {
      "sales_amount": 18000,
      "sales_quantity": 12,
      "gross_sales_amount": 20000,
      "discount_amount": 2000,
      "tax_amount": 0,
      "sale_amount": 19000,
      "return_amount": -1000,
      "order_count": 8,
      "unclassified_amount": 0,
      "unclassified_quantity": 0,
      "security_amount": 0,
      "security_quantity": 0
    },
    "comparison": {
      "current_sales_amount": 18000,
      "previous_sales_amount": 15000,
      "growth_amount": 3000,
      "growth_percent": 20
    },
    "client_type_comparison": {
      "internal": {"sales_amount": 8000, "sales_quantity": 4},
      "external": {"sales_amount": 10000, "sales_quantity": 8}
    },
    "clients": {
      "active_internal": 20,
      "active_external": 50,
      "no_sales_internal": 5,
      "no_sales_external": 12,
      "active_total": 70,
      "no_sales_total": 17
    },
    "top_salespersons": [{"id": "25", "name": "Example Salesperson", "sales_amount": 18000, "sales_quantity": 12}],
    "top_clients": [{"id": "CUS-0001", "name": "Example Customer", "sales_amount": 18000, "sales_quantity": 12}],
    "top_skus": [{"id": "SKU-001", "name": "Example Product", "sales_amount": 18000, "sales_quantity": 12}],
    "daily_trend": [{"date": "2026-10-01", "sales_amount": 18000, "sales_quantity": 12}],
    "monthly_trend": [{"month": "2026-10", "sales_amount": 18000, "sales_quantity": 12}]
  },
  "filters": {"date_from": "2026-10-01", "date_to": "2026-10-31", "month": 10, "year": 2026}
}
```

## 6. Client ledger

**Endpoints:** `GET /sales_cc_client_ledger.php` (CC) and
`GET /sales_paani_client_ledger.php` (Paani).

The ledger returns clients in the selected business, with monthly or daily
rows where qualifying bills exist. Clients without bills in the selected
window are retained with an empty `rows` array; periods with no bills are
omitted. Clients are paginated by name and ID, while the summary covers the
entire filtered client set, not only the current page.

| Parameter | Rule |
|---|---|
| `grain` | Required: `month` or `day` |
| `year`, `month` | Required together for a monthly window; with `grain=month`, this is the last month |
| `months` | `grain=month` only; 1–24, default 12 |
| `date_from`, `date_to` | Alternative period form for `grain=day`; both required and inclusive |
| `client_type` | Optional: `INTERNAL` or `EXTERNAL`; omit for both |
| `client_id` | Optional `ENTITY.ENTITY_NO` |
| `salesperson_id` | Optional numeric `UM_USERS.USER_ID` bill creator |
| `orderbooker_id` | Optional employee code from `ENTITY.SALEMANEMPNO` |
| `per_page`, `page` | Client pagination; `per_page` 1–100, default 25 |

Exactly one period form must be supplied. Daily date ranges may not exceed 24
months. Invalid or missing period forms return `error: 1` with a validation
message. The monthly `period` values use `YYYY-MM`; daily values use
`YYYY-MM-DD`.

```json
{
  "error": 0,
  "error_msg": "Client ledger retrieved successfully",
  "data": [{
    "client_id": "CUS-0001",
    "client_name": "Example Customer",
    "client_type": "EXTERNAL",
    "client_status": 1,
    "orderbooker_id": "42",
    "orderbooker_name": "Example Booker",
    "salesperson_id": null,
    "rows": [
      {"period": "2026-09", "sales_amount": 15000, "sales_quantity": 10, "return_amount": 0, "order_count": 6},
      {"period": "2026-10", "sales_amount": 18000, "sales_quantity": 12, "return_amount": -500, "order_count": 8}
    ]
  }],
  "summary": {"total_clients": 1, "sales_amount": 33000, "sales_quantity": 22, "return_amount": -500},
  "meta": {"current_page": 1, "per_page": 25, "total": 1, "last_page": 1},
  "filters": {"grain": "month", "year": 2026, "month": 10, "months": 12}
}
```

Example: `GET /sales_paani_client_ledger.php?grain=month&year=2026&month=10&months=12&per_page=25&page=1`.

## 7. Sales directory

**Endpoints:** `GET /sales_cc_directory.php` (CC) and
`GET /sales_paani_directory.php` (Paani).

The directory is unpaged and always returns the complete business-scoped
salesperson and external order-booker lists. It accepts either `year` and
`month`, or inclusive `date_from` and `date_to`, to validate and echo the
selected reporting period. The period does not narrow the directory.

Salespersons include inactive users and expose the matching business group,
department, employee code, and `linked_orderbooker_id`. Order bookers use the
client's current `SALEMANEMPNO` assignment. Unassigned external clients are
grouped under `orderbooker_id: null` and `Unassigned`. `linked_user_id` is
the ID of a Sales-department UM user with the same employee code, or `null`.
`is_active` is true when at least one assigned client has `ENTITY.STATUS = 1`.

```json
{
  "error": 0,
  "error_msg": "Directory retrieved successfully",
  "data": {
    "salespersons": [{
      "salesperson_id": 25,
      "employee_code": "42",
      "salesperson_name": "Example Salesperson",
      "department": "Sales",
      "sales_group": "SALE",
      "user_status": "A",
      "linked_orderbooker_id": "42"
    }],
    "order_bookers": [
      {"orderbooker_id": "42", "orderbooker_name": "Example Booker", "client_count": 12, "is_active": true, "linked_user_id": 25},
      {"orderbooker_id": null, "orderbooker_name": "Unassigned", "client_count": 3, "is_active": false, "linked_user_id": null}
    ]
  }
}
```

## 8. Acceptance and live-data notes

The client-ledger period summary should reconcile to the dashboard sales
amount for the same business, period, and filters. Internal and external
dashboard totals should add to the overall total. Booker client totals and
salesperson totals are scoped by the same bill attribution rules.

The Oracle data inspected for this implementation currently links one
`SALE`-group user to `DEPT.DNAME = 'SALES'`, and no `CCSALE`-group users to
that department. The specification's department-plus-group rule therefore
currently produces Paani Sales-department rows but an empty CC salesperson
directory and zero CC qualifying bills. If CC sales staff should qualify
despite another department label, Finance must confirm a different department
mapping before that business rule is changed.