123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591 |
- CREATE TABLE FmsWorkCodeCauseClass
- (
- SiteId int NOT NULL ,
- CauseClassId int identity(1,1) NOT NULL ,
- ParentId int NOT NULL ,
- Name nvarchar(50) NOT NULL ,
- IsUse bit NULL ,
-
- PRIMARY KEY (SiteId ASC , CauseClassId ASC)
- );
- CREATE TABLE FmsWorkCodeEmergency
- (
- SiteId int NOT NULL ,
- EmergencyId int identity(1,1) NOT NULL ,
- Name nvarchar(50) NULL ,
- IsUse bit NULL ,
- PRIMARY KEY (SiteId ASC , EmergencyId ASC)
- );
- CREATE TABLE FmsWorkCodeType
- (
- WorkTypeId int NOT NULL ,
- Name nvarchar(50) NOT NULL ,
-
- [Description] nvarchar(512) NULL ,
-
- PRIMARY KEY (WorkTypeId ASC)
- );
- INSERT INTO FmsWorkCodeType VALUES (1, '점검계획', '');
- INSERT INTO FmsWorkCodeType VALUES (2, '법정검사', '');
- INSERT INTO FmsWorkCodeType VALUES (3, '민원', '');
- CREATE TABLE FmsWorkCodeProgress
- (
- WorkProgressId int NOT NULL ,
- Name nvarchar(24) NOT NULL ,
- [Description] nvarchar(512) NULL ,
- PRIMARY KEY (WorkProgressId ASC)
- );
- INSERT INTO FmsWorkCodeProgress VALUES (1, '작업계획', '');
- INSERT INTO FmsWorkCodeProgress VALUES (2, '작업보류', '');
- INSERT INTO FmsWorkCodeProgress VALUES (3, '작업대기', '');
- INSERT INTO FmsWorkCodeProgress VALUES (4, '작업진행중', '');
- INSERT INTO FmsWorkCodeProgress VALUES (5, '작업완료', '');
- INSERT INTO FmsWorkCodeProgress VALUES (6, '완료승인', '');
- CREATE TABLE FmsWorkRequest
- (
- SiteId int NOT NULL ,
- WorkRequestId int identity(1,1) NOT NULL ,
- WorkTypeId int NOT NULL ,
- BusinessFieldId int NOT NULL ,
- WorkScheduleId int NULL ,
- WorkProgressId int NOT NULL ,
- Title nvarchar(200) NOT NULL ,
- Content nvarchar(1024) NULL ,
- RequestUserId nvarchar(24) NULL ,
- RequestDate datetime NOT NULL ,
- StartWorkDate datetime NOT NULL ,
- InspectionAgencyId int NULL ,
- RejectUserId nvarchar(24) NULL,
- RejectDate datetime NULL,
- RejectReason nvarchar(512) NULL ,
- PRIMARY KEY (SiteId ASC , WorkRequestId ASC)
- );
- DBCC CHECKIDENT( FmsWorkRequest, RESEED, 1 )
- CREATE TABLE FmsWorkRequestToFacility
- (
- SiteId int NOT NULL ,
- WorkRequestId int NOT NULL ,
- FacilityCode int NOT NULL ,
-
- PRIMARY KEY (SiteId ASC , WorkRequestId ASC , FacilityCode ASC)
- );
- CREATE TABLE FmsWorkOrder
- (
- SiteId int NOT NULL ,
- WorkRequestId int NOT NULL ,
- BusinessFieldId int NOT NULL ,
- EmergencyId int NULL,
- OrderUserId nvarchar(24) NOT NULL ,
- OrderDate datetime NOT NULL ,
- Title nvarchar(200) NOT NULL ,
- Content nvarchar(2000) NULL ,
-
- StartWorkDate datetime NOT NULL ,
- PRIMARY KEY (SiteId ASC , WorkRequestId ASC)
- );
- DBCC CHECKIDENT( FmsWorkOrder, RESEED, 1 )
- CREATE TABLE FmsWorkOrderToFacility
- (
- SiteId int NOT NULL ,
- WorkRequestId int NOT NULL ,
- FacilityCode int NOT NULL ,
-
- PRIMARY KEY (SiteId ASC , WorkRequestId ASC , FacilityCode ASC)
- );
- CREATE TABLE FmsWorkResult
- (
- SiteId int NOT NULL ,
- WorkRequestId int NOT NULL ,
- WorkerUserId nvarchar(24) NOT NULL ,
- StartDate datetime NOT NULL ,
- EndDate datetime NOT NULL ,
- CauseClassId int NULL ,
- Content nvarchar(1024) NULL ,
- ConfirmedDate datetime NULL ,
- ConfirmedUserId nvarchar(24) NULL ,
- IsConfirmed bit NOT NULL ,
- PRIMARY KEY (SiteId ASC , WorkRequestId ASC)
- );
- CREATE TABLE FmsWorkResultToFacility
- (
- SiteId int NOT NULL ,
- WorkRequestId int NOT NULL ,
- FacilityCode int NOT NULL ,
-
- PRIMARY KEY (SiteId ASC , WorkRequestId ASC , FacilityCode ASC)
- );
- CREATE TABLE FmsWorkResultCheckItem
- (
- SiteId int NOT NULL ,
- WorkRequestId int NOT NULL ,
- FacilityCode int NOT NULL ,
- CheckItemId int NOT NULL ,
- BoolResult bit NULL ,
- TextResult nvarchar(1024) NULL ,
-
- PRIMARY KEY (SiteId ASC , WorkRequestId ASC , FacilityCode ASC , CheckItemId ASC)
- );
- CREATE TABLE FmsWorkResultFile
- (
- SiteId int NOT NULL ,
- WorkRequestId int NOT NULL ,
- FileId int NOT NULL ,
-
- PRIMARY KEY (SiteId ASC , WorkRequestId ASC)
- );
- CREATE TABLE FmsWorkResultLaw
- (
- SiteId int NOT NULL ,
- WorkRequestId int NOT NULL ,
- CertificationNo nvarchar(50) NULL ,
- CheckCommission numeric(10,0) NULL ,
- CheckResult bit NULL ,
-
- PRIMARY KEY (SiteId ASC , WorkRequestId ASC)
- );
- ALTER TABLE FmsWorkCodeCauseClass
- ADD CONSTRAINT FK__FmsWorkCodeCauseClass__CmSite
- FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
- ;
- ALTER TABLE FmsWorkCodeEmergency
- ADD CONSTRAINT FK__FmsWorkCodeEmergency__CmSite
- FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
- ;
- ALTER TABLE FmsWorkRequest
- ADD CONSTRAINT FK__FmsWorkRequest__CmSite
- FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
- ;
- ALTER TABLE FmsWorkRequest
- ADD CONSTRAINT FK__FmsWorkRequest__FmsWorkCodeWorkType
- FOREIGN KEY (WorkTypeId) REFERENCES FmsWorkCodeType(WorkTypeId)
- ;
- ALTER TABLE FmsWorkRequest
- ADD CONSTRAINT FK__FmsWorkRequest__FmsWorkSchedule
- FOREIGN KEY (SiteId, WorkScheduleId) REFERENCES FmsWorkSchedule(SiteId, WorkScheduleId)
- ;
- ALTER TABLE FmsWorkRequest
- ADD CONSTRAINT FK__FmsWorkRequest__FmsWorkCodeProgress
- FOREIGN KEY (WorkProgressId) REFERENCES FmsWorkCodeProgress(WorkProgressId)
- ;
- ALTER TABLE FmsWorkRequest
- ADD CONSTRAINT FK__FmsWorkRequest__CmBusinessField
- FOREIGN KEY (SiteId , BusinessFieldId) REFERENCES CmBusinessField(SiteId , BusinessFieldId)
- ;
- ALTER TABLE FmsWorkRequest
- ADD CONSTRAINT FK__FmsWorkRequest__CmUser_AsRequestUserId
- FOREIGN KEY (SiteId , RequestUserId) REFERENCES CmUser(SiteId , UserId)
- ;
- ALTER TABLE FmsWorkRequest
- ADD CONSTRAINT FK__FmsWorkRequest__CmUser1_AsRejectUserId
- FOREIGN KEY (SiteId , RejectUserId) REFERENCES CmUser(SiteId , UserId)
- ;
- ALTER TABLE FmsWorkRequest
- ADD CONSTRAINT FK__FmsWorkRequest__FmsWorkCodeLegalInspectionAgency
- FOREIGN KEY (SiteId , InspectionAgencyId) REFERENCES FmsWorkCodeLegalInspectionAgency(SiteId , InspectionAgencyId)
- ;
- ALTER TABLE FmsWorkRequestToFacility
- ADD CONSTRAINT FK__FmsWorkRequestToFacility__FmsWorkRequest
- FOREIGN KEY (SiteId , WorkRequestId) REFERENCES FmsWorkRequest(SiteId , WorkRequestId)
- ON DELETE CASCADE
- ;
- ALTER TABLE FmsWorkRequestToFacility
- ADD CONSTRAINT FK__FmsWorkRequestToFacility__CmFacility
- FOREIGN KEY (SiteId , FacilityCode) REFERENCES CmFacility(SiteId , FacilityCode)
- ;
- ALTER TABLE FmsWorkOrder
- ADD CONSTRAINT FK__FmsWorkOrder__CmSite
- FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
- ;
- ALTER TABLE FmsWorkOrder
- ADD CONSTRAINT FK__FmsWorkOrder__FmsWorkRequest
- FOREIGN KEY (SiteId , WorkRequestId) REFERENCES FmsWorkRequest(SiteId , WorkRequestId)
- ;
- ALTER TABLE FmsWorkOrder
- ADD CONSTRAINT FK__FmsWorkOrder__FmsWorkCodeEmergency
- FOREIGN KEY (SiteId , EmergencyId) REFERENCES FmsWorkCodeEmergency(SiteId , EmergencyId)
- ;
- ALTER TABLE FmsWorkOrder
- ADD CONSTRAINT FK__FmsWorkOrder__CmUser
- FOREIGN KEY (SiteId , OrderUserId) REFERENCES CmUser(SiteId , UserId)
- ;
- ALTER TABLE FmsWorkOrder
- ADD CONSTRAINT FK__FmsWorkOrder__CmBusinessField
- FOREIGN KEY (SiteId , BusinessFieldId) REFERENCES CmBusinessField(SiteId , BusinessFieldId)
- ;
- ALTER TABLE FmsWorkOrderToFacility
- ADD CONSTRAINT FK__FmsWorkOrderToFacility__FmsWorkOrder
- FOREIGN KEY (SiteId , WorkRequestId) REFERENCES FmsWorkOrder(SiteId , WorkRequestId)
- ON DELETE CASCADE
- ;
- ALTER TABLE FmsWorkOrderToFacility
- ADD CONSTRAINT FK__FmsWorkOrderToFacility__CmFacility
- FOREIGN KEY (SiteId , FacilityCode) REFERENCES CmFacility(SiteId , FacilityCode)
- ;
- ALTER TABLE FmsWorkResult
- ADD CONSTRAINT FK__FmsWorkResult__FmsWorkOrder
- FOREIGN KEY (SiteId , WorkRequestId) REFERENCES FmsWorkOrder(SiteId , WorkRequestId)
- ;
- ALTER TABLE FmsWorkResult
- ADD CONSTRAINT FK__FmsWorkResult__FmsWorkCodeCauseClass
- FOREIGN KEY (SiteId , CauseClassId) REFERENCES FmsWorkCodeCauseClass(SiteId , CauseClassId)
- ;
- ALTER TABLE FmsWorkResult
- ADD CONSTRAINT FK__FmsWorkResult__CmUser_AsWorkerUserId
- FOREIGN KEY (SiteId , WorkerUserId) REFERENCES CmUser(SiteId , UserId)
- ;
- ALTER TABLE FmsWorkResult
- ADD CONSTRAINT FK__FmsWorkResult__CmUser_AsConfirmedUserId
- FOREIGN KEY (SiteId , ConfirmedUserId) REFERENCES CmUser(SiteId , UserId)
- ;
- ALTER TABLE FmsWorkResultCheckItem
- ADD CONSTRAINT FK__FmsWorkResultCheckItem__FmsWorkResult
- FOREIGN KEY (SiteId , WorkRequestId) REFERENCES FmsWorkResult(SiteId , WorkRequestId)
- ;
- ALTER TABLE FmsWorkResultCheckItem
- ADD CONSTRAINT FK__FmsWorkResultCheckItem__FmsFacilityCheckItem
- FOREIGN KEY (SiteId , FacilityCode, CheckItemId) REFERENCES FmsFacilityCheckItem(SiteId, FacilityCode, CheckItemId)
- ;
- ALTER TABLE FmsWorkResultCheckItem
- ADD CONSTRAINT FK__FmsWorkResultCheckItem__CmFacility
- FOREIGN KEY (SiteId , FacilityCode) REFERENCES CmFacility(SiteId, FacilityCode)
- ;
- ALTER TABLE FmsWorkResultToFacility
- ADD CONSTRAINT FK__FmsWorkResultToFacility__FmsWorkOrder
- FOREIGN KEY (SiteId , WorkRequestId) REFERENCES FmsWorkResult(SiteId , WorkRequestId)
- ON DELETE CASCADE
- ;
- ALTER TABLE FmsWorkResultToFacility
- ADD CONSTRAINT FK__FmsWorkResultToFacility__CmFacility
- FOREIGN KEY (SiteId , FacilityCode) REFERENCES CmFacility(SiteId , FacilityCode)
- ;
- ALTER TABLE FmsWorkResultFile
- ADD CONSTRAINT FK__FmsWorkResultFile__FmsWorkResult
- FOREIGN KEY (SiteId , WorkRequestId) REFERENCES FmsWorkResult(SiteId , WorkRequestId)
- ;
- ALTER TABLE FmsWorkResultFile
- ADD CONSTRAINT FK__FmsWorkResultFile__CmFile
- FOREIGN KEY (SiteId , FileId) REFERENCES CmFile(SiteId , FileId)
- ;
- ALTER TABLE FmsWorkResultLaw
- ADD CONSTRAINT FK__FmsWorkResultLaw__FmsWorkResult
- FOREIGN KEY (SiteId , WorkRequestId) REFERENCES FmsWorkResult(SiteId , WorkRequestId)
- ;
- ALTER TABLE FmsWorkSchedule
- ADD CONSTRAINT FK__FmsWorkSchedule__FmsWorkCodeType
- FOREIGN KEY (WorkTypeId) REFERENCES FmsWorkCodeType(WorkTypeId)
- ;
- ALTER TABLE FmsFacilityCheckItem
- ADD CONSTRAINT FK__FmsFacilityCheckItem__FmsWorkCodeType
- FOREIGN KEY (WorkTypeId) REFERENCES FmsWorkCodeType(WorkTypeId)
- ;
-
-
-
-
-
-
-
-
|