[java]代码库package .dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import .model.GuaHao;
import .model.PageBean;
import .model.Patient;
import .util.DateUtil;
import .util.StringUtil;
public class GuaHaoDao {
public ResultSet guahaoList(Connection con,PageBean pageBean,Patient patient,GuaHao guahao, String s_bGhDate, String s_eGhDate)throws Exception{
StringBuffer sb=new StringBuffer("SELECT * FROM t_ghinfo g ,t_patient p ,t_user u WHERE p.userId=u.userId AND g.patientId=p.patientId");
if(patient.getUserId()!=-1){
sb.append(" and p.userId = '"+patient.getUserId()+"'");
}
if(StringUtil.isNotEmpty(guahao.getPatientName())){
sb.append(" and p.patientName like '%"+guahao.getPatientName()+"%'");
}
if(StringUtil.isNotEmpty(patient.getSex())){
sb.append(" and p.sex ='"+patient.getSex()+"'");
}
if(StringUtil.isNotEmpty(s_bGhDate)){
sb.append(" and TO_DAYS(g.date)>=TO_DAYS('"+s_bGhDate+"')");
}
if(StringUtil.isNotEmpty(s_eGhDate)){
sb.append(" and TO_DAYS(g.date)<=TO_DAYS('"+s_eGhDate+"')");
}
if(StringUtil.isNotEmpty(guahao.getOfficeName())){
sb.append(" and g.officeName ='"+guahao.getOfficeName()+"'");
}
//分页
if(pageBean!=null){
sb.append(" limit "+pageBean.getStart()+","+pageBean.getRows());
}
PreparedStatement pstmt=con.prepareStatement(sb.toString());
return pstmt.executeQuery();
}
/**
* 获取数据总条数
* @param con
* @param grade
* @return
* @throws Exception
*/
public int guahaoCount(Connection con,Patient patient, GuaHao guahao, String s_bGhDate, String s_eGhDate)throws Exception{
StringBuffer sb=new StringBuffer("select count(*) as total from t_ghinfo g ,t_patient p ,t_user u WHERE p.userId=u.userId AND g.patientId=p.patientId");
if(patient.getUserId()!=-1){
sb.append(" and p.userId = '"+patient.getUserId()+"'");
}
if(StringUtil.isNotEmpty(guahao.getPatientName())){
sb.append(" and p.patientName like '%"+guahao.getPatientName()+"%'");
}
if(StringUtil.isNotEmpty(patient.getSex())){
sb.append(" and p.sex ='"+patient.getSex()+"'");
}
if(StringUtil.isNotEmpty(s_bGhDate)){
sb.append(" and TO_DAYS(g.date)>=TO_DAYS('"+s_bGhDate+"')");
}
if(StringUtil.isNotEmpty(s_eGhDate)){
sb.append(" and TO_DAYS(g.date)<=TO_DAYS('"+s_eGhDate+"')");
}
if(StringUtil.isNotEmpty(guahao.getOfficeName())){
sb.append(" and g.officeName ='"+guahao.getOfficeName()+"'");
}
PreparedStatement pstmt=con.prepareStatement(sb.toString());
ResultSet rs=pstmt.executeQuery();
if(rs.next()){
return rs.getInt("total");
}else{
return 0;
}
}
/**
* 数据库插入数据
* @param con
* @param guahao
* @return
* @throws Exception
*/
public int guahaoAdd(Connection con, GuaHao guahao)throws Exception{
String sql="insert into t_ghinfo values(null,?,?,?,?,?)";
PreparedStatement pstmt=con.prepareStatement(sql);
pstmt.setInt(1, guahao.getPatientId());
pstmt.setString(2, new java.text.SimpleDateFormat("yyyyMMddHHmmss").format(new java.util.Date()));//将当前时间作为流水号码
pstmt.setString(3, DateUtil.formatDate(guahao.getDate(), "yyyy-MM-dd"));
pstmt.setString(4, guahao.getOfficeName());
pstmt.setString(5, guahao.getGhDesc());
return pstmt.executeUpdate();
}
/**
* 修改数据
* @param con
* @param guahao
* @return
* @throws Exception
*/
public int guahaoModify(Connection con, GuaHao guahao)throws Exception{
String sql="update t_ghinfo set patientId=? , date=? , officeName=? , ghDesc=? where ghId=?";
PreparedStatement pstmt=con.prepareStatement(sql);
pstmt.setInt(1, guahao.getPatientId());
pstmt.setString(2, DateUtil.formatDate(guahao.getDate(), "yyyy-MM-dd"));
pstmt.setString(3, guahao.getOfficeName());
pstmt.setString(4, guahao.getGhDesc());
pstmt.setInt(5, guahao.getGhId());
return pstmt.executeUpdate();
}
/**
* 删除数据
* @param con
* @param delIds
* @return
* @throws Exception
*/
public int guahaoDelete(Connection con,String delIds)throws Exception{
String sql="delete from t_ghinfo where ghId in("+delIds+")";
PreparedStatement pstmt=con.prepareStatement(sql);
return pstmt.executeUpdate();
}
/**
* 统计每个科室的挂号数量
* @param con
* @return
* @throws Exception
*/
public ResultSet guahaoChart(Connection con,Patient patient, GuaHao guahao) throws Exception {
StringBuffer sb=new StringBuffer("SELECT g.officeName,COUNT(*) FROM t_ghinfo g ,t_patient p ,t_user u WHERE p.userId=u.userId AND g.patientId=p.patientId");
if(patient.getUserId()!=-1){
sb.append(" AND p.userId = '"+patient.getUserId()+"'");
}
sb.append(" GROUP BY g.officeName");
PreparedStatement pstmt=con.prepareStatement(sb.toString());
return pstmt.executeQuery();
}
}
[代码运行效果截图]
[源代码打包下载]
截图信息.rar(50积分)[17 次下载]
db_patient.rar(50积分)[27 次下载]
