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