public void fillGeofences (Company company) { ConcurrentQueue<Geofence> geofences = new ConcurrentQueue<Geofence>(); try { mysqlConnection = new MySqlConnection(database.getConnectionString()); mysqlConnection.Open(); string sql = "SELECT * " + "FROM cmp_" + company.DatabaseName + ".gf"; MySqlCommand mySqlCommand = new MySqlCommand(sql, mysqlConnection); MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader(); if (!mySqlDataReader.HasRows) { mySqlDataReader.Dispose(); } else { while (mySqlDataReader.Read()) { Geofence geofence = new Geofence(); geofence.Id = mySqlDataReader.GetInt32("gf_id"); geofence.Name = mySqlDataReader.GetString("gf_name"); geofence.Tracks = mySqlDataReader.GetString("gf_trks"); string geofenceData = (string)mySqlDataReader["gf_data"]; geofenceData = geofenceData.Replace("),( ", "|"); geofenceData = geofenceData.Replace(")", string.Empty); geofenceData = geofenceData.Replace("(", string.Empty); geofenceData = geofenceData.Replace(" ", string.Empty); List<string> points = geofenceData.Split('|').ToList(); foreach (string point in points) { string[] coordinates = point.Split(','); double latitude = double.Parse(coordinates[0]); double longitude = double.Parse(coordinates[1]); Coordinate location = new Coordinate(latitude, longitude); geofence.addPoint(location); } geofences.Enqueue(geofence); } company.Geofences = geofences; mySqlDataReader.Dispose(); } } catch (MySqlException mySqlException) { throw new QueryException(1, mySqlException.Message); } catch (QueryException queryException) { throw queryException; } catch (Exception exception) { throw new QueryException(1, exception.Message); } finally { mysqlConnection.Close(); } }
private void PanelLogin_OnSubmitEventHandler (object sender, RoutedEventArgs e) { //Validation panelLogin.ErrorNote = ""; if (String.IsNullOrEmpty(panelLogin.CompanyUsername)) { panelLogin.ErrorNote = "Company field is empty."; return; } if (String.IsNullOrEmpty(panelLogin.Username)) { panelLogin.ErrorNote = "Username field is empty."; return; } if (String.IsNullOrEmpty(panelLogin.Password)) { panelLogin.ErrorNote = "Password field is empty."; return; } user = new User(); company = new Company(); company.Username = panelLogin.CompanyUsername; user.Username = panelLogin.Username; user.Password = Cryptography.md5(panelLogin.Password); user.RememberMe = panelLogin.RememberMe; ThreadPool.QueueUserWorkItem(new WaitCallback(run)); }
public FormMain(Company company, User user, List<Company> companies, Database database) { this.company = company; this.user = user; this.companies = companies; this.database = database; InitializeComponent(); }
public FormMain (Company company, User user, Database database) { InitializeComponent(); this.user = user; this.database = database; this.company = company; server = new Server(); server.Name = "Realtime"; server.Ip = Settings.Default.webServiceIp; server.PortCommand = 8001; server.PortHttp = Settings.Default.webServicePort; loadMap(); loadDefaultLayout(); }
//public DataTable getAllCompanies() { // DataTable dataTable = new DataTable(); // try { // mysqlConnection.Open(); // string sql = // "SELECT * " + // "FROM dbt_tracking_master.cmps;"; // MySqlCommand mySqlCommand = new MySqlCommand(sql, mysqlConnection); // MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader(); // if (!mySqlDataReader.HasRows) { // throw new QueryException(1, "Companies's Collection is empty."); // } else { // dataTable.Load(mySqlDataReader); // } // } catch (QueryException queryException) { // throw queryException; // } catch (MySqlException mySqlException) { // throw new QueryException(1, mySqlException.Message); // } catch (Exception exception) { // throw new QueryException(1, exception.Message); // } finally { // mysqlConnection.Close(); // } // return dataTable; //} //public TrackerData getTrackerLatestData (Company company, Tracker tracker) { // TrackerData trackerData = new TrackerData(); // trackerData.Tracker = tracker; // try { // mysqlConnection.Open(); // string sql = // "SELECT * " + // "FROM trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + " " + // "ORDER BY " + // "trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_time " + // "DESC limit 1;"; // //"SELECT * " + // // "FROM trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + " " + // // "ORDER BY " + // // "trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_id " + // // "DESC limit 1;"; // MySqlCommand mySqlCommand = new MySqlCommand(sql, mysqlConnection); // MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader(); // if (!mySqlDataReader.HasRows) { // trackerData.IsDataEmpty = true; // return trackerData; // } else { // mySqlDataReader.Read(); // trackerData.IsDataEmpty = false; // trackerData.Id = mySqlDataReader.GetInt32("gm_id"); // double dateTime = double.Parse(mySqlDataReader.GetString("gm_time")); // trackerData.DateTime = Parser.UnixTime.toDateTime(dateTime); // double latitude = double.Parse(mySqlDataReader.GetString("gm_lat")); // double longitude = double.Parse(mySqlDataReader.GetString("gm_lng")); // trackerData.Coordinate = new Coordinate(latitude, longitude); // trackerData.Speed = int.Parse(mySqlDataReader.GetString("gm_speed")); // trackerData.Degrees = int.Parse(mySqlDataReader.GetString("gm_ori")); // trackerData.Direction = Direction.degreesToCardinalDetailed(double.Parse(mySqlDataReader.GetString("gm_ori"))); // //trackerData.Mileage = double.Parse(mySqlDataReader.GetString("gm_mileage")); // double deviceMileage = double.Parse(mySqlDataReader.GetString("gm_mileage")); // double carMileage = (tracker.MileageInitial); // trackerData.Mileage = deviceMileage + carMileage; // //1, // (0) // //35, //Event code(Decimal) // //11, //Number of satellites(Decimal) // //26, //GSM signal status(Decimal) // //17160691, //Mileage(Decimal)unit: meter // //0.7, //hpos accuracy(Decimal) // //18, //Altitude(Decimal)unit: meter // //18661240, //Run time(Decimal)unit: second // //427|2|0078|283F, //Base station information(binary|binary|hex|hex) (8) // //==============================================0200 // //0,0,0,0,0,0,0,0, //Io port lowbyte (low bit start from left) (9) // //0,1,0,0,0,0,0,0, //Io port lowbyte (low bit start from left) (17) // //============================================== // //000B,0000,0000,0A6E,0434, //Analog input value (25) // //00000001 //System mark // string gmData = (string)mySqlDataReader["gm_data"]; // string[] data = gmData.Split(','); // trackerData.EventCode = (EventCode)int.Parse(data[1]); // trackerData.GpsSatellites = int.Parse(data[2]); // trackerData.GsmSignal = int.Parse(data[3]); // trackerData.Altitude = int.Parse(data[6]); // trackerData.ACC = (int.Parse(data[18]) == 1) ? true : false; // trackerData.SOS = (int.Parse(data[17]) == 1) ? true : false; // trackerData.OverSpeed = ((int)trackerData.Speed > tracker.SpeedLimit) ? true : false; // //Geofence // Coordinate coordinate = new Coordinate(latitude, longitude); // foreach (Geofence geofence in company.Geofences) { // if (Geofence.isPointInPolygon(geofence, coordinate)) { // trackerData.Geofence = geofence; // } // }; // double batteryStrength = (double)int.Parse(data[28], System.Globalization.NumberStyles.AllowHexSpecifier); // batteryStrength = ((batteryStrength - 2114f) * (100f / 492f));//*100.0; // batteryStrength = Math.Round(batteryStrength, 2); // if (batteryStrength > 100) { // batteryStrength = 100f; // } else if (batteryStrength < 0) { // batteryStrength = 0; // } // double batteryVoltage = (double)int.Parse(data[28], System.Globalization.NumberStyles.AllowHexSpecifier); // batteryVoltage = (batteryVoltage * 3 * 2) / 1024; // batteryVoltage = Math.Round(batteryVoltage, 2); // double externalVoltage = (double)int.Parse(data[29], System.Globalization.NumberStyles.AllowHexSpecifier); // externalVoltage = (externalVoltage * 3 * 16) / 1024; // externalVoltage = Math.Round(externalVoltage, 2); // trackerData.Battery = batteryStrength; // trackerData.BatteryVoltage = batteryVoltage; // trackerData.ExternalVoltage = externalVoltage; // return trackerData; // } // //} catch (QueryException queryException) { // //throw queryException; // //} catch (MySqlException mySqlException) { // //throw new QueryException(1, mySqlException.Message); // } catch (Exception exception) { // Debug.Write(exception); // //throw new QueryException(1, exception.Message); // return trackerData; // } finally { // mysqlConnection.Close(); // } //} public TrackerData getTrackerLatestData (Company company, Tracker tracker, Server server) { TrackerData trackerData = new TrackerData(); trackerData.Tracker = tracker; try { WebRequest request = WebRequest.Create("http://" + server.Ip + "/connect/get_realtime.php?url=http://" + server.Ip + ":" + server.PortHttp + "/?id=" + tracker.TrackerImei); // If required by the server, set the credentials. request.Credentials = CredentialCache.DefaultCredentials; // Get the response. WebResponse response = request.GetResponse(); // Display the status. //Console.WriteLine(((HttpWebResponse)response).StatusDescription); // Get the stream containing content returned by the server. Stream dataStream = response.GetResponseStream(); // Open the stream using a StreamReader for easy access. StreamReader reader = new StreamReader(dataStream); // Read the content. string responseFromServer = reader.ReadToEnd(); // Display the content. //Console.WriteLine(responseFromServer); // Clean up the streams and the response. reader.Close(); response.Close(); //=========================================== //=========================================== responseFromServer = responseFromServer.Trim(); if (responseFromServer == "1") { trackerData.IsDataEmpty = true; return trackerData; } else { dynamic jsonData = (dynamic)JsonConvert.DeserializeObject<dynamic>(responseFromServer); trackerData.IsDataEmpty = false; trackerData.Tracker.TrackerImei = (string)jsonData.gm_unit; double dateTime = double.Parse((string)jsonData.gm_time); trackerData.DateTime = Parser.UnixTime.toDateTime(dateTime); //DateTime dateTime1 = Parser.UnixTime.toDateTime(dateTime); //TimeSpan timeSpan = dateTime1.Subtract(DateTime.Now); //if (timeSpan.Hours > 6) { // trackerData.DateTime = dateTime1; //} else { // trackerData.DateTime = DateTime.Now; //} double latitude = 0; double.TryParse((string)jsonData.gm_lat, NumberStyles.AllowDecimalPoint, CultureInfo.InvariantCulture, out latitude); double longitude = 0; double.TryParse((string)jsonData.gm_lng, NumberStyles.AllowDecimalPoint, CultureInfo.InvariantCulture, out longitude); trackerData.Coordinate = new Coordinate(latitude, longitude); trackerData.Speed = int.Parse((string)jsonData.gm_speed); trackerData.Degrees = int.Parse((string)jsonData.gm_ori); trackerData.Direction = Direction.degreesToCardinalDetailed(double.Parse((string)jsonData.gm_ori)); //trackerData.Mileage = double.Parse((string)jsonData.gm_mileage); double deviceMileage = 0; double.TryParse((string)jsonData.gm_mileage, NumberStyles.AllowDecimalPoint, CultureInfo.InvariantCulture, out deviceMileage); double carMileage = (tracker.MileageInitial); trackerData.Mileage = deviceMileage + carMileage; //1, //(0) //35, //Event code(Decimal) //11, //Number of satellites(Decimal) //26, //GSM signal status(Decimal) //17160691, //Mileage(Decimal)unit: meter //0.7, //hpos accuracy(Decimal) //18, //Altitude(Decimal)unit: meter //18661240, //Run time(Decimal)unit: second //427|2|0078|283F, //Base station information(binary|binary|hex|hex) (8) //==============================================0200 //0,0,0,0,0,0,0,0, //Io port lowbyte (low bit start from left) (9) //0,1,0,0,0,0,0,0, //Io port lowbyte (low bit start from left) (17) //============================================== //000B,0000,0000,0A6E,0434, //Analog input value (25) //00000001 //System mark string gmData = (string)jsonData.gm_data; string[] data = gmData.Split(','); trackerData.EventCode = (TqatProModel.Devices.Meitrack.EventCode)int.Parse(data[1]); trackerData.GpsSatellites = int.Parse(data[2]); trackerData.GsmSignal = int.Parse(data[3]); trackerData.Altitude = int.Parse(data[6]); trackerData.ACC = (int.Parse(data[18]) == 1) ? true : false; trackerData.SOS = (int.Parse(data[17]) == 1) ? true : false; trackerData.OverSpeed = ((int)trackerData.Speed > tracker.SpeedLimit) ? true : false; //Geofence Coordinate coordinate = new Coordinate(latitude, longitude); if (company.Geofences != null) { foreach (Geofence geofence in company.Geofences) { if (Geofence.isPointInPolygon(geofence, coordinate)) { trackerData.Geofence = geofence; } }; } double batteryVoltage = (double)int.Parse(data[28], System.Globalization.NumberStyles.AllowHexSpecifier); batteryVoltage = (batteryVoltage * 3.3 * 2) / 4096; double externalVoltage = (double)int.Parse(data[29], System.Globalization.NumberStyles.AllowHexSpecifier); externalVoltage = (externalVoltage * 3.3 * 16) / 4096; double batteryStrength = batteryVoltage - 3.5; batteryStrength = (batteryStrength / 0.8) * 100; if (batteryStrength < 0) { batteryStrength = 0; } else if (batteryStrength > 100) { batteryStrength = 100; } trackerData.EPC = (externalVoltage > 3) ? false : true; trackerData.Battery = Math.Round(batteryStrength, 2); trackerData.BatteryVoltage = Math.Round(batteryVoltage, 2); trackerData.ExternalVoltage = Math.Round(externalVoltage, 2); return trackerData; } } catch (QueryException queryException) { throw queryException; } catch (MySqlException mySqlException) { throw new QueryException(1, mySqlException.Message); } catch (Exception exception) { Debug.Write(exception); mysqlConnection.Close(); throw new QueryException(1, exception.Message); } finally { mysqlConnection.Close(); } }
public void fillTrackers (Company company) { ConcurrentQueue<Tracker> trackers = new ConcurrentQueue<Tracker>(); try { mysqlConnection.Open(); string sql = "SELECT * " + "FROM dbt_tracking_master.trks " + "WHERE dbt_tracking_master.trks.tcmp = @CompanyDatabaseName"; MySqlCommand mySqlCommand = new MySqlCommand(sql, mysqlConnection); mySqlCommand.Parameters.AddWithValue("@CompanyDatabaseName", company.DatabaseName); MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader(); if (!mySqlDataReader.HasRows) { //throw new QueryException(1, "Tracker's Collection is empty."); mySqlDataReader.Dispose(); mysqlConnection.Close(); } else { string dateTime; while (mySqlDataReader.Read()) { string[] trackerUser = mySqlDataReader.GetString("tusers").Split(','); ConcurrentQueue<User> trackerUsers = new ConcurrentQueue<User>(); foreach (User user in company.Users) { for (int index = 0; index < trackerUser.ToList().Count; index++) { if (Int32.Parse(trackerUser[index]) == user.Id) { trackerUsers.Enqueue(user); } } } Tracker tracker = new Tracker(); dynamic dynamicCollection = JsonConvert.DeserializeObject<dynamic>(mySqlDataReader.GetString("tcollections")); JArray arrayCollection = (JArray)dynamicCollection; ConcurrentQueue<Collection> collections = new ConcurrentQueue<Collection>(); Collection collectionItem = new Collection(); collectionItem.Id = 0; collectionItem.Name = "All"; collectionItem.Description = "All"; collections.Enqueue(collectionItem); foreach (User user in company.Users) { if (user.Collections == null) { continue; } foreach (Collection collection in user.Collections) { for (int index = 0; index < arrayCollection.Count; index++) { if (dynamicCollection[index].value == collection.Id) { collections.Enqueue(collection); } } } } tracker.Collections = collections; //tracker.Collections = mySqlDataReader.GetString("tcollections"); tracker.CompanyDatabaseName = (string)mySqlDataReader["tcmp"]; tracker.DatabaseHost = int.Parse((string)mySqlDataReader["tdbhost"]); tracker.DatabaseName = (string)mySqlDataReader["tdbs"]; dateTime = (string)mySqlDataReader["tcreatedate"]; tracker.DateTimeCreated = SubStandard.dateTime(dateTime); dateTime = String.Empty; dateTime = (string)mySqlDataReader["ttrackerexpiry"]; tracker.DateTimeExpired = SubStandard.dateTime(dateTime); dateTime = String.Empty; tracker.TrackerImei = (string)mySqlDataReader["tunit"]; tracker.DevicePassword = (string)mySqlDataReader["tunitpassword"]; tracker.DeviceType = int.Parse((string)mySqlDataReader["ttype"]); tracker.DriverName = (string)mySqlDataReader["tdrivername"]; tracker.Emails = (string)mySqlDataReader["temails"]; tracker.HttpHost = int.Parse((string)mySqlDataReader["thttphost"]); tracker.Id = (int)mySqlDataReader["tid"]; tracker.IdlingTime = int.Parse((string)mySqlDataReader["tidlingtime"]); tracker.ImageNumber = int.Parse((string)mySqlDataReader["timg"]); tracker.Inputs = (string)mySqlDataReader["tinputs"]; tracker.MileageInitial = int.Parse((string)mySqlDataReader["tmileageInit"]); tracker.MileageLimit = int.Parse((string)mySqlDataReader["tmileagelimit"]); tracker.MobileDataProvider = int.Parse((string)mySqlDataReader["tprovider"]); tracker.Note = (string)mySqlDataReader["tnote"]; tracker.OwnerName = (string)mySqlDataReader["townername"]; tracker.SimImei = (string)mySqlDataReader["tsimsr"]; tracker.SimNumber = (string)mySqlDataReader["tsimno"]; tracker.Users = trackerUsers; tracker.SpeedLimit = int.Parse((string)mySqlDataReader["tSpeedLimit"]); tracker.VehicleModel = (string)mySqlDataReader["tvehiclemodel"]; tracker.VehicleRegistration = (string)mySqlDataReader["tvehiclereg"]; trackers.Enqueue(tracker); } company.Trackers = trackers; mySqlDataReader.Dispose(); } } catch (QueryException queryException) { throw queryException; } catch (MySqlException mySqlException) { throw new QueryException(1, mySqlException.Message); } catch (Exception exception) { throw new QueryException(1, exception.Message); } finally { mysqlConnection.Close(); } }
public void fillPois (Company company) { foreach (User user in company.Users) { ConcurrentQueue<Poi> pois = new ConcurrentQueue<Poi>(); try { mysqlConnection = new MySqlConnection(database.getConnectionString()); mysqlConnection.Open(); string sql = "SELECT * " + "FROM cmp_" + company.DatabaseName + ".poi_" + user.DatabaseName + ";"; MySqlCommand mySqlCommand = new MySqlCommand(sql, mysqlConnection); MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader(); if (!mySqlDataReader.HasRows) { mySqlDataReader.Dispose(); } else { while (mySqlDataReader.Read()) { Poi poi = new Poi(); poi.Id = mySqlDataReader.GetInt32("poi_id"); poi.Name = mySqlDataReader.GetString("poi_name"); poi.Description = mySqlDataReader.GetString("poi_desc"); poi.Image = mySqlDataReader.GetString("poi_img"); string poiLatitude = mySqlDataReader.GetString("poi_lat"); string potLongitude = mySqlDataReader.GetString("poi_lon"); if (String.IsNullOrEmpty(poiLatitude) || String.IsNullOrEmpty(potLongitude)) { poi.Coordinate = new Coordinate(0.0f, 0.0f); } else { poi.Coordinate = new Coordinate(double.Parse(poiLatitude), double.Parse(potLongitude)); } //if (!String.IsNullOrEmpty(poiLatitude) || !String.IsNullOrEmpty(potLongitude)) { // poi.Coordinate = new Coordinate( // double.Parse(poiLatitude), // double.Parse(potLongitude) // ); //} pois.Enqueue(poi); } user.Pois = pois; mySqlDataReader.Dispose(); } } catch (MySqlException mySqlException) { throw new QueryException(1, mySqlException.Message); } catch (QueryException queryException) { throw queryException; } catch (Exception exception) { throw new QueryException(1, exception.Message); } finally { mysqlConnection.Close(); } } }
public void fillCollection (Company company) { foreach (User user in company.Users) { ConcurrentQueue<Collection> collections = new ConcurrentQueue<Collection>(); try { mysqlConnection = new MySqlConnection(database.getConnectionString()); mysqlConnection.Open(); string sql = "SELECT * " + "FROM cmp_" + company.DatabaseName + ".coll_" + user.DatabaseName + ";"; MySqlCommand mySqlCommand = new MySqlCommand(sql, mysqlConnection); MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader(); if (!mySqlDataReader.HasRows) { Collection collection = new Collection(); collection.Id = 0; collection.Name = "All"; collection.Description = "All"; collections.Enqueue(collection); user.Collections = collections; mySqlDataReader.Dispose(); } else { Collection collection = new Collection(); collection.Id = 0; collection.Name = "All"; collection.Description = "All"; collections.Enqueue(collection); while (mySqlDataReader.Read()) { Collection collectionItem = new Collection(); collectionItem.Id = mySqlDataReader.GetInt32("collid"); collectionItem.Name = mySqlDataReader.GetString("collname"); collectionItem.Description = mySqlDataReader.GetString("colldesc"); collections.Enqueue(collectionItem); } user.Collections = collections; mySqlDataReader.Dispose(); } } catch (MySqlException mySqlException) { throw new QueryException(1, mySqlException.Message); } catch (QueryException queryException) { throw queryException; } catch (Exception exception) { throw new QueryException(1, exception.Message); } finally { mysqlConnection.Close(); } } }
//public void fillPois(Company company, User user) { // ConcurrentQueue<Poi> pois = new ConcurrentQueue<Poi>(); // try { // mysqlConnection = new MySqlConnection(database.getConnectionString()); // mysqlConnection.Open(); // string sql = // "SELECT * " + // "FROM cmp_" + company.DatabaseName + ".poi_" + user.DatabaseName + ";"; // MySqlCommand mySqlCommand = new MySqlCommand(sql, mysqlConnection); // MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader(); // if (!mySqlDataReader.HasRows) { // mySqlDataReader.Dispose(); // } else { // while (mySqlDataReader.Read()) { // Poi poi = new Poi(); // poi.Id = mySqlDataReader.GetInt32("poi_id"); // poi.Name = mySqlDataReader.GetString("poi_name"); // poi.Description = mySqlDataReader.GetString("poi_desc"); // poi.Image = mySqlDataReader.GetString("poi_img"); // string latitude = mySqlDataReader.GetString("poi_lat"); // string longitude = mySqlDataReader.GetString("poi_lon"); // if(String.IsNullOrEmpty(latitude) || String.IsNullOrEmpty(longitude)) { // poi.Coordinate = new Coordinate(0.0f, 0.0f); // } else { // poi.Coordinate = new Coordinate(double.Parse(latitude), double.Parse(longitude)); // } // pois.Enqueue(poi); // } // user.Pois = pois; // mySqlDataReader.Dispose(); // } // } catch (MySqlException mySqlException) { // throw new QueryException(1, mySqlException.Message); // } catch (QueryException queryException) { // throw queryException; // } catch (Exception exception) { // throw new QueryException(1, exception.Message); // } finally { // mysqlConnection.Close(); // } //} public void fillUsers (Company company, User user) { ConcurrentQueue<User> users = new ConcurrentQueue<User>(); try { mysqlConnection = new MySqlConnection(database.getConnectionString()); mysqlConnection.Open(); string sql = ""; if (user.AccessLevel == 1 || user.AccessLevel == 2) { sql = "SELECT * " + "FROM cmp_" + company.DatabaseName + ".usrs " + "WHERE cmp_" + company.DatabaseName + ".usrs.upriv >= " + user.AccessLevel.ToString() + ";"; } else { sql = "SELECT * " + "FROM cmp_" + company.DatabaseName + ".usrs " + "WHERE cmp_" + company.DatabaseName + ".usrs.upriv = " + user.AccessLevel.ToString() + " and " + "cmp_" + company.DatabaseName + ".usrs.uname = '" + user.Username + "';"; } MySqlCommand mySqlCommand = new MySqlCommand(sql, mysqlConnection); MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader(); if (!mySqlDataReader.HasRows) { mySqlDataReader.Dispose(); throw new QueryException(1, "Users table is empty."); } else { while (mySqlDataReader.Read()) { User userSubordinate = new User(); userSubordinate.Id = mySqlDataReader.GetInt32("uid"); userSubordinate.Username = mySqlDataReader.GetString("uname"); userSubordinate.Password = mySqlDataReader.GetString("upass"); userSubordinate.Email = mySqlDataReader.GetString("uemail"); userSubordinate.Main = mySqlDataReader.GetString("umain"); userSubordinate.AccessLevel = int.Parse(mySqlDataReader.GetString("upriv")); userSubordinate.Timezone = mySqlDataReader.GetString("utimezone"); userSubordinate.IsActive = mySqlDataReader.GetString("uactive").Equals("1"); userSubordinate.DatabaseName = mySqlDataReader.GetString("udbs"); if (!String.IsNullOrEmpty(mySqlDataReader.GetString("uexpiredate"))) { string dateTime = (mySqlDataReader.GetString("uexpiredate")); if (!String.IsNullOrEmpty(dateTime)) { DateTime parsedDate = SubStandard.dateTime(dateTime); userSubordinate.DateTimeExpired = parsedDate; } } else { userSubordinate.DateTimeExpired = new DateTime(2050, 01, 01); } if (!String.IsNullOrEmpty(mySqlDataReader.GetString("ucreatedate"))) { string dateTime = mySqlDataReader.GetString("ucreatedate"); if (!String.IsNullOrEmpty(dateTime)) { DateTime parsedDate = SubStandard.dateTime(dateTime); userSubordinate.DateTimeCreated = parsedDate; } } else { userSubordinate.DateTimeCreated = new DateTime(2010, 01, 01); } users.Enqueue(userSubordinate); } mySqlDataReader.Dispose(); } } catch (MySqlException mySqlException) { throw new QueryException(1, mySqlException.Message); } catch (QueryException queryException) { throw queryException; } catch (Exception exception) { throw new QueryException(1, exception.Message); } finally { mysqlConnection.Close(); } company.Users = users; }
public void getUser (Company company, User user) { try { mysqlConnection = new MySqlConnection(database.getConnectionString()); mysqlConnection.Open(); string sql = "SELECT * " + "FROM cmp_" + company.DatabaseName + ".usrs " + "WHERE " + "cmp_" + company.DatabaseName + ".usrs.uname = @sUsername AND " + "cmp_" + company.DatabaseName + ".usrs.upass = @sPassword;"; MySqlCommand mySqlCommand = new MySqlCommand(sql, mysqlConnection); mySqlCommand.Parameters.AddWithValue("@sUsername", user.Username); mySqlCommand.Parameters.AddWithValue("@sPassword", user.Password); MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader(); if (!mySqlDataReader.HasRows) { mySqlDataReader.Dispose(); throw new QueryException(1, "Username or Password does not exist."); } else { mySqlDataReader.Read(); user.Id = mySqlDataReader.GetInt32("uid"); user.Username = mySqlDataReader.GetString("uname"); user.Password = mySqlDataReader.GetString("upass"); user.Email = mySqlDataReader.GetString("uemail"); user.Main = mySqlDataReader.GetString("umain"); user.AccessLevel = int.Parse(mySqlDataReader.GetString("upriv")); user.Timezone = mySqlDataReader.GetString("utimezone"); user.IsActive = mySqlDataReader.GetString("uactive").Equals("1"); user.DatabaseName = mySqlDataReader.GetString("udbs"); if (!String.IsNullOrEmpty(mySqlDataReader.GetString("uexpiredate"))) { string dateTime = (mySqlDataReader.GetString("uexpiredate")); if (!String.IsNullOrEmpty(dateTime)) { DateTime parsedDate = SubStandard.dateTime(dateTime); user.DateTimeExpired = parsedDate; } } else { user.DateTimeExpired = new DateTime(2050, 01, 01); } if (!String.IsNullOrEmpty(mySqlDataReader.GetString("ucreatedate"))) { string dateTime = mySqlDataReader.GetString("ucreatedate"); if (!String.IsNullOrEmpty(dateTime)) { DateTime parsedDate = SubStandard.dateTime(dateTime); user.DateTimeCreated = parsedDate; } } else { user.DateTimeCreated = new DateTime(2010, 01, 01); } mySqlDataReader.Dispose(); } } catch (MySqlException mySqlException) { throw new QueryException(1, mySqlException.Message); } catch (QueryException queryException) { throw queryException; } catch (Exception exception) { throw new QueryException(1, exception.Message); } finally { mysqlConnection.Close(); } }
public List<Company> getCompanies () { List<Company> companies = new List<Company>(); try { mysqlConnection.Open(); string sql = "SELECT * " + "FROM dbt_tracking_master.cmps;"; MySqlCommand mySqlCommand = new MySqlCommand(sql, mysqlConnection); MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader(); if (!mySqlDataReader.HasRows) { mySqlDataReader.Dispose(); throw new QueryException(1, "Company list is empty."); } else { while (mySqlDataReader.Read()) { Company company = new Company(); company.Id = mySqlDataReader.GetInt32("cmpid"); company.Username = mySqlDataReader.GetString("cmpname"); company.DisplayName = mySqlDataReader.GetString("cmpdisplayname"); company.Host = int.Parse(mySqlDataReader.GetString("cmphost")); company.Email = mySqlDataReader.GetString("cmpemail"); company.PhoneNo = mySqlDataReader.GetString("cmpphoneno"); company.MobileNo = mySqlDataReader.GetString("cmpmobileno"); company.Address = mySqlDataReader.GetString("cmpaddress"); company.IsActive = (mySqlDataReader.GetString("cmpactive") == "1") ? true : false; company.DateTimeCreated = SubStandard.dateTime(mySqlDataReader.GetString("cmpcreatedate")); company.DateTimeExpired = SubStandard.dateTime(mySqlDataReader.GetString("cmpexpiredate")); company.DatabaseName = mySqlDataReader.GetString("cmpdbname"); companies.Add(company); } mySqlDataReader.Dispose(); } } catch (MySqlException mySqlException) { throw new QueryException(1, mySqlException.Message); } catch (QueryException queryException) { throw queryException; } catch (Exception exception) { throw new QueryException(1, exception.Message); } finally { mysqlConnection.Close(); } return companies; }
public void getCompany (Company company) { try { mysqlConnection.Open(); string sql = "SELECT * " + "FROM dbt_tracking_master.cmps " + "WHERE dbt_tracking_master.cmps.cmpname = @sCompanyName;"; MySqlCommand mySqlCommand = new MySqlCommand(sql, mysqlConnection); mySqlCommand.Parameters.AddWithValue("@sCompanyName", company.Username); MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader(); if (!mySqlDataReader.HasRows) { mySqlDataReader.Dispose(); throw new QueryException(1, "Company does not exist."); } else { mySqlDataReader.Read(); company.Id = mySqlDataReader.GetInt32("cmpid"); company.Username = mySqlDataReader.GetString("cmpname"); company.DisplayName = mySqlDataReader.GetString("cmpdisplayname"); company.Host = int.Parse(mySqlDataReader.GetString("cmphost")); company.Email = mySqlDataReader.GetString("cmpemail"); company.PhoneNo = mySqlDataReader.GetString("cmpphoneno"); company.MobileNo = mySqlDataReader.GetString("cmpmobileno"); company.Address = mySqlDataReader.GetString("cmpaddress"); company.IsActive = (mySqlDataReader.GetString("cmpactive") == "1") ? true : false; company.DateTimeCreated = SubStandard.dateTime(mySqlDataReader.GetString("cmpcreatedate")); company.DateTimeExpired = SubStandard.dateTime(mySqlDataReader.GetString("cmpexpiredate")); company.DatabaseName = mySqlDataReader.GetString("cmpdbname"); mySqlDataReader.Dispose(); } } catch (MySqlException mySqlException) { throw new QueryException(1, mySqlException.Message); } catch (QueryException queryException) { throw queryException; } catch (Exception exception) { throw new QueryException(1, exception.Message); } finally { mysqlConnection.Close(); } }