acurrentaccoutreport.java

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

		beginDate = Timestamp.valueOf(getStringDate(getCurDate(),1)+"-"+beginDatey+"-"+getStringDate(getCurDate(),3)+" 0:0:0.0");		
		System.out.println("2");
   	}
	getDate(beginDate);
	setWhereDate();
	report();
	return 0;		
   }
   
   private void setWhereDate()
   {
   	beginDateWhere="";
	endDateWhere="";
	lmBeginDateWhere="";
	lmEndDateWhere="";
	lastBeginDateWhere="";
	lastEndDateWhere="";
	lmLastBeginDateWhere="";
	lmLastEndDateWhere="";
	
   	String sbeginDate="";
   	String sendDate="";
   	String slastBeginDate="";
   	String slastEndDate="";
   	sbeginDate=getStringDate(beginDate,0);
   	sendDate=getStringDate(endDate,0);
   	slastBeginDate=getStringDate(lastBeginDate,0);
   	slastEndDate=getStringDate(lastEndDate,0);
   	//whereClause=addWhereClause(whereClause, "invoiceDate", ">=", "'",beginDateTemp);
   	//lmWhereClause=addWhereClause(lmWhereClause, "lmDate", ">=", "'",beginDateTemp);
   	beginDateWhere=addWhereClause(beginDateWhere,"invoiceDate",">=","'",sbeginDate);	//应收本月开始日期
   	endDateWhere=addWhereClause(endDateWhere,"invoiceDate","<","'",sendDate);	//应收本月结束日期
   	lastBeginDateWhere=addWhereClause(lastBeginDateWhere,"invoiceDate",">=","'",slastBeginDate);	//应收上月开始日期
   	lastEndDateWhere=addWhereClause(lastEndDateWhere,"invoiceDate","<","'",slastEndDate);	//应收上月结束日期
   	lmBeginDateWhere=addWhereClause(lmBeginDateWhere,"lmDate",">=","'",sbeginDate);	//应付本月开始日期
   	lmEndDateWhere=addWhereClause(lmEndDateWhere,"lmDate","<","'",sendDate);	//应付本月结束日期
   	lmLastBeginDateWhere=addWhereClause(lmLastBeginDateWhere,"lmDate",">=","'",slastBeginDate);	//应付上月开始日期
   	lmLastEndDateWhere=addWhereClause(lmLastEndDateWhere,"lmDate","<","'",slastEndDate);	//应付上月开始日期
    }
    
    public String orWhereClause(String whereClause,   // 输入的已有的whereClause
    			String colName,        // 数据库的列名
    			String operator,       // 条件操作符号 >, < , like 
    			String fix,            // 引号 或 空 "'" , ""
    			String colValue) {     // 值
    	if( (colValue == null) || ( colValue.equals("")) )  // 未输入
		return whereClause;
    	if(fix.equals("'")){   // 字符型
		try {
	    		colValue = new String(colValue.getBytes("8859_1"));
		} catch(java.io.UnsupportedEncodingException e) {
			System.out.println("Baseservlet addWhereClause() : 编码不支持!!");
		}
	}
    	if( whereClause.equals("") == false) whereClause = whereClause + ",";
    	if(operator.equals("like"))
		whereClause = whereClause + fix + "%" + colValue + "%" + fix;
	else
		whereClause = whereClause + fix + colValue + fix;
    	return whereClause;
    }
   
   	/**
    往来款汇总表
   */
   public int report() {
     int i;
	String sql;
	sql=""; 
	if (flagReport==0) {
	//上月应收应付 
	sql=sql+" select department.deptid,department.deptname,k.lastAR,l.lastAP,m.thisAR,n.thisAP,o.invoicedate,o.lmdate from department left join ";
	sql=sql+" (select department.deptid,(isnull(invoicemoney,0)-isnull(lmmoney,0)) as lastAR from department ";
	sql=sql+" left join (select deptid,sum(invoicemoney) as invoicemoney from invoice where receipttype=0 and " +lastBeginDateWhere+" and " +lastEndDateWhere+ " group by deptId)as a ";
	sql=sql+" on department.deptid=a.deptid ";
	sql=sql+" left join (select invoice.deptid,sum(moneyflow.lmmoney) as lmmoney from invoice left join moneyflow on invoice.invoiceid=moneyflow.invoiceid where receipttype=0 and "+lmLastBeginDateWhere+" and "+lmLastEndDateWhere+" group by invoice.deptid)as b ";
	sql=sql+" on department.deptid=b.deptid) as k ";
	sql=sql+" on department.deptid=k.deptid ";
	sql=sql+" left join ";
	sql=sql+" (select department.deptid,(isnull(invoicemoney,0)-isnull(lmmoney,0)) as lastAP from department ";
	sql=sql+" left join (select deptid,sum(invoicemoney) as invoicemoney from invoice where receipttype=2 and " +lastBeginDateWhere+" and " +lastEndDateWhere+ " group by deptId)as a ";
	sql=sql+" on department.deptid=a.deptid ";
	sql=sql+" left join (select invoice.deptid,sum(moneyflow.lmmoney) as lmmoney from invoice left join moneyflow on invoice.invoiceid=moneyflow.invoiceid where receipttype=2 and "+lmLastBeginDateWhere+" and "+lmLastEndDateWhere+" group by invoice.deptid)as b ";
	sql=sql+" on department.deptid=b.deptid) as l ";
	sql=sql+" on department.deptid=l.deptid ";
	//本月应收应付
	sql=sql+" left join ";
	sql=sql+" (select department.deptid,(isnull(invoicemoney,0)-isnull(lmmoney,0)) as thisAR from department ";
	sql=sql+" left join (select deptid,sum(invoicemoney) as invoicemoney from invoice where receipttype=0 and " +beginDateWhere+" and " +endDateWhere+ " group by deptId)as a ";
	sql=sql+" on department.deptid=a.deptid ";
	sql=sql+" left join (select invoice.deptid,sum(moneyflow.lmmoney) as lmmoney from invoice left join moneyflow on invoice.invoiceid=moneyflow.invoiceid where receipttype=0 and "+lmBeginDateWhere+" and "+lmEndDateWhere+" group by invoice.deptid)as b ";
	sql=sql+" on department.deptid=b.deptid) as m ";
	sql=sql+" on department.deptid=m.deptid ";
	sql=sql+" left join ";
	sql=sql+" (select department.deptid,(isnull(invoicemoney,0)-isnull(lmmoney,0)) as thisAP from department ";
	sql=sql+" left join (select deptid,sum(invoicemoney) as invoicemoney from invoice where receipttype=2 and " +beginDateWhere+" and " +endDateWhere+ " group by deptId)as a ";
	sql=sql+" on department.deptid=a.deptid ";
	sql=sql+" left join (select invoice.deptid,sum(moneyflow.lmmoney) as lmmoney from invoice left join moneyflow on invoice.invoiceid=moneyflow.invoiceid where receipttype=2 and "+lmBeginDateWhere+" and "+lmEndDateWhere+" group by invoice.deptid)as b ";
	sql=sql+" on department.deptid=b.deptid) as n ";
	sql=sql+" on department.deptid=n.deptid ";
	sql=sql+" left join ";
	sql=sql+" (select invoice.deptid,max(invoice.invoicedate) as invoicedate,max(moneyflow.lmdate)as lmdate from invoice left join moneyflow ";
	sql=sql+" on invoice.invoiceid=moneyflow.invoiceid where "+lastBeginDateWhere+" and "+endDateWhere+" group by deptid)as o ";
	sql=sql+" on department.deptid=o.deptid ";
	sql=sql+" where "+whereClauseTemp;
	}
	else {
	System.out.println("customer");
	//上月应收应付 
	sql=sql+" select customer.customerid,customer.customername,k.lastAR,l.lastAP,m.thisAR,n.thisAP,o.invoicedate,o.lmdate from customer left join ";
	sql=sql+" (select customer.customerid,(isnull(invoicemoney,0)-isnull(lmmoney,0)) as lastAR from customer ";
	sql=sql+" left join (select customerid,sum(invoicemoney) as invoicemoney from invoice where receipttype=0 and " +lastBeginDateWhere+" and " +lastEndDateWhere+ " group by customerId)as a ";
	sql=sql+" on customer.customerid=a.customerid ";
	sql=sql+" left join (select invoice.customerid,sum(moneyflow.lmmoney) as lmmoney from invoice left join moneyflow on invoice.invoiceid=moneyflow.invoiceid where receipttype=0 and "+lmLastBeginDateWhere+" and "+lmLastEndDateWhere+" group by invoice.customerid)as b ";
	sql=sql+" on customer.customerid=b.customerid) as k ";
	sql=sql+" on customer.customerid=k.customerid ";
	sql=sql+" left join ";
	sql=sql+" (select customer.customerid,(isnull(invoicemoney,0)-isnull(lmmoney,0)) as lastAP from customer ";
	sql=sql+" left join (select customerid,sum(invoicemoney) as invoicemoney from invoice where receipttype=2 and " +lastBeginDateWhere+" and " +lastEndDateWhere+ " group by customerId)as a ";
	sql=sql+" on customer.customerid=a.customerid ";
	sql=sql+" left join (select invoice.customerid,sum(moneyflow.lmmoney) as lmmoney from invoice left join moneyflow on invoice.invoiceid=moneyflow.invoiceid where receipttype=2 and "+lmLastBeginDateWhere+" and "+lmLastEndDateWhere+" group by invoice.customerid)as b ";
	sql=sql+" on customer.customerid=b.customerid) as l ";
	sql=sql+" on customer.customerid=l.customerid ";
	//本月应收应付
	sql=sql+" left join ";
	sql=sql+" (select customer.customerid,(isnull(invoicemoney,0)-isnull(lmmoney,0)) as thisAR from customer ";
	sql=sql+" left join (select customerid,sum(invoicemoney) as invoicemoney from invoice where receipttype=0 and " +beginDateWhere+" and " +endDateWhere+ " group by customerId)as a ";
	sql=sql+" on customer.customerid=a.customerid ";
	sql=sql+" left join (select invoice.customerid,sum(moneyflow.lmmoney) as lmmoney from invoice left join moneyflow on invoice.invoiceid=moneyflow.invoiceid where receipttype=0 and "+lmBeginDateWhere+" and "+lmEndDateWhere+" group by invoice.customerid)as b ";
	sql=sql+" on customer.customerid=b.customerid) as m "; 
	sql=sql+" on customer.customerid=m.customerid ";
	sql=sql+" left join ";
	sql=sql+" (select customer.customerid,(isnull(invoicemoney,0)-isnull(lmmoney,0)) as thisAP from customer ";
	sql=sql+" left join (select customerid,sum(invoicemoney) as invoicemoney from invoice where receipttype=2 and " +beginDateWhere+" and " +endDateWhere+ " group by customerId)as a ";
	sql=sql+" on customer.customerid=a.customerid ";
	sql=sql+" left join (select invoice.customerid,sum(moneyflow.lmmoney) as lmmoney from invoice left join moneyflow on invoice.invoiceid=moneyflow.invoiceid where receipttype=2 and "+lmBeginDateWhere+" and "+lmEndDateWhere+" group by invoice.customerid)as b ";
	sql=sql+" on customer.customerid=b.customerid) as n ";
	sql=sql+" on customer.customerid=n.customerid ";
	sql=sql+" left join ";
	sql=sql+" (select invoice.customerid,max(invoice.invoicedate) as invoicedate,max(moneyflow.lmdate)as lmdate from invoice left join moneyflow ";
	sql=sql+" on invoice.invoiceid=moneyflow.invoiceid where "+lastBeginDateWhere+" and "+endDateWhere+" group by invoice.customerid)as o ";
	sql=sql+" on customer.customerid=o.customerid ";
	sql=sql+" where "+whereClauseTemp;
	}	
	 System.out.println(sql);
	 beginDateWhere="";
	 endDateWhere="";
	 lmBeginDateWhere="";
	 lmEndDateWhere="";
	 whereClauseTemp="";
     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 String getDeptId()
   {
   	return deptId;
   }
   
   public String getCustomerId()
   {
   	return customerId;
   }
   
   public String getDeptName() 
   {
      return deptName;
   }
   
   public String getEmpName() 
   {
      return empName;
   }

   public String getCustomerName() 
   {
      return customerName;
   }

   public double getLastAR() 
   {
      return lastAR;
   }

   public double getLastAP() 
   {
      return lastAP;
   }

   public double getThisAR() 
   {
      return thisAR;
   }

   public double getThisAP() 
   {
      return thisAP;
   }

   public double getBalance() 
   {
      return balance;
   }

   public Timestamp getLmDate() 
   {
      return lmDate;
   }
   
   public Timestamp getInvoiceDate() 
   {
      return invoiceDate;
   }
   
   public Timestamp getBeginDate() 
   {
      return beginDate;
   }

   public Timestamp getEndDate() 
   {
      return endDate;
   }

   public int getCountNum() 
   {
      return countNum;
   }

}