/**
* Получает последние 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;
}
