webprint.java

一个完整的

				//准备下一个的SQL语句
				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;