Ticket #4 (closed defect: worksforme)

Opened 2 years ago

Last modified 2 years ago

Rate Simulator Error

Reported by: crshman Assigned to: areski
Priority: major Milestone:
Component: Customer UI Version: Branch 1.3.x
Keywords: Simulator Cc:

Description

There seems to be some issues with the rate simulator in the Customer UI.

I am using the MySQL Database.

The rate simulator does not return any rates, the error has been traced to this sql query within: 

function rate_engine_findrates (&$A2B, $phonenumber, $tariffgroupid)

$QUERY = "SELECT tariffgroupname, lcrtype, idtariffgroup, cc_tariffgroup_plan.idtariffplan, tariffname, destination,
		
		cc_ratecard.id,	dialprefix, destination, buyrate, buyrateinitblock, buyrateincrement, rateinitial, initblock, billingblock, 
		connectcharge, disconnectcharge, stepchargea, chargea, timechargea, billingblocka, stepchargeb, chargeb, 
		timechargeb, billingblockb, stepchargec, chargec, timechargec, billingblockc,
		cc_tariffplan.id_trunk AS tp_id_trunk, tp_trunk.trunkprefix AS tp_trunk, tp_trunk.providertech AS tp_providertech, 
		tp_trunk.providerip AS tp_providerip, tp_trunk.removeprefix AS tp_removeprefix,
		cc_ratecard.id_trunk AS rc_id_trunk, rt_trunk.trunkprefix AS rc_trunkprefix, rt_trunk.providertech AS rc_providertech, 
		rt_trunk.providerip AS rc_providerip, rt_trunk.removeprefix AS rc_removeprefix,
		musiconhold,
		tp_trunk.failover_trunk AS tp_failover_trunk,
		rt_trunk.failover_trunk AS rt_failover_trunk,
		tp_trunk.addparameter AS tp_addparameter_trunk,
		rt_trunk.addparameter AS rt_addparameter_trunk,
		id_outbound_cidgroup
		
		
		FROM cc_tariffgroup 
		RIGHT JOIN cc_tariffgroup_plan ON cc_tariffgroup.id=$tariffgroupid
		
		INNER JOIN cc_tariffplan ON (cc_tariffplan.id=cc_tariffgroup_plan.idtariffplan )
		
		LEFT JOIN cc_ratecard ON cc_ratecard.idtariffplan=cc_tariffplan.id
		LEFT JOIN cc_trunk AS rt_trunk ON cc_ratecard.id_trunk=rt_trunk.id_trunk
		LEFT JOIN cc_trunk AS tp_trunk ON cc_tariffplan.id_trunk=tp_trunk.id_trunk
		
		WHERE (dialprefix=SUBSTRING('$phonenumber',1,length(dialprefix)) OR dialprefix='defaultprefix')
		AND startingdate<= CURRENT_TIMESTAMP AND (expirationdate > CURRENT_TIMESTAMP OR expirationdate IS NULL OR LENGTH(expirationdate)<5)
		AND startdate<= CURRENT_TIMESTAMP AND (stopdate > CURRENT_TIMESTAMP OR stopdate IS NULL OR LENGTH(stopdate)<5)
		$sql_clause_days
		AND idtariffgroup='$tariffgroupid'
		
		AND ( dnidprefix=SUBSTRING('$mydnid',1,length(dnidprefix)) OR (dnidprefix='all' $DNID_SUB_QUERY)) 
		
		AND ( calleridprefix=SUBSTRING('$mycallerid',1,length(calleridprefix)) OR (calleridprefix='all' $CID_SUB_QUERY)) 
		
		ORDER BY LENGTH(dialprefix) DESC";

More Specifically Line 157, the $CID_SUB_QUERY: 

AND ( calleridprefix=SUBSTRING('$mycallerid',1,length(calleridprefix)) OR (calleridprefix='all' $CID_SUB_QUERY))

$CID_SUB_QUERY = "AND 0 = (SELECT count(calleridprefix) FROM cc_tariffplan RIGHT JOIN cc_tariffgroup_plan ON cc_tariffgroup_plan.idtariffgroup=$tariffgroupid WHERE calleridprefix=SUBSTRING('$mycallerid',1,length(calleridprefix)) ) ";

The result of $CID_SUB_QUERY is "AND 0 = 2", which test's to false

A modification of the query: 

SELECT calleridprefix FROM cc_tariffplan RIGHT JOIN cc_tariffgroup_plan ON cc_tariffgroup_plan.idtariffgroup=2 WHERE calleridprefix  LIKE '%'

Returns: 

|  calleridprefix |
|  all            |
|  all            |

Change History

03/17/07 12:18:07 changed by crshman

  • version deleted.

03/17/07 12:29:19 changed by crshman

  • version set to Trunk 1.3.

03/17/07 15:14:54 changed by crshman

EDIT-- 

$CALLERID_QUERY = "SELECT count(calleridprefix) FROM cc_tariffplan RIGHT JOIN cc_tariffgroup_plan ON cc_tariffgroup_plan.idtariffgroup=$tariffgroupid WHERE calleridprefix  LIKE '$mycallerid%'";

NOT 

$CID_SUB_QUERY = "AND 0 = (SELECT count(calleridprefix) FROM cc_tariffplan RIGHT JOIN cc_tariffgroup_plan ON cc_tariffgroup_plan.idtariffgroup=$tariffgroupid WHERE calleridprefix=SUBSTRING('$mycallerid',1,length(calleridprefix)) ) ";

03/17/07 15:54:04 changed by crshman

  • status changed from new to closed.
  • resolution set to worksforme.
Index: a2billing/A2BCustomer_UI/lib/Class.RateEngine.php
===================================================================
--- a2billing/A2BCustomer_UI/lib/Class.RateEngine.php	(revision 881)
+++ a2billing/A2BCustomer_UI/lib/Class.RateEngine.php	(working copy)
@@ -106,14 +106,15 @@
 			else $nb_dnid = $result_sub[0][0];
 			$DNID_SUB_QUERY = "AND 0 = $nb_dnid";
 			
-			$CALLERID_QUERY = "SELECT count(calleridprefix) FROM cc_tariffplan RIGHT JOIN cc_tariffgroup_plan ON cc_tariffgroup_plan.idtariffgroup=$tariffgroupid WHERE calleridprefix  LIKE '$mycallerid%'";
+			// --OLD-- $CALLERID_QUERY = "SELECT count(calleridprefix) FROM cc_tariffplan RIGHT JOIN cc_tariffgroup_plan ON cc_tariffgroup_plan.idtariffgroup=$tariffgroupid WHERE calleridprefix LIKE '$mycallerid%'";
+			$CALLERID_QUERY = "SELECT count(calleridprefix) FROM cc_tariffplan RIGHT JOIN cc_tariffgroup_plan ON cc_tariffgroup_plan.idtariffgroup=$tariffgroupid WHERE calleridprefix=SUBSTRING('$mycallerid',1,length(calleridprefix))";
 			$result_sub = $A2B->instance_table -> SQLExec ($A2B -> DBHandle, $CALLERID_QUERY);
 			if (!is_array($result_sub) || count($result_sub)==0) $nb_callerid = 0;
 			else $nb_callerid = $result_sub[0][0];
 			$CID_SUB_QUERY = "AND 0 = $nb_callerid";
 			if ($this->webui) $A2B -> write_log("[CC_asterisk_rate-engine: CALLERID_QUERY]\n".$CALLERID_QUERY."\n",0);
 			if ($this->webui) $A2B -> write_log("[CC_asterisk_rate-engine: CALLERID_QUERY]\n".print_r($result_sub)."\n",0);
-			
+
 		}else{
 			$DNID_SUB_QUERY = "AND 0 = (SELECT count(dnidprefix) FROM cc_tariffplan RIGHT JOIN cc_tariffgroup_plan ON cc_tariffgroup_plan.idtariffgroup=$tariffgroupid WHERE dnidprefix=SUBSTRING('$mydnid',1,length(dnidprefix)) ) ";


Google