📄 dealerprizequery.java
字号:
}
}
rs.close();
db.closeConn();
} catch (SQLException e){
log.error("查询应扣除的金额时出错:" + e);
}
return returnValue;
}
/**
* 得到欠费的经销商信息,里面包含他所欠的费用,此方法依赖于上面的方法
* @param dealerid 经销商的编号
* @param querydate 查询日期,格式2005-09月的样式
* @return List 返回一个集合,里面包含经销商的信息和他某个月所欠的费用
*/
public List getArrearDealerInfo(String dealerid,String querydate,int currentPage){
//将日期分开,用来进行查询
String[] splitDate = querydate.split("-");
int years = Integer.parseInt(splitDate[0]);
int months = Integer.parseInt(splitDate[1]);
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");
cstmt.setString(2,"Userid, name, falseName, userflag, sort, Buytime");
cstmt.setString(3,"userid");
cstmt.setInt(4,Dictionary.PAGESIZE);
cstmt.setInt(5,currentPage);
cstmt.setInt(6,1);
cstmt.setInt(7,1);
if("".equals(dealerid)){
cstmt.setString(8,"userid='" + dealerid + "'");
} else {
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()){
boolean haveRun = false; //判断是否已经进入到内部循环
Franchiser bean = new Franchiser();
String userid = rs.getString("userid");
bean.setUserid(userid);
bean.setName(rs.getString("name"));
bean.setFalseName(rs.getString("falseName"));
bean.setUserflag(rs.getString("userflag"));
int sort = rs.getInt("sort");
if(sort==1){
bean.setSortInfo("经销商");
} else {
bean.setSortInfo("准经销商");
}
String buytime = rs.getString("buytime");
bean.setBuytime(rs.getString("buytime"));
bean.setBuytime(Unit.getFormatDateNoDay(buytime));
bean.setBuytimeen(Unit.getFormatDateNoDayByEn(buytime));
//下面是统计扣钱,只有本月仍没还清钱,才会将此经销商信息加入到list表中
String sql = "SELECT * FROM t_userMoney where userid='" + dealerid + "' and years=" + years + " and months=" + months + " isflag=1";
ResultSet innerRs = db.getRs(sql);
while(innerRs.next()){
int moneyone = innerRs.getInt("MoneyOne");
int moneytwo = innerRs.getInt("MoneyTwo");
int moneyoneother = innerRs.getInt("MoneyOneOther");
int totalprize = moneyone + moneytwo + moneyoneother;
int innerWeeks = innerRs.getInt("weeks"); //得到所属周
if(totalprize<=55000){
//找出重复消费后面几个星期(也许没有后面的星期),所得到的奖金是否够还钱
sql = "select * from t_userMoney where userid='" + dealerid + "' and years=" + years + " and months=" + months + " and weeks>" + innerWeeks;
ResultSet totalRs = db.getRs(sql);
int totalInnerprize = 0;
while(totalRs.next()){
int totalMoneyone = totalRs.getInt("MoneyOne");
int totalMoneytwo = totalRs.getInt("MoneyTwo");
int totalMoneyoneother = totalRs.getInt("MoneyOneOther");
totalInnerprize = totalInnerprize + totalMoneyone + totalMoneytwo + totalMoneyoneother;
}
totalRs.close();
if((55000-totalprize)>totalInnerprize){
haveRun = true;
bean.setDeductRepeatConsumeY(Unit.getMoneyByY(totalInnerprize -(55000-totalprize)));
}
}
}
innerRs.close();
if(haveRun==true){
//只有当执行到了循环里面才能将此条记录加入到集合中
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 dealerID 经销商编号
* @param monthDate 得到日期,日期的格式可能是2005年09月,或是2005-09
* @param currentPage 当前页
* @param purview 经销商的权限
* @return 返回经销商奖金
*/
public List getDealerReturnPrize(String dealerID,String monthDate,int currentPage,String purview){
//得到年份和月份,先将日期统一变为2005-09的格式
if(monthDate.indexOf("月")!=-1){
monthDate = monthDate.replaceAll("月","").replaceAll("年","-");
}
//得到年份和月份
String[] yearMonth = monthDate.split("-");
int years = Integer.parseInt(yearMonth[0]);
int months = Integer.parseInt(yearMonth[1]);
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_Userturn_point_getmoney,t_usersign, t_userrelation");
cstmt.setString(2,"t_usersign.name, t_usersign.Userid, t_Userturn_point_getmoney.GetMoney,t_Userturn_point_getmoney.UpdateTime, 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("".equals(monthDate) && "".equals(dealerID)){
cstmt.setString(8,"t_Userturn_point_getmoney.Userid = t_usersign.Userid");
} else if("".equals(monthDate) && !"".equals(dealerID)){
cstmt.setString(8,"t_Userturn_point_getmoney.GetUserid = t_usersign.Userid and t_usersign.Userid='" + dealerID + "'");
} else if("".equals(dealerID) && !"".equals(monthDate)){
cstmt.setString(8,"t_Userturn_point_getmoney.GetUserid = t_usersign.Userid and SUBSTRING(CONVERT(char(10), UpdateTime, 121), 1, 7)='" + monthDate + "'");
} else if(!"".equals(dealerID) && !"".equals(monthDate)){
cstmt.setString(8,"t_Userturn_point_getmoney.GetUserid = t_usersign.Userid and SUBSTRING(CONVERT(char(10), UpdateTime, 121), 1, 7)='" + monthDate + "' and t_usersign.Userid='" + dealerID + "'");
}
//根据权限来进行查询,
if(purview.equals("0")){
//普通经销商
cstmt.setString(8,"t_Userturn_point_getmoney.GetUserid = t_usersign.Userid and t_usersign.Userid = t_userrelation.Userid and (t_Userturn_point_getmoney.GetUserid = '" + dealerID + "') AND (t_Userturn_point_getmoney.years = " + years + ") AND (t_Userturn_point_getmoney.months = " + months + ") and t_userrelation.purview=" + purview);
} else {
//管理员经销商
cstmt.setString(8,"t_Userturn_point_getmoney.GetUserid = t_usersign.Userid and t_usersign.Userid = t_userrelation.Userid AND (t_Userturn_point_getmoney.years = " + years + ") AND (t_Userturn_point_getmoney.months = " + months + ") 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("userid"));
bean.setName(rs.getString("name"));
bean.setReturnTotalMoneyY(Unit.getMoneyByY(rs.getInt("GetMoney")));
bean.setUpdateTime(Unit.getFormatDateNoDay(rs.getString("UpdateTime")));
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;
}
/**
* 得到经销商月返点奖金明细(和上一个方法不同之处在于,此方法求得的是一个月内某一个经销商所得返点奖金明细)
* @param dealerID 经销商编号
* @param monthDate 要查询的日期,格式为2005年09月
* @param currentPage 当前页
* @return 返回经销商奖金
*/
public List getDealerReturnPrizeDetail(String dealerID,String monthDate,int currentPage){
//将日期格式化,即将日期转化成2005-09的格式
if(monthDate.indexOf("月")!=-1){
monthDate = monthDate.replaceAll("月","").replaceAll("年","-");
}
//得到年份和月份
String[] yearMonth = monthDate.split("-");
int years = Integer.parseInt(yearMonth[0]);
int months = Integer.parseInt(yearMonth[1]);
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_Userturn_point,t_usersign,t_userrelation");
cstmt.setString(2,"t_usersign.name, t_Userturn_point.beginUserid, t_Userturn_point.div,t_Userturn_point.gettime, t_Userturn_point.getuserid, 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(!"".equals(dealerID) && !"".equals(monthDate)){
// cstmt.setString(8,"t_Userturn_point.getuserid = t_usersign.Userid and getuserid='" + dealerID + "' and SUBSTRING(CONVERT(char(10), gettime, 121), 1, 7)='" + monthDate + "'");
// }
//根据权限来进行查询,是另一种获取一个月内数据的方式,根据数据库中已经存在的年和月
cstmt.setString(8,"t_Userturn_point.getuserid = t_usersign.Userid and t_usersign.Userid = t_userrelation.Userid and (t_Userturn_point.getuserid = '" + dealerID + "') AND (t_Userturn_point.years = " + years + ") AND (t_Userturn_point.months = " + months + ")");
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.setAvgUserid(rs.getString("getUserid"));
bean.setName(rs.getString("name"));
bean.setBeginUserid(rs.getString("beginUserid"));
bean.setBeginDiv(rs.getInt("div"));
bean.setAvgTime(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();
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -