?? moneylist.java
字號:
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;
}
}
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -