public IEnumerable <base_sbxx> Search(DeviceQueryParm parm, out int resultcount)
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append("select sbbh, sbmc, gcdm, scx, gwh, sblx, txfs, ip, sfky, sflj, bz, lrr, lrsj, com, port from base_sbxx where 1=1 ");
OracleDynamicParameters p = new OracleDynamicParameters();
if (!string.IsNullOrEmpty(parm.keyword))
{
sql.Append(" and (sbbh like :key or sbmc like :key) ");
p.Add(":key", "%" + parm.keyword + "%", OracleMappingType.Varchar2, System.Data.ParameterDirection.Input);
}
if (parm.explist.Count > 0)
{
sql.Append(" and ");
sql.Append(Util.Tool.ComQueryExp(parm.explist));
}
using (var conn = new OraDBHelper(constr).Conn)
{
var q = conn.Query <base_sbxx>(sql.ToString(), p)
.OrderBy(t => t.sbbh)
.ToPagedList(parm.pageindex, parm.pagesize);
resultcount = q.TotalItemCount;
return(q);
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
public IEnumerable <zxjc_t_jstc> Search(sys_page parm, out int resultcount)
{
try
{
OracleDynamicParameters p = new OracleDynamicParameters();
StringBuilder sql = new StringBuilder();
sql.Append("select jtid,jcbh,jcmc,jcms,wjlj,jwdx,scry,scpc,scsj,yxqx1,yxqx2,gcdm,fp_flg,fp_sj,fpr,wjfl,scx from zxjc_t_jstc where 1=1 ");
if (!string.IsNullOrEmpty(parm.keyword))
{
sql.Append(" and (jcmc like :key or jcbh like :key or jcms like :key) ");
p.Add(":key", "%" + parm.keyword + "%", OracleMappingType.Varchar2, System.Data.ParameterDirection.Input);
}
if (parm.explist.Count > 0)
{
sql.Append(" and ");
sql.Append(Tool.ComQueryExp(parm.explist));
}
using (var conn = new OraDBHelper(constr).Conn)
{
var q = conn.Query <zxjc_t_jstc>(sql.ToString(), p)
.OrderByDescending(t => t.jcbh)
.ToPagedList(parm.pageindex, parm.pagesize);
resultcount = q.TotalItemCount;
return(q);
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
public base_sbxx Add(List <base_sbxx> entity)
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append(" insert into base_sbxx(sbbh, sbmc, gcdm, scx, gwh, sblx, txfs, ip, sfky, sflj, bz, lrr, lrsj, com, port)");
sql.Append(" values");
sql.Append(" (:sbbh, :sbmc, :gcdm, :scx, :gwh, :sblx, :txfs, :ip, :sfky, :sflj, :bz, :lrr, sysdate, :com, :port)");
using (var conn = new OraDBHelper(constr).Conn)
{
int ret = conn.Execute(sql.ToString(), entity.ToArray());
if (ret > 0)
{
return(new base_sbxx());
}
else
{
return(null);
}
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
public zxjc_ryxx_jn Add(zxjc_ryxx_jn entity)
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append("insert into zxjc_ryxx_jn(gcdm, user_code, jnbh, jnxx, scx, gwh, sfhg, lrr, lrsj, jnfl, jnsj) ");
sql.Append(" values(:gcdm,:user_code,:jnbh,:jnxx,:scx,:gwh,:sfhg,:lrr,sysdate,:jnfl,:jnsj) ");
OracleDynamicParameters p = new OracleDynamicParameters();
p.Add(":gcdm", entity.gcdm, OracleMappingType.Varchar2, System.Data.ParameterDirection.Input);
p.Add(":user_code", entity.user_code, OracleMappingType.Varchar2, System.Data.ParameterDirection.Input);
p.Add(":jnbh", entity.jnbh, OracleMappingType.Varchar2, System.Data.ParameterDirection.Input);
p.Add(":jnxx", entity.jnxx, OracleMappingType.Varchar2, System.Data.ParameterDirection.Input);
p.Add(":scx", entity.scx, OracleMappingType.Varchar2, System.Data.ParameterDirection.Input);
p.Add(":gwh", entity.gwh, OracleMappingType.Varchar2, System.Data.ParameterDirection.Input);
p.Add(":sfhg", entity.sfhg, OracleMappingType.Varchar2, System.Data.ParameterDirection.Input);
p.Add(":lrr", entity.lrr, OracleMappingType.Varchar2, System.Data.ParameterDirection.Input);
p.Add(":jnfl", entity.jnfl, OracleMappingType.Varchar2, System.Data.ParameterDirection.Input);
p.Add(":jnsj", entity.jnsj, OracleMappingType.Varchar2, System.Data.ParameterDirection.Input);
using (var conn = new OraDBHelper(constr).Conn)
{
conn.Execute(sql.ToString(), p);
return(entity);
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
public bool Delete(int id)
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append("delete from sys_role where id=:id");
using (var conn = new OraDBHelper().Conn)
{
conn.Open();
var p = new { id = id };
using (var trans = conn.BeginTransaction())
{
int r1 = conn.Execute(sql.ToString(), p, transaction: trans);
int r2 = conn.Execute("delete from sys_user_role where roleid = :id", p, transaction: trans);
int r3 = conn.Execute("delete from sys_role_menu where roleid = :id", p, transaction: trans);
trans.Commit();
return(r1 > 0 ? true : false);
}
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
//public override int Modify(zxjc_djxx entity)
//{
// try
// {
// StringBuilder sql = new StringBuilder();
// sql.Append("update zxjc_djxx set gcdm=:gcdm,");
// sql.Append(" scx=:scx,");
// sql.Append(" gwh =:gwh,");
// sql.Append(" jx_no =:jx_no,");
// sql.Append(" status_no =:status_no,");
// sql.Append(" djno =:djno,");
// sql.Append(" djxx =:djxx,");
// sql.Append(" djjg =:djjg,");
// sql.Append(" bz =:bz where id=:id ");
// using (var conn = new OraDBHelper(constr).Conn)
// {
// return conn.Execute(sql.ToString(), entity);
// }
// }
// catch (Exception e)
// {
// log.Error(e.Message);
// throw;
// }
//}
public IEnumerable <zxjc_djxx> Search(sys_page parm, out int resultcount)
{
try
{
OracleDynamicParameters p = new OracleDynamicParameters();
StringBuilder sql = new StringBuilder();
sql.Append("select id,gcdm,scx,gwh,jx_no,status_no,djno,djxx,djjg,bz,lrr,lrsj from zxjc_djxx where 1=1 ");
if (!string.IsNullOrEmpty(parm.keyword))
{
sql.Append(" and (djxx like :key or jx_no like :key or djno like :key) ");
p.Add(":key", "%" + parm.keyword + "%", OracleMappingType.Varchar2, System.Data.ParameterDirection.Input);
}
if (parm.explist.Count > 0)
{
sql.Append(" and ");
sql.Append(Tool.ComQueryExp(parm.explist));
}
using (var conn = new OraDBHelper(constr).Conn)
{
var q = conn.Query <zxjc_djxx>(sql.ToString(), p)
.OrderBy(t => t.djno)
.ToPagedList(parm.pageindex, parm.pagesize);
resultcount = q.TotalItemCount;
return(q);
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
public int RoleStatus(List <int> ids, int status, int upuerid)
{
try
{
int t = status == 0 ? 1 : 0;
List <dynamic> p = new List <dynamic>();
foreach (var item in ids)
{
p.Add(new { status = status, upuser = upuerid, id = item, t = t });
}
StringBuilder sql = new StringBuilder();
sql.Append("update sys_role set status = :status, ");
sql.Append(" updatetime=sysdate,");
sql.Append(" updateuser=:upuser,");
sql.Append(" updateusername=(select name from sys_user where id=:upuser) ");
sql.Append(" where id = :id ");
sql.Append(" and status = :t ");
using (var conn = new OraDBHelper().Conn)
{
return(conn.Execute(sql.ToString(), p.ToArray()));
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
public sys_role Add(sys_role entity)
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append("insert into sys_role (id,status,title,code,adduser,addtime,addusername) values (SEQ_ROLEID.nextval,:status,:title,:code,:adduser,sysdate,(select name from sys_user where id = :adduser)) returning id into :id ");
OracleDynamicParameters p = new OracleDynamicParameters();
p.Add(":status", entity.status, OracleMappingType.Int32, ParameterDirection.Input);
p.Add(":title", entity.title, OracleMappingType.NVarchar2, ParameterDirection.Input);
p.Add(":code", MaxCode(), OracleMappingType.NVarchar2, ParameterDirection.Input);
p.Add(":adduser", entity.adduser, OracleMappingType.Int32, ParameterDirection.Input);
p.Add(":addusername", entity.addusername, OracleMappingType.NVarchar2, ParameterDirection.Input); p.Add(":id", null, OracleMappingType.Int32, ParameterDirection.ReturnValue);
using (var db = new OraDBHelper())
{
int cnt = db.Conn.Execute(sql.ToString(), p);
entity.id = p.Get <int>(":id");
return(entity);
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
/// <summary>
/// 人员信息
/// </summary>
/// <param name="key"></param>
/// <returns></returns>
public IEnumerable <sec_users> PersonList(string key)
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT comp_no,");
sql.Append(" user_code,");
sql.Append(" user_name,");
sql.Append(" user_type,");
sql.Append(" depart_no,");
sql.Append(" gwxx,");
sql.Append(" pass_word,");
sql.Append(" bz,");
sql.Append(" class_no,");
sql.Append(" tsqx,");
sql.Append(" scx,");
sql.Append(" lx");
sql.Append(" FROM sec_users where 1=1 ");
if (!string.IsNullOrEmpty(key))
{
sql.Append(" and (user_code like :key or user_name like :key or scx like :key or class_no like :key )");
}
using (var conn = new OraDBHelper(constr).Conn)
{
return(conn.Query <sec_users>(sql.ToString(), new { key = "%" + key + "%" }));
}
}
catch (Exception)
{
throw;
}
}
public int Save_RoleMenus(int roleid, List <int> menuids)
{
try
{
int ret = 0;
List <dynamic> vals = new List <dynamic>();
foreach (var item in menuids)
{
vals.Add(new { roleid = roleid, menuid = item });
}
StringBuilder sql = new StringBuilder();
sql.Append("insert into sys_role_menu(id,roleid,menuid) \r\n");
sql.Append("values \r\n");
sql.Append(" (SEQ_ROLEMENU_ID.nextval,:roleid,:menuid) \r\n");
using (var conn = new OraDBHelper().Conn)
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
conn.Execute("delete from sys_role_menu where roleid =:roleid", new { roleid = roleid }, trans);
ret = conn.Execute(sql.ToString(), vals.ToArray(), trans);
trans.Commit();
return(ret);
}
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
public IEnumerable <sys_role> Find(sys_page parm, out int resultcount)
{
try
{
OracleDynamicParameters p = new OracleDynamicParameters();
StringBuilder sql = new StringBuilder();
sql.Append(" select id,code,status,title,adduser,addtime,addusername from sys_role where 1=1 ");
if (!string.IsNullOrEmpty(parm.keyword))
{
sql.Append(" and (title like :keyword or code like :keyword) ");
p.Add(":keyword", "%" + parm.keyword + "%", OracleMappingType.NVarchar2, ParameterDirection.Input);
}
using (var db = new OraDBHelper())
{
var query = db.Conn.Query <sys_role>(sql.ToString(), p)
.OrderByDescending(t => t.id)
.ToPagedList(parm.pageindex, parm.pagesize);
resultcount = query.TotalItemCount;
return(query);
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
public int Modify(zxjc_ryxx_jn entity)
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append("update zxjc_ryxx_jn");
sql.Append(" set gcdm = :gcdm,");
sql.Append(" user_code = :user_code,");
sql.Append(" jnbh = :jnbh,");
sql.Append(" jnxx = :jnxx,");
sql.Append(" scx = :scx,");
sql.Append(" gwh = :gwh,");
sql.Append(" sfhg = :sfhg,");
sql.Append(" jnfl = :jnfl,");
sql.Append(" jnsj = :jnsj");
sql.Append(" where jnbh = :jnbh ");
using (var conn = new OraDBHelper(constr).Conn)
{
return(conn.Execute(sql.ToString(), entity));
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
public zxjc_ryxx_jn Add(List <zxjc_ryxx_jn> entitys)
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append("insert into zxjc_ryxx_jn(gcdm, user_code, jnbh, jnxx, scx, gwh, sfhg, lrr, lrsj, jnfl, jnsj) ");
sql.Append(" values ");
sql.Append("(:gcdm,:user_code,:jnbh,:jnxx,:scx,:gwh,:sfhg,:lrr, sysdate,:jnfl,:jnsj)");
using (var conn = new OraDBHelper(constr).Conn)
{
int cnt = conn.Execute(sql.ToString(), entitys.ToArray());
if (cnt > 0)
{
return(new zxjc_ryxx_jn());
}
else
{
return(null);
}
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
// GET api/values
public async Task <IHttpActionResult> Get()
{
using (var db = new OraDBHelper())
{
var d = db.Conn.QueryAsync("SELECT btj,sblx,xh,bzbb FROM base_btbz where rownum<10");
return(Json(new { list = await d }));
}
}
public sys_menu Add(sys_menu menu)
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append("insert into sys_menu(id,");
sql.Append("title,");
sql.Append("pid,");
sql.Append("icon,");
sql.Append("code,");
sql.Append("path,");
sql.Append("menutype,");
sql.Append("viewpath,");
sql.Append("addtime,");
sql.Append("adduser,");
sql.Append("addusername,comname,");
sql.Append("seq");
sql.Append(")");
sql.Append("values");
sql.Append("(SEQ_MENUID.NEXTVAL,");
sql.Append(":title,");
sql.Append(":pid,");
sql.Append(":icon,");
sql.Append(":code,");
sql.Append(":path,");
sql.Append(":menutype,");
sql.Append(":viewpath,");
sql.Append("sysdate,");
sql.Append(":adduser,");
sql.Append("(select name from sys_user where id = :adduser),:comname,");
sql.Append(":seq");
sql.Append(") returning id into :id");
using (var db = new OraDBHelper())
{
OracleDynamicParameters param = new OracleDynamicParameters();
param.Add(":title", menu.title, OracleMappingType.NVarchar2, ParameterDirection.Input);
param.Add(":pid", menu.pid, OracleMappingType.Int32, ParameterDirection.Input);
param.Add(":icon", menu.icon, OracleMappingType.NVarchar2, ParameterDirection.Input);
param.Add(":code", menu.code, OracleMappingType.NVarchar2, ParameterDirection.Input);
param.Add(":path", menu.path, OracleMappingType.NVarchar2, ParameterDirection.Input);
param.Add(":menutype", menu.menutype, OracleMappingType.NVarchar2, ParameterDirection.Input);
param.Add(":viewpath", menu.viewpath, OracleMappingType.NVarchar2, ParameterDirection.Input);
param.Add(":adduser", menu.adduser, OracleMappingType.Int32, ParameterDirection.Input);
param.Add(":comname", menu.comname, OracleMappingType.NVarchar2, ParameterDirection.Input);
param.Add(":seq", menu.seq, OracleMappingType.Int32, ParameterDirection.Input);
param.Add(":id", null, OracleMappingType.Int32, ParameterDirection.Output);
var ret = db.Conn.Execute(sql.ToString(), param);
var menuid = param.Get <int>(":id");
menu.id = menuid;
return(menu);
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
public sys_role Find(int id)
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append("select ta.id,");
sql.Append(" ta.code,");
sql.Append(" ta.status,");
sql.Append(" ta.title,");
sql.Append(" ta.adduser,");
sql.Append(" ta.addusername,");
sql.Append(" tc.id,");
sql.Append(" tc.pid,");
sql.Append(" tc.title,");
sql.Append(" tc.menutype,");
sql.Append(" tc.code,");
sql.Append(" tc.seq");
sql.Append(" from sys_role ta, sys_role_menu tb, sys_menu tc");
sql.Append(" where ta.id = tb.roleid ");
sql.Append(" and tb.menuid = tc.id ");
sql.Append(" and tc.status = 1 ");
sql.Append(" and ta.status = 1 ");
sql.Append(" and ta.id = :id ");
using (var conn = new OraDBHelper().Conn)
{
Dictionary <int, sys_role> dic_role = new Dictionary <int, sys_role>();
var role_menus = conn.Query <sys_role_menu>("select roleid,menuid,permis from sys_role_menu where roleid=:id", new { id = id });
var query = conn.Query <sys_role, sys_menu, sys_role>(sql.ToString(), (role, menu) =>
{
sys_role roleentity = new sys_role();
if (!dic_role.TryGetValue(role.id, out roleentity))
{
roleentity = role;
roleentity.role_menus = new List <sys_menu>();
dic_role.Add(roleentity.id, roleentity);
}
var permis = role_menus.Where(t => t.menuid == menu.id).Select(t => t.permis).FirstOrDefault();
if (permis != null)
{
menu.menu_permission = JsonConvert.DeserializeObject <sys_permission>(permis);
}
else
{
menu.menu_permission = new sys_permission();
}
roleentity.role_menus.Add(menu);
return(roleentity);
}, new { id = id });
return(query.FirstOrDefault());
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
public IEnumerable <sys_menu> User_Menus(int userid)
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append("select distinct tc.id, \r\n");
sql.Append("tc.title, \r\n");
sql.Append("tc.pid, \r\n");
sql.Append("tc.code, \r\n");
sql.Append("tc.path, \r\n");
sql.Append("tc.viewpath, \r\n");
sql.Append("tc.icon,tc.comname, \r\n");
sql.Append("tc.seq \r\n");
sql.Append("from sys_user_role ta, sys_role_menu tb, sys_menu tc \r\n");
sql.Append("where ta.userid = :userid \r\n");
sql.Append("and ta.roleid = tb.roleid \r\n");
sql.Append("and tb.menuid = tc.id order by tc.pid,tc.seq asc\r\n");
StringBuilder permis_sql = new StringBuilder();
permis_sql.Append("SELECT ta.roleid,ta.menuid,ta.permis FROM sys_role_menu ta,sys_user_role tb ");
permis_sql.Append(" where ta.roleid = tb.roleid");
permis_sql.Append(" and tb.userid = :userid ");
using (var db = new OraDBHelper())
{
List <sys_menu> menulist = new List <sys_menu>();
var list = db.Conn.Query <sys_menu>(sql.ToString(), new { userid = userid });
var permislist = db.Conn.Query <sys_role_menu>(permis_sql.ToString(), new { userid = userid });
foreach (var item in list.Where(t => t.pid == 0))
{
item.menu_permission = Get_UniMenu_Permission(permislist, item.id);
menulist.Add(item);
bool haschild = list.Where(t => t.pid == item.id).Count() > 0 ? true : false;
if (haschild)
{
item.children = Create_Child(permislist, list, item.id);
item.hasChildren = true;
}
else
{
item.children = new List <sys_menu>();
item.hasChildren = false;
}
}
return(menulist);
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
public List <string> FindCodesByPid(int pid)
{
try
{
using (var conn = new OraDBHelper().Conn)
{
return(conn.Query <string>("select code from sys_menu where status = 1 and pid = :pid", new { pid = pid }).ToList());
}
}
catch (Exception)
{
throw;
}
}
public IEnumerable <sys_user> FindUserByName(string key)
{
try
{
using (var conn = new OraDBHelper().Conn)
{
return(conn.Query <sys_user>("select id,status,code,name from sys_user where status = 1 and (name like :key or code like :key)", new { key = "%" + key + "%" }));
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
/// <summary>
/// 机型状态
/// </summary>
/// <param name="jx"></param>
/// <returns></returns>
public IEnumerable <ztbm_new> GetZtList(string jx)
{
try
{
using (var conn = new OraDBHelper(constr).Conn)
{
StringBuilder sql = new StringBuilder();
sql.Append("select distinct cpbm as ztbm from ZTBM_NEW where lower(jx) like :key and rownum < 10 order by cpbm asc");
return(conn.Query <ztbm_new>(sql.ToString(), new { key = "%" + jx.ToLower() + "%" }));
}
}
catch (Exception)
{
throw;
}
}
public string GetSkillNo()
{
try
{
using (var conn = new OraDBHelper(constr).Conn)
{
int skillid = conn.ExecuteScalar <int>("SELECT seq_skill_id.nextval FROM dual");
return("JN" + skillid.ToString().PadLeft(4, '0'));
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
/// <summary>
/// 点检编号
/// </summary>
public string GetDJNo()
{
try
{
using (var conn = new OraDBHelper(constr).Conn)
{
var no = conn.ExecuteScalar <int>("select seq_pointcheck_no.nextval from dual");
return("DJ" + no.ToString().PadLeft(4, '0'));
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
/// <summary>
/// 工厂
/// </summary>
/// <returns></returns>
public IEnumerable <base_gcxx> FactoryList()
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append("select gcdm, gcmc,gsxx,gsmc from base_gcxx");
using (var conn = new OraDBHelper(constr).Conn)
{
return(conn.Query <base_gcxx>(sql.ToString()));
}
}
catch (Exception)
{
throw;
}
}
public int Delete(List <int> ids)
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append("delete from sys_menu where id in :ids ");
using (var db = new OraDBHelper())
{
return(db.Conn.Execute(sql.ToString(), new { ids = ids }));
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
public string MaxCode()
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append("select LPad(max(code) + 1, 2, '0') as maxcode from Sys_Role");
using (var conn = new OraDBHelper().Conn)
{
return(conn.ExecuteScalar <string>(sql.ToString()));
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
public int DisableUser(List <int> ids)
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append("update sys_user set status = 0 where id in :ids and status = 1 ");
using (var conn = new OraDBHelper().Conn)
{
return(conn.Execute(sql.ToString(), new { ids = ids }));
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
public IEnumerable <zxjc_ryxx_jn> Search(SkillQueryParm parm, out int resultcount)
{
try
{
OracleDynamicParameters p = new OracleDynamicParameters();
StringBuilder sql = new StringBuilder();
sql.Append(" SELECT ta.gcdm, ta.user_code,(select user_name from sec_users where user_code = ta.user_code) as user_name, ta.jnbh, ta.jnxx, ta.scx, ta.gwh,(select work_name from ZXJC_GXZD where work_no = ta.gwh) as gwmc, ta.sfhg, ta.lrr, ta.lrsj, ta.jnfl, ta.jnsj");
sql.Append(" FROM zxjc_ryxx_jn ta where 1 = 1 ");
if (!string.IsNullOrEmpty(parm.keyword))
{
sql.Append(" and (user_code like :key or jnbh like :key) ");
p.Add(":key", "%" + parm.keyword + "%", OracleMappingType.Varchar2, System.Data.ParameterDirection.Input);
}
if (parm.explist.Count > 0)
{
sql.Append(" and ");
foreach (var item in parm.explist)
{
sql.Append($"{item.left}");
if (item.oper == "like")
{
sql.Append($" {item.colname} {item.oper} '%{item.value}%' {item.logic} ");
}
else
{
sql.Append($" {item.colname} {item.oper} '{item.value}' {item.logic} ");
}
sql.Append($"{item.right}");
}
}
using (var conn = new OraDBHelper(constr).Conn)
{
var q = conn.Query <zxjc_ryxx_jn>(sql.ToString(), p)
.OrderBy(t => t.jnbh)
.ToPagedList(parm.pageindex, parm.pagesize);
resultcount = q.TotalItemCount;
return(q);
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
public int Modify(base_sbxx entity)
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append("update base_sbxx ");
sql.Append(" set sbbh=:sbbh,");
sql.Append(" sbmc=:sbmc,");
sql.Append(" gcdm=:gcdm,");
sql.Append(" scx=:scx,");
sql.Append(" gwh=:gwh,");
sql.Append(" sblx=:sblx,");
sql.Append(" txfs=:txfs,");
sql.Append(" ip=:ip,");
sql.Append(" sfky=:sfky,");
sql.Append(" sflj=:sflj,");
sql.Append(" bz=:bz,");
sql.Append(" com=:com,");
sql.Append(" port =:port where sbbh = :sbbh ");
OracleDynamicParameters p = new OracleDynamicParameters();
p.Add(":sbbh", entity.sbbh, OracleMappingType.Varchar2, System.Data.ParameterDirection.Input);
p.Add(":sbmc", entity.sbmc, OracleMappingType.Varchar2, System.Data.ParameterDirection.Input);
p.Add(":gcdm", entity.gcdm, OracleMappingType.Varchar2, System.Data.ParameterDirection.Input);
p.Add(":scx", entity.scx, OracleMappingType.Varchar2, System.Data.ParameterDirection.Input);
p.Add(":gwh", entity.gwh, OracleMappingType.Varchar2, System.Data.ParameterDirection.Input);
p.Add(":sblx", entity.sblx, OracleMappingType.Varchar2, System.Data.ParameterDirection.Input);
p.Add(":txfs", entity.txfs, OracleMappingType.Varchar2, System.Data.ParameterDirection.Input);
p.Add(":ip", entity.ip, OracleMappingType.Varchar2, System.Data.ParameterDirection.Input);
p.Add(":sfky", entity.sfky, OracleMappingType.Varchar2, System.Data.ParameterDirection.Input);
p.Add(":sflj", entity.sflj, OracleMappingType.Varchar2, System.Data.ParameterDirection.Input);
p.Add(":bz", entity.bz, OracleMappingType.Varchar2, System.Data.ParameterDirection.Input);
p.Add(":com", entity.com, OracleMappingType.Varchar2, System.Data.ParameterDirection.Input);
p.Add(":port", entity.port, OracleMappingType.Varchar2, System.Data.ParameterDirection.Input);
using (var conn = new OraDBHelper(constr).Conn)
{
return(conn.Execute(sql.ToString(), p));
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
public sys_menu Find(int id)
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append("select id,pid,title,code,path,viewpath from sys_menu where id = :id ");
using (var db = new OraDBHelper())
{
var query = db.Conn.Query <sys_menu>(sql.ToString(), new { id = id });
return(query.FirstOrDefault());
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
public int ChangePwd(int userid, string pwd)
{
try
{
string pwd1 = Tool.Str2MD5(pwd);
StringBuilder sql = new StringBuilder();
sql.Append("update sys_user set pwd=:pwd where id=:userid");
using (var conn = new OraDBHelper().Conn)
{
return(conn.Execute(sql.ToString(), new { userid = userid, pwd = pwd1 }));
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
