<?php 
try { $db = new PDO('mysql:host=localhost;dbname=brainpa2_outlet;charset=utf8','brainpa2_edu_root','Meprosper@12edu');}
catch(Expression $e){ echo $e->getMessage(); }


/******************************ADMIN FUNCTIONS****************************/
function QueryDB($query){
  global $db;
  return $db->query($query);
}

function stateMsg($val){
  if($val==0){ $msg='<b class="bg-primary text-white badge">&nbsp;Pending&nbsp;</b>';}
  else if($val==1){ $msg='<b class="bg-success text-white badge">&nbsp;Sold&nbsp;</b>';}
  else if($val==2){ $msg='<b class="bg-danger text-white badge">&nbsp;Returned&nbsp;</b>';}
  return $msg;
}

/********Update***************/
function getBiller($id){
  $str = QueryDB("SELECT organization FROM biller where receipt_id ='$id' ");
  return  $str->fetch(PDO::FETCH_ASSOC); 
}

function getState($state_id){
  $get = QueryDB("SELECT name FROM nig_states where id='$state_id' ");
  $cols = $get->fetch(PDO::FETCH_ASSOC);
  return $cols['name'];
}

function getBillerAll($id){
  $str = QueryDB("SELECT * FROM biller where receipt_id ='$id' ");
  $rows =   $str->fetch(PDO::FETCH_ASSOC); 
  return $rows['track_status'].' @ '.$rows['track_status_date'];
}


function getBillerRecent($id){
  $str = QueryDB("SELECT organization FROM biller where user_id ='$id' ORDER BY id DESC LIMIT 1 ");
  return  $str->fetch(PDO::FETCH_ASSOC); 
}


/***************END************/

function getProduct($id){$str = QueryDB("SELECT * FROM products where id ='$id' ");
return $str->fetch(PDO::FETCH_ASSOC);
}

function getProductName($id){$str = QueryDB("SELECT item_name FROM products where id ='$id' ");$ld =  $str->fetch(PDO::FETCH_ASSOC); return $ld['item_name'];
}

function get_branch_name($id){$str = QueryDB("SELECT shrt_name FROM branch where id ='$id' ");$ld =  $str->fetch(PDO::FETCH_ASSOC); return $ld['shrt_name'];
}

function pay_MTH($id){$str = QueryDB("SELECT mth_name FROM  paymth  where id ='$id' ");$ld =  $str->fetch(PDO::FETCH_ASSOC); return $ld['mth_name'];
}

function get_loaded_qty($itemId){ $add = 0;
 foreach(QueryDB("SELECT item_qty,item_name FROM storekeeper ")as $getQty){
  if($getQty['item_name']==$itemId){$add +=$getQty['item_qty'];}}
 return $add;
}



function get_std_ID($od){ 
  $init = QueryDB("SELECT id FROM customers where cus_id ='$od' ");
  $row = $init->fetch(PDO::FETCH_ASSOC);  return $row['id'];
}

function get_cus_Name($od){ 
  $init = QueryDB("SELECT frm_name FROM customers where id ='$od' ");
  $row = $init->fetch(PDO::FETCH_ASSOC);  return $row['frm_name'];
}

function get_cus_type($id){
  $str = QueryDB("SELECT type FROM category where id ='$id' ");
  $lp =  $str->fetch(PDO::FETCH_ASSOC);
  return $lp['type'];
}

function organization($id){
  $str = QueryDB("SELECT organization FROM biller where receipt_id ='$id' ");
  $lp =  $str->fetch(PDO::FETCH_ASSOC);
  return $lp['organization'];
}

function get_staff_name($staff_email){
$st = QueryDB("SELECT name FROM login where username ='$staff_email' ");
$row = $st->fetch(PDO::FETCH_ASSOC);
return $row['name'];
}

function totalPaid($id){ $add = 0;
foreach(QueryDB("SELECT amt_paid FROM payment where receipt_id ='$id' ")as $pt){
$add+=$pt['amt_paid'];
}
return $add;
}

function lastStock($id,$dateSet,$loadCode){
$st = QueryDB("SELECT * FROM storekeeper where item_name ='$id' $dateSet $loadCode ORDER BY id DESC LIMIT 1 ");
return  $st->fetch(PDO::FETCH_ASSOC);
}

function lastStockWhouse($id,$dateSet,$loadCode){
$st = QueryDB("SELECT * FROM storekeeper where item_name ='$id' $dateSet $loadCode and branch_code=0; ORDER BY id DESC LIMIT 1 ");
return  $st->fetch(PDO::FETCH_ASSOC);
}

function lastStock_perBranch($id,$dateSet,$loadCode,$brch_code){
$st = QueryDB("SELECT * FROM branch_store where item_name ='$id' $dateSet $loadCode and branch_code='$brch_code' ORDER BY id DESC LIMIT 1 ");
return  $st->fetch(PDO::FETCH_ASSOC);
}


function totalStockWHouse($id,$dateSet,$load_code){
$st = QueryDB("SELECT SUM(item_qty) as qty FROM storekeeper where item_name ='$id' $dateSet and branch_code=0  ");
$des = $st->fetch(PDO::FETCH_ASSOC); return $des['qty'];
}



function totalStock($id,$dateSet,$load_code){
$st = QueryDB("SELECT SUM(item_qty) as qty FROM storekeeper where item_name ='$id' $dateSet $load_code  ");
$des = $st->fetch(PDO::FETCH_ASSOC); return $des['qty'];
}

function totalStockSupplied($id,$dateSet,$load_code){
$st = QueryDB("SELECT SUM(item_qty) as qty FROM storekeeper where item_name ='$id' $dateSet $load_code and item_qty < 0  ");
$des = $st->fetch(PDO::FETCH_ASSOC); return $des['qty'];
}

function totalStock_branch($id,$dateSet,$load_code,$brch_code){
$st = QueryDB("SELECT SUM(item_qty) as qty FROM branch_store where item_name ='$id' $dateSet $load_code and branch_code='$brch_code' ");
$des = $st->fetch(PDO::FETCH_ASSOC); return $des['qty'];
}



function getQuantity($itemId,$payDate,$dateSet){ $add = 0; //$loadedQty = totalStock($itemId,$dateSet);
 foreach(QueryDB("SELECT item,qty FROM payment where pay_status=1 $payDate GROUP BY receipt_id ")as $countItem){
  if(in_array($itemId,explode(',',$countItem['item']))){
    $key = array_search($itemId,explode(',',$countItem['item']));
    $array = explode(',',$countItem['qty']);
    $add +=$array[$key];}}
 return $add;
}




function getQuantity_Branch($itemId,$payDate,$dateSet,$branchcode){ $add = 0; 
 foreach(QueryDB("SELECT item,qty FROM payment where pay_status=1 $payDate and branchcode='$branchcode' GROUP BY receipt_id ")as $countItem){
  if(in_array($itemId,explode(',',$countItem['item']))){
    $key = array_search($itemId,explode(',',$countItem['item']));
    $array = explode(',',$countItem['qty']);
    $add +=$array[$key];}}
 return $add;
}





function numberTowords($num)
{
$ones = array(
0 =>"ZERO",
1 => "ONE",
2 => "TWO",
3 => "THREE",
4 => "FOUR",
5 => "FIVE",
6 => "SIX",
7 => "SEVEN",
8 => "EIGHT",
9 => "NINE",
10 => "TEN",
11 => "ELEVEN",
12 => "TWELVE",
13 => "THIRTEEN",
14 => "FOURTEEN",
15 => "FIFTEEN",
16 => "SIXTEEN",
17 => "SEVENTEEN",
18 => "EIGHTEEN",
19 => "NINETEEN",
"014" => "FOURTEEN"
);
$tens = array( 
0 => "ZERO",
1 => "TEN",
2 => "TWENTY",
3 => "THIRTY", 
4 => "FORTY", 
5 => "FIFTY", 
6 => "SIXTY", 
7 => "SEVENTY", 
8 => "EIGHTY", 
9 => "NINETY" 
); 
$hundreds = array( 
"HUNDRED", 
"THOUSAND", 
"MILLION", 
"BILLION", 
"TRILLION", 
"QUARDRILLION" 
); /*limit t quadrillion */
$num = number_format($num,2,".",","); 
$num_arr = explode(".",$num); 
$wholenum = $num_arr[0]; 
$decnum = $num_arr[1]; 
$whole_arr = array_reverse(explode(",",$wholenum)); 
krsort($whole_arr,1); 
$rettxt = ""; 
foreach($whole_arr as $key => $i){
  
while(substr($i,0,1)=="0")
    $i=substr($i,1,5);
if($i < 20){ 
/* echo "getting:".$i; */
$rettxt .= $ones[$i]; 
}elseif($i < 100){ 
if(substr($i,0,1)!="0")  $rettxt .= $tens[substr($i,0,1)]; 
if(substr($i,1,1)!="0") $rettxt .= " ".$ones[substr($i,1,1)]; 
}else{ 
if(substr($i,0,1)!="0") $rettxt .= $ones[substr($i,0,1)]." ".$hundreds[0]; 
if(substr($i,1,1)!="0")$rettxt .= " ".$tens[substr($i,1,1)]; 
if(substr($i,2,1)!="0")$rettxt .= " ".$ones[substr($i,2,1)]; 
} 
if($key > 0){ 
$rettxt .= " ".$hundreds[$key]." "; 
}
} 
if($decnum > 0){
$rettxt .= " and ";
if($decnum < 20){
$rettxt .= $ones[$decnum];
}elseif($decnum < 100){
$rettxt .= $tens[substr($decnum,0,1)];
$rettxt .= " ".$ones[substr($decnum,1,1)];
}
}
return $rettxt;
}

function convertNumberToWord($num = false)
{
    $num = str_replace(array(',', ' '), '' , trim($num));
    if(! $num) {
        return false;
    }
    $num = (int) $num;
    $words = array();
    $list1 = array('', 'one', 'two', 'three', 'four', 'five', 'six', 'seven', 'eight', 'nine', 'ten', 'eleven',
        'twelve', 'thirteen', 'fourteen', 'fifteen', 'sixteen', 'seventeen', 'eighteen', 'nineteen'
    );
    $list2 = array('', 'ten', 'twenty', 'thirty', 'forty', 'fifty', 'sixty', 'seventy', 'eighty', 'ninety', 'hundred');
    $list3 = array('', 'thousand', 'million', 'billion', 'trillion', 'quadrillion', 'quintillion', 'sextillion', 'septillion',
        'octillion', 'nonillion', 'decillion', 'undecillion', 'duodecillion', 'tredecillion', 'quattuordecillion',
        'quindecillion', 'sexdecillion', 'septendecillion', 'octodecillion', 'novemdecillion', 'vigintillion'
    );
    $num_length = strlen($num);
    $levels = (int) (($num_length + 2) / 3);
    $max_length = $levels * 3;
    $num = substr('00' . $num, -$max_length);
    $num_levels = str_split($num, 3);
    for ($i = 0; $i < count($num_levels); $i++) {
        $levels--;
        $hundreds = (int) ($num_levels[$i] / 100);
        $hundreds = ($hundreds ? ' ' . $list1[$hundreds] . ' hundred' . ' ' : '');
        $tens = (int) ($num_levels[$i] % 100);
        $singles = '';
        if ( $tens < 20 ) {
            $tens = ($tens ? ' ' . $list1[$tens] . ' ' : '' );
        } else {
            $tens = (int)($tens / 10);
            $tens = ' ' . $list2[$tens] . ' ';
            $singles = (int) ($num_levels[$i] % 10);
            $singles = ' ' . $list1[$singles] . ' ';
        }
        // $words[] = $hundreds . ' and '.$tens .$singles . ( ( $levels && ( int ) ( $num_levels[$i] ) ) ? ' ' . $list3[$levels] . ' ' : '' );

        $words[] = $hundreds . ' '.$tens .$singles . ( ( $levels && ( int ) ( $num_levels[$i] ) ) ? ' ' . $list3[$levels] . ' ' : '' );
    } //end for loop
    $commas = count($words);
    if ($commas > 1) {
        $commas = $commas - 1;
    }
    return implode(' ', $words);
}


function remAND($var){if(trim(substr($var,-4))=='and')
{$var =substr($var,0,-4);}
return $var;
}

function getPayment($month,$yr,$val,$status){
if(!empty($month)){ $month='and month='.$month;}
if(!empty($yr)){ $yr='and year='.$yr;}
$find = QueryDB("SELECT SUM($val) as amtp FROM payment where id!='' $month $yr and pay_status='$status' ");
  $row = $find->fetch(PDO::FETCH_ASSOC);
  return $row['amtp'];
}

function getYrMonthReg($yr,$mnth){
$find =  QueryDB("SELECT SUM(amt_paid) as pymt FROM payment where year='$yr' and month='$mnth' and pay_status=1");
$row = $find->fetch(PDO::FETCH_ASSOC);
  return $row['pymt'];

}

function stateStats($val){
if($val==1){$val ='(open)';}
else{ $val ='(lock)';}
return $val;
}


function actType($val){
if($val==1){$val ='Attendant';}
else{ $val ='Manager';}
return $val;
}

function get_total_payment($id,$value,$stats){ $start = 0; 
$init=QueryDB("SELECT SUM($value) as total FROM payment where receipt_id='$id' and pay_status='$stats' ");
  $row = $init->fetch(PDO::FETCH_ASSOC);
  return $row['total'];
}

function get_my_purchase($id,$value,$stats){ $start = 0; 
$init=QueryDB("SELECT SUM($value) as total FROM payment where cus_id='$id' and pay_status='$stats' ");
  $row = $init->fetch(PDO::FETCH_ASSOC);
  return $row['total'];
}













function get_reg_id($id){
 $str = QueryDB("SELECT ref_id FROM fillup where id ='$id' ");
  $rd = $str->fetch(PDO::FETCH_ASSOC); 
  return $rd['ref_id'];
}



//get Fillup with Reg Id
function fill_up_info($id){
  $str = QueryDB("SELECT * FROM fillup where ref_id ='$id' ");
  return $str->fetch(PDO::FETCH_ASSOC);
}




function get_course($crs_id){
$st = QueryDB("SELECT * FROM courses where id ='$crs_id' ");
return $st->fetch(PDO::FETCH_ASSOC);

}

function collect($val){ return $_POST[$val];}

function code_generator($length){
  return substr(str_shuffle('ABCDEFGHIJKLMNOPQRSTUVWXYZ'),0,$length).rand(10,99);
}



function get_std($std_id){ global $dbconn;
$get_logged_in = QueryDB("SELECT * FROM reg where reg_id='$std_id' ");
return $get_logged_in->fetch(PDO::FETCH_ASSOC);
}

function get_std_reg_number($std_id){ global $dbconn;
$get_logged_in = QueryDB("SELECT reg_id FROM reg where id='$std_id' ");
$ops = $get_logged_in->fetch(PDO::FETCH_ASSOC);
return $ops['reg_id'];
}


function getAppSource(){
  $init = QueryDB("SELECT * FROM appsource ");
    return $init->fetch(PDO::FETCH_ASSOC);
}

function getAppBranchSource($branch_code){
  $init = QueryDB("SELECT * FROM branch where id='$branch_code'  ");
    return $init->fetch(PDO::FETCH_ASSOC);
}


function get_course_name($crs_id){ global $dbconn;
$st = QueryDB("SELECT crs_title FROM courses where id ='$crs_id' ");
$row = $st->fetch(PDO::FETCH_ASSOC);
return $row['crs_title'];
}

function get_duration($crs_id){ global $dbconn;
$st = QueryDB("SELECT crs_duration FROM courses where id ='$crs_id' ");
$row = $st->fetch(PDO::FETCH_ASSOC);
return $row['crs_duration'];
}

function get_desc($crs_id){ global $dbconn;
$st = QueryDB("SELECT crs_desc FROM courses where id ='$crs_id' ");
$row = $st->fetch(PDO::FETCH_ASSOC);
return $row['crs_desc'];
}

function crs_Reg($crs_id){ global $dbconn;
$st = QueryDB("SELECT reg_cost FROM courses where id ='$crs_id' ");
$row = $st->fetch(PDO::FETCH_ASSOC);
return $row['reg_cost'];
}

function sort_code($crs_id){ global $dbconn;
$st = QueryDB("SELECT sort_code FROM courses where id ='$crs_id' ");
$row = $st->fetch(PDO::FETCH_ASSOC);
return $row['sort_code'];
}

function sort_Cert($crs_id){ global $dbconn;
$st = QueryDB("SELECT certify FROM courses where id ='$crs_id' ");
$row = $st->fetch(PDO::FETCH_ASSOC);
return $row['certify'];
}


function get_total_month_reg(){ global $dbconn;
return QueryDB("SELECT COUNT(*) FROM fillup where month ='".date('m')."' ")->fetchColumn();
}

function get_total_year_reg(){ global $dbconn;
return QueryDB("SELECT COUNT(*) FROM fillup where year ='".date('Y')."' ")->fetchColumn();
}







function getCost($crs_id){ global $dbconn;
$st = QueryDB("SELECT crs_price FROM courses where id ='$crs_id' ");
$row = $st->fetch(PDO::FETCH_ASSOC);
return $row['crs_price'];
}

function get_course_code($crs_id){ global $dbconn;
$st = QueryDB("SELECT sort_code FROM courses where id ='$crs_id' ");
$row = $st->fetch(PDO::FETCH_ASSOC);
return date('y').$row['sort_code'];
}

function generateAdmnNo($crs_id){
  $crs_sort_code = get_course_code($crs_id);
if(QueryDB("SELECT COUNT(*) FROM fillup where enrollment_code LIKE '$crs_sort_code%'")->fetchColumn()>0){
$start =QueryDB("SELECT enrollment_code FROM fillup where enrollment_code LIKE '$crs_sort_code%' ");
  $get = $start->fetch(PDO::FETCH_ASSOC);
  $get_last_digits = substr($get['enrollment_code'],-3,3);
  $new_reg_number =$crs_sort_code.str_pad($get_last_digits+1,3,"0",STR_PAD_LEFT);
}else{
  $new_reg_number =$crs_sort_code.'001'; 
}
  return $new_reg_number;

}




function get_reg_count($code){ global $dbconn;
return QueryDB("SELECT COUNT(*) FROM payment where std_id ='$code' and type='reg' ")->fetchColumn();
}

function get_eform_count($code){ global $dbconn;
return QueryDB("SELECT COUNT(*) FROM fillup where ref_id ='$code'  ")->fetchColumn();
}





function conV($val){ $msg='';
  if($val==1){ $msg='Cash';}
  if($val==2){ $msg='MPT';}
  if($val==3){ $msg='FCMB';}
  if($val==4){ $msg='Card';}
  return $msg;
}




function courseLink($od){ 
  $init = QueryDB("SELECT link FROM courses where id ='$od' ");
  $row = $init->fetch(PDO::FETCH_ASSOC);  
  return $row['link'];
}

function certify($od){ 
  $init = QueryDB("SELECT certify FROM courses where id ='$od' ");
  $row = $init->fetch(PDO::FETCH_ASSOC);  
  return $row['certify'];
}


function reg_courses_list($od){ $getIt = array();
  foreach(QueryDB("SELECT course FROM payment where std_id ='$od' ")as $mock){
    $getIt[] =$mock['course']; 
  }
  return $getIt;
}

function my_fillUp_courses($od){ $getIt = array();
  foreach(QueryDB("SELECT course_ FROM fillup where ref_id ='$od' ")as $mock){
    $getIt[] =$mock['course_']; 
  }
  return $getIt;
}


function finishDate($od){ $getIt = array();
  $lp = QueryDB("SELECT doc FROM fillup where enrollment_code ='$od' ");
  $rows = $lp->fetch(PDO::FETCH_ASSOC);
  return $rows;
}





function get_my_course_list($od){ $html ='<ol style="line-height:35px;">';
 foreach(QueryDB("SELECT * FROM fillup where ref_id ='$od' ")as $my){
$html .='<li style="text-align: left;"><b>'.get_course_name($my['course_']).'</b>
&nbsp;<a href="?ref_id_set='.$my['ref_id'].'&admn_no_set='.$my['enrollment_code'].'&crs_link='.$my['course_'].'"><i class="fa fa-list"></i></a>
</li>';
  }
  $html .='</ul>';
  return $html;
}

function get_my_course_date_reg($od){ $html ='<ul style="line-height:35px;list-style-type:square">';
 foreach(QueryDB("SELECT * FROM fillup where ref_id ='$od' ")as $my){
$html .='<li style="text-align: left;"><b>'.($my['nil']).'</b></li>';
  }
  $html .='</ul>';
  return $html;
}

function fill_up_registrar($od){ $html ='<ul style="line-height:35px;list-style-type:square">';
 foreach(QueryDB("SELECT * FROM fillup where ref_id ='$od' ")as $my){
$html .='<li style="text-align: left;"><b>'.get_staff_name($my['staff']).'</b></li>';
  }
  $html .='</ul>';
  return $html;
}



function get_my_course_listActions($od){ $html ='<ol style="line-height:35px;">';
 foreach(QueryDB("SELECT * FROM fillup where ref_id ='$od' ")as $my){
      $html .='<li style="text-align: left;list-style-type:none"><a class="btn btn-primary btn-sm" href="?generate_report='.$my['id'].'" title="Report"><i class="fa fa-file text-white"></i></a>

<a class="btn btn-dark btn-sm" href="editfillup?editForm='.$my['id'].'&myReg='.$my['ref_id'].'" title="Report"><i class="fa fa-edit text-warning"></i></a>&nbsp; ';
if(empty($my['enrollment_code'])){
$html.='<a class="btn btn-info btn-sm" href="payFirst?paycourse='.$my['id'].'&myRegNumb='.$my['ref_id'].'" title="Report"><b>&#8358;</b></a>
      </li>';
    }else {
      $html.='<a href="receeipts?no='.$my['enrollment_code'].'" class="btn btn-success btn-sm"  title="Report"><i class="fa fa-check-square"></i></a>
      </li>';
    }
  }
  $html .='</ul>';
  return $html;
}

function get_total_item($item_id){ $start = 0; 
  $init = QueryDB("SELECT SUM(qty) as total FROM load_item where item ='$item_id' ");
  $row = $init->fetch(PDO::FETCH_ASSOC);
  return $row['total'];
}

function get_last_total_item($item_id){ $start = 0; 
  $init = QueryDB("SELECT qty,date_time,time_load FROM load_item where item ='$item_id' ORDER BY id DESC LIMIT 1 ");
  $row = $init->fetch(PDO::FETCH_ASSOC);
  return $row['qty'].'<br/>'.$row['date_time'].'<br/>'.date('h:i:s A',$row['time_load']);
}

function get_current_qty($item_id){ $start = 0; 
  $init = QueryDB("SELECT SUM(qty) as total FROM load_item where item ='$item_id' ");
  $row = $init->fetch(PDO::FETCH_ASSOC);
  $totals= $row['total'];

  $init2 = QueryDB("SELECT COUNT(DISTINCT receipt_id) as total2 FROM payment where course ='$item_id' and type=1   ");
  $row2 = $init2->fetch(PDO::FETCH_ASSOC);;
  $total2= $row2['total2'];
return $totals-$total2;

}

 /**************************************************************************/



?>