CREATE OR REPLACE FUNCTION hr.fn_get_candidate_list(
    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 -1, -- идешник причины увольнения
    dismissal_type integer DEFAULT 0, -- тип увольнения
    recall_reason integer DEFAULT 0, -- причина необходимости перезвонить
    is_probation_done boolean DEFAULT NULL, -- прошел или не прошел испытательный срок (35 дней)
    is_interview boolean DEFAULT false, -- показатель того, что выборка в статусе "Собеседование"
    with_comment_status boolean DEFAULT false, -- показатель того, необходима выборка последнего комментария
    staff_id integer DEFAULT 0, -- id сотрудника
    order_field character varying DEFAULT 'self.id', -- поле сортировки
    order_direction character varying DEFAULT 'desc', -- направление сортировки
    limitval integer DEFAULT 20, -- лимит выборки
    offsetval integer DEFAULT 0 -- сдвиг выборки
) returns setof record
 as $$
DECLARE
    q_fields varchar := ''; -- поля в выборке
    q varchar := ''; -- сам запрос
    q_where varchar := ' WHERE '; -- строка условий
    list record;
BEGIN
    q_fields := '
        SELECT
        "self"."id",
        "self"."status",
        "self"."channel",
        "self"."source",
        "self"."name",
        "self"."firstname",
        "self"."patronymic",
        "self"."resume_file_name",
        "self"."date",
        "self"."birthday",
        "self"."post_id",
        "self"."site_id",
        "self"."education1",
        "self"."education2",
        "self"."class_institution",
        "self"."selection_type",
        coalesce(self.hunter_score::text::integer, 0) AS "hunter_score",
        "self"."experience_years",
        "self"."sex",
        "self"."email",
        "self"."vacancy_id",
        "self"."age",
        "self"."photo_present",
        "self"."is_responce",
        "self"."is_paid",
        "self"."estimated_post",
        "self"."url",
        "self"."vacancy_id",
        "self"."refuse_reason_id",
        "self"."tentative_date",
        "self"."resume_text",
        "self"."test_result",
        "self"."test_staff_id",
        "self"."accept_date",
        "self"."dismissal_date",
        "self"."dismissal_reason_id",
        "self"."recall_reason",
        "self"."resume_file_name",
        "self"."photo_name",
        age(coalesce(birthday::timestamp, now()))::interval YEAR AS "age",
        (
            SELECT
                coalesce((staff.surname || '' '' || staff.name), '''')
            FROM
                hr.hr_resume_log as log
            LEFT JOIN
                lspassport.lspassport_staff_base as staff ON staff.id = log.lst_staff_id
            WHERE
                log.candidate_id = self.id AND
                log.new_status_id = self.status AND
                log.old_status_id = log.new_status_id
            ORDER BY
                log.id desc
            LIMIT 1
        ) AS "powerful_staff",
        (
            SELECT
                (CASE WHEN log.date IS NULL THEN now() ELSE log.date END)
            FROM
                hr.hr_resume_log as log
            WHERE
                log.candidate_id = self.id
                AND log.new_status_id = self.status
                AND log.old_status_id <> log.new_status_id
            ORDER BY
                log.id desc
            LIMIT 1
        ) AS "date_status",
        (
            SELECT
                log.id
            FROM
                hr.hr_resume_log as log
            WHERE
                log.candidate_id = self.id
                AND log.new_status_id = self.status
                AND log.old_status_id <> log.new_status_id
            ORDER BY
                log.id desc
            LIMIT 1
        ) AS "log_id",
        (
            SELECT
                log.called
            FROM
                hr.hr_resume_log as log
            WHERE
                log.candidate_id = self.id AND
                log.new_status_id = self.status AND
                log.old_status_id <> log.new_status_id
            ORDER BY
                log.id desc
            LIMIT 1
        ) AS "called",
        (
            SELECT
                coalesce((staff.surname || '' '' || staff.name || '' '' || staff.patronymic), '''')
            FROM
                hr.hr_resume_log as log
            LEFT JOIN
                lspassport.lspassport_staff_base as staff ON staff.id = log.lst_staff_id
            WHERE
                log.candidate_id = self.id
                AND log.new_status_id = self.status
                AND log.old_status_id <> log.new_status_id
            ORDER BY
                log.id desc
            LIMIT 1
        ) AS "lst_staff",
        (
            SELECT
                coalesce(role.code, '''')
            FROM
                hr.hr_vacancy_staff_role as role
            WHERE
                role.vacancy_id = self.vacancy_id
                AND role.lst_staff_id = ' || staff_id || '
                AND role.role = 3
        ) AS "role_code",
        "site"."name" AS "site_name",
        "site"."url" AS "site_url",
        "reason"."name" AS "refuse_reason",
        (staff.surname || '' '' || staff.name || '' '' || staff.patronymic) AS "hunter_name",
        coalesce(action.date_test_end, ''1970-01-01 00:00:00'') date_test_end,
        "action"."date_test_start",
        "action"."date_test_end",
        "action"."date_request",
        "action"."current_step",
        "action"."key",
        "test"."id" AS "test_id",
        "test"."step" AS "test_step"';

    q := ' FROM hr.hr_candidate as self
        LEFT JOIN "hr"."hr_site" AS "site" ON self.site_id = site.id
        LEFT JOIN "hr"."hr_refuse_reason" AS "reason" ON self.refuse_reason_id = reason.id
        LEFT JOIN "lspassport"."lspassport_staff_base" AS "staff" ON self.hunter = staff.id
        LEFT JOIN "hr"."hr_candidate_action" AS "action" ON self.id = action.candidate_id
        LEFT JOIN "hr"."hr_candidate_test" AS "test" ON self.vacancy_id = test.vacancy_id';

    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_reason_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 != -1) THEN
        q_where := q_where || ' AND self.dismissal_reason_id = ' || dismissal_reason_id;
        IF (dismissal_reason_id = 0) THEN
            q_where := q_where || ' AND self.dismissal_date is null';
        END IF;
    END IF;

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

    IF (is_probation_done IS NOT NULL) THEN
        q_where := q_where || ' AND self.dismissal_date is not null AND self.accept_date is not null';
        IF (is_probation_done = TRUE) THEN
            q_where := q_where || ' AND date(self.accept_date) + interval''35 days'' < self.dismissal_date';
        ELSE
            q_where := q_where || ' AND date(self.accept_date) + interval''35 days'' >= self.dismissal_date';
        END IF;
    END IF;

    IF (is_interview) THEN
        q_fields := q_fields || ',
            (
                SELECT
                    fi.date
                FROM
                    hr.hr_candidate_interview as fi
                WHERE
                    fi.candidate_id = self.id AND
                    fi.number = 1
                ORDER BY
                    fi.id desc
                LIMIT 1
            ) as first_interview_date,
            (
                SELECT
                    si.date
                FROM
                    hr.hr_candidate_interview as si
                WHERE
                    si.candidate_id = self.id AND
                    si.number = 1
                ORDER BY
                    si.id desc
                LIMIT 1
            ) as second_interview_date';
    END IF;

    IF (with_comment_status) THEN
        q_fields := q_fields || ',
            (
                SELECT
                    (CASE WHEN log.comment IS NULL THEN '''' ELSE log.comment END)
                FROM
                    hr.hr_resume_log as log
                WHERE
                    log.candidate_id = self.id
                ORDER BY
                    log.id desc
                LIMIT 1
            ) as status_comment';
    END IF;

    q := q_fields || q || q_where || ' ORDER BY ' || order_field || ' ' || order_direction || ' LIMIT ' || limitval || ' OFFSET ' || offsetval;

    RAISE NOTICE  'SQL: %', q;

 FOR list IN EXECUTE q LOOP
     RETURN NEXT list;
    END LOOP;
END;
$$ LANGUAGE plpgsql;