nd_kebb
/
chachong
1
0
Fork 0
Code Issues 0 Pull Requests 0 Releases 0 Wiki Activity
丽水查重代码
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
3 Commits
1 Branch
576KB
Branch: master
Branches Tags
${ item.name }
Create branch ${ searchTerm }
from 'master'
${ noResults }
chachong/insert_history_data_total.py

411 lines
18KB
Raw Permalink Blame History 

  1. # coding=utf-8

  2. 

  3. import re

  4. import mysql_pool

  5. from pymysql.converters import escape_string

  6. import cosin_similarity

  7. import pandas as pd

  8. import glm_utils

  9. import os

  10. import json

  11. 

  12. wdys1 = {

  13.     "项目名称": "xmmc",

  14.     "现状问题": "xzwt",

  15.     "系统基础": "xtjc",

  16.     "项目目标": "xmmb",

  17.     "预期绩效": "yqjx",

  18.     "建设需求": "jsxq",

  19.     "数据需求": "sjxq",

  20.     "安全需求": "aqxq",

  21.     "业务领域": "ywly",

  22.     "核心业务": "hxyw",

  23.     "业务需求": "ywxq",

  24.     "业务协同": "ywxt",

  25.     "建设层级": "jscj",

  26.     "用户范围": "yhfw",

  27.     "目标群体": "mbqt",

  28.     "建设内容": "jsnr",

  29.     "功能模块": "gnmk",

  30.     "数据共享": "sjgx",

  31.     "智能要素": "znys",

  32.     "申报单位": "sbdw",

  33.     "所属地区": "ssdq",

  34.     "预算年度": "ysnd"

  35. }

  36. wdys2 = {

  37.     "xmmc": "项目名称",

  38.     "xzwt": "现状问题",

  39.     "xtjc": "系统基础",

  40.     "xmmb": "项目目标",

  41.     "yqjx": "预期绩效",

  42.     "jsxq": "建设需求",

  43.     "sjxq": "数据需求",

  44.     "aqxq": "安全需求",

  45.     "ywly": "业务领域",

  46.     "hxyw": "核心业务",

  47.     "ywxq": "业务需求",

  48.     "ywxt": "业务协同",

  49.     "jscj": "建设层级",

  50.     "yhfw": "用户范围",

  51.     "mbqt": "目标群体",

  52.     "jsnr": "建设内容",

  53.     "gnmk": "功能模块",

  54.     "sjgx": "数据共享",

  55.     "znys": "智能要素",

  56.     "sbdw": "申报单位",

  57.     "ssdq": "所属地区",

  58.     "ysnd": "预算年度"

  59. }

  60. gnmkys = {

  61.     "gnmc": "功能名称",

  62.     "gnms": "功能描述"

  63. }

  64. 

  65. 

  66. def getFlag():

  67.     data_dict = {}

  68.     df = pd.read_excel("0825-丽水系统查重维度.xlsx")

  69.     data = df.values

  70.     data = list(pd.Series(data[:, 1]).dropna())

  71.     for d in data:

  72.         try:

  73.             wd = re.search("(.*?)（.*?%）", d).group(1).strip()

  74.             wdc = wdys1.get(wd)

  75.             if wdc:

  76.                 qz = re.search(".*?（(.*?%)）", d).group(1)

  77.                 data_dict[wdc] = qz

  78.         except:

  79.             pass

  80.     return data_dict

  81. 

  82. # getFlag()

  83. 

  84. def gong_neng_mo_kuai(xmmc, mysql, dl, data, er_title, line):

  85.     # 将excel文件中的所有第三维度内容进行拼接

  86.     str_dict = {}

  87.     for et in er_title:

  88.         for d in data:

  89.             if d[1] == et:

  90.                 if str_dict.get(et):

  91.                     str_dict[et] = str_dict.get(et) + d[3]

  92.                 else:

  93.                     str_dict[et] = d[3]

  94.     for k, v in str_dict.items():

  95.         mysql.sql_change_msg("""insert into user_history_module_data(xmmc,gnmc,gnms,line, remark) value("%s", "%s", "%s", "%s", "%s")""" % (

  96.             escape_string(xmmc), escape_string(k), escape_string(v), line, ""))

  97.         #

  98.         similarity = cosin_similarity.CosineSimilarity(v, v)

  99.         similarity, keywords_x, keywords_y = similarity.main()

  100.         mysql.sql_change_msg("""insert into user_history_module_keywords (xmmc,gnmc,gnms,line) value("%s" ,"%s", "%s", "%s")""" % (

  101.             xmmc, escape_string(k), str(keywords_y)[1:-1], line))

  102. 

  103. 

  104. def project_check(data_list, line):

  105.     mysql = mysql_pool.ConnMysql()

  106.     # 读取维度和权重

  107.     # get_data_dict = getFlag()

  108.     # 遍历excel存储路径

  109.     for dl in data_list:

  110.         # path = "0825-丽水系统查重维度1.xlsx"

  111.         # 读取路径下的excel

  112.         print(dl)

  113.         df = pd.read_excel(dl[1])

  114.         xmmc = df.keys()

  115.         # print(type(xmmc[dup_file_test]))

  116.         xmmc=xmmc[1]

  117.         # print(type(xmmc))

  118.         # xmmc1=''

  119. 

  120.         if "可研报告"or "可研性报告"or "可行性研究报告" in xmmc:

  121.             xmmc=xmmc.replace('可研报告','')

  122.             xmmc=xmmc.replace('可研性报告','')

  123.             xmmc=xmmc.replace('可行性研究报告','')

  124.         # print(xmmc)

  125.         data = df.values

  126.         # 将excel文件中的所有维度内容进行拼接

  127.         join_str = ""

  128.         str_dict = {}

  129.         title = ""

  130.         er_title = set()

  131.         # for d in data:

  132.         #     # print(d)

  133.         #     if pd.notnull(d[0]):

  134.         #         title = d[0]

  135.         #         if title == "功能模块":

  136.         #             er_title.add(d[dup_file_test])

  137.         #         join_str = ""

  138.         #         for i in d[dup_file_test:]:

  139.         #             if pd.notnull(i):

  140.         #                 join_str += i

  141.         #         str_dict[wdys1.get(title)] = join_str

  142.         #     else:

  143.         #         if title == "功能模块":

  144.         #             er_title.add(d[dup_file_test])

  145.         #         for i in d[dup_file_test:]:

  146.         #             if pd.notnull(i):

  147.         #                 join_str += i

  148.         #         str_dict[wdys1.get(title)] = str_dict.get(wdys1.get(title)) + join_str

  149.         # print(str_dict)

  150.         gnmk_str = []

  151.         # print(data)

  152.         for d in data:

  153.             if pd.notnull(d[0]):

  154.                 title = d[0]

  155.                 if title == "功能模块":

  156.                     er_title.add(d[1])

  157.                 join_str = ""

  158.                 for i in d[1:]:

  159.                     # print(type(i))

  160.                     # i=str(i)

  161.                     if pd.notnull(i):

  162.                         join_str += str(i)

  163.                         if title == "功能模块":

  164.                             # for j in d[3:]:

  165.                             if i == '功能描述':

  166.                                 continue

  167.                             else:

  168.                                 gnmk_str.append(i)

  169.                 str_dict[wdys1.get(title)] = join_str

  170.                 # print(str_dict.get(wdys1.get(title)))

  171.             else:

  172.                 if title == "功能模块":

  173.                     er_title.add(d[1])

  174.                 for i in d[3:]:

  175.                     if pd.notnull(i):

  176.                         join_str += str(i)

  177.                         if title == "功能模块":

  178.                             gnmk_str.append(i)

  179.                 str_dict[wdys1.get(title)] = str_dict.get(wdys1.get(title)) + join_str

  180.                 # gnmk="".join(gnmk_str)

  181.                 # str_dict['gnmk']=gnmk

  182.         gnmk = ",".join(gnmk_str)

  183.         str_dict['gnmk'] = gnmk

  184.         # print(str_dict)

  185.         # print(str_dict.get("xzwt")if str_dict.get("xzwt") else None)

  186.         # print(str_dict.get('gnmk')if str_dict.get('gnmk')else None)

  187.         mysql.sql_change_msg(

  188.             """insert into user_history_data (xmmc, xzwt, xtjc, xmmb, yqjx, jsxq, sjxq, aqxq, ywly, hxyw, ywxq, ywxt, jscj, yhfw, mbqt, jsnr, gnmk, sjgx, znys,sbdw,ssdq,ysnd,line,remark) value ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s","%s","%s","%s","%s","%s")"""

  189.             % (escape_string(xmmc),

  190.                escape_string(str_dict.get("xzwt")) if str_dict.get("xzwt") else None,

  191.                escape_string(str_dict.get("xtjc")) if str_dict.get("xtjc") else None,

  192.                escape_string(str_dict.get("xmmb")) if str_dict.get("xmmb") else None,

  193.                escape_string(str_dict.get("yqjx")) if str_dict.get("yqjx") else None,

  194.                escape_string(str_dict.get("jsxq")) if str_dict.get("jsxq") else None,

  195.                escape_string(str_dict.get("sjxq")) if str_dict.get("sjxq") else None,

  196.                escape_string(str_dict.get("aqxq")) if str_dict.get("aqxq") else None,

  197.                escape_string(str_dict.get("ywly")) if str_dict.get("ywly") else None,

  198.                escape_string(str_dict.get("hxyw")) if str_dict.get("hxyw") else None,

  199.                escape_string(str_dict.get("ywxq")) if str_dict.get("ywxq") else None,

  200.                escape_string(str_dict.get("ywxt")) if str_dict.get("ywxt") else None,

  201.                escape_string(str_dict.get("jscj")) if str_dict.get("jscj") else None,

  202.                escape_string(str_dict.get("yhfw")) if str_dict.get("yhfw") else None,

  203.                escape_string(str_dict.get("mbqt")) if str_dict.get("mbqt") else None,

  204.                escape_string(str_dict.get("jsnr")) if str_dict.get("jsnr") else None,

  205.                escape_string(str_dict.get("gnmk")) if str_dict.get("gnmk") else None,

  206.                escape_string(str_dict.get("sjgx")) if str_dict.get("sjgx") else None,

  207.                escape_string(str_dict.get("znys")) if str_dict.get("znys") else None,

  208.                escape_string(str_dict.get("sbdw")) if str_dict.get("sbdw") else None,

  209.                escape_string(str_dict.get("ssdq")) if str_dict.get("ssdq") else None,

  210.                escape_string(str_dict.get("ysnd")) if str_dict.get("ysnd") else None,

  211.                line, ""))

  212.         project_gjc = {}

  213.         for w in wdys2.keys():

  214.             content_x = str_dict.get(w)

  215.             content_y = str_dict.get(w)

  216.             if content_x and content_y:

  217.                 # 循环遍历每一个维度

  218.                 similarity = cosin_similarity.CosineSimilarity(content_x, content_y)

  219.                 # 相似度  关键词

  220.                 similarity, keywords_x, keywords_y = similarity.main()

  221.                 project_gjc[w] = keywords_y

  222.         mysql.sql_change_msg(

  223.             """insert into user_history_keywords (xmmc, xzwt, xtjc, xmmb, yqjx, jsxq, sjxq, aqxq, ywly, hxyw, ywxq, ywxt, jscj, yhfw, mbqt, jsnr, gnmk, sjgx, znys, line) value ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s")"""

  224.             % (xmmc, str(project_gjc.get("xzwt"))[1:-1] if project_gjc.get("xzwt") else None,

  225.                str(project_gjc.get("xtjc"))[1:-1] if project_gjc.get("xtjc") else None,

  226.                str(project_gjc.get("xmmb"))[1:-1] if project_gjc.get("xmmb") else None,

  227.                str(project_gjc.get("yqjx"))[1:-1] if project_gjc.get("yqjx") else None,

  228.                str(project_gjc.get("jsxq"))[1:-1] if project_gjc.get("jsxq") else None,

  229.                str(project_gjc.get("sjxq"))[1:-1] if project_gjc.get("sjxq") else None,

  230.                str(project_gjc.get("aqxq"))[1:-1] if project_gjc.get("aqxq") else None,

  231.                str(project_gjc.get("ywly"))[1:-1] if project_gjc.get("ywly") else None,

  232.                str(project_gjc.get("hxyw"))[1:-1] if project_gjc.get("hxyw") else None,

  233.                str(project_gjc.get("ywxq"))[1:-1] if project_gjc.get("ywxq") else None,

  234.                str(project_gjc.get("ywxt"))[1:-1] if project_gjc.get("ywxt") else None,

  235.                str(project_gjc.get("jscj"))[1:-1] if project_gjc.get("jscj") else None,

  236.                str(project_gjc.get("yhfw"))[1:-1] if project_gjc.get("yhfw") else None,

  237.                str(project_gjc.get("mbqt"))[1:-1] if project_gjc.get("mbqt") else None,

  238.                str(project_gjc.get("jsnr"))[1:-1] if project_gjc.get("jsnr") else None,

  239.                str(project_gjc.get("gnmk"))[1:-1] if project_gjc.get("gnmk") else None,

  240.                str(project_gjc.get("sjgx"))[1:-1] if project_gjc.get("sjgx") else None,

  241.                str(project_gjc.get("znys"))[1:-1] if project_gjc.get("znys") else None,

  242.                line))

  243. 

  244.         gong_neng_mo_kuai(xmmc, mysql, dl, data, er_title, line)

  245. 

  246. 

  247. 

  248. def update_desc():

  249.     mysql = mysql_pool.ConnMysql()

  250.     module_list = mysql.sql_select_many("""select id, gnms from user_history_module_data where xmmc = '丽水市城市管理指挥中心信息系统（一期）项目'""")

  251.     for module in module_list:

  252.         # 通过chatglm进行提取信息

  253.         gnms = module.get("gnms")

  254.         content = glm_utils.CallContentResultNew(gnms)

  255. 

  256.         mysql.sql_change_msg(

  257.             """UPDATE user_history_module_data SET glm_desc = "%s" WHERE id = %d""" % (

  258.                 content if content else None,

  259.                 module.get("id")))

  260.         print(content)

  261. 

  262. 

  263. def update_desc1():

  264.     mysql = mysql_pool.ConnMysql()

  265.     module_list = mysql.sql_select_many("""select id, gnms from gnms_gml where xmmc = '丽水花园云（城市大脑）数字驾驶舱项目'""")

  266.     for module in module_list:

  267.         # 通过chatglm进行提取信息

  268.         gnms = module.get("gnms")

  269.         content = glm_utils.CallContentResultNew(gnms)

  270. 

  271.         mysql.sql_change_msg(

  272.             """UPDATE gnms_gml SET glm_desc = "%s" WHERE id = %d""" % (

  273.                 content if content else None,

  274.                 module.get("id")))

  275.         print(content)

  276. 

  277. 

  278. def info_word_project():

  279.     mysql = mysql_pool.ConnMysql()

  280.     module_list1 = mysql.sql_select_many(

  281.         """select jsnr from user_history_data where xmmc = '2023年丽水市云和县数字法治门户建设项目' """)

  282.     module_list2 = mysql.sql_select_many(

  283.         """select jsnr from user_history_data where xmmc IN ('浙江省第二监狱重点罪犯管控模型项目',

  284.             '浙江省农村水电站管理数字化应用',

  285.             '浙江省河湖库保护数字化应用建设项目',

  286.             '浙江省环境遥感监测业务智治',

  287.             '平台项目',

  288.             '浙江林业智媒平台项目',

  289.             '未来e家应用建设方案',

  290.             '浙江省智慧林业云平台升级改造项目建设方案',

  291.             '为侨服务“全球通”平台二期建设项目')""")

  292.     json_objects = []

  293.     for module_info1 in module_list1:

  294.         for jsnr1Ele in module_info1["jsnr"].split('-----》'):

  295.             for module_info2 in module_list2:

  296.                 for jsnr2Ele in module_info2["jsnr"].split('-----》'):

  297.                     str = "A：%s\nB：%s" % (jsnr1Ele, jsnr2Ele)

  298.                     data = {

  299.                         "instruction": "现在你是一个政府提案的查重检查人员，给定两段话A和B：让我们一步步思考并判断是否相似。请以相似度从高、中、低三个等级进行评价，并给出理由。",

  300.                         "input": str,

  301.                         "output": ""

  302.                     }

  303.                     json_objects.append(data)

  304. 

  305.     with open('其他-建设内容.json', 'w') as f:

  306.         for json_obj in json_objects:

  307.             json_str = json.dumps(json_obj, ensure_ascii=False)  # 将JSON对象转换为字符串

  308.             f.write(json_str + '\n')  # 写入字符串，并添加换行符

  309. 

  310. 

  311. 

  312. def info_word1():

  313.     mysql = mysql_pool.ConnMysql()

  314.     # module_list1 = mysql.sql_select_many("""select gnms from user_history_module_data where xmmc = '莲智社区' """)

  315.     # module_list2 = mysql.sql_select_many("""select gnms from user_history_module_data where xmmc IN ('古堰画乡智慧客厅项目—未来社区智慧服务平台', '未来e家')""")

  316.     module_list1 = mysql.sql_select_many("""select gnms from user_history_module_data where xmmc = '丽水市遂昌县政法委数字法治综合应用' """)

  317.     module_list2 = mysql.sql_select_many("""select gnms from user_history_module_data where xmmc IN ('浙江省第二监狱重点罪犯管控模型项目',

  318.             '浙江省农村水电站管理数字化应用',

  319.             '浙江省河湖库保护数字化应用建设项目',

  320.             '浙江省环境遥感监测业务智治',

  321.             '平台项目',

  322.             '浙江林业智媒平台项目',

  323.             '未来e家应用建设方案',

  324.             '浙江省智慧林业云平台升级改造项目建设方案',

  325.             '为侨服务“全球通”平台二期建设项目')""")

  326. 

  327.     json_objects = []

  328.     for module_info1 in module_list1:

  329.         for module_info2 in module_list2:

  330.             str = "A：%s\nB：%s" % (module_info1["gnms"], module_info2["gnms"])

  331.             data = {

  332.                 "instruction": "现在你是一个政府提案的查重检查人员，给定两段话A和B：让我们一步步思考并判断是否相似。请以相似度从高、中、低三个等级进行评价，并给出理由。",

  333.                 "input": str,

  334.                 "output": ""

  335.             }

  336.             json_objects.append(data)

  337. 

  338.     with open('其他-功能模块对比.json', 'w') as f:

  339.         for json_obj in json_objects:

  340.             json_str = json.dumps(json_obj, ensure_ascii=False)  # 将JSON对象转换为字符串

  341.             f.write(json_str + '\n')  # 写入字符串，并添加换行符

  342. 

  343. 

  344. def info_word_project_yw():

  345.     mysql = mysql_pool.ConnMysql()

  346.     module_list1 = mysql.sql_select_many(

  347.         """select jsnr from user_history_data where xmmc = '2023年丽水市云和县数字法治门户建设项目' """)

  348.     module_list2 = mysql.sql_select_many(

  349.         """select jsnr from user_history_data where xmmc IN ('2023年丽水市云和县数字法治门户建设项目', '浙江省司法厅全域数字法治监督应用系统（一期）', '丽水市遂昌县政法委数字法治综合应用', '丽水市龙泉市政法委法治龙泉门户', '庆元县数字法治综合门户')""")

  350.     json_objects = []

  351.     for module_info1 in module_list1:

  352.         for jsnr1Ele in module_info1["jsnr"].split('-----》'):

  353.             for module_info2 in module_list2:

  354.                 for jsnr2Ele in module_info2["jsnr"].split('-----》'):

  355.                     str = "A：%s\nB：%s" % (jsnr1Ele, jsnr2Ele)

  356.                     data = {

  357.                         "instruction": "现在你是一个政府提案的查重检查人员，给定两段话A和B：让我们一步步思考并判断是否相似。请以相似度从高、中、低三个等级进行评价，并给出理由。",

  358.                         "input": str,

  359.                         "output": ""

  360.                     }

  361.                     json_objects.append(data)

  362. 

  363.     with open('其他-建设内容.json', 'w') as f:

  364.         for json_obj in json_objects:

  365.             json_str = json.dumps(json_obj, ensure_ascii=False)  # 将JSON对象转换为字符串

  366.             f.write(json_str + '\n')  # 写入字符串，并添加换行符

  367. 

  368. 

  369. if __name__ == "__main__":

  370.     info_word1()

  371.     print("ok.......")

  372.     path = r"/Users/kebobo/Downloads/丽水/未来社区"

  373.     data_list = os.listdir(path)

  374.     for file in data_list:

  375.         if file != '.DS_Store':

  376.             data_list = [(0, path + '/' + file, "")]

  377.             project_check(data_list, "2024-07-27-数字法治")

  378.             print("已存入************************************* %s" % file)

  379. 

  380. """

  381. 建设目标，业务功能

  382. 

  383. gnmk_str = []

  384. for d in data:

  385.     if pd.notnull(d[0]):

  386.         title = d[0]

  387.         if title == "功能模块":

  388.             er_title.add(d[dup_file_test])

  389.         join_str = ""

  390.         for i in d[dup_file_test:]:

  391.             if pd.notnull(i):

  392.                 join_str += i

  393.                 if title == "功能模块":

  394.                     gnmk_str.append(i)

  395.         str_dict[wdys1.get(title)] = join_str

  396.     else:

  397.         if title == "功能模块":

  398.             er_title.add(d[dup_file_test])

  399.         for i in d[dup_file_test:]:

  400.             if pd.notnull(i):

  401.                 join_str += i

  402.                 if title == "功能模块":

  403.                     gnmk_str.append(i)

  404.         str_dict[wdys1.get(title)] = str_dict.get(wdys1.get(title)) + join_str

  405. gnmk = "".join(gnmk_str)

  406. 

  407. 

  408. """

  409.