二次 开发！！！！
看着别人写的像肥肠一样的代码，我失眠了！！
一个分页查询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);
?}
}
