ajgkqsldxvkqsldkqs
azelfkqsmkqsmlkdqsmld
azmlekdqmskdmlqskd
nkldsfkqsmldkqsmdmlskd
<?php
/* Database Connectivity */
function db_host(){
	return "localhost";
}
function db_username(){
	return "paat2018_db";
}
function db_password(){
	return "-+83F8X[tmy^";
}
function db_name(){
	return "paat2018_cdb";
}
function db_connect(){
	$con = @mysqli_connect(db_host(),db_username(),db_password(),db_name());
    return $con;
}
function db_close($con){
	mysqli_close ($con);
}
/* GENERAL FUNCTIONS */
function escape($con,$str){
	return mysqli_real_escape_string ($con ,$str);
}
function db_rows($con,$sql)
{
	$result = mysqli_query($con,$sql);
	$num_rows = mysqli_num_rows($result);
	return $num_rows;
}
function date_time($time){
	$atoms = explode(" ",$time);
	return format_date($atoms[0])." ".$atoms[1];
}
function login($con,$username,$password)
{
	return "select * from users where lower(username)=lower('".escape($con,$username)."') and password='".encryptPassword($password)."' and ac_type=1 and block=0";
}
function username_exist($con,$username,$userid){
	$sql =  "select * from users where lower(username)=lower('".escape($con,$username)."') and id !=".intval($userid);
	if(db_rows($con,$sql)>0){
		return true;
	}else{
		return false;
	}
}
function change_username($con,$nu,$userid){
	return "update users set username='".escape($con,$nu)."' where id=".intval($userid);
}
function ip_address(){
	$ipaddress = '';
    if (getenv('HTTP_CLIENT_IP'))
        $ipaddress = getenv('HTTP_CLIENT_IP');
    else if(getenv('HTTP_X_FORWARDED_FOR'))
        $ipaddress = getenv('HTTP_X_FORWARDED_FOR');
    else if(getenv('HTTP_X_FORWARDED'))
        $ipaddress = getenv('HTTP_X_FORWARDED');
    else if(getenv('HTTP_FORWARDED_FOR'))
        $ipaddress = getenv('HTTP_FORWARDED_FOR');
    else if(getenv('HTTP_FORWARDED'))
       $ipaddress = getenv('HTTP_FORWARDED');
    else if(getenv('REMOTE_ADDR'))
        $ipaddress = getenv('REMOTE_ADDR');
    else
        $ipaddress = 'UNKNOWN';
    return $ipaddress;
}
function generate_session($con){
	$ok = 0;
	while($ok==0){
		$token = generateToken();
		$sql = "select * from closeapp_sessions where token='".escape($con,$token)."'";
		if(db_rows($con,$sql)==0){
			$ok = 1;
		}
	}
	return $token;
}
function addUserSession($con,$userid,$session){
	return "insert into closeapp_sessions(userid,token,date) values
	(
	".intval($userid).",
	'".escape($con,$session)."',
	CURDATE()
	)
	";
}
function delete_user_login_sessions($id){
	return "delete from closeapp_sessions where userid=".intval($id);
}
function session($con,$session){
	return "select c.ac_type,c.name,c.username,c.picture,c.latitude,c.longitude,app.userid from users as c inner join closeapp_sessions as app on c.id=app.userid where app.token='".escape($con,$session)."'";
}
function is_blocked($con,$username,$password){
	$sql = "select * from users where username='".escape($con,$username)."' and password='".encryptPassword($password)."' and block=1";
	if(db_rows($con,$sql)>0){
		return true;
	}else{
		return false;
	}
}
function region_exist($con,$region){
	$sql = "select * from regions where code='".escape($con,$region)."'";
	if(db_rows($con,$sql)>0){
		return true;
	}else{
		return false;
	}
}
function town_exist($con,$region,$town){
	$sql = "select * from towns where region='".escape($con,$region)."' and code='".escape($con,$town)."'";
	if(db_rows($con,$sql)>0){
		return true;
	}else{
		return false;
	}
}
function town_name($con,$code){
	$town = "";
	$sql = "select * from towns where code='".escape($con,$code)."'";
	$query = mysqli_query($con,$sql);
	while($row=mysqli_fetch_array($query)){
		$town = $row['town'];
	}
	return $town;
}
function region_name($con,$code){
	$region = "";
	$sql = "select * from regions where code='".escape($con,$code)."'";
	$query = mysqli_query($con,$sql);
	while($row=mysqli_fetch_array($query)){
		$region = $row['name'];
	}
	return $region;
}
function category_exist($con,$code){
	$sql = "select * from businesses where code='".escape($con,$code)."'";
	if(db_rows($con,$sql)>0){
		return true;
	}else{
		return false;
	}
}
function category_name_by_code($con,$code){
	$name = "";
	$sql = "select * from businesses where code='".escape($con,$code)."'";
	$query = mysqli_query($con,$sql);
	while($row=mysqli_fetch_array($query)){
		$name = $row['name'];
	}
	return $name;
}
function service_exist($con,$category,$service){
	$sql = "select * from business_subcategories where category='".escape($con,$category)."' and code='".escape($con,$service)."'";
	if(db_rows($con,$sql)>0){
		return true;
	}else{
		return false;
	}
}
function service_recorded($con,$company,$category,$service){
	$sql = "select * from company_services where company=".intval($company)." and category='".escape($con,$category)."' and service='".escape($con,$service)."'";
	if(db_rows($con,$sql)>0){
		return true;
	}else{
		return false;
	}
}
function add_company_service($con,$mid,$company,$category,$service){
	$token = md5($userid.$company.$category.$service.time());
	return "insert into company_services(mid,usertype,company,category,service,token,date) values
	(
	".intval($mid).",
	2,
	".intval($company).",
	'".escape($con,$category)."',
	'".escape($con,$service)."',
	'".escape($con,$token)."',
	CURDATE()
	)
	";
}
function password_correct($con,$userid,$cp){
	$sql = "select * from users where password='".encryptPassword($cp)."' and id=".intval($userid);
	if(db_rows($con,$sql)>0){
		return true;
	}else{
		return false;
	}
}
function search_company($con,$query){
	return "select * from users where ac_type=1 and (name like '%".escape($con,$query)."%' or phone like '%".escape($con,$query)."%' or about like '%".escape($con,$query)."%') order by name asc limit 30";
}
function conversation_id($con,$from,$to){
	$conversation_id = "";
	$sql = "select conversation_id from messages where (fromid=".intval($from)." and toid=".intval($to).") or (fromid=".intval($to)." and toid=".intval($from).") order by id desc limit 1";
	$query = mysqli_query($con,$sql);
	while($row=mysqli_fetch_array($query)){
		$conversation_id = $row['conversation_id'];
	}
	if(empty($conversation_id)){
		$conversation_id = $from.'_'.$to;
	}
	return $conversation_id;
}
function message($con,$from,$to,$message,$attachments,$message_type,$sender_message_id){
	/* Get conversation id */
	$conversation_id = conversation_id($con,$from,$to);
	return "insert into messages(fromid,toid,message,attachments,message_type,status,sender_message_id,conversation_id,date) values(".intval($from).",".intval($to).",'".escape($con,$message)."','".escape($con,$attachments)."',".intval($message_type).",1,'".escape($con,$sender_message_id)."','".escape($con,$conversation_id)."',CURDATE())";
}
function user_push_tokens($userid){
	return "select u.ac_type,f.token from firebase_tokens as f inner join users as u on u.id=f.userid where f.userid=".intval($userid);
}
function user_other_push_tokens($con,$userid,$token){
	return "select u.ac_type,f.token from firebase_tokens as f inner join users as u on u.id=f.userid where f.userid=".intval($userid)." and f.token !='".escape($con,$token)."'";
}
function company_opening_hours($con,$company){
	$opening_hrs = "";
	$sql = "select * from companies_opening_hours where company=".intval($company)." order by day asc";
	$total = db_rows($con,$sql);
	if($total>0){
		$query = mysqli_query($con,$sql);
		$displays = 0;
		while($row=mysqli_fetch_array($query)){
			$day = $row['day'];
			$from = $row['starting_time'];
			$from_atoms = explode(":",$from);
			$from = $from_atoms[0].":".$from_atoms[1];
			$to = $row['closing_time'];
			$to_atoms = explode(":",$to);
			$to = $to_atoms[0].":".$to_atoms[1];
			$day_text = translate_day($day);
			if($day==1){
				$day_text = $day_text.add_emptyspace(10);
			}else if($day==2){
				$day_text = $day_text.add_emptyspace(9);
			}else if($day==3){
				$day_text = $day_text.add_emptyspace(3);
			}else if($day==4){
				$day_text = $day_text.add_emptyspace(7);
			}else if($day==5){
				$day_text = $day_text.add_emptyspace(13);
			}else if($day==6){
				$day_text = $day_text.add_emptyspace(8);
			}else if($day==7){
				$day_text = $day_text.add_emptyspace(11);
			}
			$cur_day = day_of_the_week();
			if(!($display==0)){
				$break = "<br>";
			}
			if($day==$cur_day){
				$opening_hrs .=$break.'<b>'.$day_text.'  '.$from.' - '.$to.'</b>
				';
			}else{
				$opening_hrs .=$break.$day_text.'  '.$from.' - '.$to.'</p>
				';
			}
			$display++;
		}
	}
	return $opening_hrs;
}
function update_profile($con,$userid,$about,$landmark,$direction,$phone,$alt_phone,$website){
	return "update users set about='".escape($con,sanitize($about))."',landmark='".escape($con,$landmark)."',direction='".escape($con,sanitize($direction))."',phone='".escape($con,$phone)."',alt_phone='".escape($con,$alt_phone)."',website='".escape($con,$website)."' where id=".intval($userid);
}
function record_opening_hrs($con,$company,$day,$from,$to){
	return "insert into companies_opening_hours(company,day,starting_time,closing_time,date) values(".intval($company).",".intval($day).",'".escape($con,$from)."','".escape($con,$to)."',CURDATE())";
}
function update_opening_hrs($con,$company,$day,$from,$to){
	return "update companies_opening_hours set mid=0,starting_time='".escape($con,$from)."',closing_time='".escape($con,$to)."' where day=".intval($day)." and company=".intval($company);
}
function opening_hrs_recorded($con,$company,$day){
	$sql = "select * from companies_opening_hours where company=".intval($company)." and day=".intval($day);
	if(db_rows($con,$sql)>0){
		return true;
	}else{
		return false;
	}
}
function wallet_balance($con,$userid){
	$balance = 0;
	$sql = "select sum(amount) as balance from wallet where userid=".intval($userid);
	$query = mysqli_query($con,$sql);
	while($row=mysqli_fetch_array($query)){
		$balance = $row['balance'];
	}
	if(empty($balance)){
		$balance = 0;
	}
	return $balance;
}
function record_offer($con,$userid,$title,$desc,$target,$image){
	$token = md5($userid.$title.$desc.$target.$image.time());
	return "insert into offers(userid,title,description,target,image,token,date) values
	(
	".intval($userid).",
	'".escape($con,$title)."',
	'".escape($con,sanitize($desc))."',
	".intval($target).",
	'".escape($con,$image)."',
	'".escape($con,$token)."',
	CURDATE()
	)
	";
}
function gen_transId($con){
	$ok = 0;
	while($ok==0){
		$trans_id = gen_digits(10);
		$sql = "select * from wallet where trans_id='".escape($con,$trans_id)."'";
		if(db_rows($con,$sql)==0){
			$ok = 1;
		}
	}
	return $trans_id;
}
function transaction($con,$userid,$trans_type,$gate_way,$gate_way_detail,$trans_id,$amount){
	$token = md5($userid.$trans_type.$gate_way.$trans_id.$amount.time());
	return "insert into wallet(userid,trans_type,gate_way,gate_way_detail,trans_id,amount,token,date) values
	(
	".intval($userid).",
	".intval($trans_type).",
	'".escape($con,$gate_way)."',
	'".escape($con,$gate_way_detail)."',
	'".escape($con,$trans_id)."',
	".escape($con,$amount).",
	'".escape($con,$token)."',
	CURDATE()
	)
	";
}
function record_payment($con,$userid,$vendor,$momo_name,$momo_phone,$kuulpay_phone,$kuulpay_token,$kuulpay_pin,$kuulpay_serial,$trans_id){
	$token = md5($userid.$vendor.$trans_id.time());
	return "insert into payments(userid,vendor,momo_name,momo_phone,kuulpay_phone,kuulpay_token,kuulpay_pin,kuulpay_serial,transaction_id,token,date) values
	(
	".intval($userid).",
	'".escape($con,$vendor)."',
	'".escape($con,$momo_name)."',
	'".escape($con,$momo_phone)."',
	'".escape($con,$kuulpay_phone)."',
	'".escape($con,$kuulpay_token)."',
	'".escape($con,$kuulpay_pin)."',
	'".escape($con,$kuulpay_serial)."',
	'".escape($con,$trans_id)."',
	'".escape($con,$token)."',
	CURDATE()
	)
	";
}
function gen_job_cat_code(){
	$ok = 0;
	while($ok==0){
		$code = gen_digits(4);
		$sql = "select * from  job_categories where code='".escape($code)."'";
		if(db_rows($con,$sql)==0){
			$ok = 1;
		}
	}
	return $code;
}
function job_cat_exist($con,$cat){
	$sql = "select * from job_categories where code='".escape($con,$cat)."'";
	if(db_rows($con,$sql)>0){
		return true;
	}else{
		return false;
	}
}
function add_job($con,$userid,$cat,$title,$desc,$req,$qual,$how_to,$date,$agent){
	$token = md5($userid.$cat.$title.$cat.$date.$agent.time());
	return "insert into jobs(userid,category,title,description,requirement,qualification,how_to_apply,deadline,agency,status,token,date) values
	(
	".intval($userid).",
	'".escape($con,$cat)."',
	'".escape($con,$title)."',
	'".escape($con,$desc)."',
	'".escape($con,$req)."',
	'".escape($con,$qual)."',
	'".escape($con,$how_to)."',
	'".escape($con,$date)."',
	".intval($agent).",
	1,
	'".escape($con,$token)."',
	CURDATE()
	)
	";
}
function job_category_name($con,$code){
	$sql = "select * from job_categories where code='".escape($con,$code)."'";
	$query = mysqli_query($con,$sql);
	while($row=mysqli_fetch_array($query)){
		$name = $row['name'];
	}
	return $name;
}
function update_last_time_firebase_token_used($con,$userid,$token){
	/* check if token has already been recorded so you just update last time used */
	$sql = "select * from firebase_token_usage where userid=".intval($userid)." and firebase_token='".escape($con,$token)."'";
	if(db_rows($con,$sql)>0){
		$sql = "update firebase_token_usage set last_time_used=now() where userid=".intval($userid)." and firebase_token='".escape($con,$token)."'";
		mysqli_query($con,$sql);
	}else{
		$sql = "insert into firebase_token_usage(userid,firebase_token,last_time_used,date) values(".intval($userid).",'".escape($con,$token)."',now(),CURDATE())";
		mysqli_query($con,$sql);
	}
}
function search_offers($con,$lat,$long,$q){
	return "SELECT o.userid,o.title,o.description,o.image,o.target,o.status,o.date,o.token as fid,u.ac_type,u.name,u.about,u.picture,u.region,u.town,u.phone,u.direction,u.website,u.alt_phone,u.landmark,u.latitude,u.longitude,u.token from offers as o inner join users as u on o.userid = u.id where o.status = 1 and o.userid in (select id from users having SQRT(
            POW(69.1 * (u.latitude - ".escape($con,$lat)."), 2) +
            POW(69.1 * (".escape($con,$long)." - u.longitude) * COS(u.latitude / 57.3), 2)) < ".nearby_boundary().") and (o.title like '%".escape($con,$q)."%' or o.description like '%".escape($con,$q)."%') ORDER BY o.id desc limit 30";
}
function subscription($con,$userid,$subscriber){
	$subscription = 0;
	if(!($userid==$id)){
		$sql = "select * from offer_subscriptions where userid=".intval($userid)." and subscriber=".intval($subscriber);
		if(db_rows($con,$sql)>0){
			$subscription = 1;
		}
	}
	return $subscription;
}
function nearby_offers($con,$lat,$long){
	return "SELECT o.userid,o.title,o.description,o.image,o.target,o.status,o.date,o.token as fid,u.ac_type,u.name,u.about,u.picture,u.region,u.town,u.phone,u.direction,u.ghana_post,u.website,u.alt_phone,u.landmark,u.latitude,u.longitude,u.token from offers as o inner join users as u on o.userid = u.id where o.status = 1 and o.userid in (select id from users having SQRT(
            POW(69.1 * (u.latitude - ".escape($con,$lat)."), 2) +
            POW(69.1 * (".escape($con,$long)." - u.longitude) * COS(u.latitude / 57.3), 2)) < ".nearby_boundary().") ORDER BY o.id desc";
}
function offers_in_country($con,$lat,$long){
	return "SELECT o.userid,o.title,o.description,o.image,o.target,o.status,o.date,o.token as fid,u.ac_type,u.name,u.about,u.picture,u.region,u.town,u.phone,u.direction,u.ghana_post,u.website,u.alt_phone,u.landmark,u.latitude,u.longitude,u.token from offers as o inner join users as u on o.userid = u.id where o.status = 1 and o.userid not in (select id from users having SQRT(
            POW(69.1 * (u.latitude - ".escape($con,$lat)."), 2) +
            POW(69.1 * (".escape($con,$long)." - u.longitude) * COS(u.latitude / 57.3), 2)) < ".nearby_boundary().") ORDER BY o.id desc";
}
function offers_in_country_including_nearby($con){
	return "SELECT o.userid,o.title,o.description,o.image,o.target,o.status,o.date,o.token as fid,u.ac_type,u.name,u.about,u.picture,u.region,u.town,u.phone,u.direction,u.ghana_post,u.website,u.alt_phone,u.landmark,u.latitude,u.longitude,u.token from offers as o inner join users as u on o.userid = u.id where o.status = 1 ORDER BY o.id desc limit 100";
}
function subscribed_offers($con,$userid){
	return "SELECT o.userid,o.title,o.description,o.image,o.target,o.status,o.date,o.token as fid,u.ac_type,u.name,u.about,u.picture,u.region,u.town,u.phone,u.direction,u.ghana_post,u.website,u.alt_phone,u.landmark,u.latitude,u.longitude,u.token from offers as o inner join users as u on o.userid = u.id where o.status = 1 and o.userid in (select userid from offer_subscriptions where subscriber=".intval($userid).") ORDER BY o.id desc";
}
function record_newsfeed($con,$userid,$title,$desc,$image){
	$token = md5($userid.$title.$desc.$image.time());
	return "insert into newsfeeds(userid,title,description,image,token,date) values
	(
	".intval($userid).",
	'".escape($con,sanitize($title))."',
	'".escape($con,sanitize($desc))."',
	'".escape($con,$image)."',
	'".escape($con,$token)."',
	CURDATE()
	)
	";
}
function subscribers($con,$userid){
	$sql = "select * from newsfeed_subscriptions where userid=".intval($userid);
	return db_rows($con,$sql);
}
function gen_advert_code($con){
	$ok = 0;
	while($ok==0){
		$code = gen_digits(8);
		$sql = "select * from ads where ad_id='".escape($con,$code)."'";
		if(db_rows($con,$sql)==0){
			$ok = 1;
		}
	}
	return $code;
}
function record_advert($con,$userid,$amount,$title,$desc,$website,$more,$target,$image){
	$code = gen_advert_code($con);
	$token = md5($userid.$title.$desc.$target.$image.$code.time());
	if(!empty($website)){
		$website = modifyUrl($website);
	}
	return "insert into ads(userid,ad_id,image,title,description,more,website,amount,balance,target,token,date) values
	(
	".intval($userid).",
	'".escape($con,$code)."',
	'".escape($con,$image)."',
	'".escape($con,$title)."',
	'".escape($con,sanitize($desc))."',
	'".escape($con,sanitize($more))."',
	'".escape($con,$website)."',
	".escape($con,$amount).",
	".escape($con,$amount).",
	".intval($target).",
	'".escape($con,$token)."',
	CURDATE()
	)
	";
}
function newsfeed_subscribed($con,$from,$userid){
	$sql = "select * from newsfeed_subscriptions where userid=".intval($from)." and subscriber=".intval($userid);
	if(db_rows($con,$sql)>0){
		return true;
	}else{
		return false;
	}
}
function member_offers($con,$userid){
	return "SELECT o.userid,o.title,o.description,o.image,o.target,o.status,o.date,o.token as fid,u.ac_type,u.name,u.about,u.picture,u.region,u.town,u.phone,u.direction,u.website,u.alt_phone,u.landmark,u.latitude,u.longitude,u.token from offers as o inner join users as u on o.userid = u.id where (o.status = 1 or o.status=3) and o.userid =".intval($userid)." ORDER BY o.id desc";
}
function job_candidates($con,$lat,$lon,$code){
	return "SELECT name,picture,phone,job_profile as profile,cv,id,SQRT(
            POW(69.1 * (latitude - ".$lat."), 2) +
            POW(69.1 * (".$lon." - longitude) * COS(latitude / 57.3), 2)) AS distance from users
			where id in(select userid from job_interests where code='".escape($con,$code)."') and ac_type=2 and job_profile !='' and cv !='' ORDER BY distance limit 500;";

}
/* Advert Functions */
function advert_activity($con,$userid,$type,$balance,$token){
	$sql = "select * from ad_activities where userid=".intval($userid)." and type=".intval($type)." and ad_id='".escape($con,$token)."' and date=CURDATE()";
	if(db_rows($con,$sql)==0){
		/* deduct money from balance */
		$cost = ad_activity_cost($type);
		$balance = $balance - $cost;
		$status = 1;
		if($balance<=0){
			$balance = 0;
			$status = 3;
		}
		/* update balance */
		$sql = "update ads set balance=".escape($con,$balance).",status=".intval($status)." where token='".escape($con,$token)."'";
		if(mysqli_query($con,$sql)){
			/* */
			/* Record ad activities */
			$sql = "insert into ad_activities(userid,type,amount,ad_id,ip_address,date) values(".intval($userid).",".intval($type).",".escape($con,$cost).",'".escape($con,$token)."','".escape($con,ip_address())."',CURDATE())";
			mysqli_query($con,$sql);
		}
	}
}
function ad_activity_cost($type){
	if($type==1){
		return 0.02;
	}else if($type==2){
		return 0.04;
	}
}
function latest_company($lat,$lon){
	return "SELECT name,about,picture,region,town,phone,direction,ghana_post,website,alt_phone,landmark,latitude,longitude,token,ac_type,id,SQRT(
            POW(69.1 * (latitude - ".$lat."), 2) +
            POW(69.1 * (".$lon." - longitude) * COS(latitude / 57.3), 2)) AS distance
			FROM users HAVING distance < ".nearby_boundary()." and ac_type=1 order by id desc limit 1";
			
}
function request_exist($con,$phone){
	$sql = "select * from signup_requests where phone='".escape($con,db_phone_format($phone))."'";
	if(db_rows($con,$sql)>0){
		return true;
	}else{
		return false;
	}
}
function request_signup($con,$name,$location,$direction,$phone){
	$token = md5($name.$location.$direction.$phone.time());
	return "insert into signup_requests(name,location,direction,phone,source,token,date) values
	(
	'".escape($con,$name)."',
	'".escape($con,$location)."',
	'".escape($con,$direction)."',
	'".escape($con,db_phone_format($phone))."',
	1,
	'".escape($con,$token)."',
	CURDATE()
	)
	";
}
?>