FloatBackOfffice/application/models/Address_model.php

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

class Address_model extends CI_Model
{
    private $read_replica;

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

    public function Address($data)
    {
        return (array) $data;
    }

    public function getAddresses($params)
    {
        $res = SQEAPI::get('address/api/address/all', $params);

        if (isset($res['error'])) {
            return ['success' => false, 'error' => $res['error']];
        }

        $data = array();
        foreach ($res['data'] as $addr) {
            $data[] = $this->parseAddress($addr);
        }

        $total = isset($res['total']) ? $res['total'] : 0;
        $page = isset($res['page']) ? $res['page'] : 0;

        return [
            'list' => $data,
            'total' => $total,
            'page' => $page,
        ];
    }

    public function getAddressesWithUsingQuery($params)
    {
        $page = $params['page'] ?? 1;
        $per_page = $params['per_page'] ?? 20;

        $offset = ($page - 1) * $per_page;
        if ($offset < 0) {
            $offset = 0;
        }

        $whereQuery = ' WHERE 1 = 1 ';
        if (!empty($params)) {
            if (!empty($params['address'])) {
                $search_text = $this->read_replica->escape_like_str($params['address']);
                $whereQuery .= ' AND address ILIKE \'%' . $search_text . '%\'';
            }
        }

        $totalQueryString = 'SELECT * FROM address ' . $whereQuery;

        $queryString = 'SELECT * FROM address ' . $whereQuery . '
                ORDER BY address ASC
                LIMIT ' . pg_escape_string($per_page) . ' OFFSET ' . pg_escape_string($offset);

        $totalQuery = $this->read_replica->query($totalQueryString);
        $query = $this->read_replica->query($queryString);

        return [
            'total' => count($totalQuery->result()),
            'data' => $query->result(),
            'page' => $page,
        ];
    }

    public function createAddress($params)
    {
        return SQEAPI::post('address/api/address/new', $params);
    }

    public function removeAddressById($addrId)
    {
        return SQEAPI::delete("address/api/address/$addrId");
    }

    public function getAddressById($addrId)
    {
        $res = SQEAPI::get("address/api/address/$addrId", null);
        if (isset($res['error'])) {
            return $res;
        }

        return array_merge($res, ['data' => $this->parseAddress($res['data'])]);
    }

    public function updateAddressById($addrId, $params)
    {
        return SQEAPI::put("address/api/address/$addrId", $params);
    }

    private function parseAddress($originData)
    {
        return $this->Address($originData);
    }

    public function getCitieByID($city_id)
    {
      $q = $this
            ->read_replica
            ->select('id, city')
            ->where('id', $city_id)
            ->get('geofence_area_city');

      return $q->result_array();
    }

    public function getCountryByCode($country_cd)
    {
      $q = $this
            ->read_replica
            ->select('id, country')
            ->where('code', $country_cd)
            ->get('country');

      return $q->result_array();
    }

    public function generate_query_select_address($filters = [], $count_record = true) {
      $numeric_array = [
          'address_id' => 'ta.address_id',
      ];

      $string_array = [
        'attraction' => 'attraction'
      ];

      $count_record ?
      $this->db->select(['count(*) AS all_count']) :
      $this->db->select([
        'gac.id as gac_id',
        'gac.city as gac_city',
        'gac.country as gac_country',
        'gac.latitude as gac_latitude',
        'gac.longitude as gac_longitude',
        'gac.location as gac_location',
        'gac.radius as gac_radius',
        'gac.status as gac_status',
        'gacs.id as gacs_id',
        'gacs.status as gacs_status',
        'gacs.geofence_area_city as gacs_geofence_area_city',
        'gacs.image_url as gacs_image_url',
        'gacs.is_fectched_data as gacs_is_fectched_data',
        'ta.id as ta_id',
        'ta.attraction as ta_attraction',
        'ta.active as ta_active',
        'ta.address_id as ta_address_id',
        'ta.city_id as ta_city_id',
        'ta.feature_image as ta_feature_image',
        'ta.description as ta_description',
        'a.address as a_address'
      ]);

      $this->db->from('tourist_attraction ta')
        ->join('address a', 'a.id=ta.address_id', 'LEFT')
        ->join('geofence_area_city gac', 'gac.id=ta.city_id', 'LEFT')
        ->join('geofence_area_city_settings gacs', 'gacs.geofence_area_city=ta.city_id', 'LEFT');

      foreach($filters as $key => $val) {
          if (array_key_exists($key, $numeric_array)) {

              $this->db->where($numeric_array[$key], $val);

          } else if (array_key_exists($key, $string_array)) {

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

          }
      }

      foreach($filters['city_id'] as $key => $val) {
        $this->db->or_where('ta.city_id', $val);
      }

      if (isset($filters['latitude']) &&
          isset($filters['longitude']) &&
          isset($filters['radius'])) {

        $this->db->where(
          "ST_DWithin(
            geometry,
            ST_SetSRID(
              ST_MakePoint(
                {$filters['longitude']},
                {$filters['latitude']}
              ), 
              4326
            ),
            {$filters['radius']}
          )"
        );

      }

      if ($count_record === false) {

          $this->db->order_by('ta.attraction ASC');

      }

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