492 lines
20 KiB
Python
492 lines
20 KiB
Python
# -*- coding: utf-8 -*-
|
||
"""
|
||
主数据模块
|
||
"""
|
||
|
||
from datetime import datetime
|
||
from decimal import Decimal, ROUND_HALF_UP
|
||
from hashlib import md5
|
||
from pathlib import Path
|
||
import sys
|
||
from typing import Any, Dict, List, Optional
|
||
|
||
sys.path.append(Path(__file__).parent.parent.as_posix())
|
||
from utils.sqlite import SQLite
|
||
|
||
|
||
class MasterData(SQLite):
|
||
"""
|
||
主数据，支持：
|
||
query_liabilities：根据保险分公司名称、被保险人姓名、证件类型、证件号码和报案时间查询被保险人的理赔责任
|
||
query_institution_type：根据购药及就医机构名称查询购药及就医机构类型
|
||
query_medicine：根据明细项中具体内容查询药品/医疗服务名称
|
||
"""
|
||
|
||
def __init__(self, database: Path):
|
||
"""
|
||
初始化
|
||
:param database: 数据库路径
|
||
"""
|
||
# 初始化SQLite客户端
|
||
super().__init__(database=database)
|
||
try:
|
||
with self:
|
||
# 初始化团单表
|
||
self.execute(
|
||
sql="""
|
||
CREATE TABLE IF NOT EXISTS group_policies
|
||
(
|
||
--团单唯一标识
|
||
guid TEXT PRIMARY KEY,
|
||
--团单号
|
||
group_policy TEXT NOT NULL,
|
||
--保险分公司名称
|
||
insurer_company TEXT NOT NULL,
|
||
--保险起期
|
||
commencement_date TEXT NOT NULL,
|
||
--保险止期
|
||
termination_date TEXT NOT NULL
|
||
)
|
||
"""
|
||
)
|
||
# 初始化个单表
|
||
self.execute(
|
||
sql="""
|
||
CREATE TABLE IF NOT EXISTS person_policies
|
||
(
|
||
--个单唯一标识
|
||
guid TEXT PRIMARY KEY,
|
||
--个单号
|
||
person_policy TEXT NOT NULL,
|
||
--保险起期
|
||
commencement_date TEXT NOT NULL,
|
||
--保险止期
|
||
termination_date TEXT NOT NULL,
|
||
--团单唯一标识，用于联查团案
|
||
group_policy_guid TEXT NOT NULL
|
||
)
|
||
"""
|
||
)
|
||
# 初始化被保险人表，保司推送赔案时，一般无团单号，需先根据保险分公司名称、被保险人姓名、证件类型和证件号码查询被保人
|
||
self.execute(
|
||
sql="""
|
||
CREATE TABLE IF NOT EXISTS insured_persons
|
||
(
|
||
--被保险人唯一标识
|
||
guid TEXT PRIMARY KEY,
|
||
--被保险人姓名
|
||
insured_person TEXT NOT NULL,
|
||
--证件类型
|
||
identity_type TEXT NOT NULL,
|
||
--证件号码
|
||
identity_number TEXT NOT NULL,
|
||
--与主被保险人关系，包括本人、父母、配偶和子女等
|
||
relationship TEXT NOT NULL,
|
||
--个单唯一标识，用于联查个单
|
||
person_policy_guid TEXT NOT NULL
|
||
|
||
)
|
||
"""
|
||
)
|
||
# 初始化理赔责任表
|
||
self.execute(
|
||
sql="""
|
||
CREATE TABLE IF NOT EXISTS liabilities
|
||
(
|
||
--理赔责任唯一标识
|
||
guid TEXT PRIMARY KEY,
|
||
--理赔责任名称
|
||
liability TEXT NOT NULL,
|
||
--理赔类型
|
||
accident TEXT NOT NULL,
|
||
--个人自费理算比例
|
||
personal_self_ratio TEXT NOT NULL,
|
||
--个人自付理算比例
|
||
non_medical_ratio TEXT NOT NULL,
|
||
--合理理算比例
|
||
reasonable_ratio TEXT NOT NULL,
|
||
--个单唯一标识
|
||
person_policy_guid TEXT NOT NULL
|
||
)
|
||
"""
|
||
)
|
||
# 初始化保额变动表
|
||
self.execute(
|
||
sql="""
|
||
CREATE TABLE IF NOT EXISTS coverage_changes
|
||
(
|
||
--保额变动唯一标识
|
||
guid TEXT PRIMARY KEY,
|
||
--变动类型，包括承保和理算等
|
||
change_type TEXT NOT NULL,
|
||
--变动前金额
|
||
before_change_amount TEXT NOT NULL,
|
||
--变动金额
|
||
change_amount TEXT NOT NULL,
|
||
--变动后金额
|
||
after_change_amount TEXT NOT NULL,
|
||
--变动时间
|
||
change_time TEXT NOT NULL,
|
||
--个单唯一标识
|
||
person_policy_guid TEXT NOT NULL
|
||
)
|
||
"""
|
||
)
|
||
# 初始化购药及就医机构表
|
||
self.execute(
|
||
sql="""
|
||
CREATE TABLE IF NOT EXISTS institutions
|
||
(
|
||
--购药及就医机构
|
||
institution TEXT PRIMARY KEY,
|
||
--购药及就医机构类型
|
||
institution_type TEXT NOT NULL,
|
||
--所在省
|
||
province TEXT NOT NULL,
|
||
--所在市
|
||
city TEXT NOT NULL
|
||
)
|
||
"""
|
||
)
|
||
# 初始化药品表
|
||
self.execute(
|
||
sql="""
|
||
CREATE TABLE IF NOT EXISTS medicines
|
||
(
|
||
--药品/医疗服务
|
||
medicine TEXT PRIMARY KEY
|
||
)
|
||
"""
|
||
)
|
||
# 初始化票据理算表
|
||
self.execute(
|
||
sql="""
|
||
CREATE TABLE IF NOT EXISTS receipt_adjustments
|
||
(
|
||
--理算记录唯一标识
|
||
guid TEXT PRIMARY KEY,
|
||
--票据号
|
||
number TEXT NOT NULL,
|
||
--剩余金额
|
||
remaining_amount TEXT NOT NULL,
|
||
--理算时间
|
||
adjust_time TEXT NOT NULL
|
||
)
|
||
"""
|
||
)
|
||
except Exception as exception:
|
||
raise RuntimeError(f"初始化主数据发生异常：{str(exception)}") from exception
|
||
|
||
def query_liabilities(
|
||
self,
|
||
insurer_company: str,
|
||
insured_person: str,
|
||
identity_type: str,
|
||
identity_number: str,
|
||
report_date: str,
|
||
) -> List[Dict[str, Any]]:
|
||
"""
|
||
根据保险分公司名称、被保险人姓名、证件类型、证件号码和报案时间查询被保险人的理赔责任
|
||
:param insurer_company: 保险分公司名称
|
||
:param insured_person: 被保险人姓名
|
||
:param identity_type: 证件类型
|
||
:param identity_number: 证件号码
|
||
:param report_date: 报案时间
|
||
:return: 被保险人的责任数据
|
||
"""
|
||
try:
|
||
with self:
|
||
result = self.query_all(
|
||
sql="""
|
||
SELECT group_policies.group_policy,
|
||
group_policies.insurer_company,
|
||
person_policies.person_policy,
|
||
insured_persons.insured_person,
|
||
insured_persons.identity_type,
|
||
insured_persons.identity_number,
|
||
insured_persons.relationship,
|
||
master_insured_persons.insured_person AS master_insured_person,
|
||
MAX(group_policies.commencement_date,
|
||
person_policies.commencement_date) AS commencement_date,
|
||
MIN(group_policies.termination_date,
|
||
person_policies.termination_date) AS termination_date,
|
||
liabilities.liability,
|
||
liabilities.accident,
|
||
liabilities.personal_self_ratio,
|
||
liabilities.non_medical_ratio,
|
||
liabilities.reasonable_ratio,
|
||
liabilities.person_policy_guid
|
||
FROM insured_persons
|
||
INNER JOIN person_policies
|
||
ON insured_persons.person_policy_guid = person_policies.guid
|
||
INNER JOIN insured_persons master_insured_persons
|
||
ON person_policies.guid = master_insured_persons.person_policy_guid
|
||
AND master_insured_persons.relationship = "本人"
|
||
INNER JOIN group_policies
|
||
ON person_policies.group_policy_guid = group_policies.guid
|
||
INNER JOIN liabilities
|
||
ON person_policies.guid = liabilities.person_policy_guid
|
||
INNER JOIN coverage_changes
|
||
ON liabilities.person_policy_guid = coverage_changes.person_policy_guid
|
||
AND coverage_changes.change_time = (SELECT MAX(change_time)
|
||
FROM coverage_changes
|
||
WHERE liabilities.person_policy_guid = person_policy_guid)
|
||
WHERE group_policies.insurer_company = ?
|
||
AND insured_persons.insured_person = ?
|
||
AND insured_persons.identity_type = ?
|
||
AND insured_persons.identity_number = ?
|
||
AND ? BETWEEN group_policies.commencement_date AND group_policies.termination_date
|
||
AND ? BETWEEN person_policies.commencement_date AND person_policies.termination_date
|
||
AND CAST(coverage_changes.after_change_amount AS REAL) > 0
|
||
ORDER BY commencement_date
|
||
""",
|
||
parameters=(
|
||
insurer_company,
|
||
insured_person,
|
||
identity_type,
|
||
identity_number,
|
||
report_date,
|
||
report_date,
|
||
),
|
||
)
|
||
if not result:
|
||
raise RuntimeError("查无数据")
|
||
|
||
# 就个人自费比例、个人自付比例和合理比例转为小数（decimal对象），保险起期、止期则转为日期时间（datetime对象）
|
||
for index, record in enumerate(result):
|
||
for key, value in record.items():
|
||
match key:
|
||
case _ if key in [
|
||
"personal_self_ratio",
|
||
"non_medical_ratio",
|
||
"reasonable_ratio",
|
||
]:
|
||
result[index][key] = Decimal(value).quantize(
|
||
Decimal("0.00"),
|
||
rounding=ROUND_HALF_UP,
|
||
)
|
||
case _ if key in ["commencement_date", "termination_date"]:
|
||
result[index][key] = datetime.strptime(value, "%Y-%m-%d")
|
||
return result
|
||
|
||
except Exception as exception:
|
||
raise RuntimeError(f"{str(exception)}") from exception
|
||
|
||
def query_institution_type(self, institution: str) -> str:
|
||
"""
|
||
根据购药及就医机构名称查询购药及就医机构类型
|
||
:param institution: 购药及就医机构名称
|
||
:return: 购药及就医机构类型
|
||
"""
|
||
try:
|
||
with self:
|
||
result = self.query_one(
|
||
sql="""
|
||
SELECT institution_type
|
||
FROM institutions
|
||
WHERE institution = ?
|
||
""",
|
||
parameters=(institution,),
|
||
)
|
||
if not result:
|
||
raise RuntimeError("查无数据")
|
||
|
||
return result["institution_type"]
|
||
|
||
except Exception as exception:
|
||
raise RuntimeError(f"{str(exception)}") from exception
|
||
|
||
def query_medicine(
|
||
self,
|
||
content: str,
|
||
) -> str:
|
||
"""
|
||
根据明细项中具体内容查询药品/医疗服务名称
|
||
:param content: 明细项具体内容
|
||
:return: 药品/医疗服务名称
|
||
"""
|
||
try:
|
||
with self:
|
||
result = self.query_all(
|
||
sql="""
|
||
SELECT medicine
|
||
FROM medicines
|
||
WHERE ? LIKE '%' || medicine || '%'
|
||
""",
|
||
parameters=(content,),
|
||
)
|
||
if not result:
|
||
raise RuntimeError("查无数据")
|
||
|
||
return max(result, key=lambda x: len(x["medicine"]))[
|
||
"medicine"
|
||
] # 返回最大长度的药品/医疗服务
|
||
|
||
except Exception as exception:
|
||
raise RuntimeError(f"{str(exception)}") from exception
|
||
|
||
def query_remaining_amount(
|
||
self,
|
||
number: str,
|
||
) -> Optional[Decimal]:
|
||
"""
|
||
根据票据号查询剩余金额
|
||
:param number: 票据号
|
||
:return: 剩余金额
|
||
"""
|
||
try:
|
||
with self:
|
||
result = self.query_one(
|
||
sql="""
|
||
SELECT remaining_amount
|
||
FROM receipt_adjustments
|
||
WHERE number = ?
|
||
ORDER BY adjust_time DESC
|
||
LIMIT 1;
|
||
""",
|
||
parameters=(number,),
|
||
)
|
||
if not result:
|
||
return None
|
||
|
||
return Decimal(result["remaining_amount"]).quantize(
|
||
Decimal("0.00"),
|
||
rounding=ROUND_HALF_UP,
|
||
)
|
||
|
||
except Exception as exception:
|
||
raise RuntimeError(f"{str(exception)}") from exception
|
||
|
||
def add_receipt_adjustment(
|
||
self,
|
||
number: str,
|
||
remaining_amount: Decimal,
|
||
) -> None:
|
||
"""
|
||
新增票据理算记录
|
||
:param number: 票据号
|
||
:param remaining_amount: 剩余金额
|
||
:return: 无
|
||
"""
|
||
if remaining_amount < Decimal("0.00"):
|
||
raise ValueError("剩余金额小于0")
|
||
|
||
# 当前时间
|
||
current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S.%f")
|
||
|
||
# 构建票据理算记录唯一标识
|
||
|
||
guid = (
|
||
md5(
|
||
string=f"{number} {remaining_amount:.2f} {current_time}".encode(
|
||
"utf-8"
|
||
)
|
||
)
|
||
.hexdigest()
|
||
.upper()
|
||
)
|
||
|
||
with self:
|
||
if not self.execute(
|
||
sql="""
|
||
INSERT INTO receipt_adjustments
|
||
(guid, number, remaining_amount, adjust_time)
|
||
VALUES
|
||
(?, ?, ?, ?)
|
||
""",
|
||
parameters=(
|
||
guid,
|
||
number,
|
||
f"{remaining_amount:.2f}",
|
||
current_time,
|
||
),
|
||
):
|
||
raise RuntimeError("新增理算记录发生异常")
|
||
|
||
def query_after_change_amount(
|
||
self,
|
||
person_policy_guid: str,
|
||
) -> Decimal:
|
||
"""
|
||
根据个单唯一标识查询最新一条保额变动记录的变动后金额
|
||
:param person_policy_guid: 个单唯一标识
|
||
:return: 变动后金额
|
||
"""
|
||
try:
|
||
with self:
|
||
result = self.query_one(
|
||
sql="""
|
||
SELECT after_change_amount
|
||
FROM coverage_changes
|
||
WHERE person_policy_guid = ?
|
||
ORDER BY change_time DESC
|
||
LIMIT 1;
|
||
""",
|
||
parameters=(person_policy_guid,),
|
||
)
|
||
if not result:
|
||
raise RuntimeError("查无数据")
|
||
|
||
return Decimal(result["after_change_amount"]).quantize(
|
||
Decimal("0.00"),
|
||
rounding=ROUND_HALF_UP,
|
||
)
|
||
|
||
except Exception as exception:
|
||
raise RuntimeError(f"{str(exception)}") from exception
|
||
|
||
def add_coverage_change(
|
||
self,
|
||
person_policy_guid: str,
|
||
before_change_amount: Decimal,
|
||
change_amount: Decimal,
|
||
) -> None:
|
||
"""
|
||
新增保额扣减记录
|
||
:param person_policy_guid: 个单唯一标识
|
||
:param before_change_amount: 变动前金额
|
||
:param change_amount: 变动金额
|
||
:return: 无
|
||
"""
|
||
# 变动后金额
|
||
after_change_amount = (before_change_amount - change_amount).quantize(
|
||
Decimal("0.00"),
|
||
rounding=ROUND_HALF_UP,
|
||
)
|
||
if after_change_amount < Decimal("0.00"):
|
||
raise ValueError("变动后金额小于0")
|
||
|
||
# 当前时间
|
||
current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S.%f")
|
||
|
||
# 构建保额变动唯一标识
|
||
guid = (
|
||
md5(
|
||
string=f"{person_policy_guid} 保额扣减 {before_change_amount:.2f} {change_amount:.2f} {after_change_amount:.2f} {current_time}".encode(
|
||
"utf-8"
|
||
)
|
||
)
|
||
.hexdigest()
|
||
.upper()
|
||
)
|
||
|
||
with self:
|
||
if not self.execute(
|
||
sql="""
|
||
INSERT INTO coverage_changes
|
||
(guid, change_type, before_change_amount, change_amount, after_change_amount, change_time, person_policy_guid)
|
||
VALUES
|
||
(?, ?, ?, ?, ?, ?, ?)
|
||
""",
|
||
parameters=(
|
||
guid,
|
||
"保额扣减",
|
||
f"{before_change_amount:.2f}",
|
||
f"{change_amount:.2f}",
|
||
f"{after_change_amount:.2f}",
|
||
current_time,
|
||
person_policy_guid,
|
||
),
|
||
):
|
||
raise RuntimeError("新增保额扣减记录发生异常")
|