Форум пользователей MySQL

mysql> select GetCurUserFullname();
+----------------------+
| GetCurUserFullname() |
+----------------------+
| main root            |
+----------------------+
1 row in set (0.00 sec)

Добавление договора:
  1.) Открываю транзакцию.
  2.) Заношу сопутствующие данные:
    а.) Список водителей в БД (открывает транзакцию). Через процедуру DriverAdd.
    б.) Резервирую бланк (открывает транзакцию). BlankReserve.
      Всем соединениям должно быть известно, что данный бланк зарезервирован.
      Если транзакция не завершается, бланк нужно освободить.
  3.) Заношу договор в БД.
    1. Закрепляю бланк за договором.
    2. Вставляю договор.
    3. Переношу список водителей в БД.
  4.) Завершаю транзакцию.

/*==============================================================*/
/* Procedure : BlankReserve                                     */
/*==============================================================*/

DROP PROCEDURE IF EXISTS BlankReserve;

CREATE
DEFINER = 'root'@'%'
PROCEDURE BlankReserve(
  IN v_id_ins_company INT,
  IN v_dog_ser VARCHAR(10), IN v_dog_num VARCHAR(25),
  OUT err_code TINYINT)
SQL SECURITY DEFINER
COMMENT 'Предположительные привилегии: пользователь.'
sproc: BEGIN
  declare granted BOOL;
  declare user_name CHAR(16);
  declare user_host CHAR(60);
  set err_code := 1;

  # Проверяю полномочия
  select ifnull(PRIV_CONT_ADD = true, false) from user_groups
    where ID_GROUP = GetGroupID() into granted;
  if (not granted) then
    leave sproc;
  end if;

  # Проверяю наличие пустого незарезервированного бланка
  select count(*) != 0 into granted from blanks_journal where
    DOG_SER = v_dog_ser and DOGNUMB = v_dog_num
    and ID_INSURANCE_COMPANY = v_id_ins_company
    and USER_RESERVER is NULL and HOST_RESERVER is NULL
    and ID_BSO_STATUS = 1;

  if (not granted) then
    leave sproc;
  end if;

  call GetCurUser(user_name, user_host);

  update blanks_journal set
    USER_RESERVER       = user_name,
    HOST_RESERVER       = user_host,
    DATE_UPDATE         = CURDATE()
  where
    DOG_SER = v_dog_ser and DOGNUMB = v_dog_num
    and ID_INSURANCE_COMPANY = v_id_ins_company;

  set err_code := 0;
END;

/*==============================================================*/
/* Procedure : BlankUnreserve                                   */
/*==============================================================*/

DROP PROCEDURE IF EXISTS BlankReserve;

CREATE
DEFINER = 'root'@'%'
PROCEDURE BlankUnreserve(
  IN v_id_ins_company INT,
  IN v_dog_ser VARCHAR(10), IN v_dog_num VARCHAR(25),
  OUT err_code TINYINT)
SQL SECURITY DEFINER
COMMENT 'Предположительные привилегии: пользователь.'
sproc: BEGIN
  declare granted BOOL;
  declare user_name CHAR(16);
  declare user_host CHAR(60);
  # Данные для udpate ... where
  declare real_id_ins_company INT default v_id_ins_company;
  declare real_dog_ser VARCHAR(10) default v_dog_ser;
  declare real_dog_num VARCHAR(25) default v_dog_num;

  set err_code := 1;

  # Проверяю полномочия
  select ifnull(PRIV_CONT_ADD = true, false) from user_groups
    where ID_GROUP = GetGroupID() into granted;
  if (not granted) then
    leave sproc;
  end if;

  call GetCurUser(user_name, user_host);

  # Проверяю наличие данного зарезервированного бланка.

  if (v_dog_ser is NULL or v_dog_num is NULL or v_id_ins_company is NULL) then
  # Если заданы NULL, то проверяю резервировал ли этот пользователь какие-либо
  # бланки, вообще. Выбираю самый новый.
    select count(*) != 0, ID_INSURANCE_COMPANY, DOG_SER, DOGNUMB
      into granted, real_id_ins_company, real_dog_ser, real_dog_num
      from blanks_journal where 
      USER_RESERVER = user_name and HOST_RESERVER = user_host
      and ID_BSO_STATUS = 1
      order by DATE_UPDATE desc limit 1;
  else
    select BlankIsReserved(v_dog_ser, v_dog_num, v_id_ins_company) into granted;
  end if;

  if (not granted) then
    leave sproc;
  end if;

  update blanks_journal set
    USER_RESERVER       = NULL,
    HOST_RESERVER       = NULL,
    DATE_UPDATE         = CURDATE()
  where
    DOG_SER = real_dog_ser and DOGNUMB = real_dog_num
    and ID_INSURANCE_COMPANY = real_id_ins_company;

  set err_code := 0;
END;

/*==============================================================*/
/* Procedure : DriverAdd()                                      */
/*==============================================================*/

DROP PROCEDURE IF EXISTS DriverAdd;

CREATE
DEFINER = 'root'@'%'
PROCEDURE DriverAdd(
  IN v_id_client DECIMAL(23, 0),
  OUT err_code TINYINT)
SQL SECURITY DEFINER
COMMENT 'Предположительные привилегии: пользователь.'
sproc: BEGIN
  declare granted BOOL;

  set err_code := 1;

  # Проверяю полномочия
  select ifnull(PRIV_CONT_ADD = true, false) or
    ifnull(PRIV_CONT_CHG = true, false) from user_groups
    where ID_GROUP = GetGroupID() into granted;
  if (not granted) then
    leave sproc;
  end if;

  create temporary table if not exists drivers_in_contract
  (
    ID_CLIENT DECIMAL(23, 0),
    primary key(ID_CLIENT)
  ) ENGINE=InnoDB;
  # Не MEMORY. Транзакции.
  # replace select * from drivers limit 0;

  insert into drivers_in_contract values(v_id_client);

  set err_code := 0;
END;

/*==============================================================*/
/* Function : GetNextIDDriver()                                 */
/*==============================================================*/

DROP FUNCTION IF EXISTS GetNextIDDriver;

CREATE
DEFINER = 'root'@'%'
FUNCTION GetNextIDDriver()
RETURNS DECIMAL(23, 0)
READS SQL DATA
SQL SECURITY INVOKER
COMMENT 'Возвращает свободный ID водителя. Привилегии вызывающего.'
BEGIN
  declare result DECIMAL(23, 0);
  select ifnull(max(ID_DRIVER) + 1, 0) into result from drivers;
  return result;
END;

/*==============================================================*/
/* Procedure : ContAdd()                                        */
/*==============================================================*/

DROP PROCEDURE IF EXISTS ContAdd;

CREATE
DEFINER = 'root'@'%'
PROCEDURE ContAdd(
  IN v_dog_ser VARCHAR(10),
  IN v_dog_numb VARCHAR(25),
  IN v_date_start DATE,
  IN v_date_end DATE,
  IN v_start_use DATE,
  IN v_end_use DATE,
  IN v_start_use1 DATE,
  IN v_end_use1 DATE,
  IN v_start_use2 DATE,
  IN v_end_use2 DATE,
  IN v_comment VARCHAR(255),
  IN v_id_client VARCHAR(22),
  IN v_id_insurance_company INT,
  IN v_transit BOOL,
  IN v_id_car VARCHAR(22),
  IN v_id_territory_use INT,
  IN v_ins_sum FLOAT,
  IN v_ins_prem FLOAT,
  IN v_koef_ter FLOAT,
  IN v_koef_bonus_malus FLOAT,
  IN v_koef_stag FLOAT,
  IN v_koef_unlimited FLOAT,
  IN v_koef_power FLOAT,
  IN v_koef_period_use FLOAT,
  IN v_koef_srok_ins FLOAT,
  IN v_koef_kn FLOAT,
  IN v_base_sum FLOAT,
  IN v_unlimited_drivers BOOL,
  IN v_date_write DATE,
  IN v_date_begin DATE,
  IN v_id_insurance_class INT,
  OUT v_id_contract DECIMAL(23, 0),
  OUT err_code TINYINT
)
SQL SECURITY DEFINER
COMMENT 'Предположительные привилегии: пользователь.'
sproc: BEGIN
  declare granted BOOL;
  declare user_name CHAR(16);
  declare user_host CHAR(60);
  declare creating_date DATE default CURDATE();
  declare i INT;
  declare ccln_id DECIMAL(23, 0);
  declare ccont_id DECIMAL(23, 0);
  # Обработка отсутствия временной таблицы с водителями.
  DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET i := 0;

  set err_code := 1;

  # Проверяю полномочия.
  select ifnull(PRIV_CONT_ADD = true, false) from user_groups
    where ID_GROUP = GetGroupID() into granted;
  if (not granted) then
    leave sproc;
  end if;

  call GetCurUser(user_name, user_host);

  # Проверяю серию/номер на соответствие зарезервированным.
  # Выбираю неиспользованный бланк, с заданными номерами, зарезервированный
  # текущим пользователем.
  select BlankIsReserved(v_id_insurance_company, v_dog_ser, v_dog_numb)
    into granted;

  if (not granted) then
    leave sproc;
  end if;

  # Если имеются права на изменение договора, проверка дат пропускается.
  select ifnull(PRIV_CONT_CHG = true, false) from user_groups
    where ID_GROUP = GetGroupID() into granted;
  if (not granted) then
    # Даты начала нельзя вводить задним числом.
    set granted := (v_date_begin >= creating_date)
      and (v_date_start >= creating_date);
    # Период не может быть больше срока действия.
    set granted :=
      granted and (v_date_start < v_date_end)
      and (v_start_use >= v_date_start)
      and (v_start_use < v_end_use)
      and (v_end_use <= v_date_end);
    # Прочие проверки.
    set granted :=
      granted
      and (((v_start_use1 < v_end_use1) and (v_end_use1 <= v_start_use2)
        and (v_end_use1 <= v_date_end))
        or ((v_start_use1 is NULL) and (v_end_use1 is NULL)))
      and (((v_start_use2 < v_end_use2) and (v_end_use <= v_start_use1)
        and (v_end_use2 <= v_date_end))
        or ((v_start_use2 is NULL) and (v_end_use2 is NULL)))
      and (v_date_write <= v_date_begin);

    if (not granted) then
      leave sproc;
    end if;
  end if;


  set autocommit = false;
  start transaction;

  select ifnull(max(ID_DOGOVOR) + 1, 0) into ccont_id from dogovor;

  insert into dogovor(ID_DOGOVOR, DOG_SER, DOGNUMB, DATE_DOG_CREATE,
    DATE_START, DATE_END,
    START_USE, END_USE, START_USE1, END_USE1, START_USE2, END_USE2,
    `COMMENT`, ID_CLIENT, ID_DOGOVOR_TYPE, ID_INSURANCE_COMPANY, TRANSIT,
    ID_CAR, ID_TERRITORY_USE, INS_SUM, INS_PREM, KOEF_TER, KOEF_BONUSMALUS,
    KOEF_STAG, KOEF_UNLIMITED, KOEF_POWER, KOEF_PERIOD_USE, KOEF_SROK_INS,
    KOEF_KN, BASE_SUM, UNLIMITED_DRIVERS, DATE_WRITE, DATE_BEGIN,
    ID_INSURANCE_CLASS, DATE_INSERT, USER_INSERT_NAME)
  values(ccont_id, v_dog_ser, v_dog_numb,
    creating_date, v_date_start, v_date_end,
    v_start_use, v_end_use, v_start_use1, v_end_use1, v_start_use2, v_end_use2,
    v_comment, v_id_client, 1, v_id_insurance_company, v_transit, v_id_car,
    v_id_territory_use, v_ins_sum, v_ins_prem, v_koef_ter, v_koef_bonus_malus,
    v_koef_stag, v_koef_unlimited, v_koef_power, v_koef_period_use,
    v_koef_srok_ins, v_koef_kn, v_base_sum, v_unlimited_drivers,
    v_date_write, v_date_begin,
    v_id_insurance_class, creating_date, GetCurUserFullname());

  # Перенос временного списка клиентов в список водителей по договору.
  # Если таблица отсутствует, HANDLER устанавливает i = 0.
  select COUNT(*) into i from drivers_in_contract;
  while (i > 0) do
    select ID_CLIENT into ccln_id from drivers_in_contract
      order by ID_CLIENT asc limit 0, 1;
    insert into drivers(ID_DRIVER, ID_CAR, ID_CLIENT, ID_DOGOVOR, DATE_INSERT)
      values(GetNextIDDriver(), v_id_car, ccln_id, ccont_id, CURDATE());
    delete from drivers_in_contract where ID_CLIENT = ccln_id;
    set i := i - 1;
  end while;

  update blanks_journal set
    ID_BSO_STATUS = 2
  where
    DOG_SER = v_dog_ser and DOGNUMB = v_dog_numb
    and ID_INSURANCE_COMPANY = v_id_insurance_company;

  commit;
  set v_id_contract := ccont_id;
  set err_code      := 0;
END;

CREATE
DEFINER = 'root'@'%'
PROCEDURE CarAdd(
  IN v_arenda BOOL,
  IN v_id_purpose_type INT,
  IN v_id_car_type INT,
  IN v_id_client VARCHAR(22),
  IN v_id_car_mark INT,
  IN v_car_model VARCHAR(255),
  IN v_vin_num VARCHAR(255),
  IN v_year_issue INT,
  IN v_power_kvt FLOAT,
  IN v_power_ls FLOAT,
  IN v_max_kg FLOAT,
  IN v_num_places INT,
  IN v_shassi VARCHAR(255),
  IN v_kusov VARCHAR(255),
  IN v_gos_num VARCHAR(255),
  IN v_pts_date DATE,
  IN v_pts_ser VARCHAR(255),
  IN v_pts_no VARCHAR(255),
  IN v_foreing BOOL,
  IN v_comments TEXT,
  OUT v_id_car DECIMAL(23, 0)
)
SQL SECURITY DEFINER
COMMENT 'Предположительные привилегии: пользователь.'
sproc: BEGIN
  declare granted BOOL;
  declare car_id DECIMAL(23, 0);
  # Дублирование ключа.
  DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET car_id := car_id + 1;

  # Проверяю полномочия.
  select ifnull(PRIV_CAR_ADD = true, false) from user_groups
    where ID_GROUP = GetGroupID() into granted;
  if (not granted) then
    call SetLastError('ERR_NOT_ENOUGH_RIGHTS');
    leave sproc;
  end if;

  select ifnull(max(ID_CAR) + 1, 0) into car_id from car;

  repeat
    insert into car(ID_CAR, CAR_MARK, CAR_MODEL, VIN_NUM, YEAR_ISSUE, POWER_KVT,
      POWER_LS, MAX_KG, NUM_PLACES, SHASSI, KUSOV, GOS_NUM, PTS_SER, PTS_NO,
      PTS_DATE, ARENDA, ID_PURPOSE_TYPE, ID_CAR_TYPE, ID_CLIENT, FOREING,
      COMMENTS, DATE_INSERT, USER_UPDATE_NAME)
    values(car_id, v_id_car_mark, v_car_model, v_vin_num, v_year_issue,
      v_power_kvt, v_power_ls, v_max_kg, v_num_places, v_shassi, v_kusov,
      v_gos_num, v_pts_ser, v_pts_no, v_pts_date, v_arenda, v_id_purpose_type,
      v_id_car_type, v_id_client, v_foreing, v_comments,
      CURDATE(), GetCurUserFullname());
    # Проверяю было ли вставлено ТС.
    select ROW_COUNT() >= 1 into granted;
  until (granted)
  end repeat;
  set v_id_car := car_id;
  call SetLastError('ERR_NO_ERROR');
END;