hytos / ID2.Manager / ID2.Manager.Dapper / Repository / DocumentRepository.cs @ c0420a29
이력 | 보기 | 이력해설 | 다운로드 (13.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 | |||
| 9 | 5898479a | yoush97 | using ID2.Manager.Data.Models; |
| 10 | |||
| 11 | 8e373ccf | yoush97 | using Dapper; |
| 12 | |||
| 13 | 5898479a | yoush97 | namespace ID2.Manager.Dapper.Repository |
| 14 | {
|
||
| 15 | public class DocumentRepository : BaseRepository |
||
| 16 | {
|
||
| 17 | public DocumentRepository(string connectionStr) : base(connectionStr) { }
|
||
| 18 | |||
| 19 | 7066b8a9 | yoush97 | public IEnumerable<Documents> GetAllDocuments() |
| 20 | 5898479a | yoush97 | {
|
| 21 | 7066b8a9 | yoush97 | string query = $@" |
| 22 | 54977253 | yoush97 | select doc.* |
| 23 | 7066b8a9 | yoush97 | from dbo.Documents doc |
| 24 | where doc.IsDeleted=0 |
||
| 25 | order by doc.Seq;"; |
||
| 26 | return Query<Documents>(query); |
||
| 27 | } |
||
| 28 | |||
| 29 | public (IEnumerable<Documents> dwgs, int totalCnt) GetDocuments(string projectCode, string personIncharge, string jobLevel, string documentNo, string isToIsDiscussion, string isFrReviewStatus, string isID2Work, string id2Status, string avevaStatus, string prodIsResult, string clientIsResult) |
||
| 30 | {
|
||
| 31 | var dynamicParameters = new DynamicParameters(); |
||
| 32 | 54977253 | yoush97 | dynamicParameters.Add("Total", dbType: DbType.Int32, direction: ParameterDirection.Output);
|
| 33 | 7066b8a9 | yoush97 | |
| 34 | 8e373ccf | yoush97 | StringBuilder sbWhere = new StringBuilder(); |
| 35 | var parameters = new Dictionary<string, object>(); |
||
| 36 | 36a31d25 | yoush97 | if (!string.IsNullOrEmpty(projectCode)) |
| 37 | 8e373ccf | yoush97 | {
|
| 38 | 36a31d25 | yoush97 | sbWhere.Append(" and doc.RefProjectCode=@RefProjectCode ");
|
| 39 | parameters.Add("RefProjectCode", projectCode);
|
||
| 40 | 8e373ccf | yoush97 | } |
| 41 | if (!string.IsNullOrEmpty(personIncharge)) |
||
| 42 | {
|
||
| 43 | sbWhere.Append(" and doc.PersonInCharge=@PersonInCharge ");
|
||
| 44 | parameters.Add("PersonInCharge", personIncharge);
|
||
| 45 | } |
||
| 46 | 08ea0584 | yoush97 | if (!string.IsNullOrEmpty(jobLevel)) |
| 47 | {
|
||
| 48 | sbWhere.Append(" and doc.JobLevel=@JobLevel ");
|
||
| 49 | parameters.Add("JobLevel", jobLevel);
|
||
| 50 | } |
||
| 51 | 8e373ccf | yoush97 | if (!string.IsNullOrEmpty(documentNo)) |
| 52 | {
|
||
| 53 | sbWhere.Append(" and doc.DocumentNo like '%' + @DocumentNo +'%' ");
|
||
| 54 | parameters.Add("DocumentNo", documentNo);
|
||
| 55 | } |
||
| 56 | |||
| 57 | 296ffcbd | yoush97 | if (!string.IsNullOrEmpty(isToIsDiscussion)) |
| 58 | {
|
||
| 59 | sbWhere.Append(" and doc.ToIsDiscussion=@ToIsDiscussion ");
|
||
| 60 | parameters.Add("ToIsDiscussion", isToIsDiscussion);
|
||
| 61 | } |
||
| 62 | |||
| 63 | if (!string.IsNullOrEmpty(isFrReviewStatus)) |
||
| 64 | {
|
||
| 65 | sbWhere.Append(" and doc.FrReviewStatus=@FrReviewStatus ");
|
||
| 66 | parameters.Add("FrReviewStatus", isFrReviewStatus);
|
||
| 67 | } |
||
| 68 | |||
| 69 | 08ea0584 | yoush97 | if (!string.IsNullOrEmpty(isID2Work)) |
| 70 | {
|
||
| 71 | sbWhere.Append(" and doc.IsID2Work=@IsID2Work ");
|
||
| 72 | parameters.Add("IsID2Work", isID2Work);
|
||
| 73 | } |
||
| 74 | |||
| 75 | if (!string.IsNullOrEmpty(id2Status)) |
||
| 76 | {
|
||
| 77 | sbWhere.Append(" and doc.ID2Status=@ID2Status ");
|
||
| 78 | parameters.Add("ID2Status", id2Status);
|
||
| 79 | } |
||
| 80 | if (!string.IsNullOrEmpty(avevaStatus)) |
||
| 81 | {
|
||
| 82 | sbWhere.Append(" and doc.AVEVAStatus=@AVEVAStatus ");
|
||
| 83 | parameters.Add("AVEVAStatus", avevaStatus);
|
||
| 84 | } |
||
| 85 | |||
| 86 | if (!string.IsNullOrEmpty(prodIsResult)) |
||
| 87 | {
|
||
| 88 | sbWhere.Append(" and doc.ProdIsResult=@ProdIsResult ");
|
||
| 89 | parameters.Add("ProdIsResult", prodIsResult);
|
||
| 90 | } |
||
| 91 | if (!string.IsNullOrEmpty(clientIsResult)) |
||
| 92 | {
|
||
| 93 | sbWhere.Append(" and doc.ClientIsResult=@ClientIsResult ");
|
||
| 94 | parameters.Add("ClientIsResult", clientIsResult);
|
||
| 95 | } |
||
| 96 | |||
| 97 | 5898479a | yoush97 | string query = $@" |
| 98 | 54977253 | yoush97 | select doc.*, datediff(SECOND, doc.ID2StartDate, doc.ID2EndDate) as ID2JobTime, |
| 99 | 3c43044d | taeseongkim | |
| 100 | ( |
||
| 101 | e5c99b6a | yoush97 | select markus.* |
| 102 | 3c43044d | taeseongkim | from [markus_SEC].[dbo].[ViewMarkupData] markus where doc.DocumentNo = markus.DOCUMENT_ID |
| 103 | FOR JSON PATH |
||
| 104 | ) as MarkupText |
||
| 105 | 5898479a | yoush97 | from dbo.Documents doc |
| 106 | 8e373ccf | yoush97 | where doc.IsDeleted=0 {sbWhere}
|
| 107 | 7066b8a9 | yoush97 | order by doc.Seq |
| 108 | |||
| 109 | select @Total=count(*) from dbo.Documents |
||
| 110 | select @Total;"; |
||
| 111 | 8e373ccf | yoush97 | |
| 112 | if (parameters.Count > 0) |
||
| 113 | {
|
||
| 114 | 7066b8a9 | yoush97 | dynamicParameters.AddDynamicParams(parameters); |
| 115 | 8e373ccf | yoush97 | } |
| 116 | 5898479a | yoush97 | |
| 117 | 7066b8a9 | yoush97 | var ret = Query<Documents>(query, dynamicParameters); |
| 118 | |||
| 119 | int totalCount = dynamicParameters.Get<int>("Total");
|
||
| 120 | |||
| 121 | return (ret, totalCount); |
||
| 122 | 5898479a | yoush97 | } |
| 123 | 482f6326 | yoush97 | |
| 124 | 54977253 | yoush97 | public bool SetDocumentData(List<Documents> docList, List<Documents> delDocList, string userId) |
| 125 | 482f6326 | yoush97 | {
|
| 126 | bool isSuccess = false; |
||
| 127 | |||
| 128 | try |
||
| 129 | {
|
||
| 130 | d2d4f84b | yoush97 | using (var transaction = base.BeginTransaction()) |
| 131 | 482f6326 | yoush97 | {
|
| 132 | 709c1971 | yoush97 | string query = string.Empty; |
| 133 | |||
| 134 | if (delDocList.Count > 0) |
||
| 135 | 482f6326 | yoush97 | {
|
| 136 | 709c1971 | yoush97 | string docIDList = string.Join("','", delDocList.Where(x => !string.IsNullOrEmpty(x.DocID)).Select(x => x.DocID).ToList());
|
| 137 | |||
| 138 | if (docIDList.Length > 0) |
||
| 139 | {
|
||
| 140 | 53fde692 | yoush97 | query = $@" |
| 141 | 74aa670a | yoush97 | update dbo.Documents |
| 142 | set IsDeleted=1 |
||
| 143 | 54977253 | yoush97 | ,DeletedDate=getdate(), |
| 144 | ,DeletedUser=@DeletedUser |
||
| 145 | where DocID in ('{docIDList}');";
|
||
| 146 | base.Execute(query, new { DeletedUser = userId }, transaction);
|
||
| 147 | 709c1971 | yoush97 | } |
| 148 | } |
||
| 149 | 482f6326 | yoush97 | |
| 150 | foreach (Documents doc in docList) |
||
| 151 | {
|
||
| 152 | if (string.IsNullOrEmpty(doc.DocID)) |
||
| 153 | {
|
||
| 154 | 54977253 | yoush97 | doc.RegisteredUser = userId; |
| 155 | 482f6326 | yoush97 | query = $@" |
| 156 | insert into dbo.Documents |
||
| 157 | ( |
||
| 158 | 54977253 | yoush97 | DocID |
| 159 | ,DocumentNo |
||
| 160 | ,RevisonNo |
||
| 161 | ,RefProjectCode |
||
| 162 | ,DocFilePath |
||
| 163 | ,DocFileName |
||
| 164 | ,JobLevel |
||
| 165 | ,IsTypical |
||
| 166 | ,PersonInCharge |
||
| 167 | ,RegisteredDate |
||
| 168 | ,RegisteredUser |
||
| 169 | ,ToIsDiscussion |
||
| 170 | ,ToRemarks |
||
| 171 | ,ToCreator |
||
| 172 | ,ToCapture |
||
| 173 | ,ToIsMarkup |
||
| 174 | ,FrReviewStatus |
||
| 175 | ,FrRemarks |
||
| 176 | ,FrCreator |
||
| 177 | ,FrCapture |
||
| 178 | ,FrIsMarkup |
||
| 179 | ,IsID2Work |
||
| 180 | ,ID2Connection |
||
| 181 | ,ID2StartDate |
||
| 182 | ,ID2EndDate |
||
| 183 | ,ID2Status |
||
| 184 | ,ID2Issues |
||
| 185 | ,AVEVAConnection |
||
| 186 | ,AVEVAConvertDate |
||
| 187 | ,AVEVAReviewDate |
||
| 188 | ,AVEVAStatus |
||
| 189 | ,AVEVAIssues |
||
| 190 | ,ReviewFilePath |
||
| 191 | ,ReviewFileName |
||
| 192 | ,ProdReviewer |
||
| 193 | ,ProdIsResult |
||
| 194 | ,ProdRemarks |
||
| 195 | ,ClientReviewer |
||
| 196 | ,ClientIsResult |
||
| 197 | ,ClientRemarks |
||
| 198 | ,DTIsGateWay |
||
| 199 | ,DTIsImport |
||
| 200 | ,DTIsRegSystem |
||
| 201 | ,DTRemarks |
||
| 202 | 482f6326 | yoush97 | ) |
| 203 | values |
||
| 204 | ( |
||
| 205 | lower(newid()) |
||
| 206 | ,@DocumentNo |
||
| 207 | ,@RevisonNo |
||
| 208 | 36a31d25 | yoush97 | ,@RefProjectCode |
| 209 | 482f6326 | yoush97 | ,@DocFilePath |
| 210 | ,@DocFileName |
||
| 211 | ,@JobLevel |
||
| 212 | ,@IsTypical |
||
| 213 | ,@PersonInCharge |
||
| 214 | 54977253 | yoush97 | ,getdate() |
| 215 | ,@RegisteredUser |
||
| 216 | 482f6326 | yoush97 | ,@ToIsDiscussion |
| 217 | ,@ToRemarks |
||
| 218 | ,@ToCreator |
||
| 219 | 00d11333 | yoush97 | ,@ToCapture |
| 220 | 482f6326 | yoush97 | ,@ToIsMarkup |
| 221 | ,@FrReviewStatus |
||
| 222 | ,@FrRemarks |
||
| 223 | ,@FrCreator |
||
| 224 | 00d11333 | yoush97 | ,@FrCapture |
| 225 | 482f6326 | yoush97 | ,@FrIsMarkup |
| 226 | ,@IsID2Work |
||
| 227 | ,@ID2Connection |
||
| 228 | ,@ID2StartDate |
||
| 229 | ,@ID2EndDate |
||
| 230 | ,@ID2Status |
||
| 231 | ,@ID2Issues |
||
| 232 | ,@AVEVAConnection |
||
| 233 | ,@AVEVAConvertDate |
||
| 234 | ,@AVEVAReviewDate |
||
| 235 | ,@AVEVAStatus |
||
| 236 | ,@AVEVAIssues |
||
| 237 | ,@ReviewFilePath |
||
| 238 | ,@ReviewFileName |
||
| 239 | ,@ProdReviewer |
||
| 240 | ,@ProdIsResult |
||
| 241 | ,@ProdRemarks |
||
| 242 | ,@ClientReviewer |
||
| 243 | ,@ClientIsResult |
||
| 244 | ,@ClientRemarks |
||
| 245 | ,@DTIsGateWay |
||
| 246 | ,@DTIsImport |
||
| 247 | ,@DTIsRegSystem |
||
| 248 | ,@DTRemarks |
||
| 249 | 709c1971 | yoush97 | );"; |
| 250 | 482f6326 | yoush97 | } |
| 251 | else |
||
| 252 | {
|
||
| 253 | 54977253 | yoush97 | doc.ModifiedUser = userId; |
| 254 | 482f6326 | yoush97 | query = $@" |
| 255 | update dbo.Documents |
||
| 256 | 54977253 | yoush97 | set DocumentNo=@DocumentNo |
| 257 | ,RevisonNo=@RevisonNo |
||
| 258 | ,RefProjectCode=@RefProjectCode |
||
| 259 | ,DocFilePath=@DocFilePath |
||
| 260 | ,DocFileName=@DocFileName |
||
| 261 | ,JobLevel=@JobLevel |
||
| 262 | ,IsTypical=@IsTypical |
||
| 263 | ,PersonInCharge=@PersonInCharge |
||
| 264 | ,ModifiedDate=getdate() |
||
| 265 | ,ModifiedUser=@ModifiedUser |
||
| 266 | ,ToIsDiscussion=@ToIsDiscussion |
||
| 267 | ,ToRemarks=@ToRemarks |
||
| 268 | ,ToCreator=@ToCreator |
||
| 269 | ,ToCapture=@ToCapture |
||
| 270 | ,ToIsMarkup=@ToIsMarkup |
||
| 271 | ,FrReviewStatus=@FrReviewStatus |
||
| 272 | ,FrRemarks=@FrRemarks |
||
| 273 | ,FrCreator=@FrCreator |
||
| 274 | ,FrCapture=@FrCapture |
||
| 275 | ,FrIsMarkup=@FrIsMarkup |
||
| 276 | ,IsID2Work=@IsID2Work |
||
| 277 | ,ID2Connection=@ID2Connection |
||
| 278 | ,ID2StartDate=@ID2StartDate |
||
| 279 | ,ID2EndDate=@ID2EndDate |
||
| 280 | ,ID2Status=@ID2Status |
||
| 281 | ,ID2Issues=@ID2Issues |
||
| 282 | ,AVEVAConnection=@AVEVAConnection |
||
| 283 | ,AVEVAConvertDate=@AVEVAConvertDate |
||
| 284 | ,AVEVAReviewDate=@AVEVAReviewDate |
||
| 285 | ,AVEVAStatus=@AVEVAStatus |
||
| 286 | ,AVEVAIssues=@AVEVAIssues |
||
| 287 | ,ReviewFilePath=@ReviewFilePath |
||
| 288 | ,ReviewFileName=@ReviewFileName |
||
| 289 | ,ProdReviewer=@ProdReviewer |
||
| 290 | ,ProdIsResult=@ProdIsResult |
||
| 291 | ,ProdRemarks=@ProdRemarks |
||
| 292 | ,ClientReviewer=@ClientReviewer |
||
| 293 | ,ClientIsResult=@ClientIsResult |
||
| 294 | ,ClientRemarks=@ClientRemarks |
||
| 295 | ,DTIsGateWay=@DTIsGateWay |
||
| 296 | ,DTIsImport=@DTIsImport |
||
| 297 | ,DTIsRegSystem=@DTIsRegSystem |
||
| 298 | ,DTRemarks=@DTRemarks |
||
| 299 | where DocID=@DocID;"; |
||
| 300 | 482f6326 | yoush97 | } |
| 301 | base.Execute(query, doc, transaction); |
||
| 302 | } |
||
| 303 | |||
| 304 | transaction.Commit(); |
||
| 305 | isSuccess = true; |
||
| 306 | } |
||
| 307 | } |
||
| 308 | catch (Exception ex) |
||
| 309 | {
|
||
| 310 | throw ex; |
||
| 311 | } |
||
| 312 | |||
| 313 | return isSuccess; |
||
| 314 | } |
||
| 315 | 978488b0 | yoush97 | |
| 316 | public Documents SetDocumentDataField(Documents doc, string userId) |
||
| 317 | {
|
||
| 318 | Documents resultData = null; |
||
| 319 | |||
| 320 | try |
||
| 321 | {
|
||
| 322 | using (var transaction = base.BeginTransaction()) |
||
| 323 | {
|
||
| 324 | string query = string.Empty; |
||
| 325 | |||
| 326 | if (!string.IsNullOrEmpty(doc.DocID)) |
||
| 327 | {
|
||
| 328 | StringBuilder sbSet = new StringBuilder(); |
||
| 329 | var parameters = new Dictionary<string, object>(); |
||
| 330 | |||
| 331 | #region Update 할 목록 |
||
| 332 | if (doc.ID2StartDate != null) |
||
| 333 | {
|
||
| 334 | 4b8d9ad9 | yoush97 | sbSet.Append(" ,ID2StartDate=(case when ID2StartDate is null then @DateTimeNow else ID2StartDate end) ");
|
| 335 | 978488b0 | yoush97 | parameters.Add("ID2StartDate", doc.ID2StartDate);
|
| 336 | } |
||
| 337 | 4b8d9ad9 | yoush97 | |
| 338 | if (doc.Worker != null) |
||
| 339 | {
|
||
| 340 | sbSet.Append(" ,Worker=@Worker ");
|
||
| 341 | parameters.Add("Worker", doc.Worker);
|
||
| 342 | } |
||
| 343 | 978488b0 | yoush97 | #endregion |
| 344 | |||
| 345 | if (parameters.Count > 0) |
||
| 346 | {
|
||
| 347 | sbSet.Append(" ,ModifiedUser=@ModifiedUser ");
|
||
| 348 | parameters.Add("ModifiedUser", userId);
|
||
| 349 | |||
| 350 | 54977253 | yoush97 | parameters.Add("DocID", doc.DocID);
|
| 351 | |||
| 352 | 978488b0 | yoush97 | query = $@" |
| 353 | declare @DateTimeNow datetime |
||
| 354 | set @DateTimeNow = getdate() |
||
| 355 | |||
| 356 | update dbo.Documents |
||
| 357 | set ModifiedDate=@DateTimeNow {sbSet}
|
||
| 358 | where [DocID]=@DocID |
||
| 359 | |||
| 360 | if @@rowcount > 0 |
||
| 361 | begin |
||
| 362 | c0420a29 | yoush97 | select *, datediff(SECOND, ID2StartDate, ID2EndDate) as ID2JobTime from dbo.Documents where DocID=@DocID |
| 363 | 978488b0 | yoush97 | end |
| 364 | else |
||
| 365 | begin |
||
| 366 | c0420a29 | yoush97 | select *, 0 as ID2JobTime from dbo.Documents where 1=2 |
| 367 | 978488b0 | yoush97 | end;"; |
| 368 | resultData = base.QueryFirstOrDefault<Documents>(query, parameters, transaction); |
||
| 369 | } |
||
| 370 | } |
||
| 371 | |||
| 372 | transaction.Commit(); |
||
| 373 | } |
||
| 374 | } |
||
| 375 | catch (Exception ex) |
||
| 376 | {
|
||
| 377 | throw ex; |
||
| 378 | } |
||
| 379 | |||
| 380 | return resultData; |
||
| 381 | } |
||
| 382 | fe57f64a | yoush97 | |
| 383 | 54977253 | yoush97 | public bool SetDocumentDatasField(List<Documents> docs, string userId) |
| 384 | {
|
||
| 385 | bool isSuccess = false; |
||
| 386 | |||
| 387 | try |
||
| 388 | {
|
||
| 389 | using (var transaction = base.BeginTransaction()) |
||
| 390 | {
|
||
| 391 | foreach (Documents doc in docs) |
||
| 392 | {
|
||
| 393 | string query = string.Empty; |
||
| 394 | |||
| 395 | if (!string.IsNullOrEmpty(doc.DocID)) |
||
| 396 | {
|
||
| 397 | StringBuilder sbSet = new StringBuilder(); |
||
| 398 | var parameters = new Dictionary<string, object>(); |
||
| 399 | |||
| 400 | #region Update 할 목록 |
||
| 401 | if (doc.ID2EndDate != null) |
||
| 402 | {
|
||
| 403 | sbSet.Append(" ,ID2EndDate=@ID2EndDate ");
|
||
| 404 | parameters.Add("ID2EndDate", doc.ID2EndDate);
|
||
| 405 | } |
||
| 406 | #endregion |
||
| 407 | |||
| 408 | if (parameters.Count > 0) |
||
| 409 | {
|
||
| 410 | sbSet.Append(" ,ModifiedUser=@ModifiedUser ");
|
||
| 411 | parameters.Add("ModifiedUser", userId);
|
||
| 412 | |||
| 413 | parameters.Add("DocID", doc.DocID);
|
||
| 414 | |||
| 415 | query = $@" |
||
| 416 | update dbo.Documents |
||
| 417 | set ModifiedDate=getdate() {sbSet}
|
||
| 418 | where [DocID]=@DocID;"; |
||
| 419 | base.Execute(query, parameters, transaction); |
||
| 420 | } |
||
| 421 | } |
||
| 422 | } |
||
| 423 | transaction.Commit(); |
||
| 424 | isSuccess = true; |
||
| 425 | } |
||
| 426 | } |
||
| 427 | catch (Exception ex) |
||
| 428 | {
|
||
| 429 | throw ex; |
||
| 430 | } |
||
| 431 | |||
| 432 | return isSuccess; |
||
| 433 | } |
||
| 434 | |||
| 435 | fe57f64a | yoush97 | |
| 436 | //ID2 |
||
| 437 | public IEnumerable<ID2Drawings> GetID2DrawingsByProject(ID2ProjectInfo id2Info) |
||
| 438 | {
|
||
| 439 | string query = $@" |
||
| 440 | select @Name PROJECTNAME |
||
| 441 | ,dw.[UID], dw.[NAME], left(dw.[NAME], len(dw.[NAME]) - dw.lastidx) DOCNAME |
||
| 442 | ,case when rtrim(ltrim(isnull(convert(varchar, dw.[DATETIME]),''))) = '' then null else convert(datetime, convert(varchar, dw.[DATETIME])) end [DATETIME] |
||
| 443 | ,dw.OCCUPIED, dw.[Image] |
||
| 444 | from |
||
| 445 | ( |
||
| 446 | select [UID], [NAME], [DATETIME], OCCUPIED, [Image], charindex('.', reverse([NAME])) lastidx
|
||
| 447 | from dbo.Drawings |
||
| 448 | ) dw;"; |
||
| 449 | return Query<ID2Drawings>(query, id2Info); |
||
| 450 | } |
||
| 451 | 5898479a | yoush97 | } |
| 452 | d2d4f84b | yoush97 | } |
