Navigation

← Previous Changeset
Next Changeset →

Changeset 1011

Timestamp:

09/14/08 19:02:48 (4 months ago)

Author:

areski

Message:

optimization for CDRs

Files:

trunk/agent/Public/call-comp.php (modified) (16 diffs)
trunk/agent/Public/call-count-reporting.php (modified) (6 diffs)
trunk/agent/Public/call-daily-load.php (modified) (14 diffs)
trunk/agent/Public/call-last-month.php (modified) (1 diff)
trunk/agent/Public/call-log-customers.php (modified) (3 diffs)
trunk/agent/Public/graph_hourdetail.php (modified) (3 diffs)
trunk/agent/Public/graph_pie.php (modified) (6 diffs)
trunk/agent/Public/graph_statbar.php (modified) (4 diffs)
trunk/agent/Public/graph_stat.php (modified) (9 diffs)

Legend:

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

trunk/agent/Public/call-comp.php

r1005	r1011
15	15
16	16
17		if (!isset ($current_page) \|\| ($current_page == "")){
18		$current_page=0;
19		}
	17	if (!isset ($current_page) \|\| ($current_page == "")) {
	18	$current_page=0;
	19	}
20	20
21	21	// this variable specifie the debug type (0 => nothing, 1 => sql result, 2 => boucle checking, 3 other value checking)
…	…
29	29	// THIS VARIABLE DEFINE THE COLOR OF THE HEAD TABLE
30	30	$FG_TABLE_HEAD_COLOR = "#D1D9E7";
31
32
33	31	$FG_TABLE_EXTERN_COLOR = "#7F99CC"; //#CC0033 (Rouge)
34	32	$FG_TABLE_INTERN_COLOR = "#EDF3FF"; //#FFEAFF (Rose)
35
36
37
38
39	33	// THIS VARIABLE DEFINE THE COLOR OF THE HEAD TABLE
40	34	$FG_TABLE_ALTERNATE_ROW_COLOR[] = "#FFFFFF";
…	…
42	36
43	37
44
45		~~//$link = DbConnect();~~
46	38	$DBHandle = DbConnect();
47
48		~~// The variable Var_col would define the col that we want show in your table~~
49		~~// First Name of the column in the html page, second name of the field~~
50	39	$FG_TABLE_COL = array();
51
52
53	40
54	41	/*******
…	…
57	44
58	45	$FG_TABLE_COL[]=array (gettext("Calldate"), "starttime", "15%", "center", "SORT", "19", "", "", "", "", "", "display_dateformat");
59		~~//$FG_TABLE_COL[]=array ("Callend", "stoptime", "15%", "center", "SORT", "19");~~
60
61
62		~~//$FG_TABLE_COL[]=array ("Source", "source", "20%", "center", "SORT", "30");~~
63
64	46	$FG_TABLE_COL[]=array (gettext("CalledNumber"), "calledstation", "15%", "center", "SORT", "30", "", "", "", "", "", "remove_prefix");
65		$FG_TABLE_COL[]=array (gettext("Destination"), "destination", "15%", "center", "SORT", "30", "", "", "", "", "", "remove_prefix");
66		//$FG_TABLE_COL[]=array ("Country", "calledcountry", "10%", "center", "SORT", "30", "lie", "country", "countryname", "countrycode='%id'", "%1");
67		//$FG_TABLE_COL[]=array ("Site", "site_id", "7%", "center", "sort", "15", "lie", "site", "name", "id='%id'", "%1");
68
	47	$FG_TABLE_COL[]=array (gettext("Destination"), "id_cc_prefix", "10%", "center", "SORT", "15", "lie", "cc_prefix", "destination", "id='%id'", "%1");
69	48	$FG_TABLE_COL[]=array (gettext("Duration"), "sessiontime", "7%", "center", "SORT", "30", "", "", "", "", "", "display_minute");
70
71		$FG_TABLE_COL[]=array (gettext("CardUsed"), "username", "11%", "center", "SORT", "", "30", "", "", "", "", "linktocustomer");
72		$FG_TABLE_COL[]=array (gettext("terminatecauseid"), "terminatecauseid", "10%", "center", "SORT", "30");
	49	$FG_TABLE_COL[]=array (gettext("CardUsed"), "card_id", "11%", "center", "SORT", "", "30", "", "", "", "", "linktocustomer");
	50	$FG_TABLE_COL[]=array (gettext("Terminatecause"), "terminatecauseid", "10%", "center", "SORT", "30");
73	51	$FG_TABLE_COL[]=array (gettext("IAX/SIP"), "sipiax", "6%", "center", "SORT", "", "list", $yesno);
74		~~//$FG_TABLE_COL[]=array ("DestID", "destID", "12%", "center", "SORT", "30");~~
75
76		~~//if ($_SESSION["is_admin"]==1) $FG_TABLE_COL[]=array ("Con_charg", "connectcharge", "12%", "center", "SORT", "30");~~
77		~~//if ($_SESSION["is_admin"]==1) $FG_TABLE_COL[]=array ("Dis_charg", "disconnectcharge", "12%", "center", "SORT", "30");~~
78		~~//if ($_SESSION["is_admin"]==1) $FG_TABLE_COL[]=array ("Sec/mn", "secpermin", "12%", "center", "SORT", "30");~~
79
80
81		~~//if ($_SESSION["is_admin"]==1) $FG_TABLE_COL[]=array ("Buycosts", "buycosts", "12%", "center", "SORT", "30");~~
82	52	$FG_TABLE_COL[]=array (gettext("InitialRate"), "calledrate", "10%", "center", "SORT", "30", "", "", "", "", "", "display_2dec");
83	53	$FG_TABLE_COL[]=array (gettext("Cost"), "sessionbill", "10%", "center", "SORT", "30", "", "", "", "", "", "display_2bill");
…	…
85	55
86	56
87		~~// ??? cardID~~
88	57	$FG_TABLE_DEFAULT_ORDER = "t1.starttime";
89	58	$FG_TABLE_DEFAULT_SENS = "DESC";
90	59
91	60	// This Variable store the argument for the SQL query
92
93		$FG_COL_QUERY='t1.starttime, t1.calledstation, t1.destination, t1.sessiontime, t1.username, t1.terminatecauseid, t1.sipiax, t1.calledrate, t1.sessionbill';
94		// t1.stoptime,
95
	61	$FG_COL_QUERY='t1.starttime, t1.calledstation, t1.id_cc_prefix, t1.sessiontime, t1.card_id, t1.terminatecauseid, t1.sipiax, t1.calledrate, t1.sessionbill';
96	62	$FG_COL_QUERY_GRAPH='t1.starttime, t1.sessiontime, t1.sessionbill-t1.buycost as profit, t1.sessionbill, t1.buycost';
97
98	63
99	64	// The variable LIMITE_DISPLAY define the limit of record to display by page
…	…
117	82
118	83
119
120
121	84	if ($FG_DEBUG == 3) echo "<br>Table : $FG_TABLE_NAME - Col_query : $FG_COL_QUERY";
122	85	$instance_table = new Table($FG_TABLE_NAME, $FG_COL_QUERY);
…	…
124	87
125	88
126		if ( is_null ($order) \|\| is_null($sens) ){
	89	if ( is_null ($order) \|\| is_null($sens)) {
127	90	$order = $FG_TABLE_DEFAULT_ORDER;
128	91	$sens = $FG_TABLE_DEFAULT_SENS;
…	…
130	93
131	94
132		if ($posted==1){
133		$SQLcmd = '';
134		$SQLcmd = do_field($SQLcmd, 'src', 'src');
135		$SQLcmd = do_field($SQLcmd, 'dst', 'calledstation');
136
137		if ($_POST['before']) {
138		if (strpos($SQLcmd, 'WHERE') > 0) { $SQLcmd = "$SQLcmd AND ";
139		}else{ $SQLcmd = "$SQLcmd WHERE "; }
140		$SQLcmd = "$SQLcmd starttime <'".$_POST['before']."'";
141		}
142		if ($_POST['after']) { if (strpos($SQLcmd, 'WHERE') > 0) { $SQLcmd = "$SQLcmd AND ";
143		} else { $SQLcmd = "$SQLcmd WHERE "; }
144		$SQLcmd = "$SQLcmd starttime >'".$_POST['after']."'";
145		}
146
147
148		}
149
150		//echo "SQLcmd:$SQLcmd<br>";
	95	if ($posted==1) {
	96	$SQLcmd = '';
	97	$SQLcmd = do_field($SQLcmd, 'src', 'src');
	98	$SQLcmd = do_field($SQLcmd, 'dst', 'calledstation');
	99
	100	if ($_POST['before']) {
	101	if (strpos($SQLcmd, 'WHERE') > 0) {
	102	$SQLcmd = "$SQLcmd AND ";
	103	} else {
	104	$SQLcmd = "$SQLcmd WHERE ";
	105	}
	106	$SQLcmd = "$SQLcmd starttime <'".$_POST['before']."'";
	107	}
	108	if ($_POST['after']) {
	109	if (strpos($SQLcmd, 'WHERE') > 0) {
	110	$SQLcmd = "$SQLcmd AND ";
	111	} else {
	112	$SQLcmd = "$SQLcmd WHERE ";
	113	}
	114	$SQLcmd = "$SQLcmd starttime >'".$_POST['after']."'";
	115	}
	116	}
151	117
152	118	$date_clause='';
153	119	// Period (Month-Day)
154
155
156		if (!isset($fromstatsday_sday)){
	120	if (!isset($fromstatsday_sday)) {
157	121	$fromstatsday_sday = date("d");
158	122	$fromstatsmonth_sday = date("Y-m");
159	123	}
160	124
161
162		if (!isset($days_compare)){
163		$days_compare=2;
164		}
165
166
167
168		//if (isset($fromstatsday_sday) && isset($fromstatsmonth_sday)) $date_clause.=" AND calldate <= '$fromstatsmonth_sday-$fromstatsday_sday+23' AND calldate >= SUBDATE('$fromstatsmonth_sday-$fromstatsday_sday',INTERVAL $days_compare DAY)";
169
170		if (DB_TYPE == "postgres"){
171		if (isset($fromstatsday_sday) && isset($fromstatsmonth_sday)) $date_clause.=" AND t1.starttime < date'$fromstatsmonth_sday-$fromstatsday_sday'+ INTERVAL '1 DAY' AND t1.starttime >= date'$fromstatsmonth_sday-$fromstatsday_sday' - INTERVAL '$days_compare DAY'";
172		}else{
173		if (isset($fromstatsday_sday) && isset($fromstatsmonth_sday)) $date_clause.=" AND t1.starttime < ADDDATE('$fromstatsmonth_sday-$fromstatsday_sday',INTERVAL 1 DAY) AND t1.starttime >= SUBDATE('$fromstatsmonth_sday-$fromstatsday_sday',INTERVAL $days_compare DAY)";
	125	if (!isset($days_compare)) $days_compare=2;
	126
	127
	128
	129	if (DB_TYPE == "postgres") {
	130	if (isset($fromstatsday_sday) && isset($fromstatsmonth_sday))
	131	$date_clause.=" AND t1.starttime < date'$fromstatsmonth_sday-$fromstatsday_sday'+ INTERVAL '1 DAY' AND t1.starttime >= date'$fromstatsmonth_sday-$fromstatsday_sday' - INTERVAL '$days_compare DAY'";
	132	} else {
	133	if (isset($fromstatsday_sday) && isset($fromstatsmonth_sday))
	134	$date_clause.=" AND t1.starttime < ADDDATE('$fromstatsmonth_sday-$fromstatsday_sday',INTERVAL 1 DAY) AND t1.starttime >= SUBDATE('$fromstatsmonth_sday-$fromstatsday_sday',INTERVAL $days_compare DAY)";
174	135	}
175	136
…	…
177	138
178	139
179		if (isset($customer) && ($customer>0)){
	140	if (isset($customer) && ($customer>0)) {
180	141	if (strlen($SQLcmd)>0) $SQLcmd.=" AND ";
181	142	else $SQLcmd.=" WHERE ";
182		$SQLcmd.=" ~~username~~='$customer' ";
183		}~~else~~{
	143	$SQLcmd.=" card_id='$customer' ";
	144	} else {
184	145	if (isset($entercustomer) && ($entercustomer>0)){
185	146	if (strlen($SQLcmd)>0) $SQLcmd.=" AND ";
186	147	else $SQLcmd.=" WHERE ";
187		$SQLcmd.=" ~~username~~='$entercustomer' ";
	148	$SQLcmd.=" card_id='$entercustomer' ";
188	149	}
189	150	}
190		if ($_SESSION["is_admin"] == 1)
191		{
	151
	152	if ($_SESSION["is_admin"] == 1) {
192	153	if (isset($enterprovider) && $enterprovider > 0) {
193	154	if (strlen($SQLcmd) > 0) $SQLcmd .= " AND "; else $SQLcmd .= " WHERE ";
…	…
208	169	}
209	170
210		//print $SQLcmd;
	171
211	172	if (strpos($SQLcmd, 'WHERE') > 0) {
212	173	$FG_TABLE_CLAUSE = substr($SQLcmd,6).$date_clause;
213		}~~elseif (strpos($date_clause, 'AND') > 0)~~{
	174	} elseif (strpos($date_clause, 'AND') > 0) {
214	175	$FG_TABLE_CLAUSE = substr($date_clause,5);
215	176	}
…	…
223	184
224	185	if ($FG_DEBUG == 3) echo "<br>Clause : $FG_TABLE_CLAUSE";
225		~~//$nb_record = $instance_table -> Table_count ($FG_TABLE_CLAUSE);~~
226	186	$nb_record = count($list_total);
227	187	if ($FG_DEBUG >= 1) var_dump ($list);
…	…
229	189
230	190
231		if ($nb_record<=$FG_LIMITE_DISPLAY){
	191	if ($nb_record<=$FG_LIMITE_DISPLAY) {
232	192	$nb_record_max=1;
233		}~~else~~{
	193	} else {
234	194	$nb_record_max=(intval($nb_record/$FG_LIMITE_DISPLAY)+1);
235	195	}
…	…
239	199
240	200
241		~~/*************************************************************/~~
242
243
244		~~$instance_table_customer = new Table("cc_card", "id, username, lastname");~~
245
246		~~$FG_TABLE_CLAUSE = "";~~
247		~~/*if ($_SESSION["is_admin"]==0){~~
248		~~$FG_TABLE_CLAUSE =" IDmanager='".$_SESSION["pr_reseller_ID"]."'";~~
249		~~}*/~~
250
251		~~$list_customer = $instance_table_customer -> Get_list ($DBHandle, $FG_TABLE_CLAUSE, "id", "ASC", null, null, null, null);~~
252
253		~~$nb_customer = count($list_customer);~~
254
255		?>
256
257		~~<?php~~
258	201	$smarty->display('main.tpl');
	202
259	203	?>
260	204
…	…
284	228	<tr>
285	229	<td class="fontstyle_searchoptions" width="50%" valign="top">
286		<?php echo gettext("Enter the card~~number~~");?>: <INPUT TYPE="text" NAME="entercustomer" value="<?php echo $entercustomer?>" class="form_input_text">
287		<a href="#" onclick="window.open('A2B_entity_card.php?popup_select=2&popup_formname=myForm&popup_fieldname=entercustomer' , 'CardNumberSelection','scrollbars=1,width=550,height=330,top=20,left=100,scrollbars=1');"><img src="<?php echo Images_Path;?>/icon_arrow_orange.gif"></a>
	230	<?php echo gettext("Enter the card ID");?>: <INPUT TYPE="text" NAME="entercustomer" value="<?php echo $entercustomer?>" class="form_input_text">
	231	<a href="#" onclick="window.open('A2B_entity_card.php?popup_select=1&popup_formname=myForm&popup_fieldname=entercustomer' , 'CardNumberSelection','scrollbars=1,width=550,height=330,top=20,left=100,scrollbars=1');"><img src="<?php echo Images_Path;?>/icon_arrow_orange.gif"></a>
288	232	</td>
289	233	<td width="50%">
…	…
580	524	<IMG SRC="graph_stat.php?min_call=<?php echo $min_call?>&fromstatsday_sday=<?php echo $fromstatsday_sday?>&days_compare=<?php echo $days_compare?>&fromstatsmonth_sday=<?php echo $fromstatsmonth_sday?>&dsttype=<?php echo $dsttype?>&srctype=<?php echo $srctype?>&clidtype=<?php echo $clidtype?>&channel=<?php echo $channel?>&resulttype=<?php echo $resulttype?>&dst=<?php echo $dst?>&src=<?php echo $src?>&clid=<?php echo $clid?>&userfieldtype=<?php echo $userfieldtype?>&userfield=<?php echo $userfield?>&accountcodetype=<?php echo $accountcodetype?>&accountcode=<?php echo $accountcode?>&customer=<?php echo $customer?>&entercustomer=<?php echo $entercustomer?>&entertariffgroup=<?php echo $entertariffgroup?>&enterprovider=<?php echo $enterprovider?>&entertrunk=<?php echo $entertrunk?>&enterratecard=<?php echo $enterratecard?>" ALT="Stat Graph">
581	525
582		<?php }~~else~~{ ?>
	526	<?php } else { ?>
583	527	<center><h3><?php echo gettext("No calls in your selection");?>.</h3></center>
584	528	<?php } ?>
…	…
587	531
588	532	<br><br>
	533
589	534	<?php
590		$smarty->display('footer.tpl');
591		?>
	535
	536	$smarty->display('footer.tpl');

trunk/agent/Public/call-count-reporting.php

r1005	r1011
12	12	getpost_ifset(array('inputtopvar','topsearch', 'posted', 'Period', 'frommonth', 'fromstatsmonth', 'tomonth', 'tostatsmonth', 'fromday', 'fromstatsday_sday', 'fromstatsmonth_sday', 'today', 'tostatsday_sday', 'tostatsmonth_sday', 'resulttype', 'stitle', 'atmenu', 'current_page', 'order', 'sens', 'choose_currency', 'terminatecauseid', 'nodisplay','grouped'));
13	13
14		~~//var $field=array();~~
15
16		~~switch ($topsearch){~~
17		~~case "topuser":~~
18		~~$field=array('CARD ID','username');~~
19		~~break;~~
20		~~case "topdestination":~~
21		~~$field=array('DESTINATION','destination');~~
22		~~break;~~
23		~~default:~~
24		~~$field=array('CARD ID','username');~~
25		}
26
27	14
28	15	if (!isset ($current_page) \|\| ($current_page == "")){
…	…
30	17	}
31	18
32		if (!isset ($FG_TABLE_CLAUSE) \|\| strlen($FG_TABLE_CLAUSE)==0){
33
34		$cc_yearmonth = sprintf("%04d-%02d-%02d",date("Y"),date("n"),date("d"));
35		$FG_TABLE_CLAUSE=" $UNIX_TIMESTAMP(starttime) <= $UNIX_TIMESTAMP('$cc_yearmonth')";
	19	if (!isset ($FG_TABLE_CLAUSE) \|\| strlen($FG_TABLE_CLAUSE)==0) {
	20	$cc_yearmonth = sprintf("%04d-%02d-%02d",date("Y"),date("n"),date("d"));
	21	$FG_TABLE_CLAUSE=" $UNIX_TIMESTAMP(starttime) <= $UNIX_TIMESTAMP('$cc_yearmonth')";
36	22	}
37	23
38	24	// this variable specifie the debug type (0 => nothing, 1 => sql result, 2 => boucle checking, 3 other value checking)
39	25	$FG_DEBUG = 0;
40
41		~~// The variable FG_TABLE_NAME define the table name to use~~
42	26	$FG_TABLE_NAME="cc_call";
43
44		~~// THIS VARIABLE DEFINE THE COLOR OF THE HEAD TABLE~~
45	27	$FG_TABLE_HEAD_COLOR = "#D1D9E7";
46
47
48	28	$FG_TABLE_EXTERN_COLOR = "#7F99CC"; //#CC0033 (Rouge)
49	29	$FG_TABLE_INTERN_COLOR = "#EDF3FF"; //#FFEAFF (Rose)
…	…
54	34	$FG_TABLE_ALTERNATE_ROW_COLOR[] = "#F2F8FF";
55	35
56		~~//$link = DbConnect();~~
57	36	$DBHandle = DbConnect();
58	37
59		~~// The variable Var_col would define the col that we want show in your table~~
60		~~// First Name of the column in the html page, second name of the field~~
61	38	$FG_TABLE_COL = array();
62	39
63	40
64		if ((!isset($field)) && (count($field)<=1)){
65		$field[0]="CARD ID";
66		$field[1]="username"; }
67
68		$FG_TABLE_COL[]=array (gettext($field[0]), $field[1], "20%", "center","SORT", "", "30", "", "", "", "", "linktocustomer");
	41	switch ($topsearch) {
	42
	43	case "topdestination":
	44	$FG_TABLE_COL[]=array (gettext("Destination"), "id_cc_prefix", "10%", "center", "SORT", "15", "lie", "cc_prefix", "destination", "id='%id'", "%1");
	45	$on_field = "id_cc_prefix";
	46	break;
	47
	48	case "topuser":
	49	default:
	50	$FG_TABLE_COL[]=array (gettext("CardUsed"), 'card_id', "20%", "center","SORT", "", "30", "", "", "", "", "linktocustomer");
	51	$on_field = "card_id";
	52
	53	}
	54
69	55	$FG_TABLE_COL[]=array (gettext("Duration"), "calltime", "20%", "center", "SORT", "30", "", "", "", "", "", "display_minute");
70	56	$FG_TABLE_COL[]=array (gettext("Buy"), "buy", "25%", "center","sort","","","","","","","display_2bill");
…	…
78	64
79	65	if ($grouped){
80		$FG_COL_QUERY=$~~field[1]~~.', sum(sessiontime) AS calltime, sum(sessionbill) as cost, sum(buycost) as buy,substring(starttime,1,10) AS day,terminatecauseid, count(*) as nbcall';
81		$SQL_GROUP="GROUP BY ".$~~field[1]~~.",day,terminatecauseid ";
	66	$FG_COL_QUERY=$on_field.', sum(sessiontime) AS calltime, sum(sessionbill) as cost, sum(buycost) as buy,substring(starttime,1,10) AS day,terminatecauseid, count(*) as nbcall';
	67	$SQL_GROUP="GROUP BY ".$on_field.",day,terminatecauseid ";
82	68	}else{
83		$FG_COL_QUERY=$~~field[1]~~.', sum(sessiontime) AS calltime, sum(sessionbill) as cost, sum(buycost) as buy,terminatecauseid, count(*) as nbcall';
84		$SQL_GROUP="GROUP BY ".$~~field[1]~~.",terminatecauseid ";
	69	$FG_COL_QUERY=$on_field.', sum(sessiontime) AS calltime, sum(sessionbill) as cost, sum(buycost) as buy,terminatecauseid, count(*) as nbcall';
	70	$SQL_GROUP="GROUP BY ".$on_field.",terminatecauseid ";
85	71	}
86	72
…	…
161	147	if ((isset($inputtopvar)) && ($inputtopvar!="") && (isset($topsearch)) && ($topsearch!="")){
162	148	if ($grouped){
163		$FG_COL_QUERY1=$~~field[1]~~.', sum(sessiontime) AS sessiontime, sum(sessionbill) as sessionbill, sum(buycost) as buycost,substring(starttime,1,10) AS starttime,terminatecauseid, count(*) as nbcall';
164		$SQL_GROUP1=" GROUP BY $~~field[1]~~,starttime,terminatecauseid";
	149	$FG_COL_QUERY1=$on_field.', sum(sessiontime) AS sessiontime, sum(sessionbill) as sessionbill, sum(buycost) as buycost,substring(starttime,1,10) AS starttime,terminatecauseid, count(*) as nbcall';
	150	$SQL_GROUP1=" GROUP BY $on_field,starttime,terminatecauseid";
165	151	}else{
166		$FG_COL_QUERY1=$~~field[1]~~.', sum(sessiontime) AS sessiontime, sum(sessionbill) as sessionbill, sum(buycost) as buycost,terminatecauseid, count(*) as nbcall,starttime';
167		$SQL_GROUP1=" GROUP BY $~~field[1]~~,terminatecauseid,starttime";
	152	$FG_COL_QUERY1=$on_field.', sum(sessiontime) AS sessiontime, sum(sessionbill) as sessionbill, sum(buycost) as buycost,terminatecauseid, count(*) as nbcall,starttime';
	153	$SQL_GROUP1=" GROUP BY $on_field,terminatecauseid,starttime";
168	154	}
169	155	$QUERY = "CREATE TEMPORARY TABLE temp_result AS (SELECT $FG_COL_QUERY1 FROM $FG_TABLE_NAME WHERE $FG_TABLE_CLAUSE $SQL_GROUP1 ORDER BY nbcall DESC LIMIT $inputtopvar)";
…	…
172	158	$FG_TABLE_NAME="temp_result";
173	159	if ($grouped){
174		$FG_COL_QUERY=$~~field[1]~~.', sum(sessiontime) AS calltime, sum(sessionbill) as cost, sum(buycost) as buy,substring(starttime,1,10) AS day,terminatecauseid, nbcall';
175		$SQL_GROUP=" GROUP BY ".$~~field[1]~~.",day,terminatecauseid,nbcall ";
	160	$FG_COL_QUERY=$on_field.', sum(sessiontime) AS calltime, sum(sessionbill) as cost, sum(buycost) as buy,substring(starttime,1,10) AS day,terminatecauseid, nbcall';
	161	$SQL_GROUP=" GROUP BY ".$on_field.",day,terminatecauseid,nbcall ";
176	162	$QUERY_TOTAL = "SELECT sum(sessiontime) AS calltime, sum(sessionbill) as cost, sum(buycost) as buy, sum(nbcall) as nbcall,substring(starttime,1,10) AS day FROM $FG_TABLE_NAME GROUP BY day";
177	163	}else{
178		$FG_COL_QUERY=$~~field[1]~~.', sum(sessiontime) AS calltime, sum(sessionbill) as cost, sum(buycost) as buy,terminatecauseid, nbcall';
179		$SQL_GROUP=" GROUP BY ".$~~field[1]~~.",terminatecauseid,nbcall ";
	164	$FG_COL_QUERY=$on_field.', sum(sessiontime) AS calltime, sum(sessionbill) as cost, sum(buycost) as buy,terminatecauseid, nbcall';
	165	$SQL_GROUP=" GROUP BY ".$on_field.",terminatecauseid,nbcall ";
180	166	$QUERY_TOTAL = "SELECT sum(sessiontime) AS calltime, sum(sessionbill) as cost, sum(buycost) as buy, sum(nbcall) as nbcall FROM $FG_TABLE_NAME";
181	167	}

trunk/agent/Public/call-daily-load.php

r1005	r1011
4	4	include ("../lib/agent.smarty.php");
5	5
6		if (! has_rights (ACX_CALL_REPORT)){
	6	if (! has_rights (ACX_CALL_REPORT)) {
7	7	Header ("HTTP/1.0 401 Unauthorized");
8	8	Header ("Location: PP_error.php?c=accessdenied");
…	…
11	11
12	12
13
14	13	getpost_ifset(array('current_page', 'fromstatsday_sday', 'fromstatsmonth_sday', 'days_compare', 'min_call', 'posted', 'dsttype', 'srctype', 'clidtype', 'channel', 'resulttype', 'stitle', 'atmenu', 'current_page', 'order', 'sens', 'dst', 'src', 'clid', 'userfieldtype', 'userfield', 'accountcodetype', 'accountcode', 'customer', 'entercustomer','entertariffgroup' ,'enterprovider', 'entertrunk', 'enterratecard'));
15	14
16	15
17		if (!isset ($current_page) \|\| ($current_page == "")){
18		$current_page=0;
19		}
20
21		// this variable specifie the debug type (0 => nothing, 1 => sql result, 2 => boucle checking, 3 other value checking)
	16	if (!isset ($current_page) \|\| ($current_page == "")) {
	17	$current_page=0;
	18	}
	19
	20
22	21	$FG_DEBUG = 0;
23	22
24		// The variable FG_TABLE_NAME define the table name to use
	23
25	24	$FG_TABLE_NAME="cc_call t1 LEFT OUTER JOIN cc_trunk t3 ON t1.id_trunk = t3.id_trunk";
26
27		~~if ($_SESSION["is_admin"]==0){~~
28		~~//$FG_TABLE_NAME.=", cc_card t2";~~
29		}
30	25
31	26	// THIS VARIABLE DEFINE THE COLOR OF THE HEAD TABLE
32	27	$FG_TABLE_HEAD_COLOR = "#D1D9E7";
33
34
35	28	$FG_TABLE_EXTERN_COLOR = "#7F99CC"; //#CC0033 (Rouge)
36	29	$FG_TABLE_INTERN_COLOR = "#EDF3FF"; //#FFEAFF (Rose)
37
38
39
40
41	30	// THIS VARIABLE DEFINE THE COLOR OF THE HEAD TABLE
42	31	$FG_TABLE_ALTERNATE_ROW_COLOR[] = "#FFFFFF";
…	…
44	33
45	34
46
47		~~//$link = DbConnect();~~
48	35	$DBHandle = DbConnect();
49
50		~~// The variable Var_col would define the col that we want show in your table~~
51		~~// First Name of the column in the html page, second name of the field~~
52	36	$FG_TABLE_COL = array();
53	37
54	38
55	39
56		/*******
57		~~Calldate Clid Src Dst Dcontext Channel Dstchannel Lastapp Lastdata Duration Billsec Disposition Amaflags Accountcode Uniqueid Serverid~~
58		*******/
59	40
60	41	$FG_TABLE_COL[]=array (gettext("Calldate"), "starttime", "15%", "center", "SORT", "19", "", "", "", "", "", "display_dateformat");
61		~~//$FG_TABLE_COL[]=array ("Callend", "stoptime", "15%", "center", "SORT", "19");~~
62
63
64		~~//$FG_TABLE_COL[]=array ("Source", "source", "20%", "center", "SORT", "30");~~
65
66	42	$FG_TABLE_COL[]=array (gettext("CalledNumber"), "calledstation", "15%", "center", "SORT", "30", "", "", "", "", "", "remove_prefix");
67		$FG_TABLE_COL[]=array (gettext("Destination"), "destination", "15%", "center", "SORT", "30", "", "", "", "", "", "remove_prefix");
68		//$FG_TABLE_COL[]=array ("Country", "calledcountry", "10%", "center", "SORT", "30", "lie", "country", "countryname", "countrycode='%id'", "%1");
69		//$FG_TABLE_COL[]=array ("Site", "site_id", "7%", "center", "sort", "15", "lie", "site", "name", "id='%id'", "%1");
70
	43	$FG_TABLE_COL[]=array (gettext("Destination"), "id_cc_prefix", "10%", "center", "SORT", "15", "lie", "cc_prefix", "destination", "id='%id'", "%1");
71	44	$FG_TABLE_COL[]=array (gettext("Duration"), "sessiontime", "7%", "center", "SORT", "30", "", "", "", "", "", "display_minute");
72
73		$FG_TABLE_COL[]=array (gettext("CardUsed"), "username", "11%", "center", "SORT", "", "30", "", "", "", "", "linktocustomer");
	45	$FG_TABLE_COL[]=array (gettext("CardUsed"), "card_id", "11%", "center", "SORT", "", "30", "", "", "", "", "linktocustomer");
74	46	$FG_TABLE_COL[]=array (gettext("terminatecauseid"), "terminatecauseid", "10%", "center", "SORT", "30");
75	47	$FG_TABLE_COL[]=array (gettext("IAX/SIP"), "sipiax", "6%", "center", "SORT", "", "list", $yesno);
76		~~//$FG_TABLE_COL[]=array ("DestID", "destID", "12%", "center", "SORT", "30");~~
77
78		~~//if ($_SESSION["is_admin"]==1) $FG_TABLE_COL[]=array ("Con_charg", "connectcharge", "12%", "center", "SORT", "30");~~
79		~~//if ($_SESSION["is_admin"]==1) $FG_TABLE_COL[]=array ("Dis_charg", "disconnectcharge", "12%", "center", "SORT", "30");~~
80		~~//if ($_SESSION["is_admin"]==1) $FG_TABLE_COL[]=array ("Sec/mn", "secpermin", "12%", "center", "SORT", "30");~~
81
82
83		~~//if ($_SESSION["is_admin"]==1) $FG_TABLE_COL[]=array ("Buycosts", "buycosts", "12%", "center", "SORT", "30");~~
84	48	$FG_TABLE_COL[]=array (gettext("InitialRate"), "calledrate", "10%", "center", "SORT", "30", "", "", "", "", "", "display_2dec");
85	49	$FG_TABLE_COL[]=array (gettext("Cost"), "sessionbill", "10%", "center", "SORT", "30", "", "", "", "", "", "display_2bill");
86	50
87	51
88
89		~~// ??? cardID~~
90	52	$FG_TABLE_DEFAULT_ORDER = "t1.starttime";
91	53	$FG_TABLE_DEFAULT_SENS = "DESC";
92
93		// This Variable store the argument for the SQL query
94
95		$FG_COL_QUERY='t1.starttime, t1.calledstation, t1.destination, t1.sessiontime, t1.username, t1.terminatecauseid, t1.sipiax, t1.calledrate, t1.sessionbill';
96		// t1.stoptime,
97
	54
	55	$FG_COL_QUERY='t1.starttime, t1.calledstation, t1.id_cc_prefix, t1.sessiontime, t1.card_id, t1.terminatecauseid, t1.sipiax, t1.calledrate, t1.sessionbill';
98	56	$FG_COL_QUERY_GRAPH='t1.starttime, t1.sessiontime';
99	57
100		~~// The variable LIMITE_DISPLAY define the limit of record to display by page~~
101	58	$FG_LIMITE_DISPLAY=25;
102
103		~~// Number of column in the html table~~
104	59	$FG_NB_TABLE_COL=count($FG_TABLE_COL);
105
106		~~// The variable $FG_EDITION define if you want process to the edition of the database record~~
107	60	$FG_EDITION=true;
108
109		~~//This variable will store the total number of column~~
110	61	$FG_TOTAL_TABLE_COL = $FG_NB_TABLE_COL;
111	62	if ($FG_DELETION \|\| $FG_EDITION) $FG_TOTAL_TABLE_COL++;
112
113		~~//This variable define the Title of the HTML table~~
114	63	$FG_HTML_TABLE_TITLE= gettext(" - Call Logs - ");
115
116		~~//This variable define the width of the HTML table~~
117	64	$FG_HTML_TABLE_WIDTH="90%";
118	65
…	…
125	72
126	73
127		if ( ~~is_null ($order) \|\| is_null($sens) )~~{
	74	if (is_null ($order) \|\| is_null($sens)) {
128	75	$order = $FG_TABLE_DEFAULT_ORDER;
129	76	$sens = $FG_TABLE_DEFAULT_SENS;
…	…
131	78
132	79
133		if ($posted==1){
134		$SQLcmd = '';
135
136		//$SQLcmd = do_field($SQLcmd, 'src', 'source');
137		$SQLcmd = do_field($SQLcmd, 'dst', 'calledstation');
138		if ($_POST['before']) {
139		if (strpos($SQLcmd, 'WHERE') > 0) { $SQLcmd = "$SQLcmd AND ";
140		}else{ $SQLcmd = "$SQLcmd WHERE "; }
141		$SQLcmd = "$SQLcmd t1.starttime <'".$_POST['before']."'";
142		}
143		if ($_POST['after']) { if (strpos($SQLcmd, 'WHERE') > 0) { $SQLcmd = "$SQLcmd AND ";
144		} else { $SQLcmd = "$SQLcmd WHERE "; }
145		$SQLcmd = "$SQLcmd t1.starttime >'".$_POST['after']."'";
146		}
147
148		}
149
150
151		if (isset($customer) && ($customer>0)){
	80	if ($posted==1) {
	81	$SQLcmd = '';
	82
	83	//$SQLcmd = do_field($SQLcmd, 'src', 'source');
	84	$SQLcmd = do_field($SQLcmd, 'dst', 'calledstation');
	85	if ($_POST['before']) {
	86	if (strpos($SQLcmd, 'WHERE') > 0) {
	87	$SQLcmd = "$SQLcmd AND ";
	88	}else{
	89	$SQLcmd = "$SQLcmd WHERE ";
	90	}
	91	$SQLcmd = "$SQLcmd t1.starttime <'".$_POST['before']."'";
	92	}
	93	if ($_POST['after']) {
	94	if (strpos($SQLcmd, 'WHERE') > 0) {
	95	$SQLcmd = "$SQLcmd AND ";
	96	} else {
	97	$SQLcmd = "$SQLcmd WHERE ";
	98	}
	99	$SQLcmd = "$SQLcmd t1.starttime >'".$_POST['after']."'";
	100	}
	101	}
	102
	103
	104	if (isset($customer) && ($customer>0)) {
152	105	if (strlen($SQLcmd)>0) $SQLcmd.=" AND ";
153	106	else $SQLcmd.=" WHERE ";
154		$SQLcmd.=" ~~username~~='$customer' ";
155		}~~else~~{
156		if (isset($entercustomer) && ($entercustomer>0)){
	107	$SQLcmd.=" card_id='$customer' ";
	108	} else {
	109	if (isset($entercustomer) && ($entercustomer>0)) {
157	110	if (strlen($SQLcmd)>0) $SQLcmd.=" AND ";
158	111	else $SQLcmd.=" WHERE ";
159		$SQLcmd.=" ~~username~~='$entercustomer' ";
	112	$SQLcmd.=" card_id='$entercustomer' ";
160	113	}
161	114	}
162		if ($_SESSION["is_admin"] == 1)
163		{
164		if (isset($enterprovider) && $enterprovider > 0) {
165		if (strlen($SQLcmd) > 0) $SQLcmd .= " AND "; else $SQLcmd .= " WHERE ";
166		$SQLcmd .= " t3.id_provider = '$enterprovider' ";
167		}
168		if (isset($entertrunk) && $entertrunk > 0) {
169		if (strlen($SQLcmd) > 0) $SQLcmd .= " AND "; else $SQLcmd .= " WHERE ";
170		$SQLcmd .= " t3.id_trunk = '$entertrunk' ";
171		}
172		if (isset($entertariffgroup) && $entertariffgroup > 0) {
173		if (strlen($SQLcmd) > 0) $SQLcmd .= " AND "; else $SQLcmd .= " WHERE ";
174		$SQLcmd .= "t1.id_tariffgroup = '$entertariffgroup'";
175		}
176		if (isset($enterratecard) && $enterratecard > 0) {
177		if (strlen($SQLcmd) > 0) $SQLcmd .= " AND "; else $SQLcmd .= " WHERE ";
178		$SQLcmd .= "t1.id_ratecard = '$enterratecard'";
179		}
180
	115	if ($_SESSION["is_admin"] == 1) {
	116	if (isset($enterprovider) && $enterprovider > 0) {
	117	if (strlen($SQLcmd) > 0) $SQLcmd .= " AND "; else $SQLcmd .= " WHERE ";
	118	$SQLcmd .= " t3.id_provider = '$enterprovider' ";
	119	}
	120	if (isset($entertrunk) && $entertrunk > 0) {
	121	if (strlen($SQLcmd) > 0) $SQLcmd .= " AND "; else $SQLcmd .= " WHERE ";
	122	$SQLcmd .= " t3.id_trunk = '$entertrunk' ";
	123	}
	124	if (isset($entertariffgroup) && $entertariffgroup > 0) {
	125	if (strlen($SQLcmd) > 0) $SQLcmd .= " AND "; else $SQLcmd .= " WHERE ";
	126	$SQLcmd .= "t1.id_tariffgroup = '$entertariffgroup'";
	127	}
	128	if (isset($enterratecard) && $enterratecard > 0) {
	129	if (strlen($SQLcmd) > 0) $SQLcmd .= " AND "; else $SQLcmd .= " WHERE ";
	130	$SQLcmd .= "t1.id_ratecard = '$enterratecard'";
	131	}
181	132	}
182	133
183	134
184	135	$date_clause='';
185		~~// Period (Month-Day)~~
186
187
188	136	if (!isset($fromstatsday_sday)){
189		$fromstatsday_sday = date("d");
190		$fromstatsmonth_sday = date("Y-m");
191		}
192
193
194
195		//if (isset($fromstatsday_sday) && isset($fromstatsmonth_sday)) $date_clause.=" AND calldate <= '$fromstatsmonth_sday-$fromstatsday_sday+23' AND calldate >= SUBDATE('$fromstatsmonth_sday-$fromstatsday_sday',INTERVAL $days_compare DAY)";
196
197		if (DB_TYPE == "postgres"){
198		if (isset($fromstatsday_sday) && isset($fromstatsmonth_sday)) $date_clause.=" AND t1.starttime < date'$fromstatsmonth_sday-$fromstatsday_sday'+ INTERVAL '1 DAY' AND t1.starttime >= date'$fromstatsmonth_sday-$fromstatsday_sday'";
199		}else{
200		if (isset($fromstatsday_sday) && isset($fromstatsmonth_sday)) $date_clause.=" AND t1.starttime < ADDDATE('$fromstatsmonth_sday-$fromstatsday_sday',INTERVAL 1 DAY) AND t1.starttime >= '$fromstatsmonth_sday-$fromstatsday_sday'";
	137	$fromstatsday_sday = date("d");
	138	$fromstatsmonth_sday = date("Y-m");
	139	}
	140
	141	if (DB_TYPE == "postgres") {
	142	if (isset($fromstatsday_sday) && isset($fromstatsmonth_sday))
	143	$date_clause.=" AND t1.starttime < date'$fromstatsmonth_sday-$fromstatsday_sday'+ INTERVAL '1 DAY' AND t1.starttime >= date'$fromstatsmonth_sday-$fromstatsday_sday'";
	144	} else {
	145	if (isset($fromstatsday_sday) && isset($fromstatsmonth_sday))
	146	$date_clause.=" AND t1.starttime < ADDDATE('$fromstatsmonth_sday-$fromstatsday_sday',INTERVAL 1 DAY) AND t1.starttime >= '$fromstatsmonth_sday-$fromstatsday_sday'";
201	147	}
202	148
203	149	if ($FG_DEBUG == 3) echo "<br>$date_clause<br>";
204
205	150
206	151
207	152	if (strpos($SQLcmd, 'WHERE') > 0) {
208	153	$FG_TABLE_CLAUSE = substr($SQLcmd,6).$date_clause;
209		}~~elseif (strpos($date_clause, 'AND') > 0)~~{
	154	} elseif (strpos($date_clause, 'AND') > 0) {
210	155	$FG_TABLE_CLAUSE = substr($date_clause,5);
211	156	}
212	157
213		if ($_POST['posted']==1){
	158	if ($_POST['posted']==1) {
214	159	$list = $instance_table -> Get_list ($DBHandle, $FG_TABLE_CLAUSE, $order, $sens, null, null, $FG_LIMITE_DISPLAY, $current_page*$FG_LIMITE_DISPLAY);
215
216	160	$list_total = $instance_table_graph -> Get_list ($DBHandle, $FG_TABLE_CLAUSE, null, null, null, null, null, null);
217	161	}
…	…
219	163
220	164	if ($FG_DEBUG == 3) echo "<br>Clause : $FG_TABLE_CLAUSE";
221		~~//$nb_record = $instance_table -> Table_count ($FG_TABLE_CLAUSE);~~
222	165	$nb_record = count($list_total);
223	166	if ($FG_DEBUG >= 1) var_dump ($list);
…	…
225	168
226	169
227		if ($nb_record<=$FG_LIMITE_DISPLAY){
	170	if ($nb_record<=$FG_LIMITE_DISPLAY) {
228	171	$nb_record_max=1;
229		}~~else~~{
	172	} else {
230	173	$nb_record_max=(intval($nb_record/$FG_LIMITE_DISPLAY)+1);
231	174	}
…	…
237	180	/*************************************************************/
238	181
239
240		$instance_table_customer = new Table("cc_card", "id, username, lastname");
241
242		$FG_TABLE_CLAUSE = "";
243		/*if ($_SESSION["is_admin"]==0){
244		$FG_TABLE_CLAUSE =" IDmanager='".$_SESSION["pr_reseller_ID"]."'";
245		}*/
246
247		$list_customer = $instance_table_customer -> Get_list ($DBHandle, $FG_TABLE_CLAUSE, "id", "ASC", null, null, null, null);
248
249		$nb_customer = count($list_customer);
250
251		?>
252
253		<?php
254		$smarty->display('main.tpl');
	182	$smarty->display('main.tpl');
	183
255	184	?>
256	185
…	…
267	196
268	197	<!-- Part for the research -->
269		<center>
	198	<center>
270	199	<FORM METHOD=POST name="myForm" ACTION="<?php echo $PHP_SELF?>?s=<?php echo $s?>&t=<?php echo $t?>&order=<?php echo $order?>&sens=<?php echo $sens?>&current_page=<?php echo $current_page?>">
271	200	<INPUT TYPE="hidden" NAME="posted" value=1>
…	…
280	209	<tr>
281	210	<td class="fontstyle_searchoptions" width="50%" valign="top">
282		<?php echo gettext("Enter the card~~number~~");?>: <INPUT TYPE="text" NAME="entercustomer" value="<?php echo $entercustomer?>" class="form_input_text">
283		<a href="#" onclick="window.open('A2B_entity_card.php?popup_select=2&popup_formname=myForm&popup_fieldname=entercustomer' , 'CardNumberSelection','scrollbars=1,width=550,height=330,top=20,left=100,scrollbars=1');"><img src="<?php echo Images_Path;?>/icon_arrow_orange.gif"></a>
	211	<?php echo gettext("Enter the card ID");?>: <INPUT TYPE="text" NAME="entercustomer" value="<?php echo $entercustomer?>" class="form_input_text">
	212	<a href="#" onclick="window.open('A2B_entity_card.php?popup_select=1&popup_formname=myForm&popup_fieldname=entercustomer' , 'CardNumberSelection','scrollbars=1,width=550,height=330,top=20,left=100,scrollbars=1');"><img src="<?php echo Images_Path;?>/icon_arrow_orange.gif"></a>
284	213	</td>
285	214	<td width="50%">
…	…
424	353	</td></tr></tbody></table>
425	354
426		<!-- ~~FIN~~ TITLE GLOBAL MINUTES //-->
	355	<!-- END TITLE GLOBAL MINUTES //-->
427	356
428	357	<table border="0" cellspacing="0" cellpadding="0" width="80%">
…	…
467	396	?>
468	397	</tr>
469		<!-- ~~FIN~~ DETAIL -->
	398	<!-- END DETAIL -->
470	399
471		<!-- ~~FIN BOUCLE~~ -->
	400	<!-- END LOOP -->
472	401
473	402	<!-- TOTAL -->
…	…
478	407	<td align="center" nowrap="nowrap"><font class="fontstyle_003"><?php echo $total_tmc_60?