<?php
//error_reporting(0);
require_once(LIB_PATH.DS.'database.php');
class ONLINEPAY extends DatabaseObject{
	protected static $table_name="acceptance_tranzact";
	public $id;
	public $surname;
	public $othernames;
	public $session;
	public $semester;
	public $ses_sem_id;
	public $amount;
	public $matricno;
	public $regDate;
	public $datetime;
	public $courses;
	public $sex;
	public $dob;
	public $lga;
	public $department;
	public $departmentcode;
	public $email;
	public $state;
	public $reg_date;
	public $admitted_date;
	public $faculty;
	public $facultycode;
	public $formno;
	public $level;
	public $levelcode;
	public $overhead;
	public $phone;
	public $programme;
	public $programmecode;
	public $ptype;
	public $responsecode;
	public $status;
	public $txt_fees;
	public $txt_ref;
	public $verified;
	public $verified_date;
	public $verified_by;
	public $deptcode;
	
public static function authenticate($formno="", $email=""){
global $database;
//$formno= $database->escape_value($formno);
//$email = $database->escape_value($email);
$sql = "SELECT * FROM ".self ::$table_name." ";
$sql .= " WHERE formno='{$formno}' ";
$sql .= "AND email='{$email}' LIMIT 1";
//$sql .= "AND responsecode = 'Successful' LIMIT 1" ;	

$result_array = self::find_by_sql($sql);
return !empty($result_array)? array_shift($result_array):
false;
}


public static function showdept(){
global $database;
//$formno= $database->escape_value($formno);
//$email = $database->escape_value($email);
$sql = "SELECT * FROM department_tbl" ;

//$sql .= "AND responsecode = 'Successful' LIMIT 1" ;	

return self::find_by_sql($sql);
//return !empty($result_array)? array_shift($result_array):
//false;
}

public static function showsessiondetails( $table, $dept, $level, $session){
global $database;
$sql = "SELECT  * FROM $table WHERE department= '{$dept}' and level= '{$level}' and session= '{$session}' and responsecode = '00'" ;
return self::find_by_sql($sql);

}
public static function showalltranzact($table, $level="", $session=""){
global $database;
    $sql = "SELECT * FROM $table WHERE level='{$level}' and session = '{$session}' and status = 'Approved Successful'";
    
    return self::find_by_sql($sql);
}

public static function searchbydate($table, $start_date,$end_date){
global $database;
//$start_date = date('yyyy-mm-dd', $start_date);
//$end_date = date('yyyy-mm-dd', $end_date);
$sql = "SELECT * FROM $table WHERE responsecode = '00' AND datetime BETWEEN date('{$start_date}') AND date('{$end_date}') " ;
return self::find_by_sql($sql);
}

public static function searchbyprogramme($tab, $prog, $level){
   global $database; 
    $sql = "SELECT * FROM $tab WHERE formno LIKE '%$prog%' AND level='{$level}' AND responsecode = '00'";
    return self::find_by_sql($sql);
}
public static function searchbydatesessionlevel($table,$level, $session, $start_date,$end_date){
global $database;

$sql = "SELECT * FROM $table WHERE level='{$level}' AND session = '{$session}' AND responsecode = '00' AND datetime BETWEEN date('{$start_date}') AND date('{$end_date}') " ;
return self::find_by_sql($sql);
}

public static function search_txtid($table='', $tranzID=''){
global $database;
    $sql = "SELECT * FROM $table WHERE txt_ref ='{$tranzID}' AND status = 'Approved Successful'";
    $result_array = self::find_by_sql($sql);
return !empty($result_array)? array_shift($result_array):
false;
}

public static function search_schoolfees_txtid($table='', $tranzID=''){
global $database;
    $sql = "SELECT * FROM $table WHERE txt_ref ='{$tranzID}' AND status = 'Approved Successful'";
    $result_array = self::find_by_sql($sql);
return !empty($result_array)? array_shift($result_array):
false;
}


public static function update_txtid($table='', $tranzID='',$id=0){
global $database;
$newdate = date('Y-m-d h:i:s');
    $sql = "UPDATE $table SET verified=1, verified_date='{$newdate}', verified_by={$id} WHERE txt_ref ='{$tranzID}' AND status = 'Approved Successful' ";
    //return self::find_by_sql($sql);
    	$database->query($sql);
return ($database->affected_rows()==1)? true: false;
}

//  Update activities level with Student Matric Number-------------------------


public static function update_matricno($table='', $mat='',$formno='',$email =''){
global $database;
    $sql = "UPDATE $table SET matricno = $mat WHERE formno ='{$formno}' AND email = '{$email}' ";
    //return self::find_by_sql($sql);
    	$database->query($sql);
return ($database->affected_rows()==1)? true: false;
}


// .--------------------------------------------------------------------------

public static function showallbyfac($table, $facul="", $level="", $session=""){
    
    global $database;
    $sql = "SELECT * FROM $table WHERE faculty = '{$facul}'and level='{$level}' AND session = '{$session}' and status = 'Approved Successful'";
    
    return self::find_by_sql($sql);
}

// Display all Course Registered Students  search Class
public static function reg_students($table, $dept = "", $ses_sem_id){
    global $database;
    $sql = "SELECT * FROM $table WHERE ses_sem_id = '{$ses_sem_id}' AND department = '{$dept}'";
    return self::find_by_sql($sql);
}

public static function get_ses_sem_id($session, $semester){
    global $database;
    $sql = "SELECT ses_sem_id FROM session_semester WHERE session = '$session' AND semester ='$semester'";
    return self::find_by_sql($sql);
}


// ------------------ Get details by formno -----------------------------

public static function getdetails($formno = ""){
    global $database;
    $sql = "SELECT * FROM admitted_student_details WHERE `formno` = '$formno'";
    return self::find_by_sql($sql);
}

public static function state_details($formno = "", $state=""){
    global $database;
    $sql = "SELECT * FROM admitted_student_details WHERE `formno` = '$formno' AND 'state' = '$state'";
    return self::find_by_sql($sql);
}

// ---------------- Get States of Students-----------------------------------

public static function get_state(){
    global $database;
    $sql = "SELECT state FROM admitted_student_details GROUP BY state ORDER BY state ASC";
    return self::find_by_sql($sql);
}
// ------------------Search Per Programme---------------------------------

public static function searchbyprog($level = "", $prog = ""){
    global $database;
    $sql = "SELECT formno,surname,othernames,courses,programme,session,reg_date FROM admitted_student_details
    WHERE `formno` LIKE '%$prog%' AND programme LIKE '%$level%'";
    return self::find_by_sql($sql);
}
//--------------------END-------------------------------------------------
public static function getdeptcode($dept = ""){
    global $database;
    $sql = "SELECT * FROM department_tbl WHERE department='$dept'";
    return self::find_by_sql($sql);
}

//--------------------Restrict already assigned mat no---------------------
public static function restrictformno($dept = "", $level = "", $prog = ""){
    global $database;
    $sql = "SELECT distinct(schoolfees_tranzact.formno),schoolfees_tranzact.surname,schoolfees_tranzact.othernames,schoolfees_tranzact.department,schoolfees_tranzact.email,
    schoolfees_tranzact.level from schoolfees_tranzact JOIN student_activity_level ON student_activity_level.formno = schoolfees_tranzact.formno
    WHERE schoolfees_tranzact.department = '$dept' AND schoolfees_tranzact.formno LIKE '%$prog%' AND schoolfees_tranzact.level = '$level' 
    AND responsecode = '00' AND student_activity_level.matricno=0 ORDER BY surname ASC";
    // where student_activity_level.formno = schoolfees_tranzact.formno AND schoolfees_tranzact.responsecode = '00' AND 
    return self::find_by_sql($sql);
    
} 


public static function auto_mat($dept = "", $level = "", $prog = ""){
    global $database;
    $sql = "SELECT student_activity_level.matricno from student_activity_level JOIN schoolfees_tranzact ON schoolfees_tranzact.formno =student_activity_level.formno
    WHERE schoolfees_tranzact.department = '$dept' AND schoolfees_tranzact.formno LIKE '%$prog%' AND schoolfees_tranzact.level = '$level' 
    AND responsecode = '00' order by matricno desc limit 1";
    return self::find_by_sql($sql);
} 

public static function view_matricno($dept = "", $level = "", $prog = ""){
    global $database;
    $sql = "SELECT distinct(schoolfees_tranzact.formno),schoolfees_tranzact.surname,schoolfees_tranzact.othernames,schoolfees_tranzact.department,schoolfees_tranzact.email,
    schoolfees_tranzact.level,student_activity_level.matricno from student_activity_level JOIN schoolfees_tranzact ON schoolfees_tranzact.formno = student_activity_level.formno
    WHERE schoolfees_tranzact.department = '$dept' AND schoolfees_tranzact.formno LIKE '%$prog%' AND schoolfees_tranzact.level = '$level' 
    AND responsecode = '00' AND student_activity_level.matricno!=0 ORDER BY matricno ASC";
    return self::find_by_sql($sql);
} 


// ------------------Search Per Department and Programme---------------------------------

public static function searchbydptprog($dept = "", $level = "", $prog = ""){
    global $database;
    $sql = "SELECT distinct(formno),surname,othernames,department,email,level FROM schoolfees_tranzact
    WHERE department = '$dept' AND `formno` LIKE '%$prog%' AND level = '$level' AND responsecode = '00' ORDER BY surname ASC";
    return self::find_by_sql($sql);
}
public static function search_prog($level = "", $prog = ""){
    global $database;
    $sql = "SELECT * FROM schoolfees_tranzact
    WHERE `formno` LIKE '%$prog%' AND programme = '$level' AND responsecode = '00' ORDER BY surname ASC";
    return self::find_by_sql($sql);
}


public static function deptlev($dept = "", $level = ""){
    global $database;
    $sql = "SELECT * FROM schoolfees_tranzact
    WHERE department = '$dept' AND programme = '$level' AND responsecode = '00' ORDER BY surname ASC";
    return self::find_by_sql($sql);
}

//--------------------END-------------------------------------------------


public static function showdept_byf($faculty=""){
global $database;
$sql = "SELECT department FROM department_tbl WHERE faculty = '$faculty'" ;
return self::find_by_sql($sql);
}

public static function showdept_HoD($HoD=""){
global $database;
$sql = "SELECT department FROM department_tbl WHERE department = '$HoD'" ;
return self::find_by_sql($sql);
}

public static function showsession(){
global $database;
$sql = "SELECT  distinct (session) FROM session_semester" ;
return self::find_by_sql($sql);
}

public static function showsemester(){
global $database;
$sql = "SELECT distinct (semester) FROM session_semester" ;
return self::find_by_sql($sql);
}

public static function search_by_formno($formno){
global $database;
$sql = "SELECT * FROM admitted_students WHERE formno = '{$formno}'" ;
return self::find_by_sql($sql);
}

public static function search_by_date($start_date,$end_date){
global $database;
$sql = "SELECT * FROM admitted_students WHERE admitted_date BETWEEN '{$start_date}' AND '{$end_date}'" ;
return self::find_by_sql($sql);
}

public static function search_all($dept, $level, $session, $start_date, $end_date){
global $database;
// $date_search = self :: search_by_date()
$sql = "SELECT  * FROM admitted_students WHERE department= '{$dept}' AND level= '{$level}' AND session= '{$session}' AND admitted_date BETWEEN '{$start_date}' AND '{$end_date}'";
return self::find_by_sql($sql);
}
public static function search_session($dept, $level, $session){
global $database;
// $date_search = self :: search_by_date()
$sql = "SELECT  * FROM admitted_students WHERE department= '{$dept}' AND level= '{$level}' AND session= '{$session}'";
return self::find_by_sql($sql);
}


public static function search_level($level, $session){
global $database;
// $date_search = self :: search_by_date()
$sql = "SELECT  * FROM admitted_students WHERE level= '{$level}' AND session= '{$session}' ORDER BY department ASC";
return self::find_by_sql($sql);
}


public static function search_level_count($level, $session){
global $database;
$sql = "SELECT department, COUNT(formno) FROM admitted_students WHERE level= '{$level}' AND session= '{$session}' GROUP BY department";
return self::find_by_sql($sql);
}

public static function search_date($dept, $level, $session, $start_date, $end_date){
global $database;
// $date_search = self :: search_by_date()
$sql = "SELECT  * FROM admitted_students WHERE department= '{$dept}' OR level= '{$level}' OR session= '{$session}' OR admitted_date BETWEEN '{$start_date}' AND '{$end_date}'";
return self::find_by_sql($sql);
}




public static function joined_tables(){
global $database;
// $formno_class = self::showpayment_online("admission_status");
// $formno = $formno_class->formno;
// $sql = "SELECT  * FROM admission_status JOIN admitted_student_details ON admission_status.formno = admitted_student_details.formno";
$sql = "SELECT  * FROM status JOIN admitted_student_details ON status.formno = admitted_student_details.formno";
return self::find_by_sql($sql);
}


public function insert_table($formno,$surname,$othername,$email,$department,$sex,$level,$session,$admitted_date){
global $database;
	$sql = "INSERT INTO admitted_students (";
    // $sql = "INSERT INTO admitted_student (";
	$sql .= "formno,surname,othernames,email,department,sex,level,session,admitted_date";
	$sql .= ") 	VALUES ('";
	$sql .= $database->escape_value($formno)."', '"	;
	$sql .= $database->escape_value($surname)."', '";
	$sql .= $database->escape_value($othername)."', '";
	$sql .= $database->escape_value($email)."', '";
	$sql .= $database->escape_value($department)."', '";
	$sql .= $database->escape_value($sex)."', '";
	$sql .= $database->escape_value($level)."', '";
	$sql .= $database->escape_value($session)."', '";
	$sql .= $database->escape_value($admitted_date)."') ";
	
	if ($database->query($sql))
	{
// 		$id=$database->insert_id();
		return true;
	}else {
		return false;
	}
	
}





public static function showpayment_online($table=""){
global $database;
$sql = "SELECT * FROM {$table}" ;
return self::find_by_sql($sql);

}



public static function t_processing($id=0, $curr_session=""){
	
global $database;
$user_data = Userdata::find_by_id($id);
 $biodata = Biodata::find_by_id($user_data->formno,$user_data->email);
 $dp = DEPARTMENTCLASS::department_authenticate($biodata->courses);
$alldp = DEPARTMENTCLASS::find_all($dp->faculty);
foreach ($alldp as $aldp){
	$faculty = $aldp->faculty;
}
if ($faculty=='Engineering'or $faculty=='Applied Sciences'or $faculty=='Enviromental Studies'){
$pay_cat='science';	
}elseif ($faculty=='Management Sciences'){
	$pay_cat='nonscience';
}
$level = $biodata->programme;
 $level = $level." "."I";
$time = date('Y-m-d h:i:s');
$a = (substr(rand(1, 10000000000),0,10));
					$txt_ref = $a.(substr( $biodata->surname,0,2));	


$pay_settings=Pay_settings::find_by_id(1,$level,$pay_cat);

$sql = "INSERT INTO ".self ::$table_name ." (";
$sql .= "formno,surname, othername,faculty,department,programme,phone,email,session,level,txt_ref,amount,txt_fees,overhead,datetime,status,responsecode";

$sql .= ") VALUES ('";
$sql .= $biodata->formno."', '";
$sql .= $biodata->surname."', '";
$sql .= $biodata->othernames. "', '";
$sql .=$biodata->courses. "', '";
$sql .=$biodata->courses. "', '";
$sql .=$biodata->programme."', '";
$sql .= $biodata->phone."', '";
$sql.=$biodata->email."', '";
$sql .= $curr_session ."', '";
$sql .=$biodata->programme."', '";
$sql .=$txt_ref."' ,'";
$sql.= $pay_settings->amount."', '"; 
$sql .=$pay_settings->charge_interswitch + $pay_settings->charge_omoluabi."' ,'";
$sql .=$pay_settings->charge_interswitch + $pay_settings->charge_omoluabi."' ,'";
$sql .= $time. "' ,'" ;
$sql .=$biodata->programme."', '";
$sql .='Approved Successful'.
"')" ;

if ($database->query($sql)){
		//$this->id=$database->insert_id();
		return true;
	}else {
		return false;
	}
	
//$result_array = self::find_by_sql($sql);
//return !empty($result_array)? array_shift($result_array) : false;
//return $result_array;

}
public function fullname(){
	
if (isset($this->formno)&& isset($this->username)){
	return $this->formno ." ". $this->username;	
	} else {
		return "";
	}
}



//Common database Methods
public static function find_all(){
	global $database;
	return self::find_by_sql("SELECT * FROM ".self:: $table_name);
	//return $result_set;
	
}
public static function find_by_id($id=0){
	global $database;
	$result_array = self::find_by_sql("SELECT * FROM  ".self::$table_name." WHERE id={$id} LIMIT 1");
	//$found = $database->fetch_array($result_set);
	//return $found;
	return !empty($result_array)? array_shift($result_array) : false;
	
}

public static function find_by_sql($sql=""){
	global $database;
	$result_set = $database->query($sql);
	$object_array = array();
	while ($row= $database->fetch_array($result_set)){
	$object_array[]=self::instantiate($row);
	}
	return $object_array;
	
}
private static function instantiate($record){
	
	$object = new self;
	foreach ($record as $attribute=>$value){
	if ($object->has_attribute($attribute)){
	$object->$attribute = $value;
	}
		
	}
	return $object;
}
private function has_attribute($attribute){
	
$object_vars = get_object_vars($this);
return array_key_exists($attribute,$object_vars);	
}

public function save(){
return isset($this->id) ?	$this->update() : $this->create();	
}

public function create(){
global $database;
	$sql = "INSERT INTO ".self ::$table_name." (";
	$sql .= "formno, email";
	$sql .= ") 	VALUES ('";
	$sql .= $database->escape_value($this->formno)."', '"	;
	$sql .= $database->escape_value($this->email)."') '"	;
	if ($database->query($sql)){
		$this->id=$database->insert_id();
		return true;
	}else {
		return false;
	}
	
}

public function update(){
	
	global $database;
	$sql = "UPDATE ".self ::$table_name."(";
	$sql .="formno = '". $database->escape_value($this->formno)."',"; 
	$sql .= "email ='". $database->escape_value($this->email)."', ";	
	$sql .= "WHERE id=". $database->escape_value($this->id);
	
	$database->query($sql);
return ($database->affected_rows()==1)? true: false;
	}
public function delete(){
	
global $database;
$sql = "DELETE FROM ".self ::$table_name." ";
$sql .= "WHERE id=" . $database->escape_value($this->id);
$sql .= " LIMIT 1";	
$database->query($sql);
return ($database->affected_rows() ==1) ? true: false;
}

//Closing tags for user's class
}
$onlinepay = new ONLINEPAY();
?>