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