package com.vsked.services;

import java.sql.Connection;

import java.sql.PreparedStatement;
import java.sql.ResultSet;

import java.util.ArrayList;

import com.vsked.javaBean.*;
import com.vsked.models.ConnectDB;

//新聞方法集合
public class Newsm {
	private Connection conn=null;
	private PreparedStatement pt=null;
	private ResultSet rs=null;
	//表示查詢結果集合的對象
	private ArrayList results=null;
	//存儲具體單條結果的對象，javaBean
	private Newst nes=null;
	private boolean flag=false;
	
	
	//添加新聞
	public boolean addnews(Newst nes){
		conn=ConnectDB.getConnectionJDBC();
	    try {
			pt=conn.prepareStatement("insert into newst(newsname,authors,newsbody,typeid) values(?,?,?,?)");
			pt.setString(1,nes.getNewstitle());
			pt.setString(2, nes.getAuthors());
			pt.setString(3, nes.getNewsbody());
			pt.setInt(4, nes.getTypeid());
			pt.execute();
			flag=true;			
		} catch (Exception e) {
		
		}finally{
			ConnectDB.close();
		}
		
		return flag;
	}
	
	//修改新聞
	public boolean modifynews(Newst nes){
		int row=0;
		conn=ConnectDB.getConnectionJDBC();
	    try {
			pt=conn.prepareStatement("update newst set newsname=?,authors=?,newsbody=?,typeid=? where newsid=?");
			pt.setString(1,nes.getNewstitle());
			pt.setString(2, nes.getAuthors());
			pt.setString(3, nes.getNewsbody());
			pt.setInt(4, nes.getTypeid());
			pt.setInt(5,nes.getNewsid());
			row=pt.executeUpdate();
			if(row>0){
				flag=true;		
			}				
		} catch (Exception e) {
		
		}finally{
			ConnectDB.close();
		}
		
		return flag;
	}
	
	//查詢所以一種類別新聞的前五條
	public ArrayList getTopNewsNameInfoByTypeId(int typeid){
		results=new ArrayList();
		conn=ConnectDB.getConnectionJDBC();
	    try {
			pt=conn.prepareStatement("select top 5 * from newst where typeid=? order by newsdate asc");
			pt.setInt(1, typeid);
			rs=pt.executeQuery();
			while(rs.next()){
				nes=new Newst(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getDate(5),rs.getInt(6));
				results.add(nes);
			}
			flag=true;			
		} catch (Exception e) {
		
		}finally{
			ConnectDB.close();
		}		
		
		return results;
	}
	
	//查詢所以一種類別的所有新聞
	public ArrayList getNewsInfoByTypeId(int typeid){
		results=new ArrayList();
		conn=ConnectDB.getConnectionJDBC();
	    try {
			pt=conn.prepareStatement("select * from newst where typeid=?");
			pt.setInt(1, typeid);
			rs=pt.executeQuery();
			while(rs.next()){
				nes=new Newst(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getDate(5),rs.getInt(6));
				results.add(nes);
			}
			flag=true;			
		} catch (Exception e) {
		
		}finally{
			ConnectDB.close();
		}		
		
		return results;
	}
	//查詢所所有新聞
	public ArrayList getAllNewsInfo(){
		results=new ArrayList();
		conn=ConnectDB.getConnectionJDBC();
	    try {
			pt=conn.prepareStatement("select newsid,newsname,authors,newsbody,newsdate,typename from newst,typest where newst.typeid=typest.typeid");			
			rs=pt.executeQuery();
			while(rs.next()){
//				System.out.println(rs.getInt(1)+rs.getString(2)+rs.getString(3)+rs.getString(4)+rs.getDate(5)+rs.getInt(6));
				nes=new Newst(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getDate(5),rs.getString(6));
				results.add(nes);
			}
			flag=true;			
		} catch (Exception e) {
		
		}finally{
			ConnectDB.close();
		}		
		return results;
	}
    
	//根據編號查詢新聞
	public Newst getNewsInfoByid(int id){		
		conn=ConnectDB.getConnectionJDBC();
	    try {
			pt=conn.prepareStatement("select newsid,newsname,authors,newsbody,newsdate,typename from newst,typest where newst.typeid=typest.typeid and newsid=?");			
			pt.setInt(1, id);
			rs=pt.executeQuery();
			if(rs.next()){
//				System.out.println(rs.getInt(1)+rs.getString(2)+rs.getString(3)+rs.getString(4)+rs.getDate(5)+rs.getInt(6));
				nes=new Newst(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getDate(5),rs.getString(6));
				results.add(nes);
			}
			flag=true;			
		} catch (Exception e) {
		
		}finally{
			ConnectDB.close();
		}		
		return nes;
	}
	//得到類型總數
	public int getTypeCount(){
		int cou=0;
		conn=ConnectDB.getConnectionJDBC();
	    try {
			pt=conn.prepareStatement("select count(*) from typest");			
			rs=pt.executeQuery();
			if(rs.next()){
				cou=rs.getInt(1);
			}				
		} catch (Exception e) {
		
		}finally{
			ConnectDB.close();
		}		
		
		return cou;
		
	}
	
	
	//根據編號刪除新聞
	public boolean deleteNewsById(int typeid){	
		int row=0;
		conn=ConnectDB.getConnectionJDBC();
	    try {
			pt=conn.prepareStatement("delete newst where newsid=?");
			pt.setInt(1, typeid);
			row=pt.executeUpdate();
			if(row>0){
				flag=true;	
			}					
		} catch (Exception e) {
		
		}finally{
			ConnectDB.close();
		}		
		
		return flag;
	}
}