<?php 
class Provider_model extends CI_Model {
	
	public function _consruct(){
		parent::_construct();
 	}

 	public function addProvider($provider_data = array()){
 		if(empty($provider_data))
 			return 0;

 		$userNameChk = $this->db->query("SELECT * FROM users 
 									     WHERE status!='2' AND username='".$provider_data['username']."'");
 		if(!empty($userNameChk) && $userNameChk->num_rows() > 0) return 4;

 		$emailChk = $this->db->query("SELECT * FROM provider AS PRV 
 									  INNER JOIN users AS USR ON (USR.id=PRV.provider_id)
								      WHERE USR.status!='2' AND PRV.email='".$provider_data['email']."'");
 		if(!empty($emailChk) && $emailChk->num_rows() > 0) return 2;

 		$phoneChk = $this->db->query("SELECT * FROM provider AS PRV 
 									  INNER JOIN users AS USR ON (USR.id=PRV.provider_id)
									  WHERE USR.status!='2' AND PRV.phone='".$provider_data['phone']."'");
 		if(!empty($phoneChk) && $phoneChk->num_rows() > 0) return 3;

 		$status = $this->db->insert('users',
									array('username'=>$provider_data['username'],
										  'password'=>$provider_data['password'],
										  'display_name'=>$provider_data['display_name'],
										  'profile_image'=>$provider_data['profile_image'],
										  'user_type'=>'2','status'=>'1'));
 		if(!$status){
 			return 0;
 		}
 		$provider_id = $this->db->insert_id();
		$status = $this->db->insert('provider', 
									 array('provider_id'=>$provider_id,
										   'name'=>$provider_data['name'],
										   'email'=>$provider_data['email'],
										   'phone'=>$provider_data['phone'],
										   'profile_image'=>$provider_data['profile_image']));
 		return $status;
 	}

 	public function getProviderData($provider_id='',$view='1'){
 		$cond  = (!empty($view))?" USR.status IN ($view) ":" USR.status != '2' ";
 		$cond .= (!empty($provider_id))?" AND PRV.provider_id='$provider_id' ":"";

 		$sql = "SELECT USR.username,USR.display_name,USR.profile_image,USR.user_type,USR.status,
 					   PRV.provider_id,PRV.name,PRV.email,PRV.phone,PRV.profile_image
			    FROM provider AS PRV 
			    INNER JOIN users AS USR ON (USR.id=PRV.provider_id)
			    WHERE $cond";

	    $providerData = $this->db->query($sql);

	    if(!empty($providerData)){
	    	return (empty($provider_id))?$providerData->result():$providerData->row();
	    }
	    return 0;
 	}

 	function updateProvider($provider_id = '', $provider_data = array()){
 		if(empty($provider_id) || empty($provider_data))
 			return 0;
 		$userIdChk = $this->db->query("SELECT * FROM provider AS PRV 
 									   INNER JOIN users AS USR ON (USR.id = PRV.provider_id)
 									   WHERE USR.status!='2' AND USR.id!='".$provider_id."' AND 
								   			 USR.username='".$provider_data['username']."'");
 		if(!empty($userIdChk) && $userIdChk->num_rows() > 0) { return 4; }

 		$emailChk = $this->db->query("SELECT * FROM provider AS PRV 
 									  INNER JOIN users AS USR ON (USR.id = PRV.provider_id)
 									  WHERE USR.status!='2' AND USR.id!='".$provider_id."' AND 
								   			PRV.email='".$provider_data['email']."'");
 		if(!empty($emailChk) && $emailChk->num_rows() > 0) { return 2; }

 		$phoneChk = $this->db->query("SELECT * FROM provider AS PRV 
 									  INNER JOIN users AS USR ON (USR.id = PRV.provider_id)
 									  WHERE USR.status!='2' AND USR.id!='".$provider_id."' AND 
								   			PRV.phone='".$provider_data['phone']."'");
 		if(!empty($phoneChk) && $phoneChk->num_rows() > 0) { return 3; }

 		$upMecArr = array('name'=>$provider_data['name'],
 						  'email'=>$provider_data['email'],
 						  'phone'=>$provider_data['phone']);

 		$admUpdateArr = array('username'=>$provider_data['username'],
							  'display_name'=>$provider_data['display_name']);

 		if(isset($provider_data['profile_image']) && !empty($provider_data['profile_image'])){
 			$upMecArr['profile_image'] = $provider_data['profile_image'];
 			$admUpdateArr['profile_image'] = $provider_data['profile_image'];
 		}

 		$status = $this->db->update('users',$admUpdateArr,array('id'=>$provider_id));
 		if(!$status) { return 0; }


 		if(isset($provider_data['licence']) && !empty($provider_data['licence']))
 			$upMecArr['licence'] = $provider_data['licence'];
 		
		$status = $this->db->update('provider',$upMecArr,array('provider_id'=>$provider_id));
 		return $status;
 	}

 	function changeStatus($provider_id = '', $status = '0'){
 		if(empty($provider_id)){
 			return 0;
 		}

 		$status = $this->db->update('users',array('status'=>$status),array('id'=>$provider_id));
 		return $status;
 	}

 	function getNearByProviders($location_data = array(),$sub_issues = array()){
 		if(empty($location_data) || empty($sub_issues)){
 			return 0;
 		}

 		$current_lat = $location_data['pickup_lat'];
        $current_lng = $location_data['pickup_lng'];
 		$issue_cat_id = implode(',',$sub_issues);

        $sql = "SELECT USR.display_name,USR.profile_image,ME.*,MS.shop_name,MS.address AS shop_address,
        			   MS.phone AS shop_phone,MS.email_id AS shop_email_id,
        			   3956*2*ASIN(SQRT(POWER(SIN(($current_lat-ME.location_lat)*pi()/180/2),2)+
        			   COS($current_lat*pi()/180 )*COS(ME.location_lat*pi()/180)*
        			   POWER(SIN(($current_lng-ME.location_lng)*pi()/180/2),2) )) AS distance
    			FROM provider AS ME
    			INNER JOIN users AS USR ON (USR.id=ME.provider_id)
    			LEFT JOIN provider_shop AS MS ON (MS.shop_id=ME.shop_id AND MS.status='1')
                WHERE USR.status='1'
                -- HAVING distance<30";

        $mechData = $this->db->query($sql);
        if(empty($mechData) || empty($mechData = $mechData->result_array())){
        	return 0;
        }

        $estimate = 0;
        $mechDataArr = array();
        foreach($mechData AS $index => $data){
        	if(empty($data['start_time']) || empty($data['end_time'])){
        		$scheduleTiming = array('09:00 AM','10:00 AM','11:00 AM','12:00 PM','01:00 PM',
        							    '02:00 PM','03:00 PM','04:00 PM','05:00 PM','06:00 PM');
        	} else {
        		$endTime = strtotime($data['end_time']);
			    $schTime = strtotime($data['start_time']);
			    $scheduleTiming = array();

			    for( ; $schTime <= ($endTime-3600) ; $schTime += 3600){
			    	$scheduleTiming[] = date('h:i A',$schTime);
			    }
        	}

        	$provider_id = $data['provider_id'];
        	$sql = "SELECT ISS.*, IC.*, MI.*
        			FROM issues_category AS IC
        			INNER JOIN issues AS ISS ON (IC.issue_id=ISS.issue_id)
        			LEFT JOIN provider_issues AS MI ON (MI.issue_cat_id=IC.issue_cat_id AND 
								MI.provider_id='$provider_id' AND MI.status='1')
        			WHERE ISS.status='1' AND IC.status='1' AND IC.issue_cat_id IN ($issue_cat_id)";

		  	$subIssData = $this->db->query($sql);

		  	$sIssueData = array();
	        if(!empty($subIssData) && !empty($subIssData = $subIssData->result_array())){
	        	$sIssueData = $subIssData;
	        }

	        $estimate = 0;
	        foreach($sIssueData AS $sIndex => $sIssue){
		        if(!empty($sIssue['custom_service_fee'])){
		        	$estimate += $sIssue['custom_service_fee'];
		        	$sIssueData[$sIndex]['service_fee'] = $sIssue['custom_service_fee'];
		        } else {
		        	$estimate += $sIssue['default_service_fee'];
		        	$sIssueData[$sIndex]['service_fee'] = $sIssue['default_service_fee'];
		        }
	        }

	        $mechData[$index]['estimate'] = $estimate;
	        $mechData[$index]['sub_issues'] = $sIssueData;
			$mechData[$index]['scheduleTiming'] = $scheduleTiming;
        }
        return $mechData;
 	}

 	function getProviderPayData($provider_id = ''){
 		if(empty($provider_id)){
 			return array('payedDetails'=>'','pendingDetails'=>'');
 		}
 		$lastPayDate = '';
 		$payedDetails = '';
 		$pendingDetails = '';
 		$data = $this->db->get_where('commission_payment',array('provider_id'=>$provider_id,'status'=>'1'));
 		if(!empty($data) && !empty($payedDetails = $data->row())){
 			$lastPayDate = (!empty($payedDetails->last_payment_date))?$payedDetails->last_payment_date:'';
 		}

 		$cond = (!empty($lastPayDate))?" BOOK.booking_date>='$lastPayDate' AND ":'';
 		$data = $this->db->query("SELECT COUNT(BOOK.id) AS count,SUM(BOOK.amount) AS amount,
				 			      SUM(BOOK.no_of_ticket) AS no_of_ticket 
				 			      FROM booking AS BOOK 
				 				  INNER JOIN events AS EVT ON (EVT.event_id=BOOK.event_id) 
				 				  WHERE EVT.provider_id='$provider_id' AND BOOK.status IN (2) AND $cond
				 					    BOOK.booking_date<='".date('Y-m-d 00:00:00')."'");

	  	$pendingDetails = (!empty($data) && !empty($data->row()))?$data->row():'';
	  	
	  	return array('payedDetails'=>$payedDetails,'pendingDetails'=>$pendingDetails);
 	}

 	function updatePaymentDate($payDate = array()){
 		if(empty($payDate)){
 			return false;
 		}

 		$data = $this->db->get_where('commission_payment',array('provider_id'=>$payDate['provider_id'],
 																'status'=>'1'));
 		if(empty($data) || empty($data->row())){
 			$status = $this->db->insert('commission_payment',$payDate);
 		} else {
 			$status = $this->db->update('commission_payment',$payDate,array('provider_id'=>$payDate['provider_id']));
 		}
 		return ($status)?1:0;
 	}
}
?>