프로젝트

일반

사용자정보

통계
| 개정판:

hytos / ID2.Manager / ID2.Manager.Dapper / Repository / DocumentRepository.cs @ 7dc72b5c

이력 | 보기 | 이력해설 | 다운로드 (33.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 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 a8981c30 yoush97
        public (IEnumerable<Documents> dwgs, int totalCnt) GetDocuments(string projectGroupID, List<string> dateTypes, DateTime? frDate, DateTime? toDate,  string team, 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 a8981c30 yoush97
            if (!string.IsNullOrEmpty(team))
105
            {
106
                sbWhere.Append(" and prj.Team=@Team ");
107
                parameters.Add("Team", team);
108
            }
109 416979ec yoush97
            if (projectCode == null)
110 ea97eee6 yoush97
            {
111 416979ec yoush97
                sbWhere.Append(" and prj.Code is null ");
112 7dc72b5c yoush97
                sbTotalWhere.Append(" and doc.RefProjectCode in (select Code from dbo.Projects where ParentID=@RefGroupID) ");
113 3adabe82 yoush97
                parameters.Add("RefGroupID", projectGroupID);
114 416979ec yoush97
            }
115
            else if (projectCode.Equals(string.Empty))
116
            {
117 7dc72b5c yoush97
                sbWhere.Append(" and doc.RefProjectCode in (select Code from dbo.Projects where ParentID=@RefGroupID) ");
118
                sbTotalWhere.Append(" and doc.RefProjectCode in (select Code from dbo.Projects where ParentID=@RefGroupID) ");
119 ea97eee6 yoush97
                parameters.Add("RefGroupID", projectGroupID);
120
            }
121
            else
122 8e373ccf yoush97
            {
123 7dc72b5c yoush97
                sbTotalWhere.Append(" and doc.RefProjectCode in (select Code from dbo.Projects where ParentID=@RefGroupID) ");
124 e8a86b9b yoush97
                parameters.Add("RefGroupID", projectGroupID);
125
126 36a31d25 yoush97
                sbWhere.Append(" and doc.RefProjectCode=@RefProjectCode ");
127
                parameters.Add("RefProjectCode", projectCode);
128 8e373ccf yoush97
            }
129
            if (!string.IsNullOrEmpty(personIncharge))
130
            {
131
                sbWhere.Append(" and doc.PersonInCharge=@PersonInCharge ");
132
                parameters.Add("PersonInCharge", personIncharge);
133
            }
134 08ea0584 yoush97
            if (!string.IsNullOrEmpty(jobLevel))
135
            {
136
                sbWhere.Append(" and doc.JobLevel=@JobLevel ");
137
                parameters.Add("JobLevel", jobLevel);
138
            }
139 8e373ccf yoush97
            if (!string.IsNullOrEmpty(documentNo))
140
            {
141
                sbWhere.Append(" and doc.DocumentNo like '%' + @DocumentNo +'%' ");
142
                parameters.Add("DocumentNo", documentNo);
143
            }
144
145 296ffcbd yoush97
            if (!string.IsNullOrEmpty(isToIsDiscussion))
146
            {
147
                sbWhere.Append(" and doc.ToIsDiscussion=@ToIsDiscussion ");
148
                parameters.Add("ToIsDiscussion", isToIsDiscussion);
149
            }
150
151
            if (!string.IsNullOrEmpty(isFrReviewStatus))
152
            {
153
                sbWhere.Append(" and doc.FrReviewStatus=@FrReviewStatus ");
154
                parameters.Add("FrReviewStatus", isFrReviewStatus);
155
            }
156
157 08ea0584 yoush97
            if (!string.IsNullOrEmpty(id2Status))
158
            {
159
                sbWhere.Append(" and doc.ID2Status=@ID2Status ");
160
                parameters.Add("ID2Status", id2Status);
161
            }
162 3cc84cb6 yoush97
163
            if (!string.IsNullOrEmpty(id2Issues))
164
            {
165
                sbWhere.Append(" and (case when isnull(ltrim(rtrim(doc.ID2Issues)),'') = '' then 'No' else 'Yes' end)=@ID2Issues ");
166
                parameters.Add("ID2Issues", id2Issues);
167
            }
168
            
169 08ea0584 yoush97
            if (!string.IsNullOrEmpty(avevaStatus))
170
            {
171
                sbWhere.Append(" and doc.AVEVAStatus=@AVEVAStatus ");
172
                parameters.Add("AVEVAStatus", avevaStatus);
173
            }
174
175 3cc84cb6 yoush97
            if (!string.IsNullOrEmpty(avevaIssues))
176
            {
177
                sbWhere.Append(" and (case when isnull(ltrim(rtrim(doc.AVEVAIssues)),'') = '' then 'No' else 'Yes' end)=@AVEVAIssues ");
178
                parameters.Add("AVEVAIssues", avevaIssues);
179
            }
180
181 08ea0584 yoush97
            if (!string.IsNullOrEmpty(prodIsResult))
182
            {
183
                sbWhere.Append(" and doc.ProdIsResult=@ProdIsResult ");
184
                parameters.Add("ProdIsResult", prodIsResult);
185
            }
186
            if (!string.IsNullOrEmpty(clientIsResult))
187
            {
188
                sbWhere.Append(" and doc.ClientIsResult=@ClientIsResult ");
189
                parameters.Add("ClientIsResult", clientIsResult);
190
            }
191 ee82162b yoush97
            if (!string.IsNullOrEmpty(isGateWay))
192
            {
193
                sbWhere.Append(" and doc.DTIsGateWay=@DTIsGateWay ");
194
                parameters.Add("DTIsGateWay", isGateWay);
195
            }
196
            if (!string.IsNullOrEmpty(isRegSystem))
197
            {
198
                sbWhere.Append(" and doc.DTIsRegSystem=@DTIsRegSystem ");
199
                parameters.Add("DTIsRegSystem", isRegSystem);
200
            }
201 08ea0584 yoush97
202 82705273 yoush97
            try
203
            {
204 08499f5f taeseongkim
205
                //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,
206 fe310cac yoush97
                string query = $@"
207 7dc72b5c yoush97
select   doc.*
208
        ,prj.Team
209
        ,files.*
210
        ,null PROJECT_NO
211
		,null DOCUMENT_ID
212
		,null PAGENUMBER
213
		,null [TEXT]
214
		,null CREATE_DATE
215
		,null CREATE_USER
216 fe310cac yoush97
from     dbo.Documents doc
217 7dc72b5c yoush97
             left outer join dbo.Projects prj on doc.RefProjectCode=prj.Code
218
left outer join AttachFIles files ON doc.DocID = fIles.RefID
219 fe310cac yoush97
where    doc.IsDeleted=0 {sbWhere}
220
order by doc.Seq
221 7066b8a9 yoush97
222 416979ec yoush97
select @Total=count(*) from dbo.Documents doc where doc.IsDeleted=0 {sbTotalWhere};";
223 8e373ccf yoush97
224 82705273 yoush97
                if (parameters.Count > 0)
225
                {
226
                    dynamicParameters.AddDynamicParams(parameters);
227
                }
228 5898479a yoush97
229 e458a996 taeseongkim
                var docDictionary = new Dictionary<string, Documents>();
230
231 08499f5f taeseongkim
                var ret = MultiQuery<Documents, AttFileInfo, MarkupText, Documents>(query,
232
                                (document, attfile, markusText) =>
233 e458a996 taeseongkim
                                {
234
                                    Documents doc;
235
236 08499f5f taeseongkim
                                    if (!docDictionary.TryGetValue(document.DocID, out doc))
237 e458a996 taeseongkim
                                    {
238
                                        doc = document;
239 08499f5f taeseongkim
                                        docDictionary.Add(doc.DocID, doc);
240
                                    }
241
242
                                    if (markusText != null)
243
                                    {
244 9096bc6d taeseongkim
                                        doc.ConvertStatus = 4;
245 08499f5f taeseongkim
246 9096bc6d taeseongkim
                                        if (markusText.TEXT != null && markusText.CREATE_DATE != null && markusText.CREATE_USER != null)
247 08499f5f taeseongkim
                                        {
248 9096bc6d taeseongkim
                                            doc.Markups = doc.Markups ?? new List<MarkupText>();
249
250
                                            if (!doc.Markups.Any(x => x.Equals(markusText)))
251
                                            {
252
                                                doc.Markups.Add(markusText);
253
                                            }
254 08499f5f taeseongkim
                                        }
255
                                    }
256
257
                                    if (attfile != null)
258
                                    {
259
                                        doc.AttFiles = doc.AttFiles ?? new List<AttFileInfo>();
260
                                        System.Diagnostics.Debug.WriteLine(attfile.FileName);
261
                                        if (!doc.AttFiles.Any(x => x.FileID == attfile.FileID))
262
                                        {
263 e8a86b9b yoush97
                                            switch (attfile.Category)
264
                                            {
265
                                                case "toreview":
266
                                                    doc.ToCapture++;
267
                                                    break;
268
                                                case "frreview":
269
                                                    doc.FrCapture++;
270
                                                    break;
271 f4230b40 yoush97
                                                case "id2work":
272
                                                    doc.ID2Capture++;
273 e8a86b9b yoush97
                                                    break;
274
                                            }
275
276 08499f5f taeseongkim
                                            doc.AttFiles.Add(attfile);
277
                                        }
278 e458a996 taeseongkim
                                    }
279
280
                                    return doc;
281
282 a37dc9a7 taeseongkim
                                }, dynamicParameters, splitOn: "DocID,FileID,PROJECT_NO").Distinct();
283 7066b8a9 yoush97
284 82705273 yoush97
                int totalCount = dynamicParameters.Get<int>("Total");
285 7066b8a9 yoush97
286 82705273 yoush97
                return (ret, totalCount);
287
            }
288
            catch (Exception ex)
289
            {
290
                throw ex;
291
            }
292 5898479a yoush97
        }
293 482f6326 yoush97
294 ff990784 yoush97
        public int ExistsDocument(string projectGroupID, List<string> newDwgNos)
295
        {
296 9107c43b yoush97
            int existCount = 0;
297 ff990784 yoush97
298 9107c43b yoush97
            int paramMaxCount = 2000;
299
            int execCount = (newDwgNos.Count / paramMaxCount) + 1;
300 ff990784 yoush97
301 9107c43b yoush97
            for (int i = 0; i < execCount; i++)
302
            {
303
                var dynamicParameters = new DynamicParameters();
304
                StringBuilder sbWhere = new StringBuilder();
305
                var parameters = new Dictionary<string, object>();
306
                if (!string.IsNullOrEmpty(projectGroupID))
307 ff990784 yoush97
                {
308 9107c43b yoush97
                    sbWhere.Append(" and doc.RefProjectCode in (select Code from dbo.Projects where ParentID=@RefGroupID) ");
309
                    parameters.Add("RefGroupID", projectGroupID);
310 ff990784 yoush97
                }
311
312 9107c43b yoush97
                var limitDwgNos = newDwgNos.Skip(paramMaxCount * i).Take(paramMaxCount).ToList();
313
                sbWhere.Append($@" and doc.DocumentNo in @limitDwgNos ");
314
                parameters.Add("limitDwgNos", limitDwgNos);
315
316
                try
317
                {
318
                    string query = $@"
319 ff990784 yoush97
select   count(*) cnt
320
from     dbo.Documents doc
321
where    doc.IsDeleted=0 {sbWhere}";
322
323 9107c43b yoush97
                    if (parameters.Count > 0)
324
                    {
325
                        dynamicParameters.AddDynamicParams(parameters);
326
                    }
327
328
                    existCount += ExecuteScalar<int>(query, dynamicParameters);
329
                }
330
                catch (Exception ex)
331 ff990784 yoush97
                {
332 9107c43b yoush97
                    throw ex;
333 ff990784 yoush97
                }
334
            }
335 9107c43b yoush97
336
            return existCount;
337 ff990784 yoush97
        }
338
339 416979ec yoush97
//        public bool ExistsNotRegID2ProjectDocuments()
340
//        {
341
//            try
342
//            {
343
//                string query = $@"
344
//select top 1 *
345
//from   dbo.Documents doc
346
//where  doc.IsDeleted=0
347
//   and doc.RefProjectCode not in (select Code from dbo.Projects prj);";
348
349
//                bool isRet = ExecuteScalar<int>(query, null) > 0;
350
351
//                return isRet;
352
//            }
353
//            catch (Exception ex)
354
//            {
355
//                throw ex;
356
//            }
357
//        }
358
359 f074a104 yoush97
        public bool SetDocumentData(string projectGroupID, List<Documents> docList, List<string> delDocList, string userId)
360 482f6326 yoush97
        {
361
            bool isSuccess = false;
362
363
            try
364
            {
365 d2d4f84b yoush97
                using (var transaction = base.BeginTransaction())
366 482f6326 yoush97
                {
367 709c1971 yoush97
                    string query = string.Empty;
368
369
                    if (delDocList.Count > 0)
370 482f6326 yoush97
                    {
371 993feace yoush97
                        int paramMaxCount = 2000;
372
                        int execCount = (delDocList.Count / paramMaxCount) + 1;
373 709c1971 yoush97
374 993feace yoush97
                        for (int i = 0; i < execCount; i++)
375 709c1971 yoush97
                        {
376 993feace yoush97
                            var dynamicParameters = new DynamicParameters();
377
                            StringBuilder sbWhere = new StringBuilder();
378
                            var parameters = new Dictionary<string, object>();
379
380
                            parameters.Add("DeletedUser", userId);
381
382 f074a104 yoush97
                            var limitDwgNos = delDocList.Skip(paramMaxCount * i).Take(paramMaxCount).ToList();
383 39938acd yoush97
                            sbWhere.Append($@" and DocID in @limitDwgNos ");
384 993feace yoush97
                            parameters.Add("limitDwgNos", limitDwgNos);
385
386 53fde692 yoush97
                            query = $@"
387 74aa670a yoush97
update dbo.Documents
388
set    IsDeleted=1
389 993feace yoush97
      ,DeletedDate=getdate()
390 54977253 yoush97
      ,DeletedUser=@DeletedUser
391 39938acd yoush97
where  IsDeleted=0 {sbWhere}";
392 993feace yoush97
                            if (parameters.Count > 0)
393
                            {
394
                                dynamicParameters.AddDynamicParams(parameters);
395
                            }
396
397
                            base.Execute(query, dynamicParameters, transaction);
398 709c1971 yoush97
                        }
399
                    }
400 482f6326 yoush97
401
                    foreach (Documents doc in docList)
402
                    {
403
                        if (string.IsNullOrEmpty(doc.DocID))
404
                        {
405 54977253 yoush97
                            doc.RegisteredUser = userId;
406 993feace yoush97
                            doc.ModifiedUser = userId;
407
408
                            query = $@"
409 39938acd yoush97
if exists(select * from dbo.Documents where RefProjectCode=@RefProjectCode and DocumentNo=@DocumentNo and IsDeleted=0)
410 993feace yoush97
begin
411
    --update
412
    update dbo.Documents
413
    set    RevisonNo=case when isnull(@RevisonNo,'')='' then '0' else @RevisonNo end
414
          ,System=@System
415
          ,SubSystemCode=@SubSystemCode
416
          ,JobLevel=@JobLevel
417 b2c03c2e yoush97
          ,Simularity=@Simularity
418 993feace yoush97
          ,PersonInCharge=@PersonInCharge
419 62e3647c yoush97
          ,Worker=@Worker
420 993feace yoush97
          ,ModifiedDate=getdate()
421
          ,ModifiedUser=@ModifiedUser
422
          ,ToIsDiscussion=@ToIsDiscussion
423
          ,ToRemarks=@ToRemarks
424
          ,ToCreator=@ToCreator
425
          ,FrReviewStatus=@FrReviewStatus
426
          ,FrRemarks=@FrRemarks
427
          ,FrCreator=@FrCreator
428
          ,ID2StartDate=@ID2StartDate
429
          ,ID2EndDate=@ID2EndDate
430
          ,ID2Status=@ID2Status
431
          ,ID2Issues=@ID2Issues
432
          ,ReplyModifications=@ReplyModifications
433
          ,ReplyRequester=@ReplyRequester
434
          ,IsConvert=@IsConvert
435
          ,AVEVAPersonInCharge=@AVEVAPersonInCharge
436
          ,AVEVAWorker=@AVEVAWorker
437
          ,AVEVAConvertDate=@AVEVAConvertDate
438
          ,AVEVAReviewDate=@AVEVAReviewDate
439
          ,AVEVAWorkDate=@AVEVAWorkDate
440
          ,AVEVAStatus=@AVEVAStatus
441
          ,AVEVAIssues=@AVEVAIssues
442
          ,ProdReviewer=@ProdReviewer
443
          ,ProdIsResult=@ProdIsResult
444
          ,ProdRemarks=@ProdRemarks
445
          ,ClientReviewer=@ClientReviewer
446
          ,ClientIsResult=@ClientIsResult
447
          ,ClientRemarks=@ClientRemarks
448
          ,DTIsGateWay=@DTIsGateWay
449
          ,DTIsImport=@DTIsImport
450
          ,DTIsRegSystem=@DTIsRegSystem
451
          ,DTRemarks=@DTRemarks
452 39938acd yoush97
    where  RefProjectCode=@RefProjectCode and DocumentNo=@DocumentNo and IsDeleted=0
453 993feace yoush97
454
    if @@rowcount > 0
455
    begin
456 39938acd yoush97
        select @DocID
457 993feace yoush97
    end
458
    else
459
    begin
460
        select ''
461
    end
462
end
463
else
464
begin
465
    --insert
466
    declare @tbdoc table(docid varchar(36))
467
    insert into dbo.Documents
468
    (
469
         DocID
470
        ,DocumentNo
471
        ,RevisonNo
472
        ,System
473
        ,SubSystemCode
474
        ,RefProjectCode
475
        ,JobLevel
476 b2c03c2e yoush97
        ,Simularity
477 993feace yoush97
        ,PersonInCharge
478 62e3647c yoush97
        ,Worker
479 993feace yoush97
        ,RegisteredDate
480
        ,RegisteredUser
481
        ,ToIsDiscussion
482
        ,ToRemarks
483
        ,ToCreator
484
        ,FrReviewStatus
485
        ,FrRemarks
486
        ,FrCreator
487
        ,ID2StartDate
488
        ,ID2EndDate
489
        ,ID2Status
490
        ,ID2Issues
491
        ,ReplyModifications
492
        ,ReplyRequester
493
        ,IsConvert
494
        ,AVEVAPersonInCharge
495
        ,AVEVAWorker
496
        ,AVEVAConvertDate
497
        ,AVEVAReviewDate
498
        ,AVEVAWorkDate
499
        ,AVEVAStatus
500
        ,AVEVAIssues
501
        ,ProdReviewer
502
        ,ProdIsResult
503
        ,ProdRemarks
504
        ,ClientReviewer
505
        ,ClientIsResult
506
        ,ClientRemarks
507
        ,DTIsGateWay
508
        ,DTIsImport
509
        ,DTIsRegSystem
510
        ,DTRemarks
511
    )
512
    output inserted.DocID into @tbdoc
513
    values 
514
    (
515
         lower(newid())
516
        ,@DocumentNo
517
        ,case when isnull(@RevisonNo,'')='' then '0' else @RevisonNo end
518
        ,@System
519
        ,@SubSystemCode
520
        ,@RefProjectCode
521
        ,@JobLevel
522 b2c03c2e yoush97
        ,@Simularity
523 993feace yoush97
        ,@PersonInCharge
524 62e3647c yoush97
        ,@Worker
525 993feace yoush97
        ,getdate()
526
        ,@RegisteredUser
527
        ,@ToIsDiscussion
528
        ,@ToRemarks
529
        ,@ToCreator
530
        ,@FrReviewStatus
531
        ,@FrRemarks
532
        ,@FrCreator
533
        ,@ID2StartDate
534
        ,@ID2EndDate
535
        ,@ID2Status
536
        ,@ID2Issues
537
        ,@ReplyModifications
538
        ,@ReplyRequester
539
        ,@IsConvert
540
        ,@AVEVAPersonInCharge
541
        ,@AVEVAWorker
542
        ,@AVEVAConvertDate
543
        ,@AVEVAReviewDate
544
        ,@AVEVAWorkDate
545
        ,@AVEVAStatus
546
        ,@AVEVAIssues
547
        ,@ProdReviewer
548
        ,@ProdIsResult
549
        ,@ProdRemarks
550
        ,@ClientReviewer
551
        ,@ClientIsResult
552
        ,@ClientRemarks
553
        ,@DTIsGateWay
554
        ,@DTIsImport
555
        ,@DTIsRegSystem
556
        ,@DTRemarks
557
    )
558
559
    if @@rowcount > 0
560
    begin
561
        select docid from @tbdoc
562
    end
563
    else
564
    begin
565
        select ''
566
    end
567
end";
568 482f6326 yoush97
                        }
569
                        else
570
                        {
571 54977253 yoush97
                            doc.ModifiedUser = userId;
572 482f6326 yoush97
                            query = $@"
573
update dbo.Documents
574 0a6036cb yoush97
set    RevisonNo=case when isnull(@RevisonNo,'')='' then '0' else @RevisonNo end
575 79d7836e yoush97
      ,System=@System
576
      ,SubSystemCode=@SubSystemCode
577 a8981c30 yoush97
      ,RefProjectCode=@RefProjectCode
578 54977253 yoush97
      ,JobLevel=@JobLevel
579 b2c03c2e yoush97
      ,Simularity=@Simularity
580 54977253 yoush97
      ,PersonInCharge=@PersonInCharge
581 62e3647c yoush97
      ,Worker=@Worker
582 54977253 yoush97
      ,ModifiedDate=getdate()
583
      ,ModifiedUser=@ModifiedUser
584
      ,ToIsDiscussion=@ToIsDiscussion
585
      ,ToRemarks=@ToRemarks
586
      ,ToCreator=@ToCreator
587
      ,FrReviewStatus=@FrReviewStatus
588
      ,FrRemarks=@FrRemarks
589
      ,FrCreator=@FrCreator
590
      ,ID2StartDate=@ID2StartDate
591
      ,ID2EndDate=@ID2EndDate
592
      ,ID2Status=@ID2Status
593
      ,ID2Issues=@ID2Issues
594 cb2e1138 yoush97
      ,ReplyModifications=@ReplyModifications
595
      ,ReplyRequester=@ReplyRequester
596
      ,IsConvert=@IsConvert
597
      ,AVEVAPersonInCharge=@AVEVAPersonInCharge
598
      ,AVEVAWorker=@AVEVAWorker
599 54977253 yoush97
      ,AVEVAConvertDate=@AVEVAConvertDate
600
      ,AVEVAReviewDate=@AVEVAReviewDate
601 ab3c1c74 yoush97
      ,AVEVAWorkDate=@AVEVAWorkDate
602 54977253 yoush97
      ,AVEVAStatus=@AVEVAStatus
603
      ,AVEVAIssues=@AVEVAIssues
604
      ,ProdReviewer=@ProdReviewer
605
      ,ProdIsResult=@ProdIsResult
606
      ,ProdRemarks=@ProdRemarks
607
      ,ClientReviewer=@ClientReviewer
608
      ,ClientIsResult=@ClientIsResult
609
      ,ClientRemarks=@ClientRemarks
610
      ,DTIsGateWay=@DTIsGateWay
611
      ,DTIsImport=@DTIsImport
612
      ,DTIsRegSystem=@DTIsRegSystem
613
      ,DTRemarks=@DTRemarks
614 b1591ae6 yoush97
where  DocID=@DocID
615
616
if @@rowcount > 0
617
begin
618
    select @DocID
619
end
620
else
621
begin
622
    select ''
623
end;";
624
                        }
625
                        string refID = base.QueryFirstOrDefault<string>(query, doc, transaction);
626
627
                        if (doc.AttFiles != null && doc.AttFiles.Count > 0)
628
                        {
629
                            string attDelIDList = string.Join("','", doc.AttFiles.Where(x => !string.IsNullOrEmpty(x.FileID)).Select(x => x.FileID).ToList());
630
631
                            if (!string.IsNullOrEmpty(refID) && attDelIDList.Length > 0)
632
                            {
633
                                query = $@"
634
delete from dbo.AttachFIles
635
where  RefID=@RefID and FileID in ('{attDelIDList}');";
636
                                base.Execute(query, new { RefID = refID }, transaction);
637
                            }
638
639
                            foreach (AttFileInfo attFile in doc.AttFiles)
640
                            {
641 e5ade387 yoush97
                                if (string.IsNullOrEmpty(attFile.RefID))
642
                                {
643
                                    attFile.RefID = refID;
644
                                    attFile.Creator = userId;
645 b1591ae6 yoush97
646 e5ade387 yoush97
                                    query = $@"
647 b1591ae6 yoush97
insert into dbo.AttachFIles (FileID,RefID,Category,FileType,FileName,FilePath,FileExtension,FileData,Creator)
648
values
649
(
650
    lower(newid())
651
   ,@RefID
652
   ,@Category
653
   ,@FileType
654
   ,@FileName
655
   ,@FilePath
656
   ,@FileExtension
657
   ,@FileData
658
   ,@Creator
659
)";
660
661 e5ade387 yoush97
                                    base.Execute(query, attFile, transaction);
662
                                }
663 b1591ae6 yoush97
                            }
664 482f6326 yoush97
                        }
665
                    }
666
667
                    transaction.Commit();
668
                    isSuccess = true;
669
                }
670
            }
671
            catch (Exception ex)
672
            {
673
                throw ex;
674
            }
675
676
            return isSuccess;
677
        }
678 978488b0 yoush97
679
        public Documents SetDocumentDataField(Documents doc, string userId)
680
        {
681
            Documents resultData = null;
682
683
            try
684
            {
685
                using (var transaction = base.BeginTransaction())
686
                {
687
                    string query = string.Empty;
688
689
                    if (!string.IsNullOrEmpty(doc.DocID))
690
                    {
691
                        StringBuilder sbSet = new StringBuilder();
692
                        var parameters = new Dictionary<string, object>();
693
694
                        #region Update 할 목록
695
                        if (doc.ID2StartDate != null)
696
                        {
697 4b8d9ad9 yoush97
                            sbSet.Append(" ,ID2StartDate=(case when ID2StartDate is null then @DateTimeNow else ID2StartDate end) ");
698 978488b0 yoush97
                            parameters.Add("ID2StartDate", doc.ID2StartDate);
699
                        }
700 4b8d9ad9 yoush97
701 62e3647c yoush97
                        if (!string.IsNullOrEmpty(doc.Worker))
702 4b8d9ad9 yoush97
                        {
703
                            sbSet.Append(" ,Worker=@Worker ");
704
                            parameters.Add("Worker", doc.Worker);
705
                        }
706 978488b0 yoush97
                        #endregion
707
708
                        if (parameters.Count > 0)
709
                        {
710
                            sbSet.Append(" ,ModifiedUser=@ModifiedUser ");
711
                            parameters.Add("ModifiedUser", userId);
712
713 54977253 yoush97
                            parameters.Add("DocID", doc.DocID);
714
715 978488b0 yoush97
                            query = $@"
716
declare @DateTimeNow datetime
717
set @DateTimeNow = getdate()
718
719
update dbo.Documents
720
set    ModifiedDate=@DateTimeNow {sbSet}
721
where  [DocID]=@DocID
722
723
if @@rowcount > 0
724
begin
725 fe310cac yoush97
    select * from dbo.Documents where DocID=@DocID
726 978488b0 yoush97
end
727
else
728
begin
729 fe310cac yoush97
    select * from dbo.Documents where 1=2
730 978488b0 yoush97
end;";
731
                            resultData = base.QueryFirstOrDefault<Documents>(query, parameters, transaction);
732
                        }
733
                    }
734
735
                    transaction.Commit();
736
                }
737
            }
738
            catch (Exception ex)
739
            {
740
                throw ex;
741
            }
742
743
            return resultData;
744
        }
745 fe57f64a yoush97
746 54977253 yoush97
        public bool SetDocumentDatasField(List<Documents> docs, string userId)
747
        {
748
            bool isSuccess = false;
749
750
            try
751
            {
752
                using (var transaction = base.BeginTransaction())
753
                {
754
                    foreach (Documents doc in docs)
755
                    {
756
                        string query = string.Empty;
757
758
                        if (!string.IsNullOrEmpty(doc.DocID))
759
                        {
760
                            StringBuilder sbSet = new StringBuilder();
761
                            var parameters = new Dictionary<string, object>();
762
763
                            #region Update 할 목록
764
                            if (doc.ID2EndDate != null)
765
                            {
766
                                sbSet.Append(" ,ID2EndDate=@ID2EndDate ");
767
                                parameters.Add("ID2EndDate", doc.ID2EndDate);
768
                            }
769
                            #endregion
770
771
                            if (parameters.Count > 0)
772
                            {
773
                                sbSet.Append(" ,ModifiedUser=@ModifiedUser ");
774
                                parameters.Add("ModifiedUser", userId);
775
776
                                parameters.Add("DocID", doc.DocID);
777
778
                                query = $@"
779
update dbo.Documents
780
set    ModifiedDate=getdate() {sbSet}
781
where  [DocID]=@DocID;";
782 e458a996 taeseongkim
                                base.Execute(query, parameters, transaction);
783 54977253 yoush97
                            }
784
                        }
785
                    }
786
                    transaction.Commit();
787
                    isSuccess = true;
788
                }
789
            }
790
            catch (Exception ex)
791
            {
792
                throw ex;
793
            }
794
795
            return isSuccess;
796
        }
797
798 fe57f64a yoush97
799
        //ID2
800
        public IEnumerable<ID2Drawings> GetID2DrawingsByProject(ID2ProjectInfo id2Info)
801
        {
802 82705273 yoush97
            try
803
            {
804
                string query = $@"
805 fe57f64a yoush97
select @Name PROJECTNAME
806
      ,dw.[UID], dw.[NAME], left(dw.[NAME], len(dw.[NAME]) - dw.lastidx) DOCNAME
807
      ,case when rtrim(ltrim(isnull(convert(varchar, dw.[DATETIME]),''))) = '' then null else convert(datetime, convert(varchar, dw.[DATETIME])) end [DATETIME]
808
      ,dw.OCCUPIED, dw.[Image]
809
from
810
(
811
    select [UID], [NAME], [DATETIME], OCCUPIED, [Image], charindex('.', reverse([NAME])) lastidx
812
    from   dbo.Drawings
813
) dw;";
814 82705273 yoush97
                return Query<ID2Drawings>(query, id2Info);
815
            }
816
            catch (Exception ex)
817
            {
818
                throw ex;
819
            }
820 fe57f64a yoush97
        }
821 39938acd yoush97
822
823
        //Transactions
824
        public int GetTranKey(string userId, string projectID)
825
        {
826
            int result = -1;
827
828
            try
829
            {
830
                var dynamicParameters = new DynamicParameters();
831
832
                var parameters = new Dictionary<string, object>()
833
                {
834
                    { "UserId", userId },
835
                    { "ProjectID", projectID }
836
                };
837
838
                dynamicParameters.AddDynamicParams(parameters);
839
840
                using (var transaction = base.BeginTransaction())
841
                {
842
                    string query = $@"
843
--log master 입력
844
insert into dbo.Transactions (UserId, ProjectID)
845
values (@UserId, @ProjectID);
846
select scope_identity();";
847
848
                    result = base.ExecuteScalar<int>(query, dynamicParameters, transaction);
849
850
                    transaction.Commit();
851
                }
852
            }
853
            catch (Exception ex)
854
            {
855
                throw ex;
856
            }
857
858
            return result;
859
        }
860
861
        public bool SetTran(int seq, bool isMgt, bool isStart, int itemCount, int markusItemCount)
862
        {
863
            bool result = false;
864
865
            try
866
            {
867
                var dynamicParameters = new DynamicParameters();                
868
                var parameters = new Dictionary<string, object>()
869
                {
870
                    { "Seq", seq },
871
                    { "ItemCount", itemCount },
872
                    { "MarkusItemCount", markusItemCount }
873
                };
874
                dynamicParameters.AddDynamicParams(parameters);
875
                StringBuilder sbWhere = new StringBuilder();
876
                if (isMgt)
877
                {
878
                    if (isStart)
879
                    {
880
                        sbWhere.Append("StartDate=getdate(), ItemCount=@ItemCount");
881
                    }
882
                    else
883
                    {
884
                        sbWhere.Append("EndDate=getdate()");
885
                    }
886
                    
887
                }
888
                else
889
                {
890
                    if (isStart)
891
                    {
892
                        sbWhere.Append("MarkusStartDate=getdate(), MarkusItemCount=@MarkusItemCount");
893
                    }
894
                    else
895
                    {
896
                        sbWhere.Append("MarkusEndDate=getdate()");
897
                    }
898
                }
899
900
                using (var transaction = base.BeginTransaction())
901
                {
902
                    string query = $@"
903
update dbo.Transactions
904
set    {sbWhere}
905
where Seq=@Seq;";
906
                    base.Execute(query, dynamicParameters, transaction);
907
908
                    transaction.Commit();
909
910
                    result = true;
911
                }
912
            }
913
            catch (Exception ex)
914
            {
915
                throw ex;
916
            }
917
918
            return result;
919
        }
920
921
        public string GetTranData(int seq)
922
        {
923
            try
924
            {
925
                var dynamicParameters = new DynamicParameters();
926
                var parameters = new Dictionary<string, object>()
927
                {
928
                    { "Seq", seq }
929
                };
930
                dynamicParameters.AddDynamicParams(parameters);
931
932
                string query = $@"
933
select *
934
from   dbo.Transactions
935
where  Seq=@Seq";
936
937
                IEnumerable<dynamic> rows = base.Query<dynamic>(query, dynamicParameters);
938
939
                return this.ToSerializeData(rows);
940
            }
941
            catch (Exception ex)
942
            {
943
                throw ex;
944
            }
945
        }
946
947
        public string ToSerializeData(IEnumerable<dynamic> items)
948
        {
949
            List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
950
951
            if (items == null) return string.Empty;
952
            var datas = items.ToArray();
953
            if (datas.Length == 0) return string.Empty;
954
955
            foreach (var data in datas)
956
            {
957
                var dicItem = new Dictionary<string, object>();
958
                foreach (var pair in ((IDictionary<string, object>)data))
959
                {
960
                    dicItem.Add(pair.Key, pair.Value);
961
                }
962
                list.Add(dicItem);
963
            }
964
965
            return JsonConvert.SerializeObject(list);
966
        }
967
968
        public IEnumerable<Documents> GetTrDocuments(int seq)
969
        {
970
            var dynamicParameters = new DynamicParameters();
971
            StringBuilder sbWhere = new StringBuilder();
972
            var parameters = new Dictionary<string, object>()
973
            {
974
                { "Seq", seq }
975
            };
976
            dynamicParameters.AddDynamicParams(parameters);
977
978
            try
979
            {
980
                string query = $@"
981
declare @CreatedDate datetime
982
declare @EndDate datetime
983
declare @ProjectGroupID varchar(36)
984
985
select top 1 @CreatedDate=CreatedDate, @EndDate=EndDate, @ProjectGroupID=ProjectID
986
from   dbo.Transactions
987
where  Seq=@Seq
988
989 9d611a63 yoush97
--select RefProjectCode, DocumentNo
990
--from   dbo.Documents
991
--where  RefProjectCode in (select Code from dbo.Projects where ParentID=@ProjectGroupID)
992
--   and RegisteredDate between @CreatedDate and @EndDate
993
994 39938acd yoush97
select RefProjectCode, DocumentNo
995
from   dbo.Documents
996 416979ec yoush97
where  RefProjectCode is not null and ((RegisteredDate between @CreatedDate and @EndDate) or (ModifiedDate between @CreatedDate and @EndDate))";
997 39938acd yoush97
998
                if (parameters.Count > 0)
999
                {
1000
                    dynamicParameters.AddDynamicParams(parameters);
1001
                }
1002
1003
                return Query<Documents>(query, dynamicParameters);
1004
            }
1005
            catch (Exception ex)
1006
            {
1007
                throw ex;
1008
            }
1009
        }
1010 9096bc6d taeseongkim
1011
        public class MarkusConvert
1012
        {
1013
            public string PROJECT_NO { get; set; }
1014
            public string DOCUMENT_ID { get; set; }
1015
            public int STATUS { get; set; }
1016
        }
1017
1018 5898479a yoush97
    }
1019 d2d4f84b yoush97
}