<?php
session_start();
if (!isset($_SESSION['id'])) {
   header("location: index.php");
}
?>

<?PHP
//set_time_limit(300);
include 'connection.php';
// error_reporting(0);

$id = $_SESSION['id'];


// $sql = "SELECT * FROM NAA_MEMBERS WHERE MEM_ID = '$id'";
$sql = "select yr_of_pass,mem_mobile_no
from naa_members
where MEM_ID = '$id'";
// print_r($sql);

    $parse = ociparse($conn, $sql);
    oci_execute($parse);

    // print_r($sql);
    while ($row = oci_fetch_assoc($parse)) {
        $mem_mobile_no[] = $row;
    }
$yr_of_pass = $mem_mobile_no[0]['YR_OF_PASS'];
$mem_mobile_no = $mem_mobile_no[0]['MEM_MOBILE_NO'];
// echo "$yr_of_pass";


/* Retrive batch */
$curs = oci_new_cursor($conn);

$stid = oci_parse($conn, "begin PKG_DASH_BOARD.DPD_Div(:cur_data); end;");

oci_bind_by_name($stid, ":cur_data", $curs, -1, OCI_B_CURSOR);

oci_execute($stid);
oci_execute($curs);

while (($row = oci_fetch_array($curs, OCI_ASSOC + OCI_RETURN_NULLS)) != false) {

    $division[] = $row;
}


/* Retrive profession */
$curs = oci_new_cursor($conn);

$stid = oci_parse($conn, "begin PKG_DASH_BOARD.DPD_Prof(:cur_data); end;");

oci_bind_by_name($stid, ":cur_data", $curs, -1, OCI_B_CURSOR);

oci_execute($stid);
oci_execute($curs);

while (($row = oci_fetch_array($curs, OCI_ASSOC + OCI_RETURN_NULLS)) != false) {

    $profession[] = $row;
}



if (!isset($_POST['submit'])) {

    $DIV_CODE = null;
    $PRE_DIST = null;
    $PRE_THANA = null;
    $PROF_CODE = null;



    /* Start Procedure for Report */
    $curs = oci_new_cursor($conn);

    $stid = oci_parse($conn, "begin PKG_DASH_BOARD.DPD_mem_Details(:cur_data,:p_mem_mobile_no,:p_Div_code,:p_DIST_code,:p_THANA_code,:p_prof_code); end;");


    oci_bind_by_name($stid, ":cur_data", $curs, -1, OCI_B_CURSOR);
    oci_bind_by_name($stid, ":p_mem_mobile_no", $mem_mobile_no, -1, SQLT_CHR);
    oci_bind_by_name($stid, ":p_Div_code", $DIV_CODE, -1, SQLT_CHR);
    oci_bind_by_name($stid, ":p_DIST_code", $PRE_DIST, -1, SQLT_CHR);
    oci_bind_by_name($stid, ":p_THANA_code", $PRE_THANA, -1, SQLT_CHR);
    oci_bind_by_name($stid, ":p_prof_code", $PROF_CODE, -1, SQLT_CHR);



    oci_execute($stid);
    oci_execute($curs);


    while (($row = oci_fetch_array($curs, OCI_ASSOC + OCI_RETURN_NULLS)) != false) {

        $user_row[] = $row;
    }



// $sql = "select mem_photo, Mem_name,mem_mobile_no,coll_roll_no,yr_of_pass,PRE_ADDR,DIV_desc Div_name,dist_desc Dist_name,thana_desc Thana_name,pre_email email, e.prof_name, Designation Pre_post, office_name pre_office from naa_members a, NAA_DIVISION_MST b,NAA_DISTRICT_MST c,NAA_THANA_MST d ,NAA_PROF_MST e where A.PRE_DIV=B.DIV_CODE and b.DIV_CODE like nvl('','%') and A.PRE_DIST=C.DIST_CODE and c.DIST_CODE like nvl('','%') and A.PRE_THANA=D.THANA_CODE and d.Thana_code like nvl('','%') and A.PROF_CODE=E.PROF_CODE and e.prof_code like nvl('','%') and a.yr_of_pass like nvl('$yr_of_pass','%')";

//     // print_r($sql);
//     $parse = ociparse($conn, $sql);
//     oci_execute($parse);

//     // print_r($sql);
//     while ($row = oci_fetch_assoc($parse)) {
//         $user_row[] = $row;
//     }

//     oci_free_statement($parse);


    
}






// Handling form submission
if (isset($_POST['submit'])) {
    $DIV_CODE = $_POST['PRE_DIV'];
    $PRE_DIST = $_POST['PRE_DIST'];
    $PRE_THANA = $_POST['PRE_THANA'];
    $PROF_CODE = $_POST['PROF_CODE'];


    
    /* Start Procedure for Report */
    $curs = oci_new_cursor($conn);

    $stid = oci_parse($conn, "begin PKG_DASH_BOARD.DPD_mem_Details(:cur_data,:p_mem_mobile_no,:p_Div_code,:p_DIST_code,:p_THANA_code,:p_prof_code); end;");


    oci_bind_by_name($stid, ":cur_data", $curs, -1, OCI_B_CURSOR);
    oci_bind_by_name($stid, ":p_mem_mobile_no", $mem_mobile_no, -1, SQLT_CHR);
    oci_bind_by_name($stid, ":p_Div_code", $DIV_CODE, -1, SQLT_CHR);
    oci_bind_by_name($stid, ":p_DIST_code", $PRE_DIST, -1, SQLT_CHR);
    oci_bind_by_name($stid, ":p_THANA_code", $PRE_THANA, -1, SQLT_CHR);
    oci_bind_by_name($stid, ":p_prof_code", $PROF_CODE, -1, SQLT_CHR);



    oci_execute($stid);
    oci_execute($curs);


    while (($row = oci_fetch_array($curs, OCI_ASSOC + OCI_RETURN_NULLS)) != false) {

        $user_row[] = $row;
    }

    // Building SQL query based on form inputs and the year of passing
    // $sql = "SELECT mem_photo, Mem_name,mem_mobile_no,coll_roll_no,yr_of_pass,PRE_ADDR,DIV_desc Div_name,dist_desc Dist_name,thana_desc Thana_name,pre_email email,
    //         e.prof_name, Designation Pre_post, office_name pre_office 
    //         FROM naa_members a, NAA_DIVISION_MST b,NAA_DISTRICT_MST c,NAA_THANA_MST d ,NAA_PROF_MST e 
    //         WHERE A.PRE_DIV=B.DIV_CODE
    //         AND A.PRE_DIST=C.DIST_CODE
    //         AND A.PRE_THANA=D.THANA_CODE
    //         AND A.PROF_CODE=E.PROF_CODE
    //         AND a.yr_of_pass like nvl('$yr_of_pass','%')"; // Using year of passing from session
    // // Adding filter conditions based on form inputs
    // if (!empty($DIV_CODE)) {
    //     $sql .= " AND b.DIV_CODE like nvl('$DIV_CODE','%')";
    // }
    // if (!empty($PRE_DIST)) {
    //     $sql .= " AND c.DIST_CODE like nvl('$PRE_DIST','%')";
    // }
    // if (!empty($PRE_THANA)) {
    //     $sql .= " AND d.Thana_code like nvl('$PRE_THANA','%')";
    // }
    // if (!empty($PROF_CODE)) {
    //     $sql .= " AND e.prof_code like nvl('$PROF_CODE','%')";
    // }

    // // print_r($sql); // Printing the SQL query for debugging

    // $parse = ociparse($conn, $sql);
    // oci_execute($parse);
    // while ($row = oci_fetch_assoc($parse)) {
    //     $user_row[] = $row;
    // }
    // oci_free_statement($parse);

    // $test = $user_row[0]['MEM_NAME']; // Example usage of retrieved data
    // // echo "$test"; // Example output of retrieved data
}

?>


<!DOCTYPE html>
<html lang="en">

<head>

    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <meta name="description" content="">
    <meta name="author" content="">

    <title>NOTRE DAME COLLEGE ALUMNI ASSOCIATION Batch Wise List </title>
    <link rel="icon" href="img/ndc_75.png" type="image/ico">

    <!-- Custom fonts for this template -->
    <link href="vendor/fontawesome-free/css/all.min.css" rel="stylesheet" type="text/css">
    <link href="https://fonts.googleapis.com/css?family=Nunito:200,200i,300,300i,400,400i,600,600i,700,700i,800,800i,900,900i" rel="stylesheet">

    <!-- Custom styles for this template -->
    <link href="css/sb-admin-2.min.css" rel="stylesheet">

    <!-- datatable  -->
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.24/css/jquery.dataTables.css">
    <link href="https://nightly.datatables.net/buttons/css/buttons.dataTables.css?_=c6b24f8a56e04fcee6105a02f4027462.css" rel="stylesheet" type="text/css" />

    <style>
        /* for excel button design start */


        /* for excel button design start */
    </style>

</head>

<body id="page-top">

    <!-- Page Wrapper -->
    <div id="wrapper">

        <!-- Sidebar -->
        <?php include 'dashboard.php'?>

        <!-- End of Sidebar -->

        <!-- Content Wrapper -->
        <div id="content-wrapper" class="d-flex flex-column">

            <!-- Main Content -->
            <div id="content">

                <!-- Topbar -->
                <?php //include 'topbar.php' ?>

                <!-- End of Topbar -->

                <!-- Begin Page Content -->
                <div class="container-fluid" style="width: 1000px;">

                <div class="card shadow mb-4">
                        <div class="card-header py-3" style="background-color: #e8b742;;">
                            <!-- <h6 class="m-0 font-weight-bold text-primary text-center m-4">Suhrawardy Hall Alumni Association, BUET</h6> -->
                            <h4 class=" text-center m-4" style="color: #212121; font-weight: bold;">NOTRE DAME COLLEGE ALUMNI ASSOCIATION</h4>
                            <h4 class=" text-center m-4" style="color: #212121; font-weight: bold;">NOTRE DAME COLLEGE, DHAKA</h4>
                        </div>
                        <div class="card-body">
                            <!-- form start -->
                            <form action="" method="post">
                                <div class="row">


                                    <div class="col-sm-12 col-md-4">
                                        <label for="">Division:</label>
                                        <select name="PRE_DIV" id="p_division" class="form-control">
                                            <option value="">-Select Division</option>
                                            <?php
                                            for ($i = 0; $i < count($division); $i++) {


                                                if ($division[$i]["DIV_CODE"] == $DIV_CODE) {
                                                    echo "<option value='" . $division[$i]["DIV_CODE"] . "' selected>" . $division[$i]["DIV_DESC"] . "</option>";
                                                } else {
                                                    echo "<option value='" . $division[$i]["DIV_CODE"] . "'>" . $division[$i]["DIV_DESC"] . "</option>";
                                                }
                                            }

                                            ?>



                                        </select>
                                    </div>

                                    <div class="col-sm-12 col-md-4">
                                        <label for="">District:</label>
                                        <select name="PRE_DIST" id="p_district" class="form-control">
                                            <option value="">-Select District</option>
                                        </select>
                                    </div>


                                    <div class="col-sm-12 col-md-4">
                                        <label for="">Thana:</label>
                                        <select name="PRE_THANA" id="p_upazilla" class="form-control">
                                            <option value="">-Select Thana</option>
                                           
                                        </select>
                                    </div>



                                    <div class="col-sm-12 col-md-4">
                                        <label for="">Profession:</label>
                                        <select name="PROF_CODE" id="PROF_CODE" class="form-control">
                                            <option value="">-Select Profession</option>
                                            <?php
                                            for ($i = 0; $i < count($profession); $i++) {


                                                if ($profession[$i]["PROF_CODE"] == $PROF_CODE) {
                                                    echo "<option value='" . $profession[$i]["PROF_CODE"] . "' selected>" . $profession[$i]["PROF_NAME"] . "</option>";
                                                } else {
                                                    echo "<option value='" . $profession[$i]["PROF_CODE"] . "'>" . $profession[$i]["PROF_NAME"] . "</option>";
                                                }
                                            }

                                            ?>



                                        </select>
                                    </div>



                                    <div class="col-sm-12 col-md-4" style="justify-content:center;">
                                        <div style="margin-top: 30px;margin-left: 80px;">
                                            <input type="submit" name="submit" value="Load" class="btn btn-success">
                                        </div>

                                    </div>



                                </div>







                            </form>
                            <!-- form end -->

                        </div>
                    </div>

                    
                    
                        <!-- DataTales Example -->
                        <div class="card shadow mb-4">

                            <div class="card-body">
                                <div class="table-responsive">
                                    <table class="table table-bordered" id="table_id" width="100%" cellspacing="0">
                                        <thead style="color: #212121;">
                                            <tr>
                                                <th style="text-align: center;">SL No</th>
                                                <th style="text-align: center;">Member Photo</th>
                                                <th style="text-align: center;">Member Name</th>
                                                <th style="text-align: center;">Mobile No</th>
                                                <th style="text-align: center;">College Roll No</th>
                                                <!-- <th style="text-align: center;">Batch</th> -->
                                                <th style="text-align: center;">Profession Name</th>
                                                <th style="text-align: center;">Present Post</th>
                                                <th style="text-align: center;">Present Office</th>
                                                
                                                <th style="text-align: center;">Email</th>
                                                
                                                <th style="text-align: center;">Division</th>
                                                <th style="text-align: center;">District</th>
                                                <th style="text-align: center;">Thana</th>
                                                <th style="text-align: center;">Present Address</th>

                                        </thead>

                                        <tbody>
                                            <?php
                                            $serialNumber = 1;
                                           
                                            for ($i = 0; $i < count($user_row); $i++) {

                                                $abc = array(); // Initialize $abc for each row

                                                foreach ($user_row[$i] as $key => $data) {
                                                    if ($key === 'MEM_PHOTO') {
                                                        if (is_object($data) && get_class($data) === 'OCI-Lob') {
                                                            // Read binary data from CLOB
                                                            $lob = oci_new_descriptor($conn, OCI_D_LOB);
                                                            oci_lob_read($data, $lob);
                                                            $abc[$key] = base64_encode($lob->load());
                                                            $lob->free();
                                                        } else {
                                                            // Handle other types or strings here
                                                            $abc[$key] = is_object($data) ? $data->load() : $data;
                                                        }
                                                    } else {
                                                        $abc[$key] = $data;
                                                    }
                                                }
                                                

                                            ?>


                                                <tr style="color: black!important;">
                                                    <td style="text-align: center;"><?php echo $serialNumber; ?></td>

                                                    <td style="text-align: center;"><?php if (isset($abc['MEM_PHOTO'])) : ?>
                                                            <img src="data:image/jpeg;base64,<?php echo $abc['MEM_PHOTO']; ?>" id="output_image" width="60px" height="60px" />
                                                        <?php endif; ?></td>

                                                    <td style="text-align: center;"><?php echo $user_row[$i]['MEM_NAME'] ?></td>
                                                    
                                                    <td style="text-align: center;"><?php echo $user_row[$i]['MEM_MOBILE_NO'] ?></td>
                                                   
                                                    <td style="text-align: center;"><?php echo $user_row[$i]['COLL_ROLL_NO'] ?></td>
                                                    <!-- <td style="text-align: center;"><?php echo $user_row[$i]['YR_OF_PASS'] ?></td> -->

                                                    <td style="text-align: center;"><?php echo $user_row[$i]['PROF_NAME'] ?></td>
                                                    <td style="text-align: center;"><?php echo $user_row[$i]['PRE_POST'] ?></td>
                                                    
                                                    <td style="text-align: center;"><?php echo $user_row[$i]['PRE_OFFICE'] ?></td>
                                                    
                                                    
                                                    <td style="text-align: center;"><?php echo $user_row[$i]['EMAIL'] ?></td>
                                                   

                                                    <td style="text-align: center;"><?php echo $user_row[$i]['DIV_NAME'] ?></td>
                                                    <td style="text-align: center;"><?php echo $user_row[$i]['DIST_NAME'] ?></td>
                                                    <td style="text-align: center;"><?php echo $user_row[$i]['THANA_NAME'] ?></td>
                                                    <td style="text-align: center;">
                                                    <?php echo $user_row[$i]['PRE_ADDR'] ?>
                                                </td>
                                                   
                                                </tr>

                                            <?php
                                                $serialNumber++;
                                            }
                                            ?>

                                        </tbody>
                                    </table>
                                </div>
                            </div>
                        </div>

                       

                   
                </div>
                <!-- /.container-fluid -->



            </div>
            <!-- End of Main Content -->

            <!-- Footer -->
            <!-- <footer class="sticky-footer bg-white">
                <div class="container my-auto">
                    <div class="copyright text-center my-auto">
                    </div>
                </div>
            </footer> -->
            <!-- End of Footer -->

        </div>
        <!-- End of Content Wrapper -->

    </div>
    <!-- End of Page Wrapper -->

    <!-- Scroll to Top Button-->
    <a class="scroll-to-top rounded" href="#page-top">
        <i class="fas fa-angle-up"></i>
    </a>



    <!-- datatable start -->
    <script src="https://code.jquery.com/jquery-3.4.1.slim.min.js" integrity="sha384-J6qa4849blE2+poT4WnyKhv5vZF5SrPo0iEjwBvKU7imGFAV0wwj1yYfoRSJoZ+n" crossorigin="anonymous"></script>
    <script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.0/dist/umd/popper.min.js" integrity="sha384-Q6E9RHvbIyZFJoft+2mJbHaEWldlvI9IOYy5n3zV9zzTtmI3UksdQRVvoxMfooAo" crossorigin="anonymous"></script>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@4.4.1/dist/js/bootstrap.min.js" integrity="sha384-wfSDF2E50Y2D1uUdj0O3uMBJnjuUD4Ih7YwaYd1iqfktj0Uod8GCExl3Og8ifwB6" crossorigin="anonymous"></script>

    <script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.9.4/Chart.js"></script>

    <script type="text/javascript" src="https://code.jquery.com/jquery-3.5.1.js"></script>
    <script type="text/javascript" src="https://cdn.datatables.net/1.11.3/js/jquery.dataTables.min.js"></script>

    <script type="text/javascript" src="https://cdn.datatables.net/buttons/2.1.0/js/dataTables.buttons.min.js"></script>

    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
    <!-- <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js"></script> -->
    <!-- <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js"></script> -->

    <!-- excel starts  -->
    <script type="text/javascript" src="https://cdn.datatables.net/buttons/2.1.0/js/buttons.html5.min.js"></script>
    <script type="text/javascript" src="https://cdn.datatables.net/buttons/2.1.0/js/buttons.print.min.js"></script>
    <script type="text/javascript" charset="utf8" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.68/pdfmake.min.js"></script>
    <script type="text/javascript" charset="utf8" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.68/vfs_fonts.js"></script>
    <!-- excel and pdf script ends  -->


    <script>
		// district code cascading starts 
		$('#p_division').on('change', function() {
			var divisionID = $(this).val();

			// console.log(divisionID);
			if (divisionID) {
				$.ajax({
					type: 'POST',
					url: 'ajaxDataCascading_div_dist1.php',
					data: 'div_id=' + divisionID,
					success: function(html) {
						$('#p_district').html(html);
					}
				});
			} else {
				$('#p_district').html('<option value="">Select division first</option>');
			}
		});
		// distirct code cascading ends
	</script>


   <script>
		// district code cascading starts 
		$('#p_district').on('change', function() {
			var districtID = $(this).val();

			// console.log(divisionID);
			if (districtID) {
				$.ajax({
					type: 'POST',
					url: 'ajaxDataCascading_dis_upo.php',
					data: 'dis_id=' + districtID,
					success: function(html) {
						$('#p_upazilla').html(html);
					}
				});
			} else {
				$('#p_upazilla').html('<option value="">Select upazilla first</option>');
			}
		});
		// distirct code cascading ends
	</script>






    <!-- using online scripts -->

    
    <!-- <script>
        $(document).ready(function() {
            var dataTable = $('#table_id').DataTable({
                
            });
        });
    </script> -->




<script>
        $(document).ready(function() {
            var dataTable = $('#table_id').DataTable({
                dom: 'Bfrtip',
                buttons: [{
                    extend: 'excelHtml5', // Add Excel button
                        text: 'Download Excel',
                        titleAttr: 'Export to Excel',
                        className: 'excel-button', // Add a class to the Excel button
                        exportOptions: {
                            modifier: {
                                page: 'all'
                            }
                        }
                       
                    },
                    
                ]
            });
        });
    </script>






    <!-- <script>
        $(document).ready(function() {
            $('#table_id').DataTable({
                dom: 'lBfrtip',
                buttons: [
                    'excel'
                ]
            });
        });
    </script> -->

</body>

</html>



