public DataTable ConnectDB()
{
DataTable myDataTable = new DataTable();
try
{
// Open OleDb Connection
OleDbConnection myConnection = new OleDbConnection
{
ConnectionString = DBpath()
};
myConnection.Open();
// Execute Queries
OleDbCommand cmd = myConnection.CreateCommand();
cmd.CommandText = $"{this.Query}";
// close conn after complete
OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
// Load the result into a DataTable
myDataTable.Load(reader);
}
catch (Exception ex)
{
MessageBox.Show(translationText.Get("noConnectionBase") + ex.Message);
myDataTable = null;
Application.Exit();
}
return(myDataTable);
}
public int OrdinalOptional(string comboBoxInOutDoor)
{
if (comboBoxInOutDoor.Trim() == translationText.Get("exit"))
{
return(0);
}
if (comboBoxInOutDoor.Trim() == translationText.Get("in"))
{
return(1);
}
return(-1);
}
public void ExportToExcel(List <PrintList> print)
{
// Load Excel application
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
// Create empty workbook
excel.Workbooks.Add();
// Create Worksheet from active sheet
Microsoft.Office.Interop.Excel._Worksheet workSheet = excel.ActiveSheet;
// I created Application and Worksheet objects before try/catch,
// so that i can close them in finnaly block.
// It's IMPORTANT to release these COM objects!!
try
{
// ------------------------------------------------
// Creation of header cells
// ------------------------------------------------
workSheet.Cells[1, "A"] = translationText.Get("excelA");
workSheet.Cells[1, "B"] = translationText.Get("excelB");
workSheet.Cells[1, "C"] = translationText.Get("excelC");
workSheet.Cells[1, "D"] = translationText.Get("excelD");
workSheet.Cells[1, "E"] = translationText.Get("excelE");
// ------------------------------------------------
// Populate sheet with some real data from "cars" list
// ------------------------------------------------
int row = 2; // start row (in row 1 are header cells)
foreach (PrintList prn in print)
{
workSheet.Cells[row, "A"] = prn.EmployeeName;
workSheet.Cells[row, "B"] = prn.DateTime;
workSheet.Cells[row, "C"] = prn.CtrlID;
workSheet.Cells[row, "D"] = prn.Ordinal;
workSheet.Cells[row, "E"] = prn.DepartmentName;
row++;
}
// Apply some predefined styles for data to look nicely :)
workSheet.Range["A1"].AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic1);
// Define filename
string fileName = string.Format(@"{0}\ExcelData.xlsx", Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory));
// Save this data as a file
workSheet.SaveAs(fileName);
// Display SUCCESS message
Console.WriteLine(string.Format("The file '{0}' is saved successfully!", fileName));
//MessageBox.Show(string.Format("The file '{0}' is saved successfully!", fileName));
}
catch (Exception exception)
{
Console.WriteLine("Exception", "There was a PROBLEM saving Excel file!\n" + exception.Message);
//MessageBox.Show("Exception", "There was a PROBLEM saving Excel file!\n" + exception.Message, MessageBoxButtons.OK, essageBoxIcon.Error);
}
finally
{
// Quit Excel application
excel.Quit();
// Release COM objects (very important!)
if (excel != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
}
if (workSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
}
// Empty variables
excel = null;
workSheet = null;
// Force garbage collector cleaning
GC.Collect();
}
}
private async void BtnSubmit_Click(object sender, EventArgs e)
{
try
{
this.listViewEventRecord.Items.Clear();
AssController assController = new AssController();
Dictionary <int, string> doorCtrlID = assController.DoorCtrlID();
Dictionary <int, string> doorOrdinal = assController.DoorOrdinal();
SearchDataTime searchDataTime =
new SearchDataTime(this.startSearchDataTimePicker.Text, this.endSearchDataTimePicker.Text);
string startSearchData = searchDataTime.StartData();
string endSearchData = searchDataTime.EndData();
string startSearchTime = searchDataTime.StartTime();
string endSearchTime = searchDataTime.EndTime();
int ctrlIDOptional = 3;
int ordinalOptional = assController.OrdinalOptional(this.comboBoxInOutDoor.Text);
int searchDepartment = assController.SearchDepartment(this.comboBoxDepartmen.Text);
//First Name Second Name Last Name
string searchUserName = assController.SearchUserName(this.comboBoxEmploee.Text);
//checket error
ErrorMessage errorMessage = new ErrorMessage();
errorMessage.ShowErrorMessage(startSearchData, endSearchData, startSearchTime,
endSearchTime, ordinalOptional, searchDepartment, searchUserName);
lblStatus.Text = string.Format($"{translationText.Get("process")}...10%");
lblStatus.Update();
progressBar.Value = 10;
progressBar.Update();
EventRecord eventRecord = assController.EventRecordRead(startSearchData, endSearchData,
startSearchTime, endSearchTime, ctrlIDOptional, ordinalOptional);
//progress bar
Progress <ProgressReport> progress = new Progress <ProgressReport>();
progress.ProgressChanged += (o, report) => {
if (report.PercentComplete > 15)
{
lblStatus.Text = string.Format($"{translationText.Get("process")}...{report.PercentComplete}%");
lblStatus.Update();
progressBar.Value = report.PercentComplete;
progressBar.Update();
}
};
await ProcessData(eventRecord, progress);
lblStatus.Text = translationText.Get("done");
Dictionary <int, Employee> cardEmployeeDepart = new Dictionary <int, Employee>();
cardEmployeeDepart = assController.CardEmployeeDepart(searchUserName, searchDepartment);
print = new List <PrintList>();
int index = 0;
foreach (var cardLow in eventRecord.CardLow)
{
if (!cardEmployeeDepart.ContainsKey(cardLow))
{
index++;
continue;
}
PrintList prnName = new PrintList
{
EmployeeName = cardEmployeeDepart[cardLow].EmployeeName,
DateTime = eventRecord.AriseTime[index],
CtrlID = doorCtrlID[eventRecord.CtrlID[index]],
Ordinal = doorOrdinal[eventRecord.Ordinal[index]],
DepartmentName = cardEmployeeDepart[cardLow].DepartmentName
};
index++;
print.Add(prnName);
}
if (print.Count == 0)
{
throw new Exception(translationText.Get("emptyOfficers"));
}
ViewFormEvent(print);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}