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; } }