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

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

Partial Class FI_gerp_fifo
    Inherits System.Web.UI.Page
    Private sqlf As New SqlFunctions
    Private sw As New Diagnostics.Stopwatch

    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        sw.Start()

        If all.Checked Then
            Dim materials As DataTable = sqlf.GetData("SELECT DISTINCT material FROM UserModules.[user].GERP_MB_51_FOR_GTD")
            For Each dr As DataRow In materials.Rows
                processMaterial(dr("material"))
            Next
        ElseIf TextBox1.Text <> "" Then
            For Each r As String In TextBox1.Text.Split(Chr(10))
                If r.Trim.Replace(Chr(13), "") <> "" Then
                    processMaterial(r.Trim.Replace(Chr(13), ""))
                End If
                'lblMEssage.Text &= r & " " & TextBox1.Text.Split(Chr(10)).Count
            Next

        End If
        sw.Stop()
        Response.Write("FINISHED within " & sw.Elapsed.ToString)
    End Sub
    Private Sub processMaterial(material As String)
        sqlf.NonQuery("UPDATE UserModules.[user].GERP_MB_51_FOR_GTD SET REMARK='', REFERENCE_GTD = gtd WHERE gtd is null and material ='" & material & "' ")

        Dim sql As New StringBuilder

        sql.AppendLine("select PLNT, SLOC, " & _
                       "MIN(PSTNG_DATE) as PSTNG_DATE, " & _
                       "MIN(ENTRY_DATE) as ENTRY_DATE, " & _
                       "MIN([time]) as [time], material, " & _
                      "sum(cast(replace(quantity,' ','') as integer)) as quantity, " & _
                      "sum(cast(replace(quantity,' ','') as integer)) as iquantity, " & _
                      "gtd as gtd1, " & _
                      "gtd+'['+pstng_date+']' as gtd, '' as remark ")
        sql.AppendLine("from UserModules.[user].GERP_MB_51_FOR_GTD ")
        sql.AppendLine("where material ='" & material & "' ")
        sql.AppendLine("and gtd is not null ")
        sql.AppendLine("GROUP BY PLNT, SLOC, material, gtd, gtd+'['+pstng_date+']' ") ' ---  One GTD could GR several dates ......
        sql.AppendLine("order by MIN(PSTNG_DATE), MIN(ENTRY_DATE), MIN([time]) ")

        Dim gtdList As DataTable = sqlf.GetData(sql.ToString)

        sql.Clear()
        'Просуммируем по документу-материалу чтобы убрать строки, когда тдин материал несколько раз встречается в документе
        sql.AppendLine("select plnt, sloc, mat__doc_, ")
        sql.AppendLine("PSTNG_DATE, ENTRY_DATE, [time], material, mvt, " & _
                       "SUM(cast(replace(quantity,' ','') as integer)) as quantity, " & _
                       "SUM(cast(replace(quantity,' ','') as integer)) as iquantity, " & _
                       "isnull(gtd,'') as gtd, " & _
                       "isnull(REMARK,'') as remark ")
        sql.AppendLine("from UserModules.[user].GERP_MB_51_FOR_GTD ")
        sql.AppendLine("where material ='" & material & "'  ")
        sql.AppendLine("and gtd is NULL ")
        sql.AppendLine("GROUP BY plnt, sloc, mat__doc_, PSTNG_DATE, ENTRY_DATE, [time], material, mvt, isnull(gtd,''), isnull(REMARK,'') ")
        sql.AppendLine("order by PSTNG_DATE, ENTRY_DATE, [time], mat__doc_, SUM(cast(replace(quantity,' ','') as integer)) ")
        Dim movementList As DataTable = sqlf.GetData(sql.ToString)
        Dim i As Integer = 0

        For Each mvtRow As DataRow In movementList.Rows
            'Dim remainGtd() As DataRow = gtdList.Select("quantity > 0")
            'If remainGtd.Count > 0 Then
            If mvtRow("quantity") < 0 Then 'GI  case
                i = 0
                While (i < gtdList.Rows.Count) And (mvtRow("quantity") < 0)
                    'Checking GTD for same plant and storage location
                    If gtdList.Rows(i)("plnt") = mvtRow("plnt") _
                        And gtdList.Rows(i)("sloc") = mvtRow("sloc") Then
                        If gtdList.Rows(i)("quantity") > 0 Then
                            'This GTD has enough quantity

                            '////////////////////// Проверить - первое кол-во идет без -
                            If (mvtRow("quantity") + gtdList.Rows(i)("quantity")) >= 0 Then
                                If mvtRow("remark") <> "" Then mvtRow("remark") &= "|"
                                mvtRow("remark") &= gtdList.Rows(i)("gtd") & ":" & gtdList.Rows(i)("quantity")

                                gtdList.Rows(i)("quantity") = mvtRow("quantity") + gtdList.Rows(i)("quantity")
                                mvtRow("remark") &= ":" & gtdList.Rows(i)("quantity") & ":" & mvtRow("quantity")

                                If mvtRow("gtd") <> "" Then mvtRow("gtd") &= "|"
                                mvtRow("gtd") &= gtdList.Rows(i)("gtd1")

                                mvtRow("quantity") = 0
                                gtdList.Rows(i)("remark") &= "|" & mvtRow("mat__doc_")
                            Else
                                If mvtRow("remark") <> "" Then mvtRow("remark") &= "|"
                                mvtRow("quantity") += gtdList.Rows(i)("quantity")
                                mvtRow("remark") &= gtdList.Rows(i)("gtd") & ":" & gtdList.Rows(i)("quantity") & ":0:" & gtdList.Rows(i)("quantity")

                                If mvtRow("gtd") <> "" Then mvtRow("gtd") &= "|"
                                mvtRow("gtd") &= gtdList.Rows(i)("gtd1")

                                gtdList.Rows(i)("quantity") = 0
                                gtdList.Rows(i)("remark") &= "|" & mvtRow("mat__doc_")
                            End If
                        End If
                    End If
                    i += 1
                End While
                ''Check if TD was assigned to this movement
                If mvtRow("gtd") = "" Then
                    mvtRow("gtd") = "CAN_NOT_MATCH"
                Else
                    'Mark same material document with matched GTD  -   for movements like 344
                    ' Потому что при перемещении с локации на локацию надо привязать это к номеру ГТД
                    ' Надо добавить номер гтд в спико гтд для локации
                    For Each r In movementList.Select("mat__doc_ = '" & mvtRow("mat__doc_") & "' AND sloc <> '" & mvtRow("sloc") & "'")
                        r("gtd") = mvtRow("gtd")
                        r("remark") = mvtRow("remark")
                    Next
                End If

            Else 'GR or return case
                i = gtdList.Rows.Count - 1
                'Checking if there is already GTD assigned
                If mvtRow("gtd") <> "" Then 'Есть связанные ГТД - трансфер 
                    Dim linkedGTD() As String = mvtRow("remark").split("|")
                    mvtRow("remark") = ""
                    For Each gtd In linkedGTD
                        Dim gtdParams() As String = gtd.Split(":")
                        Dim foundGTD() As DataRow = gtdList.Select("plnt='" & mvtRow("plnt") & "' and sloc='" & mvtRow("sloc") & "' and gtd='" & gtdParams(0) & "'", "PSTNG_DATE desc, ENTRY_DATE desc, [time] desc")
                        If foundGTD.Count > 0 Then
                            'Предполагаем что должна быть только одна гтд с таким номером в текущей локации
                            If mvtRow("remark") <> "" Then mvtRow("remark") &= "|"
                            mvtRow("remark") &= gtdParams(0) & ":" & foundGTD(0)("quantity")
                            foundGTD(0)("quantity") += Math.Abs(Convert.ToInt64(gtdParams(3)))
                            mvtRow("remark") &= ":" & foundGTD(0)("quantity") & ":" & Math.Abs(Convert.ToInt64(gtdParams(3)))
                        Else 'Если нет такой гтд в текущей локации - добавляем ее в список ГТД 
                            If mvtRow("remark") <> "" Then mvtRow("remark") &= "|"
                            mvtRow("remark") &= gtdParams(0) & ":0:" & Math.Abs(Convert.ToInt64(gtdParams(3))) & ":" & Math.Abs(Convert.ToInt64(gtdParams(3)))
                            Dim newGtd = gtdList.NewRow
                            newGtd("plnt") = mvtRow("plnt")
                            newGtd("sloc") = mvtRow("sloc")
                            'newGtd("mat__doc_") = mvtRow("mat__doc_")
                            'newGtd("item") = mvtRow("item")
                            newGtd("pstng_date") = mvtRow("pstng_date")
                            newGtd("entry_date") = mvtRow("entry_date")
                            newGtd("time") = mvtRow("time")
                            newGtd("material") = mvtRow("material")
                            'newGtd("mvt") = mvtRow("mvt")
                            newGtd("quantity") = Math.Abs(Convert.ToInt64(gtdParams(3)))
                            newGtd("iquantity") = Math.Abs(Convert.ToInt64(gtdParams(3)))
                            newGtd("gtd") = gtdParams(0)
                            newGtd("gtd1") = gtdParams(0).Split("[")(0)
                            gtdList.Rows.Add(newGtd)
                        End If
                    Next
                Else 'Нет связанных ГТД - возврат  122 скорее всего или отмена GI
                    While (i >= 0) And (mvtRow("quantity") > 0) 'бежим с конца чтобы найти последнюю использовавшуюся ГТД
                        If gtdList.Rows(i)("plnt") = mvtRow("plnt") And gtdList.Rows(i)("sloc") = mvtRow("sloc") Then
                            If gtdList.Rows(i)("quantity") <> gtdList.Rows(i)("iquantity") Then
                                If (gtdList.Rows(i)("quantity") + mvtRow("quantity")) > gtdList.Rows(i)("iquantity") Then 'Заполнили текущую ГТД и еще осталось на следующую
                                    If mvtRow("remark") <> "" Then mvtRow("remark") &= "|"

                                    mvtRow("remark") &= gtdList.Rows(i)("gtd") & ":" & gtdList.Rows(i)("quantity") & ":" & gtdList.Rows(i)("iquantity") & ":" & gtdList.Rows(i)("iquantity") - gtdList.Rows(i)("quantity")

                                    If mvtRow("gtd") <> "" Then mvtRow("gtd") &= "|"
                                    mvtRow("gtd") &= gtdList.Rows(i)("gtd1")


                                    mvtRow("quantity") -= (gtdList.Rows(i)("iquantity") - gtdList.Rows(i)("quantity"))
                                    gtdList.Rows(i)("quantity") = gtdList.Rows(i)("iquantity")

                                Else  ' Все влезает в текущую ГТД
                                    If mvtRow("remark") <> "" Then mvtRow("remark") &= "|"
                                    mvtRow("remark") &= gtdList.Rows(i)("gtd") & ":" & gtdList.Rows(i)("quantity") '& ":" & mvtRow("quantity")

                                    If mvtRow("gtd") <> "" Then mvtRow("gtd") &= "|"
                                    mvtRow("gtd") &= gtdList.Rows(i)("gtd1")

                                    gtdList.Rows(i)("quantity") += mvtRow("quantity")
                                    mvtRow("remark") &= ":" & gtdList.Rows(i)("quantity") & ":" & mvtRow("quantity")
                                    mvtRow("quantity") = 0
                                End If
                            End If
                        End If
                        i -= 1
                    End While
                    If mvtRow("remark") = "" Then 'Не нашли подходящую ГТД - первый приход в текущей локации. Добавляем ГТД в список
                        Dim newGtd = gtdList.NewRow
                        newGtd("plnt") = mvtRow("plnt")
                        newGtd("sloc") = mvtRow("sloc")
                        'newGtd("mat__doc_") = mvtRow("mat__doc_")
                        'newGtd("item") = mvtRow("item")
                        newGtd("pstng_date") = mvtRow("pstng_date")
                        newGtd("entry_date") = mvtRow("entry_date")
                        newGtd("time") = mvtRow("time")
                        newGtd("material") = mvtRow("material")
                        'newGtd("mvt") = mvtRow("mvt")
                        newGtd("quantity") = Math.Abs(Convert.ToInt64(mvtRow("quantity")))
                        newGtd("iquantity") = Math.Abs(Convert.ToInt64(mvtRow("quantity")))
                        newGtd("gtd") = "NEW_GR_WITH_" & mvtRow("mat__doc_")
                        newGtd("gtd1") = "NEW_GR_WITH_" & mvtRow("mat__doc_")
                        gtdList.Rows.Add(newGtd)
                    End If
                End If
            End If
        Next
        For Each dr As DataRow In movementList.Rows
            sqlf.NonQuery("UPDATE UserModules.[user].GERP_MB_51_FOR_GTD SET REFERENCE_GTD='" & dr("gtd").ToString.Trim("|") & "', REMARK='" & dr("remark").ToString.Trim("|") &
                          "' WHERE mat__doc_ = '" & dr("mat__doc_") & "' and material = '" & dr("material") & "' and sloc='" & dr("sloc") & "' ")
            '"' WHERE ID=" & dr("id"))
        Next

        GridView1.DataSource = movementList
        GridView1.DataBind()

        'GridView2.DataSource = gtdList
        'GridView2.DataBind()


    End Sub
End Class