<?
class gatewayModel extends Model{

        function gatewayModel()
        {
                parent::Model();
        }


/**
 ***********************************************************************
 * @Start Global Section  **********************************************
 ***********************************************************************
*/

function getRealIpAddr()
{
    if (!empty($_SERVER['HTTP_CLIENT_IP']))   //check ip from share internet
    {
      $ip=$_SERVER['HTTP_CLIENT_IP'];
    }
    elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR']))   //to check ip is pass from proxy
    {
      $ip=$_SERVER['HTTP_X_FORWARDED_FOR'];
    }
    else
    {
      $ip=$_SERVER['REMOTE_ADDR'];
    }
    return $ip;
}






/**
 ***********************************************************************
 * @Start Trunk Section **********************************************
 ***********************************************************************
*/


/**
 * @author: Md. Golam Rabbany
 * @email: mgrnahid@gmail.com
 * @description: This function return total number of Trunk List
 * @copyright 2011
 */
 
 	function getTotalnumTrunk($description,$ipAddress)
 	{

		if($description=="NULL")
		$descriptionStr = "";
		else
		$descriptionStr = "AND description like 'description%'";

		if($ipAddress=="NULL")
		$ipAddressStr = "";
		else
		$ipAddressStr = "AND providerip like '$ipAddress%'";


	   	$sql="SELECT
	   	count(id_trunk) as totalrows
	   	FROM trunk 		
		Where id_trunk != '-1' $descriptionStr $ipAddressStr";

		$row = 0;
		$query = $this->db->query($sql);
		foreach($query->result() as $resultrow)
		{
			$row = $resultrow->totalrows;
		}
		return $row;

	}
	

/**
 * @author: Md. Golam Rabbany
 * @email: mgrnahid@gmail.com
 * @description: This function return Gateway List
 * @copyright 2011
 */
	function trunkList($startoffset,$limit,$description,$ipAddress)
	{
		$new_startoffset = $startoffset - 1;
		if($new_startoffset <0)
		$new_startoffset = 0;
		$startoffset1 = $limit * $new_startoffset;
	    $limit_string = "LIMIT $startoffset1,$limit";


		if($description=="NULL")
		$descriptionStr = "";
		else
		$descriptionStr = "AND description like '$description%'";

		if($ipAddress=="NULL")
		$ipAddressStr = "";
		else
		$ipAddressStr = "AND providerip like '$ipAddress%'";

        $sql = "SELECT
				id_trunk,
				description,
				providertech,
				providerip,
				port,
				inuse,
				maxuse,
				status
				
				FROM trunk
				where id_trunk !=-1  $descriptionStr $ipAddressStr order by description ASC $limit_string";


	    $query = $this->db->query($sql);
	    if($query->num_rows()>0)
		  return $query->result();
	    else
		 return 0;
	}


/**
 * @author: Md. Golam Rabbany
 * @email: mgrnahid@gmail.com
 * @description: This function Add Turnk
 * @copyright 2011
 */

 function addTrunk()
 {


		//$data['id_trunk']		= $this->input->post('id_trunk');
		$data['description']		= $this->input->post('description');
		$data['id_rate']		= $this->input->post('id_rate');	
		$data['providertech']		= $this->input->post('providertech');
		$data['providerip']		= $this->input->post('providerip');
		$data['inuse']		= $this->input->post('inuse');
		$data['maxuse']		= $this->input->post('maxuse');
		$data['if_max_use']		= $this->input->post('if_max_use');
		$data['disallow_loss']		= $this->input->post('disallow_loss');
		$data['status']		= $this->input->post('status');

		$sql = $this->db->insert_string('trunk', $data);
		$query = $this->db->query($sql);
		$idInsert = $this->db->insert_id();
 }
 
 
 
 
 /**
 * @author: Md. Golam Rabbany
 * @email: mgrnahid@gmail.com
 * @description: This function Return Trunk Infomation
 * @copyright 2011
 */

	function trunkEditInfo($id_trunk)
	{
    	$query = $this->db->query("select *from trunk where id_trunk = '$id_trunk' LIMIT 1");
    	if($query->num_rows()>0)
		{
			return $query->result();
		}
    	else
		return 0;
	}


/**
 * @author: Md. Golam Rabbany
 * @email: mgrnahid@gmail.com
 * @description: This function Edit Trunk Information 
 * @copyright 2011
 */

 function editTrunk()
 {

		$keyvalue		= $this->input->post('id_trunk');
		$data['description']		= $this->input->post('description');
		$data['id_rate']		= $this->input->post('id_rate');	
		$data['providertech']		= $this->input->post('providertech');
		$data['providerip']		= $this->input->post('providerip');
		$data['inuse']		= $this->input->post('inuse');
		$data['maxuse']		= $this->input->post('maxuse');
		$data['if_max_use']		= $this->input->post('if_max_use');
		$data['disallow_loss']		= $this->input->post('disallow_loss');
		$data['status']		= $this->input->post('status');

        $where = "id_trunk = $keyvalue";
		$sql = $this->db->update_string('trunk', $data, $where);
		$query = $this->db->query($sql);
		
 }
 


 /**
 * @author: Md. Golam Rabbany
 * @email: mgrnahid@gmail.com
 * @description: This function Delete Trunk Information
 * @copyright 2011
 */

	function deleteTrunk($id_trunk)
	{
 		$query = $this->db->query("delete from dialplan where id_trunk = '$id_trunk'");
    	$query = $this->db->query("delete from trunk where id_trunk = '$id_trunk'");
	}







/**
 ***********************************************************************
 * @Start Dial Plan  Section *******************************************
 ***********************************************************************
*/


/**
 * @author: Md. Golam Rabbany
 * @email: mgrnahid@gmail.com
 * @description: This function return total number of Dial Plan
 * @copyright 2011
 */
 
  
 	function getTotalnumDialPlan($idTrunk,$perfix,$routeType = 0)
 	{
		if($perfix=="NULL")
		$perfixStr = "";
		else
		$perfixStr = "AND prefix like '$perfix%'";

		if($idTrunk=="NULL")
		$idTrunkStr = "";
		else
		$idTrunkStr = "AND id_trunk = '$idTrunk'";

	   	$sql="SELECT
	   	count(id) as totalrows
	   	FROM dialplan	
		Where id != '-1' AND route_type = '$routeType' $perfixStr $idTrunkStr";

		$row = 0;
		$query = $this->db->query($sql);
		foreach($query->result() as $resultrow)
		{
			$row = $resultrow->totalrows;
		}
		return $row;

	}


/**
 * @author: Md. Golam Rabbany
 * @email: mgrnahid@gmail.com
 * @description: This function return Provider List
 * @copyright 2011
 */
	function dialPlanList($startoffset,$limit,$idTrunk,$perfix,$routeType = 0)
	{

		$new_startoffset = $startoffset - 1;
		if($new_startoffset <0)
		$new_startoffset = 0;
		$startoffset1 = $limit * $new_startoffset;
	    $limit_string = "LIMIT $startoffset1,$limit";

		if($perfix=="NULL")
		$perfixStr = "";
		else
		$perfixStr = "AND a.prefix like '$perfix%'";

		if($idTrunk=="NULL")
		$idTrunkStr = "";
		else
		$idTrunkStr = "AND a.id_trunk = '$idTrunk'";

        $sql = "SELECT
				a.id,
				a.id_trunk,
				a.prefix,
				a.add_prefix,
				a.remove_prefix,
				a.priority,
				a.jump,
				a.share,
				a.from_day,
				a.to_day,
				a.from_hour,
				a.to_hour,
				a.route_type,
				a.status,
				b.description,
				b.providertech,
				b.providerip,
				b.status
				
				FROM dialplan a
				INNER JOIN trunk b
				ON a.id_trunk = b.id_trunk	
				where a.id !=-1 AND a.route_type = '$routeType' $idTrunkStr $perfixStr  order by  a.prefix ASC,a.priority ASC $limit_string";

         //echo $sql;
	    $query = $this->db->query($sql);
	    if($query->num_rows()>0)
		  return $query->result();
	    else
		 return 0;
	}


/**
 * @author: Md. Golam Rabbany
 * @email: mgrnahid@gmail.com
 * @description: This function Add Dial Plan
 * @copyright 2011
 */

 	function addDialPlan()
	 {
		//$data['id']		= $this->input->post('id');
		$data['id_trunk']		= $this->input->post('id_trunk');
		$data['prefix']		= $this->input->post('prefix');
		$data['add_prefix']		= $this->input->post('add_prefix');
		$data['remove_prefix']		= $this->input->post('remove_prefix');
		$data['priority']		= $this->input->post('priority');
		$data['jump']		= $this->input->post('jump');
		$data['share']		= $this->input->post('share');
		$data['from_day']		= $this->input->post('from_day');
		$data['to_day']		= $this->input->post('to_day');
		$data['from_hour']		= $this->input->post('from_hour');
		$data['to_hour']		= $this->input->post('to_hour');
		$data['route_type']		= $this->input->post('routeType');
		$data['status']		= $this->input->post('status');
		$sql = $this->db->insert_string('dialplan', $data);
		$query = $this->db->query($sql);
		$idInsert = $this->db->insert_id();
 	}
 
 
 
  /**
 * @author: Md. Golam Rabbany
 * @email: mgrnahid@gmail.com
 * @description: This function Return Trunk Infomation
 * @copyright 2011
 */

	function dialPlanEditInfo($id)
	{
    	$query = $this->db->query("select *from dialplan where id = '$id' LIMIT 1");
    	if($query->num_rows()>0)
		{
			return $query->result();
		}
    	else
		return 0;
	}
	


/**
 * @author: Md. Golam Rabbany
 * @email: mgrnahid@gmail.com
 * @description: This function Edit Dial Plan
 * @copyright 2011
 */
  
 	function editDialPlan()
	 {


		$keyvalue		= $this->input->post('id');
		//$data['id']		= $this->input->post('id');
		$data['id_trunk']		= $this->input->post('id_trunk');
		$data['prefix']		= $this->input->post('prefix');
		$data['add_prefix']		= $this->input->post('add_prefix');
		$data['remove_prefix']		= $this->input->post('remove_prefix');
		$data['priority']		= $this->input->post('priority');
		$data['jump']		= $this->input->post('jump');
		$data['share']		= $this->input->post('share');
		$data['from_day']		= $this->input->post('from_day');
		$data['to_day']		= $this->input->post('to_day');
		$data['from_hour']		= $this->input->post('from_hour');
		$data['to_hour']		= $this->input->post('to_hour');
		$data['route_type']		= $this->input->post('routeType');
		$data['status']		= $this->input->post('status');

        $where = "id = $keyvalue";
		$sql = $this->db->update_string('dialplan', $data, $where);
		$query = $this->db->query($sql);

 	}


 /**
 * @author: Md. Golam Rabbany
 * @email: mgrnahid@gmail.com
 * @description: This function Delete Dial Plan
 * @copyright 2011
 */

	function deleteDialPlan($id)
	{
    	$query = $this->db->query("delete from dialplan where id = '$id'");
	}
		 
		 
		 



/**
 ***********************************************************************
 * @Start Gatway Section *********************************************
 ***********************************************************************
*/



/**
 * @author: Md. Golam Rabbany
 * @email: mgrnahid@gmail.com
 * @description: This function return total number of Gateway
 * @copyright 2011
 */
 
 	function getTotalnumGateway($full_name,$description,$id_rate,$ip_address,$phone)
 	{
		if($description=="NULL")
		$descriptionStr = "";
		else
		$descriptionStr = "AND description like '$description%'";

		if($full_name=="NULL")
		$fullNameStr = "";
		else
		$fullNameStr = "AND full_name LIKE '$full_name%'";

		if($id_rate=="NULL")
		$rateStr = "";
		else
		$rateStr = "AND id_rate = '$id_rate'";

		if($ip_address=="NULL")
		$ipAddressStr = "";
		else
		$ipAddressStr = "AND ip_address LIKE '$ip_address%'";

		if($phone=="NULL")
		$phoneStr = "";
		else
		$phoneStr = "AND phone LIKE '$phone%'";


	   	$sql="SELECT
	   	count(id_route) as totalrows
	   	FROM gateways Where id_route != '-1' $descriptionStr $fullNameStr $rateStr $ipAddressStr $phoneStr";
		$row = 0;
		$query = $this->db->query($sql);
		foreach($query->result() as $resultrow)
		{
			$row = $resultrow->totalrows;
		}
		return $row;

	}
	

/**
 * @author: Md. Golam Rabbany
 * @email: mgrnahid@gmail.com
 * @description: This function return Gateway List
 * @copyright 2011
 */
	function gatewayList($startoffset,$limit,$full_name,$description,$id_rate,$ip_address,$phone)
	{

		$new_startoffset = $startoffset - 1;
		if($new_startoffset <0)
		$new_startoffset = 0;
		$startoffset1 = $limit * $new_startoffset;
	    $limit_string = "LIMIT $startoffset1,$limit";

		if($description=="NULL")
		$descriptionStr = "";
		else
		$descriptionStr = "AND description like '$description%'";

		if($full_name=="NULL")
		$fullNameStr = "";
		else
		$fullNameStr = "AND full_name LIKE '$full_name%'";

		if($id_rate=="NULL")
		$rateStr = "";
		else
		$rateStr = "AND id_rate = '$id_rate'";

		if($ip_address=="NULL")
		$ipAddressStr = "";
		else
		$ipAddressStr = "AND ip_address LIKE '$ip_address%'";

		if($phone=="NULL")
		$phoneStr = "";
		else
		$phoneStr = "AND phone LIKE '$phone%'";


        $sql = "SELECT * FROM gateways
				 Where id_route != '-1' $descriptionStr $fullNameStr $rateStr $ipAddressStr $phoneStr order by description ASC $limit_string";

			   //echo $sql;

	    $query = $this->db->query($sql);
	    if($query->num_rows()>0)
		  return $query->result();
	    else
		 return 0;
	}
	

/**
 * @author: Md. Golam Rabbany
 * @email: mgrnahid@gmail.com
 * @description: This function Add Provider Name
 * @copyright 2011
 */

 function addProvider()
 {

		//$data['id']		= $this->input->post('id');
		$data['provider_name']		= $this->input->post('provider_name');
		$data['description']		= $this->input->post('description');
		$data['contact_name']		= $this->input->post('contact_name');
		$data['contact_email']		= $this->input->post('contact_email');
		$data['contact_phone']		= $this->input->post('contact_phone');
		$data['office_address']		= $this->input->post('office_address');
		$data['office_phone']		= $this->input->post('office_phone');
		$data['office_email']		= $this->input->post('office_email');
		$data['office_country']		= $this->input->post('office_country');

		$sql = $this->db->insert_string('gatwayprovider', $data);
		$query = $this->db->query($sql);
		$idInsert = $this->db->insert_id();
 }
 	


 /**
 * @author: Md. Golam Rabbany
 * @email: mgrnahid@gmail.com
 * @description: This function Return Edit Infomation of Provider
 * @copyright 2011
 */

	function providerEditInfo($id)
	{
    	$query = $this->db->query("select *from gatwayprovider where id = '$id' LIMIT 1");
    	if($query->num_rows()>0)
		{
			return $query->result();
		}
    	else
		return 0;
	}



  /**
 * @author: Md. Golam Rabbany
 * @email: mgrnahid@gmail.com
 * @description: This function Edit Rate Chart Name
 * @copyright 2011
 */

 function editProvider()
 {

		$keyvalue		= $this->input->post('id');
		$data['provider_name']		= $this->input->post('provider_name');
		$data['description']		= $this->input->post('description');
		$data['contact_name']		= $this->input->post('contact_name');
		$data['contact_email']		= $this->input->post('contact_email');
		$data['contact_phone']		= $this->input->post('contact_phone');
		$data['office_address']		= $this->input->post('office_address');
		$data['office_phone']		= $this->input->post('office_phone');
		$data['office_email']		= $this->input->post('office_email');
		$data['office_country']		= $this->input->post('office_country');
        $where = "id = $keyvalue";
		$sql = $this->db->update_string('gatwayprovider', $data, $where);
		$query = $this->db->query($sql);
		
 }



/**
 * @author: Md. Golam Rabbany
 * @email: mgrnahid@gmail.com
 * @description: Find check turnk information of provider
 * @copyright 2011
 */


function checkProviderTurnk($id)
{
    $query = $this->db->query("select *from trunk where id_provider = '$id'");

    if($query->num_rows()>0)
	{
		return 1;
	}
    else
	return 0;
}



/**
 * @author: Md. Golam Rabbany
 * @email: mgrnahid@gmail.com
 * @description: Delete information of Provider Name
 * @copyright 2011
 */


function deleteProvider($id)
{
    $query = $this->db->query("delete from gatwayprovider where id = '$id'");

}



	function getTrunkRateID($idTrunk)
	{
 		$query = $this->db->query("SELECT id_rate FROM trunk WHERE id_trunk = '$idTrunk'");
		if ($query->num_rows() > 0)
	 	{
	 		$row = $query->row();
	  		return $row->id_rate;
	 	}
	 	else
	 	return 0;
	}
	



/**
 * @author: Md. Golam Rabbany
 * @email: mgrnahid@gmail.com
 * @description: This function Add Dial Plan
 * @copyright 2011
 */

 	function addLCRDialPlan()
	{
		$prefixListArray = array();
		$ratePrefix = array();
		$ratePrefixValue = array();
		$trunkIDArray = array();
		
		//$data['id']		= $this->input->post('id');
		$idTrunks		= $this->input->post('idTrunks');
		$prefix		= $this->input->post('prefix');
		$dataRoute['add_prefix']		= $this->input->post('add_prefix');
		$dataRoute['remove_prefix']		= $this->input->post('remove_prefix');
		$dataRoute['from_day']		= $this->input->post('from_day');
		$dataRoute['to_day']		= $this->input->post('to_day');
		$dataRoute['from_hour']		= $this->input->post('from_hour');
		$dataRoute['to_hour']		= $this->input->post('to_hour');
		$dataRoute['status']		= $this->input->post('status');
		
		$idRate = "";
		if(is_array($idTrunks))
		{
			foreach($idTrunks as $rowID)
			{
				$trunkID = $rowID;
				if($trunkID>0)
				{	
					$trunkRateID = $this->getTrunkRateID($trunkID);
					$trunkIDArray[$trunkRateID] = $trunkID;					
					$idRate .= $trunkRateID.",";
				}	
			}
		}
		
		$rateIDs = substr($idRate,0,-1);
		
		$rateList = $this->getLcrRatePrefixList($prefix,$rateIDs);
		$prefixList = $this->getLCRprefixList($prefix,$rateIDs);
	
		if(is_array($prefixList))
		{
			foreach($prefixList as $prefixRow)
			{
				$prefixListArray[$prefixRow->prefix] = $prefixRow->prefix;
			}
		}
		
		
		if(is_array($rateList))
		{
			foreach($rateList as $rpRow)
			{
				$ratePrefix[$rpRow->id_rate][$rpRow->prefix] = $rpRow->voice_rate;
				$ratePrefixValue[$rpRow->prefix][$rpRow->voice_rate] = $rpRow->id_rate;
			}
		}
		
		
		
		if(is_array($prefixList))
		{
			foreach($prefixList as $row)
			{
				$rateIDsArray = explode(",",$rateIDs);
				if(is_array($rateIDsArray))
				{
					$priority = 1;
					foreach($rateIDsArray as $rateID)
					{	
						if(isset($ratePrefix[$rateID][$row->prefix]))
						{
							$arrayRateValue[] = array('rateID' => $rateID, 'voiceRate' => $ratePrefix[$rateID][$row->prefix],'billPrefix'=>$row->prefix);
							$arrayVoiceRateValue[] = array('routeRate'=>$ratePrefix[$rateID][$row->prefix],'billPrefix'=>$row->prefix,'rateID' => $rateID,'trunkID' => $trunkIDArray[$rateID], 'priority' => $priority);
						}
						else
						{
							$searchPrefixLen = strlen($searchPrefix);
							$resultPrefixLen = strlen($row->prefix);
							$diffLen = $resultPrefixLen - $searchPrefixLen;
							for ($kk = 1; $kk<=$diffLen;$kk++)
							{
								$ratingPrefix = substr($row->prefix,0,-$kk);
								if(isset($ratePrefix[$rateID][$ratingPrefix]))
								{
									$arrayRateValue[] = array('rateID' => $rateID, 'voiceRate' => $ratePrefix[$rateID][$ratingPrefix],'billPrefix'=>$ratingPrefix);
									//$arrayVoiceRateValue[] = $ratePrefix[$rateID][$ratingPrefix];
									$arrayVoiceRateValue[] = array('routeRate'=>$ratePrefix[$rateID][$row->prefix],'billPrefix'=>$ratingPrefix,'rateID' => $rateID, 'trunkID'=>$trunkIDArray[$rateID], 'priority' => $priority);
									$kk = $diffLen + 1;
								}
							}
						}
						$priority = $priority + 1;
					}	
				}
			}
			
			$arraySortRateKey = array();
			foreach ($arrayRateValue as $rateValue)
			{
				$arraySortRateKey[] = $rateValue['voiceRate'];
			}
			array_multisort($arraySortRateKey, SORT_ASC, $arrayRateValue);
			
			
			
			if(is_array($arrayVoiceRateValue))
			{
				foreach ($arrayVoiceRateValue as $routeRow)
				{
					$routePrefix = $routeRow['billPrefix'];
					$routeTrunkID = $routeRow['trunkID'];
					$routePriority = $routeRow['priority'];
					$routeRate = $routeRow['routeRate'];
					
					$dataRoute['id_trunk']		= $routeTrunkID;
					$dataRoute['voice_rate']		= $routeRate;
					$dataRoute['prefix']		= $routePrefix;
					$dataRoute['priority']		= $routePriority;
					$dataRoute['jump']		= 1;
					$dataRoute['share']		= 100;
					$dataRoute['route_type']		= 1;
					
					// Delete Dial Plan
					
					$sqlDialPlan = "DELETE FROM dialplan WHERE id_trunk = '$routeTrunkID' AND prefix = '$routePrefix' AND route_type = 1";
					$queryDelete = $this->db->query($sqlDialPlan);
					$sql = $this->db->insert_string('dialplan', $dataRoute);
					$query = $this->db->query($sql);
					$idInsert = $this->db->insert_id();
		
				}
			}
			//echo "<pre>";
			//echo print_r($arrayVoiceRateValue);
			//echo "</pre>";		
		}				
 	}
 	
 
 
 /**
 * @author: Md. Golam Rabbany
 * @email: mgrnahid@gmail.com
 * @description: This function Return Priefix & Rate List
 * @copyright 02 Jun 2014
 */
		
	function getLcrRatePrefixList($prefix,$rateIDs)
	{
		$sql = "SELECT 
				id_rate,
				prefix,
				voice_rate 
				FROM ratechart 
				WHERE prefix like '$prefix%' AND id_rate IN($rateIDs)
				group by id_rate,prefix,voice_rate order by prefix ASC,voice_rate ASC";
				
		$query = $this->db->query($sql);
    	if($query->num_rows()>0)
		{
			return $query->result();
		}
    	else
		return 0;
	}
	


/**
 * @author: Md. Golam Rabbany
 * @email: mgrnahid@gmail.com
 * @description: This function Return Priefix & Rate List
 * @copyright 02 Jun 2014
 */


		//id, id_rate, prefix, description, voice_rate, rate_mult, grace_period, minimal_time, resolution, is_disable, creationdate, user_id, usertype, ipaddress


	function getLCRprefixList($prefix,$rateIDs)
	{
		$sql = "SELECT 
				prefix,
				description 
				FROM ratechart 
				WHERE prefix LIKE '$prefix%' AND id_rate IN($rateIDs)
				group by prefix order by prefix ASC";
				
		$query = $this->db->query($sql);
    	if($query->num_rows()>0)
		{
			return $query->result();
		}
    	else
		return 0;
	}
	
			
	
		
		
			

}

?>