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.List;

import org.jfw.apt.annotation.Nullable;
import org.jfw.apt.orm.annotation.dao.DAO;
import org.jfw.apt.orm.annotation.dao.method.OrderBy;
import org.jfw.apt.orm.annotation.dao.method.operator.Insert;
import org.jfw.apt.orm.annotation.dao.method.operator.SelectList;
import org.jfw.apt.orm.annotation.dao.method.operator.SelectOne;

import com.ekexiu.portal.po.Demand;

@DAO
public abstract class DemandDao {
	@Insert
	public abstract int insert(Connection con,Demand demand)throws SQLException;
	
	@SelectOne
	@Nullable
	public abstract Demand queryOne(Connection con,String demandId)throws SQLException;
	
	@SelectList
	@OrderBy("ORDER BY CREATE_TIME DESC")
	public abstract List<Demand> queryByDemander(Connection con,String demander)throws SQLException;
	
	public List<Demand> queryByStatus(Connection con,String demandStatus,String demandAim,String subject,String industry,String createTime,long orderKey,int rows,int sortType) throws SQLException{
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT DEMAND_ID,DEMANDER,DEMAND_AIM,DEMAND_TYPE,DEMAND_TITLE,DEMAND_CONTENT,DEMAND_STATUS,ORDER_KEY,CREATE_TIME FROM DEMAND ");
        boolean hasDemandAim = null != demandAim;
        boolean hasSubject = null !=subject;
        boolean hasIndustry = null != industry;
        sql.append(" WHERE DEMAND_STATUS = ? AND ORDER_KEY < ? AND CREATE_TIME < ? ");
        if(hasDemandAim){
        	sql.append(" AND DEMAND_AIM = ? ");
        }
        if(hasSubject){
        	sql.append(" AND DEMAND_ID IN (SELECT DEMAND_ID FROM DEMAND_SUB_OR_INDUS WHERE SUB_OR_INDUS LIKE ?) ");
        }
        if(hasIndustry){
        	sql.append(" AND DEMAND_ID IN (SELECT DEMAND_ID FROM DEMAND_SUB_OR_INDUS WHERE SUB_OR_INDUS LIKE ?) ");
        }
        sql.append(" ORDER BY CREATE_TIME ");
        if(sortType == 0){
        	sql.append(" DESC ");
        }
        sql.append(" ,ORDER_KEY ");
        if(sortType == 0){
        	sql.append(" DESC ");
        }
        sql.append(" LIMIT ").append(rows);
        int index = 1;
        PreparedStatement ps = con.prepareStatement(sql.toString());
        try{
        	ps.setString(index++, demandStatus);
            ps.setLong(index++,orderKey);
            ps.setString(index++,createTime);
            if(hasDemandAim){
            	ps.setString(index++,demandAim);
            }
            if(hasSubject){
            	ps.setString(index++,subject);
            }
            if(hasIndustry){
            	ps.setString(index++,industry);
            }
            ResultSet rs = ps.executeQuery();
            try{
                List<Demand> _result = new ArrayList<Demand>();
                while(rs.next()){
                    Demand demand =  new Demand();
                    demand.setDemandId(rs.getString(1));
                    demand.setDemander(rs.getString(2));
                    demand.setDemandAim(rs.getString(3));
                    demand.setDemandType(rs.getString(4));
                    demand.setDemandTitle(rs.getString(5));
                    String content = rs.getString(6);
                    if(rs.wasNull()){
                    	content = null;
                    }
                    demand.setDemandContent(content);
                    demand.setDemandStatus(rs.getString(7));
                    demand.setOrderKey(rs.getLong(8));
                    demand.setCreateTime(rs.getString(9));
                    _result.add(demand);
                }
                return _result;
            }finally{
                try{rs.close();}catch(Exception e1){}
            }
        }finally{
            try{ps.close();}catch(Exception e2){}
        }
    }
	
}