package com.ekexiu.portal.dao;

import com.ekexiu.portal.po.Ppaper;
import com.ekexiu.portal.pojo.AssedPaper;
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.LimitColumn;
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.LimitQuery;
import org.jfw.apt.orm.annotation.dao.method.operator.LimitSelect;
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.GroupSqlColumn;
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.apt.orm.core.defaultImpl.LongHandler;
import org.jfw.apt.orm.core.defaultImpl.StringHandler;
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 PpaperDao {

	@SelectOne
	@Nullable
	public abstract	Ppaper query(Connection con, String id) throws SQLException;

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

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

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

	@Exclude("summary")
	@LimitQuery
	@OrderBy(cols = {@LimitColumn(value = "assTime", asc = false), @LimitColumn(value = "id", asc = false)}, value = "")
	public abstract List<AssedPaper> queryProfessor(Connection con, String professorId,@Nullable @SqlColumn(handlerClass = StringHandler.class,value = {"P.NAME LIKE ?"}) String name, String assTime, String id, int rows) throws SQLException;

	@Exclude("summary")
	@PageQuery
	@OrderBy(" ORDER BY ASS_TIME DESC,ID ASC")
	public abstract PageQueryResult<AssedPaper> 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(Ppaper.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<Ppaper> 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<Ppaper> query(Connection con,@Alias({"name","authors","keywords","cn4periodical","en4periodical"}) @Like String cnt,int pageSize,int pageNo) throws SQLException;

	@LimitSelect
	@Exclude("summary")
	@OrderBy(cols = {@LimitColumn(value = "sortNum", asc = false, handlerClass = LongHandler.class), @LimitColumn(value = "createTime", asc = false), @LimitColumn(value = "id", asc = false)}, value = "")
	public abstract List<Ppaper> indexSearch(Connection con,@Nullable @GroupSqlColumn(handlerClass = StringHandler.class, value = {"EN4PERIODICAL LIKE ?", "CN4PERIODICAL LIKE ?", "KEYWORDS LIKE ?", "AUTHORS LIKE ?", "NAME LIKE ?"}, isAnd = false) String key, long sortNum, String createTime, String id, int rows) throws SQLException;

	@SelectOne
	@Nullable
	public abstract Ppaper query(Connection con,long shareId)throws SQLException;
	
	public String[] queryPaperIdWithSameKeyWord(Connection con,String id,int rows)throws SQLException{
		List<String> ret = new ArrayList<String>();
		String sql = "select id,count(1) sort from pap_key_word where kw in (select kw from pap_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 pap_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()]);
	}
}