# Net Store Transfer Controller Documentation

**File**: `/controllers/netStoreTransfer.php`  
**Purpose**: Provides comprehensive store transfer analysis and movement reporting across warehouses  
**Last Updated**: December 20, 2024  
**Total Functions**: 15  
**Lines of Code**: ~636

---

## 📋 Overview

The Net Store Transfer Controller is a sophisticated reporting tool that analyzes product movements between warehouses and provides detailed transfer analytics. It manages:
- Comprehensive store movement analysis
- Product transfer tracking between warehouses
- Inventory balance calculations across stores
- Sales and purchase data integration
- Transfer quantity reconciliation
- Multi-store movement reporting
- Category-based filtering and analysis
- Seller performance tracking

### Primary Functions
- [x] Store-to-store transfer analysis
- [x] Product movement tracking
- [x] Inventory balance reconciliation  
- [x] Sales/purchase integration
- [x] Category-based filtering
- [x] Date range analysis
- [x] User-specific reporting
- [x] Transfer deficit calculation

### Related Controllers
- [storemovementController.php](#) - Store movement operations
- [storereportController.php](#) - Store inventory reports
- [sellbillController.php](#) - Sales operations
- [buyBillController.php](#) - Purchase operations

---

## 🗄️ Database Tables

### Primary Tables (Analysis Data)
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **storedetail** | Store inventory details | storedetailid, storeid, productid, storedetailquantity |
| **storemovement** | Store transfer records | storemovementid, storeidfrom, storeidto, productid, transferproductamount, transferproductdate |
| **storemovement_main** | New transfer headers | id, sysdate |
| **storemovement_details** | New transfer details | id, storemovementid, productid, amount |

### Sales/Purchase Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **sellbill** | Sales bills | sellbillid, sellbillstoreid, sellbilldate, sellbillclientid |
| **sellbilldetail** | Sales details | sellbilldetailid, sellbillid, sellbilldetailproductid, sellbilldetailquantity |
| **buybill** | Purchase bills | buybillid, buybillstoreid, buybilldate |
| **buybilldetail** | Purchase details | buybilldetailid, buybillid, buybilldetailproductid, buybilldetailquantity |
| **returnsellbill** | Sales returns | returnsellbillid, returnsellbillstoreid, returnsellbilldate |
| **returnbuybill** | Purchase returns | returnbuybillid, returnbuybillstoreid, returnbuybilldate |

### Reference Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **store** | Warehouse information | storeId, storeName, conditions |
| **product** | Product catalog | productId, productName, productCatId |
| **productcat** | Product categories | productCatId, productCatName, productCatParent |
| **productunit** | Product units | productunitid, productid, unitid, productnumber |
| **user** | System users | userid, employeename |

---

## 🔑 Key Functions

### 1. **Default Action (show)** - Transfer Analysis Interface
**Location**: Lines 185-303  
**Purpose**: Main interface for store transfer analysis with comprehensive filtering

**Process Flow**:
1. Load category hierarchy for filtering
2. Set up store access based on user permissions
3. Process filter parameters (store, date range, category, product)
4. Execute transfer analysis for selected criteria
5. Display results with detailed movement data

**Permission-Based Store Access**:
```php
$smarty->assign("searchinonestore", $_SESSION['searchinonestore']);
if ($_SESSION['searchinonestore'] == 0) {
    if ($_SESSION['storeids'] == 0) {
        $stores = $myStoreEx->queryByConditions();
    } else {
        $stores = $myStoreEx->queryByConditions(' and store.storeId in (' . $_SESSION['storeids'] . ')');
    }
} else {
    $storedef = $myStoreEx->queryByConditionsOne(' and store.storeId = ' . $_SESSION['storeid'] . ' ');
}
```

**Category Filtering Logic**:
```php
if (isset($productCatId) && !empty($productCatId) && $productCatId != -1) {
    $productCat = $productCatDAO->load($productCatId);
    $message .= "التصنيف : " . $productCat->productCatName . "</br>";
    
    // Get all subcategories
    $catsIDS = '' . $productCatId;
    getAllSubCat($productCatId, 1); // Get all sub cats
    $productsOfCat = $ProductEX->queryByProductCatIdIn($catsIDS);
    
    // Convert to product ID list
    $productId = '0';
    foreach ($productsOfCat as $value) {
        $productId .= ',' . $value->productId;
    }
}
```

---

### 2. **getData()** - Core Transfer Analysis Function
**Location**: Lines 311-331  
**Purpose**: Main data aggregation function for store transfer analysis

**Function Signature**:
```php
function getData($storeId, $startDate, $endDate, $productId)
```

**Process Flow**:
1. Query store details for specified store and products
2. For each product, calculate:
   - Initial quantity (product add quantity)
   - First duration quantity (opening balance)
   - Transfer FROM quantities  
   - Transfer TO quantities
   - Sales data (sold, returned, seller names)
   - Purchase data (bought, returned)
   - Store deficits

**Data Enrichment**:
```php
foreach ($allDataArr as $storeDetail) {
    $storeDetail->productAddQuantity = getProductAddQuantity($storeDetail->productid, $storeId);
    $storeDetail->firstDurationQuantity = getFirstDurationQuantity($storeDetail->productid, $startDate, $storeId);
    $storeDetail->transferFrom = getTransferFromQuantity($storeId, $storeDetail->productid, $startDate, $endDate);
    $storeDetail->transferTo = getTransferToQuantity($storeId, $storeDetail->productid, $startDate, $endDate);
    list($storeDetail->sell, $storeDetail->retSell, $storeDetail->sellsersName) = getSellData($storeId, $storeDetail->productid, $startDate, $endDate);
    list($storeDetail->buy, $storeDetail->retBuy) = getBuyData($storeId, $storeDetail->productid, $startDate, $endDate);
    $storeDetail->storeDeficit = getStoreDeficit($storeId, $storeDetail->productid, $startDate, $endDate);
}
```

---

### 3. **getTransferFromQuantity()** - Outbound Transfer Calculation
**Location**: Lines 534-551  
**Purpose**: Calculate quantities transferred FROM a specific store

**Function Signature**:
```php
function getTransferFromQuantity($storeId, $productid, $startDate, $endDate)
```

**Process Flow**:
1. Query old transfer system (`storemovement` table)
2. Query new transfer system (`storemovement_main` + `storemovement_details`)
3. Sum all outbound transfer quantities
4. Return total transferred FROM store

**SQL Queries**:
```php
// Old system query
$storeMovementData = $storeMovementEX->queryByStoreidfromAndProductAndDate($storeId, $productid, $startDate, $endDate);

// New system query
$storeMovementDataNew = R::getAll('SELECT * FROM storemovement_main 
    JOIN storemovement_details ON storemovement_details.storemovementid = storemovement_main.id
    WHERE storeidfrom = ' . $storeId . ' 
      AND productid = ' . $productid . ' 
      AND sysdate >= "' . $startDate . '" 
      AND sysdate <= "' . $endDate . '"');
```

---

### 4. **getTransferToQuantity()** - Inbound Transfer Calculation  
**Location**: Lines 553-570  
**Purpose**: Calculate quantities transferred TO a specific store

**Function Signature**:
```php
function getTransferToQuantity($storeId, $productid, $startDate, $endDate)
```

**Process Flow**:
1. Query transfers TO the specified store
2. Handle both old and new transfer systems
3. Sum all inbound quantities
4. Return total received by store

**Dual System Support**:
```php
// Old transfer system
$storeMovementData = $storeMovementEX->queryByStoreidtoAndProductAndDate($storeId, $productid, $startDate, $endDate);

// New transfer system  
$storeMovementDataNew = R::getAll('SELECT * FROM storemovement_main 
    JOIN storemovement_details ON storemovement_details.storemovementid = storemovement_main.id
    WHERE storeidto = ' . $storeId . ' 
      AND productid = ' . $productid . ' 
      AND transferproductdate >= "' . $startDate . '" 
      AND transferproductdate <= "' . $endDate . '"');
```

---

### 5. **getSellData()** - Sales Analysis Function
**Location**: Lines 429-532  
**Purpose**: Comprehensive sales data analysis with seller tracking

**Function Signature**:
```php
function getSellData($storeId, $productId, $datefrom, $dateto)
```

**Process Flow**:
1. Build queries for all sales tables (sellbill, returnsellbill, sellbillandrutern)
2. Apply date, store, and product filters
3. Process unit conversions for accurate quantities
4. Track sales by individual sellers
5. Return sales, returns, and seller performance data

**Unit Conversion Logic**:
```php
foreach ($sellBillData as $value) {
    $quantity = $value->sellbilldetailquantity;
    $productId = $value->sellbilldetailproductid;
    $productunitId = $value->productunitid;
    $productunitData = loadProductUnitWithProductAndUnit($productId, $productunitId);
    $productnumber = $productunitData->productnumber;
    $finalquantity = $quantity * $productnumber; // Convert to base unit
    $soldQuantity += $finalquantity;
}
```

**Seller Performance Tracking**:
```php
$sellerSales = array();
foreach ($sellBillData as $value) {
    if (!isset($sellerSales[$value->userid])) {
        $sellerSales[$value->userid] = 0;
    }
    $sellerSales[$value->userid] += $finalquantity;
}

// Convert to display format
$sellsersName = '';
foreach ($sellerSales as $key => $value) {
    $userData = $userDAO->load($key);
    $sellsersName .= " $userData->employeename : $value <br/>";
}
```

---

### 6. **getBuyData()** - Purchase Analysis Function
**Location**: Lines 348-427  
**Purpose**: Comprehensive purchase data analysis

**Function Signature**:
```php
function getBuyData($storeId, $productId, $datefrom, $dateto)
```

**Process Flow**:
1. Query all purchase tables (buybill, returnbuybill, buyandruternbill)
2. Apply comprehensive filtering (date, store, product)
3. Handle unit conversions for accurate quantities
4. Process both purchases and returns
5. Return net purchase quantities

**Purchase Processing**:
```php
foreach ($buyBillData as $value) {
    $quantity = $value->buybilldetailquantity;
    $productId = $value->buybilldetailproductid;
    $productunitId = $value->productunitid;
    $productunitData = loadProductUnitWithProductAndUnit($productId, $productunitId);
    $productnumber = $productunitData->productnumber;
    $finalquantity = $quantity * $productnumber;
    $buyQuantity += $finalquantity;
}
```

---

### 7. **getStoreDeficit()** - Store Deficit Calculation
**Location**: Lines 333-346  
**Purpose**: Calculate store-specific deficits and adjustments

**Function Signature**:
```php
function getStoreDeficit($storeId, $productid, $startDate, $endDate)
```

**Process Flow**:
1. Query store report data for deficit entries
2. Process deficit types (positive/negative adjustments)
3. Calculate net deficit for the period
4. Return total store-specific adjustment

**Deficit Logic**:
```php
$netStoreDeficit = 0;
$storeReportData = $myStorereportEx->getStoreDeficit($productid, $storeId, $startDate, $endDate);
foreach ($storeReportData as $value) {
    if ($value->storereporttype == 0) {
        $netStoreDeficit += $value->productquantity; // Positive adjustment
    } else if ($value->storereporttype == 1) {
        $netStoreDeficit -= $value->productquantity; // Negative adjustment
    }
}
```

---

### 8. **getFirstDurationQuantity()** - Opening Balance Calculation
**Location**: Lines 571-581  
**Purpose**: Calculate opening balance for products before period start

**Function Signature**:
```php
function getFirstDurationQuantity($productId, $startDate, $storeId)
```

**Process Flow**:
1. Query store reports before start date
2. Get last recorded balance
3. Return opening quantity for analysis period

---

### 9. **getProductAddQuantity()** - Product Addition Tracking
**Location**: Lines 583-595  
**Purpose**: Track quantities added directly via product controller

**Function Signature**:
```php
function getProductAddQuantity($productId, $storeId)
```

**Process Flow**:
1. Query store reports for product additions
2. Filter by product controller operations
3. Return total added quantities

---

### 10. **getAllSubCat()** - Category Hierarchy Processing
**Location**: Lines 597-636  
**Purpose**: Recursively process product category hierarchies

**Function Signature**:
```php
function getAllSubCat($catid, $mode)
```

**Parameters**:
- `$catid` - Parent category ID
- `$mode` - Processing mode (1=all subcats, 2=last level only)

**Process Flow**:
1. Query child categories for given parent
2. Recursively process subcategories
3. Build category ID string for product filtering
4. Support both flat and hierarchical category processing

**Recursive Logic**:
```php
$result = $productCatExt->queryByParentExt2($catid);
if (count($result) > 0) {
    foreach ($result as $data) {
        if ($mode == 1) {
            $catsIDS .= "," . $data->productCatId;
            getAllSubCat($data->productCatId, $mode); // Recursive call
        }
    }
}
```

---

## 🔄 Workflows

### Workflow 1: Comprehensive Store Transfer Analysis
```
┌─────────────────────────────────────────────────────────────┐
│            START: Store Transfer Analysis                  │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Set Analysis Parameters                                 │
│     - Select date range for analysis                       │
│     - Choose specific store or all user stores             │
│     - Optional: Filter by product category                 │
│     - Optional: Select specific product                    │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Load Store Inventory Data                               │
│     - Query storedetail table for base inventory           │
│     - Apply product and store filters                      │
│     - Get list of products to analyze                      │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Calculate Movement Components                           │
│     FOR EACH product in analysis:                          │
│       │                                                     │
│       ├─→ Opening Balance (first duration quantity)        │
│       ├─→ Product Additions (direct adds)                  │
│       ├─→ Transfers FROM store (outbound)                  │
│       ├─→ Transfers TO store (inbound)                     │
│       ├─→ Sales Quantities (with seller breakdown)         │
│       ├─→ Sales Returns                                    │
│       ├─→ Purchase Quantities                              │
│       ├─→ Purchase Returns                                 │
│       └─→ Store Deficits/Adjustments                       │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Generate Comprehensive Report                           │
│     - Net transfer calculations                             │
│     - Inventory reconciliation                             │
│     - Seller performance metrics                           │
│     - Store movement summary                               │
│     - Period-over-period analysis                          │
└─────────────────────────────────────────────────────────────┘
```

---

### Workflow 2: Transfer Reconciliation Process
```
┌─────────────────────────────────────────────────────────────┐
│                Transfer Reconciliation                     │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Opening Balance Verification                           │
│     - Get pre-period inventory levels                      │
│     - Verify against last period closing                   │
│     - Identify any discrepancies                           │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Movement Tracking                                       │
│     Inbound Movements:                                      │
│       ├─→ Transfers received from other stores             │
│       ├─→ New purchases from suppliers                     │
│       └─→ Positive inventory adjustments                   │
│     Outbound Movements:                                     │
│       ├─→ Transfers sent to other stores                   │
│       ├─→ Sales to customers                               │
│       ├─→ Returns to suppliers                             │
│       └─→ Negative inventory adjustments                   │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Reconciliation Calculation                             │
│     Expected Balance =                                      │
│       Opening Balance                                       │
│       + Transfers IN                                        │
│       + Purchases                                          │
│       + Positive Adjustments                               │
│       - Transfers OUT                                       │
│       - Sales                                              │
│       - Purchase Returns                                    │
│       - Negative Adjustments                               │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Variance Analysis                                       │
│     - Compare expected vs actual balances                  │
│     - Identify discrepancies and root causes               │
│     - Generate variance reports                            │
│     - Recommend corrective actions                         │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=show` or empty | Default action | Display transfer analysis interface |

### Filter Parameters (POST)
| Parameter | Type | Description |
|-----------|------|-------------|
| `storeId` | Integer | Store ID for analysis (-1 for all stores) |
| `from` | Date | Start date (YYYY-MM-DD) |
| `to` | Date | End date (YYYY-MM-DD) |
| `level` | Integer | Category hierarchy level |
| `productCatId[level]` | Integer | Category ID for filtering |
| `productId` | Integer | Specific product filter |
| `proIsOptic` | Integer | Product type filter |

---

## 🧮 Calculation Methods

### Transfer Balance Formula
```
Net Store Balance = 
  Opening Balance
  + Product Additions
  + Transfers IN
  + Purchases
  + Positive Adjustments
  - Transfers OUT
  - Sales
  - Purchase Returns
  - Negative Adjustments
```

### Unit Conversion Logic
```php
// Convert all quantities to base unit for consistency
$finalquantity = $quantity * $productnumber;
// Where $productnumber is the conversion factor
```

### Seller Performance Calculation
```php
// Track sales by individual sellers
if (!isset($sellerSales[$value->userid])) {
    $sellerSales[$value->userid] = 0;
}
$sellerSales[$value->userid] += $finalquantity;
```

---

## 🔒 Security & Permissions

### Store Access Control
```php
// User can only access assigned stores
if ($_SESSION['searchinonestore'] == 0) {
    // Multi-store access
    if ($_SESSION['storeids'] == 0) {
        $stores = $myStoreEx->queryByConditions(); // All stores
    } else {
        $stores = $myStoreEx->queryByConditions(' and store.storeId in (' . $_SESSION['storeids'] . ')');
    }
} else {
    // Single store access only
    $storeId = $_SESSION['storeid'];
}
```

### Data Filtering
- All queries filtered by user's assigned stores
- Date range validation required
- Product access based on store assignments

---

## 📊 Performance Considerations

### Database Optimization
1. **Critical Indexes Needed**:
   - `storedetail(storeid, productid)`
   - `storemovement(storeidfrom, productid, transferproductdate)`
   - `storemovement(storeidto, productid, transferproductdate)`
   - `sellbilldetail(sellbilldetailproductid)` with `sellbill(sellbillstoreid, sellbilldate)`
   - `buybilldetail(buybilldetailproductid)` with `buybill(buybillstoreid, buybilldate)`

2. **Query Performance**:
   - Complex JOINs across multiple tables
   - Date range filtering on large datasets
   - Unit conversion calculations for every record

### Memory Management
```php
// Large datasets require careful memory management
foreach ($allDataArr as $storeDetail) {
    // Process one item at a time
    // Avoid loading entire dataset into memory
}
```

### Caching Opportunities
- Product unit conversion factors
- Category hierarchy data
- User permissions and store assignments

---

## 🐛 Common Issues & Troubleshooting

### 1. **Slow Report Generation**
**Issue**: Reports take too long to generate for large stores  
**Cause**: Missing database indexes and complex calculations

**Fix**: Optimize with proper indexes:
```sql
CREATE INDEX idx_storedetail_analysis ON storedetail(storeid, productid);
CREATE INDEX idx_storemovement_from ON storemovement(storeidfrom, productid, transferproductdate);
CREATE INDEX idx_storemovement_to ON storemovement(storeidto, productid, transferproductdate);
```

### 2. **Unit Conversion Errors**
**Issue**: Incorrect quantities due to unit conversion problems  
**Cause**: Missing or incorrect productunit data

**Debug**:
```php
$productunitData = loadProductUnitWithProductAndUnit($productId, $productunitId);
if (!$productunitData) {
    error_log("Missing unit conversion for product: $productId, unit: $productunitId");
    $productnumber = 1; // Default fallback
}
```

### 3. **Category Filtering Issues**
**Issue**: Recursive category processing causing timeouts  
**Cause**: Deep category hierarchies or circular references

**Fix**: Add depth limiting:
```php
function getAllSubCat($catid, $mode, $depth = 0) {
    if ($depth > 10) { // Prevent infinite recursion
        return;
    }
    // Continue with processing
    getAllSubCat($data->productCatId, $mode, $depth + 1);
}
```

### 4. **Transfer System Compatibility**
**Issue**: Data discrepancy between old and new transfer systems  
**Cause**: Dual system queries not properly synchronized

**Fix**: Ensure consistent date filtering:
```php
// Use same date format for both systems
$startDate = date('Y-m-d 00:00:00', strtotime($startDate));
$endDate = date('Y-m-d 23:59:59', strtotime($endDate));
```

---

## 🧪 Testing Scenarios

### Test Case 1: Basic Transfer Analysis
```
1. Select single store and date range
2. Verify all movement components calculate correctly
3. Check transfer IN/OUT quantities
4. Confirm sales and purchase data accuracy
```

### Test Case 2: Multi-Store Analysis
```
1. Run analysis for multiple stores
2. Verify store isolation and permissions
3. Check cross-store transfer calculations
4. Confirm aggregated reporting accuracy
```

### Test Case 3: Category Filtering
```
1. Test single category filtering
2. Verify recursive subcategory inclusion
3. Check product list generation from categories
4. Test category hierarchy edge cases
```

### Test Case 4: Performance Testing
```
1. Test with large product datasets
2. Measure report generation time
3. Monitor memory usage during processing
4. Verify timeout handling for long operations
```

---

## 📚 Related Documentation

- [CLAUDE.md](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [Store Management Guide](#) - Warehouse setup and configuration
- [Transfer Operations Manual](#) - Store movement procedures
- [Inventory Reconciliation Guide](#) - Balance verification procedures

---

**Documented By**: AI Assistant  
**Review Status**: ✅ Complete  
**Next Review**: When performance optimization needed