invoices_customer.php

来自「asterisk用 的voip记费软件」· PHP 代码 · 共 1,200 行 · 第 1/3 页

<?php
include ("../lib/defines.php");
include ("../lib/module.access.php");
include ("../lib/smarty.php");


if (! has_rights (ACX_INVOICING)){
	Header ("HTTP/1.0 401 Unauthorized");
	Header ("Location: PP_error.php?c=accessdenied");	   
	die();
}

session_start();

getpost_ifset(array('customer', 'entercustomer', 'enterprovider', 'entertrunk', 'posted', 'Period', 'frommonth', 'fromstatsmonth', 'tomonth', 'tostatsmonth', 'fromday', 'fromstatsday_sday', 'fromstatsmonth_sday', 'today', 'tostatsday_sday', 'tostatsmonth_sday', 'dsttype', 'sourcetype', 'clidtype', 'channel', 'resulttype', 'stitle', 'atmenu', 'current_page', 'order', 'sens', 'dst', 'src', 'clid', 'fromstatsmonth_sday', 'fromstatsmonth_shour', 'tostatsmonth_sday', 'tostatsmonth_shour', 'fromstatsmonth_smin', '','tostatsmonth_smin', 'src', 'choose_currency','exporttype'));

$error_message = "";
if ($entercustomer != "")
{
	//$link = DbConnect();
	$DBHandle  = DbConnect();
	
	$QUERY = "Select id from cc_card t1 where username = $entercustomer";
	$res_user = $DBHandle -> Execute($QUERY);
	if (count($res_user) > 0 &&  isset($res_user))
		$num = $res_user -> RecordCount();
	if($num > 0)
	{	
		$userRecord = $res_user -> fetchRow();
		$customerID = $userRecord[0];	
	}
	else
	{
		$error_message = "No such card number found. Please check your card number!";
	}	
	if (($_GET[download]=="file") && $_GET[file] ) 
	{
		$value_de=base64_decode($_GET[file]);
		$dl_full = MONITOR_PATH."/".$value_de;
		$dl_name=$value_de;
	
		if (!file_exists($dl_full))
		{ 
			echo gettext("ERROR: Cannot download file ".$dl_full.", it does not exist.<br>");
			exit();
		} 
		
		header("Content-Type: application/octet-stream");
		header("Content-Disposition: attachment; filename=$dl_name");
		header("Content-Length: ".filesize($dl_full));
		header("Accept-Ranges: bytes");
		header("Pragma: no-cache");
		header("Expires: 0");
		header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
		header("Content-transfer-encoding: binary");
				
		@readfile($dl_full);
		exit();
	}
	
	
	if (!isset ($current_page) || ($current_page == "")){	
		$current_page=0; 
	}
	
	
	// this variable specifie the debug type (0 => nothing, 1 => sql result, 2 => boucle checking, 3 other value checking)
	$FG_DEBUG = 0;
	
	// The variable FG_TABLE_NAME define the table name to use
	$FG_TABLE_NAME="cc_call t1 LEFT OUTER JOIN cc_trunk t3 ON t1.id_trunk = t3.id_trunk";
	
	if ($_SESSION["is_admin"]==0){
		$FG_TABLE_NAME.=", cc_card t2";
	}
	
	// The variable Var_col would define the col that we want show in your table
	// First Name of the column in the html page, second name of the field
	$FG_TABLE_COL = array();
	
	
	/*******
	Calldate Clid Src Dst Dcontext Channel Dstchannel Lastapp Lastdata Duration Billsec Disposition Amaflags Accountcode Uniqueid Serverid
	*******/
	
	$FG_TABLE_COL[]=array (gettext("Calldate"), "starttime", "18%", "center", "SORT", "19", "", "", "", "", "", "display_dateformat");
	$FG_TABLE_COL[]=array (gettext("Source"), "src", "10%", "center", "SORT", "30");
	$FG_TABLE_COL[]=array (gettext("CalledNumber"), "calledstation", "18%", "right", "SORT", "30", "", "", "", "", "", "");
	$FG_TABLE_COL[]=array (gettext("Destination"), "destination", "18%", "center", "SORT", "30", "", "", "", "", "", "remove_prefix");
	$FG_TABLE_COL[]=array (gettext("Duration"), "sessiontime", "8%", "center", "SORT", "30", "", "", "", "", "", "display_minute");
	
	if (!(isset($customer)  &&  ($customer>0)) && !(isset($entercustomer)  &&  ($entercustomer>0))){
		$FG_TABLE_COL[]=array (gettext("CardUsed"), "username", "11%", "center", "SORT", "30");
	}
	
	$FG_TABLE_COL[]=array (gettext("Cost"), "sessionbill", "9%", "center", "SORT", "30", "", "", "", "", "", "display_2bill");
	// ??? cardID
	$FG_TABLE_DEFAULT_ORDER = "t1.starttime";
	$FG_TABLE_DEFAULT_SENS = "DESC";
		
	// This Variable store the argument for the SQL query
	
	$FG_COL_QUERY='t1.starttime, t1.src, t1.calledstation, t1.destination, t1.sessiontime  ';
	if (!(isset($customer)  &&  ($customer>0)) && !(isset($entercustomer)  &&  ($entercustomer>0))){
		$FG_COL_QUERY.=', t1.username';
	}
	$FG_COL_QUERY.=', t1.sessionbill';
	// t1.stoptime,
	if (LINK_AUDIO_FILE == 'YES') 
		$FG_COL_QUERY .= ', t1.uniqueid';
	
	$FG_COL_QUERY_GRAPH='t1.callstart, t1.duration';
	
	// The variable LIMITE_DISPLAY define the limit of record to display by page
	$FG_LIMITE_DISPLAY=500;
	
	// Number of column in the html table
	$FG_NB_TABLE_COL=count($FG_TABLE_COL);
	
	// The variable $FG_EDITION define if you want process to the edition of the database record
	$FG_EDITION=true;
	
	//This variable will store the total number of column
	$FG_TOTAL_TABLE_COL = $FG_NB_TABLE_COL;
	if ($FG_DELETION || $FG_EDITION) $FG_TOTAL_TABLE_COL++;
	
	//This variable define the Title of the HTML table
	$FG_HTML_TABLE_TITLE=" - Call Logs - ";
	
	//This variable define the width of the HTML table
	$FG_HTML_TABLE_WIDTH="70%";
	
		if ($FG_DEBUG == 3) echo "<br>Table : $FG_TABLE_NAME  	- 	Col_query : $FG_COL_QUERY";
		$instance_table = new Table($FG_TABLE_NAME, $FG_COL_QUERY);
		$instance_table_graph = new Table($FG_TABLE_NAME, $FG_COL_QUERY_GRAPH);
	
	
	if ( is_null ($order) || is_null($sens) ){
		$order = $FG_TABLE_DEFAULT_ORDER;
		$sens  = $FG_TABLE_DEFAULT_SENS;
	}
	
	if ($posted==1){
	  
	  function do_field($sql,$fld,$dbfld){
			$fldtype = $fld.'type';
			global $$fld;
			global $$fldtype;		
			//echo "<br>$fldtype=".$$fldtype."<br>";
			if ($$fld){
					if (strpos($sql,'WHERE') > 0){
							$sql = "$sql AND ";
					}else{
							$sql = "$sql WHERE ";
					}
					$sql = "$sql t1.$dbfld";
					if (isset ($$fldtype)){                
							switch ($$fldtype) {							
					case 1:	$sql = "$sql='".$$fld."'";  break;
					case 2: $sql = "$sql LIKE '".$$fld."%'";  break;
					case 3: $sql = "$sql LIKE '%".$$fld."%'";  break;
					case 4: $sql = "$sql LIKE '%".$$fld."'";  break;
					case 5:	$sql = "$sql <> '".$$fld."'";  
				}
					}else{ $sql = "$sql LIKE '%".$$fld."%'"; }
			}
			return $sql;
	  }  
	  $SQLcmd = '';
	  
	  $SQLcmd = do_field($SQLcmd, 'src', 'src');
	  $SQLcmd = do_field($SQLcmd, 'dst', 'calledstation');
	  
	}
	
	$date_clause='';
	// Period (Month-Day)
	if (DB_TYPE == "postgres"){		
			$UNIX_TIMESTAMP = "";
	}else{
			$UNIX_TIMESTAMP = "UNIX_TIMESTAMP";
	}
	
	  
	if (strpos($SQLcmd, 'WHERE') > 0) { 
		$FG_TABLE_CLAUSE = substr($SQLcmd,6).$date_clause; 
	}elseif (strpos($date_clause, 'AND') > 0){
		$FG_TABLE_CLAUSE = substr($date_clause,5); 
	}
	
	if (isset($entercustomer)  &&  ($entercustomer>0)){
		if (strlen($FG_TABLE_CLAUSE)>0) $FG_TABLE_CLAUSE.=" AND ";
		$FG_TABLE_CLAUSE.="t1.username='$entercustomer'";
	}
	
	if (strlen($FG_TABLE_CLAUSE)>0)
	{
		$FG_TABLE_CLAUSE.=" AND ";
	}
	$FG_TABLE_CLAUSE .="t1.starttime >(Select CASE  WHEN max(cover_enddate) IS NULL THEN '0001-01-01 01:00:00' ELSE max(cover_enddate) END from cc_invoices WHERE cardid = $customerID)";
	
	if ($_SESSION["is_admin"]==0){ 	
		if (strlen($FG_TABLE_CLAUSE)>0) $FG_TABLE_CLAUSE.=" AND ";
		$FG_TABLE_CLAUSE.="t1.cardID=t2.IDCust AND t2.IDmanager='".$_SESSION["pr_reseller_ID"]."'";	
	}
	
	if (!$nodisplay){
		$list = $instance_table -> Get_list ($DBHandle, $FG_TABLE_CLAUSE, $order, $sens, null, null, $FG_LIMITE_DISPLAY, $current_page*$FG_LIMITE_DISPLAY);
	}
	$_SESSION["pr_sql_export"]= "SELECT $FG_COL_QUERY FROM $FG_TABLE_NAME WHERE $FG_TABLE_CLAUSE";
	
	/************************/
	//$QUERY = "SELECT substring(calldate,1,10) AS day, sum(duration) AS calltime, count(*) as nbcall FROM cdr WHERE ".$FG_TABLE_CLAUSE." GROUP BY substring(calldate,1,10)"; //extract(DAY from calldate) 
	
	
	$QUERY = "SELECT substring(t1.starttime,1,10) AS day, sum(t1.sessiontime) AS calltime, sum(t1.sessionbill) AS cost, count(*) as nbcall FROM $FG_TABLE_NAME WHERE ".$FG_TABLE_CLAUSE." GROUP BY substring(t1.starttime,1,10) ORDER BY day"; //extract(DAY from calldate) 
	
	if (!$nodisplay)
	{
		$list_total_day  = $instance_table->SQLExec ($DBHandle, $QUERY);
		$nb_record = $instance_table -> Table_count ($DBHandle, $FG_TABLE_CLAUSE);
	
	}//end IF nodisplay
	
	// GROUP BY DESTINATION FOR THE INVOICE
	
	$QUERY = "SELECT destination, sum(t1.sessiontime) AS calltime, 
	sum(t1.sessionbill) AS cost, count(*) as nbcall FROM $FG_TABLE_NAME WHERE ".$FG_TABLE_CLAUSE." GROUP BY destination"; 
	
	if (!$nodisplay)
	{
		$list_total_destination = $instance_table->SQLExec ($DBHandle, $QUERY);			 
	}//end IF nodisplay
	
	if ($nb_record<=$FG_LIMITE_DISPLAY){ 
		$nb_record_max=1;
	}else{ 
		if ($nb_record % $FG_LIMITE_DISPLAY == 0){
			$nb_record_max=(intval($nb_record/$FG_LIMITE_DISPLAY));
		}else{
			$nb_record_max=(intval($nb_record/$FG_LIMITE_DISPLAY)+1);
		}	
	}	
	if ($FG_DEBUG == 3) echo "<br>Nb_record : $nb_record";
	if ($FG_DEBUG == 3) echo "<br>Nb_record_max : $nb_record_max";
	
	$instance_table_customer = new Table("cc_card", "id,  username, lastname");	
	$FG_TABLE_CLAUSE = "";
	if ($_SESSION["is_admin"]==0){ 	
		$FG_TABLE_CLAUSE =" IDmanager='".$_SESSION["pr_reseller_ID"]."'";	
	}	
	$list_customer = $instance_table_customer -> Get_list ($DBHandle, $FG_TABLE_CLAUSE, "id", "ASC", null, null, null, null);
	
	$nb_customer = count($list_customer);
	
	
	/*************************************************************/
	
	if ((isset($customer)  &&  ($customer>0)) || (isset($entercustomer)  &&  ($entercustomer>0))){
	
		$FG_TABLE_CLAUSE = "";
		if (isset($customer)  &&  ($customer>0)){		
			$FG_TABLE_CLAUSE =" username='$customer' ";
		}elseif (isset($entercustomer)  &&  ($entercustomer>0)){
			$FG_TABLE_CLAUSE =" username='$entercustomer' ";
		}
	
		$instance_table_customer = new Table("cc_card", "id,  username, lastname, firstname, address, city, state, country, zipcode, phone, email, fax, activated, vat, creationdate");
		$info_customer = $instance_table_customer -> Get_list ($DBHandle, $FG_TABLE_CLAUSE, "id", "ASC", null, null, null, null);
		
		// if (count($info_customer)>0){
	}
	/************************************************************/
	$date_clause='';
	
	if (strpos($date_clause, 'AND') > 0){
		$date_clause = ' WHERE '.substr($date_clause,5); 
	}
	
	$tclause = (strlen($date_clause)>1)?'AND':'WHERE';
	$date_clause .= " $tclause t1.id_cc_card = t2.id AND t2.username = '$entercustomer' AND t1.creationdate >= (Select CASE WHEN max(cover_enddate) is NULL ";
	$date_clause .= " THEN '0001-01-01 01:00:00' ELSE max(cover_enddate) END from cc_invoices) ";
	$QUERY = "SELECT substring(t1.creationdate,1,10) AS day, sum(t1.amount) AS cost, count(*) as nbcharge, t1.currency FROM cc_charge t1, cc_card t2 ".$date_clause." GROUP BY substring(t1.creationdate,1,10),t1.currency  ORDER BY day"; //extract(DAY from calldate) 
	
	if (!$nodisplay)
	{
		$list_total_day_charge = $instance_table->SQLExec ($DBHandle, $QUERY);			 
	}//end IF nodisplay
	
	$QUERY = "Select CASE WHEN max(cover_enddate) is NULL THEN '0001-01-01 01:00:00' ELSE max(cover_enddate) END from cc_invoices WHERE cardid = ".$info_customer[0][0];
	if (!$nodisplay)
	{
		$invoice_dates = $instance_table->SQLExec ($DBHandle, $QUERY);			 
	}//end IF nodisplay
	if ($invoice_dates[0][0] == '0001-01-01 01:00:00')
	{
		$invoice_dates[0][0] = $info_customer[0][14];
	}

}

$currencies_list = get_currencies();
if($exporttype!="pdf"){
	$smarty->display('main.tpl');
?>
<script language="JavaScript" type="text/JavaScript">
<!--
function MM_openBrWindow(theURL,winName,features) { //v2.0
  window.open(theURL,winName,features);
}

//-->
</script>

<!-- ** ** ** ** ** Part for the research ** ** ** ** ** -->
	<center>
	<FORM name="myForm"  METHOD=POST ACTION="<?php echo $PHP_SELF?>?s=1&t=0&order=<?php echo $order?>&sens=<?php echo $sens?>&current_page=<?php echo $current_page?>">
	<INPUT TYPE="hidden" NAME="posted" value=1>
	<INPUT TYPE="hidden" NAME="current_page" value=0>	
	<?php if (strlen($error_message)>0){ ?>
	<center><font color="#FF0000"><?php echo $error_message ?></center>
	<?php }?>	
		<table  width="95%" border="0" cellspacing="1" cellpadding="2" align="center">
			<tbody>			
			<tr>
				<td align="left" valign="top" class="bgcolor_004">
					<font class="fontstyle_003">&nbsp;&nbsp;<?php echo gettext("CUSTOMERS");?></font>
				</td>				
				<td class="bgcolor_005" align="left" >
				<table width="100%" border="0" cellspacing="0" cellpadding="0"><tr>
					<td class="fontstyle_searchoptions">
						<?php echo gettext("Enter the cardnumber");?>: <INPUT TYPE="text" NAME="entercustomer" value="<?php echo $entercustomer?>" class="form_input_text">
						<a href="#" onclick="window.open('A2B_entity_card.php?popup_select=2&popup_formname=myForm&popup_fieldname=entercustomer' , 'CardNumberSelection','width=550,height=330,top=20,left=100,scrollbars=1');"><img src="<?php echo Images_Path;?>/icon_arrow_orange.gif"></a>
					</td>
			    </tr></table></td>
			</tr>			
			<tr>
        		<td class="bgcolor_004" align="left"><font face="verdana" size="1" color="#ffffff"><b>&nbsp;&nbsp;<?php echo gettext("OPTIONS");?></b></font> </td>

				<td class="bgcolor_005" align="center" >

				<table width="100%" border="0" cellspacing="0" cellpadding="0">
				<tr>
					<td width="20%"  class="fontstyle_searchoptions">
						<?php echo gettext("RESULT");?> :  						
				   </td>
				   <td width="80%"  class="fontstyle_searchoptions">				   		
					 <?php echo gettext("Minutes");?><input type="radio" NAME="resulttype" value="min" <?php if((!isset($resulttype))||($resulttype=="min")){?>checked<?php }?>> - <?php echo gettext("Seconds");?> <input type="radio" NAME="resulttype" value="sec" <?php if($resulttype=="sec"){?>checked<?php }?>>
				  </td>
				</tr>
				<tr class="bgcolor_005">
					<td  class="fontstyle_searchoptions">
						<?php echo gettext("EXPORT FORMAT");?> : 
				   </td>
				   <td  class="fontstyle_searchoptions">
				   <?php echo gettext("See Invoice in HTML");?><input type="radio" NAME="exporttype" value="html" <?php if((!isset($exporttype))||($exporttype=="html")){?>checked<?php }?>>
					<?php echo gettext("or Export PDF");?> <input type="radio" NAME="exporttype" value="pdf" <?php if($exporttype=="pdf"){?>checked<?php }?>>
				  </td>
				</tr>
				<tr>
					<td  class="fontstyle_searchoptions">
						<?php echo gettext("CURRENCY");?> :
					</td>
					<td  class="fontstyle_searchoptions">
						
					<select NAME="choose_currency" size="1" class="form_input_select">
						<?php
							$currencies_list = get_currencies();
							foreach($currencies_list as $key => $cur_value) {
						?>
							<option value='<?php echo $key ?>' <?php if (($choose_currency==$key) || (!isset($choose_currency) && $key==strtoupper(BASE_CURRENCY))){?>selected<?php } ?>><?php echo $cur_value[1].' ('.$cur_value[2].')' ?>
							</option>
						<?php 	} ?>
					</select>
					</td>
				</tr>
				</table>
	  			</td>
    		</tr>
			<tr>
				<td class="bgcolor_004" align="left">
					
				</td>				
				<td class="bgcolor_005" align="left" >
					<center>
					<input type="image"  name="image16" align="top" border="0" src="<?php echo Images_Path;?>/button-search.gif" />						
					</center>	
				</td>
			</tr>			
		</tbody></table>
	</FORM>
</center>
<br><br>
<?php  
}else{
   require('pdf-invoices/html2pdf/html2fpdf.php');
   ob_start();

} ?>