📄 querytime.java
字号:
package com.gzrealmap.oa;
import java.util.StringTokenizer ;
import java.util.Vector ;
import java.sql.ResultSet;
public class QueryTime
{
public static String queryTimeSentence="1=0";
public static String getqueryTime(String field,String selectTime) throws Exception{
try
{
if (selectTime!=null){
if (selectTime.equals("")){
queryTimeSentence="1=1";
}else if(selectTime.equals("jintian")){
//今天
queryTimeSentence=field +"= DATE_FORMAT(CURDATE(), '%Y-%m-%d')";
}else if(selectTime.equals("zuotian")){
//昨天
queryTimeSentence=field +"= DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-%d'),INTERVAL -1 DAY)";
}else if(selectTime.equals("qiantian")){
//前天
queryTimeSentence=field +"= DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-%d'),INTERVAL -2 DAY)";
}else if(selectTime.equals("benyue")){
//本月
queryTimeSentence=field +"> DATE_ADD(CURDATE(),INTERVAL -DAYOFMONTH(CURDATE()) DAY) and "+field+"<= DATE_ADD(DATE_ADD(CURDATE(),INTERVAL -DAYOFMONTH(CURDATE()) DAY),INTERVAL 1 MONTH)";
}else if(selectTime.equals("shangyue")){
//上月
queryTimeSentence=field +" > DATE_ADD(DATE_ADD(CURDATE(),INTERVAL -DAYOFMONTH(CURDATE()) DAY),INTERVAL -1 MONTH) and "+field+" <= DATE_ADD(CURDATE(),INTERVAL -DAYOFMONTH(CURDATE()) DAY)";
}else if(selectTime.equals("qianyue")){
//前月
queryTimeSentence=field +" > DATE_ADD(DATE_ADD(CURDATE(),INTERVAL -DAYOFMONTH(CURDATE()) DAY),INTERVAL -2 MONTH) and "+field+" <= DATE_ADD(DATE_ADD(CURDATE(),INTERVAL -DAYOFMONTH(CURDATE()) DAY),INTERVAL -1 MONTH)";
}else if(selectTime.equals("jinnian")){
//今年
queryTimeSentence="year("+field+") = year(CURDATE())";
}else if(selectTime.equals("qunian")){
//去年
queryTimeSentence="year("+field+") = year(CURDATE())-1";
}else if(selectTime.equals("qiannian")){
//前年
queryTimeSentence="year("+field+") = year(CURDATE())-2";
}else if(selectTime.equals("quanbu")){
//全部
queryTimeSentence="1=1";
}
}
}
catch (Exception exc)
{
throw new Exception ("输入的时间不正确，请重新输入！exception in QueryTime.getqueryTime();"+exc.getMessage());
}
return queryTimeSentence;
}
public static String getqueryTime(String field,String fromTime,String toTime) throws Exception{
try
{
if (fromTime!=null&&toTime!=null){
if (fromTime.equals("")||toTime.equals("")){
queryTimeSentence="1=1";
}
else{
queryTimeSentence=field + ">='" + fromTime + "' and " + field + "<='" + toTime+ "'";
}
}
}
catch (Exception exc)
{
throw new Exception ("输入的时间不正确，请重新输入！exception in QueryTime.getqueryTime(String field,String fromTime,String toTime);"+exc.getMessage());
}
return queryTimeSentence;
}
public static String getReportTime(String selectTime) throws Exception{
String reportDate="";
try
{
String sql="SELECT DISTINCT ";
if (selectTime!=null){
if(selectTime.equals("jintian")){
//今天
sql=sql +" DATE_FORMAT(CURDATE(), '%Y-%m-%d') ";
}else if(selectTime.equals("zuotian")){
//昨天
sql=sql +" DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-%d'),INTERVAL -1 DAY) ";
}else if(selectTime.equals("qiantian")){
//前天
sql=sql +" DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-%d'),INTERVAL -2 DAY) ";
}else if(selectTime.equals("benyue")){
//本月
sql=sql +"DATE_ADD(DATE_ADD(CURDATE(),INTERVAL -DAYOFMONTH(CURDATE()) DAY),INTERVAL +1 MONTH)";//" DATE_FORMAT(varchar(7), CURDATE(), 126) ";
}else if(selectTime.equals("shangyue")){
//上月
sql=sql +"DATE_ADD(DATE_ADD(CURDATE(),INTERVAL -DAYOFMONTH(CURDATE()) DAY),INTERVAL -0 MONTH)";//" DATE_FORMAT(varchar(7), DATE_ADD(month, - 1, DATE_ADD(day, - DAY(CURDATE()),CURDATE()) + 1), 126) ";
}else if(selectTime.equals("qianyue")){
//前月
sql=sql +"DATE_ADD(DATE_ADD(CURDATE(),INTERVAL -DAYOFMONTH(CURDATE()) DAY),INTERVAL -1 MONTH)";//" DATE_FORMAT(varchar(7), DATE_ADD(month, - 2, DATE_ADD(day, - DAY(CURDATE()),CURDATE()) + 1), 126) ";
}else if(selectTime.equals("jinnian")){
//今年
sql=sql +" year(CURDATE()) ";
}else if(selectTime.equals("qunian")){
//去年
sql=sql +" year(CURDATE())-1 ";
}else if(selectTime.equals("qiannian")){
//前年
sql=sql +" year(CURDATE())-2 ";
}else if(selectTime.equals("quanbu")){
//全部
sql=sql +" '全部' ";
}
sql =sql +" AS reportDate ";
com.gzrealmap.lib.jdbc.JDBCUtil db =com.gzrealmap.lib.jdbc.JDBCUtil.getInstance();
db.connect();
ResultSet rs=db.query(sql);
rs.next();
reportDate =rs.getString("reportDate");
rs.close();
db.disconnect();
Vector v =new Vector();
StringTokenizer st=new StringTokenizer(reportDate,"-");
int i =st.countTokens();
while (st.hasMoreTokens()) {
v.add(st.nextToken());
}
switch (i)
{
case 1:
if (!((String)v.get(0)).equals("全部"))
{
reportDate="";
reportDate=(String)v.get(0)+"年";
}
break;
case 2:
reportDate="";
reportDate=(String)v.get(0)+"年"+(String)v.get(1)+"月";
break;
case 3:
reportDate="";
reportDate=(String)v.get(0)+"年"+(String)v.get(1)+"月"+(String)v.get(2)+"日";
break;
}
}
return reportDate;
}
catch (Exception exc)
{
throw new Exception ("输入的时间不正确，请重新输入！exception in QueryTime.getReportTime(String selectTime);"+exc.getMessage());
}
}
public static String getReportTime(String fromTime,String toTime) throws Exception{
String reportDate="";
try
{
Vector v =new Vector();
StringTokenizer st=new StringTokenizer(fromTime,"-");
while (st.hasMoreTokens()) {
v.add(st.nextToken());
}
fromTime="";
fromTime=(String)v.get(0)+"年"+(String)v.get(1)+"月"+(String)v.get(2)+"日";
Vector vv =new Vector();
StringTokenizer stst=new StringTokenizer(toTime,"-");
while (stst.hasMoreTokens()) {
vv.add(stst.nextToken());
}
toTime="";
toTime=(String)vv.get(0)+"年"+(String)vv.get(1)+"月"+(String)vv.get(2)+"日";
if(!fromTime.equals(toTime)){
reportDate=fromTime+" 到 "+toTime;
}else{
reportDate=fromTime;
}
return reportDate;
}
catch (Exception exc)
{
throw new Exception ("输入的时间不正确，请重新输入！exception in QueryTime.getReportTime(String fromTime,String toTime);"+exc.getMessage());
}
}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -