public ArrayList<NoProjectEvaluationBean> searchNoProjectByEvaluation(
String evaluationId, String departmentId) {
ArrayList<NoProjectEvaluationBean> list = new ArrayList<NoProjectEvaluationBean>();
String selectSQL =
"select evaid,evaluationcontent,leaddepartmentid,dept1.departmentname as leaddept,evadepartmentid,dept2.departmentname as evadept,evamonth,monthfinish,submittime,status,firstview,secondview,thirdview "
+ "from((select evaluationform.evaluationid as evaid,evaluationcontent,leaddepartmentid,evadepartmentid,evamonth,monthfinish,submittime,status,firstview,secondview,thirdview "
+ "from evaluationform join noprojectevaluation on evaluationform.evaluationid = noprojectevaluation.evaluationid) t "
+ "join department as dept1 on (t.leaddepartmentid=dept1.departmentid) join department as dept2 on (t.evadepartmentid=dept2.departmentid)) "
+ "where evadepartmentid='"
+ departmentId
+ "' and evaid='"
+ evaluationId
+ "';";
/*evaid
evaluationcontent
leaddepartmentid
leaddept
evadepartmentid
evadept
evamonth
monthfinish
submittime
status
firstview
secondview
thirdview*/
ResultSet rs = this.SelectDB(selectSQL);
try {
while (rs.next()) {
NoProjectEvaluationBean npe = new NoProjectEvaluationBean();
npe.setEvaluationId(rs.getString("evaid"));
npe.setEvaluationContent(rs.getString("evaluationcontent"));
npe.setLeadDepartmentId(rs.getString("leaddepartmentid"));
npe.setLeadDepartmentName(rs.getString("leaddept"));
npe.setEvaDepartmentId(rs.getString("evadepartmentid"));
npe.setLeadDepartmentName(rs.getString("evadept"));
npe.setEvaMonth(rs.getInt("evamonth"));
npe.setMonthFinish(rs.getString("monthfinish"));
npe.setSubmitTime(rs.getString("submittime"));
npe.setStatus(rs.getString("status"));
npe.setFirstView(rs.getString("firstview"));
npe.setSecondView(rs.getString("secondview"));
npe.setThirdView(rs.getString("thirdview"));
list.add(npe);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
/**
* ********************************************区分管领导对项目的操作！！*********************************************
*/
public ArrayList<ProjectMonthRecordBean> QianTouLeaderViewList(String leadarName) {
ArrayList<ProjectMonthRecordBean> list = new ArrayList<ProjectMonthRecordBean>();
String selectSQL =
"select Project.projectId,Project.projectName,evaForm.isMoney,evaForm.evaluationContent,evaForm.separateLeader,Project.totalProgress,resDep.departmentName,Project.evaContent,ProjectMonthRecord.taskMonth,ProjectMonthRecord.status,ProjectMonthRecord.monthFinish,ProjectMonthRecord.monthMoney,ProjectMonthRecord.monthProgress,ProjectMonthRecord.submitTime,ProjectMonthRecord.firstView,ProjectMonthRecord.secondView,ProjectMonthRecord.thirdView,Project.currentFinish,Project.firstClassify, Project.secondClassify "
+ "from (((project inner join Department as resDep on Project.resDepartmentId=resDep.departmentID ) inner join EvaluationForm as evaForm on evaForm.evaluationId=Project.evaluationId )left outer join ProjectMonthRecord on Project.projectId=ProjectMonthRecord.projectId ) where status='T00' and separateLeader='"
+ leadarName
+ "';";
System.out.println(selectSQL);
ResultSet rs = this.SelectDB(selectSQL);
try {
while (rs.next()) {
/*projectId projectName isMoney evaluationContent totalProgress
* departmentName evaContent taskMonth status monthFinish
* monthMoney monthProgress submitTime firstView secondView
* thirdView currentFinish firstClassify secondClassify
*/
ProjectMonthRecordBean pmrb = new ProjectMonthRecordBean();
pmrb.setProjectId(rs.getString("projectId"));
pmrb.setProjectName(rs.getString("projectName"));
pmrb.setIsMoney(rs.getString("isMoney"));
pmrb.setTotalProgress(rs.getDouble("totalProgress"));
pmrb.setResDepartmentName(rs.getString("departmentName"));
pmrb.setEvaluationContent(rs.getString("evaluationContent"));
pmrb.setProjectEvaContent(rs.getString("evaContent"));
pmrb.setEvaMonth(rs.getInt("taskMonth"));
pmrb.setStatus(rs.getString("status"));
pmrb.setMonthFinish(rs.getString("monthFinish"));
pmrb.setMonthProgress(rs.getDouble("monthMoney"));
pmrb.setMonthProgress(rs.getDouble("monthProgress"));
pmrb.setSubmitTime(rs.getString("submitTime"));
// pmrb.setMonthFinish(rs.getString("currentFinish"));
pmrb.setFirstView(rs.getString("firstView"));
pmrb.setSecondView(rs.getString("secondView"));
pmrb.setThirdView(rs.getString("thirdView"));
pmrb.setMonthMoney(rs.getDouble("monthMoney"));
list.add(pmrb);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
/** 该函数返回当月之前所有没有提交的项目记录 */
public ArrayList<ProjectMonthRecordBean> searchAllProjectByNoReport(String departmentID) {
ArrayList<ProjectMonthRecordBean> list = new ArrayList<ProjectMonthRecordBean>();
int len = (new java.util.Date()).getMonth() + 1;
for (int i = len; i > 0; i--) {
ArrayList<ProjectMonthRecordBean> listTemp = searchProjectByNoReport(departmentID, i);
for (ProjectMonthRecordBean pmb : listTemp) list.add(pmb);
}
return list;
}
/** 该函数筛选出牵头领导审核的非项目记录！！ */
public ArrayList<NoProjectEvaluationBean> QianTouLeaderNoProjectViewList(String leadarName) {
ArrayList<NoProjectEvaluationBean> list = new ArrayList<NoProjectEvaluationBean>();
String selectSQL =
"select ed.evaDepartmentid,resDep.departmentName as resDeptName,ef.evaluationId,ef.evaluationContent,ef.leadDepartmentId, leadDep.departmentName as leadDeptName,ef.separateLeader,npe.evaluationId, npe.evaMonth,npe.monthFinish,npe.submitTime,npe.status,npe.firstView,npe.secondView,npe.thirdView "
+ "from ((((EvaluationDepartment as ed LEFT JOIN NoProjectEvaluation AS npe ON ed.evaDepartmentId=npe.evaDepartmentId AND ed.evaluationId=npe.evaluationId ) JOIN Department AS resDep ON resDep.departmentId=ed.evaDepartmentid ) JOIN EvaluationForm as ef on ef.evaluationId=ed.evaluationId) JOIN Department AS leadDep ON leadDep.departmentId=ef.leadDepartmentId) where status='T00' and ef.separateLeader='"
+ leadarName
+ "';";
System.out.println(selectSQL);
ResultSet rs = this.SelectDB(selectSQL);
try {
while (rs.next()) {
/*projectId projectName isMoney evaluationContent totalProgress
* departmentName evaContent taskMonth status monthFinish
* monthMoney monthProgress submitTime firstView secondView
* thirdView currentFinish firstClassify secondClassify
*/
NoProjectEvaluationBean pmrb = new NoProjectEvaluationBean();
pmrb.setEvaluationId(rs.getString("evaluationId"));
pmrb.setEvaluationContent(rs.getString("evaluationContent"));
pmrb.setLeadDepartmentId(rs.getString("leadDepartmentId"));
pmrb.setLeadDepartmentName(rs.getString("leadDeptName"));
pmrb.setEvaDepartmentId(rs.getString("evaDepartmentid"));
// System.out.println(rs.getString("resDeptName"));
// System.out.println(rs.getString("leadDeptName"));
pmrb.setEvaDepartmentName(rs.getString("resDeptName"));
pmrb.setEvaMonth(rs.getInt("evaMonth"));
pmrb.setMonthFinish(rs.getString("monthFinish"));
pmrb.setSubmitTime(rs.getString("submitTime"));
pmrb.setStatus(rs.getString("status"));
pmrb.setFirstView(rs.getString("firstview"));
pmrb.setSecondView(rs.getString("secondview"));
pmrb.setThirdView(rs.getString("thirdview"));
list.add(pmrb);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
/** 该函数筛选出某牵头部门审核的非项目信息 */
public ArrayList<NoProjectEvaluationBean> searchNoProjectByLeadDepatrment(
String leadDeparmtentId) {
ArrayList<NoProjectEvaluationBean> list = new ArrayList<NoProjectEvaluationBean>();
String selectSQL =
"select ed.evaDepartmentid,resDep.departmentName as resDeptName,ef.evaluationId,ef.evaluationContent,ef.leadDepartmentId, leadDep.departmentName as leadDeptName,ef.separateLeader,npe.evaluationId, npe.evaMonth,npe.monthFinish,npe.submitTime,npe.status,npe.firstView,npe.secondView,npe.thirdView "
+ "from ((((EvaluationDepartment as ed LEFT JOIN NoProjectEvaluation AS npe ON ed.evaDepartmentId=npe.evaDepartmentId AND ed.evaluationId=npe.evaluationId ) JOIN Department AS resDep ON resDep.departmentId=ed.evaDepartmentid ) JOIN EvaluationForm as ef on ef.evaluationId=ed.evaluationId) JOIN Department AS leadDep ON leadDep.departmentId=ef.leadDepartmentId) where status='000' and leadDep.departmentId='"
+ leadDeparmtentId
+ "' ;";
System.out.println(selectSQL);
ResultSet rs = this.SelectDB(selectSQL);
try {
while (rs.next()) {
// evaDepartmentid departmentName evaluationId evaluationContent leadDepartmentId
// departmentName separateLeader evaluationId evaMonth monthFinish submitTime
// status firstView secondView thirdView
NoProjectEvaluationBean npe = new NoProjectEvaluationBean();
npe.setEvaluationId(rs.getString("evaluationId"));
npe.setEvaluationContent(rs.getString("evaluationContent"));
npe.setLeadDepartmentId(rs.getString("leadDepartmentId"));
npe.setLeadDepartmentName(rs.getString("leadDeptName"));
npe.setEvaDepartmentId(rs.getString("evaDepartmentid"));
// System.out.println(rs.getString("resDeptName"));
// System.out.println(rs.getString("leadDeptName"));
npe.setEvaDepartmentName(rs.getString("resDeptName"));
npe.setEvaMonth(rs.getInt("evaMonth"));
npe.setMonthFinish(rs.getString("monthFinish"));
npe.setSubmitTime(rs.getString("submitTime"));
npe.setStatus(rs.getString("status"));
npe.setFirstView(rs.getString("firstview"));
npe.setSecondView(rs.getString("secondview"));
npe.setThirdView(rs.getString("thirdview"));
list.add(npe);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
/**
* 该函数筛选出某部门上报没有通过的项目记录
*
* @param leadDeparmtentId 上报部门编号
* @return
*/
public ArrayList<ProjectMonthRecordBean> searchProjectFaildByLeadDepatrment(
String leadDeparmtentId) {
ArrayList<ProjectMonthRecordBean> list = new ArrayList<ProjectMonthRecordBean>();
String selectSQL =
"select Project.projectId,Project.projectName,evaForm.isMoney,evaForm.evaluationContent,Project.totalProgress,resDep.departmentName,Project.evaContent,ProjectMonthRecord.taskMonth,ProjectMonthRecord.status,ProjectMonthRecord.monthFinish,ProjectMonthRecord.monthMoney,ProjectMonthRecord.monthProgress,ProjectMonthRecord.submitTime,ProjectMonthRecord.firstView,ProjectMonthRecord.secondView,ProjectMonthRecord.thirdView,Project.currentFinish,Project.firstClassify, Project.secondClassify "
+ "from (((project inner join Department as resDep on Project.resDepartmentId=resDep.departmentID ) inner join EvaluationForm as evaForm on evaForm.evaluationId=Project.evaluationId and evaForm.leadDepartmentId='"
+ leadDeparmtentId
+ "' )inner join ProjectMonthRecord on Project.projectId=ProjectMonthRecord.projectId ) where status like '%F%';";
ResultSet rs = this.SelectDB(selectSQL);
try {
while (rs.next()) {
ProjectMonthRecordBean pmrb = new ProjectMonthRecordBean();
pmrb.setProjectId(rs.getString("projectId"));
pmrb.setProjectName(rs.getString("projectName"));
pmrb.setIsMoney(rs.getString("isMoney"));
pmrb.setTotalProgress(rs.getDouble("totalProgress"));
pmrb.setResDepartmentName(rs.getString("departmentName"));
pmrb.setEvaluationContent(rs.getString("evaluationContent"));
pmrb.setProjectEvaContent(rs.getString("evaContent"));
pmrb.setEvaMonth(rs.getInt("taskMonth"));
pmrb.setStatus(rs.getString("status"));
pmrb.setMonthFinish(rs.getString("monthFinish"));
pmrb.setMonthProgress(rs.getDouble("monthMoney"));
pmrb.setMonthProgress(rs.getDouble("monthProgress"));
pmrb.setSubmitTime(rs.getString("submitTime"));
pmrb.setMonthFinish(rs.getString("currentFinish"));
pmrb.setFirstView(rs.getString("firstView"));
pmrb.setSecondView(rs.getString("secondView"));
pmrb.setThirdView(rs.getString("thirdView"));
list.add(pmrb);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
/** 该函数返回某部门某考核指标下的全部项目 如果该部门没有承担项目，则需要返回到该部门完成的辅助完成详情！（其他函数中实现） */
public ArrayList<ProjectBean> searchProjectByDepartment(
String evaluationId, String departmentId) {
ArrayList<ProjectBean> list = new ArrayList<ProjectBean>();
// String selectSQL="select t.evaid,t.evaluationcontent,t.leaddepartmentid,d1.departmentname as
// leadDept,t.ismoney,t.totalmoney,t.evacurrfinish,t.procurrentfinish,t.projectid,t.projectname,t.resdepartmentid,d2.departmentname as resDept,t.separateleader,t.evacontent,t.firstclassify,t.secondclassify " +
// "from((select evaluationform.evaluationid as
// evaid,evaluationcontent,leaddepartmentid,ismoney,totalmoney,evaluationform.currentfinish as
// evacurrfinish,project.currentFinish as
// procurrentfinish,projectid,projectname,resdepartmentid,separateleader,evacontent,firstclassify,secondclassify " +
// "from evaluationform join project on (evaluationform.evaluationid=project.evaluationid)) t
// join department as d1 on (t.leaddepartmentid=d1.departmentid)join department as d2 on
// (t.resdepartmentid=d2.departmentid))where t.resdepartmentid='"+departmentId+"' and
// t.evaid='"+evaluationId+"';";
String selectSQL =
"select evaid,leaddepartmentid,d1.departmentname as leadDeptName,ismoney,evatotalmoney,prototalmoney,evacurrentfinish,procurrentfinish,projectid,projectname,resdepartmentid,d2.departmentname as resDeptName,separateleader,efevacontent,proevacontent,t.firstclassify,t.secondclassify from((select evaluationform.evaluationid as evaid,evaluationform.evaluationcontent as efevacontent,project.evacontent as proevacontent,leaddepartmentid,ismoney,evaluationform.totalmoney as evatotalmoney,project.totalMoney as prototalmoney,evaluationform.currentfinish as evacurrentfinish,project.currentFinish as procurrentfinish,projectid,projectname,resdepartmentid,separateleader,firstclassify,secondclassify from evaluationform join project on (evaluationform.evaluationid=project.evaluationid)) t join department as d1 on (t.leaddepartmentid=d1.departmentid)join department as d2 on (t.resdepartmentid=d2.departmentid))where t.resdepartmentid='"
+ departmentId
+ "' and t.evaid='"
+ evaluationId
+ "'; ";
ResultSet rs = this.SelectDB(selectSQL);
try {
while (rs.next()) {
ProjectBean pb = new ProjectBean();
// System.out.println(rs.getString("evaid"));
// System.out.println(rs.getString("leaddepartmentid"));
// System.out.println(rs.getString("leadDeptName"));
// System.out.println(rs.getString("ismoney"));
// System.out.println(rs.getString("evatotalmoney"));//evatotalmoney//prototalmoney
//
//
// System.out.println(rs.getString("prototalmoney"));
// System.out.println(rs.getString("evacurrentfinish"));
// System.out.println(rs.getString("procurrentfinish"));
// System.out.println(rs.getString("projectid"));
// System.out.println(rs.getString("projectname"));
//
// System.out.println(rs.getString("resdepartmentid"));
// System.out.println(rs.getString("resDeptName"));
// System.out.println(rs.getString("separateleader"));
// System.out.println(rs.getString("efevacontent"));
// System.out.println(rs.getString("proevacontent"));
//
// System.out.println(rs.getString("firstclassify"));
// System.out.println(rs.getString("secondclassify"));
// System.out.println("-------------------------------------");
//
pb.setProjectId(rs.getString("projectid"));
pb.setProjectName(rs.getString("projectname"));
pb.setEvaContent(rs.getString("proevacontent")); // 该项目的考核标准
pb.setEvaluationId(rs.getString("evaid"));
pb.setEvaluationContent(rs.getString("efevacontent"));
pb.setSeparateLeader(rs.getString("separateleader"));
pb.setEvaContent(rs.getString("proevacontent"));
pb.setLeadDepartmentId(rs.getString("leaddepartmentid"));
pb.setLeadDepartmentName(rs.getString("leadDeptName"));
pb.setResDepartmentId(rs.getString("resdepartmentid"));
pb.setResDepartmentName(rs.getString("resDeptName"));
pb.setIsMoney(rs.getString("ismoney"));
pb.setTotalMoney(rs.getDouble("prototalmoney")); // 有误！
pb.setCurrentFinish(rs.getDouble("procurrentfinish"));
pb.setFirstClassify(rs.getString("firstclassify"));
pb.setSecondClassify(rs.getString("secondclassify"));
list.add(pb);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
