CommonDao.java

package com.pipi.qa.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.pipi.qa.framework.utils.common.DbUtil;
import com.pipi.qa.framework.utils.common.JsonFormatUtil;
import com.pipi.qa.sql.SqlConfig;


public class CommonDao {

    private static DbUtil DbUtil;
    private final static Logger logger = Logger.getLogger(CommonDao.class);

    /**
     * @param phone
     * @param @return
     * @return String
     * @Description: 根据手机号码查询短信验证码
     */
    public static String getSmsContent(String phone) {
        String SMS_CONTENT = null;
        DbUtil = new DbUtil("yy_base_url", "yy_base_Username", "yy_base_Password");
        String[] phone_list = {phone};
        ResultSet res = DbUtil.query(SqlConfig.base_t_sms_send_SMS_CONTENT, phone_list);
        try {
            while (res.next()) {
                SMS_CONTENT = res.getString("SMS_CONTENT");
                logger.info(SMS_CONTENT);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        DbUtil.close();
        return SMS_CONTENT;
    }

    /**
     * @param @param  phone
     * @param @return
     * @return String
     * @Description: 根据手机号码获取user_id
     */
    public static String getUserId(String phone) {
        String user_id = null;
        DbUtil = new DbUtil("yy_user_url", "yy_user_Username", "yy_user_Password");
        String[] phone_list = {phone};
        ResultSet res = DbUtil.query(SqlConfig.Yy_userDao_userId, phone_list);
        try {
            while (res.next()) {
                user_id = res.getString("user_id");
                logger.info(user_id);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        DbUtil.close();
        return user_id;
    }

    /**
     * @param @param  phone
     * @param @return
     * @return String
     * @Description: 根据手机号码获取password
     */
    public static String getPassword(String phone) {
        String password = null;
        DbUtil = new DbUtil("yy_user_url", "yy_user_Username", "yy_user_Password");
        String[] phone_list = {phone};
        ResultSet res = DbUtil.query(SqlConfig.Yy_password, phone_list);
        try {
            while (res.next()) {
                password = res.getString("password");
                logger.info(password);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        DbUtil.close();
        return password;
    }

    /**
     * @param @param  user_id
     * @param @return
     * @return String
     * @Description: 根据user_id获取real_name
     */
    public static String getRealName(String user_id) {
        String real_name = null;
        DbUtil = new DbUtil("yy_user_url", "yy_user_Username", "yy_user_Password");
        String[] user_id_list = {user_id};
        ResultSet res = DbUtil.query(SqlConfig.Yy_userDao_realName, user_id_list);
        try {
            while (res.next()) {
                real_name = res.getString("real_name");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        DbUtil.close();
        logger.info("获取real_name :" + real_name);
        return real_name;
    }

    /**
     * @param @param  根据user_id获取idcard_no
     * @param @return
     * @return String
     * @Description:
     */
    public static String getIdcardNO(String user_id) {
        String idcard_no = null;
        DbUtil = new DbUtil("yy_user_url", "yy_user_Username", "yy_user_Password");
        String[] user_id_list = {user_id};
        ResultSet res = DbUtil.query(SqlConfig.Yy_userDao_idcardNO, user_id_list);
        try {
            while (res.next()) {
                idcard_no = res.getString("idcard_no");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        DbUtil.close();
        logger.info("获取idcard_no :" + idcard_no);
        return idcard_no;
    }

    public static String trade_order_contract_no(String user_id) {
        String contract_no = null;
        DbUtil = new DbUtil("yy_trade_url", "yy_trade_Username", "yy_trade_Password");
        String[] user_id_list = {user_id};
        ResultSet res = DbUtil.query(SqlConfig.trade_order_contract_no, user_id_list);
        try {
            while (res.next()) {
                contract_no = res.getString("contract_no");
            }
            DbUtil.close();
        } catch (Exception ex) {
            logger.error("Error : " + ex.toString());
        }
        logger.debug("查询出合同编号:" + contract_no);
        return contract_no;
    }

    //撤销商品贷订单
    public static int trade_order_update(String user_id) {
        int resp = 0;
        DbUtil = new DbUtil("yy_trade_url", "yy_trade_Username", "yy_trade_Password");
        try {
            resp = DbUtil.update(SqlConfig.trade_order_update, user_id);
            DbUtil.close();
        } catch (Exception ex) {
            logger.error("Error : " + ex.toString());
        }
        logger.debug("撤销商品贷订单" + user_id);
        return resp;
    }

    //撤销老核心订单
    public static int business_contractUpdate(String contract_no) {
        int resp = 0;
        DbUtil = new DbUtil("alsc_new_url", "alsc_new_Username", "alsc_new_Password");
        try {
            resp = DbUtil.update(SqlConfig.business_contractUpdate, contract_no);
            DbUtil.close();
        } catch (Exception ex) {
            logger.error("Error : " + ex.toString());
        }
        logger.debug("撤销老核心订单:" + contract_no);
        return resp;
    }

    //撤销新核心订单
    public static int audit_work_orderUpdate(String user_id) {
        int resp = 0;
        DbUtil = new DbUtil("arbiter_betaDw_url", "arbiter_betaD_Username", "arbiter_betaD_Password");
        try {
            resp = DbUtil.update(SqlConfig.audit_work_orderUpdate, user_id);
            DbUtil.close();
        } catch (Exception ex) {
            logger.error("Error : " + ex.toString());
        }
        logger.debug("撤销新核心订单audit_work_orderUpdate:" + resp);
        return resp;
    }

    public static int ext_order_requestUpdate(String user_id) {
        int resp = 0;
        DbUtil = new DbUtil("arbiter_betaDw_url", "arbiter_betaD_Username", "arbiter_betaD_Password");
        try {
            resp = DbUtil.update(SqlConfig.audit_work_orderUpdate, user_id);
            DbUtil.close();
        } catch (Exception ex) {
            logger.error("Error : " + ex.toString());
        }
        logger.debug("撤销新核心订单ext_order_requestUpdate:" + resp);
        return resp;
    }

    public static int selectProjId(String env) {
        int resp = 0;
        if ("test".equals(env.toLowerCase())) {
            DbUtil = new DbUtil("t8t_mid_proj.url", "t8t_mid_proj.username", "t8t_mid_proj.password");
        } else if ("old_test".equals(env.toLowerCase())) {
            DbUtil = new DbUtil("old_t8t_mid_proj.url", "old_t8t_mid_proj.username", "old_t8t_mid_proj.password");
        } else if ("uat".equals(env.toLowerCase())) {
            return 0;
        }

        ResultSet res = DbUtil.query(SqlConfig.CU_PROJECT_PROJ_ID);
        try {
            while (res.next()) {
                resp = res.getInt("proj_id");
            }
            DbUtil.close();
        } catch (Exception ex) {
            logger.error("Error : " + ex.toString());
        }
        return resp;
    }


    /**
     * @param @param  根据serviceName获取相关服务列表
     * @param @return
     * @return String
     * @Description:
     */
    public static List<String> getServiceList(String serviceName) {
        String before_service = null;
        List<String> before_service_list = new ArrayList<>();
        DbUtil = new DbUtil("jdbc.url", "jdbc.username", "jdbc.password");
        String[] serviceList = {serviceName};
        ResultSet res = DbUtil.query(SqlConfig.test_service_info, serviceName);
        try {
            while (res.next()) {
                before_service = res.getString("before_service");
                logger.debug("before_service:" + before_service);
                before_service_list.add(before_service);
            }
            DbUtil.close();
        } catch (Exception ex) {
            logger.error("Error : " + ex.toString());
        }

        return before_service_list;
    }

    //埋点插入
    public static int buriedPointDataAdd(String apiName, String apiPass, int executionResult, int createTime, String pvCode, String content) {
        int resp = 0;
        DbUtil = new DbUtil("buried_point_data.url", "buried_point_data.username", "buried_point_data.password");
        try {
            resp = DbUtil.update(SqlConfig.BURIED_POINT_DATA_ADD, apiName, apiPass, executionResult, createTime, pvCode, content);
            DbUtil.close();
        } catch (Exception ex) {
            logger.error("Error : " + ex.toString());
        }
        logger.debug("埋点插入buried_point_data:" + resp);
        return resp;
    }

    //发布数据
    public static int tProjectResponseadd(String requestJson, int executionResult, int createTime, int category, long demId, String executionRecord, String user) {
        int resp = 0;
        DbUtil = new DbUtil("buried_point_data.url", "buried_point_data.username", "buried_point_data.password");
        try {
            resp = DbUtil.update(SqlConfig.T_PROJECT_RESPONSE_ADD, requestJson, executionResult, createTime, category, demId, executionRecord, user);
            DbUtil.close();
        } catch (Exception ex) {
            logger.error("Error : " + ex.toString());
        }
        logger.debug("发布数据t_project_response:" + resp);
        return resp;
    }

    //查询数据
    public static List<Map<String, Object>> getTProjectResponseList(int page, int size, int category, long projectId) {
        //long projectId = 50001233;
        List<Map<String, Object>> t_project_response_list = new ArrayList<Map<String, Object>>();
        DbUtil = new DbUtil("buried_point_data.url", "buried_point_data.username", "buried_point_data.password");
        ResultSet res = null;
        if (projectId == 0) {
            res = DbUtil.query(SqlConfig.T_PROJECT_RESPONSE_SELECT, category, (page * size), size);
        } else {
            res = DbUtil.query(SqlConfig.T_PROJECT_RESPONSE_SELECT_project, category, "%" + projectId + "%", (page * size), size);
        }
        logger.debug(res.toString());
        try {
            while (res.next()) {
                Map<String, Object> result = JsonFormatUtil.jsonMap(res.getString("request_json"));
                result.put("execution_result", res.getInt("execution_result"));
                result.put("create_time", res.getInt("create_time"));
                result.put("executionRecord", res.getString("executionRecord"));
                result.put("user", res.getString("user"));
                //logger.debug("result:" + result);
                t_project_response_list.add(result);
            }
            DbUtil.close();
        } catch (Exception ex) {
            logger.error("Error : " + ex.toString());
        }
        return t_project_response_list;
    }

    /**
     * @param @param  获取全部sc服务列表
     * @param @return
     * @return String
     * @Description:
     */
    public static List<String> getAllServiceList() {
        String before_service = null;
        List<String> before_service_list = new ArrayList<>();
        DbUtil = new DbUtil("t8t_mid_proj.url", "t8t_mid_proj.username", "t8t_mid_proj.password");
        String[] serviceList = {};
        ResultSet res = DbUtil.query(SqlConfig.test_service_info_all, serviceList);
        try {
            while (res.next()) {
                before_service = res.getString("before_service");
                logger.debug("before_service:" + before_service);
                before_service_list.add(before_service);
            }
            DbUtil.close();
        } catch (Exception ex) {
            logger.error("Error : " + ex.toString());
        }
        return before_service_list;
    }

    public static int projectTotal(int category, long demId) {
        int resp = 0;
        DbUtil = new DbUtil("buried_point_data.url", "buried_point_data.username", "buried_point_data.password");
        ResultSet res = null;
        if (demId == 0) {
            res = DbUtil.query(SqlConfig.T_PROJECT_RESPONSE_TOTAL, category);
        } else {
            res = DbUtil.query(SqlConfig.T_PROJECT_RESPONSE_DEMID_TOTAL, category, "%" + demId + "%");
        }
        try {
            while (res.next()) {
                resp = res.getInt("total");
            }
            DbUtil.close();
        } catch (Exception ex) {
            logger.error("Error : " + ex.toString());
        }
        return resp;
    }

    public static long getAccountIdByuid(String uid) {
        long resp = 0;
        DbUtil = new DbUtil("to8to.url", "to8to.username", "to8to.password");
        String[] list = {uid};
        ResultSet res = DbUtil.query(SqlConfig.to8to_members_select, list);
        try {
            while (res.next()) {
                resp = res.getLong("account_id");
            }
            DbUtil.close();
        } catch (Exception ex) {
            logger.error("Error : " + ex.toString());
        }
        return resp;
    }

    //获取签章表主键
    public static long getZid(long projectId) {
        long resp = 0;
        DbUtil = new DbUtil("to8to.url", "to8to.username", "to8to.password");
        ResultSet res = DbUtil.query(SqlConfig.to8to_jj_smt_zb_select, projectId);
        try {
            while (res.next()) {
                resp = res.getLong("zid");
                logger.debug("zid:" + resp);
            }
            DbUtil.close();
        } catch (Exception ex) {
            logger.error("Error : " + ex.toString());
        }
        return resp;
    }


    //获取to8to_jj_smt_zb_cost 中标表中标记录ID是否存在
    public static long to8to_jj_smt_zb_cost(long zid) {
        long resp = 0;
        DbUtil = new DbUtil("to8to.url", "to8to.username", "to8to.password");
        ResultSet res = DbUtil.query(SqlConfig.to8to_jj_smt_zb_cost_select, zid);
        try {
            while (res.next()) {
                resp = res.getLong("count(*)");
                logger.debug("resp:" + resp);
            }
            DbUtil.close();
        } catch (Exception ex) {
            logger.error("Error : " + ex.toString());
        }
        return resp;
    }

    //获取to8to_jj_smt_zb_cost 插入中标记录
    public static int to8to_jj_smt_zb_cost_insert(long zid) {
        int resp = 0;
        DbUtil = new DbUtil("to8to.url", "to8to.username", "to8to.password");
        try {
            resp = DbUtil.update(SqlConfig.to8to_jj_smt_zb_cost_insert, zid);
            DbUtil.close();
        } catch (Exception ex) {
            logger.error("Error : " + ex.toString());
        }
        if (resp == 0) {
            logger.debug("插入中标记录失败" + zid);
        } else {
            logger.debug("插入中标记录成功" + zid);
        }
        return resp;
    }

    //发布数据
    public static int executionRecordAdd(String executionRecord, long demId) {
        int resp = 0;
        DbUtil = new DbUtil("buried_point_data.url", "buried_point_data.username", "buried_point_data.password");
        try {
            resp = DbUtil.update(SqlConfig.T_PROJECT_EXECUTION_RECORD_ADD, executionRecord, demId);
            DbUtil.close();
        } catch (Exception ex) {
            logger.error("Error : " + ex.toString());
        }
        logger.debug("发布数据t_project_response:" + resp);
        return resp;
    }

    /**
     *    根据id查询用户身份标识
      */
    public static int getIndentityForId(Integer userId) {
        int resp = 0;
        DbUtil = new DbUtil("t8t.url", "t8t.username", "t8t.password");
        ResultSet res = DbUtil.query(SqlConfig.to8to_members_select_indentity, userId);
        try {
            while (res.next()) {
                resp = res.getInt("indentity");
            }
            DbUtil.close();
        } catch (Exception ex) {
            logger.error("Error : " + ex.toString());
        }
        return resp;
    }
    /**
     * 新增一条数据
     * @param
     * @return
     */

    public static int account_insert(int userId,int account_config_id,java.math.BigDecimal money,String user_type_code,String account_type_name) {
        int resp = 0;
        DbUtil = new DbUtil("t8t.url", "t8t.username", "t8t.password");
        try {
            resp = DbUtil.update(SqlConfig.account_insert,userId,account_config_id,money,user_type_code,account_type_name);
            DbUtil.close();
        } catch (Exception ex) {
            logger.error("Error : " + ex.toString());
        }
        return resp;
    }

    /**
     * 更新
     * @param
     * @return
     */
    public static int account_update(java.math.BigDecimal money,String user_type_code,int userId,String account_type_name) {
        int resp = 0;
        DbUtil = new DbUtil("t8t.url", "t8t.username", "t8t.password");
        try {
            resp = DbUtil.update(SqlConfig.account_update,money,user_type_code,userId,account_type_name);
            DbUtil.close();
        } catch (Exception ex) {
            logger.error("Error : " + ex.toString());
        }
        return resp;
    }

    /**
     * 查询是否存在钱包余额，轮单余额，轮单充值账户，轮单赠送账户
     * @param
     * @return
     */
    public static List<Map<String, Object>> t8t_fi_acc_select(int userId,String wholeCode,String a,String b,String c,String d){
        List<Map<String,Object>> LM = new ArrayList<>();
        Map<String, Object> map = null;
        DbUtil = new DbUtil("t8t.url", "t8t.username", "t8t.password");
        ResultSet resp = DbUtil.query(SqlConfig.t8t_fi_acc_select, userId,wholeCode,a,b,c,d);
        try {
            while (resp.next()) {
                map = new HashMap<>();
                map.put("balance", resp.getString("balance"));
                map.put("account_type_name", resp.getObject("account_type_name"));
                map.put("user_type_code",resp.getObject("user_type_code"));
                LM.add(map);
                logger.debug("resp:" + resp);
            }
            DbUtil.close();
        } catch (Exception ex) {
            logger.error("Error : " + ex.toString());
        }
        return LM;
    }

    public static void main(String args[]) {

        //CommonDao.selectProjId();
        //CommonDao.getAllServiceList();
        //CommonDao.getAccountIdByuid("1678255");
        //CommonDao.getZid(50001097);
        //CommonDao.to8to_jj_smt_zb_cost(34876);
        //CommonDao.to8to_jj_smt_zb_cost_insert(34859);
        //CommonDao.getTProjectResponseList(1, 10, 0,50001233);
    }
}