# Charts ERP Controller Documentation

**File**: `/controllers/chartserp.php`  
**Purpose**: Business intelligence charts and analytics for sales data visualization  
**Last Updated**: December 20, 2024  
**Total Functions**: 8  
**Lines of Code**: ~333

---

## 📋 Overview

The Charts ERP Controller is a specialized analytics module that provides data visualization and business intelligence capabilities. It handles:
- Sales analytics by categories, products, and clients
- Time-series data analysis with monthly aggregation
- Profit calculation using different evaluation methods
- Interactive chart data generation for frontend visualization
- Select2 autocomplete data feeds for search functionality
- Dynamic date range analysis
- JSON API responses for AJAX chart updates

### Primary Functions
- [x] Category-based sales analytics
- [x] Product performance analysis  
- [x] Client sales tracking
- [x] Monthly sales trend analysis
- [x] Profit evaluation calculations
- [x] Search autocomplete data feeds
- [x] JSON API responses for charts
- [x] Dynamic date filtering

### Related Controllers
- [sellbillController.php](sellbillController.md) - Sales data source
- [productReportsController.php](productReportsController.md) - Product analytics
- [clientReportsController.php](clientReportsController.md) - Client analytics

---

## 🗄️ Database Tables

### Primary Sales Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **sellbill** | Sales bills master | sellbillid, sellbillclientid, sellbilldate |
| **sellbilldetail** | Sales line items | sellbillid, sellbilldetailproductid, sellbilldetailcatid, sellbilldetailquantity, sellbilldetailtotalprice |
| **client** | Customer master | clientid, clientname |
| **product** | Product master | productId, productName, productCatId |
| **productcat** | Product categories | productCatId, productCatName |

### System Configuration
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **programsettings** | System settings | programsettingsid, Profitevaluation |

### Test Data Table
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **tbl_marks** | Sample chart data | id, student_name, marks |

---

## 🔑 Key Functions

### 1. **Default Action** - Chart Dashboard
**Location**: Line 4  
**Purpose**: Display main charts dashboard

**Function Signature**:
```php
if (empty($do)) {
   $smarty->display("chartserp/index.html");
}
```

**Available Chart Pages**:
- `do=chartssellbill` - Sales bill charts
- `do=chartsproducts` - Product performance charts  
- `do=chartsclients` - Client analysis charts
- `do=chartscategories` - Category analytics charts

---

### 2. **categories()** - Category Sales Analytics
**Location**: Line 85  
**Purpose**: Analyze sales performance by product categories

**Function Signature**:
```php
function categories()
```

**Parameters**:
- `$fromdate` - Start date for analysis
- `$todate` - End date for analysis  
- `$kindprice` - Price evaluation method
- `$categorieid` - Specific category ID (optional)

**Process Flow**:
1. **Time Range Processing**:
   - If specific category: monthly aggregation from fromdate to todate
   - If no category: single date range query
2. **Monthly Loop** (for specific category):
   ```php
   while($fromdate <= $todate) {
       $start = date('Y-m-d', $fromdate);
       $end = date("Y-m-t", strtotime($start));
       // Process month data
       $fromdate = strtotime("+1 month", $fromdate);
   }
   ```
3. **Query Execution**:
   ```sql
   SELECT sum(sellbilldetailquantity) as quantitys, 
          sum(sellbilldetailtotalprice) as totals,
          productCatName as Namedata, 
          SUM(kindprice * sellbilldetailquantity) AS totalpro 
   FROM sellbilldetail 
   LEFT JOIN productcat ON sellbilldetailcatid = productCatId
   ```

**Output**: JSON array with category performance data

---

### 3. **products()** - Product Performance Analysis
**Location**: Line 129  
**Purpose**: Generate product-specific sales analytics

**Function Signature**:
```php
function products()
```

**Parameters**:
- `$productid` - Specific product filter
- `$fromdate/$todate` - Date range
- `$kindprice` - Profit evaluation method

**Two Analysis Modes**:
1. **Single Product Monthly Trend**: If productid specified
2. **All Products Summary**: If no productid specified

**Query Structure**:
```sql
SELECT sum(sellbilldetailquantity) as quantitys, 
       sum(sellbilldetailtotalprice) as totals, 
       productName as Namedata,
       SUM(kindprice * sellbilldetailquantity) AS totalpro
FROM sellbilldetail 
LEFT JOIN product ON sellbilldetailproductid = productId
```

---

### 4. **clients()** - Client Sales Analysis
**Location**: Line 173  
**Purpose**: Analyze sales performance by customer

**Function Signature**:
```php
function clients()
```

**Two Analysis Modes**:
1. **Single Client Monthly**: When clientid specified
2. **All Clients Summary**: When no clientid specified

**Key Query**:
```sql
SELECT sum(sellbilldetailquantity) as quantitys,
       sum(sellbilldetailtotalprice) as totals,
       clientname as Namedata,
       SUM(kindprice * sellbilldetailquantity) AS totalpro
FROM sellbill
LEFT JOIN sellbilldetail ON sellbillid = sellbilldetail.sellbillid
LEFT JOIN client ON sellbillclientid = clientid
GROUP BY sellbillclientid
```

---

### 5. **sellbills()** - Overall Sales Trend
**Location**: Line 259  
**Purpose**: Generate monthly sales trend analysis

**Function Signature**:
```php
function sellbills()
```

**Process**:
1. **Date Range Setup**:
   ```php
   if($fromdate != '' && $todate != ''){
       $fromdate = strtotime("first day of this month", strtotime($fromdate));
       $todate = strtotime("last day of this month", strtotime($todate));
   }
   ```

2. **Monthly Aggregation**:
   ```php
   $mounths = array(' ','يناير','فبراير','مارس','أبريل','مايو','يونيو',
                   'يوليو','أغسطس','سبتمبر','أكتوبر','نوفمبر','ديسمبر');
   $mounthds = $mounths[intval($mounthd)];
   ```

3. **Sales Summary Query**: Aggregates all sales data by month

---

### 6. **profitevaluation()** - Profit Calculation Method
**Location**: Line 292  
**Purpose**: Determine profit calculation method from system settings

**Function Signature**:
```php
function profitevaluation()
```

**Evaluation Methods**:
```php
switch ($Profitevaluation) {
    case 'first':           return 'sellbilldetail.buyprice';
    case 'last':            return 'sellbilldetail.lastbuyprice';
    case 'last_discount':   return 'sellbilldetail.lastbuyprice_withDiscount';
    case 'mean':            return 'sellbilldetail.meanbuyprice';
    case 'mean_discount':   return 'sellbilldetail.meanbuyprice_withDiscount';
    case 'generalPrice':    return 'product.productBuyPrice';
    default:                return 'sellbilldetail.buyprice';
}
```

---

### 7. **Select2 Autocomplete Functions**
**Location**: Lines 32, 50, 68  
**Purpose**: Provide search autocomplete data for frontend components

**Functions**:
- `select2categories()` - Category search
- `select2products()` - Product search with category path
- `select2clients()` - Client search

**Example Implementation**:
```php
function select2products() {
    $name = $_POST['searchTerm']; 
    $productsData = R::getAll("SELECT productId,
                               CONCAT(productName,'/',productCatName) as name
                               FROM product 
                               LEFT JOIN productcat ON product.productCatId = productcat.productCatId
                               WHERE CONCAT(productName,'/',productCatName) LIKE '%$name%' 
                               LIMIT 50");
    // Format for Select2 dropdown
    foreach ($productsData as $pro) {
        $row_array['id'] = $pro['productId'];
        $row_array['text'] = $pro['name'];
        array_push($return_arr, $row_array);
    }
    echo json_encode($return_arr);
}
```

---

### 8. **getdata()** - Sample Data Generator
**Location**: Line 322  
**Purpose**: Generate sample chart data for testing

**Function Signature**:
```php
function getdata()
```

**Usage**: Returns sample data from `tbl_marks` table for chart testing

---

## 🔄 Workflows

### Workflow 1: Category Analytics Generation
```
┌─────────────────────────────────────────────────────────────┐
│              START: Category Analytics Request             │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Parse Request Parameters                                │
│     - Extract fromdate, todate                              │
│     - Get categorieid (specific or all)                     │
│     - Determine profit evaluation method                    │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Determine Analysis Mode                                 │
│     IF categorieid specified:                              │
│       └─→ Monthly trend analysis for category              │
│     ELSE:                                                   │
│       └─→ All categories summary for date range            │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Execute Data Queries                                    │
│     FOR EACH month (if monthly analysis):                  │
│       │                                                     │
│       ├─→ Calculate month start/end dates                  │
│       ├─→ Execute sales aggregation query                  │
│       ├─→ Include profit calculations                      │
│       └─→ Add Arabic month name                            │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Format and Return Data                                  │
│     - Structure data for chart consumption                  │
│     - Include quantities, totals, profit                    │
│     - Return JSON response                                  │
└─────────────────────────────────────────────────────────────┘
```

### Workflow 2: Search Autocomplete
```
┌─────────────────────────────────────────────────────────────┐
│              START: User Types in Search Box               │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. AJAX Request to Controller                              │
│     - Send searchTerm parameter                             │
│     - Specify entity type (product/client/category)         │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Execute Search Query                                    │
│     - Use LIKE operator for partial matching               │
│     - Join relevant tables for context                     │
│     - Limit results to 50 items                            │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Format for Select2                                     │
│     - Structure as {id, text} objects                      │
│     - Include relevant context (e.g., category path)       │
│     - Return JSON array                                     │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=` (empty) | Default | Charts dashboard homepage |
| `do=categories` | `categories()` | Category analytics data |
| `do=products` | `products()` | Product performance data |
| `do=clients` | `clients()` | Client sales analytics |
| `do=sellbills` | `sellbills()` | Overall sales trends |
| `do=select2products` | `select2products()` | Product search autocomplete |
| `do=select2clients` | `select2clients()` | Client search autocomplete |
| `do=select2categories` | `select2categories()` | Category search autocomplete |
| `do=chartssellbill` | Template | Sales bill charts page |
| `do=chartsproducts` | Template | Product charts page |
| `do=chartsclients` | Template | Client charts page |
| `do=chartscategories` | Template | Category charts page |
| `do=getdata` | `getdata()` | Sample chart data |

### POST Parameters

**Analytics Functions**:
- `fromdate` - Start date (YYYY-MM-DD)
- `todate` - End date (YYYY-MM-DD)
- `kindprice` - Profit evaluation method
- `categorieid` - Category filter ID
- `productid` - Product filter ID  
- `clientid` - Client filter ID

**Search Functions**:
- `searchTerm` - Search query string

---

## 📊 Data Formats

### Analytics Response Format
```json
[
  {
    "quantitys": "150",
    "totals": "15000.00", 
    "Namedata": "Electronics",
    "totalpro": "12000.00"
  },
  {
    "quantitys": "75",
    "totals": "8500.00",
    "Namedata": "Computers", 
    "totalpro": "7200.00"
  }
]
```

### Select2 Response Format
```json
[
  {
    "id": "123",
    "text": "Samsung Galaxy/Electronics/Mobile Phones"
  },
  {
    "id": "124", 
    "text": "iPhone 13/Electronics/Mobile Phones"
  }
]
```

### Monthly Trend Format
```json
[
  {
    "quantitys": "200",
    "totals": "25000.00",
    "Namedata": "يناير",
    "totalpro": "20000.00"
  },
  {
    "quantitys": "180", 
    "totals": "22000.00",
    "Namedata": "فبراير",
    "totalpro": "18500.00"
  }
]
```

---

## 🔒 Security & Performance

### SQL Injection Prevention
- Uses RedBean ORM with parameterized queries
- Direct SQL only for complex aggregations
- Input validation for date formats

### Performance Optimization
1. **Query Limits**: All search queries limited to 50 results
2. **Date Indexing**: Requires indexes on date fields
3. **Aggregation Efficiency**: Uses GROUP BY for summary data

### Required Database Indexes
```sql
CREATE INDEX idx_sellbilldetail_date ON sellbilldetail(sellbilldetaildate);
CREATE INDEX idx_sellbilldetail_cat ON sellbilldetail(sellbilldetailcatid);  
CREATE INDEX idx_sellbilldetail_product ON sellbilldetail(sellbilldetailproductid);
CREATE INDEX idx_product_name ON product(productName);
CREATE INDEX idx_client_name ON client(clientname);
```

---

## 🧮 Chart Integration

### Frontend Chart Libraries
The controller provides JSON data compatible with:
- Chart.js
- D3.js  
- Google Charts
- Highcharts

### Typical Frontend Usage
```javascript
// Fetch category data
$.post('chartserp.php?do=categories', {
    fromdate: '2024-01-01',
    todate: '2024-12-31',
    categorieid: ''
}, function(data) {
    // data is ready for chart consumption
    createChart(JSON.parse(data));
});

// Product search autocomplete
$('#productSearch').select2({
    ajax: {
        url: 'chartserp.php?do=select2products',
        type: 'POST',
        dataType: 'json',
        data: function(params) {
            return { searchTerm: params.term };
        }
    }
});
```

---

## 🐛 Common Issues & Troubleshooting

### 1. **Empty Chart Data**
**Issue**: Charts show no data  
**Cause**: Date range or filters too restrictive

**Debug**:
```sql
SELECT COUNT(*) FROM sellbilldetail 
WHERE sellbilldetaildate BETWEEN 'start' AND 'end';
```

### 2. **Slow Search Responses**
**Issue**: Select2 autocomplete is slow  
**Cause**: Missing database indexes

**Fix**: Add indexes on search fields

### 3. **Profit Calculations Wrong**
**Issue**: Profit values don't match expectations  
**Cause**: Wrong evaluation method selected

**Debug**: Check `profitevaluation()` output and settings table

---

## 📚 Related Documentation

- [CLAUDE.md](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [sellbillController.md](sellbillController.md) - Sales data source
- [productReportsController.md](productReportsController.md) - Detailed reporting
- Chart.js Documentation - Frontend integration guide

---

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