CREATE GENERATOR GEN_CARGO_DANGER_ID;

CREATE TABLE CARGO_DANGER (ID    INTEGER NOT NULL, NAME  VARCHAR(64) NOT NULL);

ALTER TABLE CARGO_DANGER ADD CONSTRAINT PK_CARGO_DANGER PRIMARY KEY (ID);
alter table CARGO_DANGER add constraint UNQ1_CARGO_DANGER unique (NAME);

CREATE TRIGGER CARGO_DANGER_BI FOR CARGO_DANGER
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL)
  THEN
    NEW.ID = GEN_ID(GEN_CARGO_DANGER_ID, 1);
END;

INSERT INTO CARGO_DANGER(ID, NAME) VALUES(0, 'По умолчанию');

ALTER TABLE EVENT_BRUTTO ADD VALUME DOUBLE PRECISION NOT NULL;

UPDATE EVENT_BRUTTO SET VALUME = 0;

ALTER TABLE EVENT_BRUTTO ADD CARGO_DANGER_ID INTEGER NOT NULL;

UPDATE EVENT_BRUTTO SET CARGO_DANGER_ID = 0;

alter table EVENT_BRUTTO add constraint FK_EVENT_BRUTTO_12 foreign key (CARGO_DANGER_ID) references CARGO_DANGER(ID) on delete CASCADE;

ALTER PROCEDURE SAVE_TARA_NETTO (
    PROGRAM_ID INTEGER,
    OPERATOR_ID INTEGER,
    TRANSPORT_ID INTEGER,
    EVENT_BRUTTO_ID INTEGER,
    CARGO_ID INTEGER,
    TARA DOUBLE PRECISION,
    BRUTTO DOUBLE PRECISION,
    DIRECTION INTEGER,
    COUNT_WEIGHT INTEGER,
    PRICE_TONNA DOUBLE PRECISION,
    LOSS DOUBLE PRECISION,
    ORGANIZATION_LIMIT_ID INTEGER,
    CARD_ID INTEGER,
    PASS_BACK INTEGER,
    WEIGH_TWICE INTEGER,
    VALUME DOUBLE PRECISION,
    CARGO_DANGER_ID INTEGER)
RETURNS (
    ID INTEGER,
    EVENT_TARA_ID INTEGER)
AS
DECLARE VARIABLE NETTO DOUBLE PRECISION;
DECLARE VARIABLE NETTO_KG INTEGER;
DECLARE VARIABLE PRICE DOUBLE PRECISION;
DECLARE VARIABLE REMAINDER DOUBLE PRECISION;
DECLARE VARIABLE REAL_NETTO DOUBLE PRECISION;
DECLARE VARIABLE PRICE_KOPP INTEGER;
begin
  /************************************************/
  EXECUTE PROCEDURE
    SAVE_TARA(
     :PROGRAM_ID,
     :OPERATOR_ID,
     :TRANSPORT_ID,
     :TARA,
     :COUNT_WEIGHT,
     :CARD_ID)
  RETURNING_VALUES
    :EVENT_TARA_ID;
  /************************************************/
  REAL_NETTO = BRUTTO - TARA;
  NETTO_KG = (REAL_NETTO - (REAL_NETTO/100*LOSS)) * 1000;
  NETTO = NETTO_KG;
  NETTO = NETTO / 1000;
  /************************************************/
  FOR
    SELECT
      REMAINDER
    FROM
      ORGANIZATION_CARGO
    WHERE
      (CARGO_ID = :CARGO_ID) AND
      (ORGANIZATION_ID = :ORGANIZATION_LIMIT_ID)
    INTO
      :REMAINDER
  DO
    BEGIN
      IF (:DIRECTION = 0)
      THEN
        REMAINDER = REMAINDER + NETTO;
      ELSE
        REMAINDER = REMAINDER - NETTO;

      UPDATE
        ORGANIZATION_CARGO
      SET
        REMAINDER = :REMAINDER
      WHERE
        (CARGO_ID = :CARGO_ID) AND
        (ORGANIZATION_ID = :ORGANIZATION_LIMIT_ID);
    END
  /************************************************/
  ID = GEN_ID(GEN_EVENT_NETTO_ID, 1);

  PRICE_KOPP = NETTO * PRICE_TONNA * 100;
  PRICE = PRICE_KOPP;
  PRICE = PRICE / 100;
  INSERT INTO
    EVENT_NETTO
    (ID,
     PROGRAM_ID,
     OPERATOR_ID,
     EVENT_TARA_ID,
     EVENT_BRUTTO_ID,
     REAL_NETTO,
     LOSS,
     NETTO,
     PRICE,
     PRICE_TONNA,
     PASS_BACK)
  VALUES
    (:ID,
     :PROGRAM_ID,
     :OPERATOR_ID,
     :EVENT_TARA_ID,
     :EVENT_BRUTTO_ID,
     :REAL_NETTO,
     :LOSS,
     :NETTO,
     :PRICE,
     :PRICE_TONNA,
     :PASS_BACK);
  /************************************************/
  UPDATE
    EVENT_BRUTTO
  SET
    EVENT_BRUTTO.CARGO_ID = :CARGO_ID,
    EVENT_BRUTTO.CARGO_PRICE_TONNA = :PRICE_TONNA,
    EVENT_BRUTTO.VALUME = :VALUME,
    EVENT_BRUTTO.CARGO_DANGER_ID = :CARGO_DANGER_ID
  WHERE
    EVENT_BRUTTO.ID = :EVENT_BRUTTO_ID;
  /************************************************/
  IF (WEIGH_TWICE = 1)
  THEN
    BEGIN
      UPDATE
        TRANSPORT
      SET
        EVENT_TARA_ID = NULL
      WHERE
        ID = :TRANSPORT_ID;
    END
  /************************************************/
  SUSPEND;
END;

ALTER PROCEDURE SAVE_BRUTTO (
    PROGRAM_ID INTEGER,
    OPERATOR_ID INTEGER,
    CARGO_ID INTEGER,
    TRANSPORT_ID INTEGER,
    BRUTTO DOUBLE PRECISION,
    REAL_BRUTTO DOUBLE PRECISION,
    DIRECTION INTEGER,
    COUNT_WEIGHT INTEGER,
    ORGANIZATION_POLUCHATEL_ID INTEGER,
    ORGANIZATION_OTPRAVITEL_ID INTEGER,
    ORGANIZATION_PLATELSHIK_ID INTEGER,
    ORGANIZATION_TRANSPORT_ID INTEGER,
    DRIVER_ID INTEGER,
    PAYMENT_ID INTEGER,
    CARD_ID INTEGER,
    CARGO_PRICE_TONNA DOUBLE PRECISION,
    VALUME DOUBLE PRECISION,
    CARGO_DANGER_ID INTEGER
    )
RETURNS (
    ID INTEGER)
AS
DECLARE VARIABLE PRICE_KOPP INTEGER;
BEGIN
  ID = GEN_ID(GEN_EVENT_BRUTTO_ID, 1);

  PRICE_KOPP = CARGO_PRICE_TONNA * 100;
  CARGO_PRICE_TONNA = PRICE_KOPP;
  CARGO_PRICE_TONNA = CARGO_PRICE_TONNA / 100;

  INSERT INTO
    EVENT_BRUTTO
    (ID,
     PROGRAM_ID,
     OPERATOR_ID,
     TRANSPORT_ID,
     CARGO_ID,
     BRUTTO,
     DIRECTION,
     COUNT_WEIGHT,
     ORGANIZATION_POLUCHATEL_ID,
     ORGANIZATION_OTPRAVITEL_ID,
     ORGANIZATION_PLATELSHIK_ID,
     ORGANIZATION_TRANSPORT_ID,
     DRIVER_ID,
     PAYMENT_ID,
     CARD_ID,
     CARGO_PRICE_TONNA,
     REAL_BRUTTO,
     VALUME,
     CARGO_DANGER_ID)
  VALUES
    (:ID,
     :PROGRAM_ID,
     :OPERATOR_ID,
     :TRANSPORT_ID,
     :CARGO_ID,
     :BRUTTO,
     :DIRECTION,
     :COUNT_WEIGHT,
     :ORGANIZATION_POLUCHATEL_ID,
     :ORGANIZATION_OTPRAVITEL_ID,
     :ORGANIZATION_PLATELSHIK_ID,
     :ORGANIZATION_TRANSPORT_ID,
     :DRIVER_ID,
     :PAYMENT_ID,
     :CARD_ID,
     :CARGO_PRICE_TONNA,
     :REAL_BRUTTO,
     :VALUME,
     :CARGO_DANGER_ID);

  UPDATE
    TRANSPORT
  SET
    EVENT_BRUTTO_ID = :ID
  WHERE
    ID = :TRANSPORT_ID;

  SUSPEND;
END;

ALTER PROCEDURE SAVE_BRUTTO_NETTO (
    PROGRAM_ID INTEGER,
    OPERATOR_ID INTEGER,
    TRANSPORT_ID INTEGER,
    EVENT_TARA_ID INTEGER,
    CARGO_ID INTEGER,
    TARA DOUBLE PRECISION,
    BRUTTO DOUBLE PRECISION,
    REAL_BRUTTO DOUBLE PRECISION,
    DIRECTION INTEGER,
    COUNT_WEIGHT INTEGER,
    ORGANIZATION_POLUCHATEL_ID INTEGER,
    ORGANIZATION_OTPRAVITEL_ID INTEGER,
    ORGANIZATION_PLATELSHIK_ID INTEGER,
    ORGANIZATION_TRANSPORT_ID INTEGER,
    DRIVER_ID INTEGER,
    PAYMENT_ID INTEGER,
    PRICE_TONNA DOUBLE PRECISION,
    LOSS DOUBLE PRECISION,
    ORGANIZATION_LIMIT INTEGER,
    CARD_ID INTEGER,
    TERMINAL_CHEK VARCHAR(32),
    KASSA_CHEK VARCHAR(32),
    PASS_BACK INTEGER,
    WEIGH_TWICE INTEGER,
    VALUME DOUBLE PRECISION,
    CARGO_DANGER_ID INTEGER
    )
RETURNS (
    ID INTEGER,
    EVENT_BRUTTO_ID INTEGER)
AS
DECLARE VARIABLE NETTO DOUBLE PRECISION;
DECLARE VARIABLE PRICE DOUBLE PRECISION;
DECLARE VARIABLE REMAINDER DOUBLE PRECISION;
DECLARE VARIABLE REAL_NETTO DOUBLE PRECISION;
DECLARE VARIABLE NETTO_KG INTEGER;
DECLARE VARIABLE PRICE_KOPP INTEGER;
begin
  /************************************************/
  EXECUTE PROCEDURE
    SAVE_BRUTTO(
     :PROGRAM_ID,
     :OPERATOR_ID,
     :CARGO_ID,
     :TRANSPORT_ID,
     :BRUTTO,
     :REAL_BRUTTO,
     :DIRECTION,
     :COUNT_WEIGHT,
     :ORGANIZATION_POLUCHATEL_ID,
     :ORGANIZATION_OTPRAVITEL_ID,
     :ORGANIZATION_PLATELSHIK_ID,
     :ORGANIZATION_TRANSPORT_ID,
     :DRIVER_ID,
     :PAYMENT_ID,
     :CARD_ID,
     :PRICE_TONNA,
     :VALUME,
     :CARGO_DANGER_ID)
  RETURNING_VALUES
    :EVENT_BRUTTO_ID;
  /************************************************/
  IF (BRUTTO = 0)
  THEN
    BEGIN
      REAL_NETTO = 0;
      NETTO = 0;
    END
   ELSE
    BEGIN
      REAL_NETTO = BRUTTO - TARA;
      NETTO_KG = (REAL_NETTO - (REAL_NETTO/100*LOSS)) * 1000;
      NETTO = NETTO_KG;
      NETTO = NETTO / 1000;
    END
  /************************************************/
    if (:ORGANIZATION_LIMIT = 0)
    then
      ORGANIZATION_LIMIT = ORGANIZATION_POLUCHATEL_ID;
    else
      ORGANIZATION_LIMIT = ORGANIZATION_PLATELSHIK_ID;
  /************************************************/
  FOR
    SELECT
      REMAINDER
    FROM
      ORGANIZATION_CARGO
    WHERE
      (CARGO_ID = :CARGO_ID) AND
      (ORGANIZATION_ID = :ORGANIZATION_LIMIT)
    INTO
      :REMAINDER
  DO
    BEGIN
      IF (:DIRECTION = 0)
      THEN
        REMAINDER = REMAINDER + NETTO;
      ELSE
        REMAINDER = REMAINDER - NETTO;

      UPDATE
        ORGANIZATION_CARGO
      SET
        REMAINDER = :REMAINDER
      WHERE
        (CARGO_ID = :CARGO_ID) AND
        (ORGANIZATION_ID = :ORGANIZATION_LIMIT);
    END
  /************************************************/
  ID = GEN_ID(GEN_EVENT_NETTO_ID, 1);
  PRICE_KOPP = NETTO * PRICE_TONNA * 100;
  PRICE = PRICE_KOPP;
  PRICE = PRICE / 100;
  INSERT INTO
    EVENT_NETTO
    (ID,
     PROGRAM_ID,
     OPERATOR_ID,
     EVENT_TARA_ID,
     EVENT_BRUTTO_ID,
     REAL_NETTO,
     LOSS,
     NETTO,
     PRICE,
     PRICE_TONNA,
     TERMINAL_CHEK,
     KASSA_CHEK,
     PASS_BACK)
  VALUES
    (:ID,
     :PROGRAM_ID,
     :OPERATOR_ID,
     :EVENT_TARA_ID,
     :EVENT_BRUTTO_ID,
     :REAL_NETTO,
     :LOSS,
     :NETTO,
     :PRICE,
     :PRICE_TONNA,
     :TERMINAL_CHEK,
     :KASSA_CHEK,
     :PASS_BACK);
  /************************************************/
  IF (WEIGH_TWICE = 1)
  THEN
    BEGIN
      UPDATE
        TRANSPORT
      SET
        EVENT_BRUTTO_ID = NULL,
        EVENT_TARA_ID = NULL
      WHERE
        ID = :TRANSPORT_ID;
    END
  ELSE
    BEGIN
      UPDATE
        TRANSPORT
      SET
        EVENT_BRUTTO_ID = NULL
      WHERE
        ID = :TRANSPORT_ID;
    END
  /************************************************/
  suspend;
END;

ALTER PROCEDURE CHANGE_EVENT_NETTO (
    EVENT_NETTO_ID INTEGER,
    PROGRAM_ID INTEGER,
    OPERATOR_ID INTEGER,
    CARGO_ID INTEGER,
    POLUCHATEL_ID INTEGER,
    PLATELSHIK_ID INTEGER,
    OTPRAVITEL_ID INTEGER,
    DRIVER_ID INTEGER,
    ORGANIZATION_TRANSPORT_ID INTEGER,
    PAYMENT_ID INTEGER,
    DIRECTION INTEGER,
    ORGANIZATION_LIMIT INTEGER,
    PRICE_CARGO DOUBLE PRECISION,
    TRANSPORT_ID INTEGER,
    NETTO DOUBLE PRECISION,
    BRUTTO DOUBLE PRECISION,
    TERMINAL_CHEK VARCHAR(32),
    KASSA_CHEK VARCHAR(32),
    VALUME DOUBLE PRECISION,
    CARGO_DANGER_ID INTEGER)
AS
DECLARE VARIABLE EVENT_BRUTTO_ID INTEGER;
DECLARE VARIABLE EVENT_TARA_ID INTEGER;
DECLARE VARIABLE OLD_CARGO_ID INTEGER;
DECLARE VARIABLE OLD_PLATELSHIK_ID INTEGER;
DECLARE VARIABLE OLD_POLUCHATEL_ID INTEGER;
DECLARE VARIABLE ORGANIZATION_ID INTEGER;
DECLARE VARIABLE OLD_DIRECTION INTEGER;
DECLARE VARIABLE OLD_NETTO DOUBLE PRECISION;
DECLARE VARIABLE LOSS DOUBLE PRECISION;
DECLARE VARIABLE REAL_NETTO DOUBLE PRECISION;
DECLARE VARIABLE NETTO_KG INTEGER;
DECLARE VARIABLE PRICE_KOPP INTEGER;
DECLARE VARIABLE PRICE_RUB DOUBLE PRECISION;
BEGIN
  SELECT
    EVENT_NETTO.EVENT_BRUTTO_ID,
    EVENT_NETTO.EVENT_TARA_ID,
    EVENT_NETTO.NETTO,
    EVENT_NETTO.REAL_NETTO,
    EVENT_NETTO.LOSS,
    EVENT_BRUTTO.CARGO_ID,
    EVENT_BRUTTO.ORGANIZATION_PLATELSHIK_ID,
    EVENT_BRUTTO.ORGANIZATION_POLUCHATEL_ID,
    EVENT_BRUTTO.DIRECTION
  FROM
    EVENT_NETTO
    LEFT JOIN EVENT_BRUTTO ON EVENT_BRUTTO.ID = EVENT_NETTO.EVENT_BRUTTO_ID
  WHERE
    EVENT_NETTO.ID = :EVENT_NETTO_ID
  INTO
    :EVENT_BRUTTO_ID,
    :EVENT_TARA_ID,
    :OLD_NETTO,
    :REAL_NETTO,
    :LOSS,
    :OLD_CARGO_ID,
    :OLD_PLATELSHIK_ID,
    :OLD_POLUCHATEL_ID,
    :OLD_DIRECTION;

    IF (:ORGANIZATION_LIMIT = 0)
    THEN
      ORGANIZATION_ID = OLD_POLUCHATEL_ID;
    ELSE
      ORGANIZATION_ID = OLD_PLATELSHIK_ID;

    IF (:OLD_DIRECTION = 0) /*OLD IN CARGO */
    THEN
      BEGIN
        UPDATE
          ORGANIZATION_CARGO
        SET
          REMAINDER = (REMAINDER - :OLD_NETTO)
        WHERE
          (CARGO_ID = :OLD_CARGO_ID) AND
          (ORGANIZATION_ID = :ORGANIZATION_ID);
      END

    IF (:OLD_DIRECTION = 1) /*OLD OUT GARGO */
    THEN
      BEGIN
        UPDATE
          ORGANIZATION_CARGO
        SET
          REMAINDER = (REMAINDER + :OLD_NETTO)
        WHERE
          (CARGO_ID = :OLD_CARGO_ID) AND
          (ORGANIZATION_ID = :ORGANIZATION_ID);
      END

    IF (:ORGANIZATION_LIMIT = 0)
    THEN
      ORGANIZATION_ID = POLUCHATEL_ID;
    ELSE
      ORGANIZATION_ID = PLATELSHIK_ID;

    IF (:CARGO_ID <> :OLD_CARGO_ID)
    THEN
      BEGIN
        LOSS = 0;

        SELECT
          LOSS
        FROM
          CARGO
        WHERE
          ID = :CARGO_ID
        INTO
          :LOSS;
      END

    NETTO_KG = ((NETTO / (100 - LOSS)) * 100) * 1000;
    REAL_NETTO = NETTO_KG;
    REAL_NETTO = REAL_NETTO / 1000;

    IF (:DIRECTION = 0) /*NEW IN CARGO */
    THEN
      BEGIN
        UPDATE
          ORGANIZATION_CARGO
        SET
          REMAINDER = (REMAINDER + :NETTO)
        WHERE
          (CARGO_ID = :CARGO_ID) AND
          (ORGANIZATION_ID = :ORGANIZATION_ID);
      END

    IF (:DIRECTION = 1) /*NEW OUT GARGO */
    THEN
      BEGIN
        UPDATE
          ORGANIZATION_CARGO
        SET
          REMAINDER = (REMAINDER - :NETTO)
        WHERE
          (CARGO_ID = :CARGO_ID) AND
          (ORGANIZATION_ID = :ORGANIZATION_ID);
      END

    UPDATE
      EVENT_TARA
    SET
      PROGRAM_ID = :PROGRAM_ID,
      OPERATOR_ID = :OPERATOR_ID,
      TARA = :BRUTTO - :REAL_NETTO,
      TRANSPORT_ID = :TRANSPORT_ID
    WHERE
      ID = :EVENT_TARA_ID;

    UPDATE
      EVENT_BRUTTO
    SET
      PROGRAM_ID = :PROGRAM_ID,
      OPERATOR_ID = :OPERATOR_ID,
      CARGO_ID = :CARGO_ID,
      DIRECTION = :DIRECTION,
      BRUTTO = :BRUTTO,
      TRANSPORT_ID = :TRANSPORT_ID,
      ORGANIZATION_POLUCHATEL_ID = :POLUCHATEL_ID,
      ORGANIZATION_OTPRAVITEL_ID = :OTPRAVITEL_ID,
      ORGANIZATION_PLATELSHIK_ID = :PLATELSHIK_ID,
      ORGANIZATION_TRANSPORT_ID = :ORGANIZATION_TRANSPORT_ID,
      DRIVER_ID = :DRIVER_ID,
      PAYMENT_ID = :PAYMENT_ID,
      VALUME = :VALUME,
      CARGO_DANGER_ID = :CARGO_DANGER_ID
    WHERE
      ID = :EVENT_BRUTTO_ID;

    PRICE_KOPP = NETTO * PRICE_CARGO * 100;
    PRICE_RUB = PRICE_KOPP;
    PRICE_RUB = PRICE_RUB / 100;

    UPDATE
      EVENT_NETTO
    SET
      PROGRAM_ID = :PROGRAM_ID,
      OPERATOR_ID = :OPERATOR_ID,
      NETTO = :NETTO,
      REAL_NETTO = :REAL_NETTO,
      LOSS = 0,
      PRICE = :PRICE_RUB,
      PRICE_TONNA = :PRICE_CARGO,
      KASSA_CHEK = :KASSA_CHEK,
      TERMINAL_CHEK = :TERMINAL_CHEK
    WHERE
      ID = :EVENT_NETTO_ID;
END;
