getclothesdaoimpl.java

一个优秀的干洗店管理系统

package dao.getClothesDao.getClothesDaoImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.Vector;

import javax.swing.JOptionPane;

import vo.ClothesTypeVo;
import vo.OrderItemVo;
import vo.OrderVo;
import vo.VipCardVo;
import vo.VipConsumeVo;
import vo.VipVo;

import common.SystemFinal;

import dao.common.DBConnectionManager;
import dao.common.DbException;
import dao.common.sql.DbClothesTypeSql;
import dao.common.sql.DbGetClothesSql;
import dao.getClothesDao.GetClothesDao;

public class GetClothesDaoImpl implements GetClothesDao {
	private DBConnectionManager manager = DBConnectionManager.getInstance();

	public VipCardVo getVipCardBycard(int vipId) throws DbException {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet set = null;
		VipCardVo vipCardVo = null;
		try {
			con = manager.getConnection("oracle");
			pstmt = con.prepareStatement(DbGetClothesSql.GET_VIPCARDVO_BY_ID);
			pstmt.setInt(1, vipId);
			set = pstmt.executeQuery();
			while (set.next()) {
				int cardId = set.getInt("card_id");
				String vipPsw = set.getString("vip_pwd");
				String registDate = set.getString("regist_date").substring(0,
						10);
				String vipLevel = set.getString("vip_level");
				double restMoney = set.getDouble("rest_money");
				int points = set.getInt("vip_point");
				String cardState = set.getString("card_state");
				vipCardVo = new VipCardVo(cardId,vipId, vipPsw, registDate, vipLevel,
						restMoney, points, cardState);
			}
			if (vipCardVo == null) {
				throw new DbException("数据库中没有卡号为" + vipId + "的账号");
			}
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null, e.getMessage(), "提示",
					JOptionPane.YES_OPTION);
		} finally {
			manager.freeConnection("oracle", con);
		}
		return vipCardVo;
	}

	public VipVo getVipVoById(int vipId) throws DbException {
		if(vipId == 0)
			return null;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet set = null;
		VipVo vipVo = null;
		try {
			con = manager.getConnection("oracle");
			pstmt = con.prepareStatement(DbGetClothesSql.GET_VIPVO_BY_ID);
			pstmt.setInt(1, vipId);
			set = pstmt.executeQuery();
			while (set.next()) {
				VipCardVo vipCard = getVipCardBycard(vipId);
				String vipName = set.getString("vip_name");
				String vipSex = set.getString("vip_sex");
				String vipBirthday = set.getString("vip_birthday");
				String vipPhone = set.getString("vip_phone");
				String vipAddress = set.getString("vip_address");
				vipVo = new VipVo(vipCard, vipName, vipSex, vipBirthday,
						vipPhone, vipAddress);
			}
			if (vipVo == null) {
				throw new DbException("数据库中没有卡号为" + vipId + "的会员");
			}
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null, e.getMessage(), "提示",
					JOptionPane.YES_OPTION);
		} finally {
			manager.freeConnection("oracle", con);
		}
		return vipVo;
	}

	public double getVipDiscountByLevel(String vipLevel) throws DbException {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet set = null;
		double discount = 1;
		try {
			con = manager.getConnection("oracle");
			pstmt = con
					.prepareStatement(DbGetClothesSql.GET_VIP_DISCOUNT_BY_LEVEL);
			pstmt.setString(1, vipLevel);
			set = pstmt.executeQuery();
			while (set.next()) {
				discount = set.getDouble("vip_discount");
			}
			if (discount == 1) {
				throw new DbException("数据库中没有设置" + vipLevel + "的级别");
			}
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null, e.getMessage(), "提示",
					JOptionPane.YES_OPTION);
		} finally {
			manager.freeConnection("oracle", con);
		}
		return discount;
	}

	public long getNewOrderId() throws DbException {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet set = null;
		long newOrderId = 0;
		try {
			con = manager.getConnection("oracle");
			pstmt = con.prepareStatement(DbGetClothesSql.GET_MAX_ORDERID);
			set = pstmt.executeQuery();
			long maxOrderId = 0;
			while (set.next()) {
				maxOrderId = set.getLong("max_id");
			}
			SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
			String date = sdf.format(new Date());
			long today = Long.parseLong(date);

			if (maxOrderId == 0
					|| today != Long.parseLong(new Long(maxOrderId).toString()
							.substring(0, 8))) {
				newOrderId = today * 1000 + 1;
			} else {
				newOrderId = maxOrderId + 1;
			}
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null, e.getMessage(), "提示",
					JOptionPane.YES_OPTION);
		} finally {
			manager.freeConnection("oracle", con);
		}
		return newOrderId;
	}

	public boolean registAnOrder(OrderVo orderVo) throws DbException {
		Vector<OrderItemVo> itemVector = orderVo.getOrderItemVector();
		long orderId = orderVo.getOrderId();
		if (!registOrderItems(itemVector, orderId)){
			return false;
		}
		if (!registOrderTable(orderVo))
			return false;
		if (orderVo.isPaidOrNot() && orderVo.getCustomerId()!=0) {
			double totalValue = orderVo.getOrderValue();
			if (!updateVipCardAfterPaying(orderVo))
				return false;
			else {
				int vipId = orderVo.getCustomerId();
				String consumeDate = orderVo.getGetClothesDate();
				double neededAmount = orderVo.getOrderValue();
				double receivedAmount = neededAmount;
				double reducedAmount = 0;
				Iterator it = itemVector.iterator();
				while (it.hasNext()) {
					OrderItemVo itemVo = (OrderItemVo) it.next();
					reducedAmount += itemVo.getClothesType()
							.getUnitOriginalPrice()
							* itemVo.getClothesQuatity()
							- itemVo.getItemValue();
				}
				VipConsumeVo cosumeVo = new VipConsumeVo(orderId, vipId,
						consumeDate, neededAmount, receivedAmount,
						reducedAmount);
				registConsumevo(cosumeVo);
			}
		}
		return true;
	}

	private boolean registOrderItems(Vector<OrderItemVo> itemVector,
			long orderId) throws DbException {
		boolean flag = false;
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = manager.getConnection("oracle");
			pstmt = con.prepareStatement(DbGetClothesSql.REGIST_ORDER_ITEM);
			Iterator it = itemVector.iterator();
			int count = 0;
			while (it.hasNext()) {
				OrderItemVo itemVo = (OrderItemVo) it.next();
				pstmt.setInt(1, itemVo.getClothesType().getClothesId());
				pstmt.setString(2, itemVo.getClothesBrand());
				pstmt.setString(3, itemVo.getAccessory());
				pstmt.setString(4, itemVo.getColor());
				pstmt.setString(5, itemVo.getFlaw());
				pstmt.setString(6, itemVo.getClothesAdditionInfo());
				pstmt.setInt(7, itemVo.getClothesQuatity());
				pstmt.setDouble(8, itemVo.getItemValue());
				pstmt.setLong(9, orderId);
				pstmt.executeUpdate();
				count++;
			}
			if (count != itemVector.size()) {
				throw new DbException("订单" + orderId + "中的订单项未完全插入数据库中");
			} else {
				flag = true;
			}
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null, e.getMessage(), "提示",
					JOptionPane.YES_OPTION);
		} finally {
			manager.freeConnection("oracle", con);
		}
		return flag;
	}

	public boolean updateVipCardAfterPaying(OrderVo orderVo) throws DbException {
		boolean flag = false;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet set = null;
		double totalValue = orderVo.getOrderValue();
		try {
			// 获得消费前VIP的积分
			con = manager.getConnection("oracle");			
			pstmt = con.prepareStatement(DbGetClothesSql.GET_VIP_REST_MONEY_BY_ID);
			pstmt.setInt(1, orderVo.getCustomerId());
			set = pstmt.executeQuery();
			double vipRestMoney = 0;
			while (set.next()) {
				vipRestMoney = set.getDouble("rest_money");
			}
			if(vipRestMoney < orderVo.getOrderValue()){
				pstmt = con.prepareStatement(DbGetClothesSql.SET_ORDER_PAID_OR_NOT);
				pstmt.setString(1, "否");
				pstmt.setString(2, "否");
				pstmt.setLong(3, orderVo.getOrderId());
				int count = pstmt.executeUpdate();
				if(count == 0){
					throw new DbException("洗衣单"+orderVo.getOrderId()+"未付款，再数据库中被意外设置为已付款");
				}
				throw new DbException("会员卡上余额不足，请充值后再付款");
			}
			
			pstmt = con.prepareStatement(DbGetClothesSql.GET_VIP_POINT_BY_ID);
			pstmt.setInt(1, orderVo.getCustomerId());
			set = pstmt.executeQuery();
			int vipPrePoint = 0;
			while (set.next()) {
				vipPrePoint = set.getInt("vip_point");
			}
			// 消费后的积分
			int curVipPoint = vipPrePoint
					+ (int) (totalValue / SystemFinal.UNIT_SCORE_CONSUME);

			// 获得消费后积分所对应的级别
			pstmt = con
					.prepareStatement(DbGetClothesSql.GET_VIP_LEVEL_BY_POINT);
			pstmt.setInt(1, curVipPoint);
			set = pstmt.executeQuery();
			String newLevel = null;
			while (set.next()) {
				newLevel = set.getString("vip_level");
			}
			if (newLevel == null) {
				throw new DbException("积分已经超过最高会员级别上限，请操作员更新会员级别设置");
			}

			// 更新消费后会员的级别，余额，积分
			pstmt = con
					.prepareStatement(DbGetClothesSql.UPDATE_VIP_CARD_AFTER_PAYING);
			pstmt.setString(1, newLevel);
			pstmt.setDouble(2, totalValue);
			pstmt.setInt(3, curVipPoint - vipPrePoint);
			pstmt.setInt(4, orderVo.getCustomerId());
			int count = pstmt.executeUpdate();
			if (count == 0) {
				throw new DbException("会员卡" + orderVo.getCustomerId() + "更新失败");
			}
			flag = true;
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null, e.getMessage(), "提示",
					JOptionPane.YES_OPTION);
		} finally {
			manager.freeConnection("oracle", con);
		}
		return flag;
	}

	private boolean registOrderTable(OrderVo orderVo) throws DbException {
		boolean flag = false;
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = manager.getConnection("oracle");
			pstmt = con.prepareStatement(DbGetClothesSql.REGIST_AN_ORDER);
			pstmt.setLong(1, orderVo.getOrderId());
			pstmt.setInt(2, orderVo.getCustomerId());
			pstmt.setDouble(3, orderVo.getOrderValue());
			pstmt.setString(4, orderVo.getTakeClothesDate());
			pstmt.setString(5, orderVo.getTakeClothesDate());
			pstmt.setString(6, orderVo.isPaidOrNot() ? "是" : "否");
			pstmt.setString(7, orderVo.isTakeOrNot() ? "是" : "否");
			pstmt.setString(8, orderVo.getOperatorName());
			int count = pstmt.executeUpdate();
			if (count == 0) {
				throw new DbException("交易单" + orderVo.getOrderId() + "插入失败");
			}
			flag = true;
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null,"不好意思，请重新搜素该会员并添加相应衣服", "提示",
					JOptionPane.YES_OPTION);
		} finally {
			manager.freeConnection("oracle", con);
		}
		return flag;
	}

	public boolean registConsumevo(VipConsumeVo cosumeVo) throws DbException {
		boolean flag = false;
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = manager.getConnection("oracle");
			pstmt = con.prepareStatement(DbGetClothesSql.REGIST_VIPCUNSUMEVO);
			pstmt.setLong(1, cosumeVo.getOrderId());
			pstmt.setInt(2, cosumeVo.getVipId());
			pstmt.setString(3, cosumeVo.getConsumeDate());
			pstmt.setDouble(4, cosumeVo.getNeededAmount());
			pstmt.setDouble(5, cosumeVo.getReceivedAmount());
			pstmt.setDouble(6, cosumeVo.getReducedAmount());
			int count = pstmt.executeUpdate();
			if (count == 0) {
				throw new DbException(cosumeVo.getVipId() + "在交易单"
						+ cosumeVo.getOrderId() + "的消费记录插入失败");
			}
			flag = true;
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null, e.getMessage(), "提示",
					JOptionPane.YES_OPTION);
		} finally {
			manager.freeConnection("oracle", con);
		}
		return flag;
	}

	public Vector searchClothesTypeByNameAndService(String keyword,
			int type) throws DbException {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet set = null;
		Vector v = null;
		try {
			con = manager.getConnection("oracle");
			if (type == SEARCH_ALL_CLOTHESTYPE) {
				pstmt = con
						.prepareStatement(DbClothesTypeSql.FIND_ALL_CLOTHESTYPE);
			} else if (type == SEARCH_CLOTHESTYPE_BY_NAME) {
				pstmt = con
						.prepareStatement(DbGetClothesSql.SEARCH_CLOTHESTYPE_BY_NAME);
				pstmt.setString(1, keyword);
			} else if (type == SEARCH_CLOTHESTYPE_BY_SERVICE) {
				pstmt = con
						.prepareStatement(DbGetClothesSql.SEARCH_CLOTHESTYPE_BY_SERVICETYPE);
				pstmt.setString(1, keyword);
			}
			set = pstmt.executeQuery();
			v = new Vector();
			while (set.next()) {
				int clothesId = set.getInt("clothes_id");
				String clothesName = set.getString("clothes_name");
				String serviceType = set.getString("service_type");
				double unitOriginalPrice = set.getDouble("unit_original_price");
				double lowestDiscount = set.getDouble("lowest_discount");
				String operatorName = set.getString("operator_name");
				String addDate = set.getString("add_date").substring(0, 10);
				v.add(new ClothesTypeVo(clothesId, clothesName, serviceType,
						unitOriginalPrice, lowestDiscount, operatorName,
						addDate));
			}
			if (v.size() == 0) {
				throw new DbException("数据库中没有符合条件的记录!");
			}
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null, e.getMessage(), "提示",
					JOptionPane.YES_OPTION);
		} finally {
			manager.freeConnection("oracle", con);
		}
		return v;
	}
	
	public static final int SEARCH_CLOTHESTYPE_BY_NAME = 0; 
	public static final int SEARCH_CLOTHESTYPE_BY_SERVICE = 1;
	public static final int SEARCH_ALL_CLOTHESTYPE = 2; 
}