<?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();
    
        // Use a different layout for login if needed
        $this->layout = 'login';
    
        // Override default Auth settings for this controller
        $this->Auth->authenticate = [
            'Form' => [
                'userModel' => 'Location', // table name is `locations`
                'fields' => ['username' => 'username', 'password' => 'password'],
                'scope' => ['Location.type' => 'OUTLET'] // <-- This is the key line

            ]
        ];
    
        $this->Auth->loginAction = ['controller' => 'Outlet', 'action' => 'login'];
        $this->Auth->loginRedirect = ['controller' => 'Outlet', 'action' => 'indent']; // example
        $this->Auth->logoutRedirect = ['controller' => 'Outlet', 'action' => 'login'];
    
        // Allow access to login without auth
        $this->Auth->allow(['login']);
    }
    
    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' => 'outlet', '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 = 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);
        $this->set(compact('submittedIndents', 'approvedIndents', 'processedIndents'));
    }

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

    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;
    
            foreach ($checkeddatas as $item) {
                $skuid = $item['id'];
                $price = $item['price'];
                $quantity = $item['sku_quantity'];
                
                $product_taxes=$this->Inventory_semifinished_tax->query("select SUM(tax_percentage) as total_tax from inventory_semifinished_tax where product_id='$skuid' ");
                $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
                ];
    
                $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'=>'outlet','action' => 'login'));		
		}
        
	    $this->layout = "users";
		$id=$_REQUEST['id'];
    
        $indents = $this->Inventory_indent->query("SELECT * FROM inventory_indent where indent_id=$id");
        $this->set(compact('indents'));
        
		$items = $this->Inventory_indent_items->query("SELECT * FROM inventory_indent_items where indent_id=$id and status=1");
        
        $this->set(compact('items'));
        
        
	}
	
	public function edit_indent()
    {
        if (!$this->Session->check('Auth.User.id')) {
            return $this->redirect(['controller' => 'outlet', 'action' => 'login']);
        }
    
        $this->layout = "users";
        $id = $_REQUEST['id'];
    
        $role = $this->Session->read('Auth.User.role');
        $user_location = $this->Session->read('Auth.Location.id');
        
        // Fetch indent data
        $user = "SELECT *,
            (SELECT sku_quantity FROM inventory_indent_items 
             WHERE indent_id='$id' AND sku_id=inventory_semifinished.id) 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_indent->find('first', [
            'conditions' => ['indent_id' => $id],
            'recursive' => -1
        ]);
        
        $optlocations = $this->Location->find('all', [
            'conditions' => ['status' => 1,'type' => 'dc']
        ]);
    
        $this->set(compact('optlocations'));
    
        $selected_location_id = $indentData['Inventory_indent']['location_id'];
        $selected_warehouse_id = $indentData['Inventory_indent']['warehouse_id'];
        $selected_expiry = $indentData['Inventory_indent']['expiry'];
    
        $this->set(compact('users', 'id', 'selected_location_id', 'selected_expiry','selected_warehouse_id'));
    }

    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 = '';
    
            // 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'] : '';
            }
            
            $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;
            }
            // Delete existing indent and items
            $this->Inventory_indent_items->query("DELETE FROM inventory_indent_items WHERE indent_id = " . (int)$indentid);
            $this->Inventory_indent->query("DELETE FROM inventory_indent WHERE indent_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'];
                $quantity = $checkeddata['sku_quantity'];
                
                if (!empty($quantity)) {
                    $product_taxes=$this->Inventory_semifinished_tax->query("select SUM(tax_percentage) as total_tax from inventory_semifinished_tax where product_id='$skuid' ");
                    $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);
                    

                    $itemData = array(
                        '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
                    );
    
                    if ($this->Inventory_indent_items->saveAll($itemData)) {
                        $chk = true;
                    } else {
                        $chk = false;
                        $this->Flash->error(__('Internal indent could not be updated. Please, try again.'));
                    }
    
                    // $productamount = $quantity * $price;
                    // $amount += $productamount;
                    $amount += ($skuamount);

                }
            }
    
            if ($chk) {
                $indentData = array(
                    '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->saveAll($indentData)) {
                    echo json_encode(['response_code' => 1, 'data' => 'Updated']);
                } else {
                    echo json_encode(['response_code' => 0, 'data' => 'Internal indent could not be updated. Please, try again.']);
                }
            } else {
                echo json_encode(['response_code' => 0, 'data' => 'Internal indent items could not be updated. Please, try again.']);
            }
        }
    }
    
    public function indent_change_status() 
    {
        $this->autoRender = false; // Disable view rendering
    
        if ($this->request->is('post')) {
            $userId = $this->request->data['_id'];
    
            // Update user status
            $this->Inventory_indent->id = $userId;
            $newStatus = $this->Inventory_indent->field('status') == 1 ? 0 : 1;
            if ($this->Inventory_indent->save(['status' => $newStatus,'sync' => 0])) {
                echo json_encode(['status' => true]);
            } else {
                echo json_encode(['status' => false]);
            }
        }
    }
    
    public function grn_history()
    {
        if (!$this->Session->check('Auth.Location.id')) {
            return $this->redirect(['controller' => 'outlet', '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 stockin()
    {
        if (!$this->Session->check('Auth.Location.id')) {
            return $this->redirect(['controller' => 'outlet', '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 = 1"
            );
            $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
            $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]
            ]);
            // 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]);
                    
                    $product_taxes=$this->Inventory_semifinished_tax->query("select SUM(tax_percentage) as total_tax from inventory_semifinished_tax where product_id='$sku_id' ");
                    $total_tax=round($product_taxes['0']['0']['total_tax'],2);
                    
                    $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' => 2, 'sync' => 0, 'stockin_quantity' => $qty,'stockin_tax_value' => $tax_value,
                        'stockin_amount' => $skuamount],
                        ['consignment_id' => $poid, 'sku_id' => $sku_id]
                    );
                    
                    
                    $amount += ($skuamount);
                
                    $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')
                    ];
    				$this->Stock_ledger->saveAll($ledgerData['Stock_ledger']);
                    
                }
            }
    
            // 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 
                WHERE consignment_id = '$poid'");
    
            echo json_encode(['status' => true]);
        }
    }

    public function stock_level() {
        if (!$this->Session->check('Auth.User.id')) {
            return $this->redirect(['controller' => 'outlet', 'action' => 'login']);
        }
    
        $this->layout = "users";
    
        $stocklevel_startdate = $stocklevel_enddate = $skuledger_startdate = $skuledger_enddate = date('Y-m-d');
        $sku_id = null;
    
        if ($this->request->is(['post', 'put'])) {
            if (isset($this->request->data['submit_stocklevel'])) {
                // print_r($this->request->data['daterange']);exit;
        
                $daterange = $this->request->data['daterange'] ?? '';
                $dates = explode(" - ", $daterange);
                if (count($dates) === 2) {
                    $stocklevel_startdate = date('Y-m-d', strtotime(trim($dates[0])));
                    $stocklevel_enddate = date('Y-m-d', strtotime(trim($dates[1])));
                }
            }
    
            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;
            }
            
        }
        
        $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, 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 BETWEEN '$stocklevel_startdate 00:00:01' AND '$stocklevel_enddate 23:59:59'
                GROUP BY sku_id
            ) latest ON sl1.id = latest.max_id
            WHERE sl1.type = 'OUTLET'  and sl1.item_type = 'Semifinished'
              AND sl1.store_id = $store_id
        ");
        // print_r("created BETWEEN '$stocklevel_startdate 00:00:01' AND '$stocklevel_enddate 23:59:59'");exit;
        $skus = $this->Inventory_semifinished->query("SELECT * FROM inventory_semifinished WHERE status=1");
        
        $skutransactions = [];
        if (!empty($sku_id)) {
            $skutransactions = $this->Stock_ledger->query("
                SELECT * FROM stock_ledger 
                WHERE created BETWEEN '$skuledger_startdate 00:00:01' AND '$skuledger_enddate 23:59:59'
                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'] ?? '';
    
        $dates = explode(" - ", $date_range);
        if (count($dates) !== 2) {
            throw new BadRequestException('Invalid date range.');
        }
        $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'];
        
        $start_date = trim($dates[0]);
        $end_date = trim($dates[1]);
    
        $startdate = date('Y-m-d', strtotime($start_date));
        $enddate = date('Y-m-d', strtotime($end_date));
    
        // Fetch stock ledger data
        $transactions = $this->Stock_ledger->query("
            SELECT sl1.sku_id,sl1.sku_price,sl1.sku_name,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 = 'Semifinished'
                  AND store_id = $store_id
                  AND created BETWEEN '$startdate 00:00:01' AND '$enddate 23:59:59'
                GROUP BY sku_id
            ) latest ON sl1.id = latest.max_id
            WHERE sl1.type = 'outlet' and item_type = 'Semifinished'
              AND sl1.store_id = $store_id
        ");
        
        // 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 and formatting
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'STOCK LEVEL REPORT FROM '.$startdate.' TO '.$enddate);
        $objPHPExcel->getActiveSheet()->mergeCells('A1:F1');
        $objPHPExcel->getActiveSheet()->getStyle('A1:F1')->getFont()->setBold(true)->setSize(14);
    
        // Column headers
        $headers = ['Sr No.', 'SKU Name', 'Unit Name', 'Price', 'Quantity', 'Value'];
        $col = 'A';
        foreach ($headers as $header) {
            $objPHPExcel->getActiveSheet()->setCellValue($col . '3', $header);
            $objPHPExcel->getActiveSheet()->getStyle($col . '3')->getFont()->setBold(true);
            $col++;
        }
    
        // Fill data
        $row = 4;
        $count = 1;
        foreach ($transactions as $txn) {
            $data = $txn['sl1'];
            $value = round($data['sku_price'] * $data['actual_balance'], 2);
    
            $objPHPExcel->getActiveSheet()
                ->setCellValue("A$row", $count)
                ->setCellValue("B$row", $data['sku_name'])
                ->setCellValue("C$row", $data['unit_name'])
                ->setCellValue("D$row", (float) $data['sku_price'])
                ->setCellValue("E$row", (float) $data['actual_balance'])
                ->setCellValue("F$row", $value);
    
            $row++;
            $count++;
        }
    
        // Finalize
        $objPHPExcel->getActiveSheet()->setTitle('Stock Level');
        $objPHPExcel->setActiveSheetIndex(0);
    
        // Output headers
        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' => 'outlet', '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]
            );
    
            $items = $this->Inventory_indent_items->query(
                "SELECT * FROM inventory_indent_items 
                 WHERE indent_id = ? AND status != 0", 
                [$po_id]
            );
            $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.Location.id')) {
            return $this->redirect(['controller' => 'Outlet', '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='SKU' and type='OUTLET'
            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.Location.id')) {
            $this->redirect(['controller' => 'Outlet', '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_sku->find('all', [
            'conditions' => ['Inventory_sku.status' => 1],
            'order' => ['Inventory_sku.name DESC']
        ]);
        $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.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'=>'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.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");
        $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.Location.id')) {
            return $this->redirect(['controller' => 'outlet', '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_skus.id) as quantity 
            FROM inventory_skus 
            WHERE inventory_skus.status=1 
            ORDER BY inventory_skus.name DESC";
       
        $users = $this->Inventory_sku->query($user);
         
        // Fetch existing indent record
        $indentData = $this->Inventory_damage->find('first', [
            'conditions' => ['damage_id' => $id],
            '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.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,
                            '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("DELETE FROM inventory_damage_items WHERE damage_id = " . (int)$indentid);
            $this->Inventory_damage->query("DELETE FROM inventory_damage 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' => 'Outlet', '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])) {
                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' => 'Outlet', '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'=>'Outlet','action' => 'login'));		
		}
        
	    $this->layout = "users";
		$id=$_REQUEST['id'];
    
        $indents = $this->Inventory_damage->query("SELECT * FROM inventory_damage where damage_id=$id");
        $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' => 'outlet', '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) 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],
            '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("DELETE FROM inventory_damage_items WHERE damage_id = " . (int)$indentid);
            $this->Inventory_damage->query("DELETE FROM inventory_damage 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'=>'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' => 'outlet', 'action' => 'login']);
        }
    
        $this->layout = "users";
    
        // Default date range (last 30 days)
        $startDate = date('m/d/Y', strtotime('-7 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.User');
        
        $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'
            ],
            'fields' => ['Audit_stock.*'], // Ensure ID is included
            'order' => ['Audit_stock.id' => 'DESC'],
            'recursive' => 1
        ]);
        

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

    public function create_audit_stock()
    {
        if (!$this->Session->check('Auth.Location.id')) {
            return $this->redirect(['controller' => 'outlet', '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 DESC");
        $this->set(compact('skus'));
    
        if ($this->request->is(['post', 'put'])) {
            $data = $this->request->data;
    
            $remarks = trim($data['remarks']);
            $auditDate = $data['audit_date'] ?? $today;
            $auditTime = $data['audit_time'] ?? $currentTime;
            $submitAll = $data['submit_all_items'] ?? 'no';
            $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 = isset($loc['Location']['name']) ? $loc['Location']['name'] : '';
            
            do {
                // Generate a numeric-only ID: remove non-digits and trim to 12 digits
                $pos_audit_id = preg_replace('/\D/', '', substr(sha1(uniqid() . rand()), 0, 32));
                $pos_audit_id = substr($pos_audit_id, 0, 12); // Adjust digit length as needed
                if ($pos_audit_id[0] === '0') {
                    $pos_audit_id[0] = strval(rand(1, 9)); // replace first digit with 1–9
                }
                
                // Check for uniqueness in both pos_auditstock and temp_audit_stock tables
                $existsMain = $this->Audit_stock->find('count', [
                    'conditions' => ['Audit_stock.pos_audit_id' => $pos_audit_id],
                    'recursive' => -1
                ]);
            
            } while ($existsMain > 0);
            
            $selectAll = ($submitAll === 'yes') ? 1 : 0;
    
            $header = [
                'pos_audit_id' => $pos_audit_id,
                'store_id' => $storeId,
                'store_name' => $store_name,
                'item_type' => 'SKU',
                'type' => 'Outlet',
                'remarks' => $remarks,
                'status' => 1,
                'select_all' => $selectAll,
                'updateon' => $createdTime
            ];
    
            $quantities = $data['quantity'] ?? [];
            // print_r($data['quantity']);exit;
            
                
            $items = [];
            
            if (isset($data['prepare_submit'])) {
                foreach ($quantities as $skuId => $qty) {
                    $qty = floatval($qty);
                    if ($qty <= 0) continue;
                
                    // Get last audit info
                    $lastAudit = $this->Audit_stock_items->find('first', [
                        'conditions' => ['sku_id' => $skuId,'item_type' => 'sku','type' => 'Outlet', 'status' => 1],
                        'order' => ['id DESC'],
                        'fields' => ['physicalstock', 'auditto'],
                        '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'];
                    }
                
                    $variance = $qty - $openingstock;
                    $auditto = $auditDate . ' ' . $auditTime . ':00';
                
                    $skuName = $data['sku_name'][$skuId] ?? '';
                    $unitId = (int)($data['unit_id'][$skuId] ?? 0);
                    $unitName = $data['unit_name'][$skuId] ?? '';
                    $sku_price=$data['price'][$skuId] ?? 0;
                    
                    $auditItem = [
                        'sku_id' => $skuId,
                        'pos_audit_id' => $pos_audit_id,
                        'sku_name' => $skuName,
                        'sku_unit' => $unitId,
                        'sku_unit_name' => $unitName,
                        'sku_price'=>$sku_price,
                        'openingstock' => $openingstock,
                        'closingstock' => 0,
                        'physicalstock' => $qty,
                        'variance' => $variance,
                        '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);
                
                    
                    $skuquantityForLedger = $qty;
                
                    $ledgerEntries = $this->Stock_ledger->find('all', [
                        'conditions' => [
                            'store_id' => $storeId,
                            'sku_id' => $skuId,
                            'item_type'=>'SKU',
                            'type' => 'Outlet',
                            'record_type !=' => 'audit_stock',
                            'created >' => $auditto
                        ],
                        'order' => ['created ASC'],
                        'recursive' => -1
                    ]);
                
                    if (!empty($ledgerEntries)) {
                        foreach ($ledgerEntries as $entry) {
                            $ledger = $entry['Stock_ledger'];
                            $prevQuantity = floatval($ledger['quantity']);
                            $quantityType = $ledger['quantity_type'];
                
                            $newBalance = ($quantityType === 'credit')
                                ? $skuquantityForLedger + $prevQuantity
                                : $skuquantityForLedger - $prevQuantity;
                
                            $this->Stock_ledger->id = $ledger['id'];
                            $this->Stock_ledger->save([
                                'variance' => $variance,
                                'actual_balance' => $newBalance,
                                'variance_type' => 'back_audit',
                                'variance_id' => $pos_audit_id,
                                'modified' => $createdTime
                            ]);
                
                            $skuquantityForLedger = $newBalance;
                        }
                    } else {
                        // Insert fresh ledger row
                        $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,
                            'type' => 'Outlet',
                            'quantity_type' => 'audit',
                            'created' => $auditto,
                            'modified' => $createdTime
                        ]);
                    }
                }
                
                // Save audit_stock header
                if ($this->Audit_stock->save($header)) {
                    $this->Flash->success(__('Audit submitted successfully.'));
                    return $this->redirect(['action' => 'auditstock']);
                } else {
                    $this->Flash->error(__('Failed to submit audit.'));
                }
            }
        }
    }
    
    public function view_audit()
	{
	    if (!$this->Session->check('Auth.Location.id')) {
            return $this->redirect(['controller' => 'outlet', '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' => 'outlet', 'action' => 'login']);
        }
    
        $this->layout = "users";
    
        // Default date range (last 30 days)
        $startDate = date('m/d/Y', strtotime('-7 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', 'startdate', 'enddate', 'preparedStocks'));
    }

    public function semi_create_audit_stock()
    {
        if (!$this->Session->check('Auth.Location.id')) {
            return $this->redirect(['controller' => 'outlet', '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 DESC");
        $this->set(compact('skus'));
    
        if ($this->request->is(['post', 'put'])) {
            $data = $this->request->data;
    
            $remarks = trim($data['remarks']);
            $auditDate = $data['audit_date'] ?? $today;
            $auditTime = $data['audit_time'] ?? $currentTime;
            $submitAll = $data['submit_all_items'] ?? 'no';
            $createdTime = date('Y-m-d H:i:s');
    
            $authUser = $this->Session->read('Auth.Location');
            $storeId = $authUser['id'] ?? 0;
            $store_name = $authUser['name'] ?? 0;
            
            do {
                // Generate a numeric-only ID: remove non-digits and trim to 12 digits
                $pos_audit_id = preg_replace('/\D/', '', substr(sha1(uniqid() . rand()), 0, 32));
                $pos_audit_id = substr($pos_audit_id, 0, 12); // Adjust digit length as needed
                if ($pos_audit_id[0] === '0') {
                    $pos_audit_id[0] = strval(rand(1, 9)); // replace first digit with 1–9
                }
                
                // Check for uniqueness in both pos_auditstock and temp_audit_stock tables
                $existsMain = $this->Audit_stock->find('count', [
                    'conditions' => ['Audit_stock.pos_audit_id' => $pos_audit_id],
                    'recursive' => -1
                ]);
            
            } while ($existsMain > 0);
            
            $selectAll = ($submitAll === 'yes') ? 1 : 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' => $selectAll,
                'updateon' => $createdTime
            ];
    
            $quantities = $data['quantity'] ?? [];
            // print_r($data['quantity']);exit;
            
                
            $items = [];
            
            if (isset($data['prepare_submit'])) {
                foreach ($quantities as $skuId => $qty) {
                    $qty = floatval($qty);
                    if ($qty <= 0) continue;
                
                    // Get last audit info
                    $lastAudit = $this->Audit_stock_items->find('first', [
                        'conditions' => ['sku_id' => $skuId,'item_type' => 'Semifinished','type' => 'Outlet', 'status' => 1],
                        'order' => ['id DESC'],
                        'fields' => ['physicalstock', 'auditto'],
                        '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'];
                    }
                
                    $variance = $qty - $openingstock;
                    $auditto = $auditDate . ' ' . $auditTime . ':00';
                
                    $skuName = $data['sku_name'][$skuId] ?? '';
                    $unitId = (int)($data['unit_id'][$skuId] ?? 0);
                    $unitName = $data['unit_name'][$skuId] ?? '';
                    $sku_price=$data['price'][$skuId] ?? 0;
                    
                    $auditItem = [
                        'sku_id' => $skuId,
                        'pos_audit_id' => $pos_audit_id,
                        'sku_name' => $skuName,
                        'item_type' => 'Semifinished',
                        'type' => 'Outlet',
                        'sku_unit' => $unitId,
                        'sku_unit_name' => $unitName,
                        'sku_price'=>$sku_price,
                        'openingstock' => $openingstock,
                        'closingstock' => 0,
                        'physicalstock' => $qty,
                        'variance' => $variance,
                        'auditfrom' => $auditfrom,
                        'auditto' => $auditto,
                        'remarks' => $remarks,
                        'status' => 1,
                        'created' => $createdTime,
                        'modified' => $createdTime
                    ];
                
                    $this->Audit_stock_items->create();
                    $this->Audit_stock_items->save($auditItem);
                
                    
                    $skuquantityForLedger = $qty;
                
                    $ledgerEntries = $this->Stock_ledger->find('all', [
                        'conditions' => [
                            'store_id' => $storeId,
                            'sku_id' => $skuId,
                            'item_type'=>'Semifinished',
                            'type' => 'Outlet',
                            'record_type !=' => 'audit_stock',
                            'created >' => $auditto
                        ],
                        'order' => ['created ASC'],
                        'recursive' => -1
                    ]);
                
                    if (!empty($ledgerEntries)) {
                        foreach ($ledgerEntries as $entry) {
                            $ledger = $entry['Stock_ledger'];
                            $prevQuantity = floatval($ledger['quantity']);
                            $quantityType = $ledger['quantity_type'];
                
                            $newBalance = ($quantityType === 'credit')
                                ? $skuquantityForLedger + $prevQuantity
                                : $skuquantityForLedger - $prevQuantity;
                
                            $this->Stock_ledger->id = $ledger['id'];
                            $this->Stock_ledger->save([
                                'variance' => $variance,
                                'actual_balance' => $newBalance,
                                'variance_type' => 'back_audit',
                                'variance_id' => $pos_audit_id,
                                'modified' => $createdTime
                            ]);
                
                            $skuquantityForLedger = $newBalance;
                        }
                    } else {
                        // Insert fresh ledger row
                        $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 audit_stock header
                if ($this->Audit_stock->save($header)) {
                    $this->Flash->success(__('Audit submitted successfully.'));
                    return $this->redirect(['action' => 'semi_auditstock']);
                } else {
                    $this->Flash->error(__('Failed to submit audit.'));
                }
            }
        }
    }
    
    public function semi_view_audit()
	{
	    if (!$this->Session->check('Auth.Location.id')) {
            return $this->redirect(['controller' => 'outlet', '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'));
        
        
	}
	
    
}
