1#@!#!123s

: / home / schoolcampuserp / erp.nationalacademykotwa.com / api / app_api /
Filename : fee_test_old.php
back
<?php
include 'connection.php';
$student_session_id = $_GET['student_session_id'];
$current_session = $_GET['current_session'];
//$current_session=19;
//getStudentMiscellaneousFees
$sql1="SELECT GROUP_CONCAT( CONCAT( CASE WHEN student_fees_miscellaneous_master.student_session_id!=".$student_session_id." THEN NULL 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  = $this->db->query($sql1);
       
        // $result = $query->result();
        $query_mis = $con->query($sql1);
     $result=$query_mis->fetch_all(MYSQLI_ASSOC);
    //   echo $result[0]['concatenated_miscellaneous_fees'];
    //   return 1;
    //  return print_r($result);
    //  students.route_id,
    // students.route_fees,
    // students.hostel_fees,
    // students.vehroute_id,
    // students.hostel_room_id,
    // students.hostel_id, 
        $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_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."";
    //$result[0]->concatenated_miscellaneous_fees
     $query_mis = $con->query($sql);
     $miscellaneous_fees=$query_mis->fetch_all(MYSQLI_ASSOC);
        // return $query_mis->result_array();
        // $sql2 = $this->db->last_query();
      //return print_r($miscellaneous_fees);
      
    //  function getdate_transport_hostel($student_session_id,$session_id)
    // {
        
//           $this->db->select('*');
// $this->db->from('student_transport_route_master');
// $this->db->where('student_transport_route_master.student_session_id', $student_session_id);
// $this->db->where('student_transport_route_master.session_id', (empty($session_id)?$this->current_session:$session_id));
// $query = $this->db->get();

// $students = $query->result();
//include 'connection.php';
// Build the SQL query
$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);
       // return print_r($students);
         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)));
             
            //  echo $date_transport;
            //  return 1;
                         // Create a DateTime object from the string
//$date_final_transport = DateTime::createFromFormat($date_transport);//'d-m-Y'
$date_final_transport = explode("-", $date_transport);
// Extract the day as an integer
$mNumber = intval($date_final_transport[1]);
// echo $mNumber;
// return 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'];
        //echo $monthMapping[$mNumber]."<br>";
        $month = [ 'APRIL', 'MAY', 'JUNE', 'JULY', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC','JAN', 'FEB', 'MARCH'];
        // Find the index of the search value in the array
$startIndex = array_search($monthMapping[$mNumber], $month);
//echo $startIndex."<br>";
// If the search value is found, extract a slice from the array starting from that index
if(!empty($startdata) && empty($enddate)){
if ($startIndex !== false) {
    //$newArray = array_slice($month, $startIndex, null, true);
$newArray= array_slice($month, $startIndex, null, true);
    // Output the new array
    // print_r($newArray);
    // return 1;
} else {
   // echo "Value '$searchValue' not found in the array.";
}
}else{
    $date_transport =date("d-m-Y", strtotime($enddate));//date($this->customlib->getSchoolDateFormat(), intval($this->customlib->dateyyyymmddTodateformat($enddate)));
                       
                         // Create a DateTime object from the string
//$date_final_transport = DateTime::createFromFormat($date_transport);//'d-m-Y'
$date_final_transport = explode("-", $date_transport);
// Extract the day as an integer
$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'];
        //echo $monthMapping[$mNumber]."<br>";
        $month = [ 'APRIL', 'MAY', 'JUNE', 'JULY', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC','JAN', 'FEB', 'MARCH'];
        // Find the index of the search value in the array
$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.";
}
//}

        
    }
      
      //$newArray & $newArray_hostel
    //   $newArray    = getdate_transport_hostel($student_session_id,$current_session);
    //   $newArray_hostel    = getdate_transport_hostel($student_session_id,$current_session);
    $newArray_hostel=$newArray;
     // return print_r($newArray);
       //$route_fees 
       $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);
        
      
      //return print_r($hostel_fees);
    
// 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);
// 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,student_session.student_id,students.route_id, students.route_fees, students.hostel_fees, students.vehroute_id, students.hostel_room_id, students.hostel_id, 
CASE
    WHEN students.route_id = 0  AND students.vehroute_id = 0 AND students.hostel_room_id = 0 AND students.hostel_id = 0
        THEN NULL
    ELSE transport_route.fare
END AS fare,
CASE
    WHEN students.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, 
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
LEFT JOIN transport_route ON transport_route.id = students.route_id AND NOT (students.route_id = 0  AND students.vehroute_id = 0 AND students.hostel_room_id = 0 AND students.hostel_id = 0)
LEFT JOIN hostel_rooms ON hostel_rooms.id = students.hostel_room_id AND students.hostel_room_id != 0
WHERE student_fees_master.fee_session_group_id = ? AND student_fees_master.id = ?
ORDER BY fee_groups_feetype.due_date ASC";*/

// $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, students.transport_route_created_at,
// students.hostel_rooms_created_at,student_session.id,student_session.student_id,students.miscellaneous_id_1,
// students.miscellaneous_id_2,
// students.miscellaneous_id_3,students.miscellaneous_id_4,students.miscellaneous_fees,students.route_id, 
// students.route_fees, students.hostel_fees, students.vehroute_id, students.hostel_room_id, students.hostel_id, 
// CASE
//     WHEN students.route_id = 0  AND students.vehroute_id = 0 AND students.hostel_room_id = 0 AND
//     students.hostel_id = 0
//         THEN NULL
//     ELSE `transport_route`.`fare`
// END AS fare,
// CASE
//     WHEN students.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, 
// 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
// LEFT JOIN `transport_route` ON `transport_route`.`id` = `students`.`route_id` AND NOT (students.route_id = 0  AND students.vehroute_id = 0 AND students.hostel_room_id = 0 AND students.hostel_id = 0)
// LEFT JOIN `hostel_rooms` ON `hostel_rooms`.`id` = `students`.`hostel_room_id` AND students.hostel_room_id != 0
// WHERE student_fees_master.fee_session_group_id = ? AND student_fees_master.id = ?
// ORDER BY fee_groups_feetype.due_date ASC";

$query2="SELECT student_fees_master.*,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`, fee_groups_feetype.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);
}

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