ModuleMaster/fi/giwmsrfc.aspx.vb - main - ivan/ModMas

ModuleMaster/fi/giwmsrfc.aspx.vb
196 строк11 KB
i.krivokon
Initial commit
13 мар 2026, 13:49
13 мар 2026, 13:4900d9fb6
Imports sharedClasses
Imports sharedClasses.dbFunctions
Imports System.Data

Partial Class FI_giwmsrfc
    Inherits System.Web.UI.Page

    Private sqlf As New SqlFunctions

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        matnr.Text = matnr.Text.Trim.ToUpper
    End Sub

    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        message.Text = ""
        Dim materials() As String = matnr.Text.Split(Chr(13))
        Dim tts As DateTime = DateTime.Now
        For Each material As String In materials
            material = material.Trim
            If material <> "" Then
                Dim ts As DateTime = DateTime.Now
                Dim dt As DataTable = GetWMSData(material, mvt.Text, start_date.Text.Replace("-", ""), end_date.Text.Replace("-", ""), stloc.Text)
                Dim te As DateTime = DateTime.Now
                message.Text &= material & " : " & uploadDataTableToDB(dt, "UserModules.[user].WMS_GI_INFORMATION", material, mvt.Text.Trim.ToUpper) & " Duration: " & te.Subtract(ts).TotalSeconds & " seconds <br/>"
                'GridView1.DataSource = dt
                'GridView1.DataBind()
            End If
        Next
        Dim sql As New StringBuilder
        sql.AppendLine("UPDATE UserModules.[user].WMS_GI_INFORMATION ")
        sql.AppendLine("SET UserModules.[user].WMS_GI_INFORMATION.EXCH__RATE__RUB = UserModules.[user].CURRENCY_EXCHANGE_RATE.curs ")
        sql.AppendLine("FROM ")
        sql.AppendLine("	UserModules.[user].WMS_GI_INFORMATION ")
        sql.AppendLine("LEFT JOIN UserModules.[user].CURRENCY_EXCHANGE_RATE ")
        sql.AppendLine("ON rtrim( ")
        sql.AppendLine("	UserModules.[user].WMS_GI_INFORMATION.IV_DATE ")
        sql.AppendLine(") = UserModules.[user].CURRENCY_EXCHANGE_RATE.[DATE] ")
        sql.AppendLine("AND rtrim( ")
        sql.AppendLine("	UserModules.[user].WMS_GI_INFORMATION.CURR ")
        sql.AppendLine(") = UserModules.[user].CURRENCY_EXCHANGE_RATE.CODE ")
        sqlf.NonQuery(sql.ToString)
        Dim tte As DateTime = DateTime.Now
        message.Text &= "Total time: " & tte.Subtract(tts).TotalSeconds & " sec (" & tte.Subtract(tts).TotalMinutes & " min)"
    End Sub

    Private Function GetWMSData(material As String, mvt As String, startDate As String, endDate As String, sLoc As String) As DataTable
        'Dim r As New sharedClasses.GetData.rfc
        'Dim params As New Hashtable
        'params.Add("WERKS", "P901")
        'params.Add("LGORT", sLoc)
        'params.Add("MATNR", material)
        'params.Add("BWART", mvt)
        ''params.Add("AUFNR", "010094131254") ' Optional PO  number
        'params.Add("START_DATE", startDate)
        'params.Add("END_DATE", endDate)
        ''Return r.wmsData("ZINV_TRACE_RFC", params, "PROD")
    End Function

    Private Function uploadDataTableToDB(data As DataTable, tableName As String, material As String, movementType As String) As String
        If Not IsNothing(data) Then
            Dim sql As New StringBuilder
            Dim fieldList As New StringBuilder
            Dim paramsList As New StringBuilder
            Dim fields As New SortedSet(Of String)
            sql.Append("INSERT INTO " & tableName & " (")
            For Each col As DataColumn In data.Columns
                If fieldList.Length > 0 Then
                    fieldList.Append(",")
                    paramsList.Append(",")
                End If
                fieldList.Append(col.Caption)
                paramsList.Append("@" & col.Caption)
                fields.Add(col.Caption)
            Next

            ' From  VI_GTD_VS_INVOICE
            fieldList.Append(",MODEL, QUANTITY, MATERIAL, VENDOR, DESCRIPTION, INVOICE_QTY, GTD, MVT, ORDER_DATE")
            paramsList.Append(",@MODEL, @QUANTITY, @MATERIAL, @VENDOR, @DESCRIPTION, @INVOICE_QTY, @GTD, @MVT, @ORDER_DATE")
            ' From Invoice list
            fieldList.Append(",VENDOR_DESCRIPTION, HOUSE_BL, CURR, DELIVERY, POSTING_dATE, UNIT_PRICE, INVOICE_AMNT, PURCHASER, IV_DATE")
            paramsList.Append(",@VENDOR_DESCRIPTION, @HOUSE_BL, @CURR, @DELIVERY, @POSTING_dATE, @UNIT_PRICE, @INVOICE_AMNT, @PURCHASER, @IV_DATE")
            'From Material price
            fieldList.Append(", VALUATION_TYPE, COMM_CODE, UOM, CURR_195, PRICE_UNIT, MAP")
            paramsList.Append(", @VALUATION_TYPE, @COMM_CODE, @UOM, @CURR_195, @PRICE_UNIT, @MAP")


            sql.Append(fieldList.ToString)
            sql.Append(") VALUES (")
            sql.Append(paramsList.ToString)
            sql.Append(")")

            'Return sql.ToString
            Dim com As New SqlClient.SqlCommand()
            Dim cntOk As Integer = 0
            Dim cntNg As Integer = 0
            For Each dr As DataRow In data.Rows
                com.CommandText = sql.ToString
                With com.Parameters
                    .Clear()
                    For Each f In fields
                        .AddWithValue(f, dr(f))
                    Next
                    .AddWithValue("MATERIAL", material)
                    .AddWithValue("MVT", movementType)
                    ''Model and PO quantity from GMES  by PO number
                    Dim gmesInfo As DataRow = sqlf.GetRow("select * from openquery(GMES20, " & _
                                                          "'select model_number, plan_qty, order_date from VWM_QMS_PO_INFO " & _
                                                            " where po_number = ''" & dr("AUFNR") & "''')")
                    If Not IsNothing(gmesInfo) Then
                        .AddWithValue("MODEL", gmesInfo("MODEL_NUMBER"))
                        .AddWithValue("QUANTITY", gmesInfo("PLAN_QTY"))
                        .AddWithValue("ORDER_DATE", gmesInfo("ORDER_DATE").ToString.Insert(4, "-").Insert(7, "-"))
                    Else
                        .AddWithValue("MODEL", "")
                        .AddWithValue("QUANTITY", "")
                        .AddWithValue("ORDER_DATE", dr("BUDAT").ToString.Insert(4, "-").Insert(7, "-"))
                    End If
                    'Vendor and GTD information from GTD list list by Invoice and material
                    Dim gtdinfo As DataRow = sqlf.GetRow("select vendor, material_description, " & _
                                                         " qty as invoice_qty, gtd  from [UserModules].[Dbo].[VI_GTD_VS_INVOICE] " & _
                                                         " where material = '" & material & "' and INV = '" & dr("INV") & "'")
                    If Not IsNothing(gtdinfo) Then
                        .AddWithValue("VENDOR", gtdinfo("VENDOR"))
                        .AddWithValue("DESCRIPTION", gtdinfo("MATERIAL_DESCRIPTION"))
                        .AddWithValue("INVOICE_QTY", gtdinfo("INVOICE_QTY"))
                        .AddWithValue("GTD", gtdinfo("GTD"))
                    Else
                        .AddWithValue("VENDOR", "")
                        .AddWithValue("DESCRIPTION", "")
                        .AddWithValue("INVOICE_QTY", "")
                        .AddWithValue("GTD", "")
                    End If
                    ''Vendor and delivery detail , invoice amount from invoice list by material and invoice number
                    Dim invoiceInfo As DataRow = sqlf.GetRow("select name_1,houseb_l_number, curr_, delivery, pstng_date, " & _
                                                             "unit_price, invoice_am, purchaser, iv_date from usermodules.[user].invoice_list " & _
                                                             "where material = '" & material & "' and INVOICE_NO = '" & dr("INV") & "'")
                    If IsNothing(invoiceInfo) Then
                        .AddWithValue("VENDOR_DESCRIPTION", "")
                        .AddWithValue("HOUSE_BL", "")
                        .AddWithValue("CURR", "")
                        .AddWithValue("DELIVERY", "")
                        .AddWithValue("POSTING_DATE", "")
                        .AddWithValue("UNIT_PRICE", "")
                        .AddWithValue("INVOICE_AMNT", "")
                        .AddWithValue("PURCHASER", "")
                        .AddWithValue("IV_DATE", "")
                    Else
                        .AddWithValue("VENDOR_DESCRIPTION", invoiceInfo("NAME_1"))
                        .AddWithValue("HOUSE_BL", invoiceInfo("HOUSEB_L_NUMBER"))
                        .AddWithValue("CURR", invoiceInfo("CURR_"))
                        .AddWithValue("DELIVERY", invoiceInfo("DELIVERY"))
                        .AddWithValue("POSTING_DATE", invoiceInfo("PSTNG_DATE"))
                        .AddWithValue("UNIT_PRICE", invoiceInfo("UNIT_PRICE"))
                        .AddWithValue("INVOICE_AMNT", invoiceInfo("INVOICE_AM"))
                        .AddWithValue("PURCHASER", invoiceInfo("PURCHASER"))
                        .AddWithValue("IV_DATE", invoiceInfo("IV_DATE"))
                    End If
                    'From material proce
                    Dim priceInfo As DataRow = sqlf.GetRow("select valuation_type,  " & _
                                                            "comm__imp__code_no_, base_unit_of_measure, currency, price_unit, " & _
                                                            "moving_price from usermodules.[user].material_price where material = '" & material & "'")
                    If IsNothing(priceInfo) Then
                        .AddWithValue("VALUATION_TYPE", "")
                        .AddWithValue("COMM_CODE", "")
                        .AddWithValue("UOM", "")
                        .AddWithValue("CURR_195", "")
                        .AddWithValue("PRICE_UNIT", "")
                        .AddWithValue("MAP", "")
                    Else
                        .AddWithValue("VALUATION_TYPE", priceInfo("valuation_type"))
                        .AddWithValue("COMM_CODE", priceInfo("comm__imp__code_no_"))
                        .AddWithValue("UOM", priceInfo("base_unit_of_measure"))
                        .AddWithValue("CURR_195", priceInfo("currency"))
                        .AddWithValue("PRICE_UNIT", priceInfo("price_unit"))
                        .AddWithValue("MAP", priceInfo("moving_price"))
                    End If
                End With
                If sqlf.NonQuery(com) = 1 Then
                    cntOk += 1
                Else
                    com.CommandText = com.CommandText.Replace("WMS_GI_INFORMATION", "WMS_GI_INFORMATION_TMP")
                    sqlf.NonQuery(com)
                    cntNg += 1
                End If

            Next
            Return "Uploaded: " & cntOk & " Failed: " & cntNg
        Else
            Return -1
        End If
    End Function

    Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)

    End Sub
End Class