sellsda.java

医药供应链管理系统

		SellsBean bean = null;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, l_id);
			rs = pstmt.executeQuery();
			while(rs.next()){
				bean = new SellsBean();
				bean.setS_id(rs.getInt("s_id"));
				bean.setS_i_id(rs.getInt("s_i_id"));
				bean.setS_no(rs.getString("s_no"));
				bean.setI_name(rs.getString("i_name"));
				bean.setB_name(rs.getString("b_name"));
				bean.setP_name(rs.getString("p_name"));
				bean.setS_l_id(rs.getInt("s_l_id"));
				bean.setU_name(rs.getString("u_name"));
				bean.setS_raidtime(rs.getString("s_raidtime"));
				bean.setS_prodate(rs.getString("s_prodate"));
				bean.setS_durdate(rs.getString("s_durdate"));
				bean.setS_r_no(rs.getString("s_r_no"));
				bean.setW_name(rs.getString("w_name"));
				bean.setS_purprice(rs.getDouble("s_purprice"));
				bean.setS_i_name(rs.getString("s_i_name"));
				bean.setS_quantity(rs.getInt("s_quantity"));
				bean.setS_price(rs.getDouble("s_price"));
				bean.setS_amount(rs.getDouble("s_amount"));
				bean.setS_actual(rs.getDouble("s_actual"));
				bean.setS_note(rs.getString("s_note"));
				bean.setS_ver(rs.getInt("s_ver"));
				bean.setS_time(rs.getString("s_time"));
				arry.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 通过出库单id返回出库单信息
	 * @param s_id
	 * @return
	 */
	public SellsBean showAllByS_id(int s_id){
		String sql = "SELECT sells.*, intercourse.i_name, warehouse.w_name, produit.p_name, bank.b_name, unit.u_name FROM sells INNER JOIN intercourse ON sells.s_i_id = intercourse.i_id INNER JOIN warehouse ON sells.s_w_id = warehouse.w_id INNER JOIN produit ON sells.s_p_id = produit.p_id INNER JOIN unit ON sells.s_u_id = unit.u_id INNER JOIN bank ON sells.s_b_id = bank.b_id where s_id = ?";
		SellsBean bean = null;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, s_id);
			rs = pstmt.executeQuery();
			while(rs.next()){
				bean = new SellsBean();
				bean.setS_id(rs.getInt("s_id"));
				bean.setS_i_id(rs.getInt("s_i_id"));
				bean.setS_no(rs.getString("s_no"));
				bean.setI_name(rs.getString("i_name"));
				bean.setB_name(rs.getString("b_name"));
				bean.setP_name(rs.getString("p_name"));
				bean.setS_l_id(rs.getInt("s_l_id"));
				bean.setU_name(rs.getString("u_name"));
				bean.setS_b_id(rs.getInt("s_b_id"));
				bean.setS_raidtime(rs.getString("s_raidtime"));
				bean.setS_prodate(rs.getString("s_prodate"));
				bean.setS_durdate(rs.getString("s_durdate"));
				bean.setS_r_no(rs.getString("s_r_no"));
				bean.setW_name(rs.getString("w_name"));
				bean.setS_purprice(rs.getDouble("s_purprice"));
				bean.setS_i_name(rs.getString("s_i_name"));
				bean.setS_quantity(rs.getInt("s_quantity"));
				bean.setS_price(rs.getDouble("s_price"));
				bean.setS_amount(rs.getDouble("s_amount"));
				bean.setS_actual(rs.getDouble("s_actual"));
				bean.setS_note(rs.getString("s_note"));
				bean.setS_ver(rs.getInt("s_ver"));
				bean.setS_time(rs.getString("s_time"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return bean;
	}
	/**
	 * 修改出库单据通过
	 * @param s_id
	 */
	public void updateSellsS_type(int s_id){
		String sql = "update sells set s_type = 1 where s_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, s_id);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 修改出库单据拒绝
	 * @param s_id
	 */
	public void updateSellsS_typeFalse(int s_id){
		String sql = "update sells set s_type = 2 where s_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, s_id);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 删除拒绝的入库单据
	 * @param s_id
	 */
	public void delSellsFalse(int s_id){
		String sql = "delete sells where s_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, s_id);
			pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 出库单通过修改真正出库日期
	 * @param s_time
	 * @param s_id
	 */
	public void updateSellsS_time(String s_time, int s_id){
		String sql = "update sells set s_time = ? where s_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, s_time);
			pstmt.setInt(2, s_id);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 修改出库单为冲红状态
	 * @param s_id
	 */
	public void updateSellsChongHong(int s_id){
		String sql = "update sells set s_type = 4 where s_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, s_id);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 返回所有冲红单据
	 * @return
	 */
	public ArrayList showHong(){
		String sql = "SELECT sells.*, intercourse.i_name, warehouse.w_name, produit.p_name, bank.b_name, unit.u_name FROM sells INNER JOIN intercourse ON sells.s_i_id = intercourse.i_id INNER JOIN warehouse ON sells.s_w_id = warehouse.w_id INNER JOIN produit ON sells.s_p_id = produit.p_id INNER JOIN unit ON sells.s_u_id = unit.u_id INNER JOIN bank ON sells.s_b_id = bank.b_id where s_type = 4 order by s_time desc";
		ArrayList arry = new ArrayList();
		SellsBean bean = null;
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				bean = new SellsBean();
				bean.setS_id(rs.getInt("s_id"));
				bean.setS_i_id(rs.getInt("s_i_id"));
				bean.setS_no(rs.getString("s_no"));
				bean.setI_name(rs.getString("i_name"));
				bean.setB_name(rs.getString("b_name"));
				bean.setP_name(rs.getString("p_name"));
				bean.setS_l_id(rs.getInt("s_l_id"));
				bean.setU_name(rs.getString("u_name"));
				bean.setS_raidtime(rs.getString("s_raidtime"));
				bean.setS_prodate(rs.getString("s_prodate"));
				bean.setS_durdate(rs.getString("s_durdate"));
				bean.setS_r_no(rs.getString("s_r_no"));
				bean.setW_name(rs.getString("w_name"));
				bean.setS_purprice(rs.getDouble("s_purprice"));
				bean.setS_i_name(rs.getString("s_i_name"));
				bean.setS_quantity(rs.getInt("s_quantity"));
				bean.setS_price(rs.getDouble("s_price"));
				bean.setS_amount(rs.getDouble("s_amount"));
				bean.setS_actual(rs.getDouble("s_actual"));
				bean.setS_note(rs.getString("s_note"));
				bean.setS_ver(rs.getInt("s_ver"));
				bean.setS_time(rs.getString("s_time"));
				arry.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 删除冲红的出库单据
	 * @param s_id
	 */
	public void delSellsChongHong(int s_id){
		String sql = "delete sells where s_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, s_id);
			pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 清空冲红的单据
	 */
	public void delAllChongHong(){
		String sql = "delete sells where s_type = 4";
		try {
			stmt = conn.createStatement();
			stmt.execute(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	
	/**
	 * 通过往来单位ID返回该公司所有未结算完成的出库单据
	 * @param s_i_id
	 * @return
	 */
	public ArrayList showSellsS_no(int s_i_id){
		ArrayList arry = new ArrayList();
		SellsBean bean = null;
		String sql = "select s_id, s_no from sells where s_i_id = ? and s_mongeytype = 0 and s_type = 1";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, s_i_id);
			rs = pstmt.executeQuery();
			while(rs.next()){
				bean = new SellsBean();
				bean.setS_id(rs.getInt("s_id"));
				bean.setS_no(rs.getString("s_no"));
				arry.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 收款之后按单结算，修改出库单信息
	 * @param p_mongeytype
	 * @param p_actual
	 * @param p_id
	 */
	public void updateSellsS_mongeytype(boolean s_mongeytype, double s_actual, int s_id){
		String sql = "update sells set s_mongeytype = ?, s_actual = s_actual + ? where s_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setBoolean(1, s_mongeytype);
			pstmt.setDouble(2, s_actual);
			pstmt.setInt(3, s_id);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
}