package com.espeed.service.impl;
  
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringEscapeUtils;
import org.apache.commons.lang.StringUtils;

import com.espeed.centre.pojo.YxyUserInfo;
import com.espeed.dao.CrmCustomerIndustriesDao;
import com.espeed.dao.CrmCustomerInfoDao;
import com.espeed.dao.CrmProcessDao;
import com.espeed.dao.CrmProductDao;
import com.espeed.dao.CrmSourcefromDao;
import com.espeed.dao.CrmStatusDao;
import com.espeed.dao.SysParamatersValueDao;
import com.espeed.dao.YxyCustomerEmailDao;
import com.espeed.dao.YxyCustomerTypeDao;
import com.espeed.service.YxyCustomerService;
import com.espeed.tool.DateFormat;
import com.espeed.vo.PageBean;
import com.espeed.webmail.pojo.CrmCustomerIndustries;
import com.espeed.webmail.pojo.CrmProcess;
import com.espeed.webmail.pojo.CrmProduct;
import com.espeed.webmail.pojo.CrmSourcefrom;
import com.espeed.webmail.pojo.CrmStatus;
import com.espeed.webmail.pojo.SysParamatersValue;
import com.espeed.webmail.pojo.YxyCustomerEmail;
import com.espeed.webmail.pojo.YxyCustomerType;

/***
 * 
 * @author 谢勇
 * 客户业务service实现类
 */
public class YxyCustomerServiceImpl  implements YxyCustomerService{
 
	/**获取所有客户分类*/
	public List<YxyCustomerType> findClassifyAll(YxyUserInfo user,int iscounts) throws Exception 
	{
		String hql = "from YxyCustomerType where company_id = "+user.getCompany_id();
		if(user.getParam_level()==1){//个人级参数
			hql = "from YxyCustomerType where owner_type = 1 and owner_id = "+user.getUser_id();
		}
		if(iscounts==1){//统计各分类的数量
			List<YxyCustomerType> new_types = new ArrayList<YxyCustomerType>();
			List<YxyCustomerType> types = customertypedao.findByHql(hql);
			String sql = "select classify_id,COUNT(customer_id) from crm_customer where user_id = "+user.getUser_id()+" and delete_flag = 0 and current_status != 1 GROUP BY classify_id";
			List<Object> objects = yxycustomeremaildao.findBySqlQuery(sql);
			if(objects.size() > 0){
				for(Object object : objects){
					Object[] obj_arr = (Object[])object;
					int classify_id = Integer.parseInt(obj_arr[0].toString());
					int counts = Integer.parseInt(obj_arr[1].toString());
					for(YxyCustomerType type : types){
						if(classify_id == type.getId() && counts > 0){
							type.setCounts(counts);
							new_types.add(type);
							break;
						}
					}
				}
			}
			return new_types;
		}else{//不统计各分类的数量
			return customertypedao.findByHql(hql);
		}
	}
	
	/**获取客户分类下的客户集合*/
	public List<YxyCustomerEmail> findCustomersByClassify(int user_id,int classify_id)throws Exception{
		String hql="from YxyCustomerEmail where delete_flag = 0 and user_id = "+user_id+" and current_status != 1 and classify_id="+classify_id;
		List<YxyCustomerEmail> customers = yxycustomeremaildao.findByHql(hql);
		List<YxyCustomerEmail> new_customers = new ArrayList<YxyCustomerEmail>();
		if(customers.size()>0){
			for(YxyCustomerEmail customer : customers){
				//只查询主联系人的信息
				String sql = "select linkman_id,full_name,email from crm_linkman where customer_id = "+customer.getCustomer_id()+" and is_main_linkman = 1";
				List<Object> objects = yxycustomeremaildao.findBySqlQuery(sql);
				if(objects.size() > 0){
					Object[] obj_arr = (Object[])objects.get(0);
					if(obj_arr[1]!=null){//联系人姓名
						customer.setFull_name(obj_arr[1].toString());
					}
					if(obj_arr[2]!=null){//邮箱地址
						customer.setEmail(obj_arr[2].toString());
					}
					new_customers.add(customer);
				}
			}
		}
		return new_customers;
	}
	
	/**获取所有客户状态*/
	public List<CrmStatus> findStatusAll(YxyUserInfo user,int iscounts)throws Exception{
		String hql = "from CrmStatus where company_id = "+user.getCompany_id();
		if(user.getParam_level()==1){//个人级参数
			hql = "from CrmStatus where owner_type = 1 and owner_id = "+user.getUser_id();
		}
		if(iscounts==1){//统计各状态的数量
			List<CrmStatus> new_status = new ArrayList<CrmStatus>();
			List<CrmStatus> status = crmstatusdao.findByHql(hql);
			String sql = "select customer_status,COUNT(customer_id) from crm_customer where user_id = "+user.getUser_id()+" and delete_flag = 0 and current_status != 1 GROUP BY customer_status";
			List<Object> objects = yxycustomeremaildao.findBySqlQuery(sql);
			if(objects.size() > 0){
				for(Object object : objects){
					Object[] obj_arr = (Object[])object;
					int customer_status = Integer.parseInt(obj_arr[0].toString());
					int counts = Integer.parseInt(obj_arr[1].toString());
					for(CrmStatus statu : status){
						if(customer_status == statu.getId() && counts > 0){
							statu.setCounts(counts);
							new_status.add(statu);
							break;
						}
					}
				}
			}
			return new_status;
		}else{//不统计各状态的数量
			return crmstatusdao.findByHql(hql);
		}
	}
	
	/**获取客户状态下的客户集合*/
	public List<YxyCustomerEmail> findCustomersByStatus(int user_id,int status_id)throws Exception{
		String hql="from YxyCustomerEmail where delete_flag = 0 and user_id = "+user_id+" and current_status != 1 and customer_status="+status_id;
		List<YxyCustomerEmail> customers = yxycustomeremaildao.findByHql(hql);
		List<YxyCustomerEmail> new_customers = new ArrayList<YxyCustomerEmail>();
		if(customers.size()>0){
			for(YxyCustomerEmail customer : customers){
				//只查询主联系人的信息
				String sql = "select linkman_id,full_name,email from crm_linkman where customer_id = "+customer.getCustomer_id()+" and is_main_linkman = 1";
				List<Object> objects = yxycustomeremaildao.findBySqlQuery(sql);
				if(objects.size() > 0){
					Object[] obj_arr = (Object[])objects.get(0);
					if(obj_arr[1]!=null){//联系人姓名
						customer.setFull_name(obj_arr[1].toString());
					}
					if(obj_arr[2]!=null){//邮箱地址
						customer.setEmail(obj_arr[2].toString());
					}
					new_customers.add(customer);
				}
			}
		}
		return new_customers;
	}
	
	/**获取所有客户来源*/
	public List<CrmSourcefrom> findFromAll(YxyUserInfo user,int iscounts)throws Exception{
		String hql = "from CrmSourcefrom where company_id = "+user.getCompany_id();
		if(user.getParam_level()==1){//个人级参数
			hql = "from CrmSourcefrom where owner_type = 1 and owner_id = "+user.getUser_id();
		}
		if(iscounts==1){//统计各来源的数量
			List<CrmSourcefrom> new_froms = new ArrayList<CrmSourcefrom>();
			List<CrmSourcefrom> froms = crmsourcefromdao.findByHql(hql);
			String sql = "select source_from_id,COUNT(customer_id) from crm_customer where user_id = "+user.getUser_id()+" and delete_flag = 0 and current_status != 1 GROUP BY source_from_id";
			List<Object> objects = yxycustomeremaildao.findBySqlQuery(sql);
			if(objects.size() > 0){
				for(Object object : objects){
					Object[] obj_arr = (Object[])object;
					int source_from_id = Integer.parseInt(obj_arr[0].toString());
					int counts = Integer.parseInt(obj_arr[1].toString());
					for(CrmSourcefrom from : froms){
						if(source_from_id == from.getId() && counts > 0){
							from.setCounts(counts);
							new_froms.add(from);
							break;
						}
					}
				}
			}
			return new_froms;
		}else{//不统计各来源的数量
			return crmsourcefromdao.findByHql(hql);
		}
	}
	
	/**获取客户来源下的客户集合*/
	public List<YxyCustomerEmail> findCustomersByFrom(int user_id,int from_id)throws Exception{
		String hql="from YxyCustomerEmail where delete_flag = 0 and user_id = "+user_id+" and current_status != 1 and source_from_id="+from_id;
		List<YxyCustomerEmail> customers = yxycustomeremaildao.findByHql(hql);
		List<YxyCustomerEmail> new_customers = new ArrayList<YxyCustomerEmail>();
		if(customers.size()>0){
			for(YxyCustomerEmail customer : customers){
				//只查询主联系人的信息
				String sql = "select linkman_id,full_name,email from crm_linkman where customer_id = "+customer.getCustomer_id()+" and is_main_linkman = 1";
				List<Object> objects = yxycustomeremaildao.findBySqlQuery(sql);
				if(objects.size() > 0){
					Object[] obj_arr = (Object[])objects.get(0);
					if(obj_arr[1]!=null){//联系人姓名
						customer.setFull_name(obj_arr[1].toString());
					}
					if(obj_arr[2]!=null){//邮箱地址
						customer.setEmail(obj_arr[2].toString());
					}
					new_customers.add(customer);
				}
			}
		}
		return new_customers;
	}
	
	/**获取所有产品信息*/
	public List<CrmProduct> findProductAll(YxyUserInfo user,int iscounts)throws Exception{
		List<CrmProduct> products = new ArrayList<CrmProduct>();
		String sql1 = "select id,name from crm_product_info where company_id = "+user.getCompany_id()+" and language = 'en'";
		//不在crm显示的产品分类
		String sql2 = "select id from crm_product where is_show_crm = 0 and owner_type = 0 and owner_id = "+user.getCompany_id();
		List<Object> objs = yxycustomeremaildao.findBySqlQuery(sql2);
		if (objs.size() > 0) {
			sql1 += " and product_type_id not in(" + StringUtils.join(objs.iterator(), ",") + ")";
		}
		sql1 += " order by from_type asc,create_time desc";
		List<Object> objects = yxycustomeremaildao.findBySqlQuery(sql1);
		if(objects.size() > 0){
			for(Object obj : objects){
				Object[] obj_arr = (Object[])obj;
				int id = Integer.parseInt(obj_arr[0].toString());
				String name = obj_arr[1].toString();
				CrmProduct product = new CrmProduct();
				product.setId(id);
				product.setName(name);
				product.setCompany_id(user.getCompany_id());
				int counts = 0;
				if(iscounts == 1){//统计各产品对应的客户数量
					String hql="select count(customer_id) from YxyCustomerEmail where delete_flag = 0 and current_status != 1 " +
					"and user_id = "+user.getUser_id()+" and find_in_set("+id+",prductid) > 0";
					counts = yxycustomeremaildao.findByHqlCount(hql);
				}
				product.setCounts(counts);
				products.add(product);
			}
		}
		return products;
//		String hql = "from CrmProduct where company_id = "+user.getCompany_id();
//		if(user.getParam_level()==1){//个人级参数
//			hql = "from CrmProduct where owner_type = 1 and owner_id = "+user.getUser_id();
//		}
//		if(iscounts==1){//统计各产品的数量
//			List<CrmProduct> products = crmproductdao.findByHql(hql);
//			List<CrmProduct> new_products = new ArrayList<CrmProduct>();
//			for(CrmProduct product : products){
//				hql="select count(customer_id) from YxyCustomerEmail where delete_flag = 0 and current_status != 1 " +
//						"and user_id = "+user.getUser_id()+" and find_in_set("+product.getId()+",prductid) > 0";
//				int counts = yxycustomeremaildao.findByHqlCount(hql);
//				if(counts > 0){
//					product.setCounts(counts);
//					new_products.add(product);
//				}
//			}
//			return new_products;
//		}else{//不统计各产品的数量
//			return crmproductdao.findByHql(hql);
//		}
	}
	
	/**获取具体产品下的客户集合*/
	public List<YxyCustomerEmail> findCustomersByProduct(int user_id,int product_id)throws Exception{
		String hql="from YxyCustomerEmail where delete_flag = 0 and user_id = "+user_id+" and current_status != 1 and find_in_set("+product_id+",prductid) > 0";
		List<YxyCustomerEmail> customers = yxycustomeremaildao.findByHql(hql);
		List<YxyCustomerEmail> new_customers = new ArrayList<YxyCustomerEmail>();
		if(customers.size()>0){
			for(YxyCustomerEmail customer : customers){
				//只查询主联系人的信息
				String sql = "select linkman_id,full_name,email from crm_linkman where customer_id = "+customer.getCustomer_id()+" and is_main_linkman = 1";
				List<Object> objects = yxycustomeremaildao.findBySqlQuery(sql);
				if(objects.size() > 0){
					Object[] obj_arr = (Object[])objects.get(0);
					if(obj_arr[1]!=null){//联系人姓名
						customer.setFull_name(obj_arr[1].toString());
					}
					if(obj_arr[2]!=null){//邮箱地址
						customer.setEmail(obj_arr[2].toString());
					}
					new_customers.add(customer);
				}
			}
		}
		return new_customers;
	
	}
	
	/**获取所有销售进程*/
	public List<CrmProcess> findProcessAll(YxyUserInfo user)throws Exception{
		String hql = "from CrmProcess where company_id = "+user.getCompany_id();
		if(user.getParam_level()==1){//个人级参数
			hql = "from CrmProcess where owner_type = 1 and owner_id = "+user.getUser_id();
		}
		return crmprocessdao.findByHql(hql);
	}
	
	/**获取销售进程下的客户集合*/
	public List<YxyCustomerEmail> findCustomersByProcess(int user_id,int porcess_id)throws Exception{
		String hql="from YxyCustomerEmail where delete_flag = 0 and user_id = "+user_id+" and current_status != 1 and sales_status="+porcess_id;
		List<YxyCustomerEmail> customers = yxycustomeremaildao.findByHql(hql);
		List<YxyCustomerEmail> new_customers = new ArrayList<YxyCustomerEmail>();
		if(customers.size()>0){
			for(YxyCustomerEmail customer : customers){
				//只查询主联系人的信息
				String sql = "select linkman_id,full_name,email from crm_linkman where customer_id = "+customer.getCustomer_id()+" and is_main_linkman = 1";
				List<Object> objects = yxycustomeremaildao.findBySqlQuery(sql);
				if(objects.size() > 0){
					Object[] obj_arr = (Object[])objects.get(0);
					if(obj_arr[1]!=null){//联系人姓名
						customer.setFull_name(obj_arr[1].toString());
					}
					if(obj_arr[2]!=null){//邮箱地址
						customer.setEmail(obj_arr[2].toString());
					}
					new_customers.add(customer);
				}
			}
		}
		return new_customers;
	}
	
	/**获取公海条件下的客户集合*/
	public List<YxyCustomerEmail> findCustomersByPublic(int company_id,int user_id,int public_id)throws Exception{
		List<YxyCustomerEmail> customers = new ArrayList<YxyCustomerEmail>();
		List<YxyCustomerEmail> new_customers = new ArrayList<YxyCustomerEmail>();
		if(public_id==1){//7天内未联系客户入公海库
			int public_day = 100;//系统设置的天数
			String param_hql = "from SysParamatersValue where owner_type = 2 and para_id = 176 and owner_id = "+company_id;
			List<SysParamatersValue> values = sysparamatersvaluedao.findByHql(param_hql);
			if(values.size() > 0){
				public_day = Integer.parseInt(values.get(0).getPara_value());
			}
			//先获取当前用户未成交的客户即有没有成交记录
			String yes_customerids = "";
			String sql = "select customer_id from crm_product_sales where create_user_id ="+user_id;
			List<Object> objects = yxycustomeremaildao.findBySqlQuery(sql);
			for(int i = 0;i < objects.size();i++)
			{
				yes_customerids += objects.get(i).toString() + ",";
			}
			//计算出明天日期前的public_day日期
			String lastDayString = DateFormat.lastDays(+1)+" 00:00:00";
			String public_date = DateFormat.nDaysAfterOneDateString(lastDayString, -public_day).split(" ")[0];
			//计算出7天后日期前的public_day日期
			String lastDayString2 = DateFormat.lastDays(+7)+" 00:00:00";
			String public_date2 = DateFormat.nDaysAfterOneDateString(lastDayString2, -public_day).split(" ")[0];
			String start_date = public_date + " 00:00:00";
			String end_date = public_date2 + " 23:59:59";
			String hql = "from YxyCustomerEmail where delete_flag=0 and current_status!=1 and user_id ="+user_id+" and last_contact_date between '"+start_date+"' and '"+end_date+"'";
			if(!"".equals(yes_customerids))//有成交客户
			{
				yes_customerids = yes_customerids.substring(0,yes_customerids.length()-1);
				hql+=" and customer_id not in("+yes_customerids+")";
			}
			customers = yxycustomeremaildao.findByHql(hql);
		}else if(public_id==2){//7天内未成交客户入公海库
			String param_hql = "from SysParamatersValue where para_value = '1' and owner_type = 2 and para_id = 183 and owner_id = "+company_id;
			List<SysParamatersValue> values = sysparamatersvaluedao.findByHql(param_hql);
			//启用了未成交客户加入公海库
			if(values.size() > 0){
				int public_day = 90;//系统设置的天数
				param_hql = "from SysParamatersValue where owner_type = 2 and para_id = 184 and owner_id = "+company_id;
				values = sysparamatersvaluedao.findByHql(param_hql);
				if(values.size() > 0){
					public_day = Integer.parseInt(values.get(0).getPara_value());
				} 
				//先获取当前用户未成交的客户即有没有成交记录
				String yes_customerids = "";
				String sql = "select customer_id from crm_product_sales where create_user_id ="+user_id;
				List<Object> objects = yxycustomeremaildao.findBySqlQuery(sql);
				for(int i = 0;i < objects.size();i++)
				{
					yes_customerids += objects.get(i).toString() + ",";
				}
				//计算出明天日期前的public_day日期
				String lastDayString = DateFormat.lastDays(+1)+" 00:00:00";
				String public_date = DateFormat.nDaysAfterOneDateString(lastDayString, -public_day).split(" ")[0];
				//计算出7天后日期前的public_day日期
				String lastDayString2 = DateFormat.lastDays(+7)+" 00:00:00";
				String public_date2 = DateFormat.nDaysAfterOneDateString(lastDayString2, -public_day).split(" ")[0];
				String start_date = public_date + " 00:00:00";
				String end_date = public_date2 + " 23:59:59";
				if(!"".equals(yes_customerids))//有成交客户
				{
					yes_customerids = yes_customerids.substring(0,yes_customerids.length()-1);
					String hql = "from YxyCustomerEmail where delete_flag=0 and current_status!=1 and user_id ="+user_id+" and customer_id not in("+yes_customerids+") and enrol_time between '"+start_date+"' and '"+end_date+"'";
					customers = yxycustomeremaildao.findByHql(hql);
				}
				else//无成交客户
				{
					String hql = "from YxyCustomerEmail where delete_flag=0 and current_status!=1 and user_id ="+user_id+" and enrol_time between '"+start_date+"' and '"+end_date+"'";
					customers = yxycustomeremaildao.findByHql(hql);
				}
			}
		}else if(public_id==3){//7天内成交未联系入公海库
			String param_hql = "from SysParamatersValue where para_value = '1' and owner_type = 2 and para_id = 185 and owner_id = "+company_id;
			List<SysParamatersValue> values = sysparamatersvaluedao.findByHql(param_hql);
			//启用了成交未联系入公海库
			if(values.size() > 0){
				int public_day = 90;//系统设置的天数
				param_hql = "from SysParamatersValue where owner_type = 2 and para_id = 186 and owner_id = "+company_id;
				values = sysparamatersvaluedao.findByHql(param_hql);
				if(values.size() > 0){
					public_day = Integer.parseInt(values.get(0).getPara_value());
				}
				//先获取当前用户未成交的客户即有没有成交记录
				String yes_customerids = "";
				String sql = "select customer_id from crm_product_sales where create_user_id ="+user_id;
				List<Object> objects = yxycustomeremaildao.findBySqlQuery(sql);
				for(int i = 0;i < objects.size();i++)
				{
					yes_customerids += objects.get(i).toString() + ",";
				}
				if(!"".equals(yes_customerids))//有成交客户
				{
					yes_customerids = yes_customerids.substring(0,yes_customerids.length()-1);
					//计算出明天日期前的public_day日期
					String lastDayString = DateFormat.lastDays(+1)+" 00:00:00";
					String public_date = DateFormat.nDaysAfterOneDateString(lastDayString, -public_day).split(" ")[0];
					//计算出7天后日期前的public_day日期
					String lastDayString2 = DateFormat.lastDays(+7)+" 00:00:00";
					String public_date2 = DateFormat.nDaysAfterOneDateString(lastDayString2, -public_day).split(" ")[0];
					String start_date = public_date + " 00:00:00";
					String end_date = public_date2 + " 23:59:59";
					String hql = "from YxyCustomerEmail where delete_flag=0 and current_status!=1 and user_id ="+user_id+" and last_contact_date between '"+start_date+"' and '"+end_date+"' and customer_id in("+yes_customerids+")";
					customers = yxycustomeremaildao.findByHql(hql);
				}
			}
		}
		if(customers.size()>0){
			for(YxyCustomerEmail customer : customers){
				//只查询主联系人的信息
				String sql = "select linkman_id,full_name,email from crm_linkman where customer_id = "+customer.getCustomer_id()+" and is_main_linkman = 1";
				List<Object> objects = yxycustomeremaildao.findBySqlQuery(sql);
				if(objects.size() > 0){
					Object[] obj_arr = (Object[])objects.get(0);
					if(obj_arr[1]!=null){//联系人姓名
						customer.setFull_name(obj_arr[1].toString());
					}
					if(obj_arr[2]!=null){//邮箱地址
						customer.setEmail(obj_arr[2].toString());
					}
					new_customers.add(customer);
				}
			}
		}
		return new_customers;
	}
	
	/**根据客户id查询客户信息*/
	public List<YxyCustomerEmail> findEmailByCusid(String customer_ids)throws Exception
	{ 
		String hql="from YxyCustomerEmail where customer_id in("+customer_ids+")";
		List<YxyCustomerEmail> customers = yxycustomeremaildao.findByHql(hql);
		for(YxyCustomerEmail customer : customers){
			//只查询主联系人的信息
			String sql = "select linkman_id,full_name,email from crm_linkman where customer_id = "+customer.getCustomer_id()+" order by is_main_linkman desc";
			List<Object> objects = yxycustomeremaildao.findBySqlQuery(sql);
			if(objects.size() > 0){
				Object[] obj_arr = (Object[])objects.get(0);
				if(obj_arr[1]!=null){//联系人姓名
					customer.setFull_name(obj_arr[1].toString());
				}
				if(obj_arr[2]!=null){//邮箱地址
					customer.setEmail(obj_arr[2].toString());
				}
			}
		}
		return customers;
	}
	
	/**企业下所有客户地址信息*/
	public List<Object> findCusEmailList(int company_id)throws Exception{
		String sql="select customer_id,email from crm_linkman where company_id="+company_id+" and customer_id>0";
		return yxycustomeremaildao.findBySqlQuery(sql);
	} 
	
	/**获取所有采购偏好*/
	public List<CrmCustomerIndustries> findIndustriesAll(int user_id,int condition,String conditionValue,PageBean pb)throws Exception{
		List<CrmCustomerIndustries> list = new ArrayList<CrmCustomerIndustries>();
		if(condition==1){//不分页加载有数据的采购偏好
			String hql = "from CrmCustomerIndustries where user_id = "+user_id+" and nums > 0 order by nums desc";
			list = crmcustomerindustriesdao.findByHql(hql);
		}else if(condition==2){//分页加载
			String hql = "from CrmCustomerIndustries where user_id = "+user_id+" order by nums desc";
			String hqlcount = "select count(*) from CrmCustomerIndustries where user_id = "+user_id+" order by nums desc";
			list = crmcustomerindustriesdao.findByHqlPage(hql, hqlcount, pb);
		}else if(condition==3){
			String hql = "from CrmCustomerIndustries where user_id = "+user_id+" and name like '%"+conditionValue+"%' order by nums desc";
			String hqlcount = "select count(*) from CrmCustomerIndustries where user_id = "+user_id+" and name like '%"+conditionValue+"%' order by nums desc";
			list = crmcustomerindustriesdao.findByHqlPage(hql, hqlcount, pb);
		}
		return list;
	}
	
	/**个性营销邮查询客户数*/
	public int findCustomerByPersonSaleCounts(int company_id,int user_id,String highSearchValue) throws Exception{
		if(null == highSearchValue || highSearchValue.equals("")){
			return 0;
		}
		String[] conditionArr = highSearchValue.split("@espeed@");
		if(conditionArr.length != 10){
			return 0;
		}
		String classfiyStr = conditionArr[0];//客户分类信息
		String statusStr = conditionArr[1];//客户状态信息
		String fromStr = conditionArr[2];//客户来源信息
		String starStr = conditionArr[3];//客户星级信息
		String productStr = conditionArr[4];//关联产品信息
		String caigouStr = conditionArr[5];//采购偏好信息
		String entrolStr = conditionArr[6];//入库时间信息
		String contactStr = conditionArr[7];//联系时间信息
		String followStr = conditionArr[8];//未跟进时间信息
		String typeStr = conditionArr[9];//客户类型信息,0私海,1公海
		String hql = "select count(*) from YxyCustomerEmail where user_id = "+user_id+" and delete_flag = 0 and current_status != 1 and";
		if(typeStr.equals("1")){//公海客户
			hql = "select count(*) from YxyCustomerEmail where company_id = "+company_id+" and delete_flag = 0 and current_status = 1 and";
		}
		if(!classfiyStr.equals("no")){
			hql += " classify_id in("+classfiyStr+") and";
		} 
		if(!statusStr.equals("no")){
			hql += " customer_status in("+statusStr+") and";
		}
		if(!fromStr.equals("no")){
			hql += " source_from_id in("+fromStr+") and";
		}
		if(!starStr.equals("no")){
			hql += " important_star in("+starStr+") and";
		}
		if(!productStr.equals("no")){
			String[] productArr = productStr.split("@G@");
			String subHql = " (";
			for(String s : productArr){
				subHql += "find_in_set("+s+",prductid) > 0 or ";
			}
			subHql = subHql.substring(0,subHql.lastIndexOf("or ")-1);
			hql += subHql + ") and";
		}
		if(!caigouStr.equals("no")){
			String[] caigouArr = caigouStr.split("@G@");
			int jingzhun = Integer.parseInt(caigouArr[0]);//0:不精准,1：精准
			String customerids = "";
			for(int k = 1;k < caigouArr.length; k++){
				String sql = "";
				if(jingzhun == 1){
					sql = "select customer_id from crm_customer_info where find_in_set('"+StringEscapeUtils.escapeSql(caigouArr[k])+"',most_sourcing_industries) > 0 and user_id = " + user_id;
				}else{
					sql = "select customer_id from crm_customer_info where most_sourcing_industries like '%" + StringEscapeUtils.escapeSql(caigouArr[k]) + "%' and user_id = " + user_id;
				}
				List<Object> objects = crmcustomerinfodao.findBySqlFind(sql);
				for (int i = 0;i < objects.size();i++) {
					customerids += objects.get(i).toString() + ",";
				}
			}
			if(!customerids.equals("")){
				customerids = customerids.substring(0,customerids.length()-1);
				hql += " customer_id in("+customerids+") and";
			}
		}
		if(!entrolStr.equals("no")){
			String[] entrolArr = entrolStr.split("@G@");
			if(entrolArr.length == 1){
				hql += " enrol_time >= '"+entrolArr[0]+" 00:00:00' and";
			}else if(entrolArr.length == 2){
				hql += " enrol_time >= '"+entrolArr[0]+" 00:00:00' and enrol_time <= '"+entrolArr[1]+" 23:59:59' and";
			}
		}
		if(!contactStr.equals("no")){
			String[] contactArr = contactStr.split("@G@");
			if(contactArr.length == 1){
				hql += " last_contact_date >= '"+contactArr[0]+" 00:00:00' and";
			}else if(contactArr.length == 2){
				hql += " last_contact_date >= '"+contactArr[0]+" 00:00:00' and last_contact_date <= '"+contactArr[1]+" 23:59:59' and";
			}
		}
		if(!followStr.equals("no")){
			String[] followArr = followStr.split("@G@");
			int day = Integer.parseInt(followArr[0]);
			if(day != -1){
				String start_time = DateFormat.lastDays(-day);
				hql += " last_contact_date < '"+start_time+" 00:00:00' and";
			}else if(followArr.length == 2){
				String[] timeArr = followArr[1].split(",");
				if(timeArr.length == 1){
					hql += " last_contact_date >= '"+timeArr[0]+" 00:00:00' and";
				}else if(timeArr.length == 2){
					hql += " last_contact_date >= '"+timeArr[0]+" 00:00:00' and last_contact_date <= '"+timeArr[1]+" 23:59:59' and";
				}
			}
		}
		if(hql.equals("select count(*) from YxyCustomerEmail where user_id = "+user_id+" and delete_flag = 0 and current_status != 1 and")
				|| hql.equals("select count(*) from YxyCustomerEmail where company_id = "+company_id+" and delete_flag = 0 and current_status = 1 and")){//说明以上的条件都不适合
			return 0;
		}
		hql = hql.substring(0,hql.length()-3);
		return yxycustomeremaildao.findByHqlCount(hql);
	}
	
	/**个性营销邮查询客户列表*/
	public List<YxyCustomerEmail> findCustomerByPersonSaleList(int company_id,int user_id,String highSearchValue) throws Exception{
		List<YxyCustomerEmail> emails = new ArrayList<YxyCustomerEmail>();
		if(null == highSearchValue || highSearchValue.equals("")){
			return emails;
		}
		String[] conditionArr = highSearchValue.split("@espeed@");
		if(conditionArr.length != 10){
			return emails;
		}
		String classfiyStr = conditionArr[0];//客户分类信息
		String statusStr = conditionArr[1];//客户状态信息
		String fromStr = conditionArr[2];//客户来源信息
		String starStr = conditionArr[3];//客户星级信息
		String productStr = conditionArr[4];//意向产品信息
		String caigouStr = conditionArr[5];//采购偏好信息
		String entrolStr = conditionArr[6];//入库时间信息
		String contactStr = conditionArr[7];//联系时间信息
		String followStr = conditionArr[8];//未跟进时间信息
		String typeStr = conditionArr[9];//客户类型信息,0私海,1公海
		String hql = "from YxyCustomerEmail where user_id = "+user_id+" and delete_flag = 0 and current_status != 1 and";
		if(typeStr.equals("1")){//公海客户
			hql = "from YxyCustomerEmail where company_id = "+company_id+" and delete_flag = 0 and current_status = 1 and";
		}
		if(!classfiyStr.equals("no")){
			hql += " classify_id in("+classfiyStr+") and";
		}
		if(!statusStr.equals("no")){
			hql += " customer_status in("+statusStr+") and";
		}
		if(!fromStr.equals("no")){
			hql += " source_from_id in("+fromStr+") and";
		}
		if(!starStr.equals("no")){
			hql += " important_star in("+starStr+") and";
		}
		if(!productStr.equals("no")){
			String[] productArr = productStr.split("@G@");
			String subHql = " (";
			for(String s : productArr){
				subHql += "find_in_set("+s+",prductid) > 0 or ";
			}
			subHql = subHql.substring(0,subHql.lastIndexOf("or ")-1);
			hql += subHql + ") and";
		}
		if(!caigouStr.equals("no")){
			String[] caigouArr = caigouStr.split("@G@");
			int jingzhun = Integer.parseInt(caigouArr[0]);//0:不精准,1：精准
			String customerids = "";
			for(int k = 1;k < caigouArr.length; k++){
				String sql = "";
				if(jingzhun == 1){
					sql = "select customer_id from crm_customer_info where find_in_set('"+StringEscapeUtils.escapeSql(caigouArr[k])+"',most_sourcing_industries) > 0 and user_id = " + user_id;
				}else{
					sql = "select customer_id from crm_customer_info where most_sourcing_industries like '%" + StringEscapeUtils.escapeSql(caigouArr[k]) + "%' and user_id = " + user_id;
				}
				List<Object> objects = crmcustomerinfodao.findBySqlFind(sql);
				for (int i = 0;i < objects.size();i++) {
					customerids += objects.get(i).toString() + ",";
				}
			}
			if(!customerids.equals("")){
				customerids = customerids.substring(0,customerids.length()-1);
				hql += " customer_id in("+customerids+") and";
			}
		}
		if(!entrolStr.equals("no")){
			String[] entrolArr = entrolStr.split("@G@");
			if(entrolArr.length == 1){
				hql += " enrol_time >= '"+entrolArr[0]+" 00:00:00' and";
			}else if(entrolArr.length == 2){
				hql += " enrol_time >= '"+entrolArr[0]+" 00:00:00' and enrol_time <= '"+entrolArr[1]+" 23:59:59' and";
			}
		}
		if(!contactStr.equals("no")){
			String[] contactArr = contactStr.split("@G@");
			if(contactArr.length == 1){
				hql += " last_contact_date >= '"+contactArr[0]+" 00:00:00' and";
			}else if(contactArr.length == 2){
				hql += " last_contact_date >= '"+contactArr[0]+" 00:00:00' and last_contact_date <= '"+contactArr[1]+" 23:59:59' and";
			}
		}
		if(!followStr.equals("no")){
			String[] followArr = followStr.split("@G@");
			int day = Integer.parseInt(followArr[0]);
			if(day != -1){
				String start_time = DateFormat.lastDays(-day);
				hql += " last_contact_date < '"+start_time+" 00:00:00' and";
			}else if(followArr.length == 2){
				String[] timeArr = followArr[1].split(",");
				if(timeArr.length == 1){
					hql += " last_contact_date >= '"+timeArr[0]+" 00:00:00' and";
				}else if(timeArr.length == 2){
					hql += " last_contact_date >= '"+timeArr[0]+" 00:00:00' and last_contact_date <= '"+timeArr[1]+" 23:59:59' and";
				}
			}
		}
		if(hql.equals("from YxyCustomerEmail where user_id = "+user_id+" and delete_flag = 0 and current_status != 1 and")
				|| hql.equals("from YxyCustomerEmail where company_id = "+company_id+" and delete_flag = 0 and current_status = 1 and")){//说明以上的条件都不适合
			return emails;
		}
		hql = hql.substring(0,hql.length()-3);
		return yxycustomeremaildao.findByHql(hql);
	}
	
	/**获取采购偏好下的客户集合*/
	public List<YxyCustomerEmail> findCustomersByIndustries(int user_id,String caigou)throws Exception{
		List<YxyCustomerEmail> customers = new ArrayList<YxyCustomerEmail>();
		String customerids = "";
		String sql  = "select customer_id from crm_customer_info where most_sourcing_industries like '%" + StringEscapeUtils.escapeSql(caigou) + "%' and user_id = " + user_id;
		List<Object> objects = crmcustomerinfodao.findBySqlFind(sql);
		for (int i = 0;i < objects.size();i++) {
			customerids += objects.get(i).toString() + ",";
		}
		if(!customerids.equals("")){
			customerids = customerids.substring(0,customerids.length()-1);
			String hql = "from YxyCustomerEmail where delete_flag = 0 and current_status != 1 " +
					"and user_id = "+user_id+" and  customer_id in("+customerids+")";
			customers = yxycustomeremaildao.findByHql(hql);
		}
		return customers;
	}
	
	/**获取私海客户数量*/
	public int getMyCustomerCounts(int user_id)throws Exception{
		String sql="SELECT COUNT(*) FROM crm_customer WHERE delete_flag=0 AND user_id = "+user_id+" and current_status!=1";
		return Integer.parseInt(yxycustomeremaildao.findBySqlQuery(sql).get(0).toString());
	}
	
	/**获取公海客户数量*/
	public int getPubilcCustomerCounts(int company_id)throws Exception{
		String sql = "select count(customer_id) from crm_customer where delete_flag = 0 and current_status = 1 and company_id = "+company_id;
		return Integer.parseInt(yxycustomeremaildao.findBySqlQuery(sql).get(0).toString());
	}
	
	private YxyCustomerTypeDao customertypedao;//客户分类dao
	private YxyCustomerEmailDao yxycustomeremaildao;//客户地址dao
	private CrmProcessDao crmprocessdao;//销售进程dao
	private CrmProductDao crmproductdao;//意向产品dao
	private CrmSourcefromDao crmsourcefromdao;//客户来源dao
	private CrmStatusDao crmstatusdao;//客户状态dao
	private SysParamatersValueDao sysparamatersvaluedao;//参数值dao
	private CrmCustomerIndustriesDao crmcustomerindustriesdao;//采购偏好dao
	private CrmCustomerInfoDao crmcustomerinfodao;//阿里巴巴客户dao
	public YxyCustomerTypeDao getCustomertypedao() {
		return customertypedao;
	}
	public void setCustomertypedao(YxyCustomerTypeDao customertypedao) {
		this.customertypedao = customertypedao;
	}
	public YxyCustomerEmailDao getYxycustomeremaildao() {
		return yxycustomeremaildao;
	}
	public void setYxycustomeremaildao(YxyCustomerEmailDao yxycustomeremaildao) {
		this.yxycustomeremaildao = yxycustomeremaildao;
	}
	public CrmProcessDao getCrmprocessdao() {
		return crmprocessdao;
	}
	public void setCrmprocessdao(CrmProcessDao crmprocessdao) {
		this.crmprocessdao = crmprocessdao;
	}
	public CrmProductDao getCrmproductdao() {
		return crmproductdao;
	}
	public void setCrmproductdao(CrmProductDao crmproductdao) {
		this.crmproductdao = crmproductdao;
	}
	public CrmSourcefromDao getCrmsourcefromdao() {
		return crmsourcefromdao;
	}
	public void setCrmsourcefromdao(CrmSourcefromDao crmsourcefromdao) {
		this.crmsourcefromdao = crmsourcefromdao;
	}
	public CrmStatusDao getCrmstatusdao() {
		return crmstatusdao;
	}
	public void setCrmstatusdao(CrmStatusDao crmstatusdao) {
		this.crmstatusdao = crmstatusdao;
	}
	public SysParamatersValueDao getSysparamatersvaluedao() {
		return sysparamatersvaluedao;
	}
	public void setSysparamatersvaluedao(SysParamatersValueDao sysparamatersvaluedao) {
		this.sysparamatersvaluedao = sysparamatersvaluedao;
	}
	public CrmCustomerIndustriesDao getCrmcustomerindustriesdao() {
		return crmcustomerindustriesdao;
	}
	public void setCrmcustomerindustriesdao(
			CrmCustomerIndustriesDao crmcustomerindustriesdao) {
		this.crmcustomerindustriesdao = crmcustomerindustriesdao;
	}
	public CrmCustomerInfoDao getCrmcustomerinfodao() {
		return crmcustomerinfodao;
	}
	public void setCrmcustomerinfodao(CrmCustomerInfoDao crmcustomerinfodao) {
		this.crmcustomerinfodao = crmcustomerinfodao;
	}
}