公司-外呼管理系统源代码

package find; import java.sql.*; import java.util.*; import java.util.Date; import java.text.*; import DB.*; public class tongji{ //private String EID; private Timestamp bt; private Timestamp et; private String time1; private String time2; private DBConn conn; public tongji(){//构造函数 //todo init conn = new DBConn(); } public void setbegintime(String begintime){ //this.begintime=begintime; this.bt=Timestamp.valueOf(begintime); } public void setendtime(String endtime){ //this.endtime=endtime; this.et=Timestamp.valueOf(endtime); } public ResultSet getEIDrs(){//得到EID集合 ResultSet rs; //String sql = "select total.EID,groupID from total,employee where total.EID=employee.EID and Rtime between '"+bt+"' and '"+et+"' group by EID order by employee.groupID desc"; //String sql = "select total.EID,total.YWID,changliang from total,employee,yewu where total.EID=employee.EID and total.YWID=yewu.YWID and Rtime between '"+bt+"' and '"+et+"' group by EID,YWID order by employee.groupID desc"; String sql = "select total.EID from total,employee where total.EID=employee.EID and Rtime between '"+bt+"' and '"+et+"' group by groupID,EID order by employee.groupID desc"; ///System.out.println("aaa"); rs = conn.executeQuery(sql); //rs.next(); //EIDrs=rs; return rs; } public ResultSet getYWEIDrs(){//全业务的EIDrs ResultSet rs; String sql = "select total.EID from total,employee where total.EID=employee.EID and Rtime between '"+bt+"' and '"+et+"' group by EID order by employee.groupID desc"; //String sql = "select total.EID,YWID from total,employee where total.EID=employee.EID and Rtime between '"+bt+"' and '"+et+"' group by EID,YWID order by employee.groupID desc"; rs = conn.executeQuery(sql); return rs; } public ResultSet getEIDrs(String YWID){//得到EID集合(带业务的) ResultSet rs; String sql = "select total.EID,YWID from total,employee where total.EID=employee.EID and YWID='"+YWID+"' and Rtime between '"+bt+"' and '"+et+"' group by EID,YWID order by employee.groupID desc"; rs = conn.executeQuery(sql); ///System.out.println("bbb"); return rs; } public ResultSet getgroupIDrs() throws SQLException{//设置可查询的EID集合 //YWIDconn = new DBConn(); ResultSet rs; String sql = "select distinct groupID from employee where EID in (select EID from total)"; //rs = YWIDconn.executeQuery(sql); rs = conn.executeQuery(sql); rs.next(); return rs; } public ResultSet getYWIDrs() throws SQLException{//设置可查询的YWID集合 //YWIDconn = new DBConn(); ResultSet rs; String sql = "select YWID from total where Rtime between '"+bt+"' and '"+et+"' group by YWID "; //rs = YWIDconn.executeQuery(sql); rs = conn.executeQuery(sql); rs.next(); return rs; } public ResultSet getdaters() throws SQLException{//设置可查询的日期集合 //YWIDconn = new DBConn(); ResultSet rs; String sql = "select Rtime as Rdate from total where Rtime between '"+bt+"' and '"+et+"' group by DATE(Rtime)"; //String sql = "select Rtime as Rdate,YWID from total where Rtime between '"+bt+"' and '"+et+"' group by DATE(Rtime),YWID"; //rs = YWIDconn.executeQuery(sql); rs = conn.executeQuery(sql); rs.next(); return rs; } public ResultSet getonehourrs() throws SQLException{//设置可查询的时间集合 //YWIDconn = new DBConn(); ResultSet rs; //String sql = "select Rtime as Rdate from total where Rtime between '"+bt+"' and '"+et+"' group by DATE(Rtime)"; String sql = "select Rtime as Rdate from total where Rtime between '"+bt+"' and '"+et+"' group by hour(Rtime)"; //rs = YWIDconn.executeQuery(sql); rs = conn.executeQuery(sql); rs.next(); return rs; } public ResultSet gethourrs() throws SQLException{//设置当天可查询的时间段集合 this.time1 = this.bt.toString().substring(0,10)+" 00:00:00"; this.time2 = this.bt.toString().substring(0,10)+" 23:59:59"; ResultSet rs; //String sql = "select Rtime as Rdate from total where Rtime between '"+bt+"' and '"+et+"' group by DATE(Rtime)"; String sql = "select Rtime as Rhour from total where Rtime between '"+time1+"' and '"+time2+"' group by hour(Rtime)"; rs = conn.executeQuery(sql); rs.next(); return rs; } public String getyewuname(String YWID) throws SQLException{//得到某业务的业务名 //DBConn conn = new DBConn(); ResultSet rs; String jg; String sql = "select YWname from yewu where YWID='"+YWID+"'"; rs=conn.executeQuery(sql); rs.first(); jg=rs.getString("YWname"); //conn.dropConnection(); return jg; } //-------------------按话务员的情况统计--------------- public int eidyuanshishuju(String EID,String YWID,String Rresult) throws SQLException{//取得某结果的原始数据 //DBConn conn=new DBConn(); int jg; ResultSet rs; String sql; sql="select count(RNo) from total where Rtime between '"+bt+"' and '"+et+"'"; if (YWID.equals("all")){ ; }else{ sql=sql+" and YWID='"+YWID+"'"; } if (EID.equals("all")){; }else{ sql=sql+" and EID='"+EID+"'";//呼叫数 } if (Rresult.equals("all")){; } else{ sql=sql+" and Rresult='"+Rresult+"'"; } rs=conn.executeQuery(sql); rs.next(); jg=rs.getInt(1); //conn.dropConnection(); return jg; } public int eidtongjijieguo(String EID,String YWID,String zhuangtai) throws SQLException{//取得统计结果 //DBConn conn=new DBConn(); int jg; ResultSet rs; String sql; sql="select count(RNo) from total where Rtime between '"+bt+"' and '"+et+"'"; if (YWID.equals("all")){ ; }else{ sql=sql+" and YWID='"+YWID+"'"; } if (EID.equals("all")){; }else{ sql=sql+" and EID='"+EID+"'"; } if (zhuangtai.equals("all")){//呼叫数 ; }else{ if (zhuangtai.equals("jietongshu")){//接通数 sql=sql+" and (Rresult='本号码开' or Rresult='次月开通' or Rresult='无需求' or Rresult='已经开通')"; } if (zhuangtai.equals("dinggoushu")){//订购数 sql=sql+" and (Rresult='本号码开' or Rresult='次月开通')"; } if (zhuangtai.equals("kehuifangshu")){//可回访数 sql=sql+" and (Rresult='关机' or Rresult='忙音' or Rresult='无法接通' or Rresult='无人接听' or Rresult='转移呼叫')"; } if (zhuangtai.equals("jufangshu")){//拒访数 sql=sql+" and Rresult='无需求'"; } if (zhuangtai.equals("youxiaohaomashu")){//有效号码数 sql=sql+" and not ( Rresult='空号' or Rresult='停机' or Rresult='已经开通')"; } if (zhuangtai.equals("weijietongshu")){//未接通数 sql=sql+" and (Rresult='关机' or Rresult='忙音' or Rresult='无法接通' or Rresult='无人接听' or Rresult='转移呼叫' or Rresult='空号' or Rresult='停机')"; } if (zhuangtai.equals("wuxiaohaomashu")){//无效号码数 sql=sql+" and (Rresult='已经开通' or Rresult='空号' or Rresult='停机')"; } } rs=conn.executeQuery(sql); rs.next(); jg=rs.getInt(1); //conn.dropConnection(); return jg; } public double eidjieguofenxi(String EID,String YWID,String zhuangtai)throws SQLException{//取得结果的分析数据 //DBConn conn=new DBConn(); double jg; ResultSet rsfz; ResultSet rsfm; String sqlfz; String sqlfm; sqlfz="select count(RNo) from total where Rtime between '"+bt+"' and '"+et+"'"; sqlfm="select count(RNo) from total where Rtime between '"+bt+"' and '"+et+"'"; if (YWID.equals("all")){ ; }else{ sqlfz=sqlfz+" and YWID='"+YWID+"'"; sqlfm=sqlfm+" and YWID='"+YWID+"'"; } if (EID.equals("all")){ ; }else{ sqlfz=sqlfz+" and EID='"+EID+"'"; sqlfm=sqlfm+" and EID='"+EID+"'"; } if (zhuangtai.equals("all")){//呼叫数 ; }else{ if (zhuangtai.equals("haomayouxiaolv")){//号码有效率 sqlfz=sqlfz+" and not ( Rresult='空号' or Rresult='停机' or Rresult='已经开通')"; } if (zhuangtai.equals("hujiaojietonglv")){//呼叫接通率 sqlfz=sqlfz+"and (Rresult='本号码开' or Rresult='次月开通' or Rresult='无需求' or Rre