Sales Numbers Report Controller Documentation

File: /controllers/salesNumbersReport.php

Purpose: Generate comprehensive sales numbers and movement reports by product categories with store inventory tracking

Last Updated: December 21, 2024

Total Functions: 16

Lines of Code: ~1107

---

📋 Overview

The Sales Numbers Report Controller provides detailed product movement and sales numbers analysis focused on inventory tracking and profit calculations. It offers:

• • Product category-based sales and movement analysis
• • Store inventory movement tracking through storereport table
• • Multi-transaction type consolidation (sales, purchases, returns, service bills)
• • Profit and cost analysis with buy price tracking
• • Store-based filtering with multi-store support
• • Date range analysis with product quantity movements
• • Real-time inventory level reporting
• • Product category hierarchy navigation

Primary Functions

• ✅ Product movement analysis by category and date range
• ✅ Store inventory movement tracking and reporting
• ✅ Sales numbers consolidation across transaction types
• ✅ Profit analysis with cost calculations
• ✅ Multi-store inventory movement analysis
• ✅ Product quantity plus/minus movement tracking
• ✅ Category-based product filtering and reporting
• ✅ Current inventory level integration

Related Controllers

• • salesreport.php - General sales reporting
• • sellingReportByArea.php - Geographic sales analysis
• • sellreportpricetype.php - Price type analysis
• • reportQuotation.php - Quotation reporting

---

🗄️ Database Tables

Inventory Movement Tables

🔑 Key Functions

1. Default Action - Product Movement Report

Location: Line 188 ($do == "show" or empty($do))

Purpose: Generate product movement analysis by category

Function Signature:

// REQUEST parameters:
$startDate = $_REQUEST['from'];      // Start date filter
$endDate = $_REQUEST['to'];          // End date filter  
$productCatId = $_REQUEST['productCatId']; // Product category filter
$storeId = $_REQUEST['storeId'];     // Store filter

Process Flow:

1. Load authentication and program settings

2. Handle multi-store access control:

   if ($_SESSION['searchinonestore'] == 0) {
       if ($_SESSION['storeids'] == 0) {
           $stores = $StoreEX->queryByConditions(); // All stores
       } else {
           $stores = $StoreEX->queryByConditions(' and store.storeId in (' . $_SESSION['storeids'] . ')');
       }
   }
   ```
3. Build query filters for date range and store selection
4. Load product categories for dropdown
5. If category selected, load products and analyze movements:
   ```php
   if (!empty($productCatId)) {
       $productData = $myProductEx->queryByProductCatIdEX2($productCatId, $storeQuery);
       $productsArr = array();
       foreach ($productData as $val) {
           $productId = $val->productId;
           $arrContainer = showByStoreAndProductAndDate($productId, $query, $val->productquantity);
           if (!empty($arrContainer)) {
               array_push($productsArr, $arrContainer);
           }
       }
   }
   ```
6. Display results via `profitproductview/sallesProductCat.html` template

---

### 2. **showByStoreAndProductAndDate()** - Core Movement Analysis
**Location**: Line 291  
**Purpose**: Analyze individual product movements and validate against actual transactions

**Function Signature**:

function showByStoreAndProductAndDate($productid, $query, $productquantity)

**Process Flow**:
1. Load movement data from storereport table:
   ```php
   $storemovementData = $StorereportEX->queryWithProductAndStoreAndDateandorderMo($productid, $query);
   ```

2. Process each movement and validate against source transactions:
   ```php
   foreach ($storemovementData as $val) {
       // Validate movement against source document
       if ($val->tablename == "sellbillController.php") {
           $res = R::count("sellbill", "sellbillid =" . $val->storereportmodelid . " and conditions = 0");
       } elseif ($val->tablename == "buyBillController.php") {
           $res = R::count("buybill", "buybillid =" . $val->storereportmodelid . " and conditions = 0");
       }
       
       if ($res <= 0) {
           $val->productquantity = 0; // Nullify invalid movements
       }
   }
   ```

3. Calculate plus/minus quantities by movement type:
   ```php
   if ($storereporttype == 1) {
       $minQuantity += $resProductQuantity; // Outbound movement
   } else {
       $plusQuantity += $resProductQuantity; // Inbound movement
   }
   ```

4. Return movement summary:
   ```php
   $arrContainer = array(
       'productName' => $resProductName,
       'plusQuantity' => $plusQuantity,     // Total inbound
       'minQuantity' => $minQuantity,       // Total outbound
       'diff' => $plusQuantity - $minQuantity, // Net movement
       'productquantity' => $productquantity    // Current inventory
   );
   ```

---

### 3. **getProductInBillByDateAndProductId()** - Transaction Analysis
**Location**: Line 358  
**Purpose**: Detailed analysis of all transaction types for products in a category

**Function Signature**:

function getProductInBillByDateAndProductId($startDate, $endDate, $productCatId)

**Process Flow**:
1. Load all stores and products in category
2. For each store and product combination:
   - Load last inventory position from storereport
   - Calculate sales totals from multiple sources
   - Calculate purchase totals from multiple sources
   - Calculate returns and adjustments
   - Generate final inventory reconciliation

**Transaction Sources Analyzed**:
- Regular sales (`getTotalSellPriceByDateAndProductId()`)
- Additional sales (`getTotalAditionalSellPriceByDateAndProductId()`)
- Service bills (`getTotalbills()`)
- Sales returns (`getTotalReturnSellPriceByDateAndProductId()`)
- Purchase bills (`getTotalBuyPriceByDateAndProductId()`)
- Service returns (`getTotalbillsReturn()`)

**Final Calculation**:

$final = ($buydif + $productafter) - $selldif;

// Where:

// $buydif = Total purchases - purchase returns

// $productafter = Starting inventory

// $selldif = Total sales - sales returns

---

### 4. **Service Bills Analysis Functions**
**Location**: Lines 462-533  
**Purpose**: Analyze service bill transactions

#### **getTotalbills()** - Service Bill Totals

function getTotalbills($startDate, $endDate, $ProductIdselected, $storid)

**Process**: 
1. Query service bills with storereport linkage
2. Convert quantities using product unit factors
3. Return total quantities moved

#### **getTotalbillsReturn()** - Service Bill Returns

function getTotalbillsReturn($startDate, $endDate, $ProductIdselected, $storid)

**Process**: Similar to bills but for returns, calculating returned quantities

---

### 5. **Sales Transaction Analysis Functions**
**Location**: Lines 536-686  
**Purpose**: Analyze regular sales transactions with detailed pricing

#### **getTotalSellPriceByDateAndProductId()** - Regular Sales Analysis
**Location**: Line 536  
**Purpose**: Calculate sales totals with discount and profit analysis

**Key Calculations**:

foreach ($sellbilldetailData as $sellbilldetail) {

$quantity = $sellbilldetail->sellbilldetailquantity;

$price = $sellbilldetail->sellbilldetailprice;

$totalPrice = $sellbilldetail->sellbilldetailtotalprice;

$discountValue = $sellbilldetail->discountvalue;

$buyprice = $sellbilldetail->buyprice;

// Calculate percentage discount

if ($sellbillDiscountType == 2) {

$sellbillDiscount = ($sellbillDiscount / 100) * $sellbillTotalBill;

}

// Unit conversion

$productnumber = $myProductunitEx->getProductNumber($productunitId);

$quantityUnit += ($quantity * $productnumber);

// Profit calculation

$allbuyprice = $quantity * $buyprice;

$finalallbuyprice += $allbuyprice;

// Net selling price after discounts

$sellPriceForOneProduct = $totalPriceBeforeDiscount -

(($discountValue * $quantity) + $sellbillDiscount);

$sellPrice += $sellPriceForOneProduct;

}

return array($sellPrice, $quantityUnit, $finalallbuyprice);

---

### 6. **Purchase Transaction Analysis Functions**
**Location**: Lines 811-893  
**Purpose**: Analyze purchase transactions and costs

#### **getTotalBuyPriceByDateAndProductId()** - Purchase Analysis
**Location**: Line 811  
**Purpose**: Calculate purchase quantities from multiple bill types

**Process Flow**:
1. Query regular purchase bills (`buybilldetailData`)
2. Query combined purchase bills (`buyandruturn`)  
3. Query purchase returns (`ruturndata`)
4. Query purchase returns from combined bills (`ruturndataformbyandreturn`)
5. Convert all quantities to base units using product unit factors
6. Return net purchase quantities (purchases - returns)

**Return Structure**:

return array($quantityUnit, $ruturnquntity);

// $quantityUnit = Total purchased quantities

// $ruturnquntity = Total returned quantities

---

### 7. **Product Hierarchy Functions**
**Location**: Lines 1024-1107  
**Purpose**: Product categorization and hierarchy management

#### **getProductCats()** - Product Category Hierarchy
**Location**: Line 1068  
**Purpose**: Build complete product category hierarchy for dropdown navigation

**Process Flow**:
1. Load all products with categories
2. For each product, build recursive category path
3. Assign hierarchical paths to template variables
4. Enable multi-level category navigation

#### **fetch_recursive()** - Category Path Builder
**Location**: Line 1090  
**Purpose**: Recursively build category paths

**Recursive Logic**:

function fetch_recursive($parentid, $categories) {

$catData = $myProductcatEx->getCategoryAndParentByCatId($parentid);

if (count($catData) > 0) {

$categories .= $catData->productCatName . '/';

$newParentId = $catData->productCatParent;

if ($newParentId != 0) {

$newParentName = $catData->parentName;

$categories .= $newParentName . '/';

fetch_recursive($newParentId, $categories);

}

}

return substr($categories, 0, strlen($categories) - 1); // Remove trailing slash

}

---

## 🔄 Workflows

### Workflow 1: Product Movement Analysis

┌─────────────────────────────────────────────────────────────┐

│ START: Select Category and Date Range │

└────────────────────┬────────────────────────────────────────┘

│

▼

┌─────────────────────────────────────────────────────────────┐

│ 1. Load System Configuration │

│ - Check multi-store access permissions │

│ - Load available stores for user │

│ - Apply authentication │

└────────────────────┬────────────────────────────────────────┘

│

▼

┌─────────────────────────────────────────────────────────────┐

│ 2. Build Query Filters │

│ - Date range filter for storereport.storereportdate │

│ - Store filter based on user permissions │

│ - Build storeQuery for inventory lookup │

└────────────────────┬────────────────────────────────────────┘

│

▼

┌─────────────────────────────────────────────────────────────┐

│ 3. Load Products by Category │

│ IF productCatId provided: │

│ │ │

│ ├─→ Load all products in category │

│ │ │

│ ├─→ Apply store filter to product query │

│ │ │

│ └─→ Get current inventory levels │

└────────────────────┬────────────────────────────────────────┘

│

▼

┌─────────────────────────────────────────────────────────────┐

│ 4. Analyze Each Product's Movements │

│ FOR EACH product in category: │

│ │ │

│ ├─→ Call showByStoreAndProductAndDate() │

│ │ │ │

│ │ ├─→ Load movement data from storereport │

│ │ │ │

│ │ ├─→ Validate movements against source documents │

│ │ │ ├─ Check sellbill.conditions = 0 │

│ │ │ └─ Check buybill.conditions = 0 │

│ │ │ │

│ │ ├─→ Calculate plus/minus quantities │

│ │ │ ├─ storereporttype = 0: Inbound (purchases) │

│ │ │ └─ storereporttype = 1: Outbound (sales) │

│ │ │ │

│ │ └─→ Generate movement summary │

│ │ ├─ Total inbound movements │

│ │ ├─ Total outbound movements │

│ │ ├─ Net movement (in - out) │

│ │ └─ Current inventory level │

│ │ │

│ └─→ Add to products array if movements found │

└────────────────────┬────────────────────────────────────────┘

│

▼

┌─────────────────────────────────────────────────────────────┐

│ 5. Generate Report Output │

│ - Display category name and date range │

│ - Show movement summary for each product │

│ - Present plus/minus/net movement analysis │

│ - Include current inventory levels │

│ - Load tutorial videos │

└─────────────────────────────────────────────────────────────┘

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| No `do` parameter or `do=show` | Default action | Product movement analysis by category |

### Required Parameters

**Product Movement Report**:
- `productCatId` - Product category ID (required for analysis)
- `from` - Start date (YYYY-MM-DD, optional)
- `to` - End date (YYYY-MM-DD, optional)
- `storeId` - Store ID filter (optional, respects user permissions)

**Example URLs**:

salesNumbersReport.php?productCatId=5&from=2024-01-01&to=2024-01-31&storeId=1

salesNumbersReport.php?productCatId=10&from=2024-01-01&to=2024-01-31

---

## 🧮 Calculation Methods

### Movement Type Classification

// storereporttype values:

// 0 = Inbound movement (purchases, returns to inventory)

// 1 = Outbound movement (sales, issues from inventory)

if ($storereporttype == 1) {

$minQuantity += $resProductQuantity; // Outbound

} else {

$plusQuantity += $resProductQuantity; // Inbound

}

$netMovement = $plusQuantity - $minQuantity;

### Movement Validation Against Source Documents

// Validate that the movement corresponds to a valid transaction

if ($val->tablename == "sellbillController.php") {

$res = R::count("sellbill", "sellbillid = " . $val->storereportmodelid . " and conditions = 0");

} elseif ($val->tablename == "buyBillController.php") {

$res = R::count("buybill", "buybillid = " . $val->storereportmodelid . " and conditions = 0");

}

// If source document deleted/invalid, nullify movement

if ($res <= 0) {

$val->productquantity = 0;

}

### Profit Analysis with Unit Conversion

// Unit conversion from transaction unit to base unit

$productnumber = $myProductunitEx->getProductNumber($productunitId);

$quantityUnit += ($quantity * $productnumber);

// Cost calculation

$allbuyprice = $quantity * $buyprice;

$finalallbuyprice += $allbuyprice;

// Revenue calculation with discounts

$totalPriceBeforeDiscount = $price * $quantity;

$sellPriceForOneProduct = $totalPriceBeforeDiscount -

(($discountValue * $quantity) + $sellbillDiscount);

// Profit = Revenue - Cost

$profit = $sellPriceForOneProduct - $allbuyprice;

---

## 🔒 Security & Permissions

### Multi-Store Access Control

// Check if user has access to multiple stores

if ($_SESSION['searchinonestore'] == 0) {

if ($_SESSION['storeids'] == 0) {

// User can access all stores

$stores = $StoreEX->queryByConditions();

} else {

// User limited to specific stores

$stores = $StoreEX->queryByConditions(' and store.storeId in (' . $_SESSION['storeids'] . ')');

}

} else {

// User limited to single store

$query .= ' and storereport.storeid = ' . $_SESSION['storeid'] . ' ';

}

### Authentication

include_once("../public/authentication.php");

### Data Integrity Validation
- Movements validated against source documents
- Invalid/deleted transactions nullified in reports
- Store access controlled by user permissions

---

## 📊 Performance Considerations

### Database Performance Issues
1. **Complex Movement Queries**: Storereport table can be very large
2. **Individual Product Analysis**: Each product analyzed separately
3. **Source Document Validation**: Additional queries to validate each movement
4. **Multi-Store Queries**: Complex permission-based filtering

### Optimization Recommendations
1. **Indexing**:
   ```sql
   -- Critical indexes for performance
   CREATE INDEX idx_storereport_product_date ON storereport(productid, storereportdate);
   CREATE INDEX idx_storereport_store_date ON storereport(storeid, storereportdate);
   CREATE INDEX idx_sellbill_conditions ON sellbill(sellbillid, conditions);
   CREATE INDEX idx_buybill_conditions ON buybill(buybillid, conditions);
   ```

2. **Query Optimization**: Batch movement validation instead of individual checks
3. **Caching**: Cache category hierarchy and unit conversion factors

### Memory Considerations
- Large product categories can generate extensive movement data
- Individual product analysis can be memory-intensive
- Consider pagination for categories with many products

---

## 🐛 Common Issues & Troubleshooting

### 1. **Inconsistent Movement Data**
**Issue**: Movement totals don't match transaction records  
**Cause**: Storereport table out of sync with actual transactions

**Debug**:

-- Check for orphaned storereport records

SELECT sr.* FROM storereport sr

LEFT JOIN sellbill sb ON sr.storereportmodelid = sb.sellbillid

AND sr.tablename = 'sellbillController.php'

LEFT JOIN buybill bb ON sr.storereportmodelid = bb.buybillid

AND sr.tablename = 'buyBillController.php'

WHERE (sr.tablename = 'sellbillController.php' AND sb.sellbillid IS NULL)

OR (sr.tablename = 'buyBillController.php' AND bb.buybillid IS NULL);

-- Check for deleted transactions affecting movements

SELECT COUNT(*) FROM storereport sr

JOIN sellbill sb ON sr.storereportmodelid = sb.sellbillid

WHERE sr.tablename = 'sellbillController.php' AND sb.conditions != 0;

### 2. **Store Permission Issues**
**Issue**: Users see wrong store data or empty results  
**Cause**: Session store permissions not properly set

**Debug**:

// Add debugging

error_log("User store permissions: searchinonestore=" . $_SESSION['searchinonestore'] .

", storeid=" . $_SESSION['storeid'] .

", storeids=" . $_SESSION['storeids']);

### 3. **Unit Conversion Errors**
**Issue**: Quantities don't match expected values  
**Cause**: Missing or incorrect product unit data

**Fix**: Ensure all products have proper unit assignments:

-- Find products without unit data

SELECT p.productid, p.productname

FROM product p

LEFT JOIN productunit pu ON p.productid = pu.productid

WHERE pu.productunitid IS NULL;

---

## 🧪 Testing Scenarios

### Test Case 1: Movement Validation

1. Create sales bill and verify storereport entry created

2. Delete/mark bill as deleted (conditions = 1)

3. Run movement report

4. Verify movement shows as 0 (nullified)

5. Test with purchase bills and returns

### Test Case 2: Multi-Store Analysis

1. Set up user with multiple store access

2. Create movements in different stores

3. Filter by specific store

4. Verify only movements from selected store appear

5. Test store permission enforcement

### Test Case 3: Category Movement Analysis

1. Create products in nested categories

2. Create various transaction types for products

3. Run report for parent category

4. Verify all child product movements included

5. Check movement type classification (in/out)

### Test Case 4: Profit Calculation Accuracy

1. Create sales with known buy/sell prices

2. Apply various discount types

3. Use different product units

4. Verify profit calculations match manual calculations

5. Test with returns and adjustments

```

---

📚 Related Documentation

• • CLAUDE.md - PHP 8.2 migration guide
• • salesreport.md - General sales reporting
• • sellingReportByArea.md - Geographic sales analysis
• • sellreportpricetype.md - Price type analysis

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur