<?php
session_start();

require 'dbcon.php';

function validate($inputData){
    global $conn;
    $validatedData = mysqli_real_escape_string($conn,$inputData);
    return trim($validatedData);
}

function redirect($url,$status)
{
    $_SESSION['status'] = $status;
        header('Location: '.$url);
        exit(0);
}

function alertMessage()
{
    if(isset($_SESSION['status']))
    {
        echo '<div class="alert alert-success">
            <h6>'.$_SESSION['status'].'</h6>
        </div>';
        unset($_SESSION['status']);
    }
}

function checkParamId($paramType)
{
    if(isset($_GET[$paramType]))
    {
        if($_GET[$paramType]!=null)
        {
            return $_GET[$paramType];
        }
        else
        {
            return 'No userID found!';
        }
    }
    else
    {
        return 'No userID given!';
    }
}

function getALL($tableName)
{
    global $conn;

    $table = validate($tableName);

    $query = "SELECT * FROM $table ";
    $result = mysqli_query($conn,$query);
    return $result;
}


function getById($tableName,$userID)
{
    global $conn;
    $table = validate($tableName);
    $userID = validate($userID);
    $query = "SELECT * from $table WHERE userID='$userID' LIMIT 1";
    $result = mysqli_query($conn,$query);

    if($result)
    {
        if(mysqli_num_rows($result)==1)
        {
            $row = mysqli_fetch_array($result, MYSQLI_ASSOC);
            $response = [
                'status' => 200,
                'message' => 'Fetched Data',
                'data' => $row
            ];
            return $response;
        }
        else
        {
            $response = [
                'status' => 404,
                'message' => 'No Record Found'
            ];
            return $response;
        }
    }
    else
    {
        $response = [
            'status' => 500,
            'message' => 'Something Went Wrong'
        ];
        return $response;
    }
}

function deleteQuery($tableName,$id){
    global $conn;

    $table = validate($tableName);
    $id = validate($id);
    $query = "DELETE FROM $table WHERE userID='$id' LIMIT 1";
    $result = mysqli_query($conn,$query);
    return $result;
}

function deleteCQuery($tableName,$id){
    global $conn;

    $table = validate($tableName);
    $id = validate($id);
    $query = "DELETE FROM $table WHERE clientID='$id' LIMIT 1";
    $result = mysqli_query($conn,$query);
    return $result;
}

function deletePQuery($tableName,$id){
    global $conn;

    $table = validate($tableName);
    $id = validate($id);
    $query = "DELETE FROM $table WHERE projectID='$id' LIMIT 1";
    $result = mysqli_query($conn,$query);
    return $result;
}

function linkALL($tableName)
{
    global $conn;

    $tableName = validate($tableName);

    $query = "SELECT projects.projectID, projects.projectName, clients.clientName,
                     projects.directMaterialcost, projects.directMaterialcostPrev,
                     projects.contractValue, projects.teamLead, projects.projectCoord,
                     contracts.contractNo, contracts.contractName, contracts.contractAmt,
                     contracts.taxDetails, contracts.clientPONo, contracts.ncstartDate, contracts.ncendDate,contracts.contractID
              FROM projects
              INNER JOIN clients ON projects.clientID = clients.clientID
              LEFT JOIN contracts ON projects.contractID = contracts.contractID
              ORDER BY clients.clientName ASC";

    $result = mysqli_query($conn, $query);
    return $result;
}

function linkALLDM($tableName)
{
    global $conn;

    $tableName = validate($tableName);

    // Modify the SQL query to exclude client IDs 27, 72, and 436
    $query = "SELECT projects.projectID, projects.projectName, clients.clientName, projects.directMaterialcost, projects.directMaterialcostPrev, projects.dmPrevYear, projects.contractValue
                FROM projects
                INNER JOIN clients ON projects.clientID = clients.clientID
                WHERE clients.clientID NOT IN (27, 72, 436)
                ORDER BY clients.clientName ASC";
    
    $result = mysqli_query($conn, $query);
    return $result;
}

function getBycId($tableName,$clientID)
{
    global $conn;
    $table = validate($tableName);
    $clientID = validate($clientID);
    $query = "SELECT * from $table WHERE clientID='$clientID' LIMIT 1";
    $result = mysqli_query($conn,$query);

    if($result)
    {
        if(mysqli_num_rows($result)==1)
        {
            $row = mysqli_fetch_array($result, MYSQLI_ASSOC);
            $response = [
                'status' => 200,
                'message' => 'Fetched Data',
                'data' => $row
            ];
            return $response;
        }
        else
        {
            $response = [
                'status' => 404,
                'message' => 'No Record Found'
            ];
            return $response;
        }
    }
    else
    {
        $response = [
            'status' => 500,
            'message' => 'Something Went Wrong'
        ];
        return $response;
    }
}

function getBymId($tableName,$projectID)
{
    global $conn;
    $table = validate($tableName);
    $userID = validate($projectID);
    $query = "SELECT * from $table WHERE projectID='$projectID' LIMIT 1";
    $result = mysqli_query($conn,$query);

    if($result)
    {
        if(mysqli_num_rows($result)==1)
        {
            $row = mysqli_fetch_array($result, MYSQLI_ASSOC);
            $response = [
                'status' => 200,
                'data' => $row
            ];
            return $response;
        }
        else
        {
            $response = [
                'status' => 404,
                'message' => 'No Record Found'
            ];
            return $response;
        }
    }
    else
    {
        $response = [
            'status' => 500,
            'message' => 'Something Went Wrong'
        ];
        return $response;
    }
}

function getBypId($tableName,$projectID)
{
    global $conn;
    $table = validate($tableName);
    $userID = validate($projectID);
    $query = "SELECT * from $table WHERE projectID='$projectID'";
    $result = mysqli_query($conn,$query);

    if($result)
    {
        if(mysqli_num_rows($result)>0)
        {
            $row = mysqli_fetch_array($result, MYSQLI_ASSOC);
            $response = [
                'status' => 200,
                'data' => $row
            ];
            return $response;
        }
        else
        {
            $response = [
                'status' => 404,
                'message' => 'No Record Found'
            ];
            return $response;
        }
    }
    else
    {
        $response = [
            'status' => 500,
            'message' => 'Something Went Wrong'
        ];
        return $response;
    }
}

function calculateSumMilestoneValues($projectID, $newMilestoneValue, $conn)
{
    // Query to calculate the sum of milestone values for the project
    $query = "SELECT SUM(milestoneValue) AS totalMilestoneValue FROM milestones WHERE projectID = '$projectID'";

    // Execute the query
    $result = mysqli_query($conn, $query);

    if ($result) {
        $row = mysqli_fetch_assoc($result);
        $totalMilestoneValue = $row['totalMilestoneValue'];
        
        // Add the value of the new milestone to the sum
        $totalMilestoneValue += $newMilestoneValue;

        return $totalMilestoneValue;
    } else {
        // Handle database query error here
        return false;
    }
}

function getContractValue($projectID, $conn)
{
    // Query to retrieve the contract value for the project
    $query = "SELECT contractValue FROM projects WHERE projectID = '$projectID'";

    // Execute the query
    $result = mysqli_query($conn, $query);

    if ($result) {
        $row = mysqli_fetch_assoc($result);
        $contractValue = $row['contractValue'];

        return $contractValue;
    } else {
        // Handle database query error here
        return false;
    }
}

function getContractDetails($contractID) {
    global $conn;

    // Validate and sanitize input
    $contractID = validate($contractID);

    // Query to get contract details based on contractID
    $query = "SELECT * FROM contracts WHERE contractID = '$contractID'";
    $result = mysqli_query($conn, $query);

    if ($result && mysqli_num_rows($result) > 0) {
        // Fetch contract details as an associative array
        $contractDetails = mysqli_fetch_assoc($result);
        return $contractDetails;
    } else {
        // Return false if no contract details found
        return false;
    }
}

?>