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 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 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());
}
}
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");
}