package com.uniwin.webkey.model.smp;
import java.util.HashMap;
import java.util.Map;
import java.util.Vector;
import java.sql.*;
import com.uniwin.util.StringUtil;
import com.uniwin.webkey.itf.base.WKResultList;
import com.uniwin.webkey.itf.smp.ISmpStat;
import com.uniwin.webkey.model.util.DAO;
import com.uniwin.webkey.model.util.PubFun;
public class SmpStat implements ISmpStat {
static DAO dao = DAO.getDAOInstance();
Connection conn=null;
Statement sm=null;
ResultSet rs=null;
//定义全省11个地市数组,包括河北省,总数
private static final String[][] area = {{ "SV_AREA0", "00" }, { "SV_AREA1", "01" }
, { "SV_AREA2", "02" }, { "SV_AREA3", "03" }, { "SV_AREA4", "04" }
, { "SV_AREA5", "05" }, { "SV_AREA6", "06" } , { "SV_AREA7", "07" }
, { "SV_AREA8", "08" } ,{ "SV_AREA9", "09" } , { "SV_AREA10", "10" }
, { "SV_AREA11", "11" },{ "SV_AREATOTAL", "-1" }};
private static final String[] areas ={"00","01","02","03","04","05","06","07","08","09","10","11","12"};
// 统计所有符合条件的已发送的而信息列表
public int getList(String fromDate, String toDate, String sCondition,
WKResultList list) {
String sql = "";
sql = "SELECT "
+ list.getBeanFactory().getSql()
+ " ,SM_T_TYPE.ST_NAME FROM SM_T_INFO ,SM_T_TYPE WHERE SM_T_TYPE.ST_ID=SM_T_INFO.ST_ID "
+ "AND (SM_T_INFO.SI_STATUS =34 OR SM_T_INFO.SI_STATUS=35) AND SI_SNDTIME >='"
+ fromDate + "' AND SI_SNDTIME<='" + toDate
+ "' ORDER BY SI_SNDTIME DESC";
if (!sCondition.equals("")) {
sql += sCondition;
}
if (dao.select(sql, StringUtil.split(list.getBeanFactory().getSql(),
",").length + 1, list) == 1) {
if (list.getMydata() != null && list.dataSize() != 0) {
PubFun.processToResultList(list);
}
return 1;
}
return -1;
}
// 统计所有符合条件的接收信息列表
public Vector getTakeOverList(String fromDate, String toDate,
String sCondition) {
Vector veclist = new Vector();
String sql = "";
String tableName = "SM_T_SNDLOG" + fromDate;// 由时间得到日志表名
sql = "select * from " + tableName;
boolean flag = false;
try {
conn=dao.getConnection();
sm=conn.createStatement();
rs=sm.executeQuery(sql);
flag = true;
} catch (Exception e) {
flag = false;
}finally
{
dao.freeConnection(conn, sm, rs);
}
if (flag) {
// 从type表查到符合条件的ST_CTABLE 得到表名称
String sql1 = "SELECT DISTINCT(ST_CTABLE) FROM SM_T_TYPE ,"
+ tableName + " WHERE " + tableName
+ ".ST_ID=SM_T_TYPE.ST_ID";
Vector vecTable = dao.select(sql1, 1);
// 查询ST_CTABLE 表名称内的表 得到结果集
for (int i = 0; i < vecTable.size(); i++) {
// 从type表查到符合条件的ST_CTABLE 得到表名称
String svTypeTab = vecTable.get(i).toString();
sql = "SELECT " + tableName
+ ".SG_TMPOINT,SM_T_INFO.SI_CONTENT," + svTypeTab
+ ".SS_MOBILE" + " FROM " + tableName + ",SM_T_INFO,"
+ svTypeTab + " WHERE " + tableName + ".SS_ID="
+ svTypeTab + ".SS_ID" + " AND " + tableName
+ ".SI_ID=SM_T_INFO.SI_ID ";
if (!sCondition.equals("")) {
sql += " AND " + svTypeTab + ".SS_MOBILE='" + sCondition
+ "'";
}
veclist = dao.selectEx(sql, 0);
}
} else {
veclist = null;
}
return veclist;
}
public Vector getAreaCount(String fromDate,String toDate,String ssStatus,String ssMoble)
{
//客户统计
String sql = "";
Vector vec = new Vector();
Map map = new HashMap();
for(int i=0 ;i
{
//分别统计客户信息
if(areas[i].equals("12"))
{
sql = " SELECT COUNT(DISTINCT(SS_MOBILE)) COUNT_AREA FROM SM_T_SVALL "
+ getAllSql(fromDate,toDate,ssStatus,ssMoble);
}
else
{
sql = " SELECT COUNT(DISTINCT(SS_MOBILE)) COUNT_AREA FROM SM_T_SVALL "
+"WHERE SM_T_SVALL.SS_AREA IN('"+areas[i]+"') "
+ getSsStatusSql(ssStatus)
+ getSsMobleSql(ssMoble)
+ getFromDateSql(fromDate)
+ getToDateSql(toDate);
}
Vector vect = dao.selectEx(sql, 0);
if(vect.size()!=0 && vect!=null)
{
Map mapt = (Map) vect.get(0);
map.put("SV_AREA"+areas[i], mapt.get("COUNT_AREA"));
}
else
{
map.put("SV_AREA"+areas[i], "");
}
}
vec.add(map);
return vec;
}
private String getAreaSql(String area)
{
return area.equals("-1") ? "" : " SM_T_SVALL.SS_AREA IN('"
+ area + "') ";
}
private String getFromDateSql(String fromDate)
{
return fromDate.equals("")?"":" AND SS_STIME>='"+fromDate+"'";
}
private String getToDateSql(String toDate)
{
return toDate.equals("")?"":" AND SS_STIME<='"+ toDate+"'";
}
private String getSsStatusSql(String ssStatus)
{
return ssStatus.equals("")?"":" AND SS_STATUS='"+ssStatus+"'";
}
private String getSsMobleSql(String ssMoble)
{
return ssMoble.equals("")?"":" AND SS_MOBILE LIKE'%"+ssMoble+"%'";
}
private String getAllSql(String fromDate,String toDate,String ssStatus,String ssMoble)
{
//检查参数是否为空，拼sql条件
String sCondition = "";
if(!ssStatus.equals(""))
{
sCondition = " WHERE SS_STATUS='"+ssStatus+"'" + getSsMobleSql(ssMoble)
+getFromDateSql(fromDate)+getToDateSql(toDate);
}
if(!ssMoble.equals(""))
{
sCondition = " WHERE SS_MOBILE LIKE'%"+ssMoble+"%' "
+getSsStatusSql(ssStatus)+getFromDateSql(fromDate)+getToDateSql(toDate);
}
if(!fromDate.equals(""))
{
sCondition = " WHERE SS_STIME>='"+fromDate+"' "
+ getToDateSql(toDate)+getSsStatusSql(ssStatus)+getSsMobleSql(ssMoble);
}
if(!toDate.equals(""))
{
sCondition = " WHERE SS_STIME<='"+ toDate+"' "
+getFromDateSql(fromDate)+getSsStatusSql(ssStatus)+getSsMobleSql(ssMoble);
}
return sCondition;
}
//业务统计
public int areaOnType(String fromDate, String toDate,
String ssStatus,String smType,String ssMoble,WKResultList list)
{
//统计11个地市的业务量
String sql = "SELECT ST_ID,ST_NAME,SV_AREA0,SV_AREA1,SV_AREA2,"
+ "SV_AREA3,SV_AREA4,SV_AREA5,SV_AREA6,SV_AREA7,SV_AREA8,"
+ "SV_AREA9,SV_AREA10,SV_AREA11" +
",SV_AREATOTAL " +
" FROM SM_T_TYPE ";
for (int i = 0; i < area.length; i++)
{
sql += getAreaOnTypeSql(fromDate, toDate, area[i][0],
area[i][1],ssStatus,ssMoble);
}
if(smType.equals("0") || smType.equals(""))
{
sql += " WHERE ST_DISTFLAG='1' group BY ST_ID ";
}
else
{
sql += " WHERE ST_DISTFLAG='1' AND ST_ID="+smType+" group BY ST_ID ";
}
return dao.selectEx(sql, list);
}
private String getAreaOnTypeSql(String fromDate, String toDate,
String fieldName, String area,String ssStatus,String ssMoble)
{
//拼河北省,11个地市,总数的sql语句
String sql = "";
if(fieldName.equals("SV_AREATOTAL"))
{
sql = " LEFT JOIN " + "(SELECT COUNT(ST_ID) " + fieldName + ",ST_ID "
+ fieldName +"_SV FROM SM_T_SVALL "
+ getAllSql(fromDate,toDate,ssStatus,ssMoble)
+ " GROUP BY ST_ID) SM_V_"
+ fieldName + " " + "ON SM_V_" + fieldName + "." + fieldName
+ "_SV=SM_T_TYPE.ST_ID";
}
else
{
sql = " LEFT JOIN " + "(SELECT COUNT(ST_ID) " + fieldName + ",ST_ID "
+ fieldName +"_SV FROM SM_T_SVALL WHERE "
+ getAreaSql(area)
+ getSsStatusSql(ssStatus)
+ getSsMobleSql(ssMoble)
+ getFromDateSql(fromDate)
+ getToDateSql(toDate)
+ " GROUP BY ST_ID) SM_V_"
+ fieldName + " " + "ON SM_V_" + fieldName + "." + fieldName
+ "_SV=SM_T_TYPE.ST_ID";
}
return sql;
}
//业务统计新
public Vector getAreaTypeCount(String fromDate,String toDate,String ssStatus,
String ssMoble,String smType,WKResultList list)
{
//客户统计
String sql = "";
Vector vec = new Vector();
Map map = new HashMap();
for(int i=0 ;i {
//分别统计客户信息
// if(areas[i].equals("12"))
// {
// sql = " SELECT COUNT(ST_ID) COUNT_AREA FROM SM_T_SVALL "
// + getAllSql(fromDate,toDate,ssStatus,ssMoble);
// }
// else
// {
if(smType.equals("") || smType.equals("0"))
{
sql = " SELECT COUNT(ST_ID) COUNT_AREA,ST_NAME ST_NAME3,SS_AREA SS_AREA3 FROM SM_T_SVALL "
+"WHERE SM_T_SVALL.SS_AREA IN('"+areas[i]+"') "
+ getSsStatusSql(ssStatus)
+ getSsMobleSql(ssMoble)
+ getFromDateSql(fromDate)
+ getToDateSql(toDate)
+ " group by st_name";
}
else
{
sql = " SELECT COUNT(ST_ID) COUNT_AREA,ST_NAME ST_NAME3,SS_AREA SS_AREA3 FROM SM_T_SVALL "
+"WHERE ST_ID="+smType+" AND SM_T_SVALL.SS_AREA IN('"+areas[i]+"') "
+ getSsStatusSql(ssStatus)
+ getSsMobleSql(ssMoble)
+ getFromDateSql(fromDate)
+ getToDateSql(toDate)
+ " group by st_name";
}
// }
Vector vect = dao.selectEx(sql, 0);
Map mapt = new HashMap();
if(vect.size()!=0 && vect!=null)
{
mapt = (Map) vect.get(0);
map.put("ST_NAME", mapt.get("ST_NAME3"));
map.put("SV_AREA"+areas[i], mapt.get("COUNT_AREA"));
}
else
{
map.put("ST_NAME", mapt.get("ST_NAME3"));
map.put("SV_AREA"+areas[i], "");
}
vec.add(map);
}
System.out.println(vec);
return vec;
}
private static final String[][] area2 = new String[100][12];
//新业务
public static Vector getYw()
{
String sql = "SELECT COUNT(*) COUNT_AREA,SS_AREA,ST_NAME FROM SM_T_SVALL GROUP BY ST_ID,SS_AREA ORDER BY ST_ID ";
Vector vec = dao.selectEx(sql, 0);
System.out.println(vec);
for(int k=0;k {
Map map = (Map) vec.get(k);
}
for(int i=0;i {
for(int j=0;j<11;j++)
{
}
}
return dao.selectEx(sql, 0);
}
public Vector getCountCity()
{
Vector list = new Vector(); //用于返回值
String tid = "0"; //循环中上一次的TID值
int[] count = {0,0,0,0,0,0,0,0,0,0,0};
String sql ="SELECT ST_ID,ST_NAME,SS_AREA,COUNT(*) SS_VAL FROM SM_T_SVALL GROUP BY ST_ID,SS_AREA ORDER BY ST_ID,SS_AREA";
Vector data = dao.selectEx(sql, 0);
System.out.println(data.size());
for(int i=0;i {
HashMap tmp = (HashMap)data.get(i);
//是否是同一类型数组
if (!tid.equals(tmp.get("ST_ID").toString()))
{
tid = tmp.get("ST_ID").toString();
//不同于上一类型，取所有同类的数据
// boolean flag = true;
// int j = 0;
HashMap city = new HashMap();
int[] cv = {0,0,0,0,0,0,0,0,0,0,0,0};
city.put("ST_NAME", tmp.get("ST_NAME").toString());
while(true)
{
HashMap tmp2 = (HashMap)data.get(i);
if (tid.equals(tmp2.get("ST_ID")))
{
cv[Integer.parseInt(tmp2.get("SS_AREA").toString())] = Integer.parseInt(tmp2.get("SS_VAL").toString());
i++;
if (i==data.size())
break;
}
else
{
if (i==data.size())
break;
// flag = false;
i--;
break;
}
}
city.put("ST_VAL", cv);
list.add(city);
}
}
// System.out.println(list.size());
return list ;
}}
阅读(1849) | 评论(0) | 转发(0) |
