JezK

Edit File: upload_students.php

<?php
session_start();
include('session_check.php');
include('adm_operations.php');
//ini_set("display_errors",1);
$operations=new adm_operations();
$school_id=$_SESSION['child']['log_school_id'];
$acdid=$_SESSION['child']['choosen_academic_id'];
$me=$_SESSION['child']['auth_id'];
include("header.php");
require_once 'excel_reader2.php';
//$connection=mysql_connect("localhost","cimplify_dbclien","krify1234@","cimplify_dbmastercon");
$class=$operations->getallClassesforOrder($_SESSION['child']['log_school_id'],$_SESSION['child']['choosen_academic_id']);
	$clsoptions='<option value="0">--Select Class--</option>';
	$scnoptions='<option value="0">--Select Section--</option>';
	
	if(!empty($class))
	{	
		foreach($class as $cls)								
			if($_POST['class']==$cls['class_id'])
				$clsoptions.="<option  value='".$cls['class_id']."' selected>".$cls['class_name']."</option>";
			else
				$clsoptions.="<option  value='".$cls['class_id']."' >".$cls['class_name']."</option>";
	}
	
	$sections=$operations->getallsectionsdetailsbyclassId($_POST['class']);			
	if(!empty($sections))
	foreach($sections as $sec)
	{
		if($_POST['section']==$sec['section_id'])
			$scnoptions.="<option  value='".$sec['section_id']."' selected>".$sec['section_name']."</option>";
		else
			$scnoptions.="<option  value='".$sec['section_id']."'>".$sec['section_name']."</option>";
	}
	
			
if(isset($_POST['submit'])) 
{
//$data = new Spreadsheet_Excel_Reader("samplestudents.xls"); 
//echo "Total Sheets in this xls file: ".count($data->sheets)."<br /><br />";
$html="";
$roll=array();
$admis=array();
if(isset($_FILES['exl']['name']))
{
	$target_dir = "excel_uploads/";
	$target_file = $target_dir .date('his').basename($_FILES["exl"]["name"]);
	$FileType = pathinfo($target_file,PATHINFO_EXTENSION);
	if($FileType!='xls')
	{
		$errors='Please upload a valid Excel file and please do not upload any file other than the excel template provided under "Download Excel"';
	}
	else
	{		
		$blood=array("A+","B+","O+","AB+","A-","B-","O-","AB-");
		if (move_uploaded_file($_FILES["exl"]["tmp_name"], $target_file))
		{
			$data = new Spreadsheet_Excel_Reader($target_file);
			
			//for($i=0;$i<count($data->sheets);$i++) // Loop to get all sheets in a file.
			//{ 
				$i=1;
				if(count($data->sheets[$i][cells])>0) // checking sheet not empty
				{					
					for($j=1;$j<=count($data->sheets[$i][cells]);$j++) // loop used to get each row of the sheet
					{ 
							if($j>=3)
							{
								if(trim($data->sheets[$i][cells][$j][1])=='')
								{
									$html.="Row ".$j."- Please enter Admission Number<br>";
								}
								else if(strlen($data->sheets[$i][cells][$j][1])>50)
								{
									$html.="Row ".$j."- Admission Number field should have minimum 1 and maximum 50 characters<br>";
								}
								else if(!preg_match('/^[A-Za-z0-9 \-_.\/]+$/i', $data->sheets[$i][cells][$j][1]))
								{
									$html.="Row ".$j."- Please enter a valid Admission Number<br>";
								}
								else
								{
									$admis[]=trim($data->sheets[$i][cells][$j][1]);
								}
								
								if($data->sheets[$i][cells][$j][2]=='')
								{
									$html.="Row ".$j."- Please enter Admission Date<br>";
								}
								else if ($data->sheets[$i][cells][$j][2]!='')
								{
									$changedate=date("Y-m-d",strtotime($data->sheets[$i][cells][$j][2]));
									$date1=explode('-',$data->sheets[$i][cells][$j][2]);
									
									$admdate=$changedate;
									if(count($date1)<3)
									{
										$html.="Row ".$j."- Please enter a valid Admission Date<br>";
									}
									//~ else if(!preg_match("/^[0-9]{4}-(0[1-9]|1[0-2])-(0[1-9]|[1-2][0-9]|3[0-1])$/",$admdate))
									//~ {									
										//~ $html.="Row ".$j."- Please enter a valid Admission Date<br>";
									//~ }									
								}
								
								if(trim($data->sheets[$i][cells][$j][3])=='')
								{
									$html.="Row ".$j."- Please enter Student Roll Number<br>";
								}
								else if(strlen($data->sheets[$i][cells][$j][3])>20)
								{
									$html.="Row ".$j."- Student Roll Number should have minimum 1 and maximum 20 characters<br>";
								}
								else if(!preg_match('/^[a-zA-Z0-9 ]*$/',$data->sheets[$i][cells][$j][3]))
								{
									$html.="Row ".$j."- Please enter a valid Student Roll Number<br>";
								}
								else
								{
									$roll[]=trim($data->sheets[$i][cells][$j][3]);
								}
								if(trim($data->sheets[$i][cells][$j][4])=='')
								{
									$html.="Row ".$j."- Please enter Student First Name<br>";
								}
								else if(strlen($data->sheets[$i][cells][$j][4])>50)
								{
									$html.="Row ".$j."- Student First Name should have minimum 1 and maximum 50 characters<br>";
								}
								else if(!preg_match('/^[a-zA-Z0-9 ]*$/',$data->sheets[$i][cells][$j][4]))
								{
									$html.="Row ".$j."- Please enter a valid Student First Name<br>";
								}
								if(trim($data->sheets[$i][cells][$j][5])=='')
								{
									$html.="Row ".$j."- Please enter Student Last Name<br>";					
								}
								else if(strlen($data->sheets[$i][cells][$j][5])>50)
								{
									$html.="Row ".$j."- Student Last Name should have minimum 1 and maximum 50 characters<br>";		
								}
								else if(!preg_match('/^[a-zA-Z0-9 ]*$/',$data->sheets[$i][cells][$j][5]))
								{
									$html.="Row ".$j."- Please enter a valid Student Last Name<br>";						
								}
								if(trim($data->sheets[$i][cells][$j][6])== '')
								{
									$html.="Row ".$j."- Please enter Student Address<br>";					
								}
								else if(strlen($data->sheets[$i][cells][$j][6])>100)
								{
									$html.="Row ".$j."- Student Address should have maximum 100 characters<br>";	
								}
								else if(!preg_match('/^[A-Za-z0-9\n_@.,#;:\/\- ]+$/',$data->sheets[$i][cells][$j][6]))
								{
									$html.="Row ".$j."- Please enter valid Address<br>";
								}
								if(trim($data->sheets[$i][cells][$j][7])!='')
								{								
									if(!in_array($data->sheets[$i][cells][$j][7],$blood))
									{
										$html.="Row ".$j."-Please choose a valid blood group out of A+,B+,O+,AB+,A-,B-,O-,AB-<br>";
									}
								}
								if(trim($data->sheets[$i][cells][$j][8])== '')
								{
									$html.="Row ".$j."- Please enter Father Name<br>";						
								}
								else if(strlen($data->sheets[$i][cells][$j][8])>50)
								{
									$html.="Row ".$j."- Father Name should have minimum 1 and maximum 50 characters<br>";	
								}
								else if(!preg_match('/^[a-zA-Z0-9 ]*$/',$data->sheets[$i][cells][$j][8]))
								{
									$html.="Row ".$j."- Please enter a valid Father Name<br>";					
								}
								if(trim($data->sheets[$i][cells][$j][9]) == '')
								{
									$html.="Row ".$j."- Please enter Father Email Address<br>";						
								}
								else if(strlen($data->sheets[$i][cells][$j][9])>50)
								{
									$html.="Row ".$j."- Father Email should have minimum 1 and maximum 50 characters<br>";	
								}
								else if(!filter_var($data->sheets[$i][cells][$j][9], FILTER_VALIDATE_EMAIL))
								{
									$html.="Row ".$j."- Please enter a valid Father Email Address<br>";						
								}
								if(trim($data->sheets[$i][cells][$j][10])=='')
								{
									$html.="Row ".$j."- Please enter Father Mobile Number<br>";						
								}		
								else if(!preg_match('/^[0-9]*$/',$data->sheets[$i][cells][$j][10]))
								{
									$html.="Row ".$j."- Please enter a valid Father Mobile Number<br>";						
								}
								else if(strlen($data->sheets[$i][cells][$j][10])!=10)
								{
									$html.="Row ".$j."- Please enter a valid Father Mobile Number<br>";						
								}
								
								if($data->sheets[$i][cells][$j][11]!='')
								{
									if(!preg_match('/^[0-9]*$/',$data->sheets[$i][cells][$j][11]))
									{
										$html.="Row ".$j."- Please enter a valid Father Mobile Number2<br>";						
									}
									else if(strlen($data->sheets[$i][cells][$j][11])!=10)
									{
										$html.="Row ".$j."- Please enter a valid Father Mobile Number2<br>";						
									}						
								}		
								
													
								if(trim($data->sheets[$i][cells][$j][12])== '')
								{
									$html.="Row ".$j."- Please enter Mother Name<br>";						
								}
								else if(strlen($data->sheets[$i][cells][$j][12])>50)
								{
									$html.="Row ".$j."- Mother Name should have minimum 1 and maximum 50 characters<br>";	
								}
								else if(!preg_match('/^[a-zA-Z0-9 ]*$/',$data->sheets[$i][cells][$j][12]))
								{
									$html.="Row ".$j."- Please enter a valid Mother Name<br>";						
								}
								if(trim($data->sheets[$i][cells][$j][13]) == '')
								{
									$html.="Row ".$j."- Please enter Mother Email Address<br>";						
								}
								else if(strlen($data->sheets[$i][cells][$j][13])>50)
								{
									$html.="Row ".$j."- Mother Email should have minimum 1 and maximum 50 characters<br>";	
								}
								else if(!filter_var($data->sheets[$i][cells][$j][13], FILTER_VALIDATE_EMAIL))
								{
									$html.="Row ".$j."- Please enter a valid Mother Email Address<br>";						
								}		
								if(trim($data->sheets[$i][cells][$j][14]) == '')
								{
									$html.="Row ".$j."- Please enter Mother Mobile Number<br>";						
								}
								else if(!is_numeric($data->sheets[$i][cells][$j][14]))
								{
									$html.="Row ".$j."- Please enter a valid Mother Mobile Number<br>";						
								}
								else if(strlen($data->sheets[$i][cells][$j][14])!=10)
								{
									$html.="Row ".$j."- Please enter a valid Mother Mobile Number<br>";					
								}
								
								if($data->sheets[$i][cells][$j][15] != '')
								{
									if(!is_numeric($data->sheets[$i][cells][$j][15]))
									{
										$html.="Row ".$j."- Please enter a valid Mother Mobile Number2<br>";						
									}
									else if(strlen($data->sheets[$i][cells][$j][15])!=10)
									{
										$html.="Row ".$j."- Please enter a valid Mother Mobile Number2<br>";					
									}						
								}
								
								if(trim($data->sheets[$i][cells][$j][16])!= '')
								{
									if(strlen($data->sheets[$i][cells][$j][16])>50)
									{
										$html.="Row ".$j."- Guardian Name should have minimum 1 and maximum 50 characters<br>";
									}
									else if(!preg_match('/^[a-zA-Z0-9 ]*$/',$data->sheets[$i][cells][$j][16]))
									{
										$html.="Row ".$j."- Please enter a valid Guardian Name<br>";						
									}						
								}
								
								
								if(trim($data->sheets[$i][cells][$j][17]) != '')
								{
									if(strlen($data->sheets[$i][cells][$j][17])>50)
									{
										$html.="Row ".$j."- Guardian Email should have minimum 1 and maximum 50 characters<br>";
									}
									else if(!filter_var($data->sheets[$i][cells][$j][17], FILTER_VALIDATE_EMAIL))
									{
										$html.="Row ".$j."- Please enter a valid Guardian Email Address<br>";						
									}						
								}
										
								if(trim($data->sheets[$i][cells][$j][18])!= '')
								{
									if(!is_numeric($data->sheets[$i][cells][$j][18]))
									{
										$html.="Row ".$j."- Please enter a valid Guardian Mobile Number<br>";						
									}
									else if(strlen($data->sheets[$i][cells][$j][18])!=10)
									{
										$html.="Row ".$j."- Please enter a valid Guardian Mobile Number<br>";					
									}					
								}
								
								
								if(trim($data->sheets[$i][cells][$j][19]) != '')
								{
									if(!is_numeric($data->sheets[$i][cells][$j][19]))
									{
										$html.="Row ".$j."- Please enter a valid Guardian Mobile Number2<br>";						
									}
									else if(strlen($data->sheets[$i][cells][$j][19])!=10)
									{
										$html.="Row ".$j."- Please enter a valid Guardian Mobile Number2<br>";					
									}						
								}		
								
								if($data->sheets[$i][cells][$j][20]=='')
								{
									$html.="Row ".$j."- Please enter Birth Date<br>";
								}
								else if ($data->sheets[$i][cells][$j][20]!='')
								{
									$bd_date1=date("Y-m-d",strtotime($data->sheets[$i][cells][$j][20]));
									$date1=explode('-',$data->sheets[$i][cells][$j][2]);
									
									
									//$bdmonth=$bd_date1[1]-1;
									$dobdate=$bd_date1;
									if(count($date1)<3)
									{
										$html.="Row ".$j."- Please enter a valid Birth Date<br>";
									}
									else if(!preg_match("/^[0-9]{4}-(0[1-9]|1[0-2])-(0[1-9]|[1-2][0-9]|3[0-1])$/",$dobdate))
									{									
										$html.="Row ".$j."- Please enter a valid Birth Date<br>";
									}									
								}
								
								
								
								if(trim($data->sheets[$i][cells][$j][1])!='')
								{
									$checkadmnumber="select student_id from ced_t_students where school_id=".(int)$school_id." and academic_year_id=".(int)$acdid." and student_admission_number='".trim($data->sheets[$i][cells][$j][1])."'";
									$exe=mysql_query($checkadmnumber);
									if(mysql_num_rows($exe))
									$html.="Row ".$j."- Admission Number VALUE already exists<br>";
								}					
								
								if(trim($data->sheets[$i][cells][$j][3])!='')
								{
									$checkrollnumber="select student_id from ced_t_students where school_id=".(int)$school_id." and class_id=".(int)$_POST['class']." and section_id=".(int)$_POST['section']." and student_rollno='".trim($data->sheets[$i][cells][$j][3])."'";
									$exe1=mysql_query($checkrollnumber);
									if(mysql_num_rows($exe1))
									$html.="Row ".$j."- Roll Number VALUE for this Class and Section already exists<br>";
								}				
							}
					}
					
					$dups = array();
					$dups1= array();
					foreach(array_count_values($admis) as $val => $c)
					if($c > 1) $dups[] = $val;
					
					foreach(array_count_values($roll) as $val => $c)
					if($c > 1) $dups1[] = $val;
					
					if(count($dups)>0)
					$html.='Admission Number(s) have been found repeated, Please correct and upload again<br>';
					
					if(count($dups1)>0)
					$html.='Roll Number(s) have been found repeated, Please correct and upload again<br>';
					
					if($j==3)
					$html="errfound";
				}
			//}
		/*	INSERTING DATA INTO STUDENTS TABLE IF ALL THE FIELD ARE CORRECT AND SATISFIED ALL CONDITIONS	*/
			if($html=='')
			{
				//for($i=0;$i<count($data->sheets);$i++) // Loop to get all sheets in a file.
				//{ 
					$i=1;
					if(count($data->sheets[$i][cells])>0) // checking sheet not empty
					{					
						for($j=1;$j<=count($data->sheets[$i][cells]);$j++) // loop used to get each row of the sheet
						{ 
								if($j<3)
								continue;
								else
								{
									$admdate=date("Y-m-d",strtotime($data->sheets[$i][cells][$j][2]));
									/*$birthdate=explode('/',$data->sheets[$i][cells][$j][20]);
									$dtfm=$admdate[2].'-'.$admdate[1].'-'.$admdate[0];
									$birth_month=$birthdate[1]-1;*/			
									$bdate=date("Y-m-d",strtotime($data->sheets[$i][cells][$j][20]));	
											
									$sql="INSERT INTO `ced_t_students`(`student_rollno`, `class_id`, `section_id`, `student_firstname`, `student_lastname`, `student_admission_number`, `student_admission_date`, `student_address`, `student_blood_group`, `student_father_name`, `student_father_email`, `student_father_mobile`, `student_father_mobile2`, `student_mother_name`, `student_mother_email`, `student_mother_mobile`, `student_mother_mobile2`, `student_guardian_name`, `student_guardian_email`, `student_guardian_mobile`, `student_guardian_mobile2`, `academic_year_id`, `school_id`, `status`, `student_email_send_status`, `created_on`, `created_by`,`date_of_birth`)
									 VALUES ('".trim($data->sheets[$i][cells][$j][3])."','".$_POST['class']."','".$_POST['section']."','".trim($data->sheets[$i][cells][$j][4])."','".trim($data->sheets[$i][cells][$j][5])."','".trim($data->sheets[$i][cells][$j][1])."','".$dtfm."','".trim($data->sheets[$i][cells][$j][6])."','".trim($data->sheets[$i][cells][$j][7])."','".trim($data->sheets[$i][cells][$j][8])."','".trim($data->sheets[$i][cells][$j][9])."','".trim($data->sheets[$i][cells][$j][10])."','".trim($data->sheets[$i][cells][$j][11])."','".trim($data->sheets[$i][cells][$j][12])."','".trim($data->sheets[$i][cells][$j][13])."','".trim($data->sheets[$i][cells][$j][14])."','".trim($data->sheets[$i][cells][$j][15])."','".$data->sheets[$i][cells][$j][16]."','".$data->sheets[$i][cells][$j][17]."','".$data->sheets[$i][cells][$j][18]."','".$data->sheets[$i][cells][$j][19]."','".$acdid."','".$school_id."',1,0,'".date('Y-m-d')."','".$me."','".$bdate."')";
									mysql_query($sql);
								}
						}
					}
				//}
			}
			
			unlink($target_file);
			if($html=='')
			$errors="Data uploaded successfully";
			else
			{
				if($html=='errfound')
				$errors="Please enter data for atleast one Student";
				else
				$errors="Following errors have been found in the excel sheet. Please correct these errors and upload again:<br>".$html;
			} 
		}
	}
}
}
?>

<div class="row" style="margin-top: 30px;margin-left:5px;">
			<div class="col-md-9  text-left" style="font:Arial, Helvetica, sans-serif;font-style:normal;font-size:24px; ">
				<i class="glyphicon glyphicon-cloud-upload"   title ="Upload Student Data"   style="font-size: 20px;margin: 0px 0px 0px -15px;"></i>&nbsp;Upload Student Data
			</div>
		</div>			
		
		
		<form class="form-horizontal" method="post" enctype="multipart/form-data">
		<div class="box-body" style="background-color:#fff; padding: 10px; font-family:Arial, Helvetica, sans-serif; font-size:13px; ">
			<div class="row">
				<div class="form-group col-md-6" style="margin-bottom:1.0%;">
					<div  class="col-md-4 col-md-offset-1 text-right">
						<b style="color:#666666; ">Class</b><span style="color:#FF0000; margin:2px;">*</span>
					</div>
					<div class="col-md-7">  
						<select class="form-control" id="classes" name="class" style="height:29px; font-size:12px;width:300px!important;" tabindex="1" onchange="classchange()">
							<?=$clsoptions;?>
						</select>
						
						<div class="col-md-10" id="err_classes" style="color:#990000;margin-left:-15px;"></div>
												
					</div>
				</div>
			</div>
					
					
			<div class="row">
				<div class="form-group col-md-6" style="margin-bottom:1.0%;">
					<div  class="col-md-4 col-md-offset-1 text-right">
						<b style="color:#666666; ">Section</b><span style="color:#FF0000; margin:2px;">*</span>
					</div>
					<div class="col-md-7">  
						<select class="form-control" id="sections" name="section" style="height:29px; font-size:12px;width:300px!important;" tabindex="2" >
							<?=$scnoptions;?>							
						</select>
						
						<div class="col-md-10" id="err_sections" style="color:#990000;margin-left:-15px;"></div>
												
					</div>
				</div>
			</div>		
			
			
				<div class="row">
				<div class="form-group col-md-6" style="margin-bottom:1.0%;">
					<div  class="col-md-4 col-md-offset-1 text-right">
						<b style="color:#666666; ">Choose File</b><span style="color:#FF0000; margin:2px;">*</span>
					</div>
					<div class="col-md-7">  
						<input type="file" id="brw" name="exl" class="form-control" style="height:40px; font-size:12px;width:300px!important;" tabindex="3">
							
							<div class="col-md-10" id="err_brw" style="color:#990000;margin-left:-15px;"></div>
							
					</div>
				</div>
			</div>		
		
</form>

<div class="form-group" style="margin-left:-50px;">
				<div class="col-md-8" align="center">
					<button class="btn btn-default buttons btnclr" type="submit" name="submit" tabindex="4"  value="Upload" onclick="return validations();">Upload</button>
					<button  style="margin-left:2%;" type="button" onClick="location.href='student_management.php'" class="btn btn-default buttons btnclr" tabindex="5" >Cancel</button>
				</div>
			</div>

</div>
<script>
function validations()
{
	var cls=document.getElementById("classes").value;
	var sec=document.getElementById("sections").value;
	var fil=document.getElementById('brw').value;
	$("#err_classes").text("");
	$("#err_sections").text("");
	$("#err_brw").text("");
	if(cls=='0')
	{
		$("#err_classes").text("Please select Class");
		$("#err_classes").show();
		return false;
	}
	else if(sec=='0')
	{
		$("#err_sections").text("Please select Section");
		$("#err_sections").show();
		return false;
	}
	else if(fil=='')
	{
		$("#err_brw").text("Please select the excel file to upload");
		$("#err_brw").show();
		return false;
	}
	
	
}