public List<ProjectTo> findByProdCodes(String prodCodes) {
SimpleJdbcTemplate stj = getSimpleJdbcTemplate();
String sql = "select * from pidb_project";
if (prodCodes != null) {
String spilter = ",";
if (prodCodes != null && prodCodes.indexOf(";") > 0) {
spilter = ";";
}
String subSql = "";
String[] prodCodeList = prodCodes.split(spilter);
for (String prodCode : prodCodeList) {
if (prodCode != null && prodCode.length() > 0) {
subSql += " or pidb_include(PROD_CODE_LIST, ',','" + prodCode + "')=1";
}
}
if (subSql.length() > 0) {
subSql = subSql.substring(3);
sql += " where " + subSql;
}
}
// where pidb_contain(PROD_CODE_LIST, ',', '" + prodCodes + "',
// '"+spilter+"')=1";
logger.debug(sql);
return stj.query(sql, new GenericRowMapper<ProjectTo>(ProjectTo.class), new Object[] {});
}
public List<String> findAllProdCodes() {
SimpleJdbcTemplate sjt = getSimpleJdbcTemplate();
String sql = "select * from PIDB_PROJECT";
List<ProjectTo> projToList =
sjt.query(sql, new GenericRowMapper<ProjectTo>(ProjectTo.class), new Object[] {});
List<String> projCodeList = new ArrayList<String>();
if (projToList != null && projToList.size() > 0) {
for (ProjectTo oneProjTo : projToList) {
String projCodes = oneProjTo.getProdCodeList();
if (projCodes != null && projCodes.length() > 0) {
String[] projCodeArray = projCodes.split(",");
for (String temp : projCodeArray) {
int sign = 0;
for (String projCode : projCodeList) {
if (projCode.equals(temp)) {
sign = 1;
break;
}
} // for projCodeArray
if (sign == 0) {
projCodeList.add(temp);
}
} // for projCodeList
} // if projCodes
} // for projToList
} // if projToList
return projCodeList;
}
public List<CspTo> findByPkgNameList(String pkgName) {
SimpleJdbcTemplate sjt = getSimpleJdbcTemplate();
String sql = "select * from PIDB_CSP where PKG_NAME = ?";
return sjt.query(sql, new GenericRowMapper<CspTo>(CspTo.class), new Object[] {pkgName});
}
public List<CspTo> query(CspQueryTo queryTo) {
SimpleJdbcTemplate sjt = getSimpleJdbcTemplate();
String sql = "select * from PIDB_CSP where 1=1";
String sqlWhere = generateWhereCause(queryTo);
sql = sql + sqlWhere;
sql += " order by PROJ_NAME,PKG_NAME";
logger.debug(sql);
GenericRowMapper<CspTo> rm = new GenericRowMapper<CspTo>(CspTo.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[] {});
}
}
/**
* Method 'findDeviceTypeByDeviceID'
*
* @throws DeviceTypeTreeDaoException
* @return List<DeviceTypeTree>
*/
public List<DeviceTypeTree> findDeviceTypeByDeviceID(long devid)
throws DeviceTypeTreeDaoException {
try {
// return
// jdbcTemplate.query("select c.mrid,a.dtid,a.category,c.mrname,a.name,a.subcategory from
// t_manufacturer_info_init c "
// +
// "left join (select * from t_device_type_init d,t_category_map_init b where d.category=b.id
// ) a on a.mrid=c.mrid "
// +
// " where mrname='"+mrname+"'"
// , this);
return jdbcTemplate.query(
"select distinct mrid,dtid,category,mrname,name,subcategory , model, objectid,devid,'' from ("
+ "select c.mrid,a.dtid,a.category,c.mrname,a.name,a.subcategory , a.model,a.objectid,a.logo,a.description from t_manufacturer_info_init c "
+ "left join (select * from t_device_type_init d,t_category_map_init b where d.category=b.id) a "
+ "on a.mrid=c.mrid "
+ " ) ss"
+ "where devidid=? ",
this,
devid);
} catch (Exception e) {
throw new DeviceTypeTreeDaoException("Query failed", e);
}
}
public List<DeviceTypeTree> findDeviceTypeByWhere(
String mrName, String cateName, String subCategory) throws DeviceTypeTreeDaoException {
try {
// return
// jdbcTemplate.query("select c.mrid,a.dtid,a.category,c.mrname,a.name,a.subcategory from
// t_manufacturer_info_init c "
// +
// "left join (select * from t_device_type_init d,t_category_map_init b where d.category=b.id
// ) a on a.mrid=c.mrid "
// +
// " where mrname='"+mrname+"' and a.name='"+category+"' and a.subcategory='"+subcategory+"'"
// , this);
return jdbcTemplate.query(
"select c.mrid,a.dtid,a.category,c.mrname,a.name,a.subcategory , a.model,a.objectid,a.logo,a.description from t_manufacturer_info_init c "
+ " join (select * from t_device_type_init d,t_category_map_init b where d.category=b.id) a "
+ "on a.mrid=c.mrid "
+ "where mrname=? and a.name=? and a.subCategory=?",
this,
mrName,
cateName,
subCategory);
} catch (Exception e) {
throw new DeviceTypeTreeDaoException("Query failed", e);
}
}
public List<CspTo> findByProjNameAndPkgCode(String projName, String pkgCode) {
SimpleJdbcTemplate sjt = getSimpleJdbcTemplate();
String sql = "select * from PIDB_CSP where PROJ_NAME = ? and PKG_CODE = ?";
return sjt.query(
sql, new GenericRowMapper<CspTo>(CspTo.class), new Object[] {projName, pkgCode});
}
public UserInfo getUser(final long userId) {
final List<UserInfo> users =
jdbcTemplate.query("select * from auth_user where user_id = ?", USER_INFO_MAPPER, userId);
if (users.isEmpty()) {
return null;
}
return users.get(0);
}
/** Returns all rows from the po table that match the criteria 'id = :id'. */
@Transactional
public Po findByPrimaryKey(long id) throws PoDaoException {
try {
List<Po> list =
jdbcTemplate.query("SELECT * FROM " + getTableName() + " WHERE po_code = ?", this, id);
return list.isEmpty() ? null : list.get(0);
} catch (Exception e) {
throw new PoDaoException("Query failed", e);
}
}
@Override
public List<UserInfo> getUsers() {
final List<UserInfo> users =
jdbcTemplate.query(
"select user_id, login, fio, passwd_hash, passwd_add from auth_user", USER_INFO_MAPPER);
if (users.isEmpty()) {
return null;
}
return users;
}
/**
* Method 'execute'
*
* @param gid
* @throws IpAddrRangeCheckParentDaoException
* @return List<IpAddrRangeCheckParent>
*/
public List<IpAddrRangeCheckParent> execute(long gid) throws IpAddrRangeCheckParentDaoException {
try {
return jdbcTemplate.query(
"select IPDECODE_MIN, IPDECODE_MAX from t_list_ip where GID in (select SUPID from T_grp_net where GID=?)",
this,
gid);
} catch (Exception e) {
throw new IpAddrRangeCheckParentDaoException("Query failed", e);
}
}
@Transactional
public List<Po> findWherePoNumberNotInGR() throws PoDaoException {
try {
return jdbcTemplate.query(
"select * from po where ponumber not in (select ponumber from goodreceive) and status='Y' order by deliverydate desc",
this);
} catch (Exception e) {
throw new PoDaoException("Query failed", e);
}
}
public List<ProjectTo> findAllByProjCode(String projCode) {
SimpleJdbcTemplate stj = getSimpleJdbcTemplate();
String sql =
"select p.PROJ_NAME,pc.PROD_CODE PROD_CODE_LIST ,pc.proj_code from PIDB_PROJECT p, PIDB_PROJECT_CODE pc where p.PROJ_NAME=pc.PROJ_NAME ";
if (projCode != null && projCode.length() > 0) {
sql += " and " + super.getSmartSearchQueryString("pc.PROJ_CODE", projCode);
}
logger.debug(sql);
return stj.query(sql, new GenericRowMapper<ProjectTo>(ProjectTo.class), new Object[] {});
}
public UserInfo getUser(final String login) {
final List<UserInfo> users =
jdbcTemplate.query(
"select user_id, login, fio, passwd_hash, passwd_add from auth_user where login = ?",
USER_INFO_MAPPER,
login);
if (users.isEmpty()) {
return null;
}
return users.get(0);
}
@Override
public List<Message> getLatestMessages(int count) {
if (count < 0) {
return Collections.emptyList();
}
if (count > MAX_MESSAGES) {
count = MAX_MESSAGES;
}
return jdbcTemplate.query(SELECT_LATEST_MESSAGES_SQL, messageRowMapper, count);
}
/** Returns all rows from the stock_in table that match the criteria ''. */
@Transactional
public List<StockIn> findAll() throws StockInDaoException {
try {
return jdbcTemplate.query(
"SELECT id, document_no, document_date, product_code, product_name, quantity, is_active, is_delete, created_by, created_date, updated_by, updated_date FROM "
+ getTableName()
+ " ORDER BY id",
this);
} catch (Exception e) {
throw new StockInDaoException("Query failed", e);
}
}
/** Returns all rows from the po table that match the criteria ''. */
@Transactional
public List<Po> findAll() throws PoDaoException {
try {
return jdbcTemplate.query(
"SELECT id, ponumber, podate, prsnumber, prsdate, deliverydate, poreferensi, createdby, corpid, wh_code, department_name, supplier_name, currency, prsremarks, role_code,status, status_date FROM "
+ getTableName()
+ " ORDER BY id",
this);
} catch (Exception e) {
throw new PoDaoException("Query failed", e);
}
}
/** Returns all rows from the SessionSexualRelationshipType table that match the criteria ''. */
@Transactional
public List<SessionSexualRelationshipType> findAll()
throws SessionSexualRelationshipTypeDaoException {
try {
return jdbcTemplate.query(
"SELECT sessionSexualRelationshipTypeId, sessionReference, sexualRelationshipTypeId, dateCreated, createdBy, dateModified, modifiedBy, status FROM "
+ getTableName()
+ " ORDER BY sessionSexualRelationshipTypeId",
this);
} catch (Exception e) {
throw new SessionSexualRelationshipTypeDaoException("Query failed", e);
}
}
/* FYA : 07 January 2014 */
public List<Po> fyaGetByDepartment(String[][] criteria) throws PoDaoException {
try {
Object[] o = new Utility().fyaGenerateSQLCriteria(criteria);
return jdbcTemplate.query(
"SELECT * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY id desc) AS rownum FROM po) AS qPo"
+ o[0],
this,
o[1]);
} catch (DataAccessException e) {
e.printStackTrace();
throw new PoDaoException("Query failed", e);
}
}
public ProjectTo findByProjName(String projName) {
SimpleJdbcTemplate stj = getSimpleJdbcTemplate();
String sql = "select * from pidb_project where PROJ_NAME = ? ";
logger.debug(sql);
List<ProjectTo> result =
stj.query(sql, new GenericRowMapper<ProjectTo>(ProjectTo.class), new Object[] {projName});
if (result != null && result.size() > 0) {
return result.get(0);
} else {
return null;
}
}
public List<DeviceTypeTree> findDeviceTypeByWhereClause(String whereClause)
throws DeviceTypeTreeDaoException {
try {
return jdbcTemplate.query(
"select c.mrid,a.dtid,a.category,c.mrname,a.name,a.subcategory , a.model,a.objectid,a.logo,a.description from t_manufacturer_info_init c "
+ " join (select * from t_device_type_init d,t_category_map_init b where d.category=b.id) a "
+ "on a.mrid=c.mrid "
+ whereClause
+ " order by c.mrname, a.model",
this);
} catch (Exception e) {
throw new DeviceTypeTreeDaoException("Query failed", e);
}
}
/**
* Method 'execute'
*
* @param mrName
* @param cateName
* @param subCategory
* @throws DeviceTypeTreeDaoException
* @return List<DeviceTypeTree>
*/
public List<DeviceTypeTree> execute(
java.lang.String mrName, java.lang.String cateName, java.lang.String subCategory)
throws DeviceTypeTreeDaoException {
try {
return jdbcTemplate.query(
"select c.mrid,a.dtid,a.category,c.mrname,a.name,a.subcategory , a.model,a.objectid,a.logo,a.description from t_manufacturer_info_init c left join (select * from t_device_type_init d,t_category_map_init b where d.category=b.id) a on a.mrid=c.mrid where mrname=? and a.name=? and a.subCategory=?",
this,
mrName,
cateName,
subCategory);
} catch (Exception e) {
throw new DeviceTypeTreeDaoException("Query failed", e);
}
}
/** Returns all rows from the stock_in table that match the criteria 'id = :id'. */
@Transactional
public StockIn findByPrimaryKey(int id) throws StockInDaoException {
try {
List<StockIn> list =
jdbcTemplate.query(
"SELECT id, document_no, document_date, product_code, product_name, quantity, is_active, is_delete, created_by, created_date, updated_by, updated_date FROM "
+ getTableName()
+ " WHERE id = ?",
this,
id);
return list.size() == 0 ? null : list.get(0);
} catch (Exception e) {
throw new StockInDaoException("Query failed", e);
}
}
public DeviceTypeTree findByDtid(long dtid) throws DeviceTypeTreeDaoException {
List<DeviceTypeTree> list = null;
try {
list =
jdbcTemplate.query(
"select c.mrid,a.dtid,a.category,c.mrname,a.name,a.subcategory , a.model,a.objectid,a.logo,a.description from t_manufacturer_info_init c "
+ " join (select * from t_device_type_init d,t_category_map_init b where d.category=b.id) a "
+ "on a.mrid=c.mrid and dtid=?",
this,
dtid);
return list.size() == 0 ? null : list.get(0);
} catch (Exception e) {
throw new DeviceTypeTreeDaoException("Query failed", e);
}
}
public static List<Mail> getEmails() {
List<Mail> mailList = null;
try {
mailList = db.query("SELECT * FROM emails", rowMapper);
db.update("DELETE FROM emails");
if (mailList == null) mailList = new ArrayList<Mail>();
} catch (EmptyResultDataAccessException ex) {
} catch (Exception ex) {
System.out.println("Bug in get emails :(");
ex.printStackTrace();
}
return mailList;
}
/**
* Returns all rows from the SessionSexualRelationshipType table that match the criteria
* 'sessionSexualRelationshipTypeId = :sessionSexualRelationshipTypeId'.
*/
@Transactional
public SessionSexualRelationshipType findByPrimaryKey(long sessionSexualRelationshipTypeId)
throws SessionSexualRelationshipTypeDaoException {
try {
List<SessionSexualRelationshipType> list =
jdbcTemplate.query(
"SELECT sessionSexualRelationshipTypeId, sessionReference, sexualRelationshipTypeId, dateCreated, createdBy, dateModified, modifiedBy, status FROM "
+ getTableName()
+ " WHERE sessionSexualRelationshipTypeId = ?",
this,
sessionSexualRelationshipTypeId);
return list.size() == 0 ? null : list.get(0);
} catch (Exception e) {
throw new SessionSexualRelationshipTypeDaoException("Query failed", e);
}
}
/**
* Method 'listDeviceTypeTree';mrid,category,mrname,name,subcategory , model, objectid
*
* @throws DeviceTypeTreeDaoException
* @return List<DeviceTypeTree>
*/
public List<DeviceTypeTree> listDeviceTypeTreeWithModelOid() throws DeviceTypeTreeDaoException {
try {
return jdbcTemplate.query(
"select distinct mrid,dtid,category,mrname,name,subcategory , model, objectid,'','' from("
+
// "select c.mrid,a.dtid,a.category,c.mrname,a.name,a.subcategory from
// t_manufacturer_info_init c left join (select * from t_device_type_init
// d,t_category_map_init b where d.category=b.id) a on a.mrid=c.mrid order by
// c.mrName)",
// this);
"select c.mrid,a.dtid,a.category,c.mrname,a.name,a.subcategory , a.model,a.objectid,a.logo,a.description from t_manufacturer_info_init c "
+ "left join (select * from t_device_type_init d,t_category_map_init b where d.category=b.id) a on a.mrid=c.mrid )",
this);
} catch (Exception e) {
throw new DeviceTypeTreeDaoException("Query failed", e);
}
}
/**
* Retrieves all ingredients.
*
* @return a list of ingredients
*/
public List<Ingredient> getAll() {
logger.debug("Retrieving all ingredients");
// Prepare our SQL statement
String sql = "select id, name, description, flavour from ingredients";
// Maps a SQL result to a Java object
RowMapper<Ingredient> mapper =
new RowMapper<Ingredient>() {
public Ingredient mapRow(final ResultSet rs, final int rowNum) throws SQLException {
final Ingredient ingredient = new Ingredient();
ingredient.setId(rs.getInt("id"));
ingredient.setName(rs.getString("name"));
ingredient.setDescription(rs.getString("description"));
String flavour = (rs.getString("flavour"));
if (flavour != null) {
if (flavour.equals(Ingredient.Flavour.SALTY.name())) {
ingredient.setFlavour(Ingredient.Flavour.SALTY);
} else if (flavour.equals(Ingredient.Flavour.SWEET.name())) {
ingredient.setFlavour(Ingredient.Flavour.SWEET);
} else if (flavour.equals(Ingredient.Flavour.SOUR.name())) {
ingredient.setFlavour(Ingredient.Flavour.SOUR);
} else if (flavour.equals(Ingredient.Flavour.BITTER.name())) {
ingredient.setFlavour(Ingredient.Flavour.BITTER);
} else if (flavour.equals(Ingredient.Flavour.UMAMI.name())) {
ingredient.setFlavour(Ingredient.Flavour.UMAMI);
}
}
return ingredient;
}
};
// Retrieve all
return jdbcTemplate.query(sql, mapper);
}
public List<DeviceTypeTree> searchByMulti(
String mrName, String cateName, String subcate, String model, String objectid, String descr)
throws DeviceTypeTreeDaoException {
StringBuffer sbbase =
new StringBuffer(
"select c.mrid,a.dtid,a.category,c.mrname,a.name,a.subcategory , a.model,a.objectid,a.logo,a.description from t_manufacturer_info_init c "
+ " join (select * from t_device_type_init d,t_category_map_init b where d.category=b.id) a "
+ "on a.mrid=c.mrid ");
StringBuffer sb = addCondition(sbbase, "c.mrname", mrName);
sb = addCondition(sb, "a.name", cateName);
sb = addCondition(sb, "a.subCategory", subcate);
sb = addCondition(sb, "a.model", model);
sb = addCondition(sb, "a.objectid", objectid);
sb = addCondition(sb, "a.description", descr);
System.out.println("sb is " + sb.toString());
try {
return jdbcTemplate.query(sb.toString(), this);
} catch (Exception e) {
throw new DeviceTypeTreeDaoException("Query failed", e);
}
}
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[] {});
}
}
