/// <summary>
/// 获取所有数据
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public List <Sys_ItemsDetail> GetList(string id = "", string keyvalue = "")
{
using (var db = SqlSugarDao.GetInstance())
{
List <Sys_ItemsDetail> list = null;
var data = db.Queryable <Sys_ItemsDetail>();
list = data.ToList();
if (id != "" && id != null)
{
list = data.Where(c => c.F_ItemId == id).ToList();
}
if (keyvalue != "" && keyvalue != null)
{
list = data.Where(c => c.F_ItemName.Contains(keyvalue) || c.F_ItemCode.Contains(keyvalue)).ToList();
}
if (list.Count > 0)
{
return(list);
}
else
{
return(null);
}
}
}
public bool Add(Sys_Role obj, List <Sys_RoleAuthorize> list, bool isadd)
{
using (SqlSugarClient db = SqlSugarDao.GetInstance())//开启数据库连接
{
bool i = false;
db.CommandTimeOut = 30000;//设置超时时间
try
{
db.BeginTran();//开启事务
if (!isadd)
{
db.Delete <Sys_RoleAuthorize>("F_ObjectId=@F_id", new { F_id = obj.F_Id });
string[] notstr = { "F_CreatorUserId", "F_CreatorTime", "F_Category" };
db.AddDisableUpdateColumns(notstr);//添加禁止更新列
db.Update(obj);
}
else
{
db.Insert(obj);
}
db.InsertRange(list);
i = true;
db.CommitTran();//提交事务
}
catch (Exception ex)
{
db.RollbackTran();//回滚事务
i = false;
}
return(i);
}
}
public bool RevisePassword(string id, string password)
{
using (var db = SqlSugarDao.GetInstance())
{
return(db.Update <Sys_User>(new { F_Password = password }, c => c.F_Id == id).ObjToBool());
}
}
/// <summary>
/// 启用/禁用账户
/// </summary>
/// <param name="ID">用户id</param>
/// <param name="IsAble">是否启用</param>
/// <returns></returns>
public bool Startusing(string ID, int IsAble)
{
using (var db = SqlSugarDao.GetInstance())
{
bool i = db.Update <Sys_User>(new { F_EnabledMark = IsAble }, c => c.F_Id == ID).ObjToBool();
return(i);
}
}
/// <summary>
/// 根据角色获取按钮
/// </summary>
/// <param name="roleid"></param>
/// <returns></returns>
public List <Sys_ModuleButton> GetButtonList(string roleid)
{
using (var db = SqlSugarDao.GetInstance())
{
var data = db.SqlQuery <Sys_ModuleButton>("select * from Sys_ModuleButton where F_Id in (select F_ItemId from [Sys_RoleAuthorize] WHERE [F_ObjectId] ='" + roleid + "')").ToList();
return(data);
}
}
public virtual bool Delete(string[] idstr)
{
using (var db = SqlSugarDao.GetInstance())
{
bool i = db.Delete <T, string>(idstr).ObjToBool();
return(i);
}
}
/// <summary>
/// 添加聊天记录
/// </summary>
/// <param name="cd"></param>
/// <returns></returns>
public bool AddChatrecord(Sys_Chatrecord cd)
{
using (var db = SqlSugarDao.GetInstance())
{
bool add = db.Insert <Sys_Chatrecord>(cd).ObjToBool();
return(add);
}
}
public List <Sys_Log> GetPageList(Pagination pagination, string queryJson, out int records)
{
using (var db = SqlSugarDao.GetInstance())
{
var pageCount = 0;
List <Sys_Log> list = null;
var queryParam = queryJson.ToJObject();
var data = db.Queryable <Sys_Log>();
if (queryParam["keyword"] != null && queryParam["keyword"].ToString() != "")
{
string keyword = queryParam["keyword"].ToString();
data = data.
Where(c => c.F_Account.Contains(keyword) || c.F_NickName.Contains(keyword) || c.F_Description.Contains(keyword));
//OrderBy(pagination.sidx + " " + pagination.sord).ToPageList(pagination.page, pagination.rows, ref pageCount);
}
if (queryParam["timeType"] == null || queryParam["timeType"].ToString() == "")
{
queryParam["timeType"] = "2";
}
string timeType = queryParam["timeType"].ToString();
DateTime startTime = DateTime.Now.ToString("yyyy-MM-dd").ObjToDate();
DateTime endTime = DateTime.Now.ToString("yyyy-MM-dd").ObjToDate().AddDays(1);
switch (timeType)
{
case "1":
break;
case "2":
startTime = DateTime.Now.AddDays(-7);
break;
case "3":
startTime = DateTime.Now.AddMonths(-1);
break;
case "4":
startTime = DateTime.Now.AddMonths(-3);
break;
default:
break;
}
data = data.Where(t => t.F_Date >= startTime && t.F_Date <= endTime);
if (data != null)
{
list = data.OrderBy(pagination.sidx + " " + pagination.sord).ToPageList(pagination.page, pagination.rows, ref pageCount);
records = pageCount;
}
else
{
list = null;
records = 0;
}
return(list);
}
}
public virtual List <T> GetList()
{
using (var db = SqlSugarDao.GetInstance())
{
List <T> list = new List <T>();
list = db.SqlQuery <T>("select * from " + GetTabelname()).ToList <T>();
return(list);
}
}
public string GetUpjsonstr(string id)
{
string str = "";
using (var db = SqlSugarDao.GetInstance())
{
str = db.SqlQueryJson("select * from " + GetTabelname() + " where F_Id='" + id + "'");
}
return(str);
}
public virtual bool Delete(string id)
{
string str = GetUpjsonstr(id);
using (var db = SqlSugarDao.GetInstance())
{
bool i = db.Delete <T>("F_Id=@F_Id", new { F_Id = id }).ObjToBool();
WriteLog.WriteDatalog(str, "", i, GetTabelname(), "删除");
return(i);
}
}
public virtual bool Add(List <T> obj, string[] disstr = null)
{
using (var db = SqlSugarDao.GetInstance())
{
if (disstr != null)
{
db.DisableInsertColumns = disstr;
}
bool add = db.InsertRange(obj).ObjToBool();
string str = obj.ToJson();
WriteLog.WriteDatalog(str, "", add, GetTabelname(), "新增");
return(add);
}
}
/// <summary>
/// 用户登录
/// </summary>
public Sys_User UserLogin(string loginId, string loginPwd)
{
using (var db = SqlSugarDao.GetInstance())
{
List <Sys_User> list = db.Queryable <Sys_User>().Where(c => c.F_EnabledMark == true).Where(c => c.F_Account == loginId && c.F_Password == loginPwd).ToList();
if (list.Count > 0)
{
return(list[0]);
}
else
{
return(null);
}
}
}
/// <summary>
/// 获取用户登录信息
/// </summary>
public Sys_User GetUserById(string userId)
{
using (var db = SqlSugarDao.GetInstance())
{
List <Sys_User> list = db.Queryable <Sys_User>().Where(c => c.F_Id == userId).ToList();
if (list.Count > 0)
{
return(list[0]);
}
else
{
return(null);
}
}
}
/// <summary>
/// 获取所有机构
/// </summary>
public List <Sys_Role> GetList()
{
using (var db = SqlSugarDao.GetInstance())
{
List <Sys_Role> list = db.Queryable <Sys_Role>().Where(c => c.F_Category == 2).ToList();
if (list.Count > 0)
{
return(list);
}
else
{
return(null);
}
}
}
/// <summary>
/// id获取数据
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public Sys_Log GetForm(string id)
{
using (var db = SqlSugarDao.GetInstance())
{
var data = db.Queryable <Sys_Log>().Where(c => c.F_Id == id).ToList();
if (data.Count > 0)
{
return(data[0]);
}
else
{
return(null);
}
}
}
public virtual T GetForm(string id)
{
using (var db = SqlSugarDao.GetInstance())
{
List <T> list = new List <T>();
list = db.SqlQuery <T>("select * from " + GetTabelname() + " where F_Id=@F_Id", new { F_Id = id }).ToList <T>();
if (list.Count > 0)
{
return(list[0]);
}
else
{
return(null);
}
}
}
/// <summary>
/// 分页
/// </summary>
/// <param name="pagination"></param>
/// <param name="keyword"></param>
/// <param name="records"></param>
/// <returns></returns>
public List <Sys_File> GetPageList(Pagination pagination, string keyword, out int records)
{
using (var db = SqlSugarDao.GetInstance())
{
var pageCount = 0;
List <Sys_File> list = null;
var data = db.Queryable <Sys_File>();
if (keyword != null && keyword != "")
{
data = data.Where(c => c.F_FileType == keyword);
}
list = data.OrderBy(pagination.sidx + " " + pagination.sord).ToPageList(pagination.page, pagination.rows, ref pageCount);
records = pageCount;
return(list);
}
}
public virtual bool Update(T obj, string[] disablestr = null)
{
var otherProp = typeof(T).GetProperty("F_Id");
var valFirst = otherProp.GetValue(obj, null) as IComparable;
string str = GetUpjsonstr(valFirst.ToString());
using (var db = SqlSugarDao.GetInstance())
{
if (disablestr != null)
{
db.AddDisableUpdateColumns(disablestr);//添加禁止更新列
}
bool i = db.Update <T>(obj).ObjToBool();
WriteLog.WriteDatalog(str, obj.ToJson(), i, GetTabelname(), "修改");
return(i);
}
}
/// <summary>
/// 编码获取
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public List <Sys_ItemsDetail> GetItemList(string encode)
{
using (var db = SqlSugarDao.GetInstance())
{
var data = db.Queryable <Sys_ItemsDetail>().
JoinTable <Sys_Items>((s1, s2) => s1.F_ItemId == s2.F_Id).
Where("s2.F_EnCode = '" + encode + "'").Select("s1.*").ToList();
if (data.Count > 0)
{
return(data);
}
else
{
return(null);
}
}
}
/// <summary>
/// 分页
/// </summary>
/// <param name="pagination"></param>
/// <param name="keyword"></param>
/// <param name="records"></param>
/// <returns></returns>
public List <Sys_User> GetPageList(Pagination pagination, string keyword, out int records)
{
using (var db = SqlSugarDao.GetInstance())
{
var pageCount = 0;
List <Sys_User> list = null;
if (keyword != null && keyword != "")
{
list = db.Queryable <Sys_User>().
Where(c => c.F_Account.Contains(keyword) || c.F_RealName.Contains(keyword) || c.F_MobilePhone.Contains(keyword)).
OrderBy(pagination.sidx + " " + pagination.sord).ToPageList(pagination.page, pagination.rows, ref pageCount);
}
else
{
list = db.Queryable <Sys_User>().OrderBy(pagination.sidx + " " + pagination.sord).ToPageList(pagination.page, pagination.rows, ref pageCount);
}
records = pageCount;
return(list);
}
}
public List <Sys_ModuleButton> GetList(string id = "")
{
using (var db = SqlSugarDao.GetInstance())
{
List <Sys_ModuleButton> list = null;
var data = db.Queryable <Sys_ModuleButton>();
list = data.ToList();
if (id != "" && id != null)
{
list = data.Where(c => c.F_ModuleId == id).ToList();
}
if (list.Count > 0)
{
return(list);
}
else
{
return(null);
}
}
}
public List <Sys_Role> GetList(string id = "")
{
using (var db = SqlSugarDao.GetInstance())
{
List <Sys_Role> list = null;
var data = db.Queryable <Sys_Role>().Where(c => c.F_Category == 1);
if (id != "" && id != null)
{
data = data.Where(c => c.F_Id == id);
}
list = data.ToList();
if (list.Count > 0)
{
return(list);
}
else
{
return(null);
}
}
}
/// <summary>
/// 清空数据
/// </summary>
/// <returns></returns>
public bool Remove(string keeptime)
{
using (var db = SqlSugarDao.GetInstance())
{
DateTime operateTime = DateTime.Now;
if (keeptime == "7") //保留近一周
{
operateTime = DateTime.Now.AddDays(-7);
}
else if (keeptime == "1") //保留近一个月
{
operateTime = DateTime.Now.AddMonths(-1);
}
else if (keeptime == "3") //保留近三个月
{
operateTime = DateTime.Now.AddMonths(-3);
}
bool i = db.Delete <Sys_Log>(c => c.F_Date <= operateTime).ObjToBool();
return(true);
}
}
/// <summary>
/// 获取所有菜单
/// </summary>
public List <Sys_ModuleButton> GetAllModule()
{
using (var db = SqlSugarDao.GetInstance())
{
List <Sys_ModuleButton> list = db.Queryable <Sys_ModuleButton>().Where(c => c.F_ParentId == "0").ToList();
if (list.Count > 0)
{
//foreach (var c in list)
//{
// List<Sys_Module> listch = GetList(c.F_Id);
// if (listch != null)
// {
// c.ChildNodes = listch;
// }
//}
return(list);
}
else
{
return(null);
}
}
}
/// <summary>
/// 获取所有机构
/// </summary>
public List <Sys_Area> GetAllModule()
{
using (var db = SqlSugarDao.GetInstance())
{
List <Sys_Area> list = db.Queryable <Sys_Area>().Where(c => c.F_ParentId == "0").ToList();
if (list.Count > 0)
{
foreach (var c in list)
{
List <Sys_Area> listch = GetList(c.F_Id);
if (listch != null)
{
c.ChildNodes = listch;
}
}
return(list);
}
else
{
return(null);
}
}
}
/// <summary>
/// 获取群组
/// </summary>
/// <param name="userid"></param>
/// <returns></returns>
public CSResult GetAllGroup(string userid)
{
using (var db = SqlSugarDao.GetInstance())
{
var friendGroup = new List <CSGroupResult>();
var group = db.Queryable <Sys_Relation>().Where(s1 => s1.F_FriendId == userid && s1.F_Type == 2).GroupBy(s1 => s1.F_Group).Select("F_Group").ToDataTable();
for (int i = 0; i < group.Rows.Count; i++)
{
var friends = new List <CSBaseModel>();
string gr = group.Rows[i]["F_Group"].ToString();
var fri = db.Queryable <Sys_Relation>().
JoinTable <Sys_Flock>((s1, s2) => s1.F_UserId == s2.F_Id).
Where(s1 => s1.F_FriendId == userid && s1.F_Type == 2 && s1.F_Group == gr).
Select("s1.*,s2.*").ToDataTable();
for (int j = 0; j < fri.Rows.Count; j++)
{
friends.Add(new CSFriend {
id = fri.Rows[j]["F_UserId"].ToString(), name = fri.Rows[j]["F_FlockName"].ToString(), face = fri.Rows[j]["F_Flockphotos"].ToString()
});
}
friendGroup.Add(new CSGroupResult {
id = 1 + i, item = friends, name = group.Rows[i]["F_Group"].ToString()
});
}
CSResult result = new CSResult
{
msg = "ok",
status = 1,
data = friendGroup
};
return(result);
}
}
/// <summary>
/// 角色获取菜单
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public List <Sys_Module> GetMRoleList(string id)
{
using (var db = SqlSugarDao.GetInstance())
{
var data = db.SqlQuery <Sys_Module>("select * from Sys_Module where F_Id in (select F_ItemId from [Sys_RoleAuthorize] WHERE [F_ObjectId] ='" + id + "')");
List <Sys_Module> list = data.Where(c => c.F_ParentId == "0").ToList();
if (list.Count > 0)
{
foreach (var c in list)
{
List <Sys_Module> listch = data.Where(a => a.F_ParentId == c.F_Id).ToList();
if (listch != null)
{
c.ChildNodes = listch;
}
}
return(list);
}
else
{
return(null);
}
}
}
public CSResult GetAllGroupFriend(string groupid)
{
using (var db = SqlSugarDao.GetInstance())
{
var friends = new List <CSBaseModel>();
var fri = db.Queryable <Sys_Relation>().
JoinTable <Sys_User>((s1, s2) => s1.F_FriendId == s2.F_Id).
Where(s1 => s1.F_UserId == groupid && s1.F_Type == 2).
Select("s1.*,s2.*").ToDataTable();
for (int j = 0; j < fri.Rows.Count; j++)
{
friends.Add(new CSFriend {
id = fri.Rows[j]["F_FriendId"].ToString(), name = fri.Rows[j]["F_RealName"].ToString(), face = fri.Rows[j]["F_HeadIcon"].ToString()
});
}
CSResult result = new CSResult
{
msg = "ok",
status = 1,
data = friends
};
return(result);
}
}