public function find($experienceID, $cityID)
{
//echo " exp == ".$experienceID.", city = ".$cityID;
//query to read product type
$queryType = DB::table('products')->where('id', $experienceID)->select('name', 'type')->first();
//query to read the experience detail
$queryExperience = DB::table('products')->leftJoin('product_attributes_text as pat', 'pat.product_id', '=', 'products.id')->leftJoin('product_attributes_date as pad', 'pad.product_id', '=', 'products.id')->leftJoin('product_attributes_date as pad1', 'pad1.product_id', '=', 'products.id')->leftJoin('product_attributes_boolean as pab', 'pab.product_id', '=', 'products.id')->leftJoin('product_attributes_integer as pai', 'pai.product_id', '=', 'products.id')->leftJoin('product_attributes as pa', 'pa.id', '=', 'pat.product_attribute_id')->leftJoin('product_attributes as pa1', 'pa1.id', '=', 'pad.product_attribute_id')->leftJoin('product_attributes as pa2', 'pa2.id', '=', 'pad1.product_attribute_id')->leftJoin('product_attributes as pa4', 'pa4.id', '=', 'pai.product_attribute_id')->leftJoin('product_attributes as pa5', 'pa5.id', '=', 'pab.product_attribute_id')->leftJoin('product_pricing as pp', 'pp.product_id', '=', 'products.id')->leftJoin('price_types as pt', 'pt.id', '=', 'pp.price_type')->leftJoin('product_vendor_locations as pvl', 'pvl.product_id', '=', 'products.id')->leftJoin('vendor_locations as vl', 'vl.id', '=', 'pvl.vendor_location_id')->leftJoin('product_media_map as pmm', function ($join) {
$join->on('pmm.product_id', '=', 'products.id')->where('pmm.media_type', '=', 'main');
})->leftJoin('product_curator_map as pcm', 'pcm.product_id', '=', 'products.id')->leftJoin('curators', 'curators.id', '=', 'pcm.curator_id')->leftJoin('media', 'media.id', '=', 'pmm.media_id')->leftJoin('media_resized_new as cm', 'cm.id', '=', 'curators.media_id')->join('vendors', 'vendors.id', '=', 'vl.vendor_id')->where('products.id', $experienceID)->where('pvl.status', 'Active');
//->where('pa1.alias','experience_info')
//->where('pa2.alias','short_description')
//->where('pa4.alias','terms_and_conditions')
//->where('pa5.alias','experience_includes');
//adding additional parameters in case of simple experience
if ($queryType && $queryType->type == 'simple') {
$queryExperience->leftJoin(DB::raw('product_attributes_text as pat3'), 'pat3.product_id', '=', 'products.id')->leftJoin(DB::raw('product_attributes as pa3'), 'pa3.id', '=', 'pat3.product_attribute_id')->where('pa3.alias', 'menu')->select('products.id', 'products.name', 'products.type', 'pp.price', 'pp.tax', 'products.slug', 'pt.type_name as price_type', 'pp.is_variable', 'pp.post_tax_price', 'pp.taxes', DB::raw('MAX(IF(pa.alias = "experience_info", pat.attribute_value, "")) AS experience_info'), DB::raw('MAX(IF(pa.alias = "short_description", pat.attribute_value, "")) AS short_description'), DB::raw('MAX(IF(pa.alias = "terms_and_conditions", pat.attribute_value, "")) AS terms_and_conditions'), DB::raw('MAX(IF(pa.alias = "menu", pat.attribute_value, "")) AS menu'), DB::raw('MAX(IF(pa.alias = "menu_html", pat.attribute_value, "")) AS menu_html'), DB::raw('MAX(IF(pa.alias = "experience_includes", pat.attribute_value, "")) AS experience_includes'), DB::raw('MAX(IF(pa.alias = "seo_meta_desciption", pat.attribute_value, "")) AS seo_meta_desciption'), DB::raw('MAX(IF(pa.alias = "seo_title", pat.attribute_value, "")) AS seo_title'), DB::raw('MAX(IF(pa.alias = "seo_meta_keywords", pat.attribute_value, "")) AS seo_meta_keywords'), DB::raw('MAX(IF(pa1.alias = "start_date", pad.attribute_value, "")) AS start_date'), DB::raw('MAX(IF(pa2.alias = "end_date", pad1.attribute_value, "")) AS end_date'), DB::raw('MAX(IF(pa4.alias = "reward_points_per_reservation", pai.attribute_value, "")) AS reward_points'), DB::raw('MAX(IF(pa5.alias = "allow_gift_card_redemptions", pab.attribute_value, "")) AS gift_card'), DB::raw('MAX(IF(pa5.alias = "prepayment_allowed", pab.attribute_value, "")) AS prepaid'), 'media.file as experience_image', 'curators.name as curator_name', 'curators.bio as curator_bio', 'curators.designation', 'cm.file as curator_image', 'pvl.id as product_vendor_location_id', 'vendors.name as vendor_name', 'vendors.id as vendor_id', 'pvl.status as pvl_status');
} else {
$queryExperience->leftJoin(DB::raw('product_attributes_text as pat3'), 'pat3.product_id', '=', 'products.id')->leftJoin(DB::raw('product_attributes as pa3'), 'pa3.id', '=', 'pat3.product_attribute_id')->where('pa3.alias', 'menu')->select('products.id', 'products.name', 'products.type', 'products.slug', 'pp.price', 'pp.tax', 'pt.type_name as price_type', 'pp.is_variable', 'pp.post_tax_price', 'pat1.attribute_value as experience_info', 'curators.name as curator_name', 'curators.bio as curator_bio', 'curators.designation', 'pat2.attribute_value as short_description', 'media.file as experience_image', 'cm.file as curator_image', 'pat4.attribute_value as terms_and_condition', 'pvl.id as product_vendor_location_id', 'pat5.attribute_value as experience_includes', 'vendors.name as vendor_name', 'vendors.id as vendor_id');
}
//running the query to get the results
//echo $queryExperience->toSql();
$expResult = $queryExperience->first();
//echo "saa = ".$expResult->taxes." , ";
//echo (!empty($expResult->taxes)) ? $expResult->taxes : 'Taxes Applicable';
//echo "<pre>"; print_r($expResult); die;
//array to store the experience details
$arrExpDetails = array();
if ($expResult) {
//getting the reviews for the particular experience
$arrReviews = Self::readProductReviews($expResult->id);
$arrCuisines = Self::getExperienceCuisine($expResult->id);
$arrLocation = Self::getProductLocations($expResult->id, $expResult->product_vendor_location_id, $cityID);
$arrImage = Self::getProductImages($expResult->id);
//reading all the addons associated with the product
$arrAddOn = self::readExperienceAddOns($expResult->id);
$arrExpDetails['data'] = array('id' => $expResult->id, 'name' => $expResult->name, 'slug' => $expResult->slug, 'vendor_name' => $expResult->vendor_name, 'vendor_id' => $expResult->vendor_id, 'experience_info' => $expResult->experience_info, 'experience_includes' => $expResult->experience_includes, 'short_description' => $expResult->short_description, 'terms_and_condition' => $expResult->terms_and_conditions, 'seo_meta_desciption' => $expResult->seo_meta_desciption, 'seo_title' => $expResult->seo_title, 'seo_meta_keywords' => $expResult->seo_meta_keywords, 'start_date' => $expResult->start_date, 'end_date' => $expResult->end_date, 'image' => $arrImage, 'type' => $expResult->type, 'price' => $expResult->price, 'taxes' => !empty($expResult->taxes) ? $expResult->taxes : 'Taxes Applicable', 'pre_tax_price' => is_null($expResult->price) ? "" : $expResult->price, 'post_tax_price' => is_null($expResult->post_tax_price) ? "" : $expResult->post_tax_price, 'prepaid' => !empty($expResult->prepaid) ? $expResult->prepaid : "0", 'tax' => is_null($expResult->tax) ? "" : $expResult->tax, 'price_type' => is_null($expResult->price_type) ? "" : $expResult->price_type, 'curator_name' => is_null($expResult->curator_name) ? "" : $expResult->curator_name, 'curator_bio' => is_null($expResult->curator_bio) ? "" : $expResult->curator_bio, 'curator_image' => is_null($expResult->curator_image) ? "" : Config::get('constants.API_MOBILE_IMAGE_URL') . $expResult->curator_image, 'curator_designation' => is_null($expResult->designation) ? "" : $expResult->designation, 'menu' => $expResult->menu, 'menu_html' => $expResult->menu_html, 'rating' => is_null($arrReviews['avg_rating']) ? 0 : $arrReviews['avg_rating'], 'total_reviews' => $arrReviews['total_rating'], 'review_detail' => $arrReviews['reviews'], 'cuisine' => array_key_exists($expResult->id, $arrCuisines) ? $arrCuisines[$expResult->id] : array(), 'location' => $arrLocation, 'addons' => $arrAddOn, 'reward_points' => $expResult->reward_points, 'gift_card' => $expResult->gift_card, 'pvl_status' => $expResult->pvl_status, 'similar_option' => array());
}
return $arrExpDetails;
}
