dealerprizequery.java

一个直销用的管理软件

     * @param dealerID 要查询的经销商
     * @return 一个数组,包含上一级的一个经销商,和同级的其它三个经销商,如果有三个的话
     */
    public String[] getDealerDivInfo(String dealerID){
    	//得到经销商所在层次
    	int div = this.getDealerDiv(dealerID);
    	
    	DBConnectionManager oper = new DBConnectionManager();
        ResultSet rs = oper.getRs("SELECT upUserid FROM v_user WHERE (Userid = '" + dealerID + "')");
        String str[] = {"无","无","无","无","无"};
        try{
            while(rs.next()){
                str[0] = rs.getString("upUserid");
                break;
            }
            rs.close();
        } catch (SQLException e){
            log.error("得到指定经销商上级经销商时出错:" + e);
        }
        oper.closeConn();
        
        oper = new DBConnectionManager();
        rs = oper.getRs("SELECT userid FROM v_user WHERE (upUserid = '" + str[0] + "')");
        int i = 1;
        try{
            while(rs.next()){
                str[i++] = rs.getString("userid");
            }
            rs.close();
        } catch (SQLException e){
            log.error("得到指定经销商同级经销商时出错:" + e);
        }
        oper.closeConn();
        
        if(str[0].equals("-1")){
        	str[0] = "无";
        }
        
        //在此处将页面代码写出来
        for(int j=1;j<str.length;j++){
        	if(!str[j].equals("无")){
        		str[j] = "<a href='divinfo.jsp?dealerID=" + str[j] + "&div=" + div + "'>" + str[j] + "</a>";
        	} else {
        		str[j] = "无";
        	}
        }
        return str;
    }
    
    /**
     * 得到经销商层次
     * @param dealerID 经销商编号
     * @return 经销商层次
     */
    public int getDealerDiv(String dealerID){
    	int div = 0;
    	DBConnectionManager oper = new DBConnectionManager();
        ResultSet rs = oper.getRs("SELECT div FROM v_user WHERE (Userid = '" + dealerID + "')");
        try{
        	while(rs.next()){
        		div = rs.getInt("div");
        	}
        } catch (SQLException e){
        	log.error("得到经销商层次时出错(父级经销商层次):" + e);
        }
        oper.closeConn();
        return div;
    }
    
    /**
     * 得到经销商下面的四个经销商
     * @param dealerID 要查询的经销商
     * @param div 父经销商的层次
     * @return 一个数组,查询经销商下面的四个经销商
     */
    public String[] getDownDealerDiv(String dealerID,int div){  
    	//得到当前经销商层次
    	int currentDiv = this.getDealerDiv(dealerID);
    	//层距
    	int divDis = currentDiv - div;
    	
    	DBConnectionManager oper = new DBConnectionManager();
        ResultSet rs = oper.getRs("SELECT userid FROM v_user WHERE (upUserid = '" + dealerID + "')");
        String[] str = {dealerID,"无","无","无","无"};
        int i = 1;
        try{
            while(rs.next()){
                str[i++] = rs.getString("userid");
            }
            rs.close();
        } catch (SQLException e){
            log.error("得到指定经销商同级经销商时出错:" + e);
        }
        oper.closeConn();
        
        //在此处将页面代码写出来
        for(int j=1;j<str.length;j++){
        	if(!str[j].equals("无") && divDis == 7){
        		str[j] = "<a href='#' onclick=alert('您最多只能查看下面八层的信息!')>" + str[j] + "</a>";
        	} else if(!str[j].equals("无") && divDis != 1){
        		str[j] = "<a href='divinfo.jsp?dealerID=" + str[j] + "&div=" + div + "'>" + str[j] + "</a>";
        	} else {
        		str[j] = "无";
        	}
        }
        
        return str;
    }
    
    /**
     * 得到经销商列表，和getDealerList方法不同之处在于，它涉及到了分页，取出的数据是分页后的数据
     * @return 返回一个集合，里面包含了所有的经销商
     */
    public List getDealerListByPager(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,"v_user");
        	cstmt.setString(2,"userid,name,falsename,sex,pwd,sort,buytime");
        	cstmt.setString(3,"userid");
        	cstmt.setInt(4,Dictionary.PAGESIZE);
        	cstmt.setInt(5,currentPage);
        	cstmt.setInt(6,1);
        	cstmt.setInt(7,1);
        	cstmt.setString(8,"");
        	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.setUserid(rs.getString("userid"));
        	    bean.setName(rs.getString("name"));
        	    bean.setFalseName(rs.getString("falseName"));
        	    int sex = rs.getInt("sex");
        	    if(sex==1){
        	        bean.setSexInfo("男");
        	    } else {
        	        bean.setSexInfo("女");
        	    }
        	    bean.setPwd(rs.getString("pwd"));
        	    int sort = rs.getInt("sort");
        	    if(sort==1){
        	        bean.setSortInfo("经销商");
        	    } else {
        	        bean.setSortInfo("准经销商");
        	    }
        	    bean.setBuytime(Unit.getFormatDate(rs.getString("buytime")));
        	    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;
    }
    
    /**
     * 根据年份和星期(一年的第几周)得到当前的时间,返回结果:2005年09月的类型
     * @param years 年
     * @param weeks 第几周
     * @return 返回查询的日期
     */
    public String getWeekDateByYearWeeks(int years,int weeks){
    	String returnValue = "";
    	DBConnectionManager db = new DBConnectionManager();
    	ResultSet rs = db.getRs("select years,months from t_userMoney where years=" + years + " and weeks=" + weeks);
    	try{
    		while(rs.next()){
    			returnValue = String.valueOf(rs.getInt("years"));
    			String months = String.valueOf(rs.getInt("months"));
    			if(months.length()==1){
    				returnValue = returnValue + "年0" + months + "月";
    			} else {
    				returnValue = returnValue + "年" +  months + "月";
    			}
    		}
    		rs.close();
    	} catch(SQLException e){
    		log.error("根据年,周查询日期的时候出错:" + e);
    	}
    	db.closeConn();
    	
    	if("".equals(returnValue)){
    		returnValue = Unit.getCurrentTimerNoDayChina();
    	}
    	return returnValue;
    }
    
    /**
     * 根据年份和星期(一年的第几周)得到属于哪个月
     * @param years 年
     * @param weeks 第几周
     * @return 返回查询的日期
     */
    public String getMonthByYearWeeks(int years,int weeks){
    	String months = "";
    	DBConnectionManager db = new DBConnectionManager();
    	ResultSet rs = db.getRs("select months from t_userMoney where years=" + years + " and weeks=" + weeks);
    	try{
    		while(rs.next()){
    			months = String.valueOf(rs.getInt("months"));
    		}
    		rs.close();
    	} catch(SQLException e){
    		log.error("根据年份和星期(一年的第几周)得到属于哪个月时出错:" + e);
    	}
    	db.closeConn();
    	return months;
    }
    
    /**
     * 根据日期(年份和月份)得到经销商的欠款信息,它得到的是所有欠款信息
     * @param oldDate 查询的日期
     * @return 返回经销商的欠款信息集合
     */
    public List getArrearageInfo(String oldDate){
    	//存储返回信息
    	List list = new ArrayList();
    	
    	//截取日期,取出年和月
    	String[] arr = oldDate.split("-");
    	int years = Integer.parseInt(arr[0]);
    	int months = Integer.parseInt(arr[1]);
    	
    	DBConnectionManager db = new DBConnectionManager();

    	//得到所有要查询的经销商,但最后显示可能只有几个
    	String sql = "SELECT Userid FROM t_userMoney GROUP BY Userid";
    	
    	ResultSet rs = db.getRs(sql);
    	try{
    		while(rs.next()){
    			//得到经销商的一些相关信息
    			String userid = rs.getString("userid");
    			
    			//找到要重复消费的那一周
    			sql = "select weeks from t_userMoney where isflag=1 and userid='" + rs.getString("userid") + "' and years=" + years + " and months=" + months;
    			ResultSet innerRs = db.getRs(sql);
    			if(innerRs.next()){
    				//得到重复消费周
    				int repeatWeek = innerRs.getInt("weeks");
    				
    				//如果有重复消费,则查询出此周(包含此周以后的所有奖金)
    				sql = "select moneyone,moneytwo,moneyoneother from t_userMoney where userid='" + rs.getString("userid") + "' and years=" + years + " and months=" + months + " and weeks>=" + repeatWeek;
    				ResultSet weeksRs = db.getRs(sql);
    				//重复消费周以后应该得到的总奖金
    				int totalPrize = 0;
    				while(weeksRs.next()){
    					totalPrize = weeksRs.getInt("moneyone") + weeksRs.getInt("moneytwo") + weeksRs.getInt("moneyoneother");
    				}
    				weeksRs.close();
    				
    				//如果总奖金大于或等于550元,说明可以还清,否则应该返回记录
    				if(totalPrize < 55000){
    					Franchiser bean = new Franchiser();
    					Dealer dealerOper = new Dealer();
    	    			List dealerInfo = dealerOper.getDealerInfo(userid);
    	    			bean.setUserid(userid);
    	                bean.setName(dealerInfo.get(0).toString());