public static Team getTeam(Connection con, String team_id) throws java.sql.SQLException {
Team teamObj = null;
PreparedStatement pstmt = null;
java.sql.ResultSet rs = null;
String sql = null;
sql =
" select team_id, team_code, type_code, team_name, team_desc, team_manager_id, team_manager_name, creater, created "
+ " from t_team "
+ " where team_id = ?";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, team_id);
rs = pstmt.executeQuery();
if (rs.next()) {
teamObj = new Team();
teamObj.team_id = rs.getString("team_id");
teamObj.team_code = rs.getString("team_code");
teamObj.type_code = rs.getString("type_code");
teamObj.team_name = rs.getString("team_name");
teamObj.team_desc = rs.getString("team_desc");
teamObj.team_manager_id = rs.getString("team_manager_id");
teamObj.team_manager_name = rs.getString("team_manager_name");
teamObj.creater = rs.getString("creater");
teamObj.created = rs.getTimestamp("created");
}
rs.close();
pstmt.close();
return teamObj;
}
/**
* 根据员工ID得到他的国家（根据群组）
*
* @param con
* @param emp_id
* @return
* @throws Exception
*/
public static List<Country> getCountiesByEmpId(Connection con, String emp_id) throws Exception {
List<Country> lsCountry = new ArrayList<Country>();
List<Country> lsCountryTmp = new ArrayList<Country>();
List<Team> lsTeam = getTeamByEmpId(con, emp_id);
for (Team team : lsTeam) {
lsCountryTmp.addAll(Country.getCountryByTeamCode(team.getTeam_code()));
}
for (Country country : lsCountryTmp) {
if (!lsCountry.contains(country)) lsCountry.add(country);
}
return lsCountry;
}
/**
* 根据员工ID，得到员工所有的群组
*
* @param con
* @param emp_id
* @return
* @throws Exception
*/
public static List<Team> getTeamByEmpId(Connection con, String emp_id) throws Exception {
List<Team> lsTeam = new ArrayList<Team>();
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql =
"select team_id, team_code, type_code, team_name, team_desc, "
+ " team_manager_id, team_manager_name "
+ " from t_teammember a, t_team b "
+ " where a.tm_team_id = b.team_id and tm_user_id = ? ";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, emp_id);
rs = pstmt.executeQuery();
while (rs.next()) {
Team team = new Team();
team.setTeam_id(rs.getString("team_id"));
team.setTeam_code(rs.getString("team_code"));
team.setType_code(rs.getString("type_code"));
team.setTeam_name(rs.getString("team_name"));
team.setTeam_desc(rs.getString("team_desc"));
team.setTeam_manager_id(rs.getString("team_manager_id"));
team.setTeam_manager_name(rs.getString("team_manager_name"));
lsTeam.add(team);
}
rs.close();
pstmt.close();
return lsTeam;
}
public static int delTeam(Connection con, String team_code) throws java.sql.SQLException {
PreparedStatement pstmt = null;
java.sql.ResultSet rs = null;
String sql = null;
int delCounts = 0;
int teamMember = 0;
if (team_code == null) return delCounts;
sql = " select count(*) as teamMembers from t_teammember " + " where tm_team_id = ?";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, Team.getTeamIdbyCode(con, team_code));
rs = pstmt.executeQuery();
if (rs.next()) {
teamMember = rs.getInt("teamMembers");
}
rs.close();
if (teamMember == 0) {
sql = " delete from t_team " + " where team_code = ?";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, team_code);
pstmt.executeUpdate();
delCounts++;
}
pstmt.close();
return delCounts;
}