# -*- coding:utf-8 -*-
# 项目: jdg-core-base
# Copyright 2018 JDG <www.yunside.com>
# Created by bjccdsrlcr (longjie.jiang@yunside.com) @ 2018/7/17
import logging

from odoo import models, fields, api, SUPERUSER_ID
from odoo.osv import expression
from odoo.exceptions import ValidationError

_logger = logging.getLogger(__name__)

import json


class JdBaseDepartment(models.Model):
    _name = 'jd_base.department'
    _inherit = 'jdg.abstract.base.data'
    _description = u'角色组'

    name = fields.Char(u'名称', index=True, required=False, copy=True, default="请输入名称")
    number = fields.Char(string=u'编号', required=False, index=True, copy=False, default='/', readonly=True)
    company_id = fields.Many2one('res.company', string=u'组织',
                                 index=True, default=False, readonly=False, copy=False)
    leader_id = fields.Many2one('res.users', string=u'组长',
                                domain=lambda self: [('company_id', '=', self.env.user.company_id.id)], copy=False)
    group_ids = fields.Many2many('res.groups',
                                 'jd_dep_group_ref',
                                 'dep_id',
                                 'group_id',
                                 string=u'权限组', copy=True)
    user_ids = fields.Many2many('res.users',
                                'jd_dep_user_ref',
                                'dep_id',
                                'user_id',
                                string=u'用户', domain=lambda self: [('company_id', '=', self.env.user.company_id.id)],
                                copy=False)
    type_id = fields.Many2one('jd.department.type', string=u'角色组类型', required=True)

    domain_user_ids = fields.Char(string=u'user_domain', compute='_get_domain_user_ids',
                                  default=json.dumps("[('id', '<', 0)]"))

    _sql_constraints = [
        ('number_unique', 'unique(number, company_id)', '角色组有重复的编码!'),
        # ('name_unique', 'unique(name)', '部门有重复的名称!'),
    ]
    
    @api.multi
    def company_change(self):
        item = self[0]
        return {
            'type': 'ir.actions.act_window',
            'model': 'ir.actions.act_window',
            'name': u'角色组用户组织变更',
            'res_model': 'jd.base.wizard.department.company.change',
            'view_type': 'form',
            'view_mode': 'form',
            "view_id": False,
            'target': 'new',
            'context': {'department_id': item.id}
        }

    @api.model
    def name_search(self, name, args=None, operator='ilike', limit=100):
        args = args or []
        domain = []
        if name:
            domain = ['|', ('code', '=ilike', name + '%'), ('name', operator, name)]
            if operator in expression.NEGATIVE_TERM_OPERATORS:
                domain = ['&', '!'] + domain[1:]
        ids = self.search(domain + args, limit=limit)
        return ids.name_get()

    @api.depends('company_id')
    def _get_domain_user_ids(self):
        """
        返回用户domain
        :return:
        """
        for item in self:
            if item.company_id:
                domain = [('company_id', '=', item.company_id.id)]
            else:
                domain = []
            item.domain_user_ids = json.dumps(domain)

    @api.multi
    def name_get(self):
        result = []
        for i in self:
            if i.company_id and i.name:
                result.append((i.id, "[%s]%s" % (i.company_id.name, i.name)))
            elif i.name:
                result.append((i.id, "%s" % (i.name,)))
        return result

    @api.multi
    def write(self, values):
        old_user_ids = []
        for item in self:
            old_sql = '''
                select user_id from jd_dep_user_ref where dep_id = %s
            '''
            self.env.cr.execute(old_sql, (item.id,))
            old_user_ids = [r['user_id'] for r in self.env.cr.dictfetchall()]
        result = super(JdBaseDepartment, self).write(values)
        new_user_ids = []
        for item in self:
            new_sql = '''
                select user_id from jd_dep_user_ref where dep_id = %s
            '''
            self.env.cr.execute(new_sql, (item.id,))
            new_user_ids = [r['user_id'] for r in self.env.cr.dictfetchall()]
            if item.leader_id.id:
                if item.leader_id.id not in new_user_ids:
                    raise ValidationError('组长必须在用户列表中')
        base_group = self.env['ir.model.data'].get_object('base', 'group_user')
        self.reset_user_groups(old_user_ids, new_user_ids, [base_group.id])
        return result

    @api.model
    def create(self, values):
        if values.get('number', '/') == '/':
            values.update({
                'number': self.env['ir.sequence'].next_by_code(self._name)
            })
        result = super(JdBaseDepartment, self).create(values)
        if result.leader_id:
            sql = '''
                    insert into jd_dep_user_ref(dep_id,user_id) values(%s,%s)
                '''
            self.env.cr.execute(sql, (result.id, result.leader_id.id))
        new_user_ids = [u.id for u in result.user_ids]
        base_group = self.env['ir.model.data'].get_object('base', 'group_user')
        self.reset_user_groups([], new_user_ids, [base_group.id])
        return result

    @api.multi
    def unlink(self):
        """
        删除角色组时要删除所有用户对应的权限
        :return:
        """
        for record in self:
            all_user_ids = record.user_ids.ids
            if record.leader_id:
                all_user_ids.append(record.leader_id.id)
            if all_user_ids:
                for uid in all_user_ids:
                    if uid != SUPERUSER_ID:
                        new_groups = record.group_ids.ids
                        removed_gid_list = tuple(new_groups)
                        if new_groups:
                            sql = 'delete from res_groups_users_rel where gid in %s and uid=%s'
                            self.env.cr.execute(sql, (removed_gid_list, uid))
        result = super(JdBaseDepartment, self).unlink()
        return result

    def reset_user_groups(self, old_user_ids, new_user_ids, init_group_ids):
        '''
        重设用户权限
        :param old_user_ids: 部门下的旧用户
        :param new_user_ids: 部门下的新用户
        :param init_group_ids: 必须的分组ID，下面的分组，不能删除
        :return:
        '''
        all_user_ids = old_user_ids + new_user_ids
        for uid in all_user_ids:
            if uid != SUPERUSER_ID:
                self._reset_user_groups(uid, init_group_ids)

    @api.model
    def _reset_user_groups(self, user_id, init_group_ids):
        '''
        重新设置用户权限
        :param user_id:
        :return:
        '''
        old_groups = self._get_user_old_groups(user_id)
        new_groups = self._get_user_new_groups(user_id)
        removed_groups = set(old_groups) - set(new_groups) - set(init_group_ids)
        removed_gid_list = tuple(removed_groups)
        #### 生成权限变化记录
        minus_groups = [i for i in old_groups if i not in new_groups]
        add_groups = [i for i in new_groups if i not in old_groups]
        g_line = []
        if minus_groups:
            for g in minus_groups:
                g_line.append((0, 0, {
                    'type': 'less',
                    'group_id': g
                }))
        if add_groups:
            for g in add_groups:
                g_line.append((0, 0, {
                    'type': 'more',
                    'group_id': g
                }))
        if g_line:
            h_val = {
                'user_id': user_id,
                'src_id': u'角色组',
                'src_number': self.number,
                'operator_id': self.env.user.id,
                'line_ids': g_line,
                'note': u'由角色组单据改变了该用户的权限'
            }
            self.env['jd.groups.history'].create(h_val)
        if removed_gid_list:
            sql = 'delete from res_groups_users_rel where gid in %s and uid=%s'
            self.env.cr.execute(sql, (removed_gid_list, user_id))
        if new_groups:
            sql = 'insert into res_groups_users_rel(gid,uid) values(%s,%s)'
            for gid in new_groups:
                self.env.cr.execute('select count(*) c from res_groups_users_rel where gid=%s and uid=%s',
                                    (gid, user_id))
                if self.env.cr.dictfetchall()[0]['c'] <= 0:
                    self.env.cr.execute(sql, (gid, user_id))

    @api.model
    def _get_user_old_groups(self, user_id):
        '''
        获取用户所有旧的分组列表
        :param user_id:
        :return:
        '''
        sql = '''
        select gid from res_groups_users_rel where uid=%s
        '''
        self.env.cr.execute(sql, (user_id,))
        g_ids = [item['gid'] for item in self.env.cr.dictfetchall()]
        return g_ids

    @api.model
    def _get_user_new_groups(self, user_id):
        '''
        获取用户所有新的分组列表
        :param user_id:
        :return:
        '''
        group_ids = []
        sql = '''select dep_id,user_id from jd_dep_user_ref where user_id = %s'''
        self.env.cr.execute(sql, (user_id,))
        dep_ids = [item['dep_id'] for item in self.env.cr.dictfetchall()]
        if not dep_ids:
            return group_ids
        sql = '''select group_id from jd_dep_group_ref where dep_id in %s'''
        self.env.cr.execute(sql, (tuple(dep_ids),))
        group_ids = [item['group_id'] for item in self.env.cr.dictfetchall()]
        return list(set(group_ids))
    
    @api.model
    def get_user_type_list(self):
        type_list = []
        uid = self.env.user.id
        dep_rec_list = self.sudo().search([('users_ids', '=', uid)])
        for dep_rec in dep_rec_list:
            dep_number = dep_rec.type_id.number
            if dep_number not in type_list:
                type_list.append(dep_number)
        return type_list


class IrGroups(models.Model):
    _inherit = 'res.groups'

    dep_ids = fields.Many2many('jd_base.department',
                               'jd_dep_group_ref',
                               'group_id',
                               'dep_id',
                               string=u'部门')

    menu_ids = fields.Many2many('jd.ir.ui.menu.config',
                                'jd_menu_config_base_res_group',
                                'left_id',
                                'right_id',
                                string=u'关联菜单表配置'
                                )
    button_access_ids = fields.One2many('jd.button.access', 'group_id', string=u'按钮访问权')

    @api.multi
    def create_model_acl_by_menu_ids(self):
        """
         menu_config_ids 为选择的所有菜单项的记录集
        :return:
        """
        self.ensure_one()
        item = self[0]
        cr = self.env.cr
        group_id = item.id
        menu_config_ids = item.menu_ids

        delete_acl_sql = '''
            DELETE FROM ir_model_access WHERE group_id = %s
            '''
        cr.execute(delete_acl_sql, (item.id,))
        delete_bcl_sql = '''
            DELETE FROM jd_button_access WHERE group_id = %s
            '''
        cr.execute(delete_bcl_sql, (item.id,))

        # 1、根据关联菜单项配置，插入菜单直接关联模型的权限
        sql = """
        DO $$
        DECLARE
          rec          RECORD;
          count_access INT;
        BEGIN
          -- 根据关联菜单项配置，插入菜单直接关联模型的权限
          FOR rec IN (
            SELECT
              coalesce(mu.model_name, '无名称') AS model_name,
              mu.model_id                    AS model_id,
              CASE WHEN
                TRUE = ANY (mu.perm_read)
                THEN TRUE
              ELSE FALSE
              END                            AS perm_read,
              CASE WHEN
                TRUE = ANY (mu.perm_write)
                THEN TRUE
              ELSE FALSE
              END                            AS perm_write,
              CASE WHEN
                TRUE = ANY (mu.perm_create)
                THEN TRUE
              ELSE FALSE
              END                            AS perm_create,
              CASE WHEN
                TRUE = ANY (mu.perm_unlink)
                THEN TRUE
              ELSE FALSE
              END                            AS perm_unlink,
              CASE WHEN
                TRUE = ANY (mu.perm_audit)
                THEN TRUE
              ELSE FALSE
              END                            AS perm_audit,
              CASE WHEN
                TRUE = ANY (mu.perm_unaudit)
                THEN TRUE
              ELSE FALSE
              END                            AS perm_unaudit,
              CASE WHEN
                TRUE = ANY (mu.perm_export)
                THEN TRUE
              ELSE FALSE
              END                            AS perm_export
            FROM
              (
                SELECT
                  DISTINCT ON (md.model_id)
                  md.model_id                         AS model_id,
                  md.model_name                       AS model_name,
                  array_agg(DISTINCT mc.perm_read)    AS perm_read,
                  array_agg(DISTINCT mc.perm_write)   AS perm_write,
                  array_agg(DISTINCT mc.perm_create)  AS perm_create,
                  array_agg(DISTINCT mc.perm_unlink)  AS perm_unlink,
                  array_agg(DISTINCT mc.perm_audit)   AS perm_audit,
                  array_agg(DISTINCT mc.perm_unaudit) AS perm_unaudit,
                  array_agg(DISTINCT mc.perm_export)  AS perm_export
                FROM res_groups rg
                  INNER JOIN jd_menu_config_base_res_group rel ON rel.left_id = rg.id
                  INNER JOIN jd_ir_ui_menu_config mc ON mc.id = rel.right_id
                  INNER JOIN ir_ui_menu mu ON mu.id = mc.menu_id
                  INNER JOIN
                  (
                    -- 计算每个菜单关联的模型
                    SELECT
                      mu.id   AS menu_id,
                      md.id   AS model_id,
                      md.name AS model_name
                    FROM ir_ui_menu mu
                      INNER JOIN (SELECT
                                    id,
                                    res_model
                                  FROM ir_act_window) act ON act.id = substring(mu.action, '\d+') :: INTEGER
                      INNER JOIN (SELECT
                                    id,
                                    model,
                                    name
                                  FROM ir_model) md ON md.model = act.res_model
                    WHERE mu.action IS NOT NULL
                  ) md ON md.menu_id = mu.id
                WHERE rg.id = %(group_id)s
                GROUP BY md.model_id, md.model_name
                ORDER BY md.model_id
              ) mu
          ) LOOP
            -- 查找当前群组是否已有此模型的访问权
            SELECT count(1)
            INTO count_access
            FROM ir_model_access ma
            WHERE ma.group_id = %(group_id)s
                  AND ma.model_id = rec.model_id;

            IF count_access > 0
            THEN
              UPDATE ir_model_access ma
              SET
                active       = TRUE,
                name         = rec.model_name,
                perm_read    = coalesce(rec.perm_read, perm_read),
                perm_write   = coalesce(rec.perm_write, perm_write),
                perm_create  = coalesce(rec.perm_create, perm_create),
                perm_unlink  = coalesce(rec.perm_unlink, perm_unlink),
                perm_audit   = coalesce(rec.perm_audit, perm_audit),
                perm_unaudit = coalesce(rec.perm_unaudit, perm_unaudit),
                perm_export  = coalesce(rec.perm_export, perm_export)
              WHERE ma.group_id = %(group_id)s
                    AND ma.model_id = rec.model_id;
            ELSE
              INSERT INTO ir_model_access
              (name, model_id, group_id, active, perm_read,
               perm_write, perm_unlink, perm_create, perm_audit, perm_unaudit, perm_export)
              VALUES
                (rec.model_name, rec.model_id, %(group_id)s, TRUE, rec.perm_read,
                 rec.perm_write, rec.perm_unlink, rec.perm_create, rec.perm_audit, rec.perm_unaudit, rec.perm_export);
            END IF;
          END LOOP;
        END;
        $$
        """
        params = {
            'group_id': group_id
        }
        cr.execute(sql, params)

        # 2、根据关联菜单项配置，插入菜单模型涉及到的 many2one、many2many、one2many 模型的 读 权限
        sql = """
        DO $$
        DECLARE
          rec          RECORD;
          count_access INT;
        BEGIN
          -- 根据关联菜单项配置，插入菜单模型涉及到的 many2one、many2many、one2many 模型的 读 权限
          FOR rec IN (
            SELECT DISTINCT
              md.model_id AS model_id,
              mf.relation AS relation,
              md.name     AS model_name
            FROM ir_model_fields mf
              INNER JOIN (
                           SELECT
                             md.id    AS model_id,
                             md.model AS model,
                             md.name  AS name
                           FROM ir_model md
                         ) md ON md.model = mf.relation
            WHERE mf.model_id = ANY (array(SELECT
                                             DISTINCT md.model_id AS model_id
                                           FROM res_groups rg
                                             INNER JOIN jd_menu_config_base_res_group rel ON rel.left_id = rg.id
                                             INNER JOIN jd_ir_ui_menu_config mc ON mc.id = rel.right_id
                                             INNER JOIN ir_ui_menu mu ON mu.id = mc.menu_id
                                             INNER JOIN (
                                                          -- 计算每个菜单关联的模型
                                                          SELECT
                                                            mu.id   AS menu_id,
                                                            md.id   AS model_id,
                                                            md.name AS model_name
                                                          FROM ir_ui_menu mu
                                                            INNER JOIN (SELECT
                                                                          id,
                                                                          res_model
                                                                        FROM ir_act_window) act
                                                              ON act.id = substring(mu.action, '\d+') :: INTEGER
                                                            INNER JOIN (SELECT
                                                                          id,
                                                                          model,
                                                                          name
                                                                        FROM ir_model) md ON md.model = act.res_model
                                                          WHERE mu.action IS NOT NULL
                                                        ) md ON md.menu_id = mu.id
                                           WHERE rg.id = %(group_id)s
                                           ORDER BY md.model_id))
                  AND mf.ttype IN ('many2one', 'many2many', 'one2many')
                  AND left(mf.model, 2) <> 'ir'
                  AND left(mf.model, 3) <> 'web'
                  AND left(mf.model, 4) <> 'mail'
                  AND left(mf.model, 4) <> 'base'
                  AND left(mf.model, 6) <> 'report'
                  AND left(mf.model, 7) <> 'resource'
                  AND left(mf.model, 8) <> 'calendar'
                  AND left(mf.model, 8) <> 'workflow'
                  AND left(mf.model, 9) <> 'fetchmail'
          ) LOOP
            -- 查找当前群组是否已有此模型的访问权
            SELECT count(1)
            INTO count_access
            FROM ir_model_access ma
            WHERE ma.group_id = %(group_id)s
                  AND ma.model_id = rec.model_id;

            IF count_access > 0
            THEN
              UPDATE ir_model_access ma
              SET
                active       = TRUE,
                name         = rec.model_name,
                perm_read    = TRUE,
                perm_write   = coalesce(perm_write, FALSE),
                perm_create  = coalesce(perm_create, FALSE),
                perm_unlink  = coalesce(perm_unlink, FALSE),
                perm_audit   = coalesce(perm_audit, FALSE),
                perm_unaudit = coalesce(perm_unaudit, FALSE),
                perm_export  = coalesce(perm_export, FALSE)
              WHERE ma.group_id = %(group_id)s
                    AND ma.model_id = rec.model_id;
            ELSE
              INSERT INTO ir_model_access
              (name, model_id, group_id, active, perm_read,
               perm_write, perm_unlink, perm_create, perm_audit, perm_unaudit, perm_export)
              VALUES
                (rec.model_name, rec.model_id, %(group_id)s, TRUE, TRUE,
                 FALSE, FALSE, FALSE, FALSE, FALSE, FALSE);
            END IF;
          END LOOP;
        END;
        $$
        """
        cr.execute(sql, params)

        # 3、给关联菜单项配置涉及到的 one2many 模型赋权，权限项与表头模型的访问权一致
        sql = """
        SELECT DISTINCT
          md.model_id       AS relation_model_id,
          mf.relation       AS relation,
          md.name           AS model_name,
          mf.model          AS model,
          mf.model_id       AS model_id,
          mf.relation_field AS relation_field
        FROM ir_model_fields mf
          INNER JOIN (
                       SELECT
                         md.id    AS model_id,
                         md.model AS model,
                         md.name  AS name
                       FROM ir_model md
                     ) md ON md.model = mf.relation
        WHERE mf.model_id = ANY (array(SELECT
                                         DISTINCT md.model_id AS model_id
                                       FROM res_groups rg
                                         INNER JOIN jd_menu_config_base_res_group rel ON rel.left_id = rg.id
                                         INNER JOIN jd_ir_ui_menu_config mc ON mc.id = rel.right_id
                                         INNER JOIN ir_ui_menu mu ON mu.id = mc.menu_id
                                         INNER JOIN (
                                                      -- 计算每个菜单关联的模型
                                                      SELECT
                                                        mu.id   AS menu_id,
                                                        md.id   AS model_id,
                                                        md.name AS model_name
                                                      FROM ir_ui_menu mu
                                                        INNER JOIN (SELECT
                                                                      id,
                                                                      res_model
                                                                    FROM ir_act_window) act
                                                          ON act.id = substring(mu.action, '\d+') :: INTEGER
                                                        INNER JOIN (SELECT
                                                                      id,
                                                                      model,
                                                                      name
                                                                    FROM ir_model) md ON md.model = act.res_model
                                                      WHERE mu.action IS NOT NULL
                                                    ) md ON md.menu_id = mu.id
                                       WHERE rg.id = %(group_id)s
                                       ORDER BY md.model_id))
              AND mf.ttype IN ('one2many')
              AND left(mf.model, 2) <> 'ir'
              AND left(mf.model, 3) <> 'web'
              AND left(mf.model, 4) <> 'mail'
              AND left(mf.model, 4) <> 'base'
              AND left(mf.model, 6) <> 'report'
              AND left(mf.model, 7) <> 'resource'
              AND left(mf.model, 8) <> 'calendar'
              AND left(mf.model, 8) <> 'workflow'
              AND left(mf.model, 9) <> 'fetchmail'
              AND mf.relation_field IS NOT NULL
              AND mf.relation <> mf.model
        """
        cr.execute(sql, params)
        recs = cr.dictfetchall()
        if recs:
            for rec in recs:
                relation_model_id = rec.get('relation_model_id')  # 关联模型ID
                relation = rec.get('relation')  # 关联模型
                model_name = rec.get('model_name')  # 关联模型名称
                model = rec.get('model')  # 表头模型
                model_id = rec.get('model_id')  # 表头模型ID
                relation_field = rec.get('relation_field')  # 关联字段

                try:
                    ondelete = self.env[relation]._fields[relation_field].ondelete
                except Exception as e:
                    ondelete = 'set null'
                # 根据 关联字段的 ondelete 属性判断，如果是 cascade，则one2many字段强关联与表头的权限一致，否则只有读权限
                # 由于第2步已经给所有 one2many 字段模型的 读 权限，故此处只需要处理强关联的权限
                if ondelete == 'cascade':
                    sql = """
                    DO $$
                    DECLARE
                      v_relation_access_id INT;
                      v_perm_write         BOOLEAN;
                      v_perm_create        BOOLEAN;
                      v_perm_unlink        BOOLEAN;
                      v_perm_audit         BOOLEAN;
                      v_perm_unaudit       BOOLEAN;
                      v_perm_export        BOOLEAN;
                    BEGIN
                      -- 查找当前群组中，表头模型的访问权
                      SELECT DISTINCT ON (ma.model_id)
                        coalesce(ma.perm_write, FALSE),
                        coalesce(ma.perm_create, FALSE),
                        coalesce(ma.perm_unlink, FALSE),
                        coalesce(ma.perm_audit, FALSE),
                        coalesce(ma.perm_unaudit, FALSE),
                        coalesce(ma.perm_export, FALSE)
                      INTO
                        v_perm_write,
                        v_perm_create,
                        v_perm_unlink,
                        v_perm_audit,
                        v_perm_unaudit,
                        v_perm_export
                      FROM ir_model_access ma
                      WHERE ma.group_id = %(group_id)s
                            AND ma.model_id = %(model_id)s
                      ORDER BY ma.model_id, ma.id DESC;

                      -- 查找当前群组是否已有 one2many 字段关联模型的访问权
                      SELECT ma.id
                      INTO v_relation_access_id
                      FROM ir_model_access ma
                      WHERE ma.group_id = %(group_id)s
                            AND ma.model_id = %(relation_model_id)s
                      ORDER BY ma.id DESC
                      LIMIT 1;

                      IF v_relation_access_id IS NOT NULL
                      -- 如果有关联模型的访问权，且权限项为 FALSE 时，则更新为与表头模型一致的访问权
                      THEN
                        UPDATE ir_model_access ma
                        SET
                          active       = TRUE,
                          perm_read    = TRUE,
                          perm_write   = (CASE
                                          WHEN perm_write = TRUE
                                            THEN perm_write
                                          ELSE v_perm_write END),
                          perm_create  = (CASE
                                          WHEN perm_create = TRUE
                                            THEN perm_create
                                          ELSE v_perm_create END),
                          perm_unlink  = (CASE
                                          WHEN perm_unlink = TRUE
                                            THEN perm_unlink
                                          ELSE v_perm_unlink END),
                          perm_audit   = (CASE
                                          WHEN perm_audit = TRUE
                                            THEN perm_audit
                                          ELSE v_perm_audit END),
                          perm_unaudit = (CASE
                                          WHEN perm_unaudit = TRUE
                                            THEN perm_unaudit
                                          ELSE v_perm_unaudit END),
                          perm_export = (CASE
                                          WHEN perm_export = TRUE
                                            THEN perm_export
                                          ELSE v_perm_export END)
                        WHERE ma.id = v_relation_access_id;
                      ELSE

                        INSERT INTO ir_model_access
                        (name, model_id, group_id, active, perm_read,
                         perm_write, perm_unlink, perm_create, perm_audit, perm_unaudit, perm_export)
                        VALUES
                          (%(model_name)s, %(relation_model_id)s, %(group_id)s, TRUE, TRUE,
                           v_perm_write, v_perm_create, v_perm_unlink, v_perm_audit, v_perm_unaudit, v_perm_export);
                      END IF;
                    END;
                    $$;
                    """
                    params_access = {
                        'group_id': group_id,
                        'model_id': model_id,
                        'model_name': model_name,
                        'relation_model_id': relation_model_id
                    }
                    cr.execute(sql, params_access)
                self.env['wizard.jd.access.controls.config'].create_model_acl_by_model(group_id, relation)

        # 4、给关联菜单项配置涉及到的 reference 模型赋予 读 权限
        sql = """
        SELECT DISTINCT
          mf.model    AS model,
          mf.model_id AS model_id
        FROM ir_model_fields mf
        WHERE mf.model_id = ANY (array(SELECT
                                         DISTINCT md.model_id AS model_id
                                       FROM res_groups rg
                                         INNER JOIN jd_menu_config_base_res_group rel ON rel.left_id = rg.id
                                         INNER JOIN jd_ir_ui_menu_config mc ON mc.id = rel.right_id
                                         INNER JOIN ir_ui_menu mu ON mu.id = mc.menu_id
                                         INNER JOIN (
                                                      -- 计算每个菜单关联的模型
                                                      SELECT
                                                        mu.id   AS menu_id,
                                                        md.id   AS model_id,
                                                        md.name AS model_name
                                                      FROM ir_ui_menu mu
                                                        INNER JOIN (SELECT
                                                                      id,
                                                                      res_model
                                                                    FROM ir_act_window) act
                                                          ON act.id = substring(mu.action, '\d+') :: INTEGER
                                                        INNER JOIN (SELECT
                                                                      id,
                                                                      model,
                                                                      name
                                                                    FROM ir_model) md ON md.model = act.res_model
                                                      WHERE mu.action IS NOT NULL
                                                    ) md ON md.menu_id = mu.id
                                       WHERE rg.id = %(group_id)s
                                       ORDER BY md.model_id))
              AND mf.ttype IN ('reference')
              AND left(mf.model, 2) <> 'ir'
              AND left(mf.model, 3) <> 'web'
              AND left(mf.model, 4) <> 'mail'
              AND left(mf.model, 4) <> 'base'
              AND left(mf.model, 6) <> 'report'
              AND left(mf.model, 7) <> 'resource'
              AND left(mf.model, 8) <> 'calendar'
              AND left(mf.model, 8) <> 'workflow'
              AND left(mf.model, 9) <> 'fetchmail'
        """
        cr.execute(sql, params)
        recs = cr.dictfetchall()
        if recs:
            for rec in recs:
                model = rec.get('model')  # 表头模型
                model_id = rec.get('model_id')  # 表头模型ID
                # 获取 reference 字段详细关联的 模型
                model_list = self.env[model]._reference_models()
                if len(model_list) > 20:
                    continue
                    # raise ValidationError(u'生成访问权时发现模型【%s】包含reference字段，'
                    #                       u'且该字段未指定具体的模型范围，请联系开发人员检查。'
                    #                       % model)
                for item in model_list:
                    relation_model = item[0]
                    sql = """
                    DO $$
                    DECLARE
                      v_relation_model_name VARCHAR;
                      v_relation_model_id   INT;
                      v_relation_access_id  INT;
                    BEGIN
                      SELECT
                        md.id,
                        md.name
                      INTO
                        v_relation_model_id,
                        v_relation_model_name
                      FROM ir_model md
                      WHERE md.model = %(relation_model)s;

                      -- 查找当前群组是否已有 指定模型的访问权
                      SELECT ma.id
                      INTO v_relation_access_id
                      FROM ir_model_access ma
                      WHERE ma.group_id = %(group_id)s
                            AND ma.model_id = v_relation_model_id
                      ORDER BY ma.id DESC
                      LIMIT 1;

                      IF v_relation_access_id IS NOT NULL
                      -- 如果有关联模型的访问权，且权限项为 FALSE 时，则更新为与表头模型一致的访问权
                      THEN
                        UPDATE ir_model_access ma
                        SET
                          active    = TRUE,
                          perm_read = TRUE
                        WHERE ma.id = v_relation_access_id;
                      ELSE

                        INSERT INTO ir_model_access
                        (name, model_id, group_id, active, perm_read,
                         perm_write, perm_unlink, perm_create, perm_audit, perm_unaudit, perm_export)
                        VALUES
                          (v_relation_model_name, v_relation_model_id, %(group_id)s, TRUE, TRUE,
                           FALSE, FALSE, FALSE, FALSE, FALSE, FALSE);
                      END IF;
                    END;
                    $$;
                    """
                    params_access = {
                        'group_id': group_id,
                        'relation_model': relation_model
                    }
                    cr.execute(sql, params_access)

        # 5、如果是瞬态模型，说明是报表，需指定菜单；如果有inherits属性，则添加代理继承的所有模型访问权
        sql = """
        SELECT
          mu.model_id AS model_id,
          mu.model    AS model,
          mu.menu_ids AS menu_ids,
          CASE WHEN
            TRUE = ANY (mu.perm_read)
            THEN TRUE
          ELSE FALSE
          END         AS perm_read,
          CASE WHEN
            TRUE = ANY (mu.perm_write)
            THEN TRUE
          ELSE FALSE
          END         AS perm_write,
          CASE WHEN
            TRUE = ANY (mu.perm_create)
            THEN TRUE
          ELSE FALSE
          END         AS perm_create,
          CASE WHEN
            TRUE = ANY (mu.perm_unlink)
            THEN TRUE
          ELSE FALSE
          END         AS perm_unlink,
          CASE WHEN
            TRUE = ANY (mu.perm_audit)
            THEN TRUE
          ELSE FALSE
          END         AS perm_audit,
          CASE WHEN
            TRUE = ANY (mu.perm_unaudit)
            THEN TRUE
          ELSE FALSE
          END         AS perm_unaudit,
          CASE WHEN
            TRUE = ANY (mu.perm_export)
            THEN TRUE
          ELSE FALSE
          END         AS perm_export,
          CASE WHEN
            TRUE = ANY (mu.perm_confirm)
            THEN TRUE
          ELSE FALSE
          END         AS perm_confirm,
          CASE WHEN
            TRUE = ANY (mu.perm_cancel)
            THEN TRUE
          ELSE FALSE
          END         AS perm_cancel
        FROM
          (
            SELECT
              DISTINCT ON (md.model_id)
              md.model_id                         AS model_id,
              md.model                            AS model,
              array_agg(DISTINCT mu.id)           AS menu_ids,
              array_agg(DISTINCT mc.perm_read)    AS perm_read,
              array_agg(DISTINCT mc.perm_write)   AS perm_write,
              array_agg(DISTINCT mc.perm_create)  AS perm_create,
              array_agg(DISTINCT mc.perm_unlink)  AS perm_unlink,
              array_agg(DISTINCT mc.perm_audit)   AS perm_audit,
              array_agg(DISTINCT mc.perm_unaudit) AS perm_unaudit,
              array_agg(DISTINCT mc.perm_export)  AS perm_export,
              array_agg(DISTINCT mc.perm_confirm) AS perm_confirm,
              array_agg(DISTINCT mc.perm_cancel)  AS perm_cancel
            FROM res_groups rg
              INNER JOIN jd_menu_config_base_res_group rel ON rel.left_id = rg.id
              INNER JOIN jd_ir_ui_menu_config mc ON mc.id = rel.right_id
              INNER JOIN ir_ui_menu mu ON mu.id = mc.menu_id
              INNER JOIN
              (
                -- 计算每个菜单关联的模型
                SELECT
                  mu.id    AS menu_id,
                  md.id    AS model_id,
                  md.name  AS model_name,
                  md.model AS model
                FROM ir_ui_menu mu
                  INNER JOIN (SELECT
                                id,
                                res_model
                              FROM ir_act_window) act ON act.id = substring(mu.action, '\d+') :: INTEGER
                  INNER JOIN (SELECT
                                id,
                                model,
                                name
                              FROM ir_model) md ON md.model = act.res_model
                WHERE mu.action IS NOT NULL
              ) md ON md.menu_id = mu.id
            WHERE rg.id = %(group_id)s
            GROUP BY md.model_id, md.model
            ORDER BY md.model_id
          ) mu
        """
        params = {
            'group_id': group_id
        }
        cr.execute(sql, params)
        recs = cr.dictfetchall()
        if recs:
            for rec in recs:
                dict = {
                    'temp_perm_read': rec.get('perm_read', False),
                    'temp_perm_write': rec.get('perm_write', False),
                    'temp_perm_create': rec.get('perm_create', False),
                    'temp_perm_unlink': rec.get('perm_unlink', False),
                    'temp_perm_audit': rec.get('perm_audit', False),
                    'temp_perm_unaudit': rec.get('perm_unaudit', False),
                    'temp_perm_export': rec.get('perm_export', False),
                }
                model_id = rec.get('model_id')
                model_name = rec.get('model')
                menu_ids = rec.get('menu_ids', [])
                # 判断模型的is_transient()方法。如果为真,说明是瞬态模型， 同时添加menu_access
                if self.env[model_name].is_transient() and menu_ids:
                    #     group_rec = self.env['res.groups'].sudo().browse(group_id)
                    #     for menu_id in menu_ids:
                    #         group_rec.menu_access = [(4, menu_id)]
                    sql = '''
                    DO
                    $$
                    DECLARE
                      rec     RECORD;
                      v_count INT :=0;
                    BEGIN
                      FOR rec IN (
                        SELECT mn AS menu_id
                        FROM unnest(%(menu_ids)s) AS mn
                      ) LOOP
                        v_count := 0;
                        SELECT count(1)
                        INTO v_count
                        FROM ir_ui_menu_group_rel rel
                        WHERE rel.gid = %(group_id)s
                              AND rel.menu_id = rec.menu_id;
                        IF v_count = 0
                        THEN
                          INSERT INTO ir_ui_menu_group_rel (gid, menu_id) VALUES (%(group_id)s, rec.menu_id);
                        END IF;
                      END LOOP;
                    END;
                    $$
                    '''
                    params_group = {
                        'group_id': group_id,
                        'menu_ids': menu_ids
                    }
                    cr.execute(sql, params_group)
                # 判断该模型是否有inherits属性。如有则添加代理继承的所有模型访问权
                if self.env[model_name]._inherits.iteritems():
                    for k, v in self.env[model_name]._inherits.iteritems():
                        model = self.env['ir.model'].search([('model', '=', k)], limit=1)
                        self.env['wizard.jd.access.controls.config'].create_acl(model=model,
                                                                                dict=dict,
                                                                                group_id=group_id)
                perm_confirm = rec.get('perm_confirm', False)
                perm_cancel = rec.get('perm_cancel', False)
                if perm_confirm or perm_cancel:
                    sql = '''
                    DO $$
                    DECLARE
                      v_access_id INT;
                    BEGIN
                      -- 查找当前群组是否已有 指定模型的按钮访问权
                      SELECT ba.id
                      INTO v_access_id
                      FROM jd_button_access ba
                      WHERE ba.group_id = %(group_id)s
                            AND ba.model_id = %(model_id)s
                      ORDER BY ba.id DESC
                      LIMIT 1;

                      IF v_access_id IS NOT NULL
                      THEN
                        UPDATE jd_button_access ba
                        SET
                          perm_confirm  = (CASE
                                          WHEN perm_unlink = TRUE
                                            THEN perm_unlink
                                          ELSE %(perm_confirm)s END),
                          perm_cancel  = (CASE
                                          WHEN perm_unlink = TRUE
                                            THEN perm_unlink
                                          ELSE %(perm_cancel)s END)
                        WHERE ba.id = v_access_id;
                      ELSE
                        INSERT INTO jd_button_access
                        (model_id, group_id, perm_confirm, perm_cancel)
                        VALUES
                          (%(model_id)s, %(group_id)s, %(perm_confirm)s, %(perm_cancel)s);
                      END IF;
                    END;
                    $$
                    '''
                    params_access = {
                        'model_id': model_id,
                        'group_id': group_id,
                        'perm_confirm': perm_confirm,
                        'perm_cancel': perm_cancel
                    }
                    cr.execute(sql, params_access)
        sql = '''
        SELECT array_agg(ma.id) AS access_ids
        FROM ir_model_access ma
        WHERE ma.group_id = %(group_id)s
        '''
        params = {
            'group_id': group_id
        }
        cr.execute(sql, params)
        rec = cr.dictfetchone()
        access_ids = rec.get('access_ids')
        return access_ids

