0319543bbe60d4297614a39a9799fb98e18bcdd9.svn-base 15 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382
  1. ---------------------------------------------------
  2. -- 수시점검
  3. ---------------------------------------------------
  4. -- work_drive_date_WodrDate
  5. -- 운전일지
  6. -- work/drive/date
  7. --CREATE TABLE WodrDate
  8. CREATE TABLE FmsWorkOperationDate
  9. (
  10. SiteId int NOT NULL , -- 현장 고유번호
  11. FacilityCode int NOT NULL , -- 설비코드
  12. CheckDateTime datetime NOT NULL , -- 확인날짜
  13. PRIMARY KEY (SiteId ASC , FacilityCode ASC , CheckDateTime ASC)
  14. );
  15. -- work_drive_item_WodrItem
  16. -- 검침결과
  17. -- work/drive/item
  18. --CREATE TABLE WodrItem
  19. CREATE TABLE FmsWorkOperationItem
  20. (
  21. SiteId int NOT NULL , -- 현장 고유번호
  22. FacilityCode int NOT NULL , -- 설비코드
  23. CheckDateTime datetime NOT NULL , -- 확인일시
  24. -- CheckHourMin CHAR(5) NOT NULL , -- 검침시간 검칠일자로 대체
  25. ItemId int identity(1,1) NOT NULL , -- 작업 항목 고유번호
  26. ItemName nvarchar(100) NULL , -- 작업 항목 명
  27. ItemResult nvarchar(20) NULL , -- 작업 항목 결과
  28. ResultUnit nvarchar(10) NOT NULL , -- 작업 결과유닛
  29. SortOrderNo int NULL , -- 정렬 번호
  30. InsertedDate datetime NULL , -- 데이터 생성일
  31. InsertedUserId nvarchar(24) NULL , -- 데이터 생성자 고유번호
  32. UpdatedDate datetime NULL , -- 데이터 변경일
  33. UpdatedUserId nvarchar(24) NULL , -- 데이터 변경자 고유번호
  34. PRIMARY KEY (SiteId ASC , FacilityCode ASC , CheckDateTime ASC , ItemId ASC)
  35. );
  36. DBCC CHECKIDENT( FmsWorkOperationItem, RESEED, 1 )
  37. --CREATE TABLE WodrTime
  38. CREATE TABLE FmsWorkOperationTime
  39. (
  40. SiteId int NOT NULL , -- 현장 고유번호
  41. FacilityCode int NOT NULL , -- 설비코드
  42. CheckDatetime datetime NOT NULL , -- 확인날짜
  43. --CheckDate datetime NOT NULL ,
  44. --CheckHourMin CHAR(5) NOT NULL ,
  45. InsertedDate datetime NULL , -- 데이터 생성일
  46. InsertedUserId nvarchar(24) NULL , -- 데이터 생성자 고유번호
  47. UpdatedDate datetime NULL , -- 데이터 변경일
  48. UpdatedUserId nvarchar(24) NULL , -- 데이터 변경자 고유번호
  49. PRIMARY KEY (SiteId ASC , FacilityCode ASC , CheckDateTime ASC)
  50. );
  51. ---------------------------------------------------
  52. -- 작업일지관리
  53. ---------------------------------------------------
  54. --CREATE TABLE WodiDiary
  55. CREATE TABLE FmsWorkDailyRecord
  56. (
  57. SiteId int NOT NULL , -- 현장 고유번호
  58. WorkDate datetime NOT NULL , -- 일일 작업 날짜
  59. TemplateId int NOT NULL , -- 작업 견본 고유번호
  60. --DiaryId int NOT NULL ,
  61. DailyRecordId int identity(1,1) NOT NULL , -- 일일 작업 고유번호
  62. BusinessFieldId int NOT NULL , -- 작업 장소 고유번호
  63. UpdateFlag bit NULL , -- 업데이트 유무
  64. DeleteFlag bit NULL , -- 삭제 유무
  65. DiaryName nvarchar(100) NOT NULL , -- 작업 명
  66. Weather nvarchar(20) NULL , -- 날씨 정보
  67. ConfirmdatetimeStep1 datetime NULL , -- 승인날짜 1
  68. ConfirmUserIdStep1 nvarchar(24) NULL , -- 승인 사용자 고유번호 1
  69. ConfirmdatetimeStep2 datetime NULL , -- 승인날짜 2
  70. ConfirmUserIdStep2 nvarchar(24) NULL , -- 승인 사용자 고유번호 2
  71. ConfirmdatetimeStep3 datetime NULL , -- 승인날짜 3
  72. ConfirmUserIdStep3 nvarchar(24) NULL , -- 승인 사용자 고유번호 3
  73. ConfirmdatetimeStep4 datetime NULL , -- 승인날짜 4
  74. ConfirmUserIdStep4 nvarchar(24) NULL , -- 승인 사용자 고유번호 4
  75. ConfirmdatetimeStep5 datetime NULL , -- 승인날짜 5
  76. ConfirmUserIdStep5 nvarchar(24) NULL , -- 승인 사용자 고유번호 5
  77. SortOrderNo int NULL , -- 정렬 번호
  78. InsertedDate datetime NULL , -- 데이터 생성일
  79. InsertedUserId nvarchar(24) NULL , -- 데이터 생성자 고유번호
  80. UpdatedDate datetime NULL , -- 데이터 변경일
  81. UpdatedUserId nvarchar(24) NULL , -- 데이터 변경자 고유번호
  82. AttendComment nvarchar(200) NULL , -- FmsWorkDailyRecordAttend와 통합
  83. PlanContent nvarchar(4000) NULL , -- FmsWorkDailyRecordPlan과 통합
  84. EtcContent nvarchar(4000) NULL , -- FmsWorkDailyRecordEtc와 통합
  85. PRIMARY KEY (SiteId ASC , WorkDate ASC , TemplateId ASC , DailyRecordId ASC , BusinessFieldId ASC)
  86. );
  87. DBCC CHECKIDENT( FmsWorkDailyRecord, RESEED, 1 )
  88. -- work_diary_prevention_WodiPrevention
  89. -- 작업신청현황
  90. -- work/diary/prevention
  91. --CREATE TABLE WodiPrevention
  92. CREATE TABLE FmsWorkDailyRecordPrevention
  93. (
  94. SiteId int NOT NULL , -- 현장 고유번호
  95. WorkDate datetime NOT NULL , -- 작업일자
  96. TemplateId int NOT NULL , -- 작업양식 고유번호
  97. DailyRecordId int NOT NULL , -- 일일작업 고유번호
  98. BusinessFieldId int NOT NULL , -- 점검영역 고유번호
  99. PreventionId int identity(1,1) NOT NULL , -- 예방 정비 고유번호
  100. ShopNo nvarchar(50) NULL , -- 층호
  101. ShopName nvarchar(100) NULL , -- 상호
  102. WorkTime nvarchar(50) NULL , -- 작업시간
  103. PreventionContent nvarchar(200) NULL , -- 작업내용
  104. SignificantContent nvarchar(4000) NULL , -- 특기사항 FmsWorkDailyRecordSpecial과 통합
  105. PRIMARY KEY (SiteId ASC , WorkDate ASC , TemplateId ASC , DailyRecordId ASC , BusinessFieldId ASC , PreventionId ASC)
  106. );
  107. DBCC CHECKIDENT( FmsWorkDailyRecordPrevention, RESEED, 1 )
  108. -- work_diary_template_WodiTemplate
  109. -- 일지양식
  110. -- work/diary/template
  111. CREATE TABLE FmsWorkDailyRecordTemplate
  112. (
  113. SiteId int NOT NULL , -- 현장 고유번호
  114. BusinessFieldId int NOT NULL , -- 작업영역 고유번호
  115. TemplateId int identity(1,1) NOT NULL , -- 작업유형 고유번호
  116. TemplateName nvarchar(100) NOT NULL , -- 작업유형 이름
  117. StartDate datetime NOT NULL , -- 작업 시작일
  118. IsUse bit NULL , -- 사용 유무
  119. SortOrderNo int NULL , -- 정렬 번호
  120. InsertedDate datetime NULL , -- 데이터 생성일
  121. InsertedUserId nvarchar(24) NULL , -- 데이터 생성자 고유번호
  122. UpdatedDate datetime NULL , -- 데이터 변경일
  123. UpdatedUserId nvarchar(24) NULL , -- 데이터 변경자 고유번호
  124. PRIMARY KEY( SiteId ASC, BusinessFieldId ASC, TemplateId ASC )
  125. );
  126. DBCC CHECKIDENT( FmsWorkDailyRecordTemplate, RESEED, 1 )
  127. --CREATE TABLE WodiWork
  128. CREATE TABLE FmsWorkDailyRecordWork
  129. (
  130. SiteId int NOT NULL , -- 현장 고유번호
  131. WorkDate datetime NOT NULL , -- 작업일자
  132. DailyRecordId int NOT NULL , -- 작업일지 고유번호
  133. TemplateId int NOT NULL , -- 작업유형 고유번호
  134. BusinessFieldId int NOT NULL , -- 작업영역 고유번호
  135. WorkId int identity(1,1) NOT NULL , -- 작업 고유번호
  136. OrderId int NOT NULL , -- 작업 지시 고유번호
  137. ResultId int NOT NULL , -- 작업 결과 고유번호
  138. DeleteFlag bit NOT NULL , -- 작업 취소 플래그
  139. PRIMARY KEY( SiteId ASC , WorkDate ASC , DailyRecordId ASC , TemplateId ASC , BusinessFieldId ASC , WorkId ASC )
  140. );
  141. DBCC CHECKIDENT( FmsWorkDailyRecordWork, RESEED, 1 )
  142. --CREATE TABLE WodiWorkingHour
  143. CREATE TABLE FmsWorkDailyRecordWorkingHour
  144. (
  145. SiteId int NOT NULL , -- 현장 고유번호
  146. WorkingDate datetime NOT NULL , -- 작업 일자
  147. FacilityCode int NOT NULL , -- 설비코드
  148. WorkType int NOT NULL , -- 작업 유형
  149. WorkingMinute int NULL , -- 작업 소요 시간
  150. PRIMARY KEY( SiteId ASC , FacilityCode ASC , WorkType ASC , WorkingDate ASC )
  151. );
  152. ---------------------------------------------------
  153. -- 수시점검
  154. ---------------------------------------------------
  155. ALTER TABLE FmsWorkOperationDate
  156. ADD FOREIGN KEY (SiteId , FacilityCode) REFERENCES CmFacility(SiteId , FacilityCode)
  157. ;
  158. ALTER TABLE FmsWorkOperationItem
  159. ADD FOREIGN KEY (SiteId , FacilityCode , CheckDateTime) REFERENCES FmsWorkOperationTime(SiteId , FacilityCode , CheckDateTime)
  160. ;
  161. ALTER TABLE FmsWorkOperationTime
  162. ADD FOREIGN KEY (SiteId , FacilityCode , CheckDateTime) REFERENCES FmsWorkOperationDate(SiteId , FacilityCode , CheckDateTime)
  163. ;
  164. ---------------------------------------------------
  165. -- 작업일지관리
  166. ---------------------------------------------------
  167. ALTER TABLE FmsWorkDailyRecord
  168. ADD FOREIGN KEY (SiteId , BusinessFieldId , TemplateId ) REFERENCES FmsWorkDailyRecordTemplate(SiteId , BusinessFieldId , TemplateId )
  169. ;
  170. ALTER TABLE FmsWorkDailyRecordTemplate
  171. ADD FOREIGN KEY (SiteId , BusinessFieldId) REFERENCES CmBusinessField(SiteId , BusinessFieldId)
  172. ;
  173. ALTER TABLE FmsWorkDailyRecordWork
  174. ADD FOREIGN KEY (SiteId , WorkDate , TemplateId , DailyRecordId , BusinessFieldId) REFERENCES FmsWorkDailyRecord(SiteId , WorkDate , TemplateId , DailyRecordId , BusinessFieldId)
  175. ;
  176. ALTER TABLE FmsWorkDailyRecordWork
  177. ADD FOREIGN KEY (SiteId , OrderId , ResultId) REFERENCES FmsWorkResult(SiteId , OrderId , ResultId)
  178. ;
  179. ALTER TABLE FmsWorkDailyRecordWorkingHour
  180. ADD FOREIGN KEY (SiteId , FacilityCode) REFERENCES CmFacility(SiteId , FacilityCode)
  181. ;
  182. ALTER TABLE FmsWorkDailyRecordPrevention
  183. ADD FOREIGN KEY (SiteId , WorkDate , TemplateId , DailyRecordId , BusinessFieldId) REFERENCES FmsWorkDailyRecord(SiteId , WorkDate , TemplateId , DailyRecordId , BusinessFieldId)
  184. ;
  185. ---------------------------------------------------
  186. -- 작업일지관리
  187. ---------------------------------------------------
  188. -- work_diary_attend_WodiAttend
  189. --CREATE TABLE WodiAttend
  190. --CREATE TABLE FmsWorkDailyRecordAttend
  191. --(
  192. -- WorkDate datetime NOT NULL ,
  193. -- TemplateId int NOT NULL ,
  194. -- DailyRecordId int NOT NULL ,
  195. -- BusinessFieldId int NOT NULL ,
  196. -- AttendComment nvarchar(200) NULL ,
  197. -- PRIMARY KEY (WorkDate ASC,TemplateId ASC,DailyRecordId ASC,BusinessFieldId ASC)
  198. --);
  199. --CREATE TABLE FmsWodiEtc
  200. --CREATE TABLE FmsWorkDailyRecordEtc
  201. --(
  202. -- WorkDate datetime NOT NULL ,
  203. -- TemplateId int NOT NULL ,
  204. -- DailyRecordId int NOT NULL ,
  205. -- BusinessFieldId int NOT NULL ,
  206. -- EtcContent nvarchar(4000) NULL ,
  207. -- PRIMARY KEY (WorkDate ASC,TemplateId ASC,DailyRecordId ASC,BusinessFieldId ASC)
  208. --);
  209. -- 특기 사항
  210. --CREATE TABLE WodiSpecial
  211. --CREATE TABLE FmsWorkDailyRecordSpecial
  212. --(
  213. -- WorkDate datetime NOT NULL ,
  214. -- TemplateId int NOT NULL ,
  215. -- DailyRecordId int NOT NULL ,
  216. -- PreventionId int NOT NULL ,
  217. -- BusinessFieldId int NOT NULL ,
  218. -- SpecialContent nvarchar(4000) NULL ,
  219. -- PRIMARY KEY (WorkDate ASC,TemplateId ASC,DailyRecordId ASC,BusinessFieldId ASC)
  220. --);
  221. -- work_diary_plan_WodiPlan
  222. -- 예정사항
  223. --CREATE TABLE WodiPlan
  224. --CREATE TABLE FmsWorkDailyRecordPlan
  225. --(
  226. -- WorkDate datetime NOT NULL ,
  227. -- TemplateId int NOT NULL ,
  228. -- DailyRecordId int NOT NULL ,
  229. -- BusinessFieldId int NOT NULL ,
  230. -- PlanContent nvarchar(4000) NULL ,
  231. -- PRIMARY KEY (WorkDate ASC,TemplateId ASC,DailyRecordId ASC,BusinessFieldId ASC)
  232. --);
  233. -- 온도순찰일지
  234. -- CREATE TABLE WodItemparaturePatrol
  235. --CREATE TABLE FmsWodItemparaturePatrol
  236. --(
  237. -- WorkDate datetime NOT NULL ,
  238. -- TemplateId numeric(8,0) NOT NULL ,
  239. -- DailyRecordId int NOT NULL ,
  240. -- TemparaturePatrolId int NOT NULL ,
  241. -- BusinessFieldId int NOT NULL ,
  242. -- datetime nvarchar(20) NULL ,
  243. -- position nvarchar(20) NULL ,
  244. -- F05 nvarchar(10) NULL ,
  245. -- F04 nvarchar(10) NULL ,
  246. -- F03 nvarchar(10) NULL ,
  247. -- F02 nvarchar(10) NULL ,
  248. -- F01 nvarchar(10) NULL ,
  249. -- B01 nvarchar(10) NULL ,
  250. -- B02 nvarchar(10) NULL ,
  251. -- InsertedUser nvarchar(20) NULL ,
  252. -- InsertedDate datetime NULL ,
  253. -- PRIMARY KEY (WorkDate ASC,TemplateId ASC,DailyRecordId ASC,temparaturePatrolId ASC,BusinessFieldId ASC)
  254. --)
  255. --;
  256. -- work_diary_lightingPatrol_WodiLightingPatrol
  257. -- 조명순찰일지
  258. -- work/diary/lightingPatrol
  259. --CREATE TABLE WodiLightingPatrol
  260. --CREATE TABLE FmsWodiLightingPatrol
  261. --(
  262. -- WorkDate datetime NOT NULL ,
  263. -- TemplateId numeric(8,0) NOT NULL ,
  264. -- DailyRecordId int NOT NULL ,
  265. -- LightingPatrolId int NOT NULL ,
  266. -- BusinessFieldId int NOT NULL ,
  267. -- datetime nvarchar(20) NULL ,
  268. -- F05 nvarchar(10) NULL ,
  269. -- F04 nvarchar(10) NULL ,
  270. -- F03 nvarchar(10) NULL ,
  271. -- F02 nvarchar(10) NULL ,
  272. -- F01 nvarchar(10) NULL ,
  273. -- B01 nvarchar(10) NULL ,
  274. -- B02 nvarchar(10) NULL ,
  275. -- Park nvarchar(10) NULL ,
  276. -- Landscape nvarchar(10) NULL ,
  277. -- InsertedUser nvarchar(20) NULL ,
  278. -- InsertedDate datetime NULL ,
  279. -- PRIMARY KEY (WorkDate ASC,TemplateId ASC,DailyRecordId ASC,LightingPatrolId ASC,BusinessFieldId ASC)
  280. --)
  281. --;
  282. --CREATE TABLE FmsWodiLightingPatrolSpecial
  283. --(
  284. -- WorkDate datetime NOT NULL ,
  285. -- TemplateId numeric(8,0) NOT NULL ,
  286. -- DailyRecordId int NOT NULL ,
  287. -- BusinessFieldId int NOT NULL ,
  288. -- SpecialContent nvarchar(200) NULL ,
  289. -- PRIMARY KEY (WorkDate ASC,TemplateId ASC,DailyRecordId ASC,BusinessFieldId ASC)
  290. --)
  291. --;
  292. ---------------------------------------------------
  293. -- 작업일지관리
  294. ---------------------------------------------------
  295. --ALTER TABLE WodiAttend
  296. -- ADD FOREIGN KEY (WorkDate,TemplateId,DailyRecordId,BusinessFieldId) REFERENCES FmsWorkDailyRecord(WorkDate,TemplateId,DailyRecordId,BusinessFieldId)
  297. --;
  298. --ALTER TABLE WodiEtc
  299. -- ADD FOREIGN KEY (WorkDate,TemplateId,DailyRecordId,BusinessFieldId) REFERENCES FmsWorkDailyRecord(WorkDate,TemplateId,DailyRecordId,BusinessFieldId)
  300. --;
  301. --ALTER TABLE WodiLightingPatrol
  302. -- ADD FOREIGN KEY (WorkDate,TemplateId,DailyRecordId,BusinessFieldId) REFERENCES FmsWorkDailyRecord(WorkDate,TemplateId,DailyRecordId,BusinessFieldId)
  303. --;
  304. --ALTER TABLE WodiLightingPatrolSpecial
  305. -- ADD FOREIGN KEY (WorkDate,TemplateId,DailyRecordId,BusinessFieldId) REFERENCES FmsWorkDailyRecord(WorkDate,TemplateId,DailyRecordId,BusinessFieldId)
  306. --;
  307. --ALTER TABLE WodiPlan
  308. -- ADD FOREIGN KEY (WorkDate,TemplateId,DailyRecordId,BusinessFieldId) REFERENCES FmsWorkDailyRecord(WorkDate,TemplateId,DailyRecordId,BusinessFieldId)
  309. --;
  310. --ALTER TABLE WodiSpecial
  311. -- ADD FOREIGN KEY (WorkDate,TemplateId,DailyRecordId,BusinessFieldId) REFERENCES FmsWorkDailyRecord(WorkDate,TemplateId,DailyRecordId,BusinessFieldId)
  312. --;
  313. --ALTER TABLE WodiTemparaturePatrol
  314. -- ADD FOREIGN KEY (WorkDate,TemplateId,DailyRecordId,BusinessFieldId) REFERENCES FmsWorkDailyRecord(WorkDate,TemplateId,DailyRecordId,BusinessFieldId)
  315. --;