<?
class callModel extends Model{

        function callModel()
        {
                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 Active Call Section ******************************************
 ***********************************************************************
*/


/**
 * @author: Md. Golam Rabbany
 * @email: mgrnahid@gmail.com
 * @description: This function return total number of Sucess Call
 * @copyright 2011
 */
 
 	function getTotalnumActiveCall($id_trunk,$calledNumnber,$clientName,$id_rate,$clientType,$id_reseller)
 	{
		if($id_trunk=="NULL")
		$trunkStr = "";
		else
		$trunkStr = "AND a.id_trunk = '$id_trunk'";

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

		if($calledNumnber=="NULL")
		$numberStr = "";
		else
		$numberStr = "AND a.dialnumber LIKE '$calledNumnber%'";

		if($clientName=="NULL")
		$clientNameStr = "";
		else
		$clientNameStr = "AND a.caller_id LIKE '$clientName%'";

		if($clientType=="NULL")
		$clientTypeStr = "";
		else
		$clientTypeStr = "AND a.clienttype = '$clientType'";

		if($id_reseller=="NULL")
		$idResellerStr = "";
		else
		$idResellerStr = "AND a.id_reseller = '$id_reseller'";


        $sql = "SELECT
                count(a.id) as totalrows
 								               
				FROM activecall a

				INNER JOIN trunk b
		        ON a.id_trunk = b.id_trunk	

				INNER JOIN ratename c
		        ON a.id_rate = c.id_rate	
				
				Where a.id != '-1' $trunkStr $rateStr $numberStr $clientNameStr $clientTypeStr $idResellerStr";
	   	
		   //echo $sql;
		$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 Active Call List
 * @copyright 2011
 */
	function activeCallList($startoffset,$limit,$id_trunk,$calledNumnber,$clientName,$id_rate,$clientType,$id_reseller)
	{

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

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

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

		if($calledNumnber=="NULL")
		$numberStr = "";
		else
		$numberStr = "AND a.dialnumber LIKE '$calledNumnber%'";

		if($clientName=="NULL")
		$clientNameStr = "";
		else
		$clientNameStr = "AND a.caller_id LIKE '$clientName%'";

		if($clientType=="NULL")
		$clientTypeStr = "";
		else
		$clientTypeStr = "AND a.clienttype = '$clientType'";

		if($id_reseller=="NULL")
		$idResellerStr = "";
		else
		$idResellerStr = "AND a.id_reseller = '$id_reseller'";


        $sql = "SELECT
                a.id,
                a.client_id,
                a.id_trunk,
                a.id_reseller,
                a.id_rate,
                a.dialnumber,
                a.dialprefix,
                a.billing_prefix,
                a.billing_prefix,
                a.call_start,
		  TIMESTAMPDIFF(SECOND,a.call_start,NOW()) AS duration,
                a.caller_id as pinnumber,
                a.status,
                b.description as trunkName,
                c.description as rateName
 								               
				FROM activecall a

				INNER JOIN trunk b
		        ON a.id_trunk = b.id_trunk	

				INNER JOIN ratename c
		        ON a.id_rate = c.id_rate	
				
				 Where a.id != '-1' $trunkStr $rateStr $numberStr $clientNameStr $clientTypeStr  $idResellerStr order by a.call_start DESC $limit_string";

			   //echo $sql;

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



/**
 ***********************************************************************
 * @Start Sucess Call Section ******************************************
 ***********************************************************************
*/



/**
 * @author: Md. Golam Rabbany
 * @email: mgrnahid@gmail.com
 * @description: This function return total number of Sucess Call
 * @copyright 2011
 */
 
 	function getTotalnumSucessCall($starttime,$stoptime,$id_trunk,$calledNumnber,$callerID,$clientName,$id_rate,$callType)
 	{
		if($id_trunk=="NULL")
		$trunkStr = "";
		else
		$trunkStr = "AND a.id_trunk = '$id_trunk'";

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

		if($calledNumnber=="NULL")
		$numberStr = "";
		else
		$numberStr = "AND a.callednumber LIKE '$calledNumnber%'";

		if($callerID=="NULL")
		$calerIDStr = "";
		else
		$calerIDStr = "AND a.login LIKE '$callerID%'";

		if($clientName=="NULL")
		$clientNameStr = "";
		else
		$clientNameStr = "AND a.login LIKE '$clientName%'";

		if($callType==0)
		$callTypeStr = "AND a.duration=0";
		else
		$callTypeStr = "AND a.duration>0";
		
		
        $sql = "SELECT
                count(a.id_call) as totalrows
				               
				FROM calls a

				INNER JOIN trunk b
		        ON a.id_trunk = b.id_trunk	

				INNER JOIN ratename c
		        ON a.id_tariff = c.id_rate	

			
				 Where a.id_call != '-1' AND a.callstart between '$starttime 00:00:00' AND '$stoptime 23:59:59' $trunkStr $rateStr $numberStr $calerIDStr $clientNameStr $callTypeStr";
				 
	   	//echo $sql;
		$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 sucessCallList($startoffset,$limit,$starttime,$stoptime,$id_trunk,$calledNumnber,$callerID,$clientName,$id_rate,$callType)
	{

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

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

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

		if($calledNumnber=="NULL")
		$numberStr = "";
		else
		$numberStr = "AND a.callednumber LIKE '$calledNumnber%'";

		if($callerID=="NULL")
		$calerIDStr = "";
		else
		$calerIDStr = "AND a.login LIKE '$callerID%'";

		if($clientName=="NULL")
		$clientNameStr = "";
		else
		$clientNameStr = "AND a.login LIKE '$clientName%'";

		if($callType==0)
		$callTypeStr = "AND a.duration=0";
		else
		$callTypeStr = "AND a.duration>0";
		


        $sql = "SELECT
                a.id_call as id,
                a.id_client,
                a.callstart as starttime,
                a.callend as stoptime,
                a.duration as real_sessiontime,
                a.id_trunk,
                a.login as src,
                a.login as pinnumber,
                a.callednumber as dnid,
                a.id_reseller,
                a.id_tariff as id_rate,
                a.tariff_prefix as billing_prefix,
                a.costD as client_cost,
                a.costR1 as costr1,
                a.costR2 as costr2,
                a.costR3 as costr3,
                a.costR4 as costr4,
                a.dialing_prefix,
                a.call_status,
                b.description as trunkName,
                c.description as rateName
                
				               
				FROM calls a

				INNER JOIN trunk b
		        ON a.id_trunk = b.id_trunk	

				INNER JOIN ratename c
		        ON a.id_tariff = c.id_rate	

			
				 Where a.id_call != '-1' AND a.callstart between '$starttime 00:00:00' AND '$stoptime 23:59:59' $trunkStr $rateStr $numberStr $calerIDStr $clientNameStr $callTypeStr order by a.callend DESC $limit_string";

			   //echo $sql;

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




/**
 ***********************************************************************
 * @Start Call Summary Section *****************************************
 ***********************************************************************
*/


/**
 * @author: Md. Golam Rabbany
 * @email: mgrnahid@gmail.com
 * @description: This function return Sucess Call List
 * @copyright 2011
 */
	function sucessCallSummary($startoffset,$limit,$callStart,$callEnd,$calledPrefix,$idTrunk)
	{

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


		if($calledPrefix=="NULL")
		$calledPrefixStr = "";
		else
		$calledPrefixStr = "AND a.callednumber LIKE '$calledPrefix%'";

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



        $sql = "SELECT
                substring(a.callend,1,10) as callDay,
                SUM(a.duration) as callDuration,
                COUNT(a.id_call) as totalCall,
                a.tariff_prefix,
                b.description
   
				FROM calls a

				INNER JOIN trunk b
		        ON a.id_trunk  = b.id_trunk 	
				
				Where a.id_call != '-1' AND a.duration > 0 AND a.callend BETWEEN '$callStart 00:00:00' AND '$callEnd 23:59:59'  $calledPrefixStr   $idTrunkStr  GROUP BY callDay, a.tariff_prefix,a.id_trunk order by a.id_trunk DESC,a.tariff_prefix DESC,callDay DESC";
												               

			   //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 return Sucess Call List
 * @copyright 2011
 */
	function wholeSaleCallSummary($callStart,$callEnd,$calledPrefix,$idTrunk,$clientID)
	{


		if($calledPrefix=="NULL")
		$calledPrefixStr = "";
		else
		$calledPrefixStr = "AND callednumber LIKE '$calledPrefix%'";

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

		if($clientID=="NULL")
		$clientIDStr = "";
		else
		$clientIDStr = "AND id_client = '$clientID'";
		
        $sql = "SELECT
                substring(callend,1,10) as callDay,
                SUM(duration) as callDuration,
                COUNT(id_call) as totalCall,
                tariff_prefix,
                id_client,
                id_trunk,
                login
				FROM calls
				WHERE clienttype = 4 AND duration > 0 
				AND callend BETWEEN '$callStart 00:00:00' AND '$callEnd 23:59:59'
				$calledPrefixStr $idTrunkStr $clientIDStr
				GROUP BY callDay, tariff_prefix, id_client, id_trunk 
				ORDER BY callDay DESC";
												               

			   //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 return Sucess Call List
 * @copyright 2011
 */
	function wholeSaleProfitCallSummary($callStart,$callEnd,$calledPrefix,$idTrunk,$clientID)
	{


		if($calledPrefix=="NULL")
		$calledPrefixStr = "";
		else
		$calledPrefixStr = "AND callednumber LIKE '$calledPrefix%'";

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

		if($clientID=="NULL")
		$clientIDStr = "";
		else
		$clientIDStr = "AND id_client = '$clientID'";
		
        $sql = "SELECT
                substring(callend,1,10) as callDay,
                SUM(duration) as callDuration,
                COUNT(id_call) as totalCall,
                tariff_prefix,
                id_client,
                id_trunk,
                login,
                call_rate,
                trunk_rate
				FROM calls
				WHERE clienttype = 4 AND duration > 0 
				AND callend BETWEEN '$callStart 00:00:00' AND '$callEnd 23:59:59'
				$calledPrefixStr $idTrunkStr $clientIDStr
				GROUP BY callDay, tariff_prefix, id_client, id_trunk, call_rate, trunk_rate 
				ORDER BY callDay DESC";
												               

			   //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 return Sucess Call List
 * @copyright 2011
 */
	function retailCallSummary($callStart,$callEnd,$calledPrefix,$idTrunk,$clientName)
	{


		if($calledPrefix=="NULL")
		$calledPrefixStr = "";
		else
		$calledPrefixStr = "AND callednumber LIKE '$calledPrefix%'";

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

		if($clientName=="NULL")
		$clientNameStr = "";
		else
		$clientNameStr = "AND login = '$clientName'";
		
        $sql = "SELECT
                substring(callend,1,10) as callDay,
                SUM(duration) as callDuration,
                COUNT(id_call) as totalCall,
                tariff_prefix,
                id_trunk,
                login
				FROM calls
				WHERE clienttype = 1 AND duration > 0 
				AND callend BETWEEN '$callStart 00:00:00' AND '$callEnd 23:59:59'
				$calledPrefixStr $idTrunkStr $clientNameStr
				GROUP BY callDay, tariff_prefix, id_trunk 
				ORDER BY callDay DESC";
												               

			   //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 return Sucess Call List
 * @copyright 2011
 */
	function callingCardCallSummary($callStart,$callEnd,$calledPrefix,$idTrunk,$clientName)
	{


		if($calledPrefix=="NULL")
		$calledPrefixStr = "";
		else
		$calledPrefixStr = "AND callednumber LIKE '$calledPrefix%'";

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

		if($clientName=="NULL")
		$clientNameStr = "";
		else
		$clientNameStr = "AND login = '$clientName'";
		
        $sql = "SELECT
                substring(callend,1,10) as callDay,
                SUM(duration) as callDuration,
                COUNT(id_call) as totalCall,
                tariff_prefix,
                id_trunk,
                login
				FROM calls
				WHERE clienttype = 3 AND duration > 0 
				AND callend BETWEEN '$callStart 00:00:00' AND '$callEnd 23:59:59'
				$calledPrefixStr $idTrunkStr $clientNameStr
				GROUP BY callDay, tariff_prefix, id_trunk 
				ORDER BY callDay DESC";
												               

			   //echo $sql;

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

/**
 ***********************************************************************
 * @Start ASR/ACD Call Section *****************************************
 ***********************************************************************
*/





/**
 * @author: Md. Golam Rabbany
 * @email: mgrnahid@gmail.com
 * @description: This function return Provider List
 * @copyright 2011
 */
	function sucessAsrAcdCallList($startoffset,$limit,$starttime,$stoptime,$id_trunk,$prefix)
	{

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

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


		if($prefix=="NULL")
		$numberStr = "";
		else
		$numberStr = "AND a.callednumber LIKE '$prefix%'";


        $sql = "SELECT
                substring(a.callend,1,13) as callHours,
                SUM(a.duration) as sucessCallDuration,
                COUNT(a.id_call) as totalCall
				               
				FROM calls a

				INNER JOIN trunk b
		        ON a.id_trunk = b.id_trunk	
				
				Where a.id_call != '-1' AND a.callend between '$starttime 00:00:00' AND '$stoptime 23:59:59' $trunkStr $numberStr GROUP BY callHours order by callHours DESC";

			   //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 return total number of Reseller Sucess Call
 * @copyright 2011
 */
 
 	function getTotalnumResellerSucessCall($starttime,$stoptime,$calledNumnber,$login,$id_rate,$level)
 	{
		
		if($level=='NULL')
		$level = 3;        
        if($level==3)
        {
			
			echo "";
		}
        
        
		
		if($id_trunk=="NULL")
		$trunkStr = "";
		else
		$trunkStr = "AND a.id_trunk = '$id_trunk'";

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

		if($calledNumnber=="NULL")
		$numberStr = "";
		else
		$numberStr = "AND a.dnid LIKE '$calledNumnber%'";

		if($callerID=="NULL")
		$calerIDStr = "";
		else
		$calerIDStr = "AND a.src LIKE '$callerID%'";

		if($clientName=="NULL")
		$clientNameStr = "";
		else
		$clientNameStr = "AND d.username LIKE '$clientName%'";

        $sql = "SELECT
                count(a.id) as totalrows
				               
				FROM cc_call a

				INNER JOIN bb_trunk b
		        ON a.id_trunk = b.id_trunk	

				INNER JOIN bb_ratename c
		        ON a.id_rate = c.id_rate	

				INNER JOIN cc_card d
		        ON a.card_id = d.id	

			
				 Where a.id != '-1' AND a.starttime between '$starttime 00:00:00' AND '$stoptime 23:59:59' $trunkStr $rateStr $numberStr $calerIDStr $clientNameStr";
				 
	   	//echo $sql;
		$row = 0;
		$query = $this->db->query($sql);
		foreach($query->result() as $resultrow)
		{
			$row = $resultrow->totalrows;
		}
		return $row;

	}
	
		
		
			

}

?>