FIN PLACE

CREATE TABLE fin_place
(
  placekey      dintkey,    /* уникальный идентификатор  */
  parent        INTEGER,    /* ссылка на родителя        */  
  placekind     dintkey,    /* тип места                 */
  name          dname,      /* наименование              */  
  comment       dtext,      /* комментарий               */   
  disabled      dboolean,   /* признак: не используется  */
  security      INTEGER,    /* дескриптор безопасности   */
  icon          INTEGER,    /* иконка                    */
  reserved      INTEGER     /* зарезервировано           */   
);
 
ALTER TABLE fin_place ADD CONSTRAINT fin_pk_place
  PRIMARY KEY (placekey);
 
ALTER TABLE fin_place ADD CONSTRAINT fin_fk_place_parent
  FOREIGN KEY (parent) REFERENCES fin_place (placekey);
 
ALTER TABLE fin_place ADD CONSTRAINT fin_fk_place_placekind
  FOREIGN KEY (placekind) REFERENCES fin_placekind (placekindkey);
 
CREATE ASC INDEX fin_x_place_name
  ON fin_place (name);
 
CREATE ASC INDEX fin_x_place_disabled
  ON fin_place (disabled);
 
CREATE ASC INDEX fin_x_place_security
  ON fin_place (security);
 
CREATE GENERATOR fin_g_placekey;
SET GENERATOR fin_g_placekey TO 2000;
 
SET TERM ^ ;
 
CREATE EXCEPTION fin_e_invalidplace "Неверный адрес (Не соблюдается иерархия)"^
CREATE EXCEPTION fin_e_duplicateplace "Адрес дублируется"^
 
CREATE TRIGGER fin_insert_place FOR fin_place
  BEFORE INSERT
  POSITION 0
AS
  DECLARE VARIABLE G1 SMALLINT;
  DECLARE VARIABLE G2 SMALLINT;
  DECLARE VARIABLE C  INTEGER;
BEGIN
  /* соблюдаем иерархию */
  SELECT grade FROM fin_placekind
  WHERE placekindkey = NEW.placekind
  INTO :G1;
 
  IF (NEW.parent IS NULL) THEN
  BEGIN
    IF (:G1 <> 200) THEN
      EXCEPTION fin_e_invalidplace;
  END ELSE
  BEGIN
    SELECT grade FROM fin_placekind
    WHERE placekindkey = (SELECT placekind FROM fin_place WHERE placekey = NEW.parent)
    INTO :G2;
 
    IF (:G1 <= :G2) THEN
      EXCEPTION fin_e_invalidplace;
  END
 
  SELECT COUNT(*) FROM fin_place
    WHERE parent = NEW.parent AND g_s_ansicomparetext(name, NEW.name) = 0
    INTO :C;
 
  /*IF (:C > 0) THEN
    EXCEPTION fin_e_duplicateplace; */
 
  /* присваиваем ключ */
  IF (NEW.placekey IS NULL) THEN
    NEW.placekey = GEN_ID(fin_g_placekey, 1);
 
  /* значения по умолчанию */
  IF (NEW.disabled IS NULL) THEN
    NEW.disabled = 0;
END;
^
 
CREATE PROCEDURE cst_p_placekey
  RETURNS (placekey INTEGER)
AS
BEGIN
  placekey = GEN_ID(fin_g_placekey, 1);
END;
^
 
CREATE PROCEDURE fin_p_plc_saveplace(Place INTEGER, Building VARCHAR(8),
  Part VARCHAR(8), Office VARCHAR(8))
  RETURNS (
    PlaceKey INTEGER
  )
AS
  DECLARE VARIABLE C INTEGER;
  DECLARE VARIABLE BK INTEGER;
  DECLARE VARIABLE PK INTEGER;
  DECLARE VARIABLE OK INTEGER;
BEGIN
  /* если такого строения нет, то добавляем его */
  SELECT COUNT(*) FROM fin_place
  WHERE parent = :Place AND name = :Building
  INTO :C;
 
  IF (:C = 0) THEN
  BEGIN
    INSERT INTO fin_place(parent, placekind, name)
      VALUES (:Place, 120, :Building);
  END
 
  /* берем ключ строения */
  SELECT placekey FROM fin_place
  WHERE parent = :Place AND name = :Building
  INTO :BK;
 
  /* корпуса может и не быть */
  IF (:Part <> "") THEN
  BEGIN
    SELECT COUNT(*) FROM fin_place
    WHERE parent = :BK AND name = :Part
    INTO :C;
 
    IF (:C = 0) THEN
    BEGIN
      INSERT INTO fin_place(parent, placekind, name)
        VALUES (:BK, 130, :Part);
    END
 
    SELECT placekey FROM fin_place
    WHERE parent = :BK AND name = :Part
    INTO :PK;
  END ELSE
  BEGIN
    PK = :BK;
  END
 
  /* квартиры может и не быть */
  IF (:Office <> "") THEN
  BEGIN
    SELECT COUNT(*) FROM fin_place
    WHERE parent = :PK AND name = :Office
    INTO :C;
 
    IF (:C = 0) THEN
    BEGIN
      INSERT INTO fin_place(parent, placekind, name)
        VALUES (:PK, 140, :Office);
    END
 
    SELECT placekey FROM fin_place
    WHERE parent = :PK AND name = :Office
    INTO :OK;
  END ELSE
  BEGIN
    OK = :PK;
  END
 
  PlaceKey = :OK;
END;
^
 
CREATE PROCEDURE fin_p_plc_saveplace_newstreet(Place INTEGER, Street VARCHAR(60),
  Building VARCHAR(8), Part VARCHAR(8), Office VARCHAR(8))
  RETURNS (
    PlaceKey INTEGER
  )
AS
  DECLARE VARIABLE C INTEGER;
  DECLARE VARIABLE SK INTEGER;
BEGIN
  /* если такого города нет, то добавляем его */
  SELECT COUNT(*) FROM fin_place
  WHERE parent = :Place AND name = :Street
  INTO :C;
 
  IF (:C = 0) THEN
  BEGIN
    INSERT INTO fin_place(parent, placekind, name)
      VALUES (:Place, 70, :Street);
  END
 
  /* берем ключ города */
  SELECT placekey FROM fin_place
  WHERE parent = :Place AND name = :Street
  INTO :SK;
 
  EXECUTE PROCEDURE fin_p_plc_saveplace (:SK, Building, Part, Office)
    RETURNING_VALUES :PlaceKey;
END;
^
 
CREATE PROCEDURE fin_p_plc_place2str(PlaceKey INTEGER)
  RETURNS (
    FullAddress VARCHAR(255)
  )
AS
  DECLARE VARIABLE Parent INTEGER;
  DECLARE VARIABLE Name VARCHAR(255);
  DECLARE VARIABLE PlaceKind INTEGER;
BEGIN
  SELECT parent, name, placekind
  FROM fin_place WHERE placekey = :PlaceKey
    INTO :Parent, :Name, :PlaceKind;
 
  IF (:Parent IS NULL) THEN
  BEGIN
    FullAddress = :Name;
  END ELSE
  BEGIN
    EXECUTE PROCEDURE fin_p_plc_place2str (:Parent)
      RETURNING_VALUES :FullAddress;
 
    FullAddress = :FullAddress || ", " || :Name;
  END
END;
^
 
CREATE PROCEDURE fin_p_plc_addrlist(PlaceKey INTEGER, DetailKey INTEGER)
  RETURNS (
    FullAddress VARCHAR(255)
  )
AS
  DECLARE VARIABLE K INTEGER;
  DECLARE VARIABLE PK INTEGER;
BEGIN
  FOR
    SELECT placekey, placekind FROM fin_place
    WHERE parent = :PlaceKey
    INTO :K, :PK
  DO BEGIN
    EXECUTE PROCEDURE fin_p_plc_place2str (:K)
      RETURNING_VALUES :FullAddress;
 
    SUSPEND;
  END
END;
^
 
CREATE PROCEDURE fin_p_plc_deleteplace(PlaceKey INTEGER)
  RETURNS (
    RESULT INTEGER
  )
AS
  DECLARE VARIABLE PK INTEGER;
BEGIN
  FOR
    SELECT placekey FROM fin_place
    WHERE parent = :PlaceKey
    INTO :PK
  DO
  BEGIN
    EXECUTE PROCEDURE fin_p_plc_deleteplace(:PK)
      RETURNING_VALUES :RESULT;
  END
 
  DELETE FROM fin_place WHERE placekey = :PlaceKey;
 
  RESULT = 1;
END;
^
 
CREATE PROCEDURE fin_p_plc_expandplckey(PlaceKey INTEGER)
  RETURNS (
    CountryKey   INTEGER,
    CountryName  VARCHAR(255),
    ProvinceKey  INTEGER,
    ProvinceName VARCHAR(255),
    DistrictKey  INTEGER,
    DistrictName VARCHAR(255),
    TownKey      INTEGER,
    TownName     VARCHAR(255),
    StreetKey    INTEGER,
    StreetName   VARCHAR(255),
    BuildingKey  INTEGER,
    BuildingName VARCHAR(8),
    PartKey      INTEGER,
    PartName     VARCHAR(8),
    OfficeKey    INTEGER,
    OfficeName   VARCHAR(8)
  )
AS
  DECLARE VARIABLE PK     INTEGER;
  DECLARE VARIABLE Name   VARCHAR(255);
BEGIN
  CountryKey = NULL;
  CountryName = "";
  ProvinceKey = NULL;
  ProvinceName = "";
  DistrictKey = NULL;
  DistrictName = "";
  TownKey = NULL;
  TownName = "";
  StreetKey = NULL;
  StreetName = "";
  BuildingKey = NULL;
  BuildingName = "";
  PartKey = NULL;
  PartName = "";
  OfficeKey = NULL;
  OfficeName = "";
 
  WHILE (NOT :PlaceKey IS NULL) DO
  BEGIN
    SELECT placekind, name FROM fin_place WHERE placekey = :PlaceKey
    INTO :PK, :Name;
 
    IF (:PK >= 140 AND :PK <= 170) THEN
    BEGIN
      OfficeKey = :PlaceKey;
      OfficeName = :Name;
    END
 
    IF (:PK = 130) THEN
    BEGIN
      PartKey = :PlaceKey;
      PartName = :Name;
    END
 
    IF (:PK = 120) THEN
    BEGIN
      BuildingKey = :PlaceKey;
      BuildingName = :Name;
    END
 
    IF (:PK >= 70 AND :PK <= 110) THEN
    BEGIN
      StreetKey = :PlaceKey;
      StreetName = :Name;
    END
 
    IF (:PK >= 40 AND :PK <= 60) THEN
    BEGIN
      TownKey = :PlaceKey;
      TownName = :Name;
    END
 
    IF (:PK = 30) THEN
    BEGIN
      DistrictKey = :PlaceKey;
      DistrictName = :Name;
    END
 
    IF (:PK = 20) THEN
    BEGIN
      ProvinceKey = :PlaceKey;
      ProvinceName = :Name;
    END
 
    IF (:PK = 10) THEN
    BEGIN
      CountryKey = :PlaceKey;
      CountryName = :Name;
    END
 
    SELECT parent FROM fin_place WHERE placekey = :PlaceKey
      INTO :PlaceKey;
  END
END;
^
 
CREATE PROCEDURE fin_p_plc_subplacelist(ParentKey INTEGER)
  RETURNS (
    PlaceKey INTEGER
  )
AS 
  DECLARE VARIABLE PK INTEGER;
BEGIN
  FOR
    SELECT placekey 
    FROM fin_place
    WHERE placekey = :ParentKey
    INTO :PlaceKey
  DO 
    SUSPEND;
 
  FOR
    SELECT placekey 
    FROM fin_place
    WHERE parent = :ParentKey
    INTO :PK
  DO 
    FOR
      SELECT placekey 
      FROM fin_p_plc_subplacelist(:PK)
      INTO :PlaceKey
    DO
      SUSPEND;
END;	
^
 
SET TERM ; ^