チーム打撃成績の表示(一部要改修)
| @@ -0,0 +1,31 @@ | ||
| 1 | +SELECT | |
| 2 | + name, | |
| 3 | + p.team_id as team_id, | |
| 4 | + player_id, | |
| 5 | + t.short_name as opponent_name, | |
| 6 | + sum(tpa) as tpa, | |
| 7 | + sum(at_bats)as at_bats, | |
| 8 | + sum(hit) as hit, | |
| 9 | + (sum(hit)/sum(at_bats)) as average, | |
| 10 | + sum(rbi) as rbi, | |
| 11 | + sum(four_ball) as four_ball, | |
| 12 | + sum(strike_out) as strike_out, | |
| 13 | + sum(twobase) as twobase, | |
| 14 | + sum(homerun) as homerun, | |
| 15 | + g.game_date as game_date, | |
| 16 | + g.game_id as game_id, | |
| 17 | + g.game_number as game_number, | |
| 18 | + myteam_id, | |
| 19 | + g.league_id as league_id | |
| 20 | + FROM jcbl.batting_sum b | |
| 21 | +inner join GAME g | |
| 22 | +on b.game_id=g.game_id | |
| 23 | +inner join RESULT r | |
| 24 | +on b.myteam_id=r.team_id and g.game_id=r.game_id | |
| 25 | +inner join player p | |
| 26 | +on p.id=b.player_id | |
| 27 | +inner join team t | |
| 28 | +on r.opponent=t.team_id | |
| 29 | +where p.team_id=/*teamId*/ and game_date>=/*beginDate*/ and game_date<=/*endDate*/ | |
| 30 | +group by player_id,league_id,game_date,game_number with rollup | |
| 31 | +; | |
| \ No newline at end of file |
| @@ -2,6 +2,7 @@ | ||
| 2 | 2 | |
| 3 | 3 | import cx.myhome.ckoshien.SqlFiles; |
| 4 | 4 | import cx.myhome.ckoshien.dto.BattingResultDto; |
| 5 | +import cx.myhome.ckoshien.dto.TeamBattingResultDto; | |
| 5 | 6 | import cx.myhome.ckoshien.entity.BattingSum; |
| 6 | 7 | |
| 7 | 8 | import java.util.ArrayList; |
| @@ -25,6 +26,7 @@ | ||
| 25 | 26 | |
| 26 | 27 | public List<BattingSum> list; |
| 27 | 28 | public List<BattingResultDto> battingResultDtos; |
| 29 | + public List<TeamBattingResultDto> teamBattingResultDtos; | |
| 28 | 30 | /** |
| 29 | 31 | * 識別子でエンティティを検索します。 |
| 30 | 32 | * |
| @@ -62,4 +64,14 @@ | ||
| 62 | 64 | battingResultDtos=jdbcManager.selectBySqlFile(BattingResultDto.class, "cx.myhome.ckoshien.sql.BattingResult.sql",param).getResultList(); |
| 63 | 65 | return battingResultDtos; |
| 64 | 66 | } |
| 67 | + | |
| 68 | + public List<TeamBattingResultDto> findTBRByPeriod(Date date,Date date2,Integer teamId){ | |
| 69 | + battingResultDtos=new ArrayList<BattingResultDto>(); | |
| 70 | + Map<String, Object> param = new HashMap<String, Object>(); | |
| 71 | + param.put("beginDate", date(date)); | |
| 72 | + param.put("endDate", date(date2)); | |
| 73 | + param.put("teamId", teamId); | |
| 74 | + teamBattingResultDtos=jdbcManager.selectBySqlFile(TeamBattingResultDto.class, "cx.myhome.ckoshien.sql.TeamBattingResult.sql",param).getResultList(); | |
| 75 | + return teamBattingResultDtos; | |
| 76 | + } | |
| 65 | 77 | } |
| \ No newline at end of file |
| @@ -39,4 +39,8 @@ | ||
| 39 | 39 | public List<League> findAllOrderByIdExceptTotal() { |
| 40 | 40 | return select().where("totalFlg=0").orderBy(desc(id())).getResultList(); |
| 41 | 41 | } |
| 42 | + | |
| 43 | + public List<League> findTotal() { | |
| 44 | + return select().where("totalFlg=1").orderBy(desc(beginDate())).getResultList(); | |
| 45 | + } | |
| 42 | 46 | } |
| \ No newline at end of file |
| @@ -7,4 +7,7 @@ | ||
| 7 | 7 | /*チーム名*/ |
| 8 | 8 | @Required |
| 9 | 9 | public String teamName; |
| 10 | + | |
| 11 | + public String teamId; | |
| 12 | + public String leagueId; | |
| 10 | 13 | } |
| @@ -0,0 +1,16 @@ | ||
| 1 | +package cx.myhome.ckoshien.dto; | |
| 2 | + | |
| 3 | +import java.util.Date; | |
| 4 | + | |
| 5 | +public class TeamBattingResultDto extends BattingResultDto { | |
| 6 | + | |
| 7 | + private static final long serialVersionUID = 1L; | |
| 8 | + public Integer teamId; | |
| 9 | + public String opponentName; | |
| 10 | + public Date gameDate; | |
| 11 | + public Integer gameNumber; | |
| 12 | + public Integer gameId; | |
| 13 | + public Integer myteamId; | |
| 14 | + public Integer leagueId; | |
| 15 | + | |
| 16 | +} |
| @@ -156,7 +156,7 @@ | ||
| 156 | 156 | opsTop10=resultLogic.returnOpsTop10(opsTop10,regAtBats); |
| 157 | 157 | //最多四球TOP10 |
| 158 | 158 | nsoTop10=battingSumService.findByPeriod(league.beginDate, league.endDate,"not_strike_out asc"); |
| 159 | - nsoTop10=resultLogic.returnNsoTop10(nsoTop10); | |
| 159 | + nsoTop10=resultLogic.returnNsoTop10(nsoTop10,regAtBats); | |
| 160 | 160 | return "stats.jsp"; |
| 161 | 161 | } |
| 162 | 162 | } |
| @@ -10,8 +10,12 @@ | ||
| 10 | 10 | import org.seasar.struts.annotation.ActionForm; |
| 11 | 11 | import org.seasar.struts.annotation.Execute; |
| 12 | 12 | |
| 13 | +import cx.myhome.ckoshien.dto.TeamBattingResultDto; | |
| 14 | +import cx.myhome.ckoshien.entity.League; | |
| 13 | 15 | import cx.myhome.ckoshien.entity.Team; |
| 14 | 16 | import cx.myhome.ckoshien.form.TeamForm; |
| 17 | +import cx.myhome.ckoshien.service.BattingSumService; | |
| 18 | +import cx.myhome.ckoshien.service.LeagueService; | |
| 15 | 19 | import cx.myhome.ckoshien.service.TeamService; |
| 16 | 20 | |
| 17 | 21 | public class TeamAction { |
| @@ -21,14 +25,23 @@ | ||
| 21 | 25 | @ActionForm |
| 22 | 26 | public TeamForm teamForm; |
| 23 | 27 | |
| 28 | + @Resource | |
| 29 | + public LeagueService leagueService; | |
| 24 | 30 | public List<Team> teamList; |
| 25 | 31 | |
| 26 | 32 | public Team team; |
| 33 | + public League league; | |
| 27 | 34 | |
| 35 | + @Resource | |
| 36 | + public BattingSumService battingSumService; | |
| 37 | + public List<TeamBattingResultDto> tbrDtos; | |
| 38 | + public List<League> leagueList; | |
| 39 | + | |
| 28 | 40 | //チーム一覧表示 |
| 29 | 41 | @Execute(validator = false) |
| 30 | 42 | public String index() { |
| 31 | 43 | teamList=teamService.findAllOrderById(); |
| 44 | + teamForm.leagueId=leagueService.findTotal().get(0).id.toString(); | |
| 32 | 45 | return "index.jsp"; |
| 33 | 46 | } |
| 34 | 47 |
| @@ -44,4 +57,20 @@ | ||
| 44 | 57 | teamService.insert(team); |
| 45 | 58 | return "createComplete.jsp"; |
| 46 | 59 | } |
| 60 | + | |
| 61 | + @Execute(urlPattern="batting/{teamId}/{leagueId}",validator = false) | |
| 62 | + public String batting(){ | |
| 63 | + if(teamForm.leagueId==null){ | |
| 64 | + teamForm.leagueId=leagueService.findTotal().get(0).id.toString(); | |
| 65 | + } | |
| 66 | + try{ | |
| 67 | + league=leagueService.findById(Integer.parseInt(teamForm.leagueId)); | |
| 68 | + }catch(NumberFormatException e){ | |
| 69 | + return "index&redirect=true"; | |
| 70 | + } | |
| 71 | + leagueList=leagueService.findTotal(); | |
| 72 | + tbrDtos=battingSumService.findTBRByPeriod(league.beginDate, league.endDate, Integer.parseInt(teamForm.teamId)); | |
| 73 | + return "result.jsp"; | |
| 74 | + | |
| 75 | + } | |
| 47 | 76 | } |
| @@ -273,13 +273,13 @@ | ||
| 273 | 273 | return opsTop10; |
| 274 | 274 | } |
| 275 | 275 | |
| 276 | - public List<BattingResultDto> returnNsoTop10(List<BattingResultDto> battingResultList){ | |
| 276 | + public List<BattingResultDto> returnNsoTop10(List<BattingResultDto> battingResultList,int regAtBats){ | |
| 277 | 277 | int j=0; |
| 278 | 278 | int k=0; |
| 279 | 279 | nsoTop10=new ArrayList<BattingResultDto>(); |
| 280 | 280 | for(int i=0;i<battingResultList.size();i++){ |
| 281 | 281 | battingResultDto=new BattingResultDto(); |
| 282 | - if(battingResultList.get(i).notStrikeOut>0){ | |
| 282 | + if(battingResultList.get(i).tpa>=regAtBats &&battingResultList.get(i).notStrikeOut>0){ | |
| 283 | 283 | j++; |
| 284 | 284 | if (i==0){ |
| 285 | 285 | battingResultDto.rank=1; |