dealerprizequery.java

一个直销用的管理软件

     * @param currentPage 当前页
     * @return 返回经销商这一周得到的奖金明细和经销商的一些相关信息
     */
    public List getDealerPrizeMoneyOneByWeek(String dealerID,String years,String weeks,int currentPage){
    	DBConnectionManager db = new DBConnectionManager();
        Connection conn = db.getConn();
        ResultSet rs = null;
        List list = new ArrayList();
        try{
            //得到所有的记录数
            CallableStatement cstmt = conn.prepareCall("{call pagination(?,?,?,?,?,?,?,?)}");
            cstmt.setString(1,"t_UserMoneyOne,t_usersign,t_usersign t_usersign_1");
        	cstmt.setString(2,"t_usersign.name, t_usersign_1.name AS name1, t_UserMoneyOne.GetUserid,t_UserMoneyOne.Userid, t_UserMoneyOne.onemoney, t_UserMoneyOne.gettime,t_UserMoneyOne.GetType, t_UserMoneyOne.area, t_usersign.sort,t_usersign_1.sort AS sort1");
        	cstmt.setString(3,"ID");
        	cstmt.setInt(4,Dictionary.PAGESIZE);
        	cstmt.setInt(5,currentPage);
        	cstmt.setInt(6,1);
        	cstmt.setInt(7,1);
        	cstmt.setString(8,"t_UserMoneyOne.GetUserid = t_usersign.Userid and t_UserMoneyOne.Userid = t_usersign_1.Userid and (t_UserMoneyOne.years = " + years + ") AND (t_UserMoneyOne.weeks = " + weeks + ") AND (t_UserMoneyOne.GetUserid = '" + dealerID + "')");
        	
        	rs = cstmt.executeQuery();
        	while(rs.next()){
        	    pager = new Pager(rs.getInt("Total"),Dictionary.PAGESIZE,currentPage);
        	}
        	if(rs!=null){
        	    rs.close();
        	}
        	
        	//得到查询的记录,并存储到集合中
        	cstmt.setInt(6,0);
        	rs = cstmt.executeQuery();
        	while(rs.next()){
        	    Franchiser bean = new Franchiser();
        	    bean.setName(rs.getString("name"));
        	    bean.setName1(rs.getString("name1"));
        	    bean.setMoneyOneGetUserid(rs.getString("getUserid"));
        	    bean.setMoneyOneUserid(rs.getString("userid"));
        	    bean.setMoneyOnePrizeY(Unit.getMoneyByY(rs.getInt("onemoney")));
        	    bean.setMoneyOneGetTime(Unit.getFormatDate(rs.getString("gettime")));
        	    if(rs.getInt("gettype")==1){
        	    	bean.setMoneyOneGetTypeStr("直接下属");
        	    } else {
        	    	bean.setMoneyOneGetTypeStr("推荐下属");
        	    }
        	    bean.setMoneyOneArea(rs.getString("area"));
        	    //判断经销商的类型
            	if(rs.getInt("sort")==1){
            	    bean.setSortType("经销商");
            	} else {
            	    bean.setSortType("准经销商");
            	}
            	if(rs.getInt("sort1")==1){
            	    bean.setSortType1("经销商");
            	} else {
            	    bean.setSortType1("准经销商");
            	}
        	    list.add(bean);
        	}        	
        	
        	if(rs!=null){
        	    rs.close();
        	}
        	if(cstmt!=null){
        	    cstmt.close();
        	}
        	if(conn!=null){
        	    conn.close();
        	}
        	
        } catch (SQLException e){
            log.error("得到经销每周所得的市场开发奖金明细时出错:" + e);
        }
        return list;
    }
    
    /**
     * 得到经销每天所得的市场拓展奖金明细(和getDealerPrizeMoneyTwoByWeek方法不同之处在于,此方法是得到某一周的数据,而不仅仅是一天的)
     * @param dealerID 经销商编号
     * @param monthDate 时间
     * @param currentPage 当前页
     * @return 返回经销商奖金
     */
    public List getDealerPrizeMoneyTwoByWeek(String dealerID,String years,String weeks,int currentPage){
        DBConnectionManager db = new DBConnectionManager();
        Connection conn = db.getConn();
        ResultSet rs = null;
        List list = new ArrayList();
        try{
            //得到所有的记录数
            CallableStatement cstmt = conn.prepareCall("{call pagination(?,?,?,?,?,?,?,?)}");
            cstmt.setString(1,"t_UserMoneyTwo, t_usersign");
        	cstmt.setString(2,"t_usersign.name, t_UserMoneyTwo.GetUserid, t_UserMoneyTwo.div,t_UserMoneyTwo.arearelation, t_UserMoneyTwo.Getnumber,t_UserMoneyTwo.twomoney, t_UserMoneyTwo.Gettime,t_usersign.sort");
        	cstmt.setString(3,"ID");
        	cstmt.setInt(4,Dictionary.PAGESIZE);
        	cstmt.setInt(5,currentPage);
        	cstmt.setInt(6,1);
        	cstmt.setInt(7,1);
        	cstmt.setString(8,"t_UserMoneyTwo.GetUserid = t_usersign.Userid and (t_UserMoneyTwo.years = " + years + ") AND (t_UserMoneyTwo.weeks = " + weeks + ") AND (t_UserMoneyTwo.GetUserid = '" + dealerID + "')");
        	rs = cstmt.executeQuery();
        	while(rs.next()){
        	    pager = new Pager(rs.getInt("Total"),Dictionary.PAGESIZE,currentPage);
        	}
        	if(rs!=null){
        	    rs.close();
        	}
        	
        	//得到查询的记录,并存储到集合中
        	cstmt.setInt(6,0);
        	rs = cstmt.executeQuery();
        	while(rs.next()){
        	    Franchiser bean = new Franchiser();
        	    bean.setMoneyTwoGetUserid(rs.getString("getuserid"));
        	    bean.setName(rs.getString("name"));
        	    bean.setMoneyTwoDiv(rs.getInt("div"));
        	    bean.setMoneyTwoArearelation(rs.getString("arearelation"));
        	    bean.setMoneyTwoTwomoneyY(Unit.getMoneyByY(rs.getInt("twomoney")));
        	    bean.setMoneyTwoGettime(Unit.getFormatDate(rs.getString("gettime")));
        	    //判断经销商的类型
            	if(rs.getInt("sort")==1){
            	    bean.setSortType("经销商");
            	} else {
            	    bean.setSortType("准经销商");
            	}
        	    list.add(bean);
        	}        	
        	
        	if(rs!=null){
        	    rs.close();
        	}
        	if(cstmt!=null){
        	    cstmt.close();
        	}
        	if(conn!=null){
        	    conn.close();
        	}
        	
        } catch (SQLException e){
            log.error("得到经销商市场拓展奖金明细时出错:" + e);
        }
        return list;
    }
    
    /**
     * 得到经销每天所得的推荐下线奖金明细(和getDealerPrizeMoneyOneOtherByWeek方法不同之处在于,此方法是计算一周内的奖金,而不是一天的)
     * @param dealerID 经销商编号
     * @param years 年份
     * @param weeks 所属周
     * @param currentpage 当前页
     * @return 返回经销商奖金,以及经销商的一些相关信息
     */
    public List getDealerPrizeMoneyOneOtherByWeek(String dealerID,String years,String weeks,int currentPage){
        DBConnectionManager db = new DBConnectionManager();
        Connection conn = db.getConn();
        ResultSet rs = null;
        List list = new ArrayList();
        try{
            //得到所有的记录数
            CallableStatement cstmt = conn.prepareCall("{call pagination(?,?,?,?,?,?,?,?)}");
            cstmt.setString(1,"t_UserMoneyOne_other,t_usersign,t_usersign t_usersign_1");
        	cstmt.setString(2,"t_usersign.name, t_usersign_1.name AS name1,t_UserMoneyOne_other.GetUserid, t_UserMoneyOne_other.Userid,t_UserMoneyOne_other.area, t_UserMoneyOne_other.money,t_UserMoneyOne_other.gettime,t_usersign_1.sort AS sort1,t_usersign.sort");
        	cstmt.setString(3,"ID");
        	cstmt.setInt(4,Dictionary.PAGESIZE);
        	cstmt.setInt(5,currentPage);
        	cstmt.setInt(6,1);
        	cstmt.setInt(7,1);
        	cstmt.setString(8,"t_UserMoneyOne_other.GetUserid = t_usersign.Userid and t_UserMoneyOne_other.Userid = t_usersign_1.Userid and t_UserMoneyOne_other.years=" + years + " and t_UserMoneyOne_other.weeks=" + weeks + " and t_UserMoneyOne_other.GetUserid='" + dealerID + "'");

        	rs = cstmt.executeQuery();
        	while(rs.next()){
        	    pager = new Pager(rs.getInt("Total"),Dictionary.PAGESIZE,currentPage);
        	}
        	if(rs!=null){
        	    rs.close();
        	}
        	
        	//得到查询的记录,并存储到集合中
        	cstmt.setInt(6,0);
        	rs = cstmt.executeQuery();
        	while(rs.next()){
        	    Franchiser bean = new Franchiser();
        	    bean.setUserMoneyOneOtherGetUserid(rs.getString("GetUserid"));
        	    bean.setName(rs.getString("name"));
        	    bean.setUserMoneyOneOtherUserid(rs.getString("userid"));
        	    bean.setName1(rs.getString("name1"));
        	    bean.setUserMoneyOneOtherArea(rs.getString("area"));
        	    bean.setUserMoneyOneOtherMoneyY(Unit.getMoneyByY(rs.getInt("money")));
        	    bean.setUserMoneyOneOtherGettime(Unit.getFormatDate(rs.getString("gettime")));
        	    //判断经销商的类型
            	if(rs.getInt("sort")==1){
            	    bean.setSortType("经销商");
            	} else {
            	    bean.setSortType("准经销商");
            	}
            	if(rs.getInt("sort1")==1){
            	    bean.setSortType1("经销商");
            	} else {
            	    bean.setSortType1("准经销商");
            	}
        	    list.add(bean);
        	}        	
        	
        	if(rs!=null){
        	    rs.close();
        	}
        	if(cstmt!=null){
        	    cstmt.close();
        	}
        	if(conn!=null){
        	    conn.close();
        	}
        	
        } catch (SQLException e){
            log.error("得到经销每天所得的推荐下线奖金明细时出错:" + e);
        }
        return list;
    }
    
    /**
     * 得到公司每月销售总额
     * @param monthDate 日期,格式为2005-09
     * @param currentPage 当前页
     * @return 返回公司销售额
     */
    public List getCompanyTotalConsume(String monthDate,int currentPage){
        DBConnectionManager db = new DBConnectionManager();
        Connection conn = db.getConn();
        ResultSet rs = null;
        List list = new ArrayList();
        try{
            //得到所有的记录数
            CallableStatement cstmt = conn.prepareCall("{call pagination(?,?,?,?,?,?,?,?)}");
            cstmt.setString(1,"t_company_sale");
        	cstmt.setString(2,"*");
        	cstmt.setString(3,"updatetime");
        	cstmt.setInt(4,Dictionary.PAGESIZE);
        	cstmt.setInt(5,currentPage);
        	cstmt.setInt(6,1);
        	cstmt.setInt(7,1);
        	cstmt.setString(8,"SUBSTRING(CONVERT(char(10), updatetime, 121), 1, 7)='" + monthDate + "'");
        	rs = cstmt.executeQuery();
        	while(rs.next()){
        	    pager = new Pager(rs.getInt("Total"),Dictionary.PAGESIZE,currentPage);
        	}
        	if(rs!=null){
        	    rs.close();
        	}
        	
        	//得到查询的记录,并存储到集合中,注此处是将一个月的四条记录合并在一起
        	cstmt.setInt(6,0);
        	rs = cstmt.executeQuery();
        	int usermoney = 0;
        	int gusermoney = 0;
        	int consumemoney = 0;
        	String updatetime = "";
        	while(rs.next()){
        	    usermoney = usermoney + rs.getInt("usermoney");
        	    gusermoney = gusermoney + rs.getInt("gusermoney");
        	    consumemoney = consumemoney + rs.getInt("consumemoney");
        	    updatetime = rs.getString("updatetime");
        	}        	
        	Franchiser bean = new Franchiser();
    	    bean.setUsermoneyY(Unit.getMoneyByY(usermoney));
    	    bean.setGusermoneyY(Unit.getMoneyByY(gusermoney));
    	    bean.setGconsumeMoneyY(Unit.getMoneyByY(consumemoney));
    	    bean.setUpdatetime(Unit.getFormatDateNoDay(updatetime));
    	    list.add(bean);
        	
        	if(rs!=null){
        	    rs.close();
        	}
        	if(cstmt!=null){
        	    cstmt.close();
        	}
        	if(conn!=null){
        	    conn.close();
        	}
        	
        } catch (SQLException e){
            log.error("进行公司销售额查询的时候出错(月报表):" + e);
        }
        return list;
    }
    
    /**
     * 得到经销商层次信息