ModuleMaster/sj_vend/serk_plan_total.aspx.vb - main - ivan/ModMas

ModuleMaster/sj_vend/serk_plan_total.aspx.vb
268 строк11 KB
i.krivokon
Initial commit
13 мар 2026, 13:49
13 мар 2026, 13:4900d9fb6
Imports System.Data
Imports System.Data.SqlClient
Imports sharedClasses.dbFunctions
Imports System.Net
Imports System.IO

Partial Class serk_plan
    Inherits System.Web.UI.Page

    Private sqlf As New sharedClasses.dbFunctions.SqlFunctions
    Private a As sharedClasses.Auth.auth
    Private Sub authorize(mode As String)
        a = New sharedClasses.Auth.auth(mode, IIf(Request.UserHostAddress = "::1", "106.109.72.69", Request.UserHostAddress))
    End Sub

    Dim tableId As String = ""

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        Dim userid As String = ""
        If Not IsNothing(Request.QueryString("OrchUserLogin")) Then
            userid = Request.QueryString("OrchUserLogin")
        End If

        If Not IsNothing(Request.QueryString("u")) Then
            userid = Request.QueryString("u")
        End If

        If userid = "" Then
            authorize("SSO")
            userid = a.userId
            edTableToView.Message = "-"
        End If

        edTableToView.IsReadOnly = True
        edTableToView.UserName = userid.ToUpper
        If IsNumeric(tableId) Then
            edTableToView.TableId = tableId
        End If
        edTableToView.SubReport.Controls.Add(createSubreport(tableId))
    End Sub
    Private Sub CheckUploadedData()
        Dim cnt As Integer = sqlf.GetScalar("SELECT Count(*) FROM UserModules.[user].SERK_PRODUCTION_PLAN where Model = 'Model'")
        If cnt = 0 Then
            edTableToView.Message = "Загрузите данные вместе с заголовком (например: W/C	Model	Unit	Prod Order	Site Name	Start Date	Start Time	Finish Dat	Finish Tim	S.Lo	Mfg Week	Ship Week	Ver.	2016-09-22	2016-09-23	2016-09-24	2016-09-25	2016-09-26	2016-09-27	2016-09-28	2016-09-29	2016-09-30	2016-10-01	2016-10-02)"
        ElseIf cnt > 1 Then
            edTableToView.Message = "Загрузите только 1 файл"
        Else
            edTableToView.Message = "Данные найдены"
        End If
    End Sub
    Private Function prepareDateList() As String
        Dim sql As New StringBuilder
        sql.AppendLine("SELECT ")
        sql.AppendLine("'[' + isnull(D,'') + '],' ")
        sql.AppendLine("+ '[' + isnull(D_1,'') + '],' ")
        sql.AppendLine("+ '[' + isnull(D_2,'') + '],' ")
        sql.AppendLine("+ '[' + isnull(D_3,'') + '],' ")
        sql.AppendLine("+ '[' + isnull(D_4,'') + '],' ")
        sql.AppendLine("+ '[' + isnull(D_5,'') + '],' ")
        sql.AppendLine("+ '[' + isnull(D_6,'') + '],' ")
        sql.AppendLine("+ '[' + isnull(D_7,'') + '],' ")
        sql.AppendLine("+ '[' + isnull(D_8,'') + '],' ")
        sql.AppendLine("+ '[' + isnull(D_9,'') + '],' ")
        sql.AppendLine("+ '[' + isnull(D_10,'') + ']' ")
        sql.AppendLine(" FROM UserModules.[user].SERK_PRODUCTION_PLAN where WC = 'W/C' ")
        'Return sqlf.GetScalar(sql.ToString).Replace(",[]", "")
        Return sqlf.GetScalar(sql.ToString)
    End Function
    Protected Sub Page_PreRender(sender As Object, e As System.EventArgs) Handles Me.PreRender
        If edTableToView.IsReadOnly Then
            edTableToView.CollectHeaderColumn(1)
        End If
        Title = edTableToView.TableDescription


        ' CheckUploadedData()
    End Sub
    Private Function createSubreport(tid As String) As GridView

        Dim gv As New GridView
        Dim sqlf As New sharedClasses.dbFunctions.SqlFunctions
        Dim query As String = sqlf.GetScalar("SELECT isnull(SUBREPORT_QUERY,'') from usermodules.dbo.moduletables where id = " & tid)
        If query <> "" And Not (query.Contains("update") Or
                               query.Contains("delete") Or
                               query.Contains("exec") Or
                               query.Contains("truncate") Or
                               query.Contains("drop")) Then
            gv.DataSource = sqlf.GetData(query)
            Try
                gv.DataBind()
            Catch ex As Exception
                Return Nothing
            End Try
        End If
        Return gv
    End Function
    Protected Sub UpdateUserInfo(ByRef com As SqlCommand) Handles edTableToView.NewRecordSaving
        Try
            com.Parameters("@CHANGED_BY").Value = Request.QueryString("orchuserlogin")
        Catch ex As Exception

        End Try
    End Sub
    Protected Sub Page_SaveStateComplete(sender As Object, e As EventArgs) Handles Me.SaveStateComplete
        edTableToView.CreateGroupingText()

        'edTableToView.CollectHeaderColumn(2)
        'edTableToView.CollectHeaderColumn(3)
        'edTableToView.CollectHeaderColumn(4)
        'edTableToView.CollectHeaderColumn(5)
        'edTableToView.CollectHeaderColumn(6)
        'edTableToView.CollectHeaderColumn(7)
        'edTableToView.CollectHeaderColumn(8)
        'edTableToView.CollectHeaderColumn(10)


        Dim dates() As String = prepareDateList().Split(",")

        Dim cellCnt As Integer = 0
        Dim datesCnt As Integer = 0

        Dim skip As Integer = 2
        For Each c As TableCell In edTableToView.HeaderRow.Cells
            If cellCnt > skip Then
                If c.Controls.Count > 0 And datesCnt < dates.Count Then
                    CType(c.Controls(0), LinkButton).Text = dates(datesCnt)
                End If
                datesCnt += 1
            End If
            cellCnt += 1
        Next
    End Sub
    Protected Sub action(sender As Object, e As CommandEventArgs) Handles edTableToView.ActionRequest
        Select Case e.CommandArgument
            Case "BUILD_REPORT"
                Dim sql As New StringBuilder
                '--------------  Build total report ------------------------
                sql.AppendLine(" TRUNCATE TABLE UserModules.[user].[SERK_PRODUCTION_PLAN_TOTAL] ")

                sql.AppendLine(" INSERT INTO UserModules.[user].[SERK_PRODUCTION_PLAN_TOTAL] ")
                sql.AppendLine(" ([MODEL] ,[D],[D_1],[D_2],[D_3],[D_4],[D_5],[D_6],[D_7],[D_8],[D_9],[D_10]) ")
                sql.AppendLine(" Select ")
                sql.AppendLine(" ' MODEL' ")
                sql.AppendLine(" ,isnull(D,'') ")
                sql.AppendLine(" ,isnull(D_1,'') ")
                sql.AppendLine(" ,isnull(D_2,'') ")
                sql.AppendLine(" ,isnull(D_3,'') ")
                sql.AppendLine(" ,isnull(D_4,'') ")
                sql.AppendLine(" ,isnull(D_5,'') ")
                sql.AppendLine(" ,isnull(D_6,'') ")
                sql.AppendLine(" ,isnull(D_7,'') ")
                sql.AppendLine(" ,isnull(D_8,'') ")
                sql.AppendLine(" ,isnull(D_9,'') ")
                sql.AppendLine(" ,isnull(D_10,'') ")
                sql.AppendLine(" FROM UserModules.[user].SERK_PRODUCTION_PLAN where WC = 'W/C' ")
                sql.AppendLine(" UNION ")
                sql.AppendLine(" SELECT MODEL ")
                sql.AppendLine(" ,cast(SUM(CAST(case when D = '' then 0 else D END AS NUMERIC)) as varchar) ")
                sql.AppendLine(" ,cast(SUM(CAST(case when D_1 = '' then 0 else D_1 END AS NUMERIC)) as varchar) ")
                sql.AppendLine(" ,cast(SUM(CAST(case when D_2 = '' then 0 else D_2 END AS NUMERIC)) as varchar) ")
                sql.AppendLine(" ,cast(SUM(CAST(case when D_3 = '' then 0 else D_3 END AS NUMERIC)) as varchar) ")
                sql.AppendLine(" ,cast(SUM(CAST(case when D_4 = '' then 0 else D_4 END AS NUMERIC)) as varchar) ")
                sql.AppendLine(" ,cast(SUM(CAST(case when D_5 = '' then 0 else D_5 END AS NUMERIC)) as varchar) ")
                sql.AppendLine(" ,cast(SUM(CAST(case when D_6 = '' then 0 else D_6 END AS NUMERIC)) as varchar) ")
                sql.AppendLine(" ,cast(SUM(CAST(case when D_7 = '' then 0 else D_7 END AS NUMERIC)) as varchar) ")
                sql.AppendLine(" ,cast(SUM(CAST(case when D_8 = '' then 0 else D_8 END AS NUMERIC)) as varchar) ")
                sql.AppendLine(" ,cast(SUM(CAST(case when D_9 = '' then 0 else D_9 END AS NUMERIC)) as varchar) ")
                sql.AppendLine(" ,cast(SUM(CAST(case when D_10 = '' then 0 else D_10 END AS NUMERIC)) as varchar) ")
                sql.AppendLine(" FROM UserModules.[user].SERK_PRODUCTION_PLAN ")
                sql.AppendLine(" WHERE WC <> 'W/C' ")
                sql.AppendLine(" GROUP BY MODEL ")

                sqlf.NonQuery(sql.ToString)

                Dim dates() As String = prepareDateList().Split(",")
                Dim cnt As Integer = 0
                Dim params As New Hashtable

                '--------------  Forecast detail requirements ------------------------
                cnt = 0

                For Each d As String In dates
                    params.Clear()
                    If cnt > 0 Then
                        params.Add("D", "D_" & cnt)
                    Else
                        params.Add("D", "D")
                    End If

                    'sqlf.orchestraSql(680, params)
                    orchestraSql(693, params)

                    cnt += 1
                Next
                '--------------  Forecast ASSY requirement ------------------------
                cnt = 0

                For Each d As String In dates
                    params.Clear()
                    If cnt > 0 Then
                        params.Add("D", "D_" & cnt)
                    Else
                        params.Add("D", "D" & cnt)
                    End If

                    'sqlf.orchestraSql(681, params)
                    orchestraSql(695, params) '0-------------!!!!!!!!!!!!
                    cnt += 1
                Next
                '--------------  Forecast Components requirement ------------------------
                cnt = 0

                For Each d As String In dates
                    params.Clear()
                    If cnt > 0 Then
                        params.Add("D", "D_" & cnt)
                    Else
                        params.Add("D", "D" & cnt)
                    End If

                    'sqlf.orchestraSql(682, params)
                    orchestraSql(694, params)

                    cnt += 1
                Next
                '-------------------------------------------------------------------------
                edTableToView.Message = sqlf.lastException
                edTableToView.RefreshData()
        End Select
    End Sub

    Private Sub decorateRows(ByRef row As GridViewRow) Handles edTableToView.RowDataBoundComplete
        If row.RowType = DataControlRowType.DataRow Then
            If row.Cells(2).Text.Trim = "MODEL" Then
                For Each c As TableCell In row.Cells
                    c.Visible = False
                Next
            Else
                For Each c As TableCell In row.Cells
                    If c.Text = "0" Then c.Text = ""
                Next
            End If
        End If
    End Sub
    Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)

    End Sub

    Private Function orchestraSql(queryId As Integer, params As Hashtable) As DataTable
        Try
            Dim url As String = "http://serkweb.serk.lan:8080/modulemaster/Handlers/getsqldata.ashx?id=" & queryId
            Dim f As Boolean = True
            For Each k As DictionaryEntry In params
                url &= "&"
                url &= k.Key & "=" & k.Value
            Next
            Dim wb As WebRequest
            wb = WebRequest.Create(url)
            Dim res As WebResponse = wb.GetResponse
            Dim sr As New StreamReader(res.GetResponseStream)
            Return Newtonsoft.Json.JsonConvert.DeserializeObject(Of DataTable)(sr.ReadToEnd)
        Catch ex As Exception
            Return Nothing
        End Try

    End Function

End Class