# Free Sales Controller Documentation

**File**: `/controllers/freesales.php`  
**Purpose**: Free sales transactions reporting and analysis system  
**Last Updated**: December 20, 2024  
**Total Functions**: 6+  
**Lines of Code**: ~813

---

## 📋 Overview

The Free Sales Controller manages and reports on sales transactions with zero cost (free sales/giveaways). It provides:
- Free sales transaction analysis across multiple bill types
- Optical shop and regular sales integration
- Product profitability analysis for promotional items
- Category and date-based filtering
- Detailed transaction drill-down with links
- Cost analysis and profit/loss calculations
- Multi-bill type consolidation (sales, returns, combined, optical)

### Primary Functions
- [x] Comprehensive free sales reporting
- [x] Optical shop integration (bills/billsreturn)
- [x] Regular sales integration (sellbill/returnsellbill)
- [x] Combined bill processing (sellbillandrutern)
- [x] Product profitability analysis
- [x] Category hierarchy filtering
- [x] Transaction detail drill-down
- [x] Cost and profit calculations

### Related Controllers
- [freebuys.php](freebuys.md) - Free purchase reporting
- [sellbillController.php](sellbillController.md) - Sales management
- [returnsellbillController.php](returnsellbillController.md) - Sales returns
- [bills.php](bills.md) - Optical sales

---

## 🗄️ Database Tables

### Sales Bill Tables (Regular Sales)
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **sellbilldetail** | Sales bill line items | sellbilldetailid, sellbillid, sellbilldetailproductid, sellbilldetailquantity, sellbilldetailtotalprice, productunitid, pricetype |
| **returnsellbilldetail** | Sales return details | returnsellbilldetailid, returnsellbillid, returnsellbilldetailproductid, returnsellbilldetailquantity, returnsellbilldetailtotalprice |
| **sellandruternbilldetail** | Combined sales/return | sellandruternbilldetailid, sellbillid, sellbilldetailproductid, sellbilldetailquantity, sellbilldetailtotalprice, selltype |

### Optical Bill Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **billsproducts** | Optical sales products | billsproductsid, billid, productid, productno, producttotalprice |
| **billsreturnproducts** | Optical return products | billsreturnproductsid, billproductid, productid, productno, producttotalprice |

### Bill Header Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **sellbill** | Sales bills | sellbillid, sellbilldate, sellbillclientid, sellbillstoreid |
| **returnsellbill** | Sales returns | returnsellbillid, returnsellbilldate, returnsellbillclientid |
| **sellbillandrutern** | Combined bills | sellbillid, sellbilldate, sellbillclientid |
| **bills** | Optical sales bills | billid, billdate, clientid, pricetype, card, paymentnetworkid, cardvalue, netdiscountpercent |
| **billsreturn** | Optical returns | billproductid, date |

### Reference Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **product** | Product master data | productId, productName, productCatId, productBuyPrice, isOptic |
| **productcat** | Product categories | productCatId, productCatName, productCatParent |
| **productunit** | Unit conversions | productunitid, productid, unitid, productnumber |

---

## 🔑 Key Functions

### 1. **Default Action** - Free Sales Report Interface
**Location**: Line 134  
**Purpose**: Display search interface and generate comprehensive free sales reports

**Process Flow**:
1. Load category hierarchy for filtering
2. Process search parameters (dates, categories, products)
3. Build query strings for all bill types (5 different types)
4. Filter for zero-cost transactions across all tables
5. Call `getData()` to process and consolidate results

**Multi-Bill Type Support**:
- `bills` - Optical shop sales
- `billsreturn` - Optical shop returns
- `sellbill` - Regular sales bills
- `returnsellbill` - Regular sales returns
- `sellbillandrutern` - Combined sales/return bills

### 2. **getData()** - Comprehensive Report Processing
**Location**: Line 326  
**Purpose**: Process free sales across all bill types and calculate profitability

**Function Signature**:
```php
function getData($queryString, $queryString1, $queryStringR, $queryString1R, $queryString1SR, $searchtype, $productCatId, $theStore)
```

**Zero-Cost Query Filters**:
```php
$queryString .= 'and  billsproducts.producttotalprice = 0 ';
$queryString1 .= 'and  sellbilldetail.sellbilldetailtotalprice = 0 ';
$queryStringR .= 'and  billsreturnproducts.producttotalprice = 0 ';
$queryString1R .= 'and  returnsellbilldetail.returnsellbilldetailtotalprice = 0 ';
$queryString1SR .= 'and  sellandruternbilldetail.sellbilldetailtotalprice = 0 ';
```

**Bill Type Processing**:

**Optical Sales (`bills`)**:
- Direct quantity processing (no unit conversion)
- Network payment discount calculation
- Card payment discount handling
- MADA payment special processing

**Regular Sales (`sellbill`)**:
- Unit conversion to standard quantities
- Bill-level discount allocation
- Line-level discount subtraction
- Pricetype filtering support

**Combined Bills (`sellbillandrutern`)**:
- Type-based processing (selltype: 0=sale, 1=return)
- Unified discount calculation
- Consolidated reporting

### 3. **Network Payment Discount Calculation**
**Purpose**: Handle optical shop payment method discounts

```php
if ($theBill->card == 1) {
    if ($theBill->paymentnetworkid == 4) {
        // MADA payment processing
        $madaData = $billsEX->queryTotalNetworkReportMadaSimple($theBill->billdate);
        if ($madaData->totalCarry < 5000)
            $dicount = (7 * $madaData->totalCarry) / 1000;
        else
            $dicount = 40;
    } else {
        // Other card networks
        $dicount = ($theBill->cardvalue * $theBill->netdiscountpercent) / 100;
    }
}
```

### 4. **getDetails()** - Multi-Source Transaction Details
**Location**: Line 640  
**Purpose**: Provide detailed transaction drill-down across all bill types

**Process Flow**:
1. Query each bill type separately
2. Format results with appropriate bill URLs
3. Handle unit conversions where applicable
4. Consolidate into single result set

**Bill URL Generation**:
```php
// Optical bills
$myproduct->billURL = "bills.php?do=details&id=" . $value->id;

// Sales bills  
$myproduct->billURL = "sellbillController.php?do=showDetail&sellbillid=" . $value->sellbillid;

// Return bills
$myproduct->billURL = "returnsellbillController.php?do=showDetail&returnsellbillid=" . $value->returnsellbillid;

// Combined bills
$myproduct->billURL = "sellbillandruternController.php?do=showDetail&sellbillid=" . $value->sellbillid;
```

---

## 🔄 Workflows

### Workflow 1: Comprehensive Free Sales Analysis
```
┌─────────────────────────────────────────────────────────────┐
│                START: Multi-Source Analysis                │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Query All Bill Types                                   │
│     - Optical sales (bills/billsproducts)                 │
│     - Optical returns (billsreturn/billsreturnproducts)   │
│     - Regular sales (sellbill/sellbilldetail)             │
│     - Sales returns (returnsellbill/returnsellbilldetail) │
│     - Combined bills (sellbillandrutern/detail)           │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Filter Zero-Cost Transactions                          │
│     - Apply producttotalprice = 0 filters                 │
│     - Apply sellbilldetailtotalprice = 0 filters          │
│     - Include date and category filters                   │
│     - Handle pricetype filtering for sales                │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Process Each Transaction Type                          │
│     FOR EACH bill type:                                   │
│       │                                                     │
│       ├─→ OPTICAL: Process without unit conversion        │
│       │   ├─ Calculate network payment discounts          │
│       │   ├─ Handle MADA special rates                    │
│       │   └─ Apply card payment discounts                 │
│       │                                                     │
│       ├─→ REGULAR: Convert units to standard quantities   │
│       │   ├─ Apply bill-level discount allocation         │
│       │   ├─ Subtract line-level discounts                │
│       │   └─ Handle pricetype filtering                   │
│       │                                                     │
│       └─→ COMBINED: Process by transaction type           │
│           ├─ Handle sale vs return differentiation        │
│           └─ Apply unified discount calculations          │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Consolidate and Analyze                                │
│     - Aggregate by product across all sources             │
│     - Calculate net quantities (sold - returned)          │
│     - Determine real costs from product master            │
│     - Calculate profit/loss on free items                 │
│     - Get current inventory levels                        │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Generate Comprehensive Report                          │
│     - Display consolidated product analysis               │
│     - Show multi-source transaction summary               │
│     - Provide drill-down to source transactions          │
│     - Calculate category and overall totals               │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=` (empty) | Default action | Free sales comprehensive report |
| `do=detail` | `getDetails()` | Multi-source transaction details |

### Required Parameters

**Summary Report**:
- `datefrom/dateto` - Date range (optional, defaults to today)
- `level` - Category hierarchy level
- `productCatId{level}` - Category filter
- `productId` - Product filter
- `searchtype` - Must be 0 for standard search
- `proIsOptic` - Optical products flag

**Detail Report** (`do=detail`):
- Same as summary plus:
- `id` - Product ID for detailed view

---

## 🧮 Calculation Methods

### Optical Shop Discount Calculation
```php
// MADA payment special rates
if ($theBill->paymentnetworkid == 4) {
    $madaData = $billsEX->queryTotalNetworkReportMadaSimple($theBill->billdate);
    if ($madaData->totalCarry < 5000)
        $dicount = (7 * $madaData->totalCarry) / 1000;
    else
        $dicount = 40;
} else {
    // Standard card discount
    $dicount = ($theBill->cardvalue * $theBill->netdiscountpercent) / 100;
}
```

### Regular Sales Unit Conversion
```php
$quantity = $value->sellbilldetailquantity;
$productunitId = $value->productunitid;
$productunitData = loadProductUnitWithProductAndUnit($productId, $productunitId);
$productnumber = $productunitData->productnumber;
$finalquantity = $quantity * $productnumber;
```

### Bill-Level Discount Allocation
```php
$dicount = $value->parcode - $value->note;  // Bill discount
if ($dicount != 0) {
    $billpecies = $sellbilldetailEX->queryBillNoOfPecies($value->sellbillid);
    $billNoOfProduct = $billpecies->note;
    $theDiscount = ($finalquantity * $dicount) / $billNoOfProduct;
    $theDiscount -= $value->discountvalue;  // Subtract line discount
}
```

---

## 🔒 Security & Permissions

### Authentication Required
- All actions require valid session
- User authentication via `../public/authentication.php`

### Multi-Source Data Access
- Queries across sales and optical systems
- Consolidated permission checking
- No sensitive pricing data exposed for free items

---

## 📊 Performance Considerations

### Query Optimization
1. **Multi-Source Efficiency**: Parallel querying of different bill types
2. **Zero-Cost Early Filtering**: Applied before joins
3. **Product Aggregation**: Efficient array indexing across sources
4. **Unit Conversion Caching**: Minimizes repeated calculations

### Memory Management
- Large result sets from multiple sources handled efficiently
- Product aggregation uses optimized data structures

---

## 🐛 Common Issues & Troubleshooting

### 1. **Missing Optical Transactions**
**Issue**: Optical shop free items not appearing  
**Cause**: Different table structure or field naming

**Debug**:
```sql
-- Check optical sales structure
SELECT bp.productid, bp.productno, bp.producttotalprice
FROM billsproducts bp
JOIN bills b ON b.billid = bp.billid
WHERE bp.producttotalprice = 0
AND DATE(b.billdate) = CURDATE();
```

### 2. **Unit Conversion Errors**
**Issue**: Regular sales quantities incorrect  
**Cause**: Missing or wrong productunit records

**Fix**:
```sql
-- Verify unit conversion data
SELECT p.productName, pu.productnumber, u.unitName
FROM product p
LEFT JOIN productunit pu ON pu.productid = p.productId
LEFT JOIN unit u ON u.unitId = pu.unitid
WHERE p.productId = [PRODUCT_ID];
```

### 3. **MADA Discount Calculation Issues**
**Issue**: Incorrect optical discount calculations  
**Cause**: MADA daily total calculation errors

**Debug**: Check `queryTotalNetworkReportMadaSimple()` method

### 4. **Combined Bill Type Confusion**
**Issue**: Combined bills showing wrong sale/return split  
**Cause**: `selltype` field interpretation

**Fix**: Verify selltype values (0=sale, 1=return) in database

---

## 🧪 Testing Scenarios

### Test Case 1: Multi-Source Free Sales Report
```
1. Set date range covering known free sales
2. Run report without filters  
3. Verify items from all bill types appear
4. Check optical and regular sales integration
5. Confirm quantity and discount calculations
```

### Test Case 2: Optical vs Regular Sales
```
1. Test with optical products (isOptic=2)
2. Test with regular products
3. Verify different calculation methods
4. Check discount handling differences
5. Confirm proper categorization
```

### Test Case 3: Payment Method Discounts
```
1. Test optical sales with card payments
2. Verify MADA special rate calculations
3. Check standard card discount calculations
4. Test cash sales (no discounts)
```

### Test Case 4: Combined Bill Processing
```
1. Create combined sales/return bill with free items
2. Verify correct sale/return split
3. Check quantity calculations
4. Test drill-down functionality
```

---

## 📚 Related Documentation

- [freebuys.md](freebuys.md) - Free purchase reporting
- [sellbillController.md](sellbillController.md) - Sales management
- [bills.md](bills.md) - Optical shop management
- [returnsellbillController.md](returnsellbillController.md) - Sales returns

---

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