public function leerTodos()
{
$query = new \yii\db\Query();
//$fecha_actual = date('Y-m-d');
$query->select('*')->from('usuarios');
$rows = $query->all(Usuarios::getDb());
return $rows;
}
public function getCategories($parent_id = 0)
{
$query = new \yii\db\Query();
$query->select('*')->from('category');
$query->join('LEFT JOIN', 'category_description', 'category_description.category_id = category.category_id AND category_description.language_id = ' . (int) \Yii::$app->params['config_language_id']);
$query->join('LEFT JOIN', 'category_to_store', 'category_to_store.category_id = category.category_id AND category_to_store.store_id = ' . (int) \Yii::$app->params['store_id']);
$query->where(['=', 'category.parent_id', $parent_id]);
$query->orderBy(['category.sort_order' => SORT_ASC]);
return $query->all();
}
public function up()
{
$query = new \yii\db\Query();
$query->select('notification.id')->from('notification');
$query->andWhere(['or', ['class' => 'humhub\\modules\\space\\notifications\\Invite'], ['class' => 'humhub\\modules\\space\\notifications\\InviteAccepted'], ['class' => 'humhub\\modules\\space\\notifications\\InviteDeclined'], ['class' => 'humhub\\modules\\space\\notifications\\ApprovalRequest'], ['class' => 'humhub\\modules\\space\\notifications\\ApprovalRequestAccepted'], ['class' => 'humhub\\modules\\space\\notifications\\ApprovalRequestDeclined']]);
$query->leftJoin('user', 'notification.originator_user_id=user.id');
$query->andWhere('user.id IS NULL');
foreach ($query->all() as $notification) {
$this->delete('notification', ['id' => $notification['id']]);
}
}
public function getAllBanner($banner_id, $limit)
{
$query = new \yii\db\Query();
$query->select('*')->from('banner_image');
$query->join('LEFT JOIN', 'banner_image_description', 'banner_image_description.banner_image_id = banner_image.banner_image_id');
$query->where(['=', 'banner_image.banner_id', $banner_id]);
$query->orderBy(['banner_image.sort_order' => SORT_ASC]);
$query->limit($limit);
$query->offset(0);
return $query->all();
}
public function actionView($menuId)
{
$query = new \yii\db\Query();
$query->select(['id', 'tabName', 'reName'])->from('query_table');
$query->where(['menuId' => $menuId]);
$tabs = $query->all();
$items = [];
foreach ($tabs as $key => $value) {
$reName = '(noReName)';
if ($value['reName']) {
$reName = '(' . $value['reName'] . ')';
}
$items[$value['id']] = $value['tabName'] . $reName;
}
return $this->render('view', ['menuId' => $menuId, 'items' => $items]);
}
public function getInstituicoesIdsAndNomes()
{
$query = new \yii\db\Query();
// compose the query
$query->select('idinstituicoes, nomeinstituicao')->from('instituicoes');
// build and execute the query
$instituicoes = $query->all();
$instituicoesNome = [];
$instituicoesId = [];
foreach ($instituicoes as $variable) {
$instituicoesNome += [$variable['idinstituicoes'] => $variable['nomeinstituicao']];
// $instituicoesNome[] = $variable['nomeInstituicao'];
// $instituicoesId[] = $variable['idInstituicoes'];
}
// $instituicoes = ['ids'=> $instituicoesId ,'nomes' =>$instituicoesNome] ;
return $instituicoesNome;
}
public function getAllBestSellerProducts($limit)
{
$time = new \DateTime('now');
$today = $time->format('Y-m-d');
$query = new \yii\db\Query();
$query->select('order_product.product_id , SUM(order_product.quantity) AS total')->from('order_product');
$query->join('LEFT JOIN', 'order', 'order_product.order_id = order.order_id');
$query->join('LEFT JOIN', 'product', 'order_product.product_id = product.product_id');
$query->join('LEFT JOIN', 'product_to_store', 'product.product_id = product_to_store.product_id');
$query->where(['>', 'order.order_status_id', 0]);
$query->andWhere(['=', 'product.status', 1]);
$query->andWhere(['<=', 'product.date_available', $today]);
$query->andWhere(['=', 'product_to_store.store_id', (int) \Yii::$app->params['store_id']]);
$query->groupBy('order_product.product_id');
$query->orderBy(['total' => SORT_DESC]);
$query->limit($limit);
$query->offset(0);
return $query->all();
}
public function buscarPdfProducto($tipo, $fecha)
{
$query = new \yii\db\Query();
$query->select('Boletin')->from('boletines')->where('Tipo LIKE :tipo', array(':tipo' => $tipo))->andWhere('Fecha = :fecha', array(':fecha' => $fecha))->orderBy('Fecha DESC')->limit(1);
$rows = $query->all(Boletines::getDb());
return $rows;
}
protected function _getSystemMess($user_id, $only_new = false)
{
$messages = new \yii\db\Query();
$messages->select(['usr.id', "usr.{$this->attributeNameUser}", 'usr.email', 'm.message', 'FROM_UNIXTIME(m.created_at, "%d-%m-%Y %H:%i") as created_at'])->from("{$this->userTableName} as usr")->leftJoin('messages as m', 'usr.id = m.whom_id')->where(['m.from_id' => null, 'usr.id' => $user_id]);
if ($only_new) {
$messages->andWhere(['m.status' => 1]);
}
return $messages->all();
}
public static function getParents($table, $id, $fields = null)
{
$idField = ArrayHelper::getValue($fields, 0, 'id');
$parentField = ArrayHelper::getValue($fields, 1, 'parent_id');
$nameField = ArrayHelper::getValue($fields, 2, 'name');
$query = new yii\db\Query();
$query->select("t2.{$idField}, t2.{$parentField}, t2.{$nameField}, tlink.level")->from($table . ' t1, ' . self::tableName() . ' tlink, ' . $table . ' t2')->where('
t1.' . $idField . ' = tlink.id
AND tlink.parent_id = t2.' . $idField . '
AND t1.' . $idField . ' = :id
AND tlink.table = :table
')->params([':id' => $id, ':table' => $table]);
return $query->all();
}
public function productosSupermercadoOrigen($fechaini, $fechafin, $condicion)
{
$query = new \yii\db\Query();
$date = new \DateTime($fechaini);
$fechaini = $date->format('Y-d-m H:i:s');
$date = new \DateTime($fechafin);
$fechafin = $date->format('Y-d-m H:i:s');
$query->select(['Producto.[producto],cod_producto,Origen.[origen],cod_origen,AVG(precio) as precio'])->from('Datos_Supermercados')->innerJoin('Origen', 'Origen.codigo_origen = Datos_Supermercados.cod_origen')->innerJoin('Localizacion', 'Localizacion.codigo_localizacion = Datos_Supermercados.cod_localizacion')->innerJoin('Producto', 'Producto.codigo_producto = Datos_Supermercados.cod_producto')->where("fecha <= '" . $fechaini . "'")->andWhere("fecha >= '" . $fechafin . "'")->andWhere($condicion)->orderBy('producto')->groupBy('producto,cod_producto,origen,cod_origen');
$rows = $query->all(DatosSupermercados::getDb());
return $rows;
}
public function consultarPrecios($fecha)
{
$query = new \yii\db\Query();
$query->select('Producto,Fecha,Tipo,AVG(Pond_Suma) as Pond_Suma')->from('alhondigas')->where('Tipo=:tipo and Fecha=:fecha', array(':tipo' => 'Precios', ':fecha' => $fecha))->groupBy('Producto');
$rows = $query->all(AlhondigasPreciosPonderados::getDb());
return $rows;
}
/**
*
*/
public function tagData()
{
$query = new \yii\db\Query();
$query->select('Id,name')->from('tc_tags')->where('issystem=0')->limit(10);
$rows = $query->all();
//$tag = new Tags;
//$tagData = $tag->find()->asArray()->all();
foreach ($rows as $values) {
$tagArray[] = $values['name'];
}
return $tagArray;
}
public function actionUser()
{
echo 'Start export.' . PHP_EOL;
// Наше PDO подключение к БД
$db = Yii::$app->db;
// Размер одной части обрабатываемых данных
$part_size = 1000;
$fields = ['user.id' => 'ID', 'profile.name' => 'Name', 'profile.last_name' => 'Last Name', 'user.social_id' => 'Social ID', 'created_at' => 'Created at'];
// Файл, в который будем записывать результат (в корне сайта)
$fname = Yii::$app->basePath . '/export/user.csv';
$f = @fopen($fname, 'w');
// Записываем в начало файла заголовок для sitemap-файла
$csvHeader = '"' . implode('";"', $fields) . '"';
fwrite($f, $csvHeader . PHP_EOL);
$query = new \yii\db\Query();
// Команда, которая будет делать порционную выборку новостей
$query->select(implode(',', array_keys($fields)));
$query->from('user, profile');
$query->andWhere('user.id = profile.user_id');
//$query->join('LEFT JOIN', 'profile', 'user.id = profile.user_id');
// Определяем количество данных, которое нам нужно обработать
$all_count = (int) $db->createCommand("SELECT COUNT(id) FROM user")->queryScalar();
// Устанавливаем лимит, сколько новостей надо выбрать из таблицы
$query->limit($part_size);
// Перебираем все части данных
for ($i = 0; $i < ceil($all_count / $part_size); $i++) {
// Сюда будем складывать порции данных, для записи в файл, каждый
// элемент массива - это одна строка
$part = array();
// Вычисляем отступ от уже обработанных данных
$offset = $i * $part_size;
// Устанавливам отступ
$query->offset($offset);
// Находим очередную часть данных
$rows = $query->all();
// Перебираем найденные данные
foreach ($rows as $row) {
$row['created_at'] = date('m-d-Y', $row['created_at']);
// Открываем тег <url> - начало описания элемента в sitemap-файле
$part[] = '"' . implode('";"', $row) . '"';
}
// Убираем из памяти найденную часть данных
unset($rows);
// Добавляем в наш файл обработанную часть данных
if (count($part)) {
// Здесь мы объединяем все элементы массива $xml в строки
fwrite($f, implode(PHP_EOL, $part) . PHP_EOL);
}
unset($part);
}
// Заканчиваем работу с файлом
fclose($f);
echo 'Done.' . PHP_EOL;
}
public function findAllUsersInfo($fields, $filter = null, $order_by = null)
{
$query = new \yii\db\Query();
$query->select($fields)->from('user')->innerJoin('personal_information', 'personal_information.pi_id = user.pi_id')->innerJoin('user_education', 'user_education.id = user.pi_id')->innerJoin('education_information', 'education_information.ei_id = user_education.ei_id')->innerJoin('course', 'course.course_id = education_information.course_id')->innerJoin('institution_course', 'institution_course.course_id = course.course_id')->innerJoin('institution', 'institution.inst_id = institution_course.inst_id');
if ($filter != null) {
$fields = array();
foreach ($filter as $key => $value) {
$fields[$key] = $value;
}
$query->where($fields);
}
if ($order_by != null) {
$query->orderBy($order_by);
}
return $query->all();
}
public function actionPublic($id)
{
$query = new \yii\db\Query();
$query->select(['payment_informations.payment_first_name', 'payment_informations.payment_last_name', 'payment_informations.payment_biography', 'payment_informations.payment_image', 'payment_informations.payment_bussines_name', 'payment_informations.payment_active_lead', 'payment_informations.payment_clientele', 'payment_informations.payment_certifications', 'payment_informations.payment_style', 'payment_informations.payment_strength', 'payment_informations.payment_fitness', 'payment_informations.payment_height', 'payment_informations.payment_weight', 'payment_informations.payment_gender', 'payment_informations.payment_video_url', 'payment_informations.payment_video_title', 'payment_informations.created_at', 'payment_informations.payment_user_status', 'users.user_type'])->from('payment_informations')->leftJoin('users', 'payment_informations.payment_user_id = users.user_id')->where(['users.user_id' => $id, 'user_status' => 'active'])->orWhere(['users.user_id' => $id, 'user_status' => 're-approve']);
return ApiHelper::successResponse($query->all());
}
public function productosOrigenFecha($fechaini, $fechafin, $condicion)
{
$query = new \yii\db\Query();
$date = new \DateTime($fechaini);
$fechaini = $date->format('Y-d-m H:i:s');
$date = new \DateTime($fechafin);
$fechafin = $date->format('Y-d-m H:i:s');
$query->select(['fecha'])->from('Datos_origen')->innerJoin('Origen', 'Origen.codigo_origen = Datos_origen.cod_origen')->innerJoin('Localizacion', 'Localizacion.codigo_localizacion = Datos_origen.cod_localizacion')->innerJoin('Producto', 'Producto.codigo_producto = Datos_origen.cod_producto')->where("fecha <= '" . $fechaini . "'")->andWhere("fecha >= '" . $fechafin . "'")->andWhere('cod_localizacion=1 and cod_origen = 17')->andWhere($condicion)->orderBy('fecha DESC');
$rows = $query->all(DatosOrigen::getDb());
return $rows;
}