调用方法
地址说明
通过获取程序Api地址接口返回参数中ApiServerAddress的值
调用方法
POSTContentType: application/x-www-form-urlencoded;Charset=UTF-8
参数：
| 参数名 | 必选 | 类型 | 说明 |
|---|---|---|---|
| managename | 是 | string | 接口名 固定值为：GraspCMServerApi.dll |
| dbname | 是 | string | 数据库名称 |
| paramkey | 是 | string | 接口传入参数ParamsKey，详见具体的查询Json格式 |
| paramjson | 是 | string | 查询条件参数json格式，详见具体的查询Json格式 |
| apiparam | 是 | string | 通过获取程序API地址接口返回 |
| apitype | 是 | string | 接口类型 传query |
| sign | 是 | string | 签名字符串 |
| mobile | 是 | string | 云平台手机号 获取API地址返回的GraspCloudMobile，为空时传0 |
| serviceid | 是 | string | 云平台ServerID 获取API地址返回的GraspCloudServerId，为空时传0 |
| interiorapi | 是 | int | 该参数始终为1 |
参数传入说明
1、采用x-www-form-urlencode的方式传参，即参数通过key=value&key=value拼接成字符串，再通过Body传入。
2.再拼接字符串时需要对参数中的所有value做URLEncode，特别注意只有再拼接Post传入参数时才对Value做URLEncode，而计算签名时不要进行转译
sign签名规则
- 将上述参数中除了sign以外的所有参数，根据参数名按照字母排序，以Key+Value的形式拼接字符串。
- 字符串拼接后在结尾加上SigKey。(SignKey获取方法见：获取接口所需SignStr接口)
- 将这个最终拼接成的字符串通过MD5加密，取32位小写。
MD5加密前字符串示例
apiparam7/eAVT1NN2MtBY+MkAylEBVN/FsnC9BL4IzSHtHyt+2s+jAZRVqfMAwzFgSudBH3eDau916Vd6aNEFxve2ZoYey1Zvi+Vp8xb7VrAnJNbCs=apitypequerydbnameM7215API测试interiorapi1managenameGraspCMServerApi.dllmobile0paramjson{"pageindex":1,"pagesize":11}paramkeycustomerserviceid02020cmgmsignkeyappkey@#986
PostMan传参示例
返回参数说明
| 参数名 | 类型 | 说明 |
|---|---|---|
| code | string | 小于0表示失败 |
| message | string | 单据过账消息 成功还是失败都会返回消息 |
| pagecount | int | 返回的当前数据条数 |
| pagetotal | int | 所有页加起来的总数据条数 |
| response | json数组 | 成功则返回查询数据，失败时会返回具体的错误信息 ，具体字段内容详见数据结构 |
返回参数示例
{
"code": "0",
"message": "",
"pagetotal": 2,
"pagecount": 2,
"response": [
{
"rownum": 1,
"vchcode": 14,
"vchtype": 11,
"date": "2020-03-19",
"number": "XS-2020-03-19-00001",
"btypeid": "00002",
"busercode": "001",
"bfullname": "客户1",
"settlebtypeid": "00002",
"settlebusercode": "001",
"settlebfullname": "客户1",
"ktypeid": "00001",
"kusercode": "001",
"kfullname": "仓库1",
"ktypeid2": "",
"k2usercode": "",
"k2fullname": "",
"etypeid": "00001",
"eusercode": "00001",
"efullname": "会计主管",
"etypeid2": "",
"e2usercode": "",
"e2fullname": "",
"dtypeid": "00001",
"dusercode": "001",
"dfullname": "部门1",
"mtypeid": "",
"musercode": "",
"mfullname": "",
"total": 89.32,
"billtotal": 89.32,
"summary": "销售【存货1】等给【客户1】:会计主管",
"gatheringdate": "2020-03-26",
"userdefined01": "",
"userdefined02": "",
"userdefined03": "",
"userdefined04": "",
"userdefined05": "",
"userdefined06": "",
"userdefined07": "",
"userdefined08": "",
"userdefined09": "",
"userdefined10": "",
"userdefined11": "",
"userdefined12": "",
"userdefined13": "",
"userdefined14": 0,
"userdefined15": 0,
"userdefined16": 0,
"inputno": "会计主管",
"savetime": "2020-03-19 12:37:32",
"difatype": 0,
"difatypename": "",
"atypeid": "",
"mltotal": 0,
"source1": "14",
"source2": "14",
"detail": [
{
"vchcode": 14,
"vchtype": 11,
"dlyorder": 1,
"ptypeid": "00001",
"pusercode": "001",
"pfullname": "存货1",
"pstandard": "",
"ptype": "",
"unit": 1,
"unitname": "",
"barcode": "",
"ktypeid": "00001",
"kusercode": "001",
"kfullname": "仓库1",
"custom1": "",
"c1usercode": "",
"c1fullname": "",
"custom2": "",
"c2usercode": "",
"c2fullname": "",
"custom3": "",
"custom4": "",
"blockno": "",
"prodate": "",
"comment": "",
"qty": -1,
"price": 33,
"total": -33,
"discount": 1,
"discountprice": 33,
"discounttotal": -33,
"tax": 16,
"taxprice": 38.28,
"taxtotal": -5.28,
"tax_total": -38.28,
"qtyother": 0,
"freedom01": "",
"freedom02": "",
"freedom03": "",
"freedom04": "",
"freedom05": "",
"freedom06": "",
"freedom07": "",
"freedom08": "",
"freedom09": "",
"freedom10": "",
"freedom11": "",
"freedom12": "",
"freedom13": "",
"freedom14": 0,
"freedom15": 0,
"freedom16": 0,
"freedomdatedif": 0,
"detailsign": 0,
"source1": "14",
"source2": "14_1",
"serialno": []
},
{
"vchcode": 14,
"vchtype": 11,
"dlyorder": 2,
"ptypeid": "00002",
"pusercode": "002",
"pfullname": "存货2",
"pstandard": "",
"ptype": "",
"unit": 1,
"unitname": "",
"barcode": "",
"ktypeid": "00001",
"kusercode": "001",
"kfullname": "仓库1",
"custom1": "",
"c1usercode": "",
"c1fullname": "",
"custom2": "",
"c2usercode": "",
"c2fullname": "",
"custom3": "",
"custom4": "",
"blockno": "",
"prodate": "",
"comment": "",
"qty": -2,
"price": 22,
"total": -44,
"discount": 1,
"discountprice": 22,
"discounttotal": -44,
"tax": 16,
"taxprice": 25.52,
"taxtotal": -7.04,
"tax_total": -51.04,
"qtyother": 0,
"freedom01": "",
"freedom02": "",
"freedom03": "",
"freedom04": "",
"freedom05": "",
"freedom06": "",
"freedom07": "",
"freedom08": "",
"freedom09": "",
"freedom10": "",
"freedom11": "",
"freedom12": "",
"freedom13": "",
"freedom14": 0,
"freedom15": 0,
"freedom16": 0,
"freedomdatedif": 0,
"detailsign": 0,
"source1": "14",
"source2": "14_2",
"serialno": []
}
],
"settle": [
{
"vchcode": 14,
"vchtype": 11,
"dlyorder": 4,
"atypeid": "0000100001",
"ausercode": "1001",
"afullname": "库存现金",
"total": 1,
"freedom01": "",
"freedom02": "",
"freedom03": "",
"freedom04": "",
"freedom05": "",
"freedom06": "",
"freedom07": "",
"freedom08": "",
"freedom09": "",
"freedom10": "",
"freedom11": "",
"freedom12": "",
"freedom13": "",
"freedom14": 0,
"freedom15": 0,
"freedom16": 0,
"freedomdatedif": 0,
"source1": "14",
"source2": ""
},
{
"vchcode": 14,
"vchtype": 11,
"dlyorder": 5,
"atypeid": "0000100002",
"ausercode": "1002",
"afullname": "银行存款",
"total": 2,
"freedom01": "",
"freedom02": "",
"freedom03": "",
"freedom04": "",
"freedom05": "",
"freedom06": "",
"freedom07": "",
"freedom08": "",
"freedom09": "",
"freedom10": "",
"freedom11": "",
"freedom12": "",
"freedom13": "",
"freedom14": 0,
"freedom15": 0,
"freedom16": 0,
"freedomdatedif": 0,
"source1": "14",
"source2": ""
}
]
},
{
"rownum": 2,
"vchcode": 15,
"vchtype": 11,
"date": "2020-03-19",
"number": "XS-2020-03-19-00002",
"btypeid": "00002",
"busercode": "001",
"bfullname": "客户1",
"settlebtypeid": "00002",
"settlebusercode": "001",
"settlebfullname": "客户1",
"ktypeid": "00001",
"kusercode": "001",
"kfullname": "仓库1",
"ktypeid2": "",
"k2usercode": "",
"k2fullname": "",
"etypeid": "",
"eusercode": "",
"efullname": "",
"etypeid2": "",
"e2usercode": "",
"e2fullname": "",
"dtypeid": "",
"dusercode": "",
"dfullname": "",
"mtypeid": "",
"musercode": "",
"mfullname": "",
"total": 124.04,
"billtotal": 124.04,
"summary": "销售【存货2】等给【客户1】:",
"gatheringdate": "",
"userdefined01": "",
"userdefined02": "",
"userdefined03": "",
"userdefined04": "",
"userdefined05": "",
"userdefined06": "",
"userdefined07": "",
"userdefined08": "",
"userdefined09": "",
"userdefined10": "",
"userdefined11": "",
"userdefined12": "",
"userdefined13": "",
"userdefined14": 0,
"userdefined15": 0,
"userdefined16": 0,
"inputno": "会计主管",
"savetime": "2020-03-19 12:40:02",
"difatype": 25,
"difatypename": "销售抹零",
"atypeid": "0000700013",
"mltotal": 2,
"source1": "15",
"source2": "15",
"detail": [
{
"vchcode": 15,
"vchtype": 11,
"dlyorder": 3,
"ptypeid": "00002",
"pusercode": "002",
"pfullname": "存货2",
"pstandard": "",
"ptype": "",
"unit": 1,
"unitname": "",
"barcode": "",
"ktypeid": "00001",
"kusercode": "001",
"kfullname": "仓库1",
"custom1": "",
"c1usercode": "",
"c1fullname": "",
"custom2": "",
"c2usercode": "",
"c2fullname": "",
"custom3": "",
"custom4": "",
"blockno": "",
"prodate": "",
"comment": "",
"qty": -1,
"price": 22,
"total": -22,
"discount": 1,
"discountprice": 22,
"discounttotal": -22,
"tax": 16,
"taxprice": 25.52,
"taxtotal": -3.52,
"tax_total": -25.52,
"qtyother": 0,
"freedom01": "",
"freedom02": "",
"freedom03": "",
"freedom04": "",
"freedom05": "",
"freedom06": "",
"freedom07": "",
"freedom08": "",
"freedom09": "",
"freedom10": "",
"freedom11": "",
"freedom12": "",
"freedom13": "",
"freedom14": 0,
"freedom15": 0,
"freedom16": 0,
"freedomdatedif": 0,
"detailsign": 0,
"source1": "15",
"source2": "15_3",
"serialno": []
},
{
"vchcode": 15,
"vchtype": 11,
"dlyorder": 4,
"ptypeid": "00001",
"pusercode": "001",
"pfullname": "存货1",
"pstandard": "",
"ptype": "",
"unit": 1,
"unitname": "",
"barcode": "",
"ktypeid": "00001",
"kusercode": "001",
"kfullname": "仓库1",
"custom1": "",
"c1usercode": "",
"c1fullname": "",
"custom2": "",
"c2usercode": "",
"c2fullname": "",
"custom3": "",
"custom4": "",
"blockno": "",
"prodate": "",
"comment": "",
"qty": -4,
"price": 11,
"total": -44,
"discount": 1,
"discountprice": 11,
"discounttotal": -44,
"tax": 16,
"taxprice": 12.76,
"taxtotal": -7.04,
"tax_total": -51.04,
"qtyother": 0,
"freedom01": "",
"freedom02": "",
"freedom03": "",
"freedom04": "",
"freedom05": "",
"freedom06": "",
"freedom07": "",
"freedom08": "",
"freedom09": "",
"freedom10": "",
"freedom11": "",
"freedom12": "",
"freedom13": "",
"freedom14": 0,
"freedom15": 0,
"freedom16": 0,
"freedomdatedif": 0,
"detailsign": 0,
"source1": "15",
"source2": "15_4",
"serialno": []
},
{
"vchcode": 15,
"vchtype": 11,
"dlyorder": 5,
"ptypeid": "00004",
"pusercode": "004",
"pfullname": "存货4批号自定义",
"pstandard": "",
"ptype": "",
"unit": 1,
"unitname": "",
"barcode": "",
"ktypeid": "00001",
"kusercode": "001",
"kfullname": "仓库1",
"custom1": "00001",
"c1usercode": "001",
"c1fullname": "自定义101",
"custom2": "00001",
"c2usercode": "001",
"c2fullname": "自定义201",
"custom3": "3",
"custom4": "4",
"blockno": "A01",
"prodate": "",
"comment": "",
"qty": -3,
"price": 12.643333,
"total": -37.93,
"discount": 1,
"discountprice": 12.643333,
"discounttotal": -37.93,
"tax": 16,
"taxprice": 14.666667,
"taxtotal": -6.07,
"tax_total": -44,
"qtyother": 0,
"freedom01": "",
"freedom02": "",
"freedom03": "",
"freedom04": "",
"freedom05": "",
"freedom06": "",
"freedom07": "",
"freedom08": "",
"freedom09": "",
"freedom10": "",
"freedom11": "",
"freedom12": "",
"freedom13": "",
"freedom14": 0,
"freedom15": 0,
"freedom16": 0,
"freedomdatedif": 0,
"detailsign": 0,
"source1": "15",
"source2": "15_5",
"serialno": []
},
{
"vchcode": 15,
"vchtype": 11,
"dlyorder": 6,
"ptypeid": "00003",
"pusercode": "003",
"pfullname": "存货3 序列号",
"pstandard": "",
"ptype": "",
"unit": 1,
"unitname": "",
"barcode": "",
"ktypeid": "00001",
"kusercode": "001",
"kfullname": "仓库1",
"custom1": "",
"c1usercode": "",
"c1fullname": "",
"custom2": "",
"c2usercode": "",
"c2fullname": "",
"custom3": "",
"custom4": "",
"blockno": "",
"prodate": "",
"comment": "",
"qty": -2,
"price": 1.5,
"total": -3,
"discount": 1,
"discountprice": 1.5,
"discounttotal": -3,
"tax": 16,
"taxprice": 1.74,
"taxtotal": -0.48,
"tax_total": -3.48,
"qtyother": 0,
"freedom01": "",
"freedom02": "",
"freedom03": "",
"freedom04": "",
"freedom05": "",
"freedom06": "",
"freedom07": "",
"freedom08": "",
"freedom09": "",
"freedom10": "",
"freedom11": "",
"freedom12": "",
"freedom13": "",
"freedom14": 0,
"freedom15": 0,
"freedom16": 0,
"freedomdatedif": 0,
"detailsign": 0,
"source1": "15",
"source2": "15_6",
"serialno": [
{
"source1": "15_6",
"sn": "A02",
"comment": ""
},
{
"source1": "15_6",
"sn": "A03",
"comment": ""
}
]
}
],
"settle": []
}
]
}
查询接口表
Note
数据查询的API接口是通过动态获取存储过程返回的数据，并转成json格式返回的。
可以根据需求添加存储过程，自定义返回查询结果。
一、查询接口表
表名：T_Api_ReportQueryProcedureList
| 字段 | 类型 | 空 | 默认 | 注释 |
|---|---|---|---|---|
| ParamKey | nVarchar(200) | 是 | 空 | 接口传入的ParamsKey，用来区分调用的接口 |
| ProcName | nVarchar(200) | 是 | 空 | 存储过程名字 |
| IsSys | Int | 是 | 0 | 是否是系统自带的接口 |
| Comment | nVarchar(200) | 是 | 空 | 备注 |
| KeyName | nVarchar(200) | 是 | 空 | 用于显是json里面的key，第一级必须为response |
| KeySign | Int | 是 | 0 | 用于json中上下级关联，从0开始 |
| ChildKeySign | Int | 是 | 0 | 关联对应的KeySign，表示是返回的数据是对应KeySign的数据行中的下级 通过source关联判断具体是哪行 |
| Leveal | Int | 是 | 0 | 返回数据所在json的级次 |
返回数据中需要返回两个字段用于关联Source1和Source2
通过当前明细的Source1关联上级明细的Source2实现上下级次的关联
- 插入数据示例
| ParamKey | ProcName | IsSys | Comment | KeyName | KeySign | ChildKeySign | Leveal |
|---|---|---|---|---|---|---|---|
| DlySale | P_Api_QueryDlyNdxData | 1 | response | 0 | 1 | 1 | |
| DlySale | P_Api_QueryDlySaleData | 1 | detail | 1 | 2 | 2 | |
| DlySale | P_Api_QueryDlySettleData | 1 | settle | 1 | 3 | 2 | |
| DlySale | P_Api_QueryDlySNData | 1 | serialno | 2 | 4 | 3 |
示例说明：示例为返回销售类单据数据，分为四部分
P_Api_QueryDlyNdxData为单据索引，返回查询的所有单据索引
P_Api_QueryDlySaleData为单据明细，返回查询的所有单据对应的全部明细
P_Api_QueryDlySettleData单据现收明细,返回查询的所有单据对应的全部现收明细
P_Api_QueryDlySNData序列号明细，返回所有查询单据的序列号明细
· 索引为第一级，所以KeySign为0，ChildKeySign设置为1，Source1和Source2返回均为Vchcode值。
· 单据明细和现收明细并列为第二级，所以KeySign为1关联上一级的ChildKeySign，
ChildKeySign设置为2和3,Source1为Vchcode,Source2为Vchcode+''+DlyOrder。
· 序列号明细为单据明细下面的第三级，所以KeySign为2关联上一级ChildKeySign为2的单据明细，
Source1为Vchcode+''+DlyOrder，没有下一级Source2可以为空。
· Source字段为字符串，实际内容没有具体要求，只要能够标识唯一关联关系即可。
· 具体返回结果可以查看：返回参数说明文档，了解返回的Json效果。
- 二、接口存储过程传入参数：
存储过程传入参数统一为XML格式；
参数名：@ParamXML nvarchar(max)
参数传入后在存储过程中自行解析获取查询条件。
- 三、分页
存储过程返回的查询数据建议调用分页存储过程进行分页。
分页存储过程名：P_Api_ToPage
参数：1、@TableName NVARCHAR(100) --临时表名字
2、@PageSize INT,--每页显示数量
3、@PageIndex INT,--页码
4、@PageTotal INT OUTPUT --共有多少条数据
示例：EXEC P_Api_ToPage '#NdxData', @PageSize, @PageIndex, @PageTotal OUTPUT
先在存储过程中根据查询条件查询出所有要返回的数据插入临时表，
然后调用分页存储过程进行分页，
最后再查询临时表的数据即为分页后的数据。
- 存储过程示例
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[P_Api_QueryDlyNdxData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].P_Api_QueryDlyNdxData
GO
CREATE PROCEDURE dbo.[P_Api_QueryDlyNdxData]
(
@ParamXML nvarchar(max)
)
As
BEGIN
SET NOCOUNT ON
DECLARE @xmlHandle INT
EXEC sp_xml_preparedocument @xmlHandle OUTPUT, @ParamXML
SELECT Vchtype,Number,BeginDate,EndDate,PageSize,PageIndex
INTO #CondData
FROM OPENXML(@xmlHandle,N'/Root/Record')
WITH(Vchtype int,Number nvarchar(100),BeginDate nvarchar(10),
EndDate nvarchar(10), PageSize INT,PageIndex INT)
EXEC sp_xml_removedocument @xmlHandle
DECLARE @Vchtype int
DECLARE @Number nvarchar(100)
DECLARE @BeginDate nvarchar(10)
DECLARE @EndDate nvarchar(10)
DECLARE @PageSize INT
DECLARE @PageIndex INT
DECLARE @PageFilter NVARCHAR(MAX)
SELECT @Vchtype = ISNULL(Vchtype,0),
@Number = ISNULL(Number,''),
@BeginDate = ISNULL(BeginDate,CONVERT(nvarchar(10),GetDate(),120)),
@EndDate = ISNULL(EndDate,CONVERT(nvarchar(10),GetDate(),120)),
@PageSize = (CASE WHEN ISNULL(PageSize,200) <= 0 OR
ISNULL(PageSize,200) > 200 THEN 200
ELSE ISNULL(PageSize,200) END), --每页显示数量
@PageIndex = (ISNULL(PageIndex,0))--页码
FROM #CondData
SELECT ROW_NUMBER() OVER (ORDER BY ndx.Date ) as RowNum,
ndx.Vchcode,ndx.Vchtype,ndx.Date,ndx.Number,
ndx.BtypeID,ISNULL(b.UserCode,'') as BUserCode,
ISNULL(b.FullName,'') as BFullName,ndx.SettleBtypeID,
ISNULL(b2.UserCode,'') as SettleBUserCode,
ISNULL(b2.FullName,'') as SettleBFullName,
ndx.KtypeID,ISNULL(k.UserCode,'') as KUserCode,
ISNULL(k.FullName,'') as KFullName,
ndx.KtypeID2,ISNULL(k2.UserCode,'') as K2UserCode,
ISNULL(k2.FullName,'') as K2FullName,
ndx.EtypeID,ISNULL(e.UserCode,'') as EUserCode,
ISNULL(e.FullName,'') as EFullName,
ndx.EtypeID2,ISNULL(e2.UserCode,'') as E2UserCode,
ISNULL(e2.FullName,'') as E2FullName,
ndx.ProjectID as DtypeID,ISNULL(d.UserCode,'') as DUserCode,ISNULL(d.FullName,'') as DFullName,
ndx.MtypeID,ISNULL(m.UserCode,'') as MUserCode,
ISNULL(m.FullName,'') as MFullName,
ndx.Total,ndx.BillTotal,ndx.Summary,ndx.GatheringDate,
ndx.UserDefined01,ndx.UserDefined02,ndx.UserDefined03,
ndx.UserDefined04,ndx.UserDefined05,
ndx.UserDefined06,ndx.UserDefined07,ndx.UserDefined08,
ndx.UserDefined09,ndx.UserDefined10,
ndx.UserDefined11,ndx.UserDefined12,ndx.UserDefined13,
ndx.UserDefined14,ndx.UserDefined15,
ndx.UserDefined16,ndx.InputNo,ndx.SaveTime,
ndx.DifAtype,ISNULL(da.Memo,'') as DifAtypeName,
ISNULL(da.Atype,'') as AtypeID,ISNULL(a.Total,0) as MLTotal,
CAST(ndx.Vchcode as nvarchar) as Source1,
CAST(ndx.Vchcode as nvarchar) as Source2
INTO #NdxData
FROM DlyNdx ndx
LEFT JOIN Btype b ON ndx.BtypeID = b.TypeID
LEFT JOIN Btype b2 ON ndx.SettleBtypeID = b2.TypeID
LEFT JOIN Stock k ON ndx.KtypeID = k.TypeID
LEFT JOIN Stock k2 ON ndx.KtypeID2 = k2.TypeID
LEFT JOIN Employee e ON ndx.EtypeID = e.TypeID
LEFT JOIN Employee e2 ON ndx.EtypeID2 = e2.TypeID
LEFT JOIN Department d ON ndx.ProjectID = d.TypeID
LEFT JOIN Mtype m ON ndx.MtypeID = m.TypeID
LEFT JOIN DlyA a ON ndx.Vchcode = a.Vchcode
and ndx.Vchtype = a.Vchtype and a.UsedType = 6
LEFT JOIN DifAtype da ON ndx.DifAtype = da.Rec
WHERE ndx.Vchtype = @Vchtype
AND ndx.Date BETWEEN @BeginDate AND @EndDate
AND ndx.Number like '%'+@Number+'%'
ORDER BY ndx.Date
DECLARE @PageTotal INT
EXEC P_Api_ToPage '#NdxData', @PageSize, @PageIndex, @PageTotal OUTPUT
SELECT * FROM #NdxData
DROP TABLE #NdxData
END
GO