void ClearLogsForTracker(CTracker tracker)
{
int now = CTime.GetTime(DateTime.UtcNow);
try
{
using (CDatabase db = Configuration.inst().GetDB())
{
if (!db.IsConnected())
{
m_Logger.Push(LogLevel.ERROR, 0, "Не удалось соединиться с БД");
return;
}
int storedatatime = now - tracker.m_nDaysToStore * 86400;
using (MySqlCommand cmd = new MySqlCommand("DELETE FROM Points WHERE TrackerID = " + tracker.m_nID + " AND Time < " + storedatatime, db.connection))
cmd.ExecuteNonQuery();
/*string types = "";
* int storeunusedeventstime = now - 10 * 86400;
* for (int i = 0; i < CEventList.UsefulEvents.Length; i++)
* types += ((i > 0) ? "," : "") + (int)CEventList.UsefulEvents[i];*/
using (MySqlCommand cmd = new MySqlCommand("DELETE FROM Events WHERE TrackerID = " + tracker.m_nID + " AND Time < " + storedatatime + " AND Status=3", db.connection))
cmd.ExecuteNonQuery();
}
}
catch (Exception e)
{
m_Logger.Push(LogLevel.ERROR, 0, e.ToString());
}
}
//////////////////////////////////////////////////////////////////////
void ReloadConfig(object obj)//зачитываем данные из Ѕƒ
{
Dictionary <long, CTracker> trackersByIMEI = new Dictionary <long, CTracker>();
using (CDatabase db = Configuration.inst().GetDB())
if (db.IsConnected())
{
CTrackerList trackers = new CTrackerList();
if (!trackers.Load(db))
{
m_Logger.Push(LogLevel.ERROR, 0, "Error loading trackers list");
}
for (int i = 0; i < trackers.Count; i++)
{
trackersByIMEI[trackers[i].m_IMEI] = trackers[i];
}
////////////////////////////////////////////////////////////////////////////////////////
try
{
//вставим точки дл¤ трекеров в curpos
using (MySqlCommand cmd = new MySqlCommand("INSERT INTO curpos(TrackerID) SELECT ID FROM Trackers ON DUPLICATE KEY UPDATE curpos.TrackerID=Trackers.ID", db.connection))
cmd.ExecuteNonQuery();
//вставим точки дл¤ трекеров в lastpoint
using (MySqlCommand cmd = new MySqlCommand("INSERT INTO lastpoint(TrackerID) SELECT ID FROM Trackers ON DUPLICATE KEY UPDATE lastpoint.TrackerID=Trackers.ID", db.connection))
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
m_Logger.Push(LogLevel.ERROR, 0, ex.ToString());
}
}
else
{
m_Logger.Push(LogLevel.ERROR, 0, "Cant connect to db");
}
////////////////////////////////////////////////////////////////////////////////////////
lock (m_TrackersByIMEI)
lock (m_TrackersByID)
{
m_TrackersByIMEI.Clear();
m_TrackersByID.Clear();
foreach (CTracker tracker in trackersByIMEI.Values)
{
m_TrackersByIMEI[tracker.m_IMEI] = tracker;
m_TrackersByID[tracker.m_nID] = tracker;
}
}
}
void Load()
{
List <TrackerPacket> newPackets = new List <TrackerPacket>();
try
{
using (CDatabase db = Configuration.inst().GetDB())
{
if (!db.IsConnected())
{
m_Logger.Push(LogLevel.ERROR, 0, "Could not connect to database");
return;
}
//загрузка точек
TrackerPacketList list = new TrackerPacketList();
list.Load(db, "SELECT TrackerID, Time, Lat, Lng, Alt, Speed, Status, IO, GSMInfo FROM " + m_strTable);
for (int i = 0; i < list.Count; i++)
{
TrackerPacket packet = list[i];
if (packet.IsValid())
{
lock (m_Points)
if (!m_Points.ContainsKey(packet.m_ID) || m_Points[packet.m_ID].m_Time != packet.m_Time)
{
m_Points[packet.m_ID] = packet;
newPackets.Add(packet);
}
}
}
}
for (int i = 0; i < newPackets.Count; i++)
{
OnCurPosChange(newPackets[i]);
}
}
catch (Exception e)
{
m_Logger.Push(LogLevel.ERROR, 0, e.ToString());
}
}
//////////////////////////////////////////////////////////////////////////
void Tick(object obj)
{
m_Logger.Push(LogLevel.INFO, 0, "Clearing DB......");
try
{
DateTime dt1970 = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);
using (CDatabase db = Configuration.inst().GetDB())
{
if (!db.IsConnected())
{
m_Logger.Push(LogLevel.ERROR, 0, "Cant connect to db");
return;
}
DateTime dtNow = DateTime.Now;
long now = (dtNow.ToFileTime() - dt1970.ToFileTime()) / 10000000;
long time = now - 86400;
using (MySqlCommand cmd = new MySqlCommand("UPDATE events SET status = status | 1 where status = 2 AND Time < " + time, db.connection))
cmd.ExecuteNonQuery();
CTracker[] trackers = Global.m_ConfigMgr.GetTrackers();
for (int i = 0; i < trackers.Length; i++)
{
time = now - trackers[i].m_nDaysToStore * 86400;
//удалить выполненные
Thread.Sleep(3000);
using (MySqlCommand cmd =
new MySqlCommand("DELETE FROM Points WHERE TrackerID = " + trackers[i].m_nID + " AND Time < " + time + ";" +
"DELETE FROM Events WHERE TrackerID = " + trackers[i].m_nID + " AND Time < " + time + ";", db.connection))
cmd.ExecuteNonQuery();
}
}
}
catch (Exception e)
{
m_Logger.Push(LogLevel.ERROR, 0, e.ToString());
}
m_Logger.Push(LogLevel.INFO, 0, "Old data deleted");
}
void ClearCommonLogs()
{
try
{
using (CDatabase db = Configuration.inst().GetDB())
{
if (!db.IsConnected())
{
m_Logger.Push(LogLevel.ERROR, 0, "Не удалось соединиться с БД");
return;
}
int time = CTime.GetTime(DateTime.UtcNow) - 86400;
using (MySqlCommand cmd = new MySqlCommand("UPDATE events SET status = status | 1 where status = 2 AND Time < " + time, db.connection))
cmd.ExecuteNonQuery();
}
}
catch (Exception e)
{
m_Logger.Push(LogLevel.ERROR, 0, e.ToString());
}
}
//////////////////////////////////////////////////////////////////////
void ReloadConfig(object obj)//зачитываем данные из БД
{
CTrackerList trackers = new CTrackerList();
Dictionary <long, CTracker> trackersByIMEI = new Dictionary <long, CTracker>();
List <ulong> zones2users = new List <ulong>();
//List<ulong> trackers2users = new List<ulong>();
CUserList users = new CUserList();
using (CDatabase db = Configuration.inst().GetDB())
if (db.IsConnected())
{
if (!trackers.Load(db))
{
m_Logger.Push(LogLevel.ERROR, 0, "Ошибка загрузки списка трекеров");
}
users.Load(db);
////////////////////////////////////////////////////////////////////////////////////////
try
{
//вставим точки для трекеров в curpos
using (MySqlCommand cmd = new MySqlCommand("INSERT IGNORE INTO curpos(TrackerID) SELECT ID FROM Trackers", db.connection))
cmd.ExecuteNonQuery();
//вставим точки для трекеров в lastpoint
using (MySqlCommand cmd = new MySqlCommand("INSERT IGNORE INTO lastpoint(TrackerID) SELECT ID FROM Trackers", db.connection))
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
m_Logger.Push(LogLevel.ERROR, 0, ex.ToString());
}
}
else
{
m_Logger.Push(LogLevel.ERROR, 0, "Не удалось соединиться с БД");
}
////////////////////////////////////////////////////////////////////////////////////////
lock (m_TrackersByIMEI)
lock (m_TrackersByID)
{
m_TrackersByIMEI.Clear();
m_TrackersByID.Clear();
for (int i = 0; i < trackers.Count; i++)
{
m_TrackersByIMEI[trackers[i].m_IMEI] = trackers[i];
m_TrackersByID[trackers[i].m_nID] = trackers[i];
}
}
/*lock (m_TrackersByUserID)
* {
* m_TrackersByUserID.Clear();
* for (int i = 0; i < trackers.Count; i++)
* m_TrackersByUserID.Add(trackers[i].m_nUserID, trackers[i]);
*
* for (int i = 0; i < trackers2users.Count; i++)
* {
* int ID = (int)(trackers2users[i] & 0xFFFFFFFF);
* int userID = (int)(trackers2users[i] >> 32);
*
* CTracker tracker = GetTrackerByID(ID);
* if (tracker != null)
* m_TrackersByUserID.Add(userID, tracker);
* }
* }*/
////////////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////////////////
lock (m_Users)
{
m_Users.Clear();
for (int i = 0; i < users.Count; i++)
{
m_Users[users[i].m_nID] = users[i];
}
}
}
//////////////////////////////////////////////////////////////////////////
void Tick(object obj)
{
if (m_bRunning)
{
return;
}
m_bRunning = true;
try
{
TrackerPacketList list = new TrackerPacketList();
lock (m_Points)
{
foreach (TrackerPacket packet in m_Points4DB.Values)
{
list.Add(packet);
}
m_Points4DB.Clear();
}
if (list.Count > 0)
{
using (CDatabase db = Configuration.inst().GetDB())
{
if (db.IsConnected())
{
using (MySqlCommand cmdUpdate = new MySqlCommand("UPDATE " + m_strTable + " SET Time=?Time, Lat=?Lat, Lng=?Lng, Status=?Status, Speed=?Speed, Alt=?Alt, IO=?IO, GSMInfo=?GSMInfo WHERE TrackerID = ?ID AND Time <= ?Time", db.connection))
{
cmdUpdate.Parameters.Add("?ID", MySqlDbType.Int32);
cmdUpdate.Parameters.Add("?Time", MySqlDbType.Int32);
cmdUpdate.Parameters.Add("?Lat", MySqlDbType.Float);
cmdUpdate.Parameters.Add("?Lng", MySqlDbType.Float);
cmdUpdate.Parameters.Add("?Alt", MySqlDbType.Int32);
cmdUpdate.Parameters.Add("?Speed", MySqlDbType.Int32);
cmdUpdate.Parameters.Add("?Status", MySqlDbType.Int32);
cmdUpdate.Parameters.Add("?IO", MySqlDbType.Blob);
cmdUpdate.Parameters.Add("?GSMInfo", MySqlDbType.UInt64);
using (MySqlTransaction trans = db.connection.BeginTransaction())
{
for (int i = 0; i < list.Count; i++)
{
if (!list[i].Write2DB(cmdUpdate))
{
break;
}
}
trans.Commit();
}
}
}
else
{
m_Logger.Push(LogLevel.ERROR, 0, "Could not connect to database");
}
}
}
}
catch (Exception e)
{
m_Logger.Push(LogLevel.ERROR, 0, e.ToString());
}
m_bRunning = false;
}
//////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////
void FlushData(object obj) //записываем данные в базу данных
{
if (m_bFlushing)
{
return;
}
m_bFlushing = true;
//записать в базу все точки
lock (m_Queue)
if (m_Queue.Count > 100000)
{
m_Queue.Clear();
m_Logger.Push(LogLevel.ERROR, 0, "Points queue purged");
}
using (CDatabase db = Configuration.inst().GetDB())
{
if (!db.IsConnected())
{
m_bFlushing = false;
m_Logger.Push(LogLevel.ERROR, 0, "Cant connect to db");
return;
}
if (m_Queue.Count > 0)
{
try
{
using (MySqlCommand cmdUpdate = new MySqlCommand("UPDATE curpos SET Time=?Time, Lat=?Lat, Lng=?Lng, Status=?Status, Speed=?Speed, Alt=?Alt, IO=?IO, GSMInfo=?GSMInfo WHERE TrackerID = ?ID AND Time <= ?Time", db.connection))
using (MySqlCommand cmdUpdate1 = new MySqlCommand("UPDATE lastpoint SET Time=?Time, Lat=?Lat, Lng=?Lng, Status=?Status, Speed=?Speed, Alt=?Alt, IO=?IO, GSMInfo=?GSMInfo WHERE TrackerID = ?ID AND Time <= ?Time", db.connection))
using (MySqlCommand cmdInsert = new MySqlCommand("INSERT INTO points(TrackerID, Time, Lat, Lng, Status, Speed, Alt, IO, GSMInfo,ForwardTime,ReceivedTime) VALUES(?ID, ?Time, ?Lat, ?Lng, ?Status, ?Speed, ?Alt, ?IO, ?GSMInfo,0," + CTime.GetTime(DateTime.UtcNow) + ") ON DUPLICATE KEY UPDATE TrackerID=TrackerID", db.connection))
{
cmdUpdate.Parameters.Add("?ID", MySqlDbType.Int32); cmdInsert.Parameters.Add("?ID", MySqlDbType.Int32);
cmdUpdate.Parameters.Add("?Time", MySqlDbType.Int32); cmdInsert.Parameters.Add("?Time", MySqlDbType.Int32);
cmdUpdate.Parameters.Add("?Lat", MySqlDbType.Float); cmdInsert.Parameters.Add("?Lat", MySqlDbType.Float);
cmdUpdate.Parameters.Add("?Lng", MySqlDbType.Float); cmdInsert.Parameters.Add("?Lng", MySqlDbType.Float);
cmdUpdate.Parameters.Add("?Alt", MySqlDbType.Int32); cmdInsert.Parameters.Add("?Alt", MySqlDbType.Int32);
cmdUpdate.Parameters.Add("?Speed", MySqlDbType.Int32); cmdInsert.Parameters.Add("?Speed", MySqlDbType.Int32);
cmdUpdate.Parameters.Add("?Status", MySqlDbType.Int32); cmdInsert.Parameters.Add("?Status", MySqlDbType.Int32);
cmdUpdate.Parameters.Add("?IO", MySqlDbType.Blob); cmdInsert.Parameters.Add("?IO", MySqlDbType.Blob);
cmdUpdate.Parameters.Add("?GSMInfo", MySqlDbType.UInt64); cmdInsert.Parameters.Add("?GSMInfo", MySqlDbType.UInt64);
cmdUpdate1.Parameters.Add("?ID", MySqlDbType.Int32);
cmdUpdate1.Parameters.Add("?Time", MySqlDbType.Int32);
cmdUpdate1.Parameters.Add("?Lat", MySqlDbType.Float);
cmdUpdate1.Parameters.Add("?Lng", MySqlDbType.Float);
cmdUpdate1.Parameters.Add("?Alt", MySqlDbType.Int32);
cmdUpdate1.Parameters.Add("?Speed", MySqlDbType.Int32);
cmdUpdate1.Parameters.Add("?Status", MySqlDbType.Int32);
cmdUpdate1.Parameters.Add("?IO", MySqlDbType.Blob);
cmdUpdate1.Parameters.Add("?GSMInfo", MySqlDbType.UInt64);
using (MySqlTransaction trans = db.connection.BeginTransaction())
{
while (true)
{
lock (m_Queue)
if (m_Queue.Count > 0)
{
TrackerPacket point = m_Queue.Dequeue();
if (point.IsFixed(true) && !point.Write2DB(cmdUpdate))
{
break;
}
if (!point.Write2DB(cmdInsert))
{
break;
}
if (!point.Write2DB(cmdUpdate1))
{
break;
}
}
else
{
break;
}
}
trans.Commit();
}
}
}
catch (Exception e)
{
m_Logger.Push(LogLevel.ERROR, 0, "Points " + e.ToString());
}
}
//online режим
try
{
using (MySqlTransaction trans = db.connection.BeginTransaction())
{
using (MySqlCommand cmd1 = new MySqlCommand("UPDATE curpos SET online = 0", db.connection))
cmd1.ExecuteNonQuery();
using (MySqlCommand cmd = new MySqlCommand("UPDATE curpos SET online = 1 WHERE TrackerID = ?ID", db.connection))
{
cmd.Parameters.Add("?ID", MySqlDbType.Int32);
lock (m_SessionTrackers)
foreach (KeyValuePair <uint, long> value in m_SessionTrackers)
{
cmd.Parameters["?ID"].Value = value.Value;
cmd.ExecuteNonQuery();
}
}
trans.Commit();
}
}
catch (Exception e)
{
m_Logger.Push(LogLevel.ERROR, 0, "Events " + e.ToString());
}
}
GC.Collect();
m_bFlushing = false;
}
//////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////
void FlushData(object obj) //записываем данные в базу данных
{
if (m_bFlushing)
{
return;
}
m_bFlushing = true;
//записать в базу все точки
lock (m_Queue)
if (m_Queue.Count > 300000)
{
while (m_Queue.Count > 300000)
{
m_Queue.Dequeue();
}
m_Logger.Push(LogLevel.ERROR, 0, "points was purged from queue");
}
using (CDatabase db = Configuration.inst().GetDB())
{
if (!db.IsConnected())
{
m_bFlushing = false;
m_Logger.Push(LogLevel.ERROR, 0, "Не удалось соединиться с БД");
return;
}
DateTime dtFrom = DateTime.Now;
int nPointCount = 0;
if (m_Queue.Count > 0)
{
try
{
using (MySqlCommand cmdInsert = new MySqlCommand("INSERT IGNORE INTO points(TrackerID, Time, Lat, Lng, Status, Speed, Alt, IO, GSMInfo,ForwardTime,ReceivedTime) VALUES(?ID, ?Time, ?Lat, ?Lng, ?Status, ?Speed, ?Alt, ?IO, ?GSMInfo,?ForwardTime,?ReceivedTime)", db.connection))
{
cmdInsert.Parameters.Add("?ID", MySqlDbType.Int32);
cmdInsert.Parameters.Add("?Time", MySqlDbType.Int32);
cmdInsert.Parameters.Add("?Lat", MySqlDbType.Float);
cmdInsert.Parameters.Add("?Lng", MySqlDbType.Float);
cmdInsert.Parameters.Add("?Alt", MySqlDbType.Int32);
cmdInsert.Parameters.Add("?Speed", MySqlDbType.Int32);
cmdInsert.Parameters.Add("?Status", MySqlDbType.Int32);
cmdInsert.Parameters.Add("?IO", MySqlDbType.Blob);
cmdInsert.Parameters.Add("?GSMInfo", MySqlDbType.UInt64);
cmdInsert.Parameters.Add("?ForwardTime", MySqlDbType.Int32);
cmdInsert.Parameters.Add("?ReceivedTime", MySqlDbType.Int32);
using (MySqlTransaction trans = db.connection.BeginTransaction())
{
while (++nPointCount < 5000 && m_Queue.Count > 0)
{
TrackerPacket point = null;
lock (m_Queue)
point = m_Queue.Dequeue();
if (point == null || !point.Write2DB(cmdInsert))
{
break;
}
}
trans.Commit();
}
}
}
catch (Exception e)
{
m_Logger.Push(LogLevel.ERROR, 0, "Points " + e.ToString());
}
}
DateTime dtPoints = DateTime.Now;
DateTime dtFuel = DateTime.Now;
DateTime dtPhoto = DateTime.Now;
DateTime dtEkey = DateTime.Now;
//online режим
try
{
StringBuilder str = new StringBuilder("update curpos set online = (trackerid in (0");
lock (m_SessionTrackers)
foreach (KeyValuePair <uint, long> value in m_SessionTrackers)
{
str.Append("," + value.Value);
}
str.Append("))");
using (MySqlCommand cmd = new MySqlCommand(str.ToString(), db.connection))
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
m_Logger.Push(LogLevel.ERROR, 0, "Curpos " + e.ToString());
}
double t = ((TimeSpan)(DateTime.Now - dtFrom)).TotalMilliseconds;
double t1 = ((TimeSpan)(dtPoints - dtFrom)).TotalMilliseconds;
double t2 = ((TimeSpan)(dtFuel - dtPoints)).TotalMilliseconds;
double t3 = ((TimeSpan)(dtPhoto - dtFuel)).TotalMilliseconds;
double t4 = ((TimeSpan)(dtEkey - dtPhoto)).TotalMilliseconds;
double t5 = ((TimeSpan)(DateTime.Now - dtEkey)).TotalMilliseconds;
if (t > 300)
{
m_Logger.Push(LogLevel.WARNING, 0, "GPSQueue: " + nPointCount + " pts. Times:" + t1 + "/" + t2 + "/" + t3 + "/" + t4 + "/" + t5 + "/");
}
}
GC.Collect();
m_bFlushing = false;
}