{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# 配置分区" ] }, { "cell_type": "code", "execution_count": 4, "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" ] } ], "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", "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", "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" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# CHECK" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "开始处理！\n", "7fd19ced-321d-46d2-9b6e-a901438d81fb {}\n" ] } ], "source": [ "\"\"\" 处理流程开始 \"\"\"\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", " 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", " couponAvailableQuery_list = couponAvailableQuery(cellPhone,groupId,skuList,passportId)\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", " # 是否低于限价 radioField_ljxz9ho5 是否发券 radioField_ljibuzgc 版本限价 textField_lk6hj6sw\n", " data_new['textField_lk6hj6sw'] = textField_ljxv26ip[result['saas_edition_fmt'][0] + \"1\"]\n", " if 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 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", " # # 集合 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", " \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", " for data in form_data['data'][v]['data']['tableField_ljibuzgq']:\n", " # 获取门店编码对应的信息\n", " result = data_NGV[data_NGV['org_code'] == data['textField_ljibuzgn']]\n", " result = result.reset_index()\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(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", " 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)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# 测试区" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "queryPriceInfoBySkuIdnew = queryPriceInfoBySkuId(result['id_own_org'][0],all_skuId[result['saas_edition_fmt'][0] + \"1\"],\"false\")\n", "queryPriceInfoBySkuIdnew['data']['renewalDiscountAmount']" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "'15190283526'" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "data_new['textField_ljibuzgl']" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [], "source": [ "# 获取提前续约立减额 textField_ljibuzgj\n", "try:\n", " renewalDiscountAmount = queryLastSaasInfo('13116269758')\n", " data_new['textField_ljibuzgj'] = renewalDiscountAmount[\"data\"][\"renewalDiscountAmount\"]\n", "except:\n", " data_new['textField_ljibuzgj'] = \"0\"" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "{'code': 200,\n", " 'data': {'businessType': 26,\n", " 'businessTypeDesc': 'Saas',\n", " 'category': 2,\n", " 'categoryDesc': 'Saas续约',\n", " 'couponPrice': 200.0,\n", " 'discountPrice': 600.0,\n", " 'number': 1.0,\n", " 'orderId': 90103253,\n", " 'orderPayFinishTime': 1651047426000,\n", " 'orderStatus': 6,\n", " 'orderStatusDesc': '已完成',\n", " 'price': 1799.0,\n", " 'projectname': '续约标准版-门店管理系统',\n", " 'renewalDiscountAmount': 400.0,\n", " 'reservationNumber': '1651047415446',\n", " 'serverDateDeductionPrice': 0.0,\n", " 'totalFee': 2399.0},\n", " 'message': 'SUCCESS'}" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "renewalDiscountAmount" ] }, { "cell_type": "code", "execution_count": 62, "metadata": {}, "outputs": [], "source": [ "# 获取已有优惠券\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", "couponAvailableQuery_list = couponAvailableQuery(cellPhone,groupId,skuList,passportId)\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']) +\"；\"" ] }, { "cell_type": "code", "execution_count": 65, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "'2499.0'" ] }, "execution_count": 65, "metadata": {}, "output_type": "execute_result" } ], "source": [ "str(data_ONE ['couponValue']) " ] }, { "cell_type": "code", "execution_count": 36, "metadata": {}, "outputs": [], "source": [ "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()" ] }, { "cell_type": "code", "execution_count": 52, "metadata": {}, "outputs": [], "source": [ "def couponAvailableQuery(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}&groupId={groupId}&skuList' + str(skuList)\n", "\n", " headers = {\n", " \"token\":\"af88h9KvLQrdvVhFhS8zD1yWx9cu\"\n", " }\n", "\n", " res = requests.post(api,headers=headers)\n", " return res.json()" ] }, { "cell_type": "code", "execution_count": 55, "metadata": {}, "outputs": [], "source": [ "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", "couponAvailableQuery_list = couponAvailableQuery(cellPhone,groupId,skuList,passportId)" ] }, { "cell_type": "code", "execution_count": 56, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "{'code': 200,\n", " 'data': [{'applyOrg': '10545511425556432399',\n", " 'applySkuList': ['14582097882934118703'],\n", " 'conditionType': 0,\n", " 'conditionValue': None,\n", " 'couponName': '至尊版1年续约优惠券',\n", " 'couponTemplateId': '125530',\n", " 'couponValue': 2499.0,\n", " 'customerId': '14365556958334901332',\n", " 'goodsRangeType': 0,\n", " 'groupName': '山东圣罗萨汽车维修养护有限公司',\n", " 'id': '11387979',\n", " 'validityBegin': 1689696000000,\n", " 'validityBeginStr': '2023-07-19',\n", " 'validityEnd': 1690300799000,\n", " 'validityEndStr': '2023-07-25'}],\n", " 'message': 'SUCCESS'}" ] }, "execution_count": 56, "metadata": {}, "output_type": "execute_result" } ], "source": [ "couponAvailableQuery_list" ] }, { "cell_type": "code", "execution_count": 30, "metadata": {}, "outputs": [], "source": [ "couponAvailableQuery_list = couponAvailableQuery(passportId,groupId,skuList)" ] }, { "cell_type": "code", "execution_count": 31, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "{'code': 200,\n", " 'data': [{'applyOrg': '10545511425556432399',\n", " 'applySkuList': ['14582097882934118703'],\n", " 'conditionType': 0,\n", " 'conditionValue': None,\n", " 'couponName': '至尊版1年续约优惠券',\n", " 'couponTemplateId': '125530',\n", " 'couponValue': 2499.0,\n", " 'customerId': '14365556958334901332',\n", " 'goodsRangeType': 0,\n", " 'groupName': '山东圣罗萨汽车维修养护有限公司',\n", " 'id': '11387979',\n", " 'validityBegin': 1689696000000,\n", " 'validityBeginStr': '2023-07-19',\n", " 'validityEnd': 1690300799000,\n", " 'validityEndStr': '2023-07-25'}],\n", " 'message': 'SUCCESS'}" ] }, "execution_count": 31, "metadata": {}, "output_type": "execute_result" } ], "source": [ "couponAvailableQuery_list" ] }, { "cell_type": "code", "execution_count": 123, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "{'tableField_ljcfpjg4': [{'numberField_ljwn6n7w_value': '',\n", " 'textField_ljcfpjg8': '',\n", " 'textField_ljcfpjg6': 'CHS202106110129113'},\n", " {'numberField_ljwn6n7w_value': '',\n", " 'textField_ljcfpjg8': '18165351165',\n", " 'textField_ljcfpjg6': 'CHS201904170027447'},\n", " {'numberField_ljwn6n7w_value': '1',\n", " 'textField_ljcfpjg8': '15829920707',\n", " 'textField_ljcfpjg6': 'CHS202207090184044',\n", " 'numberField_ljwn6n7w': 1},\n", " {'numberField_ljwn6n7w_value': '',\n", " 'textField_ljcfpjg8': '13679207172',\n", " 'textField_ljcfpjg6': 'CHS202207140184433'}]}" ] }, "execution_count": 123, "metadata": {}, "output_type": "execute_result" } ], "source": [ "form_data['data'][v]['data']" ] }, { "cell_type": "code", "execution_count": 94, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "{'textField_ljibuzgl': '18165351165',\n", " 'textField_ljibuzgm': '西安铂赛汽车维修服务有限责任公司',\n", " 'textField_ljibuzgh': '西安铂赛汽车维修服务有限责任公司',\n", " 'textField_ljibuzgi': '基础版',\n", " 'textField_ljibuzgf': '2023-07-13 16:47:00',\n", " 'textField_ljibuzgn': 'CHS201904170027447',\n", " 'textField_ljibuzgg': 999.0,\n", " 'textField_ljibuzgj': 0.0,\n", " 'textField_ljibuzgp': 600.0,\n", " 'textField_ljibuzgk': 999}" ] }, "execution_count": 94, "metadata": {}, "output_type": "execute_result" } ], "source": [ "formDataJson_check = form_data['data'][v]['data']" ] }, { "cell_type": "code", "execution_count": 91, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "{'code': 200,\n", " 'data': [{'infoId': '14582097882934062210',\n", " 'pkId': '15632863102804967489',\n", " 'saasEdition': 10,\n", " 'saasEditionName': '基础版',\n", " 'sellMode': 0,\n", " 'sellPrice': 1599.0,\n", " 'spuName': '续约基础版-门店管理系统',\n", " 'type': 2,\n", " 'validityPeriod': 12},\n", " {'infoId': '14582097882934062599',\n", " 'pkId': '15632864029540634690',\n", " 'saasEdition': 20,\n", " 'saasEditionName': '标准版',\n", " 'sellMode': 0,\n", " 'sellPrice': 2399.0,\n", " 'spuName': '续约标准版-门店管理系统',\n", " 'type': 2,\n", " 'validityPeriod': 12},\n", " {'infoId': '14582097882934062350',\n", " 'pkId': '15632863364235939894',\n", " 'saasEdition': 10,\n", " 'saasEditionName': '基础版',\n", " 'sellMode': 0,\n", " 'sellPrice': 2998.0,\n", " 'spuName': '续约基础版-门店管理系统2年',\n", " 'type': 2,\n", " 'validityPeriod': 24},\n", " {'infoId': '14582097882934061696',\n", " 'pkId': '15632864740751982653',\n", " 'saasEdition': 30,\n", " 'saasEditionName': '旗舰版',\n", " 'sellMode': 0,\n", " 'sellPrice': 3599.0,\n", " 'spuName': '续约旗舰版-门店管理系统',\n", " 'type': 2,\n", " 'validityPeriod': 12},\n", " {'infoId': '14582097882934062477',\n", " 'pkId': '15632863778524119101',\n", " 'saasEdition': 10,\n", " 'saasEditionName': '基础版',\n", " 'sellMode': 0,\n", " 'sellPrice': 4397.0,\n", " 'spuName': '续约基础版-门店管理系统3年',\n", " 'type': 2,\n", " 'validityPeriod': 36},\n", " {'infoId': '14582097882934061475',\n", " 'pkId': '15632864303034417220',\n", " 'saasEdition': 20,\n", " 'saasEditionName': '标准版',\n", " 'sellMode': 0,\n", " 'sellPrice': 4398.0,\n", " 'spuName': '续约标准版-门店管理系统2年',\n", " 'type': 2,\n", " 'validityPeriod': 24},\n", " {'infoId': '14582097882934119922',\n", " 'pkId': '15632866651362316368',\n", " 'saasEdition': 35,\n", " 'saasEditionName': '尊享版',\n", " 'sellMode': 0,\n", " 'sellPrice': 4999.0,\n", " 'spuName': '续约尊享版-门店管理系统',\n", " 'type': 2,\n", " 'validityPeriod': 12},\n", " {'infoId': '14582097882934118703',\n", " 'pkId': '15632865553121558537',\n", " 'saasEdition': 40,\n", " 'saasEditionName': '至尊版',\n", " 'sellMode': 0,\n", " 'sellPrice': 5999.0,\n", " 'spuName': '续约至尊版-门店管理系统',\n", " 'type': 2,\n", " 'validityPeriod': 12},\n", " {'infoId': '14582097882934061530',\n", " 'pkId': '15632864509033467924',\n", " 'saasEdition': 20,\n", " 'saasEditionName': '标准版',\n", " 'sellMode': 0,\n", " 'sellPrice': 6397.0,\n", " 'spuName': '续约标准版-门店管理系统3年',\n", " 'type': 2,\n", " 'validityPeriod': 36},\n", " {'infoId': '14582097882934062790',\n", " 'pkId': '15632864934621102091',\n", " 'saasEdition': 30,\n", " 'saasEditionName': '旗舰版',\n", " 'sellMode': 0,\n", " 'sellPrice': 6598.0,\n", " 'spuName': '续约旗舰版-门店管理系统2年',\n", " 'type': 2,\n", " 'validityPeriod': 24},\n", " {'infoId': '14582097882934120039',\n", " 'pkId': '15632866883714170973',\n", " 'saasEdition': 35,\n", " 'saasEditionName': '尊享版',\n", " 'sellMode': 0,\n", " 'sellPrice': 9198.0,\n", " 'spuName': '续约尊享版-门店管理系统2年',\n", " 'type': 2,\n", " 'validityPeriod': 24},\n", " {'infoId': '14582097882934062861',\n", " 'pkId': '15632865133871513626',\n", " 'saasEdition': 30,\n", " 'saasEditionName': '旗舰版',\n", " 'sellMode': 0,\n", " 'sellPrice': 9597.0,\n", " 'spuName': '续约旗舰版-门店管理系统3年',\n", " 'type': 2,\n", " 'validityPeriod': 36},\n", " {'infoId': '14582097882934120240',\n", " 'pkId': '15632867295586435075',\n", " 'saasEdition': 50,\n", " 'saasEditionName': '皇冠版',\n", " 'sellMode': 0,\n", " 'sellPrice': 9999.0,\n", " 'spuName': '续约皇冠版-门店管理系统',\n", " 'type': 2,\n", " 'validityPeriod': 12},\n", " {'infoId': '14582097882934118768',\n", " 'pkId': '15632866197765111902',\n", " 'saasEdition': 40,\n", " 'saasEditionName': '至尊版',\n", " 'sellMode': 0,\n", " 'sellPrice': 10998.0,\n", " 'spuName': '续约至尊版-门店管理系统2年',\n", " 'type': 2,\n", " 'validityPeriod': 24},\n", " {'infoId': '14582097882934120138',\n", " 'pkId': '15632867088366845997',\n", " 'saasEdition': 35,\n", " 'saasEditionName': '尊享版',\n", " 'sellMode': 0,\n", " 'sellPrice': 13397.0,\n", " 'spuName': '续约尊享版-门店管理系统3年',\n", " 'type': 2,\n", " 'validityPeriod': 36},\n", " {'infoId': '14582097882934119826',\n", " 'pkId': '15632866379017768961',\n", " 'saasEdition': 40,\n", " 'saasEditionName': '至尊版',\n", " 'sellMode': 0,\n", " 'sellPrice': 15997.0,\n", " 'spuName': '续约至尊版-门店管理系统3年',\n", " 'type': 2,\n", " 'validityPeriod': 36},\n", " {'infoId': '14582097882934120287',\n", " 'pkId': '15632867515841925177',\n", " 'saasEdition': 50,\n", " 'saasEditionName': '皇冠版',\n", " 'sellMode': 0,\n", " 'sellPrice': 18998.0,\n", " 'spuName': '续约皇冠版-门店管理系统2年',\n", " 'type': 2,\n", " 'validityPeriod': 24},\n", " {'infoId': '14582097882934119441',\n", " 'pkId': '15632867688550776879',\n", " 'saasEdition': 50,\n", " 'saasEditionName': '皇冠版',\n", " 'sellMode': 0,\n", " 'sellPrice': 27997.0,\n", " 'spuName': '续约皇冠版-门店管理系统3年',\n", " 'type': 2,\n", " 'validityPeriod': 36}],\n", " 'message': 'SUCCESS'}" ] }, "execution_count": 91, "metadata": {}, "output_type": "execute_result" } ], "source": [ "skuList" ] }, { "cell_type": "code", "execution_count": 90, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "11240984669917040519" ] }, "execution_count": 90, "metadata": {}, "output_type": "execute_result" } ], "source": [ "groupId" ] }, { "cell_type": "code", "execution_count": 89, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "14365556958331777365" ] }, "execution_count": 89, "metadata": {}, "output_type": "execute_result" } ], "source": [ "passportId" ] }, { "cell_type": "code", "execution_count": 88, "metadata": {}, "outputs": [ { "ename": "TypeError", "evalue": "'dict' object is not callable", "output_type": "error", "traceback": [ "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m", "\u001b[1;31mTypeError\u001b[0m Traceback (most recent call last)", "\u001b[1;32m\u001b[0m in \u001b[0;36m\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mcouponAvailableQuery_list\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mcouponAvailableQuery\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mpassportId\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mgroupId\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mskuList\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m", "\u001b[1;31mTypeError\u001b[0m: 'dict' object is not callable" ] } ], "source": [ "couponAvailableQuery_list = couponAvailableQuery(passportId,groupId,skuList)" ] }, { "cell_type": "code", "execution_count": 81, "metadata": {}, "outputs": [], "source": [ "cellPhone_group_name = queryCompanies(\"13679207172\")" ] }, { "cell_type": "code", "execution_count": 67, "metadata": {}, "outputs": [], "source": [ "querysku_List = queryskuList()" ] }, { "cell_type": "code", "execution_count": 68, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "{'code': 200,\n", " 'data': {'f6User': True,\n", " 'groupList': [{'groupId': '11240984669917396094',\n", " 'groupName': '嘀嗒名车维修服务中心'}],\n", " 'passportId': '14365556958331777365'},\n", " 'message': 'SUCCESS'}" ] }, "execution_count": 68, "metadata": {}, "output_type": "execute_result" } ], "source": [ "cellPhone_group_name" ] }, { "cell_type": "code", "execution_count": 83, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "{'code': 200,\n", " 'data': [{'applyOrg': '0',\n", " 'applySkuList': ['10546443564011488721',\n", " '10546443564011489866',\n", " '10546443564011491837',\n", " '10546443564011487432',\n", " '10546443564011486031',\n", " '10546443564011484540'],\n", " 'conditionType': 1,\n", " 'conditionValue': 100.0,\n", " 'couponName': '邀请有礼-新客奖励（短信包）',\n", " 'couponTemplateId': '75455',\n", " 'couponValue': 50.0,\n", " 'customerId': '14365556958331777365',\n", " 'goodsRangeType': 0,\n", " 'groupName': '',\n", " 'id': '8562643',\n", " 'validityBegin': 1657728000000,\n", " 'validityBeginStr': '2022-07-14',\n", " 'validityEnd': 1689263999000,\n", " 'validityEndStr': '2023-07-13'},\n", " {'applyOrg': '11240984669917396094',\n", " 'applySkuList': ['14582097882934062210'],\n", " 'conditionType': 0,\n", " 'conditionValue': None,\n", " 'couponName': '基础版1年续约优惠券',\n", " 'couponTemplateId': '125514',\n", " 'couponValue': 600.0,\n", " 'customerId': '14365556958331777365',\n", " 'goodsRangeType': 0,\n", " 'groupName': '嘀嗒名车维修服务中心',\n", " 'id': '11325321',\n", " 'validityBegin': 1688486400000,\n", " 'validityBeginStr': '2023-07-05',\n", " 'validityEnd': 1689091199000,\n", " 'validityEndStr': '2023-07-11'}],\n", " 'message': 'SUCCESS'}" ] }, "execution_count": 83, "metadata": {}, "output_type": "execute_result" } ], "source": [ "passportId=int(cellPhone_group_name['data']['passportId'])\n", "groupId = int(result['id_own_group'][0])\n", "skuList = queryskuList()\n", "couponAvailableQuery = couponAvailableQuery(passportId,groupId,skuList)\n", "for data_ONE in couponAvailableQuery['data']:\n", " if data_ONE ['couponName'] ==\"基础版1年续约优惠券\":\n", " data_new['textField_ljibuzgd'] = data_new['textField_ljibuzgd'] + data_ONE ['couponName'] + \"：\"+ data_ONE ['couponValue'] +\"；\"" ] }, { "cell_type": "code", "execution_count": 85, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "{'applyOrg': '0', 'applySkuList': ['10546443564011488721', '10546443564011489866', '10546443564011491837', '10546443564011487432', '10546443564011486031', '10546443564011484540'], 'conditionType': 1, 'conditionValue': 100.0, 'couponName': '邀请有礼-新客奖励（短信包）', 'couponTemplateId': '75455', 'couponValue': 50.0, 'customerId': '14365556958331777365', 'goodsRangeType': 0, 'groupName': '', 'id': '8562643', 'validityBegin': 1657728000000, 'validityBeginStr': '2022-07-14', 'validityEnd': 1689263999000, 'validityEndStr': '2023-07-13'}\n", "{'applyOrg': '11240984669917396094', 'applySkuList': ['14582097882934062210'], 'conditionType': 0, 'conditionValue': None, 'couponName': '基础版1年续约优惠券', 'couponTemplateId': '125514', 'couponValue': 600.0, 'customerId': '14365556958331777365', 'goodsRangeType': 0, 'groupName': '嘀嗒名车维修服务中心', 'id': '11325321', 'validityBegin': 1688486400000, 'validityBeginStr': '2023-07-05', 'validityEnd': 1689091199000, 'validityEndStr': '2023-07-11'}\n" ] } ], "source": [ "for data_ONE in couponAvailableQuery['data']:\n", " if data_ONE ['couponName'] ==\"基础版1年续约优惠券\":\n", " data_new['textField_ljibuzgd'] = data_new['textField_ljibuzgd'] + data_ONE ['couponName'] + \"：\"+ data_ONE ['couponValue'] +\"；\"" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "OrgId_list1 = couponAvailableQuery(OrgId_list)" ] }, { "cell_type": "code", "execution_count": 63, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "1599" ] }, "execution_count": 63, "metadata": {}, "output_type": "execute_result" } ], "source": [ "int(textField_ljwon3vt[result['saas_edition_fmt'][0] + \"1\"])" ] }, { "cell_type": "code", "execution_count": 65, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0.0" ] }, "execution_count": 65, "metadata": {}, "output_type": "execute_result" } ], "source": [ "data_new['textField_ljibuzgj']" ] }, { "cell_type": "code", "execution_count": 64, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "'1'" ] }, "execution_count": 64, "metadata": {}, "output_type": "execute_result" } ], "source": [ "data_new['textField_ljibuzgp']" ] }, { "cell_type": "code", "execution_count": 57, "metadata": {}, "outputs": [ { "ename": "TypeError", "evalue": "unsupported operand type(s) for -: 'str' and 'float'", "output_type": "error", "traceback": [ "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m", "\u001b[1;31mTypeError\u001b[0m Traceback (most recent call last)", "\u001b[1;32m\u001b[0m in \u001b[0;36m\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mtextField_ljwon3vt\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mresult\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'saas_edition_fmt'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m+\u001b[0m \u001b[1;34m\"1\"\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m-\u001b[0m \u001b[0mdata_new\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'textField_ljibuzgg'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m", "\u001b[1;31mTypeError\u001b[0m: unsupported operand type(s) for -: 'str' and 'float'" ] } ], "source": [ "textField_ljwon3vt[result['saas_edition_fmt'][0] + \"1\"] - data_new['textField_ljibuzgg']" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "OrgId_list1 = couponAvailableQuery(OrgId_list)" ] }, { "cell_type": "code", "execution_count": 43, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "'13679207172'" ] }, "execution_count": 43, "metadata": {}, "output_type": "execute_result" } ], "source": [ "data_new['textField_ljibuzgl']" ] }, { "cell_type": "code", "execution_count": 48, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0.0" ] }, "execution_count": 48, "metadata": {}, "output_type": "execute_result" } ], "source": [ "renewalDiscountAmount = queryLastSaasInfo(data_new['textField_ljibuzgl'])\n", "renewalDiscountAmount[\"data\"][\"renewalDiscountAmount\"]" ] }, { "cell_type": "code", "execution_count": 58, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "'1599'" ] }, "execution_count": 58, "metadata": {}, "output_type": "execute_result" } ], "source": [ "'''读取商品和券信息'''\n", "FORMID_TWO = \"FORM-6L966171O3RB70TJDB75R6RBI31V2NNACKCJLF1\" # 商品ID券ID关系表\n", "all_skuId = {}\n", "all_couponTemplateId = {}\n", "textField_ljwon3vt = {}\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 in form_data_TWO.get('data'):\n", " all_skuId[data['formData']['textField_vmqmrm1'] + data['formData']['textField_cfm11ut']] = data['formData']['textField_nt0h72h']\n", " all_couponTemplateId[data['formData']['textField_vmqmrm1'] + data['formData']['textField_cfm11ut']] = data['formData']['textField_47og525']\n", " textField_ljwon3vt[data['formData']['textField_vmqmrm1'] + data['formData']['textField_cfm11ut']] = data['formData']['textField_ljwon3vt']\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": 56, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "{'textField_47og525': '125538',\n", " 'textField_nt0h72h': '15632867688550776879',\n", " 'textField_cfm11ut': '3',\n", " 'textField_vmqmrm1': '皇冠版'}" ] }, "execution_count": 56, "metadata": {}, "output_type": "execute_result" } ], "source": [ "data['formData']" ] }, { "cell_type": "code", "execution_count": 36, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "999.0" ] }, "execution_count": 36, "metadata": {}, "output_type": "execute_result" } ], "source": [ "OrgId_list = queryDetailByOrgId(result['id_own_org'][0])\n", "lastSaasOrderPrice = OrgId_list['data']['lastSaasOrderPrice']/OrgId_list['data']['lastTransactionYear']\n", "lastSaasOrderPrice" ] }, { "cell_type": "code", "execution_count": 34, "metadata": {}, "outputs": [], "source": [ "# 获取公司名称+门店名称+门店版本+到期日\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]" ] }, { "cell_type": "code", "execution_count": 35, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "{'textField_ljibuzgl': '13679207172',\n", " 'textField_ljibuzgm': '嘀嗒名车维修服务中心',\n", " 'textField_ljibuzgh': '嘀嗒名车维修服务中心',\n", " 'textField_ljibuzgi': '基础版',\n", " 'textField_ljibuzgf': '2023-07-14 00:00:00'}" ] }, "execution_count": 35, "metadata": {}, "output_type": "execute_result" } ], "source": [ "data_new" ] }, { "cell_type": "code", "execution_count": 33, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "{'textField_ljibuzgl': '17375726227'}\n", "{'textField_ljibuzgl': '18165351165'}\n", "{'textField_ljibuzgl': '15829920707'}\n", "{'textField_ljibuzgl': '13679207172'}\n" ] } ], "source": [ "for data in form_data['data'][v]['data']['tableField_ljcfpjg4']:\n", " # 获取门店编码对应的信息\n", " result = data_NGV[data_NGV['org_code'] == data['textField_ljcfpjg6']]\n", " result = result.reset_index()\n", " # 基础字段预设\n", " data_new = {}\n", " # 获取手机号+判断手机号是否在公司名下\n", " if data[\"textField_ljcfpjg8\"] ==\"\": # 手机号未填写，默认填充ngv中对应的手机号\n", " data_new['textField_ljibuzgl'] = result['contact_mobile'][0]\n", " else:\n", " try:\n", " cellPhone_group_name = queryCompanies(data[\"textField_ljcfpjg8\"])\n", " if len(cellPhone_group_name['data']['groupList']) > 0:\n", " data_new['textField_ljibuzgl'] = data[\"textField_ljcfpjg8\"] \n", " except:\n", " data_new['textField_ljibuzgd'] = data_new['textField_ljibuzgd'] + \"F6中不存在该手机号；\"\n", "\n", " print(data_new)" ] }, { "cell_type": "code", "execution_count": 26, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "{'嘀嗒名车维修服务中心': '11240984669917396094'}" ] }, "execution_count": 26, "metadata": {}, "output_type": "execute_result" } ], "source": [ "groupName_all" ] }, { "cell_type": "code", "execution_count": 32, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "1" ] }, "execution_count": 32, "metadata": {}, "output_type": "execute_result" } ], "source": [ "len(cellPhone_group_name['data']['groupList'])" ] }, { "cell_type": "code", "execution_count": 19, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "'13679207172'" ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "result = data_NGV[data_NGV['org_code'] == data['textField_ljcfpjg6']]\n", "result = result.reset_index()\n", "result['contact_mobile'][0]" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": 17, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
indexdate_iddate_fmtid_own_groupgroup_nameid_own_orgorg_nameorg_codegroup_gradeorg_type...org_sizequalification_type_fmtbusiness_scope_fmtstore_type_fmtareastation_numberheader_type_fmtorg_stageg_count_this_monthsaas_customer_type
023934202307092023-07-0911240984669917396094嘀嗒名车维修服务中心11240984669917403098嘀嗒名车维修服务中心CHS202207140184433普通客户（VIP）一般...中三类资格保养,改装,洗车,维修,美容,轮胎,钣喷综合修理厂600.0010自有单店-无需选图片续约9D
\n", "

1 rows × 140 columns

\n", "
" ], "text/plain": [ " index date_id date_fmt id_own_group group_name \n", "0 23934 20230709 2023-07-09 11240984669917396094 嘀嗒名车维修服务中心 \\\n", "\n", " id_own_org org_name org_code group_grade org_type \n", "0 11240984669917403098 嘀嗒名车维修服务中心 CHS202207140184433 普通客户（VIP） 一般 \\\n", "\n", " ... org_size qualification_type_fmt business_scope_fmt store_type_fmt \n", "0 ... 中 三类资格 保养,改装,洗车,维修,美容,轮胎,钣喷 综合修理厂 \\\n", "\n", " area station_number header_type_fmt org_stage g_count_this_month \n", "0 600.00 10 自有单店-无需选图片 续约 9 \\\n", "\n", " saas_customer_type \n", "0 D \n", "\n", "[1 rows x 140 columns]" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "result" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "{'selectField_ljicmdwn_id': '2',\n", " 'textField_ljcfpjg5': '',\n", " 'textField_ljcfpjg8': '13679207172',\n", " 'selectField_ljicmdwn': '2',\n", " 'textField_ljcfpjg6': 'CHS202207140184433'}" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "data" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# 备份" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "\"\"\" 处理流程开始 \"\"\"\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", " 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", " \"\"\" 读取相关数据 \"\"\"\n", " '''读取商品和券信息'''\n", " FORMID_TWO = \"FORM-6L966171O3RB70TJDB75R6RBI31V2NNACKCJLF1\" # 商品ID券ID关系表\n", " all_skuId = {}\n", " all_couponTemplateId = {}\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 in form_data_TWO.get('data'):\n", " all_skuId[data['formData']['textField_vmqmrm1'] + data['formData']['textField_cfm11ut']] = data['formData']['textField_nt0h72h']\n", " all_couponTemplateId[data['formData']['textField_vmqmrm1'] + data['formData']['textField_cfm11ut']] = data['formData']['textField_47og525']\n", " # print(len(all_skuId),len(all_couponTemplateId))\n", "\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", "\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_ONE = \"FORM-NO766591Q79CIKPVE1WY067WKS033L9YPHWJLL\" # 【禁止提交】自动触发明细表\n", " all_CODE = {}\n", " form_data_ONE = read_instances(token=TOKEN, formUuid=FORMID_ONE, page=1, n=100)\n", " PAGES_ONE = form_data_ONE.get('totalCount')//100 + 1\n", " for F in range(1, PAGES_ONE+1):\n", " form_data_ONE = read_instances(token=TOKEN, formUuid=FORMID_ONE, page=F, n=100)\n", " for data in form_data_ONE.get('data'):\n", " if data['formData']['textField_ljchgz1h'] ==\"同意\":\n", " all_CODE[data['formData']['textField_ljchgz1f']] = data['formData']['textField_ljchgz1f']\n", "\n", "\n", " \"\"\" 开始分别读取数据并填充数据进行创建 \"\"\"\n", " succeed_all = 0\n", " fail_all = 0\n", " txt_code_all = \"\"\n", " formDataJson_check = form_data['data'][v]['data']\n", " list_new = 0\n", " for data in form_data['data'][v]['data']['tableField_ljcfpjg4']:\n", " result = data_NGV[data_NGV['org_code'] == data['textField_ljcfpjg6']]\n", " result = result.reset_index()\n", " ALL_formData = []\n", " data_new = {}\n", " try:\n", " data_new = {\n", " 'textField_ljchgz12':data[\"textField_ljcfpjg8\"],#\"cellPhone\"\n", " 'textField_ljchgz16':\"2268275546837446\",#\"dingDingId\"\n", " 'textField_ljchgz17':2,#\"stage\"\n", " 'textField_ljchgz18':1,#\"couponApplyType\"\n", " 'textField_ljchgz19':all_couponTemplateId[result['saas_edition_fmt'][0] + data['textField_ljcgefye']],#\"couponTemplateId\"\n", " 'textField_ljchgz1a':data[\"textField_ljcfpjg5\"],#\"couponAmount\"\n", " 'textField_ljchgz1b':1,#\"grantGroup\"\n", " 'textField_ljchgz1c':result['id_own_group'][0],#\"groupId\"\n", " 'textField_ljchgz1d':all_skuId[result['saas_edition_fmt'][0] + data['textField_ljcgefye']],#\"skuId\"\n", " 'textField_ljchgz1e':result['id_own_org'][0],#\"orgId\"\n", " 'textField_ljchgz13':0,#\"demotion\"\n", " 'textField_ljchgz14':0,#\"demotionSaaSEdition\"\n", " 'textField_ljchgz15':data[\"textField_ljcfpjg7\"],#\"公司名称\"\n", " 'textField_ljchgz1f':data[\"textField_ljcfpjg6\"],#\"门店编码\"\n", " # 'textField_ljchgz1i':data[\"数据集\"][i],#\"数据集\"\n", " 'textField_ljchgz1h':\"同意\",#\"check结果\"\n", " }\n", " data_new['textField_ljchgz1i'] = str({\n", " \"cellPhone\":data[\"textField_ljcfpjg8\"], # 收劵人手机号码\n", " \"dingDingId\":\"2268275546837446\", # 申请人钉钉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] + data['textField_ljcgefye']], # 劵模板ID\n", " \"couponAmount\":int(data[\"textField_ljcfpjg5\"]), # 劵金额，>0\n", " \"grantGroup\":1, # 1:发送到公司+人，0:发送到人\n", " \"groupId\":result['id_own_group'][0], # 发放公司ID，发在公司下=true是有值\n", " \"skuId\":all_skuId[result['saas_edition_fmt'][0] + data['textField_ljcgefye']], # 商品id \n", " \"orgId\":result['id_own_org'][0], # 门店ID，（申请续约劵并且降级时必填）\n", " \"demotion\":0, # 降级（是：1，否：0）\n", " \"demotionSaaSEdition\":0 # 降级版本,门店降级时必填\n", " }]\n", " })\n", " data_new['textField_ljchgz1i'] = data_new['textField_ljchgz1i'].replace(\"'\", '\"')\n", " data_new['textField_ljchgz1i'] = data_new['textField_ljchgz1i'].replace(\" \", \"\")\n", " \n", " if data[\"textField_ljcfpjg7\"] != result['group_name'][0]:\n", " data_new['textField_ljchgz1h'] = data_new['textField_ljchgz1h'] + \"公司名称与门店编码对应的group_name关系错误！\"\n", " if data[\"textField_ljcfpjg7\"] not in groupName_all:\n", " data_new['textField_ljchgz1h'] = data_new['textField_ljchgz1h'] + \"联系电话与公司名称对应的contact_mobile关系错误！\"\n", " except:\n", " data_new['textField_ljchgz1h'] = \"数据异常无法创建集合数据！\"\n", " try:\n", " cellPhone_group_name = queryCompanies(data[\"textField_ljcfpjg8\"])\n", " groupName_all = {}\n", " for data_two in cellPhone_group_name['data']['groupList']:\n", " groupName_all[data_two[\"groupName\"]]=data_two[\"groupId\"]\n", " except:\n", " data_new['textField_ljchgz1h'] = data_new['textField_ljchgz1h'] + \"F6中不存在该手机号！\"\n", " try:\n", " OrgId_list = queryDetailByOrgId(result['id_own_org'][0])\n", " lastSaasOrderPrice = OrgId_list['data']['lastSaasOrderPrice']/OrgId_list['data']['lastTransactionYear']\n", " except:\n", " data_new['textField_ljchgz1h'] = data_new['textField_ljchgz1h'] + \"门店编码查询不到上次购买价\"\n", " \n", " if data[\"textField_ljcfpjg6\"] in all_CODE:\n", " data_new['textField_ljchgz1h'] = data_new['textField_ljchgz1h'] + \"已经发过券了，请勿重复发券！\"\n", " if data_new['textField_ljchgz1h'] == \"同意\":\n", " succeed_all = succeed_all + 1\n", " else:\n", " fail_all = succeed_all + 1\n", " data_new['textField_ljchgz1h'] = data_new['textField_ljchgz1h'].replace(\"同意\", \"\")\n", " try:\n", " txt_code_all = txt_code_all + data[\"textField_ljcfpjg7\"]\n", " except:\n", " pass\n", " # check结果回传\n", " \n", " formDataJson_check['tableField_ljcfpjg4'][list_new]['textField_lje1r7at'] = data_new['textField_ljchgz1h']\n", " if data_new['textField_ljchgz1h'] == \"同意\":\n", " formDataJson_check['tableField_ljcfpjg4'][list_new]['radioField_lje1r7au'] = \"是\"\n", " else:\n", " formDataJson_check['tableField_ljcfpjg4'][list_new]['radioField_lje1r7au'] = \"否\"\n", " list_new = list_new + 1\n", " ALL_formData.append(json.dumps(data_new, cls=NpEncoder)) \n", " FORMID_NEW = \"FORM-XHA66881AUMBJR6171OZXBSJD7F929XA7HCJLX\" # 【禁止提交】自动触发明细表\n", " res = Batch_creation(FORMID_NEW,TOKEN,ALL_formData)\n", " print(res)\n", " time.sleep(2)\n", " txt_all = f\"发送成功：{succeed_all}条；发送失败：{fail_all}条；{txt_code_all}\"\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)\n", " " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# 分割---------------------------------------------------------" ] }, { "attachments": {}, "cell_type": "markdown", "metadata": {}, "source": [ "# 读取审批结果\n", "# 补充字段\n", "# check\n", "# 将数据创建到明细表中\n", "# 每2秒同意一条并调用集成自动化自动发券" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "{'code': 200,\n", " 'data': {'f6User': True,\n", " 'groupList': [],\n", " 'passportId': '10546172455180024665'},\n", " 'message': 'SUCCESS'}" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import pandas as pd\n", "import time\n", "import requests\n", "import json\n", "\n", "url = 'http://manage.f6yc.com/hive-admin/yida/account/query?cellPhone=13572193083'\n", "\n", "headers = {\n", " \"token\":\"af88h9KvLQrdvVhFhS8zD1yWx9cu\"\n", "}\n", "\n", "res = requests.post(url,headers=headers)\n", "res.json()" ] }, { "cell_type": "code", "execution_count": 90, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "{'code': 200,\n", " 'data': {'abbreviation': '恒鑫隆汽车服务',\n", " 'code': 'CHS202111160145737',\n", " 'endDate': '2023-06-08',\n", " 'lastSaasOrderPrice': 1199.0,\n", " 'lastTransactionYear': 1.0,\n", " 'name': '深圳市恒鑫隆汽车服务有限公司',\n", " 'orgStatus': 0,\n", " 'pkId': None,\n", " 'saas': True,\n", " 'saasEdition': 10,\n", " 'saasEditionName': '基础版',\n", " 'saasStatus': 3,\n", " 'serviceImplPrincipal': '严冬延'},\n", " 'message': 'SUCCESS'}" ] }, "execution_count": 90, "metadata": {}, "output_type": "execute_result" } ], "source": [ "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", "OrgId_list = queryDetailByOrgId(\"11240984669917181016\")\n", "lastSaasOrderPrice = OrgId_list['data']['lastSaasOrderPrice']/OrgId_list['data']['lastTransactionYear']" ] }, { "cell_type": "code", "execution_count": 86, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "开始处理！\n", "29469fde-bdaf-4180-8d3a-3a4a8c596fcd {}\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", "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", "\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", "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", "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", "# FORMID_NEW = \"FORM-XHA66881AUMBJR6171OZXBSJD7F929XA7HCJLX\"\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", "TOKEN = generateToken()\n", "FORMID = \"FORM-MFA66S91S4YBY5D36RN1JB2SD8V13CX4OFCJL3\" # 【客成专用】批量自动发券\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", " 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-398ea39a-7eec-a6ee-5ff6-eb5c8664de63\": # check\n", " print(\"开始处理！\")\n", " \"\"\" 读取相关数据 \"\"\"\n", " '''读取商品和券信息'''\n", " FORMID_TWO = \"FORM-6L966171O3RB70TJDB75R6RBI31V2NNACKCJLF1\" # 商品ID券ID关系表\n", " all_skuId = {}\n", " all_couponTemplateId = {}\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 in form_data_TWO.get('data'):\n", " all_skuId[data['formData']['textField_vmqmrm1'] + data['formData']['textField_cfm11ut']] = data['formData']['textField_nt0h72h']\n", " all_couponTemplateId[data['formData']['textField_vmqmrm1'] + data['formData']['textField_cfm11ut']] = data['formData']['textField_47og525']\n", " # print(len(all_skuId),len(all_couponTemplateId))\n", "\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", "\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_ONE = \"FORM-XHA66881AUMBJR6171OZXBSJD7F929XA7HCJLX\" # 【禁止提交】自动触发明细表\n", " all_CODE = {}\n", " form_data_ONE = read_instances(token=TOKEN, formUuid=FORMID_ONE, page=1, n=100)\n", " PAGES_ONE = form_data_ONE.get('totalCount')//100 + 1\n", " for F in range(1, PAGES_ONE+1):\n", " form_data_ONE = read_instances(token=TOKEN, formUuid=FORMID_ONE, page=F, n=100)\n", " for data in form_data_ONE.get('data'):\n", " if data['formData']['textField_ljchgz1h'] ==\"同意\":\n", " all_CODE[data['formData']['textField_ljchgz1f']] = data['formData']['textField_ljchgz1f']\n", "\n", "\n", " \"\"\" 开始分别读取数据并填充数据进行创建 \"\"\"\n", " succeed_all = 0\n", " fail_all = 0\n", " txt_code_all = \"\"\n", " formDataJson_check = form_data['data'][v]['data']\n", " list_new = 0\n", " for data in form_data['data'][v]['data']['tableField_ljcfpjg4']:\n", " result = data_NGV[data_NGV['org_code'] == data['textField_ljcfpjg6']]\n", " result = result.reset_index()\n", " ALL_formData = []\n", " data_new = {}\n", " try:\n", " data_new = {\n", " 'textField_ljchgz12':data[\"textField_ljcfpjg8\"],#\"cellPhone\"\n", " 'textField_ljchgz16':\"2268275546837446\",#\"dingDingId\"\n", " 'textField_ljchgz17':2,#\"stage\"\n", " 'textField_ljchgz18':1,#\"couponApplyType\"\n", " 'textField_ljchgz19':all_couponTemplateId[result['saas_edition_fmt'][0] + data['textField_ljcgefye']],#\"couponTemplateId\"\n", " 'textField_ljchgz1a':data[\"textField_ljcfpjg5\"],#\"couponAmount\"\n", " 'textField_ljchgz1b':1,#\"grantGroup\"\n", " 'textField_ljchgz1c':result['id_own_group'][0],#\"groupId\"\n", " 'textField_ljchgz1d':all_skuId[result['saas_edition_fmt'][0] + data['textField_ljcgefye']],#\"skuId\"\n", " 'textField_ljchgz1e':result['id_own_org'][0],#\"orgId\"\n", " 'textField_ljchgz13':0,#\"demotion\"\n", " 'textField_ljchgz14':0,#\"demotionSaaSEdition\"\n", " 'textField_ljchgz15':data[\"textField_ljcfpjg7\"],#\"公司名称\"\n", " 'textField_ljchgz1f':data[\"textField_ljcfpjg6\"],#\"门店编码\"\n", " # 'textField_ljchgz1i':data[\"数据集\"][i],#\"数据集\"\n", " 'textField_ljchgz1h':\"同意\",#\"check结果\"\n", " }\n", " data_new['textField_ljchgz1i'] = str({\n", " \"cellPhone\":data[\"textField_ljcfpjg8\"], # 收劵人手机号码\n", " \"dingDingId\":\"2268275546837446\", # 申请人钉钉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] + data['textField_ljcgefye']], # 劵模板ID\n", " \"couponAmount\":int(data[\"textField_ljcfpjg5\"]), # 劵金额，>0\n", " \"grantGroup\":1, # 1:发送到公司+人，0:发送到人\n", " \"groupId\":result['id_own_group'][0], # 发放公司ID，发在公司下=true是有值\n", " \"skuId\":all_skuId[result['saas_edition_fmt'][0] + data['textField_ljcgefye']], # 商品id \n", " \"orgId\":result['id_own_org'][0], # 门店ID，（申请续约劵并且降级时必填）\n", " \"demotion\":0, # 降级（是：1，否：0）\n", " \"demotionSaaSEdition\":0 # 降级版本,门店降级时必填\n", " }]\n", " })\n", " data_new['textField_ljchgz1i'] = data_new['textField_ljchgz1i'].replace(\"'\", '\"')\n", " data_new['textField_ljchgz1i'] = data_new['textField_ljchgz1i'].replace(\" \", \"\")\n", " \n", " if data[\"textField_ljcfpjg7\"] != result['group_name'][0]:\n", " data_new['textField_ljchgz1h'] = data_new['textField_ljchgz1h'] + \"公司名称与门店编码对应的group_name关系错误！\"\n", " if data[\"textField_ljcfpjg7\"] not in groupName_all:\n", " data_new['textField_ljchgz1h'] = data_new['textField_ljchgz1h'] + \"联系电话与公司名称对应的contact_mobile关系错误！\"\n", " except:\n", " data_new['textField_ljchgz1h'] = \"数据异常无法创建集合数据！\"\n", " try:\n", " cellPhone_group_name = queryCompanies(data[\"textField_ljcfpjg8\"])\n", " groupName_all = {}\n", " for data_two in cellPhone_group_name['data']['groupList']:\n", " groupName_all[data_two[\"groupName\"]]=data_two[\"groupId\"]\n", " except:\n", " data_new['textField_ljchgz1h'] = data_new['textField_ljchgz1h'] + \"F6中不存在该手机号！\"\n", " try:\n", " OrgId_list = queryDetailByOrgId(result['id_own_org'][0])\n", " lastSaasOrderPrice = OrgId_list['data']['lastSaasOrderPrice']/OrgId_list['data']['lastTransactionYear']\n", " except:\n", " data_new['textField_ljchgz1h'] = data_new['textField_ljchgz1h'] + \"门店编码查询不到上次购买价\"\n", " \n", " if data[\"textField_ljcfpjg6\"] in all_CODE:\n", " data_new['textField_ljchgz1h'] = data_new['textField_ljchgz1h'] + \"已经发过券了，请勿重复发券！\"\n", " if data_new['textField_ljchgz1h'] == \"同意\":\n", " succeed_all = succeed_all + 1\n", " else:\n", " fail_all = succeed_all + 1\n", " data_new['textField_ljchgz1h'] = data_new['textField_ljchgz1h'].replace(\"同意\", \"\")\n", " try:\n", " txt_code_all = txt_code_all + data[\"textField_ljcfpjg7\"]\n", " except:\n", " pass\n", " # check结果回传\n", " \n", " formDataJson_check['tableField_ljcfpjg4'][list_new]['textField_lje1r7at'] = data_new['textField_ljchgz1h']\n", " if data_new['textField_ljchgz1h'] == \"同意\":\n", " formDataJson_check['tableField_ljcfpjg4'][list_new]['radioField_lje1r7au'] = \"是\"\n", " else:\n", " formDataJson_check['tableField_ljcfpjg4'][list_new]['radioField_lje1r7au'] = \"否\"\n", " list_new = list_new + 1\n", " # ALL_formData.append(json.dumps(data_new, cls=NpEncoder)) \n", " # FORMID_NEW = \"FORM-XHA66881AUMBJR6171OZXBSJD7F929XA7HCJLX\" # 【禁止提交】自动触发明细表\n", " # res = Batch_creation(FORMID_NEW,TOKEN,ALL_formData)\n", " # print(res)\n", " # time.sleep(2)\n", " # txt_all = f\"发送成功：{succeed_all}条；发送失败：{fail_all}条；{txt_code_all}\"\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)\n", " elif taskId_new==\"sid-1b527e18-2053-657f-cdfd-5530aefcc5b7\": # api自动发券\n", " print(\"开始处理！\")\n", " \"\"\" 读取相关数据 \"\"\"\n", " '''读取商品和券信息'''\n", " FORMID_TWO = \"FORM-6L966171O3RB70TJDB75R6RBI31V2NNACKCJLF1\" # 商品ID券ID关系表\n", " all_skuId = {}\n", " all_couponTemplateId = {}\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 in form_data_TWO.get('data'):\n", " all_skuId[data['formData']['textField_vmqmrm1'] + data['formData']['textField_cfm11ut']] = data['formData']['textField_nt0h72h']\n", " all_couponTemplateId[data['formData']['textField_vmqmrm1'] + data['formData']['textField_cfm11ut']] = data['formData']['textField_47og525']\n", " # print(len(all_skuId),len(all_couponTemplateId))\n", "\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", "\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_ONE = \"FORM-XHA66881AUMBJR6171OZXBSJD7F929XA7HCJLX\" # 【禁止提交】自动触发明细表\n", " all_CODE = {}\n", " form_data_ONE = read_instances(token=TOKEN, formUuid=FORMID_ONE, page=1, n=100)\n", " PAGES_ONE = form_data_ONE.get('totalCount')//100 + 1\n", " for F in range(1, PAGES_ONE+1):\n", " form_data_ONE = read_instances(token=TOKEN, formUuid=FORMID_ONE, page=F, n=100)\n", " for data in form_data_ONE.get('data'):\n", " if data['formData']['textField_ljchgz1h'] ==\"同意\":\n", " all_CODE[data['formData']['textField_ljchgz1f']] = data['formData']['textField_ljchgz1f']\n", "\n", "\n", " \"\"\" 开始分别读取数据并填充数据进行创建 \"\"\"\n", " succeed_all = 0\n", " fail_all = 0\n", " txt_code_all = \"\"\n", " formDataJson_check = form_data['data'][v]['data']\n", " list_new = 0\n", " for data in form_data['data'][v]['data']['tableField_ljcfpjg4']:\n", " result = data_NGV[data_NGV['org_code'] == data['textField_ljcfpjg6']]\n", " result = result.reset_index()\n", " ALL_formData = []\n", " data_new = {}\n", " try:\n", " cellPhone_group_name = queryCompanies(data[\"textField_ljcfpjg8\"])\n", " groupName_all = {}\n", " for data_two in cellPhone_group_name['data']['groupList']:\n", " groupName_all[data_two[\"groupName\"]]=data_two[\"groupId\"]\n", " except:\n", " data_new['textField_ljchgz1h'] = \"F6中不存在该手机号！\"\n", " try:\n", " data_new = {\n", " 'textField_ljchgz12':data[\"textField_ljcfpjg8\"],#\"cellPhone\"\n", " 'textField_ljchgz16':\"2268275546837446\",#\"dingDingId\"\n", " 'textField_ljchgz17':2,#\"stage\"\n", " 'textField_ljchgz18':1,#\"couponApplyType\"\n", " 'textField_ljchgz19':all_couponTemplateId[result['saas_edition_fmt'][0] + data['textField_ljcgefye']],#\"couponTemplateId\"\n", " 'textField_ljchgz1a':data[\"textField_ljcfpjg5\"],#\"couponAmount\"\n", " 'textField_ljchgz1b':1,#\"grantGroup\"\n", " 'textField_ljchgz1c':result['id_own_group'][0],#\"groupId\"\n", " 'textField_ljchgz1d':all_skuId[result['saas_edition_fmt'][0] + data['textField_ljcgefye']],#\"skuId\"\n", " 'textField_ljchgz1e':result['id_own_org'][0],#\"orgId\"\n", " 'textField_ljchgz13':0,#\"demotion\"\n", " 'textField_ljchgz14':0,#\"demotionSaaSEdition\"\n", " 'textField_ljchgz15':data[\"textField_ljcfpjg7\"],#\"公司名称\"\n", " 'textField_ljchgz1f':data[\"textField_ljcfpjg6\"],#\"门店编码\"\n", " # 'textField_ljchgz1i':data[\"数据集\"][i],#\"数据集\"\n", " 'textField_ljchgz1h':\"同意\",#\"check结果\"\n", " 'textField_ljf6nf7s':data['radioField_lje1r7au'], #是否发券\n", " }\n", " data_new['textField_ljchgz1i'] = str({\n", " \"cellPhone\":data[\"textField_ljcfpjg8\"], # 收劵人手机号码\n", " \"dingDingId\":\"2268275546837446\", # 申请人钉钉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] + data['textField_ljcgefye']], # 劵模板ID\n", " \"couponAmount\":int(data[\"textField_ljcfpjg5\"]), # 劵金额，>0\n", " \"grantGroup\":1, # 1:发送到公司+人，0:发送到人\n", " \"groupId\":result['id_own_group'][0], # 发放公司ID，发在公司下=true是有值\n", " \"skuId\":all_skuId[result['saas_edition_fmt'][0] + data['textField_ljcgefye']], # 商品id \n", " \"orgId\":result['id_own_org'][0], # 门店ID，（申请续约劵并且降级时必填）\n", " \"demotion\":0, # 降级（是：1，否：0）\n", " \"demotionSaaSEdition\":0 # 降级版本,门店降级时必填\n", " }]\n", " })\n", " data_new['textField_ljchgz1i'] = data_new['textField_ljchgz1i'].replace(\"'\", '\"')\n", " data_new['textField_ljchgz1i'] = data_new['textField_ljchgz1i'].replace(\" \", \"\")\n", " if data[\"textField_ljcfpjg7\"] != result['group_name'][0]:\n", " data_new['textField_ljchgz1h'] = \"公司名称与门店编码对应的group_name关系错误！\"\n", " if data[\"textField_ljcfpjg7\"] not in groupName_all:\n", " data_new['textField_ljchgz1h'] = \"联系电话与公司名称对应的contact_mobile关系错误！\"\n", " except:\n", " data_new['textField_ljchgz1h'] = \"数据异常无法创建集合数据！\"\n", " if data[\"textField_ljcfpjg6\"] in all_CODE:\n", " data_new['textField_ljchgz1h'] = data_new['textField_ljchgz1h'] + \"已经发过券了，请勿重复发券！\"\n", " if data_new['textField_ljchgz1h'] == \"同意\" and data_new['radioField_lje1r7au'] == \"是\":\n", " succeed_all = succeed_all + 1\n", " else:\n", " fail_all = succeed_all + 1\n", " try:\n", " txt_code_all = txt_code_all + data[\"textField_ljcfpjg7\"]\n", " except:\n", " pass\n", " # check结果回传\n", " \n", " formDataJson_check['tableField_ljcfpjg4'][list_new]['textField_lje1r7at'] = data_new['textField_ljchgz1h']\n", " if data_new['textField_ljchgz1h'] == \"同意\":\n", " formDataJson_check['tableField_ljcfpjg4'][list_new]['radioField_lje1r7au'] = \"是\"\n", " else:\n", " formDataJson_check['tableField_ljcfpjg4'][list_new]['radioField_lje1r7au'] = \"否\"\n", " list_new = list_new + 1\n", " ALL_formData.append(json.dumps(data_new, cls=NpEncoder)) \n", " FORMID_NEW = \"FORM-XHA66881AUMBJR6171OZXBSJD7F929XA7HCJLX\" # 【禁止提交】自动触发明细表\n", " res = Batch_creation(FORMID_NEW,TOKEN,ALL_formData)\n", " print(res)\n", " time.sleep(2)\n", " txt_all = f\"发送成功：{succeed_all}条；发送失败：{fail_all}条；{txt_code_all}\"\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" }, "orig_nbformat": 4 }, "nbformat": 4, "nbformat_minor": 2 }