📄 saledept_tp.java
字号:
if(!DepartNameOld.equals("")) {
sb.append("</table>\n");
sb.append("</td>\n");
sb.append("<td width=\"10\" valign='top'><a name='" + DepartIdOld + "' ></a> </td>\n");
sequenceNum = 1;
}
//每一个部门开始时打印每个部门的表头
sb.append("<td valign=top>\n");
sb.append("<table border=1 width=100% cellpadding=3 cellspacing=0 bordercolor=#668591 style='border-collapse: collapse;border-style:none'>\n");
sb.append(" <tr align=center>\n");
sb.append(" <td colspan=6 bgcolor=#FAE8CC nowrap>" + DepartName + "(经理：" + ManagerPerName + ")</td>\n");
sb.append(" </tr>\n");
sb.append("<tr bgcolor=\"#FAE8CC\" align=center>\n");
sb.append(" <td align=\"center\" nowrap>名次</td>\n");
sb.append(" <td align=\"center\" nowrap>销售人员</td>\n");
sb.append(" <td align=\"center\" nowrap>T数</td>\n");
sb.append(" <td align=\"center\" nowrap>时间能力系数<br>①</td>\n");
sb.append(" <td align=\"center\" nowrap>当月签单额(元)<br>②</td>\n");
sb.append(" <td align=\"center\" nowrap>可比签单业绩<br>②/①</td>\n");
sb.append("</tr>\n");
shortcut = shortcut + "<a href='#" + DepartId + "'>" + DepartName + "</a> ";
DepartIdOld = DepartId;
DepartNameOld = DepartName;
} // endif
sb.append("<tr bgcolor=\"#ffffff\" align=\"center\" onmouseover=\"this.bgColor='#c8ca74';\" onmouseout=\"this.bgColor='#ffffff';\">\n");
sb.append("<td>" + sequenceNum + "</td>\n");
if(StateID.equals("1703"))
sb.append(" <td>" + PersonName + "<br>(<font color='red'>离职</font>)</td>\n");
else
sb.append(" <td>" + PersonName + "</td>\n");
sb.append(" <td>" + T + "</td>\n");
sb.append(" <td align=\"right\">" + Pernf.format(TP) + "</td>\n");
sb.append(" <td align=\"right\">" + nf.format(TractMoney) + "</td>\n");
sb.append(" <td align=\"right\">" + nf.format(TPG) + " </td>\n"); //Pernf.format((Float)TPG)
sb.append("</tr>\n");
} // end while
//////////////////////////////////////////////////////////////////////////////
sb.append("</table>\n");
sb.append("</td>\n");
sb.append("<td width=\"10\" valign='top'><a name='" + DepartIdOld + "' ></a> </td>\n");
sb.append("</tr>\n");
sb.append("</table>\n");
sb.append("</td>\n");
sb.append("</tr>\n");
sb.append("</table>\n");
///////////快捷方式/////////////
shortcut = shortcut + "<a href='#top0'><font color=red>页面顶部</font></a>";
sb.append("<table border=\"0\">\n");
sb.append("<tr>\n");
sb.append("<td width=\"50\" > </td>\n");
sb.append("<td>\n");
sb.append(" <div style=\"width:'666'\"><font color=red><b></b>快捷方式：</font>" + shortcut + "</div>\n");
sb.append("</td>\n");
sb.append("</tr>\n");
sb.append("</table>\n");
///////////快捷方式end/////////////
sb.append("<table border=\"0\">\n");
sb.append("<tr>\n");
sb.append("<td width=\"50\" valign=\"top\"> </td>\n");
sb.append("<td valign=\"top\">\n");
sb.append("<table border=1 cellpadding=3 cellspacing=0 bordercolor=#668591 style='border-collapse: collapse;border-style:none'>\n");
sb.append(" <tr bgcolor=\"#FAE8CC\" align=center>\n");
sb.append(" <td colspan='9' nowrap><font color=red>注意：按可比签单业绩排序</font></td>\n");
sb.append(" </tr>\n");
sb.append(" <tr bgcolor=\"#FAE8CC\" align=center>\n");
sb.append(" <td align=\"center\" nowrap>名次</td>\n");
sb.append(" <td align=\"center\" nowrap>部门</td>\n");
sb.append(" <td align=\"center\" nowrap>部门经理</td>\n");
sb.append(" <td align=\"center\" nowrap>销售人员</td>\n");
sb.append(" <td align=\"center\" nowrap>升降</td>\n");
sb.append(" <td align=\"center\" nowrap>T数</td>\n");
sb.append(" <td align=\"center\" nowrap>时间能力系数<br>①</td>\n");
sb.append(" <td align=\"center\" nowrap>当月签单额(元)<br>②</td>\n");
sb.append(" <td align=\"center\" nowrap>可比签单业绩<br>②/①</td>\n");
sb.append(" </tr>\n");
orderByStr = " order by ptms.Sequence";
SQL = selectStr + orderByStr;
//System.out.println(SQL);
sequenceNum = 0;
RS = db.getRS(stmt, SQL);
while(RS.next()) {
PersonName = RS.getString("PersonName").trim();
T = RS.getInt("T");
DepartId = RS.getString("DepartID").trim();
DepartName = RS.getString("DepartName").trim();
TractMoney = RS.getInt("TractMoney");
TP = RS.getFloat("TP");
TPG = RS.getInt("TPG");
//sequenceNum=RS.getInt("Sequence");
sequenceNum = sequenceNum + 1;
flag = RS.getInt("Flag");
range = RS.getInt("Range");
StateID = RS.getString("StateID");
ManagerPerName = RS.getString("ManagerPerName");
if(sequenceNum <= 50)
sb.append("<tr bgcolor=\"#FFFFCC\" onmouseover=\"this.bgColor='#c8ca74';\" onmouseout=\"this.bgColor='#FFFFCC';\">\n");
else
sb.append("<tr bgcolor=\"#ffffff\" onmouseover=\"this.bgColor='#c8ca74';\" onmouseout=\"this.bgColor='#ffffff';\">\n");
sb.append(" <td align='center' nowrap>" + sequenceNum + "</td>\n");
sb.append(" <td nowrap>" + DepartName + "</td>\n");
sb.append(" <td nowrap>" + ManagerPerName + "</td>\n");
if(StateID.equals("1703"))
sb.append(" <td nowrap>" + PersonName + "<br>(<font color='red'>离职</font>)</td>\n");
else
sb.append(" <td nowrap>" + PersonName + "</td>\n");
if(flag == 0) {
sb.append("<td nowrap>→</td>\n");
} else if(flag == 1) {
sb.append("<td nowrap><font color=\"green\">↑</font>(" + range + ")</td>\n");
} else if(flag == 2) {
sb.append("<td nowrap><font color=\"red\">↓</font>(" + range + ")</td>\n");
}
sb.append(" <td nowrap>" + T + "</td>\n");
sb.append(" <td align=\"right\" nowrap>" + Pernf.format(TP) + "</td>\n");
sb.append(" <td align=\"right\" nowrap>" + nf.format(TractMoney) + "</td>\n");
sb.append(" <td align=\"right\" nowrap>" + nf.format(TPG) + " </td>\n"); //Pernf.format((Float)TPG)
sb.append("</tr>\n");
}
sb.append("</table >\n");
sb.append("</td>\n");
sb.append("<td width=\"10\" valign=\"top\"> </td>\n");
sb.append("<td valign=\"top\">\n");
sb.append("<table border=1 cellpadding=3 cellspacing=0 bordercolor=#668591 style='border-collapse: collapse;border-style:none'>\n");
sb.append(" <tr bgcolor=\"#FAE8CC\" align=center>\n");
sb.append(" <td colspan='4' nowrap><font color=red>可比签单业绩前50名按部门人数统计</font></td>\n");
sb.append(" </tr>\n");
sb.append(" <tr bgcolor=\"#FAE8CC\" align=center>\n");
sb.append(" <td align=\"center\" nowrap>名次</td>\n");
sb.append(" <td align=\"center\" nowrap>部门</td>\n");
sb.append(" <td align=\"center\" nowrap>部门经理</td>\n");
sb.append(" <td align=\"center\" nowrap>人数</td>\n");
sb.append(" </tr>\n");
/*SQL = "select DepartId, DepartName, ManagerPerName, COUNT(*) CNT from "
+ "(select top 50 ptms.PersonID, ptms.PersonName, ptms.T, ptms.DepartID, ptms.DepartName, ptms.TractMoney, "
+ "ptms.TP, ptms.TPG, ptms.Sequence, ptms.Flag, ptms.Range, ptms.StateID, tmp.ManagerPerName "
+ "from PerTractMoneyTPGSequence ptms "
+ "left join( "
+ " select sde.DeptID as DepartID, hpm.Name ManagerPerName "
+ " from SaleMoneyDest_Dept_EveryMonth sde "
+ " left join HrPerson_Main hpm on sde.ManagerPerID=hpm.ID "
+ " where sde.YearNum='"
+ strYear
+ "' and sde.MonthNum='"
+ strIMonth
+ "' "
+ ") tmp on ptms.DepartID=tmp.DepartID "
+ "order by ptms.Sequence "
+ ") t "
+ "group by DepartId, DepartName, ManagerPerName "
+ "order by CNT DESC";*/
SQL = "select a.DepartID, a.DepartName, c.Name ManagerPerName, isnull(sum(personnum),0) as CNT "
+ " from "
+ " (select DeptID DepartID, DepartName, ManagerPerID "
+ " from SaleMoneyDest_Dept_EveryMonth "
+ " where YearNum='" + strYear + "' and MonthNum='" + strIMonth + "' "
+ " ) a, "
+ " (select top 50 DepartID, 1 as personnum from PerTractMoneyTPGSequence order by sequence "
+ " ) b, "
+ " ( select ID, Name from HRPerson_Main) c "
+ " where a.DepartID *= b.DepartID "
+ " and a.ManagerPerID = c.ID "
+ " group by a.DepartID, a.DepartName, c.Name "
+ " order by CNT desc";
//System.out.println(SQL);
count = 0;
sequenceNum = 0;
RS = db.getRS(stmt, SQL);
while(RS.next()) {
DepartId = RS.getString("DepartID").trim();
DepartName = RS.getString("DepartName").trim();
ManagerPerName = RS.getString("ManagerPerName");
count = RS.getInt("CNT");
sequenceNum = sequenceNum + 1;
if(count==0){
sb.append("<tr bgcolor=\"#ffffff\" style=\"color:red\" onmouseover=\"this.bgColor='#c8ca74';\" onmouseout=\"this.bgColor='#ffffff';\">\n");
}
else {
sb.append("<tr bgcolor=\"#ffffff\" onmouseover=\"this.bgColor='#c8ca74';\" onmouseout=\"this.bgColor='#ffffff';\">\n");
}
sb.append(" <td align='center' nowrap>" + sequenceNum + "</td>\n");
sb.append(" <td nowrap>" + DepartName + "</td>\n");
sb.append(" <td nowrap>" + ManagerPerName + "</td>\n");
sb.append(" <td nowrap>" + count + "</td>\n");
sb.append("</tr>\n");
}
sb.append("</table >\n");
sb.append("</td>\n");
sb.append("</tr>\n");
sb.append("</table>\n");
sb.append("</body>\n");
/*sb.append("<script language=\"javascript\">\n");
sb.append(" setTimeout(\"this.location.reload()\",1*60*1000);\n");
sb.append("</script>\n");*/
RS.close();
stmt.close();
DBConn.close();
} catch(Exception e) {
System.out.println(e.getMessage());
try {
RS.close();
stmt.close();
DBConn.close();
} catch(SQLException sqle) {
System.out.println(sqle.getMessage());
}
}
}
public static void main(String[] args) {
String localPath = "html\\";
String fileName = "SALEDept_TP.html";
long times = System.currentTimeMillis();
String strDate = (new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm")).format(new java.util.Date());
SALEDept_TP creator = new SALEDept_TP(new StringBuffer(10240));
boolean ret = creator.create(localPath, fileName);
if(ret) {
times = (System.currentTimeMillis() - times) / 1000;
System.out.println(localPath + fileName + "生成成功,时间:" + strDate + " 耗时" + times + "秒");
try {
Runtime rt = Runtime.getRuntime();
String[] cmd = new String[3];
cmd[0] = "cmd.exe";
cmd[1] = "/C";
cmd[2] = "bat\\copySALEDept_TP.bat";
Process proc = rt.exec(cmd);
InputStream isError = proc.getErrorStream();
InputStreamReader isrError = new InputStreamReader(isError);
BufferedReader brError = new BufferedReader(isrError);
InputStream isOutput = proc.getInputStream();
InputStreamReader isrOutput = new InputStreamReader(isOutput);
BufferedReader brOutput = new BufferedReader(isrOutput);
int retc = proc.waitFor();
if(retc==0){
System.out.println(cmd[2]+"执行成功");
String line = null;
while ((line = brOutput.readLine()) != null)
System.out.println(">>" + line);
}
else{
System.out.println(cmd[2]+"执行失败");
String line = null;
while ((line = brError.readLine()) != null)
System.out.println(">>" + line);
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
}⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -