FloatBackOfffice/application/models/Emission_Average_model.php

<?php
defined('BASEPATH') or exit('No direct script access allowed');

class Emission_Average_model extends CI_Model
{

    const PERPAGE = 100;

    // table associate
    protected $table = 'emission_avrg_commute';

    // validate rules config
    public $rules = array(
        array(
            'field' => 'mkey',
            'label' => 'Mkey',
            'rules' => 'required'
        ),
        array(
            'field' => 'gramskm_perc',
            'label' => 'Gramskm_perc',
            'rules' => 'required'
        ),
        array(
            'field' => 'country',
            'label' => 'Country',
            'rules' => 'required|max_length[2]'
        )
    );

    // read only database
    private $read_replica;

    public function __construct()
    {
        parent::__construct();
        $this->read_replica = $this->load->database('savvy_replica', TRUE);
    }

    /**
     * get Emission Avrg Commute query string
     * @return string
     */
    public function getEmissionAvrgQuery()
    {
        $this->read_replica->select('
			e.id, 
			em.transport_mode,
			e.country, 
			e.state, 
			e.city, 
			e.gramskm_perc, 
			(c.avrg_commute*e.gramskm_perc*0.01) as A,
			(c.avrg_commute*e.gramskm_perc*0.01*em.grams_km/em.passengers) as B,
		');
        $this->read_replica->from(sprintf('%s e', $this->table));
        $this->read_replica->join('country c', 'e.country = c.code');
        $this->read_replica->join('emission_model em', 'em.mkey = e.mkey');
        // $this->read_replica->limit(self::PERPAGE, $paged);
        $query = $this->read_replica->get_compiled_select();
        return $query;
    }

    public function getTotalCount()
    {
        // retreive emission avrg query string
        $query = $this->getEmissionAvrgQuery();
        $results = $this->read_replica->query($query);
        if (!$results) {
            return;
        }
        return $results->num_rows();
    }

    /**
     * Get emission avrg results and pagination links
     * @param int $page current page
     * @return array
     */
    public function index($page = 1)
    {
        try {
            // pagination
            $results_count = $this->getTotalCount();
            $pagingUrl = base_url() . 'emission';
            $links = initPagination(self::PERPAGE, $results_count, $page, $pagingUrl);

            // get results per page
            $query = $this->getEmissionAvrgQuery();

            $results = sprintf('%s LIMIT %d OFFSET %d', $query, self::PERPAGE, self::PERPAGE * ($page - 1));
            $results = $this->read_replica->query($results);

            return array(
                'results' => $results,
                'charts' => $this->getChart($results->result_array()),
                'pagination' => $links
            );
        } catch (Exception $e) {
            return;
        }
    }

    public function getChart($emission_results)
    {
        $charts = [];
        foreach ($emission_results as $item) {
            $charts[$item['country']]['label'][] = $item['transport_mode'];
            $charts[$item['country']]['data'][] = $item['gramskm_perc'];
        }
        return $charts;
    }

    /**
     * Get emission avrg details
     * @param int $id emission id
     * @return array   emission details
     */
    public function edit($id)
    {
        $result = $this->read_replica->get_where($this->table, array('id' => $id));
        // get only first matching
        $result = $result->row_array();
        return $result;
    }

    /**
     * Store emission avrg
     * @param arrat $data form request
     * @return bool
     */
    public function store($data)
    {
        try {
            $this->db->insert($this->table, $data);
            return $this->db->insert_id();
        } catch (Exception $e) {
            return;
        }
    }

    /**
     * Update emission avrg by id
     * @param int $id emission id
     * @param array $data form request
     * @return bool
     */
    public function update($id, $data)
    {
        try {
            $this->db->where('id', $id);
            $this->db->update($this->table, $data);
            return $this->db->affected_rows();
        } catch (Exception $e) {
            return false;
        }

    }

    /**
     * Delete emission avrg record
     * @param int $id emission id
     * @return int affected rows index
     */
    public function delete($id)
    {
        $this->db->where('id', $id);
        $this->db->delete($this->table);
        return $this->db->affected_rows();
    }
}