</tr> <tr> <td align="right">City:</td> <td align="left"><?php echo $row->customer_city;?></td> </tr> <tr> <td align="right">State:</td> <td align="left"><?php echo $row->customer_state;?></td> </tr> <tr> <td align="right">Zipcode:</td> <td align="left"><?php echo $row->customer_zip;?></td> </tr> <tr> <td align="right">Country:</td> <td align="left"><?php echo country_any_cell($row->customer_country, 'countryname');?></td> </tr> <tr> <td align="right">Phone:</td> <td align="left"><?php echo $row->customer_phone_prefix.'-'.$row->customer_phone;?></td> </tr> <tr> <td align="right">Timezone:</td> <td align="left"><?php echo timezone_any_cell($row->customer_timezone, 'timezone_location').' '.timezone_any_cell($row->customer_timezone, 'gmt');?></td> </tr> <tr> <td align="right">Group:</td> <td align="left"><?php echo group_any_cell($row->customer_rate_group, 'group_name');?></td> </tr> <tr> <td align="right">Billing Email:</td>
function get_country_prefix()
{
$id = $this->input->post('id');
echo country_any_cell($id, 'countryprefix');
}
<tr class="bottom_link">
<td height="20" width="20%" align="center">Country</td>
<td width="20%" align="center">Total Minutes</td>
<td width="20%" align="center">Total Calls</td>
<td width="20%" align="center">Total Sell Cost</td>
<td width="20%" align="center">Total Buy Cost</td>
</tr>
<tr><td colspan="5" id="shadowDiv" style="height:5px;margin-top:-1px"></td></tr>
<?php if($countries->num_rows() > 0) {?>
<?php foreach ($countries->result() as $row): ?>
<tr class="main_text">
<td align="center" height="30"><?php echo country_any_cell($row->id, 'countryname'); ?></td>
<!-- Dynamic calls started from here for each field -->
<!--**************GET TOTAL MINUTES FOR COUNTRY ******************-->
<?php
$total_duration = $this->cdr_model->get_country_total_mins($row->id, $filter_date_from, $filter_date_to);
if($total_duration == '')
{
$total_duration = 0;
}
if($filter_display_results == 'sec')
{
$total_duration_in_min_sec = $total_duration; //be defualt it is in sec
function generate_manual_invoice()
{
set_time_limit(0);
$this->load->library('user_agent');
if($this->agent->referrer() == '')
{
redirect ('reseller/billing/invoices/');
}
if ($urlParts = parse_url($this->agent->referrer()))
{
$baseUrl = $urlParts["scheme"] . "://" . $urlParts["host"] . $urlParts["path"];
if($baseUrl != ''.base_url().'reseller/billing/invoices' && $baseUrl != ''.base_url().'reseller/billing/invoices/')
{
redirect ('reseller/billing/invoices/');
}
}
$customer_id = $this->input->post('new_inv_customer');
$misc_charges = $this->input->post('misc_charges');
if($customer_id == '')
{
redirect ('reseller/billing/invoices/');
}
if(!is_numeric($misc_charges)) //invalid value redirect
{
redirect ('reseller/billing/invoices/');
}
if(!is_numeric($customer_id)) //invalid value redirect
{
redirect ('reseller/billing/invoices/');
}
$misc_charges = round($misc_charges, 4);
$current_date = date('Y-m-d');
$current_date_time = strtotime($current_date);
//get all customers whos invoicing date is today
$sql = "SELECT * FROM customers WHERE customer_id = '".$customer_id."' ";
$query = $this->db->query($sql);
if($query->num_rows() > 0) //if we found the customer
{
$row = $query->row();
$billing_cycle = $row->customer_billing_cycle;
$is_prepaid = $row->customer_prepaid;
//other info
$customer_name = $row->customer_firstname.' '.$row->customer_lastname;
$customer_address = $row->customer_address;
$customer_city_country = $row->customer_city.', '.country_any_cell($row->customer_country, 'countryname');
$customer_contact = $row->customer_phone_prefix.$row->customer_phone;
$customer_send_cdr = $row->customer_send_cdr;
$customer_email = $row->customer_billing_email;
$customer_parent = $row->parent_id;
$customer_grand_parent = $row->grand_parent_id;
//get the last invoice id generated for particular customer
$sql2 = "SELECT MAX(id) AS id FROM invoices WHERE customer_id = '".$customer_id."' ";
$query2 = $this->db->query($sql2);
$row2 = $query2->row();
if($row2->id != '') //if there is invoice generated last time
{
$sql3 = "SELECT to_date FROM invoices WHERE id = '".$row2->id."' ";
$query3 = $this->db->query($sql3);
$row3 = $query3->row();
$date_from = $row3->to_date;
}
else //this is the first invoice
{
$m= date("m");
$d= date("d");
$y= date("Y");
if($billing_cycle == 'daily')
{
$d = date('Y-m-d',mktime(0,0,0,$m,($d-1),$y));
$date_from = strtotime($d);
}
else if($billing_cycle == 'weekly')
{
$d = date('Y-m-d',mktime(0,0,0,$m,($d-7),$y));
$date_from = strtotime($d);
}
else if($billing_cycle == 'bi_weekly')
{
$d = date('Y-m-d',mktime(0,0,0,$m,($d-14),$y));
$date_from = strtotime($d);
}
else if($billing_cycle == 'monthly')
{
$d = date('Y-m-d',mktime(0,0,0,$m,($d-30),$y));
$date_from = strtotime($d);
}
}
// to 2011-12-16 00:00:00 (less or equal to 2011-12-16 00:00:00)
// from 2011-12-15 00:00:00 (greater than from starting from 2011-12-15 00:00:01)
$cdr_from = $date_from * 1000000; //convert into micro seconds
$cdr_to = $current_date_time * 1000000; //convert into micro seconds
if(customer_any_cell($customer_id, 'reseller_level') == '2') //if the customer is reseller of level 2 created by reseller 3
{
//get total invoice amount , total calls of reseller 2
$sql4 = "SELECT SUM(total_sell_cost) as total_invoice_amount, COUNT(*) as total_calls FROM cdr WHERE customer_id = '".$customer_id."' AND (hangup_cause = 'ALLOTTED_TIMEOUT' || hangup_cause = 'NORMAL_CLEARING') AND billsec > 0 AND parent_id = '0' AND created_time > '".sprintf("%.0f", $cdr_from)."' AND created_time <= '".sprintf("%.0f", $cdr_to)."'";
$query4 = $this->db->query($sql4);
$row4 = $query4->row();
//get total invoice amount , total calls of all customers of reseller 2
$sqlReseller = "SELECT SUM(total_reseller_sell_cost) as total_reseller_invoice_amount, COUNT(*) as total_calls FROM cdr WHERE parent_reseller_id = '".$customer_id."' AND (hangup_cause = 'ALLOTTED_TIMEOUT' || hangup_cause = 'NORMAL_CLEARING') AND billsec > 0 AND parent_id = '0' AND created_time > '".sprintf("%.0f", $cdr_from)."' AND created_time <= '".sprintf("%.0f", $cdr_to)."'";
$queryReseller = $this->db->query($sqlReseller);
$rowReseller = $queryReseller->row();
$total_invoice_amount = $row4->total_invoice_amount + $rowReseller->total_reseller_invoice_amount;
if($total_invoice_amount == '')
{
$total_invoice_amount = 0;
}
$actual_invoice_amount = $total_invoice_amount;
$total_invoice_amount = $total_invoice_amount + $misc_charges;
$total_calls_made = $row4->total_calls + $rowReseller->total_calls;
}
else //if the customer is normal customer created by reseller 3 or reseller 2 (for both same logic)
{
//sum total invoice amount from cdr between 2 dates
$sql4 = "SELECT SUM(total_sell_cost) as total_invoice_amount, COUNT(*) as total_calls FROM cdr WHERE customer_id = '".$customer_id."' AND (hangup_cause = 'ALLOTTED_TIMEOUT' || hangup_cause = 'NORMAL_CLEARING') AND billsec > 0 AND created_time > '".sprintf("%.0f", $cdr_from)."' AND created_time <= '".sprintf("%.0f", $cdr_to)."'";
$query4 = $this->db->query($sql4);
$row4 = $query4->row();
$total_invoice_amount = $row4->total_invoice_amount;
if($total_invoice_amount == '')
{
$total_invoice_amount = 0;
}
$actual_invoice_amount = $total_invoice_amount;
$total_invoice_amount = $total_invoice_amount + $misc_charges;
$total_calls_made = $row4->total_calls;
}
$status = "pending";
if($total_invoice_amount == 0)
{
$status = "paid";
}
else if($is_prepaid == '1')
{
$status = "paid";
}
//generate random invoice number
$check = 0;
do {
$invoice_number = 'INV-'.rand(1,999).rand(1,999).rand(1,99);
$check_invoice_number_existis = $this->billing_model->check_invoice_number_existis($invoice_number);
if($check_invoice_number_existis == 0)
{
$check = 1;
}
} while ($check == 0);
$due_date = $current_date_time + 604800; //generated date + 7 days
//now we are all good to insert the invoice
$sql5 = "INSERT INTO invoices (invoice_id , customer_id, from_date, to_date, total_charges, total_calls, customer_prepaid, invoice_generated_date, due_date, status, misc_charges, parent_id, grand_parent_id) VALUES ('".$invoice_number."', '".$customer_id."', '".$date_from."', '".$current_date_time."', '".$total_invoice_amount."', '".$total_calls_made."' ,'".$is_prepaid."', '".$current_date_time."', '".$due_date."', '".$status."', '".$misc_charges."', '".$customer_parent."', '".$customer_grand_parent."')";
$query5 = $this->db->query($sql5);
//update the next invoice date for customer
$m= date("m");
$d= date("d");
$y= date("Y");
if($billing_cycle == 'daily')
{
$d = date('Y-m-d',mktime(0,0,0,$m,($d+1),$y));
$next_invoice_date = strtotime($d);
}
else if($billing_cycle == 'weekly')
{
$d = date('Y-m-d',mktime(0,0,0,$m,($d+7),$y));
$next_invoice_date = strtotime($d);
}
else if($billing_cycle == 'bi_weekly')
{
$d = date('Y-m-d',mktime(0,0,0,$m,($d+14),$y));
$next_invoice_date = strtotime($d);
}
else if($billing_cycle == 'monthly')
{
$d = date('Y-m-d',mktime(0,0,0,$m,($d+30),$y));
$next_invoice_date = strtotime($d);
}
$sql6 = "UPDATE customers SET next_invoice_date = '".$next_invoice_date."' WHERE customer_id = '".$customer_id."'";
$query6 = $this->db->query($sql6);
//generate pdf invoice and save
//get from fields
$getFrom = "SELECT * FROM customers WHERE customer_id = '".$this->session->userdata('customer_id')."' ";
$queryFrom = $this->db->query($getfrom);
$rowFrom = $queryFrom->row();
$obj = new $this->pdf;
// set document information
$obj->SetSubject('INVOICE');
$obj->SetKeywords('DigitalLinx, INVOICE, CDR');
// add a page
$obj->SetPrintHeader(false);
$obj->SetPrintFooter(false);
$obj->AddPage();
$obj->SetFont('helvetica', '', 6);
$tbl = '<table width="100%" cellspacing="0" cellpadding="0" border="0" style="margin:0px;margin-left:auto;margin-right:auto;padding:5px">
<tbody>
<tr>
<td align="center" colspan="2">
<table width="100%" style="background-color:#dadada;padding:5px;border:5px solid #cccccc;">
<tbody><tr>
<td align="right" style="font-weight:bold;color:red" colspan="2">Invoice # '.$invoice_number.'</td>
</tr>
<tr>
<td align="right" style="font-weight:bold;" colspan="2">Date: '.$current_date.'</td>
</tr>
<tr>
<td align="right" style="font-weight:bold;" colspan="2">Due Date: '.date('Y-m-d H:i:s', $due_date).'</td>
</tr>
</tbody></table>
</td>
</tr>
<tr>
<td height="30px" colspan="2"> </td>
</tr>
<tr>
<td height="30px" colspan="2"> </td>
</tr>
<tr>
<td align="center">
<table width="100%" style="background-color:#dadada;padding:5px;border:5px solid #cccccc;">
<tbody>
<tr>
<td style="background-color:#777777;color:#fff;padding-left:10px" align="left">
From:
</td>
</tr>
<tr>
<td align="left">
<table width="100%">
<tbody><tr><td>'.$rowFrom->customer_company.'</td></tr>
<tr><td>'.$rowFrom->customer_address.'</td></tr>
<tr><td>'.$rowFrom->customer_city.', '.country_any_cell($rowFrom->customer_country, 'countryname').'</td></tr>
<tr><td>Phone: +'.$rowFrom->customer_phone_prefix.'-'.$rowFrom->customer_phone.'</td></tr>
<tr><td>Email: '.$rowFrom->customer_contact_email.'</td></tr>
</tbody></table>
</td>
</tr>
</tbody></table>
</td>
<td align="center">
<table width="100%" style="background-color:#dadada;padding:5px;border:5px solid #cccccc;">
<tbody>
<tr>
<td style="background-color:#777777;color:#fff;padding-left:10px" align="left">
To:
</td>
</tr>
<tr>
<td align="left">
<table width="100%" style="float:right">
<tbody><tr><td>'.$customer_name.'</td></tr>
<tr><td>'.$customer_address.'</td></tr>
<tr><td>'.$customer_city_country.'</td></tr>
<tr><td>Contact: '.$customer_contact.'</td></tr>
</tbody></table>
</td>
</tr>
</tbody></table>
</td>
</tr>
<tr>
<td height="30px" colspan="2"> </td>
</tr>
<tr>
<td align="center">
<table width="100%" style="background-color:#dadada;padding:5px;border:5px solid #cccccc;">
<tbody>
<tr>
<td style="background-color:#777777;color:#fff;padding-left:10px" align="left">
Billing Period From:
</td>
</tr>
<tr>
<td align="left">
'.date('Y-m-d', $date_from).'
</td>
</tr>
</tbody></table>
</td>
<td align="center">
<table width="100%" style="background-color:#dadada;padding:5px;border:5px solid #cccccc;">
<tbody>
<tr>
<td style="background-color:#777777;color:#fff;padding-left:10px" align="left">
Billing Period To:
</td>
</tr>
<tr>
<td align="left">
'.$current_date.'
</td>
</tr>
</tbody></table>
</td>
</tr>
<tr>
<td height="30px" colspan="2"> </td>
</tr>
<tr>
<td height="30px" colspan="2"> </td>
</tr>
<tr>
<td align="center" colspan="2">
<table width="100%" style="border: 5px solid #dadada;">
<tbody><tr style="background-color:#777777;color:#fff">
<td>Total Calls</td>
<td>Total Charges</td>
<td align="center">Total</td>
</tr>
<tr style="background-color:#ccc">
<td>'.$total_calls_made.'</td>
<td>'.$actual_invoice_amount.'</td>
<td align="center">'.$actual_invoice_amount.'</td>
</tr>
<tr style="background-color:#ccc">
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr style="background-color:#ccc">
<td> </td>
<td align="right" style="font-weight:bold;">Tax</td>
<td style="font-weight:bold;text-align:center;">N/A</td>
</tr>
<tr style="background-color:#ccc">
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr style="background-color:#ccc">
<td> </td>
<td align="right" style="font-weight:bold;">Other</td>
<td style="font-weight:bold;text-align:center;">'.$misc_charges.'</td>
</tr>
<tr style="background-color:#ccc">
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr style="background-color:#ccc">
<td> </td>
<td align="right" style="font-weight:bold;color:#3172C6">Total</td>
<td style="font-weight:bold;color:#3172C6;text-align:center">'.$total_invoice_amount.'</td>
</tr>
</tbody></table>
</td>
</tr>
<tr>
<td height="30px" colspan="2"> </td>
</tr>
<tr>
<td style="text-align: justify; font-style: italic; color: rgb(136, 136, 136); padding: 55px;" colspan="2">
Please contact us immediately if you have any concerns regarding this invoice.<br/><br/>
Thanks,<br/>
'.$rowFrom->customer_company.'<br/>
Phone: +'.$rowFrom->customer_phone_prefix.'-'.$rowFrom->customer_phone.'<br/>
Email: '.$rowFrom->customer_contact_email.'
</td>
</tr>
</tbody></table>';
$obj->writeHTML($tbl, true, false, false, false, '');
//Close and output PDF document
$obj->Output('media/invoices/'.$invoice_number.'.pdf', 'F');
//create cdr pdf
$objcdr = new $this->pdf;
// set document information
$objcdr->SetSubject('INVOICE CDR');
$objcdr->SetKeywords('DigitalLinx, INVOICE, CDR');
// add a page
$objcdr->SetPrintHeader(false);
$objcdr->SetPrintFooter(false);
$objcdr->AddPage();
$objcdr->SetFont('helvetica', '', 6);
if(customer_any_cell($customer_id, 'reseller_level') == '2')
{
$sql6 = "SELECT * FROM cdr WHERE (customer_id = '".$customer_id."' || parent_reseller_id = '".$customer_id."') AND (hangup_cause = 'ALLOTTED_TIMEOUT' || hangup_cause = 'NORMAL_CLEARING') AND billsec > 0 AND parent_id = '0' AND created_time > '".sprintf("%.0f", $cdr_from)."' AND created_time <= '".sprintf("%.0f", $cdr_to)."'";
$query6 = $this->db->query($sql6);
}
else
{
$sql6 = "SELECT * FROM cdr WHERE customer_id = '".$customer_id."' AND (hangup_cause = 'ALLOTTED_TIMEOUT' || hangup_cause = 'NORMAL_CLEARING') AND billsec > 0 AND created_time > '".sprintf("%.0f", $cdr_from)."' AND created_time <= '".sprintf("%.0f", $cdr_to)."'";
$query6 = $this->db->query($sql6);
}
$tbl_cdr = '<table width="100%" cellspacing="0" cellpadding="0" border="0" style="margin:0px;margin-left:auto;margin-right:auto;padding:5px">
<tbody>
<tr>
<td align="center" colspan="2">
<table width="100%" style="background-color:#dadada;padding:5px;border:5px solid #cccccc;">
<tbody><tr>
<td align="right" style="font-weight:bold;color:red" colspan="2">Invoice # '.$invoice_number.'</td>
</tr>
<tr>
<td align="right" style="font-weight:bold;" colspan="2">Date: '.$current_date.'</td>
</tr>
<tr>
<td align="right" style="font-weight:bold;" colspan="2">Due Date: '.date('Y-m-d H:i:s', $due_date).'</td>
</tr>
</tbody></table>
</td>
</tr>
<tr>
<td height="30px" colspan="2"> </td>
</tr>
<tr>
<td height="30px" colspan="2"> </td>
</tr>
<tr>
<td align="center">
<table width="100%" style="background-color:#dadada;padding:5px;border:5px solid #cccccc;">
<tbody>
<tr>
<td style="background-color:#777777;color:#fff;padding-left:10px" align="left">
Billing Period From:
</td>
</tr>
<tr>
<td align="left">
'.date('Y-m-d', $date_from).'
</td>
</tr>
</tbody></table>
</td>
<td align="center">
<table width="100%" style="background-color:#dadada;padding:5px;border:5px solid #cccccc;">
<tbody>
<tr>
<td style="background-color:#777777;color:#fff;padding-left:10px" align="left">
Billing Period To:
</td>
</tr>
<tr>
<td align="left">
'.$current_date.'
</td>
</tr>
</tbody></table>
</td>
</tr>
<tr>
<td height="30px" colspan="2"> </td>
</tr>
</tbody></table>';
$tbl_cdr .= '<table cellspacing="0" cellpadding="1" border="1" width="100%">
<tr style="background-color:grey; color:#ffffff;">
<td height="20" align="center">Date/Time</td>
<td align="center">Destination</td>
<td align="center">Bill Duration</td>
<td align="center">Total Charges</td>
</tr>';
if($query6->num_rows() > 0)
{
foreach ($query6->result() as $row6)
{
$tbl_cdr .= '<tr>
<td align="center" height="30">'.date("Y-m-d H:i:s", $row6->created_time/1000000).'</td>
<td align="center">'.$row6->destination_number.'</td>
<td align="center">'.$row6->billsec.'</td>';
if($row6->customer_id == $customer_id)
{
$tbl_cdr .= '<td align="center">'.$row6->total_sell_cost.'</td></tr>';
}
else
{
$tbl_cdr .= '<td align="center">'.$row6->total_reseller_sell_cost.'</td></tr>';
}
}
}
else
{
$tbl_cdr .= '<tr><td colspan="4" style="color:red;" align="center">No Calls Were Made During This Period</td></tr>';
}
$tbl_cdr .= '</table>';
$objcdr->writeHTML($tbl_cdr, true, false, false, false, '');
//Close and output PDF document
$objcdr->Output('media/invoices/'.$invoice_number.'_cdr.pdf', 'F');
//time to send email to the customer
if($customer_email != '')
{
if($customer_send_cdr == 0) //dont attach cdr
{
$this->load->library('email');
$this->email->clear(TRUE);
$this->email->from('*****@*****.**', 'Digital Linx');
$this->email->to($customer_email);
//$this->email->cc('*****@*****.**');
$this->email->subject('INVOICE -- (Dated: '.$current_date.')');
$this->email->message('Please see the attachment with this email to view your invoice:<br/><br/>
<b>Billing Period From: '.date('Y-m-d', $date_from).'</b><br/>
<b>Billing Period To: '.$current_date.'</b><br/>
<br/><br/>
Thanks,<br/>
Digital Linx
');
$this->email->attach('media/invoices/'.$invoice_number.'.pdf');
$this->email->send();
}
else
{
$this->load->library('email');
$this->email->clear(TRUE);
$this->email->from('*****@*****.**', 'Digital Linx');
$this->email->to($customer_email);
//$this->email->cc('*****@*****.**');
$this->email->subject('INVOICE -- (Dated: '.$current_date.')');
$this->email->message('Please see the attachment with this email to view your invoice:<br/><br/>
<b>Billing Period From: '.date('Y-m-d', $date_from).'</b><br/>
<b>Billing Period To: '.$current_date.'</b><br/>
<br/><br/>
Thanks & Regards,<br/>
DigitalLinx,
');
$this->email->attach('media/invoices/'.$invoice_number.'.pdf');
$this->email->attach('media/invoices/'.$invoice_number.'_cdr.pdf');
$this->email->send();
}
}
}
$this->session->set_flashdata('success','Invoice Generated Successfully.');
redirect ('reseller/billing/invoices/');
}
