Esempio n. 1
0
        /// <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);
        }
Esempio n. 2
0
        /// <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);
        }
Esempio n. 3
0
        /// <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;
        }
Esempio n. 4
0
        /// <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);
        }
Esempio n. 5
0
        /// <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;
        }
Esempio n. 6
0
        /// <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);
        }
Esempio n. 7
0
        /// <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);
        }
Esempio n. 8
0
        /// <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);
        }
Esempio n. 9
0
        /// <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;
        }