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 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 int Save_MenuRole(int menuid, List <int> roleids)
{
try
{
int ret = 0;
List <dynamic> mr = new List <dynamic>();
foreach (var item in roleids)
{
mr.Add(new { menuid = menuid, roleid = item });
}
StringBuilder sql = new StringBuilder();
sql.Append("insert into sys_role_menu(id,roleid,menuid) \r\n");
sql.Append("values \r\n");
//sql.Append
using (var conn = new OraDBHelper().Conn)
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
conn.Execute("delete from sys_role_menu where menuid=:menuid", new { menuid = menuid }, trans);
conn.Execute(sql.ToString(), mr.ToArray(), trans);
trans.Commit();
}
}
return(ret);
}
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;
}
}
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 int Add(List <zxjc_djgw> entitys)
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append("insert into zxjc_djgw ");
sql.Append("(gcdm, scx, gwh, jx_no, status_no, djno, djxx, scbz, lrr, lrsj, djlx)");
sql.Append(" values ");
sql.Append(" (:gcdm,:scx,:gwh,:jx_no,:status_no,:djno,:djxx,:scbz,:lrr, sysdate,:djlx)");
using (var conn = new OraDBHelper(constr).Conn)
{
int ret = conn.Execute(sql.ToString(), entitys.ToArray());
if (ret > 0)
{
logservice.InsertLog <zxjc_djgw>(entitys);
}
return(ret);
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
public int LogOut(string token)
{
try
{
var newtoken = new JWTHelper().CreateToken();
StringBuilder sql = new StringBuilder();
sql.Append("update sys_user set token=:newtoken where token=:token");
StringBuilder sql1 = new StringBuilder();
sql1.Append("SELECT id,code,name,token,adduser,addtime,status,headimg FROM sys_user where token=:token");
using (var conn = new OraDBHelper().Conn)
{
var user = conn.Query <sys_user>(sql1.ToString(), new { token = token }).FirstOrDefault();
int cnt = conn.Execute(sql.ToString(), new { token = token, newtoken = newtoken });
if (cnt > 0)
{
LogService logservice = new LogService();
logservice.LogoutLog(user);
}
return(cnt);
}
}
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 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 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 int Save_RoleMenus(List <sys_menu_permission> list)
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append("insert into sys_role_menu(id,roleid,menuid,permis) \r\n");
sql.Append("values \r\n");
sql.Append(" (SEQ_ROLEMENU_ID.nextval,:roleid,:menuid,:permis) \r\n");
List <int> roleid = list.Select(t => t.roleid).ToList();
List <dynamic> p = new List <dynamic>();
foreach (var item in list)
{
var json = JsonConvert.SerializeObject(item.permission);
p.Add(new
{
roleid = item.roleid,
menuid = item.menuid,
permis = json
});
}
using (var conn = new OraDBHelper().Conn)
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
conn.Execute("delete from sys_role_menu where roleid in :roleid", new { roleid = roleid }, trans);
int ret = conn.Execute(sql.ToString(), p, trans);
trans.Commit();
return(ret);
}
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
public int SaveUserRoles(int userid, List <int> roleids)
{
try
{
int ret = 0;
List <dynamic> ur = new List <dynamic>();
foreach (var item in roleids)
{
ur.Add(new { userid = userid, roleid = item });
}
StringBuilder sql = new StringBuilder();
sql.Append("insert into sys_user_role \r\n");
sql.Append("(id,");
sql.Append("userid,");
sql.Append("roleid)");
sql.Append("values");
sql.Append("(seq_userrole_id.nextval,");
sql.Append(" :userid,");
sql.Append(" :roleid");
sql.Append(")\r\n");
using (var conn = new OraDBHelper().Conn)
{
conn.Open();
using (var transaction = conn.BeginTransaction())
{
conn.Execute("delete from sys_user_role where userid=:userid", new { userid = userid }, transaction);
ret = conn.Execute(sql.ToString(), ur.ToArray(), transaction);
transaction.Commit();
}
}
return(ret);
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
public int Save_RolePermis(List <sys_menu_permission> list)
{
try
{
var roleids = list.Select(t => t.roleid).ToList();
StringBuilder sql = new StringBuilder();
sql.Append("insert into sys_role_permis(id,roleid,menuid,permis) ");
sql.Append(" values ");
sql.Append(" (seq_rolepromis_id.nextval,:roleid,:menuid,:permis)");
List <dynamic> p = new List <dynamic>();
foreach (var item in list)
{
var json = JsonConvert.SerializeObject(item.permission);
p.Add(new
{
roleid = item.roleid,
menuid = item.menuid,
permis = json
});
}
using (var conn = new OraDBHelper().Conn)
{
conn.Open();
using (var tran = conn.BeginTransaction())
{
conn.Execute("delete from sys_role_permis where roleid in :roleid", new { roleid = roleids }, transaction: tran);
int cnt = conn.Execute(sql.ToString(), p, transaction: tran);
tran.Commit();
return(cnt);
}
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
public int Save_RoleUsers(int roleid, List <int> userids)
{
try
{
List <dynamic> p = new List <dynamic>();
foreach (var item in userids)
{
p.Add(new { roleid = roleid, userid = item });
}
StringBuilder sql = new StringBuilder();
sql.Append("insert into sys_user_role \r\n");
sql.Append("(id,");
sql.Append("userid,");
sql.Append("roleid)");
sql.Append("values");
sql.Append("(seq_userrole_id.nextval,");
sql.Append(" :userid,");
sql.Append(" :roleid");
sql.Append(")\r\n");
using (var conn = new OraDBHelper().Conn)
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
conn.Execute("delete FROM sys_user_role where roleid = :roleid", new { roleid = roleid }, transaction: trans);
int res = conn.Execute(sql.ToString(), p.ToArray());
trans.Commit();
return(res);
}
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
public bool Delete(int id)
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append("delete from sys_menu where id =:id ");
using (var conn = new OraDBHelper().Conn)
{
conn.Open();
using (var tran = conn.BeginTransaction())
{
int cnt = conn.Execute(sql.ToString(), new { id = id }, transaction: tran);
int cnt2 = conn.Execute("delete from sys_role_menu where menuid=:id", new { id = id }, transaction: tran);
tran.Commit();
return(cnt > 0 ? true : false);
}
}
}
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 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 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;
}
}
public int FreshToken(int userid)
{
try
{
var token = new JWTHelper().CreateToken();
StringBuilder sql = new StringBuilder();
sql.Append("update sys_user set token=:token where id=:userid");
using (var conn = new OraDBHelper().Conn)
{
return(conn.Execute(sql.ToString(), new { userid = userid, token = token }));
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
public int Modify(zxjc_djgw entity)
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append("update zxjc_djgw ");
sql.Append(" 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(" scbz = :scbz, ");
sql.Append(" djlx = :djlx ");
sql.Append(" where djno = :djno ");
StringBuilder sql1 = new StringBuilder();
sql1.Append("select gcdm, ");
sql1.Append(" scx, ");
sql1.Append(" gwh, ");
sql1.Append(" jx_no, ");
sql1.Append(" status_no, ");
sql1.Append(" djno, ");
sql1.Append(" djxx, ");
sql1.Append(" scbz, ");
sql1.Append(" djlx from zxjc_djgw ");
sql1.Append(" where djno = :djno ");
using (var conn = new OraDBHelper(constr).Conn)
{
zxjc_djgw old = conn.Query <zxjc_djgw>(sql1.ToString(), new { djno = entity.djno }).FirstOrDefault();
int ret = conn.Execute(sql.ToString(), entity);
if (ret > 0)
{
logservice.UpdateLogJson <zxjc_djgw>(entity, old);
}
return(ret);
}
}
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 gcdm = :gcdm");
sql.Append(" and user_code = :user_code");
sql.Append(" and jnbh = :jnbh");
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(":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)
{
return(conn.Execute(sql.ToString(), p));
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
public int Delete(List <zxjc_djgw> entitys)
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append("delete from zxjc_djgw where djno in :djno ");
using (var conn = new OraDBHelper(constr).Conn)
{
int ret = conn.Execute(sql.ToString(), entitys.ToArray());
if (ret > 0)
{
logservice.DeleteLog <zxjc_djgw>(entitys);
}
return(ret);
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
public int Modify(sys_user entity)
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append("update sys_user set name=:name where id=:id");
using (var conn = new OraDBHelper().Conn)
{
sys_user user = new sys_user()
{
id = entity.id,
name = entity.name
};
int isok = conn.Execute(sql.ToString(), user);
return(isok);
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
