package com.espeed.service.impl;
 
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.text.SimpleDateFormat;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.espeed.centre.pojo.YxyUserInfo;
import com.espeed.dao.YxyMailCountDao;
import com.espeed.dao.YxyMailDayCountDao;
import com.espeed.dao.YxyMailMonthCountDao;
import com.espeed.dao.YxyReadingCountDao;
import com.espeed.dao.YxyReadingInfoDao;
import com.espeed.dao.YxyReplyInfoDao;
import com.espeed.dao.YxySendMailDetailDao;
import com.espeed.dao.YxySendMailMasterBaseDao;
import com.espeed.dao.YxySysParamaterDao;
import com.espeed.dao.YxyUnsubscribeInfoDao;
import com.espeed.dao.YxyUserInfoDao;
import com.espeed.pojo.YxySendMailDetail;
import com.espeed.pojo.YxySendMailMasterBase;
import com.espeed.service.YxySendMailMasterBaseService;
import com.espeed.tool.DateFormat;
import com.espeed.tool.createAndDeleteFile;
import com.espeed.vo.PageBean;

/**
 * 程序名称:    	EspeedMail_时速邮箱
 * 程序版本:    	V1.0
 * 作    者:    	深圳市科飞时速网络技术有限公司(0755-88843776)
 * 版权所有:    	深圳市科飞时速网络技术有限公司
 * 技术支持:    	Tech@21gmail.com
 * 单元名称:     邮件营销带发列表业务层实现类
 * 开始时间:    	2013.12.24
 * 程 序 员:    	谢勇
 * 最后修改:    
 * 备    注:		如需修改请通知程序员    
 */
public class YxySendMailMasterBaseServiceImpl implements YxySendMailMasterBaseService{
	
	/**通过条件查询邮件信息*/
	public List<YxySendMailMasterBase> findMailCondition(YxyUserInfo user, PageBean pb, int status,
			int condition,String conditionvalue) throws Exception {
		String hql="from YxySendMailMasterBase where status="+status;
		String hqlcount="select count(*) from YxySendMailMasterBase where status="+status;
		if(condition==0){//默认查询用户邮件列表
			hql+=" and user_loginid='"+user.getLogin_id()+"' and user_domain='"+user.getDomain()+"'";
			hqlcount+=" and user_loginid='"+user.getLogin_id()+"' and user_domain='"+user.getDomain()+"'";
		}else if(condition==1){//用户分类查询
			hql+=" and user_loginid='"+user.getLogin_id()+"' and user_domain='"+user.getDomain()+"' and has_classify="+conditionvalue;
			hqlcount+=" and user_loginid='"+user.getLogin_id()+"' and user_domain='"+user.getDomain()+"' and has_classify="+conditionvalue;
		}else if(condition==2){//域下用户邮件列表
			if(user.getUser_role()==1)//系统管理员
			{
				hql+=" and user_domain='"+user.getDomain()+"'";
				hqlcount+=" and user_domain='"+user.getDomain()+"'";
			}
			else if(user.getUser_role()==2){//部门管理员
				String ids = "";
				//获取子部门
				String sql="select dept_id from centre_department where parent_dept_id = "+user.getDept_id();
				List<Object> objects = yxyuserinfodao.findBySqlQuery(sql);
				if(objects.size() > 0){
					for(Object obj : objects){
						ids += obj.toString() + ",";
					}
					ids += String.valueOf(user.getDept_id());
				}else{
					ids = String.valueOf(user.getDept_id());
				}
				String hql1="from YxyUserInfo where company_id="+user.getCompany_id()+" and dept_id in("+ids+") ORDER BY enrol_time desc";
				List<YxyUserInfo> users = yxyuserinfodao.findByHql(hql1);
				String loginids = "";
				for(YxyUserInfo pojo : users){
					loginids += "'"+pojo.getLogin_id()+"'"+",";
				}
				loginids=loginids.substring(0,loginids.length()-1);
				hql+=" and user_domain='"+user.getDomain()+"' and user_loginid in("+loginids+")";
				hqlcount+=" and user_domain='"+user.getDomain()+"' and user_loginid in("+loginids+")";
			}
			else//非系统管理员查询自己
			{
				hql+=" and user_domain='"+user.getDomain()+"' and user_loginid ='"+user.getLogin_id()+"'";
				hqlcount+=" and user_domain='"+user.getDomain()+"' and user_loginid ='"+user.getLogin_id()+"'";
			}
		}else if(condition==3){//选择用户查询
			hql+=" and user_loginid='"+conditionvalue+"' and user_domain='"+user.getDomain()+"'";
			hqlcount+=" and user_loginid='"+conditionvalue+"' and user_domain='"+user.getDomain()+"'";
		}else if(condition==4){//老客户查询
			if(user.getUser_type()==0){//精跟进(CRM库发送)
				hql+=" and user_loginid='"+user.getLogin_id()+"' and user_domain='"+user.getDomain()+"' and (isold=2 or isold=3 or isold=4)";
				hqlcount+=" and user_loginid='"+user.getLogin_id()+"' and user_domain='"+user.getDomain()+"' and (isold=2 or isold=3 or isold=4)";
			}else{//准营销(本地库发送)
				hql+=" and user_loginid='"+user.getLogin_id()+"' and user_domain='"+user.getDomain()+"' and isold=1";
				hqlcount+=" and user_loginid='"+user.getLogin_id()+"' and user_domain='"+user.getDomain()+"' and isold=1";
			}
		}else if(condition==5){//泛群发(新客户发送)
			hql+=" and user_loginid='"+user.getLogin_id()+"' and user_domain='"+user.getDomain()+"' and (isold=0 or isold=5 or isold=6)";
			hqlcount+=" and user_loginid='"+user.getLogin_id()+"' and user_domain='"+user.getDomain()+"' and (isold=0 or isold=5 or isold=6)";
		}
		hql+=" and mainid=0 order by create_time desc";
		hqlcount+=" and mainid=0 order by create_time desc";
		List<YxySendMailMasterBase> list=yxysendmailmasterbasedao.findByHqlPage(hql, hqlcount, pb);
		
//		//查询子数据
//		for(int i=0;i<list.size();i++){
//			//邮件ID
//			int mailid=list.get(i).getSend_mail_id();
//			String sonhql="select reading_tol,un_person,reply_mail_count from yxy_send_mail_master_base where mainid="+mailid;
//			List<Object> sonlist=yxysendmailmasterbasedao.findBySqlQuery(sonhql);
//			//累加
//			if(sonlist!=null&&sonlist.size()>0){
//				int readnum=0;
//				int unnum=0;
//				int replynum=0;
//				for(int j=0;j<sonlist.size();j++){
//					Object[] obj=(Object[]) sonlist.get(j);
//					readnum+=Integer.parseInt(obj[0].toString());
//					unnum+=Integer.parseInt(obj[1].toString());
//					replynum+=Integer.parseInt(obj[2].toString());
//				}
//				list.get(i).setReading_tol(list.get(i).getReading_tol()+readnum);
//				list.get(i).setUn_person(list.get(i).getUn_person()+unnum);
//				list.get(i).setReply_mail_count(list.get(i).getReply_mail_count()+replynum);
//			}
//		}
		return list;
	}
	
	/**查询邮件详细信息(根据邮件ID)*/
	public YxySendMailMasterBase findMasterBaseByID(int mailid)throws Exception {
		String hql="from YxySendMailMasterBase where send_mail_id="+mailid;
		List<YxySendMailMasterBase> list=yxysendmailmasterbasedao.findByHql(hql);
		if(list.size()>0){
			return list.get(0);
		}else{
			return null;
		}
	}
	
	/**地址整理*/
	public int updatememberaddress(String loginid, String domain,int mailid) throws Exception {		
		//返回值
		int result=0;
		//项目路径	
		String pathew = this.getClass().getClassLoader().getResource("/").getPath();
		pathew = pathew.substring(1, pathew.length());
		pathew = pathew.replace("/WEB-INF/classes/","");
		//获取邮件信息
		//String hql="from YxySendMailMasterBase where send_mail_id="+mailid;
		String hql="from YxySendMailMasterBase where send_mail_id="+mailid+" or mainid= "+mailid;
		//YxySendMailMasterBase mailinfo=yxysendmailmasterbasedao.findByHql(hql).get(0);
		//当前邮件包括主邮件和在发一次的邮件
		List<YxySendMailMasterBase> mailinfo =yxysendmailmasterbasedao.findByHql(hql);
		//遍历
		for (int i = 0; i < mailinfo.size(); i++) {
			//获取邮件文件
			String txtpath = mailinfo.get(i).getTxtaddresspath();
			//邮件唯一码
			String mailuid = mailinfo.get(i).getMail_uid();
			//收件人数
			int mailnum = mailinfo.get(i).getSend_num();
			//组装路径
			String txtfilepath="userSaveFile/"+domain+"/"+loginid+"/"+txtpath;
			//判断文件是否存在
			File f=new File(pathew+"/"+txtfilepath);
			
		//获取邮件文件
//		String txtpath=mailinfo.getTxtaddresspath();
//		//邮件唯一码
//		String mailuid=mailinfo.getMail_uid();
//		//收件人数
//		int mailnum=mailinfo.getSend_num();
//		//组装路径
//		String txtfilepath="userSaveFile/"+domain+"/"+loginid+"/"+txtpath;
		//判断文件是否存在
//		File f=new File(pathew+"/"+txtfilepath);
			
		if(f.exists()){
			//判断txt还是excel
			String[] typeArry=txtpath.split("\\.");
			String typename=typeArry[typeArry.length-1];
			if("txt".equals(typename)){
				int yesnum=0;
				int yes=0;
				int no=0;
				//获取该邮件点读,回复,退订
				String sql1="select yxy_reading_email from yxy_reading_info where yxy_reading_mailuid='"+mailuid+"'";
				List<Object> reademail=yxysendmailmasterbasedao.findBySqlQuery(sql1);
				//回复
				String sql2="select replyemail from yxy_reply_info where mailuid='"+mailuid+"'";
				List<Object> replyemail=yxysendmailmasterbasedao.findBySqlQuery(sql2);
				//退订
				String sql3="select unsubscribe_email from yxy_unsubscribe_info where unsubscribe_mail_uid='"+mailuid+"'";
				List<Object> unemail=yxysendmailmasterbasedao.findBySqlQuery(sql3);
				
				//当前时间命名
				SimpleDateFormat dfs=new SimpleDateFormat("yyyyMMddHHmmssSSS");
				String nowFilename=dfs.format(new Date());
				String filename=nowFilename+".xls";
				//有点读退订回复的
				String yessavepath=pathew+"\\userSaveFile\\"+domain+"\\"+loginid+"\\"+"y"+filename;
				//无点读退订回复的
				String nosavepath=pathew+"\\userSaveFile\\"+domain+"\\"+loginid+"\\"+"n"+filename;
				//生成文件
				File file1=new File(pathew+"\\userSaveFile\\"+domain+"\\"+loginid);
				file1.mkdirs();
				
				//生成excel没点读的
				HSSFWorkbook workbook = new HSSFWorkbook();
				HSSFSheet sheet = workbook.createSheet("email");//在Excel工作簿中建一工作表
				HSSFCell cell = null;//创建列
				HSSFRow row =null;//创建行
				row = sheet.createRow((short) 0);
				//加入文件头
				String [] str={"E-mail","姓名","公司名称","自定义变量"};
				for(int begin=0;begin<str.length;begin++){
					cell = row.createCell((short) begin);
					cell.setEncoding(HSSFCell.ENCODING_UTF_16);//如果内容有中文件，则要设置这里
					cell.setCellType(HSSFCell.CELL_TYPE_STRING);// 定义单元格为字符串类型
					cell.setCellValue(str[begin]);
				}
				
				
				//生成excel有点读的
				HSSFWorkbook workbook1 = new HSSFWorkbook();
				HSSFSheet sheet1 = workbook1.createSheet("email");//在Excel工作簿中建一工作表
				HSSFCell cell1 = null;//创建列
				HSSFRow row1 =null;//创建行
				row1 = sheet1.createRow((short) 0);
				//加入文件头
				String [] str1={"E-mail","姓名","公司名称","自定义变量"};
				for(int begin=0;begin<str1.length;begin++){
					cell1 = row1.createCell((short) begin);
					cell1.setEncoding(HSSFCell.ENCODING_UTF_16);//如果内容有中文件，则要设置这里
					cell1.setCellType(HSSFCell.CELL_TYPE_STRING);// 定义单元格为字符串类型
					cell1.setCellValue(str1[begin]);
				}
				//获取文件内容
				InputStreamReader in = new InputStreamReader(new FileInputStream(f), "UTF-8");
				BufferedReader bufread = new BufferedReader(in);
				String email ="";
				int line=0;
				while((email = bufread.readLine())!=null){
					line++;
					if(line==1){
						continue;
					}
					//去掉空格
					email=email.trim();
					int unisexist=Collections.frequency(unemail,email);
					int replyisexist=Collections.frequency(replyemail,email);
					int readisexist=Collections.frequency(reademail,email);
					
					if(unisexist==0&&replyisexist==0&&readisexist==0){//没点读的
						row = sheet.createRow((short)yes+1);
						yes++;
						for(int s = 0; s < 4; s++) {
							  cell = row.createCell((short)s);//创建列
							  cell.setEncoding(HSSFCell.ENCODING_UTF_16);//如果内容有中文件，则要设置这里
							  cell.setCellType(HSSFCell.CELL_TYPE_STRING);// 定义单元格为字符串类型
							  //在单元格中输入一些内容
							  if(s==0){//email
							      cell.setCellValue(email);
							  }else if(s==1){//姓名
								  cell.setCellValue("");
							  }else if(s==2){//性别
								  cell.setCellValue("");
							  }else if(s==3){
								  cell.setCellValue("");
							  }
							}  
					}else{//有点读的
						yesnum++;
						row1= sheet1.createRow((short)no+1);
						no++;
						//赋值
						for(int s = 0; s < 4; s++) {
							cell1 = row1.createCell((short)s);//创建列
							cell1.setEncoding(HSSFCell.ENCODING_UTF_16);//如果内容有中文件，则要设置这里
							cell1.setCellType(HSSFCell.CELL_TYPE_STRING);// 定义单元格为字符串类型
							 //在单元格中输入一些内容
							 if(s==0){//email
								 cell1.setCellValue(email);
							 }else if(s==1){//姓名
								 cell1.setCellValue("");
							 }else if(s==2){//性别
								 cell1.setCellValue("");
							 }else if(s==3){
								 cell1.setCellValue("");
							 }
						}  
					}
				}
				// 新建一输出文件流
				FileOutputStream fOut1 = new FileOutputStream(nosavepath);		
				workbook.write(fOut1);
				fOut1.flush();
				fOut1.close();
				
				// 新建一输出文件流
				FileOutputStream fOut2 = new FileOutputStream(yessavepath);		
				workbook1.write(fOut2);
				fOut2.flush();
				fOut2.close();
				
				//更新邮件信息
				String updatehql="update YxySendMailMasterBase set isarrange=1,membernum="+(yesnum)+", txtaddresspath='y"+filename+"',txtmemberpath='n"+filename+"' where send_mail_id="+mailid+" or mainid= "+mailid;
				yxysendmailmasterbasedao.updateByHql(updatehql);
				if(yesnum==0){
					result=-2;
				}else{
					result=1;
				}
			}else{
				int yesnum=0;
				int yes=0;
				int no=0;
				//获取该邮件点读,回复,退订
				String sql1="select yxy_reading_email from yxy_reading_info where yxy_reading_mailuid='"+mailuid+"'";
				List<Object> reademail=yxysendmailmasterbasedao.findBySqlQuery(sql1);
				//回复
				String sql2="select replyemail from yxy_reply_info where mailuid='"+mailuid+"'";
				List<Object> replyemail=yxysendmailmasterbasedao.findBySqlQuery(sql2);
				//退订
				String sql3="select unsubscribe_email from yxy_unsubscribe_info where unsubscribe_mail_uid='"+mailuid+"'";
				List<Object> unemail=yxysendmailmasterbasedao.findBySqlQuery(sql3);
				
				//当前时间命名
				SimpleDateFormat dfs=new SimpleDateFormat("yyyyMMddHHmmssSSS");
				String nowFilename=dfs.format(new Date());
				String filename=nowFilename+".xls";
				//有点读退订回复的
				String yessavepath=pathew+"\\userSaveFile\\"+domain+"\\"+loginid+"\\"+"y"+filename;
				//无点读退订回复的
				String nosavepath=pathew+"\\userSaveFile\\"+domain+"\\"+loginid+"\\"+"n"+filename;
				//生成文件
				File file1=new File(pathew+"\\userSaveFile\\"+domain+"\\"+loginid);
				file1.mkdirs();
				
				//生成excel没点读的
				HSSFWorkbook workbook = new HSSFWorkbook();
				HSSFSheet sheet = workbook.createSheet("email");//在Excel工作簿中建一工作表
				HSSFCell cell = null;//创建列
				HSSFRow row =null;//创建行
				row = sheet.createRow((short) 0);
				//加入文件头
				String [] str={"E-mail","姓名","公司名称","自定义变量"};
				for(int begin=0;begin<str.length;begin++){
					cell = row.createCell((short) begin);
					cell.setEncoding(HSSFCell.ENCODING_UTF_16);//如果内容有中文件，则要设置这里
					cell.setCellType(HSSFCell.CELL_TYPE_STRING);// 定义单元格为字符串类型
					cell.setCellValue(str[begin]);
				}
				
				
				//生成excel有点读的
				HSSFWorkbook workbook1 = new HSSFWorkbook();
				HSSFSheet sheet1 = workbook1.createSheet("email");//在Excel工作簿中建一工作表
				HSSFCell cell1 = null;//创建列
				HSSFRow row1 =null;//创建行
				row1 = sheet1.createRow((short) 0);
				//加入文件头
				String [] str1={"E-mail","姓名","公司名称","自定义变量"};
				for(int begin=0;begin<str1.length;begin++){
					cell1 = row1.createCell((short) begin);
					cell1.setEncoding(HSSFCell.ENCODING_UTF_16);//如果内容有中文件，则要设置这里
					cell1.setCellType(HSSFCell.CELL_TYPE_STRING);// 定义单元格为字符串类型
					cell1.setCellValue(str1[begin]);
				}
				
				//获取文件内容excel
				InputStream in=new FileInputStream(f);
				HSSFWorkbook wb = new HSSFWorkbook(in);
				HSSFSheet getsheet = wb.getSheetAt(0);//读取第一个工作表
				for(int j = 1; j < getsheet.getLastRowNum() + 1; j++){
					HSSFRow getrow = getsheet.getRow(j);
					String readinfo="";
					if(getrow!=null){
						for(int k = 0; k < getrow.getLastCellNum(); k++){
							HSSFCell getcell = getrow.getCell((short) k);
							if(getcell!=null){
								if (getcell.getCellType() == HSSFCell.CELL_TYPE_STRING){
									//返回字符串类型的值
									readinfo += String.valueOf(getcell.getStringCellValue()+",");
								}else{
									readinfo += ",";
								}
//								if (getcell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN){
//									// 返回布尔类型的值
//									readinfo += String.valueOf(getcell.getBooleanCellValue()+",");			           
//								}else if (getcell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
//									// 返回数值类型的值
//									readinfo +=Integer.valueOf(getcell.getStringCellValue())+",";
//								}else{
//									// 返回字符串类型的值
//									readinfo += String.valueOf(getcell.getStringCellValue()+",");
//								}
							}
						}
						//处理数据
						String email="";
						String name="";
						String company="";
						String selfbl="";
						String[] emailarry=readinfo.split(",");
						if(emailarry==null||emailarry.length<=0){
							continue;
						}
						//去掉空格
						email=emailarry[0].trim();
						if(emailarry.length>=2){
							name=emailarry[1];
						}
						if(emailarry.length>=3){
							company=emailarry[2];
						}
						if(emailarry.length>=4){
							selfbl=emailarry[3];
						}
						int unisexist=Collections.frequency(unemail,email);
						int replyisexist=Collections.frequency(replyemail,email);
						int readisexist=Collections.frequency(reademail,email);
						
						if(unisexist==0&&replyisexist==0&&readisexist==0){//没点读的
							row = sheet.createRow((short)yes+1);
							yes++;
							for(int s = 0; s < 4; s++) {
								  cell = row.createCell((short)s);//创建列
								  cell.setEncoding(HSSFCell.ENCODING_UTF_16);//如果内容有中文件，则要设置这里
								  cell.setCellType(HSSFCell.CELL_TYPE_STRING);// 定义单元格为字符串类型
								  //在单元格中输入一些内容
								  if(s==0){//email
								      cell.setCellValue(email);
								  }else if(s==1){//姓名
									  cell.setCellValue(name);
								  }else if(s==2){//性别
									  cell.setCellValue(company);
								  }else if(s==3){
									  cell.setCellValue(selfbl);
								  }
								}  
						}else{//有点读的
							yesnum++;
							row1= sheet1.createRow((short)no+1);
							no++;
							//赋值
							for(int s = 0; s < 4; s++) {
								cell1 = row1.createCell((short)s);//创建列
								cell1.setEncoding(HSSFCell.ENCODING_UTF_16);//如果内容有中文件，则要设置这里
								cell1.setCellType(HSSFCell.CELL_TYPE_STRING);// 定义单元格为字符串类型
								 //在单元格中输入一些内容
								 if(s==0){//email
									 cell1.setCellValue(email);
								 }else if(s==1){//姓名
									 cell1.setCellValue(name);
								 }else if(s==2){//性别
									 cell1.setCellValue(company);
								 }else if(s==3){
									 cell1.setCellValue(selfbl);
								 }
							}  
						}
						
					 }
				}
				// 新建一输出文件流
				FileOutputStream fOut1 = new FileOutputStream(nosavepath);		
				workbook.write(fOut1);
				fOut1.flush();
				fOut1.close();
				
				// 新建一输出文件流
				FileOutputStream fOut2 = new FileOutputStream(yessavepath);		
				workbook1.write(fOut2);
				fOut2.flush();
				fOut2.close();
				
				//判断主邮件还是再发一次的邮件 isOld = 0:主邮件  1:再发一次的邮件
				if(mailinfo.get(i).getIsold() == 0){
					//更新邮件信息
					String updatehql="update YxySendMailMasterBase set isarrange=1,membernum="+(yesnum)+", txtaddresspath='y"+filename+"',txtmemberpath='n"+filename+"' where send_mail_id="+mailid;
					yxysendmailmasterbasedao.updateByHql(updatehql);
				}else{
					//更新邮件信息
					String updatehql="update YxySendMailMasterBase set isarrange=1,membernum="+(yesnum)+", txtaddresspath='y"+filename+"',txtmemberpath='n"+filename+"' where mainid= "+mailid;
					yxysendmailmasterbasedao.updateByHql(updatehql);
				}
				
				if(yesnum==0){
					result=-2;
				}else{
					result=1;
				}
			}
			
		}else{
			result=-1;
		}
	}
		return result;
	}	
	
	
	/**查询在发一次下的邮件*/
	public List<YxySendMailMasterBase> findnextmail(int mainid)throws Exception{
		String hql="from YxySendMailMasterBase where send_mail_id="+mainid+" or mainid="+mainid;
		return yxysendmailmasterbasedao.findByHql(hql);
	}
	
	/**查询在发一次最后的时间*/
	public Map<String, Integer> findnexttime(int mainid,String create_time)throws Exception{
//		String hql="from YxySendMailMasterBase where mainid="+mainid+" or send_mail_id="+mainid+" order by create_time desc";
//		List<YxySendMailMasterBase> list= yxysendmailmasterbasedao.findByHqlSet(hql, 1);
//		if(list.size()>0){
//			return list.get(0).getCreate_time();
//		}else{
//			return "";
//		}
		Map<String,Integer> map = new HashMap<String,Integer>();
		//判断邮件间隔时间大于90天的不能再发一次
		String timejian=DateFormat.nDaysAfterOneDateString(DateFormat.getNowDate(),-90);
		//往前推时时间修改为00:00:00而不是08:00:00
		timejian = timejian.split(" ")[0] + " 00:00:00";
		if(create_time.compareTo(timejian)<0){
			map.put("result", -4);//主邮件已经超过90天
			return map;
		}
		//查询当前邮件的所有子邮件
		String hql = "from YxySendMailMasterBase where mainid="+mainid+" order by create_time asc";
		List<YxySendMailMasterBase> list= yxysendmailmasterbasedao.findByHql(hql);
		if(list.size()>0){//有子邮件则需要判断
			if(list.size()==1){//已经再发一次了，现在是第二次再发
				//判断第一次再发一次的时间
				int begin = DateFormat.nDaysBetweenTwoDate(list.get(0).getCreate_time(),DateFormat.getNowDate());
				if(begin >= 3){//第一次再发时间超过3天
					map.put("result", 1);
				}else{
					map.put("result", -3);
				}
			}else{//已经不止再发一次，则需要计算发送量了
				map.put("result", -2);
			}
		}else{//无子邮件则可以再发一次
			int begin = DateFormat.nDaysBetweenTwoDate(create_time,DateFormat.getNowDate());
			if(begin <= 90 && begin >= 3){//3-90天内可再发一次
				map.put("result", 1);
			}else{
				map.put("result", -1);
			}
		}
		map.put("times", list.size()+1);
		return map;
	}
	
	/**通过邮件ID删除邮件*/
	public void delMailByID(int mailid,String emlpath) throws Exception {
		//删除邮件
		String hql1="delete YxySendMailMasterBase where send_mail_id="+mailid;
		yxysendmailmasterbasedao.updateByHql(hql1);
		//删除该地址
		String hql2="delete YxySendMailDetail where base_mail_id="+mailid;
		yxysendmaildetaildao.updateByHql(hql2);
		//删除该文件
		File file=new File(emlpath);
		createAndDeleteFile.deleteFile(file);
	}
	
	/**单封邮件的统计信息*/
	public Map<String, Integer> findSingMailCount(int mailid,String mailuid)throws Exception{
		Map<String, Integer> resultmap=new HashMap<String, Integer>();
		
		//查询邮件总量
		String hql1="select send_num from YxySendMailMasterBase where send_mail_id="+mailid;
		int totle=yxysendmailmasterbasedao.findByHqlCount(hql1);
		resultmap.put("totle", totle);
		//查询邮件待发量
		String hql2="select count(*) from YxySendMailDetail where base_mail_id="+mailid;
		int daicount=yxysendmaildetaildao.findByHqlCount(hql2);
		resultmap.put("dai", daicount);
		//已投递量
		resultmap.put("over", totle-daicount);
		//点读
		String sql1="select count(*),sum(yxy_reading_num) from yxy_reading_info where yxy_reading_mailuid='"+mailuid+"'";
		List<Object> list=yxyreadingcountdao.findBySqlQuery(sql1);
		Object [] obj=(Object[]) list.get(0);
		if(obj[0]!=null&&!"".equals(obj[0])){
			//点读量
			resultmap.put("clickperson",Integer.parseInt(obj[0].toString()));
		}else{
			//点读量
			resultmap.put("clickperson",0);
		}
		if(obj[1]!=null&&!"".equals(obj[1])){
			//点读量
			resultmap.put("clicknum",Integer.parseInt(obj[1].toString()));
		}else{
			resultmap.put("clicknum",0);
		}
		return resultmap;
	}

	/**修改邮件点读量*/
	public void updateMailReadNum()throws Exception{
		//查询点读分组
		String sql="SELECT yxy_reading_mailuid,count(*) FROM yxy_reading_info GROUP BY yxy_reading_mailuid";
		List<Object> readobj=yxysendmailmasterbasedao.findBySqlQuery(sql);
		String mailuid="";
		int readnum=0;
		for(int i=0;i<readobj.size();i++){
			Object[] obj=(Object[]) readobj.get(i);
			if(obj[0]!=null&&"".equals(obj[0])){
				mailuid=obj[0].toString();
				readnum=Integer.parseInt(obj[1].toString());
				
				//更新邮件
				String hql="update YxySendMailMasterBase set reading_tol="+readnum +" where mail_uid='"+mailuid+"'";
				yxysendmailmasterbasedao.updateByHql(hql);
			}
		}
	}
	
	/**根据邮件基本信息IDD查询待发地址*/
	public List<YxySendMailDetail> findAddressByMailID(int basemailID)throws Exception {
		String hql = "from YxySendMailDetail where base_mail_id = "+basemailID;
		List<YxySendMailDetail> detaillist = yxysendmaildetaildao.findByHql(hql);
		return detaillist;
	}

	/**根据邮件基本信息ID导出邮件地址*/
	public List<YxySendMailDetail> exportAddressByMailID(int basemailID)throws Exception {
		String hql = "from YxySendMailDetail where base_mail_id = "+basemailID;
		List<YxySendMailDetail> detail = yxysendmaildetaildao.findByHql(hql);
		return detail;
	}
	
	/**查询邮件详细信息(根据邮件UID)*/
	public YxySendMailMasterBase findMasterBaseByUID(String mailuid)throws Exception{
		String hql="from YxySendMailMasterBase where mail_uid = '"+mailuid+"'";
		List<YxySendMailMasterBase> list=yxysendmailmasterbasedao.findByHql(hql);
		if(list.size()>0){
			return list.get(0);
		}else{
			return null;
		}
	}
	
	private YxyUserInfoDao yxyuserinfodao;
	private YxySendMailMasterBaseDao yxysendmailmasterbasedao;//邮件dao
	private YxySendMailDetailDao yxysendmaildetaildao;//待发地址dao
	private YxyReadingCountDao yxyreadingcountdao;//点击信息dao
	private YxySysParamaterDao yxysysparamaterdao;//控制参数dao
	private YxyMailDayCountDao yxymaildaycountdao;//日统计
	private YxyMailMonthCountDao yxymailmonthcountdao;//月统计
	private YxyMailCountDao yxymailcountdao;//总统计
	private YxyReplyInfoDao yxyreplyinfodao;//回复dao
	private YxyReadingInfoDao yxyreadinginfodao;//点读信息
	private YxyUnsubscribeInfoDao yxyunsubscribeinfodao;//退订dao
	public YxySendMailMasterBaseDao getYxysendmailmasterbasedao() {
		return yxysendmailmasterbasedao;
	}
	public void setYxysendmailmasterbasedao(
			YxySendMailMasterBaseDao yxysendmailmasterbasedao) {
		this.yxysendmailmasterbasedao = yxysendmailmasterbasedao;
	}
	public YxySendMailDetailDao getYxysendmaildetaildao() {
		return yxysendmaildetaildao;
	}
	public void setYxysendmaildetaildao(YxySendMailDetailDao yxysendmaildetaildao) {
		this.yxysendmaildetaildao = yxysendmaildetaildao;
	}
	public YxyReadingCountDao getYxyreadingcountdao() {
		return yxyreadingcountdao;
	}
	public void setYxyreadingcountdao(YxyReadingCountDao yxyreadingcountdao) {
		this.yxyreadingcountdao = yxyreadingcountdao;
	}
	public YxySysParamaterDao getYxysysparamaterdao() {
		return yxysysparamaterdao;
	}
	public void setYxysysparamaterdao(YxySysParamaterDao yxysysparamaterdao) {
		this.yxysysparamaterdao = yxysysparamaterdao;
	}
	public YxyMailDayCountDao getYxymaildaycountdao() {
		return yxymaildaycountdao;
	}
	public void setYxymaildaycountdao(YxyMailDayCountDao yxymaildaycountdao) {
		this.yxymaildaycountdao = yxymaildaycountdao;
	}
	public YxyMailMonthCountDao getYxymailmonthcountdao() {
		return yxymailmonthcountdao;
	}
	public void setYxymailmonthcountdao(YxyMailMonthCountDao yxymailmonthcountdao) {
		this.yxymailmonthcountdao = yxymailmonthcountdao;
	}
	public YxyMailCountDao getYxymailcountdao() {
		return yxymailcountdao;
	}
	public void setYxymailcountdao(YxyMailCountDao yxymailcountdao) {
		this.yxymailcountdao = yxymailcountdao;
	}
	public YxyReplyInfoDao getYxyreplyinfodao() {
		return yxyreplyinfodao;
	}
	public void setYxyreplyinfodao(YxyReplyInfoDao yxyreplyinfodao) {
		this.yxyreplyinfodao = yxyreplyinfodao;
	}
	public YxyReadingInfoDao getYxyreadinginfodao() {
		return yxyreadinginfodao;
	}
	public void setYxyreadinginfodao(YxyReadingInfoDao yxyreadinginfodao) {
		this.yxyreadinginfodao = yxyreadinginfodao;
	}
	public YxyUnsubscribeInfoDao getYxyunsubscribeinfodao() {
		return yxyunsubscribeinfodao;
	}
	public void setYxyunsubscribeinfodao(YxyUnsubscribeInfoDao yxyunsubscribeinfodao) {
		this.yxyunsubscribeinfodao = yxyunsubscribeinfodao;
	}
	public YxyUserInfoDao getYxyuserinfodao() {
		return yxyuserinfodao;
	}
	public void setYxyuserinfodao(YxyUserInfoDao yxyuserinfodao) {
		this.yxyuserinfodao = yxyuserinfodao;
	}
}