package com.chinacreator.videoalliance.order.dao;

import com.chinacreator.common.dao.ExecutorDao;
import com.chinacreator.videoalliance.common.util.DataSource;
import com.chinacreator.videoalliance.order.bean.*;
import com.frameworkset.common.poolman.PreparedDBUtil;
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.HashMap;
import java.util.List;
import java.util.Map;

@Component
public class OrderDao extends ExecutorDao {
	public void order(OrderInfo orderInfo) throws SQLException {
		getExecutor().insertBean("net3g", "order", orderInfo);
	}
	
	public void countOrder(OrderInfo orderInfo) throws SQLException {
		getExecutor().insertBean("net3g", "countOrder", orderInfo);
	}

  public void orderTiyan(OrderInfo orderInfo) throws SQLException {
    getExecutor().insertBean("net3g", "ordertiyan", orderInfo);
  }

  public void cancelOrder(OrderInfo orderInfo) throws SQLException {
    getExecutor().updateBean("net3g", "cancelOrder", orderInfo);
  }

  public OrderInfo findByUser(String userid, String cpid, String spid) throws SQLException {
    return (OrderInfo)getExecutor().queryObjectWithDBName(OrderInfo.class, "net3g", "findByUserAndSpid", 
      new Object[] { userid, cpid, spid });
  }

  public List<OrderInfo> findByUser(String userid, String cpid) throws SQLException {
    return getExecutor().queryListWithDBName(OrderInfo.class, "net3g", "findByUserAndCpid", 
      new Object[] { userid, cpid });
  }

  public List<OrderInfo> findByUser(String userid) throws SQLException {
    return getExecutor().queryListWithDBName(OrderInfo.class, "net3g", "findByUser", new Object[] { userid });
  }

  public boolean hasOrder(String userid, String cpid, String spid) throws SQLException {
    String sql = "select count(id) from TD_ORDER_RELATIONS where userid=? and cpid=? and spid=? and status=0 and (endtime is null or endtime > sysdate)";
    return NumberUtils.toInt(SQLExecutor.queryFieldWithDBName("net3g", sql, new Object[] { userid, cpid, spid }), 
      0) > 0;
  }

  public boolean hasOrder(String userid,  String spid) throws SQLException {
        String sql = "select count(id) from TD_ORDER_RELATIONS where userid=?  and spid=? and status=0 and (endtime is null or endtime > sysdate)";
        return NumberUtils.toInt(SQLExecutor.queryFieldWithDBName("net3g", sql, new Object[] { userid, spid }),
                0) > 0;
	}

  public boolean hasCancelOrder(String userid, String cpid, String spid) throws SQLException {
    String sql = "select count(id) from TD_ORDER_RELATIONS where userid=? and cpid=? and spid=? and status=1 and (endtime is null or endtime > sysdate)";
    return NumberUtils.toInt(SQLExecutor.queryFieldWithDBName("net3g", sql, new Object[] { userid, cpid, spid }), 
      0) > 0;
  }

  public boolean hasValid(String userid, String cpid, String spid) throws SQLException {
    String sql = "select count(id) from TD_ORDER_RELATIONS where userid=? and cpid=? and spid=? and (endtime is null or endtime > sysdate)";
    return NumberUtils.toInt(SQLExecutor.queryFieldWithDBName("net3g", sql, new Object[] { userid, cpid, spid }), 
      0) > 0;
  }

  public boolean hasTiyanValid(String userid, String cpid, String spid) throws SQLException {
    String sql = "select count(id) from TD_ORDER_RELATIONS where userid=? and cpid=? and spid=? and (endtime is null or endtime > sysdate) and orderstatus in (7,9) and spid not in (select spid from tb_sp_info where errorhandle = '0')";
    return NumberUtils.toInt(SQLExecutor.queryFieldWithDBName("net3g", sql, new Object[] { userid, cpid, spid }), 
      0) > 0;
  }

  public String getValue(String key) throws SQLException {
    return getExecutor().queryFieldWithDBName("net3g", "findpassword", new Object[] { key });
  }
  
  public Boolean isOpen(String spid,String cpid,String province) throws Exception {
		String sql = "select count(1) from tb_changshi_province_control where spid= ? and cpid = ? and province = ? and status='2'";
		return Integer.parseInt(SQLExecutor.queryFieldWithDBName(DataSource.NET3G, sql,spid,cpid,province))>=1 ? true:false;
	}

    public void insertToken(String token) throws SQLException {
	    String sql="insert  into  td_token_date(token) VALUES(?)";
	    SQLExecutor.insertWithDBName("net3g",sql,token);
    }

    public Boolean isToken(String spid,String cpid,String channel) throws Exception {
        String sql = "  select  count(1) from tb_istoken_conf where cpid in (?,'*') and spid in (?,'*')  and channel in (?,'*')  and status='0'";
        return Integer.parseInt(SQLExecutor.queryFieldWithDBName(DataSource.NET3G, sql, cpid, spid,channel)) > 0;
    }

    public Map channelProvince(OrderInfo orderInfo) throws Exception {
        String sql = "SELECT  *  FROM  TB_PROVINCE_CHANNEL_CONF WHERE STATUS='0' AND PROVINCE = REPLACE(REPLACE(?, '省', ''), '市', '')  and  channel=? and spid=?";
        return SQLExecutor.queryObjectWithDBName(HashMap.class,DataSource.NET3G, sql,new Object[] {orderInfo.getProvince(),orderInfo.getChannel(),orderInfo.getSpid()});
    }

    public boolean isMangtvCancel(String userid, String spid, String startTime, String endTime) throws SQLException {
        String sql = " select count(1) from TD_ORDER_RELATIONS where  userid=?  and spid=? and  ordertime>=to_date(?,'yyyyMMddHH24miss')  and    ORDERTIME<=to_date(?,'yyyyMMddHH24miss') and  sysdate<to_date(TO_CHAR(ADD_MONTHS(ordertime,6),'yyyyMM'),'yyyyMM') ";
        return Integer.parseInt(SQLExecutor.queryFieldWithDBName(DataSource.NET3G, sql, userid, spid,startTime,endTime)) > 0;
    }
    public boolean isMangtvOrder(String userid, String spid) throws SQLException {
        String sql = " select count(1) from TD_ORDER_RELATIONS where  userid=?  and spid=? ";
        return Integer.parseInt(SQLExecutor.queryFieldWithDBName(DataSource.NET3G, sql, userid, spid)) > 0;
    }

    public boolean queryCancelConf(String spid) throws SQLException {
	    String sql="select count(1) from TB_PRODUCT_CANCEL_CONF where  spid= ? and status='0' ";
        return Integer.parseInt(SQLExecutor.queryFieldWithDBName(DataSource.NET3G, sql,  spid))>0;
    }

    public Integer getCancelMonth(String spid) throws SQLException {
	    String sql="select month from TB_PRODUCT_CANCEL_CONF where  spid=? and status='0'";
        return Integer.parseInt(SQLExecutor.queryFieldWithDBName(DataSource.NET3G, sql,  spid));
    }

    public boolean isCancel(String userid, String spid, Integer num) throws SQLException {
        String sql = " select count(1) from TD_ORDER_RELATIONS where  userid= ?  and spid= ? and  sysdate>to_date(TO_CHAR(ADD_MONTHS(ordertime,?),'yyyyMM'),'yyyyMM')";
        return Integer.parseInt(SQLExecutor.queryFieldWithDBName(DataSource.NET3G, sql, userid, spid,num)) > 0;
    }

    public boolean isSendsms(String spid) throws SQLException {
	    String sql="select count(1) from tb_sp_info t where t.spid= ?  and t.DIRECTYPE='1'   and t.spid not in ('1027','1028','1022','1023') and not EXISTS (select * from TB_PRODUCT_CANCEL_CONF b where  b.spid=?  and b.status='0' )";
        return Integer.parseInt(SQLExecutor.queryFieldWithDBName(DataSource.NET3G, sql, spid,spid)) > 0;

    }

    public void addCoupon(OrderInfo orderInfo) throws SQLException {
        getExecutor().insertBean("net3g", "addcoupon", orderInfo);
    }

    public String getOrderid(String userid,String spid) throws SQLException {
        return getExecutor().queryFieldWithDBName("net3g", "getorderid", new Object[] { userid,spid });
    }

    public Map getActiveTime(String spid, String channel) throws Exception {
        String sql = "SELECT to_char(starttime,'yyyy-mm-dd hh24:mi:ss') starttime,to_char(endtime,'yyyy-mm-dd hh24:mi:ss') endtime from tb_acproduct_config where spid = ? and channel = ?";
        return SQLExecutor.queryObjectWithDBName(HashMap.class, DataSource.NET3G, sql,new Object[] { spid, channel});
    }

    public Boolean hasCoupon(String spid,String userid) throws Exception {
        String sql = " select count(1) from td_coupon_rec where userid =? and spid = ? and couponmonth = to_char(sysdate,'yyyymm')";
        return Integer.parseInt(SQLExecutor.queryFieldWithDBName(DataSource.NET3G, sql, userid, spid)) > 0;
    }

    public boolean hasSendCouponSms(String userid, String spid) throws SQLException {
        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')";
        int count = Integer.parseInt(SQLExecutor.queryFieldWithDBName(DataSource.NET3G, sql, spid,userid,spid,spid));
        if(count==1)
            return true;
        return false;
    }

    public List<String> querySendSmsProduct() throws SQLException {
        String sql = "select spid from tb_sendsms_product where status = '0'";
        return SQLExecutor.queryListWithDBName(String.class, DataSource.NET3G, sql);
    }

    //获得券码活动配置
    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;
    }

    public String getHmdInfo(String callerhead){
        PreparedDBUtil pdb = new PreparedDBUtil();
        String sql="select PROVINCE from TB_CALLER_AREACODE where status = '0' and callerhead = ?";
        try {
            pdb.preparedSelect(DataSource.NET3G, sql);
            pdb.setString(1, callerhead);
            return (String) pdb.executePreparedForObject(String.class);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 获取字典表里可以退订的合约包spid
     * @param name
     * @return
     * @throws SQLException
     */
    public String getDictionryValue(String name) throws SQLException {
        return getExecutor().queryFieldWithDBName("net3g", "getDictionryValue", new Object[]{name});
    }

    /**
     * 获取合约包绑定的违约包spid
     * @param spid
     * @return
     * @throws SQLException
     */
    public String getWYJSpid(String spid) throws SQLException {
        return getExecutor().queryFieldWithDBName("net3g", "getWYJSpid", new Object[]{spid});
    }

    public void addWYJLog(WyjLogBean bean) throws SQLException {
        getExecutor().insertBean("net3g", "addWYJLog", bean);
    }

    public String getSpname(String spid) throws SQLException {
        return this.getExecutor().queryFieldWithDBName("net3g", "getSpname", new Object[]{spid});
    }

    public void inserArrearageData(ReflowBean reflowBean) throws SQLException {
        getExecutor().insertBean("net3g", "inserArrearageData", reflowBean);
    }

    public List<String> queryAopProduct() throws SQLException {
        String sql = "select t1.spid from (select sp_id spid from tb_sp_aop_config group by sp_id) t1 " +
                ",(select * from tb_sp_info where vacproductid is not null and paytype = '4') t2 " +
                "where t1.spid = t2.spid ";
        return SQLExecutor.queryListWithDBName(String.class, DataSource.NET3G, sql);
    }

    public boolean getRelation(String userid,String spid) throws SQLException {
        String sql = "select count(id) from TD_ORDER_RELATIONS where userid=?  and spid=? ";
        return NumberUtils.toInt(SQLExecutor.queryFieldWithDBName("net3g", sql, new Object[] { userid,  spid }),
                0) > 0;
    }

}