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