프로젝트

일반

사용자정보

통계
| 브랜치(Branch): | 개정판:

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
}