bos webapi 单据查询接口调用参数示例原创
云社区用户26064194
2人赞赏了该文章
1255次浏览
编辑于2022年05月06日 18:09:32
单据查询接口:
"executeBillQueryUrl": "http://xxx:xxx/k3cloud/Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.ExecuteBillQuery.common.kdsvc"
--销售订单
{
"FormId": "SAL_SaleOrder",
"FieldKeys": "FSaleOrgId.FNUMBER,FCustId.FNAME,FID,FBillNo,FSaleOrderEntry_FEntryID,FTaxPrice,FSaleOrderFinance_FEntryId",
"FilterString": "(FSaleOrgId=F_HANS_Base AND FDOCUMENTSTATUS='C' and FFORBIDSTATUS='A' and (FMODIFYDATE>=to_date('2000-05-08 00:00:00','yyyy-mm-dd hh24:mi:ss') and FMODIFYDATE<=to_date('2022-05-08 00:00:00','yyyy-mm-dd hh24:mi:ss') ) )",
"OrderString": "",
"TopRowCount": 2,
"StartRow": 0,
"Limit": 0,
"SubSystemId": ""
}/// <summary>
/// POST api/webapi/mdmqryerp
/// 主数据平台拉取ERP单据数据
/// </summary>
/// <param name="inputJO"></param>
/// <returns></returns>
[HttpPost]
[Route("mdmqryerp")]
public IActionResult MDMQueryERP([FromBody] JObject inputJO)
{
//k3cloud的单据唯一标识
string k3CloudFormId = Convert.ToString(inputJO[WebApiInputFieldName.K3CLOUDFORMID]);
//增量时间字段
string strTime = Convert.ToString(inputJO[WebApiInputFieldName.MDM_TIME]);
//起始页码:注意:从0开始
string strPageIndex = Convert.ToString(inputJO[WebApiInputFieldName.MDM_PAGEINDEX]);
//每页大小(如:50)
string strPageSize = Convert.ToString(inputJO[WebApiInputFieldName.MDM_PAGESIZE]);
//返回数据
string backJson = string.Empty;
if (string.IsNullOrEmpty(k3CloudFormId)
|| string.IsNullOrEmpty(strTime)
|| string.IsNullOrEmpty(strPageIndex)
|| string.IsNullOrEmpty(strPageSize)
)
{
JObject o = new JObject();
o.Add(WebApiOutputFieldName.MDMTOCLOUD_CODE, Define.MDMTOCLOUD_ERROR);
o.Add(WebApiOutputFieldName.MDMTOCLOUD_MESSAGE, Define.MDMTOCLOUD_FAIL);
o.Add(WebApiOutputFieldName.MDMTOCLOUD_DETAIL, "接口调用失败,入参非法");
backJson = JsonConvert.SerializeObject(o);
}
else
{
#region 入参数据结构
//入参数据结构:
/*
{
"FormId": "BD_Customer",
"FieldKeys": "FNumber,FMODIFYDATE",
"FilterString": "FNumber IN ('ZZ99000','ZZ99014') and FUSEORGID=FCreateOrgId and (FMODIFYDATE>=to_date('2009-11-01 11:49:46','yyyy-mm-dd hh24:mi:ss') and FMODIFYDATE<=to_date('2029-11-01 11:49:46','yyyy-mm-dd hh24:mi:ss') )",
"OrderString": "",
"TopRowCount": 2000,
"StartRow": 0,
"Limit": 0,
"SubSystemId": ""
}
*/
#endregion
//修改日期时间开始,结束
string bFMODIFYDATE = strTime;
string eFMODIFYDATE = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
string strFilter = $" ( FDOCUMENTSTATUS='C' and FFORBIDSTATUS='A' and FUSEORGID=FCreateOrgId and (FMODIFYDATE>=to_date('{bFMODIFYDATE}','yyyy-mm-dd hh24:mi:ss') and FMODIFYDATE<=to_date('{eFMODIFYDATE}','yyyy-mm-dd hh24:mi:ss') ) )";
string strSqlFilter = $" ( x.FDOCUMENTSTATUS='C' and x.FFORBIDSTATUS='A' and x.FUSEORGID=x.FCreateOrgId and (x.FMODIFYDATE>=to_date('{bFMODIFYDATE}','yyyy-mm-dd hh24:mi:ss') and x.FMODIFYDATE<=to_date('{eFMODIFYDATE}','yyyy-mm-dd hh24:mi:ss') ) )";
string strFieldKeys = PubCollExt.GetCloudFieldKeysByFormId(k3CloudFormId);
JObject jo = new JObject();
jo.Add("FormId", k3CloudFormId);
jo.Add("FieldKeys", strFieldKeys);
jo.Add("FilterString", strFilter);
jo.Add("TopRowCount", strPageSize);
jo.Add("StartRow", strPageIndex);
string strClassName = $"HansWebApi.MDMQRYBLL.{k3CloudFormId}Bll";
string strClassMethod = "QryOrder";
CallBllMethod billMothod = new CallBllMethod(strClassName, strClassMethod, new object[] { k3CloudFormId, jo, strSqlFilter });
backJson = billMothod.BllMethod();
}
return Ok(backJson);
}using Common;
using HansWebApi.Common;
using HansWebApi.MDMBLL;
using HansWebApi.Models;
using Model;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
namespace HansWebApi.MDMQRYBLL
{
/// <summary>
/// 主数据拉数据通用处理类
/// </summary>
public class MDMQryCloudBll : MDMBase
{
/// <summary>
/// K3Cloud单据唯一标识
/// </summary>
protected string _formId;
/// <summary>
/// 用查询单据记录总条数
/// </summary>
protected string _sqlFilter;
/// <summary>
/// json数据包
/// </summary>
protected JObject _dataPacket;
/// <summary>
/// 主数据拉数据通用处理类
/// </summary>
/// <param name="k3CloudFormId">formId</param>
/// <param name="dataPacket">用于调用CloudExecuteBillQueryByObject</param>
/// <param name="sqlFilter">用查询单据记录总条数</param>
public MDMQryCloudBll(string k3CloudFormId, JObject dataPacket, string sqlFilter)
{
_formId = k3CloudFormId;
_sqlFilter = sqlFilter;
_dataPacket = dataPacket;
}
/// <summary>
/// 重写业务逻辑
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
public virtual MdmInQryModel BusinessProcess(string data)
{
MdmInQryModel retData = new MdmInQryModel(_formId: _formId, _data: new JArray(), _backJson: string.Empty);
return retData;
}
/// <summary>
/// 主数据拉取K3Cloud单据数据
/// </summary>
public virtual string QryOrder()
{
string backJson = string.Empty;
string jsonData = _dataPacket.ToJson();
var orginalJsonData = ICloudLinkBll.CloudExecuteBillQuery(jsonData);
MdmInQryModel retData = BusinessProcess(orginalJsonData);
if (!string.IsNullOrEmpty(retData.backJson))
{
//若产生异常直接返回
return retData.backJson;
}
JObject o = new JObject();
try
{
string totalRecord = GetTotalRecordWithFilter(_sqlFilter);
o.Add(WebApiOutputFieldName.MDMTOCLOUD_RTNCODE, Define.MDMTOCLOUD_RTNCODE_S);
o.Add(WebApiOutputFieldName.MDMTOCLOUD_RTNMSG, retData.backJson);
o.Add(WebApiOutputFieldName.MDMTOCLOUD_RTNTOTALRECORD, totalRecord);
o.Add(WebApiOutputFieldName.MDMTOCLOUD_RTNDATA, retData.data);
}
catch (Exception ex)
{
o.Add(WebApiOutputFieldName.MDMTOCLOUD_RTNCODE, Define.MDMTOCLOUD_RTNCODE_F);
o.Add(WebApiOutputFieldName.MDMTOCLOUD_RTNMSG, ex.ToString());
o.Add(WebApiOutputFieldName.MDMTOCLOUD_RTNTOTALRECORD, Define.MDMTOCLOUD_RTNTOTALRECORD_0);
o.Add(WebApiOutputFieldName.MDMTOCLOUD_RTNDATA, retData.data);
}
backJson = o.ToJson();
return backJson;
}
/// <summary>
/// 根据SQL获取符合条件的总记录数
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public virtual string GetTotalRecordWithFilter(string sql)
{
if (string.IsNullOrEmpty(sql)) return string.Empty;
string strTotalRecord = string.Empty;
var dbResult = ICloudLinkBll.CloudDbWebApi(sql);
if (dbResult.status == "0" && dbResult.result != "[]")
{
var jaResultList = (JArray)JsonConvert.DeserializeObject(dbResult.result);
foreach (var model in jaResultList)
{
BasicDataModel m = new BasicDataModel();
var joResult = JObject.Parse(model.ToString());
strTotalRecord = joResult["TOTALRECORD"].ToString();
}
}
return strTotalRecord;
}
}
}using K3CloudLink;
namespace HansWebApi.MDMBLL
{
public class MDMBase
{
/// <summary>
/// Cloud的接口
/// </summary>
internal ICloudLink ICloudLinkBll;
public MDMBase()
{
Initialization();
}
/// <summary>
/// 初始化
/// </summary>
public void Initialization()
{
ICloudLinkBll = new CloudLinkN();
ICloudLinkBll.CloudLogin();
}
}
}using Common;
using HansWebApi.Common;
using Model;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
namespace HansWebApi.MDMQRYBLL
{
/// <summary>
/// 付款条件
/// </summary>
public class BD_PaymentConditionBll : MDMQryCloudBll
{
public BD_PaymentConditionBll(string k3CloudFormId, JObject dataPacket, string sqlFilter) : base(k3CloudFormId, dataPacket, sqlFilter) { }
/// <summary>
/// 重写业务逻辑
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
public override MdmInQryModel BusinessProcess(string data)
{
MdmInQryModel retData = new MdmInQryModel(_formId: _formId, _data:new JArray(), _backJson: string.Empty);
try
{
List<List<string>> list = JsonConvert.DeserializeObject<List<List<string>>>(data);
if (!list.IsEmpty())
{
JArray arrData = new JArray();
list.ForEach(p => {
JObject oData = new JObject();
//FID
oData.Add("sourceId", p[0]);
//编码
oData.Add("mdmCode", p[1]);
//名称
oData.Add("mdmName", p[2]);
//描述
oData.Add("describe", p[3]);
arrData.Add(oData);
});
retData.data = arrData;
}
}
catch (Exception ex)
{
JObject o = new JObject();
o.Add(WebApiOutputFieldName.MDMTOCLOUD_RTNCODE, Define.MDMTOCLOUD_RTNCODE_F);
o.Add(WebApiOutputFieldName.MDMTOCLOUD_RTNMSG, ex.ToString());
o.Add(WebApiOutputFieldName.MDMTOCLOUD_RTNTOTALRECORD, Define.MDMTOCLOUD_RTNTOTALRECORD_0);
o.Add(WebApiOutputFieldName.MDMTOCLOUD_RTNDATA, retData.data);
retData.backJson = JsonConvert.SerializeObject(o);
}
return retData;
}
/// <summary>
/// 根据SQL获取符合条件的总记录数
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public override string GetTotalRecordWithFilter(string sqlFilter)
{
string strSQL = @$"select count(1) as TOTALRECORD
from T_BD_PaymentCondition x
inner join T_BD_PAYMENTCONDITION_L y on x.FID=y.FID
where 1=1 and {sqlFilter}
";
string strTotalRecord = base.GetTotalRecordWithFilter(strSQL);
return strTotalRecord;
}
}
}/// <summary>
/// 根据Cloud的formId获取FieldKeys
/// </summary>
/// <param name="formId">Cloud的formId</param>
/// <returns>
/// 1.2.FieldKeys：需查询的字段key集合，字符串类型，格式："key1,key2,..."（必录） 注（查询单据体内码,需加单据体Key和下划线,如：FEntryKey_FEntryId）
/// </returns>
public static string GetCloudFieldKeysByFormId(string formId)
{
string strFieldKeys = "";
formId = formId.Trim().ToUpper();
switch (formId)
{
//客户
case "BD_CUSTOMER":
{
strFieldKeys = "FNumber,FName";
break;
}
//科目
case "BD_ACCOUNT":
{
strFieldKeys = "facctid,FNumber,FName";
break;
}
//银行网点
case "WB_BANKDETAIL":
{
strFieldKeys = "FID,FNUMBER,FNAME,FBANKTYPEREC.FNUMBER,FBANKTYPEREC.FNAME,FCOUNTRY,FPROVINCE,FCITY,FDISTRICT,FDISTRICTCODE,FBANKADDRESS,FDESCRIPTION,FDOCUMENTSTATUS";
break;
}
//付款条件
case "BD_PAYMENTCONDITION":
{
strFieldKeys = "FID,FNumber,FName,FDescription";
break;
}
default:
{
strFieldKeys = "FName";
break;
}
}
return strFieldKeys;
}using Newtonsoft.Json.Linq;
namespace Model
{
public class MdmInQryModel
{
/// <summary>
/// 构造函数
/// </summary>
public MdmInQryModel() { }
/// <summary>
/// 构造函数
/// </summary>
/// <param name="_formid">单据id</param>
/// <param name="_data">处理后的JArray数据</param>
/// <param name="_backJson">解释数据产生异常时,返回的json数据</param>
public MdmInQryModel(string _formId, JArray _data, string _backJson) : this()
{
formId = _formId;
data = _data;
backJson = _backJson;
}
/// <summary>
/// 单据id
/// </summary>
public string formId { get; set; }
/// <summary>
/// 处理后的JArray数据
/// </summary>
public JArray data { get; set; }
/// <summary>
/// 解释数据产生异常时,返回的json数据
/// </summary>
public string backJson { get; set; }
}
}其他参照附件代码文件
K3CloudLink.rar(320.65KB)
推荐阅读