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> queryForOrg(Connection con,String orgId)throws SQLException;
	
	@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 total = 0;
        PageQueryResult<Demand> queryResult = new PageQueryResult<Demand>();
        int index = 1;
        boolean hasDemandStatus = null != demandStatus;
        StringBuilder sql = new StringBuilder();
        sql.append(" WHERE DEMANDER = ?");
        if(hasDemandStatus){
            sql.append(" AND DEMAND_STATUS = ?");
        }
        StringBuilder whereSql = sql;
        sql = new StringBuilder();
        sql.append("SELECT COUNT(1) FROM DEMAND");
        sql.append(whereSql);
        PreparedStatement ps = con.prepareStatement(sql.toString());
        try{
            ps.setString(index++,demander);
            if(hasDemandStatus){
                ps.setString(index++,demandStatus);
            }
            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.<Demand>emptyList());
            return queryResult;
        }
        index = 1;
        boolean firstPage = (1 == pageNo);
        if(firstPage){
        	queryResult.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(whereSql);
            sql.append(" ORDER BY CREATE_TIME");
            if(sortType == 0){
            	sql.append(" DESC ");
            }
            sql.append(" LIMIT ").append(pageSize);
        }else{
            int pageNumber = total / pageSize;
            if(total % pageSize != 0){
                ++pageNumber;
            }
            if(pageNo > pageNumber){
                pageNo = pageNumber;
            }
            queryResult.setPageNo(pageNo);
            --pageNo;
            int offset = (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(whereSql);
            sql.append(" ORDER BY CREATE_TIME");
            if(sortType == 0){
            	sql.append(" DESC ");
            }
            sql.append(" LIMIT ").append(pageSize).append(" OFFSET ").append(offset);
        }
        ps = con.prepareStatement(sql.toString());
        try{
            ps.setString(index++,demander);
            if(hasDemandStatus){
                ps.setString(index++,demandStatus);
            }
            ResultSet rs = ps.executeQuery();
            try{
                List<Demand> demands = new ArrayList<Demand>();
                queryResult.setData(demands);
                int size = 0;
                while((size<pageSize) && rs.next()){
                    ++size;
                    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 demandContent = rs.getString(6);
                    if(rs.wasNull()){
                    	demandContent = null;
                    }
                    demand.setDemandContent(demandContent);
                    demand.setDemandStatus(rs.getString(7));
                    String closeTime = rs.getString(8);
                    if(rs.wasNull()){
                    	closeTime = null;
                    }
                    demand.setCloseTime(closeTime);
                    demand.setOrderKey(rs.getLong(9));
                    demand.setCreateTime(rs.getString(10));
                    demands.add(demand);
                }
                return queryResult;
            }finally{
                try{rs.close();}catch(Exception e3){}
            }
        }finally{
            try{ps.close();}catch(Exception e4){}
        }
    }
	
	public PageQueryResult<Demand> pageQuery(Connection con,String demandStatus,String demandAim,int sortType,int pageSize,int pageNo) throws SQLException{
        int total = 0;
        PageQueryResult<Demand> queryResult = new PageQueryResult<Demand>();
        int index = 1;
        boolean hasDemandAim = null != demandAim;
        StringBuilder sql = new StringBuilder();
        sql.append(" WHERE DEMAND_STATUS = ?");
        if(hasDemandAim){
            sql.append(" AND DEMAND_AIM = ?");
        }
        StringBuilder whereSql = sql;
        sql = new StringBuilder();
        sql.append("SELECT COUNT(1) FROM DEMAND");
        if(whereSql.length()>0){
            sql.append(whereSql);
        }
        PreparedStatement ps = con.prepareStatement(sql.toString());
        try{
        	ps.setString(index++, demandStatus);
            if(hasDemandAim){
                ps.setString(index++,demandAim);
            }
            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.<Demand>emptyList());
            return queryResult;
        }
        index = 1;
        boolean firstPage = (1 == pageNo);
        if(firstPage){
        	queryResult.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,ORG_ID FROM DEMAND");
            if(whereSql.length()>0){
                sql.append(whereSql);
            }
            sql.append(" ORDER BY CREATE_TIME");
            if(sortType == 0){
            	sql.append(" 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 DEMAND_ID,DEMANDER,DEMAND_AIM,DEMAND_TYPE,DEMAND_TITLE,DEMAND_CONTENT,DEMAND_STATUS,CLOSE_TIME,ORDER_KEY,CREATE_TIME,ORG_ID FROM DEMAND");
            if(whereSql.length()>0){
                sql.append(whereSql);
            }
            sql.append(" ORDER BY CREATE_TIME");
            if(sortType == 0){
            	sql.append(" DESC ");
            }
            sql.append(" LIMIT ").append(pageSize).append(" OFFSET ").append(offset);
        }
        ps = con.prepareStatement(sql.toString());
        try{
        	ps.setString(index++, demandStatus);
            if(hasDemandAim){
                ps.setString(index++,demandAim);
            }
            ResultSet rs = ps.executeQuery();
            try{
                List<Demand> demands = new ArrayList<Demand>();
                queryResult.setData(demands);
                int size = 0;
                while((size<pageSize) && rs.next()){
                    ++size;
                    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 demandContent = rs.getString(6);
                    if(rs.wasNull()){
                        demandContent = null;
                    }
                    demand.setDemandContent(demandContent);
                    demand.setDemandStatus(rs.getString(7));
                    String closeTime = rs.getString(8);
                    if(rs.wasNull()){
                    	closeTime = null;
                    }
                    demand.setCloseTime(closeTime);
                    demand.setOrderKey(rs.getLong(9));
                    demand.setCreateTime(rs.getString(10));
                    String orgId = rs.getString(11);
                    if(rs.wasNull()){
                    	orgId = null;
                    }
                    demand.setOrgId(orgId);
                    demands.add(demand);
                }
                return queryResult;
            }finally{
                try{rs.close();}catch(Exception e3){}
            }
        }finally{
            try{ps.close();}catch(Exception e4){}
        }
    }
	
	public PageQueryResult<Demand> pageQueryOrg(Connection con,String orgId,String demandStatus,int sortType,int pageSize,int pageNo) throws SQLException{
        int total = 0;
        PageQueryResult<Demand> queryResult = new PageQueryResult<Demand>();
        int index = 1;
        boolean hasDemandStatus = null != demandStatus;
        StringBuilder sql = new StringBuilder();
        sql.append(" WHERE DEMAND_TYPE = '2' AND ORG_ID = ? ");
        if(hasDemandStatus){
            sql.append(" AND DEMAND_STATUS = ?");
        }
        StringBuilder whereSql = sql;
        sql = new StringBuilder();
        sql.append("SELECT COUNT(1) FROM DEMAND");
        if(whereSql.length()>0){
            sql.append(whereSql);
        }
        PreparedStatement ps = con.prepareStatement(sql.toString());
        try{
        	ps.setString(index++, orgId);
            if(hasDemandStatus){
                ps.setString(index++,demandStatus);
            }
            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.<Demand>emptyList());
            return queryResult;
        }
        index = 1;
        boolean firstPage = (1 == pageNo);
        if(firstPage){
        	queryResult.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,ORG_ID FROM DEMAND");
            if(whereSql.length()>0){
                sql.append(whereSql);
            }
            sql.append(" ORDER BY CREATE_TIME");
            if(sortType == 0){
            	sql.append(" 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 DEMAND_ID,DEMANDER,DEMAND_AIM,DEMAND_TYPE,DEMAND_TITLE,DEMAND_CONTENT,DEMAND_STATUS,CLOSE_TIME,ORDER_KEY,CREATE_TIME,ORG_ID FROM DEMAND");
            if(whereSql.length()>0){
                sql.append(whereSql);
            }
            sql.append(" ORDER BY CREATE_TIME");
            if(sortType == 0){
            	sql.append(" DESC ");
            }
            sql.append(" LIMIT ").append(pageSize).append(" OFFSET ").append(offset);
        }
        ps = con.prepareStatement(sql.toString());
        try{
        	ps.setString(index++, orgId);
            if(hasDemandStatus){
                ps.setString(index++,demandStatus);
            }
            ResultSet rs = ps.executeQuery();
            try{
                List<Demand> demands = new ArrayList<Demand>();
                queryResult.setData(demands);
                int size = 0;
                while((size<pageSize) && rs.next()){
                    ++size;
                    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 demandContent = rs.getString(6);
                    if(rs.wasNull()){
                        demandContent = null;
                    }
                    demand.setDemandContent(demandContent);
                    demand.setDemandStatus(rs.getString(7));
                    String closeTime = rs.getString(8);
                    if(rs.wasNull()){
                    	closeTime = null;
                    }
                    demand.setCloseTime(closeTime);
                    demand.setOrderKey(rs.getLong(9));
                    demand.setCreateTime(rs.getString(10));
                    String orgid = rs.getString(11);
                    if(rs.wasNull()){
                    	orgid = null;
                    }
                    demand.setOrgId(orgid);
                    demands.add(demand);
                }
                return queryResult;
            }finally{
                try{rs.close();}catch(Exception e3){}
            }
        }finally{
            try{ps.close();}catch(Exception e4){}
        }
    }
	
	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> demands = 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));
                    demands.add(demand);
                }
                return demands;
            }finally{
                try{rs.close();}catch(Exception e1){}
            }
        }finally{
            try{ps.close();}catch(Exception e2){}
        }
    }
	
}