salestatisticbean.java

《JSP程序设计实用教程》-梁建武-源代码-4567.rar

package com.saleSystem;
import java.sql.*;
import java.util.*;
import java.io.*;
import com.saleSystem.util.*;
/**
 *SaleBean包含和StatisticTableByArea表相关的操作
 */
public class SaleStatisticBean
{
	private Connection con;
	//构造方法，获得数据库的连接。
	public SaleStatisticBean()
	{
		this.con=DataBaseConnection.getConnection();
	}
	/**
	 *按不同的分类统计所有的销售信息。
	 *返回由StatisticTableByArea值对象组成的Collection
	 */
	public Collection getCityQuery(String Inyear,String Incity)throws Exception
	{   String sql="select sale.productCode as productCode,name,sum(quantity) as sumquantity,sum(price*quantity) as summoney from product,sale where to_char(saleDate,'yyyy')=? and product.productCode=sale.productCode and sale.saleCity=? group by saleCity,sale.productCode,name";//查询 语句
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1,Inyear);
		pstmt.setString(2,Incity);
		ResultSet rst=pstmt.executeQuery();
	    Collection ret=new ArrayList();
        while(rst.next())
		{
			StatisticTableByArea temp=new StatisticTableByArea();
			temp.setProductCode(rst.getString("productCode"));
		    temp.setName(rst.getString("name"));
	        temp.setSumquantity(rst.getInt("sumquantity"));
	        temp.setSummoney(rst.getFloat("summoney"));
            
			
			ret.add(temp);
		}
		con.close();
		return ret;
	}
	
	
	public Collection getSectorQuery(String Inyear,String Insectorname)throws Exception
	{   String sql="select sale.productCode as productCode,product.name as name,sum(quantity) as sumquantity,sum(price*quantity) as summoney from product,sale,sector where to_char(saleDate,'yyyy')=? and product.productCode=sale.productCode and sale.productCode=sector.productCode and sector.name=? group by sector.name,sale.productCode,product.name";//查询 语句
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1,Inyear);
		pstmt.setString(2,Insectorname);
		ResultSet rst=pstmt.executeQuery();
	    Collection ret=new ArrayList();
        while(rst.next())
		{
			StatisticTableByArea temp=new StatisticTableByArea();
			temp.setProductCode(rst.getString("productCode"));
		    temp.setName(rst.getString("name"));
	        temp.setSumquantity(rst.getInt("sumquantity"));
	        temp.setSummoney(rst.getFloat("summoney"));
            
			
			ret.add(temp);
		}
		con.close();
		return ret;
		
		}
	
	public Collection getPersonalQuery(String Inyear,String InpersonalCode)throws Exception
	{   String sql="select sale.productCode as productCode ,name,sum(quantity) as sumquantity,sum(price*quantity) as summoney from product,sale where to_char(saleDate,'yyyy')=? and product.productCode=sale.productCode and personalCode=? group by personalCode,sale.productCode,name";//查询 语句
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1,Inyear);
		pstmt.setString(2,InpersonalCode);
		ResultSet rst=pstmt.executeQuery();
	    Collection ret=new ArrayList();
        while(rst.next())
		{
			StatisticTableByArea temp=new StatisticTableByArea();
			temp.setProductCode(rst.getString("productCode"));
		    temp.setName(rst.getString("name"));
	        temp.setSumquantity(rst.getInt("sumquantity"));
	        temp.setSummoney(rst.getFloat("summoney"));
        
			
			ret.add(temp);
		}
		con.close();
		return ret;
	}
	
	
		public Collection getYearQuery(String Inyear)throws Exception
	    {   String sql="select sale.productCode as productCode,name,sum(quantity) as sumquantity,sum(price*quantity) as summoney from product,sale where to_char(saleDate,'yyyy')=? and product.productCode=sale.productCode  group by to_char(saleDate,'yyyy'),sale.productCode,name";//查询 语句
		    PreparedStatement pstmt=con.prepareStatement(sql);
		    pstmt.setString(1,Inyear);
		    ResultSet rst=pstmt.executeQuery();
	        Collection ret=new ArrayList();
            while(rst.next())
		{
			StatisticTableByArea temp=new StatisticTableByArea();
			temp.setProductCode(rst.getString("productCode"));
		    temp.setName(rst.getString("name"));
	        temp.setSumquantity(rst.getInt("sumquantity"));
	        temp.setSummoney(rst.getFloat("summoney"));
           
			
			ret.add(temp);
		}
		con.close();
		return ret;
	}
	
	
	public Collection getQuarterQuery(String Inyear,String Inquarter)throws Exception
	{   String sql=null;
	    String sql1="select sale.productCode as productCode,name,sum(quantity) as sumquantity,sum(price*quantity) as summoney from product,sale  where product.productCode=sale.productCode  and to_char(saleDate,'mm')>='01' and to_char(saleDate,'mm')<='03' and to_char(saleDate,'yyyy')=? group by to_char(saleDate,'yyyy'),sale.productCode,name ";//查询 语句
		String sql2="select sale.productCode as productCode,name,sum(quantity) as sumquantity,sum(price*quantity) as summoney from product,sale  where product.productCode=sale.productCode  and to_char(saleDate,'mm')>='04' and to_char(saleDate,'mm')<='06' and to_char(saleDate,'yyyy')=? group by to_char(saleDate,'yyyy'),sale.productCode,name";//查询 语句
		String sql3="select sale.productCode as productCode,name,sum(quantity) as sumquantity,sum(price*quantity) as summoney from product,sale  where product.productCode=sale.productCode  and to_char(saleDate,'mm')>='07' and to_char(saleDate,'mm')<='09' and to_char(saleDate,'yyyy')=? group by to_char(saleDate,'yyyy'),sale.productCode,name";//查询 语句
		String sql4="select sale.productCode as productCode,name,sum(quantity) as sumquantity,sum(price*quantity) as summoney from product,sale  where product.productCode=sale.productCode  and to_char(saleDate,'mm')>='10' and to_char(saleDate,'mm')<='12' and to_char(saleDate,'yyyy')=? group by to_char(saleDate,'yyyy'),sale.productCode,name";//查询 语句
		if (Inquarter.equals("1")){sql=sql1;}
		if (Inquarter.equals("2")){sql=sql2;}
		if (Inquarter.equals("3")){sql=sql3;}
		if (Inquarter.equals("4")){sql=sql4;}
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1,Inyear);
		ResultSet rst=pstmt.executeQuery();
	    Collection ret=new ArrayList();
        while(rst.next())
		{
			StatisticTableByArea temp=new StatisticTableByArea();
			temp.setProductCode(rst.getString("productCode"));
		    temp.setName(rst.getString("name"));
	        temp.setSumquantity(rst.getInt("sumquantity"));
	        temp.setSummoney(rst.getFloat("summoney"));
           
			
			ret.add(temp);
		}
		con.close();
		return ret;
	}
	
	
	public Collection getMonthQuery(String Inyear,String Inmonth)throws Exception
	{   String sql="select sale.productCode as productCode,name,sum(quantity) as sumquantity,sum(price*quantity) as summoney from product,sale where to_char(saleDate,'yyyy')=? and to_char(saleDate,'mm')=? and product.productCode=sale.productCode  group by to_char(saleDate,'mm'),sale.productCode,name";//查询 语句
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1,Inyear);
		pstmt.setString(2,Inmonth);
		ResultSet rst=pstmt.executeQuery();
	    Collection ret=new ArrayList();
        while(rst.next())
		{
			StatisticTableByArea temp=new StatisticTableByArea();
			temp.setProductCode(rst.getString("productCode"));
		    temp.setName(rst.getString("name"));
	        temp.setSumquantity(rst.getInt("sumquantity"));
	        temp.setSummoney(rst.getFloat("summoney"));
            
            
			
			ret.add(temp);
		}
		con.close();
		return ret;
	}
}