package com.espeed.reading.util;

import io.jsonwebtoken.Claims;
import io.jsonwebtoken.Jwts;

import java.io.UnsupportedEncodingException;
import java.net.URL;
import java.net.URLDecoder;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.regex.Pattern;

import javax.servlet.http.Cookie;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import net.sf.json.JSONObject;

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

import com.espeed.ua.UserAgent;

/**
 * 
 * 
 * @项目名称: 邮件跟踪系统
 * @版权所有: 深圳市科飞时速网络技术有限公司(0755-88843776)
 * @技术支持: info@21gmail.com
 * @单元名称: 邮件跟踪工具类
 * @开始时间: 2018-5-23
 * @开发人员: 杨志钊
 */
public class TrackUtil {
	
	public static void test(HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		Thread.sleep(2000);
		String ip = NetworkUtil.getIpAddress(request);
		String toMail = request.getParameter("ywxMail");
		
		System.out.println(ip);
		System.out.println(toMail);
		if (StringUtils.isNotEmpty(toMail)) {
			String uid = null;
			List<String> emails = new ArrayList<String>();

			Cookie[] cookies = request.getCookies();

			if (cookies != null && cookies.length > 0) {

				for (Cookie cookie : cookies) {
					if (ConstantUtil.USER_IDENTITY_COOKIE.equals(cookie
							.getName())) {
						uid = cookie.getValue();
					}
					if (cookie.getName().startsWith(
							ConstantUtil.EMAIL_IDENTITY_COOKIE)) {
						if (cookie.getValue() != null
								&& emailPattern.matcher(cookie.getValue())
										.matches()) {

							emails.add(cookie.getValue());
						}
					}
				}
			}
			
			// 设置cookie
			String serverDomain = request.getServerName();
			int len = 0;
			if (!ipPattern.matcher(serverDomain).matches()) {

				String[] domains = serverDomain.split("\\.");
				len = domains.length;
				if (len > 2) {
					serverDomain = domains[len - 2] + "."
							+ domains[len - 1];
				}
			}
			
			Cookie cookie = new Cookie(ConstantUtil.USER_IDENTITY_COOKIE,
					uid);
			cookie.setMaxAge(ConstantUtil.HALF_CENTURY);
			cookie.setDomain(serverDomain);

			response.addCookie(cookie);

			len = emails.size();

			for (int i = 0; i < len; i++) {
				cookie = new Cookie(ConstantUtil.EMAIL_IDENTITY_COOKIE
						+ "_" + i, emails.get(i));
				cookie.setMaxAge(ConstantUtil.HALF_CENTURY);
				cookie.setDomain(serverDomain);

				response.addCookie(cookie);
			}

		}		
	}

	// 创建一个定长线程池，可控制线程最大并发数，超出的线程会在队列中等待
	private static ExecutorService fixedThreadPool = Executors
			.newFixedThreadPool(3);

	private static final String controlCentreUrl = ConfigPath
			.getControlCentreUrl();

	// private static ExecutorService singleThreadExecutor = Executors
	// .newSingleThreadExecutor();

	private static final Pattern ipPattern = Pattern.compile("[\\d\\.]*");
	private static final Pattern emailPattern = Pattern.compile(ConfigPath
			.getEmailPattern());

	private static final String secretkey = ConfigPath.getSecretKey();

	/** 记录用户打开原邮件的行为 */
	public static void recordOriginalMailInfo(HttpServletRequest request,
			HttpServletResponse response) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int userId = 0;

		try {
			String ip = NetworkUtil.getIpAddress(request);

			if (ip != null && !ip.equalsIgnoreCase("unknown")
					&& !ip.equalsIgnoreCase("null")) {
				String toMail = request.getParameter("to");// 点读地址
				String mailUid = request.getParameter("mailuid");// 邮件唯一码
				String loginId = request.getParameter("loginid");// 用户账号
				String domain = request.getParameter("domain");// 用户域名
				String mailId = request.getParameter("mailId");

				String jsessionId = request.getSession().getId();
				
				String poporimap = request.getParameter("poporimap");//null/0是pop，1是imap
	  			poporimap = poporimap == null ? "0" : poporimap.replace("3D", "").trim();

				if (toMail == null || mailUid == null || domain == null) {

					System.err.println("参数缺失：toMail=" + toMail + ",mailUid="
							+ mailUid + ",loginId=" + loginId + ",domain="
							+ domain);

					return;
				}
				
				if (mailUid != null) {
					mailUid = URLDecoder.decode(mailUid, "UTF-8");
				}
				
				if (toMail != null) {
					toMail = URLDecoder.decode(toMail, "UTF-8");
				}
				
				if (loginId != null) {
					loginId = URLDecoder.decode(loginId, "UTF-8");
				}
				
				if (domain != null) {
					domain = URLDecoder.decode(domain, "UTF-8");
				}

				String uid = null;
				List<String> emails = new ArrayList<String>();
				boolean cookieEnabled = true;
				String emailStr = "";

				Cookie[] cookies = request.getCookies();
				if (cookies != null && cookies.length > 0) {

					for (Cookie cookie : cookies) {
						if (ConstantUtil.USER_IDENTITY_COOKIE.equals(cookie
								.getName())) {
							uid = cookie.getValue();
						}
						if (cookie.getName().startsWith(
								ConstantUtil.EMAIL_IDENTITY_COOKIE)) {
							if (cookie.getValue() != null
									&& emailPattern.matcher(cookie.getValue())
											.matches()) {

								emails.add(cookie.getValue());

								emailStr = "," + cookie.getValue();
							}
						}
					}
				}

				if (!emailStr.isEmpty()) {
					emailStr = emailStr.substring(1);
				}

				int ywxType = Integer.valueOf(request.getAttribute("ywxType")
						.toString());

				Map<String, Object> userInfo = null;

				if (ywxType == 0) {
					userInfo = CentreDBUtil.getUserInfo(loginId, domain);
					if (!userInfo.get("code").equals(200)) {

						System.err.println("获取用户信息失败：" + userInfo.get("msg"));
						return;
					}
				} else {

					if (mailUid == null || mailUid.trim().isEmpty()) {
						System.err.println(toMail + "不存在mailUid");
						return;
					}

					userInfo = WebmailDBUtil.getUserInfo(mailUid);
					if (!userInfo.get("code").equals(200)) {

						System.err.println("通过邮件id获取用户信息失败："
								+ userInfo.get("msg"));
						return;
					}
				}

				userId = Integer.valueOf(userInfo.get("userId").toString());
				int companyId = Integer.valueOf(userInfo.get("companyId")
						.toString());

				String title = null;

				Map<String, Object> mailInfo = null;

				if (ywxType == 0) {
					mailInfo = YxyDBUtil.getMailInfo(mailUid);

					if (!mailInfo.get("code").equals(200)) {
						System.err.println("获取邮件信息失败：" + mailInfo.get("msg"));
						// return;
					}
				} else {
					mailInfo = WebmailDBUtil.getMailInfo(mailUid);

					if (!mailInfo.get("code").equals(200)) {
						System.err.println("获取邮件信息失败：" + mailInfo.get("msg"));
						// return;
					}
				}

				if (mailInfo.containsKey("subject")) {
					
					title = String.valueOf(mailInfo.get("subject"));
				}
				if (title != null && title.contains("###")) {
					title = title.split("###")[0];
				}

				conn = DBUtil.getConnection();
				conn.setAutoCommit(false);// 开启事务
				String sql = null;
				long count = 0L;

				String dbuid = null;// 邮箱在数据库中的第一条记录下的cookie标识

				if (toMail != null && !toMail.trim().isEmpty()) {

					sql = "select identity from ts_customer_access where access_email = ? order by create_time ASC limit 1";
					ps = conn.prepareStatement(sql);
					ps.setString(1, toMail);

					rs = ps.executeQuery();

					if (rs.first()) {
						dbuid = rs.getString("identity");
					}

					if (dbuid != null && !dbuid.equals(uid)) {
						uid = dbuid;
					}
				}

				// cookie里面没有UID则查该ip是否有浏览过，若有的话，则该用户的浏览器不支持cookie
				if (uid == null) {
					sql = "select identity,jsession_id from ts_customer_access where ip = ? order by create_time asc";
					ps = conn.prepareStatement(sql);
					ps.setString(1, ip);

					rs = ps.executeQuery();
					if (rs.first()) {// 有浏览过
						cookieEnabled = false;
						uid = rs.getString("identity");
						jsessionId = rs.getString("jsession_id");

						sql = "select identity from ts_customer_email where email = ?";
						ps = conn.prepareStatement(sql);
						ps.setString(1, toMail);

						rs = ps.executeQuery();
						if (rs.first()) {
							// 若是数据库已经存在cookie唯一标示，而又不对应，则替换
							// 在搜索引擎的爬虫下会造成数据混乱
							// if (!uid.equals(rs.getString("identity"))) {
							// sql =
							// "update ts_customer_access set identity = ? , access_email = ? where identity = ?";
							// ps = conn.prepareStatement(sql);
							// ps.setString(1, rs.getString("identity"));
							// ps.setString(2, toMail);
							// ps.setString(3, uid);
							//
							// ps.executeUpdate();
							//
							// uid = rs.getString("identity");
							// }
						} else {
							sql = "select count(*) from ts_customer_email where identity = ? and email = ?";
							ps = conn.prepareStatement(sql);
							ps.setString(1, uid);
							ps.setString(2, toMail);

							rs = ps.executeQuery();
							if (rs.first()) {
								count = rs.getLong(1);
							}

							if (count == 0L) {
								sql = "insert into ts_customer_email(identity,email,create_time) values(?,?,?)";
								ps = conn.prepareStatement(sql);
								ps.setString(1, uid);
								ps.setString(2, toMail);
								ps.setString(3, DateFormat.getNowDate());
								// 添加email
								ps.executeUpdate();
							}
						}

					}

					// 没有浏览过
					if (uid == null) {
						sql = "select identity from ts_customer_email where email = ?";
						ps = conn.prepareStatement(sql);
						ps.setString(1, toMail);
						rs = ps.executeQuery();
						if (rs.first()) {
							uid = rs.getString("identity");
						} else {
							uid = UUID.randomUUID().toString();

							sql = "insert into ts_customer(identity,type,create_time) values(?,?,?)";
							ps = conn.prepareStatement(sql);
							ps.setString(1, uid);
							ps.setInt(2, 1);// 客户类型，0：路人、1：已存在客户
							ps.setString(3, DateFormat.getNowDate());
							// 添加客户
							ps.executeUpdate();

							sql = "select count(*) from ts_customer_email where identity = ? and email = ?";
							ps = conn.prepareStatement(sql);
							ps.setString(1, uid);
							ps.setString(2, toMail);

							rs = ps.executeQuery();
							if (rs.first()) {
								count = rs.getLong(1);
							}

							if (count == 0L) {
								sql = "insert into ts_customer_email(identity,email,create_time) values(?,?,?)";
								ps = conn.prepareStatement(sql);
								ps.setString(1, uid);
								ps.setString(2, toMail);
								ps.setString(3, DateFormat.getNowDate());
								// 添加email
								ps.executeUpdate();
							}
						}

					}

				} else {
					// sql =
					// "select identity from ts_customer_email where email = ?";
					// ps = conn.prepareStatement(sql);
					// ps.setString(1, toMail);
					// String identity = null;
					// rs = ps.executeQuery();
					// if (rs.first()) {
					// identity = rs.getString("identity");
					// }
					// 在搜索引擎的爬虫下会造成数据混乱
					// 若是已经存在cookie唯一标示，而数据库又有该邮箱的cookie，则替换
					// if (identity != null && !uid.equals(identity)) {
					// sql =
					// "update ts_customer_access set identity = ? , access_email = ? where identity = ?";
					// ps = conn.prepareStatement(sql);
					// ps.setString(1, identity);
					// ps.setString(2, toMail);
					// ps.setString(3, uid);
					//
					// ps.executeUpdate();
					//
					// uid = identity;
					// }

					// 浏览器没有该客户邮箱且数据没有保存，则保存
					if (!emails.contains(toMail)) {

						sql = "select count(*) from ts_customer_email where identity = ? and email = ?";
						ps = conn.prepareStatement(sql);
						ps.setString(1, uid);
						ps.setString(2, toMail);

						rs = ps.executeQuery();
						if (rs.first()) {
							count = rs.getLong(1);
						}

						if (count == 0L) {
							sql = "insert into ts_customer_email(identity,email,create_time) values(?,?,?)";
							ps = conn.prepareStatement(sql);
							ps.setString(1, uid);
							ps.setString(2, toMail);
							ps.setString(3, DateFormat.getNowDate());
							// 添加email
							ps.executeUpdate();
						}
					}

				}

				if (!emails.contains(toMail)) {
					emails.add(toMail);
				}

				// 数据组装
				String agentString = request.getHeader("User-Agent");

				String accessUrl = request.getHeader("Referer");
				String accessDomain = null;
				if (accessUrl != null) {

					try {
						accessDomain = new URL(accessUrl).getHost();
					} catch (Exception e) {
						System.err.println("accessUrl域名解析失败："
								+ e.getClass().getName()
								+ (e.getMessage() == null ? "" : ("->" + e
										.getMessage())));
					}
					if (request.getQueryString() != null) {

						accessUrl += "?" + request.getQueryString();
					}
				}

				String language = request.getLocale().getLanguage();
				if (request.getLocale().getCountry() != null
						&& !request.getLocale().getCountry().trim().isEmpty()) {
					language += "-" + request.getLocale().getCountry();
				}

				// 解析ip
				String url = controlCentreUrl + "/webservice/ip";
				Map<String, Object> params = new HashMap<String, Object>();
				params.put("ip", ip);

				String continent = null;
				String country = null;
				String province = null;
				String city = null;
				String address = null;
				String netProvider = null;

				Map<String, Object> result = HttpUtil.doGet(url, params);
				if (result.get("code").equals(200)) {
					Object data = result.get("data");

					JSONObject json = JSONObject.fromObject(data);

					if (json.getInt("code") == 200) {
						json = JSONObject.fromObject(json.get("result"));

						netProvider = json.getString("isp");
						continent = json.getString("land");
						country = json.getString("country");
						province = json.getString("prov");
						city = json.getString("city");
						address = json.getString("dist");
					} else {
						System.err.println("ip解析失败：" + json.get("result"));
					}
				} else {
					System.err.println("ip解析失败：" + result.get("msg"));
				}

				UserAgent userAgent = UserAgent
						.parseUserAgentString(agentString);

				if ((accessUrl == null || accessUrl.trim().isEmpty())
						|| (mailUid == null || mailUid.trim().isEmpty())
						|| (toMail == null || toMail.trim().isEmpty())) {
					// 只要有一个为空就放弃添加

					System.err.println("值缺失：accessUrl=" + accessUrl
							+ ",mailUid=" + mailUid + ",toMail=" + toMail);

					// return;
				}

				if (toMail != null && !toMail.trim().isEmpty()) {
					if (!emailPattern.matcher(toMail.trim().toLowerCase())
							.matches()) {
						// 邮箱格式错误就放弃添加

						System.err.println("邮箱格式错误：" + toMail);

						return;
					}
				}

				// 添加浏览记录
				sql = "insert into ts_customer_access(identity,user_id,company_id,mail_uid,title,from_url,from_domain,access_url,access_domain,ip,jsession_id,continent,country,province,city,address,device_type,browser_name,language,screen_x,screen_y,net_provider,os,search_word,user_agent,cookie_enable,access_time,access_email,res_type,create_time,template_id,customer_id,from_type,count_id,status,is_imap) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
				ps = conn.prepareStatement(sql);
				ps.setString(1, uid);// cookie唯一标示
				ps.setInt(2, userId);// 用户id
				ps.setInt(3, companyId);// 企业id
				ps.setString(4, mailUid);// 邮件唯一标示
				ps.setString(5, title);// 来源标题
				ps.setString(6, null);// 来源url
				ps.setString(7, null);// 来源域名
				ps.setString(8, accessUrl);// 受访问url
				ps.setString(9, accessDomain);// 受访问域名
				ps.setString(10, ip);// 客户端ip
				ps.setString(11, jsessionId);// 回话id
				ps.setString(12, continent);// 访问者所属洲
				ps.setString(13, country);// 访问者所属国际
				ps.setString(14, province);// //访问者所属省份
				ps.setString(15, city);// //访问者所属城市
				ps.setString(16, address);// //访问者所属详细地址
				ps.setInt(17, TrackUtil.getDeviceType(userAgent));// 设备类型
				ps.setString(18, userAgent.getBrowser().getName());// 浏览器名称
				ps.setString(19, language);// 浏览器设置的语言
				ps.setInt(20, 0);// 设备宽度
				ps.setInt(21, 0);// 设备高度
				ps.setString(22, netProvider);// 网络接入商
				ps.setString(23, userAgent.getOperatingSystem().getName());// 操作系统名称
				ps.setString(24, null);// 搜索词
				ps.setString(25, agentString);// 浏览器ua
				ps.setInt(26, cookieEnabled ? 1 : 0);// 是否支持cookie,1支持、0不支持
				ps.setInt(27, 0);// 访问时长，单位毫秒
				ps.setString(28, toMail);// 客户邮箱
				ps.setInt(29, ywxType);// 受访问资源类型，0营销邮邮件、1营销邮邮件HTML页面、2易外销用户站点、3营销邮邮件里需要跟踪的超链、4营销邮邮件HTML页面里需要跟踪的超链、5易外销邮件、6易外销邮件里需要跟踪的超链、7询盘
				ps.setString(30, DateFormat.getNowDate());// 创建时间
				ps.setInt(31, 0);// 模板id
				ps.setInt(32, WebmailDBUtil.getCustomerId(userId, toMail));// 客户id
				ps.setInt(33, 3);// 来源类别，-1无来源url、0：外链、1社交、2搜索、3邮件
				ps.setLong(34, 0L);// 统计id
				ps.setInt(35, 1);// 状态，0删除、1正常
				ps.setInt(36, Integer.valueOf(poporimap));// 是否是imap邮件，0否、1是
				// 提交添加
				ps.executeUpdate();
				// 获取自动生成的id
				rs = ps.getGeneratedKeys();
				long id = 0L;
				if (rs.first()) {
					id = rs.getLong(1);
				}

				sql = "select id,create_time from ts_customer_access where id != ? and identity = ? and user_id = ? and company_id = ? and to_days(create_time) = to_days(now()) order by create_time desc";
				ps = conn.prepareStatement(sql);
				ps.setLong(1, id);
				ps.setString(2, uid);
				ps.setInt(3, userId);
				ps.setInt(4, companyId);
				// 有上一条记录则修改上一条记录的访问时长
				rs = ps.executeQuery();
				if (rs.first()) {
					long milliSeconds = DateFormat.milliSeconds(
							rs.getString("create_time"),
							DateFormat.getNowDate());

					if (milliSeconds < (1000 * 60 * 60 * 2)) {// 当天前后两个请求大于2个小时的丢掉
						sql = "update ts_customer_access set access_time = ? where id = ?";
						ps = conn.prepareStatement(sql);
						ps.setLong(1, milliSeconds);
						ps.setLong(2, rs.getLong("id"));
						// 修改上一个请求的访问时长
						ps.executeUpdate();
					}

				}

				// 修改为已存在客户
				// sql = "update ts_customer set type = ? where identity = ?";
				// ps = conn.prepareStatement(sql);
				// ps.setInt(1, 1);// 客户类型，0：路人、1：已存在客户
				// ps.setString(2, uid);
				// // 修改客户
				// ps.executeUpdate();

				// 设置cookie
				String serverDomain = request.getServerName();
				int len = 0;
				if (!ipPattern.matcher(serverDomain).matches()) {

					String[] domains = serverDomain.split("\\.");
					len = domains.length;
					if (len > 2) {
						serverDomain = domains[len - 2] + "."
								+ domains[len - 1];
					}
				}

				Cookie cookie = new Cookie(ConstantUtil.USER_IDENTITY_COOKIE,
						uid);
				cookie.setMaxAge(ConstantUtil.HALF_CENTURY);
				cookie.setDomain(serverDomain);

				response.addCookie(cookie);

				len = emails.size();

				for (int i = 0; i < len; i++) {
					cookie = new Cookie(ConstantUtil.EMAIL_IDENTITY_COOKIE
							+ "_" + i, emails.get(i));
					cookie.setMaxAge(ConstantUtil.HALF_CENTURY);
					cookie.setDomain(serverDomain);

					response.addCookie(cookie);
				}

				// 若不是在shenyingshejiao.net域名下，则要把cookie设置到该域名下
				if (!request.getServerName().contains(
						ConfigPath.getClickDomain())) {
					response.sendRedirect(ConfigPath.getClickUrl()
							+ "/cookieSetter.jsp?jsessionId=" + jsessionId
							+ "&identity=" + uid + "&emails=" + emailStr);
				}

				// try的最后提交事务
				conn.commit();
			} else {
				System.err.println("无法获取ip");
			}

		} catch (Exception e) {
			if (conn != null) {
				try {
					// 回滚事务
					conn.rollback();
				} catch (SQLException ex) {
					ex.printStackTrace();
				}
			}
			e.printStackTrace();

			LogUtil.saveInfoLog(request.getAttribute("ywxType") + "/" + userId,
					e);
		} finally {
			DBUtil.close(rs, ps, conn);
		}
	}
	
	/** 记录用户打开原邮件里的超链接的行为 */
	public static void recordFromOriginalMailInfo(JSONObject param, List<String> emails) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int ywxType = Integer.valueOf(param.getString("ywxType")
				.toString().replace("3D", ""));
		
		Integer userId2 = 0;
		Integer companyId2 = 0;
		
		if (ywxType == 9) {//9(sale100.com独立)营销邮邮件里需要跟踪的超链
			ywxType = 3;
			
			Map<String, Object>  userInfo = CentreDBUtil.getUserInfo(param.getString("ywxUid"), param.getString("ywxCid"));
			if (!userInfo.get("code").equals(200)) {

				System.err.println("获取用户信息失败：" + userInfo.get("msg"));
				return;
			}
			
			userId2 = Integer.valueOf(userInfo.get("userId").toString());
			companyId2 = Integer.valueOf(userInfo.get("companyId").toString());
		} else {
			userId2 = Integer
					.valueOf(param.getString("ywxUid") == null ? "0" : param.getString("ywxUid").replace("3D", ""));
			companyId2 = Integer.valueOf(param.getString("ywxCid") == null ? "0" : param.getString("ywxCid").replace("3D", ""));
		}

		final Integer userId = userId2;
		final Integer companyId = companyId2;

		try {
			String ip = param.getString("ip");

			if (ip != null && !ip.equalsIgnoreCase("unknown")
					&& !ip.equalsIgnoreCase("null")) {
				
				if (!param.containsKey("ywxMail")) {
					param.put("ywxMail", "");
				}
				if (!param.containsKey("ywxMid")) {
					param.put("ywxMid", "");
				}
				if (!param.containsKey("ywxX")) {
					param.put("ywxX", "");
				}
				if (!param.containsKey("ywxH")) {
					param.put("ywxH", "");
				}
				if (!param.containsKey("poporimap")) {
					param.put("poporimap", "0");
				}
				if (!param.containsKey("ywxTitle")) {
					param.put("ywxTitle", "");
				}
				if (!param.containsKey("ywxTo")) {
					param.put("ywxTo", "");
				}
				
				final String toMail = (param.getString("ywxMail") != null && param.getString("ywxMail").startsWith("3D")) ? 
						URLDecoder.decode(param.getString("ywxMail").replaceFirst("3D", ""), "UTF-8")
						: URLDecoder.decode(param.getString("ywxMail"), "UTF-8");// 点读地址

				final String mailUid = (param.getString("ywxMid") != null && param.getString("ywxMid").startsWith("3D")) ? 
						URLDecoder.decode(param.getString("ywxMid").replaceFirst("3D", ""), "UTF-8")
						: URLDecoder.decode(param.getString("ywxMid"), "UTF-8");// 邮件唯一码


				Boolean cookieEnabled = Boolean.valueOf(param.getString(
						"ywxCookie").replace("3D", ""));

				Integer ywxX = Integer
						.valueOf(param.getString("ywxX") == null ? "0"
								: param.getString("ywxX")
										.replace("3D", ""));
				Integer ywxH = Integer
						.valueOf(param.getString("ywxH") == null ? "0"
								: param.getString("ywxH")
										.replace("3D", ""));
				
				String poporimap = param.getString("poporimap");//null/0是pop，1是imap
	  			poporimap = poporimap == null ? "0" : poporimap.replace("3D", "").trim();

				String jsessionId = param.getString("jsessionId");

				if (StringUtils.isEmpty(toMail) || StringUtils.isEmpty(mailUid) || userId == null
						|| companyId == null || cookieEnabled == null
						|| ywxX == null || ywxH == null) {

					System.err.println("参数缺失");

					return;
				}

				Map<String, Object> mailInfo = null;

				String title = (param.getString("ywxTitle") != null && param.getString("ywxTitle").startsWith("3D")) ? 
						param.getString("ywxTitle").replaceFirst("3D", "")
						: param.getString("ywxTitle");

				if (title == null || title.trim().isEmpty()
						|| title.equals("null")) {

					if (ywxType == 3) {

						mailInfo = YxyDBUtil.getMailInfo(mailUid);

						if (!mailInfo.get("code").equals(200)) {
							System.err.println("获取邮件信息失败："
									+ mailInfo.get("msg"));
							// return;
						}

					} else {
						mailInfo = WebmailDBUtil.getMailInfo(mailUid);

						if (!mailInfo.get("code").equals(200)) {
							System.err.println("获取邮件信息失败："
									+ mailInfo.get("msg"));
							// return;
						}
					}

					if (mailInfo.containsKey("subject")) {
						
						title = String.valueOf(mailInfo.get("subject"));
					}

					if (title != null && title.contains("###")) {
						title = title.split("###")[0];
					}

				}
				String uid = null;
				if (param.containsKey("uid")) {
					uid = param.getString("uid");
				} 

				conn = DBUtil.getConnection();
				conn.setAutoCommit(false);// 开启事务
				String sql = null;
				long count = 0L;

				String dbuid = null;// 邮箱在数据库中的第一条记录下的cookie标识

				if (toMail != null && !toMail.trim().isEmpty()) {

					sql = "select identity from ts_customer_access where access_email = ? order by create_time ASC limit 1";
					ps = conn.prepareStatement(sql);
					ps.setString(1, toMail);

					rs = ps.executeQuery();

					if (rs.first()) {
						dbuid = rs.getString("identity");
					}

					if (dbuid != null && !dbuid.equals(uid)) {
						uid = dbuid;
					}
				}

				// cookie里面没有UID则查该ip是否有浏览过，若有的话，则该用户的浏览器不支持cookie
				if (uid == null) {
					if (!cookieEnabled) {// 浏览器不支持cookie
						sql = "select identity,jsession_id from ts_customer_access where ip = ? order by create_time asc";
						ps = conn.prepareStatement(sql);
						ps.setString(1, ip);

						rs = ps.executeQuery();
						if (rs.first()) {// 有浏览过
							cookieEnabled = false;
							uid = rs.getString("identity");
							jsessionId = rs.getString("jsession_id");

							sql = "select identity from ts_customer_email where email = ?";
							ps = conn.prepareStatement(sql);
							ps.setString(1, toMail);

							rs = ps.executeQuery();
							if (rs.first()) {
								// 若是数据库已经存在cookie唯一标示，而又不对应，则替换
								// 在搜索引擎的爬虫下会造成数据混乱
								// if (!uid.equals(rs.getString("identity"))) {
								// sql =
								// "update ts_customer_access set identity = ? , access_email = ? where identity = ?";
								// ps = conn.prepareStatement(sql);
								// ps.setString(1, rs.getString("identity"));
								// ps.setString(2, toMail);
								// ps.setString(3, uid);
								//
								// ps.executeUpdate();
								//
								// uid = rs.getString("identity");
								// }
							} else {
								sql = "select count(*) from ts_customer_email where identity = ? and email = ?";
								ps = conn.prepareStatement(sql);
								ps.setString(1, uid);
								ps.setString(2, toMail);

								rs = ps.executeQuery();
								if (rs.first()) {
									count = rs.getLong(1);
								}

								if (count == 0L) {
									sql = "insert into ts_customer_email(identity,email,create_time) values(?,?,?)";
									ps = conn.prepareStatement(sql);
									ps.setString(1, uid);
									ps.setString(2, toMail);
									ps.setString(3, DateFormat.getNowDate());
									// 添加email
									ps.executeUpdate();
								}
							}

						}
					}

					// 没有浏览过
					if (uid == null) {
						sql = "select identity from ts_customer_email where email = ?";
						ps = conn.prepareStatement(sql);
						ps.setString(1, toMail);
						rs = ps.executeQuery();
						if (rs.first()) {
							uid = rs.getString("identity");
						} else {
							uid = UUID.randomUUID().toString();
						}

						sql = "insert into ts_customer(identity,type,create_time) values(?,?,?)";
						ps = conn.prepareStatement(sql);
						ps.setString(1, uid);
						ps.setInt(2, 1);// 客户类型，0：路人、1：已存在客户
						ps.setString(3, DateFormat.getNowDate());
						// 添加客户
						ps.executeUpdate();

						sql = "select count(*) from ts_customer_email where identity = ? and email = ?";
						ps = conn.prepareStatement(sql);
						ps.setString(1, uid);
						ps.setString(2, toMail);

						rs = ps.executeQuery();
						if (rs.first()) {
							count = rs.getLong(1);
						}

						if (count == 0L) {
							sql = "insert into ts_customer_email(identity,email,create_time) values(?,?,?)";
							ps = conn.prepareStatement(sql);
							ps.setString(1, uid);
							ps.setString(2, toMail);
							ps.setString(3, DateFormat.getNowDate());
							// 添加email
							ps.executeUpdate();
						}
					}

				} else {
					// sql =
					// "select identity from ts_customer_email where email = ?";
					// ps = conn.prepareStatement(sql);
					// ps.setString(1, toMail);
					// String identity = null;
					// rs = ps.executeQuery();
					// if (rs.first()) {
					// identity = rs.getString("identity");
					// }
					//
					// 若是已经存在cookie唯一标示，而数据库又有该邮箱的cookie，则替换
					// 在搜索引擎的爬虫下会造成数据混乱
					// if (identity != null && !uid.equals(identity)) {
					// sql =
					// "update ts_customer_access set identity = ? , access_email = ? where identity = ?";
					// ps = conn.prepareStatement(sql);
					// ps.setString(1, identity);
					// ps.setString(2, toMail);
					// ps.setString(3, uid);
					//
					// ps.executeUpdate();
					//
					// uid = identity;
					// }

					// 浏览器没有该客户邮箱且数据没有保存，则保存
					if (!emails.contains(toMail)) {

						sql = "select count(*) from ts_customer_email where identity = ? and email = ?";
						ps = conn.prepareStatement(sql);
						ps.setString(1, uid);
						ps.setString(2, toMail);

						rs = ps.executeQuery();
						if (rs.first()) {
							count = rs.getLong(1);
						}

						if (count == 0L) {
							sql = "insert into ts_customer_email(identity,email,create_time) values(?,?,?)";
							ps = conn.prepareStatement(sql);
							ps.setString(1, uid);
							ps.setString(2, toMail);
							ps.setString(3, DateFormat.getNowDate());
							// 添加email
							ps.executeUpdate();
						}
					}

				}

				if (!emails.contains(toMail)) {
					emails.add(toMail);
				}

				// 数据组装
				String agentString = param.getString("agentString");
				String referer = param.getString("referer");
				String fromDomain = null;

				if (referer != null && !referer.trim().isEmpty()) {
					try {
						referer = URLDecoder.decode(referer, "UTF-8");
						fromDomain = new URL(referer).getHost();
					} catch (Exception e) {
						System.err.println("referer域名解析失败："
								+ e.getClass().getName()
								+ (e.getMessage() == null ? "" : ("->" + e
										.getMessage())));
					}
				}

				String language = param.getString("language");
				String country2 = param.getString("country");
				if (country2 != null
						&& !country2.trim().isEmpty()) {
					language += "-" + country2;
				}

				String accessUrl = (param.getString("ywxTo") != null && param.getString("ywxTo").startsWith("3D")) ? 
						param.getString("ywxTo").replaceFirst("3D", "") : param.getString("ywxTo");

				// final String accessUrl2 = accessUrl;

				String accessDomain = null;
				if (accessUrl != null) {
					try {
						accessUrl = URLDecoder.decode(accessUrl, "UTF-8");

						accessDomain = new URL(accessUrl).getHost();
					} catch (Exception e) {
						System.err.println("ywxTo域名解析失败："
								+ e.getClass().getName()
								+ (e.getMessage() == null ? "" : ("->" + e
										.getMessage())));
					}
				}

				// 解析ip
				String url = controlCentreUrl + "/webservice/ip";
				Map<String, Object> params = new HashMap<String, Object>();
				params.put("ip", ip);

				String continent = null;
				String country = null;
				String province = null;
				String city = null;
				String address = null;
				String netProvider = null;

				Map<String, Object> result = HttpUtil.doGet(url, params);
				if (result.get("code").equals(200)) {
					Object data = result.get("data");

					JSONObject json = JSONObject.fromObject(data);

					if (json.getInt("code") == 200) {
						json = JSONObject.fromObject(json.get("result"));

						netProvider = json.getString("isp");
						continent = json.getString("land");
						country = json.getString("country");
						province = json.getString("prov");
						city = json.getString("city");
						address = json.getString("dist");
					} else {
						System.err.println("ip解析失败：" + json.get("result"));
					}
				} else {
					System.err.println("ip解析失败：" + result.get("msg"));
				}

				UserAgent userAgent = UserAgent
						.parseUserAgentString(agentString);

				if ((accessUrl == null || accessUrl.trim().isEmpty())
						|| (mailUid == null || mailUid.trim().isEmpty())
						|| (toMail == null || toMail.trim().isEmpty())) {
					// 只要有一个为空就放弃添加
					return;
				}
				
				if (toMail != null && !toMail.trim().isEmpty()) {
					if (!emailPattern.matcher(toMail.trim().toLowerCase())
							.matches()) {
						
						// 邮箱格式错误就放弃添加
						return;
					}
				}
				
				// 添加浏览记录
				sql = "insert into ts_customer_access(identity,user_id,company_id,mail_uid,title,from_url,from_domain,access_url,access_domain,ip,jsession_id,continent,country,province,city,address,device_type,browser_name,language,screen_x,screen_y,net_provider,os,search_word,user_agent,cookie_enable,access_time,access_email,res_type,create_time,template_id,customer_id,from_type,count_id,status,is_imap) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
				ps = conn.prepareStatement(sql);
				ps.setString(1, uid);// cookie唯一标示
				ps.setInt(2, userId);// 用户id
				ps.setInt(3, companyId);// 企业id
				ps.setString(4, mailUid);// 邮件唯一标示
				ps.setString(5, title);// 来源标题
				ps.setString(6, referer);// 来源url
				ps.setString(7, fromDomain);// 来源域名
				ps.setString(8, accessUrl);// 受访问url
				ps.setString(9, accessDomain);// 受访问域名
				ps.setString(10, ip);// 客户端ip
				ps.setString(11, jsessionId);// 回话id
				ps.setString(12, continent);// 访问者所属洲
				ps.setString(13, country);// 访问者所属国际
				ps.setString(14, province);// //访问者所属省份
				ps.setString(15, city);// //访问者所属城市
				ps.setString(16, address);// //访问者所属详细地址
				ps.setInt(17, TrackUtil.getDeviceType(userAgent));// 设备类型
				ps.setString(18, userAgent.getBrowser().getName());// 浏览器名称
				ps.setString(19, language);// 浏览器设置的语言
				ps.setInt(20, ywxX);// 设备宽度
				ps.setInt(21, ywxH);// 设备高度
				ps.setString(22, netProvider);// 网络接入商
				ps.setString(23, userAgent.getOperatingSystem().getName());// 操作系统名称
				ps.setString(24, TrackUtil.getSearchWord(referer));// 搜索词
				ps.setString(25, agentString);// 浏览器ua
				ps.setInt(26, cookieEnabled ? 1 : 0);// 是否支持cookie,1支持、0不支持
				ps.setInt(27, 0);// 访问时长，单位毫秒
				ps.setString(28, toMail);// 客户邮箱
				ps.setInt(29, ywxType);// 受访问资源类型，0营销邮邮件、1营销邮邮件HTML页面、2易外销用户站点、3营销邮邮件里需要跟踪的超链、4营销邮邮件HTML页面里需要跟踪的超链、5易外销邮件、6易外销邮件里需要跟踪的超链、7询盘
				ps.setString(30, DateFormat.getNowDate());// 创建时间
				ps.setInt(31, 0);// 模板id
				ps.setInt(32, WebmailDBUtil.getCustomerId(userId, toMail));// 客户id
				ps.setInt(33, 3);// 来源类别，-1无来源url、0：外链、1社交、2搜索、3邮件
				ps.setLong(34, 0L);// 统计id
				ps.setInt(35, 1);// 状态，0删除、1正常
				ps.setInt(36, Integer.valueOf(poporimap));// 是否是imap邮件，0否、1是
				// 提交添加
				ps.executeUpdate();
				// 获取自动生成的id
				rs = ps.getGeneratedKeys();
				long id = 0L;
				if (rs.first()) {
					id = rs.getLong(1);
				}

				// final long aid = id;

				sql = "select id,create_time from ts_customer_access where id != ? and identity = ? and user_id = ? and company_id = ? and to_days(create_time) = to_days(now()) order by create_time desc";
				ps = conn.prepareStatement(sql);
				ps.setLong(1, id);
				ps.setString(2, uid);
				ps.setInt(3, userId);
				ps.setInt(4, companyId);
				// 有上一条记录则修改上一条记录的访问时长
				rs = ps.executeQuery();
				if (rs.first()) {
					long milliSeconds = DateFormat.milliSeconds(
							rs.getString("create_time"),
							DateFormat.getNowDate());

					if (milliSeconds < (1000 * 60 * 60 * 2)) {// 当天前后两个请求大于2个小时的丢掉
						sql = "update ts_customer_access set access_time = ? where id = ?";
						ps = conn.prepareStatement(sql);
						ps.setLong(1, milliSeconds);
						ps.setLong(2, rs.getLong("id"));
						// 修改上一个请求的访问时长
						ps.executeUpdate();
					}

				}

				final int recordType = ywxType;
				final long recordId = id;
				final String recordEmail = toMail;
				final String recordLink = (accessUrl == null ? null : URLDecoder.decode(accessUrl, "UTF-8"));
				final int recordUserId = userId;

				// 点击超链接时异步添加消息提醒：3营销邮邮件里需要跟踪的超链、6易外销邮件里需要跟踪的超链
				if (recordLink != null && !recordLink.trim().isEmpty()
						&& recordEmail != null && !recordEmail.trim().isEmpty()
						&& Arrays.asList(3, 6).contains(recordType)) {

					fixedThreadPool.execute(new Runnable() {

						public void run() {
							System.out.println("开始添加邮件里超链点击消息提醒和统计...");
							
							if (mailUid != null && !mailUid.trim().isEmpty() && recordType == 6) {
								System.out.println("开始添加邮件里超链点击统计...");
								Connection conn3 = null;
								PreparedStatement ps3 = null;
								ResultSet rs3 = null;
								try {
									int tryCount = 0;
									conn3 = DBUtil.getConnection();
									String sql2 = "select count(*) from ts_customer_access where res_type = 6 and access_url = ? and access_email = ? and user_id = ? and company_id = ? and mail_uid = ?";
									ps3 = conn3.prepareStatement(sql2);
									ps3.setObject(1, recordLink);
									ps3.setObject(2, recordEmail);
									ps3.setObject(3, userId);
									ps3.setObject(4, companyId);
									ps3.setObject(5, mailUid);
									
									int cou = 0;
									rs3 = ps3.executeQuery();
									if (rs3.first()) {
										cou = rs3.getInt(1);
									}
									if (cou <= 1) {//超过1条记录则不统计
										Connection conn2 = null;
										PreparedStatement ps2 = null;
										ResultSet rs2 = null;
										while (conn2 == null && tryCount < 3) {
											tryCount++;
											try {
												conn2 = WebmailDBUtil.getConnection();
											} catch (Exception e) {
												e.printStackTrace();
												try {
													Thread.sleep(5000);
												} catch (InterruptedException ex) {
													ex.printStackTrace();
												}
											}
										}
										
										sql2 = "select id from attr_link_count where mail_uid = ?";
										ps2 = conn2.prepareStatement(sql2);
										ps2.setObject(1, mailUid);
										rs2 = ps2.executeQuery();
										if (rs2.first()) {//已存在
											cou = rs2.getInt(1);
											
											rs2.close();
											ps2.close();
											
											sql2 = "update attr_link_count set link_count = link_count + 1 where id = ?";
											ps2 = conn2.prepareStatement(sql2);
											ps2.setObject(1, cou);
											ps2.executeUpdate();
										} else {//未存在
											rs2.close();
											ps2.close();
											
											sql2 = "insert into attr_link_count(mail_uid,file_count,link_count) values(?,?,?)";
											ps2 = conn2.prepareStatement(sql2);
											ps2.setObject(1, mailUid);
											ps2.setObject(2, 0);
											ps2.setObject(3, 1);
											ps2.executeUpdate();
										}
										//conn2.commit();
										
										WebmailDBUtil.close(rs2, ps2, conn2);
									}
									
								} catch (SQLException ex) {
									ex.printStackTrace();
								} finally {
									DBUtil.close(rs3, ps3, conn3);
								}
								
							}
							
							String title = null;

							Map<String, Object> mailInfo = null;

							if (recordType == 3) {

								mailInfo = YxyDBUtil.getMailInfo(mailUid);

								if (!mailInfo.get("code").equals(200)) {
									System.err.println("获取邮件信息失败："
											+ mailInfo.get("msg"));
									// return;
								}

							} else {
								mailInfo = WebmailDBUtil.getMailInfo(mailUid);

								if (!mailInfo.get("code").equals(200)) {
									System.err.println("获取邮件信息失败："
											+ mailInfo.get("msg"));
									// return;
								}
							}
							
							if (mailInfo.containsKey("subject")) {
								
								title = String.valueOf(mailInfo.get("subject"));
							}


							if (title != null && title.contains("###")) {
								title = title.split("###")[0];
							}

							if (title == null || title.trim().isEmpty()) {
								title = "未知";
							}

							Connection conn = null;
							PreparedStatement ps = null;
							ResultSet rs = null;

							try {
								//一个封邮件里的一个超链接一天提醒一次
								// 《xx》邮箱点开了您邮件/模板《xx》里的超链接《xx》
								conn = WebmailDBUtil.getConnection();
								
								String sql = "select id from sys_user_remind where to_days(remind_time) = to_days(now()) and remind_content like '《"
										+ recordEmail.trim() + "》%点开了您邮件《" + title.trim() + "》%超链接《" + recordLink.trim() + "》%' and remind_userid = " + recordUserId;
								
								ps = conn.prepareStatement(sql);
								rs = ps.executeQuery();
								
								int id = 0;
								if (rs.first()) {
									id = rs.getInt("id");
									System.out.println("已有消息id：" + id);
								}
								
								if (id == 0) {
									String content = "《"+recordEmail.trim()+"》邮箱点开了您邮件《"+title.trim()+"》里的超链接《"+recordLink.trim()+"》，请前往追踪统计查看！";
									
									sql = "insert into sys_user_remind(remind_userid,remind_type,remind_status,remind_time,remind_content,target_id,is_read) values(?,?,?,?,?,?,?)";
									ps = conn.prepareStatement(sql);
									ps.setObject(1, recordUserId);
									ps.setObject(2, 21);
									ps.setObject(3, 0);
									ps.setObject(4, DateFormat.getNowDate());
									ps.setObject(5, content);
									ps.setObject(6, recordId);
									ps.setObject(7, 0);
									ps.executeUpdate();
									
									System.out.println("消息提醒添加完成");
								}
								
							} catch (Exception e) {
								e.printStackTrace();
							} finally {
								WebmailDBUtil.close(rs, ps, conn);
							}

						}
					});
				}

				// 异步统计邮件里的超链
				// singleThreadExecutor.execute(new Runnable() {
				//
				// public void run() {
				//
				// TrackUtil.countLink(userId, companyId, mailUid,
				// accessUrl2, toMail, aid);
				// }
				// });

				// try的最后提交事务
				conn.commit();

			} else {
				System.err.println("无法获取ip");
			}

		} catch (Exception e) {
			if (conn != null) {
				try {
					// 回滚事务
					conn.rollback();
				} catch (SQLException ex) {
					ex.printStackTrace();
				}
			}
			e.printStackTrace();
			LogUtil.saveInfoLog(param.getString("ywxType") + "/" + userId,
					e);
		} finally {
			DBUtil.close(rs, ps, conn);
		}
	}
	
	/** 记录用户打开原邮件里的超链接的行为 */
	public static void recordFromOriginalMailInfo2(
			final HttpServletRequest request, HttpServletResponse response) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		int ywxType = Integer.valueOf(request.getParameter("ywxType")
				.toString().replace("3D", ""));
		
		Integer userId2 = 0;
		Integer companyId2 = 0;
		
		if (ywxType == 9) {//9(sale100.com独立)营销邮邮件里需要跟踪的超链
			ywxType = 3;
			
			Map<String, Object>  userInfo = CentreDBUtil.getUserInfo(request.getParameter("ywxUid"), request.getParameter("ywxCid"));
			if (!userInfo.get("code").equals(200)) {
				
				System.err.println("获取用户信息失败：" + userInfo.get("msg"));
				return;
			}
			
			userId2 = Integer.valueOf(userInfo.get("userId").toString());
			companyId2 = Integer.valueOf(userInfo.get("companyId").toString());
		} else {
			userId2 = Integer
					.valueOf(request.getParameter("ywxUid") == null ? "0" : request
							.getParameter("ywxUid").replace("3D", ""));
			companyId2 = Integer.valueOf(request
					.getParameter("ywxCid") == null ? "0" : request
							.getParameter("ywxCid").replace("3D", ""));
		}
		
		final Integer userId = userId2;
		final Integer companyId = companyId2;
		
		try {
			String ip = NetworkUtil.getIpAddress(request);
			
			if (ip != null && !ip.equalsIgnoreCase("unknown")
					&& !ip.equalsIgnoreCase("null")) {
				final String toMail = (request.getParameter("ywxMail") != null && request
						.getParameter("ywxMail").startsWith("3D")) ? URLDecoder.decode(request
								.getParameter("ywxMail").replaceFirst("3D", ""), "UTF-8")
								: URLDecoder.decode(request.getParameter("ywxMail"), "UTF-8");// 点读地址
						
						final String mailUid = (request.getParameter("ywxMid") != null && request
								.getParameter("ywxMid").startsWith("3D")) ? URLDecoder.decode(request
										.getParameter("ywxMid").replaceFirst("3D", ""), "UTF-8")
										: URLDecoder.decode(request.getParameter("ywxMid"), "UTF-8");// 邮件唯一码
								
								
								Boolean cookieEnabled = Boolean.valueOf(request.getParameter(
										"ywxCookie").replace("3D", ""));
								
								Integer ywxX = Integer
										.valueOf(request.getParameter("ywxX") == null ? "0"
												: request.getParameter("ywxX")
												.replace("3D", ""));
								Integer ywxH = Integer
										.valueOf(request.getParameter("ywxH") == null ? "0"
												: request.getParameter("ywxH")
												.replace("3D", ""));
								
								String poporimap = request.getParameter("poporimap");//null/0是pop，1是imap
								poporimap = poporimap == null ? "0" : poporimap.replace("3D", "").trim();
								
								String jsessionId = request.getSession().getId();
								
								if (toMail == null || mailUid == null || userId == null
										|| companyId == null || cookieEnabled == null
										|| ywxX == null || ywxH == null) {
									
									System.err.println("参数缺失");
									
									return;
								}
								
								String uid = null;
								List<String> emails = new ArrayList<String>();
								
								Cookie[] cookies = request.getCookies();
								
								if (cookies != null && cookies.length > 0) {
									
									for (Cookie cookie : cookies) {
										if (ConstantUtil.USER_IDENTITY_COOKIE.equals(cookie
												.getName())) {
											uid = cookie.getValue();
										}
										if (cookie.getName().startsWith(
												ConstantUtil.EMAIL_IDENTITY_COOKIE)) {
											if (cookie.getValue() != null
													&& emailPattern.matcher(cookie.getValue())
													.matches()) {
												
												emails.add(cookie.getValue());
											}
										}
									}
								}
								
								Map<String, Object> mailInfo = null;
								
								String title = (request.getParameter("ywxTitle") != null && request
										.getParameter("ywxTitle").startsWith("3D")) ? request
												.getParameter("ywxTitle").replaceFirst("3D", "")
												: request.getParameter("ywxTitle");
												
												if (title == null || title.trim().isEmpty()
														|| title.equals("null")) {
													
													if (ywxType == 3) {
														
														mailInfo = YxyDBUtil.getMailInfo(mailUid);
														
														if (!mailInfo.get("code").equals(200)) {
															System.err.println("获取邮件信息失败："
																	+ mailInfo.get("msg"));
															// return;
														}
														
													} else {
														mailInfo = WebmailDBUtil.getMailInfo(mailUid);
														
														if (!mailInfo.get("code").equals(200)) {
															System.err.println("获取邮件信息失败："
																	+ mailInfo.get("msg"));
															// return;
														}
													}
													
													if (mailInfo.containsKey("subject")) {
														
														title = String.valueOf(mailInfo.get("subject"));
													}
													
													if (title != null && title.contains("###")) {
														title = title.split("###")[0];
													}
													
												}
												
												conn = DBUtil.getConnection();
												conn.setAutoCommit(false);// 开启事务
												String sql = null;
												long count = 0L;
												
												String dbuid = null;// 邮箱在数据库中的第一条记录下的cookie标识
												
												if (toMail != null && !toMail.trim().isEmpty()) {
													
													sql = "select identity from ts_customer_access where access_email = ? order by create_time ASC limit 1";
													ps = conn.prepareStatement(sql);
													ps.setString(1, toMail);
													
													rs = ps.executeQuery();
													
													if (rs.first()) {
														dbuid = rs.getString("identity");
													}
													
													if (dbuid != null && !dbuid.equals(uid)) {
														uid = dbuid;
													}
												}
												
												// cookie里面没有UID则查该ip是否有浏览过，若有的话，则该用户的浏览器不支持cookie
												if (uid == null) {
													if (!cookieEnabled) {// 浏览器不支持cookie
														sql = "select identity,jsession_id from ts_customer_access where ip = ? order by create_time asc";
														ps = conn.prepareStatement(sql);
														ps.setString(1, ip);
														
														rs = ps.executeQuery();
														if (rs.first()) {// 有浏览过
															cookieEnabled = false;
															uid = rs.getString("identity");
															jsessionId = rs.getString("jsession_id");
															
															sql = "select identity from ts_customer_email where email = ?";
															ps = conn.prepareStatement(sql);
															ps.setString(1, toMail);
															
															rs = ps.executeQuery();
															if (rs.first()) {
																// 若是数据库已经存在cookie唯一标示，而又不对应，则替换
																// 在搜索引擎的爬虫下会造成数据混乱
																// if (!uid.equals(rs.getString("identity"))) {
																// sql =
																// "update ts_customer_access set identity = ? , access_email = ? where identity = ?";
																// ps = conn.prepareStatement(sql);
																// ps.setString(1, rs.getString("identity"));
																// ps.setString(2, toMail);
																// ps.setString(3, uid);
																//
																// ps.executeUpdate();
																//
																// uid = rs.getString("identity");
																// }
															} else {
																sql = "select count(*) from ts_customer_email where identity = ? and email = ?";
																ps = conn.prepareStatement(sql);
																ps.setString(1, uid);
																ps.setString(2, toMail);
																
																rs = ps.executeQuery();
																if (rs.first()) {
																	count = rs.getLong(1);
																}
																
																if (count == 0L) {
																	sql = "insert into ts_customer_email(identity,email,create_time) values(?,?,?)";
																	ps = conn.prepareStatement(sql);
																	ps.setString(1, uid);
																	ps.setString(2, toMail);
																	ps.setString(3, DateFormat.getNowDate());
																	// 添加email
																	ps.executeUpdate();
																}
															}
															
														}
													}
													
													// 没有浏览过
													if (uid == null) {
														sql = "select identity from ts_customer_email where email = ?";
														ps = conn.prepareStatement(sql);
														ps.setString(1, toMail);
														rs = ps.executeQuery();
														if (rs.first()) {
															uid = rs.getString("identity");
														} else {
															uid = UUID.randomUUID().toString();
														}
														
														sql = "insert into ts_customer(identity,type,create_time) values(?,?,?)";
														ps = conn.prepareStatement(sql);
														ps.setString(1, uid);
														ps.setInt(2, 1);// 客户类型，0：路人、1：已存在客户
														ps.setString(3, DateFormat.getNowDate());
														// 添加客户
														ps.executeUpdate();
														
														sql = "select count(*) from ts_customer_email where identity = ? and email = ?";
														ps = conn.prepareStatement(sql);
														ps.setString(1, uid);
														ps.setString(2, toMail);
														
														rs = ps.executeQuery();
														if (rs.first()) {
															count = rs.getLong(1);
														}
														
														if (count == 0L) {
															sql = "insert into ts_customer_email(identity,email,create_time) values(?,?,?)";
															ps = conn.prepareStatement(sql);
															ps.setString(1, uid);
															ps.setString(2, toMail);
															ps.setString(3, DateFormat.getNowDate());
															// 添加email
															ps.executeUpdate();
														}
													}
													
												} else {
													// sql =
													// "select identity from ts_customer_email where email = ?";
													// ps = conn.prepareStatement(sql);
													// ps.setString(1, toMail);
													// String identity = null;
													// rs = ps.executeQuery();
													// if (rs.first()) {
													// identity = rs.getString("identity");
													// }
													//
													// 若是已经存在cookie唯一标示，而数据库又有该邮箱的cookie，则替换
													// 在搜索引擎的爬虫下会造成数据混乱
													// if (identity != null && !uid.equals(identity)) {
													// sql =
													// "update ts_customer_access set identity = ? , access_email = ? where identity = ?";
													// ps = conn.prepareStatement(sql);
													// ps.setString(1, identity);
													// ps.setString(2, toMail);
													// ps.setString(3, uid);
													//
													// ps.executeUpdate();
													//
													// uid = identity;
													// }
													
													// 浏览器没有该客户邮箱且数据没有保存，则保存
													if (!emails.contains(toMail)) {
														
														sql = "select count(*) from ts_customer_email where identity = ? and email = ?";
														ps = conn.prepareStatement(sql);
														ps.setString(1, uid);
														ps.setString(2, toMail);
														
														rs = ps.executeQuery();
														if (rs.first()) {
															count = rs.getLong(1);
														}
														
														if (count == 0L) {
															sql = "insert into ts_customer_email(identity,email,create_time) values(?,?,?)";
															ps = conn.prepareStatement(sql);
															ps.setString(1, uid);
															ps.setString(2, toMail);
															ps.setString(3, DateFormat.getNowDate());
															// 添加email
															ps.executeUpdate();
														}
													}
													
												}
												
												if (!emails.contains(toMail)) {
													emails.add(toMail);
												}
												
												// 数据组装
												String agentString = request.getHeader("User-Agent");
												String referer = request.getParameter("ywxReferrer");
												String fromDomain = null;
												
												if (referer != null && !referer.trim().isEmpty()) {
													try {
														referer = URLDecoder.decode(referer, "UTF-8");
														fromDomain = new URL(referer).getHost();
													} catch (Exception e) {
														System.err.println("referer域名解析失败："
																+ e.getClass().getName()
																+ (e.getMessage() == null ? "" : ("->" + e
																		.getMessage())));
													}
												}
												
												String language = request.getLocale().getLanguage();
												if (request.getLocale().getCountry() != null
														&& !request.getLocale().getCountry().trim().isEmpty()) {
													language += "-" + request.getLocale().getCountry();
												}
												
												String accessUrl = (request.getParameter("ywxTo") != null && request
														.getParameter("ywxTo").startsWith("3D")) ? request
																.getParameter("ywxTo").replaceFirst("3D", "") : request
																.getParameter("ywxTo");
																
																// final String accessUrl2 = accessUrl;
																
																String accessDomain = null;
																if (accessUrl != null) {
																	try {
																		accessUrl = URLDecoder.decode(accessUrl, "UTF-8");
																		
																		accessDomain = new URL(accessUrl).getHost();
																	} catch (Exception e) {
																		System.err.println("ywxTo域名解析失败："
																				+ e.getClass().getName()
																				+ (e.getMessage() == null ? "" : ("->" + e
																						.getMessage())));
																	}
																}
																
																// 解析ip
																String url = controlCentreUrl + "/webservice/ip";
																Map<String, Object> params = new HashMap<String, Object>();
																params.put("ip", ip);
																
																String continent = null;
																String country = null;
																String province = null;
																String city = null;
																String address = null;
																String netProvider = null;
																
																Map<String, Object> result = HttpUtil.doGet(url, params);
																if (result.get("code").equals(200)) {
																	Object data = result.get("data");
																	
																	JSONObject json = JSONObject.fromObject(data);
																	
																	if (json.getInt("code") == 200) {
																		json = JSONObject.fromObject(json.get("result"));
																		
																		netProvider = json.getString("isp");
																		continent = json.getString("land");
																		country = json.getString("country");
																		province = json.getString("prov");
																		city = json.getString("city");
																		address = json.getString("dist");
																	} else {
																		System.err.println("ip解析失败：" + json.get("result"));
																	}
																} else {
																	System.err.println("ip解析失败：" + result.get("msg"));
																}
																
																UserAgent userAgent = UserAgent
																		.parseUserAgentString(agentString);
																
																if ((accessUrl == null || accessUrl.trim().isEmpty())
																		|| (mailUid == null || mailUid.trim().isEmpty())
																		|| (toMail == null || toMail.trim().isEmpty())) {
																	// 只要有一个为空就放弃添加
																	return;
																}
																
																if (toMail != null && !toMail.trim().isEmpty()) {
																	if (!emailPattern.matcher(toMail.trim().toLowerCase())
																			.matches()) {
																		
																		// 邮箱格式错误就放弃添加
																		return;
																	}
																}
																
																// 添加浏览记录
																sql = "insert into ts_customer_access(identity,user_id,company_id,mail_uid,title,from_url,from_domain,access_url,access_domain,ip,jsession_id,continent,country,province,city,address,device_type,browser_name,language,screen_x,screen_y,net_provider,os,search_word,user_agent,cookie_enable,access_time,access_email,res_type,create_time,template_id,customer_id,from_type,count_id,status,is_imap) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
																ps = conn.prepareStatement(sql);
																ps.setString(1, uid);// cookie唯一标示
																ps.setInt(2, userId);// 用户id
																ps.setInt(3, companyId);// 企业id
																ps.setString(4, mailUid);// 邮件唯一标示
																ps.setString(5, title);// 来源标题
																ps.setString(6, referer);// 来源url
																ps.setString(7, fromDomain);// 来源域名
																ps.setString(8, accessUrl);// 受访问url
																ps.setString(9, accessDomain);// 受访问域名
																ps.setString(10, ip);// 客户端ip
																ps.setString(11, jsessionId);// 回话id
																ps.setString(12, continent);// 访问者所属洲
																ps.setString(13, country);// 访问者所属国际
																ps.setString(14, province);// //访问者所属省份
																ps.setString(15, city);// //访问者所属城市
																ps.setString(16, address);// //访问者所属详细地址
																ps.setInt(17, TrackUtil.getDeviceType(userAgent));// 设备类型
																ps.setString(18, userAgent.getBrowser().getName());// 浏览器名称
																ps.setString(19, language);// 浏览器设置的语言
																ps.setInt(20, ywxX);// 设备宽度
																ps.setInt(21, ywxH);// 设备高度
																ps.setString(22, netProvider);// 网络接入商
																ps.setString(23, userAgent.getOperatingSystem().getName());// 操作系统名称
																ps.setString(24, TrackUtil.getSearchWord(referer));// 搜索词
																ps.setString(25, agentString);// 浏览器ua
																ps.setInt(26, cookieEnabled ? 1 : 0);// 是否支持cookie,1支持、0不支持
																ps.setInt(27, 0);// 访问时长，单位毫秒
																ps.setString(28, toMail);// 客户邮箱
																ps.setInt(29, ywxType);// 受访问资源类型，0营销邮邮件、1营销邮邮件HTML页面、2易外销用户站点、3营销邮邮件里需要跟踪的超链、4营销邮邮件HTML页面里需要跟踪的超链、5易外销邮件、6易外销邮件里需要跟踪的超链、7询盘
																ps.setString(30, DateFormat.getNowDate());// 创建时间
																ps.setInt(31, 0);// 模板id
																ps.setInt(32, WebmailDBUtil.getCustomerId(userId, toMail));// 客户id
																ps.setInt(33, 3);// 来源类别，-1无来源url、0：外链、1社交、2搜索、3邮件
																ps.setLong(34, 0L);// 统计id
																ps.setInt(35, 1);// 状态，0删除、1正常
																ps.setInt(36, Integer.valueOf(poporimap));// 是否是imap邮件，0否、1是
																// 提交添加
																ps.executeUpdate();
																// 获取自动生成的id
																rs = ps.getGeneratedKeys();
																long id = 0L;
																if (rs.first()) {
																	id = rs.getLong(1);
																}
																
																// final long aid = id;
																
																sql = "select id,create_time from ts_customer_access where id != ? and identity = ? and user_id = ? and company_id = ? and to_days(create_time) = to_days(now()) order by create_time desc";
																ps = conn.prepareStatement(sql);
																ps.setLong(1, id);
																ps.setString(2, uid);
																ps.setInt(3, userId);
																ps.setInt(4, companyId);
																// 有上一条记录则修改上一条记录的访问时长
																rs = ps.executeQuery();
																if (rs.first()) {
																	long milliSeconds = DateFormat.milliSeconds(
																			rs.getString("create_time"),
																			DateFormat.getNowDate());
																	
																	if (milliSeconds < (1000 * 60 * 60 * 2)) {// 当天前后两个请求大于2个小时的丢掉
																		sql = "update ts_customer_access set access_time = ? where id = ?";
																		ps = conn.prepareStatement(sql);
																		ps.setLong(1, milliSeconds);
																		ps.setLong(2, rs.getLong("id"));
																		// 修改上一个请求的访问时长
																		ps.executeUpdate();
																	}
																	
																}
																
																// 修改为已存在客户
																// sql = "update ts_customer set type = ? where identity = ?";
																// ps = conn.prepareStatement(sql);
																// ps.setInt(1, 1);// 客户类型，0：路人、1：已存在客户
																// ps.setString(2, uid);
																// // 修改客户
																// ps.executeUpdate();
																
																// 设置cookie
																String serverDomain = request.getServerName();
																int len = 0;
																if (!ipPattern.matcher(serverDomain).matches()) {
																	
																	String[] domains = serverDomain.split("\\.");
																	len = domains.length;
																	if (len > 2) {
																		serverDomain = domains[len - 2] + "."
																				+ domains[len - 1];
																	}
																}
																
																Cookie cookie = new Cookie(ConstantUtil.USER_IDENTITY_COOKIE,
																		uid);
																cookie.setMaxAge(ConstantUtil.HALF_CENTURY);
																cookie.setDomain(serverDomain);
																
																response.addCookie(cookie);
																
																len = emails.size();
																
																for (int i = 0; i < len; i++) {
																	cookie = new Cookie(ConstantUtil.EMAIL_IDENTITY_COOKIE
																			+ "_" + i, emails.get(i));
																	cookie.setMaxAge(ConstantUtil.HALF_CENTURY);
																	cookie.setDomain(serverDomain);
																	
																	response.addCookie(cookie);
																}
																
																final int recordType = ywxType;
																final long recordId = id;
																final String recordEmail = toMail;
																final String recordLink = (accessUrl == null ? null : URLDecoder.decode(accessUrl, "UTF-8"));
																final int recordUserId = userId;
																
																// 点击超链接时异步添加消息提醒：3营销邮邮件里需要跟踪的超链、6易外销邮件里需要跟踪的超链
																if (recordLink != null && !recordLink.trim().isEmpty()
																		&& recordEmail != null && !recordEmail.trim().isEmpty()
																		&& Arrays.asList(3, 6).contains(recordType)) {
																	
																	fixedThreadPool.execute(new Runnable() {
																		
																		public void run() {
																			System.out.println("开始添加邮件里超链点击消息提醒和统计...");
																			
																			if (mailUid != null && !mailUid.trim().isEmpty() && recordType == 6) {
																				System.out.println("开始添加邮件里超链点击统计...");
																				Connection conn3 = null;
																				PreparedStatement ps3 = null;
																				ResultSet rs3 = null;
																				try {
																					int tryCount = 0;
																					conn3 = DBUtil.getConnection();
																					String sql2 = "select count(*) from ts_customer_access where res_type = 6 and access_url = ? and access_email = ? and user_id = ? and company_id = ? and mail_uid = ?";
																					ps3 = conn3.prepareStatement(sql2);
																					ps3.setObject(1, recordLink);
																					ps3.setObject(2, recordEmail);
																					ps3.setObject(3, userId);
																					ps3.setObject(4, companyId);
																					ps3.setObject(5, mailUid);
																					
																					int cou = 0;
																					rs3 = ps3.executeQuery();
																					if (rs3.first()) {
																						cou = rs3.getInt(1);
																					}
																					if (cou <= 1) {//超过1条记录则不统计
																						Connection conn2 = null;
																						PreparedStatement ps2 = null;
																						ResultSet rs2 = null;
																						while (conn2 == null && tryCount < 3) {
																							tryCount++;
																							try {
																								conn2 = WebmailDBUtil.getConnection();
																							} catch (Exception e) {
																								e.printStackTrace();
																								try {
																									Thread.sleep(5000);
																								} catch (InterruptedException ex) {
																									ex.printStackTrace();
																								}
																							}
																						}
																						
																						sql2 = "select id from attr_link_count where mail_uid = ?";
																						ps2 = conn2.prepareStatement(sql2);
																						ps2.setObject(1, mailUid);
																						rs2 = ps2.executeQuery();
																						if (rs2.first()) {//已存在
																							cou = rs2.getInt(1);
																							
																							rs2.close();
																							ps2.close();
																							
																							sql2 = "update attr_link_count set link_count = link_count + 1 where id = ?";
																							ps2 = conn2.prepareStatement(sql2);
																							ps2.setObject(1, cou);
																							ps2.executeUpdate();
																						} else {//未存在
																							rs2.close();
																							ps2.close();
																							
																							sql2 = "insert into attr_link_count(mail_uid,file_count,link_count) values(?,?,?)";
																							ps2 = conn2.prepareStatement(sql2);
																							ps2.setObject(1, mailUid);
																							ps2.setObject(2, 0);
																							ps2.setObject(3, 1);
																							ps2.executeUpdate();
																						}
																						//conn2.commit();
																						
																						WebmailDBUtil.close(rs2, ps2, conn2);
																					}
																					
																				} catch (SQLException ex) {
																					ex.printStackTrace();
																				} finally {
																					DBUtil.close(rs3, ps3, conn3);
																				}
																				
																			}
																			
																			String title = null;
																			
																			Map<String, Object> mailInfo = null;
																			
																			if (recordType == 3) {
																				
																				mailInfo = YxyDBUtil.getMailInfo(mailUid);
																				
																				if (!mailInfo.get("code").equals(200)) {
																					System.err.println("获取邮件信息失败："
																							+ mailInfo.get("msg"));
																					// return;
																				}
																				
																			} else {
																				mailInfo = WebmailDBUtil.getMailInfo(mailUid);
																				
																				if (!mailInfo.get("code").equals(200)) {
																					System.err.println("获取邮件信息失败："
																							+ mailInfo.get("msg"));
																					// return;
																				}
																			}
																			
																			if (mailInfo.containsKey("subject")) {
																				
																				title = String.valueOf(mailInfo.get("subject"));
																			}
																			
																			
																			if (title != null && title.contains("###")) {
																				title = title.split("###")[0];
																			}
																			
																			if (title == null || title.trim().isEmpty()) {
																				title = "未知";
																			}
																			
																			Connection conn = null;
																			PreparedStatement ps = null;
																			ResultSet rs = null;
																			
																			try {
																				//一个封邮件里的一个超链接一天提醒一次
																				// 《xx》邮箱点开了您邮件/模板《xx》里的超链接《xx》
																				conn = WebmailDBUtil.getConnection();
																				
																				String sql = "select id from sys_user_remind where to_days(remind_time) = to_days(now()) and remind_content like '《"
																						+ recordEmail.trim() + "》%点开了您邮件《" + title.trim() + "》%超链接《" + recordLink.trim() + "》%' and remind_userid = " + recordUserId;
																				
																				ps = conn.prepareStatement(sql);
																				rs = ps.executeQuery();
																				
																				int id = 0;
																				if (rs.first()) {
																					id = rs.getInt("id");
																					System.out.println("已有消息id：" + id);
																				}
																				
																				if (id == 0) {
																					String content = "《"+recordEmail.trim()+"》邮箱点开了您邮件《"+title.trim()+"》里的超链接《"+recordLink.trim()+"》，请前往追踪统计查看！";
																					
																					sql = "insert into sys_user_remind(remind_userid,remind_type,remind_status,remind_time,remind_content,target_id,is_read) values(?,?,?,?,?,?,?)";
																					ps = conn.prepareStatement(sql);
																					ps.setObject(1, recordUserId);
																					ps.setObject(2, 21);
																					ps.setObject(3, 0);
																					ps.setObject(4, DateFormat.getNowDate());
																					ps.setObject(5, content);
																					ps.setObject(6, recordId);
																					ps.setObject(7, 0);
																					ps.executeUpdate();
																					
																					System.out.println("消息提醒添加完成");
																				}
																				
																			} catch (Exception e) {
																				e.printStackTrace();
																			} finally {
																				WebmailDBUtil.close(rs, ps, conn);
																			}
																			
																		}
																	});
																}
																
																// 异步统计邮件里的超链
																// singleThreadExecutor.execute(new Runnable() {
																//
																// public void run() {
																//
																// TrackUtil.countLink(userId, companyId, mailUid,
																// accessUrl2, toMail, aid);
																// }
																// });
																
																// try的最后提交事务
																conn.commit();
																
			} else {
				System.err.println("无法获取ip");
			}
			
		} catch (Exception e) {
			if (conn != null) {
				try {
					// 回滚事务
					conn.rollback();
				} catch (SQLException ex) {
					ex.printStackTrace();
				}
			}
			e.printStackTrace();
			
			LogUtil.saveInfoLog(request.getParameter("ywxType") + "/" + userId,
					e);
		} finally {
			DBUtil.close(rs, ps, conn);
		}
	}
	
	/** 记录用户打开SEO HTML邮件里的超链接、及其本身HTML页面和用户官网的行为 */
	public static void recordFromHtmlInfo(HttpServletRequest request,
			HttpServletResponse response) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Integer userId = 0;

		try {
			String ip = NetworkUtil.getIpAddress(request);

			if (ip != null && !ip.equalsIgnoreCase("unknown")
					&& !ip.equalsIgnoreCase("null")) {
				Integer templateId = Integer.valueOf(request
						.getParameter("ywxMid") == null ? "0" : request
						.getParameter("ywxMid"));// 邮件模板id

				userId = Integer
						.valueOf(request.getParameter("ywxUid") == null ? "0"
								: request.getParameter("ywxUid"));
				Integer companyId = Integer.valueOf(request
						.getParameter("ywxCid") == null ? "0" : request
						.getParameter("ywxCid"));

				Boolean cookieEnabled = Boolean.valueOf(request
						.getParameter("ywxCookie"));

				Integer ywxX = Integer
						.valueOf(request.getParameter("ywxX") == null ? "0"
								: request.getParameter("ywxX"));
				Integer ywxH = Integer
						.valueOf(request.getParameter("ywxH") == null ? "0"
								: request.getParameter("ywxH"));
				
				String poporimap = request.getParameter("poporimap");//null/0是pop，1是imap
	  			poporimap = poporimap == null ? "0" : poporimap.replace("3D", "").trim();

				String jsessionId = request.getSession().getId();

				if (userId == null || companyId == null
						|| cookieEnabled == null || ywxX == null
						|| ywxH == null) {

					System.err.println("参数缺失");

					return;
				}
				
				String uid = null;
				List<String> emails = new ArrayList<String>();

				Cookie[] cookies = request.getCookies();

				if (cookies != null && cookies.length > 0) {

					for (Cookie cookie : cookies) {
						if (ConstantUtil.USER_IDENTITY_COOKIE.equals(cookie
								.getName())) {
							uid = cookie.getValue();
						}
						if (cookie.getName().startsWith(
								ConstantUtil.EMAIL_IDENTITY_COOKIE)) {
							if (cookie.getValue() != null
									&& emailPattern.matcher(cookie.getValue())
											.matches()) {

								emails.add(cookie.getValue());
							}
						}
					}
				}

				Integer ywxType = Integer.valueOf(request
						.getParameter("ywxType") == null ? request
						.getAttribute("ywxType").toString() : request
						.getParameter("ywxType"));

				String title = request.getParameter("ywxTitle");

				if (ywxType == 1 || ywxType == 4) {

					if (title == null || title.trim().isEmpty()
							|| title.equals("null")) {
						Map<String, Object> templateInfo = YxyDBUtil
								.getTemplateInfo(templateId);

						if (!templateInfo.get("code").equals(200)) {
							System.err.println("获取模板信息失败："
									+ templateInfo.get("msg"));
//							return;
						} else {
							
							title = String.valueOf(templateInfo.get("subject"));
						}

					}
				}

				conn = DBUtil.getConnection();
				conn.setAutoCommit(false);// 开启事务
				String sql = null;
				long count = 0L;
				String toMail = request.getParameter("ywxMail");
				
				if (toMail != null) {
					toMail = URLDecoder.decode(toMail, "UTF-8");
				}

				Map<String, Object> linkInfo = null;

				// cookie里面没有UID则查该ip是否有浏览过，若有的话，则该用户的浏览器不支持cookie
				if (uid == null) {
					if (!cookieEnabled) {// 浏览器不支持cookie
						sql = "select identity,jsession_id from ts_customer_access where ip = ? order by create_time asc";
						ps = conn.prepareStatement(sql);
						ps.setString(1, ip);

						rs = ps.executeQuery();
						if (rs.first()) {// 有浏览过
							uid = rs.getString("identity");
							jsessionId = rs.getString("jsession_id");

							sql = "select email from ts_customer_email where identity = ?";
							ps = conn.prepareStatement(sql);
							ps.setString(1, uid);

							String ems = "";

							rs = ps.executeQuery();
							while (rs.next()) {
								ems += ",'" + rs.getString("email") + "'";
							}

							if (request.getParameter("ywxMail") != null) {
								ems += ",'" + request.getParameter("ywxMail")
										+ "'";
							}

							if (ems.trim().length() > 0) {
								ems = ems.substring(1);
								linkInfo = WebmailDBUtil.getUserEmail(userId,
										companyId, ems);

								toMail = String.valueOf(linkInfo.get("email"));
								if (ywxType != 8 && linkInfo.get("userId") != null) {
									userId = Integer.valueOf(linkInfo.get(
											"userId").toString());
								}

							}

						}
					}

					// 没有浏览过
					if (uid == null) {
						uid = UUID.randomUUID().toString();

						sql = "insert into ts_customer(identity,type,create_time) values(?,?,?)";
						ps = conn.prepareStatement(sql);
						ps.setString(1, uid);
						ps.setInt(2, 1);// 客户类型，0：路人、1：已存在客户
						ps.setString(3, DateFormat.getNowDate());
						// 添加客户
						ps.executeUpdate();

						if (toMail != null && !toMail.equals("null")) {

							sql = "select count(*) from ts_customer_email where identity = ? and email = ?";
							ps = conn.prepareStatement(sql);
							ps.setString(1, uid);
							ps.setString(2, toMail);

							rs = ps.executeQuery();
							if (rs.first()) {
								count = rs.getLong(1);
							}

							if (count == 0L) {
								sql = "insert into ts_customer_email(identity,email,create_time) values(?,?,?)";
								ps = conn.prepareStatement(sql);
								ps.setString(1, uid);
								ps.setString(2, toMail);
								ps.setString(3, DateFormat.getNowDate());
								// 添加email
								ps.executeUpdate();
							}
						}
					}

				} else {
					String ems = "";

					if (emails.size() > 0) {// 浏览器里有邮箱
						for (String email : emails) {
							ems += ",'" + email + "'";
						}

						if (request.getParameter("ywxMail") != null) {
							ems += ",'" + request.getParameter("ywxMail") + "'";
						}

						if (ems.trim().length() > 0) {
							ems = ems.substring(1);
							linkInfo = WebmailDBUtil.getUserEmail(userId,
									companyId, ems);

							toMail = String.valueOf(linkInfo.get("email"));
							if (ywxType != 8 && linkInfo.get("userId") != null) {
								userId = Integer.valueOf(linkInfo.get("userId")
										.toString());
							}
						}

						// 浏览器有客户邮箱且数据没有保存，则保存
						if (toMail != null && !toMail.equals("null")) {

							sql = "select count(*) from ts_customer_email where identity = ? and email = ?";
							ps = conn.prepareStatement(sql);
							ps.setString(1, uid);
							ps.setString(2, toMail);

							rs = ps.executeQuery();
							if (rs.first()) {
								count = rs.getLong(1);
							}

							if (count == 0L) {
								sql = "insert into ts_customer_email(identity,email,create_time) values(?,?,?)";
								ps = conn.prepareStatement(sql);
								ps.setString(1, uid);
								ps.setString(2, toMail);
								ps.setString(3, DateFormat.getNowDate());
								// 添加email
								ps.executeUpdate();
							}
						}

					} else {// 浏览器里没有邮箱
						sql = "select email from ts_customer_email where identity = ?";
						ps = conn.prepareStatement(sql);
						ps.setString(1, uid);

						rs = ps.executeQuery();
						while (rs.next()) {
							ems += ",'" + rs.getString("email") + "'";
						}

						if (request.getParameter("ywxMail") != null) {
							ems += ",'" + request.getParameter("ywxMail") + "'";
						}

						if (ems.trim().length() > 0) {
							ems = ems.substring(1);
							linkInfo = WebmailDBUtil.getUserEmail(userId,
									companyId, ems);

							toMail = String.valueOf(linkInfo.get("email"));
							if (ywxType != 8 && linkInfo.get("userId") != null) {
								userId = Integer.valueOf(linkInfo.get("userId")
										.toString());
							}
						}
					}

				}

				if ((toMail == null || toMail.trim().isEmpty() || toMail
						.equals("null"))
						&& request.getParameter("ywxMail") != null) {
					toMail = request.getParameter("ywxMail");
				}

				if (toMail != null && !toMail.equals("null")
						&& !emails.contains(toMail)) {
					emails.add(toMail);
				}

				// 数据组装
				String agentString = request.getHeader("User-Agent");
				String referer = request.getParameter("ywxReferrer");
				String fromDomain = null;

				if (referer != null && !referer.trim().isEmpty()) {
					try {
						referer = URLDecoder.decode(referer, "UTF-8");

						fromDomain = new URL(referer).getHost();
					} catch (Exception e) {
						System.err.println("referer域名解析失败："
								+ e.getClass().getName()
								+ (e.getMessage() == null ? "" : ("->" + e
										.getMessage())));
					}
				}

				String language = request.getLocale().getLanguage();
				if (request.getLocale().getCountry() != null
						&& !request.getLocale().getCountry().trim().isEmpty()) {
					language += "-" + request.getLocale().getCountry();
				}

				String accessUrl = request.getParameter("ywxTo");
				String accessDomain = null;
				if (accessUrl != null) {
					try {
						accessUrl = URLDecoder.decode(accessUrl, "UTF-8");

						accessDomain = new URL(accessUrl).getHost();
					} catch (Exception e) {
						System.err.println("ywxTo域名解析失败："
								+ e.getClass().getName()
								+ (e.getMessage() == null ? "" : ("->" + e
										.getMessage())));
					}
				}

				// 解析ip
				String url = controlCentreUrl + "/webservice/ip";
				Map<String, Object> params = new HashMap<String, Object>();
				params.put("ip", ip);

				String continent = null;
				String country = null;
				String province = null;
				String city = null;
				String address = null;
				String netProvider = null;

				JSONObject areaJson = null;
				
				Map<String, Object> result = HttpUtil.doGet(url, params);
				if (result.get("code").equals(200)) {
					Object data = result.get("data");

					areaJson = JSONObject.fromObject(data);

					if (areaJson.getInt("code") == 200) {
						areaJson = JSONObject.fromObject(areaJson.get("result"));

						netProvider = areaJson.getString("isp");
						continent = areaJson.getString("land");
						country = areaJson.getString("country");
						province = areaJson.getString("prov");
						city = areaJson.getString("city");
						address = areaJson.getString("dist");
					} else {
						System.err.println("ip解析失败：" + areaJson.get("result"));
					}
				} else {
					System.err.println("ip解析失败：" + result.get("msg"));
				}

				UserAgent userAgent = UserAgent
						.parseUserAgentString(agentString);

				if (toMail != null && toMail.equals("null")) {
					toMail = null;
				}

				if (toMail == null && emails.size() > 0) {
					toMail = emails.get(0);
				}

				if (userId == null || userId == 0) {
					// 没有用户id则获取设置id
					userId = WebmailDBUtil.getUserId(companyId, accessUrl);
					// 没有用户id则获取管理员id
					if (userId == 0) {
						userId = CentreDBUtil.getUserId(companyId);
					}
				}

				if (toMail != null && !toMail.trim().isEmpty()) {
					if (!emailPattern.matcher(toMail.trim().toLowerCase())
							.matches()) {
						// 邮箱格式错误就放弃添加
						
						System.err.println("邮箱格式错误就放弃添加：" + toMail);
						return;
					}
				}

				toMail = ywxType == 7 ? request.getParameter("ywxMail")
						: toMail;

				String dbuid = null;// 邮箱在数据库中的第一条记录下的cookie标识

				if (toMail != null && !toMail.trim().isEmpty()) {

					sql = "select identity from ts_customer_access where access_email = ? order by create_time ASC limit 1";
					ps = conn.prepareStatement(sql);
					ps.setString(1, toMail);

					rs = ps.executeQuery();

					if (rs.first()) {
						dbuid = rs.getString("identity");
					}

					if (dbuid != null && !dbuid.equals(uid)) {
						uid = dbuid;
					}
				}

				if (toMail != null && !toMail.trim().isEmpty()
						&& !toMail.equalsIgnoreCase("null")) {
					sql = "select count(*) from ts_customer_email where identity = ? and email = ?";
					ps = conn.prepareStatement(sql);
					ps.setString(1, uid);
					ps.setString(2, toMail);

					rs = ps.executeQuery();
					if (rs.first()) {
						count = rs.getLong(1);
					}

					if (count == 0L) {
						sql = "insert into ts_customer_email(identity,email,create_time) values(?,?,?)";
						ps = conn.prepareStatement(sql);
						ps.setString(1, uid);
						ps.setString(2, toMail);
						ps.setString(3, DateFormat.getNowDate());
						// 添加email
						ps.executeUpdate();
					}

				}
				
				final int customerId = WebmailDBUtil.getCustomerId(userId, toMail);

				// 添加浏览记录
				sql = "insert into ts_customer_access(identity,user_id,company_id,mail_uid,title,from_url,from_domain,access_url,access_domain,ip,jsession_id,continent,country,province,city,address,device_type,browser_name,language,screen_x,screen_y,net_provider,os,search_word,user_agent,cookie_enable,access_time,access_email,res_type,create_time,template_id,customer_id,from_type,count_id,status,is_imap) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
				ps = conn.prepareStatement(sql);
				ps.setString(1, uid);// cookie唯一标示
				ps.setInt(2, userId);// 用户id
				ps.setInt(3, companyId);// 企业id
				ps.setString(4, null);// 邮件唯一标示
				ps.setString(5, title);// 来源标题
				ps.setString(6, referer);// 来源url
				ps.setString(7, fromDomain);// 来源域名
				ps.setString(8, accessUrl);// 受访问url
				ps.setString(9, accessDomain);// 受访问域名
				ps.setString(10, ip);// 客户端ip
				ps.setString(11, jsessionId);// 回话id
				ps.setString(12, continent);// 访问者所属洲
				ps.setString(13, country);// 访问者所属国际
				ps.setString(14, province);// //访问者所属省份
				ps.setString(15, city);// //访问者所属城市
				ps.setString(16, address);// //访问者所属详细地址
				ps.setInt(17, TrackUtil.getDeviceType(userAgent));// 设备类型
				ps.setString(18, userAgent.getBrowser().getName());// 浏览器名称
				ps.setString(19, language);// 浏览器设置的语言
				ps.setInt(20, ywxX);// 设备宽度
				ps.setInt(21, ywxH);// 设备高度
				ps.setString(22, netProvider);// 网络接入商
				ps.setString(23, userAgent.getOperatingSystem().getName());// 操作系统名称
				ps.setString(24, TrackUtil.getSearchWord(referer));// 搜索词
				ps.setString(25, agentString);// 浏览器ua
				ps.setInt(26, cookieEnabled ? 1 : 0);// 是否支持cookie,1支持、0不支持
				ps.setInt(27, 0);// 访问时长，单位毫秒
				ps.setString(28, toMail);// 客户邮箱
				ps.setInt(29, ywxType);// 受访问资源类型，0营销邮邮件、1营销邮邮件HTML页面、2易外销用户站点、3营销邮邮件里需要跟踪的超链、4营销邮邮件HTML页面里需要跟踪的超链、5易外销邮件、6易外销邮件里需要跟踪的超链、7询盘
				ps.setString(30, DateFormat.getNowDate());// 创建时间
				ps.setInt(31, templateId);// 模板id
				ps.setInt(32, customerId);// 客户id
				ps.setInt(33, TrackUtil.getFromType(referer));// 来源类别，-1无来源url、0：外链、1社交、2搜索、3邮件
				ps.setLong(34, 0L);// 统计id
				ps.setInt(35, 1);// 状态，0删除、1正常
				ps.setInt(36, Integer.valueOf(poporimap));// 是否是imap邮件，0否、1是
				// 提交添加
				ps.executeUpdate();
				// 获取自动生成的id
				rs = ps.getGeneratedKeys();
				long id = 0L;
				if (rs.first()) {
					id = rs.getLong(1);
				}

				sql = "select id,create_time from ts_customer_access where id != ? and identity = ? and user_id = ? and company_id = ? and to_days(create_time) = to_days(now()) order by create_time desc";
				ps = conn.prepareStatement(sql);
				ps.setLong(1, id);
				ps.setString(2, uid);
				ps.setInt(3, userId);
				ps.setInt(4, companyId);
				// 有上一条记录则修改上一条记录的访问时长
				rs = ps.executeQuery();
				if (rs.first()) {
					long milliSeconds = DateFormat.milliSeconds(
							rs.getString("create_time"),
							DateFormat.getNowDate());

					if (milliSeconds < (1000 * 60 * 60 * 2)) {// 当天前后两个请求大于2个小时的丢掉
						sql = "update ts_customer_access set access_time = ? where id = ?";
						ps = conn.prepareStatement(sql);
						ps.setLong(1, milliSeconds);
						ps.setLong(2, rs.getLong("id"));
						// 修改上一个请求的访问时长
						ps.executeUpdate();
					}

				}

				// 修改为已存在客户
				// sql = "update ts_customer set type = ? where identity = ?";
				// ps = conn.prepareStatement(sql);
				// ps.setInt(1, 1);// 客户类型，0：路人、1：已存在客户
				// ps.setString(2, uid);
				// // 修改客户
				// ps.executeUpdate();

				// 设置cookie
				String serverDomain = request.getServerName();
				int len = 0;
				if (!ipPattern.matcher(serverDomain).matches()) {

					String[] domains = serverDomain.split("\\.");
					len = domains.length;
					if (len > 2) {
						serverDomain = domains[len - 2] + "."
								+ domains[len - 1];
					}
				}

				Cookie cookie = new Cookie(ConstantUtil.USER_IDENTITY_COOKIE,
						uid);
				cookie.setMaxAge(ConstantUtil.HALF_CENTURY);
				cookie.setDomain(serverDomain);

				response.addCookie(cookie);

				emails.remove("null");
				len = emails.size();

				for (int i = 0; i < len; i++) {
					cookie = new Cookie(ConstantUtil.EMAIL_IDENTITY_COOKIE
							+ "_" + i, emails.get(i));
					cookie.setMaxAge(ConstantUtil.HALF_CENTURY);
					cookie.setDomain(serverDomain);

					response.addCookie(cookie);
				}
				
				final JSONObject areaJson2 = areaJson;
				final int recordType = ywxType;
				final long recordId = id;
				final String recordEmail = toMail;
				final String recordLink = (accessUrl == null ? null : URLDecoder.decode(accessUrl, "UTF-8"));
				final int recordUserId = userId;
				final int recordTemplateId = templateId;
				System.out.println("ok...");
				// 点击超链接时异步添加消息提醒：4营销邮邮件HTML页面里需要跟踪的超链
				if ((recordLink != null && !recordLink.trim().isEmpty())
						&& (recordType == 2 || recordType == 4)) {

					fixedThreadPool.execute(new Runnable() {
						public void run() {
							System.out.println("开始添加消息提醒，recordType：" + recordType);
							if (recordType == 2) {
								String chatType = "Unknown";
								
								if (recordLink.startsWith("skype:live:")) {
									chatType = "Skype";
								} else if (recordLink.startsWith("https://api.whatsapp.com/send")) {
									chatType = "WhatAPP";
								} else if (recordLink.startsWith("http://wpa.qq.com/msgrd")) {
									chatType = "QQ";
								} else if (recordLink.startsWith("http://amos.alicdn.com/msg.aw")) {
									chatType = "阿里旺旺";
								}
								
								if (!chatType.equals("Unknown")) {
									System.out.println("开始添加页面在线咨询超链点击消息提醒...");
									String name = null;
									if (customerId > 0) {
										name = WebmailDBUtil.getCustomerName(customerId);
									}
									if (name == null || name.trim().isEmpty()) {
										name = "新访客";
									}

									String area = "";
									if (areaJson2 != null && areaJson2.containsKey("country")) {
										String country2 = areaJson2.getString("country");
										if (country2 != null && !country2.trim().isEmpty()) {
											area += country2;
										}
										String province2 = areaJson2.getString("prov");
										if (province2 != null && !province2.trim().isEmpty()) {
											area += province2;
										}
										String city2 = areaJson2.getString("city");
										if (city2 != null && !city2.trim().isEmpty()) {
											area += city2;
										}
									}
									if (area.isEmpty()) {
										area = "未知地区";
									}
									Connection conn = null;
									PreparedStatement ps = null;
									ResultSet rs = null;
									
									try {
										conn = WebmailDBUtil.getConnection();
										//张三@@espeed@@美国@@espeed@@Skype
										String content = name + "@@espeed@@" + area + "@@espeed@@" + chatType;
										
										String sql = "insert into sys_user_remind(remind_userid,remind_type,remind_status,remind_time,remind_content,target_id,is_read) values(?,?,?,?,?,?,?)";
										ps = conn.prepareStatement(sql);
										ps.setObject(1, recordUserId);
										ps.setObject(2, 32);
										ps.setObject(3, 0);
										ps.setObject(4, DateFormat.getNowDate());
										ps.setObject(5, content);
										ps.setObject(6, recordId);
										ps.setObject(7, 0);
										ps.executeUpdate();
										
										System.out.println("消息提醒添加完成");
									} catch (Exception e) {
										e.printStackTrace();
									} finally {
										WebmailDBUtil.close(rs, ps, conn);
									}
								}
								
							} else if (recordType == 4 && recordEmail != null && !recordEmail.trim().isEmpty()) {
								System.out.println("开始添加营销邮页面超链点击消息提醒...");
								String title = null;
								
								Map<String, Object> templateInfo = YxyDBUtil
										.getTemplateInfo(recordTemplateId);
								
								if (!templateInfo.get("code").equals(200)) {
									System.err.println("获取模板信息失败："
											+ templateInfo.get("msg"));
									//return;
								} else {
									title = String.valueOf(templateInfo.get("subject"));
								}
								
								if (title == null || title.trim().isEmpty()) {
									title = "未知";
								}
								
								Connection conn = null;
								PreparedStatement ps = null;
								ResultSet rs = null;
								
								try {
									//一个封邮件里的一个超链接一天提醒一次
									// 《xx》邮箱点开了您邮件/模板《xx》里的超链接《xx》
									conn = WebmailDBUtil.getConnection();
									
									String sql = "select id from sys_user_remind where to_days(remind_time) = to_days(now()) and remind_content like '《"
											+ recordEmail.trim() + "》%点开了您模板《" + title.trim() + "》%超链接《" + recordLink.trim() + "》%' and remind_userid = " + recordUserId;
									
									ps = conn.prepareStatement(sql);
									rs = ps.executeQuery();
									
									int id = 0;
									if (rs.first()) {
										id = rs.getInt("id");
									}
									
									if (id == 0) {
										String content = "《"+recordEmail.trim()+"》邮箱点开了您模板《"+title.trim()+"》里的超链接《"+recordLink.trim()+"》，请前往追踪统计查看！";
										
										sql = "insert into sys_user_remind(remind_userid,remind_type,remind_status,remind_time,remind_content,target_id,is_read) values(?,?,?,?,?,?,?)";
										ps = conn.prepareStatement(sql);
										ps.setObject(1, recordUserId);
										ps.setObject(2, 21);
										ps.setObject(3, 0);
										ps.setObject(4, DateFormat.getNowDate());
										ps.setObject(5, content);
										ps.setObject(6, recordId);
										ps.setObject(7, 0);
										ps.executeUpdate();
										
										System.out.println("消息提醒添加完成");
									}
									
								} catch (Exception e) {
									e.printStackTrace();
								} finally {
									WebmailDBUtil.close(rs, ps, conn);
								}
							}

						}
					});
				}

				// try的最后提交事务
				conn.commit();
			} else {
				System.err.println("无法获取ip");
			}

		} catch (Exception e) {
			if (conn != null) {
				try {
					// 回滚事务
					conn.rollback();
				} catch (SQLException ex) {
					ex.printStackTrace();
				}
			}
			e.printStackTrace();

			LogUtil.saveInfoLog(request.getParameter("ywxType") + "/" + userId,
					e);
		} finally {
			DBUtil.close(rs, ps, conn);
		}
	}
	
	/** 记录用户打开SEO HTML邮件里的超链接、及其本身HTML页面和用户官网的行为 */
	public static void recordFromHtmlInfo2(JSONObject param, List<String> emails) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Integer userId = 0;
		
		try {
			String ip = param.getString("ip");
			
			if (ip != null && !ip.equalsIgnoreCase("unknown")
					&& !ip.equalsIgnoreCase("null")) {
				
				if (!param.containsKey("ywxMid")) {
					param.put("ywxMid", "");
				}
				if (!param.containsKey("ywxUid")) {
					param.put("ywxUid", "0");
				}
				if (!param.containsKey("ywxCid")) {
					param.put("ywxCid", "0");
				}
				if (!param.containsKey("poporimap")) {
					param.put("poporimap", "0");
				}
				if (!param.containsKey("ywxTitle")) {
					param.put("ywxTitle", "");
				}
				if (!param.containsKey("ywxTo")) {
					param.put("ywxTo", "");
				}
				if (!param.containsKey("ywxX")) {
					param.put("ywxX", "");
				}
				if (!param.containsKey("ywxH")) {
					param.put("ywxH", "");
				}
				if (!param.containsKey("ywxMail")) {
					param.put("ywxMail", "");
				}
				if (!param.containsKey("ywxTitle")) {
					param.put("ywxTitle", "");
				}
				
				Integer templateId = Integer.valueOf(param.getString("ywxMid") == null ? "0" : param.getString("ywxMid"));// 邮件模板id
				
				userId = Integer
						.valueOf(param.getString("ywxUid") == null ? "0"
								: param.getString("ywxUid"));
				Integer companyId = Integer.valueOf(param.getString("ywxCid") == null ? "0" : param.getString("ywxCid"));
				
				Boolean cookieEnabled = Boolean.valueOf(param.getString("ywxCookie"));
				
				Integer ywxX = Integer
						.valueOf(param.getString("ywxX") == null ? "0"
								: param.getString("ywxX"));
				Integer ywxH = Integer
						.valueOf(param.getString("ywxH") == null ? "0"
								: param.getString("ywxH"));
				
				String poporimap = param.getString("poporimap");//null/0是pop，1是imap
				poporimap = poporimap == null ? "0" : poporimap.replace("3D", "").trim();
				
				String jsessionId = param.getString("jsessionId");
				
				if (userId == null || companyId == null
						|| cookieEnabled == null || ywxX == null
						|| ywxH == null) {
					
					System.err.println("参数缺失");
					
					return;
				}
				
				String uid = null;
				if (param.containsKey("uid")) {
					uid = param.getString("uid");
				} 
				
				Integer ywxType = Integer.valueOf(param.getString("ywxType"));
				
				String title = param.getString("ywxTitle");
				
				if (ywxType == 1 || ywxType == 4) {
					
					if (title == null || title.trim().isEmpty()
							|| title.equals("null")) {
						Map<String, Object> templateInfo = YxyDBUtil
								.getTemplateInfo(templateId);
						
						if (!templateInfo.get("code").equals(200)) {
							System.err.println("获取模板信息失败："
									+ templateInfo.get("msg"));
//							return;
						} else {
							
							title = String.valueOf(templateInfo.get("subject"));
						}
						
					}
				}
				
				conn = DBUtil.getConnection();
				conn.setAutoCommit(false);// 开启事务
				String sql = null;
				long count = 0L;
				String toMail = param.getString("ywxMail");
				
				if (toMail != null) {
					toMail = URLDecoder.decode(toMail, "UTF-8");
				}
				
				Map<String, Object> linkInfo = null;
				
				// cookie里面没有UID则查该ip是否有浏览过，若有的话，则该用户的浏览器不支持cookie
				if (uid == null) {
					if (!cookieEnabled) {// 浏览器不支持cookie
						sql = "select identity,jsession_id from ts_customer_access where ip = ? order by create_time asc";
						ps = conn.prepareStatement(sql);
						ps.setString(1, ip);
						
						rs = ps.executeQuery();
						if (rs.first()) {// 有浏览过
							uid = rs.getString("identity");
							jsessionId = rs.getString("jsession_id");
							
							sql = "select email from ts_customer_email where identity = ?";
							ps = conn.prepareStatement(sql);
							ps.setString(1, uid);
							
							String ems = "";
							
							rs = ps.executeQuery();
							while (rs.next()) {
								ems += ",'" + rs.getString("email") + "'";
							}
							
							if (StringUtils.isNotEmpty(param.getString("ywxMail"))) {
								ems += ",'" + param.getString("ywxMail")
										+ "'";
							}
							
							if (ems.trim().length() > 0) {
								ems = ems.substring(1);
								linkInfo = WebmailDBUtil.getUserEmail(userId,
										companyId, ems);
								
								toMail = String.valueOf(linkInfo.get("email"));
								if (ywxType != 8 && linkInfo.get("userId") != null) {
									userId = Integer.valueOf(linkInfo.get(
											"userId").toString());
								}
								
							}
							
						}
					}
					
					// 没有浏览过
					if (uid == null) {
						uid = UUID.randomUUID().toString();
						
						sql = "insert into ts_customer(identity,type,create_time) values(?,?,?)";
						ps = conn.prepareStatement(sql);
						ps.setString(1, uid);
						ps.setInt(2, 1);// 客户类型，0：路人、1：已存在客户
						ps.setString(3, DateFormat.getNowDate());
						// 添加客户
						ps.executeUpdate();
						
						if (toMail != null && !toMail.equals("null")) {
							
							sql = "select count(*) from ts_customer_email where identity = ? and email = ?";
							ps = conn.prepareStatement(sql);
							ps.setString(1, uid);
							ps.setString(2, toMail);
							
							rs = ps.executeQuery();
							if (rs.first()) {
								count = rs.getLong(1);
							}
							
							if (count == 0L) {
								sql = "insert into ts_customer_email(identity,email,create_time) values(?,?,?)";
								ps = conn.prepareStatement(sql);
								ps.setString(1, uid);
								ps.setString(2, toMail);
								ps.setString(3, DateFormat.getNowDate());
								// 添加email
								ps.executeUpdate();
							}
						}
					}
					
				} else {
					String ems = "";
					
					if (emails.size() > 0) {// 浏览器里有邮箱
						for (String email : emails) {
							ems += ",'" + email + "'";
						}
						
						if (StringUtils.isNotEmpty(param.getString("ywxMail"))) {
							ems += ",'" + param.getString("ywxMail") + "'";
						}
						
						if (ems.trim().length() > 0) {
							ems = ems.substring(1);
							linkInfo = WebmailDBUtil.getUserEmail(userId,
									companyId, ems);
							
							toMail = String.valueOf(linkInfo.get("email"));
							if (ywxType != 8 && linkInfo.get("userId") != null) {
								userId = Integer.valueOf(linkInfo.get("userId")
										.toString());
							}
						}
						
						// 浏览器有客户邮箱且数据没有保存，则保存
						if (toMail != null && !toMail.equals("null")) {
							
							sql = "select count(*) from ts_customer_email where identity = ? and email = ?";
							ps = conn.prepareStatement(sql);
							ps.setString(1, uid);
							ps.setString(2, toMail);
							
							rs = ps.executeQuery();
							if (rs.first()) {
								count = rs.getLong(1);
							}
							
							if (count == 0L) {
								sql = "insert into ts_customer_email(identity,email,create_time) values(?,?,?)";
								ps = conn.prepareStatement(sql);
								ps.setString(1, uid);
								ps.setString(2, toMail);
								ps.setString(3, DateFormat.getNowDate());
								// 添加email
								ps.executeUpdate();
							}
						}
						
					} else {// 浏览器里没有邮箱
						sql = "select email from ts_customer_email where identity = ?";
						ps = conn.prepareStatement(sql);
						ps.setString(1, uid);
						
						rs = ps.executeQuery();
						while (rs.next()) {
							ems += ",'" + rs.getString("email") + "'";
						}
						
						if (StringUtils.isNotEmpty(param.getString("ywxMail"))) {
							ems += ",'" + param.getString("ywxMail") + "'";
						}
						
						if (ems.trim().length() > 0) {
							ems = ems.substring(1);
							linkInfo = WebmailDBUtil.getUserEmail(userId,
									companyId, ems);
							
							toMail = String.valueOf(linkInfo.get("email"));
							if (ywxType != 8 && linkInfo.get("userId") != null) {
								userId = Integer.valueOf(linkInfo.get("userId")
										.toString());
							}
						}
					}
					
				}
				
				if ((toMail == null || toMail.trim().isEmpty() || toMail
						.equals("null"))
						&& StringUtils.isNotEmpty(param.getString("ywxMail"))) {
					toMail = param.getString("ywxMail");
				}
				
				if (toMail != null && !toMail.equals("null")
						&& !emails.contains(toMail)) {
					emails.add(toMail);
				}
				
				// 数据组装
				String agentString = param.getString("agentString");
				String referer = param.getString("referer");
				String fromDomain = null;
				
				if (referer != null && !referer.trim().isEmpty()) {
					try {
						referer = URLDecoder.decode(referer, "UTF-8");
						
						fromDomain = new URL(referer).getHost();
					} catch (Exception e) {
						System.err.println("referer域名解析失败："
								+ e.getClass().getName()
								+ (e.getMessage() == null ? "" : ("->" + e
										.getMessage())));
					}
				}
				
				String language = param.getString("language");
				String country2 = param.getString("country");
				if (country2 != null
						&& !country2.trim().isEmpty()) {
					language += "-" + country2;
				}
				
				String accessUrl = param.getString("ywxTo");
				String accessDomain = null;
				if (accessUrl != null) {
					try {
						accessUrl = URLDecoder.decode(accessUrl, "UTF-8");
						
						accessDomain = new URL(accessUrl).getHost();
					} catch (Exception e) {
						System.err.println("ywxTo域名解析失败："
								+ e.getClass().getName()
								+ (e.getMessage() == null ? "" : ("->" + e
										.getMessage())));
					}
				}
				
				// 解析ip
				String url = controlCentreUrl + "/webservice/ip";
				Map<String, Object> params = new HashMap<String, Object>();
				params.put("ip", ip);
				
				String continent = null;
				String country = null;
				String province = null;
				String city = null;
				String address = null;
				String netProvider = null;
				
				JSONObject areaJson = null;
				
				Map<String, Object> result = HttpUtil.doGet(url, params);
				if (result.get("code").equals(200)) {
					Object data = result.get("data");
					
					areaJson = JSONObject.fromObject(data);
					
					if (areaJson.getInt("code") == 200) {
						areaJson = JSONObject.fromObject(areaJson.get("result"));
						
						netProvider = areaJson.getString("isp");
						continent = areaJson.getString("land");
						country = areaJson.getString("country");
						province = areaJson.getString("prov");
						city = areaJson.getString("city");
						address = areaJson.getString("dist");
					} else {
						System.err.println("ip解析失败：" + areaJson.get("result"));
					}
				} else {
					System.err.println("ip解析失败：" + result.get("msg"));
				}
				
				UserAgent userAgent = UserAgent
						.parseUserAgentString(agentString);
				
				if (toMail != null && toMail.equals("null")) {
					toMail = null;
				}
				
				if (toMail == null && emails.size() > 0) {
					toMail = emails.get(0);
				}
				
				if (userId == null || userId == 0) {
					// 没有用户id则获取设置id
					userId = WebmailDBUtil.getUserId(companyId, accessUrl);
					// 没有用户id则获取管理员id
					if (userId == 0) {
						userId = CentreDBUtil.getUserId(companyId);
					}
				}
				
				if (toMail != null && !toMail.trim().isEmpty()) {
					if (!emailPattern.matcher(toMail.trim().toLowerCase())
							.matches()) {
						// 邮箱格式错误就放弃添加
						
						System.err.println("邮箱格式错误就放弃添加：" + toMail);
						return;
					}
				}
				
				toMail = ywxType == 7 ? param.getString("ywxMail")
						: toMail;
				
				String dbuid = null;// 邮箱在数据库中的第一条记录下的cookie标识
				
				if (toMail != null && !toMail.trim().isEmpty()) {
					
					sql = "select identity from ts_customer_access where access_email = ? order by create_time ASC limit 1";
					ps = conn.prepareStatement(sql);
					ps.setString(1, toMail);
					
					rs = ps.executeQuery();
					
					if (rs.first()) {
						dbuid = rs.getString("identity");
					}
					
					if (dbuid != null && !dbuid.equals(uid)) {
						uid = dbuid;
					}
				}
				
				if (toMail != null && !toMail.trim().isEmpty()
						&& !toMail.equalsIgnoreCase("null")) {
					sql = "select count(*) from ts_customer_email where identity = ? and email = ?";
					ps = conn.prepareStatement(sql);
					ps.setString(1, uid);
					ps.setString(2, toMail);
					
					rs = ps.executeQuery();
					if (rs.first()) {
						count = rs.getLong(1);
					}
					
					if (count == 0L) {
						sql = "insert into ts_customer_email(identity,email,create_time) values(?,?,?)";
						ps = conn.prepareStatement(sql);
						ps.setString(1, uid);
						ps.setString(2, toMail);
						ps.setString(3, DateFormat.getNowDate());
						// 添加email
						ps.executeUpdate();
					}
					
				}
				
				final int customerId = WebmailDBUtil.getCustomerId(userId, toMail);
				
				// 添加浏览记录
				sql = "insert into ts_customer_access(identity,user_id,company_id,mail_uid,title,from_url,from_domain,access_url,access_domain,ip,jsession_id,continent,country,province,city,address,device_type,browser_name,language,screen_x,screen_y,net_provider,os,search_word,user_agent,cookie_enable,access_time,access_email,res_type,create_time,template_id,customer_id,from_type,count_id,status,is_imap) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
				ps = conn.prepareStatement(sql);
				ps.setString(1, uid);// cookie唯一标示
				ps.setInt(2, userId);// 用户id
				ps.setInt(3, companyId);// 企业id
				ps.setString(4, null);// 邮件唯一标示
				ps.setString(5, title);// 来源标题
				ps.setString(6, referer);// 来源url
				ps.setString(7, fromDomain);// 来源域名
				ps.setString(8, accessUrl);// 受访问url
				ps.setString(9, accessDomain);// 受访问域名
				ps.setString(10, ip);// 客户端ip
				ps.setString(11, jsessionId);// 回话id
				ps.setString(12, continent);// 访问者所属洲
				ps.setString(13, country);// 访问者所属国际
				ps.setString(14, province);// //访问者所属省份
				ps.setString(15, city);// //访问者所属城市
				ps.setString(16, address);// //访问者所属详细地址
				ps.setInt(17, TrackUtil.getDeviceType(userAgent));// 设备类型
				ps.setString(18, userAgent.getBrowser().getName());// 浏览器名称
				ps.setString(19, language);// 浏览器设置的语言
				ps.setInt(20, ywxX);// 设备宽度
				ps.setInt(21, ywxH);// 设备高度
				ps.setString(22, netProvider);// 网络接入商
				ps.setString(23, userAgent.getOperatingSystem().getName());// 操作系统名称
				ps.setString(24, TrackUtil.getSearchWord(referer));// 搜索词
				ps.setString(25, agentString);// 浏览器ua
				ps.setInt(26, cookieEnabled ? 1 : 0);// 是否支持cookie,1支持、0不支持
				ps.setInt(27, 0);// 访问时长，单位毫秒
				ps.setString(28, toMail);// 客户邮箱
				ps.setInt(29, ywxType);// 受访问资源类型，0营销邮邮件、1营销邮邮件HTML页面、2易外销用户站点、3营销邮邮件里需要跟踪的超链、4营销邮邮件HTML页面里需要跟踪的超链、5易外销邮件、6易外销邮件里需要跟踪的超链、7询盘
				ps.setString(30, DateFormat.getNowDate());// 创建时间
				ps.setInt(31, templateId);// 模板id
				ps.setInt(32, customerId);// 客户id
				ps.setInt(33, TrackUtil.getFromType(referer));// 来源类别，-1无来源url、0：外链、1社交、2搜索、3邮件
				ps.setLong(34, 0L);// 统计id
				ps.setInt(35, 1);// 状态，0删除、1正常
				ps.setInt(36, Integer.valueOf(poporimap));// 是否是imap邮件，0否、1是
				// 提交添加
				ps.executeUpdate();
				// 获取自动生成的id
				rs = ps.getGeneratedKeys();
				long id = 0L;
				if (rs.first()) {
					id = rs.getLong(1);
				}
				
				sql = "select id,create_time from ts_customer_access where id != ? and identity = ? and user_id = ? and company_id = ? and to_days(create_time) = to_days(now()) order by create_time desc";
				ps = conn.prepareStatement(sql);
				ps.setLong(1, id);
				ps.setString(2, uid);
				ps.setInt(3, userId);
				ps.setInt(4, companyId);
				// 有上一条记录则修改上一条记录的访问时长
				rs = ps.executeQuery();
				if (rs.first()) {
					long milliSeconds = DateFormat.milliSeconds(
							rs.getString("create_time"),
							DateFormat.getNowDate());
					
					if (milliSeconds < (1000 * 60 * 60 * 2)) {// 当天前后两个请求大于2个小时的丢掉
						sql = "update ts_customer_access set access_time = ? where id = ?";
						ps = conn.prepareStatement(sql);
						ps.setLong(1, milliSeconds);
						ps.setLong(2, rs.getLong("id"));
						// 修改上一个请求的访问时长
						ps.executeUpdate();
					}
					
				}
				
				final JSONObject areaJson2 = areaJson;
				final int recordType = ywxType;
				final long recordId = id;
				final String recordEmail = toMail;
				final String recordLink = (accessUrl == null ? null : URLDecoder.decode(accessUrl, "UTF-8"));
				final int recordUserId = userId;
				final int recordTemplateId = templateId;
				System.out.println("ok...");
				// 点击超链接时异步添加消息提醒：4营销邮邮件HTML页面里需要跟踪的超链
				if ((recordLink != null && !recordLink.trim().isEmpty())
						&& (recordType == 2 || recordType == 4)) {
					
					fixedThreadPool.execute(new Runnable() {
						public void run() {
							System.out.println("开始添加消息提醒，recordType：" + recordType);
							if (recordType == 2) {
								String chatType = "Unknown";
								
								if (recordLink.startsWith("skype:live:")) {
									chatType = "Skype";
								} else if (recordLink.startsWith("https://api.whatsapp.com/send")) {
									chatType = "WhatAPP";
								} else if (recordLink.startsWith("http://wpa.qq.com/msgrd")) {
									chatType = "QQ";
								} else if (recordLink.startsWith("http://amos.alicdn.com/msg.aw")) {
									chatType = "阿里旺旺";
								}
								
								if (!chatType.equals("Unknown")) {
									System.out.println("开始添加页面在线咨询超链点击消息提醒...");
									String name = null;
									if (customerId > 0) {
										name = WebmailDBUtil.getCustomerName(customerId);
									}
									if (name == null || name.trim().isEmpty()) {
										name = "新访客";
									}
									
									String area = "";
									if (areaJson2 != null && areaJson2.containsKey("country")) {
										String country2 = areaJson2.getString("country");
										if (country2 != null && !country2.trim().isEmpty()) {
											area += country2;
										}
										String province2 = areaJson2.getString("prov");
										if (province2 != null && !province2.trim().isEmpty()) {
											area += province2;
										}
										String city2 = areaJson2.getString("city");
										if (city2 != null && !city2.trim().isEmpty()) {
											area += city2;
										}
									}
									if (area.isEmpty()) {
										area = "未知地区";
									}
									Connection conn = null;
									PreparedStatement ps = null;
									ResultSet rs = null;
									
									try {
										conn = WebmailDBUtil.getConnection();
										//张三@@espeed@@美国@@espeed@@Skype
										String content = name + "@@espeed@@" + area + "@@espeed@@" + chatType;
										
										String sql = "insert into sys_user_remind(remind_userid,remind_type,remind_status,remind_time,remind_content,target_id,is_read) values(?,?,?,?,?,?,?)";
										ps = conn.prepareStatement(sql);
										ps.setObject(1, recordUserId);
										ps.setObject(2, 32);
										ps.setObject(3, 0);
										ps.setObject(4, DateFormat.getNowDate());
										ps.setObject(5, content);
										ps.setObject(6, recordId);
										ps.setObject(7, 0);
										ps.executeUpdate();
										
										System.out.println("消息提醒添加完成");
									} catch (Exception e) {
										e.printStackTrace();
									} finally {
										WebmailDBUtil.close(rs, ps, conn);
									}
								}
								
							} else if (recordType == 4 && recordEmail != null && !recordEmail.trim().isEmpty()) {
								System.out.println("开始添加营销邮页面超链点击消息提醒...");
								String title = null;
								
								Map<String, Object> templateInfo = YxyDBUtil
										.getTemplateInfo(recordTemplateId);
								
								if (!templateInfo.get("code").equals(200)) {
									System.err.println("获取模板信息失败："
											+ templateInfo.get("msg"));
									//return;
								} else {
									title = String.valueOf(templateInfo.get("subject"));
								}
								
								if (title == null || title.trim().isEmpty()) {
									title = "未知";
								}
								
								Connection conn = null;
								PreparedStatement ps = null;
								ResultSet rs = null;
								
								try {
									//一个封邮件里的一个超链接一天提醒一次
									// 《xx》邮箱点开了您邮件/模板《xx》里的超链接《xx》
									conn = WebmailDBUtil.getConnection();
									
									String sql = "select id from sys_user_remind where to_days(remind_time) = to_days(now()) and remind_content like '《"
											+ recordEmail.trim() + "》%点开了您模板《" + title.trim() + "》%超链接《" + recordLink.trim() + "》%' and remind_userid = " + recordUserId;
									
									ps = conn.prepareStatement(sql);
									rs = ps.executeQuery();
									
									int id = 0;
									if (rs.first()) {
										id = rs.getInt("id");
									}
									
									if (id == 0) {
										String content = "《"+recordEmail.trim()+"》邮箱点开了您模板《"+title.trim()+"》里的超链接《"+recordLink.trim()+"》，请前往追踪统计查看！";
										
										sql = "insert into sys_user_remind(remind_userid,remind_type,remind_status,remind_time,remind_content,target_id,is_read) values(?,?,?,?,?,?,?)";
										ps = conn.prepareStatement(sql);
										ps.setObject(1, recordUserId);
										ps.setObject(2, 21);
										ps.setObject(3, 0);
										ps.setObject(4, DateFormat.getNowDate());
										ps.setObject(5, content);
										ps.setObject(6, recordId);
										ps.setObject(7, 0);
										ps.executeUpdate();
										
										System.out.println("消息提醒添加完成");
									}
									
								} catch (Exception e) {
									e.printStackTrace();
								} finally {
									WebmailDBUtil.close(rs, ps, conn);
								}
							}
							
						}
					});
				}
				
				// try的最后提交事务
				conn.commit();
			} else {
				System.err.println("无法获取ip");
			}
			
		} catch (Exception e) {
			if (conn != null) {
				try {
					// 回滚事务
					conn.rollback();
				} catch (SQLException ex) {
					ex.printStackTrace();
				}
			}
			e.printStackTrace();
			
			LogUtil.saveInfoLog(param.getString("ywxType") + "/" + userId,
					e);
		} finally {
			DBUtil.close(rs, ps, conn);
		}
	}

	/** 重新组装cookie */
	public static Map<String, String> getCookieMap(Cookie[] cookies) {
		Map<String, String> resultMap = new HashMap<String, String>();

		for (Cookie cookie : cookies) {
			resultMap.put(cookie.getName(), cookie.getValue());
		}

		return resultMap;
	}

	/** 获取浏览器设备类型 */
	public static int getDeviceType(UserAgent userAgent) {
		int type = 0;

		if (userAgent != null) {
			String name = userAgent.getOperatingSystem().getDeviceType()
					.getName();
			// 访问者设备类型，0：pc、1：手机、2：平板、3：其他
			if (name.equals("Computer")) {
				type = 0;
			} else if (name.equals("Mobile")) {
				type = 1;
			} else if (name.equals("Tablet")) {
				type = 2;
			} else {
				type = 3;
			}
		}

		return type;
	}

	/** 获取来源url中带有的搜索词 */
	public static String getSearchWord(String referer) {
		String searchWord = null;
		if (referer != null && !referer.trim().isEmpty()) {
			referer = referer.toLowerCase();

			if (referer.contains("baidu.com")
					&& (referer.contains("wd=") || referer.contains("word="))) {// 百度

				if (referer.contains("wd=")) {
					referer = referer.split("wd=")[1];
				} else if (referer.contains("word=")) {
					referer = referer.split("word=")[1];
				}

				if (referer.contains("&")) {
					searchWord = referer.split("&")[0];
				} else {
					searchWord = referer;
				}
			} else if (referer.contains("so.com") && referer.contains("q=")) {// 360

				referer = referer.split("q=")[1];

				if (referer.contains("&")) {
					searchWord = referer.split("&")[0];
				} else {
					searchWord = referer;
				}
			} else if (referer.contains("sogou.com")
					&& (referer.contains("query=") || referer
							.contains("keyword="))) {// 搜狗

				if (referer.contains("query=")) {
					referer = referer.split("query=")[1];
				} else if (referer.contains("keyword=")) {
					referer = referer.split("keyword=")[1];
				}

				if (referer.contains("&")) {
					searchWord = referer.split("&")[0];
				} else {
					searchWord = referer;
				}
			} else if (referer.contains("google.com") && referer.contains("q=")) {// 谷歌
				referer = referer.split("q=")[1];

				if (referer.contains("&")) {
					searchWord = referer.split("&")[0];
				} else {
					searchWord = referer;
				}
			} else if (referer.contains("bing.com") && referer.contains("q=")) {// 必应
				referer = referer.split("q=")[1];

				if (referer.contains("&")) {
					searchWord = referer.split("&")[0];
				} else {
					searchWord = referer;
				}
			} else if (referer.contains("yahoo.com") && referer.contains("p=")) {// 雅虎
				referer = referer.split("p=")[1];

				if (referer.contains("&")) {
					searchWord = referer.split("&")[0];
				} else {
					searchWord = referer;
				}
			}
		}

		if (searchWord != null) {
			try {
				searchWord = URLDecoder.decode(searchWord, "UTF-8");
			} catch (UnsupportedEncodingException e) {

			}
		}

		return searchWord;
	}

	/** 统计邮件里的超链 */
	public static void countLink(Integer userId, Integer companyId,
			String mailUid, String url, String email, Long aid) {

		if (url == null || url.trim().isEmpty()) {
			return;
		}

		url = url.trim();

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			conn = DBUtil.getConnection();
			conn.setAutoCommit(false);// 开启事务
			long id = 0L;

			String sql = "select id from ts_count_link where user_id = ? and company_id = ? and mail_uid = ? and url = ? and email = ?";

			ps = conn.prepareStatement(sql);
			ps.setInt(1, userId);
			ps.setInt(2, companyId);
			ps.setString(3, mailUid);
			ps.setString(4, url);
			ps.setString(5, email);

			rs = ps.executeQuery();

			if (rs.first()) {
				id = rs.getLong("id");
			}

			if (id > 0L) {
				sql = "update ts_count_link set count = count + 1 where id = ?";
				ps = conn.prepareStatement(sql);
				ps.setLong(1, id);

				ps.executeUpdate();
			} else {
				sql = "insert into ts_count_link(user_id,company_id,mail_uid,url,email,count) values(?,?,?,?,?,?)";
				ps = conn.prepareStatement(sql);
				ps.setInt(1, userId);
				ps.setInt(2, companyId);
				ps.setString(3, mailUid);
				ps.setString(4, url);
				ps.setString(5, email);
				ps.setInt(6, 1);

				ps.executeUpdate();

				// 获取自动生成的id
				rs = ps.getGeneratedKeys();
				if (rs.first()) {
					id = rs.getLong(1);
				}
			}

			sql = "update ts_customer_access set count_id = ? where id = ?";
			ps = conn.prepareStatement(sql);
			ps.setLong(1, id);
			ps.setLong(2, aid);

			ps.executeUpdate();

			// try的最后提交事务
			conn.commit();
		} catch (Exception e) {
			if (conn != null) {
				try {
					// 回滚事务
					conn.rollback();
				} catch (SQLException ex) {
					ex.printStackTrace();
				}
			}
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ps, conn);
		}
	}

	/** 获取来源类别，-1无来源url、0：外链、1社交、2搜索、3邮件 */
	public static int getFromType(String fromUrl) {
		int type = -1;

		if (fromUrl != null) {
			String domain = null;

			try {
				domain = new URL(fromUrl).getHost();
			} catch (Exception e) {

			}
			if (domain != null) {
				if (!ipPattern.matcher(domain).matches()) {

					FromRequest from = FromRequest.getFrom(domain);

					if (from != null) {
						type = from.getType();
					} else {
						type = 0;
					}

				} else {
					type = 0;
				}

			}

		}

		return type;
	}

	public static Map<String, Object> changeStatus(String ids, Integer status) {

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Map<String, Object> resultMap = new HashMap<String, Object>();

		if (ids == null || ids.trim().isEmpty()) {
			resultMap.put("code", 400);
			resultMap.put("success", false);
			resultMap.put("msg", "id不能为空");

			return resultMap;
		}

		if (status == null) {
			resultMap.put("code", 400);
			resultMap.put("success", false);
			resultMap.put("msg", "状态值不能为空");

			return resultMap;
		}

		try {
			conn = DBUtil.getConnection();
			conn.setAutoCommit(false);// 开启事务

			String sql = "update ts_customer_access set status = ? where id in("
					+ StringEscapeUtils.escapeSql(ids) + ")";
			ps = conn.prepareStatement(sql);

			if (status == 1) {
				ps.setInt(1, 1);
			} else if (status == 0) {
				ps.setInt(1, 0);
			}

			ps.executeUpdate();

			// try的最后提交事务
			conn.commit();

			resultMap.put("code", 200);
			resultMap.put("success", true);
			resultMap.put("msg", ConstantUtil.SUCCESS);

		} catch (Exception e) {
			if (conn != null) {
				try {
					// 回滚事务
					conn.rollback();
				} catch (SQLException ex) {
					ex.printStackTrace();
				}
			}

			resultMap.put("success", false);
			resultMap.put("code", 500);
			resultMap.put(
					"msg",
					"异常："
							+ e.getClass().getName()
							+ (e.getMessage() == null ? "" : ("->" + e
									.getMessage())));
		} finally {
			DBUtil.close(rs, ps, conn);
		}

		return resultMap;

	}

	public static Map<String, Object> updateCustomerIds(Long id,
			Integer customerId) {

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Map<String, Object> resultMap = new HashMap<String, Object>();

		if (id == null) {
			resultMap.put("code", 400);
			resultMap.put("success", false);
			resultMap.put("msg", "id不能为空");

			return resultMap;
		}

		if (customerId == null) {
			resultMap.put("code", 400);
			resultMap.put("success", false);
			resultMap.put("msg", "客户id不能为空");

			return resultMap;
		}

		try {
			conn = DBUtil.getConnection();
			conn.setAutoCommit(false);// 开启事务

			String sql = "select user_id,access_email from ts_customer_access where id = ?";
			ps = conn.prepareStatement(sql);
			ps.setLong(1, id);

			rs = ps.executeQuery();

			if (!rs.first()) {
				resultMap.put("code", 400);
				resultMap.put("success", false);
				resultMap.put("msg", "不存在该id");

				return resultMap;
			}

			if (rs.getString("access_email") != null
					&& !rs.getString("access_email").trim().isEmpty()) {
				sql = "update ts_customer_access set customer_id = ? where user_id = ? and access_email = ?";
				ps = conn.prepareStatement(sql);

				ps.setInt(1, customerId);
				ps.setInt(2, rs.getInt("user_id"));
				ps.setString(3, rs.getString("access_email"));

				ps.executeUpdate();
			}

			// try的最后提交事务
			conn.commit();

			resultMap.put("code", 200);
			resultMap.put("success", true);
			resultMap.put("msg", ConstantUtil.SUCCESS);

		} catch (Exception e) {
			if (conn != null) {
				try {
					// 回滚事务
					conn.rollback();
				} catch (SQLException ex) {
					ex.printStackTrace();
				}
			}

			resultMap.put("success", false);
			resultMap.put("code", 500);
			resultMap.put(
					"msg",
					"异常："
							+ e.getClass().getName()
							+ (e.getMessage() == null ? "" : ("->" + e
									.getMessage())));
		} finally {
			DBUtil.close(rs, ps, conn);
		}

		return resultMap;

	}

	public static List<Map<String, Object>> getRecordInfos(Long id) {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		if (id == null) {

			return list;
		}

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ResultSetMetaData metaData = null;
		int count = 0;

		try {
			conn = DBUtil.getConnection();

			String sql = "select id,user_id,company_id,mail_uid,access_url,access_email,ip,country,province,city,user_agent,create_time from ts_customer_access where res_type =3 and id > ? limit ?";
			ps = conn.prepareStatement(sql);
			ps.setLong(1, id);
			ps.setInt(2, 200);

			rs = ps.executeQuery();
			String name = null;
			Map<String, Object> recordMap = null;
			while (rs.next()) {
				metaData = rs.getMetaData();
				count = metaData.getColumnCount();
				if (count > 0) {
					recordMap = new HashMap<String, Object>();
					for (int i = 1; i <= count; i++) {
						name = metaData.getColumnName(i);
						recordMap.put(name, rs.getObject(name));
					}
					list.add(recordMap);
				}
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ps, conn);
		}

		return list;

	}

	public static Map<String, Object> getUserId(String token) {
		Map<String, Object> resultMap = new HashMap<String, Object>();

		resultMap.put("code", 300);
		resultMap.put("success", false);
		resultMap.put("msg", "token缺失或者格式错误或已过期");

		if (token != null && !token.trim().isEmpty()) {

			try {
				// 解析token
				Claims claims = Jwts.parser().setSigningKey(secretkey)
						.parseClaimsJws(token).getBody();

				resultMap.put("code", 200);
				resultMap.put("success", true);
				resultMap.put("msg", ConstantUtil.SUCCESS);
				resultMap.put("data", claims.get("user_id", Integer.class));
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

		return resultMap;
	}
}