salecost.java

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

			sql=sql+" group by employeeid) as t";
			sql=sql+" on employee.employeeid=t.employeeid";
			//招待费
			sql=sql+" left join ";
			sql=sql+" (select employeeid,sum(feemoney)as serverCost 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=24";
			sql=sql+" group by employeeid) as u";
			sql=sql+" on employee.employeeid=u.employeeid";
			//办公费
			sql=sql+" left join ";
			sql=sql+" (select employeeid,sum(feemoney)as workCost 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=25";
			sql=sql+" group by employeeid) as v";
			sql=sql+" on employee.employeeid=v.employeeid";
			//条件
			sql=sql+" where employee.employeeid='"+employeeId+"'";
		}else {
			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";
			sql=sql+" group by employeeid) as t";
			sql=sql+" on employee.employeeid=t.employeeid";
			//招待费
			sql=sql+" left join ";
			sql=sql+" (select employeeid,sum(feemoney)as serverCost 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=24";
			sql=sql+" group by employeeid) as u";
			sql=sql+" on employee.employeeid=u.employeeid";
			//办公费
			sql=sql+" left join ";
			sql=sql+" (select employeeid,sum(feemoney)as workCost 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=25";
			sql=sql+" group by employeeid) as v";
			sql=sql+" on employee.employeeid=v.employeeid";
			//条件
			sql=sql+" where employee.employeeid in (select employeeId from employee where deptId='"+deptId+"')";
		}
		System.out.println(sql);
		try{
	     		Statement updStmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
		        rs = updStmt.executeQuery(sql);
		        int rtcode = 0; 
			return rtcode; 
	      	}catch(SQLException E){
		      	System.out.println(E.getMessage());
		      	return -102;
	      	}
   	}
   	
   	public int loadAttr(){
	try{
	   	   employeeId = getRsString(rs,"employeeId");
	   	   deptName=getRsString(rs,"deptName");
	   	   employeeName = getRsString(rs,"empName");
	   	   saleMoney = rs.getDouble("saleMoney");
	   	   saleCost = rs.getDouble("saleCost");
	   	   transCost = rs.getDouble("transCost");
	   	   storeCost = rs.getDouble("storeCost");
	   	   intest = rs.getDouble("intest");
	   	   stamTax = rs.getDouble("stamTax");
	   	   otherCost=rs.getDouble("otherCost");
	   	   //profitBeforeTax = rs.getDouble("profitBeforeTax");
	   	   profitBeforeTax=saleMoney-saleCost-transCost-storeCost-intest-stamTax-otherCost;
	   	   evectionCost = rs.getDouble("evectionCost");
	   	   communicateCost = rs.getDouble("communicateCost");
	   	   serverCost = rs.getDouble("serverCost");
	   	   workCost = rs.getDouble("workCost");
	   	   salary = rs.getDouble("salary");
	   	   //profit = rs.getDouble("profit");
	   	   profit=profitBeforeTax-evectionCost-communicateCost-serverCost-workCost-salary;
	   	   //countNum = rs.getInt("countNum");
	           return 1;
	   }catch(SQLException e){
			e.printStackTrace();
			System.out.println("query failed!");
			return -1;   
	   }		 
  	}
	
	public String getDeptId()
	{
		return deptId;
	}
	
	public String getDeptName()
	{
		return deptName;
	}
	
	public String getEmployeeId()
	{
		return employeeId;
	}
	
	public String getEmployeeName()
	{
		return employeeName;
	}
	
	public double getSaleMoney()
	{
		return saleMoney;
	}
	
	public double getSaleCost()
	{
		return saleCost;
	}
	
	public double getTransCost()
	{
		return transCost;
	}
	
	public double getStoreCost()
	{
		return storeCost;
	}
	
	public double getIntest()
	{
		return intest;
	}
	
	public double getStamTax()
	{
		return stamTax;
	}
	
	public double getOtherCost()
	{
		return otherCost;
	}
	
	public double getProfitBeforeTax()
	{
		return profitBeforeTax;
	}
	
	public double getEventionCost()
	{
		return evectionCost;
	}
	
	public double getCommunicateCost()
	{
		return communicateCost;
	}
	
	public double getServerCost()
	{
		return serverCost;
	}
	
	public double getWorkCost()
	{
		return workCost;
	}
	
	public double getSalary()
	{
		return salary;
	}
	
	public double getProfit()
	{
		return profit;
	}
}