ALTER TABLE PROGRAM ADD TTN_FORMAT VARCHAR(32);

CREATE TABLE TARA_FOTO (
    EVENT_TARA_ID  INTEGER NOT NULL,
    DT_FOTO        TIMESTAMP NOT NULL,
    AUTO_NUMBER    VARCHAR(16)
);

ALTER TABLE TARA_FOTO ADD CONSTRAINT FK_TARA_FOTO_1 FOREIGN KEY (EVENT_TARA_ID) REFERENCES EVENT_TARA (ID) ON DELETE CASCADE;

CREATE TABLE BRUTTO_FOTO (
    EVENT_BRUTTO_ID  INTEGER NOT NULL,
    DT_FOTO          TIMESTAMP NOT NULL,
    AUTO_NUMBER      VARCHAR(16)
);

ALTER TABLE BRUTTO_FOTO ADD CONSTRAINT FK_BRUTTO_FOTO_1 FOREIGN KEY (EVENT_BRUTTO_ID) REFERENCES EVENT_BRUTTO (ID) ON DELETE CASCADE;

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_TONNA 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,
    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;
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_TONNA;
  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_TONNA,
     :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 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,
    CLEAR_TRANSPORT integer,
    ORGANIZATION_LIMIT_ID integer,
    CARD_ID integer)
returns (
    ID integer,
    EVENT_TARA_ID integer)
as
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_TARA(
     :PROGRAM_ID,
     :OPERATOR_ID,
     :TRANSPORT_ID,
     :TARA,
     :COUNT_WEIGHT,
     :CARD_ID)
  RETURNING_VALUES
    :EVENT_TARA_ID;
  /************************************************/
  REAL_NETTO = BRUTTO - TARA;
  NETTO = REAL_NETTO - (REAL_NETTO/100*LOSS);
  /************************************************/
  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 = NETTO * PRICE_TONNA;
  INSERT INTO
    EVENT_NETTO
    (ID,
     PROGRAM_ID,
     OPERATOR_ID,
     EVENT_TARA_ID,
     EVENT_BRUTTO_ID,
     REAL_NETTO,
     LOSS,
     NETTO,
     PRICE,
     PRICE_TONNA)
  VALUES
    (:ID,
     :PROGRAM_ID,
     :OPERATOR_ID,
     :EVENT_TARA_ID,
     :EVENT_BRUTTO_ID,
     :REAL_NETTO,
     :LOSS,
     :NETTO,
     :PRICE,
     :PRICE_TONNA);
  /************************************************/
  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;

