ModuleMaster/qc/tf.aspx.vb - main - ivan/ModMas

ModuleMaster/qc/tf.aspx.vb
293 строки13 KB
i.krivokon
Initial commit
13 мар 2026, 13:49
13 мар 2026, 13:4900d9fb6
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO

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

    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
        End If

        Dim tableId As String = ""
        Dim viewId As String = ""
        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

        If Not IsNothing(Request.QueryString("viewid")) Then
            viewId = Request.QueryString("viewid")
        End If
        If Not IsNothing(Request.QueryString("v")) Then
            viewId = Request.QueryString("v")
        End If

        If IsPostBack Then
            edTableToView.gridNotice.Visible = False
        End If

        edTableToView.IsReadOnly = True
        edTableToView.UserName = userid.ToUpper

        If Not IsPostBack Then
            UpdateLTAndFlags()
        End If
    End Sub

    Protected Sub DataRefreshing() Handles edTableToView.Refreshing
        UpdateLTAndFlags()
    End Sub

    Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)

    End Sub

    Protected Sub filterChanged() Handles edTableToView.GridFilterChanged

    End Sub

    Private Sub DataSelected(dt As DataTable) Handles edTableToView.DataSelected

    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
    End Sub

    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

    Private Sub UpdateLTAndFlags()
        Dim sql As New StringBuilder
        ' ---------------- Updating LT ---------------------
        sql.AppendLine("Use UserModules ")
        sql.AppendLine("MERGE [user].[NEW_MODELS_TF_TEAM_ACTIVITIES_SUMMARY_DATA] as target ")
        sql.AppendLine("USING [user].VI_TF_TEAM_LT as source ")
        sql.AppendLine("ON target.id = source.id ")
        sql.AppendLine("AND IsDate(target.PROBLEM_SOLVE_TIME) = 1 ")
        'sql.AppendLine("AND (target.INVESTIGATION_LT = '' ")
        ' sql.AppendLine("OR target.INVESTIGATION_LT IS NULL) ")
        sql.AppendLine("WHEN MATCHED THEN ")
        sql.AppendLine("UPDATE SET target.INVESTIGATION_LT = source.H + ':' + source.M + ':' + source.S; ")

        sql.AppendLine("MERGE [user].[NEW_MODELS_TF_TEAM_ACTIVITIES_SUMMARY_DATA] as target ")
        sql.AppendLine("USING [user].VI_TF_TEAM_LT as source ")
        sql.AppendLine("ON target.id = source.id ")
        sql.AppendLine("AND IsDate(target.VENDOR_RESPOSE_DATE) = 1 ")
        'sql.AppendLine("AND (target.L_T = '' ")
        'sql.AppendLine("OR target.L_T IS NULL) ")
        sql.AppendLine("WHEN MATCHED THEN ")
        sql.AppendLine("UPDATE SET target.L_T = source.LT_DAY; ")

        sql.AppendLine("UPDATE [user].[NEW_MODELS_TF_TEAM_ACTIVITIES_SUMMARY_DATA]  SET INVESTIGATION_LT = '' WHERE IsDate(PROBLEM_SOLVE_TIME) = 0 ")
        sql.AppendLine("UPDATE [user].[NEW_MODELS_TF_TEAM_ACTIVITIES_SUMMARY_DATA]  SET L_T = '' WHERE IsDate(VENDOR_RESPOSE_DATE) = 0 ")
        sqlf.NonQuery(sql.ToString)

        'edTableToView.Message = sql.ToString

        '-------------- Updating RE-OCCURANCE
        sql.Clear()
        sql.AppendLine("UPDATE ")
        sql.AppendLine("UserModules.[user].NEW_MODELS_TF_TEAM_ACTIVITIES_SUMMARY_DATA ")
        sql.AppendLine("SET REOCURRANCE = 'O' ")
        sql.AppendLine("WHERE ID IN ( ")
        sql.AppendLine("SELECT	ID ")
        sql.AppendLine("FROM	( ")
        sql.AppendLine("		SELECT ")
        sql.AppendLine("			SYMPTOM, ")
        sql.AppendLine("			SYMPTOM_GROUP, ")
        sql.AppendLine("			MATERIAL_CODE, ")
        sql.AppendLine("			VENDOR, ")
        sql.AppendLine("			id, ")
        sql.AppendLine("			REOCURRANCE, ")
        sql.AppendLine("			COUNT (*) OVER ( ")
        sql.AppendLine("				partition BY SYMPTOM, ")
        'sql.AppendLine("				SYMPTOM_GROUP, ")
        sql.AppendLine("				MATERIAL_CODE ")
        'sql.AppendLine("				,VENDOR ")
        sql.AppendLine("			) AS recurrance ")
        sql.AppendLine("		FROM UserModules.[user].NEW_MODELS_TF_TEAM_ACTIVITIES_SUMMARY_DATA ")
        sql.AppendLine("	) t WHERE recurrance > 1 and (REOCURRANCE = 'X' or REOCURRANCE = '')) ")

        sql.AppendLine("UPDATE ")
        sql.AppendLine("UserModules.[user].NEW_MODELS_TF_TEAM_ACTIVITIES_SUMMARY_DATA ")
        sql.AppendLine("SET REOCURRANCE = 'X' ")
        sql.AppendLine("WHERE ID IN ( ")
        sql.AppendLine("SELECT	ID ")
        sql.AppendLine("FROM	( ")
        sql.AppendLine("		SELECT ")
        sql.AppendLine("			SYMPTOM, ")
        sql.AppendLine("			SYMPTOM_GROUP, ")
        sql.AppendLine("			MATERIAL_CODE, ")
        sql.AppendLine("			VENDOR, ")
        sql.AppendLine("			id, ")
        sql.AppendLine("			REOCURRANCE, ")
        sql.AppendLine("			COUNT (*) OVER ( ")
        sql.AppendLine("				partition BY SYMPTOM, ")
        'sql.AppendLine("				SYMPTOM_GROUP, ")
        sql.AppendLine("				MATERIAL_CODE ")
        'sql.AppendLine("				,VENDOR ")
        sql.AppendLine("			) AS recurrance ")
        sql.AppendLine("		FROM UserModules.[user].NEW_MODELS_TF_TEAM_ACTIVITIES_SUMMARY_DATA ")
        sql.AppendLine("	) t WHERE recurrance < 2 and (REOCURRANCE = 'O' or REOCURRANCE = '') ) ")
        sqlf.NonQuery(sql.ToString)

        'edTableToView.Message &= "|" & sql.ToString

    End Sub

    Private Sub action(sender As Object, e As CommandEventArgs) Handles edTableToView.ActionRequest
        'If e.CommandName Then

        '    Dim sql As New StringBuilder
        '    sql.AppendLine("INSERT INTO UserModules.[User].[NEW_MODELS_TF_TEAM_ACTIVITIES_SUMMARY_DATA] ")
        '    sql.AppendLine("([YEAR] , ")
        '    sql.AppendLine("[MONTH] , ")
        '    sql.AppendLine("W , ")
        '    sql.AppendLine("PROBLEM_FOUND_TIME , ")
        '    sql.AppendLine("PROBLEM_SOLVE_TIME , ")
        '    sql.AppendLine("INVESTIGATION_LT , ")
        '    sql.AppendLine("SYMPTOM , ")
        '    sql.AppendLine("SYMPTOM_GROUP , ")
        '    sql.AppendLine("CAUSE_PART , ")
        '    sql.AppendLine("REOCURRANCE , ")
        '    sql.AppendLine("FOUND_LOCATION , ")
        '    sql.AppendLine("DETECTABILITY_CHECK , ")
        '    sql.AppendLine("LOCATION_ANALYSIS , ")
        '    sql.AppendLine("MODEL , ")
        '    sql.AppendLine("C_N , ")
        '    sql.AppendLine("PRODUCT , ")
        '    sql.AppendLine("PART , ")
        '    sql.AppendLine("LINE , ")
        '    sql.AppendLine("DEFECT_CAUSE , ")
        '    sql.AppendLine("MATERIAL_CODE , ")
        '    sql.AppendLine("VENDOR , ")
        '    sql.AppendLine("[DATE] , ")
        '    sql.AppendLine("PE_CIRCUIT , ")
        '    sql.AppendLine("PE_MECHA , ")
        '    sql.AppendLine("PRODUCTION , ")
        '    sql.AppendLine("IQC , ")
        '    sql.AppendLine("VENDOR_955 , ")
        '    sql.AppendLine("VENDOR_RESPOSE_DATE , ")
        '    sql.AppendLine("L_T , ")
        '    sql.AppendLine("STATUS , ")
        '    sql.AppendLine("DUMMY_TEST , ")
        '    sql.AppendLine("PHOTO , ")
        '    sql.AppendLine("FILES , ")
        '    sql.AppendLine("NEW_FROM_GMES ) ")
        '    sql.AppendLine("SELECT ")
        '    sql.AppendLine("datepart(YEAR,convert(DATE,DEFECT_DATE,112)) AS YEAR, ")
        '    sql.AppendLine("(SELECT FIELDVALUE From UserModules.dbo.FieldsValues WHERE ID = (DATEPART(MONTH,convert(DATE,DEFECT_DATE,112)) + 4748)) AS MON, ")
        '    sql.AppendLine("DATEPART(WEEK,convert(DATE,DEFECT_DATE,112)) as WEEK, ")
        '    sql.AppendLine("convert(varchar,convert(datetime,stuff(stuff(stuff(DEFECT_DATETIME, 9, 0, ' '), 12, 0, ':'), 15, 0, ':')),20) as [Problem found time] , ")
        '    sql.AppendLine("convert(varchar,convert(datetime,stuff(stuff(stuff(REPAIR_DATETIME, 9, 0, ' '), 12, 0, ':'), 15, 0, ':')),20) as [Problem solve time], ")
        '    sql.AppendLine("'' as [Investigation LT], ")
        '    sql.AppendLine("REMARK as [Symptom], ")
        '    sql.AppendLine("'' as [Symptom group], ")
        '    sql.AppendLine("'' as [Cause Part], ")
        '    sql.AppendLine("'X' as [Reocurrance], ")
        '    sql.AppendLine("'' as [Found Location], ")
        '    sql.AppendLine("'' as [Detectability check], ")
        '    sql.AppendLine("'Production' as [Location Analysis], ")
        '    sql.AppendLine("MODEL_NUMBER as [Model], ")
        '    sql.AppendLine("CONTROL_NUMBER as [C/N], ")
        '    sql.AppendLine("CASE (SELECT PROD_GROUP FROM GMESData.dbo.TB_VWM_BSC_MODEL T WHERE T.MODEL_NUMBER = TT.MODEL_NUMBER) ")
        '    sql.AppendLine("WHEN 'VT' THEN 'LED' ")
        '    sql.AppendLine("WHEN 'VM' THEN 'MON' ")
        '    sql.AppendLine("WHEN 'LW' THEN 'W/M' ")
        '    sql.AppendLine("ELSE '???' ")
        '    sql.AppendLine("END as [Product], ")
        '    sql.AppendLine("isnull((SELECT MATR_DESC FROM GMESData.[dbo].TB_TBM_MD_MATR T WHERE T.MATR_CODE = TT.PART_CODE),'-') as [Defective parts], ")
        '    sql.AppendLine("TT.LINE, ")
        '    sql.AppendLine("Replace((SELECT DEFT_CAUSE_NM FROM GMESData.[dbo].TB_TBC_MD_DEFT_CAUSE T WHERE T.DEFT_CAUSE_CODE = TT.CAUSE_CODE),'Pannel','Panel') as [Defect cause],")
        '    sql.AppendLine("isnull(PART_CODE,'-') as [Material Code], ")
        '    sql.AppendLine(" ")
        '    sql.AppendLine("CASE SUBSTRING(DEFECT_PART_CN,1,4) ")
        '    sql.AppendLine("WHEN 'BJGP' THEN 'AUO' ")
        '    sql.AppendLine("WHEN 'HU09' THEN 'SEH' ")
        '    sql.AppendLine("WHEN 'SK09' THEN 'SESK' ")
        '    sql.AppendLine("WHEN 'MY09' THEN 'SDMA' ")
        '    sql.AppendLine("WHEN 'BBM8' THEN 'BOE' ")
        '    sql.AppendLine("ELSE ")
        '    sql.AppendLine("isnull((SELECT VEND_NM FROM GMESData.[dbo].TB_TBM_MD_VEND T WHERE T.VEND_CODE = TT.VEND_CODE),'-') ")
        '    sql.AppendLine("END ")
        '    sql.AppendLine("as [Vendor], ")
        '    sql.AppendLine(" ")
        '    sql.AppendLine("convert(varchar, convert(DATE,DEFECT_DATE,112), 121) as [Request date], ")
        '    sql.AppendLine("'-','-','Repaired','-','-', ")
        '    sql.AppendLine("convert(varchar, convert(DATE,DEFECT_DATE,112), 121) as [Vendor response date], ")
        '    sql.AppendLine("'' as [L/T], ")
        '    sql.AppendLine("'Closed' as [Status], ")
        '    sql.AppendLine("'No need' as [Dummy test], ")
        '    sql.AppendLine("'' as [Photo], ")
        '    sql.AppendLine("'' as [Files], ")
        '    sql.AppendLine("'Y' as [New from GMES] ")
        '    sql.AppendLine("FROM GMESData.[dbo].[TB_VWP_QMS_QUALITY] TT ")
        '    sql.AppendLine("LEFT JOIN UserModules.[User].[NEW_MODELS_TF_TEAM_ACTIVITIES_SUMMARY_DATA] TTT ")
        '    sql.AppendLine("ON TT.CONTROL_NUMBER collate Cyrillic_General_CI_AI = TTT.C_N collate Cyrillic_General_CI_AI ")
        '    sql.AppendLine("AND convert(varchar,convert(datetime,stuff(stuff(stuff(DEFECT_DATETIME, 9, 0, ' '), 12, 0, ':'), 15, 0, ':')),20 ) collate Cyrillic_General_CI_AI ")
        '    sql.AppendLine("= TTT.PROBLEM_FOUND_TIME collate Cyrillic_General_CI_AI ")
        '    sql.AppendLine("WHERE ")
        '    sql.AppendLine("defect_date >= replace(convert(varchar, getdate()-1,111),'/','') -- '20150626' ")
        '    sql.AppendLine("AND SECT in ('P001', 'P008') ")
        '    sql.AppendLine("AND DEFECT_DECISION = 'R' ")
        '    sql.AppendLine("AND REPAIR_FLAG = 'Y' ")
        '    sql.AppendLine("AND USE_FLAG = 'Y' ")
        '    sql.AppendLine("AND IsNULL(TTT.ID,0) = 0 ")
        '    sql.AppendLine("Order by [Problem found time] ")
        '    sql.AppendLine(" ")

        '    sqlf.NonQuery(sql.ToString)


        '    UpdateLTAndFlags()

        '    edTableToView.RefreshData(True)
        '    edTableToView.Refresh()

        'End If
    End Sub

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

    Protected Sub edTableToView_PreRender(sender As Object, e As System.EventArgs) Handles edTableToView.PreRender

    End Sub

    Protected Sub Page_Unload(sender As Object, e As EventArgs) Handles Me.Unload

    End Sub

End Class