Java tutorial
/** * */ package com.tzquery.fsn.dao.impl; import com.gettec.fsnip.fsn.exception.DaoException; import com.gettec.fsnip.fsn.model.facility.FacilityInfo; import com.gettec.fsnip.fsn.model.facility.FacilityMaintenanceRecord; import com.gettec.fsnip.fsn.model.member.Member; import com.gettec.fsnip.fsn.model.operate.OperateInfo; import com.gettec.fsnip.fsn.model.procurement.ProcurementDispose; import com.gettec.fsnip.fsn.model.procurement.ProcurementInfo; import com.gettec.fsnip.fsn.model.procurement.ProcurementUsageRecord; import com.tzquery.fsn.dao.TzQueryDao; import com.tzquery.fsn.util.StringUtil; import com.tzquery.fsn.vo.*; import org.apache.commons.lang3.StringUtils; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.stereotype.Repository; import javax.persistence.EntityManager; import javax.persistence.PersistenceContext; import javax.persistence.Query; import java.util.ArrayList; import java.util.List; /** * @author ChenXiaolin 2015-11-30 */ @Repository("tzQueryDao") public class TzQueryDaoImpl implements TzQueryDao { Logger logger = LoggerFactory.getLogger(TzQueryDaoImpl.class); @PersistenceContext protected EntityManager entityManager; /** * ????? * @author ChenXiaolin 2015-12-01 * @param paramVO * @return * @throws DaoException */ @Override public List<TzQueryResponseProInfoVO> productQuery(TzQueryRequestParamVO paramVO) throws DaoException { try { StringBuffer sb = new StringBuffer(); sb.append(" SELECT pro.id,pro.barcode,pro.name,pro.format,proLicInfo.qs_no,pro.category"); sb.append(" FROM business_unit bus"); sb.append(" INNER JOIN tz_stock stock ON bus.id = stock.business_id"); sb.append(" INNER JOIN product pro ON pro.id = stock.product_id"); sb.append(" LEFT JOIN product_to_businessunit protobus ON pro.id = protobus.PRODUCT_ID"); sb.append(" LEFT JOIN production_license_info proLicInfo ON protobus.qs_id = proLicInfo.id"); /* ??? */ if (StringUtil.isNotEmpty(paramVO.getProName())) { sb.append(" AND pro.name LIKE '%").append(paramVO.getProName()).append("%'"); } /* ??? */ if (StringUtil.isNotEmpty(paramVO.getProBarcode())) { sb.append(" AND pro.barcode LIKE '%").append(paramVO.getProBarcode()).append("%'"); } /* ? */ if (StringUtil.isNotEmpty(paramVO.getProvice())) { String address = paramVO.getProvice(); if (StringUtil.isNotEmpty(paramVO.getCity())) { address = address + paramVO.getCity(); if (StringUtil.isNotEmpty(paramVO.getArea())) { address = address + paramVO.getArea(); } } sb.append(" AND bus.address LIKE '%").append(address).append("%'"); } /* ? */ //fsn?fsn?,?? sb.append(" GROUP BY pro.id"); return excuteSql(paramVO, sb.toString().replaceFirst("AND", "WHERE")); } catch (Exception e) { throw new DaoException( "TzQueryDaoImpl-->productQuery()??????", e); } } /** * Sql? * @author ChenXiaolin 2015-12-1 * @param paramVO * @param sql * @return */ @SuppressWarnings("unchecked") private List<TzQueryResponseProInfoVO> excuteSql(TzQueryRequestParamVO paramVO, String sql) throws DaoException { try { Query query = entityManager.createNativeQuery(sql); if (paramVO.getPage() > 0 && paramVO.getPageSize() > 0) { query.setFirstResult((paramVO.getPage() - 1) * paramVO.getPageSize()); query.setMaxResults(paramVO.getPageSize()); } List<Object[]> objects = query.getResultList(); return setproQuery(objects); } catch (Exception e) { throw new DaoException( "TzQueryDaoImpl-->excuteSql()sql??????", e); } } /** * ??? * @author ChenXiaolin 2015-12-1 * @param objects * @return */ private List<TzQueryResponseProInfoVO> setproQuery(List<Object[]> objects) { List<TzQueryResponseProInfoVO> list = new ArrayList<TzQueryResponseProInfoVO>(); TzQueryResponseProInfoVO vo = null; if (objects != null && objects.size() > 0) { for (int i = 0; i < objects.size(); i++) { Object[] object = objects.get(i); if (object != null) { vo = new TzQueryResponseProInfoVO(); vo.setProId(object[0] != null ? object[0].toString() : ""); vo.setProBarcode(object[1] != null ? object[1].toString() : ""); vo.setProName(object[2] != null ? object[2].toString() : ""); vo.setProFormat(object[3] != null ? object[3].toString() : ""); vo.setProQs(object[4] != null ? object[4].toString() : ""); vo.setProCategory(getFirstCattegory(object[5] != null ? object[5].toString() : "")); vo.setCheckWay(""); //FSN? vo.setSalesArea(""); //FSN? vo.setWhetherEexport(""); //FSN? list.add(vo); } } } return list; } /** * ?? * @author ChenXiaolin 2015-12-1 * @param paramVO * @return * @throws DaoException */ @Override public Long getproductQueryTotal(TzQueryRequestParamVO paramVO) throws DaoException { try { StringBuffer sb = new StringBuffer(); sb.append(" SELECT count(DISTINCT pro.id)"); sb.append(" FROM business_unit bus"); sb.append(" INNER JOIN tz_stock stock ON bus.id = stock.business_id"); sb.append(" INNER JOIN product pro ON pro.id = stock.product_id"); sb.append(" LEFT JOIN product_to_businessunit protobus ON pro.id = protobus.PRODUCT_ID"); sb.append(" LEFT JOIN production_license_info proLicInfo ON protobus.qs_id = proLicInfo.id"); /* ??? */ if (StringUtil.isNotEmpty(paramVO.getProName())) { sb.append(" AND pro.name LIKE '%").append(paramVO.getProName()).append("%'"); } /* ??? */ if (StringUtil.isNotEmpty(paramVO.getProBarcode())) { sb.append(" AND pro.barcode LIKE '%").append(paramVO.getProBarcode()).append("%'"); } /* ? */ if (StringUtil.isNotEmpty(paramVO.getProvice())) { String address = paramVO.getProvice(); if (StringUtil.isNotEmpty(paramVO.getCity())) { address = address + paramVO.getCity(); if (StringUtil.isNotEmpty(paramVO.getArea())) { address = address + paramVO.getArea(); } } sb.append(" AND bus.address LIKE '%").append(address).append("%'"); } /* ? */ /* sql */ Query query = entityManager.createNativeQuery(sb.toString().replaceFirst("AND", "WHERE")); return Long.parseLong(query.getSingleResult().toString()); } catch (Exception e) { throw new DaoException( "TzQueryDaoImpl-->getproductQueryTotal()???", e); } } /** * ??ID?? * @author ChenXiaolin 2015-12-2 * @param proId * @return * @throws DaoException */ @SuppressWarnings("unchecked") @Override public TzQueryResponseProInfoVO getProDetail(String proId) throws DaoException { try { StringBuffer sb = new StringBuffer(); sb.append(" SELECT * FROM ("); sb.append( " SELECT pro.name proName,pro.category proCte,pro.format profor,brand.name brandName,pro.barcode proBarcode,prolicinfo.qs_no qs FROM product pro"); sb.append(" LEFT JOIN product_to_businessunit protobus ON protobus.PRODUCT_ID = pro.id"); sb.append(" LEFT JOIN production_license_info prolicinfo ON protobus.qs_id = prolicinfo.id"); sb.append(" LEFT JOIN business_brand brand ON brand.id = pro.business_brand_id"); sb.append(" WHERE pro.id = ").append(Long.parseLong(proId)).append(") test"); Query query = entityManager.createNativeQuery(sb.toString()); List<Object[]> objects = query.getResultList(); return setProDetail(objects, proId); } catch (Exception e) { throw new DaoException( "TzQueryDaoImpl-->lookReport()??ID??,?", e); } } /** * ?? * @author ChenXiaolin 2015-12-2 * @param objects * @param proId * @return */ private TzQueryResponseProInfoVO setProDetail(List<Object[]> objects, String proId) { TzQueryResponseProInfoVO vo = null; if (objects != null && objects.size() > 0) { Object[] object = objects.get(0); if (object != null) { vo = new TzQueryResponseProInfoVO(); vo.setProName(object[0] != null ? object[0].toString() : ""); //??? vo.setProCategory(getFirstCattegory(object[1] != null ? object[1].toString() : "")); //?-- vo.setProFormat(object[2] != null ? object[2].toString() : ""); //? vo.setProBrand(object[3] != null ? object[3].toString() : ""); // vo.setProBarcode(object[4] != null ? object[4].toString() : ""); //?? vo.setProQs(object[5] != null ? object[5].toString() : ""); //??? vo.setIssueUnit(""); //????--FSN? vo.setIssueDate(""); //??--FSN? vo.setValidDate(""); //--FSN? vo.setProStatus(""); //?--FSN? vo.setWhetherEexport(""); //??--FSN? vo.setSalesArea(""); //--FSN? vo.setRelationType(""); //--FSN? vo.setCheckWay(""); //?FSN? vo.setProId(proId); //?ID // //???FSN? } } return vo; } /** * ?code??? * @author ChenXiaolin 2015-12-02 * @param code * @return */ @SuppressWarnings("unchecked") private String getFirstCattegory(String code) { if (StringUtil.isNotEmpty(code) && code.length() >= 2) { String sql = " SELECT name FROM product_category WHERE code = ?1"; Query query = entityManager.createNativeQuery(sql); query.setParameter(1, code.substring(0, 2)); List<Object> objects = query.getResultList(); if (objects != null && objects.size() > 0) { return objects.get(0).toString(); } } return ""; } /** * ??ID?? * @author ChenXiaolin 2015-12-1 * @param paramVO * @return * @throws DaoException */ @SuppressWarnings("unchecked") @Override public List<TzQueryResponseReportInfoVO> lookReport(TzQueryRequestParamVO paramVO) throws DaoException { try { StringBuffer sb = new StringBuffer(); sb.append( " SELECT pro.name,pro.barcode,proInst.batch_serial_no,result.service_order,result.test_type,"); sb.append("result.test_orgnization,IF(result.pass=1,'?','??') pass,result.fullPdfPath"); sb.append(" FROM product_instance proInst"); sb.append(" LEFT JOIN test_result result ON proInst.id = result.sample_id"); sb.append(" LEFT JOIN product pro ON pro.id = proInst.product_id"); sb.append(" WHERE result.del = 0 AND proInst.product_id = ").append(Long.parseLong(paramVO.getProId())); Query query = entityManager.createNativeQuery(sb.toString()); if (paramVO.getPage() > 0 && paramVO.getPageSize() > 0) { query.setFirstResult((paramVO.getPage() - 1) * paramVO.getPageSize()); query.setMaxResults(paramVO.getPageSize()); } List<Object[]> objects = query.getResultList(); return setLookReport(objects); } catch (Exception e) { throw new DaoException( "TzQueryDaoImpl-->lookReport()??ID??,?", e); } } /** * ?? * @author ChenXiaolin 2015-12-01 * @param objects * @return */ private List<TzQueryResponseReportInfoVO> setLookReport(List<Object[]> objects) { List<TzQueryResponseReportInfoVO> list = new ArrayList<TzQueryResponseReportInfoVO>(); TzQueryResponseReportInfoVO vo = null; if (objects != null && objects.size() > 0) { for (int i = 0; i < objects.size(); i++) { Object[] object = objects.get(i); if (object != null) { vo = new TzQueryResponseReportInfoVO(); vo.setProName(object[0] != null ? object[0].toString() : ""); vo.setProBarcode(object[1] != null ? object[1].toString() : ""); vo.setProBatch(object[2] != null ? object[2].toString() : ""); vo.setReportNum(object[3] != null ? object[3].toString() : ""); vo.setTestType(object[4] != null ? object[4].toString() : ""); vo.setTestUnit(object[5] != null ? object[5].toString() : ""); vo.setTestResult(object[6] != null ? object[6].toString() : ""); vo.setReportPdf(object[7] != null ? object[7].toString() : ""); list.add(vo); } } } return list; } /** * ?? * @author ChenXiaolin 2015-12-2 * @param paramVO * @return * @throws DaoException */ @Override public Long getLookReportTotal(TzQueryRequestParamVO paramVO) throws DaoException { try { StringBuffer sb = new StringBuffer(); sb.append(" SELECT count(*) FROM product_instance proInst"); sb.append(" LEFT JOIN test_result result ON proInst.id = result.sample_id"); sb.append(" LEFT JOIN product pro ON pro.id = proInst.product_id"); sb.append(" WHERE result.del = 0 AND proInst.product_id = ").append(paramVO.getProId()); Query query = entityManager.createNativeQuery(sb.toString()); return Long.parseLong(query.getSingleResult().toString()); } catch (Exception e) { throw new DaoException( "TzQueryDaoImpl-->getLookReportTotal()?? ,?", e); } } /** * ??ID?? * @author ChenXiaolin 2015-12-02 * @param paramVO * @return * @throws DaoException */ @SuppressWarnings("unchecked") @Override public List<TzQueryResponseBusVO> getSaleBusiness(TzQueryRequestParamVO paramVO) throws DaoException { try { StringBuffer sb_qyery = new StringBuffer(" and bus.other_address LIKE '"); if (StringUtil.isNotEmpty(paramVO.getProvice())) { //? sb_qyery.append(paramVO.getProvice()).append("-"); } if (StringUtil.isNotEmpty(paramVO.getCity())) { // sb_qyery.append(paramVO.getCity()).append("-"); } if (StringUtil.isNotEmpty(paramVO.getArea())) { // sb_qyery.append(paramVO.getArea()); } sb_qyery.append("%'"); StringBuffer sb = new StringBuffer(); sb.append(" select * from ("); sb.append( " SELECT pro.name proName,pro.category cat,pro.barcode bar, bus.id busId,bus.other_address oadd,bus.name busName,"); sb.append( "bus.license_no lic,bus.type type,bus.address addr,IF(bus.id=pro.producer_id,'','??') relation,pro.id"); sb.append(" FROM tz_stock stock "); sb.append(" INNER JOIN business_unit bus ON bus.id = stock.business_id "); sb.append(" INNER JOIN product pro ON pro.id = stock.product_id"); sb.append(" WHERE stock.product_id = ?1"); sb.append(sb_qyery); sb.append(" GROUP BY bus.id ) test"); Query query = entityManager.createNativeQuery(sb.toString()); query.setParameter(1, Long.parseLong(paramVO.getProId())); if (paramVO.getPage() > 0 && paramVO.getPageSize() > 0) { query.setFirstResult((paramVO.getPage() - 1) * paramVO.getPageSize()); query.setMaxResults(paramVO.getPageSize()); } List<Object[]> objects = query.getResultList(); return setSaleBusiness(objects); } catch (Exception e) { throw new DaoException( "TzQueryDaoImpl-->getSaleBusiness()??ID?? ,?", e); } } /** * ??? * @author ChenXiaolin 2015-12-02 * @param objects * @return */ private List<TzQueryResponseBusVO> setSaleBusiness(List<Object[]> objects) { List<TzQueryResponseBusVO> list = new ArrayList<TzQueryResponseBusVO>(); TzQueryResponseBusVO vo = null; if (objects != null && objects.size() > 0) { for (int i = 0; i < objects.size(); i++) { Object[] object = objects.get(i); if (object != null) { vo = new TzQueryResponseBusVO(); vo.setProName(object[0] != null ? object[0].toString() : ""); //??? vo.setProFirstCategory(getFirstCattegory(object[1] != null ? object[1].toString() : "")); //? vo.setProBarcode(object[2] != null ? object[2].toString() : ""); //?? vo.setBusId(object[3] != null ? object[3].toString() : ""); //?ID //??--Start String otherAddress = object[4] != null ? object[4].toString() : ""; String proviceStr = ""; String cityStr = ""; String areaStr = ""; if (!"".equals(otherAddress)) { String[] address1 = otherAddress.split("\\--"); if (address1.length >= 1) { String[] address2 = address1[0].split("\\-"); if (address2.length > 0) { if (address2.length == 1) { proviceStr = address2[0]; } if (address2.length == 2) { proviceStr = address2[0]; cityStr = address2[1]; } if (address2.length >= 3) { proviceStr = address2[0]; cityStr = address2[1]; areaStr = address2[2]; } } } } vo.setProvice(proviceStr); //?--? vo.setCity(cityStr); //?-- vo.setArea(areaStr); //?-- //??--End vo.setBusName(object[5] != null ? object[5].toString() : ""); //??? vo.setBusLic(object[6] != null ? object[6].toString() : ""); //?? vo.setBusType(object[7] != null ? object[7].toString() : ""); //? vo.setBusAddress(object[8] != null ? object[8].toString() : ""); //?? vo.setProRelation(object[9] != null ? object[9].toString() : ""); //? vo.setProId(object[10] != null ? object[10].toString() : ""); //?ID list.add(vo); } } } return list; } /** * ?? * @author ChenXiaolin 2015-12-02 * @param paramVO * @return * @throws DaoException */ @Override public Long getSaleBusinessTotal(TzQueryRequestParamVO paramVO) throws DaoException { try { StringBuffer sb_qyery = new StringBuffer(" and bus.other_address LIKE '"); if (StringUtil.isNotEmpty(paramVO.getProvice())) { //? sb_qyery.append(paramVO.getProvice()).append("-"); } if (StringUtil.isNotEmpty(paramVO.getCity())) { // sb_qyery.append(paramVO.getCity()).append("-"); } if (StringUtil.isNotEmpty(paramVO.getArea())) { // sb_qyery.append(paramVO.getArea()); } sb_qyery.append("%'"); StringBuffer sb = new StringBuffer(); sb.append(" SELECT count(DISTINCT bus.id) FROM tz_stock stock"); sb.append(" INNER JOIN business_unit bus ON bus.id = stock.business_id "); sb.append(" INNER JOIN product pro ON pro.id = stock.product_id"); sb.append(" WHERE stock.product_id = ?1"); sb.append(sb_qyery); Query query = entityManager.createNativeQuery(sb.toString()); query.setParameter(1, paramVO.getProId()); return Long.parseLong(query.getSingleResult().toString()); } catch (Exception e) { throw new DaoException( "TzQueryDaoImpl-->getSaleBusinessTotal()??,?", e); } } /** * ??ID?ID?? * @author ChenXiaolin 2015-12-03 * @param paramVO * @return * @throws DaoException */ @Override public List<TzQueryResponseTansDetailVO> getProQueryTransDetailList(TzQueryRequestParamVO paramVO) throws DaoException { try { StringBuffer sb = new StringBuffer(); sb.append(" SELECT busName,busLic,transType,proBatch,time,SUM(num) FROM ("); sb.append(" SELECT bus.name busName,bus.license_no busLic,'' transType,"); sb.append("accountInfo.product_batch proBatch,account.create_time time,accountInfo.product_num num"); sb.append(" FROM tz_business_account account"); sb.append(" LEFT JOIN business_unit bus ON bus.id = account.out_business_id"); sb.append( " INNER JOIN tz_business_account_info accountInfo ON account.id = accountInfo.business_account_id AND accountInfo.product_id = ?1"); sb.append(" WHERE account.in_business_id = ?2"); sb.append(" UNION"); sb.append(" SELECT bus.name busName,bus.license_no busLic,'' transType,"); sb.append("accountInfo.product_batch proBatch,account.create_time time,accountInfo.product_num num"); sb.append(" FROM tz_business_account account"); sb.append(" LEFT JOIN business_unit bus ON bus.id = account.in_business_id"); sb.append( " INNER JOIN tz_business_account_info accountInfo ON account.id = accountInfo.business_account_id AND accountInfo.product_id = ?3"); sb.append(" WHERE account.out_business_id = ?4) test"); /* ? */ if (StringUtil.isNotEmpty(paramVO.getTransType())) { if ("".equals(paramVO.getTransType())) {// sb.append(" and (transType = '' or transType ='')"); } else {// sb.append(" and transType = '").append(paramVO.getTransType()).append("'"); } } if (StringUtil.isNotEmpty(paramVO.getProBatch())) { sb.append(" and proBatch LIKE '%").append(paramVO.getProBatch()).append("%'"); } if (StringUtil.isNotEmpty(paramVO.getTransSDate())) { sb.append(" and time >= '").append(paramVO.getTransSDate()).append(" 00:00:00").append("'"); } if (StringUtil.isNotEmpty(paramVO.getTransEDate())) { sb.append(" and time <= '").append(paramVO.getTransEDate()).append(" 23:59:59").append("'"); } String sql = ""; if (sb.toString().indexOf("test and ") > -1) { sql = sb.toString().replace("test and", "test where"); } else { sql = sb.toString(); } sql = sql + " GROUP BY test.proBatch,test.time ORDER BY test.time desc"; return excuteSqlToTransDetail(sql, paramVO); } catch (Exception e) { throw new DaoException( "TzQueryDaoImpl-->getProQueryTransDetailList()??ID?ID??,?", e); } } /** * ?sql * @author ChenXiaolin 2015-12-03 * @param paramVO * @param sql * @return */ @SuppressWarnings("unchecked") private List<TzQueryResponseTansDetailVO> excuteSqlToTransDetail(String sql, TzQueryRequestParamVO paramVO) throws DaoException { try { Query query = entityManager.createNativeQuery(sql); query.setParameter(1, paramVO.getProId()); query.setParameter(2, paramVO.getBusId()); query.setParameter(3, paramVO.getProId()); query.setParameter(4, paramVO.getBusId()); if (paramVO.getPage() > 0 && paramVO.getPageSize() > 0) { query.setFirstResult((paramVO.getPage() - 1) * paramVO.getPageSize()); query.setMaxResults(paramVO.getPageSize()); } List<Object[]> objects = query.getResultList(); return setTransDetail(objects); } catch (Exception e) { throw new DaoException( "TzQueryDaoImpl-->excuteSqlToTransDetail()?sql,?", e); } } /** * ?VO * @author ChenXiaolin 2015-12-3 * @param objects * @return */ private List<TzQueryResponseTansDetailVO> setTransDetail(List<Object[]> objects) { List<TzQueryResponseTansDetailVO> list = new ArrayList<TzQueryResponseTansDetailVO>(); TzQueryResponseTansDetailVO vo = null; if (objects != null && objects.size() > 0) { for (int i = 0; i < objects.size(); i++) { Object[] object = objects.get(i); if (object != null && object.length > 0) { vo = new TzQueryResponseTansDetailVO(); vo.setTransTarget(object[0] != null ? object[0].toString() : ""); vo.setLicense(object[1] != null ? object[1].toString() : ""); vo.setTransType(object[2] != null ? object[2].toString() : ""); vo.setProBatch(object[3] != null ? object[3].toString() : ""); vo.setTransDate(object[4] != null ? StringUtil.datestrToDataStr(object[4].toString(), "yyyy-MM-dd HH:mm:ss") : ""); vo.setTransAmount(object[5] != null ? Long.parseLong(object[5].toString()) : 0); list.add(vo); } } } return list; } /** * ?? * @author ChenXiaolin 2015-12-03 * @param paramVO * @return * @throws DaoException */ @Override public Long getProQueryTransDetailTotal(TzQueryRequestParamVO paramVO) throws DaoException { try { StringBuffer sb = new StringBuffer(); sb.append(" SELECT count(*) FROM("); sb.append(" SELECT 1 FROM ("); sb.append( " SELECT '' transType,accountInfo.product_batch proBatch,account.create_time time FROM tz_business_account account"); sb.append(" LEFT JOIN business_unit bus ON bus.id = account.out_business_id"); sb.append( " INNER JOIN tz_business_account_info accountInfo ON account.id = accountInfo.business_account_id AND accountInfo.product_id = ?1"); sb.append(" WHERE account.in_business_id = ?2"); sb.append(" UNION"); sb.append( " SELECT '' transType,accountInfo.product_batch proBatch,account.create_time time FROM tz_business_account account"); sb.append(" LEFT JOIN business_unit bus ON bus.id = account.in_business_id"); sb.append( " INNER JOIN tz_business_account_info accountInfo ON account.id = accountInfo.business_account_id AND accountInfo.product_id = ?3"); sb.append(" WHERE account.out_business_id = ?4) test"); /* ? */ if (StringUtil.isNotEmpty(paramVO.getTransType())) { if ("".equals(paramVO.getTransType())) {// sb.append(" and (transType = '' or transType ='')"); } else {// sb.append(" and transType = '").append(paramVO.getTransType()).append("'"); } } if (StringUtil.isNotEmpty(paramVO.getProBatch())) { sb.append(" and proBatch LIKE '%").append(paramVO.getProBatch()).append("%'"); } if (StringUtil.isNotEmpty(paramVO.getTransSDate())) { sb.append(" and time >= '").append(paramVO.getTransSDate()).append(" 00:00:00").append("'"); } if (StringUtil.isNotEmpty(paramVO.getTransEDate())) { sb.append(" and time <= '").append(paramVO.getTransEDate()).append(" 23:59:59").append("'"); } String sql = ""; if (sb.toString().indexOf("test and ") > -1) { sql = sb.toString().replace("test and", "test where"); } else { sql = sb.toString(); } sql = sql + " GROUP BY test.proBatch,test.time) testTotal"; Query query = entityManager.createNativeQuery(sql); query.setParameter(1, paramVO.getProId()); query.setParameter(2, paramVO.getBusId()); query.setParameter(3, paramVO.getProId()); query.setParameter(4, paramVO.getBusId()); return Long.parseLong(query.getSingleResult().toString()); } catch (Exception e) { throw new DaoException( "TzQueryDaoImpl-->getProQueryTransDetailTotal()??,?", e); } } /** * ??????? * @author ChenXiaolin 2015-12-04 * @param paramVO * @return * @throws DaoException */ @SuppressWarnings("unchecked") @Override public List<TzQueryResponseBusVO> getAccountInfo(TzQueryRequestParamVO paramVO) throws DaoException { try { String changeSql = "SELECT DISTINCT outBus.id,outBus.name,outBus.license_no,outBus.type,outBus.address"; StringBuffer sql = new StringBuffer(); StringBuffer publicSql = new StringBuffer(); publicSql.append(" FROM tz_business_account account"); publicSql.append(" INNER JOIN business_unit inBus ON inBus.id = account.in_business_id"); publicSql.append(" INNER JOIN business_unit outBus ON outBus.id = account.out_business_id"); if (paramVO.getSalesType() == 0) {// sql.append(changeSql); sql.append(publicSql); sql.append(" WHERE inBus.id = ?1"); } else if (paramVO.getSalesType() == 1) {// String temporarySql = changeSql.replace("outBus", "inBus"); sql.append(temporarySql); sql.append(publicSql); sql.append(" WHERE outBus.id = ?1"); } Query query = entityManager.createNativeQuery(sql.toString()); query.setParameter(1, paramVO.getFirstBusId()); if (paramVO.getPage() > 0 && paramVO.getPageSize() > 0) { query.setFirstResult((paramVO.getPage() - 1) * paramVO.getPageSize()); query.setMaxResults(paramVO.getPageSize()); } List<Object[]> objects = query.getResultList(); return setAccounInfo(objects, paramVO); } catch (Exception e) { throw new DaoException( "TzQueryDaoImpl-->getAccountInfo()???????,?", e); } } /** * ??? * @author ChenXiaolin 2015-12-04 * @param objects * @param paramVO * @return */ private List<TzQueryResponseBusVO> setAccounInfo(List<Object[]> objects, TzQueryRequestParamVO paramVO) { List<TzQueryResponseBusVO> list = new ArrayList<TzQueryResponseBusVO>(); TzQueryResponseBusVO vo = null; if (objects != null && objects.size() > 0) { for (int i = 0; i < objects.size(); i++) { Object[] object = objects.get(i); if (object != null && object.length > 0) { vo = new TzQueryResponseBusVO(); vo.setBusId(object[0] != null ? object[0].toString() : ""); //?ID vo.setBusName(object[1] != null ? object[1].toString() : ""); //??? vo.setBusLic(object[2] != null ? object[2].toString() : ""); //??? vo.setBusType(object[3] != null ? object[3].toString() : ""); //? vo.setBusAddress(object[4] != null ? object[4].toString() : ""); //?? vo.setFirstBusName(paramVO.getBusName()); //???:?? list.add(vo); } } } return list; } /** * ??? * @author ChenXiaolin 2015-12-04 * @param paramVO * @return * @throws DaoException */ @Override public Long getAccountInfoTotal(TzQueryRequestParamVO paramVO) throws DaoException { try { String changeSql = "SELECT count(DISTINCT outBus.id)"; StringBuffer sql = new StringBuffer(); StringBuffer publicSql = new StringBuffer(); publicSql.append(" FROM tz_business_account account"); publicSql.append(" INNER JOIN business_unit inBus ON inBus.id = account.in_business_id"); publicSql.append(" INNER JOIN business_unit outBus ON outBus.id = account.out_business_id"); if (paramVO.getSalesType() == 0) {// sql.append(changeSql); sql.append(publicSql); sql.append(" WHERE inBus.id = ?1"); } else if (paramVO.getSalesType() == 1) {// String temporarySql = changeSql.replace("outBus", "inBus"); sql.append(temporarySql); sql.append(publicSql); sql.append(" WHERE outBus.id = ?1"); } Query query = entityManager.createNativeQuery(sql.toString()); query.setParameter(1, paramVO.getFirstBusId()); return Long.parseLong(query.getSingleResult().toString()); } catch (Exception e) { throw new DaoException( "TzQueryDaoImpl-->getAccountInfoTotal()???,?", e); } } /** * ??? * @author ChenXiaolin 2015-12-04 * @param paramVO * @return * @throws DaoException */ @SuppressWarnings("unchecked") @Override public List<TzQueryResponseTansDetailVO> getBusQueryTransDetail(TzQueryRequestParamVO paramVO) throws DaoException { try { StringBuffer sb = new StringBuffer(); sb.append( " SELECT pro.barcode,pro.name,pro.format,acInfo.product_batch,acInfo.production_date,account.create_time,acInfo.product_num"); sb.append(" FROM tz_business_account account"); sb.append(" INNER JOIN tz_business_account_info acInfo ON account.id = acInfo.business_account_id"); sb.append(" INNER JOIN product pro ON pro.id = acInfo.product_id"); sb.append(" INNER JOIN business_unit inBus ON inBus.id = account.in_business_id"); sb.append(" INNER JOIN business_unit outBus ON outBus.id = account.out_business_id"); if (paramVO.getSalesType() == 0) {//????? sb.append(" WHERE account.out_business_id = ?1 AND inBus.id = ?2"); } else if (paramVO.getSalesType() == 1) {//????? sb.append(" WHERE account.in_business_id = ?1 AND outBus.id = ?2"); } /* ? */ if (StringUtil.isNotEmpty(paramVO.getProBarcode())) {//??? sb.append(" AND pro.barcode LIKE '%").append(paramVO.getProBarcode()).append("%'"); } if (StringUtil.isNotEmpty(paramVO.getProName())) {//??? sb.append(" AND pro.name LIKE '%").append(paramVO.getProName()).append("%'"); } if (StringUtil.isNotEmpty(paramVO.getTransSDate())) { sb.append(" AND account.create_time >= '").append(paramVO.getTransSDate()).append(" 00:00:00") .append("'"); } if (StringUtil.isNotEmpty(paramVO.getTransEDate())) { sb.append(" AND account.create_time <= '").append(paramVO.getTransEDate()).append(" 23:59:59") .append("'"); } sb.append(" ORDER BY account.create_time DESC"); /* sql */ Query query = entityManager.createNativeQuery(sb.toString()); query.setParameter(1, paramVO.getBusId()); query.setParameter(2, paramVO.getFirstBusId()); if (paramVO.getPage() > 0 && paramVO.getPageSize() > 0) { query.setFirstResult((paramVO.getPage() - 1) * paramVO.getPageSize()); query.setMaxResults(paramVO.getPageSize()); } List<Object[]> objects = query.getResultList(); return setBusQueryDetail(objects); } catch (Exception e) { throw new DaoException( "TzQueryDaoImpl-->getBusQueryTransDetail()???,?", e); } } /** * ??Vo * @author ChenXiaolin 2015-12-04 * @param objects * @return */ private List<TzQueryResponseTansDetailVO> setBusQueryDetail(List<Object[]> objects) { List<TzQueryResponseTansDetailVO> list = new ArrayList<TzQueryResponseTansDetailVO>(); TzQueryResponseTansDetailVO vo = null; if (objects != null && objects.size() > 0) { for (int i = 0; i < objects.size(); i++) { Object[] object = objects.get(i); if (object != null && object.length > 0) { vo = new TzQueryResponseTansDetailVO(); vo.setProBarcode(object[0] != null ? object[0].toString() : ""); vo.setProName(object[1] != null ? object[1].toString() : ""); vo.setProFormat(object[2] != null ? object[2].toString() : ""); vo.setProBatch(object[3] != null ? object[3].toString() : ""); vo.setProDate(object[4] != null ? object[4].toString() : ""); vo.setTransDate(object[5] != null ? StringUtil.datestrToDataStr(object[5].toString(), "yyyy-MM-dd HH:mm:ss") : ""); vo.setTransAmount(object[6] != null ? Long.parseLong(object[6].toString()) : 0); list.add(vo); } } } return list; } /** * ??? * @author ChenXiaolin 2015-12-04 * @param paramVO * @return * @throws DaoException */ @Override public Long getBusQueryAccountInfoTotal(TzQueryRequestParamVO paramVO) throws DaoException { try { StringBuffer sb = new StringBuffer(); sb.append(" SELECT count(*) FROM tz_business_account account"); sb.append(" INNER JOIN tz_business_account_info acInfo ON account.id = acInfo.business_account_id"); sb.append(" INNER JOIN product pro ON pro.id = acInfo.product_id"); sb.append(" INNER JOIN business_unit inBus ON inBus.id = account.in_business_id"); sb.append(" INNER JOIN business_unit outBus ON outBus.id = account.out_business_id"); if (paramVO.getSalesType() == 0) {//????? sb.append(" WHERE account.out_business_id = ?1 AND inBus.id = ?2"); } else if (paramVO.getSalesType() == 1) {//????? sb.append(" WHERE account.in_business_id = ?1 AND outBus.id = ?2"); } /* ? */ if (StringUtil.isNotEmpty(paramVO.getProBarcode())) {//??? sb.append(" AND pro.barcode LIKE '%").append(paramVO.getProBarcode()).append("%'"); } if (StringUtil.isNotEmpty(paramVO.getProName())) {//??? sb.append(" AND pro.name LIKE '%").append(paramVO.getProName()).append("%'"); } if (StringUtil.isNotEmpty(paramVO.getTransSDate())) { sb.append(" AND account.create_time >= '").append(paramVO.getTransSDate()).append(" 00:00:00") .append("'"); } if (StringUtil.isNotEmpty(paramVO.getTransEDate())) { sb.append(" AND account.create_time <= '").append(paramVO.getTransEDate()).append(" 23:59:59") .append("'"); } /* sql */ Query query = entityManager.createNativeQuery(sb.toString()); query.setParameter(1, paramVO.getBusId()); query.setParameter(2, paramVO.getFirstBusId()); return Long.parseLong(query.getSingleResult().toString()); } catch (Exception e) { throw new DaoException( "TzQueryDaoImpl-->getBusQueryAccountInfoTotal()???,?", e); } } /** * ??????ID * @author ChenXiaolin 2015-12-05 * @param busName * @return * @throws DaoException */ @SuppressWarnings("unchecked") public String getBusIdByBusName(String busName) throws DaoException { try { String sql = "SELECT id from business_unit WHERE name = ?1"; Query query = entityManager.createNativeQuery(sql); query.setParameter(1, busName); List<Object> objects = query.getResultList(); if (objects != null && objects.size() > 0) { return objects.get(0).toString(); } return ""; } catch (Exception e) { throw new DaoException( "TzQueryDaoImpl-->getBusIdByBusName()??????ID,?", e); } } /** * ??????? * @author ChenXiaolin 2015-12-14 * @param paramVO * @return * @throws DaoException */ @SuppressWarnings("unchecked") @Override public List<TzQueryResponseProListVO> getBusQueryProList(TzQueryRequestParamVO paramVO) throws DaoException { try { StringBuffer sb = new StringBuffer(); sb.append(" SELECT * FROM ( "); sb.append(" SELECT pro.id id,pro.name proN,pro.barcode pb,pro.format f,pro.category cet"); sb.append(" FROM tz_business_account account"); sb.append(" INNER JOIN business_unit bus ON bus.id = account.out_business_id"); sb.append(" INNER JOIN tz_business_account_info acInfo ON acInfo.business_account_id = account.id"); sb.append(" INNER JOIN product pro ON pro.id = acInfo.product_id"); sb.append(" WHERE bus.name = '").append(paramVO.getBusName()).append("'"); if (StringUtil.isNotEmpty(paramVO.getLicenseNo())) {//?? sb.append(" AND bus.license_no = '").append(paramVO.getLicenseNo()).append("'"); } /* ? */ if (StringUtil.isNotEmpty(paramVO.getProName())) {//??? sb.append(" AND pro.name LIKE '%").append(paramVO.getProName()).append("%'"); } if (StringUtil.isNotEmpty(paramVO.getFormat())) {//? sb.append(" AND pro.format LIKE '%").append(paramVO.getFormat()).append("%'"); } if (StringUtil.isNotEmpty(paramVO.getProBarcode())) {//??? sb.append(" AND pro.barcode LIKE '%").append(paramVO.getProBarcode()).append("%'"); } /* ?????? */ //FSN?? sb.append(" GROUP BY pro.id ) test"); /* sql */ Query query = entityManager.createNativeQuery(sb.toString()); if (paramVO.getPage() > 0 && paramVO.getPageSize() > 0) { query.setFirstResult((paramVO.getPage() - 1) * paramVO.getPageSize()); query.setMaxResults(paramVO.getPageSize()); } List<Object[]> objects = query.getResultList(); return setBusQueryProList(objects); } catch (Exception e) { throw new DaoException( "TzQueryDaoImpl-->getBusQueryProList()???????,?", e); } } /** * ????????? * @author ChenXiaolin 2015-12-14 * @param objects * @return */ private List<TzQueryResponseProListVO> setBusQueryProList(List<Object[]> objects) throws DaoException { List<TzQueryResponseProListVO> list = new ArrayList<TzQueryResponseProListVO>(); TzQueryResponseProListVO vo = null; if (objects != null && objects.size() > 0) { for (int i = 0; i < objects.size(); i++) { Object[] object = objects.get(i); if (object != null && object.length > 0) { vo = new TzQueryResponseProListVO(); vo.setProId(object[0] != null ? object[0].toString() : ""); //?ID vo.setProName(object[1] != null ? object[1].toString() : ""); //??? vo.setProBarcode(object[2] != null ? object[2].toString() : ""); //?? vo.setProFormat(object[3] != null ? object[3].toString() : ""); //? vo.setProCategory(getFirstCattegory(object[4] != null ? object[4].toString() : "")); //?-- vo.setProQs(getQsByProId(vo.getProId())); //? vo.setStandardStr(getStandarByProId(vo.getProId())); // vo.setIssueUnit(""); //????--FSN? vo.setIssueDate(""); //??--FSN? vo.setValidDate(""); //--FSN? vo.setProStatus(""); //?--FSN? vo.setWhetherEexport(""); //??--FSN? vo.setSalesArea(""); //--FSN? vo.setRelationType(""); //--FSN? vo.setCheckWay(""); //?--FSN? list.add(vo); } } } return list; } /** * ??ID? * @author ChenXiaolin 2015-12-14 * @param proId * @return */ @SuppressWarnings("unchecked") private String getStandarByProId(String proId) throws DaoException { try { StringBuffer sb = new StringBuffer(); sb.append(" SELECT cateInfo.name FROM product_to_regularity regluar"); sb.append(" INNER JOIN product_category_info cateInfo ON cateInfo.id = regluar.regularity_id"); sb.append(" WHERE regluar.product_id = ?1 LIMIT 0,1"); Query query = entityManager.createNativeQuery(sb.toString()); query.setParameter(1, Long.parseLong(proId)); List<Object> list = query.getResultList(); if (list != null && list.size() > 0) { return list.get(0).toString(); } return ""; } catch (NumberFormatException e) { throw new DaoException( "TzQueryDaoImpl-->getStandarByProId()??ID?,?", e); } } /** * ??ID?qs * @author ChenXiaolin 2015-12-14 * @param proId * @return */ @SuppressWarnings("unchecked") private String getQsByProId(String proId) throws DaoException { try { StringBuffer sb = new StringBuffer(); sb.append(" SELECT prolic.qs_no FROM product_to_businessunit protobus"); sb.append(" INNER JOIN production_license_info prolic ON protobus.qs_id = prolic.id"); sb.append(" WHERE protobus.PRODUCT_ID = ?1 LIMIT 0,1"); Query query = entityManager.createNativeQuery(sb.toString()); query.setParameter(1, Long.parseLong(proId)); List<Object> list = query.getResultList(); if (list != null && list.size() > 0) { return list.get(0).toString(); } return ""; } catch (NumberFormatException e) { throw new DaoException("TzQueryDaoImpl-->getQsByProId()??ID?qs,?", e); } } /** * ??????? * @author ChenXiaolin 2015-12-14 * @param paramVO * @return * @throws DaoException */ @Override public Long getBusQueryProListTotal(TzQueryRequestParamVO paramVO) throws DaoException { try { StringBuffer sb = new StringBuffer(); sb.append(" SELECT COUNT(DISTINCT pro.id) FROM tz_business_account account"); sb.append(" INNER JOIN business_unit bus ON bus.id = account.out_business_id"); sb.append(" INNER JOIN tz_business_account_info acInfo ON acInfo.business_account_id = account.id"); sb.append(" INNER JOIN product pro ON pro.id = acInfo.product_id"); sb.append(" WHERE bus.name = '").append(paramVO.getBusName()).append("'"); if (StringUtil.isNotEmpty(paramVO.getLicenseNo())) {//?? sb.append(" AND bus.license_no = '").append(paramVO.getLicenseNo()).append("'"); } /* ? */ if (StringUtil.isNotEmpty(paramVO.getProName())) {//??? sb.append(" AND pro.name LIKE '%").append(paramVO.getProName()).append("%'"); } if (StringUtil.isNotEmpty(paramVO.getFormat())) {//? sb.append(" AND pro.format LIKE '%").append(paramVO.getFormat()).append("%'"); } if (StringUtil.isNotEmpty(paramVO.getProBarcode())) {//??? sb.append(" AND pro.barcode LIKE '%").append(paramVO.getProBarcode()).append("%'"); } /* ?????? */ //FSN?? /* sql */ Query query = entityManager.createNativeQuery(sb.toString()); return Long.parseLong(query.getSingleResult().toString()); } catch (Exception e) { throw new DaoException( "TzQueryDaoImpl-->getBusQueryProListTotal()???????,?", e); } } /** * ???????? * @param paramVO * @return * @throws DaoException */ @Override public List<ProcurementInfo> getRawMaterialInfoList(TzQueryRequestParamVO paramVO, int type) throws DaoException { try { List<ProcurementInfo> list = new ArrayList<ProcurementInfo>(); StringBuffer sql = new StringBuffer(); sql.append("SELECT p.* FROM procurement_info p "); sql.append("LEFT JOIN business_unit b ON b.organization=p.organization_id "); sql.append("WHERE b.`name`=?1 AND p.type=?2 "); if (StringUtils.isNotBlank(paramVO.getProName())) { sql.append(" AND p.name LIKE ?3 "); } sql.append(" ORDER BY p.procurement_date desc "); Query query = entityManager.createNativeQuery(sql.toString(), ProcurementInfo.class); query.setParameter(1, paramVO.getBusName()); query.setParameter(2, type); if (StringUtils.isNotBlank(paramVO.getProName())) { query.setParameter(3, "%" + paramVO.getProName() + "%"); } if (paramVO.getPage() > 0 && paramVO.getPageSize() > 0) { query.setFirstResult((paramVO.getPage() - 1) * paramVO.getPageSize()); query.setMaxResults(paramVO.getPageSize()); } list = query.getResultList(); return list; } catch (Exception e) { throw new DaoException( "TzQueryDaoImpl-->getRawMaterialInfoList()?????????", e); } } /** * ????????? * @param paramVO * @return * @throws DaoException */ @Override public Long getRawMaterialInfoTotal(TzQueryRequestParamVO paramVO, int type) throws DaoException { try { StringBuffer sql = new StringBuffer(); sql.append("SELECT count(*) FROM procurement_info p "); sql.append("LEFT JOIN business_unit b ON b.organization=p.organization_id "); sql.append("WHERE b.`name`=?1 AND p.type=?2 "); if (StringUtils.isNotBlank(paramVO.getProName())) { sql.append(" AND p.name LIKE ?3 "); } Query query = entityManager.createNativeQuery(sql.toString()); query.setParameter(1, paramVO.getBusName()); query.setParameter(2, type); if (StringUtils.isNotBlank(paramVO.getProName())) { query.setParameter(3, "%" + paramVO.getProName() + "%"); } return Long.parseLong(query.getSingleResult().toString()); } catch (Exception e) { throw new DaoException( "TzQueryDaoImpl-->getRawMaterialInfoTotal()??????????", e); } } /** * ?????? * @param paramVO * @return * @throws DaoException */ @Override public List<Member> getMemberInfoList(TzQueryRequestParamVO paramVO) throws DaoException { try { List<Member> list = new ArrayList<Member>(); StringBuffer sql = new StringBuffer(); sql.append("SELECT m.* FROM member m LEFT JOIN business_unit b ON m.orgId=b.id "); sql.append("WHERE b.`name`=?1 "); if (StringUtils.isNotBlank(paramVO.getProName())) { sql.append(" AND ( m.name LIKE ?2 OR m.position LIKE ?3 OR m.identificationNo LIKE ?4 )"); } sql.append(" ORDER BY m.id desc "); Query query = entityManager.createNativeQuery(sql.toString(), Member.class); query.setParameter(1, paramVO.getBusName()); if (StringUtils.isNotBlank(paramVO.getProName())) { query.setParameter(2, "%" + paramVO.getProName() + "%"); query.setParameter(3, "%" + paramVO.getProName() + "%"); query.setParameter(4, "%" + paramVO.getProName() + "%"); } if (paramVO.getPage() > 0 && paramVO.getPageSize() > 0) { query.setFirstResult((paramVO.getPage() - 1) * paramVO.getPageSize()); query.setMaxResults(paramVO.getPageSize()); } list = query.getResultList(); return list; } catch (Exception e) { throw new DaoException( "TzQueryDaoImpl-->getMemberInfoList()???????", e); } } /** * ??????? * @param paramVO * @return * @throws DaoException */ @Override public Long getMemberInfoTotal(TzQueryRequestParamVO paramVO) throws DaoException { try { StringBuffer sql = new StringBuffer(); sql.append("SELECT count(*) FROM member m LEFT JOIN business_unit b ON m.orgId=b.id "); sql.append("WHERE b.`name`=?1 "); if (StringUtils.isNotBlank(paramVO.getProName())) { sql.append(" AND ( m.name LIKE ?2 OR m.position LIKE ?3 OR m.identificationNo LIKE ?4 )"); } Query query = entityManager.createNativeQuery(sql.toString()); query.setParameter(1, paramVO.getBusName()); if (StringUtils.isNotBlank(paramVO.getProName())) { query.setParameter(2, "%" + paramVO.getProName() + "%"); query.setParameter(3, "%" + paramVO.getProName() + "%"); query.setParameter(4, "%" + paramVO.getProName() + "%"); } return Long.parseLong(query.getSingleResult().toString()); } catch (Exception e) { throw new DaoException( "TzQueryDaoImpl-->getMemberInfoTotal()????????", e); } } /** * ?????? * @param paramVO * @return * @throws DaoException */ @Override public List<FacilityInfo> getFacilityInfoList(TzQueryRequestParamVO paramVO) throws DaoException { try { List<FacilityInfo> list = new ArrayList<FacilityInfo>(); StringBuffer sql = new StringBuffer(); sql.append("SELECT f.* FROM facility_info f LEFT JOIN business_unit b ON f.business_id=b.id "); sql.append("WHERE b.`name`=?1 "); if (StringUtils.isNotBlank(paramVO.getProName())) { sql.append(" AND f.facility_name LIKE ?2 "); } sql.append(" ORDER BY f.buying_time desc "); Query query = entityManager.createNativeQuery(sql.toString(), FacilityInfo.class); query.setParameter(1, paramVO.getBusName()); if (StringUtils.isNotBlank(paramVO.getProName())) { query.setParameter(2, "%" + paramVO.getProName() + "%"); } if (paramVO.getPage() > 0 && paramVO.getPageSize() > 0) { query.setFirstResult((paramVO.getPage() - 1) * paramVO.getPageSize()); query.setMaxResults(paramVO.getPageSize()); } list = query.getResultList(); return list; } catch (Exception e) { throw new DaoException( "TzQueryDaoImpl-->getFacilityInfoList()???????", e); } } /** * ??????? * @param paramVO * @return * @throws DaoException */ @Override public Long getFacilityInfoTotal(TzQueryRequestParamVO paramVO) throws DaoException { try { StringBuffer sql = new StringBuffer(); sql.append("SELECT count(*) FROM facility_info f LEFT JOIN business_unit b ON f.business_id=b.id "); sql.append("WHERE b.`name`=?1 "); if (StringUtils.isNotBlank(paramVO.getProName())) { sql.append(" AND f.facility_name LIKE ?2 "); } Query query = entityManager.createNativeQuery(sql.toString()); query.setParameter(1, paramVO.getBusName()); if (StringUtils.isNotBlank(paramVO.getProName())) { query.setParameter(2, "%" + paramVO.getProName() + "%"); } return Long.parseLong(query.getSingleResult().toString()); } catch (Exception e) { throw new DaoException( "TzQueryDaoImpl-->getFacilityInfoTotal()????????", e); } } /** * ?????? * @param paramVO * @return * @throws DaoException */ @Override public OperateInfo getOperateInfo(TzQueryRequestParamVO paramVO) throws DaoException { try { List<OperateInfo> list = new ArrayList<OperateInfo>(); StringBuffer sql = new StringBuffer(); sql.append("SELECT * FROM operate_info o LEFT JOIN business_unit b ON o.business_id=b.id "); sql.append("WHERE b.`name`=?1 "); Query query = entityManager.createNativeQuery(sql.toString(), OperateInfo.class); query.setParameter(1, paramVO.getBusName()); list = query.getResultList(); if (list != null && list.size() > 0) { return list.get(0); } return null; } catch (Exception e) { throw new DaoException("TzQueryDaoImpl-->getOperateInfo()???????", e); } } /** * ?id???? * @param paramVO * @param rId * @return * @throws DaoException */ @Override public List<ProcurementUsageRecord> getProcurementUsageRecordList(TzQueryRequestParamVO paramVO, Long rId) throws DaoException { try { List<ProcurementUsageRecord> list = new ArrayList<ProcurementUsageRecord>(); StringBuffer sql = new StringBuffer(); sql.append("SELECT * FROM procurement_usage_record WHERE procurement_id=?1 ORDER BY use_date desc"); Query query = entityManager.createNativeQuery(sql.toString(), ProcurementUsageRecord.class); query.setParameter(1, rId); if (paramVO.getPage() > 0 && paramVO.getPageSize() > 0) { query.setFirstResult((paramVO.getPage() - 1) * paramVO.getPageSize()); query.setMaxResults(paramVO.getPageSize()); } list = query.getResultList(); return list; } catch (Exception e) { throw new DaoException( "TzQueryDaoImpl-->getProcurementUsageRecordList()?id?????", e); } } /** * ?id????? * @param paramVO * @param rId * @return * @throws DaoException */ @Override public Long getProcurementUsageRecordTotal(TzQueryRequestParamVO paramVO, Long rId) throws DaoException { try { StringBuffer sql = new StringBuffer(); sql.append("SELECT count(*) FROM procurement_usage_record WHERE procurement_id=?1 "); Query query = entityManager.createNativeQuery(sql.toString()); query.setParameter(1, rId); return Long.parseLong(query.getSingleResult().toString()); } catch (Exception e) { throw new DaoException( "TzQueryDaoImpl-->getProcurementUsageRecordTotal()?id??????", e); } } /** * ?id?????? * @param paramVO * @return * @throws DaoException */ @Override public List<ProcurementDispose> getProcurementDisposeList(TzQueryRequestParamVO paramVO, int type) throws DaoException { try { List<ProcurementDispose> list = new ArrayList<ProcurementDispose>(); StringBuffer sql = new StringBuffer(); sql.append( "SELECT p.* FROM procurement_dispose p LEFT JOIN business_unit b ON b.organization=p.organization_id WHERE b.name=?1 AND p.type=?2 "); if (StringUtils.isNotBlank(paramVO.getProName())) { sql.append(" AND p.procurement_name LIKE ?3 "); } sql.append(" ORDER BY dispose_date desc "); Query query = entityManager.createNativeQuery(sql.toString(), ProcurementDispose.class); query.setParameter(1, paramVO.getBusName()); query.setParameter(2, type); if (StringUtils.isNotBlank(paramVO.getProName())) { query.setParameter(3, "%" + paramVO.getProName() + "%"); } if (paramVO.getPage() > 0 && paramVO.getPageSize() > 0) { query.setFirstResult((paramVO.getPage() - 1) * paramVO.getPageSize()); query.setMaxResults(paramVO.getPageSize()); } list = query.getResultList(); return list; } catch (Exception e) { throw new DaoException( "TzQueryDaoImpl-->getProcurementDisposeList()?id???????", e); } } /** * ?id??????? * @param paramVO * @return * @throws DaoException */ @Override public Long getProcurementDisposeTotal(TzQueryRequestParamVO paramVO, int type) throws DaoException { try { StringBuffer sql = new StringBuffer(); sql.append( "SELECT count(*) FROM procurement_dispose p LEFT JOIN business_unit b ON b.organization=p.organization_id WHERE b.name=?1 AND p.type=?2 "); if (StringUtils.isNotBlank(paramVO.getProName())) { sql.append(" AND p.procurement_name LIKE ?3 "); } Query query = entityManager.createNativeQuery(sql.toString()); query.setParameter(1, paramVO.getBusName()); query.setParameter(2, type); if (StringUtils.isNotBlank(paramVO.getProName())) { query.setParameter(3, "%" + paramVO.getProName() + "%"); } return Long.parseLong(query.getSingleResult().toString()); } catch (Exception e) { throw new DaoException( "TzQueryDaoImpl-->getProcurementDisposeTotal()?id????????", e); } } /** * ?id?? * @param paramVO * @param fId * @return * @throws DaoException */ @Override public List<FacilityMaintenanceRecord> getFacilityMaintenanceRecordList(TzQueryRequestParamVO paramVO, Long fId) throws DaoException { try { List<FacilityMaintenanceRecord> list = new ArrayList<FacilityMaintenanceRecord>(); StringBuffer sql = new StringBuffer(); sql.append( "SELECT * FROM facility_maintenance_record WHERE facility_id=?1 ORDER BY maintenance_time desc"); Query query = entityManager.createNativeQuery(sql.toString(), FacilityMaintenanceRecord.class); query.setParameter(1, fId); if (paramVO.getPage() > 0 && paramVO.getPageSize() > 0) { query.setFirstResult((paramVO.getPage() - 1) * paramVO.getPageSize()); query.setMaxResults(paramVO.getPageSize()); } list = query.getResultList(); return list; } catch (Exception e) { throw new DaoException( "TzQueryDaoImpl-->getFacilityMaintenanceRecordList()?id???", e); } } /** * ?id??? * @param paramVO * @param fId * @return * @throws DaoException */ @Override public Long getFacilityMaintenanceRecordTotal(TzQueryRequestParamVO paramVO, Long fId) throws DaoException { try { StringBuffer sql = new StringBuffer(); sql.append("SELECT count(*) FROM facility_maintenance_record WHERE facility_id=?1 "); Query query = entityManager.createNativeQuery(sql.toString()); query.setParameter(1, fId); return Long.parseLong(query.getSingleResult().toString()); } catch (Exception e) { throw new DaoException( "TzQueryDaoImpl-->getFacilityMaintenanceRecordTotal()?id????", e); } } }