/home/mip/public_html_/old_mip/admin/func_select.php
<?
/* The collection of functions that query the database in select statement. */
require_once("config.php");
require_once("func_proc.php");
/* The function getdata() get all the data in a table. */
function getdata($query){
$db = db_connect();
if (!$db){
echo "Error connecting to database.</center>\n";
exit;
}
$count = 1;
@$result = mysql_query($query, $db);
@$num_rows = mysql_num_rows($result);
if ($num_rows > 0)
{
while ($myrow = mysql_fetch_array($result)){
foreach($myrow as $key => $value){
if (!is_numeric($key)){
$row[$key]=stripslashes($value);
}
}
$array1[$count] = $myrow;
$count++;
}
return $array1;
}
else return $array1;
}// end of function getdata
// select one field only
function getdata_one($select,$table,$field,$value)
{
//extract from the database all users
if (!($conn = db_connect()))
return false;
$sql = "select $select from $table where $field = ";
if (is_numeric($value)){$sql .= " $value";}
else {$sql .= " '$value'";}
$result = mysql_query($sql);
if (!$result || (mysql_num_rows($result)<=0))
return false;
$ans = mysql_fetch_array ($result);
foreach ($ans as $key => $value) {
$ans[$key]=stripslashes($value);
}
return $ans;
}
/* The function getname() get the equivalent name of an id */
function getname($id,$table,$field){
$db = db_connect();
if (!$db){
echo "Error connecting to database.</center>\n";
exit;
}
$query = "select name from $table where $field=$id";
$result = mysql_query($query,$db);
$myrow = mysql_fetch_array($result);
return $myrow[0];
}// end of function getname
/* The function getname() get the equivalent name of an id */
function getfield($what,$table,$field,$id){
$db = db_connect();
if (!$db){
echo "Error connecting to database.</center>\n";
exit;
}
$query = "select $what from $table where $field='$id'";
$result = mysql_query($query,$db);
$myrow = mysql_fetch_array($result);
return $myrow[0];
}// end of function getname
/* The function get_applicantname() get the equivalent name of an applicant_id */
function get_applicantname($applicant_id){
$db = db_connect();
if (!$db){
echo "Error connecting to database.</center>\n";
exit;
}
$query = "select (concat(lname,', ',fname,' ',mname)) as applicant_name from personal_live where applicant_id='$applicant_id'";
$result = mysql_query($query,$db);
$myrow = mysql_fetch_array($result);
return $myrow[0];
}// end of function get_applicantname
/* The function get_num_applied in a campaign or a job order()*/
function get_num_applied($job_order_id){
$db = db_connect();
if (!$db){
echo "Error connecting to database.</center>\n";
exit;
}
$query = "SELECT count(job_order_id) as num
FROM process
WHERE job_order_id=$job_order_id";
$result = mysql_query($query,$db);
$myrow = mysql_fetch_array($result);
if($result) return $myrow[num];
else return 0;
}// end of function get_num_applied()
function get_max_id()
{
$db = db_connect();
$sql = "select max(applicant_id) from personal ";
$result = mysql_query($sql, $db);
$row = mysql_fetch_array ($result);
$row[0];
if($row[0]) return $row[0];
else return 0;
}
// function to get the applicant id
function get_applicant_id(){
$max_id = get_max_id();
$year = substr($max_id, 3, 2);
$this_year = date("y");
if ($year != $this_year){
$applicant_id = "LB-$this_year-0001";
}
else{
$num3 = substr($max_id, 6, 4);
$num = $num3 + 1;
$applicant_id="LB-$year-".str_pad($num, 4, "0", STR_PAD_LEFT);
}
return($applicant_id);
}
function get_balance($applicant_id="")
{
$db = db_connect();
/** payment **/
$payment = getdata("select payment_id,total_amount from payment where applicant_id='$applicant_id'");
$num_debits = count($payment);
for($i=1;$i<=$num_debits;$i++){
$payment_id = $payment[$i][payment_id];
$payment_detail = getdata("select SUM(amount) as total_paid from payment_detail where payment_id=$payment_id GROUP by payment_id");
$balance = $payment[$i][total_amount] - $payment_detail[1][total_paid];
$bal = $bal + $balance;
$total_bill = $total_bill + $payment[$i][total_amount];
$total_paid = $total_paid + $payment_detail[1][total_paid];
}
$bill = array("total_bill"=>"$total_bill","total_paid"=>"$total_paid","balance"=>"$bal");
/** end of payment **/
if(empty($bill)) return 0;
else return $bill;
}// end of function get_balance
function check_if_full($jopos_id){
$jop = getdata("select no_of_male as m,no_of_female as f,no_of_coed as c from jo_position where jo_pos_id='$jopos_id'");
$num = getdata("select COUNT(jo_pos_id) AS n from process where jo_pos_id='$jopos_id'");
$tot_jop = $jop[1][m]+$jop[1][f]+$jop[1][c];
$available = $tot_jop - $num[1][n];
if($available >= 1 ) return(0);
else return(1);
}
function get_yrs_exp($applicant_id)
{
$db = db_connect();
if (!$db){
echo "Can't connect to the MySQL database...";
exit;
}
$sql = "SELECT sum(exp_yrs) FROM employment WHERE applicant_id = '$applicant_id'";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
$exp_yrs = $row[0];
$exp_yrs = number_format($exp_yrs,1);
if($exp_yrs!=0.0) return $exp_yrs;
else return;
}
/* Function to connect to the database */
function db_connect(){
global $db_host ,$db_name, $db_username, $db_password;
$result = @mysql_pconnect("$db_host", "$db_username","$db_password");
if (!$result) return false;
if (!@mysql_select_db("$db_name")) return false;
return($result);
}
/* Function to check if the visitor is a valid IRIS user. */
function login($username,$password){
$db = db_connect();
if (!$db){
echo "Can't connect to the MySQL database...";
exit;
}
$query = "SELECT user_id,access_id FROM users WHERE username='$username' AND password=password('$password') ";
$result = mysql_query($query,$db);
$myrow = mysql_fetch_array($result);
/*
if($myrow[access_id] == 1){
repair_tables(); // to repair the tables
}
*/
if (!$result) return 0;
if (mysql_num_rows($result)>0) return $myrow[access_id];
else return 0;
}
function repair_tables(){
$db = db_connect();
if (!$db){
echo "Can't connect to the MySQL database...";
exit;
}
mysql_query("REPAIR TABLE brokers",$db);
mysql_query("REPAIR TABLE clinics",$db);
mysql_query("REPAIR TABLE complaints",$db);
mysql_query("REPAIR TABLE country",$db);
mysql_query("REPAIR TABLE education",$db);
mysql_query("REPAIR TABLE employment",$db);
mysql_query("REPAIR TABLE family",$db);
mysql_query("REPAIR TABLE jo_position",$db);
mysql_query("REPAIR TABLE job_order",$db);
mysql_query("REPAIR TABLE medical",$db);
mysql_query("REPAIR TABLE payment",$db);
mysql_query("REPAIR TABLE payment_detail",$db);
mysql_query("REPAIR TABLE personal",$db);
mysql_query("REPAIR TABLE positions",$db);
mysql_query("REPAIR TABLE prequalify",$db);
mysql_query("REPAIR TABLE principals",$db);
mysql_query("REPAIR TABLE process",$db);
mysql_query("REPAIR TABLE training",$db);
mysql_query("REPAIR TABLE users",$db);
return;
}
?>
