# -*- 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.system.log.clean'
    _description = u'日志清理'

    @api.model
    def jd_system_log_clean(self):
        log_login_days_maintain = self.env['ir.config_parameter'].jdg_sudo().get_param("log_login_days_maintain")
        log_login_days_maintain = int(log_login_days_maintain) if log_login_days_maintain else 0
        self.jd_system_log_clean_login(log_login_days_maintain)

        log_biz_days_maintain = self.env['ir.config_parameter'].jdg_sudo().get_param("log_biz_days_maintain")
        log_biz_days_maintain = int(log_biz_days_maintain) if log_biz_days_maintain else 0
        self.jd_system_log_clean_biz(log_biz_days_maintain)

        log_access_days_maintain = self.env['ir.config_parameter'].jdg_sudo().get_param("log_access_days_maintain")
        log_access_days_maintain = int(log_access_days_maintain) if log_access_days_maintain else 0
        self.jd_system_log_clean_access(log_access_days_maintain)
    
    @api.model
    def jd_system_log_clean_login(self, log_login_days_maintain):
        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_system_log_login 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=log_login_days_maintain), ))
        recs = cr.dictfetchall()
        ids = []
        filename = 'log_login_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_system_log_login WHERE id = ANY(%s);
            """
            cr.execute(sql, (ids, ))

    @api.model
    def jd_system_log_clean_biz(self, log_biz_days_maintain):
        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.source_id,
            log.model_id,
            im.name             AS model_name,
            ll.field,
            ll.field_cn,
            ll.value_before,
            ll.value_after
          FROM jd_system_log_biz 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_system_log_biz_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=log_biz_days_maintain), ))
        recs = cr.dictfetchall()
        ids = []
        filename = 'log_biz_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'单据编码'),
            ('source_id', u'单据ID'),
            ('model_id', u'单据模型'),
            ('model_name', 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_system_log_biz_line WHERE parent_id = ANY(%(log_ids)s);
              DELETE FROM jd_system_log_biz WHERE id = ANY(%(log_ids)s);
            """
            cr.execute(sql, {
                'log_ids': ids
            })
    
    @api.model
    def jd_system_log_clean_access(self, log_access_days_maintain):
        cr = self.env.cr
        sql = """
          SELECT
            log.id,
            log.user_id,
            usr.login,
            log.time_access,
            im.name             AS model_name,
            ia.name             AS action_name
          FROM jd_system_log_access log
            LEFT JOIN res_users usr ON usr.id = log.user_id
            LEFT JOIN ir_actions ia ON ia.id = log.action_id
            LEFT JOIN ir_model im ON im.id = log.model_id
          WHERE log.time_access < %s;
        """
        cr.execute(sql, (datetime.datetime.now() - datetime.timedelta(days=log_access_days_maintain), ))
        recs = cr.dictfetchall()
        ids = []
        filename = 'log_access_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_access', u'访问时间'),
            ('model_name', u'访问单据'),
            ('action_name', 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_system_log_access WHERE id = ANY(%s);
            """
            cr.execute(sql, (ids, ))
