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;	
		    }
}