Navigation

← Previous Changeset
Next Changeset →

Changeset 606

Timestamp:

03/03/08 10:57:06 (9 months ago)

Author:

areski

Message:

UPDATE : move prefix optimization to branch 1.3

Files:

branches/1.3/A2BCustomer_UI/lib/Class.RateEngine.php (modified) (10 diffs)
branches/1.3/A2Billing_AGI/libs_a2billing/Class.RateEngine.php (modified) (10 diffs)
branches/1.3/A2Billing_UI/lib/Class.RateEngine.php (modified) (10 diffs)

Legend:

: Unmodified
: Added
: Removed
: Modified
: Copied
: Moved

branches/1.3/A2BCustomer_UI/lib/Class.RateEngine.php

r507	r606
142	142	}
143	143
144		$QUERY = "SELECT tariffgroupname, lcrtype, idtariffgroup, cc_tariffgroup_plan.idtariffplan, tariffname, destination,
145
	144	// $prefixclause to allow good DB servers to use an index rather than sequential scan
	145	// justification at http://forum.asterisk2billing.org/viewtopic.php?p=9620#9620
	146	$max_len_prefix = min(strlen($phonenumber), 15); // don't match more than 15 digits (the most I have on my side is 8 digit prefixes)
	147	$prefixclause = '';
	148	while ($max_len_prefix > 0 ) {
	149	$prefixclause .= "dialprefix='".substr($phonenumber,0,$max_len_prefix)."' OR ";
	150	$max_len_prefix--;
	151	}
	152	$prefixclause .= "dialprefix='defaultprefix'";
	153
	154	$QUERY = "SELECT
	155	tariffgroupname, lcrtype, idtariffgroup, cc_tariffgroup_plan.idtariffplan, tariffname, destination,
146	156	cc_ratecard.id, dialprefix, destination, buyrate, buyrateinitblock, buyrateincrement, rateinitial, initblock, billingblock,
147	157	connectcharge, disconnectcharge, stepchargea, chargea, timechargea, billingblocka, stepchargeb, chargeb,
…	…
150	160	tp_trunk.providerip AS tp_providerip, tp_trunk.removeprefix AS tp_removeprefix,
151	161	cc_ratecard.id_trunk AS rc_id_trunk, rt_trunk.trunkprefix AS rc_trunkprefix, rt_trunk.providertech AS rc_providertech,
152		rt_trunk.providerip AS rc_providerip, rt_trunk.removeprefix AS rc_removeprefix,
153		musiconhold,
154		tp_trunk.failover_trunk AS tp_failover_trunk,
155		rt_trunk.failover_trunk AS rt_failover_trunk,
156		tp_trunk.addparameter AS tp_addparameter_trunk,
157		rt_trunk.addparameter AS rt_addparameter_trunk,
158		id_outbound_cidgroup,
	162	rt_trunk.providerip AS rc_providerip, rt_trunk.removeprefix AS rc_removeprefix, musiconhold,
	163	tp_trunk.failover_trunk AS tp_failover_trunk, rt_trunk.failover_trunk AS rt_failover_trunk,
	164	tp_trunk.addparameter AS tp_addparameter_trunk, rt_trunk.addparameter AS rt_addparameter_trunk, id_outbound_cidgroup,
159	165	freetimetocall_package_offer, freetimetocall, packagetype, billingtype, startday, id_cc_package_offer
160
161	166
162	167	FROM cc_tariffgroup
163	168	RIGHT JOIN cc_tariffgroup_plan ON cc_tariffgroup.id=$tariffgroupid
164
165	169	INNER JOIN cc_tariffplan ON (cc_tariffplan.id=cc_tariffgroup_plan.idtariffplan )
166
167	170	LEFT JOIN cc_ratecard ON cc_ratecard.idtariffplan=cc_tariffplan.id
168	171	LEFT JOIN cc_trunk AS rt_trunk ON cc_ratecard.id_trunk=rt_trunk.id_trunk
…	…
170	173	LEFT JOIN cc_package_offer ON cc_package_offer.id=cc_tariffgroup.id_cc_package_offer
171	174
172		WHERE (~~dialprefix=SUBSTRING('$phonenumber',1,length(dialprefix)) OR dialprefix='defaultprefix'~~)
	175	WHERE ($prefixclause)
173	176	AND startingdate<= CURRENT_TIMESTAMP AND (expirationdate > CURRENT_TIMESTAMP OR expirationdate IS NULL OR LENGTH(expirationdate)<5)
174	177	AND startdate<= CURRENT_TIMESTAMP AND (stopdate > CURRENT_TIMESTAMP OR stopdate IS NULL OR LENGTH(stopdate)<5)
…	…
179	182	ORDER BY LENGTH(dialprefix) DESC";
180	183
181		~~//-- if ($this -> debug_st) echo $QUERY."\n\n";~~
182	184	if ($this->webui) $A2B -> debug( WRITELOG, $agi, __FILE__, __LINE__, "[RATE ENGINE QUERY]\n".$QUERY."\n",0);
183
184	185
185	186	$A2B->instance_table = new Table();
186	187	$result = $A2B->instance_table -> SQLExec ($A2B -> DBHandle, $QUERY);
187	188
188
189	189	if (!is_array($result) \|\| count($result)==0) return 0; // NO RATE FOR THIS NUMBER
190
191		if ($this -> debug_st) echo "::> Count Total result ".count($result)."\n\n";
	190
	191	if ($this->debug_st) echo "::> Count Total result ".count($result)."\n\n";
192	192	if ($this->webui) $A2B -> debug( WRITELOG, $agi, __FILE__, __LINE__, "[rate-engine: Count Total result ".count($result)."]");
193	193
…	…
202	202	}
203	203	// IMPORTANT TO CUT THE PART OF THE defaultprefix CAUSE WE WILL APPLY THE SORT ACCORDING TO THE RATE
204		// DEFAULPERFIX IS AN ESCAPE IN CASE OF NO RATE IS DEFINED, NOT BE COUNT WITH OTHER DURING THE
205		// SORT OF RATE
	204	// DEFAULPERFIX IS AN ESCAPE IN CASE OF NO RATE IS DEFINED, NOT BE COUNT WITH OTHER DURING THE SORT OF RATE
206	205	if ($ind_stop_default>0) {
207	206	$result_defaultprefix = array_slice ($result, 0, $ind_stop_default);
208	207	$result = array_slice ($result, $ind_stop_default, count($result)-$ind_stop_default);
209	208	}
210
211
212		~~//if ($ind_stop_default>0) array_rotate($result, $ind_stop_default);~~
213
214	209
215	210	//1) REMOVE THOSE THAT HAVE A SMALLER DIALPREFIX
…	…
224	219	//LCR : According to the buyer price -0 buyrate [col 6]
225	220	//LCD : According to the seller price -1 rateinitial [col 9]
226
227	221	$LCtype = $result[0][1];
228	222
229	223	// Thanks for the fix from the wiki :D next time email me, lol
230	224	if ($LCtype==0){
231		~~//$result = $this -> array_csort($result,'6',SORT_ASC); GOTTYA!~~
232	225	$result = $this -> array_csort($result,'9',SORT_ASC); //1
233	226	}else{
234		~~//$result = $this -> array_csort($result,'9',SORT_ASC); GOTTYA!~~
235	227	$result = $this -> array_csort($result,'12',SORT_ASC); //1
236		}
237
	228	}
238	229
239	230	// WE ADD THE DEFAULTPREFIX WE REMOVE BEFORE
…	…
241	232	$result = array_merge ($result, $result_defaultprefix);
242	233	}
243
244	234
245	235	// 3) REMOVE THOSE THAT USE THE SAME TRUNK - MAKE A DISTINCT
…	…
249	239	if ($result[$i][34]==-1) $mylistoftrunk_next[]= $mycurrenttrunk = $result[$i][29];
250	240	else $mylistoftrunk_next[]= $mycurrenttrunk = $result[$i][34];
251
252	241
253	242	// Check if we already have the same trunk in the ratecard
…	…
257	246
258	247	$mylistoftrunk[]= $mycurrenttrunk;
259		}
260
261
262		//4) $result[K][34]==-1 USE THE VALUES OF THE TRUNK OF $result[K][29]
263
	248	}
264	249
265	250	$this -> ratecard_obj = $distinct_result;
…	…
284	269	return 1;
285	270	}
286
287	271
288	272	/*

branches/1.3/A2Billing_AGI/libs_a2billing/Class.RateEngine.php

r602	r606
142	142	}
143	143
144		$QUERY = "SELECT tariffgroupname, lcrtype, idtariffgroup, cc_tariffgroup_plan.idtariffplan, tariffname, destination,
145
	144	// $prefixclause to allow good DB servers to use an index rather than sequential scan
	145	// justification at http://forum.asterisk2billing.org/viewtopic.php?p=9620#9620
	146	$max_len_prefix = min(strlen($phonenumber), 15); // don't match more than 15 digits (the most I have on my side is 8 digit prefixes)
	147	$prefixclause = '';
	148	while ($max_len_prefix > 0 ) {
	149	$prefixclause .= "dialprefix='".substr($phonenumber,0,$max_len_prefix)."' OR ";
	150	$max_len_prefix--;
	151	}
	152	$prefixclause .= "dialprefix='defaultprefix'";
	153
	154	$QUERY = "SELECT
	155	tariffgroupname, lcrtype, idtariffgroup, cc_tariffgroup_plan.idtariffplan, tariffname, destination,
146	156	cc_ratecard.id, dialprefix, destination, buyrate, buyrateinitblock, buyrateincrement, rateinitial, initblock, billingblock,
147	157	connectcharge, disconnectcharge, stepchargea, chargea, timechargea, billingblocka, stepchargeb, chargeb,
…	…
150	160	tp_trunk.providerip AS tp_providerip, tp_trunk.removeprefix AS tp_removeprefix,
151	161	cc_ratecard.id_trunk AS rc_id_trunk, rt_trunk.trunkprefix AS rc_trunkprefix, rt_trunk.providertech AS rc_providertech,
152		rt_trunk.providerip AS rc_providerip, rt_trunk.removeprefix AS rc_removeprefix,
153		musiconhold,
154		tp_trunk.failover_trunk AS tp_failover_trunk,
155		rt_trunk.failover_trunk AS rt_failover_trunk,
156		tp_trunk.addparameter AS tp_addparameter_trunk,
157		rt_trunk.addparameter AS rt_addparameter_trunk,
158		id_outbound_cidgroup,
	162	rt_trunk.providerip AS rc_providerip, rt_trunk.removeprefix AS rc_removeprefix, musiconhold,
	163	tp_trunk.failover_trunk AS tp_failover_trunk, rt_trunk.failover_trunk AS rt_failover_trunk,
	164	tp_trunk.addparameter AS tp_addparameter_trunk, rt_trunk.addparameter AS rt_addparameter_trunk, id_outbound_cidgroup,
159	165	freetimetocall_package_offer, freetimetocall, packagetype, billingtype, startday, id_cc_package_offer
160
161	166
162	167	FROM cc_tariffgroup
163	168	RIGHT JOIN cc_tariffgroup_plan ON cc_tariffgroup.id=$tariffgroupid
164
165	169	INNER JOIN cc_tariffplan ON (cc_tariffplan.id=cc_tariffgroup_plan.idtariffplan )
166
167	170	LEFT JOIN cc_ratecard ON cc_ratecard.idtariffplan=cc_tariffplan.id
168	171	LEFT JOIN cc_trunk AS rt_trunk ON cc_ratecard.id_trunk=rt_trunk.id_trunk
…	…
170	173	LEFT JOIN cc_package_offer ON cc_package_offer.id=cc_tariffgroup.id_cc_package_offer
171	174
172		WHERE (~~dialprefix=SUBSTRING('$phonenumber',1,length(dialprefix)) OR dialprefix='defaultprefix'~~)
	175	WHERE ($prefixclause)
173	176	AND startingdate<= CURRENT_TIMESTAMP AND (expirationdate > CURRENT_TIMESTAMP OR expirationdate IS NULL OR LENGTH(expirationdate)<5)
174	177	AND startdate<= CURRENT_TIMESTAMP AND (stopdate > CURRENT_TIMESTAMP OR stopdate IS NULL OR LENGTH(stopdate)<5)
…	…
179	182	ORDER BY LENGTH(dialprefix) DESC";
180	183
181		~~//-- if ($this -> debug_st) echo $QUERY."\n\n";~~
182	184	if ($this->webui) $A2B -> debug( WRITELOG, $agi, __FILE__, __LINE__, "[RATE ENGINE QUERY]\n".$QUERY."\n",0);
183
184	185
185	186	$A2B->instance_table = new Table();
186	187	$result = $A2B->instance_table -> SQLExec ($A2B -> DBHandle, $QUERY);
187	188
188
189	189	if (!is_array($result) \|\| count($result)==0) return 0; // NO RATE FOR THIS NUMBER
190
191		if ($this -> debug_st) echo "::> Count Total result ".count($result)."\n\n";
	190
	191	if ($this->debug_st) echo "::> Count Total result ".count($result)."\n\n";
192	192	if ($this->webui) $A2B -> debug( WRITELOG, $agi, __FILE__, __LINE__, "[rate-engine: Count Total result ".count($result)."]");
193	193
…	…
202	202	}
203	203	// IMPORTANT TO CUT THE PART OF THE defaultprefix CAUSE WE WILL APPLY THE SORT ACCORDING TO THE RATE
204		// DEFAULPERFIX IS AN ESCAPE IN CASE OF NO RATE IS DEFINED, NOT BE COUNT WITH OTHER DURING THE
205		// SORT OF RATE
	204	// DEFAULPERFIX IS AN ESCAPE IN CASE OF NO RATE IS DEFINED, NOT BE COUNT WITH OTHER DURING THE SORT OF RATE
206	205	if ($ind_stop_default>0) {
207	206	$result_defaultprefix = array_slice ($result, 0, $ind_stop_default);
208	207	$result = array_slice ($result, $ind_stop_default, count($result)-$ind_stop_default);
209	208	}
210
211
212		~~//if ($ind_stop_default>0) array_rotate($result, $ind_stop_default);~~
213
214	209
215	210	//1) REMOVE THOSE THAT HAVE A SMALLER DIALPREFIX
…	…
224	219	//LCR : According to the buyer price -0 buyrate [col 6]
225	220	//LCD : According to the seller price -1 rateinitial [col 9]
226
227	221	$LCtype = $result[0][1];
228	222
229	223	// Thanks for the fix from the wiki :D next time email me, lol
230	224	if ($LCtype==0){
231		~~//$result = $this -> array_csort($result,'6',SORT_ASC); GOTTYA!~~
232	225	$result = $this -> array_csort($result,'9',SORT_ASC); //1
233	226	}else{
234		~~//$result = $this -> array_csort($result,'9',SORT_ASC); GOTTYA!~~
235	227	$result = $this -> array_csort($result,'12',SORT_ASC); //1
236		}
237
	228	}
238	229
239	230	// WE ADD THE DEFAULTPREFIX WE REMOVE BEFORE
…	…
241	232	$result = array_merge ($result, $result_defaultprefix);
242	233	}
243
244	234
245	235	// 3) REMOVE THOSE THAT USE THE SAME TRUNK - MAKE A DISTINCT
…	…
249	239	if ($result[$i][34]==-1) $mylistoftrunk_next[]= $mycurrenttrunk = $result[$i][29];
250	240	else $mylistoftrunk_next[]= $mycurrenttrunk = $result[$i][34];
251
252	241
253	242	// Check if we already have the same trunk in the ratecard
…	…
257	246
258	247	$mylistoftrunk[]= $mycurrenttrunk;
259		}
260
261
262		//4) $result[K][34]==-1 USE THE VALUES OF THE TRUNK OF $result[K][29]
263
	248	}
264	249
265	250	$this -> ratecard_obj = $distinct_result;
…	…
285	270	return 1;
286	271	}
287
288	272
289	273	/*

branches/1.3/A2Billing_UI/lib/Class.RateEngine.php

r507	r606
142	142	}
143	143
144		$QUERY = "SELECT tariffgroupname, lcrtype, idtariffgroup, cc_tariffgroup_plan.idtariffplan, tariffname, destination,
145
	144	// $prefixclause to allow good DB servers to use an index rather than sequential scan
	145	// justification at http://forum.asterisk2billing.org/viewtopic.php?p=9620#9620
	146	$max_len_prefix = min(strlen($phonenumber), 15); // don't match more than 15 digits (the most I have on my side is 8 digit prefixes)
	147	$prefixclause = '';
	148	while ($max_len_prefix > 0 ) {
	149	$prefixclause .= "dialprefix='".substr($phonenumber,0,$max_len_prefix)."' OR ";
	150	$max_len_prefix--;
	151	}
	152	$prefixclause .= "dialprefix='defaultprefix'";
	153
	154	$QUERY = "SELECT
	155	tariffgroupname, lcrtype, idtariffgroup, cc_tariffgroup_plan.idtariffplan, tariffname, destination,
146	156	cc_ratecard.id, dialprefix, destination, buyrate, buyrateinitblock, buyrateincrement, rateinitial, initblock, billingblock,
147	157	connectcharge, disconnectcharge, stepchargea, chargea, timechargea, billingblocka, stepchargeb, chargeb,
…	…
150	160	tp_trunk.providerip AS tp_providerip, tp_trunk.removeprefix AS tp_removeprefix,
151	161	cc_ratecard.id_trunk AS rc_id_trunk, rt_trunk.trunkprefix AS rc_trunkprefix, rt_trunk.providertech AS rc_providertech,
152		rt_trunk.providerip AS rc_providerip, rt_trunk.removeprefix AS rc_removeprefix,
153		musiconhold,
154		tp_trunk.failover_trunk AS tp_failover_trunk,
155		rt_trunk.failover_trunk AS rt_failover_trunk,
156		tp_trunk.addparameter AS tp_addparameter_trunk,
157		rt_trunk.addparameter AS rt_addparameter_trunk,
158		id_outbound_cidgroup,
	162	rt_trunk.providerip AS rc_providerip, rt_trunk.removeprefix AS rc_removeprefix, musiconhold,
	163	tp_trunk.failover_trunk AS tp_failover_trunk, rt_trunk.failover_trunk AS rt_failover_trunk,
	164	tp_trunk.addparameter AS tp_addparameter_trunk, rt_trunk.addparameter AS rt_addparameter_trunk, id_outbound_cidgroup,
159	165	freetimetocall_package_offer, freetimetocall, packagetype, billingtype, startday, id_cc_package_offer
160
161	166
162	167	FROM cc_tariffgroup
163	168	RIGHT JOIN cc_tariffgroup_plan ON cc_tariffgroup.id=$tariffgroupid
164
165	169	INNER JOIN cc_tariffplan ON (cc_tariffplan.id=cc_tariffgroup_plan.idtariffplan )
166
167	170	LEFT JOIN cc_ratecard ON cc_ratecard.idtariffplan=cc_tariffplan.id
168	171	LEFT JOIN cc_trunk AS rt_trunk ON cc_ratecard.id_trunk=rt_trunk.id_trunk
…	…
170	173	LEFT JOIN cc_package_offer ON cc_package_offer.id=cc_tariffgroup.id_cc_package_offer
171	174
172		WHERE (~~dialprefix=SUBSTRING('$phonenumber',1,length(dialprefix)) OR dialprefix='defaultprefix'~~)
	175	WHERE ($prefixclause)
173	176	AND startingdate<= CURRENT_TIMESTAMP AND (expirationdate > CURRENT_TIMESTAMP OR expirationdate IS NULL OR LENGTH(expirationdate)<5)
174	177	AND startdate<= CURRENT_TIMESTAMP AND (stopdate > CURRENT_TIMESTAMP OR stopdate IS NULL OR LENGTH(stopdate)<5)
…	…
179	182	ORDER BY LENGTH(dialprefix) DESC";
180	183
181		~~//-- if ($this -> debug_st) echo $QUERY."\n\n";~~
182	184	if ($this->webui) $A2B -> debug( WRITELOG, $agi, __FILE__, __LINE__, "[RATE ENGINE QUERY]\n".$QUERY."\n",0);
183
184	185
185	186	$A2B->instance_table = new Table();
186	187	$result = $A2B->instance_table -> SQLExec ($A2B -> DBHandle, $QUERY);
187	188
188
189	189	if (!is_array($result) \|\| count($result)==0) return 0; // NO RATE FOR THIS NUMBER
190
191		if ($this -> debug_st) echo "::> Count Total result ".count($result)."\n\n";
	190
	191	if ($this->debug_st) echo "::> Count Total result ".count($result)."\n\n";
192	192	if ($this->webui) $A2B -> debug( WRITELOG, $agi, __FILE__, __LINE__, "[rate-engine: Count Total result ".count($result)."]");
193	193
…	…
202	202	}
203	203	// IMPORTANT TO CUT THE PART OF THE defaultprefix CAUSE WE WILL APPLY THE SORT ACCORDING TO THE RATE
204		// DEFAULPERFIX IS AN ESCAPE IN CASE OF NO RATE IS DEFINED, NOT BE COUNT WITH OTHER DURING THE
205		// SORT OF RATE
	204	// DEFAULPERFIX IS AN ESCAPE IN CASE OF NO RATE IS DEFINED, NOT BE COUNT WITH OTHER DURING THE SORT OF RATE
206	205	if ($ind_stop_default>0) {
207	206	$result_defaultprefix = array_slice ($result, 0, $ind_stop_default);
208	207	$result = array_slice ($result, $ind_stop_default, count($result)-$ind_stop_default);
209	208	}
210
211
212		~~//if ($ind_stop_default>0) array_rotate($result, $ind_stop_default);~~
213
214	209
215	210	//1) REMOVE THOSE THAT HAVE A SMALLER DIALPREFIX
…	…
224	219	//LCR : According to the buyer price -0 buyrate [col 6]
225	220	//LCD : According to the seller price -1 rateinitial [col 9]
226
227	221	$LCtype = $result[0][1];
228	222
229	223	// Thanks for the fix from the wiki :D next time email me, lol
230	224	if ($LCtype==0){
231		~~//$result = $this -> array_csort($result,'6',SORT_ASC); GOTTYA!~~
232	225	$result = $this -> array_csort($result,'9',SORT_ASC); //1
233	226	}else{
234		~~//$result = $this -> array_csort($result,'9',SORT_ASC); GOTTYA!~~
235	227	$result = $this -> array_csort($result,'12',SORT_ASC); //1
236		}
237
	228	}
238	229
239	230	// WE ADD THE DEFAULTPREFIX WE REMOVE BEFORE
…	…
241	232	$result = array_merge ($result, $result_defaultprefix);
242	233	}
243
244	234
245	235	// 3) REMOVE THOSE THAT USE THE SAME TRUNK - MAKE A DISTINCT
…	…
249	239	if ($result[$i][34]==-1) $mylistoftrunk_next[]= $mycurrenttrunk = $result[$i][29];
250	240	else $mylistoftrunk_next[]= $mycurrenttrunk = $result[$i][34];
251
252	241
253	242	// Check if we already have the same trunk in the ratecard
…	…
257	246
258	247	$mylistoftrunk[]= $mycurrenttrunk;
259		}
260
261
262		//4) $result[K][34]==-1 USE THE VALUES OF THE TRUNK OF $result[K][29]
263
	248	}
264	249
265	250	$this -> ratecard_obj = $distinct_result;
…	…
284	269	return 1;
285	270	}
286
287	271
288	272	/*

Download in other formats: