internal static void JourneysDeleteDelete(HttpListenerRequest request, HttpListenerResponse response)
{
string data = WebServer.GetDataFromRequestInputStream(request);
dynamic r = JsonConvert.DeserializeObject(data);
int journeyID = r["id"];
try
{
using (MySqlConnection con = new MySqlConnection(DBHelper.DBConnectionstring))
{
con.Open();
using (MySqlCommand cmd = new MySqlCommand(@"
DELETE
FROM
journeys
WHERE
ID = @journeyID
", con))
{
cmd.Parameters.AddWithValue("@journeyID", journeyID);
Tools.DebugLog(cmd);
SQLTracer.TraceNQ(cmd);
}
}
}
catch (Exception ex)
{
ex.ToExceptionless().FirstCarUserID().Submit();
Logfile.Log(ex.ToString());
}
WriteString(response, "OK");
}
private static void CalculateChargeDuration(int journeyId)
{
try
{
using (MySqlConnection con = new MySqlConnection(DBHelper.DBConnectionstring))
{
con.Open();
double charge_duration_minutes = 0;
using (MySqlCommand cmd = new MySqlCommand(@"
SELECT
chargingstate.EndDate,
chargingstate.StartDate
FROM
chargingstate
WHERE
chargingstate.Pos >= (SELECT StartPosID FROM journeys WHERE ID = @journeyID)
AND chargingstate.Pos < (SELECT EndPosID FROM journeys WHERE ID = @journeyID)
AND chargingstate.carID = (SELECT CarID FROM journeys WHERE ID = @journeyID)
", con))
{
cmd.Parameters.AddWithValue("@journeyID", journeyId);
Tools.DebugLog(cmd);
MySqlDataReader dr = SQLTracer.TraceDR(cmd);
while (dr.Read())
{
DateTime d1 = (DateTime)dr[0];
DateTime d2 = (DateTime)dr[1];
TimeSpan ts = d1 - d2;
charge_duration_minutes += ts.TotalMinutes;
}
dr.Close();
}
using (MySqlCommand cmd = new MySqlCommand(@"
UPDATE
journeys
SET
charge_duration_minutes = @charge_duration_minutes
WHERE
Id = @journeyID", con))
{
cmd.Parameters.AddWithValue("@journeyID", journeyId);
cmd.Parameters.AddWithValue("@charge_duration_minutes", (int)charge_duration_minutes);
Tools.DebugLog(cmd);
SQLTracer.TraceNQ(cmd);
}
}
}
catch (Exception ex)
{
ex.ToExceptionless().FirstCarUserID().Submit();
Logfile.Log(ex.ToString());
}
}
static internal void UpdateDataTable(string table)
{
if (!DBHelper.ColumnExists(table, "export"))
{
using (MySqlConnection con = new MySqlConnection(DBHelper.DBConnectionstring))
{
con.Open();
using (MySqlCommand cmd = new MySqlCommand($"alter table {table} ADD column export TINYINT(1) NULL", con)
{
CommandTimeout = 6000
})
{
SQLTracer.TraceNQ(cmd);
}
}
}
}
private int AddNewSupercharger(string name, double lat, double lng)
{
using (MySqlConnection con = new MySqlConnection(DBHelper.DBConnectionstring))
{
con.Open();
// find internal ID of supercharger by name
using (MySqlCommand cmd = new MySqlCommand("INSERT superchargers (name, lat, lng) values (@name, @lat, @lng) ", con))
{
cmd.Parameters.AddWithValue("@name", name);
cmd.Parameters.AddWithValue("@lat", lat);
cmd.Parameters.AddWithValue("@lng", lng);
SQLTracer.TraceNQ(cmd);
}
con.Close();
}
GetSuperchargerByName(name, out int sucID);
return(sucID);
}
private static void CalculateConsumption(int journeyId)
{
try
{
using (MySqlConnection con = new MySqlConnection(DBHelper.DBConnectionstring))
{
con.Open();
double consumption_kWh = 0;
using (MySqlCommand cmd = new MySqlCommand(@"
SELECT
SUM(trip.consumption_kWh)
FROM
trip
WHERE
trip.StartPosID >= (SELECT StartPosID FROM journeys WHERE ID = @journeyID)
AND trip.EndPosID <= (SELECT EndPosID FROM journeys WHERE ID = @journeyID)
AND trip.carID = (SELECT CarID FROM journeys WHERE ID = @journeyID)
", con))
{
cmd.Parameters.AddWithValue("@journeyID", journeyId);
Tools.DebugLog(cmd);
consumption_kWh = (double)SQLTracer.TraceSc(cmd);
}
using (MySqlCommand cmd = new MySqlCommand(@"
UPDATE
journeys
SET
consumption_kWh = @consumption_kWh
WHERE
Id = @journeyID", con))
{
cmd.Parameters.AddWithValue("@journeyID", journeyId);
cmd.Parameters.AddWithValue("@consumption_kWh", consumption_kWh);
Tools.DebugLog(cmd);
SQLTracer.TraceNQ(cmd);
}
}
}
catch (Exception ex)
{
ex.ToExceptionless().FirstCarUserID().Submit();
Logfile.Log(ex.ToString());
}
}
private static void CalculateDriveDuration(int journeyId)
{
try
{
using (MySqlConnection con = new MySqlConnection(DBHelper.DBConnectionstring))
{
con.Open();
int drive_duration_minutes = 0;
using (MySqlCommand cmd = new MySqlCommand(@"
SELECT
SUM(trip.DurationMinutes)
FROM
trip
WHERE
trip.StartPosID >= (SELECT StartPosID FROM journeys WHERE ID = @journeyID)
AND trip.EndPosID <= (SELECT EndPosID FROM journeys WHERE ID = @journeyID)
AND trip.carID = (SELECT CarID FROM journeys WHERE ID = @journeyID)
", con))
{
cmd.Parameters.AddWithValue("@journeyID", journeyId);
Tools.DebugLog(cmd);
drive_duration_minutes = int.Parse(SQLTracer.TraceSc(cmd).ToString(), Tools.ciEnUS);
}
using (MySqlCommand cmd = new MySqlCommand(@"
UPDATE
journeys
SET
drive_duration_minutes = @drive_duration_minutes
WHERE
Id = @journeyID", con))
{
cmd.Parameters.AddWithValue("@journeyID", journeyId);
cmd.Parameters.AddWithValue("@drive_duration_minutes", drive_duration_minutes);
Tools.DebugLog(cmd);
SQLTracer.TraceNQ(cmd);
}
}
}
catch (Exception ex)
{
ex.ToExceptionless().FirstCarUserID().Submit();
Logfile.Log(ex.ToString());
}
}
private static void CalculateCharged(int journeyId)
{
try
{
using (MySqlConnection con = new MySqlConnection(DBHelper.DBConnectionstring))
{
con.Open();
double charged_kwh = 0;
using (MySqlCommand cmd = new MySqlCommand(@"
SELECT
SUM(chargingstate.charge_energy_added)
FROM
chargingstate
WHERE
chargingstate.Pos > (SELECT StartPosID FROM journeys WHERE ID = @journeyID)
AND chargingstate.Pos < (SELECT EndPosID FROM journeys WHERE ID = @journeyID)
AND chargingstate.carID = (SELECT CarID FROM journeys WHERE ID = @journeyID)
", con))
{
cmd.Parameters.AddWithValue("@journeyID", journeyId);
Tools.DebugLog(cmd);
charged_kwh = (double)SQLTracer.TraceSc(cmd);
}
using (MySqlCommand cmd = new MySqlCommand(@"
UPDATE
journeys
SET
charged_kwh = @charged_kwh
WHERE
Id = @journeyID", con))
{
cmd.Parameters.AddWithValue("@journeyID", journeyId);
cmd.Parameters.AddWithValue("@charged_kwh", charged_kwh);
Tools.DebugLog(cmd);
SQLTracer.TraceNQ(cmd);
}
}
}
catch (Exception ex)
{
ex.ToExceptionless().FirstCarUserID().Submit();
Logfile.Log(ex.ToString());
}
}
private void AddSuperchargerState(Newtonsoft.Json.Linq.JObject suc, ArrayList send)
{
int sucID = int.MinValue;
bool SuCfound = GetSuperchargerByName(suc["name"].ToString(), out sucID);
dynamic location = suc["location"];
double lat = location["lat"];
double lng = location["long"];
if (!SuCfound)
{
// add new entry to supercharger list in DB
sucID = AddNewSupercharger(suc["name"].ToString(), lat, lng);
}
if (suc.ContainsKey("available_stalls") &&
suc.ContainsKey("total_stalls") &&
suc.ContainsKey("site_closed") &&
bool.TryParse(suc["site_closed"].ToString(), out bool site_closed) &&
site_closed == false)
{
Tools.DebugLog($"SuC: <{suc["name"]}> <{suc["available_stalls"]}> <{suc["total_stalls"]}>");
if (int.TryParse(suc["available_stalls"].ToString(), out int available_stalls) &&
int.TryParse(suc["total_stalls"].ToString(), out int total_stalls))
{
if (total_stalls > 0)
{
if (!ContainsSupercharger(send, suc["name"].ToString()))
{
Dictionary <string, object> sendKV = new Dictionary <string, object>();
send.Add(sendKV);
sendKV.Add("n", suc["name"]);
sendKV.Add("lat", lat);
sendKV.Add("lng", lng);
sendKV.Add("ts", DateTime.UtcNow.ToString("s", Tools.ciEnUS));
sendKV.Add("a", available_stalls);
sendKV.Add("t", total_stalls);
using (MySqlConnection con = new MySqlConnection(DBHelper.DBConnectionstring))
{
con.Open();
// find internal ID of supercharger by name
using (MySqlCommand cmd = new MySqlCommand(@"
INSERT
superchargerstate(
nameid,
ts,
available_stalls,
total_stalls
)
VALUES(
@nameid,
@ts,
@available_stalls,
@total_stalls
)", con))
{
cmd.Parameters.AddWithValue("@nameid", sucID);
cmd.Parameters.AddWithValue("@ts", DateTime.Now);
cmd.Parameters.AddWithValue("@available_stalls", available_stalls);
cmd.Parameters.AddWithValue("@total_stalls", total_stalls);
SQLTracer.TraceNQ(cmd);
}
con.Close();
}
}
}
else
{
// TODO how do we handle total_stalls == 0 ?
}
}
}
else if (suc.ContainsKey("site_closed") &&
bool.TryParse(suc["site_closed"].ToString(), out site_closed) &&
site_closed)
{
Tools.DebugLog($"SuC: <{suc["name"]}> site_closed");
if (!ContainsSupercharger(send, suc["name"].ToString()))
{
Dictionary <string, object> sendKV = new Dictionary <string, object>();
send.Add(sendKV);
sendKV.Add("n", suc["name"]);
sendKV.Add("lat", lat);
sendKV.Add("lng", lng);
sendKV.Add("ts", DateTime.UtcNow.ToString("s", Tools.ciEnUS));
sendKV.Add("a", -1);
sendKV.Add("t", -1);
using (MySqlConnection con = new MySqlConnection(DBHelper.DBConnectionstring))
{
con.Open();
// find internal ID of supercharger by name
using (MySqlCommand cmd = new MySqlCommand("INSERT superchargerstate (nameid, ts, available_stalls, total_stalls) values (@nameid, @ts, @available_stalls, @total_stalls) ", con))
{
cmd.Parameters.AddWithValue("@nameid", sucID);
cmd.Parameters.AddWithValue("@ts", DateTime.Now);
cmd.Parameters.AddWithValue("@available_stalls", -1);
cmd.Parameters.AddWithValue("@total_stalls", -1);
SQLTracer.TraceNQ(cmd);
}
con.Close();
}
}
}
else if (suc.ContainsKey("site_closed") &&
bool.TryParse(suc["site_closed"].ToString(), out site_closed) &&
!site_closed)
{
Tools.DebugLog($"SuC: <{suc["name"]}> no info (fields available: available_stalls {suc.ContainsKey("available_stalls")} total_stalls {suc.ContainsKey("available_stalls")})");
Tools.DebugLog(new Tools.JsonFormatter(JsonConvert.SerializeObject(suc)).Format());
}
else
{
Tools.DebugLog($"suc ContainsKey available_stalls {suc.ContainsKey("available_stalls")} total_stalls {suc.ContainsKey("available_stalls")} site_closed {suc.ContainsKey("site_closed")}");
}
}
internal static void JourneysCreateCreate(HttpListenerRequest request, HttpListenerResponse response)
{
// in: CarID, StartPosID, EndPosId
// out: nothing
// action: create journey table entry, render result selection HTML
string data = WebServer.GetDataFromRequestInputStream(request);
dynamic r = JsonConvert.DeserializeObject(data);
int CarID = r["CarID"];
int StartPosID = Convert.ToInt32(r["StartPosID"]);
int EndPosID = Convert.ToInt32(r["EndPosID"]);
string name = r["name"];
Tools.DebugLog($"JourneysCreateCreate CarID:{CarID} StartPosID:{StartPosID} EndPosID:{EndPosID} name:{name}");
DataRow car = DBHelper.GetCar(CarID);
if (car != null && StartPosID < EndPosID && !string.IsNullOrEmpty(name))
{
try
{
using (MySqlConnection con = new MySqlConnection(DBHelper.DBConnectionstring))
{
con.Open();
using (MySqlCommand cmd = new MySqlCommand(@"
INSERT journeys (
CarID,
StartPosID,
EndPosID,
name
)
VALUES (
@CarID,
@StartPosID,
@EndPosID,
@name
)", con))
{
cmd.Parameters.AddWithValue("@CarID", CarID);
cmd.Parameters.AddWithValue("@StartPosID", StartPosID);
cmd.Parameters.AddWithValue("@EndPosID", EndPosID);
cmd.Parameters.AddWithValue("@name", name);
Tools.DebugLog(cmd);
SQLTracer.TraceNQ(cmd);
}
using (MySqlCommand cmd = new MySqlCommand(@"
SELECT
Id
FROM
journeys
WHERE
CarID = @CarID
AND StartPosID = @StartPosID
AND EndPosID = @EndPosID
ORDER BY
Id DESC
LIMIT 1", con))
{
cmd.Parameters.AddWithValue("@CarID", CarID);
cmd.Parameters.AddWithValue("@StartPosID", StartPosID);
cmd.Parameters.AddWithValue("@EndPosID", EndPosID);
cmd.Parameters.AddWithValue("@name", name);
Tools.DebugLog(cmd);
int journeyId = (int)SQLTracer.TraceSc(cmd);
_ = Task.Factory.StartNew(() =>
{
CalculateConsumption(journeyId);
CalculateDriveDuration(journeyId);
CalculateCharged(journeyId);
CalculateChargeDuration(journeyId);
}, CancellationToken.None, TaskCreationOptions.DenyChildAttach, TaskScheduler.Default);
}
}
}
catch (Exception ex)
{
ex.ToExceptionless().FirstCarUserID().Submit();
Logfile.Log(ex.ToString());
}
}
WriteString(response, "OK");
}
public async Task <string> GetDataFromWebservice()
{
string resultContent = "";
try
{
using (FormUrlEncodedContent content = new FormUrlEncodedContent(new[]
{
new KeyValuePair <string, string>("t", token)
}))
{
DateTime start = DateTime.UtcNow;
HttpResponseMessage result = await httpclient_teslalogger_de.PostAsync(new Uri("http://teslalogger.de/get_scanmytesla.php"), content).ConfigureAwait(true);
if (result.StatusCode == System.Net.HttpStatusCode.ServiceUnavailable)
{
car.CreateExeptionlessLog("ScanMyTesla", "GetDataFromWebservice Error Service Unavailable (503)", Exceptionless.Logging.LogLevel.Warn).Submit();
car.Log("SMT: Error Service Unavailable (503)");
System.Threading.Thread.Sleep(25000);
return("ERROR: 503");
}
resultContent = await result.Content.ReadAsStringAsync().ConfigureAwait(true);
DBHelper.AddMothershipDataToDB("teslalogger.de/get_scanmytesla.php", start, (int)result.StatusCode);
if (resultContent == "not found")
{
return("not found");
}
if (resultContent.Contains("Resource Limit Is Reached"))
{
car.CreateExeptionlessLog("ScanMyTesla", "Resource Limit Is Reached", Exceptionless.Logging.LogLevel.Warn).Submit();
car.Log("SMT: Resource Limit Is Reached");
Thread.Sleep(25000);
return("Resource Limit Is Reached");
}
var diff = DateTime.UtcNow - lastScanMyTeslaActive;
if (diff.TotalMinutes > 60)
{
car.CreateExeptionlessFeature("ScanMyTeslaActive").Submit();
lastScanMyTeslaActive = DateTime.UtcNow;
}
string temp = resultContent;
int i = 0;
i = temp.IndexOf("\r\n", StringComparison.Ordinal);
string id = temp.Substring(0, i);
temp = temp.Substring(i + 2);
i = temp.IndexOf("\r\n", StringComparison.Ordinal);
string date = temp.Substring(0, i);
temp = temp.Substring(i + 2);
dynamic j = JsonConvert.DeserializeObject(temp);
DateTime d = DateTime.Parse(j["d"].ToString());
car.CurrentJSON.lastScanMyTeslaReceived = d;
car.CurrentJSON.CreateCurrentJSON();
Dictionary <string, object> kv = j["dict"].ToObject <Dictionary <string, object> >();
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO `can` (`datum`, `id`, `val`, CarId) VALUES ");
bool first = true;
string sqlDate = d.ToString("yyyy-MM-dd HH:mm:ss", Tools.ciEnUS);
foreach (KeyValuePair <string, object> line in kv)
{
if (line.Value.ToString().Contains("Infinity") || line.Value.ToString().Contains("NaN"))
{
continue;
}
switch (line.Key)
{
case "2":
car.CurrentJSON.SMTCellTempAvg = Convert.ToDouble(line.Value, Tools.ciEnUS);
break;
case "5":
car.CurrentJSON.SMTCellMinV = Convert.ToDouble(line.Value, Tools.ciEnUS);
break;
case "6":
car.CurrentJSON.SMTCellAvgV = Convert.ToDouble(line.Value, Tools.ciEnUS);
break;
case "7":
car.CurrentJSON.SMTCellMaxV = Convert.ToDouble(line.Value, Tools.ciEnUS);
break;
case "9":
car.CurrentJSON.SMTACChargeTotal = Convert.ToDouble(line.Value, Tools.ciEnUS);
break;
case "11":
car.CurrentJSON.SMTDCChargeTotal = Convert.ToDouble(line.Value, Tools.ciEnUS);
break;
case "27":
car.CurrentJSON.SMTCellImbalance = Convert.ToDouble(line.Value, Tools.ciEnUS);
break;
case "28":
car.CurrentJSON.SMTBMSmaxCharge = Convert.ToDouble(line.Value, Tools.ciEnUS);
break;
case "29":
car.CurrentJSON.SMTBMSmaxDischarge = Convert.ToDouble(line.Value, Tools.ciEnUS);
break;
case "442":
if (Convert.ToDouble(line.Value, Tools.ciEnUS) == 287.6) // SNA - Signal not Available
{
car.CurrentJSON.SMTSpeed = 0;
car.Log("SMT Speed: Signal not Available");
}
else
{
car.CurrentJSON.SMTSpeed = Convert.ToDouble(line.Value, Tools.ciEnUS);
}
break;
case "43":
car.CurrentJSON.SMTBatteryPower = Convert.ToDouble(line.Value, Tools.ciEnUS);
break;
case "71":
car.CurrentJSON.SMTNominalFullPack = Convert.ToDouble(line.Value, Tools.ciEnUS);
break;
default:
break;
}
if (first)
{
first = false;
}
else
{
sb.Append(",");
}
sb.Append("('");
sb.Append(sqlDate);
sb.Append("',");
sb.Append(line.Key);
sb.Append(",");
sb.Append(Convert.ToDouble(line.Value, Tools.ciEnUS).ToString(Tools.ciEnUS));
sb.Append(",");
sb.Append(car.CarInDB);
sb.Append(")");
}
using (MySqlConnection con = new MySqlConnection(DBHelper.DBConnectionstring))
{
con.Open();
#pragma warning disable CA2100 // SQL-Abfragen auf Sicherheitsrisiken überprüfen
using (MySqlCommand cmd = new MySqlCommand(sb.ToString(), con))
#pragma warning restore CA2100 // SQL-Abfragen auf Sicherheitsrisiken überprüfen
{
SQLTracer.TraceNQ(cmd);
try
{
using (MySqlConnection con2 = new MySqlConnection(DBHelper.DBConnectionstring))
{
con2.Open();
using (MySqlCommand cmd2 = new MySqlCommand("update cars set lastscanmytesla=@lastscanmytesla where id=@id", con2))
{
cmd2.Parameters.AddWithValue("@id", car.CarInDB);
cmd2.Parameters.AddWithValue("@lastscanmytesla", DateTime.Now);
SQLTracer.TraceNQ(cmd2);
}
}
}
catch (Exception)
{ }
return("insert ok [" + kv.Keys.Count + "] " + d.ToString(Tools.ciEnUS));
}
}
}
}
catch (TaskCanceledException)
{
car.CreateExeptionlessLog("ScanMyTesla", "Timeout", Exceptionless.Logging.LogLevel.Warn).Submit();
car.Log("Scanmytesla: Timeout");
System.Threading.Thread.Sleep(60000);
}
catch (Exception ex)
{
car.CreateExceptionlessClient(ex).AddObject(resultContent, "ResultContent").Submit();
Logfile.ExceptionWriter(ex, resultContent);
Thread.Sleep(10000);
}
return("NULL");
}