Sales Reports Bills Controller Documentation

File: /controllers/salesreportbills.php

Purpose: Comprehensive sales and returns reporting system with advanced filtering, payment tracking, and bill management

Last Updated: December 20, 2024

Total Functions: 25+

Lines of Code: ~4,332

---

📋 Overview

The Sales Reports Bills Controller is a comprehensive reporting and management system that handles all aspects of sales bill reporting, returns analysis, and bill review processes. It serves as the primary interface for:

• • Sales bill reporting with advanced filtering
• • Returns tracking and analysis
• • Bill review and approval workflows
• • Payment processing and tracking
• • AJAX-powered data tables for real-time reporting
• • Multi-format bill handling (sales, returns, combined bills)
• • Cash vs credit bill separation
• • Bill reservation management
• • Client payment processing
• • Financial transaction recording

Primary Functions

• ✅ Advanced sales bill reporting with multiple filters
• ✅ Returns bill tracking and analysis
• ✅ Real-time AJAX data tables for bill listings
• ✅ Bill review and approval workflows
• ✅ Payment processing and client debt management
• ✅ Cash vs credit bill separation
• ✅ Combined sales and returns reporting
• ✅ Bill reservation management
• ✅ Receipt printing functionality
• ✅ Financial transaction recording (daily entries)
• ✅ Multi-user permission system
• ✅ Branch and store filtering
• ✅ Date range analysis
• ✅ Seller performance tracking

Related Controllers

• • sellbillController.php - Sales bill creation
• • returnSellBillController.php - Returns processing
• • clientController.php - Customer management
• • dailyentry.php - Accounting entries
• • saveController.php - Cash register management

---

🗄️ Database Tables

Primary Sales Tables (Direct Operations)

🔑 Key Functions

1. Default Action / Main Report - Comprehensive Sales Report

Location: Line 284

Purpose: Main sales and returns reporting interface with advanced filtering

Function Signature:

// Triggered when: do='' (empty) or no specific action
$storeId = $_REQUEST['storeId'] ?? -1;
$branchId = $_REQUEST['branchId'] ?? -1;
$sellerid = $_REQUEST['sellerid'] ?? -1;
$userid = $_REQUEST['userid'] ?? -1;
$clientid = $_REQUEST['clientid'] ?? -1;
$billid = $_REQUEST['billid'] ?? -1;
$billserial = $_REQUEST['billserial'] ?? -1;
$billtype = $_REQUEST['billtype'] ?? -1;

Process Flow:

1. Load user permissions and restrictions

2. Build dynamic query strings for different bill types

3. Apply filters: store, branch, seller, user, client, bill ID/serial

4. Handle bill type filtering (sales only, returns only, combined, service bills)

5. Call getData() with all query parameters

6. Display via salesreportbillsview/show.html

Bill Type Filtering:

• • billtype = 1: Sales only (sellbill)
• • billtype = 2: Returns only (returnsellbill)
• • billtype = 3: Combined sales & returns (sellbillandrutern)
• • billtype = 4: Service bills (bills)
• • billtype = 5: Service returns (billsreturn)

---

2. getData() - Core Data Processing Engine

Location: Line 920

Purpose: Process and aggregate sales/returns data with profit calculations

Function Signature:

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

Process Flow:

1. Execute multiple queries for different bill types:

- Service bills (bills table)

- Service returns (billsreturn table)

- Sales bills (sellbill table)

- Return bills (returnsellbill table)

- Combined bills (sellbillandrutern table)

2. For each bill, calculate:

- Profit based on Profitevaluation setting

- Total quantities and amounts

- Tax and discount values

3. Merge all datasets and sort by date

4. Assign aggregated data to Smarty templates

Profit Calculation Methods:

switch ($Programsettingdata->Profitevaluation) {
    case "first": // First purchase price
        $profit = $sellPrice - $firstPurchasePrice;
        break;
    case "last": // Last purchase price
        $profit = $sellPrice - $lastPurchasePrice;
        break;
    case "mean": // Average purchase price
        $profit = $sellPrice - $meanPurchasePrice;
        break;
    case "generalPrice": // Fixed general price
        $profit = $sellPrice - $product->productgeneralprice;
        break;
}

---

3. showbills - Bills Search Interface

Location: Line 512

Purpose: Display search interface for bill reporting

Process Flow:

1. Load search dropdowns:

- Stores, branches, sellers, users

- Cash registers (saves)

- YouTube tutorial links

2. Apply user permissions for data filtering

3. Display via salesreportbillsview/showBills.html

---

4. billReservation - Reservation Management

Location: Line 542

Purpose: Manage bills marked for future processing

Process Flow:

1. Calculate future end date (+2 days)

2. Query bills with billReservation = 1

3. Load all bill types: sales, returns, combined

4. Display reservation overview

---

5. reviewbills - Bill Review Interface

Location: Line 564

Purpose: Interface for bill review and approval workflows

Process Flow:

1. Load user permissions for review access

2. Check reviewBillsAllowedTables permissions

3. Load search tools and filters

4. Display review interface for authorized bill types

---

6. showallajaxsell() - AJAX Sales Bills Table

Location: Line 1364

Purpose: Generate AJAX DataTable for sales bills with server-side processing

Function Signature:

function showallajaxsell()

Process Flow:

1. Parse DataTable parameters (pagination, sorting, search)

2. Build dynamic WHERE clause with filters

3. Handle user permissions and restrictions

4. Query sellbill with joins to client, user, review tables

5. Calculate payment totals from clientdebtchange

6. Format data for DataTable JSON response

7. Return structured JSON with pagination info

Features:

• • Server-side pagination
• • Multi-column sorting
• • Global and column-specific search
• • Payment status indicators
• • Review status tracking
• • User permission filtering

---

7. showallajaxsellCash() - Cash-Only Sales Bills

Location: Line 1805

Purpose: AJAX table for cash sales bills only

Filtering Logic:

if ($cashOnlyNotClient == 0) { // Cash only
    $sWhere .= " AND sellbill.sellbillclientid = 1 ";
} elseif ($cashOnlyNotClient == 1) { // Credit only  
    $sWhere .= " AND sellbill.sellbillclientid != 1 ";
}

---

8. showallajaxsellBook() - Credit Sales Bills

Location: Line 2251

Purpose: AJAX table for credit/book sales bills only

---

9. showallajaxret() - AJAX Returns Bills Table

Location: Line 2695

Purpose: Generate AJAX DataTable for return bills

Process Flow:

1. Similar to sales bills but queries returnsellbill table

2. Includes return-specific fields and calculations

3. Links to original sales bills for reference

---

10. showallajaxretCash() - Cash Returns

Location: Line 3137

Purpose: AJAX table for cash return bills only

---

11. showallajaxretBook() - Credit Returns

Location: Line 3583

Purpose: AJAX table for credit return bills only

---

12. reviewUnReview() - Review Status Management

Location: Line 705

Purpose: Change review status of bills (approve/unapprove)

Function Signature:

// Parameters: billId, billtype, reviewType
$billId = $_REQUEST['billId'];
$billtype = $_REQUEST['billtype']; // 'sell', 'retsell', 'sellAndRet'
$reviewType = $_REQUEST['reviewType']; // 0=unapprove, 1=approve

Process Flow:

1. Validate user permissions for review actions

2. Insert/update review record in appropriate review table

3. Update bill's review status

4. Record review timestamp and user

5. Return JSON success/error response

---

13. printReceipt - Receipt Generation

Location: Line 661

Purpose: Generate printable receipts for bills

Process Flow:

1. Get bill ID and type from request

2. Load client information based on bill type

3. Format receipt data for printing

4. Return formatted receipt view

---

14. laterNakdiPayment - Delayed Cash Payment Processing

Location: Line 850

Purpose: Process cash payments for credit bills

Process Flow:

1. Validate payment amount and bill details

2. Update client debt via payClientDept()

3. Record cash register transaction

4. Update bank account if applicable

5. Create accounting entries (daily entry)

---

15. payClientDept() - Client Payment Processing

Location: Line 4029

Purpose: Core function for processing customer payments

Function Signature:

function payClientDept($clientid, $payedDept, $visapayed = 0, $paysaveid = '', $payaccountid = '')

Process Flow:

1. Lock client record (inUse = 1) to prevent concurrent updates

2. Validate payment amount against client debt

3. Update client debt balance

4. Record debt change transaction

5. Process cash register update if cash payment

6. Process bank account update if card/transfer payment

7. Create accounting entries (debit/credit)

8. Release client lock (inUse = 0)

Accounting Integration:

// Create daily entry
$myDailyEntry->dailyentrydebit = $payedDept;
$myDailyEntry->dailyentrycredit = $payedDept;
$myDailyEntry->dailyentrycomment = "دفعة من العميل";

// Create creditor entry (client account)
$myDailyEntryCreditor->dailyentrycreditoraccountid = $client->clientaccountid;
$myDailyEntryCreditor->dailyentrycreditoramount = $payedDept;

// Create debtor entry (cash/bank account)  
$myDailyEntryDebtor->dailyentrydebtoraccountid = $saveTreeId; // or $bankTreeId
$myDailyEntryDebtor->dailyentrydebtorAmount = $payedDept;

---

16. unifyDate() - Date Standardization

Location: Line 1338

Purpose: Standardize date formats across different bill types

Function Signature:

function unifyDate($allDataArr, $datefrom, $dateto)

Process Flow:

1. Merge arrays from different bill types

2. Standardize date fields for consistent sorting

3. Apply date range filters

4. Return unified dataset

---

🔄 Workflows

Workflow 1: Comprehensive Sales Report Generation

---

Workflow 2: AJAX DataTable Processing

---

Workflow 3: Bill Review Process

---

🌐 URL Routes & Actions

Main Sales Report (do= empty):

• • datefrom - Start date (YYYY-MM-DD)
• • dateto - End date (YYYY-MM-DD)
• • storeId - Store filter (-1 for all)
• • branchId - Branch filter (-1 for all)
• • sellerid - Seller filter (-1 for all)
• • userid - User filter (-1 for all)
• • clientid - Client filter (-1 for all)
• • billtype - Bill type filter (1-5, -1 for all)

AJAX Tables (all showallajax*):

• • DataTable standard parameters (draw, start, length, order, search)
• • Custom filters passed via POST

Review Actions (do=reviewUnReview):

• • billId - Bill ID to review
• • billtype - Bill type ('sell', 'retsell', 'sellAndRet')
• • reviewType - Review action (0=reject, 1=approve)

Payment Processing (do=laterNakdiPayment*):

• • clientid - Customer ID
• • payedDept - Payment amount
• • paysaveid - Cash register ID (for cash payments)
• • payaccountid - Bank account ID (for card payments)

---

🧮 Calculation Methods

Profit Calculation by Evaluation Method

switch ($Programsettingdata->Profitevaluation) {
    case "first":
        // Use first purchase price recorded
        $profit = $sellbilldetailtotalprice - ($sellbilldetailquantity * $firstCost);
        break;
        
    case "last": 
        // Use most recent purchase price
        $profit = $sellbilldetailtotalprice - ($sellbilldetailquantity * $lastCost);
        break;
        
    case "mean":
        // Use average of all purchase prices
        $profit = $sellbilldetailtotalprice - ($sellbilldetailquantity * $meanCost);
        break;
        
    case "generalPrice":
        // Use fixed general price from product
        $profit = $sellbilldetailtotalprice - ($sellbilldetailquantity * $productgeneralprice);
        break;
        
    case "tax":
        // Include tax in profit calculation
        $profit = ($sellbilldetailtotalprice * (1 + $taxRate)) - ($sellbilldetailquantity * $lastCost);
        break;
}

Client Payment Processing

function payClientDept($clientid, $payedDept, $visapayed = 0, $paysaveid = '', $payaccountid = '') {
    // Lock client to prevent concurrent modifications
    $client = getClientDataFromClientInUseSP($clientid);
    
    // Validate payment amount
    if ($payedDept > $client->clientdebt) {
        throw new Exception("Payment exceeds debt balance");
    }
    
    // Update client debt
    $newDebt = $client->clientdebt - $payedDept;
    R::exec("UPDATE client SET clientdebt = $newDebt WHERE clientid = $clientid");
    
    // Record debt change
    insertClientDebtChange($clientid, $payedDept, 1, 'payment');
    
    // Process cash register update
    if (!empty($paysaveid)) {
        $newSaveValue = getSaveValueAndPlus($payedDept, $paysaveid);
        updateSave($paysaveid, $newSaveValue);
        insertSavedaily($oldValue, $payedDept, 1, $paysaveid, 'client_payment');
    }
    
    // Process bank account update  
    if (!empty($payaccountid)) {
        insertBankAccountMovement($payaccountid, $bankId, $oldBalance, 
                                 $payedDept, $newBalance, 1, 'client_payment');
    }
    
    // Create accounting entries
    createDailyEntryForPayment($clientid, $payedDept, $paysaveid, $payaccountid);
    
    // Release client lock
    markClientAsNOTInUse($clientid);
}

Cash Register Movement Tracking

function getSaveValueAndPlus($amount, $saveid) {
    $currentValue = R::getCell("SELECT savevalue FROM save WHERE saveid = $saveid");
    $newValue = $currentValue + $amount;
    return $newValue;
}

function insertSavedaily($saveBefore, $amount, $changeType, $saveid, $process, $modelId = -1, $tablename = '') {
    $savedaily = new Savedaily();
    $savedaily->savedailysavebefore = $saveBefore;
    $savedaily->savedailychangeamount = $amount;
    $savedaily->savedailychangetype = $changeType; // 0=subtract, 1=add
    $savedaily->saveid = $saveid;
    $savedaily->processname = $process;
    $savedaily->savedailymodelid = $modelId;
    $savedaily->savedailysaveafter = $saveBefore + ($changeType == 1 ? $amount : -$amount);
    $savedaily->tablename = $tablename;
    $savedaily->savedailydate = date('Y-m-d H:i:s');
    
    $savedailyDAO->save($savedaily);
}

Bank Account Movement Tracking

function insertBankAccountMovement($accountId, $bankId, $beforeVal, $amount, $afterVal, 
                                  $type, $comment, $tablename, $modelId, $clientId) {
    $movement = new Accountmovement();
    $movement->accountId = $accountId;
    $movement->bankId = $bankId;
    $movement->beforeVal = $beforeVal;
    $movement->amount = $amount;
    $movement->afterVal = $afterVal;
    $movement->type = $type; // 0=debit, 1=credit
    $movement->comment = $comment;
    $movement->tablename = $tablename;
    $movement->modelId = $modelId;
    $movement->clientId = $clientId;
    $movement->created_at = date('Y-m-d H:i:s');
    
    $accountMoveDAO->save($movement);
}

---

🔒 Security & Permissions

User Permission Levels

// ViewBills Permission Check
$user = $userDAO->load($_SESSION['userid']);
if ($user->viewbills == 0) {
    // User can only see their own bills
    $queryString .= ' AND sellbill.userid = ' . $_SESSION['userid'];
} elseif ($user->viewbills == 2) {
    // User can see bills from their user group
    $userGroupIds = getUserGroupMembers($user->usergroupid);
    $queryString .= ' AND sellbill.userid IN (' . implode(',', $userGroupIds) . ')';
}

Permission Levels:

• • viewbills = 0 - Own bills only
• • viewbills = 1 - All bills (admin)
• • viewbills = 2 - User group bills only

Review Permissions

// Check allowed tables for review
$usergroup = $UsergroupDAO->load($_SESSION['usergroupid']);
$allowedTables = explode(",", $usergroup->reviewBillsAllowedTables);

if (!in_array($billtype, $allowedTables)) {
    throw new Exception("Access denied for this bill type");
}

Input Sanitization

// Numeric parameters
$billId = (int) $_REQUEST['billId'];
$clientid = (int) $_REQUEST['clientid'];

// String parameters with escaping
$billserial = addslashes($_REQUEST['billserial']);

// Date validation
if (!empty($datefrom)) {
    $datefrom = date('Y-m-d', strtotime($datefrom)) . ' 00:00:00';
}

---

📊 Performance Considerations

Database Optimization Tips

1. Essential Indexes:

   -- Sales performance
   CREATE INDEX idx_sellbill_date_client ON sellbill(sellbilldate, sellbillclientid);
   CREATE INDEX idx_sellbill_user_date ON sellbill(userid, sellbilldate);
   CREATE INDEX idx_sellbill_conditions ON sellbill(conditions, sellbilldate);
   
   -- Returns performance  
   CREATE INDEX idx_returnsellbill_date ON returnsellbill(returnsellbilldate, returnsellbillclientid);
   
   -- Review performance
   CREATE INDEX idx_sellbillreview_bill ON sellbillreview(sellbillid, reviewdate);
   
   -- Payment performance
   CREATE INDEX idx_clientdebtchange_client_date ON clientdebtchange(clientid, clientdebtchangedate);
   ```

2. **Query Optimization**:
   - Use proper date ranges with time components
   - Limit result sets with appropriate WHERE clauses
   - Use JOINs efficiently for related data
   - Avoid N+1 queries in loops

3. **Memory Management**:
   - Large date ranges may return thousands of records
   - Consider implementing server-side pagination for all views
   - Use AJAX tables for better user experience

### Known Performance Issues
1. **Profit Calculations**: 
   - Multiple cost queries per product can be slow
   - Consider caching common calculations
   - Pre-calculate profit margins during bill saving

2. **Review Status Queries**:
   - Subqueries for review status can be expensive
   - Consider denormalizing review status to main tables

3. **Large Dataset Handling**:
   - Monthly reports for busy stores can timeout
   - Implement background processing for complex reports
   - Add progress indicators for long-running operations

---

## 🐛 Common Issues & Troubleshooting

### 1. **AJAX Table Loading Issues**
**Issue**: DataTable fails to load data or shows "No data available"
**Causes**: 
- JSON parsing errors due to PHP errors in response
- Incorrect column configuration  
- Permission restrictions blocking data access

**Debug**:

// Check browser console for JavaScript errors

// Inspect AJAX response in network tab

// Verify JSON structure matches DataTable expectations

### 2. **Permission Denied Errors**
**Issue**: Users cannot see bills they should have access to
**Causes**:
- Incorrect `viewbills` permission setting
- Missing user group configuration
- Branch/store restrictions too restrictive

**Debug**:

-- Check user permissions

SELECT userid, viewbills, saveids, usergroupid FROM user WHERE userid = [USER_ID];

-- Check user group settings

SELECT usergroupid, reviewBillsAllowedTables FROM usergroup WHERE usergroupid = [GROUP_ID];

### 3. **Profit Calculation Errors**  
**Issue**: Profit values showing as 0 or incorrect amounts
**Causes**:
- Missing purchase price data
- Incorrect `Profitevaluation` setting
- Product without cost information

**Debug**:

-- Check product costs

SELECT productid, productprice, productgeneralprice FROM product WHERE productid = [ID];

-- Check purchase history

SELECT * FROM buybilldetail WHERE buybilldetailproductid = [PRODUCT_ID] ORDER BY buybillid DESC;

-- Check system setting

SELECT settingvalue FROM programsettings WHERE settingkey = 'Profitevaluation';

### 4. **Review Process Failures**
**Issue**: Bills cannot be reviewed or review status not updating
**Causes**:
- User lacks review permissions
- Bill type not in allowed tables list
- Review table relationship issues

**Debug**:

-- Check review permissions

SELECT reviewBillsAllowedTables FROM usergroup WHERE usergroupid = [GROUP_ID];

-- Check existing reviews

SELECT * FROM sellbillreview WHERE sellbillid = [BILL_ID] ORDER BY reviewdate DESC;

### 5. **Payment Processing Errors**
**Issue**: Client payments fail or don't update balances correctly
**Causes**:
- Client record locked (`inUse = 1`)
- Insufficient permissions for cash register access
- Bank account configuration issues

**Fix**:

-- Release stuck client locks

UPDATE client SET inUse = 0 WHERE clientid = [CLIENT_ID];

-- Check save permissions

SELECT saveid, saveids FROM user WHERE userid = [USER_ID];

-- Verify bank account setup

SELECT accountid, balance, treeId FROM bankaccount WHERE accountid = [ACCOUNT_ID];

---

## 🧪 Testing Scenarios

### Test Case 1: Basic Sales Report

1. Select date range with known sales data

2. Apply various filters (store, seller, client)

3. Verify data accuracy against database

4. Check profit calculations match expected values

5. Test export/print functionality

### Test Case 2: AJAX Table Performance

1. Load table with large dataset (1000+ records)

2. Test pagination navigation

3. Test column sorting on different fields

4. Test search functionality (global + column)

5. Verify server-side filtering works correctly

### Test Case 3: Review Workflow

1. Login with different permission levels

2. Attempt to review bills of various types

3. Verify permission restrictions work

4. Test approve/reject functionality

5. Check review history tracking

### Test Case 4: Payment Processing

1. Process cash payment for credit client

2. Verify debt balance updates correctly

3. Check cash register movement recorded

4. Verify accounting entries created

5. Test bank account payment processing

### Debug Mode Enable

// Add at top of controller for debugging

error_reporting(E_ALL);

ini_set('display_errors', 1);

// Debug SQL queries

echo "Query: " . $queryString . "
";

// Debug data arrays

echo "

";

print_r($salesData);

echo "

// Debug AJAX responses

header('Content-Type: application/json');

echo json_encode($debugData);

```

---

📚 Related Documentation

• • CLAUDE.md - PHP 8.2 migration guide
• • sellbillController.md - Sales bill creation
• • returnSellBillController.md - Returns processing
• • clientReportsController.md - Customer reports
• • saveController.md - Cash register management

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur