/// <summary>
/// Get user info when login
/// </summary>
/// <param name="username"></param>
/// <param name="pass"></param>
/// <returns></returns>
public m_user LoginUser(string username, string pass)
{
MySQLDao SQL = new MySQLDao();
string query = string.Empty;
query = "SELECT `m_user`.`user_id`,`m_user`.`user_name`,`m_user`.`password`,`m_user`.`email`,`m_user`.`phone`,";
query += "`m_user`.`reg_date`,`m_user`.`is_active` FROM `maindb`.`m_user` ";
query += "WHERE `m_user`.`user_name` ='" + username + "' ";
query += "AND `m_user`.`password` ='" + pass + "'";
SQL.OpenConnection();
IDataReader reader = SQL.Command(query).ExecuteReader();
reader.Read();
m_user outItem = new m_user
{
user_id = (int)reader["user_id"],
user_name = reader["user_name"].ToString(),
password = reader["password"].ToString(),
email = reader["email"].ToString(),
phone = reader["phone"].ToString(),
reg_date = (DateTime)reader["reg_date"],
is_active = reader.GetBoolean(reader.GetOrdinal("is_active")),
};
reader.Close();
SQL.CloseConnection();
query = string.Empty;
return(outItem);
}
/// <summary>
/// Update a lamp
/// </summary>
/// <param name="inItem">input lamp (lamp_cd: empty if skip, lamp_state, wattage)</param>
/// <param name="changeState">true: change state of lamp</param>
/// <param name="changeValue">true: change wattage of lamp</param>
/// <returns></returns>
public int UpdateLamp(m_lamp inItem, bool changeState, bool changeValue)
{
MySQLDao SQL = new MySQLDao();
string query = string.Empty;
SQL.OpenConnection();
query = "UPDATE `maindb`.`m_lamp` SET `reg_date` ='" + DateTime.Now + "' ";
if (!string.IsNullOrEmpty(inItem.lamp_cd))
{
query += ",`lamp_cd` ='" + inItem.lamp_cd + "' ";
}
if (changeState)
{
query += ",`lamp_state`='" + inItem.lamp_state + "' ";
}
if (changeValue)
{
query += ",`wattage`='" + inItem.wattage + "' ";
}
query += "WHERE `lamp_id` ='" + inItem.lamp_id + "'";
int result = SQL.Command(query).ExecuteNonQuery();
SQL.CloseConnection();
query = string.Empty;
return(result);
}
/// <summary>
/// Update state of user when login or log out
/// </summary>
/// <param name="isActive">true: login, false: log out</param>
public void UpdateUserState(bool isActive)
{
MySQLDao SQL = new MySQLDao();
string query = string.Empty;
SQL.OpenConnection();
query = "UPDATE `maindb`.`m_user` SET `is_active` = '" + isActive + "' WHERE `user_id` ='" + user_id + "'";
int result = SQL.Command(query).ExecuteNonQuery();
SQL.CloseConnection();
query = string.Empty;
}
/// <summary>
/// Delete current user
/// </summary>
/// <returns></returns>
public int DeleteUser()
{
MySQLDao SQL = new MySQLDao();
string query = string.Empty;
SQL.OpenConnection();
query = "DELETE FROM `maindb`.`m_user` WHERE `user_id` ='" + user_id + "'";
int result = SQL.Command(query).ExecuteNonQuery();
SQL.CloseConnection();
query = string.Empty;
return(result);
}
/// <summary>
/// Get user info in database into current user list
/// </summary>
/// <param name="user">input user info need search (id: 0 if skip, name, email, phone)</param>
/// <param name="checkState">true: check state of user</param>
public void SearchUser(m_user user, bool checkState)
{
MySQLDao SQL = new MySQLDao();
string query = string.Empty;
listUser = new List <m_user>();
query = "SELECT `m_user`.`user_id`,`m_user`.`user_name`,`m_user`.`password`,`m_user`.`email`,";
query += @"`m_user`.`phone`,`m_user`.`reg_date`,`m_user`.`is_active` ";
query += "FROM `maindb`.`m_user` WHERE 1=1 ";
if (user.user_id > 0)
{
query += "AND `user_id` ='" + user.user_id + "' ";
}
if (!string.IsNullOrEmpty(user.user_name))
{
query += "AND `user_name` ='" + user.user_name + "' ";
}
if (!string.IsNullOrEmpty(user.email))
{
query += "AND `email` ='" + user.email + "' ";
}
if (!string.IsNullOrEmpty(user.phone))
{
query += "AND `phone` ='" + user.phone + "' ";
}
if (checkState)
{
query += "AND `is_active` ='" + user.is_active + "' ";
}
SQL.OpenConnection();
IDataReader reader = SQL.Command(query).ExecuteReader();
while (reader.Read())
{
m_user outItem = new m_user()
{
user_id = (int)reader["user_id"],
user_name = reader["user_name"].ToString(),
password = reader["password"].ToString(),
email = reader["email"].ToString(),
phone = reader["phone"].ToString(),
reg_date = (DateTime)reader["reg_date"],
is_active = reader.GetBoolean(reader.GetOrdinal("is_active")),
};
listUser.Add(outItem);
}
reader.Close();
SQL.CloseConnection();
query = string.Empty;
}
/// <summary>
/// Update an user
/// </summary>
/// <param name="inItem">input user (user id, user_name, password, email, phone, reg_date)</param>
/// <returns></returns>
public int UpdateUser(m_user inItem)
{
MySQLDao SQL = new MySQLDao();
string query = string.Empty;
SQL.OpenConnection();
query = "UPDATE `maindb`.`m_user` SET `user_name` = '" + inItem.user_name + "', `password`='" + inItem.password;
query += "',`email`='" + inItem.email + "',`phone`='" + inItem.phone + "',`reg_date` ='" + DateTime.Now + "' ";
query += "WHERE `user_id` ='" + user_id + "'";
int result = SQL.Command(query).ExecuteNonQuery();
SQL.CloseConnection();
query = string.Empty;
return(result);
}
/// <summary>
/// Add new user
/// </summary>
/// <param name="inItem">input user (username, pass, email, phone)</param>
/// <returns></returns>
public int AddUser(m_user inItem)
{
MySQLDao SQL = new MySQLDao();
string query = string.Empty;
SQL.OpenConnection();
query = "INSERT INTO `maindb`.`m_user`(`user_name`,`password`,`email`,`phone`)";
query += "VALUES('" + inItem.user_name + "','" + inItem.password + "','" + inItem.email + "','";
query += inItem.phone + "')";
int result = SQL.Command(query).ExecuteNonQuery();
SQL.CloseConnection();
query = string.Empty;
return(result);
}
/// <summary>
/// Add a lamp into database
/// </summary>
/// <param name="inItem">input lamp (lamp_cd, lamp_state, wattage, reg_user)</param>
/// <returns></returns>
public int AddLamp(m_lamp inItem)
{
MySQLDao SQL = new MySQLDao();
string query = string.Empty;
SQL.OpenConnection();
query = @"INSERT INTO `maindb`.`m_lamp`(`lamp_cd`,`lamp_state`,`wattage`,`reg_user`)";
query += "VALUES('" + inItem.lamp_cd + "','" + inItem.lamp_state + "','" + inItem.wattage + "','";
query += inItem.reg_user + "')";
int result = SQL.Command(query).ExecuteNonQuery();
SQL.CloseConnection();
query = string.Empty;
return(result);
}
/// <summary>
/// Get lamp info into current lamp list
/// </summary>
/// <param name="lamp">input lamp info need search</param>
/// <param name="checkState">true: check state of lamp</param>
public void SearchLamp(m_lamp lamp, bool checkState)
{
MySQLDao SQL = new MySQLDao();
string query = string.Empty;
listLamp = new List <m_lamp>();
query = "SELECT `m_lamp`.`lamp_id`,`m_lamp`.`lamp_cd`,`m_lamp`.`lamp_state`,`m_lamp`.`wattage` ";
query += "FROM `maindb`.`m_lamp` WHERE 1=1 ";
if (lamp.lamp_id > 0)
{
query += "AND `lamp_id` ='" + lamp.lamp_id + "' ";
}
if (!string.IsNullOrEmpty(lamp.lamp_cd))
{
query += "AND `lamp_cd` ='" + lamp.lamp_cd + "' ";
}
if (checkState)
{
query += "AND `lamp_state` ='" + lamp.lamp_state + "' ";
}
SQL.OpenConnection();
IDataReader reader = SQL.Command(query).ExecuteReader();
while (reader.Read())
{
m_lamp outItem = new m_lamp()
{
lamp_id = (int)reader["lamp_id"],
lamp_cd = reader["lamp_cd"].ToString(),
lamp_state = reader.GetBoolean(reader.GetOrdinal("lamp_state")),
wattage = (double)reader["wattage"],
reg_date = (DateTime)reader["reg_date"],
reg_user = reader["reg_user"].ToString(),
};
listLamp.Add(outItem);
}
reader.Close();
SQL.CloseConnection();
query = string.Empty;
}