ModuleMaster/handlers/getpanelsinfo.ashx - main - ivan/ModMas

ModuleMaster/handlers/getpanelsinfo.ashx
361 строка12 KB
i.krivokon
Initial commit
13 мар 2026, 13:49
13 мар 2026, 13:4900d9fb6
<%@ WebHandler Language="VB" Class="panels_info" %>


Imports sharedClasses.dbFunctions
Imports System.Data
Imports System.Net
Imports System.IO
Imports System.Data.SqlClient


Public Class panels_info : Implements IHttpHandler



    Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
        Dim sqlf As New SqlFunctions
        Dim com As New Data.SqlClient.SqlCommand

        Dim result As Integer
        Dim dt As New DataTable
        Dim dt_doc As New DataTable

        dt = GetPanelInfo()
        dt_doc = GetMaterialList()


        Dim sql As New StringBuilder
        sql.AppendLine("with tmp as ( ")
        sql.AppendLine("select ")
        sql.AppendLine("distinct ")
        sql.AppendLine("doc.mblnr ")
        sql.AppendLine(",doc.zeile ")
        sql.AppendLine(",doc.matnr ")
        sql.AppendLine(",doc.menge ")
        sql.AppendLine(",inf.VENDOR_NAME ")
        sql.AppendLine(",inf.VENDOR_code ")
        sql.AppendLine("from [UserModules].[user].[PANELS_DOCUMENTS_TMP] doc ")
        sql.AppendLine("left join [UserModules].[user].[Panels_info] inf ")
        sql.AppendLine("on doc.mblnr = inf.MBLNR ")
        sql.AppendLine(") ")
        sql.AppendLine("merge [usermodules].[user].[PANELS_MATERIAL_DOCUMENTS] as target ")
        sql.AppendLine("using tmp as source ")
        sql.AppendLine("on target.document_number = source.mblnr ")
        sql.AppendLine("when not matched then ")
        sql.AppendLine("	insert ")
        sql.AppendLine("	(document_number, material, qty, vendor, vendor_code) ")
        sql.AppendLine("	values ")
        sql.AppendLine("	(source.mblnr, source.matnr, source.menge, source.vendor_name, source.vendor_code); ")


        result = sqlf.NonQuery("delete from [UserModules].[user].[Panels_info]")
        result = sqlf.NonQuery("delete from [UserModules].[user].[PANELS_DOCUMENTS_TMP]")
        result = sqlf.NonQuery("delete from [UserModules].[user].[PANELS_MATERIAL_DOCUMENTS]")



        Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SqlFunctions.ConString)
            bulkCopy.DestinationTableName = "[usermodules].[user].[PANELS_DOCUMENTS_TMP]"


            Dim mapID As New _
            SqlBulkCopyColumnMapping("MBLNR", "MBLNR")
            bulkCopy.ColumnMappings.Add(mapID)
            mapID = New _
            SqlBulkCopyColumnMapping("MJAHR", "MJAHR")
            bulkCopy.ColumnMappings.Add(mapID)
            mapID = New _
            SqlBulkCopyColumnMapping("BLDAT", "BLDAT")
            bulkCopy.ColumnMappings.Add(mapID)
            mapID = New _
            SqlBulkCopyColumnMapping("BUDAT", "BUDAT")
            bulkCopy.ColumnMappings.Add(mapID)
            mapID = New _
            SqlBulkCopyColumnMapping("USNAM", "USNAM")
            bulkCopy.ColumnMappings.Add(mapID)
            mapID = New _
            SqlBulkCopyColumnMapping("AEDAT", "AEDAT")
            bulkCopy.ColumnMappings.Add(mapID)
            mapID = New _
            SqlBulkCopyColumnMapping("BKTXT", "BKTXT")
            bulkCopy.ColumnMappings.Add(mapID)
            mapID = New _
            SqlBulkCopyColumnMapping("TCODE2", "TCODE2")
            bulkCopy.ColumnMappings.Add(mapID)
            mapID = New _
            SqlBulkCopyColumnMapping("BLART", "BLART")
            bulkCopy.ColumnMappings.Add(mapID)
            mapID = New _
            SqlBulkCopyColumnMapping("BLAUM", "BLAUM")
            bulkCopy.ColumnMappings.Add(mapID)
            mapID = New _
            SqlBulkCopyColumnMapping("CPUTM", "CPUTM")
            bulkCopy.ColumnMappings.Add(mapID)
            mapID = New _
            SqlBulkCopyColumnMapping("CPUDT", "CPUDT")
            bulkCopy.ColumnMappings.Add(mapID)
            mapID = New _
            SqlBulkCopyColumnMapping("XBLNR", "XBLNR")
            bulkCopy.ColumnMappings.Add(mapID)
            mapID = New _
            SqlBulkCopyColumnMapping("LE_VBELN", "LE_VBELN")
            bulkCopy.ColumnMappings.Add(mapID)
            mapID = New _
            SqlBulkCopyColumnMapping("ZEILE", "ZEILE")
            bulkCopy.ColumnMappings.Add(mapID)
            mapID = New _
            SqlBulkCopyColumnMapping("BWART", "BWART")
            bulkCopy.ColumnMappings.Add(mapID)
            mapID = New _
            SqlBulkCopyColumnMapping("MATNR", "MATNR")
            bulkCopy.ColumnMappings.Add(mapID)
            mapID = New _
            SqlBulkCopyColumnMapping("WERKS", "WERKS")
            bulkCopy.ColumnMappings.Add(mapID)
            mapID = New _
            SqlBulkCopyColumnMapping("LGORT", "LGORT")
            bulkCopy.ColumnMappings.Add(mapID)
            mapID = New _
            SqlBulkCopyColumnMapping("CHARG", "CHARG")
            bulkCopy.ColumnMappings.Add(mapID)
            mapID = New _
            SqlBulkCopyColumnMapping("INSMK", "INSMK")
            bulkCopy.ColumnMappings.Add(mapID)
            mapID = New _
            SqlBulkCopyColumnMapping("MENGE", "MENGE")
            bulkCopy.ColumnMappings.Add(mapID)
            mapID = New _
            SqlBulkCopyColumnMapping("DMBTR", "DMBTR")
            bulkCopy.ColumnMappings.Add(mapID)
            mapID = New _
            SqlBulkCopyColumnMapping("BWTAR", "BWTAR")
            bulkCopy.ColumnMappings.Add(mapID)
            mapID = New _
            SqlBulkCopyColumnMapping("MEINS", "MEINS")
            bulkCopy.ColumnMappings.Add(mapID)
            mapID = New _
            SqlBulkCopyColumnMapping("ERFMG", "ERFMG")
            bulkCopy.ColumnMappings.Add(mapID)
            mapID = New _
            SqlBulkCopyColumnMapping("ERFME", "ERFME")
            bulkCopy.ColumnMappings.Add(mapID)
            mapID = New _
            SqlBulkCopyColumnMapping("SGTXT", "SGTXT")
            bulkCopy.ColumnMappings.Add(mapID)
            mapID = New _
            SqlBulkCopyColumnMapping("KZBEW", "KZBEW")
            bulkCopy.ColumnMappings.Add(mapID)
            mapID = New _
            SqlBulkCopyColumnMapping("EXBWR", "EXBWR")
            bulkCopy.ColumnMappings.Add(mapID)
            mapID = New _
            SqlBulkCopyColumnMapping("SHKZG", "SHKZG")
            bulkCopy.ColumnMappings.Add(mapID)
            mapID = New _
            SqlBulkCopyColumnMapping("WEMPF", "WEMPF")
            bulkCopy.ColumnMappings.Add(mapID)
            result = 1

            Try
                ' Write from the source to the destination.
                bulkCopy.WriteToServer(dt_doc)

            Catch ex As Exception
                Console.WriteLine(ex.Message)
                result = 0
            Finally
                ' Close the SqlDataReader. The SqlBulkCopy
                ' object is automatically closed at the end
                ' of the Using block.
            End Try
        End Using









        Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SqlFunctions.ConString)
            bulkCopy.DestinationTableName = "[usermodules].[user].[Panels_info]"


            Dim mapID As New _
                              SqlBulkCopyColumnMapping("LGNUM", "LGNUM")
            bulkCopy.ColumnMappings.Add(mapID)

            mapID = New _
                              SqlBulkCopyColumnMapping("LGTYP", "LGTYP")
            bulkCopy.ColumnMappings.Add(mapID)

            mapID = New _
                        SqlBulkCopyColumnMapping("LGPLA", "LGPLA")
            bulkCopy.ColumnMappings.Add(mapID)

            mapID = New _
                        SqlBulkCopyColumnMapping("LENUM", "LENUM")
            bulkCopy.ColumnMappings.Add(mapID)

            mapID = New _
                        SqlBulkCopyColumnMapping("MATNR", "MATNR")
            bulkCopy.ColumnMappings.Add(mapID)

            mapID = New _
                        SqlBulkCopyColumnMapping("MAKTX", "MAKTX")
            bulkCopy.ColumnMappings.Add(mapID)

            mapID = New _
                        SqlBulkCopyColumnMapping("MENGE", "MENGE")
            bulkCopy.ColumnMappings.Add(mapID)

            mapID = New _
                        SqlBulkCopyColumnMapping("MEINS", "MEINS")
            bulkCopy.ColumnMappings.Add(mapID)

            mapID = New _
                        SqlBulkCopyColumnMapping("IVAMT", "INVAMT")
            bulkCopy.ColumnMappings.Add(mapID)

            mapID = New _
                        SqlBulkCopyColumnMapping("WAERS", "CURRENCY")
            bulkCopy.ColumnMappings.Add(mapID)

            mapID = New _
                        SqlBulkCopyColumnMapping("INVNO", "INVNO")
            bulkCopy.ColumnMappings.Add(mapID)

            mapID = New _
                        SqlBulkCopyColumnMapping("LIFNR", "VENDOR_CODE")
            bulkCopy.ColumnMappings.Add(mapID)

            mapID = New _
                        SqlBulkCopyColumnMapping("NAME1", "VENDOR_NAME")
            bulkCopy.ColumnMappings.Add(mapID)

            mapID = New _
                        SqlBulkCopyColumnMapping("BOENO", "BOENO")
            bulkCopy.ColumnMappings.Add(mapID)

            mapID = New _
                    SqlBulkCopyColumnMapping("BKTXT", "GR_REFERENCE")
            bulkCopy.ColumnMappings.Add(mapID)

            mapID = New _
                        SqlBulkCopyColumnMapping("INVDT", "INVDT")
            bulkCopy.ColumnMappings.Add(mapID)

            mapID = New _
                        SqlBulkCopyColumnMapping("PRICE", "PRICE")
            bulkCopy.ColumnMappings.Add(mapID)

            mapID = New _
                        SqlBulkCopyColumnMapping("EBELN", "EBELN")
            bulkCopy.ColumnMappings.Add(mapID)

            mapID = New _
                        SqlBulkCopyColumnMapping("EBELP", "EBELP")
            bulkCopy.ColumnMappings.Add(mapID)

            mapID = New _
                        SqlBulkCopyColumnMapping("DISPO", "DISPO")
            bulkCopy.ColumnMappings.Add(mapID)

            mapID = New _
                    SqlBulkCopyColumnMapping("MBLNR", "MBLNR")
            bulkCopy.ColumnMappings.Add(mapID)

            mapID = New _
                    SqlBulkCopyColumnMapping("ZEILE", "ZEIL")
            bulkCopy.ColumnMappings.Add(mapID)

            mapID = New _
               SqlBulkCopyColumnMapping("MJAHR", "MJAH")
            bulkCopy.ColumnMappings.Add(mapID)

            mapID = New _
                SqlBulkCopyColumnMapping("MENGE_ZGTD", "MENGE_ZGTD")
            bulkCopy.ColumnMappings.Add(mapID)
            result = 1

            Try
                ' Write from the source to the destination.
                bulkCopy.WriteToServer(dt)

            Catch ex As Exception
                Console.WriteLine(ex.Message)
                result = 0
            Finally
                ' Close the SqlDataReader. The SqlBulkCopy
                ' object is automatically closed at the end
                ' of the Using block.
            End Try
        End Using

        sqlf.NonQuery(sql.ToString)


        context.Response.ContentType = "content/text"
        If result > 0 Then
            context.Response.Write("ok")
        Else
            context.Response.Write("ng")
        End If
    End Sub

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





    Private Function GetPanelInfo() As DataTable
        Try
            Dim url As String = "http://serkweb.serk.lan:8080/tools/Handlers/getwmsdata.ashx?fname=Z_RFC_SU_GR_GTD&iv_werks=P901&iv_lgnum=R01&iv_dispo=R24&format=json"
            Dim o As DataTable = Newtonsoft.Json.JsonConvert.DeserializeObject(Of DataTable)(GetWMSData(url))
            Return o
        Catch ex As Exception
            Return Nothing
        End Try
    End Function



    Private Function GetMaterialList() As DataTable
        Dim todaysdate As String = String.Format("{0:yyyy-MM-dd}", DateTime.Now)
        Try
            Dim url As String = "http://serkweb.serk.lan:8080/tools/Handlers/getwmsdata.ashx?fname=Z_RFC_MAT_DOCS&mvt=501&plant=P901&post_date=" & todaysdate & "&i_dispo=R24&format=json"
            'Dim url As String = "http://serkweb.serk.lan:8080/tools/Handlers/getwmsdata.ashx?fname=Z_RFC_MAT_DOCS&mvt=501&plant=P901&post_date=2017-05-16&i_dispo=R24&format=json"
            Dim o As DataTable = Newtonsoft.Json.JsonConvert.DeserializeObject(Of DataTable)(GetWMSData(url))
            Return o
        Catch ex As Exception
            Return Nothing
        End Try
    End Function


    Private Function GetWMSData(url As String) As String
        Try
            Dim wb As WebRequest
            wb = WebRequest.Create(url)
            Dim res As WebResponse = wb.GetResponse
            Dim sr As New StreamReader(res.GetResponseStream)
            Return sr.ReadToEnd()
        Catch ex As Exception
            Return ""
        End Try
    End Function








End Class