dealerprizequery.java

一个直销用的管理软件

        	
        } catch (SQLException e){
            log.error("进行经销商返点奖金查询(月奖金明细)的时候出错:" + e);
        }
        return list;
    }
    
    /**
     * 得到经销每天所得到的市场开发奖金,根据经销商权限的不同,所返回的结果也不同,其中权限为1,2的经销商是可以看到所有经销商每天得到的市场开
     * 发奖金的,而权限为0的用户只能看到本人每天所得到的市场开发奖金
     * 
     * @param dealerID 经销商编号
     * @param dayDate 查询日期
     * @param currentPage 当前页
     * @param purview 经销商的权限
     * @return 经销商每天所得到的市场开发奖金以及经销商的一些相关信息
     */
    public List getDealerPrizeMoneyOne(String dealerID,String dayDate,int currentPage,String purview){
    	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_usersign,t_UserMoneyOne,t_usersign t_usersign_1,t_userrelation,t_userrelation t_userrelation_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_userrelation.purview, t_userrelation.areaid, t_userrelation_1.areaid AS areaid1,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);
        	//根据权限来进行查询,如果权限是0(普通经销商权限),应该加上经销商限制条件,如果权限是1或2,则不加上经销商编号限制条件,但是两种情况下
        	//都应该加上权限限制条件,虽然普通经销商的情况下并不需要
        	if(purview.equals("0")){
        	    //普通经销商
        	    cstmt.setString(8,"t_usersign.Userid = t_UserMoneyOne.GetUserid and t_UserMoneyOne.Userid = t_usersign_1.Userid and t_usersign.Userid = t_userrelation.Userid and t_usersign_1.Userid = t_userrelation_1.Userid and (CONVERT(char(10), gettime, 121)='" + dayDate + "') and t_UserMoneyOne.GetUserid='" + dealerID + "' and t_userrelation.purview=" + purview);
        	} else {
        	    //管理员经销商
        	    cstmt.setString(8,"t_usersign.Userid = t_UserMoneyOne.GetUserid and t_UserMoneyOne.Userid = t_usersign_1.Userid and t_usersign.Userid = t_userrelation.Userid and t_usersign_1.Userid = t_userrelation_1.Userid and (CONVERT(char(10), gettime, 121) = '" + dayDate + "') and t_userrelation.purview<=" + purview);
            }
        	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"));
        	    bean.setAreaid(rs.getString("areaid"));
        	    bean.setAreaid1(rs.getString("areaid1"));
        	    //判断经销商的类型
            	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;
    }
    
    /**
     * 得到经销商市场拓展奖金,根据经销商权限的不同,所返回的结果也不同,其中权限为1,2的经销商是可以看到所有经销商每天得到的市场开
     * 拓奖金的,而权限为0的用户只能看到本人每天所得到的市场开拓奖金
     * 
     * @param dealerID 经销商编号
     * @param dayDate 时间
     * @param currentPage 当前页
     * @param purview 经销商权限
     * @return 返回经销商市场拓展奖金和经销商的一些相关信息
     */
    public List getDealerPrizeMoneyTwo(String dealerID,String dayDate,int currentPage,String purview){
    	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,t_userrelation");
        	cstmt.setString(2,"t_usersign.name, t_UserMoneyTwo.GetUserid, t_UserMoneyTwo.div,t_UserMoneyTwo.isadd, t_UserMoneyTwo.arearelation, t_UserMoneyTwo.Getnumber,t_UserMoneyTwo.twomoney, t_UserMoneyTwo.Gettime, t_userrelation.purview,t_userrelation.areaid,t_usersign.sort");
        	cstmt.setString(3,"ID");
        	cstmt.setInt(4,Dictionary.PAGESIZE);
        	cstmt.setInt(5,currentPage);
        	cstmt.setInt(6,1);
        	cstmt.setInt(7,1);
        	//根据权限来进行查询,如果权限是0(普通经销商权限),应该加上经销商限制条件,如果权限是1或2,则不加上经销商编号限制条件,但是两种情况下
        	//都应该加上权限限制条件,虽然普通经销商的情况下并不需要
        	if(purview.equals("0")){
        	    //普通经销商
        	    cstmt.setString(8,"t_UserMoneyTwo.GetUserid = t_usersign.Userid and t_UserMoneyTwo.GetUserid = t_userrelation.Userid and (CONVERT(char(10), gettime, 121) = '" + dayDate + "') and t_UserMoneyTwo.GetUserid='" + dealerID + "' and t_userrelation.purview=" + purview);
        	} else {
        	    //管理员经销商
        	    cstmt.setString(8,"t_UserMoneyTwo.GetUserid = t_usersign.Userid and t_UserMoneyTwo.GetUserid = t_userrelation.Userid and (CONVERT(char(10), gettime, 121) = '" + dayDate + "') and t_userrelation.purview<=" + purview);
            }
        	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"));
        	    int isadd = rs.getInt("isadd");
        	    String arearelation = rs.getString("arearelation");
        	    //下面是对补单的情况进行处理,如果是补单产生的则区域关系以黑色的正常字体显示,如果不是补单的情况产生,以如下的规则来处理
        	    //两个字母全变红,三个或四个字母都将最后一个字母变红
        	    if(isadd==0){
        	    	if(arearelation.length()==2){
        	    		bean.setMoneyTwoArearelation("<font color='red'>" + arearelation + "</font>");
        	    	} else if(arearelation.length()==3 || arearelation.length()==4){
        	    		bean.setMoneyTwoArearelation(arearelation.substring(0,arearelation.length()-1) + "<font color='red'>" + arearelation.substring(arearelation.length()-1,arearelation.length()) + "</font>");
        	    	}
        	    } else if(isadd==1){
        	    	bean.setMoneyTwoArearelation(arearelation);
        	    }
        	    bean.setMoneyTwoTwomoneyY(Unit.getMoneyByY(rs.getInt("twomoney")));
        	    bean.setMoneyTwoGettime(Unit.getFormatDate(rs.getString("gettime")));
        	    bean.setAreaid(rs.getString("areaid"));
        	    //判断经销商的类型
            	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;
    }
    
    /**
     * 得到推荐经销商所得奖金,根据经销商权限的不同,所返回的结果也不同,其中权限为1,2的经销商是可以看到所有经销商每天得到的推荐经销商
     * 奖金的,而权限为0的用户只能看到本人每天所得到的推荐经销商奖金
     * 
     * @param dealerID 经销商编号
     * @param monthDate 时间
     * @param currentPage 当前页
     * @return 返回推荐经销商奖金,和此经销商的一些相关信息
     */
    public List getDealerPrizeMoneyOneOther(String dealerID,String dayDate,int currentPage,String purview){
    	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_usersign,t_UserMoneyOne_other,t_usersign t_usersign_1,t_userrelation");
        	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_userrelation.purview, 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);
        	if("".equals(dayDate) && "".equals(dealerID)){
        	    cstmt.setString(8,"t_UserMoneyOne_other.GetUserid = t_usersign.Userid and t_UserMoneyOne_other.Userid = t_usersign_1.Userid");
        	} else if("".equals(dayDate) && !"".equals(dealerID)){
        	    cstmt.setString(8,"t_UserMoneyOne_other.GetUserid = t_usersign.Userid and t_UserMoneyOne_other.Userid = t_usersign_1.Userid and t_UserMoneyOne_other.GetUserid='" + dealerID + "'");
        	} else if("".equals(dealerID) && !"".equals(dayDate)){
        	    cstmt.setString(8,"t_UserMoneyOne_other.GetUserid = t_usersign.Userid and t_UserMoneyOne_other.Userid = t_usersign_1.Userid and CONVERT(char(10), gettime, 121)='" + dayDate + "'");
        	} else if(!"".equals(dealerID) && !"".equals(dayDate)){
        	    cstmt.setString(8,"t_UserMoneyOne_other.GetUserid = t_usersign.Userid and t_UserMoneyOne_other.Userid = t_usersign_1.Userid and CONVERT(char(10), gettime, 121)='" + dayDate + "' and t_UserMoneyOne_other.GetUserid='" + dealerID + "'");
        	}
        	//根据权限来进行查询,如果权限是0(普通经销商权限),应该加上经销商限制条件,如果权限是1或2,则不加上经销商编号限制条件,但是两种情况下
        	//都应该加上权限限制条件,虽然普通经销商的情况下并不需要
        	if(purview.equals("0")){
        	    //普通经销商
        	    cstmt.setString(8,"t_UserMoneyOne_other.GetUserid = t_usersign.Userid and t_UserMoneyOne_other.Userid = t_usersign_1.Userid and t_usersign.Userid = t_userrelation.Userid and (CONVERT(char(10), gettime, 121) = '" + dayDate + "') and t_UserMoneyOne_other.GetUserid='" + dealerID + "' and t_userrelation.purview=" + purview);
        	} else {
        	    //管理员经销商
        	    cstmt.setString(8,"t_UserMoneyOne_other.GetUserid = t_usersign.Userid and t_UserMoneyOne_other.Userid = t_usersign_1.Userid and t_usersign.Userid = t_userrelation.Userid and (CONVERT(char(10), gettime, 121) = '" + dayDate + "') and t_userrelation.purview<=" + purview);
            }
        	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;
    }
    
    /**
     * 得到经销每周所得的市场开发奖金明细(和getDealerPrizeMoneyOne方法不同之处在于,此方法得到的是一周的数据,而不是一天的)
     * @param dealerID 经销商编号
     * @param years 年份
     * @param weeks 属于一年中的哪一周