@Override
public void addRights(Rights rights) {
dataBaseDaoHelper.persist(rights);
String sql =
"insert into t_role_rights values (s_role_rights.nextval,'" + rights.getRightsId() + "',1)";
dataBaseDaoHelper.executeNativeSQL(sql);
}
@Override
public void dropRights(String name) {
String sql = "delete t_rights t where t.rights_name ='" + name + "'";
String rightsId = "select s.rightsId from Rights s where s.rightsName = '" + name + "'";
rightsId = dataBaseDaoHelper.getSingleResultByHql(rightsId).toString();
String roleRightsId = "delete t_role_rights r where r.rights_Id = '" + rightsId + "'";
String dropReport = "delete t_report_data trd where trd.rights_id ='" + rightsId + "'";
dataBaseDaoHelper.executeNativeSQL(roleRightsId);
dataBaseDaoHelper.executeNativeSQL(dropReport);
dataBaseDaoHelper.executeNativeSQL(sql);
}
@Override
public List<Rights> getOpRoleRights(Long id) {
String sql =
" select distinct tr.rights_id,tr.rights_name , tr.rights_level ,tr.p_rights_id from t_role_rights trr, t_rights tr , t_operator top, t_operator_role tor where tr.rights_id = trr.rights_id and top.operator_id = tor.operator_id and tor.role_id = trr.role_id and tor.operator_id = "
+ id;
List<Rights> lstRights = new ArrayList<Rights>();
try {
lstRights =
dataBaseDaoHelper.getList(
sql,
new RowHandler<Rights>() {
public Rights handle(Object[] obj, int rowNum) {
Rights ri = new Rights();
ri.setRightsId((obj[0]).toString());
ri.setRightsName((obj[1]).toString());
ri.setRightsLevel((obj[2]).toString());
if (obj[3] != null && !"".equals(obj[3].toString())) {
ri.setPrightsId(obj[3].toString());
} else {
ri.setPrightsId("rootnode");
}
return ri;
}
});
} catch (Exception e) {
e.printStackTrace();
}
return lstRights;
}
@Override
public List<Rights> getlv2IdList(String id) {
String hql =
"from Rights ri where ri.rightsParamter like 'images/%' and ri.prightsId = " + id;
// String hql = "from Rights ri where ri.prightsId = "+id;
return dataBaseDaoHelper.getResultListByHql(hql);
}
@Override
public List<Rights> getLegalMenu(String id) {
// select * from t_rights tt where tt.rights_level = 2 and tt.p_rights_id = 1006000000000
String hql =
"from Rights where rightsLevel = 2 and rightsParamter like 'images%' and prightsId =" + id;
return dataBaseDaoHelper.getResultListByHql(hql);
}
@Override
public Long getlv2Id(String id) {
try {
String sql = "select max(t.rights_id) from t_rights t where t.p_rights_id = " + id;
// String sql = " select * from (select ttt.rights_id from t_rights ttt where
// ttt.p_rights_id = "+id+" order by ttt.rights_id desc) where rownum <2 ";
return Long.parseLong(dataBaseDaoHelper.getSingleResult(sql).toString());
} catch (Exception e) {
return Long.parseLong(id);
}
}
public List<Rights> findDailyByCondition(Rights rights, Integer firstResult, Integer maxResults) {
String strHql = "SELECT d FROM Rights d WHERE 1=1";
// 员工工号
if (null != rights.getRightsName() && !"".equals(rights.getRightsName())) {
strHql += " AND d.rightsName LIKE('%" + rights.getRightsName() + "%')";
}
strHql += " ORDER BY d.rightsId";
System.out.println(strHql);
return dataBaseDaoHelper.getResultListByHql(strHql, firstResult, maxResults);
}
@Override
public void updateLv2(MergeItemGroup merge) {
String sql =
"update bi_dim.D_MERGE_ITEM_GROUP dmig set dmig.name = '"
+ merge.getMergeName()
+ "', dmig.state_date = to_date('"
+ merge.getMergeDate()
+ "','yyyy-mm-dd'),dmig.dimension_table_name = '"
+ merge.getTableName()
+ "' where dmig.merge_group_id = "
+ merge.getMergeId();
dataBaseDaoHelper.executeNativeSQL(sql);
}
@Override
public Long countDailyByCondition(Rights rights) {
String strHql = "SELECT COUNT(d) FROM Rights d WHERE 1=1";
// 员工工号
if (null != rights.getRightsName() && !"".equals((rights.getRightsName()))) {
strHql += " AND d.rightsName LIKE('%" + rights.getRightsName() + "%')";
}
strHql += " ORDER BY d.rightsId";
System.out.println(strHql);
return (Long) dataBaseDaoHelper.getSingleResultByHql(strHql);
}
@Override
public void lv2Add(MergeItemGroup merge) {
String sql =
"insert into bi_dim.D_MERGE_ITEM_GROUP values (bi_dim.s_merge_item_group.nextval,'"
+ merge.getLatituId()
+ "','"
+ merge.getMergeName()
+ "',to_date('"
+ merge.getMergeDate()
+ "','yyyy-MM-dd'),'"
+ merge.getTableName()
+ "')";
dataBaseDaoHelper.executeNativeSQL(sql);
// return dataBaseDaoHelper.persist("bi_dim.S_MERGE_ITEM_GROUP", merge,
// "mergeId");
}
@Override
@Transactional(propagation = Propagation.REQUIRED)
public void dropId(String id) {
try {
if (!"".equals(id) && null != id && !"没有可选择的菜单！".equals(id)) {
String hql = "from Rights ri where ri.prightsId = " + id;
List<Rights> list = dataBaseDaoHelper.getResultListByHql(hql);
if (list.size() == 0 || null == list) {
String roleHql = "delete t_Role_Rights where Rights_Id =" + id;
dataBaseDaoHelper.executeNativeSQL(roleHql);
String reportHql = "delete t_report_data where Rights_Id =" + id;
dataBaseDaoHelper.executeNativeSQL(reportHql);
String rightsHql = "delete t_Rights where Rights_Id =" + id;
dataBaseDaoHelper.executeNativeSQL(rightsHql);
} else {
for (int i = 0; i < list.size(); i++) {
String temp = list.get(i).getRightsId();
// 删除二级菜单下的三级菜单
String roleHql1 = "delete t_Role_Rights where Rights_Id =" + temp;
dataBaseDaoHelper.executeNativeSQL(roleHql1);
String reportHql1 = "delete t_report_data where Rights_Id =" + temp;
dataBaseDaoHelper.executeNativeSQL(reportHql1);
String rightsHql1 = "delete t_Rights where Rights_Id =" + temp;
dataBaseDaoHelper.executeNativeSQL(rightsHql1);
// 删除空的二级菜单
String idDelete = "delete t_Role_Rights where Rights_Id =" + id;
dataBaseDaoHelper.executeNativeSQL(idDelete);
String idDelete1 = "delete t_Rights where Rights_Id =" + id;
dataBaseDaoHelper.executeNativeSQL(idDelete1);
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
@Override
public List<MergeItemGroup> queryLv2(Long name) {
List<MergeItemGroup> mergeList = new ArrayList<MergeItemGroup>();
String sql = "select * from bi_dim.D_MERGE_ITEM_GROUP dmig where dmig.merge_group_id = " + name;
return mergeList =
dataBaseDaoHelper.getList(
sql,
new RowHandler<MergeItemGroup>() {
public MergeItemGroup handle(Object[] obj, int rowNum) {
MergeItemGroup merge = new MergeItemGroup();
merge.setMergeId(Long.parseLong(obj[0].toString()));
merge.setLatituId(Long.parseLong(obj[1].toString()));
merge.setMergeName(obj[2].toString());
merge.setMergeDate(obj[3].toString());
merge.setTableName(obj[4].toString());
return merge;
}
});
}
@Override
public List<MergeItemGroup> findAllLv2(Integer id) {
List<MergeItemGroup> mergeList = new ArrayList<MergeItemGroup>();
String sql = "select * from bi_dim.D_MERGE_ITEM_GROUP merge where merge.latitude_id =" + id;
return mergeList =
dataBaseDaoHelper.getList(
sql,
new RowHandler<MergeItemGroup>() {
public MergeItemGroup handle(Object[] obj, int rowNum) {
MergeItemGroup merge = new MergeItemGroup();
merge.setMergeId(Long.parseLong(obj[0].toString()));
merge.setLatituId(Long.parseLong(obj[1].toString()));
merge.setMergeName(obj[2].toString());
merge.setMergeDate(obj[3].toString());
merge.setTableName(obj[4].toString());
return merge;
}
});
}
// 拜访记录
@Override
public List<bfjlBean> bfjlfindbfjlBeanByCondition(
bfjlBean bfjlBean, Integer start, Integer limit) {
String strHql =
"select t.bfjl_id ,t1.khgl_id ,t1.khgl_bh ,t1.khgl_qc ,t2.lxrgl_id ,t2.lxrgl_xm ,t3.employee_id,t3.employee_no, t3.employee_name ,t.bfjl_jlzt,t.bfjl_kssj,t.bfjl_jssj,t.bfjl_xtry,t.bfjl_cxqx,t.bfjl_jlnr,t.bfjl_xcjlzt,t.bfjl_xcjlsj, t.bfjl_zdry,t.bfjl_zdrybh ,t.bfjl_zdbm ,t.bfjl_zdbmbh ,t.bfjl_bz from T_BFJL t ,t_khgl t1 ,t_lxrgl t2 ,t_employee t3 where t.khgl_id =t1.khgl_id and t.lxrgl_id =t2.lxrgl_id and t.employee_id =t3.employee_id and 1=1 ";
// 申请人员
// if (null != bfjlBean.getKhgxlbmc()
// && !"".equals(bfjlBean.getKhgxlbmc().trim())) {
// strHql += " and t2.khlb_mc LIKE('%"
// + bfjlBean.getKhgxlbmc().trim() + "%')" ;
// }
// // 类型名称
// if (null != bfjlBean.getKhgxqc()
// && !"".equals(bfjlBean.getKhgxqc())) {
// strHql += " and t.bfjl_qc LIKE('%"
// +bfjlBean.getKhgxqc() + "%')";
// }
strHql += "ORDER BY t.bfjl_id";
return dataBaseDaoHelper.bfjlfindbfjlBeanCondition(strHql, start, limit);
}
@Override
public void updateRightsName(String before, String after, String params) {
try {
String sql =
"update t_rights r set r.rights_Name = '"
+ after
+ "',r.rights_paramter = '"
+ params.replace("'", "''")
+ "' where r.rights_name = '"
+ before
+ "'";
System.out.println(sql);
dataBaseDaoHelper.executeNativeSQL(sql);
/*String hql = "update Rights set rightsName = :a, rightsParamter = :b where rightsName = :c";
Query query = entityManager.createQuery(hql);
query.setParameter("a", after);
query.setParameter("b", params.replace("'", "''"));
query.setParameter("c", before);
query.executeUpdate();*/
} catch (Exception e) {
e.printStackTrace(); // TODO: handle exception
}
}
@Override
public List<Latitude> findAllLatitude() {
String hql = "select la from Latitude la ";
return dataBaseDaoHelper.getResultListByHql(hql);
}
// 联系人下拉框
public List<lxrgl> lxrglBylxrgl(lxrgl lxrgl) {
String strHql = "select t.lxrgl_id,t.lxrgl_xm from T_lxrgl t";
return dataBaseDaoHelper.lxrglxlkbfjl(strHql);
}
@Override
public List<Rights> dropMenu(String id) {
String hql = "from Rights ri where ri.prightsId = " + id;
return dataBaseDaoHelper.getResultListByHql(hql);
}
@Override
public void updateLv1(Latitude latitude) {
dataBaseDaoHelper.updateObject(latitude);
}
// 增加
@Override
public Long addbfjl(bfjl bfjl) {
return dataBaseDaoHelper.persist("S_BFJL", bfjl, "bfjlid");
}
// 删除
@Override
public void removebfjlid(Long bfjlid) {
String strHql = "delete T_bfjl t where t.bfjl_id=" + bfjlid;
dataBaseDaoHelper.executeNativeSQL(strHql);
}
@Override
public String getRightsParams(String name) {
String hql = "select rightsParamter from Rights where rightsName = '" + name + "'";
return dataBaseDaoHelper.getSingleResultByHql(hql).toString();
}
@Override
public Employee getygglid(Long ygglid) {
return (Employee) dataBaseDaoHelper.getObjectbyId(Employee.class, ygglid);
}
// 修改
@Override
public void updateyggl(Employee Employee) {
dataBaseDaoHelper.updateObject(Employee);
}
// 增加
@Override
public Long addyggl(Employee Employee) {
return dataBaseDaoHelper.persist("S_EMPLOYEE", Employee, "employeeId");
}
@Override
public Long lv1Add(Latitude latitude) {
return dataBaseDaoHelper.persist("s_latitude", latitude, "latiId");
}
@Override
public Latitude queryById(Long id) {
// TODO Auto-generated method stub
return (Latitude) dataBaseDaoHelper.getObjectbyId(Latitude.class, id);
}
// 修改
@Override
public void updatebfjl(bfjl bfjl) {
dataBaseDaoHelper.updateObject(bfjl);
}
// 删除
@Override
public void removeygglid(Long ygglid) {
String strHql = "delete T_EMPLOYEE t where t.EMPLOYEE_id=" + ygglid;
dataBaseDaoHelper.executeNativeSQL(strHql);
}
// 根据联系人id查相关数据
public List<lxrgl> glsjcx(lxrgl lxrgl) {
String strHql =
"select t.lxrgl_id,t.lxrgl_zw,t.lxrgl_dh,t.lxrgl_yddh,t.lxrgl_email from T_LXRGL t where t.lxrgl_id="
+ lxrgl.getLxrid();
return dataBaseDaoHelper.glsjcxlxrgl(strHql);
}