public class DriverInfoDaoImpl extends BaseDaoImpl<DriverInfo> implements DriverInfoDao {
public DriverInfoDaoImpl() {
super(DriverInfo.class);
}
/**
* 新增驾驶员
* @param driverInfo
* @throws DataAccessException
*/
@Override
public void createDriver(DriverInfo driverInfo) throws DataAccessException {
super.save(driverInfo);
}
/**
* 修改驾驶员
* @param driverInfo
* @throws DataAccessException
*/
@Override
public void editDriver(DriverInfo driverInfo) throws DataAccessException {
super.update(driverInfo);
}
/**
* 删除驾驶员
* @param driverId
* @throws DataAccessException
*/
@Override
public void deleteDriver(String driverId) throws DataAccessException {
super.remove(driverId);
}
/**
* 查询驾驶员
* @return
* @throws DataAccessException
*/
@SuppressWarnings("unchecked")
@Override
public List<DriverInfo> listDriver(final int curNum,final String location,
final String dvId,final String dvName) throws DataAccessException {
return (List<DriverInfo>)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
Criteria criteria = session.createCriteria(DriverInfo.class);
criteria.add(Restrictions.ne("state", "2"));
if (StringUtils.isNotBlank(location)) {
criteria.add(Restrictions.eq("location", location));
}
if (StringUtils.isNotBlank(dvId)) {
criteria.add(Restrictions.like("dvId", dvId, MatchMode.START));
}
if (StringUtils.isNotBlank(dvName)) {
criteria.add(Restrictions.like("dvName", dvName, MatchMode.START));
}
criteria.addOrder(Order.desc("drTime"));
criteria.addOrder(Order.desc("location"));
criteria.setFirstResult((curNum - 1) * PageTag.PAGE_NUM);
criteria.setMaxResults(PageTag.PAGE_NUM);
return criteria.list();
}
});
}
@Override
public Integer listDriverTotal(final String location,
final String dvId,final String dvName) throws DataAccessException {
return (Integer)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
Criteria criteria = session.createCriteria(DriverInfo.class);
criteria.add(Restrictions.ne("state", "2"));
if (StringUtils.isNotBlank(location)) {
criteria.add(Restrictions.eq("location", location));
}
if (StringUtils.isNotBlank(dvId)) {
criteria.add(Restrictions.like("dvId", dvId, MatchMode.START));
}
if (StringUtils.isNotBlank(dvName)) {
criteria.add(Restrictions.like("dvName", dvName, MatchMode.START));
}
return (Integer)criteria.setProjection(Projections.rowCount()).uniqueResult();
}
});
}
/**
* 查询驾驶员
* @return
* @throws DataAccessException
*/
@Override
public DriverInfo getDriver(String driverId) throws DataAccessException {
return super.get(driverId);
}
/**
* 查询驾驶员
* @return
* @throws DataAccessException
*/
@Override
public boolean getDriverInfo(final String driverId) throws DataAccessException {
Integer i = (Integer)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
Query query = session.createQuery("select count(*) from DriverInfo where dvId = :driverId");
query.setParameter("driverId", driverId);
Integer num = (Integer)query.uniqueResult();
logger.info("驾驶员编号:" + driverId + "-->" + num);
return num;
}
});
if (i==0) {
return false;
}
return true;
}
}
public class CarInfoDaoImpl extends BaseDaoImpl<CarInfo> implements CarInfoDao {
public CarInfoDaoImpl() {
super(CarInfo.class);
}
/**
* 新增车辆
* @param carInfo
* @throws DataAccessException
*/
@Override
public void createCar(CarInfo carInfo) throws DataAccessException {
super.save(carInfo);
}
/**
* 修改车辆
* @param carInfo
* @throws DataAccessException
*/
@Override
public void editCar(CarInfo carInfo) throws DataAccessException {
super.update(carInfo);
}
/**
* 删除车辆
* @param carId
* @throws DataAccessException
*/
@Override
public void deleteCar(String carId) throws DataAccessException {
super.remove(carId);
}
/**
* 查询车辆
* @return
* @throws DataAccessException
*/
@SuppressWarnings("unchecked")
@Override
public List<CarInfo> listCar(final int curNum, final String location,
final String car) throws DataAccessException {
return (List<CarInfo>)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
Criteria criteria = session.createCriteria(CarInfo.class);
criteria.add(Restrictions.ne("state", "2"));
if (StringUtils.isNotBlank(location)) {
criteria.add(Restrictions.eq("location", location));
}
if (StringUtils.isNotBlank(car)) {
criteria.add(Restrictions.like("car", car, MatchMode.START));
}
criteria.addOrder(Order.desc("cTime"));
criteria.addOrder(Order.desc("location")).addOrder(Order.asc("car"));
criteria.setFirstResult((curNum - 1) * PageTag.PAGE_NUM);
criteria.setMaxResults(PageTag.PAGE_NUM);
return criteria.list();
}
});
}
/**
* 查询车辆
* @return
* @throws DataAccessException
*/
@Override
public Integer listCarTotal(final int curNum, final String location,
final String car) throws DataAccessException {
return (Integer)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
Criteria criteria = session.createCriteria(CarInfo.class);
criteria.add(Restrictions.ne("state", "2"));
if (StringUtils.isNotBlank(location)) {
criteria.add(Restrictions.eq("location", location));
}
if (StringUtils.isNotBlank(car)) {
criteria.add(Restrictions.like("car", car, MatchMode.END));
}
return (Integer)criteria.setProjection(Projections.rowCount()).uniqueResult();
}
});
}
/**
* 查询车辆
* @return
* @throws DataAccessException
*/
@Override
public CarInfo getCar(String carId) throws DataAccessException {
return super.get(carId);
}
@SuppressWarnings("unchecked")
@Override
public List<Object[]> listCarByLoc(final String location) throws DataAccessException {
return (List<Object[]>)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
StringBuilder sql = new StringBuilder();
sql.append("SELECT * FROM (");
sql.append("SELECT TR.F_CAR, F_SEATS, TR.F_STATE, F_R_T_O ");
sql.append("FROM (SELECT * FROM (SELECT F_CAR,F_STATE, ");
sql.append("TO_CHAR(F_R_T_O, 'yyyy-mm-dd hh24:mi:ss') F_R_T_O, ");
sql.append("ROW_NUMBER() OVER(PARTITION BY F_CAR ORDER BY F_TR_T DESC) RN ");
sql.append("FROM T_RECORD) WHERE RN = 1) TR,T_CAR_INFO TC ");
sql.append("WHERE TC.F_CAR = TR.F_CAR AND TC.F_LOCATION = :location ");
sql.append("AND TC.F_STATE = '1' UNION ");
sql.append("SELECT TC.F_CAR, TC.F_SEATS, '1', '' ");
sql.append("FROM T_CAR_INFO TC ");
sql.append("WHERE TC.F_LOCATION = :location ");
sql.append("AND TC.F_STATE = '1' AND NOT EXISTS ");
sql.append("(SELECT 1 FROM T_RECORD TR WHERE TR.F_CAR = TC.F_CAR) ");
sql.append(") ORDER BY F_STATE,F_R_T_O ");
logger.info("车辆详细列表--->打印参数:"+location);
return session.createSQLQuery(sql.toString()).setParameter("location", location).list();
}
});
}
/**
* 查询车辆对应的驾驶员
* @return
* @throws DataAccessException
*/
@SuppressWarnings("unchecked")
@Override
public List<String> listDriversByLoc(final String location) throws DataAccessException {
return (List<String>)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
Query query = session.createQuery("select dvName from DriverInfo where location=:location");
query.setParameter("location", location);
return query.list();
}
});
}
@SuppressWarnings("unchecked")
@Override
public List<Object[]> getAllLocation() throws DataAccessException {
return (List<Object[]>)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
Query query = session.createSQLQuery("SELECT COMPANY_ID, NAME FROM SPFS.COMPANYS ORDER BY COMPANY_ID");
return query.list();
}
});
}
@Override
public boolean getCarInfo(final String car) throws DataAccessException {
Integer i = (Integer)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
Query query = session.createQuery("select count(*) from CarInfo where car = :car");
query.setParameter("car", car);
Integer num = (Integer)query.uniqueResult();
logger.info("车牌号:" + car + "-->" + num);
return num;
}
});
if (i==0) {
return false;
}
return true;
}
@SuppressWarnings("unchecked")
@Override
public List<String> getAllCarByLoc(final String location)
throws DataAccessException {
return (List<String>)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
Query query = session.createQuery("select car from CarInfo WHERE location = :location");
query.setParameter("location", location);
return query.list();
}
});
}
@Override
public boolean getAccoutnInfo(final String atId) throws DataAccessException {
Integer i = (Integer)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
Query query = session.createQuery("select count(*) from Account where atId = :atId");
query.setParameter("atId", atId);
Integer num = (Integer)query.uniqueResult();
logger.info("编号:" + atId + "-->" + num);
return num;
}
});
if (i==0) {
return false;
}
return true;
}
/**
* 获取费用记录
* @return
* @throws DataAccessException
*/
@SuppressWarnings("unchecked")
@Override
public List<Object[]> getReportData() throws DataAccessException {
return (List<Object[]>)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
StringBuilder sql = new StringBuilder();
sql.append("SELECT B.F_CARD_GAS, B.F_CARD_PASS,A.* FROM (SELECT F_CAR,F_MONTH,SUM(F_FEE_GAS) TOTAL_GAS, ");
sql.append("SUM(F_FEE_CARD) TOTAL_CARD,SUM(F_FEE_MAINTAIN) TOTAL_MAINTAIN FROM (SELECT F_CAR,TO_CHAR( ");
sql.append("F_COST_DATE, 'MM') F_MONTH,F_FEE_GAS,F_FEE_CARD,F_FEE_MAINTAIN FROM T_COST WHERE TRUNC(SYSDATE,");
sql.append("'YYYY')=TRUNC(F_COST_DATE,'YYYY')) GROUP BY F_CAR,F_MONTH) A,T_CAR_INFO B WHERE A.F_CAR = B.F_CAR ORDER BY A.F_CAR, F_MONTH ");
Query query = session.createSQLQuery(sql.toString());
return query.list();
}
});
}
}
public class ApplyDaoImpl extends BaseDaoImpl<Apply> implements ApplyDao {
public ApplyDaoImpl() {
super(Apply.class);
}
/**
* 新增申请条目
* @param applyInfo
* @throws DataAccessException
*/
@Override
public void createApply(Apply applyInfo) throws DataAccessException {
super.save(applyInfo);
}
/**
* 修改申请条目
* @param applyInfo
* @throws DataAccessException
*/
@Override
public void editApply(Apply applyInfo) throws DataAccessException {
super.update(applyInfo);
}
/**
* 删除申请条目
* @param applyId
* @throws DataAccessException
*/
@Override
public void deleteApply(String applyId) throws DataAccessException {
super.remove(applyId);
}
/**
* 查询申请条目记录
* @return
* @throws DataAccessException
*/
@SuppressWarnings("unchecked")
@Override
public List<Apply> listApply(final String atId, final String location,
final String aim, final String uTime, final int curNum)
throws DataAccessException {
return (List<Apply>)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
Criteria criteria = session.createCriteria(Apply.class);
criteria.add(Restrictions.eq("location", location));
criteria.add(Restrictions.eq("apId", atId));
if (StringUtils.isNotBlank(aim)) {
criteria.add(Restrictions.like("aim", aim, MatchMode.START));
}
if (StringUtils.isNotBlank(uTime)) {
try {
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
criteria.add(Restrictions.between("useBegin", format.parse(
uTime+" 00:00:00"),format.parse(uTime+" 23:59:59")));
} catch (Exception e) {
}
}
criteria.addOrder(Order.desc("cTime")).addOrder(Order.desc("useBegin"));
criteria.setFirstResult((curNum - 1) * PageTag.PAGE_NUM);
criteria.setMaxResults(PageTag.PAGE_NUM);
return criteria.list();
}
});
}
/**
* 查询申请条目数
* @return
* @throws DataAccessException
*/
@Override
public Integer listApplyTotal(final String atId, final String location,
final String aim, final String uTime) throws DataAccessException {
return (Integer)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
Criteria criteria = session.createCriteria(Apply.class);
criteria.add(Restrictions.eq("location", location));
criteria.add(Restrictions.eq("apId", atId));
if (StringUtils.isNotBlank(aim)) {
criteria.add(Restrictions.like("aim", aim, MatchMode.START));
}
if (StringUtils.isNotBlank(uTime)) {
try {
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
criteria.add(Restrictions.between("useBegin", format.parse(
uTime+" 00:00:00"),format.parse(uTime+" 23:59:59")));
} catch (Exception e) {
}
}
return (Integer)criteria.setProjection(Projections.rowCount()).uniqueResult();
}
});
}
/**
* 查询申请条目
* @return
* @throws DataAccessException
*/
@Override
public Apply getApply(String applyId) throws DataAccessException {
return super.get(applyId);
}
/**
* 查询申请条目
* @return
* @throws DataAccessException
*/
@Override
public Object[] getApplyDetail(final String applyId) throws DataAccessException {
return (Object[])getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
StringBuilder sql = new StringBuilder();
sql.append("SELECT TA.F_AP_NAME,TA.F_AP_NUM,TA.F_AP_DEPT,TA.F_AIM,TA.F_CAUSE, ");
sql.append("TA.F_USE_SPEC,TA.F_USE_BEGIN,TA.F_USE_END,TA.F_TYPE_OUT,TA.F_TYPE_IN, ");
sql.append("(SELECT TP.F_AV_NAME FROM T_APPROVE TP WHERE TP.F_APPLY_ID = TA.F_APPLY_ID ");
sql.append("AND TP.F_LEVEL = '10' AND ROWNUM = 1) LEVEL_ONE, ");
sql.append("(SELECT TP.F_AV_NAME FROM T_APPROVE TP WHERE TP.F_APPLY_ID = TA.F_APPLY_ID ");
sql.append("AND TP.F_LEVEL <> '10' AND ROWNUM = 1) LEVEL_TWO, ");
sql.append("(SELECT TD.F_WAY_LINE FROM T_DISTRIBUTE TD WHERE TD.F_APPLY_ID = TA.F_APPLY_ID ");
sql.append("AND TD.F_STATE = '1' AND ROWNUM = 1) F_WAY_LINE ");
sql.append("FROM T_APPLY TA WHERE TA.F_APPLY_ID = :applyId AND ROWNUM = 1 ");
logger.info("基本信息列表--->打印参数:"+applyId);
return session.createSQLQuery(sql.toString()).setParameter("applyId", applyId).uniqueResult();
}
});
}
/**
* 门卫登记列表
* @return
* @throws DataAccessException
*/
@SuppressWarnings("unchecked")
@Override
public List<Object[]> getGuardInfo(final String applyId) throws DataAccessException {
return (List<Object[]>)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
StringBuilder sql = new StringBuilder();
sql.append("SELECT TR.F_TYPE,TR.F_CAR,TR.F_NAME,F_R_T_O,F_R_P_O, ");
sql.append("F_R_P_N_O,F_R_T_I,F_R_P_I,F_R_P_N_I FROM T_RECORD TR, ");
sql.append("T_DISTRIBUTE TD, t_apply ta WHERE TR.F_DISTRIBUTE_ID= ");
sql.append("TD.F_DISTRIBUTE_ID AND TD.F_APPLY_ID = TA.F_APPLY_ID AND ");
sql.append("TA.F_APPLY_ID=:applyId AND TD.F_STATE='1' ORDER BY TR.F_TYPE");
logger.info("门卫登记列表:");
return session.createSQLQuery(sql.toString()).setParameter("applyId", applyId).list();
}
});
}
@SuppressWarnings("unchecked")
@Override
public List<Apply> listApprove(final int curNum, final String atId,final String apId,
final String apName,final String cTime,final String aim) throws DataAccessException {
return (List<Apply>)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
StringBuilder sql = new StringBuilder();
sql.append("SELECT * FROM T_APPLY TA WHERE TA.F_STATE IN ('2','3','4') ");
if (StringUtils.isNotBlank(apId)) {
sql.append("AND F_AP_ID LIKE '").append(apId).append("%'");
}
if (StringUtils.isNotBlank(apName)) {
sql.append("AND F_AP_NAME LIKE '").append(apName).append("%'");
}
if (StringUtils.isNotBlank(aim)) {
sql.append("AND F_AIM LIKE '").append(aim).append("%'");
}
if (StringUtils.isNotBlank(cTime)) {
sql.append("AND TO_CHAR(F_C_T,'YYYY-MM-DD')='").append(cTime).append("'");
}
sql.append("AND EXISTS (SELECT 1 FROM (SELECT EMP_ID, UD.REAL_NAME ");
sql.append("FROM SPFS.USER_DETAILS UD WHERE UD.REAL_NAME <> :atId START WITH ");
sql.append("UD.REAL_NAME = :atId CONNECT BY PRIOR UD.EMP_ID = ");
sql.append("UD.MANAGER_ID) WUD WHERE WUD.REAL_NAME = TA.F_AP_ID) ");
sql.append("ORDER BY TA.F_C_T DESC, TA.F_USE_BEGIN DESC ");
SQLQuery query = session.createSQLQuery(sql.toString());
query.addEntity("ta", Apply.class);
query.setParameter("atId", atId);
query.setFirstResult((curNum - 1) * PageTag.PAGE_NUM);
query.setMaxResults(PageTag.PAGE_NUM);
return query.list();
}
});
}
@Override
public Integer listApproveTotal(final String atId,final String apId,
final String apName,final String cTime,final String aim) throws DataAccessException {
return (Integer)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
StringBuilder sql = new StringBuilder();
sql.append("SELECT count(1) FROM T_APPLY TA WHERE TA.F_STATE IN ('2','3','4') ");
if (StringUtils.isNotBlank(apId)) {
sql.append("AND F_AP_ID LIKE '").append(apId).append("%'");
}
if (StringUtils.isNotBlank(apName)) {
sql.append("AND F_AP_NAME LIKE '").append(apName).append("%'");
}
if (StringUtils.isNotBlank(aim)) {
sql.append("AND F_AIM LIKE '").append(aim).append("%'");
}
if (StringUtils.isNotBlank(cTime)) {
sql.append("AND TO_CHAR(F_C_T,'YYYY-MM-DD')='").append(cTime).append("'");
}
sql.append("AND EXISTS (SELECT 1 FROM (SELECT EMP_ID, UD.REAL_NAME ");
sql.append("FROM SPFS.USER_DETAILS UD WHERE UD.REAL_NAME <> :atId START WITH ");
sql.append("UD.REAL_NAME = :atId CONNECT BY PRIOR UD.EMP_ID = ");
sql.append("UD.MANAGER_ID) WUD WHERE WUD.REAL_NAME = TA.F_AP_ID) ");
sql.append("ORDER BY TA.F_C_T DESC, TA.F_USE_BEGIN DESC ");
SQLQuery query = session.createSQLQuery(sql.toString());
query.setParameter("atId", atId);
return ((BigDecimal)query.uniqueResult()).intValue();
}
});
}
@SuppressWarnings("unchecked")
@Override
public List<Apply> listUnApprove(final int curNum, final String atId, final String level) throws DataAccessException {
return (List<Apply>)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
StringBuilder sql = new StringBuilder();
sql.append("SELECT * FROM T_APPLY TA WHERE TA.F_STATE IN ('1',:level) ");
sql.append("AND EXISTS (SELECT 1 FROM (SELECT EMP_ID, UD.REAL_NAME ");
sql.append("FROM SPFS.USER_DETAILS UD WHERE UD.REAL_NAME <> :atId START WITH ");
sql.append("UD.REAL_NAME = :atId CONNECT BY PRIOR UD.EMP_ID = ");
sql.append("UD.MANAGER_ID) WUD WHERE WUD.REAL_NAME = TA.F_AP_ID) ");
sql.append("ORDER BY TA.F_C_T DESC, TA.F_USE_BEGIN DESC ");
SQLQuery query = session.createSQLQuery(sql.toString());
query.addEntity("ta", Apply.class);
query.setParameter("level", level);
query.setParameter("atId", atId);
query.setFirstResult((curNum - 1) * PageTag.PAGE_NUM);
query.setMaxResults(PageTag.PAGE_NUM);
return query.list();
}
});
}
@Override
public Integer listUnApproveTotal(final int curNum, final String atId, final String level) throws DataAccessException {
return (Integer)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
StringBuilder sql = new StringBuilder();
sql.append("SELECT count(1) FROM T_APPLY TA WHERE TA.F_STATE IN ('1',:level) ");
sql.append("AND EXISTS (SELECT 1 FROM (SELECT EMP_ID, UD.REAL_NAME ");
sql.append("FROM SPFS.USER_DETAILS UD WHERE UD.REAL_NAME <> :atId START WITH ");
sql.append("UD.REAL_NAME = :atId CONNECT BY PRIOR UD.EMP_ID = ");
sql.append("UD.MANAGER_ID) WUD WHERE WUD.REAL_NAME = TA.F_AP_ID) ");
sql.append("ORDER BY TA.F_C_T DESC, TA.F_USE_BEGIN DESC ");
SQLQuery query = session.createSQLQuery(sql.toString());
query.setParameter("level", level);
query.setParameter("atId", atId);
return ((BigDecimal)query.uniqueResult()).intValue();
}
});
}
@Override
public void updateApplysState(final String[] applyIds, final String state) throws DataAccessException {
getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
String hql = "update Apply set state = :state where applyId in (:applyIds)";
Query query = session.createQuery(hql);
query.setParameter("state", state);
query.setParameterList("applyIds", applyIds);
query.executeUpdate();
return null;
}
});
}
@Override
public void saveApproves(Approve[] approves) throws DataAccessException {
for (Approve approve : approves) {
getHibernateTemplate().save(approve);
getHibernateTemplate().flush();
getHibernateTemplate().clear();
}
}
@SuppressWarnings("unchecked")
@Override
public List<Record> listOutGuard(final int curNum, final String location) throws DataAccessException {
return (List<Record>)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
StringBuilder sql = new StringBuilder();
sql.append("SELECT F_CAR,F_NAME,MAX(F_TR_T) F_TR_T FROM T_RECORD ");
sql.append("WHERE F_R_T_O IS NULL AND F_LOCATION=:location AND TRUNC(F_TR_T) >= TRUNC(SYSDATE) ");
sql.append("GROUP BY F_CAR,F_NAME ORDER BY F_TR_T DESC ");
SQLQuery query = session.createSQLQuery(sql.toString());
query.setParameter("location", location);
query.setFirstResult((curNum - 1) * PageTag.PAGE_NUM);
query.setMaxResults(PageTag.PAGE_NUM);
List<Object[]> objects = (List<Object[]>)query.list();
List<Record> records = new ArrayList<Record>();
Record record = null;
if (objects!=null&&!objects.isEmpty()) {
for (Object[] object : objects) {
record = new Record();
record.setCar((String)object[0]);
record.setrName((String)object[1]);
record.settTime((Date)object[2]);
records.add(record);
}
}
return records;
}
});
}
@Override
public Integer listOutGuardTotal(int curNum, final String location) throws DataAccessException {
return (Integer)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
StringBuilder sql = new StringBuilder();
sql.append("SELECT COUNT(DISTINCT F_CAR) FROM T_RECORD WHERE F_R_T_O IS NULL AND ");
sql.append("F_LOCATION=:location AND TRUNC(F_TR_T) >= TRUNC(SYSDATE) ");
SQLQuery query = session.createSQLQuery(sql.toString());
query.setParameter("location", location);
return ((BigDecimal)query.uniqueResult()).intValue();
}
});
}
@SuppressWarnings("unchecked")
@Override
public List<Record> listInGuard(final int curNum, final String location) throws DataAccessException {
return (List<Record>)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
StringBuilder sql = new StringBuilder();
sql.append("SELECT F_CAR,F_NAME,F_R_T_O,MAX(F_TR_T) F_TR_T FROM T_RECORD WHERE F_R_T_O IS NOT NULL AND ");
sql.append("F_R_T_I IS NULL AND F_LOCATION=:location AND TRUNC(F_TR_T) >= TRUNC(SYSDATE) ");
sql.append("GROUP BY F_R_T_O,F_NAME,F_CAR ORDER BY F_TR_T DESC ");
SQLQuery query = session.createSQLQuery(sql.toString());
query.setParameter("location", location);
query.setFirstResult((curNum - 1) * PageTag.PAGE_NUM);
query.setMaxResults(PageTag.PAGE_NUM);
List<Object[]> objects = (List<Object[]>)query.list();
List<Record> records = new ArrayList<Record>();
Record record = null;
if (objects!=null&&!objects.isEmpty()) {
for (Object[] object : objects) {
record = new Record();
record.setCar((String)object[0]);
record.setrName((String)object[1]);
record.setRto((Date)object[2]);
record.settTime((Date)object[3]);
records.add(record);
}
}
return records;
}
});
}
@Override
public Integer listInGuardTotal(int curNum, final String location) throws DataAccessException {
return (Integer)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
StringBuilder sql = new StringBuilder();
sql.append("SELECT COUNT(DISTINCT F_CAR) FROM T_RECORD WHERE F_R_T_I IS NULL AND F_R_T_O ");
sql.append("IS NOT NULL AND F_LOCATION=:location AND TRUNC(F_TR_T) >= TRUNC(SYSDATE) ");
SQLQuery query = session.createSQLQuery(sql.toString());
query.setParameter("location", location);
return ((BigDecimal)query.uniqueResult()).intValue();
}
});
}
@SuppressWarnings("unchecked")
@Override
public List<Apply> listDistribute(final int curNum,final String location,final String apNum,
final String apId,final String aim) throws DataAccessException {
return (List<Apply>)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
Criteria criteria = session.createCriteria(Apply.class);
criteria.add(Restrictions.ne("state", "1"));
criteria.add(Restrictions.ne("state", "2"));
criteria.add(Restrictions.ne("state", "3"));
criteria.add(Restrictions.ne("state", "4"));
if (StringUtils.isNotBlank(location)) {
criteria.add(Restrictions.eq("location", location));
}
if (StringUtils.isNotBlank(apNum)) {
criteria.add(Restrictions.eq("apNum", apNum));
}
if (StringUtils.isNotBlank(apId)) {
criteria.add(Restrictions.like("apId", apId, MatchMode.START));
}
if (StringUtils.isNotBlank(aim)) {
criteria.add(Restrictions.like("aim", aim, MatchMode.START));
}
criteria.addOrder(Order.desc("cTime")).addOrder(Order.desc("useBegin"));
criteria.setFirstResult((curNum - 1) * PageTag.PAGE_NUM);
criteria.setMaxResults(PageTag.PAGE_NUM);
return criteria.list();
}
});
}
@Override
public Integer listDistributeTotal(final String location,final String apNum,
final String apId,final String aim) throws DataAccessException {
return (Integer)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
Criteria criteria = session.createCriteria(Apply.class);
criteria.add(Restrictions.ne("state", "1"));
criteria.add(Restrictions.ne("state", "2"));
criteria.add(Restrictions.ne("state", "3"));
criteria.add(Restrictions.ne("state", "4"));
if (StringUtils.isNotBlank(location)) {
criteria.add(Restrictions.eq("location", location));
}
if (StringUtils.isNotBlank(apNum)) {
criteria.add(Restrictions.eq("apNum", apNum));
}
if (StringUtils.isNotBlank(apId)) {
criteria.add(Restrictions.like("apId", apId, MatchMode.START));
}
if (StringUtils.isNotBlank(aim)) {
criteria.add(Restrictions.like("aim", aim, MatchMode.START));
}
return (Integer)criteria.setProjection(Projections.rowCount()).uniqueResult();
}
});
}
@SuppressWarnings("unchecked")
@Override
public List<Apply> listUnDistribute(final int curNum) throws DataAccessException {
return (List<Apply>)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
Criteria criteria = session.createCriteria(Apply.class);
criteria.add(Restrictions.eq("state", "3"));
criteria.add(Restrictions.ge("useBegin", DateUtil.getCurDate()));
criteria.addOrder(Order.desc("cTime")).addOrder(Order.asc("useBegin"));
criteria.setFirstResult((curNum - 1) * PageTag.PAGE_NUM);
criteria.setMaxResults(PageTag.PAGE_NUM);
return criteria.list();
}
});
}
@Override
public Integer listUnDistributeTotal(int curNum) throws DataAccessException {
return (Integer)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
Criteria criteria = session.createCriteria(Apply.class);
criteria.add(Restrictions.eq("state", "3"));
criteria.add(Restrictions.ge("useBegin", DateUtil.getCurDate()));
return (Integer)criteria.setProjection(Projections.rowCount()).uniqueResult();
}
});
}
@SuppressWarnings("unchecked")
@Override
public List<Apply> listSurvey(final int curNum, final String atId) throws DataAccessException {
return (List<Apply>)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
Criteria criteria = session.createCriteria(Apply.class);
criteria.add(Restrictions.eq("state", "7"));
criteria.add(Restrictions.eq("apId", atId));
criteria.addOrder(Order.desc("cTime"));
criteria.setFirstResult((curNum - 1) * PageTag.PAGE_NUM);
criteria.setMaxResults(PageTag.PAGE_NUM);
return criteria.list();
}
});
}
@Override
public Integer listSurveyTotal(int curNum, final String atId) throws DataAccessException {
return (Integer)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
Criteria criteria = session.createCriteria(Apply.class);
criteria.add(Restrictions.eq("state", "7"));
criteria.add(Restrictions.eq("apId", atId));
return (Integer)criteria.setProjection(Projections.rowCount()).uniqueResult();
}
});
}
@Override
public String saveRecord(Record record) throws DataAccessException {
return (String)getHibernateTemplate().save(record);
}
@Override
public void saveDistributes(Distribute[] distributes)
throws DataAccessException {
for (Distribute distribute : distributes) {
getHibernateTemplate().save(distribute);
getHibernateTemplate().flush();
getHibernateTemplate().clear();
}
}
@Override
public String saveDistribute(Distribute distribute)
throws DataAccessException {
return (String)getHibernateTemplate().save(distribute);
}
@Override
public void saveRecords(List<Record> records) throws DataAccessException {
for (Record record : records) {
getHibernateTemplate().save(record);
getHibernateTemplate().flush();
getHibernateTemplate().clear();
}
}
@Override
public Record getItemGuard(String recordId, String flag) throws DataAccessException {
Date rto=(Date)getHibernateTemplate().find("SELECT rto FROM Record WHERE car ='" +
recordId + "' AND state = '" +
flag + "' AND ROWNUM=1").get(0);
Record record = new Record();
record.setRto(rto);
return record;
}
@Override
public void updateItemGuard(final String recordId, final String atId, final String atName,
final String flag) throws DataAccessException {
getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
StringBuilder hql = new StringBuilder();
if (flag.equals("2")) {
hql.append("UPDATE Record SET rto=sysdate,rpo =:atId,rpno=:atName,state=:state ");
hql.append("WHERE rto IS NULL AND car=:car");
} else {
hql.append("UPDATE Record SET rti=sysdate,rpi =:atId,rpni=:atName,state=:state ");
hql.append("WHERE rto IS NOT NULL AND rti IS NULL AND car=:car");
}
Query query = session.createQuery(hql.toString());
query.setParameter("atId", atId);
query.setParameter("atName", atName);
query.setParameter("state", flag);
query.setParameter("car", recordId);
return query.executeUpdate();
}
});
}
@Override
public void updateAccount(final String atid, final String psd1,
final String role) throws DataAccessException {
getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
String hql = null;
Query query = null;
if (psd1 != null) {
hql = "UPDATE Account a SET atPsd = :psd1 WHERE atId = :atid";
query = session.createQuery(hql);
query.setParameter("atid", atid);
query.setParameter("psd1", psd1);
} else {
hql = "UPDATE Account a SET role = :role WHERE atId = :atid";
query = session.createQuery(hql);
query.setParameter("atid", atid);
query.setParameter("role", role);
}
return query.executeUpdate();
}
});
}
@Override
public void updateApplyStateByRId(final String recordId, final String state)
throws DataAccessException {
getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
StringBuilder hql = new StringBuilder();
hql.append("UPDATE Apply ta SET ta.state = :state WHERE ta.applyId IN (select ");
hql.append("td.applyId FROM Distribute td, Record tr WHERE td.distributeId ");
hql.append("= tr.distributeId AND tr.car = :recordId) ");
Query query = session.createQuery(hql.toString());
query.setParameter("state", state);
query.setParameter("recordId", recordId);
query.executeUpdate();
return null;
}
});
}
@Override
public void saveSurvey(Survey survey) throws DataAccessException {
getHibernateTemplate().save(survey);
}
@SuppressWarnings("unchecked")
@Override
public List<String> getApplyIds(final String recordId) throws DataAccessException {
return (List<String>)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
Query query = session.createQuery("SELECT TA.applyId FROM Apply TA, " +
"Distribute TD, Record TR WHERE TA.applyId = TD.applyId AND " +
"TD.distributeId=TR.distributeId AND TR.car=:recordId AND TR.rti IS NULL");
query.setParameter("recordId", recordId);
return query.list();
}
});
}
@Override
public void saveCost(Cost cost) throws DataAccessException {
getHibernateTemplate().save(cost);
}
@Override
public boolean checkCarNoState(final String carNo) throws DataAccessException {
Integer i = (Integer)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
Query query = session.createQuery("SELECT COUNT(*) FROM Record WHERE car = :car" +
" AND rto IS NOT NULL AND state='2'");
query.setParameter("car", carNo);
Integer num = (Integer)query.uniqueResult();
return num;
}
});
if (i==0) {
return false;
}
return true;
}
@Override
public boolean checkExistsCarNo(final String distributeId, final String carNo)
throws DataAccessException {
Integer i = (Integer)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
Query query = session.createQuery("SELECT COUNT(*) FROM Record WHERE car = :car" +
" AND rto IS NULL AND state='3' AND distributeId = :distributeId");
query.setParameter("distributeId", distributeId);
query.setParameter("car", carNo);
Integer num = (Integer)query.uniqueResult();
return num;
}
});
if (i==0) {
return false;
}
return true;
}
@Override
public Object[] getSurveyInfo(final String applyId) throws DataAccessException {
return (Object[])getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
StringBuilder sql = new StringBuilder();
sql.append("SELECT TS.F_IMPROVE, TS.F_GRADE, TS.F_ADVICE, TS.F_SV_T FROM ");
sql.append("T_SURVEY TS WHERE TS.F_APPLY_ID = :applyId");
logger.info("满意度登记列表:");
return session.createSQLQuery(sql.toString()).setParameter("applyId", applyId).uniqueResult();
}
});
}
@SuppressWarnings("unchecked")
@Override
public List<Cost> listCost(final int curNum,final String car,final String costDate) throws DataAccessException {
return (List<Cost>)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
Criteria criteria = session.createCriteria(Cost.class);
if (StringUtils.isNotBlank(costDate)) {
try {
criteria.add(Restrictions.eq("costDate", new SimpleDateFormat("yyyy-MM-dd").parse(costDate)));
} catch (Exception e) {
criteria.add(Restrictions.eq("cp","TEST"));
}
}
if (StringUtils.isNotBlank(car)) {
criteria.add(Restrictions.like("car", car, MatchMode.START));
}
criteria.addOrder(Order.desc("ct"));
criteria.addOrder(Order.asc("car"));
criteria.addOrder(Order.desc("costDate"));
criteria.setFirstResult((curNum - 1) * PageTag.PAGE_NUM);
criteria.setMaxResults(PageTag.PAGE_NUM);
return criteria.list();
}
});
}
@Override
public Integer listCostTotal(final String car,final String costDate) throws DataAccessException {
return (Integer)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
Criteria criteria = session.createCriteria(Cost.class);
if (StringUtils.isNotBlank(costDate)) {
try {
criteria.add(Restrictions.eq("costDate", new SimpleDateFormat("yyyy-MM-dd").parse(costDate)));
} catch (Exception e) {
criteria.add(Restrictions.eq("cp","TEST"));
}
}
if (StringUtils.isNotBlank(car)) {
criteria.add(Restrictions.like("car", car, MatchMode.START));
}
return (Integer)criteria.setProjection(Projections.rowCount()).uniqueResult();
}
});
}
@Override
public void delCost(String costId) throws DataAccessException {
Cost cost = (Cost)getHibernateTemplate().load(Cost.class, costId);
getHibernateTemplate().delete(cost);
}
@SuppressWarnings("unchecked")
@Override
public List<Object[]> listRecord(final int curNum, final String location)
throws DataAccessException {
return (List<Object[]>)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
Query query = session.createQuery("select distinct car,rName,rto,rpno,rti,rpni from Record r where location=? order by rto desc,rti desc");
query.setString(0, location);
query.setFirstResult((curNum - 1) * PageTag.PAGE_NUM);
query.setMaxResults(PageTag.PAGE_NUM);
return query.list();
}
});
}
@Override
public Integer listRecordTotal(final String location) throws DataAccessException {
return (Integer)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
Query query = session.createSQLQuery("SELECT COUNT(*) FROM (SELECT DISTINCT F_CAR,F_NAME,F_R_T_O,F_R_P_N_O,F_R_T_I,F_R_P_N_I FROM T_RECORD R WHERE F_LOCATION=?)");
query.setString(0, location);
return ((BigDecimal)query.uniqueResult()).intValue();
}
});
}
@SuppressWarnings("unchecked")
public List<String> getFleetAtId() throws DataAccessException {
return (List<String>)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
StringBuilder sql = new StringBuilder();
sql.append("SELECT U.USER_EMAIL FROM SPFS.USER_DETAILS UD,SC.USERS U, ");
sql.append("T_ACCOUNT TA WHERE UD.USER_ID=U.USER_ID AND UD.REAL_NAME ");
sql.append("= TA.F_AT_ID AND UD.IS_NEW = 'N' AND TA.F_ROLE = '4' ");
Query query = session.createSQLQuery(sql.toString());
return query.list();
}
});
}
/**
* 申请人获取到上级邮箱
* @param atId
* @throws DataAccessException
*/
@SuppressWarnings("unchecked")
@Override
public List<String> getMailById(final String atId) throws DataAccessException {
return (List<String>)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
StringBuilder sql = new StringBuilder();
sql.append("SELECT USER_EMAIL FROM (SELECT U.USER_EMAIL FROM (SELECT USER_ID,JOB_LEVEL,IS_NEW FROM ");
sql.append("SPFS.USER_DETAILS UD WHERE REAL_NAME<>:atId START WITH REAL_NAME=:atId CONNECT BY ");
sql.append("PRIOR UD.MANAGER_ID=UD.EMP_ID) TU,SC.USERS U WHERE TU.USER_ID=U.USER_ID AND IS_NEW ");
sql.append("= 'N' AND (JOB_LEVEL<='50' OR JOB_LEVEL IS NULL) ORDER BY JOB_LEVEL) WHERE ROWNUM <=2");
Query query = session.createSQLQuery(sql.toString());
query.setParameter("atId", atId);
return query.list();
}
});
}
}
public class LoginDaoImpl extends HibernateDaoSupport implements LoginDao {
@Override
public Map<String, String> getLocations() throws DataAccessException {
// TODO Auto-generated method stub
return null;
}
@Override
public Account getAccount(final String atId, final String atPsd)
throws DataAccessException {
return (Account)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
Criteria criteria = session.createCriteria(Account.class);
criteria.add(Restrictions.eq("atId", atId));
if (atPsd != null) {
criteria.add(Restrictions.eq("atPsd", atPsd));
}
criteria.add(Restrictions.eq("state", "1"));
return criteria.uniqueResult();
}
});
}
@Override
public boolean checkAccount(final String atId) throws DataAccessException {
Integer i = (Integer)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
SQLQuery query =session.createSQLQuery("SELECT COUNT(1) FROM SPFS.USER_DETAILS WHERE IS_NEW='N' AND REAL_NAME = :real_name");
query.setParameter("real_name", atId);
Integer num = ((BigDecimal)query.uniqueResult()).intValue();
logger.info("登录名:" + atId + "-->" + num);
return num;
}
});
if (i==0) {
return false;
}
return true;
}
@Override
public void saveAccount(Account account) throws DataAccessException {
getHibernateTemplate().save(account);
}
@Override
public Object[] getLocById(final String atId) throws DataAccessException {
return (Object[])getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
StringBuilder sql = new StringBuilder();
sql.append("SELECT UD.REAL_NAME,D.NAME,C.COMPANY_ID,C.NAME,UD.ALTERNATE_NAME,UD.JOB_LEVEL,UD.EMP_ID FROM ");
sql.append("SPFS.USER_DETAILS UD ,SPFS.DEPTS D, SPFS.COMPANYS C WHERE UD.COMPANY_ID = C.COMPANY_ID AND UD.DEPT_ID ");
sql.append("= D.DEPT_ID AND IS_NEW='N' AND REAL_NAME = :real_name ");
SQLQuery query =session.createSQLQuery(sql.toString());
query.setParameter("real_name", atId);
return query.uniqueResult();
}
});
}
@SuppressWarnings("unchecked")
@Override
public List<Account> listAccount(final int curNum, final String role,final String actId)
throws DataAccessException {
return (List<Account>)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
Criteria criteria = session.createCriteria(Account.class);
if ("4".equals(role)) {
criteria.add(Restrictions.eq("state", "1"));
criteria.add(Restrictions.eq("role", "3"));
}
if (StringUtils.isNotBlank(actId)) {
criteria.add(Restrictions.like("atId", actId, MatchMode.START));
}
criteria.addOrder(Order.desc("cTime"));
criteria.addOrder(Order.desc("role"));
criteria.addOrder(Order.asc("location"));
criteria.setFirstResult((curNum - 1) * PageTag.PAGE_NUM);
criteria.setMaxResults(PageTag.PAGE_NUM);
return criteria.list();
}
});
}
@Override
public Integer listAccountTotal(final String role,final String actId)
throws DataAccessException {
return (Integer)getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
Criteria criteria = session.createCriteria(Account.class);
if ("4".equals(role)) {
criteria.add(Restrictions.eq("state", "1"));
criteria.add(Restrictions.eq("role", "3"));
}
if (StringUtils.isNotBlank(actId)) {
criteria.add(Restrictions.like("atId", actId, MatchMode.START));
}
return (Integer)criteria.setProjection(Projections.rowCount()).uniqueResult();
}
});
}
@Override
public void delAccount(String accountId) throws DataAccessException {
Account account = (Account)getHibernateTemplate().load(Account.class, accountId);
getHibernateTemplate().delete(account);
}
@Override
public Role getRole(String role) throws DataAccessException {
return (Role)getHibernateTemplate().get(Role.class, role);
}
@SuppressWarnings("unchecked")
@Override
public List<Role> getAllRole() throws DataAccessException {
return (List<Role>)getHibernateTemplate().find("from Role order by roleName");
}
@Override
public void updateRole(Role role) throws DataAccessException {
getHibernateTemplate().flush();
getHibernateTemplate().clear();
getHibernateTemplate().update(role);
}
}