salecost.java

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

package src.wuyang;
import src.com.*;
import java.sql.*;
import java.io.*;
import java.math.BigDecimal;

public class SaleCost extends ShangObject
{
	/*
	部门名称
	**/
	private String deptId;
	
	/*
	部门姓名
	**/
	private String deptName;
	
	/*
	员工编号
	**/
	private String employeeId;
	
	/*
	员工名称
	**/
	private String employeeName;
	
	/*
	销售收入
	**/
	private double saleMoney;
	
	/*
	销售成本
	**/
	private double saleCost;
	
	/*
	运杂费
	**/
	private double transCost;
	
	/*
	仓储费 
	**/
	private double storeCost;
	
	/*
	其他费用 
	**/
	private double otherCost;
	/*
	资金占用利息
	**/
	private double intest;
	
	/*
	二金一费印花税代理费 
	**/
	private double stamTax;
	
	/*
	个人税前利润
	**/
	private double profitBeforeTax;
	
	/**
	旅差费
	*/
	private double evectionCost;
 
	/**
	通讯费
	*/
	private double communicateCost;   
   
	/**
	招待费
	*/
	private double serverCost;   

	/**
	办公费和其他
	*/
	private double workCost;   
  
	/*
	个人工资
	**/
	private double salary;
	
	/**
	利润
	*/
	private double profit;
	
	/**
	开始日期
	*/
   	public Timestamp beginDate;
   
   	/**
	结束日期
   	*/
   	public Timestamp endDate;  
   	
   	private int flagReport;
	// 0 表示查员工 1 表示查部门
	
	public SaleCost()
	{
		numInOnePager=10;
	}
	
	public String formatDouble(double number1,int per)
	{
	BigDecimal num1=new BigDecimal(number1);
	num1 = num1.setScale(per,5);
	return num1.toString();
	}
	
	public int conditionSetup(javax.servlet.ServletRequest request) {
		String beginDaten;
		String beginDatey;
		String beginDater;	
		String endDaten;
		String endDatey;
		String endDater;	
		int yue;
		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+"23:59: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+"23:59:0.0");
		} else {
			endDate = getCurDate();	
		}
		if ((request.getParameter("deptId")!=null)&&(!request.getParameter("deptId").equals(""))) {
			deptId=request.getParameter("deptId");
			flagReport=0;
		}
		if ((request.getParameter("employeeId")!=null)&&(!request.getParameter("employeeId").equals(""))) {
			employeeId=request.getParameter("employeeId");
			flagReport=1;
		}
		saleCost();
		return 0;
   	}
   	
   	public int saleCost()	 
   	{
   		int i;
   		String sbeginDate="";
	   	String sendDate="";
	   	sbeginDate=getStringDate(beginDate,0);
	   	sendDate=getStringDate(endDate,0);
		String sql= "";
		if (flagReport==1) {
			sql="select employee.employeeid,department.deptName,employee.empName,l.saleMoney,m.saleCost,n.transCost,o.storeCost,p.intest,q.stamTax,r.otherCost,";
			sql=sql+" s.evectionCost,t.communicateCost,u.serverCost,v.workCost,(isnull(daSalary,0)+isnull(slSalary,0)+isnull(zwAllowance,0)+isnull(gbAllowance,0)) as salary";
			sql=sql+" from employee ";
			sql=sql+" left join department ";
			sql=sql+" on employee.deptId=department.deptId";
			//销售收入
			sql=sql+" left join";
			sql=sql+" (select employeeid,sum(invoicemoney) as saleMoney from invoice";
			sql=sql+" where receiptType=0 and invoiceDate>='"+sbeginDate+"' and invoiceDate<dateadd(day,1,'"+sendDate+"')";
			sql=sql+" group by employeeid) as l";
			sql=sql+" on employee.employeeid=l.employeeid";
			//销售成本
			sql=sql+" left join";
			sql=sql+" (select proposer,sum(a.cost) as saleCost from SalesSlip left join";
			sql=sql+" (select slipId,sum(isnull(actquantity,0)*isnull(refPrice,0)) as cost from slipProduct ";
			sql=sql+" left join production on slipProduct.productId=production.productId";
			sql=sql+" group by slipid)as a";
			sql=sql+" on salesslip.slipid=a.slipid";
			sql=sql+" where slipdate>='"+sbeginDate+"' and slipdate<dateadd(day,1,'"+sendDate+"')";
			sql=sql+" group by proposer)as m";
			sql=sql+" on employee.employeeid=m.proposer";
			//运杂费
			sql=sql+" left join ";
			sql=sql+" (select proposer,sum(feemoney) as transCost from salesslip left join ";
			sql=sql+" (select slipid,feeid,sum(feemoney) as feemoney from feecost";
			sql=sql+" where feedate>='"+sbeginDate+"' and feedate<dateadd(day,1,'"+sendDate+"')";
			sql=sql+" group by slipid,feeid) as a";
			sql=sql+" on salesslip.slipid=a.slipid";
			sql=sql+" where feeid in(1,2)";
			sql=sql+" group by proposer)as n";
			sql=sql+" on employee.employeeid=n.proposer";
			//仓储费
			sql=sql+" left join";
			sql=sql+" (select proposer,sum(feemoney) as storeCost from salesslip left join ";
			sql=sql+" (select slipid,feeid,sum(feemoney) as feemoney from feecost";
			sql=sql+" where feedate>='"+sbeginDate+"' and feedate<dateadd(day,1,'"+sendDate+"')";
			sql=sql+" group by slipid,feeid) as a";
			sql=sql+" on salesslip.slipid=a.slipid";
			sql=sql+" where feeid in(3)";
			sql=sql+" group by proposer)as o";
			sql=sql+" on employee.employeeid=o.proposer";
			//资金占用费
			sql=sql+" left join";
			sql=sql+" (select proposer,sum(feemoney) as intest from salesslip left join ";
			sql=sql+" (select slipid,feeid,sum(feemoney) as feemoney from feecost";
			sql=sql+" where feedate>='"+sbeginDate+"' and feedate<dateadd(day,1,'"+sendDate+"')";
			sql=sql+" group by slipid,feeid) as a";
			sql=sql+" on salesslip.slipid=a.slipid";
			sql=sql+" where feeid in(4)";
			sql=sql+" group by proposer)as p";
			sql=sql+" on employee.employeeid=p.proposer";
			//印花税
			sql=sql+" left join";
			sql=sql+" (select proposer,sum(feemoney) as stamTax from salesslip left join ";
			sql=sql+" (select slipid,feeid,sum(feemoney) as feemoney from feecost";
			sql=sql+" where feedate>='"+sbeginDate+"' and feedate<dateadd(day,1,'"+sendDate+"')";
			sql=sql+" group by slipid,feeid) as a";
			sql=sql+" on salesslip.slipid=a.slipid";
			sql=sql+" where feeid in(5,6,7)";
			sql=sql+" group by proposer)as q";
			sql=sql+" on employee.employeeid=q.proposer";
			//其他费用
			sql=sql+" left join";
			sql=sql+" (select proposer,sum(feemoney) as otherCost from salesslip left join ";
			sql=sql+" (select slipid,feeid,sum(feemoney) as feemoney from feecost";
			sql=sql+" where feedate>='"+sbeginDate+"' and feedate<dateadd(day,1,'"+sendDate+"')";
			sql=sql+" group by slipid,feeid) as a";
			sql=sql+" on salesslip.slipid=a.slipid";
			sql=sql+" where feeid in(9)";
			sql=sql+" group by proposer)as r";
			sql=sql+" on employee.employeeid=r.proposer";
			//旅差费
			sql=sql+" left join ";
			sql=sql+" (select employeeid,sum(feemoney)as evectionCost from account";
			sql=sql+" left join operatefee on account.accountId=operatefee.accountid";
			sql=sql+" where feedate>='"+sbeginDate+"' and feedate<dateadd(day,1,'"+sendDate+"') and feeid=22";
			sql=sql+" group by employeeid) as s";
			sql=sql+" on employee.employeeid=s.employeeid";
			//通讯费
			sql=sql+" left join ";
			sql=sql+" (select employeeid,sum(feemoney)as communicateCost from account";
			sql=sql+" left join operatefee on account.accountId=operatefee.accountid";
			sql=sql+" where feedate>='"+sbeginDate+"' and feedate<dateadd(day,1,'"+sendDate+"') and feeid=23";