/**
* 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;
}
