ModuleMaster/pc/waitstock.aspx.vb - main - ivan/ModMas

ModuleMaster/pc/waitstock.aspx.vb
266 строк13 KB
i.krivokon
Initial commit
13 мар 2026, 13:49
13 мар 2026, 13:4900d9fb6
Imports System.Data
Imports System.Data.SqlClient
Imports sharedClasses.dbFunctions

Partial Class waitStock
    Inherits System.Web.UI.Page

    Private sqlf As New 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
        Dim tableId As String = ""
        If Not IsNothing(Request.QueryString("tableId")) Then
            tableId = Request.QueryString("tableId")
        End If
        If userid = "" Then
            authorize("SSO")
            userid = a.userId
            edTableToView.Message = "By SSO: " & a.userId
        End If

        edTableToView.IsReadOnly = True
        edTableToView.UserName = userid.ToUpper
        pendingsEditableGrid.IsReadOnly = True
        pendingsEditableGrid.UserName = userid.ToUpper
        gmesEditableGrid.IsReadOnly = True
        gmesEditableGrid.UserName = userid.ToUpper
        zrlec53130EditableGrid.IsReadOnly = True
        zrlec53130EditableGrid.UserName = userid.ToUpper
        mb52EditableGrid.IsReadOnly = True
        mb52EditableGrid.UserName = userid.ToUpper

    End Sub

    Protected Sub edtabletoview_rowDataBound(ByRef row As GridViewRow) Handles edTableToView.RowDataBoundComplete
        'Dim rowView As DataRowView = CType(row.DataItem, DataRowView)
        'Dim cellCount As Integer = 0
        'For Each c As TableCell In row.Cells
        '    Try
        '        If row.RowType = DataControlRowType.Footer Then
        '            c.Attributes.Add("field", "Total|" & edGrid.config.GetParam(cellCount, "NAME"))
        '        Else
        '            c.CssClass = "click"
        '            c.Attributes.Add("tid", 470)
        '            c.Attributes.Add("field", edGrid.config.GetParam(cellCount, "NAME"))
        '            c.Attributes.Add("type", edGrid.config.GetParam(cellCount, "Fieldtype"))
        '            c.Attributes.Add("rowid", rowView("id"))
        '        End If
        '    Catch ex As Exception

        '    End Try

        '    cellCount += 1
        'Next
    End Sub

    Protected Sub edTableToView_SavedElement(tableName As String, fieldName As String, rowId As String, value As String, fieldType As String, byButton As String, text As String) Handles edTableToView.SavedElement
        Dim sql As New StringBuilder
        Dim model = sqlf.GetScalar("SELECT MODEL FROM UserModules.[user].FG_INVENTORY WHERE id = " & rowId)
        If value.Trim = "" Then
            sqlf.NonQuery("DELETE FROM UserModules.[user].FG_INVENTORY_No_PENDINGS WHERE [MODEL] = '" & model & "'")
        Else
            sql.Append(" UPDATE UserModules.[user].FG_INVENTORY_No_PENDINGS SET QTY=@QTY WHERE MODEL = @MODEL and LOCATION = @LOCATION")
            Dim com As New SqlCommand
            com.CommandText = sql.ToString
            With com.Parameters
                .AddWithValue("QTY", value)
                Select Case fieldName
                    Case "NO_PR"
                        .AddWithValue("LOCATION", "NO PR")
                    Case "QC_IBI"
                        .AddWithValue("LOCATION", "QC/IBI")
                    Case "LINE", "REJECT"
                        .AddWithValue("LOCATION", fieldName)
                    Case Else
                        Exit Sub
                End Select
                .AddWithValue("MODEL", model)
            End With
            If sqlf.NonQuery(com) = 0 Then
                com.CommandText = "INSERT INTO UserModules.[user].FG_INVENTORY_No_PENDINGS (QTY,MODEL,LOCATION) VALUES (@QTY,@MODEL,@LOCATION) "
                sqlf.NonQuery(com)
            End If

        End If
    End Sub

    Protected Sub edTableToView_ActionRequest(sender As Object, e As CommandEventArgs) Handles edTableToView.ActionRequest
        'Select Case e.CommandArgument
        '    Case "GET_DATA_GMES"
        '        GetDataFromGMES()
        '    Case "GET_DATA_GERP1"
        '        ActivateJob(6)
        '    Case "GET_DATA_GERP2"
        '        ActivateJob(4)
        '    Case "UPDATE_ALL"
        '        Dim sql As New StringBuilder
        '        sql.Append("BEGIN TRANSACTION ")
        '        sql.Append("UPDATE usermodules.[user].FG_INVENTORY SET [DELETE] = 'Y' ")
        '        sql.Append(" ")
        '        sql.Append("INSERT INTO usermodules.[user].FG_INVENTORY ")
        '        sql.Append("(MODEL, PHYSICAL_INVENTORY_WH_STOCK_, NO_PR, QC_IBI, LINE, REJECT, BUFFER_ZONE, TOTAL, TOTAL_STOCK_P901_FC01__FC03___P903_FC01_03_,DIFFERENCES,PLANT) ")
        '        sql.Append(" ")
        '        sql.Append("select ")
        '        sql.Append("ISNULL(ISNULL(T1.MODEL, T2.MATERIAL),T3.MATERIAL), ")
        '        sql.Append("T1.PHYSICAL_INVENTORY_WH_STOCK_, ")
        '        sql.Append("T1.NO_PR, T1.QC_IBI, T1.LINE, T1.REJECT, ")
        '        sql.Append("T2.ACT__QTY as BUFFER_ZONE, ")
        '        sql.Append("T1.TOTAL, ")
        '        sql.Append("T3.TTL, ")
        '        sql.Append("T1.DIFFERENCES, ")
        '        sql.Append("ISNULL(T1.PLANT, T3.PLNT) ")
        '        sql.Append("FROM ")
        '        sql.Append("usermodules.[user].FG_INVENTORY T1 ")
        '        sql.Append("FULL JOIN usermodules.[user].FG_INVENTORY_No_ZRLEC53130 T2 ")
        '        sql.Append("ON T1.MODEL = T2.MATERIAL ")
        '        sql.Append("FULL JOIN ")
        '        sql.Append("( ")
        '        sql.Append("SELECT ")
        '        sql.Append("PLNT, MATERIAL, ")
        '        sql.Append("SUM(UNRESTRICTED + TRANSIT_TRANSF_ + INQUALITYINSP_+ RESTRICTEDNoUSE + BLOCKED + RETURNS) as TTL ")
        '        sql.Append("FROM usermodules.[user].FG_INVENTORY_No_MB52 ")
        '        sql.Append("WHERE SLOC in ('FC01', 'FC03') ")
        '        sql.Append("GROUP BY PLNT, MATERIAL ")
        '        sql.Append(") T3 ")
        '        sql.Append("ON ISNULL(T1.MODEL, T2.MATERIAL) = T3.MATERIAL ")
        '        sql.Append("ORDER BY ISNULL(ISNULL(T1.MODEL, T2.MATERIAL),T3.MATERIAL) ")
        '        sql.Append(" ")
        '        sql.Append("DELETE FROM usermodules.[user].FG_INVENTORY WHERE [DELETE] = 'Y' ")
        '        sql.Append("COMMIT ")

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

    Private Sub ActivateJob(jobId As String)
        Dim com As New SqlCommand
        com.CommandText = "UPDATE UserModules.[user].WA_JOB_LIST SET RUN_IMMEDIATELY='Y' WHERE ID=" & jobId
        sqlf.NonQuery(com)
    End Sub

    Private Sub GetDataFromGMES()

        Dim sql As New StringBuilder

        sql.Append("truncate table usermodules.[user].FG_INVENTORY_No_GMES ")
        sql.Append("insert into usermodules.[user].FG_INVENTORY_No_GMES (PLANT, MODEL, TEMP_LOADING, WAIT_STOCK, UPDATED, STLOC ) ")

        sql.Append("select TT.*, GETDATE(), 'FC01' from openquery(GMES20, ' ")

        sql.Append(" SELECT ")
        sql.Append(" NVL(TEMP.PLANT_CODE, WT.PLANT_CODE) as PLANT, ")
        sql.Append(" NVL(TEMP.MODEL_CODE, WT.MODEL_CODE) as MODEL, ")
        sql.Append(" TEMP.TEMP, WT.QTY as WTQT ")
        sql.Append(" FROM ( ")
        sql.Append(" select ")
        sql.Append(" plant_code, ")
        sql.Append(" MODEL_CODE, ")
        sql.Append(" sum(set_qty) as TEMP ")
        sql.Append(" from TBP_PM_PACK_ACRS ")
        sql.Append(" where ACRS_YMD > to_char(sysdate - 7,''yyyyMMdd'') AND ")
        sql.Append(" OUT_STATUS_CODE = 40 AND STRG_ID <> ''FL01'' ")
        sql.Append(" and del_yn = ''N'' ")
        sql.Append(" and use_yn = ''Y'' ")
        sql.Append(" GROUP BY plant_code, MODEL_CODE ")
        sql.Append(" ) TEMP FULL JOIN ( ")
        sql.Append(" select ")
        sql.Append(" PLANT_CODE, ")
        sql.Append(" sum(set_qty) as QTY, ")
        sql.Append(" model_code ")
        sql.Append(" from ")
        sql.Append(" TBP_PM_PACK_ACRS WHERE OUT_STATUS_CODE <> ''50'' AND STRG_ID = ''FL01'' ")
        sql.Append(" and del_yn = ''N'' ")
        sql.Append(" and use_yn = ''Y'' ")
        sql.Append(" GROUP BY plant_code, model_code ")
        sql.Append(" ) WT ON TEMP.PLANT_CODE = WT.PLANT_CODE ")
        sql.Append(" AND TEMP.MODEL_CODE = WT.MODEL_CODE ")
        sql.Append(" ORDER BY NVL(TEMP.PLANT_CODE, WT.PLANT_CODE), ")
        sql.Append(" NVL(TEMP.MODEL_CODE, WT.MODEL_CODE) ")
        sql.Append("') TT ")

        sqlf.NonQuery(sql.ToString)

        gmesEditableGrid.RefreshData(True)

    End Sub

    Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)

    End Sub

    Protected Sub Page_PreRender(sender As Object, e As System.EventArgs) Handles Me.PreRender
        If edTableToView.IsReadOnly Then
            edTableToView.CollectHeaderColumn(0)
        End If
        Title = edTableToView.TableDescription
    End Sub

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

    Protected Sub getGmes_Click(sender As Object, e As ImageClickEventArgs) Handles getGmes.Click
        GetDataFromGMES()
    End Sub

    Protected Sub getZRLEC53130_Click(sender As Object, e As ImageClickEventArgs) Handles getZRLEC53130.Click
        ActivateJob(6)
    End Sub

    Protected Sub getMB52_Click(sender As Object, e As ImageClickEventArgs) Handles getMB52.Click
        ActivateJob(80)
    End Sub

    Protected Sub buildReport_Click(sender As Object, e As ImageClickEventArgs) Handles buildReport.Click
        Dim sql As New StringBuilder
        sql.Append(" BEGIN TRANSACTION ")
        sql.Append(" TRUNCATE TABLE usermodules.[user].FG_INVENTORY ")
        sql.Append(" update UserModules.[user].FG_INVENTORY_No_ZRLEC53130 set stloc = 'FC01' ")
        sql.Append(" INSERT INTO usermodules.[user].FG_INVENTORY ")
        'sql.Append(" (ST__LOC_, PLANT, MODEL, PHYSICAL_INVENTORY_WH_STOCK_, NO_PR, QC_IBI, LINE, REJECT, BUFFER_ZONE, TOTAL_STOCK_P901_FC01__FC03___P903_FC01_03_,TEMP_LOADING) ")
        sql.Append(" (PLANT, MODEL, PHYSICAL_INVENTORY_WH_STOCK_, NO_PR, QC_IBI, LINE, REJECT, BUFFER_ZONE, TOTAL_STOCK_P901_FC01__FC03___P903_FC01_03_,TEMP_LOADING) ")
        sql.Append(" ")
        'sql.Append(" SELECT INV.SLOC,")
        sql.Append(" SELECT ")
        sql.Append(" ISNULL(INV.PLNT, GMES.PLANT) AS PLANT, ")
        sql.Append(" ISNULL(ISNULL(ISNULL(INV.MATERIAL, BUF.MATERIAL), GMES.MODEL),PEND.MODEL) as MODEL, ")
        sql.Append(" SUM(CAST(GMES.WAIT_STOCK AS INTEGER)) as WAIT, ")
        sql.Append(" SUM ( CASE PEND.LOCATION WHEN 'NO PR' THEN CAST(QTY AS DECIMAL) ELSE NULL END ) AS NO_PR, ")
        sql.Append(" SUM ( CASE PEND.LOCATION WHEN 'QC/IBI' THEN CAST(QTY AS DECIMAL) ELSE NULL END ) AS QC_IBI, ")
        sql.Append(" SUM ( CASE PEND.LOCATION WHEN 'LINE' THEN CAST(QTY AS DECIMAL) ELSE NULL END ) AS LINE, ")
        sql.Append(" SUM ( CASE PEND.LOCATION WHEN 'REJECT' THEN CAST(QTY AS DECIMAL) ELSE NULL END ) AS REJECT, ")
        sql.Append(" SUM(CAST(BUF.ACT__QTY as INTEGER)) as BUFFER, ")
        sql.Append(" SUM(INV.UNRESTRICTED + INV.TRANSIT_TRANSF_ + INV.INQUALITYINSP_+ INV.RESTRICTEDNoUSE + INV.BLOCKED + INV.RETURNS) as TTL, ")
        sql.Append(" SUM(CAST(GMES.TEMP_LOADING AS INTEGER)) as TEMP ")
        sql.Append(" FROM UserModules.[user].VI_FG_INVENTORY_No_MB52 INV ")
        sql.Append(" FULL JOIN UserModules.[user].FG_INVENTORY_No_ZRLEC53130 BUF ")
        sql.Append(" ON INV.MATERIAL = BUF.MATERIAL and INV.SLOC = BUF.stloc ")
        sql.Append(" FULL JOIN UserModules.[user].FG_INVENTORY_No_GMES GMES ")
        sql.Append(" ON ISNULL(INV.MATERIAL, BUF.MATERIAL) = GMES.MODEL and ISNULL(INV.SLOC, BUF.STLOC) = GMES.STLOC")
        sql.Append(" FULL JOIN UserModules.[user].FG_INVENTORY_No_PENDINGS PEND ")
        sql.Append(" ON ISNULL(ISNULL(INV.MATERIAL, BUF.MATERIAL), GMES.MODEL) = PEND.MODEL  AND ISNULL(ISNULL(INV.SLOC, BUF.STLOC), GMES.STLOC) = PEND.LOCATION ")
        'sql.Append(" GROUP BY INV.SLOC,")
        sql.Append(" GROUP BY ")
        sql.Append(" ISNULL(INV.PLNT, GMES.PLANT), ")
        sql.Append(" ISNULL(ISNULL(ISNULL(INV.MATERIAL, BUF.MATERIAL), GMES.MODEL),PEND.MODEL) ")
        sql.Append(" ")
        sql.Append(" COMMIT ")
        'lblmessage.Text = sql.ToString
        sqlf.NonQuery(sql.ToString)
        edTableToView.RefreshData(True)

    End Sub

    Protected Sub refresh_Click(sender As Object, e As ImageClickEventArgs) Handles refresh.Click
        edTableToView.RefreshData(True)
    End Sub
End Class