/// <summary>
/// 向数据库中插入客户端拿到的数据 并返回要向客户端报告的执行结果
/// </summary>
/// <returns></returns>
public string InsertDate(string remoteIp, string clientMac, string formData)
{
string result = string.Empty;
LogInfoHelper.WriteRunTimeMessage(string.Format("远程客户端IP:[{0}] , MAC:[{1}] 提交了formData\r\n{2}", remoteIp, clientMac.ToUpper(), formData), RunTimeMessageType.Log, true);
//分析from中的cityList信息
List <AirDataInfo> cityList = AnayFormData(formData);
string[] insertedCityName = null;
try
{
insertedCityName = m_access.InsertCityListInfo(cityList, clientMac);
}
catch (Exception exp)
{
LogInfoHelper.WriteRunTimeMessage(string.Format("插入客户端提交过来的数据时发生异常,异常信息为{0}\r\n调用堆栈为{1}", exp.Message, exp.StackTrace), RunTimeMessageType.Exception, true);
}
if (insertedCityName.Length > 0)
{
LogInfoHelper.WriteRunTimeMessage(string.Format("远程客户端IP:{0},MAC:[{1}]此次提交成功插入城市{2}个", remoteIp, clientMac.ToUpper(), insertedCityName.Length), RunTimeMessageType.Log, true);
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append("status:OK\r\nak:\r\nmessage:成功接收到城市数据 且成功插入到数据库的城市数据有:\r\n");
foreach (string cityName in insertedCityName)
{
sb.Append(cityName + ",");
}
result = sb.ToString().TrimEnd(',') + "\r\n";
}
else
{
result = string.Format("status:{0}\r\nak:{1}\r\nmessage:{2}\r\n", "OK", "", "成功接收数据但是没有插入数据");
}
return(result);
}
/*请求URL格式
* Service.ashx?accesskey=指定的ak&MAC=请求机器的MAC地址&requesttype=请求类型 (requesttype 有两种类型 getcitylist、updatecitydata)
* form中数据格式为 data = {[cityInfo],[cityInfo],[cityInfo].....}
*/
public void ProcessRequest(HttpContext context)
{
Business business = new Business();
try
{
LogInfoHelper.WriteRunTimeMessage("客户端请求 原始信息 ->" + context.Request.RawUrl, RunTimeMessageType.Log, true);
context.Response.ContentType = "text/plain";
HttpRequest request = context.Request;
HttpResponse response = context.Response;
string accesskey = request.QueryString["accesskey"];
#region 接入key 判断部分
if (accesskey == null)
{
responseInfo(response, "error", "access key is null");
return;
}
if (!business.AccessKeyValidate(accesskey))
{
responseInfo(response, "error", "access key illegal");
return;
}
#endregion
#region 业务部分
//MAC 获取部分
string clinetMac = request.QueryString["MAC"].ToLower();
if (clinetMac == null)
{
responseInfo(response, "error", "clientMAC is null");
return;
}
//此次请求的业务类型
string requestType = request.QueryString["requesttype"].ToLower();
if (requestType == null)
{
responseInfo(response, "error", "requesttype is null");
return;
}
switch (requestType)
{
case "getcitylist":
string result = business.GetCityList(clinetMac);
LogInfoHelper.WriteRunTimeMessage(string.Format("clientMac:[{0}]的获取城市列表Business层返回的字符结果为\r\n{1}", clinetMac.ToUpper(), result), RunTimeMessageType.Log, true);
response.Write(result);
response.Flush();
break;
case "updatecitydata":
//form中数据格式为 data = {[cityInfo],[cityInfo],[cityInfo].....}
System.IO.Stream inputStream = context.Request.InputStream;
string formData = GetFormStr(inputStream);
string insResult = business.InsertDate(context.Request.UserHostAddress, clinetMac, formData);
response.Write(insResult);
response.Flush();
break;
default:
responseInfo(response, "error", "requesttype is illegal");
return;
}
#endregion
}
catch (Exception exp)
{
LogInfoHelper.WriteRunTimeMessage(string.Format("Process 函数内捕捉到的异常 \r\n Message :{0} \r\n 堆栈信息:{1}", exp.Message, exp.StackTrace), RunTimeMessageType.Exception, true);
}
finally
{
business.Dispose();
}
}
/// <summary>
/// 获取最新的一小时没有被刷新数据的城市列表 且返回此次请求所使用的akStr
/// (PS:此时对表加了write锁以控制并发 非长久之计！！高并发情况下要首先考虑优化此处)
/// </summary>
/// <returns></returns>
public List <string> GetNoFreashedCityList(string RemoteMac, out string akStr)
{
List <string> cityList = new List <string>();
akStr = "";
LockTable(new string[] { "citylistinfo", "akinfo" }, SqlLockType.write);
#region 获取当前还有多少城市没有被更新
//当前小时时段内没有被更新的或者分配过机器去更新但是5分钟没有被更新的数量
string countsql = @"-- 20170216版
select count(*) as count from citylistinfo
where
-- 且如果上一次更新时间的日期不等于当前时间的日期时上一次的更新时间不是在23点(跨天bug)
((day(LastFreashTime)!= day(now()) and hour(LastFreashTime)<23)
or
(day(LastFreashTime)!=day(now()) and hour(AssignTime)=0 and hour(now())>0)
or
-- 或者 上一次更新日期等于当前系统日期 但是上一次更新早于当前的系统时间的小时部分
((day(LastFreashTime)=day(now()))
and
abs(hour(lastfreashtime)-hour(now()))>1))
or
-- 上面的部分bool值 或者 分配给某个MAC地址的城市5分钟后还没有回送上一个小时的数据
((TIMESTAMPDIFF(MINUTE,assigntime,now())>5) and (TIMESTAMPDIFF(HOUR,LastFreashTime,AssignTime)>0))
";
MySqlCommand cmd = new MySqlCommand(countsql, conn);
MySqlDataReader reader = cmd.ExecuteReader();
reader.Read();
int noFreashCityCount = int.Parse(reader["count"].ToString());
reader.Close();
//当前一个小时内 所有城市数据都是新的
if (noFreashCityCount == 0)
{
UnLockTable();
return(cityList);
}
#endregion
#region 计算该次请求要分配的城市列表数量
int nowMinute = DateTime.Now.Minute / 10;
int assignCount = 0;
//按当前时间在一小时内所处的位置分发任务量(此处有CAST丢失精度问题 ！！格外注意)
if (noFreashCityCount < 5)
{
assignCount = noFreashCityCount;
}
else
{
//nowminute 处理
nowMinute = nowMinute == 0 ? 1 : nowMinute;
nowMinute = nowMinute == 5 ? 4 : nowMinute;
assignCount = (int)(noFreashCityCount * ((nowMinute + 1) / 5.0));
}
#endregion
#region 获取和更新数据
//当前小时时段内没有被更新的或者分配过机器去更新但是5分钟没有被更新的城市列表
string sql = @"-- 20170311版
select * from citylistinfo
where
-- 且如果上一次更新时间的日期不等于当前时间的日期时上一次的更新时间不是在23点(跨天bug)
((day(LastFreashTime)!= day(now()) and hour(LastFreashTime)<23)
or
(day(LastFreashTime)!=day(now()) and hour(AssignTime)=0 and hour(now())>0)
or
-- 或者 上一次更新日期等于当前系统日期 但是上一次更新早于当前的系统时间的小时部分
((day(LastFreashTime)=day(now()))
and
abs(hour(lastfreashtime)-hour(now()))>1))
or
-- 上面的部分bool值 或者 分配给某个MAC地址的城市5分钟后还没有回送上一个小时的数据
((TIMESTAMPDIFF(MINUTE,assigntime,now())>5) and (TIMESTAMPDIFF(HOUR,LastFreashTime,AssignTime)>0))
";
//Adt 貌似是会自己关连接的
/// MySqlDataAdapter adt = new MySqlDataAdapter(sql, conn);
cmd.CommandText = sql;
reader = cmd.ExecuteReader();
int i = 0;
while (reader.Read())
{
cityList.Add(reader["CityName"].ToString());
i++;
if (i == assignCount)
{
break;
}
}
reader.Close();
if (cityList.Count > 0)
{
//获取ak
string ak = GetAKByCount(cityList.Count);
//out返回值
akStr = ak;
if (string.IsNullOrEmpty(ak))
{
throw new Exception(string.Format("{0} 引发异常--> 没有满足请求count值的ak可用 请求的count值为{1}", DateTime.Now, cityList.Count));
}
//更新分配时间和MAC字段
foreach (string cityName in cityList)
{
cmd.CommandText = string.Format("update citylistinfo set assigntime=SYSDATE(),assignmachinemac ='{0}' where CityName='{1}'", RemoteMac, cityName);
int execResultNum = cmd.ExecuteNonQuery();
if (execResultNum == 1)
{
continue;
}
else
{
LogInfoHelper.WriteRunTimeMessage(string.Format("更新城市分配信息时发生异常 城市名称为{0} 客户端MAC地址为{1]", cityName, RemoteMac), RunTimeMessageType.Exception, true);
}
}
}
#endregion
//释放表级锁(！！重要！！)
UnLockTable();
//释放数据库连接(！！重要！！)
DisconnectDatabase();
return(cityList);
}
/// <summary>
/// 将指定客户端Mac地址提交上的CityInfo插入到数据库中并返回成功插入的城市的列表
/// </summary>
/// <param name="cityInfoList">客户端服务提交上来的城市数据列表</param>
/// <param name="clientMac">客户端MAC地址</param>
/// <returns></returns>
public string[] InsertCityListInfo(List <AirDataInfo> cityInfoList, string clientMac)
{
LockTable(new string[] { "citylistinfo", "serviceairinfotable" }, SqlLockType.write);
List <string> resultList = new List <string>();
string[] noFreashCityArr = GetNoFreashCityListByClientMac(clientMac);
if (noFreashCityArr.Length > 0)
{
foreach (string cityName in noFreashCityArr)
{
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
IEnumerable <AirDataInfo> Enumer = cityInfoList.Where(t => t.CityName == cityName);
IEnumerator <AirDataInfo> itertor = Enumer.GetEnumerator();
bool haveThisCity = itertor.MoveNext();
//未刷新城市在当前List中存在
if (haveThisCity)
{
AirDataInfo airInfo = itertor.Current;
cmd.CommandText = "INSERT INTO serviceairinfotable(AQI,UpdateTime,PM25,PM10,PolText,FL,FS,Pressure,Text,cityName,Temp,ShiDu,Week) VALUES(?aqi,?updatetime,?pm25,?pm10,?pollutionindex,?fl,?fs,?pressure,?nowweathertext,?cityname,?temperature,?shidu,?week)";
MySqlParameter[] param = new MySqlParameter[]
{
new MySqlParameter("?aqi", airInfo.AQI),
new MySqlParameter("?updatetime", airInfo.UpdateTime),
new MySqlParameter("?pm25", airInfo.PM25),
new MySqlParameter("?pm10", airInfo.PM10),
new MySqlParameter("?pollutionindex", airInfo.PollutionIndex),
new MySqlParameter("?fl", airInfo.FL),
new MySqlParameter("?fs", airInfo.FS),
new MySqlParameter("?pressure", airInfo.Pressure),
new MySqlParameter("?nowweathertext", airInfo.NowWeatherText),
new MySqlParameter("?cityname", airInfo.CityName),
new MySqlParameter("?temperature", airInfo.Temp),
new MySqlParameter("?shidu", airInfo.Hum),
new MySqlParameter("?week", airInfo.Week)
};
cmd.Parameters.AddRange(param);
int resultNum = 0;
try
{
resultNum = cmd.ExecuteNonQuery();
}
catch (Exception exp)
{
LogInfoHelper.WriteRunTimeMessage("插入城市数据信息异常" + exp.Message + "\r\n" + exp.StackTrace, RunTimeMessageType.Exception, true);
}
if (resultNum == 1)
{
cmd.CommandText = string.Format("update citylistinfo set LastFreashTime='{1}',AssignMachineMAC='' where CityName='{0}'", cityName, airInfo.UpdateTime.ToString("yyyy-MM-dd HH:mm:ss"));
if (cmd.ExecuteNonQuery() != 1)
{
LogInfoHelper.WriteRunTimeMessage("更新LastFreashTime信息异常", RunTimeMessageType.Exception, true);
}
resultList.Add(cityName);
}
}
else
{
LogInfoHelper.WriteRunTimeMessage(string.Format("client :{0}提交上的城市列表信息中有未包含的城市信息 城市名称为{1}", clientMac, cityName), RunTimeMessageType.Exception, true);
continue;
}
}
}
else
{
LogInfoHelper.WriteRunTimeMessage(string.Format("client {0} 被分配的城市列表全部被覆盖~_~!!!", clientMac), RunTimeMessageType.Log, true);
}
UnLockTable();
DisconnectDatabase();
return(resultList.ToArray());
}
/// <summary>
/// 将form中的城市信息解析为城市天气信息的实例集合
/// </summary>
/// <param name="formDate">客户端服务提交上来的额form信息</param>
/// <returns></returns>
private List <AirDataInfo> AnayFormData(string formDate)
{
List <AirDataInfo> resultList = new List <AirDataInfo>();
try
{
//form中数据格式为 data = {[cityInfo],[cityInfo],[cityInfo].....} [cityInfo]格式 [key=value,key=value,key=value........]
formDate = formDate.Trim('{');
formDate = formDate.Trim('}');
string[] singleCityInfoArr = formDate.Split(new string[] { "|" }, StringSplitOptions.RemoveEmptyEntries);
//singleCityInfoArr中 格式应为 1. [cityInfo] 2. [cityInfo] 、、、、
foreach (string singleCityInfoStr in singleCityInfoArr)
{
string cityInfo = singleCityInfoStr.TrimStart('[').TrimEnd(']');
string[] keyValueArr = cityInfo.Split(',');
//[cityInfo]中单个属性的格式为 [key=value,key=value,key=value、、、、]
//keyValueArr 中格式应为 key=value,key=value,key=value、、、、、
Dictionary <string, string> airInfoDic = new Dictionary <string, string>();
foreach (string keyValueInfo in keyValueArr)
{
string key = keyValueInfo.Split('=')[0];
string value = keyValueInfo.Split('=')[1];
if (!string.IsNullOrEmpty(key) && !string.IsNullOrEmpty(value))
{
airInfoDic.Add(key, value);
}
}
AirDataInfo newCityInfo = new AirDataInfo();
Type airInfoType = newCityInfo.GetType();
PropertyInfo[] properties = airInfoType.GetProperties();
//反射的方式循环设置相应属性的值
foreach (PropertyInfo property in properties)
{
try
{
//该属性为int类型
if (property.PropertyType == typeof(int))
{
int value = int.Parse(airInfoDic[property.Name]);
property.SetValue(newCityInfo, value, null);
}
else if (property.PropertyType == typeof(DateTime))
{
DateTime value = DateTime.Parse(airInfoDic[property.Name]);
property.SetValue(newCityInfo, value, null);
}
//该属性为string类型
else
{
property.SetValue(newCityInfo, airInfoDic[property.Name], null);
}
}
catch (Exception exp)
{
LogInfoHelper.WriteRunTimeMessage(string.Format("反射设置对象属性值时发生异常 属性名为{0} dic中value为{1} exp.Messge 为{2}", property.Name, airInfoDic[property.Name], exp.Message), RunTimeMessageType.Exception, true);
}
}
resultList.Add(newCityInfo);
}//第一层Foreach
}
catch (Exception exp)
{
string message = string.Format("解析客户端提交上来的form数据异常 form数据为{0} \r\n 异常信息为 {1}", formDate, exp.Message);
LogInfoHelper.WriteRunTimeMessage(message, RunTimeMessageType.Exception, true);
}
return(resultList);
}
