private MySqlCommand play_countsInsertCommand(DateTime nowTime, Practices practice, List <Users> allUsers)
{
// play_counts挿入用SQL
List <Users> playCountsInsertUsers = allUsers.Where(user => user.count == null).ToList();
int play_countsInsertCount = playCountsInsertUsers.Count();
if (play_countsInsertCount == 0)
{
return(null);
}
List <MySqlParameter> play_countsSqlParameter = new List <MySqlParameter>();
StringBuilder play_countsInsertBuilder = new StringBuilder();
for (int i = 0; i < play_countsInsertCount; i++)
{
play_countsInsertBuilder.Append("INSERT INTO rmaster.play_counts ");
play_countsInsertBuilder.Append("(practice_id, user_id, count, created_at, updated_at) ");
play_countsInsertBuilder.Append(string.Format("VALUES (@practice_id_{0}, @user_id_{0}, @count_{0}, @created_at_{0}, @updated_at{0} ); ", i));
play_countsSqlParameter.Add(new MySqlParameter("practice_id_" + i, practice.id));
play_countsSqlParameter.Add(new MySqlParameter("user_id_" + i, playCountsInsertUsers[i].id));
play_countsSqlParameter.Add(new MySqlParameter("count_" + i, 1));
play_countsSqlParameter.Add(new MySqlParameter("created_at_" + i, nowTime));
play_countsSqlParameter.Add(new MySqlParameter("updated_at" + i, nowTime));
}
MySqlCommand play_countsInsertCommand = new MySqlCommand(play_countsInsertBuilder.ToString());
play_countsInsertCommand.Parameters.AddRange(play_countsSqlParameter.ToArray());
return(play_countsInsertCommand);
}
/// <summary>
/// Initialises a new instance of the <see cref="PrescriptionsService" /> class.
/// </summary>
/// <param name="practices">The practices.</param>
/// <param name="prescriptionsReader">The prescriptions reader.</param>
/// <param name="queryFactory">The query factory.</param>
public PrescriptionsService(
Practices practices,
IPrescriptionsCsvReader prescriptionsReader,
IPrescriptionsQueryFactory queryFactory)
{
// Set objects
this.practices = practices;
this.prescriptionsReader = prescriptionsReader;
this.queryFactory = queryFactory;
}
public void saveData(List <Results> results, List <Users> gamePlayers, Practices practice, List <Users> allUsers)
{
// データ更新用現在時刻
DateTime now = DateTime.Now;
using (MySqlConnection connection = new MySqlConnection(Static.BUILDER.ConnectionString))
{
MySqlTransaction transaction = null;
try
{
connection.Open();
transaction = connection.BeginTransaction();
MySqlCommand matchesInsertCommand = this.matchesInsertCommand(now, practice);
matchesInsertCommand.Connection = connection;
// matchesのInsertとID取得
int matchesId = (int)matchesInsertCommand.ExecuteScalar();
// resultへmatchesIdを設定
results.ForEach(obj => obj.match_id = matchesId);
MySqlCommand resultInsertCommand = this.resultsInsertCommand(now, results);
resultInsertCommand.Connection = connection;
resultInsertCommand.Transaction = transaction;
resultInsertCommand.ExecuteNonQuery();
// カウントは全ユーザーでプラス
MySqlCommand play_countsUpdateCommand = this.play_countsUpdateCommand(now, practice, allUsers);
play_countsUpdateCommand.Connection = connection;
resultInsertCommand.Transaction = transaction;
play_countsUpdateCommand.ExecuteNonQuery();
// Insertが不要の場合がある
MySqlCommand play_countsInsertCommand = this.play_countsInsertCommand(now, practice, allUsers);
if (play_countsInsertCommand != null)
{
play_countsInsertCommand.Connection = connection;
resultInsertCommand.Transaction = transaction;
play_countsInsertCommand.ExecuteNonQuery();
}
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
}
}
private MySqlCommand play_countsUpdateCommand(DateTime nowTime, Practices practice, List <Users> allUsers)
{
// play_counts更新用SQL
StringBuilder play_countsUpdateBuilder = new StringBuilder();
play_countsUpdateBuilder.Append("UPDATE rmaster.play_counts ");
play_countsUpdateBuilder.Append("SET count = count + 1, updated_at = @updated_at ");
play_countsUpdateBuilder.Append("WHERE practice_id = @practiceId ");
play_countsUpdateBuilder.Append("AND user_id in ( ");
play_countsUpdateBuilder.Append(string.Join(",", allUsers.Select(u => u.id).ToArray()));
play_countsUpdateBuilder.Append(") ");
MySqlCommand play_countsUpdateCommand = new MySqlCommand(play_countsUpdateBuilder.ToString());
play_countsUpdateCommand.Parameters.Add(new MySqlParameter("practiceId", practice.id));
play_countsUpdateCommand.Parameters.Add(new MySqlParameter("updated_at", nowTime));
return(play_countsUpdateCommand);
}
/// <summary>
/// matchesのInsert用コマンド
/// 加えてIDを取得する
/// </summary>
private MySqlCommand matchesInsertCommand(DateTime nowTime, Practices practice)
{
// matches 挿入用SQL作成
StringBuilder matchesBuilder = new StringBuilder();
matchesBuilder.Append("INSERT INTO rmaster.matches ");
matchesBuilder.Append("(practice_id, link, type, created_at, updated_at) ");
matchesBuilder.Append("VALUES (@practice_id, @link, @type, @created_at, @updated_at); ");
matchesBuilder.Append("SELECT MAX(id) FROM rmaster.matches WHERE practice_id = " + practice.id);
MySqlCommand matchesInsertCommand = new MySqlCommand(matchesBuilder.ToString());
matchesInsertCommand.Parameters.Add(new MySqlParameter("practice_id", practice.id));
matchesInsertCommand.Parameters.Add(new MySqlParameter("link", null));
matchesInsertCommand.Parameters.Add(new MySqlParameter("type", 2));
matchesInsertCommand.Parameters.Add(new MySqlParameter("created_at", nowTime));
matchesInsertCommand.Parameters.Add(new MySqlParameter("updated_at", nowTime));
return(matchesInsertCommand);
}
protected void PopulatePractices()
{
// Create test PostcodeRegions
var postcodeRegion1 = new PostcodeRegion
{
Postcode = Data.Postcode1,
Region = Data.RegionLondon
};
var postcodeRegion2 = new PostcodeRegion
{
Postcode = Data.Postcode2,
Region = Data.RegionNe
};
// Create dictionary, add the values
var dictionary = new ConcurrentDictionary<string, PostcodeRegion>();
dictionary.TryAdd(Data.PracticeCode1, postcodeRegion1);
dictionary.TryAdd(Data.PracticeCode2, postcodeRegion2);
// Return new Practices object
Practices = new Practices(dictionary);
}
/// <summary>
/// Calculates the total nic by code by region.
/// </summary>
/// <param name="bnfCode">The BNF code.</param>
/// <param name="region">The region.</param>
/// <param name="practices">The practices.</param>
/// <returns></returns>
public ICalcTotalNicByCodeByRegion CalcTotalNicByCodeByRegion(string bnfCode, Region region, Practices practices)
{
return new CalcTotalNicByCodeByRegion(bnfCode, region, practices);
}
/// <summary>
/// Calculates the total spend per postcode.
/// </summary>
/// <param name="practices">The practices.</param>
/// <returns></returns>
public ICalcTotalActCostPerPostcode CalcTotalSpendPerPostcode(Practices practices)
{
return new CalcTotalActCostPerPostcode(practices);
}
/// <summary>
/// Calculates the average cost by code by region.
/// </summary>
/// <param name="bnfCode">The BNF code.</param>
/// <param name="region">The region.</param>
/// <param name="practices">The practices.</param>
/// <returns></returns>
public ICalcAvgCostByCodeByRegion CalcAvgCostByCodeByRegion(string bnfCode, Region region, Practices practices)
{
return new CalcAvgActCostByCodeByRegion(bnfCode, region, practices);
}
public void RegisterModule(Practices.IoC.SimpleContainer container, Func<object> lifetimeScopeAccess)
{
container.RegisterPerLifetime(lifetimeScopeAccess, typeof (ITestSimpleDependency), null,
typeof (TestSimpleDependency));
}
/// <summary>
/// Gets all practices, along with their regions.
/// </summary>
private void InitialisePractices()
{
// Use the practices reader to obtain practice data
var practicesData = this.practicesReader.GetPracticeData();
// Get the regions for these practices (and validate the postcode exists)
var practicesDictionary = this.postcodesReader.GetPracticeDictionary(practicesData);
// Return practices object
Practices = new Practices(practicesDictionary);
}
public Practices Update(Practices practice)
{
GentuUow.RegisterDirty <Practices>(practice);
GentuUow.Commit();
return(practice);
}
public HashSet <string> UpdateValidation(Practices practice)
{
CommonValidation(practice);
return(ValidationMessages);
}
public Practices Add(Practices practice)
{
GentuUow.RegisterNew <Practices>(practice);
GentuUow.Commit();
return(practice);
}
protected BaseCalcTotalByCodeByRegion(string bnfCode, Region region, Practices practices)
{
this.bnfCode = bnfCode;
this.region = region;
this.practices = practices;
}
/// <summary>
/// Initialises a new instance of the <see cref="PrescriptionsService"/> class.
/// </summary>
public PrescriptionsService(Practices practices)
: this(practices, new PrescriptionsCsvReader(), new PrescriptionsQueryFactory())
{
}
public CalcTotalNicByCodeByRegion(string bnfCode, Region region, Practices practices)
: base(bnfCode, region, practices)
{
}
/// <summary>
/// Initialises a new instance of the <see cref="CalcAvgActCostByCodeByRegion"/> class.
/// </summary>
/// <param name="bnfCode">The BNF code.</param>
/// <param name="region">The region.</param>
/// <param name="practices">The practices.</param>
public CalcAvgActCostByCodeByRegion(string bnfCode, Region region, Practices practices) : base(bnfCode)
{
Region = region;
this.practices = practices;
}