public void UpdatePersonStepRank(List <PersonStepRank> allPersonRank)
{
var conn = XiaoMiData.GetConnectstr();
var updateString = "begin transaction delete from Innocellence_GSK_WeChat_HM_PersonStepRank {0} commit transaction";
var insertSql = allPersonRank.Aggregate("", (current, stepRank) => current + createInsertPersonStepRankStr(stepRank.WechatId, stepRank.WechatName, stepRank.RankByDay, stepRank.StepsByDay, stepRank.Rank, stepRank.Steps));
SqlHelper.ExecuteNonQuery(conn, CommandType.Text, string.Format(updateString, insertSql));
}
public void UpdateGroupRank(List <GroupScoreRank> group)
{
var conn = XiaoMiData.GetConnectstr();
var sql = "begin transaction delete from Innocellence_GSK_WeChat_HM_GroupScoreRank {0} commit transaction";
var insertSql = string.Format(sql, group.Aggregate("", (current, currentGroup) => current + createInsertGroupRankStr(currentGroup)));
SqlHelper.ExecuteNonQuery(conn, CommandType.Text, string.Format(sql, insertSql));
}
public List <MetaData> GetAllMetaData(DateTime fromDate, DateTime toDate)
{
var conn = XiaoMiData.GetConnectstr();
var sql = @"SELECT * From Innocellence_GSK_WeChat_HM_MetaData where CreatedDate>='" + fromDate.Date + "' and CreatedDate<='" + toDate.Date + "'";
var data = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql).Tables[0];
var result = SqlHelper.ConvertTo <MetaData>(data).ToList();
return(result);
}
public List <MetaData> GetAllMetaData()
{
var conn = XiaoMiData.GetConnectstr();
var sql = @"SELECT * From Innocellence_GSK_WeChat_HM_MetaData";
var data = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql).Tables[0];
var result = SqlHelper.ConvertTo <MetaData>(data).ToList();
return(result);
}
public void dealRewardMetadata(List <MetaData> allMetadata)
{
var sql = @"select * from Innocellence_GSK_WeChat_HM_Score";
var conn = XiaoMiData.GetConnectstr();
var data = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql).Tables[0];
var result = SqlHelper.ConvertTo <Score>(data).ToList();
foreach (var score in result)
{
var traget = allMetadata.Find(string.Compare(m => m.WechatId.ToLower(), score.WechatId.ToLower(), true) == 0 && DateTime.Compare(m.CreatedDate.Date, score.CreatedDate.Date) == 0);
if (traget != null)
{
traget.Score += score.score;
}
}
}
private Dictionary <string, int> getGroupNameAndCountMap()
{
var result = new Dictionary <string, int>();
var conn = XiaoMiData.GetConnectstr();
var sql = @"select GroupName,count(GroupName) from Innocellence_GSK_WeChat_HM_GroupInfo group by GroupName";
using (SqlConnection connection = new SqlConnection(conn))
{
connection.Open();
var data = SqlHelper.ExecuteReader(connection, CommandType.Text, sql);
while (data.Read())
{
result[data.GetString(0)] = data.GetInt32(1);
}
return(result);
}
}
private Dictionary <string, string> getGroupAndUserMap()
{
var result = new Dictionary <string, string>(StringComparer.OrdinalIgnoreCase);
var conn = XiaoMiData.GetConnectstr();
var sql = @"select Account,GroupName from Innocellence_GSK_WeChat_HM_GroupInfo";
using (SqlConnection connection = new SqlConnection(conn))
{
connection.Open();
var data = SqlHelper.ExecuteReader(connection, CommandType.Text, sql);
while (data.Read())
{
result[data.GetString(0)] = data.GetString(1);
}
return(result);
}
}
public int GetPersonStepRankByWeChatId(string WechatId)
{
var result = 0;
var conn = XiaoMiData.GetConnectstr();
var sqlString = "select stepRank from (select WechatId,rank() over (order by steps desc)AS stepRank from Innocellence_GSK_WeChat_HM_MetaData where CreatedDate='{0}') as rankTable where WechatId='{1}'";
using (SqlConnection connection = new SqlConnection(conn))
{
connection.Open();
var data = SqlHelper.ExecuteReader(connection, CommandType.Text, string.Format(sqlString, DateTime.Now.Date, WechatId));
while (data.Read())
{
result = (int)data.GetInt64(0);
}
return(result == 0 ? -1 : result);
}
}
public static Dictionary <string, string> GetWeChatNameById()
{
var conn = XiaoMiData.GetConnectstr();
var sql = @"SELECT WechatId,WechatName FROM Innocellence_GSK_WeChat_HM_Setting";
using (SqlConnection connection = new SqlConnection(conn))
{
connection.Open();
var data = SqlHelper.ExecuteReader(connection, CommandType.Text, sql);
var result = new Dictionary <string, string>(StringComparer.OrdinalIgnoreCase);
while (data.Read())
{
result[data.GetString(0)] = data.GetString(1);
}
return(result);
}
}
public void UpdateMetaData(Setting personSetting, DataResponse xiaoMiData)
{
var conn = XiaoMiData.GetConnectstr();
var selectString = "select * from Innocellence_GSK_WeChat_HM_MetaData where WechatId='{0}'and CreatedDate='{1}' ";
var updateString = "update Innocellence_GSK_WeChat_HM_MetaData set Steps={0},Score={1} where WechatId='{2}'and CreatedDate='{3}'";
var createString = "insert into Innocellence_GSK_WeChat_HM_MetaData(WechatId,CreatedDate,Steps,Score) values ('{0}','{1}',{2},{3})";
var data = SqlHelper.ExecuteDataset(conn, CommandType.Text, string.Format(selectString, personSetting.WechatId, xiaoMiData.date)).Tables[0];
var metaDataResult = SqlHelper.ConvertTo <MetaData>(data).ToList();
if (metaDataResult.Count > 0)
{
SqlHelper.ExecuteNonQuery(conn, CommandType.Text, string.Format(updateString, xiaoMiData.step, (int.Parse(xiaoMiData.step) > 7999 ? 1 : 0), personSetting.WechatId, xiaoMiData.date));
}
else
{
SqlHelper.ExecuteNonQuery(conn, CommandType.Text, string.Format(createString, personSetting.WechatId, xiaoMiData.date, xiaoMiData.step, (int.Parse(xiaoMiData.step) > 8000 ? 1 : 0)));
}
}
public void dealRewardsScore(List <PersonScoreRank> personScoreRank)
{
var sql = @"SELECT WechatId,sum([score]) as score FROM Innocellence_GSK_WeChat_HM_Score group by WechatId";
var conn = XiaoMiData.GetConnectstr();
var result = new Dictionary <string, int>();
using (SqlConnection connection = new SqlConnection(conn))
{
connection.Open();
var data = SqlHelper.ExecuteReader(connection, CommandType.Text, sql);
while (data.Read())
{
result[data.GetString(0)] = data.GetInt32(1);
}
}
foreach (var rewardsScore in result)
{
var targetPerson = personScoreRank.Find(p => p.WechatId == rewardsScore.Key);
if (targetPerson != null)
{
targetPerson.Score += rewardsScore.Value;
}
}
}