/// <summary>获得所有插件（包括被禁用的）
/// </summary>
public IEnumerable<PluginEntity> GetPlugins(int pageIndex, int pageSize, string searchName)
{
StringBuilder sql = new StringBuilder();
sql.Append("select Id,Name,Describe,Status,Author,DefaultController,DefaultAction,PVersion,MenuShow,Icon from t_plugin");
if (!string.IsNullOrEmpty(searchName))
{
sql.Append(" where Name=@Name ");
}
if (pageSize != 0)
{
sql.Append(" Limit @pageSize");
}
if (pageSize != 0 && pageIndex != 0)
{
sql.Append(" offset (@pageSize*(@pageIndex-1))");
}
SQLiteParameter[] para = new SQLiteParameter[]
{
new SQLiteParameter("@pageSize", pageSize),
new SQLiteParameter("@pageIndex", pageIndex),
new SQLiteParameter("@Name", searchName)
};
SqlLiteHelper sqlLiteHelper = new SqlLiteHelper();
DataTable dataTable = sqlLiteHelper.GetDataTable(sql.ToString(), para);
return (from DataRow dataRow in dataTable.Rows select Convert2Entity(dataRow)).ToList();
}
/// <summary>获取角色列表
/// </summary>
/// <param name="skip">跳过的记录数</param>
/// <param name="count">获取的数量</param>
/// <returns></returns>
public List<RoleEntity> GetRoles(int skip, int pagesize, string RoleName, out int count)
{
StringBuilder sql = new StringBuilder();
sql.Append("select Id,Name,RightIds,IsSuper from t_roles ");
if (!string.IsNullOrEmpty(RoleName))
{
sql.Append(" where Name=@Name ");
}
if (pagesize != 0)
{
sql.Append(" Limit @count Offset @skip ");
}
SqlLiteHelper sqliteHelper = new SqlLiteHelper();
SQLiteParameter[] para = new SQLiteParameter[] {
new SQLiteParameter("@count", pagesize), new SQLiteParameter("@skip", skip),
new SQLiteParameter("@Name", RoleName)
};
DataTable dataTable = sqliteHelper.GetDataTable(sql.ToString(), para);
List<RoleEntity> roleEntities = new List<RoleEntity>();
foreach (DataRow dataRow in dataTable.Rows)
{
roleEntities.Add(Conver2Entity(dataRow));
}
count = sqliteHelper.GetCount("t_roles");
return roleEntities;
}
public List<UserEntity> GetUsers(int skip, int pagesize, string userName, out int count)
{
StringBuilder sql = new StringBuilder();
sql.Append(" select Uid,Name,Photo,RolesIds,Status from t_users ");
if (!string.IsNullOrEmpty(userName))
{
sql.Append(" where Uid like @Uid ");
}
if (pagesize != 0)
{
sql.Append(" Limit @count Offset @skip ");
}
SQLiteParameter[] para = new SQLiteParameter[] {
new SQLiteParameter("@count", pagesize),
new SQLiteParameter("@skip", skip),
new SQLiteParameter("@Uid", "%"+userName+"%")
};
SqlLiteHelper sqliteHelper = new SqlLiteHelper();
DataTable dataTable = sqliteHelper.GetDataTable(sql.ToString(), para);
List<UserEntity> userEntities = new List<UserEntity>();
foreach (DataRow dataRow in dataTable.Rows)
{
userEntities.Add(Conver2Entity(dataRow));
}
count = sqliteHelper.GetCount("t_users");
return userEntities;
}
public bool SetUserStatus(string uId, int status)
{
string sql = "update t_users set Status=@status where Uid=@uId";
SQLiteParameter[] para = new SQLiteParameter[] { new SQLiteParameter("@status", status), new SQLiteParameter("@uId", uId) };
int result = new SqlLiteHelper().RunSQL(sql, para);
return result > 0;
}
internal void creatTable(SqlLiteHelper sqlLiteHelper)
{
string sql = @"CREATE TABLE `t_userLogin` (
`Uid` TEXT NOT NULL,
`Sign` TEXT,
`ExtTime` TEXT
);";
sqlLiteHelper.RunSQL(sql);
}
/// <summary>获取角色列表
/// </summary>
/// <param name="roleIds">角色id串</param>
/// <returns></returns>
public List<RoleEntity> GetRoles(List<int> roleIds)
{
string sql = string.Format("select Id,Name,RightIds,IsSuper from t_roles where Id in ({0})", string.Join(",", roleIds));
DataTable dataTable = new SqlLiteHelper().GetDataTable(sql);
List<RoleEntity> roleEntities = new List<RoleEntity>();
foreach (DataRow dataRow in dataTable.Rows)
{
roleEntities.Add(Conver2Entity(dataRow));
}
return roleEntities;
}
/// <summary>获取权限列表
/// </summary>
/// <returns></returns>
public List<RightEntity> GetAllRights()
{
const string sql = "select Id,Name,Url,Level,ParentId,Describe,IsMenu,Deleted,Category from t_rights where Deleted=0";
DataTable dataTable = new SqlLiteHelper().GetDataTable(sql);
List<RightEntity> list = new List<RightEntity>();
foreach (DataRow dataRow in dataTable.Rows)
{
list.Add(Conver2Entity(dataRow));
}
return list;
}
internal void creatTable(SqlLiteHelper sqlLiteHelper)
{
//Id,Name,Describe,Status,Author,DefaultController,DefaultAction,PVersion,MenuShow,Icon
string sql = @"CREATE TABLE `t_roles` (
`Id` INTEGER NOT NULL,
`Name` TEXT NOT NULL,
`RightIds` TEXT NOT NULL DEFAULT ',',
`IsSuper` INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY(Id)
);";
sqlLiteHelper.RunSQL(sql);
}
/// <summary>获得权限列表
/// </summary>
/// <param name="category">类别，插件Id</param>
/// <returns></returns>
public List<RightEntity> GetRights(int category)
{
const string sql = "select Id,Name,Url,Level,ParentId,Describe,IsMenu,Deleted,Category from t_rights where Category = @category;";
SQLiteParameter[] para = new SQLiteParameter[] { new SQLiteParameter("@category", category) };
DataTable dataTable = new SqlLiteHelper().GetDataTable(sql, para);
List<RightEntity> list = new List<RightEntity>();
foreach (DataRow dataRow in dataTable.Rows)
{
list.Add(Conver2Entity(dataRow));
}
return list;
}
public List<RoleEntity> GetRoles()
{
string sql = "select Id,Name,RightIds,IsSuper from t_roles";
//, count, skip);
SqlLiteHelper sqliteHelper = new SqlLiteHelper();
DataTable dataTable = sqliteHelper.GetDataTable(sql);
List<RoleEntity> roleEntities = new List<RoleEntity>();
foreach (DataRow dataRow in dataTable.Rows)
{
roleEntities.Add(Conver2Entity(dataRow));
}
return roleEntities;
}
public List<RightEntity> GetRights(string rightIds)
{
string sql =
string.Format(
"select Id,Name,Url,Level,ParentId,Describe,IsMenu,Deleted from t_rights where Id in ({0})",
rightIds);
DataTable dataTable = new SqlLiteHelper().GetDataTable(sql);
List<RightEntity> list = new List<RightEntity>();
foreach (DataRow dataRow in dataTable.Rows)
{
list.Add(Conver2Entity(dataRow));
}
return list;
}
internal void creatTable(SqlLiteHelper sqlLiteHelper)
{
//Id,Name,Describe,Status,Author,DefaultController,DefaultAction,PVersion,MenuShow,Icon
string sql = @"CREATE TABLE `t_users` (
`Uid` TEXT NOT NULL,
`Name` TEXT NOT NULL,
`Photo` TEXT,
`RolesIds` TEXT NOT NULL DEFAULT (','),
`Status` INTEGER NOT NULL DEFAULT 0,
`PassWord` TEXT NOT NULL DEFAULT ('000000'),
PRIMARY KEY(Uid)
);";
sqlLiteHelper.RunSQL(sql);
}
/// <summary>初始化数据库
/// </summary>
public void InitDb(UserEntity user)
{
SqlLiteHelper herpler = new SqlLiteHelper();
if (herpler.InitDb())
{
new LoginDal().creatTable(herpler);
new PluginDal().creatTable(herpler);
new RightDal().creatTable(herpler);
new RoleDal().creatTable(herpler);
new UserDal().creatTable(herpler);
new UserDal().AddUser(user, EncryptionFunc.MD5Encrypt(user.Uid+"\f123456"));
}
}
internal void creatTable(SqlLiteHelper sqlLiteHelper)
{
//Id,Name,Describe,Status,Author,DefaultController,DefaultAction,PVersion,MenuShow,Icon
string sql = @"CREATE TABLE `t_rights` (
`Id` INTEGER NOT NULL,
`Name` TEXT NOT NULL,
`Url` TEXT NOT NULL UNIQUE,
`Level` INTEGER NOT NULL,
`ParentId` INTEGER NOT NULL,
`Describe` TEXT NOT NULL,
`IsMenu` INTEGER NOT NULL DEFAULT 0,
`Deleted` INTEGER NOT NULL DEFAULT 0,
`Category` INTEGER NOT NULL,
PRIMARY KEY(Id)
);";
sqlLiteHelper.RunSQL(sql);
}
internal void creatTable(SqlLiteHelper sqlLiteHelper)
{
//Id,Name,Describe,Status,Author,DefaultController,DefaultAction,PVersion,MenuShow,Icon
string sql = @"CREATE TABLE `t_plugin` (
`Id` INTEGER NOT NULL,
`Name` TEXT NOT NULL,
`Status` INTEGER NOT NULL DEFAULT 0,
`Describe` TEXT NOT NULL,
`Author` TEXT NOT NULL,
`DefaultController` TEXT NOT NULL,
`DefaultAction` TEXT NOT NULL,
`PVersion` INTEGER,
`MenuShow` INTEGER,
`Icon` TEXT,
PRIMARY KEY(Id)
);";
sqlLiteHelper.RunSQL(sql);
}
public int GetPluginsCount(string searchName)
{
StringBuilder sql = new StringBuilder();
sql.Append("select count(*) as c from t_plugin");
if (!string.IsNullOrEmpty(searchName))
{
sql.Append(" where Name=@Name");
}
SQLiteParameter[] para = new SQLiteParameter[]
{
new SQLiteParameter("@Name", searchName)
};
SqlLiteHelper sqlLiteHelper = new SqlLiteHelper();
DataTable dataTable = sqlLiteHelper.GetDataTable(sql.ToString(), para);
return int.Parse(dataTable.Rows[0]["c"].ToString());
}
/// <summary>登录
/// </summary>
/// <param name="uId">用户id</param>
/// <param name="pwd">密码（加密之后的）</param>
/// <returns>1登录成功，-1账号不存在，-2密码错误， -3账户已被禁用</returns>
public int login(string uId, string pwd)
{
string sql = "select Uid,Name,RolesIds,Status,PassWord from t_users where Uid=@uId";
SQLiteParameter[] para = new SQLiteParameter[] { new SQLiteParameter("@uId", uId) };
DataTable dataTable = new SqlLiteHelper().GetDataTable(sql, para);
if (dataTable != null && dataTable.Rows.Count == 1)
{
DataRow dataRow = dataTable.Rows[0];
if (dataRow["Status"].ToString() == "1")
{
if (dataRow["PassWord"].ToString() == pwd)
{
return 1;
}
else
{
return -2;
}
}
else
{
return -3;
}
}
else
{
return -1;
}
}
public bool UpdateRole(string uId, string roleIds)
{
string sql = "update t_users set RolesIds = @roleIds where Uid = @uId";
SQLiteParameter[] para = new SQLiteParameter[]
{
new SQLiteParameter("@uId", uId),
new SQLiteParameter("@roleIds",roleIds)
};
int result = new SqlLiteHelper().RunSQL(sql, para);
return result > 0;
}
public bool AddRole(string uId, List<int> roleIds)
{
StringBuilder sb = new StringBuilder();
sb.Append("update t_users set ");
foreach (int roleId in roleIds)
{
sb.AppendFormat("RolesIds = Replace(RolesIds,',{0},',',') , ", roleId);
}
sb.AppendFormat("RolesIds = RolesIds || '{0},' ", string.Join(",", roleIds));
sb.Append("where Uid = @uId;");
SQLiteParameter[] para = new SQLiteParameter[] { new SQLiteParameter("@uId", uId) };
int result = new SqlLiteHelper().RunSQL(sb.ToString(), para);
return result > 0;
}
/// <summary>移除角色
/// </summary>
/// <param name="roleId"></param>
/// <returns></returns>
public bool RemoveRole(int roleId)
{
string sql = "update t_users set RolesIds = Replace(RolesIds,','||@roleId||',',',');";
SQLiteParameter[] para = new SQLiteParameter[] { new SQLiteParameter("@roleId", roleId), new SQLiteParameter("@roleId", roleId) };
int result = new SqlLiteHelper().RunSQL(sql, para);
return result > 0;
}
/// <summary>修改密码
/// </summary>
/// <param name="uId"></param>
/// <param name="passWord"></param>
/// <returns></returns>
public int UpdatePassWord(string uId, string passWord, string newPWD)
{
string sql = "select PassWord from t_users where Uid =@uId ;";
SQLiteParameter[] para = new SQLiteParameter[] { new SQLiteParameter("@uId", uId) };
DataTable dt = new SqlLiteHelper().GetDataTable(sql, para);
if (dt == null || dt.Rows.Count == 0)
{
return -1;
}
else
{
if (dt.Rows[0]["PassWord"].ToString() != passWord)
{
return 0;
}
else
{
sql = "update t_users set PassWord =@passWord where Uid =@uId ;";
para = new SQLiteParameter[] { new SQLiteParameter("@passWord", newPWD), new SQLiteParameter("@uId", uId) };
int result = new SqlLiteHelper().RunSQL(sql, para);
if (result > 0)
{
return 1;
}
else
{
return -1;
}
}
}
}
/// <summary>修改密码
/// </summary>
/// <param name="uId"></param>
/// <param name="passWord"></param>
/// <returns></returns>
public bool UpdatePassWord(string uId, string passWord)
{
string sql = "update t_users set PassWord =@passWord where Uid =@uId;";
SQLiteParameter[] para = new SQLiteParameter[] { new SQLiteParameter("@passWord", passWord), new SQLiteParameter("@uId", uId) };
int result = new SqlLiteHelper().RunSQL(sql, para);
return result > 0;
}
/// <summary>删除用户角色
/// </summary>
/// <param name="uId">用户Id</param>
/// <param name="roleId">角色Id</param>
/// <returns></returns>
public bool DeleteRole(string uId, int roleId)
{
string sql = "update t_users set RolesIds = Replace(RolesIds,','||@roleId||',',',') where Uid=@uId";
SQLiteParameter[] para = new SQLiteParameter[] { new SQLiteParameter("@roleId", roleId), new SQLiteParameter("@uId", uId) };
int result = new SqlLiteHelper().RunSQL(sql, para);
return result > 0;
}
/// <summary>添加一个节点
/// </summary>
/// <param name="rightEntity"></param>
/// <returns></returns>
public int AddRight(RightEntity rightEntity)
{
string sql = "insert into t_rights (Name,Url,Level,ParentId,Describe,IsMenu,Deleted,Category) values (@Name,@Url,@Level,@ParentId,@Describe,@IsMenu,@Deleted,@Category);select last_insert_rowid();";
SQLiteParameter[] para = new SQLiteParameter[]{
new SQLiteParameter("@Name",rightEntity.Name),
new SQLiteParameter("@Url", rightEntity.Url),
new SQLiteParameter("@Level", rightEntity.Level),
new SQLiteParameter("@ParentId", rightEntity.ParentId),
new SQLiteParameter("@Describe",rightEntity.Describe),
new SQLiteParameter("@IsMenu",rightEntity.IsMenu),
new SQLiteParameter("@Deleted",rightEntity.Deleted),
new SQLiteParameter("@Category",rightEntity.Category)
};
DataTable dataTable = new SqlLiteHelper().GetDataTable(sql, para);
if (dataTable != null && dataTable.Rows.Count == 1)
{
return Int32.Parse(dataTable.Rows[0][0].ToString());
}
return -1;
}
/// <summary>修改用户头像
/// </summary>
/// <param name="uId">用户Id</param>
/// <param name="photo">用户头像</param>
/// <returns></returns>
public bool UpdatePhoto(string uId, string photo)
{
string sql = "update t_users set Photo=@Photo where Uid=@uId";
SQLiteParameter[] para = new SQLiteParameter[] { new SQLiteParameter("@Photo", photo), new SQLiteParameter("@uId", uId) };
int result = new SqlLiteHelper().RunSQL(sql, para);
return result > 0;
}
public bool AddUser(UserEntity userEntity, string pwd)
{
string sql = "Insert into t_users (Uid,Name,Photo,Status,PassWord ) values (@uId,@Name,@Photo,@status,@passWord)";
SQLiteParameter[] para = new SQLiteParameter[] {
new SQLiteParameter("@status", userEntity.Status),
new SQLiteParameter("@Name", userEntity.Name),
new SQLiteParameter("@Photo", userEntity.Photo),
new SQLiteParameter("@uId", userEntity.Uid),
new SQLiteParameter("@passWord", pwd),
};
int result = new SqlLiteHelper().RunSQL(sql, para);
return result > 0;
}
/// <summary>删除用户
/// </summary>
/// <param name="uId"></param>
/// <returns></returns>
public int DeleteUser(string uId)
{
string sql = "delete from t_users where Uid=@uId";
SQLiteParameter[] para = new SQLiteParameter[] { new SQLiteParameter("@uId", uId) };
int result = new SqlLiteHelper().RunSQL(sql, para);
return result;
}
public UserEntity GetUser(string uId)
{
string sql = "select Uid,Name,Photo,RolesIds,Status from t_users where Uid=@uId";
SQLiteParameter[] para = new SQLiteParameter[] { new SQLiteParameter("@uId", uId) };
DataTable dataTable = new SqlLiteHelper().GetDataTable(sql, para);
if (dataTable != null && dataTable.Rows.Count == 1)
{
UserEntity userEntity = Conver2Entity(dataTable.Rows[0]);
return userEntity;
}
return null;
}
/// <summary>用户Id是否已存在
/// </summary>
/// <param name="uId"></param>
/// <returns></returns>
public bool ExistUId(string uId)
{
string sql = "select Uid from t_users where Uid=@uId";
SQLiteParameter[] para = new SQLiteParameter[] { new SQLiteParameter("@uId", uId) };
DataTable dataTable = new SqlLiteHelper().GetDataTable(sql, para);
if (dataTable != null && dataTable.Rows.Count > 1)
return true;
return false;
}
/// <summary>修改用户昵称
/// </summary>
/// <param name="uId">用户Id</param>
/// <param name="userName">用户昵称</param>
/// <returns></returns>
public bool UpdateName(string uId, string userName)
{
string sql = "update t_users set Name=@userName where Uid=@uId";
SQLiteParameter[] para = new SQLiteParameter[] { new SQLiteParameter("@userName", userName), new SQLiteParameter("@uId", uId) };
int result = new SqlLiteHelper().RunSQL(sql, para);
return result > 0;
}
