private static DataTable GetUserLoginDataTable()
{
DataTable userLogin = _accessMySql.getDataTable(string.Format(@"select login_id,u.user_email,ul.user_id,login_ipfrom, FROM_UNIXTIME(login_createdate) login_createdate,
(select ul2.login_address from users_login ul2 where ul2.login_ipfrom=ul.login_ipfrom and ul2.login_address<>'' limit 1) login_address
from users_login ul
INNER JOIN users u on u.user_id=ul.user_id where FROM_UNIXTIME(login_createdate)>'{0}'", CommonFunction.DateTimeToString(DateTime.Now.AddHours(-1))));
DataTable othenCityUser = new DataTable();
othenCityUser.Columns.Add("會員編號", typeof(string));
othenCityUser.Columns.Add("會員賬號", typeof(string));
othenCityUser.Columns.Add("IP", typeof(string));
othenCityUser.Columns.Add("登錄地區", typeof(string));
othenCityUser.Columns.Add("登錄時間", typeof(string));
foreach (DataRow drUser in userLogin.Rows)
{
DataRow newRow = othenCityUser.NewRow();
newRow["會員編號"] = drUser["user_id"];
newRow["會員賬號"] = drUser["user_email"];
newRow["IP"] = drUser["login_ipfrom"];
Console.Write("正在處理IP" + newRow["IP"] + "\n");
string ip = drUser["login_ipfrom"].ToString();
IPMessage ipMessage = new IPMessage();
try
{
if (null == drUser["login_address"] || string.IsNullOrEmpty(drUser["login_address"].ToString()))
{
if (drUser["login_ipfrom"].ToString().Contains("192.168"))
{
newRow["登錄地區"] = "內網";
}
else
{
ipMessage = GetMessageByIP(ip);
newRow["登錄地區"] = ipMessage.country.ToString() + ipMessage.region + ipMessage.city + ipMessage.county;
}
}
else
{
newRow["登錄地區"] = drUser["login_address"];
}
_accessMySql.execCommand(string.Format(@"update users_login set login_address='{0}' where login_ipfrom='{1}'", newRow["登錄地區"].ToString(), drUser["login_ipfrom"]));
if (newRow["登錄地區"].ToString().Contains("台湾") || newRow["登錄地區"].ToString() == "內網")
{
continue;
}
}
catch (Exception ex)
{
//ErrorLogHelper loghelper = new ErrorLogHelper("項目名稱:Program/Main" + ex.ToString());
newRow["登錄地區"] = "無資訊";
continue;
}
newRow["登錄時間"] = CommonFunction.DateTimeToString(Convert.ToDateTime(drUser["login_createdate"]));
othenCityUser.Rows.Add(newRow);
}
return othenCityUser;
}
private static IPMessage GetMessageByIP(string ip)
{
string errorMesage = String.Empty;
IPMessage ipMessage = new IPMessage();
try
{
string url = "http://ip.taobao.com/service/getIpInfo.php?ip=";
HttpWebRequest httpRequest = (HttpWebRequest)WebRequest.Create(url + ip);
httpRequest.Timeout = 10000;
httpRequest.Method = "GET";
HttpWebResponse httpResponse = (HttpWebResponse)httpRequest.GetResponse();
StreamReader sr = new StreamReader(httpResponse.GetResponseStream(), System.Text.Encoding.UTF8);
string result = sr.ReadToEnd();
result = result.Replace("\r", "").Replace("\n", "").Replace("\t", "");
//helper.SendToGroup(GroupCode, "rgf", json, true, true);
int status = (int)httpResponse.StatusCode;
//ErrorLogHelper loghelper = new ErrorLogHelper("請求ip地址信息出錯" + result);
CodeData codeData = JsonConvert.DeserializeObject<CodeData>(result);
ipMessage = JsonConvert.DeserializeObject<IPMessage>(codeData.data.ToString());
sr.Close();
return ipMessage;
}
catch (Exception ex)
{
errorMesage += ex.Message;
//ErrorLogHelper loghelper = new ErrorLogHelper("請求ip地址信息出錯"+ex.ToString());
Console.WriteLine(ex);
return ipMessage;
}
}
