package com.chinacreator.process.dao; import java.sql.SQLException; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import org.apache.log4j.Logger; import org.springframework.cache.annotation.Cacheable; import org.springframework.stereotype.Component; import com.chinacreator.common.support.cache.annotation.CacheName; import com.chinacreator.process.bean.ContinueBean; import com.chinacreator.process.bean.KuaishouPushBean; import com.chinacreator.process.bean.OrderBean; import com.chinacreator.process.util.DataSource; import com.frameworkset.common.poolman.PreparedDBUtil; import com.frameworkset.common.poolman.SQLExecutor; @Component @CacheName("OrderSendVip") public class OrderSendVipDao{ /** * 更新处理结果 * @param id * @param resultcode * @param resultinfo * @return * @throws SQLException */ public boolean updatePush(String id, String resultcode, String resultinfo)throws SQLException { String sql = "UPDATE TD_ORDER_SENDVIP_REC SET RESULTCODE = ? , RESULTINFO = ? WHERE ID = ? "; Object rows = SQLExecutor.updateWithDBName(DataSource.NET3G, sql, resultcode,resultinfo, id); if(Integer.parseInt(rows.toString()) > 0){ return true; }else{ return false; } } /** * 查询是否有配置信息 * @param cpid * @param spid * @param orderchannel * @return * @throws SQLException */ public boolean qryOrderSendvipConf(String cpid, String spid,String orderchannel)throws SQLException { String sql = " SELECT COUNT(1) CNT FROM TB_ORDER_SENDVIP_CONF WHERE CPID = ? AND SPID = ? AND CHANNEL = ? AND STATUS = '0' "; int cnt = SQLExecutor.queryObjectWithDBName(Integer.class, DataSource.NET3G,sql, cpid, spid, orderchannel); if(cnt > 0){ return true; }else{ return false; } } /** * 查询所有自动领取会员配置 * @param * @return */ @Cacheable(value="qryOrderSendvipAllConf", key="#cpid + '_' + #spid") public List qryOrderSendvipAllConf(String cpid, String spid)throws SQLException { String sql = " SELECT ID,CPID,SPID,CHANNEL,VIPID,VIPTYPE,VIPPWD,TO_CHAR(STARTTIME,'YYYYMMDDHH24MISS') STARTTIME, TO_CHAR(ENDTIME,'YYYYMMDDHH24MISS') ENDTIME, ORDERTYPE, PRODUCTNAME, PRODUCTID, SENDTYPE, SUBCHANNEL FROM TB_ORDER_SENDVIP_CONF WHERE CPID = ? AND SPID = ? AND STATUS = '0' "; return SQLExecutor.queryListWithDBName(HashMap.class, DataSource.NET3G, sql, cpid, spid); } /** * 查询自动领取会员配置 * @param * @return */ public List qryOrderSendvipConf(String cpid, String spid)throws SQLException { String sql = " SELECT CPID,SPID,CHANNEL,TO_CHAR(STARTTIME,'YYYYMMDDHH24MISS') STARTTIME, TO_CHAR(ENDTIME,'YYYYMMDDHH24MISS') ENDTIME, ORDERTYPE, PRODUCTNAME, PRODUCTID, SUBCHANNEL FROM TB_ORDER_SENDVIP_CONF WHERE CPID = ? AND SPID = ? AND STATUS = '0' AND SENDTYPE = '1' "; return SQLExecutor.queryListWithDBName(HashMap.class, DataSource.NET3G, sql, cpid, spid); } /** * 查询自动走直充送会员配置 * @param * @return */ public List qryOrderSendvipConfByZc(String cpid, String spid)throws SQLException { String sql = " SELECT CPID,SPID,CHANNEL,VIPID,VIPTYPE,VIPPWD,TO_CHAR(STARTTIME,'YYYYMMDDHH24MISS') STARTTIME, TO_CHAR(ENDTIME,'YYYYMMDDHH24MISS') ENDTIME, ORDERTYPE, PRODUCTNAME, PRODUCTID, SUBCHANNEL FROM TB_ORDER_SENDVIP_CONF WHERE CPID = ? AND SPID = ? AND STATUS = '0' AND SENDTYPE = '0' "; return SQLExecutor.queryListWithDBName(HashMap.class, DataSource.NET3G, sql, cpid, spid); } /** * 获取ID * @return * @throws SQLException */ public String getNo()throws SQLException { String sql = "SELECT TO_CHAR(SYSDATE,'yyyyMMddhh24miss')||SEQ_COMMON6.nextval ID FROM DUAL "; return (String)SQLExecutor.queryFieldWithDBName("net3g",sql,null); } /** * 添加自动赠送会员记录 * @param bean * @throws SQLException */ public void addSendvipRec(HashMap params) throws SQLException { String sql = "INSERT INTO TD_ORDER_SENDVIP_REC(ID, USERID, CPID, SPID, SENDMONTH ,RESULTCODE, RESULTINFO ,ORDERTIME ,ORDERCHANNEL, INSERTTIME, CONFID, SUBCHANNEL) " + " VALUES(#[ID], #[USERID],#[CPID],#[SPID],TO_CHAR(SYSDATE, 'YYYYMM'),#[RESULTCODE],#[RESULTINFO], TO_DATE(#[ORDERTIME],'YYYYMMDDHH24MISS'),#[ORDERCHANNEL], SYSDATE, #[CONFID],#[SUBCHANNEL] )"; SQLExecutor.insertBean("net3g", sql, params); } /** * 根据手机号码和SPID查询订购关系 * @param userid * @param spid * @return * @throws SQLException */ public List findOrderRel(String userid,String spid)throws SQLException { String sql = " SELECT ID, CPID, SPID, USERID, TO_CHAR(ORDERTIME, 'yyyymmddhh24miss') ORDERTIME, "+ " TO_CHAR(endtime, 'yyyymmddhh24miss') endtime, TO_CHAR(CANCELTIME, 'yyyymmddhh24miss') CANCELTIME, ORDERCHANNEL, ORDERCHANNEL2, CANCELCHANNEL2, "+ " (case when endtime is null then '0' when endtime >= sysdate then '1' else '2' end) STATUS "+ " FROM TD_ORDER_RELATIONS WHERE USERID = ? AND SPID = ? "; return SQLExecutor.queryListWithDBName(HashMap.class, DataSource.NET3G, sql, userid, spid); } /** * 获取待生成活动关系的数据 * @param srcflag * @return * @throws SQLException */ public List getBusshandleWaitData(String srcflag) throws SQLException { String sql = "SELECT * FROM ( "+ " SELECT ID WAITID,USERID,CPID,SPID,PROVINCE,AREA,STATUS ORDERTYPE, "+ " DECODE(STATUS,'0',ORDERTIME,CANCELTIME) UPDATETIME, "+ " DECODE(STATUS,'0', "+ " DECODE(ORDERCHANNEL,'kf','spfbtx11',NVL(ORDERCHANNEL,'spfbtx1')), "+ " DECODE(CANCELCHANNEL,'kf','spfbtx11',NVL(CANCELCHANNEL,'spfbtx1')) "+ " ) CHANNEL, "+ " DECODE(STATUS,'0', "+ " DECODE(ORDERCHANNEL,NULL,'0','2'), "+ " DECODE(CANCELCHANNEL,NULL,'0','2') "+ " ) ACTIVATED "+ " FROM TD_BUSSHANDLE_WAIT WHERE RESULTCODE = '1' AND SRCFLAG = ? ORDER BY INSERTTIME "+ ") WHERE ROWNUM < 200 "; return SQLExecutor.queryListWithDBName(OrderBean.class, DataSource.NET3G, sql, srcflag); } /** * 更新记录表的状态 * @param id * @param resultcode * @param resultinfo * @return * @throws SQLException */ public boolean updBusshandleWaitData(String id, String resultcode, String resultinfo) throws SQLException { String sql = " UPDATE TD_BUSSHANDLE_WAIT SET RESULTCODE = ?, RESULTINFO = ?, UPDTIME = SYSDATE WHERE ID = ? "; Object rows = SQLExecutor.updateWithDBName(DataSource.NET3G, sql, resultcode, resultinfo, id); if(Integer.parseInt(rows.toString()) > 0){ return true; }else{ return false; } } public static void main(String[] args) throws SQLException { OrderSendVipDao dao = new OrderSendVipDao(); //System.out.println(dao.updatePush("202005091510455306373", "0", "ok")); //System.out.println(dao.findOrderRelaBySpid("18673197465", "1022")); //System.out.println(dao.getNo()); //System.out.println(dao.queryPush("202005091510455306373")); HashMap params = new HashMap(); params.put("USERID", "18673197465"); params.put("CPID", "youtu"); params.put("SPID", "1168"); params.put("SENDMONTH", "202005"); params.put("RESULTCODE", "2"); params.put("RESULTINFO", "处理中"); params.put("ORDERTIME", "20200521164921"); params.put("ORDERCHANNEL", "yyyjjj"); //System.out.println( //dao.addSendvipRec(params); //); //System.out.println(dao.findOrderRel("18673197465", "1022")); //System.out.println(dao.updatePush("202005281022397945602", "0","OK")); System.out.println(dao.getBusshandleWaitData("0")); System.out.println(dao.updBusshandleWaitData("20210615110232000001", "0", "ok")); } }