프로젝트

일반

사용자정보

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

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
}