Реестр страховой по счету

Для вывода реестра специализированного формата для страховой компании, сотрудниками разработчика составлена процедура формирования данных для подобного реестра.

CREATE OR ALTER PROCEDURE SDSYS$JACCREPORT
(
  SCHNUM TYPE OF COLUMN JACCOUNTS.SCHNUM,
  REPTYPE INTEGER = 0,
  APCODE TYPE OF COLUMN CLIENTS.PCODE = NULL
)
RETURNS
(
  ROWNUM TICODE,
  CLIENTNUM TICODE,
  ISFIRSTROWBYCLIENT TICODE,
  HISTID TYPE OF COLUMN JPPAYMENTS.HISTID,
  TREATCODE TYPE OF COLUMN JPPAYMENTS.TREATCODE,
  DCODE TYPE OF COLUMN JPPAYMENTS.DCODE,
  DEPNUM TYPE OF COLUMN DEPARTMENTS.DEPNUM,
  DEPNAME TYPE OF COLUMN DEPARTMENTS.DEPNAME,
  SORTORDER TYPE OF COLUMN TREATSCH.SCHNUM,
  PMDATE TYPE OF COLUMN JPPAYMENTS.PMDATE,
  NSP TYPE OF COLUMN JPPAYMENTS.NSP,
  HISTNUM TYPE OF COLUMN JPPAYMENTS.HISTNUM,
  FILIAL TYPE OF COLUMN JPPAYMENTS.FILIAL,
  FULLNAME TYPE OF COLUMN CLIENTS.FULLNAME,
  FIRSTNAME TYPE OF COLUMN CLIENTS.FIRSTNAME,
  MIDNAME TYPE OF COLUMN CLIENTS.MIDNAME,
  LASTNAME TYPE OF COLUMN CLIENTS.LASTNAME,
  BDATE TYPE OF COLUMN CLIENTS.BDATE,
  PCODE TYPE OF COLUMN CLIENTS.PCODE,
  DATEENTER TYPE OF COLUMN CLIENTS.DATEENTER,
  DATEEXIT TYPE OF COLUMN CLIENTS.DATEEXIT,
  DNAME TYPE OF COLUMN DOCTOR.DNAME,
  SCHID TYPE OF COLUMN WSCHEMA.SCHID,
  KODOPER TYPE OF COLUMN WSCHEMA.KODOPER,
  SCHNAME TYPE OF COLUMN WSCHEMA.SCHNAME,
  SCOUNT TYPE OF COLUMN JPAYMDET.SCOUNT,
  AMOUNTRUB TYPE OF COLUMN JPAYMDET.AMOUNTRUB,
  AMOUNTRUB_A TYPE OF COLUMN JPAYMDET.AMOUNTRUB_A,
  ROUNDRUB_A TYPE OF COLUMN JPAYMDET.ROUNDRUB,
  ROUNDRUB TYPE OF COLUMN JPAYMDET.ROUNDRUB,
  REALCOUNT TYPE OF COLUMN JPAYMDET.REALCOUNT,
  TOOTHCODE TYPE OF COLUMN JPAYMDET.TOOTHCODE,
  DGNAME TYPE OF COLUMN DIAGNOSIS.DGNAME,
  MKBCODE TYPE OF COLUMN DIAGNOSIS.MKBCODE,
  WORKPLACE TYPE OF COLUMN DICINFO.SIMPLENAME,
  MKBCODES TTEXT16K,
  IS_FIRST_DIAG TSMALLCODE,
  SERVCOUNT_BY_CLIENT TYPE OF COLUMN JPAYMDET.REALCOUNT,
  TOTAL_BY_CLIENT TYPE OF COLUMN JPAYMDET.ROUNDRUB
)
AS
DECLARE histid_buf TYPE OF COLUMN jppayments.histid;
DECLARE treatcode_buf TYPE OF COLUMN jppayments.treatcode;
DECLARE dcode_buf TYPE OF COLUMN JPPAYMENTS.dcode;
DECLARE sortorder_buf TYPE OF COLUMN treatsch.schnum;
DECLARE pmdate_buf TYPE OF COLUMN jppayments.pmdate;
DECLARE depnum_buf TYPE OF COLUMN departments.depnum;
DECLARE depname_buf TYPE OF COLUMN departments.depname;
DECLARE nsp_buf TYPE OF COLUMN jppayments.nsp;
DECLARE histnum_buf TYPE OF COLUMN jppayments.histnum;
DECLARE filial_buf TYPE OF COLUMN jppayments.filial;
DECLARE fullname_buf TYPE OF COLUMN clients.fullname;
DECLARE firstname_buf TYPE OF COLUMN clients.firstname;
DECLARE midname_buf TYPE OF COLUMN clients.midname;
DECLARE lastname_buf TYPE OF COLUMN clients.lastname;
DECLARE bdate_buf TYPE OF COLUMN clients.bdate;
DECLARE pcode_buf TYPE OF COLUMN clients.pcode;
DECLARE dateenter_buf TYPE OF COLUMN clients.dateenter;
DECLARE dateexit_buf TYPE OF COLUMN clients.dateexit;
DECLARE dname_buf TYPE OF COLUMN doctor.dname;
DECLARE schid_buf TYPE OF COLUMN wschema.schid;
DECLARE kodoper_buf TYPE OF COLUMN wschema.kodoper;
DECLARE schname_buf TYPE OF COLUMN wschema.schname;
DECLARE scount_buf TYPE OF COLUMN jpaymdet.scount;
DECLARE amountrub_buf TYPE OF COLUMN jpaymdet.amountrub;
DECLARE amountrub_a_buf TYPE OF COLUMN jpaymdet.amountrub_a;
DECLARE roundrub_a_buf TYPE OF COLUMN jpaymdet.roundrub;
DECLARE roundrub_buf TYPE OF COLUMN jpaymdet.roundrub;
DECLARE realcount_buf TYPE OF COLUMN jpaymdet.realcount;
DECLARE toothcode_buf TYPE OF COLUMN jpaymdet.toothcode;
DECLARE dgname_buf TYPE OF COLUMN diagnosis.dgname;
DECLARE mkbcode_buf TYPE OF COLUMN diagnosis.mkbcode;
DECLARE workplace_buf TYPE OF COLUMN dicinfo.simplename;
DECLARE mkbcodes_buf ttext16k;
DECLARE total_by_client_buf TYPE OF COLUMN jpaymdet.roundrub;
DECLARE servcount_by_client_buf TYPE OF COLUMN jpaymdet.realcount;
DECLARE prev_pcode TYPE OF COLUMN clients.pcode;
DECLARE total_by_client_accum TYPE OF COLUMN jpaymdet.roundrub;
DECLARE servcount_by_client_accum TYPE OF COLUMN jpaymdet.realcount;
DECLARE is_first_row ticode;
DECLARE dgcode_buf TYPE OF COLUMN jpaymdet.dgcode;
DECLARE diaglist_by_client ttext32k;
DECLARE is_next_client tsmallcode;
BEGIN
  total_by_client_accum = 0;
  servcount_by_client_accum = 0;
  is_first_row = 1;
  isfirstrowbyclient = 1;
  clientnum = 0;
  is_next_client = 0;
  rownum = 0;
  diaglist_by_client = ',';
 
  FOR SELECT (SELECT FIRST 1 ts.schnum FROM treatsch ts WHERE ts.treatcode = jd.treatcode AND ts.schid = jd.schid) sortorder,
             jd.treatcode, jd.dcode, t.depnum, dep.depname,
             t.treatdate pmdate,
             (SELECT list(DISTINCT Jj.NSP) FROM jppayments jj INNER JOIN jacclink jjl ON jj.treatcode=jjl.treatcode
               WHERE jjl.accid = :schnum AND jj.pcode = jd.pcode) nsp,
             c.histnum, c.fullname, c.firstname, c.midname, c.lastname, c.bdate, d.dname, c.pcode, c.dateenter,
             c.dateexit, w.schid,
             CASE WHEN POSITION('-', w.kodoper)>0 THEN SUBSTRING(w.kodoper FROM 1 FOR POSITION('-', w.kodoper)-1) ELSE w.kodoper END kodoper, 
             COALESCE(NULLIF(w.texttoorder,''), w.schname) schname,
             jd.scount, jd.amountrub,jd.amountrub_a, (jd.amountrub_a*jd.scount) roundrub_a,
             jd.roundrub, jd.realcount,
             jd.dgcode, COALESCE(dgc.mkbcode, dgj.mkbcode, dg.mkbcode, dgt.mkbcode), 
             COALESCE(dgc.dgname, dgj.dgname, dg.dgname, dgt.dgname), jd.filial,
             (SELECT list(dg2.mkbcode, ';;') FROM diagclients dc LEFT JOIN diagnosis dg2 ON dg2.dgcode = dc.dgcode
               WHERE dc.objcode = jd.treatcode AND dc.objtype = 1) mkbcodes,
             iif(jd.toothcode > 0, jd.toothcode, NULL) toothcode,
             dic.simplename workplace
        FROM jpaymdet jd
       INNER JOIN treatsch ts ON jd.treatcode = ts.treatcode AND jd.schid = ts.schid AND jd.schnum = ts.schnum AND COALESCE(ts.complex_schid,0) = 0
       INNER JOIN wschema w ON jd.schid = w.schid
       INNER JOIN jacclink jl ON jd.treatcode=jl.treatcode
        LEFT JOIN Treat T ON Jd.TreatCode = T.TreatCode
        LEFT JOIN doctor d ON t.dcode = d.dcode
        LEFT JOIN clients c ON jd.pcode = c.pcode
        LEFT JOIN diagclients dgcl ON dgcl.objtype=1
                                  AND dgcl.objcode=t.treatcode
                                  AND dgcl.organid=jd.toothcode
                                  AND dgcl.dgtypecode=1
        LEFT JOIN diagnosis dgc ON dgc.dgcode = dgcl.dgcode
        LEFT JOIN diagnosis dg ON dg.dgcode = jd.dgcode
        LEFT JOIN diagnosis dgj ON dgj.dgcode = jd.dgcode
        LEFT JOIN DIAGNOSIS dgt ON t.dgcode = dgt.dgcode
        LEFT JOIN dicinfo dic ON c.workplaceid = dic.dicid AND dic.refid = 53
        LEFT JOIN departments dep ON dep.depnum = t.depnum
        LEFT JOIN speciality spec ON w.speccode = spec.scode
       WHERE jl.accid = :schnum AND (jd.pcode IS NOT NULL) AND (:apcode IS NULL OR jd.pcode = :apcode)
         AND ( :REPTYPE = 0 OR spec.razdel= :REPTYPE OR w.speccode = :REPTYPE -3)
       ORDER BY c.fullname, c.pcode, t.treatdate,jd.treatcode,sortorder
  INTO sortorder_buf, treatcode_buf, dcode_buf, depnum_buf, depname_buf, pmdate_buf, nsp_buf, histnum_buf, fullname_buf,
       firstname_buf, midname_buf, lastname_buf, bdate_buf, dname_buf, pcode_buf, dateenter_buf, dateexit_buf, schid_buf, 
       kodoper_buf, schname_buf, scount_buf, amountrub_buf, amountrub_a_buf, roundrub_a_buf, roundrub_buf,  realcount_buf, 
       dgcode_buf, mkbcode_buf, dgname_buf, filial_buf, mkbcodes_buf, toothcode_buf, workplace_buf do
  BEGIN
    IF (is_first_row = 1) THEN
      prev_pcode = pcode_buf;
 
    IF (prev_pcode <> pcode_buf) THEN
    BEGIN
      is_next_client = 1;
      diaglist_by_client = ',';
      total_by_client = total_by_client_accum;
      total_by_client_accum = roundrub_buf;
 
      servcount_by_client = servcount_by_client_accum;
      servcount_by_client_accum = realcount_buf;
 
      prev_pcode = pcode_buf;
    END
    ELSE
    BEGIN
      total_by_client_accum = total_by_client_accum + roundrub_a_buf;
      servcount_by_client_accum = servcount_by_client_accum + realcount_buf;
      --suspend;
    END
 
    IF (is_first_row = 0) THEN
    BEGIN
      suspend;
      isfirstrowbyclient = 0;
    END
    ELSE
    BEGIN
      is_first_row = 0;
    END
 
    rownum = rownum + 1;
 
    treatcode = treatcode_buf;
    dcode = dcode_buf;
    depnum = depnum_buf;
    depname = depname_buf;
    sortorder = sortorder_buf;
    pmdate = pmdate_buf;
    nsp = nsp_buf;
    histnum = histnum_buf;
    filial = filial_buf;
    fullname = fullname_buf;
    firstname = firstname_buf;
    midname = midname_buf;
    lastname = lastname_buf;
    bdate = bdate_buf;
    pcode = pcode_buf;
    dateenter = dateenter_buf;
    dateexit = dateexit_buf;
    dname = dname_buf;
    schid = schid_buf;
    kodoper = kodoper_buf;
    schname = schname_buf;
    scount = scount_buf;
    amountrub = amountrub_buf;
    amountrub_a = amountrub_a_buf;
    roundrub_a = roundrub_a_buf;
    roundrub = roundrub_buf;
    realcount = realcount_buf;
    toothcode = toothcode_buf;
    dgname = dgname_buf;
    mkbcode = mkbcode_buf;
    workplace = workplace_buf;
    mkbcodes = mkbcodes_buf;
 
    total_by_client = NULL;
    servcount_by_client = NULL;
 
    IF (diaglist_by_client containing ',' || :dgcode_buf || ',' OR :dgcode_buf IS NULL) THEN
    BEGIN
      is_first_diag = 0;
    END 
    ELSE
    BEGIN
      diaglist_by_client = diaglist_by_client || :dgcode_buf || ',';
      is_first_diag = 1;
    END
    --if (rownum > 13) then
    --  exception error diaglist_by_client || dgcode_buf ;
 
    IF (is_next_client = 1) THEN
    BEGIN
      isfirstrowbyclient = 1;
      is_next_client = 0;
      clientnum = clientnum + 1;
    END
    ELSE
      IF (rownum > 1) THEN
        isfirstrowbyclient = 0;
 
    is_first_row = 0;
  END
  total_by_client = total_by_client_accum;
  servcount_by_client = servcount_by_client_accum;
  suspend;
END