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