# 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
- [x] Aggregate sales data across multiple bill types (sellbill, returnsellbill, sellbillandrutern, bills)
- [x] Calculate client payment totals and debt changes
- [x] Generate seller performance reports
- [x] Track discounts and returns by client
- [x] Filter by date ranges, categories, stores, and client types
- [x] Provide detailed transaction breakdowns
- [x] Support real-time data updates via AJAX

### Related Controllers
- [sellbillController.php](sellbillController.md) - Sales bill processing
- [clientReportsController.php](clientReportsController.md) - Client reporting
- [returnSellBillController.php](#) - Sales returns
- [clientPayedDeptController.php](#) - Payment processing

---

## 🗄️ Database Tables

### Primary Sales Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **sellbill** | Sales bills | sellbillid, sellbillclientid, sellbilltotalbill, sellbillaftertotalbill, sellbilltotalpayed, sellbilldiscount, sellbilldiscounttype, conditions |
| **returnsellbill** | Sales return bills | returnsellbillid, returnsellbillclientid, returnsellbilltotalbill, returnsellbillaftertotalbill, returnsellbilltotalpayed, conditions |
| **sellbillandrutern** | Combined sales/returns | sellbillid, sellbillclientid, sellbillprice, returnsellbillprice, sellbilldate, conditions |

### Detail Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **sellbilldetail** | Sales bill line items | sellbilldetailid, sellbillid, sellbilldetailproductid, sellbilldetailquantity, sellbilldetailcatid |
| **returnsellbilldetail** | Return bill line items | returnsellbilldetailid, returnsellbillid, returnsellbilldetailproductid, returnsellbilldetailquantity, returnsellbilldetailcatid |
| **sellandruternbilldetail** | Combined bill line items | sellandruternbilldetailid, sellbillid, sellbilldetailproductid, sellbilldetailquantity, sellbilldetailcatid, selltype |

### Reference Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **client** | Customer master data | clientid, clientname, clientdebt, clientphone, clientmobile, typeclientid, userid |
| **clientdebtchange** | Customer debt transaction log | clientdebtchangeid, clientid, clientdebtchangeamount, clientdebtchangetype, clientdebtchangedate, tablename, clientdebtchangemodelid |
| **typeclient** | Client type categories | typeId, typeName, conditions |
| **user** | System users/sellers | userid, employeename, username |

---

## 🔑 Key Functions

### 1. **Default Action** - Sales Analysis Dashboard
**Location**: Lines 175-276  
**Purpose**: Main sales analysis interface with filtering options

**Function Signature**:
```php
// 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**:
```php
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**:
```php
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**:
```php
// 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**:
```php
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**:
```php
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**:
```php
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**:
```php
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**:
```php
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**:
```php
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**:
```php
$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
```
┌─────────────────────────────────────────────────────────────┐
│              START: Sales Analysis Request                 │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Process Filter Parameters                               │
│     - Date range (with program hour adjustments)           │
│     - Client type filtering                                │
│     - Store/branch filtering                               │
│     - Product category filtering                           │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Build Dynamic Queries                                   │
│     - Sales bill query (sellbill)                          │
│     - Return bill query (returnsellbill)                   │
│     - Combined bill query (sellbillandrutern)              │
│     - Service bill queries (bills, billsreturn)            │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Execute Aggregation Queries                            │
│     FOR EACH bill type:                                     │
│       ├─→ Apply date and filter conditions                 │
│       ├─→ Join with client and product data                │
│       ├─→ Aggregate totals by client                       │
│       └─→ Calculate discounts and adjustments              │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Merge Client Data                                       │
│     - Combine results from all bill types                  │
│     - Resolve client information conflicts                 │
│     - Calculate net sales vs returns                       │
│     - Sum discount amounts                                 │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Calculate Payment Data                                  │
│     FOR EACH client:                                        │
│       ├─→ Query debt change transactions                   │
│       ├─→ Process bill payments vs separate payments       │
│       ├─→ Calculate net payment totals                     │
│       └─→ Handle payment type classifications              │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  6. Generate Report Output                                  │
│     - Format client sales summaries                        │
│     - Include payment analysis                             │
│     - Show discount breakdowns                             │
│     - Display via template system                          │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=` (empty) | Default action | Main sales analysis dashboard |
| `do=showsellersajax` | `showsellersajax()` | AJAX seller performance report |

### Required Parameters by Action

**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
```php
// 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
```php
// Fixed amount discount
if ($sellbilldiscounttype == 1) {
    $discount = $bill->sellbilldiscount;
}
// Percentage discount
else {
    $discount = $bill->sellbillprice * $bill->sellbilldiscount / 100;
}
```

### Payment Calculation Logic
```php
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
```php
include_once("../public/authentication.php");
```

### User Permission Filtering
```php
$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
```php
$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
```sql
-- 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**:
```sql
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**:
```php
// 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**:
```php
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**:
```php
// 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](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [clientReportsController.md](clientReportsController.md) - Client reporting
- [sellbillController.md](sellbillController.md) - Sales operations
- [Database Schema Documentation](#) - Table relationships

---

**Documented By**: AI Assistant  
**Review Status**: ✅ Complete  
**Next Review**: When major changes occur