USR$INV STATREPORT

CREATE OR ALTER PROCEDURE USR$INV_STATREPORT (
    DEPOTKEY DFOREIGNKEY,
    BEGINDATE DATE,
    ENDDATE DATE,
    DEPARTSKEYS VARCHAR(200))
RETURNS ( 
    USR$NAMEGOODGROUP VARCHAR(1024),
    SUMTORGBEL DOUBLE PRECISION,
    SUMTORGIMP DOUBLE PRECISION,
    SUMBEL DOUBLE PRECISION,
    SUMIMP DOUBLE PRECISION,
    VALUEKEY DFOREIGNKEY,
    NUMBERGOODGROUP VARCHAR(10),
    SUMBALANCEBEL DOUBLE PRECISION,
    SUMBALANCEIMP DOUBLE PRECISION,
    BALANCEBEL DOUBLE PRECISION,
    BALANCEIMP DOUBLE PRECISION,
    LB DINTEGER,
    RB DINTEGER,
    PARENT DFOREIGNKEY)
 AS
    DECLARE variable spenddoctypekey DFOREIGNKEY;
    DECLARE variable todoctypekey DFOREIGNKEY;
    DECLARE variable fromdoctypekey DFOREIGNKEY;
BEGIN
  BalanceBel = 0;
  BalanceImp = 0;
  sumBalanceBel = 0;
  sumBalanceImp = 0;
  spenddoctypekey = NULL;
  todoctypekey = NULL;
  fromdoctypekey = NULL;
 
  SELECT
    p.id
  FROM
    gd_p_getid(147006557, 63934951) p
  INTO
    :spenddoctypekey;
 
  SELECT
    p.id
  FROM
    gd_p_getid(147005934, 63934951) p
  INTO
    :todoctypekey;
 
  SELECT
    p.id
  FROM
    gd_p_getid(147006209, 63934951) p
  INTO
    :fromdoctypekey;
 
  FOR
    SELECT iif(COALESCE(r.id,0) > 0, r.LB, rg.LB) AS LB, iif(COALESCE(r.id,0) > 0, r.RB, rg.RB) AS RB, iif(COALESCE(r.id,0) > 0, r.parent, rg.parent) AS parent,
      iif(COALESCE(r.id,0) > 0, r.usr$namegoodgroup, rg.usr$namegoodgroup) AS usr$namegoodgroup,
      iif(COALESCE(r.id,0) > 0, r.USR$VALUEKEY, rg.USR$VALUEKEY) AS USR$VALUEKEY,
      iif(COALESCE(r.id,0) > 0, r.USR$NUMBERROW, rg.USR$NUMBERROW) AS USR$NUMBERROW,
      SUM(iif(c.usr$inv_origincountrykey=1800010, m.credit * COALESCE(g.USR$INV_WEIGHT,1) * iif(COALESCE(r.id,0) > 0, COALESCE(g.usr$inv_coeff,1), COALESCE(gr.usr$inv_refformtorgkoeff,1)), 0)) AS SumTorgBel,
      SUM(iif(c.usr$inv_origincountrykey=1800010,0, m.credit * COALESCE(g.USR$INV_WEIGHT,1) * iif(COALESCE(r.id,0) > 0, COALESCE(g.usr$inv_coeff,1), COALESCE(gr.usr$inv_refformtorgkoeff,1)))) AS SumTorgImp,
      SUM(iif(c.usr$inv_origincountrykey=1800010, c.usr$inv_costretailncu * m.credit, 0)) AS SumBel,
      SUM(iif(c.usr$inv_origincountrykey=1800010,0, c.usr$inv_costretailncu * m.credit)) AS SumImp
    FROM
      inv_movement m
      JOIN gd_good g ON g.id = m.goodkey
      LEFT JOIN gd_goodgroup gr ON gr.id = g.groupkey
      LEFT JOIN usr$inv_refformtorg r ON r.id = g.usr$inv_refformtorgkey
      LEFT JOIN usr$inv_refformtorg rg ON rg.id = gr.usr$inv_refformtorgkey
      LEFT JOIN inv_movement m1 ON m1.movementkey = m.movementkey AND m1.debit > 0
      LEFT JOIN inv_card c ON c.id = m.cardkey
      LEFT JOIN gd_document doc ON m.documentkey = doc.id
    WHERE
      m.movementdate >= :BeginDate
      AND
      m.movementdate <= :EndDate
      AND
      m.credit > 0
      AND
      m.contactkey = :depotkey
      AND (r.id IS NOT NULL OR rg.id IS NOT NULL)
      AND doc.documenttypekey = :spenddoctypekey
    GROUP BY
      1, 2, 3, 4, 5, 6
    UNION ALL
    SELECT iif(COALESCE(r.id,0) > 0, r.LB, rg.LB) AS LB, iif(COALESCE(r.id,0) > 0, r.RB, rg.RB) AS RB, iif(COALESCE(r.id,0) > 0, r.parent, rg.parent) AS parent,
      iif(COALESCE(r.id,0) > 0, r.usr$namegoodgroup, rg.usr$namegoodgroup) AS usr$namegoodgroup,
      iif(COALESCE(r.id,0) > 0, r.USR$VALUEKEY, rg.USR$VALUEKEY) AS USR$VALUEKEY,
      iif(COALESCE(r.id,0) > 0, r.USR$NUMBERROW, rg.USR$NUMBERROW) AS USR$NUMBERROW,
      SUM(iif(c.usr$inv_origincountrykey=1800010, (-m.debit+m.credit) * COALESCE(g.USR$INV_WEIGHT,1) * iif(COALESCE(r.id,0) > 0, COALESCE(g.usr$inv_coeff,1), COALESCE(gr.usr$inv_refformtorgkoeff,1)), 0)) AS SumTorgBel,
      SUM(iif(c.usr$inv_origincountrykey=1800010,0, (-m.debit+m.credit) * COALESCE(g.USR$INV_WEIGHT,1) * iif(COALESCE(r.id,0) > 0, COALESCE(g.usr$inv_coeff,1), COALESCE(gr.usr$inv_refformtorgkoeff,1)))) AS SumTorgImp,
      SUM(iif(c.usr$inv_origincountrykey=1800010, c.usr$inv_costretailncu * (-m.debit+m.credit), 0)) AS SumBel,
      SUM(iif(c.usr$inv_origincountrykey=1800010,0, c.usr$inv_costretailncu * (-m.debit+m.credit))) AS SumImp
    FROM
      inv_movement m
      JOIN gd_good g ON g.id = m.goodkey
      LEFT JOIN gd_goodgroup gr ON gr.id = g.groupkey
      LEFT JOIN usr$inv_refformtorg r ON r.id = g.usr$inv_refformtorgkey
      LEFT JOIN usr$inv_refformtorg rg ON rg.id = gr.usr$inv_refformtorgkey
      LEFT JOIN inv_movement m1 ON m1.movementkey = m.movementkey AND m1.debit > 0
      LEFT JOIN inv_card c ON c.id = m.cardkey
      LEFT JOIN gd_document doc ON m.documentkey = doc.id
    WHERE
      m.movementdate >= :BeginDate
      AND
      m.movementdate <= :EndDate
      AND
      m.credit > 0
      AND
      m.contactkey = :depotkey
      AND (r.id IS NOT NULL OR rg.id IS NOT NULL)
      AND doc.documenttypekey IN (:todoctypekey, :fromdoctypekey)
    GROUP BY
      1, 2, 3, 4, 5, 6
    INTO :LB, :RB, :PARENT, :USR$NAMEGOODGROUP, :VALUEKEY, :NUMBERGOODGROUP,
         :SUMTORGBEL, :SUMTORGIMP, :SUMBEL, :SUMIMP
  DO
  BEGIN
    SUSPEND;
  END
  SumTorgBel = 0;
  SumTorgImp = 0;
  SumBel = 0;
  SumImp = 0;
  FOR
    SELECT iif(COALESCE(r.id,0) > 0, r.LB, rg.LB) AS LB, iif(COALESCE(r.id,0) > 0, r.RB, rg.RB) AS RB, iif(COALESCE(r.id,0) > 0, r.parent, rg.parent) AS parent,
      iif(COALESCE(r.id,0) > 0, r.usr$namegoodgroup, rg.usr$namegoodgroup) AS usr$namegoodgroup,
      iif(COALESCE(r.id,0) > 0, r.USR$VALUEKEY, rg.USR$VALUEKEY) AS USR$VALUEKEY,
      iif(COALESCE(r.id,0) > 0, r.USR$NUMBERROW, rg.USR$NUMBERROW) AS USR$NUMBERROW,
      SUM(iif(c.usr$inv_origincountrykey=1800010,((m.debit - m.credit) * COALESCE(g.USR$INV_WEIGHT,1) * iif(COALESCE(r.id,0) > 0, COALESCE(g.usr$inv_coeff,1), COALESCE(gr.usr$inv_refformtorgkoeff,1))),0)) AS BalanceBEL,
      SUM(iif(c.usr$inv_origincountrykey=1800010,0,((m.debit - m.credit) * COALESCE(g.USR$INV_WEIGHT,1) * iif(COALESCE(r.id,0) > 0, COALESCE(g.usr$inv_coeff,1), COALESCE(gr.usr$inv_refformtorgkoeff,1))))) AS Balance,
      SUM(iif(c.usr$inv_origincountrykey=1800010, c.usr$inv_costretailncu * (m.debit - m.credit), 0)) AS SumBalanceBel,
      SUM(iif(c.usr$inv_origincountrykey=1800010,0, c.usr$inv_costretailncu * (m.debit - m.credit))) AS SumBalanceImp
    FROM
      inv_movement m
      JOIN gd_good g ON g.id = m.goodkey
      LEFT JOIN gd_goodgroup gr ON gr.id = g.groupkey
      LEFT JOIN usr$inv_refformtorg r ON r.id = g.usr$inv_refformtorgkey
      LEFT JOIN usr$inv_refformtorg rg ON rg.id = gr.usr$inv_refformtorgkey
      JOIN gd_contact con ON con.id = m.contactkey
      JOIN gd_contact maincon ON maincon.lb <= con.lb AND maincon.rb >= con.rb
      LEFT JOIN inv_card c ON c.id = m.cardkey
    WHERE
      m.movementdate <= :EndDate
      AND (r.id IS NOT NULL OR rg.id IS NOT NULL) AND
      maincon.id = :depotkey
    GROUP BY
      1, 2, 3, 4, 5, 6
    INTO :LB, :RB, :PARENT, :USR$NAMEGOODGROUP, :VALUEKEY, :NUMBERGOODGROUP,
         :BALANCEBEL, :BALANCEIMP, :SUMBALANCEBEL, :SUMBALANCEIMP
  DO
  BEGIN
    SUSPEND;
  END
END