484 lines
14 KiB
C#
484 lines
14 KiB
C#
using Epost.Common;
|
||
|
||
using Epost.Model;
|
||
using System;
|
||
using System.Collections.Generic;
|
||
using System.Data;
|
||
using System.Linq;
|
||
using System.Text;
|
||
using System.Threading.Tasks;
|
||
|
||
namespace Epost.DAL
|
||
{
|
||
public class UserDAL
|
||
{
|
||
//DB db = new DB();
|
||
|
||
#region 获取订单信息
|
||
public DataTable getUsercode(string block, string area)
|
||
{
|
||
try
|
||
{
|
||
string sql = string.Format("select id from users where block='{0}' and area='{1}' ",
|
||
block,
|
||
area
|
||
);
|
||
return db.GetsqlForDT(sql);
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
LogHelper.WriteLogInfo("获取工号异常：" + ex.Message);
|
||
return null;
|
||
}
|
||
}
|
||
|
||
#endregion
|
||
|
||
|
||
#region 获取每个通道的工号
|
||
public List<UserModel> getUserBlockByOne()
|
||
{
|
||
try
|
||
{
|
||
string sql = string.Format("select * from users where role_id=3");
|
||
|
||
|
||
List<UserModel> list = ModelConvertHelper<UserModel>.ConvertToList(db.GetsqlForDT(sql));
|
||
return list;
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
LogHelper.WriteLogInfo("获取工号异常：" + ex.Message);
|
||
return null;
|
||
}
|
||
}
|
||
|
||
#endregion
|
||
|
||
|
||
DataBaseOpration.OprationSqlDAL db = DB_DLL.GetInstance();
|
||
#region 分页获取用户表列表
|
||
|
||
public List<UserModel> GetUsersListByPage(string strWhere, string orderby, int startIndex, int endIndex, out int recordCount)
|
||
{
|
||
|
||
try
|
||
{
|
||
|
||
StringBuilder strSql = new StringBuilder();
|
||
strSql.Append("SELECT * FROM ( ");
|
||
strSql.Append(" SELECT ROW_NUMBER() OVER (");
|
||
if (!string.IsNullOrEmpty(orderby.Trim()))
|
||
{
|
||
strSql.Append("order by T." + orderby);
|
||
}
|
||
else
|
||
{
|
||
strSql.Append("order by Id asc");
|
||
}
|
||
strSql.Append(")AS Row, T.* from Users T WITH(NOLOCK) ");
|
||
if (!string.IsNullOrEmpty(strWhere.Trim()))
|
||
{
|
||
strSql.Append(" WHERE 1=1 " + strWhere);
|
||
}
|
||
strSql.Append(" ) TT");
|
||
strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
|
||
List<UserModel> list = ModelConvertHelper<UserModel>.ConvertToList(db.GetsqlForDT(strSql.ToString()));
|
||
|
||
strSql.Remove(0, strSql.Length);
|
||
strSql.Append("SELECT COUNT(*) FROM Users AS T ");
|
||
if (!string.IsNullOrEmpty(strWhere.Trim()))
|
||
{
|
||
strSql.AppendFormat(" WHERE 1=1 {0}", strWhere);
|
||
}
|
||
|
||
object obj = db.GetsqlForDT(strSql.ToString()).Rows[0][0];
|
||
if (obj != null)
|
||
recordCount = Convert.ToInt32(obj);
|
||
else
|
||
recordCount = 0;
|
||
|
||
return list;
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
LogHelper.WriteLog(GetType(), ex.Message);
|
||
recordCount = 0;
|
||
return new List<Model.UserModel>();
|
||
}
|
||
|
||
}
|
||
#endregion
|
||
|
||
|
||
#region 分页获取用户表列表
|
||
|
||
public List<UserModel> GetUsersListByPageByMySql(string strWhere, string orderby, int startIndex, int endIndex, out int recordCount)
|
||
{
|
||
|
||
try
|
||
{
|
||
StringBuilder strSql = new StringBuilder();
|
||
strSql.Append("SELECT users.* from users inner join(");
|
||
strSql.Append("select id from users ");
|
||
if (!string.IsNullOrEmpty(strWhere.Trim()))
|
||
{
|
||
|
||
strSql.Append(" WHERE 1=1 " + strWhere);
|
||
}
|
||
else
|
||
{
|
||
strSql.Append(" WHERE 1=1 ");
|
||
}
|
||
strSql.Append(")AS tmp on tmp.id=users.id ");
|
||
if (!string.IsNullOrEmpty(orderby.Trim()))
|
||
{
|
||
|
||
strSql.Append(" order by " + orderby);
|
||
}
|
||
else
|
||
{
|
||
strSql.Append(" order by block asc");
|
||
}
|
||
|
||
strSql.AppendFormat(" limit {0},{1}", startIndex, endIndex);
|
||
DataTable dt = db.GetsqlForDT(strSql.ToString());
|
||
List <UserModel> list = ModelConvertHelper<UserModel>.ConvertToList(dt);
|
||
|
||
strSql.Remove(0, strSql.Length);
|
||
strSql.Append("SELECT COUNT(*) FROM users AS T ");
|
||
if (!string.IsNullOrEmpty(strWhere.Trim()))
|
||
{
|
||
strSql.AppendFormat(" WHERE 1=1 {0}", strWhere);
|
||
}
|
||
|
||
object obj = db.GetsqlForDT(strSql.ToString()).Rows[0][0];
|
||
if (obj != null)
|
||
recordCount = Convert.ToInt32(obj);
|
||
else
|
||
recordCount = 0;
|
||
|
||
return list;
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
LogHelper.WriteLog(GetType(), ex.Message);
|
||
recordCount = 0;
|
||
return new List<Model.UserModel>();
|
||
}
|
||
|
||
}
|
||
#endregion
|
||
|
||
|
||
|
||
|
||
#region 根据条件删除用户
|
||
public bool DeleteUser(string strwhere)
|
||
{
|
||
string sql = "delete FROM Users where Id is not null ";
|
||
try
|
||
{
|
||
if (strwhere != string.Empty && strwhere != null)
|
||
{
|
||
sql += " " + strwhere;
|
||
}
|
||
|
||
}
|
||
catch (Exception)
|
||
{
|
||
|
||
throw;
|
||
}
|
||
long dt = db.DeleteSql(sql);
|
||
if (dt > 0)
|
||
{
|
||
return true;
|
||
}
|
||
else
|
||
{
|
||
return false;
|
||
}
|
||
|
||
}
|
||
#endregion
|
||
|
||
#region 检测用户
|
||
public string CheckUser(string username, string password)
|
||
{
|
||
string sql = "select LoginName,Role_Id from Users where loginname='" + username + "' and PassWord= '" + password + "'";
|
||
DataTable dt = db.GetsqlForDT(sql);
|
||
if (dt.Rows.Count == 0)
|
||
{
|
||
return "";
|
||
}
|
||
else
|
||
{
|
||
|
||
string loginname = dt.Rows[0]["LoginName"].ToString();
|
||
string Role_Id = dt.Rows[0]["Role_Id"].ToString();
|
||
return loginname + "," + Role_Id;
|
||
}
|
||
|
||
|
||
}
|
||
#endregion
|
||
|
||
#region 添加用户
|
||
public bool AddUser(string id, string username, string password, string loginName, string admin,string lightcolor="")
|
||
{
|
||
if (admin == "管理员")
|
||
{
|
||
admin = "1";
|
||
}
|
||
else if (admin == "普通用户")
|
||
{
|
||
admin = "2";
|
||
}
|
||
else if (admin == "小组组长")
|
||
{
|
||
admin = "3";
|
||
}
|
||
string sql = "insert into Users(UserName,PassWord,LoginName,Role_Id,lightcolor) values('{0}','{1}','{2}','{3}','{4}')";
|
||
sql = string.Format(sql, username, password, loginName, admin, lightcolor);
|
||
int x = db.InsertSql(sql);
|
||
if (x > 0)
|
||
{
|
||
return true;
|
||
}
|
||
else
|
||
{
|
||
return false;
|
||
}
|
||
|
||
}
|
||
#endregion
|
||
|
||
#region 根据条件查询用户
|
||
public DataTable SelectUser(string userId, string userName, string loginName)
|
||
{
|
||
string sql = "select Id,UserName,PassWord,LoginName,Role_Id from Users where id is not null ";
|
||
try
|
||
{
|
||
if (userId != string.Empty && userId != null)
|
||
{
|
||
sql += "and Id='" + userId + "'";
|
||
}
|
||
if (userName != string.Empty && userName != null)
|
||
{
|
||
sql += "and UserName like '%" + userName + "%'";
|
||
}
|
||
if (loginName != string.Empty && loginName != null)
|
||
{
|
||
sql += "and LoginName like '%" + loginName + "%'";
|
||
}
|
||
}
|
||
catch (Exception)
|
||
{
|
||
|
||
throw;
|
||
}
|
||
DataTable dt = db.GetsqlForDT(sql);
|
||
return dt;
|
||
}
|
||
#endregion
|
||
|
||
#region 查询用户名有无
|
||
public int SelectUserName(string username, string Id)
|
||
{
|
||
string sql = "select count(*) from Users where UserName='" + username + "'";
|
||
DataTable dt = db.GetsqlForDT(sql);
|
||
int x = Convert.ToInt32
|
||
(dt.Rows[0][0]);
|
||
string sql2 = "select count(*) from Users where Id='" + Id + "'";
|
||
DataTable dt2 = db.GetsqlForDT(sql2);
|
||
int y = Convert.ToInt32
|
||
(dt2.Rows[0][0]);
|
||
|
||
if (x > 0 && y > 0)
|
||
{
|
||
return 1;
|
||
}
|
||
else if (x > 0 && y == 0)
|
||
{
|
||
return 2;
|
||
}
|
||
else if (y > 0 && x == 0)
|
||
{
|
||
return 3;
|
||
}
|
||
else
|
||
{
|
||
return 4;
|
||
}
|
||
}
|
||
#endregion
|
||
|
||
#region 查询用户名有无2
|
||
public int SelectUserName2(string Id)
|
||
{
|
||
|
||
string sql2 = "select count(*) from Users where Id='" + Id + "'";
|
||
DataTable dt2 = db.GetsqlForDT(sql2);
|
||
int y = Convert.ToInt32
|
||
(dt2.Rows[0][0]);
|
||
|
||
if (y > 0)
|
||
{
|
||
return 1;
|
||
}
|
||
else
|
||
{
|
||
return 2;
|
||
}
|
||
}
|
||
#endregion
|
||
|
||
|
||
|
||
#region 获取人员的拣货总和
|
||
public DataTable GetUsernameNumber(string str)
|
||
{
|
||
DataTable dt = db.GetsqlForDT(str);
|
||
return dt;
|
||
}
|
||
#endregion
|
||
|
||
#region 获取人员的拣货次数
|
||
public DataTable GetUsernameCount(string str)
|
||
{
|
||
DataTable dt = db.GetsqlForDT(str);
|
||
return dt;
|
||
}
|
||
#endregion
|
||
|
||
#region 分页获取用户表列表
|
||
|
||
public List<AddressstorageModel> GetPerformanceByPage(string strWhere, string orderby, int startIndex, int endIndex, out int recordCount)
|
||
{
|
||
|
||
//定义一个方法显示用户和货位的列表,存储在list中
|
||
try
|
||
{
|
||
//GetUsernameLocation();
|
||
StringBuilder strSql = new StringBuilder();
|
||
strSql.Append("SELECT * FROM ( ");
|
||
strSql.Append(" SELECT ROW_NUMBER() OVER (");
|
||
if (!string.IsNullOrEmpty(orderby.Trim()))
|
||
{
|
||
strSql.Append("order by T." + orderby);
|
||
}
|
||
else
|
||
{
|
||
strSql.Append("order by Id asc");
|
||
}
|
||
strSql.Append(")AS Row, T.* from Users T WITH(NOLOCK) ");
|
||
if (!string.IsNullOrEmpty(strWhere.Trim()))
|
||
{
|
||
strSql.Append(" WHERE 1=1 " + strWhere);
|
||
}
|
||
strSql.Append(" ) TT");
|
||
strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
|
||
List<AddressstorageModel> list = ModelConvertHelper<AddressstorageModel>.ConvertToList(db.GetsqlForDT(strSql.ToString()));
|
||
|
||
strSql.Remove(0, strSql.Length);
|
||
strSql.Append("SELECT COUNT(*) FROM Users AS T ");
|
||
if (!string.IsNullOrEmpty(strWhere.Trim()))
|
||
{
|
||
strSql.AppendFormat(" WHERE 1=1 {0}", strWhere);
|
||
}
|
||
|
||
object obj = db.GetsqlForDT(strSql.ToString());
|
||
if (obj != null)
|
||
recordCount = Convert.ToInt32(obj);
|
||
else
|
||
recordCount = 0;
|
||
|
||
return list;
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
LogHelper.WriteLog(GetType(), ex.Message);
|
||
recordCount = 0;
|
||
return new List<Model.AddressstorageModel>();
|
||
}
|
||
|
||
}
|
||
#endregion
|
||
|
||
#region 绑定用户
|
||
public bool AddUserArea(string Id, string username, string password, string loginName, string admin, string Area)
|
||
{
|
||
username = Id;
|
||
//loginName = Id;
|
||
//判断block是否有，有则删除
|
||
DataTable dc = db.GetsqlForDT("select * from Users where block='" + Area + "'");
|
||
if (dc.Rows.Count > 0)
|
||
{
|
||
string sql = string.Format("update Users set block='{0}',username='{1}',password='{2}',loginname='{3}',role_id='{4}',area='{5}' where block='{6}' ", Area, username, password, loginName, admin, Area, Area);
|
||
db.UpdateSql(sql);
|
||
return true;
|
||
}
|
||
else
|
||
{
|
||
string sql = "insert into Users(Id,UserName,PassWord,LoginName,Role_Id,Area,block) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}')";
|
||
sql = string.Format(sql, Id, username, password, loginName, admin, Area, Area);
|
||
int x = db.InsertSql(sql);
|
||
if (x > 0)
|
||
{
|
||
return true;
|
||
}
|
||
else
|
||
{
|
||
return false;
|
||
}
|
||
}
|
||
|
||
|
||
}
|
||
#endregion
|
||
|
||
|
||
#region 员工录入修改
|
||
public bool UserInputModify(string area, string id, string area_pre, string Id_pre)
|
||
{
|
||
string sql = "update Users set Area='{0}',Block='{1}' where Id='" + Id_pre + "' and Area='" + area_pre + "'";
|
||
sql = string.Format(sql, area, area);
|
||
long x = db.UpdateSql(sql);
|
||
if (x > 0)
|
||
{
|
||
return true;
|
||
}
|
||
else
|
||
{
|
||
return false;
|
||
}
|
||
|
||
}
|
||
#endregion
|
||
|
||
|
||
|
||
|
||
#region 获取订单信息
|
||
public DataTable getUserColor(string usercode)
|
||
{
|
||
try
|
||
{
|
||
string sql = string.Format("select lightcolor,username from users where loginname='{0}' ",
|
||
usercode
|
||
);
|
||
return db.GetsqlForDT(sql);
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
LogHelper.WriteLogInfo("获取工号异常：" + ex.Message);
|
||
return null;
|
||
}
|
||
}
|
||
|
||
#endregion
|
||
|
||
}
|
||
}
|