123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394 |
- CREATE TABLE FmsFacilityCodeClass
- (
- SiteId int NOT NULL ,
- FacilityClassId int identity(1,1) NOT NULL ,
- ParentFacilityClassId int NULL ,
- Depth int NOT NULL ,
- Name nvarchar(80) NOT NULL ,
- Abbreviation nvarchar(40) NULL ,
- IsUse bit NULL ,
-
- PRIMARY KEY (SiteId ASC , FacilityClassId ASC)
- );
- DBCC CHECKIDENT( FmsFacilityCodeClass, RESEED, 1 )
- CREATE TABLE FmsFacilityOperationItem
- (
- SiteId int NOT NULL ,
- FacilityCode int NOT NULL ,
- ItemId int identity(1,1) NOT NULL ,
- Name nvarchar(100) NULL ,
- ResultUnit nvarchar(10) NOT NULL ,
- PointId nvarchar(50) NULL ,
- SystemId int NULL ,
- EnergyCode bit NULL ,
- IsUse bit NULL ,
- SortOrderNo int NULL ,
- InsertedDate datetime NULL ,
- InsertedUserId nvarchar(24) NULL ,
- UpdatedDate datetime NULL ,
- UpdatedUserId nvarchar(24) NULL ,
- PRIMARY KEY (SiteId ASC , ItemId ASC)
- );
- DBCC CHECKIDENT( FmsFacilityOperationItem, RESEED, 1 )
- CREATE TABLE CmFacility
- (
- SiteId int NOT NULL ,
- FacilityTypeId int NOT NULL,
- FacilityCode int NOT NULL,
- FirstClassId int NULL,
- SecondClassId int NULL,
- ThirdClassId int NULL,
- DeviceType nvarchar(48) NULL,
- ParentFacilityCode int NULL,
- Name nvarchar(80) NOT NULL,
- Nickname nvarchar(40) NULL,
- Rfid nvarchar(52) NULL,
- Manufacturer nvarchar(40) NULL,
- ManufactureDate date NULL,
- ManufactureSerial nvarchar(20) NULL,
- ManufactureModel nvarchar(40) NULL,
- Supplier nvarchar(40) NULL,
- SupplierPhoneNo nvarchar(20) NULL,
- BuildingId int NULL ,
- FloorId int NULL ,
- ZoneId int NULL,
- InstallDate date NULL,
- OperationStartDate date NULL,
-
- FacilityCapacity nvarchar(40) NULL,
- FacilityCost int NULL,
- FacilityCount int NULL,
- FacilitySeviceLifeYear int NULL,
- FacilityUsage nvarchar(80) NULL,
- RatedPowerConsumption nvarchar(40) NULL,
- RatedCOP int NULL,
- FileId int NULL,
- MainContactorUserId nvarchar(24) NULL,
- SubContactorUserId nvarchar(24) NULL,
- Note nvarchar(1024) NULL,
- Reserved1 nvarchar(64) NULL,
- Reserved2 nvarchar(64) NULL,
-
- IsUse bit NULL,
-
-
-
-
- PRIMARY KEY (SiteId ASC , FacilityCode ASC)
- );
- CREATE TABLE FmsFacilityCheckItem
- (
- SiteId int NOT NULL ,
- FacilityCode int NOT NULL ,
- CheckItemId int identity(1,1) NOT NULL ,
- WorkTypeId int NOT NULL ,
- CycleUnitId int NOT NULL ,
- Name nvarchar(100) NOT NULL ,
-
- IsYesNoResult bit NOT NULL ,
-
- IsUse bit NULL ,
-
- PRIMARY KEY (SiteId ASC, CheckItemId ASC, FacilityCode ASC)
- );
- DBCC CHECKIDENT( FmsFacilityCheckItem, RESEED, 1 )
- CREATE TABLE FmsFacilityManual
- (
- SiteId int NOT NULL ,
- FacilityCode int NOT NULL ,
- ManualId int identity(1,1) NOT NULL ,
- BusinessFieldId int NOT NULL ,
- Name nvarchar(100) NULL ,
- [Description] nvarchar(1024) NULL ,
- Keyword nvarchar(100) NULL ,
- FileId int NULL ,
- IsUse bit NULL ,
-
- PRIMARY KEY( SiteId ASC, FacilityCode ASC, ManualId ASC )
- );
- DBCC CHECKIDENT( FmsFacilityManual, RESEED, 1 )
- ALTER TABLE FmsFacilityOperationItem
- ADD CONSTRAINT FK__FmsFacilityOperationItem__CmFacility FOREIGN KEY (SiteId , FacilityCode) REFERENCES CmFacility(SiteId , FacilityCode)
- ;
- ALTER TABLE FmsFacilityCodeClass
- ADD CONSTRAINT FK__FmsFacilityCodeClass__CmSite
- FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
- ;
- ALTER TABLE FmsFacilityCodeClass
- ADD CONSTRAINT FK__FmsFacilityCodeClass__FmsFacilityCodeClass
- FOREIGN KEY (SiteId , ParentFacilityClassId) REFERENCES FmsFacilityCodeClass(SiteId , FacilityClassId)
- ;
- ALTER TABLE CmFacility
- ADD CONSTRAINT FK__CmFacility__CmFacility FOREIGN KEY (SiteId , ParentFacilityCode) REFERENCES CmFacility(SiteId , FacilityCode)
- ;
- ALTER TABLE CmFacility
- ADD CONSTRAINT FK__CmFacility__BemsFacilityType
- FOREIGN KEY (FacilityTypeId) REFERENCES BemsFacilityType(FacilityTypeId)
- ;
- ALTER TABLE CmFacility
- ADD CONSTRAINT FK__CmFacility__FmsFacilityCodeClassAsFirst FOREIGN KEY (SiteId , FirstClassId) REFERENCES FmsFacilityCodeClass(SiteId , FacilityClassId)
- ;
- ALTER TABLE CmFacility
- ADD CONSTRAINT FK__CmFacility__FmsFacilityCodeClassAsSecond FOREIGN KEY (SiteId , SecondClassId) REFERENCES FmsFacilityCodeClass(SiteId , FacilityClassId)
- ;
- ALTER TABLE CmFacility
- ADD CONSTRAINT FK__CmFacility__FmsFacilityCodeClassAsThird FOREIGN KEY (SiteId , ThirdClassId) REFERENCES FmsFacilityCodeClass(SiteId , FacilityClassId)
- ;
- ALTER TABLE CmFacility
- ADD CONSTRAINT FK__CmFacility__CmBuilding FOREIGN KEY (SiteId , BuildingId) REFERENCES CmBuilding(SiteId , BuildingId)
- ;
- ALTER TABLE CmFacility
- ADD CONSTRAINT FK__CmFacility__CmFloor FOREIGN KEY (SiteId , BuildingId , FloorId) REFERENCES CmFloor(SiteId , BuildingId , FloorId)
- ;
- ALTER TABLE CmFacility
- ADD CONSTRAINT FK__CmFacility__CmZone FOREIGN KEY (SiteId , BuildingId , FloorId , ZoneId) REFERENCES CmZone(SiteId , BuildingId , FloorId , ZoneId)
- ;
- ALTER TABLE CmFacility
- ADD CONSTRAINT FK__CmFacility__CmUserAsMainContactorUserId FOREIGN KEY (SiteId , MainContactorUserId) REFERENCES CmUser(SiteId , UserId)
- ;
- ALTER TABLE CmFacility
- ADD CONSTRAINT FK__CmFacility__CmUserAsSubContactorUserId FOREIGN KEY (SiteId , SubContactorUserId) REFERENCES CmUser(SiteId , UserId)
- ;
- ALTER TABLE CmFacility
- ADD CONSTRAINT FK__CmFacility__CmFile FOREIGN KEY (SiteId , FileId) REFERENCES CmFile(SiteId , FileId)
- ;
- ALTER TABLE FmsFacilityCheckItem
- ADD CONSTRAINT FK__FmsFacilityCheckItem__CmFacility FOREIGN KEY (SiteId , FacilityCode) REFERENCES CmFacility(SiteId , FacilityCode)
- ;
- ALTER TABLE FmsFacilityManual
- ADD FOREIGN KEY (SiteId , FacilityCode) REFERENCES CmFacility(SiteId , FacilityCode)
- ;
- ALTER TABLE FmsFacilityManual
- ADD FOREIGN KEY (SiteId , BusinessFieldId) REFERENCES CmBusinessField(SiteId , BusinessFieldId)
- ;
-
-
-
|