本地SQL Server环境基于易正BPM系统与销帮帮CRM互通api实战（第一阶段，写入同步）

需要在bpm属性；完善参考库,引用库

[code]替换参考库：
System.dll
System.Transactions.dll
BPM.dll
BPM.Server.dll
System.Core.dll
System.Dynamic.dll
System.Data.dll
System.Web.dll
替换引用库：
using System;
using System.IO;
using System.Text;
using System.Transactions;
using BPM;
using BPM.Server;
using BPM.Server.OAL;
using System.Net;
using System.Dynamic;
using System.Data;
using System.Data.SqlClient;
using System.Threading;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.Configuration;[/code]

开发过程：在C#构建一个HTTP请求。

[code]
//获取 FormDataSet 数据集中的 e_ty_dj 表的 taskid 字段的值
var table=FormDataSet.Tables["e_ty_dj"].Rows[0];
var taskid=Convert.ToString(table["taskid"]);

//拼接请求URL,带上获取的taskid参数
var apiUrl = "http://{你的ip}/xbongadd/run_python_script.php?taskid="+taskid;

//创建HttpWebRequest请求
var req = (HttpWebRequest)WebRequest.Create(apiUrl);
req.Method = "GET"; //指定请求方法为GET

//发送请求并获取响应
var response = (HttpWebResponse)req.GetResponse();

//读取响应并输出
var stream = response.GetResponseStream();
var reader = new StreamReader(stream, Encoding.UTF8);
var result = reader.ReadToEnd();
Console.WriteLine(result);
[/code]

撰写一个接收PHP的代码

[php]
<?php
// run_python_from_php.php
header('Content-Type: text/html; charset=utf-8');

// 数据库配置
require 'inc/config.php';

$python_script = "py_xbongadd.py";
$python_script2 = "py_xbongadd2.py";
$python_executable = "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\python.exe";
$taskid = $_GET['taskid'] ?? null;

if ($taskid) {
if (!preg_match('/^[0-9]{5,6}$/', $taskid)) {
echo "任务ID无效！";
exit;
}

// 使用数据库连接参数创建新的 sqlsrv 对象
$conn = sqlsrv_connect($serverName, $connectionOptions);

if ($conn === false) {
echo "错误：无法连接到数据库。";
exit;
}

$query_je = "SELECT je from db_bsp.dbo.e_ty_dj WHERE taskid=?";
$params = array($taskid);
$stmt_je = sqlsrv_query($conn, $query_je, $params);

if ($stmt_je === false) {
echo "错误：无法执行查询。";
exit;
}

$row_je = sqlsrv_fetch_array($stmt_je, SQLSRV_FETCH_ASSOC);
if ($row_je === null) {
echo "错误：任务ID不存在，请重新关联！";
exit;
}

$je = $row_je['je'];

if ($je == 0) {
echo "错误：je 等于 0，退出脚本。";
exit;
} else {
$query = "SELECT text_2 from db_bpm.dbo.xbongbong_khxx_ownerid WHERE wldwbm = (SELECT gh from db_bsp.dbo.e_ty_dj WHERE taskid=?)";
$params2 = array($taskid);
$stmt = sqlsrv_query($conn, $query, $params2);

if ($stmt === false) {
echo "错误：无法执行查询。";
exit;
}

$row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);
if ($row === null) { // 查询没有返回任何结果
$python_script = $python_script2;
} else {
$text_2 = $row['text_2'];

if ($text_2 != null && $text_2 != 'N/A') {
$python_script = $python_script;
} else {
$python_script = $python_script2;
}
}

// 执行 Python 脚本并将结果保存到 $output 数组
// 添加 2>&1 以捕获错误输出
exec("$python_executable $python_script " . escapeshellarg($taskid) . " 2>&1", $output);

// 输出 Python 脚本的结果或错误信息
if (!empty($output)) {
foreach ($output as $line) {
echo $line; // ".<br>"
}
} else {
echo "Python 脚本没有输出。";
}
}

sqlsrv_free_stmt($stmt_je);;
sqlsrv_close($conn);
} else {
echo "错误：未提供任务ID参数。";
}
?>
[/php]

然后调用撰写一个python执行脚本。（菜鸟级代码）
在过程中增加报错，将报错结果使用{微信机器人}推送到内部运维群。

[python]
# -*- coding: UTF-8 -*-
#写入合同接口
import requests
import json,hashlib
import time
import pandas as pd
import pyodbc
import sys

# 接收传递的 taskid 参数
if len(sys.argv) &amp;amp;gt; 1:
taskid = sys.argv[1]
else:
print("Error: taskid 参数缺失")
sys.exit(1)

class XbbRequest:
# 共享变量
request_session = requests.session()

def __init__(self, url:str, params:json, token:str):
self.header = {
'Content-Type': 'application/json;charset=UTF-8',
}
self.url = url
self.params = params
self.strparams = json.dumps(params, separators=(',', ':'))
self.token = token

def post(self):
# 生成加密sign
sign = self.__gen_sign_code(self.params, self.token)
# sign值需要放在header中
self.header["sign"] = sign
# 发起post请求
res = self.request_session.post(headers=self.header, data=self.strparams, url=self.url)
return res

def __gen_sign_code(self, request_parameters, xbb_access_token):
"""
生成请求发送时的sign_code值
"""
# json转化成字符串时加上ensure_ascii=False，不然会在有中文字符时导致sign检验不通过
parameters = (json.dumps(request_parameters, separators=(',', ':'), ensure_ascii=False) + xbb_access_token).encode("utf-8")
return hashlib.sha256(parameters).hexdigest()

if __name__ == "__main__":
host = 'http://appapi.xbongbong.com'
token = 'test_token_ding4a3aebc0873df49335c2f4657eb6378f'
url = '/pro/v2/api/contract/add'
#时间
timestamp = time.time()
date_timestamp = int(timestamp / (24 * 3600)) * 24 * 3600
#连接数据库获得数据信息
conn = pyodbc.connect('Driver={SQL Server};'
'Server=192.168.*.*;'
'Database=db_bsp;'
'UID=bpmsa;'
'PWD=密码12345;')
cursor = conn.cursor()

# 在执行其他查询之前，首先检查xdateid是否大于0
cursor.execute("SELECT isnull(xdateid,0) as xdateid from db_bpm.dbo.xbongbong_ddxx_list WHERE taskid=?", (taskid,))
xdateid_check = cursor.fetchone()

# 检查xdateid_check[0]是否大于0
if xdateid_check[0] &amp;amp;gt; 0:
# xdateid_check大于0，因此跳出
print(f"There is id data, jump out;xdateid:[{xdateid_check[0]}],taskid:[{taskid}]")
cursor.close()
conn.close() # 关闭数据库连接
sys.exit() # 退出脚本
else:
# xdateid_check等于0，执行下一步
print("Next;")
#下一步
cursor.execute("SELECT A.serialNo,A.num_1,B.text_2,B.ownerId,B.text_3,DATEDIFF(second, '19700101',DATEADD(day, CAST(CASE WHEN A.do14 = '/' then 7 else A.do14 end AS int), GETDATE())) AS date_20,C.img from(SELECT gh,djh as serialNo,CAST(je AS FLOAT) as num_1,do14,taskid from e_ty_dj WHERE taskid =?)AS A LEFT JOIN db_bpm.dbo.xbongbong_khxx_ownerid as B ON A.gh=B.wldwbm LEFT JOIN db_bpm.dbo.xbongbong_ddxx_list as C ON C.taskid=A.taskid", (taskid,))
results = cursor.fetchall()

serial_nos = [row[0] for row in results]
anum_1 = [row[1] for row in results]
btext_2 = [row[2] for row in results]
cownerId = [row[3] for row in results]
dtext_3 = [row[4] for row in results]
date_20 = [row[5] for row in results]
img_1=[row[6] for row in results]

#连接数据库结束
request_json = {
"formId": 业务id,
"corpid": "公司ID",
"dataList": {
"date_1":date_timestamp, #unix时间
"file_1": [img_1[0]],
"text_3": dtext_3[0], # 客户联系人
"text_2": btext_2[0], #dateid
"ownerId":[cownerId[0]],
"num_1": float(anum_1[0]),
"text_6": "完毕",
"serialNo": serial_nos[0],
"text_8": cownerId[0]
},
"userId":cownerId[0]
}
xbbrequest = XbbRequest(host + url, request_json, token)
res = xbbrequest.post()

# 解析响应的 JSON 内容，并提取 dataId
response_json = res.json()
xdataId = response_json.get('result', {}).get('dataId')

if xdataId is not None:
#print(f'Extracted dataId: {xdataId}')

# 使用提取出的 dataId 更新数据库
cursor.execute("UPDATE db_bpm.dbo.xbongbong_ddxx_list SET xdateid = ? WHERE taskid = ? ", (xdataId,taskid,))

# 提交更改
conn.commit()
else:
webhook_url = 'https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=企业微信keyID'
headers = {'Content-Type': 'application/json'}
data = {
"msgtype": "text",
"text": {
"content": f"无法写入合同订单信息，对应的任务id：{taskid}"
}
}
response = requests.post(webhook_url, headers=headers, data=json.dumps(data))
#插入结束

# 第二个请求开始
url2 = '/pro/v2/api/payment/add'
request_json2 = {
"formId": 业务id,
"corpid": "公司ID",
"dataList": {
"date_20": date_timestamp,
"ownerId":[cownerId[0]],
"serialNo": serial_nos[0],
"date_1": date_20[0],
"text_3": "未收款",
"text_5": int(xdataId),
"text_4": btext_2[0],
"num_1": float(anum_1[0]),
"text_7": "来自api写入",
"text_6": "常规"
},
"userId": cownerId[0]
}
xbbrequest2 = XbbRequest(host + url2, request_json2, token)
res2 = xbbrequest2.post()
#打印结果
#print(res2.status_code)
#print(res2.text)
# 解析响应的 JSON 内容，并提取 dataId
response_json2 = res2.json()
ydataId = response_json2.get('result', {}).get('dataId')

if ydataId is not None:
#print(f'Extracted dataId: {ydataId}')

# 使用提取出的 dataId 更新数据库
cursor.execute("UPDATE db_bpm.dbo.xbongbong_ddxx_list SET ydateid = ? ,je = ? WHERE taskid = ? ", (ydataId,anum_1[0],taskid,))

# 提交更改
conn.commit()
else:
webhook_url = 'https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=企业微信keyID'
headers = {'Content-Type': 'application/json'}
data = {
"msgtype": "text",
"text": {
"content": f"无法写入应收款信息，对应的任务id：{taskid}"
}
}
response = requests.post(webhook_url, headers=headers, data=json.dumps(data))
#插入结束

cursor.close()
conn.close() #关闭数据库连接

print("yes")

[/python]