Xreferat.com » Рефераты по информатике » Организация удаленного доступа к распределенным базам данных

Организация удаленного доступа к распределенным базам данных

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  


Приложение 2


Приложение 3

Исходные тексты коммуникационного сервиса

ServiceMain.c

Файл ServiceMain.c - Модуль инициализации и управления сервером.

/*************************************************************/

/*           Main unit for Communication Service             */

/*                  Copyright (c) 1997 by Malkov O.V.        */

/*                      JSC "Svyazinform"  RM                */

/*************************************************************/

#include <windows.h>

#include "DoService.h"

#include "CommonConfig.h"

/* Globals */

SERVICE_STATUS          ServiceStatus;

SERVICE_STATUS_HANDLE   ServiceStatusHandle;

/* Function Prototypes */

void WINAPI ServiceStart (DWORD   argc, LPTSTR  *argv);

VOID WINAPI ServiceCtrlHandler (IN 

Если Вам нужна помощь с академической работой (курсовая, контрольная, диплом, реферат и т.д.), обратитесь к нашим специалистам. Более 90000 специалистов готовы Вам помочь.
Бесплатные корректировки и доработки. Бесплатная оценка стоимости работы.

Поможем написать работу на аналогичную тему

Получить выполненную работу или консультацию специалиста по вашему учебному проекту
Нужна помощь в написании работы?
Мы - биржа профессиональных авторов (преподавателей и доцентов вузов). Пишем статьи РИНЦ, ВАК, Scopus. Помогаем в публикации. Правки вносим бесплатно.

Похожие рефераты: