import DateUtil from '../util/DateUtil'
import request from 'request'
import config from '../config/Config'
import logger from '../logger/Logger'
import DBService from '../db/DBService'
import BaseCronJob from '../core/BaseCronJob'

export default class DailyReportMail extends BaseCronJob {

    async execute() {
        try {
            let dbService = new DBService()
            // 获取昨日的日报的输出文件,发送邮件
            let date = DateUtil.getLastDayDate()

            // 查询美国新增用户
            let registerNum
            let sql = `SELECT COUNT(*) AS num FROM climb.sys_user WHERE pt = '${date}' and countryZh = "美国"`
            let result = await dbService.query(sql, [])

            if (result["err"]) {
                logger.info(result["err"])
            } else {
                if (result["rows"]) {
                    registerNum = result["rows"][0]["num"]
                }
            }

            // 查询美国活跃用户
            let activeNum
            sql = `SELECT COUNT(DISTINCT (userName)) AS num FROM climb.hourly_active_user WHERE queryTime = '${date}' AND countryZh = '美国'`
            result = await dbService.query(sql, [])

            if (result["err"]) {
                logger.info(result["err"])
            } else {
                if (result["rows"]) {
                    activeNum = result["rows"][0]["num"]
                }
            }

            // 查询留存
            let beforeDate = DateUtil.getTheDayBeforeYesterday()
            let retainedRate
            sql = `SELECT (sum(keep_number_1) / sum(sales_user_num) * 100) AS rate FROM climb.ot_user_keeps where pt = "${beforeDate}" and country_zh = "美国"`
            result = await dbService.query(sql, [])

            if (result["err"]) {
                logger.info(result["err"])
            } else {
                if (result["rows"]) {
                    retainedRate = result["rows"][0]["rate"] + "%"
                }
            }

            // 查询美国连接用户数
            let connectUser
            sql = `SELECT count(distinct(userName)) as num FROM climb.hourly_connect_user where pt = "${date}" and countryZh = "美国";`
            result = await dbService.query(sql, [])

            if (result["err"]) {
                logger.info(result["err"])
            } else {
                if (result["rows"]) {
                    connectUser = result["rows"][0]["num"]
                }
            }

            // 矿机连接成功率
            let minerConnectRate
            sql = `SELECT (sum(successCount) / sum(totalCount) * 100) as rate FROM climb.hourly_miner_connect_rate where pt = "${date}" and countryZh = "美国";`
            result = await dbService.query(sql, [])

            if (result["err"]) {
                logger.info(result["err"])
            } else {
                if (result["rows"]) {
                    minerConnectRate = result["rows"][0]["rate"] + "%"
                }
            }

            // 查询会话次数
            let sessionCount
            sql = `SELECT count(*) as num FROM climb.hourly_connect_user where pt = "${date}" and countryZh = "美国";`
            result = await dbService.query(sql, [])

            if (result["err"]) {
                logger.info(result["err"])
            } else {
                if (result["rows"]) {
                    sessionCount = result["rows"][0]["num"]
                }
            }

            let averageConnCount = activeNum == 0 ? 0 : (sessionCount / connectUser).toFixed(2)

            // 计算虚拟分配成功率
            let allocRate
            sql = `SELECT (sum(allocSuccessCount) / sum(allocCount) * 100) as rate FROM climb.hourly_connect_user where pt = "${date}" and countryZh = "美国" and (error != "asset-not-enough" or error is null)`
            result = await dbService.query(sql, [])

            if (result["err"]) {
                logger.info(result["err"])
            } else {
                if (result["rows"]) {
                    allocRate = result["rows"][0]["rate"] + "%"
                }
            }

            // 计算数据获取成功率
            let dataAccessRate
            sql = `SELECT (sum(haveReturnDataCount) / sum(totalCount) * 100) as rate FROM climb.hourly_miner_connect_rate where pt = "${date}" and countryZh = "美国";`
            result = await dbService.query(sql, [])

            if (result["err"]) {
                logger.info(result["err"])
            } else {
                if (result["rows"]) {
                    dataAccessRate = result["rows"][0]["rate"] + "%"
                }
            }

            // 矿机crash次数
            let crashCount
            sql = `SELECT COUNT(*) as num FROM log_miner_crash WHERE DATE(add_time) = "${date}"`
            result = await dbService.query(sql, [])

            if (result["err"]) {
                logger.info(result["err"])
            } else {
                if (result["rows"]) {
                    crashCount = result["rows"][0]["num"]
                }
            }

            // 矿机流量
            let tx, rx, duration
            sql = `select sum(tx) tx , sum(rx) rx , sum(duration) duration from daily_user_used_flow where date = "${date}" and country = "美国"`
            result = await dbService.query(sql, [])

            if (result["err"]) {
                logger.info(result["err"])
            } else {
                let { rows } = result

                if (rows) {
                    tx = rows[0]["tx"]
                    rx = rows[0]["rx"]
                    duration = rows[0]["duration"]
                }
            }

            let averageFlow = connectUser == 0 ? 0 : (((tx + rx) / 1024 / 1024 / connectUser).toFixed(2) + "MB")
            let averageTime = (connectUser == 0 ? 0 : (duration / 60 / connectUser).toFixed(2)) + "分钟"
            let txSpeed = duration == 0 ? 0 : ((tx / 1024 / duration).toFixed(2) + "KB/S")
            let rxSpeed = duration == 0 ? 0 : ((rx / 1024 / duration).toFixed(2) + "KB/S")
            let txMB = (tx / 1024 / 1024).toFixed(2) + "MB"
            let rxMB = (rx / 1024 / 1024).toFixed(2) + "MB"

            let out =
                `<table>` +
                `<tr><td>US新增</td><td>${registerNum}</td></tr>` +
                `<tr><td>US活跃</td><td>${activeNum}</td></tr>` +
                `<tr><td>US前日留存</td><td>${retainedRate}</td></tr>` +
                `<tr><td>US矿机连接成功率</td><td>${minerConnectRate}</td></tr>` +
                `<tr><td>US连接用户数</td><td>${connectUser}</td></tr>` +
                `<tr><td>US连接次数</td><td>${sessionCount}</td></tr>` +
                `<tr><td>US平均连接次数</td><td>${averageConnCount}</td></tr>` +
                `<tr><td>US虚拟分配成功率</td><td>${allocRate}</td></tr>` +
                `<tr><td>US数据获取成功率</td><td>${dataAccessRate}</td></tr>` +
                `<tr><td>矿机crash次数</td><td>${crashCount}</td></tr>` +
                `<tr><td>US矿机流量统计</td><td>上行: ${txMB} 下行: ${rxMB}</td></tr>` +
                `<tr><td>US用户平均使用流量</td><td>${averageFlow}</td></tr>` +
                `<tr><td>US平均速度</td><td>上行: ${txSpeed} 下行: ${rxSpeed}</td></tr>` +
                `<tr><td>US用户平均使用时长</td><td>${averageTime}</td></tr>` +
                `</tr>` +
                `</table>`

            let params = {
                "topic": "DailyOperation",
                "sender_name": "流计算每日简报-" + date,
                "title": "流计算每日简报-" + date,
                "content": out
            }

            console.log(params)

            request({
                url: `http://${config["maintainmail"]}/sendmessage`,
                method: 'POST',
                json: true,
                body: params
            }, (err, resp, data) => {
                if (err) {
                    console.log(err)
                } else {
                    logger.info("日报发送成功")
                }
            })
        } catch (err) {
            logger.info(err)
        }
    }
}