videoalliance/.svn/pristine/86/86647423279379c834dc35346f5a4e5ceab0617c.svn-base

package com.chinacreator.videoalliance.smc.dao;

import com.chinacreator.videoalliance.common.util.DataSource;
import com.chinacreator.videoalliance.smc.bean.TlVerifiCationCodeBean;
import com.frameworkset.common.poolman.PreparedDBUtil;

import java.sql.SQLException;
/**
 * 短信验证码数据层类
 * @author lizhiyong
 *
 */
public class SendSmsCodeDao {
	/**
	 * 检查验证码是否超时(大于60秒)
	 * @param cpid
	 * @param userid
	 * @return 时间差
	 * @throws SQLException 
	 */
	public String checkValidatTime(String cpid, String userid) throws SQLException{
		PreparedDBUtil pdb = new PreparedDBUtil();
		String sql="select (SYSDATE - TO_DATE (MAKETIME, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 3600 from TL_VERIFICATION_CODE  where USERID=? AND CPID=? and  (SYSDATE - TO_DATE (MAKETIME, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 3600<60";
		pdb.preparedSelect(DataSource.NET3G, sql);
		pdb.setString(1, userid);
		pdb.setString(2, cpid);
		pdb.executePrepared();
		if(pdb.size() > 0) {
			System.out.println(pdb);
			System.out.println(pdb.getInt(0, 0) + "");
			return pdb.getInt(0, 0) + "";
		}
		return "61";
	}

	public static void main(String[] args) throws SQLException {
		PreparedDBUtil pdb = new PreparedDBUtil();
		SendSmsCodeDao sendSmsCodeDao = new SendSmsCodeDao();
		String pptv = sendSmsCodeDao.checkValidatTime("pptv", "17673136322");
	}
	public int checkTransfer(String userid) throws SQLException{
		if(userid != null){
			PreparedDBUtil pdb = new PreparedDBUtil();
			String sql="select count(*) count from wosdk.tb_transfer_number where userid='"+userid.trim()+"' and status='0'";
			pdb.preparedSelect(DataSource.NET3G, sql);
			pdb.executePrepared();
			if(pdb.size() > 0) {
				return pdb.getInt(0, 0);
			}
		}
		return 0;
	}
	
	public int checkArea(String userid) throws SQLException{
		if(userid != null && userid.length()>=7){
			PreparedDBUtil pdb = new PreparedDBUtil();
			String sql="select count(*) count from TB_CALLER_AREACODE t where operators='联通' and status='0' and callerhead='"+userid.substring(0,7)+"'";
			pdb.preparedSelect(DataSource.NET3G, sql);
			pdb.executePrepared();
			if(pdb.size() > 0) {
				return pdb.getInt(0, 0);
			}
		}
		return 0;
	}
	
	/**
	 * 插入用户成功获取验证码的信息
	 * @param tvcc 确认验证码实体类
	 * @throws SQLException
	 */
	public void insertTlVerifiCationCode(TlVerifiCationCodeBean tvcc) throws SQLException{
		PreparedDBUtil pdp=new PreparedDBUtil();
		String sql="insert into TL_VERIFICATION_CODE(ROWNO,CPID,USERID,VERCODE,MAKETIME,SENDSTATUS,REMARK,CHANNEL)values"
				+ "(to_char(sysdate,'yyyyMMddhh24miss')||SEQ_COMMON6.nextval,?,?,?,?,?,?,?)";
		pdp.preparedInsert(DataSource.NET3G, sql);
		pdp.setString(1, tvcc.getCpid());
		pdp.setString(2, tvcc.getUserid());
		pdp.setString(3, tvcc.getVercode());
		pdp.setString(4, tvcc.getMakeTime());
		pdp.setString(5, tvcc.getSendStatus());
		pdp.setString(6, tvcc.getRemark());
		pdp.setString(7, tvcc.getChannel());
		pdp.executePrepared();

	}
}