private ArrayList<QueryParameter> queryCondition(ActionForm aform) { NpcStockInboundForm form = (NpcStockInboundForm) aform; where = " where 1=1 and nf.organizationCode=" + form.getOrgCode() + " and substr(nf.stockStatus,0,1)='" + STOCK_TRANSFER_TMP_STATUS + "'"; ArrayList<QueryParameter> paramList = new ArrayList<QueryParameter>(); if (StringUtils.isNotBlank(form.getQueryPartCode())) { QueryParameter param = new QueryParameter(); where = where + " and nf.skuCode like :partCode"; param.setName("partCode"); param.setValue("%" + form.getQueryPartCode() + "%"); param.setHbType(Hibernate.STRING); paramList.add(param); } return paramList; }
/** * Inquire running statistics of some background report in one ASC * * @param reportCode * @param orgCode * @return * @throws Exception */ public ArrayList<String[]> bgReportQuery(Long reportCode, Long orgCode) throws Exception { ArrayList<String[]> bgList = new ArrayList<String[]>(); String strHql = "from BackgrounderReportForm as bgr where bgr.createDate>=sysdate-7 " + "and bgr.reportCode= :reportCode and bgr.organizationCode= :organizationCode order by bgr.bgId desc"; ArrayList<QueryParameter> paramList = new ArrayList<QueryParameter>(); QueryParameter param = new QueryParameter(); param.setName("reportCode"); param.setValue(reportCode); param.setHbType(Hibernate.LONG); paramList.add(param); param = new QueryParameter(); param.setName("organizationCode"); param.setValue(orgCode); param.setHbType(Hibernate.LONG); paramList.add(param); ArrayList<BackgrounderReportForm> al = (ArrayList<BackgrounderReportForm>) this.getDao().parameterQuery(strHql, paramList); String[] path = Operate.getReportPath(); String strWeek = Operate.getWeek2(); for (int i = 0; i < al.size(); i++) { BackgrounderReportForm bgrf = al.get(i); String[] temp = new String[7]; temp[0] = bgrf.getBgId().toString(); temp[1] = bgrf.getReportUserName(); if (bgrf.getReportFile() != null) { // temp[2]=bgrf.getReportFile().substring(bgrf.getReportFile().lastIndexOf("/")+1); temp[2] = bgrf.getReportFile(); } temp[3] = bgrf.getReportStatus(); temp[4] = bgrf.getBeginTime().toString(); temp[5] = bgrf.getExceptionMessage(); if (temp[2] != null) { temp[6] = path[1] + temp[2]; } bgList.add(temp); } return bgList; }
private ArrayList queryCondition(StockInfoForm form) { ArrayList paramList = new ArrayList(); where = " where 1=1 "; try { if (form != null) { if (form.getOrganizationCode() != null && form.getOrganizationCode().longValue() != 0) { where = where + " and pa.organizationCode = :organizationCode"; QueryParameter param = new QueryParameter(); param.setName("organizationCode"); param.setValue(form.getOrganizationCode()); param.setHbType(Hibernate.LONG); paramList.add(param); } if (form.getStockCode() != null && !form.getStockCode().equals("")) { where = where + " and pa.stockCode = :stockCode"; QueryParameter param = new QueryParameter(); param.setName("stockCode"); param.setValue(form.getStockCode()); param.setHbType(Hibernate.STRING); paramList.add(param); } if (form.getBinCode() != null && !form.getBinCode().equals("")) { where = where + " and pa.binCode = :binCode"; QueryParameter param = new QueryParameter(); param.setName("binCode"); param.setValue(form.getBinCode()); param.setHbType(Hibernate.STRING); paramList.add(param); } if (form.getStockType() != null && !form.getStockType().equals("")) { where = where + " and pa.stockType = :stockType"; QueryParameter param = new QueryParameter(); param.setName("stockType"); param.setValue(form.getStockType()); param.setHbType(Hibernate.STRING); paramList.add(param); } if (form.getSkuType() != null && !form.getSkuType().equals("")) { where = where + " and pa.skuType = :skuType"; QueryParameter param = new QueryParameter(); param.setName("skuType"); param.setValue(form.getSkuType()); param.setHbType(Hibernate.STRING); paramList.add(param); } if (form.getSkuCode() != null && !form.getSkuCode().equals("")) { where = where + " and pa.skuCode = :skuCode"; QueryParameter param = new QueryParameter(); param.setName("skuCode"); param.setValue(form.getSkuCode()); param.setHbType(Hibernate.STRING); paramList.add(param); } if (form.getSkuNum() != null && form.getSkuNum().longValue() != 0) { where = where + " and pa.skuNum = :skuNum"; QueryParameter param = new QueryParameter(); param.setName("skuNum"); param.setValue(form.getSkuNum()); param.setHbType(Hibernate.INTEGER); paramList.add(param); } if (form.getSnNo() != null && !form.getSnNo().equals("")) { where = where + " and pa.snNo = :snNo"; QueryParameter param = new QueryParameter(); param.setName("snNo"); param.setValue(form.getSnNo()); param.setHbType(Hibernate.STRING); paramList.add(param); } if (form.getLotNo() != null && !form.getLotNo().equals("")) { where = where + " and pa.lotNo = :lotNo"; QueryParameter param = new QueryParameter(); param.setName("lotNo"); param.setValue(form.getLotNo()); param.setHbType(Hibernate.STRING); paramList.add(param); } if (form.getFlowNo() != null && !form.getFlowNo().toString().equals("0")) { // System.out.println("getFlowNo------------"+form.getFlowNo()); where = where + " and pa.flowNo = :flowNo"; QueryParameter param = new QueryParameter(); param.setName("flowNo"); param.setValue(form.getFlowNo()); param.setHbType(Hibernate.LONG); paramList.add(param); } /* if (form.getRealCost() != null && form.getRealCost().doubleValue()!=-1) { where = where + " and pa.realCost = :realCost"; QueryParameter param = new QueryParameter(); param.setName("realCost"); param.setValue(form.getRealCost()); param.setHbType(Hibernate.DOUBLE); paramList.add(param); } */ if (form.getRealCost3() != null && !form.getRealCost3().equals("")) { where = where + " and pa.realCost = :realCost"; QueryParameter param = new QueryParameter(); param.setName("realCost"); param.setValue(new Double(form.getRealCost3())); param.setHbType(Hibernate.DOUBLE); paramList.add(param); } if (form.getOwner() != null && !form.getOwner().equals("")) { where = where + " and pa.owner = :owner"; QueryParameter param = new QueryParameter(); param.setName("owner"); param.setValue(form.getOwner()); param.setHbType(Hibernate.STRING); paramList.add(param); } if (form.getStockStatus() != null && !form.getStockStatus().equals("")) { if ("adjust".equals(form.getStockStatus())) { // 可用库存和差错处理 where = where + " and pa.stockStatus in ('A','B')"; } else { where = where + " and pa.stockStatus = :stockStatus"; QueryParameter param = new QueryParameter(); param.setName("stockStatus"); param.setValue(form.getStockStatus()); param.setHbType(Hibernate.STRING); paramList.add(param); } } if (form.getBinType() != null && !form.getBinType().equals("")) { where = where + " and pa.binType = (select distinct(sb.binType) from TsStationBinForm as sb,TsStoreInfoForm as si " + "where si.storeId=sb.storeId and si.storeCode=pa.stockCode and sb.binCode=pa.binCode " + "and sb.organizationCode=pa.organizationCode and sb.binType='" + form.getBinType() + "'))"; } if (form.getRequestId() != null && !form.getRequestId().toString().equals("0")) { where = where + " and pa.requestId = :requestId"; QueryParameter param = new QueryParameter(); param.setName("requestId"); param.setValue(form.getRequestId()); param.setHbType(Hibernate.LONG); paramList.add(param); } } } catch (Exception e) { e.printStackTrace(); } return paramList; }
/** * Verify whether the background report is running or in line exceed. * * @param reportId * @param orgCode * @param userId * @return reportFull;orgFull;userFull;ok */ public String checkBgStatus(Long reportCode, Long orgCode, Long userId) throws Exception { String status = null; String strHql = "select count(*) from BackgrounderReportForm as bgr where bgr.reportStatus='running'"; Integer count = (Integer) this.getDao().uniqueResult(strHql); // 报表总运行条数超过20,退出 if (count >= 20) { status = "reportFull"; } else { String strHql2 = "select count(*) from BackgrounderReportForm as bgr where bgr.reportStatus='running' " + "and bgr.reportCode= :reportCode and bgr.organizationCode= :organizationCode "; ArrayList<QueryParameter> paramList = new ArrayList<QueryParameter>(); QueryParameter param = new QueryParameter(); param.setName("reportCode"); param.setValue(reportCode); param.setHbType(Hibernate.LONG); paramList.add(param); param = new QueryParameter(); param.setName("organizationCode"); param.setValue(orgCode); param.setHbType(Hibernate.LONG); paramList.add(param); Integer orgCount = (Integer) this.getDao().uniqueResult(strHql2, paramList); // 该站运行条数超过5,退出 if (orgCount != null && orgCount >= 5) { status = "orgFull"; } else { String strHql3 = "select count(*) from BackgrounderReportForm as bgr where bgr.reportStatus='running' " + "and bgr.reportCode= :reportCode and bgr.createBy= :createBy "; ArrayList<QueryParameter> paramList3 = new ArrayList<QueryParameter>(); QueryParameter param3 = new QueryParameter(); param3.setName("reportCode"); param3.setValue(reportCode); param3.setHbType(Hibernate.LONG); paramList3.add(param3); param3 = new QueryParameter(); param3.setName("createBy"); param3.setValue(userId); param3.setHbType(Hibernate.LONG); paramList3.add(param3); Integer userCount = (Integer) this.getDao().uniqueResult(strHql3, paramList3); // 该用户启动条数超过2,退出 if (userCount != null && userCount >= 2) { status = "userFull"; } else { status = "ok"; } } } return status; }
/** * Generate HQL sentence for querying results quantity * * @param aform ActionForm * @return AdvQueryString */ protected AdvQueryString generateCountQuery(ActionForm aform) { TsRepairLevelForm form = (TsRepairLevelForm) aform; AdvQueryString countQuery = new AdvQueryString(); ArrayList paramList = new ArrayList(); String queryString = "select count(pa) from TsRepairLevelForm as pa "; String where = " where 1=1 "; if (form != null) { if (form.getMethodCode() != null && !form.getMethodCode().equals("")) { where = where + " and (pa.methodCode like :methodCode "; QueryParameter param = new QueryParameter(); param.setName("methodCode"); param.setValue(form.getMethodCode()); param.setHbType(Hibernate.STRING); paramList.add(param); where = where + " or pa.methodCode like :methodCodeUPCASE"; param = new QueryParameter(); param.setName("methodCodeUPCASE"); param.setValue("%" + form.getMethodCode().toUpperCase() + "%"); param.setHbType(Hibernate.STRING); paramList.add(param); where = where + " or pa.methodCode like :methodCodeLOWERCASE"; param = new QueryParameter(); param.setName("methodCodeLOWERCASE"); param.setValue("%" + form.getMethodCode().toLowerCase() + "%"); param.setHbType(Hibernate.STRING); paramList.add(param); where += ")"; } if (form.getPartCode() != null && !form.getPartCode().equals("")) { where = where + " and (pa.partCode like :partCode "; QueryParameter param = new QueryParameter(); param.setName("partCode"); param.setValue(form.getPartCode()); param.setHbType(Hibernate.STRING); paramList.add(param); where = where + " or pa.partCode like :partCodeUPCASE"; param = new QueryParameter(); param.setName("partCodeUPCASE"); param.setValue("%" + form.getPartCode().toUpperCase() + "%"); param.setHbType(Hibernate.STRING); paramList.add(param); where = where + " or pa.partCode like :partCodeLOWERCASE"; param = new QueryParameter(); param.setName("partCodeLOWERCASE"); param.setValue("%" + form.getPartCode().toLowerCase() + "%"); param.setHbType(Hibernate.STRING); paramList.add(param); where += ")"; } if (form.getBizGroupName() != null && !form.getBizGroupName().equals("")) { where = where + " and (pa.bizGroupName like :bizGroupName"; QueryParameter param = new QueryParameter(); param.setName("bizGroupName"); param.setValue(form.getBizGroupName()); param.setHbType(Hibernate.STRING); paramList.add(param); where = where + " or pa.bizGroupName like :bizGroupNameUPCASE"; param = new QueryParameter(); param.setName("bizGroupNameUPCASE"); param.setValue("%" + form.getBizGroupName().toUpperCase() + "%"); param.setHbType(Hibernate.STRING); paramList.add(param); where = where + " or pa.bizGroupName like :bizGroupNameLOWERCASE"; param = new QueryParameter(); param.setName("bizGroupNameLOWERCASE"); param.setValue("%" + form.getBizGroupName().toLowerCase() + "%"); param.setHbType(Hibernate.STRING); paramList.add(param); where += ")"; } if (form.getRepairType() != null && !form.getRepairType().equals("N") && !form.getRepairType().equals("")) { where = where + " and (pa.repairType = :repairType)"; QueryParameter param = new QueryParameter(); param.setName("repairType"); param.setValue(form.getRepairType()); param.setHbType(Hibernate.STRING); paramList.add(param); } if (form.getRepairProperites() != null && !form.getRepairProperites().equals("N") && !form.getRepairProperites().equals("")) { where = where + " and (pa.repairProperites = :repairProperites)"; QueryParameter param = new QueryParameter(); param.setName("repairProperites"); param.setValue(form.getRepairProperites()); param.setHbType(Hibernate.STRING); paramList.add(param); } if (form.getRepairLevel() != null && form.getRepairLevel().longValue() != 0 && !form.getRepairLevel().equals("")) { where = where + " and (pa.repairLevel = :repairLevel) "; QueryParameter param = new QueryParameter(); param.setName("repairLevel"); param.setValue(form.getRepairLevel()); param.setHbType(Hibernate.LONG); paramList.add(param); } if (form.getModelCode() != null && !form.getModelCode().equals("")) { where = where + " and (pa.modelCode like :modelCode "; QueryParameter param = new QueryParameter(); param.setName("modelCode"); param.setValue(form.getModelCode()); param.setHbType(Hibernate.STRING); paramList.add(param); where = where + " or pa.modelCode like :modelCodeUPCASE"; param = new QueryParameter(); param.setName("modelCodeUPCASE"); param.setValue("%" + form.getModelCode().toUpperCase() + "%"); param.setHbType(Hibernate.STRING); paramList.add(param); where = where + " or pa.modelCode like :modelCodeLOWERCASE"; param = new QueryParameter(); param.setName("modelCodeLOWERCASE"); param.setValue("%" + form.getModelCode().toLowerCase() + "%"); param.setHbType(Hibernate.STRING); paramList.add(param); where += ")"; } if (form.getMajorCat() != null && !"".equals(form.getMajorCat())) { where = where + " and (pa.majorCat like :majorCat) "; QueryParameter param = new QueryParameter(); param.setName("majorCat"); param.setValue(form.getMajorCat()); param.setHbType(Hibernate.STRING); paramList.add(param); } if (form.getMinorCat() != null && !"".equals(form.getMinorCat())) { where = where + " and (pa.minorCat like :minorCat) "; QueryParameter param = new QueryParameter(); param.setName("minorCat"); param.setValue(form.getMinorCat()); param.setHbType(Hibernate.STRING); paramList.add(param); } } queryString = queryString + where; countQuery.setQueryString(queryString); countQuery.setParameters(paramList); return countQuery; }