<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

App::uses('UtilityController', 'Controller');
App::uses('AppController', 'Controller');
App::uses('CakeTime', 'Utility');

/**
 * CakePHP DsrController
 * @author Aether2
 */
class OutletController extends AppController {
    public $uses = array();
    public $components = array('Paginator');
    
    public function beforeFilter()
    {
        parent::beforeFilter();
        $this->layout = "dashboard";
        $this->response->disableCache();

        $this->Auth->allow();

        if ($this->Auth->user()) {

            $storeId = $this->Session->read('Auth.Location.id');
            $storeIdsArr = [$storeId];

            $this->loadModel('Inventory_semifinished');
            $this->loadModel('Inventory_sku');

            $lowStockSemifinished = $this->Inventory_semifinished->getLowStockData($storeId);
            $lowStockSkus = $this->Inventory_sku->getLowStockSkus($storeIdsArr);

            $semifinishedSkuIds = [];
            if (!empty($lowStockSemifinished)) {
                $semifinishedSkuIds = array_map(function($row) {
                    return (int)$row['s']['id'];
                }, $lowStockSemifinished);
            }

            $skuIds = [];
            if (!empty($lowStockSkus)) {
                $skuIds = array_map(function($row) {
                    return (int)$row['l']['sku_id'];
                }, $lowStockSkus);
            }

            $limit = 4;

            $semifinishedMessage = $this->buildLowStockList($lowStockSemifinished, $limit, 'semifinished');
            $skuMessage = $this->buildLowStockList($lowStockSkus, $limit, 'sku');
            $controller = 'outlet';

            $this->set(compact('semifinishedMessage', 'skuMessage', 'semifinishedSkuIds', 'skuIds', 'storeId', 'controller'));
        }
    }
    
     
    private function buildLowStockList($data, $limit = 4, $type = 'semifinished')
    {
        if (empty($data)) {
            return '';
        }

        $count = count($data);
        $html  = "<ul style='margin:0; padding-left:15px;'>";

        for ($i = 0; $i < min($limit, $count); $i++) {
            $item = $data[$i];
            
            // Handle different data structures
            if ($type === 'semifinished') {
                $name = h($item['s']['name']);
                $requiredStock = (float)$item['s']['required_stock'];
                $availableStock = (float)$item['sl']['available_stock'];
            } else {
                $name = h($item['s']['name']);
                $requiredStock = (float)$item['s']['required_stock'];
                $availableStock = (float)$item['l']['last_balance'];
            }

            $html .= "
                <li>
                    <span style='color:red; font-weight:bold;'>{$name}</span>
                    <span> / Required: {$requiredStock}</span>
                    <span> / Available: {$availableStock}</span>
                </li>
            ";
        }

        $html .= "</ul>";

        if ($count > $limit) {
            $html .= "<div style='font-weight:bold; margin-top:4px;'>... more</div>";
        }

        return $html;
    }

    public function markAlertViewed() {
        $this->autoRender = false;
        $this->layout = false;
        if ($this->request->is('post')) {
            $type = $this->request->data('type');
            $skuIds = $this->request->data('sku_ids');
            $storeId = $this->request->data('store_id');

            if (empty($skuIds) || empty($storeId)) {
                echo json_encode(['success' => false, 'message' => 'Missing parameters']);
                return;
            }

            try {
                if ($type === 'semifinished') {
                    $this->loadModel('Inventory_semifinished');
                    $this->Inventory_semifinished->markAlertsAsViewed($storeId, $skuIds);
                } elseif ($type === 'sku') {
                    $this->loadModel('Inventory_sku');
                    $this->Inventory_sku->markAlertsAsViewed([$storeId], $skuIds);
                }

                echo json_encode(['success' => true, 'message' => 'Alerts marked as viewed']);
            } catch (Exception $e) {
                echo json_encode(['success' => false, 'message' => $e->getMessage()]);
            }
        } else {
            echo json_encode(['success' => false, 'message' => 'Invalid request']);
        }
    }
    
    public function logout() {
	    return $this->redirect($this->Auth->logout());
    }
    
    public function index() {
        $this->set('title', 'Outlet Login Page');
        $this->Flash->success(__('Login'));
    }

    public function login() {
        $this->set('title', 'Outlet Login Page');
    
        if ($this->Session->check('Auth.User')) {
            return $this->redirect(array('controller' => 'Outlet', 'action' => 'indent'));
        }
    
        if ($this->request->is('post')) {
            if ($this->Auth->login()) {
                $location = $this->Auth->user(); // fetched from `locations` table
                $this->Session->write('Auth.Location', $location); // Optional: store separately
                return $this->redirect($this->Auth->redirectUrl());
            }
            $this->Flash->error(__('Invalid outlet code or password, try again'));
        }
    }
    
    public function indent()
    {
        if (!$this->Session->check('Auth.Location.id')) {
            return $this->redirect(['controller' => 'Dashboard', 'action' => 'login']);
        }
    
        $this->layout = "users";
    
        // Default date range
        $startDate = date('m/d/Y', strtotime('-6 months'));
        $endDate = date('m/d/Y');
        
        if ($this->request->is(['post', 'put'])) 
        {
			$daterange= $this->request->data['daterange'];
            $dates = explode(" - ", $daterange);
            $start_date = trim($dates[0]);
            $startDate = date('m/d/Y', strtotime($start_date));
            $end_date = trim($dates[1]);
            $endDate = date('m/d/Y', strtotime($end_date));
            $startdate= date('Y-m-d', strtotime($start_date));
            $enddate= date('Y-m-d', strtotime($end_date));
        }else{
            $startdate= date('Y-m-d', strtotime($startDate));
            $enddate= date('Y-m-d', strtotime($endDate));
        }   
    
        // Auth info
        $locationId = $this->Session->read('Auth.Location.id');
        // Base condition
        $conditions = "created BETWEEN '$startdate 00:00:01' AND '$enddate 23:59:59'";
        if ($userRole !== 'admin') {
            $conditions .= " AND location_id = '$locationId'";
        }
    
        // Indents by status
        $submittedIndents = $this->Inventory_indent->query("
            SELECT * FROM inventory_indent 
            WHERE $conditions and status IN (0,1)
            ORDER BY id DESC
        ");
        $approvedIndents = $this->Inventory_indent->query("
            SELECT * FROM inventory_indent 
            WHERE $conditions AND status = 2 
            ORDER BY id DESC
        ");
        $processedIndents = $this->Inventory_indent->query("
            SELECT * FROM inventory_indent 
            WHERE $conditions AND status = 3 
            ORDER BY id DESC
        ");
        $yourStartDateValue=$startDate . ' - ' . $endDate;
        // print_r($yourStartDateValue);exit;
        $this->set('startdate', $yourStartDateValue);
                
        $newconditions='';
        if ($userRole !== 'admin') {
            $newconditions .= " location_id = '$locationId'";
        }
        $submitted_indents = $this->Inventory_indent->query("
            SELECT count(id) as total FROM inventory_indent 
            WHERE $newconditions and status IN (1)
        ");
        $submitted_indent=$submitted_indents[0][0]['total'];
        
        $approved_indents = $this->Inventory_indent->query("
            SELECT count(id) as total FROM inventory_indent 
            WHERE $newconditions AND status = 2 
        ");
        $approved_indent=$approved_indents[0][0]['total'];
        
        $processed_indents = $this->Inventory_indent->query("
            SELECT count(id) as total FROM inventory_indent 
            WHERE $newconditions AND status = 3 
        ");
        $processed_indent=$processed_indents[0][0]['total'];
        
        $stockedin_indents = $this->Inventory_indent->query("
            SELECT count(id) as total FROM inventory_indent 
            WHERE $newconditions AND status = 4 
        ");
        $stockedin_indent=$stockedin_indents[0][0]['total'];
        
        $this->set(compact('submittedIndents', 'approvedIndents', 'processedIndents','submitted_indent','approved_indent','processed_indent','stockedin_indent'));
    }
    /*
    public function create_indent()
    {
        if (!$this->Session->check('Auth.User.id')) {
            return $this->redirect(['controller' => 'dashboard', 'action' => 'login']);
        }
    
        $this->layout = "users";
    
        // Logged-in location
        $locations = $this->Session->read('Auth.Location.id');
        $this->set(compact('locations'));
    
        // All DC locations
        $optlocations = $this->Location->find('all', [
            'conditions' => ['status' => 1, 'type' => 'dc']
        ]);
        $this->set(compact('optlocations'));    
        // Use user's assigned location_id (not user id)
        $authUser = $this->Session->read('Auth.User');
        $location_id = $authUser['id']; 
    
        // --- SEMIFINISHED ITEMS ---
        // $this->Inventory_semifinished->virtualFields['actual_balance'] = 
        //     "(SELECT sl.actual_balance 
        //       FROM stock_ledger sl 
        //       WHERE sl.sku_id = Inventory_semifinished.id 
        //         AND sl.store_id = $location_id
        //         AND sl.type = 'OUTLET' 
        //         AND sl.item_type = 'semifinished' 
        //       ORDER BY sl.id DESC 
        //       LIMIT 1)";
    
        // $semifinished = $this->Inventory_semifinished->find('all', [
        //     'conditions' => ['Inventory_semifinished.status' => 1],
        //     'order' => ['Inventory_semifinished.name ASC'],
        //     'fields' => ['Inventory_semifinished.*', 'Inventory_semifinished.actual_balance']
        // ]);
    
        // --- SKU ITEMS ---
        // $skus = $this->Inventory_sku->find('all', [
        //     'conditions' => ['Inventory_sku.status' => 1],
        //     'order' => ['Inventory_sku.name ASC']
        // ]);

        $skus = $this->Inventory_sku->find('all', [
            'fields' => [
                'Inventory_sku.*',
                'StockLedger.actual_balance',
                'COALESCE(SUM(InventorySkuTax.tax_percentage),0) AS total_tax_percentage'
            ],
        
            'joins' => [
        
                // ✅ Stock ledger join (existing)
                [
                    'table' => 'stock_ledger',
                    'alias' => 'StockLedger',
                    'type' => 'LEFT',
                    'conditions' => [
                        'StockLedger.sku_id = Inventory_sku.id',
                        'StockLedger.store_id' => $location_id,
                        'StockLedger.type' => 'OUTLET',
                        'StockLedger.id = (
                            SELECT MAX(sl2.id)
                            FROM stock_ledger sl2
                            WHERE sl2.sku_id = Inventory_sku.id
                            AND sl2.store_id = ' . $location_id . '
                            AND sl2.type = "OUTLET"
                        )'
                    ]
                ],
        
                // ✅ NEW Tax join
                [
                    'table' => 'inventory_sku_tax',
                    'alias' => 'InventorySkuTax',
                    'type' => 'LEFT',
                    'conditions' => [
                        'InventorySkuTax.product_id = Inventory_sku.id',
                        'InventorySkuTax.status' => 1
                    ]
                ]
            ],
        
            'conditions' => [
                'Inventory_sku.status' => 1
            ],
        
            // ✅ IMPORTANT for SUM()
            'group' => ['Inventory_sku.id'],
        
            'order' => ['Inventory_sku.name ASC']
        ]);
    
        // Combine both sets
        $items = array_merge($skus);

        // echo "<pre/>";
        // print_r($items);exit;
    
        $this->set(compact('items'));
    }

    public function get_actualbal_items_by_location()
    {
        $this->autoRender = false;
        $this->response->type('json');

        $dc_location_id = $this->request->data('location_id');
        $outlet_location_id = $this->Session->read('Auth.Location.id');

        if (empty($dc_location_id) || empty($outlet_location_id)) {
            echo json_encode(['status' => 'error', 'message' => 'Invalid locations']);
            return;
        }

        
        $skus = $this->Inventory_sku->find('all', [
            'fields' => [
                'Inventory_sku.*',
                'OutletLedger.actual_balance AS outlet_balance',
                'DcLedger.actual_balance AS dc_balance'
            ],
            'joins' => [
                [
                    'table' => 'stock_ledger',
                    'alias' => 'OutletLedger',
                    'type' => 'LEFT',
                    'conditions' => [
                        'OutletLedger.sku_id = Inventory_sku.id',
                        'OutletLedger.store_id' => $outlet_location_id,
                        'OutletLedger.type' => 'OUTLET',
                        'OutletLedger.id = (
                            SELECT MAX(sl1.id)
                            FROM stock_ledger sl1
                            WHERE sl1.sku_id = Inventory_sku.id
                            AND sl1.store_id = ' . $outlet_location_id . '
                            AND sl1.type = "OUTLET"
                        )'
                    ]
                ],
                [
                    'table' => 'stock_ledger',
                    'alias' => 'DcLedger',
                    'type' => 'LEFT',
                    'conditions' => [
                        'DcLedger.sku_id = Inventory_sku.id',
                        'DcLedger.store_id' => $dc_location_id,
                        'DcLedger.type' => 'DC',
                        'DcLedger.id = (
                            SELECT MAX(sl2.id)
                            FROM stock_ledger sl2
                            WHERE sl2.sku_id = Inventory_sku.id
                            AND sl2.store_id = ' . $dc_location_id . '
                            AND sl2.type = "DC"
                        )'
                    ]
                ]
            ],
            'conditions' => ['Inventory_sku.status' => 1],
            'order' => ['Inventory_sku.name ASC']
        ]);

        $items = [];
        foreach ($skus as $s) {
            $sku = $s['Inventory_sku'];
            $outlet = $s['OutletLedger'];
            $dc = $s['DcLedger'];
            $items[] = [
                'id' => $sku['id'],
                'name' => $sku['name'],
                'cat_name' => $sku['cat_name'] ?? '-',
                'unit_name' => $sku['unit_name'] ?? '-',
                'price' => $sku['price'] ?? 0,
                'outlet_balance' => $outlet['outlet_balance'] ?? 'NA',
                'dc_balance' => $dc['dc_balance'] ?? 'NA',
                'unit' => $sku['unit'],
                'type' => 'SKU'
            ];
        }

        echo json_encode(['status' => 'success', 'items' => $items]);
    }
    */
    
    public function create_indent()
    {
        if (!$this->Session->check('Auth.User.id')) {
            return $this->redirect(['controller' => 'dashboard', 'action' => 'login']);
        }
    
        $this->layout = "users";
    
        // Logged-in location
        $locations = $this->Session->read('Auth.Location.id');
        $this->set(compact('locations'));
    
        // All DC locations
        $optlocations = $this->Location->find('all', [
            'conditions' => ['status' => 1, 'type' => 'dc']
        ]);
        $this->set(compact('optlocations'));    
        // Use user's assigned location_id (not user id)
        $authUser = $this->Session->read('Auth.User');
        $location_id = $authUser['id']; 
    
        // --- SEMIFINISHED ITEMS ---
        // $this->Inventory_semifinished->virtualFields['actual_balance'] = 
        //     "(SELECT sl.actual_balance 
        //       FROM stock_ledger sl 
        //       WHERE sl.sku_id = Inventory_semifinished.id 
        //         AND sl.store_id = $location_id
        //         AND sl.type = 'OUTLET' 
        //         AND sl.item_type = 'semifinished' 
        //       ORDER BY sl.id DESC 
        //       LIMIT 1)";
    
        // $semifinished = $this->Inventory_semifinished->find('all', [
        //     'conditions' => ['Inventory_semifinished.status' => 1],
        //     'order' => ['Inventory_semifinished.name ASC'],
        //     'fields' => ['Inventory_semifinished.*', 'Inventory_semifinished.actual_balance']
        // ]);
    
        // --- SKU ITEMS ---
        // $skus = $this->Inventory_sku->find('all', [
        //     'conditions' => ['Inventory_sku.status' => 1],
        //     'order' => ['Inventory_sku.name ASC']
        // ]);

        $skus = $this->Inventory_sku->find('all', [
            'fields' => [
                'Inventory_sku.*',
                'StockLedger.actual_balance',
                'COALESCE(SUM(InventorySkuTax.tax_percentage),0) AS total_tax_percentage'
            ],
        
            'joins' => [
        
                // ✅ Stock ledger join (existing)
                [
                    'table' => 'stock_ledger',
                    'alias' => 'StockLedger',
                    'type' => 'LEFT',
                    'conditions' => [
                        'StockLedger.sku_id = Inventory_sku.id',
                        'StockLedger.store_id' => $location_id,
                        'StockLedger.type' => 'OUTLET',
                        'StockLedger.id = (
                            SELECT MAX(sl2.id)
                            FROM stock_ledger sl2
                            WHERE sl2.sku_id = Inventory_sku.id
                            AND sl2.store_id = ' . $location_id . '
                            AND sl2.type = "OUTLET"
                        )'
                    ]
                ],
        
                // ✅ NEW Tax join
                [
                    'table' => 'inventory_sku_tax',
                    'alias' => 'InventorySkuTax',
                    'type' => 'LEFT',
                    'conditions' => [
                        'InventorySkuTax.product_id = Inventory_sku.id',
                        'InventorySkuTax.status' => 1
                    ]
                ]
            ],
        
            'conditions' => [
                'Inventory_sku.status' => 1
            ],
        
            // ✅ IMPORTANT for SUM()
            'group' => ['Inventory_sku.id'],
        
            'order' => ['Inventory_sku.name ASC']
        ]);

    
        // Combine both sets
        $items = array_merge($skus);

        // echo "<pre/>";
        // print_r($items);exit;
    
        $this->set(compact('items'));
    }

    public function get_actualbal_items_by_location()
    {
        $this->autoRender = false;
        $this->response->type('json');

        $dc_location_id = $this->request->data('location_id');
        $outlet_location_id = $this->Session->read('Auth.Location.id');

        if (empty($dc_location_id) || empty($outlet_location_id)) {
            echo json_encode(['status' => 'error', 'message' => 'Invalid locations']);
            return;
        }

        
        // $skus = $this->Inventory_sku->find('all', [
        //     'fields' => [
        //         'Inventory_sku.*',
        //         'OutletLedger.actual_balance AS outlet_balance',
        //         'DcLedger.actual_balance AS dc_balance'
        //     ],
        //     'joins' => [
        //         [
        //             'table' => 'stock_ledger',
        //             'alias' => 'OutletLedger',
        //             'type' => 'LEFT',
        //             'conditions' => [
        //                 'OutletLedger.sku_id = Inventory_sku.id',
        //                 'OutletLedger.store_id' => $outlet_location_id,
        //                 'OutletLedger.type' => 'OUTLET',
        //                 'OutletLedger.id = (
        //                     SELECT MAX(sl1.id)
        //                     FROM stock_ledger sl1
        //                     WHERE sl1.sku_id = Inventory_sku.id
        //                     AND sl1.store_id = ' . $outlet_location_id . '
        //                     AND sl1.type = "OUTLET"
        //                 )'
        //             ]
        //         ],
        //         [
        //             'table' => 'stock_ledger',
        //             'alias' => 'DcLedger',
        //             'type' => 'LEFT',
        //             'conditions' => [
        //                 'DcLedger.sku_id = Inventory_sku.id',
        //                 'DcLedger.store_id' => $dc_location_id,
        //                 'DcLedger.type' => 'DC',
        //                 'DcLedger.id = (
        //                     SELECT MAX(sl2.id)
        //                     FROM stock_ledger sl2
        //                     WHERE sl2.sku_id = Inventory_sku.id
        //                     AND sl2.store_id = ' . $dc_location_id . '
        //                     AND sl2.type = "DC"
        //                 )'
        //             ]
        //         ]
        //     ],
        //     'conditions' => ['Inventory_sku.status' => 1],
        //     'order' => ['Inventory_sku.name ASC']
        // ]);
        
        $skus = $this->Inventory_sku->find('all', [
            'fields' => [
                'Inventory_sku.*',
        
                // ledgers
                'OutletLedger.actual_balance AS outlet_balance',
                'DcLedger.actual_balance AS dc_balance',
        
                // ✅ ADD THIS (tax sum)
                 '(SELECT COALESCE(SUM(tax_percentage),0)
                  FROM inventory_sku_tax
                  WHERE product_id = Inventory_sku.id
                  AND status = 1) AS total_tax_percentage'
            ],
        
            'joins' => [
        
                // Outlet stock
                [
                    'table' => 'stock_ledger',
                    'alias' => 'OutletLedger',
                    'type' => 'LEFT',
                    'conditions' => [
                        'OutletLedger.sku_id = Inventory_sku.id',
                        'OutletLedger.store_id' => $outlet_location_id,
                        'OutletLedger.type' => 'OUTLET',
                        'OutletLedger.id = (
                            SELECT MAX(sl1.id)
                            FROM stock_ledger sl1
                            WHERE sl1.sku_id = Inventory_sku.id
                            AND sl1.store_id = ' . $outlet_location_id . '
                            AND sl1.type = "OUTLET"
                        )'
                    ]
                ],
        
                // DC stock
                [
                    'table' => 'stock_ledger',
                    'alias' => 'DcLedger',
                    'type' => 'LEFT',
                    'conditions' => [
                        'DcLedger.sku_id = Inventory_sku.id',
                        'DcLedger.store_id' => $dc_location_id,
                        'DcLedger.type' => 'DC',
                        'DcLedger.id = (
                            SELECT MAX(sl2.id)
                            FROM stock_ledger sl2
                            WHERE sl2.sku_id = Inventory_sku.id
                            AND sl2.store_id = ' . $dc_location_id . '
                            AND sl2.type = "DC"
                        )'
                    ]
                ]
            ],
        
            'conditions' => ['Inventory_sku.status' => 1],
            'order' => ['Inventory_sku.name ASC']
        ]);


        $items = [];
        foreach ($skus as $s) {
            $sku = $s['Inventory_sku'];
            $outlet = $s['OutletLedger'];
            $dc = $s['DcLedger'];
            $items[] = [
                'id' => $sku['id'],
                'name' => $sku['name'],
                'cat_name' => $sku['cat_name'] ?? '-',
                'unit_name' => $sku['unit_name'] ?? '-',
                'price' => $sku['price'] ?? 0,
                'outlet_balance' => $outlet['outlet_balance'] ?? 'NA',
                'dc_balance' => $dc['dc_balance'] ?? 'NA',
                'unit' => $sku['unit'],
                'type' => 'SKU',
                'tax' => $s[0]['total_tax_percentage'] ?? 0
            ];
        }

        echo json_encode(['status' => 'success', 'items' => $items]);
    }


    public function add_indent() 
    {
        $this->autoRender = false;
    
        if (!$this->Session->check('Auth.User.id')) {
            echo json_encode(['response_code' => 0, 'data' => 'User session expired.']);
            return;
        }
    
        $id = $this->Session->read('Auth.Location.id');
        $name = $this->Session->read('Auth.Location.name');
        
        if (isset($_POST['checkedData']) && isset($_POST['location_id'])) {
            $checkeddatas = json_decode($_POST['checkedData'], true);
            $location_id = (int)$_POST['location_id'];
            $expiry = $_POST['expiry'];
            $dclocation = $_POST['dclocation'];
            // Validate location
            $location = $this->Location->find('first', [
                'conditions' => ['Location.id' => $location_id],
                'fields' => ['id', 'name'],
                'recursive' => -1
            ]);
            
            if (empty($location)) {
                echo json_encode(['response_code' => 0, 'data' => 'Invalid location selected.']);
                return;
            }
            
            $dclocation = $this->Location->find('first', [
                'conditions' => ['Location.id' => $dclocation,'type' => 'dc'],
                'fields' => ['id', 'name'],
                'recursive' => -1
            ]);
            if (empty($dclocation)) {
                echo json_encode(['response_code' => 0, 'data' => 'Invalid Warehouse selected.']);
                return;
            }
    
            // Generate unique indent_id
            do {
                $txnid = rand(1, 9);
                for ($i = 0; $i < 10; $i++) {
                    $txnid .= rand(0, 9);
                }
    
                $existing = $this->Inventory_indent_items->find('first', [
                    'conditions' => ['indent_id' => $txnid],
                    'recursive' => -1
                ]);
            } while ($existing);
    
            $amount = 0;
            $itemsSaved = true;

            // echo "<pre/>";
            // print_r($checkeddatas);exit;
    
            foreach ($checkeddatas as $item) {
                $idParts = explode('_', $item['id']);
                $item_type = $idParts[0];   // "Semifinished" or "SKU"
                $skuid = $idParts[1];       // "4" or "133"
                $price = $item['price'];
                $quantity = $item['sku_quantity'];
                // $item_type = $item['item_type'];
                
                if($item_type=='Semifinished'){
                    
                    $product_taxes=$this->Inventory_semifinished_tax->query("select SUM(tax_percentage) as total_tax from inventory_semifinished_tax where product_id='$skuid' AND status = 1");
                    $total_tax=round($product_taxes['0']['0']['total_tax'],2);
                    
                    $tax_value = round(($price*$quantity) * ($total_tax / 100),2);
    
                    $skuamount=round(($price*$quantity) + $tax_value,2);
                    
                    $skuData = [
                        'indent_id' => $txnid,
                        'sku_id' => $skuid,
                        'sku_name' => $item['sku_name'],
                        'sku_brand' => $item['sku_brand'],
                        'sku_unit' => $item['unit_id'],
                        'sku_unit_name' => $item['unit_name'],
                        'sku_price' => $price,
                        'sku_quantity' => $quantity,
                        'sku_total_tax_percentage' => $total_tax,
                        'sku_total_tax_value' => $tax_value,
                        'sku_amount' => $skuamount,
                        'item_type' =>'semifinished'
                    ];
                
                    $this->Inventory_indent_items->create(); // ensure new row
                    if (!$this->Inventory_indent_items->save($skuData)) {
                        $itemsSaved = false;
                        break;
                    }
                    
                }else{
                    $product_taxes=$this->Inventory_sku_tax->query("select SUM(tax_percentage) as total_tax from inventory_sku_tax where product_id='$skuid' and status = 1");
                    $total_tax=round($product_taxes['0']['0']['total_tax'],2);
                    
                    $tax_value = round(($price*$quantity) * ($total_tax / 100),2);
    
                    $skuamount=round(($price*$quantity) + $tax_value,2);
                    
                    $skuData = [
                        'indent_id' => $txnid,
                        'sku_id' => $skuid,
                        'sku_name' => $item['sku_name'],
                        'sku_brand' => $item['sku_brand'],
                        'sku_unit' => $item['unit_id'],
                        'sku_unit_name' => $item['unit_name'],
                        'sku_price' => $price,
                        'sku_quantity' => $quantity,
                        'sku_total_tax_percentage' => $total_tax,
                        'sku_total_tax_value' => $tax_value,
                        'sku_amount' => $skuamount,
                        'item_type' =>'sku'
                    ];
        
                    $this->Inventory_indent_items->create(); // ensure new row
                    if (!$this->Inventory_indent_items->save($skuData)) {
                        $itemsSaved = false;
                        break;
                    }
                }
                
                $amount += ($skuamount);
            }
        
            if ($itemsSaved) {
                $indentData = [
                    'indent_id' => $txnid,
                    'created_by' => $id,
                    'created_by_name' => $name,
                    'total_amount' => $amount,
                    'location_id' => $location['Location']['id'],
                    'location_name' => $location['Location']['name'],
                    'warehouse_id' => $dclocation['Location']['id'],
                    'warehouse_name' => $dclocation['Location']['name'],
                    'expiry' => $expiry
                ];
    
                $this->Inventory_indent->create();
                
                if ($this->Inventory_indent->save($indentData)) {
                    echo json_encode(['response_code' => 1, 'data' => 'saved']);
                } else {
                    echo json_encode(['response_code' => 0, 'data' => 'Failed to save indent master data.']);
                }
            } else {
                echo json_encode(['response_code' => 0, 'data' => 'Failed to save one or more items.']);
            }
        } else {
            echo json_encode(['response_code' => 0, 'data' => 'Invalid request. Missing data.']);
        }
    }

    public function view_indent()
	{
	    if(!$this->Session->check('Auth.User.id')){
			$this->redirect(array('controller'=>'dashboard','action' => 'login'));		
		}
        
	    $this->layout = "users";
		$id=$_REQUEST['id'];
        $indents = $this->Inventory_indent->query("SELECT * FROM inventory_indent where indent_id=$id and status != 0");
        $this->set(compact('indents'));
        
		$items = $this->Inventory_indent_items->query("SELECT * FROM inventory_indent_items where indent_id=$id and status != 0");
        
        $this->set(compact('items'));
        
        
	}
	/*
	public function edit_indent()
    {
        if (!$this->Session->check('Auth.User.id')) {
            return $this->redirect(['controller' => 'dashboard', 'action' => 'login']);
        }
    
        $this->layout = "users";
    
        // --- GET INDENT ID ---
        $id = $_REQUEST['id'];
    
        // --- USER SESSION INFO ---
        $authUser = $this->Session->read('Auth.User');
        $user_location = $this->Session->read('Auth.Location.id');
    
        // --- FETCH EXISTING INDENT RECORD ---
        $indentData = $this->Inventory_indent->find('first', [
            'conditions' => ['indent_id' => $id,'status' => 1],
            'recursive' => -1
        ]);
    
        if (empty($indentData)) {
            $this->Flash->error('Invalid Indent ID.');
            return $this->redirect(['controller' => 'outlet', 'action' => 'indent']);
        }
    
        // --- FETCH SEMIFINISHED ITEMS + EXISTING QUANTITIES ---
        // $semifinishedQuery = "
        //     SELECT 
        //         sf.*, 
        //         -- previously saved indent quantity
        //         (
        //             SELECT iit.sku_quantity 
        //             FROM inventory_indent_items iit
        //             WHERE iit.indent_id = $id
        //               AND iit.sku_id = sf.id 
        //               AND iit.item_type = 'Semifinished' and iit.status=1
        //             LIMIT 1
        //         ) AS quantity,
        
        //         -- latest stock balance from ledger
        //         (
        //             SELECT sl.actual_balance 
        //             FROM stock_ledger sl 
        //             WHERE sl.sku_id = sf.id 
        //               AND sl.store_id = $user_location
        //               AND sl.type = 'OUTLET' 
        //               AND sl.item_type = 'Semifinished'
        //             ORDER BY sl.id DESC 
        //             LIMIT 1
        //         ) AS actual_balance
        
        //     FROM inventory_semifinished sf
        //     WHERE sf.status = 1
        //     ORDER BY sf.name ASC
        // ";
        // $semifinished = $this->Inventory_semifinished->query($semifinishedQuery);
    
        $selected_location_id = $indentData['Inventory_indent']['location_id']; //outlet
        $selected_warehouse_id = $indentData['Inventory_indent']['warehouse_id']; //dc
        // --- FETCH SKU ITEMS + EXISTING QUANTITIES ---
        // $skuQuery = "
        //     SELECT *,
        //         (
        //             SELECT sku_quantity 
        //             FROM inventory_indent_items 
        //             WHERE indent_id = '$id' 
        //             AND sku_id = inventory_skus.id 
        //             AND item_type = 'sku'  and status=1
        //         ) AS quantity
        //     FROM inventory_skus
        //     WHERE inventory_skus.status = 1
        //     ORDER BY inventory_skus.name ASC;
        // ";
        // $skus = $this->Inventory_sku->query($skuQuery);

        $skuQuery = "
            SELECT 
                inventory_skus.*, 
                (
                    SELECT sku_quantity 
                    FROM inventory_indent_items 
                    WHERE indent_id = '$id' 
                    AND sku_id = inventory_skus.id 
                    AND item_type = 'sku'  
                    AND status = 1
                    LIMIT 1
                ) AS quantity,
                (
                    SELECT sl1.actual_balance 
                    FROM stock_ledger sl1 
                    WHERE sl1.sku_id = inventory_skus.id 
                    AND sl1.store_id = '$selected_location_id'
                    AND sl1.type = 'Outlet'
                    ORDER BY sl1.id DESC 
                    LIMIT 1
                ) AS outlet_balance,
                (
                    SELECT sl2.actual_balance 
                    FROM stock_ledger sl2 
                    WHERE sl2.sku_id = inventory_skus.id 
                    AND sl2.store_id = '$selected_warehouse_id'
                    AND sl2.type = 'DC'
                    ORDER BY sl2.id DESC 
                    LIMIT 1
                ) AS dc_balance

            FROM inventory_skus 
            WHERE inventory_skus.status = 1
            ORDER BY inventory_skus.name ASC;";
        $skus = $this->Inventory_sku->query($skuQuery);

    
        // --- COMBINE BOTH ---
        $items = array_merge($skus);
    
        // --- FETCH DC LOCATIONS ---
        $optlocations = $this->Location->find('all', [
            'conditions' => ['status' => 1, 'type' => 'dc']
        ]);
    
        // --- SET VARIABLES FOR VIEW ---
        $selected_expiry = $indentData['Inventory_indent']['expiry'];
    
        $this->set(compact(
            'items',
            'id',
            'selected_location_id',
            'selected_warehouse_id',
            'selected_expiry',
            'optlocations'
        ));
    }
    */
    public function edit_indent()
    {
        if (!$this->Session->check('Auth.User.id')) {
            return $this->redirect(['controller' => 'dashboard', 'action' => 'login']);
        }
    
        $this->layout = "users";
    
        // --- GET INDENT ID ---
        $id = $_REQUEST['id'];
    
        // --- USER SESSION INFO ---
        $authUser = $this->Session->read('Auth.User');
        $user_location = $this->Session->read('Auth.Location.id');
    
        // --- FETCH EXISTING INDENT RECORD ---
        $indentData = $this->Inventory_indent->find('first', [
            'conditions' => ['indent_id' => $id,'status' => 1],
            'recursive' => -1
        ]);
    
        if (empty($indentData)) {
            $this->Flash->error('Invalid Indent ID.');
            return $this->redirect(['controller' => 'outlet', 'action' => 'indent']);
        }
    
        // --- FETCH SEMIFINISHED ITEMS + EXISTING QUANTITIES ---
        // $semifinishedQuery = "
        //     SELECT 
        //         sf.*, 
        //         -- previously saved indent quantity
        //         (
        //             SELECT iit.sku_quantity 
        //             FROM inventory_indent_items iit
        //             WHERE iit.indent_id = $id
        //               AND iit.sku_id = sf.id 
        //               AND iit.item_type = 'Semifinished' and iit.status=1
        //             LIMIT 1
        //         ) AS quantity,
        
        //         -- latest stock balance from ledger
        //         (
        //             SELECT sl.actual_balance 
        //             FROM stock_ledger sl 
        //             WHERE sl.sku_id = sf.id 
        //               AND sl.store_id = $user_location
        //               AND sl.type = 'OUTLET' 
        //               AND sl.item_type = 'Semifinished'
        //             ORDER BY sl.id DESC 
        //             LIMIT 1
        //         ) AS actual_balance
        
        //     FROM inventory_semifinished sf
        //     WHERE sf.status = 1
        //     ORDER BY sf.name ASC
        // ";
        // $semifinished = $this->Inventory_semifinished->query($semifinishedQuery);
    
        $selected_location_id = $indentData['Inventory_indent']['location_id']; //outlet
        $selected_warehouse_id = $indentData['Inventory_indent']['warehouse_id']; //dc
        // --- FETCH SKU ITEMS + EXISTING QUANTITIES ---
        // $skuQuery = "
        //     SELECT *,
        //         (
        //             SELECT sku_quantity 
        //             FROM inventory_indent_items 
        //             WHERE indent_id = '$id' 
        //             AND sku_id = inventory_skus.id 
        //             AND item_type = 'sku'  and status=1
        //         ) AS quantity
        //     FROM inventory_skus
        //     WHERE inventory_skus.status = 1
        //     ORDER BY inventory_skus.name ASC;
        // ";
        // $skus = $this->Inventory_sku->query($skuQuery);

        $skuQuery = "
            SELECT 
                inventory_skus.*, 
                (SELECT COALESCE(SUM(tax_percentage),0)
                  FROM inventory_sku_tax
                  WHERE product_id = inventory_skus.id
                  AND status = 1) AS total_tax_percentage,
                (
                    SELECT sku_quantity 
                    FROM inventory_indent_items 
                    WHERE indent_id = '$id' 
                    AND sku_id = inventory_skus.id 
                    AND item_type = 'sku'  
                    AND status = 1
                    LIMIT 1
                ) AS quantity,
                (
                    SELECT sl1.actual_balance 
                    FROM stock_ledger sl1 
                    WHERE sl1.sku_id = inventory_skus.id 
                    AND sl1.store_id = '$selected_location_id'
                    AND sl1.type = 'Outlet'
                    ORDER BY sl1.id DESC 
                    LIMIT 1
                ) AS outlet_balance,
                (
                    SELECT sl2.actual_balance 
                    FROM stock_ledger sl2 
                    WHERE sl2.sku_id = inventory_skus.id 
                    AND sl2.store_id = '$selected_warehouse_id'
                    AND sl2.type = 'DC'
                    ORDER BY sl2.id DESC 
                    LIMIT 1
                ) AS dc_balance

            FROM inventory_skus 
            WHERE inventory_skus.status = 1
            ORDER BY inventory_skus.name ASC";
        $skus = $this->Inventory_sku->query($skuQuery);

    
        // --- COMBINE BOTH ---
        $items = array_merge($skus);
    
        // --- FETCH DC LOCATIONS ---
        $optlocations = $this->Location->find('all', [
            'conditions' => ['status' => 1, 'type' => 'dc']
        ]);
    
        // --- SET VARIABLES FOR VIEW ---
        $selected_expiry = $indentData['Inventory_indent']['expiry'];
    
        $this->set(compact(
            'items',
            'id',
            'selected_location_id',
            'selected_warehouse_id',
            'selected_expiry',
            'optlocations'
        ));
    }

    public function update_indent() 
    {
        $this->autoRender = false;
    
        $id = $this->Session->read('Auth.Location.id');
        $name = $this->Session->read('Auth.Location.name');
    
        if (isset($_POST['checkedData'])) {
            $checkeddatas = json_decode($_POST['checkedData'], true);
            $indentid = $_POST['indent_id'];
            $expiry = $_POST['expiry'];
            $dclocation = $_POST['dclocation'];
            $location_id = isset($_POST['location_id']) ? $_POST['location_id'] : null;
            $location_name = '';
    
            // --- Fetch location name if not passed ---
            if (!empty($location_id)) {
                $loc = $this->Location->find('first', [
                    'conditions' => ['Location.id' => $location_id],
                    'fields' => ['Location.name'],
                    'recursive' => -1
                ]);
                $location_name = isset($loc['Location']['name']) ? $loc['Location']['name'] : '';
            }
    
            // --- Validate DC location ---
            $dclocation = $this->Location->find('first', [
                'conditions' => ['Location.id' => $dclocation, 'type' => 'dc','status'=>1],
                'fields' => ['id', 'name'],
                'recursive' => -1
            ]);
            if (empty($dclocation)) {
                echo json_encode(['response_code' => 0, 'data' => 'Invalid Warehouse selected.']);
                return;
            }
    
            // --- Mark existing indent and items inactive ---
            $this->Inventory_indent_items->query("UPDATE inventory_indent_items SET status=0, sync=0, local_sync=0 WHERE indent_id ='$indentid'");
            $this->Inventory_indent->query("UPDATE inventory_indent SET status=0, sync=0, local_sync=0 WHERE indent_id = '$indentid'");
    
            $amount = 0;
            $anySaved = false;
    
            // --- Loop through each checked item ---
            foreach ($checkeddatas as $item) {
                $skuid = $item['id'];
                $price = $item['price'];
                $sku_name = $item['sku_name'];
                $sku_brand = $item['sku_brand'];
                $unit_name = $item['unit_name'];
                $unit_id = $item['unit_id'];
                $quantity = $item['sku_quantity'];
                $item_type = strtolower(trim($item['item_type'])) === 'semifinished' ? 'Semifinished' : 'SKU';
                
                if (!empty($quantity)) {
    
                    // === Select tax table based on item_type ===
                    if ($item_type === 'Semifinished') {
                        $product_taxes = $this->Inventory_semifinished_tax->query(
                            "SELECT SUM(tax_percentage) AS total_tax 
                             FROM inventory_semifinished_tax 
                             WHERE product_id = '$skuid' AND status = 1"
                        );
                    } else {
                        $product_taxes = $this->Inventory_sku_tax->query(
                            "SELECT SUM(tax_percentage) AS total_tax 
                             FROM inventory_sku_tax 
                             WHERE product_id = '$skuid' AND status = 1"
                        );
                    }
                
                    $total_tax = round($product_taxes[0][0]['total_tax'] ?? 0, 2);
                    $tax_value = round(($price * $quantity) * ($total_tax / 100), 2);
                    $skuamount = round(($price * $quantity) + $tax_value, 2);
    
                    // === Save item ===
                    $itemData = [
                        'Inventory_indent_items' => [
                            'indent_id' => $indentid,
                            'sku_id' => $skuid,
                            'sku_name' => $sku_name,
                            'sku_brand' => $sku_brand,
                            'sku_unit' => $unit_id,
                            'sku_unit_name' => $unit_name,
                            'sku_price' => $price,
                            'sku_quantity' => $quantity,
                            'sku_total_tax_percentage' => $total_tax,
                            'sku_total_tax_value' => $tax_value,
                            'sku_amount' => $skuamount,
                            'item_type' => $item_type
                        ]
                    ];
                    
                    if ($this->Inventory_indent_items->saveAll($itemData)) {
                        $anySaved = true;
                    }
                    $amount += $skuamount;
                }
            }
            
    
            // --- Save indent master ---
            if ($anySaved) {
                $indentData = [
                    'Inventory_indent' => [
                        'indent_id' => $indentid,
                        'expiry' => $expiry,
                        'created_by' => $id,
                        'created_by_name' => $name,
                        'location_id' => $location_id,
                        'location_name' => $location_name,
                        'total_amount' => $amount,
                        'warehouse_id' => $dclocation['Location']['id'],
                        'warehouse_name' => $dclocation['Location']['name'],
                        'is_updated' => 1
                    ]
                ];
    
                if ($this->Inventory_indent->save($indentData)) {
                    echo json_encode(['response_code' => 1, 'data' => 'Indent updated successfully.']);
                } else {
                    echo json_encode(['response_code' => 0, 'data' => 'Failed to update indent.']);
                }
            } else {
                echo json_encode(['response_code' => 0, 'data' => 'No items saved.']);
            }
        }
    }
    /*
    public function indent_change_status() 
    {
        $this->autoRender = false; 
    
        if ($this->request->is('post')) {
            $indentId = $this->request->data['_id']; 

            $currentStatusData = $this->Inventory_indent->find('first', [
                'conditions' => ['Inventory_indent.indent_id' => $indentId],
                'fields' => ['Inventory_indent.status','Inventory_indent.id'],
                'order' => ['Inventory_indent.id DESC'],
                'recursive' => -1
            ]);
            
            $ind_id=$currentStatusData['Inventory_indent']['id'];
            $newStatus = $currentStatusData['Inventory_indent']['status'] == 1 ? 0 : 1;
    
            $updated = $this->Inventory_indent->updateAll(
                [
                    'Inventory_indent.status' => $newStatus,
                    'Inventory_indent.sync' => 0,
                    'Inventory_indent.local_sync' => 0
                ],
                ['Inventory_indent.id' => $ind_id]
            );
    
            if ($updated) {
                echo json_encode(['status' => true]);
            } else {
                echo json_encode(['status' => false]);
            }
        }
    }
    */
    public function indent_change_status()
    {
        $this->autoRender = false;
        $this->response->type('json');
    
        if (!$this->request->is('post')) {
            echo json_encode([
                'status' => false,
                'message' => 'Invalid request method'
            ]);
            return;
        }
    
        $indentId = $this->request->data['_id'] ?? null;
    
        if (empty($indentId)) {
            echo json_encode([
                'status' => false,
                'message' => 'Indent ID missing'
            ]);
            return;
        }
    
        $currentStatusData = $this->Inventory_indent->find('first', [
            'conditions' => ['Inventory_indent.indent_id' => $indentId],
            'fields' => ['Inventory_indent.status', 'Inventory_indent.id'],
            'recursive' => -1
        ]);
    
        if (empty($currentStatusData)) {
            echo json_encode([
                'status' => false,
                'message' => 'Indent not found'
            ]);
            return;
        }
    
        $ind_id    = $currentStatusData['Inventory_indent']['id'];
        $oldStatus = (int)$currentStatusData['Inventory_indent']['status'];
        $newStatus = ($oldStatus === 1) ? 0 : 1;
    
        // ===== UPDATE INDENT =====
        $updatedIndent = $this->Inventory_indent->updateAll(
            [
                'Inventory_indent.status'      => $newStatus,
                'Inventory_indent.sync'        => 0,
                'Inventory_indent.local_sync'  => 0
            ],
            ['Inventory_indent.id' => $ind_id]
        );
    
        if (!$updatedIndent) {
            echo json_encode([
                'status' => false,
                'message' => 'Failed to update indent status'
            ]);
            return;
        }
    
        // ===== UPDATE INDENT ITEMS =====
        $updatedItems = $this->Inventory_indent_items->updateAll(
            [
                'Inventory_indent_items.status'     => $newStatus,
                'Inventory_indent_items.sync'       => 0,
                'Inventory_indent_items.local_sync' => 0
            ],
            ['Inventory_indent_items.indent_id' => $indentId]
        );
    
        if (!$updatedItems) {
            echo json_encode([
                'status' => false,
                'message' => 'Indent updated but items update failed'
            ]);
            return;
        }
    
        echo json_encode([
            'status' => true,
            'message' => 'Status updated successfully'
        ]);
    }
    /*
    public function grn_history()
    {
        if (!$this->Session->check('Auth.Location.id')) {
            return $this->redirect(['controller' => 'dashboard', 'action' => 'login']);
        }
    
        $this->layout = "users";
    
        $startDate = date('m/d/Y', strtotime('-1 month'));
        $endDate = date('m/d/Y');
    
        if ($this->request->is(['post', 'put'])) {
            $daterange = $this->request->data['daterange'];
            $dates = explode(" - ", $daterange);
            $start_date = trim($dates[0]);
            $end_date = trim($dates[1]);
    
            $startDate = date('m/d/Y', strtotime($start_date));
            $endDate = date('m/d/Y', strtotime($end_date));
    
            $startdate = date('Y-m-d', strtotime($start_date));
            $enddate = date('Y-m-d', strtotime($end_date));
        } else {
            $startdate = date('Y-m-d', strtotime($startDate));
            $enddate = date('Y-m-d', strtotime($endDate));
        }
    
        // Session values
        $locationId = $this->Session->read('Auth.Location.id');
        
        // Build condition based on role
        $conditions = "created BETWEEN '$startdate 00:00:01' AND '$enddate 23:59:59'";
        if ($userRole !== 'admin') {
            $conditions .= " AND location_id = '$locationId'";
        }
        $conditions .= " AND status = '4'";
        
        $users = $this->Inventory_indent->query("
            SELECT * FROM inventory_indent 
            WHERE  $conditions 
            ORDER BY id DESC
        ");
    
        $yourStartDateValue = $startDate . ' - ' . $endDate;
        $this->set('startdate', $yourStartDateValue);
        $this->set(compact('users'));
    }
    */
    public function grn_history()
    {
        if (!$this->Session->check('Auth.Location.id')) {
            return $this->redirect(['controller' => 'dashboard', 'action' => 'login']);
        }
    
        $this->layout = "users";
    
        $startDate = date('m/d/Y', strtotime('-1 month'));
        $endDate = date('m/d/Y');
    
        if ($this->request->is(['post', 'put'])) {
            $daterange = $this->request->data['daterange'];
            $dates = explode(" - ", $daterange);
            $start_date = trim($dates[0]);
            $end_date = trim($dates[1]);
    
            $startDate = date('m/d/Y', strtotime($start_date));
            $endDate = date('m/d/Y', strtotime($end_date));
    
            $startdate = date('Y-m-d', strtotime($start_date));
            $enddate = date('Y-m-d', strtotime($end_date));
        } else {
            $startdate = date('Y-m-d', strtotime($startDate));
            $enddate = date('Y-m-d', strtotime($endDate));
        }
    
        // Session values
        $locationId = $this->Session->read('Auth.Location.id');
        
        // Build condition based on role
        $conditions = "created BETWEEN '$startdate 00:00:01' AND '$enddate 23:59:59'";
        if ($userRole !== 'admin') {
            $conditions .= " AND location_id = '$locationId'";
        }
        $conditions .= " AND status = '4'";
        
        $users = $this->Inventory_indent->query("
            SELECT * FROM inventory_indent 
            WHERE  $conditions 
            ORDER BY id DESC
        ");
    
        $yourStartDateValue = $startDate . ' - ' . $endDate;
        $this->set('startdate', $yourStartDateValue);
        
        $newconditions='';
        if ($userRole !== 'admin') {
            $newconditions .= " location_id = '$locationId'";
        }
        $submitted_indents = $this->Inventory_indent->query("
            SELECT count(id) as total FROM inventory_indent 
            WHERE $newconditions and status IN (1)
        ");
        $submitted_indent=$submitted_indents[0][0]['total'];
        
        $approved_indents = $this->Inventory_indent->query("
            SELECT count(id) as total FROM inventory_indent 
            WHERE $newconditions AND status = 2 
        ");
        $approved_indent=$approved_indents[0][0]['total'];
        
        $processed_indents = $this->Inventory_indent->query("
            SELECT count(id) as total FROM inventory_indent 
            WHERE $newconditions AND status = 3 
        ");
        $processed_indent=$processed_indents[0][0]['total'];
        
        $stockedin_indents = $this->Inventory_indent->query("
            SELECT count(id) as total FROM inventory_indent 
            WHERE $newconditions AND status = 4 
        ");
        $stockedin_indent=$stockedin_indents[0][0]['total'];
        
        $this->set(compact('submitted_indent','approved_indent','processed_indent','stockedin_indent'));
     
        $this->set(compact('users'));
    }
    /*
    public function stockin()
    {
        if (!$this->Session->check('Auth.Location.id')) {
            return $this->redirect(['controller' => 'dashboard', 'action' => 'login']);
        }
    
        $authUser = $this->Session->read('Auth.Location');
        $userLocationId = $authUser['id'] ?? 0;
    
        $this->layout = "users";
        $id = $_POST['po'] ?? null;
        $this->set(compact('id'));
    
        if ($id) {
            // Fetch PO
            $poQuery = "SELECT * FROM inventory_indent WHERE consignment_id = '$id'";
            $indents = $this->Inventory_indent->query($poQuery);
    
            if (empty($indents)) {
                $this->set('po_error', 'Consignment Not Found'); // trigger JS alert
                return;
            }
    
            $po = $indents[0]['inventory_indent'];
            
            if ($po['status'] == 4) {
                $this->set('po_error', 'Consignment Already Stocked IN'); // trigger JS alert
                return;
            }
            
            if ($po['status'] != 3) {
                $this->set('po_error', 'Consignment is not Proccessed'); // trigger JS alert
                return;
            }
            
            if ($userRole != 'admin' && $po['location_id'] != $userLocationId) {
                $this->set('po_error', 'Location Not Found'); // trigger JS alert
                return;
            }
            // print_r($indents);exit;
            // All good, load items
            $items = $this->Inventory_indent_items->query(
                "SELECT * FROM inventory_indent_items WHERE consignment_id = '$id' AND status = 3"
            );
            $this->set(compact('indents', 'items'));
        }
    }
    
    public function stockin()
    {
        if (!$this->Session->check('Auth.Location.id')) {
            return $this->redirect(['controller' => 'dashboard', 'action' => 'login']);
        }
    
        $authUser = $this->Session->read('Auth.Location');
        $userLocationId = $authUser['id'] ?? 0;
    
        $this->layout = "users";
        $id = $_POST['po'] ?? null;
        $this->set(compact('id'));
        
        $newconditions='';
        if ($userRole !== 'admin') {
            $newconditions .= " location_id = '$userLocationId'";
        }
        $submitted_indents = $this->Inventory_indent->query("
            SELECT count(id) as total FROM inventory_indent 
            WHERE $newconditions and status IN (1)
        ");
        $submitted_indent=$submitted_indents[0][0]['total'];
        
        $approved_indents = $this->Inventory_indent->query("
            SELECT count(id) as total FROM inventory_indent 
            WHERE $newconditions AND status = 2 
        ");
        $approved_indent=$approved_indents[0][0]['total'];
        
        $processed_indents = $this->Inventory_indent->query("
            SELECT count(id) as total FROM inventory_indent 
            WHERE $newconditions AND status = 3 
        ");
        $processed_indent=$processed_indents[0][0]['total'];
        
        $stockedin_indents = $this->Inventory_indent->query("
            SELECT count(id) as total FROM inventory_indent 
            WHERE $newconditions AND status = 4 
        ");
        $stockedin_indent=$stockedin_indents[0][0]['total'];
        
        $this->set(compact('submitted_indent','approved_indent','processed_indent','stockedin_indent'));
        
        if ($id) {
            // Fetch PO
            if($_POST['data']['Search']['type']==0){
                $poQuery = "SELECT * FROM inventory_indent WHERE consignment_id = '$id'";
                $indents = $this->Inventory_indent->query($poQuery);
                    
                if (empty($indents)) {
                    $this->set('po_error', 'Consignment Not Found'); // trigger JS alert
                    return;
                }
                $po = $indents[0]['inventory_indent'];
                
                if ($po['status'] == 4) {
                    $this->set('po_error', 'Consignment Already Stocked IN'); // trigger JS alert
                    return;
                }
                
                if ($po['status'] != 3) {
                    $this->set('po_error', 'Consignment is not Proccessed'); // trigger JS alert
                    return;
                }
                
                if ($userRole != 'admin' && $po['location_id'] != $userLocationId) {
                    $this->set('po_error', 'Location Not Found'); // trigger JS alert
                    return;
                }
                
                $items = $this->Inventory_indent_items->query(
                    "SELECT * FROM inventory_indent_items WHERE consignment_id = '$id' AND status = 3"
                );
                
            }else{
                $poQuery = "SELECT * FROM inventory_purchase_order WHERE p_o_id = '$id' and location_id='$userLocationId'";
                $indents = $this->Inventory_purchase_order->query($poQuery);
                
                if (empty($indents)) {
                    $this->set('po_error', 'PO Not Found or not available for this location'); // trigger JS alert
                    return;
                }
                $po = $indents[0]['inventory_purchase_order'];
                
                if ($po['status'] == 4) {
                    $this->set('po_error', 'PO Already Stocked IN'); // trigger JS alert
                    return;
                }
                
                if ($po['status'] != 3) {
                    $this->set('po_error', 'PO is not Proccessed'); // trigger JS alert
                    return;
                }
                
                $items = $this->Inventory_indent_items->query(
                    "SELECT * FROM inventory_purchase_order_items WHERE p_o_id = '$id' AND status = 3"
                );
                
            }
    
            $this->set(compact('indents', 'items'));
        }
    }
    */
    
    public function stockin()
    {
        if (!$this->Session->check('Auth.Location.id')) {
            return $this->redirect(['controller' => 'dashboard', 'action' => 'login']);
        }
    
        $authUser = $this->Session->read('Auth.Location');
        $userLocationId = $authUser['id'] ?? 0;
    
        $this->layout = "users";
        $id = $_POST['po'] ?? null;
        $this->set(compact('id'));
        
        $newconditions='';
        if ($userRole !== 'admin') {
            $newconditions .= " location_id = '$userLocationId'";
        }
        $submitted_indents = $this->Inventory_indent->query("
            SELECT count(id) as total FROM inventory_indent 
            WHERE $newconditions and status IN (1)
        ");
        $submitted_indent=$submitted_indents[0][0]['total'];
        
        $approved_indents = $this->Inventory_indent->query("
            SELECT count(id) as total FROM inventory_indent 
            WHERE $newconditions AND status = 2 
        ");
        $approved_indent=$approved_indents[0][0]['total'];
        
        $processed_indents = $this->Inventory_indent->query("
            SELECT count(id) as total FROM inventory_indent 
            WHERE $newconditions AND status = 3 
        ");
        $processed_indent=$processed_indents[0][0]['total'];
        
        $stockedin_indents = $this->Inventory_indent->query("
            SELECT count(id) as total FROM inventory_indent 
            WHERE $newconditions AND status = 4 
        ");
        $stockedin_indent=$stockedin_indents[0][0]['total'];
        
        $this->set(compact('submitted_indent','approved_indent','processed_indent','stockedin_indent'));
        
        if ($id) {
            // Fetch PO
            if($_POST['data']['Search']['type']==0){
                $poQuery = "SELECT * FROM inventory_indent WHERE consignment_id = '$id'";
                $indents = $this->Inventory_indent->query($poQuery);
                    
                if (empty($indents)) {
                    $this->set('po_error', 'Consignment Not Found'); // trigger JS alert
                    return;
                }
                $po = $indents[0]['inventory_indent'];
                
                if ($po['status'] == 4) {
                    $this->set('po_error', 'Consignment Already Stocked IN'); // trigger JS alert
                    return;
                }
                
                if ($po['status'] != 3) {
                    $this->set('po_error', 'Consignment is not Proccessed'); // trigger JS alert
                    return;
                }
                
                if ($userRole != 'admin' && $po['location_id'] != $userLocationId) {
                    $this->set('po_error', 'Location Not Found'); // trigger JS alert
                    return;
                }
                
                $items = $this->Inventory_indent_items->query(
                    "SELECT * FROM inventory_indent_items WHERE consignment_id = '$id' AND status = 3"
                );
                
            }else{
                $poQuery = "SELECT * FROM inventory_purchase_order WHERE p_o_id = '$id' and location_id='$userLocationId'";
                $indents = $this->Inventory_purchase_order->query($poQuery);
                
                if (empty($indents)) {
                    $this->set('po_error', 'PO Not Found or not available for this location'); // trigger JS alert
                    return;
                }
                $po = $indents[0]['inventory_purchase_order'];
                
                if ($po['status'] == 4 && $po['is_stockedin'] == 1) {
                    $this->set('po_error', 'PO Already Stocked IN'); // trigger JS alert
                    return;
                }
                
                if ($po['status'] != 3 && $po['status'] != 4) {
                    $this->set('po_error', 'PO is not Proccessed'); // trigger JS alert
                    return;
                }
                
                $items = $this->Inventory_indent_items->query("SELECT * 
                    FROM inventory_purchase_order_items 
                    WHERE p_o_id = '$id' 
                    AND (
                            status = 3 
                         OR (status = 4 AND is_stockedin = 0)
                        
                    )"
                );
                    
            }
    
            $this->set(compact('indents', 'items'));
        }
    }

	public function invoice_id_check() 
    {
        $this->autoRender = false; // Disable view rendering
    
        if ($this->request->is('post')) {
            $poid = $this->request->data['_id'];
            $invoice = trim($this->request->data['_invoice']);
            
            $invoiceidchk = $this->Inventory_indent->find('first', [
                'conditions' => ['consignment_id' => $poid,'indent_id' => $invoice]
            ]);
            
            if(!empty($invoiceidchk)){
                echo json_encode(['status' => true]);
            }else{
                echo json_encode(['status' => false,'message' => 'Invalid Invoice No']);
            }
        }
    }
    /*
	public function stockin_po() 
    {
        $this->autoRender = false; // Disable view rendering
    
        if ($this->request->is('post')) {
            $poid = $this->request->data['_id'];
            $invoice = trim($this->request->data['_invoice']);
            $quantities = $this->request->data['_quantities']; // associative array: sku_id => quantity
            $item_types = $this->request->data['_item_type']; 

            $today = date('Y-m-d H:i:s');
            
            $invoiceidchk = $this->Inventory_indent->find('first', [
                'conditions' => ['consignment_id' => $poid,'indent_id' => $invoice]
            ]);
            
            $amount = 0;
            // Generate unique stockin_id
            do {
                $txnid = rand(1, 9);
                for ($i = 0; $i < 10; $i++) {
                    $txnid .= rand(0, 9);
                }
    
                $existing = $this->Inventory_indent->find('first', [
                    'conditions' => ['stockin_id' => $txnid]
                ]);
            } while ($existing);
    
            $location_id = $this->Session->read('Auth.Location.id');
            
            $stockin_skus = $this->Inventory_indent_items->find('all', [
                'conditions' => ['consignment_id' => $poid,'status' => 3]
            ]);
            // echo "<pre/>";
            // print_r($stockin_skus);exit;
            
            foreach ($stockin_skus as $stockin_sku) {
                $sku = $stockin_sku['Inventory_indent_items'];
                $sku_id = $sku['sku_id'];
                $approved_price = $sku['approved_price'];
                
                if (isset($quantities[$sku_id])) {
                    $qty = floatval($quantities[$sku_id]);
                    $item_type = isset($item_types[$sku_id]) ? $item_types[$sku_id] : 'sku';
                    
                    if ($item_type == 'semifinished') {
                        $product_taxes = $this->Inventory_semifinished_tax->query("
                            SELECT SUM(tax_percentage) AS total_tax 
                            FROM inventory_semifinished_tax 
                            WHERE product_id='$sku_id' AND status = 1
                        ");
                    } else {
                        $product_taxes = $this->Inventory_sku_tax->query("
                            SELECT SUM(tax_percentage) AS total_tax 
                            FROM inventory_sku_tax 
                            WHERE product_id='$sku_id' AND status = 1
                        ");
                    }
                    $total_tax = isset($product_taxes[0][0]['total_tax']) ? round($product_taxes[0][0]['total_tax'], 2) : 0;
                    $tax_value = round(($approved_price * $qty) * ($total_tax / 100), 2);
                    $skuamount = round(($approved_price * $qty) + $tax_value, 2);
                    
                    // Update purchase order item
                    $this->Inventory_indent_items->updateAll(
                        ['status' => 4, 'sync' => 0, 'local_sync' => 0, 'stockin_quantity' => $qty,'stockin_tax_value' => $tax_value,
                        'stockin_amount' => $skuamount, 'status'=> 4],
                        ['consignment_id' => $poid, 'sku_id' => $sku_id]
                    );
                    
                    $amount += ($skuamount);
                    
                    //stock ledger entry semifinished
                    if ($item_type == 'semifinished') {
                        
                        $ledgerprevdetails = $this->Stock_ledger->find('first', [
                            'conditions' => ['sku_id' => $sku_id,'type'=>'OUTLET','item_type'=>'semifinished','store_id'=>$location_id],
                            'fields' => ['actual_balance'],
                            'order' => ['id DESC'],
                            'recursive' => -1
                        ]);
                        
                        $ledger_prev_quantity = !empty($ledgerprevdetails['Stock_ledger']['actual_balance']) 
                            ? $ledgerprevdetails['Stock_ledger']['actual_balance'] 
                            : 0;
                        
                        $newQuantity = $qty + $ledger_prev_quantity;
                        
                        $ledgerData['Stock_ledger'] = [
                                'store_id'        => $location_id,
                                'sku_id'          => $sku_id,
                                'sku_name'        => $sku['sku_name'],
                                'sku_price'       => $sku['sku_price'],
                                'unit_id'         => $sku['sku_unit'],
                                'unit_name'       => $sku['sku_unit_name'],
                                'record_id'       => $txnid,
                                'record_type'     => 'stockin',
                                'quantity'        => $qty,
                                'balance_stock'   => $newQuantity,
                                'actual_balance'  => $newQuantity,
                                'quantity_type'   => 'credit',
                                'type'            => 'OUTLET',
                                'item_type'       => 'semifinished',
                                'created'         => date('Y-m-d H:i:s')
                        ];
        				if($this->Stock_ledger->saveAll($ledgerData['Stock_ledger'])){
        				
            				$ingredients = $this->Inventory_linking->find('all', [
                                'conditions' => ['parent_id' => $sku_id,'parent_type' => 'Semifinished','component_type' => 'Sku', 'status' => 1]
                            ]);
                            // print_r($ingredients);exit;
                            foreach ($ingredients as $ingredient) {
                                $linked_sku_id = $ingredient['Inventory_linking']['component_id'];
                                $sku_quantity= $ingredient['Inventory_linking']['quantity']*$qty;
                                    
                                $ledgerprevdetails = $this->Stock_ledger->find('first', [
                                    'conditions' => ['sku_id' => $linked_sku_id,'store_id' =>$location_id,'item_type'=>'SKU' ,'type' =>'OUTLET' ],
                                    'fields' => ['actual_balance'],
                                    'order' => ['id DESC'],
                                    'recursive' => -1
                                ]);
                                
                                $ledger_prev_quantity = !empty($ledgerprevdetails['Stock_ledger']['actual_balance']) 
                                    ? $ledgerprevdetails['Stock_ledger']['actual_balance'] 
                                    : 0;
                                
                                $newQuantity = $ledger_prev_quantity+$sku_quantity;
                                
                                $sku = $this->Inventory_sku->find('first', [
                                    'conditions' => ['Inventory_sku.id' => $sku_id],
                                    'recursive' => -1
                                ]);
                                
                                $ledgerData['Stock_ledger'] = [
                                        'store_id'        => $location_id,
                                        'sku_id'          => $ingredient['Inventory_linking']['component_id'],
                                        'sku_name'        => $ingredient['Inventory_linking']['component_name'],
                                        'sku_price'       => $sku['Inventory_sku']['price'],
                                        'unit_id'         => $ingredient['Inventory_linking']['component_unit'],
                                        'unit_name'       => $ingredient['Inventory_linking']['component_unit_name'],
                                        'record_id'       => $txnid,
                                        'record_type'     => 'stockin',
                                        'quantity'        => $sku_quantity,
                                        'balance_stock'   => $newQuantity,
                                        'actual_balance'  => $newQuantity,
                                        'quantity_type'   => 'credit',
                                        'item_type'       => 'SKU',
                                        'type'            => 'OUTLET',
                                        'created'         => date('Y-m-d H:i:s')
                                ];
                				if(!$this->Stock_ledger->saveAll($ledgerData['Stock_ledger'])){
                                    echo json_encode(['status' => false]);
                                }
                            }
        				}else{
        				    echo json_encode(['status' => false]);
        				}
                    }else{
                        
                        $ledgerprevdetails = $this->Stock_ledger->find('first', [
                            'conditions' => ['sku_id' => $sku_id,'type'=>'OUTLET','item_type'=>'SKU','store_id'=>$location_id],
                            'fields' => ['actual_balance'],
                            'order' => ['id DESC'],
                            'recursive' => -1
                        ]);
                        
                        $ledger_prev_quantity = !empty($ledgerprevdetails['Stock_ledger']['actual_balance']) 
                            ? $ledgerprevdetails['Stock_ledger']['actual_balance'] 
                            : 0;
                        
                        $newQuantity = $qty + $ledger_prev_quantity;
                        
                        $ledgerData['Stock_ledger'] = [
                                'store_id'        => $location_id,
                                'sku_id'          => $sku_id,
                                'sku_name'        => $sku['sku_name'],
                                'sku_price'       => $sku['sku_price'],
                                'unit_id'         => $sku['sku_unit'],
                                'unit_name'       => $sku['sku_unit_name'],
                                'record_id'       => $txnid,
                                'record_type'     => 'stockin',
                                'quantity'        => $qty,
                                'balance_stock'   => $newQuantity,
                                'actual_balance'  => $newQuantity,
                                'quantity_type'   => 'credit',
                                'type'            => 'OUTLET',
                                'item_type'       => 'SKU',
                                'created'         => date('Y-m-d H:i:s')
                        ];
        				if(!$this->Stock_ledger->saveAll($ledgerData['Stock_ledger'])){
        				    echo json_encode(['status' => false]);
        				}
                    }
                }
            }
    
            // Update purchase order
            $this->Inventory_indent->query("UPDATE inventory_indent SET 
                stockin_id = '$txnid',
                status = 4,
                stockin_date = '$today',
                invoice_no = '$invoice',
                stockin_amount = '$amount',
                sync = 0, 
                local_sync = 0 
                WHERE consignment_id = '$poid'");
    
            echo json_encode(['status' => true]);
        }
    }

    public function stockin_po() 
    {
        $this->autoRender = false; // Disable view rendering
    
        if ($this->request->is('post')) {
            $poid = $this->request->data['_id'];
            $invoice = trim($this->request->data['_invoice']);
            $quantities = $this->request->data['_quantities']; // associative array: sku_id => quantity
            $item_types = $this->request->data['_item_type']; 
            $charge = $this->request->data['_charge'] ?? 0; 
            $type = $this->request->data['_type']; 

            $today = date('Y-m-d H:i:s');
            
            if (empty($poid)) {
                echo json_encode(['status'=>false,'message'=>'Invalid PO/Consignment ID']);
                return;
            }
            
            if (empty($invoice)) {
                echo json_encode(['status'=>false,'message'=>'Invoice number required']);
                return;
            }
            
            $dataSource = $this->Inventory_indent->getDataSource();
            $dataSource->begin();
            
            try {
                if (empty($quantities) || !is_array($quantities)) {
                    throw new Exception('Invalid quantity data');
                }

                if($type=='consignment'){
                    $invoiceidchk = $this->Inventory_indent->find('first', [
                        'conditions' => ['consignment_id' => $poid,'indent_id' => $invoice]
                    ]);
                    
                    if(empty($invoiceidchk)){
                        throw new Exception('Something went wrong for this indent...!');
                    }
                    
                    $amount = 0;
                    // Generate unique stockin_id
                    do {
                        $txnid = rand(1, 9);
                        for ($i = 0; $i < 10; $i++) {
                            $txnid .= rand(0, 9);
                        }
            
                        $existing = $this->Inventory_indent->find('first', [
                            'conditions' => ['stockin_id' => $txnid]
                        ]);
                    } while ($existing);
            
                    $location_id = $this->Session->read('Auth.Location.id');
                    
                    $stockin_skus = $this->Inventory_indent_items->find('all', [
                        'conditions' => ['consignment_id' => $poid,'status' => 3]
                    ]);
                    
                    if(empty($stockin_skus)){
                        throw new Exception('No item found for this...!');
                    }
                    
                    foreach ($stockin_skus as $stockin_sku) {
                        $sku = $stockin_sku['Inventory_indent_items'];
                        $sku_id = $sku['sku_id'];
                        $approved_price = $sku['approved_price'];
                        
                        if (isset($quantities[$sku_id])) {
                            $qty = floatval($quantities[$sku_id]);
                            $item_type = isset($item_types[$sku_id]) ? $item_types[$sku_id] : 'sku';
                            
                            if ($item_type == 'semifinished') {
                                $product_taxes = $this->Inventory_semifinished_tax->find('all', [
                                    'fields' => ['SUM(tax_percentage) as total_tax'],
                                    'conditions' => ['product_id' => $sku_id, 'status' => 1]
                                ]);
                            } else {
                                $product_taxes = $this->Inventory_sku_tax->find('all', [
                                    'fields' => ['SUM(tax_percentage) as total_tax'],
                                    'conditions' => ['product_id' => $sku_id, 'status' => 1]
                                ]);

                            }
                            $total_tax = isset($product_taxes[0][0]['total_tax'])
                                ? round($product_taxes[0][0]['total_tax'], 2)
                                : 0;

                            // $total_tax = isset($product_taxes[0][0]['total_tax']) ? round($product_taxes[0][0]['total_tax'], 2) : 0;
                            $tax_value = round(($approved_price * $qty) * ($total_tax / 100), 2);
                            $skuamount = round(($approved_price * $qty) + $tax_value, 2);
                            
                            // Update purchase order item
                            $this->Inventory_indent_items->updateAll(
                                ['status' => 4, 'sync' => 0, 'local_sync' => 0, 'stockin_quantity' => $qty,'stockin_tax_value' => $tax_value,
                                'stockin_amount' => $skuamount],
                                ['consignment_id' => $poid, 'sku_id' => $sku_id, 'status'=> 3]
                            );
                            
                            $amount += ($skuamount);
                            
                            //stock ledger entry semifinished
                            if ($item_type == 'semifinished') {
                                
                                $ledgerprevdetails = $this->Stock_ledger->find('first', [
                                    'conditions' => ['sku_id' => $sku_id,'type'=>'OUTLET','item_type'=>'semifinished','store_id'=>$location_id],
                                    'fields' => ['actual_balance'],
                                    'order' => ['id DESC'],
                                    'recursive' => -1
                                ]);
                                
                                $ledger_prev_quantity = !empty($ledgerprevdetails['Stock_ledger']['actual_balance']) 
                                    ? $ledgerprevdetails['Stock_ledger']['actual_balance'] 
                                    : 0;
                                
                                $newQuantity = $qty + $ledger_prev_quantity;
                                
                                $ledgerData['Stock_ledger'] = [
                                        'store_id'        => $location_id,
                                        'sku_id'          => $sku_id,
                                        'sku_name'        => $sku['sku_name'],
                                        'sku_price'       => $approved_price,
                                        'unit_id'         => $sku['sku_unit'],
                                        'unit_name'       => $sku['sku_unit_name'],
                                        'record_id'       => $txnid,
                                        'record_type'     => 'stockin',
                                        'quantity'        => $qty,
                                        'balance_stock'   => $newQuantity,
                                        'actual_balance'  => $newQuantity,
                                        'quantity_type'   => 'credit',
                                        'type'            => 'OUTLET',
                                        'item_type'       => 'semifinished',
                                        'created'         => date('Y-m-d H:i:s')
                                ];
                				if($this->Stock_ledger->saveAll($ledgerData['Stock_ledger'])){
                				
                				    //semis semi update
                    				$ingredients = $this->Inventory_linking->find('all', [
                                        'conditions' => ['parent_id' => $sku_id,'parent_type' => 'Semifinished','component_type' => 'Semifinished', 'status' => 1]
                                    ]);
                                    // print_r($ingredients);exit;
                                    foreach ($ingredients as $ingredient) {
                                        $linked_sku_id = $ingredient['Inventory_linking']['component_id'];
                                        $sku_quantity= $ingredient['Inventory_linking']['quantity']*$qty;
                                            
                                        $ledgerprevdetails = $this->Stock_ledger->find('first', [
                                            'conditions' => ['sku_id' => $linked_sku_id,'store_id' =>$location_id,'item_type'=>'Semifinished' ,'type' =>'OUTLET' ],
                                            'fields' => ['actual_balance'],
                                            'order' => ['id DESC'],
                                            'recursive' => -1
                                        ]);
                                        
                                        $ledger_prev_quantity = !empty($ledgerprevdetails['Stock_ledger']['actual_balance']) 
                                            ? $ledgerprevdetails['Stock_ledger']['actual_balance'] 
                                            : 0;
                                        
                                        $newQuantity = $ledger_prev_quantity+$sku_quantity;
                                        
                                        $semi = $this->Inventory_semifinished->find('first', [
                                            'conditions' => ['Inventory_semifinished.id' => $sku_id],
                                            'recursive' => -1
                                        ]);
                                        
                                        $ledgerData['Stock_ledger'] = [
                                                'store_id'        => $location_id,
                                                'sku_id'          => $ingredient['Inventory_linking']['component_id'],
                                                'sku_name'        => $ingredient['Inventory_linking']['component_name'],
                                                'sku_price'       => $semi['Inventory_semifinished']['price'],
                                                'unit_id'         => $ingredient['Inventory_linking']['component_unit'],
                                                'unit_name'       => $ingredient['Inventory_linking']['component_unit_name'],
                                                'record_id'       => $txnid,
                                                'record_type'     => 'stockin',
                                                'quantity'        => $sku_quantity,
                                                'balance_stock'   => $newQuantity,
                                                'actual_balance'  => $newQuantity,
                                                'quantity_type'   => 'credit',
                                                'item_type'       => 'Semifinished',
                                                'type'            => 'OUTLET',
                                                'created'         => date('Y-m-d H:i:s')
                                        ];
                        				if(!$this->Stock_ledger->saveAll($ledgerData['Stock_ledger'])){
                                            throw new Exception('issue in linking ledger update!');
                                        }
                                    }
                                    
                                    //semis sku update
                                    $ingredients = $this->Inventory_linking->find('all', [
                                        'conditions' => ['parent_id' => $sku_id,'parent_type' => 'Semifinished','component_type' => 'Sku', 'status' => 1]
                                    ]);
                                    // print_r($ingredients);exit;
                                    foreach ($ingredients as $ingredient) {
                                        $linked_sku_id = $ingredient['Inventory_linking']['component_id'];
                                        $sku_quantity= $ingredient['Inventory_linking']['quantity']*$qty;
                                            
                                        $ledgerprevdetails = $this->Stock_ledger->find('first', [
                                            'conditions' => ['sku_id' => $linked_sku_id,'store_id' =>$location_id,'item_type'=>'SKU' ,'type' =>'OUTLET' ],
                                            'fields' => ['actual_balance'],
                                            'order' => ['id DESC'],
                                            'recursive' => -1
                                        ]);
                                        
                                        $ledger_prev_quantity = !empty($ledgerprevdetails['Stock_ledger']['actual_balance']) 
                                            ? $ledgerprevdetails['Stock_ledger']['actual_balance'] 
                                            : 0;
                                        
                                        $newQuantity = $ledger_prev_quantity+$sku_quantity;
                                        
                                        $sku = $this->Inventory_sku->find('first', [
                                            'conditions' => ['Inventory_sku.id' => $sku_id],
                                            'recursive' => -1
                                        ]);
                                        
                                        $ledgerData['Stock_ledger'] = [
                                                'store_id'        => $location_id,
                                                'sku_id'          => $ingredient['Inventory_linking']['component_id'],
                                                'sku_name'        => $ingredient['Inventory_linking']['component_name'],
                                                'sku_price'       => $sku['Inventory_sku']['price'],
                                                'unit_id'         => $ingredient['Inventory_linking']['component_unit'],
                                                'unit_name'       => $ingredient['Inventory_linking']['component_unit_name'],
                                                'record_id'       => $txnid,
                                                'record_type'     => 'stockin',
                                                'quantity'        => $sku_quantity,
                                                'balance_stock'   => $newQuantity,
                                                'actual_balance'  => $newQuantity,
                                                'quantity_type'   => 'credit',
                                                'item_type'       => 'SKU',
                                                'type'            => 'OUTLET',
                                                'created'         => date('Y-m-d H:i:s')
                                        ];
                        				if(!$this->Stock_ledger->saveAll($ledgerData['Stock_ledger'])){
                                            throw new Exception('issue in sku ledger update!');
                                        }
                                    }
                				}else{
                				    throw new Exception('issue in linking sku ledger update!');
                                }
                            }else{
                                
                                $ledgerprevdetails = $this->Stock_ledger->find('first', [
                                    'conditions' => ['sku_id' => $sku_id,'type'=>'OUTLET','item_type'=>'SKU','store_id'=>$location_id],
                                    'fields' => ['actual_balance'],
                                    'order' => ['id DESC'],
                                    'recursive' => -1
                                ]);
                                
                                $ledger_prev_quantity = !empty($ledgerprevdetails['Stock_ledger']['actual_balance']) 
                                    ? $ledgerprevdetails['Stock_ledger']['actual_balance'] 
                                    : 0;
                                
                                $newQuantity = $qty + $ledger_prev_quantity;
                                
                                $ledgerData['Stock_ledger'] = [
                                        'store_id'        => $location_id,
                                        'sku_id'          => $sku_id,
                                        'sku_name'        => $sku['sku_name'],
                                        'sku_price'       => $approved_price,
                                        'unit_id'         => $sku['sku_unit'],
                                        'unit_name'       => $sku['sku_unit_name'],
                                        'record_id'       => $txnid,
                                        'record_type'     => 'stockin',
                                        'quantity'        => $qty,
                                        'balance_stock'   => $newQuantity,
                                        'actual_balance'  => $newQuantity,
                                        'quantity_type'   => 'credit',
                                        'type'            => 'OUTLET',
                                        'item_type'       => 'SKU',
                                        'created'         => date('Y-m-d H:i:s')
                                ];
                				if(!$this->Stock_ledger->saveAll($ledgerData['Stock_ledger'])){
                				    throw new Exception('issue in ledger update!');
                				}
                            }
                        }
                    }
                    
                    $this->Inventory_indent->updateAll(
                        [
                            'stockin_id'     => "'$txnid'",
                            'status'         => 4,
                            'stockin_date'   => "'$today'",
                            'invoice_no'     => "'$invoice'",
                            'stockin_amount' => "'$amount'",
                            'sync'           => 0,
                            'local_sync'     => 0
                        ],
                        [
                            'consignment_id' => $poid,
                            'status' => 3
                        ]
                    );
            
                    $response = ['status'=>true,'message'=>'success'];

                }elseif($type=='po'){
                    $indents = $this->Inventory_purchase_order->find('first', [
                        'conditions' => ['p_o_id' => $poid, 'status' => 3]
                    ]);
                    
                    if (empty($indents)) {
                        throw new Exception('Invalid PO!');
                    }
                    
                    $invidcheck = $this->Inventory_purchase_order->find('first', [
                        'conditions' => ['invoice_no' => $invoice, 'status !=' => 0]
                    ]);
                    
                    if (!empty($invidcheck)) {
                        throw new Exception('Invoice No already exist!');
                    }

                    $store_id=$indents['Inventory_purchase_order']['location_id'];
                    $name=$indents['Inventory_purchase_order']['location_name'];
                    // Generate unique stockin_id
                    do {
                        $txnid = rand(1, 9);
                        for ($i = 0; $i < 10; $i++) {
                            $txnid .= rand(0, 9);
                        }
            
                        $existing = $this->Inventory_purchase_order->find('first', [
                            'conditions' => ['stockin_id' => $txnid]
                        ]);
                    } while ($existing);
            
                    $stockin_skus = $this->Inventory_purchase_order_item->find('all', [
                        'conditions' => ['p_o_id' => $poid,'status' => 3]
                    ]);
                    
                    $amount=0;
                    foreach ($stockin_skus as $stockin_sku) {
                        $sku = $stockin_sku['Inventory_purchase_order_item'];
                        $sku_id = $sku['sku_id'];
                        
                        if (isset($quantities[$sku_id])) {
                            $qty = floatval($quantities[$sku_id]);
                            
                            $price = $sku['sku_price'];
                            
                            $product_taxes = $this->Inventory_sku_tax->find('all', [
                                'fields' => ['SUM(tax_percentage) as total_tax'],
                                'conditions' => ['product_id' => $sku_id, 'status' => 1]
                            ]);
                            
                            $total_tax = isset($product_taxes[0][0]['total_tax'])
                                ? round($product_taxes[0][0]['total_tax'], 2)
                                : 0;
                            // $product_taxes=$this->Inventory_sku_tax->query("select SUM(tax_percentage) as total_tax from inventory_sku_tax where product_id='$sku_id' and status=1 ");
                            // $total_tax=round($product_taxes['0']['0']['total_tax'],2);
                            $tax_value = round(($price*$qty) * ($total_tax / 100),2);
                            $skuamount=round(($price*$qty) + $tax_value,2);
                            
                            // Update purchase order item
                            $this->Inventory_purchase_order_item->updateAll(
                                ['status' => 4, 'sync' => 0, 'local_sync' => 0, 'stockin_quantity' => $qty, 'stockin_total_tax_value' => $tax_value, 'stockin_amount' => $skuamount],
                                ['p_o_id' => $poid, 'sku_id' => $sku_id, 'status' => 3]
                            );
                            $amount=$amount+$skuamount;
                            
                            $ledgerprevdetails = $this->Stock_ledger->find('first', [
                                'conditions' => ['sku_id' => $sku_id,'type'=>'OUTLET','item_type'=>'SKU','store_id'=>$store_id],
                                'fields' => ['actual_balance'],
                                'order' => ['id DESC'],
                                'recursive' => -1
                            ]);
                            
                            $ledger_prev_quantity = !empty($ledgerprevdetails['Stock_ledger']['actual_balance']) 
                                ? $ledgerprevdetails['Stock_ledger']['actual_balance'] 
                                : 0;
                            
                            $newQuantity = $qty + $ledger_prev_quantity;
                            
                            $ledgerData['Stock_ledger'] = [
                                    'store_id'        => $store_id,
                                    'sku_id'          => $sku_id,
                                    'sku_name'        => $sku['sku_name'],
                                    'sku_price'       => $sku['sku_price'],
                                    'unit_id'         => $sku['sku_unit'],
                                    'unit_name'       => $sku['sku_unit_name'],
                                    'record_id'       => $txnid,
                                    'record_type'     => 'stockin',
                                    'quantity'        => $qty,
                                    'balance_stock'   => $newQuantity,
                                    'actual_balance'  => $newQuantity,
                                    'quantity_type'   => 'credit',
                                    'type'            => 'OUTLET',
                                    'sync'            =>0,
                                    'local_sync'      =>0,
                                    'created'         => date('Y-m-d H:i:s')
                            ];
            				if(!$this->Stock_ledger->saveAll($ledgerData['Stock_ledger'])){
            				    throw new Exception('issue in po ledger update!');
            				}
                            
                        }
                    }
                    
                    $amount=$charge+$amount;
                    // Update purchase order
                    // $this->Inventory_purchase_order->query("UPDATE inventory_purchase_order SET 
                    //     stockin_id = '$txnid',
                    //     status = 4,
                    //     stockin_on = '$today',
                    //     stockin_by_id = $store_id,
                    //     stockin_by_name = '$name',
                    //     stockin_charge = '$charge',
                    //     stockin_amount = '$amount',
                    //     invoice_no = '$invoice',
                    //     sync = 0,
                    //     local_sync = 0
                    //     WHERE p_o_id = '$poid' and status=3");
                    
                    $this->Inventory_purchase_order->updateAll(
                    [
                        'stockin_id' => "'$txnid'",
                        'status' => 4,
                        'stockin_on' => "'$today'",
                        'stockin_by_id' => $store_id,
                        'stockin_by_name' => "'$name'",
                        'stockin_charge' => "'$charge'",
                        'stockin_amount' => "'$amount'",
                        'invoice_no' => "'$invoice'",
                        'sync' => 0,
                        'local_sync' => 0
                    ],
                    [
                        'p_o_id' => $poid,
                        'status' => 3
                    ]
                    );

                    
                    $response = ['status'=>true,'message'=>'success'];
                }
                
                $dataSource->commit();
                
                echo json_encode($response);
                return;
            } catch (Exception $e) {
                $dataSource->rollback();
            
                echo json_encode(['status'=>false,'message'=>$e->getMessage()]);
                return;
            }
        }
    }
    */
    
    public function stockin_po() 
    {
        $this->autoRender = false; // Disable view rendering
    
        if ($this->request->is('post')) {
            $poid = $this->request->data['_id'];
            $invoice = trim($this->request->data['_invoice']);
            $quantities = $this->request->data['_quantities']; // associative array: sku_id => quantity
            $item_types = $this->request->data['_item_type']; 
            $charge = $this->request->data['_charge'] ?? 0; 
            $type = $this->request->data['_type']; 

            $today = date('Y-m-d H:i:s');
            
            if (empty($poid)) {
                echo json_encode(['status'=>false,'message'=>'Invalid PO/Consignment ID']);
                return;
            }
            
            if (empty($invoice)) {
                echo json_encode(['status'=>false,'message'=>'Invoice number required']);
                return;
            }
            
            $dataSource = $this->Inventory_indent->getDataSource();
            $dataSource->begin();
            
            try {
                if (empty($quantities) || !is_array($quantities)) {
                    throw new Exception('Invalid quantity data');
                }

                if($type=='consignment'){
                    $invoiceidchk = $this->Inventory_indent->find('first', [
                        'conditions' => ['consignment_id' => $poid,'indent_id' => $invoice]
                    ]);
                    
                    if(empty($invoiceidchk)){
                        throw new Exception('Something went wrong for this indent...!');
                    }
                    
                    $amount = 0;
                    // Generate unique stockin_id
                    do {
                        $txnid = rand(1, 9);
                        for ($i = 0; $i < 10; $i++) {
                            $txnid .= rand(0, 9);
                        }
            
                        $existing = $this->Inventory_indent->find('first', [
                            'conditions' => ['stockin_id' => $txnid]
                        ]);
                    } while ($existing);
            
                    $location_id = $this->Session->read('Auth.Location.id');
                    
                    $stockin_skus = $this->Inventory_indent_items->find('all', [
                        'conditions' => ['consignment_id' => $poid,'status' => 3]
                    ]);
                    
                    if(empty($stockin_skus)){
                        throw new Exception('No item found for this...!');
                    }
                    
                    foreach ($stockin_skus as $stockin_sku) {
                        $sku = $stockin_sku['Inventory_indent_items'];
                        $sku_id = $sku['sku_id'];
                        $approved_price = $sku['approved_price'];
                        
                        if (isset($quantities[$sku_id])) {
                            $qty = floatval($quantities[$sku_id]);
                            $item_type = isset($item_types[$sku_id]) ? $item_types[$sku_id] : 'sku';
                            
                            if ($item_type == 'semifinished') {
                                $product_taxes = $this->Inventory_semifinished_tax->find('all', [
                                    'fields' => ['SUM(tax_percentage) as total_tax'],
                                    'conditions' => ['product_id' => $sku_id, 'status' => 1]
                                ]);
                            } else {
                                $product_taxes = $this->Inventory_sku_tax->find('all', [
                                    'fields' => ['SUM(tax_percentage) as total_tax'],
                                    'conditions' => ['product_id' => $sku_id, 'status' => 1]
                                ]);

                            }
                            $total_tax = isset($product_taxes[0][0]['total_tax'])
                                ? round($product_taxes[0][0]['total_tax'], 2)
                                : 0;

                            // $total_tax = isset($product_taxes[0][0]['total_tax']) ? round($product_taxes[0][0]['total_tax'], 2) : 0;
                            $tax_value = round(($approved_price * $qty) * ($total_tax / 100), 2);
                            $skuamount = round(($approved_price * $qty) + $tax_value, 2);
                            
                            // Update purchase order item
                            $this->Inventory_indent_items->updateAll(
                                ['status' => 4, 'sync' => 0, 'local_sync' => 0, 'stockin_quantity' => $qty,'stockin_tax_value' => $tax_value,
                                'stockin_amount' => $skuamount],
                                ['consignment_id' => $poid, 'sku_id' => $sku_id, 'status'=> 3]
                            );
                            
                            $amount += ($skuamount);
                            
                            //stock ledger entry semifinished
                            if ($item_type == 'semifinished') {
                                
                                $ledgerprevdetails = $this->Stock_ledger->find('first', [
                                    'conditions' => ['sku_id' => $sku_id,'type'=>'OUTLET','item_type'=>'semifinished','store_id'=>$location_id],
                                    'fields' => ['actual_balance'],
                                    'order' => ['id DESC'],
                                    'recursive' => -1
                                ]);
                                
                                $ledger_prev_quantity = !empty($ledgerprevdetails['Stock_ledger']['actual_balance']) 
                                    ? $ledgerprevdetails['Stock_ledger']['actual_balance'] 
                                    : 0;
                                
                                $newQuantity = $qty + $ledger_prev_quantity;
                                
                                $ledgerData['Stock_ledger'] = [
                                        'store_id'        => $location_id,
                                        'sku_id'          => $sku_id,
                                        'sku_name'        => $sku['sku_name'],
                                        'sku_price'       => $approved_price,
                                        'unit_id'         => $sku['sku_unit'],
                                        'unit_name'       => $sku['sku_unit_name'],
                                        'record_id'       => $txnid,
                                        'record_type'     => 'stockin',
                                        'quantity'        => $qty,
                                        'balance_stock'   => $newQuantity,
                                        'actual_balance'  => $newQuantity,
                                        'quantity_type'   => 'credit',
                                        'type'            => 'OUTLET',
                                        'item_type'       => 'semifinished',
                                        'created'         => date('Y-m-d H:i:s')
                                ];
                				if($this->Stock_ledger->saveAll($ledgerData['Stock_ledger'])){
                				
                				    //semis semi update
                    				$ingredients = $this->Inventory_linking->find('all', [
                                        'conditions' => ['parent_id' => $sku_id,'parent_type' => 'Semifinished','component_type' => 'Semifinished', 'status' => 1]
                                    ]);
                                    // print_r($ingredients);exit;
                                    foreach ($ingredients as $ingredient) {
                                        $linked_sku_id = $ingredient['Inventory_linking']['component_id'];
                                        $sku_quantity= $ingredient['Inventory_linking']['quantity']*$qty;
                                            
                                        $ledgerprevdetails = $this->Stock_ledger->find('first', [
                                            'conditions' => ['sku_id' => $linked_sku_id,'store_id' =>$location_id,'item_type'=>'Semifinished' ,'type' =>'OUTLET' ],
                                            'fields' => ['actual_balance'],
                                            'order' => ['id DESC'],
                                            'recursive' => -1
                                        ]);
                                        
                                        $ledger_prev_quantity = !empty($ledgerprevdetails['Stock_ledger']['actual_balance']) 
                                            ? $ledgerprevdetails['Stock_ledger']['actual_balance'] 
                                            : 0;
                                        
                                        $newQuantity = $ledger_prev_quantity+$sku_quantity;
                                        
                                        $semi = $this->Inventory_semifinished->find('first', [
                                            'conditions' => ['Inventory_semifinished.id' => $sku_id],
                                            'recursive' => -1
                                        ]);
                                        
                                        $ledgerData['Stock_ledger'] = [
                                                'store_id'        => $location_id,
                                                'sku_id'          => $ingredient['Inventory_linking']['component_id'],
                                                'sku_name'        => $ingredient['Inventory_linking']['component_name'],
                                                'sku_price'       => $semi['Inventory_semifinished']['price'],
                                                'unit_id'         => $ingredient['Inventory_linking']['component_unit'],
                                                'unit_name'       => $ingredient['Inventory_linking']['component_unit_name'],
                                                'record_id'       => $txnid,
                                                'record_type'     => 'stockin',
                                                'quantity'        => $sku_quantity,
                                                'balance_stock'   => $newQuantity,
                                                'actual_balance'  => $newQuantity,
                                                'quantity_type'   => 'credit',
                                                'item_type'       => 'Semifinished',
                                                'type'            => 'OUTLET',
                                                'created'         => date('Y-m-d H:i:s')
                                        ];
                        				if(!$this->Stock_ledger->saveAll($ledgerData['Stock_ledger'])){
                                            throw new Exception('issue in linking ledger update!');
                                        }
                                    }
                                    
                                    //semis sku update
                                    $ingredients = $this->Inventory_linking->find('all', [
                                        'conditions' => ['parent_id' => $sku_id,'parent_type' => 'Semifinished','component_type' => 'Sku', 'status' => 1]
                                    ]);
                                    // print_r($ingredients);exit;
                                    foreach ($ingredients as $ingredient) {
                                        $linked_sku_id = $ingredient['Inventory_linking']['component_id'];
                                        $sku_quantity= $ingredient['Inventory_linking']['quantity']*$qty;
                                            
                                        $ledgerprevdetails = $this->Stock_ledger->find('first', [
                                            'conditions' => ['sku_id' => $linked_sku_id,'store_id' =>$location_id,'item_type'=>'SKU' ,'type' =>'OUTLET' ],
                                            'fields' => ['actual_balance'],
                                            'order' => ['id DESC'],
                                            'recursive' => -1
                                        ]);
                                        
                                        $ledger_prev_quantity = !empty($ledgerprevdetails['Stock_ledger']['actual_balance']) 
                                            ? $ledgerprevdetails['Stock_ledger']['actual_balance'] 
                                            : 0;
                                        
                                        $newQuantity = $ledger_prev_quantity+$sku_quantity;
                                        
                                        $sku = $this->Inventory_sku->find('first', [
                                            'conditions' => ['Inventory_sku.id' => $sku_id],
                                            'recursive' => -1
                                        ]);
                                        
                                        $ledgerData['Stock_ledger'] = [
                                                'store_id'        => $location_id,
                                                'sku_id'          => $ingredient['Inventory_linking']['component_id'],
                                                'sku_name'        => $ingredient['Inventory_linking']['component_name'],
                                                'sku_price'       => $sku['Inventory_sku']['price'],
                                                'unit_id'         => $ingredient['Inventory_linking']['component_unit'],
                                                'unit_name'       => $ingredient['Inventory_linking']['component_unit_name'],
                                                'record_id'       => $txnid,
                                                'record_type'     => 'stockin',
                                                'quantity'        => $sku_quantity,
                                                'balance_stock'   => $newQuantity,
                                                'actual_balance'  => $newQuantity,
                                                'quantity_type'   => 'credit',
                                                'item_type'       => 'SKU',
                                                'type'            => 'OUTLET',
                                                'created'         => date('Y-m-d H:i:s')
                                        ];
                        				if(!$this->Stock_ledger->saveAll($ledgerData['Stock_ledger'])){
                                            throw new Exception('issue in sku ledger update!');
                                        }
                                    }
                				}else{
                				    throw new Exception('issue in linking sku ledger update!');
                                }
                            }else{
                                
                                $ledgerprevdetails = $this->Stock_ledger->find('first', [
                                    'conditions' => ['sku_id' => $sku_id,'type'=>'OUTLET','item_type'=>'SKU','store_id'=>$location_id],
                                    'fields' => ['actual_balance'],
                                    'order' => ['id DESC'],
                                    'recursive' => -1
                                ]);
                                
                                $ledger_prev_quantity = !empty($ledgerprevdetails['Stock_ledger']['actual_balance']) 
                                    ? $ledgerprevdetails['Stock_ledger']['actual_balance'] 
                                    : 0;
                                
                                $newQuantity = $qty + $ledger_prev_quantity;
                                
                                $ledgerData['Stock_ledger'] = [
                                        'store_id'        => $location_id,
                                        'sku_id'          => $sku_id,
                                        'sku_name'        => $sku['sku_name'],
                                        'sku_price'       => $approved_price,
                                        'unit_id'         => $sku['sku_unit'],
                                        'unit_name'       => $sku['sku_unit_name'],
                                        'record_id'       => $txnid,
                                        'record_type'     => 'stockin',
                                        'quantity'        => $qty,
                                        'balance_stock'   => $newQuantity,
                                        'actual_balance'  => $newQuantity,
                                        'quantity_type'   => 'credit',
                                        'type'            => 'OUTLET',
                                        'item_type'       => 'SKU',
                                        'created'         => date('Y-m-d H:i:s')
                                ];
                				if(!$this->Stock_ledger->saveAll($ledgerData['Stock_ledger'])){
                				    throw new Exception('issue in ledger update!');
                				}
                            }
                        }
                    }
                    
                    $this->Inventory_indent->updateAll(
                        [
                            'stockin_id'     => "'$txnid'",
                            'status'         => 4,
                            'stockin_date'   => "'$today'",
                            'invoice_no'     => "'$invoice'",
                            'stockin_amount' => "'$amount'",
                            'sync'           => 0,
                            'local_sync'     => 0
                        ],
                        [
                            'consignment_id' => $poid,
                            'status' => 3
                        ]
                    );
            
                    $response = ['status'=>true,'message'=>'success'];

                }elseif($type=='po'){
                    $indents = $this->Inventory_purchase_order->find('first', [
                        'conditions' => [
                            'p_o_id' => $poid,
                            'OR' => [
                                ['status' => 3],
                                [
                                    'status' => 4,
                                    'is_stockedin' => 0
                                ]
                            ]
                        ]
                    ]);
                    
                    if (empty($indents)) {
                        throw new Exception('Invalid PO!');
                    }
                    
                    $invidcheck = $this->Inventory_purchase_order->find('first', [
                        'conditions' => ['invoice_no' => $invoice, 'status !=' => 0]
                    ]);
                    
                    if (!empty($invidcheck)) {
                        throw new Exception('Invoice No already exist!');
                    }

                    $store_id=$indents['Inventory_purchase_order']['location_id'];
                    $name=$indents['Inventory_purchase_order']['location_name'];
                    $location_type=$indents['Inventory_purchase_order']['location_type'];
                    $vendor_id=$indents['Inventory_purchase_order']['vendor_id'];
                    $vendor_name=$indents['Inventory_purchase_order']['vendor_name'];
                    $indent_id = $indents['Inventory_purchase_order']['internal_indent_id'];
                    $stockin_charge = $indents['Inventory_purchase_order']['stockin_charge'];
                    // Generate unique stockin_id
                    do {
                        $txnid = rand(1, 9);
                        for ($i = 0; $i < 10; $i++) {
                            $txnid .= rand(0, 9);
                        }
            
                        $existing = $this->Inventory_purchase_order->find('first', [
                            'conditions' => ['stockin_id' => $txnid]
                        ]);
                    } while ($existing);
            
                    $stockin_skus = $this->Inventory_purchase_order_item->find('all', [
                        'conditions' => [
                            'p_o_id' => $poid,
                            'OR' => [
                                ['status' => 3],
                                [
                                    'status' => 4,
                                    'is_stockedin' => 0
                                ]
                            ]
                        ]
                    ]);
                    
                    $amount=0;
                    $stockinamount=0;
                    foreach ($stockin_skus as $stockin_sku) {
                        $sku = $stockin_sku['Inventory_purchase_order_item'];
                        $sku_id = $sku['sku_id'];
                        
                        if (isset($quantities[$sku_id]) && $quantities[$sku_id]!=0) {
                            $qty = floatval($quantities[$sku_id]);
                            $stockinqty=floatval($quantities[$sku_id]);
                            
                            $qty=$sku['stockin_quantity']+$qty;
                            
                            $price = $sku['sku_price'];
                            
                            $product_taxes = $this->Inventory_sku_tax->find('all', [
                                'fields' => ['SUM(tax_percentage) as total_tax'],
                                'conditions' => ['product_id' => $sku_id, 'status' => 1]
                            ]);
                            
                            $total_tax = isset($product_taxes[0][0]['total_tax'])
                                ? round($product_taxes[0][0]['total_tax'], 2)
                                : 0;
                            
                            $tax_value = round(($price*$qty) * ($total_tax / 100),2);
                            $skuamount=round(($price*$qty) + $tax_value,2);
                            
                            $stockintax_value = round(($price*$stockinqty) * ($total_tax / 100),2);
                            $stockinskuamount=round(($price*$stockinqty) + $stockintax_value,2);
                            
                            $is_stockedin=1;
                            if($sku['processed_quantity']>$qty){
                                $is_stockedin=0;
                            }
                            
                            $stockinData['Inventory_stockin_item'] = [
                                'stockin_id'        => $txnid,
                                'p_o_id'        => $poid,
                                'sku_id'          => $sku_id,
                                'sku_name'        => $sku['sku_name'],
                                'sku_price'       => $sku['sku_price'],
                                'sku_unit'         => $sku['sku_unit'],
                                'sku_unit_name'       => $sku['sku_unit_name'],
                                'stockin_quantity'        => $stockinqty,
                                'stockin_total_tax_value'     => $stockintax_value,
                                'stockin_amount'   => $stockinskuamount
                            ];
            				if(!$this->Inventory_stockin_item->saveAll($stockinData['Inventory_stockin_item'])){
            				    throw new Exception('issue in stockin item insert!');
            				}
            				
                            // Update purchase order item
                            $this->Inventory_purchase_order_item->updateAll(
                                ['status' => 4, 'sync' => 0, 'local_sync' => 0, 'stockin_quantity' => $qty, 'stockin_total_tax_value' => $tax_value, 'stockin_amount' => $skuamount,'is_stockedin'=>$is_stockedin],
                                ['p_o_id' => $poid, 'sku_id' => $sku_id, 'status !=' => 0]
                            );
                            $amount=$amount+$skuamount;
                            $stockinamount = $stockinamount+$stockinskuamount;
                            
                            $ledgerprevdetails = $this->Stock_ledger->find('first', [
                                'conditions' => ['sku_id' => $sku_id,'type'=>'OUTLET','item_type'=>'SKU','store_id'=>$store_id],
                                'fields' => ['actual_balance'],
                                'order' => ['id DESC'],
                                'recursive' => -1
                            ]);
                            
                            $ledger_prev_quantity = !empty($ledgerprevdetails['Stock_ledger']['actual_balance']) 
                                ? $ledgerprevdetails['Stock_ledger']['actual_balance'] 
                                : 0;
                            
                            $newQuantity = $stockinqty + $ledger_prev_quantity;
                            
                            $ledgerData['Stock_ledger'] = [
                                    'store_id'        => $store_id,
                                    'sku_id'          => $sku_id,
                                    'sku_name'        => $sku['sku_name'],
                                    'sku_price'       => $sku['sku_price'],
                                    'unit_id'         => $sku['sku_unit'],
                                    'unit_name'       => $sku['sku_unit_name'],
                                    'record_id'       => $txnid,
                                    'record_type'     => 'stockin',
                                    'quantity'        => $stockinqty,
                                    'balance_stock'   => $newQuantity,
                                    'actual_balance'  => $newQuantity,
                                    'quantity_type'   => 'credit',
                                    'type'            => 'OUTLET',
                                    'sync'            =>0,
                                    'local_sync'      =>0,
                                    'created'         => date('Y-m-d H:i:s')
                            ];
            				if(!$this->Stock_ledger->saveAll($ledgerData['Stock_ledger'])){
            				    throw new Exception('issue in po ledger update!');
            				}
                            
                        }
                    }
                    
                    $amount=$charge+$amount;
                    $stockin_charge=$stockin_charge+$charge;
    				$stockinamount = $charge+$stockinamount;
                            
                    // Check for any purchase order items that are not stocked in
                    $pocompletioncheck = $this->Inventory_purchase_order_item->find('first', [
                        'conditions' => [
                            'p_o_id' => $poid,
                            'is_stockedin' => 0,
                            'status !=' => 0
                        ]
                    ]);
                    
                    // Set flag based on result
                    $pois_stockedin = $pocompletioncheck ? 0 : 1;
                    
                    $stockinmainData['Inventory_stockin'] = [
                        'location_type'        => $location_type,
                        'vendor_id' =>$vendor_id,
                        'vendor_name' =>$vendor_name,
                        'indent_id' =>$indent_id,
                        'p_o_id'         => $poid,
                        'stockin_by_id'       => $store_id,
                        'stockin_by_name'        => $name,
                        'stockin_charge'     => $charge,
                        'stockin_amount'   => $stockinamount,
                        'invoice_no'   => $invoice,
                        'stockin_id'   => $txnid
                    ];
    				if(!$this->Inventory_stockin->saveAll($stockinmainData['Inventory_stockin'])){
    				    throw new Exception('issue in stockin insert!');
    				}
    				
    				
                    $this->Inventory_purchase_order->updateAll(
                        [
                            'stockin_id' => "'$txnid'",
                            'status' => 4,
                            'stockin_on' => "'$today'",
                            'stockin_by_id' => $store_id,
                            'stockin_by_name' => "'$name'",
                            'stockin_charge' => "'$stockin_charge'",
                            'stockin_amount' => "'$amount'",
                            'invoice_no' => "'$invoice'",
                            'is_stockedin' => "'$pois_stockedin'",
                            'sync' => 0,
                            'local_sync' => 0
                        ],
                        [
                            'p_o_id' => $poid,
                            'status !=' => 0
                        ]
                    );
                    
                    $response = ['status'=>true,'message'=>'success'];
                }
                
                
                $dataSource->commit();
                
                echo json_encode($response);
                return;
            } catch (Exception $e) {
                $dataSource->rollback();
            
                echo json_encode(['status'=>false,'message'=>$e->getMessage()]);
                return;
            }
        }
    }
    
    public function stock_level() {
        if (!$this->Session->check('Auth.User.id')) {
            return $this->redirect(['controller' => 'dashboard', 'action' => 'login']);
        }
    
        $this->layout = "users";
        
        $today = date("Y-m-d");
        $currentTime = date("H:i", strtotime("+1 minutes"));

        $sel_date = $today;      // default
        $sel_time = $currentTime; // default
        
        $skuledger_startdate = date('Y-m-d', strtotime('-10 days'));
        $stocklevel_enddate   = $skuledger_enddate   = date('Y-m-d');
        $sku_id = null;
        $stocklevel_startdate = $sel_date.' '.$sel_time.':00';
        if ($this->request->is(['post', 'put'])) {
            if (isset($this->request->data['submit_stocklevel'])) {
                
                $sel_date=$this->request->data['sel_date'];
                $sel_time=$this->request->data['sel_time'];
            
                $stocklevel_startdate = $sel_date.' '.$sel_time.':00';
            }
    
            if (isset($this->request->data['submit_skutransactions'])) {
                $daterange = $this->request->data['daterange'] ?? '';
                $dates = explode(" - ", $daterange);
                if (count($dates) === 2) {
                    $skuledger_startdate = date('Y-m-d', strtotime(trim($dates[0])));
                    $skuledger_enddate = date('Y-m-d', strtotime(trim($dates[1])));
                }
                $sku_id = $this->request->data['SkuLedger']['sku'] ?? null;
            }
            
        }
        
        $this->set(compact('sel_date', 'sel_time'));

        $store_id = $this->Session->read('Auth.Location.id');
        // $username = $this->User->query("SELECT location_id FROM users WHERE id = $id");
        // $store_id = $username[0]['users']['location_id'];
        
        $transactions = $this->Stock_ledger->query("
            SELECT sl1.sku_id,sl1.sku_price,sl1.sku_name,sl1.unit_name,s.stock, sl1.actual_balance
            FROM stock_ledger sl1
            INNER JOIN (
                SELECT sku_id, MAX(id) AS max_id
                FROM stock_ledger
                WHERE type = 'OUTLET' and item_type = 'Semifinished'
                  AND store_id = $store_id
                  AND created < '$stocklevel_startdate'
                GROUP BY sku_id
            ) latest ON sl1.id = latest.max_id
            LEFT JOIN inventory_semifinished s ON s.id = sl1.sku_id
            WHERE sl1.type = 'OUTLET'  and sl1.item_type = 'Semifinished'
              AND sl1.store_id = $store_id order by s.name asc
        ");
        
        $skus = $this->Inventory_semifinished->query("SELECT * FROM inventory_semifinished WHERE status=1 order by name asc");
        
        $skutransactions = [];
        if (!empty($sku_id)) {
            
            $startDatetime = $skuledger_startdate . ' ' . shiftstart;
            $endDatetime   = date('Y-m-d', strtotime($skuledger_enddate . ' +1 day')) . ' ' . shiftend;
            
            $skutransactions = $this->Stock_ledger->query("
                SELECT * FROM stock_ledger 
                WHERE created BETWEEN '$startDatetime' AND '$endDatetime'
                AND type='OUTLET' AND sku_id = $sku_id and store_id=$store_id and item_type = 'Semifinished'
                ORDER BY id DESC
            ");
        }
        
        $this->set('stocklevel_daterange', date('m/d/Y', strtotime($stocklevel_startdate)) . ' - ' . date('m/d/Y', strtotime($stocklevel_enddate)));
        $this->set('skuledger_daterange', date('m/d/Y', strtotime($skuledger_startdate)) . ' - ' . date('m/d/Y', strtotime($skuledger_enddate)));
    
        $this->set(compact('transactions', 'skus', 'skutransactions'));
    }

    public function stock_levelexport()
    {
        $date_range = $_GET['sdate'] ?? '';
        
        if (!$date_range) {
            throw new BadRequestException('Invalid date range.');
        }
    
        $store_id = $this->Session->read('Auth.Location.id');
    
        // Fetch store name
        $store_data = $this->Location->find('first', [
            'conditions' => ['Location.id' => $store_id],
            'fields' => ['Location.name']
        ]);
        $store_name = $store_data['Location']['name'] ?? 'Unknown Store';
    
        // Fetch stock ledger data (same logic as in view)
        $transactions = $this->Stock_ledger->query("
            SELECT sl1.sku_id, sl1.sku_price, sl1.sku_name, sl1.unit_name,
                   s.stock, sl1.actual_balance
            FROM stock_ledger sl1
            INNER JOIN (
                SELECT sku_id, MAX(id) AS max_id
                FROM stock_ledger
                WHERE type = 'OUTLET' 
                  AND item_type = 'Semifinished'
                  AND store_id = $store_id
                  AND created < '$date_range'
                GROUP BY sku_id
            ) latest ON sl1.id = latest.max_id
            LEFT JOIN inventory_semifinished s ON s.id = sl1.sku_id
            WHERE sl1.type = 'OUTLET' 
              AND sl1.item_type = 'Semifinished'
              AND sl1.store_id = $store_id
            ORDER BY s.name ASC
        ");
    
        // Prepare for Excel export
        App::import('Vendor', 'PHPExcel/PHPExcel');
        error_reporting(0);
        ini_set('display_errors', TRUE);
        ini_set('display_startup_errors', TRUE);
    
        if (PHP_SAPI == 'cli') {
            die('This example should only be run from a Web Browser');
        }
    
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->getProperties()
            ->setCreator("Aether")
            ->setLastModifiedBy("Aether")
            ->setTitle("Stock Level Report")
            ->setSubject("Stock Report")
            ->setDescription("Exported stock level data.")
            ->setKeywords("stock excel")
            ->setCategory("Export File");
    
        // ===========================
        // HEADER SECTION
        // ===========================
        $sheet = $objPHPExcel->setActiveSheetIndex(0);
        $sheet->setCellValue('A1', "OUTLET STOCK LEVEL REPORT");
        $sheet->mergeCells('A1:G1');
        $sheet->getStyle('A1:G1')->getFont()->setBold(true)->setSize(14);
    
        $sheet->setCellValue('A2', "Store: $store_name");
        $sheet->mergeCells('A2:G2');
        $sheet->getStyle('A2:G2')->getFont()->setBold(true)->setSize(12);
    
        $sheet->setCellValue('A3', "Date $date_range ");
        $sheet->mergeCells('A3:G3');
        $sheet->getStyle('A3:G3')->getFont()->setBold(true)->setSize(11);
    
        // ===========================
        // TABLE HEADER
        // ===========================
        $headers = ['Sr No.', 'SKU Name', 'Unit Name', 'Price', 'Required Qty', 'Available Qty', 'Value'];
        $col = 'A';
        foreach ($headers as $header) {
            $sheet->setCellValue($col . '5', $header);
            $sheet->getStyle($col . '5')->getFont()->setBold(true);
            $sheet->getColumnDimension($col)->setAutoSize(true);
            $col++;
        }
    
        // ===========================
        // TABLE DATA
        // ===========================
        $row = 6;
        $count = 1;
    
        foreach ($transactions as $txn) {
            $data = $txn['sl1'];
            $stock = $txn['s']['stock'] ?? 0;
            $value = round($data['sku_price'] * $data['actual_balance'], 2);
    
            $sheet->setCellValue("A$row", $count);
            $sheet->setCellValue("B$row", $data['sku_name']);
            $sheet->setCellValue("C$row", $data['unit_name']);
            $sheet->setCellValue("D$row", (float)$data['sku_price']);
            $sheet->setCellValue("E$row", (float)$stock);
            $sheet->setCellValue("F$row", (float)$data['actual_balance']);
            $sheet->setCellValue("G$row", $value);
    
            $row++;
            $count++;
        }
    
        // ===========================
        // STYLE & FINALIZE
        // ===========================
        $sheet->getStyle("A5:G5")->getFont()->setBold(true);
        $sheet->setTitle('Stock Level');
    
        $objPHPExcel->setActiveSheetIndex(0);
    
        // ===========================
        // OUTPUT EXCEL
        // ===========================
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="Stock_Level_Report.xls"');
        header('Cache-Control: max-age=0');
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
        header('Cache-Control: cache, must-revalidate');
        header('Pragma: public');
    
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }

    public function indent_pdf() {
        if (!$this->Session->check('Auth.Location.id')) {
            return $this->redirect(['controller' => 'dashboard', 'action' => 'login']);
        }
    
        $this->layout = "login"; // or use 'ajax' or a dedicated PDF layout
    
        if ($this->request->is(['post', 'put'])) {
            $po_id = $this->request->data['Report']['poid'];
            // Secure: Use bound parameters
            $indents = $this->Inventory_indent->query(
                "SELECT *,(SELECT print_line1  FROM `locations`where id=inventory_indent.location_id) as print_line1
                ,(SELECT print_line2  FROM `locations`where id=inventory_indent.location_id) as print_line2
                ,(SELECT print_line3  FROM `locations`where id=inventory_indent.location_id) as print_line3
                ,(SELECT print_line1  FROM `locations`where id=inventory_indent.warehouse_id) as warehouse_print_line1
                ,(SELECT print_line2  FROM `locations`where id=inventory_indent.warehouse_id) as warehouse_print_line2
                ,(SELECT print_line3  FROM `locations`where id=inventory_indent.warehouse_id) as warehouse_print_line3
                FROM inventory_indent 
                 WHERE inventory_indent.indent_id = $po_id AND status != 0"
            );
    
            $items = $this->Inventory_indent_items->query(
                "SELECT * FROM inventory_indent_items 
                 WHERE indent_id = $po_id AND status != 0"
            );
            $location_id=$indents[0]['inventory_indent']['location_id'];
    
            // Get location details - default static for now
            $locationdetail = $this->Location->find('first', [
                'conditions' => ['Location.id' => $location_id]
            ]);
    
            // Send data to the view
            $this->set(compact('po_id', 'indents', 'items', 'locationdetail'));
    
            // Optional: Force specific view if needed
            $this->render('indent_pdf'); // e.g., pdf_template.ctp
        }
    }
        
	public function stock_damage()
    {
        if (!$this->Session->check('Auth.User.id')) {
            return $this->redirect(['controller' => 'dashboard', 'action' => 'login']);
        }
    
        $this->layout = "users";
    
        $startDate = date('m/d/Y', strtotime('-1 month'));
        $endDate = date('m/d/Y');
    
        if ($this->request->is(['post', 'put'])) {
            $daterange = $this->request->data['daterange'];
            $dates = explode(" - ", $daterange);
            $start_date = trim($dates[0]);
            $end_date = trim($dates[1]);
    
            $startDate = date('m/d/Y', strtotime($start_date));
            $endDate = date('m/d/Y', strtotime($end_date));
    
            $startdate = date('Y-m-d', strtotime($start_date));
            $enddate = date('Y-m-d', strtotime($end_date));
        } else {
            $startdate = date('Y-m-d', strtotime($startDate));
            $enddate = date('Y-m-d', strtotime($endDate));
        }
    
        // Session values
        $locationId = $this->Session->read('Auth.Location.id');
    
        // Build condition based on role
        $conditions = "created BETWEEN '$startdate 00:00:01' AND '$enddate 23:59:59'";
        if ($userRole !== 'admin') {
            $conditions .= " AND location_id = '$locationId'";
        }
    
        $users = $this->Inventory_damage->query("
            SELECT * FROM inventory_damage 
            WHERE $conditions and item_type='SKU' and type='Outlet' and status=1
            ORDER BY id DESC
        ");
    
        $yourStartDateValue = $startDate . ' - ' . $endDate;
        $this->set('startdate', $yourStartDateValue);
        $this->set(compact('users'));
    }
    
    public function damage_change_status() 
    {
        $this->autoRender = false; // Disable view rendering
    
        if ($this->request->is('post')) {
            $userId = $this->request->data['_id'];
    
            // Update user status
            $this->Inventory_damage->id = $userId;
            $newStatus = $this->Inventory_damage->field('status') == 1 ? 0 : 1;
            if ($this->Inventory_damage->save(['status' => $newStatus,'sync' => 0])) {
                echo json_encode(['status' => true]);
            } else {
                echo json_encode(['status' => false]);
            }
        }
    }

	public function create_damage()
    {
        if (!$this->Session->check('Auth.User.id')) {
            $this->redirect(['controller' => 'dashboard', 'action' => 'login']);
        }
    
        $this->layout = "users";
    
        $location_id = $this->Session->read('Auth.Location.id');
        $locations = $this->Location->find('all', [
            'conditions' => ['status' => 1, 'id' => $location_id,'type' => 'Outlet']
        ]);
    
        $this->set(compact('locations'));
    
        $users = $this->Inventory_sku->find('all', [
            'conditions' => ['Inventory_sku.status' => 1],
            'order' => ['Inventory_sku.name ASC']
        ]);
        $this->set(compact('users'));
    }

    public function add_damage() 
    {
        $this->autoRender = false;
    
        if (!$this->Session->check('Auth.User.id')) {
            echo json_encode(['response_code' => 0, 'data' => 'User session expired.']);
            return;
        }
    
        $id = $this->Session->read('Auth.Location.id');
        $name = $this->Session->read('Auth.Location.name');
    
        if (isset($_POST['checkedData']) && isset($_POST['location_id'])) {
            $checkeddatas = json_decode($_POST['checkedData'], true);
            $location_id = (int)$_POST['location_id'];
            $damage_date = $_POST['damage_date'];
            
            // Validate location
            $location = $this->Location->find('first', [
                'conditions' => ['Location.id' => $location_id],
                'fields' => ['id', 'name'],
                'recursive' => -1
            ]);
    
            if (empty($location)) {
                echo json_encode(['response_code' => 0, 'data' => 'Invalid location selected.']);
                return;
            }
    
            // Generate unique indent_id
            do {
                // $txnid = '';
                $txnid = rand(1, 9);
                for ($i = 0; $i < 10; $i++) {
                    $txnid .= rand(0, 9);
                }
    
                $existing = $this->Inventory_damage->find('first', [
                    'conditions' => ['damage_id' => $txnid],
                    'recursive' => -1
                ]);
            } while ($existing);
    
            $amount = 0;
            $itemsSaved = true;
    
            foreach ($checkeddatas as $item) {
                $skuid = $item['id'];
                $price = $item['price'];
                $quantity = $item['sku_quantity'];
                
                $skuData = [
                    'location_id' => $location_id,
                    'location_name' => $location['Location']['name'],
                    'damage_id' => $txnid,
                    'item_id' => $skuid,
                    'item_name' => $item['sku_name'],
                    'item_category_id' => $item['cat_id'],
                    'item_category_name' => $item['cat_name'],
                    'unit_id' => $item['unit_id'],
                    'unit_name' => $item['unit_name'],
                    'item_price' => $price,
                    'quantity' => $quantity,
                    'amount' => $price*$quantity
                ];
                $this->Inventory_damage_item->create(); // ensure new row
                if (!$this->Inventory_damage_item->save($skuData)) {
                    $itemsSaved = false;
                    break;
                }
                
                $amount=$amount+($price*$quantity);
                
                $ledgerprevdetails = $this->Stock_ledger->find('first', [
                    'conditions' => ['sku_id' => $skuid,'type'=>'Outlet','item_type'=>'SKU','store_id'=>$location_id],
                    'fields' => ['actual_balance'],
                    'order' => ['id DESC'],
                    'recursive' => -1
                ]);
                
                $ledger_prev_quantity = !empty($ledgerprevdetails['Stock_ledger']['actual_balance']) 
                    ? $ledgerprevdetails['Stock_ledger']['actual_balance'] 
                    : 0;
                
                $newQuantity = $ledger_prev_quantity - $quantity;
                // print_r($ledger_prev_quantity);exit;
                $ledgerData['Stock_ledger'] = [
                        'store_id'        => $location_id,
                        'sku_id'          => $skuid,
                        'sku_name'        => $item['sku_name'],
                        'sku_price'       => $price,
                        'unit_id'         => $item['unit_id'],
                        'unit_name'       => $item['unit_name'],
                        'record_id'       => $txnid,
                        'record_type'     => 'damage',
                        'quantity'        => $quantity,
                        'balance_stock'   => $newQuantity,
                        'actual_balance'  => $newQuantity,
                        'quantity_type'   => 'debit',
                        'type'            => 'Outlet',
                        'created'         => date('Y-m-d H:i:s')
                ];
				$this->Stock_ledger->saveAll($ledgerData['Stock_ledger']);
                
            }
    
            if ($itemsSaved) {
                $damageData = [
                    'location_id' => $location_id,
                    'location_name' => $location['Location']['name'],
                    'damage_id' => $txnid,
                    'damage_date' => $damage_date,
                    'type' => 'Outlet',
                    'item_type' => 'SKU',
                    'amount' => $amount
                ];
                
                $this->Inventory_damage->create();
                if ($this->Inventory_damage->save($damageData)) {
                    echo json_encode(['response_code' => 1, 'data' => 'saved']);
                } else {
                    echo json_encode(['response_code' => 0, 'data' => 'Failed to save damage master data.']);
                }
            } else {
                echo json_encode(['response_code' => 0, 'data' => 'Failed to save one or more items.']);
            }
        } else {
            echo json_encode(['response_code' => 0, 'data' => 'Invalid request. Missing data.']);
        }
    }

    public function view_damage()
	{
	    if(!$this->Session->check('Auth.User.id')){
			$this->redirect(array('controller'=>'dashboard','action' => 'login'));		
		}
        
	    $this->layout = "users";
		$id=$_REQUEST['id'];
    
        $indents = $this->Inventory_damage->query("SELECT * FROM inventory_damage where damage_id=$id and status=1");
        $this->set(compact('indents'));
        
		$items = $this->Inventory_damage_item->query("SELECT * FROM inventory_damage_items where damage_id=$id and status=1");
        
        $this->set(compact('items'));
        
        
	}
	
	public function edit_damage()
    {
        if (!$this->Session->check('Auth.User.id')) {
            return $this->redirect(['controller' => 'dashboard', 'action' => 'login']);
        }
    
        $this->layout = "users";
        $id = $_REQUEST['id'];
    
        $user_location = $this->Session->read('Auth.Location.id');
    
        // Location dropdown data
        
        $locations = $this->Location->find('list', [
            'conditions' => ['status' => 1, 'id' => $user_location,'type' => 'Outlet']
        ]);
    
        // Fetch indent data
        $user = "SELECT *,
            (SELECT quantity FROM inventory_damage_items
             WHERE damage_id='$id' AND item_id=inventory_skus.id and status=1) as quantity 
            FROM inventory_skus 
            WHERE inventory_skus.status=1 
            ORDER BY inventory_skus.name ASC";
       
        $users = $this->Inventory_sku->query($user);
         
        // Fetch existing indent record
        $indentData = $this->Inventory_damage->find('first', [
            'conditions' => ['damage_id' => $id,'status' => 1],
            'recursive' => -1
        ]);
        $selected_location_id = $indentData['Inventory_damage']['location_id'];
        $selected_date = $indentData['Inventory_damage']['damage_date'];
    
        $this->set(compact('users', 'id', 'locations', 'selected_location_id', 'selected_date'));
    }

    public function update_damage() 
    {
        $this->autoRender = false;
    
        $id = $this->Session->read('Auth.Location.id');
        $name = $this->Session->read('Auth.Location.name');
    
        if (isset($_POST['checkedData'])) {
            $checkeddatas = json_decode($_POST['checkedData'], true);
            $indentid = $_POST['indent_id'];
            $damage_date = $_POST['damage_date'];
            $location_id = isset($_POST['location_id']) ? $_POST['location_id'] : null;
            $location_name = '';
            
            // Optional: fetch location name if not passed from frontend
            if (!empty($location_id)) {
                $loc = $this->Location->find('first', [
                    'conditions' => ['Location.id' => $location_id],
                    'fields' => ['Location.name'],
                    'recursive' => -1
                ]);
                $location_name = isset($loc['Location']['name']) ? $loc['Location']['name'] : '';
            }
            
            $items = $this->Inventory_damage_item->find('all', [
                'conditions' => [
                    'damage_id' => $indentid,
                    'status' => 1
                ],
                'recursive' => -1
            ]);
            
            foreach ($items as $lastdata) {
                $damagedata = $lastdata['Inventory_damage_item']; // Use the correct model alias
                $skuid       = $damagedata['item_id'];
                $price       = $damagedata['item_price'];
                $sku_name    = $damagedata['item_name'];
                $sku_brand   = isset($damagedata['sku_brand']) ? $damagedata['sku_brand'] : ''; // if available
                $unit_name   = $damagedata['unit_name'];
                $unit_id     = $damagedata['unit_id'];
                $cat_id      = $damagedata['item_category_id'];
                $cat_name    = $damagedata['item_category_name'];
                $quantity    = $damagedata['quantity'];
            
                if (!empty($quantity)) {
                    $ledgerprevdetails = $this->Stock_ledger->find('first', [
                        'conditions' => [
                            'sku_id' => $skuid,
                            'type' => 'Outlet',
                            'item_type'=>'SKU',
                            'store_id' => $location_id
                        ],
                        'fields' => ['actual_balance'],
                        'order' => ['id DESC'],
                        'recursive' => -1
                    ]);
            
                    $ledger_prev_quantity = !empty($ledgerprevdetails['Stock_ledger']['actual_balance']) 
                        ? $ledgerprevdetails['Stock_ledger']['actual_balance'] 
                        : 0;
            
                    $newQuantity = $ledger_prev_quantity + $quantity;
            
                    $ledgerData = [
                        'Stock_ledger' => [
                            'store_id'        => $location_id,
                            'sku_id'          => $skuid,
                            'sku_name'        => $sku_name,
                            'sku_price'       => $price,
                            'unit_id'         => $unit_id,
                            'unit_name'       => $unit_name,
                            'record_id'       => $indentid,
                            'record_type'     => 'reverse_damage',
                            'quantity'        => $quantity,
                            'balance_stock'   => $newQuantity,
                            'actual_balance'  => $newQuantity,
                            'quantity_type'   => 'credit',
                            'type'            => 'Outlet',
                            'created'         => date('Y-m-d H:i:s')
                        ]
                    ];
            
                    $this->Stock_ledger->create(); // ensure a fresh entry
                    $this->Stock_ledger->save($ledgerData['Stock_ledger']);
                }
            }

    
            
            // Delete existing indent and items
            $this->Inventory_damage_item->query("UPDATE inventory_damage_items SET  status=0,sync = 0,local_sync=0 WHERE damage_id = " . (int)$indentid);
            $this->Inventory_damage->query("UPDATE inventory_damage SET  status=0,sync = 0,local_sync=0 WHERE damage_id = " . (int)$indentid);
    
            $amount = 0;
            $chk = false;
    
            foreach ($checkeddatas as $checkeddata) {
                $skuid = $checkeddata['id'];
                $price = $checkeddata['price'];
                $sku_name = $checkeddata['sku_name'];
                $sku_brand = $checkeddata['sku_brand'];
                $unit_name = $checkeddata['unit_name'];
                $unit_id = $checkeddata['unit_id'];
                $cat_id = $checkeddata['cat_id'];
                $cat_name = $checkeddata['cat_name'];
                $quantity = $checkeddata['sku_quantity'];
                
                if (!empty($quantity)) {
                    $itemData = array(
                        'location_id' => $location_id,
                        'location_name' => $location_name,
                        'damage_id' => $indentid,
                        'item_id' => $skuid,
                        'item_name' => $sku_name,
                        'item_category_id' => $cat_id,
                        'item_category_name' => $cat_name,
                        'unit_id' => $unit_id,
                        'unit_name' => $unit_name,
                        'item_price' => $price,
                        'quantity' => $quantity,
                        'amount' => $price*$quantity
                    );
                    
                    if ($this->Inventory_damage_item->saveAll($itemData)) {
                        
                        $ledgerprevdetails = $this->Stock_ledger->find('first', [
                            'conditions' => ['sku_id' => $skuid,'type'=>'Outlet','item_type'=>'SKU','store_id'=>$location_id],
                            'fields' => ['actual_balance'],
                            'order' => ['id DESC'],
                            'recursive' => -1
                        ]);
                        
                        $ledger_prev_quantity = !empty($ledgerprevdetails['Stock_ledger']['actual_balance']) 
                            ? $ledgerprevdetails['Stock_ledger']['actual_balance'] 
                            : 0;
                        
                        $newQuantity = $ledger_prev_quantity - $quantity;
                        // print_r($ledger_prev_quantity);exit;
                        $ledgerData['Stock_ledger'] = [
                                'store_id'        => $location_id,
                                'sku_id'          => $skuid,
                                'sku_name'        => $sku_name,
                                'sku_price'       => $price,
                                'unit_id'         => $unit_id,
                                'unit_name'       => $unit_name,
                                'record_id'       => $indentid,
                                'record_type'     => 'update_damage',
                                'quantity'        => $quantity,
                                'balance_stock'   => $newQuantity,
                                'actual_balance'  => $newQuantity,
                                'quantity_type'   => 'debit',
                                'type'            => 'Outlet',
                                'created'         => date('Y-m-d H:i:s')
                        ];
        				$this->Stock_ledger->saveAll($ledgerData['Stock_ledger']);
                        
                        $chk = true;
                    } else {
                        $chk = false;
                        $this->Flash->error(__('Damage could not be updated. Please, try again.'));
                    }
    
                    // $productamount = $quantity * $price;
                    // $amount += $productamount;
                    $amount += ($price*$quantity);
                }
            }
    
            if ($chk) {
                $indentData = array(
                    'location_id' => $location_id,
                    'location_name' => $location_name,
                    'damage_id' => $indentid,
                    'damage_date' => $damage_date,
                    'type' => 'Outlet',
                    'item_type' => 'SKU',
                    'amount' => $amount
                );
    
                if ($this->Inventory_damage->saveAll($indentData)) {
                    echo json_encode(['response_code' => 1, 'data' => 'Updated']);
                } else {
                    echo json_encode(['response_code' => 0, 'data' => 'Damage could not be updated. Please, try again.']);
                }
            } else {
                echo json_encode(['response_code' => 0, 'data' => 'Damage items could not be updated. Please, try again.']);
            }
        }
    }
    
    public function semi_stock_damage()
    {
        if (!$this->Session->check('Auth.Location.id')) {
            return $this->redirect(['controller' => 'dashboard', 'action' => 'login']);
        }
    
        $this->layout = "users";
    
        $startDate = date('m/d/Y', strtotime('-1 month'));
        $endDate = date('m/d/Y');
    
        if ($this->request->is(['post', 'put'])) {
            $daterange = $this->request->data['daterange'];
            $dates = explode(" - ", $daterange);
            $start_date = trim($dates[0]);
            $end_date = trim($dates[1]);
    
            $startDate = date('m/d/Y', strtotime($start_date));
            $endDate = date('m/d/Y', strtotime($end_date));
    
            $startdate = date('Y-m-d', strtotime($start_date));
            $enddate = date('Y-m-d', strtotime($end_date));
        } else {
            $startdate = date('Y-m-d', strtotime($startDate));
            $enddate = date('Y-m-d', strtotime($endDate));
        }
    
        // Session values
        $userRole = $this->Session->read('Auth.User.role');
        $locationId = $this->Session->read('Auth.Location.id');
    
        // Build condition based on role
        $conditions = "created BETWEEN '$startdate 00:00:01' AND '$enddate 23:59:59'";
        if ($userRole !== 'admin') {
            $conditions .= " AND location_id = '$locationId'";
        }
    
        $users = $this->Inventory_damage->query("
            SELECT * FROM inventory_damage 
            WHERE $conditions and item_type='semifinished' and type='Outlet'
            ORDER BY id DESC
        ");
    
        $yourStartDateValue = $startDate . ' - ' . $endDate;
        $this->set('startdate', $yourStartDateValue);
        $this->set(compact('users'));
    }
    
    public function semi_damage_change_status() 
    {
        $this->autoRender = false; // Disable view rendering
    
        if ($this->request->is('post')) {
            $userId = $this->request->data['_id'];
    
            // Update user status
            $this->Inventory_damage->id = $userId;
            $newStatus = $this->Inventory_damage->field('status') == 1 ? 0 : 1;
            if ($this->Inventory_damage->save(['status' => $newStatus,'sync' => 0,'local_sync' => 0])) {
                echo json_encode(['status' => true]);
            } else {
                echo json_encode(['status' => false]);
            }
        }
    }

	public function semi_create_damage()
    {
        if (!$this->Session->check('Auth.Location.id')) {
            $this->redirect(['controller' => 'dashboard', 'action' => 'login']);
        }
    
        $this->layout = "users";
    
        if ($this->Session->read('Auth.User.role') == 'admin') {
            $locations = $this->Location->find('all', ['conditions' => ['status' => 1,'type' => 'Outlet']]);
        } else {
            $location_id = $this->Session->read('Auth.Location.id');
            $locations = $this->Location->find('all', [
                'conditions' => ['status' => 1, 'id' => $location_id,'type' => 'Outlet']
            ]);
        }
    
        $this->set(compact('locations'));
    
        $users = $this->Inventory_semifinished->find('all', [
            'conditions' => ['Inventory_semifinished.status' => 1],
            'order' => ['Inventory_semifinished.name DESC']
        ]);
        $this->set(compact('users'));
    }

    public function semi_add_damage() 
    {
        $this->autoRender = false;
    
        if (!$this->Session->check('Auth.User.id')) {
            echo json_encode(['response_code' => 0, 'data' => 'User session expired.']);
            return;
        }
    
        $id = $this->Session->read('Auth.User.id');
        $name = $this->Session->read('Auth.User.name');
    
        if (isset($_POST['checkedData']) && isset($_POST['location_id'])) {
            $checkeddatas = json_decode($_POST['checkedData'], true);
            $location_id = (int)$_POST['location_id'];
            $damage_date = $_POST['damage_date'];
            
            // Validate location
            $location = $this->Location->find('first', [
                'conditions' => ['Location.id' => $location_id],
                'fields' => ['id', 'name'],
                'recursive' => -1
            ]);
    
            if (empty($location)) {
                echo json_encode(['response_code' => 0, 'data' => 'Invalid location selected.']);
                return;
            }
    
            // Generate unique indent_id
            do {
                // $txnid = '';
                $txnid = rand(1, 9);
                for ($i = 0; $i < 10; $i++) {
                    $txnid .= rand(0, 9);
                }
    
                $existing = $this->Inventory_damage->find('first', [
                    'conditions' => ['damage_id' => $txnid],
                    'recursive' => -1
                ]);
            } while ($existing);
    
            $amount = 0;
            $itemsSaved = true;
    
            foreach ($checkeddatas as $item) {
                $skuid = $item['id'];
                $price = $item['price'];
                $quantity = $item['sku_quantity'];
                
                $skuData = [
                    'location_id' => $location_id,
                    'location_name' => $location['Location']['name'],
                    'damage_id' => $txnid,
                    'item_id' => $skuid,
                    'item_name' => $item['sku_name'],
                    'item_category_id' => $item['cat_id'],
                    'item_category_name' => $item['cat_name'],
                    'unit_id' => $item['unit_id'],
                    'unit_name' => $item['unit_name'],
                    'item_price' => $price,
                    'quantity' => $quantity,
                    'amount' => $price*$quantity
                ];
                $this->Inventory_damage_item->create(); // ensure new row
                if (!$this->Inventory_damage_item->save($skuData)) {
                    $itemsSaved = false;
                    break;
                }
                
                $amount=$amount+($price*$quantity);
                
                $ledgerprevdetails = $this->Stock_ledger->find('first', [
                    'conditions' => ['sku_id' => $skuid,'type'=>'OUTLET','item_type'=>'semifinished','store_id'=>$location_id],
                    'fields' => ['actual_balance'],
                    'order' => ['id DESC'],
                    'recursive' => -1
                ]);
                
                $ledger_prev_quantity = !empty($ledgerprevdetails['Stock_ledger']['actual_balance']) 
                    ? $ledgerprevdetails['Stock_ledger']['actual_balance'] 
                    : 0;
                
                $newQuantity = $ledger_prev_quantity - $quantity;
                // print_r($ledger_prev_quantity);exit;
                $ledgerData['Stock_ledger'] = [
                        'store_id'        => $location_id,
                        'sku_id'          => $skuid,
                        'sku_name'        => $item['sku_name'],
                        'sku_price'       => $price,
                        'unit_id'         => $item['unit_id'],
                        'unit_name'       => $item['unit_name'],
                        'record_id'       => $txnid,
                        'record_type'     => 'damage',
                        'quantity'        => $quantity,
                        'balance_stock'   => $newQuantity,
                        'actual_balance'  => $newQuantity,
                        'quantity_type'   => 'debit',
                        'type'            => 'OUTLET',
                        'item_type'       => 'semifinished',
                        'created'         => date('Y-m-d H:i:s')
                ];
				$this->Stock_ledger->saveAll($ledgerData['Stock_ledger']);
                
            }
    
            if ($itemsSaved) {
                $damageData = [
                    'location_id' => $location_id,
                    'location_name' => $location['Location']['name'],
                    'damage_id' => $txnid,
                    'damage_date' => $damage_date,
                    'type' => 'Outlet',
                    'item_type' => 'semifinished',
                    'amount' => $amount
                ];
                
                $this->Inventory_damage->create();
                if ($this->Inventory_damage->save($damageData)) {
                    echo json_encode(['response_code' => 1, 'data' => 'saved']);
                } else {
                    echo json_encode(['response_code' => 0, 'data' => 'Failed to save damage master data.']);
                }
            } else {
                echo json_encode(['response_code' => 0, 'data' => 'Failed to save one or more items.']);
            }
        } else {
            echo json_encode(['response_code' => 0, 'data' => 'Invalid request. Missing data.']);
        }
    }

    public function semi_view_damage()
	{
	    if(!$this->Session->check('Auth.Location.id')){
			$this->redirect(array('controller'=>'dashboard','action' => 'login'));		
		}
        
	    $this->layout = "users";
		$id=$_REQUEST['id'];
    
        $indents = $this->Inventory_damage->query("SELECT * FROM inventory_damage where damage_id=$id and status=1");
        $this->set(compact('indents'));
        
		$items = $this->Inventory_damage_item->query("SELECT * FROM inventory_damage_items where damage_id=$id and status=1");
        
        $this->set(compact('items'));
        
        
	}
	
	public function semi_edit_damage()
    {
        if (!$this->Session->check('Auth.Location.id')) {
            return $this->redirect(['controller' => 'dashboard', 'action' => 'login']);
        }
    
        $this->layout = "users";
        $id = $_REQUEST['id'];
    
        $user_id = $this->Session->read('Auth.User.id');
        $role = $this->Session->read('Auth.User.role');
        $user_location = $this->Session->read('Auth.Location.id');
    
        // Location dropdown data
        if ($role == 'admin') {
            $locations = $this->Location->find('list', ['conditions' => ['status' => 1,'type' => 'Outlet']]);
        } else {
            $locations = $this->Location->find('list', [
                'conditions' => ['status' => 1, 'id' => $user_location,'type' => 'Outlet']
            ]);
        }
    
        // Fetch indent data
        $user = "SELECT *,
            (SELECT quantity FROM inventory_damage_items
             WHERE damage_id='$id' AND item_id=inventory_semifinished.id and status=1) as quantity 
            FROM inventory_semifinished 
            WHERE inventory_semifinished.status=1 
            ORDER BY inventory_semifinished.name DESC";
       
        $users = $this->Inventory_semifinished->query($user);
         
        // Fetch existing indent record
        $indentData = $this->Inventory_damage->find('first', [
            'conditions' => ['damage_id' => $id,'status' => 1],
            'recursive' => -1
        ]);
        $selected_location_id = $indentData['Inventory_damage']['location_id'];
        $selected_date = $indentData['Inventory_damage']['damage_date'];
    
        $this->set(compact('users', 'id', 'locations', 'selected_location_id', 'selected_date'));
    }

    public function semi_update_damage() 
    {
        $this->autoRender = false;
    
        $id = $this->Session->read('Auth.User.id');
        $name = $this->Session->read('Auth.User.name');
    
        if (isset($_POST['checkedData'])) {
            $checkeddatas = json_decode($_POST['checkedData'], true);
            $indentid = $_POST['indent_id'];
            $damage_date = $_POST['damage_date'];
            $location_id = isset($_POST['location_id']) ? $_POST['location_id'] : null;
            $location_name = '';
            
            // Optional: fetch location name if not passed from frontend
            if (!empty($location_id)) {
                $loc = $this->Location->find('first', [
                    'conditions' => ['Location.id' => $location_id],
                    'fields' => ['Location.name'],
                    'recursive' => -1
                ]);
                $location_name = isset($loc['Location']['name']) ? $loc['Location']['name'] : '';
            }
            
            $items = $this->Inventory_damage_item->find('all', [
                'conditions' => [
                    'damage_id' => $indentid,
                    'status' => 1
                ],
                'recursive' => -1
            ]);
            
            foreach ($items as $lastdata) {
                $damagedata = $lastdata['Inventory_damage_item']; // Use the correct model alias
                $skuid       = $damagedata['item_id'];
                $price       = $damagedata['item_price'];
                $sku_name    = $damagedata['item_name'];
                $sku_brand   = isset($damagedata['sku_brand']) ? $damagedata['sku_brand'] : ''; // if available
                $unit_name   = $damagedata['unit_name'];
                $unit_id     = $damagedata['unit_id'];
                $cat_id      = $damagedata['item_category_id'];
                $cat_name    = $damagedata['item_category_name'];
                $quantity    = $damagedata['quantity'];
            
                if (!empty($quantity)) {
                    $ledgerprevdetails = $this->Stock_ledger->find('first', [
                        'conditions' => [
                            'sku_id' => $skuid,
                            'item_type' => 'semifinished',
                            'type' => 'OUTLET',
                            'store_id' => $location_id
                        ],
                        'fields' => ['actual_balance'],
                        'order' => ['id DESC'],
                        'recursive' => -1
                    ]);
            
                    $ledger_prev_quantity = !empty($ledgerprevdetails['Stock_ledger']['actual_balance']) 
                        ? $ledgerprevdetails['Stock_ledger']['actual_balance'] 
                        : 0;
            
                    $newQuantity = $ledger_prev_quantity + $quantity;
            
                    $ledgerData = [
                        'Stock_ledger' => [
                            'store_id'        => $location_id,
                            'sku_id'          => $skuid,
                            'sku_name'        => $sku_name,
                            'sku_price'       => $price,
                            'unit_id'         => $unit_id,
                            'unit_name'       => $unit_name,
                            'record_id'       => $indentid,
                            'record_type'     => 'reverse_damage',
                            'quantity'        => $quantity,
                            'balance_stock'   => $newQuantity,
                            'actual_balance'  => $newQuantity,
                            'quantity_type'   => 'credit',
                            'item_type'       => 'semifinished',
                            'type'            => 'OUTLET',
                            'created'         => date('Y-m-d H:i:s')
                        ]
                    ];
            
                    $this->Stock_ledger->create(); // ensure a fresh entry
                    $this->Stock_ledger->save($ledgerData['Stock_ledger']);
                }
            }

    
            
            // Delete existing indent and items
            $this->Inventory_damage_item->query("UPDATE inventory_damage_items SET status=0, sync=0, local_sync=0  WHERE damage_id = '$indentid'");
            $this->Inventory_damage->query("UPDATE inventory_damage SET status=0, sync=0, local_sync=0  WHERE damage_id = '$indentid'");
    
            $amount = 0;
            $chk = false;
    
            foreach ($checkeddatas as $checkeddata) {
                $skuid = $checkeddata['id'];
                $price = $checkeddata['price'];
                $sku_name = $checkeddata['sku_name'];
                $sku_brand = $checkeddata['sku_brand'];
                $unit_name = $checkeddata['unit_name'];
                $unit_id = $checkeddata['unit_id'];
                $cat_id = $checkeddata['cat_id'];
                $cat_name = $checkeddata['cat_name'];
                $quantity = $checkeddata['sku_quantity'];
                
                if (!empty($quantity)) {
                    $itemData = array(
                        'location_id' => $location_id,
                        'location_name' => $location_name,
                        'damage_id' => $indentid,
                        'item_id' => $skuid,
                        'item_name' => $sku_name,
                        'item_category_id' => $cat_id,
                        'item_category_name' => $cat_name,
                        'unit_id' => $unit_id,
                        'unit_name' => $unit_name,
                        'item_price' => $price,
                        'quantity' => $quantity,
                        'amount' => $price*$quantity
                    );
                    
                    if ($this->Inventory_damage_item->saveAll($itemData)) {
                        
                        $ledgerprevdetails = $this->Stock_ledger->find('first', [
                            'conditions' => ['sku_id' => $skuid,'type'=>'OUTLET','item_type'=>'semifinished','store_id'=>$location_id],
                            'fields' => ['actual_balance'],
                            'order' => ['id DESC'],
                            'recursive' => -1
                        ]);
                        
                        $ledger_prev_quantity = !empty($ledgerprevdetails['Stock_ledger']['actual_balance']) 
                            ? $ledgerprevdetails['Stock_ledger']['actual_balance'] 
                            : 0;
                        
                        $newQuantity = $ledger_prev_quantity - $quantity;
                        // print_r($ledger_prev_quantity);exit;
                        $ledgerData['Stock_ledger'] = [
                                'store_id'        => $location_id,
                                'sku_id'          => $skuid,
                                'sku_name'        => $sku_name,
                                'sku_price'       => $price,
                                'unit_id'         => $unit_id,
                                'unit_name'       => $unit_name,
                                'record_id'       => $indentid,
                                'record_type'     => 'update_damage',
                                'quantity'        => $quantity,
                                'balance_stock'   => $newQuantity,
                                'actual_balance'  => $newQuantity,
                                'quantity_type'   => 'debit',
                                'type'            => 'OUTLET',
                                'item_type'       => 'semifinished',
                                'created'         => date('Y-m-d H:i:s')
                        ];
        				$this->Stock_ledger->saveAll($ledgerData['Stock_ledger']);
                        
                        $chk = true;
                    } else {
                        $chk = false;
                        $this->Flash->error(__('Damage could not be updated. Please, try again.'));
                    }
    
                    // $productamount = $quantity * $price;
                    // $amount += $productamount;
                    $amount += ($price*$quantity);
                }
            }
    
            if ($chk) {
                $indentData = array(
                    'location_id' => $location_id,
                    'location_name' => $location_name,
                    'damage_id' => $indentid,
                    'damage_date' => $damage_date,
                    'type' => 'OUTLET',
                    'item_type' => 'semifinished',
                    'amount' => $amount
                );
    
                if ($this->Inventory_damage->saveAll($indentData)) {
                    echo json_encode(['response_code' => 1, 'data' => 'Updated']);
                } else {
                    echo json_encode(['response_code' => 0, 'data' => 'Damage could not be updated. Please, try again.']);
                }
            } else {
                echo json_encode(['response_code' => 0, 'data' => 'Damage items could not be updated. Please, try again.']);
            }
        }
    }
    
    public function auditstock()
    {
        if (!$this->Session->check('Auth.Location.id')) {
            return $this->redirect(['controller' => 'dashboard', 'action' => 'login']);
        }
    
        $this->layout = "users";
    
        // Default date range (last 30 days)
        $startDate = date('m/d/Y', strtotime('-30 days'));
        $endDate = date('m/d/Y');
        
        // If form submitted, get date range
        if($this->request->is('post', 'put'))
        {
			$daterange= $this->request->data['daterange'];
            $dates = explode(" - ", $daterange);
            $start_date = trim($dates[0]);
            $startDate = date('m/d/Y', strtotime($start_date));
            $end_date = trim($dates[1]);
            $endDate = date('m/d/Y', strtotime($end_date));
            $startdate= date('Y-m-d', strtotime($start_date));
            $enddate= date('Y-m-d', strtotime($end_date));
        }else{
            $startdate= date('Y-m-d', strtotime($startDate));
            $enddate= date('Y-m-d', strtotime($endDate));
        } 
    
        // Auth User Info
        $authUser = $this->Session->read('Auth.Location');
        $storeId = $authUser['id'] ?? 0;
        
        $sdate=$startdate.' 00:00:01';
        $edate=$enddate.' 23:59:59';
       
       
        $preparedStocks = $this->Audit_stock->find('all', [
            'conditions' => [
                'Audit_stock.status' => 1,
                'Audit_stock.updateon BETWEEN ? AND ?' => [$sdate, $edate],
                'Audit_stock.item_type' => 'SKU',
                'Audit_stock.type' => 'Outlet',
                'Audit_stock.store_id' => $storeId
            ],
            'fields' => ['Audit_stock.*'], // Ensure ID is included
            'order' => ['Audit_stock.id' => 'DESC'],
            'recursive' => 1
        ]);
        

    
        // Pass to view
        $daterangeFormatted = $startDate . ' - ' . $endDate;
        $this->set(compact('daterangeFormatted', 'preparedStocks'));
    }

    public function create_audit_stock()
    {
        if (!$this->Session->check('Auth.Location.id')) {
            return $this->redirect(['controller' => 'dashboard', 'action' => 'login']);
        }
    
        $this->layout = "users";
    
        $today = date("Y-m-d");
        $currentTime = date("H:i");
        $this->set(compact('today', 'currentTime'));
    
        // Load SKU list
        $skus = $this->Inventory_sku->query("SELECT * FROM inventory_skus WHERE status = 1 ORDER BY name ASC");
        $this->set(compact('skus'));
    }
    
    public function add_audit_stock()
    {
        
        $data = $this->request->data;
        
        $remarks = trim($data['remarks']);
        $auditDate = $data['audit_date'] ?? $today;
        $auditTime = $data['audit_time'] ?? $currentTime;
    
        $createdTime = date('Y-m-d H:i:s');
            
        $authUser = $this->Session->read('Auth.Location');
        $storeId = $authUser['id'] ?? 0;
        
        $loc = $this->Location->find('first', [
            'conditions' => ['Location.id' => $storeId],
            'fields' => ['Location.name'],
            'recursive' => -1
        ]);
        $store_name = $loc['Location']['name'] ?? '';
    
        // ✅ Generate POS-Audit ID
        do {
            $pos_audit_id = substr(preg_replace('/\D/', '', sha1(uniqid())), 0, 12);
            if ($pos_audit_id[0] === '0') {
                $pos_audit_id[0] = rand(1,9);
            }
            $existsMain = $this->Audit_stock->find('count', [
                'conditions' => ['Audit_stock.pos_audit_id' => $pos_audit_id]
            ]);
        } while ($existsMain > 0);
    
        $auditto = $auditDate . ' ' . $auditTime . ':00';
        $currentServerTime = date('Y-m-d H:i:s');
    
        if (strtotime($auditto) < strtotime($currentServerTime)) {
            $auditto = $currentServerTime;
        }
    
        // ✅ Read selected items (from AJAX)
        $checkedData = json_decode($data['checkedData'], true);
    
        if (empty($checkedData)) {
            $this->response->body(json_encode(['status' => 'error', 'message' => 'No Items']));
            return $this->response;
        }
    
        // ✅ Save Header
        $header = [
            'pos_audit_id' => $pos_audit_id,
            'store_id' => $storeId,
            'store_name' => $store_name,
            'type' => 'Outlet',
            'remarks' => $remarks,
            'status' => 1,
            'select_all' => 0,
            'updateon' => $createdTime
        ];
        $this->Audit_stock->create();
        $this->Audit_stock->save($header);
    
        // ✅ Process each SKU
        foreach ($checkedData as $item) {
    
            $skuId = (int)$item['id'];
            $qty = floatval($item['quantity']);
            if ($qty <= 0) continue;
    
            $skuName = $item['sku_name'];
            $unitId = (int)$item['unit_id'];
            $unitName = $item['unit_name'];
            $sku_price = floatval($item['price']);
    
            // ✅ LAST AUDIT DATA
            $lastAudit = $this->Audit_stock_items->find('first', [
                'conditions' => [
                    'sku_id' => $skuId,
                    'item_type' => 'sku',
                    'type' => 'Outlet',
                    'status' => 1,
                    'store_id' => $storeId
                ],
                'order' => ['id DESC'],
                'fields' => ['physicalstock', 'auditto'],
                'recursive' => -1
            ]);
    
            $openingstock = !empty($lastAudit) ? floatval($lastAudit['Audit_stock_items']['physicalstock']) : 0;
            $auditfrom = !empty($lastAudit) ? $lastAudit['Audit_stock_items']['auditto'] : '2022-01-01 00:00:01';
    
            // ✅ GET purchase, consumption, damage
            $fetch_variance = "
                SELECT 
                    SUM(CASE WHEN quantity_type = 'credit' THEN quantity ELSE 0 END) AS purchase,
                    SUM(CASE WHEN record_type IN ('stockout','stockout_modifier','process_indent','stock_transfer_to_kitchen') THEN quantity ELSE 0 END) AS consumption,
                    (
                        SUM(CASE WHEN record_type IN ('damage','update_damage') AND quantity_type = 'debit' THEN quantity ELSE 0 END)
                        -
                        SUM(CASE WHEN record_type = 'reverse_damage' AND quantity_type = 'credit' THEN quantity ELSE 0 END)
                    ) AS damage
                FROM stock_ledger
                WHERE store_id = '$storeId'
                  AND item_type = 'SKU'
                  AND sku_id = $skuId
                  AND type = 'Outlet'
                  AND created BETWEEN '$auditfrom' AND '$auditto'
            ";
    
            $res = $this->Stock_ledger->query($fetch_variance);
            $purchase = floatval($res[0][0]['purchase'] ?? 0);
            $consumption = floatval($res[0][0]['consumption'] ?? 0);
            $damage = floatval($res[0][0]['damage'] ?? 0);
    
            $variance = round($qty - $openingstock - $purchase + $consumption + $damage, 2);
            $closingstock = round($openingstock + $purchase - $consumption - $damage, 2);
    
            // ✅ SAVE ITEM
            $auditItem = [
                'sku_id' => $skuId,
                'pos_audit_id' => $pos_audit_id,
                'store_id' => $storeId,
                'sku_name' => $skuName,
                'sku_unit' => $unitId,
                'sku_unit_name' => $unitName,
                'sku_price' => $sku_price,
                'openingstock' => $openingstock,
                'closingstock' => $closingstock,
                'physicalstock' => $qty,
                'variance' => $variance,
                'purchased' => $purchase,
                'consumption' => $consumption,
                'damage' => $damage,
                'auditfrom' => $auditfrom,
                'auditto' => $auditto,
                'remarks' => $remarks,
                'status' => 1,
                'type' => 'Outlet',
                'created' => $createdTime,
                'modified' => $createdTime
            ];
    
            $this->Audit_stock_items->create();
            $this->Audit_stock_items->save($auditItem);
    
            // ✅ INSERT Ledger entry (audit entry)
            $this->Stock_ledger->create();
            $this->Stock_ledger->save([
                'store_id' => $storeId,
                'sku_id' => $skuId,
                'sku_name' => $skuName,
                'sku_price' => $sku_price,
                'unit_id' => $unitId,
                'unit_name' => $unitName,
                'record_id' => $pos_audit_id,
                'record_type' => 'audit_stock',
                'quantity' => 0,
                'balance_stock' => $closingstock,
                'variance' => $variance,
                'actual_balance' => $qty,
                'variance_type' => 'audit',
                'variance_id' => $pos_audit_id,
                'type' => 'Outlet',
                'quantity_type' => 'audit',
                'created' => $auditto,
                'modified' => $createdTime
            ]);
        }
    
        echo json_encode(['status' => 'success']);
        exit;
        
    }
    
    public function view_audit()
	{
	    if (!$this->Session->check('Auth.Location.id')) {
            return $this->redirect(['controller' => 'dashboard', 'action' => 'login']);
        }
    
	    $this->layout = "users";
		$id=$_REQUEST['id'];
    
        $indents = $this->Audit_stock->query("SELECT * FROM pos_auditstock where pos_audit_id=$id");
        $this->set(compact('indents'));
        
		$items = $this->Audit_stock_items->query("SELECT * FROM pos_auditstock_items where pos_audit_id=$id and status=1");
        
        $this->set(compact('items'));
        
        
	}
	
	public function semi_auditstock()
    {
        if (!$this->Session->check('Auth.Location.id')) {
            return $this->redirect(['controller' => 'dashboard', 'action' => 'login']);
        }
    
        $this->layout = "users";
    
        // Default date range (last 30 days)
        $startDate = date('m/d/Y', strtotime('-30 days'));
        $endDate = date('m/d/Y');
        
        // If form submitted, get date range
        if($this->request->is('post', 'put'))
        {
			$daterange= $this->request->data['daterange'];
            $dates = explode(" - ", $daterange);
            $start_date = trim($dates[0]);
            $startDate = date('m/d/Y', strtotime($start_date));
            $end_date = trim($dates[1]);
            $endDate = date('m/d/Y', strtotime($end_date));
            $startdate= date('Y-m-d', strtotime($start_date));
            $enddate= date('Y-m-d', strtotime($end_date));
        }else{
            $startdate= date('Y-m-d', strtotime($startDate));
            $enddate= date('Y-m-d', strtotime($endDate));
        } 
    
        $sdate=$startdate.' 00:00:01';
        $edate=$enddate.' 23:59:59';
       
        $preparedStocks = $this->Audit_stock->find('all', [
            'conditions' => [
                'Audit_stock.status' => 1,
                'Audit_stock.updateon BETWEEN ? AND ?' => [$sdate, $edate],
                'Audit_stock.item_type' => 'Semifinished',
                'Audit_stock.type' => 'Outlet'
            ],
            'fields' => ['Audit_stock.*'], // Ensure ID is included
            'order' => ['Audit_stock.id' => 'DESC'],
            'recursive' => 1
        ]);

        // Pass to view
        $daterangeFormatted = $startDate . ' - ' . $endDate;
        $this->set(compact('daterangeFormatted', 'preparedStocks'));
    }

    public function semi_create_audit_stock()
    {
        if (!$this->Session->check('Auth.Location.id')) {
            return $this->redirect(['controller' => 'dashboard', 'action' => 'login']);
        }
    
        $this->layout = "users";
    
        $today = date("Y-m-d");
        $currentTime = date("H:i");
        $this->set(compact('today', 'currentTime'));
    
        // Load SKU list
        $skus = $this->Inventory_semifinished->query("SELECT * FROM inventory_semifinished WHERE status = 1 ORDER BY name ASC");
        $this->set(compact('skus'));
    
    }
    
    public function add_semi_auditstock()
    {
        $data = $this->request->data;
    
        $remarks = trim($data['remarks'] ?? '');
        $auditDate = $data['audit_date'] ?? $today;
        $auditTime = $data['audit_time'] ?? $currentTime;
        $createdTime = date('Y-m-d H:i:s');
    
        $authUser = $this->Session->read('Auth.Location');
        $storeId = $authUser['id'] ?? 0;
        $store_name = $authUser['name'] ?? '';
    
        // Generate unique 12 digit pos_audit_id
        do {
            $pos_audit_id = substr(preg_replace('/\D/', '', sha1(uniqid() . rand())), 0, 12);
            if ($pos_audit_id[0] == '0') {
                $pos_audit_id[0] = rand(1,9);
            }
            $existsMain = $this->Audit_stock->find('count', [
                'conditions' => ['Audit_stock.pos_audit_id' => $pos_audit_id],
                'recursive' => -1
            ]);
        } while ($existsMain > 0);
    
        $header = [
            'pos_audit_id' => $pos_audit_id,
            'store_id' => $storeId,
            'store_name' => $store_name,
            'remarks' => $remarks,
            'status' => 1,
            'item_type' => 'semifinished',
            'type' => 'Outlet',
            'select_all' => 0,
            'updateon' => $createdTime
        ];
    
        // ---------- NEW CHANGE HERE ----------
        $checkedData = json_decode($data['checkedData'], true); // <-- Read JSON
        // --------------------------------------
    
        $auditto = $auditDate . ' ' . $auditTime . ':00';
        $nowServer = date('Y-m-d H:i:s');
        if (strtotime($auditto) < strtotime($nowServer)) {
            $auditto = $nowServer;
        }
    
        foreach ($checkedData as $item) {
    
            $skuId = $item['id'];
            $qty = floatval($item['quantity']);
            $skuName = $item['sku_name'];
            $unitName = $item['unit_name'];
            $unitId = (int)$item['unit_id'];
            $sku_price = $item['price'];
    
            // Get last audit
            $lastAudit = $this->Audit_stock_items->find('first', [
                'conditions' => [
                    'sku_id' => $skuId,'item_type' => 'Semifinished','type' => 'Outlet','status' => 1,'store_id' => $storeId
                ],
                'order' => ['id DESC'],
                'recursive' => -1
            ]);
    
            $openingstock = 0;
            $auditfrom = '2022-01-01 00:00:01';
            if (!empty($lastAudit)) {
                $openingstock = floatval($lastAudit['Audit_stock_items']['physicalstock']);
                $auditfrom = $lastAudit['Audit_stock_items']['auditto'];
            }
    
            $ledgerQuery = "
                SELECT 
                    SUM(CASE WHEN quantity_type='credit' THEN quantity ELSE 0 END) AS purchase,
                    SUM(CASE WHEN record_type IN ('stockout','stockout_modifier','process_indent','stock_transfer_to_kitchen') THEN quantity ELSE 0 END) AS consumption,
                    (
                        SUM(CASE WHEN record_type IN ('damage','update_damage') AND quantity_type='debit' THEN quantity ELSE 0 END)
                        -
                        SUM(CASE WHEN record_type='reverse_damage' AND quantity_type='credit' THEN quantity ELSE 0 END)
                    ) AS damage
                FROM stock_ledger
                WHERE store_id='$storeId' AND item_type='Semifinished' AND sku_id=$skuId 
                AND type='Outlet' AND created BETWEEN '$auditfrom' AND '$auditto'
            ";
    
            $var = $this->Stock_ledger->query($ledgerQuery)[0][0];
            $purchase = floatval($var['purchase'] ?? 0);
            $consumption = floatval($var['consumption'] ?? 0);
            $damage = floatval($var['damage'] ?? 0);
    
            $variance = round($qty - $openingstock - $purchase + $consumption + $damage, 2);
            $closingstock = round($openingstock + $purchase - $consumption - $damage, 2);
    
            $this->Audit_stock_items->create();
            $this->Audit_stock_items->save([
                'sku_id' => $skuId,
                'pos_audit_id' => $pos_audit_id,
                'store_id' => $storeId,
                'sku_name' => $skuName,
                'item_type' => 'Semifinished',
                'type' => 'Outlet',
                'sku_unit' => $unitId,
                'sku_unit_name' => $unitName,
                'sku_price' => $sku_price,
                'openingstock' => $openingstock,
                'closingstock' => $closingstock,
                'physicalstock' => $qty,
                'variance' => $variance,
                'purchased' => $purchase,
                'consumption' => $consumption,
                'damage' => $damage,
                'auditfrom' => $auditfrom,
                'auditto' => $auditto,
                'remarks' => $remarks,
                'status' => 1,
                'created' => $createdTime,
                'modified' => $createdTime
            ]);
    
            // Insert audit ledger
            $this->Stock_ledger->create();
            $this->Stock_ledger->save([
                'store_id' => $storeId,
                'sku_id' => $skuId,
                'sku_name' => $skuName,
                'sku_price' => $sku_price,
                'unit_id' => $unitId,
                'unit_name' => $unitName,
                'record_id' => $pos_audit_id,
                'record_type' => 'audit_stock',
                'quantity' => 0,
                'balance_stock' => 0,
                'variance' => $variance,
                'actual_balance' => $qty,
                'variance_type' => 'audit',
                'variance_id' => $pos_audit_id,
                'item_type' => 'Semifinished',
                'type' => 'Outlet',
                'quantity_type' => 'audit',
                'created' => $auditto,
                'modified' => $createdTime
            ]);
        }
    
        // Save header at last
        $this->Audit_stock->create();
        if ($this->Audit_stock->save($header)) {
            echo "success";
            exit;
        } else {
            echo "failed";
            exit;
        }

    }
    
    public function semi_view_audit()
	{
	    if (!$this->Session->check('Auth.Location.id')) {
            return $this->redirect(['controller' => 'dashboard', 'action' => 'login']);
        }
    
	    $this->layout = "users";
		$id=$_REQUEST['id'];
    
        $indents = $this->Audit_stock->query("SELECT * FROM pos_auditstock where pos_audit_id=$id");
        $this->set(compact('indents'));
        
		$items = $this->Audit_stock_items->query("SELECT * FROM pos_auditstock_items where pos_audit_id=$id and status=1");
        
        $this->set(compact('items'));
        
        
	}
	
    //purchase cash
	public function direct_purchase()
    {
        if (!$this->Session->check('Auth.User.id')) {
            return $this->redirect(['controller' => 'dashboard', 'action' => 'login']);
        }

        $this->layout = "users";

        // --- Default date range: last 1 month
        $startDate = date('m/d/Y', strtotime('-1 month'));
        $endDate   = date('m/d/Y');
        $startdate = date('Y-m-d', strtotime('-1 month'));
        $enddate   = date('Y-m-d');

        // --- Handle date range from form
        if ($this->request->is(['post', 'put'])) {
            $daterange = $this->request->data['daterange'] ?? '';
            if (strpos($daterange, '-') !== false) {
                list($startRaw, $endRaw) = explode(" - ", $daterange);
                $startDate = trim($startRaw);
                $endDate   = trim($endRaw);
                $startdate = date('Y-m-d', strtotime($startDate));
                $enddate   = date('Y-m-d', strtotime($endDate));
            }
        }

        $authUser = $this->Session->read('Auth.User');
        $location_id = $authUser['id']; // Prefer using location_id, not user id
        
        // --- Build query conditions for internal indent
        $indentConditions = [
            'direct_purchase' => 1,
            'location_id' => $location_id,
            'status' => 4,
            "created >=" => $startdate . " 00:00:01",
            "created <=" => $enddate . " 23:59:59",
        ];

        // --- Fetch internal indent records
        $users = $this->Inventory_purchase_order->find('all', [
            'conditions' => $indentConditions,
            'order' => ['id' => 'DESC']
        ]);

        // --- Pass variables to view
        $this->set(compact('users'));
        $this->set('startdate', $startDate . ' - ' . $endDate);
    }

    
    public function create_direct_purchase()
    {
        // 🔐 Authentication check
        if (!$this->Session->check('Auth.User.id') || $this->Session->read('Auth.User.role') == 'admin') {
            return $this->redirect(['controller' => 'dashboard', 'action' => 'login']);
        }
    
        $this->layout = "users";
        $authUser = $this->Session->read('Auth.User');
    
        // ✅ Use location_id (or assign correctly)
        $location_id = $authUser['id']; // Prefer using location_id, not user id
    
        // ✅ Get vendors for this area
        $vendors = $this->Inventory_vendor->query("
            SELECT id, company 
            FROM inventory_vendors 
            WHERE status = 1 
              AND id IN (
                  SELECT vendor_id 
                  FROM inventory_vendor_service_areas 
                  WHERE area_id = $location_id
              )
            ORDER BY name ASC
        ");
    
        // ✅ Convert raw query result into CakePHP list (id => name)
        $vendoroptions = [];
        foreach ($vendors as $v) {
            $vendoroptions[$v['inventory_vendors']['id']] = $v['inventory_vendors']['company'];
        }
    
        // ✅ Fetch SKUs
        // $users = $this->Inventory_sku->find('all', [
        //     'conditions' => ['Inventory_sku.status' => 1],
        //     'order' => ['Inventory_sku.name' => 'DESC'],
        //     'recursive' => -1
        // ]);

        
        $users = $this->Inventory_sku->find('all', [
            'fields' => [
                'Inventory_sku.*',
                'StockLedger.actual_balance'
            ],
            'joins' => [
                [
                    'table' => 'stock_ledger',
                    'alias' => 'StockLedger',
                    'type' => 'LEFT',
                    'conditions' => [
                        'StockLedger.sku_id = Inventory_sku.id',
                        'StockLedger.store_id' => $location_id,
                        'StockLedger.type' => 'OUTLET',
                        'StockLedger.id = (
                            SELECT MAX(sl2.id)
                            FROM stock_ledger sl2
                            WHERE sl2.sku_id = Inventory_sku.id
                            AND sl2.store_id = ' . $location_id . '
                            AND sl2.type = "OUTLET"
                        )'
                    ]
                ]
            ],
            'conditions' => ['Inventory_sku.status' => 1],
            'order' => ['Inventory_sku.name ASC']
        ]);
    
        // ✅ Pass data to view
        $this->set(compact('vendoroptions', 'users'));
    }

    public function add_direct_purchase() 
    {
        $this->autoRender = false;
    
        // ✅ Check session
        if (!$this->Session->check('Auth.User.id')) {
            echo json_encode(['response_code' => 0, 'data' => 'User session expired.']);
            return;
        }
    
        $id = $this->Session->read('Auth.User.id');
        $name = $this->Session->read('Auth.User.name');
        $today = date('Y-m-d H:i:s');
    
        if (isset($_POST['checkedData']) && isset($_POST['vendor_id'])) {
            $checkeddatas = json_decode($_POST['checkedData'], true);
            $vendorId = $_POST['vendor_id'];
    
            // ✅ Fetch vendor info
            $vendor = $this->Inventory_vendor->find('first', [
                'conditions' => ['Inventory_vendor.id' => $vendorId],
                'fields' => ['id', 'company'],
                'recursive' => -1
            ]);
    
            if (empty($vendor)) {
                echo json_encode(['response_code' => 0, 'data' => 'Invalid vendor selected.']);
                return;
            }
    
            // ✅ Generate PO ID dynamically for the year (e.g. 2025000001)
            $year = date('Y');
            $lastPo = $this->Inventory_purchase_order->find('first', [
                'conditions' => ['p_o_id LIKE' => $year . '%'],
                'fields' => ['p_o_id'],
                'order' => ['p_o_id DESC'],
                'recursive' => -1
            ]);
    
            if (!empty($lastPo)) {
                $lastNumber = (int)substr($lastPo['Inventory_purchase_order']['p_o_id'], 4);
                $newNumber = str_pad($lastNumber + 1, 6, '0', STR_PAD_LEFT);
            } else {
                $newNumber = '000001';
            }
    
            $poId = $year . $newNumber;
    
            $amount = 0;
            $itemsSaved = true;
    
            // ✅ Loop through items
            foreach ($checkeddatas as $item) {
                $skuid = $item['id'];
                $price = $item['price'];
                $quantity = $item['sku_quantity'];
    
                // ✅ Calculate total tax %
                $product_taxes = $this->Inventory_sku_tax->query(
                    "SELECT SUM(tax_percentage) AS total_tax FROM inventory_sku_tax WHERE product_id = '$skuid' and status=1"
                );
                $total_tax = round($product_taxes[0][0]['total_tax'], 2);
                $tax_value = round(($price * $quantity) * ($total_tax / 100), 2);
                $skuamount = round(($price * $quantity) + $tax_value, 2);
    
                // ✅ Generate unique stockin_id
                do {
                    $txnid = rand(1, 9);
                    for ($i = 0; $i < 10; $i++) {
                        $txnid .= rand(0, 9);
                    }
    
                    $existing = $this->Inventory_purchase_order->find('first', [
                        'conditions' => ['stockin_id' => $txnid],
                        'recursive' => -1
                    ]);
                } while ($existing);
    
                // ✅ Prepare item data
                $itemData = [
                    'Inventory_purchase_order_item' => [
                        'p_o_id' => $poId,
                        'internal_indent_id' => 0,
                        'sku_id' => $skuid,
                        'sku_name' => $item['sku_name'],
                        'sku_unit' => $item['unit_id'],
                        'sku_unit_name' => $item['unit_name'],
                        'sku_price' => $price,
                        'sku_quantity' => $quantity,
                        'sku_total_tax_percentage' => $total_tax,
                        'sku_total_tax_value' => $tax_value,
                        'sku_amount' => $skuamount,
                        'approved_quantity' => $quantity,
                        'approved_total_tax_value' => $tax_value,
                        'approved_amount' => $skuamount,
                        'processed_quantity' => $quantity,
                        'processed_total_tax_value' => $tax_value,
                        'processed_amount' => $skuamount,
                        'stockin_quantity' => $quantity,
                        'stockin_total_tax_value' => $tax_value,
                        'stockin_amount' => $skuamount,
                        'status' => 4
                    ]
                ];
    
                if (!$this->Inventory_purchase_order_item->saveAll($itemData)) {
                    $itemsSaved = false;
                    break;
                }else{
                    
                    $ledgerprevdetails = $this->Stock_ledger->find('first', [
                        'conditions' => ['sku_id' => $skuid,'type'=>'OUTLET','item_type'=>'sku','store_id'=>$id],
                        'fields' => ['actual_balance'],
                        'order' => ['id DESC'],
                        'recursive' => -1
                    ]);
                    
                    $ledger_prev_quantity = !empty($ledgerprevdetails['Stock_ledger']['actual_balance']) 
                        ? $ledgerprevdetails['Stock_ledger']['actual_balance'] 
                        : 0;
                    
                    $newQuantity = $quantity + $ledger_prev_quantity;
                    
                    $ledgerData['Stock_ledger'] = [
                            'store_id'        => $id,
                            'sku_id'          => $skuid,
                            'sku_name'        => $item['sku_name'],
                            'sku_price'       => $price,
                            'unit_id'         => $item['unit_id'],
                            'unit_name'       => $item['unit_name'],
                            'record_id'       => $txnid,
                            'record_type'     => 'stockin',
                            'quantity'        => $quantity,
                            'balance_stock'   => $newQuantity,
                            'actual_balance'  => $newQuantity,
                            'quantity_type'   => 'credit',
                            'type'            => 'OUTLET',
                            'item_type'       => 'sku',
                            'created'         => date('Y-m-d H:i:s')
                    ];
                    
                    if(!$this->Stock_ledger->saveAll($ledgerData['Stock_ledger'])){
    				    echo json_encode(['response_code' => 0, 'data' => 'Failed to save ledger record.']);
    				}
    			}
    
                $amount += $skuamount;
            }
    
            // ✅ Save PO master record
            if ($itemsSaved) {
                $indentData = [
                    'p_o_id' => $poId,
                    'vendor_id' => $vendorId,
                    'vendor_name' => $vendor['Inventory_vendor']['company'],
                    'internal_indent_id' => 0,
                    'location_id' => $id,
                    'location_name' => $name,
                    'total_amount' => $amount,
                    'status' => 4,
                    'approved_by_id' => $id,
                    'approved_by_name' => $name,
                    'approved_on' => $today,
                    'approved_amount' => $amount,
                    'processed_by_id' => $id,
                    'processed_by_name' => $name,
                    'processed_on' => $today,
                    'processed_amount' => $amount,
                    'stockin_id' => $txnid,
                    'stockin_on' => $today,
                    'stockin_by_id' => $id,
                    'stockin_by_name' => $name,
                    'stockin_amount' => $amount,
                    'direct_purchase' => 1,
                    'invoice_no' => '0'
                ];
    
                $this->Inventory_purchase_order->create();
                if ($this->Inventory_purchase_order->save($indentData)) {
                    echo json_encode(['response_code' => 1, 'data' => 'Saved successfully.']);
                } else {
                    echo json_encode(['response_code' => 0, 'data' => 'Failed to save master record.']);
                }
            } else {
                echo json_encode(['response_code' => 0, 'data' => 'Failed to save one or more items.']);
            }
    
        } else {
            echo json_encode(['response_code' => 0, 'data' => 'Invalid request. Missing data.']);
        }
    }

    public function view_direct_purchase()
	{
	    if(!$this->Session->check('Auth.User.id')){
			$this->redirect(array('controller'=>'dashboard','action' => 'login'));		
		}
        
	    $this->layout = "users";
		$id=$_REQUEST['id'];
    
        $indents = $this->Inventory_purchase_order->query("SELECT * FROM inventory_purchase_order where p_o_id=$id");
        $this->set(compact('indents'));
        
		$items = $this->Inventory_purchase_order_item->query("SELECT * FROM inventory_purchase_order_items where p_o_id=$id and status!=0");
        
        $this->set(compact('items'));
    }
    
    public function semi_auditexport()
    {
        if (!$this->Session->check('Auth.Location.id')) {
            return $this->redirect(['controller' => 'dashboard', 'action' => 'login']);
        }
    
        $id = $_REQUEST['id'] ?? 0;
        if (empty($id)) {
            throw new BadRequestException('Invalid Audit ID.');
        }
    
        // 🔹 Fetch audit stock main info
        $indents = $this->Audit_stock->query("SELECT * FROM pos_auditstock WHERE pos_audit_id = $id");
    
        if (empty($indents)) {
            $this->Session->setFlash('No audit found for this record.', 'flash_error');
            return $this->redirect(['action' => 'semi_view_audit']);
        }
    
        $audit = $indents[0]['pos_auditstock'];
    
        // 🔹 Fetch audit stock items
        $items = $this->Audit_stock_items->query("
            SELECT * 
            FROM pos_auditstock_items 
            WHERE pos_audit_id = $id 
              AND status = 1 order by sku_name ASC
        ");
    
        if (empty($items)) {
            $this->Session->setFlash('No audit items found for this record.', 'flash_error');
            return $this->redirect(['action' => 'semi_view_audit', $id]);
        }
    
        // 🔹 Load PHPExcel
        App::import('Vendor', 'PHPExcel/PHPExcel');
        error_reporting(0);
        ini_set('display_errors', TRUE);
        ini_set('display_startup_errors', TRUE);
    
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->getProperties()
            ->setCreator("Aether")
            ->setLastModifiedBy("Aether")
            ->setTitle("Audit Stock Report")
            ->setSubject("Audit Stock Report")
            ->setDescription("Exported semi-finished audit data.")
            ->setKeywords("audit stock excel")
            ->setCategory("Export File");
    
        $sheet = $objPHPExcel->setActiveSheetIndex(0);
    
        // 🔹 Compact header info (no big text)
        $sheet->setCellValue('A1', 'Audit ID:')
              ->setCellValueExplicit('B1', (string)$audit['pos_audit_id'], PHPExcel_Cell_DataType::TYPE_STRING)
              ->setCellValue('C1', 'Store Name:')
              ->setCellValue('D1', $audit['store_name']);
    
        $sheet->setCellValue('A2', 'Remarks:')
              ->setCellValue('B2', $audit['remarks'])
              ->setCellValue('C2', 'Audit Date:')
              ->setCellValue('D2', $audit['updateon']);
    
        // Apply light formatting
        $sheet->getStyle('A1:D2')->getFont()->setBold(true)->setSize(10);
        $sheet->getStyle('A1:D2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
    
        // 🔹 Column headers (starting at row 4)
        $headers = [
            'Sr No.', 'SKU Name', 'Unit', 'Price',
             'Physical Stock','Opening Stock',
             'Purchase','Consumption','Damage',
            'Closing Stock', 'Variance', 'Amount', 'Percentage',
            'Audit From', 'Audit To'
        ];
    
        $col = 'A';
        foreach ($headers as $header) {
            $sheet->setCellValue($col . '4', $header);
            $sheet->getStyle($col . '4')->getFont()->setBold(true);
            $sheet->getStyle($col . '4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $col++;
        }
    
        // 🔹 Fill data
        $row = 5;
        $count = 1;
        foreach ($items as $itm) {
            $data = $itm['pos_auditstock_items'];
            $percentage = 0;
            
            if ((float)$data['openingstock'] > 0) {
                $percentage = round(((float)$data['variance'] / (float)$data['openingstock']) * 100, 2);
            }
            
            $sheet->setCellValue("A$row", $count)
                  ->setCellValue("B$row", $data['sku_name'])
                  ->setCellValue("C$row", $data['sku_unit_name'])
                  ->setCellValue("D$row", $data['sku_price'])
                  ->setCellValue("E$row", (float)$data['physicalstock'])
                  ->setCellValue("F$row", (float)$data['openingstock'])
                  ->setCellValue("G$row", $data['purchased'])
                  ->setCellValue("H$row", $data['consumption'])
                  ->setCellValue("I$row", $data['damage'])
                  ->setCellValue("J$row", $data['closingstock'])
                  ->setCellValue("K$row", (float)$data['variance'])
                  ->setCellValue("L$row", (float)$data['variance'] * $data['sku_price'])
                  ->setCellValue("M$row", $percentage . '%')
                  ->setCellValue("N$row", $data['auditfrom'])
                  ->setCellValue("O$row", $data['auditto']);
                  
            $row++;
            $count++;
        }
    
        // 🔹 Auto size all columns
        foreach (range('A', 'H') as $columnID) {
            $sheet->getColumnDimension($columnID)->setAutoSize(true);
        }
    
        // 🔹 Sheet name
        $sheet->setTitle('Audit Report');
        $objPHPExcel->setActiveSheetIndex(0);
    
        // 🔹 Output headers for download
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="Semi_Finished_Audit_Report_' . $id . '.xls"');
        header('Cache-Control: max-age=0');
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
        header('Cache-Control: cache, must-revalidate');
        header('Pragma: public');
    
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }

	
	public function imprest()
    {
        if (!$this->Session->check('Auth.User.id')) {
            return $this->redirect(['controller' => 'dashboard', 'action' => 'login']);
        }

        $this->layout = "users";

        // --- Default date range: last 1 month
        $startDate = date('m/d/Y', strtotime('-1 month'));
        $endDate   = date('m/d/Y');
        $startdate = date('Y-m-d', strtotime('-1 month'));
        $enddate   = date('Y-m-d');

        // --- Handle date range from form
        if ($this->request->is(['post', 'put'])) {
            $daterange = $this->request->data['daterange'] ?? '';
            if (strpos($daterange, '-') !== false) {
                list($startRaw, $endRaw) = explode(" - ", $daterange);
                $startDate = trim($startRaw);
                $endDate   = trim($endRaw);
                $startdate = date('Y-m-d', strtotime($startDate));
                $enddate   = date('Y-m-d', strtotime($endDate));
            }
        }

        // --- Session values
        $authUser = $this->Session->read('Auth.User');
        $location_id = $authUser['id']; // Prefer using location_id, not user id
        
        // --- Build query conditions for internal indent
        $indentConditions = [
            'location_id' => $location_id,
            "created >=" => $startdate . " 00:00:01",
            "created <=" => $enddate . " 23:59:59",
        ];

        // --- Fetch internal indent records
        $users = $this->Account_imprest->find('all', [
            'conditions' => $indentConditions,
            'order' => ['id' => 'DESC']
        ]);

        // --- Pass variables to view
        $this->set(compact('users'));
        $this->set('startdate', $startDate . ' - ' . $endDate);
    }

	public function add_imprest() 
    {
        $this->autoRender = false;
    
        if (!$this->Session->check('Auth.User.id')) {
            echo json_encode(['response_code' => 0, 'data' => 'User session expired.']);
            return;
        }
    
        $id = $this->Session->read('Auth.User.id');
        $name = $this->Session->read('Auth.User.name');
        
        if (isset($_POST['amount'])) {
            $amount = $_POST['amount'];
            $description=$_POST['description'];
            
            // Validate location
            $location = $this->Location->find('first', [
                'conditions' => ['Location.id' => $id],
                'fields' => ['id', 'name'],
                'recursive' => -1
            ]);
    
            if (empty($location)) {
                echo json_encode(['response_code' => 0, 'data' => 'Invalid location selected.']);
                return;
            }
    
            $skuData = [
                'location_id' => $id,
                'location_name' => $name,
                'amount' => $amount,
                'description' => $description
            ];

            $this->Account_imprest->create(); // ensure new row
            if ($this->Account_imprest->save($skuData)) {
                echo json_encode(['response_code' => 1, 'data' => 'saved']);
            } else {
                echo json_encode(['response_code' => 0, 'data' => 'Failed to save data.']);
            }
        } else {
            echo json_encode(['response_code' => 0, 'data' => 'Invalid request. Missing data.']);
        }
    }
    
    public function get_imprest()
    {
        $this->autoRender = false;
        $id = $this->request->query('id');
        $data = $this->Account_imprest->findById($id);
        echo json_encode(['status' => !empty($data), 'data' => $data['Account_imprest'] ?? []]);
    }
    
    public function update_imprest()
    {
        $this->autoRender = false;
    
        // Get raw POST data
        $data = $this->request->data;
    
        // Wrap in model key if not already
        if (!isset($data['Account_imprest'])) {
            $data = ['Account_imprest' => $data];
        }
    
        // Add sync flags
        $data['Account_imprest']['sync'] = 0;
        $data['Account_imprest']['local_sync'] = 0;
    
        // Set ID for update
        $this->Account_imprest->id = $data['Account_imprest']['id'];
    
        // Save record
        $saved = $this->Account_imprest->save($data);
    
        echo json_encode(['status' => (bool)$saved]);
    }

    
    public function view_imprest()
    {
        $this->autoRender = false;
        $id = $this->request->query('id');
        $data = $this->Account_imprest->findById($id);
        echo json_encode(['status' => !empty($data), 'data' => $data['Account_imprest'] ?? []]);
    }
    
    public function imprest_change_status() 
    {
        $this->autoRender = false; 
    
        if ($this->request->is('post')) {
            $indentId = $this->request->data['_id']; 
            $currentStatus = $this->Account_imprest->field('status', ['id' => $indentId]);
            $newStatus = $currentStatus == 1 ? 0 : 1;
    
            $updated = $this->Account_imprest->updateAll(
                [
                    'Account_imprest.status' => $newStatus,
                    'Account_imprest.sync' => 0,
                    'Account_imprest.local_sync' => 0
                ],
                ['Account_imprest.id' => $indentId]
            );
    
            if ($updated) {
                echo json_encode(['status' => true]);
            } else {
                echo json_encode(['status' => false]);
            }
        }
    }
    
    public function audit_export()
    {
        if (!$this->Session->check('Auth.User.id')) {
            return $this->redirect(['controller' => 'dashboard', 'action' => 'login']);
        }
    
        $id = $_REQUEST['id'] ?? 0;
        if (empty($id)) {
            throw new BadRequestException('Invalid Audit ID.');
        }
    
        // 🔹 Fetch main audit record
        $indents = $this->Audit_stock->query("SELECT * FROM pos_auditstock WHERE pos_audit_id = $id");
    
        if (empty($indents)) {
            $this->Session->setFlash('No audit found for this record.', 'flash_error');
            return $this->redirect(['action' => 'view_audit']);
        }
    
        $audit = $indents[0]['pos_auditstock'];
    
        // 🔹 Fetch audit items
        $items = $this->Audit_stock_items->query("
            SELECT * 
            FROM pos_auditstock_items 
            WHERE pos_audit_id = $id 
              AND status = 1
            ORDER BY sku_name ASC
        ");
    
        if (empty($items)) {
            $this->Session->setFlash('No audit items found for this record.', 'flash_error');
            return $this->redirect(['action' => 'view_audit', $id]);
        }
    
        // 🔹 Load PHPExcel
        App::import('Vendor', 'PHPExcel/PHPExcel');
        error_reporting(0);
        ini_set('display_errors', TRUE);
        ini_set('display_startup_errors', TRUE);
    
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->getProperties()
            ->setCreator("QuickMart Audit System")
            ->setLastModifiedBy("QuickMart")
            ->setTitle("Audit Stock Report")
            ->setSubject("Audit Stock Report")
            ->setDescription("Exported audit stock data.")
            ->setKeywords("audit stock excel")
            ->setCategory("Export File");
    
        $sheet = $objPHPExcel->setActiveSheetIndex(0);
    
        // 🔹 Header Info (Compact Layout)
        $sheet->setCellValue('A1', 'Audit ID:')
              ->setCellValueExplicit('B1', (string)$audit['pos_audit_id'], PHPExcel_Cell_DataType::TYPE_STRING)
              ->setCellValue('C1', 'Store Name:')
              ->setCellValue('D1', $audit['store_name']);
    
        $sheet->setCellValue('A2', 'Remarks:')
              ->setCellValue('B2', $audit['remarks'])
              ->setCellValue('C2', 'Audit Date:')
              ->setCellValue('D2', $audit['updateon']);
    
        // Format header info
        $sheet->getStyle('A1:D2')->getFont()->setBold(true)->setSize(10);
        $sheet->getStyle('A1:D2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
    
        // 🔹 Table Headers (Row 4)
        $headers = [
            'Sr No.', 'SKU Name', 'Unit', 'Price',
             'Physical Stock','Opening Stock',
             'Purchase','Consumption','Damage',
            'Closing Stock','Variance', 'Amount', 'Percentage',
            'Audit From', 'Audit To'
        ];
    
        $col = 'A';
        foreach ($headers as $header) {
            $sheet->setCellValue($col . '4', $header);
            $sheet->getStyle($col . '4')->getFont()->setBold(true);
            $sheet->getStyle($col . '4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $col++;
        }
    
        // 🔹 Fill Data Rows
        $row = 5;
        $count = 1;
        foreach ($items as $itm) {
            $data = $itm['pos_auditstock_items'];
            $opening = (float)$data['openingstock'];
            $variance = (float)$data['variance'];
            $price = (float)$data['sku_price'];
            $percentage = ($opening > 0) ? round(($variance / $opening) * 100, 2) : 0;
            
            $sheet->setCellValue("A$row", $count)
                  ->setCellValue("B$row", $data['sku_name'])
                  ->setCellValue("C$row", $data['sku_unit_name'])
                  ->setCellValue("D$row", $data['sku_price'])
                  ->setCellValue("E$row", (float)$data['physicalstock'])
                  ->setCellValue("F$row", $opening)
                  ->setCellValue("G$row", $data['purchased'])
                  ->setCellValue("H$row", $data['consumption'])
                  ->setCellValue("I$row", $data['damage'])
                  ->setCellValue("J$row", $data['closingstock'])
                  ->setCellValue("K$row", $variance)
                  ->setCellValue("L$row", $price * $variance)
                  ->setCellValue("M$row", $percentage . '%')
                  ->setCellValue("N$row", $data['auditfrom'])
                  ->setCellValue("O$row", $data['auditto']);
    
            $row++;
            $count++;
        }
    
        // 🔹 Auto-size columns
        foreach (range('A', 'J') as $columnID) {
            $sheet->getColumnDimension($columnID)->setAutoSize(true);
        }
    
        // 🔹 Sheet name and download setup
        $sheet->setTitle('Audit Report');
        $objPHPExcel->setActiveSheetIndex(0);
    
        // 🔹 Output headers
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="Audit_Report_' . $id . '.xls"');
        header('Cache-Control: max-age=0');
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
        header('Cache-Control: cache, must-revalidate');
        header('Pragma: public');
    
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }
    
    public function sku_stock_level() {
        if (!$this->Session->check('Auth.User.id')) {
            return $this->redirect(['controller' => 'dashboard', 'action' => 'login']);
        }
    
        $this->layout = "users";
    
        $today = date("Y-m-d");
        $currentTime = date("H:i", strtotime("+1 minutes"));

        $sel_date = $today;      // default
        $sel_time = $currentTime; // default

        $skuledger_startdate = date('Y-m-d', strtotime('-10 days'));
        $stocklevel_enddate   = $skuledger_enddate   = date('Y-m-d');
        $sku_id = null;
        
        $stocklevel_startdate = $sel_date.' '.$sel_time.':00';
        
        if ($this->request->is(['post', 'put'])) {
            if (isset($this->request->data['submit_stocklevel'])) {
                
                $sel_date=$this->request->data['sel_date'];
                $sel_time=$this->request->data['sel_time'];
            
                $stocklevel_startdate = $sel_date.' '.$sel_time.':00';
            }
    
            if (isset($this->request->data['submit_skutransactions'])) {
                $daterange = $this->request->data['daterange'] ?? '';
                $dates = explode(" - ", $daterange);
                if (count($dates) === 2) {
                    $skuledger_startdate = date('Y-m-d', strtotime(trim($dates[0])));
                    $skuledger_enddate = date('Y-m-d', strtotime(trim($dates[1])));
                }
                $sku_id = $this->request->data['SkuLedger']['sku'] ?? null;
            }
            
        }
        
        $this->set(compact('sel_date', 'sel_time'));

        $id = $this->Session->read('Auth.Location.id');
        
        $conditions = [
            'status' => 1,
            'type'   => 'OUTLET',
            'id' =>$id
        ];
        
    
        if (!empty($this->request->data['StockLevel']['cashier'])) {
            $store_id = $this->request->data['StockLevel']['cashier'];
            $transactions = $this->Stock_ledger->query("
                SELECT sl1.sku_id,sl1.sku_price,sl1.sku_name,s.stock,sl1.unit_name, sl1.actual_balance
                FROM stock_ledger sl1
                INNER JOIN (
                    SELECT sku_id, MAX(id) AS max_id
                    FROM stock_ledger
                    WHERE type = 'OUTLET'  AND item_type = 'SKU' 
                      AND store_id IN ($store_id)
                      AND created < '$stocklevel_startdate'
                    GROUP BY sku_id
                ) latest ON sl1.id = latest.max_id
                LEFT  JOIN inventory_skus s ON s.id = sl1.sku_id
                WHERE sl1.type = 'OUTLET' AND sl1.item_type = 'SKU' order by s.name asc
            ");
        } 
        $skus = $this->Inventory_sku->query("SELECT * FROM inventory_skus WHERE status=1 order by name asc");
        
        
        $skutransactions = [];
        
        if (!empty($sku_id)) {
            if($id){
                
                $startDatetime = $skuledger_startdate . ' ' . shiftstart;
                $endDatetime   = date('Y-m-d', strtotime($skuledger_enddate . ' +1 day')) . ' ' . shiftend;
                
                $skutransactions = $this->Stock_ledger->query("
                    SELECT * FROM stock_ledger 
                    WHERE created BETWEEN '$startDatetime' AND '$endDatetime'
                    AND type='OUTLET' AND sku_id = $sku_id and store_id IN ($id) and item_type = 'SKU'
                    ORDER BY id DESC
                ");
                
            }
        }
        
        $this->set('stocklevel_daterange', date('m/d/Y', strtotime($stocklevel_startdate)) . ' - ' . date('m/d/Y', strtotime($stocklevel_enddate)));
        $this->set('skuledger_daterange', date('m/d/Y', strtotime($skuledger_startdate)) . ' - ' . date('m/d/Y', strtotime($skuledger_enddate)));

            
        $users=$this->Location->find('all',array('conditions'=>$conditions));
                
	    $this->set(compact('users'));
    
        $this->set(compact('transactions', 'skus', 'skutransactions'));
    }
    
    
    public function sku_stock_levelexport()
    {
        $date_range = $_GET['sdate'] ?? '';
    
        if (!$date_range) {
            throw new BadRequestException('Invalid date range.');
        }
    
        $store_id = $this->Session->read('Auth.Location.id');
        
        // Fetch store name
        $store_data = $this->Location->find('first', [
            'conditions' => ['Location.id' => $store_id],
            'fields' => ['Location.name']
        ]);
        $store_name = $store_data['Location']['name'] ?? 'Unknown Store';
    
        // ===============================
        // Fetch Stock Ledger Data (Same as View)
        // ===============================
        $transactions = $this->Stock_ledger->query("
            SELECT sl1.sku_id, sl1.sku_price, sl1.sku_name, sl1.unit_name,
                   s.stock, sl1.actual_balance
            FROM stock_ledger sl1
            INNER JOIN (
                SELECT sku_id, MAX(id) AS max_id
                FROM stock_ledger
                WHERE type = 'Outlet' 
                  AND item_type = 'SKU' 
                  AND store_id = $store_id
                  AND created < '$date_range'
                GROUP BY sku_id
            ) latest ON sl1.id = latest.max_id
            LEFT JOIN inventory_skus s ON s.id = sl1.sku_id
            WHERE sl1.type = 'Outlet' AND sl1.item_type = 'SKU'
            ORDER BY s.name ASC
        ");
        
        // ===============================
        // Prepare Excel Export
        // ===============================
        App::import('Vendor', 'PHPExcel/PHPExcel');
        error_reporting(0);
        ini_set('display_errors', TRUE);
        ini_set('display_startup_errors', TRUE);
    
        if (PHP_SAPI == 'cli') {
            die('This example should only be run from a Web Browser');
        }
    
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->getProperties()
            ->setCreator("Aether")
            ->setLastModifiedBy("Aether")
            ->setTitle("DC Stock Level Report")
            ->setSubject("Stock Report")
            ->setDescription("Exported DC stock level data.")
            ->setKeywords("stock excel")
            ->setCategory("Export File");
    
        $sheet = $objPHPExcel->setActiveSheetIndex(0);
    
        // ===============================
        // Header Section
        // ===============================
        $sheet->setCellValue('A1', "SKU STOCK LEVEL REPORT");
        $sheet->mergeCells('A1:G1');
        $sheet->getStyle('A1:G1')->getFont()->setBold(true)->setSize(14);
    
        $sheet->setCellValue('A2', "Store: $store_name");
        $sheet->mergeCells('A2:G2');
        $sheet->getStyle('A2:G2')->getFont()->setBold(true)->setSize(12);
    
        $sheet->setCellValue('A3', "Date $date_range");
        $sheet->mergeCells('A3:G3');
        $sheet->getStyle('A3:G3')->getFont()->setBold(true)->setSize(11);
    
        // ===============================
        // Column Headers
        // ===============================
        $headers = ['Sr No.', 'SKU Name', 'Unit Name', 'Price', 'Required Qty', 'Available Qty', 'Value'];
        $col = 'A';
        foreach ($headers as $header) {
            $sheet->setCellValue($col . '5', $header);
            $sheet->getStyle($col . '5')->getFont()->setBold(true);
            $sheet->getColumnDimension($col)->setAutoSize(true);
            $col++;
        }
    
        // ===============================
        // Fill Data Rows
        // ===============================
        $row = 6;
        $count = 1;
        foreach ($transactions as $txn) {
            $data  = $txn['sl1'];
            $stock = $txn['s']['stock'] ?? 0;
            $value = round($data['sku_price'] * $data['actual_balance'], 2);
    
            $sheet->setCellValue("A$row", $count);
            $sheet->setCellValue("B$row", $data['sku_name']);
            $sheet->setCellValue("C$row", $data['unit_name']);
            $sheet->setCellValue("D$row", (float)$data['sku_price']);
            $sheet->setCellValue("E$row", (float)$stock);
            $sheet->setCellValue("F$row", (float)$data['actual_balance']);
            $sheet->setCellValue("G$row", $value);
    
            $row++;
            $count++;
        }
    
        // ===============================
        // Style and Output
        // ===============================
        $sheet->getStyle("A5:G5")->getFont()->setBold(true);
        $sheet->setTitle('SKU Stock Level');
        $objPHPExcel->setActiveSheetIndex(0);
    
        // Output headers
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="SKU_Stock_Level_Report.xls"');
        header('Cache-Control: max-age=0');
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
        header('Cache-Control: cache, must-revalidate');
        header('Pragma: public');
    
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }
    
    public function po_grn_history()
    {
        if (!$this->Session->check('Auth.Location.id')) {
            return $this->redirect(['controller' => 'dashboard', 'action' => 'login']);
        }
    
        $this->layout = "users";
    
        $startDate = date('m/d/Y', strtotime('-1 month'));
        $endDate = date('m/d/Y');
    
        if ($this->request->is(['post', 'put'])) {
            $daterange = $this->request->data['daterange'];
            $dates = explode(" - ", $daterange);
            $start_date = trim($dates[0]);
            $end_date = trim($dates[1]);
    
            $startDate = date('m/d/Y', strtotime($start_date));
            $endDate = date('m/d/Y', strtotime($end_date));
    
            $startdate = date('Y-m-d', strtotime($start_date));
            $enddate = date('Y-m-d', strtotime($end_date));
        } else {
            $startdate = date('Y-m-d', strtotime($startDate));
            $enddate = date('Y-m-d', strtotime($endDate));
        }
    
        // Session values
        $locationId = $this->Session->read('Auth.Location.id');
        
        // Build condition based on role
        $conditions = "created BETWEEN '$startdate 00:00:01' AND '$enddate 23:59:59'";
        $conditions .= " AND stockin_by_id = '$locationId'";
        $conditions .= " AND status = '1'";
        
        $users = $this->Inventory_stockin->query("
            SELECT * FROM inventory_stockin 
            WHERE  $conditions 
            ORDER BY id DESC
        ");
    
        $yourStartDateValue = $startDate . ' - ' . $endDate;
        $this->set('startdate', $yourStartDateValue);
        
        $this->set(compact('users'));
    }
    
    
    public function po_pdf() {
        if (!$this->Session->check('Auth.Location.id')) {
            return $this->redirect(['controller' => 'dashboard', 'action' => 'login']);
        }
    
        $this->layout = "login"; // or use 'ajax' or a dedicated PDF layout
    
        if ($this->request->is(['post', 'put'])) {
            $po_id = $this->request->data['Report']['poid'];
            // Secure: Use bound parameters
            
            $po_id = addslashes($po_id); // minimal protection (better: use bind)
            
            $indents = $this->Inventory_stockin->query("
                SELECT 
                    inventory_stockin.*,
                    inventory_vendors.*,
                    locations.print_line1,
                    locations.print_line2,
                    locations.print_line3
            
                FROM inventory_stockin
            
                LEFT JOIN inventory_vendors 
                    ON inventory_stockin.vendor_id = inventory_vendors.id
                
                LEFT JOIN locations 
                    ON locations.id = inventory_stockin.stockin_by_id
            
                WHERE inventory_stockin.stockin_id = '$po_id'
                AND inventory_stockin.status != 0
            ");
            
            $items = $this->Inventory_stockin_item->query(
                "SELECT * FROM inventory_stockin_items 
                 WHERE stockin_id = $po_id AND status != 0 and stockin_quantity!=0"
            );
            $location_id=$indents[0]['inventory_stockin']['stockin_by_id'];
    
            // Get location details - default static for now
            $locationdetail = $this->Location->find('first', [
                'conditions' => ['Location.id' => $location_id]
            ]);
    
            // ================= GST TYPE =================
            $vendorState   = trim($indents[0]['inventory_vendors']['state']);
            $locationState = trim($locationdetail['Location']['state']);
            
            $isIGST = (strcasecmp($vendorState, $locationState) !== 0) ? 1 : 0;
            $isCGST = $isIGST ? 0 : 1;
            
            // Send data to the view
            $this->set(compact('po_id', 'indents', 'items', 'locationdetail'));
    
            // Optional: Force specific view if needed
            $this->render('po_pdf'); // e.g., pdf_template.ctp
        }
    }
    

}
