public static function buscarAplicativoRelacionados2($idTicket_persona)
{
$result = \DB::table('aplicativo_ticket_persona')->select(['aplicativo.nombre_aplicativo', 'aplicativo.id', 'aplicativo_ticket_persona.created_at', 'aplicativo_ticket_persona.usucrea'])->whereIn('aplicativo_ticket_persona.ticket_persona_id', function ($query) use($idTicket_persona) {
$query->select(['persona_tickets.id'])->from('persona_tickets')->where('persona_tickets.id', $idTicket_persona)->get();
})->join('aplicativo', 'aplicativo_ticket_persona.aplicativo_id', '=', 'aplicativo.id')->get();
return $result;
}
public static function notify($idArr = array(), $body, $type, $to_all = 0, $is_system = 0)
{
$currentId = auth()->id();
if (!$currentId) {
return;
}
$data = $notifiedUidArr = [];
$now = \Carbon\Carbon::now();
if ($to_all) {
$data = ['user_id' => 0, 'body' => $body, 'type' => $type, 'to_all' => $to_all, 'is_system' => $is_system, 'created_at' => $now, 'updated_at' => $now];
} elseif (!empty($idArr)) {
$idArr = array_unique($idArr);
foreach ($idArr as $id) {
if ($id == $currentId) {
return;
}
$data[] = ['user_id' => $id, 'body' => $body, 'type' => $type, 'to_all' => $to_all, 'is_system' => $is_system, 'created_at' => $now, 'updated_at' => $now];
$notifiedUidArr[] = $id;
}
}
if (!empty($data)) {
Notify::insert($data);
if ($to_all) {
\DB::table('users')->increment('notice_count');
} elseif ($notifiedUidArr) {
User::whereIn('id', $notifiedUidArr)->increment('notice_count');
}
}
}
/**
* [getMatches description]
* @param [type] $tournament_id [description]
* @param [type] $player_id [description]
* @return [type] [description]
*/
public function getMatches($tournament_id, $player_id)
{
$m = \DB::table('matches')->join('tournaments', 'tournaments.tournament_id', '=', 'matches.tournament_id')->join('players as winner', 'winner.player_id', '=', 'matches.player1_id')->join('players as loser', 'loser.player_id', '=', 'matches.player2_id')->where('matches.tournament_id', '=', $tournament_id)->where(function ($q) use($player_id) {
$q->where('player1_id', '=', $player_id)->orWhere('player2_id', '=', $player_id);
})->orderBy('match_division')->orderBy('round')->select('*', 'winner.first_name as winner_first_name', 'winner.last_name as winner_last_name', 'loser.first_name as loser_first_name', 'loser.last_name as loser_last_name', 'loser.player_id as loser_id', 'tournaments.name as tournament')->distinct()->get();
return $m;
}
/**
* Inserts data into database, tables customers, occupancies, payments, and updates rooms
* @param array $array_fields fields from booking form
*/
public static function Insert($array_fields)
{
if (!false) {
$conn = DB::GetConnection();
$stmt = $conn->prepare("INSERT INTO customers (customer_id, customer_name, customer_lastname, customer_phone, customer_notes) VALUES (?, ?, ?, ?, ?) ");
$stmt->bindParam(1, $array_fields['id'], \PDO::PARAM_INT);
$stmt->bindParam(2, $array_fields['firstname'], \PDO::PARAM_STR);
$stmt->bindParam(3, $array_fields['lastname'], \PDO::PARAM_STR);
$stmt->bindParam(4, $array_fields['phone'], \PDO::PARAM_STR);
$stmt->bindParam(5, $array_fields['notes'], \PDO::PARAM_STR);
$stmt->execute();
$insertkey = $conn->lastInsertId();
$stmt = $conn->prepare("INSERT INTO occupancies (occupancy_id, occupancy_customer_id, occupancy_room_id, occupancy_firstdate, occupancy_lastdate) VALUES (?, ?, ?, ?, ?) ");
$stmt->bindParam(1, $array_fields['id'], \PDO::PARAM_INT);
$stmt->bindParam(2, $insertkey, \PDO::PARAM_INT);
$stmt->bindParam(3, $array_fields['room_number'], \PDO::PARAM_INT);
$stmt->bindParam(4, $array_fields['check_in'], \PDO::PARAM_STR);
$stmt->bindParam(5, $array_fields['check_out'], \PDO::PARAM_STR);
$stmt->execute();
$payment_id = null;
$payment_status_id = 1;
$payment_amount = 20;
$stmt = $conn->prepare("INSERT INTO payments (payment_id, payment_customer, payment_status_id, payment_amount, payment_date) VALUES (?, ?, ?, ?, ? )");
$stmt->bindParam(1, $payment_id);
$stmt->bindParam(2, $insertkey, \PDO::PARAM_INT);
$stmt->bindParam(3, $payment_status_id);
$stmt->bindParam(4, $payment_amount);
$stmt->bindParam(5, $array_fields['check_out'], \PDO::PARAM_STR);
$stmt->execute();
$stmt = $conn->prepare("UPDATE rooms SET room_status_id = '1' WHERE room_id = ?");
$stmt->bindParam(1, $array_fields['room_number'], \PDO::PARAM_INT);
$stmt->execute();
}
}
public function getFeira($id)
{
$dados = \DB::connection('mysql')->select(\DB::raw("SELECT *, date_format(data, '%d-%m-%Y %H:%i') as dataBR FROM feira WHERE id={$id}"))[0];
// $function = new Functions();
// $dados->data = $function->convertDataToBR("2016-01-02");
return $dados;
}
public function closest($lat, $lng, $max_distance = 25, $max_locations = 10, $units = 'miles', $fields = false)
{
/*
* Allow for changing of units of measurement
*/
switch ($units) {
case 'miles':
//radius of the great circle in miles
$gr_circle_radius = 3959;
break;
case 'kilometers':
//radius of the great circle in kilometers
$gr_circle_radius = 6371;
break;
}
/*
* Support the selection of certain fields
*/
if (!$fields) {
$fields = array('*');
}
/*
* Generate the select field for disctance
*/
$disctance_select = sprintf("( %d * acos( cos( radians(%s) ) " . " * cos( radians( lat ) ) " . " * cos( radians( lng ) - radians(%s) ) " . " + sin( radians(%s) ) * sin( radians( lat ) ) " . ") " . ") " . "AS distance", $gr_circle_radius, $lat, $lng, $lat);
return DB::table($table)->having('distance', '<', $max_distance)->take($max_locations)->order_by('distance', 'ASC')->get(array($fields, $disctance_select));
}
public static function boot()
{
parent::boot();
static::deleting(function ($category) {
DB::statement('DELETE FROM category_equipment WHERE category_id = ?', array($category->id));
});
}
public function 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 static function cuentasAplicativosExportar($ticket_id)
{
$resultado = \DB::table('cuenta')->select(['cuenta.id', 'cuenta.cuenta_usu', 'ticket.nroticket', 'aplicativo.nombre_aplicativo', 'cuenta.created_at', 'cuenta.clave'])->whereIn('cuenta.aplicativo_ticket_id', function ($query) use($ticket_id) {
$query->select(['aplicativo_tickets.id'])->from('ticket')->where('ticket.id', $ticket_id)->join('aplicativo_tickets', 'ticket.id', '=', 'aplicativo_tickets.ticket_id')->join('aplicativo', 'aplicativo_tickets.aplicativo_id', '=', 'aplicativo.id')->get();
})->join('aplicativo_tickets', 'cuenta.aplicativo_ticket_id', '=', 'aplicativo_tickets.id')->join('aplicativo', 'aplicativo_tickets.aplicativo_id', '=', 'aplicativo.id')->join('ticket', 'aplicativo_tickets.ticket_id', '=', 'ticket.id')->get();
return $resultado;
}
public static function AplicativoAreaFaltante($idarea)
{
$resultado = \DB::table('aplicativo')->select(['aplicativo.id', 'aplicativo.nombre_aplicativo'])->whereNotIn('aplicativo.id', function ($query) use($idarea) {
$query->select(['aplicativo_area.aplicativo_id'])->from('aplicativo_area')->where('area.id', $idarea)->join('area', 'aplicativo_area.area_id', '=', 'area.id')->get();
})->get();
return $resultado;
}
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 scopecontrol($query, $control)
{
if (trim($control) != "") {
$query->where(\DB::raw("CONCAT(ctl_id)"), "ILIKE", "%{$control}%");
//$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 select()
{
$user = DB::table('login')->where('name', 'crdf')->first();
var_dump($user);
die;
return $user->name;
}
public function scoperegistro($query, $registro)
{
if (trim($registro) != "") {
$query->where(\DB::raw("CONCAT(veh_movil)"), "ILIKE", "%{$registro}%");
//$query->where('full_name',"LIKE", "%$name%");
}
}
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 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 user()
{
//return $this->hasManyThrough('App\User','App\AccountLink',
// 'user_id', 'id', 'app_user_id');
$user = \DB::table('users')->join('account_links', 'users.id', '=', 'account_links.user_id')->where('account_links.app_user_id', '=', $this->id)->first();
return $user;
}
public function scopeEfficiency($query, $efficiency)
{
if (trim($efficiency) != "") {
$query->where(\DB::raw("CONCAT(efficiency)"), "LIKE", "%{$efficiency}%");
Session::flash('message', 'Rendimiento:' . ' ' . $efficiency . ' ' . 'Resultado de la busqueda');
}
}
public function scopeAn8($query, $an8)
{
if (trim($an8) != "") {
$query->where(\DB::raw("CONCAT(emp_nombre,' ',emp_apellido,emp_an8,emp_identificacion)"), "ILIKE", "%{$an8}%");
//$query->where('full_name',"LIKE", "%$name%");
}
}
public static function AplicativoFaltantedelTicket($idPersona)
{
$resultado = \DB::table('aplicativo')->select(['aplicativo.id', 'aplicativo.nombre_aplicativo'])->whereNotIn('aplicativo.id', function ($query) use($idPersona) {
$query->select(['aplicativo_ticket_persona.aplicativo_id'])->from('persona_tickets')->where('persona_tickets.persona_id', $idPersona)->join('aplicativo_ticket_persona', 'persona_tickets.id', '=', 'aplicativo_ticket_persona.ticket_persona_id')->get();
})->get();
return $resultado;
}
public function scopePhase($query, $phase)
{
if (trim($phase) != "") {
$query->where(\DB::raw("CONCAT(phase)"), "LIKE", "%{$phase}%");
Session::flash('message', 'Fase:' . ' ' . $phase . ' ' . 'Resultado de la busqueda');
}
}
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 判断括号内语句是否为真 为真则搜索 为假则放弃
}
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;
}
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%");
}
}
public function newQuery($excludeDeleted = true)
{
$raw = '';
foreach ($this->geofields as $column) {
$raw .= ' astext(' . $column . ') as ' . $column . ' ';
}
return parent::newQuery($excludeDeleted)->addSelect('*', DB::raw($raw));
}
/**
* @param integer $n
* @return array
*/
public static function findLastN($n)
{
/** @var DB $db */
$db = DB::instance();
$sql = sprintf('SELECT * FROM ' . self::TABLE . ' ORDER BY id DESC LIMIT %d', $n);
$res = $db->query($sql, self::class);
return $res;
}
public function minId()
{
$vacancy = DB::table($this->table)->select('min(id)')->first();
if ($vacancy) {
return $vacancy->min;
} else {
return null;
}
}
public function getPassedUserIds()
{
$arIds = \DB::table('vk_like')->select('to_id')->where(['from_id' => $this->id])->get();
$arIds = array_map(function ($item) {
return $item->to_id;
}, $arIds);
$arIds[] = $this->id;
return $arIds;
}
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');
}
}
