package www.mary.access;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import www.mary.Bean.ItemBean;

import www.mary.database.BaseDAO;
import www.mary.Bean.*;
//一些通用的數據展現方法
public class PowerAccess {

	public PowerAccess() {
		super();

	}

//------------------------------------------------------------------------------------
	//展現產品大類所有的大類名稱，先給Categorybean賦值，然后存放倒List里面
	public static List getCategoryName(){
		List list=new ArrayList();
		String sql="select * from category";
		System.out.println("------"+sql);
		BaseDAO dao=new BaseDAO();
		ResultSet rs=dao.executeQuery(sql);
		
		try {
			while(rs.next()){
				CategoryBean catename=new CategoryBean(rs.getString(1),rs.getString(2),rs.getString(3));
				list.add(catename);
     		}
		}catch (SQLException e) {
		        e.printStackTrace();
	  	}finally{
	  		dao.Close();
	  	}
	  	return list;
		
		
	  	
	}
	

//------------------------------------------------------------------------------------
	//展現產品大類catid所對應的產品名稱，傳遞一個catid值
	public static List getProductList(String catid){
		List list=new ArrayList();
		String sql="select * from product where catid="+catid;
		System.out.println("------"+sql);
		BaseDAO dao=new BaseDAO();
		ResultSet rs=dao.executeQuery(sql);
		
		try {
			while(rs.next()){
				ProductBean product=new ProductBean(rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4));
				list.add(product);
     		}
		}catch (SQLException e) {
		        e.printStackTrace();
	  	}finally{
	  		dao.Close();
	  	}
	  	return list;
		
		
	  	
	}


//------------------------------------------------------------------------------------
		//展現productid對應的產品項目，傳遞一個productid值
    public static List getItemlist(String productid){
		List list=new ArrayList();
		String sql="select itemid,productid,listprice,attr1 from item where productid="+productid;
		System.out.println("------"+sql);
		BaseDAO dao=new BaseDAO();
		ResultSet rs=dao.executeQuery(sql);
		
		try {
			while(rs.next()){
				ItemBean item=new ItemBean(rs.getString(1),rs.getString(2),rs.getDouble(3),rs.getString(4));  
				list.add(item);
     		}
		}catch (SQLException e) {
		        e.printStackTrace();
	  	}finally{
	  		dao.Close();
	  	}
	  	return list;

		
	}
	

//------------------------------------------------------------------------------------
			//展現itemid對應的產品項目的價格，狀態，庫存，傳遞一個itemid值
		public static List getlineitemlist(String itemid){
		    List list=new ArrayList();
			String sql="select A.descn,B.listprice,B.status,C.qty from product A,item B,inventory C where A.productid=B.productid and B.itemid=C.itemid and B.itemid='"+itemid+"'";
			System.out.println("------"+sql);
			BaseDAO dao=new BaseDAO();
			ResultSet rs=dao.executeQuery(sql);
			

			try {
				while(rs.next()){
					ItemInventoryBean one=new ItemInventoryBean(rs.getString(1),rs.getInt(2),rs.getString(3),rs.getInt(4));
	     		    list.add(one);
				} 
			}catch (SQLException e) {
			        e.printStackTrace();
		  	}finally{
		  		dao.Close();
		  	}
		  	return list;

			
		}
		


//------------------------------------------------------------------------------------
			
		//展現購物車中對應的產品項目編號，產品編號，描述，是否有庫存，數量，價格，傳遞一個itemad值
		public static List getcartlist(String itemid){
		    List list=new ArrayList();
			String sql="select B.itemid,A.productid,B.attr1,B.status,C.qty,B.listprice from product A,item B,inventory C where A.productid=B.productid and B.itemid=C.itemid and B.itemid='"+itemid+"'";
			System.out.println("------"+sql);
			BaseDAO dao=new BaseDAO();
			ResultSet rs=dao.executeQuery(sql);
			

			try {
				while(rs.next()){
					ProitemBean one=new ProitemBean(rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getInt(5));
	     		    list.add(one);
				} 
			}catch (SQLException e) {
			        e.printStackTrace();
		  	}finally{
		  		dao.Close();
		  	}
		  	return list;

			
		}
		

		public static ProitemBean getcart(String itemid){
			ProitemBean one=null;
			String sql="select B.itemid,A.productid,B.attr1,B.status,B.listprice from product A,item B,inventory C where A.productid=B.productid and B.itemid=C.itemid and B.itemid='"+itemid+"'";
			System.out.println("------"+sql);
			BaseDAO dao=new BaseDAO();
			ResultSet rs=dao.executeQuery(sql);
			

			try {
				while(rs.next()){
			    one=new ProitemBean(rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getInt(5));
	     		   
				} 
			}catch (SQLException e) {
			        e.printStackTrace();
		  	}finally{
		  		dao.Close();
		  	}
		  	return one;

			
		}
		

//------------------------------------------------------------------------------------
			

		
//------------------------------------------------------------------------------------
		   //這是通用的校驗用戶合法性的程序
			public static boolean validateUser(String uname,String pword)
			{
				boolean isCorrect=false;
				String sql="select * from signon where username='"+uname+"' and password='"+pword+"'";
				//建立數據庫連接，進行查詢
				BaseDAO dao=new BaseDAO();
				//得到結果集合
				ResultSet rs=dao.executeQuery(sql);
				//如果查到了結果集合就不為空
				try {
					if(rs.next()){
						
					//在這里標簽賦值乘true
						isCorrect=true;
						
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				dao.Close();
				return isCorrect;
				
				
			}

			
			
			
//------------------------------------------------------------------------------------

		    public static ItemBean getOneItem(String itemid)
		    {
		    	ItemBean one=null;
		          String sql="select * from item where itemid='"+itemid+"'";
		          BaseDAO dao=new BaseDAO();
		          ResultSet rs=dao.executeQuery(sql);
		          try{
		   		   if(rs.next())
		   		   {
		   			 one=new ItemBean(rs.getString(1),rs.getString(2),rs.getInt(3),rs.getDouble(4),rs.getDouble(5),rs.getString(6),
		                      rs.getString(7),rs.getString(8),rs.getString(9),rs.getString(10),rs.getString(11));   
		   		   
		   		   }
		   	    }catch (SQLException e){
		   		  e.printStackTrace();
		   	    }
		   	    dao.Close();
		   	    return one;	
		    }
}