CREATE GENERATOR GEN_PULT_ID;

CREATE TABLE PULT (
    ID    INTEGER NOT NULL,
    CODE  VARCHAR(16) NOT NULL,
    LOSS  DOUBLE PRECISION NOT NULL,
    NAME  VARCHAR(32) NOT NULL
);

ALTER TABLE PULT ADD CONSTRAINT UNQ1_PULT UNIQUE (CODE);
ALTER TABLE PULT ADD CONSTRAINT PK_PULT PRIMARY KEY (ID);

CREATE OR ALTER TRIGGER PULT_BI FOR PULT
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL)
  THEN
    NEW.ID = GEN_ID(GEN_PULT_ID, 1);
END;

ALTER TABLE EVENT_BRUTTO ADD REAL_BRUTTO DOUBLE PRECISION NOT NULL;

UPDATE EVENT_BRUTTO SET REAL_BRUTTO = BRUTTO;

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

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

  SUSPEND;
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,
    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)
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)
  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;
