public function getStats() { DB::transaction(function () { $guestlist_date = date('Y-m-d', strtotime(date('Y-m-d') . "-1 days")); $hotels = Guestlist::select('hotel_id')->where('guestlist_date', $guestlist_date)->distinct()->get(); foreach ($hotels as $row) { $total_check_in = Guestlist::select(DB::raw('count(*) as total_check_in'))->where('guestlist_date', $guestlist_date)->where('hotel_id', $row->hotel_id)->pluck('total_check_in'); $total_check_out = Guestlist::select(DB::raw('count(*) as total_check_out'))->where('checkout_date', 'like', $guestlist_date . '%')->where('hotel_id', $row->hotel_id)->pluck('total_check_out'); $foreign_guest = Guestlist::select(DB::raw('count(*) as foreign_guest'))->where('checkin_date', 'like', $guestlist_date . '%')->where('hotel_id', $row->hotel_id)->whereNotIn('nationality', array('IND', 'INDIA', 'INDIAN'))->pluck('foreign_guest'); //dd(DB::getQueryLog($total_check_out)); $data = array(); $data['hotel_id'] = $row->hotel_id; $data['guestlist_date'] = $guestlist_date; $data['total_check_in'] = $total_check_in; $data['total_check_out'] = $total_check_out; $data['foreign_guest'] = $foreign_guest; $data['id'] = DB::table('hotel_records')->insertGetId($data); } }); }