HF-MIS-SYS--LEEHSIEHJUI/HFretriveData/Form1.vb

Imports System.Net.Http
Imports System.Data.SqlClient
Imports Newtonsoft.Json.Linq
Imports System.Timers
Imports System.Text
Imports System.Data.Common

Public Class Form1
    ' API 密钥和 Web API 的 URL
    Private apiToken As String = "291A17F2-C8A0-438A-BF64-0834C1B9C1D6"
    Private apiUrl As String = "http://hf.fjkscloud.cn/Api2/AccountReportList"

    Private connectionString As String = "Server=192.168.110.164;Database=rt2021;User Id=sa;Password=Lafayette11@"
    ' 定时器，每隔5分钟执行一次
    Private timer As New Timer(60000) ' 300000毫秒即5分钟

    ' 在 Form Load 事件中设置定时器
    Private Async Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ' 设置定时器触发的事件

        'Dim requestBody As String = "{""Begin"":""2024-12-16"",""End"":""2024-12-16""}"
        'Dim response As String = Await PostApiDataAsync(requestBody)

        'If Not String.IsNullOrEmpty(response) Then
        '    SaveDataToDatabase(response)
        'End If

        Using connection As New SqlConnection(connectionString)
            connection.Open()
            Dim sql As String = "select top(1) createtime from ht_cloud_workshop order by createtime desc "
            Using command As New SqlCommand(sql, connection)
                TextBox1.Text = command.ExecuteScalar()
            End Using

        End Using




        '  AddHandler timer.Elapsed, AddressOf TimerElapsed
        ' 启动定时器
        'timer.Start()
    End Sub
    ' 定时器触发时的事件

    ' 异步调用 Web API 获取数据
    Private Async Function PostApiDataAsync(requestBody As String) As Task(Of String)
        Using client As New HttpClient()
            Try
                ' 设置 Header
                client.DefaultRequestHeaders.Add("KsApiToken", apiToken)

                ' 设置请求内容
                Dim content As New StringContent(requestBody, Encoding.UTF8, "application/json")

                ' 发送 POST 请求
                Dim response As HttpResponseMessage = Await client.PostAsync(apiUrl, content)

                response.EnsureSuccessStatusCode()
                Dim responseBody As String = Await response.Content.ReadAsStringAsync()
                Return responseBody

            Catch ex As Exception
                MessageBox.Show("请求失败: " & ex.Message)
                Return String.Empty
            End Try
        End Using
    End Function

    ' 保存数据到本地 SQL Server 数据库
    ' 保存数据到本地 SQL Server 数据库
    Private Sub SaveDataToDatabase(jsonResponse As String)
        Try
            ' 解析 JSON 数据

            ' MessageBox.Show(jsonResponse)

            Dim json As JObject = JObject.Parse(jsonResponse)

            ' 确保您知道 JSON 的具体结构，假设数据位于 "data" 字段中
            Dim resultArray As JArray = json("result")

            Using connection As New SqlConnection(connectionString)
                connection.Open()

                For Each item As JObject In resultArray
                    ' 假设字段包括 accountName, orderNumber, productName
                    Dim accountName As String = item("accountName").ToString()
                    Dim workshopName As String = item("workshopName").ToString() '车间
                    Dim orderNumber As String = item("orderNumber").ToString() '卡号
                    Dim orderName As String = item("orderName").ToString() '品名
                    Dim createTime As String = item("createTime").ToString() '时间
                    Dim remark As String = item("remark").ToString() '备注
                    Dim procedureName As String = item("procedureName").ToString() '工序

                    ' 插入数据到数据库
                    Dim query As String = "INSERT INTO ht_cloud_workshop (AccountName, workshopName, orderNumber,orderName,createTime,remark,procedureName) VALUES (@AccountName, @workshopName, @orderNumber,@orderName,@createTime,@remark,@procedureName)"
                    Using command As New SqlCommand(query, connection)
                        command.Parameters.AddWithValue("@AccountName", accountName)
                        command.Parameters.AddWithValue("@workshopName", workshopName)
                        command.Parameters.AddWithValue("@orderNumber", orderNumber)
                        command.Parameters.AddWithValue("@orderName", orderName)
                        command.Parameters.AddWithValue("@createTime", createTime)
                        command.Parameters.AddWithValue("@remark", remark)
                        command.Parameters.AddWithValue("@procedureName", procedureName)
                        command.ExecuteNonQuery()
                    End Using
                Next
            End Using

            MessageBox.Show("数据保存成功！")
        Catch ex As Exception
            MessageBox.Show("保存数据失败: " & ex.Message)
        End Try
    End Sub


    Private Async Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        ' 定义日期变量
        Dim beginDate As String = String.Format("{0:d}", dtp1.Value)
        Dim endDate As String = String.Format("{0:d}", dtp2.Value)

        ' 创建JSON请求字符串
        Dim requestBody As String = $"{{""Begin"":""{beginDate}"",""End"":""{endDate}""}}"




        Dim requestBody1 As String = "{""Begin"":""2024-12-20"",""End"":""2024-12-21""}"



        ' 调用 API 并获取返回数据
        Dim response As String = Await PostApiDataAsync(requestBody)

        ' 将数据保存到本地数据库
        If Not String.IsNullOrEmpty(response) Then

            Using connection As New SqlConnection(connectionString)
                connection.Open()

                Dim sql As String = "delete from ht_cloud_workshop where cast(createtime as date ) between '" & String.Format("{0:d}", dtp1.Value) & "' and '" & String.Format("{0:d}", dtp2.Value) & "'"
                Using cmd As New SqlCommand
                    cmd.Connection = connection
                    cmd.CommandText = sql
                    cmd.ExecuteNonQuery()
                End Using



            End Using









            SaveDataToDatabase(response)



            Using connection As New SqlConnection(connectionString)
                connection.Open()
                Dim sql As String = "select top(1) createtime from ht_cloud_workshop order by createtime desc "
                Using command As New SqlCommand(sql, connection)
                    TextBox1.Text = command.ExecuteScalar()
                End Using

            End Using



        End If
    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

        Using connection As New SqlConnection(connectionString)
            connection.Open()



            Dim sql As String = "select createtime as 日期,ordernumber as 卡号, ordername as 品名, procedurename as 工序,remark as 备注  from ht_cloud_workshop where createtime between '" & String.Format("{0:d}", dtp1.Value) & "' and '" & String.Format("{0:d}", dtp2.Value) & "'   order by createtime "





            Using command As New SqlCommand(sql, connection)

                TextBox1.Text = command.ExecuteScalar()

                Dim dt As New DataTable

                Dim da As  DataAdapter









            End Using

        End Using

    End Sub
End Class