<?
class jackycrm
{
	static $vcpType = array(1=>"V",
							2=>"C",
							3=>"P");											
															  
	static function getVcpTypeList($listName = "", $id="", $width = "100%",$multiple){
		print utility::createList(self::$vcpType,$listName,$id,$width,"Please Select",$multiple,$className="form-control");
  	}
	static function getVcpTypeById($id){
		return self::$vcpType[$id];
	}
	static function getVcpIdByType($type){
		if($type == "v" || $type == "V"){
			return 1;
		}else if($type == "c" || $type == "C"){
			return 2;
		}else if($type == "p" || $type == "P"){
			return 3;
		}
	}
	static function getJobUserById($job_user_id){
		global $db,$conn;		
		$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 getEnqQuoteJobList($listName = "", $id="", $width = "100%", $multiple,$className,$required=""){
  		global $db,$conn;		
		$result = $db->query("SELECT * FROM job 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'])." / ".ucfirst($row['first_name'])." ".ucfirst($row['last_name']);
			}			
		}
		print utility::createList($dataArray,$listName,$id,$width,"Please Select",$multiple,$className,100,$required);
	}
	static function getEnqQuoteJobById($job_id){
		global $db,$conn;		
		$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'])." / ".ucfirst($row['first_name'])." ".ucfirst($row['last_name']);
		}
	}
	static function getEnqQuoteJobStatusById($job_id){
		global $db,$conn;		
		$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,$conn;		
		$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,$conn;		
		$today_date = date("Y-m-d");
		$result = $db->query("SELECT * FROM appointment WHERE appointment_date >= '$today_date' ");
		return $db->num_rows($result);
	}
	static function getTotalActiveReminderByConsultantId($consultant_id){
		global $db,$conn;		
		$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,$conn;		
		$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,$conn;	
		$job_ids = "";
		$sql1 = "select job_id FROM job_admin_mapping where admin_id = '".$consultant_id."' ";
		$rs1 =  mysqli_query($conn,$sql1);
		if(mysqli_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,$conn;	
		$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=>"#71E096");
	static $jobCompleteStatusPercentArray = array(1=>"Less than 35%",
												  2=>"More than 35%",										
												  3=>"More than 50%");

	static function getInvoiceList($listName = "", $id="", $width = "100%", $multiple,$className="form-control",$job_id){
  		global $db,$conn;		
		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,$conn;		
		$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,$conn;		
		$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"];
			}
			if($amount == 0){
				return self::$invoicePaymentStatusArray[1];
			}else if($amount < $invoice_amount){
				return self::$invoicePaymentStatusArray[2];
			}else if($amount == $invoice_amount){
				return self::$invoicePaymentStatusArray[3];
			}
		}else{
			return self::$invoicePaymentStatusArray[1];
		}
	}
	static function getInvoicePaymentAmountById($invoice_id){
		global $db,$conn;		
		$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,$conn;		
		$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,$conn;		
		$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,$conn;
		$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 getCountyList($listName = "", $id="", $width = "100%", $multiple,$className="form-control"){
  		global $db,$conn;		
		$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,$conn;		
		$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,$conn;
		$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 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,$conn;		
		$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,$conn;		
		$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();
			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,$conn;		
		$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	number_format($amount,2);
		}else{
			return number_format($amount,2);
		}
	}
	// 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 $weekArray = array(1=>1,
							  2=>2,
							  3=>3,
							  4=>4,
							  5=>5,
							  6=>6);
							   
	static function getWeekList($listName = "", $id="", $width = "100%",$multiple,$className="form-control"){
		print utility::createList(self::$weekArray,$listName,$id,$width,"Select",$multiple,$className);
	}
	static function getWeekById($week_id){
		return self::$weekArray[$week_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 getInvoiceNumber($job_id){
		global $db,$conn;		
		$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;
		return $invoice_number;
	}
	static $itrArray = array(1=>1,
							 2=>2,
							 3=>3,
							 4=>4,
							 5=>5,
							 6=>6);
							   
	static function getITR($listName = "", $id="", $width = "100%",$multiple,$className="form-control"){
		print utility::createList(self::$itrArray,$listName,$id,$width,"Select",$multiple,$className);
	}
	static function getITRById($itr_id){
		return self::$itrArray[$itr_id];
	}
	static $referralType = array(1=>"Referral Received",
								 2=>"Referral Given");											
															  
	static function getReferralTypeList($listName = "", $id="", $width = "100%",$multiple,$className,$required=""){
		print utility::createList(self::$referralType,$listName,$id,$width,"Please Select",$multiple,$className,100,$required);
  	}
	static function getReferralTypeById($id){
		return self::$referralType[$id];
	}
} # Class Ends
?>