departmentfeereport.java

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

   public int departmentFeeReport() {
     int i;
     String sql= "{?=call sp_DepartmentFeeReport(?,?,?,?)}";
 
     try{
	CallableStatement updStmt=conn.prepareCall(sql);
	updStmt.registerOutParameter(1, java.sql.Types.INTEGER);/** 注册OUT参数 */
/*
	updStmt.setTimestamp(2,beginDate);
        updStmt.setTimestamp(3,endDate);
        updStmt.setInt(4,curPage);
        updStmt.setInt(5,numInOnePager);
        rs = updStmt.executeQuery();        
        int rtcode = 0; 
	return rtcode;  
      }catch(SQLException E){
      	System.out.println(E.getMessage());
      	return -102;
      }
   }
   */
   public int departmentFeeReport() {
   	int i;
   	sql="";
   	String sBeginDate="";
   	String sEndDate="";
   	sBeginDate=getStringDate(beginDate,0);
   	sEndDate=getStringDate(endDate,0);
   	try{
	sql=" select department.deptId,department.deptName,a.invoiceMoney,b.saleCost,((isnull(a.invoiceMoney,0.00)-isnull(b.saleCost,0.00))*0.145)as valueAddedTax,c.transferCost,d.storeCost,e.intest,";
	sql=sql + " f.stamTax,o.otherCost,";
	sql=sql + " (isnull(a.invoiceMoney,0.00)-isnull(b.saleCost,0.00)-((isnull(a.invoiceMoney,0.00)-isnull(b.saleCost,0.00))*0.145)-isnull(c.transferCost,0.00)-isnull(d.storeCost,0.00)-isnull(e.intest,0.00)-isnull(f.stamTax,0.00)-isnull(o.otherCost,0.00))*(1-isnull(z.ratio,0.00)) as handinIncome,";
	sql=sql + " (isnull(a.invoiceMoney,0.00)-isnull(b.saleCost,0.00)-((isnull(a.invoiceMoney,0.00)-isnull(b.saleCost,0.00))*0.145)-isnull(c.transferCost,0.00)-isnull(d.storeCost,0.00)-isnull(e.intest,0.00)-isnull(f.stamTax,0.00)-isnull(o.otherCost,0.00))*(isnull(z.ratio,0.00)) as departmentIncome,";
	sql=sql + " g.settlementOut,h.settlementIn,i.salary,j.evectionCost,k.communicateCost,l.serverCost,m.workCost,";
	sql=sql + " (isnull(a.invoiceMoney,0.00)-isnull(b.saleCost,0.00)-((isnull(a.invoiceMoney,0.00)-isnull(b.saleCost,0.00))*0.145)-isnull(c.transferCost,0.00)-isnull(d.storeCost,0.00)-isnull(e.intest,0.00)-isnull(f.stamTax,0.00)-isnull(o.otherCost,0.00))*(isnull(z.ratio,0.00))+isnull(h.settlementIn,0.00)-isnull(g.settlementOut,0.00)-isnull(i.salary,0.00)-isnull(j.evectionCost,0.00)-isnull(k.communicateCost,0.00)-isnull(l.serverCost,0.00)-isnull(m.workCost,0.00) as profit";
	sql=sql + " from department ";
	sql=sql + " left join";
	sql=sql + " (select deptid,isnull(sum(invoicemoney),0.00) as invoicemoney from invoice where receipttype=0 and invoiceDate>='" + sBeginDate + "' and invoiceDate<dateadd(day,1,'" + sEndDate + "') group by deptid) as a";
	sql=sql + " on department.deptid=a.deptid";
	sql=sql + " left join ";
	sql=sql + " (select deptid,isnull(sum(v_DeptFeeReportSaleCost.price),0.00)as salecost from invoice";
	sql=sql + " inner join v_DeptFeeReportSaleCost on invoice.receiptid=v_DeptFeeReportSaleCost.slipid";
	sql=sql + " where invoice.receipttype=0 and invoiceDate>='" + sBeginDate + "' and invoiceDate<dateadd(day,1,'" + sEndDate + "') group by deptid) as b";
	sql=sql + " on department.deptid=b.deptid";
	sql=sql + " left join ";
	sql=sql + " (select deptid,isnull(sum(feecost.feemoney),0.00)as transferCost from invoice left join feecost";
	sql=sql + " on invoice.receiptid=feecost.slipid where feecost.feeid in (1,2) and invoice.receipttype=0 and invoiceDate>='" + sBeginDate + "' and invoiceDate<dateadd(day,1,'" + sEndDate + "') group by deptid) as c";
	sql=sql + " on department.deptid=c.deptid";
	sql=sql + " left join";
	sql=sql + " (select deptid,isnull(sum(feecost.feemoney),0.00)as storeCost from invoice left join feecost on invoice.receiptid=feecost.slipid";
	sql=sql + " WHERE feecost.feeid in (3) and invoice.receipttype=0 and invoiceDate>='" + sBeginDate + "' and invoiceDate<dateadd(day,1,'" + sEndDate + "') group by deptid) as d";
	sql=sql + " on department.deptid=d.deptid";
	sql=sql + " left join";
	sql=sql + " (select deptid,isnull(sum(feecost.feemoney),0.00)as intest from invoice left join feecost on invoice.receiptid=feecost.slipid";
	sql=sql + " where feecost.feeid in (4) and invoice.receipttype=0 and invoiceDate>='" + sBeginDate + "' and invoiceDate<dateadd(day,1,'" + sEndDate + "') group by deptid) as e";
	sql=sql + " on department.deptid=e.deptid";
	sql=sql + " left join ";
	sql=sql + " (select deptid,isnull(sum(feecost.feemoney),0.00)as stamTax from invoice left join feecost on invoice.receiptid=feecost.slipid";
	sql=sql + " where feecost.feeid in (5,6,7) and invoice.receipttype=0 and invoiceDate>='" + sBeginDate + "' and invoiceDate<dateadd(day,1,'" + sEndDate + "') group by deptid) as f";
	sql=sql + " on department.deptid=f.deptid";
	sql=sql + " left join ";
	sql=sql + " (select deptid,isnull(sum(feecost.feemoney),0.00)as otherCost from invoice left join feecost on invoice.receiptid=feecost.slipid";
	sql=sql + " where feecost.feeid in (9) and invoice.receipttype=0 and invoiceDate>='" + sBeginDate + "' and invoiceDate<dateadd(day,1,'" + sEndDate + "') group by deptid) as o";
	sql=sql + " on department.deptid=o.deptid";
	sql=sql + " left join";
	sql=sql + " (select deptid,ratio from department)as z";
	sql=sql + " on department.deptid=z.deptid";
	sql=sql + " left join ";
	sql=sql + " (select deptid,(isnull(sum(tranmoney),0.00)*0.3) as settlementOut from depttransaction where tranDate>='" + sBeginDate + "' and tranDate<dateadd(day,1,'" + sEndDate + "') group by deptid) as g";
	sql=sql + " on department.deptid=g.deptid";
	sql=sql + " left join";
	sql=sql + " (select todeptid,(isnull(sum(tranmoney),0.00)*0.3) as settlementIn from depttransaction where tranDate>='" + sBeginDate + "' and tranDate<dateadd(day,1,'" + sEndDate + "') group by todeptid) as h";
	sql=sql + " on department.deptid=h.todeptid";
	sql=sql + " left join ";
	sql=sql + " (select deptid,isnull(sum(operatefee.feemoney),0.00)as salary from employee left join";
	sql=sql + " account on employee.employeeid=account.employeeid";
	sql=sql + " left join operatefee on account.accountid=operatefee.accountid";
	sql=sql + " where operatefee.operatefeeid=28 and operatefee.feedate>='" + sBeginDate + "' and operatefee.feedate<dateadd(day,1,'" + sEndDate + "') group by deptid) as i";
	sql=sql + " on department.deptid=i.deptid";
	sql=sql + " left join";
	sql=sql + " (select deptid,isnull(sum(operatefee.feemoney),0.00)as evectionCost from employee left join";
	sql=sql + " account on employee.employeeid=account.employeeid left join ";
	sql=sql + " operatefee on account.accountid=operatefee.accountid";
	sql=sql + " where operatefee.operatefeeid=22 and operatefee.feedate>='" + sBeginDate + "' and operatefee.feedate<dateadd(day,1,'" + sEndDate + "') group by deptid) as j";
	sql=sql + " on department.deptid=j.deptid";
	sql=sql + " left join";
	sql=sql + " (select deptid,isnull(sum(operatefee.feemoney),0.00)as communicateCost from employee left join";
	sql=sql + " account on employee.employeeid=account.employeeid left join ";
	sql=sql + " operatefee on account.accountid=operatefee.accountid";
	sql=sql + " where operatefee.operatefeeid=23 and operatefee.feedate>='" + sBeginDate + "' and operatefee.feedate<dateadd(day,1,'" + sEndDate + "')  group by deptid) as k";
	sql=sql + " on department.deptid=k.deptid";
	sql=sql + " left join";
	sql=sql + " (select deptid,isnull(sum(operatefee.feemoney),0.00)as serverCost from employee left join";
	sql=sql + " account on employee.employeeid=account.employeeid left join ";
	sql=sql + " operatefee on account.accountid=operatefee.accountid";
	sql=sql + " where operatefee.operatefeeid=24 and operatefee.feedate>='" + sBeginDate + "' and operatefee.feedate<dateadd(day,1,'" + sEndDate + "')  group by deptid) as l";
	sql=sql + " on department.deptid=l.deptid";
	sql=sql + " left join";
	sql=sql + " (select deptid,isnull(sum(operatefee.feemoney),0.00)as workCost from employee left join";
	sql=sql + " account on employee.employeeid=account.employeeid left join ";
	sql=sql + " operatefee on account.accountid=operatefee.accountid";
	sql=sql + " where operatefee.operatefeeid=25 and operatefee.feedate>='" + sBeginDate + "' and operatefee.feedate<dateadd(day,1,'" + sEndDate + "')  group by deptid) as m";
	sql=sql + " on department.deptid=m.deptid";
	sql=sql + " where " + whereClauseTemp;
	System.out.println(sql);
	Statement stmt=conn.createStatement();
	rs=stmt.executeQuery(sql);
	int rtcode=0;
	return rtcode;
	}catch(SQLException e){
	System.out.println(e.getMessage());
	return -102;
	}
   }
   
   
   /**
用于分页显示, 处理如下操作：
next , previous,覆盖原方法
*/
public int listPage(javax.servlet.ServletRequest request) {
   
   String browseMode = (String) request.getParameter("browseMode");
   String position = (String) request.getParameter("position");
   	listPosition = (new Integer(position)).intValue();
   String curentPage = (String) request.getParameter("curPage");
   	curPage = (new Integer(curentPage)).intValue();	
   	
   	if(browseMode.equals("first")){
   		curPage = 1;
   		listPosition = 0;
   		}
   	if(browseMode.equals("next")){         					 /*下翻页*/
	 		   curPage=curPage+1;
			   listPosition=listPosition+numInOnePager;
			   if( (listPosition+numInOnePager)>=recordNum ){
			       state=-1;
			   }else{ 
			       state=1;
			   }
	 	} 
	 	
	 if(browseMode.equals("previous")) {                               /*上翻页*/
	 		curPage=curPage-1;
			listPosition=listPosition-numInOnePager;
			   if (listPosition==0){
			       state=2;
			  } else {
			   	state=1;
			   }
	 	} 
/*
 	first(false);
 	previous(false);	
									/*重新定位*/
   	/*for (int index = 0; index < listPosition; index++) {
   		next(false);
   	}*/

	return 1;
}
   

   public String getDepartmentName()
   {
	return departmentName;
   }
   
   public double getInvoiceMoney()
   {
   	return invoiceMoney;
   }
   
   public double getSaleCost()
   {
   	return saleCost;
   }
   
   public double getValueAddedTax()
   {
   	return valueAddedTax;
   }
   
   public double getTransferCost()
   {
   	return transferCost;
   }
   
   public double getStoreCost()
   {
   	return storeCost;
   }
   
   public double getIntest()
   {
   	return intest;
   }
   
   public double getStamTax()
   {
   	return stamTax;
   }
   
   public double getOtherCost()
   {
   	return otherCost;
   }
   
   public double getHandinIncome()
   {
   	return handinIncome;
   }
   
   public double getDepartmentIncome()
   {
   	return departmentIncome;
   }
   
   public double getSettlementOut()
   {
   	return settlementOut;
   }
   
   public double getSettlementIn()
   {
   	return settlementIn;
   }
   
   public double getSalary()
   {
   	return salary;
   }
   
   public double getEvectionCost()
   {
   	return evectionCost;
   }
   
   public double getCommunicateCost()
   {
   	return communicateCost;
   }
   
   public double getServerCost()
   {
   	return serverCost;
   }
   
   public double getWorkCost()
   {
   	return serverCost;
   }
   
   public double getProfit()
   {
   	return profit;
   }
   
   public int getCountNum()
   {
   	return countNum;
   }
}