ModuleMaster/mi/kpi.aspx.vb - main - ivan/ModMas

ModuleMaster/mi/kpi.aspx.vb
235 строк10 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 line As String


    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        Dim DV_linear, dv_target As DataView
        Dim kpi As String
        Dim plant As String

        'Dim Day_Flag As Boolean = True
        'Dim Line_Flag As Boolean = False
        Dim sql, sql_target_queue, sql_kpi_drop_list_queue As New StringBuilder ' sql - для линейного графика, sql1 - для графика с колонками, sql2 - для таблицы


        'line = Request("line")
        'If Not String.IsNullOrEmpty(line) And line <> "Total VD" Then
        '    Line_Flag = True
        'End If

        'If Not IsPostBack Then
        '    graph_date.Value = Request("date")
        'End If


        'If graph_date.Value <> "" Then
        '        Dim tmp_date As Date = graph_date.Value
        '        If DateTime.Now.ToString("yyyy/MM/dd") <> Date.Parse(graph_date.Value).ToString("yyyy/MM/dd") Then
        '            Day_Flag = False
        '        End If
        '    End If







        If Not IsPostBack Then

            'Строка запроса для получения списка KPI
            sql_kpi_drop_list_queue.AppendLine("select ")
            sql_kpi_drop_list_queue.AppendLine("distinct plant + ' - ' + KPI_NAME as drop_list ")
            sql_kpi_drop_list_queue.AppendLine("from [UserModules].[user].[KPI_WEEKLY_REVIEW] ")
            sql_kpi_drop_list_queue.AppendLine("where plant = 'P901' or plant = 'P903' ")
            sql_kpi_drop_list_queue.AppendLine("order by drop_list ")

            sql_kpi_drop_list.SelectCommand = sql_kpi_drop_list_queue.ToString
            'Формирование выпадающего списка с KPI
            KPI_drop_list.DataSource = sql_kpi_drop_list
            KPI_drop_list.DataTextField = "drop_list"
            KPI_drop_list.DataValueField = "drop_list"
            KPI_drop_list.DataBind()
            KPI_drop_list.SelectedIndex = 0
        End If

        plant = KPI_drop_list.SelectedItem.Text.Substring(0, 4)
        kpi = KPI_drop_list.SelectedItem.Text.Substring(7)





        'Строка запроса к базе для линейного графика
        sql.AppendLine("declare @date date ")
        sql.AppendLine("set @date = cast(getdate() as date) ")
        sql.AppendLine("set @date = '2016-12-30' ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("declare @Year char(4), @Week TINYINT, @month tinyint, @quarter tinyint ")
        sql.AppendLine("set @Year = datepart(year,@date) ")
        sql.AppendLine("set @Week = datepart(ISO_WEEK, @date) ")
        sql.AppendLine("SET @month = month(dateadd(wk,@Week,@Year + '/01/01')) ")
        sql.AppendLine("set @quarter = datepart(quarter, dateadd(wk,@Week,@Year + '/01/01')) ")
        sql.AppendLine("--SELECT DATENAME(month, DATEADD(month, @month, -1 )) ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("declare @KPI TABLE (plant varchar(10), KPI_NAME varchar(100), [year] char(4), [week] char(3), kpi_value decimal(20,2), reason varchar(200), contermeasure char(200), [target] decimal(20,2), [month] char(20), [quarter] int) ")
        sql.AppendLine("insert into @KPI ")
        sql.AppendLine("select plant, KPI_NAME, [year], [week], cast(kpi_value as decimal(20,2)), reason, contremeasure, cast([target] as decimal(20,2)) ")
        sql.AppendLine(",DATENAME(month, DATEADD(month, month(dateadd(week,cast(right([week],2) as int),@Year + '/01/01')), -1 )) as [Month] ")
        sql.AppendLine(",datepart(quarter, dateadd(wk,cast(right([week],2) as int),@Year + '/01/01')) as [Quarter] ")
        sql.AppendLine("from [UserModules].[user].[KPI_WEEKLY_REVIEW] ")
        sql.AppendLine("where plant = '" + plant + "' ")
        sql.AppendLine("and KPI_NAME = '" + kpi + "' ")
        sql.AppendLine("order by week asc ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("select ")
        sql.AppendLine("plant ")
        sql.AppendLine(",KPI_NAME ")
        sql.AppendLine(",[year] ")
        sql.AppendLine(",'' as [week] ")
        sql.AppendLine(", cast(avg(kpi_value) as decimal (20,2)) as kpi_value ")
        sql.AppendLine(", cast(avg([target]) as decimal(20,2)) as [target] ")
        sql.AppendLine(",'' as [month] ")
        sql.AppendLine(",'' as [quarter] ")
        sql.AppendLine(",'PY' as label ")
        sql.AppendLine(",'1' as group# ")
        sql.AppendLine(",ROW_NUMBER() OVER(ORDER BY [quarter] ASC) AS Row# ")
        sql.AppendLine(" from @kpi ")
        sql.AppendLine("where ")
        sql.AppendLine("[year] = year(dateadd(year,-1,@Year+'/01/01')) ")
        sql.AppendLine("group by plant, KPI_NAME, [year], [quarter] ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("union all ")
        sql.AppendLine(" ")
        sql.AppendLine("select ")
        sql.AppendLine("plant ")
        sql.AppendLine(",KPI_NAME ")
        sql.AppendLine(",[year] ")
        sql.AppendLine(",'' as [week] ")
        sql.AppendLine(", cast(avg(kpi_value) as decimal (20,2)) as kpi_value ")
        sql.AppendLine(", cast(avg([target]) as decimal(20,2)) as [target] ")
        sql.AppendLine(",'' as [month] ")
        sql.AppendLine(",[quarter] ")
        sql.AppendLine(",'Q'+cast([quarter] as nvarchar) as label ")
        sql.AppendLine(",'2' as group# ")
        sql.AppendLine(",ROW_NUMBER() OVER(ORDER BY [quarter] ASC) AS Row# ")
        sql.AppendLine(" from @kpi ")
        sql.AppendLine("where [month] <> DATENAME(month, DATEADD(month, @month, -1 )) ")
        sql.AppendLine("and [quarter] <> @quarter ")
        sql.AppendLine("and [year] = @Year ")
        sql.AppendLine("group by plant, KPI_NAME, [year], [quarter] ")
        sql.AppendLine(" ")
        sql.AppendLine("union all ")
        sql.AppendLine(" ")
        sql.AppendLine("select ")
        sql.AppendLine("plant ")
        sql.AppendLine(",KPI_NAME ")
        sql.AppendLine(",[year] ")
        sql.AppendLine(",'' as [week] ")
        sql.AppendLine(", cast(avg(kpi_value) as decimal (20,2)) as kpi_value ")
        sql.AppendLine(", cast(avg([target]) as decimal(20,2)) as [target] ")
        sql.AppendLine(",[month] ")
        sql.AppendLine(",[quarter] ")
        sql.AppendLine(",left([month],3) as label ")
        sql.AppendLine(",'3' as group# ")
        sql.AppendLine(",ROW_NUMBER() OVER(ORDER BY DATEPART(MM,[month]+'01 1900') ASC) AS Row# ")
        sql.AppendLine(" from @kpi ")
        sql.AppendLine("where [month] <> DATENAME(month, DATEADD(month, @month, -1 )) ")
        sql.AppendLine("and [quarter] = @quarter ")
        sql.AppendLine("and [year] = @Year ")
        sql.AppendLine("group by plant, KPI_NAME, [year], [month], [quarter] ")
        sql.AppendLine(" ")
        sql.AppendLine("union all ")
        sql.AppendLine(" ")
        sql.AppendLine("select ")
        sql.AppendLine("plant ")
        sql.AppendLine(",KPI_NAME ")
        sql.AppendLine(",[year] ")
        sql.AppendLine(",[week] ")
        sql.AppendLine(",kpi_value ")
        sql.AppendLine(",[target] ")
        sql.AppendLine(",[month] ")
        sql.AppendLine(",[quarter] ")
        sql.AppendLine(",[week] as label ")
        sql.AppendLine(",'4' as group# ")
        sql.AppendLine(",ROW_NUMBER() OVER(ORDER BY DATEPART(MM,[month]+'01 1900') ASC) AS Row# ")
        sql.AppendLine(" from @kpi ")
        sql.AppendLine("where [month] = DATENAME(month, DATEADD(month, @month, -1 )) ")
        sql.AppendLine("and [year] = @Year ")
        sql.AppendLine(" ")
        sql.AppendLine("order by group#, row# ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")

        Sql_linear.SelectCommand = sql.ToString





        sql_target_queue.AppendLine("declare @date date ")
        'sql_target_queue.AppendLine("set @date = getdate() ")
        sql_target_queue.AppendLine("set @date = '2016-12-30' ")
        sql_target_queue.AppendLine(" ")
        sql_target_queue.AppendLine("declare @Year char(4), @Week TINYINT, @month tinyint, @quarter tinyint ")
        sql_target_queue.AppendLine("set @Year = datepart(year,@date) ")
        sql_target_queue.AppendLine("set @Week = datepart(ISO_WEEK, @date)")
        sql_target_queue.AppendLine("SET @month = month(dateadd(wk,@Week,@Year + '/01/01')) ")
        sql_target_queue.AppendLine("set @quarter = datepart(quarter, dateadd(wk,@Week,@Year + '/01/01')) ")
        sql_target_queue.AppendLine(" ")
        sql_target_queue.AppendLine("select top 1 ")
        sql_target_queue.AppendLine("[target] ")
        sql_target_queue.AppendLine(",[week] as label")
        sql_target_queue.AppendLine("from [UserModules].[user].[KPI_WEEKLY_REVIEW] ")
        sql_target_queue.AppendLine("where plant = '" + plant + "' ")
        sql_target_queue.AppendLine("and KPI_NAME = '" + kpi + "' ")
        sql_target_queue.AppendLine("and [year] = @year ")
        sql_target_queue.AppendLine("order by week desc ")

        sql_target.SelectCommand = sql_target_queue.ToString




        'Распихиваем данные по таблицам
        DV_linear = Sql_linear.Select(DataSourceSelectArguments.Empty)
        dv_target = sql_target.Select(DataSourceSelectArguments.Empty)



        'Формируем график
        Summary.Series.FindByName("KPI_value").Points.DataBind(DV_linear, "label", "KPI_VALUE", "")
        Summary.Series.FindByName("target").Points.DataBind(DV_linear, "label", "target", "")



        Summary.Series.FindByName("target_label").Points.DataBind(dv_target, "label", "target", "")
        Dim i As Integer
        For i = 0 To Summary.Series.FindByName("target").Points.Count - 2
            Dim point As New DataPoint()
            point.IsEmpty = True
            Summary.Series.FindByName("target_label").Points.Insert(0, point)
        Next

        'Легенда
        Summary.Series.FindByName("KPI_value").LegendText = kpi
        'Заголовок
        Summary.Titles.Item(0).Text = KPI_drop_list.SelectedValue.Substring(7)


    End Sub


End Class