find.java

检化验管理系统

package com.snsoft.site.jspbean;

import com.jl.zys.dbaccess.*;
import java.util.*;
import java.sql.Date;
import java.sql.*;
import java.util.Calendar;
import java.text.SimpleDateFormat;


public class find {
  private int totalpage; //总页数
  private int stride; //跨度
  private int currentpage; //当前页
  private int size = 18; //每页记录数
  private int totalsize; //总记录数

  String goods = "";
  String sign = "";
  String name = ""; //制单人
  String company = "";
  //String checkDateA = "";
  String dateA = "";
  //String checkDateB = "";
  String dateB = "";
  String person = "";
  String userids = "";
  String all = "";
  String numid = "";
  String judge = "";
  String checkmen = ""; //审核人
  String biaoshi = ""; //标识号
  String submit1 = ""; //请求的类型，是查询还是模糊查询
  //String subt = "";
  String check_input_common = ""; //check审核人查询；input制单人查询；common通用查询
  SimpleDateFormat formatter = new SimpleDateFormat ("yyyy-MM-dd");
  String show = "";//是否分页显示；y分页，n不分页
  String sqlwhere = "";

//--------------分页中set/get--------------------------
  public void setTotalsize(){//设置记录总数
      String sqlw = sqlwhere ();
      String strf = strfield();
      MyResultSet rs7 = null;
    try{
        String sqlt = "select count(*) from (SELECT distinct "+goods+".id FROM " + goods + " LEFT OUTER JOIN bianma ON " + goods +
                 ".编码流水 = bianma.编码流水 where "+sqlw+") DERIVEDTBL ";
      //rs = super.executeQuery(sqlt);
      rs7 = DbAccess.querySqlOption(sqlt);
      rs7.next();
      this.totalsize = rs7.getInt(1);
    }catch(Exception e){
      e.printStackTrace();
    }
    finally {
      try {
        if (rs7 != null) {rs7.close();}
      }
      catch (SQLException ex) {}
    }
  }

  public void setTotalpage(){//设置总页数
    this.totalpage = totalsize/size;
    if(totalsize%size>0)
      this.totalpage++;
  }

  public void setCurrentpage(int currentpage){//设置当前页
    this.currentpage = currentpage;
  }

  public void setStride(int stride){//设置跨度
    this.stride = stride;
  }

  public void setSize(int size){//设置每页记录数
    this.size = size;
  }

  public int getTotalsize(){//--获得总记录数-
    return totalsize;
  }

  public int getTotalpage(){//--获得总页数-
    return totalpage;
  }

  public int getStride(){//获得跨读
    return stride;
  }

  public int getCurrentpage(){//获取当前页
    return currentpage;
  }

  public int getSize(){//获取每页记录数
    return size;
  }
//--------------------------打印分页---------------------------
  public String show(){
    String willprint = new String();
    int p, Tmpa;
    for (Tmpa = 1; Tmpa <= totalpage; Tmpa++) {
      if (Tmpa + stride < currentpage) { //加了跨度还小于当前页的不显示
        continue;
      }
      if (Tmpa + stride == currentpage) { //刚好够跨度的页数
        p = currentpage - stride-1;
        willprint += "<a href=\"?page=" +
            (currentpage - 1) +
            "&check_input_common=" + check_input_common + "&show=" + show + "&goods=" +
              goods + "&sign=" + sign + "&company=" + company +
              "&biaoshi=" + biaoshi + "&checkmen=" + checkmen + "&name=" +
              name + "&dateA=" + dateA + "&dateB=" + dateB +
              "&submit1=" + submit1 + "\" title=\"上页\"><span style=\"border-bottom:#000000 solid 1px;\">[上一页]</span></a>&nbsp;";
      }
      if (Tmpa > currentpage + stride) { //大于当前页+跨度的页面
        break;
      }
      //-----------
      willprint += "<a href=\"?page=" + Tmpa + "&check_input_common=" + check_input_common + "&show=" + show + "&goods=" +
              goods + "&sign=" + sign + "&company=" + company +
              "&biaoshi=" + biaoshi + "&checkmen=" + checkmen + "&name=" +
              name + "&dateA=" + dateA + "&dateB=" + dateB +
              "&submit1=" + submit1 + ">";
      if(Tmpa!=currentpage){
        willprint +="<span style=\"border-bottom:#000000 solid 1px;\">["+Tmpa+"]</span></a>&nbsp;";
      }else{
        willprint +="<font color=\"#CC0000\">"+Tmpa+"</font></a>&nbsp;";
      }
      //------------
      if (Tmpa == currentpage + stride ) { //刚好够跨度的页数
        p = currentpage + stride + 1;//& Tmpa<totalpage
        willprint += "<a href=\"?page=" + (currentpage + 1) + "&check_input_common=" + check_input_common + "&show=" + show + "&goods=" +
              goods + "&sign=" + sign + "&company=" + company +
              "&biaoshi=" + biaoshi + "&checkmen=" + checkmen + "&name=" +
              name + "&dateA=" + dateA + "&dateB=" + dateB +
              "&submit1=" + submit1 + " title=\"下页\"><span style=\"border-bottom:#000000 solid 1px;\">[下一页]</span></a>";
      }
    }
    return willprint;
  }

//-------------------set方法---------------------------
  public void setGoods (String goods) {
      this.goods = goods;
  }
  public void setGoods_nom (String goods) {
      this.goods = goods;
  }
  public void setSign (String sign) {
      this.sign = sign;
  }
  public void setName (String name) {
      this.name = name;
  }
  public void setCompany (String company) {
      this.company = company;
  }
  public void setDateA (String dateA) {
      this.dateA = dateA;
  }
  public void setDateB (String dateB) {
      this.dateB = dateB;
  }
  public void setPerson (String person) {
      this.person = person;
  }
  public void setUserids (String userids) {
      this.userids = userids;
  }
  public void setNumid (String numid) {
      this.numid = numid;
  }
  public void setJudge (String judge) {
      this.judge = judge;
  }
  public void setCheckmen (String checkmen) {
      this.checkmen = checkmen;
  }
  public void setBiaoshi (String biaoshi) {
      this.biaoshi = biaoshi;
  }
  public void setCheck_input_common (String check_input_common) {
      this.check_input_common = check_input_common;
  }
  public void setShow (String show) {
      this.show = show;
  }
public String getSubmit1() {
	return submit1;
}
public void setSubmit1(String submit1) {
	this.submit1 = submit1;
}
//-------------------get方法---------------------------
  public String getShow () {
      return show;
  }
  public String getGoods () {
      return goods;
  }
  public String getSign () {
      return sign;
  }
  public String getName () {
      return name;
  }
  public String getCompany () {
      return company;
  }
  public String getDateA () {
      return dateA;
  }
  public String getDateB () {
      return dateB;
  }
  public String getPerson () {
      return person;
  }
  public String getUserids () {
      return userids;
  }
  public String getNumid () {
      return numid;
  }
  public String getJudge () {
      return judge;
  }
  public String getCheckmen () {
      return checkmen;
  }
  public String getBiaoshi () {
      return biaoshi;
  }
  public String getCheck_input_common () {
      return check_input_common;
  }
//----------------------------------------------------
  public String strfield(){
      String strfield="";
      strfield = " bianma.使用单位,bianma.取样日期,bianma.编码流水," + goods +
                 ".* from " + goods + " LEFT OUTER JOIN bianma ON " + goods +
                 ".编码流水 = bianma.编码流水 ";
      return strfield;
  }

//-------------------生成查询语句---------------------------
  public  String sqlFind () {

      String sqlw = sqlwhere();
      String strf = strfield();
      String sql = "";
      if(show.indexOf("y")!=-1){
          sql = "select distinct top " + size + " " + strf + " where " + sqlw +" and " + goods + ".id !=ALL(" +
              "select distinct top " + (currentpage - 1) * size + " " + goods +".id from " + goods + " where " + sqlw + " " +
              "order by " + goods + ".id desc) order by " + goods + ".id desc";
      }else{
          sql = "select distinct "+strf+" where "+sqlw+" order by " + goods + ".id desc";
      }
      return sql;
  }
//------------------------------------------------------------
  public String sqlwhere(){
      String userid = userids.substring(2);
      try {
          if (check_input_common.equals ("check")) {
              sqlwhere = " " + goods + ".作废 = 'n' and (" + goods + ".审核 like '%" + person +"%' or " + goods + ".审核 like '%" + userid +"%')";
          } else if (check_input_common.equals ("input")) {
              sqlwhere = " " + goods + ".作废 = 'n' and " + goods + ".制单人 = '" + person + "'";
          } else if (check_input_common.equals ("common")) {
              sqlwhere = " " + goods + ".作废 = 'n' and " + goods + ".审核结束 = 'y'";
          }
          if (checkmen.length () != 0) {
              sqlwhere = sqlwhere + " " + "and " + goods + ".审核 like '%" + checkmen +"%'";
          }
          //System.out.println(submit1);
          if (!submit1.equals ("模糊查询")) {
              if (name.length () != 0) {
                  sqlwhere = sqlwhere + " " + "and " + goods + ".制单人 = '" + name + "'";
              }
              if (biaoshi.length () != 0) {
                  sqlwhere = sqlwhere + " " + "and " + goods + ".标识 = '" +biaoshi +"'";
              }
              if (sign.length () != 0) {
                  sqlwhere = sqlwhere + " " + "and " + goods + ".流水号 = '" +sign + "'";
              }
              if (company.length () != 0) {
                  sqlwhere = sqlwhere + " " + "and " + goods + ".户名 = '" +company +"'";
              }
          } else {
              if (name.length () != 0) {
                  sqlwhere = sqlwhere + " " + "and " + goods +".制单人 like '%" + name +"%'";
              }
              if (biaoshi.length () != 0) {
                  sqlwhere = sqlwhere + " " + "and " + goods +".标识 like '%" + biaoshi +"%'";
              }
              if (sign.length () != 0) {
                  sqlwhere = sqlwhere + " " + "and " + goods +".流水号 like '%" + sign +"%'";
              }
              if (company.length () != 0) {
                  sqlwhere = sqlwhere + " " + "and " + goods +".户名 like '%" + company +"%'";
              }
          }
          if (dateA.length()==10) {
              sqlwhere = sqlwhere + " " + "and bianma.取样日期 >= DATEADD( dd , 0 ,'" +dateA +"' )";
          }
          if (dateB.length()==10) {
              sqlwhere = sqlwhere + " " + "and bianma.取样日期 <= DATEADD( dd , 0 ,'" +dateB +"' )";
          }
      } catch (Exception e) {
          e.getMessage ();
      }
      //System.out.println(sqlwhere);
      return sqlwhere;
  }

//----------------------编码前录入,查询编码表中的内容------------
  public String blFindCont (String bmid) {
      StringBuffer buf = new StringBuffer ();
      String goodst = goods + "_t";
      ResultSetMetaData rsmd = null;
      MyResultSet rs9 = null;
      MyResultSet rs10 = null;
      int t = 0;
      try {
          String sql = "select * from " + goodst + " where 编码流水 = '" + bmid +
              "'";
          String sql2 = "select * from " + goodst + "";
          //rs = super.executeQueryp (sql);
          rs9 = DbAccess.querySqlOptionp(sql);
          rs10 = DbAccess.querySqlOption(sql2);
          rsmd = rs10.getMetaData();
          //rsmd = super.executeQueryRsmt (sql2);
          if (rsmd != null) {
              int count = rsmd.getColumnCount ();
              while (rs9.next ()) {
                  for (int i = 3; i <= count; i++) {
                      buf.append (
                          "<td align=\"center\" width = \"30\" nowrap>");
                      String rsstr = rs9.getString (i);
                      if (rsstr == null)
                          rsstr = "";
                      buf.append (rsstr);
                      buf.append ("</td>");
                  }
                  t++;
              }
              if (t == 0) {
                  for (int i = 3; i <= count; i++) {
                      buf.append (
                          "<td align=\"center\" width = \"30\"  style=\"white-space:nowrap\"></td>");
                  }