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