/*
 * List.java
 *
 * Created on 2007年3月22日, 上午9:03
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package News.GUI.ProcessData;

import News.Common.Link;
import News.Common.Notice;
import News.Struts.ActionForm.SiteSettingActionForm;
import java.util.*;
import java.sql.*;

import News.Common.DBManager;
import News.GUI.Data.Catalog;
import News.Common.News;

/**
 *
 * @author Owner
 */
public class List {
    private Connection conn=null;
    private PreparedStatement ps=null;
    private ResultSet rs=null;
    private int recordCount=0;
    private int pageCount=0;
    private int pageSize=10;
    private int showPage=0;
    private int nextPage=0;
    private int frontPage=0;
    private String sql="";
    /** Creates a new instance of List */
    public List() {
    }
    
    public ArrayList listNewsOfCatalog(String showpage,int ID)
    {
        ArrayList al=new ArrayList();
        sql="select count(id) from News where parentId=?";
        try{
            showPage=Integer.parseInt(showpage);
        }catch(NumberFormatException e){showPage=1;}
        try{
        conn=DBManager.getConnection();
        ps=conn.prepareStatement(sql);
        ps.setInt(1,ID);
        rs=ps.executeQuery();
        if(rs.next())
        {
            recordCount=rs.getInt(1);
            pageCount=((recordCount%pageSize==0)?(recordCount/pageSize):(recordCount/pageSize+1));
            if(showPage<1)
            {
                showPage=1;
            }else if(showPage>pageCount)
            {
                showPage=pageCount;
            }
            if(showPage>1)
                sql="select Top "+pageSize+" * from News where parentId=? and ID not in (select Top "+pageSize*(showPage-1)+" ID from News where parentId=?)";
            else sql="select Top "+pageSize+" * from News where parentId=?";
            if(showPage>1)
            {
            ps=conn.prepareStatement(sql);
            ps.setInt(1,ID);ps.setInt(2,ID);
            }
            else
            {
            ps=conn.prepareStatement(sql);
            ps.setInt(1,ID);
            }
            
            rs=ps.executeQuery();
            while(rs.next())
            {
            News news=new News();
            news.setId(rs.getString("ID"));
            news.setTitle(rs.getString("Title"));
            news.setAuthor(rs.getString("Author"));
            news.setModifyTime(rs.getString("ModifyTime"));
            al.add(news);
    
            }
          }
        else {return null;}
         }catch(SQLException e){System.out.println(e.getMessage());return null;}
         finally{
             try{
            if(rs!=null)rs.close();
            if(ps!=null)ps.close();
            if(conn!=null)conn.close();
             }catch(SQLException e1){System.out.println(e1.getMessage());}
         }
         return al;
    }
    
    public ArrayList getAllLinks()   
    {
        ArrayList al=new ArrayList();
        conn=DBManager.getConnection();
        try{
        ps=conn.prepareStatement("select * from Links");
        rs=ps.executeQuery();
        while(rs.next())
        {
            Link n=new Link();
            n.setId(rs.getString("ID"));
            n.setName(rs.getString("Name"));
            n.setLogo(rs.getString("Logo"));
            n.setUrl(rs.getString("URL"));
            al.add(n);
        }
        }catch(SQLException e){System.out.println(e.getMessage());return null;}
        finally{
            try{
        if(rs!=null){rs.close();}
        if(ps!=null){ps.close();}
        if(conn!=null){conn.close();}
            }catch(SQLException e1){System.out.println(e1.getMessage());return null;}
        }
        return al;
    }
    
    public SiteSettingActionForm SiteSettingRecord()
    {
        SiteSettingActionForm ssam=new SiteSettingActionForm();
        try{
        conn=DBManager.getConnection();
        ps=conn.prepareStatement("select top 1 * from SiteSetting");
        rs=ps.executeQuery();
        if(rs.next())
              {
                    ssam.setUnencodeSiteName(rs.getString("SiteName"));
                    ssam.setUnencodeSiteURL(rs.getString("SiteURL"));
                    ssam.setUnencodeCompanyName(rs.getString("CompanyName"));
                    ssam.setUnencodeCompanyURL(rs.getString("CompanyURL"));
                    ssam.setUnencodeMetaDescription(rs.getString("MetaDescription"));
                    ssam.setUnencodeMetaKeywords(rs.getString("MetaKeywords"));
                    ssam.setUnencodeBottomAD(rs.getString("BottomAD"));
                    ssam.setUnencodeTopBanner(rs.getString("TopBanner"));
                    ssam.setIsCheckCode(rs.getString("IsCheckCode"));
              }else {return null;}
        }catch(SQLException e){System.out.println(e.getMessage());return null;}
        finally{
            try{
        if(rs!=null){rs.close();}        
        if(ps!=null){ps.close();}
        if(conn!=null){conn.close();}
            }catch(SQLException e1){System.out.println(e1.getMessage());}}
        return ssam;
    }
    
    public ArrayList getAllNotices()   
    {
        ArrayList al=new ArrayList();
        conn=DBManager.getConnection();
        try{
        ps=conn.prepareStatement("select * from Notice");
        rs=ps.executeQuery();
        while(rs.next())
        {
            Notice n=new Notice();
            n.setId(rs.getString("ID"));
            n.setMessage(rs.getString("Message"));
            al.add(n);
        }
        }catch(SQLException e){System.out.println(e.getMessage());return null;}
        finally{
            try{
        if(rs!=null){rs.close();}
        if(ps!=null){ps.close();}
        if(conn!=null){conn.close();}
            }catch(SQLException e1){System.out.println(e1.getMessage());return null;}
        }
        return al;
    }
    
    public ArrayList getAllNewsCatalogs()    //得到所有的新聞版塊名和ID
    {
        ArrayList al=new ArrayList();
        conn=DBManager.getConnection();
        try{
        ps=conn.prepareStatement("select ID,Title from Catalogs");
        rs=ps.executeQuery();
        while(rs.next())
        {
            Catalog catalog=new Catalog();
            catalog.setTitle(rs.getString("Title"));
            catalog.setId(rs.getInt("ID"));
            al.add(catalog);
        }
        }catch(SQLException e){System.out.println(e.getMessage());return null;}
        finally{
            try{
        if(rs!=null){rs.close();}
        if(ps!=null){ps.close();}
        if(conn!=null){conn.close();}
            }catch(SQLException e1){System.out.println(e1.getMessage());return null;}
        }
        return al;
    }
    
    public ArrayList getAllNews(int parentId,int i,String catalogName)    //得到所有的新聞
    {
        ArrayList al=new ArrayList();
        conn=DBManager.getConnection();
        try{
        ps=conn.prepareStatement("select Top "+i+" ID,Author,Title,ModifyTime from News where parentId=?");
        ps.setInt(1,parentId);
        rs=ps.executeQuery();
        while(rs.next())
        {
            News news=new News();
            news.setId(rs.getString("ID"));
            news.setParentId(parentId);
            news.setCatalogName(catalogName);
            news.setAuthor(rs.getString("Author"));
            news.setTitle(rs.getString("Title"));
            news.setModifyTime(rs.getString("ModifyTime"));
            al.add(news);
        }
        }catch(SQLException e){System.out.println(e.getMessage());return null;}
        finally{
            try{
        if(rs!=null){rs.close();}
        if(ps!=null){ps.close();}
        if(conn!=null){conn.close();}
            }catch(SQLException e1){System.out.println(e1.getMessage());return null;}
        }
        return al;
    }
    
     public int getPageSize()
    {
        return pageSize;
    }
    
    public void setPageSize(int i)
    {
        pageSize=i;
    }
    
    public int getShowPage()
    {
        return showPage;
    }
    
    public void setShowPage(int i)
    {
        showPage=i;
    }
    
    public int getNextPage()
    {
        return (showPage+1);
    }
    
    public void setNextPage(int i)
    {
        nextPage=i;
    }
    
    public int getFrontPage()
    {
        return (showPage-1);
    }
    
    public void setFrontPage(int i)
    {
        frontPage=i;
    }
    
    public int getPageCount()
    {
        return pageCount;
    }
    
    public void setPageCount(int i)
    {
        pageCount=i;
    }
    
    public int getRecordCount()
    {
        return recordCount;
    }
    
    public void setRecordCount(int i)
    {
        recordCount=i;
    }
    
    public static void main(String args[])
    {
        List l=new List();
        ArrayList al=l.listNewsOfCatalog("2",1);
        System.out.println(al);
    }
    
}