private void UpdateMetadata(List <Setting> allSetting)
{
var xiaomiDate = new XiaoMiData();
var MetadataService = new MetadataService();
foreach (var setting in allSetting)
{
richTextBox1.Text += "wechatId: " + setting.WechatId + " wechatName: " + setting.WechatName + "\r\n";
var url = xiaomiDate.GetXiaoMiDataUrl(setting);
var sevenDaysData = xiaomiDate.GetDateResponse(url);
if (sevenDaysData.result == "success")
{
richTextBox1.Text += "Get Setting metadata success！\r\n";
foreach (var data in sevenDaysData.data)
{
MetadataService.UpdateMetaData(setting, data);
richTextBox1.Text += "update Metadata date:" + data.date + " steps: " + data.step + "\r\n";
}
}
else
{
LogHelper.Error(typeof(Form1), "get setting date error: wechatId:" + setting.WechatId);
}
}
}
public List <PersonScoreRank> GetNewPersonStepRank(List <MetaData> allMetaData)
{
var personRank = new List <PersonScoreRank>();
var wechatMap = XiaoMiData.GetWeChatNameById();
foreach (var metadata in allMetaData)
{
var onePerson = new PersonScoreRank();
if (personRank.Exists(p => p.WechatId.ToLower() == metadata.WechatId.ToLower()))
{
var personScoreRank = personRank.FirstOrDefault(p => p.WechatId.ToLower() == metadata.WechatId.ToLower());
if (personScoreRank != null)
{
personScoreRank.Score += metadata.Score;
}
}
else
{
onePerson.Score = metadata.Score;
onePerson.WechatId = metadata.WechatId;
onePerson.WechatName = wechatMap.ContainsKey(metadata.WechatId)?wechatMap[metadata.WechatId]:"";
personRank.Add(onePerson);
}
}
dealRewardsScore(personRank);
var personRankResult = personRank.OrderByDescending(p => p.Score).ToList();
for (var i = 0; i < personRankResult.Count(); i++)
{
personRankResult[i].Rank = i + 1;
}
return(personRankResult);
}
private void SyncXiaoMiData(object date)
{
try
{
Control.CheckForIllegalCrossThreadCalls = false;
var fromDate = date as DateTime? ?? new DateTime();
var xiaomiDate = new XiaoMiData();
richTextBox1.Text = "" + DateTime.Now;
richTextBox1.Text += "begin get setting from DB\r\n";
var settingList = xiaomiDate.GetSetting();
richTextBox1.Text += string.Format("getted {0} settings,they are:\r\n", settingList.Count);
UpdateMetadata(settingList);
richTextBox1.Text += "Update Person metadata success!\r\n";
UpdateScoreRank(fromDate);
richTextBox1.Text += "Update Person score Rank success!\r\n";
UpdateStepRank(fromDate);
richTextBox1.Text += "Update Person step Rank success!\r\n";
UpdateGroupRank(fromDate);
richTextBox1.Text += "Update Person group Rank success!\r\n";
}
catch (Exception ex)
{
LogHelper.Error(typeof(Form1), ex);
}
}
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 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 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);
}
}
private void button3_Click_1(object sender, EventArgs e)
{
try
{
var xiaomiDate = new XiaoMiData();
var allSetting = xiaomiDate.GetSetting();
richTextBox1.Text = "get all setting,count:" + allSetting.Count;
LogHelper.Info(typeof(Form1), "get all setting,count:" + allSetting.Count);
foreach (var setting in allSetting)
{
//if (setting.WechatId != "13354280516")
//{
// continue;
//}
richTextBox1.Text += "get old access token:wechatId:" + setting.WechatId;
LogHelper.Info(typeof(Form1), "get old access token:wechatId:" + setting.WechatId);
if (setting.RefreshToken != null)
{
var url = xiaomiDate.RefreshTokenURL(setting.RefreshToken);
LogHelper.Info(typeof(Form1), "get old access token url:" + url);
var returnText = RequestUtility.HttpGet(url, null);
LogHelper.Info(typeof(Form1), "get new access token::" + returnText);
var js = new JavaScriptSerializer();
var returnObj = (Dictionary <string, string>)js.Deserialize(returnText.Replace("&&&START&&&", ""), typeof(Dictionary <string, string>));
if (!returnObj.ContainsKey("error") && returnObj.ContainsKey("access_token") && returnObj.ContainsKey("mac_key") && returnObj.ContainsKey("refresh_token"))
{
richTextBox1.Text += "update access token:" + returnObj["access_token"];
xiaomiDate.UpdateSettingMacAndToken(returnObj["access_token"], returnObj["mac_key"], returnObj["refresh_token"], setting.WechatId);
}
else
{
LogHelper.Error(typeof(Form1), "get new access token error:" + returnText);
}
}
else
{
LogHelper.Info(typeof(Form1), "current user have not refresh token:");
}
}
}
catch (Exception ex)
{
LogHelper.Error(typeof(Form1), ex);
}
}
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 List <PersonStepRank> GetPersonStepRankList(List <MetaData> allMetadata)
{
var personStepRankList = new List <PersonStepRank>();
var wechatMap = XiaoMiData.GetWeChatNameById();
foreach (var metadata in allMetadata)
{
var personStepRank = new PersonStepRank();
if (personStepRankList.Exists(p => p.WechatId == metadata.WechatId))
{
var target = personStepRankList.FirstOrDefault(p => p.WechatId == metadata.WechatId);
if (target != null)
{
target.Steps += metadata.Steps;
target.StepsByDay = DateTime.Compare(metadata.CreatedDate, DateTime.Now.Date) == 0 ? metadata.Steps : 0;
}
}
else
{
personStepRank.StepsByDay = DateTime.Compare(metadata.CreatedDate, DateTime.Now.Date) == 0 ? metadata.Steps : 0;
personStepRank.RankByDay = GetPersonStepRankByWeChatId(metadata.WechatId);
personStepRank.Steps = metadata.Steps;
personStepRank.WechatId = metadata.WechatId;
personStepRank.WechatName = wechatMap.ContainsKey(metadata.WechatId) ? wechatMap[metadata.WechatId] : "";
personStepRankList.Add(personStepRank);
}
}
var personStepRankListResult = personStepRankList.OrderByDescending(m => m.Steps).ToList();
for (var i = 0; i < personStepRankListResult.Count; i++)
{
personStepRankListResult[i].Rank = i + 1;
}
personStepRankListResult = personStepRankListResult.OrderByDescending(m => m.StepsByDay).ToList();
for (var i = 0; i < personStepRankListResult.Count; i++)
{
personStepRankListResult[i].RankByDay = i + 1;
}
return(personStepRankListResult);
}
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;
}
}
}