visitlist.java

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

package crm.filiale;

import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import sys.dao.ConnectManager;
import sys.dao.bean.Personnel;
import sys.dao.bean.Visit;

public class VisitList extends HttpServlet
{
	private static final long serialVersionUID = 1L;

	//拜访记录的查询
	public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
	{
		boolean p = false;
		ConnectManager cm = ConnectManager.newInstance();
		RequestDispatcher rd = null;
		ResultSet rs = null;
		String dept = request.getParameter("deptid").replace(" ", "");
		String personnelid = request.getParameter("personnelid").replace(" ", "");
		String gotime = request.getParameter("gotime").replace(" ", "");
		String endtime = request.getParameter("endtime").replace(" ", "");
		String temp = "";
		String sql = "";
		String str = "";
		String string = "";
		List<Visit> visits = null;
		List<String> list = new ArrayList<String>();
		String sqltemp = "";
		String visittype = request.getParameter("visittype");
		rd = request.getRequestDispatcher("/crm/filiale/visitList.jsp");
		try
		{
			if (cm.getConnection())
			{
				try
				{
					if (!"".equals(personnelid))
					{
						str = str + " personnelid in (" + personnelid + ")";
						if (!("".equals(gotime) && "".equals(endtime)))
						{
							temp = temp + " and visittime in (select visittime from visit where visittime >= '" + gotime + "' and visittime <= '" + endtime + "')";
						}
						if (!visittype.equals(""))
						{
							temp = temp + " and visittype= '"+visittype+"'";
						}
						// 开始进行统计
						// 统计查询结果的总条数
						sql = "select count(*) from visit where visittype != 'D' and " + str + temp;
						rs = cm.executeQuery(sql);
						rs.next();
						int i = rs.getInt(1);
						// 查询是哪个业务员的记录
						sql = "select * from personnel where" + str;
						rs = cm.executeQuery(sql);
						if (rs.next())
						{
							list.add(rs.getString("realname") + "已查询到的拜访数是:" + 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 + "&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);
						}
					}
					else
					{
						if (!"".equals(dept))
						{
							str = str + " personnelid in (select personnelid from personnel where outdate is null and deptid = " + dept + ")";
							if (!("".equals(gotime) && "".equals(endtime)))
							{
								temp = temp + " and visittime in (select visittime from visit where visittime >= '" + gotime + "' and visittime <= '" + endtime + "')";
							}
							if (!visittype.equals(""))
							{
								temp = temp + " and visittype= '"+visittype+"'";
							}

							// 开始进行统计
							// 统计查询结果的总条数
							sql = "select count(*) from visit where visittype != 'D' and " + str + temp;
							rs = cm.executeQuery(sql);
							rs.next();
							int i = rs.getInt(1);
							// 查询是哪个部门的记录
							sql = "select * from dept where deptid = " + dept;
							rs = cm.executeQuery(sql);
							if (rs.next())
							{
								list.add(rs.getString("deptname") + "已查询到的拜访数是:" + 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 + "&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);
								// 查询出当前部门的员工
								List<Integer> pid = new ArrayList<Integer>();
								sql = "select personnelid from personnel where outdate is null and deptid = " + dept;
								rs = cm.executeQuery(sql);
								while (rs.next())
								{
									pid.add(rs.getInt(1));
								}
								// 开始统读业务员的信息
								for (Integer id : pid)
								{
									string = "";
									sqltemp = " personnelid = " + 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 personnel where" + sqltemp;
									rs = cm.executeQuery(sql);
									if (rs.next())
									{
										list.add(rs.getString("realname") + "已查询到的拜访数是:" + 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 " + sqltemp + temp;
										rs = cm.executeQuery(sql);
										rs.next();
										string = string + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;邮件拜访数量:" + rs.getInt(1);
									}
									list.add(string);
								}
							}
							request.setAttribute("list", list);
						}
						else
						{
							if(request.getSession().getAttribute("pos")!=null)
							{
								str = str + "personnelid in (select personnelid from personnel where outdate is null and deptid in (select deptid from dept where gradeid = " + ((Personnel) request.getSession().getAttribute("user")).getDept().getGradeid() + " and CHARINDEX('"+request.getSession().getAttribute("pos")+"',deptname)>0))";
							}
							else
							{
								str = str + "personnelid in (select personnelid from personnel where outdate is null and deptid in (select deptid from dept where gradeid = " + ((Personnel) request.getSession().getAttribute("user")).getDept().getGradeid() + "))";
							}
							if (!("".equals(gotime) && "".equals(endtime)))
							{
								str = str + " and visittime in (select visittime from visit where visittime >= '" + gotime + "' and visittime <= '" + endtime + "')";
							}
							if (!visittype.equals(""))
							{
								temp = temp + " and visittype= '"+visittype+"'";
							}