<?php
//get total tasks done by technician
function getTotTaskCountTech($techId) {
	$con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
	mysqli_select_db($con, DB_NAME) or die(mysql_error());
	//as main tech	
	$mainTaskCount = $extraTaskCount = 0;
	$rs1 = mysqli_query($con, "SELECT COUNT(id) AS count FROM tbl_job WHERE technician_id = '".$techId."'");	
	if(mysqli_num_rows($rs1)>0){
		$rec = mysqli_fetch_array($rs1);
		$mainTaskCount = $rec['count'];
	}
	$rs2 = mysqli_query($con, "SELECT COUNT(id) AS count FROM tbl_job_extra_tech WHERE technician_id = '".$techId."'");	
	if(mysqli_num_rows($rs2)>0){
		$rec = mysqli_fetch_array($rs2);
		$extraTaskCount = $rec['count'];
	}
	return $mainTaskCount+$extraTaskCount;
}

//get total admin commission from a task
function getTotTaskAdminCommission($taskId) {
	$con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
	mysqli_select_db($con, DB_NAME) or die(mysql_error());	
	$rs = mysqli_query($con, "SELECT SUM(admin_commission) AS commission FROM tbl_task_completed WHERE task_id = '".$taskId."'");	
	if(mysqli_num_rows($rs)>0){
		$rec = mysqli_fetch_array($rs);
		if($rec['commission']>0){
			return $rec['commission'];
		} else {
			return 0.00;
		}
	} else {
		return 0.00;
	}		
}

function generateRandom($length = 6) {
	$con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
	mysqli_select_db($con, DB_NAME) or die(mysql_error());	
    $characters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    $charactersLength = strlen($characters);
    $randomString = '';
    for ($i = 0; $i < $length; $i++) {
        $randomString .= $characters[rand(0, $charactersLength - 1)];
    }
	//check if exist
	$userRs = mysqli_query($con, "SELECT id FROM tbl_user WHERE ref_val = '".$randomString."'");
	if(mysqli_num_rows($userRs)>0){
		$this->generateRandom(6);
	} else {
		return $randomString;
	}    
}

//returs the text to display by receiving db status as param -  for task status
function getStatusText($status) {
	$statArray = array('assigned'=>'Assigned', 'accepted'=>'Tech. accepted', 'start_off'=>'Tech. started', 'arrived'=>'Tech. reached',
	'start_job'=>'Started Job', 'completed'=>'Task completed', 'user_paid'=>'Pending payment', 'paid'=>'Payment completed', 'cancelled'=>'Cancelled');
	if(array_key_exists($status, $statArray)){
		return $statArray[$status];
	} else {
		return $status;
	}
}

function isOperator($techId) {
	$con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
	mysqli_select_db($con, DB_NAME) or die(mysql_error());	
	$rec = mysqli_fetch_array(mysqli_query($con, "SELECT operator FROM `tbl_technician` WHERE id = '".$techId."'"));
	if($rec['operator']=='yes'){
		return TRUE;
	} else {
		return FALSE;
	}
}

function isPaidTech($techId) {
	$con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
	mysqli_select_db($con, DB_NAME) or die(mysql_error());	
	$rec = mysqli_fetch_array(mysqli_query($con, "SELECT is_paid FROM `tbl_technician` WHERE id = '".$techId."'"));
	if($rec['is_paid']=='yes'){
		return TRUE;
	} else {
		return FALSE;
	}
}

//send sms Promo
function sendSMSP($phone, $smsMessage){
	//$smsMessage = urlencode($smsMessage);
	$smsRes = file_get_contents('http://roundsms.com/api/sendhttp.php?authkey=Nzk3YWJkY2VmMWU&mobiles='.$phone.'&message='.$smsMessage.'&sender=OndemandproP&type=1&route=3');
	$smsRes = json_decode($smsRes); 
	//print_r($smsRes);
	if($smsRes->error!= 'null' && $smsRes->error!=''){
		$error = $smsRes->error;
		return FALSE;
		if($error!=''){
			
		}		
	} else {
		return TRUE;
	}
}

//send sms T
function sendSMST($phone, $smsMessage){
	//$smsMessage = urlencode($smsMessage);
	$smsRes = file_get_contents('http://roundsms.com/api/sendhttp.php?authkey=Nzk3YWJkY2VmMWU&mobiles='.$phone.'&message='.$smsMessage.'&sender=Ondemandpro&type=1&route=2');
	$smsRes = json_decode($smsRes); 
	//print_r($smsRes);
	if($smsRes->error!= 'null' && $smsRes->error!=''){
		$error = $smsRes->error;
		return FALSE;
		if($error!=''){
			
		}		
	} else {
		return TRUE;
	}
} 

//get the total balance pending in tech hand
function getBalanceTech($techId) {
	$con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
	mysqli_select_db($con, DB_NAME) or die(mysql_error());	
	$techRec = mysqli_fetch_array(mysqli_query($con, "SELECT type FROM tbl_technician WHERE id = '".$techId."'"));
	$techType = $techRec['type'];
	$totalAmountPending = 0;	
	if($techType=='own'){
		$incomesRec = mysqli_fetch_array(mysqli_query($con, "SELECT SUM(task_amount) AS amount FROM `tbl_technician_payments` WHERE `technician_id` = '".$techId."' AND payment_method = 'Cash' AND status = 'paid'")); 
		$receivedRec = mysqli_fetch_array(mysqli_query($con, "SELECT SUM(amount) AS amount FROM `tbl_admin_tech_incomes` WHERE `technician_id` = '".$techId."'"));
		$cachImcome = $incomesRec['amount'];
		$totalAmountPending = $cachImcome - $receivedRec['amount'];
	} else {
		//the tech should pay the admin fee to Ondemandpro
		$incomesCashRec = mysqli_fetch_array(mysqli_query($con, "SELECT SUM(task_amount) AS amount FROM `tbl_technician_payments` WHERE `technician_id` = '".$techId."' AND payment_method = 'Cash' AND status = 'paid'"));
		$incomesOnlineRec = mysqli_fetch_array(mysqli_query($con, "SELECT SUM(task_amount) AS amount FROM `tbl_technician_payments` WHERE `technician_id` = '".$techId."' AND payment_method != 'Cash' AND status = 'paid'")); 
		$receivedRec = mysqli_fetch_array(mysqli_query($con, "SELECT SUM(amount) AS amount FROM `tbl_admin_tech_incomes` WHERE `technician_id` = '".$techId."'"));
		
		$techCommissionRec = mysqli_fetch_array(mysqli_query($con, "SELECT SUM(user_amount) AS amount FROM `tbl_technician_payments` WHERE `technician_id` = '".$techId."' AND status = 'paid'"));
		$cachIncome = $incomesCashRec['amount']; //in techs hand
		$onlineIncome = $incomesOnlineRec['amount']; //total from all tasks		
		$totalTechCommission = $techCommissionRec['amount'];
			
		$totalAmountLiiMR = ($onlineIncome+$receivedRec['amount']); //in liimr now
		$totalTechHand = $cachIncome-$receivedRec['amount']; //in tech hand now
		//if($totalTechHand>$totalTechCommission){
			$totalAmountPending = $totalTechHand-$totalTechCommission;
		/*} else {
			$totalAmountPending = $totalTechCommission-$totalTechHand;			
		}*/	
	}
	//total operator commission
	$operatorRec = mysqli_fetch_array(mysqli_query($con, "SELECT SUM(operator_commission) AS amount FROM `tbl_technician_payments` WHERE `technician_id` = '".$techId."' AND status = 'paid'")); 
	$totalOperatorCommission = $operatorRec['amount'];
	$totalAmountPending = $totalAmountPending-$totalOperatorCommission;
	return $totalAmountPending;	
}

function getReferrerCount($userId) {
	$con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
	mysqli_select_db($con, DB_NAME) or die(mysql_error());		
	$rec = mysqli_fetch_array(mysqli_query($con, "SELECT count(id) AS count FROM `tbl_user` WHERE `ref_id` = '".$userId."'"));
	return $rec['count'];	
}


function getAdminSlot($taskId){
	$con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
	mysqli_select_db($con, DB_NAME) or die(mysql_error());	
	$taskRec = mysqli_fetch_array(mysqli_query($con, "SELECT admin_slot, admin_slot_to FROM tbl_job WHERE id = '".$taskId."'"));
	if($taskRec['admin_slot']!='00:00:00' && $taskRec['admin_slot_to']!='00:00:00'){
		$slotDb = $taskRec['admin_slot'];	
		$slotDbTo = $taskRec['admin_slot_to'];			
		$slotString = date("g:i a", strtotime($slotDb)).' to '.date("g:i a", strtotime($slotDbTo));
		return $slotString;		
	} else {
		$slotString = 'No slot allotted';
		return $slotString;
	}		
}

function getTechVisitCharge($techId, $catId='') {
	$con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
	mysqli_select_db($con, DB_NAME) or die(mysql_error());
	if($techId !='') {		
		//get tech type. For own taske rate from settings. For loop take from tech_rates
		$dispatchType = getTechDispatchType($techId);
		if($dispatchType=='admin'){
			//check in category settings
			$rec = mysqli_fetch_array(mysqli_query($con, "SELECT visit_charge FROM tbl_service_categories WHERE id = '".$catId."'"));
			if($rec['visit_charge']>0){
				return $rec['visit_charge'];
			} else {
				$rec = mysqli_fetch_array(mysqli_query($con, "SELECT visit_charge FROM tbl_settings WHERE id = '1'"));
				return $rec['visit_charge'];
			}			
		} else {
			$rs = mysqli_query($con, "SELECT visit_charge FROM tbl_technician_rates WHERE technician_id = '".$techId."'");
			if(mysqli_num_rows($rs)>0){
				$rec = mysqli_fetch_array($rs);
				return $rec['visit_charge'];		
			} else {
				$rec = mysqli_fetch_array(mysqli_query($con, "SELECT visit_charge FROM tbl_service_categories WHERE id = '".$catId."'"));
				if($rec['visit_charge']>0){
					return $rec['visit_charge'];
				} else {
					$rec = mysqli_fetch_array(mysqli_query($con, "SELECT visit_charge FROM tbl_settings WHERE id = '1'"));
					return $rec['visit_charge'];
				}
			}
		}			
	}
}
 
/*function getTechMinimumCharge($techId, $catId) {
	$con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
	mysqli_select_db($con, DB_NAME) or die(mysql_error());
	if($techId !='') {
		//get tech type. For own taske rate from settings. For loop take from tech_rates
		$techRec = mysqli_fetch_array(mysqli_query($con, "SELECT type FROM tbl_technician WHERE id = '".$techId."'"));
		if($techRec['type']=='own'){
			//check in category settings
			$rec = mysqli_fetch_array(mysqli_query($con, "SELECT min_charge FROM tbl_service_categories WHERE id = '".$catId."'"));
			if($rec['min_charge']>0){
				return $rec['min_charge'];
			} else {
				$rec = mysqli_fetch_array(mysqli_query($con, "SELECT min_charge FROM tbl_settings WHERE id = '1'"));
				return $rec['min_charge'];
			}			
		} else {
			$rs = mysqli_query($con, "SELECT min_charge FROM tbl_technician_rates WHERE technician_id = '".$techId."'");
			if(mysqli_num_rows($rs)>0){
				$rec = mysqli_fetch_array($rs);
				return $rec['min_charge'];		
			} else {
				
			}
		}
			
	}
}*/

function getTechMinimumCharge($techId, $catId) {
	$con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
	mysqli_select_db($con, DB_NAME) or die(mysql_error());
	if($techId !='') {
		//get tech type. For own taske rate from settings. For loop take from tech_rates
		$dispatchType = getTechDispatchType($techId);
		//$techRec = mysqli_fetch_array(mysqli_query($con, "SELECT type FROM tbl_technician WHERE id = '".$techId."'"));
		if($dispatchType=='admin'){
			//check in category settings
			$rec = mysqli_fetch_array(mysqli_query($con, "SELECT min_charge FROM tbl_service_categories WHERE id = '".$catId."'"));
			if($rec['min_charge']>0){
				return $rec['min_charge'];
			} else {
				$rec = mysqli_fetch_array(mysqli_query($con, "SELECT min_charge FROM tbl_settings WHERE id = '1'"));
				return $rec['min_charge'];
			}			
		} else {
			$rs = mysqli_query($con, "SELECT min_charge FROM tbl_technician_rates WHERE technician_id = '".$techId."'");
			if(mysqli_num_rows($rs)>0){
				$rec = mysqli_fetch_array($rs);
				return $rec['min_charge'];		
			} else {
				$rec = mysqli_fetch_array(mysqli_query($con, "SELECT min_charge FROM tbl_service_categories WHERE id = '".$catId."'"));
				if($rec['min_charge']>0){
					return $rec['min_charge'];
				} else {
					$rec = mysqli_fetch_array(mysqli_query($con, "SELECT min_charge FROM tbl_settings WHERE id = '1'"));
					return $rec['min_charge'];
				}
			}
		}
			
	}
}

function getTechHourlyRate($techId, $catId='') {
	$con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
	mysqli_select_db($con, DB_NAME) or die(mysql_error());
	if($techId !='') {
		//get tech type. For own taske rate from settings. For loop take from tech_rates
		$dispatchType = getTechDispatchType($techId);
		if($dispatchType=='admin'){
			//check in category settings
			$rec = mysqli_fetch_array(mysqli_query($con, "SELECT hourly_rate FROM tbl_service_categories WHERE id = '".$catId."'"));
			if($rec['hourly_rate']>0){
				return $rec['hourly_rate'];
			} else {
				$rec = mysqli_fetch_array(mysqli_query($con, "SELECT hourly_rate FROM tbl_settings WHERE id = '1'"));
				return $rec['hourly_rate'];
			}			
		} else {
			$rs = mysqli_query($con, "SELECT hourly_rate FROM tbl_technician_rates WHERE technician_id = '".$techId."'");
			if(mysqli_num_rows($rs)>0){
				$rec = mysqli_fetch_array($rs);
				return $rec['hourly_rate'];		
			} else {
				$rec = mysqli_fetch_array(mysqli_query($con, "SELECT hourly_rate FROM tbl_service_categories WHERE id = '".$catId."'"));
				if($rec['hourly_rate']>0){
					return $rec['hourly_rate'];
				} else {
					$rec = mysqli_fetch_array(mysqli_query($con, "SELECT hourly_rate FROM tbl_settings WHERE id = '1'"));
					return $rec['hourly_rate'];
				}
			}
		}	
	}
}

function getExpenseCategoryName($id) {
	$con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
	mysqli_select_db($con, DB_NAME) or die(mysql_error());
	if($id !='') {
		$res = mysqli_fetch_array(mysqli_query($con, "SELECT name FROM tbl_expense_category WHERE id = '".$id."'"));
		return $res['name'];		
	}
}

function getIncomeCategoryName($id) {
	$con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
	mysqli_select_db($con, DB_NAME) or die(mysql_error());
	if($id !='') {
		$res = mysqli_fetch_array(mysqli_query($con, "SELECT name FROM tbl_income_category WHERE id = '".$id."'"));
		return $res['name'];		
	}
}

function getTechType($techId) {
	$con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
	mysqli_select_db($con, DB_NAME) or die(mysql_error());		
	$rec = mysqli_fetch_array(mysqli_query($con, "SELECT type FROM `tbl_technician` WHERE id = '".$techId."'"));
	if($rec['type']!=''){
		return $rec['type'];
	} else {
		return 'own';
	}	
}

function getTechDispatchType($techId) {
	$con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
	mysqli_select_db($con, DB_NAME) or die(mysql_error());		
	$rec = mysqli_fetch_array(mysqli_query($con, "SELECT city FROM `tbl_technician` WHERE id = '".$techId."'"));
	$techCity = $rec['city'];
	if($techCity>0){
		$cityRs = mysqli_query($con, "SELECT dispatch_type FROM tbl_city WHERE id = '".$techCity."'");
		if(mysqli_num_rows($cityRs)>0){
			$cityRec = mysqli_fetch_array($cityRs);
			return $cityRec['dispatch_type'];
		} else {
			return 'direct';
		}
	} else {
		return 'direct';
	}
}

function getTechRating($techId) {
	$con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
	mysqli_select_db($con, DB_NAME) or die(mysql_error());
	$revValue = 0;
	$rec = mysqli_fetch_array(mysqli_query($con, "SELECT count(id) AS count FROM `tbl_review` WHERE `technician_id` = '".$techId."' AND `rating` > 0"));
	$totRevCount = $rec['count'];
	$recSum = mysqli_fetch_array(mysqli_query($con, "SELECT SUM(rating) AS rating FROM `tbl_review` WHERE `technician_id` = '".$techId."'"));
	$totRevValue = $recSum['rating'];
	if($totRevCount>0){
		$revValue = $totRevValue/$totRevCount;
		$revValue = number_format((float)$revValue, 1, '.', '');		
	} 
	return $revValue;
}


function getProjectType($typeid) {
	$con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
	mysqli_select_db($con, DB_NAME) or die(mysql_error());
	if($typeid !='' && $typeid > 0) {
		$res = mysqli_fetch_array(mysqli_query($con, "SELECT name FROM tbl_task_types WHERE id = '".$typeid."'"));
		return $res['name'];		
	}
}

function getProjectName($id) {
	$con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
	mysqli_select_db($con, DB_NAME) or die(mysql_error());
	if($id !='' && $id > 0) {
		$res = mysqli_fetch_array(mysqli_query($con, "SELECT name FROM tbl_project WHERE id = '".$id."'"));
		return $res['name'];		
	}
}
//$address is comma seperated address string
function getLatLong($address){
    if(!empty($address)){
        //Formatted address
        $formattedAddr = str_replace(' ','+',$address);
        //Send request and receive json data by address
        //$geocodeFromAddr = file_get_contents('http://maps.googleapis.com/maps/api/geocode/json?address='.$formattedAddr.'&sensor=false');
		/***********Faster response with api key*************/
        $geocodeFromAddr = file_get_contents('https://maps.googleapis.com/maps/api/geocode/json?key=AIzaSyCHQ9iQLKaZF0_rH_tIrB6Tnm1C2FPr3XU&address='.$formattedAddr.'&sensor=false');
		$output = json_decode($geocodeFromAddr);
		$data = array();
        //Get latitude and longitute from json data
        if(isset($output->results[0]->geometry->location->lat) && $output->results[0]->geometry->location->lat!=''){
        	$data['latitude']  = $output->results[0]->geometry->location->lat; 
        	$data['longitude'] = $output->results[0]->geometry->location->lng;
        	//Return latitude and longitude of the given address
	        if(!empty($data)){
	            return $data;
	        }else{
	            return false;
	        }
        } else {
        	return false;   
        } 
    } else {
        return false;   
    }
}


function getCityName($id) {
	$con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
	mysqli_select_db($con, DB_NAME) or die(mysql_error());
	if($id !='') {
		$res = mysqli_fetch_array(mysqli_query($con, "SELECT name FROM tbl_city WHERE id = '".$id."'"));
		return $res['name'];		
	}
}

function getTechnicianLocation($id) {
	$con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
	mysqli_select_db($con, DB_NAME) or die(mysql_error());
	if($id !='') {
		$res = mysqli_fetch_array(mysqli_query($con, "SELECT latitude, longitude FROM tbl_technician WHERE id = '".$id."'"));
		return $res['latitude'].','.$res['longitude'];		
	}
}


function getUserLocation($userId) {
	$con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
	mysqli_select_db($con, DB_NAME) or die(mysql_error());
	if($id !='') {
		$res = mysqli_fetch_array(mysqli_query($con, "SELECT latitude, longitude FROM tbl_user WHERE id = '".$userId."'"));
		return $res['latitude'].','.$res['longitude'];
	}
}

function getUserBalance($userId) {
	$con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
	mysqli_select_db($con, DB_NAME) or die(mysql_error());
	if($id !='') {
		$res = mysqli_fetch_array(mysqli_query($con, "SELECT balance FROM tbl_user WHERE id = '".$userId."'"));
		return $res['balance'];
	}
}
	
function getStaffSalary($id) {
	$con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
	mysqli_select_db($con, DB_NAME) or die(mysql_error());
	if($id !='') {
		$res = mysqli_fetch_array(mysqli_query($con, "SELECT salary FROM tbl_staff WHERE id = '".$id."'"));
		return $res['salary'];
	}
}

/* get formated date return */
function getFormattedDateRet($datetime) {
$phpDate = strtotime($datetime);
$FormattedPhpDate = date('d-m-Y', $phpDate);
return $FormattedPhpDate;
}

/* get formated date */
function getFormattedDate($datetime) {
$phpDate = strtotime($datetime);
$FormattedPhpDate = date('d-m-Y', $phpDate);
echo $FormattedPhpDate;
}

/* get formated date and time */
function getFormattedDateTime($datetime) {
$dateTimeArr = explode(' ', $datetime);
$FormattedDateArr = explode('-', $dateTimeArr[0]);
$FormattedTimeArr = explode(':', $dateTimeArr[1]);
$FormattedDate = $FormattedDateArr[2].'-'.$FormattedDateArr[1].'-'.$FormattedDateArr[0];
$FormattedTime = $FormattedTimeArr[0].':'.$FormattedTimeArr[1];
echo $FormattedDate.' '.$FormattedTime;
}

function getFormattedDateTimeRet($datetime) {
$dateTimeArr = explode(' ', $datetime);
$FormattedDateArr = explode('-', $dateTimeArr[0]);
$FormattedTimeArr = explode(':', $dateTimeArr[1]);
$FormattedDate = $FormattedDateArr[2].'-'.$FormattedDateArr[1].'-'.$FormattedDateArr[0];
$FormattedTime = $FormattedTimeArr[0].':'.$FormattedTimeArr[1];
return $FormattedDate.' '.$FormattedTime;
}

function getFormattedTime($time) {
$timeArr = explode(':', $time);
$FormattedTime = $timeArr[0].':'.$timeArr[1];
return $FormattedTime;
}

function getParentCategory($id) {
	$con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
	mysqli_select_db($con, DB_NAME) or die(mysql_error());
	if($id !='') {
		$res = mysqli_fetch_array(mysqli_query($con, 'SELECT parent_id FROM tbl_service_categories WHERE id ='.$id));
		$parentId = $res['parent_id'];
		if($parentId!='0'){
			$parentRs = mysqli_query($con, 'SELECT name FROM tbl_service_categories WHERE id ='.$parentId);
			if(mysqli_num_rows($parentRs)>0){
				$parentRec = mysqli_fetch_array($parentRs);
				return $parentRec['name'];
			} else {
				return 'Deleted';
			}			
		} else {
			return 'NA';
		}
	}
}

function getCategory($id) {
	$con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
	mysqli_select_db($con, DB_NAME) or die(mysql_error());
	if($id !='') {
		$res = mysqli_fetch_array(mysqli_query($con, 'SELECT name FROM tbl_service_categories WHERE id ='.$id));
		return $res['name'];
	}
}

function getProductCategory($id) {
	$con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
	mysqli_select_db($con, DB_NAME) or die(mysql_error());
	if($id !='') {
		$res = mysqli_fetch_array(mysqli_query($con, 'SELECT name FROM tbl_product_categories WHERE id ='.$id));
		return $res['name'];
	}
}

function getTechnicianName($id) {
	$con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
	mysqli_select_db($con, DB_NAME) or die(mysql_error());
	if($id !='') {
		$res = mysqli_fetch_array(mysqli_query($con, "SELECT name FROM tbl_technician WHERE id = '$id'"));
		return $res['name'];
	}
}

function getStaffName($id) {
	$con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
	mysqli_select_db($con, DB_NAME) or die(mysql_error());
	if($id !='') {
		$res = mysqli_fetch_array(mysqli_query($con, "SELECT name FROM tbl_staff WHERE id = '$id'"));
		return $res['name'];
	}
}



function getUserName($id) {
	$con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
	mysqli_select_db($con, DB_NAME) or die(mysql_error());
	if($id !='') {
		$res = mysqli_fetch_array(mysqli_query($con, "SELECT name FROM tbl_user WHERE id = '$id'"));
		$name = $res['name'];
		$name = (strlen($name) > 25) ? substr($name,0,23).'..' : $name;
		return $name;
	}
}

function getPartnerName($id) {
	$con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
	mysqli_select_db($con, DB_NAME) or die(mysql_error());
	if($id !='') {
		$res = mysqli_fetch_array(mysqli_query($con, "SELECT name FROM tbl_purchase_partner WHERE id = '$id'"));
		return $res['name'];
	}
}
function getShopName($id) {
	$con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
	mysqli_select_db($con, DB_NAME) or die(mysql_error());
	if($id !='') {
		$res = mysqli_fetch_array(mysqli_query($con, "SELECT building_name FROM tbl_purchase_partner WHERE id = '$id'"));
		return $res['building_name'];
	}
}
function getTaskName($id) {
	$con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
	mysqli_select_db($con, DB_NAME) or die(mysql_error());
	if($id !='') {
		$res = mysqli_fetch_array(mysqli_query($con, "SELECT name FROM tbl_job WHERE id = '$id'"));
		return $res['name'];
	}
}
function getProductName($id) {
	$con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
	mysqli_select_db($con, DB_NAME) or die(mysql_error());
	if($id !='') {
		$res = mysqli_fetch_array(mysqli_query($con, "SELECT name FROM tbl_products WHERE id = '$id'"));
		return $res['name'];
	}
}


function getAdminUserName($id) {
	$con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
	mysqli_select_db($con, DB_NAME) or die(mysql_error());
	if($id !='') {
		$res = mysqli_fetch_array(mysqli_query($con, "SELECT ad_username FROM tbl_admin WHERE admin_id = '$id'"));
		return $res['ad_username'];
	}
}

function getStaffDeductions($staffId, $year, $month) {
	$con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
	mysqli_select_db($con, DB_NAME) or die(mysql_error());
	$error = '';
	$netAmount = $basicSalary = $advanceAmount = $advanceReturnAmount = $leaveCasual = $leaveSick = 0;
	if(($staffId!='')){
		$staffRs = mysqli_query($con, "SELECT name, salary FROM tbl_staff WHERE id = '".$staffId."'");
		if(mysqli_num_rows($staffRs)>0){
			$resStaff = mysqli_fetch_array($staffRs);
			$basicSalary = $resStaff['salary']; 
			$netAmount = $basicSalary;			
		} else {
			$error = 'Staff does not exist!';
		}
		
		//get advance amount given to staff if any
		$advanceRs = mysqli_query($con, "SELECT amount, advance_return FROM tbl_staff_advance WHERE staff_id = '".$staffId."'");
		if(mysqli_num_rows($advanceRs)>0){
			while($advanceRec = mysqli_fetch_array($advanceRs)){
				$advanceAmount += $advanceRec['amount'];
				$advanceReturnAmount += $advanceRec['advance_return'];
			}
			$advanceAmount = $advanceAmount - $advanceReturnAmount;
			$netAmount = $basicSalary-$advanceAmount;
		}
		//get total leaves
		if(($year!='') && ($month!='')){			
			$leaveRs = mysqli_query($con, "SELECT * FROM tbl_staff_leaves WHERE staff_id = '".$staffId."' AND YEAR(date) = '".$year."' AND MONTH(date) = '".$month."'");
			if(mysqli_num_rows($leaveRs)>0){
				while($leaveRec = mysqli_fetch_array($leaveRs)){
					if($leaveRec['leave_type'] == 'casual'){
						if($leaveRec['status'] == 'full_day'){
							$leaveCasual++;
						} else if($leaveRec['status'] == 'half_day'){
							$leaveCasual = $leaveCasual+0.5;
						}						
					} else if($leaveRec['leave_type'] == 'sick'){
						if($leaveRec['status'] == 'full_day'){
							$leaveSick++;
						} else if($leaveRec['status'] == 'half_day'){
							$leaveSick = $leaveSick+0.5;
						}
					}					
				}
			} 
		}
		$totLeaves = $leaveCasual+$leaveSick;
				
		//get settings for leaves and calculate deduction
		$settingsRes = mysqli_fetch_array(mysqli_query($con, "SELECT casual_leave_count, allow_advance_leave, sick_leave_count, allow_advance_sick_leave, working_days FROM tbl_settings WHERE id = '1'"));
		$casualLeaveCount = $settingsRes['casual_leave_count'];
		$allowAdvanceLeave = $settingsRes['allow_advance_leave'];
		$sickLeaveCount = $settingsRes['sick_leave_count'];
		$allowAdvanceSickLeave = $settingsRes['allow_advance_sick_leave'];
		$workingDays = $settingsRes['working_days'];
		$casualLeavePerMonth = $casualLeaveCount/12;
		$sickLeavePerMonth = $sickLeaveCount/12;
		//the total leave count in a year will vary if the staff joined on the year after some months or like that
		//so we have to calculate the total leaves from the joining month
		//get joining year,month
		$staffRec = mysqli_fetch_array(mysqli_query($con, "SELECT YEAR(date) AS year, MONTH(date) AS month FROM tbl_staff WHERE id = '".$staffId."'"));
		$joinYear = $staffRec['year'];
		$joinMonth = $staffRec['month'];
		if($joinYear==$year){
			$monthBalance = (12-$joinMonth)+1;
			$casualLeaveCount = $casualLeaveCount/$monthBalance;
			$sickLeaveCount = $sickLeaveCount/$monthBalance;
		}		
		//find total casual leaves taken by the staff in current year, month
		$leaveCasualYear = $leaveSickYear = 0;
		$leaveCasualMonth = $leaveSickMonth = 0;
		$leaveRs = mysqli_query($con, "SELECT * FROM tbl_staff_leaves WHERE staff_id = '".$staffId."' AND YEAR(date) = '".$year."'");
		if(mysqli_num_rows($leaveRs)>0){
				while($leaveRec = mysqli_fetch_array($leaveRs)){
					if($leaveRec['leave_type'] == 'casual'){
						if($leaveRec['status'] == 'full_day'){
							$leaveCasualYear++;
						} else if($leaveRec['status'] == 'half_day'){
							$leaveCasualYear = $leaveCasualYear+0.5;
						}						
					} else if($leaveRec['leave_type'] == 'sick'){
						if($leaveRec['status'] == 'full_day'){
							$leaveSickYear++;
						} else if($leaveRec['status'] == 'half_day'){
							$leaveSickYear = $leaveSickYear+0.5;
						}
					}					
				}
		}
		//calculate total casual leaves taken for the month
		if($allowAdvanceLeave == 'Yes'){
			$extraCausalLeaves = $leaveCasualYear - $casualLeaveCount;
		} else {
			//total casual leaves taken on this month
			$leaveCasualMonth = 0;
			$leaveRs = mysqli_query($con, "SELECT * FROM tbl_staff_leaves WHERE staff_id = '".$staffId."' AND YEAR(date) = '".$year."' AND MONTH(date) = '".$month."'");
			if(mysqli_num_rows($leaveRs)>0){
					while($leaveRec = mysqli_fetch_array($leaveRs)){
						if($leaveRec['leave_type'] == 'casual'){
							if($leaveRec['status'] == 'full_day'){
								$leaveCasualMonth++;
							} else if($leaveRec['status'] == 'half_day'){
								$leaveCasualMonth = $leaveCasualMonth+0.5;
							}						
						} 					
					}
			}		
			if($leaveCasualMonth>0){
				$extraCausalLeaves = $leaveCasualMonth - $casualLeavePerMonth;
			} else {
				$extraCausalLeaves = 0;
			}
			
			//$extraCausalLeaves = 2-1;
		}
		//calculate total sick leaves taken for the month
		if($allowAdvanceSickLeave == 'Yes'){
			$extraSickLeaves = $leaveSickYear - $sickLeaveCount;			
		} else {
			//total casual leaves taken on this month
			$leaveSickMonth = 0;
			$leaveRs = mysqli_query($con, "SELECT * FROM tbl_staff_leaves WHERE staff_id = '".$staffId."' AND YEAR(date) = '".$year."' AND MONTH(date) = '".$month."'");
			if(mysqli_num_rows($leaveRs)>0){
					while($leaveRec = mysqli_fetch_array($leaveRs)){
						if($leaveRec['leave_type'] == 'sick'){
							if($leaveRec['status'] == 'full_day'){
								$leaveSickMonth++;
							} else if($leaveRec['status'] == 'half_day'){
								$leaveSickMonth = $leaveSickMonth+0.5;
							}						
						} 					
					}
			}	
			if($leaveSickMonth>0){
				$extraSickLeaves = $leaveSickMonth - $sickLeavePerMonth;
			} else {
				$extraSickLeaves = 0;
			}
			
		}
		$salToCutForCasual = $salToCutForSick = 0;		
		if($extraCausalLeaves>0){
			$salToCutForCasual = ($basicSalary/$workingDays)*$extraCausalLeaves;			
		} else {
			$salToCutForCasual = 0;
		}
		if($extraSickLeaves>0){
			$salToCutForSick = ($basicSalary/$workingDays)*$extraSickLeaves;			
		} else {
			$salToCutForSick = 0;
		}
		$totDeductions = $salToCutForCasual+$salToCutForSick;		
		
		return $totDeductions;
	} else {
		//return 0;		
	}
}


function sendGCM_TechActivate($title, $body, $token, $show_in) {

    $url = 'https://fcm.googleapis.com/fcm/send';
	
	$dat = array('show_in' => $show_in);

    $fields = array('registration_ids' => array($token),
					'notification'=>array("body"=>$body, "title"=>$title, "data"=>$dat)
    );
    $fields = json_encode ( $fields );

    $headers = array (
            'Authorization: key=' . FCM_TECH_KEY,
            'Content-Type: application/json'
    );

    $ch = curl_init ();
    curl_setopt ( $ch, CURLOPT_URL, $url );
    curl_setopt ( $ch, CURLOPT_POST, true );
    curl_setopt ( $ch, CURLOPT_HTTPHEADER, $headers );
    curl_setopt ( $ch, CURLOPT_RETURNTRANSFER, true );
    curl_setopt ( $ch, CURLOPT_POSTFIELDS, $fields );

    $result = curl_exec ( $ch );
	
    curl_close ( $ch );
}

function sendIosTechActivate($title, $body, $token, $show_in) {
	
	// Put your device token here (without spaces):
	$deviceToken = str_replace(" ", "", $token);
	// Put your private key's passphrase here:
	$passphrase = 'swaroop';
	//// Put your alert message here:
	$message = $title." - ".$body;

	$ctx = stream_context_create();
	stream_context_set_option($ctx, 'ssl', 'local_cert', BASE_URI.'webservice/ckT.pem');
	stream_context_set_option($ctx, 'ssl', 'passphrase', $passphrase);

	
		//gateway.sandbox.push.apple.com:2195
	if($fp = stream_socket_client('ssl://gateway.sandbox.push.apple.com:2195',
								   $err, $errstr, 60, STREAM_CLIENT_CONNECT|STREAM_CLIENT_PERSISTENT, $ctx)){
		// Create the payload body
		$push_body['aps'] = array('badge' => +1, 'alert' => $message, 'sound' => 'default');
		$payload = json_encode($push_body);
	
		// Build the binary notification
		$msg = chr(0) . pack('n', 32) . pack('H*', $deviceToken) . pack('n', strlen($payload)) . $payload;
		// Send it to the server
		$result = fwrite($fp, $msg, strlen($msg));
		if (!$result) {
	    	//echo 'Message not delivered' . PHP_EOL;
		} else {
	    	//echo 'Message successfully delivered amar'.$message. PHP_EOL;
		}
		// Close the connection to the server
		fclose($fp);
	}	

	
}

function sendIosTechCatApproval($title, $body, $token, $show_in) {	
	// Put your device token here (without spaces):
	$deviceToken = str_replace(" ", "", $token);
	// Put your private key's passphrase here:
	$passphrase = 'swaroop';
	//// Put your alert message here:
	$message = $title." - ".$body;
	$ctx = stream_context_create();
	stream_context_set_option($ctx, 'ssl', 'local_cert', BASE_URI.'webservice/ckT.pem');
	stream_context_set_option($ctx, 'ssl', 'passphrase', $passphrase);
		//gateway.sandbox.push.apple.com:2195
	if($fp = stream_socket_client('ssl://gateway.sandbox.push.apple.com:2195',
								   $err, $errstr, 60, STREAM_CLIENT_CONNECT|STREAM_CLIENT_PERSISTENT, $ctx)){
		// Create the payload body
		$push_body['aps'] = array('badge' => +1, 'alert' => $message, 'sound' => 'default');
		$push_body['open_page'] = 'category';
		$payload = json_encode($push_body);
			
		// Build the binary notification
		$msg = chr(0) . pack('n', 32) . pack('H*', $deviceToken) . pack('n', strlen($payload)) . $payload;
		// Send it to the server
		$result = fwrite($fp, $msg, strlen($msg));
		if (!$result) {
	    	//echo 'Message not delivered' . PHP_EOL;
		} else {
	    	//echo 'Message successfully delivered amar'.$message. PHP_EOL;
		}
		// Close the connection to the server
		fclose($fp);
	}	

	
}

/*function sendGCM($title, $body, $token, $show_in) {

    $url = 'https://fcm.googleapis.com/fcm/send';
	
	$dat = array('show_in' => $show_in);

    $fields = array('registration_ids' => array($token),
					'notification'=>array("body"=>$body, "title"=>$title, "data"=>$dat)
    );
    $fields = json_encode ( $fields );

    $headers = array (
            'Authorization: key=' . "AAAAvAsjYuM:APA91bHhQVbDG9ZxoqNeP8ft0KjZOdnjmkXqVnEUTkjfm7GqmlfnTlIMK3zb27BzuGsTvB43TELgkNtRN34FtnG3cXGC06MISGmNViK6mhuaqz0k204Bm55ZNt_vgmuPDr_gK6f1HuGR",
            'Content-Type: application/json'
    );

    $ch = curl_init ();
    curl_setopt ( $ch, CURLOPT_URL, $url );
    curl_setopt ( $ch, CURLOPT_POST, true );
    curl_setopt ( $ch, CURLOPT_HTTPHEADER, $headers );
    curl_setopt ( $ch, CURLOPT_RETURNTRANSFER, true );
    curl_setopt ( $ch, CURLOPT_POSTFIELDS, $fields );

    $result = curl_exec ( $ch );
	
    curl_close ( $ch );
}*/


//already declared in view_task
/*function sendGCM_user($title, $body, $token) {

    $url = 'https://fcm.googleapis.com/fcm/send';	

    $fields = array('registration_ids' => array($token),
					'notification'=>array("body"=>$body, "title"=>$title)
    );
    $fields = json_encode ( $fields );

    $headers = array (
            'Authorization: key=' . "AAAAbldmHG0:APA91bHoy3t5-C56ctQFFSUI4zPt1MwkblO8jG4ZbZoiNxI6QPkr-a0B7Dq5ErLXueScR7POJcUnHBkD3EDlThkcTU2zNXV5lOvNaDm6Wn9pOPsIN0ySBKeqwrXjLE9kJPlrxa1SuD4N",
            'Content-Type: application/json'
    );

    $ch = curl_init ();
    curl_setopt ( $ch, CURLOPT_URL, $url );
    curl_setopt ( $ch, CURLOPT_POST, true );
    curl_setopt ( $ch, CURLOPT_HTTPHEADER, $headers );
    curl_setopt ( $ch, CURLOPT_RETURNTRANSFER, true );
    curl_setopt ( $ch, CURLOPT_POSTFIELDS, $fields );

    $result = curl_exec ( $ch );
	/*echo '<pre>';
    print_r($result);
	exit;*/
    /*curl_close ( $ch );
}*/

function sendGCMUserAdmin($title, $body, $token, $show_in, $user_id, $user_name, $type) {

    $url = 'https://fcm.googleapis.com/fcm/send';
	
	$dat = array('title'=>$title,
				 'body'=>$body,				 
				 'sound'=>'default',
				 'show_in' => $show_in,				 
				 'user_id'=>$user_id,
				 'user_name'=>$user_name,
				 'type'=>$type);	
	
    $fields = array('to' => $token,					
					'data' => $dat);
	
    $fields = json_encode ( $fields );
   

    /*$headers = array (
            'Authorization: key=' . "AAAAvAsjYuM:APA91bHhQVbDG9ZxoqNeP8ft0KjZOdnjmkXqVnEUTkjfm7GqmlfnTlIMK3zb27BzuGsTvB43TELgkNtRN34FtnG3cXGC06MISGmNViK6mhuaqz0k204Bm55ZNt_vgmuPDr_gK6f1HuGR",
            'Content-Type: application/json'
    );*/
	$headers = array (
            'Authorization: key=' . FCM_USER_KEY,
            'Content-Type: application/json'
    );

    $ch = curl_init ();
    curl_setopt ( $ch, CURLOPT_URL, $url );
    curl_setopt ( $ch, CURLOPT_POST, true );
    curl_setopt ( $ch, CURLOPT_HTTPHEADER, $headers );
    curl_setopt ( $ch, CURLOPT_RETURNTRANSFER, true );
    curl_setopt ( $ch, CURLOPT_POSTFIELDS, $fields );

    $result = curl_exec ( $ch );
	/*echo '<pre>';
    print_r($result);
	exit;*/
    curl_close ( $ch );
}


function sendIosUserPush($title, $body, $token) {
	
	// Put your device token here (without spaces):
	$deviceToken = str_replace(" ", "", $token);
	// Put your private key's passphrase here:
	$passphrase = 'swaroop';
	//// Put your alert message here:
	$message = $title." - ".$body;

	$ctx = stream_context_create();
	//stream_context_set_option($ctx, 'ssl', 'local_cert', BASE_URI.'lcontrol/includes/CKPdtnUser.pem');
	stream_context_set_option($ctx, 'ssl', 'local_cert', BASE_URI.'webservice/ck.pem');
	stream_context_set_option($ctx, 'ssl', 'passphrase', $passphrase);
//gateway.sandbox.push.apple.com:2195
//gateway.push.apple.com:2195
	if($fp = stream_socket_client('ssl://gateway.sandbox.push.apple.com:2195',
								   $err, $errstr, 60, STREAM_CLIENT_CONNECT|STREAM_CLIENT_PERSISTENT, $ctx)){
		// Create the payload body
		$push_body['aps'] = array('badge' => +1, 'alert' => $message, 'sound' => 'default');
		$payload = json_encode($push_body);
	
		// Build the binary notification
		$msg = chr(0) . pack('n', 32) . pack('H*', $deviceToken) . pack('n', strlen($payload)) . $payload;
		// Send it to the server
		$result = fwrite($fp, $msg, strlen($msg));
		if (!$result) {
	    	//echo 'Message not delivered' . PHP_EOL;
		} else {
	    	//echo 'Message successfully delivered amar'.$message. PHP_EOL;
		}
		// Close the connection to the server
		fclose($fp);		   	
	}

	
}

/******Declared in add_technician page******/
/*function sendIosTechPush($title, $body, $token, $show_in) {
	
	// Put your device token here (without spaces):
	
	$deviceToken = str_replace(" ", "", $token);
	// Put your private key's passphrase here:
	$passphrase = 'swaroop';
	//// Put your alert message here:
	$message = $title." - ".$body;

	$ctx = stream_context_create();
	stream_context_set_option($ctx, 'ssl', 'local_cert', 'CKPdtnTech.pem');	
	stream_context_set_option($ctx, 'ssl', 'passphrase', $passphrase);
	$fp = stream_socket_client('ssl://gateway.push.apple.com:2195',
							   $err, $errstr, 60, STREAM_CLIENT_CONNECT|STREAM_CLIENT_PERSISTENT, $ctx);	
	
	

	// Create the payload body
	$push_body['aps'] = array('badge' => +1, 'alert' => $message, 'sound' => 'default');
	$payload = json_encode($push_body);

	// Build the binary notification
	$msg = chr(0) . pack('n', 32) . pack('H*', $deviceToken) . pack('n', strlen($payload)) . $payload;
	// Send it to the server
	$result = fwrite($fp, $msg, strlen($msg));
	if (!$result) {
    	//echo 'Message not delivered' . PHP_EOL;
	} else {
    	//echo 'Message successfully delivered amar'.$message. PHP_EOL;
	}
	// Close the connection to the server
	fclose($fp);
}*/

/******Declared in add_technician page******/
/*function sendIosTechPush($title, $body, $token, $show_in) {
	
	// Put your device token here (without spaces):
	$deviceToken = str_replace(" ", "", $token);
	// Put your private key's passphrase here:
	$passphrase = 'swaroop';
	//// Put your alert message here:
	$message = $title." - ".$body;

	$ctx = stream_context_create();
	stream_context_set_option($ctx, 'ssl', 'local_cert', 'CKPdtnTech.pem');
	stream_context_set_option($ctx, 'ssl', 'passphrase', $passphrase);

	
		//gateway.sandbox.push.apple.com:2195
	if($fp = stream_socket_client('ssl://gateway.push.apple.com:2195',
							   $err, $errstr, 60, STREAM_CLIENT_CONNECT|STREAM_CLIENT_PERSISTENT, $ctx)){
		// Create the payload body
		$push_body['aps'] = array('badge' => +1, 'alert' => $message, 'sound' => 'default');
		$payload = json_encode($push_body);
	
		// Build the binary notification
		$msg = chr(0) . pack('n', 32) . pack('H*', $deviceToken) . pack('n', strlen($payload)) . $payload;
		// Send it to the server
		$result = fwrite($fp, $msg, strlen($msg));
		if (!$result) {
	    	//echo 'Message not delivered' . PHP_EOL;
		} else {
	    	//echo 'Message successfully delivered amar'.$message. PHP_EOL;
		}
		// Close the connection to the server
		fclose($fp);
	}	

	
}*/

?>