123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382 |
- CREATE TABLE FmsWorkOperationDate
- (
- SiteId int NOT NULL ,
- FacilityCode int NOT NULL ,
- CheckDateTime datetime NOT NULL ,
-
- PRIMARY KEY (SiteId ASC , FacilityCode ASC , CheckDateTime ASC)
- );
- CREATE TABLE FmsWorkOperationItem
- (
- SiteId int NOT NULL ,
- FacilityCode int NOT NULL ,
- CheckDateTime datetime NOT NULL ,
- ItemId int identity(1,1) NOT NULL ,
- ItemName nvarchar(100) NULL ,
- ItemResult nvarchar(20) NULL ,
- ResultUnit nvarchar(10) NOT NULL ,
- SortOrderNo int NULL ,
- InsertedDate datetime NULL ,
- InsertedUserId nvarchar(24) NULL ,
- UpdatedDate datetime NULL ,
- UpdatedUserId nvarchar(24) NULL ,
-
- PRIMARY KEY (SiteId ASC , FacilityCode ASC , CheckDateTime ASC , ItemId ASC)
- );
- DBCC CHECKIDENT( FmsWorkOperationItem, RESEED, 1 )
- CREATE TABLE FmsWorkOperationTime
- (
- SiteId int NOT NULL ,
- FacilityCode int NOT NULL ,
- CheckDatetime datetime NOT NULL ,
-
-
- InsertedDate datetime NULL ,
- InsertedUserId nvarchar(24) NULL ,
- UpdatedDate datetime NULL ,
- UpdatedUserId nvarchar(24) NULL ,
- PRIMARY KEY (SiteId ASC , FacilityCode ASC , CheckDateTime ASC)
- );
- CREATE TABLE FmsWorkDailyRecord
- (
- SiteId int NOT NULL ,
- WorkDate datetime NOT NULL ,
- TemplateId int NOT NULL ,
-
- DailyRecordId int identity(1,1) NOT NULL ,
- BusinessFieldId int NOT NULL ,
- UpdateFlag bit NULL ,
- DeleteFlag bit NULL ,
- DiaryName nvarchar(100) NOT NULL ,
- Weather nvarchar(20) NULL ,
- ConfirmdatetimeStep1 datetime NULL ,
- ConfirmUserIdStep1 nvarchar(24) NULL ,
- ConfirmdatetimeStep2 datetime NULL ,
- ConfirmUserIdStep2 nvarchar(24) NULL ,
- ConfirmdatetimeStep3 datetime NULL ,
- ConfirmUserIdStep3 nvarchar(24) NULL ,
- ConfirmdatetimeStep4 datetime NULL ,
- ConfirmUserIdStep4 nvarchar(24) NULL ,
- ConfirmdatetimeStep5 datetime NULL ,
- ConfirmUserIdStep5 nvarchar(24) NULL ,
- SortOrderNo int NULL ,
- InsertedDate datetime NULL ,
- InsertedUserId nvarchar(24) NULL ,
- UpdatedDate datetime NULL ,
- UpdatedUserId nvarchar(24) NULL ,
-
- AttendComment nvarchar(200) NULL ,
- PlanContent nvarchar(4000) NULL ,
- EtcContent nvarchar(4000) NULL ,
- PRIMARY KEY (SiteId ASC , WorkDate ASC , TemplateId ASC , DailyRecordId ASC , BusinessFieldId ASC)
- );
- DBCC CHECKIDENT( FmsWorkDailyRecord, RESEED, 1 )
- CREATE TABLE FmsWorkDailyRecordPrevention
- (
- SiteId int NOT NULL ,
- WorkDate datetime NOT NULL ,
- TemplateId int NOT NULL ,
- DailyRecordId int NOT NULL ,
- BusinessFieldId int NOT NULL ,
- PreventionId int identity(1,1) NOT NULL ,
- ShopNo nvarchar(50) NULL ,
- ShopName nvarchar(100) NULL ,
- WorkTime nvarchar(50) NULL ,
- PreventionContent nvarchar(200) NULL ,
- SignificantContent nvarchar(4000) NULL ,
- PRIMARY KEY (SiteId ASC , WorkDate ASC , TemplateId ASC , DailyRecordId ASC , BusinessFieldId ASC , PreventionId ASC)
- );
- DBCC CHECKIDENT( FmsWorkDailyRecordPrevention, RESEED, 1 )
- CREATE TABLE FmsWorkDailyRecordTemplate
- (
- SiteId int NOT NULL ,
- BusinessFieldId int NOT NULL ,
- TemplateId int identity(1,1) NOT NULL ,
- TemplateName nvarchar(100) NOT NULL ,
- StartDate datetime NOT 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, BusinessFieldId ASC, TemplateId ASC )
- );
- DBCC CHECKIDENT( FmsWorkDailyRecordTemplate, RESEED, 1 )
- CREATE TABLE FmsWorkDailyRecordWork
- (
- SiteId int NOT NULL ,
- WorkDate datetime NOT NULL ,
- DailyRecordId int NOT NULL ,
- TemplateId int NOT NULL ,
- BusinessFieldId int NOT NULL ,
- WorkId int identity(1,1) NOT NULL ,
- OrderId int NOT NULL ,
- ResultId int NOT NULL ,
- DeleteFlag bit NOT NULL ,
- PRIMARY KEY( SiteId ASC , WorkDate ASC , DailyRecordId ASC , TemplateId ASC , BusinessFieldId ASC , WorkId ASC )
- );
- DBCC CHECKIDENT( FmsWorkDailyRecordWork, RESEED, 1 )
- CREATE TABLE FmsWorkDailyRecordWorkingHour
- (
- SiteId int NOT NULL ,
- WorkingDate datetime NOT NULL ,
- FacilityCode int NOT NULL ,
- WorkType int NOT NULL ,
- WorkingMinute int NULL ,
-
- PRIMARY KEY( SiteId ASC , FacilityCode ASC , WorkType ASC , WorkingDate ASC )
- );
- ALTER TABLE FmsWorkOperationDate
- ADD FOREIGN KEY (SiteId , FacilityCode) REFERENCES CmFacility(SiteId , FacilityCode)
- ;
- ALTER TABLE FmsWorkOperationItem
- ADD FOREIGN KEY (SiteId , FacilityCode , CheckDateTime) REFERENCES FmsWorkOperationTime(SiteId , FacilityCode , CheckDateTime)
- ;
- ALTER TABLE FmsWorkOperationTime
- ADD FOREIGN KEY (SiteId , FacilityCode , CheckDateTime) REFERENCES FmsWorkOperationDate(SiteId , FacilityCode , CheckDateTime)
- ;
- ALTER TABLE FmsWorkDailyRecord
- ADD FOREIGN KEY (SiteId , BusinessFieldId , TemplateId ) REFERENCES FmsWorkDailyRecordTemplate(SiteId , BusinessFieldId , TemplateId )
- ;
- ALTER TABLE FmsWorkDailyRecordTemplate
- ADD FOREIGN KEY (SiteId , BusinessFieldId) REFERENCES CmBusinessField(SiteId , BusinessFieldId)
- ;
- ALTER TABLE FmsWorkDailyRecordWork
- ADD FOREIGN KEY (SiteId , WorkDate , TemplateId , DailyRecordId , BusinessFieldId) REFERENCES FmsWorkDailyRecord(SiteId , WorkDate , TemplateId , DailyRecordId , BusinessFieldId)
- ;
- ALTER TABLE FmsWorkDailyRecordWork
- ADD FOREIGN KEY (SiteId , OrderId , ResultId) REFERENCES FmsWorkResult(SiteId , OrderId , ResultId)
- ;
- ALTER TABLE FmsWorkDailyRecordWorkingHour
- ADD FOREIGN KEY (SiteId , FacilityCode) REFERENCES CmFacility(SiteId , FacilityCode)
- ;
- ALTER TABLE FmsWorkDailyRecordPrevention
- ADD FOREIGN KEY (SiteId , WorkDate , TemplateId , DailyRecordId , BusinessFieldId) REFERENCES FmsWorkDailyRecord(SiteId , WorkDate , TemplateId , DailyRecordId , BusinessFieldId)
- ;
-
-
|