# -*- coding: utf-8 -*-
"""
@author: MonsterHe
@contact: yuntian.hee@gmail.com
@version: python3.5
@time: 2018/11/3 10:26
@tools: Pycharm 2018.1
"""
"""
update:
是错误信息输出到表格

"""

"""
当前检查的功能：（*标记的为未实现）
1.非内容字段包含HTML标签  
2.内容标签截断
3.标题、发布时间格式错误
4.标签采集结果为空
6.非标讯检查
11.内容中包含input标签，未把标签内的值替换出来（在后续流程中标签会被去掉，这样会导致未提取的值也会被去掉造成内容不全
*5.采集非本站数据
*7.数据处理（空格替换为空）
*8.cookie不是用脚本获取的
*9.任务备注里面包含 ""或''
*10.内容是json格式或源页面json解析异常而未处理
"""

import sqlite3
import re
import os
from openpyxl import load_workbook
from openpyxl import Workbook
from collections import OrderedDict


def create_result_db(path):
    folder = os.path.exists(path)

    pass


# 配置数据库路径
r_path = r'''D:\7个火车头\标讯采集\Configuration\config.db3'''
j_path = r'''D:\7个火车头\标讯采集\Data\%s\SpiderResult.db3'''
result_db_path = r'd:\checkresult'


# excel_path = r"mistakes.xlsx"

# 连接数据库
def connect_db(db_path):
    return sqlite3.connect(db_path)


# 检查规则
def check_rule(siteid, db_path):
    # 校验是否为当前检查的组
    sqlcheck = '''SELECT Job.jobId, Job.JobName, Site.SiteId, Site.SiteName FROM Job, Site
    WHERE Job.SiteId = Site.SiteId
    AND Job.SiteId = %s
    LIMIT 1''' % siteid

    # 检查发布时间为系统时间是否标记-A
    sqldatecheck = """
    SELECT jobid,jobname FROM "Job" t
    where t.siteid  in (%s)
    and (instr(t.xmldata,'ManualTimeStr="yyyy-MM-dd"')
    and JobName NOT LIKE '%%%%-A%%%%'
    )
    """ % siteid

    # 检查标签是否完整
    sqllabelcheck = '''SELECT j.jobid, j.jobname, s.siteid, s.sitename FROM "Job" j, "Site" s
    WHERE j.SiteId = S.SiteId
    and j.siteid  in (%s)
    AND (instr(j.XmlData, '<Rule LabelName="标题"')=0
    OR instr(j.XmlData, '<Rule LabelName="发布时间"')=0
    OR instr(j.XmlData, '<Rule LabelName="采购编号"')=0
    OR instr(j.XmlData, '<Rule LabelName="地区"')=0
    OR instr(j.XmlData, '<Rule LabelName="来源"')=0
    OR instr(j.XmlData, '<Rule LabelName="数据类型"')=0
    OR instr(j.XmlData, '<Rule LabelName="公告类型"')=0
    OR instr(j.XmlData, '<Rule LabelName="内容"')=0)
    ''' % siteid

    sql2 = """
        SELECT jobid,jobname FROM "Job" t
        where t.siteid  in (%s)
        and instr(xmldata,'Rule LabelName="公告类型" GetDataType="0" StartStr="" EndStr="" RegexContent="" RegexCombine="" XpathContent="" XPathAttribue="0" TextRecognitionType="0" TextRecognitionCodeReturnType="1" LengthFiltOpertar="0" LengthFiltValue="0" LabelContentMust="" LabelContentForbid="" FileUrlMust="" FileSaveDir="" FileSaveFormat="" ManualType="0" ManualString="type01') =0
        and instr(xmldata,'Rule LabelName="公告类型" GetDataType="0" StartStr="" EndStr="" RegexContent="" RegexCombine="" XpathContent="" XPathAttribue="0" TextRecognitionType="0" TextRecognitionCodeReturnType="1" LengthFiltOpertar="0" LengthFiltValue="0" LabelContentMust="" LabelContentForbid="" FileUrlMust="" FileSaveDir="" FileSaveFormat="" ManualType="0" ManualString="type02') =0
        """ % siteid
    sql3 = """
        SELECT jobid,jobname FROM "Job" t
        where t.siteid  in (%s)
        and instr(XmlData,'MaxSpiderPerNum="0" MaxOutPerNum="0"') = 0
        """ % siteid
    sql4 = """
        SELECT jobid,jobname FROM "Job" t
        where t.siteid  in (%s)
        and instr(t.XmlData,'CheckUrlRepeat=""') = 0
        """ % siteid
    sql5 = """
        SELECT jobid,jobname FROM "Job" t
        where t.siteid  in (%s)
        and(instr(t.XmlData,'new="201') > 0
        or instr(t.XmlData,'<FillBothEnd Start="201') > 0)
        """ % siteid
    sql6 = """
        SELECT jobid,jobname FROM "Job" t
        where t.siteid  in (%s)
        and( instr(xmldata,'UrlRepeat=""') =0
        or instr(xmldata,'CheckUrlRepeat=""') =0)
        """ % siteid

    # 连接数据库并获得cursor
    conn = connect_db(db_path)
    cursor = conn.cursor()
    try:
        rules = cursor.execute(sqlcheck)
        for rule_info in rules:
            print("校验信息：\n ", rule_info, "\n----------------")

        labelcheck = cursor.execute(sqllabelcheck)
        for item1 in labelcheck:
            print(item1, '缺少标签')
        sdatacheck = cursor.execute(sqldatecheck)
        for item2 in sdatacheck:
            print(item2, '系统时间未标记-A')
        row2 = cursor.execute(sql2)
        for x2 in row2:
            print(x2, '公告类型写死')
        row3 = cursor.execute(sql3)
        for x3 in row3:
            print(x3, '误设最大页数')
        row4 = cursor.execute(sql4)
        for x4 in row4:
            print(x4, '检查重复没有勾上')
        row5 = cursor.execute(sql5)
        for x5 in row5:
            print(x5, '规则里面有前缀后缀')
        row6 = cursor.execute(sql6)
        for x6 in row6:
            print(x6, '检查网址重复未选上')
    except NotImplementedError as error:
        print(str(error))
    finally:
        cursor.close()
        conn.close()


# 生成一个列表
def add_list(job, error_info):
    return [job[0], job[1], error_info]


def open_excel():
    wb = Workbook()
    ws = wb.active
    return ws


# 检查内容
def check_content(job_path, info_lst, job_info):
    # 检查非内容字段有无截断HTML标签
    sql1 = '''SELECT ID, 标题, 发布时间 FROM content
    WHERE 已采 = 1
    AND (标题 LIKE '%<%'
    OR 标题 LIKE '%>%'
    OR 发布时间 LIKE '%<%'
    OR 发布时间 LIKE '%>%')
    '''

    # 检查有空内容的字段
    sql2 = '''SELECT ID, 标题, 内容, 发布时间 FROM Content WHERE 已采=1
    AND (标题 IS NULL
    OR 内容 IS NULL
    OR 发布时间 IS NULL
    OR 内容=''
    OR 发布时间=''
    OR 标题='')
    '''

    # 检查内容标签中包含有input标签
    sql3 = '''SELECT ID, 标题 FROM Content WHERE 已采=1
    AND 内容 LIKE '%<input%'
     '''
    # 检查内容中有截断HTML标签
    sql4 = '''SELECT ID, 标题, 内容 FROM Content WHERE 已采=1
    '''
    # 检查采集结果是否不为标讯
    sql5 = '''SELECT ID, 标题, 内容 FROM Content WHERE 已采=1
    AND 标题 IS NOT NULL
    AND 标题 != ''
    AND 内容 IS NOT NULL
    AND 内容 != ''
    '''

    # 检查标题或发布时间格式是否正确
    sql6 = '''SELECT ID, 标题, 发布时间 FROM Content WHERE 已采=1
    AND 标题 IS NOT NULL
    AND 标题 != ''
    AND 发布时间 IS NOT NULL
    AND 发布时间 != ''
    '''

    conn = connect_db(job_path)
    cursor = conn.cursor()
    # 列出任务信息
    info_lst.append(["任务ID", "任务名", "错误信息"])
    info_lst.append(job_info)
    try:
        print('---------开始检查非内容字段是否有HTML标签--------')
        contents1 = cursor.execute(sql1)
        for job1 in contents1:
            info1 = add_list(job1, "标题或时间有HTML标签")
            info_lst.append(info1)
            # print('{}\t {}\t标题或时间有HTML标签'.format(job1[0], job1[1]))

        print('\n---------开始检查内容、标题、发布时间是否有空--------')
        contents2 = cursor.execute(sql2)
        for job2 in contents2:
            # info = add_list(job2,"内容或标题或发布时间有空值")
            info2 = [job2[0], job2[1], "有空值"]
            info_lst.append(info2)
            # print('{}\t {}\t内容或标题或发布时间有空值'.format(job2[0], job2[1]))

        print('\n---------开始检查内容字段是否有input标签--------')
        contents3 = cursor.execute(sql3)
        for job3 in contents3:
            info3 = add_list(job3, "内容有Input标签没有去除")
            info_lst.append(info3)
            # print('{}\t {}\t内容有input标签没有去除'.format(job3[0], job3[1]))

        print('\n---------开始检查内容字段是否有截断标签（开头处）--------')
        # 检查内容是否有截断HTML标签（目前只能检查开头）
        contents4 = cursor.execute(sql4)
        for job4 in contents4:
            content = job4[2]
            if isinstance(content, str):
                result = re.match(r"^.*?>", content)
                if result != None:
                    if result.group(0).count('<') != result.group(0).count('>'):
                        info4 = add_list(job4, "内容可能有截断标签")
                        info_lst.append(info4)
                        # print("{}\t {}\t内容可能有截断标签".format(job4[0], job4[1]))
                    else:
                        pass
                else:
                    pass
            else:
                pass

        print('\n---------开始检查采集内容是否不为标讯（仅做参考）--------')
        contents5 = cursor.execute(sql5)
        for job5 in contents5:
            title = job5[1]
            content = job5[2]
            judge_value = check_title(title)
            # 标题若无法判断是否为标讯，则进入内容判断
            if judge_value != 1:
                judge_value = check_bidding(content)
                if judge_value != 1:
                    info5 = add_list(job5, "采集内容不是标讯(仅做参考)")
                    info_lst.append(info5)
                    # print('{}\t {}\t {tip}'.format(job5[0], job5[1], tip='采集内容不是标讯(仅做参考)'))
            else:
                pass
        print('\n---------检查标题、发布时间格式是否正确--------')
        contents6 = cursor.execute(sql6)
        for job6 in contents6:
            title = job6[1]
            publishtime = job6[2]
            judge_value1 = check_title_form(title)
            if judge_value1 == -1:
                info6 = add_list(job6, "标题前或后有空格")
                info_lst.append(info6)
                # print('{}\t {}\t {tip}'.format(job6[0], job6[1], tip='标题前或后有空格'))
            judge_value2 = check_publishtime_form(publishtime)
            if judge_value2 == -1:
                info7 = add_list(job6, "发布时间有空格")
                info_lst.append(info7)
                # print('{}\t {}\t {tip}'.format(job6[0], job6[1], tip='发布时间有空格'))
            if judge_value2 == -2:
                info8 = add_list(job6, "发布时间格式错误")
                info_lst.append(info8)
                # print('{}\t {}\t {tip}'.format(job6[0], job6[1], tip='发布时间格式错误'))

        # 写入表格
        return info_lst
        # for mistake in infos:
        #     print(mistake)
        #     ws.append(mistake)
        # wb.save(excel_path)

    except sqlite3.OperationalError as e:
        pass
    except NotImplementedError as e:
        print(str(e))
    finally:
        cursor.close()
        conn.close()


# 检查非标讯
def check_title(s_title):
    targets = ['中标', '招标', '议标', '废标', '流标', '邀标', '询价', '谈判', '采购',
               '比选', '磋商', '成交', '竞价', '竞投', '中选', '邀请招标', '投标',
               '竞谈', '单一来源', '竞争性', '竞选', '评标', '项目结果公示', '竞标',
               '摇珠', '开标', '招商', '确标', '询比价', '补遗', '中选', '发包', '补遗',
               '答疑', '项目限价', '竞标', '竞选', '议价', '竟标', '招租', '询标', '遴选', '标段', '工程'
               ]

    not_targets = ['条例', '规定', '工作', '规范', '标准', '荣获', '招聘', '试行', '行动']

    for target in targets:
        if target in s_title:
            return 1
        else:
            pass
    return -1


# 检查标讯是否被过滤
def check_bidding(s_content):
    targets = ['招标条件', '项目名称', '招标范围', '投标人资格', '投标人', '招标文件', '投标',
               '招标人', '标段名称', '中标人', '评标结果', '投标', '采购人', '单一来源编号', '招标编号',
               '采购组织', '标项名称', '采购项目', '采购方式', '成交结果', '原采购公告', '采购公告',
               '采购内容', '合格供应商', '采购文件', '保证金', '开标时间', '询价项目编号', '询价供应商',
               '标项名称', ]
    for target in targets:
        if target in s_content:
            return 1
        else:
            pass
    return None


# 检查标题格式
def check_title_form(s_content):
    result = re.search(r'''^\s*|\s*$''', s_content)
    if result.group() != '':
        return -1
    else:
        return None


# 检查发布时间格式
def check_publishtime_form(s_content):
    # 首尾空格检查
    check_block = re.search(r'''^\s*|\s*$''', s_content)
    if check_block.group() != '':
        return -1
    else:
        # 格式检查
        check_form = re.match(r'''^\d{4}-\d{1,2}-\d{1,2}$''', s_content)
        if check_form is None:
            return -2
        else:
            return None


def get_jobs(siteid, db_path):
    # job_dic = OrderedDict()
    sql = '''SELECT j.jobid, j.jobname FROM "Job"j WHERE j.siteid = %s''' % siteid
    conn = connect_db(db_path)
    cursor = conn.cursor()
    try:
        jobs = cursor.execute(sql)
        job_dic = {jobid: jobname for (jobid, jobname) in jobs}
        return job_dic
    except sqlite3.OperationalError as e:
        pass
    except NotImplementedError as e:
        print(str(e))
    finally:
        cursor.close()
        conn.close()


def check_db():
    excel_path = "mistakes.xlsx"  # 表格路径
    while True:
        siteid = input('input site ID = ')
        print('input flag:\n1 --- check rule \n2 --- check content \n3 --- all check \n')
        select_check = int(input('flag = '))
        if select_check == 1:
            # 检查规则
            check_rule(siteid=siteid, db_path=r_path)
            print('--- END')
        elif select_check == 2:
            # 选择检查某一个任务，或全部任务
            job_dic = get_jobs(siteid=siteid, db_path=r_path)
            while True:
                print('id ', '---', 'jobname')
                for jobid, jobname in sorted(job_dic.items(), key=lambda x:x[0]):
                    print(jobid, '---', jobname)
                print('input a flag:\n1 --- check all \n0 --- exit \nid --- select one job\n')
                selectid = int(input('flag = '))
                if selectid == 1:
                    info_lst = []  # 存储错误信息的列表
                    for job_id, job_name in sorted(job_dic.items(), key=lambda x:x[0]):
                        print("\n--- 开始执行检查：", job_name, job_id, "\n")
                        job_path = j_path % job_id
                        job_info = [job_id, job_name]
                        check_content(job_path, info_lst, job_info)
                    wb = Workbook()
                    ws = wb.active
                    for info in info_lst:
                        ws.append(info)
                    wb.save(excel_path)

                elif selectid == 0:
                    print('--- END')
                    break
                else:
                    job_path = j_path % selectid
                    info_lst = []
                    job_info = [selectid]
                    check_content(job_path, info_lst, job_info)
        else:
            print('--- exit')
            break


def main():
    check_db()


if __name__ == '__main__':
    main()

5f68a341b7f63c544d29c8656a0815c45e12b06e
<mask token>


def create_result_db(path):
    folder = os.path.exists(path)
    pass


<mask token>


def connect_db(db_path):
    return sqlite3.connect(db_path)


<mask token>


def add_list(job, error_info):
    return [job[0], job[1], error_info]


def open_excel():
    wb = Workbook()
    ws = wb.active
    return ws


def check_content(job_path, info_lst, job_info):
    sql1 = """SELECT ID, 标题, 发布时间 FROM content
    WHERE 已采 = 1
    AND (标题 LIKE '%<%'
    OR 标题 LIKE '%>%'
    OR 发布时间 LIKE '%<%'
    OR 发布时间 LIKE '%>%')
    """
    sql2 = """SELECT ID, 标题, 内容, 发布时间 FROM Content WHERE 已采=1
    AND (标题 IS NULL
    OR 内容 IS NULL
    OR 发布时间 IS NULL
    OR 内容=''
    OR 发布时间=''
    OR 标题='')
    """
    sql3 = """SELECT ID, 标题 FROM Content WHERE 已采=1
    AND 内容 LIKE '%<input%'
     """
    sql4 = 'SELECT ID, 标题, 内容 FROM Content WHERE 已采=1\n    '
    sql5 = """SELECT ID, 标题, 内容 FROM Content WHERE 已采=1
    AND 标题 IS NOT NULL
    AND 标题 != ''
    AND 内容 IS NOT NULL
    AND 内容 != ''
    """
    sql6 = """SELECT ID, 标题, 发布时间 FROM Content WHERE 已采=1
    AND 标题 IS NOT NULL
    AND 标题 != ''
    AND 发布时间 IS NOT NULL
    AND 发布时间 != ''
    """
    conn = connect_db(job_path)
    cursor = conn.cursor()
    info_lst.append(['任务ID', '任务名', '错误信息'])
    info_lst.append(job_info)
    try:
        print('---------开始检查非内容字段是否有HTML标签--------')
        contents1 = cursor.execute(sql1)
        for job1 in contents1:
            info1 = add_list(job1, '标题或时间有HTML标签')
            info_lst.append(info1)
        print('\n---------开始检查内容、标题、发布时间是否有空--------')
        contents2 = cursor.execute(sql2)
        for job2 in contents2:
            info2 = [job2[0], job2[1], '有空值']
            info_lst.append(info2)
        print('\n---------开始检查内容字段是否有input标签--------')
        contents3 = cursor.execute(sql3)
        for job3 in contents3:
            info3 = add_list(job3, '内容有Input标签没有去除')
            info_lst.append(info3)
        print('\n---------开始检查内容字段是否有截断标签（开头处）--------')
        contents4 = cursor.execute(sql4)
        for job4 in contents4:
            content = job4[2]
            if isinstance(content, str):
                result = re.match('^.*?>', content)
                if result != None:
                    if result.group(0).count('<') != result.group(0).count('>'
                        ):
                        info4 = add_list(job4, '内容可能有截断标签')
                        info_lst.append(info4)
                    else:
                        pass
                else:
                    pass
            else:
                pass
        print('\n---------开始检查采集内容是否不为标讯（仅做参考）--------')
        contents5 = cursor.execute(sql5)
        for job5 in contents5:
            title = job5[1]
            content = job5[2]
            judge_value = check_title(title)
            if judge_value != 1:
                judge_value = check_bidding(content)
                if judge_value != 1:
                    info5 = add_list(job5, '采集内容不是标讯(仅做参考)')
                    info_lst.append(info5)
            else:
                pass
        print('\n---------检查标题、发布时间格式是否正确--------')
        contents6 = cursor.execute(sql6)
        for job6 in contents6:
            title = job6[1]
            publishtime = job6[2]
            judge_value1 = check_title_form(title)
            if judge_value1 == -1:
                info6 = add_list(job6, '标题前或后有空格')
                info_lst.append(info6)
            judge_value2 = check_publishtime_form(publishtime)
            if judge_value2 == -1:
                info7 = add_list(job6, '发布时间有空格')
                info_lst.append(info7)
            if judge_value2 == -2:
                info8 = add_list(job6, '发布时间格式错误')
                info_lst.append(info8)
        return info_lst
    except sqlite3.OperationalError as e:
        pass
    except NotImplementedError as e:
        print(str(e))
    finally:
        cursor.close()
        conn.close()


def check_title(s_title):
    targets = ['中标', '招标', '议标', '废标', '流标', '邀标', '询价', '谈判', '采购', '比选',
        '磋商', '成交', '竞价', '竞投', '中选', '邀请招标', '投标', '竞谈', '单一来源', '竞争性',
        '竞选', '评标', '项目结果公示', '竞标', '摇珠', '开标', '招商', '确标', '询比价', '补遗',
        '中选', '发包', '补遗', '答疑', '项目限价', '竞标', '竞选', '议价', '竟标', '招租', '询标',
        '遴选', '标段', '工程']
    not_targets = ['条例', '规定', '工作', '规范', '标准', '荣获', '招聘', '试行', '行动']
    for target in targets:
        if target in s_title:
            return 1
        else:
            pass
    return -1


def check_bidding(s_content):
    targets = ['招标条件', '项目名称', '招标范围', '投标人资格', '投标人', '招标文件', '投标', '招标人',
        '标段名称', '中标人', '评标结果', '投标', '采购人', '单一来源编号', '招标编号', '采购组织',
        '标项名称', '采购项目', '采购方式', '成交结果', '原采购公告', '采购公告', '采购内容', '合格供应商',
        '采购文件', '保证金', '开标时间', '询价项目编号', '询价供应商', '标项名称']
    for target in targets:
        if target in s_content:
            return 1
        else:
            pass
    return None


<mask token>


def check_publishtime_form(s_content):
    check_block = re.search('^\\s*|\\s*$', s_content)
    if check_block.group() != '':
        return -1
    else:
        check_form = re.match('^\\d{4}-\\d{1,2}-\\d{1,2}$', s_content)
        if check_form is None:
            return -2
        else:
            return None


def get_jobs(siteid, db_path):
    sql = 'SELECT j.jobid, j.jobname FROM "Job"j WHERE j.siteid = %s' % siteid
    conn = connect_db(db_path)
    cursor = conn.cursor()
    try:
        jobs = cursor.execute(sql)
        job_dic = {jobid: jobname for jobid, jobname in jobs}
        return job_dic
    except sqlite3.OperationalError as e:
        pass
    except NotImplementedError as e:
        print(str(e))
    finally:
        cursor.close()
        conn.close()


<mask token>


def main():
    check_db()


<mask token>

<mask token>


def create_result_db(path):
    folder = os.path.exists(path)
    pass


<mask token>


def connect_db(db_path):
    return sqlite3.connect(db_path)


<mask token>


def add_list(job, error_info):
    return [job[0], job[1], error_info]


def open_excel():
    wb = Workbook()
    ws = wb.active
    return ws


def check_content(job_path, info_lst, job_info):
    sql1 = """SELECT ID, 标题, 发布时间 FROM content
    WHERE 已采 = 1
    AND (标题 LIKE '%<%'
    OR 标题 LIKE '%>%'
    OR 发布时间 LIKE '%<%'
    OR 发布时间 LIKE '%>%')
    """
    sql2 = """SELECT ID, 标题, 内容, 发布时间 FROM Content WHERE 已采=1
    AND (标题 IS NULL
    OR 内容 IS NULL
    OR 发布时间 IS NULL
    OR 内容=''
    OR 发布时间=''
    OR 标题='')
    """
    sql3 = """SELECT ID, 标题 FROM Content WHERE 已采=1
    AND 内容 LIKE '%<input%'
     """
    sql4 = 'SELECT ID, 标题, 内容 FROM Content WHERE 已采=1\n    '
    sql5 = """SELECT ID, 标题, 内容 FROM Content WHERE 已采=1
    AND 标题 IS NOT NULL
    AND 标题 != ''
    AND 内容 IS NOT NULL
    AND 内容 != ''
    """
    sql6 = """SELECT ID, 标题, 发布时间 FROM Content WHERE 已采=1
    AND 标题 IS NOT NULL
    AND 标题 != ''
    AND 发布时间 IS NOT NULL
    AND 发布时间 != ''
    """
    conn = connect_db(job_path)
    cursor = conn.cursor()
    info_lst.append(['任务ID', '任务名', '错误信息'])
    info_lst.append(job_info)
    try:
        print('---------开始检查非内容字段是否有HTML标签--------')
        contents1 = cursor.execute(sql1)
        for job1 in contents1:
            info1 = add_list(job1, '标题或时间有HTML标签')
            info_lst.append(info1)
        print('\n---------开始检查内容、标题、发布时间是否有空--------')
        contents2 = cursor.execute(sql2)
        for job2 in contents2:
            info2 = [job2[0], job2[1], '有空值']
            info_lst.append(info2)
        print('\n---------开始检查内容字段是否有input标签--------')
        contents3 = cursor.execute(sql3)
        for job3 in contents3:
            info3 = add_list(job3, '内容有Input标签没有去除')
            info_lst.append(info3)
        print('\n---------开始检查内容字段是否有截断标签（开头处）--------')
        contents4 = cursor.execute(sql4)
        for job4 in contents4:
            content = job4[2]
            if isinstance(content, str):
                result = re.match('^.*?>', content)
                if result != None:
                    if result.group(0).count('<') != result.group(0).count('>'
                        ):
                        info4 = add_list(job4, '内容可能有截断标签')
                        info_lst.append(info4)
                    else:
                        pass
                else:
                    pass
            else:
                pass
        print('\n---------开始检查采集内容是否不为标讯（仅做参考）--------')
        contents5 = cursor.execute(sql5)
        for job5 in contents5:
            title = job5[1]
            content = job5[2]
            judge_value = check_title(title)
            if judge_value != 1:
                judge_value = check_bidding(content)
                if judge_value != 1:
                    info5 = add_list(job5, '采集内容不是标讯(仅做参考)')
                    info_lst.append(info5)
            else:
                pass
        print('\n---------检查标题、发布时间格式是否正确--------')
        contents6 = cursor.execute(sql6)
        for job6 in contents6:
            title = job6[1]
            publishtime = job6[2]
            judge_value1 = check_title_form(title)
            if judge_value1 == -1:
                info6 = add_list(job6, '标题前或后有空格')
                info_lst.append(info6)
            judge_value2 = check_publishtime_form(publishtime)
            if judge_value2 == -1:
                info7 = add_list(job6, '发布时间有空格')
                info_lst.append(info7)
            if judge_value2 == -2:
                info8 = add_list(job6, '发布时间格式错误')
                info_lst.append(info8)
        return info_lst
    except sqlite3.OperationalError as e:
        pass
    except NotImplementedError as e:
        print(str(e))
    finally:
        cursor.close()
        conn.close()


def check_title(s_title):
    targets = ['中标', '招标', '议标', '废标', '流标', '邀标', '询价', '谈判', '采购', '比选',
        '磋商', '成交', '竞价', '竞投', '中选', '邀请招标', '投标', '竞谈', '单一来源', '竞争性',
        '竞选', '评标', '项目结果公示', '竞标', '摇珠', '开标', '招商', '确标', '询比价', '补遗',
        '中选', '发包', '补遗', '答疑', '项目限价', '竞标', '竞选', '议价', '竟标', '招租', '询标',
        '遴选', '标段', '工程']
    not_targets = ['条例', '规定', '工作', '规范', '标准', '荣获', '招聘', '试行', '行动']
    for target in targets:
        if target in s_title:
            return 1
        else:
            pass
    return -1


def check_bidding(s_content):
    targets = ['招标条件', '项目名称', '招标范围', '投标人资格', '投标人', '招标文件', '投标', '招标人',
        '标段名称', '中标人', '评标结果', '投标', '采购人', '单一来源编号', '招标编号', '采购组织',
        '标项名称', '采购项目', '采购方式', '成交结果', '原采购公告', '采购公告', '采购内容', '合格供应商',
        '采购文件', '保证金', '开标时间', '询价项目编号', '询价供应商', '标项名称']
    for target in targets:
        if target in s_content:
            return 1
        else:
            pass
    return None


<mask token>


def check_publishtime_form(s_content):
    check_block = re.search('^\\s*|\\s*$', s_content)
    if check_block.group() != '':
        return -1
    else:
        check_form = re.match('^\\d{4}-\\d{1,2}-\\d{1,2}$', s_content)
        if check_form is None:
            return -2
        else:
            return None


def get_jobs(siteid, db_path):
    sql = 'SELECT j.jobid, j.jobname FROM "Job"j WHERE j.siteid = %s' % siteid
    conn = connect_db(db_path)
    cursor = conn.cursor()
    try:
        jobs = cursor.execute(sql)
        job_dic = {jobid: jobname for jobid, jobname in jobs}
        return job_dic
    except sqlite3.OperationalError as e:
        pass
    except NotImplementedError as e:
        print(str(e))
    finally:
        cursor.close()
        conn.close()


def check_db():
    excel_path = 'mistakes.xlsx'
    while True:
        siteid = input('input site ID = ')
        print(
            'input flag:\n1 --- check rule \n2 --- check content \n3 --- all check \n'
            )
        select_check = int(input('flag = '))
        if select_check == 1:
            check_rule(siteid=siteid, db_path=r_path)
            print('--- END')
        elif select_check == 2:
            job_dic = get_jobs(siteid=siteid, db_path=r_path)
            while True:
                print('id ', '---', 'jobname')
                for jobid, jobname in sorted(job_dic.items(), key=lambda x:
                    x[0]):
                    print(jobid, '---', jobname)
                print(
                    'input a flag:\n1 --- check all \n0 --- exit \nid --- select one job\n'
                    )
                selectid = int(input('flag = '))
                if selectid == 1:
                    info_lst = []
                    for job_id, job_name in sorted(job_dic.items(), key=lambda
                        x: x[0]):
                        print('\n--- 开始执行检查：', job_name, job_id, '\n')
                        job_path = j_path % job_id
                        job_info = [job_id, job_name]
                        check_content(job_path, info_lst, job_info)
                    wb = Workbook()
                    ws = wb.active
                    for info in info_lst:
                        ws.append(info)
                    wb.save(excel_path)
                elif selectid == 0:
                    print('--- END')
                    break
                else:
                    job_path = j_path % selectid
                    info_lst = []
                    job_info = [selectid]
                    check_content(job_path, info_lst, job_info)
        else:
            print('--- exit')
            break


def main():
    check_db()


<mask token>

<mask token>


def create_result_db(path):
    folder = os.path.exists(path)
    pass


<mask token>


def connect_db(db_path):
    return sqlite3.connect(db_path)


def check_rule(siteid, db_path):
    sqlcheck = (
        """SELECT Job.jobId, Job.JobName, Site.SiteId, Site.SiteName FROM Job, Site
    WHERE Job.SiteId = Site.SiteId
    AND Job.SiteId = %s
    LIMIT 1"""
         % siteid)
    sqldatecheck = (
        """
    SELECT jobid,jobname FROM "Job" t
    where t.siteid  in (%s)
    and (instr(t.xmldata,'ManualTimeStr="yyyy-MM-dd"')
    and JobName NOT LIKE '%%%%-A%%%%'
    )
    """
         % siteid)
    sqllabelcheck = (
        """SELECT j.jobid, j.jobname, s.siteid, s.sitename FROM "Job" j, "Site" s
    WHERE j.SiteId = S.SiteId
    and j.siteid  in (%s)
    AND (instr(j.XmlData, '<Rule LabelName="标题"')=0
    OR instr(j.XmlData, '<Rule LabelName="发布时间"')=0
    OR instr(j.XmlData, '<Rule LabelName="采购编号"')=0
    OR instr(j.XmlData, '<Rule LabelName="地区"')=0
    OR instr(j.XmlData, '<Rule LabelName="来源"')=0
    OR instr(j.XmlData, '<Rule LabelName="数据类型"')=0
    OR instr(j.XmlData, '<Rule LabelName="公告类型"')=0
    OR instr(j.XmlData, '<Rule LabelName="内容"')=0)
    """
         % siteid)
    sql2 = (
        """
        SELECT jobid,jobname FROM "Job" t
        where t.siteid  in (%s)
        and instr(xmldata,'Rule LabelName="公告类型" GetDataType="0" StartStr="" EndStr="" RegexContent="" RegexCombine="" XpathContent="" XPathAttribue="0" TextRecognitionType="0" TextRecognitionCodeReturnType="1" LengthFiltOpertar="0" LengthFiltValue="0" LabelContentMust="" LabelContentForbid="" FileUrlMust="" FileSaveDir="" FileSaveFormat="" ManualType="0" ManualString="type01') =0
        and instr(xmldata,'Rule LabelName="公告类型" GetDataType="0" StartStr="" EndStr="" RegexContent="" RegexCombine="" XpathContent="" XPathAttribue="0" TextRecognitionType="0" TextRecognitionCodeReturnType="1" LengthFiltOpertar="0" LengthFiltValue="0" LabelContentMust="" LabelContentForbid="" FileUrlMust="" FileSaveDir="" FileSaveFormat="" ManualType="0" ManualString="type02') =0
        """
         % siteid)
    sql3 = (
        """
        SELECT jobid,jobname FROM "Job" t
        where t.siteid  in (%s)
        and instr(XmlData,'MaxSpiderPerNum="0" MaxOutPerNum="0"') = 0
        """
         % siteid)
    sql4 = (
        """
        SELECT jobid,jobname FROM "Job" t
        where t.siteid  in (%s)
        and instr(t.XmlData,'CheckUrlRepeat=""') = 0
        """
         % siteid)
    sql5 = (
        """
        SELECT jobid,jobname FROM "Job" t
        where t.siteid  in (%s)
        and(instr(t.XmlData,'new="201') > 0
        or instr(t.XmlData,'<FillBothEnd Start="201') > 0)
        """
         % siteid)
    sql6 = (
        """
        SELECT jobid,jobname FROM "Job" t
        where t.siteid  in (%s)
        and( instr(xmldata,'UrlRepeat=""') =0
        or instr(xmldata,'CheckUrlRepeat=""') =0)
        """
         % siteid)
    conn = connect_db(db_path)
    cursor = conn.cursor()
    try:
        rules = cursor.execute(sqlcheck)
        for rule_info in rules:
            print('校验信息：\n ', rule_info, '\n----------------')
        labelcheck = cursor.execute(sqllabelcheck)
        for item1 in labelcheck:
            print(item1, '缺少标签')
        sdatacheck = cursor.execute(sqldatecheck)
        for item2 in sdatacheck:
            print(item2, '系统时间未标记-A')
        row2 = cursor.execute(sql2)
        for x2 in row2:
            print(x2, '公告类型写死')
        row3 = cursor.execute(sql3)
        for x3 in row3:
            print(x3, '误设最大页数')
        row4 = cursor.execute(sql4)
        for x4 in row4:
            print(x4, '检查重复没有勾上')
        row5 = cursor.execute(sql5)
        for x5 in row5:
            print(x5, '规则里面有前缀后缀')
        row6 = cursor.execute(sql6)
        for x6 in row6:
            print(x6, '检查网址重复未选上')
    except NotImplementedError as error:
        print(str(error))
    finally:
        cursor.close()
        conn.close()


def add_list(job, error_info):
    return [job[0], job[1], error_info]


def open_excel():
    wb = Workbook()
    ws = wb.active
    return ws


def check_content(job_path, info_lst, job_info):
    sql1 = """SELECT ID, 标题, 发布时间 FROM content
    WHERE 已采 = 1
    AND (标题 LIKE '%<%'
    OR 标题 LIKE '%>%'
    OR 发布时间 LIKE '%<%'
    OR 发布时间 LIKE '%>%')
    """
    sql2 = """SELECT ID, 标题, 内容, 发布时间 FROM Content WHERE 已采=1
    AND (标题 IS NULL
    OR 内容 IS NULL
    OR 发布时间 IS NULL
    OR 内容=''
    OR 发布时间=''
    OR 标题='')
    """
    sql3 = """SELECT ID, 标题 FROM Content WHERE 已采=1
    AND 内容 LIKE '%<input%'
     """
    sql4 = 'SELECT ID, 标题, 内容 FROM Content WHERE 已采=1\n    '
    sql5 = """SELECT ID, 标题, 内容 FROM Content WHERE 已采=1
    AND 标题 IS NOT NULL
    AND 标题 != ''
    AND 内容 IS NOT NULL
    AND 内容 != ''
    """
    sql6 = """SELECT ID, 标题, 发布时间 FROM Content WHERE 已采=1
    AND 标题 IS NOT NULL
    AND 标题 != ''
    AND 发布时间 IS NOT NULL
    AND 发布时间 != ''
    """
    conn = connect_db(job_path)
    cursor = conn.cursor()
    info_lst.append(['任务ID', '任务名', '错误信息'])
    info_lst.append(job_info)
    try:
        print('---------开始检查非内容字段是否有HTML标签--------')
        contents1 = cursor.execute(sql1)
        for job1 in contents1:
            info1 = add_list(job1, '标题或时间有HTML标签')
            info_lst.append(info1)
        print('\n---------开始检查内容、标题、发布时间是否有空--------')
        contents2 = cursor.execute(sql2)
        for job2 in contents2:
            info2 = [job2[0], job2[1], '有空值']
            info_lst.append(info2)
        print('\n---------开始检查内容字段是否有input标签--------')
        contents3 = cursor.execute(sql3)
        for job3 in contents3:
            info3 = add_list(job3, '内容有Input标签没有去除')
            info_lst.append(info3)
        print('\n---------开始检查内容字段是否有截断标签（开头处）--------')
        contents4 = cursor.execute(sql4)
        for job4 in contents4:
            content = job4[2]
            if isinstance(content, str):
                result = re.match('^.*?>', content)
                if result != None:
                    if result.group(0).count('<') != result.group(0).count('>'
                        ):
                        info4 = add_list(job4, '内容可能有截断标签')
                        info_lst.append(info4)
                    else:
                        pass
                else:
                    pass
            else:
                pass
        print('\n---------开始检查采集内容是否不为标讯（仅做参考）--------')
        contents5 = cursor.execute(sql5)
        for job5 in contents5:
            title = job5[1]
            content = job5[2]
            judge_value = check_title(title)
            if judge_value != 1:
                judge_value = check_bidding(content)
                if judge_value != 1:
                    info5 = add_list(job5, '采集内容不是标讯(仅做参考)')
                    info_lst.append(info5)
            else:
                pass
        print('\n---------检查标题、发布时间格式是否正确--------')
        contents6 = cursor.execute(sql6)
        for job6 in contents6:
            title = job6[1]
            publishtime = job6[2]
            judge_value1 = check_title_form(title)
            if judge_value1 == -1:
                info6 = add_list(job6, '标题前或后有空格')
                info_lst.append(info6)
            judge_value2 = check_publishtime_form(publishtime)
            if judge_value2 == -1:
                info7 = add_list(job6, '发布时间有空格')
                info_lst.append(info7)
            if judge_value2 == -2:
                info8 = add_list(job6, '发布时间格式错误')
                info_lst.append(info8)
        return info_lst
    except sqlite3.OperationalError as e:
        pass
    except NotImplementedError as e:
        print(str(e))
    finally:
        cursor.close()
        conn.close()


def check_title(s_title):
    targets = ['中标', '招标', '议标', '废标', '流标', '邀标', '询价', '谈判', '采购', '比选',
        '磋商', '成交', '竞价', '竞投', '中选', '邀请招标', '投标', '竞谈', '单一来源', '竞争性',
        '竞选', '评标', '项目结果公示', '竞标', '摇珠', '开标', '招商', '确标', '询比价', '补遗',
        '中选', '发包', '补遗', '答疑', '项目限价', '竞标', '竞选', '议价', '竟标', '招租', '询标',
        '遴选', '标段', '工程']
    not_targets = ['条例', '规定', '工作', '规范', '标准', '荣获', '招聘', '试行', '行动']
    for target in targets:
        if target in s_title:
            return 1
        else:
            pass
    return -1


def check_bidding(s_content):
    targets = ['招标条件', '项目名称', '招标范围', '投标人资格', '投标人', '招标文件', '投标', '招标人',
        '标段名称', '中标人', '评标结果', '投标', '采购人', '单一来源编号', '招标编号', '采购组织',
        '标项名称', '采购项目', '采购方式', '成交结果', '原采购公告', '采购公告', '采购内容', '合格供应商',
        '采购文件', '保证金', '开标时间', '询价项目编号', '询价供应商', '标项名称']
    for target in targets:
        if target in s_content:
            return 1
        else:
            pass
    return None


<mask token>


def check_publishtime_form(s_content):
    check_block = re.search('^\\s*|\\s*$', s_content)
    if check_block.group() != '':
        return -1
    else:
        check_form = re.match('^\\d{4}-\\d{1,2}-\\d{1,2}$', s_content)
        if check_form is None:
            return -2
        else:
            return None


def get_jobs(siteid, db_path):
    sql = 'SELECT j.jobid, j.jobname FROM "Job"j WHERE j.siteid = %s' % siteid
    conn = connect_db(db_path)
    cursor = conn.cursor()
    try:
        jobs = cursor.execute(sql)
        job_dic = {jobid: jobname for jobid, jobname in jobs}
        return job_dic
    except sqlite3.OperationalError as e:
        pass
    except NotImplementedError as e:
        print(str(e))
    finally:
        cursor.close()
        conn.close()


def check_db():
    excel_path = 'mistakes.xlsx'
    while True:
        siteid = input('input site ID = ')
        print(
            'input flag:\n1 --- check rule \n2 --- check content \n3 --- all check \n'
            )
        select_check = int(input('flag = '))
        if select_check == 1:
            check_rule(siteid=siteid, db_path=r_path)
            print('--- END')
        elif select_check == 2:
            job_dic = get_jobs(siteid=siteid, db_path=r_path)
            while True:
                print('id ', '---', 'jobname')
                for jobid, jobname in sorted(job_dic.items(), key=lambda x:
                    x[0]):
                    print(jobid, '---', jobname)
                print(
                    'input a flag:\n1 --- check all \n0 --- exit \nid --- select one job\n'
                    )
                selectid = int(input('flag = '))
                if selectid == 1:
                    info_lst = []
                    for job_id, job_name in sorted(job_dic.items(), key=lambda
                        x: x[0]):
                        print('\n--- 开始执行检查：', job_name, job_id, '\n')
                        job_path = j_path % job_id
                        job_info = [job_id, job_name]
                        check_content(job_path, info_lst, job_info)
                    wb = Workbook()
                    ws = wb.active
                    for info in info_lst:
                        ws.append(info)
                    wb.save(excel_path)
                elif selectid == 0:
                    print('--- END')
                    break
                else:
                    job_path = j_path % selectid
                    info_lst = []
                    job_info = [selectid]
                    check_content(job_path, info_lst, job_info)
        else:
            print('--- exit')
            break


def main():
    check_db()


<mask token>

<mask token>


def create_result_db(path):
    folder = os.path.exists(path)
    pass


r_path = 'D:\\7个火车头\\标讯采集\\Configuration\\config.db3'
j_path = 'D:\\7个火车头\\标讯采集\\Data\\%s\\SpiderResult.db3'
result_db_path = 'd:\\checkresult'


def connect_db(db_path):
    return sqlite3.connect(db_path)


def check_rule(siteid, db_path):
    sqlcheck = (
        """SELECT Job.jobId, Job.JobName, Site.SiteId, Site.SiteName FROM Job, Site
    WHERE Job.SiteId = Site.SiteId
    AND Job.SiteId = %s
    LIMIT 1"""
         % siteid)
    sqldatecheck = (
        """
    SELECT jobid,jobname FROM "Job" t
    where t.siteid  in (%s)
    and (instr(t.xmldata,'ManualTimeStr="yyyy-MM-dd"')
    and JobName NOT LIKE '%%%%-A%%%%'
    )
    """
         % siteid)
    sqllabelcheck = (
        """SELECT j.jobid, j.jobname, s.siteid, s.sitename FROM "Job" j, "Site" s
    WHERE j.SiteId = S.SiteId
    and j.siteid  in (%s)
    AND (instr(j.XmlData, '<Rule LabelName="标题"')=0
    OR instr(j.XmlData, '<Rule LabelName="发布时间"')=0
    OR instr(j.XmlData, '<Rule LabelName="采购编号"')=0
    OR instr(j.XmlData, '<Rule LabelName="地区"')=0
    OR instr(j.XmlData, '<Rule LabelName="来源"')=0
    OR instr(j.XmlData, '<Rule LabelName="数据类型"')=0
    OR instr(j.XmlData, '<Rule LabelName="公告类型"')=0
    OR instr(j.XmlData, '<Rule LabelName="内容"')=0)
    """
         % siteid)
    sql2 = (
        """
        SELECT jobid,jobname FROM "Job" t
        where t.siteid  in (%s)
        and instr(xmldata,'Rule LabelName="公告类型" GetDataType="0" StartStr="" EndStr="" RegexContent="" RegexCombine="" XpathContent="" XPathAttribue="0" TextRecognitionType="0" TextRecognitionCodeReturnType="1" LengthFiltOpertar="0" LengthFiltValue="0" LabelContentMust="" LabelContentForbid="" FileUrlMust="" FileSaveDir="" FileSaveFormat="" ManualType="0" ManualString="type01') =0
        and instr(xmldata,'Rule LabelName="公告类型" GetDataType="0" StartStr="" EndStr="" RegexContent="" RegexCombine="" XpathContent="" XPathAttribue="0" TextRecognitionType="0" TextRecognitionCodeReturnType="1" LengthFiltOpertar="0" LengthFiltValue="0" LabelContentMust="" LabelContentForbid="" FileUrlMust="" FileSaveDir="" FileSaveFormat="" ManualType="0" ManualString="type02') =0
        """
         % siteid)
    sql3 = (
        """
        SELECT jobid,jobname FROM "Job" t
        where t.siteid  in (%s)
        and instr(XmlData,'MaxSpiderPerNum="0" MaxOutPerNum="0"') = 0
        """
         % siteid)
    sql4 = (
        """
        SELECT jobid,jobname FROM "Job" t
        where t.siteid  in (%s)
        and instr(t.XmlData,'CheckUrlRepeat=""') = 0
        """
         % siteid)
    sql5 = (
        """
        SELECT jobid,jobname FROM "Job" t
        where t.siteid  in (%s)
        and(instr(t.XmlData,'new="201') > 0
        or instr(t.XmlData,'<FillBothEnd Start="201') > 0)
        """
         % siteid)
    sql6 = (
        """
        SELECT jobid,jobname FROM "Job" t
        where t.siteid  in (%s)
        and( instr(xmldata,'UrlRepeat=""') =0
        or instr(xmldata,'CheckUrlRepeat=""') =0)
        """
         % siteid)
    conn = connect_db(db_path)
    cursor = conn.cursor()
    try:
        rules = cursor.execute(sqlcheck)
        for rule_info in rules:
            print('校验信息：\n ', rule_info, '\n----------------')
        labelcheck = cursor.execute(sqllabelcheck)
        for item1 in labelcheck:
            print(item1, '缺少标签')
        sdatacheck = cursor.execute(sqldatecheck)
        for item2 in sdatacheck:
            print(item2, '系统时间未标记-A')
        row2 = cursor.execute(sql2)
        for x2 in row2:
            print(x2, '公告类型写死')
        row3 = cursor.execute(sql3)
        for x3 in row3:
            print(x3, '误设最大页数')
        row4 = cursor.execute(sql4)
        for x4 in row4:
            print(x4, '检查重复没有勾上')
        row5 = cursor.execute(sql5)
        for x5 in row5:
            print(x5, '规则里面有前缀后缀')
        row6 = cursor.execute(sql6)
        for x6 in row6:
            print(x6, '检查网址重复未选上')
    except NotImplementedError as error:
        print(str(error))
    finally:
        cursor.close()
        conn.close()


def add_list(job, error_info):
    return [job[0], job[1], error_info]


def open_excel():
    wb = Workbook()
    ws = wb.active
    return ws


def check_content(job_path, info_lst, job_info):
    sql1 = """SELECT ID, 标题, 发布时间 FROM content
    WHERE 已采 = 1
    AND (标题 LIKE '%<%'
    OR 标题 LIKE '%>%'
    OR 发布时间 LIKE '%<%'
    OR 发布时间 LIKE '%>%')
    """
    sql2 = """SELECT ID, 标题, 内容, 发布时间 FROM Content WHERE 已采=1
    AND (标题 IS NULL
    OR 内容 IS NULL
    OR 发布时间 IS NULL
    OR 内容=''
    OR 发布时间=''
    OR 标题='')
    """
    sql3 = """SELECT ID, 标题 FROM Content WHERE 已采=1
    AND 内容 LIKE '%<input%'
     """
    sql4 = 'SELECT ID, 标题, 内容 FROM Content WHERE 已采=1\n    '
    sql5 = """SELECT ID, 标题, 内容 FROM Content WHERE 已采=1
    AND 标题 IS NOT NULL
    AND 标题 != ''
    AND 内容 IS NOT NULL
    AND 内容 != ''
    """
    sql6 = """SELECT ID, 标题, 发布时间 FROM Content WHERE 已采=1
    AND 标题 IS NOT NULL
    AND 标题 != ''
    AND 发布时间 IS NOT NULL
    AND 发布时间 != ''
    """
    conn = connect_db(job_path)
    cursor = conn.cursor()
    info_lst.append(['任务ID', '任务名', '错误信息'])
    info_lst.append(job_info)
    try:
        print('---------开始检查非内容字段是否有HTML标签--------')
        contents1 = cursor.execute(sql1)
        for job1 in contents1:
            info1 = add_list(job1, '标题或时间有HTML标签')
            info_lst.append(info1)
        print('\n---------开始检查内容、标题、发布时间是否有空--------')
        contents2 = cursor.execute(sql2)
        for job2 in contents2:
            info2 = [job2[0], job2[1], '有空值']
            info_lst.append(info2)
        print('\n---------开始检查内容字段是否有input标签--------')
        contents3 = cursor.execute(sql3)
        for job3 in contents3:
            info3 = add_list(job3, '内容有Input标签没有去除')
            info_lst.append(info3)
        print('\n---------开始检查内容字段是否有截断标签（开头处）--------')
        contents4 = cursor.execute(sql4)
        for job4 in contents4:
            content = job4[2]
            if isinstance(content, str):
                result = re.match('^.*?>', content)
                if result != None:
                    if result.group(0).count('<') != result.group(0).count('>'
                        ):
                        info4 = add_list(job4, '内容可能有截断标签')
                        info_lst.append(info4)
                    else:
                        pass
                else:
                    pass
            else:
                pass
        print('\n---------开始检查采集内容是否不为标讯（仅做参考）--------')
        contents5 = cursor.execute(sql5)
        for job5 in contents5:
            title = job5[1]
            content = job5[2]
            judge_value = check_title(title)
            if judge_value != 1:
                judge_value = check_bidding(content)
                if judge_value != 1:
                    info5 = add_list(job5, '采集内容不是标讯(仅做参考)')
                    info_lst.append(info5)
            else:
                pass
        print('\n---------检查标题、发布时间格式是否正确--------')
        contents6 = cursor.execute(sql6)
        for job6 in contents6:
            title = job6[1]
            publishtime = job6[2]
            judge_value1 = check_title_form(title)
            if judge_value1 == -1:
                info6 = add_list(job6, '标题前或后有空格')
                info_lst.append(info6)
            judge_value2 = check_publishtime_form(publishtime)
            if judge_value2 == -1:
                info7 = add_list(job6, '发布时间有空格')
                info_lst.append(info7)
            if judge_value2 == -2:
                info8 = add_list(job6, '发布时间格式错误')
                info_lst.append(info8)
        return info_lst
    except sqlite3.OperationalError as e:
        pass
    except NotImplementedError as e:
        print(str(e))
    finally:
        cursor.close()
        conn.close()


def check_title(s_title):
    targets = ['中标', '招标', '议标', '废标', '流标', '邀标', '询价', '谈判', '采购', '比选',
        '磋商', '成交', '竞价', '竞投', '中选', '邀请招标', '投标', '竞谈', '单一来源', '竞争性',
        '竞选', '评标', '项目结果公示', '竞标', '摇珠', '开标', '招商', '确标', '询比价', '补遗',
        '中选', '发包', '补遗', '答疑', '项目限价', '竞标', '竞选', '议价', '竟标', '招租', '询标',
        '遴选', '标段', '工程']
    not_targets = ['条例', '规定', '工作', '规范', '标准', '荣获', '招聘', '试行', '行动']
    for target in targets:
        if target in s_title:
            return 1
        else:
            pass
    return -1


def check_bidding(s_content):
    targets = ['招标条件', '项目名称', '招标范围', '投标人资格', '投标人', '招标文件', '投标', '招标人',
        '标段名称', '中标人', '评标结果', '投标', '采购人', '单一来源编号', '招标编号', '采购组织',
        '标项名称', '采购项目', '采购方式', '成交结果', '原采购公告', '采购公告', '采购内容', '合格供应商',
        '采购文件', '保证金', '开标时间', '询价项目编号', '询价供应商', '标项名称']
    for target in targets:
        if target in s_content:
            return 1
        else:
            pass
    return None


def check_title_form(s_content):
    result = re.search('^\\s*|\\s*$', s_content)
    if result.group() != '':
        return -1
    else:
        return None


def check_publishtime_form(s_content):
    check_block = re.search('^\\s*|\\s*$', s_content)
    if check_block.group() != '':
        return -1
    else:
        check_form = re.match('^\\d{4}-\\d{1,2}-\\d{1,2}$', s_content)
        if check_form is None:
            return -2
        else:
            return None


def get_jobs(siteid, db_path):
    sql = 'SELECT j.jobid, j.jobname FROM "Job"j WHERE j.siteid = %s' % siteid
    conn = connect_db(db_path)
    cursor = conn.cursor()
    try:
        jobs = cursor.execute(sql)
        job_dic = {jobid: jobname for jobid, jobname in jobs}
        return job_dic
    except sqlite3.OperationalError as e:
        pass
    except NotImplementedError as e:
        print(str(e))
    finally:
        cursor.close()
        conn.close()


def check_db():
    excel_path = 'mistakes.xlsx'
    while True:
        siteid = input('input site ID = ')
        print(
            'input flag:\n1 --- check rule \n2 --- check content \n3 --- all check \n'
            )
        select_check = int(input('flag = '))
        if select_check == 1:
            check_rule(siteid=siteid, db_path=r_path)
            print('--- END')
        elif select_check == 2:
            job_dic = get_jobs(siteid=siteid, db_path=r_path)
            while True:
                print('id ', '---', 'jobname')
                for jobid, jobname in sorted(job_dic.items(), key=lambda x:
                    x[0]):
                    print(jobid, '---', jobname)
                print(
                    'input a flag:\n1 --- check all \n0 --- exit \nid --- select one job\n'
                    )
                selectid = int(input('flag = '))
                if selectid == 1:
                    info_lst = []
                    for job_id, job_name in sorted(job_dic.items(), key=lambda
                        x: x[0]):
                        print('\n--- 开始执行检查：', job_name, job_id, '\n')
                        job_path = j_path % job_id
                        job_info = [job_id, job_name]
                        check_content(job_path, info_lst, job_info)
                    wb = Workbook()
                    ws = wb.active
                    for info in info_lst:
                        ws.append(info)
                    wb.save(excel_path)
                elif selectid == 0:
                    print('--- END')
                    break
                else:
                    job_path = j_path % selectid
                    info_lst = []
                    job_info = [selectid]
                    check_content(job_path, info_lst, job_info)
        else:
            print('--- exit')
            break


def main():
    check_db()


if __name__ == '__main__':
    main()

# -*- coding: utf-8 -*-
"""
@author: MonsterHe
@contact: yuntian.hee@gmail.com
@version: python3.5
@time: 2018/11/3 10:26
@tools: Pycharm 2018.1
"""
"""
update:
是错误信息输出到表格

"""

"""
当前检查的功能：（*标记的为未实现）
1.非内容字段包含HTML标签  
2.内容标签截断
3.标题、发布时间格式错误
4.标签采集结果为空
6.非标讯检查
11.内容中包含input标签，未把标签内的值替换出来（在后续流程中标签会被去掉，这样会导致未提取的值也会被去掉造成内容不全
*5.采集非本站数据
*7.数据处理（空格替换为空）
*8.cookie不是用脚本获取的
*9.任务备注里面包含 ""或''
*10.内容是json格式或源页面json解析异常而未处理
"""

import sqlite3
import re
import os
from openpyxl import load_workbook
from openpyxl import Workbook
from collections import OrderedDict


def create_result_db(path):
    folder = os.path.exists(path)

    pass


# 配置数据库路径
r_path = r'''D:\7个火车头\标讯采集\Configuration\config.db3'''
j_path = r'''D:\7个火车头\标讯采集\Data\%s\SpiderResult.db3'''
result_db_path = r'd:\checkresult'


# excel_path = r"mistakes.xlsx"

# 连接数据库
def connect_db(db_path):
    return sqlite3.connect(db_path)


# 检查规则
def check_rule(siteid, db_path):
    # 校验是否为当前检查的组
    sqlcheck = '''SELECT Job.jobId, Job.JobName, Site.SiteId, Site.SiteName FROM Job, Site
    WHERE Job.SiteId = Site.SiteId
    AND Job.SiteId = %s
    LIMIT 1''' % siteid

    # 检查发布时间为系统时间是否标记-A
    sqldatecheck = """
    SELECT jobid,jobname FROM "Job" t
    where t.siteid  in (%s)
    and (instr(t.xmldata,'ManualTimeStr="yyyy-MM-dd"')
    and JobName NOT LIKE '%%%%-A%%%%'
    )
    """ % siteid

    # 检查标签是否完整
    sqllabelcheck = '''SELECT j.jobid, j.jobname, s.siteid, s.sitename FROM "Job" j, "Site" s
    WHERE j.SiteId = S.SiteId
    and j.siteid  in (%s)
    AND (instr(j.XmlData, '<Rule LabelName="标题"')=0
    OR instr(j.XmlData, '<Rule LabelName="发布时间"')=0
    OR instr(j.XmlData, '<Rule LabelName="采购编号"')=0
    OR instr(j.XmlData, '<Rule LabelName="地区"')=0
    OR instr(j.XmlData, '<Rule LabelName="来源"')=0
    OR instr(j.XmlData, '<Rule LabelName="数据类型"')=0
    OR instr(j.XmlData, '<Rule LabelName="公告类型"')=0
    OR instr(j.XmlData, '<Rule LabelName="内容"')=0)
    ''' % siteid

    sql2 = """
        SELECT jobid,jobname FROM "Job" t
        where t.siteid  in (%s)
        and instr(xmldata,'Rule LabelName="公告类型" GetDataType="0" StartStr="" EndStr="" RegexContent="" RegexCombine="" XpathContent="" XPathAttribue="0" TextRecognitionType="0" TextRecognitionCodeReturnType="1" LengthFiltOpertar="0" LengthFiltValue="0" LabelContentMust="" LabelContentForbid="" FileUrlMust="" FileSaveDir="" FileSaveFormat="" ManualType="0" ManualString="type01') =0
        and instr(xmldata,'Rule LabelName="公告类型" GetDataType="0" StartStr="" EndStr="" RegexContent="" RegexCombine="" XpathContent="" XPathAttribue="0" TextRecognitionType="0" TextRecognitionCodeReturnType="1" LengthFiltOpertar="0" LengthFiltValue="0" LabelContentMust="" LabelContentForbid="" FileUrlMust="" FileSaveDir="" FileSaveFormat="" ManualType="0" ManualString="type02') =0
        """ % siteid
    sql3 = """
        SELECT jobid,jobname FROM "Job" t
        where t.siteid  in (%s)
        and instr(XmlData,'MaxSpiderPerNum="0" MaxOutPerNum="0"') = 0
        """ % siteid
    sql4 = """
        SELECT jobid,jobname FROM "Job" t
        where t.siteid  in (%s)
        and instr(t.XmlData,'CheckUrlRepeat=""') = 0
        """ % siteid
    sql5 = """
        SELECT jobid,jobname FROM "Job" t
        where t.siteid  in (%s)
        and(instr(t.XmlData,'new="201') > 0
        or instr(t.XmlData,'<FillBothEnd Start="201') > 0)
        """ % siteid
    sql6 = """
        SELECT jobid,jobname FROM "Job" t
        where t.siteid  in (%s)
        and( instr(xmldata,'UrlRepeat=""') =0
        or instr(xmldata,'CheckUrlRepeat=""') =0)
        """ % siteid

    # 连接数据库并获得cursor
    conn = connect_db(db_path)
    cursor = conn.cursor()
    try:
        rules = cursor.execute(sqlcheck)
        for rule_info in rules:
            print("校验信息：\n ", rule_info, "\n----------------")

        labelcheck = cursor.execute(sqllabelcheck)
        for item1 in labelcheck:
            print(item1, '缺少标签')
        sdatacheck = cursor.execute(sqldatecheck)
        for item2 in sdatacheck:
            print(item2, '系统时间未标记-A')
        row2 = cursor.execute(sql2)
        for x2 in row2:
            print(x2, '公告类型写死')
        row3 = cursor.execute(sql3)
        for x3 in row3:
            print(x3, '误设最大页数')
        row4 = cursor.execute(sql4)
        for x4 in row4:
            print(x4, '检查重复没有勾上')
        row5 = cursor.execute(sql5)
        for x5 in row5:
            print(x5, '规则里面有前缀后缀')
        row6 = cursor.execute(sql6)
        for x6 in row6:
            print(x6, '检查网址重复未选上')
    except NotImplementedError as error:
        print(str(error))
    finally:
        cursor.close()
        conn.close()


# 生成一个列表
def add_list(job, error_info):
    return [job[0], job[1], error_info]


def open_excel():
    wb = Workbook()
    ws = wb.active
    return ws


# 检查内容
def check_content(job_path, info_lst, job_info):
    # 检查非内容字段有无截断HTML标签
    sql1 = '''SELECT ID, 标题, 发布时间 FROM content
    WHERE 已采 = 1
    AND (标题 LIKE '%<%'
    OR 标题 LIKE '%>%'
    OR 发布时间 LIKE '%<%'
    OR 发布时间 LIKE '%>%')
    '''

    # 检查有空内容的字段
    sql2 = '''SELECT ID, 标题, 内容, 发布时间 FROM Content WHERE 已采=1
    AND (标题 IS NULL
    OR 内容 IS NULL
    OR 发布时间 IS NULL
    OR 内容=''
    OR 发布时间=''
    OR 标题='')
    '''

    # 检查内容标签中包含有input标签
    sql3 = '''SELECT ID, 标题 FROM Content WHERE 已采=1
    AND 内容 LIKE '%<input%'
     '''
    # 检查内容中有截断HTML标签
    sql4 = '''SELECT ID, 标题, 内容 FROM Content WHERE 已采=1
    '''
    # 检查采集结果是否不为标讯
    sql5 = '''SELECT ID, 标题, 内容 FROM Content WHERE 已采=1
    AND 标题 IS NOT NULL
    AND 标题 != ''
    AND 内容 IS NOT NULL
    AND 内容 != ''
    '''

    # 检查标题或发布时间格式是否正确
    sql6 = '''SELECT ID, 标题, 发布时间 FROM Content WHERE 已采=1
    AND 标题 IS NOT NULL
    AND 标题 != ''
    AND 发布时间 IS NOT NULL
    AND 发布时间 != ''
    '''

    conn = connect_db(job_path)
    cursor = conn.cursor()
    # 列出任务信息
    info_lst.append(["任务ID", "任务名", "错误信息"])
    info_lst.append(job_info)
    try:
        print('---------开始检查非内容字段是否有HTML标签--------')
        contents1 = cursor.execute(sql1)
        for job1 in contents1:
            info1 = add_list(job1, "标题或时间有HTML标签")
            info_lst.append(info1)
            # print('{}\t {}\t标题或时间有HTML标签'.format(job1[0], job1[1]))

        print('\n---------开始检查内容、标题、发布时间是否有空--------')
        contents2 = cursor.execute(sql2)
        for job2 in contents2:
            # info = add_list(job2,"内容或标题或发布时间有空值")
            info2 = [job2[0], job2[1], "有空值"]
            info_lst.append(info2)
            # print('{}\t {}\t内容或标题或发布时间有空值'.format(job2[0], job2[1]))

        print('\n---------开始检查内容字段是否有input标签--------')
        contents3 = cursor.execute(sql3)
        for job3 in contents3:
            info3 = add_list(job3, "内容有Input标签没有去除")
            info_lst.append(info3)
            # print('{}\t {}\t内容有input标签没有去除'.format(job3[0], job3[1]))

        print('\n---------开始检查内容字段是否有截断标签（开头处）--------')
        # 检查内容是否有截断HTML标签（目前只能检查开头）
        contents4 = cursor.execute(sql4)
        for job4 in contents4:
            content = job4[2]
            if isinstance(content, str):
                result = re.match(r"^.*?>", content)
                if result != None:
                    if result.group(0).count('<') != result.group(0).count('>'):
                        info4 = add_list(job4, "内容可能有截断标签")
                        info_lst.append(info4)
                        # print("{}\t {}\t内容可能有截断标签".format(job4[0], job4[1]))
                    else:
                        pass
                else:
                    pass
            else:
                pass

        print('\n---------开始检查采集内容是否不为标讯（仅做参考）--------')
        contents5 = cursor.execute(sql5)
        for job5 in contents5:
            title = job5[1]
            content = job5[2]
            judge_value = check_title(title)
            # 标题若无法判断是否为标讯，则进入内容判断
            if judge_value != 1:
                judge_value = check_bidding(content)
                if judge_value != 1:
                    info5 = add_list(job5, "采集内容不是标讯(仅做参考)")
                    info_lst.append(info5)
                    # print('{}\t {}\t {tip}'.format(job5[0], job5[1], tip='采集内容不是标讯(仅做参考)'))
            else:
                pass
        print('\n---------检查标题、发布时间格式是否正确--------')
        contents6 = cursor.execute(sql6)
        for job6 in contents6:
            title = job6[1]
            publishtime = job6[2]
            judge_value1 = check_title_form(title)
            if judge_value1 == -1:
                info6 = add_list(job6, "标题前或后有空格")
                info_lst.append(info6)
                # print('{}\t {}\t {tip}'.format(job6[0], job6[1], tip='标题前或后有空格'))
            judge_value2 = check_publishtime_form(publishtime)
            if judge_value2 == -1:
                info7 = add_list(job6, "发布时间有空格")
                info_lst.append(info7)
                # print('{}\t {}\t {tip}'.format(job6[0], job6[1], tip='发布时间有空格'))
            if judge_value2 == -2:
                info8 = add_list(job6, "发布时间格式错误")
                info_lst.append(info8)
                # print('{}\t {}\t {tip}'.format(job6[0], job6[1], tip='发布时间格式错误'))

        # 写入表格
        return info_lst
        # for mistake in infos:
        #     print(mistake)
        #     ws.append(mistake)
        # wb.save(excel_path)

    except sqlite3.OperationalError as e:
        pass
    except NotImplementedError as e:
        print(str(e))
    finally:
        cursor.close()
        conn.close()


# 检查非标讯
def check_title(s_title):
    targets = ['中标', '招标', '议标', '废标', '流标', '邀标', '询价', '谈判', '采购',
               '比选', '磋商', '成交', '竞价', '竞投', '中选', '邀请招标', '投标',
               '竞谈', '单一来源', '竞争性', '竞选', '评标', '项目结果公示', '竞标',
               '摇珠', '开标', '招商', '确标', '询比价', '补遗', '中选', '发包', '补遗',
               '答疑', '项目限价', '竞标', '竞选', '议价', '竟标', '招租', '询标', '遴选', '标段', '工程'
               ]

    not_targets = ['条例', '规定', '工作', '规范', '标准', '荣获', '招聘', '试行', '行动']

    for target in targets:
        if target in s_title:
            return 1
        else:
            pass
    return -1


# 检查标讯是否被过滤
def check_bidding(s_content):
    targets = ['招标条件', '项目名称', '招标范围', '投标人资格', '投标人', '招标文件', '投标',
               '招标人', '标段名称', '中标人', '评标结果', '投标', '采购人', '单一来源编号', '招标编号',
               '采购组织', '标项名称', '采购项目', '采购方式', '成交结果', '原采购公告', '采购公告',
               '采购内容', '合格供应商', '采购文件', '保证金', '开标时间', '询价项目编号', '询价供应商',
               '标项名称', ]
    for target in targets:
        if target in s_content:
            return 1
        else:
            pass
    return None


# 检查标题格式
def check_title_form(s_content):
    result = re.search(r'''^\s*|\s*$''', s_content)
    if result.group() != '':
        return -1
    else:
        return None


# 检查发布时间格式
def check_publishtime_form(s_content):
    # 首尾空格检查
    check_block = re.search(r'''^\s*|\s*$''', s_content)
    if check_block.group() != '':
        return -1
    else:
        # 格式检查
        check_form = re.match(r'''^\d{4}-\d{1,2}-\d{1,2}$''', s_content)
        if check_form is None:
            return -2
        else:
            return None


def get_jobs(siteid, db_path):
    # job_dic = OrderedDict()
    sql = '''SELECT j.jobid, j.jobname FROM "Job"j WHERE j.siteid = %s''' % siteid
    conn = connect_db(db_path)
    cursor = conn.cursor()
    try:
        jobs = cursor.execute(sql)
        job_dic = {jobid: jobname for (jobid, jobname) in jobs}
        return job_dic
    except sqlite3.OperationalError as e:
        pass
    except NotImplementedError as e:
        print(str(e))
    finally:
        cursor.close()
        conn.close()


def check_db():
    excel_path = "mistakes.xlsx"  # 表格路径
    while True:
        siteid = input('input site ID = ')
        print('input flag:\n1 --- check rule \n2 --- check content \n3 --- all check \n')
        select_check = int(input('flag = '))
        if select_check == 1:
            # 检查规则
            check_rule(siteid=siteid, db_path=r_path)
            print('--- END')
        elif select_check == 2:
            # 选择检查某一个任务，或全部任务
            job_dic = get_jobs(siteid=siteid, db_path=r_path)
            while True:
                print('id ', '---', 'jobname')
                for jobid, jobname in sorted(job_dic.items(), key=lambda x:x[0]):
                    print(jobid, '---', jobname)
                print('input a flag:\n1 --- check all \n0 --- exit \nid --- select one job\n')
                selectid = int(input('flag = '))
                if selectid == 1:
                    info_lst = []  # 存储错误信息的列表
                    for job_id, job_name in sorted(job_dic.items(), key=lambda x:x[0]):
                        print("\n--- 开始执行检查：", job_name, job_id, "\n")
                        job_path = j_path % job_id
                        job_info = [job_id, job_name]
                        check_content(job_path, info_lst, job_info)
                    wb = Workbook()
                    ws = wb.active
                    for info in info_lst:
                        ws.append(info)
                    wb.save(excel_path)

                elif selectid == 0:
                    print('--- END')
                    break
                else:
                    job_path = j_path % selectid
                    info_lst = []
                    job_info = [selectid]
                    check_content(job_path, info_lst, job_info)
        else:
            print('--- exit')
            break


def main():
    check_db()


if __name__ == '__main__':
    main()
