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