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

✅ Category-based sales analytics
✅ Product performance analysis
✅ Client sales tracking
✅ Monthly sales trend analysis
✅ Profit evaluation calculations
✅ Search autocomplete data feeds
✅ JSON API responses for charts
✅ Dynamic date filtering

Related Controllers

• sellbillController.php - Sales data source
• productReportsController.php - Product analytics
• clientReportsController.php - 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:

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:

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

   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 - PHP 8.2 migration guide
• sellbillController.md - Sales data source
• productReportsController.md - Detailed reporting
• Chart.js Documentation - Frontend integration guide

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur

📚 ERP Docs

Chartserp Documentation

Charts ERP Controller Documentation

📋 Overview

Primary Functions

Related Controllers

🗄️ Database Tables

Primary Sales Tables

🔑 Key Functions

1. Default Action - Chart Dashboard

2. categories() - Category Sales Analytics

2. Slow Search Responses

3. Profit Calculations Wrong

📚 Related Documentation