iBase.ru Forum

Код: Выделить всё

  OPEN CUR1;
  FETCH CUR1 INTO :ID, :X, :Y;
  WHILE (SQLCODE = 0)
  DO
  BEGIN
    FETCH CUR1 INTO :ID, :X, :Y;
    
    UPDATE DEAL
    SET PDN_PART = :X
    WHERE
      DEAL_NO = :ID;

    
  END
  CLOSE CUR1;

Код: Выделить всё

    UPDATE DEAL
    SET PDN_PART = :X
    WHERE
      DEAL_NO = :ID;

Код: Выделить всё

  BEGIN
    FETCH CUR1 INTO :ID, :REF_X, :Y;
    REF_X =  Y    
  END

Код: Выделить всё

  OPEN CUR1;
  FETCH CUR1 INTO :CURR_DEAL_ID, :CURR_DEAL_SUM;

  WHILE (SQLCODE = 0)
  DO
  BEGIN
    PDN_FOR_DEAL = :CURR_DEAL_SUM * :PDN_SUM / :ALL_DEAL_SUM;
    UPDATE REPORT_INFO
    SET
      PDN_SUM = :PDN_SUM,
      DEAL_SUM = :CURR_DEAL_SUM,
      DEAL_PDN = :PDN_FOR_DEAL
    WHERE
      FK_DEAL = :DEAL_NO;

    FETCH CUR1 INTO :CURR_DEAL_ID, :CURR_DEAL_SUM;
  END
  CLOSE CUR1;

Код: Выделить всё

  OPEN CURR_DEAL_CURSOR;

  WHILE (1=1) DO
    BEGIN
      FETCH CURR_DEAL_CURSOR INTO :CURR_DEAL_ID, :CURR_DEAL_SUM;

      IF (ROW_COUNT = 0) THEN
        LEAVE;

  CLOSE CURR_DEAL_CURSOR;

Код: Выделить всё

CREATE TABLE DEAL(
  DEAL_NO     INTEGER NOT NULL,
  FK_DEPT_NO  INTEGER,
  FK_MONTH_NO INTEGER,
  DEALSUM     DECIMAL(9,2) DEFAULT 0
);

Код: Выделить всё

CREATE TABLE REPORT_INFO(
  REPORT_INFO_NO  INTEGER NOT NULL, 
  FK_DEAL_NO     INTEGER NOT NULL,          
  FK_MONTH_NO    INTEGER NOT NULL,        
  FK_DEPT_NO     INTEGER NOT NULL,
  PDN_SUM     DECIMAL(9,2) DEFAULT 0,      /* сумма ПДН для отдела, которому принадлежит сделка (копируется из таблицы PDN) */  
  DEAL_SUM    DECIMAL(9,2) DEFAULT 0,     /* сумма сделки (копируется из  DEAL) */
  DEAL_PDN    DECIMAL(9,2) DEFAULT 0,     /* удельный ПДН для сделки. Равен: (сумма данной сделки / суммы всех сделок отдела по периоду) * PDN_SUM, то есть ПДН отдела за период */
);

Код: Выделить всё

CREATE TRIGGER TR_DEALS_SUM_FOR_PDN FOR DEAL
ACTIVE AFTER INSERT OR UPDATE OR DELETE POSITION 0
AS

DECLARE VARIABLE MONZ_NO INTEGER = 0;
DECLARE VARIABLE DEPT_NO INTEGER = 0;
DECLARE VARIABLE DEAL_NO INTEGER = 0;
DECLARE VARIABLE IS_MAKE SMALLINT = 0;
DECLARE VARIABLE IS_EXISTS SMALLINT = 0;

/*
  При любом изменении в суммах и активности сделок

  1. Если сделка добавляется, добавим соответствие в REPORT_INFO
     Если удаляется - удалим из REPORT_INFO
  2. Вызываем процедуру обновления таблицы отчетов

*/

BEGIN

-- 1.
  IF (INSERTING OR UPDATING) THEN
  BEGIN
    MONZ_NO = NEW.FK_MONTH_NO;
    DEPT_NO = NEW.FK_DEPT_NO;
    DEAL_NO = NEW.DEAL_NO;
    IS_MAKE = NEW.MAKE;
  END
  ELSE IF (DELETING) THEN
  BEGIN
    MONZ_NO = OLD.FK_MONTH_NO;
    DEPT_NO = OLD.FK_DEPT_NO;
    DEAL_NO = OLD.DEAL_NO;
    IS_MAKE = OLD.MAKE;
  END
  
-- приводим записи REPORT_INFO в соответствие с DEAL

  SELECT COUNT(*) FROM REPORT_INFO
  WHERE FK_DEAL = :DEAL_NO
  INTO :IS_EXISTS;

  IF (INSERTING AND IS_MAKE=1) THEN
    INSERT INTO REPORT_INFO(FK_DEAL, FK_MONTH, FK_DEPT)
      VALUES(:DEAL_NO, :MONZ_NO, :DEPT_NO);

  ELSE IF (UPDATING) THEN
  BEGIN
    IF ((IS_MAKE=1) AND (IS_EXISTS=0)) THEN
      INSERT INTO REPORT_INFO(FK_DEAL, FK_MONTH, FK_DEPT)
        VALUES(:DEAL_NO, :MONZ_NO, :DEPT_NO);
    ELSE IF (IS_MAKE=0) THEN
      DELETE FROM REPORT_INFO
        WHERE FK_DEAL = :DEAL_NO;
  END

  ELSE IF (DELETING) THEN
    DELETE FROM REPORT_INFO
      WHERE FK_DEAL = :DEAL_NO;

-- 2
  EXECUTE PROCEDURE PR_UPD_REPORT_INFO(:DEPT_NO, :MONZ_NO);

END
;

Код: Выделить всё

CREATE PROCEDURE PR_UPD_REPORT_INFO (
  DEPT_NO   INTEGER,
  MONZ_NO   INTEGER
)
AS
DECLARE VARIABLE PDN_SUM DECIMAL(15,3) = 0;
DECLARE VARIABLE ALL_DEAL_SUM DECIMAL(15,3) = 0;
DECLARE VARIABLE ALL_EXP_SUM DECIMAL(15,3) = 0;

DECLARE VARIABLE CURR_DEAL_CURSOR CURSOR FOR
    (SELECT D.DEAL_NO, D.DEALSUM
      FROM DEAL D
      WHERE D.FK_MONTH_NO = :MONZ_NO
      AND D.FK_DEPT_NO = :DEPT_NO
      AND D.MAKE = 1);

DECLARE VARIABLE CURR_DEAL_ID INTEGER = 0;
DECLARE VARIABLE CURR_DEAL_SUM DECIMAL(15,3) = 0;
DECLARE VARIABLE PDN_FOR_DEAL DECIMAL(15,5) = 0;

DECLARE VARIABLE CURR_EXP_CURSOR CURSOR FOR
    (SELECT SUM(E.VAL)
      FROM EXPENDS_IN_DEAL E
      WHERE E.FK_DEAL = :CURR_DEAL_ID);

BEGIN

-- 1
  SELECT
    P.PDN FROM PDN P
  WHERE
    P.FK_DEPT_NO = :DEPT_NO AND
    P.FK_MONTH_NO = :MONZ_NO
  INTO :PDN_SUM;

-- 2
  SELECT
    SUM(D.DEALSUM) FROM DEAL D
  WHERE
    D.FK_DEPT_NO = :DEPT_NO AND
    D.FK_MONTH_NO = :MONZ_NO AND
    D.MAKE = 1
  INTO :ALL_DEAL_SUM;
  

--  3

  OPEN CURR_DEAL_CURSOR;

  WHILE (1=1) DO
    BEGIN
      FETCH CURR_DEAL_CURSOR INTO :CURR_DEAL_ID, :CURR_DEAL_SUM;

      IF (ROW_COUNT = 0) THEN
        LEAVE;

      PDN_FOR_DEAL = :CURR_DEAL_SUM * :PDN_SUM / :ALL_DEAL_SUM;
      
      OPEN CURR_EXP_CURSOR;
      FETCH CURR_EXP_CURSOR INTO :ALL_EXP_SUM;
      CLOSE CURR_EXP_CURSOR;

      UPDATE REPORT_INFO
      SET
        PDN_SUM = :PDN_SUM,
        DEAL_SUM = :CURR_DEAL_SUM,
        DEAL_PDN = :PDN_FOR_DEAL,
        DEAL_EXP = :ALL_EXP_SUM,
        DEAL_PROFIT = :CURR_DEAL_SUM - :PDN_FOR_DEAL - :ALL_EXP_SUM
      WHERE
        FK_DEAL_NO = :CURR_DEAL_ID;

    END

  CLOSE CURR_DEAL_CURSOR;

END
;

Код: Выделить всё

select d.deal_no,
       d.fk_month_no,
       d.fk_dept_no,
       p.pdn as pdn_sum,
       d.dealsum as deal_sum,
       d.dealsum * p.pdn / deal_group.deals_sum as deal_pdn,
       exp_group.val as deal_exp,
       d.dealsum - d.dealsum * p.pdn / deal_group.deals_sum - coalesce(exp_group.val, 0) as deal_profit
  from DEAL d
       join PDN p on p.fk_dept_no = d.fk_dept_no and p.fk_month_no = d.fk_month_no
       (select fk_dept_no, fk_month_no, sum(dealsum) deals_sum
          from deal
          where make = 1
          group by 1, 2
       ) deal_group on deal_group.fk_dept_no = d.fk_dept_no and deal_group.fk_month_no = d.fk_month_no
       left join
       (select fk_deal, sum(val) as val
          from EXPENDS_IN_DEAL
          group by 1
       ) exp_group on exp_group.fk_deal = d.deal_no
  where d.make = 1