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.util.HashMap;
import java.util.Map;

/**
 * 
 * 
 * @项目名称: 邮件跟踪系统
 * @版权所有: 深圳市科飞时速网络技术有限公司(0755-88843776)
 * @技术支持: info@21gmail.com
 * @单元名称: 操作营销邮数据库的工具类
 * @开始时间: 2018-5-23
 * @开发人员: 杨志钊
 */
public class YxyDBUtil {
	public static String DBUrl = ConfigPath.getYxyUrl();
	public static String DBUser = ConfigPath.getYxyUsername();
	public static String DBPassword = ConfigPath.getYxyPassword();
	public static final String DBDriver = "com.mysql.jdbc.Driver";
	public static Connection conn;
	// 加载驱动
	static {
		try {
			Class.forName(DBDriver);

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

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

	/** 关闭数据库连接 */
	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> getMailInfo(String mailUid) {
		Map<String, Object> mailInfo = new HashMap<String, Object>();

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

		try {
			conn = YxyDBUtil.getConnection();

			String sql = "select subject from yxy_send_mail_master 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 {
			YxyDBUtil.close(rs, ps, conn);
		}

		return mailInfo;
	}
	
	public static Map<String, Object> getTemplateInfo(Integer templateId) {
		Map<String, Object> mailInfo = new HashMap<String, Object>();
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = YxyDBUtil.getConnection();
			
			String sql = "select stencil_name from yxy_mail_stencil where stencil_id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, templateId);
			
			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->" + templateId);
			}
			
		} catch (SQLException e) {
			mailInfo.put("code", 500);
			mailInfo.put(
					"msg",
					"异常："
							+ e.getClass().getName()
							+ (e.getMessage() == null ? "" : ("->" + e
									.getMessage())));
		} finally {
			YxyDBUtil.close(rs, ps, conn);
		}
		
		return mailInfo;
	}

}