📄 fcompany.java
字号:
rs.next();
string = string + " C类客户数量:" + rs.getInt(1);
// 统计在查询结是中的D类客户数据
sql = "select count(*) from company where " + sqltemp + temp + "and type = 'D'";
rs = cm.executeQuery(sql);
rs.next();
string = string + " D类客户数量:" + rs.getInt(1);
// 统计在查询结是中的E类客户数据
sql = "select count(*) from company where " + sqltemp + temp + "and type = 'E'";
rs = cm.executeQuery(sql);
rs.next();
string = string + " 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
{
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)))
{
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);
if (request.getSession().getAttribute("pos") != null)
{
list.add("分公司" + request.getSession().getAttribute("pos") + "部已查询到的客户数是:" + i);
}
else
{
list.add("分公司已查询到的客户数是:" + 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 + " B库数量:" + rs.getInt(1);
// 统计在查询结是中的A类客户数据
sql = "select count(*) from company where " + str + temp + "and type = 'A'";
rs = cm.executeQuery(sql);
rs.next();
string = string + " A类客户数量:" + rs.getInt(1);
// 统计在查询结是中的B类客户数据
sql = "select count(*) from company where " + str + temp + "and type = 'B'";
rs = cm.executeQuery(sql);
rs.next();
string = string + " B类客户数量:" + rs.getInt(1);
// 统计在查询结是中的C类客户数据
sql = "select count(*) from company where " + str + temp + "and type = 'C'";
rs = cm.executeQuery(sql);
rs.next();
string = string + " C类客户数量:" + rs.getInt(1);
// 统计在查询结是中的D类客户数据
sql = "select count(*) from company where " + str + temp + "and type = 'D'";
rs = cm.executeQuery(sql);
rs.next();
string = string + " D类客户数量:" + rs.getInt(1);
// 统计在查询结是中的E类客户数据
sql = "select count(*) from company where " + str + temp + "and type = 'E'";
rs = cm.executeQuery(sql);
rs.next();
string = string + " E类客户数量:" + 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 company where " + 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);
// 统计在查询结是中的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 + " B库数量:" + rs.getInt(1);
// 统计在查询结是中的A类客户数据
sql = "select count(*) from company where " + sqltemp + temp + "and type = 'A'";
rs = cm.executeQuery(sql);
rs.next();
string = string + " A类客户数量:" + rs.getInt(1);
// 统计在查询结是中的B类客户数据
sql = "select count(*) from company where " + sqltemp + temp + "and type = 'B'";
rs = cm.executeQuery(sql);
rs.next();
string = string + " B类客户数量:" + rs.getInt(1);
// 统计在查询结是中的C类客户数据
sql = "select count(*) from company where " + sqltemp + temp + "and type = 'C'";
rs = cm.executeQuery(sql);
rs.next();
string = string + " C类客户数量:" + rs.getInt(1);
// 统计在查询结是中的D类客户数据
sql = "select count(*) from company where " + sqltemp + temp + "and type = 'D'";
rs = cm.executeQuery(sql);
rs.next();
string = string + " D类客户数量:" + rs.getInt(1);
// 统计在查询结是中的E类客户数据
sql = "select count(*) from company where " + sqltemp + temp + "and type = 'E'";
rs = cm.executeQuery(sql);
rs.next();
string = string + " E类客户数量:" + rs.getInt(1);
list.add(string);
}
request.setAttribute("list", list);
}
}
str += temp;
if (!"".equals(state))
{
str = str + " and companystate = '" + state + "'";
}
if (!"".equals(type))
{
str = str + " and type = '" + type + "'";
}
if (!"".equals(content))
{
switch (stype)
{
case 1:
str += " and nameparticular like '%" + content + "%'";
break;
case 2:
str += " and companyaddress like '%" + content + "%'";
break;
case 3:
str += " and companyid = (select companyid from linkman where linkmanmoblie = '" + content + "')";
break;
case 4:
str += " and companyid = (select companyid from linkman where linkmanphone = '" + content + "')";
break;
default:
str += " and companyid = (select companyid from linkman where linkmanemail = '" + content + "')";
break;
}
}
if (!"".equals(industry))
{
str = str + " and industryid = " + Integer.valueOf(industry);
}
sql = "select * from company where " + str + " order by adddate desc";
rs = cm.executeQuery(sql);
if (rs.next())
{
p = true;
companies = new ArrayList<Company>();
do
{
Company company = new Company();
company.setCompanyid(rs.getInt("companyid"));
company.setNamekeywords(rs.getString("namekeywords"));
company.setNameparticular(rs.getString("nameparticular"));
company.setDistrictid(rs.getInt("districtid"));
company.setCompanyaddress(rs.getString("companyaddress"));
company.setType(rs.getString("type"));
company.setNeturl(rs.getString("neturl"));
company.setCompanytype(rs.getString("companytype"));
company.setCompanystate(rs.getString("companystate"));
company.setAdddate(rs.getDate("adddate"));
company.setIndate(rs.getDate("indate"));
company.setCompanynarrate(rs.getString("companynarrate"));
company.setPersonnelid(rs.getInt("personnelid"));
company.setAccepterid(rs.getInt("accepterid"));
company.setLastPersonnelid(rs.getInt("lastPersonnel"));
company.setLastvisitdate(rs.getDate("lastvisitdate"));
company.setVisitcount(rs.getInt("visitcount"));
company.setIndustryid(rs.getInt("industryid"));
companies.add(company);
}
while (rs.next());
}
rs.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
else
{
request.setAttribute("error", "系统故障，请重试或联系管理员");
rd = request.getRequestDispatcher("/error.jsp");
}
rd = request.getRequestDispatcher("/crm/filiale/fcompany.jsp");
if (p)
{
// 页面数据量
int pageSize = 15;
// 设置当前是第一页
int page = 1;
// 判断是否有传参要得到第几页，同时初始化所有数据
if (request.getParameter("page") != null)
{
page = new Integer(request.getParameter("page"));
}
// 总页面数
int pageCount = companies.size() / pageSize + (companies.size() % pageSize == 0 ? 0 : 1);
request.setAttribute("csize", "客户量:" + companies.size() + " 总页数:" + pageCount);
// 定位当前显示页的第一行
int currentPageFirstRow = (page - 1) * pageSize;
// 得到当前页的数据
List<Company> pageRows = new ArrayList<Company>();
for (int i = currentPageFirstRow; i < currentPageFirstRow + pageSize; i++)
{
if (i >= companies.size())
{
break;
}
pageRows.add((Company) companies.get(i));
}
// 设当前页的HTML游标
String htmlPage = "";
if (companies.size() != 0)
{
if (page != 1)
{
htmlPage = htmlPage + " <a href='fCompany?page=1' class='pagenum'>首页</a>";
htmlPage = htmlPage + " <a href='fCompany?page=" + (page - 1) + "' class='pagenum'>上一页</a>";
}
int i = page / 10;
if (i == 0)
{
++i;
}
else
{
i *= 10;
}
int j = (i + 9);
if (j > pageCount)
{
j = pageCount;
}
for (; i <= j; i++)
{
if (i != page)
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -