/source/Bizio.Repositories/SqlProjectRepository.cs
C# | 233 lines | 205 code | 23 blank | 5 comment | 2 complexity | 9fafe036ddb5fdb44163e354350db767 MD5 | raw file
1using Bizio.Core.Data.Companies;
2using Bizio.Core.Data.People;
3using Bizio.Core.Data.Projects;
4using Bizio.Core.Repositories;
5using Bizio.Core.Repositories.Bases;
6using Bizio.Core.Services.Data;
7using Bizio.Core.Utilities;
8using BlackPlain.Sql;
9using Microsoft.Extensions.Options;
10using System;
11using System.Collections.Generic;
12using System.Data;
13using System.Linq;
14using System.Threading;
15using System.Threading.Tasks;
16
17namespace Bizio.Repositories
18{
19 public sealed class SqlProjectRepository : SqlRepositoryBase<BizioConnectionStringOptions>, IProjectRepository
20 {
21 public SqlProjectRepository(IOptions<BizioConnectionStringOptions> options) : base(options) { }
22
23 public async Task<IEnumerable<ProjectDefinition>> LoadProjectDefinitionsAsync(CancellationToken cancellationToken = default)
24 {
25 var output = new List<ProjectDefinition>();
26
27 var dbDefinitions = await Sql.BeginSelect()
28 .Select("Id")
29 .Select("Name")
30 .Select("Description")
31 .Select("MinimumValue")
32 .Select("MaximumValue")
33 .Select("MinimumProjectLength")
34 .Select("MaximumProjectLength")
35 .From("ProjectDefinition")
36 .ExecuteAsync(Connection, cancellationToken);
37
38 // TODO: flatten the queries below because they're
39 // being called a ton
40
41 // decided to do nested queries instead of joins
42 // because the size of the returned result set
43 // outweighs the speed of the queries
44 foreach(var dbDefinition in dbDefinitions)
45 {
46 var projectDefinitionId = dbDefinition["Id"].AsShort();
47
48 var getProjectSkillsTask = Sql.BeginSelect()
49 .Select("SkillDefinitionId")
50 .Select("Minimum")
51 .Select("Maximum")
52 .Select("IsRequired")
53 .From("ProjectDefinitionSkill")
54 .Where("ProjectDefinitionId = @ProjectDefinitionId")
55 .AddParameter("@ProjectDefinitionId", projectDefinitionId)
56 .ExecuteAsync(Connection, cancellationToken);
57
58 var getIndustriesTask = Sql.BeginSelect()
59 .Select("IndustryId")
60 .From("ProjectDefinitionIndustry")
61 .Where("ProjectDefinitionId = @ProjectDefinitionId")
62 .AddParameter("@ProjectDefinitionId", projectDefinitionId)
63 .ExecuteAsync(Connection, cancellationToken);
64
65 var skills = (await getProjectSkillsTask)
66 .Select(s => new ProjectDefinitionSkill
67 {
68 SkillDefinition = new SkillDefinition
69 {
70 Id = s["SkillDefinitionId"].AsByte()
71 },
72 Value = new Range<decimal>(s["Minimum"].AsShort(), s["Maximum"].AsShort()),
73 IsRequired = s["IsRequired"].AsBool()
74 })
75 .ToList();
76
77 var industries = (await getIndustriesTask)
78 .Select(i => new Industry
79 {
80 Id = i["IndustryId"].AsByte()
81 })
82 .ToList();
83
84 output.Add(new ProjectDefinition(industries, skills)
85 {
86 Id = projectDefinitionId,
87 Name = dbDefinition["Name"].AsString(),
88 Description = dbDefinition["Description"].AsString(),
89 Value = new Range<decimal>(dbDefinition["MinimumValue"].AsInt(), dbDefinition["MaximumValue"].AsInt()),
90 ProjectLength = new Range<byte>(dbDefinition["MinimumProjectLength"].AsByte(), dbDefinition["MaximumProjectLength"].AsByte())
91 });
92 }
93
94 return output;
95 }
96
97 public void SaveGame(int gameId, ProjectServiceData data)
98 {
99 if(!data.Projects.Any())
100 {
101 return;
102 }
103
104 using (var transaction = BeginTransaction())
105 {
106 Sql.BeginMerge<Project>()
107 .Using(data.Projects, "SOURCE")
108 .Value("Id", SqlDbType.Int, p => p.Id)
109 .Value("ProjectDefinitionId", SqlDbType.SmallInt, p => p.Definition.Id)
110 .Value("Value", SqlDbType.Int, p => (int)p.Value)
111 .Value("Deadline", SqlDbType.Date, p => p.Deadline)
112 .Value("ExtensionDeadline", SqlDbType.Date, p => p.ExtensionDeadline)
113 .Value("ReputationRequired", SqlDbType.TinyInt, p => (byte)p.ReputationRequired)
114 .Value("StatusId", SqlDbType.TinyInt, p => (byte)p.Status)
115 .Value("ResultId", SqlDbType.TinyInt, p => (byte?)p.Result)
116 .EndValues()
117 .AddParameter("@GameId", gameId)
118 .Into("Project", "TARGET")
119 .On("TARGET.Id = SOURCE.Id")
120 .When().Matched().ThenUpdate()
121 .Set("TARGET.ExtensionDeadline = SOURCE.ExtensionDeadline")
122 .Set("TARGET.StatusId = SOURCE.StatusId")
123 .Set("TARGET.ResultId = SOURCE.ResultId")
124 .EndUpdate()
125 .When().NotMatched().ThenInsert()
126 .Insert("GameId", "@GameId")
127 .Insert("ProjectDefinitionId")
128 .Insert("Value")
129 .Insert("Deadline")
130 .Insert("ExtensionDeadline")
131 .Insert("ReputationRequired")
132 .Insert("StatusId")
133 .Insert("ResultId")
134 .EndInsert()
135 .Execute(Connection, transaction, AssignId);
136
137 var requirements = Flatten(data.Projects, p => p.Id, p => p.Requirements);
138
139 MergeInto("ProjectRequirement", requirements)
140 .Value("ProjectId", SqlDbType.Int, r => r.Id)
141 .Value("SkillDefinitionId", SqlDbType.TinyInt, r => r.Value.SkillDefinition.Id)
142 .Value("TargetValue", SqlDbType.Int, r => r.Value.TargetValue)
143 .Value("CurrentValue", SqlDbType.Decimal, 9, 2, r => r.Value.CurrentValue)
144 .EndValues()
145 .On("TARGET.ProjectId = SOURCE.ProjectId AND TARGET.SkillDefinitionId = SOURCE.SkillDefinitionId")
146 .When().Matched().ThenUpdate()
147 .Set("TARGET.CurrentValue = SOURCE.CurrentValue")
148 .EndUpdate()
149 .When().NotMatchedByTarget().ThenInsert()
150 .Insert("ProjectId")
151 .Insert("SkillDefinitionId")
152 .Insert("TargetValue")
153 .Insert("CurrentValue")
154 .EndInsert()
155 .Execute(Connection, transaction);
156
157 transaction.Commit();
158 }
159 }
160
161 public ProjectServiceData LoadGame(int userId, int gameId)
162 {
163 var requirements = Sql.BeginSelect()
164 .Select("pr.ProjectId")
165 .Select("pr.SkillDefinitionId")
166 .Select("pr.TargetValue")
167 .Select("pr.CurrentValue")
168 .From("ProjectRequirement", "pr")
169 .Join("Project", "p")
170 .On("p.Id = pr.ProjectId AND p.GameId = @GameId")
171 .AddParameter("@GameId", gameId)
172 .Execute(Connection, null)
173 .GroupBy(d => d["ProjectId"].AsInt())
174 .Select(g => Pair(g.Key, g.Select(r => new ProjectRequirement
175 {
176 SkillDefinition = new SkillDefinition
177 {
178 Id = r["SkillDefinitionId"].AsByte()
179 },
180 TargetValue = r["TargetValue"].AsInt(),
181 CurrentValue = r["CurrentValue"].AsDecimal()
182 }).ToList()));
183
184 var dbProjects = Sql.BeginSelect()
185 .Select("p.Id")
186 .Select("p.ProjectDefinitionId")
187 .Select("p.Value")
188 .Select("p.Deadline")
189 .Select("p.ExtensionDeadline")
190 .Select("p.ReputationRequired")
191 .Select("p.StatusId")
192 .Select("p.ResultId")
193 .From("Project", "p")
194 .LeftJoin("CompanyProject", "cp")
195 .On("cp.ProjectId = p.Id")
196 .Join("GameClock", "gc")
197 .On("gc.GameId = p.GameId")
198 .Where("p.GameId = @GameId")
199 .Where("ISNULL(p.ExtensionDeadline, p.Deadline) > DATEADD(DAY, 7 * gc.WeeksPerTurn * (gc.CurrentTurn - 1), gc.StartDate) OR cp.CompanyId IS NOT NULL")
200 .AddParameter("@GameId", gameId)
201 .Execute(Connection);
202
203 var projects = new List<Project>();
204
205 foreach(var dbProject in dbProjects)
206 {
207 var projectId = dbProject["Id"].AsInt();
208
209 var projectRequirements = requirements.First(r => r.Id == projectId);
210
211 projects.Add(new Project(projectRequirements.Value)
212 {
213 Id = projectId,
214 Definition = new ProjectDefinition
215 {
216 Id = dbProject["ProjectDefinitionId"].AsShort()
217 },
218 Value = dbProject["Value"].AsInt(),
219 Deadline = dbProject["Deadline"].AsDateTime(),
220 ExtensionDeadline = dbProject["ExtensionDeadline"].As<DateTime?>(),
221 ReputationRequired = (StarCategory)dbProject["ReputationRequired"].AsByte(),
222 Status = (ProjectStatus)dbProject["StatusId"].AsByte(),
223 Result = (ProjectResult?)dbProject["ResultId"].As<byte?>(),
224 });
225 }
226
227 return new ProjectServiceData
228 {
229 Projects = projects
230 };
231 }
232 }
233}