<?
class krscrm
{
	static $jobType = array(1=>"Fixed",
							2=>"Consultancy");											
															  
	static function getJobTypeList($listName = "", $id="", $width = "100%",$multiple){
		print utility::createList(self::$jobType,$listName,$id,$width,"Please Select",$multiple,$className="form-control");
  	}
	static function getJobTypeById($id){
		return self::$jobType[$id];
	}
	static $krsJobType = array(1=>"Domestic",
							   2=>"Commercial");											
															  
	static function getkrsJobTypeList($listName = "", $id="", $width = "100%",$multiple){
		print utility::createList(self::$krsJobType,$listName,$id,$width,"Please Select Job Type",$multiple,$className="form-control");
  	}
	static function getkrsJobTypeById($id){
		return self::$krsJobType[$id];
	}
	static $vatType = array(1=>"0%",
							2=>"5%",
							3=>"20%");											
															  
	static function getvatTypeList($listName = "", $id="", $width = "100%",$multiple){
		print utility::createList(self::$vatType,$listName,$id,$width,"Please Select",$multiple,$className="form-control");
  	}
	static function getvatTypeById($id){
		return self::$vatType[$id];
	}
	static function getJobUserById($job_user_id){
		global $db;		
		$result = $db->query("SELECT * FROM job WHERE id = $job_user_id");
		if ($db->num_rows($result) > 0){
			$row = $db->next_record($result);
			return ucfirst($row['first_name1'])." ".ucfirst($row['last_name1']);
		}
	}
	static function getJobEndingSoon(){
		global $db;		
		$result = $db->query("SELECT * FROM job WHERE is_job = 1 AND job_end_date >= CURDATE() AND job_end_date < CURDATE() + INTERVAL 60 DAY AND enquiry_status != '46'");
		return $db->num_rows($result);
	}
	static function getFixedPackagesList($listName = "", $id="", $width = "100%", $multiple,$className="form-control"){
  		global $db;		
		$result = $db->query("SELECT * FROM fixed_packages ORDER BY id");
		$dataArray = array();
		if ($db->num_rows($result) > 0){
			while($row = $db->next_record($result)){
				$dataArray[$row['id']] = stripslashes($row['package_name']);
			}			
		}
		print utility::createList($dataArray,$listName,$id,$width,"Please Select",$multiple,$className);
	}
	static function getFixedPackagesById($package_id){
		global $db;		
		$result = $db->query("SELECT * FROM fixed_packages WHERE id = $package_id");
		if ($db->num_rows($result) > 0){
			$row = $db->next_record($result);
			return $row['package_name'];
		}
	}
	static function getHourlyPackagesList($listName = "", $id="", $width = "100%", $multiple,$className="form-control"){
  		global $db;		
		$result = $db->query("SELECT * FROM hourly_packages ORDER BY id");
		$dataArray = array();
		if ($db->num_rows($result) > 0){
			while($row = $db->next_record($result)){
				$dataArray[$row['id']] = stripslashes($row['duration_month'])." months";
			}			
		}
		print utility::createList($dataArray,$listName,$id,$width,"Please Select",$multiple,$className);
	}
	static function getHourlyPackagesById($time_frame_id){
		global $db;		
		$result = $db->query("SELECT * FROM hourly_packages WHERE id = $time_frame_id");
		if ($db->num_rows($result) > 0){
			$row = $db->next_record($result);
			return $row['duration_month'];
		}
	}
	
	static function getEnqQuoteJobList($listName = "", $id="", $width = "100%", $multiple,$className,$required=""){
  		global $db,$config,$conn;;	
		$archive_status_id = $config->archive_status_id;
		$result = $db->query("SELECT * FROM job WHERE is_archive = '0' AND is_enquiry = '1' ORDER BY id");
		$dataArray = array();
		if ($db->num_rows($result) > 0){
			while($row = $db->next_record($result)){
				$site_address = "";
				/* if($row['second_address']!=""){
					$site_address = " - ".stripslashes($row['second_address']);
				} */
				$dataArray[$row['id']] = $row['id']." - ".stripslashes($row['first_name'].' '.$row['last_name']).$site_address;
				/* if($row['client_industry']==1 || $row['client_industry']==0 ){
					
					$dataArray[$row['id']] = $row['id']." - ".stripslashes($row['first_name'].' '.$row['last_name']).$site_address;
				}else{
					$dataArray[$row['id']] = $row['id']." - ".stripslashes($row['company_name']).$site_address;
				} */
			}			
		}
		print utility::createList($dataArray,$listName,$id,$width,"Please Select",$multiple,$className,100,$required);
	}
	static function getEnqQuoteJobListForInvoice($listName = "", $id="", $width = "100%", $multiple,$className,$required=""){
  		global $db,$config;	
		$archive_status_id = $config->archive_status_id;
		$result = $db->query("SELECT * FROM job ORDER BY id");
		$dataArray = array();
		if ($db->num_rows($result) > 0){
			while($row = $db->next_record($result)){
			
				if($row['client_industry']==1){
					$dataArray[$row['id']] = $row['id']." - ".stripslashes($row['first_name1'].''.$row['last_name1']);
				}else{
					$dataArray[$row['id']] = $row['id']." - ".stripslashes($row['company_name']);
				}
			}			
		}
		print utility::createList($dataArray,$listName,$id,$width,"Please Select",$multiple,$className,100,$required);
	}
	static function getEnqQuoteJobById($job_id){
		global $db;		
		$result = $db->query("SELECT * FROM job WHERE id = $job_id");
		if ($db->num_rows($result) > 0){
			$row = $db->next_record($result);
			return $row['id']." - ".stripslashes($row['company_name']);
		}
	}
	static function getEnqQuoteJobStatusById($job_id){
		global $db;		
		$result = $db->query("SELECT * FROM job WHERE id = $job_id");
		if ($db->num_rows($result) > 0){
			$row = $db->next_record($result);
			if($row['is_job']==1){
				return "j";
			}else if($row['is_quotation']==1){
				return "q";
			}else{
				return "e";
			}
		}
	}
	static function getTotalActiveAppointmentByConsultantId($consultant_id){
		global $db;		
		$today_date = date("Y-m-d");
		$result = $db->query("SELECT * FROM appointment WHERE admin_id LIKE '%".$_SESSION['user_id']."%' AND appointment_date >= '$today_date' ");
		return $db->num_rows($result);
	}
	static function getTotalActiveAppointment(){
		global $db;		
		$today_date = date("Y-m-d");
		$result = $db->query("SELECT * FROM appointment WHERE appointment_date = '$today_date 00:00:00' ");
		return $db->num_rows($result);
	}
	static function getTotalActiveReminderByConsultantId($consultant_id){
		global $db;		
		$today_date = date("Y-m-d");
		$result = $db->query("SELECT * FROM notes WHERE admin_id LIKE '%".$_SESSION['user_id']."%' AND note_date >= '$today_date' AND note_type = 1 ");
		return $db->num_rows($result);
	}
	static function getTotalActiveReminder(){
		global $db;		
		$today_date = date("Y-m-d");
		$result = $db->query("SELECT * FROM notes WHERE note_date >= '$today_date' AND note_type = 1 ");
		return $db->num_rows($result);
	}
	static function getTotalNotesByConsultantId($consultant_id){
		global $db;	
		$job_ids = "";
		$sql1 = "select job_id FROM job_admin_mapping where admin_id = '".$consultant_id."' ";
		$rs1 =  mysqli_query($conn,$sql1);
		if(mysql_num_rows($rs1) > 0){
			while($row1 = mysqli_fetch_assoc($rs1)){
				$job_ids .= $row1["job_id"].",";
			}
			$job_ids = substr($job_ids,0,-1);
		}
		if(isset($job_ids) && $job_ids !=""){
			$result = $db->query("SELECT * FROM notes WHERE job_id IN($job_ids) AND note_type = 0 AND job_user_id != 0 ");
		}else{
			$result = "";
		}
		return $db->num_rows($result);
	}
	static function getTotalNotesOfUser(){
		global $db;	
		$result = $db->query("SELECT * FROM notes WHERE note_type = 0 AND job_user_id != 0 ");
		return $db->num_rows($result);
	}
	static $invoiceStatus = array(1=>"Not Invoiced",
								  2=>"1st Invoiced",
								  3=>"2nd Invoiced",
								  4=>"3rd Invoiced");											
															  
	static function getInvoiceStatusList($listName = "", $id="", $width = "100%",$multiple){
		print utility::createList(self::$invoiceStatus,$listName,$id,$width,"Please Select",$multiple,$className="form-control");
  	}
	static function getInvoiceStatusById($id){
		return self::$invoiceStatus[$id];
	}
	
	static $paymentStatus = array(1=>"1st Payment",
								  2=>"2nd Payment",
								  3=>"3rd Payment");											
															  
	static function getPaymentStatusList($listName = "", $id="", $width = "100%",$multiple){
		print utility::createList(self::$paymentStatus,$listName,$id,$width,"Please Select",$multiple,$className="form-control");
  	}
	static function getPaymentStatusById($id){
		return self::$paymentStatus[$id];
	}
	
	static $paymentModeColorArray = array(1=>"#FF96E3",
										  2=>"#FFDC89",
										  3=>"#71E096");
										  
	static $invoiceModeColorArray = array(1=>"#f25757",
										  2=>"#AFDCEC",
										  3=>"#FFC0CB",
										  4=>"#DCDCDC");
	
	static $jobStatusColorArray = array('e'=>"#FFC0CB",
										'q'=>"#FFDC89",										
										'j'=>"#71E096");
	
	static $jobCompleteStatusColorArray = array(1=>"#FFC0CB",
												2=>"#FFDC89",										
												3=>"#b19cd9",										
												4=>"#71E096");
	static $jobCompleteStatusPercentArray = array(1=>"Less than 25%",
												  2=>"More than 25% and Less than 50%",										
												  3=>"More than 50% and Less than 75%",
												  4=>"More than 75%");
	static $invoiceStatusColorArray = array(1=>"#FFC0CB",
											2=>"#71E096");
	static $invoiceStatusArray = array(1=>"Not / Partially Paid",
									   2=>"Full Paid");
												  
	static function getInvoiceList($listName = "", $id="", $width = "100%", $multiple,$className="form-control",$job_id){
  		global $db;		
		if(isset($job_id) && $job_id !=""){
			$result = $db->query("SELECT * FROM invoice WHERE job_id = $job_id ORDER BY invoice_date DESC");
			$dataArray = array();
			if ($db->num_rows($result) > 0){
				while($row = $db->next_record($result)){
					$dataArray[$row['id']] = self::getInvoiceStatusById($row['invoice_status'])." - ".number_format($row['amount'],2);
				}			
			}
			print utility::createList($dataArray,$listName,$id,$width,"Please Select",$multiple,$className);
		}else{
			print "";
		}
	}
	static function getInvoiceById($invoice_id){
		global $db;		
		$result = $db->query("SELECT * FROM invoice WHERE id = $invoice_id");
		if ($db->num_rows($result) > 0){
			$row = $db->next_record($result);
			return self::getInvoiceStatusById($row['invoice_status'])." - ".number_format($row['amount'],2);
		}
	}
	
	static $invoicePaymentStatusArray = array(1=>"Not Paid",
											  2=>"Partially Paid",
											  3=>"Fully Paid");
	static function getInvoicePaymentStatusList($listName = "", $id="", $width = "100%",$multiple){
		print utility::createList(self::$invoicePaymentStatusArray,$listName,$id,$width,"Please Select",$multiple,$className="form-control");
  	}
	static function getInvoicePaymentStatusById($invoice_id){
		global $db;		
		$amount = 0;
		$result = $db->query("SELECT * FROM payment WHERE invoice_id = $invoice_id");
		if ($db->num_rows($result) > 0){
			//$invoice_amount = utility::getTableFieldById("invoice","amount","id",$invoice_id);
			$invoice_amount = str_ireplace(' ','',self::getInvoiceAmountByInvoiceId($invoice_id));
			while($row = $db->next_record($result)){
				$amount = $amount + $row["amount"];
			}
			if($amount == 0){				
				//$db->query("UPDATE invoice SET is_fully_paid='1' WHERE id = $invoice_id");
				return self::$invoicePaymentStatusArray[1];
			}else if($amount < $invoice_amount){
				//$db->query("UPDATE invoice SET is_fully_paid='2' WHERE id = $invoice_id");
				return self::$invoicePaymentStatusArray[2];				
			}else if($amount >= $invoice_amount){
				//$db->query("UPDATE invoice SET is_fully_paid='3' WHERE id = $invoice_id");
				return self::$invoicePaymentStatusArray[3];				
			}
		}else{
			//$db->query("UPDATE invoice SET is_fully_paid='1' WHERE id = $invoice_id");
			return self::$invoicePaymentStatusArray[1];
		}
	}
	static function getInvoicePaymentAmountById($invoice_id){
		global $db;		
		$amount = 0;
		$result = $db->query("SELECT * FROM payment WHERE invoice_id = $invoice_id");
		if ($db->num_rows($result) > 0){
			$invoice_amount = utility::getTableFieldById("invoice","amount","id",$invoice_id);
			while($row = $db->next_record($result)){
				$amount = $amount + $row["amount"];
			}
			return	number_format($amount,2);
		}else{
			return number_format($amount,2);
		}
	}
	static function getMaxInvoiceStatusByJobId($job_id){
		global $db;		
		$result = $db->query("SELECT MAX(invoice_status) as invoice_status FROM invoice WHERE job_id = $job_id");
		if ($db->num_rows($result) > 0){
			$row = $db->next_record($result);
			return $row["invoice_status"];
		}
	}
	static function getMaxPaymentStatusByJobId($job_id){
		global $db;		
		$result = $db->query("SELECT MAX(payment_status) as payment_status FROM payment WHERE job_id = $job_id");
		if ($db->num_rows($result) > 0){
			$row = $db->next_record($result);
			return $row["payment_status"];
		}
	}
	
	static function getTotalTimeByJobId($job_id){
		global $db;
		$result = $db->query("SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(`timesheet_time`))) AS total_time FROM timesheet WHERE job_id = '$job_id' ");
		if ($db->num_rows($result) > 0){
			$row = $db->next_record($result);
			if(isset($row["total_time"]) && $row["total_time"] !=""){
				return $row["total_time"];
			}else{
				return 0;
			}
		}
	}
	static function getJobIdsForLeadConsultant($admin_id){
		global $db;
		$result = $db->query("SELECT * FROM job_admin_mapping WHERE admin_id = '$admin_id' ");
		$job_ids_for_lead_consultant = "";
		if ($db->num_rows($result) > 0){
			while($data = $db->next_record($result)){
				$lead_consultant = utility::getTableFieldById('job','lead_consultant','id',$data["job_id"]);
				if($lead_consultant == $admin_id){
					$job_ids_for_lead_consultant .= $data["job_id"].",";
				}
			}
			$job_ids_for_lead_consultant = substr($job_ids_for_lead_consultant,0,-1);
		}
		return $job_ids_for_lead_consultant;
	}
	static function getTotalTimeByAdminId($admin_id,$lead="",$where_for_admin=''){
		global $db;
		if($where_for_admin !=""){
			$where_for_admin = " $where_for_admin ";
		}
		if($lead !=""){
			$job_ids_for_lead_consultant = self::getJobIdsForLeadConsultant($admin_id);
			$where = " WHERE job_id IN($job_ids_for_lead_consultant) ";
		}else{
			$where = " WHERE admin_id LIKE '%$admin_id%' ";
		}
		
		$result = $db->query("SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(`timesheet_time`))) AS total_time FROM timesheet $where $where_for_admin ");
		if ($db->num_rows($result) > 0){
			$row = $db->next_record($result);
			if(isset($row["total_time"]) && $row["total_time"] !=""){
				return $row["total_time"];
			}else{
				return 0;
			}
		}
	}
	static function getCountyList($listName = "", $id="", $width = "100%", $multiple,$className="form-control"){
  		global $db;		
		$result = $db->query("SELECT * FROM county ORDER BY id");
		$dataArray = array();
		if ($db->num_rows($result) > 0){
			while($row = $db->next_record($result)){
				$dataArray[$row['id']] = stripslashes($row['county']);
			}			
		}
		print utility::createList($dataArray,$listName,$id,$width,"Please Select",$multiple,$className);
	}
	static function getCountyById($county_id){
		global $db;		
		$result = $db->query("SELECT * FROM county WHERE id = '$county_id' ");
		if ($db->num_rows($result) > 0){
			$row = $db->next_record($result);
			return $row['county'];
		}
	}
	
	static function getTimerTimeInSecondByJobId($job_id){
		global $db;
		$result = $db->query("SELECT TIME_TO_SEC(`timer_time`) AS timer_time FROM timer WHERE job_id = '$job_id' ");
		if ($db->num_rows($result) > 0){
			$row = $db->next_record($result);
			return $row["timer_time"];
		}
	}
	static function getTimerTimeInSecondByJobIdAndAdminId($job_id,$admin_id){
		global $db;
		$result = $db->query("SELECT TIME_TO_SEC(`timer_time`) AS timer_time FROM timer WHERE job_id = '$job_id' AND admin_id = '$admin_id' ");
		if ($db->num_rows($result) > 0){
			$row = $db->next_record($result);
			return $row["timer_time"];
		}
	}
	static function getTotalTimeInDecimal($time){
		$timeArr = explode(':', $time);
		$decTime = ($timeArr[0]*60) + ($timeArr[1]) + ($timeArr[2]/60);
 
		return round(($decTime/60),2);
	}
	static function getEntryForMonthByJobId($job_id){
		global $db;		
		$result = $db->query("SELECT job_start_date FROM job WHERE id = $job_id");
		if ($db->num_rows($result) > 0){
			$row = $db->next_record($result);
			$job_start_date = $row['job_start_date'];
			$seconds  = strtotime(date('Y-m-d H:i:s')) - strtotime($job_start_date);
			$months = floor($seconds / (3600*24*30));
			$day = floor($seconds / (3600*24)) - ($months * 30);
			if($months == 0){
				return 1;
			}else if($months > 0){
				return $months + 1;
			}
		}
	}
	
	static function getDurationMonthsList($listName = "", $id="", $width = "100%", $multiple,$className="form-control"){
		global $db;		
		//$result = $db->query("SELECT MAX(duration_month) as duration FROM hourly_packages ");
		//if ($db->num_rows($result) > 0){
			//$row = $db->next_record($result);
			$dataArray = array();
			$row["duration"] =12;
			if($row["duration"] > 0){
				for($i = 1;$i <= $row["duration"];$i++){
					$dataArray[$i] = $i;
				}
			}
		//}
		print utility::createList($dataArray,$listName,$id,$width,"Please Select",$multiple,$className);
	}
	
	static function getPercentOfJobByJobId($job_id){
		$no_of_hours_worked = self::getTotalTimeInDecimal(self::getTotalTimeByJobId($job_id));
		$total_hours = utility::getTableFieldById("job","total_hours","id",$job_id);
		$percent = (($no_of_hours_worked / $total_hours) * 100);
		return number_format($percent,2);
	}
	
	static function ifHalfOfHourlyJobTimePassByJobId($job_id){
		$enquiry_type = utility::getTableFieldById("job","enquiry_type","id",$job_id);
		if($enquiry_type == 2){
			$job_start_date = utility::getTableFieldById("job","job_start_date","id",$job_id);
			$job_end_date = utility::getTableFieldById("job","job_end_date","id",$job_id);
			
			if($job_start_date != "0000-00-00 00:00:00" && $job_end_date != "0000-00-00 00:00:00"){
				$job_start_date_time  = strtotime($job_start_date);
				$job_end_date_time = strtotime($job_end_date);
				$mid_time       = ($job_start_date_time + $job_end_date_time) / 2;
				$mid_time       = round($mid_time);
				$now            = date('Y-m-d');
				$curr_date_in_time = strtotime($now);
				if($curr_date_in_time > $mid_time){
					return true;
				}else{
					return false;
				}
			}else{
				return false;
			}
		}else{
			return false;
		}
	}
	
	static function getTotalPaymentAmountByJobId($job_id){
		global $db;		
		$amount = 0;
		$result = $db->query("SELECT * FROM payment WHERE job_id = $job_id");
		if ($db->num_rows($result) > 0){
			while($row = $db->next_record($result)){
				$amount = $amount + $row["amount"];
			}
			return	round($amount,2);
		}else{
			return round($amount,2);
		}
	}
	static function getTotalQuotationItemsByJobId($job_id){
		global $db;
		$result = $db->query("SELECT * FROM quotation_items_mapping WHERE job_id = $job_id");
		return $db->num_rows($result);
	}
	static function getTotalMaterialItemsByJobId($job_id){
		global $db;
		$result = $db->query("SELECT * FROM material_items_mapping WHERE job_id = $job_id");
		return $db->num_rows($result);
	}
	static function getTotalNotesItemsByJobId($job_id){
		global $db;
		$result = $db->query("SELECT * FROM notes WHERE job_id = $job_id");
		return $db->num_rows($result);
	}
	// print year list
	static function getYearList($listName = "", $id="",  $width = "100%",$multiple,$className="form-control",$required=""){		
		$selectList ="<select $required class='$className' name='$listName' id='$listName' style='width: $width;'>";
		$options = "<option value=''>Year</option>";				
	    for($i=date('Y')-1; $i<=date('Y')+1; $i++) {
	      $selected = ($id == $i) ? "selected" : "";
	      $options.= "<option value=$i $selected>".$i."</option>";		  
	    }
		$selectList.=$options;
		$selectList.="</select>";
		print $selectList;
  	}
	static $monthArray = array("01"=>"Jan",
							   "02"=>"Feb",
							   "03"=>"Mar",
							   "04"=>"Apr",
							   "05"=>"May",
							   "06"=>"Jun",
							   "07"=>"Jul",
							   "08"=>"Aug",
							   "09"=>"Sep",
							   "10"=>"Oct",
							   "11"=>"Nov",
							   "12"=>"Dec");
							   
	static function getYearMonth($listName = "", $id="", $width = "100%",$multiple,$className="form-control",$required=""){
		print utility::createList(self::$monthArray,$listName,$id,$width,"Select Month",$multiple,$className,100,$required);
	}
	static function getYearMonthById($month_id){
		return self::$monthArray[$month_id];
	}
	
	static function getInitialOfCompanyName($company_name){
		$initial = "";
		$company_array = explode(" ",$company_name);
		foreach($company_array as $c){
			$initial .= ucfirst(substr($c,0,1));
		}
		return $initial;
	}
	static function getInitial8CharOfCompanyName($company_name){
		if(strlen($company_name) > 8){
			$company_name = substr($company_name,0,8);
			return $company_name;
		}else{
			return $company_name;
		}
	}
	static function getInvoiceNumber($job_id){
		global $db;		
		$result = $db->query("SELECT MAX(id) as invoice_id FROM invoice");
		if ($db->num_rows($result) > 0){
			$row = $db->next_record($result);
			$new_invoice_id = $row["invoice_id"] + 1;
		}
		$company_name = utility::getTableFieldById('job','company_name','id',$job_id);
		$initial_company_name = self::getInitialOfCompanyName($company_name);
		//$invoice_number = $initial_company_name."/".date("m")."/".date("y")."/".$new_invoice_id;
		$invoice_number = "INV100".$new_invoice_id;
		return $invoice_number;
	}
	static function getMonthsWorked($job_id){
		global $db;		
		$result = $db->query("SELECT DATEDIFF(CURDATE(),`job_start_date`) as d from job WHERE id = $job_id");
		if ($db->num_rows($result) > 0){
			$row = $db->next_record($result);
			$no_of_days = $row["d"];
			return round($no_of_days/30);
		}else{
			return 0;
		}
	}
	static $jobStatus = array(1=>"New",
							  2=>"Existing");											
															  
	static function jobStatusList($listName = "", $id="", $width = "100%",$multiple){
		print utility::createList(self::$jobStatus,$listName,$id,$width,"Please Select",$multiple,$className="form-control");
  	}
	static function getJobStatusById($id){
		return self::$jobStatus[$id];
	}
	static function getTimesheetTimeInRound($timesheet_time1){
		$timesheet_time_array = explode(":",$timesheet_time1);
		if($timesheet_time_array[0] > 0){
			$hours_in_min = $timesheet_time_array[0]*60;
		}else{
			$hours_in_min = 0;
		}
		$total_time_in_min = $hours_in_min + $timesheet_time_array[1];
		if($total_time_in_min <= 15){
			$timesheet_time = "00:15:00";
		}else if($total_time_in_min > 15 && $total_time_in_min <= 30){
			$timesheet_time = "00:30:00";
		}else if($total_time_in_min > 30 && $total_time_in_min <= 45){
			$timesheet_time = "00:45:00";
		}else if($total_time_in_min > 45 && $total_time_in_min <= 60){
			$timesheet_time = "01:00:00";
		}else if($total_time_in_min > 60 && $total_time_in_min <= 75){
			$timesheet_time = "01:15:00";
		}else if($total_time_in_min > 75 && $total_time_in_min <= 90){
			$timesheet_time = "01:30:00";
		}else if($total_time_in_min > 90 && $total_time_in_min <= 105){
			$timesheet_time = "01:45:00";
		}else if($total_time_in_min > 105 && $total_time_in_min <= 120){
			$timesheet_time = "02:00:00";
		}else if($total_time_in_min > 120 && $total_time_in_min <= 135){
			$timesheet_time = "02:15:00";
		}else if($total_time_in_min > 135 && $total_time_in_min <= 150){
			$timesheet_time = "02:30:00";
		}else if($total_time_in_min > 150 && $total_time_in_min <= 165){
			$timesheet_time = "02:45:00";
		}else if($total_time_in_min > 165 && $total_time_in_min <= 180){
			$timesheet_time = "03:00:00";
		}else if($total_time_in_min > 180 && $total_time_in_min <= 195){
			$timesheet_time = "03:15:00";
		}else if($total_time_in_min > 195 && $total_time_in_min <= 210){
			$timesheet_time = "03:30:00";
		}else if($total_time_in_min > 210 && $total_time_in_min <= 225){
			$timesheet_time = "03:45:00";
		}else if($total_time_in_min > 225 && $total_time_in_min <= 240){
			$timesheet_time = "04:00:00";
		}else{
			$timesheet_time = $timesheet_time1;
		}
		return $timesheet_time;
	}
	static function getTotalTarget($admin_id,$lead_status){
		global $db;		
		//$from_date = date("Y")."-04-01 00:00:00";
		$from_date = date("Y")."-".date("m")."-01 00:00:00";
		$number_of_days_in_month = cal_days_in_month(CAL_GREGORIAN, date("m"), date("Y"));
		//$to_date = date("Y-m-d")." 00:00:00";
		$to_date = date("Y")."-".date("m")."-".$number_of_days_in_month." 00:00:00";
		//echo "SELECT SUM($lead_status) as total from target WHERE admin_id = '$admin_id' AND (from_date >= '$from_date' AND to_date <= '$to_date') ";
		
		$result = $db->query("SELECT SUM($lead_status) as total from target WHERE admin_id = '$admin_id' AND (from_date >= '$from_date' AND to_date <= '$to_date') ");
		if ($db->num_rows($result) > 0){
			$row = $db->next_record($result);
			return $row["total"];
		}else{
			return 0;
		}
	}
	static function getTargetAchive($admin_id,$lead_status){
		global $db;		
		$from_date = date("Y")."-".date("m")."-01 00:00:00";
		$to_date = date("Y-m-d")." 23:59:59";
		if($lead_status == "enquiry"){
			$where_date_between = " enquiry_date BETWEEN '$from_date' and '$to_date' AND is_enquiry = '1' AND lead_consultant = '$admin_id' ";
		}else if($lead_status == "quotation"){
			$where_date_between = " quotation_date BETWEEN '$from_date' and '$to_date' AND is_quotation = '1' AND lead_consultant = '$admin_id' ";
		}else if($lead_status == "job"){
			$where_date_between = " job_date BETWEEN '$from_date' and '$to_date' AND is_job = '1' AND lead_consultant = '$admin_id' ";
		}
		//echo "SELECT COUNT(*) as total from job WHERE $where_date_between";
		
		$result = $db->query("SELECT COUNT(*) as total from job WHERE $where_date_between ");
		if ($db->num_rows($result) > 0){
			$row = $db->next_record($result);
			return $row["total"];
		}else{
			return 0;
		}
	}
	static function getTotalTargetAmount(){
		global $db;		
		$from_date = date("Y")."-04-01 00:00:00";
		$to_date = date("Y-m-d")." 00:00:00";
		//echo "SELECT SUM(target_amount) as total from target WHERE admin_id IN(30,31,32,35) AND (from_date >= '$from_date' AND to_date <= '$to_date') ";
		
		$result = $db->query("SELECT SUM(target_amount) as total from target WHERE admin_id IN(30,31,32,35) AND (from_date >= '$from_date' AND to_date <= '$to_date') ");
		if ($db->num_rows($result) > 0){
			$row = $db->next_record($result);
			return $row["total"];
		}else{
			return 0;
		}
	}
	static function getTotalTargetAmountByAdminId($admin_id){
		global $db;		
		$from_date = date("Y")."-".date("m")."-01 00:00:00";
		//$to_date = date("Y-m-d")." 00:00:00";
		$number_of_days_in_month = cal_days_in_month(CAL_GREGORIAN, date("m"), date("Y"));
		$to_date = date("Y")."-".date("m")."-".$number_of_days_in_month." 00:00:00";
		//echo "SELECT SUM(target_amount) as total from target WHERE admin_id IN(30,31,32,35) AND (from_date >= '$from_date' AND to_date <= '$to_date') ";
		
		$result = $db->query("SELECT SUM(target_amount) as total from target WHERE admin_id = '$admin_id' AND (from_date >= '$from_date' AND to_date <= '$to_date') ");
		if ($db->num_rows($result) > 0){
			$row = $db->next_record($result);
			return $row["total"]/1000;
		}else{
			return 0;
		}
	}
	static function getTotalTargetCompletedAmount(){
		global $db;		
		$from_date = date("Y")."-04-01 00:00:00";
		$to_date = date("Y-m-d")." 00:00:00";
		
		//$result = $db->query("SELECT (SUM(fixed_amount) + SUM(hourly_amount*total_hours)) as total_amount FROM job WHERE lead_consultant IN(30,31,32,35) AND (enquiry_date BETWEEN '$from_date' and '$to_date' OR quotation_date BETWEEN '$from_date' and '$to_date' OR job_date BETWEEN '$from_date' and '$to_date') ");
		
		$result = $db->query("SELECT SUM(amount) as total_amount from invoice WHERE admin_id IN(31,32,35) AND (invoice_date BETWEEN '$from_date' AND '$to_date') ");
		if ($db->num_rows($result) > 0){
			$row = $db->next_record($result);
			if($row["total_amount"] !=""){
				return $row["total_amount"];
			}else{
				return 0;
			}
		}else{
			return 0;
		}
	}
	static function getTotalTargetCompletedAmountByAdminId($admin_id){
		global $db;		
		$from_date = date("Y")."-".date("m")."-01 00:00:00";
		$to_date = date("Y-m-d")." 00:00:00";
		
		$result = $db->query("SELECT SUM(amount) as total from invoice WHERE admin_id = '$admin_id' AND (invoice_date BETWEEN '$from_date' AND '$to_date') ");
		if ($db->num_rows($result) > 0){
			$row = $db->next_record($result);
			return $row["total"]/1000;
		}else{
			return 0;
		}
		/*$result = $db->query("SELECT (SUM(fixed_amount) + SUM(hourly_amount*total_hours)) as total_amount FROM job WHERE lead_consultant = '$admin_id' AND (enquiry_date BETWEEN '$from_date' and '$to_date' OR quotation_date BETWEEN '$from_date' and '$to_date' OR job_date BETWEEN '$from_date' and '$to_date') ");
		if ($db->num_rows($result) > 0){
			$row = $db->next_record($result);
			return round($row["total_amount"]/1000,2);
		}else{
			return 0;
		}*/
	}
	static function getTotalSalesTargetAmountByAdminId($admin_id){
		$yearly_sales_target = utility::getTableFieldById('admin','yearly_sales_target','id',$admin_id);
		if($yearly_sales_target > 0){
			return round($yearly_sales_target/1000,2);
		}else{
			return 0;
		}
	}
	static function getTotalSalesCompletedAmountByAdminId($admin_id){
		global $db;		
		$from_date = date("Y")."-04-01 00:00:00";
		$to_date = date("Y-m-d")." 00:00:00";
		
		$result = $db->query("SELECT (SUM(fixed_amount) + SUM(hourly_amount*total_hours)) as total_amount FROM job WHERE lead_consultant = '$admin_id' AND (enquiry_date BETWEEN '$from_date' and '$to_date' OR quotation_date BETWEEN '$from_date' and '$to_date' OR job_date BETWEEN '$from_date' and '$to_date') ");
		if ($db->num_rows($result) > 0){
			$row = $db->next_record($result);
			return round($row["total_amount"]/1000,2);
		}else{
			return 0;
		}
	}
	
	static function getQuotationItemsList($listName = "", $id="", $width = "100%", $multiple,$className="form-control"){
  		global $db;		
		$result = $db->query("SELECT * FROM quotation_items ORDER BY id");
		$dataArray = array();
		if ($db->num_rows($result) > 0){
			while($row = $db->next_record($result)){
				$dataArray[$row['quotation_items_name']] = stripslashes($row['quotation_items_name']);
			}			
		}
		print utility::createList($dataArray,$listName,$id,$width,"Please Select",$multiple,$className);
	}
	static function getMaterialItemsList($listName = "", $id="", $width = "100%", $multiple,$className="form-control"){
  		global $db;		
		$result = $db->query("SELECT * FROM material_items ORDER BY id");
		$dataArray = array();
		if ($db->num_rows($result) > 0){
			while($row = $db->next_record($result)){
				$dataArray[$row['material_items_name']] = stripslashes($row['material_items_name']);
			}			
		}
		print utility::createList($dataArray,$listName,$id,$width,"Please Select",$multiple,$className);
	}
	static function getAddressByJobId($job_id){
		$job_id = $_REQUEST['job_id'];
		$sql_for_addess = "SELECT address1,address2,address3,city,county,postcode FROM job WHERE id = '$job_id' ";
		$rs_for_addess = mysqli_query($conn,$sql_for_addess);
		$row = mysqli_fetch_assoc($rs_for_addess);
		
		$address = "";
		if(isset($row["address1"]) && $row["address1"] !=""){
			$address .= $row["address1"]." ";
		}
		if(isset($row["address2"]) && $row["address2"] !=""){
			$address .= $row["address2"]." ";
		}
		if(isset($row["address3"]) && $row["address3"] !=""){
			$address .= $row["address3"]." ";
		}
		if(isset($row["city"]) && $row["city"] !=""){
			$address .= $row["city"]." ";
		}
		if(isset($row["county"]) && $row["county"] !=""){
			$address .= self::getCountyById($row['county'])." ";
		}
		if(isset($row["postcode"]) && $row["postcode"] !=""){
			$address .= $row['postcode'];
		}
		return $address;
	}
	static function getDescripByJobId($job_id){
		$job_id = $_REQUEST['job_id'];
		$sql_for_addess = "SELECT job_description FROM job WHERE id = '$job_id' ";
		$rs_for_addess = mysqli_query($conn,$sql_for_addess);
		$row = mysqli_fetch_assoc($rs_for_addess);
		
		$job_description = "";
		if(isset($row["job_description"]) && $row["job_description"] !=""){
			$job_description .= $row["job_description"]." ";
		}
		return $job_description;
	}
	static function getIncrementQuotationNo(){
		$sql = "SELECT max(quotation_no) as max_quotation_no FROM job";
		$rs = mysqli_query($conn,$sql);
		$data = mysqli_fetch_assoc($rs);
		$new_quotation_no = (($data['max_quotation_no']) ? $data['max_quotation_no'] : 0000) + 1;
		$new_quotation_no = str_pad($new_quotation_no, 4, '0', STR_PAD_LEFT);
		return $new_quotation_no;
	}
	static function getCustomerAddressById($job_id){
  		global $db;		
		$result = $db->query("SELECT address1,address2,address3,city,postcode FROM job WHERE id = '$job_id' ");
		$address = "";
		if ($db->num_rows($result) > 0){
			$enq_row = $db->next_record($result);
			if(isset($enq_row["address1"]) && $enq_row["address1"] !=""){
				$address .= $enq_row["address1"]; 
			}
			if(isset($enq_row["address2"]) && $enq_row["address2"] !=""){
				$address .= " ".$enq_row["address2"]; 
			}
			if(isset($enq_row["address3"]) && $enq_row["address3"] !=""){
				$address .= " ".$enq_row["address3"]; 
			}
			if(isset($enq_row["city"]) && $enq_row["city"] !=""){
				$address .= " ".$enq_row["city"]; 
			}
			if(isset($enq_row["postcode"]) && $enq_row["postcode"] !=""){
				$address .= " ".$enq_row["postcode"]; 
			}
			/* if(isset($enq_row["country"]) && $enq_row["country"] !=""){
				$address .= " ".utility::getTableFieldById('country','country','id',$enq_row["country"]); 
			} */
		}
		return $address;
	}
	static function getCustomerSiteAddressByJobId($job_id){
		return utility::getTableFieldById("job","second_address","id",$job_id);
	}
	static function getMemberSubscriptionFeeByMemberId($job_id){ 
		  $member_type_id = utility::getTableFieldById("member","member_type_id","id",$job_id);
		  $subscription_fee = utility::getTableFieldById("member_type","subscription_fee","id",$member_type_id);	
		  return $subscription_fee;
	}
	
	static function getJobListForInvoice($listName = "", $id="", $width = "100%", $multiple,$className,$required=""){
  		global $db,$config;	
		$archive_status_id = $config->archive_status_id;
		$result = $db->query("SELECT * FROM job WHERE is_job='1' ORDER BY id");
		$dataArray = array();
		if ($db->num_rows($result) > 0){
			while($row = $db->next_record($result)){
				$dataArray[$row['id']] = $row['id']." - ".stripslashes($row['company_name']);
			}			
		}
		print utility::createList($dataArray,$listName,$id,$width,"Please Select",$multiple,$className,100,$required);
	}
	static function getInvoicePaymentAmountWithoutNumberFormatById($invoice_id){
		global $db;		
		$amount = 0;
		$result = $db->query("SELECT * FROM payment WHERE invoice_id = $invoice_id");
		if ($db->num_rows($result) > 0){
			$invoice_amount = utility::getTableFieldById("invoice","amount","id",$invoice_id);
			while($row = $db->next_record($result)){
				$amount = $amount + $row["amount"];
			}
			return	$amount;
		}else{
			return $amount;
		}
	}static function getInvoiceAmountByInvoiceId($invoice_id){
		global $db;		
		$final_total = 0;
		$invoice_amount = 0;
		$total = 0;
		$job_id = utility::getTableFieldById("invoice","job_id","id",$invoice_id);
		$amount_percentage = utility::getTableFieldById("invoice","amount_percentage","id",$invoice_id);
		$vat_applied = utility::getTableFieldById("job","vat","id",$job_id);
		
		if($vat_applied == 1){
			$vat_value = 0;
		}else if($vat_applied == 2){
			$vat_value = 5;
		}else if($vat_applied == 3){
			$vat_value = 20;
		}
		
		$result = $db->query("SELECT * FROM quotation_items_mapping WHERE job_id = $job_id");
		if ($db->num_rows($result) > 0){
			while($row = $db->next_record($result)){
				$total = $row["qty_list"] * $row["quotation_items_price"];
				$vat_amount = ($total * $vat_value)/100;
				$total_with_vat = $total + $vat_amount;
				$final_total = $final_total + $total + $vat_amount;
			}
			$invoice_amount = ($amount_percentage/100) * $final_total;
			return	$invoice_amount;
		}else{
			return $invoice_amount;
		}
	}
	static function getInvoiceAmountWithoutVatInvoiceId($invoice_id){
		global $db;		
		$final_total = 0;
		$invoice_amount = 0;
		$total = 0;
		$job_id = utility::getTableFieldById("invoice","job_id","id",$invoice_id);
		$amount_percentage = utility::getTableFieldById("invoice","amount_percentage","id",$invoice_id);
		$vat_applied = utility::getTableFieldById("job","vat","id",$job_id);
		
		if($vat_applied == 1 || 1==1){
			$vat_value = 0;
		}else if($vat_applied == 2){
			$vat_value = 5;
		}else if($vat_applied == 3){
			$vat_value = 20;
		}
		
		$result = $db->query("SELECT * FROM quotation_items_mapping WHERE job_id = $job_id");
		if ($db->num_rows($result) > 0){
			while($row = $db->next_record($result)){
				$total = $row["qty_list"] * $row["quotation_items_price"];
				$vat_amount = ($total * $vat_value)/100;
				$total_with_vat = $total + $vat_amount;
				$final_total = $final_total + $total + $vat_amount;
			}
			$invoice_amount = ($amount_percentage/100) * $final_total;
			return	$invoice_amount;
		}else{
			return $invoice_amount;
		}
	}
	static function getInvoicePaidAmountByInvoiceId($invoice_id){
		global $db;		
		
		$total = 0;
		$job_id = utility::getTableFieldById("invoice","job_id","id",$invoice_id);			
		$result = $db->query("SELECT * FROM payment WHERE job_id = $job_id AND invoice_id = $invoice_id");
		if ($db->num_rows($result) > 0){
			while($row = $db->next_record($result)){
				$total = $total + $row["amount"];
				
			}
			return	$total;
		}else{
			return $total;
		}
	}
	static function getTotalAmountByQuotationId($job_id){
		global $db;		
		
		$final_total = 0;		
		$total = 0;			
		$vat_applied = utility::getTableFieldById("job","vat","id",$job_id);		
		if($vat_applied == 1){
			$vat_value = 0;
		}else if($vat_applied == 2){
			$vat_value = 5;
		}else if($vat_applied == 3){
			$vat_value = 20;
		}
		
		$result = $db->query("SELECT * FROM quotation_items_mapping WHERE job_id = $job_id");
		if ($db->num_rows($result) > 0){
			while($row = $db->next_record($result)){
				$total = $row["qty_list"] * $row["quotation_items_price"];
				$vat_amount = ($total * $vat_value)/100;
				$total_with_vat = $total + $vat_amount;
				$final_total = $final_total + $total + $vat_amount;
			}			
			return	$final_total;
		}else{
			return $final_total;
		}
	}
	static $appointmentType = array(1=>"One Off",
									3=>"Weekly",									
									4=>"Forthnightly",
									2=>"Monthly");											
															  
	static function getTypeOfAppointmentList($listName = "", $id="", $width = "100%",$multiple,$className="form-control"){
		print utility::createList(self::$appointmentType,$listName,$id,$width,"Please Select",$multiple,$className);
  	}
	static function getTypeOfAppointmentById($id){
		return self::$appointmentType[$id];
	}
	static $weekType = array(1=>"First",
							 2=>"Second",									
							 3=>"Third",
							 4=>"Fourth",
							 5=>"Last");											
															  
	static function getWeekList($listName = "", $id="", $width = "100%",$multiple,$className="form-control"){
		print utility::createList(self::$weekType,$listName,$id,$width,"Please Select",$multiple,$className);
  	}
	static $daysOfWeek = array(1=>"Mon",
							 2=>"Tue",									
							 3=>"Wed",									
							 4=>"Thu",
							 5=>"Fri",
							 6=>"Sat",
							 7=>"Sun");											
															  
	static function getDayOfWeekList($listName = "", $id="", $width = "100%",$multiple,$className="form-control"){
		print utility::createList(self::$daysOfWeek,$listName,$id,$width,"Please Select",$multiple,$className);
  	}
	static function getDayOfMonthList($listName = "", $id="", $width = "100%", $multiple,$className="form-control"){
		global $db;		
			$dataArray = array();
			$row["duration"] =31;
			if($row["duration"] > 0){
				for($i = 1;$i <= $row["duration"];$i++){
					$dataArray[$i] = $i;
				}
			}
		//}
		print utility::createList($dataArray,$listName,$id,$width,"Please Select",$multiple,$className);
	}
	
	static $jobValueSearchBy = array(">"=>"> (greater than)",
							   "<"=>"< (less than)",
							   "="=>"= (equal to)");											
															  
	static function getjobValueSearchByList($listName = "", $id="", $width = "100%",$multiple){
		print utility::createList(self::$jobValueSearchBy,$listName,$id,$width,"Please Select",$multiple,$className="form-control");
  	}
	static function getjobValueSearchById($id){
		return self::$jobValueSearchBy[$id];
	}
	static function getTotalAmountByJobId($job_id){
		global $db;		
		
		$final_total = 0;		
		$total = 0;			
		$vat_applied = utility::getTableFieldById("job","vat","id",$job_id);		
		if($vat_applied == 1 || 1==1){
			$vat_value = 0;
		}else if($vat_applied == 2){
			$vat_value = 5;
		}else if($vat_applied == 3){
			$vat_value = 20;
		}
		
		$result = $db->query("SELECT * FROM quotation_items_mapping WHERE job_id = $job_id");
		if ($db->num_rows($result) > 0){
			while($row = $db->next_record($result)){
				$total = $row["qty_list"] * $row["quotation_items_price"];
				$vat_amount = ($total * $vat_value)/100;
				$total_with_vat = $total + $vat_amount;
				$final_total = $final_total + $total + $vat_amount;
			}			
			return	$final_total;
		}else{
			return $final_total;
		}
	}
	static function getTotalPercentByJobId($job_id){
		global $db;
		$totat_percentage = 0;
		$result = $db->query("SELECT amount_percentage FROM invoice WHERE job_id = $job_id");
		if ($db->num_rows($result) > 0){			
			while($row = $db->next_record($result)){
				$totat_percentage = $totat_percentage + $row['amount_percentage'];
			}
		}
		return	$totat_percentage;		
	}
	static function getTotalFollowupsByJobId($job_id){
		global $db;
		$result = $db->query("SELECT * FROM followups WHERE job_id = $job_id");
		return $db->num_rows($result);
	}
	static function getTotalAppointmentByJobId($job_id){
		global $db;
		$result = $db->query("SELECT * FROM appointment WHERE job_id = $job_id");
		return $db->num_rows($result);
	}
	static function getTotalReminderByJobId($job_id){
		global $db;
		$result = $db->query("SELECT * FROM notes WHERE note_type = 1 AND job_id = $job_id");
		return $db->num_rows($result);
	}
	static function getTotalVatByJobId($job_id){
		global $db;		
		
		$vat_total = 0;		
			
		$vat_applied = utility::getTableFieldById("job","vat","id",$job_id);		
		if($vat_applied == 1){
			$vat_value = 0;
		}else if($vat_applied == 2){
			$vat_value = 5;
		}else if($vat_applied == 3){
			$vat_value = 20;
		}
		
		$result = $db->query("SELECT * FROM quotation_items_mapping WHERE job_id = $job_id");
		if ($db->num_rows($result) > 0){			
			while($row = $db->next_record($result)){
				$total = $row["qty_list"] * $row["quotation_items_price"];
				$vat_amount = ($total * $vat_value)/100;
				$vat_total = $vat_total + $vat_amount;
			}			
			return	$vat_total;
		}else{
			return $vat_total;
		}
	}	
	static function getInvoiceQuantityByJobId($job_id){
		global $db;
		$result = $db->query("SELECT * FROM quotation_items_mapping WHERE job_id = $job_id");
		return $db->num_rows($result);
	}
} # Class Ends
?>