<?php
	require "../commonincludefiles.php";
	error_reporting(1);
 	$arr = array();
 	$total = 0;
	$str_msg = "";
	
	$branch_id 			= Escape($_REQUEST['branch_id']);
	$year_id 			= Escape($_REQUEST['year_id']);
	$branchyear_id 			= Escape($_REQUEST['branchyear_id']);
	$semester_id 		  = Escape($_REQUEST['semester_id']);
	$student_gturegno 	 = Escape($_REQUEST['student_gturegno']);
	$message_to 		   = Escape($_REQUEST['message_to']);
	$greaterfees 		  = Escape($_REQUEST['greaterfees']);
	$head_id	 		  = Escape($_REQUEST['head_id']);
	$present_day 		  = Escape($_REQUEST['present_day']);
	$present_month 		= Escape($_REQUEST['present_month']);
	$msgprefix 			= Escape($_REQUEST['msgprefix']);
	$msgsuffix 			= Escape($_REQUEST['msgsuffix']);
	$exam_id 			  = Escape($_REQUEST['exam']);
	$allsub 			   = Escape($_REQUEST['allsub']);
	$div_id 			   = Escape($_REQUEST['div_id']);
	$bday 				= date("Y-m-d",strtotime(Escape($_REQUEST['bday'])));
	
	//$curmonth 	  = date("m");
	//$curdate		  = date("d");
	$curmonth 		 = date("m",strtotime($bday));
	$curdate		  = date("d",strtotime($bday));
		
	if($message_to != ""){
		$SQL_MESSAGE = "SELECT message_content FROM cms_message WHERE message_title = '$message_to' ";	
		$RES_MESSAGE = dbQuery($SQL_MESSAGE);
		$REC_MESSAGE = dbArray($RES_MESSAGE);
		$str_msg = $REC_MESSAGE['message_content'];
		
		
	}
	if($_REQUEST['msgtext'] != "")
		{
			$str_msg=$_REQUEST['msgtext'];
		}
	/*$SQL_STUDENT = "
		SELECT
			sm.student_id,
			sm.student_admby,
			sd.batch_id,
			sm.student_gender,
			CONCAT(sm.student_surname, ' ', sm.student_firstname, ' ', sm.student_lastname) AS student_name,student_gturegno,
			student_mobilestudents,
			student_mobileparents,
			tfw_scheme,
			(SELECT branch_name 
				FROM cms_branchmaster sec 
				WHERE sec.college_id   = '" . $_SESSION["VedantnrCollegeId"] . "' and sec.branch_id=sd.cur_branchid) AS branch_name,
	  		  	(SELECT year_name 
				FROM cms_yearmaster ym 
				WHERE ym.year_id=sd.year_id) AS year_name,
	  		  	(SELECT branchyear_name 
				FROM cms_branchyear bry 
				WHERE bry.college_id   = '" . $_SESSION["VedantnrCollegeId"] . "' and bry.branchyear_id=sd.cur_branchyearid) AS branchyear_name,
	  		  	(SELECT semester_name
			   	FROM cms_semestermaster sem 
				WHERE  sem.college_id   = '" . $_SESSION["VedantnrCollegeId"] . "' AND sem.semester_id = sd.cur_semesterid) AS semester_name	  		 	
	
		FROM
			cms_studentmaster sm,
			cms_studentdetail sd
			
			
		WHERE
			sm.student_id = sd.student_id AND
			sm.student_active = 1 ";
				//echo $SQL_STUDENT;
	$RESULT_STUDENT = dbQuery($SQL_STUDENT);*/
			
	$condition = "";
		
	if($branch_id != "")
	{
		$SQL_CONDITION .= " AND sm.branch_id = ".$branch_id;	
	}
	if($year_id != "")
	{
		$condition .= " AND sm.year_id = ".$year_id;	
	}
	if($branchyear_id != "")
	{
		$condition .= " AND sm.branchyear_id = ".$branchyear_id;	
	}
	if($semester_id != "")
	{
		$condition .= " AND sd.semester_id = ".$semester_id;	
	}

	if($student_gturegno != "")
	{
		$condition .= " AND sm.student_gturegno = ".$student_gturegno;	
	}
	if($message_to == "BirthDay")
	{
		//$SQL_CONDITION .= " AND sm.student_birthdate = '".$bday."' ";	
		$condition .= " AND month( student_birthdate ) = $curmonth AND DAYOFMONTH( student_birthdate ) = $curdate";	
	}
	$condition .= " ORDER BY sm.student_id ASC ";
//	$SQL_STUDENT .= $SQL_CONDITION;
	


$objStudent =new StudentMgmt();
$RESULT_STUDENT=$objStudent->getAllStudentRecords($limit = 0, $start = 0, $condition , $order_field = "", $order_type = "");

	$total = dbRows($RESULT_STUDENT);
	$StudentData = array("count" => $total, "data" => array());	
	$i=0;
	if($total > 0)
	{
		while($RECORD_STUDENT = dbArray($RESULT_STUDENT)){
			
			$student_id = $RECORD_STUDENT["student_id"];
			$student_name = $RECORD_STUDENT["student_name"];
			$branch_id = $RECORD_STUDENT["cur_branchid"];
			$year_id = $RECORD_STUDENT["year_id"];
			$branchyear_id = $RECORD_STUDENT["cur_branchyearid"];
			$semester_id = $RECORD_STUDENT["cur_semesterid"];
			$student_admby = $RECORD_STUDENT["student_admby"];
			$student_gender = $RECORD_STUDENT["student_gender"];
			$tfw_scheme = $RECORD_STUDENT["tfw_scheme"];
			$batch_id=$RECORD_STUDENT["batch_id"];
		
			
			$str_temp = str_replace("<StudentName>",$student_name,$str_msg);
			
			if($message_to == "fees"){
				$str_temp = getFeesData($str_temp,$branch_id,$semester_id,$student_id,$student_admby,$tfw_scheme,$greaterfees,$head_id);
			} 
			else if($message_to == "exam"){
				$str_temp = getExamData($str_temp,$branch_id,$year_id,$branchyear_id,$semester_id,$student_id,$exam_id,$allsub);
			}
			else if($message_to == "atkt"){
				$str_temp = getAtktData($str_temp,$branch_id,$year_id = "",$branchyear_id = "",$semester_id,$student_id);
			} 
			else if($message_to == "present_day" || $message_to == "present_month"){
				
				$month = array();
				$month[1] = "January";
				$month[2] = "February";
				$month[3] = "March";
				$month[4] = "April";
				$month[5] = "May";
				$month[6] = "June";
				$month[7] = "July";
				$month[8] = "August";
				$month[9] = "September";
				$month[10] = "October";
				$month[11] = "November";
				$month[12] = "December";
				
				$str_temp = str_replace("<MonthName>",$month[$present_month],$str_temp);
				$str_temp = getPresentData($str_temp,$branch_id,$year_id,$branchyear_id,$semester_id,$student_id,$present_day,$present_month,$batch_id);
			}
			
			if($str_temp != "skip student"){
				$temparray["student_id"]  = $student_id;
				$temparray["branch_name"]  = $RECORD_STUDENT["branch_name"].'-'.$year_id;
				$temparray["student_name"]  = $RECORD_STUDENT["student_name"];
				$temparray["message"] = $str_temp;
				$temparray["student_gturegno"]  = $RECORD_STUDENT["student_gturegno"];//substr($RECORD_STUDENT["student_gturegno"],strlen($RECORD_STUDENT["student_gturegno"])-3);
				$temparray["student_mobilestudents"]  = $RECORD_STUDENT["student_mobilestudents"];
				$temparray["student_mobileparents"]  = $RECORD_STUDENT["student_mobileparents"];
				$StudentData["data"][$i] = $temparray;   
				$i++;
			}
		}
	}
	echo json_encode($StudentData);	
	
	function getFeesData($str_temp = "",$branch_id = "",$semester_id = "",$student_id = "",$student_admby = "",$tfw_scheme = "",$greaterfees = 0,$head_id = "")
	{
		if($student_admby != "ACPDC"){
			$sql_setupamt = "
				SELECT 
					SUM(fees_amount) AS fees_amount 
				FROM 
					cms_feessetup 
				WHERE 
					head_id != 1 ";
					
					if($semester_id != "")
					{
						$sql_setupamt .= " AND sql_setupamt = ".$sql_setupamt;	
					}	
			if($head_id != "" && stristr($head_id,"All")==""){
				$sql_setupamt .= " AND head_id IN (".$head_id.") ";
			}		
					
			$res_setupamt = dbQuery($sql_setupamt);
			$rec_setupamt = dbArray($res_setupamt);
			$total_amount = $rec_setupamt['fees_amount'];
		}
		else{
			$flag_amt = 0;
			
			if($tfw_scheme == 0){
				$sql_setupamt = "
					SELECT 
						head_name,
						fees_amount 
					FROM 
						cms_feessetup,
						cms_feeshead 
					WHERE 
						cms_feessetup.head_id = cms_feeshead.head_id AND 
						semester_id = $semester_id ";
			}
			else{
				$sql_setupamt = "
					SELECT 
						head_name,
						fees_amount 
					FROM 
						cms_feessetup,
						cms_feeshead 
					WHERE 
						cms_feessetup.head_id = cms_feeshead.head_id AND 
						cms_feeshead.head_id != 2 AND
						semester_id = $semester_id ";
			}
			if($head_id != "" && stristr($head_id,"All")==""){
				if($head_id == 2)
					$sql_setupamt .= " AND cms_feeshead.head_id IN (".$head_id.",1) ";
				else
					$sql_setupamt .= " AND cms_feeshead.head_id IN (".$head_id.") ";
			}
			$res_setupamt = dbQuery($sql_setupamt);
			while($rec_setupamt = dbArray($res_setupamt)){	
				if($rec_setupamt['head_name'] == "ACPDC Adm Fee"){
					$flag_amt = $rec_setupamt["fees_amount"];
				} else if($rec_setupamt['head_name'] == "Tution Fee"){	
					$total_amount = $total_amount + ($rec_setupamt["fees_amount"] - $flag_amt);
				} else{
					$total_amount = $total_amount + $rec_setupamt["fees_amount"];
				}
			}
		}
		
		$sql_receivedamt = "
			SELECT
					sum(case when discount = '0' then receive_amt else total_receive_amt  end) AS receive_amt,allsem,total_receive_amt
			FROM
				cms_feesreceivemaster frm,
				cms_feesreceivedetail frd
			WHERE
				frd.feesreceive_id = frm.feesreceive_id AND
				frm.student_id = $student_id AND
				frm.semester_id = $semester_id AND
				frm.branch_id = $branch_id ";
		
		
		
		if($head_id != "" && stristr($head_id,"All")==""){
			$sql_receivedamt .= " AND frd.head_id IN (".$head_id.") ";
		}		
		$res_receivedamt = dbQuery($sql_receivedamt);
		$rec_receivedamt = dbArray($res_receivedamt);
	
	
		
			$received_amount = $rec_receivedamt['receive_amt'];
			$debit_amount = $total_amount-$received_amount;
		
	
		
		if($debit_amount >= $greaterfees && $debit_amount != 0){
			$str_temp = str_replace("<FeesAmount>",$debit_amount,$str_temp);
			return $str_temp;
		}
		else{
			return "skip student";	
		}
	}	
	
	function getPresentData($str_temp = "",$branch_id = "",$year_id = "",$branchyear_id = "",$semester_id = "",$student_id = "",$present_day = "",$present_month = "",$batch_id="")
	{
		$SQL_PRESENT = "
			SELECT 
				day_present,
				day_absent 
			FROM 
				cms_presentmaster 
			WHERE
				branch_id = ".$branch_id." AND
				day_working = 1 AND
				batch_id IN ('".$batch_id."',0)";
			

			if($year_id!="")
			{	
				$SQL_PRESENT.= " AND year_id = ".$year_id;
			}
				

			if($branchyear_id!="")
			{	
				$SQL_PRESENT.= " AND branchyear_id = ".$branchyear_id;
			}
				
			
			if($semester_id!="")
			{	
				$SQL_PRESENT.= " AND semester_id = ".$semester_id;
			}
				
					
					
		if($present_day != ""){
			 $SQL_CONDITION = " AND day_date = '".date("Y-m-d",strtotime($present_day))."' ";
			 $SQL_PRESENT.=$SQL_CONDITION;
			$RES_PRESENT = dbQuery($SQL_PRESENT);
			
			$cnt_lecture = 0;
			$cnt_absent = 0;
			$cnt_present = 0;
			
			while($REC_PRESENT = dbArray($RES_PRESENT)){
				$cnt_lecture++;
				$PRESENT= explode(",", $REC_PRESENT['day_present']);
				/*$ABSENT = explode(",", $REC_PRESENT['day_absent']);
				if(in_array($student_id,$ABSENT) == true){
					$cnt_absent++;
				}*/
				if(in_array($student_id,$PRESENT) == true){
					$cnt_present++;
				}
			}	
			
			if($cnt_lecture > 0){
				$str_temp = str_replace("<CurrentDate>",date('d-M-Y'),$str_temp);
				$str_temp = str_replace("<PresentLecture>",$cnt_present,$str_temp);
				$str_temp = str_replace("<TotalLecture>",$cnt_lecture,$str_temp);
				return $str_temp;		
			}
			else{
				return "skip student";	
			}
			
			/*
			
			if($cnt_absent > 0){
				$str_temp = str_replace("<CurrentDate>",date('d-M-Y'),$str_temp);
				$str_temp = str_replace("<AbsentLecture>",$cnt_absent,$str_temp);
				$str_temp = str_replace("<TotalLecture>",$cnt_lecture,$str_temp);
				return $str_temp;		
			}
			else{
				return "skip student";	
			}*/
		}
		else if($present_month != "")
		{
			/* START MONTH TOTAL */
			$SQL_CONDITION = " AND EXTRACT(MONTH FROM day_date) = '".$present_month."' ";
			
			$RES_PRESENT = dbQuery($SQL_PRESENT.$SQL_CONDITION);
			$monthtotal_count = 0;
			$monthpresent_count = 0;
			while($REC_PRESENT = dbArray($RES_PRESENT)){
				$PRESENT = explode(",", $REC_PRESENT['day_present']);
				$monthtotal_count++;		
				if(in_array($student_id,$PRESENT) == true){
					$monthpresent_count++;		
				}
			}
			/* END MONTH TOTAL */
			
			/* START YEAR TOTAL */
			$SQL_CONDITION = " AND EXTRACT(MONTH FROM day_date) <= '".$present_month."' ";
			$RES_PRESENT = dbQuery($SQL_PRESENT.$SQL_CONDITION);
			$semtotal_count = 0;
			$sempresent_count = 0;
			while($REC_PRESENT = dbArray($RES_PRESENT)){
				$PRESENT = explode(",", $REC_PRESENT['day_present']);
				$semtotal_count++;		
				if(in_array($student_id,$PRESENT) == true){
					$sempresent_count++;		
				}
			}
			/* END YEAR TOTAL */
			
			$str_temp = str_replace("<CurrentMonthPresent>",$monthpresent_count,$str_temp);
			$str_temp = str_replace("<CurrentMonthDays>",$monthtotal_count,$str_temp);
			$str_temp = str_replace("<SemesterMonthPresent>",$sempresent_count,$str_temp);
			$str_temp = str_replace("<SemesterMonthDays>",$semtotal_count,$str_temp);
			
			if($semtotal_count > 0){
				return $str_temp;		
			}
			else{
				return "skip student";	
			}			
		}
	}
	
	function getExamData($str_temp = "",$branch_id = "",$year_id = "",$branchyear_id = "",$semester_id = "",$student_id = "",$exam_id = "",$allsub = "")
	{
		$SQL_EXAM = "
			SELECT
				rm.result,
				rm.obtain_mark,
				rm.present,
				sm.subject_name,
				es.total_marks,
				em.exam_name
			FROM
				cms_resultmaster rm,
				cms_subjectmaster sm,
				cms_examsetup es,
				cms_exammaster em
			WHERE
				rm.subject_id = sm.subject_id AND
				rm.subject_id = es.subject_id AND
				rm.branch_id = ".$branch_id." AND
				rm.year_id = ".$year_id."  AND
				rm.exam_id = ".$exam_id." AND
				em.exam_id = ".$exam_id." AND
				rm.student_id = ".$student_id."  AND
				es.branch_id = ".$branch_id." AND
				es.year_id = ".$year_id." AND
				es.exam_id = ".$exam_id."";
		if($branchyear_id !=""){
			$SQL_EXAM = "AND rm.branchyear_id = ".$branchyear_id." AND 
			es.branchyear_id = ".$branchyear_id." ";
		}
		if($semester_id !=""){
			$SQL_EXAM = " AND rm.semester_id = ".$semester_id." AND
				es.semester_id = ".$semester_id."";
		}
		$RES_EXAM = dbQuery($SQL_EXAM);
		$result = "Pass";
		$total_total = 0;
		$total_obtain = 0;
		$sublist = "";
		$exam_name = "";
		
		if(dbRows($RES_EXAM) > 0){
			while($REC_EXAM = dbArray($RES_EXAM)){
				if($result == "Pass"){
					$result = $REC_EXAM['result'];
				}
				$total_total += $REC_EXAM['total_marks'];
				$total_obtain += $REC_EXAM['obtain_mark'];
				if($allsub == "true"){
					$sublist .= $REC_EXAM['subject_name']."-".$REC_EXAM['obtain_mark'].",";
				}	
				$exam_name = $REC_EXAM['exam_name'];
			}
			$sublist = substr($sublist,0,strlen($sublist)-1);
			$str_temp = str_replace("<ExamName>",$exam_name,$str_temp);
			$str_temp = str_replace("<Result>",$result,$str_temp);
			$str_temp = str_replace("<TotalMarks>",$total_total,$str_temp);
			$str_temp = str_replace("<ObtainMark>",$total_obtain,$str_temp);
			$str_temp = str_replace("<SubjectsMark>",$sublist,$str_temp);
			
			return $str_temp;
		}
		else{
			return "skip student";	
		}
		
		
	}
	
	function getAtktData($str_temp = "",$branch_id = "",$year_id = "",$branchyear_id = "",$semester_id = "",$student_id = "")
	{
		$SQL_ATKT = "
			SELECT
				GROUP_CONCAT(sm.subject_name) AS subject_name
			FROM
				cms_atktmaster am,
				cms_subjectmaster sm
			WHERE
				am.branch_id = ".$branch_id." AND
				am.year_id = ".$year_id."   AND
				am.student_id = ".$student_id." AND
				am.subject_id = sm.subject_id AND
				am.status = 1";
		if($branchyear_id !=""){
			$SQL_ATKT = "AND
				am.branchyear_id = ".$branchyear_id."";
		}
		if($semester_id !=""){
			$SQL_ATKT = "AND
				am.semester_id = ".$semester_id."";
		}
		$RES_ATKT = dbQuery($SQL_ATKT);
		if(dbRows($RES_ATKT) > 0){
			$REC_ATKT = dbArray($RES_ATKT);				
			$subject_name = $REC_ATKT['subject_name'];
			
			$count = count(explode(",",$subject_name));
			
			if($subject_name != ""){
				$str_temp = str_replace("<NoofSubject>",$count,$str_temp);
				$str_temp = str_replace("<SubjectName>",$subject_name,$str_temp);
				return $str_temp;
			}
			return "skip student";
		}
		else{
			return "skip student";	
		}
		
		
	}
?>