SafeDispatch/SafeMobileLIB_DLL/PostgresDB.cs

3354 lines
146 KiB
C#
Raw Permalink Normal View History

2024-02-22 16:43:59 +00:00
using System;
using System.Collections.Generic;
using System.Text;
using Npgsql;
using System.Data;
namespace SafeMobileLib
{
class PostgresDB
{
private NpgsqlConnection conn;
//list all compatible database versions
private static string[] _ver = {"1.0"};
//p_user -> root user
//p_password -> root password
public static bool CreateAndInitDB_v1_0(string p_server, string p_user, string p_password, string p_port)
{
//load and execute the SQL file
return false;
}
//returns false if the current isntalled DB version is in _ver string
bool supportedDBversion()
{
return false;
}
public PostgresDB()
{
}
~PostgresDB()
{
CloseConnection();
}
public bool InitConnection(string p_server, string p_dbname, string p_user, string p_password, string p_port)
{
string connstring = String.Format("Server={0};Port={1};" +
"User Id={2};Password={3};Database={4};",
p_server, p_port, p_user, p_password, p_dbname);
// Making connection with Npgsql provider
conn = new NpgsqlConnection(connstring);
conn.Open();
//check to see if the DB versio is supported by DLL
bool support = supportedDBversion();
if (!support)
{
conn.Close();
return false;
}
return true;
}
//to be used by all queries returning a Int32
private Int32 execSQL_Int32(string p_query)
{
NpgsqlCommand cmd = new NpgsqlCommand(p_query);
NpgsqlDataReader dr = cmd.ExecuteReader();
if (dr.RecordsAffected != 1)
throw new Exception("execSQL_Int32: More than 1 row returned by statment");
dr.Read();
Int32 result = dr.GetInt32(0);
dr.Dispose();
cmd.Dispose();
return result;
}
//to be used by all queries returning a string
private string execSQL_string(string p_query)
{
return null;
}
public Int32 get_subscriber_count()
{
return execSQL_Int32("SELECT COUNT(*) from subscriber");
}
//insert a position message into DB
public bool insert_msg_pos(posMessage p_posMsg)
{
return false;
}
//##############################################################
//get historical position for a vehicle
public ArrayList get_history_positions(string p_vehName, int p_start, int p_stop)
{
return null;
}
public ArrayList get_lastPos(string p_userName)
{
return null;
}
public ArrayList get_user_vehicles(string p_userName)
{
return null;
}
public string get_vehicle_imei(string p_vehName)
{
return null;
}
//#############################################################
public void InsertXML(string p_message)
{
XmlDocument docXML = new XmlDocument();
docXML.LoadXml(p_message);
XmlElement leRoot = docXML.DocumentElement;
XmlAttributeCollection attr = leRoot.Attributes;
string id = attr["id"].InnerXml;
switch (id)
{
case "100001":
insertSchedule(p_message);break;
case "100111":
insertRFID(p_message); break;
}
}
private void insertSchedule(string p_message)
{
String LAT, LNG;
Double dlat=0, dlng=0;
XmlDocument docXML = new XmlDocument();
try
{
docXML.LoadXml(p_message);
}
catch (Exception o)
{
Console.WriteLine("Error Read XML");
throw new ArgumentException(o.Message.ToString());
}
XmlElement leRoot = docXML.DocumentElement;
XmlAttributeCollection attr = leRoot.Attributes;
LAT = attr["latitude"].InnerXml;
LNG = attr["longitude"].InnerXml;
LAT = LAT.Replace(',', '.');
LNG = LNG.Replace(',', '.');
try
{
dlat = Convert.ToDouble(LAT);
dlng = Convert.ToDouble(LNG);
}
catch (Exception ex)
{
Console.WriteLine("Erorr on convert float1");
}
if ((dlat > 180) || (dlng > 180))
{
LAT = LAT.Replace('.', ',');
LNG = LNG.Replace('.', ',');
try
{
dlat = Convert.ToDouble(LAT);
dlng = Convert.ToDouble(LNG);
}
catch (Exception ex)
{
Console.WriteLine("Erorr on convert float2");
}
}
if (MSSQL)
{
try
{
Double sc_ev = 0; Int32 sc_id = 0;
SqlCeCommand cmd;
SqlCeDataReader dr;
cmd = new SqlCeCommand("select sc_id from subscriber where imei='" + attr["subscriber"].InnerXml + "'", cn);
dr = cmd.ExecuteReader();
while (dr.Read())
{
sc_id = dr.GetInt32(0);
}
dr.Close();
sc_ev = sc_id * 10000000000 + Convert.ToInt64(attr["time"].InnerXml);
//cmd = new SqlCeCommand("INSERT INTO messages (sc_id,lat,lng,speed,di,do,timeGMT,scevtime) VALUES (" + sc_id + "," + LAT + "," + LNG + "," + attr["speed"].InnerXml + "," + attr["di"].InnerXml + "," + attr["do"].InnerXml + "," + attr["time"].InnerXml + "," + sc_ev.ToString() + ")", cn);
//cmd.ExecuteNonQuery();
cmd = new SqlCeCommand();
cmd.Connection = cn;
cmd.CommandText = "INSERT INTO messages (sc_id,lat,lng,speed,di,do,timeGMT,scevtime) VALUES (" + sc_id + ",@lat1,@lng1," + attr["speed"].InnerXml + "," + attr["di"].InnerXml + "," + attr["do"].InnerXml + "," + attr["time"].InnerXml + "," + sc_ev.ToString() + ")";
cmd.Parameters.Add("@lat1", SqlDbType.Float).Value = dlat;
cmd.Parameters.Add("@lng1", SqlDbType.Float).Value = dlng;
cmd.Prepare();
cmd.ExecuteNonQuery();
cmd = new SqlCeCommand();
cmd.Connection = cn;
cmd.CommandText = "update lastpos set lat=@lat1, lng=@lng1, speed=" + attr["speed"].InnerXml + ", di=" + attr["di"].InnerXml + ", do=" + attr["do"].InnerXml + ", timeGMT=" + attr["time"].InnerXml + ", address='' where imei='" + attr["subscriber"].InnerXml + "' and timeGMT<" + attr["time"].InnerXml;
cmd.Parameters.Add("@lat1", SqlDbType.Float).Value = dlat;
cmd.Parameters.Add("@lng1", SqlDbType.Float).Value = dlng;
cmd.Prepare();
cmd.ExecuteNonQuery();
//cmd = new SqlCeCommand("update lastpos set lat=" + LAT + ", lng=" + LNG + ", speed=" + attr["speed"].InnerXml + ", di=" + attr["di"].InnerXml + ", do=" + attr["do"].InnerXml + ", timeGMT=" + attr["time"].InnerXml + " where imei='" + attr["subscriber"].InnerXml + "' and timeGMT<" + attr["time"].InnerXml, cn);
//cmd.ExecuteNonQuery();
string query = "call insert_msg_pos('" +
attr["subscriber"].InnerXml + "','" +
attr["latitude"].InnerXml + "','" +
attr["longitude"].InnerXml + "','" +
attr["speed"].InnerXml + "','" +
attr["dgr"].InnerXml + "','" +
attr["di"].InnerXml + "','" +
attr["do"].InnerXml + "','" +
attr["time"].InnerXml + "','0',null);";
// Console.WriteLine(query);
}
catch (Exception o)
{
Console.WriteLine("File: SMdb_access.cs Meth: insertSchedule " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
}
else
{
try
{
/*MySqlCommand cmd;
string query = "call insert_msg_pos('" +
attr["subscriber"].InnerXml + "','" +
LAT + "','" +
LNG + "','" +
attr["speed"].InnerXml + "','" +
attr["dgr"].InnerXml + "','" +
attr["di"].InnerXml + "','" +
attr["do"].InnerXml + "','" +
attr["time"].InnerXml + "','0',null);";
//Console.WriteLine(query);
cmd = new MySqlCommand(query, cnMY);
cmd.ExecuteNonQuery();*/
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = cnMY;
cmd.CommandText = "call insert_msg_pos('" + attr["subscriber"].InnerXml + "',?lat1,?lng1,'" +attr["speed"].InnerXml + "','" +attr["dgr"].InnerXml + "','" +attr["di"].InnerXml + "','" +attr["do"].InnerXml + "','" +attr["time"].InnerXml + "','0',null);";
cmd.Parameters.Add("?lat1", MySqlDbType.Float).Value = dlat;
cmd.Parameters.Add("?lng1", MySqlDbType.Float).Value = dlng;
cmd.Prepare();
cmd.ExecuteNonQuery();
}
catch (MySqlException o)
{
//if (o.Number == (int)MySqlErrorCode.DuplicateKeyEntry) Console.WriteLine("Da ma e duplicate KEY1");
Console.WriteLine("X"+(Int16)o.Number+"File: SMdb_access.csX Meth: insertSchedule " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
}
}
private void insertRFID(string p_message)
{
String LAT, LNG;
Double dlat = 0, dlng = 0; Int32 sc_id = 0;
XmlDocument docXML = new XmlDocument();
try
{
docXML.LoadXml(p_message);
}
catch (Exception o)
{
Console.WriteLine("Error Read XML");
throw new ArgumentException(o.Message.ToString());
}
XmlElement leRoot = docXML.DocumentElement;
XmlAttributeCollection attr = leRoot.Attributes;
LAT = attr["latitude"].InnerXml;
LNG = attr["longitude"].InnerXml;
LAT = LAT.Replace(',', '.');
LNG = LNG.Replace(',', '.');
try
{
dlat = Convert.ToDouble(LAT);
dlng = Convert.ToDouble(LNG);
}
catch (Exception ex)
{
Console.WriteLine("Erorr on convert float1");
}
if ((dlat > 180) || (dlng > 180))
{
LAT = LAT.Replace('.', ',');
LNG = LNG.Replace('.', ',');
try
{
dlat = Convert.ToDouble(LAT);
dlng = Convert.ToDouble(LNG);
}
catch (Exception ex)
{
Console.WriteLine("Erorr on convert float2");
}
}
if (MSSQL)
{
Double sc_ev = 0;
SqlCeCommand cmd;
try
{
SqlCeDataReader dr;
cmd = new SqlCeCommand("select sc_id from subscriber where imei='" + attr["subscriber"].InnerXml + "'", cn);
dr = cmd.ExecuteReader();
while (dr.Read())
{
sc_id = dr.GetInt32(0);
}
dr.Close();
sc_ev = sc_id * 10000000000 + Convert.ToInt64(attr["time"].InnerXml);
cmd = new SqlCeCommand("INSERT INTO rfidnew (sc_id,rfid) VALUES (" + sc_id + ",'" + attr["RFID"].InnerXml + "')", cn);
cmd.ExecuteNonQuery();
cmd = new SqlCeCommand();
cmd.Connection = cn;
cmd.CommandText = "update lastpos set lat=@lat1, lng=@lng1, speed=" + attr["speed"].InnerXml + ", di=" + attr["di"].InnerXml + ", do=" + attr["do"].InnerXml + ", timeGMT=" + attr["time"].InnerXml + ", address='' where imei='" + attr["subscriber"].InnerXml + "' and timeGMT<" + attr["time"].InnerXml;
cmd.Parameters.Add("@lat1", SqlDbType.Float).Value = dlat;
cmd.Parameters.Add("@lng1", SqlDbType.Float).Value = dlng;
cmd.Prepare();
cmd.ExecuteNonQuery();
cmd = new SqlCeCommand();
cmd.Connection = cn;
cmd.CommandText = "INSERT INTO messages (sc_id,lat,lng,speed,di,do,timeGMT,rfid,scevtime) VALUES (" + sc_id + ",@lat1,@lng1," + attr["speed"].InnerXml + "," + attr["di"].InnerXml + "," + attr["do"].InnerXml + "," + attr["time"].InnerXml + ",'" + attr["RFID"].InnerXml + "'," + sc_ev.ToString() + ")";
cmd.Parameters.Add("@lat1", SqlDbType.Float).Value = dlat;
cmd.Parameters.Add("@lng1", SqlDbType.Float).Value = dlng;
cmd.Prepare();
cmd.ExecuteNonQuery();
//cmd = new SqlCeCommand("update lastpos set lat=" + LAT + ", lng=" + LNG + ", speed=" + attr["speed"].InnerXml + ", di=" + attr["di"].InnerXml + ", do=" + attr["do"].InnerXml + ", timeGMT=" + attr["time"].InnerXml + " where imei='" + attr["subscriber"].InnerXml + "' and timeGMT<" + attr["time"].InnerXml, cn);
//cmd.ExecuteNonQuery();
//cmd = new SqlCeCommand("INSERT INTO messages (sc_id,lat,lng,speed,di,do,timeGMT,rfid,scevtime) VALUES (" + sc_id + "," + LAT + "," + LNG + "," + attr["speed"].InnerXml + "," + attr["di"].InnerXml + "," + attr["do"].InnerXml + "," + attr["time"].InnerXml + ",'" + attr["RFID"].InnerXml + "'," + sc_ev.ToString() + ")", cn);
//cmd.ExecuteNonQuery();
}
catch (SqlCeException o)
{
if (o.NativeError == 25016)
{
Console.WriteLine("Double Messages");
Double pas = 0; Boolean outwhile = false;
while ((!outwhile) && (pas < 0.099))
{
try
{
pas = pas + 0.001;
sc_ev = sc_ev + 0.001;
cmd = new SqlCeCommand();
cmd.Connection = cn;
cmd.CommandText = "INSERT INTO messages (sc_id,lat,lng,speed,di,do,timeGMT,rfid,scevtime) VALUES (" + sc_id + ",@lat1,@lng1," + attr["speed"].InnerXml + "," + attr["di"].InnerXml + "," + attr["do"].InnerXml + "," + attr["time"].InnerXml + ",'" + attr["RFID"].InnerXml + "',@sc_ev)";
cmd.Parameters.Add("@lat1", SqlDbType.Float).Value = dlat;
cmd.Parameters.Add("@lng1", SqlDbType.Float).Value = dlng;
cmd.Parameters.Add("@sc_ev", SqlDbType.Float).Value = sc_ev;
cmd.Prepare();
cmd.ExecuteNonQuery();
outwhile = true;
}
catch (SqlCeException o2)
{
if (o2.NativeError == 25016) ;
else
{
Console.WriteLine("File: SMdb_access.cs Meth: insertSchedule " + o.Message + o.StackTrace);
outwhile = true;
throw new ArgumentException(o.Message.ToString());
}
}
}
}
else
{
Console.WriteLine("File: SMdb_access.cs Meth: insertSchedule " + o.Message + o.StackTrace);
throw new ArgumentException(o.Message.ToString());
}
}
}
else
{
String sc_ev ="";
MySqlCommand cmd;
try
{
MySqlDataReader dr;
cmd = new MySqlCommand("select sc_id from subscriber where imei='" + attr["subscriber"].InnerXml + "'", cnMY);
dr = cmd.ExecuteReader();
while (dr.Read())
{
sc_id = dr.GetInt32(0);
}
dr.Close();
sc_ev = sc_id.ToString() + attr["time"].InnerXml + "00";
if (sc_ev.Length == 13) sc_ev = "000" + sc_ev;
if (sc_ev.Length == 14) sc_ev = "00" + sc_ev;
if (sc_ev.Length == 15) sc_ev = "0" + sc_ev;
cmd = new MySqlCommand();
cmd.Connection = cnMY;
cmd.CommandText = "update lastpos set lat=?lat1, lng=?lng1, speed=" + attr["speed"].InnerXml + ", di=" + attr["di"].InnerXml + ", do=" + attr["do"].InnerXml + ", timeGMT=" + attr["time"].InnerXml + ", address='' where imei='" + attr["subscriber"].InnerXml + "' and timeGMT<" + attr["time"].InnerXml;
cmd.Parameters.Add("?lat1", MySqlDbType.Float).Value = dlat;
cmd.Parameters.Add("?lng1", MySqlDbType.Float).Value = dlng;
cmd.Prepare();
cmd.ExecuteNonQuery();
cmd = new MySqlCommand("INSERT INTO rfidnew VALUES (null," + sc_id + ",'" + attr["RFID"].InnerXml + "')", cnMY);
cmd.ExecuteNonQuery();
cmd = new MySqlCommand();
cmd.Connection = cnMY;
cmd.CommandText = "INSERT INTO messages (sc_id,lat,lng,speed,di,do,timeGMT,rfid,scevtime) VALUES (" + sc_id + ",?lat1,?lng1," + attr["speed"].InnerXml + "," + attr["di"].InnerXml + "," + attr["do"].InnerXml + "," + attr["time"].InnerXml + ",'" + attr["RFID"].InnerXml + "'," + sc_ev + ")";
cmd.Parameters.Add("?lat1", MySqlDbType.Float).Value = dlat;
cmd.Parameters.Add("?lng1", MySqlDbType.Float).Value = dlng;
cmd.Prepare();
cmd.ExecuteNonQuery();
//cmd = new MySqlCommand("INSERT INTO messages (sc_id,lat,lng,speed,di,do,timeGMT,rfid,scevtime) VALUES (" + sc_id + "," + LAT + "," + LNG + "," + attr["speed"].InnerXml + "," + attr["di"].InnerXml + "," + attr["do"].InnerXml + "," + attr["time"].InnerXml + ",'" + attr["RFID"].InnerXml + "'," + sc_ev + ")", cnMY);
//cmd.ExecuteNonQuery();
}
catch (MySqlException o)
{
if (o.Number == (int)MySqlErrorCode.DuplicateKeyEntry)
{
Console.WriteLine("Double Messages");
int pas = 0; String str = ""; Boolean outwhile = false;
while ((!outwhile)&&(pas<99))
{
try
{
pas++;
if (pas < 10) str = "0" + pas.ToString();
else str = pas.ToString();
sc_ev = sc_ev.Remove(sc_ev.Length - 2);
sc_ev = sc_ev + str;
cmd = new MySqlCommand();
cmd.Connection = cnMY;
cmd.CommandText = "INSERT INTO messages (sc_id,lat,lng,speed,di,do,timeGMT,rfid,scevtime) VALUES (" + sc_id + ",?lat1,?lng1," + attr["speed"].InnerXml + "," + attr["di"].InnerXml + "," + attr["do"].InnerXml + "," + attr["time"].InnerXml + ",'" + attr["RFID"].InnerXml + "'," + sc_ev + ")";
cmd.Parameters.Add("?lat1", MySqlDbType.Float).Value = dlat;
cmd.Parameters.Add("?lng1", MySqlDbType.Float).Value = dlng;
cmd.Prepare();
cmd.ExecuteNonQuery();
outwhile = true;
}
catch (MySqlException o2)
{
if (o2.Number == (int)MySqlErrorCode.DuplicateKeyEntry) ;
else
{
Console.WriteLine("File: SMdb_access.cs Meth: insertSchedule " + o.Message.ToString());
outwhile = true;
throw new ArgumentException(o.Message.ToString());
}
}
}
}
else
{
Console.WriteLine("File: SMdb_access.cs Meth: insertSchedule " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
}
}
}
public ArrayList get_all_IMEI(int gateway_id)
{
//"SELECT s.imei from subscriber s, subs_gateway g where s.sc_id=g.sc_id and g.gateway_id=" + gateway_id + " ORDER BY s.imei"
if (MSSQL)
{
ArrayList ret = new ArrayList();
SqlCeCommand cmd;
SqlCeDataReader dr;
String Tmp = "(no imei)";
try
{
cmd = new SqlCeCommand("SELECT s.imei from subscriber s, subs_gateway g where s.sc_id=g.sc_id and g.gateway_id=" + gateway_id + " ORDER BY s.imei", cn);
try
{
dr = cmd.ExecuteReader();
while (dr.Read())
{
Tmp = dr.GetString(0);
ret.Add(Tmp);
}
dr.Close();
}
catch (Exception o)
{
//Console.Write("File: SMdb_access.cs Meth: get_IMEI : ");
//Console.WriteLine(o.Message.ToString());
}
}
catch (Exception o)
{
//Console.WriteLine("File: SMdb_access.cs Meth: get_IMEI : " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
return ret;
}
else
{
ArrayList ret = new ArrayList();
MySqlCommand cmd;
MySqlDataReader dr;
String Tmp = "(no imei)";
try
{
cmd = new MySqlCommand("SELECT s.imei from subscriber s, subs_gateway g where s.sc_id=g.sc_id and g.gateway_id=" + gateway_id + " ORDER BY s.imei", cnMY);
try
{
dr = cmd.ExecuteReader();
while (dr.Read())
{
Tmp = dr.GetString(0);
ret.Add(Tmp);
}
dr.Close();
}
catch (MySqlException o)
{
//Console.Write("File: SMdb_access.cs Meth: get_IMEI : ");
//Console.WriteLine(o.Message.ToString());
}
}
catch (MySqlException o)
{
//Console.WriteLine("File: SMdb_access.cs Meth: get_IMEI : " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
return ret;
}
}
public void updateStatus(string p_IMEI, int status, int di)
{
if (MSSQL)
{
SqlCeCommand cmd;
int stat = get_status(p_IMEI);
if (stat == -1)
{
try
{
string query = "INSERT INTO mototurbo VALUES('" + p_IMEI + "'," + status + "," + di + ")";
Console.WriteLine(query);
cmd = new SqlCeCommand(query, cn);
cmd.ExecuteNonQuery();
query = "INSERT INTO logmototurbo (imei,timeGMT,status) VALUES('" + p_IMEI + "'," + DateTo70Format(DateTime.Now.ToUniversalTime()) + "," + status + ")";
Console.WriteLine(query);
cmd = new SqlCeCommand(query, cn);
cmd.ExecuteNonQuery();
}
catch (Exception o)
{
Console.WriteLine("File: SMdb_access.cs Meth: INSERT STATUS " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
}
else if (stat != status)
{
try
{
string query = "UPDATE mototurbo SET status =" + status + " where imei='" + p_IMEI + "'";
Console.WriteLine(query);
cmd = new SqlCeCommand(query, cn);
cmd.ExecuteNonQuery();
query = "INSERT INTO logmototurbo (imei,timeGMT,status) VALUES('" + p_IMEI + "'," + DateTo70Format(DateTime.Now.ToUniversalTime()) + "," + status + ")";
Console.WriteLine(query);
cmd = new SqlCeCommand(query, cn);
cmd.ExecuteNonQuery();
}
catch (Exception o)
{
Console.WriteLine("File: SMdb_access.cs Meth: UPDATE STATUS " + o.Message.ToString());
}
}
}
else
{
MySqlCommand cmd;
int stat = get_status(p_IMEI);
if (stat == -1)
{
try
{
string query = "INSERT INTO mototurbo VALUES('" + p_IMEI + "'," + status + "," + di + ")";
Console.WriteLine(query);
cmd = new MySqlCommand(query, cnMY);
cmd.ExecuteNonQuery();
query = "INSERT INTO logmototurbo VALUES('" + p_IMEI + "'," + DateTo70Format(DateTime.Now.ToUniversalTime()) + "," + status + ",null)";
Console.WriteLine(query);
cmd = new MySqlCommand(query, cnMY);
cmd.ExecuteNonQuery();
}
catch (MySqlException o)
{
Console.WriteLine("File: SMdb_access.cs Meth: INSERT STATUS " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
}
else if (stat != status)
{
try
{
string query = "UPDATE mototurbo SET status =" + status + " where imei='" + p_IMEI + "'";
Console.WriteLine(query);
cmd = new MySqlCommand(query, cnMY);
cmd.ExecuteNonQuery();
query = "INSERT INTO logmototurbo VALUES('" + p_IMEI + "'," + DateTo70Format(DateTime.Now.ToUniversalTime()) + "," + status + ",null)";
Console.WriteLine(query);
cmd = new MySqlCommand(query, cnMY);
cmd.ExecuteNonQuery();
}
catch (MySqlException o)
{
Console.WriteLine("File: SMdb_access.cs Meth: UPDATE STATUS " + o.Message.ToString());
}
}
}
}
public int get_status(String IMEI)
{
if (MSSQL)
{
int status = -1;
SqlCeCommand cmd;
SqlCeDataReader dr;
try
{
cmd = new SqlCeCommand("SELECT status from mototurbo where imei='" + IMEI + "'", cn);
try
{
dr = cmd.ExecuteReader();
while (dr.Read())
{
status = dr.GetInt32(0);
}
dr.Close();
}
catch (Exception o)
{
//Console.Write("File: SMdb_access.cs Meth: get_IMEI : ");
//Console.WriteLine(o.Message.ToString());
}
}
catch (Exception o)
{
//Console.WriteLine("File: SMdb_access.cs Meth: get_IMEI : " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
return status;
}
else
{
int status = -1;
MySqlCommand cmd;
MySqlDataReader dr;
try
{
cmd = new MySqlCommand("SELECT status from mototurbo where imei='" + IMEI + "'", cnMY);
try
{
dr = cmd.ExecuteReader();
while (dr.Read())
{
status = dr.GetInt32(0);
}
dr.Close();
}
catch (MySqlException o)
{
//Console.Write("File: SMdb_access.cs Meth: get_IMEI : ");
//Console.WriteLine(o.Message.ToString());
}
}
catch (MySqlException o)
{
//Console.WriteLine("File: SMdb_access.cs Meth: get_IMEI : " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
return status;
}
}
public Boolean Check_RFID_Exist(String RFID,Int32 Unique_id)
{
Boolean exist = false;
if (MSSQL)
{
int status = -1;
SqlCeCommand cmd;
SqlCeDataReader dr;
try
{
cmd = new SqlCeCommand("SELECT count(*) from passenger where rfid='" + RFID + "' and id<>"+Unique_id, cn);
try
{
dr = cmd.ExecuteReader();
while (dr.Read())
{
status = dr.GetInt32(0);
}
dr.Close();
}
catch (Exception o)
{
//Console.Write("File: SMdb_access.cs Meth: get_IMEI : ");
//Console.WriteLine(o.Message.ToString());
}
if (status > 0) exist = true;
}
catch (Exception o)
{
//Console.WriteLine("File: SMdb_access.cs Meth: get_IMEI : " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
return exist;
}
else
{
int status = -1;
MySqlCommand cmd;
MySqlDataReader dr;
try
{
cmd = new MySqlCommand("SELECT count(*) from passenger where rfid='" + RFID + "' and id<>"+Unique_id, cnMY);
try
{
dr = cmd.ExecuteReader();
while (dr.Read())
{
status = dr.GetInt32(0);
}
dr.Close();
}
catch (MySqlException o)
{
//Console.Write("File: SMdb_access.cs Meth: get_IMEI : ");
//Console.WriteLine(o.Message.ToString());
}
if (status > 0) exist = true;
}
catch (MySqlException o)
{
//Console.WriteLine("File: SMdb_access.cs Meth: get_IMEI : " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
return exist;
}
}
public ArrayList get_sms_todeliver(int gateway_id)
{
if (MSSQL)
{
ArrayList ret = new ArrayList();
SqlCeCommand cmd;
SqlCeDataReader dr;
smsmessage obj;
String Tmp = "(no imei)";
try
{
cmd = new SqlCeCommand("SELECT s.imei , m.timeGMT, m.idx, m.mess, s.sc_id from sms m, subscriber s, subs_gateway g where m.status=0 and s.sc_id=g.sc_id and g.gateway_id=" + gateway_id + " and s.sc_id=m.sc_id_dest ORDER BY m.timeGMT", cn);
try
{
dr = cmd.ExecuteReader();
while (dr.Read())
{
obj = new smsmessage();
obj.imei_dest = dr.GetString(0);
obj.timeGMT = (uint)dr.GetInt32(1);
obj.idx = dr.GetInt32(2);
obj.mess = dr.GetString(3);
obj.sc_id_dest = dr.GetInt32(4);
obj.status = 0;
ret.Add(obj);
}
dr.Close();
}
catch (Exception o)
{
//Console.Write("File: SMdb_access.cs Meth: get_IMEI : ");
//Console.WriteLine(o.Message.ToString());
}
}
catch (Exception o)
{
//Console.WriteLine("File: SMdb_access.cs Meth: get_IMEI : " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
return ret;
}
else
{
ArrayList ret = new ArrayList();
MySqlCommand cmd;
MySqlDataReader dr;
smsmessage obj;
String Tmp = "(no imei)";
try
{
cmd = new MySqlCommand("SELECT s.imei , m.timeGMT, m.idx, m.mess, s.sc_id from sms m, subscriber s, subs_gateway g where m.status=0 and s.sc_id=g.sc_id and g.gateway_id=" + gateway_id + " and s.sc_id=m.sc_id_dest ORDER BY m.timeGMT", cnMY);
try
{
dr = cmd.ExecuteReader();
while (dr.Read())
{
obj = new smsmessage();
obj.imei_dest = dr.GetString(0);
obj.timeGMT = dr.GetUInt32(1);
obj.idx = dr.GetInt32(2);
obj.mess = dr.GetString(3);
obj.sc_id_dest = dr.GetInt32(4);
obj.status = 0;
ret.Add(obj);
}
dr.Close();
}
catch (MySqlException o)
{
//Console.Write("File: SMdb_access.cs Meth: get_IMEI : ");
//Console.WriteLine(o.Message.ToString());
}
}
catch (MySqlException o)
{
//Console.WriteLine("File: SMdb_access.cs Meth: get_IMEI : " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
return ret;
}
}
public ArrayList get_sms_todeliverIP(String gateway_IP)
{
if (MSSQL)
{
ArrayList ret = new ArrayList();
SqlCeCommand cmd;
SqlCeDataReader dr;
smsmessage obj;
String Tmp = "(no imei)";
try
{
cmd = new SqlCeCommand("SELECT s.imei , m.timeGMT, m.idx, m.mess, s.sc_id from sms m, subscriber s, subs_gateway g where m.status=0 and s.sc_id=g.sc_id and g.gateway_ip='" + gateway_IP + "' and s.sc_id=m.sc_id_dest ORDER BY m.timeGMT", cn);
try
{
dr = cmd.ExecuteReader();
while (dr.Read())
{
obj = new smsmessage();
obj.imei_dest = dr.GetString(0);
obj.timeGMT = (uint)dr.GetInt32(1);
obj.idx = dr.GetInt32(2);
obj.mess = dr.GetString(3);
obj.sc_id_dest = dr.GetInt32(4);
obj.status = 0;
ret.Add(obj);
}
dr.Close();
}
catch (Exception o)
{
Console.Write("File: SMdb_access.cs Meth: get_sms_todeliverIP : ");
Console.WriteLine(o.Message.ToString());
}
}
catch (Exception o)
{
Console.WriteLine("File: SMdb_access.cs Meth: get_sms_todeliverIP : " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
return ret;
}
else
{
ArrayList ret = new ArrayList();
MySqlCommand cmd;
MySqlDataReader dr;
smsmessage obj;
String Tmp = "(no imei)";
try
{
cmd = new MySqlCommand("SELECT s.imei , m.timeGMT, m.idx, m.mess, s.sc_id from sms m, subscriber s, subs_gateway g where m.status=0 and s.sc_id=g.sc_id and g.gateway_ip='" + gateway_IP + "' and s.sc_id=m.sc_id_dest ORDER BY m.timeGMT", cnMY);
try
{
dr = cmd.ExecuteReader();
while (dr.Read())
{
obj = new smsmessage();
obj.imei_dest = dr.GetString(0);
obj.timeGMT = dr.GetUInt32(1);
obj.idx = dr.GetInt32(2);
obj.mess = dr.GetString(3);
obj.sc_id_dest = dr.GetInt32(4);
obj.status = 0;
ret.Add(obj);
}
dr.Close();
}
catch (MySqlException o)
{
Console.Write("File: SMdb_access.cs Meth: get_sms_todeliverIP : ");
Console.WriteLine(o.Message.ToString());
}
}
catch (MySqlException o)
{
Console.WriteLine("File: SMdb_access.cs Meth: get_sms_todeliverIP : " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
return ret;
}
}
public void update_sms_delivered(int idxmess, int status)
{
// status = 1 : GW read the msg and delivered it
// status = 2 : GW received the confirm from radio unit
if (MSSQL)
{
SqlCeCommand cmd;
try
{
string query = "UPDATE sms SET status=" + status + " where idx=" + idxmess;
Console.WriteLine(query);
cmd = new SqlCeCommand(query, cn);
cmd.ExecuteNonQuery();
}
catch (Exception o)
{
Console.WriteLine("File: SMdb_access.cs Meth: UPDATE STATUS " + o.Message.ToString());
}
}
else
{
MySqlCommand cmd;
try
{
string query = "UPDATE sms SET status=" + status + " where idx=" + idxmess;
Console.WriteLine(query);
cmd = new MySqlCommand(query, cnMY);
cmd.ExecuteNonQuery();
}
catch (MySqlException o)
{
Console.WriteLine("File: SMdb_access.cs Meth: UPDATE STATUS " + o.Message.ToString());
}
}
}
public void insert_sms_received(String imei, string mess, int type, string emailaddr)
{
if (MSSQL)
{
SqlCeCommand cmd;
SqlCeDataReader dr;
Int32 sc_id = 0;
try
{
cmd = new SqlCeCommand("SELECT sc_id from subscriber where imei='" + imei + "'", cn);
try
{
dr = cmd.ExecuteReader();
while (dr.Read())
{
sc_id = dr.GetInt32(0);
}
dr.Close();
}
catch (Exception o)
{
Console.Write("File: SMdb_access.cs Meth: insert_sms_received : ");
Console.WriteLine(o.Message.ToString());
}
}
catch (Exception o)
{
Console.WriteLine("File: SMdb_access.cs Meth: insert_sms_received : " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
try
{
string query = "INSERT INTO sms (timeGMT,sc_id_sour,sc_id_dest,mess,status,email) VALUES(" + DateTo70Format(DateTime.Now.ToUniversalTime()) + "," + sc_id + ",0,'" + mess + "'," + type + ",'')";
if (type == 7)
query = "INSERT INTO sms (timeGMT,sc_id_sour,sc_id_dest,mess,status,email) VALUES(" + DateTo70Format(DateTime.Now.ToUniversalTime()) + ",0," + sc_id + ",'" + mess + "'," + type + ",'" + emailaddr + "')";
Console.WriteLine(query);
cmd = new SqlCeCommand(query, cn);
cmd.ExecuteNonQuery();
}
catch (Exception o)
{
Console.WriteLine("File: SMdb_access.cs Meth: UPDATE STATUS " + o.Message.ToString());
}
}
else
{
MySqlCommand cmd;
MySqlDataReader dr;
Int32 sc_id = 0;
try
{
cmd = new MySqlCommand("SELECT sc_id from subscriber where imei='" + imei + "'", cnMY);
try
{
dr = cmd.ExecuteReader();
while (dr.Read())
{
sc_id = dr.GetInt32(0);
}
dr.Close();
}
catch (MySqlException o)
{
Console.Write("File: SMdb_access.cs Meth: insert_sms_received : ");
Console.WriteLine(o.Message.ToString());
}
}
catch (MySqlException o)
{
Console.WriteLine("File: SMdb_access.cs Meth: insert_sms_received : " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
try
{
string query = "INSERT INTO sms VALUES(" + DateTo70Format(DateTime.Now.ToUniversalTime()) + "," + sc_id + ",0,'" + mess + "'," + type + ",null,'')";
if (type == 7)
query = "INSERT INTO sms VALUES(" + DateTo70Format(DateTime.Now.ToUniversalTime()) + ",0," + sc_id + ",'" + mess + "'," + type + ",null,'" + emailaddr + "')";
Console.WriteLine(query);
cmd = new MySqlCommand(query, cnMY);
cmd.ExecuteNonQuery();
}
catch (MySqlException o)
{
Console.WriteLine("File: SMdb_access.cs Meth: UPDATE STATUS " + o.Message.ToString());
}
}
}
public String ReadTimeLimit(String Code)
{
if (MSSQL)
{
String Retur = "";
try
{
SqlCeCommand cmd;
SqlCeDataReader dr;
cmd = new SqlCeCommand("SELECT wait_name from run_wait where veh_name='" + Code + "'", cn);
dr = cmd.ExecuteReader();
if (dr.Read())
{
Retur = dr.GetString(0);
}
dr.Close();
}
catch (Exception o)
{
Console.WriteLine("File: SMdb_access.cs Meth: ReadTimeLimit " + o.Message.ToString());
}
return Retur;
}
else
{
String Retur = "";
try
{
MySqlCommand cmd;
MySqlDataReader dr;
cmd = new MySqlCommand("SELECT wait_name from run_wait where veh_name='" + Code + "'", cnMY);
dr = cmd.ExecuteReader();
if (dr.Read())
{
Retur = dr.GetString(0);
}
dr.Close();
}
catch (MySqlException o)
{
Console.WriteLine("File: SMdb_access.cs Meth: ReadTimeLimit " + o.Message.ToString());
}
return Retur;
}
}
public void InsertTimeLimit(String Code, String Time)
{
if (MSSQL)
{
SqlCeCommand cmd;
string query = "";
try
{
query = "DELETE from run_wait where veh_name='" + Code + "'";
cmd = new SqlCeCommand(query, cn);
cmd.ExecuteNonQuery();
query = "INSERT INTO run_wait VALUES('" + Code + "'," + Time + ")";
cmd = new SqlCeCommand(query, cn);
cmd.ExecuteNonQuery();
}
catch (Exception o)
{
Console.WriteLine("File: SMdb_access.cs Meth: run_wait " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
}
else
{
MySqlCommand cmd;
string query = "";
try
{
query = "DELETE from run_wait where veh_name='" + Code + "'";
cmd = new MySqlCommand(query, cnMY);
cmd.ExecuteNonQuery();
query = "INSERT INTO run_wait VALUES('" + Code + "'," + Time + ")";
cmd = new MySqlCommand(query, cnMY);
cmd.ExecuteNonQuery();
}
catch (MySqlException o)
{
Console.WriteLine("File: SMdb_access.cs Meth: run_wait " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
}
}
public void insert_sms_sent2email(String imei, String mess, String email)
{
if (MSSQL)
{
SqlCeCommand cmd;
SqlCeDataReader dr;
Int32 sc_id = 0;
try
{
cmd = new SqlCeCommand("SELECT sc_id from subscriber where imei='" + imei + "'", cn);
try
{
dr = cmd.ExecuteReader();
while (dr.Read())
{
sc_id = dr.GetInt32(0);
}
dr.Close();
}
catch (Exception o)
{
Console.Write("File: SMdb_access.cs Meth: insert_sms_sent2email : ");
Console.WriteLine(o.Message.ToString());
}
}
catch (Exception o)
{
Console.WriteLine("File: SMdb_access.cs Meth: insert_sms_sent2email : " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
try
{
string query = "INSERT INTO sms (timeGMT,sc_id_sour,sc_id_dest,mess,status,email) VALUES(" + DateTo70Format(DateTime.Now.ToUniversalTime()) + "," + sc_id + ",0,'" + mess + "',5,'" + email + "')";
Console.WriteLine(query);
cmd = new SqlCeCommand(query, cn);
cmd.ExecuteNonQuery();
}
catch (Exception o)
{
Console.WriteLine("File: SMdb_access.cs Meth: UPDATE STATUS " + o.Message.ToString());
}
}
else
{
MySqlCommand cmd;
MySqlDataReader dr;
Int32 sc_id = 0;
try
{
cmd = new MySqlCommand("SELECT sc_id from subscriber where imei='" + imei + "'", cnMY);
try
{
dr = cmd.ExecuteReader();
while (dr.Read())
{
sc_id = dr.GetInt32(0);
}
dr.Close();
}
catch (MySqlException o)
{
Console.Write("File: SMdb_access.cs Meth: insert_sms_sent2email : ");
Console.WriteLine(o.Message.ToString());
}
}
catch (MySqlException o)
{
Console.WriteLine("File: SMdb_access.cs Meth: insert_sms_sent2email : " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
try
{
string query = "INSERT INTO sms VALUES(" + DateTo70Format(DateTime.Now.ToUniversalTime()) + "," + sc_id + ",0,'" + mess + "',5,null,'" + email + "')";
Console.WriteLine(query);
cmd = new MySqlCommand(query, cnMY);
cmd.ExecuteNonQuery();
}
catch (MySqlException o)
{
Console.WriteLine("File: SMdb_access.cs Meth: UPDATE STATUS " + o.Message.ToString());
}
}
}
public void CloseConnection()
{
if (MSSQL)
{
if (cn != null)
cn.Close();
}
else
{
if (cnMY != null)
cnMY.Close();
}
}
public void updateDi(string p_IMEI, int di)
{
if (MSSQL)
{
SqlCeCommand cmd;
try
{
string query = "UPDATE mototurbo SET di =" + di + " where imei='" + p_IMEI + "'";
Console.WriteLine(query);
cmd = new SqlCeCommand(query, cn);
cmd.ExecuteNonQuery();
}
catch (Exception o)
{
Console.WriteLine("File: SMdb_access.cs Meth: UPDATE STATUS " + o.Message.ToString());
}
}
else
{
MySqlCommand cmd;
try
{
string query = "UPDATE mototurbo SET di =" + di + " where imei='" + p_IMEI + "'";
Console.WriteLine(query);
cmd = new MySqlCommand(query, cnMY);
cmd.ExecuteNonQuery();
}
catch (MySqlException o)
{
Console.WriteLine("File: SMdb_access.cs Meth: UPDATE STATUS " + o.Message.ToString());
}
}
}
public Boolean addVehicleToDb(String _imei, String _user, String _name, String _serialNumber, Int32 DI,Boolean motoTRBO)
{
String SerialNr = "";
if (!motoTRBO) SerialNr = _serialNumber;
if (MSSQL)
{
SqlCeCommand cmd=null;
try
{
cmd = new SqlCeCommand("SELECT count(imei) FROM subscriber where imei='" + _imei + "'", cn);
object temp = cmd.ExecuteScalar();
if (temp.ToString() == "0")
{
string id_subscriber = "", id_vehicle = "", id_user = "";
cmd = new SqlCeCommand("INSERT INTO vehicle (lp,name,driver_id,time_route) VALUES('" + SerialNr + "' ,'" + _name + "',1,0)", cn);
cmd.ExecuteNonQuery();
cmd = new SqlCeCommand("INSERT INTO subscriber (imei) VALUES('" + _imei + "')", cn);
cmd.ExecuteNonQuery();
//get vehicle id
cmd = new SqlCeCommand("SELECT max(id) FROM vehicle ", cn);
id_vehicle = cmd.ExecuteScalar().ToString();
//get subscriber id
cmd = new SqlCeCommand("SELECT max(sc_id) FROM subscriber ", cn);
id_subscriber = cmd.ExecuteScalar().ToString();
//get user id
cmd = new SqlCeCommand("SELECT userId FROM users Where login ='" + _user + "'", cn);
id_user = cmd.ExecuteScalar().ToString();
if (id_vehicle.Length > 0 && id_subscriber.Length > 0 && id_user.Length > 0)
{
cmd = new SqlCeCommand("INSERT INTO vehicle_user (veh_id,user_id,assigned) VALUES(" + id_vehicle + "," + id_user + ",'2007-01-01')", cn);
cmd.ExecuteNonQuery();
cmd = new SqlCeCommand("INSERT INTO subscriber_history (sc_id,veh_id,assigned) VALUES(" + id_subscriber + "," + id_vehicle + ",'2007-01-01')", cn);
cmd.ExecuteNonQuery();
cmd = new SqlCeCommand("DELETE FROM lastpos where imei='" + _imei + "'", cn);
cmd.ExecuteNonQuery();
SqlCeCommand cmd2 = new SqlCeCommand();
cmd2.Connection = cn;
cmd2.CommandText = "INSERT INTO lastpos (imei,lastPos_fk,lat,lng,speed,heading,di,do,timeGMT) VALUES('" + _imei + "',1,@lat1,@lng1,0,0,0,0,0)";//42.06054,-88.02397
cmd2.Parameters.Add("@lat1", SqlDbType.Float).Value = 42.06054;
cmd2.Parameters.Add("@lng1", SqlDbType.Float).Value = -88.02397;
cmd2.Prepare();
cmd2.ExecuteNonQuery();
if (!motoTRBO)
{
cmd = new SqlCeCommand("INSERT INTO emergdi (sc_id,DI) VALUES(" + id_subscriber + "," + DI.ToString() + ")", cn);
cmd.ExecuteNonQuery();
}
}
MessageBox.Show("Vehicle " + _name + " added !", "successfuly!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
else
{
MessageBox.Show("There allready is a vehicle with IMEI : " + _imei + " in db ", "error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return false;
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
Console.WriteLine(cmd.CommandText);
MessageBox.Show("SqlCe connection Failed !!!!", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return false;
}
return true;
}
else
{
MySqlCommand cmd;
MySqlDataReader Reader;
try
{
cmd = new MySqlCommand("SELECT count(imei) FROM subscriber where imei='" + _imei + "'", cnMY);
object temp = cmd.ExecuteScalar();
if (temp.ToString() == "0")
{
string id_subscriber = "", id_vehicle = "", id_user = "";
cmd = new MySqlCommand("INSERT INTO vehicle VALUES(null,'" + SerialNr + "' ,'" + _name + "',1,0)", cnMY);
cmd.ExecuteNonQuery();
cmd = new MySqlCommand("INSERT INTO subscriber VALUES(null,'" + _imei + "')", cnMY);
cmd.ExecuteNonQuery();
//get vehicle id
cmd = new MySqlCommand("SELECT max(id) FROM vehicle ", cnMY);
Reader = cmd.ExecuteReader();
while (Reader.Read())
{
id_vehicle = Reader.GetInt32(0).ToString();
}
Reader.Close();
//get subscriber id
cmd = new MySqlCommand("SELECT max(sc_id) FROM subscriber ", cnMY);
Reader = cmd.ExecuteReader();
while (Reader.Read())
{
id_subscriber = Reader.GetInt32(0).ToString();
}
Reader.Close();
//get user id
cmd = new MySqlCommand("SELECT userId FROM users Where login ='" + _user + "'", cnMY);
Reader = cmd.ExecuteReader();
while (Reader.Read())
{
id_user = Reader.GetInt32(0).ToString();
}
Reader.Close();
if (id_vehicle.Length > 0 && id_subscriber.Length > 0 && id_user.Length > 0)
{
cmd = new MySqlCommand("INSERT INTO vehicle_user VALUES(" + id_vehicle + "," + id_user + ",'2007-01-01')", cnMY);
cmd.ExecuteNonQuery();
cmd = new MySqlCommand("INSERT INTO subscriber_history VALUES(" + id_subscriber + "," + id_vehicle + ",'2007-01-01',null)", cnMY);
cmd.ExecuteNonQuery();
cmd = new MySqlCommand("DELETE FROM lastpos where imei='" + _imei + "'", cnMY);
cmd.ExecuteNonQuery();
//cmd = new MySqlCommand("INSERT INTO lastpos VALUES('" + _imei + "',1,42.06054,-88.02397,0,0,0,0,0)", cnMY);
//cmd.ExecuteNonQuery();
MySqlCommand cmd2 = new MySqlCommand();
cmd2.Connection = cnMY;
cmd2.CommandText = "INSERT INTO lastpos (imei,lastPos_fk,lat,lng,speed,heading,di,do,timeGMT) VALUES('" + _imei + "',1,?lat1,?lng1,0,0,0,0,0)";
cmd2.Parameters.Add("?lat1", MySqlDbType.Float).Value = 42.06054;
cmd2.Parameters.Add("?lng1", MySqlDbType.Float).Value = -88.02397;
cmd2.Prepare();
cmd2.ExecuteNonQuery();
if (!motoTRBO)
{
cmd = new MySqlCommand("INSERT INTO emergdi (sc_id,DI) VALUES(" + id_subscriber + "," + DI.ToString() + ")", cnMY);
cmd.ExecuteNonQuery();
}
}
MessageBox.Show("Vehicle " + _name + " added !", "successfuly!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
else
{
MessageBox.Show("There allready is a vehicle with IMEI : " + _imei + " in db ", "error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return false;
}
}
catch (MySqlException ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
MessageBox.Show("MySql connection Failed !!!!", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return false;
}
return true;
}
}
public void Insert_Zone_Alarm(Int32 p_sc_id, UInt32 p_timeGMT, Int32 p_zone_id, Int32 p_action,Boolean Zone)
{
string query = "";
if (MSSQL)
{
try
{
SqlCeCommand cmd;
if (Zone) query = "INSERT INTO geozoneinout (sc_id,timeGMT,zone_id,action,type) VALUES(" + p_sc_id + "," + p_timeGMT + "," + p_zone_id + "," + p_action + ",1)";
else query = "INSERT INTO geozoneinout (sc_id,timeGMT,zone_id,action,type) VALUES(" + p_sc_id + "," + p_timeGMT + "," + p_zone_id + "," + p_action + ",2)";
cmd = new SqlCeCommand(query, cn);
cmd.ExecuteNonQuery();
}
catch (Exception o)
{
Console.WriteLine("File: SMdb_access.cs Meth: Insert_Zone_Alarm " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
}
else
{
try
{
MySqlCommand cmd;
if (Zone) query = "INSERT INTO geozoneinout VALUES(" + p_sc_id + "," + p_timeGMT + "," + p_zone_id + "," + p_action + ",null,0,1)";
else query = "INSERT INTO geozoneinout VALUES(" + p_sc_id + "," + p_timeGMT + "," + p_zone_id + "," + p_action + ",null,0,2)";
cmd = new MySqlCommand(query, cnMY);
cmd.ExecuteNonQuery();
}
catch (MySqlException o)
{
Console.WriteLine("File: SMdb_access.cs Meth: Insert_Zone_Alarm " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
}
}
public int getSC_ID_from_IMEI(string Imei)
{
int temp = 0;
if (MSSQL)
{
SqlCeCommand cmd;
SqlCeDataReader Reader;
try
{
cmd = new SqlCeCommand("SELECT sc_id from subscriber where imei='" + Imei + "'", cn);
Reader = cmd.ExecuteReader();
if (Reader.Read())
{
if (Reader.IsDBNull(0)) temp = 0;
else temp = Reader.GetInt32(0);
}
Reader.Close();
}
catch (Exception o)
{
Console.WriteLine("File: SMdb_access.cs Meth: getSC_ID_from_IMEI : " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
}
else
{
MySqlCommand cmd;
MySqlDataReader Reader;
try
{
/* cmd = new MySqlCommand("SELECT sc_id from subscriber where imei='" + Imei + "'", cnMY);
try
{
temp = (int)cmd.ExecuteScalar();
}
catch (MySqlException o)
{
Console.Write("File: SMdb_access.cs Meth: getSC_ID_from_IMEI : ");
Console.WriteLine(o.Message.ToString());
}*/
cmd = new MySqlCommand("SELECT sc_id from subscriber where imei='" + Imei + "'", cnMY);
Reader = cmd.ExecuteReader();
if (Reader.Read())
{
if (Reader.IsDBNull(0)) temp = 0;
else temp = Reader.GetInt32(0);
}
Reader.Close();
}
catch (MySqlException o)
{
Console.WriteLine("File: SMdb_access.cs Meth: getSC_ID_from_IMEI : " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
}
return temp;
}
public void assign_GW_ip_2_unit(string imei, string gateway_ip)
{
if (MSSQL)
{
SqlCeCommand cmd;
try
{
cmd = new SqlCeCommand("INSERT INTO subs_gateway (sc_id, gateway_ip) VALUES(" + getSC_ID_from_IMEI(imei) + ",'" + gateway_ip + "')", cn);
cmd.ExecuteNonQuery();
}
catch (Exception o)
{
Console.WriteLine("File: SMdb_access.cs Meth: run_wait " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
}
else
{
MySqlCommand cmd;
try
{
cmd = new MySqlCommand("INSERT INTO subs_gateway (sc_id,gateway_ip) VALUES(" + getSC_ID_from_IMEI(imei) + ",'" + gateway_ip + "')", cnMY);
cmd.ExecuteNonQuery();
}
catch (MySqlException o)
{
Console.WriteLine("File: SMdb_access.cs Meth: run_wait " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
}
}
public ArrayList get_all_IMEI_by_GW_ip(string gateway_ip)
{
if (MSSQL)
{
ArrayList ret = new ArrayList();
SqlCeCommand cmd;
SqlCeDataReader dr;
String Tmp = "(no imei)";
try
{
cmd = new SqlCeCommand("SELECT s.imei from subscriber s, subs_gateway g where s.sc_id=g.sc_id and g.gateway_ip='" + gateway_ip + "' ORDER BY s.imei", cn);
try
{
dr = cmd.ExecuteReader();
while (dr.Read())
{
Tmp = dr.GetString(0);
ret.Add(Tmp);
}
dr.Close();
}
catch (Exception o)
{
Console.Write("File: SMdb_access.cs Meth: get_all_IMEI_by_GW_ip : ");
Console.WriteLine(o.Message.ToString());
}
}
catch (Exception o)
{
Console.WriteLine("File: SMdb_access.cs Meth: get_all_IMEI_by_GW_ip : " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
return ret;
}
else
{
ArrayList ret = new ArrayList();
MySqlCommand cmd;
MySqlDataReader dr;
String Tmp = "(no imei)";
try
{
cmd = new MySqlCommand("SELECT s.imei from subscriber s, subs_gateway g where s.sc_id=g.sc_id and g.gateway_ip='" + gateway_ip + "' ORDER BY s.imei", cnMY);
try
{
dr = cmd.ExecuteReader();
while (dr.Read())
{
Tmp = dr.GetString(0);
ret.Add(Tmp);
}
dr.Close();
}
catch (MySqlException o)
{
Console.Write("File: SMdb_access.cs Meth: get_all_IMEI_by_GW_ip : ");
Console.WriteLine(o.Message.ToString());
}
}
catch (MySqlException o)
{
Console.WriteLine("File: SMdb_access.cs Meth: get_all_IMEI_by_GW_ip : " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
return ret;
}
}
public String get_IP_for_IMEI(string imei)
{
String Tmp = "";
if (MSSQL)
{
SqlCeCommand cmd;
SqlCeDataReader Reader;
try
{
cmd = new SqlCeCommand("SELECT gateway_ip from subs_gateway where sc_id=" + getSC_ID_from_IMEI(imei), cn);
Reader = cmd.ExecuteReader();
if (Reader.Read())
{
if (Reader.IsDBNull(0)) Tmp = "";
else Tmp = Reader.GetString(0);
}
Reader.Close();
}
catch (Exception o)
{
Console.WriteLine("File: SMdb_access.cs Meth: get_IP_for_IMEI : " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
}
else
{
MySqlCommand cmd;
MySqlDataReader Reader;
try
{
cmd = new MySqlCommand("SELECT gateway_ip from subs_gateway where sc_id=" + getSC_ID_from_IMEI(imei), cnMY);
Reader = cmd.ExecuteReader();
if (Reader.Read())
{
if (Reader.IsDBNull(0)) Tmp = "";
else Tmp = Reader.GetString(0);
}
Reader.Close();
}
catch (Exception o)
{
Console.WriteLine("File: SMdb_access.cs Meth: get_IP_for_IMEI : " + o.Message.ToString() + o.StackTrace.ToString());
throw new ArgumentException(o.Message.ToString());
}
}
return Tmp;
}
public void delete_From_GW(string imei)
{
if (MSSQL)
{
SqlCeCommand cmd;
try
{
cmd = new SqlCeCommand("DELETE FROM subs_gateway where sc_id = " + getSC_ID_from_IMEI(imei), cn);
cmd.ExecuteNonQuery();
}
catch (Exception o)
{
Console.WriteLine("File: SMdb_access.cs Meth: run_wait " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
}
else
{
MySqlCommand cmd;
try
{
cmd = new MySqlCommand("DELETE FROM subs_gateway where sc_id = " + getSC_ID_from_IMEI(imei), cnMY);
cmd.ExecuteNonQuery();
}
catch (MySqlException o)
{
Console.WriteLine("File: SMdb_access.cs Meth: run_wait " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
}
}
public void Insert_Speed_Alarm(Int32 p_sc_id, UInt32 p_timeGMT, Int32 p_speed)
{
if (MSSQL)
{
try
{
SqlCeCommand cmd;
string query = "INSERT INTO speedalarm (sc_id,timeGMT,speed,preview) VALUES(" + p_sc_id + "," + p_timeGMT + "," + p_speed + ",0)";
cmd = new SqlCeCommand(query, cn);
cmd.ExecuteNonQuery();
}
catch (Exception o)
{
Console.WriteLine("File: SMdb_access.cs Meth: Insert_Zone_Alarm " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
}
else
{
try
{
MySqlCommand cmd;
string query = "INSERT INTO speedalarm VALUES(" + p_sc_id + "," + p_timeGMT + "," + p_speed + ",0,null,'" + "no address" + "')";
cmd = new MySqlCommand(query, cnMY);
cmd.ExecuteNonQuery();
}
catch (MySqlException o)
{
Console.WriteLine("File: SMdb_access.cs Meth: Insert_Zone_Alarm " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
}
}
public void Insert_Emerg_Alarm(Int32 p_sc_id, UInt32 p_timeGMT)
{
if (MSSQL)
{
try
{
SqlCeCommand cmd;
string query = "INSERT INTO emergalarm (sc_id,timeGMT,preview) VALUES(" + p_sc_id + "," + p_timeGMT + ",0)";
cmd = new SqlCeCommand(query, cn);
cmd.ExecuteNonQuery();
}
catch (Exception o)
{
Console.WriteLine("File: SMdb_access.cs Meth: Insert_Zone_Alarm " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
}
else
{
try
{
MySqlCommand cmd;
string query = "INSERT INTO emergalarm VALUES(" + p_sc_id + "," + p_timeGMT + ",0,null,'" + "no address" + "')";
cmd = new MySqlCommand(query, cnMY);
cmd.ExecuteNonQuery();
}
catch (MySqlException o)
{
Console.WriteLine("File: SMdb_access.cs Meth: Insert_Zone_Alarm " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
}
}
public ArrayList get_all_zones()
{
if (MSSQL)
{
ArrayList ret = new ArrayList();
try
{
SqlCeCommand cmd;
SqlCeDataReader dr;
cmd = new SqlCeCommand("select name,lat,lng,lat2,lng2,idx from zone ORDER BY name", cn);
dr = cmd.ExecuteReader();
while (dr.Read())
{
Zones tmp = new Zones(dr.GetString(0), dr.GetDouble(1), dr.GetDouble(2), dr.GetDouble(3), dr.GetDouble(4), dr.GetInt32(5));
ret.Add(tmp);
}
dr.Close();
}
catch (Exception o)
{
Console.WriteLine("File: SMdb_access.cs Meth: get_zones_with_id " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
return ret;
}
else
{
ArrayList ret = new ArrayList();
try
{
MySqlCommand cmd;
MySqlDataReader dr;
cmd = new MySqlCommand("select name,lat,lng,lat2,lng2,idx from zone ORDER BY name", cnMY);
dr = cmd.ExecuteReader();
while (dr.Read())
{
Zones tmp = new Zones(dr.GetString(0), dr.GetDouble(1), dr.GetDouble(2), dr.GetDouble(3), dr.GetDouble(4), dr.GetInt32(5));
ret.Add(tmp);
}
dr.Close();
}
catch (MySqlException o)
{
Console.WriteLine("File: SMdb_access.cs Meth: get_vehicles " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
return ret;
}
}
public ArrayList get_all_landmarks()
{
if (MSSQL)
{
ArrayList ret = new ArrayList();
try
{
SqlCeCommand cmd;
SqlCeDataReader dr;
cmd = new SqlCeCommand("select name,lat,lng,idx from place ORDER BY name", cn);
dr = cmd.ExecuteReader();
while (dr.Read())
{
LandMark tmp = new LandMark(dr.GetString(0), dr.GetDouble(1), dr.GetDouble(2), dr.GetInt32(3));
ret.Add(tmp);
}
dr.Close();
}
catch (Exception o)
{
Console.WriteLine("File: SMdb_access.cs Meth: get_zones_with_id " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
return ret;
}
else
{
ArrayList ret = new ArrayList();
try
{
MySqlCommand cmd;
MySqlDataReader dr;
cmd = new MySqlCommand("select name,lat,lng,idx from place ORDER BY name", cnMY);
dr = cmd.ExecuteReader();
while (dr.Read())
{
LandMark tmp = new LandMark(dr.GetString(0), dr.GetDouble(1), dr.GetDouble(2), dr.GetInt32(3));
ret.Add(tmp);
}
dr.Close();
}
catch (MySqlException o)
{
Console.WriteLine("File: SMdb_access.cs Meth: get_vehicles " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
return ret;
}
}
public Boolean addRFIDToDb(String _RFID, String _namePassenger, Int32 sc_id)
{
if (MSSQL)
{
SqlCeCommand cmd;
try
{
cmd = new SqlCeCommand("INSERT INTO passenger (rfid,name,sc_id) VALUES('" + _RFID + "' ,'" + _namePassenger + "'," + sc_id + ")", cn);
cmd.ExecuteNonQuery();
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
return false;
}
return true;
}
else
{
MySqlCommand cmd;
try
{
cmd = new MySqlCommand("INSERT INTO passenger VALUES('" + _RFID + "' ,'" + _namePassenger + "',"+sc_id+",null)", cnMY);
cmd.ExecuteNonQuery();
}
catch (MySqlException ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
return false;
}
return true;
}
}
public Boolean DeleteRFID(Int32 _unique_id)
{
if (MSSQL)
{
SqlCeCommand cmd;
try
{
cmd = new SqlCeCommand("DELETE FROM passenger where id=" + _unique_id, cn);
cmd.ExecuteNonQuery();
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
return false;
}
return true;
}
else
{
MySqlCommand cmd;
try
{
cmd = new MySqlCommand("DELETE FROM passenger where id=" + _unique_id, cnMY);
cmd.ExecuteNonQuery();
}
catch (MySqlException ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
return false;
}
return true;
}
}
public Boolean UpdateRFID(Int32 _unique_id, String _RFID, String _namePassenger, Int32 sc_id)
{
if (MSSQL)
{
SqlCeCommand cmd;
try
{
cmd = new SqlCeCommand("UPDATE passenger SET rfid='" + _RFID + "' , name='" + _namePassenger + "', sc_id=" + sc_id + " where id="+_unique_id, cn);
cmd.ExecuteNonQuery();
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
return false;
}
return true;
}
else
{
MySqlCommand cmd;
try
{
cmd = new MySqlCommand("UPDATE passenger SET rfid='" + _RFID + "' , name='" + _namePassenger + "', sc_id=" + sc_id + " where id=" + _unique_id, cnMY);
cmd.ExecuteNonQuery();
}
catch (MySqlException ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
return false;
}
return true;
}
}
public void deleteVehicle(String _imei)
{
if (MSSQL)
{
SqlCeCommand cmd;
SqlCeDataReader Reader;
try
{
String scid = "0";
String vehid = "0";
String vehname = "0";
cmd = new SqlCeCommand("SELECT count(imei) FROM subscriber where imei='" + _imei + "'", cn);
object temp = cmd.ExecuteScalar();
if (temp.ToString() != "0")
{
cmd = new SqlCeCommand("DELETE FROM lastpos where imei='" + _imei + "'", cn);
cmd.ExecuteNonQuery();
cmd = new SqlCeCommand("SELECT sc_id FROM subscriber where imei='" + _imei + "'", cn);
Reader = cmd.ExecuteReader();
while (Reader.Read())
{
scid = Reader.GetInt32(0).ToString();
}
Reader.Close();
cmd = new SqlCeCommand("SELECT veh_id FROM subscriber_history where sc_id=" + scid, cn);
Reader = cmd.ExecuteReader();
while (Reader.Read())
{
vehid = Reader.GetInt32(0).ToString();
}
Reader.Close();
cmd = new SqlCeCommand("SELECT name FROM vehicle where id=" + vehid, cn);
Reader = cmd.ExecuteReader();
while (Reader.Read())
{
vehname = Reader.GetString(0);
}
Reader.Close();
cmd = new SqlCeCommand("DELETE FROM vehicle where id=" + vehid, cn);
cmd.ExecuteNonQuery();
cmd = new SqlCeCommand("DELETE FROM vehicle_user where veh_id=" + vehid, cn);
cmd.ExecuteNonQuery();
cmd = new SqlCeCommand("DELETE FROM subscriber_history where veh_id=" + vehid, cn);
cmd.ExecuteNonQuery();
cmd = new SqlCeCommand("DELETE FROM subscriber where sc_id=" + scid, cn);
cmd.ExecuteNonQuery();
cmd = new SqlCeCommand("DELETE FROM subs_gateway where sc_id=" + scid, cn);
cmd.ExecuteNonQuery();
cmd = new SqlCeCommand("DELETE FROM emergdi where sc_id=" + scid, cn);
cmd.ExecuteNonQuery();
cmd = new SqlCeCommand("DELETE FROM messages where sc_id=" + scid, cn);
cmd.ExecuteNonQuery();
try
{
cmd = new SqlCeCommand("DELETE FROM subs_gateway where sc_id=" + scid, cn);
cmd.ExecuteNonQuery();
}
catch
{
}
MessageBox.Show("Delete IMEI finish.");
}
else
{
MessageBox.Show("Vehicle Not in db", "error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
MessageBox.Show("SqlCe connection Failed !!!!", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MySqlCommand cmd;
MySqlDataReader Reader;
try
{
String scid = "0";
String vehid = "0";
String vehname = "0";
cmd = new MySqlCommand("SELECT count(imei) FROM subscriber where imei='" + _imei + "'", cnMY);
object temp = cmd.ExecuteScalar();
if (temp.ToString() != "0")
{
cmd = new MySqlCommand("DELETE FROM lastpos where imei='" + _imei + "'", cnMY);
cmd.ExecuteNonQuery();
cmd = new MySqlCommand("SELECT sc_id FROM subscriber where imei='" + _imei + "'", cnMY);
Reader = cmd.ExecuteReader();
while (Reader.Read())
{
scid = Reader.GetInt32(0).ToString();
}
Reader.Close();
cmd = new MySqlCommand("SELECT veh_id FROM subscriber_history where sc_id=" + scid, cnMY);
Reader = cmd.ExecuteReader();
while (Reader.Read())
{
vehid = Reader.GetInt32(0).ToString();
}
Reader.Close();
cmd = new MySqlCommand("SELECT name FROM vehicle where id=" + vehid, cnMY);
Reader = cmd.ExecuteReader();
while (Reader.Read())
{
vehname = Reader.GetString(0);
}
Reader.Close();
cmd = new MySqlCommand("DELETE FROM vehicle where id=" + vehid, cnMY);
cmd.ExecuteNonQuery();
cmd = new MySqlCommand("DELETE FROM vehicle_user where veh_id=" + vehid, cnMY);
cmd.ExecuteNonQuery();
cmd = new MySqlCommand("DELETE FROM subscriber_history where veh_id=" + vehid, cnMY);
cmd.ExecuteNonQuery();
cmd = new MySqlCommand("DELETE FROM subscriber where sc_id=" + scid, cnMY);
cmd.ExecuteNonQuery();
cmd = new MySqlCommand("DELETE FROM subs_gateway where sc_id=" + scid, cnMY);
cmd.ExecuteNonQuery();
cmd = new MySqlCommand("DELETE FROM emergdi where sc_id=" + scid, cnMY);
cmd.ExecuteNonQuery();
cmd = new MySqlCommand("DELETE FROM messages where sc_id=" + scid, cnMY);
cmd.ExecuteNonQuery();
try
{
cmd = new MySqlCommand("DELETE FROM subs_gateway where sc_id=" + scid, cnMY);
cmd.ExecuteNonQuery();
}
catch
{
}
MessageBox.Show("Delete IMEI finish.");
}
else
{
MessageBox.Show("Vehicle Not in db", "error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (MySqlException ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
MessageBox.Show("MySql connection Failed !!!!", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
public void editVehicle(String _id, String _name, Int32 _DI, String _lp)
{
if (MSSQL)
{
SqlCeCommand cmd;
try
{
cmd = new SqlCeCommand("select count(id) from vehicle where id=\'" + _id + "\'", cn);
object temp = cmd.ExecuteScalar();
cmd = new SqlCeCommand("select sc_id from subscriber_history where veh_id=\'" + _id + "\'", cn);
object tmp_sc_id = cmd.ExecuteScalar();
if (Convert.ToInt32(temp) > 0)
{
cmd = new SqlCeCommand("UPDATE vehicle SET name='" + _name + "',lp='" + _lp + "' where id=" + _id, cn);
cmd.ExecuteNonQuery();
cmd = new SqlCeCommand("update emergdi SET DI=" + _DI.ToString() + " where sc_id=" + Convert.ToInt32(tmp_sc_id), cn);
if (cmd.ExecuteNonQuery() == 0)
{
cmd = new SqlCeCommand("insert into emergdi (sc_id,DI) VALUES(" + Convert.ToInt32(tmp_sc_id) + "," + _DI.ToString() + ")", cn);
cmd.ExecuteNonQuery();
}
MessageBox.Show("Vehicle edited!", "successfuly!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
else
{
MessageBox.Show("Vehicle Not found in db", "error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
MessageBox.Show("SqlCe connection Failed !!!!", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MySqlCommand cmd;
try
{
cmd = new MySqlCommand("select count(id) from vehicle where id=\'" + _id + "\'", cnMY);
object temp = cmd.ExecuteScalar();
cmd = new MySqlCommand("select sc_id from subscriber_history where veh_id=\'" + _id + "\'", cnMY);
object tmp_sc_id = cmd.ExecuteScalar();
if (Convert.ToInt32(temp) > 0)
{
cmd = new MySqlCommand("UPDATE vehicle SET name='" + _name + "' where id=" + _id, cnMY);
cmd.ExecuteNonQuery();
cmd = new MySqlCommand("update emergdi SET DI=" + _DI.ToString() + " where sc_id=" + Convert.ToInt32(tmp_sc_id), cnMY);
//cmd.ExecuteNonQuery();
if (cmd.ExecuteNonQuery() == 0)
{
cmd = new MySqlCommand("insert into emergdi (sc_id,DI) VALUES(" + Convert.ToInt32(tmp_sc_id) + "," + _DI.ToString() + ")", cnMY);
cmd.ExecuteNonQuery();
}
MessageBox.Show("Vehicle edited!", "successfuly!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
else
{
MessageBox.Show("Vehicle Not found in db", "error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (MySqlException ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
MessageBox.Show("MySql connection Failed !!!!", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
public Hashtable get_Vechicle_and_zone()
{
Hashtable ret = new Hashtable();
if (MSSQL)
{
SqlCeCommand cmd;
SqlCeDataReader dr;
try
{
cmd = new SqlCeCommand("SELECT l.imei, l.lat, l.lng, s.sc_id,v.name from lastpos l, subscriber s, subscriber_history h,vehicle v where l.imei = s.imei and h.sc_id=s.sc_id and h.veh_id=v.id", cn);
try
{
dr = cmd.ExecuteReader();
while (dr.Read())
{
Vehicle_with_zone obj = new Vehicle_with_zone(dr.GetInt32(3), dr.GetString(0), dr.GetDouble(1), dr.GetDouble(2), dr.GetString(4));
ret.Add(dr.GetString(0),obj);
}
//Clean up.
dr.Close();
}
catch (Exception o)
{
Console.WriteLine("File: SMdb_access.cs Meth: get_Vechicle_and_zone dr : ");
Console.WriteLine(o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
}
catch (Exception o)
{
Console.WriteLine("File: SMdb_access.cs Meth: get_Vechicle_and_zone SqlCeCommand: ");
Console.WriteLine(o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
}
else
{
MySqlCommand cmd;
MySqlDataReader dr;
try
{
//cmd = new MySqlCommand("SELECT l.imei, l.lat, l.lng, s.sc_id from lastpos l, subscriber s where l.imei = s.imei", cnMY);
cmd = new MySqlCommand("SELECT l.imei, l.lat, l.lng, s.sc_id,v.name from lastpos l, subscriber s, subscriber_history h,vehicle v where l.imei = s.imei and h.sc_id=s.sc_id and h.veh_id=v.id", cnMY);
try
{
dr = cmd.ExecuteReader();
while (dr.Read())
{
Vehicle_with_zone obj = new Vehicle_with_zone(dr.GetInt32(3), dr.GetString(0), dr.GetDouble(1), dr.GetDouble(2), dr.GetString(4));
ret.Add(dr.GetString(0), obj);
}
//Clean up.
dr.Close();
}
catch (MySqlException o)
{
Console.WriteLine("File: SMdb_access.cs Meth: get_Vechicle_and_zone dr : ");
Console.WriteLine(o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
}
catch (MySqlException o)
{
Console.WriteLine("File: SMdb_access.cs Meth: get_Vechicle_and_zone MySqlCommand: ");
Console.WriteLine(o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
}
return ret;
}
public Hashtable get_sc_id_and_DI()
{
Hashtable ret = new Hashtable();
if (MSSQL)
{
SqlCeCommand cmd;
SqlCeDataReader dr;
try
{
cmd = new SqlCeCommand("SELECT sc_id,DI from emergdi", cn);
dr = cmd.ExecuteReader();
while (dr.Read())
{
ret.Add(dr.GetInt32(0), dr.GetInt32(1));
}
//Clean up.
dr.Close();
}
catch (Exception o)
{
Console.WriteLine("File: SMdb_access.cs Meth: get_sc_id_and_DI SqlCeCommand: ");
Console.WriteLine(o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
}
else
{
MySqlCommand cmd;
MySqlDataReader dr;
try
{
cmd = new MySqlCommand("SELECT sc_id,DI from emergdi", cnMY);
dr = cmd.ExecuteReader();
while (dr.Read())
{
ret.Add(dr.GetInt32(0), dr.GetInt32(1));
}
dr.Close();
}
catch (MySqlException o)
{
Console.WriteLine("File: SMdb_access.cs Meth: get_Vechicle_and_zone MySqlCommand: ");
Console.WriteLine(o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
}
return ret;
}
public ArrayList getAllVehicles()
{
Vehicles veh;
if (MSSQL)
{
ArrayList vehList = new ArrayList();
SqlCeCommand cmd;
SqlCeDataReader Reader;
/* cmd = new SqlCeCommand("SELECT veh.id,sub.imei,veh.lp,veh.name,em.DI"
+ " FROM vehicle as veh"
+ " INNER JOIN subscriber_history as sh ON (veh.id = sh.veh_id) INNER JOIN subscriber as sub"
+ " on (sh.sc_id = sub.sc_id) LEFT JOIN emergdi as em on (sub.sc_id = em.sc_id)"
+ " Group by sub.imei,veh.id,veh.lp,veh.name,em.DI"
+ " ORDER BY sub.imei", cn);*/
cmd = new SqlCeCommand("SELECT veh.id,sub.imei,veh.lp,veh.name,em.DI"
+ " FROM vehicle as veh"
+ " INNER JOIN subscriber_history as sh ON (veh.id = sh.veh_id) INNER JOIN subscriber as sub"
+ " on (sh.sc_id = sub.sc_id) LEFT JOIN emergdi as em on (sub.sc_id = em.sc_id)"
+ " Group by sub.imei,veh.id,veh.lp,veh.name,em.DI"
+ " ORDER BY sub.imei", cn);
try
{
Reader = cmd.ExecuteReader();
while (Reader.Read())
{
if (Reader.IsDBNull(4)) veh = new Vehicles(Convert.ToInt32(Reader.GetValue(0)), Reader.GetValue(1).ToString(), Reader.GetValue(2).ToString(), Reader.GetValue(3).ToString(), "", 0);
else veh = new Vehicles(Convert.ToInt32(Reader.GetValue(0)), Reader.GetValue(1).ToString(), Reader.GetValue(2).ToString(), Reader.GetValue(3).ToString(), "", Reader.GetInt32(4));
vehList.Add(veh);
}
}
catch (Exception ee)
{
Console.WriteLine("1" + ee.Message + "\n" + ee.StackTrace);
MessageBox.Show("SqlCe connection Failed !!!!", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return vehList;
}
else
{
ArrayList vehList = new ArrayList();
MySqlCommand cmd;
MySqlDataReader Reader;
cmd = new MySqlCommand("SELECT veh.id,sub.imei,veh.lp,veh.name,e.DI"
+ " FROM vehicle as veh"
+ " INNER JOIN (subscriber_history as sh,subscriber as sub)"
+ " on ( veh.id = sh.veh_id and sh.sc_id = sub.sc_id)"
+ " LEFT JOIN (emergdi as e)"
+ " on (sub.sc_id = e.sc_id)"
+ " Group by sub.imei"
+ " ORDER BY sub.imei", cnMY);
try
{
Reader = cmd.ExecuteReader();
while (Reader.Read())
{
if (Reader.IsDBNull(4)) veh = new Vehicles(Convert.ToInt32(Reader.GetValue(0)), Reader.GetValue(1).ToString(), Reader.GetValue(2).ToString(), Reader.GetValue(3).ToString(), "", 0);
else veh = new Vehicles(Convert.ToInt32(Reader.GetValue(0)), Reader.GetValue(1).ToString(), Reader.GetValue(2).ToString(), Reader.GetValue(3).ToString(), "", Reader.GetInt32(4));
vehList.Add(veh);
}
}
catch (Exception ee)
{
Console.WriteLine("1"+ee.Message + "\n" + ee.StackTrace);
MessageBox.Show("MySql connection Failed !!!!", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return vehList;
}
}
public ArrayList getAllVehicle_sc_id()
{
ArrayList vehList = new ArrayList();
if (MSSQL)
{
SqlCeCommand cmd;
SqlCeDataReader Reader;
cmd = new SqlCeCommand("SELECT veh.name,sh.sc_id"
+ " FROM vehicle as veh"
+ " INNER JOIN subscriber_history as sh ON (veh.id = sh.veh_id)"
+ " ORDER BY veh.name", cn);
try
{
Reader = cmd.ExecuteReader();
while (Reader.Read())
{
Vehicle_sc_id veh = new Vehicle_sc_id(Reader.GetInt32(1),Reader.GetString(0));
vehList.Add(veh);
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
MessageBox.Show("SqlCe connection Failed !!!!", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return vehList;
}
else
{
MySqlCommand cmd;
MySqlDataReader Reader;
cmd = new MySqlCommand("SELECT veh.name,sh.sc_id"
+ " FROM vehicle as veh"
+ " INNER JOIN subscriber_history as sh ON (veh.id = sh.veh_id)"
+ " ORDER BY veh.name", cnMY);
try
{
Reader = cmd.ExecuteReader();
while (Reader.Read())
{
Vehicle_sc_id veh = new Vehicle_sc_id(Reader.GetInt32(1), Reader.GetString(0));
vehList.Add(veh);
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
MessageBox.Show("MySql connection Failed !!!!", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return vehList;
}
}
public ArrayList getAllRFID()
{
ArrayList RfIDList = new ArrayList();
if (MSSQL)
{
SqlCeCommand cmd;
SqlCeDataReader Reader;
cmd = new SqlCeCommand("SELECT pa.id,pa.rfid,pa.name,veh.name,pa.sc_id"
+ " FROM passenger as pa"
+ " INNER JOIN subscriber_history as sh ON (pa.sc_id = sh.sc_id) INNER JOIN vehicle as veh"
+ " on (sh.veh_id = veh.id)"
+ " ORDER BY pa.id", cn);
try
{
Reader = cmd.ExecuteReader();
while (Reader.Read())
{
RFIDs rfid = new RFIDs(Reader.GetString(1), Reader.GetString(2), Reader.GetString(3), Reader.GetInt32(4),Reader.GetInt32(0));
RfIDList.Add(rfid);
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
MessageBox.Show("SqlCe connection Failed !!!!", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return RfIDList;
}
else
{
MySqlCommand cmd;
MySqlDataReader Reader;
cmd = new MySqlCommand("SELECT pa.id,pa.rfid,pa.name,veh.name,pa.sc_id"
+ " FROM passenger as pa"
+ " INNER JOIN (subscriber_history as sh,vehicle as veh)"
+ " on ( pa.sc_id = sh.sc_id and sh.veh_id = veh.id)"
+ " ORDER BY pa.id", cnMY);
try
{
Reader = cmd.ExecuteReader();
while (Reader.Read())
{
RFIDs rfid = new RFIDs(Reader.GetString(1), Reader.GetString(2), Reader.GetString(3), Reader.GetInt32(4), Reader.GetInt32(0));
RfIDList.Add(rfid);
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
MessageBox.Show("MySql connection Failed !!!!", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return RfIDList;
}
}
public Hashtable getAll_imei_sc_id()
{
Hashtable List = new Hashtable();
if (MSSQL)
{
SqlCeCommand cmd;
SqlCeDataReader Reader;
cmd = new SqlCeCommand("SELECT sc_id,imei from subscriber", cn);
try
{
Reader = cmd.ExecuteReader();
while (Reader.Read())
{
try
{
List.Add(Reader.GetString(1),Reader.GetInt32(0));
}
catch { }
}
Reader.Close();
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
}
}
else
{
MySqlCommand cmd;
MySqlDataReader Reader;
cmd = new MySqlCommand("SELECT sc_id,imei from subscriber", cnMY);
try
{
Reader = cmd.ExecuteReader();
while (Reader.Read())
{
try
{
List.Add(Reader.GetString(1), Reader.GetInt32(0));
}
catch { }
}
Reader.Close();
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
}
}
return List;
}
public ArrayList getAllUsersForCurrentVehicle(String _id)
{
if (MSSQL)
{
ArrayList userList = new ArrayList();
SqlCeCommand cmd;
SqlCeDataReader Reader;
cmd = new SqlCeCommand("SELECT firstName, lastName, login, password,userId FROM users as u"
+ " inner join vehicle_user as vh"
+ " on (vh.user_id=u.userID)"
+ " where vh.veh_id =" + _id
+ " GROUP BY u.userId,u.firstName, u.lastName, u.login, u.password"
, cn);
try
{
Reader = cmd.ExecuteReader();
while (Reader.Read())
{
User usr = new User(Reader.GetValue(0).ToString(), Reader.GetValue(1).ToString(), Reader.GetValue(2).ToString(), Reader.GetValue(3).ToString(), Reader.GetInt32(4));
userList.Add(usr);
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
MessageBox.Show("SqlCe connection Failed !!!!", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return userList;
}
else
{
ArrayList userList = new ArrayList();
MySqlCommand cmd;
MySqlDataReader Reader;
cmd = new MySqlCommand("SELECT firstName, lastName, login, password,userId FROM users as u"
+ " inner join( vehicle_user as vh)"
+ " on(vh.user_id=u.userID)"
+ " where vh.veh_id =" + _id.ToString()
+ " GROUP BY u.userId"
, cnMY);
try
{
Reader = cmd.ExecuteReader();
while (Reader.Read())
{
User usr = new User(Reader.GetValue(0).ToString(), Reader.GetValue(1).ToString(), Reader.GetValue(2).ToString(), Reader.GetValue(3).ToString(), Reader.GetInt32(4));
userList.Add(usr);
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
MessageBox.Show("MySql connection Failed !!!!", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return userList;
}
}
//get all users that ar not assigned to current vehicle
public ArrayList getAllOtherUsersForCurrentVehicle(String _id)
{
if (MSSQL)
{
ArrayList userList = new ArrayList();
SqlCeCommand cmd;
SqlCeDataReader Reader;
cmd = new SqlCeCommand("SELECT firstName, lastName, login, password,userId FROM users as u"
+ " WHERE u.userID NOT IN "
+ " (SELECT user_id FROM vehicle_user "
+ " WHERE veh_id =" + _id
+ ")"
, cn);
try
{
Reader = cmd.ExecuteReader();
while (Reader.Read())
{
User usr = new User(Reader.GetValue(0).ToString(), Reader.GetValue(1).ToString(), Reader.GetValue(2).ToString(), Reader.GetValue(3).ToString(), Convert.ToInt32(Reader.GetValue(4)));
userList.Add(usr);
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
MessageBox.Show("SqlCe connection Failed !!!!", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return userList;
}
else
{
ArrayList userList = new ArrayList();
MySqlCommand cmd;
MySqlDataReader Reader;
cmd = new MySqlCommand("SELECT firstName, lastName, login, password,userId FROM users as u"
+ " WHERE u.userID NOT IN "
+ " (SELECT user_id FROM vehicle_user "
+ " WHERE veh_id =" + _id
+ ")"
, cnMY);
try
{
Reader = cmd.ExecuteReader();
while (Reader.Read())
{
User usr = new User(Reader.GetValue(0).ToString(), Reader.GetValue(1).ToString(), Reader.GetValue(2).ToString(), Reader.GetValue(3).ToString(), Convert.ToInt32(Reader.GetValue(4)));
userList.Add(usr);
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
MessageBox.Show("MySql connection Failed !!!!", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return userList;
}
}
public void addUserToDb(String _firstName, String _lastname, String _userName, String _password)
{
if (MSSQL)
{
SqlCeCommand cmd;
try
{
cmd = new SqlCeCommand("select count(login) from users where login=\'" + _userName + "\'", cn);
object temp = cmd.ExecuteScalar();
if (temp.ToString() == "0")
{
cmd = new SqlCeCommand("INSERT INTO users (firstName,lastName,login,password) VALUES('" + _firstName + "','" + _lastname + "','" + _userName + "','" + _password + "')", cn);
cmd.ExecuteNonQuery();
MessageBox.Show("user added !", "successfuly!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
else
{
MessageBox.Show("User allready in db or some fields are empty", "error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
MessageBox.Show("SqlCe connection Failed !!!!", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MySqlCommand cmd;
try
{
cmd = new MySqlCommand("select count(login) from users where login=\'" + _userName + "\'", cnMY);
object temp = cmd.ExecuteScalar();
if (temp.ToString() == "0")
{
cmd = new MySqlCommand("INSERT INTO users VALUES(null,'" + _firstName + "','" + _lastname + "','" + _userName + "','" + _password + "')", cnMY);
cmd.ExecuteNonQuery();
MessageBox.Show("user added !", "successfuly!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
else
{
MessageBox.Show("User allready in db or some fields are empty", "error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (MySqlException ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
MessageBox.Show("MySql connection Failed !!!!", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
public void updateUserToDb(String _firstName, String _lastname, String _userName, String _password)
{
if (MSSQL)
{
SqlCeCommand cmd;
try
{
cmd = new SqlCeCommand("select count(login) from users where login=\'" + _userName + "\'", cn);
object temp = cmd.ExecuteScalar();
if (Convert.ToInt32(temp) > 0)
{
cmd = new SqlCeCommand("UPDATE users SET firstName='" + _firstName + "',lastName='" + _lastname + "',login='" + _userName + "',password='" + _password + "' WHERE login='" + _userName + "'", cn);
cmd.ExecuteNonQuery();
MessageBox.Show("user edited!", "successfuly!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
else
{
MessageBox.Show("User Not found in db", "error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
MessageBox.Show("SqlCe connection Failed !!!!", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MySqlCommand cmd;
try
{
cmd = new MySqlCommand("select count(login) from users where login=\'" + _userName + "\'", cnMY);
object temp = cmd.ExecuteScalar();
if (Convert.ToInt32(temp) > 0)
{
cmd = new MySqlCommand("UPDATE users SET firstName='" + _firstName + "',lastName='" + _lastname + "',login='" + _userName + "',password='" + _password + "' WHERE login='" + _userName + "'", cnMY);
cmd.ExecuteNonQuery();
MessageBox.Show("user edited!", "successfuly!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
else
{
MessageBox.Show("User Not found in db", "error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (MySqlException ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
MessageBox.Show("MySql connection Failed !!!!", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
public void deleteUser(string userName)
{
if (MSSQL)
{
SqlCeCommand cmd;
try
{
cmd = new SqlCeCommand("select count(login) from users where login=\'" + userName + "\'", cn);
object temp = cmd.ExecuteScalar();
if (temp.ToString() != "0")
{
cmd = new SqlCeCommand("DELETE FROM users where login='" + userName + "'", cn);
cmd.ExecuteNonQuery();
MessageBox.Show("user Deleted !", "successfuly!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
else
{
MessageBox.Show("User Not found in DB", "error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
MessageBox.Show("SqlCe connection Failed !!!!", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MySqlCommand cmd;
try
{
cmd = new MySqlCommand("select count(login) from users where login=\'" + userName + "\'", cnMY);
object temp = cmd.ExecuteScalar();
if (temp.ToString() != "0")
{
cmd = new MySqlCommand("DELETE FROM users where login='" + userName + "'", cnMY);
cmd.ExecuteNonQuery();
MessageBox.Show("user Deleted !", "successfuly!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
else
{
MessageBox.Show("User Not found in DB", "error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (MySqlException ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
MessageBox.Show("MySql connection Failed !!!!", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
//get all users
public ArrayList getAllUsers()
{
if (MSSQL)
{
ArrayList userList = new ArrayList();
SqlCeCommand cmd;
SqlCeDataReader Reader;
cmd = new SqlCeCommand("SELECT firstName, lastName, login, password,userId FROM users", cn);
try
{
Reader = cmd.ExecuteReader();
while (Reader.Read())
{
User usr = new User(Reader.GetValue(0).ToString(), Reader.GetValue(1).ToString(), Reader.GetValue(2).ToString(), Reader.GetValue(3).ToString(), Convert.ToInt32(Reader.GetValue(4)));
userList.Add(usr);
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
MessageBox.Show("SqlCe connection Failed !!!!", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return userList;
}
else
{
ArrayList userList = new ArrayList();
MySqlCommand cmd;
MySqlDataReader Reader;
cmd = new MySqlCommand("SELECT firstName, lastName, login, password,userId FROM users", cnMY);
try
{
Reader = cmd.ExecuteReader();
while (Reader.Read())
{
User usr = new User(Reader.GetValue(0).ToString(), Reader.GetValue(1).ToString(), Reader.GetValue(2).ToString(), Reader.GetValue(3).ToString(), Convert.ToInt32(Reader.GetValue(4)));
userList.Add(usr);
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
MessageBox.Show("MySql connection Failed !!!!", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return userList;
}
}
//get all vehicles for current user
public ArrayList getAllVehiclesForCurrentUser(String _id)
{
Vehicles veh;
if (MSSQL)
{
ArrayList vehList = new ArrayList();
SqlCeCommand cmd;
SqlCeDataReader Reader;
cmd = new SqlCeCommand("SELECT veh.id,sub.imei,veh.lp,veh.name,em.DI FROM vehicle as veh INNER JOIN subscriber_history as sh ON (veh.id = sh.veh_id) INNER JOIN subscriber as sub on (sh.sc_id = sub.sc_id) LEFT JOIN emergdi as em on (sub.sc_id = em.sc_id) inner join vehicle_user as vh on(vh.veh_id=veh.id) Where vh.user_id=" + _id + " Group by sub.imei,veh.id,veh.lp,veh.name,em.DI ORDER BY sub.imei", cn);
try
{
Reader = cmd.ExecuteReader();
while (Reader.Read())
{
if (Reader.IsDBNull(4)) veh = new Vehicles(Convert.ToInt32(Reader.GetValue(0)), Reader.GetValue(1).ToString(), Reader.GetValue(2).ToString(), Reader.GetValue(3).ToString(), "", 0);
else veh = new Vehicles(Convert.ToInt32(Reader.GetValue(0)), Reader.GetValue(1).ToString(), Reader.GetValue(2).ToString(), Reader.GetValue(3).ToString(), "", Reader.GetInt32(4));
vehList.Add(veh);
}
}
catch (Exception ee)
{
Console.WriteLine("2" + ee.Message + "\n" + ee.StackTrace);
MessageBox.Show("SqlCe connection Failed !!!!", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return vehList;
}
else
{
ArrayList vehList = new ArrayList();
MySqlCommand cmd;
MySqlDataReader Reader;
cmd = new MySqlCommand("SELECT veh.id,sub.imei,veh.lp,veh.name,em.DI"
+ " FROM vehicle as veh"
+ " INNER JOIN (subscriber_history as sh,subscriber as sub)"
+ " on ( veh.id = sh.veh_id and sh.sc_id = sub.sc_id)"
+ " left join(emergdi as em)"
+ " on(sub.sc_id = em.sc_id)"
+ " inner join( vehicle_user as vh)"
+ " on(vh.veh_id=veh.id)"
+ " Where vh.user_id=" + _id.ToString()
+ " Group by sub.imei"
+ " ORDER BY sub.imei", cnMY);
try
{
Reader = cmd.ExecuteReader();
while (Reader.Read())
{
if (Reader.IsDBNull(4)) veh = new Vehicles(Convert.ToInt32(Reader.GetValue(0)), Reader.GetValue(1).ToString(), Reader.GetValue(2).ToString(), Reader.GetValue(3).ToString(), "", 0);
else veh = new Vehicles(Convert.ToInt32(Reader.GetValue(0)), Reader.GetValue(1).ToString(), Reader.GetValue(2).ToString(), Reader.GetValue(3).ToString(), "", Reader.GetInt32(4));
vehList.Add(veh);
}
}
catch (Exception ee)
{
Console.WriteLine("2"+ee.Message + "\n" + ee.StackTrace);
MessageBox.Show("MySql connection Failed !!!!", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return vehList;
}
}
public DataTable get_zone_table(string command)
{
if (MSSQL)
{
SqlCeDataAdapter dataAdapter = new SqlCeDataAdapter(command, cn);
DataTable table = new DataTable();
table.Locale = System.Globalization.CultureInfo.InvariantCulture;
dataAdapter.Fill(table);
return table;
}
else
{
MySqlDataAdapter dataAdapter = new MySqlDataAdapter(command, cnMY);
DataTable table = new DataTable();
table.Locale = System.Globalization.CultureInfo.InvariantCulture;
dataAdapter.Fill(table);
return table;
}
}
//get all vehicles that ar not assigned to current user
public ArrayList getAllOtherVehiclesForCurrentUser(String _id)
{
Vehicles veh;
if (MSSQL)
{
ArrayList vehList = new ArrayList();
SqlCeCommand cmd;
SqlCeDataReader Reader;
cmd = new SqlCeCommand("SELECT veh.id,sub.imei,veh.lp,veh.name,em.DI FROM vehicle as veh INNER JOIN subscriber_history as sh ON (veh.id = sh.veh_id) INNER JOIN subscriber as sub on (sh.sc_id = sub.sc_id) LEFT JOIN emergdi as em on (sub.sc_id = em.sc_id) left join vehicle_user as vh on(vh.veh_id=veh.id) Where veh.id NOT IN (SELECT veh_id FROM vehicle_user WHERE user_id =" + _id + ") Group by sub.imei,veh.id,veh.lp,veh.name,em.DI ORDER BY sub.imei", cn);
try
{
Reader = cmd.ExecuteReader();
while (Reader.Read())
{
if (Reader.IsDBNull(4)) veh = new Vehicles(Convert.ToInt32(Reader.GetValue(0)), Reader.GetValue(1).ToString(), Reader.GetValue(2).ToString(), Reader.GetValue(3).ToString(), "", 0);
else veh = new Vehicles(Convert.ToInt32(Reader.GetValue(0)), Reader.GetValue(1).ToString(), Reader.GetValue(2).ToString(), Reader.GetValue(3).ToString(), "", Reader.GetInt32(4));
vehList.Add(veh);
}
}
catch (Exception ee)
{
Console.WriteLine("3" + ee.Message + "\n" + ee.StackTrace);
MessageBox.Show("SqlCe connection Failed !!!!", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return vehList;
}
else
{
ArrayList vehList = new ArrayList();
MySqlCommand cmd;
MySqlDataReader Reader;
cmd = new MySqlCommand("SELECT veh.id,sub.imei,veh.lp,veh.name,em.DI"
+ " FROM vehicle as veh"
+ " INNER JOIN (subscriber_history as sh,subscriber as sub)"
+ " on ( veh.id = sh.veh_id and sh.sc_id = sub.sc_id)"
+ " LEFT join(emergdi as em,vehicle_user as vh)"
+ " on(sub.sc_id = em.sc_id and vh.veh_id=veh.id)"
+ " WHERE veh.id NOT IN "
+ " (SELECT veh_id FROM vehicle_user "
+ " WHERE user_id =" + _id.ToString()
+ ")"
+ " Group by sub.imei"
+ " ORDER BY sub.imei", cnMY);
try
{
Reader = cmd.ExecuteReader();
while (Reader.Read())
{
if (Reader.IsDBNull(4)) veh = new Vehicles(Convert.ToInt32(Reader.GetValue(0)), Reader.GetValue(1).ToString(), Reader.GetValue(2).ToString(), Reader.GetValue(3).ToString(), "", 0);
else veh = new Vehicles(Convert.ToInt32(Reader.GetValue(0)), Reader.GetValue(1).ToString(), Reader.GetValue(2).ToString(), Reader.GetValue(3).ToString(), "", Reader.GetInt32(4));
vehList.Add(veh);
}
}
catch (Exception ee)
{
Console.WriteLine("3"+ee.Message + "\n" + ee.StackTrace);
MessageBox.Show("MySql connection Failed !!!!", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return vehList;
}
}
public ArrayList getMessage4vAL()
{
ArrayList List = new ArrayList();
Messages4vAL tmp;
if (MSSQL)
{
SqlCeCommand cmd;
SqlCeDataReader Reader;
cmd = new SqlCeCommand("SELECT s.type,s.imei,s.cmd,s.idx,veh.lp,s.status FROM sendtoval as s INNER JOIN subscriber as sub on (s.imei = sub.imei) INNER JOIN subscriber_history as sh ON (sub.sc_id = sh.sc_id) INNER JOIN vehicle as veh ON (veh.id = sh.veh_id) WHERE s.status !=2 Group by s.type,s.imei,s.cmd,s.idx,veh.lp,s.status", cn);
try
{
Reader = cmd.ExecuteReader();
while (Reader.Read())
{
tmp = new Messages4vAL(Reader.GetInt32(0), Reader.GetString(1), Reader.GetString(2), Reader.GetInt32(3), Reader.GetString(4), Reader.GetInt32(5));
List.Add(tmp);
}
Reader.Close();
}
catch (Exception ee)
{
Console.WriteLine("3" + ee.Message + "\n" + ee.StackTrace);
MessageBox.Show("SqlCe connection Failed !!!!", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MySqlCommand cmd;
MySqlDataReader Reader;
cmd = new MySqlCommand("SELECT s.type,s.imei,s.cmd,s.idx,veh.lp,s.status"
+ " FROM sendtoval as s"
+ " INNER JOIN (subscriber as sub,subscriber_history as sh,vehicle as veh)"
+ " on (s.imei = sub.imei and sub.sc_id = sh.sc_id and sh.veh_id = veh.id)"
+ " where s.status != 2"
+ " Group by sub.imei", cnMY);
try
{
Reader = cmd.ExecuteReader();
while (Reader.Read())
{
tmp = new Messages4vAL(Reader.GetInt32(0), Reader.GetString(1), Reader.GetString(2), Reader.GetInt32(3), Reader.GetString(4), Reader.GetInt32(5));
List.Add(tmp);
}
Reader.Close();
}
catch (Exception ee)
{
Console.WriteLine("3" + ee.Message + "\n" + ee.StackTrace);
MessageBox.Show("MySql connection Failed !!!!", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
return List;
}
public void Change_Message_Status(Int32 Idx, Int32 val)
{
if (MSSQL)
{
SqlCeCommand cmd;
try
{
cmd = new SqlCeCommand("UPDATE sendtoval SET status="+ val +" where idx="+Idx, cn);
//cmd = new SqlCeCommand("UPDATE updatelist SET version='1.0.0.2'", cn);
cmd.ExecuteNonQuery();
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
MessageBox.Show("SqlCe connection Failed !!!!", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MySqlCommand cmd;
try
{
cmd = new MySqlCommand("UPDATE sendtoval SET status=1 where idx=" + Idx, cnMY);
cmd.ExecuteNonQuery();
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
MessageBox.Show("MySql connection Failed !!!!", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
public String get_last_DBUpdate()
{
String ret = "0";
if (MSSQL)
{
try
{
SqlCeCommand cmd;
cmd = new SqlCeCommand("select version from updatelist order by idx desc", cn);
object temp = cmd.ExecuteScalar();
ret = Convert.ToString(temp);
}
catch (Exception o)
{
Console.WriteLine("File: SMdb_access.cs Meth: get_active_state : " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
}
else
{
try
{
MySqlCommand cmd;
cmd = new MySqlCommand("select version from updatelist order by idx desc", cnMY);
object temp = cmd.ExecuteScalar();
ret = Convert.ToString(temp);
}
catch (MySqlException o)
{
Console.WriteLine("File: SMdb_access.cs Meth: get_active_state : " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
}
return ret;
}
public String Get_DB_version()
{
String name = "";
if (MSSQL)
{
SqlCeCommand cmd;
try
{
String query = "select max(idx) from updatelist";
cmd = new SqlCeCommand(query, cn);
Int32 nr = (Int32)cmd.ExecuteScalar();
query = "SELECT version from updatelist where idx=" + nr;
cmd = new SqlCeCommand(query, cn);
name = (String)cmd.ExecuteScalar();
}
catch (Exception o)
{
Console.WriteLine("File: SMdb_access.cs Meth: test_place_exist SqlCeCommand: ");
Console.WriteLine(o.Message.ToString() + o.StackTrace);
throw new ArgumentException(o.Message.ToString());
}
}
else
{
MySqlCommand cmd;
try
{
String query = "SELECT version from updatelist where idx=(select max(idx) from updatelist)";
cmd = new MySqlCommand(query, cnMY);
name = (String)cmd.ExecuteScalar();
}
catch (MySqlException o)
{
Console.WriteLine("File: SMdb_access.cs Meth: test_place_exist MySqlCommand: ");
Console.WriteLine(o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
}
return name;
}
// add connection between user and vehicle
public void addVehicleUserConnection(string vehId, string userId)
{
if (MSSQL)
{
SqlCeCommand cmd;
try
{
cmd = new SqlCeCommand("INSERT INTO vehicle_user VALUES(" + vehId + "," + userId + ",'2007-01-01')", cn);
cmd.ExecuteNonQuery();
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
MessageBox.Show("SqlCe connection Failed !!!!", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MySqlCommand cmd;
try
{
cmd = new MySqlCommand("INSERT INTO vehicle_user VALUES(" + vehId + "," + userId + ",'2007-01-01')", cnMY);
cmd.ExecuteNonQuery();
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
MessageBox.Show("MySql connection Failed !!!!", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
//remove connection between user and vehicle
public void removeVehicleUserConnection(string vehId, string userId)
{
if (MSSQL)
{
SqlCeCommand cmd;
try
{
cmd = new SqlCeCommand("DELETE FROM vehicle_user WHERE veh_id ='" + vehId + "' and user_id='" + userId + "'", cn);
cmd.ExecuteNonQuery();
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
MessageBox.Show("SqlCe connection Failed !!!!", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MySqlCommand cmd;
try
{
cmd = new MySqlCommand("DELETE FROM vehicle_user WHERE veh_id ='" + vehId + "' and user_id='" + userId + "'", cnMY);
cmd.ExecuteNonQuery();
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
MessageBox.Show("MySql connection Failed !!!!", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}
}