PHP app\DB类代码示例

 public static function buscarAplicativoRelacionados2($idTicket_persona)
 {
     $result = \DB::table('aplicativo_ticket_persona')->select(['aplicativo.nombre_aplicativo', 'aplicativo.id', 'aplicativo_ticket_persona.created_at', 'aplicativo_ticket_persona.usucrea'])->whereIn('aplicativo_ticket_persona.ticket_persona_id', function ($query) use($idTicket_persona) {
         $query->select(['persona_tickets.id'])->from('persona_tickets')->where('persona_tickets.id', $idTicket_persona)->get();
     })->join('aplicativo', 'aplicativo_ticket_persona.aplicativo_id', '=', 'aplicativo.id')->get();
     return $result;
 }

 public static function notify($idArr = array(), $body, $type, $to_all = 0, $is_system = 0)
 {
     $currentId = auth()->id();
     if (!$currentId) {
         return;
     }
     $data = $notifiedUidArr = [];
     $now = \Carbon\Carbon::now();
     if ($to_all) {
         $data = ['user_id' => 0, 'body' => $body, 'type' => $type, 'to_all' => $to_all, 'is_system' => $is_system, 'created_at' => $now, 'updated_at' => $now];
     } elseif (!empty($idArr)) {
         $idArr = array_unique($idArr);
         foreach ($idArr as $id) {
             if ($id == $currentId) {
                 return;
             }
             $data[] = ['user_id' => $id, 'body' => $body, 'type' => $type, 'to_all' => $to_all, 'is_system' => $is_system, 'created_at' => $now, 'updated_at' => $now];
             $notifiedUidArr[] = $id;
         }
     }
     if (!empty($data)) {
         Notify::insert($data);
         if ($to_all) {
             \DB::table('users')->increment('notice_count');
         } elseif ($notifiedUidArr) {
             User::whereIn('id', $notifiedUidArr)->increment('notice_count');
         }
     }
 }

 /**
  * [getMatches description]
  * @param  [type] $tournament_id [description]
  * @param  [type] $player_id     [description]
  * @return [type]                [description]
  */
 public function getMatches($tournament_id, $player_id)
 {
     $m = \DB::table('matches')->join('tournaments', 'tournaments.tournament_id', '=', 'matches.tournament_id')->join('players as winner', 'winner.player_id', '=', 'matches.player1_id')->join('players as loser', 'loser.player_id', '=', 'matches.player2_id')->where('matches.tournament_id', '=', $tournament_id)->where(function ($q) use($player_id) {
         $q->where('player1_id', '=', $player_id)->orWhere('player2_id', '=', $player_id);
     })->orderBy('match_division')->orderBy('round')->select('*', 'winner.first_name as winner_first_name', 'winner.last_name as winner_last_name', 'loser.first_name as loser_first_name', 'loser.last_name as loser_last_name', 'loser.player_id as loser_id', 'tournaments.name as tournament')->distinct()->get();
     return $m;
 }

 /**
  * Inserts data into database, tables customers, occupancies, payments, and updates rooms
  * @param array $array_fields fields from booking form
  */
 public static function Insert($array_fields)
 {
     if (!false) {
         $conn = DB::GetConnection();
         $stmt = $conn->prepare("INSERT INTO customers (customer_id, customer_name, customer_lastname, customer_phone, customer_notes) VALUES (?, ?, ?, ?, ?) ");
         $stmt->bindParam(1, $array_fields['id'], \PDO::PARAM_INT);
         $stmt->bindParam(2, $array_fields['firstname'], \PDO::PARAM_STR);
         $stmt->bindParam(3, $array_fields['lastname'], \PDO::PARAM_STR);
         $stmt->bindParam(4, $array_fields['phone'], \PDO::PARAM_STR);
         $stmt->bindParam(5, $array_fields['notes'], \PDO::PARAM_STR);
         $stmt->execute();
         $insertkey = $conn->lastInsertId();
         $stmt = $conn->prepare("INSERT INTO occupancies (occupancy_id, occupancy_customer_id, occupancy_room_id, occupancy_firstdate, occupancy_lastdate) VALUES (?, ?, ?, ?, ?) ");
         $stmt->bindParam(1, $array_fields['id'], \PDO::PARAM_INT);
         $stmt->bindParam(2, $insertkey, \PDO::PARAM_INT);
         $stmt->bindParam(3, $array_fields['room_number'], \PDO::PARAM_INT);
         $stmt->bindParam(4, $array_fields['check_in'], \PDO::PARAM_STR);
         $stmt->bindParam(5, $array_fields['check_out'], \PDO::PARAM_STR);
         $stmt->execute();
         $payment_id = null;
         $payment_status_id = 1;
         $payment_amount = 20;
         $stmt = $conn->prepare("INSERT INTO payments (payment_id, payment_customer, payment_status_id, payment_amount, payment_date) VALUES (?, ?, ?, ?, ? )");
         $stmt->bindParam(1, $payment_id);
         $stmt->bindParam(2, $insertkey, \PDO::PARAM_INT);
         $stmt->bindParam(3, $payment_status_id);
         $stmt->bindParam(4, $payment_amount);
         $stmt->bindParam(5, $array_fields['check_out'], \PDO::PARAM_STR);
         $stmt->execute();
         $stmt = $conn->prepare("UPDATE rooms SET room_status_id = '1' WHERE room_id = ?");
         $stmt->bindParam(1, $array_fields['room_number'], \PDO::PARAM_INT);
         $stmt->execute();
     }
 }

 public function getFeira($id)
 {
     $dados = \DB::connection('mysql')->select(\DB::raw("SELECT *, date_format(data, '%d-%m-%Y %H:%i') as dataBR FROM feira WHERE id={$id}"))[0];
     //        $function = new Functions();
     //        $dados->data = $function->convertDataToBR("2016-01-02");
     return $dados;
 }

 public function closest($lat, $lng, $max_distance = 25, $max_locations = 10, $units = 'miles', $fields = false)
 {
     /*
      *  Allow for changing of units of measurement
      */
     switch ($units) {
         case 'miles':
             //radius of the great circle in miles
             $gr_circle_radius = 3959;
             break;
         case 'kilometers':
             //radius of the great circle in kilometers
             $gr_circle_radius = 6371;
             break;
     }
     /*
      *  Support the selection of certain fields
      */
     if (!$fields) {
         $fields = array('*');
     }
     /*
      *  Generate the select field for disctance
      */
     $disctance_select = sprintf("( %d * acos( cos( radians(%s) ) " . " * cos( radians( lat ) ) " . " * cos( radians( lng ) - radians(%s) ) " . " + sin( radians(%s) ) * sin( radians( lat ) ) " . ") " . ") " . "AS distance", $gr_circle_radius, $lat, $lng, $lat);
     return DB::table($table)->having('distance', '<', $max_distance)->take($max_locations)->order_by('distance', 'ASC')->get(array($fields, $disctance_select));
 }

 public static function boot()
 {
     parent::boot();
     static::deleting(function ($category) {
         DB::statement('DELETE FROM category_equipment WHERE category_id = ?', array($category->id));
     });
 }

 public function scoperegistro($query, $registro)
 {
     if (trim($registro) != "") {
         $query->where(\DB::raw("CONCAT(veh_movil)"), "ILIKE", "%{$registro}%");
         //$query->where('full_name',"LIKE", "%$name%");
     }
 }

 public function scopeDate($query, $date)
 {
     if (trim($date) != "") {
         $query->where(\DB::raw("CONCAT(date)"), "LIKE", "%{$date}%");
         Session::flash('message', 'Fecha:' . ' ' . $date . '  ' . 'Resultado de la busqueda');
     }
 }

 public function user()
 {
     //return $this->hasManyThrough('App\User','App\AccountLink',
     //	                          'user_id', 'id', 'app_user_id');
     $user = \DB::table('users')->join('account_links', 'users.id', '=', 'account_links.user_id')->where('account_links.app_user_id', '=', $this->id)->first();
     return $user;
 }

 public function scopeEfficiency($query, $efficiency)
 {
     if (trim($efficiency) != "") {
         $query->where(\DB::raw("CONCAT(efficiency)"), "LIKE", "%{$efficiency}%");
         Session::flash('message', 'Rendimiento:' . ' ' . $efficiency . '  ' . 'Resultado de la busqueda');
     }
 }

 public function scopeAn8($query, $an8)
 {
     if (trim($an8) != "") {
         $query->where(\DB::raw("CONCAT(emp_nombre,' ',emp_apellido,emp_an8,emp_identificacion)"), "ILIKE", "%{$an8}%");
         //$query->where('full_name',"LIKE", "%$name%");
     }
 }

 public static function AplicativoFaltantedelTicket($idPersona)
 {
     $resultado = \DB::table('aplicativo')->select(['aplicativo.id', 'aplicativo.nombre_aplicativo'])->whereNotIn('aplicativo.id', function ($query) use($idPersona) {
         $query->select(['aplicativo_ticket_persona.aplicativo_id'])->from('persona_tickets')->where('persona_tickets.persona_id', $idPersona)->join('aplicativo_ticket_persona', 'persona_tickets.id', '=', 'aplicativo_ticket_persona.ticket_persona_id')->get();
     })->get();
     return $resultado;
 }

 public static function getMealsWithTotals($selectedDate, $user_id)
 {
     $meals = Meal::select(\DB::raw('*, meals.id as meal_id'))->leftJoin('foods', 'meals.food_id', '=', 'foods.id')->where('planed_food', '0')->where('date', $selectedDate)->where('meals.user_id', $user_id)->orderBy('meals.created_at', 'desc')->paginate(100);
     $meals_planed = Meal::select(\DB::raw('*, meals.id as meal_id'))->leftJoin('foods', 'meals.food_id', '=', 'foods.id')->where('planed_food', '1')->where('date', $selectedDate)->where('meals.user_id', $user_id)->orderBy('meals.created_at', 'desc')->paginate(100);
     $totals = ['sum_weight' => 0, 'sum_kcal' => 0, 'sum_proteins' => 0, 'sum_carbs' => 0, 'sum_fibre' => 0, 'sum_fats' => 0];
     $totals_planed = ['sum_weight' => 0, 'sum_kcal' => 0, 'sum_proteins' => 0, 'sum_carbs' => 0, 'sum_fibre' => 0, 'sum_fats' => 0];
     foreach ($meals as $meal) {
         $totals['sum_weight'] += $meal['weight'];
         $totals['sum_kcal'] += $meal['kcal'] * $meal['weight'] / 100;
         $totals['sum_proteins'] += $meal['proteins'] * $meal['weight'] / 100;
         $totals['sum_carbs'] += $meal['carbs'] * $meal['weight'] / 100;
         $totals['sum_fats'] += $meal['fats'] * $meal['weight'] / 100;
         $totals['sum_fibre'] += $meal['fibre'] * $meal['weight'] / 100;
     }
     foreach ($meals_planed as $meal) {
         $totals_planed['sum_weight'] += $meal['weight'];
         $totals_planed['sum_kcal'] += $meal['kcal'] * $meal['weight'] / 100;
         $totals_planed['sum_proteins'] += $meal['proteins'] * $meal['weight'] / 100;
         $totals_planed['sum_carbs'] += $meal['carbs'] * $meal['weight'] / 100;
         $totals_planed['sum_fats'] += $meal['fats'] * $meal['weight'] / 100;
         $totals_planed['sum_fibre'] += $meal['fibre'] * $meal['weight'] / 100;
     }
     //TODO calculate totals above.
     //$totals = [];
     return ['meals' => $meals, 'meals_planed' => $meals_planed, 'totals' => $totals, 'totals_planed' => $totals_planed];
 }

 public function scopeName($query, $name)
 {
     if (trim($name) != "") {
         $query->where(\DB::raw("CONCAT(name)"), "LIKE", "%{$name}%");
         Session::flash('message', 'Nombre:' . ' ' . $name . '  ' . 'Resultado de la busqueda');
     }
 }