Beispiel #1
0
 public int countResult(ProjectQueryTo queryTo) {
   SimpleJdbcTemplate sjt = getSimpleJdbcTemplate();
   String sql =
       "select count(*) from pidb_project p left join pidb_project_code pc on p.proj_name=pc.proj_name where 1 = 1 ";
   if (queryTo.getReleaseTo() != null && !queryTo.getReleaseTo().equals("")) {
     if (queryTo.getReleaseTo().equals("HX") || queryTo.getReleaseTo().equals("WP")) {
       sql +=
           " AND (pc.RELEASE_TO ='" + queryTo.getReleaseTo() + "'" + " or pc.RELEASE_TO ='ALL')";
     }
   }
   String whereCause = generateWhereCause(queryTo);
   sql += whereCause;
   return sjt.queryForInt(sql, new Object[] {});
 }
Beispiel #2
0
  private String generateWhereCause(final ProjectQueryTo queryTo) {
    StringBuilder sb = new StringBuilder();
    if (queryTo.getProjCode() != null && !queryTo.getProjCode().equals("")) {
      String projCodeQueryString =
          getSmartSearchQueryAllLike("pc.PROJ_CODE", queryTo.getProjCode());
      if (projCodeQueryString != null) {
        sb.append(" and (" + projCodeQueryString + " )");
      }
    }

    if (queryTo.getProjName() != null && !queryTo.getProjName().equals("")) {
      String projNameQueryString = getSmartSearchQueryAllLike("p.PROJ_NAME", queryTo.getProjName());
      if (projNameQueryString != null) {
        sb.append(" and (" + projNameQueryString + " )");
      }
    }

    /*
     * if (queryTo.getReleaseTo() != null &&
     * !queryTo.getReleaseTo().equals("")) {
     *
     * String releaseTo = getSmartSearchQueryString("pc.RELEASE_TO",
     * queryTo.getReleaseTo()); if (releaseTo != null) { sb.append(" and (" +
     * releaseTo + ")"); } }
     */

    if (queryTo.getProdCode() != null && !queryTo.getProdCode().equals("")) {
      String prodCode = getSmartSearchQueryAllLike("pc.PROD_CODE", queryTo.getProdCode());
      if (prodCode != null) {
        sb.append(" and (" + prodCode + " )");
      }
    }

    if (queryTo.getFab() != null && !queryTo.getFab().equals("")) {
      sb.append(" and p.FAB = " + getSQLString(queryTo.getFab()) + " ");
    }

    if (queryTo.getProjOption() != null && !queryTo.getProjOption().equals("")) {
      sb.append(" and pc.PROJ_OPTION = " + getSQLString(queryTo.getProjOption()) + " ");
    }

    if (queryTo.getPanelType() != null && !queryTo.getPanelType().equals("")) {
      sb.append(" and p.PANEL_TYPE = " + getSQLString(queryTo.getPanelType()) + " ");
    }

    if (queryTo.getProdFamily() != null && !queryTo.getProdFamily().equals("")) {
      sb.append(" and p.PROD_FAMILY = " + getSQLString(queryTo.getProdFamily()) + " ");
    }

    if (queryTo.getProdLine() != null && !queryTo.getProdLine().equals("")) {
      sb.append(" and p.PROD_LINE = " + getSQLString(queryTo.getProdLine()) + " ");
    }

    if (queryTo.getProcTech() != null && !queryTo.getProcTech().equals("")) {
      sb.append(" and p.PROC_TECH = " + getSQLString(queryTo.getProcTech()) + " ");
    }

    if (queryTo.getCust() != null && !queryTo.getCust().equals("")) {
      String custQueryString = getSmartSearchQueryString("pc.CUST", queryTo.getCust());
      if (custQueryString != null) {
        sb.append(" and (" + custQueryString + " )");
      }
    }

    if (queryTo.getTeamMember() != null && !queryTo.getTeamMember().equals("")) {
      // String teamMember =
      // super.getLikeSQLString(queryTo.getTeamMember());
      String teamMember = queryTo.getTeamMember();
      if (teamMember.startsWith("*")) {
        teamMember = "%" + teamMember.substring(1);
      }

      if (teamMember.endsWith("*")) {
        teamMember = teamMember.substring(0, teamMember.length() - 1) + "%";
      }
      teamMember = getSQLString(teamMember);

      sb.append(" and (");
      sb.append(" p.PROJ_LEADER like " + teamMember + " ");
      sb.append(" or p.DESIGN_ENGR like " + teamMember + " ");
      sb.append(" or p.PROD_ENGR like " + teamMember + " ");
      sb.append(" or p.ESD_ENGR like " + teamMember + " ");
      sb.append(" or p.APR_ENGR like " + teamMember + " ");
      sb.append(" or p.LAYOUT_ENGR like " + teamMember + " ");
      sb.append(" or p.TEST_ENGR like " + teamMember + " ");
      sb.append(" or p.ASSY_ENGR like " + teamMember + " ");
      sb.append(" or p.APP_ENGR like " + teamMember + " ");
      sb.append(" or p.PM like " + teamMember + " ");
      sb.append(" or p.QA_ENGR like " + teamMember + " ");
      sb.append(" or p.SALES_ENGR like " + teamMember + " ");
      sb.append(")");
    }

    if (queryTo.getKickOffDateFrom() != null
        && !queryTo.getKickOffDateFrom().equals("")
        && queryTo.getKickOffDateTo() != null
        && !queryTo.getKickOffDateTo().equals("")) {
      // SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
      String dateFrom =
          getSQLDateString(queryTo.getKickOffDateFrom() + " 00:00:00", "yyyy/MM/dd HH:mm:ss");
      String dateTo =
          getSQLDateString(queryTo.getKickOffDateTo() + " 23:59:59", "yyyy/MM/dd HH:mm:ss");
      sb.append(" and pc.KICK_OFF_DATE between " + dateFrom + " and " + dateTo + " ");
    }

    if (queryTo.getStatus() != null && !queryTo.getStatus().equals("")) {
      sb.append(" and p.STATUS = " + getSQLString(queryTo.getStatus()) + " ");
    }

    if (queryTo.getEstimated() != null && !queryTo.getEstimated().equals("")) {
      sb.append(" and p.ESTIMATED = " + getSQLString(queryTo.getEstimated()) + " ");
    }

    if (queryTo.getNickName() != null && !queryTo.getNickName().equals("")) {
      String nickName = queryTo.getNickName();
      if (nickName.startsWith("*")) {
        nickName = "%" + nickName.substring(1);
      }

      if (nickName.endsWith("*")) {
        nickName = nickName.substring(0, nickName.length() - 1) + "%";
      }
      nickName = getSQLString(nickName);

      sb.append(" and p.NICK_NAME like " + nickName + " ");
    }
    return sb.toString();
  }
Beispiel #3
0
 public List<ProjectTo> queryForDomain(ProjectQueryTo queryTo) {
   SimpleJdbcTemplate sjt = getSimpleJdbcTemplate();
   String sql =
       "select "
           + "p.PROJ_NAME,"
           + "(select FAB_DESCR from T_FAB_CODE tfc where tfc.FAB=p.FAB) FAB,"
           + "p.PITCH,"
           + "p.PAD_TYPE,"
           + "to_date(substr(to_char(p.FS_DATE,'yyyy/mm/dd'),1,10),'yyyy/mm/dd') AS FS_DATE,"
           + "p.PROC_TECH,"
           + "p.POLY_METAL_LAYERS,"
           + "p.VOLTAGE,"
           + "p.MASK_LAYERS_NO,"
           + "p.PROC_LAYER_NO,"
           + "p.WAFER_TYPE,"
           + "p.WAFER_INCH,"
           + "p.X_SIZE,"
           + "p.Y_SIZE,"
           + "p.GROSS_DIE,"
           + "p.FCST_CP_YIELD,"
           + "p.TO_INCLUDE_SEALRING,"
           + "p.SEALRING,"
           + "p.SCRIBE_LINE,"
           + "p.TEST_LINE,"
           + "p.WAFER_THICKNESS,"
           + "p.PROC_NAME,"
           + "p.ANY_IP_USAGE,"
           + "p.EMBEDDED_OTP,"
           + "p.OTP_SIZE,"
           + "p.PROJ_LEADER,"
           + "p.DESIGN_ENGR,"
           + "p.PROD_ENGR,"
           + "p.ESD_ENGR,"
           + "p.APR_ENGR,"
           + "p.LAYOUT_ENGR,"
           + "p.TEST_ENGR,"
           + "p.ASSY_ENGR,"
           + "p.APP_ENGR,"
           + "p.PM,"
           + "p.QA_ENGR,"
           + "p.SALES_ENGR,"
           + "p.ASSIGN_TO,"
           + "p.ASSIGN_EMAIL,"
           + "p.STATUS,"
           + "(select DESCRIPTION from WM_SAP_MASTER_PRODUCT_FAMILY wsmpf where wsmpf.PRODUCT_FAMILY=p.PROD_FAMILY) PROD_FAMILY,"
           + "p.PROD_LINE,"
           + "p.PANEL_TYPE,"
           + "(select DESCRIPTION from WM_SAP_MASTER_IC_TYPE wsmit where wsmit.IC_TYPE=p.IC_TYPE) IC_TYPE,"
           + "pc.PROD_CODE PROD_CODE_LIST,"
           + "p.CREATED_BY,"
           + "p.MODIFIED_BY,"
           + "p.ESTIMATED,"
           + "p.NICK_NAME,"
           + "p.SECOND_FOUNDRY_PROJECT,"
           + "p.SUB_FAB,"
           + "pc.PROJECT_TYPE,"
           + "pc.PROJ_CODE,"
           + "pc.PROJ_OPTION,"
           + "pc.FUNC_REMARK,"
           + "pc.CUST,"
           + "pc.REMARK,"
           + "pc.RELEASE_TO,"
           + "to_date(substr(to_char(pc.KICK_OFF_DATE,'yyyy/mm/dd'),1,10),'yyyy/mm/dd') AS KICK_OFF_DATE"
           + " from pidb_project p left join pidb_project_code pc on p.proj_name=pc.proj_name where 1 = 1 ";
   if (queryTo.getReleaseTo() != null && !queryTo.getReleaseTo().equals("")) {
     if (queryTo.getReleaseTo().equals("HX") || queryTo.getReleaseTo().equals("WP")) {
       sql +=
           " AND (pc.RELEASE_TO ='" + queryTo.getReleaseTo() + "'" + " or pc.RELEASE_TO ='ALL')";
     }
   }
   String whereCause = generateWhereCause(queryTo);
   sql += whereCause;
   sql += " order by pc.PROJ_CODE";
   GenericRowMapper<ProjectTo> rm = new GenericRowMapper<ProjectTo>(ProjectTo.class);
   if (queryTo.getPageNo() > 0) {
     int cursorFrom = (queryTo.getPageNo() - 1) * queryTo.getPageSize() + 1;
     int cursorTo = (queryTo.getPageNo()) * queryTo.getPageSize();
     return sjt.query(getPagingSql(sql, cursorFrom, cursorTo), rm, new Object[] {});
   } else {
     return sjt.query(sql, rm, new Object[] {});
   }
 }