ModuleMaster/purchasing/mat_comparison_raw.aspx.vb - main - ivan/ModMas

ModuleMaster/purchasing/mat_comparison_raw.aspx.vb
107 строк5 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 Xml_viewTable
    Inherits System.Web.UI.Page

    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

        edTableToView.IsReadOnly = True
        edTableToView.UserName = userid.ToUpper
        Dim sqlf As New SqlFunctions

    End Sub

    Private Sub PreparePivotData(sender As Object, e As EventArgs) Handles edTableToView.ActionRequest
        Dim CommandName As String = CType(e, CommandEventArgs).CommandName
        Dim CommandArgument As String = CType(e, CommandEventArgs).CommandArgument
        If CommandArgument = "PREPARE_DATA" Then
            Dim sqlf As New SqlFunctions
            sqlf.NonQuery("TRUNCATE TABLE UserModules.[user].TEMP_932")
            sqlf.NonQuery("INSERT INTO UserModules.[user].TEMP_932 (row_id, parent) SELECT id, SUBSTRING ( t.[desc], 0, charindex(';', t.[desc])) AS parent FROM UserModules.[user].ZRMMC31130 t WHERE ( t.level_11 = '..2' OR t.level_11 = '.1' ) ORDER BY id DESC")
            sqlf.NonQuery("TRUNCATE table UserModules.[user].DATA_FOR_PIVOT")
            '"tt.model, tt.plant +'/'+ tt.model as plant, tt.cost_applied, tt.inch, t2.[group], tt.[desc], tt.material_code, tt.parent, tt.qty, tt.unit_price, tt.amount_usd, tt.level_11, tt.vendor, tt.vendor_name " & _
            sqlf.NonQuery("INSERT INTO UserModules.[user].DATA_FOR_PIVOT ( model, plant, cost_applied, inch, [group], description, material_code, parent, qty, unit_price, amount__, [level], t1.vendor, t1.vendor_name ) " & _
"SELECT " & _
"tt.model, tt.plant, tt.cost_applied, tt.inch, t2.[group], tt.[desc], tt.material_code, tt.parent, tt.qty, tt.unit_price, tt.amount_usd, tt.level_11, tt.vendor, tt.vendor_name " & _
"FROM ( SELECT " & _
"t1.model, t1.id, " & _
"t1.plant, " & _
"t1.COST_APPLIED, " & _
"t1.inch, " & _
"SUBSTRING ( t1.[desc], 0, charindex(';', t1.[desc])) AS [desc], " & _
"t1.MATERIAL_CODE, " & _
"CASE t1.LEVEL_11 " & _
"WHEN '.1' THEN SUBSTRING ( t1.[desc], 0, charindex(';', t1.[desc])) " & _
"WHEN '..2' THEN SUBSTRING ( t1.[desc], 0, charindex(';', t1.[desc])) " & _
"ELSE ( SELECT TOP 1 SUBSTRING ( t.[desc], 0, charindex(';', t.[desc])) FROM UserModules.[user].ZRMMC31130 t WHERE t.id < t1.id AND ( t.level_11 = '..2' OR t.level_11 = '.1' ) ORDER BY id DESC ) " & _
"END AS parent, " & _
"t1.qty, " & _
"cast (t1.UNIT_PRICE as decimal(20,6)) / cast (isnull(ER.RATE,1) as decimal(20,6)) as unit_Price, " & _
"cast(t1.qty as decimal(20,6)) * cast(t1.UNIT_PRICE as decimal(20,6)) / cast(isnull(ER.RATE,1) as decimal(20,6)) as amount_USD, " & _
"ER.rate, " & _
"t1.LEVEL_11, t1.vendor, t1.vendor_name " & _
"FROM UserModules.[user].ZRMMC31130 t1 " & _
"LEFT JOIN UserModules.[user].exchange_rate ER " & _
"on t1.CURR_ = ER.CURR_TO " & _
") tt " & _
"LEFT JOIN UserModules.[user].MATERIAL_GROUPS_DEFINITION t2 ON tt.parent = t2.DESCRIPTION ORDER BY tt.id ")
            edTableToView.RefreshData()

        End If

    End Sub

    Private Sub updateMaterialGroups(tableName As String, fieldName As String, rowId As String, value As String, fieldType As String, byButton As Boolean, text As String) Handles edTableToView.SavedElement
        If fieldName = "GROUP" Then
            Dim sqlf As New SqlFunctions
            Dim materialParent = sqlf.GetScalar("SELECT  [parent] from UserModules.[user].DATA_FOR_PIVOT where id=" & rowId)
            'Dim sql As String = "UPDATE UserModules.[user].MATERIAL_GROUPS_DEFINITION SET"
            Dim sql As String = "INSERT INTO UserModules.[user].MATERIAL_GROUPS_DEFINITION ([description],[group]) VALUES (@description,@group)"
            Dim com As New SqlCommand(sql)
            With com.Parameters
                .AddWithValue("description", materialParent)
                .AddWithValue("group", value)
            End With
            Dim affectedrows As Integer
            sqlf.NonQuery(com, affectedrows)
            If affectedrows = 0 Then
                sql = "UPDATE UserModules.[user].MATERIAL_GROUPS_DEFINITION SET [group] = @group WHERE [description] = @description "
                com.CommandText = sql
                sqlf.NonQuery(com)
            End If
        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
        If edTableToView.IsReadOnly Then
            edTableToView.CollectHeaderColumn(1)
        End If

    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

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



End Class