public List<UserInfo> GetUserListByOrgCode(string orgCode)
{
string sql = "SELECT [UserUID],[FullName],[IsManager],[IsSystem] FROM [UserInfos] WHERE OrgCode=@OrgCode Order By Sequence";
SqlParameter sp = new SqlParameter("@OrgCode", orgCode);
List<UserInfo> list = new List<UserInfo>();
using (IDataReader reader = base.ExcuteDataReader(sql, sp))
{
while (reader.Read())
{
UserInfo user = new UserInfo();
user.UserUID = reader.GetString(0);
user.FullName = reader.GetString(1);
user.IsManager =reader.IsDBNull(2)?false: reader.GetBoolean(2);
user.IsSystem = reader.IsDBNull(3) ? false : reader.GetBoolean(3);
user.IsNew = false;
list.Add(user);
}
}
return list;
}
public UserInfo GetUserInfo(string UserId)
{
string sql = @"SELECT A.[UserUID],A.[FullName],A.[Password],
A.[OrgCode],A.[OrgName],A.[IsManager],A.[IsSystem],
A.[Sequence],A.[AccountState],A.[LastUpdateUserUID],
A.[LastUpdateUserName],A.[LastUpdateTime],B.UnitCode,B.UnitName
FROM [UserInfos] A LEFT JOIN Organizations B ON A.OrgCode=B.OrgCode
WHERE A.[UserUID]=@UserUID";
SqlParameter p = new SqlParameter("@UserUID", UserId);
UserInfo item = null;
using (IDataReader reader = base.ExcuteDataReader(sql, p))
{
if (reader.Read())
{
item = new UserInfo();
item.UserUID = reader.GetString(0);
item.FullName = reader.GetString(1);
item.Password = reader.GetString(2);
item.OrgCode = reader.GetString(3);
if (!reader.IsDBNull(4))
{
item.OrgName = reader.GetString(4);
}
item.IsManager = reader.GetBoolean(5);
item.IsSystem = reader.GetBoolean(6);
item.Sequence = reader.GetInt32(7);
item.AccountState = reader.GetByte(8);
item.LastUpdateUserUID = reader.GetString(9);
item.LastUpdateUserName = reader.GetString(10);
item.LastUpdateTime = reader.GetDateTime(11);
item.UnitCode = reader.IsDBNull(12) ? null : reader.GetString(12);
item.UnitName = reader.IsDBNull(13) ? null : reader.GetString(13);
}
}
return item;
}
public PagedList<UserInfo> QueryOrgUserList(PageView view, string orgCode)
{
string where = " AND OrgCode='" + orgCode + "'";
StoredProcedure sp = StoredProcedures.SP_PAGESELECT(where, view.PageSize, view.PageIndex
, "UserInfos", "[UserUID],[FullName],[Password],[OrgCode],[OrgName],[Sequence],[AccountState],[LastUpdateUserUID],[LastUpdateUserName],[LastUpdateTime]"
, "[UserUID]", "");
var pl = new PagedList<UserInfo>();
pl.DataList = new List<UserInfo>();
using (IDataReader dr = base.SPExecuteDataReader(sp))
{
while (dr.Read())
{
UserInfo u = new UserInfo();
u.UserUID = dr.IsDBNull(0) ? null : dr.GetString(0);
u.FullName = dr.IsDBNull(1) ? null : dr.GetString(1);
u.Password = dr.IsDBNull(2) ? null : dr.GetString(2);
u.OrgCode = dr.IsDBNull(3) ? null : dr.GetString(3);
u.OrgName = dr.IsDBNull(4) ? null : dr.GetString(4);
u.Sequence = dr.GetInt32(5);
u.AccountState = dr.GetByte(6);
u.LastUpdateUserUID = dr.IsDBNull(7) ? null : dr.GetString(7);
u.LastUpdateUserName = dr.IsDBNull(8) ? null : dr.GetString(8);
u.LastUpdateTime = dr.IsDBNull(9) ? DateTime.MinValue : dr.GetDateTime(9);
u.IsNew = false;
pl.DataList.Add(u);
}
}
if (view.PageIndex == 0)
{
pl.Total = Convert.ToInt32(sp.GetParameterValue(sp.ParamsCount - 1));
}
pl.PageIndex = view.PageIndex;
return pl;
}
public List<UserInfo> QueryTopUserList(string qtext)
{
string sql = "SELECT [UserUID],[FullName],[IsManager],[IsSystem] FROM [UserInfos] WHERE UserUID like '%"+qtext+"%' or FullName like '%"+qtext+"%' order by Sequence";
List<UserInfo> list = new List<UserInfo>();
using (IDataReader reader = base.ExcuteDataReader(sql))
{
while (reader.Read())
{
UserInfo user = new UserInfo();
user.UserUID = reader.GetString(0);
user.FullName = reader.GetString(1);
user.IsManager = reader.IsDBNull(2) ? false : reader.GetBoolean(2);
user.IsSystem = reader.IsDBNull(3) ? false : reader.GetBoolean(3);
user.IsNew = false;
list.Add(user);
}
}
return list;
}
public void Save(UserInfo item)
{
if(item.IsNew)
{
Insert(item);
}
else
{
Update(item);
}
}
public void Update(UserInfo item)
{
if(item.ChangedPropertyCount>0)
{
StringBuilder sqlbuilder = new StringBuilder();
sqlbuilder.Append("UPDATE [UserInfos] SET ");
Dictionary<string,string> cols =new Dictionary<string,string>();
cols.Add("FullName","[FullName]");
cols.Add("Password","[Password]");
cols.Add("OrgCode","[OrgCode]");
cols.Add("OrgName","[OrgName]");
cols.Add("IsManager","[IsManager]");
cols.Add("IsSystem","[IsSystem]");
cols.Add("Sequence","[Sequence]");
cols.Add("AccountState","[AccountState]");
cols.Add("LastUpdateUserUID","[LastUpdateUserUID]");
cols.Add("LastUpdateUserName","[LastUpdateUserName]");
cols.Add("LastUpdateTime","[LastUpdateTime]");
int i = 0;
//UPDATE COLUMNS
foreach (string p in item.ChangedPropertyList)
{
if(!cols.ContainsKey(p))
{
continue;
}
if (i > 0)
{
sqlbuilder.Append(",");
}
sqlbuilder.AppendFormat("{0}=@{1}", cols[p], p);
i++;
}
//WHERE;
sqlbuilder.Append(" WHERE [UserUID]=@UserUID");
List<SqlParameter> SPParams = new List<SqlParameter>();
SPParams.Add(new SqlParameter("@UserUID",item.UserUID));
if(item.IsChanged("FullName"))
{
SPParams.Add(new SqlParameter("@FullName",item.FullName));
}
if(item.IsChanged("Password"))
{
SPParams.Add(new SqlParameter("@Password",item.Password));
}
if(item.IsChanged("OrgCode"))
{
SPParams.Add(new SqlParameter("@OrgCode",item.OrgCode));
}
if(item.IsChanged("OrgName"))
{
SPParams.Add(new SqlParameter("@OrgName",item.OrgName));
}
if(item.IsChanged("IsManager"))
{
SPParams.Add(new SqlParameter("@IsManager",item.IsManager));
}
if(item.IsChanged("IsSystem"))
{
SPParams.Add(new SqlParameter("@IsSystem",item.IsSystem));
}
if(item.IsChanged("Sequence"))
{
SPParams.Add(new SqlParameter("@Sequence",item.Sequence));
}
if(item.IsChanged("AccountState"))
{
SPParams.Add(new SqlParameter("@AccountState",item.AccountState));
}
if(item.IsChanged("LastUpdateUserUID"))
{
SPParams.Add(new SqlParameter("@LastUpdateUserUID",item.LastUpdateUserUID));
}
if(item.IsChanged("LastUpdateUserName"))
{
SPParams.Add(new SqlParameter("@LastUpdateUserName",item.LastUpdateUserName));
}
if(item.IsChanged("LastUpdateTime"))
{
SPParams.Add(new SqlParameter("@LastUpdateTime",item.LastUpdateTime));
}
base.ExecuteNonQuery(sqlbuilder.ToString(), SPParams.ToArray());
}
}
public List<UserInfo> QueryAll()
{
string sql ="SELECT [UserUID],[FullName],[Password],[OrgCode],[OrgName],[IsManager],[IsSystem],[Sequence],[AccountState],[LastUpdateUserUID],[LastUpdateUserName],[LastUpdateTime] FROM [UserInfos]";
List<UserInfo> list =new List<UserInfo>();
using(IDataReader reader = base.ExcuteDataReader(sql))
{
while(reader.Read())
{
UserInfo item =new UserInfo();
item.UserUID = reader.GetString(0);
item.FullName = reader.GetString(1);
item.Password = reader.GetString(2);
item.OrgCode = reader.GetString(3);
if(!reader.IsDBNull(4))
{
item.OrgName = reader.GetString(4);
}
item.IsManager = reader.GetBoolean(5);
item.IsSystem = reader.GetBoolean(6);
item.Sequence = reader.GetInt32(7);
item.AccountState = reader.GetByte(8);
item.LastUpdateUserUID = reader.GetString(9);
item.LastUpdateUserName = reader.GetString(10);
item.LastUpdateTime = reader.GetDateTime(11);
list.Add(item);
}
}
return list;
}
public void Insert(UserInfo item)
{
string sql="INSERT INTO [UserInfos] ([UserUID],[FullName],[Password],[OrgCode],[OrgName],[IsManager],[IsSystem],[Sequence],[AccountState],[LastUpdateUserUID],[LastUpdateUserName],[LastUpdateTime]) VALUES (@UserUID,@FullName,@Password,@OrgCode,@OrgName,@IsManager,@IsSystem,@Sequence,@AccountState,@LastUpdateUserUID,@LastUpdateUserName,@LastUpdateTime)";
List<SqlParameter> SPParams = new List<SqlParameter>();
SPParams.Add(new SqlParameter("@UserUID",item.UserUID));
SPParams.Add(new SqlParameter("@FullName",item.FullName));
SPParams.Add(new SqlParameter("@Password",item.Password));
SPParams.Add(new SqlParameter("@OrgCode",item.OrgCode));
SPParams.Add(new SqlParameter("@OrgName",item.OrgName));
SPParams.Add(new SqlParameter("@IsManager",item.IsManager));
SPParams.Add(new SqlParameter("@IsSystem",item.IsSystem));
SPParams.Add(new SqlParameter("@Sequence",item.Sequence));
SPParams.Add(new SqlParameter("@AccountState",item.AccountState));
SPParams.Add(new SqlParameter("@LastUpdateUserUID",item.LastUpdateUserUID));
SPParams.Add(new SqlParameter("@LastUpdateUserName",item.LastUpdateUserName));
SPParams.Add(new SqlParameter("@LastUpdateTime",item.LastUpdateTime));
base.ExecuteNonQuery(sql, SPParams.ToArray());
}
public UserInfo Get(string key)
{
string sql = "SELECT [UserUID],[FullName],[Password],[OrgCode],[OrgName],[IsManager],[IsSystem],[Sequence],[AccountState],[LastUpdateUserUID],[LastUpdateUserName],[LastUpdateTime] FROM [UserInfos] WHERE [UserUID]=@UserUID";
SqlParameter p =new SqlParameter("@UserUID",key);
UserInfo item =null;
using(IDataReader reader = base.ExcuteDataReader(sql,p))
{
if(reader.Read())
{
item =new UserInfo();
item.UserUID = reader.GetString(0);
item.FullName = reader.GetString(1);
item.Password = reader.GetString(2);
item.OrgCode = reader.GetString(3);
if(!reader.IsDBNull(4))
{
item.OrgName = reader.GetString(4);
}
item.IsManager = reader.GetBoolean(5);
item.IsSystem = reader.GetBoolean(6);
item.Sequence = reader.GetInt32(7);
item.AccountState = reader.GetByte(8);
item.LastUpdateUserUID = reader.GetString(9);
item.LastUpdateUserName = reader.GetString(10);
item.LastUpdateTime = reader.GetDateTime(11);
}
}
return item;
}
public ActionResult EditUser(string id, string OrgCode, string OrgName)
{
UserInfo u = null;
if (!string.IsNullOrEmpty(id))
{
u = sysManageService.GetUserInfo(id);
if (u == null)
{
throw new ArgumentException("参数错误", "id");
}
else
{
u.Password = "";
}
}
else
{
u = new UserInfo();
u.OrgCode = OrgCode;
u.OrgName = OrgName;
if (u.OrgCode == AppConfig.RootOrgCode)
{
u.OrgName = AppConfig.RootOrgName;
}
}
return View(u);
}
public JsonResult SaveUserInfo(string id, UserInfo user)
{
JsonReturnMessages msg = new JsonReturnMessages();
try
{
user.IsNew = string.IsNullOrEmpty(id);
if (user.IsNew && string.IsNullOrEmpty(user.Password))
{
throw new BizException("新用户密码不能为空");
}
user.LastUpdateUserUID = base.UserId;
user.LastUpdateUserName = base.CurrentUser.FullName;
user.LastUpdateTime = DateTime.Now;
sysManageService.SaveUserInfo(user);
msg.IsSuccess = true;
msg.Msg = "操作成功";
}
catch (BizException ex)
{
msg.IsSuccess = false;
msg.Msg = ex.Message;
}
catch
{
msg.IsSuccess = false;
msg.Msg = "操作失败,请稍后重试！";
}
return Json(msg);
}