package database;

import gonggao.GG;
import guestbook.GuestBook;

import java.sql.*;
import java.util.ArrayList;

import link.Link;

import shuju.ShuJu;

import news.News;

import beans.Users;

public class DBConn {
	
	private Connection conn;// 數據庫鏈接對象

	private PreparedStatement stmt;// 預編譯對象
	
	private CallableStatement  sc;

	private ResultSet rs;// 結果集對象
	public DBConn()
	{
		try {

			Class.forName("org.gjt.mm.mysql.Driver");

			conn = DriverManager
					.getConnection("jdbc:mysql://localhost:3306/meikuang?user=root&password=1234&useUnicode=true&characterEncoding=gb2312");
			
		} catch (Exception e) {

		}
	}
	public ArrayList<GuestBook> selectAllGuestBook()
	{
		ArrayList<GuestBook> a = new ArrayList<GuestBook>();
		try {
			stmt = conn.prepareStatement("select * from guestbook order by id DESC");
			rs = stmt.executeQuery();
			while(rs.next())
			{
				GuestBook gb = new GuestBook();
				gb.setId(rs.getInt("id"));
				gb.setName(rs.getString("name"));
				gb.setFace(rs.getString("face"));
				gb.setMail(rs.getString("mail"));
				gb.setQq(rs.getString("qq"));
				gb.setContent(rs.getString("content"));
				gb.setUsertitle(rs.getString("usertitle"));
				gb.setRecontent(rs.getString("recontent"));
				gb.setMydate(rs.getString("mydate").toString());
				//gb.setRemydate(rs.getString("remydate"));
				a.add(gb);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return a;
	}
	public Users loginGuest(String name,String pwd)
	{
		try {
			stmt = conn.prepareStatement("select * from users where name = ? and pwd = ?");
			stmt.setString(1, name);
			stmt.setString(2, pwd);
			rs = stmt.executeQuery();
			if(rs.next())
			{
				Users u = new Users();
				u.setId(rs.getInt("id"));
				u.setName(rs.getString("name"));
				u.setPwd(rs.getString("pwd"));
				u.setPower(rs.getInt("power"));
				u.setEmail(rs.getString("email"));
				return u;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	public void addGuest(GuestBook gb)
	{
		try {
			stmt = conn.prepareStatement("insert into guestbook(name,face,mail,qq,usertitle,content,mydate) values(?,?,?,?,?,?,?)");
			stmt.setString(1, gb.getName());
			stmt.setString(2, gb.getFace());
			stmt.setString(3, gb.getMail());
			stmt.setString(4, gb.getQq());
			stmt.setString(5, gb.getUsertitle());
			stmt.setString(6, gb.getContent());
			stmt.setString(7, gb.getMydate());
			stmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public void addNews(News news)
	{
		try {
			stmt = conn.prepareStatement("insert into news(title,users,content,classes) values(?,?,?,?)");
			stmt.setString(1, news.getTitle());
			stmt.setString(2, news.getUsers());
			stmt.setString(3, news.getContent());
			stmt.setString(4, news.getClasses());
			stmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public ArrayList<News> selectNews()
	{
		ArrayList<News> a = new ArrayList<News>();
		try {
			stmt = conn.prepareStatement("SELECT n.*,c.name FROM news n,newsclass c where n.classes=c.id order by n.id DESC");
			rs = stmt.executeQuery();
			while(rs.next())
			{
				News news = new News();
				news.setId(rs.getInt("id"));
				news.setTitle(rs.getString("title"));
				news.setUsers(rs.getString("users"));
				news.setContent(rs.getString("content"));
				news.setClasses(rs.getString("name"));
				news.setClanum(rs.getInt("classes"));
				a.add(news);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return a;
	}
	public News showNews(int id)
	{
		News news = new News();
		try {
			stmt = conn.prepareStatement("select * from news where id = ?");
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			if(rs.next())
			{
				news.setId(rs.getInt("id"));
				news.setTitle(rs.getString("title"));
				news.setUsers(rs.getString("users"));
				news.setContent(rs.getString("content"));
				return news;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	public void reg(Users u)
	{
		try {
			stmt=conn.prepareStatement("insert into users(name,pwd,email) values(?,?,?)");
			stmt.setString(1, u.getName());
			stmt.setString(2, u.getPwd());
			stmt.setString(3, u.getEmail());
			stmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public void editNews(News news)
	{
		try {
			stmt=conn.prepareStatement("update news set title = ? , users = ? , content= ? where id = ?");
			stmt.setString(1, news.getTitle());
			stmt.setString(2, news.getUsers());
			stmt.setString(3, news.getContent());
			stmt.setInt(4, news.getId());
			stmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public void delNews(int id)
	{
		try {
			stmt=conn.prepareStatement("delete from news where id = ?");
			stmt.setInt(1, id);
			stmt.execute();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public void addGG(GG gg)
	{
		try {
			stmt=conn.prepareStatement("insert into gonggao(title,content) values(?,?)");
			stmt.setString(1, gg.getTitle());
			stmt.setString(2, gg.getContent());
			stmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public ArrayList selectGG()
	{
		ArrayList a = new ArrayList();
		
		try {
			stmt = conn.prepareStatement("select * from gonggao order by id DESC");
			rs = stmt.executeQuery();
			while(rs.next())
			{
				GG gg = new GG();
				gg.setId(rs.getInt("id"));
				gg.setTitle(rs.getString("title"));
				gg.setContent(rs.getString("content"));
				a.add(gg);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return a;
	}
	public ArrayList selectSG()
	{
		ArrayList a = new ArrayList();
		
		try {
			stmt = conn.prepareStatement("select * from shuju order by id DESC");
			rs = stmt.executeQuery();
			while(rs.next())
			{
				ShuJu shuju = new ShuJu();
				shuju.setId(rs.getInt("id"));
				shuju.setShidu(rs.getDouble("shidu"));
				shuju.setFangchen(rs.getDouble("fangchen"));
				shuju.setFengsu(rs.getDouble("fengsu"));
				shuju.setWasi(rs.getDouble("wasi"));
				shuju.setTime(rs.getString("time"));
				a.add(shuju);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return a;
	}
	public ShuJu time(int time)
	{
		//ArrayList a = new ArrayList();
		ShuJu shuju = new ShuJu();
		try {
			stmt = conn.prepareStatement("select avg(wasi),avg(fengsu),avg(fangchen),avg(shidu) from shuju where YEAR(time)=?");
			stmt.setInt(1, time);
			rs = stmt.executeQuery();
			if(rs.next())
			{
				
				//shuju.setId(rs.getInt("id"));
				shuju.setShidu(rs.getDouble(4));
				//rs.getString(1);
				shuju.setFangchen(rs.getDouble(3));
				shuju.setFengsu(rs.getDouble(2));
				shuju.setWasi(rs.getDouble(1));
				shuju.setTime(time+"");
				//a.add(shuju);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return shuju;
	}
	public Users cklogin(String username,String password)
	{
		try {
			stmt=conn.prepareStatement("select * from users where name=? and pwd=?");
			stmt.setString(1, username);
			stmt.setString(2, password);
			rs=stmt.executeQuery();
			if(rs.next())
			{
				Users u = new Users();
				u.setId(rs.getInt("id"));
				u.setName(rs.getString("name"));
				u.setPwd(rs.getString("pwd"));
				u.setPower(rs.getInt("power"));
				u.setEmail(rs.getString("email"));
				return u;
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}
	public void delGuest(int id)
	{
		try {
			stmt=conn.prepareStatement("delete from guestbook where id = ?");
			stmt.setInt(1, id);
			stmt.execute();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public void reGuest(GuestBook gb)
	{
		try {
			stmt = conn.prepareStatement("update guestbook set recontent = ? , remydate = ?  where id = ?");
			stmt.setString(1, gb.getRecontent());
			stmt.setString(2, gb.getRemydate());
			stmt.setInt(3, gb.getId());
			stmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public GuestBook showGuest(int id)
	{
		GuestBook gb = new GuestBook();
		try {
			stmt = conn.prepareStatement("select * from guestbook where id = ?");
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			if(rs.next())
			{
				gb.setId(rs.getInt("id"));
				gb.setName(rs.getString("name"));
				gb.setFace(rs.getString("face"));
				gb.setMail(rs.getString("mail"));
				gb.setQq(rs.getString("qq"));
				gb.setContent(rs.getString("content"));
				gb.setUsertitle(rs.getString("usertitle"));
				gb.setRecontent(rs.getString("recontent"));
				gb.setMydate(rs.getString("mydate").toString());
				return gb;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	public void editguest(GuestBook gb)
	{
		try {
			stmt=conn.prepareStatement("update guestbook set name = ? , face = ? , content= ? ,mail = ? ,qq = ? ,usertitle = ? where id = ?");
			stmt.setString(1, gb.getName());
			stmt.setString(2, gb.getFace());
			stmt.setString(3, gb.getContent());
			stmt.setString(4, gb.getMail());
			stmt.setString(5, gb.getQq());
			stmt.setString(6, gb.getUsertitle());
			stmt.setInt(7, gb.getId());
			stmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public void delGG(int id)
	{
		try {
			stmt=conn.prepareStatement("delete from gonggao where id = ?");
			stmt.setInt(1, id);
			stmt.execute();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public void addLink(Link link)
	{
		try {
			stmt = conn.prepareStatement("insert into links(title,addr) values(?,?)");
			stmt.setString(1, link.getTitle());
			stmt.setString(2, link.getAddr());
			stmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public ArrayList<Link> showLink()
	{
		ArrayList<Link> a = new ArrayList<Link>();
		try {
			stmt = conn.prepareStatement("select * from links order by id DESC");
			rs = stmt.executeQuery();
			while(rs.next())
			{
				Link link = new Link();
				link.setId(rs.getInt("id"));
				link.setTitle(rs.getString("title"));
				link.setAddr(rs.getString("addr"));
				a.add(link);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return a;
	}
}