# Inventory Controller Documentation

**File**: `/controllers/inventoryController.php`  
**Purpose**: Physical inventory management and stock reconciliation system  
**Last Updated**: December 20, 2024  
**Total Functions**: 15+  
**Lines of Code**: ~634

---

## 📋 Overview

The Inventory Controller handles physical inventory counting, stock reconciliation, and inventory adjustments. It provides tools for:
- Physical inventory counting by category, store, or product
- Inventory adjustments and reconciliation
- Automatic daily entries for inventory changes
- Support for size/color variations
- Store reporting and tracking
- Multi-store inventory management
- Real-time quantity updates

### Primary Functions
- [x] Physical inventory counting interface
- [x] Quantity adjustments and updates
- [x] Size/color variant inventory management
- [x] Daily entry generation for inventory changes
- [x] Store report generation
- [x] Category-based inventory filtering
- [x] Auto-save functionality
- [x] Inventory history tracking
- [x] Store quantity synchronization

### Related Controllers
- [inventorybybarcodeController.php](inventorybybarcodeController.md) - Barcode-based inventory
- [inventoryexpirationController.php](inventoryexpirationController.md) - Product expiration tracking
- [storedetailController.php](storedetailController.md) - Store quantity management
- [storereportController.php](storereportController.md) - Store reports

---

## 🗄️ Database Tables

### Primary Tables (Direct Operations)
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **storedetail** | Store inventory quantities | storedetailid, productid, storeid, productquantity, userid, storedetaildate |
| **storereport** | Inventory movement history | storereportid, productid, storeid, productbefore, productafter, productquantity, storereporttype, tablename, userid, storereportdate |
| **sizecolorstoredetail** | Size/color variant quantities | sizecolorstoredetailid, productid, storeid, sizeid, colorid, quantity, userid, sysdate |

### Product Tables (Referenced)
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **product** | Product master data | productId, productName, productCatId, productBuyPrice, meanbuyprice, lastbuyprice |
| **productcat** | Product categories | productCatId, productCatName, productCatParent |
| **store** | Store locations | storeId, storeName, conditions, treeId |

### Reference Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **unit** | Units of measure | unitId, unitName |
| **productunit** | Product unit conversions | productunitid, productid, unitid, productnumber |
| **programsettings** | System configuration | programsettingsid, dailyEntryCostprice, Inventoryevaluation |
| **youtubelink** | Tutorial links | youtubelinkid, title, url |
| **user** | System users | userid, username, storeid, storeids |

---

## 🔑 Key Functions

### 1. **show() / Default Action** - Inventory Entry Interface
**Location**: Line 139  
**Purpose**: Display inventory counting interface with category and store filters

**Process Flow**:
1. Load YouTube tutorial links
2. Load category hierarchy for filtering
3. Load available stores
4. Assign session variables for display
5. Display via `inventoryview/add.html` template

**Template Variables**:
- `catData` - Category hierarchy
- `storesData` - Available stores
- `programsettingsdata` - System settings
- `youtubes` - Tutorial links

---

### 2. **add()** - Process Inventory Adjustments
**Location**: Line 341  
**Purpose**: Process multiple inventory quantity adjustments and generate reports

**Function Flow**:
1. Loop through submitted inventory items (`$_POST['itr']`)
2. For each item:
   - Extract new quantity, product ID, store ID
   - Handle size/color variants if present
   - Update store quantities
   - Calculate quantity differences
   - Generate store reports
   - Create daily entries
3. Sync with online store if applicable

**Key Variables**:
- `$newQty` - Updated quantity
- `$oldQty` - Previous quantity
- `$productId` - Product identifier
- `$storeid` - Store location
- `$sizeId, $colorId` - Variant identifiers

**Quantity Change Detection**:
```php
if ($oldQty > $newQty) {
    $status = "بالنقص";  // Decrease
    $actualQty = $oldQty - $newQty;
    $type = 1;
} else if ($oldQty < $newQty) {
    $status = "بالزيادة";  // Increase
    $actualQty = $newQty - $oldQty;
    $type = 0;
} else {
    $status = "لم يتغير";  // No change
    $type = 0;
}
```

---

### 3. **doInventoryDailyEntry()** - Accounting Entry Generation
**Location**: Line 440  
**Purpose**: Generate accounting entries for inventory adjustments

**Function Signature**:
```php
function doInventoryDailyEntry($storeId, $productId, $quantity, $newQty, $type, $storeReport)
```

**Process Flow**:
1. Get store and product data
2. Determine cost price based on system settings
3. Calculate total cost impact
4. Create debit/credit entries:
   - **Increase** (type=0): Debit store account, Credit inventory variance
   - **Decrease** (type=1): Debit inventory variance, Credit store account
5. Insert daily entry with linking

**Cost Price Options**:
- `first` - Original buy price
- `last` - Last purchase price  
- `mean` - Average buy price
- `last_discount` - Last price with discount
- `mean_discount` - Average price with discount
- `tax` - Last price with tax
- `mean_tax` - Average price with tax

---

### 4. **autosave()** - Single Item Auto-Save
**Location**: Line 510  
**Purpose**: Save individual inventory adjustments via AJAX

**Similar to `add()` but processes single item**:
- Handles one product adjustment
- Immediate quantity update
- Real-time store report generation
- Used for progressive inventory entry

---

### 5. **invReport() Action** - Inventory Reports
**Location**: Line 202  
**Purpose**: Generate inventory adjustment reports with filtering

**Search Parameters**:
- `storeId` - Filter by store
- `productCatId` - Filter by category
- `product` - Filter by specific product
- `from/to` - Date range filtering

**Query Building**:
```php
$queryString = '';
if ($storeId > 0) {
    $queryString .= " and storereport.storeid=$storeId ";
}
if ($product > 0) {
    $queryString .= " and storereport.productid=$product ";
} else if ($productCatId > 0) {
    // Get all subcategories and products
    getAllSubCat($productCatId, 1);
    $productsOfCat = $ProductEX->queryByProductCatIdIn($catsIDS);
    $IDS = '0';
    foreach ($productsOfCat as $value) {
        $IDS .= ',' . $value->productId;
    }
    $queryString .= " and storereport.productid in ($IDS) ";
}
```

---

### 6. **getCategoryChilds()** - Category Hierarchy
**Location**: Line 284  
**Purpose**: Load category tree structure for filtering

**Returns**: Array containing parent object and children array

---

### 7. **getStores()** - Store List
**Location**: Line 334  
**Purpose**: Load active stores for selection

**Returns**: Array of store objects with `conditions = 0`

---

### 8. **getAllSubCat()** - Recursive Category Traversal
**Location**: Line 605  
**Purpose**: Recursively get all subcategories for filtering

**Parameters**:
- `$catid` - Starting category ID
- `$mode` - 1=all subcats, 2=last level only

---

## 🔄 Workflows

### Workflow 1: Physical Inventory Count Process
```
┌─────────────────────────────────────────────────────────────┐
│                START: Physical Inventory                   │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Setup Inventory Session                                 │
│     - Select store(s) to count                              │
│     - Choose category filter (optional)                     │
│     - Load current quantities                               │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Physical Count Entry                                    │
│     - Display products with current quantities             │
│     - Enter actual counted quantities                      │
│     - Support auto-save for real-time updates              │
│     - Handle size/color variants                           │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Process Adjustments                                     │
│     FOR EACH product with quantity change:                 │
│       │                                                     │
│       ├─→ Calculate difference (increase/decrease)          │
│       │                                                     │
│       ├─→ Update store quantities                          │
│       │   ├─ Regular products: storedetail table           │
│       │   └─ Size/color variants: sizecolorstoredetail     │
│       │                                                     │
│       ├─→ Generate store report entry                      │
│       │   ├─ Record before/after quantities                │
│       │   ├─ Set adjustment type and reason                │
│       │   └─ Link to inventory controller                  │
│       │                                                     │
│       └─→ Create accounting entry                          │
│           ├─ Calculate cost impact                         │
│           ├─ Generate debit/credit entries                 │
│           └─ Link to store report                          │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Finalize Inventory                                      │
│     - Sync quantities with online systems                  │
│     - Generate completion report                           │
│     - Update inventory completion status                   │
└─────────────────────────────────────────────────────────────┘
```

---

### Workflow 2: Size/Color Variant Handling
```
┌─────────────────────────────────────────────────────────────┐
│           Product with Size/Color Variants                 │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Parse Variant Information                               │
│     - Extract product ID, size ID, color ID                │
│     - Format: "hasSizeColor" + productId + "-" + sizeId    │
│                + "-" + colorId                              │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Update Variant Quantity                                 │
│     - Load sizecolorstoredetail record                     │
│     - Update specific size/color quantity                  │
│     - Sync online store data                               │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Update Parent Product Quantity                          │
│     - Recalculate total product quantity                   │
│     - Sum all size/color combinations                      │
│     - Update main storedetail record                       │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=` (empty) or `do=show` | Default action | Display inventory entry interface |
| `do=add` | `add()` | Process inventory adjustments |
| `do=autosave` | `autosave()` | Auto-save single item |
| `do=details` | Details display | Show specific inventory report |
| `do=invReport` | Report generation | Display inventory adjustment reports |

### Required Parameters

**Inventory Entry** (`do=add`):
- `itr` - Number of items being processed
- `newQty{N}` - New quantity for item N
- `productId{N}` - Product ID for item N
- `storeid{N}` - Store ID for item N
- `oldQty{N}` - Previous quantity for item N

**Auto-save** (`do=autosave`):
- Same as add but for single item

**Reports** (`do=invReport`):
- `storeId` - Store filter (optional)
- `productCatId{level}` - Category filter (optional)
- `product` - Product filter (optional)
- `from/to` - Date range (optional)

---

## 🧮 Calculation Methods

### Quantity Difference Calculation
```php
if ($oldQty > $newQty) {
    $actualQty = $oldQty - $newQty;  // Shortage
    $type = 1;  // Decrease
} else if ($oldQty < $newQty) {
    $actualQty = $newQty - $oldQty;  // Overage
    $type = 0;  // Increase
}
```

### Cost Impact Calculation
```php
$productData = R::getRow('select ' . $priceColName . ' as price from product where productId=' . $productId);
$productCost = $productData['price'] * $quantity;
```

### Size/Color Quantity Aggregation
```php
// Update parent product from sum of all variants
$storeDetailExt->updateQuantityWithSumChild(
    $storeDetailData->storedetailid, 
    $_SESSION['userid'], 
    date("Y-m-d"), 
    0, 
    $storeid, 
    $productid
);
```

---

## 🔒 Security & Permissions

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

### Session Variables Used
- `$_SESSION['userid']` - Current user ID for tracking
- `$_SESSION['storenegative']` - Display setting for negative quantities
- `$_SESSION["serialarray"]` - Serial number tracking

### Input Validation
- Numeric casting for quantities and IDs
- Product ID validation for size/color format
- Store and product existence validation

---

## 📊 Performance Considerations

### Database Optimization
1. **Batch Processing**: Processes multiple items in single transaction
2. **Indexed Queries**: Uses primary keys for quick lookups
3. **Minimal Queries**: Efficient update patterns

### Memory Management
- Processes items iteratively to avoid large memory usage
- Cleans up variables after processing

### Potential Bottlenecks
- Large inventory counts with many variants
- Daily entry generation for high-value items
- Category traversal for large hierarchies

---

## 🐛 Common Issues & Troubleshooting

### 1. **Size/Color Quantity Sync Issues**
**Issue**: Parent product quantity doesn't match variant totals  
**Cause**: Failed `updateQuantityWithSumChild()` call

**Debug**:
```sql
SELECT p.productName, 
       sd.productquantity as parent_qty,
       SUM(scsd.quantity) as variant_total
FROM product p
JOIN storedetail sd ON sd.productid = p.productId  
LEFT JOIN sizecolorstoredetail scsd ON scsd.productid = p.productId
WHERE p.productId = [ID]
GROUP BY p.productId;
```

### 2. **Daily Entry Creation Failures**
**Issue**: Accounting entries not created for inventory adjustments  
**Cause**: Missing store tree ID or invalid cost price setting

**Fix**:
```sql
-- Check store tree mapping
SELECT storeId, storeName, treeId FROM store WHERE storeId = [ID];

-- Verify cost price setting
SELECT dailyEntryCostprice FROM programsettings WHERE programsettingsid = 1;
```

### 3. **Negative Quantity Issues**
**Issue**: System allows negative quantities  
**Cause**: No validation on quantity entry

**Fix**: Add client-side and server-side validation for minimum quantities

### 4. **Category Filtering Not Working**
**Issue**: Category filter returns no products  
**Cause**: Recursive category traversal issues

**Debug**:
```sql
-- Check category hierarchy
SELECT productCatId, productCatName, productCatParent 
FROM productcat 
WHERE productCatId = [CATID] OR productCatParent = [CATID];
```

---

## 🧪 Testing Scenarios

### Test Case 1: Basic Inventory Adjustment
```
1. Navigate to inventory controller
2. Select a store and product
3. Enter new quantity different from current
4. Submit inventory adjustment
5. Verify quantity updated in storedetail table
6. Check store report entry created
7. Confirm daily entry generated
```

### Test Case 2: Size/Color Variant Adjustment
```
1. Select product with size/color variants
2. Adjust quantity for specific variant
3. Verify sizecolorstoredetail updated
4. Check parent product quantity recalculated
5. Confirm variant totals match parent
```

### Test Case 3: Category-Based Inventory
```
1. Select category filter
2. Verify all products in category displayed
3. Include subcategory products
4. Test recursive category inclusion
```

### Test Case 4: Auto-Save Functionality
```
1. Enter quantity for single product
2. Trigger auto-save (AJAX)
3. Verify immediate quantity update
4. Check real-time report generation
5. Confirm no page reload required
```

---

## 📚 Related Documentation

- [CLAUDE.md](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [inventorybybarcodeController.md](inventorybybarcodeController.md) - Barcode inventory
- [inventoryexpirationController.md](inventoryexpirationController.md) - Expiration tracking
- [storedetailController.md](storedetailController.md) - Store management
- [dailyentry.php](dailyentry.md) - Accounting integration

---

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