@RequestMapping("/list")
public ModelAndView list(
@RequestParam(value = "flowno", required = false) String flowno,
@RequestParam(value = "lotno", required = false) String lotno,
@RequestParam(value = "agencyno", required = false) String agencyno,
@RequestParam(value = "batchcode", required = false) String batchcode,
@RequestParam(value = "channel", required = false) String channel,
@RequestParam(value = "subchannel", required = false) String subchannel,
@RequestParam(value = "starttime", required = false) String starttime,
@RequestParam(value = "endtime", required = false) String endtime,
@RequestParam(value = "usertype", required = false) String usertype,
@RequestParam(value = "userid", required = false) String userid,
@RequestParam(value = "prize", required = false) String prize,
@RequestParam(value = "state", required = false) BigDecimal state,
@RequestParam(value = "bettype", required = false) BigDecimal bettype,
@RequestParam(value = "sellstarttime", required = false) String sellstarttime,
@RequestParam(value = "sellendtime", required = false) String sellendtime,
@ModelAttribute("page") Page<Tlot> page,
ModelAndView view) {
logger.info("tlots/list");
try {
StringBuilder builder = new StringBuilder(" where");
List<Object> params = new ArrayList<Object>();
if (!StringUtil.isEmpty(flowno)) {
builder.append(" o.flowno=? and");
params.add(flowno);
} else {
if (!StringUtil.isEmpty(lotno)) {
builder.append(" o.lotno=? and");
params.add(lotno);
}
if (!StringUtil.isEmpty(agencyno)) {
builder.append(" o.agencyno=? and");
params.add(agencyno);
}
if (!StringUtil.isEmpty(batchcode)) {
builder.append(" o.batchcode=? and");
params.add(batchcode);
}
if (!StringUtil.isEmpty(channel)) {
builder.append(" o.channel=? and");
params.add(channel);
}
if (!StringUtil.isEmpty(subchannel)) {
builder.append(" o.subchannel=? and");
params.add(subchannel);
}
if (!StringUtil.isEmpty(starttime)) {
builder.append(" to_char(o.ordertime, 'yyyy-mm-dd') >= ? and");
params.add(starttime);
}
if (!StringUtil.isEmpty(endtime)) {
builder.append(" to_char(o.ordertime, 'yyyy-mm-dd') <= ? and");
params.add(endtime);
}
if (!StringUtil.isEmpty(sellstarttime)) {
builder.append(
" o.lotcenterordertime is not null and to_char(o.lotcenterordertime, 'yyyy-mm-dd') >= ? and");
params.add(sellstarttime);
view.addObject("sellstarttime", sellstarttime);
}
if (!StringUtil.isEmpty(sellendtime)) {
builder.append(
" o.lotcenterordertime is not null and to_char(o.lotcenterordertime, 'yyyy-mm-dd') <= ? and");
params.add(sellendtime);
}
if (!StringUtil.isEmpty(usertype) && !StringUtil.isEmpty(userid)) {
Tuserinfo tuserinfo = null;
try {
if (Const.Tuserinfo_Mobileid.equals(usertype)) {
tuserinfo = Tuserinfo.findTuserinfoesByMobileid(userid, Const.Subchannel);
} else if (Const.Tuserinfo_Email.equals(usertype)) {
tuserinfo = Tuserinfo.findTuserinfoesByEmail(userid, Const.Subchannel);
} else if (Const.Tuserinfo_UserName.equals(usertype)) {
tuserinfo = Tuserinfo.findTuserinfoesByUserName(userid, Const.Subchannel);
}
} catch (Exception exception) {
throw new RuntimeException("用户不存在");
}
if (null == tuserinfo) {
throw new RuntimeException("用户不存在");
}
builder.append(" o.userno=? and");
params.add(tuserinfo.getUserno());
view.addObject("usertype", usertype);
view.addObject("userid", userid);
}
if (!StringUtil.isEmpty(prize)) {
if ("yes".equals(prize)) {
builder.append(" o.prizeamt > 0 and");
}
if ("no".equals(prize)) {
builder.append(" o.prizeamt = 0 and");
}
view.addObject("prize", prize);
}
if (null != state) {
if (new BigDecimal(-1).equals(state)) {
builder.append(" o.state = -1 and");
}
if (BigDecimal.ZERO.equals(state)) {
builder.append(" o.state = 0 and");
}
if (BigDecimal.ONE.equals(state)) {
builder.append(" o.state = 1 and");
}
if (new BigDecimal(2).equals(state)) {
builder.append(" o.state = 2 and");
}
view.addObject("state", state);
}
if (null != bettype) {
if (BetType.touzhu.value().equals(bettype)) {
builder.append(" o.bettype = 2 and");
}
if (BetType.hemai.value().equals(bettype)) {
builder.append(" o.bettype = 3 and");
}
if (BetType.taocan.value().equals(bettype)) {
builder.append(" o.bettype = 1 and");
}
if (BetType.zengsong.value().equals(bettype)) {
builder.append(" (o.bettype = 4 or o.bettype = 5) and");
}
if (BetType.zhuihao.value().equals(bettype)) {
builder.append(" o.bettype = 0 and");
}
}
}
if (builder.toString().endsWith("and")) {
builder.delete(builder.length() - 3, builder.length());
}
if (builder.toString().endsWith("where")) {
builder.delete(builder.length() - 5, builder.length());
}
if (!StringUtil.isEmpty(builder.toString()))
Tlot.findList(builder.toString(), " order by o.ordertime desc", params, page);
view.addObject("page", page);
} catch (Exception e) {
logger.error("tlots/list error", e);
view.addObject("errormsg", e.getMessage());
}
view.setViewName("tlots/list");
return view;
}
// @RequestMapping("/generateReport")
public void generateReport(
@RequestParam(value = "flowno", required = false) String flowno,
@RequestParam(value = "lotno") String lotno,
@RequestParam(value = "batchcode") String batchcode,
@RequestParam(value = "channel", required = false) String channel,
@RequestParam(value = "subchannel", required = false) String subchannel,
@RequestParam(value = "starttime", required = false) String starttime,
@RequestParam(value = "endtime", required = false) String endtime,
@RequestParam(value = "usertype", required = false) String usertype,
@RequestParam(value = "userid", required = false) String userid,
@RequestParam(value = "prize", required = false) String prize,
@RequestParam(value = "state", required = false) BigDecimal state,
@RequestParam(value = "bettype", required = false) BigDecimal bettype,
@RequestParam(value = "sellstarttime", required = false) String sellstarttime,
@RequestParam(value = "sellendtime", required = false) String sellendtime,
HttpServletRequest request,
HttpServletResponse response)
throws IOException {
OutputStream os = null;
WritableWorkbook wwb = null;
try {
StringBuilder builder = new StringBuilder(" where");
List<Object> params = new ArrayList<Object>();
if (!StringUtil.isEmpty(flowno)) {
builder.append(" o.flowno=? and");
params.add(flowno);
} else {
if (!StringUtil.isEmpty(lotno)) {
builder.append(" o.lotno=? and");
params.add(lotno);
}
if (!StringUtil.isEmpty(batchcode)) {
builder.append(" o.batchcode=? and");
params.add(batchcode);
}
if (!StringUtil.isEmpty(channel)) {
builder.append(" o.channel=? and");
params.add(channel);
}
if (!StringUtil.isEmpty(subchannel)) {
builder.append(" o.subchannel=? and");
params.add(subchannel);
}
if (!StringUtil.isEmpty(starttime)) {
builder.append(" to_char(o.ordertime, 'yyyy-mm-dd') >= ? and");
params.add(starttime);
}
if (!StringUtil.isEmpty(endtime)) {
builder.append(" to_char(o.ordertime, 'yyyy-mm-dd') <= ? and");
params.add(endtime);
}
if (!StringUtil.isEmpty(sellstarttime)) {
builder.append(
" o.lotcenterordertime is not null and to_char(o.lotcenterordertime, 'yyyy-mm-dd') >= ? and");
params.add(sellstarttime);
}
if (!StringUtil.isEmpty(sellendtime)) {
builder.append(
" o.lotcenterordertime is not null and to_char(o.lotcenterordertime, 'yyyy-mm-dd') <= ? and");
params.add(sellendtime);
}
if (!StringUtil.isEmpty(usertype) && !StringUtil.isEmpty(userid)) {
Tuserinfo tuserinfo = null;
try {
if (Const.Tuserinfo_Mobileid.equals(usertype)) {
tuserinfo = Tuserinfo.findTuserinfoesByMobileid(userid, Const.Subchannel);
} else if (Const.Tuserinfo_Email.equals(usertype)) {
tuserinfo = Tuserinfo.findTuserinfoesByEmail(userid, Const.Subchannel);
} else if (Const.Tuserinfo_UserName.equals(usertype)) {
tuserinfo = Tuserinfo.findTuserinfoesByUserName(userid, Const.Subchannel);
}
} catch (Exception exception) {
throw new RuntimeException("用户不存在");
}
if (null == tuserinfo) {
throw new RuntimeException("用户不存在");
}
builder.append(" o.userno=? and");
params.add(tuserinfo.getUserno());
}
if (!StringUtil.isEmpty(prize)) {
if ("yes".equals(prize)) {
builder.append(" o.prizeamt > 0 and");
}
if ("no".equals(prize)) {
builder.append(" o.prizeamt = 0 and");
}
}
if (null != state) {
if (new BigDecimal(-1).equals(state)) {
builder.append(" o.state = -1 and");
}
if (BigDecimal.ZERO.equals(state)) {
builder.append(" o.state = 0 and");
}
if (BigDecimal.ONE.equals(state)) {
builder.append(" o.state = 1 and");
}
if (new BigDecimal(2).equals(state)) {
builder.append(" o.state = 2 and");
}
}
if (null != bettype) {
if (BetType.touzhu.value().equals(bettype)) {
builder.append(" o.bettype = 2 and");
}
if (BetType.hemai.value().equals(bettype)) {
builder.append(" o.bettype = 3 and");
}
if (BetType.taocan.value().equals(bettype)) {
builder.append(" o.bettype = 1 and");
}
if (BetType.zengsong.value().equals(bettype)) {
builder.append(" (o.bettype = 4 or o.bettype = 5) and");
}
if (BetType.zhuihao.value().equals(bettype)) {
builder.append(" o.bettype = 0 and");
}
}
}
if (builder.toString().endsWith("and")) {
builder.delete(builder.length() - 3, builder.length());
}
if (builder.toString().endsWith("where")) {
builder.delete(builder.length() - 5, builder.length());
}
List<Tlot> list = Tlot.findList(builder.toString(), " order by o.ordertime desc", params);
os = response.getOutputStream();
wwb = Workbook.createWorkbook(os);
response.setContentType("application/dowload");
response.setHeader(
"Content-disposition",
"attachment;filename=\""
+ new String(
(java.net.URLEncoder.encode("彩票信息报表.xls", "UTF-8")).getBytes("UTF-8"), "GB2312")
+ "\"");
// Workbook的createSheet方法有两个参数，第一个是工作表的名称，第二个是工作表在工作薄中的位置
WritableSheet ws = wwb.createSheet("sheet1", 0);
ws.addCell(new Label(0, 0, "流水号"));
ws.addCell(new Label(1, 0, "用户编号"));
ws.addCell(new Label(2, 0, "彩票中心"));
ws.addCell(new Label(3, 0, "渠道"));
ws.addCell(new Label(4, 0, "用户系统"));
ws.addCell(new Label(5, 0, "彩种"));
ws.addCell(new Label(6, 0, "期号"));
ws.addCell(new Label(7, 0, "注数"));
ws.addCell(new Label(8, 0, "倍数"));
ws.addCell(new Label(9, 0, "销售方式"));
ws.addCell(new Label(10, 0, "金额（元）"));
ws.addCell(new Label(11, 0, "中奖金额（元）"));
ws.addCell(new Label(12, 0, "投注时间"));
ws.addCell(new Label(13, 0, "类型"));
ws.addCell(new Label(14, 0, "标识"));
ws.addCell(new Label(15, 0, "状态"));
BigDecimal amtAll = BigDecimal.ZERO;
BigDecimal prizeamtAll = BigDecimal.ZERO;
for (int i = 0; i < list.size(); i++) {
Tlot tlot = list.get(i);
int j = i + 1;
ws.addCell(new Label(0, j, tlot.getFlowno()));
ws.addCell(new Label(1, j, tlot.getUserno()));
ws.addCell(new Label(2, j, LotCenter.getName(tlot.getAgencyno())));
ws.addCell(new Label(3, j, tlot.getChannel()));
ws.addCell(new Label(4, j, tlot.getSubchannel()));
ws.addCell(new Label(5, j, Lottype.getName(tlot.getLotno())));
ws.addCell(new Label(6, j, tlot.getBatchcode()));
ws.addCell(new Label(7, j, tlot.getBetnum().toString()));
ws.addCell(new Label(8, j, tlot.getLotmulti().toString()));
String sellway = "";
if (BigDecimal.ZERO.equals(tlot.getSellway())) {
sellway = "自选";
} else if (new BigDecimal(2).equals(tlot.getSellway())) {
sellway = "机选";
}
ws.addCell(new Label(9, j, sellway));
ws.addCell(new Label(10, j, tlot.getAmt().divide(new BigDecimal(100)).toString()));
amtAll = amtAll.add(tlot.getAmt());
ws.addCell(new Label(11, j, tlot.getPrizeamt().divide(new BigDecimal(100)).toString()));
prizeamtAll = prizeamtAll.add(tlot.getPrizeamt());
ws.addCell(new Label(12, j, tlot.getOrdertime().toString()));
ws.addCell(new Label(13, j, BetType.getBetType(tlot.getBettype())));
ws.addCell(new Label(14, j, SettleFlagState.getMemo(tlot.getSettleflag())));
String state1 = "";
if (new BigDecimal(-1).equals(tlot.getState())) {
state1 = "未处理";
} else if (BigDecimal.ZERO.equals(tlot.getState())) {
state1 = "失败";
} else if (BigDecimal.ONE.equals(tlot.getState())) {
state1 = "成功";
} else if (new BigDecimal(2).equals(tlot.getState())) {
state1 = "处理中";
}
ws.addCell(new Label(15, j, state1));
}
int k = list.size() + 1;
ws.addCell(new Label(0, k, "合计"));
ws.addCell(new Label(10, k, amtAll.divide(new BigDecimal(100)).toString()));
ws.addCell(new Label(11, k, prizeamtAll.divide(new BigDecimal(100)).toString()));
wwb.write();
} catch (Exception e) {
logger.error("发生异常:" + e);
e.printStackTrace();
} finally {
if (wwb != null) {
try {
wwb.close();
} catch (WriteException e) {
e.printStackTrace();
}
}
if (os != null) {
os.close();
}
}
}