export.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.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import sys.dao.ConnectManager;
import sys.dao.bean.Company;
import sys.dao.bean.Linkman;
import sys.dao.bean.Personnel;

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

	// 查询客户
	@SuppressWarnings("deprecation")
	public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
	{
		// 参数初始化 
		ConnectManager cm = ConnectManager.newInstance();
		String dept = "";
		if (request.getSession().getAttribute("dept")!=null)
		{
			dept = request.getSession().getAttribute("dept").toString();
		}
		String personnelid = "";
		if (request.getSession().getAttribute("personnelid")!=null)
		{
			personnelid = request.getSession().getAttribute("personnelid").toString();
		}
		Integer stype = null;
		if (request.getSession().getAttribute("stype")!=null)
		{
			stype = Integer.valueOf(request.getSession().getAttribute("stype").toString());
		}
		String content = "";
		if (request.getSession().getAttribute("content")!=null)
		{
			content = request.getSession().getAttribute("content").toString();
		}
		String industry = "";
		if (request.getSession().getAttribute("industry")!=null)
		{
			industry = request.getSession().getAttribute("industry").toString();
		}
		String state = "";
		if (request.getSession().getAttribute("state")!=null)
		{
			state = request.getSession().getAttribute("state").toString();
		}
		String type = "";
		if (request.getSession().getAttribute("type")!=null)
		{
			type = request.getSession().getAttribute("type").toString();
		}
		String gotime = "";
		if (request.getSession().getAttribute("gotime")!=null)
		{
			gotime = request.getSession().getAttribute("gotime").toString();
		}
		String endtime = "";
		if (request.getSession().getAttribute("endtime")!=null)
		{
			endtime = request.getSession().getAttribute("endtime").toString();
		}
		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);
									rs.next();
									string = string + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;C类客户数量:" + rs.getInt(1);
									// 统计在查询结是中的D类客户数据
									sql = "select count(*) from company where " + sqltemp + 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 " + sqltemp + 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
						{
							request.getSession().removeAttribute("perid");
							// 开始拼装SQL
							// 这一段SQL是统计与查询通用的
							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
							{