intergralsearchimp.java

自己制作的联通CRM,支持客户分类,管理,升级,积分管理等等..

package com.jn0801.intergral.detail;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import oracle.jdbc.OracleTypes;

import com.jn0801.intergral.Intergralinfo;
import com.jn0801.tools.DBConnection;

/**
 * 积分明细查询的实现类
 * @author jzh
 *
 */
public class IntergralSearchImp implements IntergralSearchInf {

	/**
	 * 按月份查询积分明细
	 * @param phone 电话号码
	 * @param time_start 起始时间
	 * @param time_end  结束时间
	 * @param intergral_start 起始积分
	 * @param intergral_end  结束积分
	 * @return  积分的集合类
	 */
	public List<Intergralinfo> searchIntergralByMonth(String phone,
			String time_start, String time_end, int intergral_start,
			int intergral_end) {
		
		List<Intergralinfo> list = new ArrayList<Intergralinfo>();
		
		Connection conn = null;
		CallableStatement csmt = null;
		ResultSet rs = null;
		
		String sql = "select phone,recordtime,phonefree,consumeintergral,onlineintergral" +
				" from intergralinfo" +
				" where phone = '" + phone + "' and" +
				" ( recordtime between  '"+ time_start +"' and '"+ time_end + "' )" ;
		
		if(intergral_start!=0)
			sql = sql + " and  consumeintergral+onlineintergral >=" + intergral_start;
		if(intergral_end!=0)
			sql = sql + " and consumeintergral+onlineintergral <=" + intergral_end;
	
		sql = sql + "order by recordtime"; 
		
		try {
			conn = DBConnection.getConnection();
			String str = "{?= call package_intergral.detailsearch(?)}";
			csmt = conn.prepareCall(str);
			csmt.registerOutParameter(1,OracleTypes.CURSOR);
			csmt.setString(2, sql);
			csmt.execute();
			rs = (ResultSet) csmt.getObject(1);
			
			while(rs.next()){
				Intergralinfo intergralinfo = new Intergralinfo();
				intergralinfo.setPhone(rs.getString("phone"));
				intergralinfo.setRecordtime(rs.getString("recordtime"));
				intergralinfo.setPhonefree(rs.getLong("phonefree"));
				intergralinfo.setConsumeintergral(rs.getLong("consumeintergral"));
				intergralinfo.setOnlineintergral(rs.getLong("onlineintergral"));
				list.add(intergralinfo);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				if(rs!=null)
					rs.close();
				if(csmt!=null)
					csmt.close();
				if(conn!=null)
					conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
			
		}
		
		return list;
	}

	/**
	 * 根据手机号码查找归属地
	 * @param phone 电话号码
	 * @return 归属地
	 */
	public String searchCity(String phone) {
		
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "select city from userinfo where phone = '" + phone + "'";
		String city = null;
		
		try {
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			
			while(rs.next())
				city = rs.getString("city");
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				if(rs!=null)
					rs.close();
				if(stmt!=null)
					stmt.close();
				if(conn!=null)
					conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		
		return city;
	}

	/**
	 * 根据手机号码查找年度积分
	 * @param phone 手机号码
	 * @param year 年份
	 * @param intergral_start 起始积分
	 * @param intergral_end 结束积分
	 * @return 年度积分
	 */
	public YearIntergral searchYearIntergral(String phone,String year,int intergral_start,int intergral_end) {
		
		YearIntergral yi = null;
		
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		String sql = "select * from (" +
				"select sum(consumeintergral) as year_consume," +
				"sum(onlineintergral) as year_online," +
				"sum(consumeintergral+onlineintergral) as year_total " +
				"from intergralinfo " +
				"where phone = '" + phone + "' " +
				"and recordtime like '" + year + "%' " +
				"group by phone) where 1=1 ";
		
		if(intergral_start!=0)
			sql = sql + "and year_total >=" + intergral_start;
		if(intergral_end!=0)
			sql = sql + " and year_total <=" + intergral_end;
		
		try {
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			
			while (rs.next()) {
				
				yi = new YearIntergral();
				yi.setYear_consume(rs.getInt("year_consume"));
				yi.setYear_online(rs.getInt("year_online"));
				yi.setYear_total(rs.getInt("year_total"));
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				if(rs!=null)
					rs.close();
				if(stmt!=null)
					stmt.close();
				if(conn!=null)
					conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		
		return yi;
	}

	/**
	 * 根据手机号码查询累积积分
	 * @param phone 手机号码
	 * @param intergral_start 起始积分
	 * @param intergral_end 结束积分
	 * @return 累积积分
	 */
	public CumulateIntergral searchCumulateIntegral(String phone, int intergral_start, int intergral_end) {
		
		CumulateIntergral cumulateIntergral = null;
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		String sql = "select * from (" +
				"select a.phone as phone,b.city as city," +
				"min(recordtime) as recordtime," +
				"sum(consumeintergral) as sum_consume," +
				"sum(onlineintergral) as sum_online," +
				"sum(consumeintergral+onlineintergral) as sum_total " +
				"from intergralinfo a " +
				"left join userinfo b " +
				"on  a.phone = b.phone " +
				"where a.phone = '" + phone +"' " +
				"group by a.phone,b.city) " +
				"where 1=1 ";
		
		if(intergral_start!=0)
			sql = sql + "and sum_total >=" + intergral_start;
		if(intergral_end!=0)
			sql = sql + " and sum_total <=" + intergral_end;
		
		try {
			
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			
			while(rs.next()){
				cumulateIntergral = new CumulateIntergral();
				cumulateIntergral.setPhone(rs.getString("phone"));
				cumulateIntergral.setCity(rs.getString("city"));
				cumulateIntergral.setRecordtime(rs.getString("recordtime"));
				cumulateIntergral.setSum_consume(rs.getInt("sum_consume"));
				cumulateIntergral.setSum_online(rs.getInt("sum_online"));
				cumulateIntergral.setSum_total(rs.getInt("sum_total"));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				if(rs!=null)
					rs.close();
				if(stmt!=null)
					stmt.close();
				if(conn!=null)
					conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		
		
		return cumulateIntergral;
	}

}