python插件开发-查询未下推应付单的采购入库单,调用API进行下推、提交、审核操作原创
6人赞赏了该文章
50次浏览
编辑于2024年09月11日 21:17:04
公司业务部门提出需求,希望采购入库单审核后,能由系统定时下推,而不是使用BOS中的单据转换自动下推, 反馈自动下推会花费较长的等待时间,影响工作效率。 于是在社区中查询资料,利用API 编写PYTHON脚本处理,程序封装后,放在服务器上执行定时任务。 下面是详细代码,供大家参考。 import json,requests import pyodbc #金蝶云星空WEB API调用接口初始化 依次:登录、下推、提交、审核 login_url="http://127.0.0.1/k3cloud/Kingdee.BOS.WebApi.ServicesStub.AuthService.ValidateUser.common.kdsvc" push_url="http://127.0.0.1/K3Cloud/Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.Push.common.kdsvc" submit_url = "http://127.0.0.1/K3Cloud/Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.Submit.common.kdsvc" audit_url="http://127.0.0.1/K3Cloud/Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.Audit.common.kdsvc" login_data={ "acctid":"6455efec1bec7f","username":"administrator","password":"888888","lcid":1352} #6455efec1bec7f为正式库的ID def login(): # 定义登录函数 login_response=requests.post(url=login_url,data=login_data) return login_response.cookies # 返回cookies,方便下次访问时携带 # 设置 SQL Server 数据库连接信息 server = '127.0.0.1' database = 'AIS20210519140740' username = 'sa' password = '123#admin' # 建立数据库连接 conn = pyodbc.connect(f'DRIVER=SQL SERVER;SERVER={server};DATABASE={database};UID={username};PWD={password}') # 创建游标 cursor = conn.cursor() # 执行 SQL 查询采购入库未下推应付单的数据,应付单的单据类型暂估入库 query = f'''SELECT t0.FBILLNO fbillno FROM t_STK_InStock t0 LEFT OUTER JOIN t_STK_InStockFin t1 ON t0.FID = t1.FID LEFT OUTER JOIN T_STK_INSTOCKENTRY t2 ON t0.FID = t2.FID LEFT OUTER JOIN T_STK_INSTOCKENTRY_F t2_F ON t2.FENTRYID = t2_F.FENTRYID WHERE (t2.F_SSX_TEXT2 = '' OR t2.F_SSX_TEXT2 = ' ' OR t2.F_SSX_TEXT2 IS NULL) AND t0.FBILLTYPEID <> '0023240234df807511e3089bc912a28a' AND t1.FISGENFORIOS <> '1' AND EXISTS (SELECT FID FROM (SELECT t0.FID FROM t_STK_InStock t0 WHERE t0.FSTOCKORGID = 1) txs WHERE txs.FID = t0.FID) AND t0.FSTOCKORGID IN (1, 0) AND FBASEAPJOINQTY = 0 AND FCANCELSTATUS = 'A' AND t0.FDOCUMENTSTATUS = 'C' AND t0.FOBJECTTYPEID = 'STK_InStock' ''' # 执行查询语句 cursor.execute(query) # 将查询结果存入数组 result_array = [] for row in cursor.fetchall(): result_array.append(row[0]) # 使用索引访问,如果第一列是您想要的列 # 打印存入数组中的数据 print(result_array) if result_array: # 完成采购入库单下推应付单************************ post_data={"FormId":"STK_InStock","Data":json.dumps({"RuleId":"AP_InStockToPayableMap" ,"Numbers": result_array})} response=requests.post(url=push_url,data=post_data,cookies=login()) print(response.text) ##********************************************** # 执行 SQL 查询采购入库未下推应付单的数据 query2 = f'''SELECT t0.FBILLNO fbillno FROM T_AP_PAYABLE t0 WHERE t0.FSETACCOUNTTYPE = '2' AND t0.FISINIT = '0' AND t0.FBYVERIFY <> '2' AND t0.FDOCUMENTSTATUS = 'A' AND EXISTS (SELECT FID FROM (SELECT t0.FID FROM T_AP_PAYABLE t0 WHERE t0.FSETTLEORGID = 1) txs WHERE txs.FID = t0.FID) AND t0.FSETTLEORGID IN (1, 0) ''' # 执行查询语句 cursor.execute(query2) # 将查询结果存入数组 result_array2 = [] for row in cursor.fetchall(): result_array2.append(row[0]) # 使用索引访问,如果第一列是您想要的列 # 打印存入数组中的数据 print(result_array2) # 关闭游标和连接 cursor.close() conn.close() if result_array2: # 完成创建状态的暂估应付单提交、审核操作************************ post_data={"FormId":"AP_Payable","Data":json.dumps({"Numbers": result_array2})} response=requests.post(url=submit_url,data=post_data,cookies=login()) print(response.text) post_data={"FormId":"AP_Payable","Data":json.dumps({"Numbers": result_array2})} response=requests.post(url=audit_url,data=post_data,cookies=login()) print(response.text) ##**********************************************
推荐阅读