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(); } } } } }