ModuleMaster/usercontrols/graphfortable.ascx.vb - main - ivan/ModMas

ModuleMaster/usercontrols/graphfortable.ascx.vb
366 строк14 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
Imports sharedClasses.dbFunctions

Partial Class UserControls_graphForTable
    Inherits System.Web.UI.UserControl

    Public Property TableId As String

    Private _selectCondition As String = ""

    Private _palette As ChartColorPalette = Nothing

    Public Property Palette As ChartColorPalette
        Set(value As ChartColorPalette)
            _palette = value
        End Set
        Get
            Return _palette
        End Get
    End Property

    Public WriteOnly Property SelectCondition As String
        Set(value As String)
            _selectCondition = value
        End Set
    End Property

    Public Property ShowHeader As Boolean
        Set(value As Boolean)
            htblHeader.Visible = value
        End Set
        Get
            Return htblHeader.Visible
        End Get
    End Property

    Public ReadOnly Property GraphTable As Table
        Get
            Return tblKpiGraphs
        End Get
    End Property


    Public Property StartSlideShow As Boolean
        Set(value As Boolean)
            If value Then
                ibStartSlideShow.ImageUrl = "~\Images\stop.ico"
                Timer1.Enabled = True
            Else
                ibStartSlideShow.ImageUrl = "~\Images\play.ico"
                Timer1.Enabled = False
            End If
        End Set
        Get
            If ibStartSlideShow.ImageUrl = "~\Images\stop.ico" Then
                Return True
            Else
                Return False
            End If
        End Get
    End Property

#Region "Graph"
    Private Function ReadGraphParams() As DataTable
        Try
            Dim ds As DataTable = (New SqlFunctions).GetData("SELECT * FROM UserModules.dbo.TableGraph where enabled='Y' and tableId=" & TableId)
            If Not IsNothing(ds) Then
                Return ds
            Else
                Return Nothing
            End If
        Catch ex As Exception
            Return Nothing
        End Try
    End Function


    Private IgnoreParams As Boolean = False


    Public Function DrawGraph(Optional graphType As Integer = -1) As Boolean
        Dim dt As DataTable = ReadGraphParams()
        If Not IsNothing(dt) Then
            If dt.Rows.Count > 0 Then
                'htblHeader.Visible = True
                For Each dr As DataRow In dt.Rows
                    Try
                        CreateGraphLayout(tblKpiGraphs, _
                                          dr("TABLENAME"), _
                                          dr("KPICOLUMN"), _
                                          dr("KPI_NAME"), _
                                          PrepareColumnsList(dr("TABLENAME"), dr("WEEKS")), _
                                          PrepareColumnsList(dr("TABLENAME"), dr("TARGETCOLUMN")), _
                                          dr("TARGETCOLUMN"), _
                                          dr("width"), _
                                          dr("height"), _
                                          dr("qty_in_a_row"), _
                                          IIf(graphType = -1, rblGraphType.SelectedValue, graphType), _
                                          dr("MARK_FIELD_NAME").ToString)
                        tblKpiGraphs.DataBind()

                        If StartSlideShow And Not IgnoreParams Then
                            With tblKpiGraphs
                                If .Rows.Count > 0 Then
                                    If .Rows(0).Cells.Count > 0 Then
                                        If .Rows(0).Cells(0).Controls.Count > 0 Then
                                            Try
                                                Dim redirectpath As String = CType(.Rows(0).Cells(0).Controls(0), UserControls_graphaspnet).MapAreaUrl
                                                If redirectpath <> "" Then
                                                    Response.Redirect(redirectpath & "&mode=slideshow&tableid=" & TableId)
                                                End If
                                            Catch ex As Exception

                                            End Try
                                        End If
                                    End If
                                End If
                            End With
                        End If


                    Catch ex As Exception

                    End Try
                Next
            Else
                Return False
            End If
        Else
            Return False
        End If
        Return True
    End Function

    Private Function PrepareColumnsList(tableName As String, _
                                       columnNameTemplate As String) As String
        tableName = tableName.Trim("[").Trim("]")
        Dim sql As New StringBuilder
        sql.Append("select t1.name ")
        sql.Append("from usermodules.sys.columns t1 ")
        sql.Append("Left Join ")
        sql.Append("(select DbFieldName , [position], [visible] from usermodules.[dbo].TableFields  ")
        sql.Append("where TableId in (select id from UserModules.[dbo].ModuleTables where dbtablename ='" & tableName & "') ")
        sql.Append(") t2 on t1.name = t2.DbFieldName  ")
        sql.Append("where t1.object_id in  ")
        sql.Append("(select object_id from usermodules.sys.tables where name = '" & tableName & "') ")
        sql.Append("and t1.name like '" & columnNameTemplate & "' and t2.visible='Y' ")
        sql.Append("order by t2.position ")
        Dim ds As Data.DataTable = (New SqlFunctions).GetData(sql.ToString)
        Dim result As String = ""

        If Not IsNothing(ds) Then
            For Each dr As Data.DataRow In ds.Rows
                If result = "" Then
                    result = "[" & dr("name") & "]"
                Else
                    result &= "," & "[" & dr("name") & "]"
                End If
            Next
        End If
        If result = "" Then
            Return columnNameTemplate
        Else
            Return result
        End If
    End Function

    Private Sub CreateGraphLayout(ByRef tblLayout As Table, _
                               tablename As String, _
                               kpiColumn As String, _
                               kpiName As String, _
                               weeks As String, _
                               targets As String, _
                               targetColumn As String, _
                               width As Integer, _
                               height As Integer, _
                               qty_in_a_row As Integer, _
                               graphType As Integer,
                               mark_field_name As String
                               )
        tblLayout.Rows.Clear()
        Dim sqlf As New SqlFunctions
        Dim ds As Data.DataTable

        Dim query As String = ""

        If mark_field_name <> "" Then
            query = "select " & kpiColumn & "," & mark_field_name & " from UserModules .[user]." & tablename & " where " & kpiColumn & " like '" & kpiName & "' "
        Else
            query = "select " & kpiColumn & " from UserModules .[user]." & tablename & " where " & kpiColumn & " like '" & kpiName & "' "
        End If

        If _selectCondition <> "" Then
            query &= _selectCondition.Replace("WHERE", " AND ")
        End If
        query &= " order by " & kpiColumn 'id"

        ds = sqlf.GetData(query)

        Dim graphCount As Integer = 0
        If Not IsNothing(ds) Then

            If Session("graph_params_" & tablename) Is Nothing Then
                Session.Add("graph_params_" & tablename, New Collection)
            Else
                CType(Session("graph_params_" & tablename), Collection).Clear()
            End If

            Dim tr As New TableRow
            Dim cnt As Integer = 0
            For Each dr As Data.DataRow In ds.Rows
                Dim c As UserControl = Nothing
                Select Case graphType
                    Case 1
                        Dim graph As UserControls_Graph = LoadControl("~/UserControls/Graph.ascx")

                        graph.tableName = tablename
                        graph.kpiColumn = kpiColumn
                        graph.kpiName = dr(kpiColumn)
                        graph.weeks = weeks

                        graph.targetColumn = targetColumn

                        graph.graphType = "UNPIVOT"

                        Dim w As Integer = 300
                        If width = 0 Then
                            graph.Width = w
                        Else
                            graph.Width = width
                        End If
                        If height = 0 Then
                            graph.Height = Math.Truncate((Math.Sqrt(5) - 1) / 2 * graph.Width)
                        Else
                            graph.Height = height
                        End If


                        c = graph
                    Case 2
                        Dim graph As UserControls_graphaspnet = LoadControl("~/UserControls/Graphaspnet.ascx")

                        If Not IsNothing(_palette) Then
                            graph.Palette = _palette
                        End If

                        graph.tableName = tablename
                        graph.kpiColumn = kpiColumn
                        graph.kpiName = dr(kpiColumn)
                        graph.weeks = weeks
                        graph.targets = targets
                        graph.targetColumn = targetColumn

                        graph.Title.Font = New System.Drawing.Font("Tahoma", 10, Drawing.FontStyle.Bold)

                        Dim w As Integer = 300
                        If width = 0 Then
                            graph.width = w
                        Else
                            graph.width = width
                        End If
                        If height = 0 Then
                            graph.height = Math.Truncate((Math.Sqrt(5) - 1) / 2 * graph.width)
                        Else
                            graph.height = height
                        End If
                        If mark_field_name <> "" Then
                            If dr(mark_field_name).ToString <> "" Then
                                graph.highlight = True
                            End If
                        End If
                        '"/viewSingleGraph.aspx?params=" & targetColumn & "|" & kpiColumn & "|" & weeks & "|" & tableName & "|" & kpiName & "|" & highlight

                        CType(Session("graph_params_" & tablename), Collection).Add(targetColumn & "|" & kpiColumn & "|" & weeks & "|" & tablename & "|" & dr(kpiColumn) & "|" & graph.highlight & "|" & targets, cnt)

                        cnt += 1

                        c = graph
                End Select

                Dim tc As New TableCell
                tc.Controls.Add(c)
                tr.Cells.Add(tc)
                graphCount += 1
                If qty_in_a_row <> 0 Then
                    If graphCount = qty_in_a_row Then
                        tblLayout.Rows.Add(tr)
                        tr = New TableRow
                        graphCount = 0
                    End If
                End If
            Next
            tblLayout.Rows.Add(tr)
        Else

        End If
    End Sub
#End Region

    Protected Sub ibSendEmail0_Click(sender As Object, e As ImageClickEventArgs) Handles ibSendEmail.Click
        pnlMailRecepients.Visible = True
        upMailRecepients.Update()
    End Sub

    Public Function GetHTMLGraph() As String

        DrawGraph(2)

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


        tblKpiGraphs.RenderControl(hw)
        'Return sw.ToString.Replace("/WEB/TempImages", Request.Url.GetLeftPart(UriPartial.Authority) & "/WEB/TempImages")
        Return sw.ToString.Replace("/ModuleMaster/TempImages", Request.Url.GetLeftPart(UriPartial.Authority) & "/ModuleMaster/TempImages")

    End Function

    Protected Sub ibSendEmail0_Click1(sender As Object, e As ImageClickEventArgs) Handles ibSendEmail0.Click
        If txtMailRecepients.Text <> "" Then
            Dim recepients() As String = txtMailRecepients.Text.Split({Chr(10), Chr(13)})
            If recepients.Count > 0 Then
                'Dim body As String = txtMessageText.Content & "<hr />" & GetHTMLGraph()
                Dim body As String = txtMessageText.Text & "<hr />" & GetHTMLGraph()
                For Each r In recepients
                    If r <> "" Then
                        Dim UserTableName As String = (New SqlFunctions).GetScalar("SELECT tablename FROM UserModules.dbo.ModuleTables where id = " & TableId)
                        Dim subject As String = UserTableName & " [by " & Session("UserLogin") & "]"
                        CommonFunctions.SendEmail(r, subject, body)
                    End If
                Next
            End If
        End If
        pnlMailRecepients.Visible = False
        upMailRecepients.Update()
    End Sub

    Protected Sub ibSaveLayout_Click(sender As Object, e As System.Web.UI.ImageClickEventArgs) Handles ibSaveLayout.Click
        If txtGroupDescription.Visible Then

            Dim query As String = "INSERT INTO [UserModules].[dbo].GRAPH_VIEWS (table_name, table_id, group_description, select_condition) VALUES ("
            query &= "(SELECT TABLENAME from [UserModules].[dbo].ModuleTables where id=" & TableId & ")," & TableId & ",@group_description,@select_condition)"
            Dim com As New SqlClient.SqlCommand(query)
            With com.Parameters
                .AddWithValue("group_description", txtGroupDescription.Text)
                .AddWithValue("select_condition", _selectCondition)
            End With

            Call (New SqlFunctions).NonQuery(com)
            txtGroupDescription.Visible = False
            txtGroupDescription.Text = ""
            upGroupDescription.Update()
        Else
            txtGroupDescription.Visible = True
            upGroupDescription.Update()
        End If
    End Sub


    Protected Sub ibStartSlideShow_Click(sender As Object, e As System.Web.UI.ImageClickEventArgs) Handles ibStartSlideShow.Click
        StartSlideShow = Not StartSlideShow
        IgnoreParams = True
    End Sub
End Class