CREATE OR REPLACE FUNCTION hr.fn_get_candidate_count(
    status integer DEFAULT 0, -- статус
    trash boolean DEFAULT false, -- trash
    date character varying DEFAULT '', -- дата, когда выборка идет за день или за месяц
    date_format character varying DEFAULT '', -- формат даты
    id integer DEFAULT 0, -- выборка по id
    name character varying DEFAULT '', -- фамилия кандидата
    firstname character varying DEFAULT '', -- имя кандидата
    patronymic character varying DEFAULT '', -- отчество кандидата
    source integer DEFAULT 0, -- источник
    date_from timestamp without time zone DEFAULT NULL, -- с
    date_to timestamp without time zone DEFAULT NULL, -- по какой дате
    hunter integer DEFAULT 0, -- id хантера
    vacancy_id integer DEFAULT 0, -- id вакансии
    post_id integer DEFAULT 0, -- id должности
    hunter_score character varying DEFAULT '', -- оценка
    photo_present integer DEFAULT -1, -- есть или нет фото
    is_responce integer DEFAULT -1, -- отклик или нет
    is_paid boolean DEFAULT NULL, -- проплачено или нет
    is_test_complete boolean DEFAULT NULL, -- пройден или нет тест
    count_on_accepted boolean DEFAULT false, -- подсчитывать ли по статусу "Принято"
    hr integer DEFAULT 0, -- id сотрудника, ответственного за вакансию
    auto_trash boolean DEFAULT NULL, -- архивировано ли резюме автоматически
    email character varying DEFAULT '', -- email
    refuse_reason_id integer DEFAULT 0, -- идешник причины отказа
    trash_reason_id integer DEFAULT 0, -- идешник причины переноса в архив
    active_with_auto_trash boolean DEFAULT false, -- флаг: учитывать или нет активные и trash + auto_trash вместе
    dismissal_reason_id integer DEFAULT 0 -- идешник причины увольнения 
    dismissal_type integer DEFAULT 0 -- тип увольнения 
) returns integer
 as $$
DECLARE
    q varchar := ''; -- сам запрос
    q_where varchar := ' WHERE '; -- строка условий
    count integer;
BEGIN
    q := 'SELECT count(DISTINCT self.id) FROM hr.hr_candidate as self ';

    IF (status != 0) THEN
        q_where := q_where || ' self.status = ' || status || ' AND ';
    END IF;

    IF (trash IS NOT NULL) THEN
        IF (trash) THEN
            q_where := q_where || ' self.trash = true';

            IF (auto_trash IS NOT NULL) THEN
                q_where := q_where || ' AND self.auto_trash = ' || auto_trash;
            END IF;

            IF (trash_reason_id != 0) THEN
                q_where := q_where || ' AND self.trash_reason_id = ' || trash_reason_id;
            END IF;
        ELSE
            q_where := q_where || ' self.trash = false';
        END IF;
    ELSE
        IF (active_with_auto_trash) THEN
            q_where := q_where || ' (self.trash = false OR (self.trash = true AND self.auto_trash = ' || auto_trash || '))';
        ELSE
            q_where := q_where || ' self.status IS NOT NULL'; -- это хак, чтобы блок условий не начинался с AND
        END IF;
    END IF;

    IF (date != '') THEN
        IF (count_on_accepted) THEN
            q_where := q_where || ' AND to_char(log.date, ' || quote_literal(date_format) || ') = ' || quote_literal(date);
        ELSE
            q_where := q_where || ' AND to_char(self.date, ' || quote_literal(date_format) || ') = ' || quote_literal(date);
        END IF;
    END IF;

    IF (id != 0) THEN
        q_where := q_where || ' AND self.id = ' || id;
    END IF;

    IF (name != '') THEN
        q_where := q_where || ' AND self.name ILIKE ' || quote_literal('%' || name || '%');
    END IF;

    IF (firstname != '') THEN
        q_where := q_where || ' AND self.firstname ILIKE ' || quote_literal('%' || firstname || '%');
    END IF;

    IF (patronymic != '') THEN
        q_where := q_where || ' AND self.patronymic ILIKE ' || quote_literal('%' || patronymic || '%');
    END IF;

    IF (source != 0) THEN
        IF (source = 1004) THEN
            q_where := q_where || ' AND self.channel = ' || quote_literal('hh.lbc.ru');
        ELSE
            q_where := q_where || ' AND self.source = ' || source;
        END IF;
    END IF;

    IF (date_from IS NOT NULL) THEN
        q_where := q_where || ' AND self.date >= ' || quote_literal(date_from);
    END IF;

    IF (date_to IS NOT NULL) THEN
        q_where := q_where || ' AND self.date <= ' || quote_literal(date_to);
    END IF;

    IF (hunter != 0) THEN
        q_where := q_where || ' AND self.hunter = ' || hunter;
    END IF;

    IF (vacancy_id != 0) THEN
        q_where := q_where || ' AND self.vacancy_id = ' || vacancy_id;
    END IF;

    IF (post_id != 0) THEN
        q_where := q_where || ' AND self.post_id = ' || post_id;
    END IF;

    IF (hunter_score != '') THEN
        q_where := q_where || ' AND self.hunter_score::text = ' || quote_literal(hunter_score);
    END IF;

    IF (photo_present > -1) THEN
        q_where := q_where || ' AND self.photo_present::integer = ' || photo_present;
    END IF;

    IF (is_responce > -1) THEN
        q_where := q_where || ' AND self.is_responce = ' || is_responce;
    END IF;

    IF (is_paid IS NOT NULL) THEN
        q_where := q_where || ' AND self.is_paid = ' || is_paid;
    END IF;

    IF (is_test_complete IS NOT NULL) THEN
        q_where := q_where || ' AND (
                SELECT
                    act.date_test_end
                FROM
                    hr.hr_candidate_action as act
                WHERE
                    act.candidate_id = self.id
                    AND act.date_test_start IS NOT NULL
                ORDER BY
                    act.id desc
                LIMIT 1
            )';

        IF (is_test_complete) THEN
            q_where := q_where || ' IS NOT NULL';
        ELSE
            q_where := q_where || ' IS NULL';
        END IF;
    END IF;

    IF (count_on_accepted) THEN
        q := q || ' JOIN hr.hr_resume_log as log ON log.candidate_id = self.id';

        q_where := q_where || '
            AND log.new_status_id = ' || status || '
            AND log.old_status_id <> log.new_status_id
            AND log.new_status_id = self.status';
    END IF;

    IF (hr != 0) THEN
        q := q || ' JOIN hr.hr_vacancy_staff_role as role ON role.vacancy_id = self.vacancy_id AND role.role = 1';

        q_where := q_where || '
            AND role.lst_staff_id = ' || hr;
    END IF;

    IF (email != '') THEN
        q_where := q_where || ' AND self.email ILIKE ' || quote_literal('%' || email || '%');
    END IF;

    IF (refuse_reason_id != 0) THEN
        q_where := q_where || ' AND self.refuse_reason_id = ' || refuse_reason_id;
    END IF;
    
    IF (dismissal_type != 0) THEN
        IF (dismissal_type == 1) THEN
            q_where := q_where || ' AND self.dismissal_date is not null AND self.dismissal_id <> 0';
        ELSE
            q := q || ' LEFT JOIN hr.hr_dismissal_reason as dismissal_reason ON self.dismissal_reason_id = dismissal_reason.id';

            IF (dismissal_type == 2) THEN
                q_where := q_where || '
                    AND dismissal_reason.by_self = true';
            END IF;

            IF (dismissal_type == 3) THEN
                q_where := q_where || '
                    AND dismissal_reason.by_self = false';
            END IF;
        END IF;    
    END IF;

    IF (dismissal_reason_id != 0) THEN
        q_where := q_where || ' AND self.dismissal_reason_id = ' || dismissal_reason_id;
    END IF;

    q := q || q_where;

    RAISE NOTICE  'SQL: %', q;

 EXECUTE q INTO count;

 RETURN count;
END;
$$ LANGUAGE plpgsql;