{ "cells": [ { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "' tableField_ljcfpjg4 ': data[ \"【导入】申请优惠券明细\" ][i],# \"【导入】申请优惠券明细\"\n", "' tableField_ljibuzgq ': data[ \"【check】申请优惠券明细\" ][i],# \"【check】申请优惠券明细\"\n", "' textField_ljcfpjgd ': data[ \"发券结果\" ][i],# \"发券结果\"\n", "开始处理发券！\n", "读取到 [[流程]自动化优惠券(+多年)表中 152 条数据！\n", "CHS202108060134956 13577991101 {'result': ['FINST-PTA66DA120ANSFLMF8C069PZVGN63ELEWMAZLHH8']}\n", "CHS202208110186407 15894498555 {'result': ['FINST-3RB66I91Q8BNHUE19SH5XC3QOID131KGWMAZLEA1']}\n", "CHS202008160104161 13888058615 {'result': ['FINST-G0A66Z81X6ANA4J7F4OZF68QOHSM2OFIWMAZLHD8']}\n", "CHS202201140148849 13095343326 {'result': ['FINST-EPB66191RZ9NKSOSFMSFNDQHC1SO3OBKWMAZLHQH']}\n", "CHS202307200237693 18787262310 {'result': ['FINST-DOC66I91N7BNUIGI7P3YM60AX2NZ2U6MWMAZLQSD']}\n", "CHS202108210138519 15125606365 {'result': ['FINST-GFA66U91L0ANYA1PA8J5QCZ995QD2S2OWMAZLUH7']}\n", "CHS202304230226238 15687189309 {'result': ['FINST-N98665810Z9ND2YN6P6VJ42E76V839ZPWMAZLM49']}\n", "CHS202108250138924 15096625841 {'result': ['FINST-5W966981FZBNIIBL7O06TAFV54Q62EYRWMAZLZZ']}\n", "CHS202105270127195 18608753030 {'result': ['FINST-K7D66L910ZANR6FMEDNEXDB6VC263GTTWMAZLH45']}\n", "CHS202208300187832 13378868887 {'result': ['FINST-9V7669A1VD9NP9XL6Z1P19A84A1S3COVWMAZLXEV']}\n", "CHS201902280009312 18987298767 {'result': ['FINST-83D66TA11CBNCSEXCYB3NBQZZLPS32JXWMAZLO9C']}\n", "CHS202211150191707 18287205610 {'result': ['FINST-ENC663810Z9N1FH2ECMF6868OLJA20EZWMAZL6Z6']}\n", "CHS202308030239090 13577272535 {'result': ['FINST-9S966871B0ANGCLSA0HUL413WIXP3H91XMAZLQJJ']}\n", "CHS202308080239543 13759020642 {'result': ['FINST-MX966DD1HE9NEVH971R3ABSEDZFU2G63XMAZL7WU']}\n", "CHS202308250240999 13988878866 {'result': ['FINST-KL766581J7ANF8O3FQ5RA9CKGWWZ2A25XMAZLZCG']}\n", "CHS201902280007000 15925128106 {'result': ['FINST-MBD66F9150CNR9RKE99YR4YENZ0L3AX6XMAZL5O']}\n", "CHS201906030030064 18608753030 {'result': ['FINST-9CC66GD1PH9NT19FAM51JCSOTXEE2GK9XMAZL33L']}\n", "CHS201912070044730 13529282875 {'result': ['FINST-LTC66R81PY9NK10Q86ORCD9A63AJ3QIBXMAZLOGA']}\n", "CHS202207120184270 13759105925 {'result': ['FINST-MT566TB1RZ9NZXZGETNYN4W3DMM33FFDXMAZLCR7']}\n", "CHS202308110239896 13700661283 {'result': ['FINST-6JA66W91EXBNLM79BOYSYC3YXNXQ3FAFXMAZLA52']}\n", "CHS202308110239915 14769361898 {'result': ['FINST-LTC66R81YZ9NNBZF8K5JD8GAVIJE3H6HXMAZLNZM']}\n", "CHS202108100137208 13658776660 {'result': ['FINST-DC666GC1K7AN2R9GAF53H9Q1PWM13K1JXMAZLZJ5']}\n", "CHS201909240038589 13759409759 {'result': ['FINST-H7D667C108ANDYDZDNXL47W9MOCA30YKXMAZLD8F']}\n", "CHS202004130066222 18687845850 {'result': ['FINST-W9D66Q612AAN4H4RB1RGO7PXHH1D29TMXMAZLVGE']}\n", "CHS202308220240771 13708438038 {'result': ['FINST-5U866VA1WABNJGEMC7G2JAEQEB932JPOXMAZLCX1']}\n", "CHS202108060134958 18213188288 {'result': ['FINST-9BB66681FY9NT6FZ5MN245VGRGE23ONQXMAZL8FA']}\n", "CHS201908310036569 13700658352 {'result': ['FINST-9BB66681GG9NPHCG9GPG1C5KBM3Q28JSXMAZL2FJ']}\n", "CHS202003210057221 13608852511 {'result': ['FINST-J8966HD1OZ9NDJIR63OCZ5Q5S49P2WJUXMAZLBQO']}\n", "CHS202208030185802 18786196199 {'result': ['FINST-I5D66NB1OBANQS8472SJYC6RZY4L3EFWXMAZLBCD']}\n", "CHS202208040185823 18286944574 {'result': ['FINST-TCC66771VY9NPLSUA4G2B7UD79GG2DEYXMAZL939']}\n", "CHS202308070239389 15185005373 {'result': ['FINST-SNB667B15CBNUDXTBU85ND71EWQT33A0YMAZLLA']}\n", "CHS202008100103255 18216690259 {'result': ['FINST-ENC66381XXANS81D8KF6P9SHB3IC3362YMAZLHGH']}\n", "CHS202108100135470 17385904666 {'result': ['FINST-TM966BD1HZBNT66690SQE6Z788P53V14YMAZLBN']}\n", "CHS202110090142521 18084298828 {'result': ['FINST-VBD66H81UG9N4YKN8Y0UGABSYYTB2DY5YMAZLI38']}\n" ] } ], "source": [ "# 基础信息\n", "import pandas as pd\n", "import numpy as np\n", "import requests\n", "import json\n", "import time\n", "import re\n", "import sys\n", "from datetime import datetime\n", "from dateutil.relativedelta import relativedelta\n", "from pathlib import Path\n", "from urllib.parse import quote\n", "from datetime import date, timedelta\n", "import datetime\n", "from io import BytesIO\n", "\n", "\n", "ROOT = Path('.').absolute() # 当前工作目录\n", "\n", "def generateToken() -> str:\n", " \"\"\" 生成 token \"\"\"\n", "\n", " token_api = 'https://api.dingtalk.com/v1.0/oauth2/accessToken'\n", "\n", " # 该信息在钉钉开放应用中\n", " data = {\n", " \"appKey\": \"ding5kqocon5s9oph5uq\",\n", " \"appSecret\": 'HL1jgsIIfLAC0eTH0A1m4mwxUDqbgsiPeCCGGE3ocM6qJBTIW7Ivt9drxF_Z4Kb_'\n", " }\n", "\n", " res = requests.post(token_api, json=data)\n", " token = res.json()['accessToken']\n", "\n", " return token\n", "\n", "def read_processes_instances(token, formUuid, page, n):\n", " \"\"\" 函数功能：读取流程表单的所有数据 \"\"\"\n", "\n", " api = f'https://api.dingtalk.com/v1.0/yida/processes/instances?pageNumber={page}&pageSize={n}'\n", "\n", " headers = {\n", " \"Content-Type\": \"application/json\",\n", " \"x-acs-dingtalk-access-token\": token\n", " }\n", "\n", " formData = {\n", " \"appType\": \"APP_UYZ0KG6L0CCNV80GZ66O\",\n", " \"systemToken\": \"XA966F81JAJOFCVVVKO64E9MIIZV1EWE5SFMKJ2\",\n", " \"userId\": \"yida_pub_account\", # 超级管理员账号\n", " \"language\": \"zh_CN\",\n", " \"formUuid\": formUuid,\n", " # \"searchFieldJson\": json.dumps(searchField), # 如果增加上这一项会要求升级宜搭存储\n", " \"instanceStatus\": \"RUNNING\" # 运行中\n", " }\n", "\n", " res = requests.post(api, headers=headers, json=formData)\n", " return res.json()\n", "\n", "def queryLastSaasInfo(cellPhone):\n", " \"\"\" 函数功能：手机号查询最近一次购买saas明细 \"\"\"\n", "\n", " api = 'http://manage.f6yc.com/hive-admin/yida/order/queryLastSaasInfo?cellPhone=' + cellPhone\n", "\n", " headers = {\n", " \"token\":\"af88h9KvLQrdvVhFhS8zD1yWx9cu\"\n", " }\n", " res = requests.post(api,headers=headers)\n", " return res.json()\n", "\n", "def get_approval_records(token: str, processInstanceId: str):\n", " \"\"\" 函数功能：获取流程表单的审批记录 \"\"\"\n", " appType = \"APP_UYZ0KG6L0CCNV80GZ66O\"\n", " systemToken = \"XA966F81JAJOFCVVVKO64E9MIIZV1EWE5SFMKJ2\"\n", " userId = \"yida_pub_account\"\n", "\n", " api = f'https://api.dingtalk.com/v1.0/yida/processes/operationRecords?appType={appType}&systemToken={systemToken}&userId={userId}&language=zh_CN&processInstanceId={processInstanceId}'\n", "\n", " headers = {\n", " \"Content-Type\": \"application/json\",\n", " \"x-acs-dingtalk-access-token\": token\n", " }\n", "\n", " res = requests.get(api, headers=headers)\n", " # print('获取流程表单的审批记录')\n", " return res.json()\n", "\n", "\n", "def aggree_approval(token: str, taskId: str, processInstanceId: str,res_new,formDataJson):\n", " \"\"\" 函数功能：同意审批节点 \"\"\"\n", " api = f'https://api.dingtalk.com/v1.0/yida/tasks/execute'\n", "\n", " headers = {\n", " \"Content-Type\": \"application/json\",\n", " \"x-acs-dingtalk-access-token\": token\n", " }\n", "\n", " payload = {\n", " \"outResult\": \"AGREE\",\n", " \"appType\": \"APP_UYZ0KG6L0CCNV80GZ66O\",\n", " \"systemToken\": \"XA966F81JAJOFCVVVKO64E9MIIZV1EWE5SFMKJ2\",\n", " \"remark\": \"同意(接口自动)\",\n", " \"formDataJson\": json.dumps(formDataJson),\n", " \"processInstanceId\": processInstanceId,\n", " # \"userId\": \"yida_pub_account\",\n", " \"userId\": res_new, \n", " \"language\": \"zh_CN\",\n", " \"taskId\": int(taskId)\n", " }\n", "\n", " res = requests.post(api, headers=headers, json=payload)\n", " # print('同意审批节点')\n", " return res\n", "\n", "def read_instances(token, formUuid, page, n):\n", " \"\"\" 函数功能：读取普通表单的所有数据 \"\"\"\n", "\n", " api = f'https://api.dingtalk.com//v1.0/yida/forms/instances/search'\n", "\n", " headers = {\n", " \"Content-Type\": \"application/json\",\n", " \"x-acs-dingtalk-access-token\": token\n", " }\n", "\n", " formData = {\n", " \"appType\" : \"APP_UYZ0KG6L0CCNV80GZ66O\",\n", " \"systemToken\" : \"XA966F81JAJOFCVVVKO64E9MIIZV1EWE5SFMKJ2\",\n", " \"userId\" : \"yida_pub_account\",\n", " \"language\" : \"zh_CN\",\n", " \"formUuid\" : formUuid,\n", " \"currentPage\" : page,\n", " \"pageSize\" : n\n", " }\n", "\n", " res = requests.post(api, headers=headers, json=formData)\n", " return res.json()\n", "\n", "def queryCompanies(cellPhone):\n", " \"\"\" 函数功能：自动发券查询手机号所处公司 \"\"\"\n", "\n", " api = 'http://manage.f6yc.com/hive-admin/yida/account/query?cellPhone=' + cellPhone\n", "\n", " headers = {\n", " \"token\":\"af88h9KvLQrdvVhFhS8zD1yWx9cu\"\n", " }\n", " res = requests.post(api,headers=headers)\n", " return res.json()\n", "def queryDetailByOrgId(orgId):\n", " \"\"\" 函数功能：自动发券查询门店上次成交价 \"\"\"\n", "\n", " api = 'http://manage.f6yc.com/hive-admin/yida/org/queryDetailByOrgId?orgId=' + orgId\n", "\n", " headers = {\n", " \"token\":\"af88h9KvLQrdvVhFhS8zD1yWx9cu\"\n", " }\n", " res = requests.post(api,headers=headers)\n", " return res.json()\n", "\n", "def component(FORMID,TOKEN):\n", " \"\"\" 获取组件信息 \"\"\"\n", " api = f'https://api.dingtalk.com//v1.0/yida/forms/formFields'\n", "\n", " headers = {\n", " \"Content-Type\": \"application/json\",\n", " \"x-acs-dingtalk-access-token\": TOKEN\n", " }\n", " payload = {\n", " \"formUuid\" : FORMID,\n", " \"appType\" : \"APP_UYZ0KG6L0CCNV80GZ66O\",\n", " # \"formDataJson\" : json.dumps(formData, cls=NpEncoder),\n", " \"systemToken\" : \"XA966F81JAJOFCVVVKO64E9MIIZV1EWE5SFMKJ2\",\n", " # \"language\" : \"zh_CN\",\n", " \"userId\" : \"yida_pub_account\"\n", " }\n", "\n", " res = requests.get(api, headers=headers, json=payload)\n", "\n", " return res.json()\n", "\n", "def Batch_creation(FORMID,TOKEN,ALL_formData):\n", " \"\"\" 获取组件信息 \"\"\"\n", " api = f'https://api.dingtalk.com//v1.0/yida/forms/instances/batchSave'\n", "\n", " headers = {\n", " \"Content-Type\": \"application/json\",\n", " \"x-acs-dingtalk-access-token\": TOKEN\n", " }\n", " payload = {\n", " # \"formDataJson\" : json.dumps(formData, cls=NpEncoder),\n", " \"noExecuteExpression\" : \"false\",\n", " \"formUuid\" : FORMID,\n", " \"appType\" : \"APP_UYZ0KG6L0CCNV80GZ66O\",\n", " \"asynchronousExecution\" : \"false\",\n", " \"systemToken\" : \"XA966F81JAJOFCVVVKO64E9MIIZV1EWE5SFMKJ2\",\n", " \"keepRunningAfterException\" : \"true\",\n", " \"userId\" : \"yida_pub_account\",\n", " \"formDataJsonList\" : ALL_formData,\n", " # \"formDataJsonList\" : json.dumps(ALL_formData, cls=NpEncoder),\n", " }\n", "\n", " res = requests.post(api, headers=headers, json=payload)\n", " return res.json()\n", "\n", "def queryskuList():\n", " \"\"\" 函数功能：按照商品分类查询商品列表接口 \"\"\"\n", "\n", " api = 'http://manage.f6yc.com/hive-admin/yida/sku/category/query?category=4'\n", "\n", " headers = {\n", " \"token\":\"af88h9KvLQrdvVhFhS8zD1yWx9cu\"\n", " }\n", "\n", " res = requests.post(api,headers=headers)\n", " return res.json()\n", "\n", "def couponAvailableQuery(cellPhone,groupId,skuList,passportId):\n", " \"\"\" 函数功能：在当前选择场景下查询手机号可以使用的优惠券 http://manage.f6yc.com/hive-admin/ \"\"\"\n", "\n", " api = f'http://manage.f6yc.com/hive-admin/yida/coupon/available/query?passportId={passportId}&cellPhone={cellPhone}&groupId={groupId}&skuList' + str(skuList)\n", "\n", " headers = {\n", " \"token\":\"af88h9KvLQrdvVhFhS8zD1yWx9cu\"\n", " }\n", "\n", " res = requests.post(api,headers=headers)\n", " return res.json()\n", "\n", "def queryPriceInfoBySkuId(orgId,skuId,saasUpdate):\n", " \"\"\" 函数功能：商品价格查询\"\"\"\n", "\n", " api = f'http://manage.f6yc.com/hive-admin/yida/sku/queryPriceInfoBySkuId?orgId={orgId}&skuId={skuId}&saasUpdate={saasUpdate}'\n", "\n", " headers = {\n", " \"token\":\"af88h9KvLQrdvVhFhS8zD1yWx9cu\"\n", " }\n", "\n", " res = requests.post(api,headers=headers)\n", " return res.json()\n", "\n", "def read_instances_new(token, formUuid, page, n,CREATE_FROM, CREATE_TO):\n", " \"\"\" 函数功能：读取流程表单的所有数据 \"\"\"\n", "\n", " api = f'https://api.dingtalk.com//v1.0/yida/processes/instances?pageNumber={page}&pageSize={n}'\n", "\n", " headers = {\n", " \"Content-Type\": \"application/json\",\n", " \"x-acs-dingtalk-access-token\": token\n", " }\n", "\n", " formData = {\n", " \"appType\": \"APP_RTPWHV37ENXPQUZHTL25\",\n", " \"systemToken\": \"IA766O61SHFZT6UB0WNOB58GI5RW2K58KCU1LL6\",\n", " \"userId\": \"yida_pub_account\", # 超级管理员账号\n", " \"language\": \"zh_CN\",\n", " \"formUuid\": formUuid,\n", " \"createFromTimeGMT\":CREATE_FROM,\n", " \"createToTimeGMT\":CREATE_TO\n", " # \"searchFieldJson\": json.dumps(searchField), # 如果增加上这一项会要求升级宜搭存储\n", " # \"instanceStatus\": \"RUNNING\"\n", " }\n", "\n", " res = requests.post(api, headers=headers, json=formData)\n", " return res.json()\n", "\n", "def updateFormDataJson (token, formUuid, updateFormDataJson):\n", " \"\"\" 函数功能：更新表单数据 \"\"\"\n", "\n", " api = f'https://api.dingtalk.com//v1.0/yida/forms/instances'\n", "\n", " headers = {\n", " \"Content-Type\": \"application/json\",\n", " \"x-acs-dingtalk-access-token\": token\n", " }\n", "\n", " formData = {\n", " \"appType\": \"APP_RTPWHV37ENXPQUZHTL25\",\n", " \"systemToken\": \"IA766O61SHFZT6UB0WNOB58GI5RW2K58KCU1LL6\",\n", " \"userId\": \"yida_pub_account\", # 超级管理员账号\n", " \"language\": \"zh_CN\",\n", " \"formInstanceId\": formUuid,\n", " \"updateFormDataJson\":updateFormDataJson\n", " }\n", "\n", " res = requests.put(api, headers=headers, json=formData)\n", " return res.json()\n", "\n", "from datetime import datetime\n", "import time\n", "def get_time_range_minute(n):\n", " \"\"\" 获取近n分钟的时间戳(单位是毫秒)\"\"\"\n", "\n", " def delay_time(time_str, years=0, months=0, days=0, hours=0, minutes=0, seconds=0):\n", " if type(time_str) == str:\n", " time_str = datetime.strptime(time_str, '%Y-%m-%d %H:%M:%S')\n", " ret = time_str + relativedelta(years=years, months=months, days=days, hours=hours, minutes=minutes, seconds=seconds)\n", " return ret\n", "\n", " # 获得当前时间\n", " now_time = datetime.now()\n", " endTime = int(time.mktime(time.strptime(now_time.strftime('%Y/%m/%d %H:%M:%S'), '%Y/%m/%d %H:%M:%S'))) * 1000 - 1000\n", " # n小时前的时间\n", " ret2 = delay_time(now_time, minutes=-n)\n", " startTime = int(time.mktime(time.strptime(ret2.strftime('%Y/%m/%d %H:%M:%S'), '%Y/%m/%d %H:%M:%S'))) * 1000\n", "\n", " # print(f'时间区间:[{startTime}-{endTime}]')\n", " return startTime, endTime\n", "\n", "def timeStamp(timeNum):\n", " \"\"\" 函数功能：将时间戳(毫秒) 转化为时间日期格式\"\"\"\n", " timeStamp = float(timeNum/1000)\n", " timeArray = time.localtime(timeStamp)\n", " otherStyleTime = time.strftime(\"%Y-%m-%d %H:%M:%S\", timeArray)\n", " return otherStyleTime\n", "# 4320分钟时间间隔\n", "CREATE_FROM, CREATE_TO = [timeStamp(t) for t in get_time_range_minute(4320)]\n", "class NpEncoder(json.JSONEncoder):\n", " def default(self, obj):\n", " if isinstance(obj, np.integer):\n", " return int(obj)\n", " elif isinstance(obj, np.floating):\n", " return float(obj)\n", " elif isinstance(obj, np.ndarray):\n", " return obj.tolist()\n", " else:\n", " return super(NpEncoder, self).default(obj)\n", " \n", "'''获取表内控件信息 '''\n", "TOKEN = generateToken()\n", "FORMID_NEW = \"FORM-0A966I81N40CV1B48U18UBZAA8R92STJC7IJL9\"\n", "component_list = component(FORMID_NEW,TOKEN)\n", "for i in range(len(component_list['result'])):\n", " componentName = component_list['result'][i]['componentName']\n", " name_value = component_list['result'][i]['label']['value']\n", " fieldId = component_list['result'][i]['fieldId']\n", " print(\"'\",fieldId,\"':\",\"data[\",name_value,\"][i],#\",name_value)\n", "\n", "\"\"\" 处理流程开始 \"\"\"\n", "FORMID = \"FORM-0A966I81N40CV1B48U18UBZAA8R92STJC7IJL9\" # 【测试版本】批量自动发券\n", "# 读取续约流程表单\n", "form_data = read_processes_instances(token=TOKEN, formUuid=FORMID, page=1, n=100)\n", "PAGES = form_data.get('totalCount')//100 + 1\n", "\"\"\" 获取全量数据 \"\"\"\n", "for i in range(1, PAGES+1):\n", " form_data = read_processes_instances(token=TOKEN, formUuid=FORMID, page=i, n=100)\n", " for v in range(0,len(form_data.get('data'))):\n", " \n", " # 获取当前所处节点\n", " res_new = get_approval_records(token=TOKEN, processInstanceId=form_data['data'][v]['processInstanceId'])\n", " records_new = res_new.get('result')\n", " # 获取审批节点的 taskId\n", " records_new = [item for item in records_new if item.get('type') == \"TODO\"]\n", " taskId_new = records_new[0]['activityId']\n", " taskId = records_new[0]['taskId']\n", " if taskId_new==\"sid-6470221a-82ec-4bdd-a873-245ee47a5605\": # check\n", " print(\"开始处理！\")\n", " # 读取NGV明细\n", " import psycopg2\n", " import pandas as pd\n", " # 获得连接\n", " conn = psycopg2.connect(database=\"f6_bi\", user=\"BASIC$ro_caowei\", password=\"!ro_caowei123\", host=\"hgprecn-cn-nif1vnv0y002-cn-shanghai.hologres.aliyuncs.com\", port=\"80\")\n", " # 获得游标对象，一个游标对象可以对数据库进行执行操作\n", " cursor = conn.cursor()\n", " import datetime\n", " now_time = datetime.datetime.now()\n", " yes_time = now_time + datetime.timedelta(days=-1)\n", " yes_time_nyr = int(yes_time.strftime('%Y%m%d'))# 获取前一天日期\n", " # sql语句 建表\n", " sql =f\"\"\"SELECT * FROM \"public\".\"holo_ads_report_saas_profile_ngv_detail_d\" WHERE \"date_id\" = '{yes_time_nyr}';\"\"\"\n", " # 执行语句\n", " cursor.execute(sql)\n", " # 获取结果集的每一行\n", " rows = cursor.fetchall()\n", " # 获取所有字段名\n", " all_fields = cursor.description\n", " #执行结果转化为dataframe\n", " col = []\n", " for i in all_fields:\n", " col.append(i[0])\n", " data_NGV = pd.DataFrame(list(rows),columns=col)\n", " # 关闭数据库连接\n", " cursor.close()\n", " conn.close()\n", " \n", " '''读取商品和券信息'''\n", " FORMID_TWO = \"FORM-6L966171O3RB70TJDB75R6RBI31V2NNACKCJLF1\" # 商品ID券ID关系表\n", " all_skuId = {}\n", " all_couponTemplateId = {}\n", " textField_ljwon3vt = {}\n", " textField_ljxv26ip = {}\n", " list_new = 0\n", " tableField_data_new = {}\n", " form_data_TWO = read_instances(token=TOKEN, formUuid=FORMID_TWO, page=1, n=100)\n", " PAGES_TWO = form_data_TWO.get('totalCount')//100 + 1\n", " for a in range(1, PAGES_TWO+1):\n", " form_data_TWO = read_instances(token=TOKEN, formUuid=FORMID_TWO, page=a, n=100)\n", " for data_TWO in form_data_TWO.get('data'):\n", " all_skuId[data_TWO['formData']['textField_vmqmrm1'] + data_TWO['formData']['textField_cfm11ut']] = data_TWO['formData']['textField_nt0h72h']\n", " all_couponTemplateId[data_TWO['formData']['textField_vmqmrm1'] + data_TWO['formData']['textField_cfm11ut']] = data_TWO['formData']['textField_47og525']\n", " textField_ljwon3vt[data_TWO['formData']['textField_vmqmrm1'] + data_TWO['formData']['textField_cfm11ut']] = data_TWO['formData']['textField_ljwon3vt']\n", " textField_ljxv26ip[data_TWO['formData']['textField_vmqmrm1'] + data_TWO['formData']['textField_cfm11ut']] = data_TWO['formData']['textField_ljxv26ip']\n", " \"\"\" 开始分别读取数据并填充数据进行创建 \"\"\"\n", " for data in form_data['data'][v]['data']['tableField_ljcfpjg4']:\n", " # 基础字段预设\n", " data_new = {}\n", " data_new['textField_ljibuzgd'] = \"\"\n", " data_new['textField_ljibuzge'] = \"\"\n", " try:\n", " # 获取门店编码对应的信息\n", " result = data_NGV[data_NGV['org_code'] == data['textField_ljcfpjg6']]\n", " result = result.reset_index()\n", " # 获取手机号+判断手机号是否在公司名下\n", " # 获取手机号+判断手机号是否在公司名下\n", " if data[\"textField_ljcfpjg8\"] ==\"\": # 手机号未填写，默认填充ngv中对应的手机号\n", " data_new['textField_ljibuzgl'] = result['contact_mobile'][0]\n", " else:\n", " data_new['textField_ljibuzgl'] = data[\"textField_ljcfpjg8\"]\n", " cellPhone_group_name = queryCompanies(data_new['textField_ljibuzgl'])\n", " groupName_all = {}\n", " for data_two in cellPhone_group_name['data']['groupList']:\n", " groupName_all[data_two[\"groupName\"]]=data_two[\"groupId\"]\n", " try:\n", " if len(cellPhone_group_name['data']['groupList']) > 0:\n", " pass\n", " else:\n", " data_new['textField_ljibuzgl'] = data[\"textField_ljcfpjg8\"] \n", " data_new['textField_ljibuzgd'] = data_new['textField_ljibuzgd'] + \"F6中不存在该手机号；\"\n", " except:\n", " data_new['textField_ljibuzgd'] = data_new['textField_ljibuzgd'] + \"F6中不存在该手机号；\"\n", " \n", " if result['group_name'][0] not in groupName_all:\n", " data_new['textField_ljibuzgd'] = data_new['textField_ljibuzgd'] + \"联系电话与门店编码关系错误；\"\n", " # # 本次成交年限（1、2、3） selectField_ljicmdwn\n", " # if data[\"selectField_ljicmdwn\"] ==\"1\" or data[\"selectField_ljicmdwn\"] ==\"2\" or data[\"selectField_ljicmdwn\"] ==\"3\":\n", " # data_new['textField_ljibuzgd'] = data_new['textField_ljibuzgd'] + \"本次成交年限填写错误，请填写【1、2、3】；\"\n", " # 获取公司名称+门店名称+门店版本+到期日+门店编码\n", " data_new['textField_ljibuzgm'] = result['group_name'][0]\n", " data_new['textField_ljibuzgh'] = result['org_name'][0]\n", " data_new['textField_ljibuzgi'] = result['saas_edition_fmt'][0]\n", " data_new['textField_ljibuzgf'] = result['expiry_time'][0]\n", " data_new['textField_ljibuzgn'] = result['org_code'][0]\n", " # 获取上次成交价 textField_ljibuzgg\n", " OrgId_list = queryDetailByOrgId(result['id_own_org'][0])\n", " data_new['textField_ljibuzgg'] = OrgId_list['data']['lastSaasOrderPrice']/OrgId_list['data']['lastTransactionYear']\n", " # 获取提前续约立减额 textField_ljibuzgj\n", " try:\n", " queryPriceInfoBySkuIdnew = queryPriceInfoBySkuId(result['id_own_org'][0],all_skuId[result['saas_edition_fmt'][0] + \"1\"],\"false\")\n", " data_new['textField_ljibuzgj'] = queryPriceInfoBySkuIdnew['data']['renewalDiscountAmount']\n", " except:\n", " data_new['textField_ljibuzgj'] = \"0\"\n", " # 版本续约金额\n", " data_new['textField_lk6g6e2l'] = textField_ljwon3vt[result['saas_edition_fmt'][0] + \"1\"] # 版本续约金额\n", " # 申请优惠券金额 numberField_ljwn6n7w_value 判断优惠券申请金额是否填写，如果没有填写则进行填充\n", " # 本次成交价 numberField_ljwn6n7w_value 判断本次成交价是否填写，如果没有填写则进行填充 \n", " if data[\"numberField_ljwn6n7w_value\"] != \"\":\n", " data_new['textField_ljibuzgk'] = data[\"numberField_ljwn6n7w_value\"]\n", " if int(data[\"numberField_ljwn6n7w_value\"]) <= 0:\n", " data_new['textField_ljibuzgd'] = data_new['textField_ljibuzgd'] + \"本次成交价需要大于0，或者为空；\"\n", " elif data[\"numberField_ljwn6n7w_value\"] == \"\": # 判断本次成交价是否填写，如果没有填写则进行填充 默认取 上次成交价\n", " data_new['textField_ljibuzgk'] = data_new['textField_ljibuzgg']\n", " else:\n", " data_new['textField_ljibuzgk'] =data[\"numberField_ljwn6n7w_value\"]\n", " \n", " # 获取申请优惠券金额 textField_ljibuzgp 申请优惠券金额= 版本价格-提前立减额-本次成交价\n", " data_new['textField_ljibuzgp'] = int(data_new['textField_lk6g6e2l']) - int(data_new['textField_ljibuzgj']) - int(data_new['textField_ljibuzgk'])\n", "\n", " # # 获取本次成交价格 textField_ljibuzgk\n", " # if data[\"numberField_ljwn6n7w_value\"] ==\"\":\n", " # data_new['textField_ljibuzgk'] = int(textField_ljwon3vt[result['saas_edition_fmt'][0] + \"1\"]) - int(data_new['textField_ljibuzgp'])- int(data_new['textField_ljibuzgj'])\n", " # else:\n", " # data_new['textField_ljibuzgk'] = int(textField_ljwon3vt[result['saas_edition_fmt'][0] + \"1\"]) - int(data_new['textField_ljibuzgp']) - int(data_new['textField_ljibuzgj'])\n", " # 获取已有优惠券\n", " passportId=int(cellPhone_group_name['data']['passportId'])\n", " cellPhone=int(data_new['textField_ljibuzgl'])\n", " groupId = int(result['id_own_group'][0])\n", " skuList = queryskuList()\n", " # 过滤 仅入参对应版本的\n", " filtered_data = [item for item in skuList[\"data\"] if item['saasEditionName'] == result['saas_edition_fmt'][0]]\n", " filtered_data_skuList={'code': 200,\n", " 'data': filtered_data,\n", " 'message': 'SUCCESS'}\n", " couponAvailableQuery_list = couponAvailableQuery(cellPhone,groupId,filtered_data_skuList,passportId)\n", " for data_ONE in couponAvailableQuery_list['data']:\n", " data_new['textField_ljibuzge'] = data_new['textField_ljibuzge'] + data_ONE ['couponName'] + \"：\"+ str(data_ONE ['couponValue']) +\"；\"\n", " if \"优惠券\" in data_ONE ['couponName']:\n", " data_new['textField_ljibuzgd'] = data_new['textField_ljibuzgd'] +\"存在未使用的优惠券；\"\n", " \n", " # 是否低于限价 radioField_ljxz9ho5 是否发券 radioField_ljibuzgc 版本限价 textField_lk6hj6sw\n", " data_new['textField_lk6hj6sw'] = textField_ljxv26ip[result['saas_edition_fmt'][0] + \"1\"]\n", " if int(data_new['textField_ljibuzgk']) < int(textField_ljxv26ip[result['saas_edition_fmt'][0] + \"1\"]):\n", " data_new['radioField_ljxz9ho5'] = \"是\"\n", " # data_new['radioField_ljibuzgc'] = \"否\"\n", " else:\n", " data_new['radioField_ljxz9ho5'] = \"否\"\n", " # 是否的低于上次成交价 radioField_ljxz9ho6 是否发券 radioField_ljibuzgc\n", " if int(data_new['textField_ljibuzgk']) < int(data_new['textField_ljibuzgg']):\n", " data_new['radioField_ljxz9ho6'] = \"是\"\n", " # data_new['radioField_ljibuzgc'] = \"否\"\n", " else:\n", " data_new['radioField_ljxz9ho6'] = \"否\"\n", " # if int(data_new['textField_ljibuzgj']) > 0:\n", " # data_new['textField_ljibuzgd'] = \"存在提前续约立减额，不是30天内过期客户！\"\n", " # if int(data_new['textField_ljibuzgp']) <= 0:\n", " # data_new['textField_ljibuzgd'] = \"申请优惠金额需要大于0元！\"\n", " # # 集合 textField_ljy172na\n", " # data_new['textField_ljy172na'] = str({\n", " # \"cellPhone\":data_new[\"textField_ljibuzgl\"], # 收劵人手机号码\n", " # \"dingDingId\":form_data['data'][v]['originator']['userId'], # 申请人钉钉ID\n", " # \"stage\":2, # 所属阶段（新签：1，续约：2）\n", " # \"couponApplyType\":1, # 优惠券申请类型；新签：saas优惠劵申请：1，短信优惠劵申请：2，人工服务优惠劵申请：3；续约：saas续约优惠劵申请：1，saas升级优惠劵申请：2，短信优惠劵申请：3，人工服务优惠劵申请：4（暂不支持）\n", " # \"detailList\":[{ # 发劵明细列表\n", " # \"couponTemplateId\":all_couponTemplateId[result['saas_edition_fmt'][0] + \"1\"], # 劵模板ID\n", " # \"couponAmount\":int(data_new[\"textField_ljibuzgp\"]), # 劵金额，>0\n", " # \"grantGroup\":1, # 1:发送到公司+人，0:发送到人\n", " # \"groupId\":result['id_own_group'][0], # 发放公司ID，发在公司下=true是有值\n", " # \"skuId\":all_skuId[result['saas_edition_fmt'][0] + \"1\"], # 商品id \n", " # \"orgId\":result['id_own_org'][0], # 门店ID，（申请续约劵并且降级时必填）\n", " # \"demotion\":0, # 降级（是：1，否：0）\n", " # \"demotionSaaSEdition\":0 # 降级版本,门店降级时必填\n", " # }]\n", " # })\n", " # data_new['textField_ljy172na'] = data_new['textField_ljy172na'].replace(\"'\", '\"')\n", "\n", " # data_new['textField_ljy172na'] = data_new['textField_ljy172na'].replace(\" \", \"\")\n", " except:\n", " data_new['textField_ljibuzgd'] = \"数据读取异常：\" + data_new['textField_ljibuzgd']\n", " # CHECK结果校验 textField_ljibuzgd\n", " if data_new['textField_ljibuzgd'] == \"\":\n", " data_new['textField_ljibuzgd'] = \"正确\"\n", " list_new = list_new + 1\n", " # 回传结果\n", " if list_new == 1:\n", " tableField_data_new['tableField_ljibuzgq'] = [data_new]\n", " else:\n", " tableField_data_new['tableField_ljibuzgq'].append(data_new)\n", " res=aggree_approval(token=TOKEN, taskId=taskId, processInstanceId=form_data['data'][v]['processInstanceId'],res_new =\"2268275546837446\",formDataJson=tableField_data_new)\n", " print(form_data['data'][v]['processInstanceId'],res.json())\n", " time.sleep(2)\n", " elif taskId_new==\"sid-23822834-234a-b12a-dfea-a6ad1acc8c1b\": # API自动发券\n", " print(\"开始处理发券！\")\n", " # 读取NGV明细\n", " import psycopg2\n", " import pandas as pd\n", " # 获得连接\n", " conn = psycopg2.connect(database=\"f6_bi\", user=\"BASIC$ro_caowei\", password=\"!ro_caowei123\", host=\"hgprecn-cn-nif1vnv0y002-cn-shanghai.hologres.aliyuncs.com\", port=\"80\")\n", " # 获得游标对象，一个游标对象可以对数据库进行执行操作\n", " cursor = conn.cursor()\n", " import datetime\n", " now_time = datetime.datetime.now()\n", " yes_time = now_time + datetime.timedelta(days=-1)\n", " yes_time_nyr = int(yes_time.strftime('%Y%m%d'))# 获取前一天日期\n", " # sql语句 建表\n", " sql =f\"\"\"SELECT * FROM \"public\".\"holo_ads_report_saas_profile_ngv_detail_d\" WHERE \"date_id\" = '{yes_time_nyr}';\"\"\"\n", " # 执行语句\n", " cursor.execute(sql)\n", " # 获取结果集的每一行\n", " rows = cursor.fetchall()\n", " # 获取所有字段名\n", " all_fields = cursor.description\n", " #执行结果转化为dataframe\n", " col = []\n", " for i in all_fields:\n", " col.append(i[0])\n", " data_NGV = pd.DataFrame(list(rows),columns=col)\n", " # 关闭数据库连接\n", " cursor.close()\n", " conn.close()\n", " '''读取商品和券信息'''\n", " FORMID_TWO = \"FORM-6L966171O3RB70TJDB75R6RBI31V2NNACKCJLF1\" # 商品ID券ID关系表\n", " all_skuId = {}\n", " all_couponTemplateId = {}\n", " textField_ljwon3vt = {}\n", " textField_ljxv26ip = {}\n", " list_new = 0\n", " succeed_all = 0\n", " all_all= 0\n", " tableField_data_new = {}\n", " data_new = {}\n", " data_check = {}\n", " formDataJson_check = {}\n", " code_new = \"\"\n", " formDataJson_check['textField_ljcfpjgd'] = \"\"\n", " form_data_TWO = read_instances(token=TOKEN, formUuid=FORMID_TWO, page=1, n=100)\n", " PAGES_TWO = form_data_TWO.get('totalCount')//100 + 1\n", " for a in range(1, PAGES_TWO+1):\n", " form_data_TWO = read_instances(token=TOKEN, formUuid=FORMID_TWO, page=a, n=100)\n", " for data_TWO in form_data_TWO.get('data'):\n", " all_skuId[data_TWO['formData']['textField_vmqmrm1'] + data_TWO['formData']['textField_cfm11ut']] = data_TWO['formData']['textField_nt0h72h']\n", " all_couponTemplateId[data_TWO['formData']['textField_vmqmrm1'] + data_TWO['formData']['textField_cfm11ut']] = data_TWO['formData']['textField_47og525']\n", " textField_ljwon3vt[data_TWO['formData']['textField_vmqmrm1'] + data_TWO['formData']['textField_cfm11ut']] = data_TWO['formData']['textField_ljwon3vt']\n", " textField_ljxv26ip[data_TWO['formData']['textField_vmqmrm1'] + data_TWO['formData']['textField_cfm11ut']] = data_TWO['formData']['textField_ljxv26ip']\n", " FORMID = \"FORM-XHA66881FHMAR0F07GT4Y59GGA972DD6B5OHLB\" # [流程]自动化优惠券(+多年)\n", " # 读取[流程]自动化优惠券(+多年)\n", " form_data_new = read_instances_new(token=TOKEN, formUuid=FORMID, page=1, n=100,CREATE_FROM=CREATE_FROM, CREATE_TO=CREATE_TO)\n", " PAGES = form_data_new.get('totalCount')//100 + 1\n", " ALL_DATA_code = []\n", " \"\"\" 获取全量数据 \"\"\"\n", " for i in range(1, PAGES+1):\n", " form_data_new = read_instances_new(token=TOKEN, formUuid=FORMID, page=i, n=100,CREATE_FROM=CREATE_FROM, CREATE_TO=CREATE_TO)\n", " for data_one in form_data_new.get('data'):\n", " try:\n", " for j in range(0,len(data_one['data']['tableField_l1w05znp'])):\n", " ALL_DATA_code.append(str(data_one['data']['tableField_l1w05znp'][j]['selectField_l1wxjd8l']) + \"+\" + str(data_one['data']['tableField_l1w05znp'][j]['textField_l5j0ym23']))\n", " except:\n", " pass\n", " print(f'读取到 [[流程]自动化优惠券(+多年)表中 {len(ALL_DATA_code)} 条数据！')\n", " for data in form_data['data'][v]['data']['tableField_ljibuzgq']: # -----------------------------------------------\n", " updateFormDataJson_check = {}\n", " # 获取门店编码对应的信息\n", " result = data_NGV[data_NGV['org_code'] == data['textField_ljibuzgn']]\n", " result = result.reset_index()\n", " # 读取[流程]自动化优惠券(+多年)中存在该门店发券流程，避免重复发券\n", " if str(data['textField_ljibuzgn']) + \"+\" + str(all_couponTemplateId[result['saas_edition_fmt'][0] + \"1\"]) in ALL_DATA_code:\n", " # updateFormDataJson_check['textField_ljibuzgd'] = data['textField_ljibuzgd'] + \"[流程]自动化优惠券(+多年)中存在该门店发券流程！；\"\n", " # updateFormDataJson_check['radioField_ljxz9ho5'] = \"否\"\n", " # FORMID = \"FORM-0A966I81N40CV1B48U18UBZAA8R92STJC7IJL9\" # 【测试版本】批量自动发券\n", " # res=updateFormDataJson(TOKEN, FORMID, updateFormDataJson_check)\n", " # print(res.json)\n", " code_new = code_new + data[\"textField_ljibuzgl\"] + \"；\"\n", " else:\n", " # res=aggree_approval(token=TOKEN, taskId=taskId, processInstanceId=form_data['data'][v]['processInstanceId'],res_new =\"2268275546837446\",formDataJson=tableField_data_new)\n", " # print(form_data['data'][v]['processInstanceId'],res.json())\n", " # for data_ONE in couponAvailableQuery_list['data']:\n", " # if \"续约优惠券\" in data_ONE ['couponName']:\n", " # data_new['textField_ljibuzgd'] = data_new['textField_ljibuzgd'] +\"存在未使用的续约优惠券；\"\n", " # data_new['textField_ljibuzge'] = data_new['textField_ljibuzge'] + data_ONE ['couponName'] + \"：\"+ str(data_ONE ['couponValue']) +\"；\"\n", " # check_all = check_all + 1\n", " \n", " data_new['textField_ljy172na'] = str({\n", " \"cellPhone\":data[\"textField_ljibuzgl\"], # 收劵人手机号码\n", " \"dingDingId\":form_data['data'][v]['originator']['userId'], # 申请人钉钉ID\n", " \"stage\":2, # 所属阶段（新签：1，续约：2）\n", " \"couponApplyType\":1, # 优惠券申请类型；新签：saas优惠劵申请：1，短信优惠劵申请：2，人工服务优惠劵申请：3；续约：saas续约优惠劵申请：1，saas升级优惠劵申请：2，短信优惠劵申请：3，人工服务优惠劵申请：4（暂不支持）\n", " \"detailList\":[{ # 发劵明细列表\n", " \"couponTemplateId\":all_couponTemplateId[result['saas_edition_fmt'][0] + \"1\"], # 劵模板ID\n", " \"couponAmount\":int(float(data[\"textField_ljibuzgp\"])), # 劵金额，>0\n", " \"grantGroup\":1, # 1:发送到公司+人，0:发送到人\n", " \"groupId\":result['id_own_group'][0], # 发放公司ID，发在公司下=true是有值\n", " \"skuId\":all_skuId[result['saas_edition_fmt'][0] + \"1\"], # 商品id \n", " \"orgId\":result['id_own_org'][0], # 门店ID，（申请续约劵并且降级时必填）\n", " \"demotion\":0, # 降级（是：1，否：0）\n", " \"demotionSaaSEdition\":0 # 降级版本,门店降级时必填\n", " }]\n", " })\n", " data_list = {\n", " 'textField_ljchgz1f':data['textField_ljibuzgn'], # 门店编码\n", " 'textField_ljchgz1i':data_new['textField_ljy172na'], # 数据集\n", " 'textField_ljchgz1h':data['textField_ljibuzgd'], # check结果\n", " 'textField_ljy1x9kb':form_data['data'][v]['processInstanceId'], # 关联表单id\n", " 'textField_ljf6nf7s':data['radioField_ljibuzgc'] # 是否发券\n", " }\n", " ALL_formData = []\n", " ALL_formData.append(json.dumps(data_list, cls=NpEncoder)) \n", " FORMID_NEW = \"FORM-JD8668C1MFBC3ND4BWO736T7G5Q7318IC0YJLA\" # 【测试版本】自动触发明细表\n", " res = Batch_creation(FORMID_NEW,TOKEN,ALL_formData)\n", " print(data['textField_ljibuzgn'],data[\"textField_ljibuzgl\"],res)\n", " time.sleep(2)\n", " all_all = all_all + 1\n", " if data['textField_ljibuzgd'] == \"正确\" and data['radioField_ljibuzgc'] == \"是\":\n", " succeed_all = succeed_all + 1\n", " fail_all = all_all - succeed_all\n", " if len(code_new)>0:\n", " txt_all = f\"发送成功：{succeed_all}条；发送失败：{fail_all}条；{code_new}在[流程]自动化优惠券(+多年)中有申请记录！\"\n", " else:\n", " txt_all = f\"发送成功：{succeed_all}条；发送失败：{fail_all}条；\"\n", " formDataJson_check = {\n", " 'textField_ljcfpjgd':txt_all\n", " }\n", " res=aggree_approval(token=TOKEN, taskId=taskId, processInstanceId=form_data['data'][v]['processInstanceId'],res_new =\"2268275546837446\",formDataJson=formDataJson_check)\n", " print(form_data['data'][v]['processInstanceId'],res.json())\n", " time.sleep(2)" ] } ], "metadata": { "kernelspec": { "display_name": "F6processing", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.9.4" } }, "nbformat": 4, "nbformat_minor": 2 }