form_columnbean.java

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

					filter.append(" "+T.FORM_COLUMN+".column_formula like '%"+fvalue+"%' ");
					b=true;
				}
				fvalue="has_link_table";
				if(fvalue!=null &&   fvalue.length()>0)
				{
					if(b) filter.append(" and");
					filter.append(" "+T.FORM_COLUMN+".form_table_id = "+T.FORM_TABLE+".id   ");
					b=true;
				}
				StringBuffer sql=new StringBuffer();
				sql.append(" select top "+pageSize+" * from "+T.FORM_COLUMN+"  where ");
				if(b)
				{
					sql.append("  "+filter.toString()+" and ");
				}
				sql.append("  (id not in (SELECT TOP "+Integer.toString(nextPageSize)+" id from "+T.FORM_COLUMN+" ");
				if(b)
				{
					sql.append(" where "+filter.toString()+"  ");
				}
				sql.append(" order by id desc  ))  order by id desc ");
             rs = executeQuery(sql.toString());
             while(rs.next()){
                 Form_columnItem item=new Form_columnItem();
                 item.setId(rs.getString("id"));
                 item.setForm_table_id(rs.getString("form_table_id"));
                 item.setNodepath(rs.getString("nodepath"));
                 item.setField_english_name(rs.getString("field_english_name"));
                 item.setField_chinese_name(rs.getString("field_chinese_name"));
                 item.setSort_id(rs.getString("sort_id"));
                 item.setField_type(rs.getString("field_type"));
                 item.setField_width(rs.getString("field_width"));
                 item.setField_is_null(rs.getString("field_is_null"));
                 item.setField_is_primary(rs.getString("field_is_primary"));
                 item.setField_validate(rs.getString("field_validate"));
                 item.setJavascript(rs.getString("javascript"));
                 item.setIs_query(rs.getString("is_query"));
                 item.setIs_show(rs.getString("is_show"));
                 item.setDirect_type(rs.getString("direct_type"));
                 item.setCom_width(rs.getString("com_width"));
                 item.setCom_height(rs.getString("com_height"));
                 item.setCom_type(rs.getString("com_type"));
                 item.setEdit_type(rs.getString("edit_type"));
                 item.setAuto_value(rs.getString("auto_value"));
                 item.setGet_value_type(rs.getString("get_value_type"));
                 item.setFix_value(rs.getString("fix_value"));
                 item.setFix_table(rs.getString("fix_table"));
                 item.setFix_column_value(rs.getString("fix_column_value"));
                 item.setRow_formula(rs.getString("row_formula"));
                 item.setColumn_formula(rs.getString("column_formula"));
                 list.add(item);
             }
             String sql1="select count(*) as total from "+T.FORM_COLUMN;
             if(b)
               sql1+=" where "+filter.toString();
             rs = executeQuery(sql1);
             if(rs.next()){
                 totalRecord=rs.getInt("total");
             }
			}catch(SQLException e){
				System.out.print(e.toString());
			}
			finally{
				DBclose();
			}
			return list;
		}
		
		/**
		 * @author	Administrator
		 * @param	aItem: 查询的数据对象,page: 翻页时的当前页,pageSize: 翻页的每页显示的记录条数
		 * @return	ArrayList:Form_columnItem 对象的 ArrayList 数组
		 * @see		查出 form_column 表中的指定数据,页号,页大小的每一条记录存储在一个 Form_columnItem 对象中
		 */
		public List findD_Table(String form_table_id,int page,int pageSize,List queryFieldList){
			int nextPageSize=pageSize*(page-1);
			ArrayList list=new ArrayList();
			ArrayList listColumn=new ArrayList();
			String table_name="";
			try {
				String sql="select a.*,b.english_name as table_name from "+T.FORM_COLUMN+" as a,"+T.FORM_TABLE+" as b where a.form_table_id=b.id and a.is_show=2 and b.id="+form_table_id+" order by a.sort_id ";
				rs = executeQuery(sql);
	            while(rs.next()){
	            	table_name=Constants.T_FORM_PRE+rs.getString("table_name");
	                Form_columnItem item=new Form_columnItem();
	                item.setId(rs.getString("id"));
	                item.setForm_table_id(rs.getString("form_table_id"));
	                item.setNodepath(rs.getString("nodepath"));
	                item.setField_english_name(rs.getString("field_english_name"));
	                item.setField_chinese_name(rs.getString("field_chinese_name"));
	                item.setSort_id(rs.getString("sort_id"));
	                item.setField_type(rs.getString("field_type"));
	                item.setField_width(rs.getString("field_width"));
	                item.setField_is_null(rs.getString("field_is_null"));
	                item.setField_is_primary(rs.getString("field_is_primary"));
	                item.setField_validate(rs.getString("field_validate"));
	                item.setJavascript(rs.getString("javascript"));
	                item.setIs_query(rs.getString("is_query"));
	                item.setIs_show(rs.getString("is_show"));
	                item.setDirect_type(rs.getString("direct_type"));
	                item.setCom_width(rs.getString("com_width"));
	                item.setCom_height(rs.getString("com_height"));
	                item.setCom_type(rs.getString("com_type"));
	                item.setEdit_type(rs.getString("edit_type"));
	                item.setAuto_value(rs.getString("auto_value"));
	                item.setGet_value_type(rs.getString("get_value_type"));
	                item.setFix_value(rs.getString("fix_value"));
	                item.setFix_table(rs.getString("fix_table"));
	                item.setFix_column_value(rs.getString("fix_column_value"));
	                item.setRow_formula(rs.getString("row_formula"));
	                item.setColumn_formula(rs.getString("column_formula"));
	                listColumn.add(item);
	            }
	            list.add(listColumn);//添加列集 columns
			
				boolean b=false;
				//where 语句组合
				StringBuffer filter=new StringBuffer();
				String fvalue=null;
				for(int i=0;i<queryFieldList.size();i++)
				{
					Form_columnItem item=(Form_columnItem)queryFieldList.get(i);
					fvalue=item.getField_value();
					if(fvalue!=null &&   fvalue.length()>0)
					{
						if(b) filter.append(" and");
						//判断查询类型
						boolean flag=false;
						if(item.getField_type().equals("int")||item.getField_type().equals("float"))
						{
							filter.append(" "+item.getField_english_name()+" ="+fvalue+" ");
							flag=true;
						}
						if(item.getField_type().equals("varchar")&&(item.getCom_type().equals("radio")||item.getCom_type().equals("dropdownlist")))
						{
							filter.append(" "+item.getField_english_name()+" = '"+fvalue+"' ");
							flag=true;
						}
						if(item.getField_type().equals("calendar"))
						{
							filter.append(" DATEDIFF(day,"+item.getField_english_name()+",'"+fvalue+"')=0 ");
							flag=true;
						}
						if(!flag)
						{
							filter.append(" "+item.getField_english_name()+" like '%"+fvalue+"%' ");
						}
						b=true;
					}
				}
				
				StringBuffer selectString=new StringBuffer();//查询的列
				selectString.append("id");
				for(int i=0;i<listColumn.size();i++)
				{
					Form_columnItem item=(Form_columnItem)listColumn.get(i);
					//如果是自动计算行
					if(item.getGet_value_type().equals("4"))
						selectString.append(","+item.getRow_formula()+" as "+item.getField_english_name());
					else
						selectString.append(","+item.getField_english_name());
				}
				
				StringBuffer sql_table=new StringBuffer();
				sql_table.append(" select top "+pageSize+" "+selectString.toString()+" from "+table_name+"  where ");
				if(b)
				{
					sql_table.append("  "+filter.toString()+" and ");
				}
				sql_table.append("  (id not in (SELECT TOP "+Integer.toString(nextPageSize)+" id from "+table_name+" ");
				if(b)
				{
					sql_table.append(" where "+filter.toString()+"  ");
				}
				sql_table.append(" order by id desc  ))  order by id desc ");
             rs = executeQuery(sql_table.toString());
             
             ArrayList tablesList=new ArrayList();//查询表的查询数据,代表多行row
             while(rs.next()){
             	ArrayList rowList=new ArrayList();//查询表的查询数据,代表一行row
             	rowList.add(rs.getString("id"));
             	for(int i=0;i<listColumn.size();i++)
				{
             		Form_columnItem item=(Form_columnItem)listColumn.get(i);
             		rowList.add(rs.getString(item.getField_english_name()));
				}
             	tablesList.add(rowList);//添加一行row
             }
             list.add(tablesList);//添加一行row
             sql="select count(*) as total from "+table_name;
             if(b)
               sql+=" where "+filter.toString();
             rs = executeQuery(sql);
             if(rs.next()){
                 totalRecord=rs.getInt("total");
             }
			}catch(SQLException e){
				System.out.print(e.toString());
			}
			finally{
				DBclose();
			}
			return list;
		}
		
		public List export(String form_table_id,List queryFieldList){
			ArrayList list=new ArrayList();
			ArrayList listColumn=new ArrayList();
			String table_name="";
			String table_chinese_name="";
			try {
				String sql="select a.*,b.english_name as table_name,b.chinese_name as table_chinese_name from "+T.FORM_COLUMN+" as a,"+T.FORM_TABLE+" as b where a.form_table_id=b.id and a.is_show=2 and b.id="+form_table_id+" order by a.sort_id ";
				rs = executeQuery(sql);
	            while(rs.next()){
	            	table_name=Constants.T_FORM_PRE+rs.getString("table_name");
	            	table_chinese_name=rs.getString("table_name");
	                Form_columnItem item=new Form_columnItem();
	                item.setId(rs.getString("id"));
	                item.setForm_table_id(rs.getString("form_table_id"));
	                item.setNodepath(rs.getString("nodepath"));
	                item.setField_english_name(rs.getString("field_english_name"));
	                item.setField_chinese_name(rs.getString("field_chinese_name"));
	                item.setSort_id(rs.getString("sort_id"));
	                item.setField_type(rs.getString("field_type"));
	                item.setField_width(rs.getString("field_width"));
	                item.setField_is_null(rs.getString("field_is_null"));
	                item.setField_is_primary(rs.getString("field_is_primary"));
	                item.setField_validate(rs.getString("field_validate"));
	                item.setJavascript(rs.getString("javascript"));
	                item.setIs_query(rs.getString("is_query"));
	                item.setIs_show(rs.getString("is_show"));
	                item.setDirect_type(rs.getString("direct_type"));
	                item.setCom_width(rs.getString("com_width"));
	                item.setCom_height(rs.getString("com_height"));
	                item.setCom_type(rs.getString("com_type"));
	                item.setEdit_type(rs.getString("edit_type"));
	                item.setAuto_value(rs.getString("auto_value"));
	                item.setGet_value_type(rs.getString("get_value_type"));
	                item.setFix_value(rs.getString("fix_value"));
	                item.setFix_table(rs.getString("fix_table"));
	                item.setFix_column_value(rs.getString("fix_column_value"));
	                item.setRow_formula(rs.getString("row_formula"));
	                item.setColumn_formula(rs.getString("column_formula"));
	                listColumn.add(item);
	            }
	            list.add(listColumn);//添加列集 columns
			
				boolean b=false;
				//where 语句组合
				StringBuffer filter=new StringBuffer();
				String fvalue=null;
				for(int i=0;i<queryFieldList.size();i++)
				{
					Form_columnItem item=(Form_columnItem)queryFieldList.get(i);
					fvalue=item.getField_value();
					if(item.getField_value_type().equals("1"))//如果是固定值
					{
						fvalue=item.getField_value_min()+item.getField_value_max();
					}
					if(fvalue!=null &&   fvalue.length()>0)
					{
						if(b) filter.append(" and");
						//判断查询类型
						boolean flag=false;
						if(item.getField_type().equals("int")||item.getField_type().equals("float"))
						{
							if(item.getField_value_type().equals("0"))//如果是固定值
							{
								filter.append(" "+item.getField_english_name()+" ="+fvalue+" ");
							}
							if(item.getField_value_type().equals("1"))//如果是范围
							{
								//判断查询范围是否为两个同时查询 如果两个同时查询加 and
								boolean is_and=false;
								if(!item.getField_value_min().equals(""))
								{
									filter.append(" "+item.getField_english_name()+" "+item.getField_sign_min()+" "+item.getField_value_min()+"  ");
									is_and=true;
								}
								if(!item.getField_value_max().equals(""))
								{
									if(is_and) filter.append(" and");
									filter.append("  "+item.getField_english_name()+" "+item.getField_sign_max()+" "+item.getField_value_max()+" ");
								}
							}
							flag=true;
						}
						if(item.getField_type().equals("datetime"))
						{
							if(item.getField_value_type().equals("0"))//如果是固定值
							{
								filter.append(" DATEDIFF(day,"+item.getField_english_name()+",'"+fvalue+"')=0 ");
							}
							if(item.getField_value_type().equals("1"))//如果是范围
							{
								//判断查询范围是否为两个同时查询 如果两个同时查询加 and
								boolean is_and=false;
								if(!item.getField_value_min().equals(""))
								{
									filter.append(" DATEDIFF(day,'"+item.getField_value_min()+"',"+item.getField_english_name()+")"+item.getField_sign_min()+"0 ");
									is_and=true;
								}
								if(!item.getField_value_max().equals(""))
								{
									if(is_and) filter.append(" and");
									filter.append(" DATEDIFF(day,'"+item.getField_value_max()+"',"+item.getField_english_name()+")"+item.getField_sign_max()+"0 ");
								}
							}
							flag=true;
						}
						if(!flag&&(item.getCom_type().equals("dropdownlist")||item.getCom_type().equals("radio")||item.getCom_type().equals("checkbox")))
						{
								filter.append(" "+item.getField_english_name()+" = '"+fvalue+"' ");
								flag=true;
						}
						if(!flag)
						{
							if(item.getField_sign().equals("="))
								filter.append(" "+item.getField_english_name()+" = '"+fvalue+"' ");
							if(item.getField_sign().equals("like"))
								filter.append(" "+item.getField_english_name()+" like '%"+fvalue+"%' ");
						}
						b=true;
					}
				}
				
				StringBuffer selectString=new StringBuffer();//查询的列
				selectString.append("id");
				for(int i=0;i<listColumn.size();i++)
				{
					Form_columnItem item=(Form_columnItem)listColumn.get(i);
					//如果是自动计算行
					if(item.getGet_value_type().equals("4"))
						selectString.append(","+item.getRow_formula()+" as "+item.getField_english_name());
					else
						selectString.append(","+item.getField_english_name());
				}
				
				StringBuffer sql_table=new StringBuffer();
				sql_table.append(" select  "+selectString.toString()+" from "+table_name+"   ");
				
				if(b)
				{
					sql_table.append(" where "+filter.toString()+"  ");
				}
				sql_table.append(" order by id desc ");
             rs = executeQuery(sql_table.toString());