<?php
include_once('../inc/global.php');
include_once("../config/route.php");
$user = unserialize($_SESSION['user']);
$today = Utility\WebSystem::displayDate(date("Y-m-d H:i:s"), 'Y-m-d');

$map = [
    'column_transactionDate' => 'transaction.transactionDate',
    'column_transactionAction' => 'transaction.action',
    'column_itemName' => 'item.name',
    'column_supplierName' => 'supplier.name',
    'column_fromStorageLocationName' => 'fromStorageLocation.name',
    'column_fromStorageLocationAreaName' => 'fromStorageLocationArea.name',
    'column_toStorageLocationName' => 'toStorageLocation.name',
    'column_toStorageLocationAreaName' => 'toStorageLocationArea.name',
    'column_transactionBatchNo' => 'transaction.batchNo',
    'column_transactionQty' => 'transaction.qty',
    'column_unitName' => 'unit.name',
    'column_totalValue' => 'transaction.purchaseUnitCost',
    'column_function' => 'transaction.id'
];

$draw = $_POST['draw'];
$row = $_POST['start'];
$rowperpage = $_POST['length']; // Rows display per page
$columnIndex = $_POST['order'][0]['column']; // Column index
$columnName = $_POST['columns'][$columnIndex]['data']; // Column name
$columnSortOrder = $_POST['order'][0]['dir']; // asc or desc
$searchValue = $_POST['search']['value']; // Search value
$searchQuery = " ";

$sqlAll = Database\Sql::select(['transaction', 'transaction'])
->leftJoin(['item', 'item'], "item.id = transaction.itemID")
->leftJoin(['supplier', 'supplier'], "supplier.id = item.supplierID")
->leftJoin(['storage_location_area', 'fromStorageLocationArea'], "transaction.fromStorageAreaID = fromStorageLocationArea.id")
->leftJoin(['storage_location', 'fromStorageLocation'], "fromStorageLocationArea.storageLocationID = fromStorageLocation.id")
->leftJoin(['storage_location_area', 'toStorageLocationArea'], "transaction.toStorageAreaID = toStorageLocationArea.id")
->leftJoin(['storage_location', 'toStorageLocation'], "toStorageLocationArea.storageLocationID = toStorageLocation.id")
->leftJoin(['unit', 'unit'], "item.unitID = unit.id");

$sqlAll->setFieldValue('
   transaction.id transactionID, 
   transaction.transactionDate transactionDate, 
   transaction.action transactionAction, 
   item.name itemName, 
   supplier.name supplierName,   
   fromStorageLocation.name fromStorageLocationName, 
   fromStorageLocationArea.name fromStorageLocationAreaName,
   toStorageLocation.name toStorageLocationName, 
   toStorageLocationArea.name toStorageLocationAreaName,
   transaction.batchNo transactionBatchNo, 
   transaction.qty transactionQty,
   unit.name unitName,
   transaction.purchaseUnitCost * transaction.qty transactionTotalValue
');

$stmAll = $sqlAll->prepare();
$stmAll->execute();

if($searchValue != ''){
    $searchValue = addslashes($searchValue);
    $sqlAll->whereOp("(transaction.transactionDate LIKE '%".$searchValue."%' 
        OR transaction.action LIKE '%".$searchValue."%'   
        OR item.name LIKE '%".$searchValue."%'
        OR fromStorageLocation.name LIKE '%".$searchValue."%'
        OR fromStorageLocationArea.name LIKE '%".$searchValue."%'
        OR toStorageLocation.name LIKE '%".$searchValue."%'
        OR toStorageLocationArea.name LIKE '%".$searchValue."%'
        OR transaction.batchNo LIKE '%".$searchValue."%'
        OR transaction.qty LIKE '%".$searchValue."%'
        OR unit.name LIKE '%".$searchValue."%'
        OR (transaction.qty * transaction.purchaseUnitCost LIKE '%".$searchValue."%')
    )");
}

foreach($_POST['columns'] as $idx => $column){
    if(!empty($column['search']['value'])) {
        $idxSearchValue = substr($column['search']['value'], 1, -1);
        if($_POST['columns'][$idx]['data']=="column_transactionStatus"){
           if($idxSearchValue=="有效") {
             $idxSearchValue="Enabled";
           }

           if($idxSearchValue=="無效") {
             $idxSearchValue="Disabled";
           }           
        } 

        $sqlAll->where([$map[$_POST['columns'][$idx]['data']], '=', '"'.strip_tags($idxSearchValue).'"']);
    }
}



if($stmAll->rowCount()==0 && $filter) {
    $sqlAll = Database\Sql::select(['transaction', 'transaction'])
    ->leftJoin(['item', 'item'], "item.id = transaction.itemID")
    ->leftJoin(['supplier', 'supplier'], "supplier.id = item.supplierID")
    ->leftJoin(['storage_location_area', 'fromStorageLocationArea'], "transaction.fromStorageAreaID = fromStorageLocationArea.id")
    ->leftJoin(['storage_location', 'fromStorageLocation'], "fromStorageLocationArea.storageLocationID = fromStorageLocation.id")
    ->leftJoin(['storage_location_area', 'toStorageLocationArea'], "transaction.toStorageAreaID = toStorageLocationArea.id")
    ->leftJoin(['storage_location', 'toStorageLocation'], "toStorageLocationArea.storageLocationID = toStorageLocation.id")
    ->leftJoin(['unit', 'unit'], "item.unitID = unit.id")
    ->leftJoin(['status', 'status'], "stock.status = status.id");
    
    $sqlAll->setFieldValue('
       transaction.id transactionID, 
       transaction.transactionDate transactionDate, 
       transaction.action transactionAction, 
       item.name itemName, 
       supplier.name supplierName,   
       fromStorageLocation.name fromStorageLocationName, 
       fromStorageLocationArea.name fromStorageLocationAreaName,
       toStorageLocation.name toStorageLocationName, 
       toStorageLocationArea.name toStorageLocationAreaName,
       transaction.batchNo transactionBatchNo, 
       transaction.qty transactionQty,
       unit.name unitName,
       transaction.purchaseUnitCost * transaction.qty transactionTotalValue
    ');
    
};

$sql = $sqlAll->order($map[$columnName],$columnSortOrder)->limit($rowperpage, $row);

$stm = $sql->prepare();
$stm->execute();

$returnArr = [];
$contentArr = [];
$lineCount = 0;

foreach($stm as $data){    

    $dataArr = [
        "column_transactionDate" => $data['transactionDate'],
        "column_transactionAction" => $data['transactionAction'],
        "column_itemName" => $data['itemName'],
        "column_supplierName" => $data['supplierName'],
        "column_fromStorageLocationName" => $data['fromStorageLocationName'],
        "column_fromStorageLocationAreaName" => $data['fromStorageLocationAreaName'],
        "column_toStorageLocationName" => $data['toStorageLocationName'],
        "column_toStorageLocationAreaName" => $data['toStorageLocationAreaName'],
        "column_transactionBatchNo" => $data['transactionBatchNo'],
        "column_transactionQty" => $data['transactionQty'],
        "column_unitName" => $data['unitName'],
        "column_totalValue" => number_format($data['transactionTotalValue'],2),
        "column_function"=>"
            <div class='btn-group' role='group' aria-label=''>
                <button class='btn btn-sm btn-dark btnView' type='button' data-bs-toggle='tooltip' data-bs-placement='top' title='".L('View')."' data-id='".$data['transactionID']."'><i class='fas fa-sm fa-th'></i></button>
            </div>
        "
    ];    

    $lineCount++;
    $contentArr[] = $dataArr;
}

$returnArr['draw'] = intval($draw);
$returnArr["iTotalDisplayRecords"] = $stm->rowCount();
$returnArr["iTotalRecords"] = $stmAll->rowCount();
$returnArr["data"] = $contentArr;

echo json_encode($returnArr);

?>