public EventRecord EventRecordRead(string startSearchData, string endSearchData,
string startSearchTime, string endSearchTime, int ctrlIDOptional, int ordinalOptional)
{
string query = $"SELECT `AriseTime`, `CardLow`, `CtrlID`, `Ordinal` " +
$"FROM `EventRecord` " +
$"WHERE `CtrlID`={ctrlIDOptional} " +
$"AND `Ordinal`={ordinalOptional} " +
$"AND AriseTime Between #{startSearchData} 00:00:00# And #{endSearchData} 23:59:59# " +
$"AND ((TimeValue(AriseTime) Between #{startSearchTime}# And #{endSearchTime}#)) " +
$"ORDER BY `AriseTime`";
ConnectMDB myDataTable = new ConnectMDB(query);
EventRecord eventRecord = new EventRecord
{
CardLow = myDataTable.ConnectDB()
.AsEnumerable()
.Select(r => r.Field <int>("CardLow"))
.ToList(),
AriseTime = myDataTable.ConnectDB()
.AsEnumerable()
.Select(r => r.Field <DateTime>("AriseTime"))
.ToList(),
CtrlID = myDataTable.ConnectDB()
.AsEnumerable()
.Select(r => r.Field <int>("CtrlID"))
.ToList(),
Ordinal = myDataTable.ConnectDB()
.AsEnumerable()
.Select(r => r.Field <byte>("Ordinal"))
.ToList()
};
return(eventRecord);
}
public Dictionary <int, Employee> CardEmployeeDepart(string searchUserName, int searchDepartment)
{
string query = "SELECT c.CardLow AS cardLow, d.DepartmentName AS dName, e.EmployeeName AS eName " +
"FROM ((`Employee` AS e " +
"INNER JOIN `Card` AS c ON e.EmployeeID = c.EmployeeID )" +
"INNER JOIN `Department` AS d ON e.DepartmentID = d.DepartmentID )";
if (searchDepartment > 0 && searchUserName.Length == 0)
{
query = "SELECT c.CardLow AS cardLow, d.DepartmentName AS dName, e.EmployeeName AS eName " +
"FROM ((`Employee` AS e " +
"INNER JOIN `Card` AS c ON e.EmployeeID = c.EmployeeID )" +
"INNER JOIN `Department` AS d ON e.DepartmentID = d.DepartmentID )" +
$"WHERE e.DepartmentID={searchDepartment}";
}
if (searchDepartment == 0 && searchUserName.Length > 0)
{
query = "SELECT c.CardLow AS cardLow, d.DepartmentName AS dName, e.EmployeeName AS eName " +
"FROM ((`Employee` AS e " +
"INNER JOIN `Card` AS c ON e.EmployeeID = c.EmployeeID )" +
"INNER JOIN `Department` AS d ON e.DepartmentID = d.DepartmentID )" +
$"WHERE e.EmployeeName='{searchUserName}'";
}
if (searchUserName.Length > 0 && searchDepartment > 0)
{
query = "SELECT c.CardLow AS cardLow, d.DepartmentName AS dName, e.EmployeeName AS eName " +
"FROM ((`Employee` AS e " +
"INNER JOIN `Card` AS c ON e.EmployeeID = c.EmployeeID )" +
"INNER JOIN `Department` AS d ON e.DepartmentID = d.DepartmentID )" +
$"WHERE e.DepartmentID={searchDepartment} " +
$"AND e.EmployeeName='{searchUserName}'";
}
ConnectMDB myDataTable = new ConnectMDB(query);
Dictionary <int, Employee> cardLowNameDepart = new Dictionary <int, Employee>();
foreach (var item in myDataTable.ConnectDB().AsEnumerable())
{
if (!cardLowNameDepart.ContainsKey(item.Field <int>("cardLow")))
{
cardLowNameDepart[item.Field <int>("cardLow")] = new Employee();
}
Employee employee = new Employee
{
EmployeeName = item.Field <string>("eName"),
DepartmentName = item.Field <string>("dName")
};
cardLowNameDepart[item.Field <int>("cardLow")] = employee;
}
return(cardLowNameDepart);
}
private void BtnLogin_Click(object sender, EventArgs e)
{
try
{
string passsword = textBoxPassword.Text.Trim();
string query = "SELECT `UserId`, `UserName` " +
"FROM `User` " +
"WHERE `UserName`='" + textBoxUsername.Text.Trim() + "' ";// +
//"AND `UserPassword`='" + password + "'";
ConnectMDB myDataTable = new ConnectMDB(query);
if (myDataTable.ConnectDB() == null)
{
//this.Close();
}
int userCount = myDataTable.ConnectDB().AsEnumerable().ToArray().Count();
if (userCount == 1)
{
User user = new User
{
UserId = myDataTable.ConnectDB().AsEnumerable()
.Select(i => i.Field <int>("UserID"))
.ToArray()[0],
UserName = myDataTable.ConnectDB().AsEnumerable()
.Select(i => i.Field <string>("UserName"))
.ToArray()[0]
};
//FormMain formMain = new FormMain();
FormViewEventRecordr formViewEventRecordr = new FormViewEventRecordr();
this.Hide();
//formMain.Show();
formViewEventRecordr.ShowDialog();
}
else
{
throw new Exception("Проверете потребителското име и паролата.");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
//throw;
}
}
public Dictionary <int, string> Department()
{
string query = "SELECT `DepartmentID`, `DepartmentName` " +
"FROM `Department` ORDER BY `DepartmentID`";
ConnectMDB myDataTable = new ConnectMDB(query);
Dictionary <int, string> department = myDataTable
.ConnectDB()
.AsEnumerable()
.ToDictionary(
r => r.Field <int>("DepartmentID"),
r => r.Field <string>("DepartmentName"));
return(department);
}
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
string query = "SELECT `UserId`, `UserName` " +
"FROM `User`";
ConnectMDB myDataTable = new ConnectMDB(query);
if (myDataTable.ConnectDB() != null)
{
//Application.Run(new FormProgress());
//Application.Run(new FormPleaseWait());
Application.Run(new FormLogin());
//Application.Run(new FormViewEventRecordr());
}
}