<?php

$path_to_root = "../..";
include_once($path_to_root . "/config_db.php");
$db_connection = $db_connections[0];
$con = mysqli_connect($db_connection['host'], $db_connection['dbuser'], $db_connection['dbpassword'], $db_connection['dbname']);

if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
    exit();
}

if (isset($_GET["Clear"])) {
    if(mysqli_query($con, "UPDATE 0_stock_moves set not_cleared=0")){
        echo 'Success!';
    }
}

if (isset($_GET["Go"])) {
    $sql = "SELECT * FROM 0_stock_master";
    $result = mysqli_query($con, $sql);
    while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
        $qoh = get_qoh_on_date($row['stock_id']);
        $result1 = mysqli_query($con, "SELECT * FROM 0_stock_moves where stock_id='" . $row['stock_id'] . "' and qty>0  AND type in (25,11,17,26) ORDER BY trans_id DESC");
        while ($row1 = mysqli_fetch_array($result1, MYSQLI_ASSOC)) {
            echo 'STOCK ID: '.$row1['stock_id']. " ".$row['description'] ." Remainder: ".$qoh."<br/>";
            if ($row1['qty'] >= $qoh) {
                mysqli_query($con, "UPDATE 0_stock_moves set not_cleared=" . $qoh . " where trans_id=" . $row1['trans_id']);
                $qoh = 0;
                break;
            } else {
                mysqli_query($con, "UPDATE 0_stock_moves set not_cleared=qty where trans_id=" . $row1['trans_id']);
                $qoh -= $row1['qty'];
            }
        }
    }
}

if (isset($_GET["Fetch"])) {
    echo '<table><tr><th>Item</th> <th>QOH</th> <th>FIFO QOH</th></tr>';
    $sql = "SELECT * FROM 0_stock_master";
    $result = mysqli_query($con, $sql);
    while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
        $qoh = get_qoh_on_date($row['stock_id']);
        $fifo_qoh = get_fifo_qoh_on_date($row['stock_id']);
        echo '<tr><td>'.$row['stock_id']." " .$row['description'].'</td> <td>'.$qoh.'</td><td>'.$fifo_qoh.'</td></tr>';
    }
    echo '</table>';
}

mysqli_close($con);

function get_qoh_on_date($stock_id) {
    global $con;
    $date = date("Y-m-d");
    $sql = "SELECT SUM(qty)
     	FROM 0_stock_moves st
   		LEFT JOIN 0_voided v ON st.type=v.type AND st.trans_no=v.id
          WHERE ISNULL(v.id)
          AND stock_id='" . ($stock_id) . "'
          AND tran_date <= '$date'";
    $result = mysqli_query($con, $sql);

    $myrow = mysqli_fetch_row($result);

    $qoh = $myrow[0];
    return $qoh ? $qoh : 0;
}

function get_fifo_qoh_on_date($stock_id) {
    global $con;
    $date = date("Y-m-d");
    $sql = "SELECT SUM(not_cleared)
     	FROM 0_stock_moves st
   		LEFT JOIN 0_voided v ON st.type=v.type AND st.trans_no=v.id
          WHERE ISNULL(v.id)
          AND stock_id='" . ($stock_id) . "'
          AND tran_date <= '$date'";
    $result = mysqli_query($con, $sql);
    $myrow = mysqli_fetch_row($result);
    $qoh = $myrow[0];
    return $qoh ? $qoh : 0;
}
