ModuleMaster/mm/buildkeymaterials.aspx.vb - main - ivan/ModMas

ModuleMaster/mm/buildkeymaterials.aspx.vb
297 строк16 KB
i.krivokon
Initial commit
13 мар 2026, 13:49
13 мар 2026, 13:4900d9fb6
Imports System.Data
Imports System.Data.SqlClient
Imports System.Net

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

    Private KeyMaterialDefinition As DataTable

    Private dataUpdateRequest As Boolean = False
    Private rowIdToUpdate As String = ""

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        Dim reportDate As String = Request.QueryString("date")
        Dim plant As String = Request.QueryString("plant")
        If IsNothing(reportDate) Or IsNothing(plant) Then
            Response.Write("provide date and plant. ex.: ?date=2016-09-10&plant=P901")
            Response.End()

        Else
            plant = plant.ToUpper
            edTableToView.IsReadOnly = True
            edTableToView.UserName = "AUTO"

            Dim sql As New StringBuilder
            'Checking that all data transfered from pick list to key material report
            sql.AppendLine("select count(*) as cnt from ( ")
            sql.AppendLine("select distinct [order] as pl_order ")
            sql.AppendLine("from UserModules.[user].PICK_LIST_MAIN_LINE ")
            sql.AppendLine("where work_ctr " & IIf(plant = "P903", "=", "<>") & " 'W' ")
            sql.AppendLine(") T1 Left join ")
            sql.AppendLine("(select distinct [order] as km_order ")
            sql.AppendLine("from UserModules.[user].KEY_MATERIAL_MASTER" & IIf(plant = "P903", "_W", " "))
            sql.AppendLine(") t2 on t1.[pl_order] = t2.[km_order] ")
            sql.AppendLine("where km_order is NULL ")

            If sqlf.GetScalar(sql.ToString) > 0 Then
                Dim wb As WebRequest
                wb = WebRequest.Create("http://serkweb.serk.lan:8080/modulemaster/Handlers/getsqldata.ashx?id=385")
                Dim res As WebResponse = wb.GetResponse
                If plant = "P903" Then
                    wb = WebRequest.Create("http://serkweb.serk.lan:8080/modulemaster/Handlers/getsqldata.ashx?id=586")
                Else
                    wb = WebRequest.Create("http://serkweb.serk.lan:8080/modulemaster/Handlers/getsqldata.ashx?id=396")
                End If

                res = wb.GetResponse
            End If

            sql.Clear()

            If plant = "P903" Then
                sql.AppendLine("select key_material_type ")
                sql.AppendLine(", LEFT(part_number_patterns , LEN(part_number_patterns )-1) as part_number_pattern ")
                sql.AppendLine(", LEFT(description_patterns , LEN(description_patterns )-1) as description_pattern ")
                sql.AppendLine(", LEFT(mrp_patterns , LEN(mrp_patterns )-1) as mrp_pattern ")
                sql.AppendLine("from UserModules.[user].key_material_definition_w as extern ")
                sql.AppendLine("CROSS APPLY( ")
                sql.AppendLine(" select part_number_pattern + '|' ")
                sql.AppendLine(" FROM UserModules.[user].key_material_definition_w as intern ")
                sql.AppendLine(" WHERE extern.key_material_type = intern.key_material_type ")
                sql.AppendLine(" FOR XML PATH ('') ")
                sql.AppendLine(") pre_trimmed(part_number_patterns) ")
                sql.AppendLine("CROSS APPLY( ")
                sql.AppendLine(" select description_pattern + '|' ")
                sql.AppendLine(" FROM UserModules.[user].key_material_definition_w as intern ")
                sql.AppendLine(" WHERE extern.key_material_type = intern.key_material_type ")
                sql.AppendLine(" FOR XML PATH ('') ")
                sql.AppendLine(") pre_trimmed1(description_patterns) ")
                sql.AppendLine("CROSS APPLY( ")
                sql.AppendLine(" select MRP_CONTROLLER + '|' ")
                sql.AppendLine(" FROM UserModules.[user].key_material_definition_w as intern ")
                sql.AppendLine(" WHERE extern.key_material_type = intern.key_material_type ")
                sql.AppendLine(" FOR XML PATH ('') ")
                sql.AppendLine(") pre_trimmed2(mrp_patterns) ")
                sql.AppendLine("WHERE enabled='Y' ")
            Else
                sql.Append("select key_material_type ")
                sql.Append(", LEFT(part_number_patterns , LEN(part_number_patterns )-1) as part_number_pattern ")
                sql.Append(", LEFT(description_patterns , LEN(description_patterns )-1) as description_pattern ")
                sql.Append("from UserModules.[user].key_material_definition as extern ")
                sql.Append("CROSS APPLY( ")
                sql.Append("	select part_number_pattern + '|' ")
                sql.Append("	FROM UserModules.[user].key_material_definition as intern ")
                sql.Append("	WHERE extern.key_material_type = intern.key_material_type and enabled='Y' ")
                sql.Append("	FOR XML PATH ('') ")
                sql.Append(") pre_trimmed(part_number_patterns) ")
                sql.Append("CROSS APPLY( ")
                sql.Append("	select description_pattern + '|' ")
                sql.Append("	FROM UserModules.[user].key_material_definition as intern ")
                sql.Append("	WHERE extern.key_material_type = intern.key_material_type and enabled='Y' ")
                sql.Append("	FOR XML PATH ('') ")
                sql.Append(") pre_trimmed1(description_patterns) ")
                sql.Append("WHERE enabled='Y' ")
                sql.Append("GROUP BY key_material_type, part_number_patterns, description_patterns ")
            End If

            KeyMaterialDefinition = sqlf.GetData(sql.ToString)

            If plant = "P903" Then
                BuildKeyMaterials_W(reportDate, "")
            Else
                BuildKeyMaterials(reportDate, "")
            End If

            Response.Write("done for " & reportDate & " plant " & plant)
            Response.End()
        End If
    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
    End Sub

    Private Sub BuildKeyMaterials(prodDate As String, rowToUpdate As String)
        Dim sCond As String = ""
        If prodDate <> "" Then
            sCond = "PRODUCTION_DATE = '" & prodDate & "'"
        ElseIf rowToUpdate <> "" Then
            sCond = "id = '" & rowToUpdate & "'"
        End If
        If sCond <> "" Then
            Dim dt As DataTable = sqlf.GetData("Select id, [order] FROM UserModules.[user].KEY_MATERIAL_MASTER WHERE " & sCond)
            For Each rv As DataRow In dt.Rows
                Dim conf As edGridNamespace.tableConfig = edTableToView.config
                Dim sql As New StringBuilder

                Dim rowId As String = rv("id")
                Dim valuesToUpdate As New Hashtable

                If rowId = rowIdToUpdate Or rowIdToUpdate = "" Then
                    sql.AppendLine("SELECT MATERIAL ")
                    sql.AppendLine(", SUBSTRING(MATERIAL_DESCRIPTION, 1, CHARINDEX(';',MATERIAL_DESCRIPTION )-1) as MATERIAL_DESC ")
                    sql.AppendLine(", STOR__LOC_ as STLOC ")
                    sql.AppendLine(" FROM UserModules.[user].PICK_LIST_MAIN_LINE ")

                    For Each dr As DataRow In KeyMaterialDefinition.Rows
                        Dim selectCondition = sql.ToString & " WHERE [order]= '" & rv("order") & "' AND ("
                        Dim pos As String = conf.GetParam(dr("key_material_type"), "POSITION")
                        Dim pnPatterns() As String = dr("part_number_pattern").ToString.Split("|")
                        Dim descPatterns() As String = dr("description_pattern").ToString.Split("|")
                        Dim qty As Integer = Math.Max(pnPatterns.Count, descPatterns.Count)
                        For i As Integer = 1 To qty
                            If i > 1 Then
                                selectCondition &= " OR "
                            End If
                            selectCondition &= "(MATERIAL LIKE '%" & pnPatterns(i - 1).Trim & "%' AND MATERIAL_DESCRIPTION LIKE '%" & descPatterns(i - 1).Trim & "%')"
                        Next
                        selectCondition &= ")"
                        Dim materials As DataTable = sqlf.GetData(selectCondition)
                        Dim m As New StringBuilder
                        If Not IsNothing(materials) Then
                            Dim origin As String = ""
                            For Each drr As DataRow In materials.Rows
                                m.AppendLine(drr("MATERIAL")) ' & "/" & drr("MATERIAL_DESC"))
                                '----------------  Determine origin by storage location
                                Try
                                    If origin <> "" Then origin &= " "
                                    If drr("STLOC") = "RC01" Then
                                        origin &= "IMPORT"
                                    ElseIf drr("STLOC") = "RC02" Or drr("STLOC") = "RC03" Then
                                        origin &= "LOCAL"
                                    ElseIf drr("STLOC") = "HC04" Then
                                        origin &= "INJECTION"
                                    Else
                                        origin &= drr("STLOC")
                                    End If
                                Catch ex As Exception
                                End Try
                                '-- -----------------------------
                            Next
                            If dr("key_material_type") = "STAND" Then
                                valuesToUpdate.Add("STAND_VEND", origin)
                            End If
                            If dr("key_material_type") = "FRONT_C" Then
                                valuesToUpdate.Add("FRONT_C_VEND", origin)
                            End If
                            If dr("key_material_type") = "REAR_C" Then
                                valuesToUpdate.Add("REAR_C_VEND", origin)
                            End If
                        End If
                        valuesToUpdate.Add(dr("key_material_type"), m.ToString)
                    Next
                    sql.Clear()
                    sql.AppendLine("UPDATE UserModules.[user].KEY_MATERIAL_MASTER SET ")
                    Dim addComma As Boolean = False
                    For Each v As DictionaryEntry In valuesToUpdate
                        If addComma Then sql.AppendLine(",")
                        sql.Append(v.Key & " = '" & v.Value & "'")
                        addComma = True
                    Next
                    sql.AppendLine(" WHERE id = " & rowId)
                    sqlf.NonQuery(sql.ToString)
                    edTableToView.Message &= sqlf.lastException
                End If
            Next
        End If
    End Sub
    Private Sub BuildKeyMaterials_W(prodDate As String, rowToUpdate As String)
        Dim sCond As String = ""
        If prodDate <> "" Then
            sCond = "PRODUCTION_DATE = '" & prodDate & "'"
        ElseIf rowToUpdate <> "" Then
            sCond = "id = '" & rowToUpdate & "'"
        End If
        If sCond <> "" Then
            Dim dt As DataTable = sqlf.GetData("Select id, [order] FROM UserModules.[user].KEY_MATERIAL_MASTER_W WHERE " & sCond)
            For Each rv As DataRow In dt.Rows
                Dim conf As edGridNamespace.tableConfig = edTableToView.config
                Dim sql As New StringBuilder

                Dim rowId As String = rv("id")
                Dim valuesToUpdate As New Hashtable

                If rowId = rowIdToUpdate Or rowIdToUpdate = "" Then
                    sql.AppendLine("SELECT MATERIAL ")
                    sql.AppendLine(", SUBSTRING(MATERIAL_DESCRIPTION, 1, CHARINDEX(';',MATERIAL_DESCRIPTION )-1) as MATERIAL_DESC ")
                    sql.AppendLine(", STOR__LOC_ as STLOC ")
                    sql.AppendLine(" FROM UserModules.[user].PICK_LIST_MAIN_LINE ")

                    For Each dr As DataRow In KeyMaterialDefinition.Rows
                        Dim selectCondition = sql.ToString & " WHERE [order]= '" & rv("order") & "' AND ("
                        Dim pos As String = conf.GetParam(dr("key_material_type"), "POSITION")
                        Dim pnPatterns() As String = dr("part_number_pattern").ToString.Split("|")
                        Dim descPatterns() As String = dr("description_pattern").ToString.Split("|")
                        Dim mrpPatterns() As String = dr("mrp_pattern").ToString.Split("|")
                        Dim qty As Integer = Math.Max(pnPatterns.Count, descPatterns.Count)
                        For i As Integer = 1 To qty
                            If i > 1 Then
                                selectCondition &= " OR "
                            End If
                            selectCondition &= "(MATERIAL LIKE '%" & pnPatterns(i - 1).Trim &
                                "%' AND MATERIAL_DESCRIPTION LIKE '%" & descPatterns(i - 1).Trim &
                                "%' AND MRP_CONTR LIKE '%" & mrpPatterns(i - 1).Trim & "%' )"
                        Next
                        selectCondition &= ")"
                        Dim materials As DataTable = sqlf.GetData(selectCondition)
                        Dim m As New StringBuilder
                        If Not IsNothing(materials) Then
                            For Each drr As DataRow In materials.Rows
                                m.AppendLine(drr("MATERIAL")) ' & "/" & drr("MATERIAL_DESC"))

                                '----------------  Determine origin by storage location
                                Dim origin As String = ""
                                Try
                                    'If drr("STLOC") = "RC01" Then
                                    '    origin = "IMPORT"
                                    'ElseIf drr("STLOC") = "RC02" Or drr("STLOC") = "RC03" Then
                                    '    origin = "LOCAL"
                                    'ElseIf drr("STLOC") = "HC04" Then
                                    '    origin = "INJECTION"
                                    'Else
                                    '    origin = drr("STLOC")
                                    'End If

                                    'If dr("key_material_type") = "STAND" Then
                                    '    valuesToUpdate.Add("STAND_VEND", origin)
                                    'End If
                                    'If dr("key_material_type") = "FRONT_C" Then
                                    '    valuesToUpdate.Add("FRONT_C_VEND", origin)
                                    'End If
                                    'If dr("key_material_type") = "REAR_C" Then
                                    '    valuesToUpdate.Add("REAR_C_VEND", origin)
                                    'End If
                                Catch ex As Exception
                                End Try
                                '-- -----------------------------

                            Next
                        End If
                        valuesToUpdate.Add(dr("key_material_type"), m.ToString)
                    Next
                    sql.Clear()
                    sql.AppendLine("UPDATE UserModules.[user].KEY_MATERIAL_MASTER_W SET ")
                    Dim addComma As Boolean = False
                    For Each v As DictionaryEntry In valuesToUpdate
                        If addComma Then sql.AppendLine(",")
                        sql.Append(v.Key & " = '" & v.Value & "'")
                        addComma = True
                    Next
                    sql.AppendLine(" WHERE id = " & rowId)
                    sqlf.NonQuery(sql.ToString)
                    edTableToView.Message &= sqlf.lastException
                End If
            Next
        End If
    End Sub
End Class