Catandproductstatistics Documentation

Category and Product Statistics Controller Documentation

File: /controllers/catandproductstatistics.php

Purpose: Provides statistical reporting for product categories and products including counts and data quality metrics

Last Updated: December 20, 2024

Total Functions: 2 main functions

Lines of Code: ~108

---

📋 Overview

The Category and Product Statistics Controller is a reporting module that provides statistical insights into product and category data quality and completeness. It handles:

Primary Functions

Related Controllers

---

🗄️ Database Tables

Primary Tables (Direct Operations)

Table NamePurposeKey Columns
**productcat**Product categoriesproductCatId, productCatName, productCatDescription, logo, conditions, productCatParent
**product**Products/inventory itemsproductId, productName, productDescription, logo, conditions
### Reference Tables

Table NamePurposeKey Columns
**youtubelink**Tutorial videosyoutubelinkId, title, url
---

🔑 Key Functions

1. Category Statistics (do=cat) - Category Data Quality Report

Location: Lines 52-70

Purpose: Generate comprehensive statistics about product categories

Process Flow:

1. Check user authentication

2. Load YouTube tutorial links

3. Execute statistical queries for categories:

- Total active categories count

- Categories with images count

- Categories with descriptions count

- Categories with both images and descriptions count

4. Assign data to Smarty template variables

5. Display via catandproductstatisticsview/cat.html

SQL Queries: 

-- Total active categories
select count(*) as productId from productcat where conditions = 0

-- Categories with images
select count(*) as productId from productcat 
where conditions = 0 and logo != "." and logo != "" and logo != "no image"

-- Categories with descriptions  
select count(*) as productId from productcat 
where conditions = 0 and productCatDescription != ""

-- Complete categories (image + description)
select count(*) as productId from productcat 
where conditions = 0 and productCatDescription != "" 
and logo != "." and logo != "" and logo != "no image"

Template Variables:

---

2. Product Statistics (do=pro) - Product Data Quality Report

Location: Lines 71-89

Purpose: Generate comprehensive statistics about products/inventory

Process Flow:

1. Check user authentication

2. Load YouTube tutorial links

3. Execute statistical queries for products:

- Total active products count

- Products with images count

- Products with descriptions count

- Products with both images and descriptions count

4. Assign data to Smarty template variables

5. Display via catandproductstatisticsview/pro.html

SQL Queries: 

-- Total active products
select count(*) as productId from product where conditions = 0

-- Products with images
select count(*) as productId from product 
where conditions = 0 and logo != "." and logo != "" and logo != "no image"

-- Products with descriptions
select count(*) as productId from product 
where conditions = 0 and productDescription != ""

-- Complete products (image + description)
select count(*) as productId from product 
where conditions = 0 and productDescription != "" 
and logo != "." and logo != "" and logo != "no image"

Template Variables:

---

🔄 Workflows

Workflow 1: Category Statistics Generation

┌─────────────────────────────────────────────────────────────┐
START: Access Category Statistics
└────────────────────┬────────────────────────────────────────┘
▼
▼
┌─────────────────────────────────────────────────────────────┐
1Authentication Check
- Verify user permissions
- Include authentication.php
└────────────────────┬────────────────────────────────────────┘
▼
▼
┌─────────────────────────────────────────────────────────────┐
2Load Tutorial Content
- Query youtubelink table
- Assign tutorial links to template
└────────────────────┬────────────────────────────────────────┘
▼
▼
┌─────────────────────────────────────────────────────────────┐
3Execute Statistical Queries
- Count total active categories
- Count categories with images
- Count categories with descriptions
- Count complete categories
└────────────────────┬────────────────────────────────────────┘
▼
▼
┌─────────────────────────────────────────────────────────────┐
4Prepare Template Data
- Assign counts to Smarty variables
- Set template file path
└────────────────────┬────────────────────────────────────────┘
▼
▼
┌─────────────────────────────────────────────────────────────┐
5Display Report
- Render cat.html template
- Show statistics dashboard
└─────────────────────────────────────────────────────────────┘

---

Workflow 2: Product Statistics Generation

┌─────────────────────────────────────────────────────────────┐
START: Access Product Statistics
└────────────────────┬────────────────────────────────────────┘
▼
▼
┌─────────────────────────────────────────────────────────────┐
1Authentication Check
- Verify user permissions
- Include authentication.php
└────────────────────┬────────────────────────────────────────┘
▼
▼
┌─────────────────────────────────────────────────────────────┐
2Load Tutorial Content
- Query youtubelink table
- Assign tutorial links to template
└────────────────────┬────────────────────────────────────────┘
▼
▼
┌─────────────────────────────────────────────────────────────┐
3Execute Statistical Queries
- Count total active products
- Count products with images
- Count products with descriptions
- Count complete products
└────────────────────┬────────────────────────────────────────┘
▼
▼
┌─────────────────────────────────────────────────────────────┐
4Prepare Template Data
- Assign counts to Smarty variables
- Set template file path
└────────────────────┬────────────────────────────────────────┘
▼
▼
┌─────────────────────────────────────────────────────────────┐
5Display Report
- Render pro.html template
- Show statistics dashboard
└─────────────────────────────────────────────────────────────┘

---

🌐 URL Routes & Actions

URL ParameterFunction CalledDescription
`do=cat`Category statisticsDisplay category data quality metrics
`do=pro`Product statisticsDisplay product data quality metrics
`do=sucess`Success pageGeneric success message page
`do=error`Error pageGeneric error message page
### Required Parameters by Action

Category Statistics (do=cat):

Product Statistics (do=pro):

---

🧮 Calculation Methods

Data Quality Percentage Calculations 

// Calculate data completeness percentages (frontend calculation)
$imagePercentage = ($withImageCount / $notDelCount) * 100;
$descriptionPercentage = ($withDescription / $notDelCount) * 100;  
$completenessPercentage = ($withImageAndDescription / $notDelCount) * 100;

Image Validation Logic 

-- Images considered valid when NOT equal to these values:
-- "." (placeholder)
-- "" (empty string)
-- "no image" (default text)
logo != "." and logo != "" and logo != "no image"

Description Validation Logic 

-- Descriptions considered valid when not empty
productDescription != ""
-- OR for categories:
productCatDescription != ""

---

🔒 Security & Permissions

Authentication Requirements 

// Authentication check enforced for all statistical views
include_once("../public/authentication.php");

Permission Levels:

Input Sanitization

---

📊 Performance Considerations

Database Optimization Tips

1. Indexes Required:

- productcat(conditions) - For filtering active categories

- product(conditions) - For filtering active products

- Consider composite indexes for logo validation

2. Query Performance:

- All queries use simple WHERE clauses with single table access

- COUNT(*) operations should be fast with proper indexes

- No JOINs required for statistical calculations

3. Caching Opportunities:

- Statistics could be cached since they change infrequently

- Consider implementing Redis/Memcache for count results

- Daily/hourly cache refresh would be sufficient

Known Performance Considerations 

-- These queries may be slow on large datasets without proper indexes:
SELECT COUNT(*) FROM product WHERE conditions = 0;
SELECT COUNT(*) FROM productcat WHERE conditions = 0;

-- Optimize with indexes:
CREATE INDEX idx_product_conditions ON product(conditions);
CREATE INDEX idx_productcat_conditions ON productcat(conditions);

---

🐛 Common Issues & Troubleshooting

1. Zero Count Results

Issue: All statistics show zero

Cause: Data marked as deleted (conditions != 0)

Debug: 

-- Check total records regardless of conditions
SELECT COUNT(*) FROM product;
SELECT COUNT(*) FROM productcat;

-- Check conditions distribution
SELECT conditions, COUNT(*) FROM product GROUP BY conditions;
SELECT conditions, COUNT(*) FROM productcat GROUP BY conditions;

2. Missing Tutorial Links

Issue: YouTube links not displaying

Cause: Empty youtubelink table or query failure

Fix: 

-- Check tutorial data
SELECT * FROM youtubelink;

-- Add sample tutorial if needed
INSERT INTO youtubelink (title, url) VALUES 
('Product Statistics Help', 'https://youtube.com/watch?v=example');

3. Template Display Issues

Issue: Statistics not rendering properly

Cause: Smarty template variables not assigned correctly

Debug: 

// Add debug output before template display
echo "Debug - notDelCount: " . $smarty->getTemplateVars('notDelCount') . "<br>";
echo "Debug - withImageCount: " . $smarty->getTemplateVars('withImageCount') . "<br>";

4. Authentication Failures

Issue: Access denied to statistics

Cause: Session issues or authentication problems

Fix:

---

🧪 Testing Scenarios

Test Case 1: Basic Statistics Accuracy 

1. Create test categories and products with known data
2. Add some with images, some without
3. Add some with descriptions, some without  
4. Run statistics reports
5. Verify counts match manually created test data

Test Case 2: Data Quality Metrics 

1. Create 10 test products:
   - 5 with images only
   - 3 with descriptions only  
   - 2 with both image and description
2. Run product statistics
3. Verify counts: total=10, withImage=7, withDescription=5, complete=2

Test Case 3: Deleted Records Exclusion 

1. Create test records with conditions = 0 (active)
2. Create test records with conditions = 1 (deleted)
3. Run statistics
4. Verify only active records (conditions = 0) are counted

Debug Mode Enable 

// Add at top of controller for debugging
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Debug query results
$result = $productEX->runSelectQuery('select count(*) as productId from product where conditions = 0');
echo "Query result: ";
print_r($result);

---

📚 Related Documentation

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur