<?php

defined('BASEPATH') OR exit('No direct script access allowed');

class Customer_model extends CI_Model {

    function __construct() {
        parent::__construct();
    }

    /*
      return country
     */

    public function getCountry() {
        return $this->db->get('countries')->result();
    }

    /*
      return state
     */

    public function getQuotationCustomer($id) {
        $this->db->select('q.*,b.*,c.*')
                ->from('quotation q')
                ->join('biller b', 'q.biller_id=b.biller_id', 'left')
                ->join('customer c', 'q.customer_id=c.customer_id', 'left')
                ->where('q.customer_id', $id);
        return $this->db->get()->result();
    }

    public function getworkorderCustomer($id) {
        $this->db->select('d.*,u.*')
                ->from('workorder d')
                ->join('customer u', 'u.customer_id = d.workorder_costumer', 'left')
                ->where('d.workorder_costumer', $id);
        return $this->db->get()->result();
    }

    public function getState($id) {
        return $this->db->select('s.*')
                        ->from('states s')
                        ->join('countries c', 'c.id = s.country_id', 'left')
                        ->where('s.country_id', $id)
                        ->get()
                        ->result();
    }

    /*
      return city
     */

    public function getCity($id) {
        return $this->db->select('c.*')
                        ->from('cities c')
                        ->join('states s', 's.id = c.state_id', 'left')
                        ->where('c.state_id', $id)
                        ->get()
                        ->result();
    }

    public function generateNumber($id) {
        return $this->db->select('c.*')
                        ->from('cities c')
                        ->join('states s', 's.id = c.state_id', 'left')
                        ->where('c.state_id', $id)
                        ->get()
                        ->result();
    }

    public function getCities() {
        $sql = "select * from cities where state_id >= 4121";

        return $this->db->query($sql)
                        ->result();
    }

    public function getStates() {

        return $this->db->select('*')
                        ->from('states')
                        ->where('country_id', 102)
                        ->get()
                        ->result();
    }

    /*
      return all customer details to dispaly list
     */

    public function getCustomer() {
        $data = $this->db->select('b.*,c.name as cname,ct.name as ctname')
                ->from('customer b')
                ->join('countries c', 'c.id = b.country_id', 'left')
                ->join('cities ct', 'ct.id = b.city_id', 'left')
                ->get()
                ->result();
        return $data;
    }

    public function getCustomerSales() {
        $data = $this->db->select('b.customer_name,b.customer_id')
                ->from('customer b')
                ->group_by('b.customer_id')
                ->get()
                ->result();
        return $data;
    }

    public function get_sales_customer(){
        $data = $this->db->select('c.customer_id,c.customer_name,sum(s.total) as total')
                ->from('customer c')
                ->join('sales s','s.customer_id = c.customer_id','left')
                ->group_by('c.customer_id')
                ->get()
                ->result();
        return $data;
    }

    public function get_sales_customer_filter($awal,$akhir){
        $data = $this->db->select('c.customer_id,c.customer_name,sum(s.total) as total')
                ->from('customer c')
                ->join('sales s','s.customer_id = c.customer_id','left')
                ->where('s.date >=',$awal)
                ->where('s.date <=',$akhir)
                ->group_by('c.customer_id')
                ->get()
                ->result();
        return $data;
    }

    public function getCustomerAddress() {
        $data = $this->db->select('b.*,c.name as cname,ct.name as ctname, m.name as cmname')
                ->from('customer_address b')
                ->join('customer m', 'm.id = b.customer_id', 'left')
                ->join('countries c', 'c.id = b.country', 'left')
                ->join('cities ct', 'ct.id = b.city', 'left')
                ->get()
                ->result();
        return $data;
    }

    public function getCustomerAddressID($id) {
        $data = $this->db->select('b.*,c.name as cname,ct.name as ctname,m.customer_name as mname')
                ->from('customer_address b')
                ->join('customer m', 'm.customer_id = b.customer_id', 'left')
                ->join('countries c', 'c.id = b.country', 'left')
                ->join('cities ct', 'ct.id = b.city', 'left')
                ->where('b.customer_id', $id)
                ->get()
                ->result();
        return $data;
    }

    public function getCustomerAddressbyID($id) {
        $data = $this->db->select('b.*,c.name as cname,ct.name as ctname,m.customer_name as mname,s.name as sname,b.address as baddress')
                ->from('customer_address b')
                ->join('customer m', 'm.customer_id = b.customer_id', 'left')
                ->join('countries c', 'c.id = b.country', 'left')
                ->join('cities ct', 'ct.id = b.city', 'left')
                ->join('states s', 's.id = b.state', 'left')
                ->where('b.id', $id)
                ->get()
                ->result();
        return $data;
    }

    public function getCustomerUserID($id) {
        $data = $this->db->select('*')
                ->from('customer_user b')
                ->where('b.customer_id', $id)
                ->get()
                ->result();
        return $data;
    }

    public function checkField($field, $where) {
        $data = $this->db->select('count(*)')
                ->from('customer')
                ->like($field, $where)
                ->get()
                ->num_rows();
        return $data;
    }

    public function getField($field) {
        $data = $this->db->distinct()
                ->select($field)
                ->from('customer')
                ->get()
                ->result();
        return $data;
    }

    public function getCustorder($id) {
        $data = $this->db->select('c.*,so.*,b.*,i.*')
                ->from('customer c')
                ->join('sales so', 'so.customer_id = c.customer_id', 'left')
                ->join('biller b', 'so.biller_id=b.biller_id', 'left')
                ->join('invoice i ', 'so.sales_id=i.sales_id', 'left')
                ->where('so.customer_id', $id)
                ->get()
                ->result();
        return $data;
    }

    /*
      insert new customer record in databse
     */
    public function addModel2($data)
    {
        if($this->db->insert('customer',$data))
        {
            return $this->db->insert_id();
        }
        else{
            return FALSE;
        }
    }

    public function addModel($data) {
        $sql = "insert into customer (gst_registration_type, customer_name, company_name, gstid, country_id, state_id, state_code, city_id, postal_code, no_telp, mobile, fax, email, jalan, blok, nomor, rt, rw, kelurahan, kecamatan, cf1, cf2) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        if ($this->db->query($sql, $data)) {
            return $this->db->insert_id();
        } else {
            return FALSE;
        }
    }

    public function addAddressModel($data) {
        $sql = "insert into customer_address (customer_id,type,country,state,city,address,zip,tel) values(?,?,?,?,?,?,?,?)";
        if ($this->db->query($sql, $data)) {
            return $this->db->insert_id();
        } else {
            //print_r($this->db->error());
            return FALSE;
        }
    }

    public function addUserModel($data) {
        $sql = "insert into customer_user (customer_id,name,title,tel,ph,email,info) values(?,?,?,?,?,?,?)";
        if ($this->db->query($sql, $data)) {
            return $this->db->insert_id();
        } else {
            //print_r($this->db->error());
            return FALSE;
        }
    }

    /*
      return specific customer record
     */

    public function getRecord($id) {
        $query = $this->db->select('customer.*, cities.name AS ciname, countries.name AS coname')
                ->from("customer")
                ->join('cities', 'cities.id = customer.city_id', 'left')
                ->join('countries', 'countries.id = customer.country_id', 'left')
                ->where('customer.customer_id', $id)
                ->get()
                ->result();
        if ($query) {
            return $query;
        } else {
            return FALSE;
        }
    }

    public function getAddressRecord($id) {
        $sql = "select * from customer_address where customer_id = ? and id = ?";
        if ($query = $this->db->query($sql, $id)) {
            return $query->result();
        } else {
            return FALSE;
        }
    }

    public function getListAddressRecord($id) {
        $sql = "select * from customer_address where customer_id = ?";
        if ($query = $this->db->query($sql, array($id))) {
            return $query->result();
        } else {
            return FALSE;
        }
    }

    public function getUserRecord($id) {
        $sql = "select * from customer_user where customer_id = ? and id = ?";
        if ($query = $this->db->query($sql, $id)) {
            return $query->result();
        } else {
            return FALSE;
        }
    }

    /*
      save edited customer record in databse
     */

    public function editModel($data, $id) {
        /* $sql = "update customer set customer_name = ?,company_name = ?,address = ?,city_id = ?,country_id = ?,state_id = ?,mobile = ?,email = ?,postal_code = ?,gstid=?,vat_no=?,pan_no=?,tan_no=?,cst_reg_no=?,excise_reg_no=?,lbt_reg_no,servicetax_reg_no=?,gst_registration_type=? where customer_id = ?"; */
        //if($this->db->query($sql,$data)){
        $this->db->where('customer_id', $id);
        if ($this->db->update('customer', $data)) {
            return TRUE;
        } else {
            return FALSE;
        }
    }

    public function editAddressModel($data, $id, $ida) {
        $this->db->where('customer_id', $id);
        $this->db->where('id', $ida);
        if ($this->db->update('customer_address', $data)) {
            return TRUE;
        } else {
            return FALSE;
        }
    }

    public function editUserModel($data, $id, $ida) {
        $this->db->where('customer_id', $id);
        $this->db->where('id', $ida);
        if ($this->db->update('customer_user', $data)) {
            return TRUE;
        } else {
            return FALSE;
        }
    }

    /*
      delete customer record in databse
     */

    public function deleteModel($id) {
        $sql = "delete from customer where customer_id = ?";
        if ($this->db->query($sql, array($id))) {
            /* $this->db->where('customer_id',$id);
              if($this->db->delete('customer')){ */
            return TRUE;
        } else {
            return FALSE;
        }
    }

    public function deleteAddressModel($id) {
        $sql = "delete from customer_address where id = ?";
        if ($this->db->query($sql, array($id))) {
            return TRUE;
        } else {
            return FALSE;
        }
    }

    public function deleteUserModel($id) {
        $sql = "delete from customer_user where id = ?";
        if ($this->db->query($sql, array($id))) {
            return TRUE;
        } else {
            return FALSE;
        }
    }

}

?>