1#@!#!123s

: / home / schoolcampuserp / erp.nationalacademykotwa.com / api / app_api /
Filename : fees_test.php
back
<?php
include 'connection.php';

$student_session_id = $_REQUEST['student_session_id'] ?? null;
$current_session = $_REQUEST['current_session'] ?? null;


if (!isset($student_session_id))
    die(json_encode(["error" => "Missing parameters: student_session_id"]));


if (!isset($current_session)) 
    die(json_encode(["error" => "Missing parameters:current_session"]));


$sql1 = "SELECT GROUP_CONCAT(
            CASE 
                WHEN student_fees_miscellaneous_master.student_session_id <> $student_session_id 
                THEN '' 
                ELSE student_fees_miscellaneous_master.fee_miscellaneous_id 
            END 
            SEPARATOR ','
        ) AS concatenated_miscellaneous_fees 
        FROM student_fees_miscellaneous_master 
        WHERE student_fees_miscellaneous_master.student_session_id = $student_session_id 
        AND student_fees_miscellaneous_master.session_id = $current_session";

$query_mis = $con->query($sql1);

if (!$query_mis) {
    die("SQL Error: " . $con->error); // Debugging SQL error
}

$result = $query_mis->fetch_all(MYSQLI_ASSOC);

if (!empty($result[0]['concatenated_miscellaneous_fees'])) {

        $sql="SELECT 
    student_session.id,
    student_session.student_id,
    SUM( fee_miscellaneous.fare
    ) AS MFfare,
    GROUP_CONCAT(
        CONCAT(
    
        fee_miscellaneous.fare
    
            
            
        )
        SEPARATOR '='
    ) AS concatenated_miscellaneous_fees,
    GROUP_CONCAT(
        CONCAT(
    
        fee_miscellaneous.miscellaneous_title
    
            
            
        )
        SEPARATOR '='
    ) AS concatenated_miscellaneous_title,
     GROUP_CONCAT(
        CONCAT(
            
                fee_miscellaneous.miscellaneous_months
            
            
        )
        SEPARATOR '='
    ) AS concatenated_miscellaneous_months
FROM 
    student_session
JOIN 
    students ON students.id = student_session.student_id
LEFT JOIN 
    `fee_miscellaneous` ON (
        `fee_miscellaneous`.`id` IN (".
           $result[0]['concatenated_miscellaneous_fees']."
        )
        
    )
WHERE 
    student_session.id = ".$student_session_id."";

     $query_mis = $con->query($sql);
     $miscellaneous_fees=$query_mis->fetch_all(MYSQLI_ASSOC);
}

$sql = "SELECT * FROM student_transport_route_master 
        WHERE student_transport_route_master.student_session_id = ".$student_session_id."
        AND student_transport_route_master.session_id = " . $current_session;
 $query_mis = $con->query($sql);
     $students=$query_mis->fetch_all(MYSQLI_ASSOC);
       
         foreach ($students as $result) {
            // $startdata= $result->transport_route_created_at;
            // $enddate= $result->transport_route_deleted_at;
            $startdata = $result['transport_route_created_at'];
    $enddate = $result['transport_route_deleted_at'];

}
unset($students);
        $date_transport =date("d-m-Y", strtotime($startdata));//date($this->customlib->getSchoolDateFormat(), intval($this->customlib->dateyyyymmddTodateformat($startdata)));
     
$date_final_transport = explode("-", $date_transport);

$mNumber = intval($date_final_transport[1]);

$monthMapping =[ 1 => 'JAN', 2 => 'FEB', 3 => 'MARCH', 4 => 'APRIL', 5 => 'MAY',6 => 'JUNE',7 => 'JULY',8 => 'AUG',9 => 'SEP',10 => 'OCT',11 => 'NOV',12 => 'DEC'];
     
        $month = [ 'APRIL', 'MAY', 'JUNE', 'JULY', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC','JAN', 'FEB', 'MARCH'];

$startIndex = array_search($monthMapping[$mNumber], $month);

if(!empty($startdata) && empty($enddate)){
if ($startIndex !== false) {
$newArray= array_slice($month, $startIndex, null, true);
  
} else {
   
}
}else{
    $date_transport =date("d-m-Y", strtotime($enddate));//date($this->customlib->getSchoolDateFormat(), intval($this->customlib->dateyyyymmddTodateformat($enddate)));
                       
$date_final_transport = explode("-", $date_transport);

$mNumber = intval($date_final_transport[1]);//(int)$date_transport->format('m');
$monthMapping =[ 1 => 'JAN', 2 => 'FEB', 3 => 'MARCH', 4 => 'APRIL', 5 => 'MAY',6 => 'JUNE',7 => 'JULY',8 => 'AUG',9 => 'SEP',10 => 'OCT',11 => 'NOV',12 => 'DEC'];
       
        $month = [ 'APRIL', 'MAY', 'JUNE', 'JULY', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC','JAN', 'FEB', 'MARCH'];
    
$endIndex = array_search($monthMapping[$mNumber], $month);

    if ($endIndex !== false) {
    //$newArray = array_slice($month, $startIndex, $endIndex, true);
$newArray= array_slice($month, $startIndex, $endIndex, true);//$endIndex+1
    // Output the new array
    //print_r($newArray);
} else {
   // echo "Value '$searchValue' not found in the array.";
}
//}

        
    }
   
       $sql = "SELECT *, transport_route.fare,transport_route.route_title 
        FROM student_transport_route_master
        JOIN transport_route ON transport_route.id = student_transport_route_master.route_id 
        WHERE student_transport_route_master.student_session_id = ".$student_session_id." AND student_transport_route_master.session_id = ".$current_session;//route_id
    
     $query_mis = $con->query($sql);
     $route_fees=$query_mis->fetch_all(MYSQLI_ASSOC);
     
         $sql = "SELECT *, hostel_rooms.cost_per_bed 
        FROM `student_transport_hostel_room_master` 
        JOIN hostel_rooms ON hostel_rooms.id = student_transport_hostel_room_master.hostel_room_id
        WHERE student_transport_hostel_room_master.student_session_id = ".$student_session_id." AND student_transport_hostel_room_master.session_id = ".$current_session;//route_id
    
     $query_mis = $con->query($sql);
     $hostel_fees=$query_mis->fetch_all(MYSQLI_ASSOC);
        
  $query    = "SELECT `student_fees_master`.*,
        fee_groups.name FROM `student_fees_master` 
        INNER JOIN fee_session_groups on student_fees_master.fee_session_group_id=fee_session_groups.id 
        INNER JOIN fee_groups on fee_groups.id=fee_session_groups.fee_groups_id
        JOIN `student_session` ON `student_session`.`id` = `student_fees_master`.`student_session_id` 
        WHERE `student_session_id` = " . $student_session_id . " ORDER BY `student_fees_master`.`id`";


$result = $con->query($query);
$row = $result->fetch_all(MYSQLI_ASSOC);
// return print_r($row);

$query_discount = "SELECT student_fees_discounts.id, student_fees_discounts.student_session_id, student_fees_discounts.status, student_fees_discounts.payment_id, student_fees_discounts.description as student_fees_discount_description, student_fees_discounts.fees_discount_id, fees_discounts.name, fees_discounts.code, fees_discounts.amount, fees_discounts.description, fees_discounts.session_id FROM student_fees_discounts JOIN fees_discounts ON fees_discounts.id = student_fees_discounts.fees_discount_id WHERE student_fees_discounts.student_session_id = " . $student_session_id . " ORDER BY student_fees_discounts.id LIMIT 1";

$result_discount = $con->query($query_discount);
$row_discount = $result_discount->fetch_assoc();

$discount = $row_discount['amount'];
//return print_r($row_discount);

// Check if the result is empty or not and create the response
if (empty($row)) {
    $response = array("status" => "0", "message" => "Its empty", "fees_data" => array());
} else {
   

$query2="SELECT student_fees_master.*,student_session.id as student_session_id,student_session.student_id,students.route_id,students.vehroute_id, students.hostel_room_id, 
fee_groups_feetype.id as `fee_groups_feetype_id`,
CASE 
    WHEN student_fees_master.amount != 0.00 THEN student_fees_master.amount
    ELSE fee_groups_feetype.amount
END AS amount,
fee_groups_feetype.due_date, fee_groups_feetype.fine_amount, fee_groups_feetype.fee_groups_id, fee_groups.name, 
fee_groups_feetype.feetype_id, feetype.code, feetype.type, IFNULL(student_fees_deposite.id, 0) as `student_fees_deposite_id`, 
IFNULL(student_fees_deposite.amount_detail, 0) as `amount_detail`

FROM `student_fees_master`

JOIN student_session on student_session.id=?
JOIN students ON students.id = student_session.student_id
INNER JOIN fee_session_groups ON fee_session_groups.id = student_fees_master.fee_session_group_id
INNER JOIN fee_groups_feetype ON fee_groups_feetype.fee_session_group_id = fee_session_groups.id
INNER JOIN fee_groups ON fee_groups.id = fee_groups_feetype.fee_groups_id
INNER JOIN feetype ON feetype.id = fee_groups_feetype.feetype_id
LEFT JOIN student_fees_deposite ON student_fees_deposite.student_fees_master_id = student_fees_master.id AND student_fees_deposite.fee_groups_feetype_id = fee_groups_feetype.id
WHERE student_fees_master.fee_session_group_id = ? AND student_fees_master.id = ?
ORDER BY fee_groups_feetype.due_date ASC";

    // Prepare the statement for the second query
    $stmt2 = $con->prepare($query2);

    // An array to store the results of the second query
    $flattenedFees = array();
//print_r($row);
    // Loop through each row of the first query result and run the second query
    foreach ($row as &$rowItem) {
        $feeSessionGroupId = $rowItem['fee_session_group_id'];
        $studentSessionId = $rowItem['id'];

        // Bind parameters to the prepared statement
        $stmt2->bind_param("iii", $student_session_id,$feeSessionGroupId, $studentSessionId);

        // Execute the second query
        $stmt2->execute();

        // Get the result set of the second query
        $result2 = $stmt2->get_result()->fetch_all(MYSQLI_ASSOC);
//return print_r($stmt2->fetch_all(MYSQLI_ASSOC));
// print_r($result2);
// echo "<br><br>new<br><br>";
        // Merge the results of the second query into the flattened array using array_push
        //$row->fess=$result2;
        $rowItem['fees']=$result2;
        
        foreach ($result2 as $item) {
            array_push($flattenedFees, $item);
        }
    }

    // Close the statement of the second query
    $stmt2->close();
    
    foreach ($flattenedFees as &$item) {
    $item['assigned_discount'] = $discount;
}


    // Update the response with the flattened fees data
    //"fees_data" => $flattenedFees,
    $response = array("status" => "1", "message" => "Its Available", "row_fees_data"=>$row,"fees_data" => $flattenedFees,"miscellaneous_fees"=>$miscellaneous_fees,"newArray"=>$newArray,"newArray_hostel"=>$newArray_hostel,"route_fees"=>$route_fees,"hostel_fees"=>$hostel_fees);
}


// =========================
// ADD TOTAL DUE CALCULATION (UP TO TODAY) INCLUDING FINE
// =========================

// Default value
$total_due = 0;

// Get route fare
$routeFare = isset($route_fees[0]['fare']) ? floatval($route_fees[0]['fare']) : 0;

// Transport start month (if transport exists)
$routeStartDate = isset($route_fees[0]['transport_route_created_at'])
    ? date('Y-m-01', strtotime($route_fees[0]['transport_route_created_at']))
    : null;

// Today's date
$today = strtotime(date('Y-m-d'));

// Loop through fees_data
foreach ($response['fees_data'] as &$fee) {

    $feeDate = strtotime($fee['due_date']);

    // Skip fees that are not due yet
    if ($feeDate > $today) {
        $fee['amount_display'] = '';
        continue;
    }

    // Start with ERP balance
    $monthly_due = floatval($fee['balance']);

    // Include fine only if there is balance (unpaid)
    $fine_due = ($fee['balance'] > 0) ? floatval($fee['fine_amount']) : 0;

    // Add route fare only after transport starts
    if ($routeStartDate && $fee['due_date'] >= $routeStartDate) {
        $monthly_due += $routeFare;
        $fee['fare_applied'] = $routeFare; // For app usage
    } else {
        $fee['fare_applied'] = 0;
    }

    // Add fine to monthly_due for total calculation
    $monthly_due += $fine_due;

    // Add to final total_due
    $total_due += $monthly_due;

    // Build amount_display: show fine + fare (only if > 0)
    $amount_parts = [];
    if ($fine_due > 0) {
        $amount_parts[] = $fine_due;
    }
    if ($fee['fare_applied'] > 0) {
        $amount_parts[] = $fee['fare_applied'];
    }

    $fee['amount_display'] = implode(' + ', $amount_parts);

    // Update balance after adding fare & fine
    $fee['balance'] = floatval($fee['balance']) + $fee['fare_applied'] + $fine_due;
}

// Attach total_due to final response
$response['total_due'] = $total_due;

// Convert the response to JSON and send it
echo json_encode($response);

?>