/// <summary>
/// 查询部门名称为departmentName的所有员工信息
/// </summary>
/// <param name="departmendId"></param>
/// <returns></returns>
public static List<Employee> QueryEmployee(String departmentName)
{
MySqlConnection con = DBTools.GetMySqlConnection();
MySqlCommand cmd;
List<Employee> employees = new List<Employee>();
try
{
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = QUERY_DEPARTMENT_EMPLOYEE_NAME_STR;
cmd.Parameters.AddWithValue("@DepartmentName", departmentName);
MySqlDataReader sqlRead = cmd.ExecuteReader();
cmd.Dispose();
while (sqlRead.Read())
{
Employee employee = new Employee();
employee.Id = int.Parse(sqlRead["id"].ToString());
employee.Name = sqlRead["name"].ToString();
employee.Position = sqlRead["position"].ToString();
employee.CanSubmit = int.Parse(sqlRead["cansubmit"].ToString());
employee.CanSign = int.Parse(sqlRead["cansign"].ToString());
employee.IsAdmin = int.Parse(sqlRead["isadmin"].ToString());
Department depart = new Department();
depart.Id = int.Parse(sqlRead["departid"].ToString());
depart.Name = sqlRead["departname"].ToString();
employee.Department = depart;
User user = new User();
user.Username = sqlRead["username"].ToString();
user.Password = sqlRead["password"].ToString();
employee.User = user;
employees.Add(employee); // 将查询出来的员工信息插入链表中
}
con.Close();
con.Dispose();
}
catch (Exception)
{
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
return employees;
}
/// <summary>
/// 获取到名称为conTempName的模版的信息
/// </summary>
/// <param name="conTempName"></param>
/// <returns></returns>
public static ContractTemplate GetContractTemplate(String conTempName)
{
MySqlConnection con = DBTools.GetMySqlConnection();
MySqlCommand cmd;
ContractTemplate conTemp = null;
try
{
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = GET_CONTRACT_TEMPLATE_ID_STR;
cmd.Parameters.AddWithValue("@Name", conTempName); // 员工编号
MySqlDataReader sqlRead = cmd.ExecuteReader();
cmd.Dispose();
if (sqlRead.Read())
{
conTemp = new ContractTemplate();
conTemp.TempId = int.Parse(sqlRead["id"].ToString());
conTemp.Name = sqlRead["name"].ToString();
// 5个栏目信息
// conTemp.ColumnCount = 5;
List<String> columns = new List<String>();
/*
columns.Add(sqlRead["column1"].ToString());
columns.Add(sqlRead["column2"].ToString());
columns.Add(sqlRead["column3"].ToString());
columns.Add(sqlRead["column4"].ToString());
columns.Add(sqlRead["column5"].ToString());
*/
for (int cnt = 1; cnt <= 5; cnt++)
{
String strColumn = "column" + cnt.ToString();
columns.Add(sqlRead[strColumn].ToString());
}
conTemp.ColumnNames = columns;
// 8个签字人信息
// conTemp.SignCount = 8;
List<SignatureTemplate> signatures = new List<SignatureTemplate>();
for (int cnt = 1; cnt <= 8; cnt++)
{
String strSignInfo = "signinfo" + cnt.ToString();
String strSignId = "signid" + cnt.ToString();
String strSignName = "signname" + cnt.ToString();
String strDepartmentId = "department" + cnt.ToString();
String strDepartmentName = "department" + cnt.ToString();
String strCanView = "canview" + cnt.ToString();
String strCanDownload = "candownload" + cnt.ToString();
SignatureTemplate signDatas = new SignatureTemplate();
signDatas.SignInfo = sqlRead[strSignInfo].ToString();
Employee employee = new Employee();
employee.Id = int.Parse(sqlRead[strSignId].ToString());
employee.Name = sqlRead[strSignName].ToString();
Department department = new Department();
department.Id = int.Parse(sqlRead[strDepartmentId].ToString());
department.Name = sqlRead[strDepartmentName].ToString();
employee.Department = department;
signDatas.SignEmployee = employee;
signDatas.CanView = int.Parse(sqlRead[strCanView].ToString());
signDatas.CanDownload = int.Parse(sqlRead[strCanDownload].ToString());
signatures.Add(signDatas);
}
conTemp.SignDatas = signatures;
}
con.Close();
con.Dispose();
}
catch (Exception)
{
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
return conTemp;
}
public static Employee LoginEmployee(User user)
{
MySqlConnection con = DBTools.GetMySqlConnection();
MySqlCommand cmd;
Employee employee = new Employee();
try
{
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = LOGIN_EMPLOYEE_COM_STR;
cmd.Parameters.AddWithValue("@Username", user.Username); // 员工登录用户名
cmd.Parameters.AddWithValue("@Password", user.Password); // 员工登录密码
MySqlDataReader sqlRead = cmd.ExecuteReader();
cmd.Dispose();
/*while (sqlRead.Read( ))
{}*/
if (sqlRead.Read())
{
// 基本信息
employee.Id = int.Parse(sqlRead["id"].ToString());
employee.Name = sqlRead["name"].ToString();
employee.Position = sqlRead["position"].ToString();
employee.CanSubmit = int.Parse(sqlRead["cansubmit"].ToString());
employee.CanSign = int.Parse(sqlRead["cansign"].ToString());
employee.IsAdmin = int.Parse(sqlRead["isadmin"].ToString());
// 职位信息
Department depart = new Department();
depart.Id = int.Parse(sqlRead["departid"].ToString());
depart.Name = sqlRead["departname"].ToString();
employee.Department = depart;
// 用户登录信息
employee.User = user;
}
else
{
employee.Id = -1;
}
con.Close();
con.Dispose();
}
catch (Exception)
{
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
return employee;
}
public static bool ModifyEmployee(Employee employee)
{
MySqlConnection con = DBTools.GetMySqlConnection();
MySqlCommand cmd;
int count = -1;
try
{
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = MODIFY_EMPLOYEE_ID_STR;
cmd.Parameters.AddWithValue("@Id", employee.Id);
cmd.Parameters.AddWithValue("@Name", employee.Name);
cmd.Parameters.AddWithValue("@Position", employee.Position); // 员工姓名
cmd.Parameters.AddWithValue("@DepartmentId", employee.Department.Id);
cmd.Parameters.AddWithValue("@CanSubmit", employee.CanSubmit);
cmd.Parameters.AddWithValue("@CanSign", employee.CanSign);
cmd.Parameters.AddWithValue("@IsAdmin", employee.IsAdmin);
cmd.Parameters.AddWithValue("@Username", employee.User.Username);
cmd.Parameters.AddWithValue("@Password", employee.User.Password);
count = cmd.ExecuteNonQuery();
cmd.Dispose();
con.Close();
con.Dispose();
}
catch (Exception)
{
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
if (count == 1)
{
Console.WriteLine("修改员工信息" + employee.Id.ToString() + "成功");
return true;
}
else
{
Console.WriteLine("修改员工信息" + employee.Id.ToString() + "失败");
return false;
}
}
public static bool IsEmployeeExist(Employee employee)
{
MySqlConnection con = DBTools.GetMySqlConnection();
MySqlCommand cmd;
int count = -1;
try
{
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = IS_EMPLOYEE_EXIST_STR;
cmd.Parameters.AddWithValue("@Username", employee.User.Username);
MySqlDataReader sqlRead = cmd.ExecuteReader();
cmd.Dispose();
while (sqlRead.Read())
{
count = int.Parse(sqlRead["count"].ToString());
}
con.Close();
con.Dispose();
}
catch (Exception)
{
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
if (count == 1)
{
return true;
}
else
{
return false;
}
}
/// <summary>
/// 插入员工信息
/// </summary>
/// <param name="employee"></param>
/// <returns></returns>
public static bool InsertEmployee(Employee employee)
{
MySqlConnection con = DBTools.GetMySqlConnection();
MySqlCommand cmd;
int count = -1; // 受影响行数
try
{
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = INSERT_EMPLOYEE_STR;
cmd.Parameters.AddWithValue("@Name", employee.Name); // 员工姓名
cmd.Parameters.AddWithValue("@Position", employee.Position); // 员工职位
cmd.Parameters.AddWithValue("@Department", employee.Department.Id); // 员工部门编号
cmd.Parameters.AddWithValue("@Username", employee.User.Username); // 员工登录用户名
cmd.Parameters.AddWithValue("@Password", employee.User.Password); // 员工密码
cmd.Parameters.AddWithValue("@CanSubmit", employee.CanSubmit);
cmd.Parameters.AddWithValue("@CanSign", employee.CanSign);
cmd.Parameters.AddWithValue("@IsAdmin", employee.IsAdmin);
count = cmd.ExecuteNonQuery();
cmd.Dispose();
con.Close();
con.Dispose();
if (count == 1) // 插入成功后的受影响行数为1
{
Console.WriteLine("用户插入成功");
return true;
}
else
{
Console.WriteLine("用户插入失败");
return false;
}
}
catch (Exception)
{
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
public static Employee GetEmployee(String employeeUsername)
{
MySqlConnection con = DBTools.GetMySqlConnection();
MySqlCommand cmd;
Employee employee = null; // 待返回的员工信息
try
{
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = GET_EMPLOYEE_USERNAME_STR;
cmd.Parameters.AddWithValue("@Username", employeeUsername); // 员工编号
MySqlDataReader sqlRead = cmd.ExecuteReader();
cmd.Dispose();
if (sqlRead.Read())
{
employee = new Employee();
employee.Id = int.Parse(sqlRead["id"].ToString());
employee.Name = sqlRead["name"].ToString();
employee.Position = sqlRead["position"].ToString();
employee.CanSubmit = int.Parse(sqlRead["cansubmit"].ToString());
employee.CanSign = int.Parse(sqlRead["cansign"].ToString());
employee.IsAdmin = int.Parse(sqlRead["isadmin"].ToString());
Department depart = new Department();
depart.Id = int.Parse(sqlRead["departid"].ToString());
depart.Name = sqlRead["departname"].ToString();
employee.Department = depart;
User user = new User();
user.Username = sqlRead["username"].ToString();
user.Password = sqlRead["password"].ToString();
employee.User = user;
Console.WriteLine(employee);
}
con.Close();
con.Dispose();
}
catch (Exception)
{
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
return employee;
}
/// <summary>
/// 用户登录请求的事件的具体信息
/// </summary>
/// <param name="state"></param>
private void RaiseLoginRequest(AsyncSocketState state)
{
Console.WriteLine("接收到来自{0}的登录信息{1}", state.ClientIp, state.SocketMessage.Message); // 输出真正的信息
this.Log.Write(new LogMessage("接收到来自" + state.ClientIp + "的登录信息" + state.SocketMessage.Message, LogMessageType.Information));
//string LOGIN_RESPONSE;
ServerResponse response = new ServerResponse();
// json数据解包
User user = JsonConvert.DeserializeObject<User>(state.SocketMessage.Message);
Employee employee = new Employee();
employee = DALEmployee.LoginEmployee(user); // 如果用户名和密码验证成功
if(employee.Id != -1)
{
Console.WriteLine(user + "用户名密码均正确，可以登录");
this.Log.Write(new LogMessage(user + "用户名密码均正确，可以登录", LogMessageType.Success));
response = ServerResponse.LOGIN_SUCCESS; // 用户登录成功信号
}
else
{
Console.WriteLine(user + "用户名密码验证失败，无法正常登录");
this.Log.Write(new LogMessage(user + "用户名密码验证失败，无法正常登录", LogMessageType.Error));
response = ServerResponse.LOGIN_FAILED; // 用户登录成功信号
}
// 将响应信号发送至客户端
if (response.Equals(ServerResponse.LOGIN_SUCCESS))
{
AsyncSocketMessage socketMessage = new AsyncSocketMessage(response, employee);
//String json = JsonConvert.SerializeObject(employee);
this.Send(state.ClientSocket, Encoding.UTF8.GetBytes(socketMessage.Package)); // 将
//Console.WriteLine("发送给")
}
else
{
AsyncSocketMessage socketMessage = new AsyncSocketMessage(response);
//String json = JsonConvert.SerializeObject(employee);
this.Send(state.ClientSocket, Encoding.UTF8.GetBytes(socketMessage.Package));
}
}
// keyi
//public static HDJContract ViewHDJContract(String contractId)
//{
// DALHDJContract.GetHDJContactRefuse(contractId);
//}
public static HDJContract GetHDJContactRefuse(String contractId)
{
MySqlConnection con = DBTools.GetMySqlConnection();
MySqlCommand cmd;
HDJContract contract = new HDJContract();
try
{
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = GET_HDJCONTRACT_REFUSE_STR;
cmd.Parameters.AddWithValue("@Id", contractId);
MySqlDataReader sqlRead = cmd.ExecuteReader();
cmd.Dispose();
while (sqlRead.Read())
{
contract.Id = sqlRead["id"].ToString();
contract.Name = sqlRead["name"].ToString();
ContractTemplate conTemp = new ContractTemplate();
conTemp.TempId = int.Parse(sqlRead["contempid"].ToString());
conTemp.Name = sqlRead["contempname"].ToString();
// 5个栏目信息
// conTemp.ColumnCount = 5;
List<String> columnnames = new List<String>();
List<String> columndatas = new List<String>();
for (int cnt = 1; cnt <= 5; cnt++)
{
String strColumnname = "columnname" + cnt.ToString();
String strColumnData = "columndata" + cnt.ToString();
columnnames.Add(sqlRead[strColumnname].ToString());
columndatas.Add(sqlRead[strColumnData].ToString());
}
conTemp.ColumnNames = columnnames;
contract.ColumnDatas = columndatas;
// 8个签字人信息
// conTemp.SignCount = 8;
List<SignatureTemplate> signatures = new List<SignatureTemplate>();
List<int> signResults = new List<int>();
List<String > signRemarks = new List<String>();
for (int cnt = 1; cnt <= 8; cnt++)
{
String strSignInfo = "signinfo" + cnt.ToString();
String strSignId = "signid" + cnt.ToString();
String strSignName = "signname" + cnt.ToString();
String strDepartmentId = "departmentid" + cnt.ToString();
String strDepartmentName = "departmentname" + cnt.ToString();
String strSignLevel = "signlevel" + cnt.ToString();
String strCanView = "canview" + cnt.ToString();
String strCanDownload = "candownload" + cnt.ToString();
String strSignResult = "result" + cnt.ToString();
String strSignRemark = "remark" + cnt.ToString();
SignatureTemplate signDatas = new SignatureTemplate();
signDatas.SignInfo = sqlRead[strSignInfo].ToString();
signDatas.SignLevel = int.Parse(sqlRead[strSignLevel].ToString());
signDatas.CanView = int.Parse(sqlRead[strCanView].ToString());
signDatas.CanDownload = int.Parse(sqlRead[strCanDownload].ToString());
Employee employee = new Employee();
employee.Id = int.Parse(sqlRead[strSignId].ToString());
employee.Name = sqlRead[strSignName].ToString();
Department department = new Department();
department.Id = int.Parse(sqlRead[strDepartmentId].ToString());
department.Name = sqlRead[strDepartmentName].ToString();
employee.Department = department;
signDatas.SignEmployee = employee;
// 8个人的签字结果
signResults.Add(int.Parse(sqlRead[strSignResult].ToString()));
signRemarks.Add(sqlRead[strSignRemark].ToString());
signatures.Add(signDatas);
}
conTemp.SignDatas = signatures;
contract.ConTemp = conTemp;
contract.SignResults = signResults;
contract.SignRemarks = signRemarks;
}
con.Close();
con.Dispose();
}
catch (Exception)
{
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
return contract;
}