package com.src.serviceimpl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.src.entity.Score;
import com.src.entity.SportInfo;
import com.src.entity.User;
import com.src.service.SportService;
import com.src.utils.Sqltools;
/**
 * 鎺ュ彛瀹炵幇绫�
 * @author Administrator
 *
 */
public class SportServiceImpl implements SportService {

	@Override
	public User login(String name, String pass, String type) {
		String sql="select * from User where name=? and pass=? and type=?";
		String [] params = {name,pass,type};
		ResultSet rs=null;	
		User tr = new User();
		try {
		    rs=Sqltools.excuteQuery(sql, params);
		    if (rs.next()) {
		    	tr.setId(rs.getInt("id"));
				tr.setName(rs.getString("name"));
				tr.setPass(rs.getString("pass"));
				tr.setSex(rs.getString("sex"));
				tr.setType(rs.getString("type"));
				tr.setArea(rs.getString("area"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
		
			e.printStackTrace();
		}finally{
			Sqltools.close(rs, Sqltools.getPs(), Sqltools.getConnection()); 
		}
		return tr;
	}

	@Override
	public boolean add_player(String name,String id, String pass, String sex, String area) {
		String sql = "insert into User (name,id,pass,sex,area,type) values(?,?,?,?,?,'1')";
		String [] params = {name,id,pass,sex,area};
		boolean  b = Sqltools.excuteUpdates(sql, params);
		return b;
	}

	@Override
	public boolean edit_player(User user) {
		String sql = "update user set name=?,pass=?,sex=?,area=? where id = ?";
		String [] params = {user.getName(),user.getPass(),user.getSex(),user.getArea(),user.getId()+""};
		boolean  b = Sqltools.excuteUpdates(sql, params);
		return b;
	}

	@Override
	public User select_playerid(String id) {
		String sql = "select * from user where id = '"+id+"'" ;
		ResultSet rs = Sqltools.excuteQuery(sql, null);
		User tr =new User();
		try{
			
			if(rs.next()){
				tr.setId(rs.getInt("id"));
				tr.setName(rs.getString("name"));
				tr.setPass(rs.getString("pass"));
				tr.setSex(rs.getString("sex"));
				tr.setType(rs.getString("type"));
				tr.setArea(rs.getString("area"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Sqltools.close(rs,null,null);
		}
		return tr;
	}

	@Override
	public boolean delete_player(String id) {
		String sql = "delete from user where id=?";
		String [] params = {id};
		boolean  b = Sqltools.excuteUpdates(sql, params);
		return b;
	}

	@Override
	public boolean add_sport(String sport_name, String sport_area,String id,String sport_sex) {
		String sql = "insert into sport_info (sport_name,sport_area,id,sport_sex) values(?,?,?,?)";
		String [] params = {sport_name,sport_area,id,sport_sex};
		boolean  b = Sqltools.excuteUpdates(sql, params);
		return b;
	}

	@Override
	public SportInfo select_sportid(String id) {
		String sql = "select * from sport_info where id ='"+id+"'";
		ResultSet rs = Sqltools.excuteQuery(sql, null);
		SportInfo tr =new SportInfo();
		try{
			
			if(rs.next()){
				tr.setId(rs.getString("id"));
				tr.setSport_name(rs.getString("sport_name"));
				tr.setSport_area(rs.getString("sport_area"));
				tr.setSport_sex(rs.getString("sport_sex"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Sqltools.close(rs,null,null);
		}
		return tr;
	}

	@Override
	public boolean edit_sport(String sport_name, String sport_area,String id ,String sport_sex) {
		String sql = "update sport_info set sport_name=?,sport_area=?,sport_sex=? Where id=?";
		String [] params = {sport_name,sport_area,sport_sex,id};
		boolean  b = Sqltools.excuteUpdates(sql, params);
		return b;
	}

	@Override
	public boolean delete_sport(String id) {
		String sql = "delete from sport_info where id=?";
		String [] params = {id};
		boolean  b = Sqltools.excuteUpdates(sql, params);
		return b;
	}

	@Override
	public List<SportInfo> sport_list() {
		String sql = "select * from sport_info";
		ResultSet rs = Sqltools.excuteQuery(sql, null);
		List<SportInfo> list = new ArrayList<SportInfo>();
		try{
			
			while(rs.next()){
				SportInfo tr = new SportInfo();
				
				tr.setSport_name(rs.getString("sport_name"));
				tr.setSport_area(rs.getString("sport_area"));
				tr.setId(rs.getString("id"));
				tr.setSport_sex(rs.getString("sport_sex"));
				list.add(tr);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Sqltools.close(rs,null,null);
		}
		return list;
	}

	@Override
	public List<User> user_list() {
		String sql = "select * from user where type = '1'";  //鏌ヨ绫诲瀷绛変簬涓�杩愬姩鍛�
		ResultSet rs = Sqltools.excuteQuery(sql, null);
		List<User> list = new ArrayList<User>();
		try{
			
			while(rs.next()){
				User tr = new User();
				tr.setId(rs.getInt("id"));
				tr.setName(rs.getString("name"));
				tr.setPass(rs.getString("pass"));
				tr.setSex(rs.getString("sex"));
				tr.setArea(rs.getString("area"));
				list.add(tr);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Sqltools.close(rs,null,null);
		}
		return list;
		
	}

	@Override
	public List<Score> select_score() {
		String sql = "select t.id,t.sex,t.user_name,t.xm_name,t.xm_cj,date_format(t.time, '%Y-%m-%d') time from score t order by xm_name DESC,sex asc,t.xm_cj desc"; //鏄剧ず鎴愮哗
		ResultSet rs = Sqltools.excuteQuery(sql, null);
		List<Score> list = new ArrayList<Score>();
		try{
			
			while(rs.next()){
				Score tr = new Score();
				tr.setId(rs.getInt("id"));
				tr.setName(rs.getString("user_name")); //杩愬姩鍛樺鍚�
				tr.setSex(rs.getString("sex")); 
				tr.setXm_name(rs.getString("xm_name")); //椤圭洰鍚嶇О
				tr.setXm_cj(rs.getString("xm_cj")); //椤圭洰鎴愮哗
				tr.setTime(rs.getString("time")); //鏃堕棿
				list.add(tr);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Sqltools.close(rs,null,null);
		}
		return list;
	}

	@Override
	public boolean add_score(String name,String sex,String xm_name, String xm_cj) {
		String sql = "insert into score (user_name,sex,xm_name,xm_cj,time) values(?,?,?,?,now())";
		String [] params = {name,sex,xm_name,xm_cj};
		boolean  b = Sqltools.excuteUpdates(sql, params);
		return b;
	}

	@Override
	public boolean delete_score(String id) {
		String sql = "delete  from score where id=?";
		String [] params = {id};
		boolean  b = Sqltools.excuteUpdates(sql, params);
		return b;
	}

	@Override
	public boolean edit_score(String id,String cj) {
		String sql = "update score set xm_cj=? where id = ?";
		String [] params = {cj,id};
		boolean  b = Sqltools.excuteUpdates(sql, params);
		return b;
	}

	@Override
	public Score select_scoreid(String id) {
		String sql = "select t.id,t.user_name,t.sex,t.xm_name,t.xm_cj,date_format(t.time, '%Y-%m-%d') time from score t where t.id = '"+id+"'" ;
		ResultSet rs = Sqltools.excuteQuery(sql, null);
		Score tr =new Score();
		try{
			
			if(rs.next()){
				tr.setId(rs.getInt("id"));
				tr.setName(rs.getString("user_name")); //杩愬姩鍛樺鍚�
				tr.setName_zz(""); //缁勭粐鍚嶇О
				tr.setSex(rs.getString("sex")); 
				tr.setXm_name(rs.getString("xm_name")); //椤圭洰鍚嶇О
				tr.setXm_cj(rs.getString("xm_cj")); //椤圭洰鎴愮哗
				tr.setTime(rs.getString("time")); //鏃堕棿
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Sqltools.close(rs,null,null);
		}
		return tr;
	}

	@Override
	public List<Score> select_tt() {
		String sql = "select sum(a.xm_cj) xm_cj,b.area from score a, user b where a.user_name = b.`name` group by b.area order by sum(a.xm_cj) desc";
		ResultSet rs = Sqltools.excuteQuery(sql, null);
		List<Score> list = new ArrayList<Score>();
		try{
			
			while(rs.next()){
				Score tr = new Score();
				tr.setName(rs.getString("area")); //闆嗗洟鍚嶇О
				tr.setXm_name(rs.getString("xm_cj")); //鎴愮哗
				list.add(tr);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Sqltools.close(rs,null,null);
		}
		return list;
	}

	@Override
	public List<Score> select_detail(String area_id) {
		String sql = "select a.user_name,a.xm_name,a.xm_cj from  score a, user b where a.user_name = b.`name` and b.area = '"+area_id+"'";
		ResultSet rs = Sqltools.excuteQuery(sql, null);
		List<Score> list = new ArrayList<Score>();
		try{
			
			
			while(rs.next()){
				Score tr = new Score();
				tr.setName(rs.getString("user_name")); //杩愬姩鍛樺鍚�
				tr.setXm_name(rs.getString("xm_name")); //椤圭洰鍚嶇О
				tr.setXm_cj(rs.getString("xm_cj")); //椤圭洰鎴愮哗
				list.add(tr);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Sqltools.close(rs,null,null);
		}
		return list;
		
	}

}