# Analytics Report Summary - Formulas and Data Sources

## Overview
This document explains the formulas and data sources for all summary values in the Analytics Report generated by `collectAnalyticsReportData.js`.

---

## Summary Section Formulas

### 1. Revenue & Profit Metrics

#### totalRevenue
**Formula:** `totalInvoiceRevenue + totalBillRevenue`
**Data Sources:**
- `totalInvoiceRevenue`: Sum of `totalAmount` from all sales invoices in the date range
- `totalBillRevenue`: Sum of `total` or `totalRevenue` from all completed bills in the date range

#### grossProfit
**Formula:** `totalInvoiceGrossProfit + totalBillGrossProfit`
**Data Sources:**
- `totalInvoiceGrossProfit`: Sum of gross profit from all sales invoices
  - If `totalGrossProfit` field exists, use it
  - Otherwise calculate: `sum(quantity * (unitPrice - costPrice))` for each line item
- `totalBillGrossProfit`: Sum of `totalGrossProfit` or `totalProfit` from all bills

#### cogs (Cost of Goods Sold)
**Formula:** `sum(invoice COGS) + sum(bill COGS)`
**Data Sources:**
- Invoice COGS: `totalRevenue - totalGrossProfit` (minimum of 0)
- Bill COGS: `totalRevenue - totalGrossProfit` (minimum of 0)

#### operatingExpenses
**Formula:** `totalExpenses + totalPayrollDeduction`
**Data Sources:**
- `totalExpenses`: Sum of all expense amounts in the date range (excluding cancelled)
- `totalPayrollDeduction`: Sum of payroll amounts where `status='paid'` and `deductFromNetProfit=true`

#### netProfit
**Formula:** `totalRevenue - cogs - operatingExpenses`
**Data Sources:**
- Derived from the above calculated values

---

### 2. Receivables & Payables

#### accountsReceivable
**Formula:** `sum(invoice totalAmount - collectedToDate)` for all invoices up to end date
**Data Sources:**
- All sales invoices with `invoiceDate <= end`
- `collectedToDate` includes:
  - Initial amount paid at sale (`amountPaid` field)
  - Loan payments collected after sale (if invoice has a loan)
- Formula: `max(totalAmount - min(totalAmount, initialCollectedAtSale + loanPaymentsToDate), 0)`

#### collectedReceivables
**Formula:** `sum(collectedToDate)` for all invoices up to end date
**Data Sources:**
- Same as accountsReceivable, but sums the collected amounts instead of outstanding

#### accountsPayable (totalPayableRemainingAmount)
**Formula:** `sum(principalAmount - totalPaidToDate)` for all payable records
**Data Sources:**
- All payable records with `date <= end`
- `totalPaidToDate`: Sum of all payments made to the record up to end date
- Formula: `max(principalAmount - min(totalPaidToDate, principalAmount), 0)`

#### totalPayablePrincipal
**Formula:** `sum(principalAmount)` for all payable records
**Data Sources:**
- All payable records with `date <= end`

#### totalPayablePaidAmount
**Formula:** `sum(min(totalPaidToDate, principalAmount))` for all payable records
**Data Sources:**
- All payable records with `date <= end`

---

### 3. Cash Flow Metrics

#### openingCashBalance
**Formula:** 
```
billsBeforeStart + openingCashFromInvoices + loanPaymentsBeforeStart 
- payablePaymentsBeforeStart - paidExpensesBeforeStart - paidPayrollBeforeStart
```
**Data Sources:**
- `billsBeforeStart`: Sum of bill totals before start date
- `openingCashFromInvoices`: Sum of cash collected at sale for invoices before start date
- `loanPaymentsBeforeStart`: Sum of loan payments before start date
- `payablePaymentsBeforeStart`: Sum of payments to vendors before start date
- `paidExpensesBeforeStart`: Sum of paid expenses before start date
- `paidPayrollBeforeStart`: Sum of paid payroll before start date

#### cashReceived
**Formula:** `totalBillRevenue + invoiceCashAtSaleInRange + loanPaymentsInRange`
**Data Sources:**
- `totalBillRevenue`: Sum of bill totals in date range
- `invoiceCashAtSaleInRange`: Sum of cash collected at sale for invoices in date range
- `loanPaymentsInRange`: Sum of loan payments in date range

#### cashPaid
**Formula:** `payablePaymentsInRange + paidExpensesInRange + paidPayrollInRange`
**Data Sources:**
- `payablePaymentsInRange`: Sum of payments to vendors in date range
- `paidExpensesInRange`: Sum of paid expenses in date range
- `paidPayrollInRange`: Sum of paid payroll in date range

#### netCashFlow
**Formula:** `cashReceived - cashPaid`
**Data Sources:**
- Derived from the above calculated values

#### cashBalance (closingCashBalance)
**Formula:** `openingCashBalance + netCashFlow`
**Data Sources:**
- Derived from the above calculated values

---

### 4. Inventory Metrics

#### totalStockUnits
**Formula:** `sum(stock)` for all active products
**Data Sources:**
- All products with `status=true`
- Uses the `stock` field from Product model

#### totalStockValue (inventoryValue)
**Formula:** `sum(stock * costPrice)` for all active products
**Data Sources:**
- All products with `status=true`
- Uses `stock` field and `costPrice` (or `mainprice` as fallback)

---

### 5. Business Equity

#### businessEquity
**Formula:** `cashBalance + totalStockValue + accountsReceivable - accountsPayable`
**Data Sources:**
- Derived from the above calculated values
- This represents the owner's equity in the business

---

### 6. Sales Metrics

#### totalSalesCount
**Formula:** `totalInvoiceCount + totalBillCount`
**Data Sources:**
- `totalInvoiceCount`: Count of all sales invoices in date range
- `totalBillCount`: Count of all completed bills in date range

#### totalInvoiceCount
**Formula:** Count of invoices in date range
**Data Sources:**
- All sales invoices with `invoiceDate` in the date range

#### totalBillCount
**Formula:** Count of bills in date range
**Data Sources:**
- All bills with `date` in date range and status not in ['pending', 'refunded', 'cancelled']

#### totalInvoiceRevenue
**Formula:** `sum(totalAmount)` for all invoices in date range
**Data Sources:**
- All sales invoices with `invoiceDate` in date range

#### totalBillRevenue
**Formula:** `sum(total or totalRevenue)` for all bills in date range
**Data Sources:**
- All bills with `date` in date range and status not in ['pending', 'refunded', 'cancelled']

#### totalInvoiceGrossProfit
**Formula:** `sum(totalGrossProfit)` for all invoices in date range
**Data Sources:**
- All sales invoices with `invoiceDate` in date range
- If `totalGrossProfit` field doesn't exist, calculated from line items

#### totalBillGrossProfit
**Formula:** `sum(totalGrossProfit or totalProfit)` for all bills in date range
**Data Sources:**
- All bills with `date` in date range and status not in ['pending', 'refunded', 'cancelled']

#### paidSalesCount
**Formula:** Count of invoices with `paymentStatus='paid'` in date range
**Data Sources:**
- All sales invoices with `invoiceDate` in date range and `paymentStatus='paid'`

#### totalItemsSold
**Formula:** `sum(quantity)` for all line items in invoices and bills
**Data Sources:**
- All line items from sales invoices in date range
- All line items from bills in date range

---

### 7. Expense & Payroll Metrics

#### totalExpenses
**Formula:** `sum(amount)` for all expenses in date range
**Data Sources:**
- All expenses with `date` in date range and `status != 'cancelled'`

#### totalPaidExpenses
**Formula:** `sum(amount)` for expenses with `status='paid'` in date range
**Data Sources:**
- All expenses with `date` in date range and `status='paid'`

#### totalExpenseRecords
**Formula:** Count of expenses in date range
**Data Sources:**
- All expenses with `date` in date range and `status != 'cancelled'`

#### totalPayrollAmount
**Formula:** `sum(amount)` for all payroll entries in date range
**Data Sources:**
- All payroll entries with `date` in date range

#### totalPayrollDeduction
**Formula:** `sum(amount)` for payroll with `status='paid'` and `deductFromNetProfit=true`
**Data Sources:**
- All payroll entries with `date` in date range, `status='paid'`, and `deductFromNetProfit=true`

#### totalPayrollRecords
**Formula:** Count of payroll entries in date range
**Data Sources:**
- All payroll entries with `date` in date range

---

### 8. Order Metrics

#### ordersCount
**Formula:** Count of orders in date range
**Data Sources:**
- All orders with `date` in date range

#### totalOrdersAmount
**Formula:** `sum(totalAmount)` for all orders in date range
**Data Sources:**
- All orders with `date` in date range

---

### 9. Payable Records Metrics

#### totalPayableRecords
**Formula:** Count of payable records created in date range
**Data Sources:**
- All payable records with `date` in date range

#### totalPayablePaidInRange
**Formula:** `sum(min(totalPaidInRange, principalAmount))` for payable records
**Data Sources:**
- All payable records with `date <= end`
- `totalPaidInRange`: Sum of payments made in the date range

---

## Balance Sheet Structure

### Assets
- **cashBalance**: Opening cash + net cash flow
- **inventoryValue**: Total stock value
- **accountsReceivable**: Outstanding customer payments
- **totalAssets**: `cashBalance + inventoryValue + accountsReceivable`

### Liabilities
- **accountsPayable**: Outstanding vendor payments
- **totalLiabilities**: `accountsPayable`

### Owner's Equity
- **businessEquity**: `totalAssets - totalLiabilities`

---

## Cash Flow Summary Structure

- **openingCashBalance**: Cash at start of period
- **cashReceived**: Total cash inflows during period
- **cashPaid**: Total cash outflows during period
- **netCashFlow**: `cashReceived - cashPaid`
- **closingCashBalance**: `openingCashBalance + netCashFlow`

---

## Performance Snapshot Structure

- **orders**: Number of orders in period
- **productsSold**: Total items sold in period
- **totalSales**: Total revenue in period
- **totalSalesLabel**: Formatted currency string of total sales
- **lastSale**: Date of last sale
- **lastSaleLabel**: Formatted date string of last sale

---

## Data Collection Queries

The report collects data from the following MongoDB collections:
1. **Invoice** - Sales invoices
2. **Bill** - Sales bills (cash sales)
3. **Expense** - Business expenses
4. **Product** - Product inventory
5. **ProductBatch** - Product purchase batches
6. **Order** - Customer orders
7. **Payroll** - Employee payroll
8. **LoanPayment** - Loan/Receivable payments
9. **AccountPayable** - Vendor accounts
10. **PayableRecord** - Vendor payable records
11. **PayablePayment** - Vendor payments

---

## Date Range Handling

- **from**: Start date of reporting period
- **to**: End date of reporting period
- **beforeStart**: Records with date < start
- **inRange**: Records with date >= start AND date <= end
- **toEnd**: Records with date <= end

All monetary values are rounded to 2 decimal places using the `toMoney()` helper function.