consumerecorddaoimpl.java

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

package com.jn0801.intergral.record;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;

import com.jn0801.common.UserInfo;
import com.jn0801.tools.DBConnection;
import com.jn0801.tools.PageDAO;

public class ConsumeRecordDaoImpl implements ConsumeRecordDao {

	/**
	 * 此方法根据电话号码返回用户信息
	 * 
	 * @param phone
	 * @return
	 */
	public UserInfo findUserInfo(String phone) {
		Connection connection = null;
		String sql = null;
		UserInfo userInfo = null;
		try {
			connection = DBConnection.getConnection();
			sql = "select * from userinfo where phone='" + phone + "'";
			QueryRunner queryRunner = new QueryRunner();
			userInfo = (UserInfo) queryRunner.query(connection, sql,
					new BeanHandler(UserInfo.class));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(null, null, null, connection);
		}
		return userInfo;
	}

	/**
	 * 此方法根据号码返回用户的可用积分
	 * 
	 * @param phone
	 * @return
	 */
	public long sumIntergral(String phone) {
		long totalIntergral = 0;
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			String sql = "select sum(onlineintergral)+sum(honorintergral)+sum(guerdonintergral)"
					+ "+sum(adjustintergral)-sum(consumeintergral) from intergralinfo "
					+ "where recordtime>to_char(add_months(sysdate,-36),'yyyy-mm')"
					+ " and phone='" + phone + "'";
			connection = DBConnection.getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			if (resultSet.next()) {
				totalIntergral = resultSet.getLong(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(resultSet, null, statement, connection);
		}
		return totalIntergral;
	}

	/**
	 * 根据条件列出所有兑换记录
	 * 
	 * @param phone
	 * @param startDate
	 * @param endDate
	 * @param request
	 * @return
	 */
	public List listConsumeRecord(String phone, String startDate,
			String endDate, HttpServletRequest request) {
		List consumeRecordList = null;
		Connection connection = null;
		String sql = null;
		try {
			connection = DBConnection.getConnection();
			PageDAO pageDAO = new PageDAO(request);
			sql = "select count(1) as rscount from pointconsumerecord where 1=1";
			if (phone != null && !"".equals(phone)) {
				sql = sql + " and phone='" + phone + "'";
			}
			if (startDate != null && !"".equals(startDate)) {
				sql = sql + " and consumedate>='" + startDate + "'";
			}
			if (endDate != null && !"".equals(endDate)) {
				sql = sql + " and consumedate<='" + endDate + "'";
			}
			int rscount = pageDAO.getRsCountForSQL(sql);
			int pagesize = 10;
			pageDAO.setPagesize(pagesize);
			pageDAO.setRscount(rscount);
			pageDAO.getPageCount();
			int currentpage = pageDAO.getCurrentPage();
			String pagetool = pageDAO.pagetool(PageDAO.BbsText);
			request.setAttribute("pagetool", pagetool);

			sql = "select * from(select consumeid,userid,productlogid,phone,consumedate,consumetype,consumepoint,consumelocation,"
					+ "consumecity,operatorid,rownum rn from pointconsumerecord where 1=1";
			if (phone != null && !"".equals(phone)) {
				sql = sql + " and phone='" + phone + "'";
			}
			if (startDate != null && !"".equals(startDate)) {
				sql = sql + " and consumedate>='" + startDate + "'";
			}
			if (endDate != null && !"".equals(endDate)) {
				sql = sql + " and consumedate<='" + endDate + "'";
			}
			sql = sql + " and rownum <=" + currentpage * pagesize + ") t ";
			sql = sql + " where t.rn >=" + ((currentpage - 1) * pagesize + 1);

			System.out.println(sql);

			QueryRunner queryRunner = new QueryRunner();
			consumeRecordList = (List) queryRunner.query(connection, sql,
					new BeanListHandler(PointConsumeRecord.class));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(null, null, null, connection);
		}
		return consumeRecordList;
	}

	/**
	 * 根据兑换记录编号返回兑换记录
	 * 
	 * @param consumeid
	 * @return
	 */
	public Map findConsumeRecord(Long consumeid) {
		Connection connection = null;
		String sql = null;
		Map consumeRecord = null;
		try {
			connection = DBConnection.getConnection();
			sql = "select psr.consumeid,psr.userid,psr.phone,psr.consumedate,"
					+ "psr.consumetype,psr.consumepoint,psr.consumelocation,"
					+ "psr.consumecity,psr.operatorid,pl.productid,pl.productnum,pd.productname "
					+ "from pointconsumerecord psr,productlog pl,productdetail pd  "
					+ "where psr.productlogid=pl.logid and pl.productid=pd.productid and consumeid="
					+ consumeid;
			System.out.println(sql);
			QueryRunner queryRunner = new QueryRunner();
			consumeRecord = (Map) queryRunner.query(connection, sql,
					new MapHandler());
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(null, null, null, connection);
		}
		return consumeRecord;
	}

	/**
	 * 此方法根据积分兑换记录编号对积分兑换进行回退操作
	 * 
	 * @param consumeid
	 * @param operatorid
	 * @return
	 */
	public boolean pointBack(Long consumeid, int operatorid) {
		// 查询兑换记录
		// 恢复物品
		// 物品日志
		// 恢复积分
		// 删除兑换记录
		Boolean flag = false;
		Connection connection = null;
		Statement statement = null;
		String sql = null;
		String phone = null;
		String consumedate = null;
		BigDecimal productid = null;
		BigDecimal productnum = null;
		BigDecimal consumepoint = null;
		Map consumeRecord = null;
		try {
			connection = DBConnection.getConnection();
			connection.setAutoCommit(false);
			statement = connection.createStatement();
			QueryRunner queryRunner = new QueryRunner();
			sql = "select psr.consumeid,psr.phone,psr.consumepoint,psr.consumedate,pl.logid,pl.productid,pl.productnum "
					+ "from pointconsumerecord psr,productlog pl where psr.productlogid=pl.logid and consumeid="
					+ consumeid;
			consumeRecord = (Map) queryRunner.query(connection, sql,
					new MapHandler());
			phone = (String) consumeRecord.get("phone");
			consumedate = (String) consumeRecord.get("consumedate");
			productid = (BigDecimal) consumeRecord.get("productid");
			productnum = (BigDecimal) consumeRecord.get("productnum");
			consumepoint = (BigDecimal) consumeRecord.get("consumepoint");
			sql = "update productdetail set productstorage=productstorage+"
					+ productnum.intValue() + " where productid="
					+ productid.intValue() + " and productstorage not in(-1)";
			System.out.println(sql);
			statement.execute(sql);
			sql = "insert into productlog (logid,productid,productnum,logtype,logdetail,logtime,operatorid)"
					+ "values (seq_productlog.nextval,"
					+ productid.intValue()
					+ ","
					+ productnum.intValue()
					+ ",'物品回退','',to_char(sysdate,'yyyy-MM-dd HH:mm'),"
					+ operatorid + ")";
			System.out.println(sql);
			statement.execute(sql);
			sql = "update intergralinfo set consumeintergral=consumeintergral-"
					+ consumepoint.intValue() + " where phone = '" + phone
					+ "' and recordtime='" + consumedate + "'";
			System.out.println(sql);
			statement.execute(sql);
			sql = "delete pointconsumerecord where consumeid="
					+ consumeid.intValue();
			System.out.println(sql);
			statement.execute(sql);

			flag = true;
			connection.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(null, null, statement, connection);
		}
		return flag;
	}

}