function get_summary_total_profit($date_frm, $date_to, $filter_contents)
{
$date_frm = $date_frm * 10000;
$date_to = $date_to * 10000;
if($filter_contents == 'all')
{
$where = "WHERE (hangup_cause = 'NORMAL_CLEARING' || hangup_cause = 'ALLOTTED_TIMEOUT') AND billsec > 0 AND parent_id = '0' AND (parent_reseller_id ='".$this->session->userdata('customer_id')."' || grand_parent_reseller_id = '".$this->session->userdata('customer_id')."') ";
}
else
{
$where = "WHERE (hangup_cause = 'NORMAL_CLEARING' || hangup_cause = 'ALLOTTED_TIMEOUT') AND billsec > 0 AND parent_id = '0' AND parent_reseller_id ='".$this->session->userdata('customer_id')."' ";
}
$profit = 0;
//if user is reseller 3
if(customer_any_cell($this->session->userdata('customer_id'), 'reseller_level') == '3'){
$sql = "SELECT * FROM cdr ".$where." AND created_time >= '".sprintf("%.0f", $date_frm)."' && created_time <= '".sprintf("%.0f", $date_to)."' ";
$query = $this->db->query($sql);
foreach($query->result() as $row)
{
if($row->parent_reseller_id == $this->session->userdata('customer_id'))
{
$diff = $row->total_sell_cost - $row->total_buy_cost;
$profit = $profit + $diff;
}
else if($row->parent_reseller_id != $this->session->userdata('customer_id') && $row->grand_parent_reseller_id == $this->session->userdata('customer_id'))
{
$diff = $row->total_reseller_sell_cost - $row->total_reseller_buy_cost;
$profit = $profit + $diff;
}
}
} else {
//user is reseller 2
$sql = "SELECT SUM((total_sell_cost - total_buy_cost)) as profit FROM cdr ".$where." AND created_time >= '".sprintf("%.0f", $date_frm)."' && created_time <= '".sprintf("%.0f", $date_to)."' ";
$query = $this->db->query($sql);
$row = $query->row();
$profit = $row->profit;
}
return $profit;
}
{
$inv_status = '<span class="button original_orange" style="width:52px">PENDING</span>';
}
else if($latest_status == 'over_due')
{
$inv_status = '<span class="button red">OVER DUE</span>';
}
?>
<td align="center" height="30"><?php echo $inv_status; ?></td>
<td align="center" height="30"><a href="<?php echo base_url(); ?>reseller/billing/download_invoice/<?php echo $row->invoice_id;?>"><img src="<?php echo base_url();?>assets/images/export-pdf.gif"/> View Invoice</a></td>
<td align="center" height="30"><a href="<?php echo base_url(); ?>reseller/billing/download_cdr/<?php echo $row->invoice_id;?>"><img src="<?php echo base_url();?>assets/images/export-pdf.gif"/> View CDR</a></td>
<?php if(customer_any_cell($row->customer_id, 'parent_id') == $this->session->userdata('customer_id')){?>
<?php if($latest_status == 'pending' || $latest_status == 'over_due') {?>
<td align="center" height="30"><a href="<?php echo base_url(); ?>reseller/billing/mark_as_paid/<?php echo $row->id; ?>">Mark as Paid</a></td>
<?php } else {?>
<td align="center" height="30">-</td>
<?php } ?>
<?php } else {?>
<td align="center" height="30">-</td>
<?php } ?>
</tr>
<tr style="height:5px;"><td colspan="13" id="shadowDiv" style="height:5px;margin-top:0px;background-color:#fff"></td></tr>
<?php endforeach;?>
<?php } else { echo '<tr><td align="center" style="color:red;" colspan="13">No Results Found</td></tr>'; } ?>
<tr>
<td colspan="3"><div class="error" id="err_div" style="display:none;"></div></td>
</tr>
<tr>
<td colspan="3"><div class="success" id="success_div" style="display:none;"></div></td>
</tr>
<tr>
<td align="center" height="20" colspan="3">
<form enctype="multipart/form-data" method="post" action="" name="addCust" id="addCust">
<table cellspacing="3" cellpadding="2" border="0" width="95%" class="search_col">
<tbody>
<?php if(customer_any_cell($this->session->userdata('customer_id'), 'reseller_level') == '3'){?>
<tr>
<td align="right"><span class="required">*</span> Type:</td>
<td align="left">
<select class="textfield" name="type" id="type">
<option value="0" selected>Normal Customer</option>
<option value="2">Reseller (Level- 2)</option>
</select>
</td></tr>
<?php } else {?>
<select class="textfield" name="type" id="type" style="display:none;">
<option value="0" selected>Normal Customer</option>
</select>
<?php } ?>
<tr>
<td align="right" width="45%"><span class="required">*</span> Firstname:</td>
function perform_batch($get_full_batch_query, $is_sell_rate, $sell_rate_value, $action_sell_rate, $is_sell_init, $sell_init_value, $action_sell_init)
{
foreach($get_full_batch_query->result() as $row)
{
$set = "";
$setForReseller = "";
$count = 0;
$countForReseller = "";
if($is_sell_rate == 1)
{
if($action_sell_rate == 'add' || $action_sell_rate == 'subtract' || $action_sell_rate == 'equal')
{
if(is_numeric($sell_rate_value))
{
if($action_sell_rate == 'add')
{
$set .= 'sell_rate = (sell_rate + '.$sell_rate_value.')';
$setForReseller .= 'cost_rate = (cost_rate + '.$sell_rate_value.')'; //admin sell rate is reseller buy rate
$count = 1;
$countForReseller = 1;
}
else if($action_sell_rate == 'subtract')
{
$set .= 'sell_rate = (sell_rate - '.$sell_rate_value.')';
$setForReseller .= 'cost_rate = (cost_rate - '.$sell_rate_value.')'; //admin sell rate is reseller buy rate
$count = 1;
$countForReseller = 1;
}
else if($action_sell_rate == 'equal')
{
$set .= 'sell_rate = '.$sell_rate_value.'';
$setForReseller .= 'cost_rate = '.$sell_rate_value.''; //admin sell rate is reseller buy rate
$count = 1;
$countForReseller = 1;
}
}
}
}
if($is_sell_init == 1)
{
if($action_sell_init == 'add' || $action_sell_init == 'subtract' || $action_sell_init == 'equal')
{
if(is_numeric($sell_init_value))
{
if($action_sell_init == 'add')
{
if($count == 1)
{
$set .= ',';
}
else
{
$count = 1;
}
$set .= 'sell_initblock = (sell_initblock + '.$sell_init_value.')';
if($countForReseller == 1)
{
$setForReseller .= ',';
}
else
{
$countForReseller = 1;
}
$setForReseller .= 'buy_initblock = (buy_initblock + '.$sell_init_value.')'; //admin sell init is reseller buy init
}
else if($action_sell_init == 'subtract')
{
if($count == 1)
{
$set .= ',';
}
else
{
$count = 1;
}
$set .= 'sell_initblock = (sell_initblock - '.$sell_init_value.')';
if($countForReseller == 1)
{
$setForReseller .= ',';
}
else
{
$countForReseller = 1;
}
$setForReseller .= 'buy_initblock = (buy_initblock - '.$sell_init_value.')'; //admin sell init is reseller buy init
}
else if($action_sell_init == 'equal')
{
if($count == 1)
{
$set .= ',';
}
else
{
$count = 1;
}
$set .= 'sell_initblock = '.$sell_init_value.'';
if($countForReseller == 1)
{
$setForReseller .= ',';
}
else
{
$countForReseller = 1;
}
$setForReseller .= 'buy_initblock = '.$sell_init_value.''; //admin sell init is reseller buy init
}
}
}
}
$sql = "UPDATE ".$row->tbl_name." SET ".$set." WHERE id = ".$row->id."";
$query = $this->db->query($sql);
/************************************************************************/
/************************************************************************/
//now the admin rates has been updated we have to update the resellers group rate tables
//get all reseller customers
if(customer_any_cell($this->session->userdata('customer_id'), 'reseller_level') == '3')
{
$sql2 = "SELECT a.*, b.customer_id, b.parent_id, b.reseller_level FROM groups as a LEFT JOIN customers as b on b.customer_id = a.created_by WHERE reseller_level = '2' && parent_id = '".$this->session->userdata('customer_id')."'";
$query2 = $this->db->query($sql2);
$numRow = $query2->num_rows();
if($numRow > 0)
{
foreach($query2->result() as $row2)
{
$sql4 = "UPDATE ".$row2->group_rate_table." SET ".$setForReseller." WHERE reseller_rate_group = '".$row->tbl_name."' && reseller_rate_id = ".$row->id."";
$query4 = $this->db->query($sql4);
}
}
}
}
}
function reseller_cdr_cust_select_all()
{
$CI = & get_instance();
$sql = "SELECT customer_id, customer_acc_num, customer_firstname, customer_lastname FROM customers WHERE parent_id = '".$CI->session->userdata('customer_id')."' ";
$query = $CI->db->query($sql);
$data = '';
$data .= '<ul id="quick_customer_filter_list" class="mcdropdown_menu">';
$data .= '<li rel=" ">Select</li>';
foreach($query->result() as $row)
{
if(customer_any_cell($row->customer_id, 'reseller_level') == 0)
{
$data .= '<li rel="'.$row->customer_id.'">'.$row->customer_firstname.' '.$row->customer_lastname.'</li>';
}
else
{
$sql2 = "SELECT customer_id, customer_acc_num, customer_firstname, customer_lastname FROM customers WHERE parent_id = '".$row->customer_id."'";
$query2 = $CI->db->query($sql2);
$data .= '<li rel="'.$row->customer_id.'">'.$row->customer_firstname.' '.$row->customer_lastname.'';
if($query2->num_rows() > 0)
{
$data .= '<ul>';
foreach($query2->result() as $row2)
{
$data .= '<li rel="'.$row2->customer_id.'">'.$row2->customer_firstname.' '.$row2->customer_lastname.'</li>';
}
$data .= '</ul>';
}
$data .= '</li>';
}
}
$data .= '</ul>';
echo $data;
}
function new_sip_access($customer_id)
{
if(customer_any_cell($customer_id, 'parent_id') != $this->session->userdata('customer_id'))
{
redirect ('reseller/customers/edit_customer/'.$customer_id.'');
}
$check = 0;
do {
$username = rand(1,999).rand(1,999);
$check_username_existis = $this->customer_model->check_sip_username_existis($username);
if($check_username_existis == 0)
{
$check = 1;
}
} while ($check == 0);
$data['customer_id'] = $customer_id;
$data['username'] = $username;
$data['password'] = rand(1,999).rand(1,999).rand(1,99);
$data['page_name'] = 'new_sip_access';
$data['selected'] = 'sip_access';
$data['sub_selected'] = '';
$data['page_title'] = 'NEW SIP CREDENTIALS';
$data['main_menu'] = '';
$data['sub_menu'] = '';
$data['main_content'] = 'reseller/customers/new_sip_view';
$data['dont_show_this'] = 1;
$this->load->view('default/template',$data);
}
function get_country_total_buy_cost($country_id, $filter_date_from, $filter_date_to, $filter_contents)
{
$where = '';
if($filter_contents == 'all')
{
$where .= "WHERE country_id = '".$country_id."' AND (hangup_cause = 'NORMAL_CLEARING' || hangup_cause = 'ALLOTTED_TIMEOUT') AND billsec > 0 AND parent_id = '0' AND (parent_reseller_id ='".$this->session->userdata('customer_id')."' || grand_parent_reseller_id = '".$this->session->userdata('customer_id')."') ";
}
else
{
$where .= "WHERE country_id = '".$country_id."' AND (hangup_cause = 'NORMAL_CLEARING' || hangup_cause = 'ALLOTTED_TIMEOUT') AND billsec > 0 AND parent_id = '0' AND parent_reseller_id ='".$this->session->userdata('customer_id')."' ";
}
if($filter_date_from != '')
{
$date_from = strtotime($filter_date_from) * 1000000; //convert into micro seconds
$where .= "AND created_time >= '".sprintf("%.0f", $date_from)."' ";
}
if($filter_date_to != '')
{
$date_to = strtotime($filter_date_to) * 1000000; //convert into micro seconds
$where .= "AND created_time <= '".sprintf("%.0f", $date_to)."' ";
}
$total_buy_cost = 0;
//if user is reseller 3
if(customer_any_cell($this->session->userdata('customer_id'), 'reseller_level') == '3'){
$sql = "SELECT * FROM cdr ".$where."";
$query = $this->db->query($sql);
foreach($query->result() as $row)
{
if($row->parent_reseller_id == $this->session->userdata('customer_id'))
{
$total_buy_cost = $total_buy_cost + $row->total_buy_cost;
}
else if($row->parent_reseller_id != $this->session->userdata('customer_id') && $row->grand_parent_reseller_id == $this->session->userdata('customer_id'))
{
$total_buy_cost = $total_buy_cost + $row->total_reseller_buy_cost;
}
}
} else {
//user is reseller 2
$sql = "SELECT SUM(total_buy_cost) AS total_buy_cost FROM cdr ".$where."";
$query = $this->db->query($sql);
$row = $query->row();
$total_buy_cost = $row->total_buy_cost;
}
return $total_buy_cost;
}
<?php if(settings_any_cell('logo', $this->session->userdata('customer_id')) != ''){ ?>
<img src="<?php echo base_url();?>media/images/<?php echo settings_any_cell('logo', $this->session->userdata('customer_id'));?>" height="33" />
<?php } else { ?>
<!--****** Check reseller level ********-->
<?php if(customer_any_cell($this->session->userdata('customer_id'), 'reseller_level') == '3'){?>
<!--****** check logo for admin ********-->
<?php if(settings_any_cell('logo', '0') != ''){ ?>
<img src="<?php echo base_url();?>media/images/<?php echo settings_any_cell('logo', '0');?>" height="33" />
<!--****** apply default ********-->
<?php } else { ?>
<img src="<?php echo base_url();?>assets/images/logo.png" alt="Digital Linx" height="33" width="167">
<?php } ?>
<?php } else { ?>
<!--****** check logo for reseller parent ********-->
<?php if(settings_any_cell('logo', customer_any_cell($this->session->userdata('customer_id'), 'parent_id')) != ''){ ?>
<img src="<?php echo base_url();?>media/images/<?php echo settings_any_cell('logo', customer_any_cell($this->session->userdata('customer_id'), 'parent_id'));?>" height="33" />
<?php } else { ?>
<!--****** check logo for admin ********-->
<?php if(settings_any_cell('logo', '0') != ''){ ?>
<img src="<?php echo base_url();?>media/images/<?php echo settings_any_cell('logo', '0');?>" height="33" />
<!--****** apply default ********-->
<?php } else { ?>
<img src="<?php echo base_url();?>assets/images/logo.png" alt="Digital Linx" height="33" width="167">
<?php } ?>
<?php } ?>
<?php } ?>
<?php } ?>
<?php } ?>
</div>
* "Digital Linx - <vbilling at digitallinx.com>"
*
* vBilling - VoIP Billing and Routing Platform
* version 0.1.3
*
*/
?>
<?php
$current_group_id = '';
if(isset($rate_group_id))
{
$current_group_id = $rate_group_id;
}
?>
<div id="subMenu">
<ul>
<li><a href="<?php echo base_url();?>reseller/groups/" <?php if($sub_selected == 'list_rate_groups') { echo 'class="sub_selected"' ;}?>>List Rate Groups</a></li>
<li><a href="<?php echo base_url();?>reseller/groups/assigned_rate_group" <?php if($sub_selected == 'list_assigned_groups') { echo 'class="sub_selected"' ;}?>>Assigned Rate Group</a></li>
<li><a href="<?php echo base_url();?>reseller/groups/list_rates/" <?php if($sub_selected == 'list_rates') { echo 'class="sub_selected"' ;}?>>List Rates</a></li>
<li><a href="<?php echo base_url();?>reseller/groups/new_rate_group" <?php if($sub_selected == 'new_rate_group') { echo 'class="sub_selected"' ;}?>>New Rate Group</a></li>
<li><a href="<?php echo base_url();?>reseller/groups/assigned_group_details/<?php echo customer_any_cell($this->session->userdata('customer_id'), 'customer_rate_group');?>" <?php if($sub_selected == 'new_rate') { echo 'class="sub_selected"' ;}?>>New Rate</a></li>
</ul>
<div class="clr"></div>
</div>
<?php
echo '<li><a href="#">'.customer_full_name($customer_id).'</a></li>';
?>
<li>TYPE ::</li>
<?php
$type = customer_any_cell($customer_id, 'reseller_level');
if($type == 0)
{
echo '<li><a href="#">Customer</a></li>';
}
else
{
echo '<li><a href="#">Reseller</a></li>';
}
?>
<li>PARENT ::</li>
<?php
$parent = customer_any_cell($customer_id, 'parent_id');
if($parent == $this->session->userdata('customer_id'))
{
echo '<li><a href="#">You !</a></li>';
}
else
{
echo '<li><a href="'.base_url().'reseller/customers/edit_customer/'.$parent.'">'.customer_full_name($parent).'</a></li>';
}
?>
</ul>
</td>
</tr>
<?php } ?>
function insert_new_rate()
{
$data['sellrate'] = $this->input->post('sell_rate');
$data['sellblock'] = $this->input->post('sell_init');
$data['group'] = $this->input->post('rate_group');
$data['parent_rate_id'] = $this->input->post('parent_rate_id');
$group_rate_table_name = $this->groups_model->group_any_cell($data['group'], 'group_rate_table');
if($group_rate_table_name != '')
{
$parent_rate_dtl = $this->groups_model->get_single_rate($data['parent_rate_id'], group_any_cell(customer_any_cell($this->session->userdata('customer_id'), 'customer_rate_group'), 'group_rate_table'));
$row = $parent_rate_dtl->row();
$data['digits'] = $row->digits;
$data['costrate'] = $row->sell_rate; //parent sell rate is reseller buy rate
$data['buyblock'] = $row->sell_initblock; //parent sell init is reseller buy init
$data['sellblock_min_duration'] = $row->sellblock_min_duration;
$data['buyblock_min_duration'] = $row->buyblock_min_duration;
$data['leadstrip'] = $row->lead_strip;
$data['trailstrip'] = $row->trail_strip;
$data['prefix'] = $row->prefix;
$data['suffix'] = $row->suffix;
$data['profile'] = $row->lcr_profile;
$data['startdate'] = $row->date_start;
$data['enddate'] = $row->date_end;
$data['quality'] = $row->quality;
$data['reliability'] = $row->reliability;
$data['lrn'] = $row->lrn;
$data['carrier'] = $row->carrier_id;
$data['country'] = $row->country_id;
$rate_duplicate = $this->groups_model->check_rate_duplicate($data['digits'], $data['sellrate'], $group_rate_table_name);
if($rate_duplicate->num_rows() == 0)
{
$this->groups_model->insert_new_rate($data, $group_rate_table_name);
}
else
{
echo "duplicate";
exit;
}
}
}
function insert_new_customer($data)
{
$current_date = date('Y-m-d');
$current_date = strtotime($current_date);
$m= date("m");
$d= date("d");
$y= date("Y");
if($data['billing_cycle'] == 'daily')
{
$dd = date('Y-m-d',mktime(0,0,0,$m,($d+1),$y));
$next_invoice_date = strtotime($dd);
}
else if($data['billing_cycle'] == 'weekly')
{
$dd = date('Y-m-d',mktime(0,0,0,$m,($d+7),$y));
$next_invoice_date = strtotime($dd);
}
else if($data['billing_cycle'] == 'bi_weekly')
{
$dd = date('Y-m-d',mktime(0,0,0,$m,($d+14),$y));
$next_invoice_date = strtotime($dd);
}
else if($data['billing_cycle'] == 'monthly')
{
$dd = date('Y-m-d',mktime(0,0,0,$m,($d+30),$y));
$next_invoice_date = strtotime($dd);
}
//if reseller 3 creates any reseller or customer than parent id will be his id
// if reseller 2 creates any reseller or customer than parent id will be his id but grand parent id will be the id of master reseller (if and only if reseller 2 was created by reseller 3 )
// but if reseller 2 was created by admin than grand parent id will be 0 becuase he dont have any grand parent
//ya ya i know it is complicated but u have to do it lol
$parent_id = 0;
$grand_parent_id = 0;
// CASE # 1: if reseller 3 is creating this
if(customer_any_cell($this->session->userdata('customer_id'), 'reseller_level') == '3')
{
$parent_id = $this->session->userdata('customer_id');
}
else if(customer_any_cell($this->session->userdata('customer_id'), 'reseller_level') == '2' && customer_any_cell($this->session->userdata('customer_id'), 'parent_id') == '0') // reseller 2 created by admin
{
$parent_id = $this->session->userdata('customer_id');
}
else if(customer_any_cell($this->session->userdata('customer_id'), 'reseller_level') == '2' && customer_any_cell($this->session->userdata('customer_id'), 'parent_id') != '0') // reseller 2 created by reseller 3
{
$parent_id = $this->session->userdata('customer_id');
$grand_parent_id = customer_any_cell($this->session->userdata('customer_id'), 'parent_id');
}
$sql = "INSERT INTO customers (customer_acc_num, customer_company, customer_firstname, customer_lastname, customer_contact_email, customer_address, customer_city, customer_state, customer_country, customer_phone_prefix, customer_phone, customer_zip, customer_prepaid, customer_credit_limit, customer_enabled, customer_max_calls, customer_send_cdr, customer_billing_email, customer_timezone, customer_rate_group, customer_billing_cycle, next_invoice_date, parent_id, grand_parent_id, reseller_level) VALUES ('".$data['account_no']."', '".$data['companyname']."', '".$data['firstname']."', '".$data['lastname']."', '".$data['email']."', '".$data['address']."', '".$data['city']."', '".$data['state']."', '".$data['country']."', '".$data['prefix']."', '".$data['phone']."', '".$data['zipcode']."', '".$data['account_type']."', '".$data['creditlimit']."', '1', '".$data['maxcalls']."', '".$data['cdr_check']."', '".$data['cdr_email']."', '".$data['timezone']."', '".$data['group']."', '".$data['billing_cycle']."', '".$next_invoice_date."', '".$parent_id."', '".$grand_parent_id."', '".$data['type']."') ";
$query = $this->db->query($sql);
return $this->db->insert_id();
}
<?php if($filter_did_type != 'sub_admin'){?>
<tr><td colspan="7" id="shadowDiv" style="height:5px;margin-top:-1px"></td></tr>
<?php } else { ?>
<tr><td colspan="6" id="shadowDiv" style="height:5px;margin-top:-1px"></td></tr>
<?php } ?>
<?php if($did->num_rows() > 0) {?>
<?php foreach ($did->result() as $row): ?>
<tr class="main_text">
<td align="center"> </td>
<?php if($row->type != 'sub_admin'){?>
<td align="left"><?php echo carrier_any_cell($row->carrier_id, 'carrier_name'); ?></td>
<td align="left"><?php echo customer_any_cell($row->customer_id, 'customer_firstname').' '.customer_any_cell($row->customer_id, 'customer_lastname'); ?></td>
<td align="left"><a href="<?php echo base_url();?>did/edit_did/<?php echo $row->did_id;?>"><?php echo $row->did_number; ?></a></td>
<td align="left"><?php echo $row->acl_ip; ?></td>
<td align="center"><input type="checkbox" id="<?php echo $row->did_id;?>" class="enable_checkbox" <?php if($row->enabled == 1){ echo 'checked="checked"';}?>/></td>
<td align="left"><a href="#" id="<?php echo $row->did_id;?>" class="delete_did"><img src="<?php echo base_url();?>assets/images/button_cancel.png" style="width:16px;margin-left:15px;border:none;cursor:pointer;" /></a></td>
<?php } else { ?>
<td width="3%" align="left"><?php echo $row->did_number; ?></td>
<td width="4%" align="center"><input type="checkbox" id="<?php echo $row->did_id;?>" class="enable_checkbox" <?php if($row->enabled == 1){ echo 'checked="checked"';}?>/></td>
<td width="6%" align="left"><a href="#" id="<?php echo $row->did_id;?>" class="delete_did"><img src="<?php echo base_url();?>assets/images/button_cancel.png" style="width:16px;margin-left:15px;border:none;cursor:pointer;" /></a></td>
<?php } ?>
</tr>
<?php if($filter_did_type != 'sub_admin'){?>
<tr style="height:5px;"><td colspan="7" id="shadowDiv" style="height:5px;margin-top:0px;background-color:#fff"></td></tr>
<?php } else { ?>
<tr style="height:5px;"><td colspan="5" id="shadowDiv" style="height:5px;margin-top:0px;background-color:#fff"></td></tr>
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/');
}
{
echo '<td align="left" style="font-weight:bold;">You !</td>';
}
else
{
if($this->session->userdata('user_type') == 'admin'){
echo '<td align="left" style="font-weight:bold;">'.anchor_popup('customers/edit_customer/'.$row->parent_id.'', customer_full_name($row->parent_id).' (L-'.customer_any_cell($row->parent_id, 'reseller_level').')', $atts).'</td>';
}
else if($this->session->userdata('user_type') == 'sub_admin'){
if(sub_admin_access_any_cell($this->session->userdata('user_id'), 'edit_customers') == 1)
{
echo '<td align="left" style="font-weight:bold;">'.anchor_popup('customers/edit_customer/'.$row->parent_id.'', customer_full_name($row->parent_id).' (L-'.customer_any_cell($row->parent_id, 'reseller_level').')', $atts).'</td>';
}
else
{
echo '<td align="left" style="font-weight:bold;">'.customer_full_name($row->parent_id).' (L-'.customer_any_cell($row->parent_id, 'reseller_level').')'.'</td>';
}
}
}
?>
<td align="left" valign="middle"><?php echo $row->customer_company; ?></td>
<td align="left" valign="middle"><?php echo $row->customer_contact_email; ?></td>
<td align="left" valign="middle"><?php echo country_any_cell($row->customer_country, 'countryname'); ?></td>
<td align="left" valign="middle"><?php echo $row->customer_phone; ?></td>
<td align="left" valign="middle"><?php echo $row->customer_balance; ?></td>
<?php if($this->session->userdata('user_type') == 'admin'){?>
<td align="center"><input type="checkbox" id="<?php echo $row->customer_id;?>" class="enable_checkbox" <?php if($row->customer_enabled == 1){ echo 'checked="checked"';}?>/></td>
<?php
} else if($this->session->userdata('user_type') == 'sub_admin'){
if(sub_admin_access_any_cell($this->session->userdata('user_id'), 'enable_disable_customers') == 1)
{
function generate_invoices_parent_r3_child_r2()
{
$current_date = date('Y-m-d');
$current_date_time = strtotime($current_date);
$sql = "SELECT customer_id, customer_billing_cycle, customer_prepaid, next_invoice_date, customer_firstname, customer_lastname, customer_address, customer_city, customer_country, customer_phone_prefix, customer_phone, customer_send_cdr, customer_billing_email, parent_id, grand_parent_id FROM customers WHERE next_invoice_date = '".$current_date_time."' && reseller_level = '2' && parent_id != '0'";
$query = $this->db->query($sql);
/*//for testing only
$sql = "SELECT customer_id, customer_billing_cycle, customer_prepaid, next_invoice_date, customer_firstname, customer_lastname, customer_address, customer_city, customer_country, customer_phone_prefix, customer_phone, customer_send_cdr, customer_billing_email, parent_id, grand_parent_id FROM customers WHERE reseller_level = '2' && parent_id != '0'";*/
if($query->num_rows() > 0) //if there are resellers whose next inv date is today
{
foreach($query->result() as $row)
{
$customer_id = $row->customer_id;
$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.', '.$row->customer_country;
$customer_contact = $row->customer_phone_prefix.$row->customer_phone;
$customer_send_cdr = $row->customer_send_cdr;
$customer_email = $row->customer_billing_email;
$customer_parent_id = $row->parent_id;
$customer_grand_parent_id = $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')
{
$dd = date('Y-m-d',mktime(0,0,0,$m,($d-1),$y));
$date_from = strtotime($dd);
}
else if($billing_cycle == 'weekly')
{
$dd = date('Y-m-d',mktime(0,0,0,$m,($d-7),$y));
$date_from = strtotime($dd);
}
else if($billing_cycle == 'bi_weekly')
{
$dd = date('Y-m-d',mktime(0,0,0,$m,($d-14),$y));
$date_from = strtotime($dd);
}
else if($billing_cycle == 'monthly')
{
$dd = date('Y-m-d',mktime(0,0,0,$m,($d-30),$y));
$date_from = strtotime($dd);
}
}
// 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
/*//for testing only
$cdr_to = time() * 1000000; //convert into micro seconds*/
//sum total invoice amount from cdr between 2 dates
//get the reseller 2 own total invoice amount and total calls he made (than we will see for his customers )
$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)."' AND parent_id = '0'";
$query4 = $this->db->query($sql4);
$row4 = $query4->row();
$total_invoice_amount = $row4->total_invoice_amount;
if($total_invoice_amount == '')
{
$total_invoice_amount = 0;
}
$total_calls_made = $row4->total_calls;
$status = "pending";
if($total_invoice_amount == 0)
{
$status = "paid";
}
else if($is_prepaid == '1')
{
$status = "paid";
}
//now sum total sell cost for his customers and total calls his childrens makes according to reseller 3 rates
$sqlResellerChild = "SELECT SUM(total_reseller_sell_cost) as total_invoice_amount, COUNT(*) as total_calls FROM cdr WHERE (parent_reseller_id = '".$customer_id."' || grand_parent_reseller_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)."' AND parent_id = '0'";
$queryResellerChild = $this->db->query($sqlResellerChild);
$rowResellerChild = $queryResellerChild->row();
$total_reseller_children_invoice_amount = $rowResellerChild->total_invoice_amount;
if($total_reseller_children_invoice_amount == '')
{
$total_reseller_children_invoice_amount = 0;
}
$total_reseller_children_calls_made = $rowResellerChild->total_calls;
$grand_reseller_inv_amt = $total_invoice_amount + $total_reseller_children_invoice_amount;
$grand_reseller_tot_calls = $total_calls_made + $total_reseller_children_calls_made;
//generate random invoice number
$invoice_number = 'INV-'.rand(1,999).rand(1,9).time();
//due date
$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, parent_id, grand_parent_id) VALUES ('".$invoice_number."', '".$customer_id."', '".$date_from."', '".$current_date_time."', '".$grand_reseller_inv_amt."', '".$grand_reseller_tot_calls."' ,'".$is_prepaid."', '".$current_date_time."', '".$due_date."', '".$status."', '".$customer_parent_id."', '".$customer_grand_parent_id."')";
$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')
{
$dd = date('Y-m-d',mktime(0,0,0,$m,($d+1),$y));
$next_invoice_date = strtotime($dd);
}
else if($billing_cycle == 'weekly')
{
$dd = date('Y-m-d',mktime(0,0,0,$m,($d+7),$y));
$next_invoice_date = strtotime($dd);
}
else if($billing_cycle == 'bi_weekly')
{
$dd = date('Y-m-d',mktime(0,0,0,$m,($d+14),$y));
$next_invoice_date = strtotime($dd);
}
else if($billing_cycle == 'monthly')
{
$dd = date('Y-m-d',mktime(0,0,0,$m,($d+30),$y));
$next_invoice_date = strtotime($dd);
}
$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
$obj = new $this->pdf;
// set document information
$obj->SetSubject('Invoice');
$obj->SetKeywords('Digital Linx, Invoice, CDR');
// add a page
$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>DigitalLinx.com</td></tr>
<tr><td>P.O. Box 305319</td></tr>
<tr><td>Riyadh, Saudi Arabia</td></tr>
<tr><td>Contact: +966-548805579</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>Contac: '.$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 colspan="2" style="background-color:#777777;color:#fff" align="center">Your Billing Details</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>Destination</td>
<td>Total Calls</td>
<td align="center">Total Charges</td>
</tr>';
$sql99 = "SELECT SUM(total_sell_cost) as total_invoice_amount, COUNT(*) as total_calls, destination_number 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)."' AND parent_id = '0' GROUP BY destination_number";
$query99 = $this->db->query($sql99);
$e_grand_total = 0;
if($query99->num_rows() > 0)
{
foreach($query99->result() as $row99)
{
$e_tot_inv_amt = $row99->total_invoice_amount;
$e_grand_total = $e_grand_total + $e_tot_inv_amt;
if($e_tot_inv_amt == '')
{
$e_tot_inv_amt = 0;
}
$e_total_calls_made = $row99->total_calls;
$e_destination = $row99->destination_number;
$tbl .= '<tr style="background-color:#ccc">
<td>'.$e_destination.'</td>
<td>'.$e_total_calls_made.'</td>
<td align="center">'.$e_tot_inv_amt.'</td>
</tr>';
}
}
else
{
$tbl .= '<tr style="background-color:#ccc">
<td>--</td>
<td>0</td>
<td align="center">0</td>
</tr>';
}
$tbl .= '<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">'.$e_grand_total.'</td>
</tr>';
$tbl .= '</tbody></table>
</td>
</tr>';
/***********************************************************/
//now we have to show his childrens data group by destination
/***********************************************************/
$tbl .= '<tr>
<td height="30px" colspan="2"> </td>
</tr>
<tr>
<td colspan="2" style="background-color:#777777;color:#fff" align="center">Your Customers Billing Details</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>Destination</td>
<td>Total Calls</td>
<td align="center">Total Charges</td>
</tr>';
$sql100 = "SELECT SUM(total_reseller_sell_cost) as total_invoice_amount, COUNT(*) as total_calls, destination_number FROM cdr WHERE (parent_reseller_id = '".$customer_id."' || grand_parent_reseller_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)."' AND parent_id = '0' GROUP BY destination_number";
$query100 = $this->db->query($sql100);
$child_e_grand_total = 0;
if($query100->num_rows() > 0)
{
foreach($query100->result() as $row100)
{
$child_e_tot_inv_amt = $row100->total_invoice_amount;
if($child_e_tot_inv_amt == '')
{
$child_e_tot_inv_amt = 0;
}
$child_e_grand_total = $child_e_grand_total + $child_e_tot_inv_amt;
$child_e_total_calls_made = $row100->total_calls;
$child_e_destination = $row100->destination_number;
$tbl .= '<tr style="background-color:#ccc">
<td>'.$child_e_destination.'</td>
<td>'.$child_e_total_calls_made.'</td>
<td align="center">'.$child_e_tot_inv_amt.'</td>
</tr>';
}
}
else
{
$tbl .= '<tr style="background-color:#ccc">
<td>--</td>
<td>0</td>
<td align="center">0</td>
</tr>';
}
$tbl .= '<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">'.$child_e_grand_total.'</td>
</tr>';
$tbl .= '<tr>
<td height="30px" colspan="2"> </td>
</tr>';
$tbl .= '<tr style="background-color:#777777;color:#fff">
<td colspan="3" align="center">Grand Total</td>
</tr>';
$tbl .= '<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;">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;color:#3172C6">Grand Total</td>
<td style="font-weight:bold;color:#3172C6;text-align:center">'.$grand_reseller_inv_amt.'</td>
</tr>
</tbody></table>
</td>
</tr>
<tr>
<td height="30px" colspan="2"> </td>
</tr>';
$terms = settings_any_cell('invoice_terms', customer_any_cell($customer_id, 'parent_id'));
if($terms != '')
{
$tbl .='<tr>
<td style="text-align: justify; font-style: italic; color: rgb(136, 136, 136); padding: 55px;" colspan="2">
'.$terms.'
</td>
</tr>';
}
$tbl .='</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('Digital Linx, Invoice, CDR');
// add a page
$objcdr->AddPage();
$objcdr->SetFont('helvetica', '', 6);
$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)."' AND parent_id = '0' ";
$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%">';
$tbl_cdr .= '<tr style="background-color:grey; color:#ffffff;">
<td height="20" align="center" colspan="4">Your CDR Details</td>
</tr>';
$tbl_cdr .= '<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>
<td align="center">'.$row6->total_sell_cost.'</td></tr>';
}
}
else
{
$tbl_cdr .= '<tr><td colspan="4" style="color:red;" align="center">No Calls Made During This Period</td></tr>';
}
$tbl_cdr .= '<tr style="background-color:grey; color:#ffffff;">
<td height="20" align="center" colspan="4">Your Customers CDR Details</td>
</tr>';
$sql7 = "SELECT * FROM cdr WHERE (parent_reseller_id = '".$customer_id."' || grand_parent_reseller_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)."' AND parent_id = '0' ";
$query7 = $this->db->query($sql7);
if($query7->num_rows() > 0)
{
foreach ($query7->result() as $row7)
{
$tbl_cdr .= '<tr>
<td align="center" height="30">'.date("Y-m-d H:i:s", $row7->created_time/1000000).'</td>
<td align="center">'.$row7->destination_number.'</td>
<td align="center">'.$row7->billsec.'</td>
<td align="center">'.$row7->total_reseller_sell_cost.'</td></tr>';
}
}
else
{
$tbl_cdr .= '<tr><td colspan="4" style="color:red;" align="center">No Calls 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 & Regards,<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/>
Digital Linx,
');
$this->email->attach('media/invoices/'.$invoice_number.'.pdf');
$this->email->attach('media/invoices/'.$invoice_number.'_cdr.pdf');
$this->email->send();
}
}
}
}
}
