public function scopePhase($query, $phase)
{
if (trim($phase) != "") {
$query->where(\DB::raw("CONCAT(phase)"), "LIKE", "%{$phase}%");
Session::flash('message', 'Fase:' . ' ' . $phase . ' ' . 'Resultado de la busqueda');
}
}
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 getDesigns(array $options = array())
{
$designs = $this->select(\DB::raw('
designs.*,
c.value AS cityName,
d.value AS districtName,
w.value AS wardName,
c.slug AS citySlug,
d.slug AS districtSlug,
w.slug AS wardSlug
'))->leftJoin('locations AS c', function ($join) {
$join->on('designs.city', '=', 'c.id')->where('c.type', '=', 1);
})->leftJoin('locations AS d', function ($join) {
$join->on('designs.district', '=', 'd.id')->where('d.type', '=', 2);
})->leftJoin('locations AS w', function ($join) {
$join->on('designs.district', '=', 'w.id')->where('w.type', '=', 3);
});
if (isset($options['citySlug'])) {
$designs = $designs->where('c.slug', $options['citySlug']);
}
if (isset($options['districtSlug'])) {
$designs = $designs->where('d.slug', $options['districtSlug']);
}
if (isset($options['wardSlug'])) {
$designs = $designs->where('w.slug', $options['wardSlug']);
}
return $designs->orderBy('designs.id', 'desc');
}
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 scopeName($query, $name)
{
if (trim($name) != "") {
$query->where(\DB::raw("CONCAT(name)"), "LIKE", "%{$name}%");
Session::flash('message', 'Nombre:' . ' ' . $name . ' ' . 'Resultado de la busqueda');
}
}
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 scopeNPerGroupRedefine($query, $group, $n = 10, $custom)
{
// queried table
$table = $this->getTable();
// initialize MySQL variables inline
$query->from(\DB::raw("(SELECT @rank:=0, @group:=0) as vars, ({$custom}) as `{$table}`"));
// if no columns already selected, let's select *
if (!$query->getQuery()->columns) {
$query->select("*");
}
// make sure column aliases are unique
$groupAlias = 'group_' . md5(time());
$rankAlias = 'rank_' . md5(time());
// apply mysql variables
$query->addSelect(\DB::raw("@rank := IF(@group = {$group}, @rank+1, 1) as {$rankAlias}, @group := {$group} as {$groupAlias}"));
// make sure first order clause is the group order
$query->getQuery()->orders = (array) $query->getQuery()->orders;
array_unshift($query->getQuery()->orders, ['column' => $group, 'direction' => 'asc']);
// prepare subquery
$subQuery = $query->toSql();
// prepare new main base Query\Builder
$newBase = $this->newQuery()->from(\DB::raw("({$subQuery}) as {$table}"))->mergeBindings($query->getQuery())->where($rankAlias, '<=', $n)->getQuery();
// replace underlying builder to get rid of previous clauses
$query->setQuery($newBase);
}
public function scopeCategory($query, $category)
{
if (trim($category) != "") {
$query->where(\DB::raw("CONCAT(category)"), "LIKE", "%{$category}%");
Session::flash('message', 'Categoria:' . ' ' . $category . ' ' . '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 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 scopecontrol($query, $control)
{
if (trim($control) != "") {
$query->where(\DB::raw("CONCAT(ctl_id)"), "ILIKE", "%{$control}%");
//$query->where('full_name',"LIKE", "%$name%");
}
}
function getName()
{
DB::table('users')->whereExists(function ($query) {
$query->select(DB::raw(1))->from('orders')->whereRaw('orders.user_id = users.id');
})->get();
//select * from users where exists (select 1 from orders where orders.user_id = users.id)
//生成上面那句语句 exists 判断括号内语句是否为真 为真则搜索 为假则放弃
}
public function newQuery($excludeDeleted = true)
{
$raw = '';
foreach ($this->geofields as $column) {
$raw .= ' astext(' . $column . ') as ' . $column . ' ';
}
return parent::newQuery($excludeDeleted)->addSelect('*', DB::raw($raw));
}
public function scopeName($query, $name)
{
if (trim($name) != "") {
$query->where(\DB::raw("usr_name"), "LIKE", "%{$name}%");
//consulta Db::raw
//$query->where('full_name',"LIKE", "%$name%");
}
}
private function getRandomWord()
{
if (self::USE_DATABASE == 0) {
$request = Requests::get('http://randomword.setgetgo.com/get.php');
return strtolower($request->body);
}
return \App\Word::orderBy(\DB::raw('RAND()'))->first()->word;
}
/**
* Returns an array of all possible question types.
*
* @return array
*/
public static function possibleTypes()
{
$type = \DB::select(\DB::raw('SHOW COLUMNS FROM questions WHERE Field = "question_type"'))[0]->Type;
preg_match('/^enum\\((.*)\\)$/', $type, $matches);
$values = [];
foreach (explode(',', $matches[1]) as $value) {
$values[] = trim($value, "'");
}
return $values;
}
/**
* Returns board uris with this permission.
*
* @param \App\Contracts\PermissionUser|null $user User roles must belong to. Defaults to null.
* @param bool $anonymous Determines if we should allow generic, unassigned roles. Defaults true.
* @return Collection of \App\Board->board_uri strings
*/
public function getBoardsWithPermissions(PermissionUser $user = null, $anonymous = true)
{
// Identify roles which affect this user.
// Sometimes we will only want direct assignments.
// This includes null user_id assignments for anonymouse users.
$userRoles = UserRole::select('role_id')->where(function ($query) use($user, $anonymous) {
if ($anonymous) {
$query->whereNull('user_id');
}
if ($user instanceof PermissionUser && !$user->isAnonymous()) {
$query->orWhere('user_id', $user->user_id);
} else {
if (!$anonymous) {
$query->where(\DB::raw('0'), '1');
}
}
})->get()->pluck('role_id');
if (!$userRoles) {
return collect();
}
$inheritRoles = Role::select('role_id', 'inherit_id')->whereIn('role_id', $userRoles)->get()->pluck('inherit_id')->filter(function ($item) {
return !is_null($item);
});
// Identify roles which use this permission,
// or which borrow inherited roles.
$validRoles = RolePermission::select('role_id', 'permission_id')->where(function ($query) use($userRoles, $inheritRoles) {
$query->orWhereIn('role_id', $userRoles);
if ($inheritRoles) {
$query->orWhereIn('role_id', $inheritRoles);
}
})->where('permission_id', $this->permission_id)->get()->pluck('role_id');
if (!$validRoles) {
return collect();
}
// Find the intersection of roles we have and roles we want.
$intersectIdents = collect($userRoles)->intersect(collect($validRoles));
$inheritIdents = collect($inheritRoles)->intersect(collect($validRoles));
$intersectRoles = collect();
if ($intersectIdents) {
// These are only roles which are directly assigned to us with
// this permission.
$intersectRoles = collect(Role::select('role_id', 'board_uri')->whereIn('role_id', $intersectIdents)->get()->pluck('board_uri'));
}
if ($inheritIdents) {
$intersectRoles = collect(Role::select('role_id', 'board_uri')->whereIn('inherit_id', $inheritIdents)->whereIn('role_id', $userRoles)->get()->pluck('board_uri'))->merge($intersectRoles);
}
return $intersectRoles;
}
public function scopeGetActiveQuestions($query, $wheres = array(), $sort_field = "questions.id", $sort_type = "questions.desc")
{
return $query->leftJoin(\DB::raw('(
SELECT question_id, COUNT(*) total_votes
FROM votes) votes'), function ($join) {
$join->on('questions.id', '=', 'votes.question_id');
})->where(function ($query) use($wheres) {
foreach ($wheres as $field => $value) {
if ($value == null) {
continue;
}
$operators = "=";
$query->where($field, $operators, $value);
}
})->where("questions.active", 1)->select("questions.*", 'votes.total_votes')->orderBy($sort_field, $sort_type)->get();
}
public function scopeHaversine($query, $lat, $lng, $max_distance = 20, $units = 'kilometers', $fields = false)
{
if (empty($lat)) {
$lat = 0;
}
if (empty($lng)) {
$lng = 0;
}
/*
* 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;
}
// dd($lat,$lng);
/*
* Support the selection of certain fields
*/
if (!$fields) {
$fields = array('place_name', 'CONCAT(lng, ",", lat) as pos ', ' tips');
}
/*
* Generate the select field for disctance
*/
$distance_select = sprintf("ROUND(( %d * acos( cos( radians(%s) ) " . " * cos( radians( lat ) ) " . " * cos( radians( lng ) - radians(%s) ) " . " + sin( radians(%s) ) * sin( radians( lat ) ) " . " ) " . ")\n \t\t\t\t\t\t\t, 2 ) " . "AS distance\n\t\t\t\t\t ", $gr_circle_radius, $lat, $lng, $lat);
$data = $query->select(\DB::raw(implode(',', $fields) . ',' . $distance_select))->having('distance', '<=', $max_distance)->orderBy('distance', 'desc');
//echo '<pre>';
//echo $query->toSQL();
//echo $distance_select;
//echo '</pre>';
//die();
//
//$queries = DB::getQueryLog();
//$last_query = end($queries);
//var_dump($last_query);
//die();
return $data;
}
/**
* Returns a few posts for the front page.
*
* @static
* @param int $number How many to pull.
* @param boolean $sfwOnly If we only want SFW boards.
* @return Collection of static
*/
public static function getRecentImages($number = 16, $sfwOnly = true)
{
$query = static::where('is_spoiler', false)->whereHas('storage', function ($query) {
$query->where('has_thumbnail', true);
})->whereHas('post.board', function ($query) use($sfwOnly) {
$query->where('is_indexed', true);
$query->where('is_overboard', true);
if ($sfwOnly) {
$query->where('is_worksafe', '=', true);
}
})->with('storage')->with('post.board')->take($number);
if ($query->getQuery()->getConnection() instanceof \Illuminate\Database\PostgresConnection) {
// PostgreSQL does not support the MySQL standards non-compliant group_by syntax.
// DISTINCT itself selects distinct combinations [attachment_id,file_idd, not just file_id.
// We have to use raw SQL to accomplish this.
$query->select(\DB::raw("DISTINCT ON (file_id) *"));
$query->orderBy('file_id', 'desc');
} else {
$query->orderBy('attachment_id', 'desc');
$query->groupBy('file_id');
}
return $query->get();
}
public static function score($group, $date)
{
switch ($group) {
case 'SSD-AMM':
$q = ['SSD-AMM-GSL', 'SSD-CLOUD', 'SSD-AMM-SV', 'SSD-MOBILITY', 'SSD-DSC'];
break;
case 'SSD-BOM':
$q = ['SSD-BOM', 'PSD-BOM'];
break;
case 'SSD-DEL':
$q = ['SSD-DEL', 'PSD-DEL'];
break;
case 'SSD-FRA':
$q = ['SSD-FRA'];
break;
case 'SSD-MOW':
$q = ['SSD-MOW'];
break;
case 'SSD-SJO':
$q = ['SSD-SJO', 'PSD-SJO'];
break;
default:
['SSD-AMM-GSL', 'SSD-CLOUD', 'SSD-AMM-SV', 'SSD-MOBILITY', 'SSD-BOM', 'PSD-BOM', 'SSD-DEL', 'PSD-DEL', 'SSD-FRA', 'SSD-MOW', 'SSD-SJO', 'PSD-SJO', 'SSD-DSC'];
break;
}
$Ticket = Ticket::select(\DB::raw('
Format(
IfNull((
(
Sum( `qops`.`tickets_items`.`weight` * `qops`.`tickets_items`.`score` )
/
Sum( `qops`.`tickets_items`.`weight` )
)
* 100), 0), 2)
AS `score`'))->join('tickets_items', 'ticket.ref_number', '=', 'tickets_items.ref_number')->whereIn('requester', $q)->where('ticket.status', 3)->get();
return $Ticket->count();
}
public static function getLastTen()
{
return self::select(\DB::raw('concat(first_name, " ", middle_name, " ", last_name ) as fullname,
IF(sensortype_id = 1,timestamp, null) AS timein,
IF(sensortype_id = 0,timestamp, null ) AS timeout '), 'terminal')->join('employees', 'employees.id', '=', 'employee_times.employee_id')->join('terminals', 'terminals.id', '=', 'employee_times.terminal_id')->orderBy('timestamp', 'desc')->get();
}
public function scopeNombre($query, $nombre)
{
$query->where(\DB::raw("CONCAT(Nombre,' ', APaterno)"), "LIKE", "%{$nombre}%");
//$query->where('id', "=", "$nombre");
}
public function scopeMonthly($query)
{
$dt = new \DateTime();
$month = intval($dt->format('n'));
$year = intval($dt->format('Y'));
$target_year_month = null;
if ($month == '1') {
$target_year_month = $year - 1 . '12';
} else {
$target_year_month = $year . ($month - 1 < 10 ? "0" . ($month - 1) : $month - 1);
}
$query->Join('projects', function ($join) {
$join->on('claims.project_id', '=', 'projects.id')->where('projects.reports_type', '=', \DB::raw('monthly'));
})->whereRaw('EXTRACT(YEAR_MONTH FROM claims.created_at)=' . $target_year_month);
return $query;
}
public function fullname($id)
{
$doctor = Doctor::select(\DB::raw("CONCAT(doctor_fname,' ', doctor_mname,' ',doctor_lname) AS full_name"))->where('id', '=', $id)->lists('full_name')->first();
return $doctor;
}
public function scopeName($query, $name)
{
if (trim($name) != "") {
$query->where(\DB::raw("CONCAT(first_name,' ',last_name)"), "LIKE", "%{$name}%");
}
}
public function ScopeId_producto($query, $id_producto)
{
//$query->where('id_producto',$id_producto);
$query->where(\DB::raw("CONCAT(id_producto, ' ', marca, ' ',modelo)"), "LIKE", "%{$id_producto}%");
}
public function scopeFull($query)
{
return $query->where(\DB::raw(0), '<', function ($sql) {
$sql->select(\DB::raw('COUNT(products.id)'))->from('products')->whereRaw('categories.id=products.category_id');
});
}
/**
* Filter data from session_times table.
*
* @var array
*/
public function scopeSearch($query, $search)
{
if (trim($search) != '') {
$query->where(\DB::raw("CONCAT( Name, ' ' , Address)"), 'LIKE', "%{$search}%");
}
}
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');
}
}
