read_replica = $this->load->database('savvy_replica', TRUE);
}
public function get_subscription_report_records(
$filters = [], $limit = null, $offset = null
)
{
$number_array = [
'reciept_count' => 's.reciept_count',
];
if(!empty($filters['id'])){
$number_array['id'] = 'me.id';
}
$date_array = [
'from_date' => 'DATE(s.subscribe) >=',
'to_date' => 'DATE(s.subscribe) <=',
];
$string_array = [
'title' => 'm.title',
'email' => 'me.email'
];
$this->read_replica->select([
'me.id',
'\'\'||me.firstname||\'\'',
'me.lastname',
'm.title',
's.subscribe',
's.status',
'm.id AS card_id',
'tp.name',
's.reciept_count',
'CASE WHEN s.reciept_count > 0 THEN \'Yes\' ELSE \'No\' END AS ReadySatus',
'\'\' AS proc1',
's.completed'
]);
$this->read_replica->from('members_card_assign s');
$this->read_replica->join('main_cards m', 'm.id = s.card_id', 'left');
$this->read_replica->join('members me', 'me.id = s.member_id', 'left');
$this->read_replica->join('transport_providers tp', 'CAST(tp.id AS text) = m.card_reciept', 'left');
$this->read_replica->where('s.subscribe IS NOT NULL');
$this->read_replica->where_in('m.button1_action', ['GOOFFERS']);
if(!empty($filters['card_receipts'])){
$this->read_replica->like('LOWER(tp.name)', strtolower($filters['card_receipts']));
}
foreach ($filters as $key => $val) {
if (array_key_exists($key, $number_array)) {
$this->read_replica->where($number_array[$key], $val);
} else if (array_key_exists($key, $date_array)) {
$this->read_replica->where($date_array[$key], $val);
} else if (array_key_exists($key, $string_array)) {
$this->read_replica->like('lower(' . $string_array[$key] . ')', strtolower($val));
}
}
// ready filter
if (!empty($filters['ready'])) {
$ready_filter_val = @intval($filters['ready']);
if ($ready_filter_val === 1) {
$this->read_replica->where('s.reciept_count > 0');
} elseif ($ready_filter_val === 0) {
$this->read_replica->where('(s.reciept_count = 0 OR s.reciept_count IS NULL)');
}
}
// completed filter
if (isset($filters['completed'])) {
if ($filters['completed'] == 1) { //complete
$this->read_replica->where('s.completed IS NOT NULL');
} elseif ($filters['completed'] == 0) { //pending
$this->read_replica->where('s.completed IS NULL');
}
}
// get only active subscription deals
$this->read_replica->where('m.status', 1);
if ($limit) {
$this->read_replica->limit($limit, $offset);
$this->read_replica->order_by('subscribe DESC');
}
$result = $this->read_replica->get()->result_array();
return $result;
}
}