PHP Controllers\DB类代码示例

 public function record()
 {
     if (\Request::hasFile('image') && \Request::file('image')->isValid() && \Auth::user()->suspend == false) {
         $file = \Request::file('image');
         $input = \Request::all();
         $date = new \DateTime();
         if (isset($input['anon'])) {
             $name = 'anon';
         } else {
             $name = \Auth::user()->name;
         }
         $validator = \Validator::make(array('image' => $file, 'category' => $input['category'], 'title' => $input['title'], 'caption' => $input['caption']), array('image' => 'required|max:1200|mimes:jpeg,jpg,gif', 'category' => 'required', 'title' => 'required|max:120', 'caption' => 'required|max:360'));
         if ($validator->fails()) {
             return redirect('/publish')->withErrors($validator);
         } else {
             $unique = str_random(10);
             $fileName = $unique;
             $destinationPath = 'database/pictures/stream_' . $input['category'] . '/';
             \Request::file('image')->move($destinationPath, $fileName);
             \DB::insert('insert into public.moderation (p_cat, p_ouser, p_title, p_caption, p_imgurl, p_status, p_reported, p_rating, created_at, updated_at) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', [$input['category'], $name, $input['title'], $input['caption'], $unique, 'available', 0, 0, $date, $date]);
             $messages = 'Your content has been succesfully submitted. Going on moderation process.';
             return redirect('/system/notification')->with('messages', $messages);
         }
     } else {
         $messages = 'Your content data is invalid. The process is aborted.';
         return redirect('/system/notification')->with('messages', $messages);
     }
 }

 public function getSensor()
 {
     $id = $_SERVER['REMOTE_ADDR'];
     if (\DB::select('select 1 from module_vanguard where id = ?', [$id])) {
         if (\DB::select('select 1 from app_status where validity = true and sensor_register = true')) {
             $results = \DB::select('select * from module_vanguard where id = ?', [$id]);
             foreach ($results as $result) {
                 $alias = $result->alias;
                 $status = $result->status;
             }
             $timestamp = new \DateTime();
             if ($status == 'active') {
                 $must_report = true;
             }
             if ($status == 'passive') {
                 $must_report = false;
             }
             $is_reported = false;
             \DB::insert('insert into vanguard_log values (?, ?, ?, ?, ?)', [$id, $alias, $must_report, $is_reported, $timestamp]);
             //open to adjustment.
             exec("nodejs /var/www/topsus/nodejs/telegram_check.js");
             return 'OK';
         } else {
             return 'NOT OK - APPLICATION IS NOT LISTENING';
         }
     } else {
         return 'NOT OK - SENSOR IS INVALID';
     }
 }

 public function test()
 {
     //return view('test');
     $results = \DB::table('v_tickets')->where('user_id', 11)->get();
     return ['ok' => true, 'code' => 200, 'data' => $results, 'error' => ['message' => 'no errors', 'code' => null]];
     //App\VTicket::all();
 }

 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]);
 }

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

 /**
  * Update the specified resource in storage.
  *
  * @param  Request  $request
  * @param  int  $id
  * @return Response
  */
 public function update(Request $request, $id)
 {
     try {
         DB::table('dictionary')->where('id', $id)->update(array('word' => \Input::get('word')));
     } catch (Exception $e) {
     }
 }

 public function anyData(Request $req)
 {
     // $data = Jenissppd::all();
     // $count = App\Flight::where('active', 1)->count();
     $data = \DB::table('jenis_sppd');
     if ($req->get('filter_skdp_id')) {
         $result = $data->where('nama_jenis_sppd', '=', $req->get('filter_skdp_id'))->get();
         // $['rows']=$result;
         return $result;
     }
     if ($req->get('page')) {
         // dd($req->get('page')-1);
         if ($req->get('page') == 1) {
             $offset = $req->get('page') - 1;
         } else {
             $offset = ($req->get('page') - 1) * $req->get('rows');
         }
         $data->skip($offset);
     }
     if ($req->get('rows')) {
         $data->take($req->get('rows'));
     }
     $datax['rows'] = $data->get();
     $total['total'] = \DB::table('jenis_sppd')->count();
     // dd($data->get());
     return $total + $datax;
     //
 }

 public function inbox()
 {
     $name = \Auth::user()->name;
     $results = \DB::select('select * from notification.inbox_' . $name . ' order by created_at desc limit 70 offset 0');
     \DB::update('update notification.inbox_' . $name . ' set n_read = true');
     return view('profile.inbox')->with('results', $results);
 }

 public function getIndex()
 {
     //Obtiene todo valores
     $result = \DB::table('users')->get();
     //Realizar un select a ciertos campos
     $result = \DB::table('users')->select(['first_name', 'last_name'])->get();
     //Realizar un select con un where
     // where  (campo,operacion,valor)
     $result = \DB::table('users')->select(['first_name', 'last_name'])->where('first_name', '<>', 'Admin')->get();
     //Realizar un select con un orderBY ASC
     // order  (campo,tipo)
     $result = \DB::table('users')->select(['first_name', 'last_name'])->where('first_name', '<>', 'Admin')->orderBy('first_name', 'ASC')->get();
     //Realizar un select con un join
     //join (tablajoin,tabla1.campo_id,'=',table2.campo_id)
     // Al comentar el select se encuentran diferentes ids
     $result = \DB::table('users')->where('first_name', '<>', 'Admin')->orderBy('first_name', 'ASC')->join('user_profiles', 'users.id', '=', 'user_profiles.user_id')->get();
     //Delimitar la consulta con el join para no tener errores de ids
     $result = \DB::table('users')->select('users.id', 'first_name', 'last_name', 'user_profiles.id as profiles_id')->where('first_name', '<>', 'Admin')->orderBy('first_name', 'ASC')->join('user_profiles', 'users.id', '=', 'user_profiles.user_id')->get();
     //Obetener todos los campos de una table y de otra ciertos campos
     $result = \DB::table('users')->select('users.*', 'user_profiles.id as profiles_id_table', 'user_profiles.user_id as user_relation', 'user_profiles.twitter')->where('first_name', '<>', 'Admin')->orderBy('first_name', 'ASC')->join('user_profiles', 'users.id', '=', 'user_profiles.user_id')->get();
     //Incluir a los usuarios que no tiene perfil (administrador)
     $result = \DB::table('users')->select('users.*', 'user_profiles.id as profiles_id_table', 'user_profiles.user_id as user_relation', 'user_profiles.twitter')->orderBy('first_name', 'ASC')->leftJoin('user_profiles', 'users.id', '=', 'user_profiles.user_id')->get();
     //Muestra contraida la informacion
     dd($result);
     return $result;
 }

 public function post_upload()
 {
     // fetch input data
     $input = \Input::all();
     // create rules for validator
     $rules = array('image' => 'required|image|max:5000', 'description' => 'required|max:100');
     // validate the data with set rules
     $v = \Validator::make($input, $rules);
     if ($v->fails()) {
         // if validation fails, redirect back with relevant error messages
         return \Redirect::back()->withInput()->withErrors($v);
     } else {
         $image = \Input::file('image');
         // URL::to('/') can be changed to public_path() on a public domain
         $directory = \URL::to('/') . '/uploads/';
         $mkdir = public_path() . '/uploads/';
         // create directory if it doesn't exist
         if (!file_exists($mkdir)) {
             \File::makeDirectory($mkdir, 0775, true);
         }
         // save the filename with the path and a unique name for the file
         $filename = uniqid() . '.' . $input['image']->getClientOriginalExtension();
         // move the file to the correct path
         $upload_success = $input['image']->move($mkdir, $filename);
         $full_path = $directory . $filename;
         // store image info in the database
         \DB::table('images')->insert(['description' => \Input::get('description'), 'image' => $full_path, 'user_id' => \Auth::user()->id, 'approved' => 0, 'created_at' => \Carbon\Carbon::now()->toDateTimeString()]);
         return \Redirect::back()->withInput()->withErrors('Upload Successful!');
     }
 }

 /**
  * Edit timeline /
  */
 public function editTimeline($timeline_id, Request $request)
 {
     $timeline = \App\Timeline::where('id', '=', $timeline_id)->first();
     $timeline_events = $timeline->event()->orderBy('start_date')->get();
     if ($request->input('showForm') == 'true') {
         return view('timelines.editTimeline')->with('showForm', 'true')->with('timeline', $timeline)->with('timeline_events', $timeline_events);
     } else {
         // Else submit form
         // Validate the request data
         $this->validate($request, ['name' => 'required']);
         $user = \Auth::user();
         $timeline->name = $request->input('name');
         $timeline->description = $request->input('description');
         $timeline->last_modified_by = $user->id;
         $timeline->save();
         // Delete events
         for ($e = 0; $e < count($timeline_events); $e++) {
             if ($request->input('delete_event' . $e) == 'true') {
                 \DB::table('character_event')->where('event_id', '=', $timeline_events[$e]->id)->delete();
                 \DB::table('event_location')->where('event_id', '=', $timeline_events[$e]->id)->delete();
                 $event = \App\Event::where('id', '=', $timeline_events[$e]->id)->first();
                 if ($event) {
                     $event->delete();
                 }
             }
         }
         // Return success message
         return view('timelines.editTimeline')->with('showForm', 'false')->with('timeline', $timeline);
     }
 }

 public function postFinishedtask()
 {
     $reservedhour = \App\ReservedHours::find(\Request::input('hour_id'));
     \App\FinishedHour::create(array('user_id' => $reservedhour->user_id, 'service_id' => $reservedhour->service_id, 'client' => $reservedhour->client, 'price' => $reservedhour->service->price, 'created_at' => date('Y-m-d')));
     \DB::table('reservedhours')->where('id', '=', $reservedhour->id)->delete();
     return 'true';
 }

 /**
  * handle data posted by ajax request
  */
 public function create()
 {
     $id = \Auth::user()->id;
     $term = \Session::get('term');
     $subject_id = \Session::get('subject_id');
     $act_name = \Input::get('deletable');
     $query = \DB::table('records')->where('enrollment_faculty_load_id', $subject_id)->where('term', $term)->select('records.id as records_id')->get();
     $recid = json_decode(json_encode($query), true);
     foreach ($recid as $key => $value) {
         $query = \DB::table('grades')->where('records_id', $value)->select('id')->get();
         $gid[] = json_decode(json_encode($query), true);
     }
     foreach ($gid as $key => $data) {
         foreach ($data as $key2 => $data2) {
             $grades_id[] = $data2['id'];
         }
     }
     foreach ($grades_id as $key => $data) {
         \DB::table('grades')->where('id', $data)->where('name', $act_name)->delete();
     }
     \DB::table('selected_activities')->where('load_id', $subject_id)->where('term', $term)->where('act_name', $act_name)->delete();
     \DB::table('activities')->join('records', 'records.id', '=', 'activities.records_id')->where('records.enrollment_faculty_load_id', $subject_id)->where('records.term', $term)->where('act_name', 'LIKE', $act_name . '%')->delete();
     $response = 'The activity named ' . $act_name . ' has been deleted.';
     return \Response::json($response);
 }

 public function anyData(Request $req)
 {
     // $data = Unitkerja::all();
     // $count = App\Flight::where('active', 1)->count();
     // $data = \DB::table('unit_kerja');
     $data = Unitkerja::with('skpd');
     // dd($data->get());
     if ($req->get('filter_skdp_id')) {
         $result = $data->where('skpd_id', '=', $req->get('filter_skdp_id'))->get();
         // $['rows']=$result;
         // return $result;
         $datax['rows'] = $this->show_relasi_kolom($result);
         // return $this->show_relasi_kolom($result);
         return $datax + ['token' => csrf_token()];
     }
     if ($req->get('page')) {
         // dd($req->get('page')-1);
         if ($req->get('page') == 1) {
             $offset = $req->get('page') - 1;
         } else {
             $offset = ($req->get('page') - 1) * $req->get('rows');
         }
         $data->skip($offset);
     }
     if ($req->get('rows')) {
         $data->take($req->get('rows'));
     }
     // $datax['rows']=$data->get();
     // $datax['rows']=$this->show_relasi_kolom($data->get(),'skpd','nama_skpd','skpd');
     $datax['rows'] = $this->show_relasi_kolom($data->get());
     $total['total'] = \DB::table('unit_kerja')->count();
     // dd($data->get());
     return $total + $datax + ['token' => csrf_token()];
     //
 }

 /**
  * reset uses to check,
  * whether user is registered,
  *  if it is, then update new password.
  *
  * @return string
  */
 public function reset(Request $request)
 {
     $credentials = $request->only('email', 'password');
     $newpassword = $request->newpassword;
     $mail = $request->email;
     try {
         // verify the credentials and create a token for the user
         if (!($token = JWTAuth::attempt($credentials))) {
             return response()->json(['error' => 'invalid_credentials', 'status' => 201], 201);
         }
     } catch (JWTException $e) {
         // something went wrong
         return response()->json(['error' => 'could_not_create_token', 'status' => 500], 500);
     }
     if ($this->CheckInternet()) {
         $adminName = \DB::select('SELECT firstname FROM users WHERE email = "' . $mail . '"');
         $sendMail = new EmailController();
         $content = 'Dear Administrator, your updated password is ' . $newpassword;
         $subject = 'COUPLEY Password Update';
         $sendMail->SendMail($mail, $adminName[0]->firstname, $subject, $content);
         $hashed = \Hash::make($newpassword);
         \DB::table('users')->where('email', $mail)->update(['password' => $hashed]);
         return response()->json(['password' => 'uptodate', 'status' => 200], 200);
     } else {
         return response()->json(['error' => 'No_network', 'status' => 203], 203);
     }
 }