コード例 #1
0
        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");
        }
コード例 #2
0
        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());
            }
        }
コード例 #3
0
        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)}&nbsp;{WebUtility.HtmlEncode(dr[0].ToString())}&nbsp;({WebUtility.HtmlEncode(dr[1].ToString())})&nbsp;-&nbsp;{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);
        }
コード例 #4
0
        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);
        }
コード例 #5
0
 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);
             }
         }
     }
 }
コード例 #6
0
 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);
 }
コード例 #7
0
        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());
            }
        }
コード例 #8
0
        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());
            }
        }
コード例 #9
0
        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());
            }
        }
コード例 #10
0
ファイル: TLStats.cs プロジェクト: bassmaster187/TeslaLogger
        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());
        }
コード例 #11
0
 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);
 }
コード例 #12
0
        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());
            }
        }
コード例 #13
0
        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);
                }
            }
        }
コード例 #14
0
        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")}");
            }
        }
コード例 #15
0
        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());
            }
        }
コード例 #16
0
        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");
        }
コード例 #17
0
        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");
        }
コード例 #18
0
        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());
            }
        }