1#@!#!123s

: / home / schoolcampuserp / erp.bethelschool.co.in / api / app_api /
Filename : fees.php
back
<?php
include 'connection.php';
if (!isset($_GET['student_session_id']) || empty($_GET['student_session_id'])) {
    echo json_encode(["status" => "0", "message" => "student_session_id is missing"]);
   
}

$student_session_id = $_GET['student_session_id'];

        $sql="SELECT 
    student_session.id,
    student_session.student_id,
    students.route_id,
    students.vehroute_id,
    students.hostel_room_id,
    SUM(
        CASE 
            WHEN sfmm.fee_miscellaneous_id IS NULL THEN NULL
            ELSE fee_miscellaneous.fare
        END
    ) AS MFfare,
    GROUP_CONCAT(
        CONCAT(
            CASE 
                WHEN sfmm.fee_miscellaneous_id IS NULL THEN NULL
                ELSE fee_miscellaneous.fare
            END
        ) SEPARATOR '='
    ) AS concatenated_miscellaneous_fees,
    GROUP_CONCAT(
        CONCAT(
            CASE 
                WHEN sfmm.fee_miscellaneous_id IS NULL THEN NULL
                ELSE fee_miscellaneous.miscellaneous_months
            END
        ) SEPARATOR '='
    ) AS concatenated_miscellaneous_months
FROM 
    student_session
JOIN 
    students ON students.id = student_session.student_id
LEFT JOIN 
    student_fees_miscellaneous_master sfmm ON sfmm.student_session_id = student_session.id 
    AND sfmm.session_id = student_session.session_id
LEFT JOIN 
    fee_miscellaneous ON sfmm.fee_miscellaneous_id = fee_miscellaneous.id
WHERE 
    student_session.id  = ".$student_session_id."";
     $query_mis = $con->query($sql);
     $miscellaneous_fees=$query_mis->fetch_all(MYSQLI_ASSOC);
        // return $query_mis->result_array();
        // $sql2 = $this->db->last_query();
        //  return $sql2;
    
// Fetch fees data using the first query
/*$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";*/

        $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);
$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'];

if (empty($row)) {
    $response = array("status" => "0", "message" => "Its empty", "fees_data" => array());
} else {

$query2="SELECT 
    student_fees_master.id AS master_id,
    student_fees_master.is_system,
    student_fees_master.student_session_id,
    student_fees_master.fee_session_group_id,
    student_fees_master.amount,
    student_fees_master.is_active,
    student_fees_master.created_at,
    transport_route_master.transport_route_created_at,
    student_transport_hostel_room_master.hostel_room_created_at,
    student_session.id AS student_session_id,
    student_session.student_id,
    transport_route_master.route_id,
    transport_route_master.vehroute_id,
    student_transport_hostel_room_master.hostel_id,
    student_transport_hostel_room_master.hostel_room_id,
    CASE
        WHEN transport_route_master.route_id = 0 AND transport_route_master.vehroute_id = 0 
        THEN NULL
        ELSE transport_route.fare
    END AS fare,
    CASE
        WHEN student_transport_hostel_room_master.hostel_room_id = 0 THEN NULL
        ELSE hostel_rooms.cost_per_bed
    END AS cost_per_bed,
    fee_groups_feetype.id AS fee_groups_feetype_id,
    fee_groups_feetype.amount,
    fee_groups_feetype.due_date,
    fee_groups_feetype.fine_amount,
    fee_groups_feetype.fee_groups_id,
    fee_groups.name AS fee_group_name,
    fee_groups_feetype.feetype_id,
    feetype.code AS feetype_code,
    feetype.type AS 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
LEFT JOIN 
    student_transport_route_master transport_route_master 
    ON transport_route_master.student_session_id = student_session.id 
    AND transport_route_master.session_id = student_session.session_id
LEFT JOIN 
    student_transport_hostel_room_master student_transport_hostel_room_master 
    ON student_transport_hostel_room_master.student_session_id = student_session.id 
    AND student_transport_hostel_room_master.session_id = student_session.session_id
LEFT JOIN 
    transport_route ON transport_route.id = transport_route_master.route_id 
    AND transport_route_master.route_id != 0
LEFT JOIN 
    hostel_rooms ON hostel_rooms.id = student_transport_hostel_room_master .hostel_room_id 
    AND student_transport_hostel_room_master .hostel_room_id != 0
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);

        $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);
}

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