File: /var/www/html/nt/application/models/frontend/Query.php
<?php
class Query extends CI_Model {
function __construct(){
parent::__construct();
}
function getSportsFacilities($page_type, $location_id=null, $sports_infrastructure_id=null) {
$facilities = array();
$loc_condition = "WHERE sports_facilities.page_type='{$page_type}'";
if($location_id) {
$loc_condition = " AND sports_facilities.location_id = {$location_id}";
}
$sql = "SELECT sports_facilities.*, master_location.location_name FROM sports_facilities INNER JOIN master_location on master_location.location_id = sports_facilities.location_id"." ".$loc_condition;
$query = $this->db->query($sql);
//echo $this->db->last_query(); die();
if($query->num_rows() > 0) {
$facilities = $query->result_array();
foreach($facilities as $key=>$facilitie){
$sql = "SELECT * FROM sports_facilities_amenitis fa INNER JOIN master_facilities_amenitis ma ON ma.facilities_amenitis_id = fa.facilities_amenitis_id WHERE fa.sports_facilities_id={$facilitie['sports_facilities_id']}";
$query = $this->db->query($sql);
$facilities[$key]['amenitis'] = $query->result_array();
$sql1 = "SELECT * FROM sports_facilities_infrastructure fi INNER JOIN master_sports_infrastructure si ON si.sports_infrastructure_id = fi.sports_infrastructure_id WHERE fi.sports_facilities_id={$facilitie['sports_facilities_id']}";
$query1 = $this->db->query($sql1);
$facilities[$key]['infrastructure'] = $query1->result_array();
}
}
//echo "<pre>"; print_r($facilities); die();
return $facilities;
}
function getSportsFacility($sports_facilities_id) {
$facilitie = array();
$sql = "SELECT sports_facilities.*, master_location.location_name FROM sports_facilities INNER JOIN master_location on master_location.location_id = sports_facilities.location_id WHERE sports_facilities.sports_facilities_id={$sports_facilities_id}";
$query = $this->db->query($sql);
if($query->num_rows() > 0) {
$facilitie = $query->row_array();
$sql = "SELECT * FROM sports_facilities_amenitis fa INNER JOIN master_facilities_amenitis ma ON ma.facilities_amenitis_id = fa.facilities_amenitis_id WHERE fa.sports_facilities_id={$facilitie['sports_facilities_id']}";
$query = $this->db->query($sql);
$facilitie['amenitis'] = $query->result_array();
$sql1 = "SELECT * FROM sports_facilities_infrastructure fi INNER JOIN master_sports_infrastructure si ON si.sports_infrastructure_id = fi.sports_infrastructure_id WHERE fi.sports_facilities_id={$facilitie['sports_facilities_id']}";
$query1 = $this->db->query($sql1);
$facilitie['infrastructure'] = $query1->result_array();
}
return $facilitie;
}
function getSportsFacilityImages($sports_facilities_id) {
$sql = "SELECT * FROM sports_facilities_images WHERE sports_facilities_images.sports_facilities_id={$sports_facilities_id}";
$query = $this->db->query($sql);
return $query->result_array();
}
function getSportsFacilityRates($sports_facilities_id, $start_date = null, $end_date = null) {
if($start_date && $end_date) {
//echo 'end date - '.$end_date.'<br>';
//$end_date = '13-07-2022';
$start_date = str_replace('/', '-', $start_date);
$end_date = str_replace('/', '-', $end_date);
//str_replace('/', '-', '13/07/2022')
$start_date = date('Y-m-d',strtotime($start_date));
$end_date = date('Y-m-d',strtotime($end_date));
//echo $start_date.'<br>';
//echo $end_date; die();
$dates = $this->dateRange($start_date, $end_date);
}else{
//echo date('Y-m-d');
$dates = $this->dateRange(date('Y-m-d'), date("Y-m-d",strtotime(date('Y-m-d').' +10 days')));
}
if(count($dates) > 0) {
$result = [];
foreach ($dates as $key => $date) {
$sql1 = "SELECT * FROM sports_facilities_rates WHERE effective_start_date <= '{$date}' AND effective_end_date >= '{$date}' AND sports_facilities_rates.sports_facilities_id={$sports_facilities_id}";
$query2 = $this->db->query($sql1);
//echo $this->db->last_query().'<br>';
$result[$date]['rates'] = $query2->result_array();
}
//echo "<pre>"; print_r($result); die();
return $result;
}else{
return [];
}
}
function getMyBookings($user_id) {
$sql = "SELECT *, sports_facilities_booking.status as booking_status FROM sports_facilities_booking
INNER JOIN sports_facilities ON sports_facilities.sports_facilities_id = sports_facilities_booking.sports_facilities_id
WHERE user_id={$user_id}
order by booking_id DESC";
$query = $this->db->query($sql);
// echo $this->db->last_query();
//echo "<pre>"; print_r($query->result_array()); die();
$query = $this->db->query($sql);
//echo $this->db->last_query();
//echo "<pre>"; print_r($query->result_array()); die();
if($query->num_rows() > 0) {
$result = $query->result_array();
foreach ($result as $key => $q) {
$sql1 = "SELECT * FROM sports_facilities_booking_details
INNER JOIN organization_category ON organization_category.organization_category_id = sports_facilities_booking_details.organization_type
WHERE sports_facilities_booking_details.booking_id={$q['booking_id']}";
$query2 = $this->db->query($sql1);
$result[$key]['booking_details'] = $query2->result_array();
}
// echo "<pre>"; print_r($result); die();
return $result;
}else{
return [];
}
}
function dateRange($from, $to)
{
return array_map(function($arg) {
return date('Y-m-d', $arg);
}, range(strtotime($from), strtotime($to), 86400));
}
function getGymnasiumRates($sports_facilities_id, $user_type) {
$current_year = date('Y').'-'.date('Y', strtotime('+1 year'));
//echo $current_year;
$sql = "SELECT *, master_effective_year.effective_year as effective_year FROM gymnasium_rates
INNER JOIN master_effective_year ON master_effective_year.effective_year_id = gymnasium_rates.effective_year_id
WHERE gymnasium_rates.sports_facilities_id='{$sports_facilities_id}' AND gymnasium_rates.user_type = '{$user_type}' AND master_effective_year.effective_year = '{$current_year}'
order by gymnasium_rate_id DESC";
$query = $this->db->query($sql);
$result = $query->row_array();
return $result;
//echo $this->db->last_query(); die();
}
function getMemberDetailsByUser_id($user_id) {
$sql = "SELECT gymnasium_member.*, master_fieldunit.fieldunit_name, master_location.location_name, sports_facilities.sports_facilities_name FROM gymnasium_member
INNER JOIN master_fieldunit ON master_fieldunit.fieldunit_id = gymnasium_member.division_id
INNER JOIN master_location ON master_location.location_id = gymnasium_member.location_id
INNER JOIN sports_facilities ON sports_facilities.sports_facilities_id = gymnasium_member.facilities_id
WHERE gymnasium_member.user_id='{$user_id}'
order by gymnasium_member.gymnasium_member_id ASC";
$query = $this->db->query($sql);
$result = $query->result_array();
return $result;
}
function getMemberDetails($gymnasium_member_id) {
$sql = "SELECT gymnasium_member.*, master_fieldunit.fieldunit_name, master_location.location_name, sports_facilities.sports_facilities_name FROM gymnasium_member
INNER JOIN master_fieldunit ON master_fieldunit.fieldunit_id = gymnasium_member.division_id
INNER JOIN master_location ON master_location.location_id = gymnasium_member.location_id
INNER JOIN sports_facilities ON sports_facilities.sports_facilities_id = gymnasium_member.facilities_id
WHERE gymnasium_member.gymnasium_member_id='{$gymnasium_member_id}'
order by gymnasium_member.gymnasium_member_id ASC";
$query = $this->db->query($sql);
//echo $this->db->last_query(); die();
$result = $query->row_array();
return $result;
}
function getBookingDetailsByUser_id($user_id) {
$sql = "SELECT booking_header.*,property_master.property_name,property_master.image1 FROM booking_header
LEFT JOIN property_master ON property_master.property_id = booking_header.property_id
WHERE booking_header.customer_id='{$user_id}'
order by booking_header.booking_id DESC";
$query = $this->db->query($sql);
$result = $query->result_array();
return $result;
}
function getBookingDetailsByUserIdNew($condn) {
$this->db->select('booking_header.*,property_master.property_name,property_master.image1');
$this->db->from('booking_header');
$this->db->join('property_master', 'property_master.property_id = booking_header.property_id', 'LEFT');
$this->db->where($condn);
$result = $this->db->get()->result_array();
return $result;
}
function getBookingDetailsOfCustomer($booking_id) {
$sql = "SELECT customer_master.* FROM customer_master
LEFT JOIN booking_payment ON booking_payment.created_by = customer_master.customer_id
WHERE booking_payment.booking_id='{$booking_id}'";
$query = $this->db->query($sql);
$result = $query->row_array();
return $result;
}
function getBookingDetailsOfCustomerNew($condn) {
$this->db->select('customer_master.*,country_master.country_name,state_master.state_name');
$this->db->from('customer_master');
$this->db->join('country_master', 'country_master.country_id = customer_master.country_id', 'LEFT');
$this->db->join('state_master', 'state_master.state_id = customer_master.state_id', 'LEFT');
$this->db->where($condn);
$result = $this->db->get()->row_array();
return $result;
}
function getguestDetails($booking_id) {
$sql = "SELECT check_in_guest.* FROM check_in_header
LEFT JOIN check_in_detail ON check_in_detail.check_in_id = check_in_header.check_in_id
LEFT JOIN check_in_guest ON check_in_guest.check_in_detail_id = check_in_detail.check_in_detail_id
WHERE check_in_header.booking_id='{$booking_id}' order by check_in_guest.check_in_guest_id ASC limit 1";
$query = $this->db->query($sql);
$result = $query->row_array();
return $result;
}
function getPropertyDetails($booking_id) {
$sql = "SELECT property_master.*,district_master.district_name,state_master.state_name FROM booking_header
LEFT JOIN property_master ON property_master.property_id = booking_header.property_id
LEFT JOIN district_master ON district_master.district_id = property_master.district_id
LEFT JOIN state_master ON state_master.state_id = property_master.state_id
WHERE booking_header.booking_id='{$booking_id}'";
$query = $this->db->query($sql);
$result = $query->row_array();
return $result;
}
function getBookingHeader($booking_id) {
$sql = "SELECT * FROM booking_header WHERE booking_id='{$booking_id}'";
$query = $this->db->query($sql);
$result = $query->row_array();
return $result;
}
}