orderdao.java

在线购物 商品管理 用户管理 购物车 订单管理 权限控制

/**
 * 
 */
package com.qrsx.shop.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.qrsx.shop.model.Book;
import com.qrsx.shop.model.Order;
import com.qrsx.shop.model.OrderBook;
import com.qrsx.shop.model.ShopCar;
import com.qrsx.shop.model.User;

/**
 *@Author:李世海	
 *@Address:青岛软件园
 *@Date: Apr 6, 2009
 */
public class OrderDAO extends BaseDAO{
	/**
	 * 形成初步订单，直接从购物车中提取数据
	 * @throws SQLException 
	 */
	public Order listOrder(Integer userId) throws SQLException{
		User user = new User();
		UserDAO userDAO = new UserDAO();
		if(userId!=null){
		user = userDAO.findById(userId);
		}
		Order order = new Order();
		order.setPostAddress(user.getAddress());
		order.setPostcard(user.getPostcard());
		order.setContact(user.getName());
		order.setTelephone(user.getTelephone());
		order.setUserId(userId);
		Date date = new Date();
		SimpleDateFormat form = new SimpleDateFormat("yyyy-MM-dd");
		String time = form.format(date);
		order.setOnDate(time);
		ShopCarDAO shopCarDAO = new ShopCarDAO();
		List<Book> list = shopCarDAO.listBook(userId);
		
		List<OrderBook> orderBooks  = new ArrayList<OrderBook>();
		for(int i=0;i<list.size();i++){
			OrderBook orderBook = new OrderBook();
			orderBook.setBook(list.get(i));
			orderBooks.add(orderBook);
		}
		order.setOrderBooks(orderBooks);
		return order;
	}
	/**
	 * 创建初步定单
	 * @param order
	 * @throws SQLException 
	 */
	public void create(Order order,String[] amounts) throws SQLException{
		/**
		 * 插入主表中的数据
		 */
		String sql = "Insert Into `order` (onDate,amount,total,postAddress,postcard,telephone,contact,userId,state) values(?,?,?,?,?,?,?,?,?)";
		ps = conn.prepareStatement(sql);
		ShopCarDAO shopCarDAO = new ShopCarDAO();
		List<ShopCar> list =shopCarDAO.findByUserId(order.getUserId());
		ps.setString(1, order.getOnDate());
		ps.setInt(2, order.getAmount());
		ps.setDouble(3,order.getTotal());
		UserDAO userDAO = new UserDAO();
		User user = userDAO.findByUserId(order.getUserId());
		if(user!=null){
			ps.setString(4, user.getAddress());
			ps.setString(5, user.getPostcard());
			ps.setString(6, user.getTelephone());
			ps.setString(7, user.getName());
			ps.setInt(8, order.getUserId());
			ps.setInt(9, 1);
		}
		ps.executeUpdate();
		
		/**
		 * 插入附表的数据
		 * 1、查询订单号
		 */
		String sql2 = "select max(id) from `order` where userId=?";
		ps = conn.prepareStatement(sql2);
		ps.setInt(1, order.getUserId());
		ResultSet rs = ps.executeQuery();
		Integer id = 0;
		if(rs.next()){
			id = rs.getInt("max(id)");
		}
		/**
		 * 2、往附表中插入数据
		 */
		
		String sql3 = "insert into orderbook (orderId,bookId,price,amount) values (?,?,?,?)";
		for(int i=0;i<amounts.length;i++){
			ShopCar shopCar = new ShopCar();
			shopCar = list.get(i);
			ps  = conn.prepareStatement(sql3);
			ps.setInt(1, id);
			ps.setInt(2, shopCar.getBookId());
			BookDAO bookDAO = new BookDAO();
			ps.setDouble(3, bookDAO.findById(shopCar.getBookId()).getPrice());
			ps.setInt(4, Integer.valueOf(amounts[i]));
			ps.executeUpdate();
		}
	}
	/**
	 * 管理员查看所有订单
	 * @throws SQLException 
	 */
	public List<Order> findAll() throws SQLException{
		String sql = "select * from `order` where state=?";
		ps = conn.prepareStatement(sql);
		ps.setInt(1, 2);
		ResultSet rs = ps.executeQuery();
		List<Order> list = new ArrayList<Order>();
		while(rs.next()){
			Order o = new Order();
			o.setId(rs.getInt("id"));
			o.setOnDate(rs.getString("onDate"));
			o.setAmount(rs.getInt("amount"));
			o.setTotal(rs.getDouble("total"));
			o.setPostAddress(rs.getString("postAddress"));
			o.setPostcard(rs.getString("postcard"));
			o.setTelephone(rs.getString("telephone"));
			o.setContact(rs.getString("contact"));
			o.setUserId(rs.getInt("userId"));
			list.add(o);
		}
		return list;
	}
	/**
	 * 用户查看自己的订单
	 * @throws SQLException 
	 */
	public List<Order> findByUserId(Integer userId) throws SQLException{
		String sql = "select * from `order` where userId=?";
		ps = conn.prepareStatement(sql);
		ps.setInt(1, userId);
		ResultSet rs = ps.executeQuery();
		List<Order> list = new ArrayList<Order>();
		while(rs.next()){
			Order o = new Order();
			o.setId(rs.getInt("id"));
			o.setOnDate(rs.getString("onDate"));
			o.setAmount(rs.getInt("amount"));
			o.setTotal(rs.getDouble("total"));
			o.setPostAddress(rs.getString("postAddress"));
			o.setPostcard(rs.getString("postcard"));
			o.setTelephone(rs.getString("telephone"));
			o.setContact(rs.getString("contact"));
			o.setUserId(rs.getInt("userId"));
			o.setState(rs.getInt("state"));
			OrderBookDAO orderBookDAO = new OrderBookDAO();
			List<OrderBook> orderBooks = orderBookDAO.findById(o.getId());
			o.setOrderBooks(orderBooks);
			list.add(o);
		}
		return list;
	}
	/**
	 * 根据主键删除订单
	 * @throws SQLException 
	 */
	public void delete(Integer id) throws SQLException{
		String sql = "delete from `order` where id=?";
		ps = conn.prepareStatement(sql);
		ps.setInt(1, id);
		ps.executeUpdate();
		
		/**
		 * 删除附表信息
		 */
		String sql2 = "delete from orderbook where orderId=?";
		ps = conn.prepareStatement(sql2);
		ps.setInt(1, id);
		ps.executeUpdate();
		
	}
	/**
	 * 提交正式订单
	 * @throws SQLException 
	 */
	public void update(Order order) throws SQLException{
		String sql = "update `order` set postAddress=?,postcard=?,telephone=?,contact=?,state=? where id =?";
		ps = conn.prepareStatement(sql);
		ps.setString(1, order.getPostAddress());
		ps.setString(2, order.getPostcard());
		ps.setString(3, order.getTelephone());
		ps.setString(4, order.getContact());
		ps.setInt(5, 2);
		ps.setInt(6, order.getId());
		ps.executeUpdate();
	}
	/**
	 * 管理员根据主键查看订单明细
	 * @throws SQLException 
	 */
	public Order findById(Integer id) throws SQLException{
		String sql = "select * from `order` where id=?";
		ps = conn.prepareStatement(sql);
		ps.setInt(1, id);
		ResultSet rs = ps.executeQuery();
		Order order = new Order();
		if(rs.next()){
			order.setId(rs.getInt("id"));
			order.setOnDate(rs.getString("onDate"));
			order.setAmount(rs.getInt("amount"));
			order.setTotal(rs.getDouble("total"));
			order.setPostAddress(rs.getString("postAddress"));
			order.setPostcard(rs.getString("postcard"));
			order.setTelephone(rs.getString("telephone"));
			order.setContact(rs.getString("contact"));
			order.setUserId(rs.getInt("userId"));
			order.setState(rs.getInt("state"));
			OrderBookDAO orderBookDAO = new OrderBookDAO();
			List<OrderBook> orderBooks = orderBookDAO.findById(order.getId());
			order.setOrderBooks(orderBooks);
		}
		return order;
	}
}