intergralconsumedaoimpl.java

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

package com.jn0801.intergral.consume;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.jn0801.common.UserInfo;
import com.jn0801.intergral.product.ProductDetail;
import com.jn0801.login.systemuser.SystemuserBean;
import com.jn0801.tools.DBConnection;
import com.jn0801.tools.PageDAO;

public class IntergralConsumeDaoImpl extends HibernateDaoSupport implements
		IntergralConsumeDao {

	/**
	 * 此方法返回用户信息
	 * 
	 * @param phone
	 * @return
	 */
	public UserInfo showUserInfo(String phone) {
		UserInfo userInfo = null;
		String hql = "select userinfo from UserInfo userinfo where userinfo.phone='"
				+ phone + "'";
		List list = this.getHibernateTemplate().find(hql);
		if (list.size() != 0) {
			userInfo = (UserInfo) list.get(0);
		}
		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 + "'";
			System.out.println(sql);
			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;
	}

	/**
	 * 此方法根据物品ID返回物品的详细信息
	 * 
	 * @param productid
	 * @return
	 */
	public Map showProductDetail(Long productid) {
		Connection connection = null;
		Map map = null;
		String sql = "select pd.productid,pd.productname,pd.productintroduce,pd.point,pd.productstorage,pd.productor,pt.sortname "
				+ "from productdetail pd,producttype pt where pd.typeid=pt.typeid and pd.productid="
				+ productid;
		try {
			connection = DBConnection.getConnection();
			QueryRunner queryRunner = new QueryRunner();
			map = (Map) queryRunner.query(connection, sql, new MapHandler());
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(null, null, null, connection);
		}
		return map;
	}

	/**
	 * 此方法根据条件列出产品
	 * 
	 * @param keyword
	 * @param typeid
	 * @param request
	 * @return
	 */
	public List listProduct(String keyword, Long typeid,
			HttpServletRequest request) {
		List productList = new ArrayList<ProductDetail>();
		Connection connection = null;
		String sql = null;
		PageDAO pageDAO = null;
		try {
			connection = DBConnection.getConnection();
			pageDAO = new PageDAO(request);
			sql = "select count(1) as rscount from productdetail where 1=1 ";
			if (typeid != null && typeid != 0) {
				sql = sql + " and typeid=" + typeid;
			}
			if (keyword != null && !"".equals(keyword)) {
				sql = sql + " and productname like '%" + keyword
						+ "%' or productintroduce like '%" + keyword + "%'";
			}
			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);

			System.out.println(sql);

			sql = "select * from "
					+ "(select * from (select pd.productid,pd.productname,pd.productintroduce,pd.point,pd.typeid,"
					+ "pt.sortname,pd.productor,pd.productstorage,rownum rn from productdetail pd,producttype pt where pd.typeid=pt.typeid) t"
					+ " where 1=1";
			if (typeid != null && typeid != 0) {
				sql = sql + " and t.typeid=" + typeid;
			}
			if (keyword != null && !"".equals(keyword)) {
				sql = sql + " and t.productname like '%" + keyword
						+ "%' or productintroduce like '%" + keyword + "%'";
			}
			sql = sql + " and rownum <=" + currentpage * pagesize + ") t ";
			sql = sql + " where t.rn >=" + ((currentpage - 1) * pagesize + 1);

			System.out.println(sql);

			QueryRunner queryRunner = new QueryRunner();
			productList = (List) queryRunner.query(connection, sql,
					new MapListHandler());
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtils.closeQuietly(connection);
		}
		return productList;
	}

	/**
	 * 此方法用于兑换物品
	 * 
	 * @param intergralConsumeForm
	 * @param systemuserBean
	 * @param userInfo
	 * @return
	 */
	public boolean changeProduct(IntergralConsumeForm intergralConsumeForm,
			SystemuserBean systemuserBean, UserInfo userInfo) {
		// 更新积分兑换记录
		// 更新库存信息
		// 更新物品操作记录
		// 更新用户积分表
		boolean flag = false;
		Long productid = intergralConsumeForm.getProductid();
		Long userid = userInfo.getUserid();
		Long point = intergralConsumeForm.getPoint();
		String phone = userInfo.getPhone();
		String consumelocation = userInfo.getArea();
		String consumecity = userInfo.getCity();
		int operatorid = systemuserBean.getNid();
		int productnum = intergralConsumeForm.getProductnum();

		Connection connection = null;
		Statement statement = null;
		String sql = null;
		try {
			connection = DBConnection.getConnection();
			connection.setAutoCommit(false);
			statement = connection.createStatement();
			sql = "insert into productlog (logid,productid,productnum,logtype,logdetail,logtime,operatorid)"
					+ "values (seq_productlog.nextval,"
					+ productid
					+ ","
					+ productnum
					+ ",'物品出库','',to_char(sysdate,'yyyy-MM-dd HH:mm'),"
					+ operatorid + ")";
			System.out.println(sql);
			statement.executeUpdate(sql);
			sql = "update productdetail set productstorage=productstorage-"
					+ productnum + " where productid=" + productid
					+ " and productstorage not in(-1)";
			System.out.println(sql);
			statement.executeUpdate(sql);
			sql = "insert into pointconsumerecord (consumeid, userid, productlogid, phone, consumedate,"
					+ " consumetype, consumepoint, consumelocation, consumecity, operatorid)"
					+ " values (seq_intergralconsume.nextval,"
					+ userid
					+ ",seq_productlog.currval,'"
					+ phone
					+ "',to_char(sysdate,'yyyy-MM-dd HH:mm'),'',"
					+ productnum
					* point
					+ ",'"
					+ consumelocation
					+ "','"
					+ consumecity
					+ "'," + operatorid + ")";
			System.out.println(sql);
			statement.executeUpdate(sql);
			sql = "update intergralinfo set consumeintergral=consumeintergral+"
					+ productnum * point + " where phone = '" + phone
					+ "' and recordtime=(to_char(sysdate,'yyyy-MM'))";
			System.out.println(sql);
			statement.executeUpdate(sql);
			flag = true;
			connection.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(null, null, statement, connection);
		}
		return flag;
	}

	/**
	 * 返回物品类型列表
	 * 
	 * @return
	 */
	public List listType() {
		List<Map<String, Object>> typeList = null;
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		String sql = null;
		try {
			connection = DBConnection.getConnection();
			statement = connection.createStatement();
			typeList = new ArrayList<Map<String, Object>>();
			sql = "select * from producttype";
			resultSet = statement.executeQuery(sql);
			while (resultSet.next()) {
				Map<String, Object> type = new HashMap<String, Object>();
				type.put("typeid", resultSet.getObject(1));
				type.put("sortname", resultSet.getObject(3));
				typeList.add(type);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return typeList;
	}
}