<?php
require "commonincludefiles.php";
error_reporting(1);
ini_set('memory_limit', '-1');
//require_once 'excel_reader2.php';
require_once "phpspreadsheet/autoload.php";

$branch_id = $_POST['branch_hidden'];
$year_id = $_POST['year_hidden'];
$branchyear_id = $_POST['branchyear_hidden'];
$semester_id = $_POST['semester_hidden'];
$college_id = $_SESSION['VedantnrCollegeId'];

//$fullpath=$_FILES['enrollment_upload']['tmp_name'];
$createdon = (date('Y-m-d h:i:s'));
$inputFileName = $_FILES['enrollment_upload']['tmp_name'];
$inputFileType = \PhpOffice\PhpSpreadsheet\IOFactory::identify($inputFileName);
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
$spreadsheet = $reader->load($inputFileName);
$worksheet = $spreadsheet->getActiveSheet();
$exceldata = $worksheet->toArray();


$html = "";
$i = 0;
$n = 1;
$r = 1;
$more_record = 0;

$total = count($exceldata);

$objStudent = new StudentMgmt();
$duplicate = "";
for ($j = 1; $j <= count($exceldata); $j++) {
	$adharno = $exceldata[$j][6];
	if ($adharno == "") {
		continue;
	}
	$condition = "";
	$condition .= " AND sm.branch_id=$branch_id   AND sm.student_adharno = $adharno";
	$count = $objStudent->CountAllStudentRecords(0, 0, $condition);
	if ($count > 0) {
		$duplicate .= $adharno . ",";
	}
}
if ($duplicate != "") {

	echo '{"success":"false","msg":"Duplicate Entry Found for AADHAR NO: ' . rtrim($duplicate, ",") . '"}';
	exit;

}
$sql = "INSERT INTO cms_studentmaster (student_active, student_gturegno, 
							student_rollno, student_admdate, 
							adm_conformation, student_admby, 
							student_adharno, student_surname, 
							student_firstname, student_lastname, 
							student_father, student_mother, 
							student_birthdate, student_birthdateplace, 
							student_gender, student_category, 
							student_subcast,student_bloodgroup, 
							student_cast, student_religion, 
							student_fathermobile, student_mobileother, 
							student_emailid, student_photo, 
							student_addressat, student_addresspost, 
							student_pincode, dist_nameeng, 
							taluko_nameeng, state_nameeng, 
							student_nationality,student_age, branch_id, year_id";

if ($branchyear_id != "") {
	$sql .= ",branchyear_id,college_id,createdon ) VALUES ";
} else if ($semester_id != "") {
	$sql .= ",semester_id,college_id,createdon ) VALUES ";
}

for ($j = 1; $j < count($exceldata); $j++) {
	$more_record = 1;

	$student_active = Escape($exceldata[$j][0]);
	$date_birthdate = new DateTime($exceldata[$j][12]);
	$current_date = new DateTime(date("Y-m-d"));
	$diffrence = $date_birthdate->diff($current_date);
	$student_age = $diffrence->format("%y Years, %m Months, %d Days");
	if ($student_active = "Yes") {
		$student_active = 1;
	} else if ($student_active = "No") {
		$student_active = 0;

	}
	$sql .= '(';
	$sql .= "$student_active,";
	if ($branchyear_id != "") {

		for ($i = 1; $i <= 30; $i++) {
			$sql .= "'" . Escape($exceldata[$j][$i]) . "',";
		}
		$sql .= "'$student_age','$branch_id','$year_id','$branchyear_id','$college_id','$createdon'";

	}

	if ($semester_id != "") {

		for ($i = 1; $i <= 30; $i++) {
			$sql .= "'" . Escape($exceldata[$j][$i]) . "',";
		}
		$sql .= "'$student_age','$branch_id','$year_id','$semester_id','$college_id','$createdon'";

	}
	$sql .= '),';
	if ($r == 100) {
		$r = 0;
		$sql = substr($sql, 0, strlen($sql) - 1) . ';';

		dbQuery($sql);
		$more_record = 0;
		$sql = "";
		$sql = "INSERT INTO cms_studentmaster (student_active, student_gturegno, 
							student_rollno, student_admdate, 
							adm_conformation, student_admby,
							student_adharno, student_surname, 
							student_firstname, student_lastname, 
							student_father, student_mother, 
							student_birthdate, student_birthdateplace, 
							student_gender, student_category, 
							student_subcast,student_bloodgroup, 
							student_cast, student_religion, 
							student_fathermobile, student_mobileother, 
							student_emailid, student_photo, 
							student_addressat, student_addresspost, 
							student_pincode, dist_nameeng, 
							taluko_nameeng, state_nameeng, 
							student_nationality, student_age, branch_id, year_id";
		if ($branchyear_id != "") {
			$sql .= ",branchyear_id,college_id,createdon ) VALUES ";
		} else if ($semester_id != "") {
			$sql .= ",semester_id,college_id,createdon ) VALUES ";
		}
	}
	$r++;
}

if ($more_record == 1) {
	$sql = substr($sql, 0, strlen($sql) - 1) . ';';

	//echo $sql;
	//exit;
	dbQuery($sql);
}
$i = 0;
for ($j = 1; $j < count($exceldata); $j++) {
	$student_gturegno = Escape($exceldata[$j][1]);
	$student_active = Escape($exceldata[$j][0]);

	if ($student_active = "Yes") {
		$student_active = 1;
	} else if ($student_active = "No") {
		$student_active = 0;
	}


	$std_details = "select student_id,student_rollno from cms_studentmaster 
			where  
			student_gturegno = $student_gturegno and
				college_id = $college_id and
				branch_id = $branch_id and 
				year_id = $year_id ";
	if ($branchyear_id != "") {
		$std_details .= "
					and 
					branchyear_id = '$branchyear_id'";
	}
	if ($semester_id != "") {
		$std_details .= "
					and semester_id = '$semester_id'";
	}
	$std_details .= "ORDER BY student_id DESC limit 1";
	//echo $std_details;
	$resemp = dbQuery($std_details);
	$rowemp = dbArray($resemp);
	$student_id = $rowemp['student_id'];

	$sql1 = "INSERT INTO cms_studentdetail (createdon, current_active, year_id, college_id, cur_branchid, student_id, student_rollno";
	if ($branchyear_id != "") {
		$sql1 .= ", cur_branchyearid) VALUES ";
	} else if ($semester_id != "") {
		$sql1 .= ", cur_semesterid) VALUES ";
	}

	$sql1 .= "('$createdon', '$student_active', '$year_id', '$college_id', '$branch_id', '$student_id', '$student_rollno'";

	if ($branchyear_id != "") {
		$sql1 .= ", '$branchyear_id'";
	} else if ($semester_id != "") {
		$sql1 .= ", '$semester_id'";
	}

	$sql1 .= ")";

	//echo $sql1;
	dbQuery($sql1);

}

echo '{"success":"true","msg":"Total ' . $total - 1 . ' row imported."}';

exit;
?>