materialtree.java

JSP移动商品管理平台源代码.........

package imis_mate.common;

import database.DBPoolManager;

import java.util.ArrayList;
import java.util.Hashtable;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

//库存树
import java.sql.SQLException;

public class MaterialTree {
	public MaterialTree() {

	}

	public ArrayList getMaterialNo() {
		ArrayList list = new ArrayList();
		DBPoolManager db = new DBPoolManager();
		db.getConnection();

		try {
			String s = null;
			String materialNo = null;
			int buyId = -1;
			String sql = "SELECT materialNo,buyid FROM (SELECT * FROM Material_Table order by InDate DESC) where rownum<=50 order by materialNo "; // 查询库存编号的SQL语句
			Statement stmt = db.conn.createStatement();
			Statement stmt2 = db.conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);

			Hashtable ht = new Hashtable();
			ht.put("OrganID", "1");
			ht.put("SupOrganID", "0");
			ht.put("OrganName", "库存编号");
			ht.put("MaterialNo", "库存编号");
			list.add(ht);
			int i = 11;
			int j = 0;

			while (rs.next()) {
				Hashtable hash = new Hashtable();
				materialNo = rs.getString("MaterialNo");
				s = rs.getString("MaterialNo");
				buyId = rs.getInt("buyid");
				if(buyId!=0) {
					String sql1 = "select Buyid ,buyNo from material_buy_detail where Buyid = "+buyId; // 查询库存编号的SQL语句
					ResultSet rs1 = stmt2.executeQuery(sql1);
					String buyNo = null;
					while(rs1.next())
					{
						buyNo = rs1.getString("buyNo");
						s = s + "("+buyNo+")";
					}
				}
				s = s.trim();
				materialNo = materialNo.trim();
				
				hash.put("OrganID", String.valueOf(i));
				hash.put("buyId", buyId);
				hash.put("SupOrganID", "1");
				hash.put("OrganName", s);
				hash.put("MaterialNo", materialNo);

				list.add(hash);
				i++;
				j++;
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {
			System.err.print("Get MaterialTree SQLException: " + ex.toString());
			ex.printStackTrace();
		} finally {
			db.freeConnection();
		}

		return list;
	}

	public ArrayList getMaterialNoAll() {
		ArrayList list = new ArrayList();
		DBPoolManager db = new DBPoolManager();
		db.getConnection();

		try {
			String s = null;
			String sql = "SELECT MaterialNo FROM Material_Table order by InDate DESC"; // 查询库存编号的SQL语句
			Statement stmt = db.conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);

			Hashtable ht = new Hashtable();
			ht.put("OrganID", "1");
			ht.put("SupOrganID", "0");
			ht.put("OrganName", "库存编号");
			list.add(ht);
			int i = 11;
			int j = 0;

			while (rs.next()) {
				Hashtable hash = new Hashtable();
				s = rs.getString("MaterialNo");
				s = s.trim();

				hash.put("OrganID", String.valueOf(i));
				hash.put("SupOrganID", "1");
				hash.put("OrganName", s);

				list.add(hash);
				i++;
				j++;
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {
			System.err.print("Get MaterialTree SQLException: " + ex.toString());
			ex.printStackTrace();
		} finally {
			db.freeConnection();
		}
		return list;
	}
}