public function loadProjectKeywords($projectId)
{
$sql = 'SELECT * FROM keywords WHERE u_p_c_id = ? AND type = 2';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($projectId);
return $this->getList($sqlQuery);
}
public function buscar($nombre)
{
$sql = 'SELECT * FROM apuntes u WHERE titulo like ? and b_borrado=0';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setString('%' . $nombre . '%');
return $this->getList($sqlQuery);
}
public function queryByConfiguracionIdMontoAsc($value)
{
$sql = 'SELECT * FROM reglas_cupones WHERE configuracion_id = ? order by monto ASC';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($value);
return $this->getList($sqlQuery);
}
public function queryByDistribuidorId($value)
{
$sql = 'SELECT * FROM condicion WHERE usuario_id = ? OR usuario_id IS NULL order by condicion_categoria_id';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($value);
return $this->getList($sqlQuery);
}
protected function _supplierCollection($key)
{
$sqlQuery = new SqlQuery();
$sql = "SELECT id, name" . $this->table . "FROM supplier`" . $this->index . "`WHERE" . $key . "\n\n LEFT JOIN ( SELECT id_ingredient, id_supplier, MIN(price) FROM has_supplier\n\n IF (id=id_supplier){min(price)}";
$result = $sqlQuery->execute($sql);
return $result['supplier'];
}
/**
* Get all records from table
*/
public function getResponses($challengeId)
{
$sql = "SELECT response.*, user.first_name, user.last_name, user.username \r\n\t\t\t\t\tFROM challenge_responses as response JOIN user_info as user\r\n\t\t\t\t\t\tWHERE response.challenge_id = ? AND response.user_id = user.id ORDER BY response.creation_time ASC ;";
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($challengeId);
return $this->getListReponse($sqlQuery);
}
public function load($indexList = null, $column = false)
{
$sqlQuery = new SqlQuery();
$column = $column ? $column : $this->index;
if (isset($indexList)) {
$indexList = " WHERE " . $column . " IN (" . implode(',', $indexList) . ")" . implode(' AND ', $this->filters);
} else {
$indexList = array_reduce($this->filters, function ($prev, $next) {
return $prev == "" ? " WHERE " . $next : $prev . " AND " . $next;
}, "");
}
if ($this->order) {
$indexList .= ' ORDER BY `' . $this->orderColumn . '` ' . $this->orderDir;
}
if ($this->limit > -1) {
$indexList .= ' LIMIT ' . $this->limit;
}
$sql = "SELECT * FROM " . $this->table . $indexList;
$result = $sqlQuery->execute($sql);
if ($result['status']) {
foreach ($result['data'] as $item) {
$this->processItem($item);
}
}
}
public function getUserJobPreferredJobLocations($userId)
{
$sql = "SELECT * FROM working_locations as locations JOIN user_locations as user_location \r\n\t\t\t\t\tWHERE locations.id = user_location.location_id \r\n\t\t\t\t\t\tAND user_location.user_id = ?";
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($userId);
return $this->getList($sqlQuery);
}
/**
* Get all records from table
*/
public function queryAllConversation($projectId)
{
$sql = 'SELECT * FROM project_responses WHERE project_id = ? AND status = 5';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($projectId);
return $this->getList($sqlQuery);
}
public function buscar($nombre)
{
$sql = 'SELECT * FROM universidades u WHERE nombre like ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setString('%' . $nombre . '%');
return $this->getList($sqlQuery);
}
public function updateStatus($id)
{
$sql = 'UPDATE user_access_aid SET status=1 WHERE id = ?;';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($id);
return $this->executeUpdate($sqlQuery);
}
public function deleteReminder($id)
{
$sql = 'UPDATE reminders SET status = 2 WHERE id = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($id);
return $this->executeUpdate($sqlQuery);
}
function queryInFechas($inicio, $fin, $hotelId)
{
$sql = "SELECT DISTINCT * FROM hotel_fechas\n WHERE hotel_id = ? AND UNIX_TIMESTAMP(fecha) BETWEEN UNIX_TIMESTAMP('{$inicio}') \n AND UNIX_TIMESTAMP('{$fin}')";
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($hotelId);
return $this->getList($sqlQuery);
}
public function deleteByFormIdUserId($userId, $formId)
{
$sql = 'DELETE FROM teamroom_push_forms.user_forms WHERE user_id = ? AND form_id = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($userId);
$sqlQuery->setNumber($formId);
return $this->executeUpdate($sqlQuery);
}
public function baja($id)
{
$sql = 'UPDATE usuarios SET b_borrado = ? WHERE id_usuario = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber("1");
$sqlQuery->setNumber($id);
return $this->executeUpdate($sqlQuery);
}
public function updateLastLoginTime($userId, $timestamp)
{
$sql = 'UPDATE user_info SET last_login_time = ? WHERE id = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($timestamp);
$sqlQuery->set($userId);
return $this->executeUpdate($sqlQuery);
}
/**
* Delete record from table
* @param skill primary key
*/
public function deleteUserSkill($id, $userId)
{
$sql = 'DELETE FROM user_skills WHERE id = ? AND user_id = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($id);
$sqlQuery->setNumber($userId);
return $this->executeUpdate($sqlQuery);
}
public function getUserJobPreference($userId)
{
$sql = "SELECT * FROM job_preference WHERE user_id = ? ORDER BY last_update_on DESC;";
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($userId);
$jobPreference = $this->getList($sqlQuery);
return $jobPreference;
}
public function getPushForm($userId)
{
$sql = 'SELECT form.* FROM teamroom_push_forms.forms as form JOIN teamroom_push_forms.user_forms as user_push_form
WHERE user_push_form.user_id = ? AND form.id = user_push_form.form_id AND user_push_form.status = 0';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($userId);
return $this->getRow($sqlQuery);
}
/**
* Update record in table
*
* @param UserSocialLinksMySql userSocialLink
*/
public function updateSocialLink($userId, $linkUrl, $type)
{
$sql = 'UPDATE user_social_links SET link_url = ? WHERE user_id = ? AND type = ? ';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($linkUrl);
$sqlQuery->setNumber($userId);
$sqlQuery->set($type);
return $this->executeUpdate($sqlQuery);
}
public function queryByHotelIdAndFechas($idHotel, $inicio, $fin)
{
$sql = 'SELECT * FROM promocion WHERE hotel_id = ?';
$sql .= ' AND ' . $inicio . ' BETWEEN UNIX_TIMESTAMP(promocion.inicio) AND UNIX_TIMESTAMP(promocion.fin)';
$sql .= ' AND ' . $fin . ' BETWEEN UNIX_TIMESTAMP(promocion.inicio) AND UNIX_TIMESTAMP(promocion.fin)';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($idHotel);
return $this->getList($sqlQuery);
}
function queryByEmpresaUsuarioId($id)
{
$sql = 'SELECT distinct e.* FROM empresa AS e
INNER JOIN usuario_empresa AS ue
ON ue.empresa_id = e.id
WHERE ue.usuario_id = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($id);
return $this->getList($sqlQuery);
}
public function getNotificationNotLogin24Hours()
{
$sql = 'SELECT notice.*, user.first_name, user.last_name, user.email, TIMESTAMPDIFF( HOUR , user.last_login_time, now( ) ) AS login_before FROM notifications AS notice JOIN user_info AS user
WHERE notice.user_id = user.id
AND TIMESTAMPDIFF( HOUR , user.last_login_time, now( ) ) >24
ORDER BY login_before';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($userId);
return $this->getList($sqlQuery);
}
public function getLastTemporadaOfSerie($idSerie)
{
$sql = 'SELECT MAX(temporada) temporada FROM vod WHERE id_serie = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($idSerie);
$tab = QueryExecutor::execute($sqlQuery);
if (count($tab) == 0) {
return null;
}
return $tab[0]['temporada'];
}
function queryByCampaniaId($value)
{
$sql = ' SELECT co.* FROM configuracion AS co
INNER JOIN usuario AS u ON u.id = co.usuario_id
INNER JOIN usuario_empresa AS ue ON ue.usuario_id = u.id
INNER JOIN empresa AS e ON e.id = ue.empresa_id
INNER JOIN campania AS ca ON ca.empresa_distribuidora_id = e.id
WHERE ca.id = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($value);
return $this->getList($sqlQuery);
}
public function getRelations()
{
$sqlQuery = new SqlQuery();
$sql = "SELECT id FROM can_do\n RIGHT JOIN cocktail ON can_do.id_cocktail = cocktail.id\n WHERE can_do.id_barkeeper = " . $this->getId();
$result = $sqlQuery->execute($sql);
$result = array_map(function ($item) {
return intval($item['id']);
}, $result['data']);
$cocktails = new CocktailCollection();
$cocktails->load($result);
return $cocktails->getItemsRaw();
}
public function queryUsuarioEvento($idUsuario, $idEvento)
{
$sql = 'SELECT DISTINCT e.* FROM evento AS e ';
$sql .= ' INNER JOIN campania AS c ON c.id = e.campania_id';
$sql .= ' INNER JOIN empresa AS e ON e.id = c.empresa_id OR e.id = c.empresa_distribuidora_id ';
$sql .= ' INNER JOIN usuario_empresa AS ue ON e.id = ue.empresa_id';
$sql .= ' WHERE ue.usuario_id = ? AND e.id = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($idUsuario);
$sqlQuery->setNumber($idEvento);
return $this->getList($sqlQuery);
}
public function loadByCocktail($cocktailId)
{
$sqlQuery = new SqlQuery();
$sql = "SELECT id_ingredient as id FROM has_ingredient WHERE id_cocktail=" . $cocktailId;
$result = $sqlQuery->execute($sql);
if ($result['status']) {
$ingredientList = array_map(function ($item) {
return $item['id'];
}, $result['data']);
$this->load($ingredientList);
}
}
function queryAllUsuariosByDistribuidor($distribuidorId)
{
$sql = 'SELECT distinct u.* FROM usuario_empresa AS ued
INNER JOIN empresa AS ed ON ed.id = ued.empresa_id
INNER JOIN campania AS c ON c.empresa_distribuidora_id = ed.id
INNER JOIN empresa AS e ON e.id = c.empresa_id OR e.id = e.empresa_distribuidora_id
INNER JOIN usuario_empresa AS ue ON ue.empresa_id = e.id
INNER JOIN usuario as u ON u.id = ue.usuario_id
WHERE ued.usuario_id = ? AND u.usuario_grupo_id = 2';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($distribuidorId);
return $this->getList($sqlQuery);
}
/**
* Get teal Dashboard records from table
*/
public function queryAllTeamDasboard($projectId, $projectId, $projectId, $teamName, $projectId, $teamName, $projectId, $teamName)
{
$sql = "(SELECT challenge.id, challenge.title, challenge.type, challenge.status, challenge.creation_time, user.first_name, user.last_name, user.username \r\n\t\t\t\t\tFROM challenges as challenge JOIN user_info as user \r\n\t\t\t\t\t\tWHERE challenge.project_id = ? \r\n\t\t\t\t\t\t\tAND (challenge.type = '1' OR challenge.type = '2') \r\n\t\t\t\t\t\t\tAND challenge.status = '1' \r\n\t\t\t\t\t\t\tAND challenge.status != '3' \r\n\t\t\t\t\t\t\tAND challenge.status != '7' \r\n\t\t\t\t\t\t\tAND challenge.user_id = user.id\r\n\t\t\t\t\t)\r\n\t\t\t\t\tUNION\r\n\t\t\t\t\t(SELECT DISTINCT challenge.id, challenge.title, challenge.type, challenge.status, challenge.creation_time, user.first_name, user.last_name, user.username \r\n\t\t\t\t\t\tFROM challenges as challenge JOIN user_info as user JOIN challenge_ownership as owner \r\n\t WHERE challenge.project_id = ?\r\n\t AND challenge.id = owner.challenge_id \r\n\t AND owner.user_id = user.id \r\n\t AND challenge.status != '3' \r\n\t AND challenge.status != '7'\r\n\t AND owner.user_id \r\n\t IN (SELECT user_id FROM teams WHERE project_id = ? AND team_name = ? AND member_status = '1')\r\n\t AND challenge.id \r\n\t \tNOT IN (SELECT challenge_id FROM team_tasks WHERE project_id = ? AND team_name = ?)\r\n\t\t\t\t\t)\r\n\t\t\t\t\tUNION\r\n\r\n\t\t\t\t\t(SELECT a.challenge_id, challenge.title, challenge.type, challenge.status, challenge.creation_time, user.first_name, user.last_name, user.username \r\n\t\t\t\t\t\tFROM team_tasks as a join challenges as challenge JOIN user_info as user\r\n\t WHERE a.project_id = ? \r\n\t AND a.team_name = ? \r\n\t AND a.challenge_id = challenge.id\r\n\t\t\t\t\t\t AND challenge.user_id = user.id\r\n\t\t\t\t\t\t AND challenge.status != '3' \r\n\t AND challenge.status != '7'\r\n\t\t\t\t)";
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($projectId);
$sqlQuery->set($projectId);
$sqlQuery->set($projectId);
$sqlQuery->set($teamName);
$sqlQuery->set($projectId);
$sqlQuery->set($teamName);
$sqlQuery->set($projectId);
$sqlQuery->set($teamName);
return $this->getListAllTeamDashboard($sqlQuery);
}
