/** * Получает последние N опубликованных проектов. * * Внимание! Используется в externalApi. * @see externalApi_Freetray * * @param integer $kind тип проектов (-1=5=Все проекты; 2=Конкурсы; 4=В офис; 6=Только для про) * @param integer $limit сколько вернуть проектов * @param array $filter массив с фильтром проектов * * @return array массив проектов. */ function getLastProjects($kind = -1, $filter = NULL, $limit = 'ALL', $is_tray = false) { global $DB; $addit = 'p.closed = false AND p.kind <> 9'; //$addit = 'p.closed = false AND p.kind <> 9 AND p.state = '.self::STATE_PUBLIC; //$addit = "p.closed = false AND p.kind <> 9 AND NOT(p.payed = 0 AND p.kind = ".self::KIND_VACANCY." AND p.state = ".self::STATE_MOVED_TO_VACANCY.") "; if ($kind == 6) { $addit .= ' AND p.pro_only = true'; } else { if ($kind == 2) { $addit .= " AND (p.kind = 2 OR p.kind = 7)"; } else { if ($kind != -1 && $kind != 5) { $addit .= " AND p.kind = {$kind}"; } } } $filterSql = new_projects::createFilterSql($filter, $kind); $limit1 = $limit; $limit2 = ''; if (is_int($limit)) { $limit1 *= 2; $limit2 = " LIMIT {$limit} "; } if ($is_tray === false) { $order = "p.post_date"; } else { $order = "p.create_date"; } $sql = "\n SELECT p.*, f.path||f.fname as logo, e.is_pro, e.is_verify\n FROM (\n SELECT p.*, NULL AS category, NULL AS subcategory\n FROM projects p\n LEFT JOIN projects_blocked pb ON p.id = pb.project_id\n\n WHERE {$addit} {$filterSql} AND pb.project_id IS NULL /*AND (p.moderator_status IS NULL OR p.moderator_status > 0)*/\n ORDER BY post_date DESC LIMIT {$limit1}\n ) as p\n\n INNER JOIN\n employer e\n ON e.uid = p.user_id\n AND e.is_banned = '0'\n LEFT JOIN\n file_projects f\n ON f.id = p.logo_id\n \n ORDER BY {$order} DESC {$limit2}\n "; if ($rows = $DB->rows($sql)) { foreach ($rows as &$row) { $cats = self::getProjectCategories($row['id'], true); $row['category'] = $cats['category_id']; $row['subcategory'] = $cats['subcategory_id']; } } return $rows; }
/** * Количество моих проектов. * * @param integer $uid ИД пользователя * @param boolean $inc_blocked Если данная переменная в значение true, то мы берем все проекты без исключения, иначе не берем заблокированные проекты * @param integer $kind Тип проекта (0 - все, 1 - проекты, 2 - конкурсы, 3 - в офис) * @param integer $folder ID папки * @param integer $filter Данные фильтра * * @return array Кол-во проектов: kind_all - проекты всех видов, kind_prj - проекты(фриланс), kind_contest - конкурсы, kind_office - проекты(в офис), open - открытых проектов(с учетом фильтра), closed - закрытых(с учетом фильтра), all - отерытых и закрытых(с учетом фильтра) */ public function CountMyProjects($uid, $inc_blocked = true, $only_open = false, $kind = 0, $folder = -1, $filter = null) { global $DB; $filterSql = new_projects::createFilterSql($filter); $closed = "(closed = true OR COALESCE(end_date, 'infinity') < now() OR blocked.project_id IS NOT NULL)"; $not_closed = "(closed = false AND COALESCE(end_date, 'infinity') > now() AND blocked.project_id IS NULL)"; switch ($kind) { case '1': $where_kind = ' AND kind=1 '; break; case '2': $where_kind = ' AND (kind=2 OR kind=7) '; break; case '3': $where_kind = ' AND kind=4 '; break; default: //$where_kind = (!$inc_blocked)?' AND kind <> 9':''; $_uid = get_uid(FALSE); $where_kind = $inc_blocked ? '' : ($_uid <= 0 ? ' AND (p.kind <> 9)' : ' AND (p.kind <> 9 OR p.exec_id = ?i)'); if (!empty($where_kind)) { $where_kind = $DB->parse($where_kind, $_uid); } break; } $nFolderId = intval($folder); $where_folder = $nFolderId >= 0 ? ' AND p.folder_id = ' . $nFolderId : ''; if ($inc_blocked) { $sql = "select(select count(p.id) from projects as p \n left join projects_blocked as blocked on blocked.project_id = p.id \n WHERE (p.trash IS NULL OR p.trash = FALSE) \n AND user_id='" . $uid . "' {$where_kind} {$where_folder} {$filterSql} AND {$not_closed}) as open"; if (!$only_open) { $sql .= ",\n (\n select count(p.id) from projects as p \n left join projects_blocked as blocked on blocked.project_id = p.id \n where (p.trash IS NULL OR p.trash = FALSE) AND \n user_id='" . $uid . "' {$where_kind} {$where_folder} {$filterSql} AND {$closed}\n ) as closed, \n (\n select count(p.id) from projects as p \n left join projects_blocked as blocked on blocked.project_id = p.id \n where (p.trash IS NULL OR p.trash = FALSE) AND \n user_id='" . $uid . "' {$where_kind} {$where_folder} {$filterSql}\n ) as all;\n "; } } else { $_where = ' AND NOT(p.payed = 0 AND p.kind = ' . self::KIND_VACANCY . ' AND p.state = ' . self::STATE_MOVED_TO_VACANCY . ')'; $sql = "select(select count(p.id) from projects as p \n left join projects_blocked as blocked on blocked.project_id = p.id \n where (p.trash IS NULL OR p.trash = FALSE) \n AND user_id='" . $uid . "' {$where_kind} {$where_folder} {$filterSql} AND {$not_closed} \n AND blocked.project_id IS NULL {$_where}) as open"; if (!$only_open) { $sql .= ",\n (\n select count(p.id) from projects as p \n left join projects_blocked as blocked on blocked.project_id = p.id \n where (p.trash IS NULL OR p.trash = FALSE) \n AND user_id='" . $uid . "' {$where_kind} {$where_folder} {$filterSql} AND {$closed} \n AND blocked.project_id IS NULL {$_where}\n ) as closed, \n (\n select count(p.id) from projects as p \n left join projects_blocked as blocked on blocked.project_id = p.id \n where (p.trash IS NULL OR p.trash = FALSE) \n AND user_id='" . $uid . "' {$where_kind} {$where_folder} {$filterSql} AND blocked.project_id IS NULL {$_where}\n ) as all;\n "; } } $ret = $DB->row($sql); if ($inc_blocked) { $sql = 'SELECT COUNT(id) as kind_all, SUM(CASE WHEN kind=1 THEN 1 ELSE 0 END) as kind_prj, SUM(CASE WHEN (kind=2 OR kind=7) THEN 1 ELSE 0 END) as kind_contest, SUM(CASE WHEN kind=4 THEN 1 ELSE 0 END) as kind_office FROM projects WHERE (projects.trash IS NULL OR projects.trash = FALSE) AND projects.user_id=?i'; } else { $sql = 'SELECT COUNT(projects.id) as kind_all, SUM(CASE WHEN kind=1 THEN 1 ELSE 0 END) as kind_prj, SUM(CASE WHEN (kind=2 OR kind=7) THEN 1 ELSE 0 END) as kind_contest, SUM(CASE WHEN kind=4 THEN 1 ELSE 0 END) as kind_office FROM projects LEFT JOIN projects_blocked as blocked ON blocked.project_id = projects.id WHERE (projects.trash IS NULL OR projects.trash = FALSE) AND projects.user_id=?i AND blocked.project_id IS NULL AND NOT(projects.payed = 0 AND projects.kind = ' . self::KIND_VACANCY . ' AND projects.state = ' . self::STATE_MOVED_TO_VACANCY . ')'; } $ret1 = $DB->row($sql, $uid); $sql = 'SELECT COUNT(id) as trash FROM projects WHERE trash = TRUE AND projects.user_id=?i'; $ret2 = $DB->row($sql, $uid); $ret = array_merge($ret, $ret1, $ret2); return $ret; }