--------------------------------------------------- -- 수시점검 --------------------------------------------------- -- work_drive_date_WodrDate -- 운전일지 -- work/drive/date --CREATE TABLE WodrDate CREATE TABLE FmsWorkOperationDate ( SiteId int NOT NULL , -- 현장 고유번호 FacilityCode int NOT NULL , -- 설비코드 CheckDateTime datetime NOT NULL , -- 확인날짜 PRIMARY KEY (SiteId ASC , FacilityCode ASC , CheckDateTime ASC) ); -- work_drive_item_WodrItem -- 검침결과 -- work/drive/item --CREATE TABLE WodrItem CREATE TABLE FmsWorkOperationItem ( SiteId int NOT NULL , -- 현장 고유번호 FacilityCode int NOT NULL , -- 설비코드 CheckDateTime datetime NOT NULL , -- 확인일시 -- CheckHourMin CHAR(5) 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 WodrTime CREATE TABLE FmsWorkOperationTime ( SiteId int NOT NULL , -- 현장 고유번호 FacilityCode int NOT NULL , -- 설비코드 CheckDatetime datetime NOT NULL , -- 확인날짜 --CheckDate datetime NOT NULL , --CheckHourMin CHAR(5) 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 WodiDiary CREATE TABLE FmsWorkDailyRecord ( SiteId int NOT NULL , -- 현장 고유번호 WorkDate datetime NOT NULL , -- 일일 작업 날짜 TemplateId int NOT NULL , -- 작업 견본 고유번호 --DiaryId 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 , -- 승인날짜 1 ConfirmUserIdStep1 nvarchar(24) NULL , -- 승인 사용자 고유번호 1 ConfirmdatetimeStep2 datetime NULL , -- 승인날짜 2 ConfirmUserIdStep2 nvarchar(24) NULL , -- 승인 사용자 고유번호 2 ConfirmdatetimeStep3 datetime NULL , -- 승인날짜 3 ConfirmUserIdStep3 nvarchar(24) NULL , -- 승인 사용자 고유번호 3 ConfirmdatetimeStep4 datetime NULL , -- 승인날짜 4 ConfirmUserIdStep4 nvarchar(24) NULL , -- 승인 사용자 고유번호 4 ConfirmdatetimeStep5 datetime NULL , -- 승인날짜 5 ConfirmUserIdStep5 nvarchar(24) NULL , -- 승인 사용자 고유번호 5 SortOrderNo int NULL , -- 정렬 번호 InsertedDate datetime NULL , -- 데이터 생성일 InsertedUserId nvarchar(24) NULL , -- 데이터 생성자 고유번호 UpdatedDate datetime NULL , -- 데이터 변경일 UpdatedUserId nvarchar(24) NULL , -- 데이터 변경자 고유번호 AttendComment nvarchar(200) NULL , -- FmsWorkDailyRecordAttend와 통합 PlanContent nvarchar(4000) NULL , -- FmsWorkDailyRecordPlan과 통합 EtcContent nvarchar(4000) NULL , -- FmsWorkDailyRecordEtc와 통합 PRIMARY KEY (SiteId ASC , WorkDate ASC , TemplateId ASC , DailyRecordId ASC , BusinessFieldId ASC) ); DBCC CHECKIDENT( FmsWorkDailyRecord, RESEED, 1 ) -- work_diary_prevention_WodiPrevention -- 작업신청현황 -- work/diary/prevention --CREATE TABLE WodiPrevention 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 , -- 특기사항 FmsWorkDailyRecordSpecial과 통합 PRIMARY KEY (SiteId ASC , WorkDate ASC , TemplateId ASC , DailyRecordId ASC , BusinessFieldId ASC , PreventionId ASC) ); DBCC CHECKIDENT( FmsWorkDailyRecordPrevention, RESEED, 1 ) -- work_diary_template_WodiTemplate -- 일지양식 -- work/diary/template 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 WodiWork 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 WodiWorkingHour 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) ; --------------------------------------------------- -- 작업일지관리 --------------------------------------------------- -- work_diary_attend_WodiAttend --CREATE TABLE WodiAttend --CREATE TABLE FmsWorkDailyRecordAttend --( -- WorkDate datetime NOT NULL , -- TemplateId int NOT NULL , -- DailyRecordId int NOT NULL , -- BusinessFieldId int NOT NULL , -- AttendComment nvarchar(200) NULL , -- PRIMARY KEY (WorkDate ASC,TemplateId ASC,DailyRecordId ASC,BusinessFieldId ASC) --); --CREATE TABLE FmsWodiEtc --CREATE TABLE FmsWorkDailyRecordEtc --( -- WorkDate datetime NOT NULL , -- TemplateId int NOT NULL , -- DailyRecordId int NOT NULL , -- BusinessFieldId int NOT NULL , -- EtcContent nvarchar(4000) NULL , -- PRIMARY KEY (WorkDate ASC,TemplateId ASC,DailyRecordId ASC,BusinessFieldId ASC) --); -- 특기 사항 --CREATE TABLE WodiSpecial --CREATE TABLE FmsWorkDailyRecordSpecial --( -- WorkDate datetime NOT NULL , -- TemplateId int NOT NULL , -- DailyRecordId int NOT NULL , -- PreventionId int NOT NULL , -- BusinessFieldId int NOT NULL , -- SpecialContent nvarchar(4000) NULL , -- PRIMARY KEY (WorkDate ASC,TemplateId ASC,DailyRecordId ASC,BusinessFieldId ASC) --); -- work_diary_plan_WodiPlan -- 예정사항 --CREATE TABLE WodiPlan --CREATE TABLE FmsWorkDailyRecordPlan --( -- WorkDate datetime NOT NULL , -- TemplateId int NOT NULL , -- DailyRecordId int NOT NULL , -- BusinessFieldId int NOT NULL , -- PlanContent nvarchar(4000) NULL , -- PRIMARY KEY (WorkDate ASC,TemplateId ASC,DailyRecordId ASC,BusinessFieldId ASC) --); -- 온도순찰일지 -- CREATE TABLE WodItemparaturePatrol --CREATE TABLE FmsWodItemparaturePatrol --( -- WorkDate datetime NOT NULL , -- TemplateId numeric(8,0) NOT NULL , -- DailyRecordId int NOT NULL , -- TemparaturePatrolId int NOT NULL , -- BusinessFieldId int NOT NULL , -- datetime nvarchar(20) NULL , -- position nvarchar(20) NULL , -- F05 nvarchar(10) NULL , -- F04 nvarchar(10) NULL , -- F03 nvarchar(10) NULL , -- F02 nvarchar(10) NULL , -- F01 nvarchar(10) NULL , -- B01 nvarchar(10) NULL , -- B02 nvarchar(10) NULL , -- InsertedUser nvarchar(20) NULL , -- InsertedDate datetime NULL , -- PRIMARY KEY (WorkDate ASC,TemplateId ASC,DailyRecordId ASC,temparaturePatrolId ASC,BusinessFieldId ASC) --) --; -- work_diary_lightingPatrol_WodiLightingPatrol -- 조명순찰일지 -- work/diary/lightingPatrol --CREATE TABLE WodiLightingPatrol --CREATE TABLE FmsWodiLightingPatrol --( -- WorkDate datetime NOT NULL , -- TemplateId numeric(8,0) NOT NULL , -- DailyRecordId int NOT NULL , -- LightingPatrolId int NOT NULL , -- BusinessFieldId int NOT NULL , -- datetime nvarchar(20) NULL , -- F05 nvarchar(10) NULL , -- F04 nvarchar(10) NULL , -- F03 nvarchar(10) NULL , -- F02 nvarchar(10) NULL , -- F01 nvarchar(10) NULL , -- B01 nvarchar(10) NULL , -- B02 nvarchar(10) NULL , -- Park nvarchar(10) NULL , -- Landscape nvarchar(10) NULL , -- InsertedUser nvarchar(20) NULL , -- InsertedDate datetime NULL , -- PRIMARY KEY (WorkDate ASC,TemplateId ASC,DailyRecordId ASC,LightingPatrolId ASC,BusinessFieldId ASC) --) --; --CREATE TABLE FmsWodiLightingPatrolSpecial --( -- WorkDate datetime NOT NULL , -- TemplateId numeric(8,0) NOT NULL , -- DailyRecordId int NOT NULL , -- BusinessFieldId int NOT NULL , -- SpecialContent nvarchar(200) NULL , -- PRIMARY KEY (WorkDate ASC,TemplateId ASC,DailyRecordId ASC,BusinessFieldId ASC) --) --; --------------------------------------------------- -- 작업일지관리 --------------------------------------------------- --ALTER TABLE WodiAttend -- ADD FOREIGN KEY (WorkDate,TemplateId,DailyRecordId,BusinessFieldId) REFERENCES FmsWorkDailyRecord(WorkDate,TemplateId,DailyRecordId,BusinessFieldId) --; --ALTER TABLE WodiEtc -- ADD FOREIGN KEY (WorkDate,TemplateId,DailyRecordId,BusinessFieldId) REFERENCES FmsWorkDailyRecord(WorkDate,TemplateId,DailyRecordId,BusinessFieldId) --; --ALTER TABLE WodiLightingPatrol -- ADD FOREIGN KEY (WorkDate,TemplateId,DailyRecordId,BusinessFieldId) REFERENCES FmsWorkDailyRecord(WorkDate,TemplateId,DailyRecordId,BusinessFieldId) --; --ALTER TABLE WodiLightingPatrolSpecial -- ADD FOREIGN KEY (WorkDate,TemplateId,DailyRecordId,BusinessFieldId) REFERENCES FmsWorkDailyRecord(WorkDate,TemplateId,DailyRecordId,BusinessFieldId) --; --ALTER TABLE WodiPlan -- ADD FOREIGN KEY (WorkDate,TemplateId,DailyRecordId,BusinessFieldId) REFERENCES FmsWorkDailyRecord(WorkDate,TemplateId,DailyRecordId,BusinessFieldId) --; --ALTER TABLE WodiSpecial -- ADD FOREIGN KEY (WorkDate,TemplateId,DailyRecordId,BusinessFieldId) REFERENCES FmsWorkDailyRecord(WorkDate,TemplateId,DailyRecordId,BusinessFieldId) --; --ALTER TABLE WodiTemparaturePatrol -- ADD FOREIGN KEY (WorkDate,TemplateId,DailyRecordId,BusinessFieldId) REFERENCES FmsWorkDailyRecord(WorkDate,TemplateId,DailyRecordId,BusinessFieldId) --;