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