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 scoperegistro($query, $registro)
 {
     if (trim($registro) != "") {
         $query->where(\DB::raw("CONCAT(veh_movil)"), "ILIKE", "%{$registro}%");
         //$query->where('full_name',"LIKE", "%$name%");
     }
 }

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

 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 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 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 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 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 scopePhase($query, $phase)
 {
     if (trim($phase) != "") {
         $query->where(\DB::raw("CONCAT(phase)"), "LIKE", "%{$phase}%");
         Session::flash('message', 'Fase:' . ' ' . $phase . '  ' . '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 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;
 }