/** * 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; }