PHP Manager::select方法代码示例

 /**
  * check database connection based on provided setting
  */
 public function checkConnection()
 {
     $success = false;
     $message = '';
     $config = $this->getPostConfiguration();
     try {
         $this->makeConnection($config);
         /**
          * Just trying to show tables with current connection
          */
         switch ($config['driver']) {
             case 'mysql':
                 $tables = Capsule::select('show tables');
                 break;
             case 'sqlite':
                 $tables = Capsule::select("SELECT * FROM sqlite_master WHERE type='table'");
                 break;
             case 'sqlsrv':
                 $tables = Capsule::select("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'");
                 break;
             case 'pgsql':
                 $tables = Capsule::select("SELECT * FROM pg_catalog.pg_tables");
                 break;
         }
         $success = true;
         $message = 'Successfully connected!';
     } catch (Exception $e) {
         $success = false;
         $message = $e->getMessage();
     }
     Response::headers()->set('Content-Type', 'application/json');
     Response::setBody(json_encode(array('success' => $success, 'message' => $message, 'config' => $config)));
 }

 function getSectorEmpresas(Request $request, Response $response)
 {
     $response = $response->withHeader('Content-type', 'application/json');
     $data = Parametros::select("*")->first();
     $km = $data["diametro_busqueda"];
     $idSector = $request->getAttribute("id");
     $lat = $request->getAttribute("latitud");
     $lng = $request->getAttribute("longitud");
     $query = "SELECT " . "(6371 * ACOS( SIN(RADIANS(su.latitud)) * SIN(RADIANS({$lat})) + COS(RADIANS(su.longitud - {$lng})) * " . "COS(RADIANS(su.latitud)) * COS(RADIANS({$lat})))) AS distancia, " . "em.id, " . "em.nit, " . "em.razonSocial, " . "em.logo, " . "'' as servicios " . "FROM sucursal su " . "INNER JOIN " . "empresa em ON (em.id = su.idEmpresa) " . "INNER JOIN " . "sectorempresa secemp ON (secemp.idEmpresa = em.id && secemp.idSector =  {$idSector}) " . "WHERE su.Estado = 'ACTIVO' AND em.estado = 'ACTIVO' " . "HAVING distancia < {$km} ORDER BY distancia ASC";
     $data = DB::select(DB::raw($query));
     for ($i = 0; $i < count($data); $i++) {
         $val = "";
         $ser = Servicio::select("nombre")->where("idEmpresa", "=", $data[$i]->id)->get();
         $tam = count($ser);
         for ($j = 0; $j < $tam; $j++) {
             $val .= $ser[$j]->nombre;
             if ($j + 1 < $tam) {
                 $val .= ",";
             }
         }
         $data[$i]->servicios = $val;
     }
     $response->getBody()->write(json_encode($data));
     return $response;
 }

 public function index()
 {
     $season = $_REQUEST['season'] ?: $this->season;
     $activity = $_REQUEST['activity'] ?: 'Poker';
     $venue_id = $_REQUEST['venue_id'] ?: League::where('season', $season)->where('activity', $activity)->value('venue_id');
     $league = League::where('season', $season)->where('activity', $activity)->where('venue_id', $venue_id)->first();
     if (!$league->id) {
         $_SESSION['ALERT'] = alert("No League Found!", "There were no leagues found matching the specified parameters.", "warning");
         return sizeof($_REQUEST) ? redirect("/rankings") : redirect("/");
     }
     $rankings_query = ' select s.player_id as id, concat(p.lastname, ", ", p.forename) as name, concat(p.forename, " ", p.lastname) as fullname, count(*) as games, sum(s.points) as total ';
     $rankings_query .= ' from t_scores s ';
     $rankings_query .= ' join t_events e on e.id = s.event_id ';
     $rankings_query .= ' join t_players p on p.id = s.player_id ';
     $rankings_query .= ' where e.league_id=? ';
     $rankings_query .= ' group by s.player_id ';
     $rankings = DB::select($rankings_query, [$league->id]);
     $points_query = ' select points ';
     $points_query .= ' from t_scores ';
     $points_query .= ' where player_id=? ';
     $points_query .= ' and event_id in ( ';
     $points_query .= ' select id ';
     $points_query .= ' from t_events ';
     $points_query .= ' where league_id=? ';
     $points_query .= ' ) ';
     $points_query .= ' order by points desc ';
     $points_query .= ' limit ' . $league->ranked_at;
     array_walk($rankings, function (&$ranking) use($points_query, $league) {
         $ranking->points = DB::select($points_query, [$ranking->id, $league->id]);
         $ranking->points = array_map(function ($p) {
             return $p->points;
         }, $ranking->points);
         $ranking->value = $ranking->games >= $league->ranked_at ? $league->ranking == 'AVG' ? floor(array_sum($ranking->points) / $league->ranked_at) : array_sum($ranking->points) : -1;
     });
     $ranked = array_filter($rankings, function ($ranking) {
         return $ranking->value !== -1;
     });
     usort($ranked, function ($a, $b) {
         return $b->value - $a->value;
     });
     $unranked = array_filter($rankings, function ($ranking) {
         return $ranking->value === -1;
     });
     usort($unranked, function ($a, $b) {
         return $b->total - $a->total;
     });
     $seasonPointsLeaders = array_filter($rankings, function ($ranking) {
         return $ranking->total > 0;
     });
     usort($seasonPointsLeaders, function ($a, $b) {
         return $b->total - $a->total;
     });
     $seasonPointsLeaders = array_slice($seasonPointsLeaders, 0, 1);
     $wildCardWinners = User::whereHas('scores', function ($query) use($league) {
         $query->where('finished', '1')->whereHas('event', function ($query) use($league) {
             $query->where('league_id', $league->id)->where('week_num', 'Wild Card');
         });
     })->get();
     return view('pages.rankings', compact('league', 'ranked', 'unranked', 'seasonPointsLeaders', 'wildCardWinners'));
 }

 protected function storeFull($json)
 {
     $categories = json_decode($json, true);
     // grab categories from the database
     $dbCategories = collect(DB::table('categories')->get(['cSlug']))->keyBy('cSlug')->toArray();
     // grab an array of columns in the categories table
     $columns = DB::select('select COLUMN_NAME as `column` from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = \'fmtc_categories\'');
     // set the counters for reporting
     $insertCount = 0;
     $removeCount = 0;
     // walk through the categories from a merchant feed
     $jsonCategoryIds = [];
     foreach ($categories as $category) {
         // is the category missing from the database?
         if (!isset($dbCategories[$category['cSlug']])) {
             // insert it (this is faster than building an insert queue and bulk inserting)
             DB::table('categories')->insert($this->formatForInsertion($category, $columns));
             $insertCount++;
         }
         // collect an array of ids to aid in the remove	queue
         $jsonCategoryIds[] = $category['cSlug'];
     }
     // remove old categories showing up in the database but not in the new merchant feed.
     $removeQueue = array_diff(array_keys($dbCategories), $jsonCategoryIds);
     $removeCount = count($removeQueue);
     foreach ($removeQueue as $categoryId) {
         DB::table('categories')->where('cSlug', $categoryId)->delete();
     }
     //---- debugging
     // debug($removeCount . ' removed');
     // debug($insertCount . ' inserted');
     //-----
     return true;
 }

 function getData()
 {
     $query = 'Select * from ' . $this->table;
     $data = Capsule::select($query);
     return json_encode($data);
     //return $data;
 }

 /**
  * @return bool
  */
 public function getSchema()
 {
     if (!$this->hasTable()) {
         return false;
     }
     $colums = Capsule::select("SHOW COLUMNS FROM {$this->tableName}");
     return $colums;
 }

 public function promedio(Request $request, Response $response)
 {
     $response = $response->withHeader('Content-type', 'application/json');
     $idCliente = $request->getAttribute("idCliente");
     $query = "SELECT COALESCE(AVG(calificacion),0) as promedio FROM calificacioncliente WHERE idCliente = " . $idCliente;
     $data = DB::select(DB::raw($query));
     $response->getBody()->write(json_encode($data));
     return $response;
 }

 function getNeighbourChurches()
 {
     $neighbours = DB::select("SELECT d.distance tavolsag,t.nev,t.ismertnev,t.varos,t.id tid FROM distance as d\n            LEFT JOIN templomok as t ON (tid1 <> :tid1 AND tid1 = id ) OR (tid2 <> :tid2 AND tid2 = id )\n            WHERE ( tid1 = :tid3 OR tid2 = :tid4 ) AND distance <= 10000 \n            AND t.id IS NOT NULL \n            ORDER BY distance ", ['tid1' => $this->id, 'tid2' => $this->id, 'tid3' => $this->id, 'tid4' => $this->id]);
     $this->neigbourChurches = (array) $neighbours;
     if (!isset($this->neigbourChurches)) {
         $neighbours = DB::select("SELECT d.distance tavolsag,t.nev,t.ismertnev,t.varos,t.id tid FROM distance as d\n                LEFT JOIN templomok as t ON (tid1 <> :tid1 AND tid1 = id ) OR (tid2 <> :tid2 AND tid2 = id )\n                WHERE ( tid1 = :tid3 OR tid2 = :tid4 )\n                ORDER BY distance \n                LIMIT 1", ['tid1' => $this->id, 'tid2' => $this->id, 'tid3' => $this->id, 'tid4' => $this->id]);
         $this->neigbourChurches = (array) $neighbours;
     }
 }

 function contasector(Request $request, Response $response)
 {
     $response = $response->withHeader('Content-type', 'application/json');
     //sum(ingresos.valor) as total,
     $id = $request->getAttribute("idSector");
     $fechainicial = $request->getAttribute("fechainicial");
     $fechafinal = $request->getAttribute("fechafinal");
     $data = DB::select(DB::raw("select sum(ingresos.valor) as total,ingresos.fecha,sectorempresa.id," . "empresa.razonSocial,sucursal.nombre" . " from sectorempresa " . "inner join empresa on empresa.id = sectorempresa.idEmpresa " . "inner join sucursal on sucursal.idEmpresa = empresa.id " . "inner join empleado on empleado.idSucursal = sucursal.id " . "inner join ingresos on ingresos.idEmpleado = empleado.id " . "where sectorempresa.idSector = " . $id . " and ingresos.fecha BETWEEN '" . $fechainicial . "' and " . "'" . $fechafinal . "' " . "GROUP BY empresa.razonSocial"));
     $response->getBody()->write(json_encode($data));
     return $response;
 }

 public function show($id)
 {
     $category = $this->categories[$id - 1];
     $query = " select player_id as id, player_fullname as fullname, {{ query }} as value ";
     $query .= " from v_player_stats where activity = 'Poker' and total_games > 96 ";
     $query .= " order by {{ field }} desc limit 3 ";
     $query = str_replace('{{ query }}', $category->query, $query);
     $query = str_replace('{{ field }}', $category->field, $query);
     $players = DB::select($query);
     return sizeof($players) ? view('hall-of-fame.show', compact('players')) : '<div class="hof-loading"><em>No Scores Found...</em></div>';
 }

 public function show($id = null)
 {
     if (!$id) {
         $id = Auth::user()->id;
     }
     if (!$id) {
         redirect('/auth/login');
     }
     $user = User::findOrFail($id);
     $stats = DB::select('select * from v_player_stats where player_id = ?', [$user->id]);
     return view('users.show', compact('user', 'stats'));
 }

 public function get()
 {
     $user_id = isset($_GET['user_id']) ? (int) $_GET['user_id'] : 0;
     $depth = isset($_GET['depth']) ? (int) $_GET['depth'] : 1;
     if ($user_id == 0 or $depth == 0) {
         echo json_encode([]);
         return;
     }
     $fields = ['level1.user_id AS user', 'level1.friend_id AS friend'];
     $joins = [];
     if ($depth > 1) {
         foreach (range(2, $depth) as $l) {
             $fields[] = 'level' . $l . '.friend_id AS mutual' . ($l - 1);
             $joins[] = 'LEFT JOIN friends level' . $l . ' ON (level' . ($l - 1) . '.`friend_id` = level' . $l . '.`user_id` AND level' . $l . '.`friend_id` != level' . ($l - 1) . '.`user_id`)';
         }
     }
     $query = 'SELECT ';
     $query .= implode(',', $fields);
     $query .= ' FROM friends level1 ';
     $query .= implode(' ', $joins);
     $query .= ' WHERE level1.user_id = ' . $user_id;
     $user_ids = [];
     $records = json_decode(json_encode(DB::select($query)), true);
     foreach ($records as $r => $record) {
         foreach ($record as $key => $id) {
             $id = (int) $id;
             if (!($id > 0)) {
                 unset($records[$r][$key]);
                 continue;
             }
             if (in_array($id, $user_ids)) {
                 continue;
             }
             $user_ids[] = $id;
         }
     }
     $Users = User::whereIn('id', $user_ids)->get();
     $users = [];
     foreach ($Users as $User) {
         $users[$User->id] = $User->toArray();
     }
     foreach ($records as $r => $record) {
         foreach ($record as $key => $id) {
             $id = (int) $id;
             if (!($id > 0)) {
                 continue;
             }
             $records[$r][$key] = $users[$id];
         }
     }
     echo json_encode($records);
 }

 public function getRegisteredEventsAttribute()
 {
     $schedule_query = ' select e.id, e.activity, v.fullname as venue, e.datetime ';
     $schedule_query .= ' from t_scores s ';
     $schedule_query .= ' left join t_events e on e.id = s.event_id ';
     $schedule_query .= ' left join t_venues v on v.id = e.venue_id ';
     $schedule_query .= ' where s.player_id = ? and e.datetime >= ? ';
     $schedule_query .= ' order by week_num, venue_id, datetime asc ';
     $events = DB::select($schedule_query, [$this->id, date('Y-m-d')]);
     return array_map(function ($event) {
         return Event::find($event->id);
     }, $events);
 }

 protected function storeIncremental($json)
 {
     $deals = json_decode($json, true);
     // grab deals from the database
     $dbDealsRows = DB::table('deals')->get(['nCouponID', 'cLastUpdated']);
     foreach ($dbDealsRows as $row) {
         $dbDeals[$row->nCouponID] = $row;
     }
     // grab an array of columns in the deals table
     $columns = DB::select('select COLUMN_NAME as `column` from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = \'fmtc_deals\'');
     // set the counters for reporting
     $insertCount = 0;
     $updateCount = 0;
     $removeCount = 0;
     // walk through the deals from a deal feed
     foreach ($deals as $deal) {
         // is this deal in the database?
         if (isset($dbDeals[$deal['nCouponID']])) {
             // is it active?
             if ($deal['cStatus'] == 'active') {
                 // has it been updated
                 if ($dbDeals[$deal['nCouponID']]->cLastUpdated != $deal['cLastUpdated']) {
                     // update it
                     $this->updateDeal($deal, $columns);
                     $updateCount++;
                 }
             } else {
                 // if it's not active remove it
                 $this->removeDeal($deal['nCouponID']);
                 $removeCount++;
             }
         } else {
             // make sure it's active
             if ($deal['cStatus'] == 'active') {
                 // insert it (this is faster than building an insert queue and bulk inserting)
                 $this->insertDeal($deal, $columns);
                 $insertCount++;
             }
         }
     }
     //---- debugging
     // debug($removeCount . ' removed');
     // debug($insertCount . ' inserted');
     // debug($updateCount . ' updated');
     //-----
     return true;
 }

 /**
  * check database connection based on provided setting
  */
 public function checkConnection()
 {
     $success = false;
     $message = '';
     $config = $this->getPostConfiguration();
     try {
         $this->makeConnection($config);
         $tables = Capsule::select('show tables');
         $success = true;
         $message = 'Successfully connected!';
     } catch (Exception $e) {
         $success = false;
         $message = $e->getMessage();
     }
     Response::headers()->set('Content-Type', 'application/json');
     Response::setBody(json_encode(array('success' => $success, 'message' => $message, 'config' => $config)));
 }