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

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

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.76", 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 viewId As String = ""
        If Not IsNothing(Request.QueryString("debug")) Then
            edTableToView.debugMode = (Request.QueryString("debug") = "true")
        End If
        If Not IsPostBack Then
            If Not IsNothing(Request.QueryString("p")) Then
                If IsNumeric(Request.QueryString("p")) Then
                    edTableToView.PageSize = Request.QueryString("p")
                End If
            End If
            refreshOqcList()
        End If
        edTableToView.IsReadOnly = True
        edTableToView.UserName = userid.ToUpper
        If Not IsNothing(Request.QueryString("viewonly")) Then
            If Request.QueryString("viewonly").ToString.ToUpper = "YES" Then
                edTableToView.UserName = ""
            End If
        End If
        If IsNumeric(viewId) Then
            edTableToView.ViewID = viewId
        Else
            edTableToView.ViewID = -1
        End If
    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
        edTableToView.SubReport.Controls.Add(createSubreport(edTableToView.TableId))
        Dim script As String = sqlf.GetScalar("SELECT Gridcommand_handler FROM UserModules.dbo.ModuleTables WHERE id=" & edTableToView.TableId)
        If script <> "" Then
            gridcommandhandlerscript.Controls.Add(New Literal With {.Text = "<script type=""text/javascript"">" & script & "</script>"})
        End If
    End Sub

    Private Function createSubreport(tid As String) As GridView
        Dim gv As New GridView
        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
            Dim dt As DataTable = sqlf.GetData(query)
            gv.DataSource = dt
            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()
    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

    Private Sub Xml_viewTable_PreRenderComplete(sender As Object, e As EventArgs) Handles Me.PreRenderComplete
        edTableToView.CollectHeaderColumn(2, 4)
        edTableToView.CollectHeaderColumn(4, 4)
        edTableToView.CollectHeaderColumn(5, 4)
        edTableToView.CollectHeaderColumn(6, 4)
        edTableToView.CollectHeaderColumn(7, 4)
        edTableToView.CollectHeaderColumn(8, 4)
        edTableToView.CollectHeaderColumn(9, 4)
        'edTableToView.CollectHeaderColumn(10, 4)
        'edTableToView.CollectHeaderColumn(12, 4)
        'edTableToView.CollectHeaderColumn(18, 4)
        'edTableToView.CollectHeaderColumn(19, 4)
        'edTableToView.CollectHeaderColumn(20, 4)
        edTableToView.CollectHeaderColumn(21, 4)
        edTableToView.CollectHeaderColumn(22, 4)
    End Sub

    Private Sub refreshOqcList()
        Dim sql As New StringBuilder
        '--> TMP table copy
        sql.appendline("TRUNCATE TABLE [UserModules].[user].[OQC_PENDING_LOT_TEMP] ")
        Sql.appendline("INSERT INTO [UserModules].[user].[OQC_PENDING_LOT_TEMP] ")
        Sql.appendline(" ([LOT_DATE] ")
        Sql.appendline(" ,[PO] ")
        Sql.appendline(" ,[OQC_LOT_NO] ")
        Sql.appendline(" ,[LINE] ")
        Sql.appendline(" ,[MODEL] ")
        Sql.appendline(" ,[OQC_LOT_SIZE] ")
        Sql.appendline(" ,[TOTAL_PROD_QTY] ")
        Sql.appendline(" ,[TTL_DIFFERENCE] ")
        Sql.appendline(" ,[LOT_BY_PO_QTY] ")
        Sql.appendline(" ,[PO_PROD_QTY] ")
        Sql.appendline(" ,[PO_DIFFERENCE] ")
        Sql.appendline(" ,[START_SN] ")
        Sql.appendline(" ,[END_SN] ")
        Sql.appendline(" ,[LAST_SN_SCAN_TIME] ")
        Sql.appendline(" ,[WAIT_SINCE_LAST_SCAN] ")
        Sql.appendline(" ,[SHIPMENT_PRIORITY] ")
        Sql.appendline(" ,[NOT_REPAIRED_SN_LIST] ")
        sql.AppendLine(" ,[PRIORITY_REMARK]) ")

        sql.AppendLine("SELECT [LOT_DATE], [PO] ")
        sql.appendline(" ,[OQC_LOT_NO] ")
        Sql.appendline(" ,[LINE] ")
        Sql.appendline(" ,[MODEL] ")
        Sql.appendline(" ,[OQC_LOT_SIZE] ")
        Sql.appendline(" ,[TOTAL_PROD_QTY] ")
        Sql.appendline(" ,[TTL_DIFFERENCE] ")
        Sql.appendline(" ,[LOT_BY_PO_QTY] ")
        Sql.appendline(" ,[PO_PROD_QTY] ")
        Sql.appendline(" ,[PO_DIFFERENCE] ")
        Sql.appendline(" ,[START_SN] ")
        Sql.appendline(" ,[END_SN] ")
        Sql.appendline(" ,[LAST_SN_SCAN_TIME] ")
        Sql.appendline(" ,[WAIT_SINCE_LAST_SCAN] ")
        Sql.appendline(" ,[SHIPMENT_PRIORITY] ")
        Sql.appendline(" ,[NOT_REPAIRED_SN_LIST] ")
        Sql.appendline(" ,[PRIORITY_REMARK] ")
        sql.AppendLine("FROM [UserModules].[user].[OQC_PENDING_LOT] ")

        '--> Main table data interface from GMES 
        sql.AppendLine("TRUNCATE TABLE [UserModules].[user].[OQC_PENDING_LOT] ")

        sql.AppendLine("INSERT INTO [UserModules].[user].[OQC_PENDING_LOT] ")
        sql.appendline(" ([LOT_DATE] ")
        Sql.appendline(" ,[PO] ")
        Sql.appendline(" ,[OQC_LOT_NO] ")
        Sql.appendline(" ,[LINE] ")
        Sql.appendline(" ,[MODEL] ")
        Sql.appendline(" ,[OQC_LOT_SIZE] ")
        Sql.appendline(" ,[TOTAL_PROD_QTY] ")
        Sql.appendline(" ,[TTL_DIFFERENCE] ")
        Sql.appendline(" ,[LOT_BY_PO_QTY] ")
        Sql.appendline(" ,[PO_PROD_QTY] ")
        Sql.appendline(" ,[PO_DIFFERENCE] ")
        Sql.appendline(" ,[START_SN] ")
        Sql.appendline(" ,[END_SN] ")
        Sql.appendline(" ,[LAST_SN_SCAN_TIME] ")
        Sql.appendline(" ,[WAIT_SINCE_LAST_SCAN] ")
        Sql.appendline(" ,[SHIPMENT_PRIORITY] ")
        sql.AppendLine(" ,[NOT_REPAIRED_SN_LIST] ")
        sql.AppendLine(" ,[LOT_COMPLETE_TIME] ")
        sql.AppendLine(" ,[WAIT_SINCE_LOT_COMPLETE] ")
        sql.AppendLine(" ,[NOT_PACKED_SN_LIST] ,  [DETAIL_FLAG],  [ISS_SN], [MARK]) ")

        sql.AppendLine("SELECT T.* ")
        'sql.AppendLine(" ,  (SELECT STUFF((SELECT ', ' +  PRODC_MAGT_NO FROM CATV.DBO.[TB_CATV_ISS_SMPLING_RSLT] where PRODC_MAGT_NO COLLATE Cyrillic_General_CI_AI between T.START_NO and T.END_NO for XML path('')), 1,1,'') ) AS ISS_SN_LIST ")
        '--> ISS SN List
        sql.AppendLine(" ,  (SELECT STUFF((SELECT ', ' +  t1.PRODC_MAGT_NO COLLATE Cyrillic_General_CI_AI + ':' + isnull(t2.INSPECTION_RESULT,'')  FROM CATV.DBO.[TB_CATV_ISS_SMPLING_RSLT]  t1 LEFT JOIN UserModules.[user].OFF_LINE_INSPECTION_LIST t2 on substring(t1.prodc_magt_no,1,14)  COLLATE Cyrillic_General_CI_AI = t2.SN where t1.PRODC_MAGT_NO COLLATE Cyrillic_General_CI_AI between T.START_NO and T.END_NO for XML path('')), 1,1,'') ) AS ISS_SN_LIST ")
        'sql.AppendLine(", ' ' AS ISS_SN_LIST ")
        '-->MARK for long pending lot 30~60 - Yellow, over 60 - Red

        sql.AppendLine(", CASE ")
        sql.AppendLine("WHEN ISNULL(DATEDIFF(MINUTE, CONVERT(DATETIME, NULLIF(REPLACE(LOT_COMPLETE_TIME, ' ', 'T'), '-'), 126 ), getdate()),0) < 30 THEN '' ")
        sql.AppendLine("WHEN ISNULL(DATEDIFF(MINUTE, CONVERT(DATETIME, NULLIF(REPLACE(LOT_COMPLETE_TIME, ' ', 'T'), '-'), 126 ), getdate()),0) < 60 THEN 'Y' ")
        sql.AppendLine("ELSE 'R' END AS MARK ")


        sql.AppendLine(" From OPENQUERY(GMES20,' ")
        sql.appendline("SELECT ")
        Sql.appendline("LOT_DATE ")
        Sql.appendline(", PO_NUMBER ")
        Sql.appendline(", LOT_NO ")
        Sql.appendline(", LINE ")
        Sql.appendline(", MODEL_NUMBER ")
        sql.AppendLine(", to_char(LOT_SIZE) LOT_SIZE ")
        sql.appendline(", TTL_PROD_QTY ")
        Sql.appendline(", DIF_TTL ")
        Sql.appendline(", to_char(LOT_PO_SIZE) LOT_PO_SIZE ")
        Sql.appendline(", PO_PROD_QTY ")
        Sql.appendline(", LOT_PO_SIZE - PO_PROD_QTY AS DIF_PO ")
        Sql.appendline(", START_NO ")
        Sql.appendline(", END_NO ")
        sql.AppendLine(", TO_CHAR(LAST_PROD_SCAN , ''yyyy-MM-dd HH24:mi:ss'') as LAST_PROD_SCAN ")
        sql.appendline(", CASE LOT_PO_SIZE - PO_PROD_QTY ")
        sql.AppendLine("		WHEN 0 THEN ''-'' ELSE TRIM(BOTH '' '' FROM TRIM(TRAILING ''.'' FROM TRIM(BOTH ''0'' FROM TRIM(LEADING ''+'' FROM TO_CHAR(SYSDATE - LAST_PROD_SCAN, ''HH24:mi:SS''))))) END AS WAIT_FOR_PROD_TIME")
        sql.AppendLine(", '''' AS SHIPMENT_PRIORITY ")
        sql.AppendLine(", CASE LOT_PO_SIZE - PO_PROD_QTY WHEN 0 THEN NULL ELSE ( SELECT LISTAGG(PRODC_MAGT_NO,'', '') WITHIN GROUP (ORDER BY PRODC_MAGT_NO) ")
        sql.appendline("		FROM TBM_QM_PROC_DEFT Q WHERE ")
        Sql.appendline("			Q.PO_NO = LS.PO_NUMBER ")
        Sql.appendline("			AND Q.RPR_YN = ''N'' ")
        Sql.appendline("			AND Q.DEFT_TYPE_DECI_CODE = ''R'' ")
        Sql.appendline("			AND Q.DEL_YN = ''N'' ")
        Sql.appendline("			AND q.USE_YN = ''Y'' ")
        sql.AppendLine("			AND Q.IDC_REFL_YN = ''Y'') END  AS SN_LIST ")

        sql.AppendLine(",CASE DIF_TTL ")
        sql.AppendLine("WHEN 0 THEN LAST_VALUE(TO_CHAR(LAST_PROD_SCAN , ''yyyy-MM-dd HH24:mi:ss'')) OVER (PARTITION BY LOT_DATE,LINE, LOT_NO ORDER BY TO_CHAR(LAST_PROD_SCAN , ''yyyy-MM-dd HH24:mi:ss'') ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) ")
        sql.AppendLine("ELSE ''-'' ")
        sql.AppendLine("END AS LOT_COMPLETE_TIME ")
        '--> Last scan time calculation 
        sql.AppendLine(",CASE DIF_TTL ")
        sql.AppendLine("WHEN 0 THEN ")
        sql.AppendLine("TRIM(BOTH '' '' FROM TRIM(TRAILING ''.'' FROM TRIM(BOTH ''0'' FROM TRIM(LEADING ''+'' FROM TO_CHAR(SYSDATE - LAST_VALUE(LAST_PROD_SCAN ) OVER (PARTITION BY LOT_DATE,LINE, LOT_NO ORDER BY LAST_PROD_SCAN ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ), ''HH24:mi:ss''))))) ")
        sql.AppendLine("ELSE ")
        sql.AppendLine("''-'' ")
        sql.AppendLine("END AS WAIT_SINCE_LOT_COMPLETE ")
        '--> not packed SN list 
        sql.AppendLine(" , CASE LOT_PO_SIZE - PO_PROD_QTY WHEN 0 THEN NULL ELSE ( ")
        sql.AppendLine(" SELECT LISTAGG(Q.PRODC_MAGT_NO,'', '') WITHIN GROUP (ORDER BY Q.PRODC_MAGT_NO) ")
        sql.AppendLine(" FROM TBM_QM_PROC_DEFT Q ")
        sql.AppendLine(" LEFT JOIN TBS_PM_PROC_PRGS P ")
        sql.AppendLine(" ON Q.PO_NO = P.PO_NO ")
        sql.AppendLine(" AND Q.PRODC_MAGT_NO = P.PROD_SN ")
        sql.AppendLine(" WHERE ")
        sql.AppendLine(" Q.PO_NO = LS.PO_NUMBER ")
        sql.AppendLine(" AND Q.RPR_YN = ''Y'' ")
        sql.AppendLine(" AND Q.DEFT_TYPE_DECI_CODE = ''R'' ")
        sql.AppendLine(" AND Q.DEL_YN = ''N'' ")
        sql.AppendLine(" AND q.USE_YN = ''Y'' ")
        sql.AppendLine(" AND Q.IDC_REFL_YN = ''Y'' ")
        sql.AppendLine(" AND P.PROD_SN IS NULL ")
        sql.AppendLine(" ) END  AS NOT_PACKED_SN ")
        'sql.AppendLine(" , LS.DETAIL_FLAG ")
        sql.AppendLine(" , (SELECT DECODE(COUNT(*),0,''N'',''Y'') FROM VWM_QM_OQC_LOT_INSPECT_INFO V WHERE V.LOT_NO = LS.LOT_NO AND STRING_VALUE IS NOT NULL) as DETAIL_FLAG ")

        sql.appendline("FROM ( ")
        Sql.appendline("SELECT ")
        Sql.appendline("LOT_DATE ")
        Sql.appendline(", PO_INFO.PO_NUMBER ")
        Sql.appendline(", LOT_LIST.LOT_NO ")
        Sql.appendline(", LOT_LIST.LINE ")
        Sql.appendline(", LOT_LIST.MODEL_NUMBER ")
        Sql.appendline(", LOT_LIST.LOT_SIZE ")
        Sql.appendline(", PO_INFO.START_NO ")
        Sql.appendline(", PO_INFO.END_NO ")
        Sql.appendline(", TTL_PROD_QTY ")
        Sql.appendline(", ( select count(*) from VWP_OQC_LOT_SET_CNST cnt where CNT.lot_no = LOT_LIST.LOT_NO and CNT.PO_NO = PO_INFO.PO_NUMBER)AS PO_PROD_QTY ")
        Sql.appendline(", PO_INFO.LOT_PO_SIZE ")
        Sql.appendline(", LOT_LIST.LOT_SIZE - TTL_PROD_QTY as DIF_TTL ")
        sql.AppendLine(", (SELECT MAX(ACRS_SUMR_TSP) FROM TBS_PM_PROC_PRGS WHERE PROD_SN = PO_INFO.END_NO) as LAST_PROD_SCAN ")
        sql.AppendLine(" , LOT_LIST.DETAIL_FLAG")
        sql.appendline(" FROM ( ")
        Sql.appendline("SELECT DISTINCT ")
        Sql.appendline("LOT_DATE , LINE, LOT_NO, MODEL_NUMBER, IBI_FLAG, DETAIL_FLAG, AUDIT_FLAG, LOT_SIZE ")
        Sql.appendline(", LAST_VALUE(JUDGE_FLAG) OVER (PARTITION BY LOT_NO ORDER BY INSP_STEP ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS lv ")
        sql.AppendLine(", ( select count(*) from VWP_OQC_LOT_SET_CNST cnt where CNT.lot_no = T.LOT_NO ) AS TTL_PROD_QTY ")
        sql.AppendLine("FROM ( ")
        sql.appendline("select ")
        Sql.appendline("LOT_DATE ")
        sql.AppendLine(", LINE , LOT_NO, INSP_STEP, MODEL_NUMBER, IBI_FLAG,  AUDIT_FLAG, INSPECT_DATE, INSPECTOR, JUDGE_FLAG, LOT_SIZE, SAMPLE_SIZE, DEFECT_SIZE, DETAIL_FLAG ")
        sql.appendline("from VWM_OQC_LOT_INFO ")
        Sql.appendline("where ")
        Sql.appendline("LOT_DATE > to_char(sysdate - 7, ''yyyyMMdd'') ")
        Sql.appendline(") T ) LOT_LIST ")
        Sql.appendline("LEFT JOIN VWP_OQC_LOT_SERIAL_INFO PO_INFO ")
        Sql.appendline("on LOT_LIST.LOT_NO = PO_INFO.LOT_NO ")
        Sql.appendline("WHERE LOT_LIST.LV <> ''Y'' ")
        Sql.appendline("AND TTL_PROD_QTY <> 0 ")
        Sql.appendline(") LS ")
        Sql.appendline("ORDER BY ")
        Sql.appendline("LOT_DATE ")
        Sql.appendline(", LINE ")
        sql.AppendLine(", LOT_NO') T WHERE PO_NUMBER LIKE '01%' ")

        sql.appendline("MERGE [UserModules].[user].[OQC_PENDING_LOT] as trg ")
        Sql.appendline("USING [UserModules].[user].[OQC_PENDING_LOT_TEMP] as src ")
        Sql.appendline("on trg.OQC_LOT_NO = src.OQC_LOT_NO ")
        Sql.appendline("AND trg.PO = src.PO ")
        Sql.appendline("WHEN MATCHED THEN ")
        Sql.appendline("UPDATE SET trg.SHIPMENT_PRIORITY = src.SHIPMENT_PRIORITY ")
        sql.AppendLine(", trg.PRIORITY_REMARK = src.PRIORITY_REMARK;")
        sqlf.NonQuery(sql.ToString)
        edTableToView.Message = sqlf.lastException
    End Sub

    Protected Sub refreshing() Handles edTableToView.Refreshing
        refreshOqcList()
    End Sub
    Protected Sub decorate(ByRef r As GridViewRow) Handles edTableToView.RowDataBoundComplete
        If r.RowType = DataControlRowType.DataRow Then
            'If r.Cells(12).Text = "0" Then
            '    r.Cells(12).Style.Add("background", "lime")
            'End If
            If r.Cells(9).Text = "0" Then
                r.Cells(9).Style.Add("background", "lime")
            End If
            If r.Cells(10).Text = "Y" Then
                r.Cells(10).Style.Add("background", "lime")
            End If
            'ISS list checking by OFF-line inspection
            Dim isslist As String()
            Dim isslistUpdated As New LiteralControl
            isslistUpdated.Text = ""
            isslist = r.Cells(20).Text.Split(",")
            For Each isssn As String In isslist
                If isssn <> "" And Not (IsNothing(isssn)) Then
                    If isssn.Contains(":") Then
                        If isslistUpdated.Text <> "" Then
                            isslistUpdated.Text &= ","
                        End If
                        If isssn.Split(":")(1) <> "" Then
                            isslistUpdated.Text &= "<span style='color: green'>" & isssn.Split(":")(0) & "</span>"
                        Else
                            isslistUpdated.Text &= "<span style='color: red'>" & isssn.Split(":")(0) & "</span>"
                        End If
                    End If
                End If
            Next
            r.Cells(20).Controls.Add(isslistUpdated)
        End If
    End Sub
End Class