hytos / ID2.Manager / ID2.Manager.Dapper / Repository / DocumentRepository.cs @ e1377864
이력 | 보기 | 이력해설 | 다운로드 (23.8 KB)
1 | 5898479a | yoush97 | using System; |
---|---|---|---|
2 | using System.Collections.Generic; |
||
3 | using System.Linq; |
||
4 | using System.Text; |
||
5 | using System.Threading.Tasks; |
||
6 | |||
7 | 54977253 | yoush97 | using System.Data; |
8 | |||
9 | 5898479a | yoush97 | using ID2.Manager.Data.Models; |
10 | |||
11 | 8e373ccf | yoush97 | using Dapper; |
12 | 08499f5f | taeseongkim | using System.ComponentModel; |
13 | using System.Reflection; |
||
14 | 8e373ccf | yoush97 | |
15 | 5898479a | yoush97 | namespace ID2.Manager.Dapper.Repository |
16 | { |
||
17 | public class DocumentRepository : BaseRepository |
||
18 | { |
||
19 | public DocumentRepository(string connectionStr) : base(connectionStr) { } |
||
20 | |||
21 | a4a166e2 | yoush97 | public IEnumerable<Documents> GetAllDocuments(string projectGroupID) |
22 | 5898479a | yoush97 | { |
23 | a4a166e2 | yoush97 | var dynamicParameters = new DynamicParameters(); |
24 | StringBuilder sbWhere = new StringBuilder(); |
||
25 | var parameters = new Dictionary<string, object>(); |
||
26 | if (!string.IsNullOrEmpty(projectGroupID)) |
||
27 | { |
||
28 | sbWhere.Append(" and doc.RefProjectCode in (select Code from dbo.Projects where ParentID=@RefGroupID) "); |
||
29 | parameters.Add("RefGroupID", projectGroupID); |
||
30 | } |
||
31 | |||
32 | 82705273 | yoush97 | try |
33 | { |
||
34 | string query = $@" |
||
35 | 54977253 | yoush97 | select doc.* |
36 | 7066b8a9 | yoush97 | from dbo.Documents doc |
37 | a4a166e2 | yoush97 | where doc.IsDeleted=0 {sbWhere} |
38 | 7066b8a9 | yoush97 | order by doc.Seq;"; |
39 | a4a166e2 | yoush97 | |
40 | if (parameters.Count > 0) |
||
41 | { |
||
42 | dynamicParameters.AddDynamicParams(parameters); |
||
43 | } |
||
44 | |||
45 | return Query<Documents>(query, dynamicParameters); |
||
46 | 82705273 | yoush97 | } |
47 | catch (Exception ex) |
||
48 | { |
||
49 | throw ex; |
||
50 | } |
||
51 | 7066b8a9 | yoush97 | } |
52 | 08499f5f | taeseongkim | static string GetDescriptionFromAttribute(MemberInfo member) |
53 | { |
||
54 | if (member == null) return null; |
||
55 | |||
56 | var attrib = (DescriptionAttribute)Attribute.GetCustomAttribute(member, typeof(DescriptionAttribute), false); |
||
57 | return (attrib?.Description ?? member.Name).ToLower(); |
||
58 | } |
||
59 | |||
60 | 7066b8a9 | yoush97 | |
61 | 2aa2a446 | yoush97 | public (IEnumerable<Documents> dwgs, int totalCnt) GetDocuments(string projectGroupID, List<string> dateTypes, DateTime? frDate, DateTime? toDate, string projectCode, string personIncharge, string jobLevel, string documentNo, string isToIsDiscussion, string isFrReviewStatus, string id2Status, string id2Issues, string avevaStatus, string avevaIssues, string prodIsResult, string clientIsResult, string isGateWay, string isRegSystem) |
62 | 7066b8a9 | yoush97 | { |
63 | 08499f5f | taeseongkim | var map = new CustomPropertyTypeMap(typeof(AttFileInfo), (type, columnName) |
64 | => type.GetProperties().FirstOrDefault(prop => GetDescriptionFromAttribute(prop) == columnName.ToLower())); |
||
65 | SqlMapper.SetTypeMap(typeof(AttFileInfo), map); |
||
66 | |||
67 | |||
68 | 7066b8a9 | yoush97 | var dynamicParameters = new DynamicParameters(); |
69 | 54977253 | yoush97 | dynamicParameters.Add("Total", dbType: DbType.Int32, direction: ParameterDirection.Output); |
70 | 7066b8a9 | yoush97 | |
71 | 8e373ccf | yoush97 | StringBuilder sbWhere = new StringBuilder(); |
72 | bf7d1c08 | yoush97 | StringBuilder sbTotalWhere = new StringBuilder(); |
73 | 8e373ccf | yoush97 | var parameters = new Dictionary<string, object>(); |
74 | b2ff5ff4 | yoush97 | if (dateTypes.Count > 0 && (frDate != null || toDate != null)) |
75 | { |
||
76 | List<string> subWheres = new List<string>(); |
||
77 | foreach (string dateType in dateTypes) |
||
78 | { |
||
79 | StringBuilder sbSubWhere = new StringBuilder(); |
||
80 | if (frDate != null) |
||
81 | { |
||
82 | sbSubWhere.Insert(0, $@"convert(datetime, '{Convert.ToDateTime(frDate).AddDays(-1):yyyy-MM-dd 23:59:59.997}') < doc.{dateType}"); |
||
83 | } |
||
84 | if (toDate != null) |
||
85 | { |
||
86 | if (frDate != null) |
||
87 | sbSubWhere.Append($@" and "); |
||
88 | sbSubWhere.Append($@"doc.{dateType} < convert(datetime, '{Convert.ToDateTime(toDate).AddDays(1):yyyy-MM-dd 00:00:00.000}')"); |
||
89 | } |
||
90 | |||
91 | if (sbSubWhere.Length > 0) |
||
92 | { |
||
93 | subWheres.Add("(" + sbSubWhere.ToString() + ")"); |
||
94 | } |
||
95 | } |
||
96 | |||
97 | if (subWheres.Count > 0) |
||
98 | { |
||
99 | sbWhere.Append(" and (" + string.Join(" or ", subWheres) + ") "); |
||
100 | } |
||
101 | } |
||
102 | ea97eee6 | yoush97 | if (string.IsNullOrEmpty(projectCode)) |
103 | { |
||
104 | sbWhere.Append(" and isnull(doc.RefProjectCode,'') in (select Code from dbo.Projects where ParentID=@RefGroupID union all select '') "); |
||
105 | e8a86b9b | yoush97 | sbTotalWhere.Append(" and isnull(doc.RefProjectCode,'') in (select Code from dbo.Projects where ParentID=@RefGroupID union all select '') "); |
106 | ea97eee6 | yoush97 | parameters.Add("RefGroupID", projectGroupID); |
107 | } |
||
108 | else |
||
109 | 8e373ccf | yoush97 | { |
110 | e8a86b9b | yoush97 | sbTotalWhere.Append(" and isnull(doc.RefProjectCode,'') in (select Code from dbo.Projects where ParentID=@RefGroupID union all select '') "); |
111 | parameters.Add("RefGroupID", projectGroupID); |
||
112 | |||
113 | 36a31d25 | yoush97 | sbWhere.Append(" and doc.RefProjectCode=@RefProjectCode "); |
114 | parameters.Add("RefProjectCode", projectCode); |
||
115 | 8e373ccf | yoush97 | } |
116 | if (!string.IsNullOrEmpty(personIncharge)) |
||
117 | { |
||
118 | sbWhere.Append(" and doc.PersonInCharge=@PersonInCharge "); |
||
119 | parameters.Add("PersonInCharge", personIncharge); |
||
120 | } |
||
121 | 08ea0584 | yoush97 | if (!string.IsNullOrEmpty(jobLevel)) |
122 | { |
||
123 | sbWhere.Append(" and doc.JobLevel=@JobLevel "); |
||
124 | parameters.Add("JobLevel", jobLevel); |
||
125 | } |
||
126 | 8e373ccf | yoush97 | if (!string.IsNullOrEmpty(documentNo)) |
127 | { |
||
128 | sbWhere.Append(" and doc.DocumentNo like '%' + @DocumentNo +'%' "); |
||
129 | parameters.Add("DocumentNo", documentNo); |
||
130 | } |
||
131 | |||
132 | 296ffcbd | yoush97 | if (!string.IsNullOrEmpty(isToIsDiscussion)) |
133 | { |
||
134 | sbWhere.Append(" and doc.ToIsDiscussion=@ToIsDiscussion "); |
||
135 | parameters.Add("ToIsDiscussion", isToIsDiscussion); |
||
136 | } |
||
137 | |||
138 | if (!string.IsNullOrEmpty(isFrReviewStatus)) |
||
139 | { |
||
140 | sbWhere.Append(" and doc.FrReviewStatus=@FrReviewStatus "); |
||
141 | parameters.Add("FrReviewStatus", isFrReviewStatus); |
||
142 | } |
||
143 | |||
144 | 08ea0584 | yoush97 | if (!string.IsNullOrEmpty(id2Status)) |
145 | { |
||
146 | sbWhere.Append(" and doc.ID2Status=@ID2Status "); |
||
147 | parameters.Add("ID2Status", id2Status); |
||
148 | } |
||
149 | 3cc84cb6 | yoush97 | |
150 | if (!string.IsNullOrEmpty(id2Issues)) |
||
151 | { |
||
152 | sbWhere.Append(" and (case when isnull(ltrim(rtrim(doc.ID2Issues)),'') = '' then 'No' else 'Yes' end)=@ID2Issues "); |
||
153 | parameters.Add("ID2Issues", id2Issues); |
||
154 | } |
||
155 | |||
156 | 08ea0584 | yoush97 | if (!string.IsNullOrEmpty(avevaStatus)) |
157 | { |
||
158 | sbWhere.Append(" and doc.AVEVAStatus=@AVEVAStatus "); |
||
159 | parameters.Add("AVEVAStatus", avevaStatus); |
||
160 | } |
||
161 | |||
162 | 3cc84cb6 | yoush97 | if (!string.IsNullOrEmpty(avevaIssues)) |
163 | { |
||
164 | sbWhere.Append(" and (case when isnull(ltrim(rtrim(doc.AVEVAIssues)),'') = '' then 'No' else 'Yes' end)=@AVEVAIssues "); |
||
165 | parameters.Add("AVEVAIssues", avevaIssues); |
||
166 | } |
||
167 | |||
168 | 08ea0584 | yoush97 | if (!string.IsNullOrEmpty(prodIsResult)) |
169 | { |
||
170 | sbWhere.Append(" and doc.ProdIsResult=@ProdIsResult "); |
||
171 | parameters.Add("ProdIsResult", prodIsResult); |
||
172 | } |
||
173 | if (!string.IsNullOrEmpty(clientIsResult)) |
||
174 | { |
||
175 | sbWhere.Append(" and doc.ClientIsResult=@ClientIsResult "); |
||
176 | parameters.Add("ClientIsResult", clientIsResult); |
||
177 | } |
||
178 | ee82162b | yoush97 | if (!string.IsNullOrEmpty(isGateWay)) |
179 | { |
||
180 | sbWhere.Append(" and doc.DTIsGateWay=@DTIsGateWay "); |
||
181 | parameters.Add("DTIsGateWay", isGateWay); |
||
182 | } |
||
183 | if (!string.IsNullOrEmpty(isRegSystem)) |
||
184 | { |
||
185 | sbWhere.Append(" and doc.DTIsRegSystem=@DTIsRegSystem "); |
||
186 | parameters.Add("DTIsRegSystem", isRegSystem); |
||
187 | } |
||
188 | 08ea0584 | yoush97 | |
189 | 82705273 | yoush97 | try |
190 | { |
||
191 | 08499f5f | taeseongkim | |
192 | //files.[FileID],files.[FileID] as AttFileID, files.RefID, files.Category, files.FileType, files.FileName, files.FilePath, files.FileExtension, files.FileData, files.Remark, files.CreatedDate, files.Creator, |
||
193 | fe310cac | yoush97 | string query = $@" |
194 | select doc.*, |
||
195 | files.[FileID] as FileID, files.*, |
||
196 | markus.PROJECT_NO as PROJECT_NO, markus.* |
||
197 | from dbo.Documents doc |
||
198 | LEFT OUTER JOIN AttachFIles files ON doc.DocID = fIles.RefID |
||
199 | LEFT OUTER JOIN |
||
200 | (SELECT [PROJECT_NO] as PROJECT_NO |
||
201 | ,[DOCUMENT_ID] as DocumentNo |
||
202 | 08499f5f | taeseongkim | |
203 | fe310cac | yoush97 | ,[PAGENUMBER] |
204 | ,[Text] as TEXT |
||
205 | ,[CREATE_DATE] as CREATE_DATE |
||
206 | ,[NAME] as CREATE_USER |
||
207 | FROM ViewMarkupData) markus |
||
208 | ON doc.DocumentNo = markus.DocumentNo |
||
209 | where doc.IsDeleted=0 {sbWhere} |
||
210 | order by doc.Seq |
||
211 | 7066b8a9 | yoush97 | |
212 | 3ccb1a8d | yoush97 | select @Total=count(*) from dbo.Documents doc where doc.IsDeleted=0 {sbTotalWhere} |
213 | 7066b8a9 | yoush97 | select @Total;"; |
214 | 8e373ccf | yoush97 | |
215 | 82705273 | yoush97 | if (parameters.Count > 0) |
216 | { |
||
217 | dynamicParameters.AddDynamicParams(parameters); |
||
218 | } |
||
219 | 5898479a | yoush97 | |
220 | e458a996 | taeseongkim | var docDictionary = new Dictionary<string, Documents>(); |
221 | |||
222 | 08499f5f | taeseongkim | var ret = MultiQuery<Documents, AttFileInfo, MarkupText, Documents>(query, |
223 | (document, attfile, markusText) => |
||
224 | e458a996 | taeseongkim | { |
225 | Documents doc; |
||
226 | |||
227 | 08499f5f | taeseongkim | if (!docDictionary.TryGetValue(document.DocID, out doc)) |
228 | e458a996 | taeseongkim | { |
229 | doc = document; |
||
230 | 08499f5f | taeseongkim | docDictionary.Add(doc.DocID, doc); |
231 | } |
||
232 | |||
233 | if (markusText != null) |
||
234 | { |
||
235 | e458a996 | taeseongkim | doc.Markups = doc.Markups ?? new List<MarkupText>(); |
236 | 08499f5f | taeseongkim | |
237 | a37dc9a7 | taeseongkim | if (!doc.Markups.Any(x => x.Equals(markusText))) |
238 | 08499f5f | taeseongkim | { |
239 | doc.Markups.Add(markusText); |
||
240 | } |
||
241 | } |
||
242 | |||
243 | if (attfile != null) |
||
244 | { |
||
245 | doc.AttFiles = doc.AttFiles ?? new List<AttFileInfo>(); |
||
246 | System.Diagnostics.Debug.WriteLine(attfile.FileName); |
||
247 | if (!doc.AttFiles.Any(x => x.FileID == attfile.FileID)) |
||
248 | { |
||
249 | e8a86b9b | yoush97 | switch (attfile.Category) |
250 | { |
||
251 | case "toreview": |
||
252 | doc.ToCapture++; |
||
253 | break; |
||
254 | case "frreview": |
||
255 | doc.FrCapture++; |
||
256 | break; |
||
257 | case "prodvalidation": |
||
258 | doc.ProdCapture++; |
||
259 | break; |
||
260 | case "clientvalidation": |
||
261 | doc.ClientCapture++; |
||
262 | break; |
||
263 | } |
||
264 | |||
265 | 08499f5f | taeseongkim | doc.AttFiles.Add(attfile); |
266 | } |
||
267 | e458a996 | taeseongkim | } |
268 | |||
269 | return doc; |
||
270 | |||
271 | a37dc9a7 | taeseongkim | }, dynamicParameters, splitOn: "DocID,FileID,PROJECT_NO").Distinct(); |
272 | 7066b8a9 | yoush97 | |
273 | 82705273 | yoush97 | int totalCount = dynamicParameters.Get<int>("Total"); |
274 | 7066b8a9 | yoush97 | |
275 | 82705273 | yoush97 | return (ret, totalCount); |
276 | } |
||
277 | catch (Exception ex) |
||
278 | { |
||
279 | throw ex; |
||
280 | } |
||
281 | 5898479a | yoush97 | } |
282 | 482f6326 | yoush97 | |
283 | ff990784 | yoush97 | public int ExistsDocument(string projectGroupID, List<string> newDwgNos) |
284 | { |
||
285 | var dynamicParameters = new DynamicParameters(); |
||
286 | StringBuilder sbWhere = new StringBuilder(); |
||
287 | var parameters = new Dictionary<string, object>(); |
||
288 | if (!string.IsNullOrEmpty(projectGroupID)) |
||
289 | { |
||
290 | sbWhere.Append(" and doc.RefProjectCode in (select Code from dbo.Projects where ParentID=@RefGroupID) "); |
||
291 | parameters.Add("RefGroupID", projectGroupID); |
||
292 | } |
||
293 | |||
294 | if (newDwgNos.Count > 0) |
||
295 | { |
||
296 | string dwgNoList = string.Join("','", newDwgNos.Where(x => !string.IsNullOrEmpty(x)).Select(x => x).ToList()); |
||
297 | |||
298 | if (dwgNoList.Length > 0) |
||
299 | { |
||
300 | if (!string.IsNullOrEmpty(projectGroupID)) |
||
301 | { |
||
302 | sbWhere.Append($@" and doc.DocumentNo in ('{dwgNoList}') "); |
||
303 | } |
||
304 | } |
||
305 | } |
||
306 | |||
307 | try |
||
308 | { |
||
309 | string query = $@" |
||
310 | select count(*) cnt |
||
311 | from dbo.Documents doc |
||
312 | where doc.IsDeleted=0 {sbWhere}"; |
||
313 | |||
314 | if (parameters.Count > 0) |
||
315 | { |
||
316 | dynamicParameters.AddDynamicParams(parameters); |
||
317 | } |
||
318 | |||
319 | return ExecuteScalar<int>(query, dynamicParameters); |
||
320 | } |
||
321 | catch (Exception ex) |
||
322 | { |
||
323 | throw ex; |
||
324 | } |
||
325 | } |
||
326 | |||
327 | 54977253 | yoush97 | public bool SetDocumentData(List<Documents> docList, List<Documents> delDocList, string userId) |
328 | 482f6326 | yoush97 | { |
329 | bool isSuccess = false; |
||
330 | |||
331 | try |
||
332 | { |
||
333 | d2d4f84b | yoush97 | using (var transaction = base.BeginTransaction()) |
334 | 482f6326 | yoush97 | { |
335 | 709c1971 | yoush97 | string query = string.Empty; |
336 | |||
337 | if (delDocList.Count > 0) |
||
338 | 482f6326 | yoush97 | { |
339 | 709c1971 | yoush97 | string docIDList = string.Join("','", delDocList.Where(x => !string.IsNullOrEmpty(x.DocID)).Select(x => x.DocID).ToList()); |
340 | |||
341 | if (docIDList.Length > 0) |
||
342 | { |
||
343 | 53fde692 | yoush97 | query = $@" |
344 | 74aa670a | yoush97 | update dbo.Documents |
345 | set IsDeleted=1 |
||
346 | 54977253 | yoush97 | ,DeletedDate=getdate(), |
347 | ,DeletedUser=@DeletedUser |
||
348 | where DocID in ('{docIDList}');"; |
||
349 | base.Execute(query, new { DeletedUser = userId }, transaction); |
||
350 | 709c1971 | yoush97 | } |
351 | } |
||
352 | 482f6326 | yoush97 | |
353 | foreach (Documents doc in docList) |
||
354 | { |
||
355 | if (string.IsNullOrEmpty(doc.DocID)) |
||
356 | { |
||
357 | 54977253 | yoush97 | doc.RegisteredUser = userId; |
358 | 482f6326 | yoush97 | query = $@" |
359 | b1591ae6 | yoush97 | declare @tbdoc table(docid varchar(36)) |
360 | 482f6326 | yoush97 | insert into dbo.Documents |
361 | ( |
||
362 | 54977253 | yoush97 | DocID |
363 | ,DocumentNo |
||
364 | ,RevisonNo |
||
365 | 79d7836e | yoush97 | ,System |
366 | ,SubSystemCode |
||
367 | 54977253 | yoush97 | ,RefProjectCode |
368 | ,JobLevel |
||
369 | ,PersonInCharge |
||
370 | ,RegisteredDate |
||
371 | ,RegisteredUser |
||
372 | ,ToIsDiscussion |
||
373 | ,ToRemarks |
||
374 | ,ToCreator |
||
375 | ,FrReviewStatus |
||
376 | ,FrRemarks |
||
377 | ,FrCreator |
||
378 | ,ID2StartDate |
||
379 | ,ID2EndDate |
||
380 | ,ID2Status |
||
381 | ,ID2Issues |
||
382 | cb2e1138 | yoush97 | ,ReplyModifications |
383 | ,ReplyRequester |
||
384 | ,IsConvert |
||
385 | ,AVEVAPersonInCharge |
||
386 | ,AVEVAWorker |
||
387 | 54977253 | yoush97 | ,AVEVAConvertDate |
388 | ,AVEVAReviewDate |
||
389 | ,AVEVAStatus |
||
390 | ,AVEVAIssues |
||
391 | ,ProdReviewer |
||
392 | ,ProdIsResult |
||
393 | ,ProdRemarks |
||
394 | ,ClientReviewer |
||
395 | ,ClientIsResult |
||
396 | ,ClientRemarks |
||
397 | ,DTIsGateWay |
||
398 | ,DTIsImport |
||
399 | ,DTIsRegSystem |
||
400 | ,DTRemarks |
||
401 | 482f6326 | yoush97 | ) |
402 | b1591ae6 | yoush97 | output inserted.DocID into @tbdoc |
403 | 482f6326 | yoush97 | values |
404 | ( |
||
405 | lower(newid()) |
||
406 | ,@DocumentNo |
||
407 | ,@RevisonNo |
||
408 | 79d7836e | yoush97 | ,@System |
409 | ,@SubSystemCode |
||
410 | 36a31d25 | yoush97 | ,@RefProjectCode |
411 | 482f6326 | yoush97 | ,@JobLevel |
412 | ,@PersonInCharge |
||
413 | 54977253 | yoush97 | ,getdate() |
414 | ,@RegisteredUser |
||
415 | 482f6326 | yoush97 | ,@ToIsDiscussion |
416 | ,@ToRemarks |
||
417 | ,@ToCreator |
||
418 | ,@FrReviewStatus |
||
419 | ,@FrRemarks |
||
420 | ,@FrCreator |
||
421 | ,@ID2StartDate |
||
422 | ,@ID2EndDate |
||
423 | ,@ID2Status |
||
424 | ,@ID2Issues |
||
425 | cb2e1138 | yoush97 | ,@ReplyModifications |
426 | ,@ReplyRequester |
||
427 | ,@IsConvert |
||
428 | ,@AVEVAPersonInCharge |
||
429 | ,@AVEVAWorker |
||
430 | 482f6326 | yoush97 | ,@AVEVAConvertDate |
431 | ,@AVEVAReviewDate |
||
432 | ,@AVEVAStatus |
||
433 | ,@AVEVAIssues |
||
434 | ,@ProdReviewer |
||
435 | ,@ProdIsResult |
||
436 | ,@ProdRemarks |
||
437 | ,@ClientReviewer |
||
438 | ,@ClientIsResult |
||
439 | ,@ClientRemarks |
||
440 | ,@DTIsGateWay |
||
441 | ,@DTIsImport |
||
442 | ,@DTIsRegSystem |
||
443 | ,@DTRemarks |
||
444 | b1591ae6 | yoush97 | ) |
445 | |||
446 | if @@rowcount > 0 |
||
447 | begin |
||
448 | select docid from @tbdoc |
||
449 | end |
||
450 | else |
||
451 | begin |
||
452 | select '' |
||
453 | end;"; |
||
454 | 482f6326 | yoush97 | } |
455 | else |
||
456 | { |
||
457 | 54977253 | yoush97 | doc.ModifiedUser = userId; |
458 | 482f6326 | yoush97 | query = $@" |
459 | update dbo.Documents |
||
460 | 54977253 | yoush97 | set DocumentNo=@DocumentNo |
461 | ,RevisonNo=@RevisonNo |
||
462 | 79d7836e | yoush97 | ,System=@System |
463 | ,SubSystemCode=@SubSystemCode |
||
464 | 54977253 | yoush97 | ,RefProjectCode=@RefProjectCode |
465 | ,JobLevel=@JobLevel |
||
466 | ,PersonInCharge=@PersonInCharge |
||
467 | ,ModifiedDate=getdate() |
||
468 | ,ModifiedUser=@ModifiedUser |
||
469 | ,ToIsDiscussion=@ToIsDiscussion |
||
470 | ,ToRemarks=@ToRemarks |
||
471 | ,ToCreator=@ToCreator |
||
472 | ,FrReviewStatus=@FrReviewStatus |
||
473 | ,FrRemarks=@FrRemarks |
||
474 | ,FrCreator=@FrCreator |
||
475 | ,ID2StartDate=@ID2StartDate |
||
476 | ,ID2EndDate=@ID2EndDate |
||
477 | ,ID2Status=@ID2Status |
||
478 | ,ID2Issues=@ID2Issues |
||
479 | cb2e1138 | yoush97 | ,ReplyModifications=@ReplyModifications |
480 | ,ReplyRequester=@ReplyRequester |
||
481 | ,IsConvert=@IsConvert |
||
482 | ,AVEVAPersonInCharge=@AVEVAPersonInCharge |
||
483 | ,AVEVAWorker=@AVEVAWorker |
||
484 | 54977253 | yoush97 | ,AVEVAConvertDate=@AVEVAConvertDate |
485 | ,AVEVAReviewDate=@AVEVAReviewDate |
||
486 | ,AVEVAStatus=@AVEVAStatus |
||
487 | ,AVEVAIssues=@AVEVAIssues |
||
488 | ,ProdReviewer=@ProdReviewer |
||
489 | ,ProdIsResult=@ProdIsResult |
||
490 | ,ProdRemarks=@ProdRemarks |
||
491 | ,ClientReviewer=@ClientReviewer |
||
492 | ,ClientIsResult=@ClientIsResult |
||
493 | ,ClientRemarks=@ClientRemarks |
||
494 | ,DTIsGateWay=@DTIsGateWay |
||
495 | ,DTIsImport=@DTIsImport |
||
496 | ,DTIsRegSystem=@DTIsRegSystem |
||
497 | ,DTRemarks=@DTRemarks |
||
498 | b1591ae6 | yoush97 | where DocID=@DocID |
499 | |||
500 | if @@rowcount > 0 |
||
501 | begin |
||
502 | select @DocID |
||
503 | end |
||
504 | else |
||
505 | begin |
||
506 | select '' |
||
507 | end;"; |
||
508 | } |
||
509 | string refID = base.QueryFirstOrDefault<string>(query, doc, transaction); |
||
510 | |||
511 | if (doc.AttFiles != null && doc.AttFiles.Count > 0) |
||
512 | { |
||
513 | string attDelIDList = string.Join("','", doc.AttFiles.Where(x => !string.IsNullOrEmpty(x.FileID)).Select(x => x.FileID).ToList()); |
||
514 | |||
515 | if (!string.IsNullOrEmpty(refID) && attDelIDList.Length > 0) |
||
516 | { |
||
517 | query = $@" |
||
518 | delete from dbo.AttachFIles |
||
519 | where RefID=@RefID and FileID in ('{attDelIDList}');"; |
||
520 | base.Execute(query, new { RefID = refID }, transaction); |
||
521 | } |
||
522 | |||
523 | foreach (AttFileInfo attFile in doc.AttFiles) |
||
524 | { |
||
525 | e5ade387 | yoush97 | if (string.IsNullOrEmpty(attFile.RefID)) |
526 | { |
||
527 | attFile.RefID = refID; |
||
528 | attFile.Creator = userId; |
||
529 | b1591ae6 | yoush97 | |
530 | e5ade387 | yoush97 | query = $@" |
531 | b1591ae6 | yoush97 | insert into dbo.AttachFIles (FileID,RefID,Category,FileType,FileName,FilePath,FileExtension,FileData,Creator) |
532 | values |
||
533 | ( |
||
534 | lower(newid()) |
||
535 | ,@RefID |
||
536 | ,@Category |
||
537 | ,@FileType |
||
538 | ,@FileName |
||
539 | ,@FilePath |
||
540 | ,@FileExtension |
||
541 | ,@FileData |
||
542 | ,@Creator |
||
543 | )"; |
||
544 | |||
545 | e5ade387 | yoush97 | base.Execute(query, attFile, transaction); |
546 | } |
||
547 | b1591ae6 | yoush97 | } |
548 | 482f6326 | yoush97 | } |
549 | } |
||
550 | |||
551 | transaction.Commit(); |
||
552 | isSuccess = true; |
||
553 | } |
||
554 | } |
||
555 | catch (Exception ex) |
||
556 | { |
||
557 | throw ex; |
||
558 | } |
||
559 | |||
560 | return isSuccess; |
||
561 | } |
||
562 | 978488b0 | yoush97 | |
563 | public Documents SetDocumentDataField(Documents doc, string userId) |
||
564 | { |
||
565 | Documents resultData = null; |
||
566 | |||
567 | try |
||
568 | { |
||
569 | using (var transaction = base.BeginTransaction()) |
||
570 | { |
||
571 | string query = string.Empty; |
||
572 | |||
573 | if (!string.IsNullOrEmpty(doc.DocID)) |
||
574 | { |
||
575 | StringBuilder sbSet = new StringBuilder(); |
||
576 | var parameters = new Dictionary<string, object>(); |
||
577 | |||
578 | #region Update 할 목록 |
||
579 | if (doc.ID2StartDate != null) |
||
580 | { |
||
581 | 4b8d9ad9 | yoush97 | sbSet.Append(" ,ID2StartDate=(case when ID2StartDate is null then @DateTimeNow else ID2StartDate end) "); |
582 | 978488b0 | yoush97 | parameters.Add("ID2StartDate", doc.ID2StartDate); |
583 | } |
||
584 | 4b8d9ad9 | yoush97 | |
585 | if (doc.Worker != null) |
||
586 | { |
||
587 | sbSet.Append(" ,Worker=@Worker "); |
||
588 | parameters.Add("Worker", doc.Worker); |
||
589 | } |
||
590 | 978488b0 | yoush97 | #endregion |
591 | |||
592 | if (parameters.Count > 0) |
||
593 | { |
||
594 | sbSet.Append(" ,ModifiedUser=@ModifiedUser "); |
||
595 | parameters.Add("ModifiedUser", userId); |
||
596 | |||
597 | 54977253 | yoush97 | parameters.Add("DocID", doc.DocID); |
598 | |||
599 | 978488b0 | yoush97 | query = $@" |
600 | declare @DateTimeNow datetime |
||
601 | set @DateTimeNow = getdate() |
||
602 | |||
603 | update dbo.Documents |
||
604 | set ModifiedDate=@DateTimeNow {sbSet} |
||
605 | where [DocID]=@DocID |
||
606 | |||
607 | if @@rowcount > 0 |
||
608 | begin |
||
609 | fe310cac | yoush97 | select * from dbo.Documents where DocID=@DocID |
610 | 978488b0 | yoush97 | end |
611 | else |
||
612 | begin |
||
613 | fe310cac | yoush97 | select * from dbo.Documents where 1=2 |
614 | 978488b0 | yoush97 | end;"; |
615 | resultData = base.QueryFirstOrDefault<Documents>(query, parameters, transaction); |
||
616 | } |
||
617 | } |
||
618 | |||
619 | transaction.Commit(); |
||
620 | } |
||
621 | } |
||
622 | catch (Exception ex) |
||
623 | { |
||
624 | throw ex; |
||
625 | } |
||
626 | |||
627 | return resultData; |
||
628 | } |
||
629 | fe57f64a | yoush97 | |
630 | 54977253 | yoush97 | public bool SetDocumentDatasField(List<Documents> docs, string userId) |
631 | { |
||
632 | bool isSuccess = false; |
||
633 | |||
634 | try |
||
635 | { |
||
636 | using (var transaction = base.BeginTransaction()) |
||
637 | { |
||
638 | foreach (Documents doc in docs) |
||
639 | { |
||
640 | string query = string.Empty; |
||
641 | |||
642 | if (!string.IsNullOrEmpty(doc.DocID)) |
||
643 | { |
||
644 | StringBuilder sbSet = new StringBuilder(); |
||
645 | var parameters = new Dictionary<string, object>(); |
||
646 | |||
647 | #region Update 할 목록 |
||
648 | if (doc.ID2EndDate != null) |
||
649 | { |
||
650 | sbSet.Append(" ,ID2EndDate=@ID2EndDate "); |
||
651 | parameters.Add("ID2EndDate", doc.ID2EndDate); |
||
652 | } |
||
653 | #endregion |
||
654 | |||
655 | if (parameters.Count > 0) |
||
656 | { |
||
657 | sbSet.Append(" ,ModifiedUser=@ModifiedUser "); |
||
658 | parameters.Add("ModifiedUser", userId); |
||
659 | |||
660 | parameters.Add("DocID", doc.DocID); |
||
661 | |||
662 | query = $@" |
||
663 | update dbo.Documents |
||
664 | set ModifiedDate=getdate() {sbSet} |
||
665 | where [DocID]=@DocID;"; |
||
666 | e458a996 | taeseongkim | base.Execute(query, parameters, transaction); |
667 | 54977253 | yoush97 | } |
668 | } |
||
669 | } |
||
670 | transaction.Commit(); |
||
671 | isSuccess = true; |
||
672 | } |
||
673 | } |
||
674 | catch (Exception ex) |
||
675 | { |
||
676 | throw ex; |
||
677 | } |
||
678 | |||
679 | return isSuccess; |
||
680 | } |
||
681 | |||
682 | fe57f64a | yoush97 | |
683 | //ID2 |
||
684 | public IEnumerable<ID2Drawings> GetID2DrawingsByProject(ID2ProjectInfo id2Info) |
||
685 | { |
||
686 | 82705273 | yoush97 | try |
687 | { |
||
688 | string query = $@" |
||
689 | fe57f64a | yoush97 | select @Name PROJECTNAME |
690 | ,dw.[UID], dw.[NAME], left(dw.[NAME], len(dw.[NAME]) - dw.lastidx) DOCNAME |
||
691 | ,case when rtrim(ltrim(isnull(convert(varchar, dw.[DATETIME]),''))) = '' then null else convert(datetime, convert(varchar, dw.[DATETIME])) end [DATETIME] |
||
692 | ,dw.OCCUPIED, dw.[Image] |
||
693 | from |
||
694 | ( |
||
695 | select [UID], [NAME], [DATETIME], OCCUPIED, [Image], charindex('.', reverse([NAME])) lastidx |
||
696 | from dbo.Drawings |
||
697 | ) dw;"; |
||
698 | 82705273 | yoush97 | return Query<ID2Drawings>(query, id2Info); |
699 | } |
||
700 | catch (Exception ex) |
||
701 | { |
||
702 | throw ex; |
||
703 | } |
||
704 | fe57f64a | yoush97 | } |
705 | 5898479a | yoush97 | } |
706 | d2d4f84b | yoush97 | } |