---------------------------------------------------
-- 수시점검
---------------------------------------------------

-- 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)
--;
