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

• ✅ Store-to-store transfer analysis
• ✅ Product movement tracking
• ✅ Inventory balance reconciliation
• ✅ Sales/purchase integration
• ✅ Category-based filtering
• ✅ Date range analysis
• ✅ User-specific reporting
• ✅ 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)

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

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

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:

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:

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:

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:

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

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:

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

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:

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:

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

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:

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:

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:

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

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:

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:

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:

$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

---

Workflow 2: Transfer Reconciliation Process

---

🌐 URL Routes & Actions

🧮 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

// Convert all quantities to base unit for consistency
$finalquantity = $quantity * $productnumber;
// Where $productnumber is the conversion factor

Seller Performance Calculation

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

---

🔒 Security & Permissions

Store Access Control

// 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

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

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:

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

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:

// 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 - 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