Order Clients With Sales Controller Documentation

File: /controllers/orderClientsWithSelles.php

Purpose: Comprehensive client sales analysis with transaction summaries, payment tracking, and seller performance reporting

Last Updated: December 20, 2024

Total Functions: 6

Lines of Code: ~983

---

📋 Overview

The Order Clients With Sales Controller provides comprehensive analysis of client sales activities across multiple bill types. It aggregates data from various sales and billing systems to provide unified reporting on:

• • Client sales performance across multiple bill types
• • Payment history and debt tracking
• • Seller performance analysis
• • Transaction summaries with discounts and returns
• • Category-based product filtering
• • Store and branch-specific reporting

Primary Functions

• ✅ Aggregate sales data across multiple bill types (sellbill, returnsellbill, sellbillandrutern, bills)
• ✅ Calculate client payment totals and debt changes
• ✅ Generate seller performance reports
• ✅ Track discounts and returns by client
• ✅ Filter by date ranges, categories, stores, and client types
• ✅ Provide detailed transaction breakdowns
• ✅ Support real-time data updates via AJAX

Related Controllers

• • sellbillController.php - Sales bill processing
• • clientReportsController.php - Client reporting
• • returnSellBillController.php - Sales returns
• • clientPayedDeptController.php - Payment processing

---

🗄️ Database Tables

Primary Sales Tables

🔑 Key Functions

1. Default Action - Sales Analysis Dashboard

Location: Lines 175-276

Purpose: Main sales analysis interface with filtering options

Function Signature:

// Triggered when: empty $do
// Includes: Category filters, store filters, date ranges, client types

Process Flow:

1. Load filtering options (categories, stores, users, client types)

2. Process search parameters from POST

3. Build dynamic query strings for different bill types

4. Apply date filtering with program settings

5. Call getData() to aggregate results

6. Display via orderClientsWithSells/show.html

Key Features:

• • Category Filtering: Supports multi-level product category filtering
• • Date Range Processing: Handles program-specific hour adjustments
• • Store/Branch Filtering: Filters by specific stores or branches
• • Client Type Filtering: Filter by client type categories
• • User Permissions: Respects user store and branch assignments

---

2. getData() - Core Data Aggregation

Location: Lines 291-518

Purpose: Aggregate sales data across all bill types for clients

Function Signature:

function getData($datefrom, $dateto, $queryString, $queryStringR, $queryString1, $queryString1R, $queryString1SR)

Process Flow:

1. Define clientData class for result aggregation

2. Query each bill type with respective filters:

- Sales bills (sellbill)

- Return bills (returnsellbill)

- Combined bills (sellbillandrutern)

- Service bills (bills)

- Return service bills (billsreturn)

3. Aggregate data by client ID

4. Calculate totals and discounts

5. Get payment data for each client

6. Assign results to template

Client Data Aggregation:

class clientData {
    public $clientid;
    public $clientname;
    public $totalsell = 0;
    public $totalreturn = 0;
    public $totaldiscount = 0;
    public $clientdebt = 0;
    public $phone;
    public $mobile;
    public $typeclient;
}

Bill Type Processing:

// Sales bills processing
foreach ($sellBillData as $key => $value) {
    if (in_array($value->sellbillclientid, $existId)) {
        $key = array_search($value->sellbillclientid, $existId);
        $myclient = $sellbillDataArr[$key];
    } else {
        $myclient = new clientData();
        $myclient->clientid = $value->sellbillclientid;
        array_push($existId, $value->sellbillclientid);
    }
    
    $myclient->clientname = $value->clientname;
    $myclient->totalsell = $value->allGenTotal + $value->sellbilldiscount;
    $myclient->sellbillaftertotalbill += $value->sellbillaftertotalbill;
    $myclient->clientdebt = $value->clientdebt;
    $myclient->selldis += (($value->allGenTotal - $value->sellbillaftertotalbill) + $value->sellbilldiscount);
}

---

3. showAllOperations() - Payment Analysis

Location: Lines 521-597

Purpose: Calculate comprehensive payment history for clients

Function Signature:

function showAllOperations($clientid, $datefrom, $dateto)

Process Flow:

1. Get sales bill data for date range

2. Get return bill data for date range

3. Query client debt change records

4. Process different payment types

5. Calculate net payment totals

6. Return aggregated payment amount

Payment Processing Logic:

foreach ($clientShowData as $c) {
    if ($c->tablename == "sellbillController.php") {
        // Bill payment included in bill total
        $alldata = $sellBillDAO->load($c->clientdebtchangemodelid);
        $c->clientdebtchangeamount = $alldata->sellbilltotalpayed;
        $totalPrice += $alldata->sellbilltotalpayed;
    } else {
        // Separate payment transaction
        $clientdebtchangetype = $c->clientdebtchangetype;
        if ($c->tablename != 'clientController.php') {
            if ($clientdebtchangetype == 0) {
                $totalPrice -= $c->clientdebtchangeamount; // Debt increase
            } else {
                $totalPrice += $c->clientdebtchangeamount; // Payment
            }
        }
    }
}

---

4. showSellBillsByClientAndDate() - Sales Bill Query

Location: Lines 599-681

Purpose: Retrieve and process sales bills for specific client/date range

Function Signature:

function showSellBillsByClientAndDate($clientid, $datefrom, $dateto)

Process Flow:

1. Query regular sales bills

2. Query combined sales/return bills

3. Count product quantities for each bill

4. Apply discount calculations

5. Filter out cancelled bills

6. Return merged dataset

Discount Processing:

if ($sellbilldiscounttype == 1) {
    // Fixed amount discount
    $sellbillprice = ($bill->sellbillprice) - ($bill->sellbilldiscount);
} else {
    // Percentage discount
    $sellbillprice = ($bill->sellbillprice) - ((($bill->sellbillprice) * ($bill->sellbilldiscount)) / 100);
}

---

5. showReturnSellBillsByClientAndDate() - Returns Query

Location: Lines 684-768

Purpose: Retrieve and process return bills for analysis

Function Signature:

function showReturnSellBillsByClientAndDate($clientid, $datefrom, $dateto)

Process Flow:

1. Query return bills from returnsellbill table

2. Query return portions from sellbillandrutern table

3. Count returned quantities

4. Calculate return totals

5. Return combined datasets

---

6. showsellersajax() - Seller Performance Report

Location: Lines 770-981

Purpose: AJAX endpoint for seller-specific sales analysis

Function Signature:

function showsellersajax()

Process Flow:

1. Parse request parameters (dates, client ID)

2. Build queries grouped by seller ID

3. Aggregate sales data by seller

4. Calculate seller performance metrics

5. Return formatted response for AJAX display

Seller Aggregation Queries:

$sellBillData = R::getAll('SELECT sellbill.sellerid, sellbill.sellbillclientid, 
    SUM(sellbill.sellbilltotalbill) as allGenTotal,
    SUM(sellbill.sellbillaftertotalbill) as sellbillaftertotalbill,
    COUNT(sellbill.sellbillclientid), client.clientname as clientname,
    client.clientdebt as clientdebt, user.employeename as sellername
    FROM sellbill
    join client on client.clientid = sellbill.sellbillclientid
    left join user on user.userid = sellbill.sellerid
    ' . $queryString1 . ' AND sellbill.conditions = 0 
    GROUP BY sellbill.sellerid');

---

🔄 Workflows

Workflow 1: Comprehensive Sales Analysis

---

🌐 URL Routes & Actions

Main Report (do= empty):

• • datefrom - Start date (optional, defaults to today)
• • dateto - End date (optional, defaults to today)
• • clientType - Client type filter (optional)
• • storeid - Store filter (optional)
• • level - Category level for filtering
• • productCatId{N} - Category ID for level N

Seller Report (do=showsellersajax):

• • datefrom - Start date
• • dateto - End date
• • clientid - Specific client ID

---

🧮 Calculation Methods

Sales Total Calculation

// Sales bill totals
$myclient->totalsell = $value->allGenTotal + $value->sellbilldiscount;
$myclient->sellbillaftertotalbill += $value->sellbillaftertotalbill;

// Return bill totals  
$myclient->totalreturn += $value->allGenTotalReturn + $value->returnsellbilldiscount;
$myclient->returnsellbillaftertotalbill += $value->returnsellbillaftertotalbill;

// Net calculations
$t_data->t_bure = $t_data->sellbillaftertotalbill - $t_data->returnsellbillaftertotalbill;
$t_data->t_dis = $t_data->selldis - $t_data->returnselldis + $t_data->sellandreturndis;

Discount Processing

// Fixed amount discount
if ($sellbilldiscounttype == 1) {
    $discount = $bill->sellbilldiscount;
}
// Percentage discount
else {
    $discount = $bill->sellbillprice * $bill->sellbilldiscount / 100;
}

Payment Calculation Logic

foreach ($clientShowData as $c) {
    if ($c->tablename == "sellbillController.php") {
        // Payment included in bill
        $totalPrice += $alldata->sellbilltotalpayed;
    } else {
        // Separate payment transaction
        if ($clientdebtchangetype == 0) {
            $totalPrice -= $c->clientdebtchangeamount; // Debt increase
        } else {
            $totalPrice += $c->clientdebtchangeamount; // Payment received
        }
    }
}

---

🔒 Security & Permissions

Authentication Requirements

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

User Permission Filtering

$user = $userDAO->load($_SESSION['userid']);
if ($user->userstoreid == 0) {
    // User can see all stores
    $storeData = loadStore();
} 
if ($user->branchId == 0) {
    // User can see all branches
    $branchData = loadBranch();
}

Input Sanitization

$datefrom = filter_input(INPUT_POST, 'datefrom');
$dateto = filter_input(INPUT_POST, 'dateto');
$clientType = (int) filter_input(INPUT_POST, 'clientType');
$storeid = (int) filter_input(INPUT_POST, 'storeid');

---

📊 Performance Considerations

Database Optimization Tips

1. Required Indexes:

- sellbill(sellbillclientid, sellbilldate, conditions)

- returnsellbill(returnsellbillclientid, returnsellbilldate, conditions)

- sellbillandrutern(sellbillclientid, sellbilldate, conditions)

- clientdebtchange(clientid, clientdebtchangedate)

- client(typeclientid, userid)

2. Query Optimization:

- Use appropriate date range filtering

- Limit results with proper WHERE clauses

- Efficient JOINs for client and product data

- Batch process payment calculations

3. Memory Management:

- Process large client lists in chunks

- Avoid loading unnecessary detail data

- Clean up temporary arrays after processing

Known Performance Issues

-- This aggregation can be slow for large date ranges
SELECT SUM(sellbilltotalbill) as allGenTotal, 
       COUNT(sellbillclientid),
       client.clientname
FROM sellbill 
JOIN client ON client.clientid = sellbill.sellbillclientid
WHERE sellbilldate >= ? AND sellbilldate <= ?
GROUP BY sellbillclientid;

---

🐛 Common Issues & Troubleshooting

1. Incorrect Sales Totals

Issue: Sales totals don't match individual bill totals

Cause: Missing discount calculations or double-counting

Debug Query:

SELECT 
    SUM(sellbilltotalbill) as gross_total,
    SUM(sellbillaftertotalbill) as net_total,
    SUM(sellbilldiscount) as total_discount
FROM sellbill 
WHERE sellbillclientid = [CLIENT_ID] 
AND sellbilldate BETWEEN '[START]' AND '[END]'
AND conditions = 0;

2. Payment Calculation Errors

Issue: Payment totals don't match actual transactions

Cause: Double-counting bill payments vs separate payments

Verification:

// Verify payment calculation logic
foreach ($sellbillData as $data) {
    if ($data->sellbilltotalpayed != 0 && $data->conditions == 0) {
        echo "Bill {$data->sellbillid}: Payment {$data->sellbilltotalpayed}";
    }
}

3. Category Filtering Not Working

Issue: Category filters not applied correctly

Cause: Missing category joins or incorrect level processing

Debug:

echo "Category Level: " . $level;
echo "Category ID: " . $catId;
echo "Query String: " . $queryString1;

4. Date Range Issues

Issue: Reports show no data for valid date ranges

Cause: Hour adjustment conflicts or timezone issues

Fix:

// Debug date processing
echo "Original dates: $datefrom - $dateto";
if (isset($Programsetting->reportsPlusHours)) {
    echo "Hour adjustment: " . $Programsetting->reportsPlusHours;
}

---

🧪 Testing Scenarios

Test Case 1: Comprehensive Sales Report

1. Create test data:
   - Multiple clients with different types
   - Sales bills, returns, combined bills
   - Various discount types and amounts
2. Run report with date range
3. Verify totals match database sums
4. Check discount calculations
5. Validate payment totals

Test Case 2: Category Filtering

1. Set up products in different categories
2. Create sales bills with mixed categories
3. Apply category filters at different levels
4. Verify only matching transactions included
5. Test multi-level category hierarchies

Test Case 3: Seller Performance

1. Create sales with different sellers
2. Use showsellersajax endpoint
3. Verify sales grouped by seller correctly
4. Check seller name resolution
5. Validate performance metrics

Test Case 4: Payment Analysis

1. Create complex payment scenarios:
   - Bill payments vs separate payments
   - Multiple payment types
   - Payment reversals
2. Run showAllOperations for clients
3. Verify payment totals are accurate
4. Check debt change transaction processing

---

📚 Related Documentation

• • CLAUDE.md - PHP 8.2 migration guide
• • clientReportsController.md - Client reporting
• • sellbillController.md - Sales operations
• • Database Schema Documentation - Table relationships

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur