package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.bean.Shop;
import com.util.JDBCUtil;

public class Dao {

		
	public static int getcount(){
		int count=0;
		Connection conn = JDBCUtil.getconn();
		try {
			PreparedStatement ps = conn.prepareStatement("select count(*) from shop");
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				count=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}
	public static ArrayList<Shop> query(String id,String name,String content,String start,String end,String bname){
		ArrayList<Shop> list = new ArrayList<Shop>();
		Connection conn = JDBCUtil.getconn();
		String sql="select  s.*,b.bname from shop s,brand b where s.bid = b.bid  ";
		if (id!=null&&!"".equals(id)) {
			sql+="and id='"+id+"'";
		}
		if (name!=null&&!"".equals(name)) {
			sql+="and name like '%"+name+"%'";
		}
		if (content!=null&&!"".equals(content)) {
			sql+="and content like '%"+content+"%'";
		}
		if (start!=null&&!"".equals(start)&&end!=null&&!"".equals(end)) {
			sql+="and datea >= to_date('"+start+"','yyyy-MM-dd HH24:mi:ss') and datea <= to_date('"+end+"','yyyy-MM-dd HH24:mi:ss')";
		}
		if (bname!=null&&!"".equals(bname)) {
			sql+="and b.bname like '%"+bname+"%'";
		}
		String sql1="select * from ("+sql+")";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				Shop s = new Shop(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getInt(5), rs.getString(6));
				list.add(s);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	public static int del(String id){
		int i=0;
		Connection conn = JDBCUtil.getconn();
		try {
			PreparedStatement ps = conn.prepareStatement("delete shop where id in ("+id+")");
			i = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}
	
	public static ArrayList<Shop> show(int currpage, int pagesize){
		ArrayList<Shop> list = new ArrayList<Shop>();
		Connection conn = JDBCUtil.getconn();
		String sql="";
		
		try {
			PreparedStatement ps = conn.prepareStatement("select * from (select  s.*,b.bname,rownum r from shop s,brand b where s.bid = b.bid) where r>? and r<?");
			ps.setInt(1,pagesize*(currpage-1));
			ps.setInt(2,pagesize*currpage+1);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				Shop s = new Shop(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getInt(5), rs.getString(6));
				list.add(s);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	public static int adds(String name,String content,String datea,String bid){
		int i=0;
		Connection conn = JDBCUtil.getconn();
		try {
			PreparedStatement ps = conn.prepareStatement("insert into shop values(stu_squ.nextval,?,?,to_date(?,'yyyy-MM-dd HH24:mi:ss'),?)");
			ps.setString(1, name);
			ps.setString(2, content);
			ps.setString(3, datea);
			ps.setString(4, bid);
			i = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}
	public static int xg(String name,String content,String datea,String bid,String id){
		int i=0;
		Connection conn = JDBCUtil.getconn();
		try {
			PreparedStatement ps = conn.prepareStatement("update shop set name=?,content=?,datea=to_date(?,'yyyy-MM-dd HH24:mi:ss'),bid=? where id=?");
			ps.setString(1, name);
			ps.setString(2, content);
			ps.setString(3, datea);
			ps.setString(4, bid);
			ps.setString(5, id);
			i = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}
	public static ArrayList<Shop> toAdd(){
		ArrayList<Shop> list = new ArrayList<Shop>();
		Connection conn = JDBCUtil.getconn();
		String sql="select * from brand";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				Shop s = new Shop(rs.getInt(1), rs.getString(2));
				list.add(s);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	public static Shop upd(String id){
		Shop s=null;
		Connection conn = JDBCUtil.getconn();
		String sql="select * from shop where id=?";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, id);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				s = new Shop(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getInt(5),null);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return s;
	}
}