package cn.com.countrygarden.bi.main.repository.erp;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import cn.com.countrygarden.bi.main.model.erp.CacheEntity;
import cn.com.countrygarden.bi.main.model.erp.FeesSerial;
import cn.com.countrygarden.bi.main.model.erp.JdbcConstants;
import cn.com.countrygarden.bi.main.util.erp.ConnectionPool;

/**
 * @Author christ.lai
 * @Date 2018-05-31
 * @Descripe 项目中是否包含该房间编号
 */
public class JdbcPublicRepository {
	
	
	private ConnectionPool pool = null; //数据库连接池变量
	
	public JdbcPublicRepository(JdbcConstants jdbcConstants){
		this.pool = ConnectionPool.getPool(jdbcConstants); //初始化一个连接池
	}
	/**
	 * @author Austin
	 * @deprecation 检索家庭重复信息
	 * @param stmt 数据库操作
	 * @param fm FamilyMemberMsg
	 * @throws SQLException 
	 * @throws ClassNotFoundException 
	 */
    public boolean checkFamilyMember(Integer commid) throws SQLException, ClassNotFoundException{
    	Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        boolean flag = false;
        conn = pool.getConnection(); //连接池是单例的
	    // 设为手动提交
//	    conn.setAutoCommit(false); 
	    stmt = conn.createStatement();
	    String sql = "select memberName from tb_hspr_household where commid = "+commid;
    	rs = stmt.executeQuery(sql);
		if(rs.next()){
			flag = true;
		}
		rs.close();
		stmt.close();
    	conn.close();
    	return flag;
    }
    
    /**
     * @author Austin
     * @deprecation 查询房屋业主资料是否重复
     * @param stmt 数据库操作
     * @param ho HouseOwnerMsg
     * @throws SQLException
     * @throws ClassNotFoundException 
     */
    public boolean checkHouse(int commid, String roomSign ) throws SQLException, ClassNotFoundException{
    	Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        boolean flag = false;
        conn = pool.getConnection();
	    // 设为手动提交
//	    conn.setAutoCommit(false); 
	    stmt = conn.createStatement();
	    String sql = "SELECT roomid FROM  tb_hspr_room where commid="+commid+" AND RoomSign = '"+roomSign+"'";
    	rs = stmt.executeQuery(sql);
		if(rs.next()){
			flag = true;
		}
		rs.close();
		stmt.close();
    	conn.close();
    	return flag;
    }
    
    /**
     * @author Austin
     * @param jdbcConstants
     * @param commid
     * @param yzmc
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     * @description 检查客户是否存在
     */
    public boolean checkCustomer(int commid,String yzmc) throws SQLException, ClassNotFoundException{
    	Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        boolean flag = false;
        conn = pool.getConnection();
	    // 设为手动提交
//	    conn.setAutoCommit(false); 
	    stmt = conn.createStatement();
    	String sql = 
    			"SELECT CustID FROM \n"+
    			"tb_hspr_customer \n"+
    			"WHERE \n"+
    			"commid = "+commid+" \n"+
    			"AND custname = '"+yzmc+"' \n"+
    			"AND isdelete = 0; ";
    	rs = stmt.executeQuery(sql);
    	if(rs.next()){
			flag = true;
		}
    	rs.close();
		stmt.close();
    	conn.close();
    	return flag;
    }
    
    /**
     * @author Austin
     * @param jdbcConstants
     * @param commid
     * @return flag
     * @throws SQLException
     * @throws ClassNotFoundException
     * @description 基础资料导入之前，检查是否创建了对应项目
     */
    public boolean checkCommid(int commid) throws SQLException, ClassNotFoundException{
    	Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        boolean flag = false;
        conn = pool.getConnection();
	    // 设为手动提交
//	    conn.setAutoCommit(false); 
	    stmt = conn.createStatement();
    	String sql = "select commid from tb_hspr_community where commid = "+commid; 
    	rs = stmt.executeQuery(sql);
    	if(rs.next()){
			flag = true;
		}
    	rs.close();
		stmt.close();
    	conn.close();
    	return flag;
    }
    /**
     * @author Austin
     * @param jdbcConstants
     * @param commid
     * @param parkName
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     * @description 检查车位名称是否存在
     */
    public boolean checkParkName(int commid, String parkName)throws SQLException, ClassNotFoundException{
    	Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        boolean flag = false;
        conn = pool.getConnection();
	    // 设为手动提交
//	    conn.setAutoCommit(false); 
	    stmt = conn.createStatement();
    	String sql = "select parkId from tb_hspr_parking where commid = "+commid+"and parkName = " +"'"+parkName+"'"; 
    	rs = stmt.executeQuery(sql);
    	if(rs.next()){
			flag = true;
		}
    	rs.close();
		stmt.close();
    	conn.close();
    	return flag;
    }
    
    /**
     * @author Austin
     * @param jdbcConstants
     * @param commid
     * @param costName
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     * @description 检查收费项目是否存在
     */
    public boolean checkCostItem(int commid, String costName)throws SQLException, ClassNotFoundException{
    	Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        boolean flag = false;
        conn = pool.getConnection();
	    // 设为手动提交
//	    conn.setAutoCommit(false); 
	    stmt = conn.createStatement();
    	String sql = "select costId from tb_hspr_costitem where commid = "+commid+"and costName = " +"'"+costName+"'"; 
    	rs = stmt.executeQuery(sql);
    	if(rs.next()){
			flag = true;
		}
    	rs.close();
		stmt.close();
    	conn.close();
    	return flag;
    }
    /**
     * @author Austin
     * @param jdbcConstants
     * @param commid
     * @param stanName
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     * @description 检查收费标准
     */
    public boolean checkCostStandard(int commid, String stanName)throws SQLException, ClassNotFoundException{
    	Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        boolean flag = false;
        conn = pool.getConnection();
	    // 设为手动提交
//	    conn.setAutoCommit(false); 
	    stmt = conn.createStatement();
    	String sql = "select stanId from Tb_HSPR_CostStandard where commid = "+commid+"and stanName = " +"'"+stanName+"'"; 
    	rs = stmt.executeQuery(sql);
    	if(rs.next()){
			flag = true;
		}
    	rs.close();
		stmt.close();
    	conn.close();
    	return flag;
    }
    
   
    /**
     * @author Austin
     * @param conn
     * @param commid
     * @return max FeesId
     * @description 得到最大的FeesID
     */
  	public long getMaxFeesId(Connection conn, int commid){
  		String findMaxFeesId = "SELECT isnull(max(FeesID),("+commid+"* CAST(1000000000000 AS bigint))) AS maxFeesId FROM Tb_HSPR_Fees where CommID ="+commid;
  		String findMaxFeesId2 = "SELECT isnull(max(FeesID),("+commid+"* CAST(1000000000000 AS bigint))) AS maxFeesId FROM Tb_HSPR_Feeshis where CommID ="+commid;
  		long maxFeesId = 0;
  		long otherMaxFeesId = 0;
  		try {
  			Statement st = conn.createStatement();
  			ResultSet rs = st.executeQuery(findMaxFeesId);
  			while(rs.next()){
  				maxFeesId = rs.getLong("maxFeesId");
  			}
  			rs = st.executeQuery(findMaxFeesId2);
  			while(rs.next()){
  				otherMaxFeesId = rs.getLong("maxFeesId");
  			}
  		} catch (SQLException e) {
  			e.printStackTrace();
  		}
  		return (maxFeesId > otherMaxFeesId ? maxFeesId : otherMaxFeesId);
  	}
  	
  	
  	/**
  	 * @author Austin
  	 * @return List<FeesSerial>
  	 * @description 取得流水数据
  	 */
  	public List<FeesSerial> getFeesSerial(Connection conn, int commid, long startId, long endId){
          QueryRunner qr = new QueryRunner(); //创建DBUtils查询接口实例
          List<FeesSerial> fsList = new ArrayList<FeesSerial>(); //保存 查询结果集
          String sql = "select newid() as id ,a.CommID,a.FeesID,a.CustID,a.RoomID,aa.ParkID,aa.FeesDueDate \n"
  		+" ,case ISNULL(a.isdelete,0) when 0 then '实收' when 1 then '撤销实收' end as busstype \n"
  		+" ,case ISNULL(a.isdelete,0) when 0 then -ChargeAmount when 1 then ChargeAmount end as dealamount \n"
  		+" ,case ISNULL(a.isdelete,0) when 0 then ChargeDate when 1 then ChargeDate end as dealdate \n"
  		+" ,b.ChargesModeFeesDueDate \n"
  		+" ,aa.CostID \n"
  		+" ,b.CorpCostID \n"
  		+" ,d.StanID \n"
  		+" ,d.StanAmount \n"
  		+" ,d.CorpStanID \n"
  		+" ,b.房屋使用性质 as RoomPropertyUses \n"
  		+" ,b.房屋产权性质 as RoomPropertyRights \n"
  		+" ,b.车位产权性质 as ParkPropertyRights \n"
  		+" ,b.房屋实际交房时间 as RoomActualSubDate \n"
  		+" ,b.车位实际交房时间 as ParkActualSubDate \n"
  		+" ,0 as custlivetype \n"
  		+" ,b.BuildType \n"
  		+" ,b.房屋建筑面积   as BuildArea \n"
  		+" ,1 as RoomParkCount \n"
  		+" ,b.地产代付  as IsHelpPay \n"
  		+" ,0 as RoomUseState \n"
  		+" ,0 as ParkUseState \n"
  		+" ,case ISNULL(a.isdelete,0) when 1 then a.LateFeeAmount when 1 then -a.LateFeeAmount end as LateAmount \n"
  		+" ,b.客户类别  as CustTypeName \n"
  		+" ,0.00 as TaxRate \n"
  		+" ,0.00 as TaxAmount \n"
  		+" ,0.00 as Tax \n"
  		+" ,dbo.funGetLateFeeStartDate(d.DelinType,d.DelinDay,d.DelinDelay,aa.FeesDueDate) as LateStartDate \n"
  		+" ,-aa.DueAmount * d.DelinRates as LatePrice \n"
  		+" ,d.DelinRates as LateRate \n"
  		+" ,b.车位面积  as ParkArea \n"
  		+" ,d.StanFormula \n"
  		+" ,aa.RoomState \n"
  		+" ,b.房屋花园面积   as GardenArea \n"
  		+" ,b.房屋地下室面积  as YardArea \n"
  		+" ,a.ChargeMode \n"
  		+" ,aa.CommisionAmount \n"
  		+" From Tb_Hspr_FeesDetail  as a  with(nolock) \n"
  		+" left join TB_hspr_fees as aa  with(nolock) \n"
  		+" on aa.FeesID  = a.FeesID \n"
  		+" left Join Tb_HSPR_FeesProcessLog as b with(nolock) \n"
  		+" on aa.FeesID  = b.FeesID AND aa.CommID=b.CommID \n"
  		+" left outer join Tb_HSPR_CostStandard as d  with(nolock) \n"
  		+" on aa.CommID = d.CommID and aa.StanID = d.StanID \n"
  		+" where aa.CommId="+commid+" and aa.feesid in(select feesid from TB_hspr_fees where commid="+commid+" and feesid > "+startId+" and feesid<"+(endId+1)+")";
          
          try {
  			fsList = (List<FeesSerial>) qr.query(conn, sql , new BeanListHandler<FeesSerial>(FeesSerial.class)); //调用DBUtils 自动封装结果集的查询接口
  		} catch (SQLException e) {
  			e.printStackTrace();
  		} 
         return fsList;
  	}
  	/**
     * @author Austin
     * @param commid
     * @return CacheEntity
     * @throws SQLException
     * @throws ClassNotFoundException
     * @description 查询房屋编号、车位编号、客户名称 
     */
    public CacheEntity getCacheData(int commid)throws SQLException, ClassNotFoundException{
    	Connection conn = null;
    	PreparedStatement pstmt = null;
        ResultSet rs = null;
        /*
         * 初始化三个空集合
         */
        CacheEntity ce = new CacheEntity(); 
        ce.setRoomSet(new HashSet<String>());
        ce.setParkSet(new HashSet<String>());
        ce.setCustSet(new HashSet<String>());
        
        conn = pool.getConnection();
	    
        String selectRoomSign = "select roomSign from tb_hspr_room where commid = ?"; 
	    pstmt = conn.prepareStatement(selectRoomSign);
	    pstmt.setInt(1, commid);
    	rs = pstmt.executeQuery();
    	while(rs.next()){
    		ce.getRoomSet().add(rs.getString("roomSign"));
		}
    	String selectParkName = "select parkName from tb_hspr_parking where commid = ?"; 
 	    pstmt = conn.prepareStatement(selectParkName);
 	    pstmt.setInt(1, commid);
     	rs = pstmt.executeQuery();
     	while(rs.next()){
    		ce.getParkSet().add(rs.getString("parkName"));
		}
     	String selectCustName = "select custName from tb_hspr_customer where commid = ?"; 
 	    pstmt = conn.prepareStatement(selectCustName);
 	    pstmt.setInt(1, commid);
     	rs = pstmt.executeQuery();
     	while(rs.next()){
    		ce.getCustSet().add(rs.getString("custName"));
		}
    	//遍历结果集，封装房屋编号、车位编号、业主名称
    	rs.close();
		pstmt.close();
    	conn.close();
    	return ce;
    }
   
}
