//以前写的东西 里面有自动递归删除 。 留着
/*
* L.java 2008-1-21 下午01:00:32
*
*
*/
package com.edgedu.module.em.dao.jdbc;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.support.DataAccessUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import util.DateUtils;
import util.LabelValue;
import com.edgedu.commons.DataCodeSet;
import com.edgedu.commons.persistence.jdbc.core.ReflectionRowExtractor;
import com.edgedu.module.em.dao.EmStudentDAO;
import com.edgedu.module.em.domain.EmClass;
import com.edgedu.module.em.domain.EmStudent;
/**
*
* @author Wei jian
*
*/
public class EmStudentDAOJdbc extends JdbcDaoSupport implements EmStudentDAO {
JdbcTemplate jt = getJdbcTemplate();
/**
* 学员信息 查询
*
*/
public List getEmStudents(String name, String classname, String status)
throws DataAccessException {
JdbcTemplate jt = getJdbcTemplate();
String sql = "SELECT c.name , a.emid, a.id, a.name,s.unit,s.unit_time, a.sex, a.start_date, a.stop_date, a.school, a.specialty, a.eng_level, a.jap_level, s.status ,s.home_phone ,a.phone, a.operator ,a.operate_date "
+ "FROM em_class c, em_student s, em_archive a "
+ "WHERE c.classid=s.classid AND s.emid=a.emid AND a.del_flag = 'N' AND a.name LIKE ? AND c.name LIKE ? AND s.pay_status LIKE ?";
List list = jt.query(sql, new Object[] { name ,classname,status},
new RowMapper() {
public Object mapRow(ResultSet rs, int arg1)
throws SQLException {
EmStudent a = new EmStudent();
a.setEmid(rs.getString("a.emid"));
a.setId(rs.getString("a.id"));
a.setStatus(rs.getString("s.status"));
a.setHomePhone(rs.getString("s.home_phone"));
a.setClassname(rs.getString("c.name"));
a.setName(rs.getString("a.name"));
a.setSex(rs.getString("a.sex"));
a.setStartDate(rs.getDate("a.start_date"));
a.setStopDate(rs.getDate("a.stop_date"));
a.setSchool(rs.getString("a.school"));
a.setSpecialty(rs.getString("a.specialty"));
a.setEngLevel(rs.getString("a.eng_level"));
a.setJapLevel(rs.getString("a.jap_level"));
a.setOperateDate(rs.getDate("a.operate_date"));
a.setOperator(rs.getString("a.operator"));
a.setUnit(rs.getString("s.unit"));
a.setUnitTime(rs.getString("s.unit_time"));
return a;
}
});
DataCodeSet code = DataCodeSet.getInstance();
for (int i = 0; i < list.size(); i++) {
EmStudent a = (EmStudent) list.get(i);
a.setSex(getCodeByT("name", "sys_dict", "id",a.getSex()));
a.setOperator(getCodeByT("name", "em_archive", "emid",a.getOperator()));
a.setEngLevel(getCodeByT("name", "sys_dict", "id",a.getEngLevel()));
a.setJapLevel(getCodeByT("name", "sys_dict", "id",a.getJapLevel()));
}
return list;
}
/**
* 学生信息保存
*
*/
public void saveEmStudent(EmStudent e) throws DataAccessException {
JdbcTemplate jt = getJdbcTemplate();
/** em_archive */
String sql2 = "insert into em_archive(emid,id,timecard_no,name,sex,start_date,stop_date,parkingno,photo,nation,folk,birthday,married,phone,oldaddress,education,school,specialty,eng_level,jap_level,memo,del_flag,operator,operate_date) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
jt.update(sql2, new Object[] { e.getEmid(), e.getId(),
e.getTimecardNo(), e.getName(), e.getSex(), e.getStartDate(),
e.getStopDate(), e.getParkingno(), e.getPhoto(), e.getNation(),
e.getFolk(), e.getBirthday(), e.getMarried(), e.getPhone(),
e.getOldaddress(), e.getEducation(), e.getSchool(),
e.getSpecialty(), e.getEngLevel(), e.getJapLevel(),
e.getMemo(), e.getDelFlag(), e.getOperator(),
e.getOperateDate() });
/** em_student */
String sql1 = "insert into em_student(emid,computer_no,classid,home_phone,now_address,address,schooling,pay_type,pay_status,unit,unit_time,status) values(?,?,?,?,?,?,?,?,?,?,?,?)";
jt
.update(sql1, new Object[] { e.getEmid(), e.getComputerNo(),
e.getClassid(), e.getHomePhone(), e.getNowAddress(),
e.getAddress(), e.getSchooling(), e.getPayType(),
e.getPayStatus(), e.getUnit(), e.getUnitTime(),
e.getStatus() });
}
/**
* 学员信息 更新转向
*
*/
public Object update(String emid) throws DataAccessException {
JdbcTemplate jt = getJdbcTemplate();
EmStudent emstudent = null;
String sql = "SELECT a.name , a.emid emid , a.id , a.timecard_no , a.sex , s.classid , s.computer_no computer_no , a.married married "
+ " ,a.birthday , a.start_date , a.stop_date , a.nation , a.folk , a.polity , "
+ "a.oldaddress , a.school ,s.unit unit,s.unit_time unit_time ,a.education , a.specialty ,a.parkingno , a.eng_level eng_level , "
+ "a.jap_level , s.schooling , s.pay_type pay_type , s.pay_status pay_status , s.address address , s.home_phone home_phone , "
+ "s.now_address ,a.phone , a.memo "
+ "FROM em_student s, em_archive a WHERE s.emid =a.emid AND s.emid = ?";
List list = jt.query(sql, new Object[] { emid },
new ReflectionRowExtractor(EmStudent.class));
emstudent = (EmStudent) DataAccessUtils.uniqueResult(list);
return emstudent;
}
/**
* 学生信息更新保存
*
*/
public void saveUpdateEmStudent(EmStudent e) throws DataAccessException {
JdbcTemplate jt = getJdbcTemplate();
String sql = "UPDATE em_student SET computer_no=? , classid=? , home_phone=? , now_address=? ,address=? , schooling=? , pay_type=? , pay_status=? ,unit =? ,unit_time =? ,status =? WHERE emid=?";
jt.update(sql,
new Object[] { e.getComputerNo(), e.getClassid(),
e.getHomePhone(), e.getNowAddress(), e.getAddress(),
e.getSchooling(), e.getPayType(), e.getPayStatus(),
e.getUnit(),e.getUnitTime(),e.getStatus(),
e.getEmid() });
String sql2 = "UPDATE em_archive SET id=? , timecard_no=? , name =? , sex =? ,start_date =? ,stop_date =? ,parkingno =? ,photo =? ,nation =? ,folk =? ,polity =? ,birthday =? ,married =? ,phone =? ,oldaddress =? ,education =? ,school =? ,specialty =? ,eng_level =? ,jap_level =? ,memo =? ,del_flag =? ,operator =? ,operate_date =? WHERE emid=? ";
jt.update(sql2, new Object[] { e.getId(), e.getTimecardNo(),
e.getName(), e.getSex(), e.getStartDate(), e.getStopDate(),
e.getParkingno(), e.getPhoto(), e.getNation(), e.getFolk(),
e.getPolity(), e.getBirthday(), e.getMarried(), e.getPhone(),
e.getOldaddress(), e.getEducation(), e.getSchool(),
e.getSpecialty(), e.getEngLevel(), e.getJapLevel(),
e.getMemo(), e.getDelFlag(), e.getOperator(),
e.getOperateDate(), e.getEmid() });
}
/**
* 得到emid
*
*/
public String getEmid(String id) throws DataAccessException {
String emid = "";
JdbcTemplate jt = getJdbcTemplate();
String sql = "select emid from em_archive where emid like ? order by emid desc limit 0,1";
List list = jt.query(sql, new Object[] { id + "%" }, new RowMapper() {
public Object mapRow(ResultSet rs, int arg1) throws SQLException {
String result = rs.getString("emid");
return result;
}
});
String idtemp = (String) DataAccessUtils.uniqueResult(list);
if (!"".equals(idtemp) && idtemp != null) {
String temp1 = idtemp.substring(idtemp.length() - 3);
int e = Integer.parseInt(temp1) + 1;
String estr = String.valueOf(e);
if (estr.length() == 1) {
emid = "00" + estr;
} else {
if (estr.length() == 2) {
emid = "0" + estr;
} else {
if (estr.length() == 3) {
emid = estr;
}
}
}
} else {
emid = "001";
}
return emid;
}
/**
* 学员信息删除
*/
public void delete(String emid) throws DataAccessException {
try {
deleteByIdAndT(emid, "em_student", " emid");
deleteByIdAndT(emid, "em_archive", " emid");
} catch (Exception e) {
setUse(emid);
}
}
public void setUse(String emid)throws DataAccessException {
String sql="update em_archive set del_flag = 'Y' where emid = ?" ;
JdbcTemplate jt = getJdbcTemplate();
jt.update(sql, new Object[]{emid});
}
/**
* 付费信息查询
*
*/
public List getEmPayment(String planDate, String factDate, String emid)
throws DataAccessException {
String sql = "SELECT p.* FROM EdgeduMana.em_payment p ,EdgeduMana.em_archive a WHERE a.emid = p.emid AND a.del_flag = 'N' AND p.plan_date like ? AND p.fact_date like ? AND p.emid like ?";
JdbcTemplate jt = getJdbcTemplate();
List list = jt.query(sql, new Object[] { planDate, factDate, emid },
new ReflectionRowExtractor(EmStudent.class));
DataCodeSet code = DataCodeSet.getInstance();
for (Iterator iter = list.iterator(); iter.hasNext();) {
EmStudent e = (EmStudent) iter.next();
e.setName(code.getNameByEmid(e.getEmid()));
}
return list;
}
/**
* 付费信息 更新转向
*
*/
public List getEmPaymentUpdate(String pkid) throws DataAccessException {
String sql = "select * from EdgeduMana.em_payment where pkid = ? ";
JdbcTemplate jt = getJdbcTemplate();
List list = jt.query(sql, new Object[] { pkid },
new ReflectionRowExtractor(EmStudent.class));
return list;
}
/**
* 付费信息 增加保存
*
*/
public void insertEmPayment(EmStudent e) throws DataAccessException {
String sql = "insert into EdgeduMana.em_payment ( emid ,plan_date ,fact_date ,plan_payment ,fact_payment ,status ,memo ,operator,operate_date) values(?,?,?,?,?,?,?,?,?) ";
JdbcTemplate jt = getJdbcTemplate();
jt.update(sql, new Object[] { e.getEmid(), e.getPlanDate(),
e.getFactDate(), e.getPlanPayment(), e.getFactPayment(),
"EM_NOT_SOLVE", e.getMemo() ,e.getOperator(),e.getOperateDate()});
}
/**
* 付费信息 更新保存
*
*/
public void updateEmPayment(EmStudent e) throws DataAccessException {
String sql = "update EdgeduMana.em_payment set plan_date = ? ,fact_date = ? ,plan_payment = ? , fact_payment = ? ,memo = ? where pkid = ? ";
JdbcTemplate jt = getJdbcTemplate();
jt.update(sql, new Object[] { e.getPlanDate(), e.getFactDate(),
e.getPlanPayment(), e.getFactPayment(), e.getMemo(),
e.getPkid() });
}
/**
* 付费信息 删除
*
*/
public void deleteEmPayment(String emid) throws DataAccessException {
deleteByIdAndT(emid, "em_payment", "emid");
}
/**
* 学生成绩 查询
*
*/
public List getEmGrade(String subject, String examDate, String emid)
throws DataAccessException {
String sql = "SELECT g.* FROM EdgeduMana.em_grade g ,EdgeduMana.em_archive a WHERE a.emid = g.emid AND a.del_flag = 'N' AND g.subject like ? AND g.exam_date like ? AND g.emid like ? ";
JdbcTemplate jt = getJdbcTemplate();
List list = jt.query(sql, new Object[] { subject, examDate, emid },
new ReflectionRowExtractor(EmStudent.class));
DataCodeSet code = DataCodeSet.getInstance();
for (Iterator iter = list.iterator(); iter.hasNext();) {
EmStudent e = (EmStudent) iter.next();
String examDateStr = DateUtils.dateToString(
DateUtils.DATE_PATTERN, e.getExamDate());
e.setExamDateStr(examDateStr);
e.setName(code.getNameByEmid(e.getEmid()));
e.setSubject(getCodeByT("name", "sys_dict", "id", e.getSubject()));
}
return list;
}
/**
* 学生成绩 更新转向
*
*/
public List updateEmGrade(String pkid) throws DataAccessException {
String sql = "select * from EdgeduMana.em_grade where pkid = ?";
JdbcTemplate jt = getJdbcTemplate();
List list = jt.query(sql, new Object[] { pkid },
new ReflectionRowExtractor(EmStudent.class));
return list;
}
/**
* 学生成绩 增加保存
*
*/
public void insertEmGrade(EmStudent e) throws DataAccessException {
String sql = "insert into EdgeduMana.em_grade ( subject ,grade_name ,exam_date ,grade ,memo ,operator ,operate_date ,emid) values(?,?,?,?,?,?,?,?)";
JdbcTemplate jt = getJdbcTemplate();
jt.update(sql, new Object[] { e.getSubject(), e.getGradeName(),
e.getExamDate(), e.getGrade(), e.getMemo(), e.getOperator(),
e.getOperateDate(), e.getEmid() });
}
/**
* 学生成绩 更新保存
*
*/
public void saveUpdateEmGrade(EmStudent e) throws DataAccessException {
String sql = "update EdgeduMana.em_grade set subject = ? ,grade_name = ? ,exam_date = ? ,grade = ? ,memo = ? ,operator = ? ,operate_date = ? where pkid = ?";
JdbcTemplate jt = getJdbcTemplate();
jt.update(sql, new Object[] { e.getSubject(), e.getGradeName(),
e.getExamDate(), e.getGrade(), e.getMemo(), e.getOperator(),
e.getOperateDate(), e.getPkid() });
}
/**
* 查询联系方式
*
*/
public List searchRelation(String emid) throws DataAccessException {
String sql = "select a.emid,a.name,a.phone,a.email,a.msn,a.qq,a.skype,s.now_address,s.address ,s.home_phone from EdgeduMana.em_archive a, EdgeduMana.em_student s where a.emid = ? ";
JdbcTemplate jt = getJdbcTemplate();
List list = jt.query(sql, new Object[] { emid },
new ReflectionRowExtractor(EmStudent.class));
return list;
}
/**
* 更新回显联系方式
*
*/
public void updateRelation(EmStudent e) throws DataAccessException {
// em_archive 姓名 联系电话 email msn qq skype student 当前住址 家庭住址 家庭电话homePhone
String sql1 = "update EdgeduMana.em_archive set name = ? ,phone = ? ,email = ? , qq = ? ,skype = ? where emid = ?";
JdbcTemplate jt = getJdbcTemplate();
jt.update(sql1, new Object[] { e.getName(), e.getPhone(), e.getEmail(),
e.getQq(), e.getSkype(), e.getEmid() });
String sql2 = "update EdgeduMana.em_student set now_address = ? ,address = ? , home_phone = ? where emid = ? ";
jt.update(sql2, new Object[] { e.getNowAddress(), e.getAddress(),
e.getHomePhone(), e.getEmid() });
System.err.println();
}
/** ------------------------------------------------------------------------------------------------- */
/** ----------------删除------------------------------------------------------------------------------ */
/** ------------------------------------------------------------------------------------------------- */
/**
* 执行 保护删除
*
*/
public int deleteIfKey(String pkid, String table, String id)
throws DataAccessException {
if (table == null || table.trim().length() == 0 || id == null
|| id.trim().length() == 0)
return 0;
// 如果有以此表的字段为 外键的子表 则不执行删除
if (getFKey(table).size() != 0 || getFKey(table) != null)
return 0;
return deleteByIdAndT(id, table, pkid);
}
/**
* 执行 强制删除
*
*/
public int deleteAllKey(String pkid, String table, String id)
throws DataAccessException {
if (table == null || table.trim().length() == 0 || id == null
|| id.trim().length() == 0)
return 0;
// 删除所有以此表的字段为外键的 所有子表的相应记录
List list = getFKey(table);
for (Iterator iter = list.iterator(); iter.hasNext();) {
String tables = (String) iter.next();
deleteAllKey(id, tables, pkid);
}
return deleteByIdAndT(id, table, pkid);
}
/**
* 查询此表所关联的所有外键表 返回表名和主键
*
*/
public List getFKey(String table) throws DataAccessException {
if (table == null || table.trim().length() == 0)
return null;
String sql = "SELECT TABLE_SCHEMA ,TABLE_NAME ,COLUMN_NAME FROM information_schema.COLUMNS WHERE COLUMN_KEY = 'PRI' AND TABLE_NAME in (SELECT TABLE_NAME FROM information_schema.KEY_COLUMN_USAGE where REFERENCED_TABLE_NAME = '"
+ table
+ "' AND REFERENCED_COLUMN_NAME IN (SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_NAME = '"
+ table + "')) ";
JdbcTemplate jt = getJdbcTemplate();
List list = jt.query(sql, new RowMapper() {
public Object mapRow(ResultSet rs, int arg1) throws SQLException {
String result = rs.getString("TABLE_NAME");
return result;
}
});
return list;
}
/**
* 根据键名称数值 和表名 删除记录
*
*/
public int deleteByIdAndT(String id, String table, String pkid)
throws DataAccessException {
if (table == null || table.trim().length() == 0
|| (id == null || id.trim().length() == 0)
|| (pkid == null || pkid.trim().length() == 0))
return 0;
JdbcTemplate jt = getJdbcTemplate();
String sql = "delete from " + table + " where " + pkid + " = ?";
int i = jt.update(sql, new Object[] { id });
return i;
}
/** ------------------------------------------------------------------------------------------------- */
/**
* 通过表名 获取单项
*
*/
public String getCodeByT(String name, String table, String codename,
String codeid) throws DataAccessException {
final String fname = name;
String sql = "select " + name + " from " + table + " where " + codename
+ " = ? ";
JdbcTemplate jt = getJdbcTemplate();
List list = jt.query(sql, new Object[] { codeid }, new RowMapper() {
public Object mapRow(ResultSet rs, int arg1) throws SQLException {
String result = rs.getString(fname);
return result;
}
});
if (list != null && list.size() > 1)
return (String) list.get(1);
else if(list.size() == 1)
return (String) list.get(0);
else
return null;
}
/**
* 通过表名 获取选项
*
*/
public List getCodesByT(String id, String name, String table)
throws DataAccessException {
final String fid = id;
final String fname = name;
String sql = "select " + name + "," + id + " from " + table;
JdbcTemplate jt = getJdbcTemplate();
List list = jt.query(sql, new RowMapper() {
public Object mapRow(ResultSet rs, int arg1) throws SQLException {
LabelValue lv = new LabelValue();
lv.setLabel(rs.getString(fname));
lv.setValue(rs.getString(fid));
return lv;
}
});
return list;
}
/**
* 通过表名 获取选项
*
*/
public List getCodesByT(String id, String name, String table,
String codename, String codeid) throws DataAccessException {
final String fid = id;
final String fname = name;
String sql = "select " + name + "," + id + " from " + table + " where "
+ codename + " like '" + codeid + "'";
JdbcTemplate jt = getJdbcTemplate();
List list = jt.query(sql, new RowMapper() {
public Object mapRow(ResultSet rs, int arg1) throws SQLException {
LabelValue lv = new LabelValue();
lv.setLabel(rs.getString(fname));
lv.setValue(rs.getString(fid));
return lv;
}
});
return list;
}
/**
* 通过表名 获取选项 两个查询条件
*
*/
public List getCodesByT(String id, String name, String table,
String codename, String codeid,String wcolumn ,String columnvalue) throws DataAccessException {
final String fid = id;
final String fname = name;
String sql = "select " + name + "," + id + " from " + table + " where "
+ codename + " like '" + codeid + "' and "+wcolumn+" = '"+columnvalue+"'";
JdbcTemplate jt = getJdbcTemplate();
List list = jt.query(sql, new RowMapper() {
public Object mapRow(ResultSet rs, int arg1) throws SQLException {
LabelValue lv = new LabelValue();
lv.setLabel(rs.getString(fname));
lv.setValue(rs.getString(fid));
return lv;
}
});
return list;
}
}