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());
}
}
internal static void JourneysDelete(HttpListenerRequest request, HttpListenerResponse response)
{
// in: CarID, StartPosID, EndPosId
// out: CarID, StartPosID, EndPosId
// action: render really delete HTML
response.AddHeader("Content-Type", "text/html; charset=utf-8");
StringBuilder sb = new StringBuilder();
int journeyID = Convert.ToInt32(GetUrlParameterValue(request, "id"), Tools.ciEnUS);
try
{
using (MySqlConnection con = new MySqlConnection(DBHelper.DBConnectionstring))
{
con.Open();
using (MySqlCommand cmd = new MySqlCommand(@"
SELECT
journeys.name,
cars.display_name,
tripStart.Start_address,
tripEnd.End_address
FROM
journeys,
cars,
trip tripStart,
trip tripEnd
WHERE
journeys.CarID = cars.Id
AND journeys.StartPosID = tripStart.StartPosID
AND journeys.EndPosID = tripEnd.EndPosID
AND journeys.ID = @journeyID", con))
{
cmd.Parameters.AddWithValue("@journeyID", journeyID);
Tools.DebugLog(cmd);
MySqlDataReader dr = SQLTracer.TraceDR(cmd);
if (dr.Read())
{
sb.Append($@"
<tr><td>{WebUtility.HtmlEncode(TEXT_LABEL_REALLY_DELETE)} {WebUtility.HtmlEncode(dr[0].ToString())} ({WebUtility.HtmlEncode(dr[1].ToString())}) - {WebUtility.HtmlEncode(dr[2].ToString())}{WebUtility.HtmlEncode("-->")}{WebUtility.HtmlEncode(dr[3].ToString())}?</td>
<td><form action=""{EndPoints["JourneysDeleteDelete"]}""><input type=""hidden"" name=""id"" value=""{journeyID}""><button type=""submit"">{WebUtility.HtmlEncode(TEXT_BUTTON_DELETE_DELETE)}</button></form></td>
");
}
}
}
}
catch (Exception ex)
{
ex.ToExceptionless().FirstCarUserID().Submit();
Logfile.Log(ex.ToString());
sb.Append(ex.ToString());
}
WriteString(response, html1 + sb.ToString() + html2);
}
internal static void JourneysCreateStart(HttpListenerRequest request, HttpListenerResponse response)
{
string json = "";
string data = WebServer.GetDataFromRequestInputStream(request);
dynamic r = JsonConvert.DeserializeObject(data);
int CarID = r["carid"];
Tools.DebugLog($"JourneysCreateStart CarID:{CarID}");
var o = new List<object>();
o.Add(new KeyValuePair<string, string>("", "Please Select"));
try
{
using (MySqlConnection con = new MySqlConnection(DBHelper.DBConnectionstring))
{
con.Open();
using (MySqlCommand cmd = new MySqlCommand(@"
SELECT
StartPosID,
StartDate,
Start_address
FROM
trip
WHERE
CarID = @CarID
ORDER BY
StartDate", con))
{
cmd.Parameters.AddWithValue("@CarID", CarID);
Tools.DebugLog(cmd);
MySqlDataReader dr = SQLTracer.TraceDR(cmd);
while (dr.Read() && dr[0] != DBNull.Value)
{
o.Add(new KeyValuePair<string, string>(dr[0].ToString(), dr[1].ToString() + " - " + dr[2].ToString()));
}
}
}
}
catch (Exception ex)
{
ex.ToExceptionless().FirstCarUserID().Submit();
Logfile.Log(ex.ToString());
}
json = JsonConvert.SerializeObject(o);
WriteString(response, json);
}
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());
}
}
internal string Dump()
{
StringBuilder sb = new StringBuilder();
_ = sb.Append($"TeslaLogger process statistics{Environment.NewLine}");
try
{
Process proc = Process.GetCurrentProcess();
_ = sb.Append($"WorkingSet64: {proc.WorkingSet64,12}{Environment.NewLine}");
_ = sb.Append($"PeakWorkingSet64: {proc.PeakWorkingSet64,12}{Environment.NewLine}");
_ = sb.Append($"PrivateMemorySize64: {proc.PrivateMemorySize64,12}{Environment.NewLine}");
_ = sb.Append($"VirtualMemorySize64: {proc.VirtualMemorySize64,12}{Environment.NewLine}");
_ = sb.Append($"StartTime: {proc.StartTime}{Environment.NewLine}");
_ = sb.Append($"Database sizes: DB {DBHelper.Database}{Environment.NewLine}");
using (MySqlConnection con = new MySqlConnection(DBHelper.DBConnectionstring))
{
con.Open();
using (MySqlCommand cmd = new MySqlCommand(@"
SELECT
TABLE_NAME AS `Table`,
ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = @dbschema
and ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) > 0.9
ORDER BY
(DATA_LENGTH + INDEX_LENGTH)
DESC", con))
{
cmd.Parameters.AddWithValue("@dbschema", DBHelper.Database);
MySqlDataReader dr = SQLTracer.TraceDR(cmd);
while (dr.Read())
{
_ = sb.Append($" table {dr[0]} has {dr[1]}mb{Environment.NewLine}");
}
}
}
}
catch (Exception) { }
return(sb.ToString());
}
private static bool GetSuperchargerByName(string suc, out int sucID)
{
using (MySqlConnection con = new MySqlConnection(DBHelper.DBConnectionstring))
{
con.Open();
// find internal ID of supercharger by name
using (MySqlCommand cmd = new MySqlCommand("SELECT id from superchargers where name = @name", con))
{
cmd.Parameters.AddWithValue("@name", suc);
MySqlDataReader dr = SQLTracer.TraceDR(cmd);
if (dr.Read() && dr[0] != DBNull.Value)
{
if (int.TryParse(dr[0].ToString(), out sucID))
{
return(true);
}
}
}
}
sucID = int.MinValue;
return(false);
}
public void SendDegradationData()
{
if (!shareData)
{
return;
}
try
{
int ProtocolVersion = 1;
car.Log("ShareData: SendDegradationData start");
string sql = @"
SELECT
MIN(chargingstate.StartDate) AS Date,
(SELECT
LEFT(VERSION, LOCATE(' ', VERSION) - 1)
FROM
car_version
WHERE
car_version.StartDate < MIN(chargingstate.StartDate)
AND carid = @carid
ORDER BY
id DESC
LIMIT 1
) AS v,
odometer DIV 500 * 500 AS odo,
ROUND(AVG(charging_End.ideal_battery_range_km / charging_End.battery_level * 100), 0) AS 'TR',
ROUND(AVG(pos.outside_temp), 0) AS temp
FROM
charging
INNER JOIN
chargingstate
ON
charging.id = chargingstate.StartChargingID
INNER JOIN
pos
ON
chargingstate.pos = pos.id
LEFT OUTER JOIN
charging AS charging_End
ON
chargingstate.EndChargingID = charging_End.id
WHERE
odometer > 0
AND chargingstate.carid = @carid
GROUP BY
odo";
using (DataTable dt = new DataTable())
{
int ms = Environment.TickCount;
using (MySqlDataAdapter da = new MySqlDataAdapter(sql, DBHelper.DBConnectionstring))
{
da.SelectCommand.Parameters.AddWithValue("@carid", car.CarInDB);
da.SelectCommand.CommandTimeout = 600;
SQLTracer.TraceDA(dt, da);
ms = Environment.TickCount - ms;
car.Log("ShareData: SELECT degradation Data ms: " + ms);
Dictionary <string, object> d1 = new Dictionary <string, object>
{
{ "ProtocolVersion", ProtocolVersion },
{ "TaskerToken", TaskerToken } // TaskerToken is the primary key and is used to make sure data won't be imported twice
};
List <object> t = new List <object>();
d1.Add("T", t);
foreach (DataRow dr in dt.Rows)
{
Dictionary <string, object> d = new Dictionary <string, object>();
foreach (DataColumn col in dt.Columns)
{
if (col.Caption.EndsWith("Date", StringComparison.Ordinal))
{
d.Add(col.Caption, ((DateTime)dr[col.Caption]).ToString("s", Tools.ciEnUS));
}
else
{
d.Add(col.Caption, dr[col.Caption]);
}
}
t.Add(d);
}
string json = JsonConvert.SerializeObject(d1);
try
{
using (HttpClient client = new HttpClient())
{
client.Timeout = TimeSpan.FromSeconds(30);
using (StringContent content = new StringContent(json, Encoding.UTF8, "application/json"))
{
DateTime start = DateTime.UtcNow;
HttpResponseMessage result = client.PostAsync(new Uri("http://teslalogger.de/share_degradation.php"), content).Result;
string r = result.Content.ReadAsStringAsync().Result;
DBHelper.AddMothershipDataToDB("teslalogger.de/share_degradation.php", start, (int)result.StatusCode);
//resultContent = result.Content.ReadAsStringAsync();
car.Log("ShareData: " + r);
car.Log("ShareData: SendDegradationData end");
}
}
}
catch (Exception ex)
{
car.SendException2Exceptionless(ex);
car.Log("Error in ShareData:SendDegradationData " + ex.Message);
}
}
dt.Clear();
}
}
catch (Exception ex)
{
car.SendException2Exceptionless(ex);
car.Log("Error in ShareData:SendDegradationData " + ex.Message);
Logfile.WriteException(ex.ToString());
}
}
private List <object> GetChargingDT(int startid, int endid, out int count)
{
count = 0;
string sql = @"
SELECT
AVG(UNIX_TIMESTAMP(Datum)) AS Datum,
AVG(battery_level),
AVG(charger_power),
AVG(ideal_battery_range_km),
AVG(charger_voltage),
AVG(charger_phases),
AVG(charger_actual_current),
MAX(battery_heater),
(
SELECT
val
FROM
can
WHERE
can.carid = @CarID
AND can.datum < charging.Datum
AND can.datum > DATE_ADD(charging.Datum, INTERVAL -3 MINUTE)
AND id = 3
ORDER BY
can.datum DESC
LIMIT 1
) AS cell_temp
FROM
charging
WHERE
id BETWEEN @startid AND @endid
AND carid = @CarID
GROUP BY
battery_level
ORDER BY
battery_level";
using (DataTable dt = new DataTable())
{
List <object> l = new List <object>();
using (MySqlDataAdapter da = new MySqlDataAdapter(sql, DBHelper.DBConnectionstring))
{
da.SelectCommand.Parameters.AddWithValue("@CarID", car.CarInDB);
da.SelectCommand.Parameters.AddWithValue("@startid", startid);
da.SelectCommand.Parameters.AddWithValue("@endid", endid);
da.SelectCommand.CommandTimeout = 300;
SQLTracer.TraceDA(dt, da);
foreach (DataRow dr in dt.Rows)
{
Dictionary <string, object> d = new Dictionary <string, object>();
foreach (DataColumn col in dt.Columns)
{
string name = col.Caption;
name = name.Replace("AVG(", "");
name = name.Replace("MAX(", "");
name = name.Replace(")", "");
if (name == "Datum")
{
long date = Convert.ToInt64(dr[col.Caption], Tools.ciEnUS) * 1000;
d.Add(name, DBHelper.UnixToDateTime(date).ToString("s", Tools.ciEnUS));
}
else
{
d.Add(name, dr[col.Caption]);
}
}
l.Add(d);
}
count = dt.Rows.Count;
return(l);
}
}
}
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")}");
}
}
public void SendAllChargingData()
{
if (!shareData)
{
return;
}
try
{
car.Log("ShareData: SendAllChargingData start");
int ProtocolVersion = 5;
string sql = $@"SELECT
chargingstate.id AS HostId,
StartDate,
EndDate,
charging.charge_energy_added,
conn_charge_cable,
fast_charger_brand,
fast_charger_type,
fast_charger_present,
address AS pos_name,
lat,
lng,
odometer,
charging.outside_temp,
StartChargingID,
EndChargingID
FROM
chargingstate
JOIN
pos
ON
chargingstate.Pos = pos.id
JOIN
charging
ON
charging.id = chargingstate.EndChargingID
WHERE
chargingstate.carid = {car.CarInDB} AND(
EXPORT IS NULL OR EXPORT < {ProtocolVersion}
) AND(
fast_charger_present
OR address LIKE 'Supercharger%'
OR address LIKE 'Ionity%'
OR max_charger_power > 25
)
ORDER BY
StartDate";
using (DataTable dt = new DataTable())
{
int ms = Environment.TickCount;
using (MySqlDataAdapter da = new MySqlDataAdapter(sql, DBHelper.DBConnectionstring))
{
da.SelectCommand.CommandTimeout = 600;
SQLTracer.TraceDA(dt, da);
ms = Environment.TickCount - ms;
car.Log("ShareData: SELECT chargingstate ms: " + ms);
foreach (DataRow dr in dt.Rows)
{
// lat, lng
if (double.TryParse(dr["lat"].ToString(), out double lat) && double.TryParse(dr["lng"].ToString(), out double lng))
{
Address addr = Geofence.GetInstance().GetPOI(lat, lng, false);
if (addr != null && addr.IsHome)
{
car.Log("Do not share ChargingData for +home (" + addr.name + ")");
continue;
}
// get raw address w/o automatically added unicode characters
if (dr["pos_name"] != null && addr != null)
{
dr["pos_name"] = addr.rawName;
}
}
int HostId = Convert.ToInt32(dr["HostId"], Tools.ciEnUS);
Dictionary <string, object> d = new Dictionary <string, object>
{
{ "ProtocolVersion", ProtocolVersion }
};
string Firmware = car.DbHelper.GetFirmwareFromDate((DateTime)dr["StartDate"]);
d.Add("Firmware", Firmware);
d.Add("TaskerToken", TaskerToken); // TaskerToken and HostId is the primary key and is used to make sure data won't be imported twice
foreach (DataColumn col in dt.Columns)
{
if (col.Caption.EndsWith("ChargingID", StringComparison.Ordinal))
{
continue;
}
if (col.Caption.EndsWith("Date", StringComparison.Ordinal))
{
d.Add(col.Caption, ((DateTime)dr[col.Caption]).ToString("s", Tools.ciEnUS));
}
else
{
d.Add(col.Caption, dr[col.Caption]);
}
}
List <object> l = GetChargingDT(Convert.ToInt32(dr["StartChargingID"], Tools.ciEnUS), Convert.ToInt32(dr["EndChargingID"], Tools.ciEnUS), out int count);
d.Add("teslalogger_version", TeslaloggerVersion);
d.Add("charging", l);
string json = JsonConvert.SerializeObject(d);
//string resultContent = "";
try
{
using (HttpClient client = new HttpClient())
{
client.Timeout = TimeSpan.FromSeconds(30);
using (StringContent content = new StringContent(json, Encoding.UTF8, "application/json"))
{
DateTime start = DateTime.UtcNow;
HttpResponseMessage result = client.PostAsync(new Uri("http://teslalogger.de/share_charging.php"), content).Result;
string r = result.Content.ReadAsStringAsync().Result;
DBHelper.AddMothershipDataToDB("teslalogger.de/share_charging.php", start, (int)result.StatusCode);
//resultContent = result.Content.ReadAsStringAsync();
car.Log("ShareData: " + r);
if (r.Contains("ERROR"))
{
Logfile.WriteException(r + "\r\n" + json);
}
else if (r.Contains("Insert OK:"))
{
DBHelper.ExecuteSQLQuery("update chargingstate set export=" + ProtocolVersion + " where id = " + HostId);
}
}
}
}
catch (Exception ex)
{
car.SendException2Exceptionless(ex);
car.Log("ShareData: " + ex.Message);
}
}
car.Log("ShareData: SendAllChargingData finished");
}
dt.Clear();
}
}
catch (Exception ex)
{
car.SendException2Exceptionless(ex);
car.Log("Error in ShareData:SendAllChargingData " + ex.Message);
Logfile.WriteException(ex.ToString());
}
}
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");
}
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 void SendAllDrivingData()
{
if (!shareData)
{
return;
}
try
{
car.Log("ShareData: SendAllDrivingData start");
int ProtocolVersion = 1;
string sql = $@"SELECT
drivestate.id as hostid,
(`pos_end`.`odometer` - `pos_start`.`odometer`) AS `km_diff`,
(`pos_end`.`odometer` - `pos_start`.`odometer`) / (pos_start.battery_level - pos_end.battery_level) * 100 as max_range_km,
pos_start.battery_level as StartSoc,
pos_end.battery_level as EndSoc,
((`pos_start`.`ideal_battery_range_km` - `pos_end`.`ideal_battery_range_km`) * `cars`.`wh_tr`) AS `consumption_kWh`,
((((`pos_start`.`ideal_battery_range_km` - `pos_end`.`ideal_battery_range_km`) * `cars`.`wh_tr`) / (`pos_end`.`odometer` - `pos_start`.`odometer`)) * 100) AS `avg_consumption_kWh_100km`,
TIMESTAMPDIFF(MINUTE,
`drivestate`.`StartDate`,
`drivestate`.`EndDate`) AS `DurationMinutes`,
round(`pos_start`.`odometer`/5000)*5000 as odometer,
`drivestate`.`outside_temp_avg` AS `outside_temp_avg`,
`drivestate`.`speed_max` AS `speed_max`,
`drivestate`.`power_max` AS `power_max`,
`drivestate`.`power_min` AS `power_min`,
`drivestate`.`power_avg` AS `power_avg`,
(select km_diff / durationminutes * 60) as speed_avg,
meters_up, meters_down, distance_up_km, distance_down_km, distance_flat_km,
(select version from car_version where car_version.StartDate < drivestate.StartDate and car_version.carid = drivestate.carid order by id desc limit 1) as Firmware
FROM
(((`drivestate`
JOIN `pos` `pos_start` ON ((`drivestate`.`StartPos` = `pos_start`.`id`)))
JOIN `pos` `pos_end` ON ((`drivestate`.`EndPos` = `pos_end`.`id`)))
JOIN `cars` ON ((`cars`.`id` = `drivestate`.`CarID`)))
WHERE
drivestate.CarID = {car.CarInDB}
and (drivestate.export <> {ProtocolVersion} or drivestate.export is null)
and ((`pos_end`.`odometer` - `pos_start`.`odometer`) > 99)
and pos_start.battery_level is not null
and pos_end.battery_level is not null
and `pos_end`.`odometer` - `pos_start`.`odometer` < 1000";
using (DataTable dt = new DataTable())
{
int ms = Environment.TickCount;
using (MySqlDataAdapter da = new MySqlDataAdapter(sql, DBHelper.DBConnectionstring))
{
da.SelectCommand.CommandTimeout = 600;
SQLTracer.TraceDA(dt, da);
ms = Environment.TickCount - ms;
car.Log("ShareData: SELECT drivestate ms: " + ms);
if (dt.Rows.Count == 0)
{
return;
}
Dictionary <string, object> d1 = new Dictionary <string, object>
{
{ "ProtocolVersion", ProtocolVersion },
{ "TaskerToken", TaskerToken } // TaskerToken is the primary key and is used to make sure data won't be imported twice
};
List <object> t = new List <object>();
d1.Add("T", t);
foreach (DataRow dr in dt.Rows)
{
Dictionary <string, object> d = new Dictionary <string, object>();
foreach (DataColumn col in dt.Columns)
{
d.Add(col.Caption, dr[col.Caption]);
}
t.Add(d);
}
string json = JsonConvert.SerializeObject(d1);
try
{
using (HttpClient client = new HttpClient())
{
client.Timeout = TimeSpan.FromSeconds(30);
using (StringContent content = new StringContent(json, Encoding.UTF8, "application/json"))
{
DateTime start = DateTime.UtcNow;
HttpResponseMessage result = client.PostAsync(new Uri("http://teslalogger.de/share_drivestate.php"), content).Result;
string r = result.Content.ReadAsStringAsync().Result;
DBHelper.AddMothershipDataToDB("teslalogger.de/share_drivestate.php", start, (int)result.StatusCode);
//resultContent = result.Content.ReadAsStringAsync();
car.Log("ShareData: " + r);
if (r == "OK" && dt.Rows.Count > 0)
{
var ids = from myrow in dt.AsEnumerable() select myrow["hostid"];
var l = String.Join(",", ids.ToArray());
DBHelper.ExecuteSQLQuery($"update drivestate set export = {ProtocolVersion} where id in ({l})");
}
car.Log("ShareData: SendAllDrivingData end");
}
}
}
catch (Exception ex)
{
car.SendException2Exceptionless(ex);
car.Log("Error in ShareData:SendAllDrivingData " + ex.Message);
}
dt.Clear();
car.Log("ShareData: SendAllDrivingData finished");
}
}
}
catch (Exception ex)
{
car.SendException2Exceptionless(ex);
car.Log("Error in ShareData:SendAllDrivingData " + ex.Message);
Logfile.WriteException(ex.ToString());
}
}
