ModuleMaster/pc/invstat.aspx.vb - main - ivan/ModMas

ModuleMaster/pc/invstat.aspx.vb
344 строки18 KB
i.krivokon
Initial commit
13 мар 2026, 13:49
13 мар 2026, 13:4900d9fb6
Imports System.IO
Imports sharedClasses.dbFunctions

Partial Class PC_invStat
    Inherits System.Web.UI.Page

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

        If Not IsPostBack Then
            Dim today As DayOfWeek = Date.Today.DayOfWeek
            Dim monday As Date
            Dim saturday As Date
            Select Case today
                Case DayOfWeek.Monday
                    monday = Date.Today
                    saturday = Date.Today.AddDays(5)
                Case DayOfWeek.Tuesday
                    monday = Date.Today.AddDays(-1)
                    saturday = Date.Today.AddDays(4)
                Case DayOfWeek.Wednesday
                    monday = Date.Today.AddDays(-2)
                    saturday = Date.Today.AddDays(3)
                Case DayOfWeek.Thursday
                    monday = Date.Today.AddDays(-3)
                    saturday = Date.Today.AddDays(2)
                Case DayOfWeek.Friday
                    monday = Date.Today.AddDays(-4)
                    saturday = Date.Today.AddDays(1)
                Case DayOfWeek.Saturday
                    monday = Date.Today.AddDays(-1)
                    saturday = Date.Today
            End Select
            txtFrom.Text = monday.ToString("yyyy-MM-dd")
            txtTo.Text = saturday.ToString("yyyy-MM-dd")

            edPendingReport.UpdateSorting("DATE", "DESC")
            edDifferentReport.UpdateSorting("DATE", "DESC")

        End If

        Dim userid As String = ""
        If Not IsNothing(Request.QueryString("OrchUserLogin")) Then
            userid = Request.QueryString("OrchUserLogin")
        End If
        Dim tableId As String = ""

        edDetails.IsReadOnly = True
        edDetails.UserName = userid.ToUpper

        edPending.UserName = userid.ToUpper
        edPendingReport.Caption = "Pending"
        edPending.Caption = "Pending"
        edPendingReport.IsFiltersVisible = False

        edDifferent.UserName = userid.ToUpper
        edDifferentReport.Caption = "Different"
        edDifferent.Caption = "Different"
        edDifferentReport.IsFiltersVisible = False

        If txtFrom.Text <> "" And txtTo.Text <> "" Then
            edPendingReport.UpdateFilters("DATE", " between ", "'" & txtFrom.Text & "' AND '" & txtTo.Text & "'")
            edDifferentReport.UpdateFilters("DATE", " between ", "'" & txtFrom.Text & "' AND '" & txtTo.Text & "'")
        ElseIf txtFrom.Text <> "" And txtTo.Text = "" Then
            edPendingReport.UpdateFilters("DATE", " >= ", "'" & txtFrom.Text & "'")
            edDifferentReport.UpdateFilters("DATE", " >= ", "'" & txtFrom.Text & "'")
        ElseIf txtFrom.Text = "" And txtTo.Text <> "" Then
            edPendingReport.UpdateFilters("DATE", " <= ", "'" & txtTo.Text & "'")
            edDifferentReport.UpdateFilters("DATE", " <= ", "'" & txtTo.Text & "'")
        ElseIf txtFrom.Text = "" And txtTo.Text = "" Then
            edPendingReport.ClearFilters("DATE")
            edDifferentReport.ClearFilters("DATE")
        End If

        Dim sqlf As New SqlFunctions

        Dim ds As Data.DataTable
        ds = BuildPivot(txtFrom.Text, txtTo.Text)


        GridView1.DataSource = ds
        GridView1.DataBind()
        CollectHeaderColumn(1)
        CollectHeaderColumn(0)
        

    End Sub

    Private Function BuildPivot(FromDate As String, ToDate As String) As Data.DataTable
        Dim sql As New StringBuilder

        sql.Append("USE UserModules 																")
        sql.Append(vbNewLine)
        sql.Append("DECLARE	@COLUMNS VARCHAR (8000)                                                 ")
        sql.Append(vbNewLine)
        sql.Append("SELECT @COLUMNS = COALESCE (@COLUMNS + ',[' + DATE + ']',	'[' + DATE + ']')   ")
        sql.Append("	FROM [USER].INVENTORY_STATUS WHERE date between '" & FromDate & "' and '" & ToDate & "' ")
        sql.Append("	GROUP BY DATE                                                               ")
        sql.Append(vbNewLine)
        sql.Append("DECLARE                                                                         ")
        sql.Append("			@QUERY VARCHAR (8000)                                               ")
        sql.Append(vbNewLine)
        sql.Append("		SET @QUERY = '                                                          ")
        sql.Append("SELECT * FROM")
        sql.Append("( SELECT ''1'' as sequ,")
        sql.Append("				nullif(rtrim([DATE]),'''')  as DATE,")
        sql.Append("				nullif(rtrim([TYPE]),'''')  as TYPE,")
        sql.Append("				cast ( nullif(rtrim([Q_TY]),'''') as decimal )  as Q_TY,")
        sql.Append("				nullif(rtrim([LOCATION]),'''')  as LOCATION ")
        sql.Append("				FROM usermodules.[user].INVENTORY_STATUS")
        sql.Append("				where [type] = ''Beginning of Day''")
        sql.Append("				) AS source_table")
        sql.Append("				PIVOT (SUM (Q_TY)  FOR DATE IN (' + @COLUMNS + ')) p1 ")
        sql.Append("union ")
        sql.Append("SELECT * FROM  ")
        sql.Append("( SELECT ''2'' as sequ,                                                           ")
        sql.Append("				nullif(rtrim([DATE]),'''')  as DATE,                            ")
        sql.Append("				''Beginning of Day''  as TYPE,                                  ")
        sql.Append("				0  as Q_TY,                                                     ")
        sql.Append("				''TTL''  as LOCATION		                                    ")
        sql.Append("				FROM usermodules.[user].INVENTORY_STATUS                        ")
        sql.Append("				where [type] = ''Beginning of Day''                             ")
        sql.Append("				) AS source_table                                               ")
        sql.Append("				PIVOT (SUM (Q_TY)  FOR DATE IN (' + @COLUMNS + ')) p1		    ")
        sql.Append("union                                                                           ")
        sql.Append("SELECT * FROM  	                                                                ")
        sql.Append("( SELECT ''3'' as sequ,                                                       ")
        sql.Append("				nullif(rtrim([DATE]),'''')  as DATE,                            ")
        sql.Append("				nullif(rtrim([TYPE]),'''')  as TYPE,                            ")
        sql.Append("				cast ( nullif(rtrim([Q_TY]),'''') as decimal )  as Q_TY,        ")
        sql.Append("				nullif(rtrim([LOCATION]),'''')  as LOCATION		                ")
        sql.Append("				FROM usermodules.[user].INVENTORY_STATUS                        ")
        sql.Append("				where [type] = ''''                                             ")
        sql.Append("				) AS source_table                                               ")
        sql.Append("				PIVOT (SUM (Q_TY)  FOR DATE IN (' + @COLUMNS + ')) p1			")
        sql.Append("union                                                                           ")
        sql.Append("SELECT * FROM  	                                                                ")
        sql.Append("( SELECT ''4'' as sequ,                                                       ")
        sql.Append("				nullif(rtrim([DATE]),'''')  as DATE,                            ")
        sql.Append("				nullif(rtrim([TYPE]),'''')  as TYPE,                            ")
        sql.Append("				cast ( nullif(rtrim([Q_TY]),'''') as decimal )  as Q_TY,        ")
        sql.Append("				nullif(rtrim([LOCATION]),'''')  as LOCATION		                ")
        sql.Append("				FROM usermodules.[user].INVENTORY_STATUS                        ")
        sql.Append("				where [type] = ''End of day''                                   ")
        sql.Append("				) AS source_table                                               ")
        sql.Append("				PIVOT (SUM (Q_TY)  FOR DATE IN (' + @COLUMNS + ')) p1			")
        sql.Append("union                                                                           ")
        sql.Append("SELECT * FROM  	                                                                ")
        sql.Append("(  SELECT ''5'' as sequ,                                                           ")
        sql.Append("				nullif(rtrim([DATE]),'''')  as DATE,                            ")
        sql.Append("				''End of Day''  as TYPE,                                        ")
        sql.Append("				0  as Q_TY,                                                     ")
        sql.Append("				''TTL''  as LOCATION		                                    ")
        sql.Append("				FROM usermodules.[user].INVENTORY_STATUS                        ")
        sql.Append("				where [type] = ''End of day''                                   ")
        sql.Append("				) AS source_table                                               ")
        sql.Append("				PIVOT (SUM (Q_TY)  FOR DATE IN (' + @COLUMNS + ')) p1		    ")
        sql.Append("union                                                                           ")
        sql.Append("                                                                                ")
        sql.Append("SELECT * FROM  	                                                                ")
        sql.Append("( SELECT ''6'' as sequ,                                                       ")
        sql.Append("				nullif(rtrim([DATE]),'''')  as DATE,                            ")
        sql.Append("				nullif(rtrim([TYPE]),'''')  as TYPE,                            ")
        sql.Append("				cast ( nullif(rtrim([Q_TY]),'''') as decimal )  as Q_TY,        ")
        sql.Append("				nullif(rtrim([LOCATION]),'''')  as LOCATION		                ")
        sql.Append("				FROM usermodules.[user].INVENTORY_STATUS                        ")
        sql.Append("				where [type] = ''System''                                       ")
        sql.Append("				) AS source_table                                               ")
        sql.Append("				PIVOT (SUM (Q_TY)  FOR DATE IN (' + @COLUMNS + ')) p1		    ")
        sql.Append("union                                                                           ")
        sql.Append("SELECT * FROM  	                                                                ")
        sql.Append("(  SELECT ''7'' as sequ,                                                           ")
        sql.Append("				nullif(rtrim([DATE]),'''')  as DATE,                            ")
        sql.Append("				''System''  as TYPE,                                       ")
        sql.Append("				0  as Q_TY,                                                     ")
        sql.Append("				''TTL''  as LOCATION		                            ")
        sql.Append("				FROM usermodules.[user].INVENTORY_STATUS                        ")
        sql.Append("				where [type] = ''System''                                       ")
        sql.Append("				) AS source_table                                               ")
        sql.Append("				PIVOT (SUM (Q_TY)  FOR DATE IN (' + @COLUMNS + ')) p1	        ")
        sql.Append("union                                                                           ")
        sql.Append("SELECT * FROM  	                                                                ")
        sql.Append("( SELECT ''8'' as sequ,                                                           ")
        sql.Append("				nullif(rtrim([DATE]),'''')  as DATE,                            ")
        sql.Append("				''''  as TYPE,         ")
        sql.Append("				0  as Q_TY,                                                     ")
        sql.Append("				''Difference between planned and actual W/H''  as LOCATION		")
        sql.Append("				FROM usermodules.[user].INVENTORY_STATUS                        ")
        sql.Append("				where [type] = ''System''                                       ")
        sql.Append("				) AS source_table                                               ")
        sql.Append("				PIVOT (SUM (Q_TY)  FOR DATE IN (' + @COLUMNS + ')) p1	        ")
        sql.Append("'                                                                               ")
        sql.Append(vbNewLine)
        sql.Append("EXECUTE (@QUERY)                                                                ")

        Return (New SqlFunctions).GetData(sql.ToString)
    End Function

    Public Sub CollectHeaderColumn(colNumber As Integer, Optional referenceColNumber As Integer = -1)
        Dim curCell As New TableCell With {.Text = "."}
        Dim rowSpan As Integer = 1

        Dim ColNumberToCheckText As Integer
        If referenceColNumber = -1 Then
            ColNumberToCheckText = colNumber
        Else
            ColNumberToCheckText = referenceColNumber
        End If

        Dim curText As String = "."

        For Each dr As GridViewRow In GridView1.Rows
            If dr.Cells(ColNumberToCheckText).Text <> curText Then 'curCell.Text Then
                If rowSpan > 1 Then
                    curCell.RowSpan = rowSpan
                End If
                curCell = dr.Cells(colNumber)
                curText = dr.Cells(ColNumberToCheckText).Text
                rowSpan = 1
            Else
                dr.Cells(colNumber).Visible = False
                rowSpan += 1
            End If
        Next
        If rowSpan > 1 Then
            curCell.RowSpan = rowSpan
        End If
    End Sub


    Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)

    End Sub

    Protected Sub Page_PreRenderComplete(sender As Object, e As EventArgs) Handles Me.PreRenderComplete
        ' GridView1.Columns(0).Visible = False
    End Sub

    Protected Sub Page_SaveStateComplete(sender As Object, e As EventArgs) Handles Me.SaveStateComplete
        'GridView1.Columns(0).Visible = False
        edPendingReport.CreateGroupingText()
        edDifferentReport.CreateGroupingText()
        edDetails.CreateGroupingText()
    End Sub
    Dim ttlBeginningOfDay As New Hashtable
    Dim beginningOfDayRange() As Integer = {0, 1, 2, 3, 4, 5}
    Dim endOfDayRange() As Integer = {8, 9, 10, 11, 12, 13}
    Dim systemRange() As Integer = {14, 15, 16}

    Protected Sub GridView1_RowDataBound(sender As Object, e As GridViewRowEventArgs) Handles GridView1.RowDataBound
        e.Row.Cells(0).Visible = False
        If beginningOfDayRange.Contains(e.Row.RowIndex) Or _
            endOfDayRange.Contains(e.Row.RowIndex) Or _
            systemRange.Contains(e.Row.RowIndex) Then
            If e.Row.RowType = DataControlRowType.DataRow Then
                Dim cellCount As Integer = 3
                If e.Row.Cells(2).Text <> "TTL" Then
                    While cellCount <= e.Row.Cells.Count - 1
                        If ttlBeginningOfDay.Contains(cellCount) Then
                            If e.Row.Cells(cellCount).Text <> "&nbsp;" Then
                                ttlBeginningOfDay(cellCount) += Convert.ToDecimal(e.Row.Cells(cellCount).Text)
                            End If
                        Else
                            If e.Row.Cells(cellCount).Text <> "&nbsp;" Then
                                ttlBeginningOfDay.Add(cellCount, e.Row.Cells(cellCount).Text)
                            Else
                                ttlBeginningOfDay.Add(cellCount, 0)
                            End If
                        End If
                        cellCount += 1
                    End While
                    e.Row.Cells(2).BackColor = Drawing.Color.LightGreen
                Else
                    e.Row.Cells(2).BackColor = Drawing.Color.Yellow
                    e.Row.Cells(2).Font.Bold = True
                    While cellCount <= e.Row.Cells.Count - 1
                        e.Row.Cells(cellCount).Text = ttlBeginningOfDay(cellCount)
                        e.Row.Cells(cellCount).BackColor = Drawing.Color.Yellow
                        e.Row.Cells(cellCount).Font.Bold = True
                        cellCount += 1
                    End While
                    ttlBeginningOfDay.Clear()
                End If
                e.Row.Cells(1).BackColor = Drawing.Color.LightGreen
            End If
        Else
            e.Row.Cells(1).Text = e.Row.Cells(2).Text
            e.Row.Cells(1).ColumnSpan = 2
            e.Row.Cells(2).Visible = False
        End If
    End Sub

    Protected Sub GridView1_DataBound(sender As Object, e As EventArgs) Handles GridView1.DataBound
        Dim ttl1RowIndex As Integer = 0
        Dim ttl2RowIndex As Integer = 0
        Dim ttlCOunt As Integer = 0
        If GridView1.Rows.Count > 0 Then
            For Each r As GridViewRow In GridView1.Rows
                If r.Cells(2).Text = "TTL" Then
                    ttlCOunt += 1
                    If ttlCOunt = 2 Then
                        ttl1RowIndex = r.RowIndex
                    ElseIf ttlCOunt = 3 Then
                        ttl2RowIndex = r.RowIndex
                    End If
                End If
            Next
            Dim cellCount As Integer = 3
            With GridView1.Rows
                While cellCount <= GridView1.Rows(.Count - 1).Cells.Count - 1
                    GridView1.Rows(.Count - 1).Cells(cellCount).Text = Convert.ToDecimal(IIf(.Item(ttl1RowIndex).Cells(cellCount).Text = "&nbsp;", 0, .Item(ttl1RowIndex).Cells(cellCount).Text)) - Convert.ToDecimal(IIf(.Item(ttl2RowIndex).Cells(cellCount).Text = "&nbsp;", 0, .Item(ttl2RowIndex).Cells(cellCount).Text))
                    GridView1.Rows(.Count - 1).Cells(cellCount).BackColor = Drawing.Color.FromArgb(0, 58, 130)
                    GridView1.Rows(.Count - 1).Cells(cellCount).ForeColor = Drawing.Color.White
                    GridView1.Rows(.Count - 1).Cells(cellCount).Font.Bold = True
                    cellCount += 1
                End While
                GridView1.Rows(.Count - 1).Cells(1).ForeColor = Drawing.Color.White
                GridView1.Rows(.Count - 1).Cells(1).BackColor = Drawing.Color.FromArgb(0, 58, 130)
                GridView1.Rows(.Count - 1).Cells(1).Font.Bold = True
            End With
        End If
    End Sub

    Protected Sub ibExcelDownload_Click(sender As Object, e As ImageClickEventArgs) Handles ibExcelDownload.Click
        ExcelDownloadSub(GridView1, "Daily_Inventory_Report_" & DateTime.Now.ToString)
    End Sub

    Sub ExcelDownloadSub(ByVal gv As GridView, ByVal filename As String)
        Response.Clear()
        Response.AddHeader("content-disposition", "attachment;filename=" & filename & ".xls")
        Response.Charset = ""
        Response.ContentType = "application/vnd.xls"

        gv.AllowSorting = False
        gv.AllowPaging = False

        Dim sw As StringWriter = New StringWriter
        Dim hw As HtmlTextWriter = New HtmlTextWriter(sw)
        gv.RenderControl(hw)
        Response.Write(sw.ToString)
        Response.End()
    End Sub


End Class