salesslipreport.java

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

package src.report;

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

public class SalesSlipReport extends ShangObject
{
	/**
产品名称
	*/
	private String productName;
	
	/**
月收入数量
	*/
	private double actQuantity;
	
	/**
月收入单价
	*/
	private double unitPrice;

	/**
月收入货款
	*/
	private double invoiceMoney;

	/**
月成本单价
	*/
	private double refPrice;
	
	/**
月成本金额
	*/
	private double costMoney;
	
	/**
月毛利
	*/
	private double grossProfit;
	
	/**
累积毛利
	*/
	private double cumulateGrossProfit;
		
	/**
累积收入数量
	*/
	private double cumulateActQuantity;
	
	/**
累积收入单价
	*/
	private double cumulateUnitPrice;
	
	/**
累积收入货款
	*/
	private double cumulateInvoiceMoney;
	
	/**
累积成本单价
	*/
	private double cumulateRefPrice;
	
	/**
累积成本金额
	*/
	private double cumulateCostMoney;
	
	/**
总收入货款
	*/
	public static double totalInvoiceMoney;

	/**
总成本金额
	*/
	public static double totalRefMoney;
	
	/**
累积总收入货款
	*/
	public static double cumulateTotalInvoiceMoney;

	/**
累积总成本金额
	*/
	public static double cumulateTotalRefMoney;

	/**
月开始日期
	*/
	public Timestamp monthBeginDate;

	/**
月结束日期
	*/
	public Timestamp monthEndDate;

	/**
年开始日期
	*/
	public Timestamp yearBeginDate;

	/**
年结束日期
	*/
	public Timestamp yearEndDate;

    	public SalesSlipReport()
        {
          numInOnePager=10;
          totalInvoiceMoney=0;
          totalRefMoney=0;
		  cumulateTotalInvoiceMoney=0;
          cumulateTotalRefMoney=0;
        }

		public double getActQuantity()
        {
          return actQuantity;
        }
		
		public double getUnitPrice()
		{
			return unitPrice;
		}

        public double getInvoiceMoney()
        {
          return invoiceMoney;
        }

        public String getProductName()
        {
          return productName;
        }

        public double getRefPrice()
        {
          return refPrice;
        }
		
		public double getCostMoney()
		{
			return costMoney;
		}
		
		public double getGrossProfit()
		{
			return grossProfit;
		}
		
		public double getCumulateActQuantity()
		{
			return cumulateActQuantity;
		}
		
		public double getCumulateUnitPrice()
		{
			return cumulateUnitPrice;
		}
				
		public double getCumulateInvoiceMoney()
		{
			return cumulateInvoiceMoney;
		}
		
		public double getCumulateRefPrice()
		{
			return cumulateRefPrice;
		}
		
		public double getCumulateCostMoney()
		{
			return cumulateCostMoney;
		}
		
		public double getCumulateGrossProfit()
		{
			return cumulateGrossProfit;
		}
		
		/*public String formatDouble(double number1,int per)
		{
			BigDecimal num1=new BigDecimal(number1);
			num1 = num1.setScale(per,5);
			return num1.toString();
		}*/

        public int loadAttr(){
        try{
			productName=getRsString(rs,"productName");
	   		actQuantity=rs.getDouble("actQuantity");
			unitPrice=rs.getDouble("unitPrice");
	   		invoiceMoney = rs.getDouble("invoiceMoney");
	   		refPrice = rs.getDouble("refPrice");
			costMoney=rs.getDouble("costMoney");
			grossProfit=rs.getDouble("grossProfit");
			cumulateActQuantity=rs.getDouble("cumulateActQuantity");
			cumulateUnitPrice=rs.getDouble("cumulateUnitPrice");
			cumulateInvoiceMoney=rs.getDouble("cumulateInvoiceMoney");
			cumulateRefPrice=rs.getDouble("cumulateRefPrice");
			cumulateCostMoney=rs.getDouble("cumulateCostMoney");
			cumulateGrossProfit=rs.getDouble("cumulateGrossProfit");
           	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;
	yearBeginDate=Timestamp.valueOf(getStringDate(getCurDate(),1) + "-1-1 0:0:0.0");
	yearEndDate=Timestamp.valueOf(getStringDate(getCurDate(),1) + "-" + getStringDate(getCurDate(),2) + "-" + getStringDate(getCurDate(),3) + " 0:0:0.0"); 
	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");
		monthBeginDate=Timestamp.valueOf(beginDaten+"-"+beginDatey+"-"+beginDater+" 0:0:0.0");
	} else {
		yue = new Integer(getStringDate(getCurDate(),2)).intValue() - 1;
	        beginDatey = "" + yue;	
		monthBeginDate = 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");
		monthEndDate=Timestamp.valueOf(endDaten+"-"+endDatey+"-"+endDater+" 0:0:0.0");
	} else {
		monthEndDate = getCurDate();	
	}
		monthReport();		
		return 0;
   }
   
   public int monthReport() {
     try{
	//CallableStatement updStmt=conn.prepareCall("{?=call sp_SalesSlipReport(?,?,?,?)}");
	String sql="SELECT Production.productName AS productName,MonthSale.quantity AS actQuantity,MonthSale.price AS unitPrice,MonthSale.invoiceMoney, " +
		"MonthSale.refPrice AS refPrice,MonthSale.costMoney AS costMoney,MonthSale.grossProfit AS grossProfit, "+
		"CumulateSale.quantity AS cumulateActQuantity, CumulateSale.price AS cumulateUnitPrice,CumulateSale.invoiceMoney AS cumulateInvoiceMoney, "+
		"CumulateSale.refPrice AS cumulateRefPrice,CumulateSale.costMoney AS cumulateCostMoney,CumulateSale.grossProfit AS cumulateGrossProfit "+
		"FROM Production "+

		"LEFT OUTER JOIN "+
		"(select Production.productId,sum(SlipProduct.actQuantity) as quantity,sum(Invoice.invoiceMoney)/sum(SlipProduct.actQuantity) as price,sum(Invoice.invoiceMoney) as invoiceMoney,Production.refPrice,sum(SlipProduct.actQuantity)*Production.refPrice as costMoney,sum(Invoice.invoiceMoney)-(sum(SlipProduct.actQuantity)*Production.refPrice) as grossProfit "+
		"from Production "+
		"left outer join SlipProduct on Production.productId=SlipProduct.productId "+
		"left outer join (select * from Invoice where Invoice.receiptType = 0 AND Invoice.invoiceDate >=? AND  Invoice.invoiceDate <dateadd(day,1,?)) Invoice on SlipProduct.slipId=Invoice.receiptId "+
		"where SlipProduct.actQuantity<>0 group by Production.productId,Production.refPrice) MonthSale "+
		"ON Production.productId=MonthSale.productId "+
		"LEFT OUTER JOIN "+
		"(select Production.productId,sum(SlipProduct.actQuantity) as quantity,sum(Invoice.invoiceMoney)/sum(SlipProduct.actQuantity) as price,sum(Invoice.invoiceMoney) as invoiceMoney,Production.refPrice,sum(SlipProduct.actQuantity)*Production.refPrice as costMoney,sum(Invoice.invoiceMoney)-(sum(SlipProduct.actQuantity)*Production.refPrice) as grossProfit "+
		"from Production "+
		"left outer join SlipProduct on Production.productId=SlipProduct.productId "+
		"left outer join (select * from Invoice where Invoice.receiptType = 0 AND Invoice.invoiceDate >=? AND  Invoice.invoiceDate <dateadd(day,1,?)) Invoice on SlipProduct.slipId=Invoice.receiptId "+
		"where SlipProduct.actQuantity<>0 group by Production.productId,Production.refPrice) CumulateSale "+
		"ON Production.productId=CumulateSale.productId ";
        //System.out.println(sql);
        PreparedStatement updStmt = conn.prepareStatement(sql,rs.TYPE_SCROLL_INSENSITIVE,rs.CONCUR_READ_ONLY);
	updStmt.setTimestamp(1,monthBeginDate);
        updStmt.setTimestamp(2,monthEndDate);
	updStmt.setTimestamp(3,yearBeginDate);
	updStmt.setTimestamp(4,yearEndDate);
        rs = updStmt.executeQuery();        
        int rtcode = 0; 
		return rtcode;  
      }catch(SQLException E){
      	System.out.println(E.getMessage());
      	return -102;
      }
   }


}