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[] {});
}
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();
}
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[] {});
}
}
