moneylist.java

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

package src.report;

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

public class MoneyList extends ShangObject
{
	/**
客户编号
	*/
	private String customerId;
	
	/**
客户名称
	*/
	private String customerName;
	
	/**
应收
	*/
	private double shouldReceive;
	
	/**
已收
	*/ 
	private double getReceive;
	
	/**
应付
	*/
	private double shouldPay;

	/**
已付
	*/
	private double getPay;	
	   
   /**
开始日期
   */
	public Timestamp beginDate;
   
   /**
结束日期
   */
	public Timestamp endDate; 
	
	private String whereClauseTemp;  
	
	private String lmWhereClause="";

	
	public MoneyList()
	{
		numInOnePager=10;
		whereClause="";
	}
	
	public String getCustomerId()
	{
		return customerId;
	}
	
	public String getCustomerName()
	{
		return customerName;
	}
	
	public double getShouldReceive()
	{
		return shouldReceive;
	}
	
	public double getGetReceive()
	{
		return getReceive;
	}
	
	public double getShouldPay()
	{
		return shouldPay;
	}
	
	public double getGetPay()
	{
		return getPay;
	}
	
	public Timestamp getBeginDate() 
   {
      return beginDate;
   }

   public Timestamp getEndDate() 
   {
      return endDate;
   }
	
	public int loadAttr(){
       try{
		   customerId=getRsString(rs,"customerId");
		   customerName = getRsString(rs,"customerName");
	   	   shouldReceive = rs.getDouble("shouldReceive");
	   	   getReceive = rs.getDouble("getReceive");
	   	   shouldPay = rs.getDouble("shouldPay");
	   	   getPay = rs.getDouble("getPay");
	   	   return 1;
	   }catch(SQLException e){
			e.printStackTrace();
			System.out.println("query failed!");
			return -1;   
	    }		 
   }
   
   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 orWhereClause() : 编码不支持!!");
		}
	}
    	if( whereClause.equals("") == false) whereClause = whereClause + ",";
    	if(operator.equals("like"))
		whereClause = whereClause + fix + "%" + colValue + "%" + fix;
	else
		whereClause = whereClause + fix + colValue + fix;
    	return whereClause;
    }
    
    public String addWhereClause(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 + " and ";
    	if(operator.equals("like"))
		whereClause = whereClause + fix + "%" + colValue + "%" + fix;
	else if(operator.equals("<"))
		whereClause = whereClause + "(" + colName + " " + operator + " " +" dateadd(day,1," + fix + colValue + fix + ")" + ")";
		//whereClause = whereClause + "dateadd(day,1," + fix + colValue + fix + ")";
	else if (operator.equals(">="))
		whereClause = whereClause + "(" + colName + operator + fix + colValue + fix + ")";
	else
		whereClause = whereClause + fix + colValue + fix;
    	return whereClause;
    }
   
   public int conditionSetup(javax.servlet.ServletRequest request) {
	String beginDaten;
	String beginDatey;
	String beginDater;	
	String endDaten;
	String endDatey;
	String endDater;
	String customerId;
	String beginDateTemp;
	String endDateTemp;
	int yue;
	whereClause="";
	whereClause="customerId in (select customerid from customer)";
       	whereClauseTemp="customer." + whereClause;
	if ((request.getParameter("customerId")!=null)&&(!request.getParameter("customerId").equals(""))) {
		whereClause="";
		whereClauseTemp="";
		customerId=request.getParameter("customerId");
		//whereClause = orWhereClause(whereClause, "customer.customerId", "=", "'", customerId[i]);
		whereClause = orWhereClause(whereClause, "", "", "'", customerId);
		//whereClause=whereClause+ "'"+ customerId[i] +"'";
       		whereClause=whereClause+ ")";
       		whereClause="customerId in (" + whereClause;
       		whereClauseTemp="customer." + whereClause;
       	}
       	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+" 0:0:0.0");
		beginDateTemp=request.getParameter("beginDaten")+"-"+request.getParameter("beginDatey")+"-"+request.getParameter("beginDater");
		//whereClause=addWhereClause(whereClause, "v_receive.invoiceDate", ">=", "'",beginDateTemp);
   		//whereClause=addWhereClause(whereClause, "v_pay.invoiceDate", ">=", "'",beginDateTemp);
   		whereClause=addWhereClause(whereClause, "invoiceDate", ">=", "'",beginDateTemp);
   		lmWhereClause=addWhereClause(lmWhereClause, "lmDate", ">=", "'",beginDateTemp);
   	} 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");		
		beginDateTemp=getStringDate(getCurDate(),1)+"-"+beginDatey+"-"+getStringDate(getCurDate(),3);
		//whereClause=addWhereClause(whereClause, "v_receive.invoiceDate", ">=", "'",beginDateTemp);
   		//whereClause=addWhereClause(whereClause, "v_pay.invoiceDate", ">=", "'",beginDateTemp);	
   		whereClause=addWhereClause(whereClause, "invoiceDate", ">=", "'",beginDateTemp);	
   		lmWhereClause=addWhereClause(lmWhereClause, "lmDate", ">=", "'",beginDateTemp);	
   	}
	
	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+" 0:0:0.0");
		endDateTemp=request.getParameter("endDaten")+"-"+request.getParameter("endDatey")+"-"+request.getParameter("endDater");
		//whereClause=addWhereClause(whereClause, "v_receive.invoiceDate", "<", "'",endDateTemp);
   		//whereClause=addWhereClause(whereClause, "v_pay.invoiceDate", "<", "'",endDateTemp);
   		whereClause=addWhereClause(whereClause, "invoiceDate", "<", "'",endDateTemp);
   		lmWhereClause=addWhereClause(lmWhereClause, "lmDate", "<", "'",endDateTemp);
   	} else {
		endDate = getCurDate();	
		endDateTemp=getStringDate(getCurDate(),1)+"-"+getStringDate(getCurDate(),2)+"-"+getStringDate(getCurDate(),3);
		//whereClause=addWhereClause(whereClause, "v_receive.invoiceDate", "<", "'",endDateTemp);
   		//whereClause=addWhereClause(whereClause, "v_pay.invoiceDate", "<", "'",endDateTemp);
   		whereClause=addWhereClause(whereClause, "invoiceDate", "<", "'",endDateTemp);
   		lmWhereClause=addWhereClause(lmWhereClause, "lmDate", "<", "'",endDateTemp);
   	}
	moneyList();
	//System.out.println(whereClause);
	return 0;		
   }
   
   public int moneyList() {
     int i;
	 String sql;
	 /*
	 sql="select customer.customerId as customerId,customer.customerName as customerName,p.shouldReceive,";
	 sql=sql + " p.getReceive,q.shouldPay,q.getPay from customer";
	 sql=sql + " left join (select customerid,sum(invoicemoney) as shouldReceive,sum(lmmoney) as getReceive from v_receive where "+ whereClause +" group by customerId)as p ";
	 sql=sql + " on customer.customerid=p.customerId ";
	 sql=sql + " left join (select customerid,sum(invoicemoney) as shouldPay,sum(lmmoney) as getPay from v_pay where " + whereClause + " group by customerId )as q ";
	 sql=sql + " on customer.customerid=q.customerId where " + whereClauseTemp;*/
	 sql="select customer.customerId,customerName,a.shouldreceive,a.getreceive,b.shouldpay,b.getpay from customer ";
	 sql=sql + "left join";
	 sql=sql + "(select customer.customerid as customerid,p.shouldreceive,q.getreceive from customer ";
	 sql=sql + "left join ";
	 sql=sql + "(select customerid,sum(invoicemoney) as shouldreceive from invoice ";
	 sql=sql + "where " + whereClause;
	 sql=sql + " and receipttype=0 group by customerid) as p on customer.customerid=p.customerid ";	
	 sql=sql + "left join (select customerid,sum(lmmoney) as getreceive from invoice ";
	 sql=sql + "left join moneyflow on invoice.invoiceid=moneyflow.invoiceid ";
	 sql=sql + "where " + lmWhereClause;
	 sql=sql + "and receipttype=0 group by customerid) as q on customer.customerid=q.customerid) as a ";
	 sql=sql + "on customer.customerId=a.customerId ";
	 sql=sql + "left join ";
	 sql=sql + "(select customer.customerid as customerid,p.shouldpay,q.getpay from customer ";
	 sql=sql + "left join ";
	 sql=sql + "(select customerid,sum(invoicemoney) as shouldpay from invoice ";
	 sql=sql + "where " + whereClause;
	 sql=sql + "and receipttype=2 group by customerid) as p on customer.customerid=p.customerid ";
	 sql=sql + "left join (select customerid,sum(lmmoney) as getpay from invoice ";
	 sql=sql + "left join moneyflow on invoice.invoiceid=moneyflow.invoiceid ";
	 sql=sql + "where " + lmWhereClause;
	 sql=sql + "and receipttype=2 group by customerid) as q on customer.customerid=q.customerid) as b ";
	 sql=sql + "on customer.customerId=b.customerId where " + whereClauseTemp;

	 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 String getWhereClause(javax.servlet.ServletRequest request)
   {
   	String beginDate;
   	String endDate;
   	if (((request.getParameter("beginDaten")!=null)&&(!request.getParameter("beginDaten").equals("")))&&((request.getParameter("beginDatey")!=null)&&(!request.getParameter("beginDatey").equals("")))&&((request.getParameter("beginDater")!=null)&&(!request.getParameter("beginDatenr").equals(""))))
   	{
   		beginDate=request.getParameter("beginDaten")+"-"+request.getParameter("beginDatey")+"-"+request.getParameter("beginDater");
   		whereClause=addWhereClause(whereClause, "v_receive.invoiceDate", ">=", "'",beginDate);
   		whereClause=addWhereClause(whereClause, "v_pay.invoiceDate", ">=", "'",beginDate);
   	}
   	if (((request.getParameter("endDaten")!=null)&&(!request.getParameter("endDaten").equals("")))&&((request.getParameter("endDatey")!=null)&&(!request.getParameter("endDatey").equals("")))&&((request.getParameter("endDater")!=null)&&(!request.getParameter("endDater").equals(""))))
   	{
   		endDate=request.getParameter("endDaten")+"-"+request.getParameter("endDatey")+"-"+request.getParameter("endDater");
   		whereClause=addWhereClause(whereClause, "v_receive.invoiceDate", "<", "'",endDate);
   		whereClause=addWhereClause(whereClause, "v_pay.invoiceDate", "<", "'",endDate);
   	}
   	return whereClause;
   }
  
   
}