#!/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)