#!/usr/bin/python # -*- coding=utf-8 -*- # @Create Time: 2024-01-12 15:23:13 # @Last Modified time: 2024-01-24 15:53:01 from datetime import datetime import click from flask import current_app from flask.cli import with_appcontext from flask_sqlalchemy import SQLAlchemy 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') Full_Url = db.Column(db.UnicodeText, nullable=False, doc='') Sim_Url = db.Column(db.UnicodeText, nullable=True, doc='') User_Id = db.Column(db.String, nullable=False, doc='用户id') Device_Id = db.Column(db.String, nullable=True, doc='设备id') Request_Method = db.Column(db.String, nullable=False, doc='请求方法') Event_Type = db.Column(db.String, nullable=True, doc='事件类型') Form_Data = db.Column(db.UnicodeText, 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='状态码') Target_Url = db.Column(db.UnicodeText, nullable=False, doc='请求地址') Iframe_Url = db.Column(db.UnicodeText, nullable=True, doc='内联url') Button_Text = db.Column(db.String, nullable=True, doc='按钮文字') def to_json(self): json_data = { 'record_time': self.Record_Time, 'current_url': self.Current_Url, 'full_url': self.Full_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)