package www.zsj.DAO.impl;

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


import www.zsj.DAO.IOrderDao;
import www.zsj.entity.Food;
import www.zsj.entity.Order;
import www.zsj.utils.JDBCUtil;

public class OrderDaoImpl implements IOrderDao{

	@Override
	public List<Order> findList(int currentPage, int pageSize) {
		// TODO Auto-generated method stub
		ArrayList<Order> list = new ArrayList<Order>();
		Connection conn = JDBCUtil.getConnection();
		try {
			PreparedStatement ps = conn.prepareStatement("select b.oid,b.oname,b.status,b.fn,b.rn from (select a.*,rownum rn from (select o.oid,o.oname,o.status,wm_concat(f.fname) fn from tb_order o,order_foods fo,tb_foods f  where fo.oid=o.oid and f.fid=fo.fid group by o.oid,o.oname,o.status) a where rownum<?) b where rn>?");
			ps.setInt(1,pageSize*currentPage+1);
			ps.setInt(2,pageSize*(currentPage-1));
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Order o = new Order(rs.getInt(1),rs.getString(2),rs.getInt(3),rs.getString(4));
				list.add(o);
			}
			JDBCUtil.close(rs, ps, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	public static List<Food> findFoodList(int oid) {
		// TODO Auto-generated method stub
		List<Food> list = new ArrayList<Food>();
		Connection conn = JDBCUtil.getConnection();
		try {
			PreparedStatement ps = conn.prepareStatement("select f.fid,f.fname,f.fprice from order_foods fo left join tb_foods f on fo.fid=f.fid where fo.oid=?");
			ps.setInt(1, oid);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Food f = new Food(rs.getInt(1), rs.getString(2), rs.getDouble(3));
				list.add(f);
			}
			JDBCUtil.close(rs, ps, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public int getCount() {
		// TODO Auto-generated method stub
		int count=0;
		Connection conn = JDBCUtil.getConnection();
		try {
			PreparedStatement ps = conn.prepareStatement("select count(*) from tb_order");
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				count = rs.getInt(1);
			}
			JDBCUtil.close(rs, ps, conn);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}

	@Override
	public List<Food> showFoods() {
		// TODO Auto-generated method stub
		Connection conn = JDBCUtil.getConnection();
		List<Food> list = new ArrayList<Food>();
		try {
			PreparedStatement ps = conn.prepareStatement("select * from tb_foods");
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Food f = new Food(rs.getInt(1), rs.getString(2), rs.getDouble(3));
				list.add(f);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public int addO(String oid,String oname){
		// TODO Auto-generated method stub
		Connection conn = JDBCUtil.getConnection();
		int i=0;
		try {
			PreparedStatement ps = conn.prepareStatement("insert into tb_order values(?,?,?,'1')");
			ps.setString(1, oid);
			ps.setString(2, oname);
			ps.setInt(3, 10);
			i = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}

	@Override
	public int addOF(String oid, String fid) {
		// TODO Auto-generated method stub
		Connection conn = JDBCUtil.getConnection();
		String[] s = fid.split(",");
		int i = 0;
		try {
			for (int j = 0; j < s.length; j++) {
				PreparedStatement ps = conn.prepareStatement("insert into order_foods values(myseq.nextval,?,?)");
				ps.setString(1, oid);
				ps.setString(2, s[j]);
				i = ps.executeUpdate();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}

	@Override
	public ArrayList<Food> xq(String oid) {
		// TODO Auto-generated method stub
		Connection conn = JDBCUtil.getConnection();
		ArrayList<Food> list = new ArrayList<Food>();
		try {
			PreparedStatement ps = conn.prepareStatement("select * from tb_foods where fid in (select fid from order_foods where oid=?)");
			ps.setString(1, oid);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Food f = new Food(rs.getInt(1), rs.getString(2), rs.getDouble(3));
				list.add(f);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}


}