hytos / ID2.Manager / ID2.Manager.Dapper / Repository / DocumentRepository.cs @ 39938acd
이력 | 보기 | 이력해설 | 다운로드 (39.4 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 | 39938acd | yoush97 | using System.ComponentModel; |
9 | using System.Reflection; |
||
10 | 54977253 | yoush97 | |
11 | 5898479a | yoush97 | using ID2.Manager.Data.Models; |
12 | |||
13 | 8e373ccf | yoush97 | using Dapper; |
14 | 39938acd | yoush97 | |
15 | using Newtonsoft.Json; |
||
16 | 8e373ccf | yoush97 | |
17 | 5898479a | yoush97 | namespace ID2.Manager.Dapper.Repository |
18 | { |
||
19 | public class DocumentRepository : BaseRepository |
||
20 | { |
||
21 | public DocumentRepository(string connectionStr) : base(connectionStr) { } |
||
22 | |||
23 | a4a166e2 | yoush97 | public IEnumerable<Documents> GetAllDocuments(string projectGroupID) |
24 | 5898479a | yoush97 | { |
25 | a4a166e2 | yoush97 | var dynamicParameters = new DynamicParameters(); |
26 | StringBuilder sbWhere = new StringBuilder(); |
||
27 | var parameters = new Dictionary<string, object>(); |
||
28 | if (!string.IsNullOrEmpty(projectGroupID)) |
||
29 | { |
||
30 | sbWhere.Append(" and doc.RefProjectCode in (select Code from dbo.Projects where ParentID=@RefGroupID) "); |
||
31 | parameters.Add("RefGroupID", projectGroupID); |
||
32 | } |
||
33 | |||
34 | 82705273 | yoush97 | try |
35 | { |
||
36 | string query = $@" |
||
37 | 54977253 | yoush97 | select doc.* |
38 | 7066b8a9 | yoush97 | from dbo.Documents doc |
39 | a4a166e2 | yoush97 | where doc.IsDeleted=0 {sbWhere} |
40 | 7066b8a9 | yoush97 | order by doc.Seq;"; |
41 | a4a166e2 | yoush97 | |
42 | if (parameters.Count > 0) |
||
43 | { |
||
44 | dynamicParameters.AddDynamicParams(parameters); |
||
45 | } |
||
46 | |||
47 | return Query<Documents>(query, dynamicParameters); |
||
48 | 82705273 | yoush97 | } |
49 | catch (Exception ex) |
||
50 | { |
||
51 | throw ex; |
||
52 | } |
||
53 | 7066b8a9 | yoush97 | } |
54 | 08499f5f | taeseongkim | static string GetDescriptionFromAttribute(MemberInfo member) |
55 | { |
||
56 | if (member == null) return null; |
||
57 | |||
58 | var attrib = (DescriptionAttribute)Attribute.GetCustomAttribute(member, typeof(DescriptionAttribute), false); |
||
59 | return (attrib?.Description ?? member.Name).ToLower(); |
||
60 | } |
||
61 | |||
62 | 7066b8a9 | yoush97 | |
63 | 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) |
64 | 7066b8a9 | yoush97 | { |
65 | 08499f5f | taeseongkim | var map = new CustomPropertyTypeMap(typeof(AttFileInfo), (type, columnName) |
66 | => type.GetProperties().FirstOrDefault(prop => GetDescriptionFromAttribute(prop) == columnName.ToLower())); |
||
67 | SqlMapper.SetTypeMap(typeof(AttFileInfo), map); |
||
68 | |||
69 | |||
70 | 7066b8a9 | yoush97 | var dynamicParameters = new DynamicParameters(); |
71 | 54977253 | yoush97 | dynamicParameters.Add("Total", dbType: DbType.Int32, direction: ParameterDirection.Output); |
72 | 7066b8a9 | yoush97 | |
73 | 8e373ccf | yoush97 | StringBuilder sbWhere = new StringBuilder(); |
74 | bf7d1c08 | yoush97 | StringBuilder sbTotalWhere = new StringBuilder(); |
75 | 8e373ccf | yoush97 | var parameters = new Dictionary<string, object>(); |
76 | b2ff5ff4 | yoush97 | if (dateTypes.Count > 0 && (frDate != null || toDate != null)) |
77 | { |
||
78 | List<string> subWheres = new List<string>(); |
||
79 | foreach (string dateType in dateTypes) |
||
80 | { |
||
81 | StringBuilder sbSubWhere = new StringBuilder(); |
||
82 | if (frDate != null) |
||
83 | { |
||
84 | sbSubWhere.Insert(0, $@"convert(datetime, '{Convert.ToDateTime(frDate).AddDays(-1):yyyy-MM-dd 23:59:59.997}') < doc.{dateType}"); |
||
85 | } |
||
86 | if (toDate != null) |
||
87 | { |
||
88 | if (frDate != null) |
||
89 | sbSubWhere.Append($@" and "); |
||
90 | sbSubWhere.Append($@"doc.{dateType} < convert(datetime, '{Convert.ToDateTime(toDate).AddDays(1):yyyy-MM-dd 00:00:00.000}')"); |
||
91 | } |
||
92 | |||
93 | if (sbSubWhere.Length > 0) |
||
94 | { |
||
95 | subWheres.Add("(" + sbSubWhere.ToString() + ")"); |
||
96 | } |
||
97 | } |
||
98 | |||
99 | if (subWheres.Count > 0) |
||
100 | { |
||
101 | sbWhere.Append(" and (" + string.Join(" or ", subWheres) + ") "); |
||
102 | } |
||
103 | } |
||
104 | ea97eee6 | yoush97 | if (string.IsNullOrEmpty(projectCode)) |
105 | { |
||
106 | sbWhere.Append(" and isnull(doc.RefProjectCode,'') in (select Code from dbo.Projects where ParentID=@RefGroupID union all select '') "); |
||
107 | e8a86b9b | yoush97 | sbTotalWhere.Append(" and isnull(doc.RefProjectCode,'') in (select Code from dbo.Projects where ParentID=@RefGroupID union all select '') "); |
108 | ea97eee6 | yoush97 | parameters.Add("RefGroupID", projectGroupID); |
109 | } |
||
110 | else |
||
111 | 8e373ccf | yoush97 | { |
112 | e8a86b9b | yoush97 | sbTotalWhere.Append(" and isnull(doc.RefProjectCode,'') in (select Code from dbo.Projects where ParentID=@RefGroupID union all select '') "); |
113 | parameters.Add("RefGroupID", projectGroupID); |
||
114 | |||
115 | 36a31d25 | yoush97 | sbWhere.Append(" and doc.RefProjectCode=@RefProjectCode "); |
116 | parameters.Add("RefProjectCode", projectCode); |
||
117 | 8e373ccf | yoush97 | } |
118 | if (!string.IsNullOrEmpty(personIncharge)) |
||
119 | { |
||
120 | sbWhere.Append(" and doc.PersonInCharge=@PersonInCharge "); |
||
121 | parameters.Add("PersonInCharge", personIncharge); |
||
122 | } |
||
123 | 08ea0584 | yoush97 | if (!string.IsNullOrEmpty(jobLevel)) |
124 | { |
||
125 | sbWhere.Append(" and doc.JobLevel=@JobLevel "); |
||
126 | parameters.Add("JobLevel", jobLevel); |
||
127 | } |
||
128 | 8e373ccf | yoush97 | if (!string.IsNullOrEmpty(documentNo)) |
129 | { |
||
130 | sbWhere.Append(" and doc.DocumentNo like '%' + @DocumentNo +'%' "); |
||
131 | parameters.Add("DocumentNo", documentNo); |
||
132 | } |
||
133 | |||
134 | 296ffcbd | yoush97 | if (!string.IsNullOrEmpty(isToIsDiscussion)) |
135 | { |
||
136 | sbWhere.Append(" and doc.ToIsDiscussion=@ToIsDiscussion "); |
||
137 | parameters.Add("ToIsDiscussion", isToIsDiscussion); |
||
138 | } |
||
139 | |||
140 | if (!string.IsNullOrEmpty(isFrReviewStatus)) |
||
141 | { |
||
142 | sbWhere.Append(" and doc.FrReviewStatus=@FrReviewStatus "); |
||
143 | parameters.Add("FrReviewStatus", isFrReviewStatus); |
||
144 | } |
||
145 | |||
146 | 08ea0584 | yoush97 | if (!string.IsNullOrEmpty(id2Status)) |
147 | { |
||
148 | sbWhere.Append(" and doc.ID2Status=@ID2Status "); |
||
149 | parameters.Add("ID2Status", id2Status); |
||
150 | } |
||
151 | 3cc84cb6 | yoush97 | |
152 | if (!string.IsNullOrEmpty(id2Issues)) |
||
153 | { |
||
154 | sbWhere.Append(" and (case when isnull(ltrim(rtrim(doc.ID2Issues)),'') = '' then 'No' else 'Yes' end)=@ID2Issues "); |
||
155 | parameters.Add("ID2Issues", id2Issues); |
||
156 | } |
||
157 | |||
158 | 08ea0584 | yoush97 | if (!string.IsNullOrEmpty(avevaStatus)) |
159 | { |
||
160 | sbWhere.Append(" and doc.AVEVAStatus=@AVEVAStatus "); |
||
161 | parameters.Add("AVEVAStatus", avevaStatus); |
||
162 | } |
||
163 | |||
164 | 3cc84cb6 | yoush97 | if (!string.IsNullOrEmpty(avevaIssues)) |
165 | { |
||
166 | sbWhere.Append(" and (case when isnull(ltrim(rtrim(doc.AVEVAIssues)),'') = '' then 'No' else 'Yes' end)=@AVEVAIssues "); |
||
167 | parameters.Add("AVEVAIssues", avevaIssues); |
||
168 | } |
||
169 | |||
170 | 08ea0584 | yoush97 | if (!string.IsNullOrEmpty(prodIsResult)) |
171 | { |
||
172 | sbWhere.Append(" and doc.ProdIsResult=@ProdIsResult "); |
||
173 | parameters.Add("ProdIsResult", prodIsResult); |
||
174 | } |
||
175 | if (!string.IsNullOrEmpty(clientIsResult)) |
||
176 | { |
||
177 | sbWhere.Append(" and doc.ClientIsResult=@ClientIsResult "); |
||
178 | parameters.Add("ClientIsResult", clientIsResult); |
||
179 | } |
||
180 | ee82162b | yoush97 | if (!string.IsNullOrEmpty(isGateWay)) |
181 | { |
||
182 | sbWhere.Append(" and doc.DTIsGateWay=@DTIsGateWay "); |
||
183 | parameters.Add("DTIsGateWay", isGateWay); |
||
184 | } |
||
185 | if (!string.IsNullOrEmpty(isRegSystem)) |
||
186 | { |
||
187 | sbWhere.Append(" and doc.DTIsRegSystem=@DTIsRegSystem "); |
||
188 | parameters.Add("DTIsRegSystem", isRegSystem); |
||
189 | } |
||
190 | 08ea0584 | yoush97 | |
191 | 82705273 | yoush97 | try |
192 | { |
||
193 | 08499f5f | taeseongkim | |
194 | //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, |
||
195 | fe310cac | yoush97 | string query = $@" |
196 | select doc.*, |
||
197 | files.[FileID] as FileID, files.*, |
||
198 | markus.PROJECT_NO as PROJECT_NO, markus.* |
||
199 | from dbo.Documents doc |
||
200 | LEFT OUTER JOIN AttachFIles files ON doc.DocID = fIles.RefID |
||
201 | LEFT OUTER JOIN |
||
202 | (SELECT [PROJECT_NO] as PROJECT_NO |
||
203 | ,[DOCUMENT_ID] as DocumentNo |
||
204 | 08499f5f | taeseongkim | |
205 | fe310cac | yoush97 | ,[PAGENUMBER] |
206 | ,[Text] as TEXT |
||
207 | ,[CREATE_DATE] as CREATE_DATE |
||
208 | ,[NAME] as CREATE_USER |
||
209 | FROM ViewMarkupData) markus |
||
210 | ON doc.DocumentNo = markus.DocumentNo |
||
211 | where doc.IsDeleted=0 {sbWhere} |
||
212 | order by doc.Seq |
||
213 | 7066b8a9 | yoush97 | |
214 | 3ccb1a8d | yoush97 | select @Total=count(*) from dbo.Documents doc where doc.IsDeleted=0 {sbTotalWhere} |
215 | 7066b8a9 | yoush97 | select @Total;"; |
216 | 8e373ccf | yoush97 | |
217 | 82705273 | yoush97 | if (parameters.Count > 0) |
218 | { |
||
219 | dynamicParameters.AddDynamicParams(parameters); |
||
220 | } |
||
221 | 5898479a | yoush97 | |
222 | e458a996 | taeseongkim | var docDictionary = new Dictionary<string, Documents>(); |
223 | |||
224 | 08499f5f | taeseongkim | var ret = MultiQuery<Documents, AttFileInfo, MarkupText, Documents>(query, |
225 | (document, attfile, markusText) => |
||
226 | e458a996 | taeseongkim | { |
227 | Documents doc; |
||
228 | |||
229 | 08499f5f | taeseongkim | if (!docDictionary.TryGetValue(document.DocID, out doc)) |
230 | e458a996 | taeseongkim | { |
231 | doc = document; |
||
232 | 08499f5f | taeseongkim | docDictionary.Add(doc.DocID, doc); |
233 | } |
||
234 | |||
235 | if (markusText != null) |
||
236 | { |
||
237 | e458a996 | taeseongkim | doc.Markups = doc.Markups ?? new List<MarkupText>(); |
238 | 08499f5f | taeseongkim | |
239 | a37dc9a7 | taeseongkim | if (!doc.Markups.Any(x => x.Equals(markusText))) |
240 | 08499f5f | taeseongkim | { |
241 | doc.Markups.Add(markusText); |
||
242 | } |
||
243 | } |
||
244 | |||
245 | if (attfile != null) |
||
246 | { |
||
247 | doc.AttFiles = doc.AttFiles ?? new List<AttFileInfo>(); |
||
248 | System.Diagnostics.Debug.WriteLine(attfile.FileName); |
||
249 | if (!doc.AttFiles.Any(x => x.FileID == attfile.FileID)) |
||
250 | { |
||
251 | e8a86b9b | yoush97 | switch (attfile.Category) |
252 | { |
||
253 | case "toreview": |
||
254 | doc.ToCapture++; |
||
255 | break; |
||
256 | case "frreview": |
||
257 | doc.FrCapture++; |
||
258 | break; |
||
259 | f4230b40 | yoush97 | //case "prodvalidation": |
260 | // doc.ProdCapture++; |
||
261 | // break; |
||
262 | //case "clientvalidation": |
||
263 | // doc.ClientCapture++; |
||
264 | // break; |
||
265 | case "id2work": |
||
266 | doc.ID2Capture++; |
||
267 | e8a86b9b | yoush97 | break; |
268 | } |
||
269 | |||
270 | 08499f5f | taeseongkim | doc.AttFiles.Add(attfile); |
271 | } |
||
272 | e458a996 | taeseongkim | } |
273 | |||
274 | return doc; |
||
275 | |||
276 | a37dc9a7 | taeseongkim | }, dynamicParameters, splitOn: "DocID,FileID,PROJECT_NO").Distinct(); |
277 | 7066b8a9 | yoush97 | |
278 | 82705273 | yoush97 | int totalCount = dynamicParameters.Get<int>("Total"); |
279 | 7066b8a9 | yoush97 | |
280 | 82705273 | yoush97 | return (ret, totalCount); |
281 | } |
||
282 | catch (Exception ex) |
||
283 | { |
||
284 | throw ex; |
||
285 | } |
||
286 | 5898479a | yoush97 | } |
287 | 482f6326 | yoush97 | |
288 | ff990784 | yoush97 | public int ExistsDocument(string projectGroupID, List<string> newDwgNos) |
289 | { |
||
290 | 9107c43b | yoush97 | int existCount = 0; |
291 | ff990784 | yoush97 | |
292 | 9107c43b | yoush97 | int paramMaxCount = 2000; |
293 | int execCount = (newDwgNos.Count / paramMaxCount) + 1; |
||
294 | ff990784 | yoush97 | |
295 | 9107c43b | yoush97 | for (int i = 0; i < execCount; i++) |
296 | { |
||
297 | var dynamicParameters = new DynamicParameters(); |
||
298 | StringBuilder sbWhere = new StringBuilder(); |
||
299 | var parameters = new Dictionary<string, object>(); |
||
300 | if (!string.IsNullOrEmpty(projectGroupID)) |
||
301 | ff990784 | yoush97 | { |
302 | 9107c43b | yoush97 | sbWhere.Append(" and doc.RefProjectCode in (select Code from dbo.Projects where ParentID=@RefGroupID) "); |
303 | parameters.Add("RefGroupID", projectGroupID); |
||
304 | ff990784 | yoush97 | } |
305 | |||
306 | 9107c43b | yoush97 | var limitDwgNos = newDwgNos.Skip(paramMaxCount * i).Take(paramMaxCount).ToList(); |
307 | sbWhere.Append($@" and doc.DocumentNo in @limitDwgNos "); |
||
308 | parameters.Add("limitDwgNos", limitDwgNos); |
||
309 | |||
310 | try |
||
311 | { |
||
312 | string query = $@" |
||
313 | ff990784 | yoush97 | select count(*) cnt |
314 | from dbo.Documents doc |
||
315 | where doc.IsDeleted=0 {sbWhere}"; |
||
316 | |||
317 | 9107c43b | yoush97 | if (parameters.Count > 0) |
318 | { |
||
319 | dynamicParameters.AddDynamicParams(parameters); |
||
320 | } |
||
321 | |||
322 | existCount += ExecuteScalar<int>(query, dynamicParameters); |
||
323 | } |
||
324 | catch (Exception ex) |
||
325 | ff990784 | yoush97 | { |
326 | 9107c43b | yoush97 | throw ex; |
327 | ff990784 | yoush97 | } |
328 | } |
||
329 | 9107c43b | yoush97 | |
330 | return existCount; |
||
331 | ff990784 | yoush97 | } |
332 | |||
333 | 993feace | yoush97 | public bool SetDocumentData(string projectGroupID, List<Documents> docList, List<Documents> delDocList, string userId) |
334 | 482f6326 | yoush97 | { |
335 | bool isSuccess = false; |
||
336 | |||
337 | try |
||
338 | { |
||
339 | 993feace | yoush97 | |
340 | #region merge 주석처리 |
||
341 | /* |
||
342 | { |
||
343 | docList.ForEach(x => |
||
344 | { |
||
345 | x.RegisteredUser = userId; |
||
346 | x.ModifiedUser = userId; |
||
347 | }); |
||
348 | string query = $@" |
||
349 | merge into dbo.Documents as tg |
||
350 | using |
||
351 | ( |
||
352 | values |
||
353 | ( |
||
354 | @DocumentNo |
||
355 | ,@RevisonNo |
||
356 | ,@System |
||
357 | ,@SubSystemCode |
||
358 | ,@RefProjectCode |
||
359 | ,@JobLevel |
||
360 | ,@PersonInCharge |
||
361 | ,@RegisteredUser |
||
362 | ,@ModifiedUser |
||
363 | ,@ToIsDiscussion |
||
364 | ,@ToRemarks |
||
365 | ,@ToCreator |
||
366 | ,@FrReviewStatus |
||
367 | ,@FrRemarks |
||
368 | ,@FrCreator |
||
369 | ,@ID2StartDate |
||
370 | ,@ID2EndDate |
||
371 | ,@ID2Status |
||
372 | ,@ID2Issues |
||
373 | ,@ReplyModifications |
||
374 | ,@ReplyRequester |
||
375 | ,@IsConvert |
||
376 | ,@AVEVAPersonInCharge |
||
377 | ,@AVEVAWorker |
||
378 | ,@AVEVAConvertDate |
||
379 | ,@AVEVAReviewDate |
||
380 | ,@AVEVAWorkDate |
||
381 | ,@AVEVAStatus |
||
382 | ,@AVEVAIssues |
||
383 | ,@ProdReviewer |
||
384 | ,@ProdIsResult |
||
385 | ,@ProdRemarks |
||
386 | ,@ClientReviewer |
||
387 | ,@ClientIsResult |
||
388 | ,@ClientRemarks |
||
389 | ,@DTIsGateWay |
||
390 | ,@DTIsImport |
||
391 | ,@DTIsRegSystem |
||
392 | ,@DTRemarks |
||
393 | ) |
||
394 | ) as sc |
||
395 | ( |
||
396 | DocumentNo |
||
397 | ,RevisonNo |
||
398 | ,[System] |
||
399 | ,SubSystemCode |
||
400 | ,RefProjectCode |
||
401 | ,JobLevel |
||
402 | ,PersonInCharge |
||
403 | ,RegisteredUser |
||
404 | ,ModifiedUser |
||
405 | ,ToIsDiscussion |
||
406 | ,ToRemarks |
||
407 | ,ToCreator |
||
408 | ,FrReviewStatus |
||
409 | ,FrRemarks |
||
410 | ,FrCreator |
||
411 | ,ID2StartDate |
||
412 | ,ID2EndDate |
||
413 | ,ID2Status |
||
414 | ,ID2Issues |
||
415 | ,ReplyModifications |
||
416 | ,ReplyRequester |
||
417 | ,IsConvert |
||
418 | ,AVEVAPersonInCharge |
||
419 | ,AVEVAWorker |
||
420 | ,AVEVAConvertDate |
||
421 | ,AVEVAReviewDate |
||
422 | ,AVEVAWorkDate |
||
423 | ,AVEVAStatus |
||
424 | ,AVEVAIssues |
||
425 | ,ProdReviewer |
||
426 | ,ProdIsResult |
||
427 | ,ProdRemarks |
||
428 | ,ClientReviewer |
||
429 | ,ClientIsResult |
||
430 | ,ClientRemarks |
||
431 | ,DTIsGateWay |
||
432 | ,DTIsImport |
||
433 | ,DTIsRegSystem |
||
434 | ,DTRemarks |
||
435 | ) |
||
436 | ON sc.DocumentNo=tg.DocumentNo |
||
437 | when matched and tg.IsDeleted=0 then |
||
438 | |||
439 | update |
||
440 | set tg.DocumentNo=sc.DocumentNo |
||
441 | ,tg.RevisonNo=sc.RevisonNo |
||
442 | ,tg.[System]=sc.[System] |
||
443 | ,tg.SubSystemCode=sc.SubSystemCode |
||
444 | ,tg.RefProjectCode=sc.RefProjectCode |
||
445 | ,tg.JobLevel=sc.JobLevel |
||
446 | ,tg.PersonInCharge=sc.PersonInCharge |
||
447 | ,tg.ModifiedDate=getdate() |
||
448 | ,tg.ModifiedUser=sc.ModifiedUser |
||
449 | ,tg.ToIsDiscussion=sc.ToIsDiscussion |
||
450 | ,tg.ToRemarks=sc.ToRemarks |
||
451 | ,tg.ToCreator=sc.ToCreator |
||
452 | ,tg.FrReviewStatus=sc.FrReviewStatus |
||
453 | ,tg.FrRemarks=sc.FrRemarks |
||
454 | ,tg.FrCreator=sc.FrCreator |
||
455 | ,tg.ID2StartDate=sc.ID2StartDate |
||
456 | ,tg.ID2EndDate=sc.ID2EndDate |
||
457 | ,tg.ID2Status=sc.ID2Status |
||
458 | ,tg.ID2Issues=sc.ID2Issues |
||
459 | ,tg.ReplyModifications=sc.ReplyModifications |
||
460 | ,tg.ReplyRequester=sc.ReplyRequester |
||
461 | ,tg.IsConvert=sc.IsConvert |
||
462 | ,tg.AVEVAPersonInCharge=sc.AVEVAPersonInCharge |
||
463 | ,tg.AVEVAWorker=sc.AVEVAWorker |
||
464 | ,tg.AVEVAConvertDate=sc.AVEVAConvertDate |
||
465 | ,tg.AVEVAReviewDate=sc.AVEVAReviewDate |
||
466 | ,tg.AVEVAWorkDate=sc.AVEVAWorkDate |
||
467 | ,tg.AVEVAStatus=sc.AVEVAStatus |
||
468 | ,tg.AVEVAIssues=sc.AVEVAIssues |
||
469 | ,tg.ProdReviewer=sc.ProdReviewer |
||
470 | ,tg.ProdIsResult=sc.ProdIsResult |
||
471 | ,tg.ProdRemarks=sc.ProdRemarks |
||
472 | ,tg.ClientReviewer=sc.ClientReviewer |
||
473 | ,tg.ClientIsResult=sc.ClientIsResult |
||
474 | ,tg.ClientRemarks=sc.ClientRemarks |
||
475 | ,tg.DTIsGateWay=sc.DTIsGateWay |
||
476 | ,tg.DTIsImport=sc.DTIsImport |
||
477 | ,tg.DTIsRegSystem=sc.DTIsRegSystem |
||
478 | ,tg.DTRemarks=sc.DTRemarks |
||
479 | when not matched then |
||
480 | |||
481 | |||
482 | insert |
||
483 | ( |
||
484 | DocID |
||
485 | ,DocumentNo |
||
486 | ,RevisonNo |
||
487 | ,[System] |
||
488 | ,SubSystemCode |
||
489 | ,RefProjectCode |
||
490 | ,JobLevel |
||
491 | ,PersonInCharge |
||
492 | ,RegisteredDate |
||
493 | ,RegisteredUser |
||
494 | ,ToIsDiscussion |
||
495 | ,ToRemarks |
||
496 | ,ToCreator |
||
497 | ,FrReviewStatus |
||
498 | ,FrRemarks |
||
499 | ,FrCreator |
||
500 | ,ID2StartDate |
||
501 | ,ID2EndDate |
||
502 | ,ID2Status |
||
503 | ,ID2Issues |
||
504 | ,ReplyModifications |
||
505 | ,ReplyRequester |
||
506 | ,IsConvert |
||
507 | ,AVEVAPersonInCharge |
||
508 | ,AVEVAWorker |
||
509 | ,AVEVAConvertDate |
||
510 | ,AVEVAReviewDate |
||
511 | ,AVEVAWorkDate |
||
512 | ,AVEVAStatus |
||
513 | ,AVEVAIssues |
||
514 | ,ProdReviewer |
||
515 | ,ProdIsResult |
||
516 | ,ProdRemarks |
||
517 | ,ClientReviewer |
||
518 | ,ClientIsResult |
||
519 | ,ClientRemarks |
||
520 | ,DTIsGateWay |
||
521 | ,DTIsImport |
||
522 | ,DTIsRegSystem |
||
523 | ,DTRemarks |
||
524 | ) |
||
525 | values |
||
526 | ( |
||
527 | lower(newid()) |
||
528 | ,sc.DocumentNo |
||
529 | ,case when isnull(sc.RevisonNo,'')='' then '0' else sc.RevisonNo end |
||
530 | ,sc.System |
||
531 | ,sc.SubSystemCode |
||
532 | ,sc.RefProjectCode |
||
533 | ,sc.JobLevel |
||
534 | ,sc.PersonInCharge |
||
535 | ,getdate() |
||
536 | ,sc.RegisteredUser |
||
537 | ,sc.ToIsDiscussion |
||
538 | ,sc.ToRemarks |
||
539 | ,sc.ToCreator |
||
540 | ,sc.FrReviewStatus |
||
541 | ,sc.FrRemarks |
||
542 | ,sc.FrCreator |
||
543 | ,sc.ID2StartDate |
||
544 | ,sc.ID2EndDate |
||
545 | ,sc.ID2Status |
||
546 | ,sc.ID2Issues |
||
547 | ,sc.ReplyModifications |
||
548 | ,sc.ReplyRequester |
||
549 | ,sc.IsConvert |
||
550 | ,sc.AVEVAPersonInCharge |
||
551 | ,sc.AVEVAWorker |
||
552 | ,sc.AVEVAConvertDate |
||
553 | ,sc.AVEVAReviewDate |
||
554 | ,sc.AVEVAWorkDate |
||
555 | ,sc.AVEVAStatus |
||
556 | ,sc.AVEVAIssues |
||
557 | ,sc.ProdReviewer |
||
558 | ,sc.ProdIsResult |
||
559 | ,sc.ProdRemarks |
||
560 | ,sc.ClientReviewer |
||
561 | ,sc.ClientIsResult |
||
562 | ,sc.ClientRemarks |
||
563 | ,sc.DTIsGateWay |
||
564 | ,sc.DTIsImport |
||
565 | ,sc.DTIsRegSystem |
||
566 | ,sc.DTRemarks |
||
567 | ) |
||
568 | output $action;"; |
||
569 | //base.Query QueryFirstOrDefault<string>(query, docList, transaction); |
||
570 | //var test = base.Query<string>(query, docList); |
||
571 | var test = base.QueryFirstOrDefault<string>(query, docList); |
||
572 | } |
||
573 | */ |
||
574 | #endregion |
||
575 | |||
576 | d2d4f84b | yoush97 | using (var transaction = base.BeginTransaction()) |
577 | 482f6326 | yoush97 | { |
578 | 709c1971 | yoush97 | string query = string.Empty; |
579 | |||
580 | if (delDocList.Count > 0) |
||
581 | 482f6326 | yoush97 | { |
582 | 993feace | yoush97 | int paramMaxCount = 2000; |
583 | int execCount = (delDocList.Count / paramMaxCount) + 1; |
||
584 | 709c1971 | yoush97 | |
585 | 993feace | yoush97 | for (int i = 0; i < execCount; i++) |
586 | 709c1971 | yoush97 | { |
587 | 993feace | yoush97 | var dynamicParameters = new DynamicParameters(); |
588 | StringBuilder sbWhere = new StringBuilder(); |
||
589 | var parameters = new Dictionary<string, object>(); |
||
590 | |||
591 | parameters.Add("DeletedUser", userId); |
||
592 | |||
593 | var limitDwgNos = delDocList.Skip(paramMaxCount * i).Take(paramMaxCount).Select(x => x.DocID).ToList(); |
||
594 | 39938acd | yoush97 | sbWhere.Append($@" and DocID in @limitDwgNos "); |
595 | 993feace | yoush97 | parameters.Add("limitDwgNos", limitDwgNos); |
596 | |||
597 | 53fde692 | yoush97 | query = $@" |
598 | 74aa670a | yoush97 | update dbo.Documents |
599 | set IsDeleted=1 |
||
600 | 993feace | yoush97 | ,DeletedDate=getdate() |
601 | 54977253 | yoush97 | ,DeletedUser=@DeletedUser |
602 | 39938acd | yoush97 | where IsDeleted=0 {sbWhere}"; |
603 | 993feace | yoush97 | if (parameters.Count > 0) |
604 | { |
||
605 | dynamicParameters.AddDynamicParams(parameters); |
||
606 | } |
||
607 | |||
608 | base.Execute(query, dynamicParameters, transaction); |
||
609 | 709c1971 | yoush97 | } |
610 | } |
||
611 | 482f6326 | yoush97 | |
612 | foreach (Documents doc in docList) |
||
613 | { |
||
614 | if (string.IsNullOrEmpty(doc.DocID)) |
||
615 | { |
||
616 | 54977253 | yoush97 | doc.RegisteredUser = userId; |
617 | 993feace | yoush97 | doc.ModifiedUser = userId; |
618 | |||
619 | query = $@" |
||
620 | 39938acd | yoush97 | if exists(select * from dbo.Documents where RefProjectCode=@RefProjectCode and DocumentNo=@DocumentNo and IsDeleted=0) |
621 | 993feace | yoush97 | begin |
622 | --update |
||
623 | update dbo.Documents |
||
624 | set RevisonNo=case when isnull(@RevisonNo,'')='' then '0' else @RevisonNo end |
||
625 | ,System=@System |
||
626 | ,SubSystemCode=@SubSystemCode |
||
627 | ,JobLevel=@JobLevel |
||
628 | ,PersonInCharge=@PersonInCharge |
||
629 | ,ModifiedDate=getdate() |
||
630 | ,ModifiedUser=@ModifiedUser |
||
631 | ,ToIsDiscussion=@ToIsDiscussion |
||
632 | ,ToRemarks=@ToRemarks |
||
633 | ,ToCreator=@ToCreator |
||
634 | ,FrReviewStatus=@FrReviewStatus |
||
635 | ,FrRemarks=@FrRemarks |
||
636 | ,FrCreator=@FrCreator |
||
637 | ,ID2StartDate=@ID2StartDate |
||
638 | ,ID2EndDate=@ID2EndDate |
||
639 | ,ID2Status=@ID2Status |
||
640 | ,ID2Issues=@ID2Issues |
||
641 | ,ReplyModifications=@ReplyModifications |
||
642 | ,ReplyRequester=@ReplyRequester |
||
643 | ,IsConvert=@IsConvert |
||
644 | ,AVEVAPersonInCharge=@AVEVAPersonInCharge |
||
645 | ,AVEVAWorker=@AVEVAWorker |
||
646 | ,AVEVAConvertDate=@AVEVAConvertDate |
||
647 | ,AVEVAReviewDate=@AVEVAReviewDate |
||
648 | ,AVEVAWorkDate=@AVEVAWorkDate |
||
649 | ,AVEVAStatus=@AVEVAStatus |
||
650 | ,AVEVAIssues=@AVEVAIssues |
||
651 | ,ProdReviewer=@ProdReviewer |
||
652 | ,ProdIsResult=@ProdIsResult |
||
653 | ,ProdRemarks=@ProdRemarks |
||
654 | ,ClientReviewer=@ClientReviewer |
||
655 | ,ClientIsResult=@ClientIsResult |
||
656 | ,ClientRemarks=@ClientRemarks |
||
657 | ,DTIsGateWay=@DTIsGateWay |
||
658 | ,DTIsImport=@DTIsImport |
||
659 | ,DTIsRegSystem=@DTIsRegSystem |
||
660 | ,DTRemarks=@DTRemarks |
||
661 | 39938acd | yoush97 | where RefProjectCode=@RefProjectCode and DocumentNo=@DocumentNo and IsDeleted=0 |
662 | 993feace | yoush97 | |
663 | if @@rowcount > 0 |
||
664 | begin |
||
665 | 39938acd | yoush97 | select @DocID |
666 | 993feace | yoush97 | end |
667 | else |
||
668 | begin |
||
669 | select '' |
||
670 | end |
||
671 | end |
||
672 | else |
||
673 | begin |
||
674 | --insert |
||
675 | declare @tbdoc table(docid varchar(36)) |
||
676 | insert into dbo.Documents |
||
677 | ( |
||
678 | DocID |
||
679 | ,DocumentNo |
||
680 | ,RevisonNo |
||
681 | ,System |
||
682 | ,SubSystemCode |
||
683 | ,RefProjectCode |
||
684 | ,JobLevel |
||
685 | ,PersonInCharge |
||
686 | ,RegisteredDate |
||
687 | ,RegisteredUser |
||
688 | ,ToIsDiscussion |
||
689 | ,ToRemarks |
||
690 | ,ToCreator |
||
691 | ,FrReviewStatus |
||
692 | ,FrRemarks |
||
693 | ,FrCreator |
||
694 | ,ID2StartDate |
||
695 | ,ID2EndDate |
||
696 | ,ID2Status |
||
697 | ,ID2Issues |
||
698 | ,ReplyModifications |
||
699 | ,ReplyRequester |
||
700 | ,IsConvert |
||
701 | ,AVEVAPersonInCharge |
||
702 | ,AVEVAWorker |
||
703 | ,AVEVAConvertDate |
||
704 | ,AVEVAReviewDate |
||
705 | ,AVEVAWorkDate |
||
706 | ,AVEVAStatus |
||
707 | ,AVEVAIssues |
||
708 | ,ProdReviewer |
||
709 | ,ProdIsResult |
||
710 | ,ProdRemarks |
||
711 | ,ClientReviewer |
||
712 | ,ClientIsResult |
||
713 | ,ClientRemarks |
||
714 | ,DTIsGateWay |
||
715 | ,DTIsImport |
||
716 | ,DTIsRegSystem |
||
717 | ,DTRemarks |
||
718 | ) |
||
719 | output inserted.DocID into @tbdoc |
||
720 | values |
||
721 | ( |
||
722 | lower(newid()) |
||
723 | ,@DocumentNo |
||
724 | ,case when isnull(@RevisonNo,'')='' then '0' else @RevisonNo end |
||
725 | ,@System |
||
726 | ,@SubSystemCode |
||
727 | ,@RefProjectCode |
||
728 | ,@JobLevel |
||
729 | ,@PersonInCharge |
||
730 | ,getdate() |
||
731 | ,@RegisteredUser |
||
732 | ,@ToIsDiscussion |
||
733 | ,@ToRemarks |
||
734 | ,@ToCreator |
||
735 | ,@FrReviewStatus |
||
736 | ,@FrRemarks |
||
737 | ,@FrCreator |
||
738 | ,@ID2StartDate |
||
739 | ,@ID2EndDate |
||
740 | ,@ID2Status |
||
741 | ,@ID2Issues |
||
742 | ,@ReplyModifications |
||
743 | ,@ReplyRequester |
||
744 | ,@IsConvert |
||
745 | ,@AVEVAPersonInCharge |
||
746 | ,@AVEVAWorker |
||
747 | ,@AVEVAConvertDate |
||
748 | ,@AVEVAReviewDate |
||
749 | ,@AVEVAWorkDate |
||
750 | ,@AVEVAStatus |
||
751 | ,@AVEVAIssues |
||
752 | ,@ProdReviewer |
||
753 | ,@ProdIsResult |
||
754 | ,@ProdRemarks |
||
755 | ,@ClientReviewer |
||
756 | ,@ClientIsResult |
||
757 | ,@ClientRemarks |
||
758 | ,@DTIsGateWay |
||
759 | ,@DTIsImport |
||
760 | ,@DTIsRegSystem |
||
761 | ,@DTRemarks |
||
762 | ) |
||
763 | |||
764 | if @@rowcount > 0 |
||
765 | begin |
||
766 | select docid from @tbdoc |
||
767 | end |
||
768 | else |
||
769 | begin |
||
770 | select '' |
||
771 | end |
||
772 | end"; |
||
773 | #region 기존 입력 쿼리 주석 |
||
774 | /* |
||
775 | 482f6326 | yoush97 | query = $@" |
776 | b1591ae6 | yoush97 | declare @tbdoc table(docid varchar(36)) |
777 | 482f6326 | yoush97 | insert into dbo.Documents |
778 | ( |
||
779 | 54977253 | yoush97 | DocID |
780 | ,DocumentNo |
||
781 | ,RevisonNo |
||
782 | 79d7836e | yoush97 | ,System |
783 | ,SubSystemCode |
||
784 | 54977253 | yoush97 | ,RefProjectCode |
785 | ,JobLevel |
||
786 | ,PersonInCharge |
||
787 | ,RegisteredDate |
||
788 | ,RegisteredUser |
||
789 | ,ToIsDiscussion |
||
790 | ,ToRemarks |
||
791 | ,ToCreator |
||
792 | ,FrReviewStatus |
||
793 | ,FrRemarks |
||
794 | ,FrCreator |
||
795 | ,ID2StartDate |
||
796 | ,ID2EndDate |
||
797 | ,ID2Status |
||
798 | ,ID2Issues |
||
799 | cb2e1138 | yoush97 | ,ReplyModifications |
800 | ,ReplyRequester |
||
801 | ,IsConvert |
||
802 | ,AVEVAPersonInCharge |
||
803 | ,AVEVAWorker |
||
804 | 54977253 | yoush97 | ,AVEVAConvertDate |
805 | ,AVEVAReviewDate |
||
806 | ab3c1c74 | yoush97 | ,AVEVAWorkDate |
807 | 54977253 | yoush97 | ,AVEVAStatus |
808 | ,AVEVAIssues |
||
809 | ,ProdReviewer |
||
810 | ,ProdIsResult |
||
811 | ,ProdRemarks |
||
812 | ,ClientReviewer |
||
813 | ,ClientIsResult |
||
814 | ,ClientRemarks |
||
815 | ,DTIsGateWay |
||
816 | ,DTIsImport |
||
817 | ,DTIsRegSystem |
||
818 | ,DTRemarks |
||
819 | 482f6326 | yoush97 | ) |
820 | b1591ae6 | yoush97 | output inserted.DocID into @tbdoc |
821 | 482f6326 | yoush97 | values |
822 | ( |
||
823 | lower(newid()) |
||
824 | ,@DocumentNo |
||
825 | edded054 | yoush97 | ,case when isnull(@RevisonNo,'')='' then '0' else @RevisonNo end |
826 | 79d7836e | yoush97 | ,@System |
827 | ,@SubSystemCode |
||
828 | 36a31d25 | yoush97 | ,@RefProjectCode |
829 | 482f6326 | yoush97 | ,@JobLevel |
830 | ,@PersonInCharge |
||
831 | 54977253 | yoush97 | ,getdate() |
832 | ,@RegisteredUser |
||
833 | 482f6326 | yoush97 | ,@ToIsDiscussion |
834 | ,@ToRemarks |
||
835 | ,@ToCreator |
||
836 | ,@FrReviewStatus |
||
837 | ,@FrRemarks |
||
838 | ,@FrCreator |
||
839 | ,@ID2StartDate |
||
840 | ,@ID2EndDate |
||
841 | ,@ID2Status |
||
842 | ,@ID2Issues |
||
843 | cb2e1138 | yoush97 | ,@ReplyModifications |
844 | ,@ReplyRequester |
||
845 | ,@IsConvert |
||
846 | ,@AVEVAPersonInCharge |
||
847 | ,@AVEVAWorker |
||
848 | 482f6326 | yoush97 | ,@AVEVAConvertDate |
849 | ,@AVEVAReviewDate |
||
850 | ab3c1c74 | yoush97 | ,@AVEVAWorkDate |
851 | 482f6326 | yoush97 | ,@AVEVAStatus |
852 | ,@AVEVAIssues |
||
853 | ,@ProdReviewer |
||
854 | ,@ProdIsResult |
||
855 | ,@ProdRemarks |
||
856 | ,@ClientReviewer |
||
857 | ,@ClientIsResult |
||
858 | ,@ClientRemarks |
||
859 | ,@DTIsGateWay |
||
860 | ,@DTIsImport |
||
861 | ,@DTIsRegSystem |
||
862 | ,@DTRemarks |
||
863 | b1591ae6 | yoush97 | ) |
864 | |||
865 | if @@rowcount > 0 |
||
866 | begin |
||
867 | select docid from @tbdoc |
||
868 | end |
||
869 | else |
||
870 | begin |
||
871 | select '' |
||
872 | end;"; |
||
873 | 993feace | yoush97 | */ |
874 | #endregion |
||
875 | 482f6326 | yoush97 | } |
876 | else |
||
877 | { |
||
878 | 54977253 | yoush97 | doc.ModifiedUser = userId; |
879 | 482f6326 | yoush97 | query = $@" |
880 | update dbo.Documents |
||
881 | 54977253 | yoush97 | set DocumentNo=@DocumentNo |
882 | 993feace | yoush97 | ,RevisonNo=case when isnull(@RevisonNo,'')='' then '0' else @RevisonNo end |
883 | 79d7836e | yoush97 | ,System=@System |
884 | ,SubSystemCode=@SubSystemCode |
||
885 | 54977253 | yoush97 | ,RefProjectCode=@RefProjectCode |
886 | ,JobLevel=@JobLevel |
||
887 | ,PersonInCharge=@PersonInCharge |
||
888 | ,ModifiedDate=getdate() |
||
889 | ,ModifiedUser=@ModifiedUser |
||
890 | ,ToIsDiscussion=@ToIsDiscussion |
||
891 | ,ToRemarks=@ToRemarks |
||
892 | ,ToCreator=@ToCreator |
||
893 | ,FrReviewStatus=@FrReviewStatus |
||
894 | ,FrRemarks=@FrRemarks |
||
895 | ,FrCreator=@FrCreator |
||
896 | ,ID2StartDate=@ID2StartDate |
||
897 | ,ID2EndDate=@ID2EndDate |
||
898 | ,ID2Status=@ID2Status |
||
899 | ,ID2Issues=@ID2Issues |
||
900 | cb2e1138 | yoush97 | ,ReplyModifications=@ReplyModifications |
901 | ,ReplyRequester=@ReplyRequester |
||
902 | ,IsConvert=@IsConvert |
||
903 | ,AVEVAPersonInCharge=@AVEVAPersonInCharge |
||
904 | ,AVEVAWorker=@AVEVAWorker |
||
905 | 54977253 | yoush97 | ,AVEVAConvertDate=@AVEVAConvertDate |
906 | ,AVEVAReviewDate=@AVEVAReviewDate |
||
907 | ab3c1c74 | yoush97 | ,AVEVAWorkDate=@AVEVAWorkDate |
908 | 54977253 | yoush97 | ,AVEVAStatus=@AVEVAStatus |
909 | ,AVEVAIssues=@AVEVAIssues |
||
910 | ,ProdReviewer=@ProdReviewer |
||
911 | ,ProdIsResult=@ProdIsResult |
||
912 | ,ProdRemarks=@ProdRemarks |
||
913 | ,ClientReviewer=@ClientReviewer |
||
914 | ,ClientIsResult=@ClientIsResult |
||
915 | ,ClientRemarks=@ClientRemarks |
||
916 | ,DTIsGateWay=@DTIsGateWay |
||
917 | ,DTIsImport=@DTIsImport |
||
918 | ,DTIsRegSystem=@DTIsRegSystem |
||
919 | ,DTRemarks=@DTRemarks |
||
920 | b1591ae6 | yoush97 | where DocID=@DocID |
921 | |||
922 | if @@rowcount > 0 |
||
923 | begin |
||
924 | select @DocID |
||
925 | end |
||
926 | else |
||
927 | begin |
||
928 | select '' |
||
929 | end;"; |
||
930 | } |
||
931 | string refID = base.QueryFirstOrDefault<string>(query, doc, transaction); |
||
932 | |||
933 | if (doc.AttFiles != null && doc.AttFiles.Count > 0) |
||
934 | { |
||
935 | string attDelIDList = string.Join("','", doc.AttFiles.Where(x => !string.IsNullOrEmpty(x.FileID)).Select(x => x.FileID).ToList()); |
||
936 | |||
937 | if (!string.IsNullOrEmpty(refID) && attDelIDList.Length > 0) |
||
938 | { |
||
939 | query = $@" |
||
940 | delete from dbo.AttachFIles |
||
941 | where RefID=@RefID and FileID in ('{attDelIDList}');"; |
||
942 | base.Execute(query, new { RefID = refID }, transaction); |
||
943 | } |
||
944 | |||
945 | foreach (AttFileInfo attFile in doc.AttFiles) |
||
946 | { |
||
947 | e5ade387 | yoush97 | if (string.IsNullOrEmpty(attFile.RefID)) |
948 | { |
||
949 | attFile.RefID = refID; |
||
950 | attFile.Creator = userId; |
||
951 | b1591ae6 | yoush97 | |
952 | e5ade387 | yoush97 | query = $@" |
953 | b1591ae6 | yoush97 | insert into dbo.AttachFIles (FileID,RefID,Category,FileType,FileName,FilePath,FileExtension,FileData,Creator) |
954 | values |
||
955 | ( |
||
956 | lower(newid()) |
||
957 | ,@RefID |
||
958 | ,@Category |
||
959 | ,@FileType |
||
960 | ,@FileName |
||
961 | ,@FilePath |
||
962 | ,@FileExtension |
||
963 | ,@FileData |
||
964 | ,@Creator |
||
965 | )"; |
||
966 | |||
967 | e5ade387 | yoush97 | base.Execute(query, attFile, transaction); |
968 | } |
||
969 | b1591ae6 | yoush97 | } |
970 | 482f6326 | yoush97 | } |
971 | } |
||
972 | |||
973 | transaction.Commit(); |
||
974 | isSuccess = true; |
||
975 | } |
||
976 | } |
||
977 | catch (Exception ex) |
||
978 | { |
||
979 | throw ex; |
||
980 | } |
||
981 | |||
982 | return isSuccess; |
||
983 | } |
||
984 | 978488b0 | yoush97 | |
985 | public Documents SetDocumentDataField(Documents doc, string userId) |
||
986 | { |
||
987 | Documents resultData = null; |
||
988 | |||
989 | try |
||
990 | { |
||
991 | using (var transaction = base.BeginTransaction()) |
||
992 | { |
||
993 | string query = string.Empty; |
||
994 | |||
995 | if (!string.IsNullOrEmpty(doc.DocID)) |
||
996 | { |
||
997 | StringBuilder sbSet = new StringBuilder(); |
||
998 | var parameters = new Dictionary<string, object>(); |
||
999 | |||
1000 | #region Update 할 목록 |
||
1001 | if (doc.ID2StartDate != null) |
||
1002 | { |
||
1003 | 4b8d9ad9 | yoush97 | sbSet.Append(" ,ID2StartDate=(case when ID2StartDate is null then @DateTimeNow else ID2StartDate end) "); |
1004 | 978488b0 | yoush97 | parameters.Add("ID2StartDate", doc.ID2StartDate); |
1005 | } |
||
1006 | 4b8d9ad9 | yoush97 | |
1007 | if (doc.Worker != null) |
||
1008 | { |
||
1009 | sbSet.Append(" ,Worker=@Worker "); |
||
1010 | parameters.Add("Worker", doc.Worker); |
||
1011 | } |
||
1012 | 978488b0 | yoush97 | #endregion |
1013 | |||
1014 | if (parameters.Count > 0) |
||
1015 | { |
||
1016 | sbSet.Append(" ,ModifiedUser=@ModifiedUser "); |
||
1017 | parameters.Add("ModifiedUser", userId); |
||
1018 | |||
1019 | 54977253 | yoush97 | parameters.Add("DocID", doc.DocID); |
1020 | |||
1021 | 978488b0 | yoush97 | query = $@" |
1022 | declare @DateTimeNow datetime |
||
1023 | set @DateTimeNow = getdate() |
||
1024 | |||
1025 | update dbo.Documents |
||
1026 | set ModifiedDate=@DateTimeNow {sbSet} |
||
1027 | where [DocID]=@DocID |
||
1028 | |||
1029 | if @@rowcount > 0 |
||
1030 | begin |
||
1031 | fe310cac | yoush97 | select * from dbo.Documents where DocID=@DocID |
1032 | 978488b0 | yoush97 | end |
1033 | else |
||
1034 | begin |
||
1035 | fe310cac | yoush97 | select * from dbo.Documents where 1=2 |
1036 | 978488b0 | yoush97 | end;"; |
1037 | resultData = base.QueryFirstOrDefault<Documents>(query, parameters, transaction); |
||
1038 | } |
||
1039 | } |
||
1040 | |||
1041 | transaction.Commit(); |
||
1042 | } |
||
1043 | } |
||
1044 | catch (Exception ex) |
||
1045 | { |
||
1046 | throw ex; |
||
1047 | } |
||
1048 | |||
1049 | return resultData; |
||
1050 | } |
||
1051 | fe57f64a | yoush97 | |
1052 | 54977253 | yoush97 | public bool SetDocumentDatasField(List<Documents> docs, string userId) |
1053 | { |
||
1054 | bool isSuccess = false; |
||
1055 | |||
1056 | try |
||
1057 | { |
||
1058 | using (var transaction = base.BeginTransaction()) |
||
1059 | { |
||
1060 | foreach (Documents doc in docs) |
||
1061 | { |
||
1062 | string query = string.Empty; |
||
1063 | |||
1064 | if (!string.IsNullOrEmpty(doc.DocID)) |
||
1065 | { |
||
1066 | StringBuilder sbSet = new StringBuilder(); |
||
1067 | var parameters = new Dictionary<string, object>(); |
||
1068 | |||
1069 | #region Update 할 목록 |
||
1070 | if (doc.ID2EndDate != null) |
||
1071 | { |
||
1072 | sbSet.Append(" ,ID2EndDate=@ID2EndDate "); |
||
1073 | parameters.Add("ID2EndDate", doc.ID2EndDate); |
||
1074 | } |
||
1075 | #endregion |
||
1076 | |||
1077 | if (parameters.Count > 0) |
||
1078 | { |
||
1079 | sbSet.Append(" ,ModifiedUser=@ModifiedUser "); |
||
1080 | parameters.Add("ModifiedUser", userId); |
||
1081 | |||
1082 | parameters.Add("DocID", doc.DocID); |
||
1083 | |||
1084 | query = $@" |
||
1085 | update dbo.Documents |
||
1086 | set ModifiedDate=getdate() {sbSet} |
||
1087 | where [DocID]=@DocID;"; |
||
1088 | e458a996 | taeseongkim | base.Execute(query, parameters, transaction); |
1089 | 54977253 | yoush97 | } |
1090 | } |
||
1091 | } |
||
1092 | transaction.Commit(); |
||
1093 | isSuccess = true; |
||
1094 | } |
||
1095 | } |
||
1096 | catch (Exception ex) |
||
1097 | { |
||
1098 | throw ex; |
||
1099 | } |
||
1100 | |||
1101 | return isSuccess; |
||
1102 | } |
||
1103 | |||
1104 | fe57f64a | yoush97 | |
1105 | //ID2 |
||
1106 | public IEnumerable<ID2Drawings> GetID2DrawingsByProject(ID2ProjectInfo id2Info) |
||
1107 | { |
||
1108 | 82705273 | yoush97 | try |
1109 | { |
||
1110 | string query = $@" |
||
1111 | fe57f64a | yoush97 | select @Name PROJECTNAME |
1112 | ,dw.[UID], dw.[NAME], left(dw.[NAME], len(dw.[NAME]) - dw.lastidx) DOCNAME |
||
1113 | ,case when rtrim(ltrim(isnull(convert(varchar, dw.[DATETIME]),''))) = '' then null else convert(datetime, convert(varchar, dw.[DATETIME])) end [DATETIME] |
||
1114 | ,dw.OCCUPIED, dw.[Image] |
||
1115 | from |
||
1116 | ( |
||
1117 | select [UID], [NAME], [DATETIME], OCCUPIED, [Image], charindex('.', reverse([NAME])) lastidx |
||
1118 | from dbo.Drawings |
||
1119 | ) dw;"; |
||
1120 | 82705273 | yoush97 | return Query<ID2Drawings>(query, id2Info); |
1121 | } |
||
1122 | catch (Exception ex) |
||
1123 | { |
||
1124 | throw ex; |
||
1125 | } |
||
1126 | fe57f64a | yoush97 | } |
1127 | 39938acd | yoush97 | |
1128 | |||
1129 | //Transactions |
||
1130 | public int GetTranKey(string userId, string projectID) |
||
1131 | { |
||
1132 | int result = -1; |
||
1133 | |||
1134 | try |
||
1135 | { |
||
1136 | var dynamicParameters = new DynamicParameters(); |
||
1137 | |||
1138 | var parameters = new Dictionary<string, object>() |
||
1139 | { |
||
1140 | { "UserId", userId }, |
||
1141 | { "ProjectID", projectID } |
||
1142 | }; |
||
1143 | |||
1144 | dynamicParameters.AddDynamicParams(parameters); |
||
1145 | |||
1146 | using (var transaction = base.BeginTransaction()) |
||
1147 | { |
||
1148 | string query = $@" |
||
1149 | --log master 입력 |
||
1150 | insert into dbo.Transactions (UserId, ProjectID) |
||
1151 | values (@UserId, @ProjectID); |
||
1152 | select scope_identity();"; |
||
1153 | |||
1154 | result = base.ExecuteScalar<int>(query, dynamicParameters, transaction); |
||
1155 | |||
1156 | transaction.Commit(); |
||
1157 | } |
||
1158 | } |
||
1159 | catch (Exception ex) |
||
1160 | { |
||
1161 | throw ex; |
||
1162 | } |
||
1163 | |||
1164 | return result; |
||
1165 | } |
||
1166 | |||
1167 | public bool SetTran(int seq, bool isMgt, bool isStart, int itemCount, int markusItemCount) |
||
1168 | { |
||
1169 | bool result = false; |
||
1170 | |||
1171 | try |
||
1172 | { |
||
1173 | var dynamicParameters = new DynamicParameters(); |
||
1174 | var parameters = new Dictionary<string, object>() |
||
1175 | { |
||
1176 | { "Seq", seq }, |
||
1177 | { "ItemCount", itemCount }, |
||
1178 | { "MarkusItemCount", markusItemCount } |
||
1179 | }; |
||
1180 | dynamicParameters.AddDynamicParams(parameters); |
||
1181 | StringBuilder sbWhere = new StringBuilder(); |
||
1182 | if (isMgt) |
||
1183 | { |
||
1184 | if (isStart) |
||
1185 | { |
||
1186 | sbWhere.Append("StartDate=getdate(), ItemCount=@ItemCount"); |
||
1187 | } |
||
1188 | else |
||
1189 | { |
||
1190 | sbWhere.Append("EndDate=getdate()"); |
||
1191 | } |
||
1192 | |||
1193 | } |
||
1194 | else |
||
1195 | { |
||
1196 | if (isStart) |
||
1197 | { |
||
1198 | sbWhere.Append("MarkusStartDate=getdate(), MarkusItemCount=@MarkusItemCount"); |
||
1199 | } |
||
1200 | else |
||
1201 | { |
||
1202 | sbWhere.Append("MarkusEndDate=getdate()"); |
||
1203 | } |
||
1204 | } |
||
1205 | |||
1206 | using (var transaction = base.BeginTransaction()) |
||
1207 | { |
||
1208 | string query = $@" |
||
1209 | update dbo.Transactions |
||
1210 | set {sbWhere} |
||
1211 | where Seq=@Seq;"; |
||
1212 | base.Execute(query, dynamicParameters, transaction); |
||
1213 | |||
1214 | transaction.Commit(); |
||
1215 | |||
1216 | result = true; |
||
1217 | } |
||
1218 | } |
||
1219 | catch (Exception ex) |
||
1220 | { |
||
1221 | throw ex; |
||
1222 | } |
||
1223 | |||
1224 | return result; |
||
1225 | } |
||
1226 | |||
1227 | public string GetTranData(int seq) |
||
1228 | { |
||
1229 | try |
||
1230 | { |
||
1231 | var dynamicParameters = new DynamicParameters(); |
||
1232 | var parameters = new Dictionary<string, object>() |
||
1233 | { |
||
1234 | { "Seq", seq } |
||
1235 | }; |
||
1236 | dynamicParameters.AddDynamicParams(parameters); |
||
1237 | |||
1238 | string query = $@" |
||
1239 | select * |
||
1240 | from dbo.Transactions |
||
1241 | where Seq=@Seq"; |
||
1242 | |||
1243 | IEnumerable<dynamic> rows = base.Query<dynamic>(query, dynamicParameters); |
||
1244 | |||
1245 | return this.ToSerializeData(rows); |
||
1246 | } |
||
1247 | catch (Exception ex) |
||
1248 | { |
||
1249 | throw ex; |
||
1250 | } |
||
1251 | } |
||
1252 | |||
1253 | public string ToSerializeData(IEnumerable<dynamic> items) |
||
1254 | { |
||
1255 | List<Dictionary<string, object>> list = new List<Dictionary<string, object>>(); |
||
1256 | |||
1257 | if (items == null) return string.Empty; |
||
1258 | var datas = items.ToArray(); |
||
1259 | if (datas.Length == 0) return string.Empty; |
||
1260 | |||
1261 | foreach (var data in datas) |
||
1262 | { |
||
1263 | var dicItem = new Dictionary<string, object>(); |
||
1264 | foreach (var pair in ((IDictionary<string, object>)data)) |
||
1265 | { |
||
1266 | dicItem.Add(pair.Key, pair.Value); |
||
1267 | } |
||
1268 | list.Add(dicItem); |
||
1269 | } |
||
1270 | |||
1271 | return JsonConvert.SerializeObject(list); |
||
1272 | } |
||
1273 | |||
1274 | public IEnumerable<Documents> GetTrDocuments(int seq) |
||
1275 | { |
||
1276 | var dynamicParameters = new DynamicParameters(); |
||
1277 | StringBuilder sbWhere = new StringBuilder(); |
||
1278 | var parameters = new Dictionary<string, object>() |
||
1279 | { |
||
1280 | { "Seq", seq } |
||
1281 | }; |
||
1282 | dynamicParameters.AddDynamicParams(parameters); |
||
1283 | |||
1284 | try |
||
1285 | { |
||
1286 | string query = $@" |
||
1287 | declare @CreatedDate datetime |
||
1288 | declare @EndDate datetime |
||
1289 | declare @ProjectGroupID varchar(36) |
||
1290 | |||
1291 | select top 1 @CreatedDate=CreatedDate, @EndDate=EndDate, @ProjectGroupID=ProjectID |
||
1292 | from dbo.Transactions |
||
1293 | where Seq=@Seq |
||
1294 | |||
1295 | select RefProjectCode, DocumentNo |
||
1296 | from dbo.Documents |
||
1297 | where RefProjectCode in (select Code from dbo.Projects where ParentID=@ProjectGroupID) |
||
1298 | and RegisteredDate > @CreatedDate and RegisteredDate <= @EndDate"; |
||
1299 | |||
1300 | if (parameters.Count > 0) |
||
1301 | { |
||
1302 | dynamicParameters.AddDynamicParams(parameters); |
||
1303 | } |
||
1304 | |||
1305 | return Query<Documents>(query, dynamicParameters); |
||
1306 | } |
||
1307 | catch (Exception ex) |
||
1308 | { |
||
1309 | throw ex; |
||
1310 | } |
||
1311 | } |
||
1312 | 5898479a | yoush97 | } |
1313 | d2d4f84b | yoush97 | } |