form_tablebean.java

来自「java开发的办公系统 1.系统管理 (地区管理,部门管理,菜单管理,用户管理」· Java 代码 · 共 944 行 · 第 1/2 页

package com.vere.form.bean;

	import java.sql.*;
	import java.util.*;

	import com.vere.db.*;
	import com.vere.form.item.*;
	import com.vere.item.*;
import com.vere.util.TransFormat;


	public class Form_tableBean
	{
		private ResultSet rs=null;
		private int keyId;
		private int totalRecord;
		private Access access = null;
		private StringBuffer buff=null;
		private int level_num=0;

		public Form_tableBean()
		{
			try {
				access=new Access();
			}
			catch(Exception e) {
				System.out.println(e.toString());
			}
		}

		public List findDadabaseTable()
		{
			access.getConnection();
			ArrayList list=new ArrayList();
			//从数据源中取表
			try{
                DatabaseMetaData metaData = access.getDataBaseMetaData();
                String[] tableType = {"table"};
                String userID = metaData.getUserName();
                ResultSet r;
                r = metaData.getTables(null, null, null, tableType);
                while (r.next()) {
                    list.add(r.getString("TABLE_NAME"));
                }
			}catch(Exception e)
			{
				e.printStackTrace();
			}
			finally{
				DBclose();
			}
			return list;
		}
		
		public List findTableColumn(String tablename)
		{
			access.getConnection();
			ArrayList list=new ArrayList();
			//通过数据表名拿列名
			try{
				ResultSetMetaData rsMetaData=access.getResultSetMetaData(tablename);
				for(int j=1;j<=rsMetaData.getColumnCount();j++){
					list.add(rsMetaData.getColumnLabel(j));
				}
			}catch(Exception e1){
				System.out.println(e1.toString());
			}
			finally{
				DBclose();
			}
			return list;
		}
		
		/**
		 * @author	Administrator
		 * @param	无
		 * @return	
		 * @see		关联表的值
		 */
		public List findByTable(String tablename,String field){
			ArrayList list=new ArrayList();
			try {
				String sql="select "+field+" from "+tablename+" ";
				rs = executeQuery(sql);
				while(rs.next()){
					list.add(rs.getString(field));
				}
			}catch(SQLException e){
				System.out.print(e.toString());
			}
			finally{
				DBclose();
			}
			return list;
		}
		
		/**
		 * @author	Administrator
		 * @param	无
		 * @return	ArrayList:Form_tableItem 对象的 ArrayList 数组
		 * @see		取出 form_table 表中的所有记录,每一条记录存储在一个 Form_tableItem 对象中
		 */
		public List find(){
			ArrayList list=new ArrayList();
			try {
				String sql="select * from "+T.FORM_TABLE+" order by pid asc,id asc";
				rs = executeQuery(sql);
				while(rs.next()){
					Form_tableItem item=new Form_tableItem();
					item.setId(rs.getString("id"));
					item.setPid(rs.getString("pid"));
					item.setNodepath(rs.getString("nodepath"));
					item.setChinese_name(rs.getString("chinese_name"));
					item.setEnglish_name(rs.getString("english_name"));
					item.setSort_id(rs.getString("sort_id"));
					item.setTable_type(rs.getString("table_type"));
					item.setUrl(rs.getString("url"));
					item.setFunc_id(rs.getString("func_id"));
					list.add(item);
				}
			}catch(SQLException e){
				System.out.print(e.toString());
			}
			finally{
				DBclose();
			}
			return list;
		}
		
		/**
		 * @author	Administrator
		 * @param	无
		 * @return	ArrayList:Form_tableItem 对象的 ArrayList 数组
		 * @see		取出 form_table 表中的所有记录,每一条记录存储在一个 Form_tableItem 对象中
		 */
		public List find(Form_tableItem aItem){
			ArrayList list=new ArrayList();
			try {
				boolean b=false;
				StringBuffer filter=new StringBuffer();
				String fvalue=null;
				
				fvalue=aItem.getTable_type();
				if(fvalue!=null &&   fvalue.length()>0)
				{
					if(b) filter.append(" and");
					filter.append(" table_type="+fvalue+" ");
					b=true;
				}
				
	             String sql="select * from "+T.FORM_TABLE+" ";
	             if(b)
	               sql+=" where "+filter.toString()+" order by pid asc,id desc ";
	             else
	                 sql+=" order  by pid asc,id desc ";
				rs = executeQuery(sql);
				while(rs.next()){
					Form_tableItem item=new Form_tableItem();
					item.setId(rs.getString("id"));
					item.setPid(rs.getString("pid"));
					item.setNodepath(rs.getString("nodepath"));
					item.setChinese_name(rs.getString("chinese_name"));
					item.setEnglish_name(rs.getString("english_name"));
					item.setSort_id(rs.getString("sort_id"));
					item.setTable_type(rs.getString("table_type"));
					item.setUrl(rs.getString("url"));
					item.setFunc_id(rs.getString("func_id"));
					list.add(item);
				}
			}catch(SQLException e){
				System.out.print(e.toString());
			}
			finally{
				DBclose();
			}
			return list;
		}
		
		public List findMenu(Form_tableItem aItem){
			ArrayList list=new ArrayList();
			try {
				boolean b=false;
				StringBuffer filter=new StringBuffer();
				String fvalue=null;
				
				fvalue=aItem.getTable_type();
				if(fvalue!=null &&   fvalue.length()>0)
				{
					if(b) filter.append(" and");
					filter.append(" table_type="+fvalue+" ");
					b=true;
				}
				
	             String sql="select * from "+T.FORM_TABLE+" ";
	             if(b)
	               sql+=" where "+filter.toString()+" order by pid asc,sort_id  ";
	             else
	                 sql+=" order  by pid asc,sort_id ";
				rs = executeQuery(sql);
				while(rs.next()){
					Form_tableItem item=new Form_tableItem();
					item.setId(rs.getString("id"));
					item.setPid(rs.getString("pid"));
					item.setNodepath(rs.getString("nodepath"));
					item.setChinese_name(rs.getString("chinese_name"));
					item.setEnglish_name(rs.getString("english_name"));
					item.setSort_id(rs.getString("sort_id"));
					item.setTable_type(rs.getString("table_type"));
					item.setUrl(rs.getString("url"));
					item.setFunc_id(rs.getString("func_id"));
					list.add(item);
				}
			}catch(SQLException e){
				System.out.print(e.toString());
			}
			finally{
				DBclose();
			}
			return list;
		}
		
		
		/**
		 * @author	Administrator
		 * @param	无
		 * @return	ArrayList:Manager_menuItem 对象的 ArrayList 数组
		 * @see		已菜单装显示
		 */
		public String toHtmlListMenu(){
			List list=find();
			buff=new StringBuffer();
			buff.append("<table  class='contentsTable'  border='0' cellpadding='0'  cellspacing='1'>");
			buff.append("<tr class='tr1'  align=center ><td width=20% >表单分类</td><td width=40% >表单名称</td><td >排序号</td><td  width=20% >操作</td></tr>");
			Iterator e=list.iterator();
			while(e.hasNext()){
				Form_tableItem rItem=(Form_tableItem)e.next();
				if(!rItem.getPid().equals("0")) break;
				buff.append("<tr class='tr2'>");
				buff.append("<td><img src='../img/folderopen.gif' border=0>"+rItem.getChinese_name()+"</td>");//表单分类
				buff.append("<td align='center'>&nbsp;</td>");//表单名称
				buff.append("<td align='center'>"+rItem.getSort_id()+"</td>");//排序号
				buff.append("<td align='center'><input  type='button' class='button' value='删除' onClick='javascript:remove_Category("+rItem.getId()+")'> <input  type='button' class='button' value='修改' onClick='javascript:edit_Category("+rItem.getId()+")'></td>");
				buff.append("</tr>");
				String levelStr="";
				level_num=1;
				for(int i=0;i<list.size();i++)
				{
					Form_tableItem subItem=(Form_tableItem)list.get(i);
					for(int j=0;j<level_num;j++)
						levelStr+="----";
					if(subItem.getPid().equals(rItem.getId()))
					{
						buff.append("<tr class='tr2'>");
						buff.append("<td align='center'>&nbsp;</td>");//表单分类
						buff.append("<td><img src='../img/page.gif' border=0>"+subItem.getChinese_name()+"("+Constants.T_FORM_PRE+subItem.getEnglish_name()+")</td>");//表单名称
						buff.append("<td align='center'>"+subItem.getSort_id()+"</td>");//排序号
						buff.append("<td align='center'><input  type='button' class='button' value='删除' onClick='javascript:remove_Table("+subItem.getId()+")'> <input  type='button' class='button' value='修改' onClick='javascript:edit_Table("+subItem.getId()+")'></td>");
						buff.append("</tr>");
						subHtmlListMenu(list,subItem.getId());
					}
					levelStr="";
				}
			}
			buff.append("</table>");
			return buff.toString();
		}
		
		/**
		 * @author	Administrator
		 * @param	无
		 * @return	ArrayList:Manager_menuItem 对象的 ArrayList 数组
		 * @see		子菜单递归
		 */
		public void subHtmlListMenu(List list,String id){
			level_num++;
			for(int i=0;i<list.size();i++)
			{
				Form_tableItem subItem=(Form_tableItem)list.get(i);
				String levelStr="";
				for(int j=0;j<level_num;j++)
					levelStr+="----";
				if(subItem.getPid().equals(id))
				{
					buff.append("<tr class='tr2'>");
					buff.append("<td>"+levelStr+"<input type='checkbox' name=checkvalue value='"+subItem.getId()+"'>"+subItem.getEnglish_name()+"("+subItem.getChinese_name()+")</td>");//表单名称
					buff.append("<td align='center'>"+subItem.getSort_id()+"</td>");//排序号
					buff.append("</tr>");
					subHtmlListMenu(list,subItem.getId());
					levelStr="";
				}
			}
			level_num--;
		}
		

		/**
		 * @author	Administrator
		 * @param	无
		 * @return	ArrayList:Manager_menuItem 对象的 ArrayList 数组
		 * @see		菜单下拉列表筐显示
		 */
		public String toHtmlOptionMenu(String pid,Form_tableItem findForm_tableItem){
			List list=find(findForm_tableItem);
			buff=new StringBuffer();
			Iterator e=list.iterator();
			while(e.hasNext()){
				Form_tableItem rItem=(Form_tableItem)e.next();
				if(!rItem.getPid().equals("0")) break;
				if(pid.equals(rItem.getId()))
					buff.append("<option value='"+rItem.getId()+"' selected>"+rItem.getChinese_name()+"</option>");
				else
					buff.append("<option value='"+rItem.getId()+"' >"+rItem.getChinese_name()+"</option>");
				String levelStr="";
				level_num=1;
				for(int i=0;i<list.size();i++)
				{
					Form_tableItem subItem=(Form_tableItem)list.get(i);
					for(int j=0;j<level_num;j++)
						levelStr+="----";
					if(subItem.getPid().equals(rItem.getId()))
					{
						if(pid.equals(subItem.getId()))
							buff.append("<option value='"+subItem.getId()+"' selected>"+levelStr+subItem.getChinese_name()+"("+subItem.getEnglish_name()+")</option>");
						else
							buff.append("<option value='"+subItem.getId()+"' >"+levelStr+subItem.getChinese_name()+"("+subItem.getEnglish_name()+")</option>");
						subHtmlOptionMenu(list,subItem.getId(),pid);
					}
					levelStr="";
				}
			}
			return buff.toString();
		}
		
		/**
		 * @author	Administrator
		 * @param	无
		 * @return	ArrayList:Manager_menuItem 对象的 ArrayList 数组
		 * @see		下拉列表筐递归
		 */
		public void subHtmlOptionMenu(List list,String id,String pid){
			level_num++;
			for(int i=0;i<list.size();i++)
			{
				Form_tableItem subItem=(Form_tableItem)list.get(i);
				String levelStr="";
				for(int j=0;j<level_num;j++)
					levelStr+="----";
				if(subItem.getPid().equals(id))
				{
					if(pid.equals(subItem.getId()))
						buff.append("<option value='"+subItem.getId()+"' selected>"+subItem.getEnglish_name()+"("+subItem.getChinese_name()+")</option>");
					else
						buff.append("<option value='"+subItem.getId()+"' >"+levelStr+subItem.getEnglish_name()+"("+subItem.getChinese_name()+")</option>");
					subHtmlOptionMenu(list,subItem.getId(),pid);
					levelStr="";
				}
			}
			level_num--;
		}



		/**
		 * @author	Administrator
		 * @param	id: 当前要修改记录的id号
		 * @return	FORM_TABLEItem
		 * @see		取出 FORM_TABLE 表中的指定id号的一条记录存储在一个 FORM_TABLEItem 对象中
		 */
		public Form_tableItem find(String id){
			try {
				String sql="select * from "+T.FORM_TABLE+" where id="+id;
				rs = executeQuery(sql);
				Form_tableItem item=new Form_tableItem();
				if(rs.next()){
					item.setId(rs.getString("id"));
					item.setPid(rs.getString("pid"));
					item.setNodepath(rs.getString("nodepath"));
					item.setChinese_name(rs.getString("chinese_name"));
					item.setEnglish_name(rs.getString("english_name"));
					item.setSort_id(rs.getString("sort_id"));
					item.setTable_type(rs.getString("table_type"));
					item.setUrl(rs.getString("url"));
					item.setFunc_id(rs.getString("func_id"));
				}
				return item;
			}catch(SQLException e){
				System.out.print(e.toString());
				return null;
			}
			finally{
				DBclose();
			}
		}
		
		public List findData(List listColumn,String form_table_id,String id){
			ArrayList rowList=new ArrayList();//查询表的查询数据,代表一行row
			try {
				//声名变量
				String table_name="";
				String sql1="select  * from "+T.FORM_TABLE+" where id="+form_table_id;
				rs=executeQuery(sql1);
				if(rs.next())
				{
					table_name=Constants.T_FORM_PRE+rs.getString("english_name");
				}
				
				String sql="select * from "+table_name+" where id="+id;
				rs = executeQuery(sql);
				 if(rs.next()){
	             	for(int i=0;i<listColumn.size();i++)
					{
	             		Form_columnItem item=(Form_columnItem)listColumn.get(i);
	             		rowList.add(rs.getString(item.getField_english_name()));
					}
	             }
			}catch(SQLException e){
				System.out.print(e.toString());
				return null;
			}
			finally{
				DBclose();
			}
			return rowList;
		}


		/**
		 * @author	Administrator
		 * @param	无
		 * @return	KeyId:插入记录时当前记录的id号
		 * @see		获得插入记录时当前记录的id号
		 */
		public int getKey(){
			return keyId;
		}

		/**
		 * @author	Administrator
		 * @param	无
		 * @return	(totalRecord):插入记录时当前记录的id号
		 * @see		获得插入记录时当前记录的id号
		 */
		public int getTotalRecord(){
	        return totalRecord;
	    }

		/**
		 * @author	Administrator
		 * @param	item:要添加的数据对象FORM_TABLEItem
		 * @return	boolean:是否添加成功,如果添加成功返回true,否则返回false
		 * @see		添加一条记录
		 */
		public boolean addTable(Form_tableItem item){
			try{
				StringBuffer sql=new StringBuffer();
				//声名变量
				sql.append("declare @id int , @nodepath varchar(255) ");
				//创建一个表,默认创建该表的字段
				
				sql.append("create table "+Constants.T_FORM_PRE+item.getEnglish_name()+"(");
				sql.append("id int IDENTITY (1, 1) NOT NULL,");
				sql.append("authorize_user varchar(255)  NULL ,");
				sql.append("authorize_department_id int  NULL ,");
				sql.append("authorize_department_nodepath varchar(255)  NULL ");
				sql.append(") ");

				//把创建的表,插入动态表单中
				sql.append("insert into "+T.FORM_TABLE+"("+
					"pid,"+
					"nodepath,"+
					"chinese_name,"+
					"english_name,"+