public SYS_USER RetriveOneEntityById(int id, string connectionString = null)
{
SYS_USER _USER;
string sql = $"SELECT * from sys_user where id='{id}'";
using (IDbConnection conn = DataBaseConfig.GetSqlConnection(DBType.mysql, connectionString))
{
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
object obj = cmd.ExecuteScalar();
_USER = (SYS_USER)obj;
}
return(_USER);
}
/// <summary>
/// 判断会议室是否停用
/// </summary>
/// <param name="roomid"></param>
/// <param name="connectionString"></param>
/// <returns></returns>
public bool IsStop(string roomid, string connectionString = null)
{
bool _user = false;
string sql = $"SELECT `status` from sys_roominfo where id='{roomid}'";
using (IDbConnection conn = DataBaseConfig.GetSqlConnection(DBType.mysql, connectionString))
{
var obj = conn.ExecuteScalar <int>(sql);
if (obj == 0)
{
_user = true;
}
}
return(_user);
}
public List <SYS_ROOMINFO> GetRoomInfo(string connectionString = null)
{
List <SYS_ROOMINFO> _user = null;
string sql = $"SELECT * from sys_roominfo where `status`=1";
using (IDbConnection conn = DataBaseConfig.GetSqlConnection(DBType.mysql, connectionString))
{
var obj = conn.Query <SYS_ROOMINFO>(sql).AsList();
if (obj != null && obj.Count >= 1)
{
return(obj);
}
}
return(_user);
}
public List <SYS_USER> getUser(string connectionString)
{
List <SYS_USER> _user = null;
string sql = $"SELECT * from sys_user where area!=0";
using (IDbConnection conn = DataBaseConfig.GetSqlConnection(DBType.mysql, connectionString))
{
var obj = conn.Query <SYS_USER>(sql).AsList();
if (obj != null && obj.Count >= 1)
{
return(obj);
}
}
return(_user);
}
public bool editUserPwd(string id, string pwd, string connectionString = null)
{
bool _user = false;
string sql = $@"update sys_user set `password`='{pwd}' where id='{id}'";
using (IDbConnection conn = DataBaseConfig.GetSqlConnection(DBType.mysql, connectionString))
{
var obj = conn.Execute(sql);
if (obj >= 1)
{
_user = true;
}
}
return(_user);
}
public List <SYS_BOOKINFO> getBook(string connectionString)
{
List <SYS_BOOKINFO> _user = null;
string sql = $"SELECT * from sys_bookinfo";
using (IDbConnection conn = DataBaseConfig.GetSqlConnection(DBType.mysql, connectionString))
{
var obj = conn.Query <SYS_BOOKINFO>(sql).AsList();
if (obj != null && obj.Count >= 1)
{
return(obj);
}
}
return(_user);
}
public SYS_USER AuthUser(string username, string password, string connstr)
{
SYS_USER _user = null;
string sql = $"SELECT * from sys_user where username='******' and `password`='{password}'";
using (IDbConnection conn = DataBaseConfig.GetSqlConnection(DBType.mysql, connstr))
{
var obj = conn.Query <SYS_USER>(sql).AsList();
if (obj != null && obj.Count >= 1)
{
_user = (SYS_USER)obj[0];
}
}
return(_user);
}
public bool delBook(int id, string connectionString)
{
bool _user = false;
string sql = $@"DELETE from sys_bookinfo where id='{id}'";
using (IDbConnection conn = DataBaseConfig.GetSqlConnection(DBType.mysql, connectionString))
{
var obj = conn.Execute(sql);
if (obj >= 1)
{
_user = true;
}
}
return(_user);
}
public List <SYS_PRIORITY> getPriority(string connstr)
{
List <SYS_PRIORITY> _user = null;
string sql = $"SELECT * from sys_priority";
using (IDbConnection conn = DataBaseConfig.GetSqlConnection(DBType.mysql, connstr))
{
var obj = conn.Query <SYS_PRIORITY>(sql).AsList();
if (obj != null && obj.Count >= 1)
{
return(obj);
}
}
return(_user);
}
public bool completeToDo(string id, string connstr)
{
bool _user = false;
string sql = $@"update sys_todolist set endtime='{DateTime.Now.ToString()}',progress=100 where id='{id}'";
using (IDbConnection conn = DataBaseConfig.GetSqlConnection(DBType.mysql, connstr))
{
var obj = conn.Execute(sql);
if (obj >= 1)
{
_user = true;
}
}
return(_user);
}
public bool editProgress(string id, int progress, string connstr)
{
bool _user = false;
var end = progress == 100?$",endtime='{DateTime.Now.ToString()}'": ",endtime=''";
string sql = $@"update sys_todolist set progress='{progress}' {end} where id='{id}'";
using (IDbConnection conn = DataBaseConfig.GetSqlConnection(DBType.mysql, connstr))
{
var obj = conn.Execute(sql);
if (obj >= 1)
{
_user = true;
}
}
return(_user);
}
public bool addRoom(SYS_ROOMINFO room, string connectionString)
{
bool _user = false;
string sql = $@"INSERT INTO `bcr`.`sys_roominfo` ( `name`, `area`, `status`) VALUES
(
'{room.NAME}',{room.AREA},{room.STATUS});" ;
using (IDbConnection conn = DataBaseConfig.GetSqlConnection(DBType.mysql, connectionString))
{
var obj = conn.Execute(sql);
if (obj >= 1)
{
_user = true;
}
}
return(_user);
}
public bool AuthTime(string roomid, DateTime starttime, DateTime endtime, string connectionString = null)
{
bool _user = false;
string sql = $@"SELECT count(1) num from sys_bookinfo where roomid='{roomid}' and
(('{starttime}' >= starttime and '{starttime}' < endtime)
or('{endtime}'> starttime and '{endtime}' <= endtime)
or ('{starttime}'<starttime and '{endtime}'>endtime))";
using (IDbConnection conn = DataBaseConfig.GetSqlConnection(DBType.mysql, connectionString))
{
var obj = conn.ExecuteScalar <int>(sql);
if (obj == 0)
{
_user = true;
}
}
return(_user);
}
public bool addArea(SYS_AREA area, string connectionString)
{
bool _user = false;
string sql = $@"INSERT INTO `sys_area` ( `name`, `abbreviation`)
VALUES
(
'{area.NAME}',
'{area.ABBREVIATION}'
);" ;
using (IDbConnection conn = DataBaseConfig.GetSqlConnection(DBType.mysql, connectionString))
{
var obj = conn.Execute(sql);
if (obj >= 1)
{
_user = true;
}
}
return(_user);
}
{/// <summary>
/// 获取会议室预定情况
/// </summary>
/// <param name="area"></param>
/// <returns></returns>
public List <AreaBook> AreaBook(int areaid, string connectionString)
{
List <AreaBook> books = new List <AreaBook>();
string sql = $@"SELECT b.starttime,b.endtime,b.`status`,b.meetname,u.username,r.`name` roomname
from sys_roominfo r
LEFT JOIN sys_bookinfo b on b.roomid=r.id
LEFT JOIN sys_user u on b.uid=u.id
LEFT JOIN sys_area a on r.area=a.id
where a.id={areaid}
ORDER BY starttime";
using (IDbConnection conn = DataBaseConfig.GetSqlConnection(DBType.mysql, connectionString))
{
var obj = conn.Query <AreaBook>(sql).AsList();
if (obj != null && obj.Count >= 1)
{
books = obj;
}
}
return(books);
}
public List <ToDoModel> getToDo(string uid, string connstr)
{
List <ToDoModel> _user = null;
string sql = $@"SELECT t.id ID,t.title TITLE,t.content CONTENT,t.endtime ENDTIME,t.createtime CREATETIME,
t.progress PROGRESS, p.color PRIORITYCOLOR, u.username USERNAME from sys_todolist t
INNER JOIN sys_priority p on t.priority = p.id
INNER JOIN sys_user u on t.uid = u.id where progress<100";
if (!string.IsNullOrEmpty(uid))
{
sql += $" and t.uid = '{uid}'";
}
sql += " ORDER BY priority desc,progress asc";
using (IDbConnection conn = DataBaseConfig.GetSqlConnection(DBType.mysql, connstr))
{
var obj = conn.Query <ToDoModel>(sql).AsList();
if (obj != null && obj.Count >= 1)
{
return(obj);
}
}
return(_user);
}