statistic.java

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

													"<td align='lefp'>"+0+"个</td></tr></table>");
										}
										else
										{
											list.add("<table width='100%' border='0' cellpadding='8' cellspacing='0' class='contentListTable'>" +
													"<tr bgcolor='#FFFFFF'><td bgcolor='#FFFFFF' width='110px'>"+year+"年"+month+"月新增客户</td>" +
													"<td align='center' width='"+i/10+"px'><div style='height:10px;background:#ff0000;width:"+i/10+"px'></div></td><td align='lefp'>"+i+"个</td></tr></table><br/>");
										}
										++month;
									}
									++year;
								}
							}
							else if("3".equals(statType))
							{
								list.add("<h3>"+rs.getString("deptname")+goDate.get(Calendar.YEAR)+"年"+(goDate.get(Calendar.MONTH)+1)+"月"+goDate.get(Calendar.DATE)+"日"+"--"+endDate.get(Calendar.YEAR)+"年"+(endDate.get(Calendar.MONTH)+1)+"月"+endDate.get(Calendar.DATE)+"日"+"期间日增量客户统计</h3>");
								int year = goDate.get(Calendar.YEAR);
								int month = (goDate.get(Calendar.MONTH)+1);
								int date = goDate.get(Calendar.DATE);
								int endmonth = 12;
								Calendar tempc = Calendar.getInstance();
								tempc.setTime(Date.valueOf(year +"-" + month + "-" + date));
								int enddate = tempc.getActualMinimum(Calendar.DATE);
								list.add("<tr class='contentListTd'>");
								while (year<=endDate.get(Calendar.YEAR))
								{
									if (year!=goDate.get(Calendar.YEAR))
									{
										month = 1;
									}
									if (year==endDate.get(Calendar.YEAR))
									{
										endmonth = (endDate.get(Calendar.MONTH)+1);
									}
									while (month <= endmonth)
									{
										if (month!=(goDate.get(Calendar.MONTH)+1))
										{
											date = 1;
										}
										if ((year==endDate.get(Calendar.YEAR))&&(month==(endDate.get(Calendar.MONTH)+1)))
										{
											enddate = endDate.get(Calendar.DATE);
										}
										while (date <= enddate)
										{
											temp = " and adddate = '" + (year+"-"+month+"-"+date)+"'"; 
											sql = "select count(*) from company where " + str + temp;
											rs = cm.executeQuery(sql);
											rs.next();
											int i = rs.getInt(1);
											if (i==0)
											{
												list.add("<table width='100%' border='0' cellpadding='8' cellspacing='0' class='contentListTable'>" +
														"<tr bgcolor='#FFFFFF'><td bgcolor='#FFFFFF' width='140px'>"+year+"年"+month+"月"+date+"日新增客户</td>" +
														"<td align='lefp'>"+0+"个</td></tr></table>");
											}
											else
											{
												list.add("<table width='100%' border='0' cellpadding='8' cellspacing='0' class='contentListTable'>" +
														"<tr bgcolor='#FFFFFF'><td bgcolor='#FFFFFF' width='140px'>"+year+"年"+month+"月"+date+"日新增客户</td>" +
														"<td align='center' width='"+i+"px'><div style='height:10px;background:#ff0000;width:"+i+"px'></div></td><td align='lefp'>"+i+"个</td></tr></table>");
											}
											++date;
										}
										++month;
									}
									++year;
								}
							}
							request.setAttribute("list", list);
						}
						else
						{
							// 开始拼装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 ("1".equals(statType))
							{	
								if(request.getSession().getAttribute("pos")!=null)
								{
									list.add("<h3>分公司"+request.getSession().getAttribute("pos")+"部"+goDate.get(Calendar.YEAR)+"年"+endDate.get(Calendar.YEAR)+"年"+"期间年增量客户统计</h3>");
								}
								else
								{
									list.add("<h3>分公司"+goDate.get(Calendar.YEAR)+"年"+endDate.get(Calendar.YEAR)+"年"+"期间年增量客户统计</h3>");
								}
								int year = goDate.get(Calendar.YEAR);
								while (year<=endDate.get(Calendar.YEAR))
								{
									temp = " and adddate in (select adddate from company where adddate >= '" + 
									(year+"-"+1+"-"+1) + 
									"' and adddate <= '" + (year +"-" + 12 + "-" + 31) + "')";
									sql = "select count(*) from company where " + str + temp;
									rs = cm.executeQuery(sql);
									rs.next();
									int i = rs.getInt(1);
									if (i==0)
									{
										list.add("<table width='100%' border='0' cellpadding='8' cellspacing='0' class='contentListTable'>" +
												"<tr bgcolor='#FFFFFF'><td bgcolor='#FFFFFF' width='90px'>"+year+"年新增客户</td>" +
												"<td align='lefp'>"+0+"个</td></tr></table>");
									}
									else
									{
										list.add("<table width='100%' border='0' cellpadding='8' cellspacing='0' class='contentListTable'>" +
												"<tr bgcolor='#FFFFFF'><td bgcolor='#FFFFFF' width='90px'>"+year+"年新增客户</td>" +
												"<td align='center' width='"+i/100+"px'><div style='height:10px;background:#ff0000;width:"+i/100+"px'></div></td><td align='lefp'>"+i+"个</td></tr></table>");
									}
									++year;
								}
							}
							else if("2".equals(statType))
							{
								if(request.getSession().getAttribute("pos")!=null)
								{
									list.add("<h3>分公司"+request.getSession().getAttribute("pos")+"部"+goDate.get(Calendar.YEAR)+"年"+(goDate.get(Calendar.MONTH)+1)+"月--"+endDate.get(Calendar.YEAR)+"年"+(endDate.get(Calendar.MONTH)+1)+"月"+"期间月增量客户统计</h3>");
								}
								else
								{
									list.add("<h3>分公司"+goDate.get(Calendar.YEAR)+"年"+(goDate.get(Calendar.MONTH)+1)+"月--"+endDate.get(Calendar.YEAR)+"年"+(endDate.get(Calendar.MONTH)+1)+"月"+"期间月增量客户统计</h3>");
								}
								int year = goDate.get(Calendar.YEAR);
								int month = (goDate.get(Calendar.MONTH)+1);
								int endmonth = 12;
								Calendar tempc = Calendar.getInstance();
								while (year<=endDate.get(Calendar.YEAR))
								{
									if (year!=goDate.get(Calendar.YEAR))
									{
										month = 1;
									}
									if (year==endDate.get(Calendar.YEAR))
									{
										endmonth = (endDate.get(Calendar.MONTH)+1);
									}
									while (month <= endmonth)
									{
										tempc.setTime(Date.valueOf(year +"-" + month + "-" + 1));
										temp = " and adddate in (select adddate from company where adddate >= '" + 
										(year+"-"+month+"-"+1) + 
										"' and adddate <= '" + (year +"-" + month + "-" + tempc.getActualMaximum(Calendar.DATE)) + "')";
										sql = "select count(*) from company where " + str + temp;
										rs = cm.executeQuery(sql);
										rs.next();
										int i = rs.getInt(1);
										if (i==0)
										{
											list.add("<table width='100%' border='0' cellpadding='8' cellspacing='0' class='contentListTable'>" +
													"<tr bgcolor='#FFFFFF'><td bgcolor='#FFFFFF' width='110px'>"+year+"年"+month+"月新增客户</td>" +
													"<td align='lefp'>"+0+"个</td></tr></table>");
										}
										else
										{
											list.add("<table width='100%' border='0' cellpadding='8' cellspacing='0' class='contentListTable'>" +
													"<tr bgcolor='#FFFFFF'><td bgcolor='#FFFFFF' width='110px'>"+year+"年"+month+"月新增客户</td>" +
													"<td align='center' width='"+i/10+"px'><div style='height:10px;background:#ff0000;width:"+i/10+"px'></div></td><td align='lefp'>"+i+"个</td></tr></table>");
										}
										++month;
									}
									++year;
								}
							}
							else if("3".equals(statType))
							{
								if(request.getSession().getAttribute("pos")!=null)
								{
									list.add("<h3>分公司"+request.getSession().getAttribute("pos")+"部"+goDate.get(Calendar.YEAR)+"年"+(goDate.get(Calendar.MONTH)+1)+"月"+goDate.get(Calendar.DATE)+"日"+"--"+endDate.get(Calendar.YEAR)+"年"+(endDate.get(Calendar.MONTH)+1)+"月"+endDate.get(Calendar.DATE)+"日"+"期间日增量客户统计</h3>");
								}
								else
								{
									list.add("<h3>分公司"+goDate.get(Calendar.YEAR)+"年"+(goDate.get(Calendar.MONTH)+1)+"月"+goDate.get(Calendar.DATE)+"日"+"--"+endDate.get(Calendar.YEAR)+"年"+(endDate.get(Calendar.MONTH)+1)+"月"+endDate.get(Calendar.DATE)+"日"+"期间日增量客户统计</h3>");
								}
								int year = goDate.get(Calendar.YEAR);
								int month = (goDate.get(Calendar.MONTH)+1);
								int date = goDate.get(Calendar.DATE);
								int endmonth = 12;
								Calendar tempc = Calendar.getInstance();
								tempc.setTime(Date.valueOf(year +"-" + month + "-" + date));
								int enddate = tempc.getActualMinimum(Calendar.DATE);
								list.add("<tr class='contentListTd'>");
								while (year<=endDate.get(Calendar.YEAR))
								{
									if (year!=goDate.get(Calendar.YEAR))
									{
										month = 1;
									}
									if (year==endDate.get(Calendar.YEAR))
									{
										endmonth = (endDate.get(Calendar.MONTH)+1);
									}
									while (month <= endmonth)
									{
										if (month!=(goDate.get(Calendar.MONTH)+1))
										{
											date = 1;
										}
										if ((year==endDate.get(Calendar.YEAR))&&(month==(endDate.get(Calendar.MONTH)+1)))
										{
											enddate = endDate.get(Calendar.DATE);
										}
										while (date <= enddate)
										{
											temp = " and adddate = '" + (year+"-"+month+"-"+date)+"'"; 
											sql = "select count(*) from company where " + str + temp;
											rs = cm.executeQuery(sql);
											rs.next();
											int i = rs.getInt(1);
											if (i==0)
											{
												list.add("<table width='100%' border='0' cellpadding='8' cellspacing='0' class='contentListTable'>" +
														"<tr bgcolor='#FFFFFF'><td bgcolor='#FFFFFF' width='140px'>"+year+"年"+month+"月"+date+"日新增客户</td>" +
														"<td align='lefp'>"+0+"个</td></tr></table>");
											}
											else
											{
												list.add("<table width='100%' border='0' cellpadding='8' cellspacing='0' class='contentListTable'>" +
														"<tr bgcolor='#FFFFFF'><td bgcolor='#FFFFFF' width='140px'>"+year+"年"+month+"月"+date+"日新增客户</td>" +
														"<td align='center' width='"+i+"px'><div style='height:10px;background:#ff0000;width:"+i+"px'></div></td><td align='lefp'>"+i+"个</td></tr></table>");
											}
											++date;
										}
										++month;
									}
									++year;
								}
							}
							request.setAttribute("list", list);
						}
					}
				}
				catch(SQLException e)
				{
					e.printStackTrace();
				}
			}
			else
			{
				request.setAttribute("error", "系统故障，请重试或联系管理员");
				request.getRequestDispatcher("/error.jsp").forward(request, response);
			}
		}
		finally
		{
			cm.close();
		}
		rd.forward(request, response);
	}
}