CREATE OR REPLACE FUNCTION "public"."searchcases8"(_caseid varchar, caseuid varchar, patientuid varchar, lastname varchar, firstname varchar, middlename varchar, birthdate varchar, snils varchar, polis varchar, medicalorganizationogrn varchar, casetypeid varchar, initgoalid varchar, doctorlastname varchar, doctorfirstname varchar, doctormiddlename varchar, doctorsnils varchar, tabelnumber varchar, positionid varchar, positionname varchar, diagnosmkb varchar, datestepfrom varchar, datestepto varchar, createddatefrom varchar, _createddateto varchar)
  RETURNS SETOF "pg_catalog"."int4" AS $BODY$
declare
caseid integer;
caseuid character varying;
patientUid character varying;
lastName character varying;
firstName character varying;
middleName character varying;
birthDate date;
snils character varying;
polis character varying;
medicalOrganizationOgrn character varying;
caseTypeId integer;
initGoalId integer;
doctorlastName character varying;
doctorfirstName character varying;
doctormiddleName character varying;
doctorsnils character varying;
TabelNumber character varying;
positionId integer;
positionName character varying;
diagnosMKB character varying;
dateStepFrom date;
dateStepTo date;
createdDateFrom date;
createdDateTo date;

patientId integer;

begin
if $1 is not null and $1 <> ''  then caseid := $1::integer; end if;
caseuid  := $2;

patientUid  := $3;
lastName  := $4;
firstName  := $5;
middleName  := $6;
if $7 is not null and $7 <> ''  then birthDate  := $7::date; else birthDate  := null; end if;
snils := $8;
polis := $9;
medicalOrganizationOgrn :=$10;
if $11 is not null and $11 <> ''  then  caseTypeId  := $11::integer; end if;
if $12 is not null and $12 <> ''  then initGoalId  := $12::integer; end if;
doctorlastName := $13;
doctorfirstName  := $14;
doctormiddleName  := $15;
doctorsnils  := $16;
TabelNumber  := $17;
if $18 is not null and $18 <> ''  then positionId  := $17::integer; end if;
positionName  := $19;
diagnosMKB  := $20;
if $21 is not null and $21 <> ''  then dateStepFrom  := $21::date; else dateStepFrom  := (SELECT '-infinity'::date);  end if;
if $22 is not null and $22 <> ''  then dateStepTo  := $22::date; else dateStepTo  := (SELECT 'infinity'::date);  end if;
if $23 is not null and $23 <> ''  then createdDateFrom  := $23::date; else createdDateFrom  := (SELECT '-infinity'::date);  end if;
if $24 is not null and $24 <> ''  then createdDateTo  := $24::date;  else createdDateTo  := (SELECT 'infinity'::date); end if;


if caseid is not null
 THEN RETURN QUERY select caseid;
 ELSE

 if caseuid is not null
  THEN RETURN QUERY select id from mc_case where uid = caseuid;

  ELSE

   if snils is not null
    then patientId := (select indiv_id from pim_individual_doc where type_id = 19 and number = snils limit 1);
   end if;

   if polis is not null
    then patientId := (select indiv_id from pim_individual_doc where type_id = 26 and number = polis limit 1);
   end if;

   if patientUid is not null and patientId is null
       then patientId := (select indiv_id from pim_indiv_code where type_id = 8 and code = patientUid limit 1);
   end if;

   if patientId is null
       then patientId:= (select id from pim_individual where surname like  coalesce (lastName, '%') and name like  coalesce (firstName, '%') and patr_name like  coalesce (middleName, '%')  and birth_dt::text like coalesce (birthDate::text, '%') limit 1 );
   end if;

    if patientId is not null then

    RETURN QUERY select distinct (mc.id) from mc_case mc
    join mc_step ms on ms.case_id = mc.id
    where mc.case_type_id = caseTypeId and mc.patient_id = patientId and mc.init_goal_id::text like coalesce (initGoalId::text, '%%') and ms.id in
    (select steps_by_res_group_search3 (medicalOrganizationOgrn, doctorlastName, doctorfirstName, doctormiddleName, doctorsnils, TabelNumber, positionId, positionName, diagnosMKB, dateStepFrom, dateStepTo, patientId))
    and  mc.open_date  >= coalesce (createdDateFrom, '-infinity'::date) and mc.open_date  <= coalesce (createdDateTo, 'infinity'::date);

    else RETURN QUERY select distinct (mc.id)  from mc_case mc
    join mc_step ms on ms.case_id = mc.id
where mc.case_type_id = caseTypeId and mc.init_goal_id::text like coalesce (initGoalId::text, '%%') and ms.id in
    (select steps_by_res_group_search3_without_patient (medicalOrganizationOgrn, doctorlastName, doctorfirstName, doctormiddleName, doctorsnils, TabelNumber, positionId, positionName, diagnosMKB, dateStepFrom, dateStepTo))
    and  mc.open_date  >= coalesce (createdDateFrom, '-infinity'::date) and mc.open_date  <= coalesce (createdDateTo, 'infinity'::date);

  end if;
 END IF;
 END if;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE COST 100
 ROWS 1000
;