<?php
if (!empty($_POST['FirstFilterValue'])) {
    $fran_sql = mysqli_query($con, "SELECT uid FROM users WHERE uname = '{$_POST['FirstFilterValue']}' AND utype = 'Associate' ");
    if($fran_row = mysqli_fetch_assoc($fran_sql)){
        $fran_id = $fran_row["uid"];
    }
    $conditions[] = "pFranchiee = '" . $fran_id . "'";
}


if (!empty($_POST['SecondFilterValue'])) {
    $emp_sql = mysqli_query($con, "SELECT uid FROM users WHERE uname = '{$_POST['SecondFilterValue']}' AND utype = 'Employee' ");
    if($emp_row = mysqli_fetch_assoc($emp_sql)){
        $emp_id = $emp_row["uid"];
    }
    $conditions[] = "pEmployee = '" . $emp_id . "'";
}

if (!empty($_POST['college'])) {
    $clg_id = $_POST['college'];
    $conditions[] = "pCollege = '" . $clg_id . "'";
}

if (!empty($_POST['course'])) {
    $course_id = $_POST['course'];
    $conditions[] = "product = '" . $course_id. "'";
}

if (!empty($_POST['StatusValue'])) {
    $conditions[] = "pStatus = '" . $_POST['StatusValue'] . "'";
}

// Add date range filters
if (!empty($_POST['StartDate']) && !empty($_POST['EndDate'])) {
    $startDate = mysqli_real_escape_string($con, $_POST['StartDate']);
    $endDate = mysqli_real_escape_string($con, $_POST['EndDate']);
    $conditions[] = "pCreateDate BETWEEN '$startDate' AND '$endDate'";  
} elseif (!empty($_POST['StartDate'])) {
    $startDate = mysqli_real_escape_string($con, $_POST['StartDate']);
    $conditions[] = "pCreateDate >= '$startDate'"; 
} elseif (!empty($_POST['EndDate'])) {
    $endDate = mysqli_real_escape_string($con, $_POST['EndDate']);
    $conditions[] = "pCreateDate <= '$endDate'";  
}
?>