| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382 | 
							- ---------------------------------------------------
 
- -- 수시점검
 
- ---------------------------------------------------
 
- -- 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)
 
- --;
 
 
  |