Ejemplo n.º 1
0
        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();
                }
            }
        }
Ejemplo n.º 2
0
        public FormMain(Company company, User user)
        {
            InitializeComponent();

            this.DoubleBuffered = true;
            SetStyle(ControlStyles.OptimizedDoubleBuffer, true);

            this.user = user;
            this.company = company;

            database = new Database(Settings.Default.DatabaseHost, Settings.Default.DatabaseUsername, Settings.Default.DatabasePassword, 30);
        }
Ejemplo n.º 3
0
        static void Main()
        {
            //if (Debugger.IsAttached) {
            //    Settings.Default.Reset();
            //}

            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);

            //0 = ResetMode
            //1 = DevelopersMode
            //2 = Static Ip
            int appMode = 2;
            //int appServer = 2;

            if (appMode == 0) {
                Settings.Default.Reset();
            }

            User user = new User();
            Company company = new Company();

            while (true) {
                DialogLogin dialogLogin = new DialogLogin(company,user);
                DialogResult dialogResult = dialogLogin.ShowDialog();

                if (dialogResult == DialogResult.OK) {
                    FormMain formMain = new FormMain(company, user);
                    dialogResult = formMain.ShowDialog();
                    if (dialogResult == DialogResult.Cancel) {
                        Environment.Exit(0);
                    } else {
                        continue;
                    }
                } else {
                    Environment.Exit(0);
                }
            }
        }
Ejemplo n.º 4
0
        public DataTable getTrackerAccData(Company company, User user, DateTime dateTimeDateFrom, DateTime dateTimeDateTo, int limit, int offset, Tracker tracker)
        {
            DataTable dataTableHistoricalData = this.getTrackerHistoricalData(company, user, dateTimeDateFrom, dateTimeDateTo, limit, offset, tracker);
            DataTable dataTableIAccData = this.getReportTable(ReportType.ACC);
            dataTableIAccData.TableName = tracker.DatabaseName;

            try {

                bool accStatusNow = false;
                bool accStatusBefore = false;

                int index = 0;

                double speed = 0;
                double speedDivisor = 0;
                double speedAccumulator = 0;
                double speedMax = 0;
                double speedAverage = 0;

                double distanceCovered = 0;
                double distance = 0;

                String geofenceFrom = "";
                String geofenceTo = "";

                DateTime dateTimeRunningFrom = new DateTime();
                DateTime dateTimeRunningTo = new DateTime();

                double latitudeTo = 0;
                double latitudeFrom = 0;
                double longitudeTo = 0;
                double longitudeFrom = 0;

                TimeSpan timeSpan;

                for (int no = 0; no < dataTableHistoricalData.Rows.Count; no++) {
                    //Initialized
                    accStatusNow = (bool)dataTableHistoricalData.Rows[no]["ACC"];
                    speed = (int)dataTableHistoricalData.Rows[no]["Speed"];
                    distance = (double)dataTableHistoricalData.Rows[no]["Distance"];

                    //OneTime
                    if ((int)dataTableHistoricalData.Rows[no]["No"] == 1) {
                        dateTimeRunningFrom = dateTimeDateFrom;//(DateTime)dataTableHistoricalData.Rows[no]["DateTime"];
                        latitudeFrom = (double)dataTableHistoricalData.Rows[no]["Latitude"];
                        longitudeFrom = (double)dataTableHistoricalData.Rows[no]["Longitude"];
                        geofenceFrom = (dataTableHistoricalData.Rows[no]["Geofence"] == System.DBNull.Value) ? "OUTSIDE" : (string)dataTableHistoricalData.Rows[no]["Geofence"];

                        accStatusBefore = accStatusNow;
                    }

                    if (accStatusBefore != accStatusNow) {
                        index++;
                        dateTimeRunningTo = (DateTime)dataTableHistoricalData.Rows[no]["DateTime"];
                        latitudeTo = (double)dataTableHistoricalData.Rows[no]["Latitude"];
                        longitudeTo = (double)dataTableHistoricalData.Rows[no]["Longitude"];
                        geofenceTo = (dataTableHistoricalData.Rows[no]["Geofence"] == System.DBNull.Value) ? "OUTSIDE" : (string)dataTableHistoricalData.Rows[no]["Geofence"];

                        timeSpan = dateTimeRunningTo - dateTimeRunningFrom;

                        speedAverage = speedAccumulator / 1;
                        speedAccumulator = 0;
                        speedDivisor = 0;

                        DataRow dataRowAccData = dataTableIAccData.NewRow();
                        dataRowAccData["No"] = index;
                        dataRowAccData["Status"] = accStatusBefore;
                        dataRowAccData["DateTimeFrom"] = dateTimeRunningFrom;
                        dataRowAccData["DateTimeTo"] = dateTimeRunningTo;

                        dataRowAccData["LatitudeFrom"] = latitudeFrom;
                        dataRowAccData["LatitudeTo"] = latitudeTo;
                        dataRowAccData["LongitudeFrom"] = longitudeFrom;
                        dataRowAccData["LongitudeTo"] = longitudeTo;

                        dataRowAccData["Time"] = timeSpan;
                        dataRowAccData["Distance"] = distanceCovered;

                        dataRowAccData["Fuel"] = distanceCovered / Settings.Default.fuelLiterToKilometer;
                        dataRowAccData["Cost"] = (double)dataRowAccData["Fuel"] / Settings.Default.fuelLiterToCost;

                        if (geofenceTo.Equals(geofenceFrom)) {
                            dataRowAccData["Geofences"] = geofenceFrom;
                        } else {
                            dataRowAccData["Geofences"] = geofenceFrom + " to " + geofenceTo;
                        }
                        dataRowAccData["SpeedMax"] = speedMax;
                        dataRowAccData["SpeedAve"] = speedAverage;

                        speedMax = 0;
                        distanceCovered = 0;
                        dateTimeRunningFrom = dateTimeRunningTo;
                        longitudeFrom = longitudeTo;
                        latitudeFrom = latitudeTo;
                        geofenceFrom = geofenceTo;

                        dataTableIAccData.Rows.Add(dataRowAccData);
                        accStatusBefore = accStatusNow;

                    }
                    if (no == dataTableHistoricalData.Rows.Count - 1) {
                        index++;
                        dateTimeRunningTo = dateTimeDateTo;//(DateTime)dataTableHistoricalData.Rows[no]["DateTime"];
                        latitudeTo = (double)dataTableHistoricalData.Rows[no]["Latitude"];
                        longitudeTo = (double)dataTableHistoricalData.Rows[no]["Longitude"];
                        geofenceTo = (dataTableHistoricalData.Rows[no]["Geofence"] == System.DBNull.Value) ? "OUTSIDE" : (string)dataTableHistoricalData.Rows[no]["Geofence"];

                        timeSpan = dateTimeRunningTo - dateTimeRunningFrom;

                        speedAverage = speedAccumulator / 1;
                        speedAccumulator = 0;
                        speedDivisor = 0;

                        DataRow dataRowAccData = dataTableIAccData.NewRow();
                        dataRowAccData["No"] = index;
                        dataRowAccData["Status"] = accStatusNow;

                        dataRowAccData["DateTimeFrom"] = dateTimeRunningFrom;
                        dataRowAccData["DateTimeTo"] = dateTimeRunningTo;

                        dataRowAccData["LatitudeFrom"] = latitudeFrom;
                        dataRowAccData["LatitudeTo"] = latitudeTo;
                        dataRowAccData["LongitudeFrom"] = longitudeFrom;
                        dataRowAccData["LongitudeTo"] = longitudeTo;

                        dataRowAccData["Time"] = timeSpan;
                        dataRowAccData["Distance"] = distanceCovered;

                        dataRowAccData["Fuel"] = distanceCovered / Settings.Default.fuelLiterToKilometer;
                        dataRowAccData["Cost"] = (double)dataRowAccData["Fuel"] / Settings.Default.fuelLiterToCost;

                        if (geofenceTo.Equals(geofenceFrom)) {
                            dataRowAccData["Geofences"] = geofenceFrom;
                        } else {
                            dataRowAccData["Geofences"] = geofenceFrom + " to " + geofenceTo;
                        }

                        dataRowAccData["SpeedMax"] = speedMax;
                        dataRowAccData["SpeedAve"] = speedAverage;

                        speedMax = 0;
                        distanceCovered = 0;
                        dateTimeRunningFrom = dateTimeRunningTo;
                        longitudeFrom = longitudeTo;
                        latitudeFrom = latitudeTo;
                        geofenceFrom = geofenceTo;

                        dataTableIAccData.Rows.Add(dataRowAccData);
                        accStatusBefore = accStatusNow;

                    }

                    //Accumulators : SpeedAve, SpeedMax, Distance
                    speedDivisor++;
                    speedAccumulator += speed;
                    if (speed > speedMax) {
                        speedMax = speed;
                    }
                    distanceCovered += distance;
                }
            } catch (QueryException queryException) {
                throw queryException;
            } catch (Exception exception) {
                throw new QueryException(1, exception.Message);
            } finally {

            }
            return dataTableIAccData;
        }
Ejemplo n.º 5
0
        public DataTable getTrackersGeofence(Company company, User user, DateTime dateTimeDateFrom, DateTime dateTimeDateTo, int limit, int offset, List<Tracker> trackerList)
        {
            DataTable dataTable = this.getReportTable(ReportType.TRACKERS_GEOFENCE);
            //dataTable.TableName = "";
            try {
                int index = 0;
                foreach (Tracker tracker in trackerList) {
                    index++;

                    DataTable dataTableGeofence = (this.getTrackerGeofence(company, user, dateTimeDateFrom, dateTimeDateTo, limit, offset, tracker));

                    if (dataTableGeofence.Rows.Count <= 0) {
                        DataRow dataRow = dataTable.NewRow();
                        dataRow["No"] = index;
                        dataRow["VehicleRegistration"] = tracker.VehicleRegistration;
                        dataRow["DriverName"] = tracker.DriverName;
                        dataRow["DeviceImei"] = tracker.TrackerImei;

                        dataRow["DateTimeFrom"] = dateTimeDateFrom;
                        dataRow["DateTimeTo"] = dateTimeDateTo;
                        dataRow["SpeedAve"] = 0;
                        dataRow["SpeedMax"] = 0;
                        dataRow["Distance"] = 0;
                        dataRow["Fuel"] = 0;
                        dataRow["Cost"] = 0;
                        dataRow["Geofence"] = "";
                        dataTable.Rows.Add(dataRow);
                    } else {
                        DataRow dataRow = dataTable.NewRow();
                        dataRow["No"] = index;
                        dataRow["VehicleRegistration"] = tracker.VehicleRegistration;
                        dataRow["DriverName"] = tracker.DriverName;
                        dataRow["DeviceImei"] = tracker.TrackerImei;

                        dataRow["DateTimeFrom"] = dateTimeDateFrom;
                        dataRow["DateTimeTo"] = dateTimeDateTo;

                        double speedAve = 0;
                        if (dataTableGeofence.Rows.Count > 0)
                            speedAve = Converter.dataTableColumnSumValue(dataTableGeofence, dataTableGeofence.Columns["SpeedAve"]) / dataTableGeofence.Rows.Count;
                        dataRow["SpeedAve"] = speedAve;

                        double speedMax = Converter.dataTableColumnMaxValue(dataTableGeofence, dataTableGeofence.Columns["SpeedMax"]);
                        dataRow["SpeedMax"] = speedMax;

                        dataRow["Distance"] = Converter.dataTableColumnSumValue(dataTableGeofence, dataTableGeofence.Columns["Distance"]);
                        dataRow["Fuel"] = Converter.dataTableColumnSumValue(dataTableGeofence, dataTableGeofence.Columns["Fuel"]);
                        dataRow["Cost"] = Converter.dataTableColumnSumValue(dataTableGeofence, dataTableGeofence.Columns["Cost"]);

                        dataRow["Geofence"] = Converter.dataTableColumnAppend(dataTableGeofence, dataTableGeofence.Columns["Geofence"]);
                        dataTable.Rows.Add(dataRow);
                    }
                }
                return dataTable;
            } catch (QueryException queryException) {
                throw queryException;
            } catch (MySqlException mySqlException) {
                throw new QueryException(1, mySqlException.Message);
            } catch (Exception exception) {
                throw new QueryException(1, exception.Message);
            }
        }
Ejemplo n.º 6
0
        public DataTable getTrackerExternalPowerCutData(Company company, User user, DateTime dateTimeDateFrom, DateTime dateTimeDateTo, int limit, int offset, Tracker tracker)
        {
            DataTable dataTableHistoricalData = this.getTrackerHistoricalData(company, user, dateTimeDateFrom, dateTimeDateTo, limit, offset, tracker);
            DataTable dataTableExternalPowerCutData = this.getReportTable(ReportType.EXTERNAL_POWER_CUT);
            dataTableExternalPowerCutData.TableName = tracker.DatabaseName;

            try {

                bool externalPowerStatusNow = false;
                bool externalPowerStatusBefore = false;

                EventCode eventCode = EventCode.TRACK_BY_TIME_INTERVAL;

                int index = 0;

                double speed = 0;
                double speedDivisor = 0;
                double speedAccumulator = 0;
                double speedMax = 0;
                double speedAverage = 0;

                double distanceCovered = 0;
                double distance = 0;

                String geofenceFrom = "";
                String geofenceTo = "";

                DateTime dateTimeRunningFrom = new DateTime();
                DateTime dateTimeRunningTo = new DateTime();

                double latitudeTo = 0;
                double latitudeFrom = 0;
                double longitudeTo = 0;
                double longitudeFrom = 0;

                TimeSpan timeSpan;

                for (int no = 0; no < dataTableHistoricalData.Rows.Count; no++) {
                    //Initialized
                    eventCode = (dataTableHistoricalData.Rows[no]["EventCode"] == System.DBNull.Value) ?
                       EventCode.TRACK_BY_TIME_INTERVAL : (EventCode)Enum.Parse(typeof(EventCode), (string)dataTableHistoricalData.Rows[no]["EventCode"], true);
                    speed = (int)dataTableHistoricalData.Rows[no]["Speed"];
                    distance = (double)dataTableHistoricalData.Rows[no]["Distance"];
                    double externalVolt = (double)dataTableHistoricalData.Rows[no]["ExternalVoltage"];

                    //Conditions
                    if (eventCode == EventCode.EXTERNAL_BATTERY_CUT || externalVolt < 12) {
                        externalPowerStatusNow = true;
                    }
                    if (eventCode == EventCode.EXTERNAL_BATTERY_ON || externalVolt >= 12) {
                        externalPowerStatusNow = false;
                    }

                    //OneTime
                    if ((int)dataTableHistoricalData.Rows[no]["No"] == 1) {
                        dateTimeRunningFrom = dateTimeDateFrom;//(DateTime)dataTableHistoricalData.Rows[no]["DateTime"];
                        latitudeFrom = (double)dataTableHistoricalData.Rows[no]["Latitude"];
                        longitudeFrom = (double)dataTableHistoricalData.Rows[no]["Longitude"];
                        geofenceFrom = (dataTableHistoricalData.Rows[no]["Geofence"] == System.DBNull.Value) ? "OUTSIDE" : (string)dataTableHistoricalData.Rows[no]["Geofence"];
                        externalPowerStatusBefore = externalPowerStatusNow;
                    }

                    if (externalPowerStatusBefore != externalPowerStatusNow) {

                        index++;
                        dateTimeRunningTo = (DateTime)dataTableHistoricalData.Rows[no]["DateTime"];
                        latitudeTo = (double)dataTableHistoricalData.Rows[no]["Latitude"];
                        longitudeTo = (double)dataTableHistoricalData.Rows[no]["Longitude"];
                        geofenceTo = (dataTableHistoricalData.Rows[no]["Geofence"] == System.DBNull.Value) ? "OUTSIDE" : (string)dataTableHistoricalData.Rows[no]["Geofence"];

                        timeSpan = dateTimeRunningTo - dateTimeRunningFrom;

                        speedAverage = speedAccumulator / speedDivisor;
                        speedAccumulator = 0;
                        speedDivisor = 0;

                        DataRow dataRowExternalPowerCutData = dataTableExternalPowerCutData.NewRow();
                        dataRowExternalPowerCutData["No"] = index;
                        dataRowExternalPowerCutData["Status"] = externalPowerStatusBefore;
                        dataRowExternalPowerCutData["DateTimeFrom"] = dateTimeRunningFrom;
                        dataRowExternalPowerCutData["DateTimeTo"] = dateTimeRunningTo;

                        dataRowExternalPowerCutData["LatitudeFrom"] = latitudeFrom;
                        dataRowExternalPowerCutData["LatitudeTo"] = latitudeTo;
                        dataRowExternalPowerCutData["LongitudeFrom"] = longitudeFrom;
                        dataRowExternalPowerCutData["LongitudeTo"] = longitudeTo;

                        dataRowExternalPowerCutData["Time"] = timeSpan;
                        dataRowExternalPowerCutData["Distance"] = distanceCovered;

                        dataRowExternalPowerCutData["Fuel"] = distanceCovered / Settings.Default.fuelLiterToKilometer;
                        dataRowExternalPowerCutData["Cost"] = ((double)dataRowExternalPowerCutData["Fuel"]) / Settings.Default.fuelLiterToCost;

                        if (geofenceTo.Equals(geofenceFrom)) {
                            dataRowExternalPowerCutData["Geofences"] = geofenceFrom;
                        } else {
                            dataRowExternalPowerCutData["Geofences"] = geofenceFrom + " to " + geofenceTo;
                        }
                        dataRowExternalPowerCutData["SpeedMax"] = speedMax;
                        dataRowExternalPowerCutData["SpeedAve"] = speedAverage;

                        speedMax = 0;
                        distanceCovered = 0;
                        dateTimeRunningFrom = dateTimeRunningTo;
                        longitudeFrom = longitudeTo;
                        latitudeFrom = latitudeTo;
                        dataTableExternalPowerCutData.Rows.Add(dataRowExternalPowerCutData);
                        externalPowerStatusBefore = externalPowerStatusNow;

                    }
                    if (no == dataTableHistoricalData.Rows.Count - 1) {
                        index++;
                        dateTimeRunningTo = dateTimeDateTo;// (DateTime)dataTableHistoricalData.Rows[no]["DateTime"];
                        latitudeTo = (double)dataTableHistoricalData.Rows[no]["Latitude"];
                        longitudeTo = (double)dataTableHistoricalData.Rows[no]["Longitude"];
                        geofenceTo = (dataTableHistoricalData.Rows[no]["Geofence"] == System.DBNull.Value) ? "OUTSIDE" : (string)dataTableHistoricalData.Rows[no]["Geofence"];

                        timeSpan = dateTimeRunningTo - dateTimeRunningFrom;

                        speedAverage = speedAccumulator / 1;
                        speedAccumulator = 0;
                        speedDivisor = 0;

                        DataRow dataRowExternalPowerCutData = dataTableExternalPowerCutData.NewRow();
                        dataRowExternalPowerCutData["No"] = index;
                        dataRowExternalPowerCutData["Status"] = externalPowerStatusNow;
                        dataRowExternalPowerCutData["DateTimeFrom"] = dateTimeRunningFrom;
                        dataRowExternalPowerCutData["DateTimeTo"] = dateTimeRunningTo;

                        dataRowExternalPowerCutData["LatitudeFrom"] = latitudeFrom;
                        dataRowExternalPowerCutData["LatitudeTo"] = latitudeTo;
                        dataRowExternalPowerCutData["LongitudeFrom"] = longitudeFrom;
                        dataRowExternalPowerCutData["LongitudeTo"] = longitudeTo;

                        dataRowExternalPowerCutData["Time"] = timeSpan;
                        dataRowExternalPowerCutData["Distance"] = distanceCovered;

                        dataRowExternalPowerCutData["Fuel"] = distanceCovered / Settings.Default.fuelLiterToKilometer;
                        dataRowExternalPowerCutData["Cost"] = (double)dataRowExternalPowerCutData["Fuel"] / Settings.Default.fuelLiterToCost;

                        if (geofenceTo.Equals(geofenceFrom)) {
                            dataRowExternalPowerCutData["Geofences"] = geofenceFrom;
                        } else {
                            dataRowExternalPowerCutData["Geofences"] = geofenceFrom + " to " + geofenceTo;
                        }
                        dataRowExternalPowerCutData["SpeedMax"] = speedMax;
                        dataRowExternalPowerCutData["SpeedAve"] = speedAverage;

                        speedMax = 0;
                        distanceCovered = 0;
                        dateTimeRunningFrom = dateTimeRunningTo;
                        longitudeFrom = longitudeTo;
                        latitudeFrom = latitudeTo;

                        dataTableExternalPowerCutData.Rows.Add(dataRowExternalPowerCutData);
                        externalPowerStatusBefore = externalPowerStatusNow;

                    }
                    //Accumulators : SpeedAve, SpeedMax, Distance
                    speedDivisor++;
                    speedAccumulator += speed;
                    if (speed > speedMax) {
                        speedMax = speed;
                    }
                    distanceCovered += distance;
                }
            } catch (QueryException queryException) {
                throw queryException;
            } catch (Exception exception) {
                throw new QueryException(1, exception.Message);
            } finally {

            }
            return dataTableExternalPowerCutData;
        }
Ejemplo n.º 7
0
        //public DataTable getTrackers(Company company, User user, int userAccountId) {
        //    DataTable dataTable = this.getReportTable(ReportType.TRACKERS);
        //    try {
        //        mysqlConnection.Open();
        //        string sql =
        //            "SELECT * " +
        //            "FROM dbt_tracking_master.trks " +
        //            "WHERE dbt_tracking_master.trks.tcmp = @sCompanyName " +
        //            "AND dbt_tracking_master.trks.tusers LIKE @sUserId;";
        //        MySqlCommand mySqlCommand = new MySqlCommand(sql, mysqlConnection);
        //        mySqlCommand.Parameters.AddWithValue("@sCompanyName", company.DatabaseName.Substring(4));
        //        mySqlCommand.Parameters.AddWithValue("@sUserId", "%" + userAccountId.ToString() + "%");
        //        MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();
        //        if (!mySqlDataReader.HasRows) {
        //            throw new QueryException(1, "Tracker's Collection is empty.");
        //        } else {
        //            Tracker tracker = new Tracker();
        //            string dateTime;
        //            while (mySqlDataReader.Read()) {
        //                tracker.collections = (string)mySqlDataReader["tcollections"];
        //                tracker.companyDatabaseName = (string)mySqlDataReader["tcmp"];
        //                tracker.databaseHost = int.Parse((string)mySqlDataReader["tdbhost"]);
        //                tracker.DatabaseName = (string)mySqlDataReader["tdbs"];
        //                dateTime = (string)mySqlDataReader["tcreatedate"];
        //                tracker.dateCreated = Converter.subStandardDateTimeToDateTime(dateTime);
        //                dateTime = String.Empty;
        //                dateTime = (string)mySqlDataReader["ttrackerexpiry"];
        //                tracker.dateExpired = Converter.subStandardDateTimeToDateTime(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 = (string)mySqlDataReader["tusers"];
        //                tracker.SpeedLimit = int.Parse((string)mySqlDataReader["tSpeedLimit"]);
        //                tracker.vehicleModel = (string)mySqlDataReader["tvehiclemodel"];
        //                tracker.vehicleRegistration = (string)mySqlDataReader["tvehiclereg"];
        //                DataRow dataRow = dataTable.NewRow();
        //                dataRow["id"] = tracker.id;
        //                dataRow["vehicleRegistration"] = tracker.vehicleRegistration;
        //                dataRow["vehicleModel"] = tracker.vehicleModel;
        //                dataRow["ownerName"] = tracker.ownerName;
        //                dataRow["driverName"] = tracker.driverName;
        //                dataRow["simImei"] = tracker.simImei;
        //                dataRow["simNumber"] = tracker.simNumber;
        //                dataRow["mobileDataProvider"] = tracker.mobileDataProvider;
        //                dataRow["deviceImei"] = tracker.trackerImei;
        //                dataRow["devicePassword"] = tracker.devicePassword;
        //                dataRow["deviceType"] = tracker.deviceType;
        //                dataRow["emails"] = tracker.emails;
        //                dataRow["users"] = tracker.users;
        //                dataRow["mileageInitial"] = tracker.mileageInitial;
        //                dataRow["mileageLimit"] = tracker.mileageLimit;
        //                dataRow["SpeedLimit"] = tracker.SpeedLimit;
        //                dataRow["idlingTime"] = tracker.idlingTime;
        //                dataRow["inputs"] = tracker.inputs;
        //                dataRow["imageNumber"] = tracker.imageNumber;
        //                dataRow["note"] = tracker.note;
        //                dataRow["collections"] = tracker.collections;
        //                dataRow["companyDatabaseName"] = tracker.companyDatabaseName;
        //                dataRow["databaseHost"] = tracker.databaseHost;
        //                dataRow["DatabaseName"] = tracker.DatabaseName;
        //                dataRow["httpHost"] = tracker.httpHost;
        //                dataRow["dateCreated"] = tracker.dateCreated;
        //                dataRow["dateExpired"] = tracker.dateExpired;
        //                dataTable.Rows.Add(dataRow);
        //            }
        //            return dataTable;
        //        }
        //    } 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 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();
            }
        }
Ejemplo n.º 8
0
        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();
            }
        }
Ejemplo n.º 9
0
        public void fillUsers(Company company, User user)
        {
            List<User> users = new List<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.Add(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;
        }
Ejemplo n.º 10
0
        public DataTable getTrackersHistorical(Company company, User user, DateTime dateTimeDateFrom, DateTime dateTimeDateTo, int limit, int offset, List<Tracker> trackerList)
        {
            DataTable dataTable = this.getReportTable(ReportType.TRACKERS_HISTORICAL);

            try {
                int index = 0;
                foreach (Tracker tracker in trackerList) {
                    index++;

                    DataTable dataTableHistorical = (this.getTrackerHistoricalData(company, user, dateTimeDateFrom, dateTimeDateTo, limit, offset, tracker));

                    if (dataTableHistorical.Rows.Count <= 0) {
                        DataRow dataRow = dataTable.NewRow();
                        dataRow["VehicleRegistration"] = tracker.VehicleRegistration;
                        dataRow["DriverName"] = tracker.DriverName;
                        dataRow["VehicleModel"] = tracker.VehicleModel;
                        dataRow["OwnerName"] = tracker.OwnerName;
                        dataRow["SimNumber"] = tracker.SimNumber;
                        dataRow["SimImei"] = tracker.SimImei;
                        dataRow["TrackerImei"] = tracker.TrackerImei;

                        dataTable.Rows.Add(dataRow);

                        DataRow dataRowEmpty = dataTable.NewRow();
                        dataTable.Rows.Add(dataRowEmpty);

                    } else {
                        foreach (DataRow dataRowItem in dataTableHistorical.Rows) {

                            DataRow dataRow = dataTable.NewRow();
                            dataRow["VehicleRegistration"] = tracker.VehicleRegistration;
                            dataRow["DriverName"] = tracker.DriverName;
                            dataRow["VehicleModel"] = tracker.VehicleModel;
                            dataRow["OwnerName"] = tracker.OwnerName;
                            dataRow["SimNumber"] = tracker.SimNumber;
                            dataRow["SimImei"] = tracker.SimImei;
                            dataRow["TrackerImei"] = tracker.TrackerImei;

                            dataRow["No"] = dataRowItem["No"];
                            dataRow["DateTime"] = dataRowItem["DateTime"];
                            dataRow["Latitude"] = dataRowItem["Latitude"];
                            dataRow["Longitude"] = dataRowItem["Longitude"];
                            dataRow["Speed"] = dataRowItem["Speed"];
                            dataRow["Mileage"] = dataRowItem["Mileage"];
                            dataRow["Distance"] = dataRowItem["Distance"];
                            dataRow["Fuel"] = dataRowItem["Fuel"];
                            dataRow["Cost"] = dataRowItem["Cost"];
                            dataRow["Altitude"] = dataRowItem["Altitude"];
                            dataRow["Degrees"] = dataRowItem["Degrees"];
                            dataRow["Direction"] = dataRowItem["Direction"];
                            dataRow["No"] = dataRowItem["No"];
                            dataRow["GpsSatellites"] = dataRowItem["GpsSatellites"];
                            dataRow["Latitude"] = dataRowItem["Latitude"];
                            dataRow["GsmSignal"] = dataRowItem["GsmSignal"];
                            dataRow["EventCode"] = dataRowItem["EventCode"];
                            dataRow["Geofence"] = dataRowItem["Geofence"];
                            dataRow["ACC"] = dataRowItem["ACC"];
                            dataRow["SOS"] = dataRowItem["SOS"];
                            dataRow["OverSpeed"] = dataRowItem["OverSpeed"];
                            dataRow["Battery"] = dataRowItem["Battery"];
                            dataRow["BatteryVoltage"] = dataRowItem["BatteryVoltage"];
                            dataRow["ExternalVoltage"] = dataRowItem["ExternalVoltage"];

                            dataTable.Rows.Add(dataRow);
                        }
                        DataRow dataRowEmpty = dataTable.NewRow();
                        dataTable.Rows.Add(dataRowEmpty);
                    }
                }
                return dataTable;
            } catch (QueryException queryException) {
                throw queryException;
            } catch (MySqlException mySqlException) {
                throw new QueryException(1, mySqlException.Message);
            } catch (Exception exception) {
                throw new QueryException(1, exception.Message);
            }
        }
Ejemplo n.º 11
0
        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);
                String coded = Cryptography.md5(user.Password);
                mySqlCommand.Parameters.AddWithValue("@sPassword", coded);

                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();
            }
        }
Ejemplo n.º 12
0
        public DataTable getTrackers(Company company)
        {
            DataTable dataTable = this.getReportTable(ReportType.TRACKERS);
            this.fillTrackers(company);
            List<Tracker> trackers = company.Trackers;

            foreach (Tracker tracker in trackers) {
                DataRow dataRow = dataTable.NewRow();
                dataRow["id"] = tracker.Id;
                dataRow["vehicleRegistration"] = tracker.VehicleRegistration;
                dataRow["vehicleModel"] = tracker.VehicleModel;
                dataRow["ownerName"] = tracker.OwnerName;
                dataRow["driverName"] = tracker.DriverName;

                dataRow["simImei"] = tracker.SimImei;
                dataRow["simNumber"] = tracker.SimNumber;
                dataRow["mobileDataProvider"] = tracker.MobileDataProvider;

                dataRow["deviceImei"] = tracker.TrackerImei;
                dataRow["devicePassword"] = tracker.DevicePassword;
                dataRow["deviceType"] = tracker.DeviceType;

                dataRow["emails"] = tracker.Emails;
                dataRow["users"] = tracker.Users;

                dataRow["mileageInitial"] = tracker.MileageInitial;
                dataRow["mileageLimit"] = tracker.MileageLimit;
                dataRow["SpeedLimit"] = tracker.SpeedLimit;

                dataRow["idlingTime"] = tracker.IdlingTime;
                dataRow["inputs"] = tracker.Inputs;
                dataRow["imageNumber"] = tracker.ImageNumber;
                dataRow["note"] = tracker.Note;

                dataRow["collections"] = tracker.Collections;
                dataRow["companyDatabaseName"] = tracker.CompanyDatabaseName;
                dataRow["databaseHost"] = tracker.DatabaseHost;
                dataRow["DatabaseName"] = tracker.DatabaseName;
                dataRow["httpHost"] = tracker.HttpHost;
                dataRow["dateCreated"] = tracker.DateTimeCreated;
                dataRow["dateExpired"] = tracker.DateTimeExpired;

                dataTable.Rows.Add(dataRow);
            }

            return dataTable;
        }
Ejemplo n.º 13
0
 public DialogLogin(Company company, User user)
 {
     InitializeComponent();
     this.company = company;
     this.user = user;
 }
Ejemplo n.º 14
0
        public DataTable getTrackerOverSpeedData(Company company, User user, DateTime dateTimeDateFrom, DateTime dateTimeDateTo, int limit, int offset, Tracker tracker)
        {
            DataTable dataTableHistoricalData = this.getTrackerHistoricalData(company, user, dateTimeDateFrom, dateTimeDateTo, limit, offset, tracker);
            DataTable dataTableOverSpeedData = this.getReportTable(ReportType.OVERSPEED);
            dataTableOverSpeedData.TableName = tracker.DatabaseName;

            try {

                bool overSpeedStatusNow = false;
                int index = 0;
                String geofence = "";

                foreach (DataRow dataRowNow in dataTableHistoricalData.Rows) {

                    overSpeedStatusNow = (bool)dataRowNow["OverSpeed"];

                    if (overSpeedStatusNow) {

                        index++;

                        geofence = ((dataRowNow["Geofence"] == System.DBNull.Value) ? "" : (string)dataRowNow["Geofence"]);

                        DataRow dataRowOverspeedData = dataTableOverSpeedData.NewRow();
                        dataRowOverspeedData["No"] = index;
                        dataRowOverspeedData["Status"] = overSpeedStatusNow;
                        dataRowOverspeedData["DateTime"] = (DateTime)dataRowNow["DateTime"];
                        dataRowOverspeedData["Latitude"] = (double)dataRowNow["Latitude"];
                        dataRowOverspeedData["Longitude"] = (double)dataRowNow["Longitude"];
                        dataRowOverspeedData["Speed"] = (int)dataRowNow["Speed"];
                        dataRowOverspeedData["Mileage"] = (double)dataRowNow["Mileage"];
                        dataRowOverspeedData["Geofence"] = geofence;
                        dataTableOverSpeedData.Rows.Add(dataRowOverspeedData);
                    }
                }
            } catch (QueryException queryException) {
                throw queryException;
            } catch (Exception exception) {
                throw new QueryException(1, exception.Message);
            } finally {

            }
            return dataTableOverSpeedData;
        }
Ejemplo n.º 15
0
        public void fillPois(Company company)
        {
            foreach (User user in company.Users) {
                List<Poi> pois = new List<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");
                            //poi.location = new Coordinate(double.Parse(mySqlDataReader.GetString("poi_lat")),
                            //                            double.Parse(mySqlDataReader.GetString("poi_lon")));
                            //pois.Add(poi);
                            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(
                                        double.Parse(poiLatitude),
                                        double.Parse(potLongitude)
                                        );
                            }

                            pois.Add(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();
                }
            }
        }
Ejemplo n.º 16
0
        public DataTable getTrackerHistoricalData(Company company, User user, DateTime dateTimeDateFrom, DateTime dateTimeDateTo, int limit, int offset, Tracker tracker)
        {
            DataTable dataTable = this.getReportTable(ReportType.HISTORICAL);
            dataTable.TableName = tracker.DatabaseName;

            try {
                mysqlConnection.Open();

                double timestampFrom = Ats.Helper.Converter.dateTimeToUnixTimestamp(dateTimeDateFrom);
                double timestampTo = Ats.Helper.Converter.dateTimeToUnixTimestamp(dateTimeDateTo);

                string sql = "";
                if (timestampFrom > timestampTo) {
                    sql =
                       "SELECT " +

                       "trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_id, " +
                       "trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_time, " +
                       "trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_lat, " +
                       "trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_lng, " +
                       "trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_ori, " +
                       "trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_speed, " +
                       "trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_mileage, " +
                       "trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_data " +

                       "FROM trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + " " +
                       "WHERE trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_time >= " + timestampTo.ToString() + " " +
                       "AND trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_time <= " + timestampFrom.ToString() + " " +
                       "ORDER BY trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_time ASC LIMIT " + limit.ToString() + " OFFSET " + offset.ToString() + ";";
                } else {
                    sql =
                        "SELECT " +

                        "trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_id, " +
                        "trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_time, " +
                        "trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_lat, " +
                        "trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_lng, " +
                        "trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_ori, " +
                        "trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_speed, " +
                        "trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_mileage, " +
                        "trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_data " +

                        "FROM trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + " " +
                        "WHERE trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_time <= " + timestampTo.ToString() + " " +
                        "AND trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_time >= " + timestampFrom.ToString() + " " +
                        "ORDER BY trk_" + tracker.DatabaseName + ".gps_" + tracker.DatabaseName + ".gm_time ASC LIMIT " + limit.ToString() + " OFFSET " + offset.ToString() + ";";
                }
                MySqlCommand mySqlCommand = new MySqlCommand(sql, mysqlConnection);

                MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();

                if (!mySqlDataReader.HasRows) {
                    //throw new QueryException(1, "Tracker data is empty.");
                    return dataTable;
                } else {
                    int index = offset;

                    DataRow dataRow;
                    DataRow dataRowBefore = dataTable.NewRow();

                    while (mySqlDataReader.Read()) {
                        index++;

                        dataRow = dataTable.NewRow();
                        dataRow["No"] = index;
                        dataRow["DateTime"] = Converter.unixTimeStampToDateTime(double.Parse((string)mySqlDataReader["gm_time"]));

                        double latitude = double.Parse((string)mySqlDataReader["gm_lat"]);
                        double longitude = double.Parse((string)mySqlDataReader["gm_lng"]);

                        dataRow["Latitude"] = latitude;
                        dataRow["Longitude"] = longitude;
                        dataRow["Speed"] = int.Parse((string)mySqlDataReader["gm_speed"]);
                        dataRow["Mileage"] = double.Parse((string)mySqlDataReader["gm_mileage"]);

                        if ((double)dataRow["Mileage"] < 0) {
                            dataRow["Mileage"] = ((double)dataRow["Mileage"]) * -1;
                        }

                        if (dataRowBefore["Mileage"] != System.DBNull.Value) {
                            double mileAgeObtained = (double)dataRow["Mileage"] - (double)dataRowBefore["Mileage"];
                            dataRow["Distance"] = mileAgeObtained;
                            dataRow["Fuel"] = mileAgeObtained / Settings.Default.fuelLiterToKilometer;
                            dataRow["Cost"] = ((double)dataRow["Fuel"]) * Settings.Default.fuelLiterToCost;
                        } else {
                            dataRow["Distance"] = 0.00f;
                            dataRow["Fuel"] = 0.00f;
                            dataRow["Cost"] = 0.00f;
                        }
                        dataRow["Degrees"] = int.Parse((string)mySqlDataReader["gm_ori"]);
                        dataRow["Direction"] = Converter.degreesToCardinalDetailed(double.Parse((string)mySqlDataReader["gm_ori"]));

                        //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(',');
                        dataRow["EventCode"] = Enum.GetName(typeof(EventCode), (EventCode)int.Parse(data[1]));
                        dataRow["GpsSatellites"] = int.Parse(data[2]);
                        dataRow["GsmSignal"] = int.Parse(data[3]);
                        dataRow["Altitude"] = int.Parse(data[6]);

                        dataRow["ACC"] = (int.Parse(data[18]) == 1) ? true : false;
                        dataRow["SOS"] = (int.Parse(data[17]) == 1) ? true : false;
                        dataRow["OverSpeed"] = ((int)dataRow["Speed"] > tracker.SpeedLimit) ? true : false;
                        //Geofence
                        Coordinate Coordinate = new Coordinate(latitude, longitude);
                        //MapTools mapTools = new MapTools();

                        if (company.Geofences != null) {
                            foreach (Geofence geofence in company.Geofences) {
                                if (Geofence.isPointInPolygon(geofence, Coordinate)) {
                                    if (String.IsNullOrEmpty(geofence.Name)) {
                                        dataRow["Geofence"] = "";
                                    } else {
                                        dataRow["Geofence"] = geofence.Name;
                                    }
                                }
                            }
                        }

                        double batteryStrength = (double)int.Parse(data[28], System.Globalization.NumberStyles.AllowHexSpecifier);
                        batteryStrength = ((batteryStrength - 2114f) * (100f / 492f));//*100.0;

                        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;

                        double externalVoltage = (double)int.Parse(data[29], System.Globalization.NumberStyles.AllowHexSpecifier);
                        externalVoltage = (externalVoltage * 3 * 16) / 1024;

                        dataRow["Battery"] = batteryStrength;
                        dataRow["BatteryVoltage"] = batteryVoltage;
                        dataRow["ExternalVoltage"] = externalVoltage;

                        dataTable.Rows.Add(dataRow);
                        dataRowBefore = dataRow;
                    }
                    mySqlDataReader.Close();
                    return dataTable;
                }
            } catch (QueryException queryException) {
                throw queryException;
            } catch (MySqlException mySqlException) {
                throw mySqlException;
            } catch (Exception exception) {
                throw exception;
            } finally {
                mysqlConnection.Close();
            }
        }
Ejemplo n.º 17
0
        public DataTable getTrackerGeofence(Company company, User user, DateTime dateTimeDateFrom, DateTime dateTimeDateTo, int limit, int offset, Tracker tracker)
        {
            DataTable dataTableHistoricalData = this.getTrackerHistoricalData(company, user, dateTimeDateFrom, dateTimeDateTo, limit, offset, tracker);
            DataTable dataTableGeofenceData = this.getReportTable(ReportType.GEOFENCE);
            dataTableGeofenceData.TableName = tracker.DatabaseName;

            try {

                string geofenceStatusNow = "";
                string geofenceStatusBefore = "";

                bool acc = false;

                int index = 0;

                double speed = 0;
                double speedDivisor = 0;
                double speedAccumulator = 0;
                double speedMax = 0;
                double speedAverage = 0;

                double distance = 0;
                double distanceCovered = 0;

                DateTime dateTimeGeofenceFrom = new DateTime();
                DateTime dateTimeGeofenceTo = new DateTime();

                double latitudeTo = 0;
                double latitudeFrom = 0;
                double longitudeTo = 0;
                double longitudeFrom = 0;

                TimeSpan timeSpan;

                for (int no = 0; no < dataTableHistoricalData.Rows.Count; no++) {

                    acc = (bool)dataTableHistoricalData.Rows[no]["ACC"];
                    speed = (int)dataTableHistoricalData.Rows[no]["Speed"];
                    distance = (double)dataTableHistoricalData.Rows[no]["Distance"];

                    if (!(dataTableHistoricalData.Rows[no]["Geofence"] == System.DBNull.Value)) {
                        geofenceStatusNow = (string)dataTableHistoricalData.Rows[no]["Geofence"];
                    } else {
                        geofenceStatusNow = "-";
                    }

                    //OneTime
                    if ((int)dataTableHistoricalData.Rows[no]["No"] == 1) {
                        dateTimeGeofenceFrom = dateTimeDateFrom;//(DateTime)dataTableHistoricalData.Rows[no]["DateTime"];
                        latitudeFrom = (double)dataTableHistoricalData.Rows[no]["Latitude"];
                        longitudeFrom = (double)dataTableHistoricalData.Rows[no]["Longitude"];
                        geofenceStatusBefore = geofenceStatusNow;
                    }

                    if (geofenceStatusBefore != geofenceStatusNow) {
                        index++;

                        dateTimeGeofenceTo = (DateTime)dataTableHistoricalData.Rows[no]["DateTime"];
                        latitudeTo = (double)dataTableHistoricalData.Rows[no]["Latitude"];
                        longitudeTo = (double)dataTableHistoricalData.Rows[no]["Longitude"];

                        timeSpan = dateTimeGeofenceTo - dateTimeGeofenceFrom;

                        speedAverage = speedAccumulator / speedDivisor;
                        speedAccumulator = 0;
                        speedDivisor = 0;

                        DataRow dataRowGeofenceData = dataTableGeofenceData.NewRow();
                        dataRowGeofenceData["No"] = index;

                        dataRowGeofenceData["DateTimeFrom"] = dateTimeGeofenceFrom;
                        dataRowGeofenceData["DateTimeTo"] = dateTimeGeofenceTo;

                        dataRowGeofenceData["LatitudeFrom"] = latitudeFrom;
                        dataRowGeofenceData["LatitudeTo"] = latitudeTo;
                        dataRowGeofenceData["LongitudeFrom"] = longitudeFrom;
                        dataRowGeofenceData["LongitudeTo"] = longitudeTo;

                        dataRowGeofenceData["Time"] = timeSpan;
                        dataRowGeofenceData["Distance"] = distanceCovered;

                        dataRowGeofenceData["Fuel"] = distanceCovered / Settings.Default.fuelLiterToKilometer;
                        dataRowGeofenceData["Cost"] = ((double)dataRowGeofenceData["Fuel"]) / Settings.Default.fuelLiterToCost;

                        dataRowGeofenceData["Geofence"] = geofenceStatusBefore;
                        dataRowGeofenceData["SpeedMax"] = speedMax;
                        dataRowGeofenceData["SpeedAve"] = speedAverage;

                        dataRowGeofenceData["Status"] = (geofenceStatusBefore == "-" ? false : true);

                        speedMax = 0;
                        distanceCovered = 0;
                        dateTimeGeofenceFrom = dateTimeGeofenceTo;
                        geofenceStatusBefore = geofenceStatusNow;
                        longitudeFrom = longitudeTo;
                        latitudeFrom = latitudeTo;
                        dataTableGeofenceData.Rows.Add(dataRowGeofenceData);
                    }

                    if (no == dataTableHistoricalData.Rows.Count - 1) {
                        index++;

                        dateTimeGeofenceTo = dateTimeDateTo;//(DateTime)dataTableHistoricalData.Rows[no]["DateTime"];
                        latitudeTo = (double)dataTableHistoricalData.Rows[no]["Latitude"];
                        longitudeTo = (double)dataTableHistoricalData.Rows[no]["Longitude"];

                        timeSpan = dateTimeGeofenceTo - dateTimeGeofenceFrom;

                        speedAverage = speedAccumulator / 1;
                        speedAccumulator = 0;
                        speedDivisor = 0;

                        DataRow dataRowGeofenceData = dataTableGeofenceData.NewRow();
                        dataRowGeofenceData["No"] = index;

                        dataRowGeofenceData["DateTimeFrom"] = dateTimeGeofenceFrom;
                        dataRowGeofenceData["DateTimeTo"] = dateTimeGeofenceTo;

                        dataRowGeofenceData["LatitudeFrom"] = latitudeFrom;
                        dataRowGeofenceData["LatitudeTo"] = latitudeTo;
                        dataRowGeofenceData["LongitudeFrom"] = longitudeFrom;
                        dataRowGeofenceData["LongitudeTo"] = longitudeTo;

                        dataRowGeofenceData["Time"] = timeSpan;
                        dataRowGeofenceData["Distance"] = distanceCovered;

                        dataRowGeofenceData["Fuel"] = distanceCovered / Settings.Default.fuelLiterToKilometer;
                        dataRowGeofenceData["Cost"] = (double)dataRowGeofenceData["Fuel"] / Settings.Default.fuelLiterToCost;

                        dataRowGeofenceData["Geofence"] = geofenceStatusNow;
                        dataRowGeofenceData["SpeedMax"] = speedMax;
                        dataRowGeofenceData["SpeedAve"] = speedAverage;
                        dataRowGeofenceData["Status"] = (geofenceStatusBefore == "-" ? false : true);

                        speedMax = 0;
                        distanceCovered = 0;
                        dateTimeGeofenceFrom = dateTimeGeofenceTo;
                        geofenceStatusBefore = geofenceStatusNow;
                        longitudeFrom = longitudeTo;
                        latitudeFrom = latitudeTo;
                        dataTableGeofenceData.Rows.Add(dataRowGeofenceData);
                    }

                    //Accumulators : SpeedAve, SpeedMax, Distance
                    speedDivisor++;
                    speedAccumulator += speed;
                    if (speed > speedMax) {
                        speedMax = speed;
                    }
                    distanceCovered += distance;

                }

                return dataTableGeofenceData;

            } catch (QueryException queryException) {
                throw queryException;
            } catch (Exception exception) {
                throw new QueryException(1, exception.Message);
            }
        }
Ejemplo n.º 18
0
        public void fillGeofences(Company company)
        {
            List<Geofence> geofences = new List<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.Add(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();
            }
        }