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

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


Partial Class Xml_viewTable
    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 = "By SSO: " & a.userId
            edTableToView.Message = "-"
        End If


        If Not IsNothing(Request.QueryString("tableId")) Then
            tableId = Request.QueryString("tableId")
        End If
        If Not IsNothing(Request.QueryString("t")) Then
            tableId = Request.QueryString("t")
        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

    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
        edTableToView.HeaderRow.Cells(edTableToView.HeaderRow.Cells.Count - 2).Controls.Add(New CheckBox With {.ID = "selectall", .ClientIDMode = UI.ClientIDMode.Static, .ToolTip = "@@@"})

    End Sub
    Protected Sub AddCheckbox(ByRef r As GridViewRow) Handles edTableToView.RowDataBoundComplete
        If r.RowType = DataControlRowType.DataRow Then
            r.Cells(r.Cells.Count - 2).Controls.Add(New CheckBox With {.ID = r.Cells(0).Text,
                                                    .ClientIDMode = UI.ClientIDMode.Static,
                                                    .CssClass = "check",
                                                    .ToolTip = "Check to print this row"})
        End If
    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)
    End Sub

    Protected Sub action(sender As Object, e As CommandEventArgs) Handles edTableToView.ActionRequest

        Dim targetDate As String = edTableToView.Filters("[PROD_DATE]")
        If Not IsNothing(targetDate) Then
            targetDate = targetDate.Split("N")(1).Trim("A").Trim().Trim("'")

            edTableToView.Message = "Updated data for " & targetDate
            Select Case e.CommandArgument
                Case "DATA_DOWNLOAD", "DATA_DOWNLOAD1"
                    Dim sqlf As New sqlfunctions
                    Dim sql As New StringBuilder

                    sqlf.NonQuery("DELETE FROM [UserModules].[user].[SUMMARY_ACCESSORY_REQUIREMENTS] WHERE PROD_DATE='" & targetDate & "'")

                    sql.AppendLine("WITH GMES_PLAN_W_ACCESSORY AS ( ")
                    '----------------   OLD ----------------
                    'sql.AppendLine("SELECT DISTINCT T.*, TT.MAKTX, TT.DISPO FROM ")
                    'sql.AppendLine("OPENQUERY(GMES20,' ")
                    'sql.AppendLine("SELECT DISTINCT ")
                    'sql.AppendLine("to_char(to_date(TPL.plan_ymd, ''yyyyMMdd''),''yyyy-MM-dd'') as plan_ymd ")
                    'sql.AppendLine(", TPL.PLant_code ")
                    'sql.AppendLine(", TPL.PO_NO ")
                    'sql.AppendLine(", TPL.TRNK_MODEL_CODE as MODEL ")
                    'sql.AppendLine(", TPL.MODEL_VER_NO as BOM_VER ")
                    'sql.AppendLine(", TPL.ERP_WC_CODE as LINE ")
                    'sql.AppendLine(", TPL.PO_TYPE_CODE ")
                    'sql.AppendLine(", TO_CHAR(TO_DATE(TPL.PLAN_START_DT, ''YYYYMMDDHH24MISS''),''HH24:MI:SS'') as PLAN_START_DT ")
                    'sql.AppendLine(", TO_CHAR(TPL.PLAN_QTY) AS PLAN_QTY ")
                    'sql.AppendLine(", TBOM.HRNK_MATR_CODE AS MATERIAL ")
                    'sql.AppendLine(", TMAT.MATR_DESC as DESCRIPTIOM ")
                    'sql.AppendLine("FROM TBM_PM_PO_PLAN TPL ")
                    'sql.AppendLine("LEFT JOIN tbm_md_line TLN ")
                    'sql.AppendLine("ON TPL.LINE_CODE = TLN.LINE_CODE ")
                    'sql.AppendLine("LEFT JOIN TBM_MD_MFG_BOM TBOM ")
                    'sql.AppendLine("ON TPL.PLANT_CODE = TBOM.PLANT_CODE ")
                    'sql.AppendLine("AND TPL.MODEL_CODE = TBOM.MODEL_MATR_CODE ")
                    'sql.AppendLine("AND TPL.MODEL_VER_NO = TBOM.BOM_VER ")
                    'sql.AppendLine("LEFT JOIN TBM_MD_MATR TMAT ")
                    'sql.AppendLine("ON TBOM.HRNK_MATR_CODE = TMAT.MATR_CODE ")
                    'sql.AppendLine("WHERE ")
                    'sql.AppendLine("TPL.DEL_YN = ''N'' ")
                    'sql.AppendLine("AND TPL.USE_YN = ''Y'' ")
                    'sql.AppendLine("AND TPL.TECO_YN <> ''Y'' ")
                    'sql.AppendLine("AND TPL.plan_ymd = ''" & targetDate.Replace("-", "") & "'' ")
                    'sql.AppendLine("AND TLN.MFG_PART_CODE IN (''P001'',''P008'') ")
                    'sql.AppendLine("AND TBOM.DEL_YN = ''N'' ")
                    'sql.AppendLine("AND TBOM.USE_YN = ''Y'' ")
                    'sql.AppendLine("AND TMAT.MATR_DESC LIKE ''%ASSY%'' ")
                    'sql.AppendLine("') T LEFT JOIN WMSData.dbo.TB_MATERIAL_MASTER TT ")
                    'sql.AppendLine("ON T.MATERIAL = TT.MATNR ")
                    'sql.AppendLine("WHERE TT.DISPO = 'H06' ")

                    '---------------- NEW --------------
                    sql.AppendLine("SELECT ")
                    sql.AppendLine("SUBSTRING(TPL.PLAN_YMD,1,4)+'-'+ SUBSTRING(TPL.PLAN_YMD,5,2)+'-'+SUBSTRING(TPL.PLAN_YMD,7,2) as PLAN_YMD ")
                    sql.AppendLine(", TPL.PLANT_CODE ")
                    sql.AppendLine(", TPL.PO_NO ")
                    sql.AppendLine(", TPL.TRNK_MODEL_CODE as MODEL ")
                    sql.AppendLine(", TPL.MODEL_VER_NO as BOM_VER ")
                    sql.AppendLine(", TLN.LINE_NM as LINE ")
                    sql.AppendLine(", TPL.PO_TYPE_CODE ")
                    sql.AppendLine(", SUBSTRING(TPL.PLAN_START_DT,9,2)+':'+SUBSTRING(TPL.PLAN_START_DT,11,2)+':'+SUBSTRING(TPL.PLAN_START_DT,13,2) as PLAN_START_DT ")
                    sql.AppendLine(", CAST(TPL.PLAN_QTY as INTEGER ) AS PLAN_QTY ")
                    sql.AppendLine(", TBOM.MATERIAL AS MATERIAL ")
                    sql.AppendLine(", TBOM.MATERIAL_DESCRIPTION as DESCRIPTIOM ")
                    sql.AppendLine(", TBOM.MATERIAL_DESCRIPTION as MAKTX ")
                    sql.AppendLine(", TBOM.MRP_CONTR as DISPO ")
                    sql.AppendLine(" FROM ")
                    sql.AppendLine("GMESData.dbo.TBM_PM_PO_PLAN TPL ")
                    sql.AppendLine("LEFT JOIN GmesData.dbo.TB_TBM_MD_LINE TLN ")
                    sql.AppendLine("ON TPL.LINE_CODE = TLN.LINE_CODE ")
                    sql.AppendLine("LEFT JOIN UserModules.[user].PICK_LIST_MAIN_LINE TBOM ")
                    sql.AppendLine("ON TPL.PO_NO Collate Cyrillic_General_CI_AI = '0' + TBOM.[order] ")

                    sql.AppendLine("WHERE TPL.PLAN_YMD = '" & targetDate.Replace("-", "") & "' ")
                    sql.AppendLine("AND TBOM.MRP_CONTR = 'H06' ")
                    'sql.AppendLine("AND TBOM.MRP_CONTR = 'J01'  OR TBOM.MRP_CONTR  = 'WJ4' ")
                    'sql.AppendLine("AND TBOM.MATERIAL_DESCRIPTION like 'ASSY ACCESSORY%' OR TBOM.MATERIAL_DESCRIPTION like 'ASSY MANUAL%' ")

                    sql.AppendLine("), ")
                    sql.AppendLine("GMES_PLAN AS ( ")
                    sql.AppendLine("SELECT * FROM OPENQUERY(GMES20,' ")
                    sql.AppendLine("SELECT DISTINCT ")
                    sql.AppendLine("to_char(to_date(TPL.plan_ymd, ''yyyyMMdd''),''yyyy-MM-dd'') as plan_ymd ")
                    sql.AppendLine(", TPL.PLant_code ")
                    sql.AppendLine(", TPL.ERP_WC_CODE as LINE ")
                    sql.AppendLine(", TO_CHAR(TO_DATE(TPL.PLAN_START_DT, ''YYYYMMDDHH24MISS''),''HH24:MI:SS'') as PLAN_START_DT ")
                    sql.AppendLine(", TPL.TRNK_MODEL_CODE as MODEL ")
                    sql.AppendLine(", TPL.PO_NO ")
                    sql.AppendLine(", TPL.MODEL_VER_NO as BOM_VER ")
                    sql.AppendLine(", TO_CHAR(TPL.PLAN_QTY) AS PLAN_QTY ")
                    sql.AppendLine("FROM TBM_PM_PO_PLAN TPL ")
                    sql.AppendLine("LEFT JOIN tbm_md_line TLN ")
                    sql.AppendLine("ON TPL.LINE_CODE = TLN.LINE_CODE ")
                    sql.AppendLine("WHERE ")
                    sql.AppendLine("TPL.DEL_YN = ''N'' ")
                    sql.AppendLine("AND TPL.USE_YN = ''Y'' ")
                    sql.AppendLine("AND TPL.TECO_YN <> ''Y'' ")
                    sql.AppendLine("AND TPL.plan_ymd = ''" & targetDate.Replace("-", "") & "'' ")
                    sql.AppendLine("AND TLN.MFG_PART_CODE IN (''P001'',''P008'') ')) ")
                    sql.AppendLine("INSERT INTO [UserModules].[user].[SUMMARY_ACCESSORY_REQUIREMENTS] ")
                    sql.AppendLine(" ([PROD_DATE] ")
                    sql.AppendLine(" ,[PLANT] ")
                    sql.AppendLine(" ,[LINE] ")
                    sql.AppendLine(" ,[ACCESSORY] ")
                    sql.AppendLine(" ,[DESCRIPTION] ")
                    sql.AppendLine(" ,[QUANTITY] ")
                    sql.AppendLine(" ,[MAIN_MODEL] ")
                    sql.AppendLine(" ,[BOM_VERSION]) ")
                    sql.AppendLine(" ")
                    sql.AppendLine("SELECT ")
                    sql.AppendLine("T1.plan_ymd ")
                    sql.AppendLine(", T1.PLANT_CODE ")
                    sql.AppendLine(", T1.LINE ")
                    sql.AppendLine(", T2.Material ")
                    sql.AppendLine(", T2.Maktx ")
                    sql.AppendLine(", SUM(CAST(T1.PLAN_QTY as INTEGER)) as PLAN_QTY ")
                    If e.CommandArgument = "DATA_DOWNLOAD1" Then
                        sql.AppendLine(", T1.MODEL ")
                    Else
                        sql.AppendLine(", '' ")
                    End If
                    If e.CommandArgument = "DATA_DOWNLOAD1" Then
                        sql.AppendLine(", T1.BOM_VER ")
                    Else
                        sql.AppendLine(", '' ")
                    End If
                    sql.AppendLine("from GMES_PLAN T1 ")
                    sql.AppendLine("LEFT JOIN GMES_PLAN_W_ACCESSORY T2 ")
                    sql.AppendLine("ON T1.PO_NO = T2.PO_NO ")
                    sql.AppendLine("GROUP BY ")
                    sql.AppendLine("T1.plan_ymd ")
                    sql.AppendLine(", T1.PLANT_CODE ")
                    If e.CommandArgument = "DATA_DOWNLOAD1" Then sql.AppendLine(", T1.MODEL ")
                    If e.CommandArgument = "DATA_DOWNLOAD1" Then Sql.AppendLine(", T1.BOM_VER ")
                    sql.AppendLine(", T1.LINE ")
                    sql.AppendLine(", T2.Material ")
                    sql.AppendLine(", T2.Maktx ")
                    sql.AppendLine("ORDER BY T2.Material, T1.LINE ")

                    'edTableToView.Message = sql.ToString


                    sqlf.NonQuery(sql.ToString)
                    edTableToView.RefreshData()
            End Select
        End If
    End Sub

    Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)

    End Sub

End Class