package com.ekexiu.portal.dao;

import com.ekexiu.portal.po.Ppatent;
import com.ekexiu.portal.pojo.AssedPatent;
import org.jfw.apt.annotation.Nullable;
import org.jfw.apt.orm.annotation.dao.DAO;
import org.jfw.apt.orm.annotation.dao.method.Exclude;
import org.jfw.apt.orm.annotation.dao.method.From;
import org.jfw.apt.orm.annotation.dao.method.Or;
import org.jfw.apt.orm.annotation.dao.method.OrderBy;
import org.jfw.apt.orm.annotation.dao.method.SetSentence;
import org.jfw.apt.orm.annotation.dao.method.operator.PageQuery;
import org.jfw.apt.orm.annotation.dao.method.operator.PageSelect;
import org.jfw.apt.orm.annotation.dao.method.operator.SelectList;
import org.jfw.apt.orm.annotation.dao.method.operator.SelectOne;
import org.jfw.apt.orm.annotation.dao.method.operator.UpdateWith;
import org.jfw.apt.orm.annotation.dao.param.Alias;
import org.jfw.apt.orm.annotation.dao.param.In;
import org.jfw.apt.orm.annotation.dao.param.Like;
import org.jfw.apt.orm.annotation.dao.param.Set;
import org.jfw.apt.orm.annotation.dao.param.SqlColumn;
import org.jfw.apt.orm.core.defaultImpl.FixLenStringHandler;
import org.jfw.util.PageQueryResult;

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

@DAO
public abstract class PpatentDao {
	@SelectOne
	@Nullable
	public abstract Ppatent query(Connection con, String id) throws SQLException;

	@SelectList
	public abstract List<Ppatent> query(Connection con, @In String[] id) throws SQLException;

	@UpdateWith
	@From(Ppatent.class)
	public abstract int update(Connection con, String id, @Set String keywords) throws SQLException;
	@UpdateWith
	@From(Ppatent.class)
	public abstract int update(Connection con, String id, @Set String keywords,@Set String cooperation) throws SQLException;

	@Exclude("summary")
	@PageQuery
	@OrderBy(" ORDER BY ASS_TIME DESC,ID ASC")
	public abstract PageQueryResult<AssedPatent> pageQueryWithProfessor(Connection con,
			@SqlColumn(handlerClass = FixLenStringHandler.class, value = { "A.PROFESSOR_ID=?" }) String professId,
			@SqlColumn(handlerClass = FixLenStringHandler.class, value = { "P.NAME LIKE ?" }) String name, int pageSize, int pageNo) throws SQLException;

	@Exclude("summary")
	@PageQuery
	@OrderBy(" ORDER BY ASS_TIME DESC,ID ASC")
	public abstract PageQueryResult<AssedPatent> pageQueryWithAuthor(Connection con,
			@SqlColumn(handlerClass = FixLenStringHandler.class, value = { "A.NAME=?" }) String author,
			@SqlColumn(handlerClass = FixLenStringHandler.class, value = {
					"((A.PROFESSOR_ID=?)  OR  (A.PROFESSOR_ID='################################'))" }) String id,
			@SqlColumn(handlerClass = FixLenStringHandler.class, value = { "P.NAME LIKE ?" }) String name, int pageSize, int pageNo) throws SQLException;

	@From(Ppatent.class)
	@UpdateWith
	@SetSentence("PAGE_VIEWS = PAGE_VIEWS + 1")
	public abstract int incPageViews(Connection con, String id) throws SQLException;

	@Exclude("summary")
	@PageSelect
	@OrderBy(" ORDER BY SORT_NUM DESC,CREATE_TIME DESC,ID DESC")
	public abstract PageQueryResult<Ppatent> query(Connection con, int pageSize, int pageNo) throws SQLException;

	@Exclude("summary")
	@PageSelect
	@OrderBy(" ORDER BY SORT_NUM DESC,CREATE_TIME DESC,ID DESC")
	@Or
	public abstract PageQueryResult<Ppatent> query(Connection con, @Alias({ "code", "authors", "name", "keywords","reqPerson" }) @Like String cnt, int pageSize, int pageNo)
			throws SQLException;


	public List<Ppatent> indexSearch(Connection con, String key, long sortNum, String createTime, String id, int rows) throws SQLException {
		List<Ppatent> result = new ArrayList<>();
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT AUTHORS,SORT_NUM,ID,NAME,PAGE_VIEWS,CODE,REQ_CODE,REQ_PERSON,REQ_DAY,PUB_DAY,REF_PARAM,KEYWORDS,SHARE_ID,CREATE_TIME FROM PPATENT");
		sql.append(" WHERE (SORT_NUM < ? OR (SORT_NUM = ? AND CREATE_TIME < ?) OR (SORT_NUM=? AND CREATE_TIME = ? AND ID < ?)) ");
		if (key != null) {
			sql.append(" AND (REQ_PERSON LIKE ? OR KEYWORDS LIKE ? OR NAME LIKE ? OR AUTHORS LIKE ? OR CODE LIKE ?) ");
		}
		sql.append("ORDER BY SORT_NUM DESC,CREATE_TIME DESC,ID DESC LIMIT ?");
		PreparedStatement ps = con.prepareStatement(sql.toString());
		try {
			int index = 1;
			ps.setLong(index++, sortNum);
			ps.setLong(index++, sortNum);
			ps.setString(index++, createTime);
			ps.setLong(index++, sortNum);
			ps.setString(index++, createTime);
			ps.setString(index++, id);
			if (key != null) {
				ps.setString(index++, key);
				ps.setString(index++, key);
				ps.setString(index++, key);
				ps.setString(index++, key);
				ps.setString(index++, key);
			}
			ps.setInt(index++, rows);
			ResultSet rs = ps.executeQuery();
			try {
				while (rs.next()) {
					Ppatent ppatent = new Ppatent();
					ppatent.setAuthors(rs.getString(1));
					ppatent.setSortNum(rs.getLong(2));
					ppatent.setId(rs.getString(3));
					ppatent.setName(rs.getString(4));
					ppatent.setPageViews(rs.getLong(5));
					String code = rs.getString(6);
					if (rs.wasNull()) {
						code = null;
					}
					ppatent.setCode(code);
					String reqCode = rs.getString(7);
					if (rs.wasNull()) {
						reqCode = null;
					}
					ppatent.setReqCode(reqCode);
					String reqPerson = rs.getString(8);
					if (rs.wasNull()) {
						reqPerson = null;
					}
					ppatent.setReqPerson(reqPerson);
					String reqDay = rs.getString(9);
					if (rs.wasNull()) {
						reqDay = null;
					}
					ppatent.setReqDay(reqDay);
					String pubDay = rs.getString(10);
					if (rs.wasNull()) {
						pubDay = null;
					}
					ppatent.setPubDay(pubDay);
					String refParam = rs.getString(11);
					if (rs.wasNull()) {
						refParam = null;
					}
					ppatent.setRef_param(refParam);
					String keyWords = rs.getString(12);
					if (rs.wasNull()) {
						keyWords = null;
					}
					ppatent.setKeywords(keyWords);
					ppatent.setShareId(rs.getLong(13));
					ppatent.setCreateTime(rs.getString(14));
					result.add(ppatent);
				}
				return result;
			} finally {
				try {
					rs.close();
				} catch (Exception _m_18) {
				}
			}
		} finally {
			try {
				ps.close();
			} catch (Exception _m_19) {
			}
		}
	}


	@SelectOne
	@Nullable
	public abstract Ppatent query(Connection con, long shareId) throws SQLException;

	public String[] queryPatentIdWithSameKeyWord(Connection con, String id, int rows) throws SQLException {
		List<String> ret = new ArrayList<String>();
		String sql = "select id,count(1) sort from pat_key_word where kw in (select kw from pat_key_word where id=?) and id <>? group by id order by sort desc limit ?";
		PreparedStatement ps = con.prepareStatement(sql);
		try {
			ps.setString(1, id);
			ps.setString(2, id);
			ps.setInt(3, rows);
			ResultSet rs = ps.executeQuery();
			try {
				while (rs.next()) {
					ret.add(rs.getString(1));
				}
			} finally {
				try {
					rs.close();
				} catch (Exception e1) {
				}
			}
		} finally {
			try {
				ps.close();
			} catch (Exception e2) {
			}
		}
		return ret.isEmpty() ? null : ret.toArray(new String[ret.size()]);
	}

	public String[] queryPatentIdWithKeyWord(Connection con, String[] kws, int rows) throws SQLException {
		List<String> ret = new ArrayList<String>();
		StringBuilder sql = new StringBuilder();
		sql.append("select id,count(1) sort from pat_key_word where kw in (");
		for (int i = 0; i < kws.length; ++i) {
			sql.append("?,");
		}
		sql.delete(sql.length() - 1, sql.length());
		sql.append(") group by id order by sort desc limit ?");
		PreparedStatement ps = con.prepareStatement(sql.toString());
		try {
			int idx = 1;
			for (String kw : kws) {
				ps.setString(idx++, kw);
			}
			ps.setInt(idx++, rows);
			ResultSet rs = ps.executeQuery();
			try {
				while (rs.next()) {
					ret.add(rs.getString(1));
				}
			} finally {
				try {
					rs.close();
				} catch (Exception e1) {
				}
			}
		} finally {
			try {
				ps.close();
			} catch (Exception e2) {
			}
		}
		return ret.isEmpty() ? null : ret.toArray(new String[ret.size()]);
	}

}