finacial_projectbean.java

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

					b=true;
				}
				fvalue=aItem.getRegister_name();
				if(fvalue!=null &&   fvalue.length()>0)
				{
					if(b) filter.append(" and");
					filter.append(" "+T.FINACIAL_PROJECT+".register_name like '%"+fvalue+"%' ");
					b=true;
				}
				fvalue=aItem.getRegister_department_id();
				if(fvalue!=null &&   fvalue.length()>0)
				{
					if(b) filter.append(" and");
					filter.append(" "+T.FINACIAL_PROJECT+".register_department_id ="+fvalue+" ");
					b=true;
				}
				fvalue=aItem.getRegister_department_nodepath();
				if(fvalue!=null &&   fvalue.length()>0)
				{
					if(b) filter.append(" and");
					filter.append("  ','+"+T.FINACIAL_PROJECT+".register_department_nodepath+',' like ',%"+fvalue+"%,'   ");
					b=true;
				}
				fvalue=aItem.getRegister_department();
				if(fvalue!=null &&   fvalue.length()>0)
				{
					if(b) filter.append(" and");
					filter.append(" "+T.FINACIAL_PROJECT+".register_department like '%"+fvalue+"%' ");
					b=true;
				}
				fvalue=aItem.getRegister_area_id();
				if(fvalue!=null &&   fvalue.length()>0)
				{
					if(b) filter.append(" and");
					filter.append(" "+T.FINACIAL_PROJECT+".register_area_id ="+fvalue+" ");
					b=true;
				}
				fvalue=aItem.getRegister_area_nodepath();
				if(fvalue!=null &&   fvalue.length()>0)
				{
					if(b) filter.append(" and");
					filter.append("  ','+"+T.FINACIAL_PROJECT+".register_area_nodepath+',' like ',%"+fvalue+"%,'   ");
					b=true;
				}
				fvalue=aItem.getRegister_area();
				if(fvalue!=null &&   fvalue.length()>0)
				{
					if(b) filter.append(" and");
					filter.append(" "+T.FINACIAL_PROJECT+".register_area like '%"+fvalue+"%' ");
					b=true;
				}
				fvalue=aItem.getSend_people();
				if(fvalue!=null &&   fvalue.length()>0)
				{
					if(b) filter.append(" and");
					filter.append(" "+T.FINACIAL_PROJECT+".send_people like '%"+fvalue+"%' ");
					b=true;
				}
				fvalue=aItem.getSend_department();
				if(fvalue!=null &&   fvalue.length()>0)
				{
					if(b) filter.append(" and");
					filter.append(" "+T.FINACIAL_PROJECT+".send_department like '%"+fvalue+"%' ");
					b=true;
				}
				fvalue=aItem.getSend_area();
				if(fvalue!=null &&   fvalue.length()>0)
				{
					if(b) filter.append(" and");
					filter.append(" "+T.FINACIAL_PROJECT+".send_area like '%"+fvalue+"%' ");
					b=true;
				}
				fvalue=aItem.getSend_date();
				if(fvalue!=null &&   fvalue.length()>0)
				{
					if(b) filter.append(" and");
					filter.append(" DATEDIFF(day,"+T.FINACIAL_PROJECT+".send_date,'"+fvalue+"')=0 ");
					b=true;
				}
				fvalue=aItem.getWorkflow_id();
				if(fvalue!=null &&   fvalue.length()>0)
				{
					if(b) filter.append(" and");
					filter.append(" "+T.FINACIAL_PROJECT+".workflow_id ="+fvalue+" ");
					b=true;
				}
				fvalue=aItem.getWorkflow_state_id();
				if(fvalue!=null &&   fvalue.length()>0)
				{
					if(b) filter.append(" and");
					filter.append(" "+T.FINACIAL_PROJECT+".workflow_state_id ="+fvalue+" ");
					b=true;
				}
				fvalue=aItem.getWorkflow_state_name();
				if(fvalue!=null &&   fvalue.length()>0)
				{
					if(b) filter.append(" and");
					filter.append(" "+T.FINACIAL_PROJECT+".workflow_state_name like '%"+fvalue+"%' ");
					b=true;
				}
				fvalue=aItem.getApproval_type();
				if(fvalue!=null &&   fvalue.length()>0)
				{
					if(b) filter.append(" and");
					filter.append(" "+T.FINACIAL_PROJECT+".approval_type ="+fvalue+" ");
					b=true;
				}
				fvalue=aItem.getAppraisal_method();
				if(fvalue!=null &&   fvalue.length()>0)
				{
					if(b) filter.append(" and");
					filter.append(" "+T.FINACIAL_PROJECT+".appraisal_method ="+fvalue+" ");
					b=true;
				}
				fvalue=aItem.getApproval_user();
				if(fvalue!=null &&   fvalue.length()>0)
				{
					if(b) filter.append(" and");
					filter.append(" "+T.FINACIAL_PROJECT+".approval_user like '%"+fvalue+"%' ");
					b=true;
				}
				fvalue=aItem.getApproval_name();
				if(fvalue!=null &&   fvalue.length()>0)
				{
					if(b) filter.append(" and");
					filter.append(" "+T.FINACIAL_PROJECT+".approval_name like '%"+fvalue+"%' ");
					b=true;
				}
				fvalue=aItem.getApproval_departmentid();
				if(fvalue!=null &&   fvalue.length()>0)
				{
					if(b) filter.append(" and");
					filter.append(" "+T.FINACIAL_PROJECT+".approval_departmentid like '%"+fvalue+"%' ");
					b=true;
				}
				fvalue=aItem.getApproval_department();
				if(fvalue!=null &&   fvalue.length()>0)
				{
					if(b) filter.append(" and");
					filter.append(" "+T.FINACIAL_PROJECT+".approval_department like '%"+fvalue+"%' ");
					b=true;
				}
				fvalue=aItem.getReceive_user();
				if(fvalue!=null &&   fvalue.length()>0)
				{
					if(b) filter.append(" and");
					filter.append(" "+T.FINACIAL_PROJECT+".receive_user like '%"+fvalue+"%' ");
					b=true;
				}
				fvalue=aItem.getReceive_name();
				if(fvalue!=null &&   fvalue.length()>0)
				{
					if(b) filter.append(" and");
					filter.append(" "+T.FINACIAL_PROJECT+".receive_name like '%"+fvalue+"%' ");
					b=true;
				}
				fvalue=aItem.getFunc_id();
				if(fvalue!=null &&   fvalue.length()>0)
				{
					if(b) filter.append(" and");
					filter.append(" "+T.FINACIAL_PROJECT+".func_id like '%"+fvalue+"%' ");
					b=true;
				}
				fvalue=aItem.getState_type();
				if(fvalue!=null &&   fvalue.length()>0)
				{
					if(b) filter.append(" and");
					filter.append(" "+T.FINACIAL_PROJECT+".state_type ="+fvalue+" ");
					b=true;
				}
				fvalue="has_link_table";
				if(fvalue!=null &&   fvalue.length()>0)
				{
					if(b) filter.append(" and");
					filter.append(" "+T.FINACIAL_PROJECT+".checkout_method =  checkout_method_dictionary.code and  ");
					filter.append(" checkout_method_dictionary.num = 28   ");
					b=true;
				}
				StringBuffer sql=new StringBuffer();
				sql.append(" select top "+pageSize+"  "+T.FINACIAL_PROJECT+".* ,checkout_method_dictionary.code_name as  checkout_method_dictionaryname,(select sum(in_price) from "+T.FINACIAL_PROJECT_IN_RECORD+" where finacial_project_id="+T.FINACIAL_PROJECT+".id) as in_record_price,(select sum(out_price) from "+T.FINACIAL_PROJECT_OUT_RECORD+" where finacial_project_id="+T.FINACIAL_PROJECT+".id) as out_record_price  from   "+T.FINACIAL_PROJECT+" ,"+T.OA_DICTIONARY+"  as  checkout_method_dictionary   where ");
				if(b)
				{
					sql.append("  "+filter.toString()+" and ");
				}
				sql.append("  ("+T.FINACIAL_PROJECT+".id not in (SELECT TOP "+Integer.toString(nextPageSize)+" "+T.FINACIAL_PROJECT+".id from   "+T.FINACIAL_PROJECT+" ,"+T.OA_DICTIONARY+"  as  checkout_method_dictionary  ");
				if(b)
				{
					sql.append(" where "+filter.toString()+"  ");
				}
				sql.append(" order by "+T.FINACIAL_PROJECT+".id desc  ))  order by "+T.FINACIAL_PROJECT+".id desc ");
				rs = executeQuery(sql.toString());
				while(rs.next()){
					Finacial_projectItem item=new Finacial_projectItem();
					item.setId(rs.getString("id"));
					item.setCrm_project_info_id(rs.getString("crm_project_info_id"));
					item.setCrm_customer_name(rs.getString("crm_customer_name"));
					item.setCrm_project_name(rs.getString("crm_project_name"));
					item.setCrm_customer_id(rs.getString("crm_customer_id"));
					item.setCrm_customer_contact_id(rs.getString("crm_customer_contact_id"));
					item.setCrm_customer_contact_name(rs.getString("crm_customer_contact_name"));
					item.setProject_price(rs.getString("project_price"));
					//收入记录,付出记录相减得实际收入
					double in_record_price=rs.getDouble("in_record_price");
					double out_record_price=rs.getDouble("out_record_price");
					System.out.println(in_record_price);
					System.out.println(out_record_price);
					item.setReal_price(Double.toString(Arith.sub(in_record_price,out_record_price)));
					
					item.setDuty(rs.getString("duty"));
					item.setCheckout_method(rs.getString("checkout_method"));
					item.setSign_date(rs.getString("sign_date"));
					item.setEnd_date(rs.getString("end_date"));
					item.setProject_manager(rs.getString("project_manager"));
					item.setMemo(rs.getString("memo"));
					item.setRegister_date(rs.getString("register_date"));
					item.setRegister_username(rs.getString("register_username"));
					item.setRegister_name(rs.getString("register_name"));
					item.setRegister_department_id(rs.getString("register_department_id"));
					item.setRegister_department_nodepath(rs.getString("register_department_nodepath"));
					item.setRegister_department(rs.getString("register_department"));
					item.setRegister_area_id(rs.getString("register_area_id"));
					item.setRegister_area_nodepath(rs.getString("register_area_nodepath"));
					item.setRegister_area(rs.getString("register_area"));
					item.setSend_people(rs.getString("send_people"));
					item.setSend_department(rs.getString("send_department"));
					item.setSend_area(rs.getString("send_area"));
					item.setSend_date(rs.getString("send_date"));
					item.setWorkflow_id(rs.getString("workflow_id"));
					item.setWorkflow_state_id(rs.getString("workflow_state_id"));
					item.setWorkflow_state_name(rs.getString("workflow_state_name"));
					item.setApproval_type(rs.getString("approval_type"));
					item.setAppraisal_method(rs.getString("appraisal_method"));
					item.setApproval_user(rs.getString("approval_user"));
					item.setApproval_name(rs.getString("approval_name"));
					item.setApproval_departmentid(rs.getString("approval_departmentid"));
					item.setApproval_department(rs.getString("approval_department"));
					item.setReceive_user(rs.getString("receive_user"));
					item.setReceive_name(rs.getString("receive_name"));
					item.setFunc_id(rs.getString("func_id"));
					item.setState_type(rs.getString("state_type"));
					item.setCheckout_method_dictionaryname(rs.getString("checkout_method_dictionaryname"));
					list.add(item);
				}
				String sql1="select count(*) as total from   "+T.FINACIAL_PROJECT+" ,"+T.OA_DICTIONARY+"  as  checkout_method_dictionary  ";
				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	无
		 * @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:要添加的数据对象Finacial_projectItem
		 * @return	boolean:是否添加成功,如果添加成功返回true,否则返回false
		 * @see		添加一条记录
		 */
		public boolean addItem(Finacial_projectItem item){
			try{
				String sql="insert into "+T.FINACIAL_PROJECT+"("+
							"crm_project_info_id,"+
							"crm_customer_name,"+
							"crm_project_name,"+
							"crm_customer_id,"+
							"crm_customer_contact_id,"+
							"crm_customer_contact_name,"+
							"project_price,"+
							"real_price,"+
							"duty,"+
							"checkout_method,"+
							"sign_date,"+
							"end_date,"+
							"project_manager,"+
							"memo,"+
							"register_date,"+
							"register_username,"+
							"register_name,"+
							"register_department_id,"+
							"register_department_nodepath,"+
							"register_department,"+
							"register_area_id,"+
							"register_area_nodepath,"+
							"register_area,"+
							"send_people,"+
							"send_department,"+
							"send_area,"+
							"send_date,"+
							"workflow_id,"+
							"workflow_state_id,"+
							"workflow_state_name,"+
							"approval_type,"+
							"appraisal_method,"+
							"approval_user,"+
							"approval_name,"+
							"approval_departmentid,"+
							"approval_department,"+
							"receive_user,"+
							"receive_name,"+
							"func_id,"+
							"state_type)values("+
							""+item.getCrm_project_info_id()+","+
							"'"+item.getCrm_customer_name()+"',"+
							"'"+item.getCrm_project_name()+"',"+
							""+item.getCrm_customer_id()+","+
							""+item.getCrm_customer_contact_id()+","+
							"'"+item.getCrm_customer_contact_name()+"',"+
							""+item.getProject_price()+","+
							""+item.getReal_price()+","+
							"'"+item.getDuty()+"',"+
							""+item.getCheckout_method()+","+
							"'"+item.getSign_date()+"',"+
							"'"+item.getEnd_date()+"',"+
							"'"+item.getProject_manager()+"',"+
							"'"+item.getMemo()+"',"+
							"'"+item.getRegister_date()+"',"+
							"'"+item.getRegister_username()+"',"+
							"'"+item.getRegister_name()+"',"+