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

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

Partial Class FI_fifo_gi
    Inherits System.Web.UI.Page

    Private sqlf As New SqlFunctions

    Private Sub BuildInvoiceInfo(gtd As String, inv As String, material As String)
        Dim sql As New StringBuilder
        sql.AppendLine("select TMP.gtd as [GTD], tmp.material as [Material], tmp.invoice as [Invoce], tmp.vendor as [Vendor], tmp.price as [Price], tmp.invoice_qty as [Invoice Qty],  ")
        sql.AppendLine("(select sum( cast (MENGE as decimal(18,0)) ) from UserModules.[user].WMS_GR_LIST where XBLNR = TMP.Invoice and matnr = TMP.Material and LGORT='RC01') as [GR Qty], ")
        'sql.AppendLine("tmp.invoice_amnt,")
        sql.AppendLine("(select distinct(MBLNR) from UserModules.[user].WMS_GR_LIST where XBLNR = TMP.Invoice and matnr = TMP.MAterial and LGORT='RC01') as GR_Doc, ")
        sql.AppendLine("(select distinct(BUDAT) from UserModules.[user].WMS_GR_LIST where XBLNR = TMP.Invoice and matnr = TMP.MAterial and LGORT='RC01') as GR_Date, ")
        sql.AppendLine("(select sum(case SHKZG WHEN 'H' then (-MENGE) WHEN 'S' then (MENGE) end ) from UserModules.[user].WMS_GR_LIST ")
        sql.AppendLine("WHERE CPUDT+CPUTM < (select distinct CPUDT+CPUTM from UserModules.[user].WMS_GR_LIST where XBLNR = TMP.Invoice and matnr = TMP.Material) AND MATNR = TMP.Material and LGORT='RC01' ")
        sql.AppendLine(") as Stock, '' as [GI Start date], '' as [GI End date], '' as [GI Qty], '' as [Material docs], '' as [Print] , ''  as [Docs prior GI] ")
        sql.AppendLine("from UserModules.[user].TMP_GTD___MATERIAL_LIST TMP WHERE 1=1 ")
        If gtd <> "" Then
            sql.AppendLine("AND tmp.gtd like  '%" & gtd & "%' ")
        End If
        If inv <> "" Then
            sql.AppendLine("AND tmp.invoice like  '%" & inv & "%' ")
        End If
        If material <> "" Then
            sql.AppendLine("AND tmp.material like '%" & material & "%' ")
        End If
        sql.AppendLine("ORDER by TMP.gtd ")

        invoiceinfo.DataSource = sqlf.GetData(sql.ToString)
        invoiceinfo.DataBind()
    End Sub

    Private Class materialDoc
        Public docNumber As String
        Public postingDate As String
        Public mvt As String
        Public qty As Integer
    End Class

    Private Class materialInfoRec
        Public material As String
        Public materialDoc As String
        Public postingDate As String
        Public stockPart As Integer ' If partial GI - from dufferent invoices in one doc - held given invoice part
        Public stock As Integer
        Public materialDocs As Collection
        Public ReadOnly Property giQty As Integer
            Get
                Return getGiQty()
            End Get
        End Property

        Public Sub New()
            material = ""
            materialDoc = ""
            postingDate = ""
            stockPart = 0
            stock = 0
            materialDocs = New Collection
        End Sub

        Private Function getGiQty() As Integer
            Dim s As Integer = 0
            For Each item As materialDoc In materialDocs
                s += item.qty
            Next
            Return s
        End Function
    End Class

    Private Function getDocumentList(info As materialInfoRec) As DataTable
        Dim sql As New StringBuilder
        Sql.AppendLine("select mblnr, budat, bwart, cpudt+cputm as docDateTime,")
        Sql.AppendLine("sum(case SHKZG WHEN 'H' then (-MENGE) WHEN 'S' then (MENGE) end ) as qty ")
        Sql.AppendLine("from UserModules.[user].WMS_GR_LIST ")
        sql.AppendLine("where matnr = '" & info.material & "' and budat >= '" & info.postingDate & "' ")
        Sql.AppendLine("group by mblnr, budat, bwart,  cpudt+cputm ")
        Sql.AppendLine("having sum(case SHKZG WHEN 'H' then (-MENGE) WHEN 'S' then (MENGE) end ) < 0 ")
        Sql.AppendLine("order by cpudt+cputm, mblnr ")
        Return sqlf.GetData(sql.ToString)
    End Function

    Private Function defineGIDate(info As materialInfoRec, countOnly261 As Boolean) As materialInfoRec
        Dim result As New materialInfoRec

        Dim sql As New StringBuilder
        Dim dt As DataTable = getDocumentList(info)

        Dim curStock As Integer = info.stock
        Dim prevStock As Integer = info.stock
        For Each dr As DataRow In dt.Rows
            If IsNumeric(dr("qty")) Then
                Dim qty As Integer = Convert.ToInt16(dr("qty"))
                If (Not countOnly261) Or (countOnly261 And dr("bwart") = "261") Then
                    result.materialDocs.Add(New materialDoc With {.docNumber = dr("mblnr"),
                                                                   .postingDate = dr("budat"),
                                                                   .mvt = dr("bwart"),
                                                                   .qty = qty})
                    curStock += qty
                    If curStock <= 0 Then
                        result.materialDoc = dr("mblnr")
                        result.postingDate = dr("budat")
                        result.stockPart = curStock + prevStock
                        Return result
                    End If
                End If
                prevStock = curStock
            End If
        Next
        Return result
    End Function

    Protected Sub Print(sender As ImageButton, e As ImageClickEventArgs)
        Dim params As Hashtable
        If IsNothing(Session("params")) Then
            params = New Hashtable
        Else
            params = Session("params")
        End If
        Dim paramId As String = Guid.NewGuid.ToString
        params.Add(paramId, sender.Attributes("params"))
        Session.Add("params", params)
        ClientScript.RegisterStartupScript(Me.GetType, "a", "<script>window.open('prntl2.aspx?form=GI_BY_MATERILA_DOC&paramid=" & paramId & "')</script>")
    End Sub

    Private Class gtdAndMaterialInfo
        Public ReadOnly Property infoId As String
            Get
                Return gtd & "|" & material
            End Get
        End Property
        Public gtd As String = ""
        Public material As String = ""
        Public ttlInvoiceQty As Integer = 0
        Public materialDocsAndQty As SortedDictionary(Of String, Integer)  'Hashtable
        Public Sub New()
            materialDocsAndQty = New SortedDictionary(Of String, Integer) ' New Hashtable
        End Sub
    End Class

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load

        invoiceinfo.Visible = showDetailsInfo.Checked

        BuildInvoiceInfo(gtd.Text, invoice.Text, Material.Text)

        Dim docListByMaterialAndGTD As New Hashtable

        For Each gvr As GridViewRow In invoiceinfo.Rows
            If gvr.RowType = DataControlRowType.DataRow Then
                Dim l As New Literal With {.Text = ""}
                Dim ll As New Literal With {.Text = ""}
                Try
                    Dim info As New materialInfoRec
                    info.material = gvr.Cells(1).Text
                    info.postingDate = gvr.Cells(8).Text
                    info.materialDoc = gvr.Cells(7).Text
                    info.stock = gvr.Cells(9).Text

                    Dim giStart As materialInfoRec = defineGIDate(info, False)

                    gvr.Cells(10).Text = giStart.postingDate

                    For Each item As materialDoc In giStart.materialDocs
                        ll.Text &= "<br>" & item.docNumber & " : " & item.mvt & " : " & item.postingDate & " : " & item.qty
                    Next
                    '-------------------------------------------

                    info.postingDate = giStart.postingDate
                    info.stock = gvr.Cells(5).Text
                    info.materialDoc = ""

                    giStart = defineGIDate(info, countOnly261.Checked)

                    gvr.Cells(12).Text = giStart.giQty
                    gvr.Cells(11).Text = giStart.postingDate
                    '/-------------------------------------------------------------------/
                    Dim inf As New gtdAndMaterialInfo
                    inf.gtd = "" 'gvr.Cells(0).Text
                    inf.material = gvr.Cells(1).Text
                    inf.ttlInvoiceQty = gvr.Cells(5).Text

                    If docListByMaterialAndGTD.Contains(inf.infoId) Then
                        docListByMaterialAndGTD(inf.infoId).ttlinvoiceqty += gvr.Cells(5).Text
                    Else
                        docListByMaterialAndGTD.Add(inf.infoId, inf)
                    End If
                    '/-------------------------------------------------------------------/
                    For Each item As materialDoc In giStart.materialDocs
                        If Not docListByMaterialAndGTD(inf.infoId).materialdocsandqty.containskey(item.docNumber) Then
                            docListByMaterialAndGTD(inf.infoId).materialdocsandqty.add(item.docNumber, item.qty)
                        End If

                        l.Text &= "<br>" & item.docNumber & " : " & item.mvt & " : " & item.postingDate & " : " & item.qty
                    Next

                    gvr.Cells(12).Style.Add("font-weight", "bold")
                    gvr.Cells(5).Style.Add("font-weight", "bold")

                    If Convert.ToInt16(gvr.Cells(12).Text) + Convert.ToInt16(gvr.Cells(5).Text) > 0 Then
                        gvr.Style.Add("background-color", "coral")
                    End If

                    'If countOnly261.Checked Then
                    Dim img As New ImageButton With {.ImageUrl = "~/Images/Buttons/24/Print.png", .ToolTip = "Print"}
                    img.Attributes.Add("params", l.Text)
                    img.Enabled = countOnly261.Checked
                    AddHandler img.Click, AddressOf Print
                    gvr.Cells(14).Controls.Add(img)
                    'End If

                Catch ex As Exception
                    l.Text &= "<br>" & gvr.Cells(0).Text & "/" & gvr.Cells(2).Text & ":" & ex.Message
                End Try
                Dim d As New Panel With {.Height = Unit.Pixel(100)}
                d.Style.Add("overflow", "scroll")
                d.Controls.Add(l)
                gvr.Cells(13).Controls.Add(d)


                Dim dd As New Panel With {.Height = Unit.Pixel(100)}
                dd.Style.Add("overflow", "scroll")
                dd.Controls.Add(ll)

                gvr.Cells(15).Controls.Add(dd)

                If gvr.Cells(5).Text <> gvr.Cells(6).Text Then
                    gvr.Cells(6).Style.Add("color", "red")
                End If
            End If
        Next
        '--------------------------------------------------------------------
        Dim tbl As New HtmlTable
        tbl.Style.Add("width", "600px")
        Dim th As New HtmlTableRow
        For i = 0 To 6
            Dim tc As New HtmlTableCell
            th.Cells.Add(tc)
        Next
        tbl.Rows.Add(th)
        With tbl.Rows(0)
            .Cells(0).InnerText = ""
            .Cells(1).InnerText = "Material"
            .Cells(2).InnerText = "TTL GTD Qty"
            .Cells(3).InnerText = "GI Docs"
            .Cells(4).InnerText = "TTL FIFO GI Qty"
            .Cells(5).InnerText = "Print"
            .Cells(6).InnerText = "Docs qty"
        End With


        For Each item In docListByMaterialAndGTD
            Dim tr As New HtmlTableRow
            Dim tc As New HtmlTableCell With {.InnerText = item.value.gtd}
            Dim tc1 As New HtmlTableCell With {.InnerText = item.value.material}
            Dim tc2 As New HtmlTableCell With {.InnerText = item.value.ttlInvoiceQty}
            Dim l As New Literal
            Dim s As Integer = 0

            For Each doc In item.value.materialDocsAndQty
                l.Text &= "<br>" & doc.key & " : " & doc.value
                s += doc.value
            Next
            Dim tc3 As New HtmlTableCell
            Dim d As New Panel With {.Height = Unit.Pixel(100)}
            d.Style.Add("overflow", "scroll")
            d.Controls.Add(l)
            tc3.Controls.Add(d)
            Dim tc4 As New HtmlTableCell With {.InnerText = s}
            tr.Cells.Add(tc)
            tr.Cells.Add(tc1)
            tr.Cells.Add(tc2)
            tr.Cells.Add(tc3)
            tr.Cells.Add(tc4)

            If (item.value.ttlInvoiceQty + s) > 0 Then
                tr.Style.Add("background-color", "coral")
            End If


            Dim img As New ImageButton With {.ImageUrl = "~/Images/Buttons/24/Print.png", .ToolTip = "Print"}
            img.Attributes.Add("params", l.Text)
            img.Enabled = countOnly261.Checked
            AddHandler img.Click, AddressOf Print
            Dim tc5 As New HtmlTableCell
            tc5.Controls.Add(img)
            tr.Cells.Add(tc5)

            Dim tc6 As New HtmlTableCell With {.InnerText = item.value.materialDocsAndQty.COunt}
            tr.Cells.Add(tc6)

            tbl.Rows.Add(tr)
        Next
        report.Controls.Add(tbl)

    End Sub

End Class