visitlist.java

使用java写的ERP系统,功能比较全

							// 开始进行统计
							// 统计查询结果的总条数
							sql = "select count(*) from visit where visittype != 'D' and " + str + temp;
							rs = cm.executeQuery(sql);
							rs.next();
							int i = rs.getInt(1);
							if(request.getSession().getAttribute("pos")!=null)
							{
								list.add("分公司"+request.getSession().getAttribute("pos")+"部已查询到的拜访数是:" + i);
							}
							else
							{
								list.add("分公司已查询到的拜访数是:" + i);
							}
							//统计在查询结是中的电话拜访数据
							sql = "select count(*) from visit where visittype = 'A' and " + str + temp;
							rs = cm.executeQuery(sql);
							rs.next();
							string = string + "电话拜访数量:" + rs.getInt(1);
							//统计在查询结是中的上门拜访数据
							sql = "select count(*) from visit where visittype = 'B' and " + str + temp;
							rs = cm.executeQuery(sql);
							rs.next();
							string = string + "     上门拜访数量:" + rs.getInt(1);
							//统计在查询结是中的邮件拜访数据
							sql = "select count(*) from visit where visittype = 'C' and " + str + temp;
							rs = cm.executeQuery(sql);
							rs.next();
							string = string + "     邮件拜访数量:" + rs.getInt(1);
							list.add(string);
							// 查询出所有
							List<Integer> did = new ArrayList<Integer>();
							if(request.getSession().getAttribute("pos")!=null)
							{
								sql = "select deptid from dept where gradeid = " + ((Personnel) request.getSession().getAttribute("user")).getDept().getGradeid()+" and CHARINDEX('"+request.getSession().getAttribute("pos")+"',deptname)>0";
							}
							else
							{
								sql = "select deptid from dept where gradeid = " + ((Personnel) request.getSession().getAttribute("user")).getDept().getGradeid();
							}
							rs = cm.executeQuery(sql);
							while (rs.next())
							{
								if (rs.getInt(1) != ((Personnel) request.getSession().getAttribute("user")).getDeptid().intValue())
								{
									did.add(rs.getInt(1));
								}
							}
							// 开始统读部门的信息
							for (Integer id : did)
							{
								string = "";
								sqltemp = " personnelid in (select personnelid from personnel where outdate is null and deptid = " + id + ")";
								sql = "select count(*) from visit where visittype != 'D' and " + sqltemp + temp;
								rs = cm.executeQuery(sql);
								rs.next();
								i = rs.getInt(1);
								// 查询部门的记录
								sql = "select * from dept where deptid = " + id;
								rs = cm.executeQuery(sql);
								rs.next();
								list.add(rs.getString("deptname") + "已查询到的客户数是:" + i);
								//统计在查询结是中的电话拜访数据
								sql = "select count(*) from visit where visittype = 'A' and " + sqltemp + temp;
								rs = cm.executeQuery(sql);
								rs.next();
								string = string + "电话拜访数量:" + rs.getInt(1);
								//统计在查询结是中的上门拜访数据
								sql = "select count(*) from visit where visittype = 'B' and " + sqltemp + temp;
								rs = cm.executeQuery(sql);
								rs.next();
								string = string + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;上门拜访数量:" + rs.getInt(1);
								//统计在查询结是中的邮件拜访数据
								sql = "select count(*) from visit where visittype = 'C' and " + str + temp;
								rs = cm.executeQuery(sql);
								rs.next();
								string = string + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;邮件拜访数量:" + rs.getInt(1);
								list.add(string);
							}
							request.setAttribute("list", list);
						}
					}
					str += temp;
					sql = "select * from visit where visittype != 'D' and " + str + " order by visittime desc";
					rs = cm.executeQuery(sql);
					if (rs.next())
					{
						p = true;
						visits = new ArrayList<Visit>();
						do
						{
							Visit visit = new Visit();
							visit.setVisitid(rs.getInt("visitid"));
							visit.setVisittype(rs.getString("visittype"));
							visit.setVisittime(rs.getDate("visittime"));
							visit.setLinkmanid(rs.getInt("linkmanid"));
							visit.setPersonnelid(rs.getInt("personnelid"));
							visit.setCompanyid(rs.getInt("companyid"));
							visit.setVisitlog(rs.getString("visitlog"));
							visits.add(visit);
						}
						while (rs.next());
					}
					rs.close();
				}
				catch (SQLException e)
				{
					e.printStackTrace();
				}
			}
			else
			{
				request.setAttribute("error", "系统故障，请重试或联系管理员");
				rd = request.getRequestDispatcher("/error.jsp");
			}
			if (p)
			{
				// 页面数据量
				int pageSize = 10;
				// 设置当前是第一页
				int page = 1;
				// 判断是否有传参要得到第几页，同时初始化所有数据
				if (request.getParameter("page") != null)
				{
					page = new Integer(request.getParameter("page"));
				}
				// 总页面数
				int pageCount = visits.size() / pageSize + (visits.size() % pageSize == 0 ? 0 : 1);
				request.setAttribute("csize", "总拜访量:" + visits.size() + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;总页数:" + pageCount);
				// 定位当前显示页的第一行
				int currentPageFirstRow = (page - 1) * pageSize;
				// 得到当前页的数据
				List<Visit> pageRows = new ArrayList<Visit>();
				for (int i = currentPageFirstRow; i < currentPageFirstRow + pageSize; i++)
				{
					if (i >= visits.size())
					{
						break;
					}
					pageRows.add((Visit) visits.get(i));
				}
				// 设当前页的HTML游标
				String htmlPage = "";
				if (visits.size() != 0)
				{

					if (page != 1)
					{
						htmlPage = htmlPage + "&nbsp;<a href='fvList?visittype="+visittype+"&page=1&deptid=" + dept + "&personnelid=" + personnelid + "&gotime=" + gotime + "&endtime=" + endtime + "' class='pagenum'>首页</a>";
						htmlPage = htmlPage + "&nbsp;<a href='fvList?visittype="+visittype+"&page=" + (page - 1) + "&deptid=" + dept + "&personnelid=" + personnelid + "&gotime=" + gotime + "&endtime=" + endtime + "' class='pagenum'>上一页</a>";
					}
					int i = page / 10;
					if (i == 0)
					{
						++i;
					}
					else
					{
						i *= 10;
						++i;
					}
					int j = (i + 9);
					if (j > pageCount)
					{
						j = pageCount;
					}
					for (; i <= j; i++)
					{
						if (i != page)
						{
							htmlPage = htmlPage + "&nbsp;<a href='fvList?visittype="+visittype+"&page=" + i + "&deptid=" + dept + "&personnelid=" + personnelid + "&gotime=" + gotime + "&endtime=" + endtime + "' class='pagenum'>" + i + "</a>";
						}
						else
						{
							htmlPage = htmlPage + "&nbsp;" + i;
						}
					}
					if (page != pageCount)
					{
						htmlPage = htmlPage + "&nbsp;<a href='fvList?visittype="+visittype+"&page=" + (page + 1) + "&deptid=" + dept + "&personnelid=" + personnelid + "&gotime=" + gotime + "&endtime=" + endtime + "' class='pagenum'>下一页</a>";
						htmlPage = htmlPage + "&nbsp;<a href='fvList?visittype="+visittype+"&page=" + (pageCount) + "&deptid=" + dept + "&personnelid=" + personnelid + "&gotime=" + gotime + "&endtime=" + endtime + "' class='pagenum'>尾页</a>";
					}

				}
				request.setAttribute("htmlPage", htmlPage);
				request.setAttribute("pageRows", pageRows);
			}
		}
		finally
		{
			cm.close();
		}
		rd.forward(request, response);
	}
}