package zichan;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;

/**
 * <p>Title: </p>
 *
 * <p>Description: </p>
 *
 * <p>Copyright: Copyright (c) 2006</p>
 *
 * <p>Company: </p>
 *
 * @author not attributable
 * @version 1.0
 */
public class TreeModle {
    Connection conn;
    public TreeModle() {
        conn=(new DBConnt()).getConnection();
        try {
            jbInit();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
    public int selCateCnt(){
        int cnt=0;
        if(conn==null){
            System.out.print("無連接1");
            return -1;
        }
        try{
            Statement stmt=conn.createStatement();
            ResultSet rset=stmt.executeQuery("select count(distinct cateid) from category");
            while(rset.next()){
                cnt=rset.getInt(1);
               //System.out.println(cnt);
            }
            rset.close();
            stmt.close();
        }catch(SQLException s){
            s.printStackTrace();
        }
        return cnt;
    }
    public void selSubCateCnt(int [] scnt){
        int k = 0;
        String sql;
        if (conn == null) {
            System.out.print("無連接2");
            return;
        }
        try {
            sql =
                    "select cateid,count(subcateid) from category group by cateid";
            Statement stmt = conn.createStatement();
            ResultSet rset = stmt.executeQuery(sql);
            while (rset.next()) {
                scnt[k++] = rset.getInt(2);
               // System.out.println(scnt[k]);
            }
            rset.close();
            stmt.close();
        } catch (SQLException s) {
            s.printStackTrace();
        }
    }
        public void selCate(String [] data,int [] scnt,int cnt){
            int k= 0;
            String sql;
            if (conn == null) {
                System.out.print("無連接3");
                return;
            }
            try {
                sql="select cateid,catename,subcateid,subcatename from category order by cateid,subcateid";
                Statement stmt = conn.createStatement();
                ResultSet rset = stmt.executeQuery(sql);
                rset.next() ;
                for(int i=0;i<cnt;i++){
                    data[k++]=rset.getString(2);
                    for(int j=0;j<scnt[i];j++){
                        data[k++]=rset.getString(4);
                        //System.out.println(data[k]);
                        rset.next();
                    }
                }


                rset.close();
                stmt.close();
            } catch (SQLException s) {
                s.printStackTrace();
            }
        }

    private void jbInit() throws Exception {
    }


}