public bool DataExists(SQLData data)
{
int result = 0;
string cmdStr = "SELECT COUNT(*) AS count FROM data " +
"WHERE LOWER(user_id) = @user AND time = @time AND (mean = @mean OR median = @median OR std_dev = @std_dev OR count = @count) ;";
SqlCommand command = new SqlCommand(cmdStr, SQLraw);
command.Parameters.AddWithValue("@user", data.User);
command.Parameters.AddWithValue("@time", data.Time);
command.Parameters.AddWithValue("@mean", data.Mean);
command.Parameters.AddWithValue("@median", data.Median);
command.Parameters.AddWithValue("@std_dev", data.StdDev);
command.Parameters.AddWithValue("@count", data.Count);
SqlDataReader reader = null;
try
{
reader = command.ExecuteReader();
while (reader.Read())
{
result = Convert.ToInt32(reader["count"]);
}
reader.Close();
}
catch (Exception e)
{
Supporting.WriteLog(e.Message);
return(true);
}
return(result == 0 ? false : true);
}
public string DeleteUserSQL(string username)
{
string cmdStr = " DELETE a FROM Transactions a " +
" INNER JOIN Accounts b ON a.Account_Id = b.Id " +
" WHERE b.username = @username;" +
" DELETE FROM Accounts " +
" WHERE username = @username;" +
" DELETE FROM UserInstitution " +
" WHERE username = @username;" +
" DELETE FROM Users " +
" WHERE username = @username;";
SqlCommand command = new SqlCommand(cmdStr, SQLraw);
command.Parameters.AddWithValue("@username", username);
try { command.ExecuteNonQuery(); }
catch (Exception e)
{
Supporting.WriteLog(e.ToString());
return("error: delete failed");
}
return("info: success");
}
private string InsertData(SQLData data)
{
string cmdStr = " INSERT INTO data (user_id, time_zone, time, active, count, mean, median, std_dev) " +
" VALUES (@user , @time_zone, @time , @active, @count, @mean, @median, @std_dev);";
SqlCommand command = new SqlCommand(cmdStr, SQLraw);
command.Parameters.AddWithValue("@user", data.User);
command.Parameters.AddWithValue("@time_zone", data.TimeZone);
command.Parameters.AddWithValue("@time", data.Time);
command.Parameters.AddWithValue("@active", data.Active);
command.Parameters.AddWithValue("@count", data.Count);
command.Parameters.AddWithValue("@mean", data.Mean);
command.Parameters.AddWithValue("@median", data.Median);
command.Parameters.AddWithValue("@std_dev", data.StdDev);
try { command.ExecuteNonQuery(); }
catch (Exception e)
{
Supporting.WriteLog(e.Message);
return("error: adding data");
}
return("info: added data");
}
public string SubmitStatus(string token, SQLData data)
{
string ip = GetIP();
if (CheckDDOS(ip))
{
return(null);
}
if (data == null)
{
return(null);
}
if (!ServerMain.Instance.Sessions.Exists(p => p.IP == ip && p.Token == token))
{
return("error:no token");
}
AuthenticatedToken current = ServerMain.Instance.Sessions.Find(p => p.Token == token);
ServerMain.Instance.Sessions.RemoveAll(p => p.IP == ip);
if (data.Count < 2000)
{
Supporting.WriteLog($"[{ip}]:error:data count");
return("error:green");
}
if (DateTime.UtcNow.Ticks - current.Time.Ticks < 0 || DateTime.UtcNow - current.Time > TimeSpan.FromMinutes(2))
{
Supporting.WriteLog($"[{ip}]:error:time");
return("error:green");
}
if (DateTime.UtcNow.Ticks - data.Time.Ticks < 0 || DateTime.UtcNow - data.Time > TimeSpan.FromMinutes(2))
{
Supporting.WriteLog($"[{ip}]:error:time utc");
return("error:green");
}
if (ServerMain.Instance.mySQL.AddData(data).IndexOf("error") != -1)
{
Supporting.WriteLog($"[{ip}]:error:sql failed");
return("error:SQL failed");
}
Supporting.WriteLog($"[{ip}][{data.User.Substring(0, 8)}]:Added data[{data.Mean.ToString("N4")}][{data.Median.ToString("N4")}][{data.StdDev.ToString("N4")}][{data.Active}][{data.Count}]");
ServerMain.Instance.myStats.Add(new ServerMain.ServerStats {
ip = ip, served = ServerMain.Served.Submit, time = DateTime.UtcNow
});
return("success");
}
public void AddToken(AuthenticatedToken token)
{
string ip = GetIP();
if (CheckDDOS(ip))
{
return;
}
ServerMain.Instance.Sessions.Add(token);
Supporting.WriteLog($"[{token.IP}]:Added token");
ServerMain.Instance.myStats.Add(new ServerMain.ServerStats {
ip = ip, served = ServerMain.Served.Token, time = DateTime.UtcNow
});
}
public static bool CheckDDOS(string ip)
{
DateTime cutOff = DateTime.UtcNow.AddSeconds(-5);
ServerMain.DDOSlist.RemoveAll(p => (DateTime)p.Item2 < cutOff);
if (ServerMain.DDOSlist.Count(p => (string)p.Item1 == ip) > 10)
{
Supporting.WriteLog($"[{ip}]:DDOS triggered");
ServerMain.Instance.myStats.Add(new ServerMain.ServerStats {
ip = ip, served = ServerMain.Served.DDOS, time = DateTime.UtcNow
});
return(true);
}
ServerMain.DDOSlist.Add(new Tuple <string, DateTime>(ip, DateTime.UtcNow));
return(false);
}
public List <SQLData> GetDataListTimeZone(int zone, TimeSpan time)
{
List <SQLData> result = new List <SQLData>();
string cmdStr = " SELECT * FROM data " +
" WHERE time_zone = @zone AND time > @start_time";
DateTime start_time = DateTime.UtcNow - time;
SqlCommand command = new SqlCommand(cmdStr, SQLraw);
command.Parameters.AddWithValue("@zone", zone);
command.Parameters.AddWithValue("@start_time", start_time);
SqlDataReader reader = null;
try
{
reader = command.ExecuteReader();
while (reader.Read())
{
SQLData current = new SQLData();
current.User = Convert.ToString(reader["user_id"]);
current.TimeZone = Convert.ToInt32(reader["time_zone"]);
current.Time = Convert.ToDateTime(reader["time"]);
current.Active = Convert.ToBoolean(reader["active"]);
current.Count = Convert.ToInt32(reader["count"]);
current.Mean = Convert.ToDouble(reader["mean"]);
current.Median = Convert.ToDouble(reader["median"]);
current.StdDev = Convert.ToDouble(reader["std_dev"]);
result.Add(current);
}
reader.Close();
}
catch (Exception e)
{
Supporting.WriteLog(e.Message);
}
return(result);
}
public List <SQLData> GetUserData(string userID, TimeSpan time)
{
string ip = GetIP();
if (CheckDDOS(ip))
{
return(null);
}
if (time == null || time.TotalDays > 30 || time.Ticks <= 0)
{
return(null);
}
Supporting.WriteLog($"[{ip}]:Served userdata [{userID.Substring(0, 8)}][{time}]");
ServerMain.Instance.myStats.Add(new ServerMain.ServerStats {
ip = ip, served = ServerMain.Served.GetLocal, time = DateTime.UtcNow
});
return(ServerMain.Instance.mySQL.GetDataListUser(userID, time));
}
public List <SQLData> GetTimeZoneData(int timeZone, TimeSpan time)
{
string ip = GetIP();
if (CheckDDOS(ip))
{
return(null);
}
if (timeZone < -12 || timeZone > 12 || time == null || time.TotalDays > 30 || time.Ticks <= 0)
{
return(null);
}
List <SQLData> result = new List <SQLData>();
List <SQLData> rawData = ServerMain.Instance.mySQL.GetDataListTimeZone(timeZone, time);
if (rawData.Count == 0)
{
return(null);
}
for (int i = -Convert.ToInt32(time.TotalMinutes); i <= 0; i += 5)
{
DateTime start = DateTime.UtcNow + TimeSpan.FromMinutes(i);
DateTime end = DateTime.UtcNow + TimeSpan.FromMinutes(i + 5);
double meanS = 0, medianS = 0, stdDevS = 0;
long count = 0;
foreach (SQLData data in rawData)
{
if (data.Time < start || data.Time > end)
{
continue;
}
meanS += data.Mean;
medianS += data.Median;
stdDevS += data.StdDev;
count++;
}
if (meanS == 0 || medianS == 0)
{
continue;
}
SQLData current = new SQLData();
current.Mean = meanS / count;
current.Median = medianS / count;
current.StdDev = stdDevS / count;
current.TimeZone = timeZone;
current.Time = end;
result.Add(current);
}
if (result.Count == 0 || result.Exists(p => p.Mean == double.NaN || p.Median == double.NaN || p.StdDev == double.NaN))
{
return(null);
}
Supporting.WriteLog($"[{ip}]:Served global data [{timeZone}][{time}]");
ServerMain.Instance.myStats.Add(new ServerMain.ServerStats {
ip = ip, served = ServerMain.Served.GetGlobal, time = DateTime.UtcNow
});
return(result);
}