项目实训--物流管理系统之调度中心

调度中心

调度中心根据订单生成任务单，同时生成货物调拨单（退货不生成货物调拨单）

订单操作

对订单进行调度分配，生成任务单和调拨单。

任务单查询

查询所有任务单的详细信息，包括新订订单产生的任务单和退货产生的任务单。

调拨单查询

查询所有任务单的详细信息

代码部分:

package com.neusoft.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.neusoft.services.support.JdbcSupport;

/**
 * 
 * @author xukai
 * 以下为调度中心的所有操作以及查询
 */
public class TransferCenterDao extends JdbcSupport{
	
	/**
	 * 查询所有的订单信息
	 */
	public List<Map<Object,Object>> queryAll() throws SQLException{
		String sql = "SELECT ORDER_ID,CUSTOMER_NAME,START_DATE,DEADLINE,ORDER_STATUS,ADDRESS" +
						" FROM ORDER_INFO,CUSTOMER_INFO"+
						" WHERE ORDER_INFO.CUSTOMER_ID = CUSTOMER_INFO.CUSTOMER_ID" +
						" AND ORDER_TYPE = '新订' " +
						" AND ORDER_STATUS = '可分配' ";
		return this.queryToMapList(sql, null);
	}
	
	/**
	 * 查询单条订单信息
	 */
	public Map<Object,Object> queryMore(Object val) throws SQLException{
		String sql = " SELECT ORDER_ID,PRODUCT_NAME,O.PRODUCT_ID,PRODUCT_ACCOUNT,UNIT,PRICE,PAY_MONEY" +
				",START_DATE,DEADLINE,ORDER_STATUS,ORDER_TYPE,CUSTOMER_NAME,C.CUSTOMER_ID,CELLPHONE,POST,ADDRESS" +
				" FROM ORDER_INFO O,CUSTOMER_INFO C,PRODUCT_INFO P" +
				" WHERE O.PRODUCT_ID = P.PRODUCT_ID AND O.CUSTOMER_ID=C.CUSTOMER_ID AND O.ORDER_ID=?";
		return this.queryToMap(sql, val);
	}

	/**
	 * 订单操作 -> 单据操作
	 * 事务:
	 * 1.任务单	添加信息 
	 * 2.调拨单	添加信息
	 * 3.订单 	修改状态
	 */
	public boolean danJuCaoZuo(Object[] obj1,Object[] obj2,Object [] obj3) throws SQLException{
		String sql1 = "INSERT INTO MISSION_INFO(MISSION_ID,ORDER_ID,CUSTOMER_ID,WAREHOUSE_ID" +
						",SUBSTATION_NAME,TAKEOUT_NAME,TAKEOUT_DATE,MISSION_DATE)" +
						" VALUES(SEQ_MISSION_ID.NEXTVAL,?,?,?,?,?," +
						"TO_CHAR(SYSDATE,'YYYY-MM-DD'),TO_CHAR(SYSDATE,'YYYY-MM-DD'))";
		String sql2 = "UPDATE ORDER_INFO SET ORDER_STATUS = ? WHERE ORDER_ID = ? ";
		String sql3 = "INSERT INTO TRANSFER_INFO(TRANSFER_ID,ORDER_ID,MISSION_ID,CUSTOMER_ID,WAREHOUSE_ID,TRANSFER_DATE,TRANSFER_TYPE,TRANSFER_STATUS)" + 
						" VALUES(SEQ_TRANSFER_ID.NEXTVAL,?,SEQ_MISSION_ID.CURRVAL,?,?,TO_CHAR(SYSDATE,'YYYY-MM-DD'),'出库','可分配')";
		this.pstmAddVal(sql1, obj1);
		this.pstmAddVal(sql2, obj2);
		this.pstmAddVal(sql3, obj3);
		return this.executeListPstm();
	}
	
	/**
	 * 通过库房名查询库房ID
	 */
	public Map<Object,Object> findWarehouseName(Object val) throws SQLException{
		String sql = "SELECT WAREHOUSE_ID FROM WAREHOUSE_INFO WHERE WAREHOUSE_NAME = ?";
		return this.queryToMap(sql, val);
	}

	/**
	 * 查询所有任务单
	 */
	public List<Map<Object,Object>> findAllMission() throws SQLException{
		String sql = "SELECT MISSION_ID,CUSTOMER_NAME,MISSION_DATE,PRODUCT_NAME,ORDER_STATUS,ADDRESS" +
				"             FROM MISSION_INFO M,ORDER_INFO O,CUSTOMER_INFO C,PRODUCT_INFO P" +
				"             WHERE M.ORDER_ID = O.ORDER_ID " +
				"                   AND M.CUSTOMER_ID = C.CUSTOMER_ID " +
				"                   AND O.PRODUCT_ID = P.PRODUCT_ID" +
				"					AND M.MISSION_TYPE <>'退货' ";
		return this.queryToMapList(sql, null);
	}
	
	/**
	 * 查询任务单详细信息
	 */
	public Map<Object,Object> queryMoreMission(Object val) throws SQLException{
		String sql = "SELECT M.MISSION_ID,O.ORDER_ID,P.PRODUCT_NAME,P.PRODUCT_ID,PRODUCT_ACCOUNT,UNIT,PRICE,PAY_MONEY," +
		"        START_DATE,DEADLINE,MISSION_STATUS,MISSION_TYPE,CUSTOMER_NAME,CELLPHONE,POST,ADDRESS,SUBSTATION_NAME" +
		"        FROM MISSION_INFO M,ORDER_INFO O,CUSTOMER_INFO C,PRODUCT_INFO P" +
		"        WHERE M.ORDER_ID = O.ORDER_ID " +
		"              AND M.CUSTOMER_ID = C.CUSTOMER_ID " +
		"              AND O.PRODUCT_ID = P.PRODUCT_ID " +
		"              AND M.MISSION_ID = ?";
		return this.queryToMap(sql, val);
	}
	
	/**
	 * 查询所有调拨单信息
	 */
	public List<Map<Object,Object>> queryAllTransfer() throws SQLException{
		String sql = "SELECT TRANSFER_ID,CUSTOMER_NAME,TRANSFER_DATE,PRODUCT_NAME,TRANSFER_STATUS,ADDRESS" +
				"             FROM TRANSFER_INFO T,CUSTOMER_INFO C,PRODUCT_INFO P,ORDER_INFO O" +
				"             WHERE T.CUSTOMER_ID=C.CUSTOMER_ID " +
				"             AND O.ORDER_ID=T.ORDER_ID" +
				"             AND O.PRODUCT_ID=P.PRODUCT_ID";
		return this.queryToMapList(sql, null);
	}
	
	/**
	 * 通过调拨单编号查看详细信息
	 */
	public Map<Object,Object> queryMoreTransfer(Object val) throws SQLException{
		String sql = "SELECT T.TRANSFER_ID,M.MISSION_ID,O.ORDER_ID,P.PRODUCT_NAME,P.PRODUCT_ID,O.PRODUCT_ACCOUNT,P.UNIT,P.PRICE," +
				"	       O.PAY_MONEY,O.START_DATE,O.DEADLINE,M.MISSION_STATUS,M.MISSION_TYPE,C.CUSTOMER_NAME," +
				"	       C.CELLPHONE,C.POST,C.ADDRESS,W.WAREHOUSE_NAME" +
				"	       FROM TRANSFER_INFO T,CUSTOMER_INFO C,PRODUCT_INFO P,ORDER_INFO,MISSION_INFO M,ORDER_INFO O,WAREHOUSE_INFO W" +
				"	       WHERE T.ORDER_ID=O.ORDER_ID" +
				"	       AND T.MISSION_ID=M.MISSION_ID" +
				"	       AND T.CUSTOMER_ID=C.CUSTOMER_ID" +
				"	       AND O.PRODUCT_ID=P.PRODUCT_ID" +
				"	       AND T.WAREHOUSE_ID=W.WAREHOUSE_ID" +
				"		   AND T.TRANSFER_ID = ?";
		return this.queryToMap(sql, val);
	}

	/******************************************************************
	 * 							以下为条件查询
	 ******************************************************************/
	
	/**
	 * 查询所有满足条件的 订单 信息
	 */
	public List<Map<Object,Object>> findOrdersByOthers(String orderId,String startDate,
			String deadline,String customerName,String orderStatus) throws SQLException{
		StringBuilder sql = new StringBuilder("SELECT ORDER_ID,CUSTOMER_NAME,START_DATE,DEADLINE,ORDER_STATUS,ADDRESS" +
				" FROM ORDER_INFO,CUSTOMER_INFO"+
				" WHERE ORDER_INFO.CUSTOMER_ID = CUSTOMER_INFO.CUSTOMER_ID " +
				" AND ORDER_TYPE = '新订' ");
		List<String> list = new ArrayList<String>();
		if(this.checkVal(orderId)){
			sql.append(" AND ORDER_ID = ?");
			list.add(orderId);
		}
		if(this.checkVal(startDate)){
			sql.append(" AND START_DATE = ?");
			list.add(startDate);
		}
		if(this.checkVal(deadline)){
			sql.append(" AND DEADLINE = ?");
			list.add(deadline);
		}
		if(this.checkVal(customerName)){
			sql.append(" AND CUSTOMER_NAME = ?");
			list.add(customerName);
		}
		if(this.checkVal(orderStatus)){
			sql.append(" AND ORDER_STATUS = ? ");
			list.add(orderStatus);
		}
//		System.out.println("SQL : >>>>>" + sql.toString());
		return this.queryToMapList(sql.toString(), list.toArray());
	}
	
	/**
	 * 查询所有满足条件的 任务单 信息
	 */
	public List<Map<Object,Object>> findMissionsByOthers(String missionId,String missionDate,
			String customerName,String missionStatus) throws SQLException{
		StringBuilder sql = new StringBuilder("SELECT MISSION_ID,CUSTOMER_NAME,MISSION_DATE,PRODUCT_NAME,ORDER_STATUS,ADDRESS,MISSION_STATUS" +
				"   FROM MISSION_INFO M,ORDER_INFO O,CUSTOMER_INFO C,PRODUCT_INFO P" +
				"   WHERE M.ORDER_ID = O.ORDER_ID " +
				"   AND M.CUSTOMER_ID = C.CUSTOMER_ID " +
				"   AND O.PRODUCT_ID = P.PRODUCT_ID ");
		List<String> list = new ArrayList<String>();
		if(this.checkVal(missionId)){
			sql.append(" AND MISSION_ID = ?");
			list.add(missionId);
		}
		if(this.checkVal(missionDate)){
			sql.append(" AND MISSION_DATE = ?");
			list.add(missionDate);
		}
		if(this.checkVal(customerName)){
			sql.append(" AND CUSTOMER_NAME = ?");
			list.add(customerName);
		}
		if(this.checkVal(missionStatus)){
			sql.append(" AND MISSION_STATUS = ? ");
			list.add(missionStatus);
		}
//		System.out.println("SQL : >>>>>" + sql.toString());
		return this.queryToMapList(sql.toString(), list.toArray());
	}
	
	/**
	 * 查询所有满足条件的 调拨单 信息
	 */
	public List<Map<Object,Object>> findTransfersByOthers(String transferId,String transferDate,
			String customerName,String transferStatus) throws SQLException{
		StringBuilder sql = new StringBuilder("SELECT TRANSFER_ID,CUSTOMER_NAME,TRANSFER_DATE,PRODUCT_NAME,TRANSFER_STATUS,ADDRESS" +
				"  FROM TRANSFER_INFO T,CUSTOMER_INFO C,PRODUCT_INFO P,ORDER_INFO O" +
				"  WHERE T.CUSTOMER_ID=C.CUSTOMER_ID " +
				"  AND O.ORDER_ID=T.ORDER_ID" +
				"  AND O.PRODUCT_ID=P.PRODUCT_ID ");
		List<String> list = new ArrayList<String>();
		if(this.checkVal(transferId)){
			sql.append(" AND TRANSFER_ID = ?");
			list.add(transferId);
		}
		if(this.checkVal(transferDate)){
			sql.append(" AND TRANSFER_DATE = ?");
			list.add(transferDate);
		}
		if(this.checkVal(customerName)){
			sql.append(" AND CUSTOMER_NAME = ?");
			list.add(customerName);
		}
		if(this.checkVal(transferStatus)){
			sql.append(" AND TRANSFER_STATUS = ? ");
			list.add(transferStatus);
		}
//		System.out.println("SQL : >>>>>" + sql.toString());
		return this.queryToMapList(sql.toString(), list.toArray());
	}

}