iepy-develop/examples/coreline/bin/settlement.py

from django.db.models import Q
import datetime,time
import iepy
iepy.setup(__file__)
from iepy.data.db import DocumentManager
from iepy.data.models import IEDocument,LabeledIEDocument,IEDocumentMetadata,LabeledIEDocumentMetadata,Payroll
from brat.models import BratAnnotation,LabeledBratAnnotation
from django.db import transaction
import pandas as pd
from django.contrib.auth.models import User

def object_to_dict(obj,class_model):
    '''
    :param obj:对象
    :param class_model:django model
    :return: 由对象生成的键值对
    '''
    _dict = {}
    concrete_model = class_model._meta.concrete_model
    for field in concrete_model._meta.local_fields:
        value = field.value_from_object(obj)
        _dict[field.name] = value
    return _dict

def timeAdd(_time,days):
    a = time.mktime(time.strptime(_time,'%Y-%m-%d'))+86400*days
    _time1 = time.strftime("%Y-%m-%d",time.localtime(a))
    return _time1

def getWage(user,start_time,end_time,percentPass):
    import time
    from django.db import connection
    cursor = connection.cursor()


    current_begin = max(timeAdd(end_time,-30),start_time)

    print(user,start_time,current_begin,end_time)

    sql = " select count(1) from corpus_iedocument where edituser='%s' and edittime<='%s' and edittime>'%s'"%(user,end_time,start_time)
    cursor.execute(sql)
    doc_count = cursor.fetchall()[0][0]
    print("doc_count",doc_count)
    sql = " select count(1) from brat_bratannotation where document_id in (select human_identifier from corpus_iedocument where edituser='%s' and edittime<='%s' and edittime>'%s' order by edittime desc limit 1200) and value like '%s' "%(user,end_time,current_begin,"T%")
    cursor.execute(sql)
    t_count = cursor.fetchall()[0][0]
    print("t_count",t_count)
    sql = " select count(1) from brat_bratannotation where document_id in (select human_identifier from corpus_iedocument where edituser='%s' and edittime<='%s' and edittime>'%s' order by edittime desc limit 1200) and value like '%s' "%(user,end_time,current_begin,"R%")
    cursor.execute(sql)
    r_count = cursor.fetchall()[0][0]
    print("r_count",r_count)
    sql = " select count(1) from brat_bratannotation where document_id in (select human_identifier from corpus_iedocument where edituser='%s' and edittime<='%s' and edittime>'%s') and value like '%s' "%(user,end_time,current_begin,"T%")
    cursor.execute(sql)
    all_t_count = cursor.fetchall()[0][0]
    print("all_t_count",all_t_count)
    sql = " select count(1) from brat_bratannotation where document_id in (select human_identifier from corpus_iedocument where edituser='%s' and edittime<='%s' and edittime>'%s') and value like '%s' "%(user,end_time,current_begin,"R%")
    cursor.execute(sql)
    all_r_count = cursor.fetchall()[0][0]
    print("all_r_count",all_r_count)

    sql = " select count(1) from brat_bratannotation where document_id in (select human_identifier from corpus_iedocument where edituser='%s' and edittime<='%s' and edittime>'%s') and value like '%s' "%(user,current_begin,start_time,"T%")
    cursor.execute(sql)
    other_t_count = cursor.fetchall()[0][0]
    print("other_t_count",other_t_count)

    sql = " select count(1) from brat_bratannotation where document_id in (select human_identifier from corpus_iedocument where edituser='%s' and edittime<='%s' and edittime>'%s') and value like '%s' "%(user,current_begin,start_time,"R%")
    cursor.execute(sql)
    other_r_count = cursor.fetchall()[0][0]
    print("other_r_count",other_r_count)

    wage = round(0.015*(t_count+other_t_count)+0.025*(r_count+other_r_count)+(all_t_count-t_count)*0.02+(all_r_count-r_count)*0.03,2)

    # and_sql = " and 1=1 "
    # if start_time is not None:
    #     and_sql += " and to_char(edittime,'yyyy-mm-dd')>'%s' "%start_time
    #
    # if end_time is not None:
    #     and_sql += " and to_char(edittime,'yyyy-mm-dd')<='%s'"%end_time
    #
    #
    # sql = " select human_identifier,to_char(edittime,'yyyy-mm-dd hh24:mi:ss') from corpus_iedocument where edituser='%s' %s order by edittime asc" %(user,and_sql)
    # cursor.execute(sql)
    # list_docid = []
    # list_t = []
    # set_docid_before = set()
    # _time_split = time.mktime(time.strptime("2021-05-07 14:00:00","%Y-%m-%d %H:%M:%S"))
    # for row in cursor.fetchall():
    #     list_docid.append(row[0])
    #     _et = time.mktime(time.strptime(row[1],"%Y-%m-%d %H:%M:%S"))
    #     if _et<_time_split:
    #         set_docid_before.add(row[0])
    # set_first = set(list_docid[:1200])
    # sql = " select document_id,value from brat_bratannotation where document_id in(select human_identifier from corpus_iedocument where edituser='%s' %s) "%(user,and_sql)
    # cursor.execute(sql)
    # eleCount = 0
    # relCount = 0
    # wage = 0
    #
    # for document_id,value in cursor.fetchall():
    #     if document_id in set_first:
    #         if document_id in set_docid_before:
    #             if len(value.strip())>0:
    #                 if value[0]=="T":
    #                     eleCount += 1
    #                     wage += 0.03
    #                 else:
    #                     relCount += 1
    #                     wage += 0.05
    #
    #         else:
    #             if len(value.strip())>0:
    #                 if value[0]=="T":
    #                     eleCount += 1
    #                     wage += 0.015
    #                 else:
    #                     relCount += 1
    #                     wage += 0.025
    #
    #     else:
    #         if document_id in set_docid_before:
    #             if len(value.strip())>0:
    #                 if value[0]=="T":
    #                     eleCount += 1
    #                     wage += 0.04
    #                 else:
    #                     relCount += 1
    #                     wage += 0.06
    #         else:
    #             if len(value.strip())>0:
    #                 if value[0]=="T":
    #                     eleCount += 1
    #                     wage += 0.02
    #                 else:
    #                     relCount += 1
    #                     wage += 0.03
    return {"docCount":doc_count,"eleCount":other_t_count+all_t_count,"relCount":other_r_count+all_r_count,"wage":round(wage*percentPass,2)}

class Settlement():

    '''
    @summary: 结算类，定义了结算者所需要执行的各种方法
    '''

    def makePayroll(self,list_user,begin_time,time_end):
        '''
        :param _user: 用户名
        :param time_begin: 起始时间
        :param time_end: 截至时间
        :return:根据用户，时间段生成用户的标注情况
        '''
        from django.db import connection
        with transaction.atomic():
            cursor = connection.cursor()
            time_begin = "2020-08-01"
            for _user in list(set(list_user)):
                sql = 'select max(end_time) from corpus_payroll where "user"=\'%s\''%(_user)
                cursor.execute(sql)
                rows = cursor.fetchall()
                if rows[0][0] is not None:
                    time_begin = rows[0][0]
                else:
                    time_begin = "2020-08-01"

                print(_user,time_begin,time_end)
                dict_wage = getWage(_user,time_begin,time_end,0.99)


                # sql = " select count(1) from corpus_iedocument where edituser='%s' and to_char(edittime,'yyyy-mm-dd')<='%s' and to_char(edittime,'yyyy-mm-dd')>'%s'"%(_user,time_end,time_begin)
                # cursor.execute(sql)
                # doc_count = cursor.fetchall()[0][0]
                # sql = " select count(1) from brat_bratannotation where document_id in (select human_identifier from corpus_iedocument where edituser='%s' and to_char(edittime,'yyyy-mm-dd')<='%s' and to_char(edittime,'yyyy-mm-dd')>'%s' order by edittime desc limit 1200) and value like '%s' "%(_user,time_end,time_begin,"T%")
                # cursor.execute(sql)
                # t_count = cursor.fetchall()[0][0]
                # sql = " select count(1) from brat_bratannotation where document_id in (select human_identifier from corpus_iedocument where edituser='%s' and to_char(edittime,'yyyy-mm-dd')<='%s' and to_char(edittime,'yyyy-mm-dd')>'%s' order by edittime desc limit 1200) and value like '%s' "%(_user,time_end,time_begin,"R%")
                # cursor.execute(sql)
                # r_count = cursor.fetchall()[0][0]
                # sql = " select count(1) from brat_bratannotation where document_id in (select human_identifier from corpus_iedocument where edituser='%s' and to_char(edittime,'yyyy-mm-dd')<='%s' and to_char(edittime,'yyyy-mm-dd')>'%s') and value like '%s' "%(_user,time_end,time_begin,"T%")
                # cursor.execute(sql)
                # all_t_count = cursor.fetchall()[0][0]
                # sql = " select count(1) from brat_bratannotation where document_id in (select human_identifier from corpus_iedocument where edituser='%s' and to_char(edittime,'yyyy-mm-dd')<='%s' and to_char(edittime,'yyyy-mm-dd')>'%s') and value like '%s' "%(_user,time_end,time_begin,"R%")
                # cursor.execute(sql)
                # all_r_count = cursor.fetchall()[0][0]
                # wage = round(0.015*t_count+0.025*r_count+(all_t_count-t_count)*0.02+(all_r_count-r_count)*0.03,2)
                doc_count = dict_wage["docCount"]
                t_count = dict_wage['eleCount']
                r_count = dict_wage["relCount"]
                wage = dict_wage["wage"]
                all_t_count = t_count
                all_r_count = r_count

                print(_user,doc_count,t_count,r_count,wage)
                payrolls = Payroll.objects.filter(Q(user=_user)& Q(begin_time=time_begin) & Q(end_time=time_end))
                if len(payrolls)==0:
                    _payroll = Payroll.objects.create(**{"user":_user,"doc_count":doc_count,"begin_time":time_begin,"end_time":time_end,"t_count":all_t_count,"r_count":all_r_count,"wage":wage})
                    _payroll.save()
                else:
                    _payroll = payrolls[0]
                    _payroll.doc_count = doc_count
                    _payroll.t_count = all_t_count
                    _payroll.r_count = all_r_count
                    _payroll.wage = wage
                    _payroll.save()

    def exportPayroll(self,begin_time,end_time):
        '''
        :param begin_time: 导出开始时间
        :param end_time: 导出结束时间
        :return:
        '''
        list_user = []
        list_username = []
        list_doc_count = []
        list_t_count = []
        list_r_count = []
        list_wage = []
        list_yield = []
        list_account = []
        list_begin = []
        list_end = []
        if begin_time is not None:
            payrolls = Payroll.objects.filter(Q(begin_time=begin_time) & Q(end_time=end_time))
        else:
            payrolls = Payroll.objects.filter(Q(end_time=end_time))
        for _payroll in payrolls:
            list_user.append(User.objects.filter(username=_payroll.user).first().first_name)
            list_username.append(_payroll.user)
            list_doc_count.append(_payroll.doc_count)
            list_t_count.append(_payroll.t_count)
            list_r_count.append(_payroll.r_count)
            list_wage.append(_payroll.wage)
            list_yield.append(_payroll._yield)
            list_account.append(_payroll.account)
            list_begin.append(_payroll.begin_time)
            list_end.append(_payroll.end_time)
        df = pd.DataFrame({"用户":list_user,"登录名":list_username,"开始时间":list_begin,"结束时间":list_end,"文章数":list_doc_count,"要素数":list_t_count,"关系数":list_r_count,"总价":list_wage,"合格率":list_yield,"结算价":list_account})
        df.to_excel("%s-%s要素标注统计.xls"%(begin_time,end_time),columns=["用户","登录名","开始时间","结束时间","文章数","要素数","关系数","总价","合格率","结算价"])

    def getAllUser(self):
        from django.db import connection
        with transaction.atomic():
            list_user = []
            cursor = connection.cursor()
            sql = "select username from auth_user where is_staff='t'"
            cursor.execute(sql)
            for row in cursor.fetchall():
                list_user.append(row[0])
            return list_user


    def makeMigrate(self,_user,time_begin,time_end):
        '''
        :param _user: 用户名
        :param time_begin: 起始时间
        :param time_end: 截至时间
        :return: 将用户在时间段内的数据迁移到标准表中
        '''
        pass
        # from django.db import connection
        # with transaction.atomic():
        #     cursor = connection.cursor()
        #     sql = " select human_identifier,offsets_to_text,sentences from corpus_iedocument where edituser is null"
        #     cursor.execute(sql)
        #     cursor1 = connection.cursor()
        #     _index = 0
        #     rows = True
        #     while(rows):
        #         rows=cursor.fetchmany(1000)
        #         for row in rows:
        #             _index += 1
        #             print(_index)
        #             human_identifier,offsets_to_text,sentences = row
        #             if sentences!="[]":
        #                 _off = offsets_to_text.split(", ")[-1][:-1]
        #                 _sen = sentences.split(", ")[-1][:-1]
        #                 print(_off,_sen)
        #                 if int(_off)!=int(_sen):
        #                     offsets_to_text = offsets_to_text[:-1]+", "+str(int(_sen))+"]"
        #                     print(offsets_to_text)
        #                     cursor1.execute("update corpus_iedocument set offsets_to_text='%s' where human_identifier='%s'"%(offsets_to_text,human_identifier))



            # ieDocuments = IEDocument.objects.filter(Q(edituser=_user) & Q(edittime__range=(time_begin,time_end)))
            # for obj in ieDocuments:
            #     _dict = object_to_dict(obj,IEDocument)
            #     _dict_meta = object_to_dict(obj.metadata,IEDocumentMetadata)
            #     labeledMeta = LabeledIEDocumentMetadata.objects.create(**_dict_meta)
            #     labeledMeta.save()
            #     _dict["metadata"] = labeledMeta
            #     tmp = LabeledIEDocument.objects.create(**_dict)
            #     tmp.save()
            #
            #     bratAnnotations = BratAnnotation.objects.filter(Q(document_id=obj.human_identifier))
            #     for ann in bratAnnotations:
            #         _dict_ann = object_to_dict(ann,BratAnnotation)
            #         labeledAnn = LabeledBratAnnotation.objects.create(**_dict_ann)
            #         labeledAnn.save()


    def getPercentOfPass(self,_user,time_begin,time_end):
        '''
        :param _user:用户名
        :param time_begin: 起始时间
        :param time_end: 截至时间
        :return: 获得用户在时间段内标注数据的合格率
        '''

    def makePayrolls(self,time_begin,time_end):
        '''
        :param time_begin:起始时间
        :param time_end: 截至时间
        :return: 获得所有用户的工资表
        '''
        for _user in self.getAllUser():
            self.makePayroll(_user,time_begin,time_end)
        self.exportPayroll(time_begin,time_end)

    def createUser_batch(self,batch_size=90):
        '''
        :param batch_size: 用户个数
        :return:
        '''
        list_user = [User.objects.create_user(username="bidi%d"%(i+1),password="bidi%d"%(i+1)) for i in range(batch_size)]

    def exportLabels(self):
        groups = [[1,7],[8,14],[15,22],[23,29],[30,36],[37,43],[44,50],[51,56],[57,62],[63,71]]
        from django.db import connection
        cursor = connection.cursor()
        for _i in range(len(groups)):
            _begin,_end = groups[_i]
            list_username = []
            list_user = []
            list_label = []
            list_time = []
            for _j in range(_begin,_end+1):
                username = "bidi%d"%_j
                list_username.append("'%s'"%username)
            sql = " select edituser,human_identifier,to_char(edittime,'yyyy-mm-dd') from corpus_iedocument where edituser in(%s) order by edittime asc"%(",".join(list_username))
            print(sql)
            cursor.execute(sql)
            rows = cursor.fetchall()
            for row in rows:
                list_user.append(row[0])
                list_label.append(row[1])
                list_time.append(row[2])
            df = pd.DataFrame({"时间":list_time,"用户":list_user,"文章编号":list_label})
            df.to_excel("分组_%d.xls"%(_i+1),columns=["时间","用户","文章编号"])

    def filter(self):
        '''
        过滤拍卖公告
        :return:
        '''
        import re
        ieDocuments = IEDocument.objects.all()
        for obj in ieDocuments:
            if re.search("拍卖",obj.text) is not None:
                obj.jump_signal = 1
                obj.save()
                print(obj.human_identifier)

def makeCheck():

    import re
    from datetime import datetime
    T_pattern = re.compile('(?P<T>T\d+)\t(?P<type>[^\s]+) (?P<begin>\d+) (?P<end>\d+)')
    R_pattern = re.compile("(?P<R>R\d+)\t(?P<type>[^\s]+)[^:]*:(?P<arg1>T\d+)[^:]*:(?P<arg2>T\d+)")
    import traceback
    from brat.models import BratAnnotation as brat_annotations
    from iepy.webui.corpus.models import IEDocument
    filename = '32762908'
    pre_label = IEDocument.objects.filter(human_identifier=filename).values("pre_label")
    _label = ""
    if len(pre_label)>0:
        if pre_label[0]["pre_label"] is not None:
            _label = pre_label[0]["pre_label"]
    dict_T = dict()
    dict_R = dict()
    set_pre_label = set()
    for _i in _label.split(";"):
        if _i!="":
            set_pre_label.add(_i)
    set_label = set()
    anns = brat_annotations.objects.filter(document_id=filename).values("value")
    for _str in anns:
        _str = _str["value"].strip()
        if _str != "":
            if _str[0]=="T":
                match = re.search(T_pattern,_str)
                if match is not None:
                    match = match.groupdict()
                    dict_T[match["T"]] = {"type":match["type"],"begin":match["begin"],"end":match["end"]}
            if _str[0]=="R":
                match = re.search(R_pattern,_str)
                if match is not None:
                    match = match.groupdict()
                    dict_R[match["R"]] = {"type":match["type"],"arg1":match["arg1"],"arg2":match["arg2"]}
    for _T,_v in dict_T.items():
        set_label.add("T|%s|%d|%d"%(_v["type"],int(_v["begin"]),int(_v["end"])))
    print(dict_R)
    for _R,_v in dict_R.items():
        set_label.add("R|%s|%d|%d|%d|%d"%(_v["type"],int(dict_T[_v["arg1"]]["begin"]),int(dict_T[_v["arg1"]]["end"]),int(dict_T[_v["arg2"]]["begin"]),int(dict_T[_v["arg2"]]["end"])))
    union_set = set_pre_label&set_label
    deleted = len(set_pre_label)-len(union_set)
    added = len(set_label)-len(union_set)
    print(deleted,added)
    print(set_pre_label-set_label)
    print(set_label-set_pre_label)

if __name__=="__main__":
    settle = Settlement()
    # settle.makeMigrate("test","2020-08-01","2020-08-31")
    settle_end = "2021-09-25"
    settle.makePayroll(["test1","test7","test8","test21","test27","test2","test9","test10","test11","test12","test25","test22","test29"],"2020-08-01",settle_end)
    settle.exportPayroll(begin_time=None,end_time=settle_end)

    # settle.createUser_batch(batch_size=102)
    # settle.exportLabels()
    # settle.filter()