flowcarddao4mysqlimpl.java

用java开发的一个企业的DRP系统源码

package com.bjsxt.drp.flowcard.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import com.bjsxt.drp.basedata.AimClient;
import com.bjsxt.drp.basedata.Client;
import com.bjsxt.drp.basedata.model.Item;
import com.bjsxt.drp.exception.AppException;
import com.bjsxt.drp.flowcard.model.FlowCard;
import com.bjsxt.drp.flowcard.model.FlowCardDetail;
import com.bjsxt.drp.sysmgr.User;
import com.bjsxt.drp.util.DB;
import com.bjsxt.drp.util.PageModel;
import com.bjsxt.drp.util.datadict.ItemUnit;

public class FlowCardDao4MySqlImpl implements FlowCardDao {
	
	/**
	 * 保存流向单
	 */
	public void addFlowCard(Connection conn, FlowCard flowCard) {
		String vouNo = addFlowCardMaster(conn, flowCard);
		addFlowCardDetail(conn, vouNo, flowCard.getFlowCardDetailList());
	}
	
	/**
	 * 保存流向单主表
	 * @param conn
	 * @param flowCard FlowCard
	 * @return
	 */
	private String addFlowCardMaster(Connection conn, FlowCard flowCard) {
		String sql = "insert into t_flow_card(vou_no, fiscal_year, fiscal_period, client_id, " +
					 "opr_type, record_date, recorder_id, vou_sts, spot_flag) " + 
                     "values(?,?,?,?,?,?,?,?,?)";
			PreparedStatement pstmt = null;
			String vouNo = "";
			try {
				pstmt = conn.prepareStatement(sql);
				vouNo = getVouNo(conn);
				pstmt.setString(1, vouNo);
				pstmt.setInt(2, flowCard.getFiscalYear());
				pstmt.setInt(3, flowCard.getFiscalPeriod());
				pstmt.setString(4, flowCard.getClient().getClientId());
				pstmt.setString(5, flowCard.getOptType());
				pstmt.setTimestamp(6, new Timestamp(flowCard.getRecordDate().getTime()));
				pstmt.setString(7, flowCard.getReocorder().getUserId());
				pstmt.setString(8, flowCard.getVouSts());
				pstmt.setString(9, flowCard.getSpotFlag());
				pstmt.executeUpdate();
			} catch (SQLException e) {
				System.out.println("保存流向单失败,FlowCardDao4MySqlImpl.addFlowCardMaster()," + e);
				throw new AppException("保存流向单失败");
			}finally {
				DB.closeStmt(pstmt);
			}
			return vouNo;
	}
	
	/**
	 * 取得的单号
	 * 
	 * @param conn
	 * @return yyyymmdd0001
	 * @throws SQLException 
	 */
	private String getVouNo(Connection conn) throws SQLException {
		String currentDate = new SimpleDateFormat("yyyyMMdd").format(new Date());
		//注意max和substring的应用
		String sql = "select max(vou_no) as vou_no from t_flow_card where substring(vou_no, 1, 8)='" + currentDate  + "'";
		String vouNo = currentDate + "0001";
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			rs.next();
			if (rs.getLong(1) != 0) {
				vouNo = String.valueOf(rs.getLong(1) + 1);
			}
		}finally {
			DB.closeRs(rs);
			DB.closeStmt(stmt);
		}
		return vouNo;
	}
	
	/**
	 * 保存流向单明细表
	 * @param conn
	 * @param vouNo 单号
	 * @param flowCardDetailList FlowCardDetail对象的集合
	 */
	private void addFlowCardDetail(Connection conn, String vouNo, List flowCardDetailList) {
		String sql = "insert into t_flow_card_detail(vou_no, aim_id, item_no, qty) values(?,?,?,?)";
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sql);
			for (Iterator iter = flowCardDetailList.iterator(); iter.hasNext();) {
				FlowCardDetail flowCardDetail = (FlowCardDetail)iter.next();
				pstmt.setString(1, vouNo);
				pstmt.setString(2, flowCardDetail.getAimClient().getId());
				pstmt.setString(3, flowCardDetail.getItem().getItemNo());
				pstmt.setDouble(4, flowCardDetail.getQty());
				//批量更新
				pstmt.addBatch();
			}
			pstmt.executeBatch();
		} catch (SQLException e) {
			System.out.println("保存流向单失败,FlowCardDao4MySqlImpl.addFlowCardDetail()," + e);
			throw new AppException("保存流向单失败");
		}finally {
			DB.closeStmt(pstmt);
		}		
	}

	public void auditFlowCardById(Connection conn, String vouNo) {
		String sql = "update t_flow_card set vou_sts='S' where vou_no=?";
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, vouNo);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			System.out.println("FlowCardDao4MySqlImpl-->>auditFlowCardById() " + e);
			throw new AppException("流向单送审失败！");
		}finally {
			DB.closeStmt(pstmt);
		}		
	}

	public void deleteFlowCardById(Connection conn, String vouNo) {
		// TODO Auto-generated method stub
		
	}
	
	/**
	 * 分页查询流向单
	 */
	public PageModel findAllFlowCards(int pageNo, int pageSize, String clientId, String beginDate, String endDate) {
		StringBuffer sbfSql = new StringBuffer();
		sbfSql.append("select a.vou_no, a.client_id, b.name as client_name, a.recorder_id, c.user_name, a.record_date ")
		      .append("from t_flow_card a, t_client b, t_user c ")
		      .append("where a.client_id=b.client_id and a.recorder_id=c.user_id and a.vou_sts='N'");
		if (clientId !=null && !clientId.equals("")) {
			sbfSql.append("and a.client_id='")
			.append(clientId)
			.append("' ");
		}
		//注意时间的比较
		sbfSql.append("and a.record_date between '")
		      .append(beginDate + " 00:00:00")
		      .append("' and '")
		      .append(endDate + " 23:59:59' ")
		      .append("order by a.vou_no ")
		      .append("limit ")
		      .append((pageNo-1) * pageSize)
		      .append(",")
		      .append(pageSize);
		System.out.println("FlowCardDao4MySqlImpl-->findAllFlowCards()-sql=" + sbfSql.toString());
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		PageModel pageModel = null;
		try {
			conn = DB.getConn();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sbfSql.toString());
			List flowCardList = new ArrayList();
			while (rs.next()) {
				FlowCard flowCard = new FlowCard();
				flowCard.setVouNo(rs.getString("vou_no"));
				Client client = new Client();
				client.setClientId(rs.getString("client_id"));
				client.setName(rs.getString("client_name"));
				flowCard.setClient(client);
				User recorder = new User();
				recorder.setUserId(rs.getString("recorder_id"));
				recorder.setUserName(rs.getString("user_name"));
				flowCard.setReocorder(recorder);
				flowCard.setRecordDate(rs.getTimestamp("record_date"));
				flowCardList.add(flowCard);
			}
			pageModel = new PageModel();
			pageModel.setPageNo(pageNo);
			pageModel.setPageSize(pageSize);
			pageModel.setList(flowCardList);
			int totalRecords = getTotalFlowCardRecords(conn, clientId, beginDate, endDate);
			pageModel.setTotalRecords(totalRecords);
		}catch(SQLException e) {
			System.out.println("FlowCardDao4MySqlImpl-->findAllFlowCards() failure:" + e);
			throw new AppException("查询流向单失败!");
		}finally{
			DB.closeRs(rs);
			DB.closeStmt(stmt);
		}
		return pageModel;
	}
	
	/**
	 * 取得查询流向单的记录数
	 * @param conn
	 * @param clientId
	 * @param beginDate
	 * @param endDate
	 * @return
	 */
	private int getTotalFlowCardRecords(Connection conn, String clientId, String beginDate, String endDate) {
		int totalRecords = 0;
		StringBuffer sbfSql = new StringBuffer();
		sbfSql.append("select count(*) from t_flow_card where ");
		if (clientId !=null && !clientId.equals("")) {
			sbfSql.append(" client_id='")
			.append(clientId)
			.append("' and ");
		}
		sbfSql.append("record_date between '")
		      .append(beginDate + "00:00:00")
		      .append("' and '")
		      .append(endDate + "23:59:59' ");
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sbfSql.toString());
			if (rs.next()) {
				totalRecords = rs.getInt(1);
			}
		}catch(SQLException e) {
			System.out.println("FlowCardDao4MySqlImpl-->getTotalFlowCardRecords() failure:" + e);
			throw new AppException("查询流向单失败!");
		}finally {
			DB.closeRs(rs);
			DB.closeStmt(stmt);
		}
		return totalRecords;
	}
	
	public FlowCard findFlowCardById(String vouNo) {
		// TODO Auto-generated method stub
		return null;
	}

	public void modifyFlowCard(Connection conn, FlowCard flowCard) {
		// TODO Auto-generated method stub
	}

	public void deleteFlowCard(Connection conn, String[] vouNos) {
		// TODO Auto-generated method stub
		
	}


}