search

asfernandes / node-firebird-drivers

Node.js Firebird Drivers
MIT License
53 stars 17 forks source link

named parameters in SQL queries #126

Open gsbelarus opened 2 years ago

gsbelarus commented 2 years ago

couple examples of usage:

...
const result = await attachment.executeSingleton(transaction, 
  'SELECT id FROM some_table WHERE id > :min_id AND id < :max_id', 
  {  
    min_id: 1000,
    max_id: 2000
  });

with preparing query:

  ...
  const statement = await attachment.prepare(transaction, 
    'INSERT INTO t1 (n1) VALUES (:n)', 
    { namedParams: true });
  await statement.execute(transaction, { n: 1 });
asfernandes commented 2 years ago

I tend to not accept it as I don't think it's the way things should be, sorry.

It would save you some time if you firstly opened an issue or discussion, if you are not going to use it external to the driver.

Yes, there are libraries and tools doing that for Firebird, but this should (if done) be in the server engine.

If parameters are reused, it's the engine who must describe they types.

It's the engine who must allow or not this, for example in the middle of EXECUTE BLOCK or DDL statements.

There is already this kind of implementation in EXECUTE STATEMENT.

And your implementation does not care about q'...' strings, maybe more things.

It will always lack with engine's new features.

It will conflict with possible future Firebird engine's implementation.

gsbelarus commented 2 years ago

I see your point but not having named parameters makes working with large queries almost impossible. We have queries of hundreds of lines of code with dozens parameters. Just imagine what the amount of efforts is needed from developer just to insert one parameter in the middle of others. We work with named parameters in our existing system and it never was a problem. Now, implementing some parts of the system on nodejs with only positional parameters supported is a real pain.

gsbelarus commented 2 years ago

and the way it is implemented current functionality is not affected. just don't provide object with named parameters or don't specify the flag for prepare method and everything will work as before.

asfernandes commented 2 years ago

Give me a time to think more about it.

gsbelarus commented 2 years ago

here is an example from real life:

INSERT INTO gd_people(contactkey, firstname, surname, middlename, nickname, rank, 
    haddress, hcity, hregion, hzip, hcountry, hdistrict, hphone, 
    wcompanykey, wcompanyname, wdepartment, 
    spouse, children, sex, birthday, visitcard, photo, 
    passportnumber, passportexpdate, passportissdate, passportissuer, 
    passportisscity, personalnumber, wpositionkey, hplacekey) 
VALUES(:new_id, :new_firstname, :new_surname, :new_middlename, :new_nickname, :new_rank, 
    :new_haddress, :new_hcity, :new_hregion, :new_hzip, :new_hcountry, :new_hdistrict, :new_hphone, 
    :new_wcompanykey, :new_wcompanyname, :new_wdepartment, 
    :new_spouse, :new_children, :new_sex, :new_birthday, 
    :new_visitcard, :new_photo, 
    :new_passportnumber, :new_passportexpdate, :new_passportissdate, :new_passportissuer, 
    :new_passportisscity, :new_personalnumber, :new_wpositionkey, :new_hplacekey)

now, imagine it with only positional parameters:

INSERT INTO gd_people(contactkey, firstname, surname, middlename, nickname, rank, 
    haddress, hcity, hregion, hzip, hcountry, hdistrict, hphone, 
    wcompanykey, wcompanyname, wdepartment, 
    spouse, children, sex, birthday, visitcard, photo, 
    passportnumber, passportexpdate, passportissdate, passportissuer, 
    passportisscity, personalnumber, wpositionkey, hplacekey) 
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

how long it would take for a developer to understand where the problem is if something gets wrong? he/she would spend half of the working day just counting positions of that ?s. And simple modification of the table (adding or removing sole field will render all existing code unworkable.

let's look at another real life query:

SELECT 
  r.rec_counter, 
  SUM(r.begin_q) AS begin_q,          SUM(r.begin_w) AS begin_w,          SUM(r.begin_s) AS begin_s, 
  SUM(r.in_incub_q) AS in_incub_q,    SUM(r.in_incub_w) AS in_incub_w,    SUM(r.in_incub_s) AS in_incub_s, 
  SUM(r.in_weight_q) AS in_weight_q,  SUM(r.in_weight_w) AS in_weight_w,  SUM(r.in_weight_s) AS in_weight_s, 
  SUM(r.in_group_q) AS in_group_q,    SUM(r.in_group_w) AS in_group_w,    SUM(r.in_group_s) AS in_group_s, 
  SUM(r.in_buy_q) AS in_buy_q,        SUM(r.in_buy_w) AS in_buy_w,        SUM(r.in_buy_s) AS in_buy_s, 
  SUM(r.out_group_q) AS out_group_q,  SUM(r.out_group_w) AS out_group_w,  SUM(r.out_group_s) AS out_group_s, 
  SUM(r.out_01_1_q) AS out_01_1_q,        SUM(r.out_01_1_w) AS out_01_1_w,        SUM(r.out_01_1_s) AS out_01_1_s, 
  SUM(r.out_02_q) AS out_02_q,        SUM(r.out_02_w) AS out_02_w,        SUM(r.out_02_s) AS out_02_s, 
  SUM(r.out_03_q) AS out_03_q,        SUM(r.out_03_w) AS out_03_w,        SUM(r.out_03_s) AS out_03_s, 
  SUM(r.out_04_q) AS out_04_q,        SUM(r.out_04_w) AS out_04_w,        SUM(r.out_04_s) AS out_04_s 
FROM 
  /* Остаток на начало периода */ 
  (SELECT 
     CASE 
       WHEN (rem.agegroup = :age_42) AND (rem.goodkey = :good_chick) THEN 1 
       WHEN (rem.agegroup = :age_42) AND (rem.goodkey = :good_cock) THEN 2 
       WHEN (rem.agegroup = :age_140) AND (rem.goodkey = :good_chick) THEN 3 
       WHEN (rem.agegroup = :age_140) AND (rem.goodkey = :good_cock) THEN 4 
       WHEN (rem.agegroup = :age_43) AND (rem.goodkey = :good_chick) THEN 5 
       WHEN (rem.agegroup = :age_43) AND (rem.goodkey = :good_cock) THEN 6 
       WHEN (rem.agegroup = :age_130) AND (rem.goodkey = :good_chick) THEN 7 
       WHEN (rem.agegroup = :age_130) AND (rem.goodkey = :good_cock) THEN 8 
       WHEN (rem.agegroup = :age_1_130) AND (rem.goodkey = :good_chick) THEN 9 
       WHEN (rem.agegroup = :age_1_130) AND (rem.goodkey = :good_cock) THEN 10 
       WHEN (rem.agegroup = :age_180) AND (rem.goodkey = :good_chick) THEN 11 
       WHEN (rem.agegroup = :age_180) AND (rem.goodkey = :good_cock) THEN 12 
       WHEN (rem.agegroup = :age_broiler and rem.contactkey = :broiler_ceh1) THEN 13 
       WHEN (rem.agegroup = :age_broiler and rem.contactkey = :broiler_ceh2) THEN 14 
       WHEN (rem.agegroup = :age_broiler and rem.contactkey = :broiler_ceh3) THEN 15 
       WHEN (rem.agegroup = :age_parent) AND (rem.goodkey = :good_chick) THEN 16 
       WHEN (rem.agegroup = :age_parent) AND (rem.goodkey = :good_cock) THEN 17 
     END AS rec_counter, 
     rem.balance AS begin_q, 
     rem.weight AS begin_w, 
     rem.usr$sumncu AS begin_s, 
     0 AS in_incub_q,     0 AS in_incub_w,       0 AS in_incub_s, 
     0 AS in_weight_q,    0 AS in_weight_w,      0 AS in_weight_s, 
     0 AS in_group_q,     0 AS in_group_w,       0 AS in_group_s, 
     0 AS in_buy_q,       0 AS in_buy_w,         0 AS in_buy_s, 
     0 AS out_group_q,    0 AS out_group_w,      0 AS out_group_s, 
     0 AS out_01_1_q,       0 AS out_01_1_w,         0 AS out_01_1_s, 
     0 AS out_02_q,       0 AS out_02_w,         0 AS out_02_s, 
     0 AS out_03_q,       0 AS out_03_w,         0 AS out_03_s, 
     0 AS out_04_q,       0 AS out_04_w,         0 AS out_04_s 
   FROM 
     ( 
     SELECT 
       c.usr$brd_agegroupkey AS agegroup, 
       c.goodkey, 
       l.quantity as balance, l.usr$weight as weight, l.usr$sumncu, con.id as contactkey 
     FROM 
     GD_DOCUMENT Z 
       JOIN USR$BRD_INCOMELINE L ON z.id = l.documentkey 
       JOIN USR$BRD_INCOME I ON L.masterkey = I.documentkey 
       JOIN gd_contact con ON I.USR$TOCONTACTKEY = con.id 
       JOIN inv_card c ON c.id = l.fromcardkey 
     WHERE 
       z.documentdate = :datebegin - 1 and l.usr$vbpf_depotkey is null 

     ) rem 

   UNION ALL 

   /* Вывод цыплят из инкубации за выбранный период */ 
   SELECT 
     CASE 
       WHEN (c.usr$brd_agegroupkey = :age_42) AND (c.goodkey = :good_chick) THEN 1 
       WHEN (c.usr$brd_agegroupkey = :age_42) AND (c.goodkey = :good_cock) THEN 2 
       WHEN (c.usr$brd_agegroupkey = :age_140) AND (c.goodkey = :good_chick) THEN 3 
       WHEN (c.usr$brd_agegroupkey = :age_140) AND (c.goodkey = :good_cock) THEN 4 
       WHEN (c.usr$brd_agegroupkey = :age_43) AND (c.goodkey = :good_chick) THEN 5 
       WHEN (c.usr$brd_agegroupkey = :age_43) AND (c.goodkey = :good_cock) THEN 6 
       WHEN (c.usr$brd_agegroupkey = :age_130) AND (c.goodkey = :good_chick) THEN 7 
       WHEN (c.usr$brd_agegroupkey = :age_130) AND (c.goodkey = :good_cock) THEN 8 
       WHEN (c.usr$brd_agegroupkey = :age_1_130) AND (c.goodkey = :good_chick) THEN 9 
       WHEN (c.usr$brd_agegroupkey = :age_1_130) AND (c.goodkey = :good_cock) THEN 10 
       WHEN (c.usr$brd_agegroupkey = :age_180) AND (c.goodkey = :good_chick) THEN 11 
       WHEN (c.usr$brd_agegroupkey = :age_180) AND (c.goodkey = :good_cock) THEN 12 
       WHEN (c.usr$brd_agegroupkey = :age_broiler and head.usr$tocontactkey = :broiler_ceh1) THEN 13 
       WHEN (c.usr$brd_agegroupkey = :age_broiler and head.usr$tocontactkey = :broiler_ceh2) THEN 14 
       WHEN (c.usr$brd_agegroupkey = :age_broiler and head.usr$tocontactkey = :broiler_ceh3) THEN 15 
       WHEN (c.usr$brd_agegroupkey = :age_parent) AND (c.goodkey = :good_chick) THEN 16 
       WHEN (c.usr$brd_agegroupkey = :age_parent) AND (c.goodkey = :good_cock) THEN 17 
     END AS rec_counter, 
     0 AS begin_q,        0 AS begin_w,          0 AS begin_s, 
     line.quantity AS in_incub_q, 
     line.USR$WEIGHT AS in_incub_w, 
     (line.usr$sumncu) AS in_incub_s, 
     0 AS in_weight_q,    0 AS in_weight_w,      0 AS in_weight_s, 
     0 AS in_group_q,     0 AS in_group_w,       0 AS in_group_s, 
     0 AS in_buy_q,       0 AS in_buy_w,         0 AS in_buy_s, 
     0 AS out_group_q,    0 AS out_group_w,      0 AS out_group_s, 
     0 AS out_01_1_q,       0 AS out_01_1_w,         0 AS out_01_1_s, 
     0 AS out_02_q,       0 AS out_02_w,         0 AS out_02_s, 
     0 AS out_03_q,       0 AS out_03_w,         0 AS out_03_s, 
     0 AS out_04_q,       0 AS out_04_w,         0 AS out_04_s 
   FROM 
     gd_document doc 
     JOIN USR$BRD_INTMOVE head ON head.documentkey = doc.id 
     JOIN USR$BRD_INTMOVEline line ON line.masterkey = head.documentkey 
     LEFT JOIN inv_card c ON c.id = line.fromcardkey 
   WHERE 
     doc.documenttypekey = :intmove_doctype 
     AND doc.documentdate >= :datebegin 
     AND doc.documentdate <= :dateend 
     AND head.usr$fromcontactkey = :incub_ceh 
     AND head.usr$tocontactkey in (:broiler_ceh1, :broiler_ceh2, :broiler_ceh3) 
     AND c.goodkey IN (306180741,306180742,302298356) 

   UNION ALL 
   SELECT 
     CASE 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_42) AND (e.usr$gs_good = :good_chick) THEN 1 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_42) AND (e.usr$gs_good = :good_cock) THEN 2 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_140) AND (e.usr$gs_good = :good_chick) THEN 3 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_140) AND (e.usr$gs_good = :good_cock) THEN 4 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_43) AND (e.usr$gs_good = :good_chick) THEN 5 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_43) AND (e.usr$gs_good = :good_cock) THEN 6 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_130) AND (e.usr$gs_good = :good_chick) THEN 7 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_130) AND (e.usr$gs_good = :good_cock) THEN 8 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_1_130) AND (e.usr$gs_good = :good_chick) THEN 9 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_1_130) AND (e.usr$gs_good = :good_cock) THEN 10 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_180) AND (e.usr$gs_good = :good_chick) THEN 11 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_180) AND (e.usr$gs_good = :good_cock) THEN 12 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_broiler and e.usr$gs_department = :broiler_ceh1) THEN 13 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_broiler and e.usr$gs_department = :broiler_ceh2) THEN 14 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_broiler and e.usr$gs_department = :broiler_ceh3) THEN 15 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_parent) AND (e.usr$gs_good = :good_chick) THEN 16 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_parent) AND (e.usr$gs_good = :good_cock) THEN 17 
     END AS rec_counter, 
     0 AS begin_q,        0 AS begin_w,          0 AS begin_s, 
     0 AS in_incub_q,     0 AS in_incub_w,       e.debitncu AS in_incub_s, 
     0 AS in_weight_q, 
     0 AS in_weight_w, 
     0 AS in_weight_s, 
     0 AS in_group_q,     0 AS in_group_w,       0 AS in_group_s, 
     0 AS in_buy_q,       0 AS in_buy_w,         0 AS in_buy_s, 
     0 AS out_group_q,    0 AS out_group_w,      0 AS out_group_s, 
     0 AS out_01_1_q,       0 AS out_01_1_w,         0 AS out_01_1_s, 
     0 AS out_02_q,       0 AS out_02_w,         0 AS out_02_s, 
     0 AS out_03_q,       0 AS out_03_w,         0 AS out_03_s, 
     0 AS out_04_q,       0 AS out_04_w,         0 AS out_04_s 
   FROM 
     ac_entry e 
   WHERE 
     e.entrydate >= :datebegin 
     AND e.entrydate <= :dateend 
     AND e.accountkey = :acc113  
     AND e.transactionkey = :trkey_incub 
     AND e.usr$gs_good IN (306180741,306180742,302298356) 

   UNION ALL 
   /* Привес за выбранный период */ 
   SELECT 
     CASE 
       WHEN (c.usr$brd_agegroupkey = :age_42) AND (c.goodkey = :good_chick) THEN 1 
       WHEN (c.usr$brd_agegroupkey = :age_42) AND (c.goodkey = :good_cock) THEN 2 
       WHEN (c.usr$brd_agegroupkey = :age_140) AND (c.goodkey = :good_chick) THEN 3 
       WHEN (c.usr$brd_agegroupkey = :age_140) AND (c.goodkey = :good_cock) THEN 4 
       WHEN (c.usr$brd_agegroupkey = :age_43) AND (c.goodkey = :good_chick) THEN 5 
       WHEN (c.usr$brd_agegroupkey = :age_43) AND (c.goodkey = :good_cock) THEN 6 
       WHEN (c.usr$brd_agegroupkey = :age_130) AND (c.goodkey = :good_chick) THEN 7 
       WHEN (c.usr$brd_agegroupkey = :age_130) AND (c.goodkey = :good_cock) THEN 8 
       WHEN (c.usr$brd_agegroupkey = :age_1_130) AND (c.goodkey = :good_chick) THEN 9 
       WHEN (c.usr$brd_agegroupkey = :age_1_130) AND (c.goodkey = :good_cock) THEN 10 
       WHEN (c.usr$brd_agegroupkey = :age_180) AND (c.goodkey = :good_chick) THEN 11 
       WHEN (c.usr$brd_agegroupkey = :age_180) AND (c.goodkey = :good_cock) THEN 12 
       WHEN (c.usr$brd_agegroupkey = :age_broiler and con.id = :broiler_ceh1) THEN 13 
       WHEN (c.usr$brd_agegroupkey = :age_broiler and con.id = :broiler_ceh2) THEN 14 
       WHEN (c.usr$brd_agegroupkey = :age_broiler and con.id = :broiler_ceh3) THEN 15 
       WHEN (c.usr$brd_agegroupkey = :age_parent) AND (c.goodkey = :good_chick) THEN 16 
       WHEN (c.usr$brd_agegroupkey = :age_parent) AND (c.goodkey = :good_cock) THEN 17 
     END AS rec_counter, 
     0 AS begin_q,        0 AS begin_w,          0 AS begin_s, 
     0 AS in_incub_q,     0 AS in_incub_w,       0 AS in_incub_s, 
     0 AS in_weight_q, 
     line.USR$DELTAWEIGHT AS in_weight_w, 
     (line.USR$DELTAWEIGHT * p.usr$inv_costaccncu) AS in_weight_s, 
     0 AS in_group_q,     0 AS in_group_w,       0 AS in_group_s, 
     0 AS in_buy_q,       0 AS in_buy_w,         0 AS in_buy_s, 
     0 AS out_group_q,    0 AS out_group_w,      0 AS out_group_s, 
     0 AS out_01_1_q,       0 AS out_01_1_w,         0 AS out_01_1_s, 
     0 AS out_02_q,       0 AS out_02_w,         0 AS out_02_s, 
     0 AS out_03_q,       0 AS out_03_w,         0 AS out_03_s, 
     0 AS out_04_q,       0 AS out_04_w,         0 AS out_04_s 
   FROM      gd_document doc      JOIN USR$BRD_WEIGHTDOC head ON head.documentkey = doc.id      JOIN USR$BRD_WEIGHTDOCline line ON line.masterkey = head.documentkey      JOIN gd_contact con ON head.USR$MAINDEPTKEY = con.id      JOIN inv_card c ON c.id = line.tocardkey      LEFT JOIN USR$VBPF_BRD_GETPRICECOST_IDS(doc.documentdate, c.goodkey, c.usr$brd_agegroupkey, 0, 1,       iif(head.USR$MAINDEPTKEY = :broiler_ceh1, 360790255, iif(head.USR$MAINDEPTKEY = :broiler_ceh2, 360790256, iif(head.USR$MAINDEPTKEY = :broiler_ceh3, 709842634, -1))), :IDS ) p ON 1 = 1 
   WHERE      doc.documenttypekey = <RUID XID = "292135255" DBID = "1571403123"/>      AND doc.documentdate >= :datebegin      AND doc.documentdate <= :dateend      AND con.lb >= :com_lb 
     AND con.rb <= :com_rb 
     AND c.goodkey IN (306180741,306180742,302298356) 

   UNION ALL 
   SELECT 
     CASE 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_42) AND (e.usr$gs_good = :good_chick) THEN 1 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_42) AND (e.usr$gs_good = :good_cock) THEN 2 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_140) AND (e.usr$gs_good = :good_chick) THEN 3 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_140) AND (e.usr$gs_good = :good_cock) THEN 4 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_43) AND (e.usr$gs_good = :good_chick) THEN 5 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_43) AND (e.usr$gs_good = :good_cock) THEN 6 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_130) AND (e.usr$gs_good = :good_chick) THEN 7 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_130) AND (e.usr$gs_good = :good_cock) THEN 8 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_1_130) AND (e.usr$gs_good = :good_chick) THEN 9 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_1_130) AND (e.usr$gs_good = :good_cock) THEN 10 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_180) AND (e.usr$gs_good = :good_chick) THEN 11 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_180) AND (e.usr$gs_good = :good_cock) THEN 12 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_broiler and e.usr$gs_department = :broiler_ceh1) THEN 13 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_broiler and e.usr$gs_department = :broiler_ceh2) THEN 14 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_broiler and e.usr$gs_department = :broiler_ceh3) THEN 15 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_parent) AND (e.usr$gs_good = :good_chick) THEN 16 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_parent) AND (e.usr$gs_good = :good_cock) THEN 17 
     END AS rec_counter, 
     0 AS begin_q,        0 AS begin_w,          0 AS begin_s, 
     0 AS in_incub_q,     0 AS in_incub_w,       0 AS in_incub_s, 
     0 AS in_weight_q, 
     0 AS in_weight_w, 
     (e.debitncu) AS in_weight_s, 
     0 AS in_group_q,     0 AS in_group_w,       0 AS in_group_s, 
     0 AS in_buy_q,       0 AS in_buy_w,         0 AS in_buy_s, 
     0 AS out_group_q,    0 AS out_group_w,      0 AS out_group_s, 
     0 AS out_01_1_q,       0 AS out_01_1_w,         0 AS out_01_1_s, 
     0 AS out_02_q,       0 AS out_02_w,         0 AS out_02_s, 
     0 AS out_03_q,       0 AS out_03_w,         0 AS out_03_s, 
     0 AS out_04_q,       0 AS out_04_w,         0 AS out_04_s 
   FROM 
     ac_entry e 
       join ac_entry e1 ON e.recordkey = e1.recordkey and e.accountpart <> e1.accountpart and e.accountkey <> e1.accountkey and e.accountpart = 'D'    WHERE 
     e.entrydate >= :datebegin 
     AND e.entrydate <= :dateend 
     AND e.accountkey = :acc113  
     AND e.transactionkey in (:trkey) 
     AND e.usr$gs_good IN (306180741,306180742,302298356) 

   UNION ALL 
   SELECT 
     CASE 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_42) AND (e.usr$gs_good = :good_chick) THEN 1 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_42) AND (e.usr$gs_good = :good_cock) THEN 2 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_140) AND (e.usr$gs_good = :good_chick) THEN 3 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_140) AND (e.usr$gs_good = :good_cock) THEN 4 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_43) AND (e.usr$gs_good = :good_chick) THEN 5 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_43) AND (e.usr$gs_good = :good_cock) THEN 6 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_130) AND (e.usr$gs_good = :good_chick) THEN 7 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_130) AND (e.usr$gs_good = :good_cock) THEN 8 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_1_130) AND (e.usr$gs_good = :good_chick) THEN 9 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_1_130) AND (e.usr$gs_good = :good_cock) THEN 10 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_180) AND (e.usr$gs_good = :good_chick) THEN 11 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_180) AND (e.usr$gs_good = :good_cock) THEN 12 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_broiler and e.usr$gs_department = :broiler_ceh1) THEN 13 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_broiler and e.usr$gs_department = :broiler_ceh2) THEN 14 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_broiler and e.usr$gs_department = :broiler_ceh3) THEN 15 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_parent) AND (e.usr$gs_good = :good_chick) THEN 16 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_parent) AND (e.usr$gs_good = :good_cock) THEN 17 
     END AS rec_counter, 
     0 AS begin_q,        0 AS begin_w,          0 AS begin_s, 
     0 AS in_incub_q,     0 AS in_incub_w,       0 AS in_incub_s, 
     0 AS in_weight_q, 
     0 AS in_weight_w, 
     0 AS in_weight_s, 
     0 AS in_group_q,     0 AS in_group_w,       0 AS in_group_s, 
     0 AS in_buy_q,       0 AS in_buy_w,         (e.debitncu - e.creditncu) AS in_buy_s, 
     0 AS out_group_q,    0 AS out_group_w,      0 AS out_group_s, 
     0 AS out_01_1_q,       0 AS out_01_1_w,         0 AS out_01_1_s, 
     0 AS out_02_q,       0 AS out_02_w,         0 AS out_02_s, 
     0 AS out_03_q,       0 AS out_03_w,         0 AS out_03_s, 
     0 AS out_04_q,       0 AS out_04_w,         0 AS out_04_s 
   FROM 
     ac_entry e 
   WHERE 
     e.entrydate >= :datebegin 
     AND e.entrydate <= :dateend 
     AND e.accountkey = :acc113  
     AND e.transactionkey in (:trkey_mat) 
     AND e.usr$gs_good IN (306180741,306180742,302298356) 

   UNION ALL 
   /* Приход из другой группы */ 
   SELECT 
     CASE 
       WHEN (c_to.usr$brd_agegroupkey = :age_42) AND (c_to.goodkey = :good_chick) THEN 1 
       WHEN (c_to.usr$brd_agegroupkey = :age_42) AND (c_to.goodkey = :good_cock) THEN 2 
       WHEN (c_to.usr$brd_agegroupkey = :age_140) AND (c_to.goodkey = :good_chick) THEN 3 
       WHEN (c_to.usr$brd_agegroupkey = :age_140) AND (c_to.goodkey = :good_cock) THEN 4 
       WHEN (c_to.usr$brd_agegroupkey = :age_43) AND (c_to.goodkey = :good_chick) THEN 5 
       WHEN (c_to.usr$brd_agegroupkey = :age_43) AND (c_to.goodkey = :good_cock) THEN 6 
       WHEN (c_to.usr$brd_agegroupkey = :age_130) AND (c_to.goodkey = :good_chick) THEN 7 
       WHEN (c_to.usr$brd_agegroupkey = :age_130) AND (c_to.goodkey = :good_cock) THEN 8 
       WHEN (c_to.usr$brd_agegroupkey = :age_1_130) AND (c_to.goodkey = :good_chick) THEN 9 
       WHEN (c_to.usr$brd_agegroupkey = :age_1_130) AND (c_to.goodkey = :good_cock) THEN 10 
       WHEN (c_to.usr$brd_agegroupkey = :age_180) AND (c_to.goodkey = :good_chick) THEN 11 
       WHEN (c_to.usr$brd_agegroupkey = :age_180) AND (c_to.goodkey = :good_cock) THEN 12 
       WHEN (c_to.usr$brd_agegroupkey = :age_broiler and con.id = :broiler_ceh1) THEN 13 
       WHEN (c_to.usr$brd_agegroupkey = :age_broiler and con.id = :broiler_ceh2) THEN 14 
       WHEN (c_to.usr$brd_agegroupkey = :age_broiler and con.id = :broiler_ceh2) THEN 15 
       WHEN (c_to.usr$brd_agegroupkey = :age_parent) AND (c_to.goodkey = :good_chick) THEN 16 
       WHEN (c_to.usr$brd_agegroupkey = :age_parent) AND (c_to.goodkey = :good_cock) THEN 17 
     END AS rec_counter, 
     0 AS begin_q,        0 AS begin_w,          0 AS begin_s, 
     0 AS in_incub_q,     0 AS in_incub_w,       0 AS in_incub_s, 
     0 AS in_weight_q,    0 AS in_weight_w,      0 AS in_weight_s, 
     line.quantity AS in_group_q, 
     line.usr$newweight AS in_group_w, 
     (line.usr$sumncu) AS in_group_s, 
     0 AS in_buy_q,       0 AS in_buy_w,         0 AS in_buy_s, 
     0 AS out_group_q,    0 AS out_group_w,      0 AS out_group_s, 
     0 AS out_01_1_q,       0 AS out_01_1_w,         0 AS out_01_1_s, 
     0 AS out_02_q,       0 AS out_02_w,         0 AS out_02_s, 
     0 AS out_03_q,       0 AS out_03_w,         0 AS out_03_s, 
     0 AS out_04_q,       0 AS out_04_w,         0 AS out_04_s 
   FROM 
     gd_document doc 
     JOIN usr$brd_agedoc head ON head.documentkey = doc.id 
     JOIN usr$brd_agedocline line ON line.masterkey = head.documentkey 
     LEFT JOIN inv_card c_to ON c_to.id = line.tocardkey 
     LEFT JOIN gd_contact con ON con.id = head.usr$deptkey 
   WHERE 
     doc.documenttypekey = :age_doctype 
     AND doc.documentdate >= :datebegin 
     AND doc.documentdate <= :dateend 
     AND con.lb >= :com_lb 
     AND con.rb <= :com_rb 
     AND c_to.goodkey IN (306180741,306180742,302298356) 

   UNION ALL 
   SELECT 
     CASE 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_42) AND (e.usr$gs_good = :good_chick) THEN 1 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_42) AND (e.usr$gs_good = :good_cock) THEN 2 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_140) AND (e.usr$gs_good = :good_chick) THEN 3 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_140) AND (e.usr$gs_good = :good_cock) THEN 4 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_43) AND (e.usr$gs_good = :good_chick) THEN 5 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_43) AND (e.usr$gs_good = :good_cock) THEN 6 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_130) AND (e.usr$gs_good = :good_chick) THEN 7 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_130) AND (e.usr$gs_good = :good_cock) THEN 8 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_1_130) AND (e.usr$gs_good = :good_chick) THEN 9 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_1_130) AND (e.usr$gs_good = :good_cock) THEN 10 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_180) AND (e.usr$gs_good = :good_chick) THEN 11 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_180) AND (e.usr$gs_good = :good_cock) THEN 12 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_broiler and e.usr$gs_department = :broiler_ceh1) THEN 13 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_broiler and e.usr$gs_department = :broiler_ceh2) THEN 14 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_broiler and e.usr$gs_department = :broiler_ceh3) THEN 15 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_parent) AND (e.usr$gs_good = :good_chick) THEN 16 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_parent) AND (e.usr$gs_good = :good_cock) THEN 17 
     END AS rec_counter, 
     0 AS begin_q,        0 AS begin_w,          0 AS begin_s, 
     0 AS in_incub_q,     0 AS in_incub_w,       0 AS in_incub_s, 
     0 AS in_weight_q,    0 AS in_weight_w,      0 AS in_weight_s, 
     0 AS in_group_q, 
     0 AS in_group_w, 
     (e.debitncu) AS in_group_s, 
     0 AS in_buy_q,       0 AS in_buy_w,         0 AS in_buy_s, 
     0 AS out_group_q,    0 AS out_group_w,      0 AS out_group_s, 
     0 AS out_01_1_q,       0 AS out_01_1_w,         0 AS out_01_1_s, 
     0 AS out_02_q,       0 AS out_02_w,         0 AS out_02_s, 
     0 AS out_03_q,       0 AS out_03_w,         0 AS out_03_s, 
     0 AS out_04_q,       0 AS out_04_w,         0 AS out_04_s 
   FROM 
     ac_entry e 
       join ac_entry e1 ON e.recordkey = e1.recordkey and e.accountkey = e1.accountkey and e.accountpart <> e1.accountpart 
   WHERE 
     e.entrydate >= :datebegin AND e.debitncu <> 0 
     AND e.entrydate <= :dateend 
     AND e.accountkey = :acc113  
     AND e.transactionkey in (:trkey, :trkey_1, :trkey_2) 
     AND e.usr$gs_good IN (306180741,306180742,302298356) 

   UNION ALL 
   SELECT 
     CASE 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_42) AND (e.usr$gs_good = :good_chick) THEN 1 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_42) AND (e.usr$gs_good = :good_cock) THEN 2 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_140) AND (e.usr$gs_good = :good_chick) THEN 3 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_140) AND (e.usr$gs_good = :good_cock) THEN 4 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_43) AND (e.usr$gs_good = :good_chick) THEN 5 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_43) AND (e.usr$gs_good = :good_cock) THEN 6 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_130) AND (e.usr$gs_good = :good_chick) THEN 7 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_130) AND (e.usr$gs_good = :good_cock) THEN 8 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_1_130) AND (e.usr$gs_good = :good_chick) THEN 9 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_1_130) AND (e.usr$gs_good = :good_cock) THEN 10 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_180) AND (e.usr$gs_good = :good_chick) THEN 11 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_180) AND (e.usr$gs_good = :good_cock) THEN 12 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_broiler and e.usr$gs_department = :broiler_ceh1) THEN 13 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_broiler and e.usr$gs_department = :broiler_ceh2) THEN 14 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_broiler and e.usr$gs_department = :broiler_ceh3) THEN 15 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_parent) AND (e.usr$gs_good = :good_chick) THEN 16 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_parent) AND (e.usr$gs_good = :good_cock) THEN 17 
     END AS rec_counter, 
     0 AS begin_q,        0 AS begin_w,          0 AS begin_s, 
     0 AS in_incub_q,     0 AS in_incub_w,       0 AS in_incub_s, 
     0 AS in_weight_q,    0 AS in_weight_w,      0 AS in_weight_s, 
     0 AS in_group_q, 
     0 AS in_group_w, 
     0 AS in_group_s, 
     0 AS in_buy_q,       0 AS in_buy_w,         0 AS in_buy_s, 
     0 AS out_group_q,    0 AS out_group_w,      e.creditncu AS out_group_s, 
     0 AS out_01_1_q,       0 AS out_01_1_w,         0 AS out_01_1_s, 
     0 AS out_02_q,       0 AS out_02_w,         0 AS out_02_s, 
     0 AS out_03_q,       0 AS out_03_w,         0 AS out_03_s, 
     0 AS out_04_q,       0 AS out_04_w,         0 AS out_04_s 
   FROM 
     ac_entry e 
       join ac_entry e1 ON e.recordkey = e1.recordkey and e.accountkey = e1.accountkey and e.accountpart <> e1.accountpart 
   WHERE 
     e.entrydate >= :datebegin AND e.creditncu <> 0 
     AND e.entrydate <= :dateend 
     AND e.accountkey = :acc113  
     AND e.transactionkey in (:trkey, :trkey_1, :trkey_2) 
     AND e.usr$gs_good IN (306180741,306180742,302298356) 

   UNION ALL 

   /* Покупка */ 
   SELECT  
     CASE  
       WHEN (c_to.usr$brd_agegroupkey = :age_42) AND (c_to.goodkey = :good_chick) THEN 1  
       WHEN (c_to.usr$brd_agegroupkey = :age_42) AND (c_to.goodkey = :good_cock) THEN 2  
       WHEN (c_to.usr$brd_agegroupkey = :age_140) AND (c_to.goodkey = :good_chick) THEN 3  
       WHEN (c_to.usr$brd_agegroupkey = :age_140) AND (c_to.goodkey = :good_cock) THEN 4  
       WHEN (c_to.usr$brd_agegroupkey = :age_43) AND (c_to.goodkey = :good_chick) THEN 5  
       WHEN (c_to.usr$brd_agegroupkey = :age_43) AND (c_to.goodkey = :good_cock) THEN 6  
       WHEN (c_to.usr$brd_agegroupkey = :age_130) AND (c_to.goodkey = :good_chick) THEN 7  
       WHEN (c_to.usr$brd_agegroupkey = :age_130) AND (c_to.goodkey = :good_cock) THEN 8  
       WHEN (c_to.usr$brd_agegroupkey = :age_1_130) AND (c_to.goodkey = :good_chick) THEN 9 
       WHEN (c_to.usr$brd_agegroupkey = :age_1_130) AND (c_to.goodkey = :good_cock) THEN 10 
       WHEN (c_to.usr$brd_agegroupkey = :age_180) AND (c_to.goodkey = :good_chick) THEN 11 
       WHEN (c_to.usr$brd_agegroupkey = :age_180) AND (c_to.goodkey = :good_cock) THEN 12 
       WHEN (c_to.usr$brd_agegroupkey = :age_broiler and con.id = :broiler_ceh1) THEN 13 
       WHEN (c_to.usr$brd_agegroupkey = :age_broiler and con.id = :broiler_ceh2) THEN 14 
       WHEN (c_to.usr$brd_agegroupkey = :age_broiler and con.id = :broiler_ceh3) THEN 15 
       WHEN (c_to.usr$brd_agegroupkey = :age_parent) AND (c_to.goodkey = :good_chick) THEN 16 
       WHEN (c_to.usr$brd_agegroupkey = :age_parent) AND (c_to.goodkey = :good_cock) THEN 17 
     END AS rec_counter,  
     0 AS begin_q,        0 AS begin_w,          0 AS begin_s,  
     0 AS in_incub_q,     0 AS in_incub_w,       0 AS in_incub_s,  
     0 AS in_weight_q,    0 AS in_weight_w,      0 AS in_weight_s,  
     0 AS in_group_q,     0 AS in_group_w,       0 AS in_group_s, 
     line.quantity AS in_buy_q,  
     line.usr$weight AS in_buy_w,  
     (CAST(line.quantity * c_to.usr$inv_costaccncu as NUMERIC(15, 4))) AS in_buy_s,  
     0 AS out_group_q,    0 AS out_group_w,      0 AS out_group_s,  
     0 AS out_01_1_q,       0 AS out_01_1_w,         0 AS out_01_1_s, 
     0 AS out_02_q,       0 AS out_02_w,         0 AS out_02_s,  
     0 AS out_03_q,       0 AS out_03_w,         0 AS out_03_s,  
     0 AS out_04_q,       0 AS out_04_w,         0 AS out_04_s  
   FROM  
     gd_document doc  
     JOIN usr$inv_addwbill head ON head.documentkey = doc.id 
     JOIN usr$inv_addwbillline line ON line.masterkey = head.documentkey 
     LEFT JOIN inv_card c_to ON c_to.id = line.fromcardkey 
     LEFT JOIN gd_contact con ON con.id = head.usr$deptkey  
   WHERE  
     doc.documenttypekey IN (:addwbill_doctype, :addwbill_import_doctype) 
     AND doc.documentdate >= :datebegin  
     AND doc.documentdate <= :dateend  
     AND con.lb >= :com_lb  
     AND con.rb <= :com_rb  
     AND c_to.goodkey IN (306180741,306180742,302298356) 

   UNION ALL 
   /* Покупка */ 
   SELECT  
     CASE  
       WHEN (c_to.usr$brd_agegroupkey = :age_42) AND (c_to.goodkey = :good_chick) THEN 1  
       WHEN (c_to.usr$brd_agegroupkey = :age_42) AND (c_to.goodkey = :good_cock) THEN 2  
       WHEN (c_to.usr$brd_agegroupkey = :age_140) AND (c_to.goodkey = :good_chick) THEN 3  
       WHEN (c_to.usr$brd_agegroupkey = :age_140) AND (c_to.goodkey = :good_cock) THEN 4  
       WHEN (c_to.usr$brd_agegroupkey = :age_43) AND (c_to.goodkey = :good_chick) THEN 5  
       WHEN (c_to.usr$brd_agegroupkey = :age_43) AND (c_to.goodkey = :good_cock) THEN 6  
       WHEN (c_to.usr$brd_agegroupkey = :age_130) AND (c_to.goodkey = :good_chick) THEN 7  
       WHEN (c_to.usr$brd_agegroupkey = :age_130) AND (c_to.goodkey = :good_cock) THEN 8  
       WHEN (c_to.usr$brd_agegroupkey = :age_1_130) AND (c_to.goodkey = :good_chick) THEN 9 
       WHEN (c_to.usr$brd_agegroupkey = :age_1_130) AND (c_to.goodkey = :good_cock) THEN 10 
       WHEN (c_to.usr$brd_agegroupkey = :age_180) AND (c_to.goodkey = :good_chick) THEN 11 
       WHEN (c_to.usr$brd_agegroupkey = :age_180) AND (c_to.goodkey = :good_cock) THEN 12 
       WHEN (c_to.usr$brd_agegroupkey = :age_broiler and head.usr$deptkey = :broiler_ceh1) THEN 13 
       WHEN (c_to.usr$brd_agegroupkey = :age_broiler and head.usr$deptkey = :broiler_ceh2) THEN 14 
       WHEN (c_to.usr$brd_agegroupkey = :age_broiler and head.usr$deptkey = :broiler_ceh3) THEN 15 
       WHEN (c_to.usr$brd_agegroupkey = :age_parent) AND (c_to.goodkey = :good_chick) THEN 16 
       WHEN (c_to.usr$brd_agegroupkey = :age_parent) AND (c_to.goodkey = :good_cock) THEN 17 
     END AS rec_counter,  
     0 AS begin_q,        0 AS begin_w,          0 AS begin_s,  
     0 AS in_incub_q,     0 AS in_incub_w,       0 AS in_incub_s,  
     0 AS in_weight_q,    0 AS in_weight_w,      0 AS in_weight_s,  
     0 AS in_group_q,     0 AS in_group_w,       0 AS in_group_s, 
     0 AS in_buy_q,  
     0 AS in_buy_w,  
     (line.usr$sumncu) AS in_buy_s,  
     0 AS out_group_q,    0 AS out_group_w,      0 AS out_group_s,  
     0 AS out_01_1_q,       0 AS out_01_1_w,         0 AS out_01_1_s, 
     0 AS out_02_q,       0 AS out_02_w,         0 AS out_02_s,  
     0 AS out_03_q,       0 AS out_03_w,         0 AS out_03_s,  
     0 AS out_04_q,       0 AS out_04_w,         0 AS out_04_s  
   FROM  
     gd_document doc  
     JOIN usr$inv_newcost head ON head.documentkey = doc.id 
     JOIN usr$inv_newcostline line ON line.masterkey = head.documentkey 
     LEFT JOIN inv_card c_to ON c_to.id = line.fromcardkey 
     LEFT JOIN gd_contact con ON con.id = head.usr$deptkey  
   WHERE  
     doc.documenttypekey IN (:auto_doctype) 
     AND doc.documentdate >= :datebegin  
     AND doc.documentdate <= :dateend  
     AND con.lb >= :com_lb  
     AND con.rb <= :com_rb  
     AND c_to.goodkey IN (306180741,306180742,302298356) 

   UNION ALL 

   /* Расход в другую группу */ 
   SELECT 
     CASE 
       WHEN (c_from.usr$brd_agegroupkey = :age_42) AND (c_from.goodkey = :good_chick) THEN 1 
       WHEN (c_from.usr$brd_agegroupkey = :age_42) AND (c_from.goodkey = :good_cock) THEN 2 
       WHEN (c_from.usr$brd_agegroupkey = :age_140) AND (c_from.goodkey = :good_chick) THEN 3 
       WHEN (c_from.usr$brd_agegroupkey = :age_140) AND (c_from.goodkey = :good_cock) THEN 4 
       WHEN (c_from.usr$brd_agegroupkey = :age_43) AND (c_from.goodkey = :good_chick) THEN 5 
       WHEN (c_from.usr$brd_agegroupkey = :age_43) AND (c_from.goodkey = :good_cock) THEN 6 
       WHEN (c_from.usr$brd_agegroupkey = :age_130) AND (c_from.goodkey = :good_chick) THEN 7 
       WHEN (c_from.usr$brd_agegroupkey = :age_130) AND (c_from.goodkey = :good_cock) THEN 8 
       WHEN (c_from.usr$brd_agegroupkey = :age_1_130) AND (c_from.goodkey = :good_chick) THEN 9 
       WHEN (c_from.usr$brd_agegroupkey = :age_1_130) AND (c_from.goodkey = :good_cock) THEN 10 
       WHEN (c_from.usr$brd_agegroupkey = :age_180) AND (c_from.goodkey = :good_chick) THEN 11 
       WHEN (c_from.usr$brd_agegroupkey = :age_180) AND (c_from.goodkey = :good_cock) THEN 12 
       WHEN (c_from.usr$brd_agegroupkey = :age_broiler and con.id = :broiler_ceh1) THEN 13 
       WHEN (c_from.usr$brd_agegroupkey = :age_broiler and con.id = :broiler_ceh2) THEN 14 
       WHEN (c_from.usr$brd_agegroupkey = :age_broiler and con.id = :broiler_ceh3) THEN 15 
       WHEN (c_from.usr$brd_agegroupkey = :age_parent) AND (c_from.goodkey = :good_chick) THEN 16 
       WHEN (c_from.usr$brd_agegroupkey = :age_parent) AND (c_from.goodkey = :good_cock) THEN 17 
     END AS rec_counter, 
     0 AS begin_q,        0 AS begin_w,          0 AS begin_s, 
     0 AS in_incub_q,     0 AS in_incub_w,       0 AS in_incub_s, 
     0 AS in_weight_q,    0 AS in_weight_w,      0 AS in_weight_s, 
     0 AS in_group_q,     0 AS in_group_w,       0 AS in_group_s, 
     0 AS in_buy_q,       0 AS in_buy_w,         0 AS in_buy_s, 
     line.quantity AS out_group_q, 
     line.usr$oldweight AS out_group_w, 
     (line.usr$sumncu) AS out_group_s, 
     0 AS out_01_1_q,       0 AS out_01_1_w,         0 AS out_01_1_s, 
     0 AS out_02_q,       0 AS out_02_w,         0 AS out_02_s, 
     0 AS out_03_q,       0 AS out_03_w,         0 AS out_03_s, 
     0 AS out_04_q,       0 AS out_04_w,         0 AS out_04_s 
   FROM 
     gd_document doc 
     JOIN usr$brd_agedoc head ON head.documentkey = doc.id 
     JOIN usr$brd_agedocline line ON line.masterkey = head.documentkey 
     LEFT JOIN inv_card c_from ON c_from.id = line.fromcardkey 
     LEFT JOIN gd_contact con ON con.id = head.usr$deptkey 
   WHERE 
     doc.documenttypekey = :age_doctype 
     AND doc.documentdate >= :datebegin 
     AND doc.documentdate <= :dateend 
     AND con.lb >= :com_lb 
     AND con.rb <= :com_rb 
     AND c_from.goodkey IN (306180741,306180742,302298356) 

   UNION ALL 

   /* Расход за выбранный период */ 
   SELECT 
     CASE 
       WHEN (c.usr$brd_agegroupkey = :age_42) AND (c.goodkey = :good_chick) THEN 1 
       WHEN (c.usr$brd_agegroupkey = :age_42) AND (c.goodkey = :good_cock) THEN 2 
       WHEN (c.usr$brd_agegroupkey = :age_140) AND (c.goodkey = :good_chick) THEN 3 
       WHEN (c.usr$brd_agegroupkey = :age_140) AND (c.goodkey = :good_cock) THEN 4 
       WHEN (c.usr$brd_agegroupkey = :age_43) AND (c.goodkey = :good_chick) THEN 5 
       WHEN (c.usr$brd_agegroupkey = :age_43) AND (c.goodkey = :good_cock) THEN 6 
       WHEN (c.usr$brd_agegroupkey = :age_130) AND (c.goodkey = :good_chick) THEN 7 
       WHEN (c.usr$brd_agegroupkey = :age_130) AND (c.goodkey = :good_cock) THEN 8 
       WHEN (c.usr$brd_agegroupkey = :age_1_130) AND (c.goodkey = :good_chick) THEN 9 
       WHEN (c.usr$brd_agegroupkey = :age_1_130) AND (c.goodkey = :good_cock) THEN 10 
       WHEN (c.usr$brd_agegroupkey = :age_180) AND (c.goodkey = :good_chick) THEN 11 
       WHEN (c.usr$brd_agegroupkey = :age_180) AND (c.goodkey = :good_cock) THEN 12 
       WHEN (c.usr$brd_agegroupkey = :age_broiler and con.id = :broiler_ceh1) THEN 13 
       WHEN (c.usr$brd_agegroupkey = :age_broiler and con.id = :broiler_ceh2) THEN 14 
       WHEN (c.usr$brd_agegroupkey = :age_broiler and con.id = :broiler_ceh3) THEN 15 
       WHEN (c.usr$brd_agegroupkey = :age_parent) AND (c.goodkey = :good_chick) THEN 16 
       WHEN (c.usr$brd_agegroupkey = :age_parent) AND (c.goodkey = :good_cock) THEN 17 
     END AS rec_counter, 
     0 AS begin_q,        0 AS begin_w,          0 AS begin_s, 
     0 AS in_incub_q,     0 AS in_incub_w,       0 AS in_incub_s, 
     0 AS in_weight_q,    0 AS in_weight_w,      0 AS in_weight_s, 
     0 AS in_group_q,     0 AS in_group_w,       0 AS in_group_s, 
     0 AS in_buy_q,       0 AS in_buy_w,         0 AS in_buy_s, 
     0 AS out_group_q,    0 AS out_group_w,      0 AS out_group_s, 
     IIF(head.usr$reasonkey = :out_reason_01 and head.USR$TOCONTACTKEY in (:ceh1, :ceh2, :ceh_new), line.quantity, 0) AS out_01_1_q, 
     IIF(head.usr$reasonkey = :out_reason_01 and head.USR$TOCONTACTKEY in (:ceh1, :ceh2, :ceh_new), line.usr$accweight, 0) AS out_01_1_w, 
     IIF(head.usr$reasonkey = :out_reason_01 and head.USR$TOCONTACTKEY in (:ceh1, :ceh2, :ceh_new), iif((c.usr$brd_agegroupkey = :age_parent) AND ((c.goodkey = :good_chick) OR (c.goodkey = :good_cock)), line.quantity, line.usr$accweight) * CAST(p.usr$inv_costaccncu as NUMERIC(15, 4)), 0) AS out_01_1_s, 
     IIF(head.usr$reasonkey = :out_reason_02, line.quantity, 0) AS out_02_q, 
     IIF(head.usr$reasonkey = :out_reason_02, line.usr$accweight, 0) AS out_02_w, 
     IIF(head.usr$reasonkey = :out_reason_02, iif((c.usr$brd_agegroupkey = :age_parent) AND ((c.goodkey = :good_chick) OR (c.goodkey = :good_cock)), line.quantity, line.usr$accweight) * CAST(p.usr$inv_costaccncu as NUMERIC(15, 4)), 0) AS out_02_s, 
     IIF(head.usr$reasonkey = :out_reason_03 or head.usr$reasonkey = :out_reason_03_1, line.quantity, 0) AS out_03_q, 
     IIF(head.usr$reasonkey = :out_reason_03 or head.usr$reasonkey = :out_reason_03_1, line.usr$accweight, 0) AS out_03_w, 
     IIF(head.usr$reasonkey = :out_reason_03 or head.usr$reasonkey = :out_reason_03_1, iif((c.usr$brd_agegroupkey = :age_parent) AND ((c.goodkey = :good_chick) OR (c.goodkey = :good_cock)), line.quantity, line.usr$accweight) * CAST(p.usr$inv_costaccncu as NUMERIC(15, 4)), 0) AS out_03_s, 
     IIF(head.usr$reasonkey = :out_reason_04 or head.usr$reasonkey = :out_reason_05 , line.quantity, 0) AS out_04_q, 
     IIF(head.usr$reasonkey = :out_reason_04 or head.usr$reasonkey = :out_reason_05, line.usr$accweight, 0) AS out_04_w, 
     IIF(head.usr$reasonkey = :out_reason_04 or head.usr$reasonkey = :out_reason_05, iif((c.usr$brd_agegroupkey = :age_parent) AND ((c.goodkey = :good_chick) OR (c.goodkey = :good_cock)), line.quantity, line.usr$accweight) * CAST(p1.usr$inv_costaccncu as NUMERIC(15, 4)), 0) AS out_04_s 
   FROM 
     gd_document doc 
     JOIN usr$brd_intmove head ON head.documentkey = doc.id 
     JOIN usr$brd_intmoveline line ON line.masterkey = head.documentkey 
     LEFT JOIN inv_card c ON c.id = line.tocardkey 
     LEFT JOIN gd_contact con ON con.id = head.usr$fromcontactkey 
     LEFT JOIN USR$VBPF_BRD_GETPRICECOST_IDS(doc.documentdate, c.goodkey, c.usr$brd_agegroupkey, 0, 0, iif(head.usr$fromcontactkey = :broiler_ceh1, 360790255, iif(head.usr$fromcontactkey = :broiler_ceh2, 360790256, iif(head.usr$fromcontactkey = :broiler_ceh3, 709842634, -1))), :IDS) p ON 1 = 1 
     LEFT JOIN USR$VBPF_BRD_GETPRICECOST_IDS(doc.documentdate, c.goodkey, c.usr$brd_agegroupkey, 1, 0, iif(head.usr$fromcontactkey = :broiler_ceh1, 360790255, iif(head.usr$fromcontactkey = :broiler_ceh2, 360790256, iif(head.usr$fromcontactkey = :broiler_ceh3, 709842634, -1))), :IDS) p1 ON 1 = 1 
   WHERE 
     doc.documenttypekey = :intmove_doctype 
     AND doc.documentdate >= :datebegin 
     AND doc.documentdate <= :dateend 
     AND con.lb >= :com_lb 
     AND con.rb <= :com_rb 
     AND c.goodkey IN (306180741,306180742,302298356) 

   UNION ALL 
   SELECT 
     CASE 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_42) AND (e.usr$gs_good = :good_chick) THEN 1 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_42) AND (e.usr$gs_good = :good_cock) THEN 2 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_140) AND (e.usr$gs_good = :good_chick) THEN 3 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_140) AND (e.usr$gs_good = :good_cock) THEN 4 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_43) AND (e.usr$gs_good = :good_chick) THEN 5 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_43) AND (e.usr$gs_good = :good_cock) THEN 6 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_130) AND (e.usr$gs_good = :good_chick) THEN 7 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_130) AND (e.usr$gs_good = :good_cock) THEN 8 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_1_130) AND (e.usr$gs_good = :good_chick) THEN 9 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_1_130) AND (e.usr$gs_good = :good_cock) THEN 10 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_180) AND (e.usr$gs_good = :good_chick) THEN 11 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_180) AND (e.usr$gs_good = :good_cock) THEN 12 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_broiler and e.usr$gs_department = :broiler_ceh1) THEN 13 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_broiler and e.usr$gs_department = :broiler_ceh2) THEN 14 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_broiler and e.usr$gs_department = :broiler_ceh3) THEN 15 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_parent) AND (e.usr$gs_good = :good_chick) THEN 16 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_parent) AND (e.usr$gs_good = :good_cock) THEN 17 
     END AS rec_counter, 
     0 AS begin_q,        0 AS begin_w,          0 AS begin_s, 
     0 AS in_incub_q,     0 AS in_incub_w,       0 AS in_incub_s, 
     0 AS in_weight_q, 
     0 AS in_weight_w, 
     0 AS in_weight_s, 
     0 AS in_group_q,     0 AS in_group_w,       0 AS in_group_s, 
     0 AS in_buy_q,       0 AS in_buy_w,         0 AS in_buy_s, 
     0 AS out_group_q,    0 AS out_group_w,      0 AS out_group_s, 
     0 AS out_01_1_q,     0 AS out_01_1_w,     iif(e1.accountkey <> :acc902 and e1.usr$gs_department = :ceh_new, e.creditncu, 0) AS out_01_1_s, 
     0 AS out_02_q,       0 AS out_02_w,         0 AS out_02_s, 
     0 AS out_03_q,       0 AS out_03_w,  iif(e1.accountkey = <RUID XID = "424976600" DBID = "1716733059"/>, e.creditncu, 0) AS out_003_s, 
     0 AS out_04_q,       0 AS out_04_w,         0 AS out_04_s 
   FROM 
     ac_entry e LEFT JOIN ac_entry e1 ON e.recordkey = e1.recordkey and e.accountpart = 'C' and e1.accountpart = 'D'
   WHERE 
     e.entrydate >= :datebegin 
     AND e.entrydate <= :dateend 
     AND e.accountkey = :acc113 AND e.creditncu <> 0  
     AND e.transactionkey in (:trkey, :trkey_1, :trkey_2) 
     AND e.usr$gs_good IN (306180741,306180742,302298356) 

   UNION ALL 
   SELECT 
     CASE 
       WHEN (e.usr$gs_department = :broiler_ceh1) THEN 13 
       WHEN (e.usr$gs_department = :broiler_ceh2) THEN 14 
       WHEN (e.usr$gs_department = :broiler_ceh3) THEN 15 
     END AS rec_counter, 
     0 AS begin_q,        0 AS begin_w,          0 AS begin_s, 
     0 AS in_incub_q,     0 AS in_incub_w,       0 AS in_incub_s, 
     0 AS in_weight_q, 
     0 AS in_weight_w, 
     0 AS in_weight_s, 
     0 AS in_group_q,     0 AS in_group_w,       0 AS in_group_s, 
     0 AS in_buy_q,       0 AS in_buy_w,         0 AS in_buy_s, 
     0 AS out_group_q,    0 AS out_group_w,      0 AS out_group_s, 
     0 AS out_01_1_q,     0 AS out_01_1_w,       0 AS out_01_1_s, 
     0 AS out_02_q,       0 AS out_02_w,         0 AS out_02_s, 
     0 AS out_03_q,       0 AS out_03_w,         e.creditncu  AS out_03_s, 
     0 AS out_04_q,       0 AS out_04_w,         0 AS out_04_s 
   FROM 
     ac_entry e LEFT JOIN ac_entry e1 ON e.recordkey = e1.recordkey and e.accountpart = 'C' and e1.accountpart = 'D'
   WHERE 
     e.entrydate >= :datebegin 
     AND e.entrydate <= :dateend 
     AND e.accountkey = :acc113 AND e.creditncu <> 0  
     AND e.transactionkey in (<RUID XID = "769919496" DBID = "991893850"/>, <RUID XID = "770716337" DBID = "991893850"/>) 
     AND e.usr$gs_good IN (306180741,306180742,302298356) 

   UNION ALL 
   SELECT 
     CASE 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_42) AND (e.usr$gs_good = :good_chick) THEN 1 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_42) AND (e.usr$gs_good = :good_cock) THEN 2 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_140) AND (e.usr$gs_good = :good_chick) THEN 3 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_140) AND (e.usr$gs_good = :good_cock) THEN 4 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_43) AND (e.usr$gs_good = :good_chick) THEN 5 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_43) AND (e.usr$gs_good = :good_cock) THEN 6 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_130) AND (e.usr$gs_good = :good_chick) THEN 7 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_130) AND (e.usr$gs_good = :good_cock) THEN 8 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_1_130) AND (e.usr$gs_good = :good_chick) THEN 9 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_1_130) AND (e.usr$gs_good = :good_cock) THEN 10 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_180) AND (e.usr$gs_good = :good_chick) THEN 11 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_180) AND (e.usr$gs_good = :good_cock) THEN 12 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_broiler and e.usr$gs_department = :broiler_ceh1) THEN 13 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_broiler and e.usr$gs_department = :broiler_ceh2) THEN 14 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_broiler and e.usr$gs_department = :broiler_ceh3) THEN 15 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_parent) AND (e.usr$gs_good = :good_chick) THEN 16 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_parent) AND (e.usr$gs_good = :good_cock) THEN 1 
     END AS rec_counter, 
     0 AS begin_q,        0 AS begin_w,          0 AS begin_s, 
     0 AS in_incub_q,     0 AS in_incub_w,       0 AS in_incub_s, 
     0 AS in_weight_q, 
     0 AS in_weight_w, 
     0 AS in_weight_s, 
     0 AS in_group_q,     0 AS in_group_w,       0 AS in_group_s, 
     0 AS in_buy_q,       0 AS in_buy_w,         e.debitncu AS in_buy_s, 
     0 AS out_group_q,    0 AS out_group_w,      0 AS out_group_s, 
     0 AS out_01_1_q,       0 AS out_01_1_w,     0 AS out_01_1_s, 
     0 AS out_02_q,       0 AS out_02_w,         0 AS out_02_s, 
     0 AS out_03_q,       0 AS out_03_w,         0 AS out_03_s, 
     0 AS out_04_q,       0 AS out_04_w,         0 AS out_04_s 
   FROM 
     ac_entry e LEFT JOIN ac_entry e1 ON e.recordkey = e1.recordkey and e.accountpart = 'D' and e1.accountpart = 'C'
   WHERE 
     e.entrydate >= :datebegin 
     AND e.entrydate <= :dateend 
     AND e.accountkey = :acc113 AND e.debitncu <> 0  
     AND e.transactionkey  = 147011094 
     AND e.usr$gs_good IN (306180741,306180742,302298356) 

   UNION ALL 
   SELECT 
     CASE 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_42) AND (e.usr$gs_good = :good_chick) THEN 1 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_42) AND (e.usr$gs_good = :good_cock) THEN 2 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_140) AND (e.usr$gs_good = :good_chick) THEN 3 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_140) AND (e.usr$gs_good = :good_cock) THEN 4 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_43) AND (e.usr$gs_good = :good_chick) THEN 5 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_43) AND (e.usr$gs_good = :good_cock) THEN 6 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_130) AND (e.usr$gs_good = :good_chick) THEN 7 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_130) AND (e.usr$gs_good = :good_cock) THEN 8 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_1_130) AND (e.usr$gs_good = :good_chick) THEN 9 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_1_130) AND (e.usr$gs_good = :good_cock) THEN 10 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_180) AND (e.usr$gs_good = :good_chick) THEN 11 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_180) AND (e.usr$gs_good = :good_cock) THEN 12 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_broiler and e.usr$gs_department = :broiler_ceh1) THEN 13 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_broiler and e.usr$gs_department = :broiler_ceh2) THEN 14 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_broiler and e.usr$gs_department = :broiler_ceh3) THEN 15 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_parent) AND (e.usr$gs_good = :good_chick) THEN 16 
       WHEN (e.USR$VBPF_AGEGROUPKEY = :age_parent) AND (e.usr$gs_good = :good_cock) THEN 17 
     END AS rec_counter, 
     0 AS begin_q,        0 AS begin_w,          0 AS begin_s, 
     0 AS in_incub_q,     0 AS in_incub_w,       0 AS in_incub_s, 
     0 AS in_weight_q, 
     0 AS in_weight_w, 
     0 AS in_weight_s, 
     0 AS in_group_q,     0 AS in_group_w,       0 AS in_group_s, 
     0 AS in_buy_q,       0 AS in_buy_w,         0 AS in_buy_s, 
     0 AS out_group_q,    0 AS out_group_w,      0 AS out_group_s, 
     0 AS out_01_1_q,       0 AS out_01_1_w,         0 AS out_01_1_s, 
     0 AS out_02_q,       0 AS out_02_w,         0 AS out_02_s, 
     0 AS out_03_q,       0 AS out_03_w,         iif(e1.accountkey in (:acc902, :acc25, 344501), e.creditncu, 0) AS out_03_s, 
     0 AS out_04_q,       0 AS out_04_w,         0 AS out_04_s 
   FROM 
     ac_entry e LEFT JOIN ac_entry e1 ON e.recordkey = e1.recordkey and e.accountpart = 'C' and e1.accountpart = 'D'
   WHERE 
     e.entrydate >= :datebegin 
     AND e.entrydate <= :dateend 
     AND e.accountkey = :acc113 AND e.creditncu <> 0  
     AND e.transactionkey in (:trkey, :trkey_1, :trkey_2) 
     AND e.usr$gs_good IN (306180741,306180742,302298356) 

   UNION ALL 

   /* Выбытие за выбранный период */ 
   SELECT 
     CASE 
       WHEN (c.usr$brd_agegroupkey = :age_42) AND (c.goodkey = :good_chick) THEN 1 
       WHEN (c.usr$brd_agegroupkey = :age_42) AND (c.goodkey = :good_cock) THEN 2 
       WHEN (c.usr$brd_agegroupkey = :age_140) AND (c.goodkey = :good_chick) THEN 3 
       WHEN (c.usr$brd_agegroupkey = :age_140) AND (c.goodkey = :good_cock) THEN 4 
       WHEN (c.usr$brd_agegroupkey = :age_43) AND (c.goodkey = :good_chick) THEN 5 
       WHEN (c.usr$brd_agegroupkey = :age_43) AND (c.goodkey = :good_cock) THEN 6 
       WHEN (c.usr$brd_agegroupkey = :age_130) AND (c.goodkey = :good_chick) THEN 7 
       WHEN (c.usr$brd_agegroupkey = :age_130) AND (c.goodkey = :good_cock) THEN 8 
       WHEN (c.usr$brd_agegroupkey = :age_1_130) AND (c.goodkey = :good_chick) THEN 9 
       WHEN (c.usr$brd_agegroupkey = :age_1_130) AND (c.goodkey = :good_cock) THEN 10 
       WHEN (c.usr$brd_agegroupkey = :age_180) AND (c.goodkey = :good_chick) THEN 11 
       WHEN (c.usr$brd_agegroupkey = :age_180) AND (c.goodkey = :good_cock) THEN 12 
       WHEN (c.usr$brd_agegroupkey = :age_broiler and con.id = :broiler_ceh1) THEN 13 
       WHEN (c.usr$brd_agegroupkey = :age_broiler and con.id = :broiler_ceh2) THEN 14 
       WHEN (c.usr$brd_agegroupkey = :age_broiler and con.id = :broiler_ceh3) THEN 15 
       WHEN (c.usr$brd_agegroupkey = :age_parent) AND (c.goodkey = :good_chick) THEN 16 
       WHEN (c.usr$brd_agegroupkey = :age_parent) AND (c.goodkey = :good_cock) THEN 17 
     END AS rec_counter, 
     0 AS begin_q,        0 AS begin_w,          0 AS begin_s, 
     0 AS in_incub_q,     0 AS in_incub_w,       0 AS in_incub_s, 
     0 AS in_weight_q,    0 AS in_weight_w,      0 AS in_weight_s, 
     0 AS in_group_q,     0 AS in_group_w,       0 AS in_group_s, 
     0 AS in_buy_q,       0 AS in_buy_w,         0 AS in_buy_s, 
     0 AS out_group_q,    0 AS out_group_w,      0 AS out_group_s, 
     IIF(head.usr$reasonkey = :out_reason_01 and head.USR$TODEPTINFOKEY in (:ceh1, :ceh2, :ceh_new), line.quantity, 0) AS out_01_1_q, 
     IIF(head.usr$reasonkey = :out_reason_01 and head.USR$TODEPTINFOKEY in (:ceh1, :ceh2, :ceh_new), line.usr$weightsum, 0) AS out_01_1_w, 
     IIF(head.usr$reasonkey = :out_reason_01 and head.USR$TODEPTINFOKEY in (:ceh1, :ceh2, :ceh_new), line.usr$sumncu, 0) AS out_01_1_s, 
     IIF(head.usr$reasonkey = :out_reason_02, line.quantity, 0) AS out_02_q, 
     IIF(head.usr$reasonkey = :out_reason_02, line.usr$weightsum, 0) AS out_02_w, 
     IIF(head.usr$reasonkey = :out_reason_02, line.usr$sumncu, 0) AS out_02_s, 
     IIF(head.usr$reasonkey = :out_reason_03 or head.usr$reasonkey = :out_reason_03_1 or head.usr$reasonkey = :out_reason_03_2 or head.usr$reasonkey = :out_reason_03_3, line.quantity, 0) AS out_03_q, 
     IIF(head.usr$reasonkey = :out_reason_03 or head.usr$reasonkey = :out_reason_03_1 or head.usr$reasonkey = :out_reason_03_2 or head.usr$reasonkey = :out_reason_03_3, line.usr$weightsum, 0) AS out_03_w, 
     IIF(head.usr$reasonkey = :out_reason_03 or head.usr$reasonkey = :out_reason_03_1 or head.usr$reasonkey = :out_reason_03_2, line.usr$sumncu, 0) AS out_03_s, 
     IIF(head.usr$reasonkey = :out_reason_04 or head.usr$reasonkey = :out_reason_05, line.quantity, 0) AS out_04_q, 
     IIF(head.usr$reasonkey = :out_reason_04 or head.usr$reasonkey = :out_reason_05, line.usr$weightsum, 0) AS out_04_w, 
     IIF(head.usr$reasonkey = :out_reason_04 or head.usr$reasonkey = :out_reason_05, line.usr$sumncu, 0) AS out_04_s 
   FROM 
     gd_document doc 
     JOIN usr$brd_disposal head ON head.documentkey = doc.id 
     JOIN usr$brd_disposalline line ON line.masterkey = head.documentkey 
     LEFT JOIN inv_card c ON c.id = line.tocardkey 
     LEFT JOIN gd_contact con ON con.id = head.usr$maindeptkey 
   WHERE 
     doc.documenttypekey = :disposal_doctype 
     AND doc.documentdate >= :datebegin 
     AND doc.documentdate <= :dateend 
     AND con.lb >= :com_lb 
     AND con.rb <= :com_rb 
     AND c.goodkey IN (306180741,306180742,302298356) 
   UNION ALL 

   /* Выбытие за выбранный период */ 
   SELECT 
     CASE 
       WHEN (c.usr$brd_agegroupkey = :age_42) AND (c.goodkey = :good_chick) THEN 1 
       WHEN (c.usr$brd_agegroupkey = :age_42) AND (c.goodkey = :good_cock) THEN 2 
       WHEN (c.usr$brd_agegroupkey = :age_140) AND (c.goodkey = :good_chick) THEN 3 
       WHEN (c.usr$brd_agegroupkey = :age_140) AND (c.goodkey = :good_cock) THEN 4 
       WHEN (c.usr$brd_agegroupkey = :age_43) AND (c.goodkey = :good_chick) THEN 5 
       WHEN (c.usr$brd_agegroupkey = :age_43) AND (c.goodkey = :good_cock) THEN 6 
       WHEN (c.usr$brd_agegroupkey = :age_130) AND (c.goodkey = :good_chick) THEN 7 
       WHEN (c.usr$brd_agegroupkey = :age_130) AND (c.goodkey = :good_cock) THEN 8 
       WHEN (c.usr$brd_agegroupkey = :age_1_130) AND (c.goodkey = :good_chick) THEN 9 
       WHEN (c.usr$brd_agegroupkey = :age_1_130) AND (c.goodkey = :good_cock) THEN 10 
       WHEN (c.usr$brd_agegroupkey = :age_180) AND (c.goodkey = :good_chick) THEN 11 
       WHEN (c.usr$brd_agegroupkey = :age_180) AND (c.goodkey = :good_cock) THEN 12 
       WHEN (c.usr$brd_agegroupkey = :age_broiler and con.id = :broiler_ceh1) THEN 13 
       WHEN (c.usr$brd_agegroupkey = :age_broiler and con.id = :broiler_ceh2) THEN 14 
       WHEN (c.usr$brd_agegroupkey = :age_broiler and con.id = :broiler_ceh3) THEN 15 
       WHEN (c.usr$brd_agegroupkey = :age_parent) AND (c.goodkey = :good_chick) THEN 16 
       WHEN (c.usr$brd_agegroupkey = :age_parent) AND (c.goodkey = :good_cock) THEN 17 
     END AS rec_counter, 
     0 AS begin_q,        0 AS begin_w,          0 AS begin_s, 
     0 AS in_incub_q,     0 AS in_incub_w,       0 AS in_incub_s, 
     0 AS in_weight_q,    0 AS in_weight_w,      0 AS in_weight_s, 
     0 AS in_group_q,     0 AS in_group_w,       0 AS in_group_s, 
     0 AS in_buy_q,       0 AS in_buy_w,         0 AS in_buy_s, 
     0 AS out_group_q,    0 AS out_group_w,      0 AS out_group_s, 
     0 AS out_01_1_q, 
     0 AS out_01_1_w, 
     0 AS out_01_1_s, 
     0 AS out_02_q, 
     0 AS out_02_w, 
     0 AS out_02_s, 
     line.quantity AS out_03_q, 
     line.USR$GRASSWEIGHT AS out_03_w, 
     iif(c.goodkey <> :kura, line.USR$GRASSWEIGHT, line.quantity) * p.usr$inv_costaccncu AS out_03_s, 
     0 AS out_04_q, 
     0 AS out_04_w, 
     0 AS out_04_s 
   FROM 
     usr$inv_sellbill head 
     JOIN usr$inv_sellbillline line ON line.masterkey = head.documentkey 
     JOIN inv_card c ON c.id = line.tocardkey 
     LEFT JOIN gd_document doc ON head.documentkey = doc.id 
     LEFT JOIN gd_contact con ON con.id = head.usr$maindepotkey 
     LEFT JOIN USR$VBPF_BRD_GETPRICECOST_IDS(doc.documentdate, c.goodkey, c.usr$brd_agegroupkey, 0, 0, iif(head.usr$maindepotkey = :broiler_ceh1, 360790255, iif(head.usr$maindepotkey = :broiler_ceh2, 360790256, iif(head.usr$maindepotkey = :broiler_ceh3, 709842634, -1))), :IDS) p ON 1 = 1 
   WHERE 
     doc.documenttypekey = :sellbill_doctype 
     AND doc.documentdate >= :datebegin 
     AND doc.documentdate <= :dateend 
     AND con.lb >= :com_lb 
     AND con.rb <= :com_rb 
     AND c.goodkey IN (306180741,306180742,302298356) 

  ) r 
GROUP BY 
  r.rec_counter 
HAVING 
  r.rec_counter > 0

I even woudn't dare to count the number of parameters in the query not saying of replacing them with ?s.

I'm sure that without named parameters the library just isn't suited for usage with our existing customers.

So, I have only two options at hand:

1) fork the library and work with the fork importing it in package.json from github account. 2) write wrapping classes around the library with support of named parameters.

and I dislike both approaches very much.