fcompany.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.Company;
import sys.dao.bean.Personnel;

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

	// 部门经理查询员工客户
	public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
	{
		// 参数初始化
		boolean p = false;
		ConnectManager cm = ConnectManager.newInstance();
		RequestDispatcher rd = null;
		String dept = request.getParameter("deptid").replace(" ", "");
		if (dept.equals(""))
		{
			request.getSession().setAttribute("dept", "");
		}
		else
		{
			request.getSession().setAttribute("dept", dept);
		}
		String personnelid = request.getParameter("personnelid").replace(" ", "");
		if (personnelid.equals(""))
		{
			request.getSession().setAttribute("personnelid", "");
		}
		else
		{
			request.getSession().setAttribute("personnelid", personnelid);
		}
		Integer stype = Integer.valueOf(request.getParameter("stype").replace(" ", ""));
		if (stype.equals(""))
		{
			request.getSession().setAttribute("stype", "");
		}
		else
		{
			request.getSession().setAttribute("stype", stype);
		}
		String content = request.getParameter("content").replace(" ", "");
		if (content.equals(""))
		{
			request.getSession().setAttribute("content", "");
		}
		else
		{
			request.getSession().setAttribute("content", content);
		}
		String industry = request.getParameter("industry").replace(" ", "");
		if (industry.equals(""))
		{
			request.getSession().setAttribute("industry", "");
		}
		else
		{
			request.getSession().setAttribute("industry", industry);
		}
		String state = request.getParameter("state").replace(" ", "");
		if (state.equals(""))
		{
			request.getSession().setAttribute("state", "");
		}
		else
		{
			request.getSession().setAttribute("state", state);
		}
		String type = request.getParameter("type").replace(" ", "");
		if (type.equals(""))
		{
			request.getSession().setAttribute("type", "");
		}
		else
		{
			request.getSession().setAttribute("type", stype);
		}
		String gotime = request.getParameter("gotime").replace(" ", "");
		if (gotime.equals(""))
		{
			request.getSession().setAttribute("gotime", "");
		}
		else
		{
			request.getSession().setAttribute("gotime", gotime);
		}
		String endtime = request.getParameter("endtime").replace(" ", "");
		if (endtime.equals(""))
		{
			request.getSession().setAttribute("endtime", "");
		}
		else
		{
			request.getSession().setAttribute("endtime", endtime);
		}
		List<Company> companies = null;
		ResultSet rs = null;
		String sql = "";
		List<String> list = new ArrayList<String>();
		String str = "";
		String temp = "";
		String sqltemp = "";
		try
		{
			// 打开数据库
			if (cm.getConnection())
			{
				try
				{
					// 开始拼装SQL
					if (!"".equals(personnelid))
					{
						request.getSession().setAttribute("perid", personnelid);
						// 这一段SQL是统计与查询通用的
						str = str + " personnelid in (" + personnelid + ")";
						if (!("".equals(gotime) && "".equals(endtime)))
						{
							temp = temp + " and indate in (select indate from company where indate >= '" + gotime + "' and indate <= '" + endtime + "')";
						}
						// 开始进行统计
						// 统计查询结果的总条数
						sql = "select count(*) from company where " + 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);
							String string = "";
							string = string + "A库数量:" + rs.getInt(1);
							// 统计在查询结是中的B库数据
							sql = "select count(*) from company where " + str + temp + "and companystate = 'B'";
							rs = cm.executeQuery(sql);
							rs.next();
							string = string + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;B库数量:" + rs.getInt(1);
							// 统计在查询结是中的A类客户数据
							sql = "select count(*) from company where " + str + temp + "and type = 'A'";
							rs = cm.executeQuery(sql);
							rs.next();
							string = string + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;A类客户数量:" + rs.getInt(1);
							// 统计在查询结是中的B类客户数据
							sql = "select count(*) from company where " + str + temp + "and type = 'B'";
							rs = cm.executeQuery(sql);
							rs.next();
							string = string + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;B类客户数量:" + rs.getInt(1);
							// 统计在查询结是中的C类客户数据
							sql = "select count(*) from company where " + str + temp + "and type = 'C'";
							rs = cm.executeQuery(sql);
							rs.next();
							string = string + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;C类客户数量:" + rs.getInt(1);
							// 统计在查询结是中的D类客户数据
							sql = "select count(*) from company where " + str + temp + "and type = 'D'";
							rs = cm.executeQuery(sql);
							rs.next();
							string = string + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;D类客户数量:" + rs.getInt(1);
							// 统计在查询结是中的E类客户数据
							sql = "select count(*) from company where " + str + temp + "and type = 'E'";
							rs = cm.executeQuery(sql);
							rs.next();
							string = string + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;E类客户数量:" + rs.getInt(1);
							list.add(string);
							request.setAttribute("list", list);
						}
					}
					else
					{
						// 开始拼装SQL
						if (!"".equals(dept))
						{
							request.getSession().removeAttribute("perid");
							// 这一段SQL是统计与查询通用的
							str = str + " personnelid in (select personnelid from personnel where outdate is null and deptid = " + dept + ")";
							if (!("".equals(gotime) && "".equals(endtime)))
							{
								temp = temp + " and indate in (select indate from company where indate >= '" + gotime + "' and indate <= '" + endtime + "')";
							}
							// 开始进行统计
							// 统计查询结果的总条数
							sql = "select count(*) from company where " + 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);
								// 统计在查询结是中的A库数据
								sql = "select count(*) from company where " + str + temp + "and companystate = 'A'";
								rs = cm.executeQuery(sql);
								rs.next();
								String string = "";
								string = string + "A库数量:" + rs.getInt(1);
								// 统计在查询结是中的B库数据
								sql = "select count(*) from company where " + str + temp + "and companystate = 'B'";
								rs = cm.executeQuery(sql);
								rs.next();
								string = string + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;B库数量:" + rs.getInt(1);
								// 统计在查询结是中的A类客户数据
								sql = "select count(*) from company where " + str + temp + "and type = 'A'";
								rs = cm.executeQuery(sql);
								rs.next();
								string = string + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;A类客户数量:" + rs.getInt(1);
								// 统计在查询结是中的B类客户数据
								sql = "select count(*) from company where " + str + temp + "and type = 'B'";
								rs = cm.executeQuery(sql);
								rs.next();
								string = string + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;B类客户数量:" + rs.getInt(1);
								// 统计在查询结是中的C类客户数据
								sql = "select count(*) from company where " + str + temp + "and type = 'C'";
								rs = cm.executeQuery(sql);
								rs.next();
								string = string + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;C类客户数量:" + rs.getInt(1);
								// 统计在查询结是中的D类客户数据
								sql = "select count(*) from company where " + str + temp + "and type = 'D'";
								rs = cm.executeQuery(sql);
								rs.next();
								string = string + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;D类客户数量:" + rs.getInt(1);
								// 统计在查询结是中的E类客户数据
								sql = "select count(*) from company where " + str + temp + "and type = 'E'";
								rs = cm.executeQuery(sql);
								rs.next();
								string = string + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;E类客户数量:" + 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 company where " + sqltemp + temp;
									rs = cm.executeQuery(sql);
									rs.next();
									i = rs.getInt(1);
									// 查询是业务员的记录
									sql = "select * from personnel where " + sqltemp;
									rs = cm.executeQuery(sql);
									rs.next();
									list.add(rs.getString("realname") + "已查询到的客户数是:" + i);
									// 统计在查询结是中的A库数据
									sql = "select count(*) from company where " + sqltemp + temp + "and companystate = 'A'";
									rs = cm.executeQuery(sql);
									rs.next();
									string = string + "A库数量:" + rs.getInt(1);
									// 统计在查询结是中的B库数据
									sql = "select count(*) from company where " + sqltemp + temp + "and companystate = 'B'";
									rs = cm.executeQuery(sql);
									rs.next();
									string = string + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;B库数量:" + rs.getInt(1);
									// 统计在查询结是中的A类客户数据
									sql = "select count(*) from company where " + sqltemp + temp + "and type = 'A'";
									rs = cm.executeQuery(sql);
									rs.next();
									string = string + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;A类客户数量:" + rs.getInt(1);
									// 统计在查询结是中的B类客户数据
									sql = "select count(*) from company where " + sqltemp + temp + "and type = 'B'";
									rs = cm.executeQuery(sql);
									rs.next();
									string = string + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;B类客户数量:" + rs.getInt(1);
									// 统计在查询结是中的C类客户数据
									sql = "select count(*) from company where " + sqltemp + temp + "and type = 'C'";
									rs = cm.executeQuery(sql);