public void threadFunctionQuery(object uncast)
{
#region -instances
DataTable dataTableDetails = new DataTable();
int speedLimit;
Hashtable hashTable = (Hashtable)uncast;
Query query = new Query(database);
Tracker tracker = (Tracker)hashTable["tracker"];
if (tracker != null)
speedLimit = tracker.SpeedLimit;
TabControl tabControl = (TabControl)hashTable["tabControl"];
DateTime dateTimeFrom = (DateTime)hashTable["dateTimeFrom"];
DateTime dateTimeTo = (DateTime)hashTable["dateTimeTo"];
List<Tracker> trackerList = (List<Tracker>)hashTable["trackerList"];
int limit = (int)hashTable["limit"];
int offset = (int)hashTable["offset"];
int userId = (int)hashTable["comboBoxAccountId"];
int dataCount = 0;
string comboBoxReportTypeText = (string)hashTable["comboBoxReportTypeItem"];
ReportType reportType = (ReportType)Enum.Parse(typeof(ReportType), comboBoxReportTypeText);
bool tabPageExist = false;
string tabPageName = "";
string reportItemName = (string)hashTable["reportItemName"];
string reportTypeName = Enum.GetName(typeof(ReportType), reportType);
bool pagingStatus = false;
#endregion
#region -loop
//If timeout we will query again up to 3 times
for (int count = 0; count < 3; count++) {
try {
#region checkTabPage
tabPageName = reportTypeName + " : " + reportItemName;
this.Invoke(new MethodInvoker(delegate {
foreach (TabPage tabPageItem in tabControl.TabPages) {
if (tabPageItem.Text == tabPageName) {
tabControl.SelectedTab = tabPageItem;
tabPageExist = true;
break;
}
}
}));
if (tabPageExist == true) {
break;
}
#endregion
#region querySelection
switch (reportType) {
case ReportType.HISTORICAL:
dataTableDetails = query.getTrackerHistoricalData(this.company, this.user, dateTimeFrom, dateTimeTo, limit, offset, tracker);
dataCount = query.getTrackerHistoricalDataCount(this.user, dateTimeFrom, dateTimeTo, tracker);
//dataTableDetails.writeToCsvFile(Directory.GetCurrentDirectory());
pagingStatus = true;
break;
case ReportType.IDLING:
dataTableDetails = query.getTrackerIdlingData(this.company, this.user, dateTimeFrom, dateTimeTo, 1000000, 0, tracker);
dataCount = dataTableDetails.Rows.Count;
break;
case ReportType.RUNNING:
dataTableDetails = query.getTrackerRunningData(this.company, this.user, dateTimeFrom, dateTimeTo, 1000000, 0, tracker);
dataCount = dataTableDetails.Rows.Count;
break;
case ReportType.GEOFENCE:
dataTableDetails = query.getTrackerGeofence(this.company, this.user, dateTimeFrom, dateTimeTo, 1000000, 0, tracker);
dataCount = dataTableDetails.Rows.Count;
break;
case ReportType.TRACKERS:
dataTableDetails = query.getTrackers(this.company);
dataCount = dataTableDetails.Rows.Count;
break;
case ReportType.ACC:
dataTableDetails = query.getTrackerAccData(this.company, this.user, dateTimeFrom, dateTimeTo, 1000000, 0, tracker);
dataCount = dataTableDetails.Rows.Count;
break;
case ReportType.EXTERNAL_POWER_CUT:
dataTableDetails = query.getTrackerExternalPowerCutData(this.company, this.user, dateTimeFrom, dateTimeTo, 1000000, 0, tracker);
dataCount = dataTableDetails.Rows.Count;
break;
case ReportType.OVERSPEED:
dataTableDetails = query.getTrackerOverSpeedData(this.company, this.user, dateTimeFrom, dateTimeTo, 1000000, 0, tracker);
dataCount = dataTableDetails.Rows.Count;
break;
case ReportType.TRACKERS_GEOFENCE:
dataTableDetails = query.getTrackersGeofence(this.company, this.user, dateTimeFrom, dateTimeTo, 1000000, 0, trackerList);
dataCount = dataTableDetails.Rows.Count;
break;
case ReportType.TRACKERS_HISTORICAL:
dataTableDetails = query.getTrackersHistorical(this.company, this.user, dateTimeFrom, dateTimeTo, 500, 0, trackerList);
dataCount = dataTableDetails.Rows.Count;
break;
case ReportType.ALL_COMPANIES:
dataTableDetails = query.getAllCompanies();
dataCount = dataTableDetails.Rows.Count;
break;
case ReportType.ALL_TRACKERS:
dataTableDetails = query.getAllTrackers();
dataCount = dataTableDetails.Rows.Count;
break;
}
#endregion
#region dataGridViewInformation
DataGridView dataGridView = new DataGridView();
dataGridView.Tag = dataTableDetails;
dataGridView.VirtualMode = true;
dataGridView.BackgroundColor = Color.White;
dataGridView.Name = reportTypeName;
dataGridView.RowHeadersVisible = false;
dataGridView.AllowUserToAddRows = false;
dataGridView.AllowUserToDeleteRows = false;
dataGridView.ReadOnly = true;
//DataView dataView = new DataView();
//dataView.Table = (DataTable)dataGridView.Tag;
//dataView.RowFilter = ("No > 0 AND No <= 100");
dataGridView.DataSource = dataTableDetails;
dataGridView.Dock = DockStyle.Fill;
dataGridView.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
dataGridView.DataBindingComplete += dataGridView_DataBindingComplete;
dataGridView.CellDoubleClick += dataGridView_CellDoubleClick;
dataGridView.MouseHover += dataGridView_MouseHover;
ContextMenuStrip contextMenuStripDataGridView = new ContextMenuStrip();
contextMenuStripDataGridView.Name = "dataGridView";
contextMenuStripDataGridView.Opening += contextMenuStripDataGridView_Opening;
dataGridView.ContextMenuStrip = contextMenuStripDataGridView;
#endregion
#region tableLayoutPanelRight
ListViewItem[] listViewItemsDetail = new ListViewItem[11];
for (int index = 0; index < listViewItemsDetail.Count(); index++) {
listViewItemsDetail[index] = new ListViewItem();
}
listViewItemsDetail[0].Text = "Company Name";
listViewItemsDetail[0].SubItems.Add(company.DisplayName);
listViewItemsDetail[1].Text = "User Name";
listViewItemsDetail[1].SubItems.Add(user.Username);
if (tracker != null) {
listViewItemsDetail[2].Text = "Vehicle Reg";
listViewItemsDetail[2].SubItems.Add(tracker.VehicleRegistration);
listViewItemsDetail[3].Text = "Vehicle Model";
listViewItemsDetail[3].SubItems.Add(tracker.VehicleModel);
listViewItemsDetail[4].Text = "Owner Name";
listViewItemsDetail[4].SubItems.Add(tracker.OwnerName);
listViewItemsDetail[5].Text = "Driver Name";
listViewItemsDetail[5].SubItems.Add(tracker.DriverName);
listViewItemsDetail[6].Text = "Device Imei";
listViewItemsDetail[6].SubItems.Add(tracker.TrackerImei);
listViewItemsDetail[7].Text = "Sim Number";
listViewItemsDetail[7].SubItems.Add(tracker.SimNumber);
listViewItemsDetail[8].Text = "Vehicle Created";
listViewItemsDetail[8].SubItems.Add(tracker.DateTimeCreated.ToString("yyyy/MM/dd HH:mm:ss"));
listViewItemsDetail[9].Text = "Vehicle Expiry";
listViewItemsDetail[9].SubItems.Add(tracker.VehicleRegistrationExpiry.ToString("yyyy/MM/dd HH:mm:ss"));
listViewItemsDetail[10].Text = "Device Expiry";
listViewItemsDetail[10].SubItems.Add(tracker.DateTimeExpired.ToString("yyyy/MM/dd HH:mm:ss"));
}
ListView listViewDetails = new ListView();
listViewDetails.Dock = DockStyle.Fill;
listViewDetails.View = View.Details;
listViewDetails.Scrollable = false;
listViewDetails.FullRowSelect = true;
ColumnHeader columnHeaderLabelDetail = listViewDetails.Columns.Add("Details");
columnHeaderLabelDetail.Width = 90;
ColumnHeader columnHeaderValueDetail = listViewDetails.Columns.Add("Value");
columnHeaderValueDetail.Width = 150;
listViewDetails.Items.AddRange(listViewItemsDetail);
ListViewItem[] listViewItemsSummary = new ListViewItem[10];
for (int index = 0; index < listViewItemsSummary.Count(); index++) {
listViewItemsSummary[index] = new ListViewItem();
}
listViewItemsSummary[0].Text = "DateTime From";
listViewItemsSummary[0].SubItems.Add(dateTimeFrom.ToString("yyyy/MM/dd HH:mm:ss"));
listViewItemsSummary[1].Text = "DateTime To";
listViewItemsSummary[1].SubItems.Add(dateTimeTo.ToString("yyyy/MM/dd HH:mm:ss"));
if (reportType == ReportType.RUNNING ||
reportType == ReportType.IDLING ||
reportType == ReportType.ACC ||
reportType == ReportType.GEOFENCE ||
reportType == ReportType.EXTERNAL_POWER_CUT) {
listViewItemsSummary[2].Text = "Total Distance";
listViewItemsSummary[2].SubItems.Add(Converter.dataTableColumnSumValueIfTrue(dataTableDetails, "Distance").ToString() + " Km");
listViewItemsSummary[3].Text = "Total Fuel";
listViewItemsSummary[3].SubItems.Add(Converter.dataTableColumnSumValueIfTrue(dataTableDetails, "Fuel").ToString() + " L");
listViewItemsSummary[4].Text = "Total Cost";
listViewItemsSummary[4].SubItems.Add(Converter.dataTableColumnSumValueIfTrue(dataTableDetails, "Cost").ToString() + " Qr");
}
if (reportType == ReportType.TRACKERS_GEOFENCE) {
listViewItemsSummary[2].Text = "Total Distance";
listViewItemsSummary[2].SubItems.Add(Converter.dataTableColumnSumValue(dataTableDetails, "Distance").ToString() + " Km");
listViewItemsSummary[3].Text = "Total Fuel";
listViewItemsSummary[3].SubItems.Add(Converter.dataTableColumnSumValue(dataTableDetails, "Fuel").ToString() + " L");
listViewItemsSummary[4].Text = "Total Cost";
listViewItemsSummary[4].SubItems.Add(Converter.dataTableColumnSumValue(dataTableDetails, "Cost").ToString() + " Qr");
}
if (reportType == ReportType.RUNNING ||
reportType == ReportType.IDLING ||
reportType == ReportType.ACC ||
reportType == ReportType.GEOFENCE ||
reportType == ReportType.EXTERNAL_POWER_CUT) {
if (reportType == ReportType.RUNNING) {
listViewItemsSummary[5].Text = "Total Running Time";
} else if (reportType == ReportType.IDLING) {
listViewItemsSummary[5].Text = "Total Idling Time";
} else if (reportType == ReportType.GEOFENCE) {
listViewItemsSummary[5].Text = "Total Geofence Active Time";
} else if (reportType == ReportType.ACC) {
listViewItemsSummary[5].Text = "Total ACC Active Time";
} else if (reportType == ReportType.EXTERNAL_POWER_CUT) {
listViewItemsSummary[5].Text = "Total ExternalPower Cut Time";
}
listViewItemsSummary[5].SubItems.Add(Converter.dataTableColumnSumTimeSpanIfTrue(dataTableDetails, "Time").ToString(@"dd\.hh\:mm\:ss"));
}
ListView listViewSummary = new ListView();
listViewSummary.Dock = DockStyle.Fill;
listViewSummary.View = View.Details;
listViewSummary.Scrollable = false;
listViewSummary.FullRowSelect = true;
ColumnHeader columnHeaderLabelSummary = listViewSummary.Columns.Add("Summary");
columnHeaderLabelSummary.Width = 90;
ColumnHeader columnHeaderValueSummary = listViewSummary.Columns.Add("Value");
columnHeaderValueSummary.Width = 150;
listViewSummary.Items.AddRange(listViewItemsSummary);
TableLayoutPanel tableLayoutPanelRight = new TableLayoutPanel();
tableLayoutPanelRight.Dock = DockStyle.Fill;
tableLayoutPanelRight.RowStyles.Clear();
tableLayoutPanelRight.ColumnStyles.Clear();
tableLayoutPanelRight.Margin = new Padding(0, 0, 0, 0);
tableLayoutPanelRight.ColumnStyles.Add(new ColumnStyle(SizeType.Percent, 100));
tableLayoutPanelRight.RowStyles.Add(new RowStyle(SizeType.Absolute, 220));
tableLayoutPanelRight.RowStyles.Add(new RowStyle(SizeType.Absolute, 220));
tableLayoutPanelRight.RowStyles.Add(new RowStyle(SizeType.Percent, 100));
tableLayoutPanelRight.Controls.Add(listViewDetails, 0, 0);
tableLayoutPanelRight.Controls.Add(listViewSummary, 0, 1);
#endregion
#region tableLayoutPanelBottom
Button buttonNext = new Button();
buttonNext.Dock = DockStyle.Fill;
//buttonNext.Text = "Next";
buttonNext.Click += buttonNext_Click;
buttonNext.Visible = pagingStatus;
buttonNext.BackgroundImage = Resources.icon_stepr_001;
buttonNext.BackgroundImageLayout = ImageLayout.Zoom;
Button buttonPrev = new Button();
buttonPrev.Dock = DockStyle.Fill;
//buttonPrev.Text = "Prev";
buttonPrev.Click += buttonPrev_Click;
buttonPrev.Visible = pagingStatus;
buttonPrev.BackgroundImage = Resources.icon_stepl_001;
buttonPrev.BackgroundImageLayout = ImageLayout.Zoom;
Button buttonFirst = new Button();
buttonFirst.Dock = DockStyle.Fill;
//buttonFirst.Text = "First";
buttonFirst.Click += buttonFirst_Click;
buttonFirst.Visible = pagingStatus;
buttonFirst.BackgroundImage = Resources.icon_stepll_001;
buttonFirst.BackgroundImageLayout = ImageLayout.Zoom;
Button buttonLast = new Button();
buttonLast.Dock = DockStyle.Fill;
//buttonLast.Text = "Last";
buttonLast.Click += buttonLast_Click;
buttonLast.Visible = pagingStatus;
buttonLast.BackgroundImage = Resources.icon_steprl_001;
buttonLast.BackgroundImageLayout = ImageLayout.Zoom;
Label LabelPageNumber = new Label();
LabelPageNumber.Dock = DockStyle.Fill;
LabelPageNumber.Text = "1";
LabelPageNumber.TextAlign = ContentAlignment.MiddleRight;
LabelPageNumber.Visible = pagingStatus;
Label LabelTotalPageNumber = new Label();
LabelTotalPageNumber.Dock = DockStyle.Fill;
int totalPageNumber = (dataCount / (int)hashTable["limit"]) + 1;
LabelTotalPageNumber.Text = "/ " + totalPageNumber.ToString() + " Page(s)";
LabelTotalPageNumber.TextAlign = ContentAlignment.MiddleLeft;
LabelTotalPageNumber.Visible = pagingStatus;
Label LabelDataCount = new Label();
LabelDataCount.Dock = DockStyle.Fill;
LabelDataCount.Text = dataCount.ToString();
LabelDataCount.TextAlign = ContentAlignment.MiddleRight;
Label labelDetails = new Label();
labelDetails.Dock = DockStyle.Fill;
labelDetails.TextAlign = ContentAlignment.MiddleLeft;
labelDetails.Text = "Record(s) found";
TableLayoutPanel tableLayoutPanelBottom = new TableLayoutPanel();
tableLayoutPanelBottom.Dock = DockStyle.Fill;
tableLayoutPanelBottom.RowStyles.Clear();
tableLayoutPanelBottom.ColumnStyles.Clear();
tableLayoutPanelBottom.ColumnStyles.Add(new ColumnStyle(SizeType.Absolute, 50));
tableLayoutPanelBottom.ColumnStyles.Add(new ColumnStyle(SizeType.Absolute, 50));
tableLayoutPanelBottom.ColumnStyles.Add(new ColumnStyle(SizeType.Absolute, 50));
tableLayoutPanelBottom.ColumnStyles.Add(new ColumnStyle(SizeType.Absolute, 50));
tableLayoutPanelBottom.ColumnStyles.Add(new ColumnStyle(SizeType.Absolute, 50));
tableLayoutPanelBottom.ColumnStyles.Add(new ColumnStyle(SizeType.Absolute, 100));
tableLayoutPanelBottom.ColumnStyles.Add(new ColumnStyle(SizeType.Percent, 100));
tableLayoutPanelBottom.ColumnStyles.Add(new ColumnStyle(SizeType.Absolute, 100));
tableLayoutPanelBottom.RowStyles.Add(new RowStyle(SizeType.Percent, 100));
tableLayoutPanelBottom.Controls.Add(buttonFirst, 0, 0);
tableLayoutPanelBottom.Controls.Add(buttonPrev, 1, 0);
tableLayoutPanelBottom.Controls.Add(buttonNext, 2, 0);
tableLayoutPanelBottom.Controls.Add(buttonLast, 3, 0);
tableLayoutPanelBottom.Controls.Add(LabelPageNumber, 4, 0);
tableLayoutPanelBottom.Controls.Add(LabelTotalPageNumber, 5, 0);
tableLayoutPanelBottom.Controls.Add(LabelDataCount, 6, 0);
tableLayoutPanelBottom.Controls.Add(labelDetails, 7, 0);
#endregion
#region tableLayoutInformationParent
TableLayoutPanel tableLayoutInformationParent = new TableLayoutPanel();
tableLayoutInformationParent.RowStyles.Clear();
tableLayoutInformationParent.ColumnStyles.Clear();
tableLayoutInformationParent.ColumnStyles.Add(new ColumnStyle(SizeType.Percent, 100));
tableLayoutInformationParent.ColumnStyles.Add(new ColumnStyle(SizeType.Absolute, 250));
tableLayoutInformationParent.RowStyles.Add(new RowStyle(SizeType.Percent, 100));
tableLayoutInformationParent.RowStyles.Add(new RowStyle(SizeType.Absolute, 40));
tableLayoutInformationParent.Controls.Add(dataGridView, 0, 0);
tableLayoutInformationParent.Controls.Add(tableLayoutPanelBottom, 0, 1);
tableLayoutInformationParent.Controls.Add(tableLayoutPanelRight, 1, 0);
//tableLayoutInformationParent.Controls.Add(labelDetails, 1, 1);
tableLayoutInformationParent.Dock = DockStyle.Fill;
#endregion
#region tabPageAndTabControls
TabPage tabPage = new TabPage(tabPageName);
tabPage.Name = reportItemName;
tabPage.Tag = hashTable;
tabPage.Controls.Add(tableLayoutInformationParent);
hashTable["dataGridViewInformation"] = dataGridView;
if (tabControl != null) {
this.Invoke(new MethodInvoker(delegate {
tabControl.TabPages.Add(tabPage);
tabControl.SelectTab(tabPage);
tabControl.Click += tabControl_Click;
tabControl.Refresh();
Application.DoEvents();
}));
}
#endregion
break;
} catch (QueryException queryException) {
Log log = new Log(LogFileType.TXT, LogType.EXCEPTION);
string logData = "";
if (tracker != null)
logData = DateTime.Now.ToString() + "\t\t queryException \t\t" + tracker.VehicleRegistration + " : " + queryException.Message;
else
logData = DateTime.Now.ToString() + "\t\t queryException \t\t" + queryException.Message;
log.write(logData);
break;
} catch (MySqlException mySqlException) {
Log log = new Log(LogFileType.TXT, LogType.EXCEPTION);
string logData = "";
if (tracker != null)
logData = DateTime.Now.ToString() + "\t\t mySqlException \t\t" + tracker.VehicleRegistration + " : " + mySqlException.Message;
else
logData = DateTime.Now.ToString() + "\t\t mySqlException \t\t" + mySqlException.Message;
log.write(logData);
if (mySqlException.ErrorCode == -2147467259) {
//requery
if (count == 2) {
break;
}
} else {
break;
}
} catch (Exception exception) {
Log log = new Log(LogFileType.TXT, LogType.EXCEPTION);
string logData = "";
if (tracker != null) {
logData = DateTime.Now.ToString() + "\t\t exception \t\t" + tracker.VehicleRegistration + " : " + exception.Message;
} else {
logData = DateTime.Now.ToString() + "\t\t exception \t\t" + exception.Message;
}
log.write(logData);
break;
}
}
#endregion
#region -updateProgressBarStatus
lock (this.progressBarStatus) {
workerThreadFinished++;
double result = workerThreadFinished / workerThreadCount;
double progressValue = result * (double)progressBarStatus.Maximum;
updateProgressBarStatus((int)progressValue);
}
#endregion
}
