webprint.java

一个完整的

        tmpsGroup=fc_RepStr(sGroup2,"1999=1999","1999=1999"+" and " +sGroupField1+"='"+sGroupKey1+"'");

        //System.out.println("my:"+tmpsGroup);
        rs2 = stmt2.executeQuery(tmpsGroup);
            ResultSetMetaData rsmd2=rs2.getMetaData();

        if(beforeSum.equals("1")){
          //-------合计行在前
          if (i>(iPageNo-1)*iPageSize && i<=iPageNo*iPageSize) {
            strRetXml+=strX;
          }
          i++;
        }

        while (rs2.next()) {
          sGroupKey2=rs2.getString(1);
          //算出第二个分组的合计行值
                strX1 =  "<recordsum2>";
            iCount=rsmd2.getColumnCount();
                //第一个字段是分组字段值,所以从2开始
                for (iField = 2 ; iField<=iCount ;iField++ )
            {
              //当有NULL字段名时,表示此则无需给值.用fcnull标记名区分.
              String colName=rsmd2.getColumnName(iField);
              if (colName==null) {
                strX1 = strX1 + "<fcnull></fcnull>" ;
              }
              else {
                String sT=rs2.getString(iField);
                if (sT==null) sT="";
                if(iField == 2)
                  strX1 = strX1 + "<fc>小 计 " + sT +"</fc>" ;
                else
                        strX1 = strX1 + "<fc>" + sT +"</fc>" ;
                    }
            }
                strX1 = strX1 + "</recordsum2>";
          //-------------------------------

          //准备下一个的SQL语句
          tmpsGroup=fc_RepStr(strSql,"1999=1999","1999=1999"+" and " +sGroupField1+"='"+sGroupKey1+"'"+" and " +sGroupField2+"='"+sGroupKey2+"'");
          //此处定合计行在明细行的前面还是后面
          if(beforeSum.equals("1")){
            //-------合计行在前
            if (i>(iPageNo-1)*iPageSize && i<=iPageNo*iPageSize) {
              strRetXml+=strX1;
            }
            i++;
            strRetXml+=fc_selectTmp(iPageNo,iPageSize,i,tmpsGroup);
            i=iPosition;
          }
          else {
            //-------合计行在后
            strRetXml+=fc_selectTmp(iPageNo,iPageSize,i,tmpsGroup);
            i=iPosition;
            if (i>(iPageNo-1)*iPageSize && i<=iPageNo*iPageSize) {
              strRetXml+=strX1;
            }
            i++;
          }

        }//二级分组的循环结束

        if(beforeSum.equals("0")){
          //-------合计行在后
          if (i>(iPageNo-1)*iPageSize && i<=iPageNo*iPageSize) {
            strRetXml+=strX;
          }
          i++;
        }
      } //判断有无二级分组的IF结束
    }//一级分组的循环结束
//----- loop end

    //第3个子节点(mainsql)的第3个子节点下(end)的第1个子节点(sql)的值用如下求法.
      String strEndXml="";	//首合计行的xml串
      //总计行之类的
      try {
        //如有一个节点表示首合计行，如有二个节点表示第一为首合计行，第二为尾合计行
        String sStartSql=root.getChildNodes().item(2).getChildNodes().item(2).getChildNodes().item(0).getChildNodes().item(0).getNodeValue();
      //System.out.println("endsql:"+sStartSql);
      //此处现只假定只有一个查询,如有多个,则此处要用循环
      strEndXml=fc_selectTmp(iPageNo,iPageSize,i,sStartSql);
      i=iPosition;
    }
    catch (Exception e){
    }
    //-----------------

    i=i-1;
    if (i>0) {
      strRetXml = strStartXml+strRetXml +strEndXml+ "<pages>"+i+"</pages>";
    }
    strRetXml = "<root>"+strRetXml + "</root>";
    //rs.close();
  }
  catch (Exception e){
    e.printStackTrace() ;
    sTmp=e.getMessage() ;
    System.out.println("071201"+sTmp);
  }

  return strRetXml ;

}
//--------------------------------------
private String fc_insertunescape() {
  Node root = document.getChildNodes().item(0);
    String strSql=root.getChildNodes().item(0).getChildNodes().item(0).getNodeValue();
//	strSql=fcTrans(strSql);
  String sTmp = "";
//	CallableStatement cstmt=null;
  try {
    Statement stmt = con.createStatement();
//		stmt.executeQuery(strSql);

    stmt.executeUpdate(strSql);
/*
    cstmt=con.prepareCall("{call sp_getID(?)}");
    cstmt.registerOutParameter(1,java.sql.Types.INTEGER,1);
    cstmt.executeUpdate();
    sTmp="<dsnID>"+ cstmt.getInt(1)+"</dsnID>";
*/
  }
  catch (Exception e){
    sTmp=e.getMessage() ;
  }

  return sTmp ;

}


//--------------------------------------
private String fc_insert() {
  Node root = document.getChildNodes().item(0);
    String strSql=root.getChildNodes().item(0).getChildNodes().item(0).getNodeValue();
  strSql=fcTrans(strSql);
  String sTmp = "";
//	CallableStatement cstmt=null;
  try {
    Statement stmt = con.createStatement();
//		stmt.executeQuery(strSql);

    stmt.executeUpdate(strSql);
/*
    cstmt=con.prepareCall("{call sp_getID(?)}");
    cstmt.registerOutParameter(1,java.sql.Types.INTEGER,1);
    cstmt.executeUpdate();
    sTmp="<dsnID>"+ cstmt.getInt(1)+"</dsnID>";
*/
  }
  catch (Exception e){
    sTmp=e.getMessage() ;
  }

  return sTmp ;

}
//--------------------------------------
private String fc_insert1() {
//凭证增加
  Node root = document.getChildNodes().item(0);
  String sTmp = "";
  CallableStatement cstmt=null;
  try {
    Statement stmt = con.createStatement();
    int i;
    //System.out.println(root.getChildNodes().getLength());
      String strSql=root.getChildNodes().item(0).getChildNodes().item(0).getNodeValue();
    strSql=fcTrans(strSql);
    stmt.executeUpdate(strSql);

    String sID="1"; //存主表新生成的ID

    try {
      //SQL Server 的存储过程,用于计算自动ID
      cstmt=con.prepareCall("{call sp_getID(?)}");
      cstmt.registerOutParameter(1,java.sql.Types.INTEGER,1);
      cstmt.executeUpdate();
      sID=""+cstmt.getInt(1);
    }
    catch (Exception e1){
      try {
        //计算MS Access数据库的主表ID
        String sDBDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
        String sConnStr = "jdbc:odbc:webprint";
        Class.forName(sDBDriver);
        Connection con1 = DriverManager.getConnection(sConnStr,"sa","");
        Statement stmt1 = con1.createStatement();
        rs1 = stmt1.executeQuery("select max(flngSendOutID) From SendOut");
        if (rs1.next()) {
          sID=rs1.getString(1);
        }
      }
      catch (Exception e2){
      }
    }

    //追加子表内容
    for(i=1;i<root.getChildNodes().getLength();i++) {
        strSql=root.getChildNodes().item(i).getChildNodes().item(0).getNodeValue();
      strSql=fcTrans(strSql);
      strSql=strSql+sID+" )";
      stmt.executeUpdate(strSql);
    }

  }
  catch (Exception e){
    System.out.println(e.getMessage()) ;
  }

  return sTmp ;

}
//--------------------------------------
private String fc_update1() {
//凭证修改
  Node root = document.getChildNodes().item(0);
  String sTmp = "";
  String strSql="";
  try {
    Statement stmt = con.createStatement();
    int i;

    for(i=0;i<root.getChildNodes().getLength();i++) {
        strSql=root.getChildNodes().item(i).getChildNodes().item(0).getNodeValue();
      strSql=fcTrans(strSql);
      stmt.executeUpdate(strSql);
    }

  }
  catch (Exception e){
    System.out.println(e.getMessage()) ;
  }

  return sTmp ;

}
//--------------------------------------
/**
*检查SQL语句是否为合法的SQL语句
*@param sql 要检查的SQL语句.
*@return 是则返回空,否则返回非空
*@date 2002-05-20
**/
private String checksql() {
  Node root = document.getChildNodes().item(0);
    String strSql=root.getChildNodes().item(0).getChildNodes().item(0).getNodeValue();

  strSql=fcTrans(strSql);
  //System.out.println(strSql);
  String strX = "";
  try {
    Statement stmt = con.createStatement();
    rs = stmt.executeQuery(strSql);
    rs.close();
  }
  catch (Exception e){
    strX=e.getMessage() ;
  }

  return strX ;

}

//--------------------------------------
private String fillcombox() {
//拼成的XML串的节点名为option,可以直接填入combox中
//返回的XML串无根节点.
  Node root = document.getChildNodes().item(0);
    String strSql=root.getChildNodes().item(0).getChildNodes().item(0).getNodeValue();
/*
    String strPageNo=root.getChildNodes().item(1).getChildNodes().item(0).getNodeValue();
  int iPageNo=Integer.parseInt(strPageNo,10);//页号
    String strPageSize=root.getChildNodes().item(2).getChildNodes().item(0).getNodeValue();
  int iPageSize=Integer.parseInt(strPageSize,10);//页尺寸
*/
  strSql=fcTrans(strSql);
  //System.out.println(strSql);
  String strX = "";
  String sTmp = "";
  int iField=1;
  int iCount=1;
  int i=1;
  try {
    Statement stmt = con.createStatement();
    rs = stmt.executeQuery(strSql);
          ResultSetMetaData rsmd=rs.getMetaData();
    iCount=rsmd.getColumnCount();
                String sT="";
    while (rs.next()) {
      //每页30行
      //if ((i>(iPageNo-1)*iPageSize && i<=iPageNo*iPageSize) || iPageSize==-1) {
            //strX = strX + "<option>";
      if(iCount>1){
      //假定两列时,第一列为value,第二列为text
         sT=rs.getString(1);
         if (sT==null) sT="";
         strX+="<option value='" +sT;
         sT=rs.getString(2);
         if (sT==null) sT="";
         strX+="' >"+sT+"</option>";
      }else {
      //一列时则value和text相同
         sT=rs.getString(1);
         if (sT==null) sT="";
         strX+="<option value='" +sT;
         strX+="' >"+sT+"</option>";

      }
//	                }
                  i = i + 1;
    }
    //strX = strX + "</root>";
    rs.close();
  }
  catch (Exception e){
    sTmp=e.getMessage() ;
    System.out.println(sTmp);
  }

  return strX ;

}

//--------------------------------------
private String SqlToField(String strSql) {
//用一个SQL语句返回一个字段的第一个记录值
  String strR =  "";
  try {
    Statement stmt = con.createStatement();
    rs = stmt.executeQuery(strSql);
    if (rs.next()) {
        strR = rs.getString(1) ;
    }
    rs.close();
    stmt.close();
  }
  catch (Exception e){
      //out.println("读表发生错误");
    }
  return strR;

}
//--------------------------------------
//报表增加
//--------------------------------------
private String GetReportCell() {
//找到指定位置的单元的值.
  Node root = document.getChildNodes().item(0);
  String sRow = root.getChildNodes().item(0).getChildNodes().item(0).getNodeValue();
  int iRow=Integer.parseInt(sRow,10);
  String sCol =root.getChildNodes().item(1).getChildNodes().item(0).getNodeValue();
  int iCol=Integer.parseInt(sCol,10);
  String strTableName =root.getChildNodes().item(2).getChildNodes().item(0).getNodeValue();
  strTableName=fcTrans(strTableName);
  //int iRow=Integer.parseInt(sRow,10);
  String strSql="";
  //String strID=TableNameToID(strTableName);
  //strSql = "Select fstrDiv1 From cellsheet where flngID=" + strID + " and flngSheetNo=" + strPage  ;
  strSql = "Select fstrTable1 From fcCell where fstrTableName='" + strTableName+"'"  ;

  //String sLogCol=ToLogCol(sRow,sCol,"Select fstroCoor From cellsheet where flngID=" + strID + " and flngSheetNo=" + strPage );
  //int iCol=Integer.parseInt(sLogCol,10);
  String strR="";
  String strR1="";
  int iStart=0;
  int iStartTmp=0;
  int iTmp=0;
  int iEnd=0;
  try {
    Statement stmt = con.createStatement();
    rs = stmt.executeQuery(strSql);
    if (rs.next()) {
        strR = rs.getString(1) ;
    }

    rs.close();
    stmt.close();
    //strR1=strR;
    //查找定位TR TD
    //iStart=strR.indexOf("%20id%3Dt%20",0);//查找 id=t
    for (int i=0;i<=iRow;i++) {
      iStartTmp=strR.indexOf("%3CTR",iStart+1);//TR开始符
      if (iStartTmp>0) {
        iStart=iStartTmp;
      }
    }
    if (iStartTmp>0) {
      for (int i=0;i<=iCol;i++) {
        iStartTmp=strR.indexOf("%3CTD",iStart+1); //TD开始符
        if (iStartTmp>0) {
          iStart=iStartTmp;
        }
      }
      iStart=strR.indexOf("%3E",iStart);//TD后的第一个>
      iEnd=strR.indexOf("%3C/TD%3E",iStart);//TD结束符
      if (iEnd>iStart && iStart>0) {
        strR=strR.substring(iStart+3,iEnd);
      }
    }
  }
  catch (Exception e){
    System.out.println(e.getMessage());
  }
  //return Integer.toString(iEnd) ;
  System.out.println(strR);
  return "<root>"+strR+"</root>";
}

//--------------------------------------






//--------------------------------------
//通用函数
private  String fc_RepStr(String mainStr,String findStr,String replaceStr){
//多次替代
  int iStart=0;
  int iEnd=0;
  String sRet="";