managecalendardao.java

公司自己开发的工作流引擎

package cn.com.iaspec.workflow.manage.dao;

import java.sql.*;
import java.util.*;
import cn.com.iaspec.workflow.db.*;
import cn.com.iaspec.workflow.manage.*;
import cn.com.iaspec.workflow.util.*;
import cn.com.iaspec.workflow.vo.workflow.*;

/**
 * <p>Title: 日历管理</p>
 *
 * <p>Description: 深圳市劳动仲裁信息管理系统</p>
 *
 * <p>Copyright: Copyright (c) 2005</p>
 *
 * <p>Company: 永泰软件工程有限公司</p>
 *
 * @author syj
 * @version 1.0
 */
public class ManageCalendarDAO{
  /**
   * 日历的查询分页函数 type（上一页或者是下一页）
   * @param rownum int
   * @param type String
   * @return ArrayList
   * @throws ManageCalendarException
   */
  public ArrayList query(int rownum,int RowsPerPage,String type)
      throws ManageCalendarException{
    ArrayList list=new ArrayList();
    String sql="select calendar_id,calendar_date,calendar_type,to_char(calendar_am_btime,'hh24:mi:ss')"+" as calendar_am_btime, to_char(calendar_am_etime,'hh24:mi:ss') as calendar_am_etime,"+
        " to_char(calendar_pm_btime,'hh24:mi:ss') as calendar_pm_btime,"+" to_char(calendar_pm_etime,'hh24:mi:ss') as calendar_pm_etime,calendar_memo,calendar_week"+
        " from (select rownum rm,a.* from la_calendar a,la_calendar b"+
        " where b.calendar_date=trunc(sysdate) and rownum<?) where rm>=?";
    PreparedStatement prep=null;
    Connection conn=null;
    ResultSet rs=null;
    int begin_id=0;
    int end_id=0;
    if(rownum==0){
      begin_id=getCalendar_id();
      end_id=begin_id+RowsPerPage;
    }
    else{

      if(type.equals("down")){
        begin_id=rownum;
        end_id=begin_id+RowsPerPage;
      }
      else if(type.equals("up")){
        begin_id=rownum-RowsPerPage;
        end_id=rownum;
      }
    }

    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      prep=conn.prepareStatement(sql);
      prep.setInt(1,end_id);
      prep.setInt(2,begin_id);
      rs=prep.executeQuery();

      while(rs.next()){
        WfCalendar la_calendar=new WfCalendar();
        la_calendar.setCalendar_id(rs.getInt("calendar_id"));
        la_calendar.setCalendar_date(rs.getDate("calendar_date"));
        la_calendar.setCalendar_type(rs.getString("calendar_type"));
        la_calendar.setCalendar_am_btime(rs.getString("calendar_am_btime"));
        la_calendar.setCalendar_am_etime(rs.getString("calendar_am_etime"));
        la_calendar.setCalendar_pm_btime(rs.getString("calendar_pm_btime"));
        la_calendar.setCalendar_pm_etime(rs.getString("calendar_pm_etime"));
        la_calendar.setCalendar_memo(rs.getString("calendar_memo"));
        la_calendar.setCalendar_week(rs.getString("calendar_week"));
        list.add(la_calendar);
      }
    }
    catch(Exception e){
      e.printStackTrace();
      throw new ManageCalendarException("日历分页查询异常");
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(rs);
      WorkflowDBConnectionManager.getInstance().close(prep,false);
      WorkflowDBConnectionManager.getInstance().close(conn);

    }
    return list;
  }

  /**
   * 取得当天日期的ID
   * @return int
   * @throws ManageCalendarException
   */
  public int getCalendar_id()
      throws ManageCalendarException{
    String sql=
        "select t.calendar_id from la_calendar t where t.calendar_date=trunc(sysdate) ";
    PreparedStatement prep=null;
    Connection conn=null;
    ResultSet rs=null;
    int calendar_id=0;

    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      prep=conn.prepareStatement(sql);
      rs=prep.executeQuery();
      while(rs.next()){
        calendar_id=rs.getInt("calendar_id");
      }
    }
    catch(Exception e){
      throw new ManageCalendarException("日历查询异常");
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(rs);
      WorkflowDBConnectionManager.getInstance().close(prep,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }
    return calendar_id;

  }

  /**
   * 按时间查询日历
   * @param calendar_date String
   * @return La_calendar
   * @throws ManageCalendarException
   */
  public WfCalendar query(String calendar_date)
      throws ManageCalendarException{
    WfCalendar la_calendar=new WfCalendar();
    String sql="select calendar_id,calendar_date,calendar_type,to_char(calendar_am_btime,'hh24:mi:ss')"+" as calendar_am_btime, to_char(calendar_am_etime,'hh24:mi:ss') as calendar_am_etime,"+
        " to_char(calendar_pm_btime,'hh24:mi:ss') as calendar_pm_btime,"+" to_char(calendar_pm_etime,'hh24:mi:ss') as calendar_pm_etime,calendar_memo,calendar_week"+
        " from la_calendar  where calendar_date=to_date(?,'yyyy-mm-dd')";
    PreparedStatement prep=null;
    Connection conn=null;
    ResultSet rs=null;
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      prep=conn.prepareStatement(sql);
      prep.setString(1,calendar_date);
      rs=prep.executeQuery();
      while(rs.next()){
        la_calendar.setCalendar_id(rs.getInt("calendar_id"));
        la_calendar.setCalendar_date(rs.getDate("CALENDAR_DATE"));
        la_calendar.setCalendar_type(rs.getString("CALENDAR_TYPE"));
        la_calendar.setCalendar_am_btime(rs.getString("CALENDAR_AM_BTIME"));
        la_calendar.setCalendar_am_etime(rs.getString("CALENDAR_AM_ETIME"));
        la_calendar.setCalendar_pm_btime(rs.getString("CALENDAR_PM_BTIME"));
        la_calendar.setCalendar_pm_etime(rs.getString("CALENDAR_PM_ETIME"));
        la_calendar.setCalendar_memo(rs.getString("CALENDAR_MEMO"));
        la_calendar.setCalendar_week(rs.getString("CALENDAR_WEEK"));
      }

    }
    catch(Exception e){
      throw new ManageCalendarException("按时间查询日历异常");
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(rs);
      WorkflowDBConnectionManager.getInstance().close(prep,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }
    return la_calendar;
  }

  /**
   * 根据时间段按（日期类型）条件查询
   * @param calendar_type String
   * @param begin_time String
   * @param end_time String
   * @return ArrayList
   * @throws ManageCalendarException
   */
  public ArrayList queryCalendar(String sql)
      throws ManageCalendarException{
    ArrayList list=new ArrayList();
    PreparedStatement prep=null;
    Connection conn=null;
    ResultSet rs=null;
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      prep=conn.prepareStatement(sql);
      rs=prep.executeQuery();
      while(rs.next()){
        WfCalendar la_calendar=new WfCalendar();
        la_calendar.setCalendar_id(rs.getInt("calendar_id"));
        la_calendar.setCalendar_date(rs.getDate("CALENDAR_DATE"));
        la_calendar.setCalendar_type(rs.getString("CALENDAR_TYPE"));
        la_calendar.setCalendar_am_btime(rs.getString("CALENDAR_AM_BTIME"));
        la_calendar.setCalendar_am_etime(rs.getString("CALENDAR_AM_ETIME"));
        la_calendar.setCalendar_pm_btime(rs.getString("CALENDAR_PM_BTIME"));
        la_calendar.setCalendar_pm_etime(rs.getString("CALENDAR_PM_ETIME"));
        la_calendar.setCalendar_memo(rs.getString("CALENDAR_MEMO"));
        la_calendar.setCalendar_week(rs.getString("CALENDAR_WEEK"));
        list.add(la_calendar);
      }

    }
    catch(Exception e){
      throw new ManageCalendarException("按时间查询日历异常");
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(rs);
      WorkflowDBConnectionManager.getInstance().close(prep,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }

    return list;
  }

  /**
   *  根据时间段按（日期类型）条件查询
   * @param calendar_type String
   * @param begin_time String
   * @param end_time String
   * @return ArrayList
   * @throws ManageCalendarException
   */
  public ArrayList query(String calendar_type,String begin_time,String end_time)
      throws ManageCalendarException{
    ArrayList list=new ArrayList();
    String sql="select calendar_id,calendar_date,calendar_type,to_char(calendar_am_btime,'hh24:mi:ss')"+" as calendar_am_btime, to_char(calendar_am_etime,'hh24:mi:ss') as calendar_am_etime,"+
        " to_char(calendar_pm_btime,'hh24:mi:ss') as calendar_pm_btime,"+" to_char(calendar_pm_etime,'hh24:mi:ss') as calendar_pm_etime,calendar_memo,calendar_week"+
        " from la_calendar  where  calendar_date between to_date('"+begin_time+
        "','yyyy-mm-dd')"+" and to_date('"+end_time+
        "','yyyy-mm-dd') and calendar_type='"+calendar_type+"'";
    String sq="select calendar_id,calendar_date,calendar_type,to_char(calendar_am_btime,'hh24:mi:ss')"+" as calendar_am_btime, to_char(calendar_am_etime,'hh24:mi:ss') as calendar_am_etime,"+
        " to_char(calendar_pm_btime,'hh24:mi:ss') as calendar_pm_btime,"+" to_char(calendar_pm_etime,'hh24:mi:ss') as calendar_pm_etime,calendar_memo,calendar_week"+
        " from la_calendar  where  calendar_date between to_date('"+begin_time+
        "','yyyy-mm-dd')"+" and to_date('"+end_time+"','yyyy-mm-dd') ";
    if(calendar_type.equals("0")){
      list=queryCalendar(sq);
    }
    else{
      list=queryCalendar(sql);
    }

    return list;
  }

  /**
   * 更新日历信息
   * @param la_calendar La_calendar
   * @return int
   * @throws ManageCalendarException
   */
  public int update(WfCalendar la_calendar)
      throws ManageCalendarException{
    PreparedStatement prep=null;
    Connection conn=null;
    int i=0;
    String sql=" update LA_CALENDAR set CALENDAR_TYPE=?,CALENDAR_AM_BTIME=?,"+
        " CALENDAR_AM_ETIME=?,CALENDAR_PM_BTIME=?,CALENDAR_PM_ETIME=?,CALENDAR_MEMO=?"+
        "  where CALENDAR_ID=?";
    System.out.println("la_calendar.getCalendar_am_btime()==="+
        la_calendar.getCalendar_am_btime());
    System.out.println(
        "TimeTools.getTime(la_calendar.getCalendar_am_btime())===="+
        TimeTools.getTime(la_calendar.getCalendar_am_btime()));
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      prep=conn.prepareStatement(sql);
      prep.setString(1,la_calendar.getCalendar_type());
      prep.setTimestamp(2,TimeTools.getTime(la_calendar.getCalendar_am_btime()));
      prep.setTimestamp(3,TimeTools.getTime(la_calendar.getCalendar_am_etime()));
      prep.setTimestamp(4,TimeTools.getTime(la_calendar.getCalendar_pm_btime()));
      prep.setTimestamp(5,TimeTools.getTime(la_calendar.getCalendar_pm_etime()));
      prep.setString(6,la_calendar.getCalendar_memo());
      prep.setInt(7,la_calendar.getCalendar_id());
      i=prep.executeUpdate();
    }
    catch(Exception e){
      throw new ManageCalendarException();
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(prep,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }

    return i;
  }
}