/**
* Returns the Reservation record of a user.
*
* @static true
* @access public
* @param integer $userID
* @param integer $start
* @param integer $limit
* @return array
* @since 1.0.0
*/
public static function getReservationRecord($userID, $start = NULL, $limit = NULL)
{
$queryResult = DB::table('reservation_details as rd')->leftJoin('vendor_locations as vl', 'vl.id', '=', 'rd.vendor_location_id')->leftJoin('product_vendor_locations as pvl', function ($join) {
$join->on('pvl.product_id', '=', 'rd.product_id')->on('pvl.vendor_location_id', '=', 'rd.vendor_location_id')->where('pvl.status', '=', 'Active');
})->leftJoin('products', 'products.id', '=', 'rd.product_id')->leftJoin('vendors', 'vendors.id', '=', 'vl.vendor_id')->leftJoin('product_attributes_text as pat', 'pat.product_id', '=', 'products.id')->leftJoin('product_attributes as pa', 'pa.id', '=', 'pat.product_attribute_id')->leftJoin('vendor_location_attributes_text as vlat', 'vlat.vendor_location_id', '=', 'vl.id')->leftJoin('vendor_attributes as va', 'va.id', '=', 'vlat.vendor_attribute_id')->leftJoin('locations as ploc', 'ploc.id', '=', 'vl.location_id')->leftJoin('vendor_location_address as pvla', 'pvla.vendor_location_id', '=', 'rd.vendor_location_id')->leftJoin('vendor_location_address as vvla', 'vvla.vendor_location_id', '=', 'rd.vendor_location_id')->leftJoin('locations as pvlaloc', 'pvlaloc.id', '=', 'pvla.city_id')->leftJoin('locations as vvlaloc', 'vvlaloc.id', '=', 'vvla.city_id')->leftJoin('locations as vloc', 'vloc.id', '=', 'vl.location_id')->leftJoin('product_media_map as pmm', 'pmm.product_id', '=', 'products.id')->leftJoin('media_resized_new as mrn3', function ($join) {
$join->on('mrn3.media_id', '=', 'pmm.media_id')->where('mrn3.image_type', '=', 'mobile_listing_ios_experience');
})->leftJoin('media_resized_new as mrn4', function ($join) {
$join->on('mrn4.media_id', '=', 'pmm.media_id')->where('mrn4.image_type', '=', 'mobile_listing_android_experience');
})->leftJoin('vendor_locations_media_map as vlmm', 'vlmm.vendor_location_id', '=', 'vl.id')->leftJoin('media_resized_new as mrn1', function ($join) {
$join->on('mrn1.media_id', '=', 'vlmm.media_id')->where('mrn1.image_type', '=', 'mobile_listing_ios_alacarte');
})->leftJoin('media_resized_new as mrn2', function ($join) {
$join->on('mrn2.media_id', '=', 'vlmm.media_id')->where('mrn2.image_type', '=', 'mobile_listing_android_alacarte');
})->where('rd.user_id', $userID)->select('rd.id', 'rd.user_id', 'rd.reservation_status', 'rd.reservation_date', 'rd.reservation_time', 'rd.no_of_persons', 'products.name as product_name', 'vendors.id as vendor_id', 'vendors.name as vendor_name', 'rd.reservation_type', 'products.id as product_id', 'rd.vendor_location_id', 'rd.product_vendor_location_id', 'rd.special_request', 'rd.giftcard_id', 'rd.guest_name', 'rd.guest_name', 'rd.guest_email', 'rd.guest_phone', 'rd.points_awarded', 'pvl.id as pvl_id', DB::raw('MAX(IF(pa.alias="short_description", pat.attribute_value,"")) AS product_short_description'), DB::raw('MAX(IF(va.alias="short_description", vlat.attribute_value, ""))AS vendor_short_description'), 'ploc.name as product_locality', 'pvla.address as product_address', 'vloc.name as vendor_locality', 'vvla.address as vendor_address', 'pvla.city_id as product_city_id', 'vvla.city_id as vendor_city_id', 'pvlaloc.name as product_city_name', 'vvlaloc.name as vendor_city_name', 'products.slug as experience_slug', 'vl.slug as alacarte_slug', 'vendors.name as restaurant_name', 'mrn1.file as ios_image_alacarte', 'mrn2.file as android_image_alacarte', 'mrn3.file as ios_image_experience', 'mrn4.file as android_image_experience')->orderBy('rd.reservation_date', 'asc')->orderBy('rd.reservation_time', 'asc')->groupBy('rd.id')->get();
//echo $queryResult->toSql(); die();
//array to store the information
$arrData = array();
//sub array to store the previous reservation information
$arrData['data']['pastReservation'] = array();
//sub array to store the upcoming reservation information
$arrData['data']['upcomingReservation'] = array();
if ($queryResult) {
//converting current day time to timestamp
$currentTimestamp = strtotime(date('Y-m-d H:i:s'));
//getting each reservation addons
foreach ($queryResult as $row) {
$arrReservation[] = $row->id;
}
//array to keep record of addons of reservation
$arrSelectedAddOn = array();
$arrSchedule = array();
$arrAddOn = array();
$arrSelectedAddOn = self::getReservationAddonsDetails($arrReservation);
//$arrAddOn = Experiences::readExperienceAddOns($row->product_id);
foreach ($queryResult as $row) {
//converting reservation day time to timestamp
$reservationTimestamp = strtotime($row->reservation_date . ' ' . date('H:i:s', strtotime($row->reservation_time)));
if ($reservationTimestamp >= $currentTimestamp) {
if ($row->reservation_type == 'experience' || $row->reservation_type == 'event') {
$day = date('D', strtotime($row->reservation_date));
$arrSchedule = Schedules::getExperienceLocationSchedule($row->product_id, NULL, $day, $row->vendor_location_id);
$arrAddOn = Experiences::readExperienceAddOns($row->product_id);
$slug = $row->experience_slug;
} else {
if ($row->reservation_type == 'alacarte') {
$day = date('D', strtotime($row->reservation_date));
$arrSchedule = Schedules::getVendorLocationSchedule($row->vendor_location_id, $day);
$slug = $row->alacarte_slug;
}
}
}
if (empty($row->vendor_name) && empty($row->product_name)) {
continue;
// $name = "";
// $product_id = "";
// $address = "";
// $locality = "";
} else {
$name = empty($row->product_name) ? $row->vendor_name : $row->product_name;
$product_id = $row->product_vendor_location_id == 0 ? $row->vendor_id : $row->product_id;
$address = empty($row->product_address) ? $row->vendor_address : $row->product_address;
$locality = empty($row->product_locality) ? $row->vendor_locality : $row->product_locality;
$city = empty($row->product_city_id) ? $row->vendor_city_id : $row->product_city_id;
$cityName = empty($row->product_city_name) ? $row->vendor_city_name : $row->product_city_name;
}
$arrDatum = array('id' => $row->id, 'short_description' => empty($row->product_short_description) ? $row->vendor_short_description : $row->product_short_description, 'status' => empty($row->reservation_status) ? "" : $row->reservation_status, 'date' => empty($row->reservation_date) ? "" : $row->reservation_date, 'time' => empty($row->reservation_time) ? "" : date('H:i:s', strtotime($row->reservation_time)), 'no_of_persons' => empty($row->no_of_persons) ? "" : $row->no_of_persons, 'name' => $name, 'type' => empty($row->reservation_type) ? "" : $row->reservation_type, 'product_id' => $product_id, 'vl_id' => empty($row->pvl_id) ? $row->vendor_location_id : $row->pvl_id, 'special_request' => is_null($row->special_request) ? "" : $row->special_request, 'giftcard_id' => is_null($row->giftcard_id) ? "" : $row->giftcard_id, 'guest_name' => empty($row->guest_name) ? "" : $row->guest_name, 'guest_email' => empty($row->guest_email) ? "" : $row->guest_email, 'guest_phone' => empty($row->guest_phone) ? "" : $row->guest_phone, 'reward_point' => empty($row->points_awarded) ? 0 : $row->points_awarded, 'selected_addon' => array_key_exists($row->id, $arrSelectedAddOn) ? $arrSelectedAddOn[$row->id] : array(), 'day_schedule' => $arrSchedule, 'address' => array('address' => empty($address) ? "" : $address, 'locality' => empty($locality) ? "" : $locality), 'addons' => empty($arrAddOn) ? [] : $arrAddOn, 'slug' => empty($slug) ? "" : $slug, 'city_id' => empty($city) ? "" : $city, 'city' => empty($cityName) ? "" : $cityName, 'restaurant_name' => empty($row->restaurant_name) ? "" : $row->restaurant_name, 'image' => array('mobile_listing_ios_alacarte' => empty($row->ios_image_alacarte) ? "" : Config::get('constants.API_MOBILE_IMAGE_URL') . $row->ios_image_alacarte, 'mobile_listing_android_alacarte' => empty($row->android_image_alacarte) ? "" : Config::get('constants.API_MOBILE_IMAGE_URL') . $row->android_image_alacarte, 'mobile_listing_android_experience' => empty($row->android_image_experience) ? "" : Config::get('constants.API_MOBILE_IMAGE_URL') . $row->android_image_experience, 'mobile_listing_ios_experience' => empty($row->ios_image_experience) ? "" : Config::get('constants.API_MOBILE_IMAGE_URL') . $row->ios_image_experience));
if ($reservationTimestamp >= $currentTimestamp && $row->reservation_status != 'cancel') {
array_push($arrData['data']['upcomingReservation'], $arrDatum);
} else {
array_push($arrData['data']['pastReservation'], $arrDatum);
}
}
$arrData['data']['pastReservationCount'] = count($arrData['data']['pastReservation']);
$arrData['data']['upcomingReservationCount'] = count($arrData['data']['upcomingReservation']);
$arrData['status'] = Config::get('constants.API_SUCCESS');
} else {
$arrData['status'] = Config::get('constants.API_SUCCESS');
$arrData['msg'] = 'No matching record found.';
$arrData['data']['pastReservationCount'] = 0;
$arrData['data']['upcomingReservationCount'] = 0;
}
return $arrData;
}
/**
* Returns the Reservation record of a user.
*
* @static true
* @access public
* @param integer $userID
* @param integer $start
* @param integer $limit
* @return array
* @since 1.0.0
*/
public static function getReservationRecord($userID, $start = NULL, $limit = NULL)
{
/*$queryResult = DB::table('reservation_details as rd')
->leftJoin('vendor_locations as vl','vl.id','=', 'rd.vendor_location_id')
//->leftJoin('product_vendor_locations as pvl','pvl.id','=','rd.product_vendor_location_id')
//->leftJoin('product_vendor_locations as pvl','pvl.product_id','=','rd.product_id and pvl.vendor_location_id = rd.vendor_location_id')
->leftJoin('product_vendor_locations as pvl', function($join){
$join->on('pvl.product_id', '=', 'rd.product_id');
$join->on(DB::raw('( and pvl.vendor_location_id = rd.vendor_location_id)'));
})
->leftJoin('products','products.id','=','pvl.product_id')
->leftJoin('vendors','vendors.id','=','vl.vendor_id')
->leftJoin('product_attributes_text as pat','pat.product_id','=','products.id')
->leftJoin('product_attributes as pa','pa.id','=','pat.product_attribute_id')
->leftJoin('vendor_location_attributes_text as vlat','vlat.vendor_location_id','=','vl.id')
->leftJoin('vendor_attributes as va','va.id','=','vlat.vendor_attribute_id')
->leftJoin('vendor_locations as vl2','vl2.id','=','pvl.vendor_location_id')
->leftJoin('locations as ploc','ploc.id','=','vl2.location_id')
->leftJoin('vendor_location_address as pvla','pvla.vendor_location_id','=','pvl.vendor_location_id')
->leftJoin('vendor_location_address as vvla','vvla.vendor_location_id','=','rd.vendor_location_id')
->leftJoin('locations as vloc', 'vloc.id','=', 'vl.location_id')
->where('rd.user_id', $userID)
->whereIn('reservation_status',array('new','edited'))
->select('rd.id','rd.user_id','rd.reservation_status','rd.reservation_date',
'rd.reservation_time','rd.no_of_persons', 'products.name as product_name','vendors.id as vendor_id',
'vendors.name as vendor_name', 'rd.reservation_type', 'products.id as product_id',
'rd.vendor_location_id', 'rd.product_vendor_location_id', 'rd.special_request',
'rd.giftcard_id', 'rd.guest_name', 'rd.guest_name', 'rd.guest_email',
'rd.guest_phone', 'rd.points_awarded',
DB::raw('MAX(IF(pa.alias="short_description", pat.attribute_value,"")) AS product_short_description'),
DB::raw('MAX(IF(va.alias="short_description", vlat.attribute_value, ""))AS vendor_short_description'),
'ploc.name as product_locality','pvla.address as product_address',
'vloc.name as vendor_locality', 'vvla.address as vendor_address',
'vvla.latitude as latitude', 'vvla.longitude as longitude', 'products.slug as product_slug',
'ploc.name as city',DB::raw('DAYNAME(rd.reservation_date) as dayname'),'pvl.id as product_vendor_location_id')
->orderBy('rd.reservation_date','asc')
->orderBy('rd.reservation_time','asc')
->groupBy('rd.id')
->get();*/
$queryResult = DB::select("select `rd`.`id`, `rd`.`user_id`, `rd`.`reservation_status`, `rd`.`reservation_date`, `rd`.`reservation_time`, `rd`.`no_of_persons`,\n `products`.`name` as `product_name`, `vendors`.`id` as `vendor_id`, `vendors`.`name` as `vendor_name`,\n `rd`.`reservation_type`, `products`.`id` as `product_id`, `rd`.`vendor_location_id`,\n `rd`.`product_vendor_location_id`,\n `rd`.`special_request`, `rd`.`giftcard_id`, `rd`.`guest_name`, \n `rd`.`guest_name`, `rd`.`guest_email`, `rd`.`guest_phone`, \n `rd`.`points_awarded`, MAX(IF(pa.alias='short_description', pat.attribute_value,'')) AS product_short_description,\n MAX(IF(va.alias='short_description', vlat.attribute_value, ''))AS vendor_short_description, `ploc`.`name` as `product_locality`,\n `pvla`.`address` as `product_address`, `vloc`.`name` as `vendor_locality`,\n `vvla`.`address` as `vendor_address`, `vvla`.`latitude` as `latitude`,\n `vvla`.`longitude` as `longitude`, `products`.`slug` as `product_slug`, `ploc`.`name` as `city`,\n DAYNAME(rd.reservation_date) as dayname,pvl.id as product_vendor_location_id,`vloc1`.name as city_name,`vloc1`.id as city_id \n from `reservation_details` as `rd` \n left join `vendor_locations` as `vl` on `vl`.`id` = `rd`.`vendor_location_id`\n left join `product_vendor_locations` as `pvl` on `pvl`.`product_id` = `rd`.`product_id` and pvl.vendor_location_id = `rd`.`vendor_location_id` \n left join `products` on `products`.`id` = `pvl`.`product_id` \n left join `vendors` on `vendors`.`id` = `vl`.`vendor_id` \n left join `product_attributes_text` as `pat` on `pat`.`product_id` = `products`.`id` \n left join `product_attributes` as `pa` on `pa`.`id` = `pat`.`product_attribute_id` \n left join `vendor_location_attributes_text` as `vlat` on `vlat`.`vendor_location_id` = `vl`.`id` \n left join `vendor_attributes` as `va` on `va`.`id` = `vlat`.`vendor_attribute_id` \n left join `vendor_locations` as `vl2` on `vl2`.`id` = `pvl`.`vendor_location_id` \n left join `locations` as `ploc` on `ploc`.`id` = `vl2`.`location_id` \n left join `vendor_location_address` as `pvla` on `pvla`.`vendor_location_id` = `pvl`.`vendor_location_id` \n left join `vendor_location_address` as `vvla` on `vvla`.`vendor_location_id` = `rd`.`vendor_location_id` \n left join `locations` as `vloc` on `vloc`.`id` = `vl`.`location_id`\n left join `locations` as `vloc1` on `vloc1`.`id` = vvla.city_id\n where `rd`.`user_id` = {$userID} and `reservation_status` in ('new', 'edited','prepaid')\n group by `rd`.`id` order by `rd`.`reservation_date` asc, `rd`.`reservation_time` asc");
//echo $queryResult->toSql();
//array to store the information
$arrData = array();
//sub array to store the previous reservation information
$arrData['data']['pastReservation'] = array();
//sub array to store the upcoming reservation information
$arrData['data']['upcomingReservation'] = array();
if ($queryResult) {
//converting current day time to timestamp
$currentTimestamp = strtotime(date('Y-m-d H:i:s'));
//getting each reservation addons
foreach ($queryResult as $row) {
$arrReservation[] = $row->id;
}
//array to keep record of addons of reservation
$arrSelectedAddOn = array();
$arrSchedule = array();
$arrAddOn = array();
$arrSelectedAddOn = self::getReservationAddonsDetails($arrReservation);
//$arrAddOn = Experiences::readExperienceAddOns($row->product_id);
foreach ($queryResult as $row) {
//converting reservation day time to timestamp
$reservationTimestamp = strtotime($row->reservation_date . ' ' . $row->reservation_time);
if ($reservationTimestamp >= $currentTimestamp) {
if ($row->reservation_type == 'experience') {
$day = date('D', strtotime($row->reservation_date));
$arrSchedule = Schedules::getExperienceLocationSchedule($row->product_id, NULL, $day);
$arrAddOn = Experiences::readExperienceAddOns($row->product_id);
} else {
if ($row->reservation_type == 'alacarte') {
$day = date('D', strtotime($row->reservation_date));
$arrSchedule = Schedules::getVendorLocationSchedule($row->vendor_location_id, $day);
}
}
}
$arrDatum = array('id' => $row->id, 'short_description' => empty($row->product_short_description) ? $row->vendor_short_description : $row->product_short_description, 'status' => $row->reservation_status, 'date' => $row->reservation_date, 'dayname' => $row->dayname, 'time' => $row->reservation_time, 'no_of_persons' => $row->no_of_persons, 'name' => empty($row->vendor_name) ? $row->product_name : $row->product_name, 'type' => $row->reservation_type, 'product_id' => $row->product_vendor_location_id == 0 ? $row->vendor_id : $row->product_id, 'vl_id' => $row->product_vendor_location_id, 'vendor_name' => $row->vendor_name, 'vendor_location_id' => $row->vendor_location_id, 'special_request' => is_null($row->special_request) ? "" : $row->special_request, 'giftcard_id' => is_null($row->giftcard_id) ? "" : $row->giftcard_id, 'guest_name' => $row->guest_name, 'guest_email' => $row->guest_email, 'guest_phone' => $row->guest_phone, 'reward_point' => $row->points_awarded, 'latitude' => $row->latitude, 'longitude' => $row->longitude, 'product_slug' => $row->product_slug, 'address' => empty($row->product_address) ? $row->vendor_address : $row->product_address, 'locality' => empty($row->product_locality) ? $row->vendor_locality : $row->product_locality, 'city' => $row->city_name, 'city_id' => $row->city_id, 'selected_addon' => array_key_exists($row->id, $arrSelectedAddOn) ? $arrSelectedAddOn[$row->id] : array(), 'day_schedule' => $arrSchedule, 'addons' => $arrAddOn);
if ($reservationTimestamp >= $currentTimestamp) {
array_push($arrData['data']['upcomingReservation'], $arrDatum);
} else {
array_push($arrData['data']['pastReservation'], $arrDatum);
}
}
$arrData['data']['pastReservationCount'] = count($arrData['data']['pastReservation']);
$arrData['data']['upcomingReservationCount'] = count($arrData['data']['upcomingReservation']);
$arrData['status'] = Config::get('constants.API_SUCCESS');
} else {
$arrData['status'] = Config::get('constants.API_SUCCESS');
$arrData['msg'] = 'No matching record found.';
$arrData['data']['pastReservationCount'] = 0;
$arrData['data']['upcomingReservationCount'] = 0;
}
return $arrData;
}