actitve-process/.svn/pristine/81/81b96e90aaa893fadcf06265f08697a84723b15d.svn-base

package com.chinacreator.process.dao;

import com.chinacreator.process.bean.OrderBean;
import com.chinacreator.process.bean.VipBlackBean;
import com.chinacreator.process.util.DataSource;
import com.frameworkset.common.poolman.SQLExecutor;
import org.apache.commons.lang.math.NumberUtils;
import org.springframework.stereotype.Component;

import java.sql.SQLException;
import java.util.List;

@Component
public class BlackDao {

    public String getActiveType(VipBlackBean bean) throws SQLException {
        String sql = "select ACTIVETYPE from TB_ACTIVITY_CONFIG where cpid=#[cpid] and spid= #[spid] and province in (#[province],'0') and ROWNUM=1";
        return SQLExecutor.queryFieldBeanWithDBName(DataSource.NET3G, sql,bean);
    }

    /**
     * 统计十小时内各省份会员三户
     */
    public List<VipBlackBean> getAllOrderCount(String cpid,String spid) throws SQLException {

        String sql = "select DISTINCT province,count(*) count,cpid,spid from net3g.TD_ORDER_RELATIONS where   cpid=? and spid=? and ORDERTIME BETWEEN (sysdate -interval '10' hour ) and sysdate  group by PROVINCE,cpid,spid having count(*) >=50";
        return SQLExecutor.queryList( VipBlackBean.class, sql,cpid,spid);
    }

    /**
     * 根据产品统计十小时内省份套餐订购记录
     */
    public VipBlackBean getOrderCountBy(String cpid,String spid,String province) throws SQLException {
        String sql = "SELECT A.PROVINCE province,B.PACAKGE limit,count(1) count,A.spid,A.cpid FROM NET3G.TD_ORDER_RELATIONS A LEFT JOIN NET3G.TB_ECB_LOG_TEMP B " +
                "ON A.USERID=B.USERID AND A.SPID=B.SPID AND B.RESULTCODE='0' AND TO_CHAR(A.ORDERTIME,'YYYY-MM')=TO_CHAR(B.INSERTTIME,'YYYY-MM') " +
                "WHERE A.CPID=? and A.SPID=? and PROVINCE=? and FILEPATH is not null  AND A.ORDERTIME BETWEEN (sysdate -interval '10' hour ) and sysdate and PACAKGE is not null " +
                "group by A.PROVINCE, B.PACAKGE,A.spid,A.cpid  order by count desc";
        List<VipBlackBean> beans=SQLExecutor.queryList(VipBlackBean.class, sql,cpid,spid,province);
        if(beans.size()>0){
            return beans.get(0);
        }else {
            return null;
        }
    }

    /**
     * 是否在订购黑名单
     * @param cpid
     * @param spid
     * @param province
     * @return
     * @throws SQLException
     */
    public boolean isOrderBlackUser(String cpid, String spid,String province,String limit) throws SQLException {
        String sql = "select count(*) from TB_ECB_CONFIG where status=0 and  cpid=? and spid=? and province=? and limit like concat(concat('%',?),'%')";
        return NumberUtils.toInt(SQLExecutor.queryFieldWithDBName(DataSource.NET3G, sql, cpid, spid,province,limit), 0) > 0;
    }


    /**
     * 加入订购黑名单
     * @param bean
     * @throws SQLException
     */
    public void insertOrderBlack(VipBlackBean bean) throws SQLException{
        String sql = "merge into TB_ECB_CONFIG a using (select #[cpid] cpid, #[spid] spid, #[province] province from dual) b " +
                "on(a.cpid=b.cpid and a.spid=b.spid and a.province=b.province)   " +
                "when matched then update set LIMIT=concat(concat(concat(LIMIT,'||*'),#[limit]),'*') " +
                "when not matched then insert(CPID, SPID, INSERTTIME, STATUS, PROVINCE, LIMIT, AREA) " +
                " VALUES (#[cpid], #[spid], sysdate, '0', #[province], concat(concat('*',#[limit]),'*'), '0') ";
        SQLExecutor.insertBean(DataSource.NET3G, sql, bean);
    }

    /**
     * 是否在领取黑名单
     * @param cpid
     * @param spid
     * @param province
     * @return
     * @throws SQLException
     */
    public boolean isVipBlackUser(String cpid, String spid,String province,String limit) throws SQLException {
        String sql = "select count(*) from TB_JOINECB_CONFIG where status=0 and  cpid=? and spid=? and province=? and limit like concat(concat('%',?),'%')";
        return NumberUtils.toInt(SQLExecutor.queryFieldWithDBName(DataSource.NET3G, sql, cpid, spid,province,limit), 0) > 0;
    }
    /**
     * 加入领取黑名单
     * @param bean
     * @throws SQLException
     */
    public void insertVipBlack(VipBlackBean bean) throws SQLException{
        String sql = "merge into TB_JOINECB_CONFIG a using (select #[cpid] cpid, #[spid] spid, #[province] province from dual) b " +
                "on(a.cpid=b.cpid and a.spid=b.spid and a.province=b.province)   " +
                "when matched then update set LIMIT=concat(concat(concat(LIMIT,'||*'),#[limit]),'*')" +
                "when not matched then insert(CPID, SPID, INSERTTIME, STATUS, PROVINCE, LIMIT, AREA,ACTIVETYPE,BRANDLIMIT) " +
                " VALUES (#[cpid], #[spid], sysdate, '0', #[province], concat(concat('*',#[limit]),'*'), '0',#[activetype],NULL) ";
        SQLExecutor.insertBean(DataSource.NET3G, sql, bean);
    }


    /**
     * 是否在白名单
     * @param cpid
     * @param spid
     * @param province
     * @return
     * @throws SQLException
     */
    public boolean isOrderWhiteUser(String cpid, String spid,String province,String limit) throws SQLException {
        String sql = "select count(*) from TB_ECBWHITE_CONFIG where status=0 and  cpid=? and spid=? and province in (?,'0') and limit like concat(concat('%',?),'%')";
        return NumberUtils.toInt(SQLExecutor.queryFieldWithDBName(DataSource.NET3G, sql, cpid, spid,province,limit), 0) > 0;
    }




}