package com.ekexiu.portal.dao;

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

import org.jfw.apt.annotation.Nullable;
import org.jfw.apt.orm.annotation.dao.DAO;
import org.jfw.apt.orm.annotation.dao.Dynamic;
import org.jfw.apt.orm.annotation.dao.method.From;
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.Where;
import org.jfw.apt.orm.annotation.dao.method.operator.DeleteWith;
import org.jfw.apt.orm.annotation.dao.method.operator.Insert;
import org.jfw.apt.orm.annotation.dao.method.operator.LimitSelect;
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.Update;
import org.jfw.apt.orm.annotation.dao.method.operator.UpdateWith;
import org.jfw.apt.orm.annotation.dao.param.LessThan;
import org.jfw.apt.orm.annotation.dao.param.Like;
import org.jfw.apt.orm.annotation.dao.param.Set;
import org.jfw.util.PageQueryResult;

import com.ekexiu.portal.po.Article;
import com.ekexiu.portal.pojo.FindInfo;

@DAO
public abstract class ArticleDao {
	@Insert
	public abstract int insert(Connection con, Article article) throws SQLException;
	
	@Update
	@Dynamic
	public abstract int update(Connection con, Article article) throws SQLException;
	
	@UpdateWith
	@From(Article.class)
	@SetSentence("PAGE_VIEWS = PAGE_VIEWS + 1")
	public abstract int incPageViews(Connection con,String articleId) throws SQLException;
	
	@UpdateWith
	@From(Article.class)
	public abstract int updateStatus(Connection con,String articleId,@Set String status) throws SQLException;
	
	@UpdateWith
	@From(Article.class)
	public abstract int updatePublishTime(Connection con,String articleId,@Set String status,@Set String publishTime) throws SQLException;
	
	@UpdateWith
	@From(Article.class)
	@SetSentence("ARTICLE_AGREE = ARTICLE_AGREE + 1")
	public abstract int incAgree(Connection con,String articleId) throws SQLException;
	
	@UpdateWith
	@From(Article.class)
	@SetSentence("ARTICLE_AGREE = ARTICLE_AGREE - 1")
	public abstract int decAgree(Connection con,String articleId) throws SQLException;
	
	@UpdateWith
	@From(Article.class)
	public abstract int updateSubject(Connection con, @Set String subject, String articleId) throws SQLException;
	
	@UpdateWith
	@From(Article.class)
	public abstract int updateIndustry(Connection con, @Set String industry, String articleId) throws SQLException;
	
	@DeleteWith
	@From(Article.class)
	public abstract int delete(Connection con, String articleId) throws SQLException;
	
	@DeleteWith
	@From(Article.class)
	public abstract int deletePro(Connection con, String professorId) throws SQLException;
	
	@SelectList
	@Where("STATUS = '2'")
	public abstract List<Article> queryTiming(Connection con, @LessThan String publishTime) throws SQLException;
	
	@SelectList
	@OrderBy(" ORDER BY MODIFY_TIME DESC ")
	public abstract List<Article> queryPro(Connection con, String professorId) throws SQLException;
	
	@SelectList
	@OrderBy(" ORDER BY MODIFY_TIME DESC ")
	public abstract List<Article> queryOrg(Connection con, String orgId) throws SQLException;
	
	@LimitSelect
	@OrderBy(" ORDER BY MODIFY_TIME DESC ")
	public abstract List<Article> queryLimit(Connection con,String orgId,@LessThan String modifyTime,int rows)throws SQLException;
	
	@SelectOne
	@Nullable
	public abstract Article queryOne(Connection con, String articleId) throws SQLException;
	
	@PageSelect
	@OrderBy("ORDER BY PUBLISH_TIME DESC")
	@Where("STATUS = '1'")
	public abstract PageQueryResult<Article> queryPageForPublish(Connection con,@Like@Nullable String articleTitle,int pageSize,int pageNo) throws SQLException;
	
	public PageQueryResult<Article> queryPageForSelf(Connection con,@Nullable String professorId,@Nullable String orgId,
			@Nullable String articleTitle,int pageSize,int pageNo) throws SQLException{
        int total = 0;
        PageQueryResult<Article> queryResult = new PageQueryResult<Article>();
        int index = 1;
        boolean hasProfessorId = null != professorId;
        boolean hasOrgId = null != orgId;
        boolean hasArticleTitle = null != articleTitle;
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT COUNT(1) FROM ARTICLE WHERE STATUS IN('0','1','2')");
        if(hasProfessorId){
        	sql.append(" AND PROFESSOR_ID = ?");
        }
        if(hasOrgId){
        	sql.append(" AND ORG_ID = ?");
        }
        if(hasArticleTitle){
        	sql.append(" AND ARTICLE_TITLE LIKE ?");
        }
        PreparedStatement ps = con.prepareStatement(sql.toString());
        try{
        	if(hasProfessorId){
        		ps.setString(index++, professorId);
        	}
        	if(hasOrgId){
        		ps.setString(index++, orgId);
        	}
        	if(hasArticleTitle){
        		ps.setString(index++, articleTitle);
        	}
        	queryResult.setPageSize(pageSize);
            ResultSet rs = ps.executeQuery();
            try{
                rs.next();
                total = rs.getInt(1);
            }finally{
                try{rs.close();}catch(Exception e1){}
            }
        }finally{
            try{ps.close();}catch(Exception e2){}
        }
        queryResult.setTotal(total);
        if(0== total){
        	queryResult.setPageNo(1);
        	queryResult.setData(Collections.<Article>emptyList());
            return queryResult;
        }
        boolean firstPage = (1 == pageNo);
        if(firstPage){
        	queryResult.setPageNo(1);
        	sql = new StringBuilder();
        	sql.append("(SELECT ARTICLE_ID,PROFESSOR_ID,ARTICLE_TITLE,ARTICLE_CONTENT,SUBJECT,INDUSTRY,PUBLISH_TIME,ARTICLE_IMG,ORG_ID,ARTICLE_TYPE,ARTICLE_AGREE,PAGE_VIEWS,STATUS,CREATE_TIME,MODIFY_TIME FROM ARTICLE WHERE STATUS IN('0','2')");
        	if(hasProfessorId){
            	sql.append(" AND PROFESSOR_ID = ?");
            }
            if(hasOrgId){
            	sql.append(" AND ORG_ID = ?");
            }
        	if(hasArticleTitle){
        		sql.append(" AND ARTICLE_TITLE LIKE ?");
        	}
        	sql.append(" ORDER BY MODIFY_TIME DESC) UNION ALL (SELECT ARTICLE_ID,PROFESSOR_ID,ARTICLE_TITLE,ARTICLE_CONTENT,SUBJECT,INDUSTRY,PUBLISH_TIME,ARTICLE_IMG,ORG_ID,ARTICLE_TYPE,ARTICLE_AGREE,PAGE_VIEWS,STATUS,CREATE_TIME,MODIFY_TIME FROM ARTICLE WHERE STATUS = '1'");
        	if(hasProfessorId){
            	sql.append(" AND PROFESSOR_ID = ?");
            }
            if(hasOrgId){
            	sql.append(" AND ORG_ID = ?");
            }
        	if(hasArticleTitle){
        		sql.append(" AND ARTICLE_TITLE LIKE ?");
        	}
        	sql.append(" ORDER BY PUBLISH_TIME DESC)");
        	sql.append(" LIMIT ").append(pageSize);
        }else{
            int pageNum = total / pageSize;
            if(total % pageSize != 0){
                ++pageNum;
            }
            if(pageNo > pageNum){
                pageNo = pageNum;
            }
            queryResult.setPageNo(pageNo);
            --pageNo;
            int offset = (pageNo * pageSize);
            sql = new StringBuilder();
            sql.append("(SELECT ARTICLE_ID,PROFESSOR_ID,ARTICLE_TITLE,ARTICLE_CONTENT,SUBJECT,INDUSTRY,PUBLISH_TIME,ARTICLE_IMG,ORG_ID,ARTICLE_TYPE,ARTICLE_AGREE,PAGE_VIEWS,STATUS,CREATE_TIME,MODIFY_TIME FROM ARTICLE WHERE STATUS IN('0','2')");
        	if(hasProfessorId){
            	sql.append(" AND PROFESSOR_ID = ?");
            }
            if(hasOrgId){
            	sql.append(" AND ORG_ID = ?");
            }
        	if(hasArticleTitle){
        		sql.append(" AND ARTICLE_TITLE LIKE ?");
        	}
        	sql.append(" ORDER BY MODIFY_TIME DESC) UNION ALL (SELECT ARTICLE_ID,PROFESSOR_ID,ARTICLE_TITLE,ARTICLE_CONTENT,SUBJECT,INDUSTRY,PUBLISH_TIME,ARTICLE_IMG,ORG_ID,ARTICLE_TYPE,ARTICLE_AGREE,PAGE_VIEWS,STATUS,CREATE_TIME,MODIFY_TIME FROM ARTICLE WHERE STATUS = '1'");
        	if(hasProfessorId){
            	sql.append(" AND PROFESSOR_ID = ?");
            }
            if(hasOrgId){
            	sql.append(" AND ORG_ID = ?");
            }
        	if(hasArticleTitle){
        		sql.append(" AND ARTICLE_TITLE LIKE ?");
        	}
        	sql.append(" ORDER BY PUBLISH_TIME DESC)");
            sql.append(" LIMIT ").append(pageSize).append(" OFFSET ").append(offset);
        }
        ps = con.prepareStatement(sql.toString());
        System.out.println(sql.toString());
        index = 1;
        try{
        	if(hasProfessorId){
        		ps.setString(index++, professorId);
        	}
        	if(hasOrgId){
        		ps.setString(index++, orgId);
        	}
        	if(hasArticleTitle){
        		ps.setString(index++, articleTitle);
        	}
        	if(hasProfessorId){
        		ps.setString(index++, professorId);
        	}
        	if(hasOrgId){
        		ps.setString(index++, orgId);
        	}
        	if(hasArticleTitle){
        		ps.setString(index++, articleTitle);
        	}
            ResultSet rs = ps.executeQuery();
            try{
                List<Article> articles = new ArrayList<Article>();
                queryResult.setData(articles);
                int size = 0;
                while((size<pageSize) && rs.next()){
                    ++size;
                    Article article =  new Article();
                    article.setArticleId(rs.getString(1));
                    String proId = rs.getString(2);
                    if(rs.wasNull()){
                    	proId = null;
                    }
                    article.setProfessorId(proId);
                    article.setArticleTitle(rs.getString(3));
                    String articleContent = rs.getString(4);
                    if(rs.wasNull()){
                    	articleContent = null;
                    }
                    article.setArticleContent(articleContent);
                    String subject = rs.getString(5);
                    if(rs.wasNull()){
                    	subject = null;
                    }
                    article.setSubject(subject);
                    String industry = rs.getString(6);
                    if(rs.wasNull()){
                    	industry = null;
                    }
                    article.setIndustry(industry);
                    String publishTime = rs.getString(7);
                    if(rs.wasNull()){
                    	publishTime = null;
                    }
                    article.setPublishTime(publishTime);
                    String articleImg = rs.getString(8);
                    if(rs.wasNull()){
                    	articleImg = null;
                    }
                    article.setArticleImg(articleImg);
                    String org = rs.getString(9);
                    if(rs.wasNull()){
                    	org = null;
                    }
                    article.setOrgId(org);
                    String articleType = rs.getString(10);
                    if(rs.wasNull()){
                    	articleType = null;
                    }
                    article.setArticleType(articleType);
                    article.setArticleAgree(rs.getInt(11));
                    article.setPageViews(rs.getInt(12));
                    String status = rs.getString(13);
                    if(rs.wasNull()){
                    	status = null;
                    }
                    article.setStatus(status);
                    article.setCreateTime(rs.getString(14));
                    article.setModifyTime(rs.getString(15));
                    articles.add(article);
                }
                return queryResult;
            }finally{
                try{rs.close();}catch(Exception e3){}
            }
        }finally{
            try{ps.close();}catch(Exception e4){}
        }
    }
	
	public PageQueryResult<FindInfo> queryPage(Connection con,int pageSize,int pageNo) throws SQLException{
        int total = 0;
        PageQueryResult<FindInfo> queryResult = new PageQueryResult<FindInfo>();
        String sql = null;
        sql = "SELECT SUM(NUM) FROM (SELECT COUNT(1) NUM FROM ARTICLE UNION ALL SELECT COUNT(1) NUM FROM RESOURCE) TBL";
        PreparedStatement ps = con.prepareStatement(sql);
        try{
        	queryResult.setPageSize(pageSize);
            ResultSet rs = ps.executeQuery();
            try{
                rs.next();
                total = rs.getInt(1);
            }finally{
                try{rs.close();}catch(Exception e1){}
            }
        }finally{
            try{ps.close();}catch(Exception e2){}
        }
        queryResult.setTotal(total);
        if(0== total){
        	queryResult.setPageNo(1);
        	queryResult.setData(Collections.<FindInfo>emptyList());
            return queryResult;
        }
        boolean firstPage = (1 == pageNo);
        if(firstPage){
        	queryResult.setPageNo(1);
        	sql = "SELECT ARTICLE_ID,ARTICLE_TITLE,ARTICLE_IMG,ARTICLE_TYPE,CREATE_TIME,ORG_ID,PROFESSOR_ID FROM ARTICLE"
                	+ " UNION ALL SELECT RESOURCE_ID,RESOURCE_NAME,NULL,'3',CREATE_TIME,NULL,PROFESSOR_ID FROM RESOURCE";
            sql = sql + " ORDER BY CREATE_TIME DESC ";
            sql = sql + " LIMIT " + pageSize;
        }else{
            int pageNum = total / pageSize;
            if(total % pageSize != 0){
                ++pageNum;
            }
            if(pageNo > pageNum){
                pageNo = pageNum;
            }
            queryResult.setPageNo(pageNo);
            --pageNo;
            int offset = (pageNo * pageSize);
            sql = "SELECT ARTICLE_ID,ARTICLE_TITLE,ARTICLE_IMG,ARTICLE_TYPE,CREATE_TIME,ORG_ID,PROFESSOR_ID FROM ARTICLE"
                	+ " UNION ALL SELECT RESOURCE_ID,RESOURCE_NAME,NULL,'3',CREATE_TIME,NULL,PROFESSOR_ID FROM RESOURCE";
            sql = sql + " ORDER BY CREATE_TIME DESC ";
            sql = sql + " LIMIT " + pageSize+ " OFFSET " + offset;
        }
        ps = con.prepareStatement(sql);
        try{
            ResultSet rs = ps.executeQuery();
            try{
                List<FindInfo> findInfos = new ArrayList<FindInfo>();
                queryResult.setData(findInfos);
                int size = 0;
                while((size<pageSize) && rs.next()){
                    ++size;
                    FindInfo findInfo =  new FindInfo();
                    findInfo.setId(rs.getString(1));
                    findInfo.setName(rs.getString(2));
                    String image = rs.getString(3);
                    if(rs.wasNull()){
                    	image = null;
                    }
                    findInfo.setImage(image);
                    findInfo.setType(rs.getString(4));
                    findInfo.setCreateTime(rs.getString(5));
                    String orgId = rs.getString(6);
                    if(rs.wasNull()){
                    	findInfo.setOwner(rs.getString(7));
                    }else{
                    	findInfo.setOwner(orgId);
                    }
                    findInfos.add(findInfo);
                }
                return queryResult;
            }finally{
                try{rs.close();}catch(Exception e3){}
            }
        }finally{
            try{ps.close();}catch(Exception e4){}
        }
    }

}