dealerprizequery.java

一个直销用的管理软件

package com.susssoft.richjl.operation;

import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.CallableStatement;
import java.sql.SQLException;
import org.apache.log4j.Logger;
import com.susssoft.richjl.persistence.Franchiser;
import com.susssoft.richjl.dboperation.DBConnectionManager;
import com.susssoft.richjl.common.Pager;
import com.susssoft.richjl.common.Unit;
import com.susssoft.richjl.operation.Dictionary;
import java.util.ArrayList;
import java.util.List;

/**
 * 此类中定义了经销商查询的所有方法,包括,每天奖金查询,每周奖金查询,每月奖金查询等
 * @author 付祖远
 */
public class DealerPrizeQuery {
    private static final Logger log = Logger.getLogger(DealerPrizeQuery.class);
    public Pager pager = null;
    public int currentPageSize = 0; //当前页的显示数
    
    /**
     * 得到经销商每天重复消费的奖金,根据经销商编号的不同将会有两种情况产生,如果是公司查看的话,将会显示所有
     * 的经销商每天重复消费的奖金,如果是一般经销商的话,将只能看到自己每天重复消费的奖金,另外只能查看今天(包括)
     * 以前的每天消费的金额(前台加脚本控制)
     * @return 返回一个记录集,包括所有消费的金额列表
     */
    public List getDealerRepeatConsume(String dealerID,String consumeDate,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_UserRepeatConsume,t_usersign,t_userrelation");
        	cstmt.setString(2,"t_usersign.name, t_UserRepeatConsume.ConsumeMoney,t_UserRepeatConsume.ConsumeUserid, t_UserRepeatConsume.ConsumeDate,t_usersign.sort, t_userrelation.purview");
        	cstmt.setString(3,"ID");
        	cstmt.setInt(4,Dictionary.PAGESIZE);
        	cstmt.setInt(5,currentPage);
        	cstmt.setInt(6,1);
        	cstmt.setInt(7,1);
        	//根据权限来判断显示的数据
        	if(purview.equals("0")){
        		cstmt.setString(8,"(CONVERT(char(10), t_UserRepeatConsume.ConsumeDate, 121) = '" + consumeDate + "') AND t_UserRepeatConsume.ConsumeUserid = t_usersign.Userid AND t_usersign.Userid = t_userrelation.Userid AND (t_UserRepeatConsume.ConsumeUserid = '" + dealerID + "') AND (t_userrelation.purview = " + purview + ")");
        	} else {
        		cstmt.setString(8,"(CONVERT(char(10), t_UserRepeatConsume.ConsumeDate, 121) = '" + consumeDate + "') AND t_UserRepeatConsume.ConsumeUserid = t_usersign.Userid AND t_usersign.Userid = t_userrelation.Userid 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.setUserid(rs.getString("ConsumeUserid"));
        	    bean.setName(rs.getString("name"));
        	    bean.setConsumeMoneyY(Unit.getMoneyByY(rs.getInt("ConsumeMoney")));
        	    bean.setConsumeDate(Unit.getFormatDate(rs.getString("ConsumeDate")));
        	    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;
    }
    
    /**
     * 得到经销商相关表中的所有存在日期,即将表中的日期进行汇总,然后进行显示,可能会出现的情况是,日期不是连着的,因为数据库中可能没有那天的记录
     * 注:这是一个公用方法,以传入的sql语句来区别
     * @return 返回日期列表
     */
    public List getDealerDate(String sql){
        DBConnectionManager db = new DBConnectionManager();
        ResultSet rs = db.getRs(sql);
        List list = new ArrayList();
        try{
            while(rs.next()){
                list.add(rs.getString("date"));
            }
        } catch(SQLException e){
            log.error("进行日期汇总的时候出错:" + e);
        }
        return list;
    }
    
    /**
     * 得到经销商每周得到的奖金,包含三种奖金的汇总(开发,拓展,推荐),根据权限的不同产生的列表信息也将不同,purview是一个整数,代表的值越大,表明
     * 它的权限越高
     * 
     * @param dealerID 经销商编号(当经销商编号为空时,查询的是所有的经销商周奖金)
     * @param consumeDate 消费日期
     * @param currentPage 当前页
     * @param purview 经销商权限
     * @return 周奖金列表
     */
    public List getDealerWeekPrize(String dealerID,int years,int weeks,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_userMoney,t_usersign,t_userrelation");
        	cstmt.setString(2,"t_usersign.name, t_usersign.Userid, t_userMoney.MoneyOne,t_userMoney.MoneyTwo, t_userMoney.MoneyOneOther, t_userMoney.years,t_userMoney.weeks, t_userMoney.months, t_userMoney.gettime,t_userMoney.IsSend, t_userMoney.isflag, t_userrelation.purview,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_userMoney.Userid = t_usersign.Userid and t_usersign.Userid = t_userrelation.Userid  and t_userMoney.years=" + years + " and weeks=" + weeks + " and t_userMoney.userid='" + dealerID + "' and t_userrelation.purview=0");
        	} else {
        	    //管理员经销商
        	    cstmt.setString(8,"t_userMoney.Userid = t_usersign.Userid and t_usersign.Userid = t_userrelation.Userid  and t_userMoney.years=" + years + " and weeks=" + weeks + " 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();
        	    String userid = rs.getString("userid");
        	    bean.setUserid(userid);
        	    bean.setName(rs.getString("name"));
        	    int moneyone = rs.getInt("moneyone");
        	    int moneyOneOther = rs.getInt("MoneyOneOther");
        	    int moneytwo = rs.getInt("moneytwo");
        	    bean.setMoneyOneY(Unit.getMoneyByY(moneyone));
        	    bean.setMoneyOneOtherY(Unit.getMoneyByY(moneyOneOther));
        	    bean.setMoneyTwoY(Unit.getMoneyByY(moneytwo));
        	    bean.setWeekTotalMoney(Unit.getMoneyByY(moneyone+moneyOneOther+moneytwo));
        	    bean.setYears(rs.getInt("years"));
        	    bean.setWeeks(rs.getInt("weeks"));
        	    bean.setMonths(rs.getInt("months"));
        	    bean.setWeekTime(Unit.getFormatDate(rs.getString("gettime")));
        	    int isSend = rs.getInt("IsSend");
        	    if(isSend==1){
        	        //奖金已经发送,将选项框置为不可用,并为选中状态
        	        bean.setIsSendStr("<input type='checkbox' value='" + userid + "' name='isSend' checked disabled>");
        	    } else if(isSend ==0){
        	        //奖金未发送,则将选项框置为可用
        	        bean.setIsSendStr("<input type='checkbox' value='" + userid + "' name='isSend'>");
        	    }
        	    int isFlag = rs.getInt("isflag");
        	    bean.setDeductRepeatConsumeY(Unit.getMoneyByY(moneyone + moneyOneOther + moneytwo - this.getDeductPrize(userid,years,weeks,isFlag)));
        	    if(rs.getInt("sort")==1){
            	    bean.setSortType("经销商");
            	} else {
            	    bean.setSortType("准经销商");
            	}
        	    //当前页的显示数
        	    this.currentPageSize = this.currentPageSize + 1;
        	    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;
    }
    
    /**
     * 下面的代码的目的是:根据某个星期,某一年,找出所属的月份,二,在根据经销商的编号,找出此经销商在这一月的四周中是否有重重消费,然后判断奖金
     * 是否够发扣除重复消费的金额550,如果这一月的第二周不够扣,那么累积到下一周去扣,如果到月底仍不够扣,那么将累积到下一个月去扣,满足一月都不够
     * 扣的情况,将显示在一个新的页面中
     * @param dealerID 经销商编号
     * @param years 年份
     * @param weeks 星期
     * @param isflag 是否重复消费
     * @param consumeid 每周奖金的编号
     * @return 返回应该扣除的金额,扣除的金额包含两种可能,一种直接扣除550,另一种扣除上回不够扣的钱
     */
    public int getDeductPrize(String dealerID,int years,int weeks,int isflag){
    	//定义返回变量
    	int returnValue = 0;
    	
    	//根据星期和年份,得到月份
    	String months = this.getMonthByYearWeeks(years,weeks);
    	
    	//先判断此月份是否有重复消费的权力,isflag变量的值为1,如果为0则直接退出程序
    	String sql = "SELECT count(*) as totalCount FROM t_userMoney WHERE (years = " + years + ") AND (months = " + months + ") AND (Userid = '" + dealerID + "') AND isflag=1";
    	DBConnectionManager db = new DBConnectionManager();
    	ResultSet rs = db.getRs(sql);
    	try{
    		while(rs.next()){
    			if(rs.getInt("totalCount")==0){
    				//记录数为0,说明根本没有进行重复消费
    				rs.close();
    				db.closeConn();
    				return returnValue;
    			}
    		}
    		rs.close();
    		
    		//下面的情况,都说明这个月有重复消费的情况,如果本月重复消费的那周重复消费金额小于那周的奖金,则除了重复消费的那周返回55000,其它周都返回0
    		//找到重复消费的那一周
    		sql = "SELECT * FROM t_userMoney WHERE (years = " + years + ") AND (months = " + months + ") AND (Userid = '" + dealerID + "') AND isflag=1";
    		rs = db.getRs(sql);
    		if(rs.next()){
    			int moneyone = rs.getInt("MoneyOne");
    			int moneytwo = rs.getInt("MoneyTwo");
    			int moneyoneother = rs.getInt("MoneyOneOther");
    			int total = moneyone + moneytwo + moneyoneother;
    			int repeatConsumeweeks = rs.getInt("weeks"); //重复消费周
    			if(total>=55000){
    				//合法消费55000,即不存在奖金不够消费
    				if(isflag==1){
    					returnValue = 55000;
    				} else {
    					returnValue = 0;
    				}
    			} else {
    				//存在负金额的情况
    				if(isflag==1){
    					returnValue = 55000;
    				} else if(weeks<repeatConsumeweeks){
    					//在重复消费以前的周,不存在扣奖金
    					returnValue = 0;
    				} else if(weeks>repeatConsumeweeks){
    					//重复消费周到查询周前一周内的奖金和
    					int innerTotal = 0;
    					
    					//得到重复消费周到查询周的前一周的金额信息,如果这几周的总金额还是小于550元,说明到查询周还是没有还清金额
						sql = "SELECT * FROM t_userMoney WHERE (years = " + years + ") AND (weeks >= " + repeatConsumeweeks + ") and weeks< " + weeks + " AND (Userid = '" + dealerID + "')";
						ResultSet innerRs = db.getRs(sql);
						while(innerRs.next()){
							innerTotal = innerTotal + innerRs.getInt("moneyone") + innerRs.getInt("MoneyTwo") + innerRs.getInt("MoneyOneOther");
			    			if(innerTotal >=55000){
			    				//在查询周以前,已经将奖金还清
			    				returnValue = 0;
			    			} else {
			    				//到查询周时,仍未还清奖金
			    				returnValue = 55000 - innerTotal;
			    			}
						}
						innerRs.close();	
    				}