/** * 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; }
public function getPrecioAttribute() { if (Auth::check()) { $lista = Auth::user()->lista; } else { $lista = config('lista_precio_publico'); } $precio = \DB::select("select format(precio,2) as precio from item_precios where lista = ? and codigo = ?", [$lista, $this->codigo]); //dd($lista); return count($precio) == 0 ? '-' : round($precio[0]->precio); }
public static function UserValidate($email, $password) { $valid = false; $sql = 'SELECT u.* , r.role FROM users u JOIN roles r ON r.user_id = u.id WHERE u.email = ?'; if ($user = \DB::select($sql, [$email])) { if (Hash::check($password, $user[0]->password)) { session::set('user_id', $user[0]->id); session::set('user_name', $user[0]->name); session::flash('sm', 'welcome ' . $user[0]->name . '!'); if ($user[0]->role == 1) { session::set('is_admin', true); } $valid = true; } } return $valid; }
public function getVerbsFullyConjugated() { //this query returns a collection object containing verbs_ids for fully conjugated verbs $verbs_full_conj = \DB::select("select verb_id from ((SELECT verb_id,COUNT(*) as count FROM conjugations GROUP BY verb_id ORDER BY count DESC)) as ttl where count = 15"); //this array is used to store just the verb_ids from the above query $vfc_array = []; foreach ($verbs_full_conj as $a) { $vfc_array[$a->verb_id] = $a->verb_id; } //$verbs = $this->orderBy('infinitive_it', 'ASC')->get(); //this has been optimized to only retrieve the desired results instead of all verbs $verbs = $this->whereIn('id', $vfc_array)->orderBy('infinitive_it', 'ASC')->get(); $verbs_for_dropdown = []; foreach ($verbs as $verb) { $verbs_for_dropdown[$verb->id] = $verb->infinitive_it; } return $verbs_for_dropdown; }
/** * スニペットアクティビティを取得 * @param $id */ public static function getSnippetActivity($id) { $query = <<<SQL select DATE_FORMAT(snippets.created_at, '%Y-%c-%e') as date, count(snippets.created_at) as value from users left join snippets on users.id = snippets.user_id where snippets.user_id = {$id} group by DATE_FORMAT(snippets.created_at, '%Y-%c-%e') order by snippets.`created_at` DESC SQL; return \DB::select(\DB::raw($query)); }
public function ownBookClubBook($bookClubId, $bookId) { if (count(\DB::select('select * from book_book_club where book_id = ' . $bookId . ' and book_club_id =' . $bookClubId . ' and owner_id = ' . auth()->user()->id . ';'))) { return true; } return false; }
public function selectQuery($sql_stmt) { return DB::select($sql_stmt); }
/** * Возвращает массив ID каталогов, для которых есть открытые заказы * @return array */ public function getOpenOrdersCatalogsIdsArr() { $open_orders_catalogs_arr = \DB::select('select catalog_id from ' . \App\Models\OrderedProduct::TABLE . ' where `user_id` = ? and `status` = 0 and is_deferred = 0 group by `catalog_id`', [$this->id]); if (empty($open_orders_catalogs_arr)) { return []; } $open_orders_catalogs_ids_arr = []; foreach ($open_orders_catalogs_arr as $open_orders_catalog_mix) { $open_orders_catalogs_ids_arr[] = $open_orders_catalog_mix->catalog_id; } return $open_orders_catalogs_ids_arr; }
public static function getVentasUsuario($id_usuario) { return \DB::select('select ventas.id, ventas.total, ventas.fecha, (select count(*) from productos_venta where productos_venta.id_venta = ventas.id) as articulos from ventas where ventas.id_usuario = :id order by id desc', ['id' => $id_usuario]); }
public static function getSearches() { $sql = "SELECT\n *,\n searches.id AS search_id\n FROM searches\n JOIN status_types\n ON status_types.id = searches.status_id"; return \DB::select($sql); }
/** * Возвращает доступные средства. * Т.е. учитываются заблокированные средства */ public function getCashBalance() { $resource = \DB::select('SELECT (SUM(admission) - SUM(expense) - SUM(blocking)) AS balance FROM payments_transactions WHERE user_id = ?', [$this->id]); return doubleval($resource[0]->balance); }
public function getTallesAttribute() { $talles = \DB::select("select distinct codtalle, codtalle as talle from mallas where coditm = ?", [$this->coditm]); return json_encode($talles); }
public function getFoodSuggestion() { $age = Carbon::Parse($this->bdate)->diffInYears(); $ageRange = AgeRange::where('min_age', '<=', $age)->where('max_age', '>=', $age)->first(); $gender = $this->gender == 1 ? 'F' : 'M'; if ($this->getFoodHistory()->first() == null) { $foodSuggestion = \DB::select(\DB::raw(' SELECT foods.*, SUM(fn.amount_in_food / rem_nutr.remaining_val) / (2000 / foods.calories) as score FROM foods INNER JOIN food_nutrient AS fn ON foods.id = fn.food_id INNER JOIN (SELECT users.id, daily_value AS remaining_val, nutrient_id FROM users INNER JOIN recommended_values WHERE users.id = ' . $this->id . ' AND recommended_values.age_range = ' . $ageRange->id . ' AND recommended_values.sex = \'' . $gender . '\' GROUP BY nutrient_id) AS rem_nutr ON rem_nutr.nutrient_id = fn.nutrient_id and foods.id not in (select food_id from food_restriction as fr inner join restriction_user as ru on ru.restriction_id = fr.restriction_id where user_id = ' . $this->id . ') GROUP BY foods.id order by score DESC, foods.id, fn.nutrient_id;')); } else { $foodSuggestion = \DB::select(\DB::raw(' SELECT foods.*, SUM(fn.amount_in_food / rem_nutr.remaining_val) / (2000 / foods.calories) as score FROM foods INNER JOIN food_nutrient AS fn ON foods.id = fn.food_id INNER JOIN (SELECT users.id, fn.nutrient_id, nutr.daily_value - SUM((quantity * amount_in_food / 100)) AS remaining_val FROM users INNER JOIN user_history AS uh ON users.id = uh.user_id INNER JOIN food_nutrient AS fn ON fn.food_id = uh.food_id INNER JOIN (SELECT nutrient_id, daily_value FROM recommended_values WHERE age_range = ' . $ageRange->id . ' AND sex = \'' . $gender . '\') AS nutr ON nutr.nutrient_id = fn.nutrient_id WHERE timestamp > DATE_SUB(NOW(), INTERVAL 24 HOUR) AND users.id = ' . $this->id . ' GROUP BY nutrient_id) AS rem_nutr ON rem_nutr.nutrient_id = fn.nutrient_id and foods.id not in (select food_id from food_restriction as fr inner join restriction_user as ru on ru.restriction_id = fr.restriction_id where user_id = ' . $this->id . ') GROUP BY foods.id order by score DESC, foods.id, fn.nutrient_id;')); } $random = rand(0, 200); $foodReturn = Food::where('name', $foodSuggestion[$random]->name)->first(); return $foodReturn; }
public static function dbo() { $select = \DB::select(self::table); return $select; }
public static function allIndicatorsOfUser() { $idUser = '******'; // OBTIENE TODOS LOS INDICADORES DEL USUARIO. $userIndicators = \DB::select('select PKG_CMI_CONTROLVIEW.FUNC_GET_USERINDICATOR(?) from dual', [$idUser]); // OBTIENE LOS DATOS PERSONALES DEL USUARIO Y FACULTAD. $employeeNoRelated = \DB::select('select PKG_CMI_EMPLOYEES.FUNC_GET_EMPLOYEENOTRELATED(?) from dual', [$idUser]); $tablero; $i = 0; // BUCLE QUE OBTIENE CADA INDICADOR DEL USUARIOS REGISTRADO. foreach ($userIndicators as $userIndicator) { // CONSULTAS QUE OBTIENEN LOS DATOS DE UN INDICADOR ESPECIFICO. $faculty = \DB::select('select PKG_CMI_FACULTIES.FUNC_GET_FACULTY(?) from dual', [$employeeNoRelated[0]->facultyid]); $lastsState = \DB::select('select PKG_CMI_INDICATOR_STATES.FUNC_GET_LASTSSTATE(?) from dual', [$userIndicator->indicatorid]); $colorValues = \DB::select('select PKG_CMI_COLORMETRIC.FUNC_GET_COLORVALUES(?) from dual', [$userIndicator->colormetricid]); $colometric = \DB::select('select PKG_CMI_COLORMETRIC.FUNC_GET_COLORMETRIC(?) from dual', [$userIndicator->colormetricid]); $averageColor = \DB::select('select PKG_CMI_COLORVALUE.FUNC_GET_COLORVALUE(?) from dual', [$userIndicator->actuallevel]); //Define la colometría para el campo colorimetría de la tabla. /* $averageColor1 = ''; if ( ($userIndicator->actualvalue >= $colorValues[0]->initialvalue) && ($userIndicator->actualvalue <= $colorValues[0]->lastvalue) ) { $averageColor1 = $colorValues[0]->color; }elseif ( ($userIndicator->actualvalue >= $colorValues[1]->initialvalue) && ($userIndicator->actualvalue <= $colorValues[1]->lastvalue) ) { $averageColor1 = $colorValues[1]->color; }elseif ( ($userIndicator->actualvalue >= $colorValues[2]->initialvalue) && ($userIndicator->actualvalue <= $colorValues[2]->lastvalue) ) { $averageColor1 = $colorValues[2]->color; }elseif ( ($userIndicator->actualvalue >= $colorValues[3]->initialvalue) && ($userIndicator->actualvalue <= $colorValues[3]->lastvalue) ) { $averageColor1 = $colorValues[3]->color; }else{ $averageColor1 = 'N/A'; } */ /*-------------------------------------------------- //PRUEBA PARA CADA INDICADOR $tamano=0; if ($userIndicator->indicatorid == 'ind-nueve') { $tamano = count($lastsState); break; } */ //Count consulta el tamaño de lasstate para poder sacarle el promedio y le asigna el valor del score, //sino le asigna N/A para mostrarlo en Ultimas Alimentaciones. $lastsState_0_score = ''; $lastsState_1_score = ''; $lastsState_2_score = ''; $lastsState_3_score = ''; $colometricAverage = 0.0; if (count($lastsState) == 5) { if (!empty($lastsState[0]->score)) { $colometricAverage += $lastsState[0]->score / 4; $lastsState_0_score = $lastsState[0]->score; } else { $colometricAverage += 0; $lastsState_0_score = 'N/A'; } if (!empty($lastsState[1]->score)) { $colometricAverage += $lastsState[1]->score / 4; $lastsState_1_score = $lastsState[1]->score; } else { $colometricAverage += 0; $lastsState_1_score = 'N/A'; } if (!empty($lastsState[2]->score)) { $colometricAverage += $lastsState[2]->score / 4; $lastsState_2_score = $lastsState[2]->score; } else { $colometricAverage += 0; $lastsState_2_score = 'N/A'; } if (!empty($lastsState[3]->score)) { $colometricAverage += $lastsState[3]->score / 4; $lastsState_3_score = $lastsState[3]->score; } else { $colometricAverage += 0; $lastsState_3_score = 'N/A'; } } elseif (count($lastsState) == 4) { if (!empty($lastsState[0]->score)) { $colometricAverage += $lastsState[0]->score / 3; $lastsState_0_score = $lastsState[0]->score; } else { $colometricAverage += 0; $lastsState_0_score = 'N/A'; } if (!empty($lastsState[1]->score)) { $colometricAverage += $lastsState[1]->score / 3; $lastsState_1_score = $lastsState[1]->score; } else { $colometricAverage += 0; $lastsState_1_score = 'N/A'; } if (!empty($lastsState[2]->score)) { $colometricAverage += $lastsState[2]->score / 3; $lastsState_2_score = $lastsState[2]->score; } else { $colometricAverage += 0; $lastsState_2_score = 'N/A'; } $lastsState_3_score = 'N/A'; } elseif (count($lastsState) == 3) { if (!empty($lastsState[0]->score)) { $colometricAverage += $lastsState[0]->score / 2; $lastsState_0_score = $lastsState[0]->score; } else { $colometricAverage += 0; $lastsState_0_score = 'N/A'; } if (!empty($lastsState[1]->score)) { $colometricAverage += $lastsState[1]->score / 2; $lastsState_1_score = $lastsState[1]->score; } else { $colometricAverage += 0; $lastsState_1_score = 'N/A'; } $lastsState_2_score = 'N/A'; $lastsState_3_score = 'N/A'; } elseif (count($lastsState) == 2) { if (!empty($lastsState[0]->score)) { $colometricAverage += $lastsState[0]->score / 1; $lastsState_0_score = $lastsState[0]->score; } else { $colometricAverage += 0; $lastsState_0_score = 'N/A'; } $lastsState_1_score = 'N/A'; $lastsState_2_score = 'N/A'; $lastsState_3_score = 'N/A'; } elseif (count($lastsState) == 1 || count($lastsState) == 0) { $lastsState_0_score = 'N/A'; $lastsState_1_score = 'N/A'; $lastsState_2_score = 'N/A'; $lastsState_3_score = 'N/A'; } //Toma el promedio final de últimas alimentaciones y los compara con el //valor-inicial y último-valor para asignarle la colorimetría. $averageColor2; if ($colometricAverage >= $colorValues[0]->initialvalue && $colometricAverage <= $colorValues[0]->lastvalue) { $averageColor2 = $colorValues[0]->color; } elseif ($colometricAverage >= $colorValues[1]->initialvalue && $colometricAverage <= $colorValues[1]->lastvalue) { $averageColor2 = $colorValues[1]->color; } elseif ($colometricAverage >= $colorValues[2]->initialvalue && $colometricAverage <= $colorValues[2]->lastvalue) { $averageColor2 = $colorValues[2]->color; } elseif ($colometricAverage >= $colorValues[3]->initialvalue && $colometricAverage <= $colorValues[3]->lastvalue) { $averageColor2 = $colorValues[3]->color; } elseif ($colometricAverage = 0) { $averageColor2 = 'N/A'; } //Frecuencia de alimentación de los indicadores. //Convierte la actualización en días, meses y años. $frecuenciaAlimentacion = ''; if ($userIndicator->updatefrq == 1) { $frecuenciaAlimentacion = 'Diaria'; } elseif ($userIndicator->updatefrq == 7) { $frecuenciaAlimentacion = 'Semanal'; } elseif ($userIndicator->updatefrq == 15) { $frecuenciaAlimentacion = 'Quincenal'; } elseif ($userIndicator->updatefrq == 30) { $frecuenciaAlimentacion = 'Mensual'; } elseif ($userIndicator->updatefrq == 60) { $frecuenciaAlimentacion = 'Bimensual'; } elseif ($userIndicator->updatefrq == 90) { $frecuenciaAlimentacion = 'Trimestral'; } elseif ($userIndicator->updatefrq == 120) { $frecuenciaAlimentacion = 'Cuatrimestral'; } elseif ($userIndicator->updatefrq == 180) { $frecuenciaAlimentacion = 'Semestral'; } elseif ($userIndicator->updatefrq == 365) { $frecuenciaAlimentacion = 'Anual'; } //Arreglo de indicadores para la el tablero de control $tablero[$i++] = ['employeeID' => $employeeNoRelated[0]->employeeid, 'firstName' => $employeeNoRelated[0]->firstname, 'lastName' => $employeeNoRelated[0]->lastname, 'employeeActive' => $employeeNoRelated[0]->active, 'indicatorid' => $userIndicator->indicatorid, 'indicatorname' => $userIndicator->indicatorname, 'categoryname' => $userIndicator->categoryname, 'updatefrq' => $frecuenciaAlimentacion, 'score' => $userIndicator->score, 'actualvalue' => $userIndicator->actualvalue, 'colometric' => $colometric[0]->metricname, 'desirevalue' => $userIndicator->desirevalue, 'averageColor1' => $averageColor[0]->color, 'lastsState_0_score' => round($lastsState_0_score, 2), 'lastsState_1_score' => round($lastsState_1_score, 2), 'lastsState_2_score' => round($lastsState_2_score, 2), 'lastsState_3_score' => round($lastsState_3_score, 2), 'colometricAverage' => round($colometricAverage, 2), 'averageColor2' => $averageColor2]; } return $tablero; }