金蝶K3cloud 库存账龄表 WebAPI接口取数存入Excel

#!/usr/bin/env python
# coding:utf-8
import requests
import json
import datetime
import pymssql
from openpyxl import Workbook

# 登录
accTid = "62c4e6a3dc66d6"
username = "username"
password = "password"
lcid = 2052


# 存储数据
wb = Workbook()
sheet = wb.active
sheet.title = '库存账龄表'

login_url = 'http://localhost/k3cloud/Kingdee.BOS.WebApi.ServicesStub.AuthService.ValidateUser.common.kdsvc'
login_data = {"accTid": accTid, "username": username, "password": password, "lcid": lcid}
headers = {
    'Content-Type': 'application/json',
    'user-agent': 'Mozilla/5.0(Windows NT 6.1;WOW64) AppleWebKit/537.36(KHTML, like Gecko) Chrome/74.0.3729.169 '
                  'Safari / 537.36 '
}
url = "http://localhost/k3cloud/Kingdee.K3.SCM.WebApi.ServicesStub.StockReportQueryService.\
GetReportData,Kingdee.K3.SCM.WebApi.ServicesStub.common.kdsvc"
payload = "{\r\n  \"parameters\": [\r\n      \"{\r\n            \\\"FORMID\\\":\\\"STK_InvAgeAnalyzeRpt\\\"," \
          "\r\n            \\\"FSCHEMEID\\\":\\\"643c9b8f217b5c\\\",\r\n            \\\"QuicklyCondition\\\":[\r\n    " \
          "                {\\\"FieldName\\\":\\\"MaterialFrom\\\",\\\"FieldValue\\\":\\\"7382903800\\\"},\r\n        " \
          "            {\\\"FieldName\\\":\\\"MaterialTo\\\",\\\"FieldValue\\\":\\\"7382903800\\\"},\r\n              " \
          "      {\\\"FieldName\\\":\\\"QueryGroup\\\",\\\"FieldValue\\\":\\\"Now\\\"},\r\n                    {" \
          "\\\"FieldName\\\":\\\"QueryDate\\\",\\\"FieldValue\\\":\\\"\\\"},\r\n                    {" \
          "\\\"FieldName\\\":\\\"InvAgeEntity\\\"}\r\n\t\t        ],\r\n              \\\"FieldKeys\\\": " \
          "\\\"\\\"\r\n        }\"\r\n    ]\r\n}"
'''6422509259125f'''

session = requests.session()
response = session.get(login_url, data=login_data)
cookie_str = response.headers.get("Set-Cookie")
cookie_tuple = cookie_str.split(";")
kds = cookie_tuple[0].split("=")[1]
asps = cookie_tuple[1].split("=")[2]

headers = {
    'Content-Type': 'application/json',
    'Cookie': 'ASP.NET_SessionId={0}; kdservice-sessionid={1}'.format(asps, kds)
}

response = requests.request("POST", url, headers=headers, data=payload)
jResult = json.loads(response.text.encode('utf-8'))
items = jResult['data']

# Excel标题
data = ['组织ID', '组织编码', '组织名称', '物料编码', '物料名称', '规格', '分组名称', '仓库编码', '仓库名称',\
        '仓位', '库存状态', '库存单位', '数量', '单价', '金额', '0~30天数量', '0~30天金额', '30~90天数量', '30~90天金额',\
        '90~180天数量', '90~180天金额', '180天以上数量', '180天以上金额']
sheet.append(data)

for item in items:
    ForgID = item['FSTOCKORGID']  # 组织ID
    ForgNum = item['FStockOrgNumber']  # 组织编码
    ForgName = item['FStockOrgName']  # 组织名称
    FmateNum = item['FMATERIALNUMBER']  # 物料编码
    FmateName = item['FMATERIALNAME']  # 物料名称
    FmateSpec = item['FMATERIALMODEL']  # 规格
    FmateGroupName = item['FMATERIALGROUPNAME']  # 分组名称
    FstockNum = item['FStockNumber']  # 仓库编码
    FstockName = item['FStockName']  # 仓库名称
    FstockLoc = item['FSTOCKLOC']  # 仓位
    FstockStatus = item['FSTOCKSTATUSNAME']  # 库存状态
    FunitName = item['FBaseUnitName']  # 库存单位
    FbaseQty = item['fbaseqty']  # 数量
    FbasePrice = item['fbaseprice']  # 单价
    Famount = item['famount']  # 金额
    FbaseQty_0 = item['fbaseqty_0']  # 0~30天数量
    Famount_0 = item['famount_0']  # 0~30天金额
    FbaseQty_30 = item['fbaseqty_30']  # 30~90天数量
    Famount_30 = item['famount_30']  # 30~90天金额
    FbaseQty_90 = item['fbaseqty_90']  # 90~180天数量
    Famount_90 = item['famount_90']  # 90~180天金额
    Fbaseqty_180 = item['fbaseqty_180']  # 180天以上数量
    Famount_180 = item['famount_180']  # 180天以上金额

    # print(ForgID, ForgNum, ForgName, FmateNum, FmateName, FmateSpec, FmateGroupName, FstockNum, FstockName,\
    #       FstockLoc.split(':')[-1].split(';')[0], FstockStatus, FunitName, FbaseQty, FbasePrice, Famount, FbaseQty_0, \
    #       Famount_0, FbaseQty_30, Famount_30, FbaseQty_90, Famount_90, Fbaseqty_180, Famount_180)

    data1 = [ForgID, ForgNum, ForgName, FmateNum, FmateName, FmateSpec, FmateGroupName, FstockNum,\
             FstockName, FstockLoc.split(':')[-1].split(';')[0], FstockStatus, FunitName, FbaseQty, FbasePrice, \
             Famount, FbaseQty_0, Famount_0, FbaseQty_30, Famount_30, FbaseQty_90, Famount_90, \
             Fbaseqty_180, Famount_180]
    sheet.append(data1)
wb.save('库存账龄表_'+str(datetime.datetime.now().date())+'.xlsx')