PHP User::whereRaw方法代码示例

 /**
  * Display the specified resource.
  *
  * @param  int  $id
  * @return Response
  */
 public function show($id)
 {
     $company = Company::findOrFail($id);
     $message = '';
     $users = User::whereRaw('company_id = ? order by first_name', array($company->id))->paginate(15)->appends(Input::except('page'));
     return view('company.show', ['company' => $company, 'users' => $users, 'message' => $message]);
 }

 function profile()
 {
     $id = Input::get('id');
     if (Auth::user()->id == $id) {
         return redirect("/profile");
     }
     $user = User::find($id);
     if ($user) {
         $data = ['member' => $user];
         $request = AddRequest::where('requested_by_user_id', Auth::user()->id)->where('recipient_user_id', $user->id)->first();
         if ($request) {
             $data['request'] = $request;
         }
         // check if a member available for the new team
         $xu = User::whereRaw('parent_user_id IS NULL')->where('id', $user->id)->get();
         if ($xu && count($xu) > 0) {
             $data['available'] = true;
         }
         // check if member is member of current user
         $cU = User::where('parent_user_id', Auth::user()->id)->get();
         if ($cU && count($cU) > 0) {
             $data['myMember'] = true;
         }
         return view('member.others-profile', $data);
     } else {
         return view('member.others-profile')->with('notFound', true);
     }
 }

 public function findUserByEmailOrFail($userEmail)
 {
     if (User::where('email', '=', $userEmail)->count() == 1) {
         return User::whereRaw('email = ?', [$userEmail])->get();
     } else {
         return false;
     }
 }

 public function findByUserNameOrCreate($provider, $userData)
 {
     $user = User::whereRaw('provider_id = ? and provider = ?', [$userData->id, $provider])->first();
     if (!$user) {
         $user = User::create(['provider' => $provider, 'provider_id' => $userData->id, 'name' => $userData->name, 'username' => $userData->nickname, 'email' => $userData->email, 'avatar' => $userData->avatar, 'active' => 1]);
         \Flash::message('New user created!');
     }
     $this->checkIfUserNeedsUpdating($userData, $user);
     return $user;
 }

 public static function tipoUserLogado()
 {
     if (!\Auth::check()) {
         return "*";
     }
     $user = User::whereRaw('name=?', [\Auth::user()->name]);
     if ($user->count() == 1) {
         return $user->first()->tipo;
     }
     return "";
 }

 public function search(Request $request)
 {
     $rules = array('page' => 'required', 'pageSize' => 'required');
     $validator = Validator::make($request->all(), $rules);
     if ($validator->fails()) {
         return response()->json(array('result' => false, 'message' => $validator->errors()->first()));
     }
     $role = $request->input('type');
     $active = $request->input('status');
     $email = $request->input('email');
     $page = $request->input('page');
     $pageSize = $request->input('pageSize');
     $skip = ($page - 1) * $pageSize;
     //$sql = \DB::table('users');
     $sql = User::whereRaw('(1+1=2)')->where('isAdmin', 0);
     if ($role == 99 && $active == 99 && !isset($email)) {
         $user = $sql->limit($skip, $pageSize)->get();
         return response()->json(['result' => true, 'data' => $user, 'total' => $sql->count()]);
     } else {
         if ($role == 99 && $active == 99 && isset($email)) {
             $count = $sql->email($email)->count();
             $user = $sql->email($email)->limit($skip, $pageSize)->get();
             return response()->json(['result' => true, 'data' => $user, 'total' => $count]);
         }
     }
     if (isset($role) && $role != 99) {
         $sql->role($role);
     }
     if (isset($active) && $active != 99) {
         $sql->active($active);
     }
     if (isset($email)) {
         $sql->email($email);
     }
     $sql_str = $sql->toSql();
     $count = $sql->count();
     $user = $sql->limit($skip, $pageSize)->get();
     //var_dump($role , $active);
     return response()->json(['result' => true, 'data' => $user, 'total' => $count, 'sql' => $sql_str]);
 }

 private function getActiveCompanyUsers($keyword)
 {
     $role_id = Auth::user()->role_id;
     $company_id = Auth::user()->company_id;
     $manager_id = Auth::user()->id;
     $filter = '';
     if ($role_id == 3) {
         $filter = ' and role_id in( 2 ) ';
         if (empty($keyword)) {
             return User::whereRaw('company_id = ? and status = ? and manager_id = ? ' . $filter . ' order by first_name', array($company_id, 'ACTIVE', $manager_id))->paginate(25)->appends(Input::except('page'));
         } else {
             return User::whereRaw(' ( first_name like ? or last_name like ? or email like ? ) and  status = ? and company_id = ? and manager_id = ? ' . $filter . ' order by first_name', array('%' . $keyword . '%', '%' . $keyword . '%', '%' . $keyword . '%', 'ACTIVE', $company_id, $manager_id))->paginate(25)->appends(Input::except('page'));
         }
     } elseif ($role_id == 4) {
         // En teoria deberia tambien incluir en la lista al mismo owner... se deberia agregar el id del owner
         $filter = ' and role_id in( 2, 3 ) ';
         if (empty($keyword)) {
             return User::whereRaw('company_id = ? and status = ? ' . $filter . ' order by first_name', array(Auth::user()->company_id, 'ACTIVE'))->paginate(25)->appends(Input::except('page'));
         } else {
             return User::whereRaw(' ( first_name like ? or last_name like ? or email like ? ) and  status = ? and company_id = ? ' . $filter . ' order by first_name', array('%' . $keyword . '%', '%' . $keyword . '%', '%' . $keyword . '%', 'ACTIVE', $company_id))->paginate(25)->appends(Input::except('page'));
         }
     }
 }

 public function login(Request $request)
 {
     $a = $request->all();
     $error = array();
     if (empty($a['username'])) {
         $error['username'] = "Vui lòng nhập Tên đăng nhập";
     }
     if (empty($a['password'])) {
         $error['password'] = "Vui lòng nhập mặt khẩu";
     }
     if (empty($error)) {
         $pa = md5($a['password']);
         $a = User::whereRaw(DB::raw("username='{$a['username']}' AND password='{$pa}'"))->first();
         if (!empty($a)) {
             Auth::login($a);
             return Response::json(Auth::user());
         } else {
             $a = User::whereRaw(DB::raw("email='{$a['username']}' AND password='{$pa}'"))->first();
             if (!empty($a)) {
                 Auth::login($a);
                 return Response::json(Auth::user());
             }
         }
         return Response::json(['error' => '1', 'username' => "Tên đăng nhập hoặc mật khẩu không chính xác"]);
     }
     $error['error'] = '1';
     return Response::json($error);
 }

 private function getActiveCompanyUsers($keyword)
 {
     $role_id = Auth::user()->role_id;
     $company_id = Auth::user()->company_id;
     $manager_id = Auth::user()->id;
     $filter = '';
     if ($role_id == 3) {
         $filter = ' and role_id in( 2 ) ';
         if (empty($keyword)) {
             return User::whereRaw('company_id = ? and manager_id = ? ' . $filter . ' order by first_name', array($company_id, $manager_id))->paginate(15)->appends(Input::except('page'));
         } else {
             return User::whereRaw(' ( first_name like ? or last_name like ? or email like ? ) and company_id = ? and manager_id = ?  ' . $filter . ' order by first_name', array('%' . $keyword . '%', '%' . $keyword . '%', '%' . $keyword . '%', $company_id, $manager_id))->paginate(15)->appends(Input::except('page'));
         }
     } elseif ($role_id == 4) {
         $filter = ' and role_id in( 2, 3 ) ';
         if (empty($keyword)) {
             return User::whereRaw('company_id = ? ' . $filter . ' order by first_name', array(Auth::user()->company_id))->paginate(15)->appends(Input::except('page'));
         } else {
             return User::whereRaw(' ( first_name like ? or last_name like ? or email like ? ) and company_id = ? ' . $filter . ' order by first_name', array('%' . $keyword . '%', '%' . $keyword . '%', '%' . $keyword . '%', $company_id))->paginate(15)->appends(Input::except('page'));
         }
     }
 }

 public function findByStatus()
 {
     $status = Input::get('status');
     $companies = Input::get('companies');
     $keyword = '';
     if ($status == null || $status == 'ALL') {
         $users = $this->getActiveUsers();
         $status = '';
         $companies = '';
         return view('user.list', compact('users', 'status', 'keyword', 'companies'));
     } else {
         if ($companies == null || $companies == 'default') {
             $companies = '';
             //$users = User::whereRaw(' ( company_id = ?  ) order by first_name', array($companies))->paginate(15)->appends(Input::except('page')); debe ser asi
             $users = User::whereRaw(' ( status = ?  ) order by first_name', array($status))->paginate(100)->appends(Input::except('page'));
             return view('user.list', compact('users', 'status', 'keyword', 'companies'));
         } else {
             //$users = User::whereRaw(' ( company_id = ?  ) order by first_name', array($companies))->paginate(15)->appends(Input::except('page')); debe ser asi
             $users = User::whereRaw(' ( status = ? and company_id = ? ) order by first_name', array($status, $companies))->paginate(100)->appends(Input::except('page'));
             return view('user.list', compact('users', 'status', 'keyword', 'companies'));
         }
     }
 }

 public function scopeGetUsersCount($query, $id)
 {
     $count = User::whereRaw('company_id = ?', array($id))->count();
     return $count . " users";
 }

 /**
  * Return data for all members within the system
  *
  * @return array
  */
 public function listMembers(Router $router)
 {
     $uri = $router->getCurrentRoute()->uri();
     $users = User::whereRaw('id != 1')->orderBy('created_at')->get();
     foreach ($users as $key => $user) {
         foreach ($user->standings as $standing) {
             if (empty($standing->end_date)) {
                 $user->activeStanding = $standing->type;
             }
         }
     }
     return view('members.list', compact('uri', 'users'));
 }

 public function friends()
 {
     $data = array('slide' => array('up', 'down', 'left', 'right'), 'size' => array('tile', 'tile-wide', 'tile-large', 'tile-big', 'tile-square', 'tile-big-x'), 'color' => array('blue', 'green', 'red', 'black'), 'friends' => User::whereRaw('namadepan<>"" and namabelakang<>"" and tanggallahir<>"0000-00-00" and blokir=0 and foto<>1')->orderByRaw('id')->paginate(25));
     return view('mine.friends')->with($data);
 }

 public function sharePost(Request $request)
 {
     if ($request->ajax()) {
         $validator = Validator::make(['post_id' => $request['share_post_id'], 'links' => $request['share_links'], 'groups' => $request['share_groups']], ['post_id' => 'required', 'links' => 'required_without:groups', 'groups' => 'required_without:links|unique:post_group_taggings,post_id,group_id'], ['links.required_without' => 'Either link or group is required for sharing.', 'groups.required_without' => 'Either group or link is required for sharing.', 'groups.unique' => 'Either group or link is required for sharing.']);
         if ($validator->fails()) {
             return response()->json(array('success' => false, 'errors' => $validator->getMessageBag()->toArray()), 500);
         } else {
             $isShared = 0;
             $sharePostId = $request['share_post_id'];
             $post = Postjob::findOrFail($sharePostId);
             $data = [];
             if ($post != null) {
                 // share to link
                 if ($request['share_links'] != null) {
                     $taggedUsers = $request['share_links'];
                     $post->taggeduser()->attach($taggedUsers, array('mode' => 'shared', 'tag_share_by' => Auth::user()->induser_id));
                     $induserIds = implode(', ', $taggedUsers);
                     $userIds = User::whereRaw('induser_id in (' . $induserIds . ')')->get(['id']);
                     foreach ($userIds as $r) {
                         $to_user = $r->id;
                         if ($to_user != null) {
                             $notification = new Notification();
                             $notification->from_user = Auth::user()->id;
                             $notification->to_user = $to_user;
                             $notification->remark = 'has shared post: ' . $post->unique_id;
                             $notification->operation = 'post sharing';
                             $notification->save();
                         }
                     }
                     $isShared++;
                 }
                 // share to group
                 if ($request['share_groups'] != null) {
                     $taggedGroups = $request['share_groups'];
                     $post->taggedGroup()->attach($taggedGroups, array('mode' => 'shared', 'tag_share_by' => Auth::user()->induser_id));
                     $isShared++;
                 }
                 // myactivity update
                 if ($isShared > 0) {
                     $postActivity = Postactivity::where('post_id', '=', $sharePostId)->where('user_id', '=', Auth::user()->id)->first();
                     if ($postActivity == null) {
                         $postActivity = new Postactivity();
                         $postActivity->post_id = $sharePostId;
                         $postActivity->user_id = Auth::user()->id;
                         $postActivity->share = 1;
                         $postActivity->share_dtTime = \Carbon\Carbon::now(new \DateTimeZone('Asia/Kolkata'));
                         $postActivity->save();
                     } elseif ($postActivity != null && $postActivity->share == 0) {
                         $postActivity->share = 1;
                         $postActivity->share_dtTime = \Carbon\Carbon::now(new \DateTimeZone('Asia/Kolkata'));
                         $postActivity->save();
                     }
                     $sharecount = Postactivity::where('post_id', '=', $sharePostId)->sum('share');
                     $data['sharecount'] = $sharecount;
                     $data['page'] = 'home';
                 }
             }
             return response()->json(['success' => true, 'data' => $data]);
         }
     } else {
         return redirect("/home");
     }
 }

 public function run()
 {
     DB::table('role_user')->delete();
     /*
             Role::create(['name' => 'receipt'       , 'display_name' => 'Receiver'                , 'description' => 'Receiver worker at a rework table'      ]);
             Role::create(['name' => 'putAwayReserve', 'display_name' => 'Put Away to reserve'     , 'description' => 'Put Away to reserve locations'          ]);
             Role::create(['name' => 'putAwayPick'   , 'display_name' => 'Put Away to pick face'   , 'description' => 'Put Away to pick face locations'        ]);
             Role::create(['name' => 'replen'        , 'display_name' => 'Replen'                  , 'description' => 'Move Inventory from reserve to pick face locations']);
             Role::create(['name' => 'poReconcile'   , 'display_name' => 'PO Receipt Reconciliator', 'description' => 'Reconcile Receipt POs variances'        ]);
             Role::create(['name' => 'teamLead'      , 'display_name' => 'Shift Team Leader'       , 'description' => 'Team Lead for the shift'                ]);
             Role::create(['name' => 'super'         , 'display_name' => 'Shift Supervisor'        , 'description' => 'Supervising the shift'                  ]);
             Role::create(['name' => 'manager'       , 'display_name' => 'Shift Manager'           , 'description' => 'Managing shift activities'              ]);
             Role::create(['name' => 'admin'         , 'display_name' => 'Administrator'           , 'description' => 'Administrates users of this application']);
             Role::create(['name' => 'support'       , 'display_name' => 'IT Support'              , 'description' => 'IT Support for this application'        ]);
     
             User::create(['name' => 'worker' , 'email' => 'worker@bisconsulting.net' , 'password' => Hash::make( 'vital123' )]);
             User::create(['name' => 'teamLead'  , 'email' => 'super@bisconsulting.net'  , 'password' => Hash::make( 'vital123' )]);
             User::create(['name' => 'super'  , 'email' => 'super@bisconsulting.net'  , 'password' => Hash::make( 'vital123' )]);
             User::create(['name' => 'manager', 'email' => 'manager@bisconsulting.net', 'password' => Hash::make( 'vital123' )]);
             User::create(['name' => 'admin'  , 'email' => 'admin@bisconsulting.net'  , 'password' => Hash::make( 'vital123' )]);
             User::create(['name' => 'rbowers', 'email' => 'rbowers@legacyscs.com'    , 'password' => Hash::make( 'vital123' )]);
             User::create(['name' => 'pneal'  , 'email' => 'pneal@bisconsulting.net'  , 'password' => Hash::make( 'vital123' )]);
     */
     $roles = Role::whereRaw("name in ('receipt', 'putAwayReserve', 'putAwayPick', 'replen')")->get();
     $users = User::whereRaw("name in ('worker')")->get();
     foreach ($roles as $role) {
         foreach ($users as $user) {
             DB::table('role_user')->insert(['role_id' => $role->id, 'user_id' => $user->id]);
         }
     }
     $roles = Role::whereRaw("name in ('receipt', 'putAwayReserve', 'putAwayPick', 'replen', 'poReconcile', 'teamLead')")->get();
     $users = User::whereRaw("name in ('teamLead')")->get();
     foreach ($roles as $role) {
         foreach ($users as $user) {
             DB::table('role_user')->insert(['role_id' => $role->id, 'user_id' => $user->id]);
         }
     }
     $roles = Role::whereRaw("name in ('receipt', 'putAwayReserve', 'putAwayPick', 'replen', 'poReconcile', 'teamLead', 'super')")->get();
     $users = User::whereRaw("name in ('super')")->get();
     foreach ($roles as $role) {
         foreach ($users as $user) {
             DB::table('role_user')->insert(['role_id' => $role->id, 'user_id' => $user->id]);
         }
     }
     $roles = Role::whereRaw("name in ('receipt', 'putAwayReserve', 'putAwayPick', 'replen', 'poReconcile', 'teamLead', 'super', 'manager')")->get();
     $users = User::whereRaw("name in ('manager', 'rbowers')")->get();
     foreach ($roles as $role) {
         foreach ($users as $user) {
             DB::table('role_user')->insert(['role_id' => $role->id, 'user_id' => $user->id]);
         }
     }
     $roles = Role::whereName('admin')->get();
     $users = User::whereRaw("name in ('admin', 'rbowers')")->get();
     foreach ($roles as $role) {
         foreach ($users as $user) {
             DB::table('role_user')->insert(['role_id' => $role->id, 'user_id' => $user->id]);
         }
     }
     $roles = Role::get();
     $users = User::whereName('pneal')->get();
     foreach ($roles as $role) {
         foreach ($users as $user) {
             DB::table('role_user')->insert(['role_id' => $role->id, 'user_id' => $user->id]);
         }
     }
 }