baseService/ETL/Mars/Model/HuiJiModel.py

import json
import uuid
from Config.Config import *
from Utils import CommonUtil
from Utils.Db import Db


class HuiJiModel(object):
    # 表名前缀
    tablePrefix = 't_huiji_fill_'

    # 创建模板
    def createModule(self, module_name, module_body):
        sql = "insert into t_huiji_module(module_name,module_body,create_time) values('%s','%s',now())"
        params = (module_name, module_body)
        sql = sql % params
        Db.execute(sql)
        # 笨拙的二次查询
        sql = "select max(module_id) as module_id from t_huiji_module"
        moudle_id = Db.findFirstValue(sql)

        # 同时，需要生成表
        tableSettings = json.loads(module_body)
        self.createTable(tableName=self.tablePrefix + str(moudle_id), tableSettings=tableSettings)
        return moudle_id

    # 根据任务ID获取表名
    def getTableName(self, task_id):
        res = self.getTask(task_id)
        if res is None:
            return None
        moudle_id = res["module_id"]
        table_name = self.tablePrefix + str(moudle_id)
        return table_name

    # 更新模板
    def updateModule(self, module_id, module_name, module_body):
        res = {"success": True, "message": "保存成功！"}
        if module_name is not None and len(module_name) > 0:
            sql = "update t_huiji_module set module_name = '%s' where module_id = %s"
            params = (module_name, module_id)
            sql = sql % params
            Db.execute(sql)

        if module_body is not None and len(module_body) > 0:
            # 判断是不是已经有了填报的任务记录
            sql = "select count(1) as c from " + self.tablePrefix + str(module_id)
            c = Db.findFirstValue(sql)
            if c == 0:
                sql = "update t_huiji_module set module_body ='%s' where module_id = %s"
                params = (module_body, module_id)
                sql = sql % params
                Db.execute(sql)
            else:
                res = {"success": False, "message": "此模板已产生依赖的任务，无法修改！"}
        return res

    # 删除模板
    def delModule(self, module_id):
        # 判断是不是可以删除此模板，需要枚举它相关的所有t_huiji_task中task_id
        sql = 'select * from t_huiji_task where module_id = %s' % module_id
        list1 = Db.find(sql)

        if len(list1) == 0:
            sql = "delete from t_huiji_module where module_id = %s" % module_id
            Db.execute(sql)

            # 删除表
            tableName = self.tablePrefix + str(module_id)
            sql = "drop table %s" % tableName
            Db.execute(sql)

            return True
        else:
            return False

    # 获取指定模板
    def getModule(self, module_id):
        sql = "select * from t_huiji_module where module_id = %s"
        return Db.findFirst(sql % module_id)

    # 获取模块列表，支持关键字查询

    def listModule(self, keyword, page, limit):
        sql = "select t1.*,(select count(1) from t_huiji_task as t2 where t2.module_id=t1.module_id) as task_count from t_huiji_module as t1"
        if keyword is not None and len(keyword) > 0:
            sql = sql + " where module_name like '%%'|| '%s' ||'%%'"
            sql = sql % keyword
        sql = sql + " order by module_id desc"
        list1, cnt = Db.paginate(sql, page, limit)
        return list1, cnt

    # 创建任务
    def createTask(self, module_id, task_name, range_json, end_date):
        sql = "insert into t_huiji_task(module_id,task_name,range_json,end_date,all_count,fill_count) values ('%s','%s','%s','%s',0,0) returning task_id"
        sql = sql % (module_id, task_name, range_json, end_date)
        task_id = Db.fetchOne(sql, "task_id")
        # 填充接收人员列表
        self.fillTaskInput(task_id, module_id, range_json)
        return task_id

    # 获取任务
    def getTask(self, task_id):
        sql = "select * from t_huiji_task where task_id=%s" % task_id
        l1 = Db.find(sql)
        if len(l1) == 0:
            return None
        return l1[0]

    def expandTaskReceiveOrgsPrefix(self, record):
        r = json.loads(record["range_json"])
        expandInfo = []
        for organizationno in r["receiveOrgs"]:
            if len(organizationno) == 10 and organizationno.isdigit():
                sql = "select regionc,schooltype from schoolbusinessrelation where organizationno='{organizationno}'".format(
                    organizationno=organizationno)
                r1 = Db.findFirst(sql)
                expandInfo.append("1_" + r1["regionc"] + "_" + r1["schooltype"])
            else:
                if len(organizationno.split("_")) == 3:
                    expandInfo.append(organizationno.split("_")[0] + "_" + organizationno.split("_")[1])

        # 补充上两层的
        newExpandInfo = []
        _dict = {}
        for x in expandInfo:
            level2 = x.split("_")[0] + '_' + x.split("_")[1]
            if level2 not in _dict:
                newExpandInfo.append(level2)
                _dict[level2] = 1

        for x in expandInfo:
            newExpandInfo.append(x)

        newExpandInfo = list(set(newExpandInfo))

        # 排序
        res = []
        for x in newExpandInfo:
            if len(x.split("_")) == 2:
                res.append(x)
        for x in newExpandInfo:
            if len(x.split("_")) == 3:
                res.append(x)
        return res

    # 清空任务
    def clearTask(self, task_id):
        tableName = self.getTableName(task_id)
        sql = "delete from " + tableName + " where task_id=%s" % task_id
        Db.execute(sql)

        sql = "delete from t_huiji_task_org_releation  where task_id=%s" % task_id
        Db.execute(sql)

    # 修改任务
    def updateTask(self, task_id, module_id, task_name, range_json, end_date):
        # 需要清空已经做好的接收者选择数据，然后重新安排接收单位
        self.clearTask(task_id)

        sql = "update t_huiji_task set task_name='%s',range_json='%s',end_date='%s',module_id='%s' where task_id=%s"
        sql = sql % (task_name, range_json, end_date, module_id, task_id)
        Db.execute(sql)

        # 填充接收人员列表
        self.fillTaskInput(task_id, module_id, range_json)

    # 填充任务接收人员列表
    def fillTaskInput(self, task_id, module_id, range_json):
        range_json = json.loads(range_json)
        # 需要根据与吴缤商量好的JSON样例，对JSON进行解析，解析出具体哪些单位需要填报本任务
        tableName = self.tablePrefix + str(module_id)
        regioncData = range_json['receiveRangeArea']
        schooltypeData = range_json['receiveRangeSchoolType']
        receiveOrgsData = range_json['receiveOrgs']

        organizationnoList = []
        flag = False
        for x in receiveOrgsData:
            if x == "1":
                flag = True
                sql = "select organizationno from schoolbusinessrelation where recordyear={recordyear} and regionb='{regionb}'".format(
                    recordyear=recordyear, regionb=city)
                list1 = Db.find(sql)
                for c in list1:
                    organizationnoList.append(c["organizationno"])
            elif "_" not in x:  # 如果是真实的单位
                organizationnoList.append(x)
            else:
                # 2、按单独的数据进行处理
                length = len(x.split("_")) - 1
                # 县区
                regionc = x.split("_")[1]
                # 2.1 县区
                if length == 1:
                    sql = "select organizationno from schoolbusinessrelation where recordyear={recordyear} and regionc = '{regionc}'".format(
                        recordyear=recordyear, regionc=regionc)
                # 　2.2　县区+类型
                else:
                    schooltype = x.split("_")[2]
                    sql = "select organizationno from schoolbusinessrelation where recordyear={recordyear} and regionc = '{regionc}' and schooltype ='{schooltype}'".format(
                        recordyear=recordyear, regionc=regionc, schooltype=schooltype)

                list1 = Db.find(sql)
                for x in list1:
                    organizationnoList.append(x["organizationno"])

        if not flag and (len(regioncData) > 0 or len(schooltypeData) > 0):
            # 1、处理按组合条件查询
            # 　哨兵 活学活用
            sql = "select organizationno from schoolbusinessrelation where recordyear={recordyear} ".format(
                recordyear=recordyear)

            if len(regioncData) == 1:
                regioncData.append("-1")

            if len(regioncData) > 1:
                sql = sql + " and regionc in {regionc}".format(regionc=tuple(regioncData))

            if len(schooltypeData) == 1:
                schooltypeData.append("-1")
            if len(schooltypeData) > 1:
                sql = sql + " and schooltype in {schooltype}".format(schooltype=tuple(schooltypeData))

            list1 = Db.find(sql)
            for y in list1:
                organizationnoList.append(y["organizationno"])
        # 去重
        organizationnoList = list(set(organizationnoList))
        # 删除
        sql = "delete from {tableName} where task_id={task_id}".format(tableName=tableName, task_id=task_id)
        Db.execute(sql)
        # 批量插入
        sql = "insert into " + tableName + "(task_id,org_code,flag,action_time) values(%s,%s,0,now())"

        data = []
        for x in organizationnoList:
            data.append((task_id, x))
        # 批量插入
        # start = datetime.datetime.now()

        # 去重
        data = list(set(data))
        Db.execute_by_execute_batch(sql, data)
        # end = datetime.datetime.now()
        # print("execute_by_execute_batch方法用时：", end - start, "秒")
        # 回写总个数
        sql = "update t_huiji_task set all_count={all_count} where task_id={task_id}".format(all_count=len(data),
                                                                                             task_id=task_id)
        Db.execute(sql)

        # 回写关联关系
        sql = "insert into t_huiji_task_org_releation(task_id,org_code,flag) select task_id,org_code,flag from {tableName} where task_id={task_id}".format(
            tableName=tableName, task_id=task_id)
        Db.execute(sql)

    # 删除任务
    def delTask(self, task_id):
        # 需要清空此任务的所有拼接者数据
        self.clearTask(task_id)
        sql = "delete from t_huiji_task where task_id=%s" % task_id
        Db.execute(sql)

        sql = "delete from t_huiji_task_org_releation where task_id=%s" % task_id
        Db.execute(sql)

    # 用户填报
    def fillTask(self, task_id, org_code, resultJson):
        # 1、填报
        table_name = self.getTableName(task_id)
        resultJson["org_code"] = org_code
        resultJson["flag"] = 1
        Db.update(table_name, "id", resultJson)
        # 2、更新父表统计数据
        sql = "select count(1) as c from {table_name} where task_id={task_id} and flag=1".format(
            table_name=table_name,
            task_id=task_id)
        fill_count = Db.findFirstValue(sql)
        sql = "update t_huiji_task set fill_count={fill_count} where task_id={task_id}".format(
            fill_count=fill_count,
            task_id=task_id)
        Db.execute(sql)

    # 获取任务列表
    def listTask(self, keyword, page, limit, is_publish):
        sql = "select t1.task_id,t1.module_id,t2.module_name,t1.task_name,t1.create_time,t1.range_json,to_char(t1.end_date,'YYYY-MM-DD')as end_date,all_count,fill_count,is_publish from t_huiji_task as t1 inner join t_huiji_module as t2 on t1.module_id=t2.module_id"
        if keyword is not None and len(keyword) > 0:
            sql = sql + " where t1.task_name like '%%'|| '%s' ||'%%'"
            sql = sql % keyword
        if is_publish != -1:
            sql = sql + " and t1.is_publish={is_publish}".format(is_publish=is_publish)
        sql = sql + " order by t1.task_id desc"
        list1, cnt = Db.paginate(sql, page, limit)
        return list1, cnt

    # 创建填充任务表
    def createTable(self, tableName, tableSettings):
        # 默认修改描述的语句
        cols = ''
        comments = []
        # 添加固定字段：id主键
        sql = '''COMMENT ON COLUMN "public"."''' + tableName + '''"."id" IS '主键';'''
        comments.append(sql)
        # 添加固定字段：操作时间
        sql = '''COMMENT ON COLUMN "public"."''' + tableName + '''"."action_time" IS '操作时间';'''
        comments.append(sql)

        sql = '''COMMENT ON COLUMN "public"."''' + tableName + '''"."flag" IS '是否已填报(1,0)';'''
        comments.append(sql)

        for x in tableSettings["list"]:
            name = x['options']["name"].strip()  # 描述
            id = x['options']["id"]  # 字段名
            cols += id + '    varchar(1024),'
            # 记录描述修改的语句
            sql = '''
                           COMMENT ON COLUMN "public"."''' + tableName + '''"."''' + id + '''" IS \'''' + name + ''''
                           '''
            comments.append(sql)
        # 删除表
        sql = 'DROP TABLE IF EXISTS ' + tableName
        Db.execute(sql)
        # 创建表
        sql = '''create table ''' + tableName + '''
                              (ID serial  PRIMARY KEY  NOT NULL,task_id int4,flag int4, org_code varchar(128),''' + cols + '''action_time    timestamp NOT NULL);'''
        Db.execute(sql)
        # 添加注释
        for s in comments:
            Db.execute(s)

    # 发布任务
    def publishTask(self, task_id):
        sql = "update t_huiji_task set is_publish=1 where task_id={task_id}".format(task_id=task_id)
        Db.execute(sql)

    # 填报任务
    def updateTaskForSchool(self, org_code, task_id, resultJson):
        result = json.loads(resultJson)
        table_name = self.getTableName(task_id)
        result["task_id"] = task_id
        result["org_code"] = org_code
        result["action_time"] = CommonUtil.getTime()

        sql = "select id from {table_name} where task_id={task_id} and org_code='{org_code}'".format(
            table_name=table_name, task_id=task_id, org_code=org_code)
        _id = Db.findFirstValue(sql)
        result["id"] = _id
        result["flag"] = 1
        Db.update(table_name, "id", result)

        # 更新关系表
        sql = "update t_huiji_task_org_releation set flag=1 where task_id={task_id} and org_code='{org_code}'".format(
            task_id=task_id, org_code=org_code)
        Db.execute(sql)

        sql = "select count(1) as c  from {table_name} where task_id={task_id} and flag=1".format(table_name=table_name,
                                                                                                  task_id=task_id)
        fill_count = Db.findFirstValue(sql)
        sql = "update t_huiji_task set fill_count={fill_count} where task_id={task_id}".format(fill_count=fill_count,
                                                                                               task_id=task_id)
        Db.execute(sql)

    # 异步获取报送单位数据
    def getSelectTree(self, _id):
        # 　拼接的结果
        res = []
        if _id is None or _id == "":
            res.append({"id": "1", "name": city, "isParent": True})  # 1、根节点
            return json.dumps(res, ensure_ascii=False)
        if _id == "1":
            sql = '''
                     select regionc,count(1) as c from schoolbusinessrelation where recordyear=%s  
                     and regionb='%s' and schooltype in (%s) group by regionc,substring(regionno,1,6) order by substring(regionno,1,6)
                  '''
            sql = sql % (recordyear, city, schoolTypeStr)
            level1List = Db.find(sql)  # 2、县区
            for x in level1List:
                res.append({"id": "1_" + x['regionc'], "name": x['regionc'], "isParent": True})
            return json.dumps(res, ensure_ascii=False)

        _count = len(_id.split('_')) - 1
        if _count == 1:
            # 县区，返回下面的学校类型
            for q in schoolTypeList:
                res.append({"id": _id + '_' + q, "name": q, "isParent": True})
            return json.dumps(res, ensure_ascii=False)

        if _count == 2:
            qu = _id.split('_')[1]
            lx = _id.split('_')[2]
            sql = '''
            select organizationno ,organizationname from schoolbusinessrelation where regionc='%s' 
            and schooltype='%s' and recordyear=%s order by organizationno
        '''
            sql = sql % (qu, lx, recordyear)
            level3List = Db.find(sql)
            for x in level3List:
                res.append({"id": x['organizationno'], "name": x['organizationname'], "isParent": False})
            return json.dumps(res, ensure_ascii=False)

    # 获取所有县区
    def getXianQu(self):
        sql = "select regionc from schoolbusinessrelation where recordyear={recordyear} and regionb='{regionb}'  GROUP BY regionc".format(
            recordyear=recordyear, regionb=city)
        return Db.find(sql)

    # 登录
    def doLogin(self, organizationname):
        sql = "select organizationno from schoolbusinessrelation  where organizationname='{organizationname}' and recordyear={recordyear}".format(
            organizationname=organizationname, recordyear=recordyear)
        organizationno = Db.findFirstValue(sql)
        return organizationno

    # 学校任务列表
    def listTaskForSchool(self, keyword, org_code, flag, page, limit):
        tj = ''
        if flag == "1":
            tj = " and t1.flag=1"
        elif flag == "0":
            tj = " and t1.flag=0"
        sql = "select t1.task_id,t2.module_id,to_char(t2.end_date,'YYYY-MM-DD')as end_date,t1.org_code,t1.flag,t2.task_name,t2.create_time from t_huiji_task_org_releation as t1 inner join t_huiji_task as t2  on t1.task_id=t2.task_id where t2.is_publish=1 and t1.org_code='{org_code}'".format(
            org_code=org_code)
        sql = sql + tj
        if keyword is not None and len(keyword) > 0:
            sql = sql + " and t2.task_name like '%%'|| '{keyword}' ||'%%'".format(keyword=keyword)

        sql = sql + " order by t1.task_id desc"

        print(sql)
        return Db.paginate(sql, page, limit)

    # 根据任务ID获取模块ID
    def getModuleIdByTaskId(self, task_id):
        sql = "select module_id from t_huiji_task where task_id=%s" % task_id
        module_id = Db.findFirstValue(sql)
        return module_id

    # 显示某条任务的填报情况
    def getTaskForSchool(self, org_code, task_id):
        table_name = self.getTableName(task_id)
        sql = "select * from {table_name} where task_id={task_id} and org_code='{org_code}'".format(
            table_name=table_name, org_code=org_code,
            task_id=task_id)
        return Db.findFirst(sql)

    # 获取填报情况
    def getTaskFillInfo(self, task_id, flag, page, limit):
        table_name = self.getTableName(task_id)
        if table_name is None:
            return None, 0
        sql = "select distinct t1.org_code,t2.organizationname,t1.flag from {table_name} as t1 inner join schoolbusinessrelation as t2 on t1.org_code=t2.organizationno ".format(
            table_name=table_name)
        sql = sql + " where task_id={task_id} and t2.recordyear={recordyear}".format(task_id=task_id,
                                                                                     recordyear=recordyear)
        if flag == "1" or flag == "0":
            sql = sql + " and flag={flag}".format(flag=flag)

        print(sql)
        list1, cnt = Db.paginate(sql, page, limit)
        return list1, cnt

    def getFormTypeList(self):
        sql = "select * from t_huiji_form_type"
        list1, cnt = Db.paginate(sql, 1, 1000)
        return list1, cnt

    def getFormType(self, type_id):
        sql = "select * from t_huiji_form_type where type_id=%s" % type_id
        list1, cnt = Db.paginate(sql, 1, 1000)
        return list1, cnt

    def addFormType(self, type_name):
        sql = "insert into t_huiji_form_type(type_id,type_name) values('%s','%s')"
        type_id = uuid.uuid1()
        params = (type_id, type_name)
        sql = sql % params
        Db.execute(sql)

    def updateFormType(self, type_id, type_name):
        sql = "update t_huiji_form_type set type_name='%s' where type_id='%s'"
        params = (type_name, type_id)
        sql = sql % params
        Db.execute(sql)

    def delFormType(self, type_id):
        sql = "delete from  t_huiji_form_type  where type_id='%s'"
        params = (type_id)
        sql = sql % params
        Db.execute(sql)

    def getBureauInfoByOrganizationNo(self, organizationno):
        sql = "select regiona,regionb,regionc as area_name,organizationno as school_id,organizationname as school_name from schoolbusinessrelation where organizationno='%s' and recordyear=2020" % (
            organizationno)
        return Db.find(sql)


if __name__ == '__main__':
    # 　清空表
    # 　truncate t_huiji_module restart identity;
    hm = HuiJiModel()
    # res = hm.getSelectTree("1")
    # print(res)
    #
    # # 县区下所有学校类型
    # res = hm.getSelectTree("1_岳塘区")
    # print(res)
    #
    # res = hm.getSelectTree("1_岳塘区_幼儿园")
    # print(res)

    # sql = 'select * from t_task_1'
    # list1 = Db.page(sql, 1, 10)
    # print(list1)
    range_json = {
        "taskName": "测试任务",
        "moduleId": "13",
        "endDate": "2022-09-23",
        "receiveRangeSchoolType": [
            "幼儿园",
            "小学",
            "初级中学",
            "高级中学",
            "完全中学",
            "九年一贯制学校",
            "十二年一贯制学校"
        ],
        "receiveRangeArea": [
            "岳塘区",
            "雨湖区",
            "湘乡市",
            "韶山市",
            "湘潭县",
            "湘潭九华示范区",
            "湘潭昭山示范区",
            "湖南湘潭高新技术产业园区"
        ],
        "receiveOrgs": [
            # "1",
            "1_岳塘区",
            "1_湖南湘潭高新技术产业园区",
            "1_湘乡市_小学",
            "1_湘潭九华示范区",
            "1_湘潭县",
            "1_湘潭昭山示范区",
            "1_雨湖区",
            "1_韶山市"
        ]
    }
    hm.fillTaskInput(1, 13, range_json)