1<?php
2if(empty($sortBy)) $sortBy="1";
3
4// ��������� ������ � ��
5// -----------------------------------------------------------------------------------------------------
6
7
8// ��������� ������� ��� ���������� ��������������� �������
9// ------------------------------------------------------------------------------------------------------
10
11// City table
12$qA1="
13CREATE TEMPORARY TABLE tmp_A (
14co smallint NULL default '0',
15duration decimal(65,0) NULL default '0'
16)
17";
18
19$qA2="
20INSERT INTO tmp_A SELECT calls.co,SUM(duration)
21from calls
22where ((calls.timeofcall>='".$from_date."')
23AND (calls.timeofcall<='".$to_date."')
24".$additionalReq."
25".VectorOfCall(MathVector(14))."
26)
27group by calls.co
28";
29
30// ��������� ������� ��� ���������� ������������� �������
31// ------------------------------------------------------------------------------------------------------
32//
33
34// Trunk table
35$qB1="
36CREATE TEMPORARY TABLE tmp_B (
37co smallint NULL default '0',
38duration decimal(65,0) NULL default '0'
39)
40";
41
42$qB2="
43INSERT INTO tmp_B SELECT calls.co,SUM(duration)
44from calls
45where ((calls.timeofcall>='".$from_date."')
46AND (calls.timeofcall<='".$to_date."')
47".$additionalReq."
48".VectorOfCall(MathVector(13))."
49)
50group by calls.co
51";
52
53// ��������� ������� ��� ���������� ������� ������� �����
54// ------------------------------------------------------------------------------------------------------
55
56// Mobile table
57$qC1="
58CREATE TEMPORARY TABLE tmp_C (
59co smallint NULL default '0',
60duration decimal(65,0) NULL default '0'
61)
62";
63
64$qC2="
65INSERT INTO tmp_C SELECT calls.co,SUM(duration)
66from calls
67where ((calls.timeofcall>='".$from_date."')
68AND (calls.timeofcall<='".$to_date."')
69".$additionalReq."
70".VectorOfCall(MathVector(11))."
71)
72group by calls.co
73";
74
75// ��������� ������� ��� ���������� ������������� �������
76// ------------------------------------------------------------------------------------------------------
77
78// Long distance calls table
79$qD1="
80CREATE TEMPORARY TABLE tmp_D (
81co smallint NULL default '0',
82duration decimal(65,0) NULL default '0'
83)
84";
85
86$qD2="
87INSERT INTO tmp_D SELECT calls.co,SUM(duration)
88from calls
89where ((calls.timeofcall>='".$from_date."')
90AND (calls.timeofcall<='".$to_date."')
91".$additionalReq."
92".VectorOfCall(MathVector(7))."
93)
94group by calls.co
95";
96
97// ��������� ��������� ��� ��������� ������ ���������� �������
98// ------------------------------------------------------------------------------------------------------
99//
100
101// For limits
102$qP1="
103CREATE TEMPORARY TABLE tmp_P (
104 internally smallint NULL default '0',
105 duration decimal(65,0) NULL default '0'
106)
107";
108
109$qP2="
110INSERT INTO tmp_P
111SELECT calls.co,COUNT(co)
112FROM calls
113where ((calls.timeofcall>='".$from_date."')
114AND (calls.timeofcall<='".$to_date."')
115".$additionalReq."
116".$vectorReq."
117
118)
119GROUP BY calls.co";
120
121$qP="
122SELECT COUNT(*) FROM tmp_P
123";
124
125
126$conn->Execute($qA1);
127if(empty($CityLine)) $conn->Execute($qA2);
128
129$conn->Execute($qB1);
130if(empty($TrunkLine)) $conn->Execute($qB2);
131
132$conn->Execute($qC1);
133if(empty($MobLine)) $conn->Execute($qC2);
134
135$conn->Execute($qD1);
136if(empty($NationalLine)) $conn->Execute($qD2);
137
138$conn->Execute($qP1);
139$conn->Execute($qP2);
140
141// �������� ��������� ��� ��������� ������ ���������� �������
142// ----------------------------------------------------------------------------
143$limitsP = getLimits($qP);
144// ----------------------------------------------------------------------------
145
146// ������� �� ��������� ������
147// ------------------------------------------------------------------------------------------------------
148//
149$q="SELECT calls.co,extlines.name,COUNT(*),
150tmp_A.duration,tmp_B.duration,tmp_C.duration,tmp_D.duration
151from calls
152LEFT JOIN extlines ON calls.co = extlines.line
153LEFT JOIN tmp_A ON calls.co=tmp_A.co
154LEFT JOIN tmp_B ON calls.co=tmp_B.co
155LEFT JOIN tmp_C ON calls.co=tmp_C.co
156LEFT JOIN tmp_D ON calls.co=tmp_D.co
157where (calls.timeofcall>='".$from_date."'
158AND (calls.timeofcall<='".$to_date."')
159".$additionalReq."
160".$vectorReq."
161)
162group by calls.co,extlines.name,tmp_A.duration,tmp_B.duration,tmp_C.duration,tmp_D.duration
163ORDER BY ".$sortBy." ".$order.$limitsP;
164
165// �������� �������� ������ ��������� ������� �������������� LIMIT
166if($cacheflush) $res = $conn->CacheFlush($q);
167$res = $conn->CacheExecute($q);
168
169$qDrop="DROP TABLE tmp_A,tmp_B,tmp_C,tmp_D,tmp_P";
170
171$qDebug="$qA1;<br><br>$qA2;<br><br><br>$qB1;<br><br>$qB2;<br><br><br>$qC1;<br><br>$qC2;<br><br><br>$qD1;<br><br>$qD2;<br><br><br>$qP1;<br><br>$qP2;<br><br><br>$q;<br><br><br>$qDrop;";
172
173if($debug) echo $qDebug."<br>";
174
175$conn->Execute($qDrop);
176
177// ���������� �� �������� ��� �������� ��������� ������ �������
178// ----------------------------------------------------------------------------
179
180if(empty($export)) pechat_ishodnyh();
181
182
183// ���� �� ������� ������� ������ � �������
184// ----------------------------------------------------------------------------
185if ($res && $res->RecordCount() > 0) {
186    if(empty($export)){
187	echo("<table cellspacing=0 cellpadding=1 border=0>
188			<tr ".$COLORS['BaseBorderTableBgcolor'].">
189				<td>
190					<table cellspacing=1 cellpadding=4 border=0>");
191	echo ("<tr ".$COLORS['BaseTablesBgcolor']." align=center valign=top>
192	<td nowrap>");
193	AddTableHeader("1",$GUI_LANG['ExternalLine'],$toprint);
194	echo ("</td>");
195
196	echo ("<td>");
197	AddTableHeader("3",$GUI_LANG['QuantityOfCalls'],$toprint);
198	echo ("</td>");
199
200	if(empty($CityLine)){
201	    echo ("<td>");
202	    AddTableHeader("4",$GUI_LANG['DurationOfCityCalls'],$toprint);
203	    echo("</td>");
204	}
205
206	if(empty($TrunkLine)){
207	    echo("<td>");
208	    AddTableHeader("5",$GUI_LANG['DurationOfTrunkCalls'],$toprint);
209	    echo("</td>");
210	}
211	if(empty($MobLine)){
212	    echo("<td>");
213	    AddTableHeader("6",$GUI_LANG['DurationOfCellularCalls'],$toprint);
214	    echo("</td>");
215	}
216
217	if(empty($NationalLine)){
218	    echo("<td>");
219	    AddTableHeader("7",$GUI_LANG['DurationOfLongDistanceCalls'],$toprint);
220	    echo("</td>");
221	}
222
223	echo("</tr>");
224    }else{
225	$expor_excel->MontaConteudo(0, 0,$GUI_LANG['ExternalLine']);
226	$expor_excel->MontaConteudo(0, 1,$GUI_LANG['QuantityOfCalls']);
227	if(empty($CityLine)){
228	    $expor_excel->MontaConteudo(0, 2,$GUI_LANG['DurationOfCityCalls']);
229	}
230	if(empty($TrunkLine)){
231	    $expor_excel->MontaConteudo(0, 3,$GUI_LANG['DurationOfTrunkCalls']);
232	}
233	if(empty($MobLine)){
234	    $expor_excel->MontaConteudo(0, 4,$GUI_LANG['DurationOfCellularCalls']);
235	}
236	if(empty($NationalLine)){
237	    $expor_excel->MontaConteudo(0, 5,$GUI_LANG['DurationOfLongDistanceCalls']);
238	}
239	$expor_excel->MontaConteudo(1, 0, "   ");
240	$expor_excel->MontaConteudo(1, 1, "   ");
241	$expor_excel->mid_sqlparaexcel();
242    }
243
244		$anyDigit=1;
245		$linha=0;
246		while ($row = $res->FetchRow()) {
247			if(!empty($row[1])) {
248			    $intPhoneDescription="($row[1])";
249			}else{
250			    $intPhoneDescription="";
251			}
252			if(empty($export)){
253			    echo ("<tr ".$COLORS['BaseTrBgcolor']." onmouseover=\"setPointer(this, $anyDigit, 'over');\" onmouseout=\"setPointer(this, $anyDigit, 'out');\" onmousedown=\"setPointer(this, $anyDigit, 'click');\">
254				    <td nowrap>
255					<a href=\"".complitLink($local_co="$row[0]",$local_type="CoDetail")."\" title=\"".$GUI_LANG['InDetails'].". ".$GUI_LANG['CallsThroughAnExternalLine']." $row[0] $intPhoneDescription.\">$row[0]</a>
256					$intPhoneDescription
257				    </td>
258				    <td>
259				        <a href=\"".complitLink($local_co="$row[0]",$local_type="CoNum")."\" title=\"".$GUI_LANG['NumbersThroughAnExternalLine']." $row[0] $intPhoneDescription\">$row[2]</a>
260				    </td>");
261			}else{
262			    $expor_excel->MontaConteudo($linha+2, 0, $row[0]." ".$intPhoneDescription);
263			    $expor_excel->MontaConteudo($linha+2, 1, $row[2]);
264			}
265			if(empty($CityLine)){
266			    if(empty($export)){
267				echo ("<td>".sumTotal($row[3],0)."</td>");
268			    }else{
269				$expor_excel->MontaConteudo($linha+2, 2, sumTotal($row[3],0));
270			    }
271			}
272			if(empty($TrunkLine)){
273			    if(empty($export)){
274				echo ("<td>".sumTotal($row[4],0)."</td>");
275			    }else{
276				$expor_excel->MontaConteudo($linha+2, 3, sumTotal($row[4],0));
277			    }
278			}
279			if(empty($MobLine)){
280			    if(empty($export)){
281				echo ("<td>".sumTotal($row[5],0)."</td>");
282			    }else{
283				$expor_excel->MontaConteudo($linha+2, 4, sumTotal($row[5],0));
284			    }
285			}
286			if(empty($NationalLine)){
287			    if(empty($export)){
288				echo ("<td>".sumTotal($row[6],0)."</td>");
289			    }else{
290				$expor_excel->MontaConteudo($linha+2, 5, sumTotal($row[6],0));
291			    }
292			}
293		    if(empty($export)){
294    			echo ("</tr>\n");
295		    }
296
297			if(!isset($InAll)) {
298				$InAll=array();
299				for($i=0;$i<7;$i++) {
300					$InAll[$i]=0;
301				}
302			}
303
304		    array($InAll);
305		    if(!empty($row[0])) $InAll[0] ++;
306		    if(!empty($row[2])) $InAll[2] += $row[2];
307		    if(!empty($row[3])) $InAll[3] += $row[3];
308		    if(!empty($row[4])) $InAll[4] += $row[4];
309		    if(!empty($row[5])) $InAll[5] += $row[5];
310		    if(!empty($row[6])) $InAll[6] += $row[6];
311
312		    $linha++;
313		    $anyDigit++;
314		}
315
316// ������� totalTableFooter() ������� �������� ��������. � �������� ��������� ��������� ����� SQL �������
317// ������� sumTotal() ����������� ����� ������ � ���������� �����,����� � ������.
318// ------------------------------------------------------------------------------------------------------
319
320    if(empty($export)){
321	echo ("<tr ".$COLORS['AltogetherTrBgcolor']."><td>".$GUI_LANG['Total'].": <b>".$InAll[0]."</b></td>");
322	echo ("<td nowrap><b>".$InAll[2]."</b></td>");
323	if(empty($CityLine))echo ("<td nowrap>".sumTotal($InAll[3],1)."</td>");
324	if(empty($TrunkLine))echo ("<td nowrap>".sumTotal($InAll[4],1)."</td>");
325	if(empty($MobLine))echo ("<td nowrap>".sumTotal($InAll[5],1)."</td>");
326	if(empty($NationalLine))echo ("<td nowrap>".sumTotal($InAll[6],1)."</td>");
327	print ("</tr>\n");
328	if($pages > 1 or $debug){
329	    echo ("<tr ".$COLORS['TotalTrBgcolor']."><td>".$GUI_LANG['Altogether'].":</td>");
330	    echo ("<td nowrap><b>".totalTableFooter('4',1)."</b></td>");
331	    if(empty($CityLine))echo ("<td nowrap>".sumTotal(totalTableFooter('5',1),1)."</td>");
332	    if(empty($TrunkLine))echo ("<td nowrap>".sumTotal(totalTableFooter('6',1),1)."</td>");
333	    if(empty($MobLine))echo ("<td nowrap>".sumTotal(totalTableFooter('7',1),1)."</td>");
334	    if(empty($NationalLine))echo ("<td nowrap>".sumTotal(totalTableFooter('8',1),1)."</td>");
335	    print ("</tr>\n");
336	}
337	print ("</table>\n\n </td></tr></table>");
338    }else{
339	$expor_excel->MontaConteudo($linha+3, 0, $GUI_LANG['Total'].": ".$InAll[0]);
340	$expor_excel->MontaConteudo($linha+3, 1, $InAll[2]);
341	if(empty($CityLine)) $expor_excel->MontaConteudo($linha+3, 2, sumTotal($InAll[3],0));
342	if(empty($TrunkLine)) $expor_excel->MontaConteudo($linha+3, 3, sumTotal($InAll[4],0));
343	if(empty($MobLine)) $expor_excel->MontaConteudo($linha+3, 4, sumTotal($InAll[5],0));
344	if(empty($NationalLine)) $expor_excel->MontaConteudo($linha+3, 5, sumTotal($InAll[6],0));
345
346	if($pages > 1 or $debug){
347	    $expor_excel->MontaConteudo($linha+4, 0, $GUI_LANG['Altogether'].":");
348	    $expor_excel->MontaConteudo($linha+4, 1, totalTableFooter('4',1));
349	    if(empty($CityLine)) $expor_excel->MontaConteudo($linha+4, 2, sumTotal(totalTableFooter('5',1),0));
350	    if(empty($TrunkLine)) $expor_excel->MontaConteudo($linha+4, 3, sumTotal(totalTableFooter('6',1),0));
351	    if(empty($MobLine)) $expor_excel->MontaConteudo($linha+4, 4, sumTotal(totalTableFooter('7',1),0));
352	    if(empty($NationalLine)) $expor_excel->MontaConteudo($linha+4, 5, sumTotal(totalTableFooter('8',1),0));
353	}
354    }
355
356    if(!empty($export)) $expor_excel->GeraArquivo();
357
358// ------------------------------------------------------------------------------------------------------
359
360}else{
361	echo "<font size=+1>".$GUI_LANG['NoSuchData']."</font>";
362}
363?>
364