package yxy.timer.method;

import java.util.List;

import org.apache.commons.lang.StringEscapeUtils;

import yxy.timer.dao.TimerCountYxyDao;
import yxy.timer.dao.YxyMailDayCountDao;
import yxy.timer.dao.YxyReadDao;
import yxy.timer.dao.YxyReplyInfoDao;
import yxy.timer.dao.YxyUnsubscribeDao;
import yxy.timer.pojo.TimerCountYxy;
import yxy.timer.tool.DateFormat;

/**
 * 统计分析定时器
 * @author 陈南巧
 */
public class YxyCountAnalysisTime 
{
	//定时器运行的主方法
	public void counting()
	{
		try
		{
			System.out.println("营销邮统计分析定时器开始，开始时间："+DateFormat.getNowDate());
			
		    //营销邮统计发送总次数与发送总数
		    yxyCountSMailInfo();
		    
		    //营销邮统计点读总数yxyCoutReadMail
		    yxyCountReadMail();
		    
		    //营销邮统计退订总数
		    yxyCountUnsubscribeMail();
		    
		    //营销邮统计回复总数
		    yxyCountReplyMail();
			
			System.out.println("营销邮统计分析定时器结束，结束时间："+DateFormat.getNowDate());
		}
		catch (Exception e) 
		{
			System.out.println("营销邮统计分析定时器异常......");
			
			e.printStackTrace();
		}
	}
	
	//营销邮统计发送总次数与发送总数
	public void yxyCountSMailInfo()
	{
		try
		{
			System.out.println("营销邮-按时间统计发送总次数与发送总数开始，开始时间："+DateFormat.getNowDate());
			
			String sql = "select yxy_day_count_id,yxy_day_count_loiginid,yxy_day_count_domain,yxy_day_count_date,yxy_day_count_ci_num,yxy_day_count_tol_num " +
					"from yxy_mail_day_count where is_timer_count = 0 limit 0,100";
			
			List<Object> objects = yxymaildaycountdao.findBySqlQuery(sql);
			
			if(objects.size() > 0)
			{
				String ids = "";
				
				for(int i = 0; i < objects.size(); i++)
				{
					Object []obj=(Object[])objects.get(i);
					//日统计表的id
					int yxy_day_count_id = Integer.parseInt(obj[0].toString());
					ids += yxy_day_count_id + ",";
					//登录帐号
					String loginid = obj[1].toString();
					//登录域名
					String domain = obj[2].toString();
					//日期
					String date = obj[3].toString();
					//当天发送次数
					int ci_num = Integer.parseInt(obj[4].toString());
					//当天发送总数
					int tol_num = Integer.parseInt(obj[5].toString());
					//先判断记录是否存在，不存在则添加，存在则更新
					sql = "select timer_id from timer_count_yxy where count_id = 8 and count_date = '"+date+"' and login_id = '"+StringEscapeUtils.escapeSql(loginid)+"' and domain = '"+StringEscapeUtils.escapeSql(domain)+"'";
					
					List<Object> select_objects = timercountyxydao.findBySqlQuery(sql); 
					
					if(select_objects.size() > 0)//已存在，则更新记录
					{
						int timer_id = Integer.parseInt(select_objects.get(0).toString());
						
						String hql = "update TimerCountYxy set day_count = "+ci_num+" where timer_id = "+timer_id;
						
						timercountyxydao.updateByHql(hql);
					}
					else//不存在，则添加记录
					{
						TimerCountYxy yxy1 = new TimerCountYxy();
						yxy1.setCount_date(date);
						yxy1.setDay(date.split("-")[2]);
						yxy1.setMonth(date.split("-")[1]);
						yxy1.setYear(Integer.parseInt(date.split("-")[0]));
						yxy1.setLogin_id(loginid);
						yxy1.setDomain(domain);
						yxy1.setCount_id(8);
						yxy1.setDay_count(ci_num);
						yxy1.setRecord_id("");
						timercountyxydao.addPojo(yxy1);
					}
					
					sql = "select timer_id from timer_count_yxy where count_id = 9 and count_date = '"+date+"' and login_id = '"+StringEscapeUtils.escapeSql(loginid)+"' and domain = '"+StringEscapeUtils.escapeSql(domain)+"'";
					
					select_objects = timercountyxydao.findBySqlQuery(sql); 
					
					if(select_objects.size() > 0)//已存在，则更新记录
					{
						int timer_id = Integer.parseInt(select_objects.get(0).toString());
						
						String hql = "update TimerCountYxy set day_count = "+ci_num+" where timer_id = "+timer_id;
						
						timercountyxydao.updateByHql(hql);
					}
					else//不存在，则添加记录
					{
						TimerCountYxy yxy2 = new TimerCountYxy();
						yxy2.setCount_date(date);
						yxy2.setDay(date.split("-")[2]);
						yxy2.setMonth(date.split("-")[1]);
						yxy2.setYear(Integer.parseInt(date.split("-")[0]));
						yxy2.setLogin_id(loginid);
						yxy2.setDomain(domain);
						yxy2.setCount_id(9);
						yxy2.setDay_count(tol_num);
						yxy2.setRecord_id("");
						timercountyxydao.addPojo(yxy2);
					}
				}
				
				ids = ids.substring(0, ids.length()-1);//去掉最后一个分号
				
				//操作完毕后改变该日统计表的状态为已统计
				String hql = "update YxyMailDayCount set is_timer_count = 1 where yxy_day_count_id in("+ids+")";
				
				yxymaildaycountdao.updateByHql(hql);
			}
			
			System.out.println("营销邮-按时间统计发送总次数与发送总数结束，结束时间："+DateFormat.getNowDate());
		}
		catch (Exception e) 
		{
			e.printStackTrace();
			
			System.out.println("营销邮--统计发送总次数与发送总数异常......");
		}
	}
	
	//营销邮统计点读总数
	public void yxyCountReadMail()
	{
		try
		{
			System.out.println("营销邮-按时间统计点读总数开始，开始时间："+DateFormat.getNowDate());
			
			String sql = "select * from yxy_reading_info where is_timer_count = 0 LIMIT 0,100";
			
			List<Object> objects = yxyreaddao.findBySql(sql);
			
			if(objects.size() > 0)
			{
				String ids = "";
				
				for(int i = 0; i < objects.size(); i++)
				{
					Object []obj = (Object[])objects.get(i);
					
					//点读id
					int yxy_reading_id = Integer.parseInt(obj[0].toString());
					ids += yxy_reading_id + ",";
					
					//登录帐号
					String loginid = obj[1].toString();
					
					//登录域名
					String domain = obj[2].toString();
					
					//当天点读数量
					int count = Integer.parseInt(obj[7].toString());
					
					//日期
					String date = obj[5].toString().split(" ")[0];
					
					//先判断记录是否存在，不存在则添加，存在则更新
					sql = "select timer_id,day_count from timer_count_yxy where count_id = 10 and count_date = '"+date+"' and login_id = '"+StringEscapeUtils.escapeSql(loginid)+"' and domain = '"+StringEscapeUtils.escapeSql(domain)+"'";
					
					List<Object> select_objects = timercountyxydao.findBySqlQuery(sql); 
					
					if(select_objects.size() > 0)//已存在，则更新记录
					{
						Object []arr_select_objects = (Object[])select_objects.get(0);
						
						int timer_id = Integer.parseInt(arr_select_objects[0].toString());
						
						int day_count = Integer.parseInt(arr_select_objects[1].toString());
						
						day_count = day_count + count;
						
						String hql = "update TimerCountYxy set day_count = "+day_count+" where timer_id = "+timer_id;
						
						timercountyxydao.updateByHql(hql);
					}
					else//不存在，则添加记录
					{
						TimerCountYxy yxy1 = new TimerCountYxy();
						yxy1.setCount_date(date);
						yxy1.setDay(date.split("-")[2]);
						yxy1.setMonth(date.split("-")[1]);
						yxy1.setYear(Integer.parseInt(date.split("-")[0]));
						yxy1.setLogin_id(loginid);
						yxy1.setDomain(domain);
						yxy1.setCount_id(10);
						yxy1.setDay_count(count);
						yxy1.setRecord_id("");
						timercountyxydao.addPojo(yxy1);
					}
				}
				
				ids = ids.substring(0, ids.length()-1);//去掉最后一个分号
				
				//操作完毕后改变该点读表的状态为已统计
				String hql = "update YxyReadingInfo set is_timer_count = 1 where yxy_reading_id in("+ids+")";
				
				yxyreaddao.updateByHql(hql);
			}
			
			System.out.println("营销邮-按时间统计点读总数结束，结束时间："+DateFormat.getNowDate());
		}
		catch (Exception e) 
		{
			e.printStackTrace();
			
			System.out.println("营销邮--统计点读总数异常......");
		}
	}
	
	//营销邮统计退订总数
	public void yxyCountUnsubscribeMail()
	{
		try
		{
			System.out.println("营销邮-按时间统计退订总数开始，开始时间："+DateFormat.getNowDate());
			
			String sql = "select * from yxy_unsubscribe_info where is_timer_count = 0 LIMIT 0,100";
			
			List<Object> objects = yxyunsubscribedao.findBySql(sql);
			
			if(objects.size() > 0)
			{
				String ids = "";
				
				for(int i = 0; i < objects.size(); i++)
				{
					Object []obj=(Object[])objects.get(i);
					
					//退订id
					int unsubscribe_id = Integer.parseInt(obj[0].toString());
					ids += unsubscribe_id + ",";
					
					//登录帐号
					String loginid = obj[1].toString();
					
					//登录域名
					String domain = obj[2].toString();
					
					//日期
					String date = obj[4].toString().split(" ")[0];
					
					//先判断记录是否存在，不存在则添加，存在则更新
					sql = "select timer_id,day_count from timer_count_yxy where count_id = 11 and count_date = '"+date+"' and login_id = '"+StringEscapeUtils.escapeSql(loginid)+"' and domain = '"+StringEscapeUtils.escapeSql(domain)+"'";
					
					List<Object> select_objects = timercountyxydao.findBySqlQuery(sql); 
					
					if(select_objects.size() > 0)//已存在，则更新记录
					{
						Object []arr_select_objects = (Object[])select_objects.get(0);
						
						int timer_id = Integer.parseInt(arr_select_objects[0].toString());
						
						int day_count = Integer.parseInt(arr_select_objects[1].toString());
						
						day_count = day_count + 1;
						
						String hql = "update TimerCountYxy set day_count = "+day_count+" where timer_id = "+timer_id;
						
						timercountyxydao.updateByHql(hql);
					}
					else//不存在，则添加记录
					{
						TimerCountYxy yxy = new TimerCountYxy();
						yxy.setCount_date(date);
						yxy.setDay(date.split("-")[2]);
						yxy.setMonth(date.split("-")[1]);
						yxy.setYear(Integer.parseInt(date.split("-")[0]));
						yxy.setLogin_id(loginid);
						yxy.setDomain(domain);
						yxy.setCount_id(11);
						yxy.setDay_count(1);
						yxy.setRecord_id("");
						timercountyxydao.addPojo(yxy);
					}
				}
				
				ids = ids.substring(0, ids.length()-1);//去掉最后一个分号
				
				//操作完毕后改变该退订表的状态为已统计
				String hql = "update YxyUnsubscribeInfo set is_timer_count = 1 where unsubscribe_id in("+ids+")";
				
				yxyunsubscribedao.updateByHql(hql);
			}
			
			System.out.println("营销邮-按时间统计退订总数结束，结束时间："+DateFormat.getNowDate());
		}
		catch (Exception e) 
		{
			e.printStackTrace();
			
			System.out.println("营销邮--统计退订总数异常......");
		}
	}
	
	//营销邮统计回复总数
	public void yxyCountReplyMail()
	{
		try
		{
			System.out.println("营销邮-按时间统计回复总数开始，开始时间："+DateFormat.getNowDate());
			
			String sql = "select * from yxy_reply_info where is_timer_count = 0 LIMIT 0,100";
			
			List<Object> objects = yxyreplyinfodao.findBySql(sql);
			
			if(objects.size() > 0)
			{
				String ids = "";
				
				for(int i = 0; i < objects.size(); i++)
				{
					Object []obj=(Object[])objects.get(i);
					
					//回复id
					int replyid = Integer.parseInt(obj[0].toString());
					ids += replyid + ",";
					
					//登录帐号
					String loginid = obj[1].toString();
					
					//登录域名
					String domain = obj[2].toString();
					
					//日期
					String date = obj[3].toString().split(" ")[0];
					
					//先判断记录是否存在，不存在则添加，存在则更新
					sql = "select timer_id,day_count from timer_count_yxy where count_id = 12 and count_date = '"+date+"' and login_id = '"+StringEscapeUtils.escapeSql(loginid)+"' and domain = '"+StringEscapeUtils.escapeSql(domain)+"'";
					
					List<Object> select_objects = timercountyxydao.findBySqlQuery(sql); 
					
					if(select_objects.size() > 0)//已存在，则更新记录
					{
						Object []arr_select_objects = (Object[])select_objects.get(0);
						
						int timer_id = Integer.parseInt(arr_select_objects[0].toString());
						
						int day_count = Integer.parseInt(arr_select_objects[1].toString());
						
						day_count = day_count + 1;
						
						String hql = "update TimerCountYxy set day_count = "+day_count+" where timer_id = "+timer_id;
						
						timercountyxydao.updateByHql(hql);
					}
					else//不存在，则添加记录
					{
						TimerCountYxy yxy = new TimerCountYxy();
						yxy.setCount_date(date);
						yxy.setDay(date.split("-")[2]);
						yxy.setMonth(date.split("-")[1]);
						yxy.setYear(Integer.parseInt(date.split("-")[0]));
						yxy.setLogin_id(loginid);
						yxy.setDomain(domain);
						yxy.setCount_id(12);
						yxy.setDay_count(1);
						yxy.setRecord_id("");
						timercountyxydao.addPojo(yxy);
					}
				}
				
				ids = ids.substring(0, ids.length()-1);//去掉最后一个分号
				
				//操作完毕后改变该回复表的状态为已统计
				String hql = "update YxyReplyInfo set is_timer_count = 1 where replyid in("+ids+")";
				
				yxyreplyinfodao.updateByHql(hql);
			}
			
			System.out.println("营销邮-按时间统计回复总数结束，结束时间："+DateFormat.getNowDate());
		}
		catch (Exception e) 
		{
			e.printStackTrace();
			
			System.out.println("营销邮--统计回复总数异常......");
		}
	}
	
	private TimerCountYxyDao timercountyxydao;//营销邮时间统计dao
	private YxyMailDayCountDao yxymaildaycountdao;//营销邮日总统计dao
	private YxyReadDao yxyreaddao;//点读dao
	private YxyUnsubscribeDao yxyunsubscribedao;//退订dao
	private YxyReplyInfoDao yxyreplyinfodao;//回复dao

	public TimerCountYxyDao getTimercountyxydao() {
		return timercountyxydao;
	}

	public void setTimercountyxydao(TimerCountYxyDao timercountyxydao) {
		this.timercountyxydao = timercountyxydao;
	}

	public YxyMailDayCountDao getYxymaildaycountdao() {
		return yxymaildaycountdao;
	}

	public void setYxymaildaycountdao(YxyMailDayCountDao yxymaildaycountdao) {
		this.yxymaildaycountdao = yxymaildaycountdao;
	}

	public YxyReadDao getYxyreaddao() {
		return yxyreaddao;
	}

	public void setYxyreaddao(YxyReadDao yxyreaddao) {
		this.yxyreaddao = yxyreaddao;
	}

	public YxyUnsubscribeDao getYxyunsubscribedao() {
		return yxyunsubscribedao;
	}

	public void setYxyunsubscribedao(YxyUnsubscribeDao yxyunsubscribedao) {
		this.yxyunsubscribedao = yxyunsubscribedao;
	}

	public YxyReplyInfoDao getYxyreplyinfodao() {
		return yxyreplyinfodao;
	}

	public void setYxyreplyinfodao(YxyReplyInfoDao yxyreplyinfodao) {
		this.yxyreplyinfodao = yxyreplyinfodao;
	}
}