<?php


ini_set('session.save_path',realpath(dirname($_SERVER['DOCUMENT_ROOT']) . '/var/cpanel/php/sessions/easytech-bpatcsc'));
session_start();

// Check user login or not
if(!isset($_SESSION['username'])){
   header('Location: admin.php');
}

?>
<!DOCTYPE html>
<html>
<head>
	<title>XLSx</title>
	<meta charset="utf-8">
  <!----<meta name="viewport" content="width=device-width, initial-scale=1">--->
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>
  <script src="https://code.jquery.com/jquery-3.1.0.min.js"></script>
  
<style>
body{ font: 14px sans-serif; }
.custom-file{
	//margin-left:400px;

}
h3{
	margin-left:0px;
}
.topnav {
  overflow: hidden;
  background-color: #333;
}

.topnav a {
  float: left;
  color: #f2f2f2;
  text-align: center;
  padding: 14px 16px;
  text-decoration: none;
  font-size: 17px;
}

.topnav a:hover {
  background-color: #ddd;
  color: black;
}

.topnav a.active {
  background-color: #4CAF50;
  color: white;
}

  td { 
            background-color: #E4F5D4; 
            border: 1px solid black; 
        } 
  
        th, 
        td { 
            font-weight: bold; 
            border: 1px solid black; 
            padding: 10px; 
            text-align: center; 
        } 
  
        td { 
		font-size: 14px;
            font-weight: lighter; 
        } 
	
   
.table-container {
  margin-left: 350px;
   margin-top:-28px;
}

.table_data{
     margin-left: 0px;
   margin-top:0px;
}  
.container{
    margin-left:100px;
}
th {
  background: purple;
  color: white;
  position: sticky;
  top: 0; /* Don't forget this, required for the stickiness */
  box-shadow: 0 2px 2px -1px rgba(0, 0, 0, 0.4);
}
</style>

</head>
<body>

   
<div class="topnav">
  
  <a href="zoom_link.php">Upload Zoom Link</a>
  <a href="access_code.php">Upload Access Code</a>
  <a class="active" href="student_list.php">Upload Student List</a>
  <a href="class_routine.php">Upload Class Routine</a>
  <a  href="sms.php">Send SMS</a>
  <a href="adminlogout.php">Logout</a>
  <a href="#about">About</a>
</div>


<div class="container">

<h3>Student List Upload:xlsx/xlsm</h3><br>

<form action="#" method="POST" enctype="multipart/form-data">
<p><a href="assets/student_list_sample.xlsx">Download Student List Sample Excel file</a></p>
<div class="custom-file mb-3">
 
      <input style="width:30%" type="file" class="custom-file-input" id="customFile" name="excel">
      <label style="width:30%" class="custom-file-label" for="customFile">Choose file</label><br><br>
    <input type="submit" class="btn btn-primary" name="submit" value="Upload">
    <input type="submit" class="btn btn-success" name="show" value="View Student List">
	</div>
</form>

</div>


<?php
if(isset($_POST['submit'])){
if(isset($_FILES['excel']['name'])){

  //echo $_FILES['excel']['name'];
	//$link=mysqli_connect("localhost","legendco_examuser","lc***2000###","legendco_exam");
	include "config.php";
	include "xlsx.php";
		
	if($link){
		
		//mysqli_query($link,"TRUNCATE TABLE student_class");  //delete all data from table
		
    $excel=SimpleXLSX::parse($_FILES['excel']['tmp_name']);
    //echo $_FILES['excel']['tmp_name'];
		//echo "<br><br><br><br>";
		echo "<pre>";	
				$excel->dimension(2);
		$excel->sheetNames();
        $sheet=0;
		$rowcol=$excel->dimension($sheet);
        		 $c=0;
        if($rowcol[0]!=1 &&$rowcol[1]!=1){
		foreach ($excel->rows($sheet) as $key => $row) {
			 $c++;
			//print_r($row);
      $q="";
      $ind=0;
      $column_name="";
			foreach ($row as $key => $cell) {
        $q.="'".$cell. "',";
        $columnarry=array("st_id","st_class","st_group","st_section","st_name",
      "father_name","st_mobile","st_email");
       $indval=$columnarry[$ind];
       $column_name.=$indval."='".$cell."',";
        $ind++;
                if ($ind==1){
                  $student_id=$cell;
                  $query=mysqli_query($link, "SELECT * FROM student_class WHERE st_id = $student_id ");
                }

				}
			if ($c>1){
      $date_today=date("Y-m-d");
      $column_name=rtrim($column_name,",");
//echo $column_name.'</br>';
      
  if (mysqli_num_rows($query) > 0){
//UPDATE table_name SET column1=value, column2=value2,... WHERE column_name=some_value
$query="UPDATE student_class SET ".$column_name." WHERE st_id= $student_id "   ;
$result=mysqli_query($link,$query);
  } else{

    $query = "insert into student_class (st_id,st_class,st_group,st_section,
    st_name,father_name,st_mobile,st_email) values (".rtrim($q,",").")";
    $result=mysqli_query($link,$query);
    //echo $query;

  }

					}
		}
		if($result){
		$tot=($c++)-1;
		echo "Total ".$tot." Rows added succesfully.";
		}
		}
		
		
		
		}
}
}

?>


<div class= "table-container">
<button type="button" class="btn btn-primary" id="download-list">Download Student List</button>
<div class="table_data" style="height: 300px; width: 900px; overflow: auto;">
<?php

date_default_timezone_set("Asia/Dhaka");


$today_time=date("h:i:sa");

include('config.php');
 
 if(isset($_POST['show'])){


$query ="SELECT * From student_class order by st_id ASC";		


	
//echo $query;

$result = mysqli_query($link,$query);
$serial_no=0;

     //if($result = $mysqli->query($query))
		 if($result){
		     
            echo '<table >';
            echo '<tr>';
            //echo '<th><input type="checkbox" id="checkAl"> Select All</th>';
			echo "<th>serial</th>";
            for($i=0;$i<mysqli_num_fields($result);$i++)
            {
                $column_info=mysqli_fetch_field_direct($result, $i); 
               /*this function returns all the information about table metioned in the query.
               variable i over here refers to field no*/
			   echo "<th>".$column_info->name."</th>"; //here fetching only name from whole information
            }
            //echo '<th>Picture</th></tr>';
			
            while ($row = mysqli_fetch_array($result))
            {
				$serial_no++;
                echo '<tr>';
                //echo '<td>'.$serial_no. '<input type="checkbox" id="checkItem" name="check[]" value="'.$row["st_id"].'"> </td>';
				 echo '<td>'.$serial_no.'</td>';
				 
                for($i=0;$i<mysqli_num_fields($result);$i++)
                {
                    echo '<td>'.$row[$i].'</td>';
                }
                //echo '<td><img src="http://legendcollegebd.org/student/assets/student/'.$row[0].'.jpg" alt="student pic" style="width:90px;height:90px;"></td></tr>';
           
			
			}
            echo '</table>';
        }
        else
        {
            echo "<script>alert('Error occured, Please check your syntax or internet connection')</script>";
        }



}
   
   
// close connection
mysqli_close($link);

?>
</div>
</div>



<script type="text/javascript" src="js/jquery.js"></script>

<script type="text/javascript">
          $(document).ready(function(){
          // Add the following code if you want the name of the file appear on select
                  $(".custom-file-input").on("change", function() {
                    var excel = $(this).val().split("\\").pop();
                    $(this).siblings(".custom-file-label").addClass("selected").html(excel);
                  });




                                $("#download-list").click(function(e){
                                    e.preventDefault();
                                    //alert("tyyyy");
                                    $.ajax({
                                                url : "student_list_exportxl.php",
                                                type : "POST",
                                                success: function() {
                                                 window.location = "student_list_exportxl.php";// you can use window.open also
                                                   }
                                        });
                                    
                                });









                                

          });


</script>

</body>
</html>