JezK

Edit File: sa_operations.php

<?php
include("error_codes.php");
include("class.phpmailer.php");
define('LOGO_PATH', 'https://bu1is.krify.com/skoolonline_new/school/uploads/logoinner.png');
use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;
use PHPMailer\PHPMailer\OAuth;
use League\OAuth2\Client\Provider\Google;
require '../composer_test/vendor/autoload.php';
class sa_operations
{
	private $conn;
 
 function __construct() {
 require_once dirname(__FILE__) . '/connect.php';
 // opening db connection
 $db = new dbConnect();
 $this->conn = $db->connect();
 }
	/*********************To Escape string quotes*********************/
	function getpreventInjections($string)
	{
		$safe = mysqli_real_escape_string($this->conn,$string);
		return $safe;
	}
	
	/*********************ADMIN LOGIN AUTHENTICATION*******************/	
	function admin_login($user,$password)
	{
		$user=$this->getpreventInjections($user);
		$password=$this->encrypt($password);
 
		$sql="select * from ced_t_authentication_details where email='".$user."' and password='".$password."'";
		$res=mysqli_query($this->conn,$sql);
		if(mysqli_num_rows($res)>0)
		{
			$rec=mysqli_fetch_array($res);
			$response[0]=md5(uniqid(rand(), true));
			$response[1]=$rec['authenticate_id'];
		}
		else
		{ 
			$response=ERR202;
		}
		return $response;		
	}
	
	/**************************GET ALL SCHOOL FOR LISTING*************************/	
	function get_Allschoolformanage()
	{
		$qry="select school_id,school_name,school_email,school_phone,school_status,school_license from ced_t_school order by school_id desc";
		$exe=mysqli_query($this->conn,$qry);
		if(mysqli_num_rows($exe))
		{
			while($res=mysqli_fetch_assoc($exe))
			{
				$row[]=$res;
			}
			//~ print_r($row);
			return $row;
		}
		else{
		return '0';
	}
	}	
	/*********************ADD NEW SCHOOL*********************/
	function createNewSchool($whodid,$schname,$sch_email,$sch_address,$sch_address1,$phno,$city,$district,$state,$pincode,$website,$contperson,$mobile,$nooflic,$duration,$modules)
	{		
		$send=$this->SendLicenceKeytoSchool(1);
		$schname=$this->getpreventInjections($schname);
		$sch_email=$this->getpreventInjections($sch_email);
			//$exist_school=$this->check_email_existance($sch_email);
		$exist_phone=$this->check_phone_exist($phno);
		$sch_address=$this->getpreventInjections($sch_address);
		$sch_address1=$this->getpreventInjections($sch_address1);
		$city=$this->getpreventInjections($city);
		$district=$this->getpreventInjections($district);
		$state=$this->getpreventInjections($state);
		$pincode=$this->getpreventInjections($pincode);
		$phno=$this->getpreventInjections($phno);
		$website=$this->getpreventInjections($website);
		$contperson=$this->getpreventInjections($contperson);
		$mobile=$this->getpreventInjections($mobile);
		$nooflic=$this->getpreventInjections($nooflic);
		$duration=$this->getpreventInjections($duration);
		$sch_lic_name=$schname.'_'.date('Y').date('m').date('d').'_'.$nooflic;	
		$emailenc=$schname.''.date('Y-m-d H:i:s');
		$savemod=implode(',',$modules);
		$savemod=rtrim($savemod,',');
		if(!$exist_phone)
		{
		$shuffler=date('Ymd')."0123456789abcdefghijklm";
		$digit16=substr(str_shuffle($shuffler),0,16);
		//$digit16=md5(uniqid($emailenc, true));			
		//school table insertion
		$qry="insert into ced_t_school set school_name='".$schname."',school_license='".$digit16."',school_email='".$sch_email."',school_address_line_1='".$sch_address."',school_address_line_2='".$sch_address1."',school_phone='".$phno."',school_site_url='".$website."',school_city='".$city."',school_district='".$district."',school_state='".$state."',school_pincode='".$pincode."',school_status=1,created_on='".date('Y-m-d')."',created_by='$whodid'";
		// echo $qry;exit;
		$exe=mysqli_query($this->conn,$qry);
		$sch_id=mysqli_insert_id($this->conn);
			if($sch_id)
			{				//school license insertion				
				$effectiveDate = date('Y-m-d');			
				$effectiveDate2 = date('Y-m-d', strtotime("+".$duration." months", strtotime($effectiveDate)));
								// activating the school licence
				$insert_lic="insert into ced_t_school_licenses set school_id='".$sch_id."',license_name='".$sch_lic_name."',license_key='".$digit16."',license_count='".$nooflic."',license_valid_months='".$duration."',license_valid_from='".$effectiveDate."',license_valid_to='".$effectiveDate2."',license_status=1,modules_included='".$savemod."',created_by='$whodid'";
				mysqli_query($this->conn,$insert_lic);
				$schlic_id=mysqli_insert_id($this->conn);
							// school contacts insertion
				$insert_contact="insert into ced_t_school_contacts set school_id='".$sch_id."',contact_name='".$contperson."',contact_number='".$mobile."',created_on='".date('Y-m-d')."',created_by='$whodid'";
				mysqli_query($this->conn,$insert_contact);
							// purchased modules insertions
				foreach($modules as $val)
				{
					$purchase_prevs="insert into ced_t_school_purchased_modules set module_id='".$val."',school_id='".$sch_id."',purchase_date='".date('Y-m-d')."',created_by='$whodid',created_on='".date('Y-m-d')."'";
					mysqli_query($this->conn,$purchase_prevs);
				}
				$send=$this->SendLicenceKeytoSchool($sch_id);
				$response=ERR203;
				return $response;			
			}			
			else
			{
				$response=ERR204;
			}
		}
		else if($exist_phone)
		{
			return 'phone_exist';
		}
	}
	
	/*********************DISPLAY SCHOOL COMPLETE DETAILS*********************/
	function viewSchoolDetails($schoolid)
	{
		$qry="select s.school_name,s.school_email,s.school_address_line_1,s.school_address_line_2,s.school_phone,s.school_site_url,s.school_city,s.school_district,s.school_state,s.school_pincode,c.contact_name,c.contact_number from ced_t_school s inner join ced_t_school_contacts c on s.school_id=c.school_id where s.school_id=".(int)$schoolid;
		$exe=mysqli_query($this->conn,$qry);
		$row=mysqli_fetch_array($exe);
		$record['school_name']=$row['school_name'];
		$record['school_email']=$row['school_email'];
		$record['school_address']=$row['school_address_line_1'];
		$record['school_address1']=$row['school_address_line_2'];
		$record['school_contact']=$row['school_phone'];
		$record['school_city']=$row['school_city'];
		$record['school_district']=$row['school_district'];
		$record['school_state']=$row['school_state'];
		$record['school_pincode']=$row['school_pincode'];
		$record['school_site_url']=$row['school_site_url'];
		$record['contact_name']=$row['contact_name'];
		$record['contact_number']=$row['contact_number'];
		
		$mpurchase="select module_id from ced_t_school_purchased_modules where school_id='$schoolid' order by module_id asc";
		$mexe=mysqli_query($this->conn,$mpurchase);
		while($res=mysqli_fetch_array($mexe))
		{
			$rec[]=$res['module_id'];
		}
		$record['purchase_modules']=$rec;
		
		$licinfo="select license_count,license_valid_from,license_valid_to,license_key from ced_t_school_licenses where school_id='$schoolid' order by school_license_id desc";
		$lexe=mysqli_query($this->conn,$licinfo);
		$lrec=mysqli_fetch_array($lexe);
		$record['license_count']=$lrec['license_count'];
		$record['license_key']=$lrec['license_key'];
		$d1=str_replace('-','/',$lrec['license_valid_from']);
		$d2=str_replace('-','/',$lrec['license_valid_to']);
		$record['validity']=date('d/m/Y',strtotime($d1)).' to '.date('d/m/Y',strtotime($d2));
		return $record;
	}
	
	/*********************UPDATE SCHOOL INFO*********************/
	function update_school_details($me,$schid,$schname,$sch_email,$sch_address,$sch_address1,$phno,$city,$district,$state,$pincode,$website,$contperson,$mobile,$nooflic,$duration,$modules,$prev_mods)
	{
		$sch_email=$this->getpreventInjections($sch_email);
		//$exist_school=$this->check_email_existance_check($sch_email,$schid);
		$exist_phone=$this->check_phone_exist_edit($phno,$schid);
		$schname=$this->getpreventInjections($schname);
		$sch_address=$this->getpreventInjections($sch_address);
		$sch_address1=$this->getpreventInjections($sch_address1);
		$city=$this->getpreventInjections($city);
		$district=$this->getpreventInjections($district);
		$state=$this->getpreventInjections($state);
		$pincode=$this->getpreventInjections($pincode);
		$phno=$this->getpreventInjections($phno);
		$website=$this->getpreventInjections($website);
		$contperson=$this->getpreventInjections($contperson);
		$mobile=$this->getpreventInjections($mobile);
		$savemod=implode(',',$modules);
		$savemod=rtrim($savemod,',');
		
		//return $prev_modules;
		if(!$exist_phone)
		{	
			
			$sql = "UPDATE `ced_t_school` SET `school_name` = '".$schname."',`school_email` = '".$sch_email."',`school_address_line_1` = '".$sch_address."',
			`school_address_line_2` = '".$sch_address1."',`school_phone` = '".$phno."',school_city='".$city."',school_district='".$district."',school_state='".$state."',school_pincode='".$pincode."',
			`school_site_url` = '".$website."',updated_by='$me',updated_on='".date('Y-m-d')."'WHERE `school_id`=".(int)$schid;
			$res = mysqli_query($this->conn,$sql);
			
			$authen="update ced_t_authentication_details set email='".$phno."',updated_by='$me' where school_id='$schid' and school_su=1";
			mysqli_query($this->conn,$authen);
					
			$contupd="update ced_t_school_contacts set contact_name='".$contperson."',contact_number='".$mobile."',updated_by='$me' where school_id=".(int)$schid;
			mysqli_query($this->conn,$contupd);	
				$prev_mods=explode(',',rtrim($prev_mods,','));
				for($i=1;$i<=6;$i++)
				{
					if(in_array($i,$prev_mods))
					{
						if(in_array($i,$modules))
						{
							
						}
						else
						{
						$sql_remove = 'DELETE FROM `ced_t_school_purchased_modules` WHERE `school_id` = "'.$schid.'" and `module_id` = "'.$i.'"';
						$res_remove = mysqli_query($this->conn,$sql_remove); 
						}
					}
					else
					{
						if(in_array($i,$modules))
						{
						$insert_prev="insert into ced_t_school_purchased_modules set module_id='".$i."',school_id='".$schid."',purchase_date='".date('Y-m-d')."',created_by='$me',created_on='".date('Y-m-d')."'";
						mysqli_query($this->conn,$insert_prev);
						}
					}
				}
				$imprev=trim(implode(',',$modules),',');;
				$latestprevs="update ced_t_school_licenses set modules_included='".$imprev."' where school_id=".(int)$schid;
				mysqli_query($this->conn,$latestprevs);
			return 'ok';
			}		
		else if($exist_phone)
		return 'exist_phone';
		else					
		return ERR204;	
	}
	
	/*********************GET SCHOOL DETAILS FOR UPDATE*********************/
	function getSchoolDetailsforUpdate($id)
	{
		$editschool=array();
		$sql="select * from ced_t_school where school_id=".(int)$id;
		$exe=mysqli_query($this->conn,$sql);
		if(mysqli_num_rows($exe))
		{
			$res=mysqli_fetch_array($exe);
			$record['school_name']=$res['school_name'];
			$record['school_email']=$res['school_email'];
			$record['school_address_line_1']=$res['school_address_line_1'];
			$record['school_address_line_2']=$res['school_address_line_2'];
			$record['school_phone']=$res['school_phone'];
			$record['school_site_url']=$res['school_site_url'];
			$record['school_city']=$res['school_city'];
			$record['school_district']=$res['school_district'];
			$record['school_state']=$res['school_state'];
			$record['school_pincode']=$res['school_pincode'];
			$contqry="select * from ced_t_school_contacts where school_id='".$id."'";
			$licexe=mysqli_query($this->conn,$contqry);
			if(mysqli_num_rows($licexe))
			{
				$licres=mysqli_fetch_array($licexe);
				$record['contact_name']=$licres['contact_name'];
				$record['contact_number']=$licres['contact_number'];
			}
			
			$prevqry="select license_count,license_valid_months from ced_t_school_licenses where school_id='".$id."' order by school_license_id desc";
			$prevexe=mysqli_query($this->conn,$prevqry);
			if(mysqli_num_rows($prevexe))
			{
				$prevres=mysqli_fetch_array($prevexe);
				{
				$record['license_count']=$prevres['license_count'];
				$record['valid_months']=$prevres['license_valid_months'];
				}
			}
			
			return $record;
		}
		else
		{
			return ERR205;
		}
	}
	
	/*********************DELETE SCHOOL IF NO RECORDS BOUND WITH IT********************/
	function deletetheSchool($school_id)
	{
		$sql1="select school_id from ced_t_academicyear where school_id=".(int)$school_id;
		echo $sql1;
		$exe1=mysqli_query($this->conn,$sql1);
		
		$sql2="select school_id from ced_t_class where school_id=".(int)$school_id;
		echo $sql2;
		$exe2=mysqli_query($this->conn,$sql2);
		
		$sql3="select school_id from ced_t_subjects where school_id=".(int)$school_id;
		$exe3=mysqli_query($this->conn,$sql3);
		
		if(mysqli_num_rows($exe1) || mysqli_num_rows($exe2) || mysqli_num_rows($exe3))
		{
			return 'AEBT';
		}
		else
		{			
			$qry="delete from ced_t_school where school_id=".(int)$school_id;
			mysqli_query($this->conn,$qry);
			
			$qry1="delete from ced_t_school_licenses where school_id=".(int)$school_id;
			mysqli_query($this->conn,$qry1);
			
			$qry2="delete from ced_t_school_contacts where school_id=".(int)$school_id;
			mysqli_query($this->conn,$qry2);
			
			$qry3="delete from ced_t_school_purchased_modules where school_id=".(int)$school_id;
			mysqli_query($this->conn,$qry3);
			
			$qry4="delete from ced_t_authentication_details where school_id=".(int)$school_id;
			mysqli_query($this->conn,$qry4);
			return 'ok';			
		}
	}
	
	/**************************ACTIVATE OR DEACTIVATE SCHOOL*************************/
	function activateORdeactivateschool($id,$whodid,$status)
	{
		// echo $status;exit;
		$show=0;
		$qry="update ced_t_school set school_status='$status',school_status_updated_by='$whodid',updated_by='$whodid' where school_id=".(int)$id;
		$exe=mysqli_query($this->conn,$qry);
		if(mysqli_affected_rows($this->conn))
		{
			if($status==1)
			{
				$lic_info=$this->get_All_licensesOfschool($id);
				if($lic_info!=0)
				foreach($lic_info as $lic)
				{
					if($lic['license_valid_to'] > date('Y-m-d'))
					{
						$lic_active="update ced_t_school_licenses set license_status=1 where school_license_id=".(int)$lic['school_license_id'];
						mysqli_query($this->conn,$lic_active);
					}
				}
				//////////////////////SEND ACTIVATION MAIL
				$sql="SELECT s.school_name,s.school_email,c.contact_name FROM ced_t_school s inner join ced_t_school_contacts c on s.school_id=c.school_id where s.school_id=".(int)$id;
				$exe=mysqli_query($this->conn,$sql);
				$res=mysqli_fetch_array($exe);
				$email=$res['school_email'];
				$avail_license="SELECT sum(license_count) as available_lic FROM `ced_t_school_licenses` WHERE `license_valid_to` > CURRENT_DATE and license_status=1 and school_id=".(int)$id;
				$lcount=mysqli_query($this->conn,$avail_license);
				if(mysqli_num_rows($lcount))
				{
					$show=mysqli_fetch_array($lcount);
				}
				$subject="Reactivation of Skoolonline for ".$res['school_name'];
				$message="
				<html>
				<head>
					<title>Skoolonline</title>
				</head>
				<body>
					Dear ".$res['contact_name']."(".$res['school_name']."),
					This is to kindly inform you that your school has been reactivated. You have a total of ".$show['available_lic']." active licenses for Skoolonline Parent App.
					If you have any questions related to your account, you can email us at help@skoolonline.in.

Thanking you once again for selecting our services

<address>Warm Regards, 
				Skoolonline Team</address>
				</body>
				</html>";
				$headers = "MIME-Version: 1.0" . "\r\n";
				$headers .= "Content-type:text/html;charset=UTF-8" . "\r\n";
				// More headers
				$headers .= 'From: Skoolonline <dontreply@skoolonline.in>';
				$headers .= 'Cc: dontreply@skoolonline.in';
				$sent=$this->mail_send($email,$subject,$message);
				
			}
			return '100';
		}
		else
		{
			return '101';	
		}	
	}
		
	/*********************REGENERATE SCHOOL LICENCE KEY******************/
	function getReGenerateLicenceKey($school_id,$whodid)
	{
		$sql="select school_id,school_email from ced_t_school where school_id='".$school_id."'";
		$exe=mysqli_query($this->conn,$sql);
		
		$res=mysqli_fetch_array($exe);
		$emailenc=$res['school_email'];
		
		$shuffler=date('Ymd')."0123456789abcdefghijklm";
		$digit16=substr(str_shuffle($shuffler),0,16);
		//$digit16=md5(uniqid($emailenc, true));
		
		$qry="update ced_t_school set school_license='".$digit16."',school_status_updated_by='$whodid',updated_by='$whodid' where school_id='".$school_id."'";
		$upd=mysqli_query($this->conn,$qry);	
			
		$qry1="update ced_t_school_licenses set license_key='".$digit16."',created_by='$whodid' where school_id=".(int)$school_id;
		mysqli_query($this->conn,$qry1);
		
		$sent=$this->SendLicenceKeytoSchool($school_id);
			
		if($sent)
		return '1';
		else
		return '0';
			
	}
	
	/*********************DISPLAY SCHOOL LICENSE INFO ******************/
		function get_All_licensesOfschool($schid)
		{
			$sql="select * from ced_t_school_licenses where school_id=".(int)$schid;
			$exe=mysqli_query($this->conn,$sql);
			if(mysqli_num_rows($exe))
			{
				while($res=mysqli_fetch_assoc($exe))
				{
					$row[]=$res;
				}
				return $row;
			}
			else
			return '0';
		}
		
	/*********************ADD NEW LICENSE TO SCHOOL ********************/	
	function add_licensetoSchool($sch_id,$schname,$license_count,$duration)
	{
		$mods=array();		
		$prevmod="select module_id from ced_t_school_purchased_modules where school_id=".(int)$sch_id;
		$exemod=mysqli_query($this->conn,$prevmod);
		while($resmod=mysqli_fetch_array($exemod))
		{
			$mods[]=$resmod['module_id'];
		}
		/*foreach($modules as $mod)
		{
			if(in_array($mod,$mods))
			{
			
			}
			else
			{
				$qry="insert into ced_t_school_purchased_modules set module_id='".$mod."',purchase_date='".date('Y-m-d')."',school_id='".$sch_id."',created_by='1',created_on='".date('Y-m-d')."'";
				mysqli_query($qry);
			}
		}*/
		$modules_include=implode(',',$mods);
		$modules_include=rtrim($modules_include,',');
		$schname=$schname.'_'.date('Ymd').'_'.$license_count;	
		$check="select school_license from ced_t_school where school_id=".(int)$sch_id;
		$exe=mysqli_query($this->conn,$check);
		$res=mysqli_fetch_array($exe);
		$license_key=$res['school_license'];
		$effectiveDate1=date('Y-m-d');
		$effectiveDate2= date('Y-m-d', strtotime("+".$duration." months", strtotime($effectiveDate1)));
		$sql="insert into ced_t_school_licenses set school_id='".$sch_id."',license_name='".$schname."',license_key='".$license_key."',license_count='".$license_count."',license_valid_from='".$effectiveDate1."',license_valid_to='".$effectiveDate2."',license_valid_months='".$duration."',license_status='1',modules_included='".$modules_include."',created_by=1";
		mysqli_query($this->conn,$sql);
		
		$sql="SELECT s.school_name,s.school_email,c.contact_name FROM ced_t_school s inner join ced_t_school_contacts c on s.school_id=c.school_id where s.school_id=".(int)$sch_id;
		$exe=mysqli_query($this->conn,$sql);
		$res=mysqli_fetch_array($exe);
		$email=$res['school_email'];
		
		$subject="Purchase of Skoolonline Licenses";
		$message="
			<html>
			<head>
				<title>Skoolonline</title>
			</head>
			<body>
				Dear ".$res['contact_name']."(".$res['school_name']."), 		
				Thank you for purchasing ".$license_count." number of Skoolonline Parent App licenses. The validity of these 
				licenses is ".$duration." Month(s) and the expiry date is ".date('d/m/Y',strtotime($effectiveDate2)).". Please ensure to renew these licenses before the expiry date. 
				If you have any questions related to your account, you can email us at help@skoolonline.in. 
				Thanking you once again for selecting our services 
		<address>Warm Regards, 
		Skoolonline Team</address>
		</body>
		</html>";
		$headers = "MIME-Version: 1.0" . "\r\n";
		$headers .= "Content-type:text/html;charset=UTF-8" . "\r\n";
		// More headers
		$headers .= 'From: Skoolonline <dontreply@skoolonline.in>';
		$headers .= 'Cc: dontreply@skoolonline.in';
		$sent=$this->mail_send($email,$subject,$message);
		
	}
	
	/*********************GET SCHOOL NAME BY SCHOOL ID********************/	
	function getschool_nameById($schid)
	{
		$sql="select school_name from ced_t_school where school_id=".(int)$schid;
		$exe=mysqli_query($this->conn,$sql);
		$res=mysqli_fetch_array($exe);
		return $res['school_name'];
	}
	
	/*********************SEND LICENSE KEY TO SCHOOL********************/
	function SendLicenceKeytoSchool($schoolid)
	{
		$sql1="select license_count,license_valid_months,license_valid_to from ced_t_school_licenses where school_id=".(int)$schoolid." order by school_license_id asc limit 1";
		$lc=mysqli_query($this->conn,$sql1);
		$lres=mysqli_fetch_array($lc);
		
		$sql="SELECT s.school_name,s.school_email,s.school_phone,s.school_license,c.contact_name FROM ced_t_school s inner join ced_t_school_contacts c on s.school_id=c.school_id where s.school_id=".(int)$schoolid;
		$exe=mysqli_query($this->conn,$sql);
		$res=mysqli_fetch_array($exe);
		$email=$res['school_email'];
		$crpt=$res['school_license'];
		$crpt=urlencode($crpt);
		$qry="select m.module_name from ced_t_school_modules m inner join ced_t_school_purchased_modules s on m.module_id=s.module_id where school_id=".(int)$schoolid." order by s.module_id";
		$row=mysqli_query($this->conn,$qry);
 $mods="";
		while($result=mysqli_fetch_assoc($row))
		{
			$mods.='<li>'.$result['module_name'].'</li>';
		}
		$subject = "Welcome to Skoolonline";
		$licenseVerifyLink = ADMIN_PATH.'registration.php?authid='.$crpt.'&regon='.$res['school_phone'].'';
		$message = "
		<html>
		<head>
		<title>Skoolonline</title>
		</head>
		<body>
		Dear ".$res['contact_name']."(".$res['school_name']."),
		Thank you for registering with Skoolonline. You have selected the following products / services offered by us:
		<ul>$mods</ul>
		To complete the registration process and access all the features linked to your account<a href=".$licenseVerifyLink."> click here</a> and enter the following License Key:
		".$res['school_license']."
		You have purchased ".$lres['license_count']." number of Skoolonline Parent App licenses. The validity of these licenses is ".$lres['license_valid_months']." Month(s) and the expiry date is ".date('d-m-Y',strtotime($lres['license_valid_to'])).". Please ensure to renew these licenses before the expiry date.
		Once registered, you would be able to login into Skoolonline using www.Skoolonline.com. You would also be provided access to the following mobile apps:
		<ul>
		<li>Parent App - Available on Google Play Store</li>
		<li>Teacher App - Will be separately provided</li>
		<li>Driver App - Will be separately provided</li></ul>
		If you have any questions related to your account, you can email us at help@skoolonline.in
		Thanking you once again for selecting our services
		<address>Warm Regards, 
		Skoolonline Team</address>
		</body>
		</html>";
		// echo $message;exit;
		$sent=$this->mail_send($email,$subject,$message);
		if($sent)
		return '1';
		else
		return '0';			
	}
	
	function check_phone_exist($phone)
	{
		$qry="select school_id from ced_t_school where school_phone='".$phone."'";
		$res=mysqli_query($this->conn,$qry);
		$cnt1=mysqli_num_rows($res);
		
		$qry1="select staff_id from ced_t_school_staff where staff_mobile='".$phone."'";
		$res1=mysqli_query($this->conn,$qry1);
		$cnt2=mysqli_num_rows($res1);
		
		if($cnt1==0 && $cnt2==0)		
		return 0; 
		else 
		return 1;
		
	}
	
	function check_phone_exist_edit($phone,$schid)
	{
		$qry="select school_id from ced_t_school where school_phone='".$phone."' and school_id<>".(int)$schid;
		$res=mysqli_query($this->conn,$qry);
		$cnt1=mysqli_num_rows($res);
		
		$qry1="select staff_id from ced_t_school_staff where staff_mobile='".$phone."'";
		$res1=mysqli_query($this->conn,$qry1);
		$cnt2=mysqli_num_rows($res1);
		
		if($cnt1==0 && $cnt2==0)		
		return 0; 
		else 
		return 1;
		
	}
	
	/*********************CHECK EMAIL IS EXIST IN THE SYSTEM OR NOT********************/
	function check_email_existance($email)
	{
		//$sql = 'SELECT `email` FROM `ced_t_authentication_details` WHERE `email` = "'.$email.'" and parent_app_access<>1';
		$sql="select staff_id from ced_t_school_staff where staff_email='".$email."'";
		$res = mysqli_query($this->conn,$sql);
		$cnt1 = mysqli_num_rows($res);
		
		$sql1="select school_email from ced_t_school where school_email='".$email."'";
		$res1 = mysqli_query($this->conn,$sql1);
		$cnt2 = mysqli_num_rows($res1);
		
		if($cnt1==0 && $cnt2==0)		
		return 0; 
		else 
		return 1;
	}
	
	/*********************CHECK EMAIL IS EXIST IN THE SYSTEM OR NOT FOR EDIT********************/
	function check_email_existance_check($email,$schid)
	{
		$sql1="select school_email from ced_t_school where school_email='".$email."' and school_id<>".(int)$schid;
		$res1 = mysqli_query($this->conn,$sql1);
		$cnt1 = mysqli_num_rows($res1);
		
		//$sql2 = 'SELECT `email` FROM `ced_t_authentication_details` WHERE `email` = "'.$email.'" and school_id='.(int)$schid.' and school_su<>1 and parent_app_access<>1';
		$sql2="select staff_email from ced_t_school_staff where staff_email='".$email."' and school_id<>".(int)$schid;
		$res2 = mysqli_query($this->conn,$sql2);
		$cnt2 = mysqli_num_rows($res2);
		
		if($cnt1==0 && $cnt2==0)		
		return 0; 
		else 
		return 1;
	}
	
	/*********************GET SCHOOL LICENCE ID*********************/	
	function get_lic_id_school($sch_id)
	{
		$sql = 'SELECT `schlic_id` FROM `ced_t_school_licenses` WHERE `school_id` = "'.$sch_id.'"';
		$res = mysqli_query($this->conn,$sql);
		$row = mysqli_fetch_array($res);
		return $row['schlic_id']; 
	}
	
	/*********************GET SCHOOL PREVILAGES*********************/
	function get_school_privileges($sch_id)
	{
		$sql = 'SELECT purchased_module_id,module_id FROM ced_t_school_purchased_modules WHERE `school_id` = "'.$sch_id.'"';
		$res = mysqli_query($this->conn,$sql);
		if(mysqli_num_rows($res) > 0)
		{
			$status['res'] = '1';
			while($row = mysqli_fetch_array($res))	
			{
				$data[] = $row['module_id'];
			}
			$status['result'] = $data;
		}
		else
		{
			$status['res'] = '0';
		}
		return $status;
	}
	
	
	
		/******************************SUPER ADMIN CHANGE PASSWORD**************/
		function superadmin_settigs_change_password($email,$current_password,$new_password)
		{				
			$current_password=$this->encrypt($current_password);
			$new_password=$this->encrypt($new_password);
			$sql="SELECT `authenticate_id` FROM `ced_t_authentication_details` WHERE `email`='".$email."' and `password`='".$current_password."'";
			$result=mysqli_query($this->conn,$sql);
			$row=mysqli_fetch_array($result);
			if(mysqli_num_rows($result)>0)
			{
				$sql2="UPDATE `ced_t_authentication_details` SET `password`='".$new_password."' where `email`='".$email."' and `authenticate_id`='".$row['authenticate_id']."' ";
				$result2=mysqli_query($this->conn,$sql2);
				if($result2)
				{
					$error=1;
				}
				else
				{
					$error=2;
				}
			}
			else
			{
				$error=3;
			}
			return $error;
		}
		
		/***********GET SUPER ADMIN EMAIL***********/
		function get_authentication_email($authid)
		{
			$sql="SELECT `email` FROM `ced_t_authentication_details` WHERE `authenticate_id`='".$authid."'";
			$result=mysqli_query($this->conn,$sql);
			$row['email']=mysqli_fetch_array($result);
			return $row['email'];
		}

/*********************MAKE DEACTIVE SCHOOL(DEPRICIATED)*********************/
	function getActive_DeActiveSchool($sch_id)
	{
		$sql="update ced_t_school set school_status='0' where school_id='".$sch_id."'";
		$exe=mysqli_query($this->conn,$sql);
		if(mysqli_affected_rows($this->conn))
		{
			return ERR207;
		}
	}
	
	/********************* EMAIL THE PASSWORD *********************/	
	function forgotpwd_sendpasswordbyemail($email)
	{
		$qexist="SELECT `password` FROM `ced_t_authentication_details` WHERE `email` ='".$email."' and `school_id`='0' and `status`='0' ";
		// echo $qexist;exit;
		$exe=mysqli_query($this->conn,$qexist);
		if(mysqli_num_rows($exe)>0)
		{
			$password=mysqli_fetch_array($exe);	
			$pwd=$this->decrypt($password['password']);	
			$to = $email;
			$subject = "Forgot Password: Skoolonline";
			$message = '
						<table border="0" cellpadding="0" cellspacing="0" width="100%" style="font-family:verdana,arial,san-sarif;">
							<tr><td align="left" valign="top">
								<table border="0" cellpadding="0" cellspacing="0" width="100%">
								<tr><td valign="top" align="center">
									<table border="0" cellpadding="0" cellspacing="0" width="100%" align="center">
										<tr>
										 <td colspan="2" style="padding:0px 10px" align="center" height="75" valign="middle" width="150">
											<img src="'.LOGO_PATH.'" alt="Skoolonline" align="center" >
										</td>
										</tr>
									</table>
								</td></tr>
							 <tr><td align="left" valign="top">&nbsp;</td></tr>
							 <tr>
								 <td style="" align="left" valign="top">
										
										You have requested for forgot password. Your Password is '.$pwd.'. You may Login now 
								</td>
							 </tr>
							 <tr><td> 	
									Thanks and Regards, 	
									Skoolonline Team 	
								</td></tr>
								</table>

</td></tr>
						</table>';

// Always set content-type when sending HTML email
								$headers = "MIME-Version: 1.0" . "\r\n";
								$headers .= "Content-type:text/html;charset=UTF-8";
								$headers .= "From: Skoolonline <dontreply@skoolonline.in>";
								$sent=$this->mail_send($to,$subject,$message);
								//$mailer=$this->sendingemail($to,$subject,$message);
								//return 1;
								if($sent)
								return 1;
								else
								return 2;
					}
					else
					{
						return 3;
					}
		}
		/*************************ENCRYTPTION FUNCTION FOR PRIVATE KEYS*************************/
		function encrypt($string)
		{
			//~ echo 2432;
				$key='skoolonl!ne2o2!';
				$result = '';
				for($i=0; $i<strlen($string); $i++)
				{
					$char = substr($string, $i, 1);
					$keychar = substr($key, ($i % strlen($key))-1, 1);
					$char = chr(ord($char)+ord($keychar));
					$result.=$char;
				}
				 return base64_encode($result);
		}
 
 function decrypt($string)
 {
		//~ echo 234;
 $key='skoolonl!ne2o2!';
 $result='';
 $string = base64_decode($string);
 for($i=0; $i<strlen($string); $i++)
 {
 $char = substr($string, $i, 1);
 $keychar = substr($key, ($i % strlen($key))-1, 1);
 $char = chr(ord($char)-ord($keychar));
 $result.=$char;
 }
 return $result;
 }
		
		/*************************GET LICENSE DETAILS BY LICENSE ID*************************/
		function get_licenseSetInfo($lid)
		{
			$sql="select s.school_name,s.school_id,sl.* from ced_t_school_licenses sl inner join ced_t_school s on s.school_id=sl.school_id where school_license_id=".(int)$lid;
			$exe=mysqli_query($this->conn,$sql);
			if(mysqli_num_rows($exe))
			{
				$row=mysqli_fetch_array($exe);
				$data['school_name']=$row['school_name'];
				$data['school_id']=$row['school_id'];
				$data['license_count']=$row['license_count'];
				$data['license_valid_from']=date('Y-m-d',strtotime($row['license_valid_from']));
				$data['license_valid_to']=date('Y-m-d',strtotime($row['license_valid_to']));
				$data['modules_included']=$row['modules_included'];
				return $data;
			}
		}
		
		function check_password($pass,$uid)
		{
			$sql="SELECT `password` FROM `ced_t_authentication_details` WHERE `authenticate_id` = '".$uid."' ";
			//~ echo $sql;
			//~ exit;
			$result=mysqli_query($this->conn,$sql);
			$row=mysqli_fetch_array($result);
			$pwd=$this->decrypt($row['password']);
			return $pwd;
		}
		
		
		/*************************RENEWAL LICENSE BY LICENSE ID*************************/
		function renewal_this_license($lcid,$months,$start,$end,$schid,$count)
		{		
			$qry="update ced_t_school_licenses set license_valid_from='".$start."',license_valid_to='".$end."',license_valid_months='".$months."',license_status=1 where school_license_id=".(int)$lcid;
			$q=mysqli_query($this->conn,$qry);
			
			$sql="SELECT s.school_name,s.school_email,c.contact_name FROM ced_t_school s inner join ced_t_school_contacts c on s.school_id=c.school_id where s.school_id=".(int)$schid;
			$exe=mysqli_query($this->conn,$sql);
			$res=mysqli_fetch_array($exe);
			$email=$res['school_email'];
			$subject="Renewal of Skoolonline Licenses";
			$message="
			<html>
			<head>
				<title>Skoolonline</title>
			</head>
			<body>
				Dear ".$res['contact_name']."(".$res['school_name']."),
				Thank you for renewing ".$count." number of Skoolonline Parent App licenses. The validity of these licenses is 
					".$months." Month(s) and the expiry date is ".date('d/m/Y',strtotime($end)).". Please ensure to renew these licenses before the expiry date.
				If you have any questions related to your account, you can email us at help@skoolonline.in.

Thank you once again for selecting our services

Thank you for selecting our services

//smtp mail
	function mail_send($to,$sub,$mess)
	{
		$google_email = 'productsbu1@gmail.com';
        $oauth2_clientId = '707771317205-m01emngdqfp8a7s5fn412pdl14d9s2ma.apps.googleusercontent.com';
        $oauth2_clientSecret = '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';
        $oauth2_refreshToken = '1//04mwEsI6-S-8WCgYIARAAGAQSNwF-L9IrMwoiBVMD5u7LcnOoAQgCGSR-FS3kp-kdyDV4g3kTCFUAzWtIIhH1yBZBm_Bl2wa4-ek';

$mail = new PHPMailer(TRUE);

try {
		
			$mail->setFrom($google_email, 'Skoolonline');
			$mail->addAddress($to);

$mail->Subject = $sub;
			$mail->Body = $mess;

$mail->isSMTP();
			$mail->Port = 587;
			$mail->SMTPAuth = TRUE;
			$mail->SMTPSecure = 'tls';
			$mail->IsHTML(true);
			/* Google's SMTP */
			$mail->Host = 'smtp.gmail.com';
			
			/* Set AuthType to XOAUTH2. */
			$mail->AuthType = 'XOAUTH2';
			
			/* Create a new OAuth2 provider instance. */
			$provider = new Google(
				[
					'clientId' => $oauth2_clientId,
					'clientSecret' => $oauth2_clientSecret,
				]
			);
			
			/* Pass the OAuth provider instance to PHPMailer. */
			$mail->setOAuth(
				new OAuth(
					[
						'provider' => $provider,
						'clientId' => $oauth2_clientId,
						'clientSecret' => $oauth2_clientSecret,
						'refreshToken' => $oauth2_refreshToken,
						'userName' => $google_email,
					]
				)
			);
			
			/* Finally send the mail. */
			
			if ($mail->send()) {
				// echo "Mail sent successfully";
				return 1;
			} else {
				echo "Mail not sent";exit;
			}
		}
		catch (Exception $e)
		{
			echo $e->errorMessage();
		}
		catch (\Exception $e)
		{
			echo $e->getMessage();
		}
	}
	
}
?>