Customers Products Controller Documentation

File: /controllers/customersproductsController.php

Purpose: Generates comprehensive customer-product sales analysis reports with detailed transaction tracking and category hierarchy support

Last Updated: December 20, 2024

Total Functions: 22

Lines of Code: ~506

---

📋 Overview

The Customers Products Controller is a comprehensive analytics module that provides detailed customer-product sales analysis with support for complex product category hierarchies. This controller extends beyond simple reporting to include sophisticated product categorization, detailed transaction analysis, and comprehensive customer purchasing behavior insights. The system features:

• • Customer-product sales analysis with detailed breakdowns
• • Complex product category hierarchy management
• • Multi-source transaction aggregation (sales, returns, combined bills)
• • Date range filtering with comprehensive analysis
• • Product category tree navigation and display
• • Customer purchase pattern analysis
• • Detailed quantity and pricing metrics

Primary Functions

• ✅ Customer-product sales analysis
• ✅ Product category hierarchy management
• ✅ Multi-transaction type aggregation
• ✅ Date range filtering and analysis
• ✅ Category tree navigation
• ✅ Customer purchase behavior analysis
• ✅ Detailed quantity tracking
• ✅ Price analysis and trends

Related Controllers

• • sellbillController.php - Sales operations
• • returnsellbillController.php - Sales returns
• • productController.php - Product management
• • productCatController.php - Category management
• • clientController.php - Customer management

---

🗄️ Database Tables

Primary Tables (Transaction Analysis)

🔑 Key Functions

1. Default Action - Analysis Interface Display

Location: Line 124-155

Purpose: Display customer-product analysis form with data loading

Process Flow:

1. Load product category hierarchy

2. Load customer data for dropdown

3. Set breadcrumb navigation

4. Enable product selection interface

5. Display analysis form

if (empty($do)) {
    include_once("../public/authentication.php");
    
    // Load product categories with hierarchy
    $productCatData = loadProductCat();
    $smarty->assign("productCatData", $productCatData);
    
    // Load all customers
    $allclient = getallclient();
    $smarty->assign("allclient", $allclient);
    
    // Load product/customer data if already selected
    $myclientdata = $ClientDAO->load($clientid);
    $myproductdata = $myProductRecord->load($productId);
    
    // Breadcrumb navigation
    $breadcrumbObject->add('الادارة><a href="productsellsreportController.php">التقارير</a> >تقرير مبيعات منتج', 'productsellsreportController.php', 0);
    
    $smarty->assign("settlementstoreshow", 1);
    $smarty->display("customersproductsview/show.html");
}

---

2. show Action - Analysis Report Generation

Location: Line 156-228

Purpose: Process analysis parameters and generate detailed customer-product report

Function Signature:

// URL Parameters
$startDate = $_REQUEST['from'];
$endDate = $_REQUEST['to'];
$productId = $_REQUEST['productId'];
$clientid = $_REQUEST['clientid'];

Process Flow:

elseif ($do == "show") {
    include_once("../public/authentication.php");
    
    // Load supporting data
    $productCatData = loadProductCat();
    $smarty->assign("productCatData", $productCatData);
    
    $allclient = getallclient();
    $smarty->assign("allclient", $allclient);
    
    // Validate required parameters
    if (isset($startDate) && isset($endDate) && isset($clientid) &&
        isset($productId) && $startDate != "" && $endDate != "" && 
        $clientid != -1 && $productId != '-1') {
        
        // Load master data for report header
        $myclientdata = $ClientDAO->load($clientid);
        $myproductdata = $myProductRecord->load($productId);
        
        // Generate report message
        $message = "تقرير مشتريات  العميل :" . $myclientdata->clientname . 
                   " من المنتج :" . $myproductdata->productName . 
                   "<br> من تاريخ:" . $startDate . " الى تاريخ" . $endDate;
        $smarty->assign('message', $message);
        
        // Execute analysis
        $allmydata = loadProductByProductclientAndDate($productId, $startDate, $endDate, $clientid);
        $smarty->assign("allmydata", $allmydata);
    }
    
    // Display results
    $smarty->display("customersproductsview/show.html");
    $smarty->assign("settlementstoreshow", 1);
}

---

3. loadProductByProductclientAndDate() - Core Analysis Engine

Location: Line 368-379

Purpose: Retrieve detailed transaction data for customer-product combination

Function Signature:

function loadProductByProductclientAndDate($productid, $startDate, $endDate, $clientid)

Implementation:

function loadProductByProductclientAndDate($productid, $startDate, $endDate, $clientid) {
    global $myProductEx;
    global $mySellbilldetailEx;
    global $smarty;
    
    // Get all product transactions for customer in date range
    $productData = $mySellbilldetailEx->getallproductbyclientanddate($startDate, $endDate, $productid, $clientid);
    
    return $productData;
}

---

4. Product Analysis Functions - Quantity Aggregation

4a. showSellByProduct() - Sales Quantity Analysis

Location: Line 243-251

function showSellByProduct($productId) {
    global $mySellbilldetailEx;
    $sumQuantity = $mySellbilldetailEx->queryQuantityWithProduct($productId);
    return $sumQuantity;
}

4b. showAditionalSellByProduct() - Additional Sales Analysis

Location: Line 254-263

function showAditionalSellByProduct($productId) {
    global $mySellandruternbilldetailEx;
    $sumQuantity = $mySellandruternbilldetailEx->queryQuantityWithProductAndSellType($productId, 0);
    return $sumQuantity;
}

4c. showReturnSellByProduct() - Return Quantity Analysis

Location: Line 265-274

function showReturnSellByProduct($productId) {
    global $myReturnsellbilldetailEx;
    $sumQuantity = $myReturnsellbilldetailEx->queryQuantityWithProduct($productId);
    return $sumQuantity;
}

4d. showAditionalReturnSellByProduct() - Additional Returns Analysis

Location: Line 276-285

function showAditionalReturnSellByProduct($productId) {
    global $mySellandruternbilldetailEx;
    $sumQuantity = $mySellandruternbilldetailEx->queryQuantityWithProductAndSellType($productId, 1);
    return $sumQuantity;
}

---

5. Date-Filtered Analysis Functions

5a. showSellByProductAndDate() - Date-Filtered Sales

Location: Line 323-331

function showSellByProductAndDate($productId, $startDate, $endDate) {
    global $mySellbilldetailEx;
    $sumQuantity = $mySellbilldetailEx->queryQuantityWithProductAndDate($productId, $startDate, $endDate);
    return $sumQuantity;
}

5b. showAditionalSellByProductAndDate() - Date-Filtered Additional Sales

Location: Line 334-342

function showAditionalSellByProductAndDate($productId, $startDate, $endDate) {
    global $mySellandruternbilldetailEx;
    $sumQuantity = $mySellandruternbilldetailEx->queryQuantityWithProductAndSellTypeAndDate($productId, 0, $startDate, $endDate);
    return $sumQuantity;
}

5c. showReturnSellByProductAndDate() - Date-Filtered Returns

Location: Line 345-353

function showReturnSellByProductAndDate($productId, $startDate, $endDate) {
    global $myReturnsellbilldetailEx;
    $sumQuantity = $myReturnsellbilldetailEx->queryQuantityWithProductAndDate($productId, $startDate, $endDate);
    return $sumQuantity;
}

---

6. Category Hierarchy Functions

6a. loadProductCat() - Category Tree Builder

Location: Line 453-467

Purpose: Build complete product category hierarchy for display

function loadProductCat() {
    global $myProductcatEx;
    
    // Get categories that have products
    $productcatData = $myProductcatEx->queryProductcatInProduct();
    
    // Build full hierarchy path for each category
    foreach ($productcatData as $productcat) {
        $productcatName;
        $productcat->productCatName = loadProductCatNameById($productcat->productCatId, $productcatName, 1);
    }
    
    return $productcatData;
}

6b. loadProductCatNameById() - Recursive Category Name Builder

Location: Line 470-494

Purpose: Build full category path with parent hierarchy

function loadProductCatNameById($productCatId, $productcatName, $itr) {
    global $myProductcatRecord;
    
    $productcatNamex = $productcatName;
    $productcatData = $myProductcatRecord->load($productCatId);
    
    if (count($productcatData) > 0) {
        if ($itr == 1) {
            // First iteration - current category name
            $productcatNamex = $productcatData->productCatName;
        } elseif ($itr == 2) {
            // Subsequent iterations - prepend parent name
            $productcatNamex = $productcatData->productCatName . "/" . $productcatNamex;
        }
        
        // Recurse to parent if exists
        if ($productcatData->productCatParent != 0) {
            return loadProductCatNameById($productcatData->productCatParent, $productcatNamex, 2);
        }
    }
    
    return $productcatNamex;
}

---

7. Category Analysis Functions

7a. loadProductByProductcatId() - Category-Based Analysis

Location: Line 288-320

Purpose: Analyze all products within a category

function loadProductByProductcatId($productcatId) {
    global $myProductEx;
    global $myProductRecord;
    global $smarty;
    
    // Load all products in category
    $productData = $myProductRecord->queryByProductCatId($productcatId);
    $smarty->assign("productData", $productData);
    
    $h = 1;
    foreach ($productData as $product) {
        $productId = $product->productId;
        
        // Calculate sales quantities
        $sellsQuantity = showSellByProduct($productId);
        $aditionalSellsQuantity = showAditionalSellByProduct($productId);
        $totalSellsQuantity = $sellsQuantity + $aditionalSellsQuantity;
        
        // Calculate return quantities
        $returnSellQuantity = showReturnSellByProduct($productId);
        $aditionalReturnSellsQuantity = showAditionalReturnSellByProduct($productId);
        $totalReturnSellsQuantity = $returnSellQuantity + $aditionalReturnSellsQuantity;
        
        // Calculate net quantity
        $remainQuantity = $totalSellsQuantity - $totalReturnSellsQuantity;
        
        // Assign to template with indexed variables
        $smarty->assign("totalSellsQuantity" . $h . "", $totalSellsQuantity);
        $smarty->assign("totalReturnSellsQuantity" . $h . "", $totalReturnSellsQuantity);
        $smarty->assign("remainQuantity" . $h . "", $remainQuantity);
        
        $h++;
    }
}

---

8. Supporting Functions

8a. getallclient() - Customer Data Loader

Location: Line 496-504

function getallclient() {
    global $Client;
    global $ClientDAO;
    global $ClientEX;
    
    $allclientdata = $ClientDAO->queryAll();
    return $allclientdata;
}

---

🔄 Workflows

Workflow 1: Customer-Product Analysis

---

Workflow 2: Product Category Hierarchy Building

---

🌐 URL Routes & Actions

Customer-Product Analysis:

• • clientid - Customer ID (must not be -1)
• • productId - Product ID (must not be "-1" or empty)
• • from - Start date (YYYY-MM-DD format)
• • to - End date (YYYY-MM-DD format)

Example URL:

customersproductsController.php?do=show&clientid=123&productId=456&from=2024-01-01&to=2024-12-31

---

🧮 Calculation Methods

Quantity Aggregation (Category Analysis)

// Sales quantities
$sellsQuantity = showSellByProduct($productId);
$aditionalSellsQuantity = showAditionalSellByProduct($productId);
$totalSellsQuantity = $sellsQuantity + $aditionalSellsQuantity;

// Return quantities
$returnSellQuantity = showReturnSellByProduct($productId);
$aditionalReturnSellsQuantity = showAditionalReturnSellByProduct($productId);
$totalReturnSellsQuantity = $returnSellQuantity + $aditionalReturnSellsQuantity;

// Net quantity
$remainQuantity = $totalSellsQuantity - $totalReturnSellsQuantity;

Category Hierarchy Path Building

// Recursive category path construction
function loadProductCatNameById($productCatId, $productcatName, $itr) {
    // $itr = 1: First call, use current name
    // $itr = 2: Recursive calls, prepend parent
    
    if ($itr == 1) {
        $productcatNamex = $productcatData->productCatName; // "Smartphones"
    } elseif ($itr == 2) {
        $productcatNamex = $productcatData->productCatName . "/" . $productcatNamex; // "Mobile Phones/Smartphones"
    }
    
    // Continue recursion if parent exists
    if ($productcatData->productCatParent != 0) {
        return loadProductCatNameById($productcatData->productCatParent, $productcatNamex, 2);
    }
    
    // Final result: "Electronics/Mobile Phones/Smartphones"
}

---

🔒 Security & Permissions

Access Control

// Authentication required for all actions
include_once("../public/authentication.php");

Input Sanitization

• • Current: Uses $_REQUEST directly (security risk)
• • Should Use: filter_input() for all parameters

Security Risk Example:

// Current (unsafe)
$productId = $_REQUEST['productId'];
$clientid = $_REQUEST['clientid'];

// Should be (safe)
$productId = filter_input(INPUT_REQUEST, 'productId', FILTER_VALIDATE_INT);
$clientid = filter_input(INPUT_REQUEST, 'clientid', FILTER_VALIDATE_INT);

SQL Injection Prevention

• • Protected by DAO layer parameterized queries
• • No direct SQL concatenation in controller functions

---

📊 Performance Considerations

Database Optimization Tips

1. Indexes Required:

- sellbilldetail(sellbilldetailproductid, sellbillid)

- returnsellbilldetail(returnsellbilldetailproductid, returnsellbillid)

- sellandruternbilldetail(sellbilldetailproductid, sellbillid, selltype)

- sellbill(sellbillclientid, sellbilldate, conditions)

- productcat(productCatParent, productCatId)

- product(productCatId, conditions)

2. Query Optimization:

- Category hierarchy loaded once and cached

- Separate queries for different transaction types

- Date filtering applied at database level

3. Category Performance:

- Recursive category path building can be expensive

- Consider caching category paths

- Limit hierarchy depth if needed

Performance Risks

// Recursive category loading could be slow with deep hierarchies
function loadProductCatNameById($productCatId, $productcatName, $itr) {
    // Each recursion = one database query
    // Deep hierarchies = many queries
}

Improvement Suggestion:

// Cache category paths or use single query with CTE
$categoryPaths = loadAllCategoryPaths(); // Single query
foreach ($categories as $category) {
    $category->fullPath = $categoryPaths[$category->productCatId];
}

---

🐛 Common Issues & Troubleshooting

1. Category Hierarchy Display Issues

Issue: Category names showing incorrectly or incompletely

Cause: Recursive function errors or missing parent categories

Debug:

// Test category hierarchy
$testCatId = 10;
echo "Testing category ID: " . $testCatId . "<br>";
$fullName = loadProductCatNameById($testCatId, "", 1);
echo "Full path: " . $fullName . "<br>";

// Check for orphaned categories
SELECT c1.productCatId, c1.productCatName, c1.productCatParent
FROM productcat c1
LEFT JOIN productcat c2 ON c1.productCatParent = c2.productCatId
WHERE c1.productCatParent != 0 AND c2.productCatId IS NULL;

2. Product Selection Issues

Issue: Products not appearing in analysis despite existing transactions

Cause: Parameter validation or product status filtering

Debug:

// Check parameter validation
if (isset($productId) && $productId != "-1" && $productId != "") {
    echo "Product ID is valid: " . $productId . "<br>";
} else {
    echo "Product ID validation failed<br>";
    echo "Product ID value: '" . $productId . "'<br>";
}

// Check product exists and is active
SELECT productId, productName, conditions FROM product WHERE productId = [ID];

3. Date Range Analysis Issues

Issue: Analysis returns no data for valid date ranges

Cause: Date format issues or timezone problems

Debug:

echo "Date range: " . $startDate . " to " . $endDate . "<br>";

// Test date format
if (DateTime::createFromFormat('Y-m-d', $startDate)) {
    echo "Start date format valid<br>";
} else {
    echo "Start date format invalid<br>";
}

// Check for transactions in date range
SELECT COUNT(*) FROM sellbilldetail sbd
JOIN sellbill sb ON sbd.sellbillid = sb.sellbillid
WHERE sb.sellbillclientid = [CLIENT_ID]
AND sbd.sellbilldetailproductid = [PRODUCT_ID]
AND sb.sellbilldate BETWEEN 'startdate' AND 'enddate';

4. Template Variable Indexing Issues

Issue: Category analysis results not displaying correctly

Cause: Dynamic template variable assignment in loadProductByProductcatId()

Debug:

// Check template variable assignment
foreach ($productData as $key => $product) {
    $h = $key + 1; // Ensure consistent indexing
    echo "Assigning variables for product " . $h . ": " . $product->productName . "<br>";
    $smarty->assign("totalSellsQuantity" . $h, $totalSellsQuantity);
    // ... other assignments
}

// Verify variables in template
// In template: {if isset($totalSellsQuantity1)}{$totalSellsQuantity1}{/if}

---

🧪 Testing Scenarios

Test Case 1: Customer-Product Analysis

1. Create customer with product transactions
2. Create sales, returns, and combined transactions
3. Set date range covering all transactions
4. Run analysis
5. Verify all transaction types appear
6. Check quantity calculations are correct

Test Case 2: Category Hierarchy Display

1. Create multi-level category hierarchy:
   - Electronics (parent: none)
   - Mobile Phones (parent: Electronics)  
   - Smartphones (parent: Mobile Phones)
2. Add products to deepest category
3. Load category interface
4. Verify full path displays as "Electronics/Mobile Phones/Smartphones"

Test Case 3: Date Range Filtering

1. Create transactions on different dates
2. Test various date ranges:
   - Single day
   - Week range
   - Month range
   - Cross-month range
3. Verify filtering works correctly
4. Test edge cases (transaction on boundary dates)

Test Case 4: Parameter Validation

1. Test with missing parameters:
   - No customer ID (-1)
   - No product ID (-1 or empty)
   - No dates (empty strings)
2. Test with invalid parameters:
   - Non-existent customer ID
   - Non-existent product ID
   - Invalid date formats
3. Verify form displays without analysis when validation fails

---

📚 Related Documentation

• • CLAUDE.md - PHP 8.2 migration guide
• • sellbillController.md - Sales operations
• • productController.md - Product management
• • productCatController.md - Category management
• • clientController.php - Customer management

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur