调用接口，将数据插入数据库表，并通过表配置跑某个时间段数据

最新推荐文章于 2024-05-09 17:06:44 发布

灿烂滴大白牙

最新推荐文章于 2024-05-09 17:06:44 发布

阅读量198

点赞数 7

文章标签： c# json sqlserver

本文链接：https://blog.csdn.net/qq_41528228/article/details/135924824

版权

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Net.Http.Json;
using System.Security.Cryptography.X509Certificates;
using System.Text;
using System.Threading.Tasks;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Microsoft.Data.SqlClient;
using static Program;
using System.Net;
using System.Data;
using System.Data.SqlTypes;
using System.Diagnostics;
using System.Timers;

class Program
{
static async System.Threading.Tasks.Task Main(string[] args)
{

string tokenEndpoint = "aaa";
string appKey = "bbb";
string appSecurity = "ccc";

//接口url
//获取单据
string ApplyListUrl = "ddd";

//查询员工：包含姓名
string StaffIdsUrl = "eee";

//根据部门ID获取部门信息
string DepartmentsUrl = "fff";

string connectionString = @"Server=;uid=;pwd=;Database=;TrustServerCertificate=true;Encrypt=true";

DateTime now = DateTime.Now;
string currentTime = now.ToString("yyyy-MM-dd HH:mm:ss");

try
{
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
string sqlConfigQuery = "SELECT * FROM table";
string sqlConfigUpdate = "update table set startTime = @configStartTime, endTime = @configEndTime, lastStartTime = @lastStartTime, lastEndTime = @lastEndTime";

sqlConnection.Open();

using (SqlCommand queryConfigCommand = new SqlCommand(sqlConfigQuery, sqlConnection))
{

//执行查询操作
SqlDataReader reader = queryConfigCommand.ExecuteReader();

if (reader.Read())
{
string configStartTime = (string)reader["startTime"];
string configEndTime = (string)reader["endTime"];
string lastStartTime = (string)reader["lastStartTime"];
string lastEndTime = (string)reader["lastEndTime"];

try
{
using (HttpClient client = new HttpClient())
{

// 获取accessToken
// 构建请求参数
var requestData = new StringContent(JsonConvert.SerializeObject(new Dictionary<string, string>
{
//{ "grant_type", "client_credentials" },
{ "appKey", appKey },
{ "appSecurity", appSecurity },
}), Encoding.UTF8, "application/json");

// 发送POST请求到Token端点
HttpResponseMessage response = await client.PostAsync(tokenEndpoint, requestData);
response.EnsureSuccessStatusCode();

// 读取响应内容并解析Token
string json = await response.Content.ReadAsStringAsync();
TokenResponseWrapper responseWrapper = JsonConvert.DeserializeObject<TokenResponseWrapper>(json);
TokenResponse tokenResponse = responseWrapper?.value;

// 调用获取单据的接口
// 创建包含参数的查询字符串
Dictionary<string, string> getApplyListParameters = new Dictionary<string, string>
{
{ "accessToken", tokenResponse.accessToken },
{ "type", "requisition" },
{ "state", "paid"},
//{ "start", "0"},
{ "count", "100"},
{ "startDate", configStartTime},
{ "endDate", configEndTime}
};
string getApplyListQueryString = string.Join("&",
getApplyListParameters.Select(x => $"{x.Key}={Uri.EscapeDataString(x.Value)}"));

// 构建完整的请求 URL
string getApplyListDynamicParameters = "start=0";
string ApplyListRequestUrl = $"{ApplyListUrl}?{getApplyListQueryString}&{getApplyListDynamicParameters}";

//发送get请求
HttpResponseMessage getApplyListUrl = await client.GetAsync(ApplyListRequestUrl);
//确保响应码为成功状态
getApplyListUrl.EnsureSuccessStatusCode();

//将响应内容读取为字符串
string ApplyList = await getApplyListUrl.Content.ReadAsStringAsync();
ApplyListRootObject applylistroot = JsonConvert.DeserializeObject<ApplyListRootObject>(ApplyList);

//获取总条数
int count = applylistroot.count;

//start：分页查询起始值
//count：数据查询条数，最多为100
//需先循环输出单据列表，start以0为起始值，100条数据为每页查询条数
if (count > 0)
{
for (int cnt = 0; cnt < count; cnt += 100)
{
getApplyListDynamicParameters = "start=" + cnt;
ApplyListRequestUrl = $"{ApplyListUrl}?{getApplyListQueryString}&{getApplyListDynamicParameters}";
getApplyListUrl = await client.GetAsync(ApplyListRequestUrl);
getApplyListUrl.EnsureSuccessStatusCode();

ApplyList = await getApplyListUrl.Content.ReadAsStringAsync();
applylistroot = JsonConvert.DeserializeObject<ApplyListRootObject>(ApplyList);

if (ApplyList != null && applylistroot.items != null)
{

//将long类型的expireTime转换为date类型
//DateTimeOffset expireTime = DateTimeOffset.FromUnixTimeMilliseconds(tokenResponse.expireTime);
//Console.WriteLine("过期时间: {0}", expireTime);

//ApplyListRootObject applylistroot = JsonConvert.DeserializeObject<ApplyListRootObject>(ApplyList);

for (int i = 0; i < applylistroot.items.Length; i++)
{
ApplyListItem applylistitem = applylistroot.items[i];
ApplyListForm applylistform = applylistitem.form;
ApplyListfeeDatePeriod applylistfeeDatePeriod = applylistform.feeDatePeriod;

string code = applylistform.code;
string submitterId = applylistform.submitterId;
long submitDate = applylistform.submitDate;
string expenseDepartment = applylistform.expenseDepartment;
long startTime = applylistfeeDatePeriod.start;
long endTime = applylistfeeDatePeriod.end;
string description = applylistform.description;
long flowEndTime = applylistform.flowEndTime;

//调用"查询员工"的接口,获取申请人姓名
Dictionary<string, string> postStaffIdsParameters = new Dictionary<string, string>
{
{ "accessToken", tokenResponse.accessToken }
};

string postStaffIdsQueryString = string.Join("&",
postStaffIdsParameters.Select(x => $"{x.Key}={Uri.EscapeDataString(x.Value)}"));

string StaffIdsRequestUrl = $"{StaffIdsUrl}?{postStaffIdsQueryString}";

var postStaffIdsBodyParameters = new StringContent("{\"type\":\"STAFFID\",\"conditionIds\":[\"" + submitterId + "\"]}");
postStaffIdsBodyParameters.Headers.ContentType.MediaType = "application/json";

HttpResponseMessage postStaffIdsResponse = await client.PostAsync(StaffIdsRequestUrl, postStaffIdsBodyParameters);
postStaffIdsResponse.EnsureSuccessStatusCode();

string StaffIds = await postStaffIdsResponse.Content.ReadAsStringAsync();

StaffIdstRootObject staffidsroot = JsonConvert.DeserializeObject<StaffIdstRootObject>(StaffIds);
string staffname;
string staffcode;

if (staffidsroot.items != null && staffidsroot.items.Length >= 1)
{
StaffIdsItem staffidsitem = staffidsroot.items[0];

staffname = staffidsitem.name;
staffcode = staffidsitem.code;

}
else
{
staffname = null;
staffcode = null;
}

//修改时间格式
DateTime submitDateTime = DateTimeOffset.FromUnixTimeMilliseconds(submitDate).DateTime.AddHours(8);
DateTime startDateTime = DateTimeOffset.FromUnixTimeMilliseconds(startTime).DateTime.AddHours(8);
DateTime endDateTime = DateTimeOffset.FromUnixTimeMilliseconds(endTime).DateTime.AddHours(8);
DateTime flowEndDateTime = DateTimeOffset.FromUnixTimeMilliseconds(flowEndTime).DateTime.AddHours(8);

string submitDateTimeFormatted = submitDateTime.ToString("yyyy/MM/dd HH:mm:ss.fff");
string startDateTimeFormatted = startDateTime.ToString("yyyy/MM/dd HH:mm:ss.fff");
string endDateTimeFormatted = endDateTime.ToString("yyyy/MM/dd HH:mm:ss.fff");
string flowEndDateTimeFormatted = flowEndDateTime.ToString("yyyy/MM/dd HH:mm:ss.fff");

//出差天数
TimeSpan duration = endDateTime.Date - startDateTime.Date;
int daysDuration = duration.Days + 1;

//调用“根据部门ID获取部门信息”接口，获取申请人部门
//“获取单据”接口中的expensedepartment时最后一级部门，它的上级部门是“根据部门ID获取部门信息”接口中的parentid

Dictionary<string, string> getDepartmentsParameters = new Dictionary<string, string>
{
{ "accessToken", tokenResponse.accessToken }
};

string getDepartmentsQueryString = string.Join("&",
getDepartmentsParameters.Select(x => $"{x.Key}={Uri.EscapeDataString(x.Value)}"));

string DepartmentsRequestUrl = $"{DepartmentsUrl}${expenseDepartment}?{getDepartmentsQueryString}";

HttpResponseMessage getDepartmentsResponse = await client.GetAsync(DepartmentsRequestUrl);

string departmentname;

if (getDepartmentsResponse.IsSuccessStatusCode)
{
getDepartmentsResponse.EnsureSuccessStatusCode();

string Departments = await getDepartmentsResponse.Content.ReadAsStringAsync();
DepartmentReponseData departmentResponseData = JsonConvert.DeserializeObject<DepartmentReponseData>(Departments);

departmentname = departmentResponseData.value.name;
string departmentparentid = departmentResponseData.value.parentId;

if (departmentparentid != "")
{
while (!string.IsNullOrEmpty(departmentparentid))
{
DepartmentsRequestUrl = $"{DepartmentsUrl}${departmentparentid}?{getDepartmentsQueryString}";

//以parentid作为id去响应
HttpResponseMessage getDepartmentsResponseData = await client.GetAsync(DepartmentsRequestUrl);
getDepartmentsResponseData.EnsureSuccessStatusCode();

Departments = await getDepartmentsResponseData.Content.ReadAsStringAsync();
departmentResponseData = JsonConvert.DeserializeObject<DepartmentReponseData>(Departments);

if (departmentResponseData.value.name == "")
{
break;
}
else
{
departmentname = departmentResponseData.value.name + "/" + departmentname;
departmentparentid = departmentResponseData.value.parentId;
}

}
}
else
{
departmentparentid = departmentResponseData.value.parentId;
}
}
else
{
departmentname = null;
}

//Console.WriteLine(code + ":" + staffcode + staffname + "," + departmentname + ";" + submitDateTimeFormatted + "," + startDateTimeFormatted + "," + endDateTimeFormatted + ":" + description + "!!!" + daysDuration);

try
{
//创建连接对象
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sqlQuery = "SELECT COUNT(*) FROM table WHERE CODE = @code";

//string sqlInsert = "MERGE INTO table AS A " +
// "USING table AS B ON A.CODE = @code " +
// "WHEN NOT MATCHED THEN " +
// "INSERT VALUES (@code, @staffcode, @staffname, @departmentname, @submitDateTimeFormatted, @startDateTimeFormatted, @endDateTimeFormatted, @daysDuration, @description);";

string sqlInsert = "INSERT INTO table VALUES " +
"(@code, @staffcode, @staffname, @departmentname, @submitDateTimeFormatted, @startDateTimeFormatted, @endDateTimeFormatted, @daysDuration, @description, '0',@flowEndDateTimeFormatted);";

//打开数据库连接
connection.Open();

//创建命令对象
using (SqlCommand queryCommand = new SqlCommand(sqlQuery, connection))
{
queryCommand.Parameters.AddWithValue("@code", code);

//执行查询操作
int cntSqlQuery = (int)queryCommand.ExecuteScalar();

if (cntSqlQuery == 0)
{
using (SqlCommand insertCommand = new SqlCommand(sqlInsert, connection))
{
//主键，不可能为空
insertCommand.Parameters.AddWithValue("@code", code);
insertCommand.Parameters.AddWithValue("@staffcode", staffcode != null ? staffcode : DBNull.Value);
insertCommand.Parameters.AddWithValue("@staffname", staffname != null ? staffname : DBNull.Value);
insertCommand.Parameters.AddWithValue("@departmentname", departmentname != null ? departmentname : DBNull.Value);
insertCommand.Parameters.AddWithValue("@submitDateTimeFormatted", submitDateTimeFormatted != null ? submitDateTimeFormatted : DBNull.Value);
insertCommand.Parameters.AddWithValue("@startDateTimeFormatted", startDateTimeFormatted != null ? startDateTimeFormatted : DBNull.Value);
insertCommand.Parameters.AddWithValue("@endDateTimeFormatted", endDateTimeFormatted != null ? endDateTimeFormatted : DBNull.Value);
insertCommand.Parameters.AddWithValue("@daysDuration", daysDuration != null ? daysDuration : DBNull.Value);
insertCommand.Parameters.AddWithValue("@description", description != null ? description : DBNull.Value);
insertCommand.Parameters.AddWithValue("@flowEndDateTimeFormatted", flowEndDateTimeFormatted != null ? flowEndDateTimeFormatted : DBNull.Value);

//执行插入操作
insertCommand.ExecuteNonQuery();
}
}
}

//关闭数据库连接
connection.Close();
}
}
catch (Exception ex)
{
Console.WriteLine("发生异常：" + ex.Message);
}

}

}
else
{
Console.WriteLine("获取响应数据失败");
}

}

}
else
{
count = applylistroot.count;
}

}

//执行完成后更新配置表时间
string configStartDateTime = configEndTime;
DateTime lastEndDate = DateTime.Parse(lastEndTime).AddDays(1);
string configEndDateTime = lastEndDate.ToString("yyyy-MM-dd HH:mm:ss");

lastStartTime = configStartTime;
lastEndTime = configEndTime;

try
{
using (SqlConnection sqlConfigConnection = new SqlConnection(connectionString))
{
sqlConfigConnection.Open();

using (SqlCommand updateConfigCommand = new SqlCommand(sqlConfigUpdate, sqlConfigConnection))
{
updateConfigCommand.Parameters.AddWithValue("configStartTime", configStartDateTime);
updateConfigCommand.Parameters.AddWithValue("configEndTime", configEndDateTime);
updateConfigCommand.Parameters.AddWithValue("@lastStartTime", lastStartTime);
updateConfigCommand.Parameters.AddWithValue("@lastEndTime", lastEndTime);

updateConfigCommand.ExecuteNonQuery();
}

sqlConfigConnection.Close();
}

}
catch (Exception ex)
{
Console.WriteLine("发生异常: " + ex.Message);
}

}
catch (Exception ex)
{
Console.WriteLine("发生异常: " + ex.Message);
}
}

}

//关闭数据库连接
sqlConnection.Close();
}
}
catch (Exception ex)
{
Console.WriteLine("发生异常：" + ex.Message);
}

}

//accessToken
public class TokenResponseWrapper
{
public TokenResponse value { get; set; }
}

public class TokenResponse
{
public string accessToken { get; set; }

public string refreshToken { get; set; }

public long expireTime { get; set; }

public string corporationId { get; set; }
}

//获取单据接口
public class ApplyListRootObject
{
public int count { get; set; }

public ApplyListItem[] items { get; set; }

}

public class ApplyListItem
{
public ApplyListForm form { get; set; }

}

//form表单内数据
public class ApplyListForm
{
//单号
public string code { get; set; }

//申请人
public string submitterId { get; set; }

//申请日期
public long submitDate { get; set; }

//报销部门
public string expenseDepartment { get; set; }

//出差开始时间和结束时间
public ApplyListfeeDatePeriod feeDatePeriod { get; set; }

public string description { get; set; }

//单据完成时间
public long flowEndTime { get; set; }

}

//出差开始时间和结束时间
public class ApplyListfeeDatePeriod
{
public long end { get; set; }

public long start { get; set; }
}

//查询员工接口
public class StaffIdstRootObject
{
public StaffIdsItem[] items { get; set; }

}

public class StaffIdsItem
{
public string name { get; set; }

public string code { get; set; }

}

//获取部门列表接口
public class Department
{
public string id { get; set; }

public string name { get; set; }

public string parentId { get; set; }

}

public class DepartmentReponseData
{
public Department value { get; set; }
}

}

灿烂滴大白牙

关注

7
点赞
踩
3

收藏

觉得还不错? 一键收藏
1
评论
调用接口，将数据插入数据库表，并通过表配置跑某个时间段数据

/“获取单据”接口中的expensedepartment时最后一级部门，它的上级部门是“根据部门ID获取部门信息”接口中的parentid。//Console.WriteLine("过期时间: {0}", expireTime);//主键，不可能为空。//调用“根据部门ID获取部门信息”接口，获取申请人部门。//将long类型的expireTime转换为date类型。//需先循环输出单据列表，start以0为起始值，100条数据为每页查询条数。//调用"查询员工"的接口,获取申请人姓名。
复制链接

扫一扫