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',
'\'\' AS Weight',
'\'\' 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',
'\'\' AS Personality',
'\'\' AS OfferCount',
'\'\' 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||\' \'||c.button1_action||\'
\'||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();
}
}