PHP DB::raw方法代码示例

 protected function afterSave($resultRecords, $occur_date)
 {
     //     	\DB::enableQueryLog();
     $tankDataValue = TankDataValue::getTableName();
     $tank = Tank::getTableName();
     $columns = [\DB::raw("sum(BEGIN_VOL) \tas\tBEGIN_VOL"), \DB::raw("sum(END_VOL) \t\t\tas\tEND_VOL"), \DB::raw("sum(BEGIN_LEVEL) \t\tas\tBEGIN_LEVEL"), \DB::raw("sum(END_LEVEL) \t\tas\tEND_LEVEL"), \DB::raw("sum(TANK_GRS_VOL) \t\tas\tGRS_VOL"), \DB::raw("sum(TANK_NET_VOL) \t\tas\tNET_VOL"), \DB::raw("sum(AVAIL_SHIPPING_VOL) as\tAVAIL_SHIPPING_VOL")];
     $attributes = ['OCCUR_DATE' => $occur_date];
     $storage_ids = [];
     foreach ($resultRecords as $mdlName => $records) {
         //     		$mdl = "App\Models\\".$mdlName;
         //     		$mdlRecords = $mdl::with('Tank')->whereIn();
         foreach ($records as $mdlRecord) {
             $storageID = $mdlRecord->getStorageId();
             if ($storageID) {
                 $storage_ids[] = $storageID;
             }
         }
     }
     $storage_ids = array_unique($storage_ids);
     foreach ($storage_ids as $storage_id) {
         $values = TankDataValue::join($tank, function ($query) use($tankDataValue, $tank, $storage_id) {
             $query->on("{$tank}.ID", '=', "{$tankDataValue}.TANK_ID")->where("{$tank}.STORAGE_ID", '=', $storage_id);
         })->whereDate('OCCUR_DATE', '=', $occur_date)->select($columns)->first();
         $attributes['STORAGE_ID'] = $storage_id;
         $values = $values->toArray();
         $values['STORAGE_ID'] = $storage_id;
         $values['OCCUR_DATE'] = $occur_date;
         StorageDataValue::updateOrCreate($attributes, $values);
     }
     //     	\Log::info(\DB::getQueryLog());
 }

 /**
  * Display a listing of the resource.
  *
  * @return \Illuminate\Http\Response
  */
 public function index(Request $request)
 {
     $date = date('Y-m-');
     $compliance = TicketItem::select(\DB::raw("  IFNULL( Sum(`tickets_items`.`weight` * `tickets_items`.`score` ) ,0)  AS score "))->join('ticket', 'ticket.id', '=', 'tickets_items.id')->groupBy('requester')->get();
     $tickets = Ticket::select('requester', \DB::raw("count(ticket.requester) AS requesters "))->groupBy('requester')->get();
     return \Response::json(["legends" => $tickets->lists('requester'), "set1" => $tickets->lists('requesters'), "set2" => $compliance->lists('score')], 200, [], JSON_NUMERIC_CHECK);
 }

 /**
  * Display the specified resource.
  *
  * @param  int  $id
  * @return \Illuminate\Http\Response
  */
 public function show($type, $id)
 {
     if ($id == 'video') {
         $events = Video::all();
         return view('video.show', compact('events'));
     } elseif ($id == 'staff') {
         $events = Staff::all();
         return view('staff.show', compact('events'));
     } elseif ($id == 'gallery') {
         $events = Image::all();
         return view('gallery.show', compact('events'));
     } else {
         $event = Event::where('slug', $id)->where('type', $type)->first();
         $location = Location::where('event_id', $event->id)->first();
         $slider = EventImage::where('event_id', $event->id)->orderBy(\DB::raw('RAND()'))->take(4)->get();
         $gallery = EventImage::where('event_id', $event->id)->first();
         if ($event->type == $type) {
             if ($event->status == 1) {
                 return view($type . '.show', compact('event', 'location', 'slider', 'gallery'));
             } else {
                 return redirect('/' . $type . '/');
             }
         }
     }
 }

 public function index2()
 {
     $data = ['publicidades' => \App\publicidad::leftJoin('tipo_galeria as tg', 'tg.id_tipo_galeria', '=', 'publicidad.seccion')->select('id_publicidad', 'cliente', \DB::raw('DATE(fecha_inicio) as fecha_inicio'), 'tg.nombre as seccion', 'posicion', 'url', 'link', 'publicidad.activo')->get(), 'categorias' => \App\tipo_galeria::select('id_tipo_galeria as id', 'nombre')->get()];
     // dd($data);
     return view('admin.publicidades', $data);
     // return view('admin.publicidades',$data);
 }

 public function index(Request $request)
 {
     /*
      * session()->keep here is used to retain the lesson id even if the user refreshes the page
      * this keeps the lesson id hidden to the user and inaccessible by anyone else
      * the lesson id is not retained when navigating away from the page so the page will be inaccessible once done
      */
     $lessonId = session('lessonId');
     // If lesson id does not exist in the session, do not allow access to the page
     if (!isset($lessonId)) {
         return redirect('lessons');
     }
     $user = auth()->user();
     $words = Word::orderBy(\DB::raw('RAND()'))->take(80)->get();
     $questions = LessonWord::with('word')->where('lesson_id', $lessonId)->get();
     session()->flash('maxQuestions', count($questions));
     if (empty(session('questionIndex'))) {
         session()->flash('questionIndex', 0);
         // Start with index zero
     } else {
         session()->keep('questionIndex');
     }
     $generatedOptions = $this->generateOptions($questions, $words);
     // Pass this to view
     if ($generatedOptions == null) {
         return redirect('lessons');
         // Return users to lesson page if they try to go back to the finished exam
     }
     return view('lessons.exam', ['user' => $user, 'questions' => $questions, 'options' => $generatedOptions]);
 }

 /**
  * [FunctionName description]
  * @param string $value [description]
  */
 public function activity()
 {
     $lastdate = \Carbon\Carbon::now()->subMonths(3);
     $checkins = $this->checkins->with('driver')->select(\DB::raw("*, COUNT(*) as activity , \n                            MONTHNAME(operasi_time) as mountname,\n                            MONTH(operasi_time) as mount"))->where('pool_id', $this->user->pool_id)->where('operasi_status_id', 1)->where('operasi_time', '>=', $lastdate->format('Y-m-d'))->where(\DB::raw('YEAR(operasi_time)'), date('Y'))->groupBy('driver_id')->groupBy(\DB::raw('MONTH(operasi_time)'))->get();
     //dd($drivers);
     return view('drivers.activity', compact('checkins'));
 }

 public function divideTeam($team, $min, $max, $boolit, $compID)
 {
     $len = count($team);
     $numTeam = $len / $min;
     $totalTeam = array();
     //$team = array_flip($team);
     //return $team;
     if ($boolit) {
         $a = array_keys($team);
     } else {
         $a = $team;
     }
     //print_r($a);
     while (count($a) >= $min) {
         $t = array();
         while (count($t) < $min) {
             array_push($t, array_pop($a));
             $a = array_reverse($a);
         }
         array_push($totalTeam, $t);
     }
     if (count($a) > 0 && count($totalTeam) > 0) {
         $i = 0;
         foreach ($a as $b) {
             array_push($totalTeam[$i % count($totalTeam)], $b);
             $i = $i + 1;
         }
     }
     $unsorted = array();
     $teamteam = array();
     //return $totalTeam;
     foreach ($totalTeam as $c) {
         if (count($c) > $max) {
             while (count($c) > $max) {
                 print_r($c);
                 array_push($unsorted, array_pop($c));
             }
             array_push($teamteam, $c);
         } else {
             array_push($teamteam, $c);
         }
     }
     if (count($teamteam) > 0) {
         foreach ($teamteam as $t) {
             team::create(['competition' => $compID, 'teamName' => 'Unnamed Team']);
             $val = \DB::select(\DB::raw('(SELECT max(teamID) as i FROM team)'));
             $teamID = $val[0]->i;
             foreach ($t as $s) {
                 //print_r($s);
                 //print_r(',');
                 team_contents::create(['teamID' => $teamID, 'studentID' => $s]);
                 \DB::table('student_competition')->where('studentID', '=', $s)->delete();
             }
         }
     }
     if (count($totalTeam == 0)) {
         return $a;
     }
     return $unsorted;
 }

 public function indexjson($month, $year)
 {
     $eventList1 = Events::where('e_type', 'single')->where(\DB::raw('MONTH(e_date)'), '=', $month)->where(\DB::raw('YEAR(e_date)'), '=', $year)->orWhere('e_type', 'annual')->where(\DB::raw('MONTH(e_date)'), '=', $month)->where(\DB::raw('YEAR(e_date)'), '<=', $year)->get();
     $eventList2 = Events::where('e_type', '!=', 'single')->where('e_type', '!=', 'annual')->get();
     $eventList = $eventList1->merge($eventList2);
     return response()->json($eventList);
 }

 /**
  * Show the application dashboard.
  *
  * @return Response
  */
 public function index()
 {
     $users = \DB::table('users')->join('orders', 'orders.manager', '=', 'users.id')->groupBy('orders.manager')->select('users.*', \DB::raw('count(manager) as total'))->get();
     $orders = new OrderRepository(new Order());
     $managers = $orders->findAllManager('success', 'monitor', ['*'], true);
     return view('home', compact('users', 'managers'));
 }

 /**
  * Display FRONT PAGE
  *
  * @return \Illuminate\Http\Response
  */
 public function index()
 {
     $programs = \App\Program::orderBy(\DB::raw('RAND()'))->take(4)->get();
     $programs_high_bonus = \App\Program::orderBy(\DB::raw('RAND()'))->take(4)->get();
     $program_logos = \App\Program::get(array('logo_bgcolor', 'slug'));
     return view(\App\Template::view('page.front-page'))->with(['menu_item' => 'home', 'edit_mode' => false, 'programs' => $programs, 'programs_high_bonus' => $programs_high_bonus, 'program_logos' => $program_logos]);
 }

 /**
  * Display a listing of the resource.
  *
  * @return Response
  */
 public function index()
 {
     $status = Input::get('status');
     if ($status == 'comment') {
         $comment_new = User_App_Comment::join('apps', 'apps.id', '=', 'user__app__comments.a_id')->join('users', 'users.id', '=', 'user__app__comments.u_id')->select('user__app__comments.id', 'users.name as user_name', 'users.id as user_id', 'apps.name as app_name', 'apps.id as app_id', 'apps.img_url as app_img', 'comment', 'user__app__comments.created_at')->orderBy('user__app__comments.created_at', 'desc')->take(5)->get();
         return $comment_new;
     }
     //multisearch
     $app_list = App::leftjoin('user__app__favorite', 'user__app__favorite.a_id', '=', 'apps.id')->select('apps.id', 'apps.name', 'apps.img_url', 'apps.rating_users', 'apps.genre', 'apps.rating', \DB::raw('count(user__app__favorite.id) as favorite_count'))->groupBy('apps.id')->orderBy('apps.rating_users', 'desc')->orderBy('id', 'asc');
     if (Input::has('name')) {
         $name = Input::get('name');
         $app_list->where('apps.name', 'LIKE', '%' . $name . '%');
     }
     if (Input::has('genre')) {
         $genre = Input::get('genre');
         $app_list->where('apps.genre', '=', $genre);
     }
     if (Input::has('skip')) {
         $skip = Input::get('skip');
         $app_list->skip($skip);
     }
     $apps = $app_list->take(10)->get();
     if (empty($apps->first())) {
         return Response::json(array('message' => 'Empty Query Man~', 'status' => 'error'));
     } else {
         foreach ($apps as $key => $value) {
             $app_comment_counts = App::join('user__app__comments', 'user__app__comments.a_id', '=', 'apps.id')->where('apps.id', '=', $value['id'])->count();
             //$value->suck_count = $app_suck_counts;
             $value->app_comment = $app_comment_counts;
         }
         return $apps;
     }
 }

 public function login_auth()
 {
     $user_id = Request::get('user_id');
     $user_password = Request::get('user_password');
     $result_list = \DB::select(\DB::raw("SELECT * FROM user_table"));
     foreach ($result_list as $result) {
         if ($result->user_id == $user_id) {
             if ($result->user_password == $user_password) {
                 if ($result->user_type == 'admin') {
                     $_SESSION['TICKET_USER_ID'] = $user_id;
                     $_SESSION['TICKET_USER_TYPE'] = $user_type;
                     return view('ticket_views.home');
                 }
                 if ($result->user_type == 'user') {
                     $client_id = $result->client_id;
                     if ($client_id == 'c_robi') {
                         $_SESSION['TICKET_USER_ID'] = $user_id;
                         $_SESSION['TICKET_USER_TYPE'] = $result->user_type;
                         $_SESSION['TICKET_USER_CLIENT_ID'] = $client_id;
                         $_SESSION['TICKET_USER_CLIENT_TYPE'] = 'telco';
                         return view('ticket_views.create_ticket_client');
                     }
                 }
             }
         }
     }
 }

 /**
  * Display the specified resource.
  *
  * @param  int  $id
  * @return Response
  */
 public function show(Request $request)
 {
     $fromDate = $request['fromDate'] . " 00:00:00";
     $toDate = $request['toDate'] . " 23:59:59";
     $precinct = $request['precinct'];
     if ($precinct == "0") {
         $precinct = "%";
     }
     $department = $request['department'];
     if ($department == "0") {
         $department = "%";
     }
     $category = $request['category'];
     if ($category == "0") {
         $category = "%";
     }
     $status = $request['status'];
     if ($status == "0") {
         $status = "%";
     }
     $reporter = $request['reporter'];
     if ($reporter == "0") {
         $reporter = "%";
     }
     $cases = \DB::table('cases')->join('departments', 'cases.department', '=', 'departments.id')->join('municipalities', 'cases.precinct', '=', 'municipalities.id')->join('users', 'cases.reporter', '=', 'users.id')->join('categories', 'cases.category', '=', 'categories.id')->select(\DB::raw("\n                                        cases.id,\n                                        cases.created_at,\n                                        cases.description,\n                                        cases.status,\n                                        cases.priority,\n                                        cases.severity,\n                                        departments.name as department,\n                                        municipalities.name as precinct,\n                                        IF(`cases`.`addressbook` = 1,(SELECT CONCAT(`FirstName`, ' ', `Surname`) FROM `addressbook` WHERE `addressbook`.`id`= `cases`.`reporter`), (SELECT CONCAT(users.`name`, ' ', users.`surname`) FROM `users` WHERE `users`.`id`= `cases`.`reporter`)) as reporterName,\n                                        categories.name as category\n                                    "))->whereBetween('cases.created_at', array($fromDate, $toDate))->where('municipalities.slug', 'LIKE', $precinct)->where('departments.slug', 'LIKE', $department)->where('categories.slug', 'LIKE', $category)->where('cases.status', 'LIKE', $status)->whereRaw("CONCAT(`users`.`name`, ' ', `users`.`surname`) LIKE '{$reporter}'")->groupBy('cases.id');
     return \Datatables::of($cases)->addColumn('actions', '<a class="btn btn-xs btn-alt" data-toggle="modal" onClick="launchCaseModal({{$id}});" data-target=".modalCase">View</a>')->make(true);
 }

 /**
  * Show the application dashboard.
  *
  * @return Response
  */
 public function index()
 {
     $days = Input::get('days', 7);
     $range = \Carbon\Carbon::now()->subDays($days);
     $stats = User::where('created_at', '>=', $range)->groupBy('date')->orderBy('date', 'DESC')->remember(1440)->get([DB::raw('Date(created_at) as date'), DB::raw('COUNT(*) as value')])->toJSON();
     // return view('home');
     $this->layout->content = View::make('home', compact('stats'));
 }

 public function customer_data()
 {
     \DB::statement(\DB::raw('set @rownum=0'));
     $customers = Customer::select([\DB::raw('@rownum  := @rownum  + 1 AS rownum'), 'id', 'name', 'address', 'phone', 'membership']);
     return Datatables::of($customers)->addColumn('action', function ($customer) {
         return '<a href="./customer/edit/' . $customer->id . '" class="btn btn-xs btn-primary"><i class="glyphicon glyphicon-edit"></i> Edit</a>';
     })->make(true);
 }