ModuleMaster/batch/getrfcdata.ashx - main - ivan/ModMas

ModuleMaster/batch/getrfcdata.ashx
138 строк6 KB
i.krivokon
Initial commit
13 мар 2026, 13:49
13 мар 2026, 13:4900d9fb6
<%@ WebHandler Language="VB" Class="getRfcData" %>

Imports System
Imports System.Web
Imports System.Data
Imports Newtonsoft.Json
Imports System.Net
Imports System.IO
Imports sharedClasses


Public Class getRfcData : Implements IHttpHandler

    Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
        Dim sqlf As New dbFunctions.SqlFunctions
        Dim mode As String = IIf(IsNothing(context.Request.QueryString("mode")), "MATERIAL", context.Request.QueryString("mode"))
        mode = mode.ToUpper
        If mode = "MATERIAL" Then
            Dim lastInterfaceDate As String = sqlf.GetScalar("select max(replace(convert(varchar,updated,102),'.','')) from [WMSData].dbo.TB_MATERIAL_MASTER")
            If Not IsNothing(lastInterfaceDate) Then
                Dim dt As DataTable = GetMaterialMaster(lastInterfaceDate)

                sqlf.NonQuery("TRUNCATE TABLE WMSData.dbo.TB_MATERIAL_MASTER_TEMP")

                Dim batchCount As Integer = 1000
                Dim count As Integer = 1
                Dim cnt As Integer = 0
                Dim sql As String = "INSERT INTO WMSData.dbo.TB_MATERIAL_MASTER_TEMP VALUES "
                Dim s As String = ""
                Dim response As New StringBuilder
                response.AppendLine("Last update date: " & lastInterfaceDate)

                If Not IsNothing(dt) Then
                    For Each dr As DataRow In dt.Rows
                        cnt += 1
                        If count > batchCount Then
                            response.AppendLine("Step: " & cnt & " / Inserted:" & sqlf.NonQuery(sql & s) & " / Exception" & sqlf.lastException)
                            count = 1
                            s = ""
                        End If
                        If s <> "" Then s &= ","
                        s &= "('" & dr("MATNR") & "','" &
                                dr("WERKS") & "','" &
                                dr("MAKTX").ToString.Replace("'", "") & "','" &
                                dr("ZZPURCHASER_ID") & "','" &
                                dr("GITYPE") & "','" &
                                dr("PSZGI") & "','" &
                                dr("DISPO") & "', GETDATE())"
                        count += 1
                    Next
                End If
                If s <> "" Then
                    response.AppendLine("Step: " & cnt & " (Last) / Inserted:" & sqlf.NonQuery(sql & s) & " / Exception" & sqlf.lastException)
                End If
                response.AppendLine("")
                context.Response.ContentType = "text/plain"

                context.Response.Write(response.ToString)
                context.Response.Write("Total rows received: " & dt.Rows.Count & " / Processed:" & cnt)

                dt = Nothing

                Dim Query As New StringBuilder
                Query.AppendLine("MERGE WMsData.dbo.TB_MATERIAL_MASTER as trg ")
                Query.AppendLine("USING WMsData.dbo.TB_MATERIAL_MASTER_TEMP as src ")
                Query.AppendLine("on trg.MATNR = SRC.MATNR and trg.WERKS = SRC.WERKS ")
                Query.AppendLine("WHEN MATCHED THEN UPDATE ")
                Query.AppendLine("SET trg.ZZPURCHASER_ID = src.ZZPURCHASER_ID ")
                Query.AppendLine(", trg.GITYPE = src.GITYPE ")
                Query.AppendLine(", trg.PSZGI = src.PSZGI ")
                Query.AppendLine(", trg.DISPO = src.DISPO ")
                Query.AppendLine(", trg.UPDATED = getdate() ")
                Query.AppendLine("WHEN NOT MATCHED BY TARGET THEN ")
                Query.AppendLine("INSERT (MATNR, WERKS, MAKTX, ZZPURCHASER_ID, GITYPE, PSZGI, DISPO, UPDATED) ")
                Query.AppendLine("VALUES (SRC.MATNR, SRC.WERKS, SRC.MAKTX, SRC.ZZPURCHASER_ID, SRC.GITYPE, SRC.PSZGI, SRC.DISPO, getdate()); ")

                sqlf.NonQuery(Query.ToString)
            Else
                context.Response.ContentType = "text/plain"
                context.Response.Write("Can not get last update date.")
            End If
        ElseIf mode = "STBINSTATUS" Then
            context.Response.ContentType = "text/plain"
            context.Response.Write("Interfaced rows: " & CommonFunctions.UploadDataTableToDB("[WMSData]", "dbo", "TB_STBIN_STATUS", GetStBinStatus(), True))
            sqlf.NonQuery("UPDATE [WMSData].dbo.TB_STBIN_STATUS SET Updated = getdate() ")
        End If

    End Sub

    Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
        Get
            Return False
        End Get
    End Property

    Private Function GetMaterialMaster(Optional lastChangeDate As String = "") As DataTable
        Dim wb As WebRequest
        Dim url As String = ""
        If lastChangeDate <> "" Then
            url = "http://serkweb.serk.lan:8080/tools/Handlers/getwmsdata.ashx?fname=Z_RFC_MAT_MASTER&format=json&mode=param&LAST_CHANGE_DATETIME=" & lastChangeDate
        Else
            url = "http://serkweb.serk.lan:8080/tools/Handlers/getwmsdata.ashx?fname=Z_RFC_MAT_MASTER&format=json"
        End If
        wb = WebRequest.Create(url)
        Dim res As WebResponse = wb.GetResponse
        Dim sr As New StreamReader(res.GetResponseStream)
        Dim json As String = sr.ReadToEnd()
        Dim dt As DataTable
        Try
            dt = JsonConvert.DeserializeObject(Of DataTable)(json)
            Return dt
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Private Function GetStBinStatus(Optional lastChangeDate As String = "") As DataTable
        Dim wb As WebRequest
        Dim url As String = ""
        'If lastChangeDate <> "" Then
        '    url = "http://serkweb.serk.lan:8080/tools/Handlers/getwmsdata.ashx?fname=Z_RFC_STBIN_STATUS&format=json&mode=param&LAST_CHANGE_DATETIME=" & lastChangeDate
        'Else
        url = "http://serkweb.serk.lan:8080/tools/Handlers/getwmsdata.ashx?fname=Z_RFC_STBIN_STATUS&format=json"
        'End If
        wb = WebRequest.Create(url)
        Dim res As WebResponse = wb.GetResponse
        Dim sr As New StreamReader(res.GetResponseStream)
        Dim json As String = sr.ReadToEnd()
        Dim dt As DataTable
        Try
            dt = JsonConvert.DeserializeObject(Of DataTable)(json)
            Return dt
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

End Class