CREATE TABLE ID (
    BASE_ID  VARCHAR(32) NOT NULL
);

ALTER TABLE ID ADD CONSTRAINT UNQ1_ID UNIQUE (BASE_ID);

CREATE OR ALTER TRIGGER ID_BI FOR ID
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS
begin
  IF (NEW.BASE_ID IS NULL)
  THEN
    NEW.BASE_ID = '';
end;

INSERT INTO ID (BASE_ID) VALUES ('');

ALTER TABLE EVENT_NETTO ADD TERMINAL_CHEK VARCHAR(32);

ALTER TABLE EVENT_NETTO ADD KASSA_CHEK VARCHAR(32);

CREATE OR ALTER TRIGGER EVENT_NETTO_BI FOR EVENT_NETTO
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL)
  THEN
    NEW.ID = GEN_ID(GEN_EVENT_NETTO_ID, 1);

  IF (NEW.DT_EVENT IS NULL)
  THEN
    NEW.DT_EVENT = 'NOW';

  IF (NEW.KASSA_CHEK = '')
  THEN
    NEW.KASSA_CHEK = NULL;

  IF (NEW.TERMINAL_CHEK = '')
  THEN
    NEW.TERMINAL_CHEK = NULL;
END;

CREATE OR ALTER TRIGGER EVENT_NETTO_BU FOR EVENT_NETTO
ACTIVE BEFORE UPDATE POSITION 0
AS
BEGIN
  IF (NEW.KASSA_CHEK = '')
  THEN
    NEW.KASSA_CHEK = NULL;

  IF (NEW.TERMINAL_CHEK = '')
  THEN
    NEW.TERMINAL_CHEK = NULL;
END;

CREATE OR 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,
    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_CARGO double precision,
    LOSS double precision,
    CLEAR_TRANSPORT integer,
    ORGANIZATION_LIMIT integer,
    CARD_ID integer,
    TERMINAL_CHEK varchar(32),
    KASSA_CHEK varchar(32))
returns (
    ID integer)
as
declare variable EVENT_BRUTTO_ID integer;
declare variable NETTO double precision;
declare variable PRICE double precision;
declare variable REMAINDER double precision;
declare variable REAL_NETTO double precision;
begin
  /************************************************/
  EXECUTE PROCEDURE
    SAVE_BUTTO(
     :PROGRAM_ID,
     :OPERATOR_ID,
     :CARGO_ID,
     :TRANSPORT_ID,
     :BRUTTO,
     :DIRECTION,
     :COUNT_WEIGHT,
     :ORGANIZATION_POLUCHATEL_ID,
     :ORGANIZATION_OTPRAVITEL_ID,
     :ORGANIZATION_PLATELSHIK_ID,
     :ORGANIZATION_TRANSPORT_ID,
     :DRIVER_ID,
     :PAYMENT_ID,
     :CARD_ID)
  RETURNING_VALUES
    :EVENT_BRUTTO_ID;
  /************************************************/
  REAL_NETTO = BRUTTO - TARA;
  NETTO = REAL_NETTO - (REAL_NETTO/100*LOSS);
  /************************************************/
    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 = NETTO * PRICE_CARGO;
  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)
  VALUES
    (:ID,
     :PROGRAM_ID,
     :OPERATOR_ID,
     :EVENT_TARA_ID,
     :EVENT_BRUTTO_ID,
     :REAL_NETTO,
     :LOSS,
     :NETTO,
     :PRICE,
     :PRICE_CARGO,
     :TERMINAL_CHEK,
     :KASSA_CHEK);
  /************************************************/
  IF (CLEAR_TRANSPORT = 1)
  THEN
    EVENT_TARA_ID = NULL;

  UPDATE
    TRANSPORT
  SET
    EVENT_BRUTTO_ID = NULL, 
    EVENT_TARA_ID = :EVENT_TARA_ID
  WHERE
    ID = :TRANSPORT_ID;
  /************************************************/
  suspend;
end;

CREATE OR 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))
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;
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

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

    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
    WHERE
      ID = :EVENT_BRUTTO_ID;

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