hytos / ID2.Manager / ID2.Manager.Dapper / Repository / ProjectRepository.cs @ 31cd836b
이력 | 보기 | 이력해설 | 다운로드 (6.09 KB)
| 1 | cab8a4a1 | yoush97 | using System; |
|---|---|---|---|
| 2 | using System.Collections.Generic; |
||
| 3 | using System.Linq; |
||
| 4 | using System.Text; |
||
| 5 | using System.Threading.Tasks; |
||
| 6 | |||
| 7 | using ID2.Manager.Data.Models; |
||
| 8 | |||
| 9 | namespace ID2.Manager.Dapper.Repository |
||
| 10 | {
|
||
| 11 | public class ProjectRepository : BaseRepository |
||
| 12 | {
|
||
| 13 | public ProjectRepository(string connectionStr) : base(connectionStr) { }
|
||
| 14 | |||
| 15 | 87bcedb4 | yoush97 | public IEnumerable<ProjectInfo> GetAllProjectList() |
| 16 | {
|
||
| 17 | 82705273 | yoush97 | try |
| 18 | {
|
||
| 19 | string query = $@" |
||
| 20 | 87bcedb4 | yoush97 | ;with Prj as |
| 21 | ( |
||
| 22 | select ProjectID |
||
| 23 | ,Code |
||
| 24 | ,[Name] |
||
| 25 | ,isnull(Description,'') Description |
||
| 26 | ,isnull(ParentID,'') GroupID |
||
| 27 | ,convert(varchar(255),'') GroupName |
||
| 28 | a8981c30 | yoush97 | ,isnull(Team,'') Team |
| 29 | 87bcedb4 | yoush97 | ,1 [Level] |
| 30 | from dbo.Projects |
||
| 31 | where ParentID is null |
||
| 32 | |||
| 33 | UNION ALL |
||
| 34 | |||
| 35 | select p.ProjectID |
||
| 36 | ,p.Code |
||
| 37 | ,p.[Name] |
||
| 38 | ,p.Description |
||
| 39 | ,p.ParentID GroupID |
||
| 40 | ,pp.[Name] GroupName |
||
| 41 | a8981c30 | yoush97 | ,p.Team |
| 42 | 87bcedb4 | yoush97 | ,pp.[Level]+1 [Level] |
| 43 | FROM dbo.Projects p |
||
| 44 | inner join Prj pp ON p.ParentID=pp.ProjectID |
||
| 45 | ) |
||
| 46 | select * |
||
| 47 | from prj |
||
| 48 | order by [Level], [Name];"; |
||
| 49 | |||
| 50 | 82705273 | yoush97 | return Query<ProjectInfo>(query); |
| 51 | } |
||
| 52 | catch (Exception ex) |
||
| 53 | {
|
||
| 54 | throw ex; |
||
| 55 | } |
||
| 56 | cab8a4a1 | yoush97 | } |
| 57 | |||
| 58 | public ProjectInfo GetProjectInfo(string ProjectID) |
||
| 59 | {
|
||
| 60 | 82705273 | yoush97 | try |
| 61 | {
|
||
| 62 | string query = $@" |
||
| 63 | a23d0a0c | yoush97 | ;with Prj as |
| 64 | ( |
||
| 65 | select ProjectID |
||
| 66 | ,Code |
||
| 67 | ,[Name] |
||
| 68 | ,isnull(Description,'') Description |
||
| 69 | ,isnull(ParentID,'') GroupID |
||
| 70 | ,convert(varchar(255),'') GroupName |
||
| 71 | a8981c30 | yoush97 | ,isnull(Team,'') Team |
| 72 | a23d0a0c | yoush97 | ,1 [Level] |
| 73 | from dbo.Projects |
||
| 74 | where ParentID is null |
||
| 75 | |||
| 76 | UNION ALL |
||
| 77 | |||
| 78 | select p.ProjectID |
||
| 79 | ,p.Code |
||
| 80 | ,p.[Name] |
||
| 81 | ,p.Description |
||
| 82 | ,p.ParentID GroupID |
||
| 83 | ,pp.[Name] GroupName |
||
| 84 | a8981c30 | yoush97 | ,p.Team |
| 85 | a23d0a0c | yoush97 | ,pp.[Level]+1 [Level] |
| 86 | FROM dbo.Projects p |
||
| 87 | inner join Prj pp ON p.ParentID=pp.ProjectID |
||
| 88 | ) |
||
| 89 | select * |
||
| 90 | from prj |
||
| 91 | where ProjectID=@ProjectID"; |
||
| 92 | cab8a4a1 | yoush97 | |
| 93 | 82705273 | yoush97 | return Query<ProjectInfo>(query, new { ProjectID }).FirstOrDefault();
|
| 94 | } |
||
| 95 | catch (Exception ex) |
||
| 96 | {
|
||
| 97 | throw ex; |
||
| 98 | } |
||
| 99 | cab8a4a1 | yoush97 | } |
| 100 | a23d0a0c | yoush97 | |
| 101 | public bool SetProjectData(ProjectInfo projectInfo, List<ID2ProjectInfo> id2ProjectList) |
||
| 102 | {
|
||
| 103 | bool isSuccess = false; |
||
| 104 | |||
| 105 | try |
||
| 106 | {
|
||
| 107 | using (var transaction = base.BeginTransaction()) |
||
| 108 | {
|
||
| 109 | string query = string.Empty; |
||
| 110 | |||
| 111 | if (projectInfo.ProjectID == null) |
||
| 112 | {
|
||
| 113 | query = $@" |
||
| 114 | declare @newproject_uid table(projectId varchar(36)) |
||
| 115 | insert into dbo.Projects(ProjectID, Code, [Name], [Description]) |
||
| 116 | output inserted.ProjectID into @newproject_uid |
||
| 117 | values (lower(newid()), @Code, @Name, @Description) |
||
| 118 | if @@rowcount > 0 |
||
| 119 | begin |
||
| 120 | select projectId from @newproject_uid |
||
| 121 | end |
||
| 122 | else |
||
| 123 | begin |
||
| 124 | select '' projectId |
||
| 125 | end;"; |
||
| 126 | |||
| 127 | projectInfo.ProjectID = base.ExecuteScalar<string>(query, projectInfo, transaction); |
||
| 128 | } |
||
| 129 | else |
||
| 130 | {
|
||
| 131 | query = $@" |
||
| 132 | update dbo.Projects |
||
| 133 | set Code=@Code |
||
| 134 | ,[Name]=@Name |
||
| 135 | ,[Description]=@Description |
||
| 136 | where ProjectID=@ProjectID;"; |
||
| 137 | base.Execute(query, projectInfo, transaction); |
||
| 138 | } |
||
| 139 | |||
| 140 | if (id2ProjectList.Count > 0) |
||
| 141 | {
|
||
| 142 | var parameters = new Dictionary<string, object> |
||
| 143 | {
|
||
| 144 | { "ParentID", projectInfo.ProjectID }
|
||
| 145 | }; |
||
| 146 | |||
| 147 | string id2NameList = string.Join("','", id2ProjectList.Select(x => x.Name).ToList());
|
||
| 148 | |||
| 149 | if (id2NameList.Length > 0) |
||
| 150 | {
|
||
| 151 | query = $@"delete dbo.Projects where ParentID=@ParentID and [Code] not in ('{id2NameList}');";
|
||
| 152 | base.Execute(query, parameters, transaction); |
||
| 153 | } |
||
| 154 | } |
||
| 155 | |||
| 156 | foreach (ID2ProjectInfo prj in id2ProjectList) |
||
| 157 | {
|
||
| 158 | if (!string.IsNullOrEmpty(prj.Name)) |
||
| 159 | {
|
||
| 160 | var parameters = new Dictionary<string, object> |
||
| 161 | {
|
||
| 162 | { "ParentID", projectInfo.ProjectID },
|
||
| 163 | { "Name", prj.Name },
|
||
| 164 | a8981c30 | yoush97 | { "Desc", prj.Desc },
|
| 165 | { "Team", prj.Team }
|
||
| 166 | a23d0a0c | yoush97 | }; |
| 167 | |||
| 168 | query = $@" |
||
| 169 | if exists(select * from dbo.Projects where ParentID=@ParentID and Code=@Name) |
||
| 170 | begin |
||
| 171 | update dbo.Projects |
||
| 172 | set [Description]=@Desc |
||
| 173 | a8981c30 | yoush97 | ,Team=@Team |
| 174 | a23d0a0c | yoush97 | where ParentID=@ParentID and Code=@Name |
| 175 | end |
||
| 176 | else |
||
| 177 | begin |
||
| 178 | insert into dbo.Projects |
||
| 179 | ( |
||
| 180 | ProjectID |
||
| 181 | ,ParentID |
||
| 182 | ,Code |
||
| 183 | ,[Name] |
||
| 184 | ,[Description] |
||
| 185 | a8981c30 | yoush97 | ,Team |
| 186 | a23d0a0c | yoush97 | ) |
| 187 | values |
||
| 188 | ( |
||
| 189 | lower(newid()) |
||
| 190 | ,@ParentID |
||
| 191 | ,@Name |
||
| 192 | ,@Name |
||
| 193 | ,@Desc |
||
| 194 | a8981c30 | yoush97 | ,@Team |
| 195 | a23d0a0c | yoush97 | ) |
| 196 | end;"; |
||
| 197 | base.Execute(query, parameters, transaction); |
||
| 198 | } |
||
| 199 | } |
||
| 200 | |||
| 201 | transaction.Commit(); |
||
| 202 | isSuccess = true; |
||
| 203 | } |
||
| 204 | } |
||
| 205 | catch (Exception ex) |
||
| 206 | {
|
||
| 207 | throw ex; |
||
| 208 | } |
||
| 209 | |||
| 210 | return isSuccess; |
||
| 211 | } |
||
| 212 | |||
| 213 | public bool SetProjectGroupData(ProjectInfo projectInfo) |
||
| 214 | {
|
||
| 215 | bool isSuccess = false; |
||
| 216 | |||
| 217 | try |
||
| 218 | {
|
||
| 219 | using (var transaction = base.BeginTransaction()) |
||
| 220 | {
|
||
| 221 | string query = string.Empty; |
||
| 222 | |||
| 223 | query = $@" |
||
| 224 | delete dbo.Projects where ParentID=@ProjectID; |
||
| 225 | delete dbo.Projects where ProjectID=@ProjectID;"; |
||
| 226 | |||
| 227 | base.Execute(query, projectInfo, transaction); |
||
| 228 | |||
| 229 | transaction.Commit(); |
||
| 230 | isSuccess = true; |
||
| 231 | } |
||
| 232 | } |
||
| 233 | catch (Exception ex) |
||
| 234 | {
|
||
| 235 | throw ex; |
||
| 236 | } |
||
| 237 | |||
| 238 | return isSuccess; |
||
| 239 | } |
||
| 240 | cab8a4a1 | yoush97 | } |
| 241 | 8e373ccf | yoush97 | } |
