his.sql

Delphi6开发的HIS程序

    CheckInDate         DATE,
    Created             DATE              DEFAULT SYSDATE NOT NULL,
    Updated             DATE              DEFAULT SYSDATE NOT NULL,
    IsApprove           NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    IsActive            NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    IsValid             NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    ApproveDate         DATE,
    PStockMedicineID    NUMBER(38, 0),
    MedicineStockID     NUMBER(38, 0)    NOT NULL,
    CreateBy            NUMBER(38, 0)    NOT NULL,
    UpdateBy            NUMBER(38, 0),
    ApproveBy           NUMBER(38, 0),
    StockOutOrderID     NUMBER(38, 0),
    CONSTRAINT PKPStockInOrder PRIMARY KEY (PStockInOrderID)
) 
;



-- 
-- TABLE: PStockMedicine 
--

CREATE TABLE PStockMedicine(
    PStockMedicineID    NUMBER(38, 0)    NOT NULL,
    SAmount             NUMBER(16, 4)    NOT NULL,
    Amount              NUMBER(16, 4)    NOT NULL,
    Created             DATE              DEFAULT SYSDATE NOT NULL,
    Updated             DATE              DEFAULT SYSDATE NOT NULL,
    MedicineStockID     NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKPStockMedicine PRIMARY KEY (PStockMedicineID)
) 
;



-- 
-- TABLE: PStockOutOrder 
--

CREATE TABLE PStockOutOrder(
    PStockOutOrderID    NUMBER(38, 0)    NOT NULL,
    Total               NUMBER(16, 4)    NOT NULL,
    Taxrate             NUMBER(2, 2)     NOT NULL,
    Amount              NUMBER(16, 4)    NOT NULL,
    IsActive            NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    IsValid             NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    IsApprove           NUMBER(5, 0)      DEFAULT 0,
    ApproveDate         DATE,
    Created             DATE              DEFAULT SYSDATE NOT NULL,
    Updated             DATE              DEFAULT SYSDATE NOT NULL,
    CheckOutDate        DATE,
    PStockMedicineID    NUMBER(38, 0)    NOT NULL,
    CreateBy            NUMBER(38, 0)    NOT NULL,
    UpdateBy            NUMBER(38, 0),
    ApproveBy           NUMBER(38, 0),
    CONSTRAINT PKPStockOutOrder PRIMARY KEY (PStockOutOrderID)
) 
;



-- 
-- TABLE: PurchaseMethod 
--

CREATE TABLE PurchaseMethod(
    PurchaseMethodID    NUMBER(38, 0)    NOT NULL,
    Caption             VARCHAR2(32)     NOT NULL,
    CONSTRAINT PKPurchaseMethod PRIMARY KEY (PurchaseMethodID)
) 
;



-- 
-- TABLE: PurchaseOrder 
--

CREATE TABLE PurchaseOrder(
    PurchaseOrderID     NUMBER(38, 0)    NOT NULL,
    IsActive            NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    CheckInDate         DATE,
    Total               NUMBER(16, 4)     DEFAULT 0 NOT NULL,
    Taxrate             NUMBER(2, 2)     NOT NULL,
    Memo                VARCHAR2(64),
    ReferDate           DATE,
    IsRefered           NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    IsApprove           NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    ApproveDate         DATE,
    Created             DATE              DEFAULT SYSDATE NOT NULL,
    Updated             DATE              DEFAULT SYSDATE NOT NULL,
    CreateBy            NUMBER(38, 0)    NOT NULL,
    UpdateBy            NUMBER(38, 0),
    PurchaseMethodID    NUMBER(38, 0)    NOT NULL,
    ApproveBy           NUMBER(38, 0),
    CONSTRAINT PKPurchaseOrder PRIMARY KEY (PurchaseOrderID)
) 
;



-- 
-- TABLE: Recipe 
--

CREATE TABLE Recipe(
    RecipeID       NUMBER(38, 0)    NOT NULL,
    Created        DATE              DEFAULT SYSDATE NOT NULL,
    IsActive       NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    IsValid        NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    IsPrinted      NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    Amount         NUMBER(16, 4)     DEFAULT 1 NOT NULL,
    Updated        DATE              DEFAULT SYSDATE NOT NULL,
    DoctorID       NUMBER(38, 0),
    ReckoningID    NUMBER(38, 0)    NOT NULL,
    CreateBy       NUMBER(38, 0)    NOT NULL,
    UpdateBy       NUMBER(38, 0),
    SickID         NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKRecipe PRIMARY KEY (RecipeID)
) 
;



-- 
-- TABLE: Reckoning 
--

CREATE TABLE Reckoning(
    ReckoningID    NUMBER(38, 0)    NOT NULL,
    Created        DATE              DEFAULT SYSDATE NOT NULL,
    Updated        DATE              DEFAULT SYSDATE NOT NULL,
    Charge         NUMBER(16, 4)    NOT NULL,
    IsActive       NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    IsValid        NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    SickID         NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKReckoning PRIMARY KEY (ReckoningID)
) 
;



-- 
-- TABLE: RMaterial 
--

CREATE TABLE RMaterial(
    RMaterialID    NUMBER(38, 0)    NOT NULL,
    IsActive       NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    Price          NUMBER(16, 4)    NOT NULL,
    Amount         NUMBER(16, 4)    NOT NULL,
    Total          NUMBER(16, 4)    NOT NULL,
    ReckoningID    NUMBER(38, 0)    NOT NULL,
    UnitsID        NUMBER(38, 0)    NOT NULL,
    MaterialID     NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKRMaterial PRIMARY KEY (RMaterialID)
) 
;



-- 
-- TABLE: RMedicine 
--

CREATE TABLE RMedicine(
    RMedicineID         NUMBER(38, 0)    NOT NULL,
    IsActive            NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    Price               NUMBER(16, 4)    NOT NULL,
    Total               NUMBER(16, 4)    NOT NULL,
    Amount              NUMBER(16, 4)    NOT NULL,
    ReckoningID         NUMBER(38, 0)    NOT NULL,
    PStockMedicineID    NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKRMedicine PRIMARY KEY (RMedicineID)
) 
;



-- 
-- TABLE: ROperation 
--

CREATE TABLE ROperation(
    ROperationID    NUMBER(38, 0)    NOT NULL,
    IsActive        NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    Price           NUMBER(16, 4)    NOT NULL,
    Amount          NUMBER(16, 4)    NOT NULL,
    Total           NUMBER(16, 4)    NOT NULL,
    ReckoningID     NUMBER(38, 0)    NOT NULL,
    OperationID     NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKROperation PRIMARY KEY (ROperationID)
) 
;



-- 
-- TABLE: RService 
--

CREATE TABLE RService(
    RServiceID     NUMBER(38, 0)    NOT NULL,
    IsActive       NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    Price          NUMBER(16, 4)    NOT NULL,
    Total          NUMBER(16, 4)    NOT NULL,
    Amount         NUMBER(16, 4)    NOT NULL,
    ReckoningID    NUMBER(38, 0)    NOT NULL,
    UnitsID        NUMBER(38, 0)    NOT NULL,
    ServiceID      NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKRService PRIMARY KEY (RServiceID)
) 
;



-- 
-- TABLE: SCOMaterial 
--

CREATE TABLE SCOMaterial(
    SCOMaterialID            NUMBER(38, 0)    NOT NULL,
    OAmount                  NUMBER(16, 4)    NOT NULL,
    CQuantity                NUMBER(16, 4)    NOT NULL,
    Created                  DATE              DEFAULT SYSDATE NOT NULL,
    Updated                  DATE              DEFAULT SYSDATE NOT NULL,
    IsApprove                NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    ApproveDate              DATE,
    MaterialStockDetailID    NUMBER(38, 0)    NOT NULL,
    CreateBy                 NUMBER(38, 0)    NOT NULL,
    UpdateBy                 NUMBER(38, 0),
    ApproveBy                NUMBER(38, 0),
    CONSTRAINT PKSCOMaterial PRIMARY KEY (SCOMaterialID)
) 
;



-- 
-- TABLE: SCOMedicine 
--

CREATE TABLE SCOMedicine(
    SCOMedicineID            NUMBER(38, 0)    NOT NULL,
    IsApprove                NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    OAmount                  NUMBER(16, 4)    NOT NULL,
    CQuantity                NUMBER(16, 4)    NOT NULL,
    Created                  DATE              DEFAULT SYSDATE NOT NULL,
    Updated                  DATE              DEFAULT SYSDATE NOT NULL,
    MedicineStockDetailID    NUMBER(38, 0)    NOT NULL,
    CreateBy                 NUMBER(38, 0)    NOT NULL,
    UpdateBy                 NUMBER(38, 0),
    ApproveBy                NUMBER(38, 0),
    ApproveDate              DATE,
    CONSTRAINT PKSCOMedicine PRIMARY KEY (SCOMedicineID)
) 
;



-- 
-- TABLE: SectionOffice 
--

CREATE TABLE SectionOffice(
    SectionOfficeID    NUMBER(38, 0)    NOT NULL,
    Caption            VARCHAR2(32)     NOT NULL,
    CONSTRAINT PKSectionOffice PRIMARY KEY (SectionOfficeID)
) 
;



-- 
-- TABLE: SectionOfficeChargeItem 
--

CREATE TABLE SectionOfficeChargeItem(
    ChargeItemCode     VARCHAR2(8)      NOT NULL,
    SectionOfficeID    NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKSectionOfficeChargeItem PRIMARY KEY (ChargeItemCode, SectionOfficeID)
) 
;



-- 
-- TABLE: Service 
--

CREATE TABLE Service(
    ServiceID           NUMBER(38, 0)    NOT NULL,
    PinYin              VARCHAR2(32)     NOT NULL,
    Code                VARCHAR2(32)     NOT NULL,
    Charge              NUMBER(16, 4)    NOT NULL,
    Caption             VARCHAR2(32)     NOT NULL,
    ServiceCatalogID    NUMBER(38, 0),
    CONSTRAINT PKService PRIMARY KEY (ServiceID)
) 
;



-- 
-- TABLE: ServiceCatalog 
--

CREATE TABLE ServiceCatalog(
    ServiceCatalogID     NUMBER(38, 0)    NOT NULL,
    PinYin               VARCHAR2(16)     NOT NULL,
    ParentID             NUMBER(38, 0),
    Code                 VARCHAR2(16)     NOT NULL,
    ChargeCatalogCode    VARCHAR2(8)      NOT NULL,
    Caption              VARCHAR2(32)     NOT NULL,
    CONSTRAINT PKServiceCatalog PRIMARY KEY (ServiceCatalogID)
) 
;



-- 
-- TABLE: Sick 
--

CREATE TABLE Sick(
    SickID             NUMBER(38, 0)    NOT NULL,
    IsArrearage        NUMBER(5, 0)      DEFAULT 0 NOT NULL
                       CHECK (IsArrearage in(0, 1)),
    Code               VARCHAR2(16)     NOT NULL,
    Birthday           DATE,
    Sex                NUMBER(5, 0)     NOT NULL
                       CHECK (Sex in(1, 2)),
    CName              VARCHAR2(32)     NOT NULL,
    Created            DATE              DEFAULT SYSDATE NOT NULL,
    IsActive           NUMBER(5, 0)      DEFAULT 0 NOT NULL
                       CHECK (IsActive in(0, 1)),
    IsValid            NUMBER(5, 0)      DEFAULT 0 NOT NULL
                       CHECK (IsValid in(0, 1)),
    ST                 NUMBER(5, 0)      DEFAULT 1 NOT NULL,
    Updated            DATE              DEFAULT SYSDATE NOT NULL,
    ProfessionID       NUMBER(38, 0),
    WedlockID          NUMBER(38, 0),
    NativePlaceID      NUMBER(38, 0),
    NationID           NUMBER(38, 0),
    PaymentMethodID    NUMBER(38, 0)    NOT NULL,
    CreateBy           NUMBER(38, 0)    NOT NULL,
    UpdateBy           NUMBER(38, 0)    NOT NULL,
    TollStationID      NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKSick PRIMARY KEY (SickID)
) 
;



-- 
-- TABLE: Sickness 
--

CREATE TABLE Sickness(
    SicknessCode           VARCHAR2(16)    NOT NULL,
    Caption                VARCHAR2(32)    NOT NULL,
    SicknessCatalogCode    VARCHAR2(8),
    CONSTRAINT PKSickness PRIMARY KEY (SicknessCode)
) 
;



-- 
-- TABLE: SicknessCatalog 
--

CREATE TABLE SicknessCatalog(
    SicknessCatalogCode    VARCHAR2(8)     NOT NULL,
    ParentCode             VARCHAR2(8),
    Caption                VARCHAR2(32)    NOT NULL,
    CONSTRAINT PKSicknessCatalog PRIMARY KEY (SicknessCatalogCode)
) 
;



-- 
-- TABLE: SicknessHistory 
--

CREATE TABLE SicknessHistory(
    SicknessCode       VARCHAR2(16)     NOT NULL,
    InpatientID        NUMBER(38, 0)    NOT NULL,
    BeginStateID       NUMBER(38, 0)    NOT NULL,
    EndStateID         NUMBER(38, 0),
    DiagnoseID         NUMBER(38, 0)    NOT NULL,
    SicknessLevelID    NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKSicknessHistory PRIMARY KEY (SicknessCode, InpatientID)
) 
;



-- 
-- TABLE: SicknessLevel 
--

CREATE TABLE SicknessLevel(
    SicknessLevelID    NUMBER(38, 0)    NOT NULL,
    Caption            VARCHAR2(32)     NOT NULL,
    CONSTRAINT PKSicknessLevel PRIMARY KEY (SicknessLevelID)
) 
;



-- 
-- TABLE: SicknessState 
--

CREATE TABLE SicknessState(
    SicknessStateID    NUMBER(38, 0)    NOT NULL,
    Caption            VARCHAR2(32)     NOT NULL,
    CONSTRAINT PKSicknessState PRIMARY KEY (SicknessStateID)
) 
;



-- 
-- TABLE: Sickroom 
--

CREATE TABLE Sickroom(
    SickroomID         NUMBER(38, 0)    NOT NULL,
    Caption            VARCHAR2(32)     NOT NULL,
    SectionOfficeID    NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKSickroom PRIMARY KEY (SickroomID)
) 
;



-- 
-- TABLE: SickroomChargeItem 
--

CREATE TABLE SickroomChargeItem(
    SickroomID        NUMBER(38, 0)    NOT NULL,
    ChargeItemCode    VARCHAR2(8)      NOT NULL,
    CONSTRAINT PKSickroomChargeItem PRIMARY KEY (SickroomID, ChargeItemCode)
) 
;



-- 
-- TABLE: SickType 
--

CREATE TABLE SickType(
    SickTypeID    NUMBER(38, 0)    NOT NULL,
    Caption       VARCHAR2(18),
    CONSTRAINT PKSickType PRIMARY KEY (SickTypeID)
) 
;



-- 
-- TABLE: SIOMaterialDetail 
--

CREATE TABLE SIOMaterialDetail(
    SIOMaterialDetailID    NUMBER(38, 0)    NOT NULL,
    Maturity               DATE             NOT NULL,
    Produced               DATE             NOT NULL,
    PSN                    VARCHAR2(32)     NOT NULL,
    Price                  NUMBER(16, 4)    NOT NULL,
    SPrice                 NUMBER(16, 4)    NOT NULL,
    Amount                 NUMBER(16, 4)    NOT NULL,
    Total                  NUMBER(16, 4)    NOT NULL,
    StockInOrderID         NUMBER(38, 0)    NOT NULL,
    POMaterialDetailID     NUMBER(38, 0),
    ManufacturerID         NUMBER(38, 0)    NOT NULL,
    ProviderID             NUMBER(38, 0)    NOT NULL,
    MaterialID             NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKSIOMaterialDetail PRIMARY KEY (SIOMaterialDetailID)
) 
;



-- 
-- TABLE: SIOMedicineDetail 
--

CREATE TABLE SIOMedicineDetail(
    SIOMedicineDetailID    NUMBER(38, 0)    NOT NULL,
    PSN                    VARCHAR2(16)     NOT NULL,
    Produced               DATE             NOT NULL,
    Maturity               DATE,
    SPrice                 NUMBER(16, 4)    NOT NULL,
    Price                  NUMBER(16, 4)    NOT NULL,
    Amount                 NUMBER(16, 4)    NOT NULL,
    Total                  NUMBER(16, 4)    NOT NULL,
    StockInOrderID         NUMBER(38, 0)    NOT NULL,
    POMedicineDetailID     NUMBER(38, 0),
    ManufacturerID         NUMBER(38, 0)    NOT NULL,
    ProviderID             NUMBER(38, 0)    NOT NULL,
    MedicineID             NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKSIOMedicineDetail PRIMARY KEY (SIOMedicineDetailID)
) 
;



-- 
-- TABLE: SOOMaterialDetail 
--

CREATE TABLE SOOMaterialDetail(