package lgcsgwxt.means;

import java.util.Vector;
import java.sql.*;
import lgcsgwxt.*;

/**
 * <p>Title: 魯廣超市進銷存系統</p>
 *
 * <p>Description: 北大青鳥魯廣校區S1畢業設計</p>
 *
 * <p>Copyright: Copyright (c) 2007</p>
 *
 * <p>Company: ST-117班</p>
 *
 * @author ST-117班第二小組
 * @version 1.0
 */
public class Select_Means {

    private static final String select1 =
            "select * from Merchandise where SortNumber=?";
    private static final String select2 =
            "select * from Merchandise where MerchandiseNumber=?";
    private static final String select3 =
            "select * from Merchandise where MerchandiseName=?";
    private static final String select4 =
            "select * from Merchandise where TreatyCode=?";
    private static final String select5 =
            "select * from UserManage where CardId=? ";
    private static final String select6 =
            "select * from UserManage where UserName=? and Password=? and UserGrade=?";
    private static final String select7 =
            "select * from BackSale where NextBack=?";
    private static final String select8 =
            "select * from BackSale where Product_ID=? and NextBack=?";
    private static final String select9 =
            "select number from StockPile where MerchandiseNumber=?";
    private static final String select10 =
            "select * from StockPile where MerchandiseNumber=?";
    private static final String select11 =
            "select * from StockPile";
    public Select_Means() {
    }

    public static Vector tablehead() {
        Vector dqname = new Vector();
        dqname.add("商品編碼");
        dqname.add("條型碼");
        dqname.add("類別編號");
        dqname.add("商品名稱");
        dqname.add("零售價(元)");
        dqname.add("商品規格");
        dqname.add("計量單位");
        dqname.add("進貨價");
        dqname.add("備注");
        return dqname;
    }

    public static Vector Select_sort1(String number) { //商品類別查詢
        Connection conn = null;
        PreparedStatement pmst = null;
        ResultSet rs = null;
        Vector vector2 = new Vector();
        conn = DBAccess.getConnection();

        try {
            pmst = conn.prepareStatement(select1);
            pmst.setString(1, number);
            rs = pmst.executeQuery();
            while (rs.next()) {
                Vector vector1 = new Vector();
                vector1.add(rs.getString(2));
                vector1.add(rs.getString(3));
                vector1.add(rs.getString(4));
                vector1.add(rs.getString(5));
                vector1.add(rs.getString(6));
                vector1.add(rs.getString(7));
                vector1.add(rs.getString(8));
                vector1.add(rs.getString(9));
                vector1.add(rs.getString(11));
                vector2.add(vector1);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pmst != null) {
                    pmst.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception ex1) {
                ex1.printStackTrace();
            }

        }
        return vector2;
    }

    public static Vector Select_sort2(String number) { //按商品編號查詢
        Connection conn = null;
        PreparedStatement pmst = null;
        ResultSet rs = null;
        Vector vector2 = new Vector();
        conn = DBAccess.getConnection();

        try {
            pmst = conn.prepareStatement(select2);
            pmst.setString(1, number);
            rs = pmst.executeQuery();
            while (rs.next()) {
                Vector vector1 = new Vector();
                vector1.add(rs.getString(2));
                vector1.add(rs.getString(3));
                vector1.add(rs.getString(4));
                vector1.add(rs.getString(5));
                vector1.add(rs.getString(6));
                vector1.add(rs.getString(7));
                vector1.add(rs.getString(8));
                vector1.add(rs.getString(9));
                vector1.add(rs.getString(11));
                vector2.add(vector1);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pmst != null) {
                    pmst.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception ex1) {
                ex1.printStackTrace();
            }

        }
        return vector2;
    }

    public static Vector Select_sort3(String number) { //按商品名稱查詢
        Connection conn = null;
        PreparedStatement pmst = null;
        ResultSet rs = null;
        Vector vector2 = new Vector();
        conn = DBAccess.getConnection();

        try {
            pmst = conn.prepareStatement(select3);
            pmst.setString(1, number);
            rs = pmst.executeQuery();
            while (rs.next()) {
                Vector vector1 = new Vector();
                vector1.add(rs.getString(2));
                vector1.add(rs.getString(3));
                vector1.add(rs.getString(4));
                vector1.add(rs.getString(5));
                vector1.add(rs.getString(6));
                vector1.add(rs.getString(7));
                vector1.add(rs.getString(8));
                vector1.add(rs.getString(9));
                vector1.add(rs.getString(11));
                vector2.add(vector1);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pmst != null) {
                    pmst.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception ex1) {
                ex1.printStackTrace();
            }

        }
        return vector2;
    }

    public static Vector Select_sort4(String number) { //按商品條形碼查詢
        Connection conn = null;
        PreparedStatement pmst = null;
        ResultSet rs = null;
        Vector vector2 = new Vector();
        conn = DBAccess.getConnection();

        try {
            pmst = conn.prepareStatement(select4);
            pmst.setString(1, number);
            rs = pmst.executeQuery();
            while (rs.next()) {
                Vector vector1 = new Vector();
                vector1.add(rs.getString(2));
                vector1.add(rs.getString(3));
                vector1.add(rs.getString(4));
                vector1.add(rs.getString(5));
                vector1.add(rs.getString(6));
                vector1.add(rs.getString(7));
                vector1.add(rs.getString(8));
                vector1.add(rs.getString(9));
                vector1.add(rs.getString(11));
                vector2.add(vector1);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pmst != null) {
                    pmst.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception ex1) {
                ex1.printStackTrace();
            }

        }
        return vector2;
    }

    public static Vector Select_User(String name, String password) {
        Vector vcUser = new Vector();
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pmst = null;