invoice.php

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

<?php


function EmailInvoice($id, $invoice_type = 1)
{
	//getpost_ifset(array('customer', 'posted', 'Period', 'cardid','exporttype','choose_billperiod','id','invoice_type'));
	if ($invoice_type == "")
	{
		$invoice_type = 1;
	}
	if ($invoice_type == 1)
	{
		$cardid = $id;
		if($cardid == "" )
		{
			exit("Invalid ID");
		}
	}
	if ( $invoice_type == 2)
	{
		if(($id == "" || !is_numeric($id)))
		{
			exit(gettext("Invalid ID"));
		}
	}
	if ($invoice_type == 1)
	{
		$invoice_heading = gettext("Unbilled Details");
		$invocie_top_heading = gettext("Unbilled Invoice Details for Card Number");	
	}
	else
	{
		$invoice_heading = gettext("Billed Details");
		$invocie_top_heading = gettext("Billed Invoice Details for Card Number");	
	}
	
	
	$DBHandle = DbConnect();
	$num = 0;
	if ($invoice_type == 1)
	{
		$QUERY = "Select username, vat, t1.id from cc_card t1 where t1.id = $cardid";
	}
	else
	{
		$QUERY = "Select username, vat, t1.id from cc_card t1, cc_invoices t2 where t1.id = t2.cardid and t2.id = $id";
	}
	$res_user = $DBHandle -> Execute($QUERY);
	if ($res_user)
		$num = $res_user -> RecordCount();
	
	if($num > 0)
	{
		$userRecord = $res_user -> fetchRow();
		$customer = $userRecord[0];
		$vat = $userRecord[1];
		$customerID = $userRecord[2];	
	}
	else
	{
		exit(gettext("No User found"));
	}
	
	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";
	
	// 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();
	
	$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");
	
	$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';
	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);
	
	
	//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++;
	
		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;		
			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";
	}
	
	
	$lastdayofmonth = date("t", strtotime($tostatsmonth.'-01'));
	
	if ($Period=="Month"){
			
			
			if ($frommonth && isset($fromstatsmonth)) $date_clause.=" AND $UNIX_TIMESTAMP(t1.starttime) >= $UNIX_TIMESTAMP('$fromstatsmonth-01')";
			if ($tomonth && isset($tostatsmonth)) $date_clause.=" AND $UNIX_TIMESTAMP(t1.starttime) <= $UNIX_TIMESTAMP('".$tostatsmonth."-$lastdayofmonth 23:59:59')"; 
			
	}else{
			if ($fromday && isset($fromstatsday_sday) && isset($fromstatsmonth_sday) && isset($fromstatsmonth_shour) && isset($fromstatsmonth_smin) ) $date_clause.=" AND $UNIX_TIMESTAMP(t1.starttime) >= $UNIX_TIMESTAMP('$fromstatsmonth_sday-$fromstatsday_sday $fromstatsmonth_shour:$fromstatsmonth_smin')";
			if ($today && isset($tostatsday_sday) && isset($tostatsmonth_sday) && isset($tostatsmonth_shour) && isset($tostatsmonth_smin)) $date_clause.=" AND $UNIX_TIMESTAMP(t1.starttime) <= $UNIX_TIMESTAMP('$tostatsmonth_sday-".sprintf("%02d",intval($tostatsday_sday))." $tostatsmonth_shour:$tostatsmonth_smin')";
	}
	
	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($customer)  &&  ($customer>0)){
		if (strlen($FG_TABLE_CLAUSE)>0) $FG_TABLE_CLAUSE.=" AND ";
		$FG_TABLE_CLAUSE.="t1.username='$customer'";
	}else{
		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 ";
	}
	
	if ($invoice_type == 1)
	{
		$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 = '$cardid')";
	}
	else
	{
		$FG_TABLE_CLAUSE.="t1.starttime >(Select cover_startdate  from cc_invoices where id ='$id') AND t1.stoptime <(Select cover_enddate from cc_invoices where id ='$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(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
	
	/************************************************ DID Billing Section *********************************************/
	// Fixed + Dial = 0
	// Fixed = 1
	// Dail = 2
	// Free = 3
	
	
	// 1. Billing Type:: All DID Calls that have DID Type 0 and 2
	// 1. Billing Type:: All DID Calls that have DID Type 0 and 2
	if ($invoice_type == 1)
	{
		$QUERY = "SELECT t1.amount, t1.creationdate, t1.description, t3.countryname, t2.did, t1.currency ".
		" FROM cc_charge t1 LEFT JOIN (cc_did t2, cc_country t3 ) ON ( t1.id_cc_did = t2.id AND t2.id_cc_country = t3.id ) ".
		" WHERE (t1.chargetype = 1 OR t1.chargetype = 2) AND t1.id_cc_card = ".$cardid.
		" AND t1.creationdate >(Select CASE  WHEN max(cover_enddate) IS NULL THEN '0001-01-01 01:00:00' ELSE max(cover_enddate) END from cc_invoices)";
	}
	else
	{
		$QUERY = "SELECT t1.amount, t1.creationdate, t1.description, t3.countryname, t2.did, t1.currency ".
		" FROM cc_charge t1 LEFT JOIN (cc_did t2, cc_country t3 ) ON ( t1.id_cc_did = t2.id AND t2.id_cc_country = t3.id ) ".
		" WHERE (t1.chargetype = 2 OR t1.chargetype = 1) AND t1.id_cc_card = ".$customerID.
		" AND t1.creationdate > (Select cover_startdate  from cc_invoices where id ='$id') AND t1.creationdate <(Select cover_enddate from cc_invoices where id ='$id')";
	}
	 
	 
	if (!$nodisplay)
	{
		$list_total_did  = $instance_table->SQLExec ($DBHandle, $QUERY);
	}//end IF nodisplay
	
	/************************************************ END DID Billing Section *********************************************/
	/*************************************************CHARGES SECTION START ************************************************/
	
	// Charge Types
	
	// Connection charge for DID setup = 1
	// Monthly Charge for DID use = 2
	// Subscription fee = 3
	// Extra charge =  4
	if ($invoice_type == 1)
	{
		$QUERY = "SELECT t1.id_cc_card, t1.iduser, t1.creationdate, t1.amount, t1.chargetype, t1.id_cc_did, t1.currency, t1.description" .
		" FROM cc_charge t1, cc_card t2 WHERE (t1.chargetype <> 1 AND t1.chargetype <> 2) " .
		" AND t2.username = '$customer' AND t1.id_cc_card = t2.id AND t1.creationdate >= (Select CASE WHEN max(cover_enddate) is NULL " .
		" THEN '0001-01-01 01:00:00' ELSE max(cover_enddate) END from cc_invoices) Order by t1.creationdate";
	}
	else
	{
		$QUERY = "SELECT t1.id_cc_card, t1.iduser, t1.creationdate, t1.amount, t1.chargetype, t1.id_cc_did, t1.currency, t1.description" .
		" FROM cc_charge t1, cc_card t2 WHERE (t1.chargetype <> 2 AND t1.chargetype <> 1)" .
		" AND t2.username = '$customer' AND t1.id_cc_card = t2.id AND " .
		" t1.creationdate >(Select cover_startdate  from cc_invoices where id ='$id') " .
		" AND t1.creationdate <(Select cover_enddate  from cc_invoices where id ='$id')";
	}
	//echo "<br>".$QUERY."<br>";
	
	if (!$nodisplay)
	{
		$list_total_charges = $instance_table->SQLExec ($DBHandle, $QUERY);
	}//end IF nodisplay
	/*************************************************CHARGES SECTION END ************************************************/
	
	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";
	
	/*************************************************************/
	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, creationdate");
		$info_customer = $instance_table_customer -> Get_list ($DBHandle, $FG_TABLE_CLAUSE, "id", "ASC", null, null, null, null);
	
	}
	
	if($invoice_type == 1)
	{
		$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 = ".$cardid;
	}
	else
	{
		$QUERY = "Select cover_enddate,cover_startdate  from cc_invoices where id ='$id'";
	}
	if (!$nodisplay){
		$invoice_dates = $instance_table->SQLExec ($DBHandle, $QUERY);			
		if ($invoice_dates[0][0] == '0001-01-01 01:00:00')
		{
			$invoice_dates[0][0] = $info_customer[0][13];
		}
	}//end IF nodisplay
	?>
	<?php

		require('../Public/pdf-invoices/html2pdf/html2fpdf.php');
		ob_start();
	
	?>
	<?php 
	$currencies_list = get_currencies();
	
	//For DID DIAL & Fixed + Dial
	$totalcost = 0;
	if (is_array($list_total_destination) && count($list_total_destination)>0){
		$mmax=0;
		$totalcall=0;
		$totalminutes=0;	
		foreach ($list_total_destination as $data){	
			if ($mmax < $data[1]) $mmax=$data[1];
			$totalcall+=$data[3];
			$totalminutes+=$data[1];
			$totalcost+=$data[2];	
		}
	}
	?>
	<table cellpadding="0"  align="center">
	<tr>
	<td align="center">
	<img src="<?php echo Images_Path;?>/asterisk01.jpg" align="middle">
	</td>
	</tr>
	</table>
	<br>
	<center><h4><font color="#FF0000"><?php echo $invocie_top_heading; ?>&nbsp;<?php echo $info_customer[0][1] ?> </font></h4></center>
	<br>
	<br>
		
		<table cellspacing="0" cellpadding="2" align="center" width="80%" >
		 
		  <tr>
			<td colspan="2" bgcolor="#FFFFCC"><font size="5" color="#FF0000"><?php echo $invoice_heading; ?></font></td>
		  </tr>
		  <tr>
			<td valign="top" colspan="2"></td>
		  </tr>	 
		<tr>
		  <td width="35%">&nbsp; </td>
		  <td >&nbsp; </td>
		</tr>
		<tr>
		  <td width="35%" ><font color="#003399"><?php echo gettext("Name")?>&nbsp; :</font> </td>
		  <td  ><font color="#003399"><?php echo $info_customer[0][3] ." ".$info_customer[0][2] ?></font></td>
		</tr>
		<tr>
		  <td width="35%" ><font color="#003399"><?php echo gettext("Card Number")?>&nbsp; :</font></td>
		  <td  ><font color="#003399"><?php echo $info_customer[0][1] ?></font> </td>
		</tr>
		<?php if ($invoice_type == 1){ ?>
		<tr>
		  <td width="35%" ><font color="#003399"><?php echo gettext("From Date")?>&nbsp; :</font></td>
		  <td><font color="#003399"><?php echo display_dateonly($invoice_dates[0][0]);?> </font></td>
		</tr>  		
		  <?php }else{ ?>
		<tr>
		 <td width="35%" ><font color="#003399"><?php echo gettext("From Date")?>&nbsp; :</font></td>
		 <td  ><font color="#003399"><?php echo display_dateonly($invoice_dates[0][1]);?> </font></td>
		</tr>
		<tr>
		  <td width="35%" ><font color="#003399"><?php echo gettext("To Date")?>&nbsp; :</font></td>
		  <td><font color="#003399"><?php echo display_dateonly($invoice_dates[0][0]);?> </font></td>
		</tr>  
		  <?php } ?>
		  </table>
		  <table align="center" width="80%">
		  	<?php 
			if (is_array($list_total_destination) && count($list_total_destination)>0){
			?>
				<tr>