# -*- coding: utf-8 -*-
# @File  : procedure.py
# Created by cx on 2020/7/20
# xuan.chen@yunside.com

from odoo import models, api
import logging

_logger = logging.getLogger(__name__)


class BaseProcedureInit(models.TransientModel):
    _name = 'jd.base.procedure'
    _description = u'基础模块存储过程方法'

    @api.model
    def create_base_procedure(self):
        _logger.info('========= begin create base_procedure =========')
        cr = self.env.cr

        # 判断指定组织指定日期的单据是否已锁定
        sql = """
        CREATE OR REPLACE FUNCTION JD_GET_IS_LOCK(
          _company_id INT, -- 组织ID
          _model      VARCHAR, -- 模型名称 'hd.bd.pro.mating'
          _date       DATE -- 计算日期 '2020-07-20'
        )
          RETURNS BOOLEAN AS $$
        DECLARE
          v_range_id  INT; -- 锁单范围ID
          v_unlock_id INT; -- 解锁申请单ID
          v_is_lock   BOOLEAN := FALSE; -- 是否锁单
        BEGIN
          -- 1、先查询指定组织指定日期的某单据是否锁单
          SELECT lr.id
          INTO v_range_id
          FROM jd_lock_range lr
            INNER JOIN rel_jd_lock_range_ir_model rem ON rem.left_id = lr.id
            INNER JOIN (SELECT
                          id,
                          model
                        FROM ir_model) md ON md.id = rem.right_id
            INNER JOIN rel_jd_lock_range_res_company rec ON rec.left_id = lr.id
          WHERE lr.state = 'audit'
                AND _date BETWEEN lr.date_start AND lr.date_end
                AND md.model = _model
                AND md.model != 'jd.lock.range'
                AND rec.right_id = _company_id
          ORDER BY lr.id DESC
          LIMIT 1;
        
          IF v_range_id ISNULL
          THEN
            -- 2、再查询指定日期的组织为空的某单据是否锁单
            SELECT lr.id
            INTO v_range_id
            FROM jd_lock_range lr
              INNER JOIN rel_jd_lock_range_ir_model rem ON rem.left_id = lr.id
              INNER JOIN (SELECT
                            id,
                            model
                          FROM ir_model) md ON md.id = rem.right_id
            WHERE lr.state = 'audit'
                  AND _date BETWEEN lr.date_start AND lr.date_end
                  AND md.model = _model
                  AND md.model != 'jd.lock.range'
            ORDER BY lr.id DESC
            LIMIT 1;
          END IF;
          -- 3、如果有锁单设置，再检查是否有解锁申请
          IF v_range_id IS NOT NULL
          THEN
            SELECT ua.id
            INTO v_unlock_id
            FROM jd_unlock_apply ua
              INNER JOIN rel_jd_unlock_apply_ir_model rel ON rel.left_id = ua.id
              INNER JOIN (SELECT
                            id,
                            model
                          FROM ir_model) md ON md.id = rel.right_id
            WHERE ua.state = 'audit'
                  AND _date BETWEEN ua.date_start AND ua.date_end
                  AND md.model = _model
                  AND md.model != 'jd.lock.range'
                  AND ua.company_id = _company_id
            ORDER BY ua.id DESC
            LIMIT 1;
          END IF;
          -- 4、如果有锁单设置，并没有解锁申请，则单据被锁定
          IF v_range_id IS NOT NULL AND v_unlock_id ISNULL
          THEN
            v_is_lock := TRUE;
          END IF;
          RETURN v_is_lock;
        END;
        $$
        LANGUAGE plpgsql;
        """
        cr.execute(sql)

        sql = """
          CREATE OR REPLACE FUNCTION JD_CREATE_BIZ_LOG_LINE (
            _log_id       INT,
            _write_fields JSON
          )
          RETURNS VOID AS $$
            DECLARE
              rec         RECORD;
            BEGIN
              IF NOT EXISTS (
                SELECT 
                  1 
                FROM pg_type 
                WHERE typname IN ('bizlogline', 'BIZLOGLINE')
              )
              THEN
                CREATE TYPE BIZLOGLINE AS (field VARCHAR, field_cn VARCHAR, value_before VARCHAR, value_after VARCHAR);
              END IF;

              FOR rec IN (
                SELECT
                  *
                FROM json_populate_recordset(NULL::BIZLOGLINE, _write_fields)
              ) LOOP
                INSERT INTO jd_system_log_biz_line (
                  parent_id, field, field_cn, value_before, value_after
                )
                VALUES (
                  _log_id, rec.field, rec.field_cn, rec.value_before, rec.value_after
                );
              END LOOP;
            END;
          $$
          LANGUAGE plpgsql;
        """
        cr.execute(sql)

        sql = """
          CREATE OR REPLACE FUNCTION JD_ADD_LOGIN_TIMES (
            _user_id INT,
            _region  VARCHAR,
            _hour    VARCHAR,
            _success INT,
            _fail    INT
          )
          RETURNS VOID AS $$
            DECLARE
              v_analysis_id INT := 0;
            BEGIN
              SELECT
                id
              INTO
                v_analysis_id
              FROM jd_system_analysis_login
              WHERE user_id = _user_id
                AND region = _region
                AND hour_login = _hour;
              
              IF v_analysis_id <> 0
              THEN
                UPDATE jd_system_analysis_login
                SET
                  times_login_success = times_login_success + _success,
                  times_login_fail = times_login_fail + _fail
                WHERE id = v_analysis_id;
              ELSE
                INSERT INTO jd_system_analysis_login (
                  user_id, region, hour_login, times_login_success, times_login_fail
                ) VALUES (
                  _user_id, _region, _hour, _success, _fail
                );
              END IF;
            END;
          $$
          LANGUAGE plpgsql;
        """
        cr.execute(sql)

        sql = """
          CREATE OR REPLACE FUNCTION JD_ADD_BIZ_TIMES (
            _user_id  INT,
            _model_id INT,
            _access   INT,
            _create   INT,
            _write    INT,
            _audit    INT,
            _unaudit  INT,
            _cancel   INT,
            _unlink   INT
          )
          RETURNS VOID AS $$
            DECLARE
              v_analysis_id INT := 0;
            BEGIN
              SELECT
                id
              INTO
                v_analysis_id
              FROM jd_system_analysis_biz
              WHERE user_id = _user_id
                AND model_id = _model_id;
              
              IF v_analysis_id <> 0
              THEN
                UPDATE jd_system_analysis_biz
                SET
                  times_access = times_access + _access,
                  times_create = times_create + _create,
                  times_write = times_write + _write,
                  times_audit = times_audit + _audit,
                  times_unaudit = times_unaudit + _unaudit,
                  times_cancel = times_cancel + _cancel,
                  times_unlink = times_unlink + _unlink
                WHERE id = v_analysis_id;
              ELSE
                INSERT INTO jd_system_analysis_biz (
                  user_id, model_id, times_access, times_create, times_write,
                  times_audit, times_unaudit, times_cancel, times_unlink
                ) VALUES (
                  _user_id, _model_id, _access, _create, _write,
                  _audit, _unaudit, _cancel, _unlink
                );
              END IF;
            END;
          $$
          LANGUAGE plpgsql;
        """
        cr.execute(sql)

        _logger.info('========= create base_procedure successful =========')
