公司第五个礼拜的java后端总结

进公司第五个礼拜

这周的任务是:
1.修改运动项目排序
2.医护端随访查看与录入界面接口的撰写和优化
完成情况:
全部完成
途中遇到的困难:
1.在运动项目排序的时候先按ordinal字段里的数字从小到大排序,再对该字段里的空值按照名字从a-z排序
2.在医护端随访查看与录入界面的接口撰写的时候遇到数据库里的时间明明是%Y-%m-%d格式的但是拿到java中就变成时间轴了.一开始想的是用java的SimpleDateFormat方法将时间轴转换回时间但是一直报错java.lang.IllegalArgumentException,网上有说jar包没加载完全,有的说参数传的不对,反正我都试了但是都没有用,最后用数据库的DATE_FORMAT直接在数据库转格式就可以了
3.还有就是下面这个接口我写的挺麻烦的不知道有什么好的改进方法

public JSONArray getTeamPatByType(final String userId,final String type,final String name,final String diagnosis,final String month) throws SQLException {
		return executeQuery(new QueryExecutor<JSONArray>(this) {
			@Override
			protected JSONArray onExecute() throws SQLException {
				String sql = null;
				JSONArray list = new JSONArray();
				if(name == null || "".equals(name)) {
					if((diagnosis == null || "".equals(diagnosis)) && (month == null || "".equals(month))) {
						sql = "SELECT GROUP_CONCAT(icd.`name`) diagnosis, a.*, DATE_FORMAT( fu.follow_up_date, '%Y-%m-%d' ) AS follow_up_date, hr.`diagnosis_other` FROM ( SELECT mtp.team_id, p.patient_id, p.`name`, p.`portrait_uri`, mt. NAME AS tname, CASE WHEN p.sex_code = 1 THEN '男' WHEN p.sex_code = 2 THEN '女' END sex, ROUND( DATEDIFF(CURDATE(), p.date_of_birth) / 365.2422 ) age FROM stw.`medical_team_patient` mtp LEFT JOIN stw.`patient` p ON mtp.`patient_id` = p.`patient_id` LEFT JOIN stw.`medical_team` mt ON mt.`team_id` = mtp.`team_id` WHERE mtp.`service_code` IN ("+type+") AND mtp.`quit_date_time` IS NULL AND mtp.`team_id` IN ( SELECT team_id FROM stw.`medical_team_staff` mts WHERE mts.`user_id` = ? ) GROUP BY p.patient_id ) a LEFT JOIN stw.`health_record` hr ON hr.`patient_id` = a.patient_id AND hr.`invalidate_time` IS NULL LEFT JOIN stw.`health_record_diagnosis` hrd ON hr.`rec_id` = hrd.`hr_rec_id` LEFT JOIN stw.`icd` icd ON hrd.`icd_code` = icd.`icd_code` LEFT JOIN ( SELECT f.patient_id, f.follow_up_date FROM ( SELECT fur.* FROM stw.`follow_up_record` fur ORDER BY fur.`follow_up_date` DESC ) f GROUP BY f.patient_id ) fu ON fu.`patient_id` = a.patient_id GROUP BY a.patient_id ORDER BY CONVERT (a.`name` USING gbk) COLLATE gbk_chinese_ci";
						list = dbo.executeQueryAsJSON(sql, new int[1], new String[]{userId});
					}else if((diagnosis != null) && (month == null || "".equals(month))) {
						sql = "SELECT b.* FROM ( SELECT GROUP_CONCAT(icd.`name`) diagnosis, a.*, hr.`diagnosis_other`, DATE_FORMAT( fu.follow_up_date, '%Y-%m-%d' ) AS follow_up_date, ffur.follow_up_date AS start_follow_up_date FROM ( SELECT mtp.team_id, mt. NAME AS tname, p.`patient_id`, p.`name`, p.`portrait_uri`, CASE WHEN p.sex_code = 1 THEN '男' WHEN p.sex_code = 2 THEN '女' END sex, ROUND( DATEDIFF(CURDATE(), p.date_of_birth) / 365.2422 ) age FROM stw.`medical_team_patient` mtp LEFT JOIN stw.`patient` p ON mtp.`patient_id` = p.`patient_id` LEFT JOIN stw.`medical_team` mt ON mt.`team_id` = mtp.`team_id` WHERE mtp.`service_code` IN ("+type+") AND mtp.`quit_date_time` IS NULL AND mtp.`team_id` IN ( SELECT team_id FROM stw.`medical_team_staff` mts WHERE mts.`user_id` = ? ) GROUP BY p.patient_id ) a LEFT JOIN stw.`health_record` hr ON hr.`patient_id` = a.patient_id AND hr.`invalidate_time` IS NULL LEFT JOIN stw.`health_record_diagnosis` hrd ON hr.`rec_id` = hrd.`hr_rec_id` LEFT JOIN stw.`icd` icd ON hrd.`icd_code` = icd.`icd_code` LEFT JOIN ( SELECT f.patient_id, f.follow_up_date FROM ( SELECT fur.* FROM stw.`follow_up_record` fur ORDER BY fur.`follow_up_date` DESC ) f GROUP BY f.patient_id ) fu ON fu.`patient_id` = a.patient_id LEFT JOIN ( SELECT f.patient_id, f.follow_up_date FROM ( SELECT fur.* FROM stw.`follow_up_record` fur ORDER BY fur.`follow_up_date` ) f GROUP BY f.patient_id ) ffur ON ffur.`patient_id` = a.patient_id GROUP BY a.patient_id ) b WHERE b.diagnosis LIKE ? ORDER BY CONVERT (b.`name` USING gbk) COLLATE gbk_chinese_ci";
						list = dbo.executeQueryAsJSON(sql, new int[2], new String[]{userId,"%"+diagnosis+"%"});
					}else if((diagnosis == null || "".equals(diagnosis)) && (month != null)) {
						sql = "SELECT b.* FROM ( SELECT GROUP_CONCAT(icd.`name`) diagnosis, a.*, hr.`diagnosis_other`, DATE_FORMAT( fu.follow_up_date, '%Y-%m-%d' ) AS follow_up_date, ffur.follow_up_date AS start_follow_up_date FROM ( SELECT mtp.team_id, mt. NAME AS tname, p.`patient_id`, p.`name`, p.`portrait_uri`, CASE WHEN p.sex_code = 1 THEN '男' WHEN p.sex_code = 2 THEN '女' END sex, ROUND( DATEDIFF(CURDATE(), p.date_of_birth) / 365.2422 ) age FROM stw.`medical_team_patient` mtp LEFT JOIN stw.`patient` p ON mtp.`patient_id` = p.`patient_id` LEFT JOIN stw.`medical_team` mt ON mt.`team_id` = mtp.`team_id` WHERE mtp.`service_code` IN ("+type+") AND mtp.`quit_date_time` IS NULL AND mtp.`team_id` IN ( SELECT team_id FROM stw.`medical_team_staff` mts WHERE mts.`user_id` = ? ) GROUP BY p.patient_id ) a LEFT JOIN stw.`health_record` hr ON hr.`patient_id` = a.patient_id AND hr.`invalidate_time` IS NULL LEFT JOIN stw.`health_record_diagnosis` hrd ON hr.`rec_id` = hrd.`hr_rec_id` LEFT JOIN stw.`icd` icd ON hrd.`icd_code` = icd.`icd_code` LEFT JOIN ( SELECT f.patient_id, f.follow_up_date FROM ( SELECT fur.* FROM stw.`follow_up_record` fur ORDER BY fur.`follow_up_date` DESC ) f GROUP BY f.patient_id ) fu ON fu.`patient_id` = a.patient_id LEFT JOIN ( SELECT f.patient_id, f.follow_up_date FROM ( SELECT fur.* FROM stw.`follow_up_record` fur ORDER BY fur.`follow_up_date` ) f GROUP BY f.patient_id ) ffur ON ffur.`patient_id` = a.patient_id GROUP BY a.patient_id ) b WHERE TIMESTAMPDIFF( MONTH, b.start_follow_up_date, DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S')) <= ? ORDER BY CONVERT (b.`name` USING gbk) COLLATE gbk_chinese_ci";
						list = dbo.executeQueryAsJSON(sql, new int[2], new String[]{userId,month});
					}else if((diagnosis != null) && (month != null)){
						sql = "SELECT b.* FROM ( SELECT GROUP_CONCAT(icd.`name`) diagnosis, a.*, hr.`diagnosis_other`, DATE_FORMAT( fu.follow_up_date, '%Y-%m-%d' ) AS follow_up_date, ffur.follow_up_date AS start_follow_up_date FROM ( SELECT mtp.team_id, mt. NAME AS tname, p.`patient_id`, p.`name`, p.`portrait_uri`, CASE WHEN p.sex_code = 1 THEN '男' WHEN p.sex_code = 2 THEN '女' END sex, ROUND( DATEDIFF(CURDATE(), p.date_of_birth) / 365.2422 ) age FROM stw.`medical_team_patient` mtp LEFT JOIN stw.`patient` p ON mtp.`patient_id` = p.`patient_id` LEFT JOIN stw.`medical_team` mt ON mt.`team_id` = mtp.`team_id` WHERE mtp.`service_code` IN ("+type+") AND mtp.`quit_date_time` IS NULL AND mtp.`team_id` IN ( SELECT team_id FROM stw.`medical_team_staff` mts WHERE mts.`user_id` = ? ) GROUP BY p.patient_id ) a LEFT JOIN stw.`health_record` hr ON hr.`patient_id` = a.patient_id AND hr.`invalidate_time` IS NULL LEFT JOIN stw.`health_record_diagnosis` hrd ON hr.`rec_id` = hrd.`hr_rec_id` LEFT JOIN stw.`icd` icd ON hrd.`icd_code` = icd.`icd_code` LEFT JOIN ( SELECT f.patient_id, f.follow_up_date FROM ( SELECT fur.* FROM stw.`follow_up_record` fur ORDER BY fur.`follow_up_date` DESC ) f GROUP BY f.patient_id ) fu ON fu.`patient_id` = a.patient_id LEFT JOIN ( SELECT f.patient_id, f.follow_up_date FROM ( SELECT fur.* FROM stw.`follow_up_record` fur ORDER BY fur.`follow_up_date` ) f GROUP BY f.patient_id ) ffur ON ffur.`patient_id` = a.patient_id GROUP BY a.patient_id ) b WHERE TIMESTAMPDIFF( MONTH, b.start_follow_up_date, DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S')) <= ? AND b.diagnosis LIKE ? ORDER BY CONVERT (b.`name` USING gbk) COLLATE gbk_chinese_ci";
						list = dbo.executeQueryAsJSON(sql, new int[3], new String[]{userId,month,"%"+diagnosis+"%"});
					}
										
				}else {
					if((diagnosis == null || "".equals(diagnosis)) && (month == null || "".equals(month))) {
						sql = "SELECT GROUP_CONCAT(icd.`name`) diagnosis, a.*, DATE_FORMAT( fu.follow_up_date, '%Y-%m-%d' ) AS follow_up_date, hr.`diagnosis_other` FROM ( SELECT mtp.team_id, p.patient_id, p.`name`, p.`portrait_uri`, mt. NAME AS tname, CASE WHEN p.sex_code = 1 THEN '男' WHEN p.sex_code = 2 THEN '女' END sex, ROUND( DATEDIFF(CURDATE(), p.date_of_birth) / 365.2422 ) age FROM stw.`medical_team_patient` mtp LEFT JOIN stw.`patient` p ON mtp.`patient_id` = p.`patient_id` LEFT JOIN stw.`medical_team` mt ON mt.`team_id` = mtp.`team_id` WHERE mtp.`service_code` IN ("+type+") AND mtp.`quit_date_time` IS NULL AND mtp.`team_id` IN ( SELECT team_id FROM stw.`medical_team_staff` mts WHERE mts.`user_id` = ? ) GROUP BY p.patient_id ) a LEFT JOIN stw.`health_record` hr ON hr.`patient_id` = a.patient_id AND hr.`invalidate_time` IS NULL LEFT JOIN stw.`health_record_diagnosis` hrd ON hr.`rec_id` = hrd.`hr_rec_id` LEFT JOIN stw.`icd` icd ON hrd.`icd_code` = icd.`icd_code` LEFT JOIN ( SELECT f.patient_id, f.follow_up_date FROM ( SELECT fur.* FROM stw.`follow_up_record` fur ORDER BY fur.`follow_up_date` DESC ) f GROUP BY f.patient_id ) fu ON fu.`patient_id` = a.patient_id WHERE a. NAME LIKE ? GROUP BY a.patient_id ORDER BY CONVERT (a.`name` USING gbk) COLLATE gbk_chinese_ci";
						list = dbo.executeQueryAsJSON(sql, new int[2], new String[]{userId,"%"+name+"%"});
					}else if((diagnosis != null) && (month == null || "".equals(month))) {
						sql = "SELECT b.* FROM ( SELECT GROUP_CONCAT(icd.`name`) diagnosis, a.*, hr.`diagnosis_other`, DATE_FORMAT( fu.follow_up_date, '%Y-%m-%d' ) AS follow_up_date, ffur.follow_up_date AS start_follow_up_date FROM ( SELECT mtp.team_id, mt. NAME AS tname, p.`patient_id`, p.`name`, p.`portrait_uri`, CASE WHEN p.sex_code = 1 THEN '男' WHEN p.sex_code = 2 THEN '女' END sex, ROUND( DATEDIFF(CURDATE(), p.date_of_birth) / 365.2422 ) age FROM stw.`medical_team_patient` mtp LEFT JOIN stw.`patient` p ON mtp.`patient_id` = p.`patient_id` LEFT JOIN stw.`medical_team` mt ON mt.`team_id` = mtp.`team_id` WHERE mtp.`service_code` IN ("+type+") AND mtp.`quit_date_time` IS NULL AND mtp.`team_id` IN ( SELECT team_id FROM stw.`medical_team_staff` mts WHERE mts.`user_id` = ? ) GROUP BY p.patient_id ) a LEFT JOIN stw.`health_record` hr ON hr.`patient_id` = a.patient_id AND hr.`invalidate_time` IS NULL LEFT JOIN stw.`health_record_diagnosis` hrd ON hr.`rec_id` = hrd.`hr_rec_id` LEFT JOIN stw.`icd` icd ON hrd.`icd_code` = icd.`icd_code` LEFT JOIN ( SELECT f.patient_id, f.follow_up_date FROM ( SELECT fur.* FROM stw.`follow_up_record` fur ORDER BY fur.`follow_up_date` DESC ) f GROUP BY f.patient_id ) fu ON fu.`patient_id` = a.patient_id LEFT JOIN ( SELECT f.patient_id, f.follow_up_date FROM ( SELECT fur.* FROM stw.`follow_up_record` fur ORDER BY fur.`follow_up_date` ) f GROUP BY f.patient_id ) ffur ON ffur.`patient_id` = a.patient_id GROUP BY a.patient_id ) b WHERE b.diagnosis LIKE ? AND b. NAME LIKE ? ORDER BY CONVERT (b.`name` USING gbk) COLLATE gbk_chinese_ci";
						list = dbo.executeQueryAsJSON(sql, new int[3], new String[]{userId,"%"+diagnosis+"%","%"+name+"%"});
					}else if((diagnosis == null || "".equals(diagnosis)) && (month != null)) {
						sql = "SELECT b.* FROM ( SELECT GROUP_CONCAT(icd.`name`) diagnosis, a.*, hr.`diagnosis_other`, DATE_FORMAT( fu.follow_up_date, '%Y-%m-%d' ) AS follow_up_date, ffur.follow_up_date AS start_follow_up_date FROM ( SELECT mtp.team_id, mt. NAME AS tname, p.`patient_id`, p.`name`, p.`portrait_uri`, CASE WHEN p.sex_code = 1 THEN '男' WHEN p.sex_code = 2 THEN '女' END sex, ROUND( DATEDIFF(CURDATE(), p.date_of_birth) / 365.2422 ) age FROM stw.`medical_team_patient` mtp LEFT JOIN stw.`patient` p ON mtp.`patient_id` = p.`patient_id` LEFT JOIN stw.`medical_team` mt ON mt.`team_id` = mtp.`team_id` WHERE mtp.`service_code` IN ("+type+") AND mtp.`quit_date_time` IS NULL AND mtp.`team_id` IN ( SELECT team_id FROM stw.`medical_team_staff` mts WHERE mts.`user_id` = ? ) GROUP BY p.patient_id ) a LEFT JOIN stw.`health_record` hr ON hr.`patient_id` = a.patient_id AND hr.`invalidate_time` IS NULL LEFT JOIN stw.`health_record_diagnosis` hrd ON hr.`rec_id` = hrd.`hr_rec_id` LEFT JOIN stw.`icd` icd ON hrd.`icd_code` = icd.`icd_code` LEFT JOIN ( SELECT f.patient_id, f.follow_up_date FROM ( SELECT fur.* FROM stw.`follow_up_record` fur ORDER BY fur.`follow_up_date` DESC ) f GROUP BY f.patient_id ) fu ON fu.`patient_id` = a.patient_id LEFT JOIN ( SELECT f.patient_id, f.follow_up_date FROM ( SELECT fur.* FROM stw.`follow_up_record` fur ORDER BY fur.`follow_up_date` ) f GROUP BY f.patient_id ) ffur ON ffur.`patient_id` = a.patient_id GROUP BY a.patient_id ) b WHERE TIMESTAMPDIFF( MONTH, b.start_follow_up_date, DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S')) <= ? AND b. NAME LIKE ? ORDER BY CONVERT (b.`name` USING gbk) COLLATE gbk_chinese_ci";
						list = dbo.executeQueryAsJSON(sql, new int[3], new String[]{userId,month,"%"+name+"%"});
					}else if((diagnosis != null) && (month != null)){
						sql = "SELECT b.* FROM ( SELECT GROUP_CONCAT(icd.`name`) diagnosis, a.*, hr.`diagnosis_other`, DATE_FORMAT( fu.follow_up_date, '%Y-%m-%d' ) AS follow_up_date, ffur.follow_up_date AS start_follow_up_date FROM ( SELECT mtp.team_id, mt. NAME AS tname, p.`patient_id`, p.`name`, p.`portrait_uri`, CASE WHEN p.sex_code = 1 THEN '男' WHEN p.sex_code = 2 THEN '女' END sex, ROUND( DATEDIFF(CURDATE(), p.date_of_birth) / 365.2422 ) age FROM stw.`medical_team_patient` mtp LEFT JOIN stw.`patient` p ON mtp.`patient_id` = p.`patient_id` LEFT JOIN stw.`medical_team` mt ON mt.`team_id` = mtp.`team_id` WHERE mtp.`service_code` IN ("+type+") AND mtp.`quit_date_time` IS NULL AND mtp.`team_id` IN ( SELECT team_id FROM stw.`medical_team_staff` mts WHERE mts.`user_id` = ? ) GROUP BY p.patient_id ) a LEFT JOIN stw.`health_record` hr ON hr.`patient_id` = a.patient_id AND hr.`invalidate_time` IS NULL LEFT JOIN stw.`health_record_diagnosis` hrd ON hr.`rec_id` = hrd.`hr_rec_id` LEFT JOIN stw.`icd` icd ON hrd.`icd_code` = icd.`icd_code` LEFT JOIN ( SELECT f.patient_id, f.follow_up_date FROM ( SELECT fur.* FROM stw.`follow_up_record` fur ORDER BY fur.`follow_up_date` DESC ) f GROUP BY f.patient_id ) fu ON fu.`patient_id` = a.patient_id LEFT JOIN ( SELECT f.patient_id, f.follow_up_date FROM ( SELECT fur.* FROM stw.`follow_up_record` fur ORDER BY fur.`follow_up_date` ) f GROUP BY f.patient_id ) ffur ON ffur.`patient_id` = a.patient_id GROUP BY a.patient_id ) b WHERE TIMESTAMPDIFF( MONTH, b.start_follow_up_date, DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S')) <= ? AND b.diagnosis LIKE ? AND b. NAME LIKE ? ORDER BY CONVERT (b.`name` USING gbk) COLLATE gbk_chinese_ci";
						list = dbo.executeQueryAsJSON(sql, new int[4], new String[]{userId,month,"%"+diagnosis+"%","%"+name+"%"});
					}					
				}
				
				return list;
			}
		});
	}