/home/mip/www/img/credit/datatables/func_select.php.tar
home/mip/public_html_/old-website/employer/func_select.php 0000644 00000043613 15152075013 0020005 0 ustar 00 <?
/* The collection of functions that query the database in select statement. */
$applicant_status = getdata("select * from applicant_status order by orderid");
$num_applicant_status = count($applicant_status);
function report_headers() {
global $site_url;
$company = getdata_one("*","company",1,1);
$head = '<table border=0 width=100% cellspacing=3 cellpadding=3>';
$head .= '<tr><td valign="middle" align="'.$company['report_header_align'].'">';
$head .= ($company['report_header']=='Image')? resize_pic(750,50,"http://".$site_url.$company['agency_logo'],"","") : "<span style=\"font-size:".$company['report_header_topsize']."px; font-weight:".$company['report_header_weight'].";font-family:".$company['report_header_font'].";\">".$company['agency_name']."</span><br>
<span style=\"font-size:".$company['report_header_subsize']."px; font-weight:".$company['report_header_weight'].";font-family:".$company['report_header_font'].";\">".$company['agency_address']."</span><br>
<span style=\"font-size:".$company['report_header_subsize']."px; font-weight:".$company['report_header_weight'].";font-family:".$company['report_header_font'].";\">".$company['agency_tel']."</span><br>
<span style=\"font-size:".$company['report_header_subsize']."px; font-weight:".$company['report_header_weight'].";font-family:".$company['report_header_font'].";\">".$company['agency_email']."</span>";
$head .= '</td></tr>';
$head .= '</table>';
return $head;
}
function get_morefield($fieldname,$tablename,$applicant_id) {
$pos1 = strrpos($fieldname, ",");
if ($pos1 === false) {
if($tablename=='medical') {
return getfieldformat($fieldname,$tablename.", clinics","applicant_id",$applicant_id,"and medical.clinic_id = clinics.clinic_id order by date desc limit 1");
} elseif($tablename=='doc_library') {
return getfieldformat($fieldname,$tablename,"applicant_id",$applicant_id,"order by date_submitted desc, id desc limit 0,1");
} else {
return getfieldformat($fieldname,$tablename,"applicant_id",$applicant_id);
}
} else {
$string=explode(",",$fieldname);
for($i=0;$i<count($string);$i++) {
$a = $a." ".getfieldformat(trim($string[$i]),$tablename,"applicant_id",$applicant_id);
}
return $a;
}
}
/* The function getname() get the equivalent name of an id */
function getfieldformat($what,$table,$field,$id, $and=""){
$db = db_connect();
if (!$db){
echo "Error connecting to database.</center>\n";
exit;
}
$query = "select $what from $table where $field='$id' $and";
$result = mysql_query($query,$db);
$myrow = mysql_fetch_array($result);
$newstring = "";
switch($what) {
//personal
case "apply_date":
$newstring = @dateformat($myrow[0]);
break;
case "availability":
$newstring = @dateformat($myrow[0]);
break;
case "birthdate":
$newstring = @dateformat($myrow[0]);
break;
case "sex":
if($myrow[0]=='M') { $newstring = "Male"; }
if($myrow[0]=='F') { $newstring = "Female"; }
break;
case "position_id1":
$newstring = @getname($myrow[0],"positions","position_id");
break;
case "position_id2":
$newstring = @getname($myrow[0],"positions","position_id");
break;
case "country_id":
$newstring = @getname($myrow[0],"country","country_id");
break;
case "branch_id":
if($myrow[0]=='0') {
$newstring = ' ';
} else {
$newstring = @getname($myrow[0],"branch","id");
}
break;
case "source_id":
$newstring = @getsource($myrow[0],"source","id");
break;
case "date_blacklisted":
$newstring = @dateformat($myrow[0]);
break;
case "date_reserved":
$newstring = @dateformat($myrow[0]);
break;
// medical
case "date":
$newstring = dateformat($myrow[0]);
break;
case "clinic_id":
$newstring = @getname($myrow[0],"clinics","clinic_id");
break;
// process
case "principal_id":
$newstring = @getname($myrow[0],"principals","principal_id");
break;
case "job_acceptance":
$newstring = dateformat($myrow[0]);
break;
case "direct_hire":
$newstring = dateformat($myrow[0]);
if($myrow[0]=='0') { $newstring = "No"; }
if($myrow[0]=='1') { $newstring = "Yes"; }
break;
case "visa_type":
$newstring = dateformat($myrow[0]);
if($myrow[0]=='0') { $newstring = "Block Visa"; }
if($myrow[0]=='1') { $newstring = "Unique Visa"; }
break;
case "pdos":
$newstring = dateformat($myrow[0]);
break;
case "attest":
$newstring = dateformat($myrow[0]);
break;
case "drug_test":
$newstring = dateformat($myrow[0]);
break;
case "pp_date_issued":
$newstring = dateformat($myrow[0]);
break;
case "pp_date_expires":
$newstring = dateformat($myrow[0]);
break;
case "visa_filed":
$newstring = dateformat($myrow[0]);
break;
case "visa_released":
$newstring = dateformat($myrow[0]);
break;
case "poea_filed":
$newstring = dateformat($myrow[0]);
break;
case "poea_released":
$newstring = dateformat($myrow[0]);
break;
case "ticket":
$newstring = dateformat($myrow[0]);
break;
case "deployment":
$newstring = dateformat($myrow[0]);
break;
case "cla_date":
$newstring = dateformat($myrow[0]);
break;
case "arrival":
$newstring = dateformat($myrow[0]);
break;
case "dateselect":
$newstring = dateformat($myrow[0]);
break;
case "job_order_id":
$newstring = @getfield("job_order_no","job_order","job_order_id",$myrow[0]);
//$newstring = $myrow[0];
break;
case "jo_pos_id":
$newstring = @getname(getfield("position_id","jo_position","jo_pos_id",$myrow[0]),"positions","position_id");
break;
case "visa_id":
$newstring = @getfield("visa_no","visa","visa_id",$myrow[0]);
break;
case "visa_pos_id":
$newstring = @getfield("position_id","visa_position","visa_pos_id",$myrow[0]);
break;
case "job_order_id_poea":
$newstring = @getfield("job_order_no","job_order_poea","job_order_id",$myrow[0]);
break;
case "visa_id_poea":
$newstring = @getfield("visa_no","visa","visa_id",$myrow[0]);
break;
case "visa_pos_id_poea":
$newstring = @getfield("position_id","visa_position","visa_pos_id",$myrow[0]);
break;
case "visa_expire":
$newstring = dateformat($myrow[0]);
break;
case "oec_filed":
$newstring = dateformat($myrow[0]);
break;
case "oec_released":
$newstring = dateformat($myrow[0]);
break;
case "flightdate":
$newstring = dateformat($myrow[0]);
break;
case "job_order_id_poea":
$newstring = @getfield("job_order_no","job_order_poea","job_order_id",$myrow[0]);
//$newstring = $myrow[0];
break;
case "jo_pos_id_poea":
$newstring = @getname(getfield("position_id","jo_position_poea","jo_pos_id",$myrow[0]),"positions","position_id");
break;
// document
case "date_issued":
$newstring = dateformat($myrow[0]);
break;
case "type_id":
$newstring = @getname($myrow[0],"doc_type","type_id");
break;
case "date_expired":
$newstring = dateformat($myrow[0]);
break;
case "date_submitted":
$newstring = dateformat($myrow[0]);
break;
case "date_withdrawn":
$newstring = dateformat($myrow[0]);
break;
default : $newstring = $myrow[0];
}
//return $myrow[0];
return $newstring;
}// end of function getname
/* The function get_count() get the equivalent number records in a table*/
function get_count_deployed($table,$field=1,$id=1,$and=""){
$db = db_connect();
if (!$db){
echo "Error connecting to database.</center>\n";
exit;
}
$query = "select count(*) from $table,personal where $field='$id' $and and $table.applicant_id = personal.applicant_id";
$result = mysql_query($query,$db);
echo mysql_error();
$myrow = mysql_fetch_array($result);
return $myrow[0];
}// end of function getname
/* 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 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()
/* The function get_num_vacancies in a campaign or a job order()*/
function get_num_vacancies($job_order_id){
$db = db_connect();
if (!$db){
echo "Error connecting to database.</center>\n";
exit;
}
$query = "SELECT (SUM(no_of_male + no_of_female + no_of_coed)) as num_vacancies
FROM jo_position
WHERE job_order_id=$job_order_id";
$result = mysql_query($query,$db);
$myrow = mysql_fetch_array($result);
if($result) return $myrow[num_vacancies];
else return 0;
}// end of function get_num_vacancies()
/* The function get_num_applied in a campaign or a job order()*/
function get_num_applied_invisa($visa_id){
$db = db_connect();
if (!$db){
echo "Error connecting to database.</center>\n";
exit;
}
$query = "SELECT count(visa_id) as num
FROM process
WHERE visa_id=$visa_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()
/* The function get_num_vacancies in a campaign or a job order()*/
function get_num_vacancies_invisa($visa_id){
$db = db_connect();
if (!$db){
echo "Error connecting to database.</center>\n";
exit;
}
$query = "SELECT (SUM(no_of_male + no_of_female + no_of_coed)) as num_vacancies
FROM visa_position
WHERE visa_id=$visa_id";
$result = mysql_query($query,$db);
$myrow = mysql_fetch_array($result);
if($result) return $myrow[num_vacancies];
else return 0;
}// end of function get_num_vacancies()
/* The function get_count() get the equivalent number records in a table*/
function get_count($table,$field=1,$id=1,$and=""){
$db = db_connect();
if (!$db){
echo "Error connecting to database.</center>\n";
exit;
}
$query = "select count(*) from $table where $field='$id' $and";
$result = mysql_query($query,$db);
echo mysql_error();
$myrow = mysql_fetch_array($result);
return $myrow[0];
}// end of function getname
function check_full($table="",$max_num){
if(!$accounting){
if(getcount($table) >= $max_num)
error_message("You are only allowed to have a maximum of $max_num $table.");
}
}
function get_max_id()
{
$db = db_connect();
$sql = "select max(applicant_id) from personal ";
$result = mysql_query($sql, $db);
$row = mysql_fetch_array ($result);
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, 0, 4);
$this_year = date("Y");
if ($year != $this_year) $applicant_id = $this_year."000001";
else{ $applicant_id=$max_id + 1;}
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 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 principal_id FROM principals WHERE username='$username' AND password='$password' ";
$result = mysql_query($query,$db);
$myrow = mysql_fetch_array($result);
if (!$result) return 0;
elseif (mysql_num_rows($result)>0) return $myrow['principal_id'];
else return 0;
}
/* Function to check if the visitor is a valid IRIS user. */
function login_valid($myprincipalid1){
global $sqltoday;
$db = db_connect();
if (!$db){
echo "Can't connect to the MySQL database...";
exit;
}
$query = "SELECT principal_id FROM principals WHERE principal_id='$myprincipalid1' AND acc_date_from <= '$sqltoday' AND acc_date >= '$sqltoday'";
$result = mysql_query($query,$db);
$myrow = mysql_fetch_array($result);
if (!$result) return 0;
elseif (mysql_num_rows($result)>0) return $myrow['principal_id'];
else return 0;
}
function get_yrs_exp($applicant_id)
{
$db = db_connect();
if (!$db){
echo "Can't connect to the MySQL database...";
exit;
}
$employment = getdata("SELECT to_date,from_date
FROM employment
WHERE applicant_id = '$applicant_id'");
$num_emp = count($employment);
for($i=1;$i<=$num_emp;$i++){
if($employment[$i][from_date]!='0000-00-00' && $employment[$i][to_date]=='0000-00-00')
$employment[$i][to_date] = date(Y-m-d);
$yrs = ( strtotime ( $employment[$i][to_date] ) - strtotime ( $employment[$i][from_date] ) ) / (86400 * 365);
$exp_yrs = $exp_yrs + $yrs;
}
$exp_yrs = number_format($exp_yrs,1);
if($exp_yrs!=0.0) return $exp_yrs;
else return;
}
function get_rebate($clinic_id,$phase){
$db = db_connect();
$query = "SELECT phase$phase FROM clinics WHERE clinic_id=$clinic_id";
$result = mysql_query($query,$db);
$myrow = mysql_fetch_array($result);
if (!$result) return 0;
if (mysql_num_rows($result)>0) return $myrow[0];
else return 0;
}
function get_jo_principal($job_order_id){
// used in input_expense and input_income
$db = db_connect();
$query = "SELECT concat(principal_name,'-',job_order_no) as name from job_order as A,principals as B where A.principal_id=B.principal_id and job_order_id=$job_order_id";
$result = mysql_query($query,$db);
$myrow = mysql_fetch_array($result);
if (!$result) return "";
if (mysql_num_rows($result)>0) return $myrow[0];
else return "None";
}
?>
