ModuleMaster/mm/missing.aspx.vb - main - ivan/ModMas

ModuleMaster/mm/missing.aspx.vb
168 строк6 KB
i.krivokon
Initial commit
13 мар 2026, 13:49
13 мар 2026, 13:4900d9fb6

Imports System.Data
Imports System.IO
Imports System.Web.UI.DataVisualization.Charting

Partial Class r
    Inherits System.Web.UI.Page



    Private ExcelDownload As Boolean = False
    Private Const parameterPrefix As String = "$#"
    Private WeekNumber, DayOfWeekNumber As Integer
    Public year, year_prev As String



    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        Dim DV, DV_prev As DataView

        Dim label As String
        Dim i As Integer
        Dim sql As New StringBuilder
        Dim sql_c, sql_p As String

        ' Текущий и предыдущий года
        year = DatePart(DateInterval.Year, Date.Today, Microsoft.VisualBasic.FirstDayOfWeek.Monday, FirstWeekOfYear.FirstFourDays).ToString
        year_prev = (DatePart(DateInterval.Year, Date.Today, Microsoft.VisualBasic.FirstDayOfWeek.Monday, FirstWeekOfYear.FirstFourDays) - 1).ToString

        'Строка запроса к базе
        sql.AppendLine("WITH months(MonthNumber) AS ")
        sql.AppendLine("( ")
        sql.AppendLine(" SELECT 0 ")
        sql.AppendLine(" UNION ALL ")
        sql.AppendLine(" SELECT MonthNumber+1 ")
        sql.AppendLine(" FROM months ")
        sql.AppendLine(" WHERE MonthNumber < 11 ")
        sql.AppendLine(") ")
        sql.AppendLine(" ")
        sql.AppendLine("select DATENAME(MONTH,DATEADD(MONTH,+MonthNumber,'1900-01-01')) AS [month] ")
        sql.AppendLine(", MonthNumber ")
        sql.AppendLine(",closed ")
        sql.AppendLine(",[In Progress] ")
        sql.AppendLine("from months ")
        sql.AppendLine("left join ")
        sql.AppendLine(" ")
        sql.AppendLine("( ")
        sql.AppendLine("select * ")
        sql.AppendLine(" from ")
        sql.AppendLine(" (select ")
        sql.AppendLine(" DATENAME(month, cast([POSTING_DATE] as date)) as [Month] ")
        sql.AppendLine(" ,count( cast([Quntity] as float)) as [Qty] ")
        sql.AppendLine(" ,[CURRENT_STATUS] ")
        sql.AppendLine(" from [UserModules].[user].[MISSING] ")
        sql.AppendLine(" where [DIFF_TYPE]<> 'OVER' ")
        sql.AppendLine(" and DATENAME(year, cast([POSTING_DATE] as date)) = '@year' ")
        sql.AppendLine(" group by [CURRENT_STATUS], [POSTING_DATE]) as t5 ")
        sql.AppendLine(" pivot (sum([Qty] ) for [CURRENT_STATUS] ")
        sql.AppendLine(" in ")
        sql.AppendLine(" ([Closed], [In Progress])) as t2 ")
        sql.AppendLine(") T ")
        sql.AppendLine("on DATENAME(MONTH,DATEADD(MONTH,+MonthNumber,'1900-01-01')) = T.Month ")
        sql.AppendLine("order by MonthNumber ")

        'Строки запроса для текущего и предыдущего годов
        sql_c = sql.ToString
        sql_p = sql.ToString
        sql_c = sql_c.Replace("@year", year)
        sql_p = sql_p.Replace("@year", year_prev)

        Sql_current.SelectCommand = sql_c
        Sql_current.Select(DataSourceSelectArguments.Empty)

        SQL_prev.SelectCommand = sql_p
        SQL_prev.Select(DataSourceSelectArguments.Empty)

        'Распихиваем данные по таблицам
        DV = Sql_current.Select(DataSourceSelectArguments.Empty)
        DV_prev = SQL_prev.Select(DataSourceSelectArguments.Empty)

        'Распихиваем данные по GridView
        report.DataSource = SqlDataSource
        report.DataBind()
        Details.DataSource = SqlDataSource1
        Details.DataBind()

        'Формируем график unsolved / solved
        Chart1.Series.FindByName("Closed_current").Points.DataBind(DV, "Month", "Closed", "")
        Chart1.Series.FindByName("InProgress_current").Points.DataBind(DV, "Month", "In Progress", "")

        Chart1.Series.FindByName("Closed_prev").Points.DataBind(DV_prev, "Month", "Closed", "")
        Chart1.Series.FindByName("InProgress_prev").Points.DataBind(DV_prev, "Month", "In Progress", "")
        'Группируем колонки
        Chart1.Series.FindByName("Closed_current")("StackedGroupName") = "Gropu1"
        Chart1.Series.FindByName("InProgress_current")("StackedGroupName") = "Gropu1"

        Chart1.Series.FindByName("Closed_prev")("StackedGroupName") = "Gropu2"
        Chart1.Series.FindByName("InProgress_prev")("StackedGroupName") = "Gropu2"

        'Делаем подписи по оси X
        i = 1
        For Each row As DataRowView In DV
            label = row.Item(0).ToString
            Chart1.ChartAreas("ChartArea1").AxisX.CustomLabels.Add(i - 0.4, i, year_prev, 1, LabelMarkStyle.LineSideMark)
            Chart1.ChartAreas("ChartArea1").AxisX.CustomLabels.Add(i, i + 0.4, year, 1, LabelMarkStyle.LineSideMark)
            'Chart1.ChartAreas("ChartArea1").AxisX.CustomLabels.Add(i - 0.4, i + 0.4, label, 2, LabelMarkStyle.LineSideMark)
            i = i + 1
        Next







    End Sub

    Private Function IndexOf(str As String, c As String, startPos As Integer) As Integer
        Return IIf(str.IndexOf(c, startPos) = -1, Integer.MaxValue, str.IndexOf(c, startPos))
    End Function


    Private Sub r_PreRender(sender As Object, e As EventArgs) Handles Me.PreRender
        Try
            If ExcelDownload Then
                ExcelDownloadSub(report, "Vendor on hand rate" & "_" & DateTime.Now.ToString("yyyyMMddHHmmss"))
            Else
                report.DataBind()
            End If
        Catch ex As Exception

        End Try
    End Sub

    'Protected Sub ibExcelDownload_Click(sender As Object, e As ImageClickEventArgs) Handles ibExcelDownload.Click
    '    ExcelDownload = True
    'End Sub

    Sub ExcelDownloadSub(ByVal gv As GridView, ByVal filename As String)
        Response.Clear()

        'Response.Buffer = True

        Response.AddHeader("content-disposition", "attachment;filename=" & filename & ".xls")
        Response.ContentType = "application/vnd.xls"
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8")
        EnableViewState = False

        Response.Charset = "utf-8"

        Dim myCItrad As System.Globalization.CultureInfo = New System.Globalization.CultureInfo("en-US", True)

        gv.AllowSorting = False
        gv.AllowPaging = False
        gv.DataBind()

        Dim sw As StringWriter = New StringWriter(myCItrad)
        Dim hw As HtmlTextWriter = New HtmlTextWriter(sw)

        gv.RenderControl(hw)
        Response.Write(sw.ToString)

        Response.End()
    End Sub




End Class