File: /var/www/html/pmw24/pmw_live_testing/app/application/models/customer/Minvoice.php
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class Minvoice extends CI_Model {
var $table = 'invoices';
var $column_order = array('invoices.id','invoices.invoice_no','invoices.date_of_creation','invoices.due_date','invoices.car_no','invoices.total_amount',NULL,'invoices.customer_name',NULL,'invoices.is_paid',NULl); //set column field database for datatable orderable
var $column_search = array('invoices.invoice_no','invoices.date_of_creation','invoices.car_no','invoices.total_amount','invoices.customer_name'); //set column field database for datatable searchable just firstname , lastname , address are searchable
//var $order = array('invoices.id' => 'desc'); // default order
var $order = array('invoices.id' => 'desc');
public function __construct() {
parent::__construct();
$this->customer=$this->session->userdata('customer_data');
}
private function _get_datatables_query($params=NULL){
$this->db->select('invoices.*');
$this->db->from($this->table);
$this->db->join('car','car.car_id=invoices.car_id','inner');
$this->db->join('customer','customer.customer_id=car.customer_id','left');
$this->db->where('customer.customer_id',$this->customer['customer_id']);
$this->db->where('invoices.delete_flag',0);
if(!empty($params))
{
if(!empty($params['customer_name']))
{
$this->db->where('invoices.customer_name',$params['customer_name']);
}
if(!empty($params['car_id']))
{
$this->db->where('invoices.car_id',$params['car_id']);
}
if(!empty($params['payment_mode']))
{
$this->db->where('invoices.payment_mode',$params['payment_mode']);
}
}
$i = 0;
foreach ($this->column_search as $item){ // loop column
if($_POST['search']['value']){ // if datatable send POST for search
if($i===0){ // first loop
$this->db->group_start(); // open bracket. query Where with OR clause better with bracket. because maybe can combine with other WHERE with AND.
$this->db->like($item, $_POST['search']['value']);
}
else{
$this->db->or_like($item, $_POST['search']['value']);
}
if(count($this->column_search) - 1 == $i) //last loop
$this->db->group_end(); //close bracket
}
$i++;
}
if(isset($_POST['order'])){ // here order processing
$this->db->order_by($this->column_order[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
}else if(isset($this->order)){
$order = $this->order;
$this->db->order_by(key($order), $order[key($order)]);
}
}
public function get_datatables($params=NULL){
$this->_get_datatables_query($params);
if($_POST['length'] != -1)
$this->db->limit($_POST['length'], $_POST['start']);
$query = $this->db->get();
//echo $this->db->last_query(); die;
return $query->result();
}
public function count_filtered($params=NULL){
$this->_get_datatables_query($params);
$query = $this->db->get();
return $query->num_rows();
}
public function count_all($params=NULL){
// $this->db->select('job.job_id,car.car_no,job.service,job.job_status');
// $this->db->from($this->table);
// $this->db->join('car','car.car_id=job.car_id','left');
$this->db->select('invoices.*');
$this->db->from($this->table);
$this->db->join('car','car.car_id=invoices.car_id','inner');
$this->db->join('customer','customer.customer_id=car.customer_id','left');
$this->db->where('customer.customer_id',$this->customer['customer_id']);
if(!empty($params))
{
if(!empty($params['customer_name']))
{
$this->db->where('invoices.customer_name', $params['customer_name']);
}
if(!empty($params['car_id']))
{
$this->db->where('invoices.car_id',$params['car_id']);
}
}
return $this->db->count_all_results();
}
public function get_details($id){
$this->db->select('invoices.*,car.car_no,
ifnull(CONCAT(customer.first_name," ",CASE WHEN customer.middle_name IS NULL THEN "" ELSE CONCAT(customer.middle_name," ") END,ifnull(customer.last_name,"")),"") as name,
ifnull(customer.email,"") as email,ifnull(customer.pin,"") as post_code,ifnull(customer.street_no,"") as street_no,ifnull(customer.suburb,"") as suburb,ifnull(customer.mobile,"") as contact_no,ifnull(customer.street_name,"") as street,admins.name as mechanic');
$this->db->from($this->table);
$this->db->join('car','car.car_id=invoices.car_id','left');
$this->db->join('customer','customer.customer_id=car.customer_id','left');
$this->db->join('admins','admins.admin_id=invoices.mechanic_id','left');
$this->db->where('invoices.id',$id);
$query=$this->db->get();
//echo $this->db->last_query(); die;
return $query->row_array();
}
public function update($condition,$data){
$this->db->where($condition);
$this->db->update($this->table,$data);
return 1;
}
public function add($data,$table=''){
if(empty($table)){
$this->db->insert($this->table,$data);
}else{
$this->db->insert($table,$data);
}
return $this->db->insert_id();
}
public function delete($condition){
$this->db->delete($this->table,$condition);
return 1;
}
public function active($condition,$data){
$this->db->where($condition);
$this->db->update($this->table,$data);
return 1;
}
public function get_client_list(){
$this->db->select('*');
$this->db->from('client');
$this->db->group_by('client_name');
$query=$this->db->get();
return $query->result_array();
}
public function getRow($table,$condition){
$this->db->where($condition);
$query=$this->db->get($table);
return $query->row_array();
}
public function getRows($table,$condition,$order_col=null,$order_type=null){
$this->db->where($condition);
if(isset($limit)){
$this->db->limit($limit);
}
if(!empty($order_col) && !empty($order_type)){
$this->db->order_by($order_col,$order_type);
}
$query=$this->db->get($table);
return $query->result_array();
}
public function insert($table,$data){
$this->db->insert($table,$data);
return $this->db->insert_id();
}
public function batch_insert($table,$data){
$this->db->insert_batch($table,$data);
return 1;
}
public function getServices($job_id){
$this->db->from("job_services");
$this->db->join('service','service.service_id=job_services.service_id','left');
$this->db->where('job_id',$job_id);
$query=$this->db->get();
return $query->result_array();
}
public function get_customer_list(){
$this->db->distinct();
$this->db->select('customer_name');
$this->db->where('customer_name!=',NULL);
$query = $this->db->get('invoices');
return $query->result_array();
}
public function get_car_list(){
$this->db->distinct();
$this->db->select('car_id');
// $this->db->from("invoices");
//$this->db->join('car','car.car_id=invoices.car_id','left');
$query_car = $this->db->get('invoices');
$car=$query_car->result_array();
for($i=0;$i<count($car);$i++)
{
$this->db->select('car_no');
$this->db->where('car_id',$car[$i]['car_id']);
$query = $this->db->get('car');
$car_data=$query->result_array();
$car[$i]['car_no']=$car_data[0]['car_no'];
}
return $car;
}
public function get_datatables_excel($params){
$this->db->from($this->table);
//$this->db->join('car','car.car_id=job.car_id','left');
if(!empty($params))
{
if(!empty($params['customer_name']))
{
$this->db->where('invoices.customer_name',$params['customer_name']);
}
if(!empty($params['car_id']))
{
$this->db->where('invoices.car_id',$params['car_id']);
}
}
$this->db->order_by('id','desc');
$query = $this->db->get();
return $query->result();
}
public function get_total_payment_by_invoice_no($invoice_no){
$sql="select ifnull(sum(payment_amount),0) as sum_amount from invoice_payment_details where invoice_no=".$invoice_no;
$query=$this->db->query($sql);
return $query->row_array();
}
public function get_payment_list()
{
$this->db->select('invoice_payment_details.*,invoices.car_no,invoices.customer_name,invoices.date_of_creation,invoices. total_amount');
$this->db->from('invoice_payment_details');
$this->db->join('invoices','invoice_payment_details.invoice_no=invoices.invoice_no','left');
$query=$this->db->get();
//echo $this->db->last_query(); die;
return $query->result_array();
}
public function get_latest_payment_details($invoice_no){
$sql="select ifnull(date_format(payment_date,'%d/%m/%Y'),0) as payment_date from invoice_payment_details where invoice_no='".$invoice_no."' ORDER BY invoice_payment_details_id DESC LIMIT 1";
$query=$this->db->query($sql);
return $query->row_array();
}
}