public void fillCollection(Company company)
{
foreach (User user in company.Users) {
List<Collection> collections = new List<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.Add(collection);
user.Collections = collections;
mySqlDataReader.Dispose();
} else {
Collection collection = new Collection();
collection.Id = 0;
collection.Name = "All";
collection.Description = "All";
collections.Add(collection);
while (mySqlDataReader.Read()) {
Collection collectionItem = new Collection();
collectionItem.Id = mySqlDataReader.GetInt32("collid");
collectionItem.Name = mySqlDataReader.GetString("collname");
collectionItem.Description = mySqlDataReader.GetString("colldesc");
collections.Add(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 fillTrackers(Company company)
{
List<Tracker> trackers = new List<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(',');
List<User> trackerUsers = new List<User>();
foreach (User user in company.Users) {
for (int index = 0; index < trackerUser.ToList().Count; index++) {
if (Int32.Parse(trackerUser[index]) == user.Id) {
trackerUsers.Add(user);
}
}
}
Tracker tracker = new Tracker();
dynamic dynamicCollection = JsonConvert.DeserializeObject<dynamic>(mySqlDataReader.GetString("tcollections"));
JArray arrayCollection = (JArray)dynamicCollection;
List<Collection> collections = new List<Collection>();
Collection collectionItem = new Collection();
collectionItem.Id = 0;
collectionItem.Name = "All";
collectionItem.Description = "All";
collections.Add(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.Add(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.Add(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();
}
}