Îñíîâíûå ïîäõîäû ê ïðîåêòèðîâàíèþ ðàñïðåäåëåííûõ áàç äàííûõ

       

CREATE DOMAIN CALLTIME_TYPE INTEGER NOT


SQL-ñêðèïò äëÿ ãåíåðàöèè áàçû äàííûõ
CREATE GENERATOR genUslPropsKeys;
CREATE GENERATOR genUslProps;
CREATE GENERATOR genPhonesRegions;
CREATE GENERATOR genPhonesStations;
CREATE GENERATOR genPhonesStreets;
CREATE GENERATOR genPhonesBanks;
CREATE GENERATOR genTalksPay;
CREATE GENERATOR genTalks;
CREATE GENERATOR genNach;
CREATE GENERATOR genNachBillings;
CREATE GENERATOR genNachBillDates;
CREATE GENERATOR genNachConstUsl;
CREATE GENERATOR genUslDivisions;
CREATE GENERATOR genUslLgots;
CREATE GENERATOR genUslsKeys;




CREATE GENERATOR genUsls;
CREATE GENERATOR genUslCatKeys;
CREATE GENERATOR genUslCat;
CREATE GENERATOR genPhones;
CREATE GENERATOR genPhonesOwnersKeys;
CREATE GENERATOR genPhonesOwners;
CREATE GENERATOR genSysSettings;
CREATE GENERATOR genPhonesKeys;
CREATE GENERATOR genPlat;
CREATE GENERATOR genPhonesPostStations;
CREATE GENERATOR genSysLog;
CREATE GENERATOR genUslTypes;
CREATE GENERATOR genUslDivisionsKeys;
 
CREATE DOMAIN CALLTIME_TYPE INTEGER NOT NULL;
CREATE DOMAIN CURR_TYPE FLOAT DEFAULT 0 NOT NULL;
CREATE DOMAIN DATE_TYPE DATE NOT NULL;
CREATE DOMAIN DESCR_TYPE CHAR(32);
CREATE DOMAIN PHONE_TYPE CHAR(7) NOT NULL;
CREATE DOMAIN PROCENT_TYPE FLOAT DEFAULT 100 NOT NULL
                                   CHECK (VALUE BETWEEN 0 AND 300);
CREATE TABLE Nach (
       Code                 INTEGER NOT NULL,
       Owner                INTEGER NOT NULL,
       Usl                  INTEGER NOT NULL,
       Phone                INTEGER,
       UslSum               CURR_TYPE,
       NachDate             DATE_TYPE,
       BillDate             DATE_TYPE
);
ALTER TABLE Nach
       ADD CONSTRAINT XPKNach PRIMARY KEY (Code);


CREATE TABLE NachBillDates (
       Code                 INTEGER NOT NULL,
       BillingDate          INTEGER NOT NULL
);
ALTER TABLE NachBillDates
       ADD CONSTRAINT XPKBillDates PRIMARY KEY (Code);
CREATE TABLE NachBillings (
       Code                 INTEGER NOT NULL,
       Division             INTEGER NOT NULL,
       Owner                INTEGER NOT NULL,
       BillDateCode         INTEGER NOT NULL
);
ALTER TABLE NachBillings
       ADD CONSTRAINT XPKNachBillings PRIMARY KEY (Code);
CREATE TABLE NachConstUsl (
       Code                 INTEGER NOT NULL,
       Owner                INTEGER NOT NULL,
       Usl                  INTEGER NOT NULL,
       Phone                INTEGER NOT NULL,
       UslSum               CURR_TYPE,
       BegDate              DATE_TYPE,
       EndDate              DATE_TYPE
);
ALTER TABLE NachConstUsl
       ADD CONSTRAINT XPKNachConstUsl PRIMARY KEY (Code);
CREATE TABLE Phones (
       Code                 INTEGER NOT NULL,
       Street               INTEGER NOT NULL,
       Owner                INTEGER NOT NULL,
       PKey                 INTEGER NOT NULL,
       Comment              DESCR_TYPE,
       PhoneNmb             PHONE_TYPE,
       InstallDate          DATE_TYPE,
       RemoveDate           DATE_TYPE,
       BegDate              DATE_TYPE,
       EndDate              DATE_TYPE
);
ALTER TABLE Phones
       ADD CONSTRAINT XPKPhones PRIMARY KEY (Code);
CREATE TRIGGER Phones_BUH FOR Phones
BEFORE UPDATE  POSITION 0
AS
BEGIN
 /*     Èçìåíåíèå BegDate    */
 IF (new.BegDate <> old.BegDate) THEN
   BEGIN
     IF (new.BegDate < old.BegDate) THEN
       BEGIN
         /*     Ðàñøèðåíèå BegDate    */
         UPDATE Phones
          SET EndDate = new.BegDate
          WHERE ((new.BegDate BETWEEN BegDate AND EndDate)  AND (PKey = new.PKey));


       
       END
     ELSE
       BEGIN
         /*     Ñóæåíèå BegDate     */
         UPDATE Phones
          SET EndDate = new.BegDate
          WHERE ((EndDate = old.BegDate)  AND (PKey = new.PKey));
       END
   END
 /*     Èçìåíåíèå EndDate    */
 IF (new.EndDate <> old.EndDate) THEN
   BEGIN
     IF (new.EndDate > old.EndDate) THEN
       BEGIN
         /*     Ðàñøèðåíèå EndDate    */
          UPDATE Phones
          SET BegDate = new.EndDate
          WHERE ((new. EndDate BETWEEN BegDate AND EndDate)  AND (PKey = new.PKey));
       
       END
     ELSE
       BEGIN
         /*     Ñóæåíèå EndDate     */
          UPDATE Phones
          SET BegDate = new.EndDate
          WHERE ((BegDate = old.EndDate)  AND (PKey = new.PKey));
       END
   END
    /*     Ñáîðêà ìóñîðà       */
    DELETE FROM Phones
    WHERE ((BegDate >= new.BegDate) AND (EndDate <= new.EndDate) AND (PKey = new.PKey) AND (Code <> new.Code));
END ^
CREATE TRIGGER Phones_BIH FOR Phones
BEFORE INSERT POSITION 0
AS
BEGIN
  DELETE FROM Phones
  WHERE ((BegDate >= new.BegDate) AND (EndDate <= new.EndDate) AND (PKey = new.PKey));
  UPDATE Phones
  SET BegDate = new.EndDate
  WHERE ((new.EndDate BETWEEN BegDate AND EndDate)  AND (PKey = new.PKey));
  UPDATE Phones
  SET EndDate = new.BegDate
  WHERE ((new.BegDate BETWEEN BegDate AND EndDate)  AND (PKey = new.PKey));
END ^
CREATE TRIGGER Phones_BDH FOR Phones
BEFORE DELETE  POSITION 0
AS
BEGIN
    UPDATE Phones
    SET EndDate = old.EndDate
     WHERE ((EndDate = old.BegDate)  AND (PKey = old.PKey));
 
END ^
CREATE TABLE PhonesBanks (
       Code                 INTEGER NOT NULL,
       Name1                DESCR_TYPE,
       PMFO                 CHAR(12) NOT NULL,


       Name2                DESCR_TYPE,
       ELMFO                CHAR(12) NOT NULL,
       PlatCount            SMALLINT NOT NULL,
       Acc1                 CHAR(12) NOT NULL,
       Acc2                 CHAR(12) NOT NULL
);
CREATE INDEX XIEPhonesBanksName ON PhonesBanks
(
       Name1,
       Name2
);
ALTER TABLE PhonesBanks
       ADD CONSTRAINT XPKPhonesBanks PRIMARY KEY (Code);
CREATE TABLE PhonesKeys (
       Code                 INTEGER NOT NULL
);
ALTER TABLE PhonesKeys
       ADD CONSTRAINT XPKPhonesKeys PRIMARY KEY (Code);
CREATE TABLE PhonesOwners (
       Code                 INTEGER NOT NULL,
       PKey                 INTEGER NOT NULL,
       Name1                DESCR_TYPE,
       Name2                DESCR_TYPE,
       Category             INTEGER NOT NULL,
       Bank                 INTEGER,
       Street               INTEGER NOT NULL,
       PostStation          INTEGER,
       House                CHAR(5),
       Corpus               CHAR(3),
       Flat                 CHAR(3),
       Account              CHAR(5),
       RS                   CHAR(9),
       INN                  CHAR(13),
       Nmb_Dogov            CHAR(6),
       Date_Dogov           DATE,
       BegDate              DATE_TYPE,
       EndDate              DATE_TYPE
);
ALTER TABLE PhonesOwners
       ADD CONSTRAINT XPKPhonesOwners PRIMARY KEY (Code);
CREATE TRIGGER PhonesOwners_BUH FOR PhonesOwners
BEFORE UPDATE  POSITION 0
AS
BEGIN
 /*     Èçìåíåíèå BegDate    */
 IF (new.BegDate <> old.BegDate) THEN
   BEGIN
     IF (new.BegDate < old.BegDate) THEN
       BEGIN
         /*     Ðàñøèðåíèå BegDate    */
         UPDATE PhonesOwners
          SET EndDate = new.BegDate
          WHERE ((new.BegDate BETWEEN BegDate AND EndDate)  AND (PKey = new.PKey));
       
       END
     ELSE
       BEGIN
         /*     Ñóæåíèå BegDate     */


         UPDATE PhonesOwners
          SET EndDate = new.BegDate
          WHERE ((EndDate = old.BegDate)  AND (PKey = new.PKey));
       END
   END
 /*     Èçìåíåíèå EndDate    */
 IF (new.EndDate <> old.EndDate) THEN
   BEGIN
     IF (new.EndDate > old.EndDate) THEN
       BEGIN
         /*     Ðàñøèðåíèå EndDate    */
          UPDATE PhonesOwners
          SET BegDate = new.EndDate
          WHERE ((new. EndDate BETWEEN BegDate AND EndDate)  AND (PKey = new.PKey));
       
       END
     ELSE
       BEGIN
         /*     Ñóæåíèå EndDate     */
          UPDATE PhonesOwners
          SET BegDate = new.EndDate
          WHERE ((BegDate = old.EndDate)  AND (PKey = new.PKey));
       END
   END
    /*     Ñáîðêà ìóñîðà       */
    DELETE FROM PhonesOwners
    WHERE ((BegDate >= new.BegDate) AND (EndDate <= new.EndDate) AND (PKey = new.PKey) AND (Code <> new.Code));
END ^
CREATE TRIGGER PhonesOwners_BIH FOR PhonesOwners
BEFORE INSERT POSITION 0
AS
BEGIN
  DELETE FROM PhonesOwners
  WHERE ((BegDate >= new.BegDate) AND (EndDate <= new.EndDate) AND (PKey = new.PKey));
  UPDATE PhonesOwners
  SET BegDate = new.EndDate
  WHERE ((new.EndDate BETWEEN BegDate AND EndDate)  AND (PKey = new.PKey));
  UPDATE PhonesOwners
  SET EndDate = new.BegDate
  WHERE ((new.BegDate BETWEEN BegDate AND EndDate)  AND (PKey = new.PKey));
END ^
CREATE TRIGGER PhonesOwners_BDH FOR PhonesOwners
BEFORE DELETE  POSITION 0
AS
BEGIN
    UPDATE PhonesOwners
    SET EndDate = old.EndDate
     WHERE ((EndDate = old.BegDate)  AND (PKey = old.PKey));
 
END ^
CREATE TABLE PhonesOwnersKeys (
       Code                 INTEGER NOT NULL,
       InRest               CURR_TYPE,
       OutRest              CURR_TYPE,


       NDolg                INTEGER NOT NULL
);
ALTER TABLE PhonesOwnersKeys
       ADD CONSTRAINT XPKPhonesOwnersKeys PRIMARY KEY (Code);
CREATE TABLE PhonesPostStations (
       Code                 INTEGER NOT NULL,
       Name                 DESCR_TYPE,
       Region               INTEGER NOT NULL,
       PostIndex            CHAR(6) NOT NULL,
       PostNmb              CHAR(6) NOT NULL
);
CREATE UNIQUE INDEX XAKPhonesPostStationsIndex ON PhonesPostStations
(
       PostIndex
);
CREATE UNIQUE INDEX XAKPhonesPostStationsPostNmb ON PhonesPostStations
(
       PostNmb
);
CREATE INDEX XIEPhonesPostStationsName ON PhonesPostStations
(
       Name
);
ALTER TABLE PhonesPostStations
       ADD CONSTRAINT XPKPhonesPostStations PRIMARY KEY (Code);
CREATE TABLE PhonesRegions (
       Code                 INTEGER NOT NULL,
       Name                 DESCR_TYPE NOT NULL
);
CREATE INDEX XIEPhonesRegionsName ON PhonesRegions
(
       Name
);
ALTER TABLE PhonesRegions
       ADD CONSTRAINT XPKPhonesRegions PRIMARY KEY (Code);
CREATE TABLE PhonesStations (
       Code                 INTEGER NOT NULL,
       Region               INTEGER NOT NULL,
       Name                 DESCR_TYPE NOT NULL
);
CREATE INDEX XIEPhonesStationsName ON PhonesStations
(
       Name
);
ALTER TABLE PhonesStations
       ADD CONSTRAINT XPKPhonesStations PRIMARY KEY (Code);
CREATE TABLE PhonesStreets (
       Code                 INTEGER NOT NULL,
       Station              INTEGER NOT NULL,
       Region               INTEGER NOT NULL,
       Name                 DESCR_TYPE
);
CREATE INDEX XIEPhonesStreetsName ON PhonesStreets
(
       Name
);
ALTER TABLE PhonesStreets
       ADD CONSTRAINT XPKPhonesStreets PRIMARY KEY (Code);
CREATE TABLE Plat (


       Code                 INTEGER NOT NULL,
       Owner                INTEGER NOT NULL,
       ToUsl                INTEGER,
       PlatDate             DATE_TYPE,
       PlatType             INTEGER NOT NULL,
       DocNmb               CHAR(12) NOT NULL
);
ALTER TABLE Plat
       ADD CONSTRAINT XPKPlat PRIMARY KEY (Code);
CREATE TABLE SysLog (
       Code                 INTEGER NOT NULL,
       TableName            CHAR(16) NOT NULL,
       OpType               INTEGER NOT NULL,
       NewData              CHAR(64) NOT NULL,
       OpDate               DATE NOT NULL
);
ALTER TABLE SysLog
       ADD CONSTRAINT XPKSysLog PRIMARY KEY (Code);
CREATE TABLE SysSettings (
       Code                 INTEGER NOT NULL,
       TimeTalksUsl         INTEGER NOT NULL,
       NullOwner            INTEGER NOT NULL
);
ALTER TABLE SysSettings
       ADD CONSTRAINT XPKSysSettings PRIMARY KEY (Code);
CREATE TABLE Talks (
       Code                 INTEGER NOT NULL,
       DayCode              INTEGER NOT NULL,
       Phone                INTEGER NOT NULL,
       ToPhone              INTEGER NOT NULL,
       CallTime             CALLTIME_TYPE,
       PhoneNmb             PHONE_TYPE,
       HowLong              INTEGER NOT NULL,
       ToPhoneNmb           PHONE_TYPE,
       Calculated           SMALLINT NOT NULL,
       CallDate             DATE_TYPE
);
CREATE INDEX XAK1TalksCallDate ON Talks
(
       CallDate
);
ALTER TABLE Talks
       ADD CONSTRAINT XPKTalks PRIMARY KEY (Code);
CREATE TABLE TalksPay (
       Code                 INTEGER NOT NULL,
       Phone                INTEGER NOT NULL,
       TotalSum             CURR_TYPE,
       TotalLgotTime        CALLTIME_TYPE,
       TotalFullTime        CALLTIME_TYPE,
       TotalTime            COMPUTED BY (TotalLgotTime+TotalFullTime),
       CallDate             DATE_TYPE


);
ALTER TABLE TalksPay
       ADD CONSTRAINT XPKTalksPay PRIMARY KEY (Code);
CREATE TABLE UslCat (
       Code                 INTEGER NOT NULL,
       PKey                 INTEGER NOT NULL,
       Name                 DESCR_TYPE,
       Parent               INTEGER NOT NULL,
       BegDate              DATE_TYPE,
       EndDate              DATE_TYPE
);
CREATE INDEX XIEUslCatName ON UslCat
(
       Name
);
CREATE INDEX XIEUslCatParent ON UslCat
(
       Parent
);
ALTER TABLE UslCat
       ADD CONSTRAINT XPKUslCat PRIMARY KEY (Code);
CREATE TRIGGER UslCat_BUH FOR UslCat
BEFORE UPDATE  POSITION 0
AS
BEGIN
 /*     Èçìåíåíèå BegDate    */
 IF (new.BegDate <> old.BegDate) THEN
   BEGIN
     IF (new.BegDate < old.BegDate) THEN
       BEGIN
         /*     Ðàñøèðåíèå BegDate    */
         UPDATE UslCat
          SET EndDate = new.BegDate
          WHERE ((new.BegDate BETWEEN BegDate AND EndDate)  AND (PKey = new.PKey));
       
       END
     ELSE
       BEGIN
         /*     Ñóæåíèå BegDate     */
         UPDATE UslCat
          SET EndDate = new.BegDate
          WHERE ((EndDate = old.BegDate)  AND (PKey = new.PKey));
       END
   END
 /*     Èçìåíåíèå EndDate    */
 IF (new.EndDate <> old.EndDate) THEN
   BEGIN
     IF (new.EndDate > old.EndDate) THEN
       BEGIN
         /*     Ðàñøèðåíèå EndDate    */
          UPDATE UslCat
          SET BegDate = new.EndDate
          WHERE ((new.EndDate BETWEEN BegDate AND EndDate)  AND (PKey = new.PKey));
       
       END
     ELSE
       BEGIN
         /*     Ñóæåíèå EndDate     */
          UPDATE UslCat
          SET BegDate = new.EndDate
          WHERE ((BegDate = old.EndDate)  AND (PKey = new.PKey));
       END
   END
    /*     Ñáîðêà ìóñîðà       */
    DELETE FROM UslCat
    WHERE ((BegDate >= new.BegDate) AND (EndDate <= new.EndDate) AND (PKey = new.PKey) AND (Code <> new.Code));


END ^
CREATE TRIGGER UslCat_BIH FOR UslCat
BEFORE INSERT POSITION 0
AS
BEGIN
  DELETE FROM UslCat
  WHERE ((BegDate >= new.BegDate) AND (EndDate <= new.EndDate) AND (PKey = new.PKey));
  UPDATE UslCat
  SET BegDate = new.EndDate
  WHERE ((new. EndDate BETWEEN BegDate AND EndDate)  AND (PKey = new.PKey));
  UPDATE UslCat
  SET EndDate = new.BegDate
  WHERE ((new.BegDate BETWEEN BegDate AND EndDate)  AND (PKey = new.PKey));
END ^
CREATE TRIGGER UslCat_BDH FOR UslCat
BEFORE DELETE  POSITION 0
AS
BEGIN
    UPDATE UslCat
    SET EndDate = old.EndDate
     WHERE ((EndDate = old.BegDate)  AND (PKey = old.PKey));
 
END ^
CREATE TABLE UslCatKeys (
       Code                 INTEGER NOT NULL
);
ALTER TABLE UslCatKeys
       ADD CONSTRAINT XPKUslCatKeys PRIMARY KEY (Code);
CREATE TABLE UslDivisions (
       Code                 INTEGER NOT NULL,
       Name                 DESCR_TYPE,
       PKey                 INTEGER NOT NULL,
       Parent               INTEGER NOT NULL,
       BegDate              DATE_TYPE,
       EndDate              DATE_TYPE
);
CREATE INDEX XIEUslDivisionsname ON UslDivisions
(
       Name
);
CREATE INDEX XIEUslDivisionsParent ON UslDivisions
(
       Parent
);
ALTER TABLE UslDivisions
       ADD CONSTRAINT XPKUslDivisions PRIMARY KEY (Code);
CREATE TRIGGER UslDivisions_BUH FOR UslDivisions
BEFORE UPDATE  POSITION 0
AS
BEGIN
 /*     Èçìåíåíèå BegDate    */
 IF (new.BegDate <> old.BegDate) THEN
   BEGIN
     IF (new.BegDate < old.BegDate) THEN
       BEGIN
         /*     Ðàñøèðåíèå BegDate    */
         UPDATE UslDivisions
          SET EndDate = new.BegDate
          WHERE ((new.BegDate BETWEEN BegDate AND EndDate)  AND (PKey = new.PKey));


       
       END
     ELSE
       BEGIN
         /*     Ñóæåíèå BegDate     */
         UPDATE UslDivisions
          SET EndDate = new.BegDate
          WHERE ((EndDate = old.BegDate)  AND (PKey = new.PKey));
       END
   END
 /*     Èçìåíåíèå EndDate    */
 IF (new.EndDate <> old.EndDate) THEN
   BEGIN
     IF (new.EndDate > old.EndDate) THEN
       BEGIN
         /*     Ðàñøèðåíèå EndDate    */
          UPDATE UslDivisions
          SET BegDate = new.EndDate
          WHERE ((new. EndDate BETWEEN BegDate AND EndDate)  AND (PKey = new.PKey));
       
       END
     ELSE
       BEGIN
         /*     Ñóæåíèå EndDate     */
          UPDATE UslDivisions
          SET BegDate = new.EndDate
          WHERE ((BegDate = old.EndDate)  AND (PKey = new.PKey));
       END
   END
    /*     Ñáîðêà ìóñîðà       */
    DELETE FROM UslDivisions
    WHERE ((BegDate >= new.BegDate) AND (EndDate <= new.EndDate) AND (PKey = new.PKey) AND (Code <> new.Code));
END ^
CREATE TRIGGER UslDivisions_BIH FOR UslDivisions
BEFORE INSERT POSITION 0
AS
BEGIN
  DELETE FROM UslDivisions
  WHERE ((BegDate >= new.BegDate) AND (EndDate <= new.EndDate) AND (PKey = new.PKey));
  UPDATE UslDivisions
  SET BegDate = new.EndDate
  WHERE ((new.EndDate BETWEEN BegDate AND EndDate)  AND (PKey = new.PKey));
  UPDATE UslDivisions
  SET EndDate = new.BegDate
  WHERE ((new.BegDate BETWEEN BegDate AND EndDate)  AND (PKey = new.PKey));
END ^
CREATE TRIGGER UslDivisions_BDH FOR UslDivisions
BEFORE DELETE  POSITION 0
AS
BEGIN
    UPDATE UslDivisions
    SET EndDate = old.EndDate
     WHERE ((EndDate = old.BegDate)  AND (PKey = old.PKey));
 
END ^
CREATE TABLE UslDivisionsKeys (
       Code                 INTEGER NOT NULL


);
ALTER TABLE UslDivisionsKeys
       ADD CONSTRAINT XPKUslDivisionsKeys PRIMARY KEY (Code);
CREATE TABLE UslLgots (
       Code                 INTEGER NOT NULL,
       Category             INTEGER NOT NULL,
       Property             INTEGER,
       Tax                  CURR_TYPE,
       Usl                  INTEGER NOT NULL,
       NachCoeff            INTEGER NOT NULL,
       Nalog                INTEGER NOT NULL,
       BegDate              INTEGER NOT NULL,
       Info                 INTEGER NOT NULL,
       EndDate              INTEGER NOT NULL
);
ALTER TABLE UslLgots
       ADD CONSTRAINT XPKUslLgots PRIMARY KEY (Code);
CREATE TABLE UslProps (
       Code                 INTEGER NOT NULL,
       PKey                 INTEGER NOT NULL,
       Tag                  INTEGER NOT NULL,
       ValInteger           INTEGER,
       ValFloat             FLOAT,
       BegDate              DATE_TYPE,
       EndDate              DATE_TYPE
);
ALTER TABLE UslProps
       ADD CONSTRAINT XPKUslProps PRIMARY KEY (Code);
CREATE TRIGGER UslProps_BUH FOR UslProps
BEFORE UPDATE  POSITION 0
AS
BEGIN
 /*     Èçìåíåíèå BegDate    */
 IF (new.BegDate <> old.BegDate) THEN
   BEGIN
     IF (new.BegDate < old.BegDate) THEN
       BEGIN
         /*     Ðàñøèðåíèå BegDate    */
         UPDATE UslProps
          SET EndDate = new.BegDate
          WHERE ((new.BegDate BETWEEN BegDate AND EndDate)  AND (PKey = new.PKey));
        
       END
     ELSE
       BEGIN
         /*     Ñóæåíèå BegDate     */
         UPDATE UslProps
          SET EndDate = new.BegDate
          WHERE ((EndDate = old.BegDate)  AND (PKey = new.PKey));
       END
   END
 /*     Èçìåíåíèå EndDate    */
 IF (new.EndDate <> old.EndDate) THEN
   BEGIN
     IF (new.EndDate > old.EndDate) THEN
       BEGIN
         /*     Ðàñøèðåíèå EndDate    */


          UPDATE UslProps
          SET BegDate = new.EndDate
          WHERE ((new.EndDate BETWEEN BegDate AND EndDate)  AND (PKey = new.PKey));
       
       END
     ELSE
       BEGIN
         /*     Ñóæåíèå EndDate     */
          UPDATE UslProps
          SET BegDate = new.EndDate
          WHERE ((BegDate = old.EndDate)  AND (PKey = new.PKey));
       END
   END
    /*     Ñáîðêà ìóñîðà       */
    DELETE FROM UslProps
    WHERE ((BegDate >= new.BegDate) AND (EndDate <= new.EndDate) AND (PKey = new.PKey) AND (Code <> new.Code));
END ^
CREATE TRIGGER UslProps_BIH FOR UslProps
BEFORE INSERT POSITION 0
AS
BEGIN
  DELETE FROM UslProps
  WHERE ((BegDate >= new.BegDate) AND (EndDate <= new.EndDate) AND (PKey = new.PKey));
  UPDATE UslProps
  SET BegDate = new.EndDate
  WHERE ((new.EndDate BETWEEN BegDate AND EndDate)  AND (PKey = new.PKey));
  UPDATE UslProps
  SET EndDate = new.BegDate
  WHERE ((new.BegDate BETWEEN BegDate AND EndDate)  AND (PKey = new.PKey));
END ^
CREATE TRIGGER UslProps_BDH FOR UslProps
BEFORE DELETE  POSITION 0
AS
BEGIN
    UPDATE UslProps
    SET EndDate = old.EndDate
     WHERE ((EndDate = old.BegDate)  AND (PKey = old.PKey));
 
END ^
CREATE TABLE UslPropsKeys (
       Code                 INTEGER NOT NULL
);
ALTER TABLE UslPropsKeys
       ADD CONSTRAINT XPKUslPropsKeys PRIMARY KEY (Code);
CREATE TABLE Usls (
       Code                 INTEGER NOT NULL,
       PKey                 INTEGER NOT NULL,
       Division             INTEGER NOT NULL,
       UslType              INTEGER NOT NULL,
       Name                 CHAR(64) NOT NULL,
       BegDate              DATE_TYPE,
       EndDate              DATE_TYPE
);
CREATE INDEX XIEUslsName ON Usls


(
       Name
);
ALTER TABLE Usls
       ADD CONSTRAINT XPKUsls PRIMARY KEY (Code);
CREATE TRIGGER Usls_BUH FOR Usls
BEFORE UPDATE  POSITION 0
AS
BEGIN
 /*     Èçìåíåíèå BegDate    */
 IF (new.BegDate <> old.BegDate) THEN
   BEGIN
     IF (new.BegDate < old.BegDate) THEN
       BEGIN
         /*     Ðàñøèðåíèå BegDate    */
         UPDATE Usls
          SET EndDate = new.BegDate
          WHERE ((new.BegDate BETWEEN BegDate AND EndDate)  AND (PKey = new.PKey));
       
       END
     ELSE
       BEGIN
         /*     Ñóæåíèå BegDate     */
         UPDATE Usls
          SET EndDate = new.BegDate
          WHERE ((EndDate = old.BegDate)  AND (PKey = new.PKey));
       END
   END
 /*     Èçìåíåíèå EndDate    */
 IF (new.EndDate <> old.EndDate) THEN
   BEGIN
     IF (new.EndDate > old.EndDate) THEN
       BEGIN
         /*     Ðàñøèðåíèå EndDate    */
          UPDATE Usls
          SET BegDate = new.EndDate
          WHERE ((new.EndDate BETWEEN BegDate AND EndDate)  AND (PKey = new.PKey));
       
       END
     ELSE
       BEGIN
         /*     Ñóæåíèå EndDate     */
          UPDATE Usls
          SET BegDate = new.EndDate
          WHERE ((BegDate = old.EndDate)  AND (PKey = new.PKey));
       END
   END
    /*     Ñáîðêà ìóñîðà       */
    DELETE FROM Usls
    WHERE ((BegDate >= new.BegDate) AND (EndDate <= new.EndDate) AND (PKey = new.PKey) AND (Code <> new.Code));
END ^
CREATE TRIGGER Usls_BIH FOR Usls
BEFORE INSERT POSITION 0
AS
BEGIN
  DELETE FROM Usls
  WHERE ((BegDate >= new.BegDate) AND (EndDate <= new.EndDate) AND (PKey = new.PKey));
  UPDATE Usls
  SET BegDate = new.EndDate
  WHERE ((new.EndDate BETWEEN BegDate AND EndDate)  AND (PKey = new.PKey));
  UPDATE Usls
  SET EndDate = new.BegDate


  WHERE ((new.BegDate BETWEEN BegDate AND EndDate)  AND (PKey = new.PKey));
END ^
CREATE TRIGGER Usls_BDH FOR Usls
BEFORE DELETE  POSITION 0
AS
BEGIN
    UPDATE Usls
    SET EndDate = old.EndDate
     WHERE ((EndDate = old.BegDate)  AND (PKey = old.PKey));
 
END ^
CREATE TABLE UslsKeys (
       Code                 INTEGER NOT NULL
);
ALTER TABLE UslsKeys
       ADD CONSTRAINT XPKUslsKeys PRIMARY KEY (Code);
CREATE TABLE UslTypes (
       Code                 INTEGER NOT NULL,
       Name                 DESCR_TYPE
);
ALTER TABLE UslTypes
       ADD CONSTRAINT XPKUslTypes PRIMARY KEY (Code);
ALTER TABLE Nach
       ADD CONSTRAINT R_59
              FOREIGN KEY (Usl)
                             REFERENCES UslsKeys;
ALTER TABLE Nach
       ADD CONSTRAINT R_57
              FOREIGN KEY (Phone)
                             REFERENCES PhonesKeys;
ALTER TABLE Nach
       ADD FOREIGN KEY (Owner)
                             REFERENCES PhonesOwnersKeys;
ALTER TABLE NachBillings
       ADD CONSTRAINT R_65
              FOREIGN KEY (Division)
                             REFERENCES UslDivisionsKeys;
ALTER TABLE NachBillings
       ADD FOREIGN KEY (BillDateCode)
                             REFERENCES NachBillDates;
ALTER TABLE NachBillings
       ADD FOREIGN KEY (Owner)
                             REFERENCES PhonesOwnersKeys;
ALTER TABLE NachConstUsl
       ADD CONSTRAINT R_60
              FOREIGN KEY (Usl)
                             REFERENCES UslsKeys;
ALTER TABLE NachConstUsl
       ADD CONSTRAINT R_58
              FOREIGN KEY (Phone)
                             REFERENCES PhonesKeys;
ALTER TABLE NachConstUsl
       ADD FOREIGN KEY (Owner)


                             REFERENCES PhonesOwnersKeys;
ALTER TABLE Phones
       ADD FOREIGN KEY (Owner)
                             REFERENCES PhonesOwnersKeys;
ALTER TABLE Phones
       ADD FOREIGN KEY (PKey)
                             REFERENCES PhonesKeys;
ALTER TABLE Phones
       ADD FOREIGN KEY (Street)
                             REFERENCES PhonesStreets;
ALTER TABLE PhonesOwners
       ADD FOREIGN KEY (PostStation)
                             REFERENCES PhonesPostStations;
ALTER TABLE PhonesOwners
       ADD FOREIGN KEY (Street)
                             REFERENCES PhonesStreets;
ALTER TABLE PhonesOwners
       ADD FOREIGN KEY (Bank)
                             REFERENCES PhonesBanks;
ALTER TABLE PhonesOwners
       ADD FOREIGN KEY (Category)
                             REFERENCES UslCatKeys;
ALTER TABLE PhonesOwners
       ADD FOREIGN KEY (PKey)
                             REFERENCES PhonesOwnersKeys;
ALTER TABLE PhonesPostStations
       ADD CONSTRAINT R_62
              FOREIGN KEY (Region)
                             REFERENCES PhonesRegions;
ALTER TABLE PhonesStations
       ADD FOREIGN KEY (Region)
                             REFERENCES PhonesRegions;
ALTER TABLE PhonesStreets
       ADD FOREIGN KEY (Region)
                             REFERENCES PhonesRegions;
ALTER TABLE PhonesStreets
       ADD FOREIGN KEY (Station)
                             REFERENCES PhonesStations;
ALTER TABLE Plat
       ADD CONSTRAINT R_61
              FOREIGN KEY (ToUsl)
                             REFERENCES UslsKeys;
ALTER TABLE Plat
       ADD FOREIGN KEY (Owner)
                             REFERENCES PhonesOwnersKeys;
ALTER TABLE SysSettings
       ADD FOREIGN KEY (NullOwner)


                             REFERENCES PhonesOwnersKeys;
ALTER TABLE SysSettings
       ADD FOREIGN KEY (TimeTalksUsl)
                             REFERENCES UslsKeys;
ALTER TABLE Talks
       ADD FOREIGN KEY (ToPhone)
                             REFERENCES PhonesKeys;
ALTER TABLE Talks
       ADD FOREIGN KEY (Phone)
                             REFERENCES PhonesKeys;
ALTER TABLE Talks
       ADD FOREIGN KEY (DayCode)
                             REFERENCES TalksPay;
ALTER TABLE TalksPay
       ADD FOREIGN KEY (Phone)
                             REFERENCES PhonesKeys;
ALTER TABLE UslCat
       ADD FOREIGN KEY (PKey)
                             REFERENCES UslCatKeys;
ALTER TABLE UslDivisions
       ADD CONSTRAINT R_63
              FOREIGN KEY (PKey)
                             REFERENCES UslDivisionsKeys;
ALTER TABLE UslLgots
       ADD CONSTRAINT R_50
              FOREIGN KEY (Property)
                             REFERENCES UslPropsKeys;
ALTER TABLE UslLgots
       ADD FOREIGN KEY (Usl)
                             REFERENCES UslsKeys;
ALTER TABLE UslLgots
       ADD FOREIGN KEY (Category)
                             REFERENCES UslCatKeys;
ALTER TABLE UslProps
       ADD CONSTRAINT R_51
              FOREIGN KEY (PKey)
                             REFERENCES UslPropsKeys;
ALTER TABLE Usls
       ADD CONSTRAINT R_64
              FOREIGN KEY (Division)
                             REFERENCES UslDivisionsKeys;
ALTER TABLE Usls
       ADD FOREIGN KEY (UslType)
                             REFERENCES UslTypes;
ALTER TABLE Usls
       ADD FOREIGN KEY (PKey)
                             REFERENCES UslsKeys;
CREATE PROCEDURE PrGenUslPropsKeys
RETURNS (ACode INTEGER)
AS
BEGIN
 ACode = GEN_ID(genUslPropsKeys, 1);


END ^
CREATE PROCEDURE PrGenUslProps
RETURNS (ACode INTEGER)
AS
BEGIN
 ACode = GEN_ID(genUslProps, 1);
END ^
CREATE PROCEDURE PrGenPhonesRegions
RETURNS (ACode INTEGER)
AS
BEGIN
 ACode = GEN_ID(genPhonesRegions, 1);
END ^
CREATE PROCEDURE PrGenPhonesStations
RETURNS (ACode INTEGER)
AS
BEGIN
 ACode = GEN_ID(genPhonesStations, 1);
END ^
CREATE PROCEDURE PrGenPhonesStreets
RETURNS (ACode INTEGER)
AS
BEGIN
 ACode = GEN_ID(genPhonesStreets, 1);
END ^
CREATE PROCEDURE PrGenPhonesBanks
RETURNS (ACode INTEGER)
AS
BEGIN
 ACode = GEN_ID(genPhonesBanks, 1);
END ^
CREATE PROCEDURE PrGenTalksPay
RETURNS (ACode INTEGER)
AS
BEGIN
 ACode = GEN_ID(genTalksPay, 1);
END ^
CREATE PROCEDURE PrGenTalks
RETURNS (ACode INTEGER)
AS
BEGIN
 ACode = GEN_ID(genTalks, 1);
END ^
CREATE PROCEDURE PrGenNach
RETURNS (ACode INTEGER)
AS
BEGIN
 ACode = GEN_ID(genNach, 1);
END ^
CREATE PROCEDURE PrGenNachBillings
RETURNS (ACode INTEGER)
AS
BEGIN
 ACode = GEN_ID(genNachBillings, 1);
END ^
CREATE PROCEDURE PrGenNachBillDates
RETURNS (ACode INTEGER)
AS
BEGIN
 ACode = GEN_ID(genNachBillDates, 1);
END ^
CREATE PROCEDURE PrGenNachConstUsl
RETURNS (ACode INTEGER)
AS
BEGIN
 ACode = GEN_ID(genNachConstUsl, 1);
END ^
CREATE PROCEDURE PrGenUslDivisions
RETURNS (ACode INTEGER)
AS
BEGIN
 ACode = GEN_ID(genUslDivisions, 1);
END ^
CREATE PROCEDURE PrGenUslLgots
RETURNS (ACode INTEGER)
AS
BEGIN
 ACode = GEN_ID(genUslLgots, 1);
END ^
CREATE PROCEDURE PrGenUslsKeys
RETURNS (ACode INTEGER)
AS
BEGIN
 ACode = GEN_ID(genUslsKeys, 1);
END ^
CREATE PROCEDURE PrGenUsls
RETURNS (ACode INTEGER)
AS
BEGIN
 ACode = GEN_ID(genUsls, 1);
END ^
CREATE PROCEDURE PrGenUslCatKeys
RETURNS (ACode INTEGER)


AS
BEGIN
 ACode = GEN_ID(genUslCatKeys, 1);
END ^
CREATE PROCEDURE PrGenUslCat
RETURNS (ACode INTEGER)
AS
BEGIN
 ACode = GEN_ID(genUslCat, 1);
END ^
CREATE PROCEDURE PrGenPhones
RETURNS (ACode INTEGER)
AS
BEGIN
 ACode = GEN_ID(genPhones, 1);
END ^
CREATE PROCEDURE PrGenPhonesOwnersKeys
RETURNS (ACode INTEGER)
AS
BEGIN
 ACode = GEN_ID(genPhonesOwnersKeys, 1);
END ^
CREATE PROCEDURE PrGenPhonesOwners
RETURNS (ACode INTEGER)
AS
BEGIN
 ACode = GEN_ID(genPhonesOwners, 1);
END ^
CREATE PROCEDURE PrGenSysSettings
RETURNS (ACode INTEGER)
AS
BEGIN
 ACode = GEN_ID(genSysSettings, 1);
END ^
CREATE PROCEDURE PrGenPhonesKeys
RETURNS (ACode INTEGER)
AS
BEGIN
 ACode = GEN_ID(genPhonesKeys, 1);
END ^
CREATE PROCEDURE PrGenPlat
RETURNS (ACode INTEGER)
AS
BEGIN
 ACode = GEN_ID(genPlat, 1);
END ^
CREATE PROCEDURE PrGenPhonesPostStations
RETURNS (ACode INTEGER)
AS
BEGIN
 ACode = GEN_ID(genPhonesPostStations, 1);
END ^
CREATE PROCEDURE PrGenSysLog
RETURNS (ACode INTEGER)
AS
BEGIN
 ACode = GEN_ID(genSysLog, 1);
END ^
CREATE PROCEDURE PrGenUslTypes
RETURNS (ACode INTEGER)
AS
BEGIN
 ACode = GEN_ID(genUslTypes, 1);
END ^
CREATE PROCEDURE PrGenUslDivisionsKeys
RETURNS (ACode INTEGER)
AS
BEGIN
 ACode = GEN_ID(genUslDivisionsKeys, 1);
END ^
  ^
CREATE PROCEDURE TalksExamineOwner (APhoneNmb CHAR(7),  ADate DATE)
RETURNS (APhone INTEGER)
AS
  DECLARE VARIABLE AStreet                INTEGER;
  DECLARE VARIABLE NOwner               INTEGER;
  DECLARE VARIABLE APhoneCode       INTEGER;
BEGIN
  SELECT PKey FROM Phones WHERE (PhoneNmb = :APhoneNmb) AND (:ADate BETWEEN BegDate AND EndDate)
 INTO :APhone;
 IF (:APhone IS NULL) THEN
  BEGIN
    SELECT NullOwner FROM SysSettings INTO :NOwner;


    SELECT Street FROM PhonesOwners
       WHERE (PKey = :NOwner) AND (:ADate BETWEEN PhonesOwners.BegDate AND PhonesOwners.EndDate)
    INTO :AStreet;
    IF (:AStreet IS NOT NULL) THEN
      BEGIN
        EXECUTE PROCEDURE PrGenPhonesKeys RETURNING_VALUES :APhone;
   
        INSERT INTO PhonesKeys (Code)
         VALUES (:APhone);
               
        INSERT INTO Phones(Owner, PKey, PhoneNmb, Street, InstallDate, RemoveDate, BegDate, EndDate)
        VALUES (:NOwner, :APhone, :APhoneNmb, :AStreet, :ADate, "12.12.2222", :ADate, "12.12.2222");
     END
  END
END ^
CREATE PROCEDURE TalksGetTax
AS
BEGIN
 EXIT;
END ^
CREATE PROCEDURE TalksGetPay  (APhone INTEGER,  ADay   DATE,  ACallTime INTEGER, AHowLong INTEGER)
RETURNS (APay  FLOAT,  ACalculated SMALLINT, IsLgot SMALLINT)
AS
 DECLARE VARIABLE  ATax             FLOAT;
 DECLARE VARIABLE  AProcNach   FLOAT;
 DECLARE VARIABLE  ATalksUsl     INTEGER;
 DECLARE VARIABLE  AOwner        INTEGER;
 DECLARE VARIABLE  ANalog         FLOAT;
BEGIN
 ACalculated = 0;
  SELECT TimeTalksUsl FROM SysSettings INTO :ATalksUsl;
 IF (:ATalksUsl IS NULL) THEN EXIT;
 SELECT Owner FROM Phones WHERE (PKey = :APhone) AND (:ADay BETWEEN BegDate AND EndDate)
 INTO :AOwner;
 IF (:AOwner IS NULL) THEN EXIT;
 EXECUTE PROCEDURE UslGetOwnerTax(:AOwner, :ATalksUsl, :ADay)
 RETURNING_VALUES :ATax, :AProcNach, :ANalog;
 IF (:ATax IS NULL) THEN EXIT;
  APay = ATax*AHowLong*AProcNach/3000;
  ACalculated = 1;
 
END ^
CREATE PROCEDURE TalksCallBilling (APhone INTEGER, ACallDate DATE,  ACallTime INTEGER, AHowLong INTEGER)
RETURNS (ATalksPayCode INTEGER, ACalculated SMALLINT)
AS
 DECLARE VARIABLE APay           FLOAT;
 DECLARE VARIABLE PayCode     INTEGER;


 DECLARE VARIABLE IsLgot          SMALLINT;
 DECLARE VARIABLE TTime         INTEGER;
 DECLARE VARIABLE LTime         INTEGER;
BEGIN
 EXECUTE PROCEDURE TalksGetPay(APhone, ACallDate, ACallTime, AHowLong)
 RETURNING_VALUES :APay, :ACalculated, :IsLgot;
  SELECT Code FROM TalksPay WHERE (Phone = :APhone)  AND (CallDate = :ACallDate)
 INTO PayCode;
 IF (:ACalculated = 0) THEN EXIT;
 
 IF (:IsLgot = 0) THEN  BEGIN
   TTime = AHowLong;
   LTime = 0;
 END
 ELSE BEGIN
   LTime = AHowLong;
  TTime = 0;
 END
 IF (:PayCode IS NULL) THEN   BEGIN
     EXECUTE PROCEDURE PrGenTalksPay RETURNING_VALUES :PayCode;
      INSERT INTO TalksPay (Code, Phone, CallDate, TotalSum, TotalFullTime, TotalLgotTime)
      VALUES (:PayCode, :APhone, :ACallDate, :APay, :TTime, :LTime);
  END
  ELSE BEGIN
     UPDATE TalksPay
     SET TotalSum = TotalSum+:APay,     
             TotalFullTime = TotalFullTime+:TTime,
             TotalLgotTime = TotalLgotTime+:LTime
      WHERE Code = :PayCode;
  END
END ^
CREATE PROCEDURE UslGetOwnerTax(AOwner INTEGER, AUsl INTEGER, ADate DATE) 
RETURNS (ATax               FLOAT,
                   AProcNach     FLOAT,
                   ANalog            FLOAT)
AS
  DECLARE VARIABLE ACategory INTEGER;
BEGIN
 SELECT  Category FROM PhonesOwners
  WHERE (PKey = :AOwner) AND (:ADate BETWEEN BegDate AND EndDate)
  INTO :ACategory;
  SELECT Tax, NachCoeff, Nalog FROM UslLgots
  WHERE (Usl = :AUsl) AND (:ADate BETWEEN BegDate AND EndDate)
  INTO :ATax, :AProcNach, :ANalog;
 EXIT;
END  

Ñîäåðæàíèå ðàçäåëà