public static void main(String[] args) {
ProjectManagement pm = new ProjectManagement();
pm.OpenDB();
// ArrayList<ProjectBean> list=pm.searchProjectByDepartment("department119","evaluation002");
//
// for(ProjectBean pb:list){
// System.out.println("责任部门ID="+pb.getResDepartmentId());
// System.out.println("评估ID="+pb.getEvaluationId());
// System.out.println("项目考核="+pb.getEvaContent());
// System.out.println("考核指标内容="+pb.getEvaluationContent());
// System.out.println(pb.getCurrentFinish());
//
// System.out.println(pb.getProjectName());
// System.out.println(pb.getIsMoney());
// System.out.println(pb.getTotalMoney());
// System.out.println("----------------------");
// }
ArrayList<NoProjectEvaluationBean> list =
pm.searchNoProjectEvaluationByNoReport("department119", 5);
for (NoProjectEvaluationBean pmr : list) {
System.out.println(pmr.getEvaMonth());
System.out.println(pmr.getMonthFinish());
System.out.println(pmr.getEvaluationId());
System.out.println(pmr.getEvaluationContent());
System.out.println(pmr.getEvaDepartmentName());
}
pm.CloseDB();
}
/** 该函数是督查室审核非项目记录完成情况 */
public ArrayList<NoProjectEvaluationBean> DuchaNoProjectViewList() {
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='TT0';";
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"));
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> searchNoProjectByDetails(String departmentId) {
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 is null and ed.evaDepartmentid='"
+ departmentId
+ "' ;";
System.out.println(selectSQL);
ResultSet rs = this.SelectDB(selectSQL);
try {
while (rs.next()) {
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"));
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;
}
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;
}