package data;

import java.util.*;
import java.sql.*;
import java.io.*;

public class shopdb {
	Connection dbcon = null;

	Statement stmt = null;

	ResultSet result = null;

	String driver = "";

	String url = "";

	String user = "";

	String password = "";
	
	public shopdb() {
		try {
			InputStream fis = getClass().getResourceAsStream(
					"jdbcsql.properties");
			Properties ps = new Properties();
			ps.load(fis);
			driver = ps.getProperty("driver");
			url = ps.getProperty("url");
			user = ps.getProperty("username");
			password = ps.getProperty("password");
			Class.forName(this.driver);
			this.getopenConnection();
		} catch (Exception e) {
			System.out.println(e);
		}
	}

	public Connection getConn() {
		return dbcon;
	}

	public Connection getopenConnection() {
		try {
			this.dbcon = DriverManager.getConnection(this.url, this.user,
					this.password);
			System.out.println(" ");
		} catch (SQLException e2) {
			System.out.println(e2);
		}
		return dbcon;
	}

	public ResultSet executeQuery(String query) throws SQLException {
		this.stmt = dbcon.createStatement();
		this.result = stmt.executeQuery(query);
		return result;
	}
	
	//檢查
	public boolean checkshopname (String shopname) throws SQLException {
		result = this.executeQuery("select * from shop where shopname='" + shopname
				+ "'");
		if (result.next())
			return false;
		return true;
	}
	
	public boolean checshopname (String shopname) throws SQLException {
		result = this.executeQuery("select * from shop where shopname like '%" + shopname
				+ "%'");
		if (result.next())
			return false;
		return true;
	}
	public boolean checksoldid (String soldid) throws SQLException {
		result = this.executeQuery("select * from shop where soldid='" + soldid
				+ "'");
		if (result.next())
			return true;
		return false;
	}	
	public boolean checkthing() throws SQLException {
		result = this.executeQuery("select * from thing");
		if (result.next())
			return true;
		return false;
	}
	public boolean checktshop() throws SQLException {
		result = this.executeQuery("select * from shop");
		if (result.next())
			return false;
		return true;
	}
	//插入
	public void openshop(String soldid, String xinyong,String shopmes,String shoppic,String shopname)
	throws SQLException {
     String query = "insert into shop (soldid,xinyong,shopmes,shoppic,shopname) values('"+soldid+"','"+xinyong+"','"+shopmes+"','"+shoppic+"','"+shopname+"')";
        this.executeUpdate(query);
      }
	public void addthing(String shopid, String thingmes,String thingname,String starttime,String endtime,String money,String pic,String find)
	throws SQLException {
     String query = "insert into thing (shopid,thingmes,thingname,starttime,endtime,money,pic,find) values('"+shopid+"','"+thingmes+"','"+thingname+"','"+starttime+"','"+endtime+"','"+money+"','"+pic+"','"+find+"')";
        this.executeUpdate(query);
      }
	
//	瀏覽
	public ResultSet showthing(String id) throws SQLException {
		this.stmt = dbcon.createStatement();
		this.result = stmt.executeQuery("select * from thing where shopid='" + id + "'");
		return result;
		}	
	
	public ResultSet showshop(String id) throws SQLException {
		this.stmt = dbcon.createStatement();
		this.result = stmt.executeQuery("select * from shop where soldid='" + id + "'");
		return result;
		}	
	public ResultSet showths(String shopname) throws SQLException {
		this.stmt = dbcon.createStatement();
		this.result = stmt.executeQuery("select * from shop where shopname like '%"+shopname+"%' order by soldid");
		return result;
		}	
	
//驗(yàn)證
public boolean chongfu(String fromid,String toid) throws SQLException {
		result = this.executeQuery("select * from friend where toid='" + toid
				+ "' and fromid='" + fromid + "'");
		if (result.next())
			return true;
		return false;
	}
	
	
	
	
	public void executeUpdate(String query) throws SQLException {
		this.stmt = dbcon.createStatement();
		stmt.executeUpdate(query);
		if (stmt != null)
			stmt.close();
	}
	public void close() throws SQLException {
		if (dbcon != null)
			dbcon.close();
		if (stmt != null)
			stmt.close();
		if (result != null)
			result.close();
	}	
}