submitrecorddao.java

采用JAVA开发

package com.gctech.sms.dao;

import org.apache.log4j.Logger;
import com.gctech.sms.vo.SubmitRecordObject;
import java.sql.Connection;
import java.sql.PreparedStatement;
import com.gctech.sms.util.ConnectionManager;
import com.gctech.sms.core.SubmitStatus;
import java.sql.ResultSet;
import java.util.Collection;
import java.sql.Statement;
import java.util.Vector;
import java.sql.SQLException;
import java.util.List;

/**
 * <p>Title: </p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2004</p>
 * <p>Company: gctech</p>
 * @author 王红宝
 * @version $Id: SubmitRecordDao.java,v 1.5 2004/06/22 08:13:34 wengjl Exp $
 */

public class SubmitRecordDao {
  public SubmitRecordDao() {
  }
  public static SubmitRecordObject fromResultSet(ResultSet rs) throws
      SQLException {
    SubmitRecordObject rt = new SubmitRecordObject();
    rt.setDestMobile(rs.getString("DESTMOBILE"));
    rt.setFeeMobile(rs.getString("FEEMOBILE"));
    rt.setId(rs.getString("ID"));
    rt.setIsPrepay(rs.getInt("ISPREPAY"));
    rt.setLastFeeDate(rs.getTimestamp("LASTFEEDATE"));
    rt.setNextFeeDate(rs.getTimestamp("NEXTFEEDATE"));
    rt.setStatus(rs.getInt("STATUS"));
    rt.setSubmitDate(rs.getTimestamp("SUBMITDATE"));
    rt.setSubmitId(rs.getString("SUBMITID"));
    return rt;
  }

  /**
   * 通过计费号码查询订阅列表，包括订阅服务内容，服务类别，发送时间等。
   * @param mobile 计费号码。
   * @return SubmitRecordObject 集合。
   */
  public static Collection findByFeeMobile(String feeMobile){
    Collection 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.SENDFREQ,st.name as svcTypeName from submitrecord s,submitcommand c,"+
          " subservicetype st where FEEMOBILE='"+feeMobile+
          "' and s.STATUS="+SubmitStatus.SUBSCRIBED.getValue()+
          " and s.SUBMITID=c.id and st.id=c.SERVICETYPEID";
      logger.debug(sql);
      rs = stmt.executeQuery(sql);
      while ( rs.next() ){
        SubmitRecordObject aSub = fromResultSet(rs);
        aSub.setServiceName(rs.getString("svcName"));
        aSub.setServiceType(rs.getString("svcTypeName"));
        //增加发送频次 2004-5-25 wjl
        aSub.setSendFreque(rs.getString("SENDFREQ"));
        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;
  }
  /**
   * 找到今天要扣费的记录。
   * */
  public static Collection findTodayFee(){
    return findAssSubCmd("to_char(s.NEXTFEEDATE,'YYYYMMDD')=to_char(sysdate,'YYYYMMDD') and s.status="
    + SubmitStatus.SUBSCRIBED.getValue());
  }
  /**
   * 管理订阅表查询。
   * */
  static List findAssSubCmd(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 from submitrecord s,submitcommand c"
          + " where " + where + " and s.SUBMITID=c.id";
      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");
        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;

  }

  /**
   * 通过接收号码查询订阅列表，包括订阅内容描述。
   * @param mobile 接收号码。
   * @return SubmitRecordObject 集合。
   */
  public static List findByDestMobile(String mobile){
    return findAssSubCmd("DESTMOBILE='"+mobile+
        "' and s.STATUS="+SubmitStatus.SUBSCRIBED.getValue());
    /*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 from submitrecord s,submitcommand c"+
          " where ;
      logger.debug(sql);
      rs = stmt.executeQuery(sql);
      while ( rs.next() ){
        SubmitRecordObject aSub = fromResultSet(rs);
        aSub.setServiceName(rs.getString("svcName"));
        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;*/
  }


  /**
   * 查询某个终端是否已经订阅一个服务。
   * */
  public static boolean isExist(String destMobile, String serviceId){
    boolean rt = false;
    Connection con = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try{
      con = ConnectionManager.getInstance().getConnection(SubmitRecordDao.class);
      String sql = "select count(*) from submitrecord where destmobile=?"+
          " and submitid=? and status="+
          SubmitStatus.SUBSCRIBED.getValue();
      logger.debug(sql);
      stmt = con.prepareStatement(sql);
      stmt.setString(1, destMobile);
      stmt.setString(2, serviceId);
      rs = stmt.executeQuery();
      rs.next();
      rt = (rs.getInt(1) > 0);
    }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;
  }

  /**通过订阅ID和计费号码查询服务。
   *
   * */
  public static SubmitRecordObject findByPkAndFeeMobile(String pk, String feeMobile){
    SubmitRecordObject rt = null;
    Connection con = null;
    ResultSet rs = null;
    PreparedStatement stmt = null;
    try{
      con = ConnectionManager.getInstance().getConnection(SubmitRecordDao.class);
      String sql = "select s.*,c.NAME as svcName from submitrecord s,submitcommand c"+
          " where s.id=? and feeMobile=? and s.STATUS="+SubmitStatus.SUBSCRIBED.getValue()+
          " and s.SUBMITID=c.id";
      logger.debug(sql);
      stmt = con.prepareStatement(sql);
      stmt.setString(1, pk);
      stmt.setString(2, feeMobile);
      rs = stmt.executeQuery();
      if ( rs.next() ){
        rt = fromResultSet(rs);
        rt.setServiceName(rs.getString("svcName"));
      }