/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(Entity.USERINFO model)
{
StringBuilder strSql = new StringBuilder();
DbAPI dbHelper = new DbAPI();
strSql.Append("update USERINFO set ");
strSql.Append(" F_ACCOUNT = :F_ACCOUNT , ");
strSql.Append(" F_NAME = :F_NAME , ");
strSql.Append(" F_PASSWORD = :F_PASSWORD , ");
strSql.Append(" F_EMAIL = :F_EMAIL , ");
strSql.Append(" F_PHONE = :F_PHONE , ");
strSql.Append(" F_TEL = :F_TEL , ");
strSql.Append(" F_DESCRIPTION = :F_DESCRIPTION , ");
strSql.Append(" F_PHOTO = :F_PHOTO , ");
strSql.Append(" F_ADDRESS = :F_ADDRESS , ");
strSql.Append(" F_REALNAME = :F_REALNAME ");
strSql.Append(" where F_ACCOUNT=:F_ACCOUNT ");
OracleParameter[] parameters =
{
new OracleParameter(":F_ACCOUNT", OracleType.VarChar, 36),
new OracleParameter(":F_NAME", OracleType.VarChar, 64),
new OracleParameter(":F_PASSWORD", OracleType.VarChar, 128),
new OracleParameter(":F_EMAIL", OracleType.VarChar, 128),
new OracleParameter(":F_PHONE", OracleType.VarChar, 16),
new OracleParameter(":F_TEL", OracleType.VarChar, 16),
new OracleParameter(":F_DESCRIPTION", OracleType.NVarChar),
new OracleParameter(":F_PHOTO", OracleType.Blob),
new OracleParameter(":F_ADDRESS", OracleType.NVarChar),
new OracleParameter(":F_REALNAME", OracleType.NVarChar)
};
parameters[0].Value = model.F_ACCOUNT;
parameters[1].Value = model.F_NAME;
parameters[2].Value = model.F_PASSWORD;
parameters[3].Value = model.F_EMAIL;
parameters[4].Value = model.F_PHONE;
parameters[5].Value = model.F_TEL;
parameters[6].Value = model.F_DESCRIPTION;
parameters[7].Value = model.F_PHOTO;
parameters[8].Value = model.F_ADDRESS;
parameters[9].Value = model.F_REALNAME;
int rows = dbHelper.ExecuteNonQuery(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 新增/修改用户
/// </summary>
/// <param name="userinfo">用户实体类</param>
/// <returns>返回状态对象</returns>
public dynamic EditUser(Entity.USERINFO userinfo)
{
var bn = new USERINFO_BN();
bool isOk = string.IsNullOrWhiteSpace(userinfo.F_ACCOUNT)
? bn.AddUserMain(userinfo)
: bn.EditUserMain(userinfo);
return(JsonConvert.DeserializeObject <dynamic>(isOk
? "{\"status\":true,\"msg\":\"\"}"
: "{\"status\":false,\"msg\":\"\"}"));
}
/// <summary>
/// 根据user account得到一个对象实体
/// </summary>
public Entity.USERINFO GetUserinforModel(string account)
{
var strSql = new StringBuilder();
var dbHelper = new DbAPI();
strSql.Append("select a.F_ACCOUNT,a.F_NAME,a.F_PASSWORD,a.F_EMAIL,a.F_PHONE,a.F_TEL,a.F_DESCRIPTION,a.F_ADDRESS,a.F_REALNAME,d.f_name as F_DEPARTMENTCODE,C.Roleinfos");
strSql.Append(" from USERINFO a");
strSql.Append(" left join departmentuser b");
strSql.Append(" on a.f_account = b.f_account");
strSql.Append(" left join departmentinfo d on b.f_departmentcode = d.f_departmentcode");
strSql.Append(" LEFT JOIN (SELECT A.F_ACCOUNT,");
strSql.Append(" LISTAGG(TO_CHAR(B.F_NAME), ',') WITHIN GROUP(ORDER BY A.F_ACCOUNT) AS Roleinfos");
strSql.Append(" FROM USERROLE A");
strSql.Append(" LEFT JOIN ROLEINFO B");
strSql.Append(" ON A.F_ROLECODE = B.F_ROLECODE");
strSql.Append(" GROUP BY A.F_ACCOUNT) C");
strSql.Append(" ON A.F_ACCOUNT = C.F_ACCOUNT");
strSql.Append(" where a.F_ACCOUNT = :F_ACCOUNT and rownum = 1");
OracleParameter[] parameters =
{
new OracleParameter(":F_ACCOUNT", account)
};
dbHelper.OpenConn("");
var ds = dbHelper.GetDataTable(strSql.ToString(), parameters);
if (ds == null || ds.Rows.Count <= 0)
{
return(null);
}
var row0 = ds.Rows[0];
var model = new Entity.USERINFO
{
F_ACCOUNT = row0["F_ACCOUNT"].ToString(),
F_NAME = row0["F_NAME"].ToString(),
F_PASSWORD = row0["F_PASSWORD"].ToString(),
F_EMAIL = row0["F_EMAIL"].ToString(),
F_PHONE = row0["F_PHONE"].ToString(),
F_TEL = row0["F_TEL"].ToString(),
F_DESCRIPTION = row0["F_DESCRIPTION"].ToString(),
F_ADDRESS = row0["F_ADDRESS"].ToString(),
F_REALNAME = row0["F_REALNAME"].ToString(),
DepartmentNames = row0["F_DEPARTMENTCODE"].ToString(),
RoleNames = row0["Roleinfos"].ToString()
};
dbHelper.CloseConn();
return(model);
}
/// <summary>
/// 根据用户名、密码查询用户是否存在
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public DataTable GetUserList(Entity.USERINFO user)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" SELECT F_ACCOUNT,F_NAME,F_REALNAME FROM USERINFO WHERE 1=1 AND F_NAME=:F_NAME AND F_PASSWORD=:F_PASSWORD ");
DbAPI dbHelper = new DbAPI();
List <OracleParameter> list = new List <OracleParameter>();
list.Add(new OracleParameter(":F_NAME", user.F_NAME));
list.Add(new OracleParameter(":F_PASSWORD", user.F_PASSWORD));
dbHelper.OpenConn("");
DataTable dt = dbHelper.GetDataTable(strSql.ToString(), list.ToArray());
dbHelper.CloseConn();
return(dt);
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public Entity.USERINFO GetModel(string F_ACCOUNT)
{
StringBuilder strSql = new StringBuilder();
DbAPI dbHelper = new DbAPI();
strSql.Append("select F_ACCOUNT, F_NAME, F_PASSWORD, F_EMAIL, F_PHONE, F_TEL, F_DESCRIPTION, F_PHOTO, F_ADDRESS, F_REALNAME ");
strSql.Append(" from USERINFO ");
strSql.Append(" where F_ACCOUNT=:F_ACCOUNT ");
OracleParameter[] parameters =
{
new OracleParameter(":F_ACCOUNT", OracleType.VarChar, 36)
};
parameters[0].Value = F_ACCOUNT;
dbHelper.OpenConn("");
Entity.USERINFO model = new Entity.USERINFO();
DataTable ds = dbHelper.GetDataTable(strSql.ToString(), parameters);
if (ds.Rows.Count > 0)
{
model.F_ACCOUNT = ds.Rows[0]["F_ACCOUNT"].ToString();
model.F_NAME = ds.Rows[0]["F_NAME"].ToString();
model.F_PASSWORD = ds.Rows[0]["F_PASSWORD"].ToString();
model.F_EMAIL = ds.Rows[0]["F_EMAIL"].ToString();
model.F_PHONE = ds.Rows[0]["F_PHONE"].ToString();
model.F_TEL = ds.Rows[0]["F_TEL"].ToString();
model.F_DESCRIPTION = ds.Rows[0]["F_DESCRIPTION"].ToString();
if (ds.Rows[0]["F_PHOTO"].ToString() != "")
{
model.F_PHOTO = (byte[])ds.Rows[0]["F_PHOTO"];
}
model.F_ADDRESS = ds.Rows[0]["F_ADDRESS"].ToString();
model.F_REALNAME = ds.Rows[0]["F_REALNAME"].ToString();
dbHelper.CloseConn();
return(model);
}
else
{
return(null);
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public void Add(Entity.USERINFO model)
{
StringBuilder strSql = new StringBuilder();
DbAPI dbHelper = new DbAPI();
strSql.Append("insert into USERINFO(");
strSql.Append("F_ACCOUNT,F_NAME,F_PASSWORD,F_EMAIL,F_PHONE,F_TEL,F_DESCRIPTION,F_PHOTO,F_ADDRESS,F_REALNAME");
strSql.Append(") values (");
strSql.Append(":F_ACCOUNT,:F_NAME,:F_PASSWORD,:F_EMAIL,:F_PHONE,:F_TEL,:F_DESCRIPTION,:F_PHOTO,:F_ADDRESS,:F_REALNAME");
strSql.Append(") ");
OracleParameter[] parameters =
{
new OracleParameter(":F_ACCOUNT", OracleType.VarChar, 36),
new OracleParameter(":F_NAME", OracleType.VarChar, 64),
new OracleParameter(":F_PASSWORD", OracleType.VarChar, 128),
new OracleParameter(":F_EMAIL", OracleType.VarChar, 128),
new OracleParameter(":F_PHONE", OracleType.VarChar, 16),
new OracleParameter(":F_TEL", OracleType.VarChar, 16),
new OracleParameter(":F_DESCRIPTION", OracleType.NVarChar),
new OracleParameter(":F_PHOTO", OracleType.Blob),
new OracleParameter(":F_ADDRESS", OracleType.NVarChar),
new OracleParameter(":F_REALNAME", OracleType.NVarChar)
};
parameters[0].Value = model.F_ACCOUNT;
parameters[1].Value = model.F_NAME;
parameters[2].Value = model.F_PASSWORD;
parameters[3].Value = model.F_EMAIL;
parameters[4].Value = model.F_PHONE;
parameters[5].Value = model.F_TEL;
parameters[6].Value = model.F_DESCRIPTION;
parameters[7].Value = model.F_PHOTO;
parameters[8].Value = model.F_ADDRESS;
parameters[9].Value = model.F_REALNAME;
dbHelper.ExecuteNonQuery(strSql.ToString(), parameters);
}