PHP DB::table方法代码示例

 /**
  * Add new item to the Customer Cart.
  *
  * @return Response
  */
 public function add()
 {
     $itemid = Input::get('itemid');
     $userid = \Auth::user()->id;
     $selqty = Input::get('selqty');
     $itemdata = \DB::select("SELECT A.itemcode, B.aedprice, B.usdprice, B.ispromo, B.promodisc FROM itemmas A LEFT JOIN itemprices B ON A.itemcode = B.itemcode WHERE A.isactive = 1 AND B.isactive = 1 AND B.classid IN (SELECT classid FROM users WHERE id = " . $userid . ") AND A.ID = " . $itemid);
     $cartitem = [];
     $cartitem['userid'] = \Auth::user()->id;
     $cartitem['itemcode'] = $itemdata[0]->itemcode;
     $cartitem['selqty'] = $selqty;
     $cartitem['currency'] = \Auth::user()->currency;
     if ($cartitem['currency'] == 'AED') {
         $itemrate = $itemdata[0]->aedprice;
     } else {
         $itemrate = $itemdata[0]->usdprice;
     }
     if ($itemdata[0]->ispromo == 1) {
         $cartitem['itemrate'] = number_format($itemrate * (1 - $itemdata[0]->promodisc / 100), 2);
     } else {
         $cartitem['itemrate'] = $itemrate;
     }
     $cartitem['totalprice'] = $cartitem['selqty'] * $cartitem['itemrate'];
     $cartitem['updatedby'] = \Auth::user()->id;
     \DB::table('currentcart')->insert($cartitem);
     //dd($cartitem);
 }

 /**
  * 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) {
     }
 }

 /**
  * Show the form for editing the specified resource.
  *
  * @param  int  $id
  * @return \Illuminate\Http\Response
  */
 public function edit($id)
 {
     $selectlist = \DB::table('presentations')->orderBy('presentationName')->lists('presentationName', 'id');
     $emptyOption = array('0' => '------');
     $selectlist = $emptyOption + $selectlist;
     return view('presentations.edit', ['presentationID' => $selectlist, 'selectedID' => $id]);
 }

 public function actualiza(Request $request, $id)
 {
     $cantdb = \DB::table('productos')->select('CantExistente')->where('ID', $id)->first();
     $cantinput = $request->input('cantidads');
     $resul = $cantdb->CantExistente - intval($cantinput);
     \DB::table('productos')->where('ID', $id)->update(['CantExistente' => $resul]);
     $productoid = \DB::table('productos')->select('ID')->where('ID', $id)->first();
     $cantinput = $request->input('cantidads');
     $nombreus = $request->input('usuarios');
     $nombreus2 = \DB::table('usuarios')->select('Nombre')->where('ID', $nombreus)->first();
     $nombrpr = \DB::table('productos')->select('Nombre')->where('ID', $id)->first();
     $salida = new salidasModelo();
     $salida->Producto_ID = $productoid->ID;
     $salida->Nombre_Producto = $nombrpr->Nombre;
     $salida->Cantidad = intval($cantinput);
     $salida->Usuario_ID = intval($nombreus);
     $salida->Nombre_salida = $nombreus2->Nombre;
     $salida->save();
     /*  $salida=salidasModelo::getInfoSalida($id);
         dd($salida);
         $vista = view('generapdf', compact($salida));
         $dompdf = \App::make('dompdf.wrapper');
         $dompdf->loadHTML($vista);
         return $dompdf->stream();*/
     return Redirect()->back();
     //    return view('generapdf');
 }

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

 /**
  * Display a listing of the resource.
  *
  * @return Respon
  */
 public function index(Request $request)
 {
     //return 'listado la configuracion de la base de datos';
     $conf = \DB::table('configuracion')->orderBy('id', 'ASC')->paginate(5);
     //->get()
     return view('configuracion.index')->with('datos', $conf)->with('buscar', $request->buscar);
 }

 public function ajaxStoreBajas(Requests\BajasRequest $request)
 {
     $baja = new Bajas($request->all());
     \Auth::user()->bajas()->save($baja);
     \DB::table('animales')->where('id', $baja->animal_id)->update(['estado' => 'Baja']);
     return redirect()->route('rodeos.show', $baja->rodeo_id);
 }

 /**
  * checks whether new email is already in the database
  * old and new email can be similler for the selected admin only.
  *
  * @param string        $someString
  *
  * @return string
  */
 public function update(Request $request)
 {
     $email = $request->email;
     $id = $request->id;
     $job = $request->job;
     $firstname = $request->firstname;
     $lastname = $request->lastname;
     $admin = \DB::select('SELECT email FROM users WHERE email = "' . $email . '" not in
                                         (select email from users where id!=' . $id . ')');
     if ($this->CheckInternet()) {
         if ($admin == null) {
             //update
             \DB::table('users')->where('id', $id)->update(['firstname' => $firstname, 'lastname' => $lastname, 'job' => $job, 'email' => $email]);
             $sendMail = new EmailController();
             $content = 'Dear ' . $firstname . ', Your profile been Updated.
                 Your Email is ' . $email . ' Please visit
                 WWW.COUPLEY.COM/cp-admin#/AdminLogin';
             $subject = 'COUPLEY Administrator Update';
             $sendMail->SendMail($email, $firstname, $subject, $content);
             return response()->json(['you can use this email' => $admin, 'status' => 200], 200);
         } else {
             return response()->json(['email' => 'email already exists', 'status' => 201], 201);
         }
     } else {
         return response()->json(['status' => 203], 203);
     }
 }

 public function ticket($ticketId)
 {
     $loots = \DB::table('lootgames')->join('loot', function ($join) {
         $join->on('lootgames.id', '=', 'loot.game_id')->where('loot.user_id', '=', $this->user->id);
     })->join('users', 'lootgames.user_id', '=', 'users.id')->groupBy('lootgames.id')->orderBy('lootgames.id', 'desc')->select('lootgames.*', 'users.username as winner_username', 'users.steamid64 as winner_steamid64', 'users.avatar as winner_avatar')->orderBy('id', 'steam_price')->get();
     return view('support.ticket', compact('loots'));
 }

 /**
  * Show the form for editing the specified resource.
  *
  * @param  int  $id
  * @return \Illuminate\Http\Response
  */
 public function edit($id)
 {
     $rodeo = Rodeos::findOrFail($id);
     $potreros = \DB::table('potreros')->lists('nombre', 'id');
     $personal = \DB::table('users')->where('rol', 0)->lists('name', 'id');
     return view('rodeos.edit', array('potreros' => $potreros, 'personal' => $personal, 'rodeo' => $rodeo));
 }

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

 public function updateEdit()
 {
     $data = array('item_name' => Input::get('item_name'), 'item_desc' => Input::get('item_desc'), 'item_desc' => Input::get('item_desc'), 'item_img' => Input::get('item_img'), 'start_bid' => Input::get('start_bid'), 'buy_now_price' => Input::get('buy_now_price'), 'last_bid' => Input::get('last_bid'), 'status' => Input::get('status'), 'start_date' => Input::get('start_date'), 'max_date' => Input::get('max_date'), 'seller' => Input::get('seller'));
     $id = Input::get('id');
     DB::table('items')->where('id', '=', $id)->update($data);
     return Redirect::to('item')->with('message', 'Data berhasil diupdate');
 }

 public function getNearLocation()
 {
     $inputs = \Input::get('current_pos');
     $data = json_decode($inputs);
     $builder = \DB::table('events')->where('event_date', '>', date('Y-m-d H:i:s'));
     if (!empty($data->search_string)) {
         $builder = $builder->where('events.name', 'like', '%' . $data->search_string . '%');
     }
     if (!empty($data->filters)) {
         $boardGameIds = [];
         foreach ($data->filters as $boardGameId) {
             $boardGameIds[] = (int) $boardGameId;
         }
         $builder = $builder->whereIn('boardgame_id', $boardGameIds);
     }
     if (!empty($data->search_date)) {
         $builder = $builder->whereDate('event_date', '=', date('Y-m-d', strtotime($data->search_date)));
     }
     if (!empty($data->current_pos->lat)) {
         //todo show only near location
     }
     if (!empty($data->current_pos->lng)) {
         //todo show only near location
     }
     $events = $builder->get();
     $arr = [];
     foreach ($events as $event) {
         $arr[] = ['id' => $event->id, 'lat' => floatval($event->latitude), 'lng' => floatval($event->longitude)];
     }
     return \Response::json($arr);
 }

 public function autocomplete()
 {
     dd(Input::get('companyName'));
     if (Input::get('companyName')) {
         $term = Input::get('term');
         $results = array();
         $queries = \DB::table('emp_table')->where('emp_name', 'LIKE', '%' . $term . '%')->take(5)->get();
         foreach ($queries as $query) {
             $results[] = ['id' => $query->id, 'value' => $query->company_name];
         }
         return \Response::json($results);
     }
     if (Input::get('employeeName')) {
         dd('lkhkhlk');
         $term = Input::get('term');
         $results = array();
         $queries = \DB::table('emp_table')->where('emp_name', 'LIKE', '%' . $term . '%')->where('emp_status', '=', 'Active')->take(5)->get();
         foreach ($queries as $query) {
             $results[] = ['id' => $query->emp_id, 'value' => $query->emp_name];
         }
         dd('sss');
         return \Response::json($results);
     } else {
     }
 }