FloatBackOfffice/application/models/Geofence_area_city_settings_model.php

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

class Geofence_area_city_settings_model extends CI_Model
{
    // city status
    const ACTIVE_STATUS = 1;
    const INACTIVE_STATUS = 0;

    private $read_replica;

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

    public function getAllWithPagination($filterData, $pagination = null)
    {
        $paginationQuery = '';
        $page = $pagination['page'] ?? 1;
        if ($pagination) {
          $perPage = $pagination['itemPerPage'] ?? 10;
          $offset = ($page - 1) * $perPage;
          if ($offset < 0) {
              $offset = 0;
          }
          $paginationQuery = 'LIMIT ' . pg_escape_string($perPage) . ' OFFSET ' . pg_escape_string($offset);
        }

        $q = 'SELECT settings.*, geofence_city.city, geofence_city.country, geofence_city.latitude, geofence_city.longitude FROM geofence_area_city_settings AS settings LEFT JOIN geofence_area_city AS geofence_city ON geofence_city.id = settings.geofence_area_city';
        $totalQueryString = 'SELECT COUNT(*) FROM geofence_area_city_settings as settings LEFT JOIN geofence_area_city as geofence_city ON settings.geofence_area_city = geofence_city.id';

        $lat = $filterData['lat'] ?? NULL;
        $lng = $filterData['lng'] ?? NULL;
        $radius = $filterData['radius'] ?? NULL;

        // Update query with search by radius
        if (!empty($lat) && !empty($lng) && !empty($radius)) {
          $earth_radius = 6371;
          $km_per_degree_lat = 111.2;
          $pi = 3.14/180;
          $latitude = $lat;
          $longitude = $lng;
          $distance_in_km = $radius;

          $q = "FROM (
            SELECT (
              $earth_radius * acos(
                  cos( radians( {$latitude}) )
                * cos( radians( latitude  ) )
                * cos( radians( longitude ) - radians( {$longitude} ) )
                + sin( radians( {$latitude}) )
                * sin( radians( latitude  ) ))
              ) AS distance,
              id,
              city,
              country,
              latitude,
              longitude
            FROM geofence_area_city
            WHERE latitude BETWEEN "
          . ($latitude - ($distance_in_km / $km_per_degree_lat))
          . " AND " . ($latitude + ($distance_in_km / $km_per_degree_lat));

          $delta = round($distance_in_km  / ($km_per_degree_lat  * COS(deg2rad($longitude))), 10);

          // Bounding box for speed - latitude within range (longtitude to km not linear)
          if (cos($longitude * $pi) > 0) {
            $from =  $longitude - $delta;
            $to = $longitude + $delta;

            $q .=
              " AND longitude" .
              " BETWEEN " . $from .
              " AND " . $to;

          } else {
            $from =  $longitude + $delta;
            $to = $longitude - $delta;

            $q .=
              " AND longitude" .
              " BETWEEN " . $from .
              " AND " . $to;
          }

          // distance limit for circle
          $q .= " ) geofence_city JOIN geofence_area_city_settings AS settings ON geofence_city.id = settings.geofence_area_city WHERE distance < " . $distance_in_km;

          $totalQueryString = 'SELECT COUNT(settings.id) ' .$q; // Count query
          $q = 'SELECT settings.*, geofence_city.city, geofence_city.country, geofence_city.latitude, geofence_city.longitude ' .$q;
        }
        // End update query with search by radius

        $whereQuery = [];
        if (!empty($filterData)) {
            if (!empty($filterData['country_code'])) {
                $whereQuery[] = 'geofence_city.country = ' . $this->read_replica->escape(strtoupper($filterData['country_code'])) . ' ';
            }
            if (!empty($filterData['search_text'])) {
                $search_text = $this->read_replica->escape_like_str($filterData['search_text']);
                $whereQuery[] = 'geofence_city.city ILIKE \'%' . $search_text . '%\'';
            }
            if (isset($filterData['status']) && $filterData['status'] != "all") {
                $status = $this->read_replica->escape($filterData['status']);
                $whereQuery[] = 'settings.status =' . $status;
            }
        }

        if (count($whereQuery) > 0) {
          $where = implode(" AND ", $whereQuery);
          if (!empty($lat) && !empty($lng) && !empty($radius)) {
            $q .= ' AND ' . $where;
            $totalQueryString .= ' AND ' . $where;
          } else {
            $q .= ' WHERE ' . $where;
            $totalQueryString .= ' WHERE ' . $where;
          }
        }

        $q .= ' ORDER BY settings.id ASC ' . $paginationQuery;
        $totalQuery = $this->read_replica->query($totalQueryString);
        $results = $this->read_replica->query($q);

        return [
            'total' => intval($totalQuery->result()[0]->count),
            'data_export_csv' => $this->db->query($q),
            'data' => $results->result(),
            'page' => $page,
        ];
    }

    public function getItem($id)
    {
        $queryString =
          'SELECT
              settings.*, geo.id as city, city as city_name
          FROM
              geofence_area_city_settings as settings LEFT JOIN geofence_area_city as geo ON geo.id = settings.geofence_area_city
          WHERE
              settings.id = ?
          ';

        $query = $this->read_replica->query($queryString, $id);
        return $query->row_array();
    }

    public function create($inputData)
    {
        $this->db->insert('geofence_area_city_settings', $inputData);
        $insertId = $this->db->insert_id();
    }

    public function update($id, $inputData)
    {
        $this->db->where('id', $id);
        $this->db->update('geofence_area_city_settings', $inputData);

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

    public function delete($id)
    {
        $this->db->delete('geofence_area_city_settings', ['id' => $id]);
    }

    public function getCitySettingByCityID($cityID)
    {
      $this->read_replica
        ->select('id, status, image_url, is_fectched_data')
        ->from('geofence_area_city_settings')
        ->where('geofence_area_city', $cityID);

        return $this->read_replica->get()->result_array();
    }
}