프로젝트

일반

사용자정보

통계
| 개정판:

hytos / ID2.Manager / ID2.Manager.Dapper / Repository / DocumentRepository.cs @ 3adabe82

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