/// <summary>
/// 显示用户&&角色
/// </summary>
/// <returns></returns>
public List <UserAndRole> ShowUserAndRole()
{
var db = SugerBase.GetInstance();
var userlist = db.SqlQueryable <UserAndRole>("select a.*,c.rolename from users a,userrole b,role c where a.id=b.userid and b.roleid=c.id");
return(userlist.ToList());
}
/// <summary>
/// 外派警员列表
/// </summary>
/// <returns></returns>
public List <UserAndRole> GetRoles()
{
var db = SugerBase.GetInstance();
var userList = db.SqlQueryable <UserAndRole>("select B.ID,B.RealName,B.state from Role a,Users b,UserRole c where a.ID=c.roleid AND b.id=c.UserID AND C.ROLEID=88 and b.State=0").ToList();
return(userList);
}
/// <summary>
/// 角色显示
/// </summary>
/// <returns></returns>
public List <RoleAndAuthority> GetRole()
{
var db = SugerBase.GetInstance();
var rolelist = db.SqlQueryable <RoleAndAuthority>("select * from Role");
return(rolelist.ToList());
}
/// <summary>
/// 修改用户反填
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public UserAndRole UpdateUserShow(int id)
{
var db = SugerBase.GetInstance();
var user = db.SqlQueryable <UserAndRole>("select a.*,c.rolename from users a,userrole b,role c where a.id=b.userid and b.roleid=c.id and a.ID=" + id).Single();
return(user);
}
/// <summary>
/// 获取权限下拉列表
/// </summary>
/// <param name="gid"></param>
/// <returns></returns>
public List <Authority> GetAuthorityList(int gid = 0)
{
var db = SugerBase.GetInstance();
var authority0 = db.Queryable <Authority>().Where(c => c.FatherId == 0).ToList();
return(authority0);
}
public string GetStudentName()
{
var db = SugerBase.GetInstance();
var name = db.Queryable <Student>().Single(m => m.ID == 1).Name;
return(name);
}
/// <summary>
/// 添加权限
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int AddAuthority(Authority model)
{
var db = SugerBase.GetInstance();
int i = db.Insertable(model).ExecuteCommand();
return(i);
}
/// <summary>
/// 登录
/// </summary>
/// <param name="name"></param>
/// <param name="pwd"></param>
/// <returns></returns>
public UserAndRole Login(string name, string pwd)
{
var db = SugerBase.GetInstance();
var userlist = db.SqlQueryable <UserAndRole>("select a.*,c.rolename,c.ID as RID,c.state as RState from users a,userrole b,role c where a.id=b.userid and b.roleid=c.id").Where(m => (m.LoginName == name && m.Password == pwd)).Single();
return(userlist);
}
/// <summary>
/// 批量停用权限
/// </summary>
/// <param name="ids"></param>
/// <returns></returns>
public int DeleteAuthority(string id)
{
SqlSugarClient context = new SqlSugarClient(new ConnectionConfig()
{
ConnectionString = "Data Source=169.254.159.216/orcl;User ID=scott;Password=tiger;",
DbType = DbType.Oracle
});
context.Ado.IsEnableLogEvent = true;
SimpleClient <Authority> simple = new SimpleClient <Authority>(context);
var db = SugerBase.GetInstance();
string[] ids = id.Split(',');
int state = 0;
foreach (var item in ids)
{
int intId = Convert.ToInt32(item);
state += (simple.Update(m => new Authority()
{
State = 1
}, m => m.ID == intId)) ? 1 : 0;
}
return(state);
}
/// <summary>
/// 添加报警
/// </summary>
/// <param name="alarm"></param>
/// <returns></returns>
public int AddCallPolice(Alarm alarm)
{
var db = SugerBase.GetInstance();
var result = db.Insertable <Alarm>(alarm);
return(result.ExecuteCommand());
}
/// <summary>
/// 案件添加到审批
/// </summary>
/// <param name="approve"></param>
/// <returns></returns>
public int InsertApprove(Approve approve)
{
var db = SugerBase.GetInstance();
int result = db.Insertable(approve).ExecuteCommand();
return(result);
}
public List <Business> GetBusinessesList()
{
var db = SugerBase.GetInstance();
List <Business> list = db.Queryable <Business>().ToList();
return(list);
}
/// <summary>
/// 角色下拉列表
/// </summary>
/// <returns></returns>
public List <Role> GetRoleList(int gid)
{
var db = SugerBase.GetInstance();
var role = db.Queryable <Role>().ToList();
return(role);
}
/// <summary>
/// 获取权限列表
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public List <Authority> GetAuthority()
{
var db = SugerBase.GetInstance();
var authoritys = db.Queryable <Authority>().ToList();
return(authoritys);
}
/// <summary>
/// 添加用户
/// </summary>
public int AddUser(Users users, string roleid)
{
var db = SugerBase.GetInstance();
db.Insertable(users);
users.Spare = "";
users.State = 0;
var addtime = DateTime.Now.ToString("yyyy-MM-dd hh24:mi:ss");
var x = db.Insertable <Users>(users);
int i = db.Insertable(users).ExecuteCommand();
if (i > 0)
{
var newId = db.Queryable <Users>().Where(m => m.LoginName == users.LoginName).First();
string[] ids = roleid.Split(',');
int state = 0;
foreach (var item in ids)
{
int itemid = Convert.ToInt32(item);
UserRole userRole = new UserRole();
userRole.RoleId = itemid;
userRole.UserId = newId.ID;//用户ID
state += db.Insertable(userRole).ExecuteCommand();
}
return(ids.Length == state ? state : 0);
}
return(0);
}
/// <summary>
/// 修改权限保存
/// </summary>
/// <param name="authority"></param>
/// <returns></returns>
public int UpdateAuthority(Authority authority)
{
var db = SugerBase.GetInstance();
int result = db.Updateable(authority).Where(m => m.ID == authority.ID).ExecuteCommand();
return(result);
}
/// <summary>
/// 添加角色
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int AddRole(string name, string qxid)
{
var db = SugerBase.GetInstance();
Role role = new Role();
role.RoleName = name;
int i = db.Insertable(role).ExecuteCommand();
if (i > 0)
{
var newId = db.Queryable <Role>().Where(m => m.RoleName == name).OrderBy("ID desc").First().ID;
string[] qxids = qxid.Split(',');
int state = 0;
foreach (var item in qxids)
{
int id = Convert.ToInt32(item);
RoleAuthority roleAuthority = new RoleAuthority();
roleAuthority.AuthorityId = id;
roleAuthority.RoleId = Convert.ToInt32(newId);
state += db.Insertable(roleAuthority).ExecuteCommand();//记录成功条数
}
return(qxids.Length == state ? state : -1);
}
return(0);
}
/// <summary>
/// 修改角色反填
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public RoleAndAuthority UpdateRoleShow(int id)
{
var db = SugerBase.GetInstance();
var role = db.SqlQueryable <RoleAndAuthority>("select a.*,c.name from role a,ROLEAUTHORITY b,Authority c where a.id=b.roleid and b.Authorityid=c.id and a.ID=" + id).First();
return(role);
}
/// <summary>
/// 返回审批配置对象
/// </summary>
/// <param name="placed"></param>
/// <returns></returns>
public ApproveCourse GetApproveCoursesList(int placed)
{
var db = SugerBase.GetInstance();
ApproveCourse approveCourse = db.Queryable <ApproveCourse>().Where(m => m.ID == placed).Single();
return(approveCourse);
}
/// <summary>
/// 修改权限反填
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public Authority UpdateAuthorityShow(int id)
{
var db = SugerBase.GetInstance();
List <Authority> list = db.SqlQueryable <Authority>("select * from Authority where ID=" + id).ToList();
Authority authority = list[0];
return(authority);
}
/// <summary>
/// 修改审批对象
/// </summary>
/// <param name="approve"></param>
/// <returns></returns>
public int UpdateApprove(Approve approve)
{
var db = SugerBase.GetInstance();
int result = db.Updateable(approve).Where(m => m.ID == approve.ID).ExecuteCommand();
return(result);
}
/// <summary>
/// 审核最后对案件状态进行更改
/// </summary>
/// <param name="instance"></param>
/// <returns></returns>
public int UpdateinStance(Instance instance)
{
var db = SugerBase.GetInstance();
int result = db.Updateable(instance).Where(m => m.ID == instance.ID).ExecuteCommand();
return(result);
}
/// <summary>
/// 根据ID返回审批对象
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public Approve GetApproveById(int id)
{
var db = SugerBase.GetInstance();
List <Approve> list = db.SqlQueryable <Approve>("select * from Approve where ID=" + id).ToList();
Approve approve = list[0];
return(approve);
}
/// <summary>
/// 添加案件
/// </summary>
/// <param name="instance"></param>
/// <returns></returns>
public int InsertInstance(Instance instance)
{
var db = SugerBase.GetInstance();
int result = db.Insertable(instance).ExecuteCommand();
return(result);
}
public int GetinStanceByClass(Instance instance)
{
var db = SugerBase.GetInstance();
List <Instance> instanceList = db.SqlQueryable <Instance>("select * from Instance where AlterID = " + instance.AlterID + " and RegisterPeopleID=" + instance.RegisterPeopleID + " and InstanceTypes=" + instance.InstanceTypes + " and ApproveState=" + instance.ApproveState + " and InstanceState=" + instance.InstanceState).ToList();
Instance instances = instanceList[0];
int result = instances.ID;
return(result);
}
/// <summary>
/// 权限显示
/// </summary>
/// <returns></returns>
public List <Authority> GetAuthority(int id)
{
var db = SugerBase.GetInstance();
var userlist = db.SqlQueryable <Authority>("select distinct d.Name,Url,FatherID,d.ID,OrderID from Users a join UserRole b on a.ID = b.UserID" +
" join RoleAuthority c on b.RoleID = c.RoleID " +
" join Authority d on d.ID = c.AuthorityId where a.ID = " + id).ToList();
return(userlist);
}
/// <summary>
/// 根据Id查询对应的案件
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public Instance GetInstanceById(int id)
{
var db = SugerBase.GetInstance();
List <Instance> instances = db.SqlQueryable <Instance>("select * from Instance where ID=" + id).ToList();
Instance instance = instances[0];
return(instance);
}
/// <summary>
/// 审批页面显示
/// </summary>
/// <returns></returns>
//PageList<ApproveDataModel> GetApproveList(PageParams pageParams)
//{
// var db = SugerBase.GetInstance();
// List<ApproveDataModel> list = db.SqlQueryable<ApproveDataModel>(
// "select Approve.ID,Instance.ID as InstanceID,Instance.RegisterPeopleID,Business.ID as BusinessID,Business.Name as BusinessName,Users.realName as UsersName,Role.Name as RoleName,Instance.InstanceTypes,Instance.InstanceTime,Instance.ApproveState " +
// "from Approve,Users,Instance,Business,Role " +
// "where Approve.ORIGINALID=Instance.ID and Approve.BUSINESSTYPEID=Business.ID and Approve.APPROVEPEOPLEID=USERS.ID and Approve.ROLEID=Role.ID and Approve.State=1 "+pageParams.Filter).Skip(pageParams.CurPage).Take(pageParams.PageSize).ToList();
// int count = db.SqlQueryable<ApproveDataModel>(
// "select Approve.ID,Instance.ID as InstanceID,Instance.RegisterPeopleID,Business.ID as BusinessID,Business.Name as BusinessName,Users.realName as UsersName,Role.Name as RoleName,Instance.InstanceTypes,Instance.InstanceTime,Instance.ApproveState " +
// "from Approve,Users,Instance,Business,Role " +
// "where Approve.ORIGINALID=Instance.ID and Approve.BUSINESSTYPEID=Business.ID and Approve.APPROVEPEOPLEID=USERS.ID and Approve.ROLEID=Role.ID and Approve.State=1 " + pageParams.Filter).Count();
// PageList<ApproveDataModel> pagelist = new PageList<ApproveDataModel>() { ListData = list, TotalCount = count };
// return pagelist;
//}
/// <summary>
/// 审批页面点击查看案件详情页面
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public InstanceDataModel GetInstanceById(int id)
{
var db = SugerBase.GetInstance();
List <InstanceDataModel> inStanceList = db.SqlQueryable <InstanceDataModel>(
"select Instance.ID,Alarm.AlarmReason,Alarm.DetailSplace,Users.RealName,Instance.InstanceTypes,Instance.ApproveState,Instance.InstanceState,Instance.Time as InstanceTime,Instance.Space " +
"from Instance,Alarm,Users" +
" where Instance.AlterID=Alarm.ID and Instance.RegisterPeopleID=Users.ID and Instance.ID=" + id
).ToList();
InstanceDataModel inStance = inStanceList[0];
return(inStance);
}
/// <summary>
/// 添加案件时添加审批表并返回
/// </summary>
/// <param name="instance"></param>
/// <returns></returns>
public Approve GetApprove(Instance instance)
{
var db = SugerBase.GetInstance();
List <ApproveCourse> course = db.SqlQueryable <ApproveCourse>("select * from ApproveCourse where Condition like '%" + instance.InstanceState + "%' and BusinesstypeId=" + instance.InstanceTypes).ToList();
ApproveCourse approveCourse = course[0];
//ApproveCourse approveCourse= db.Queryable<ApproveCourse>().Where(m => (m.Condition.Contains(instance.InstanceState.ToString()) && m.BusinesstypeId == Convert.ToInt32(instance.InstanceTypes))).Single();
Approve approve = new Approve()
{
RoleId = approveCourse.ApproveRoleId, BusinesstypeId = approveCourse.BusinesstypeId, PlaceID = approveCourse.PostpositionID, OriginalId = instance.ID, Ideas = "", State = "1"
};
return(approve);
}
/// <summary>
/// 修改用户保存
/// </summary>
/// <param name="id"></param>
/// <param name="users"></param>8x
/// <param name="roleid"></param>
/// <returns></returns>
public int UpdateUser(Users users, string roleid, int id)
{
var db = SugerBase.GetInstance();
var client = SimpleClientBase.GetSimpleClient <Users>();
var result = db.Updateable(users).Where(m => m.ID == users.ID).ExecuteCommand();
//update关联表
if (result > 0)
{
var linkClient = SimpleClientBase.GetSimpleClient <UserRole>();
var res = linkClient.Update(m => new UserRole {
RoleId = Convert.ToInt32(roleid)
}, m => m.UserId == users.ID);
return(res?1:0);
}
return(0);
}