mzinfo/V2/app/models.py

#!/usr/bin/python
# -*- coding=utf-8 -*-
# @Create Time:		2024-01-12 15:23:13
# @Last Modified time: 2024-02-22 17:34:06
from datetime import datetime
import click
from flask import current_app
from flask.cli import with_appcontext
from flask_sqlalchemy import SQLAlchemy

from .secure import AES_Util

db = SQLAlchemy()


class Record(db.Model):
    # 原始记录表
    __tablename__ = 'recorder'
    # 表的结构:
    id             = db.Column(db.Integer, primary_key=True, autoincrement=True, doc='记录id')
    Record_Time    = db.Column(db.DateTime, nullable=False, doc='记录时间')
    # Current_Url    = db.Column(db.UnicodeText, nullable=False, doc='当前url')
    _Current_Url    = db.Column(db.LargeBinary, nullable=False, doc='当前url')
    # Full_Url       = db.Column(db.UnicodeText, nullable=False, doc='')
    # _Full_Url       = db.Column(db.LargeBinary, nullable=False, doc='')
    # Sim_Url        = db.Column(db.UnicodeText, nullable=True, doc='')
    # _Sim_Url        = db.Column(db.LargeBinary, nullable=True, doc='')
    # User_Id        = db.Column(db.String, nullable=False, doc='用户id')
    _User_Id        = db.Column(db.LargeBinary, nullable=False, doc='用户id')
    # Device_Id      = db.Column(db.String, nullable=True, doc='设备id')
    _Device_Id      = db.Column(db.LargeBinary, nullable=True, doc='设备id')
    # Request_Method = db.Column(db.String, nullable=False, doc='请求方法')
    _Request_Method = db.Column(db.LargeBinary, nullable=False, doc='请求方法')
    # Event_Type     = db.Column(db.String, nullable=True, doc='事件类型')
    _Event_Type     = db.Column(db.LargeBinary, nullable=True, doc='事件类型')
    # Form_Data      = db.Column(db.UnicodeText, nullable=True, doc='表单数据')
    _Form_Data      = db.Column(db.LargeBinary, nullable=True, doc='表单数据')
    # Json_Data      = db.Column(db.UnicodeText, nullable=True, doc='')
    # Value_Data     = db.Column(db.UnicodeText, nullable=True, doc='')
    # Status_Code    = db.Column(db.String, nullable=True, doc='状态码')
    _Status_Code    = db.Column(db.LargeBinary, nullable=True, doc='状态码')
    # Target_Url     = db.Column(db.UnicodeText, nullable=False, doc='请求地址')
    _Target_Url     = db.Column(db.LargeBinary, nullable=False, doc='请求地址')
    # Iframe_Url     = db.Column(db.UnicodeText, nullable=True, doc='内联url')
    _Iframe_Url     = db.Column(db.LargeBinary, nullable=True, doc='内联url')
    # Button_Text    = db.Column(db.String, nullable=True, doc='按钮文字')    
    _Button_Text    = db.Column(db.LargeBinary, nullable=True, doc='按钮文字')    

    @property
    def Current_Url(self):
        aes = AES_Util("CBC", key='9999999999999999', iv=b'qqqqqqqqqqqqqqqq')
        return aes.decrypt(self._Current_Url)

    @Current_Url.setter
    def Current_Url(self, Current_Url):
        aes = AES_Util("CBC", key='9999999999999999', iv=b'qqqqqqqqqqqqqqqq')
        self._Current_Url = aes.encrypt(Current_Url)

    @property
    def User_Id(self):
        aes = AES_Util("CBC", key='9999999999999999', iv=b'qqqqqqqqqqqqqqqq')
        return aes.decrypt(self._User_Id)

    @User_Id.setter
    def User_Id(self, User_Id):
        aes = AES_Util("CBC", key='9999999999999999', iv=b'qqqqqqqqqqqqqqqq')
        self._User_Id = aes.encrypt(User_Id)

    @property
    def Device_Id(self):
        aes = AES_Util("CBC", key='9999999999999999', iv=b'qqqqqqqqqqqqqqqq')
        return aes.decrypt(self._Device_Id)

    @Device_Id.setter
    def Device_Id(self, Device_Id):
        aes = AES_Util("CBC", key='9999999999999999', iv=b'qqqqqqqqqqqqqqqq')
        self._Device_Id = aes.encrypt(Device_Id)

    @property
    def Request_Method(self):
        aes = AES_Util("CBC", key='9999999999999999', iv=b'qqqqqqqqqqqqqqqq')
        return aes.decrypt(self._Request_Method)

    @Request_Method.setter
    def Request_Method(self, Request_Method):
        aes = AES_Util("CBC", key='9999999999999999', iv=b'qqqqqqqqqqqqqqqq')
        self._Request_Method = aes.encrypt(Request_Method)

    @property
    def Event_Type(self):
        aes = AES_Util("CBC", key='9999999999999999', iv=b'qqqqqqqqqqqqqqqq')
        return aes.decrypt(self._Event_Type)

    @Event_Type.setter
    def Event_Type(self, Event_Type):
        aes = AES_Util("CBC", key='9999999999999999', iv=b'qqqqqqqqqqqqqqqq')
        self._Event_Type = aes.encrypt(Event_Type)

    @property
    def Form_Data(self):
        aes = AES_Util("CBC", key='9999999999999999', iv=b'qqqqqqqqqqqqqqqq')
        return aes.decrypt(self._Form_Data)

    @Form_Data.setter
    def Form_Data(self, Form_Data):
        aes = AES_Util("CBC", key='9999999999999999', iv=b'qqqqqqqqqqqqqqqq')
        self._Form_Data = aes.encrypt(Form_Data)

    @property
    def Status_Code(self):
        aes = AES_Util("CBC", key='9999999999999999', iv=b'qqqqqqqqqqqqqqqq')
        return aes.decrypt(self._Status_Code)

    @Status_Code.setter
    def Status_Code(self, Status_Code):
        aes = AES_Util("CBC", key='9999999999999999', iv=b'qqqqqqqqqqqqqqqq')
        self._Status_Code = aes.encrypt(Status_Code)

    @property
    def Target_Url(self):
        aes = AES_Util("CBC", key='9999999999999999', iv=b'qqqqqqqqqqqqqqqq')
        return aes.decrypt(self._Target_Url)

    @Target_Url.setter
    def Target_Url(self, Target_Url):
        aes = AES_Util("CBC", key='9999999999999999', iv=b'qqqqqqqqqqqqqqqq')
        self._Target_Url = aes.encrypt(Target_Url)

    @property
    def Iframe_Url(self):
        aes = AES_Util("CBC", key='9999999999999999', iv=b'qqqqqqqqqqqqqqqq')
        return aes.decrypt(self._Iframe_Url)

    @Iframe_Url.setter
    def Iframe_Url(self, Iframe_Url):
        aes = AES_Util("CBC", key='9999999999999999', iv=b'qqqqqqqqqqqqqqqq')
        self._Iframe_Url = aes.encrypt(Iframe_Url)

    @property
    def Button_Text(self):
        aes = AES_Util("CBC", key='9999999999999999', iv=b'qqqqqqqqqqqqqqqq')
        return aes.decrypt(self._Button_Text)

    @Button_Text.setter
    def Button_Text(self, Button_Text):
        aes = AES_Util("CBC", key='9999999999999999', iv=b'qqqqqqqqqqqqqqqq')
        self._Button_Text = aes.encrypt(Button_Text)

    def to_json(self):
        json_data = {
            'record_time': self.Record_Time,
            'current_url': self.Current_Url,
            'user_id': self.User_Id,
            'device_id': self.Device_Id,
            'request_method': self.Request_Method,
            'event_type': self.Event_Type,
            'form_data': self.Form_Data,
            'status_code': self.Status_Code,
            'target_url': self.Target_Url,
            'iframe_url': self.Iframe_Url,
            'button_text': self.Button_Text
        }
        return json_data


class Fragment(db.Model):
    # 临时表（屏一表三 | 屏一表四）
    __tablename__ = 'fragment'
    # 表的结构:
    id       = db.Column(db.Integer, primary_key=True, autoincrement=True, doc='统计id')
    Time     = db.Column(db.DateTime, nullable=False, doc='记录时间')
    User_Id  = db.Column(db.String, nullable=False, doc='用户id')
    Method   = db.Column(db.String, nullable=False, doc='请求方法')
    Has_Form = db.Column(db.Boolean, nullable=False, default=False, doc='是否有表单')
    Dom      = db.Column(db.String, nullable=False, doc='访问域')
    Title1   = db.Column(db.String, nullable=False, doc='一级标题')
    Title2   = db.Column(db.String, nullable=False, doc='二级标题')
    Title3   = db.Column(db.String, nullable=False, doc='三级标题')
    Title4   = db.Column(db.String, nullable=True, doc='四级标题')
    Behavior = db.Column(db.String, nullable=False, doc='行为内容')
    Stay     = db.Column(db.Integer, nullable=False, default=0, doc='持续时间')


class TotalCount(db.Model):
    # 统计表（屏一表一 | 屏一表二）
    __tablename__ = 'totalcount'
    # 表的结构: 采集总量，偏离总量，登录人数，四域偏离量
    id      = db.Column(db.Integer, primary_key=True, autoincrement=True, doc='统计id')
    Name    = db.Column(db.String,  nullable=False, unique=True, doc='统计名')
    Count   = db.Column(db.Integer, nullable=False, default=0,   doc='统计量')

    def __init__(self, name, count = 0):
        self.Name = name
        self.Count = count

    def to_json(self):
        json_data = {
            self.Name: self.Count
        }
        return json_data

    def add_one(self):
        self.Count += 1


class PersonLogin(db.Model):
    # 用户登录统计表（屏二表二 | 屏一表五）
    __tablename__ = 'personlogin'
    # 表的结构: 用户在线时长
    id              = db.Column(db.Integer, primary_key=True, autoincrement=True, doc='id')
    User_Id         = db.Column(db.String,  nullable=False, unique=True, doc='业务用户id')
    Online_Duration = db.Column(db.Integer, nullable=False, default=0, doc='在线时长')
    ZC_Visit        = db.Column(db.Boolean, nullable=False, default=False, doc='是否访问资产域')
    CW_Visit        = db.Column(db.Boolean, nullable=False, default=False, doc='是否访问财务域')
    RZ_Visit        = db.Column(db.Boolean, nullable=False, default=False, doc='是否访问人资域')
    YX_Visit        = db.Column(db.Boolean, nullable=False, default=False, doc='是否访问财务域')

    def __init__(self, user_id):
        self.User_Id = user_id

    def visit_zc(self):
        self.ZC_Visit = True

    def visit_cw(self):
        self.CW_Visit = True

    def visit_rz(self):
        self.RZ_Visit = True

    def visit_yx(self):
        self.YX_Visit = True

    def to_json(self):
        json_data = {
            "User_Id": self.User_Id        ,
            "Online_Duration": self.Online_Duration,
            "ZC_Visit": self.ZC_Visit       ,
            "CW_Visit": self.CW_Visit       ,
            "RZ_Visit": self.RZ_Visit       ,
            "YX_Visit": self.YX_Visit
        }
        return json_data


class Business(db.Model):
    # 业务操作统计表（屏二表一 | 屏二表三）
    __tablename__ = 'business'
    # 表的结构: 用户操作数量，业务数量，业务时长，业务偏离统计量
    id        = db.Column(db.Integer, primary_key=True, autoincrement=True, doc='行为id')
    User_Id   = db.Column(db.String, unique=True, nullable=False, doc='业务用户id')
    Be_Num    = db.Column(db.Integer, nullable=False, doc='操作统计量')
    Bu_Num    = db.Column(db.Integer, nullable=False, doc='业务统计量')
    Time      = db.Column(db.DateTime, nullable=False, doc='业务时间')
    Bias_Num  = db.Column(db.Integer, nullable=False, doc='业务偏离统计量')


class TotalVisit(db.Model):
    # 域访问量统计表
    __tablename__ = 'totalvisit'
    # 表的结构:
    id    = db.Column(db.Integer, primary_key=True, autoincrement=True, doc='域id')
    Name  = db.Column(db.String, unique=True, nullable=False, doc='域名')
    Count = db.Column(db.Integer, nullable=False, doc='访问量')


class TotalBusiness(db.Model):
    # 业务统计表
    __tablename__ = 'totalbusiness'
    # 表的结构:
    id     = db.Column(db.Integer, primary_key=True, autoincrement=True, doc='业务id')
    Name   = db.Column(db.String, nullable=False, doc='业务名称')
    Count  = db.Column(db.Integer, nullable=False, doc='访问量')


class PersonTotalVisit(db.Model):
    # 个人域访问量统计表
    __tablename__ = 'persontotalvisit'
    # 表的结构:
    id      = db.Column(db.Integer, primary_key=True, autoincrement=True, doc='域id')
    User_Id = db.Column(db.String, nullable=False, doc='用户id')
    Name    = db.Column(db.String, nullable=False, doc='域名')
    Count   = db.Column(db.Integer, nullable=False, doc='访问量')


class PersonPortrayal(db.Model):
    # 用户画像表（屏三表四）
    __tablename__ = 'personportrayal'
    # 表的结构:
    id      = db.Column(db.Integer, primary_key=True, autoincrement=True, doc='画像id')
    User_Id = db.Column(db.String, nullable=False, doc='用户id')
    Attr1   = db.Column(db.String, nullable=False, doc='用户属性1')
    Attr2   = db.Column(db.String, nullable=False, doc='用户属性2')
    Attr3   = db.Column(db.String, nullable=False, doc='用户属性3')
    Attr4   = db.Column(db.String, nullable=False, doc='用户属性4')
    Attr5   = db.Column(db.String, nullable=False, doc='用户属性5')
    Attr6   = db.Column(db.String, nullable=False, doc='用户属性6')
    Attr7   = db.Column(db.String, nullable=False, doc='用户属性7')
    Attr8   = db.Column(db.String, nullable=False, doc='用户属性8')


class PersonDeparture(db.Model):
    # 个人行为偏离统计
    __tablename__ = 'persondeparture'
    # 表的结构:
    id      = db.Column(db.Integer, primary_key=True, autoincrement=True, doc='统计id')
    User_Id = db.Column(db.String, nullable=False, doc='用户id')
    Count   = db.Column(db.Integer, nullable=False, doc='偏离量')


class PersonBehavior(db.Model):
    # 个人行为表
    __tablename__ = 'personbehavior'
    # 表的结构:
    id      = db.Column(db.Integer, primary_key=True, autoincrement=True, doc='行为id')
    User_Id = db.Column(db.String, nullable=False, unique=True, doc='行为用户id')
    Time    = db.Column(db.DateTime, nullable=False, doc='行为时间')
    BGroup  = db.Column(db.String, doc='行为组')



@click.command('init-db')
@with_appcontext
def init_db_command():
    db.create_all()
    tc1 = TotalCount(name='采集量')
    tc2 = TotalCount(name='发现行为偏离')
    tc3 = TotalCount(name='资产域行为偏离')
    tc4 = TotalCount(name='财务域行为偏离')
    tc5 = TotalCount(name='人资域行为偏离')
    tc6 = TotalCount(name='营销管理行为偏离')
    db.session.add_all([tc1,tc2,tc3,tc4,tc5,tc6])
    db.session.commit()

@click.command('drop-db')
@with_appcontext
def drop_db_command():
    db.drop_all()

def init_app(app):
    app.cli.add_command(init_db_command)
    app.cli.add_command(drop_db_command)