📄 daodisplay.java.svn-base
字号:
package first;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;
import levin.util.*;
public class DAODisplay {
Connection conn;
public DAODisplay() {
}
//java.sql.Date day = new
// Select username ‘User Name’，
// DATEADD(mm，1，firstvisit_date) ‘Registration Expires’
// FROM registration_table //select to_char(to_date(a,'yyyymmdd')+'30','yyyymmdd') from dual;
public Vector queryAuditorFollow() {
Display dis=null;
DbAccess dc=null;
try {
dc= new DbAccess();
}catch(Exception e) {}
PreparedStatement ps =null;
ResultSet rs= null;
Vector vt = new Vector();
//java.sql.Date testDate = (java.sql.Date)now;
// now.setDate(now.getDay()+7);
java.util.Calendar test = Calendar.getInstance(java.util.Locale.CHINA);
java.sql.Date now = new java.sql.Date(System.currentTimeMillis());
test.setTime(now);
now.getTime();
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd",
Locale.SIMPLIFIED_CHINESE);
String timestamp = sdf.format(now);
test.add(Calendar.DAY_OF_WEEK, -7);
// now = test.getTime();
java.sql.Date testDate2 = new java.sql.Date(test.getTimeInMillis());
String timestamp2 = sdf.format(testDate2);
try {
conn=dc.getConnection();
String sql="select Ltrim(Rtrim(e.chCommitName)) as name,count(Ltrim(Rtrim(e.chCommitName)))as countCi,count(distinct e.ifollowid)as countQi from ent_plan_following e " +
"where dtTrackingDate between ? and ?"
+"group by e.chCommitName order by countCi";
//String sql="select e.chCommitName as name,count(e.chCommitName)as countCi,count(distinct e.ifollowid)as countQi" +
// " from ent_plan_following e where dtTrackingDate between to_date(?,'yyyy-MM-dd') and " + "to_date(DATEADD(mm，1，firstvisit_date),'yyyym-MM-dd')"+
// " group by e.chCommitName order by countCi";
ps = conn.prepareStatement(sql);
ps.setDate(1, testDate2);
ps.setDate(2, now);
//System.out.println("qian:********"+testDate2+"hou********:"+now);
// System.out.println("***:"+sql);
rs = ps.executeQuery();
while(rs.next()) {
dis=Display.getDisplay();
String sr = rs.getString("name");
int countCi=rs.getInt("countCi");
int countQi = rs.getInt("countQi");
dis.setAuditorName(sr);
//System.out.println("+++++++"+sr);
dis.setCountCi(countCi);
dis.setCountQi(countQi);
vt.add(dis);
}
rs.close();
ps.close();
//conn.close();
}catch(Exception e) {
e.printStackTrace();
try {
if(rs!=null) {
rs.close();
}
if(ps!=null) {
ps.close();
}
// if(conn!=null) {
// dc.freeConnection();
// conn.close();
// }
dc.freeConnection();
}catch(Exception e1) {
}
}finally {
try {
if(rs!=null) {
rs.close();
}
if(ps!=null) {
ps.close();
}
// if(conn!=null) {
// dc.freeConnection();
// conn.close();
// }
// if (dc.autoCommit)
dc.freeConnection();
}catch(Exception e1) {
}
}
return vt;
}
public Vector queryEnterCount() {
EnterQianBean ent=null;
DbAccess dc=null;
try {
dc= new DbAccess();
}catch(Exception e) {}
PreparedStatement ps =null;
ResultSet rs= null;
Vector vt = new Vector();
//java.sql.Date testDate = (java.sql.Date)now;
// now.setDate(now.getDay()+7);
java.util.Calendar test = Calendar.getInstance(java.util.Locale.CHINA);
java.sql.Date now = new java.sql.Date(System.currentTimeMillis());
test.setTime(now);
now.getTime();
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd",
Locale.SIMPLIFIED_CHINESE);
String timestamp = sdf.format(now);
test.add(Calendar.DAY_OF_WEEK, -7);
// now = test.getTime();
java.sql.Date testDate2 = new java.sql.Date(test.getTimeInMillis());
String timestamp2 = sdf.format(testDate2);
try {
conn=dc.getConnection();
// select Ltrim(Rtrim(c.chStation)) as chStation,Ltrim(Rtrim(c.chCommitUserName))as chCommitUserName,
// count(chCommitUserName) as count from ent_customer_enterprise c
// where chIsAudited like '%1%' and c.dtcommittime
// between to_date($P{startTime},'yyyy-MM-dd') and to_date($P{endTime},'yyyy-MM-dd')
// group by Ltrim(Rtrim(c.chStation)),Ltrim(Rtrim(c.chCommitUserName))
String sql="select Ltrim(Rtrim(c.chCommitUserName)) as name,count(Ltrim(Rtrim(c.chCommitUserName))) as count from ent_customer_enterprise c" +
" where c.chIsAudited like '%1%' and c.dtcommittime between ? and ? "+"group by c.chCommitUserName order by count";
//String sql="select e.chCommitName as name,count(e.chCommitName)as countCi,count(distinct e.ifollowid)as countQi" +
// " from ent_plan_following e where dtTrackingDate between to_date(?,'yyyy-MM-dd') and " + "to_date(DATEADD(mm，1，firstvisit_date),'yyyym-MM-dd')"+
// " group by e.chCommitName order by countCi";
ps = conn.prepareStatement(sql);
ps.setDate(1, testDate2);
ps.setDate(2, now);
//System.out.println("qian:********"+testDate2+"hou********:"+now);
rs = ps.executeQuery();
// System.out.println("++++2+++");
while(rs.next()) {
ent=EnterQianBean.getEnterQianBean();
String sr = rs.getString("name");
int countCi=rs.getInt("count");
ent.setAuditorName(sr);
// System.out.println("+++++++"+sr);
ent.setCount(countCi);
vt.add(ent);
}
rs.close();
ps.close();
// conn.close();
}catch(Exception e) {
e.printStackTrace();
try {
if(rs!=null) {
rs.close();
}
if(ps!=null) {
ps.close();
}
// if(conn!=null) {
// dc.freeConnection();
// conn.close();
// }
dc.freeConnection();
}catch(Exception e1) {
}
}finally {
try {
if(rs!=null) {
rs.close();
}
if(ps!=null) {
ps.close();
}
// if(conn!=null) {
// dc.freeConnection();
// conn.close();
// }
dc.freeConnection();
}catch(Exception e1) {
}
}
return vt;
}
public Vector queryStationFollow() {
StationFollowBean dis=null;
DbAccess dc=null;
try {
dc= new DbAccess();
}catch(Exception e) {}
PreparedStatement ps =null;
ResultSet rs= null;
Vector vt = new Vector();
//java.sql.Date testDate = (java.sql.Date)now;
// now.setDate(now.getDay()+7);
java.util.Calendar test = Calendar.getInstance(java.util.Locale.CHINA);
java.sql.Date now = new java.sql.Date(System.currentTimeMillis());
test.setTime(now);
now.getTime();
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd",
Locale.SIMPLIFIED_CHINESE);
String timestamp = sdf.format(now);
test.add(Calendar.DAY_OF_WEEK, -7);
// now = test.getTime();
java.sql.Date testDate2 = new java.sql.Date(test.getTimeInMillis());
String timestamp2 = sdf.format(testDate2);
try {
conn=dc.getConnection();
String sql="select Ltrim(Rtrim(e.chStation)) as name ,count(Ltrim(Rtrim(e.chStation))) as countCi,count(distinct e.ifollowid) as countQi from ent_plan_following e " +
"where e.dtTrackingDate between ? and ?"
+"group by e.chStation order by countCi";
//String sql="select e.chCommitName as name,count(e.chCommitName)as countCi,count(distinct e.ifollowid)as countQi" +
// " from ent_plan_following e where dtTrackingDate between to_date(?,'yyyy-MM-dd') and " + "to_date(DATEADD(mm，1，firstvisit_date),'yyyym-MM-dd')"+
// " group by e.chCommitName order by countCi";
ps = conn.prepareStatement(sql);
ps.setDate(1, testDate2);
ps.setDate(2, now);
//System.out.println("qian:********"+testDate2+"hou********:"+now);
// System.out.println("***:"+sql);
rs = ps.executeQuery();
while(rs.next()) {
dis=StationFollowBean.getStationFollowBean();
String sr = rs.getString("name");
int countCi=rs.getInt("countCi");
int countQi = rs.getInt("countQi");
dis.setStation(sr);
//System.out.println("+++++++"+sr+"--------"+countCi);
dis.setCountCi(countCi);
dis.setCountQi(countQi);
vt.add(dis);
}
rs.close();
ps.close();
//conn.close();
}catch(Exception e) {
e.printStackTrace();
try {
if(rs!=null) {
rs.close();
}
if(ps!=null) {
ps.close();
}
// if(conn!=null) {
// dc.freeConnection();
// conn.close();
// }
dc.freeConnection();
}catch(Exception e1) {
}
}finally {
try {
if(rs!=null) {
rs.close();
}
if(ps!=null) {
ps.close();
}
// if(conn!=null) {
// conn.close();
// dc.freeConnection();
// }
dc.freeConnection();
}catch(Exception e1) {
}
}
return vt;
}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -