package com.espeed.yxy.service.impl;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


import com.espeed.centre.pojo.CentreYxyedmOldcustomer;
import com.espeed.webmail.pojo.YxyCustomerEmail;
import com.espeed.yxy.dao.CentreUserDao;
import com.espeed.yxy.dao.CentreYxyedmOldcustomerDao;
import com.espeed.yxy.dao.CrmCustomerInfoDao;
import com.espeed.yxy.dao.YxyCustomerEmailDao;
import com.espeed.yxy.dao.YxyMailStencilDao;
import com.espeed.yxy.dao.YxyMarketingPlanDao;
import com.espeed.yxy.dao.YxyMarketingWeekDao;
import com.espeed.yxy.dao.YxyParamaterDao;
import com.espeed.yxy.dao.YxySendCountDao;
import com.espeed.yxy.dao.YxySenderSetDao;
import com.espeed.yxy.dao.YxySysParamatersValueDao;
import com.espeed.yxy.dao.YxyUserAddressDao;
import com.espeed.yxy.pojo.YxyMailStencil;
import com.espeed.yxy.pojo.YxyMarketingPlan;
import com.espeed.yxy.pojo.YxySendCount;
import com.espeed.yxy.pojo.YxySenderSet;
import com.espeed.yxy.pojo.YxySysParamaters;
import com.espeed.yxy.pojo.YxySysParamatersValue;
import com.espeed.yxy.pojo.YxyUserAddress;
import com.espeed.yxy.service.YxyMarketingPlanService;
import com.espeed.yxy.tool.DateFormat;
 
public class YxyMarketingPlanServiceImpl implements YxyMarketingPlanService{
	
	/**是否有可执行的营销计划*/
	public List<YxyMarketingPlan> findHavePlan() throws Exception {
		SimpleDateFormat df=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String nowdate=df.format(new Date());
		String nowday=nowdate.split(" ")[0];
		String nowtime=nowdate.split(" ")[1];
		
		//hql查询
		String hql="from YxyMarketingPlan where (plan_status=0 or plan_status=-1) and plan_type=1 and plan_executetime<='"+nowtime+"' and plan_begintime<='"+nowday+"' and plan_endtime>='"+nowday+"'";
		return marketingplandao.findByHql(hql);
	}
	
	/**根据地址分组查询地址*/
	public List<YxyUserAddress> findAddressByGroupid(int typeid,int plan_differen,String loginid,String domain,String other_name) throws Exception {
		//判断是否是本地库发送还是CRM库
		if(plan_differen == 0)//本地库发送
		{
			String hql="from YxyUserAddress where user_addr_type_id="+typeid;
			return addressdao.findByHql(hql);
		}
		else//客户管理发送
		{
			int company_id = 0;
			int user_id = 0;
			List<YxyCustomerEmail> customerEmails = new ArrayList<YxyCustomerEmail>();
			if(plan_differen == 1 || plan_differen == 2){//客户分类
				String sql = "select company_id from centre_company where domain = '"+domain+"'";
				List<Object> objects = centreuserdao.findBySqlFind(sql);
				if(objects.size() > 0 && objects.get(0) != null){
					company_id = Integer.parseInt(objects.get(0).toString());
					sql = "select user_id from centre_user where company_id = "+company_id+" and login_id = '"+loginid+"'";
					objects = centreuserdao.findBySqlFind(sql);
					if(objects.size() > 0 && objects.get(0) != null){
						user_id = Integer.parseInt(objects.get(0).toString());
						String hql = "from YxyCustomerEmail where user_id = "+user_id+" and current_status != 1 " +
								"and delete_flag = 0 and classify_id = "+typeid;
						customerEmails = yxycustomeremaildao.findByHql(hql);
					}
				}
			}else if(plan_differen == 3 || plan_differen == 4){//客户状态
				String sql = "select company_id from centre_company where domain = '"+domain+"'";
				List<Object> objects = centreuserdao.findBySqlFind(sql);
				if(objects.size() > 0 && objects.get(0) != null){
					company_id = Integer.parseInt(objects.get(0).toString());
					sql = "select user_id from centre_user where company_id = "+company_id+" and login_id = '"+loginid+"'";
					objects = centreuserdao.findBySqlFind(sql);
					if(objects.size() > 0 && objects.get(0) != null){
						user_id = Integer.parseInt(objects.get(0).toString());
						String hql = "from YxyCustomerEmail where user_id = "+user_id+" and current_status != 1 " +
								"and delete_flag = 0 and customer_status = "+typeid;
						customerEmails = yxycustomeremaildao.findByHql(hql);
					}
				}
			}else if(plan_differen == 5 || plan_differen == 6){//客户来源
				String sql = "select company_id from centre_company where domain = '"+domain+"'";
				List<Object> objects = centreuserdao.findBySqlFind(sql);
				if(objects.size() > 0 && objects.get(0) != null){
					company_id = Integer.parseInt(objects.get(0).toString());
					sql = "select user_id from centre_user where company_id = "+company_id+" and login_id = '"+loginid+"'";
					objects = centreuserdao.findBySqlFind(sql);
					if(objects.size() > 0 && objects.get(0) != null){
						user_id = Integer.parseInt(objects.get(0).toString());
						String hql = "from YxyCustomerEmail where user_id = "+user_id+" and current_status != 1 " +
								"and delete_flag = 0 and source_from_id = "+typeid;
						customerEmails = yxycustomeremaildao.findByHql(hql);
					}
				}
			}else if(plan_differen == 7 || plan_differen == 8){//意向产品
				String sql = "select company_id from centre_company where domain = '"+domain+"'";
				List<Object> objects = centreuserdao.findBySqlFind(sql);
				if(objects.size() > 0 && objects.get(0) != null){
					company_id = Integer.parseInt(objects.get(0).toString());
					sql = "select user_id from centre_user where company_id = "+company_id+" and login_id = '"+loginid+"'";
					objects = centreuserdao.findBySqlFind(sql);
					if(objects.size() > 0 && objects.get(0) != null){
						user_id = Integer.parseInt(objects.get(0).toString());
						String hql = "from YxyCustomerEmail where user_id = "+user_id+" and current_status != 1 " +
								"and delete_flag = 0 and find_in_set("+typeid+",prductid) > 0";
						customerEmails = yxycustomeremaildao.findByHql(hql);
					}
				}
			}else if(plan_differen == 9 || plan_differen == 10){//采购偏好
				if(other_name != null && !"".equals(other_name)){
					String sql = "select company_id from centre_company where domain = '"+domain+"'";
					List<Object> objects = centreuserdao.findBySqlFind(sql);
					if(objects.size() > 0 && objects.get(0) != null){
						company_id = Integer.parseInt(objects.get(0).toString());
						sql = "select user_id from centre_user where company_id = "+company_id+" and login_id = '"+loginid+"'";
						objects = centreuserdao.findBySqlFind(sql);
						if(objects.size() > 0 && objects.get(0) != null){
							user_id = Integer.parseInt(objects.get(0).toString());
							String customerids = "";
							sql  = "select customer_id from crm_customer_info where most_sourcing_industries like '%" +  other_name + "%' and user_id = " + user_id;
							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+")";
								customerEmails = yxycustomeremaildao.findByHql(hql);
							}
						}
					}
				}
			}else if(plan_differen == 11 || plan_differen == 12){//个性营销
				if(other_name != null && !"".equals(other_name)){
					String sql = "select company_id from centre_company where domain = '"+domain+"'";
					List<Object> objects = centreuserdao.findBySqlFind(sql);
					if(objects.size() > 0 && objects.get(0) != null){
						company_id = Integer.parseInt(objects.get(0).toString());
						sql = "select user_id from centre_user where company_id = "+company_id+" and login_id = '"+loginid+"'";
						objects = centreuserdao.findBySqlFind(sql);
						if(objects.size() > 0 && objects.get(0) != null){
							user_id = Integer.parseInt(objects.get(0).toString());
							String[] conditionArr = other_name.split("@espeed@");
							if(conditionArr.length == 10){ 
								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++){
										sql = "";
										if(jingzhun == 1){
											sql = "select customer_id from crm_customer_info where find_in_set('"+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 '%" + caigouArr[k] + "%' and user_id = " + user_id;
										}
										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")){//说明以上的条件都不适合
									hql = hql.substring(0,hql.length()-3);
									customerEmails = yxycustomeremaildao.findByHql(hql);
								}
							}
						}
					}
				}
			}
			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
			SimpleDateFormat df2 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String nowtime = df2.format(new Date());
			String nowdate = df.format(new Date());
			List<YxyUserAddress> address_list = new ArrayList<YxyUserAddress>();
			for(YxyCustomerEmail customer : customerEmails){
				//只查询主联系人的信息
				String sql = "select full_name,email from crm_linkman where is_main_linkman = 1 and customer_id = "+customer.getCustomer_id();
				List<Object> objects = yxycustomeremaildao.findBySqlQuery(sql);
				if(objects.size() > 0){
					Object[] obj_arr = (Object[])objects.get(0);
					YxyUserAddress address = new YxyUserAddress();
					address.setUser_addr_id(customer.getCustomer_id());//客户id赋值给主键id
					address.setCompany_variable(customer.getCustomer_name());//客户名称
					address.setUser_addr_email(obj_arr[1].toString());//地址
					address.setUser_addr_name(obj_arr[0].toString());//联系人
					address_list.add(address);//添加到集合
					try{
						String hql = "from YxySendCount where user_id = "+user_id+" and customer_id = "+customer.getCustomer_id()+" and send_date = '"+nowdate+"'";
						List<YxySendCount> counts = yxysendcountdao.findByHql(hql);
						if(counts.size() > 0){
							YxySendCount count = counts.get(0);
							count.setSend_counts(count.getSend_counts()+1);
							count.setSend_time(nowtime);
							yxysendcountdao.updatePojo(count);
						}else{
							YxySendCount count = new YxySendCount();
							count.setUser_id(user_id);
							count.setCompany_id(company_id);
							count.setSend_counts(1);
							count.setSend_date(nowdate);
							count.setSend_time(nowtime);
							count.setCustomer_id(customer.getCustomer_id());
							yxysendcountdao.addPojo(count);
						}
					}catch (Exception e) {
						// TODO: handle exception
					}
				}
			}
			return address_list;
		}
	}
	
	/**查询模版*/
	public YxyMailStencil findStencilInfo(int stencilid) throws Exception {
		String hql="from YxyMailStencil where stencil_id="+stencilid;
		List<YxyMailStencil> stencillist=stencildao.findByHql(hql);
		if(stencillist.size()>0){
			return stencillist.get(0);
		}else{
			return null;
		}
	}
	
	/**查询发件人*/
	public YxySenderSet findSender(String loginid,String domain)throws Exception{
		String hql="from YxySenderSet where user_login='"+loginid+"' and user_domain='"+domain+"' and isdefault=1";
		List<YxySenderSet> senderlist=senderdao.findByHql(hql);
		if(senderlist.size()>0){
			return senderlist.get(0);
		}else{
			return null;
		}
	}
	
	/**修改计划周期状态*/
	public void updateWeekStatus(int weekid,int status)throws Exception{
		String hql="update YxyMarketingWeek set isexecution="+status+" where week_id="+weekid;
		marketingweekdao.updateByHql(hql);
	}
	
	/**修改计划状态*/
	public void updatePlanStatus(int planid,int status)throws Exception{
		String hql="update YxyMarketingPlan set plan_status="+status+" where plan_id="+planid;
		marketingplandao.updateByHql(hql);
	}
	
	/**code查询控制参数(有用户级返回用户级,否则返回默认参数)*/
	public String findCompanyParamByCode(String code,String loginid,String domain)throws Exception{
		//查询此code的paramid
		String hql="from YxySysParamaters where para_code='"+code+"'";
		List<YxySysParamaters> paraslist=yxysysparamaterdao.findByHql(hql);
		if(paraslist.size()>0){
			int paramid=paraslist.get(0).getPara_id();
			int level=paraslist.get(0).getOwner_type();//所属等级
			String defaultvalue=paraslist.get(0).getDefault_value();//默认值
			if(level==4){//用户级
				//查询参数值
				String hql1="from YxySysParamatersValue where para_id="+paramid+" and owner_loginid='"+loginid+"' and company_domain='"+domain+"'";
				List<YxySysParamatersValue> paraValue=yxysysparamatersvaluedao.findByHql(hql1);
				if(paraValue.size()>0){
					return paraValue.get(0).getPara_value();
				}else{
					return defaultvalue;
				}
			}else{//系统级
				//查询参数值
				String hql1="from YxySysParamatersValue where para_id="+paramid+" and company_domain='"+domain+"'";
				List<YxySysParamatersValue> paraValue=yxysysparamatersvaluedao.findByHql(hql1);
				if(paraValue.size()>0){
					return paraValue.get(0).getPara_value();
				}else{
					return defaultvalue;
				}
			}
			
		}else{
			return "-1";
		}
	}
	
	/**获取EDM配置-盘活客户配置信息*/
	public CentreYxyedmOldcustomer getOldCusSet(String  domain)throws Exception{
		String hql= "from CentreYxyedmOldcustomer where company_domain = '"+domain+"'";
		List<CentreYxyedmOldcustomer> list = centreyxyedmoldcustomerdao.findByHql(hql);
		if(list.size()>0){
			return list.get(0);
		}else{
			return null;
		}
	}
	
	private YxyMarketingPlanDao marketingplandao;//营销计划dao
	private YxyMarketingWeekDao marketingweekdao;//营销计划周期dao
	private YxyUserAddressDao addressdao;//用户地址dao
	private YxyMailStencilDao stencildao;//模版dao
	private YxySenderSetDao senderdao;//发件人dao
	private YxyParamaterDao yxysysparamaterdao;//控制参数dao
	private YxySysParamatersValueDao yxysysparamatersvaluedao;//控制参数值dao
	private YxyCustomerEmailDao yxycustomeremaildao;//客户邮件dao
	private CentreYxyedmOldcustomerDao centreyxyedmoldcustomerdao;//盘活客户配置dao
	private CentreUserDao centreuserdao;//用户dao
	private CrmCustomerInfoDao crmcustomerinfodao;//阿里巴巴客户dao
	private YxySendCountDao yxysendcountdao;//客户发送邮件的统计dao
	public YxyCustomerEmailDao getYxycustomeremaildao() {
		return yxycustomeremaildao;
	}
	public void setYxycustomeremaildao(YxyCustomerEmailDao yxycustomeremaildao) {
		this.yxycustomeremaildao = yxycustomeremaildao;
	}
	public YxyUserAddressDao getAddressdao() {
		return addressdao;
	}
	public void setAddressdao(YxyUserAddressDao addressdao) {
		this.addressdao = addressdao;
	}
	public YxyMarketingPlanDao getMarketingplandao() {
		return marketingplandao;
	}
	public void setMarketingplandao(YxyMarketingPlanDao marketingplandao) {
		this.marketingplandao = marketingplandao;
	}
	public YxyMarketingWeekDao getMarketingweekdao() {
		return marketingweekdao;
	}
	public void setMarketingweekdao(YxyMarketingWeekDao marketingweekdao) {
		this.marketingweekdao = marketingweekdao;
	}
	public YxyMailStencilDao getStencildao() {
		return stencildao;
	}
	public void setStencildao(YxyMailStencilDao stencildao) {
		this.stencildao = stencildao;
	}
	public YxySenderSetDao getSenderdao() {
		return senderdao;
	}
	public void setSenderdao(YxySenderSetDao senderdao) {
		this.senderdao = senderdao;
	}
	public YxyParamaterDao getYxysysparamaterdao() {
		return yxysysparamaterdao;
	}
	public void setYxysysparamaterdao(YxyParamaterDao yxysysparamaterdao) {
		this.yxysysparamaterdao = yxysysparamaterdao;
	}
	public YxySysParamatersValueDao getYxysysparamatersvaluedao() {
		return yxysysparamatersvaluedao;
	}
	public void setYxysysparamatersvaluedao(
			YxySysParamatersValueDao yxysysparamatersvaluedao) {
		this.yxysysparamatersvaluedao = yxysysparamatersvaluedao;
	}
	public CentreYxyedmOldcustomerDao getCentreyxyedmoldcustomerdao() {
		return centreyxyedmoldcustomerdao;
	}
	public void setCentreyxyedmoldcustomerdao(
			CentreYxyedmOldcustomerDao centreyxyedmoldcustomerdao) {
		this.centreyxyedmoldcustomerdao = centreyxyedmoldcustomerdao;
	}
	public CentreUserDao getCentreuserdao() {
		return centreuserdao;
	}
	public void setCentreuserdao(CentreUserDao centreuserdao) {
		this.centreuserdao = centreuserdao;
	}
	public CrmCustomerInfoDao getCrmcustomerinfodao() {
		return crmcustomerinfodao;
	}
	public void setCrmcustomerinfodao(CrmCustomerInfoDao crmcustomerinfodao) {
		this.crmcustomerinfodao = crmcustomerinfodao;
	}
	public YxySendCountDao getYxysendcountdao() {
		return yxysendcountdao;
	}
	public void setYxysendcountdao(YxySendCountDao yxysendcountdao) {
		this.yxysendcountdao = yxysendcountdao;
	}
}