submitrecorddao.java

采用JAVA开发

    }catch( Throwable e ){
      logger.error(e, e);
    }finally{
      if ( rs != null ){
        try {
          rs.close();
        }
        catch (Throwable ex) {
          logger.error(ex, ex);
        }
      }
      if ( stmt != null ){
        try {
          stmt.close();
        }
        catch (Throwable ex) {
          logger.error(ex, ex);
        }
      }
      if ( con != null ){
        try {
          con.close();
        }
        catch (Throwable ex) {
          logger.error(ex, ex);
        }
      }
    }
    return rt;
  }

  /**更新订阅记录为退订。
   * @param feeMobile 支付卡号
   * @param submitId 订阅ID
   * */
  public static int unsubmit(String feeMobile, String submitId){
    int rt = -1;
    Connection con = null;
    PreparedStatement stmt = null;
    try{
      con = ConnectionManager.getInstance().getConnection(SubServiceTypeDao.class);
      stmt = con.prepareStatement("update submitrecord set STATUS="+
          SubmitStatus.UNSUBSCRIBED.getValue()+
          ",unsubDate=sysdate where id=? and FEEMOBILE=? and STATUS="+
          SubmitStatus.SUBSCRIBED.getValue());
      stmt.setString(1, submitId);
      stmt.setString(2, feeMobile);
      rt = stmt.executeUpdate();
    }catch( Throwable e ){
      logger.error(e, e);
    }finally{
      if ( stmt != null ){
        try {
          stmt.close();
        }
        catch (Throwable ex) {
          logger.error(ex, ex);
        }
      }
      if ( con != null ){
        try {
          con.close();
        }
        catch (Throwable ex) {
          logger.error(ex, ex);
        }
      }
      return rt;
    }
  }

  public static int updateNextFeeTime(String id){
    int rt = -1;
    Connection con = null;
    Statement stmt = null;
    try{
      con = ConnectionManager.getInstance().getConnection(SubServiceTypeDao.class);
      stmt = con.createStatement();
      String sql = "update submitrecord set LASTFEEDATE=sysdate,NEXTFEEDATE=sysdate+30 where id='"+id+"'";
      logger.debug(sql);
      rt = stmt.executeUpdate(sql);
    }catch( Throwable e ){
      logger.error(e, e);
    }finally{
      if ( stmt != null ){
        try {
          stmt.close();
        }
        catch (Throwable ex) {
          logger.error(ex, ex);
        }
      }
      if ( con != null ){
        try {
          con.close();
        }
        catch (Throwable ex) {
          logger.error(ex, ex);
        }
      }
      return rt;
    }
  }


  public static int updateStatusByPk(String id, int status){
    int rt = -1;
    Connection con = null;
    Statement stmt = null;
    try{
      con = ConnectionManager.getInstance().getConnection(SubServiceTypeDao.class);
      stmt = con.createStatement();
      String sql = "update submitrecord set STATUS="+
          status+" where id='"+id+"'";
      logger.debug(sql);
      rt = stmt.executeUpdate(sql);
    }catch( Throwable e ){
      logger.error(e, e);
    }finally{
      if ( stmt != null ){
        try {
          stmt.close();
        }
        catch (Throwable ex) {
          logger.error(ex, ex);
        }
      }
      if ( con != null ){
        try {
          con.close();
        }
        catch (Throwable ex) {
          logger.error(ex, ex);
        }
      }
      return rt;
    }
  }

  /**
   * 根据接收号码退订所有。
   * */
  public static int unsubmit(String destMobile){
    int rt = -1;
    Connection con = null;
    Statement stmt = null;
    try{
      con = ConnectionManager.getInstance().getConnection(SubServiceTypeDao.class);
      stmt = con.createStatement();
      String sql = "update submitrecord set STATUS="+
          SubmitStatus.UNSUBSCRIBED.getValue()+",unsubDate=sysdate where destMobile='"+
          destMobile + "' and STATUS="+SubmitStatus.SUBSCRIBED.getValue();
      logger.debug(sql);
      rt = stmt.executeUpdate(sql);
    }catch( Throwable e ){
      logger.error(e, e);
    }finally{
      if ( stmt != null ){
        try {
          stmt.close();
        }
        catch (Throwable ex) {
          logger.error(ex, ex);
        }
      }
      if ( con != null ){
        try {
          con.close();
        }
        catch (Throwable ex) {
          logger.error(ex, ex);
        }
      }
      return rt;
    }
  }
  public static int add(SubmitRecordObject subRecord){
    int rt = -1;
    Connection con = null;
    PreparedStatement pstmt = null;
    try{
      con = ConnectionManager.getInstance().getConnection(SubServiceTypeDao.class);
      pstmt = con.
          prepareStatement("insert into submitrecord(ID,FEEMOBILE,DESTMOBILE,SUBMITID,"+
          "ISPREPAY,SUBMITDATE,LASTFEEDATE,NEXTFEEDATE,STATUS,spId,spSn)values(?,?,?,?,?,?,?,?,?,?,?)");
      //subRecord.setId(IDGenerator.nextId(con));
      pstmt.setString(1, subRecord.getId());
      pstmt.setString(2, subRecord.getFeeMobile());
      pstmt.setString(3, subRecord.getDestMobile());
      pstmt.setString(4, subRecord.getSubmitId());
      pstmt.setInt(5, subRecord.getIsPrepay());
      pstmt.setTimestamp(6, subRecord.getSubmitDate());
      pstmt.setTimestamp(7, subRecord.getLastFeeDate());
      pstmt.setTimestamp(8, subRecord.getNextFeeDate());
      pstmt.setInt(9, subRecord.getStatus());
      pstmt.setString(10, subRecord.getSpId());
      pstmt.setString(11, subRecord.getSpSn());
      pstmt.execute();
      rt = 0;
    }catch( Throwable e ){
      logger.error(e, e);
    }finally{
      if ( pstmt != null ){
        try {
          pstmt.close();
        }
        catch (Throwable ex) {
          logger.error(ex, ex);
        }
      }
      if ( con != null ){
        try {
          con.close();
        }
        catch (Throwable ex) {
          logger.error(ex, ex);
        }
      }
      return rt;
    }

  }

//增加两个函数，分别按预付和后付
  public static Collection findTodayPreFee(){//预付
    String strwhere = "to_char(s.NEXTFEEDATE,'YYYYMMDD')=to_char(sysdate,'YYYYMMDD') and s.status="
    +  SubmitStatus.SUBSCRIBED.getValue() + " and s.IsPrepay='1'";
    return findAssSubCmd(strwhere);
  }
  public static Collection findTodayLastFee(){//后付
    String strwhere = "to_char(s.NEXTFEEDATE,'YYYYMMDD')=to_char(sysdate,'YYYYMMDD') and s.status="
    +  SubmitStatus.SUBSCRIBED.getValue() + " and s.IsPrepay='0'";
    return findAssSubCmdLast(strwhere);
  }

  static List findAssSubCmdLast(String where){
    List rt = new Vector();
    Connection con = null;
    Statement stmt = null;
    ResultSet rs = null;
    try{
      con = ConnectionManager.getInstance().getConnection(SubmitRecordDao.class);
      stmt = con.createStatement();
      String sql = "select s.*,c.NAME as svcName,c.price,c.SUBMITFEETYPE,c.FEEBUSINESSID,d.MOBILE as usermobile from submitrecord s,submitcommand c,webuser d "
          + " where " + where + " and s.SUBMITID=c.id and s.FEEMOBILE=d.USERNAME ";
      logger.debug(sql);
      rs = stmt.executeQuery(sql);
      while ( rs.next() ){
        SubmitRecordObject aSub = fromResultSet(rs);
        aSub.setServiceName(rs.getString("svcName"));
        aSub.setPrice(rs.getDouble("price"));
        aSub.setFeetype("SUBMITFEETYPE");
        aSub.setFeecode("FEEBUSINESSID");
        aSub.setDestMobile("DESTMOBILE");//接收手机号码
        aSub.setFeeMobile("usermobile");
        rt.add(aSub);
      }
    }catch( Throwable e ){
      logger.error(e, e);
    }finally{
      if ( rs != null ){
        try {
          rs.close();
        }
        catch (Throwable ex) {
          logger.error(ex, ex);
        }
      }
      if ( stmt != null ){
        try {
          stmt.close();
        }
        catch (Throwable ex) {
          logger.error(ex, ex);
        }
      }
      if ( con != null ){
        try {
          con.close();
        }
        catch (Throwable ex) {
          logger.error(ex, ex);
        }
      }
    }
    return rt;

  }

  static final Logger logger = Logger.getLogger(SubmitRecordDao.class);
}