actitve-process/.svn/pristine/11/110102b1d6ac5f0e3602912f7238b26122f4b24e.svn-base

package com.chinacreator.process.dao;

import com.chinacreator.process.bean.ChannelOrderBean;
import com.chinacreator.process.util.DataSource;
import com.frameworkset.common.poolman.PreparedDBUtil;
import com.frameworkset.common.poolman.SQLExecutor;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Component;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

@Component
public class ChannelOrderDao
{
    private static Logger logger = Logger.getLogger(ChannelOrderDao.class);

    @SuppressWarnings("unchecked")
    public List<ChannelOrderBean> queryNeedDo()
    {
        //String sql = "select id,userid,to_char(ordertime,'yyyymmddhh24miss') ordertime,a.type,updatetype,to_char(canceltime,'yyyymmddhh24miss') canceltime,productid from td_channel_ftp_order a,TB_CHANNELORDER_CONFIG b where  a.status = '1' and a.inserttime between sysdate-1 and  sysdate  and a.productid = b.vacproductid order by id";
        String sql="select id,\n" +
                "       userid,\n" +
                "       to_char(ordertime, 'yyyymmddhh24miss') ordertime,\n" +
                "       a.type,\n" +
                "       updatetype,\n" +
                "       to_char(canceltime, 'yyyymmddhh24miss') canceltime,\n" +
                "       productid\n" +
                "  from td_channel_ftp_order a, TB_CHANNELORDER_CONFIG b\n" +
                " where a.status = '1'\n" +
                "   and a.inserttime between sysdate - 1 and sysdate\n" +
                "   and a.productid = b.vacproductid\n" +
                "   and a.productid != '99106371'\n" +
                "union all\n" +
                "select id,\n" +
                "       userid,\n" +
                "       to_char(ordertime, 'yyyymmddhh24miss') ordertime,\n" +
                "       a.type,\n" +
                "       updatetype,\n" +
                "       to_char(canceltime, 'yyyymmddhh24miss') canceltime,\n" +
                "       productid\n" +
                "  from td_channel_ftp_order a, TB_CHANNELORDER_CONFIG b\n" +
                " where a.productid  = '99106371'\n" +
                "   and  a.status = '1'\n" +
                "   and a.productid = b.vacproductid\n" +
                "   and a.inserttime >=to_date('2020/11/25 14:24:26', 'yyyy/MM/dd HH24:mi:ss')\n" +
                "   and updatetype = '2'\n" +
                " order by id";
        PreparedDBUtil pdb = new PreparedDBUtil();
        try {
            return pdb.executeSelectForList("sdk", sql, 0L, 500, ChannelOrderBean.class);
        } catch (Exception e) {
            logger.error("查询td_channel_ftp_order失败", e);
        }
        return null;
    }

    @SuppressWarnings("unchecked")
    public List<ChannelOrderBean> queryNeedDotest()
    {
        String sql = "select id,userid,to_char(ordertime,'yyyymmddhh24miss') ordertime,type,updatetype,to_char(canceltime,'yyyymmddhh24miss') canceltime,istimeout from  td_channel_ftp_order where  userid ='13230445009'";
        PreparedDBUtil pdb = new PreparedDBUtil();
        try {
            return pdb.executeSelectForList("sdk", sql, 0L, 500, ChannelOrderBean.class);
        } catch (SQLException e) {
            logger.error("查询td_channel_ftp_order失败", e);
        }
        return null;
    }

    @SuppressWarnings("unchecked")
    public List<ChannelOrderBean> queryNeedDoForTimeout()
    {
        String sql = "select id,userid,to_char(ordertime,'yyyymmddhh24miss') ordertime,type from  td_channel_ftp_order where  status = 1 and istimeout = 1 order by id";
        PreparedDBUtil pdb = new PreparedDBUtil();
        try {
            return pdb.executeSelectForList("sdk", sql, 0L, 500, ChannelOrderBean.class);
        } catch (SQLException e) {
            logger.error("查询td_channel_ftp_order失败", e);
        }
        return null;
    }

    public void updatestatus(String id, String result) {
        String sql = "update TD_CHANNEL_FTP_ORDER set status = 0,synctime = sysdate, result = ? where id = ?";
        try {
            SQLExecutor.updateWithDBName("sdk", sql, new Object[] { result, id });
        } catch (SQLException e) {
            logger.error("更改td_channel_ftp_order失败", e);
        }
    }
    
    /**
     * 更新办理结果
     * @param id
     * @param result 结果编码
     * @param resultinfo 结果信息
     */
    public void updatestatus(String id, String result, String resultinfo) {
        String sql = "update TD_CHANNEL_FTP_ORDER set status = 0,synctime = sysdate, result = ?, RESULTINFO = ? where id = ?";
        try {
            SQLExecutor.updateWithDBName("sdk", sql, new Object[] { result, resultinfo, id});
        } catch (SQLException e) {
            logger.error("更改td_channel_ftp_order失败", e);
        }
    }
    
    public int isOrder(String cpid, String spid, String userid) {
        int isOrder = 1;
        PreparedDBUtil pdb = new PreparedDBUtil();
        String sql = "select count(*) from TD_ORDER_RELATIONS where cpid=? and spid=? and userid=? and status='0'";
        try {
            pdb.preparedSelect(DataSource.NET3G, sql);
            pdb.setString(1, cpid);
            pdb.setString(2, spid);
            pdb.setString(3, userid);
            pdb.executePrepared();
            if (pdb.getInt(0, 0) > 0)
                isOrder = 0;
        } catch (Exception e) {
            logger.error("查询用户是否已订购失败", e);
        }
        return isOrder;
    }



    /**
     * 特殊复合产品结束时间
     * @return
     */

    public Date getLastYearDay(){
        PreparedDBUtil pdb = new PreparedDBUtil();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String sql = "select TO_DATE(TO_CHAR(trunc(add_months(sysdate,12),'MM') - 1/24/60/60,'yyyyMMddhh24miss'), 'yyyyMMddhh24miss') from dual";

        try {
            String endtime = (String)pdb.executeSelectForList(DataSource.NET3G, sql, 0L, 500, String.class).get(0);
            Date d = sdf.parse(endtime);
            return d;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 特殊复合产品结束时间
     * @return
     */

    public Date getLastYearDayFirst(Date time){
        PreparedDBUtil pdb = new PreparedDBUtil();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String sql = "select TO_DATE(TO_CHAR(trunc(add_months(?,12),'MM') - 1/24/60/60,'yyyyMMddhh24miss'), 'yyyyMMddhh24miss') from dual";

        try {
            pdb.preparedSelect(DataSource.NET3G, sql);
            pdb.setDate(1,time);
            String endtime = pdb.executePreparedForList(String.class).get(0);
            Date d = sdf.parse(endtime);
            return d;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 特殊复合产品结束时间
     * @return
     */

    public Date getLastYearDay(Date time){
        PreparedDBUtil pdb = new PreparedDBUtil();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String sql = "select TO_DATE(TO_CHAR(trunc(add_months(?,13),'MM') - 1/24/60/60,'yyyyMMddhh24miss'), 'yyyyMMddhh24miss') from dual";

        try {
            pdb.preparedSelect(DataSource.NET3G, sql);
            pdb.setDate(1,time);
            String endtime = pdb.executePreparedForList(String.class).get(0);
            Date d = sdf.parse(endtime);
            return d;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public Date getFhcpEndtime(String userid,String cpid,String spid){
        PreparedDBUtil pdb = new PreparedDBUtil();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String sql = "select endtime from TD_ORDER_RELATIONS where userid = ? and cpid = ? and spid = ?";
        try {
            String endtime = SQLExecutor.queryFieldWithDBName(DataSource.NET3G, sql, new Object[] { userid, cpid, spid });
            Date d = sdf.parse(endtime);
            return d;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    //判断是否还需要发短信（查找活动时间范围内是否有订购的记录）
    public  boolean hasSendCouponSms(String userid,String spid){
        PreparedDBUtil pdb = new PreparedDBUtil();
        String sql = "select count(1) from tl_order_log a " +
                " where spid = ? and userid =? and status = '0' and errorcode = '0' " +
                " and inserttime >= (select starttime from tb_acproduct_config where spid = ? " +
                " and coupontype = '0' and isunique = '2' and status = '0')" +
                " and inserttime <= (select endtime from tb_acproduct_config  where spid = ?  " +
                " and coupontype = '0' and  isunique = '2' and status = '0')";
        try {
            pdb.preparedSelect(DataSource.NET3G, sql);
            pdb.setString(1,spid);
            pdb.setString(2,userid);
            pdb.setString(3,spid);
            pdb.setString(4,spid);
            pdb.executePrepared();
            if (pdb.getInt(0, 0) ==1)
                return true;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    //采集需要订购后再下发其他短信的产品
    public  List<String> querySendSmsProduct(){
        PreparedDBUtil pdb = new PreparedDBUtil();
        String sql = "select spid from tb_sendsms_product where status = '0' ";
        try {
            pdb.preparedSelect(DataSource.NET3G, sql);
            return pdb.executePreparedForList(String.class);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    //获得券码活动配置
    public  HashMap getCouponConfig(String spid ){
        PreparedDBUtil pdb = new PreparedDBUtil();
        String sql = "SELECT ID, CPID,SPID,TO_CHAR(STARTTIME,'YYYYMMDDHH24MISS') STARTTIME, " +
                "TO_CHAR(ENDTIME,'YYYYMMDDHH24MISS') ENDTIME,ACTIVITYCODE,COUPONTYPE,REMARK,CHANNEL,EXTEND1 " +
                " from TB_ACPRODUCT_CONFIG where sysdate between STARTTIME and  ENDTIME and status = '0' and activitycode = 'telecomActivity' and spid = ? ";
        try {
            pdb.preparedSelect(DataSource.NET3G, sql);
            pdb.setString(1,spid);
//            return pdb.executePreparedForList(HashMap.class)==null?null:pdb.executePreparedForList(HashMap.class).get(0);
            List<HashMap> list = pdb.executePreparedForList(HashMap.class);
            if (list != null && list.size() > 0) {
                HashMap map = list.get(0);
                return map;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}