package com.espeed.service.impl;
 
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.InputStreamReader;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.espeed.dao.YxyCustomerEmailDao;
import com.espeed.dao.YxyMailFolderDao;
import com.espeed.dao.YxyReadingInfoDao;
import com.espeed.dao.YxyReplyInfoDao;
import com.espeed.dao.YxySendMailMasterBaseDao;
import com.espeed.dao.YxyUnsubscribeInfoDao;
import com.espeed.dao.YxyUserAddressDao;
import com.espeed.pojo.YxyMailFolder;
import com.espeed.pojo.YxyUserAddress;
import com.espeed.service.YxyAddressManageService;
import com.espeed.tool.ConfigPath;
import com.espeed.vo.PageBean;
 
/**
 * 程序名称:    	EspeedMail_时速邮箱
 * 程序版本:    	V1.0
 * 作    者:    	深圳市科飞时速网络技术有限公司(0755-88843776)
 * 版权所有:    	深圳市科飞时速网络技术有限公司
 * 技术支持:    	Tech@21gmail.com
 * 单元名称:     邮件营销地址库业务层实现类
 * 开始时间:    	2013.12.24
 * 程 序 员:    	谢勇
 * 最后修改:    
 * 备    注:		如需修改请通知程序员    
 */
public class YxyAddressManageServiceImpl implements YxyAddressManageService{

	/**条件查询地址库(分页)*/
	public List<YxyUserAddress> findAddressByCondition(String loginid,String domain,PageBean pb,int condition,String conditionvalue)throws Exception {
		String hql="from YxyUserAddress where user_loginid='"+loginid+"' and user_domain='"+domain+"'";
		String hqlcount="select count(*) from YxyUserAddress where user_loginid='"+loginid+"' and user_domain='"+domain+"'";
		if(condition==1){//分类查询
			hql+=" and user_addr_type_id="+conditionvalue;
			hqlcount+=" and user_addr_type_id="+conditionvalue;
		}else if(condition==2){//条件查询
			if(null!=conditionvalue&&!"".equals(conditionvalue)&&conditionvalue.length()!=0)
			{
				hql+=" and (user_addr_email like '%"+conditionvalue+"%' or user_addr_name like '%"+conditionvalue+"%')";
				hqlcount+=" and (user_addr_email like '%"+conditionvalue+"%' or user_addr_name like '%"+conditionvalue+"%')";
			}
		}else if(condition==3){//高级查询(点读,发送量,是否回复)
			String[] values=conditionvalue.split("#");
			int sendcondition=Integer.parseInt(values[0]);//发送量
			int clickcondition=Integer.parseInt(values[1]);//点读量
			int replycondition=Integer.parseInt(values[2]);//是否回复
			if(sendcondition>0){
				hql+=" and user_send_num>="+sendcondition;
				hqlcount+=" and user_send_num>="+sendcondition;
			}
			if(clickcondition>0){
				hql+=" and readingnum>="+clickcondition;
				hqlcount+=" and readingnum>="+clickcondition;
			}
			if(replycondition==1){
				hql+=" and replynum>=1";
				hqlcount+=" and replynum>=1";
			}
		}
		hql+=" order by user_addr_add_date desc,user_addr_modify_date desc";
		hqlcount+=" order by user_addr_add_date desc,user_addr_modify_date desc";
		//查询点读
		//String countsql1="select yxy_reading_email,count(*) from yxy_reading_info where yxy_reading_domain='"+domain+"' and yxy_reading_loginid='"+loginid;
		//List readlist=yxyreadinginfodao.findBySqlQuery(countsql1);
		
		//int count1=yxyreadinginfodao.findCountHql(counthql1);
		//查询地址
		List<YxyUserAddress> list=yxyuseraddressdao.findByHqlPage(hql, hqlcount, pb);
//		for(int i=0;i<list.size();i++){
//			String email=list.get(i).getUser_addr_email();
//			//查询地址点读量
//			String counthql1="select count(*) from YxyReadingInfo where yxy_reading_domain='"+domain+"' and yxy_reading_loginid='"+loginid+"' and yxy_reading_email='"+email+"'";
//			int count1=yxyreadinginfodao.findCountHql(counthql1);
//			list.get(i).setReadingnum(count1);
//			//查询回复量
//			String counthql2="select count(*) from YxyReplyInfo where domain='"+domain+"' and loginid='"+loginid+"' and replyemail='"+email+"'";
//			int count2=yxyreplyinfodao.findCountHql(counthql2);
//			list.get(i).setReplynum(count2);
//		}
		
		return list;
	}
	
	/**地址是否存在*/
	public int findisexits(String loginid, String domain, String email)throws Exception {
		String hql="from YxyUserAddress where user_addr_email='"+email+"' and user_loginid='"+loginid+"' and user_domain='"+domain+"'";
		List<YxyUserAddress> list=yxyuseraddressdao.findByHql(hql);
		if(list.size()>0){
			return 0;
		}else{
			return 1;
		}
	}
	
	/**类别是否满了*/
	public int findtypeisfull(String loginid, String domain, int typeid)throws Exception {
		//类别下最大存放量
		int setmaxnum=ConfigPath.getAddresstypenum();
		//查询此类别下地址量
		String sql="select count(*) from yxy_user_address where user_addr_type_id="+typeid+" and user_loginid='"+loginid+"' and user_domain='"+domain+"'";
		List<Object> result=yxyuseraddressdao.findBySqlQuery(sql);
		int typenum=Integer.parseInt(result.get(0).toString());
		if(typenum>=setmaxnum){
			return 0;
		}else{
			return 1;
		}
	}
	
	/**类别下地址存放剩余量*/
	public int findtypeSurplusNum(String loginid,String domain,int typeid)throws Exception{
		//类别下最大存放量
		int setmaxnum=ConfigPath.getAddresstypenum();
		//查询此类别下地址量
		String sql="select count(*) from yxy_user_address where user_addr_type_id="+typeid+" and user_loginid='"+loginid+"' and user_domain='"+domain+"'";
		List<Object> result=yxyuseraddressdao.findBySqlQuery(sql);
		int typenum=setmaxnum-Integer.parseInt(result.get(0).toString());
		return typenum;
	}
	
	/**类别查询地址*/
	public List<YxyUserAddress> findByTypeid(int typeid,String loginid,String domain) throws Exception {
		String hql="from YxyUserAddress where user_addr_type_id="+typeid+" and user_loginid='"+loginid+"' and user_domain='"+domain+"'";
		return yxyuseraddressdao.findByHql(hql);
	}
	
	/**类别状态查询地址(点读,回复)*/
	public List<YxyUserAddress> findByTypeidStatus(int typeid,int status,String loginid,String domain)throws Exception{
		String hql="";
		if(status==1){//点读
			hql="from YxyUserAddress where user_addr_type_id="+typeid+" and user_loginid='"+loginid+"' and user_domain='"+domain+"' and readingnum>0";
		}else if(status==2){//未点读
			hql="from YxyUserAddress where user_addr_type_id="+typeid+" and user_loginid='"+loginid+"' and user_domain='"+domain+"' and readingnum=0";
		}else if(status==3){//回复
			hql="from YxyUserAddress where user_addr_type_id="+typeid+" and user_loginid='"+loginid+"' and user_domain='"+domain+"' and replynum>0";
		}else if(status==4){//未回复
			hql="from YxyUserAddress where user_addr_type_id="+typeid+" and user_loginid='"+loginid+"' and user_domain='"+domain+"' and replynum=0";
		}
		return yxyuseraddressdao.findByHql(hql);
	}
	
	/**地址库最大存放剩余量*/
	public int findaddressisfull(String loginid, String domain)throws Exception {
		//配置的最大存放量
		int setmaxnum=ConfigPath.getAddressmaxnum();
		//查询用户下地址已存在量
		String sql="select count(*) from yxy_user_address where user_loginid='"+loginid+"' and user_domain='"+domain+"'";
		List result=yxyuseraddressdao.findBySqlQuery(sql);
		int emailnum=Integer.parseInt(result.get(0).toString());
		
		return setmaxnum-emailnum;
	}
	
	/**添加编辑地址*/
	public void addeditaddress(YxyUserAddress o) throws Exception {
		if(o.getUser_addr_id()>0){
			yxyuseraddressdao.updatePojo(o);
		}else{
			yxyuseraddressdao.addPojo(o);
		}
	}
	
	/**地址库最大存放量*/
	public int findmaxnum(String loginid, String domain) throws Exception {
		String sql="select count(*) from yxy_user_address where user_loginid='"+loginid+"' and user_domain='"+domain+"'";
		List<Object> result=yxyuseraddressdao.findBySqlQuery(sql);
		int emailnum=Integer.parseInt(result.get(0).toString());
		return emailnum;
	}

	/**类别下的地址量*/
	public int findtypeaddressnum(String loginid, String domain, int typeid)throws Exception {
		String sql="select count(*) from yxy_user_address where user_loginid='"+loginid+"' and user_domain='"+domain+"' and user_addr_type_id="+typeid;
		List<Object> result=yxyuseraddressdao.findBySqlQuery(sql);
		int emailnum=Integer.parseInt(result.get(0).toString());
		return emailnum;
	}
	
	/**查询用户地址库*/
	public List<Object> findUserAllAddress(String loginid, String domain)throws Exception {
		String sql="select user_addr_id,user_addr_email,user_send_time from yxy_user_address where user_loginid='"+loginid+"' and user_domain='"+domain+"'";
		List<Object> list=yxyuseraddressdao.findBySqlQuery(sql);
		return list;
	}
	
	/**查询用户地址库*/
	public List<Object> findAddressTypeid(String loginid,String domain,int typeid)throws Exception {
		String sql="select user_addr_email,user_send_time from yxy_user_address " +
				"where user_addr_type_id="+typeid+" and user_loginid = '"+loginid+"' and user_domain = '"+domain+"'";
		List<Object> list=yxyuseraddressdao.findBySqlQuery(sql);
		return list;
	}
	
	/**批量添加地址库*/
	public void addAddresspi(List<YxyUserAddress> o,int mailid) throws Exception {
		yxyuseraddressdao.addPojoPi(o);
		if(mailid>0){
			//修改邮件为已入地址库
			String hql="update YxySendMailMasterBase set isaddaddress=1 where send_mail_id="+mailid;
			yxysendmailmasterbasedao.updateByHql(hql);
		}
	}
	
	/**删除地址*/
	public void delYxyAddress(String loginID, String domain, String addressidstr)throws Exception {
		String hql="delete YxyUserAddress where user_loginid='"+loginID+"' and user_domain='"+domain+"' and user_addr_id in ("+addressidstr+")";
		yxyuseraddressdao.updateByHql(hql);
		
	}
	/**删除类别下的地址*/
	public void delYxyAddressByType(String loginID, String domain,int typeid) throws Exception{
		String hql="delete YxyUserAddress where user_loginid='"+loginID+"' and user_domain='"+domain+"' and user_addr_type_id in ("+typeid+")";
		yxyuseraddressdao.updateByHql(hql);
	}
	
	/**更新地址所属类别*/
	public void updateAddressType(String loginid,String domain,String addressid, int typeid)throws Exception {
		String hql="update YxyUserAddress set user_addr_type_id="+typeid+" where user_addr_id in ("+addressid+")";
		yxyuseraddressdao.updateByHql(hql);
	}
	/**根据分组id和类别id进行查询*/
	public List<YxyUserAddress> findAddressType(String loginid, String domain,String addressid, int typeid) throws Exception {
		String hql = "from YxyUserAddress where user_loginid='"+loginid+"' and user_domain='"+domain+"' and user_addr_type_id="+typeid+" and user_addr_id in ("+addressid+")";
		return yxyuseraddressdao.findByHql(hql);
	}
	/**多地址ID查询*/
	public List<YxyUserAddress> findByManyAddr(String id) throws Exception {
		String hql="from YxyUserAddress where user_addr_id in ("+id+")";
		return yxyuseraddressdao.findByHql(hql);
	}
	
	/**将成功发送地址加入到地址库*/
	public int addAddressList(String loginid,String domain,String txtpath,int typeid,String typename,int check,int mailid) throws Exception {
		//当前时间
		SimpleDateFormat df=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String nowdate=df.format(new Date());
		//建立新分类
		if(typeid>0){
			YxyMailFolder folder=new YxyMailFolder();
			folder.setCompany_domain(domain);
			folder.setUser_loginid(loginid);
			folder.setCreate_timer(nowdate);
			folder.setFolder_name(typename);
			folder.setSend_mail_count(0);
			
			typeid=yxymailfolderdao.addPojo(folder);
		}
		//项目路进	
		String pathew = this.getClass().getClassLoader().getResource("/").getPath();
		pathew = pathew.substring(1, pathew.length());
		pathew = pathew.replace("/WEB-INF/classes/","");
		String txtfilepath="userSaveFile/"+domain+"/"+loginid+"/"+txtpath;
		//判断文件是否存在
		File f=new File(pathew+"/"+txtfilepath);
		if(f.exists()){
			List<YxyUserAddress> addresslist=new ArrayList<YxyUserAddress>();
			//获取文件内容
			InputStreamReader in = new InputStreamReader(new FileInputStream(f), "UTF-8");
			BufferedReader bufread = new BufferedReader(in);
			String line_record ="";
			while((line_record = bufread.readLine())!=null){
				int isexits=0;
				YxyUserAddress addr = new YxyUserAddress();  
				//已存在的地址
				String hql="from YxyUserAddress where user_loginid='"+loginid+"' and user_domain='"+domain+"'";
				List<YxyUserAddress> list=yxyuseraddressdao.findByHql(hql);
				for(int i=0;i<list.size();i++){
					if(line_record.equals(list.get(i).getUser_addr_email())){
						if(check==1){//修改历史地址的类别
							list.get(i).setUser_addr_type_id(typeid);
							addr=list.get(i);
							addresslist.add(addr);
							isexits=1;
							continue;
						}
					}
				}
				//不存在
				if(isexits==0){
					addr.setUser_addr_email(line_record);//地址
					addr.setUser_addr_type_id(typeid);//所属类别ID
					addr.setUser_addr_name("");//姓名
					addr.setUser_addr_add_date(nowdate);//建立时间
					addr.setUser_loginid(loginid);//用户账号
					addr.setUser_domain(domain);//用户所属域名
					addr.setUser_addr_sex("男");//性别
					addresslist.add(addr);
				}
			}
			
			//插入数据库
			yxyuseraddressdao.addPojoPi(addresslist);
			
			//修改邮件是否加入了地址库
			String updatehql="update YxySendMailMasterBase set isaddaddress=1 where send_mail_id="+mailid;
			yxyuseraddressdao.updateByHql(updatehql);
			
			return 1;
		}else{
			return -1;
		}
		
	}
	
	/**在发一次立即整理(未点读、未回复、未退订)*/
	public int zhengliaddress(String loginid, String domain,String txtpath,String mailuid) throws Exception {
		//项目路进	
		String pathew = this.getClass().getClassLoader().getResource("/").getPath();
		pathew = pathew.substring(1, pathew.length());
		pathew = pathew.replace("/WEB-INF/classes/","");
		String txtfilepath="userSaveFile/"+domain+"/"+loginid+"/"+txtpath;
		//判断文件是否存在
		File f=new File(pathew+"/"+txtfilepath);
		if(f.exists()){
			//查询此邮件的点读地址
			String clicksql="select yxy_reading_email from yxy_reading_info where yxy_reading_loginid='"+loginid+"' and yxy_reading_domain='"+domain+"' and yxy_reading_mailuid='"+mailuid+"'";
			List<Object> clicklist= yxyreadinginfodao.findBySqlQuery(clicksql);
			//查询该用户的退订
			String sql="select unsubscribe_email from yxy_unsubscribe_info where unsubscribe_loginid='"+loginid+"' and unsubscribe_domain='"+domain+"'";
			List<Object> unlist=yxyunsubscribeinfodao.findBySqlQuery(sql);
			//查询该邮件的回复
			
			//获取地址
			//获取文件内容
			InputStreamReader in = new InputStreamReader(new FileInputStream(f), "UTF-8");
			BufferedReader bufread = new BufferedReader(in);
			String line_record ="";
			StringBuffer writestr=new StringBuffer();
			while((line_record = bufread.readLine())!=null){
				for(int i=0;i<clicklist.size();i++){
					if(line_record.equals(clicklist.get(i))){
						writestr.append(line_record+"\r\n");
						break;
					}
				}
				for(int j=0;j<unlist.size();j++){
					if(line_record.equals(unlist.get(j))){
						writestr.append(line_record+"\r\n");
						break;
					}
				}	
			}
			//写入文件
			FileWriter w = new FileWriter(txtfilepath, true);
			w.write(writestr.toString());
			w.close();
			
			//修改此邮件为已整理状态
			String updatehql="update YxySendMailMasterBase set isarrange=1 where mail_uid='"+mailuid+"'";
			yxyuseraddressdao.updateByHql(updatehql);
		}else{
			return -1;
		}
		return 0;
	}
	
	/**查询详细信息*/
	public YxyUserAddress findaddressinfo(int addressid) throws Exception {
		String hql="from YxyUserAddress where user_addr_id="+addressid;
		List<YxyUserAddress> list=yxyuseraddressdao.findByHql(hql);
		if(list.size()>0){
			return list.get(0);
		}else{
			return null;
		}
	}
	
	/**查询企业下CRM库是否存在*/
	public int findcrmisexits(int company_id,String email)throws Exception{
		String sql = "select count(*) from crm_linkman where company_id = "+company_id+
				" and email = '"+email+"' and customer_id > 0";
		List<Object> objects = yxycustomeremaildao.findBySqlQuery(sql);
		if(objects.size() > 0){
			if(null!=objects.get(0)){
				if(Integer.parseInt(objects.get(0).toString())>0){
					return 2;//存在
				}else{
					return 1;//不存在
				}
			}else{
				return 1;//不存在
			}
		}else{
			return 1;//不存在
		}
	}
	
	private YxyUserAddressDao yxyuseraddressdao;//地址dao
	private YxyReadingInfoDao yxyreadinginfodao;//点读dao
	private YxyReplyInfoDao yxyreplyinfodao;//回复dao
	private YxyUnsubscribeInfoDao yxyunsubscribeinfodao;//退订dao
	private YxyMailFolderDao yxymailfolderdao;//文件类别dao
	private YxySendMailMasterBaseDao yxysendmailmasterbasedao;//邮件dao
	private YxyCustomerEmailDao yxycustomeremaildao;//客户dao
	public YxyUserAddressDao getYxyuseraddressdao() {
		return yxyuseraddressdao;
	}
	public void setYxyuseraddressdao(YxyUserAddressDao yxyuseraddressdao) {
		this.yxyuseraddressdao = yxyuseraddressdao;
	}
	public YxyUnsubscribeInfoDao getYxyunsubscribeinfodao() {
		return yxyunsubscribeinfodao;
	}
	public void setYxyunsubscribeinfodao(YxyUnsubscribeInfoDao yxyunsubscribeinfodao) {
		this.yxyunsubscribeinfodao = yxyunsubscribeinfodao;
	}
	public YxyMailFolderDao getYxymailfolderdao() {
		return yxymailfolderdao;
	}
	public void setYxymailfolderdao(YxyMailFolderDao yxymailfolderdao) {
		this.yxymailfolderdao = yxymailfolderdao;
	}
	public YxyReadingInfoDao getYxyreadinginfodao() {
		return yxyreadinginfodao;
	}
	public void setYxyreadinginfodao(YxyReadingInfoDao yxyreadinginfodao) {
		this.yxyreadinginfodao = yxyreadinginfodao;
	}
	public YxyReplyInfoDao getYxyreplyinfodao() {
		return yxyreplyinfodao;
	}
	public void setYxyreplyinfodao(YxyReplyInfoDao yxyreplyinfodao) {
		this.yxyreplyinfodao = yxyreplyinfodao;
	}
	public YxySendMailMasterBaseDao getYxysendmailmasterbasedao() {
		return yxysendmailmasterbasedao;
	}
	public void setYxysendmailmasterbasedao(
			YxySendMailMasterBaseDao yxysendmailmasterbasedao) {
		this.yxysendmailmasterbasedao = yxysendmailmasterbasedao;
	}
	public YxyCustomerEmailDao getYxycustomeremaildao() {
		return yxycustomeremaildao;
	}
	public void setYxycustomeremaildao(YxyCustomerEmailDao yxycustomeremaildao) {
		this.yxycustomeremaildao = yxycustomeremaildao;
	}
}