二次 开发！！！！
看着别人写的像肥肠一样的代码，我失眠了！！
一个分页查询Action里的代码贴出来，留作纪念。
public class supplierMakeOrder extends Action {
public ActionForward execute(ActionMapping mapping, ActionForm form,
HttpServletRequest req, HttpServletResponse resp) throws Exception {
HttpSession session = req.getSession();
User loginUser = (User) session.getAttribute("loginUser");
String username = loginUser.getUserName();
// System.out.println(username);
SearchInvoiceActionForm searchinvoiceForm = (SearchInvoiceActionForm) form;
String forward = "Success";// 下一步的链接
if (loginUser == null) {
forward = "outdate";
} else {
int newPageNo = searchinvoiceForm.getNewPageNo();
req.setAttribute("currentPage", new Integer(newPageNo));
int pageSize = searchinvoiceForm.getPageSize();
pageSize = 10;
if (pageSize == 0) {
PageCt pageCt = new PageCt();// 取得页面大小
pageSize = pageCt.getInt_num();
}
req.setAttribute("pageSize", new Integer(pageSize));
// System.out.println(forward);
String orderStates = searchinvoiceForm.getOrderStates();
String queryBasis = searchinvoiceForm.getQueryBasis();
String queryCond = searchinvoiceForm.getQueryCond();
String orderCond = searchinvoiceForm.getOrderCond();
String cz[] = searchinvoiceForm.getCz();
if (queryCond == null) {
queryCond = queryCond;
} else {
queryCond = queryCond.trim();
}
if (orderCond == null || orderCond.equals(""))
orderCond = " id DESC ";
System.out.println(orderCond);
if ("EBELN".equals(queryBasis)) {
queryBasis = "SAP_EKPO.EBELN";
}
String sql = "";
String countSql = "";
// 订单状态为空
if (queryCond == null || queryCond.trim().length() <= 0)// 没有参数
{
StringBuffer sqlQ = new StringBuffer();
sqlQ
.append(
" select TOP 10 SAP_EKPO.EBELP,SAP_EKPO.EBELN,isNull(jjd,'0') as jjd,ddgz,jdfk,isNull((select sum(qty) from wms_stock where MaterialCode=SAP_EKPO.MATNR and SupplierCode=SAP_EKKO.LIFNR),'0') as kcsl,SAP_EKPO.MATNR,SAP_EKPO.cksl,sap_ekpo.sdsl,dbo.ufun_Suppliernm(LIFNR) AS cjmc,LIFNR,MENGE,EKGRP,CONVERT(VARCHAR(100),BEDAT,23) AS BEDAT,CONVERT(VARCHAR(100),EINDT,23) AS EINDT,CASE KZABS WHEN 'X' THEN '确认' WHEN '1' THEN '发货未完成' when '2' then '发货完成' ELSE '未确认' END AS ZT,case when bsart='ZB04' then TXZ01 else MaterialName end as MaterialName,LGORT,itemcode ")
.append(",case when RETPO='x' then '退货' " )
.append(" when RETPO!='x' and bsart = 'ZB04' then '工序' " )
.append(" when RETPO!='x' and ekgrp in(select ekgrp from sap_showpotype where gb='2') then '毛坯' " )
.append(" else '采购' end as cglx " )
.append(" from SAP_EKPO ")
.append(
" left join SAP_EKKO on SAP_EKPO.EBELN=SAP_EKKO.EBELN ")
.append(
" left join material on SAP_EKPO.MATNR = material.MaterialCode ")
.append(
" where fbsh='2' and isNull(sap_ekpo.LOEKZ,'') <> 'L' and LIFNR=(select cjdh from users where username LIKE '"
+ username //已送货完成，但仍有部分货物在途
+ "') and (KZABS ='X' or KZABS ='1' or (KZABS='2' and (isnull(SAP_EKPO.MENGE,0)-isnull(SAP_EKPO.cksl,0))>0)) and RETPO<>'X' ");
sql = sqlQ.toString();
countSql = "SELECT count(*) AS rsCount FROM SAP_EKPO left join SAP_EKKO on SAP_EKPO.EBELN=SAP_EKKO.EBELN where LIFNR=(select cjdh from users where username LIKE '"
+ username
+ "') and fbsh='2' and (KZABS ='X' or KZABS ='1') and RETPO<>'X' and isNull(sap_ekpo.LOEKZ,'') <> 'L' ";
if (newPageNo == 0) {
sql += " and (SAP_EKPO.EBELN + convert(varchar(20),SAP_EKPO.EBELP)) <= (select min(id) from (select top 1 (SAP_EKPO.EBELN + convert(varchar(20),SAP_EKPO.EBELP)) as id from SAP_EKPO left join SAP_EKKO on SAP_EKPO.EBELN=SAP_EKKO.EBELN where fbsh='2' and LIFNR=(select cjdh from users where username LIKE '"
+ username
+ "') and isNull(sap_ekpo.LOEKZ,'') <> 'L' and (KZABS ='X' or KZABS ='1' or (KZABS='2' and (isnull(SAP_EKPO.MENGE,0)-isnull(SAP_EKPO.cksl,0))>0)) and RETPO<>'X' order by (SAP_EKPO.EBELN + convert(varchar(20),SAP_EKPO.EBELP)) desc)as t) ORDER BY (SAP_EKPO.EBELN + convert(varchar(20),SAP_EKPO.EBELP)) DESC";
} else {
int a = pageSize * (newPageNo - 1) + 1;
sql += " and (SAP_EKPO.EBELN + convert(varchar(20),SAP_EKPO.EBELP)) <= (select min(id) from (select top "
+ a
+ " (SAP_EKPO.EBELN + convert(varchar(20),SAP_EKPO.EBELP)) as id from SAP_EKPO left join SAP_EKKO on SAP_EKPO.EBELN=SAP_EKKO.EBELN where fbsh='2' and LIFNR=(select cjdh from users where username LIKE '"
+ username
+ "') and isNull(sap_ekpo.LOEKZ,'') <> 'L' and (KZABS ='X' or KZABS ='1' or (KZABS='2' and (isnull(SAP_EKPO.MENGE,0)-isnull(SAP_EKPO.cksl,0))>0)) and RETPO<>'X' order by (SAP_EKPO.EBELN + convert(varchar(20),SAP_EKPO.EBELP)) desc)as t) ORDER BY (SAP_EKPO.EBELN + convert(varchar(20),SAP_EKPO.EBELP)) DESC";
}
}
// 查询条件不为空
else {
StringBuffer sqlQ = new StringBuffer();
sqlQ
.append(
" select TOP 10 SAP_EKPO.EBELP,SAP_EKPO.EBELN,SAP_EKPO.MATNR,SAP_EKPO.cksl,sap_ekpo.sdsl,dbo.ufun_Suppliernm(LIFNR) AS cjmc,LIFNR,MENGE,EKGRP,CONVERT(VARCHAR(100),BEDAT,23) AS BEDAT,CONVERT(VARCHAR(100),EINDT,23) AS EINDT,CASE KZABS WHEN 'X' THEN '确认' WHEN '1' THEN '发货未完成' when '2' then '发货完成' ELSE '未确认' END AS ZT,case when bsart='ZB04' then TXZ01 else MaterialName end as MaterialName,dbo.ufun_storagenm(LGORT) as LGORT,itemcode ")
.append(",case when RETPO='x' then '退货' " )
.append(" when RETPO!='x' and bsart = 'ZB04' then '工序' " )
.append(" when RETPO!='x' and ekgrp in(select ekgrp from sap_showpotype where gb='2') then '毛坯' " )
.append(" else '采购' end as cglx " )
.append(" from SAP_EKPO ")
.append(
" left join SAP_EKKO on SAP_EKPO.EBELN=SAP_EKKO.EBELN ")
.append(
" left join material on SAP_EKPO.MATNR = material.MaterialCode ")
.append(
" where fbsh='2' and LIFNR=(select cjdh from users where username LIKE '"
+ username
+ "') and (KZABS ='X' or KZABS ='1' or (KZABS='2' and (isnull(SAP_EKPO.MENGE,0)-isnull(SAP_EKPO.cksl,0))>0)) and RETPO<>'X' ")
.append(
" and "
+ queryBasis
+ " like '%"
+ queryCond
+ "%' and isNull(sap_ekpo.LOEKZ,'') <> 'L' ");
sql = sqlQ.toString();
countSql = "SELECT count(*) AS rsCount FROM SAP_EKPO left join SAP_EKKO on SAP_EKPO.EBELN=SAP_EKKO.EBELN left join material on SAP_EKPO.MATNR = material.MaterialCode where LIFNR=(select cjdh from users where username LIKE '"
+ username
+ "') and fbsh='2' and (KZABS ='X' or KZABS ='1' or (KZABS='2' and (isnull(SAP_EKPO.MENGE,0)-isnull(SAP_EKPO.cksl,0))>0)) and isNull(sap_ekpo.LOEKZ,'') <> 'L' and RETPO<>'X' and "
+ queryBasis + " like '%" + queryCond + "%' ";
// 处理分页时，只查询当前显示页的数据
if (newPageNo == 0) {
sql += " and (SAP_EKPO.EBELN + convert(varchar(20),SAP_EKPO.EBELP)) <= (select min(id) from (select top 1 (SAP_EKPO.EBELN + convert(varchar(20),SAP_EKPO.EBELP)) as id from SAP_EKPO left join SAP_EKKO on SAP_EKPO.EBELN=SAP_EKKO.EBELN where fbsh='2' and isNull(sap_ekpo.LOEKZ,'') <> 'L' and LIFNR=(select cjdh from users where username LIKE '"
+ username
+ "') and (KZABS ='X' or KZABS ='1' or (KZABS='2' and (isnull(SAP_EKPO.MENGE,0)-isnull(SAP_EKPO.cksl,0))>0)) and RETPO<>'X' and "
+ queryBasis
+ " like '%"
+ queryCond
+ "%' order by (SAP_EKPO.EBELN + convert(varchar(20),SAP_EKPO.EBELP)) desc)as t) ORDER BY (SAP_EKPO.EBELN + convert(varchar(20),SAP_EKPO.EBELP)) DESC";
} else {
int a = pageSize * (newPageNo - 1) + 1;
sql += " and (SAP_EKPO.EBELN + convert(varchar(20),SAP_EKPO.EBELP)) <= (select min(id) from (select top "
+ a
+ " (SAP_EKPO.EBELN + convert(varchar(20),SAP_EKPO.EBELP)) as id from SAP_EKPO left join SAP_EKKO on SAP_EKPO.EBELN=SAP_EKKO.EBELN where fbsh='2' and isNull(sap_ekpo.LOEKZ,'') <> 'L' and LIFNR=(select cjdh from users where username LIKE '"
+ username
+ "') and (KZABS ='X' or KZABS ='1' or (KZABS='2' and (isnull(SAP_EKPO.MENGE,0)-isnull(SAP_EKPO.cksl,0))>0)) and RETPO<>'X' and "
+ queryBasis
+ " like '%"
+ queryCond
+ "%' order by (SAP_EKPO.EBELN + convert(varchar(20),SAP_EKPO.EBELP)) desc)as t) ORDER BY (SAP_EKPO.EBELN + convert(varchar(20),SAP_EKPO.EBELP)) DESC";
}
}
int rsCount = 0;
// 取出本次查询的总记录数，存入request中
try {
rsCount = DbPool.executeCountQuery(countSql);
req.setAttribute("rsCount", new Integer(rsCount));
} catch (SQLException ex) {
ex.printStackTrace();
forward = "error";
}
// 处理分页时，只查询当前显示页的数据
// 本次查询具体内容
try {
ArrayList allOrder = DbPool.executeQuery(sql);
req.setAttribute("allOrder", allOrder);
String title = "生成送货单信息查询";
String header = "序号,订单号,图号,物料号,物料描述,订单数量,已送数量,已到数量,订单日期,到货日期,状态";
String displayColumn = "NUM,EBELN,itemcode,MATNR,MaterialName,MENGE,sdsl,cksl,BEDAT,EINDT,ZT";
String chExist = "0,0,0,0,1,0,0,0,0,1";
session.setAttribute("title", title);
if (queryBasis != null && queryBasis.trim().length() > 0) {
String con = null;
if (queryBasis.equals("SAP_EKPO.EBELN")) {
con = "订单号";
}
if (queryBasis.equals("itemcode")) {
con = "图号";
}
if (queryBasis.equals("MATNR")) {
con = "物料号";
}
if (queryBasis.equals("MaterialName")) {
con = "物料描述";
}
if (queryBasis.equals("CONVERT(VARCHAR(100),EINDT,23)")) {
con = "到货日期";
}
session.setAttribute("queryCond", con + "中包含【" + queryCond
+ "】的订单信息");
} else {
session.setAttribute("queryCond", "");
}
session.setAttribute("header", header);
session.setAttribute("displayColumn", displayColumn);
session.setAttribute("chExist", chExist);
session.setAttribute("resultNow", sql);
// 订单状态为空
if (queryCond == null || queryCond.trim().length() <= 0)// 没有参数
{
StringBuffer sqlQ = new StringBuffer();
sqlQ
.append(
" select sap_ekpo.sdsl,SAP_EKPO.EBELP,SAP_EKPO.EBELN,SAP_EKPO.MATNR,SAP_EKPO.cksl,dbo.ufun_Suppliernm(LIFNR) AS cjmc,LIFNR,MENGE,EKGRP,CONVERT(VARCHAR(100),BEDAT,23) AS BEDAT,CONVERT(VARCHAR(100),EINDT,23) AS EINDT,CASE KZABS WHEN 'X' THEN '确认' WHEN '1' THEN '发货未完成' when '2' then '发货完成' ELSE '未确认' END AS ZT,case when bsart='ZB04' then TXZ01 else MaterialName end as MaterialName,dbo.ufun_storagenm(LGORT) as LGORT,itemcode ")
.append(" from SAP_EKPO ")
.append(
" left join SAP_EKKO on SAP_EKPO.EBELN=SAP_EKKO.EBELN ")
.append(
" left join material on SAP_EKPO.MATNR = material.MaterialCode ")
.append(
" where fbsh='2' and isNull(sap_ekpo.LOEKZ,'') <> 'L' and LIFNR=(select cjdh from users where username LIKE '"
+ username
+ "') and (KZABS ='X' or KZABS ='1') ");
sql = sqlQ.toString();
}
// 查询条件不为空
else {
StringBuffer sqlQ = new StringBuffer();
sqlQ
.append(
" select sap_ekpo.sdsl,SAP_EKPO.EBELP,SAP_EKPO.EBELN,SAP_EKPO.MATNR,SAP_EKPO.cksl,dbo.ufun_Suppliernm(LIFNR) AS cjmc,LIFNR,MENGE,EKGRP,CONVERT(VARCHAR(100),BEDAT,23) AS BEDAT,CONVERT(VARCHAR(100),EINDT,23) AS EINDT,CASE KZABS WHEN 'X' THEN '确认' WHEN '1' THEN '发货未完成' when '2' then '发货完成' ELSE '未确认' END AS ZT,case when bsart='ZB04' then TXZ01 else MaterialName end as MaterialName,dbo.ufun_storagenm(LGORT) as LGORT,itemcode ")
.append(" from SAP_EKPO ")
.append(
" left join SAP_EKKO on SAP_EKPO.EBELN=SAP_EKKO.EBELN ")
.append(
" left join material on SAP_EKPO.MATNR = material.MaterialCode ")
.append(
" where fbsh='2' and isNull(sap_ekpo.LOEKZ,'') <> 'L' and LIFNR=(select cjdh from users where username LIKE '"
+ username
+ "') and (KZABS ='X' or KZABS ='1') ")
.append(
" and "
+ queryBasis
+ " like '%"
+ queryCond
+ "%' and (KZABS ='X' or KZABS ='1') ");
sql = sqlQ.toString();
}
// ArrayList printAllOrder = DbPool.executeQuery(sql);
session.setAttribute("result", sql);
} catch (SQLException ex) {
ex.printStackTrace();
forward = "error";
}
searchinvoiceForm.setQueryBasis(queryBasis);
searchinvoiceForm.setOrderStates(orderStates);
searchinvoiceForm.setPageSize(pageSize);
searchinvoiceForm.setQueryCond(queryCond);
searchinvoiceForm.setNewPageNo(newPageNo);
searchinvoiceForm.setOrderCond(orderCond);
}
return mapping.findForward(forward);
}
}
发表评论
相关推荐
Activiti7的开源项目，用于二次开发！
100个微信小程序源码，无需接入第三方，自己开发！适合新手与二次开发！
tpfang 房产源码 可用可学习，是一款不错的房产源码，你可以在上面进行二次开发！！
U_G_二次开发与加工自动化 UG／Grip技术在诱导轮三维设计中的运用 UGOPENC++与xml技术 UG二次开发计算机辅助公差设计 UG二次开发技术的研究 UG二次开发中数据访问方法研究 UG软件CAE_二次开发研究及应用 UG下用户CAD...
CATIA二次开发 摘要：本文介绍了在以Windows XP 为操作系统的微机上应用VC++ 6.0工具，基于Component Application Architecture（CAA） 组件应用架构的CATIA界面二次开发技术。并在此基础上通过建立一个基于CATIA二...
义博！Autodesk revit二次开发基础教程，强烈推荐，强烈推荐，强烈推荐，强烈推荐 !
用友致远A8平台二次开发 本手册主要面向二次开发人员，内容涵盖A8平台的二次开发、第三方应用集成。如果需要更详细的开发信息，请参考《平台API参考手册》。 开发背景 接口的目标是 1、与第三方应用集成，达成松散...
含32位、64位驱动，支持WIN7系统，打包了二次开发函数库MarkEzd.dll及简易开发文档，可以二次开发！再含接线定义&说明书以及安装说明！！！二次开发有难度，女司机请悠着点！注：本资源仅供研究学习使用！
LED广告屏二次开发！
本文为中望CAD二次开发技术文档，主要是.net开发，主要的适应人群为中望CAD二次开发人员，文档主要介绍中望CAD的二次开发接口、开发方式以及不同语言开发的简要教程。本人也是因工作需要整理出来的开发文档，希望对...
最近在项目中要用jeecms进行二次开发，自己整理了一些jeecms二次开发的相关资料。包括jeecms工程包结构分析和标签使用等。。。
教你如何对UCHOME进行二次开发！ 教你如何对UCHOME进行二次开发！ 教你如何对UCHOME进行二次开发！
可运营级别的第三方支付平台源码多支付渠道+代理功能等支持后台补单，源码全开源，支持二次开发!.txt
ENVI IDL 的二次开发教程，PDF格式，希望能帮助大家啊！
VS2019工程C#版本，NIKON相机D5600第二次开发第一版本，亲自开发测试通过，亲们可以拿来接着开发哦~！此版本是NIKON相机D5600与D5500的通用版本可供二次开发！！！版主会陆续更新的
二次开发手册完整版，有大量实例，如果有不明白请加我QQ。
西门子 step7中 FB41 PID 功能块的SCL语言编写，可以转换成C++格式用来二次开发！建议有工控基础和C语言基础的人员下载！转换过程参照： “https://download.csdn.net/download/weixin_37928884/85103667”、...
手册主要介绍Fluent二次开发技巧和方法，并详细解答了该如何进行Fluent二次开发： 一是如何编制UDF程序；二是如何定制Fluent界面；三是如何封装Fluent进程。
maximo二次开发，分享！
asp.net 全开源 可二次开发。 适合学校，培训机构的课消，工资，人事等使用！ 使用需要有一定的程序功底，更好地发挥软件功能！强烈推荐！