package com.espeed.reading.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import net.sf.json.JSONObject;

/**
 * 
 * 
 * @项目名称: 邮件跟踪系统
 * @版权所有: 深圳市科飞时速网络技术有限公司(0755-88843776)
 * @技术支持: info@21gmail.com
 * @单元名称: 操作易外销WebMail数据库的工具类
 * @开始时间: 2018-5-23
 * @开发人员: 杨志钊
 */
public class WebmailDBUtil {
	public static String DBUrl = ConfigPath.getWebmailUrl();
	public static String DBUser = ConfigPath.getWebmailUsername();
	public static String DBPassword = ConfigPath.getWebmailPassword();
	public static final String DBDriver = "com.mysql.jdbc.Driver";
	public static Connection conn;
	
	private static SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

	// 加载驱动
	static {
		try {
			Class.forName(DBDriver);

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 获得连接
	public static Connection getConnection() throws SQLException {
		Connection c = DriverManager.getConnection(DBUrl, DBUser, DBPassword);
		c.setAutoCommit(false);
		return c;
	}

	/** 关闭数据库连接 */
	public static void close(ResultSet rs, Statement st, Connection conn) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (st != null) {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static Map<String, Object> getUserEmail(Integer userId,
			Integer companyId, String emails) {
		Map<String, Object> linkInfo = new HashMap<String, Object>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			conn = WebmailDBUtil.getConnection();

			String sql = null;
			if (userId > 0) {
				sql = "select email,create_user_id from crm_linkman where create_user_id = ? and email in("
						+ emails + ")";
				ps = conn.prepareStatement(sql);
				ps.setInt(1, userId);

			} else {
				sql = "select email,create_user_id from crm_linkman where company_id = ? and email in("
						+ emails + ")";
				ps = conn.prepareStatement(sql);
				ps.setInt(1, companyId);
			}

			rs = ps.executeQuery();

			if (rs.first()) {
				linkInfo.put("email", rs.getString("email"));
				linkInfo.put("userId", rs.getInt("create_user_id"));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			WebmailDBUtil.close(rs, ps, conn);
		}

		return linkInfo;
	}

	/** 获取客户id */
	public static int getCustomerId(Integer userId, String email) {
		int id = 0;

		if (email == null || email.trim().isEmpty() || email.equals("null")) {
			return id;
		}

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

		try {
			conn = WebmailDBUtil.getConnection();

			String sql = "select customer_id from crm_linkman where customer_id > 0 and create_user_id = ? and email = ?";

			ps = conn.prepareStatement(sql);
			ps.setInt(1, userId);
			ps.setString(2, email);

			rs = ps.executeQuery();

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

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

		return id;
	}
	
	/** 获取客户名称 */
	public static String getCustomerName(int id) {
		String name = null;
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = WebmailDBUtil.getConnection();
			
			String sql = "select customer_name from crm_customer where customer_id = ?";
			
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			
			rs = ps.executeQuery();
			
			if (rs.first()) {
				name = rs.getString("customer_name");
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			WebmailDBUtil.close(rs, ps, conn);
		}
		
		return name;
	}

	/** 获取客户id */
	public static String getCustomerIds(String userIds, String searchWord) {
		String result = "";

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

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

		try {
			conn = WebmailDBUtil.getConnection();

			String sql = "SELECT customer_id FROM crm_customer WHERE user_id in("
					+ userIds
					+ ") "
					+ "AND customer_name LIKE '%"
					+ searchWord
					+ "%'";

			ps = conn.prepareStatement(sql);

			rs = ps.executeQuery();

			while (rs.next()) {
				result += "," + rs.getInt("customer_id");
			}

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

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

		return result;
	}

	public static Map<Integer, JSONObject> getCustomers(String customerIds) {

		Map<Integer, JSONObject> resultMap = new HashMap<Integer, JSONObject>();

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

		try {
			conn = WebmailDBUtil.getConnection();

			String sql = "select customer_id,customer_name,user_id from crm_customer where customer_id in("
					+ customerIds + ")";

			ps = conn.prepareStatement(sql);

			rs = ps.executeQuery();

			JSONObject customer = null;

			while (rs.next()) {
				customer = new JSONObject();
				customer.put("user_id", rs.getInt("user_id"));
				customer.put("customer_name", rs.getString("customer_name"));

				resultMap.put(rs.getInt("customer_id"), customer);
			}

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

		return resultMap;
	}

	public static int getUserId(Integer companyId, String url) {
		int userId = 0;

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

		try {
			conn = WebmailDBUtil.getConnection();

			String sql = "select allow_user_id,allow_weburls,default_user_id from crm_enquiry_set where company_id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, companyId);

			rs = ps.executeQuery();
			while (rs.next()) {
				userId = rs.getInt("default_user_id");
				if (url != null
						&& !url.trim().isEmpty()
						&& url.trim().equalsIgnoreCase(
								rs.getString("allow_weburls"))) {
					userId = rs.getInt("allow_user_id");
					break;
				}
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			WebmailDBUtil.close(rs, ps, conn);
		}

		return userId;
	}

	public static Map<String, Object> getMailInfo(String mailUid) {
		Map<String, Object> mailInfo = new HashMap<String, Object>();

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

		try {
			conn = WebmailDBUtil.getConnection();

			String sql = "select subject from eml_send_mail_master_base where mail_uid = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, mailUid);

			rs = ps.executeQuery();

			if (rs.first()) {
				mailInfo.put("code", 200);
				mailInfo.put("subject", rs.getObject("subject"));
			} else {
				mailInfo.put("code", 400);
				mailInfo.put("msg", "不存在的邮件ID->" + mailUid);
			}

		} catch (SQLException e) {
			mailInfo.put("code", 500);
			mailInfo.put(
					"msg",
					"异常："
							+ e.getClass().getName()
							+ (e.getMessage() == null ? "" : ("->" + e
									.getMessage())));
		} finally {
			WebmailDBUtil.close(rs, ps, conn);
		}

		return mailInfo;
	}

	public static Map<String, Object> getUserInfo(String mailUid) {
		Map<String, Object> userInfo = new HashMap<String, Object>();

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

		try {
			conn = WebmailDBUtil.getConnection();

			String sql = "select user_id,company_id from eml_send_mail_master_base where mail_uid = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, mailUid);

			rs = ps.executeQuery();

			if (rs.first()) {
				userInfo.put("code", 200);
				userInfo.put("userId", rs.getObject("user_id"));
				userInfo.put("companyId", rs.getObject("company_id"));
			} else {
				userInfo.put("code", 400);
				userInfo.put("msg", "不存在的邮件uid->" + mailUid);
			}

		} catch (SQLException e) {
			userInfo.put("code", 500);
			userInfo.put(
					"msg",
					"异常："
							+ e.getClass().getName()
							+ (e.getMessage() == null ? "" : ("->" + e
									.getMessage())));
		} finally {
			WebmailDBUtil.close(rs, ps, conn);
		}

		return userInfo;
	}

	public static void updateMailUid(Integer type, Long id, String mailUid, String poporimap) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String uid = null;
		Long sid = 0L;
		try {
			conn = WebmailDBUtil.getConnection();
			String sql = null;
			switch (type) {
			case 0:// 追踪附件
				sql = "select mail_uid from cloud_file where id = ? limit 1";
				ps = conn.prepareStatement(sql);
				ps.setObject(1, id);

				rs = ps.executeQuery();

				if (rs.first()) {
					uid = rs.getString("mail_uid");
				}
				if (uid == null || uid.trim().isEmpty()
						|| uid.toLowerCase().startsWith("uid_")) {
					sql = "update cloud_file set mail_uid = ? where id = ?";

					ps = conn.prepareStatement(sql);
					ps.setObject(1, mailUid);
					ps.setObject(2, id);

					ps.executeUpdate();
				}
				break;
			case 1:// 云盘分享附件
				sql = "select mail_uid,share_ids from cloud_share where id = ? limit 1";
				ps = conn.prepareStatement(sql);
				ps.setObject(1, id);

				rs = ps.executeQuery();

				if (rs.first()) {
					uid = rs.getString("mail_uid");
					sid = rs.getLong("share_ids");
				}

				if (uid == null || uid.trim().isEmpty()
						|| uid.toLowerCase().startsWith("uid_")) {
					sql = "update cloud_share set mail_uid = ?, is_imap = ? where id = ?";

					ps = conn.prepareStatement(sql);
					ps.setObject(1, mailUid);
					ps.setObject(2, poporimap);
					ps.setObject(3, id);

					ps.executeUpdate();

					sql = "update cloud_file set mail_uid = ? where id = ?";

					ps = conn.prepareStatement(sql);
					ps.setObject(1, mailUid);
					ps.setObject(2, sid);

					ps.executeUpdate();
				}
				break;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			WebmailDBUtil.close(rs, ps, conn);
		}
	}

	
	
	/**自动化跟进的邮件点读记录*/
	public static void updateAutoTaskMailClick(String mail_uid, String receive_mail) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = WebmailDBUtil.getConnection();
			
			//系统发件表
			String sql = "update auto_task_syssendrecord2 set read_count = read_count + 1,read_time = ? " +
					"where mail_uid = ? and receive_mail = ? LIMIT 1";
			ps = (PreparedStatement)conn.prepareStatement(sql);
			ps.setString(1, df.format(new Date()));
			ps.setString(2, mail_uid);
			ps.setString(3, receive_mail);
			ps.executeUpdate();
			
			conn.commit();
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			WebmailDBUtil.close(rs, ps, conn);
		}
		
	}
	
}