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.Column;
import org.jfw.apt.orm.annotation.dao.DAO;
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.operator.Insert;
import org.jfw.apt.orm.annotation.dao.method.operator.QueryVal;
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.Set;
import org.jfw.apt.orm.core.defaultImpl.IntHandler;
import org.jfw.util.PageQueryResult;
import org.jfw.util.exception.JfwBaseException;

import com.ekexiu.portal.po.Demand;

@DAO
public abstract class DemandDao {
	@Insert
	public abstract int insert(Connection con,Demand demand)throws SQLException;
	
	@UpdateWith
	@From(Demand.class)
	public abstract int updateDemandStatus(Connection con,String demandId,@Set String demandStatus,@Set String closeTime)throws SQLException;
	
	@SelectOne
	@Nullable
	public abstract Demand queryOne(Connection con,String demandId)throws SQLException;
	
	@QueryVal
	@Column(handlerClass=IntHandler.class,value="COUNT(1)")
	@From(Demand.class)
	public abstract int queryCountByStatus(Connection con,String demander,String demandStatus)throws SQLException, JfwBaseException;
	
	@QueryVal
	@Column(handlerClass=IntHandler.class,value="COUNT(1)")
	@From(Demand.class)
	public abstract int queryCountStatus(Connection con,String demandStatus)throws SQLException, JfwBaseException;
	
	@SelectList
	@OrderBy("ORDER BY CREATE_TIME DESC")
	public abstract List<Demand> queryByDemander(Connection con,String demander)throws SQLException;
	
	public PageQueryResult<Demand> pageQueryByDemander(Connection con,String demander,String demandStatus,int sortType,int pageSize,int pageNo) throws SQLException{
        int _m_1 = 0;
        PageQueryResult<Demand> _result = new PageQueryResult<Demand>();
        int _m_2 = 1;
        boolean _m_3 = null == demandStatus;
        StringBuilder sql = new StringBuilder();
        sql.append(" WHERE DEMANDER = ?");
        if(!_m_3){
            sql.append(" AND DEMAND_STATUS = ?");
        }
        StringBuilder _m_4 = sql;
        sql = new StringBuilder();
        sql.append("SELECT COUNT(1) FROM DEMAND");
        sql.append(_m_4);
        PreparedStatement ps = con.prepareStatement(sql.toString());
        try{
            ps.setString(_m_2++,demander);
            if(!_m_3){
                ps.setString(_m_2++,demandStatus);
            }
            _result.setPageSize(pageSize);
            ResultSet _pageRs = ps.executeQuery();
            try{
                _pageRs.next();
                _m_1 = _pageRs.getInt(1);
            }finally{
                try{_pageRs.close();}catch(Exception _m_5){}
            }
        }finally{
            try{ps.close();}catch(Exception _m_6){}
        }
        _result.setTotal(_m_1);
        if(0== _m_1){
            _result.setPageNo(1);
            _result.setData(Collections.<Demand>emptyList());
            return _result;
        }
        _m_2 = 1;
        boolean _m_7 = (1 == pageNo);
        if(_m_7){
            _result.setPageNo(1);
            sql = new StringBuilder();
            sql.append("SELECT DEMAND_ID,DEMANDER,DEMAND_AIM,DEMAND_TYPE,DEMAND_TITLE,DEMAND_CONTENT,DEMAND_STATUS,CLOSE_TIME,ORDER_KEY,CREATE_TIME FROM DEMAND");
            sql.append(_m_4);
            sql.append(" ORDER BY CREATE_TIME");
            if(sortType == 0){
            	sql.append(" DESC ");
            }
            sql.append(" LIMIT ").append(pageSize);
        }else{
            int _m_8 = _m_1 / pageSize;
            if(_m_1 % pageSize != 0){
                ++_m_8;
            }
            if(pageNo > _m_8){
                pageNo = _m_8;
            }
            _result.setPageNo(pageNo);
            --pageNo;
            int _m_9 = (pageNo * pageSize);
            sql = new StringBuilder();
            sql.append("SELECT DEMAND_ID,DEMANDER,DEMAND_AIM,DEMAND_TYPE,DEMAND_TITLE,DEMAND_CONTENT,DEMAND_STATUS,CLOSE_TIME,ORDER_KEY,CREATE_TIME FROM DEMAND");
            sql.append(_m_4);
            sql.append(" ORDER BY CREATE_TIME");
            if(sortType == 0){
            	sql.append(" DESC ");
            }
            sql.append(" LIMIT ").append(pageSize).append(" OFFSET ").append(_m_9);
        }
        ps = con.prepareStatement(sql.toString());
        try{
            ps.setString(_m_2++,demander);
            if(!_m_3){
                ps.setString(_m_2++,demandStatus);
            }
            ResultSet rs = ps.executeQuery();
            try{
                List<Demand> _m_10 = new ArrayList<Demand>();
                _result.setData(_m_10);
                int _m_11 = 0;
                while((_m_11<pageSize) && rs.next()){
                    ++_m_11;
                    Demand _obj =  new Demand();
                    _obj.setDemandId(rs.getString(1));
                    _obj.setDemander(rs.getString(2));
                    _obj.setDemandAim(rs.getString(3));
                    _obj.setDemandType(rs.getString(4));
                    _obj.setDemandTitle(rs.getString(5));
                    String _m_12 = rs.getString(6);
                    if(rs.wasNull()){
                        _m_12 = null;
                    }
                    _obj.setDemandContent(_m_12);
                    _obj.setDemandStatus(rs.getString(7));
                    String _m_13 = rs.getString(8);
                    if(rs.wasNull()){
                        _m_13 = null;
                    }
                    _obj.setCloseTime(_m_13);
                    _obj.setOrderKey(rs.getLong(9));
                    _obj.setCreateTime(rs.getString(10));
                    _m_10.add(_obj);
                }
                return _result;
            }finally{
                try{rs.close();}catch(Exception _m_14){}
            }
        }finally{
            try{ps.close();}catch(Exception _m_15){}
        }
    }
	
	public PageQueryResult<Demand> pageQuery(Connection con,String demandStatus,String demandAim,int sortType,int pageSize,int pageNo) throws SQLException{
        int _m_1 = 0;
        PageQueryResult<Demand> _result = new PageQueryResult<Demand>();
        int _m_3 = 1;
        boolean _m_2 = null == demandAim;
        StringBuilder sql = new StringBuilder();
        sql.append(" WHERE DEMAND_STATUS = ?");
        if(!_m_2){
            sql.append(" AND DEMAND_AIM = ?");
        }
        StringBuilder _m_4 = sql;
        sql = new StringBuilder();
        sql.append("SELECT COUNT(1) FROM DEMAND");
        if(_m_4.length()>0){
            sql.append(_m_4);
        }
        PreparedStatement ps = con.prepareStatement(sql.toString());
        try{
        	ps.setString(_m_3++, demandStatus);
            if(!_m_2){
                ps.setString(_m_3++,demandAim);
            }
            _result.setPageSize(pageSize);
            ResultSet _pageRs = ps.executeQuery();
            try{
                _pageRs.next();
                _m_1 = _pageRs.getInt(1);
            }finally{
                try{_pageRs.close();}catch(Exception _m_5){}
            }
        }finally{
            try{ps.close();}catch(Exception _m_6){}
        }
        _result.setTotal(_m_1);
        if(0== _m_1){
            _result.setPageNo(1);
            _result.setData(Collections.<Demand>emptyList());
            return _result;
        }
        _m_3 = 1;
        boolean _m_7 = (1 == pageNo);
        if(_m_7){
            _result.setPageNo(1);
            sql = new StringBuilder();
            sql.append("SELECT DEMAND_ID,DEMANDER,DEMAND_AIM,DEMAND_TYPE,DEMAND_TITLE,DEMAND_CONTENT,DEMAND_STATUS,CLOSE_TIME,ORDER_KEY,CREATE_TIME FROM DEMAND");
            if(_m_4.length()>0){
                sql.append(_m_4);
            }
            sql.append(" ORDER BY CREATE_TIME");
            if(sortType == 0){
            	sql.append(" DESC ");
            }
            sql.append(" LIMIT ").append(pageSize);
        }else{
            int _m_8 = _m_1 / pageSize;
            if(_m_1 % pageSize != 0){
                ++_m_8;
            }
            if(pageNo > _m_8){
                pageNo = _m_8;
            }
            _result.setPageNo(pageNo);
            --pageNo;
            int _m_9 = (pageNo * pageSize);
            sql = new StringBuilder();
            sql.append("SELECT DEMAND_ID,DEMANDER,DEMAND_AIM,DEMAND_TYPE,DEMAND_TITLE,DEMAND_CONTENT,DEMAND_STATUS,CLOSE_TIME,ORDER_KEY,CREATE_TIME FROM DEMAND");
            if(_m_4.length()>0){
                sql.append(_m_4);
            }
            sql.append(" ORDER BY CREATE_TIME");
            if(sortType == 0){
            	sql.append(" DESC ");
            }
            sql.append(" LIMIT ").append(pageSize).append(" OFFSET ").append(_m_9);
        }
        ps = con.prepareStatement(sql.toString());
        try{
        	ps.setString(_m_3++, demandStatus);
            if(!_m_2){
                ps.setString(_m_3++,demandAim);
            }
            ResultSet rs = ps.executeQuery();
            try{
                List<Demand> _m_10 = new ArrayList<Demand>();
                _result.setData(_m_10);
                int _m_11 = 0;
                while((_m_11<pageSize) && rs.next()){
                    ++_m_11;
                    Demand _obj =  new Demand();
                    _obj.setDemandId(rs.getString(1));
                    _obj.setDemander(rs.getString(2));
                    _obj.setDemandAim(rs.getString(3));
                    _obj.setDemandType(rs.getString(4));
                    _obj.setDemandTitle(rs.getString(5));
                    String _m_12 = rs.getString(6);
                    if(rs.wasNull()){
                        _m_12 = null;
                    }
                    _obj.setDemandContent(_m_12);
                    _obj.setDemandStatus(rs.getString(7));
                    String _m_13 = rs.getString(8);
                    if(rs.wasNull()){
                        _m_13 = null;
                    }
                    _obj.setCloseTime(_m_13);
                    _obj.setOrderKey(rs.getLong(9));
                    _obj.setCreateTime(rs.getString(10));
                    _m_10.add(_obj);
                }
                return _result;
            }finally{
                try{rs.close();}catch(Exception _m_14){}
            }
        }finally{
            try{ps.close();}catch(Exception _m_15){}
        }
    }
	
	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){}
        }
    }
	
}