purchaseda.java

医药供应链管理系统

				bean.setP_l_id(rs.getInt("p_l_id"));
				bean.setP_ver(rs.getInt("p_ver"));
				bean.setP_actual(rs.getDouble("p_actual"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return bean;
	}
	/**
	 * 修改单据为红冲单据
	 * @param p_id
	 */
	public void updatePurHongChong(int p_id){
		String sql = "update purchase set p_type = 4 where p_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, p_id);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	
	/**
	 * 返回红冲的入库单
	 * @return
	 */
	public ArrayList showHong(){
		PurchaseBean bean = null;
		ArrayList arry = new ArrayList();
		String sql = "select * from purchase where p_type = 4 order by p_time desc";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				bean = new PurchaseBean();
				bean.setP_id(rs.getInt("p_id"));
				bean.setP_no(rs.getString("p_no"));
				bean.setP_time(rs.getString("p_time"));
				bean.setP_quantity(rs.getInt("p_quantity"));
				bean.setP_price(rs.getDouble("p_price"));
				bean.setP_amount(rs.getDouble("p_amount"));
				arry.add(bean);				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 删除红冲入库单据
	 * @param p_id
	 */
	public void delPurHongChong(int p_id){
		String sql = "delete purchase where p_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, p_id);
			pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 	清空红冲入库单据
	 */
	public void delAllHongChong(){
		String sql = "delete purchase where p_type = 4";
		try {
			stmt = conn.createStatement();
			stmt.execute(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 通过入库单p_id返回入库单详细信息
	 * @param p_id
	 * @return
	 */
	public PurAllShowBean showAllByP_id(int p_id){
		PurAllShowBean bean = null;
		String sql = "SELECT purchase.p_id, purchase.p_no, produit.p_name, bigclass.bc_name, smallclass.sc_name, intercourse.i_name, purchase.p_prodate, purchase.p_effdate, purchase.p_quantity, purchase.p_price, purchase.p_amount, purchase.p_actual, purchase.p_time,purchase.p_note, warehouse.w_name, bank.b_name, purchase.p_l_id, unit.u_name " +
				"FROM produit INNER JOIN bigclass ON produit.p_bc_id = bigclass.bc_id INNER JOIN purchase ON produit.p_id = purchase.p_p_id INNER JOIN intercourse ON purchase.p_i_id = intercourse.i_id INNER JOIN bank ON purchase.p_b_id = bank.b_id INNER JOIN smallclass ON produit.p_sc_id = smallclass.sc_id AND bigclass.bc_id = smallclass.sc_bc_id INNER JOIN unit ON purchase.p_u_id = unit.u_id INNER JOIN warehouse ON purchase.p_w_id = warehouse.w_id " +
				"WHERE purchase.p_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, p_id);
			rs = pstmt.executeQuery();
			if(rs.next()){
				bean = new PurAllShowBean();
				bean.setP_id(rs.getInt("p_id"));
				bean.setP_no(rs.getString("p_no"));
				bean.setP_name(rs.getString("p_name"));
				bean.setBc_name(rs.getString("bc_name"));
				bean.setSc_name(rs.getString("sc_name"));
				bean.setI_name(rs.getString("i_name"));
				bean.setP_prodate(rs.getString("p_prodate"));
				bean.setP_effdate(rs.getInt("p_effdate"));
				bean.setP_quantity(rs.getInt("p_quantity"));
				bean.setP_actual(rs.getDouble("p_actual"));
				bean.setP_price(rs.getDouble("p_price"));
				bean.setP_amount(rs.getDouble("p_amount"));
				bean.setP_time(rs.getString("p_time"));
				bean.setP_note(rs.getString("p_note"));
				bean.setW_name(rs.getString("w_name"));
				bean.setB_name(rs.getString("b_name"));
				bean.setP_l_id(rs.getInt("p_l_id"));
				bean.setU_name(rs.getString("u_name"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return bean;
	}
	/**
	 * 修改入库单据为通过状态
	 * @param p_id
	 */
	public void updatePurP_type(int p_id){
		String sql = "update purchase set p_type = 1 where p_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, p_id);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 修改入库单据为拒绝状态
	 * @param p_id
	 */
	public void updatePurP_typeFalse(int p_id){
		String sql = "update purchase set p_type = 2 where p_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, p_id);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 入库单据拒绝后删除
	 * @param p_id
	 */
	public void delPurFalse(int p_id){
		String sql = "delete purchase where p_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, p_id);
			pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 通过往来单位ID返回该公司所有未结算完成的入库单据
	 * @param p_i_id
	 * @return
	 */
	public ArrayList showPurP_no(int p_i_id){
		ArrayList arry = new ArrayList();
		PurchaseBean bean = null;
		String sql = "select p_id, p_no from purchase where p_i_id = ? and p_mongeytype = 0 and p_type = 1";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, p_i_id);
			rs = pstmt.executeQuery();
			while(rs.next()){
				bean = new PurchaseBean();
				bean.setP_id(rs.getInt("p_id"));
				bean.setP_no(rs.getString("p_no"));
				arry.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 通过药品ID返回药品计量单位
	 * @param p_p_id
	 * @return
	 */
	public String getU_name(int p_p_id){
		String tmp = "";
		String sql = "SELECT unit.u_name FROM purchase INNER JOIN unit ON purchase.p_u_id = unit.u_id WHERE purchase.p_p_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, p_p_id);
			rs = pstmt.executeQuery();
			if(rs.next()){
				tmp = rs.getString("u_name");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return tmp;
	}
	/**
	 * 通过入库单ID返回入库单编号
	 * @param p_id
	 * @return
	 */
	public String getP_no(int p_id){
		String tmp = "";
		String sql = "select p_no from purchase where p_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, p_id);
			rs = pstmt.executeQuery();
			if(rs.next()){
				tmp = rs.getString("p_no");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return tmp;
	}
	/**
	 * 付款之后按单结算，修改入库单信息
	 * @param p_mongeytype
	 * @param p_actual
	 * @param p_id
	 */
	public void updatePurP_mongeytype(boolean p_mongeytype, double p_actual, int p_id){
		String sql = "update purchase set p_mongeytype = ?, p_actual = p_actual + ? where p_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setBoolean(1, p_mongeytype);
			pstmt.setDouble(2, p_actual);
			pstmt.setInt(3, p_id);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
}