public static int CreateGameWithGameConfigurationEntry(string connectionString)
{
var idSelect = new SqlSelectStatement(connectionString);
idSelect.Top(1)
.Select("G.Id")
.From("dbo.BZ_Game", "G")
.Join("dbo.BZ_Game_Configuration", "GC", "GC.GameId = G.Id");
var id = idSelect.ExecuteScalar<int?>();
if (!id.HasValue)
{
var name = Utilities.GetRandomString(16);
var gameInsert = new SqlInsertStatement(connectionString);
gameInsert.InsertInto("dbo.BZ_Game")
.Column("CreatedBy", 1)
.Column("CreatedDate", DateTime.Now)
.Column("Name")
.Column("IndustryId", 1)
.Values()
.Value("Name", name);
gameInsert.Execute();
idSelect = new SqlSelectStatement(connectionString);
idSelect.Top(1)
.Select("Id")
.From("dbo.BZ_Game")
.WhereEquals("Name", name);
id = idSelect.ExecuteScalar<int?>();
var entryInsert = new SqlInsertStatement(connectionString);
entryInsert.InsertInto("dbo.BZ_Game_Configuration")
.Column("GameId", id.Value)
.Column("ConfigurationKeyId", 1)
.Column("Value")
.Values()
.Value("Value", "Test Value");
entryInsert.Execute();
}
return id.Value;
}
public virtual SqlStatement GetResultStatement()
{
return(Statement ?? (Statement = new SqlSelectStatement(SelectQuery)));
}
public override SqlStatement GetResultStatement()
{
return(Statement ?? (Statement = new SqlSelectStatement(SelectQuery)));
}
internal SqlSelectStatementFormatter(FormatterVisitor visitor, SqlSelectStatement codeObject)
: base(visitor, codeObject, false)
{
}
public static IEnumerable<Company> GenerateCompanies(string connectionString, int gameId, int numCompanies = -1)
{
var insert = new SqlInsertStatement(connectionString);
insert.InsertInto("dbo.BZ_Company")
.Column("GameId", gameId)
.Column("Name")
.Column("Money")
.Column("InitialAccuracy");
if(numCompanies < 0)
{
numCompanies = Utilities.GetRandomInt(1, 5);
}
for (var index = 0; index < numCompanies; index++)
{
insert.Values()
.Value("Name", Utilities.GetRandomString(16).Replace("'", "''"))
.Value("Money", Utilities.GetRandomDecimal())
.Value("InitialAccuracy", Utilities.GetRandomDecimal());
}
insert.Execute();
var sql = new SqlSelectStatement(connectionString);
sql.Select("Id")
.Select("Name")
.Select("Money")
.Select("InitialAccuracy")
.Select("UserId")
.From("dbo.BZ_Company")
.WhereEquals("GameId", gameId);
return sql.ExecuteComplex<Company>();
}
public static int GetGameId(string connectionString, string name)
{
var sql = new SqlInsertStatement(connectionString);
sql.InsertInto("dbo.BZ_Game")
.Column("CreatedBy", 1)
.Column("CreatedDate", DateTime.Now)
.Column("Name")
.Column("IndustryId", 1)
.Values()
.Value("Name", name);
sql.Execute();
var select = new SqlSelectStatement(connectionString);
select.Top(1)
.From("dbo.BZ_Game")
.Select("Id")
.WhereEquals("Name", name);
return select.ExecuteScalar<int>();
}
public static IEnumerable<ProjectDefinition> GetDBProjectDefinitions(string connectionString)
{
var output = new List<ProjectDefinition>();
using (var connection = new SqlConnection(connectionString))
{
var cmd = new SqlCommand("SELECT * FROM dbo.BZ_Project_Definition", connection);
connection.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var definitionId = (short)reader.GetInt("Id");
var skills = new List<ProjectDefinitionSkill>();
var skillCmd = new SqlCommand($"SELECT * FROM dbo.BZ_Project_Definition_Skill WHERE ProjectDefinitionId = {definitionId}", connection);
using (var skillReader = skillCmd.ExecuteReader())
{
while (skillReader.Read())
{
var skillId = skillReader.GetInt("SkillDefinitionId");
var min = skillReader.GetDecimal("Minimum");
var max = skillReader.GetDecimal("Maximum");
skills.Add(new ProjectDefinitionSkill
{
SkillDefinition = new SkillDefinition
{
Id = (byte)skillId
},
Value = new Range<decimal>(min, max)
});
}
}
var industries = new SqlSelectStatement(connectionString)
.Select("I.Id", "Id")
.Select("I.Name", "Name")
.From("dbo.BZ_Project_Definition_Industry", "PDI")
.Join("dbo.BZ_Industry", "I", "I.Id = PDI.IndustryId")
.WhereEquals("PDI.ProjectDefinitionId", reader.GetInt("Id"))
.ExecuteComplex<Industry>();
output.Add(new ProjectDefinition(industries, skills)
{
Description = reader.GetString("Description"),
Id = definitionId,
Name = reader.GetString("Name"),
ProjectLength = new Range<int>(reader.GetInt("MinimumProjectLength"), reader.GetInt("MaximumProjectLength")),
Value = new Range<decimal>(reader.GetDecimal("MinimumValue"), reader.GetDecimal("MaximumValue"))
});
}
}
}
return output;
}
public static IEnumerable<Personality> GetDBPersonalities(string connectionString)
{
IEnumerable<Personality> output = null;
var sql = new SqlSelectStatement(connectionString);
sql.Select("Name")
.Select("Id")
.From("dbo.BZ_Personality");
output = sql.ExecuteComplex<Personality>();
return output;
}
public static ProjectServiceData GenerateProjectServiceData(string connectionString, int projectCount)
{
var projects = new List<Project>();
var definitions = new SqlSelectStatement(connectionString)
.Select("Id")
.From("dbo.BZ_Project_Definition")
.Execute<short>();
while (projectCount-- > 0)
{
var definition = new ProjectDefinition
{
Id = definitions.Random()
};
var dbRequirements = new SqlSelectStatement(connectionString)
.Select("ProjectDefinitionId")
.Select("SkillDefinitionId")
.Select("Minimum")
.Select("Maximum")
.From("dbo.BZ_Project_Definition_Skill")
.WhereEquals("ProjectDefinitionId", definition.Id)
.ExecuteData();
var requirements = new List<ProjectRequirement>();
foreach (var dbRequirement in dbRequirements)
{
requirements.Add(new ProjectRequirement
{
SkillDefinition = new SkillDefinition
{
Id = (byte)dbRequirement["SkillDefinitionId"]
},
TargetValue = Utilities.GetRandomDecimal((decimal)dbRequirement["Minimum"], (decimal)dbRequirement["Maximum"])
});
}
var project = new Project(requirements)
{
Id = Utilities.InvalidId,
ReputationRequired = Utilities.GetValues<StarCategory>().Random(),
Value = Utilities.GetRandomDecimal(0, 200),
Deadline = Utilities.GetRandomDate(),
ExtensionDeadline = Utilities.GetRandomDate(),
Definition = definition
};
projects.Add(project);
}
return new ProjectServiceData
{
Projects = projects
};
}
public static IEnumerable<Project> GenerateProjects(string connectionString, int gameId, int count = 1)
{
var definitions = GetDBProjectDefinitions(connectionString);
var insert = new SqlInsertStatement(connectionString);
insert.InsertInto("dbo.BZ_Project")
.Column("GameId", gameId)
.Column("ProjectDefinitionId")
.Column("Value")
.Column("Deadline")
.Column("ReputationRequired");
while (count-- > 0)
{
var deadline = Utilities.GetRandomDate(DateTime.Today, DateTime.Today.AddYears(1));
var requiredReputation = (int)Utilities.GetValues<StarCategory>().Random();
var value = Utilities.GetRandomDecimal(10, 1000);
insert.Values()
.Value("ProjectDefinitionId", definitions.Random().Id)
.Value("Value", value)
.Value("Deadline", deadline)
.Value("ReputationRequired", requiredReputation);
}
insert.Execute();
var projectSelect = new SqlSelectStatement(connectionString);
projectSelect.Select("Id")
.Select("GameId")
.Select("ProjectDefinitionId")
.Select("Value")
.Select("Deadline")
.Select("ReputationRequired")
.From("dbo.BZ_Project")
.WhereEquals("GameId", gameId);
var pData = projectSelect.ExecuteData();
var skillIds = new SqlSelectStatement(connectionString)
.Select("Id")
.From("dbo.BZ_Skill_Definition")
.Execute<int>();
var requirementInsert = new SqlInsertStatement(connectionString);
requirementInsert.InsertInto("dbo.BZ_Project_Requirement")
.Column("ProjectId")
.Column("SkillDefinitionId")
.Column("TargetValue")
.Column("CurrentValue", 0.0m);
var output = new List<Project>();
var anyHasRequirements = false;
foreach (var item in pData)
{
var id = (int)item["Id"];
var definitionId = (short)item["ProjectDefinitionId"];
var definition = definitions.FirstOrDefault(d => d.Id == definitionId);
var requirements = new List<ProjectRequirement>();
foreach (var skill in definition.Skills)
{
anyHasRequirements = true;
var targetValue = Utilities.GetRanged(skill.Value, 2);
requirements.Add(new ProjectRequirement
{
SkillDefinition = skill.SkillDefinition,
CurrentValue = 0,
TargetValue = targetValue
});
requirementInsert.Values()
.Value("ProjectId", id)
.Value("SkillDefinitionId", skill.SkillDefinition.Id)
.Value("TargetValue", targetValue);
}
output.Add(new Project(requirements)
{
Id = id,
Value = (decimal)item["Value"],
Deadline = (DateTime)item["Deadline"],
ReputationRequired = (StarCategory)(int)item["ReputationRequired"],
Definition = definition
});
}
if (anyHasRequirements)
{
requirementInsert.Execute();
}
return output;
}
public static IEnumerable<Person> GeneratePeople(string connectionString, int gameId, int count = 1)
{
var personInsert = new SqlInsertStatement(connectionString)
.InsertInto("dbo.BZ_Person")
.Column("GameId", gameId)
.Column("GenderId")
.Column("FirstName")
.Column("LastName")
.Column("Birthday")
.Column("RetirementDate")
.Column("PersonalityId", 1);
for (var index = 0; index < count; index++)
{
var gender = (int)Utilities.GetRandomValue(Gender.None);
var first = Utilities.GetRandomString(16).Replace("'", "''");
var last = Utilities.GetRandomString(16).Replace("'", "''");
var birthday = Utilities.GetRandomDate();
var retirement = birthday.AddYears(Utilities.GetRandomInt(65, 85));
var intensity = Utilities.GetRandomDecimal(0, 1);
personInsert.Values()
.Value("GenderId", gender)
.Value("FirstName", first)
.Value("LastName", last)
.Value("Birthday", birthday)
.Value("RetirementDate", retirement);
}
personInsert.Execute();
var select = new SqlSelectStatement(connectionString);
select.Select("Id")
.Select("GenderId")
.Select("FirstName")
.Select("LastName")
.Select("Birthday")
.From("BZ_Person")
.WhereEquals("GameId", gameId);
var output = select.ExecuteComplex<Person>();
var skillIds = new SqlSelectStatement(connectionString)
.Select("Id")
.From("dbo.BZ_Skill_Definition")
.Execute<int>();
var skillInsert = new SqlInsertStatement(connectionString)
.InsertInto("dbo.BZ_Person_Skill")
.Column("PersonId")
.Column("SkillDefinitionId")
.Column("Value")
.Column("LearnRate")
.Column("ForgetRate");
var skillsProperty = typeof(Person).GetProperty("Skills");
foreach (var person in output)
{
var remainingSkillIds = new List<int>(skillIds);
var skills = new List<Skill>();
var skillCount = Utilities.GetRandomInt(1, remainingSkillIds.Count);
while (skillCount-- > 0)
{
var skill = new Skill
{
ForgetRate = Utilities.GetRandomDecimal(0, 10),
LearnRate = Utilities.GetRandomDecimal(0, 10),
Value = Utilities.GetRandomDecimal(0, 255),
SkillDefinition = new SkillDefinition
{
Id = (byte)remainingSkillIds.Random()
}
};
remainingSkillIds.Remove(skill.SkillDefinition.Id);
skillInsert.Values()
.Value("PersonId", person.Id)
.Value("SkillDefinitionId", skill.SkillDefinition.Id)
.Value("Value", skill.Value)
.Value("LearnRate", skill.LearnRate)
.Value("ForgetRate", skill.ForgetRate);
skills.Add(skill);
}
skillsProperty.SetValue(person, skills);
}
skillInsert.Execute();
return output;
}
public override void Visit(SqlSelectStatement codeObject)
{
Format(codeObject);
}
