package com.mole.struts.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import com.mole.struts.bean.MerchantCommendBean;

public class MerchantCommendDAO {
	private Connection conn;
	private int pageSize;

	public void MerchantCommendDAO() {
		try {
			Context ctx = new InitialContext();
			if (ctx == null)
				throw new Exception("Failed to initial context!");
			DataSource ds = (DataSource) ctx
					.lookup("java:comp/env/jdbc/crmdata");
			conn = ds.getConnection();
			conn.setAutoCommit(true);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public Connection getConn() {
		Connection conn = null;
		try {
			Context ctx = new InitialContext();
			if (ctx == null)
				throw new Exception("Failed to initial context!");
			DataSource ds = (DataSource) ctx
					.lookup("java:comp/env/jdbc/crmdata");
			conn = ds.getConnection();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}

	public void ExecuteQuery(String id, String state) {
		if (conn == null)
			conn = getConn();
		Statement ps = null;
		String sql = "update  card set state=" + state + " where id='" + id
				+ "'";
		try {
			ps = conn.createStatement();
			ps.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public void AddCommend(String id, String title, String content)// 添加交易評價
	{
		String img = getImage(content);
		String abs = getAbstract(content);
		if (conn == null)
			conn = getConn();
		Statement ps = null;
		String sql = "insert into commendlist(merchantid,commendcontent,commendtitle,commendImage,commendAbstract) values("
				+ id
				+ ",'"
				+ content
				+ "','"
				+ title
				+ "','"
				+ img
				+ "','"
				+ abs + "')";
		try {
			ps = conn.createStatement();
			ps.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public int getPageInfo(String mid) {

		int count = 0;
		if (conn == null)
			conn = getConn();
		String sql = "select count(*) from commendlist where  merchantid="
				+ mid;
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			if (rs.next())
				count = rs.getInt(1);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return count;

	}

	// 獲取推薦列表
	public ArrayList GetCommendList(String mid, int currentPage, int pagesize)
			throws Exception {
		if (conn == null)
			conn = getConn();
		Statement ps = null;
		ResultSet rs = null;
		ps = conn.createStatement();
		String sql = "select top "
				+ pagesize
				+ " a.commendid,a.merchantid,a.commendtitle,a.commendcontent,a.commendtime from commendlist a where merchantid="
				+ mid + " and commendid not in(" + "SELECT TOP "
				+ (currentPage - 1) * pagesize
				+ " [commendId] FROM commendlist  WHERE merchantid=" + mid
				+ " order by commendtime ) order by commendtime";
		ArrayList al = new ArrayList();
		rs = ps.executeQuery(sql);
		/*
		 * String sql = "SELECT TOP " + pageSize +
		 * " a.[title],a.[issuetime],(SELECT COUNT(*) FROM blogloglist where topicid=a.[ID]) as cont ,a.[ID] "
		 * +"FROM blogloglist a  WHERE a.topicid=0 AND writerid="+articleId+
		 * " AND a.[ID] NOT IN (" + "SELECT TOP " + (currentPage-1)pageSize +
		 * " [ID] FROM blogloglist  WHERE topicid=0 and writerid="
		 * +articleId+" order by issuetime desc) order by issuetime desc";
		 */
		while (rs.next()) {
			MerchantCommendBean temp = new MerchantCommendBean();
			temp.setCommendId(String.valueOf(rs.getInt(1)));
			temp.setMid(String.valueOf(rs.getInt(2)));
			temp.setTitle(rs.getString(3));
			temp.setContent(rs.getString(4));
			temp.setCommitTime(String.valueOf(rs.getDate(5)));
			al.add(temp);
		}
		return al;

	}

	public void deleteCommend(String id) {
		if (conn == null)
			conn = getConn();
		Statement ps = null;
		String sql = "delete from commendlist where commendid=" + id + "";
		try {
			ps = conn.createStatement();
			ps.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public void EditCommend(String id, String title, String content) {
		String img = getImage(content);
		String abs = getAbstract(content);
		if (conn == null)
			conn = getConn();
		Statement ps = null;
		String sql = "update commendlist set commendcontent='" + content
				+ "',commendtitle='" + title + "',commendImage='" + img
				+ "',commendAbstract='" + abs + "' where commendid=" + id;
		try {
			ps = conn.createStatement();
			ps.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public MerchantCommendBean getCommendDetail(String commendId)
			throws Exception {

		if (conn == null)
			conn = getConn();
		Statement ps = null;
		ResultSet rs = null;
		ps = conn.createStatement();
		String sql = "select a.commendid,a.merchantid,a.commendtitle,a.commendtime,a.commendcontent from commendlist a where commendId="
				+ commendId + " order by commendtitle";

		rs = ps.executeQuery(sql);
		MerchantCommendBean temp = new MerchantCommendBean();
		while (rs.next()) {

			temp.setCommendId(String.valueOf(rs.getInt(1)));
			temp.setMid(String.valueOf(rs.getInt(2)));
			temp.setTitle(rs.getString(3));
			temp.setCommitTime(String.valueOf(rs.getDate(4)));
			temp.setContent(rs.getString(5));
		}
		return temp;

	}

	private String getImage(String str) {
		String img = "image/icon_commend.gif";
		int start = str.indexOf("<img");
		if (start >= 0) {
			start = str.indexOf("src=\"", start) + 5;
			int end = str.indexOf("\" ", start);
			img = str.substring(str.indexOf("upload/", start), end);
		}
		return img;
	}

	private String getAbstract(String str) {
		String abs = "";
		int start = str.indexOf(">") + 1;
		int end = 0;
		if (str.indexOf(">") == -1) {
			abs = str;
		} else
			while (abs.length() < 30 && start < (str.length() - 2)) {
				end = str.indexOf("<", start);
				abs += str.substring(start, end);
				start = str.indexOf(">", end) + 1;
			}
		if (abs.length() > 30) {
			abs = abs.substring(0, 30);
			abs += "...";
		}
		return abs;
	}
}