二次 开发！！！！

看着别人写的像肥肠一样的代码，我失眠了！！

一个分页查询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);
 }

}