import { Inject, Provide } from '@midwayjs/decorator';
import { BaseService } from 'midwayjs-cool-core';
import { InjectEntityModel } from '@midwayjs/orm';
import { Repository } from 'typeorm';
import * as _ from 'lodash';
import { ShuyouDataActiveEntity } from '../entity/data_active';
import { Utils } from '../../../comm/utils';

/**
 * 游戏目标
 */
@Provide()
export class ShuyouDataActiveService extends BaseService {

  @InjectEntityModel(ShuyouDataActiveEntity)
  ShuyouDataActiveEntity: Repository<ShuyouDataActiveEntity>;

  @Inject()
  utils: Utils;

  /**
  * 重写分页查询
  * @param query
  */
  async page(query) {
    const { new_date, agentOrDirector } = query;
    let sql1 = ``
    let sql2 = ``
    let str_new_date = this.utils.formatDate(new Date(new_date[0])) + " - " + this.utils.formatDate(new Date(new_date[1]))
    // 
    if (agentOrDirector == 0) {
      sql1 = `SELECT
            '`+ str_new_date + `' as new_date,
              sum( target_new ) target_new,
              sum( target_pay ) target_pay,
              sum( target_active ) target_active,
              sum( sum_new ) total_new,
              sum( sum_pay ) total_pay,
              sum( sum_active ) total_active,
              game_director,
              game_agent
            FROM
              shuyou_data_active
            WHERE game_director = '合计' and game_agent = '合计'`;
      sql2 = ``
    } else if (agentOrDirector == 2) {
      sql1 = `SELECT
            '`+ str_new_date + `' as new_date,
              sum( target_new ) target_new,
              sum( target_pay ) target_pay,
              sum( target_active ) target_active,
              sum( sum_new ) total_new,
              sum( sum_pay ) total_pay,
              sum( sum_active ) total_active,
              game_director,
              any_value(game_agent) as game_agent
            FROM
              shuyou_data_active
            WHERE game_director != '合计'`;
      sql2 = ` GROUP BY game_director`
    } else {
      sql1 = `SELECT
            '`+ str_new_date + `' as new_date,
              sum( target_new ) target_new,
              sum( target_pay ) target_pay,
              sum( target_active ) target_active,
              sum( sum_new ) total_new,
              sum( sum_pay ) total_pay,
              sum( sum_active ) total_active,
              any_value(game_director) as game_director,
              game_agent
            FROM
              shuyou_data_active
            WHERE game_agent != '合计'`;
      sql2 = ` GROUP BY game_agent`
    }
    const sql = sql1 + `
        ${this.setSql(new_date, 'and new_date >= ? and new_date <= ?', [this.utils.formatDate(new Date(new_date[0])), this.utils.formatDate(new Date(new_date[1]))])}
        `+ sql2;
    // 
    return this.sqlRenderPage(sql, query);
  }


  // }
  // /**
  //    * 重载修改接口
  //    */
  // async update(param) {
  //   // console.log(param)
  //   param.is_write = 1
  //   const exists = await this.ShuyouReasonIncompleteEntity.findOne({
  //     task_id: param.task_id,
  //     new_date: param.new_date
  //   });
  //   param.createTime = new Date()
  //   param.updateTime = new Date()
  //   if (!_.isEmpty(exists)) {
  //     let addObj = {
  //       type_id: param.type_id,
  //       reason: param.reason,
  //       solution: param.solution,
  //       is_write: param.is_write
  //     }
  //     const updateResult = await this.ShuyouReasonIncompleteEntity
  //       .createQueryBuilder()
  //       .update()
  //       .set(addObj)
  //       .where('task_id = :task_id and new_date = :new_date', {
  //         task_id: param.task_id,
  //         new_date: param.new_date
  //       })
  //       .execute();
  //     if (updateResult.affected >= 1) {
  //       // throw new CoolCommException('修改成功~');
  //       console.log('修改成功~')
  //     } else {
  //       // throw new CoolCommException('修改成功~');
  //       console.log('修改失败~')
  //     }
  //   } else {
  //     throw new CoolCommException('未查询到数据，无法修改~');
  //   }


  // }
  /**
  * 查询日数据
  * @param null
  */
  async queryByDay(new_date) {
    const sql = `
          SELECT
            new_date,sum_new,sum_pay,sum_active
          FROM
          shuyou_data_active
          WHERE 1 = 1
          ${this.setSql(new_date, 'and new_date = ?', [new_date])}
        `;
    return await this.nativeQuery(sql);
  }

  /**
  * 查询月汇总数据
  */
  async queryByMonth() {
    const year = new Date().getFullYear()
    const sql = `
        SELECT
          MONTH(new_date) mon,
          sum(sum_new) total_new,
          sum(sum_pay) total_pay,
          sum(sum_active) total_active
        FROM
          shuyou_data_active
        WHERE
          game_director = '合计' and game_agent = '合计'
          ${this.setSql(year, 'and YEAR(new_date) = ?', [year])}
          GROUP BY mon
        `;
    return await this.nativeQuery(sql);
  }

  /**
  * 查询今日操作次数
  */
  async queryOperationCount() {
    const today = this.utils.formatDate(new Date().getTime()) + " 00:00:00"
    const next_day = this.utils.formatDate(new Date().getTime() + 24 * 60 * 60 * 1000) + " 00:00:00"
    const sql = `
        select 
          count(ip) AS Operation_count 
        from 
          base_sys_log 
        where 
          ${this.setSql(today, 'createTime >= ? and createTime <= ?', [today, next_day])}
        `;
    return await this.nativeQuery(sql);
  }
  /**
  * 查询今日访问IP
  */
  async queryIpCount() {
    const today = this.utils.formatDate(new Date().getTime()) + " 00:00:00"
    const next_day = this.utils.formatDate(new Date().getTime() + 24 * 60 * 60 * 1000) + " 00:00:00"
    const sql = `
        select 
          count(DISTINCT(ip)) AS ip_count
        from 
          base_sys_log 
        where 
          ${this.setSql(today, 'createTime >= ? and createTime <= ?', [today, next_day])}
        `;
    return await this.nativeQuery(sql);
  }

  /**
   *
   * 异常统计-根据负责人和未完成原因
   * @param {Array<Date>} new_date
   * @return {*} 
   * @memberof ShuyouDataActiveService
   */
  async queryAbnormalStatisticsByDirectorReason(new_date: Array<Date>) {
    const sql = `
          SELECT
            a.directorName,
            IFNULL(b.name,'未填写') as type_name,
            count( a.type_id ) game_count 
          FROM
            shuyou_reason_incomplete a
            LEFT JOIN shuyou_problem_type b ON a.type_id = b.id
          WHERE
            1 = 1 
            ${this.setSql(new_date, 'and a.new_date >= ? and a.new_date <= ?', [this.utils.formatDate(new Date(new_date[0])), this.utils.formatDate(new Date(new_date[1]))])}
          GROUP BY
            a.directorName,
            b.name
          `;
    return await this.nativeQuery(sql);
  }
  /**
   *  
   * @param {Array<Date>} new_date
   * @return {*} 
   * @memberof ShuyouDataActiveService
   */
  async queryGameCountByDirectorGameAgent(new_date: Array<Date>) {
    const sql = `
        SELECT
          c.name game_director,
          d.name game_agent,
          count(
          DISTINCT ( task_id )) game_count
        FROM
          shuyou_game_target a
          LEFT JOIN shuyou_game_task b ON a.task_id = b.id 
          LEFT JOIN shuyou_game_director c ON b.directorId = c.id 
          LEFT JOIN shuyou_game_agent d ON b.game_agentId = d.id 
        WHERE
          1 = 1
          ${this.setSql(new_date, 'and a.new_date >= ? and a.new_date <= ?', [this.utils.formatDate(new Date(new_date[0])), this.utils.formatDate(new Date(new_date[1]))])}
        GROUP BY
          b.directorId,d.id
          ORDER BY c.id
        `;
    return await this.nativeQuery(sql);
  }
  /**
   *  
   * @param {Array<Date>} new_date
   * @return {*} 
   * @memberof ShuyouDataActiveService
   */
  async queryGameCountByDirector(new_date: Array<Date>) {
    const sql = `
        SELECT
          c.NAME as game_director,
          sum( CASE a.complete_new WHEN 0 THEN 0 ELSE 1 END ) new_count,
          sum( CASE a.complete_pay WHEN 0 THEN 0 ELSE 1 END ) pay_count,
          sum( CASE a.complete_new WHEN 0 THEN 1 ELSE 0 END ) lc_count,
          count(a.task_id ) active_count,
          count(DISTINCT(a.task_id)) game_count
        FROM
          shuyou_game_complete a
          LEFT JOIN shuyou_game_target b ON a.task_id = b.task_id and a.new_date = b.new_date
          LEFT JOIN shuyou_game_director c ON b.directorId = c.id 
        WHERE
          1 = 1
          AND b.directorId > 0
          ${this.setSql(new_date, 'and a.new_date >= ? and a.new_date <= ?', [this.utils.formatDate(new Date(new_date[0])), this.utils.formatDate(new Date(new_date[1]))])}
        GROUP BY
          b.directorId
        ORDER BY b.directorId
        `;
    return await this.nativeQuery(sql);
  }
  /**
   *  
   * @param {Array<Date>} new_date
   * @return {*} 
   * @memberof ShuyouDataActiveService
   */
  async queryGameCountByDirectorReal(new_date: Array<Date>) {
    // count(DISTINCT(a.task_id)) '按渠道分',
    //   count(DISTINCT l.game_name, l.game_agentId) '按端口分',
    //     count(DISTINCT(l.game_name)) '按游戏分'
    const sql = `
        SELECT
          c.NAME AS game_director,
          l.game_name,
          count(DISTINCT ( a.task_id )) count_qudao,
          count(DISTINCT l.game_name, l.game_agentId) count_duankou,
          count(DISTINCT (l.game_name)) count_game
        FROM
          shuyou_game_complete a
          LEFT JOIN shuyou_game_target b ON a.task_id = b.task_id 
          AND a.new_date = b.new_date
          LEFT JOIN shuyou_game_director c ON b.directorId = c.id
          LEFT JOIN task_game_record r ON r.task_id = a.task_id
          LEFT JOIN shuyou_game_list l ON l.id = r.game_id 
        WHERE
          1 = 1 
          AND b.directorId > 0 
          ${this.setSql(new_date, 'and a.new_date >= ? and a.new_date <= ?', [this.utils.formatDate(new Date(new_date[0])), this.utils.formatDate(new Date(new_date[1]))])}
        GROUP BY
          b.directorId,l.game_name
        ORDER 
          BY b.directorId
        `;
    return await this.nativeQuery(sql);
  }
  /**
   *  
   * @param {Array<Date>} new_date
   * @return {*} 
   * @memberof ShuyouDataActiveService
   */
  async queryGameCountByGameAgent(new_date: Array<Date>) {
    const sql = `
        SELECT
          c.NAME as game_agent,
          sum( CASE a.target_new WHEN 0 THEN 0 ELSE 1 END ) target_new_count,
          sum( CASE a.target_pay WHEN 0 THEN 0 ELSE 1 END ) target_pay_count,
          sum( CASE a.target_new WHEN 0 THEN 1 ELSE 0 END ) target_lc_count,
          count(DISTINCT(a.task_id)) target_active_count,
          sum( CASE b.complete_new WHEN 0 THEN 0 ELSE 1 END ) complete_new_count,
          sum( CASE b.complete_pay WHEN 0 THEN 0 ELSE 1 END ) complete_pay_count,
          sum( CASE b.complete_new WHEN 0 THEN 1 ELSE 0 END ) complete_lc_count,
          count(DISTINCT(b.task_id)) complete_active_count
        FROM
          shuyou_game_target a
          LEFT JOIN shuyou_game_task s ON a.task_id = s.id
          LEFT JOIN shuyou_game_complete b ON a.task_id = b.task_id and a.new_date = b.new_date
          LEFT JOIN shuyou_game_agent c ON c.id = s.game_agentId
        WHERE
          1 = 1
          ${this.setSql(new_date, 'and a.new_date >= ? and a.new_date <= ?', [this.utils.formatDate(new Date(new_date[0])), this.utils.formatDate(new Date(new_date[1]))])}
        GROUP BY
          c.id
        `;
    return await this.nativeQuery(sql);
  }
  /**
    * 查询queryTopFive
    */
  async queryTopFive() {
    const today = this.utils.formatDate(new Date())
    const sql = `
        select 
          (@i:=@i+1) AS 'index',
          task_id,task_name,complete_pay,complete_active 
        from 
          shuyou_game_complete,(SELECT @i:=0) AS itable
        where 1 = 1
          ${this.setSql(today, 'and new_date = ?', [today])}
        ORDER BY 
          complete_pay desc limit 9
        `;
    return await this.nativeQuery(sql);
  }
}
