ERP数据同步至第三方应用平台

需求：将ERP中销售订单数据定时同步至第三方应用平台如飞书的多维表格，或者其他ERP产品

具体实施方案及思路：之前做开发过一个销售订单中点击审核按钮调用第三方平台的接口后将数据传输过去，但是后续运维较为麻烦，比如修改还需要再调用，删除还需要再调用。调用次数多后期维护感觉繁琐，所以这次改变思路。

在本地创建一个中间库，中间库主要是用来存放销售订单中查询出来的数据，后再调用第三方平台数据接口将数据同步至第三方平台。

飞书多维表格中有一个可以连接MYSQL数据库定时更新的功能，所有飞书的接口可以不用单独写，直接配置即可。30分钟可以同步一次。那接下来就是要将ERP销售订单数据同步到本地的MYSQL中间数据库中。

以下为主要的代码，SQLhelp类网上找下就可以了：

  var clienter = new K3CloudApi();

  string formId = "CRM_Contract";

  string josnDat = "{" +

      "\"FormId\": \"CRM_Contract\"," +

      "\"FieldKeys\": \"FSalerId.FNAME,FBillNo,FDate,FName,F_ora_BasePropertyJFS,F_ora_TextZZYH,F_ora_Assistant.FNumber,FMaterialName,FMaterialModel,FUnitID.Fname,FQty,FAllAmount,FRecConditionId.FNAME,FSettleCurrId.FName,FDocumentStatus,FCancelStatus,FBDCUSTID.Fnumber,F_ora_Base1.Fname,FAllAmount_LC,F_ora_Remarks\"," +

      "\"FilterString\": [{\"Left\":\"\",\"FieldName\":\"FDate\",\"Compare\":\"39\",\"Value\":\"2023-01-01\",\"Right\":\"\",\"Logic\":0},{\"Left\":\"\",\"FieldName\":\"FSALEORGID.FName\",\"Compare\":\"67\",\"Value\":\"上海*******限公司\",\"Right\":\"\",\"Logic\":0},{\"Left\":\"\",\"FieldName\":\"FDocumentStatus\",\"Compare\":\"105\",\"Value\":\"C\",\"Right\":\"\",\"Logic\":0},{\"Left\":\"\",\"FieldName\":\"FCancelStatus\",\"Compare\":\"105\",\"Value\":\"A\",\"Right\":\"\",\"Logic\":0}]," +

      "\"OrderString\": \"\"," +

      "\"TopRowCount\": 0," +

      "\"StartRow\": 0," +

      "\"Limit\": 5000," +

      "\"SubSystemId\": \"\"}";

  clienter.InitClient("*****", "*****","*****","****", 2052, "G", "https://********.com/k3cloud/");

  var s =  clienter.BillQuery(josnDat);

  JArray ja = (JArray)JsonConvert.DeserializeObject(s);

  Console.WriteLine(ja[0].ToString());

  Console.WriteLine(ja.Count);

  string connectionString = "server=192.168.100.1;database=erp;uid=root;password=123456;";

  MySQLHelper mySQLHelper = new MySQLHelper(connectionString);

//DataTable dataTable = mySQLHelper.ExecuteQuery("SELECT * FROM crm_contract");

  Dictionary<string, object> data = new Dictionary<string, object>();

  for(int i=0;i<ja.Count; i++)

  {

      data.Add("FBillNo", ja[i]["FBillNo"].ToString());

      data.Add("FBDCUSTNumber", ja[i]["FBDCUSTID.Fnumber"].ToString());

      data.Add("FSaler", ja[i]["FSalerId.FNAME"].ToString());

      data.Add("FName", ja[i]["FName"].ToString());

      data.Add("FDate", ja[i]["FDate"]);

      data.Add("F_ora_BasePropertyJFS", ja[i]["F.ora.BasePropertyJFS"].ToString());

      data.Add("F_ora_TextZZYH", ja[i]["F.ora.TextZZYH"].ToString());

      data.Add("F_ora_Assistant", ja[i]["F.ora.Assistant.FNumber"].ToString());

      data.Add("FMaterialName", ja[i]["FMaterialName"].ToString());

      data.Add("FMaterialModel", ja[i]["FMaterialModel"].ToString());

      data.Add("FUnit", ja[i]["FUnitID.Fname"].ToString());

      data.Add("FQty", ja[i]["FQty"]);

      data.Add("FAllAmount", ja[i]["FAllAmount"]);

      data.Add("FRecCondition", ja[i]["FRecConditionId.FNAME"].ToString());

      data.Add("FSettleCurr", ja[i]["FSettleCurrId.FName"].ToString());

      data.Add("FBaseRec", ja[i]["F.ora.Base1.Fname"].ToString());

      data.Add("FRemarks", ja[i]["F.ora.Remarks"].ToString());

      data.Add("FDocStatus", ja[i]["FDocumentStatus"].ToString());

      data.Add("FCancelStatus", ja[i]["FCancelStatus"].ToString());

      data.Add("FAllAmount_LC", ja[i]["FAllAmount.LC"]);

      int result = mySQLHelper.Insert("crm_contract", data);

      data.Clear();

  }