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; }
/** * 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; }
function getData() { $query = 'Select * from ' . $this->table; $data = Capsule::select($query); return json_encode($data); //return $data; }
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')); }
/** * @return bool */ public function getSchema() { if (!$this->hasTable()) { return false; } $colums = Capsule::select("SHOW COLUMNS FROM {$this->tableName}"); return $colums; }
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; } }
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 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))); }
/** * @inheritdoc */ public function validate($value, Constraint $constraint) { $model = $constraint->getModel(); $table = $model->getTable(); $fields = $constraint->getFields(); $q = sprintf('SELECT id FROM `%s` WHERE ', $table); $fieldsCodnditions = []; foreach ($fields as $f) { $fieldsCodnditions[] = $f . '=' . "'" . addslashes($model->{$f}) . "'"; } $q .= implode(' AND ', $fieldsCodnditions); $q .= ';'; $ids = DB::select($q); if (0 !== count($ids)) { $this->context->addViolation($constraint->message, ['%field%' => implode(', ', $constraint->getFields())]); } }
protected function storeIncremental($json) { $merchants = json_decode($json, true); // grab merchants from the database $dbMerchants = collect(DB::table('merchants')->get(['nMerchantID', 'dtLastUpdated']))->keyBy('nMerchantID')->toArray(); // grab an array of columns in the merchants table $columns = DB::select('select COLUMN_NAME as `column` from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = \'fmtc_merchants\''); // set the counters for reporting $insertCount = 0; $updateCount = 0; $removeCount = 0; // walk through the merchants from a merchant feed foreach ($merchants as $merchant) { // is this merchant in the database? if (isset($dbMerchants[$merchant['nMerchantID']])) { // is it active? if ($merchant['cStatus'] == 'active') { // has it been updated if ($dbMerchants[$merchant['nMerchantID']]->dtLastUpdated != $merchant['dtLastUpdated']) { // update it DB::table('merchants')->where('nMerchantID', $merchant['nMerchantID'])->update($this->formatForInsertion($merchant, $columns)); $updateCount++; } } else { // if it's not active remove it DB::table('merchants')->where('nMerchantID', $merchant['nMerchantID'])->delete(); $removeCount++; } } else { // make sure it's active if ($merchant['cStatus'] == 'active') { // insert it (this is faster than building an insert queue and bulk inserting) DB::table('merchants')->insert($this->formatForInsertion($merchant, $columns)); $insertCount++; } } } //---- debugging // debug($removeCount . ' removed'); // debug($insertCount . ' inserted'); // debug($updateCount . ' updated'); //----- return true; }
protected function execute(InputInterface $input, OutputInterface $output) { //$products = Product::select(['id', 'description', 'price'])->get(); $query = "\n select\n products.id as CODICE,\n products.description as NOME,\n products.price as PREZZO,\n products.category as CATEGORIA,\n count(menu_products.id) as NUMERO_PUBBLICAZIONI,\n count(menu_orders_products.id) as NUMERO_VENDITE,\n DATE_FORMAT(\n (select menu.start_date\n from menu_products left join menu on (menu.id = menu_products.menu_id)\n where menu_products.product_id = products.id\n order by menu.start_date desc\n limit 1),\n \"%d %b %Y\") as ULTIMA_PUBBLICAZIONE\n\n from products\n left join menu_products on (products.id = menu_products.product_id)\n left join menu_orders_products on (menu_products.id = menu_orders_products.menu_product_id)\n\n GROUP BY products.id\n order by\n FIELD(CATEGORIA, 'Primi Piatti', 'Secondi di Carne', 'Secondi di Pesce', 'Verdure, ortaggi e contorni', 'Piatti Vegetariani', 'Contorni', 'Panini Imbottiti', 'Frutta e Dessert', 'Pane', 'Salumi', 'Formaggi', 'Bibite', 'Snack', 'Aggiunte', 'Condimenti'),\n NOME ASC\n "; //we create the CSV into memory $csv = Writer::createFromFileObject(new \SplTempFileObject()); //object to array $data = array_map(function ($row) { return (array) $row; }, Capsule::select($query)); //we insert the CSV header $csv->insertOne(array_keys($data[0])); $csv->setOutputBOM(Writer::BOM_UTF8); // The PDOStatement Object implements the Traversable Interface // that's why Writer::insertAll can directly insert // the data into the CSV $csv->insertAll($data); $csv->output(); }
public function testRawQueries() { // Insert $inserted = Capsule::insert('INSERT INTO articles (title) VALUES (:title)', ['title' => static::ARTICLE_TITLE]); $this->assertTrue($inserted); // Last Insert ID $id = Capsule::connection()->getPdo()->lastInsertId(); $this->assertEquals(1, $id); // Select $articles = Capsule::select('SELECT * FROM articles WHERE id = ?', [$id]); $this->assertCount(1, $articles); $this->assertEquals($articles[0]->title, static::ARTICLE_TITLE); // Delete $deleted = Capsule::delete('DELETE FROM articles WHERE id = ?', [$id]); $this->assertEquals(1, $deleted); // Count $count = Capsule::selectOne('SELECT COUNT(*) as nb FROM articles'); $this->assertEquals(0, $count->nb); }
/** * Vérification de la connection sur base des informations fournies */ public function checkConnection() { $success = false; $message = ''; $config = $this->getPostConfiguration(); try { $this->makeConnection($config); /** * Essaie de montrer les tables ! */ switch ($config['driver']) { case 'mysql': $tables = Capsule::select('show tables'); break; } $success = true; $message = 'Connexion établie avec succés !'; } 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 getServiciosByEmpleado(Request $request, Response $response) { $response = $response->withHeader('Content-type', 'application/json'); $idEmpleado = $request->getAttribute("idEmpleado"); $data = Servicio::select('servicio.*')->join("serviciosempleado", "serviciosempleado.idServicio", "=", "servicio.id")->where('serviciosempleado.idEmpleado', '=', $idEmpleado)->where('servicio.estado', '=', 'ACTIVO')->get(); if (count($data) == 0) { $response = $response->withStatus(404); } else { for ($i = 0; $i < count($data); $i++) { $query = "SELECT " . "Count(tu.turno) as numeroTurno " . "FROM " . "turno as tu " . "WHERE tu.idEmpleado = " . $idEmpleado . " AND " . "tu.idServicio = " . $data[$i]->id . " AND " . "(tu.estadoTurno <> 'TERMINADO' AND tu.estadoTurno <> 'CANCELADO')"; $dataNumeroTurno = DB::select(DB::raw($query)); $data[$i]['numeroTurno'] = $dataNumeroTurno[0]->numeroTurno; $data[$i]['turnoActual'] = 0; } } $response->getBody()->write($data); return $response; }
function empleadosDisponibles(Request $request, Response $response) { $response = $response->withHeader('Content-type', 'application/json'); $idServicio = $request->getAttribute("idServicio"); $idSucursal = $request->getAttribute("idSucursal"); $fecha = $request->getAttribute("fecha"); $hora = $request->getAttribute("hora"); $cupos = $request->getAttribute("cupos"); $dataMitiempo = ServiciosSucursal::select("minutos")->where("idServicio", "=", $idServicio)->where("idSucursal", "=", $idSucursal)->first(); $minutos = 60; if ($data != null) { $minutos = $dataMitiempo->minutos; } $empleado = Empleado::select("*")->where("idSucursal", "=", $idSucursal)->where("idPerfil", "=", 2)->get(); //$horaFinal = "(sec_to_time(time_to_sec('$hora') + (time_to_sec('$hora') * $cupos)))"; $tiempo = 0; for ($i = 0; $i < $cupos; $i++) { $tiempo += $minutos; } $horaFinal = "ADDTIME('{$hora}', SEC_TO_TIME({$tiempo}*60))"; $data = array(); for ($i = 0; $i < count($empleado); $i++) { $query = "SELECT " . "tur.id " . "FROM " . "turno tur " . "WHERE " . "tur.idServicio = {$idServicio} AND " . "tur.idSucursal = {$idSucursal} AND " . "tur.idEmpleado = " . $empleado[$i]->id . " AND " . "tur.fechaReserva = '{$fecha}' AND " . "tur.reserva = 'A' AND " . "(tur.estadoTurno <> 'TERMINADO' AND tur.estadoTurno <> 'CANCELADO') AND (" . "(TIMESTAMP('{$fecha}','{$hora}') >= tur.horaReserva AND TIMESTAMP('{$fecha}','{$hora}') < tur.horaFinalReserva) OR " . "(TIMESTAMP('{$fecha}',{$horaFinal}) > tur.horaReserva AND TIMESTAMP('{$fecha}',{$horaFinal}) <= tur.horaFinalReserva))"; //. "(TIMESTAMP('$fecha','$hora') < tur.horaReserva AND TIMESTAMP('$fecha',$horaFinal) > tur.horaReserva))"; $disp = DB::select(DB::raw($query)); if (count($disp) == 0) { $data[] = $empleado[$i]; } } $response->getBody()->write(json_encode($data)); return $response; }
return $app->json(['count' => $count]); }); /** * Student by id */ $rest->get('/students/{id}', function ($id) use($app, $modelToArray) { $model = Student::findOrFail($id); return $app->json($modelToArray($model, ['level'])); })->assert('id', '\\d+'); /** * Search students */ $rest->get('/students/search', function () use($app) { $q = addslashes($app['request']->get('query')); $search = strtolower($q) . '%'; $result = DB::select("SELECT `id`\n FROM `students`\n WHERE `name` LIKE '{$search}';\n "); $result = array_map(function ($itm) { return $itm['id']; }, $result); $students = Student::whereIn('id', $result)->get(); return $app->json($students); }); /** * Student creation */ $rest->post('/students', function (Request $req) use($app) { $data = $req->request->all(); $s = new Student($data); try { $s->validate(); $s->save();
/** * Adds a new Database Server to the system. * @param array $data */ public function add(array $data) { $validator = Validator::make($data, ['name' => 'required|string|max:255', 'host' => 'required|ip|unique:database_servers,host', 'port' => 'required|numeric|between:1,65535', 'username' => 'required|string|max:32', 'password' => 'required|string', 'linked_node' => 'sometimes']); if ($validator->fails()) { throw new DisplayValidationException($validator->errors()); } DB::beginTransaction(); try { $capsule = new Capsule(); $capsule->addConnection(['driver' => 'mysql', 'host' => $data['host'], 'port' => $data['port'], 'database' => 'mysql', 'username' => $data['username'], 'password' => $data['password'], 'charset' => 'utf8', 'collation' => 'utf8_unicode_ci', 'prefix' => '', 'options' => [\PDO::ATTR_TIMEOUT => 3]]); $capsule->setAsGlobal(); // Allows us to check that we can connect to things. Capsule::select('SELECT 1 FROM dual'); $dbh = new Models\DatabaseServer(); $dbh->fill(['name' => $data['name'], 'host' => $data['host'], 'port' => $data['port'], 'username' => $data['username'], 'password' => Crypt::encrypt($data['password']), 'max_databases' => NULL, 'linked_node' => !empty($data['linked_node']) && $data['linked_node'] > 0 ? $data['linked_node'] : NULL]); $dbh->save(); DB::commit(); } catch (\Exception $ex) { DB::rollBack(); throw $ex; } }
} } if (!$f) { $tehranOther[] = $oth; } } foreach ($otherOther as $odd) { $address = explode('،', $odd->address, 3); $query = "insert into `location` (`location_type`, `name`,`location_trace`,`latitude`,`longitude`)\n values ('RESTAURANT','{$odd->name}' ,'<city>{$address['0']}</city><region></region><section>{$address['1']}</section><additional>{$address['2']}</additional>',{$odd->latitude},{$odd->longitude})"; $model = new \geo\src\model\location(); try { $model->hydrateRaw($query); } catch (Exception $ex) { } } $polygons = \Illuminate\Database\Capsule\Manager::select("SELECT *, AsText(polygon) AS poly FROM geoLocation WHERE geoLocation.detail = '' "); foreach ($polygons as $pol) { $geo = geoPHP::load($pol->poly); $multipoint_points = $geo->getComponents(); $geoJson = json_decode($multipoint_points[0]->out('json')); $polygon = new \League\Geotools\Polygon\Polygon($geoJson->coordinates); foreach ($tehranOther as $json) { if ($json->latitude != '') { $point = new \geo\src\model\Point(); $json = (object) $json; $point->setLattitude($json->latitude); $point->setLangtiude($json->longitude); $address = explode('،', $odd->address, 2); $result = $polygon->pointInPolygon(new \League\Geotools\Coordinate\Coordinate([$point->getLangtiude(), $point->getLattitude()])); if ($result) { $existIn[] = $json;
/** * @return SearchView */ public function searchx() { // Get post data $data = $this->_getRequest()->query->all(); $data['types'] = Arrays::value($data, 'types', ''); $data['tags'] = Arrays::value($data, 'tags', ''); $data['search'] = Arrays::value($data, 'search', ''); $data['authors'] = Arrays::value($data, 'authors', ''); $data['order'] = Arrays::value($data, 'order', 'downloads_m'); $data['page'] = Arrays::value($data, 'page', 1); ksort($data); // Cache $memcache = new \Memcached(); $key = 'search-' . md5(json_encode(array_values($data))); $search = $memcache->get($key); if ($search === false) { // Start search $packages = Package::select('author', 'name', 'description', 'type', 'downloads_m')->where('name', '!=', '')->where('author', '!=', ''); // Types if ($data['types']) { $types = explode(',', $data['types']); $packages->whereIn('type', $types); } // Tags if ($data['tags']) { $tags = explode(',', $data['tags']); $tags = array_map('trim', $tags); $tags = array_filter($tags, 'is_numeric'); $tags = implode(',', $tags); $tags = DB::select('SELECT package_id FROM package_tag WHERE tag_id IN(' . $tags . ') GROUP BY package_id'); $packageIds = ipull($tags, 'package_id'); $packages->whereIn('id', $packageIds); } // Search if ($data['search']) { $packages->where(function ($query) use($data) { $query->where('author', 'LIKE', '%' . $data['search'] . '%')->orWhere('name', 'LIKE', '%' . $data['search'] . '%')->orWhere('description', 'LIKE', '%' . $data['search'] . '%'); }); } // Authors if ($data['authors']) { $authors = explode(',', $data['authors']); $authors = array_map('trim', $authors); $authors = array_filter($authors, 'is_numeric'); $authors = implode(',', $authors); $authors = DB::select('SELECT package_id FROM author_package WHERE author_id IN(' . $authors . ') GROUP BY package_id'); $packageIds = ipull($authors, 'package_id'); $packages->whereIn('id', $packageIds); } // Maintainers if (isset($data['maintainers']) && $data['maintainers']) { $maintainers = explode(',', $data['maintainers']); $maintainers = array_map('trim', $maintainers); $maintainers = array_filter($maintainers, 'is_numeric'); $maintainers = implode(',', $maintainers); $maintainers = DB::select('SELECT package_id FROM maintainer_package WHERE maintainer_id IN(' . $maintainers . ') GROUP BY package_id'); $packageIds = ipull($maintainers, 'package_id'); $packages->whereIn('id', $packageIds); } // Order switch ($data['order']) { case 'downloads_t': $packages->orderBy('downloads', 'desc'); break; case 'downloads_m': $packages->orderBy('downloads_m', 'desc'); break; case 'downloads_d': $packages->orderBy('downloads_d', 'desc'); break; case 'stars': $packages->orderBy('stars', 'desc'); break; case 'author': $packages->orderBy('author', 'asc'); break; case 'name': $packages->orderBy('name', 'asc'); break; } $packages->orderBy('downloads_m', 'desc'); // Pagination $count = $packages->count(); $skip = ($data['page'] - 1) * self::PER_PAGE; $packages->skip($skip)->take(self::PER_PAGE); $pages = (int) ceil($count / self::PER_PAGE); // Make into an array as you can not serialize PDO objects $packages = $packages->get()->toArray(); // Cache $memcache->set($key, [$pages, $packages], self::WEEK); } else { list($pages, $packages) = $search; } $this->_setTitle('Search'); return new SearchView($packages, $data, $pages); }
/** * Execute the command. * * @param InputInterface $input * @param username $username * @param password $password * @param collation $collation * * @return $rows mixed */ protected function getDatabases($input, $username, $password, $collation) { $this->connect(); $tables = 'SCHEMA_NAME'; if ($collation) { $tables .= ', DEFAULT_COLLATION_NAME'; } $query = ' SELECT ' . $tables . ' FROM INFORMATION_SCHEMA.SCHEMATA ORDER BY SCHEMA_NAME'; $dbs = Capsule::select($query); $rows = []; for ($i = 0; $i < count($dbs); ++$i) { $rows[$i][] = $dbs[$i]->SCHEMA_NAME; if ($collation) { $rows[$i][] = $dbs[$i]->DEFAULT_COLLATION_NAME; } } return $rows; }
function contaempleadosucursal(Request $request, Response $response) { $response = $response->withHeader('Content-type', 'application/json'); $id = $request->getAttribute("idsucursal"); $fechainicial = $request->getAttribute("fechainicial"); $fechafinal = $request->getAttribute("fechafinal"); $data = DB::select(DB::raw("select CONCAT(empleado.nombres,' ',empleado.apellidos) as empleado," . "empleado.identificacion,sucursal.nombre,ingresos.idEmpleado,sum(ingresos.valor) as total," . "empleado.email,empleado.telefono,empleado.estado " . "from sucursal " . "inner join empleado on empleado.idSucursal = sucursal.id " . "inner join ingresos on ingresos.idEmpleado = empleado.id " . "where sucursal.id = " . $id . " and ingresos.fecha BETWEEN '" . $fechainicial . "' and " . "'" . $fechafinal . "'" . "GROUP BY ingresos.idEmpleado")); $response->getBody()->write(json_encode($data)); return $response; }
case 'DOUBLE': case 'DECIMAL': $type = 'DOUBLE'; break; case 'DATE': $type = 'DATE'; break; } return $type; }); $twig->addFilter($hiveFilter); $dbName = Capsule::connection()->getDatabaseName(); $db = new DB($dbName); $query = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '" . Capsule::connection()->getDatabaseName() . "'"; $tb_objs = Capsule::select($query); foreach ($tb_objs as $tb_obj) { $table = new Table($tb_obj); $columns = Capsule::select("select * from information_schema.COLUMNS where table_name = '" . $table->name . "' and TABLE_SCHEMA = '" . $dbName . "'"); foreach ($columns as $column_obj) { $column = new Column($column_obj); $table->columns[] = $column; } $fks = Capsule::select("select TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where CONSTRAINT_NAME like 'fk_%' and table_name = '" . $table->name . "' and TABLE_SCHEMA ='" . Capsule::connection()->getDatabaseName() . "'"); foreach ($fks as $fk) { $table->foreignKeys[] = $fk->REFERENCED_TABLE_NAME; } $db->tables[] = $table; $db->tables[$table->name] = $table; } print $twig->render($params, ['db' => $db]); print "\n";
public function update($id) { $event = Event::findOrFail($id); $event->load(['scores' => function ($query) { $query->orderByRaw('-finished desc'); $query->orderByRaw('(select lastname from t_players where id=player_id)'); $query->orderByRaw('(select forename from t_players where id=player_id)'); }]); $canEdit = Auth::user()->almighty || $event->league->season >= $this->season - 1 && $event->league->admins()->where('player_id', Auth::user()->id)->first(); // Unauthorized if (!$canEdit) { $_SESSION['ALERT'] = alert("Unauthorized!", "", "error"); return redirect("/events/{$id}"); } // Update Players List if ($player_ids = $_REQUEST['player_ids']) { foreach ($player_ids as $player_id) { $score = new Score(); $score->event_id = $event->id; $score->player_id = $player_id; $score->save(); } $_SESSION['ALERT'] = alert("Players Added!", "Don't forget to update the players count.", "success"); return redirect("/events/{$id}/edit"); } // Update Flash Message if (isset($_REQUEST['flash_message'])) { $event->flash_message = $_REQUEST['flash_message']; $event->save(); $_SESSION['ALERT'] = alert("Message Set!", "The flash message was set successfully and will be displayed to all players who register for this event.", "success"); return redirect("/events/{$id}/edit"); } // Missing Data... if (!isset($_REQUEST['possible']) || !isset($_REQUEST['scores']) || !isset($_REQUEST['positions']) || $event->league->bonuses && !isset($_REQUEST['bonuses'])) { $_SESSION['ALERT'] = alert("Whoops!", "It looks like there was missing data in your request. Please try again.", "error"); return redirect("/events/{$id}/edit"); } // Update Player Count $event->possible = max($_REQUEST['possible'] ?: 0, 0); $event->save(); // Update Players, Positions & Bonuses $scores = $_REQUEST['scores']; $positions = $_REQUEST['positions']; if ($event->league->bonuses) { $bonuses = $_REQUEST['bonuses']; } if ($scores && $positions && (!$event->league->bonuses || $bonuses)) { foreach ($scores as $idx => $score) { $score = $event->scores[$score]; $score->finished = $positions[$idx]; if ($event->league->bonuses) { $score->bonus = $bonuses[$idx]; } if ($score->finished == -1) { $score->delete(); } else { $score->save(); } } } // Reload the scores $event->load(['scores' => function ($query) { $query->orderByRaw('-finished desc'); $query->orderByRaw('(select lastname from t_players where id=player_id)'); $query->orderByRaw('(select forename from t_players where id=player_id)'); }]); $_SESSION['ALERT'] = alert("Scores Updated!", "The scores have been modified. Please double check your changes.", "success"); /** Send Email **/ $subject = "Event Updated"; Mail::send('emails.events', compact('subject', 'event'), function ($m) use($subject, $event) { $m->subject("{$subject}: {$event->league->venue->name}, Season {$event->league->season}, Week {$event->week_num}, Game {$event->game_num}, " . ($event->possible ?: 0) . " Players"); }); // Position conflicts $position = DB::select('select count(finished) as `total`, count(distinct(finished)) as `unique` from t_scores where event_id=? and finished>0', [$event->id])[0]; if ($position->total > $position->unique) { // Alert will be added in `edit` handler return redirect("/events/{$id}/edit"); } // Bonuse conflicts if ($event->league->bonuses) { $bonusQuery = " select b.label, b.per_event "; $bonusQuery .= " from t_bonuses b "; $bonusQuery .= " left join t_event_bonuses eb on eb.bonus_id = b.id "; $bonusQuery .= " where eb.event_id=? "; $bonusQuery .= " group by b.id "; $bonusQuery .= " having count(*) > b.per_event "; if (sizeof(DB::select($bonusQuery, [$event->id]))) { // Alert will be added in `edit` handler return redirect("/events/{$id}/edit"); } } return redirect("/events/{$id}"); }