const system = require("../../system");
const ServiceBase = require("../sve.base");
const moment = require("moment");

class EsettleService extends ServiceBase {
    constructor() {
        super(ServiceBase.getDaoName(EsettleService));
        //this.appDao=system.getObject("db.appDao");
        this.settledb = system.getObject("db.connection").getSettleDBCon();

        this.ecompanyDao = system.getObject("db.ecompanyDao");
        this.epartnerDao = system.getObject("db.epartnerDao");
        this.userDao = system.getObject("db.userDao");
        this.usereaccountDao = system.getObject("db.usereaccountDao");
        this.esettlecommissionDao = system.getObject("db.esettlecommissionDao");
        this.pattern = /(\d{4})(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})/;
    }

    async doSettleLogic(id) {
        return 1;
    }

    async countAmtByCard(idno, begin, end) {
        var sql = "SELECT sum(actual_amt) as num FROM `tbl_order_item` t1  " +
            "WHERE t1.trade_status = '00' AND t1.id_no = '" + idno + "' ";
        if (begin) {
            sql = sql + " AND t1.pay_complete_time >= '" + begin + "' ";
        }
        if (end) {
            sql = sql + " AND t1.pay_complete_time <= '" + end + "' ";
        }

        var count = 0;
        var countrs = await this.settledb.query(sql);
        if (countrs && countrs[0, 0]) {
            count = countrs[0, 0][0].num;
        }
        return parseFloat((Number(count || 0) / 100).toFixed(2));
    }


    async findOrderItemsByCard(idno, currentPage, pageSize) {
        currentPage = Number(currentPage || 1);
        pageSize = Number(pageSize || 10);
        var startRow = (currentPage - 1) * pageSize;

        var countSql = "SELECT COUNT(1) as num FROM `tbl_order_item` t1  " +
            "INNER JOIN `tbl_busi` t3 ON t3.`id` = t1.`busi_id` " +
            "WHERE t1.trade_status = '00' AND t1.id_no = '" + idno + "' " +
            "ORDER BY  t1.`pay_complete_time` DESC  ";
        var countrs = await this.settledb.query(countSql);
        var total = 0;
        if (countrs && countrs[0, 0]) {
            total = countrs[0, 0][0].num;
        }

        if (total == 0) {
            return {count: 0, rows: []}
        }

        var sql = "SELECT t1.id, t1.`id_name`, t1.`id_no`, t1.`actual_amt`, t1.`pay_complete_time`, t1.`busi_id`, t3.`company_name` FROM `tbl_order_item` t1  " +
            "INNER JOIN `tbl_busi` t3 ON t3.`id` = t1.`busi_id` " +
            "WHERE t1.trade_status = '00' AND t1.id_no = '" + idno + "' " +
            "ORDER BY  t1.`pay_complete_time` DESC  " +
            "LIMIT " + Number(startRow) + ", " + Number(pageSize);

        var list = await this.settledb.query(sql);
        if (list && list.length > 0) {
            list = list[0, 0];
            for (var item of list) {
                item.actual_amt = parseFloat((Number(item.actual_amt || 0) / 100).toFixed(2));
                if (item.pay_complete_time) {
                    item.pay_complete_time = moment(item.pay_complete_time).format("YYYY-MM-DD HH:mm");
                }
            }
        } else {
            list = []
        }
        return {count: total, rows: list};
    }

    async statSettle(params) {
        var sql = "";
        sql = sql + "SELECT SUM(t1.amt) AS num FROM `tbl_order_item` t1 ";
        sql = sql + "INNER JOIN `tbl_busi` t2 ON t1.`busi_id` = t2.id ";
        sql = sql + "WHERE t1.`trade_status` = '00' ";

        if (params.company_name) {
            sql = sql + " AND t2.company_name = :company_name ";
        }
        if (params.begin) {
            sql = sql + " AND pay_complete_time >= :begin ";
        }
        if (params.end) {
            sql = sql + " AND pay_complete_time <= :end ";
        }

        var list = await this.settledb.query(sql);
        if (list && list.length > 0) {
            list = list[0, 0];
            return list[0].num || 0;
        }
        return 0;
    }

    async ratepayingToPe(condition) {
        var currentPage = Number(condition.currentPage || 1);
        var pageSize = Number(condition.pageSize || 20);
        var startRow = (currentPage - 1) * pageSize;
        var listSql = "SELECT COUNT(*) count,id_name,mobile,id_no,pay_complete_time FROM tbl_order_item WHERE trade_status = '00'";

        if(condition.ecompany_id){
            listSql = listSql + " AND busi_id = '" + condition.ecompany_id + "'";
        }

        if(condition.busiIds && condition.busiIds.length > 0) {
            listSql = listSql + " AND busi_id IN (" + condition.busiIds.join(",") + ")";
        }
        if (condition.id_name) {
            listSql = listSql + " AND id_name = '" + condition.id_name + "'";
        }
        if (condition.id_no) {
            listSql = listSql + " AND id_no = '" + condition.id_no + "'";
        }
        if (condition.mobile) {
            listSql = listSql + " AND mobile = '" + condition.mobile + "'";
        }
        listSql = listSql + " GROUP BY id_no";
        var countSql = "SELECT COUNT(ta.id_no) count FROM (" + listSql + ") ta";
        listSql = listSql + " ORDER BY create_time DESC LIMIT " + Number(startRow) + ", " + Number(pageSize);
        var countrs = await this.settledb.query(countSql);
        var total = countrs[0][0].count;
        if (total == 0) {
            return {count: 0, list: []}
        }
        var list = await this.settledb.query(listSql);
        if (list[0].length > 0) {
            for (let i = 0;i < list[0].length; i++){
                list[0][i].create_time  = moment( list[0][i].create_time).format("YYYY-MM-DD HH:mm:ss");
            }
        }
        return {count: total, list: list[0]}
    }
    async ratepayingToMonth(condition) {
        var currentPage = Number(condition.currentPage || 1);
        var pageSize = Number(condition.pageSize || 20);
        var startRow = (currentPage - 1) * pageSize;
        this.addmonth(condition);
        var listSql = "SELECT SUM(actual_amt) actual_amt,SUM(income_tax) income_tax,id_name,mobile,id_no,create_time FROM tbl_order_item WHERE trade_status = '00'";
        if(condition.ecompany_id){
            listSql = listSql + " AND busi_id = '" + condition.ecompany_id + "'";
        }
        if(condition.busiIds && condition.busiIds.length > 0) {
            listSql = listSql + " AND busi_id IN (" + condition.busiIds.join(",") + ")";
        }
        if (condition.createBegin) {
            listSql = listSql + " AND `create_time` >= '" + condition.createBegin + "' ";
        }
        if (condition.createEnd) {
            listSql = listSql + " AND `create_time` <= '" + condition.createEnd + "' ";
        }
        listSql = listSql + " GROUP BY id_no";
        var countSql = "SELECT COUNT(ta.id_no) count FROM (" + listSql + ") ta";
        listSql = listSql + " ORDER BY create_time DESC LIMIT " + Number(startRow) + ", " + Number(pageSize);
        var countrs = await this.settledb.query(countSql);
        var total = countrs[0][0].count;
        if (total == 0) {
            return {count: 0, list: []}
        }
        var list = await this.settledb.query(listSql);
        if (list[0].length > 0) {
            for (let i = 0;i < list[0].length; i++){
                list[0][i].create_time  = moment( list[0][i].create_time).format("YYYY-MM-DD");
            }
        }
        return {count: total, list: list[0]}
    }

    async ratepayingToOne(condition){
        var listSql = "SELECT actual_amt,income_tax,create_time FROM tbl_order_item WHERE trade_status = '00'";
        if(condition.busiIds && condition.busiIds.length > 0) {
            listSql = listSql + " AND busi_id IN (" + condition.busiIds.join(",") + ")";
        }
        if(condition.ecompany_id){
            listSql = listSql + " AND busi_id = '" + condition.ecompany_id + "'";
        }
        if(condition.id_no){
            listSql = listSql + " AND id_no = '" + condition.id_no + "'";
        }
        var list = await this.settledb.query(listSql);
        if (list[0].length > 0) {
            for (let i = 0;i < list[0].length; i++){
                list[0][i].actual_amt = parseFloat((Number(list[0][i].actual_amt || 0) / 100).toFixed(2));
                list[0][i].income_tax = parseFloat((Number(list[0][i].income_tax || 0) / 100).toFixed(2));
                list[0][i].create_time  = moment( list[0][i].create_time).format("YYYY-MM-DD");
            }
        }
        return {list: list[0]};
    }

    async getPays(condition){
        var countSql = "SELECT SUM(actual_amt) actual_amt,SUM(income_tax) income_tax FROM tbl_order_item WHERE trade_status = '00' ";
        if(condition.busiIds && condition.busiIds.length > 0) {
            countSql = countSql + "AND busi_id IN (" + "'" + condition.busiIds.join("','") + "'" + ") ";
        }
        var list = await this.settledb.query(countSql);
        return {actual_amt:list[0][0].actual_amt,income_tax:list[0][0].income_tax}
    }

    async settlePage(condition) {
        var currentPage = Number(condition.currentPage || 1);
        var pageSize = Number(condition.pageSize || 20);
        var startRow = (currentPage - 1) * pageSize;

        var countSql = "SELECT COUNT(DISTINCT t1.id) as num " +
            "FROM tbl_order t1 " +
            "INNER JOIN `tbl_busi` t2 ON t1.`busi_id` = t2.`id` " +
            "WHERE 1 = 1 ";

        var listSql = "SELECT " +
            "t2.`company_name`, t2.`open_bank`, t2.`bank_no`, t2.`bank_account_name`, t1.`busi_id`, " +
            "t1.id AS order_id, t1.out_trade_no, t1.`amt`, t1.`actual_amt`, t1.`deduct_amt`, t1.trade_status , t1.trade_type, " +
            "t1.`create_time`, t1.pay_complete_time " +
            "FROM tbl_order t1 " +
            "INNER JOIN `tbl_busi` t2 ON t1.`busi_id` = t2.`id` " +
            "WHERE 1 = 1 ";

        if (condition.busi_id) {
            countSql = countSql + " AND t2.`id` = " + condition.busi_id + " ";
            listSql = listSql + " AND t2.`id` = " + condition.busi_id + " ";
        }

        if (condition.companyNames && condition.companyNames.length > 0) {
            countSql = countSql + " AND t2.`company_name` IN (" + "'" + condition.companyNames.join("','") + "'" + ") ";
            listSql = listSql + " AND t2.`company_name` IN (" + "'" + condition.companyNames.join("','") + "'" + ") ";
        }

        if (condition.companyName) {
            countSql = countSql + " AND t2.`company_name` = '" + condition.companyName + "' ";
            listSql = listSql + " AND t2.`company_name` = '" + condition.companyName + "' ";
        }

        if (condition.companyNameLike) {
            countSql = countSql + " AND t2.`company_name` LIKE :companyNameLike ";
            listSql = listSql + " AND t2.`company_name` LIKE :companyNameLike ";

        }

        if (condition.createBegin) {
            countSql = countSql + " AND t1.`create_time` >= '" + condition.createBegin + "' ";
            listSql = listSql + " AND t1.`create_time` >= '" + condition.createBegin + "' ";
        }
        if (condition.createEnd) {
            countSql = countSql + " AND t1.`create_time` <= '" + condition.createEnd + "' ";
            listSql = listSql + " AND t1.`create_time` <= '" + condition.createEnd + "' ";
        }

        if (condition.out_trade_no) {
            countSql = countSql + " AND t1.`out_trade_no` = '" + condition.out_trade_no + "' ";
            listSql = listSql + " AND t1.`out_trade_no` = '" + condition.out_trade_no + "' ";
        }

        if (condition.amtBegin) {
            countSql = countSql + " AND t1.`amt` >= " + condition.amtBegin;
            listSql = listSql + " AND t1.`amt` >= " + condition.amtBegin;
        }

        if (condition.amtEnd) {
            countSql = countSql + " AND t1.`amt` <= " + condition.amtEnd;
            listSql = listSql + " AND t1.`amt` <= " + condition.amtEnd;
        }

        listSql = listSql + " GROUP BY t1.id ORDER BY t1.`pay_complete_time` DESC  ";
        listSql = listSql + " LIMIT " + Number(startRow) + ", " + Number(pageSize);

        var countrs = await this.settledb.query(countSql, {replacements: {companyNameLike: "%" + condition.companyNameLike + "%"}});
        var total = 0;
        if (countrs && countrs[0, 0]) {
            total = countrs[0, 0][0].num;
        }

        if (total == 0) {
            return {count: 0, rows: []}
        }

        var list = await this.settledb.query(listSql, {replacements: {companyNameLike: "%" + condition.companyNameLike + "%"}});
        if (list && list.length > 0) {
            list = list[0, 0] || [];
            var orderIds = [];
            for (var item of list) {
                orderIds.push(item.order_id);
            }

            var itmap = [];
            if (orderIds.length > 0) {
                var itemSql = "SELECT order_id, COUNT(1) AS num FROM `tbl_order_item` WHERE order_id IN (" + orderIds.join(",") + ") GROUP BY order_id ";
                var itlist = await this.settledb.query(itemSql);
                if (itlist && itlist.length > 0) {
                    itlist = itlist[0, 0] || [];
                    for (var itc of itlist) {
                        itmap['order_id_' + itc.order_id] = itc.num || 0;
                    }
                }
            }

            for (var item of list) {
                item.amt = parseFloat((Number(item.amt || 0) / 100).toFixed(2));
                item.actual_amt = parseFloat((Number(item.actual_amt || 0) / 100).toFixed(2));
                item.deduct_amt = parseFloat((Number(item.deduct_amt || 0) / 100).toFixed(2));

                if (item.trade_status == "00") {
                    item.trade_status_name = "成功";
                } else if (item.trade_status == "01") {
                    item.trade_status_name = "待处理";
                } else if (item.trade_status == "02") {
                    item.trade_status_name = "失败";
                } else {
                    item.trade_status_name = "";
                }

                if (item.trade_type == "00") {
                    item.trade_type_name = "实时";
                } else if (item.trade_type == "01") {
                    item.trade_type_name = "批量";
                } else {
                    item.trade_type_name = "";
                }

                if (item.create_time) {
                    item.create_time = moment(item.create_time).subtract(8, "hours").format("YYYY-MM-DD HH:mm");
                }
                if (item.pay_complete_time) {
                    item.pay_complete_time = moment(item.pay_complete_time).subtract(8, "hours").format("YYYY-MM-DD HH:mm");
                }
                item.itemCount = itmap["order_id_" + item.order_id] || 0;
            }
        } else {
            list = []
        }
        return {count: total, rows: list};
    }

    async statSettleAmt(condition) {
        var sql = "SELECT " +
            "t1.`amt` " +
            "FROM tbl_order t1 " +
            "INNER JOIN `tbl_busi` t2 ON t1.`busi_id` = t2.`id` " +
            "WHERE 1 = 1 ";

        if (condition.companyNames && condition.companyNames.length > 0) {
            sql = sql + " AND t2.`company_name` IN (" + "'" + condition.companyNames.join("','") + "'" + ") ";
        }

        if (condition.companyName) {
            sql = sql + " AND t2.`company_name` = '" + condition.companyName + "' ";
        }

        if (condition.companyNameLike) {
            sql = sql + " AND t2.`company_name` LIKE :companyNameLike ";
        }

        if (condition.createBegin) {
            sql = sql + " AND t1.`create_time` >= '" + condition.createBegin + "' ";
        }
        if (condition.createEnd) {
            sql = sql + " AND t1.`create_time` >= '" + condition.createBegin + "' ";
        }

        if (condition.out_trade_no) {
            sql = sql + " AND t1.`out_trade_no` = '" + condition.out_trade_no + "' ";
        }

        if (condition.amtBegin) {
            sql = sql + " AND t1.`amt` >= " + condition.amtBegin;
        }

        if (condition.amtEnd) {
            sql = sql + " AND t1.`amt` <= " + condition.amtEnd;
        }

        sql = sql + " GROUP BY t1.id  ";

        var amt = 0;
        var list = await this.settledb.query(sql, {replacements: {companyNameLike: "%" + condition.companyNameLike + "%"}});
        if (list && list.length > 0) {
            list = list[0, 0] || [];
            for (var item of list) {
                amt = amt + Number(item.amt);
            }
        }
        return parseFloat((amt / 100).toFixed(2));
    }

    async settleItemList(condition) {
        var currentPage = Number(condition.currentPage || 1);
        var pageSize = Number(condition.pageSize || 20);
        var startRow = (currentPage - 1) * pageSize;

        var countSql = "SELECT COUNT(1) as num FROM tbl_order_item WHERE 1 = 1 ";
        var listSql = "SELECT * FROM tbl_order_item WHERE 1 = 1  ";

        var where = {};
        if (condition.orderId) {
            countSql = countSql + " AND order_id = :orderId ";
            listSql = listSql + " AND order_id = :orderId ";
            where.orderId = condition.orderId;
        }

        listSql = listSql + " ORDER BY  pay_complete_time DESC, id ASC ";
        listSql = listSql + " LIMIT " + Number(startRow) + ", " + Number(pageSize);

        var countrs = await this.settledb.query(countSql, {replacements: where});
        var total = 0;
        if (countrs && countrs[0, 0]) {
            total = countrs[0, 0][0].num;
        }

        if (total == 0) {
            return {count: 0, rows: []}
        }

        var list = await this.settledb.query(listSql, {replacements: where});
        if (list && list.length > 0) {
            list = list[0, 0] || [];
            for (var item of list) {
                item.amt = parseFloat((Number(item.amt || 0) / 100).toFixed(2));
                item.actual_amt = parseFloat((Number(item.actual_amt || 0) / 100).toFixed(2));
                item.deduct_amt = parseFloat((Number(item.deduct_amt || 0) / 100).toFixed(2));

                if (item.trade_status == "00") {
                    item.trade_status_name = "成功";
                } else if (item.trade_status == "01") {
                    item.trade_status_name = "待处理";
                } else if (item.trade_status == "02") {
                    item.trade_status_name = "失败";
                } else {
                    item.trade_status_name = "";
                }

                if (item.trade_type == "00") {
                    item.trade_type_name = "实时";
                } else if (item.trade_type == "01") {
                    item.trade_type_name = "批量";
                } else {
                    item.trade_type_name = "";
                }

                if (item.create_time) {
                    item.create_time = moment(item.create_time).subtract(8, "hours").format("YYYY-MM-DD HH:mm");
                }
                if (item.pay_complete_time) {
                    item.pay_complete_time = moment(item.pay_complete_time).subtract(8, "hours").format("YYYY-MM-DD HH:mm");
                }

                item.idno = item.id_no;
                if(item.id_no) {
                    let id_no_show = item.id_no.substring(item.id_no.length - 4);
                    item.id_no = "**************" + id_no_show;
                }

            }
        } else {
            list = []
        }
        return {count: total, rows: list};
    }

    async doCommission(orderId) {
        var tblorder = await this.findTBLOrderById(orderId);
        if (!tblorder || !tblorder.company_name) {
            return;
        }

        var ecompany = await this.ecompanyDao.findOne({name: tblorder.company_name});
        var ecompanyId = 0;
        var epartnerId = 0;
        var epartnerUserId = 0;
        var epartner = null;
        if (ecompany) {
            ecompanyId = ecompany.id;
            epartner = await this.epartnerDao.findById(ecompany.epartner_id);
            if (epartner) {
                epartnerId = epartner.id;
                epartnerUserId = epartner.user_id;
            }
        }
        // || !ecompany.epartner_id || !ecompany.isBenefit


        var settle = await this.findOne({tbl_order_id: orderId});

        var payTime = moment(tblorder.create_time).subtract(8, "hours");
        var payCompleteTime = moment(tblorder.create_time).subtract(8, "hours");
        var month = Number(moment(payCompleteTime).format("YYYYMM"));
        if (!settle) {
            settle = {
                ecompany_id: ecompanyId,
                ecompanyName: tblorder.company_name,
                epartner_id: epartnerId,
                user_id: epartnerUserId,
                month: month,
                pay_time: payTime,
                pay_complete_time: payCompleteTime,
                out_trade_no: tblorder.out_trade_no,
                tbl_order_id: tblorder.id,
                busi_id: tblorder.busi_id,
                amt: tblorder.amt || 0,
                actual_amt: tblorder.actual_amt || 0,
                deduct_amt: tblorder.deduct_amt || 0,
                service_tax: tblorder.service_tax || 0,
                added_value_tax: tblorder.added_value_tax || 0,
                service_rate: tblorder.service_rate || 0,
            }
            settle = await this.create(settle);
        } else {
            settle.ecompany_id = ecompanyId;
            settle.ecompanyName = tblorder.company_name;
            settle.epartner_id = epartnerId;
            settle.user_id = epartnerUserId;
            settle.month = month;
            settle.pay_time = payTime;
            settle.pay_complete_time = payCompleteTime;
            settle.out_trade_no = tblorder.out_trade_no;
            settle.tbl_order_id = tblorder.id;
            settle.busi_id = tblorder.busi_id;
            settle.amt = tblorder.amt || 0;
            settle.actual_amt = tblorder.actual_amt || 0;
            settle.deduct_amt = tblorder.deduct_amt || 0;
            settle.service_rate = tblorder.service_rate || 0;
            settle.service_tax = tblorder.service_tax || 0;
            settle.added_value_tax = tblorder.added_value_tax || 0;
            await settle.save();
        }

        if (ecompany && epartner) {
            await this.addCommission(settle, epartner, ecompany);
        }
    }

    async addCommission(esettle, epartner, ecompany) {
        var commission = 0;
        var commissionTypeName = "每次发薪提成";
        var commissionType = '1';
        var commissionSourceName = '我的客户';
        var commissionSource = '0';
        var serviceRate = Number(esettle.service_rate);
        // 合伙人等级
        if (ecompany.partnerType == '1') {
            // 中级
            commission = Math.floor(esettle.service_tax * 0.1);
        } else if (ecompany.partnerType == '2') {
            // 高级
            if (!serviceRate) {
                commission = 0;
            } else {
                commission = Math.floor((serviceRate - 1) * esettle.service_tax / serviceRate);
            }
        } else {

            // 查询公司上个月分润
            var lastMonth = Number(moment(esettle.pay_complete_time).subtract(1, 'months').format("YYYYMM"));
            var sql = "SELECT COUNT(1) as num FROM `c_esettle_commission` WHERE ecompany_id = :ecompanyId AND `month` = :month ";
            var countrs = await this.customQuery(sql, {ecompanyId: ecompany.id, month: lastMonth});
            if (countrs && countrs.length > 0 && countrs[0].num) {
                ecompany.isBenefit = false;
                await ecompany.save();
                return;
            }

            // 初级
            commission = Math.floor(esettle.service_tax * 0.1);
            commissionTypeName = '首次发薪提成';
            commissionType = '0';
        }

        var esettleCommssion = await this.esettlecommissionDao.findOne({
            esettle_id: esettle.id,
            epartner_id: epartner.id
        });
        var isExist = esettleCommssion ? true : false;
        if (!isExist) {
            esettleCommssion = {};
        }
        esettleCommssion.month = esettle.month;
        esettleCommssion.esettle_id = esettle.id;
        esettleCommssion.ecompany_id = esettle.ecompany_id;
        esettleCommssion.ecompanyName = esettle.ecompanyName;
        esettleCommssion.amt = esettle.amt;
        esettleCommssion.service_tax = esettle.service_tax;
        esettleCommssion.service_rate = esettle.service_rate;
        esettleCommssion.out_trade_no = esettle.out_trade_no;

        esettleCommssion.commission = commission;
        esettleCommssion.commissionType = commissionType;
        esettleCommssion.commissionTypeName = commissionTypeName;
        esettleCommssion.commissionSource = commissionSource;
        esettleCommssion.commissionSourceName = commissionSourceName;
        esettleCommssion.epartner_id = esettle.epartner_id;
        esettleCommssion.user_id = esettle.user_id;
        esettleCommssion.epartnerSourceName = "";
        esettleCommssion.pay_complete_time = esettle.pay_complete_time;

        if (isExist) {
            await esettleCommssion.save();
        } else {
            await this.esettlecommissionDao.create(esettleCommssion);
        }

        if (epartner.sourcePartnerId) {
            var pepartner = await this.epartnerDao.findById(epartner.sourcePartnerId);
            var pcommission = Math.floor(commission * 0.1);
            pcommission = pcommission < 0 ? 0 : pcommission;

            var pesettleCommssion = await this.esettlecommissionDao.findOne({
                esettle_id: esettle.id,
                epartner_id: pepartner.id
            });
            var pIsExist = pesettleCommssion ? true : false;
            if (!pIsExist) {
                pesettleCommssion = {};
            }
            pesettleCommssion.month = esettle.month;
            pesettleCommssion.esettle_id = esettle.id;
            pesettleCommssion.ecompany_id = esettle.ecompany_id;
            pesettleCommssion.ecompanyName = esettle.ecompanyName;
            pesettleCommssion.amt = esettle.amt;

            pesettleCommssion.service_tax = esettle.service_tax;
            pesettleCommssion.service_rate = esettle.service_rate;
            pesettleCommssion.out_trade_no = esettle.out_trade_no;

            pesettleCommssion.commission = commission;
            pesettleCommssion.commissionType = "2";
            pesettleCommssion.commissionTypeName = "佣金提成";
            pesettleCommssion.commissionSource = "1";
            pesettleCommssion.commissionSourceName = "合伙人";
            pesettleCommssion.epartner_id = pepartner.id;
            pesettleCommssion.user_id = pepartner.user_id;
            pesettleCommssion.epartnerSourceName = epartner.nickName;
            pesettleCommssion.pay_complete_time = esettle.pay_complete_time;

            if (pIsExist) {
                await pesettleCommssion.save();
            } else {
                await this.esettlecommissionDao.create(pesettleCommssion);
            }
        }
    }

    async settleCommission(begin, end) {
        var orderIds = await this.findTBLOrderIds(begin, end);
        if (!orderIds || orderIds.length == 0) {
            return;
        }

        for (var order of orderIds) {
            try {
                await this.doCommission(order.id);
            } catch (error) {
                console.log(error);
            }
        }
    }

    async findTBLOrderById(id) {
        var sql = [];
        sql.push("SELECT ");
        sql.push("t1.create_time, t1.pay_complete_time, t1.out_trade_no, t1.id, t1.busi_id,");
        sql.push("t1.amt, t1.actual_amt, t1.deduct_amt, t1.service_tax, t1.added_value_tax,");
        sql.push("t2.company_name,");
        sql.push("t3.service_rate,");
        sql.push("1")
        sql.push("FROM tbl_order t1");
        sql.push("INNER JOIN tbl_busi t2 ON t1.busi_id = t2.id");
        sql.push("INNER JOIN tbl_busi_signed t3 ON t1.busi_id = t3.busi_id");
        sql.push("WHERE t1.id = :id");
        var where = {id: id};

        var list = await this.settledb.query(sql.join(" "), {replacements: where});
        if (list && list.length > 0) {
            list = list[0, 0] || [];
        }

        return list && list.length > 0 ? list[0] : null;
    }

    async findAmtByOutTradeNo(params) {
        let sql = [];
        //
        sql.push("SELECT");
        sql.push("SUM(actual_amt) AS actual_amt");
        sql.push("FROM tbl_order WHERE busi_id = :busi_id AND out_trade_no = :out_trade_no");
        let list = await this.settledb.query(sql.join(" "), {replacements: params});
        if (list && list.length > 0) {
            list = list[0, 0] || [];
        }
        return (!list || list.length == 0) ? 0 : (list[0].actual_amt || 0);
    }

    async findMchtApiInfo(params) {
        if (!params.mchtId) {
            return null;
        }
        let sql = [];
        sql.push("SELECT");
        sql.push("t1.id AS mchtId, t1.`company_name` AS companyName, t2.`main_id` AS mainId, t3.id AS appId, t3.secret");
        sql.push("FROM `tbl_busi` t1");
        sql.push("INNER JOIN tbl_busi_signed t2 ON t1.`id` = t2.`busi_id`");
        sql.push("INNER JOIN `tbl_busi_api` t3 ON t3.`busi_id` = t1.`id`");
        sql.push("WHERE t1.`id` = :mchtId");
        sql.push("LIMIT 1");

        let list = await this.settledb.query(sql.join(" "), {replacements: params});
        if (list && list.length > 0) {
            list = list[0, 0] || [];
        }
        return (!list || list.length == 0) ? null : list[0];
    }

    async findTBLOrderIds(begin, end) {
        if (!begin) {
            begin = moment().subtract(3, "days").format("YYYY-MM-DD");
        }
        begin = begin + " 00:00:00";
        if (!end) {
            end = moment().format("YYYY-MM-DD");
        }
        end = end + " 23:59:59";
        console.log(begin, end);

        var sql = [];
        sql.push("SELECT id");
        sql.push("FROM tbl_order");
        sql.push("WHERE");
        sql.push("trade_status = '00' AND check_status = '3'");
        sql.push("AND pay_complete_time >= :begin AND pay_complete_time <= :end");
        sql.push("AND pay_complete_time IS NOT NULL");
        sql.push("ORDER BY pay_complete_time ASC");
        var where = {begin: begin, end: end};
        var list = await this.settledb.query(sql.join(" "), {replacements: where});
        if (list && list.length > 0) {
            list = list[0, 0] || [];
        }
        return list;
    }

    async balancePage(condition) {
        var currentPage = Number(condition.currentPage || 1);
        var pageSize = Number(condition.pageSize || 20);
        var startRow = (currentPage - 1) * pageSize;

        var countSql = [];
        countSql.push("SELECT COUNT(1) AS num ");
        countSql.push("FROM tbl_busi_account t1");
        countSql.push("INNER JOIN `tbl_busi` t2 ON t1.`bus_id` = t2.`id`");
        countSql.push("WHERE 1 = 1 ");

        var listSql = [];
        listSql.push("SELECT t1.bus_id, t2.`company_name`, t1.`available_amt`,t1.`frozen_amt`");
        listSql.push("FROM tbl_busi_account t1");
        listSql.push("INNER JOIN `tbl_busi` t2 ON t1.`bus_id` = t2.`id`");
        listSql.push("WHERE 1 = 1 ");

        var where = {};
        if (condition.companyNames) {
            countSql.push(" AND t2.company_name IN (:companyNames)");
            listSql.push(" AND t2.company_name IN (:companyNames)");
            where.companyNames = condition.companyNames;
        }

        if (condition.ecompanyName) {
            countSql.push(" AND t2.`company_name` LIKE :companyNameLike ");
            listSql.push(" AND t2.`company_name` LIKE :companyNameLike ");
            where.companyNameLike = "%" + condition.ecompanyName + "%";
        }

        listSql.push(" ORDER BY t1.`available_amt` ASC, t1.`frozen_amt` ASC ");
        listSql.push(" LIMIT " + Number(startRow) + ", " + Number(pageSize));

        var countrs = await this.settledb.query(countSql.join(" "), {replacements: where});
        var total = 0;
        if (countrs && countrs[0, 0]) {
            total = countrs[0, 0][0].num;
        }

        if (total == 0) {
            return {count: 0, rows: []}
        }

        var list = await this.settledb.query(listSql.join(" "), {replacements: where});
        if (list && list.length > 0) {
            list = list[0, 0] || [];
            for (var item of list) {
                item.available_amt = parseFloat((Number(item.available_amt || 0) / 100).toFixed(2));
                item.frozen_amt = parseFloat((Number(item.frozen_amt || 0) / 100).toFixed(2));
            }
        } else {
            list = []
        }
        return {count: total, rows: list};
    }

    async statOnlineSettle(params) {
        var sql = [];
        sql.push("SELECT");
        sql.push("SUM(amt) AS amtCount,");
        sql.push("SUM(actual_amt) AS actualAmtCount,");
        sql.push("SUM(service_tax) AS serviceTaxCount,");
        sql.push("SUM(added_value_tax) AS addedValueTaxCount,");
        sql.push("COUNT(DISTINCT busi_id) AS busiCount,");
        sql.push("COUNT(DISTINCT id_no) AS idNoCount");
        sql.push("FROM");
        sql.push("tbl_order_item");
        sql.push("WHERE pay_complete_time IS NOT NULL AND trade_status = '00'");
        var where = {};
        if (params.payTimeBegin) {
            sql.push("AND pay_complete_time >= :payTimeBegin");
            where.payTimeBegin = params.payTimeBegin;
        }

        if (params.payTimeEnd) {
            sql.push("AND pay_complete_time <= :payTimeEnd");
            where.payTimeEnd = params.payTimeEnd;
        }
        var stat = {};
        var list = await this.settledb.query(sql.join(" "), {replacements: where});
        if (list && list.length > 0) {
            list = list[0, 0] || [];
            if (list && list.length > 0) {
                stat = list[0] || {};
                stat.amtCount = system.f2y(stat.amtCount);
                stat.actualAmtCount = system.f2y(stat.actualAmtCount);
                stat.serviceTaxCount = system.f2y(stat.serviceTaxCount);
                stat.addedValueTaxCount = system.f2y(stat.addedValueTaxCount);
            }
        }
        return stat;
    }

    async findIncomeTaxById(orderItemId) {
        var getTax = {};
        if (!orderItemId) {
            return getTax;
        }
        var sql = [];
        sql.push("SELECT");
        sql.push("income_tax AS incomeTax ");
        sql.push("FROM");
        sql.push("tbl_order_item ");
        sql.push("WHERE pay_complete_time IS NOT NULL AND trade_status = '00'");
        var where = {};
        if (orderItemId) {
            sql.push("AND id = :id");
            where.id = orderItemId;
        }

        var list = await this.settledb.query(sql.join(" "), {replacements: where});
        if (list && list.length > 0) {
            list = list[0, 0] || [];
            if (list && list.length > 0) {
                getTax = list[0] || {};
                getTax.incomeTax = getTax.incomeTax;
            }
        }
        return getTax;
    }

    async findOrderItemById(orderItemId) {
        if (!orderItemId) {
            return null;
        }
        var sql = [];
        sql.push("SELECT");
        sql.push("*");
        sql.push("FROM");
        sql.push("tbl_order_item ");
        sql.push("WHERE pay_complete_time IS NOT NULL AND trade_status = '00'");
        var where = {};
        if (orderItemId) {
            sql.push("AND id = :id");
            where.id = orderItemId;
        }
        var getTax;
        var list = await this.settledb.query(sql.join(" "), {replacements: where});
        if (list && list.length > 0) {
            list = list[0, 0] || [];
            if (list && list.length > 0) {
                getTax = list[0];
            }
        }
        return getTax;
    }

    async isValidAge(ids) {
        if (!ids || ids.length == 0) {
            return 0;
        }
        let sql = "SELECT COUNT(1) AS num FROM tbl_busi WHERE id IN (:ids) AND age_flag = 1 ";
        let list = await this.settledb.query(sql, {replacements: {ids: ids}});
        if (list && list.length > 0) {
            list = list[0, 0] || [];
            if (!list || list.length == 0) {
                return 0;
            }

            return list[0].num || 0;
        }

        return 0;
    }

}

module.exports = EsettleService;