ulltageproductreport.java

(Java+SQL)-大型企业JAVA的ERP系统

package src.report;

import src.com.*;
import java.io.*;
import java.lang.*;
import java.sql.*;

public class UlltageProductReport extends ShangObject
{
	/**
产品名称
	*/
	private String productName;
	
	/**
规格/型号
	*/
	private String productType;
	
	/**
数量
	*/
	private double quantity;
	
	/**
预计价格
	*/ 
	private double price;
	
	/**
部门名
	*/
	private String departmentName;
	
	   
   /**
开始日期
   */
	public Timestamp beginDate;
   
   /**
结束日期
   */
	public Timestamp endDate;   

	
	public UlltageProductReport()
	{
		numInOnePager=10;
	}
	
	public String getProductName()
	{
		return productName;
	}
	
	public String getProductType()
	{
		return productType;
	}
	
	public double getQuantity()
	{
		return quantity;
	}
	
	public double getPrice()
	{
		return price;
	}
	
	public String getDepartmentName()
	{
		return departmentName;
	}
	
	public Timestamp getBeginDate() 
   {
      return beginDate;
   }

   public Timestamp getEndDate() 
   {
      return endDate;
   }
	
	public int loadAttr(){
       try{
		   departmentName=getRsString(rs,"departmentName");
		   productName = getRsString(rs,"productName");
	   	   productType = getRsString(rs,"productType");
		   quantity = rs.getDouble("quantity");
		   price = rs.getDouble("price");
           return 1;
	   }catch(SQLException e){
			e.printStackTrace();
			System.out.println("query failed!");
			return -1;   
	    }		 
   }
   
   public int conditionSetup(javax.servlet.ServletRequest request) {
	String beginDaten;
	String beginDatey;
	String beginDater;	
	String endDaten;
	String endDatey;
	String endDater;	
	int yue;
	int state;
	if((request.getParameter("beginDaten")!=null)&&(request.getParameter("beginDatey")!=null)&&(request.getParameter("beginDater")!=null)&&(!request.getParameter("beginDaten").equals(""))&&(!request.getParameter("beginDatey").equals(""))&&(!request.getParameter("beginDater").equals(""))){
		beginDaten = request.getParameter("beginDaten");
		beginDatey = request.getParameter("beginDatey");
		beginDater = request.getParameter("beginDater");
		beginDate=Timestamp.valueOf(beginDaten+"-"+beginDatey+"-"+beginDater+" 0:0:0.0");
	} else {
		yue = new Integer(getStringDate(getCurDate(),2)).intValue() - 1;
	        beginDatey = "" + yue;	
		beginDate = Timestamp.valueOf(getStringDate(getCurDate(),1)+"-"+beginDatey+"-"+getStringDate(getCurDate(),3)+" 0:0:0.0");		
	}		

	if((request.getParameter("endDaten")!=null)&&(request.getParameter("endDatey")!=null)&&(request.getParameter("endDater")!=null)&&(!request.getParameter("endDaten").equals(""))&&(!request.getParameter("endDatey").equals(""))&&(!request.getParameter("endDater").equals(""))){
		endDaten = request.getParameter("endDaten");
		endDatey = request.getParameter("endDatey");
		endDater = request.getParameter("endDater");
		endDate=Timestamp.valueOf(endDaten+"-"+endDatey+"-"+endDater+" 0:0:0.0");
	} else {
		endDate = getCurDate();	
	}
	if((request.getParameter("state")!=null)&&(!request.getParameter("state").equals("")))
	{
		state=Integer.parseInt(request.getParameter("state"));
		ullageProduct(beginDate,endDate,state);		
	}		
		return 0;		
   }
   
   public int ullageProduct(java.sql.Timestamp aBeginDate,java.sql.Timestamp aEndDate,int state) {
     int i;
	 String sql;
     switch (state) {
	 	case 0: 
	 		sql= "SELECT deptName AS departmentName,productName,productType,sum(appQuantity) as quantity,appPrice as price FROM v_UlltageProduct where ullageDate >=? AND ullageDate <dateadd(day,1,?)  group by deptName,productName,productType,appPrice";
			break;
		case 1:
			sql="SELECT deptName AS departmentName,productName,productType,sum(appQuantity) as quantity,appPrice as price FROM v_UlltageProduct where ullageDate >=? AND ullageDate <dateadd(day,1,?)  group by deptName,productName,productType,appPrice";
			break;
		case 3:
			sql="SELECT deptName AS departmentName,productName,productType,sum(appQuantity) as quantity,appPrice as price FROM v_UlltageProduct where ullageDate >=? AND ullageDate <dateadd(day,1,?)  group by deptName,productName,productType,appPrice";
			break;
		case 10:
			sql="SELECT deptName AS departmentName,productName,productType,sum(actQuantity) as quantity,actPrice as price FROM v_UlltageProduct where ullageDate >=? AND ullageDate <dateadd(day,1,?)  AND actQuantity <> 0 group by deptName,productName,productType,actPrice";
			break;
		case 11:
			sql="SELECT deptName AS departmentName,productName,productType,sum(actQuantity) as quantity,actPrice as price FROM v_UlltageProduct where ullageDate >=? AND ullageDate <dateadd(day,1,?) AND actQuantity <> 0 group by deptName,productName,productType,actPrice";
			break;
		default:
			sql="SELECT deptName AS departmentName,productName,productType,sum(actQuantity) as quantity,actPrice as price FROM v_UlltageProduct where ullageDate >=? AND ullageDate <dateadd(day,1,?) group by deptName,productName,productType,actPrice";
			break;
	}
 
     try{
   	PreparedStatement updStmt = conn.prepareStatement(sql,rs.TYPE_SCROLL_SENSITIVE,rs.CONCUR_READ_ONLY);
//	PreparedStatement updStmt=conn.prepareStatement(sql);
        updStmt.setTimestamp(1,aBeginDate);
        updStmt.setTimestamp(2,aEndDate);
        rs = updStmt.executeQuery();        
        int rtcode = 0; 
	return rtcode;  
      }catch(SQLException E){
      	System.out.println(E.getMessage());
      	return -102;
      }
   }
   
   
}