# -*- coding: utf-8 -*-
# 项目: jdg-core-base
# Copyright 2018 JDG <www.yunside.com>
# Created by HJH <jiahao.huang@yunside.com> at 2022/05/25

from odoo import models, fields, api
from odoo.tools import config
import datetime
import logging
import re
import xlwt

_logger = logging.getLogger(__name__)


class JdLogClean(models.Model):
    _name = 'jd.log.clean'
    _description = u'日志清理'

    @api.model
    def jd_log_clean(self):
        login_log_maintain_days = self.env['ir.config_parameter'].jdg_sudo().get_param("login_log_maintain_days")
        login_log_maintain_days = int(login_log_maintain_days) if login_log_maintain_days else 0
        self.jd_clean_login_log(login_log_maintain_days)

        biz_log_maintain_days = self.env['ir.config_parameter'].jdg_sudo().get_param("biz_log_maintain_days")
        biz_log_maintain_days = int(biz_log_maintain_days) if biz_log_maintain_days else 0
        self.jd_clean_biz_log(biz_log_maintain_days)
    
    @api.model
    def jd_clean_login_log(self, login_log_maintain_days):
        cr = self.env.cr
        sql = """
          SELECT
            log.id,
            log.user_id,
            usr.login,
            log.time_login,
            CASE WHEN log.result = 'success' THEN '登录成功'
                 WHEN log.result = 'fail'    THEN '登录失败'
            END AS result,
            log.ip,
            log.country,
            log.city
          FROM jd_login_log log
            LEFT JOIN res_users usr ON usr.id = log.user_id
          WHERE log.time_login < %s;
        """
        cr.execute(sql, (datetime.datetime.now() - datetime.timedelta(days=login_log_maintain_days), ))
        recs = cr.dictfetchall()
        ids = []
        filename = 'login_log_local_save_%s.xls' % (datetime.datetime.strftime(datetime.datetime.now() + datetime.timedelta(hours=8), '%Y%m%d'))
        workbook = xlwt.Workbook(encoding='utf-8')
        title_sum = [
            ('user_id', u'用户ID'),
            ('login', u'用户账号'),
            ('time_login', u'登录时间'),
            ('result', u'登录结果'),
            ('ip', u'登录ip'),
            ('country', u'国家'),
            ('city', u'城市'),
        ]

        n, sheet = 60001, 0
        rows_lists = [recs[i:i+n] for i in range(0, len(recs), n)] or [[]]
        for rows_list in rows_lists:
            sheet += 1
            worksheet = workbook.add_sheet('Sheet %s' % sheet)

            for i in range(0, len(title_sum)):
                worksheet.write(0, i, title_sum[i][1])
                worksheet.col(i).width = 8000
            
            base_style = xlwt.easyxf('align: wrap yes')
            date_style = xlwt.easyxf('align: wrap yes', num_format_str='YYYY-MM-DD')
            datetime_style = xlwt.easyxf('align: wrap yes', num_format_str='YYYY-MM-DD HH:mm:SS')

            for r in range(0, len(rows_list)):
                ids.append(rows_list[r].get('id'))
                for vol in range(0, len(title_sum)):
                    data = rows_list[r].get(title_sum[vol][0])
                    cell_style = base_style
                    if isinstance(data, basestring):
                        data = re.sub("\r", " ", data)
                    elif isinstance(data, datetime.datetime):
                        cell_style = datetime_style
                    elif isinstance(data, datetime.date):
                        cell_style = date_style
                    worksheet.write(r + 1, vol, data, cell_style)
        
        save_route = config.get('log_route', '/log/')
        workbook.save(save_route + filename)

        if ids:
            sql = """
              DELETE FROM jd_login_log WHERE id = ANY(%s);
            """
            cr.execute(sql, (ids, ))

    @api.model
    def jd_clean_biz_log(self, biz_log_maintain_days):
        cr = self.env.cr
        sql = """
          SELECT
            log.id,
            log.company_id,
            com.name,
            log.user_id,
            usr.login,
            log.time_op,
            CASE WHEN log.action = 'create'     THEN '创建'
                 WHEN log.action = 'write'      THEN '修改'
                 WHEN log.action = 'audit'      THEN '审核'
                 WHEN log.action = 'unaudit'    THEN '反审核'
                 WHEN log.action = 'cancel'     THEN '作废'
                 WHEN log.action = 'unlink'     THEN '删除'
            END AS action,
            log.source_number,
            log.model_id,
            im.model,
            ll.field,
            ll.field_cn,
            ll.value_before,
            ll.value_after
          FROM jd_biz_log log
            LEFT JOIN res_company com ON com.id = log.company_id
            LEFT JOIN res_users usr ON usr.id = log.user_id
            LEFT JOIN jd_biz_log_line ll ON ll.parent_id = log.id
            LEFT JOIN ir_model im ON im.id = log.model_id
          WHERE log.time_op < %s;
        """
        cr.execute(sql, (datetime.datetime.now() - datetime.timedelta(days=biz_log_maintain_days), ))
        recs = cr.dictfetchall()
        ids = []
        filename = 'biz_log_local_save_%s.xls' % (datetime.datetime.strftime(datetime.datetime.now() + datetime.timedelta(hours=8), '%Y%m%d'))
        workbook = xlwt.Workbook(encoding='utf-8')
        title_sum = [
            ('company_id', u'组织ID'),
            ('name', u'组织'),
            ('user_id', u'用户ID'),
            ('login', u'用户账号'),
            ('time_op', u'操作时间'),
            ('action', u'操作'),
            ('source_number', u'单据编码'),
            ('model_id', u'单据模型'),
            ('model', u'单据类型'),
            ('field', u'字段名'),
            ('field_cn', u'字段中文'),
            ('value_before', u'字段原值'),
            ('value_after', u'修改后值'),
        ]

        n, sheet = 60001, 0
        rows_lists = [recs[i:i+n] for i in range(0, len(recs), n)] or [[]]
        for rows_list in rows_lists:
            sheet += 1
            worksheet = workbook.add_sheet('Sheet %s' % sheet)

            for i in range(0, len(title_sum)):
                worksheet.write(0, i, title_sum[i][1])
                worksheet.col(i).width = 8000
            
            base_style = xlwt.easyxf('align: wrap yes')
            date_style = xlwt.easyxf('align: wrap yes', num_format_str='YYYY-MM-DD')
            datetime_style = xlwt.easyxf('align: wrap yes', num_format_str='YYYY-MM-DD HH:mm:SS')

            for r in range(0, len(rows_list)):
                ids.append(rows_list[r].get('id'))
                for vol in range(0, len(title_sum)):
                    data = rows_list[r].get(title_sum[vol][0])
                    cell_style = base_style
                    if isinstance(data, basestring):
                        data = re.sub("\r", " ", data)
                    elif isinstance(data, datetime.datetime):
                        cell_style = datetime_style
                    elif isinstance(data, datetime.date):
                        cell_style = date_style
                    worksheet.write(r + 1, vol, data, cell_style)
        
        save_route = config.get('log_route', '/log/')
        workbook.save(save_route + filename)

        if ids:
            sql = """
              DELETE FROM jd_biz_log_line WHERE parent_id = ANY(%(log_ids)s);
              DELETE FROM jd_biz_log WHERE id = ANY(%(log_ids)s);
            """
            cr.execute(sql, {
                'log_ids': ids
            })
