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}");
}
