reportbizimpl.java

通过这个系统完成对客户基本信息、联系人信息、交往信息、客户服务信息的充分共享和规范化管理；希望通过对销售机会、客户开发过程的追踪和记录

package com.t53.crm4.report.biz.impl;

import java.util.Calendar;
import java.util.Date;
import java.util.List;

import com.t53.crm4.common.DateUtil;
import com.t53.crm4.common.entity._CustomerTemp;
import com.t53.crm4.common.entity._ServiceTemp;
import com.t53.crm4.report.biz.IReportBiz;
import com.t53.crm4.report.dao.IReportDAO;
import com.t53.crm4.report.dao.IReportDAO_sale;

public class ReportBizImpl implements IReportBiz {

	private IReportDAO reportDAO;
	private IReportDAO_sale reportDAO_sale;

	/**
	 * 客户贡献分析
	 */
	public List<Object> getCusContr() {

		// 完整sql
		// select d.odr_customer,sum(c.prod_price*x.odd_count) as total
		// from orders d,product c,orders_line x where x.odd_order_id=d.odr_id
		// and
		// x.odd_prod_id=c.prod_id group by d.odr_customer order by total

		String sql1 = "select d.odrCustomer,sum(c.prodPrice*x.oddCount)"
				+ " from Orders d,Product c,OrdersLine x "
				+ " where x.orders.odrId=d.odrId and "
				+ "x.product.prodId=c.prodId group by d.odrCustomer"
				+ " order by sum(c.prodPrice*x.oddCount) desc";

		return reportDAO_sale.find(sql1);
	}

	/**
	 * 按客户名查看客户贡献
	 */
	public List getCusContr(String cusName, String dateStart, String dateEnd) {
		StringBuilder builder = new StringBuilder();
		builder.append("select d.odrCustomer,sum(c.prodPrice*x.oddCount)");
		builder.append(" from Orders d,Product c,OrdersLine x ");
		builder
				.append(" where x.orders.odrId=d.odrId and x.product.prodId=c.prodId ");
		if (!"".equals(cusName) && cusName != null) {
			builder.append("and d.odrCustomer like '%").append(cusName).append(
					"%' ");
		}
		if (dateStart != null && dateEnd != null) {
			builder.append("and d.odrDate between '").append(dateStart).append(
					"' and '").append(dateEnd).append("' ");
		}
		builder
				.append("group by d.odrCustomer order by sum(c.prodPrice*x.oddCount) desc");

		return reportDAO_sale.find(builder.toString());
	}

	/**
	 * 得到客户服务统计
	 */
	public List getCusService(String param) {

		Integer year = 0;
		String oper = "svrCreateDate";
		if (param != null)
			year = Integer.parseInt(param);

		StringBuilder builder = new StringBuilder();
		builder
				.append("select new com.t53.crm4.common.entity._ServiceTemp(s.svrType,count(s)) from CstService s where 1=1");
		// 如果是全部年份
		if (year != 0) {
			// 开始年份
			Calendar calendar = Calendar.getInstance();
			calendar.set(year - 1, 11, 31, 23, 59, 59);
			String startY = DateUtil.parseToString(calendar.getTime(),
					DateUtil._yyyyMMddHHmmss);

			// 结束年份
			calendar.set(year, 11, 31, 23, 59, 59);
			String endY = DateUtil.parseToString(calendar.getTime(),
					DateUtil._yyyyMMddHHmmss);
			if (null != year && year != 0)
				builder.append(" and s.svrCreateDate between '" + startY
						+ "' and '" + endY + "'");
		}
		builder.append(" group by s.svrType");

		// String hql = "select new
		// com.t46.crm.model.service.ServiceCasual(s.svrType,count(s)) ";
		// hql += " from Service s where 1=1";
		// if (null != parma) {
		// hql += " and s.svrCreateDate like '%" + parma + "%'";
		// }
		// hql += " group by s.svrType";

		return reportDAO_sale.find(builder.toString());
	}

	/**
	 * 得到销售系统需要的所有的年份
	 */
	public List getAllYear4Sale(String tableName, String dName) {

		String sql = "select distinct datepart(yy," + dName + ") from "
				+ tableName;

		return reportDAO_sale.findSQL(sql);
	}

	/**
	 * 得到所有年份
	 */
	public List getAllYear(String tableName, String dName) {

		String sql = "select distinct datepart(yy," + dName + ") from "
				+ tableName;

		return reportDAO.findSQL(sql);
	}

	/**
	 * 查看客户构成
	 */
	public List getCusConts(String param) {

		Integer id = 0;
		String oper = "";

		if (param != null)
			id = Integer.parseInt(param);
		if (id == 1)
			oper = "custLevelLabel";
		else if (id == 2)
			oper = "custCredit";
		else
			oper = "custSatisfy";
		StringBuilder builder = new StringBuilder();
		builder
				.append("select new com.t53.crm4.common.entity._CustomerTemp(count(c),");
		builder.append(oper);
		builder.append(") from CstCustomer c group by c.");
		builder.append(oper);
		builder.append(" order by c.");
		builder.append(oper);
		builder.append(" desc");

		return reportDAO_sale.find(builder.toString());

	}

	/**
	 * 获得客户服务
	 * 
	 * @return
	 */
	public List<_ServiceTemp> getSelService(String sDate) {

		return null;
	}

	public void setReportDAO(IReportDAO reportDAO) {
		this.reportDAO = reportDAO;
	}

	public void setReportDAO_sale(IReportDAO_sale reportDAO_sale) {
		this.reportDAO_sale = reportDAO_sale;
	}

}