📄 saledept_tp.java
字号:
/*
*
*/
package com.jobcn.task.old;
import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.*;
import com.jobcn.database.DataBase;
import com.jobcn.task.AbstractCreateHtml;
/**
* @remark:
* @author: Stephen
* @date: 2005-3-21
* @version:
* @upd_remark:
* @upd_user:
* @upd_date:
*/
final public class SALEDept_TP extends AbstractCreateHtml {
//constructer1
public SALEDept_TP() {
super(new StringBuffer(1024));
}
//constructer2
public SALEDept_TP(StringBuffer sb) {
super(sb);
}
public void printHeader() {
sb.append("<html>\n");
sb.append("<head>\n");
sb.append(" <title>本月可比业绩排行榜</title>\n");
sb.append(" <link href=\"/BOSS_CSS/css.css\" rel=\"stylesheet\" type=\"text/css\">\n");
sb.append(" <meta http-equiv=\"content-Type\" content=\"text/html; charset=GBK\">\n");
sb.append("</head>\n");
}
public void printBody() {
java.text.DecimalFormat nf = new java.text.DecimalFormat("###,###"); //整数
// format
java.text.DecimalFormat Pernf = new java.text.DecimalFormat("###0.0000"); //四位小数
String strDate = (new java.text.SimpleDateFormat("yyyy-MM-dd")).format(new java.util.Date());
String strYear = strDate.substring(0, 4);
String strMonth = strDate.substring(5, 7);
//String strDay = strDate.substring(8, 10);
//String strYearMonth = strDate.substring(0, 7);
String strIMonth = String.valueOf(Integer.parseInt(strMonth));
//String strIDay = String.valueOf(Integer.parseInt(strDay));
DataBase db = new DataBase();
Connection DBConn = null;
Statement stmt = null;
ResultSet RS = null;
String SQL = "", selectStr = "", orderByStr = "";
String DepartId = "", DepartIdOld = "", DepartName = "", DepartNameOld = "", ManagerPerName = "";
//String PersonId = "";
String PersonName = "", StateID = "";
int T = 0, TractMoney = 0, TPG=0, sequenceNum = 0, flag = 0, range = 0, TotTractMoney=0, count=0, TotCount=0;
float TP = 0.0f, TotTP=0.0f;
String shortcut = "";
String deptChartData="";
try {
DBConn = db.connect();
stmt = db.createStatement(DBConn);
//T数及相应的时间能力系数
//生成时间能力系数
selectStr = "{?=call USP_GetTP_FromT(?,?)}";
CallableStatement Comm = null;
Comm = DBConn.prepareCall(selectStr);
Comm.registerOutParameter(1, java.sql.Types.INTEGER);
Comm.setString(2, "A");
Comm.registerOutParameter(3, java.sql.Types.VARCHAR);
Comm.execute();
String T_TP = Comm.getString(3);
Comm.close();
//end
String T_TPArray[] = T_TP.split(",");
int T_TPArrayNum = T_TPArray.length;
sb.append("<body topmargin='0' leftmargin='0' oncontextmenu='return false;' ondragstart='return false;' onsource='return false;' onselectstart='return false;'>\n");
sb.append("<br>\n");
sb.append("<font color='red'> 备注：本页面只显示在职人员数据</font><br>\n");
sb.append("<table border=\"0\" >\n");
sb.append("<tr>\n");
sb.append("<td width=\"50\" valign=\"top\"><a name='#top0'> </a></td>\n");
sb.append("<td>\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' bgcolor='#FAE8CC'>\n");
sb.append("<td>T数</td>\n");
for(int i = 0; i < T_TPArrayNum; i = i + 2) {
if(T_TPArray[i].equals("25"))
sb.append("<td >24+</td>\n");
else
sb.append("<td >" + T_TPArray[i] + "</td>\n");
}
sb.append("</tr>");
sb.append("<tr align='center' bgcolor='#FAE8CC'>\n");
sb.append("<td nowrap >时间能力系数</td>\n");
for(int i = 1; i < T_TPArrayNum; i = i + 2) {
sb.append("<td bgcolor='#ffffff'>" + T_TPArray[i] + "</td>\n");
}
sb.append("</tr>\n");
sb.append("</table>\n");
sb.append("</td>\n");
sb.append("</tr>\n");
sb.append("</table>\n");
//end
//部门平均可比签单业绩
sb.append("<br>\n");
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='7' nowrap><font color=red>部门平均可比签单业绩<font color='blue'><b>（只显示在职人员数据）</b></font></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>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");
selectStr = "select b.deptid,b.departname,b.name,a.depttractmoney,depttp,isnull(deptPersonNum,0) deptPersonNum,depttpg from "
+ "( "
+ " select departid,departname,sum(tractmoney) as depttractmoney,sum(tp) as depttp,count(*) as deptPersonNum,sum(tractmoney)/sum(tp) as DeptTPG "
+ " from PerTractMoneyTPGSequence "
+ " group by departid,departname "
+ ") a, "
+ "( "
+ " select SaleMoneyDest_Dept_EveryMonth.deptid,SaleMoneyDest_Dept_EveryMonth.departname,hrperson_main.name from SaleMoneyDest_Dept_EveryMonth,HRPerson_Main "
+ " where SaleMoneyDest_Dept_EveryMonth.managerperid=hrperson_Main.id "
+ " and SaleMoneyDest_Dept_EveryMonth.yearnum='" + strYear + "' "
+ " and SaleMoneyDest_Dept_EveryMonth.monthnum='" + strIMonth + "' "
+ ") b "
+ "where b.deptid *=a.departid ";
orderByStr = "order by depttpg desc";
SQL = selectStr + orderByStr;
//System.out.println(SQL);
sequenceNum = 0;
count = 0;
RS = db.getRS(stmt, SQL);
while(RS.next()) {
//T = RS.getInt("T");
DepartId = RS.getString("deptid").trim();
DepartName = RS.getString("departname").trim();
ManagerPerName = RS.getString("name").trim();
TractMoney = RS.getInt("depttractmoney");
TP = RS.getFloat("depttp");
count=RS.getInt("deptPersonNum");
TPG = RS.getInt("depttpg");
//sequenceNum=RS.getInt("Sequence");
TotTractMoney = TotTractMoney + TractMoney;
TotCount = TotCount + count;
TotTP = TotTP + TP;
sequenceNum = sequenceNum + 1;
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(" <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(" <tr bgcolor=\"#FAE8CC\">\n");
sb.append(" <td colspan=\"3\" align=\"center\" nowrap>合 计</td>\n");
sb.append(" <td nowrap>"+nf.format(TotCount)+"</td>\n");
sb.append(" <td align=\"right\" nowrap>"+Pernf.format(TotTP)+"</td>\n");
sb.append(" <td align=\"right\" nowrap>"+nf.format(TotTractMoney)+"</td>\n");
sb.append(" <td align=\"right\" nowrap>"+nf.format(((float)TotTractMoney)/TotTP)+"</td>\n");
sb.append(" </tr>\n");
sb.append("</table>\n");
sb.append("</td>\n");
//图表
//System.out.println(deptChartData);
orderByStr = "order by b.deptid";
SQL = selectStr + orderByStr;
RS = db.getRS(stmt, SQL);
while(RS.next()) {
DepartName = RS.getString("departname").trim();
TPG = RS.getInt("depttpg");
deptChartData = deptChartData + DepartName.replaceAll("销售", "") + "," + TPG + ",";
}
deptChartData = deptChartData.substring(0, deptChartData.length() - 1);
sb.append("<td>\n");
sb.append("<table border=0 width='1000' align='center'>\n");
sb.append("<tr valign=\"top\"><tr><td >\n");
sb.append("<img src=\"/BOSS_util/chart_java.jsp?TopTitle=部门平均可比签单业绩分布图&BottomTitle=销售部门&CoordColor=FF6600&chartData=" + deptChartData + "\">\n");
sb.append("</td></tr>\n");
sb.append("</table>\n");
sb.append("</td>\n");
sb.append("</tr>\n");
sb.append("</table >\n");
//////////////////////////////////////////////////
//各部可比签单业绩排行榜
sb.append("<br>\n");
sb.append("<table border=\"0\">\n");
sb.append("<tr>\n");
sb.append("<td width=\"50\" valign=\"top\"><a name=\"top\"></a> </td>\n");
sb.append("<td>\n");
sb.append("<font size=3> 【" + strYear + "年" + strMonth + "月】各部<font color=\"red\">可比签单业绩</font>排行榜</font>\n");
sb.append("<br>\n");
sb.append("<table border=\"0\" cellpadding=\"1\" cellspacing=\"0\" align=\"left\">\n");
sb.append("<tr>\n");
/*
* select 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='2005' and
* sde.MonthNum='3' ) tmp on ptms.DepartID=tmp.DepartID
*/
selectStr = "select 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 ";
orderByStr = "order by ptms.DepartID, ptms.Sequence";
SQL = selectStr + orderByStr;
//System.out.println(SQL);
sequenceNum = 0;
RS = db.getRS(stmt, SQL);
while(RS.next()) {
//PersonId = RS.getString("PersonID").trim();
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");
StateID = RS.getString("StateID").trim();
ManagerPerName = RS.getString("ManagerPerName");
sequenceNum = sequenceNum +1;
if(!DepartName.equals(DepartNameOld)) {
//每一个部门开始时打印上个部门的表尾并RESET计数和统计变量(除第一个部门之外)
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -