TeslaFi-Import/Program.cs
Rama master · réplica de lectura del repositorio original.
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace TeslaFi_Import
{
class Program
{
static string DBConnectionstring = "Server=127.0.0.1;Database=teslalogger;Uid=root;Password=teslalogger;";
public static System.Globalization.CultureInfo ciEnUS = new System.Globalization.CultureInfo("en-US");
static string[] columnsToCheck = new string[] { "latitude", "longitude", "speed", "power" };
static int currentPosId = 0;
static int currentChargeid = 0;
static int currentStateId = 0;
static void Main(string[] args)
{
int id = 0;
try
{
Tools.Log(0, "***** Start TeslaFi "+ System.Reflection.Assembly.GetExecutingAssembly().GetName().Version + " Started *****");
if (Tools.IsDocker())
{
Tools.Log(0, "DOCKER Version!");
DBConnectionstring = "Server=database;Database=teslalogger;Uid=root;Password=teslalogger;";
}
if (Settings1.Default.DBConnectionstring.Length > 0)
DBConnectionstring = Settings1.Default.DBConnectionstring;
Tools.Log(0, "DBConnectionstring: " + DBConnectionstring);
DataTable dt = new DataTable();
dt.Columns.Add("data_id");
dt.Columns.Add("Date", typeof(DateTime));
dt.Columns.Add("battery_level");
dt.Columns.Add("charge_energy_added");
dt.Columns.Add("ideal_battery_range");
dt.Columns.Add("battery_range");
dt.Columns.Add("charging_state");
dt.Columns.Add("fast_charger_present");
dt.Columns.Add("charger_voltage");
dt.Columns.Add("charge_current_request_max");
dt.Columns.Add("inside_temp");
dt.Columns.Add("longitude");
dt.Columns.Add("latitude");
dt.Columns.Add("speed");
dt.Columns.Add("shift_state");
dt.Columns.Add("outside_temp");
dt.Columns.Add("odometer");
dt.Columns.Add("power");
dt.Columns.Add("elevation");
dt.Columns.Add("state");
dt.Columns.Add("charger_power");
dt.Columns.Add("charger_phases");
dt.Columns.Add("charger_actual_current");
dt.Columns.Add("charger_pilot_current");
dt.Columns.Add("charge_current_request");
dt.Columns.Add("fast_charger_type");
dt.Columns.Add("car_version");
LoadAllFiles(dt);
int dateColumnID = dt.Columns["Date"].Ordinal;
Tools.Log(0, "Delete old import");
String[] tables = new String[] { "car_version", "charging", "chargingstate", "drivestate", "pos", "state" };
foreach (var table in tables)
DeleteData(table);
DateTime firstTeslaloggerData = GetFirstTeslaloggerData();
Tools.Log(0, "First Teslalogger Data: " + firstTeslaloggerData.ToLongDateString());
Tools.Log(0, "start Parsing");
string oldShiftstate = "P";
string oldChargingstate = "";
string oldState = "";
string oldCar_version = "";
int lastMovementID = 0;
DateTime lastMovement = DateTime.MaxValue;
DataView dv = new DataView(dt, "", "Date", DataViewRowState.CurrentRows);
foreach (DataRowView drv in dv)
{
try
{
DataRow dr = drv.Row;
DateTime Date = (DateTime)dr["Date"];
id = Convert.ToInt32(dr["Data_id"]);
if (Date >= firstTeslaloggerData)
{
Tools.Log(id, "First Teslalogger Data reached. TeslaFi data skipped!");
break;
}
if (dr["car_version"] != DBNull.Value && dr["car_version"].ToString().Length > 4 && dr["car_version"].ToString() != oldCar_version)
{
oldCar_version = dr["car_version"].ToString();
InsertCarVersion(id, oldCar_version, Date);
}
if (oldChargingstate != "Charging")
{
if (InsertPos(dr))
{
lastMovementID = GetMaxPosid();
lastMovement = Date;
}
if (oldShiftstate != "P" && lastMovement != null)
{
TimeSpan ts = Date - lastMovement;
if (ts.TotalMinutes > 10)
{
Tools.Log(id, "End Driving [10 Min no movement] " + lastMovement.ToString());
oldShiftstate = "P";
CloseDriveState(lastMovement, lastMovementID);
}
}
}
string newShiftstate = dr["shift_state"].ToString();
if (oldShiftstate == "P" && (newShiftstate == "D" || newShiftstate == "R"))
{
// Driving
Tools.Log(id, "Start Driving " + Date.ToString());
oldShiftstate = newShiftstate;
StartDriveState(Date);
}
else if (newShiftstate == "P" && (oldShiftstate == "D" || oldShiftstate == "R"))
{
// End of Driving
Tools.Log(id, "End Driving " + Date.ToString());
oldShiftstate = newShiftstate;
CloseDriveState(Date);
}
string newChargingstate = dr["charging_state"].ToString();
if (newChargingstate == "Charging")
{
if (oldShiftstate != "P" && lastMovementID > 0)
{
Tools.Log(id, "End Driving [charging]" + Date.ToString());
oldShiftstate = "P";
CloseDriveState(lastMovement, lastMovementID);
}
InsertCharging(dr);
}
if (oldChargingstate == "" && newChargingstate == "Charging")
{
Tools.Log(id, "Start Charging " + Date.ToString());
oldChargingstate = newChargingstate;
StartChargingState(dr);
}
else if (oldChargingstate == "Charging" && (newChargingstate == "Complete" || newChargingstate == "Disconnected"))
{
Tools.Log(id, "Stop Charging " + Date.ToString());
oldChargingstate = "";
CloseChargingState(Date);
InsertPos(dr);
}
string newState = dr["state"].ToString();
if (newState.Length > 0 && oldState != newState)
{
oldState = newState;
Tools.Log(id, "state: " + newState);
StartState(newState, Date);
}
}
catch (Exception ex)
{
Tools.Log(id, ex.ToString());
}
}
}
catch (Exception ex)
{
Tools.Log(id, ex.ToString());
}
Tools.Log(0, "end Parsing");
}
private static void InsertCarVersion(int id, string Car_version, DateTime Date)
{
Tools.Log(id, "car_version: " + Car_version);
using (MySqlConnection con = new MySqlConnection(DBConnectionstring))
{
con.Open();
MySqlCommand cmd = new MySqlCommand("insert car_version (StartDate, version, import, carid) values (@StartDate, @version, 1, @carid)", con);
cmd.Parameters.AddWithValue("@carid", Settings1.Default.CarId);
cmd.Parameters.AddWithValue("@StartDate", Date.ToString("yyyy-MM-dd HH:mm:ss"));
cmd.Parameters.AddWithValue("@version", Car_version);
cmd.ExecuteNonQuery();
}
}
private static DateTime GetFirstTeslaloggerData()
{
DateTime dtMin = DateTime.Now;
using (MySqlConnection con = new MySqlConnection(DBConnectionstring))
{
con.Open();
MySqlCommand cmd = new MySqlCommand("SELECT StartDate FROM drivestate where import is null and carid=@carid order by id limit 1", con);
cmd.Parameters.AddWithValue("@carid", Settings1.Default.CarId);
var dr = cmd.ExecuteReader();
if (dr.Read())
{
DateTime dtDrivestate = (DateTime)dr[0];
if (dtDrivestate < dtMin)
dtMin = dtDrivestate;
}
dr.Close();
cmd = new MySqlCommand("SELECT StartDate FROM chargingstate where import is null and carid=@carid order by id limit 1", con);
cmd.Parameters.AddWithValue("@carid", Settings1.Default.CarId);
dr = cmd.ExecuteReader();
if (dr.Read())
{
DateTime dtChargestate = (DateTime)dr[0];
if (dtChargestate < dtMin)
dtMin = dtChargestate;
}
dr.Close();
}
return dtMin;
}
private static void LoadAllFiles(DataTable dt)
{
var files = System.IO.Directory.EnumerateFiles(".", "TeslaFi*.csv");
if (files.Count() == 0)
{
Tools.Log(0,"No Teslafi files found!");
}
foreach (var file in files)
LoadData(dt, file);
}
static void StartChargingState(DataRow dr)
{
DateTime Date = (DateTime)dr["Date"];
string fast_charger_type = dr["fast_charger_type"].ToString();
string fast_charger_brand = ""; // dr["fast_charger_brand"].ToString();
string conn_charge_cable = ""; // dr["conn_charge_cable"].ToString();
string fast_charger_present = dr["fast_charger_present"].ToString();
if (fast_charger_present == "False")
fast_charger_present = "0";
else if (fast_charger_present == "True")
fast_charger_present = "1";
StartChargingState(Date, fast_charger_brand, fast_charger_type, conn_charge_cable, fast_charger_present);
}
public static void CloseChargingState(DateTime Date)
{
using (MySqlConnection con = new MySqlConnection(DBConnectionstring))
{
con.Open();
MySqlCommand cmd = new MySqlCommand("update chargingstate set EndDate = @EndDate, EndChargingID = @EndChargingID where EndDate is null and carid=@carid ", con);
cmd.Parameters.AddWithValue("@carid", Settings1.Default.CarId);
cmd.Parameters.AddWithValue("@EndDate", Date);
cmd.Parameters.AddWithValue("@EndChargingID", GetMaxChargeid());
cmd.ExecuteNonQuery();
}
}
private static void InsertCharging(DataRow dr)
{
DateTime Date = (DateTime)dr["Date"];
string battery_level = dr["battery_level"].ToString();
string charge_energy_added = dr["charge_energy_added"].ToString();
string charger_power = dr["charger_power"].ToString();
double ideal_battery_range = 0;
if (dr["ideal_battery_range"].ToString() == "999.0") // Raven
ideal_battery_range = Convert.ToDouble(dr["battery_range"], ciEnUS);
else
ideal_battery_range = Convert.ToDouble(dr["ideal_battery_range"], ciEnUS);
ideal_battery_range = ideal_battery_range / (double)0.62137;
string charger_voltage = dr["charger_voltage"].ToString();
string charger_phases = dr["charger_phases"].ToString();
string charger_actual_current = dr["charger_actual_current"].ToString();
string charger_pilot_current = dr["charger_pilot_current"].ToString();
string charge_current_request = dr["charge_current_request"].ToString();
double? outside_temp = null;
if (dr["outside_temp"] != DBNull.Value && dr["outside_temp"].ToString().Length > 0)
outside_temp = Convert.ToDouble(dr["outside_temp"], ciEnUS);
InsertCharging(Date, battery_level, charge_energy_added, charger_power, ideal_battery_range, charger_voltage, charger_phases, charger_actual_current, outside_temp, true, charger_pilot_current, charge_current_request);
}
static bool CheckColumn(string columnname, DataRow dr)
{
if (dr[columnname] == DBNull.Value || dr[columnname].ToString().Length == 0 || dr[columnname].ToString() == "None")
return false;
return true;
}
static bool CheckAllColumns(DataRow dr)
{
foreach (var c in columnsToCheck)
{
bool ret = CheckColumn(c, dr);
if (!ret)
return false;
}
return true;
}
private static bool InsertPos(DataRow dr)
{
if (!CheckAllColumns(dr))
return false;
DateTime Date = (DateTime)dr["Date"];
double latitude = Convert.ToDouble(dr["latitude"], ciEnUS);
double longitude = Convert.ToDouble(dr["longitude"], ciEnUS);
if (latitude > 90 || latitude < -90)
return false;
if (longitude > 180 || longitude < -180)
return false;
int speed = (int)(Convert.ToDecimal(dr["speed"]));
int power = (int)(Convert.ToDecimal(dr["power"]));
double odometerKM = (Double)(Convert.ToDecimal(dr["odometer"], ciEnUS) / 0.62137M);
double ideal_battery_range = 0;
if (dr["ideal_battery_range"].ToString() == "999.0") // Raven
ideal_battery_range = Convert.ToDouble(dr["battery_range"], ciEnUS);
else
ideal_battery_range = Convert.ToDouble(dr["ideal_battery_range"], ciEnUS);
ideal_battery_range = ideal_battery_range / (double)0.62137;
int battery_level = Convert.ToInt32(dr["battery_level"]);
string elevation = dr["elevation"].ToString();
string inside_temp = dr["inside_temp"].ToString();
double ? outside_temp = null;
if (dr["outside_temp"] != DBNull.Value && dr["outside_temp"].ToString().Length > 0)
outside_temp = Convert.ToDouble(dr["outside_temp"], ciEnUS);
InsertPos(Date, latitude, longitude, speed, power, odometerKM, ideal_battery_range, battery_level, outside_temp, elevation, inside_temp, "0", "0","0");
if (speed > 0)
return true;
return false;
}
private static void DeleteData(string table)
{
using (MySqlConnection con = new MySqlConnection(DBConnectionstring))
{
con.Open();
MySqlCommand cmd = new MySqlCommand($"alter table {table} ADD column IF NOT EXISTS import TINYINT(1) NULL", con);
cmd.CommandTimeout = 300;
cmd.ExecuteNonQuery();
}
using (MySqlConnection con = new MySqlConnection(DBConnectionstring))
{
con.Open();
MySqlCommand cmd = new MySqlCommand($"delete from {table} where import=1", con);
cmd.CommandTimeout = 300;
int cnt = cmd.ExecuteNonQuery();
Tools.Log(0, $"Deleted {cnt} Rows from Table {table }");
}
}
private static void LoadData(DataTable dt, string Filename)
{
Tools.Log(0, "Load csv File " + Filename);
string[] columns = {""};
bool firstline = true;
int dateColumnID = 0;
var lines = System.IO.File.ReadLines(Filename);
Tools.Log(0, "Write into DataTable");
foreach (var line in lines)
{
if (firstline)
{
firstline = false;
columns = Tools.SmartSplit(line);
for (int c = 0; c < columns.Length; c++)
{
string column = columns[c];
if (column == "Date")
{
dateColumnID = c;
break;
}
}
continue;
}
DataRow dr = dt.NewRow();
string l = line;
// Some files are starting with " and that's no valid CSV file
if (l.StartsWith("\""))
l = l.Substring(1);
string[] csv = Tools.SmartSplit(l);
for (int y = 0; y < csv.Length; y++)
{
if (y == dateColumnID)
dr[y] = DateTime.Parse(csv[y].Replace("\"", ""));
else
{
string columnname = columns[y];
if (dt.Columns.Contains(columnname))
dr[columnname] = csv[y];
}
}
dt.Rows.Add(dr);
}
dt.AcceptChanges();
Tools.Log(0, "Load CSV File finished");
}
internal static void InsertPos(DateTime date, double latitude, double longitude, int speed, decimal power, double odometer, double ideal_battery_range_km, int battery_level, double? outside_temp, string altitude, string inside_temp, string battery_heater, string is_preconditioning, string sentry_mode)
{
using (MySqlConnection con = new MySqlConnection(DBConnectionstring))
{
con.Open();
MySqlCommand cmd = new MySqlCommand("insert pos (import, Datum, lat, lng, speed, power, odometer, ideal_battery_range_km, outside_temp, altitude, battery_level, inside_temp, battery_heater, is_preconditioning, sentry_mode, carid) values (1, @Datum, @lat, @lng, @speed, @power, @odometer, @ideal_battery_range_km, @outside_temp, @altitude, @battery_level, @inside_temp, @battery_heater, @is_preconditioning, @sentry_mode, @carid )", con);
cmd.Parameters.AddWithValue("@carid", Settings1.Default.CarId);
cmd.Parameters.AddWithValue("@Datum", date.ToString("yyyy-MM-dd HH:mm:ss"));
cmd.Parameters.AddWithValue("@lat", latitude.ToString(ciEnUS));
cmd.Parameters.AddWithValue("@lng", longitude.ToString(ciEnUS));
cmd.Parameters.AddWithValue("@speed", (int)((decimal)speed * 1.60934M));
cmd.Parameters.AddWithValue("@power", (int)(power * 1.35962M));
cmd.Parameters.AddWithValue("@odometer", odometer.ToString(ciEnUS));
if (ideal_battery_range_km == -1)
cmd.Parameters.AddWithValue("@ideal_battery_range_km", DBNull.Value);
else
cmd.Parameters.AddWithValue("@ideal_battery_range_km", ideal_battery_range_km.ToString(ciEnUS));
if (outside_temp == null)
cmd.Parameters.AddWithValue("@outside_temp", DBNull.Value);
else
cmd.Parameters.AddWithValue("@outside_temp", ((double)outside_temp).ToString(ciEnUS));
if (altitude.Length == 0)
cmd.Parameters.AddWithValue("@altitude", DBNull.Value);
else
{
double tempAltituge = Convert.ToDouble(altitude, ciEnUS);
if (tempAltituge < 7000)
cmd.Parameters.AddWithValue("@altitude", altitude);
else
cmd.Parameters.AddWithValue("@altitude", DBNull.Value);
}
if (battery_level == -1)
cmd.Parameters.AddWithValue("@battery_level", DBNull.Value);
else
cmd.Parameters.AddWithValue("@battery_level", battery_level.ToString());
if (inside_temp == null)
cmd.Parameters.AddWithValue("@inside_temp", DBNull.Value);
else
cmd.Parameters.AddWithValue("@inside_temp", inside_temp);
cmd.Parameters.AddWithValue("@battery_heater", battery_heater);
cmd.Parameters.AddWithValue("@is_preconditioning", is_preconditioning);
cmd.Parameters.AddWithValue("@sentry_mode", sentry_mode);
cmd.ExecuteNonQuery();
cmd = new MySqlCommand("SELECT LAST_INSERT_ID();", con);
cmd.Parameters.Clear();
currentPosId = Convert.ToInt32(cmd.ExecuteScalar());
}
}
public static DateTime UnixToDateTime(long t)
{
DateTime dt = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);
dt = dt.AddMilliseconds(t);
dt = dt.ToLocalTime();
return dt;
}
public static void StartDriveState(DateTime date)
{
using (MySqlConnection con = new MySqlConnection(DBConnectionstring))
{
con.Open();
MySqlCommand cmd = new MySqlCommand("insert drivestate (import, StartDate, StartPos, carid) values (1, @StartDate, @Pos, @carid)", con);
cmd.Parameters.AddWithValue("@carid", Settings1.Default.CarId);
cmd.Parameters.AddWithValue("@StartDate", date);
cmd.Parameters.AddWithValue("@Pos", GetMaxPosid());
cmd.ExecuteNonQuery();
}
}
public static void CloseDriveState(DateTime EndDate)
{
CloseDriveState(EndDate, GetMaxPosid());
}
public static void CloseDriveState(DateTime EndDate, int posId)
{
int StartPos = 0;
using (MySqlConnection con = new MySqlConnection(DBConnectionstring))
{
con.Open();
MySqlCommand cmd = new MySqlCommand("select StartPos from drivestate where EndDate is null and carid=@carid ", con);
cmd.Parameters.AddWithValue("@carid", Settings1.Default.CarId);
MySqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
StartPos = Convert.ToInt32(dr[0]);
}
dr.Close();
}
using (MySqlConnection con = new MySqlConnection(DBConnectionstring))
{
con.Open();
MySqlCommand cmd = new MySqlCommand("update drivestate set EndDate = @EndDate, EndPos = @Pos where EndDate is null and carid=@carid ", con);
cmd.Parameters.AddWithValue("@carid", Settings1.Default.CarId);
cmd.Parameters.AddWithValue("@EndDate", EndDate);
cmd.Parameters.AddWithValue("@Pos", posId);
cmd.ExecuteNonQuery();
}
if (StartPos != 0)
UpdateDriveStatistics(StartPos, posId);
}
public static int GetMaxPosid(bool withReverseGeocoding = true)
{
return currentPosId;
}
private static void UpdateDriveStatistics(int startPos, int endPos, bool logging = false)
{
try
{
using (MySqlConnection con = new MySqlConnection(DBConnectionstring))
{
con.Open();
MySqlCommand cmd = new MySqlCommand("SELECT avg(outside_temp) as outside_temp_avg, max(speed) as speed_max, max(power) as power_max, min(power) as power_min, avg(power) as power_avg FROM pos where id between @startpos and @endpos and carid=@carid ", con);
cmd.Parameters.AddWithValue("@carid", Settings1.Default.CarId);
cmd.Parameters.AddWithValue("@startpos", startPos);
cmd.Parameters.AddWithValue("@endpos", endPos);
MySqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
using (MySqlConnection con2 = new MySqlConnection(DBConnectionstring))
{
con2.Open();
MySqlCommand cmd2 = new MySqlCommand("update drivestate set outside_temp_avg=@outside_temp_avg, speed_max=@speed_max, power_max=@power_max, power_min=@power_min, power_avg=@power_avg where StartPos=@StartPos and EndPos=@EndPos and carid=@carid ", con2);
cmd2.Parameters.AddWithValue("@carid", Settings1.Default.CarId);
cmd2.Parameters.AddWithValue("@StartPos", startPos);
cmd2.Parameters.AddWithValue("@EndPos", endPos);
cmd2.Parameters.AddWithValue("@outside_temp_avg", dr["outside_temp_avg"]);
cmd2.Parameters.AddWithValue("@speed_max", dr["speed_max"]);
cmd2.Parameters.AddWithValue("@power_max", dr["power_max"]);
cmd2.Parameters.AddWithValue("@power_min", dr["power_min"]);
cmd2.Parameters.AddWithValue("@power_avg", dr["power_avg"]);
cmd2.ExecuteNonQuery();
}
}
}
// If Startpos doesn't have an "ideal_battery_rage_km", it will be updated from the first valid dataset
using (MySqlConnection con = new MySqlConnection(DBConnectionstring))
{
con.Open();
MySqlCommand cmd = new MySqlCommand("SELECT * FROM pos where id = @startpos", con);
cmd.Parameters.AddWithValue("@startpos", startPos);
MySqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
if (dr["ideal_battery_range_km"] == DBNull.Value)
{
DateTime dt1 = (DateTime)dr["Datum"];
dr.Close();
cmd = new MySqlCommand("SELECT * FROM pos where id > @startPos and ideal_battery_range_km is not null and battery_level is not null and carid=@carid order by id asc limit 1", con);
cmd.Parameters.AddWithValue("@carid", Settings1.Default.CarId);
cmd.Parameters.AddWithValue("@startPos", startPos);
dr = cmd.ExecuteReader();
if (dr.Read())
{
DateTime dt2 = (DateTime)dr["Datum"];
TimeSpan ts = dt2 - dt1;
object ideal_battery_range_km = dr["ideal_battery_range_km"];
object battery_level = dr["battery_level"];
if (ts.TotalSeconds < 120)
{
dr.Close();
cmd = new MySqlCommand("update pos set ideal_battery_range_km = @ideal_battery_range_km, battery_level = @battery_level where id = @startPos", con);
cmd.Parameters.AddWithValue("@startPos", startPos);
cmd.Parameters.AddWithValue("@ideal_battery_range_km", ideal_battery_range_km.ToString());
cmd.Parameters.AddWithValue("@battery_level", battery_level.ToString());
cmd.ExecuteNonQuery();
// Logfile.Log($"Trip from {dt1} ideal_battery_range_km updated!");
}
else
{
// Logfile.Log($"Trip from {dt1} ideal_battery_range_km is NULL, but last valid data is too old: {dt2}!");
}
}
}
}
}
// If Endpos doesn't have an "ideal_battery_rage_km", it will be updated from the last valid dataset
using (MySqlConnection con = new MySqlConnection(DBConnectionstring))
{
con.Open();
MySqlCommand cmd = new MySqlCommand("SELECT * FROM pos where id = @endpos", con);
cmd.Parameters.AddWithValue("@endpos", endPos);
MySqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
if (dr["ideal_battery_range_km"] == DBNull.Value)
{
DateTime dt1 = (DateTime)dr["Datum"];
dr.Close();
cmd = new MySqlCommand("SELECT * FROM pos where id < @endpos and ideal_battery_range_km is not null and battery_level is not null and carid=@carid order by id desc limit 1", con);
cmd.Parameters.AddWithValue("@carid", Settings1.Default.CarId);
cmd.Parameters.AddWithValue("@endpos", endPos);
dr = cmd.ExecuteReader();
if (dr.Read())
{
DateTime dt2 = (DateTime)dr["Datum"];
TimeSpan ts = dt1 - dt2;
object ideal_battery_range_km = dr["ideal_battery_range_km"];
object battery_level = dr["battery_level"];
if (ts.TotalSeconds < 120)
{
dr.Close();
cmd = new MySqlCommand("update pos set ideal_battery_range_km = @ideal_battery_range_km, battery_level = @battery_level where id = @endpos", con);
cmd.Parameters.AddWithValue("@endpos", endPos);
cmd.Parameters.AddWithValue("@ideal_battery_range_km", ideal_battery_range_km.ToString());
cmd.Parameters.AddWithValue("@battery_level", battery_level.ToString());
cmd.ExecuteNonQuery();
// Logfile.Log($"Trip from {dt1} ideal_battery_range_km updated!");
}
else
{
// Logfile.Log($"Trip from {dt1} ideal_battery_range_km is NULL, but last valid data is too old: {dt2}!");
}
}
}
}
}
}
catch (Exception ex)
{
// Logfile.Log(ex.ToString());
System.Diagnostics.Debug.WriteLine(ex);
}
}
internal static void InsertCharging(DateTime Date, string battery_level, string charge_energy_added, string charger_power, double ideal_battery_range, string charger_voltage, string charger_phases, string charger_actual_current, double? outside_temp, bool forceinsert, string charger_pilot_current, string charge_current_request)
{
if (charger_phases == "" || charger_phases == "None")
charger_phases = "1";
double kmRange = ideal_battery_range;
double powerkW = Convert.ToDouble(charger_power);
double waitbetween2pointsdb = 1000.0 / powerkW;
using (MySqlConnection con = new MySqlConnection(DBConnectionstring))
{
con.Open();
MySqlCommand cmd = new MySqlCommand("insert charging (import, Datum, battery_level, charge_energy_added, charger_power, ideal_battery_range_km, charger_voltage, charger_phases, charger_actual_current, outside_temp, charger_pilot_current, charge_current_request, battery_heater, carid) values (1, @Datum, @battery_level, @charge_energy_added, @charger_power, @ideal_battery_range_km, @charger_voltage, @charger_phases, @charger_actual_current, @outside_temp, @charger_pilot_current, @charge_current_request, @battery_heater, @carid)", con);
cmd.Parameters.AddWithValue("@carid", Settings1.Default.CarId);
cmd.Parameters.AddWithValue("@Datum", Date.ToString("yyyy-MM-dd HH:mm:ss"));
cmd.Parameters.AddWithValue("@battery_level", battery_level);
cmd.Parameters.AddWithValue("@charge_energy_added", charge_energy_added);
cmd.Parameters.AddWithValue("@charger_power", charger_power);
cmd.Parameters.AddWithValue("@ideal_battery_range_km", kmRange.ToString(ciEnUS));
cmd.Parameters.AddWithValue("@charger_voltage", int.Parse(charger_voltage));
cmd.Parameters.AddWithValue("@charger_phases", charger_phases);
cmd.Parameters.AddWithValue("@charger_actual_current", charger_actual_current);
cmd.Parameters.AddWithValue("@battery_heater", "0");
int i = 0;
if (charger_pilot_current != null && int.TryParse(charger_pilot_current, out i))
cmd.Parameters.AddWithValue("@charger_pilot_current", i);
else
cmd.Parameters.AddWithValue("@charger_pilot_current", DBNull.Value);
if (charge_current_request != null && int.TryParse(charge_current_request, out i))
cmd.Parameters.AddWithValue("@charge_current_request", i);
else
cmd.Parameters.AddWithValue("@charge_current_request", DBNull.Value);
if (outside_temp == null)
cmd.Parameters.AddWithValue("@outside_temp", DBNull.Value);
else
cmd.Parameters.AddWithValue("@outside_temp", ((double)outside_temp).ToString(ciEnUS));
cmd.ExecuteNonQuery();
cmd = new MySqlCommand("SELECT LAST_INSERT_ID();", con);
cmd.Parameters.Clear();
currentChargeid = Convert.ToInt32(cmd.ExecuteScalar());
}
}
public static void StartChargingState(DateTime date, string fast_charger_brand, string fast_charger_type, string conn_charge_cable, string fast_charger_present)
{
using (MySqlConnection con = new MySqlConnection(DBConnectionstring))
{
con.Open();
MySqlCommand cmd = new MySqlCommand("insert chargingstate (import, StartDate, Pos, StartChargingID, fast_charger_brand, fast_charger_type, conn_charge_cable , fast_charger_present, carid ) values (1, @StartDate, @Pos, @StartChargingID, @fast_charger_brand, @fast_charger_type, @conn_charge_cable , @fast_charger_present, @carid)", con);
cmd.Parameters.AddWithValue("@carid", Settings1.Default.CarId);
cmd.Parameters.AddWithValue("@StartDate", date);
cmd.Parameters.AddWithValue("@Pos", GetMaxPosid());
cmd.Parameters.AddWithValue("@StartChargingID", GetMaxChargeid() + 1);
cmd.Parameters.AddWithValue("@fast_charger_brand", fast_charger_brand);
cmd.Parameters.AddWithValue("@fast_charger_type", fast_charger_type);
cmd.Parameters.AddWithValue("@conn_charge_cable", conn_charge_cable);
cmd.Parameters.AddWithValue("@fast_charger_present", fast_charger_present);
cmd.ExecuteNonQuery();
}
}
static int GetMaxChargeid()
{
return currentChargeid;
}
public static void StartState(string state, DateTime Date)
{
using (MySqlConnection con = new MySqlConnection(DBConnectionstring))
{
con.Open();
MySqlCommand cmd1 = new MySqlCommand("select state from state where EndDate is null and carid=@carid and id < " + currentStateId, con);
cmd1.Parameters.AddWithValue("@carid", Settings1.Default.CarId);
MySqlDataReader dr = cmd1.ExecuteReader();
if (dr.Read())
{
if (dr[0].ToString() == state)
return;
}
dr.Close();
int MaxPosid = GetMaxPosid();
CloseState(MaxPosid, Date);
MySqlCommand cmd = new MySqlCommand("insert state (import, StartDate, state, StartPos, carid) values (1, @StartDate, @state, @StartPos, @carid)", con);
cmd.Parameters.AddWithValue("@carid", Settings1.Default.CarId);
cmd.Parameters.AddWithValue("@StartDate", Date);
cmd.Parameters.AddWithValue("@state", state);
cmd.Parameters.AddWithValue("@StartPos", MaxPosid);
cmd.ExecuteNonQuery();
cmd = new MySqlCommand("SELECT LAST_INSERT_ID();", con);
cmd.Parameters.Clear();
currentStateId = Convert.ToInt32(cmd.ExecuteScalar());
}
}
public static void CloseState(int maxPosid, DateTime Date)
{
using (MySqlConnection con = new MySqlConnection(DBConnectionstring))
{
con.Open();
MySqlCommand cmd = new MySqlCommand("update state set EndDate = @enddate, EndPos = @EndPos where EndDate is null and carid=@carid", con);
cmd.Parameters.AddWithValue("@carid", Settings1.Default.CarId);
cmd.Parameters.AddWithValue("@enddate", Date);
cmd.Parameters.AddWithValue("@EndPos", maxPosid);
cmd.ExecuteNonQuery();
}
}
}
}