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

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

Related Controllers

• • freebuys.php - Free purchase reporting
• • sellbillController.php - Sales management
• • returnsellbillController.php - Sales returns
• • bills.php - Optical sales

---

🗄️ Database Tables

Sales Bill Tables (Regular Sales)

🔑 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:

function getData($queryString, $queryString1, $queryStringR, $queryString1R, $queryString1SR, $searchtype, $productCatId, $theStore)

Zero-Cost Query Filters:

$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

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:

// 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

---

🌐 URL Routes & Actions

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

// 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

$quantity = $value->sellbilldetailquantity;
$productunitId = $value->productunitid;
$productunitData = loadProductUnitWithProductAndUnit($productId, $productunitId);
$productnumber = $productunitData->productnumber;
$finalquantity = $quantity * $productnumber;

Bill-Level Discount Allocation

$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:

-- 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:

-- 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 - Free purchase reporting
• • sellbillController.md - Sales management
• • bills.md - Optical shop management
• • returnsellbillController.md - Sales returns

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur