<?php

error_reporting(E_ERROR | E_PARSE);

session_start(); 



if(!isset($_SESSION['tulsilogin']))

{

	header('Location: index.php');

	return;

}



	






$_SESSION['_token']=bin2hex(openssl_random_pseudo_bytes(16));	

	

	include("connect.php");
	include("mylibrary.php");



	require('importfromexcel/php-excel-reader/excel_reader2.php');



    require('importfromexcel/SpreadsheetReader.php');

	

	

	

	$allowed =  array('xlsx');

	$filename = $_FILES['header']['name'];

	 $ext = pathinfo($filename, PATHINFO_EXTENSION);

	if(!in_array($ext,$allowed) ) 

	{

		$_SESSION['msg']="Unsupport File.Select Excel File only";

		header('location: college.php');

		return;

	}

	else

	{

	

	

	

	date_default_timezone_set("Asia/Kolkata");

	$filenameheader = date("YmdHis").$_FILES['header']['name'];

	$path = "./temp/";

	move_uploaded_file( $_FILES['header']['tmp_name'], $path ."/". $filenameheader );

	



    $Reader = new SpreadsheetReader('temp/'.$filenameheader);

	

	$i=0;
	$j=0;
	$k=0;

    foreach ($Reader as $Col)

    {

       		
$j++;
		$collegetypecode=checkvalidstring ($Col[0]);

		
		
		 $evels = "select collegetypemid from res_collegetype_master where collegetypecode='$collegetypecode'";

		$rels = mysqli_query($conn, $evels);

		if(mysqli_num_rows($rels) > 0)

		{

			/* Check The Level Of Study is added */

			while($rtls = mysqli_fetch_assoc($rels))

			{

				 $collegetypemid=$rtls['collegetypemid'];

			}

		}	

		else

		{
			if($i==0)

		{

			$i++;

			

		}
		continue;
		}

		

		$capplyvia=checkvalidstring ($Col[1]);

		

		
if($capplyvia == 'Indirect')
{
		$indirecttieupcmpcode=checkvalidstring ($Col[2]);

		
		
		 $evels = "select indirecttieupcmpmid from res_indirecttieupcmp_master where indirecttieupcmpcode='$indirecttieupcmpcode'";

		$rels = mysqli_query($conn, $evels);

		if(mysqli_num_rows($rels) > 0)

		{

			/* Check The Level Of Study is added */

			while($rtls = mysqli_fetch_assoc($rels))

			{

				$indirecttieupcmpmid=$rtls['indirecttieupcmpmid'];

			}

		}	

		else

		{
			$indirecttieupcmpmid=0;
		}
}
		

		$collegecode=checkvalidstring ($Col[3]);

		if($collegecode == '')
		{
continue;
		}

		

		$cmname= (checkvalidstring($Col[4]));

		if($cmname == '')
		{
			continue;
		}
		
		

		$ccurrency= (checkvalidstring($Col[5]));

		

		

		$cyearoffoundation= (checkvalidstring($Col[6]));

		

		

		$cdlinumber= (checkvalidstring($Col[7]));

		

		

		$cwebsite= (checkvalidstring($Col[8]));

		

		

		$capplicationurl= (checkvalidstring($Col[9]));


		$cvideoprofile=checkvalidstring ($Col[10]);

		

		

		$cranking= (checkvalidstring($Col[11]));

		

		

		$csectiondesc= (checkvalidstring($Col[12]));

		

		$ctotalcampus=checkvalidstring ($Col[13]);

		

		$ctotalprograms=checkvalidstring ($Col[14]);

		

		$ctotalstudents=checkvalidstring ($Col[15]);

		

		$ctotaldomesticstudents=checkvalidstring ($Col[16]);

		

		$ctotaldomesticstudents=checkvalidstring ($Col[17]);

		

		$contactname=checkvalidstring ($Col[18]);

		

		$cmiddlename=checkvalidstring ($Col[19]);

		

		$csurname= (checkvalidstring($Col[20]));

		

		$contactnumber= (checkvalidstring($Col[21]));

		

		$contactemail= (checkvalidstring($Col[22]));

		

		$caddress1= (checkvalidstring($Col[23]));

		

		
		
		
		$caddress2= (checkvalidstring($Col[24]));

		

		
		$countrysortname=checkvalidstring ($Col[25]);

		
		 $evels = "select countrymid from res_country_master where countrysortname='$countrysortname'";

		$rels = mysqli_query($conn, $evels);

		if(mysqli_num_rows($rels) > 0)

		{

			/* Check The Level Of Study is added */

			while($rtls = mysqli_fetch_assoc($rels))

			{

				$ccountry=$rtls['countrymid'];

			}

		}	

		else

		{
			if($i==0)

		{

			$i++;

			

		}
		continue;
		}
		
		$statecode=checkvalidstring ($Col[26]);

		
		 $evels = "select statemid from res_state_master where statecode='$statecode'";

		$rels = mysqli_query($conn, $evels);

		if(mysqli_num_rows($rels) > 0)

		{

			/* Check The Level Of Study is added */

			while($rtls = mysqli_fetch_assoc($rels))

			{

				$statemid=$rtls['statemid'];

			}

		}	

		else

		{
			if($i==0)

		{

			$i++;

			

		}
		continue;
		}
		

		$citycode=checkvalidstring ($Col[27]);

	
		 $evels = "select citymid from res_city_master where citycode='$citycode'";

		$rels = mysqli_query($conn, $evels);

		if(mysqli_num_rows($rels) > 0)

		{

			/* Check The Level Of Study is added */

			while($rtls = mysqli_fetch_assoc($rels))

			{

				$citymid=$rtls['citymid'];

			}

		}	

		else

		{
			if($i==0)

		{

			$i++;

			

		}
		continue;
		}
		

		$cpostalcode=checkvalidstring ($Col[28]);

		

		

		$cmsgappname=checkvalidstring ($Col[29]);

		
		
		
		
		$cmsgappid=checkvalidstring ($Col[30]);

		
		
		
		$cvideoappname=checkvalidstring ($Col[31]);

	
		
		$cvideoappid=checkvalidstring ($Col[32]);

		

		
		$cbankname=checkvalidstring ($Col[33]);

		

		
		$cbankbranch=checkvalidstring ($Col[34]);

		
		
		$caccountname=checkvalidstring ($Col[35]);

		

		
		$caccountnumber=checkvalidstring ($Col[36]);

		

		
		$cifsccode=checkvalidstring ($Col[37]);

		

		
		$cmicrcode=checkvalidstring ($Col[38]);

		

		$cbankdetails=checkvalidstring ($Col[39]);

	

		
		$contactdepartmnet='Head';
	
	$cposition='Head';
		

				

		if($i==0)

		{

			$i++;

			continue;

		}

		
		

		

		 $eve = "select collegecode from res_college_master where collegecode='$collegecode'";
	$re = mysqli_query($conn, $eve);
	if(mysqli_num_rows($re) > 0)
	{
		continue;
	}
	else
	{	
	
		  $eve2 = "INSERT INTO res_college_master (cmname,collegecode,ctotalcampus,collegetypemid,ctotalprograms,ctotalinternationstudent,csectiondesc,cbankname,cbankbranch,caccountnumber,caccountname,cifsccode,cmicrcode,cbankdetails,ctotalstudents,countrymid,ctotaldomesticstudents,ccurrency,cdlinumber,cwebsite,capplicationurl,cvideoprofile,cranking,capplyvia,indirecttieupcmpmid,cyearoffoundation) VALUES('$cmname','$collegecode','$ctotalcampus','$collegetypemid','$ctotalprograms','$ctotalinternationstudent','$csectiondesc','$cbankname','$cbankbranch','$caccountnumber','$caccountname','$cifsccode','$cmicrcode','$cbankdetails','$ctotalstudents','$ccountry','$ctotaldomesticstudents','$ccurrency','$cdlinumber','$cwebsite','$capplicationurl','$cvideoprofile','$cranking','$capplyvia','$indirecttieupcmpmid','$cyearoffoundation')";
		
		if ($conn->query($eve2) === TRUE)

	{	
	$k++;
	$last_id = $conn->insert_id;
		 $eve1 = "INSERT INTO res_contact_master (contactname, contactemail,contactnumber,contactdepartmnet,cposition,cremarks,caltenateemail,caddress1,caddress2,ccountry,cstate,ccity,cpostalcode,cmiddlename,csurname,cmsgappname,cmsgappid,cvideoappname,cvideoappid,ccompanyname,csubcategory,clinktype,clinkid) VALUES('$contactname','$contactemail','$contactnumber','$contactdepartmnet','$cposition','$cremarks','$caltenateemail','$caddress1','$caddress2','$ccountry','$statemid','$citymid','$cpostalcode','$cmiddlename','$csurname','$cmsgappname','$cmsgappid','$cvideoappname','$cvideoappid','$cmname','$csubcategory','College','$last_id')";
		
		$re1 = mysqli_query($conn, $eve1);
			}

			

	$_SESSION['msg'] = "College Data Imported";

	

	}
	}
	}
	if($k == 0)
	{
		$_SESSION['msg1'] = "Institute Data Imported Failed";
	}
	else if(($j-1) == $k)
	{
	$_SESSION['msg'] = "Institute Data Imported";
	}else{
		$_SESSION['msg'] = "Institute Data Partialy Imported";
	}
	header('Location: college.php');

?>