PageRenderTime 54ms CodeModel.GetById 30ms RepoModel.GetById 0ms app.codeStats 0ms

/source/Bizio.Repositories/SqlProjectRepository.cs

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