FloatBackOfffice/application/models/Decision_model.php

<?php if (!defined('BASEPATH')) {
    exit('No direct script access allowed');
}

class Decision_model extends CI_Model {

    private $read_replica;

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

    public function get_decision_logic_records(
        $filters = [],
        $limit = null,
        $offset = null
    ) {

        $combo_array = [
            'status' => 'status',
            'survey' => 'survey'
        ];

        $numberic_array = [
            'from_weight' => 'weight >=',
            'to_weight' => 'weight <='
        ];

        $string_array = [
            'logic' => 'logic',
            'description' => 'description'
        ];

        $this->read_replica->select([
            'id',
            'lorder',
            'logic',
            'description',
            'CASE WHEN status = 1 THEN \'Active\' ELSE \'Inactive\' END AS status',
            'CASE WHEN survey = 1 THEN \'Yes\' ELSE \'No\' END AS survey',
            '\'<input type="text" class="form-control" style="width:60px;"
            aria-label="Weight" value="\'||weight||\'">\' AS Weight',
            '\'<button type="button" class="btn btn-primary btn-sm">update</button>\' AS updt'
        ]);

        $this->read_replica->from('decision_logic');

        foreach($filters as $key => $val) {

            if (array_key_exists($key, $combo_array)) {

                $this->read_replica->where($combo_array[$key], $val);
            
            } else if (array_key_exists($key, $numberic_array)) {

                $this->read_replica->where($numberic_array[$key], $val);

            } else {

                $this->read_replica->like('lower(' . $string_array[$key] . ')', strtolower($val));

            }
        }

        if ($limit) {
            $this->read_replica->limit($limit, $offset);
            $this->read_replica->order_by('id');
        }
        
        return $this->read_replica->get()->result_array();
    }

    public function get_personalty_name_records(
        $filters = [],
        $limit = null,
        $offset = null
    ) {

        $combo_array = [
            'status' => 'status',
        ];

        $numberic_array = [
            'from_offer' => 'offers_count >=',
            'to_offer' => 'offers_count <='
        ];

        $string_array = [
            'dkey' => 'dkey',
            'description' => 'description',
            'personality' => 'personality'
        ];

        $this->read_replica->select([
            'id',
            'lorder',
            'dkey',
            'CASE WHEN status = 1 THEN \'Active\' ELSE \'Inactive\' END AS status',
            'description',

            '\'<input maxlength="49" type="text" id="txt\'||dkey||\'"
            class="form-control" style="width:260px;" aria-label="Personality"
            value="\'||personality||\'">\' AS Personality',

            '\'<input maxlength="5" type="text" id="ofc\'||dkey||\'"
            class="form-control" style=\"width:60px;" aria-label="Personality"
            value="\'||offers_count||\'">\' AS OfferCount',

            '\'<button onclick="updatePersonaltyName(\'||id||\',\'\'\'||dkey||\'\'\');"
            type="button" class="btn btn-info btn-sm">Update</button>\' AS updt' 
        ]);

        $this->read_replica->from('decision_group');

        foreach($filters as $key => $val) {

            if (array_key_exists($key, $combo_array)) {

                $this->read_replica->where($combo_array[$key], $val);
            
            } else if (array_key_exists($key, $numberic_array)) {

                $this->read_replica->where($numberic_array[$key], $val);

            } else {

                $this->read_replica->like('lower(' . $string_array[$key] . ')', strtolower($val));

            }
        }

        if ($limit) {
            $this->read_replica->limit($limit, $offset);
            $this->read_replica->order_by('lorder');
        }
        
        return $this->read_replica->get()->result_array();
    }

    public function get_member_report_records(
        $filters = [],
        $limit = null,
        $offset = null
    ) {

        $combo_array = [
        ];

        $numberic_array = [
            'from_count' => 'temp.count >=',
            'to_count' => 'temp.count <='
        ];

        $string_array = [
            'decision_group' => 'temp.decision_group',
            'description' => 'temp.description',
        ];

        $this->read_replica->select([
            'count(m.id) AS count',
            'm.decision_group AS decision_group',
            'd.description AS description'
        ]);

        $this->read_replica->from('members AS m');
        $this->read_replica->join('decision_group AS d', 'd.dkey = m.decision_group', 'LEFT');
    
        $this->read_replica->where('m.id > 0');

        $this->read_replica->group_by([
            'm.decision_group',
            'd.description'
        ]);

        $sub_query = $this->read_replica->get_compiled_select();

        $this->read_replica->select([
            'temp.count',
            'temp.decision_group',
            'temp.description'
        ]);
        $this->read_replica->from('('.$sub_query.') AS temp');

        foreach($filters as $key => $val) {

            if (array_key_exists($key, $combo_array)) {

                $this->read_replica->where($combo_array[$key], $val);
            
            } else if (array_key_exists($key, $numberic_array)) {

                $this->read_replica->where($numberic_array[$key], $val);

            } else {

                $this->read_replica->like('lower(' . $string_array[$key] . ')', strtolower($val));

            }
        }

        if ($limit) {
            $this->read_replica->limit($limit, $offset);
        }

        $select_query = str_replace('""', '"',  $this->read_replica->get_compiled_select());

        return $this->read_replica->query($select_query)->result_array();
    }

    public function get_refresh_report_records(
        $filters = [],
        $limit = null,
        $offset = null
    ) {

        $combo_array = [
        ];

        $numberic_array = [
        ];

        $string_array = [
        ];

        $sub_query = 
            'SELECT \'Members with No group\' AS description,
                    count(id) AS count
            FROM members
            WHERE id > 0 AND decision_group IS NULL
            UNION
            SELECT \'Members with decision not refreshed in 30 minutes\' AS description,
                    count(id) AS count
            FROM members
            WHERE id > 0
            AND decision_group IS NOT NULL
            AND now() > decision_updated + \'30 minute\'';

        $this->read_replica->select([
            'temp.count',
            'temp.description',
        ]);

        $this->read_replica->from('('.$sub_query.') AS temp');

        foreach($filters as $key => $val) {

            if (array_key_exists($key, $combo_array)) {

                $this->read_replica->where($combo_array[$key], $val);
            
            } else if (array_key_exists($key, $numberic_array)) {

                $this->read_replica->where($numberic_array[$key], $val);

            } else {

                $this->read_replica->like('lower(' . $string_array[$key] . ')', strtolower($val));

            }
        }

        if ($limit) {
            $this->read_replica->limit($limit, $offset);
        }

        $select_query = str_replace('""', '"',  $this->read_replica->get_compiled_select());

        return $this->read_replica->query($select_query)->result_array();
    }

    public function get_card_list_records($data) {
        $query_params =
        [
            'select' => [
                'c.name||\'  <b>\'||c.button1_action||\'</b><br>\'||c.id||\' - \'|| c.title AS Card',
                'c.card_country AS card_country'
            ],
            'order_by' => [
                'c.id DESC'
            ]
        ];

        return $this
                ->read_replica
                ->query($this->get_card_list_query($query_params, $data))
                ->result_array();
    }

    public function get_card_group_records($data) {
        $query_params =
        [
            'select' => [
                'c.button1_action',
                'count(c.id) AS count'
            ],
            'group_by' => [
                'c.button1_action'
            ]
        ];

        return $this
                ->read_replica
                ->query($this->get_card_list_query($query_params, $data))
                ->result_array();
    }

    public function get_card_country_records($data) {
        $query_params =
        [
            'select' => [
                'c.card_country AS card_country',
                'count(c.id) AS count'
            ],
            'group_by' => [
                'c.card_country'
            ]
        ];

        return $this
                ->read_replica
                ->query($this->get_card_list_query($query_params, $data))
                ->result_array();
    }

    public function get_card_list_query($query_params, $data) {
        $this->read_replica->select(['id AS id'])
          ->from('decision_group')
          ->where('dkey', $data['dkey']);
        $sub_query = $this->read_replica->get_compiled_select();

        $this->read_replica->select($query_params['select'])
          ->from('decision_cards d')
          ->join('main_cards c', 'c.id=d.card_id', 'LEFT')
          ->where('d.status', '1') // Active
          ->where("d.decision_id = ($sub_query)");

        if (isset($query_params['group_by'])) {
            $this->read_replica->group_by($query_params['group_by'][0]);
        }

        if (isset($query_params['order_by'])) {
            $this->read_replica->order_by($query_params['order_by'][0]);
        }

        return $this->read_replica->get_compiled_select();
    }

    public function get_personalty_card_records(
        $filters = [],
        $limit = null,
        $offset = null
    ) {
        $combo_array = [
            'status' => 'status'
        ];

        $numberic_array = [
        ];

        $string_array = [
            'dkey' => 'dkey',
            'description' => 'description',
            'personality' => 'personality'
        ];

        $this->read_replica->select([
            'id',
            'lorder',
            'dkey',
            'CASE WHEN status = 1 THEN \'Active\' ELSE \'Inactive\' END AS status',
            'description',
            'personality'
        ]);

        $this->read_replica->from('decision_group');

        foreach($filters as $key => $val) {

            if (array_key_exists($key, $combo_array)) {

                $this->read_replica->where($combo_array[$key], $val);
            
            } else if (array_key_exists($key, $numberic_array)) {

                $this->read_replica->where($numberic_array[$key], $val);

            } else {

                $this->read_replica->like('lower(' . $string_array[$key] . ')', strtolower($val));

            }
        }

        if ($limit) {
            $this->read_replica->limit($limit, $offset);
            $this->read_replica->order_by('lorder');
        }
        
        return $this->read_replica->get()->result_array();
    }
}