ModuleMaster/sop confirmation/test.aspx.vb - main - ivan/ModMas

ModuleMaster/sop confirmation/test.aspx.vb
154 строки8 KB
i.krivokon
Initial commit
13 мар 2026, 13:49
13 мар 2026, 13:4900d9fb6
Imports sharedClasses.dbFunctions
Imports System.Data

Partial Class SOP_Confirmation_test
    Inherits System.Web.UI.Page
    Dim sqlf As New SqlFunctions
    Private Sub SOP_Confirmation_test_Load(sender As Object, e As EventArgs) Handles Me.Load
        Dim sql As New StringBuilder
        sql.AppendLine("select ")
        sql.AppendLine("ITEM ")
        sql.AppendLine(", sum(cast(on_hand as numeric)) initial_on_hand ")
        sql.AppendLine(", SUM(isnull(cast(MATERIAL_REMAINS as numeric),0)) as initial_remains_oh ")
        sql.AppendLine(", sum(cast(in_transit as numeric)) initial_in_transit ")
        sql.AppendLine(", SUM(isnull(cast(MATERIAL_REMAINS_IT as numeric),0)) as initial_remains_it ")

        sql.AppendLine(", sum(cast(on_hand as numeric)) + ")
        sql.AppendLine(" sum(cast(in_transit as numeric)) + SUM(isnull(cast(MATERIAL_REMAINS as numeric),0)) + SUM(isnull(cast(MATERIAL_REMAINS_IT as numeric),0))  AS initial_TTL ")

        sql.AppendLine(", sum(cast(on_hand as numeric)) on_hand ")
        sql.AppendLine(", SUM(isnull(cast(MATERIAL_REMAINS as numeric),0)) as remains_oh ")
        sql.AppendLine(", sum(cast(in_transit as numeric)) in_transit ")
        sql.AppendLine(", SUM(isnull(cast(MATERIAL_REMAINS as numeric),0)) as remains_it ")

        sql.AppendLine(", sum(cast(on_hand as numeric)) + ")
        sql.AppendLine(" sum(cast(in_transit as numeric)) + SUM(isnull(cast(MATERIAL_REMAINS as numeric),0)) + SUM(isnull(cast(MATERIAL_REMAINS_IT as numeric),0))  AS TTL ")

        sql.AppendLine("FROM usermodules.[user].[PRE_SOP_CONFIRMATION_2] ")
        sql.AppendLine("WHERE week = 'W21' and item is not null ")
        sql.AppendLine("and division = 'WM' ")
        sql.AppendLine("GROUP BY ITEM ")
        Dim materialQuantity As DataTable = sqlf.GetData(sql.ToString)

        sql.Clear()
        sql.AppendLine("USE UserModules ")
        sql.AppendLine("select DENSE_RANK() OVER (ORDER BY TTT.MIN_QUANTITY DESC,  TTT.PRE_SOP_Q_TY DESC) as num , TTT.* from ( ")
        sql.AppendLine(" Select  TT.* ")
        sql.AppendLine(", ISNULL(TT.MIN_QUANTITY, TT.PRE_SOP_Q_TY) As TO_CONFIRM ")
        sql.AppendLine(", 0 As OH_CONF_QTY ")
        sql.AppendLine(", 0 As IT_CONF_QTY ")
        sql.AppendLine("FROM ( ")
        sql.AppendLine("Select ")
        sql.AppendLine("t1.model ")
        sql.AppendLine(", t1.ITEM ")
        sql.AppendLine(", t1.PRE_SOP_Q_TY ")
        sql.AppendLine(", t2.MIN_QUANTITY ")
        sql.AppendLine(", t1.PRE_SOP_Q_TY - t2.MIN_QUANTITY As remain_qty ")
        sql.AppendLine("FROM [user].[PRE_SOP_CONFIRMATION_2] t1 ")
        sql.AppendLine("left join [user].SOP_MODEL_PRIORITY t2 ")
        sql.AppendLine("On t1.MODEL = t2.MODEL and t1.week = t2.week ")
        sql.AppendLine("where t1.week = 'W21' ")
        sql.AppendLine("and t1.ITEM is not null ")
        sql.AppendLine("and t1.division = 'WM' ")
        sql.AppendLine("UNION ")
        sql.AppendLine("SELECT ")
        sql.AppendLine("t1.model ")
        sql.AppendLine(", t1.ITEM ")
        sql.AppendLine(", t1.PRE_SOP_Q_TY - ISNULL(t2.MIN_QUANTITY,0) AS remain_qty ")
        sql.AppendLine(", null ")
        sql.AppendLine(", NULL ")
        sql.AppendLine("FROM [user].[PRE_SOP_CONFIRMATION_2] t1 ")
        sql.AppendLine("left join [user].SOP_MODEL_PRIORITY t2 ")
        sql.AppendLine("on t1.MODEL = t2.MODEL  and t1.week = t2.week  ")
        sql.AppendLine("where t1.week = 'W21'")
        sql.AppendLine("and t1.division = 'WM' ")
        sql.AppendLine("and t1.ITEM is not null ")
        sql.AppendLine("AND T2.MIN_QUANTITY IS NOT NULL ")
        sql.AppendLine(") tt ")
        sql.AppendLine(") TTT ORDER BY TTT.MIN_QUANTITY DESC, TTT.PRE_SOP_Q_TY DESC ")
        Dim modelVSItem As DataTable = sqlf.GetData(sql.ToString)

        sql.Clear()
        sql.AppendLine(" USE UserModules ")
        sql.AppendLine("select DENSE_RANK() OVER (ORDER BY TTT.MIN_QUANTITY DESC,  TTT.PRE_SOP_Q_TY DESC) as num , TTT.* from ( ")
        sql.AppendLine(" Select  DISTINCT TT.* ")
        sql.AppendLine(", ISNULL(TT.MIN_QUANTITY, TT.PRE_SOP_Q_TY) As TO_CONFIRM ")
        sql.AppendLine("FROM ( ")
        sql.AppendLine("Select ")
        sql.AppendLine("t1.model ")
        sql.AppendLine(", t1.PRE_SOP_Q_TY ")
        sql.AppendLine(", t2.MIN_QUANTITY ")
        sql.AppendLine(", t1.PRE_SOP_Q_TY - t2.MIN_QUANTITY As remain_qty ")
        sql.AppendLine("FROM [user].[PRE_SOP_CONFIRMATION_2] t1 ")
        sql.AppendLine("left join [user].SOP_MODEL_PRIORITY t2 ")
        sql.AppendLine("On t1.MODEL = t2.MODEL and t1.week = t2.week ")
        sql.AppendLine("where t1.week = 'W21' ")
        sql.AppendLine("and t1.division = 'WM' ")
        sql.AppendLine("and t1.ITEM is not null ")
        sql.AppendLine("UNION ")
        sql.AppendLine("SELECT ")
        sql.AppendLine("t1.model ")
        sql.AppendLine(", t1.PRE_SOP_Q_TY - ISNULL(t2.MIN_QUANTITY,0) AS remain_qty ")
        sql.AppendLine(", null ")
        sql.AppendLine(", NULL ")
        sql.AppendLine("FROM [user].[PRE_SOP_CONFIRMATION_2] t1 ")
        sql.AppendLine("left join [user].SOP_MODEL_PRIORITY t2 ")
        sql.AppendLine("on t1.MODEL = t2.MODEL and t1.week = t2.week ")
        sql.AppendLine("where t1.week = 'W21' ")
        sql.AppendLine("and t1.division = 'WM' ")
        sql.AppendLine("and t1.ITEM is not null ")
        sql.AppendLine("AND T2.MIN_QUANTITY IS NOT NULL ")
        sql.AppendLine(") tt ")
        sql.AppendLine(") TTT WHERE TTT.MODEL is not null ORDER BY TTT.MIN_QUANTITY DESC, TTT.PRE_SOP_Q_TY DESC ")
        Dim planData As DataTable = sqlf.GetData(sql.ToString)
        If Not IsNothing(planData) Then
            Dim curModel As String = ""
            Dim curNum As String = ""
            For Each model As DataRow In planData.Rows
                curModel = model("MODEL")
                curNum = model("num")

                Dim curModelMaterials() As DataRow = modelVSItem.Select("MODEL = '" & curModel & "' and num = '" & curNum & "'")
                Dim minQty As Integer = Integer.MaxValue
                'Looking for smallest quantity
                For Each material As DataRow In curModelMaterials
                    Dim quantity As DataRow = materialQuantity.Select("item='" & material("item") & "'")(0)
                    If minQty > quantity("TTL") Then
                        minQty = quantity("TTL")
                    End If
                Next

                For Each material As DataRow In curModelMaterials
                    Dim quantity As DataRow = materialQuantity.Select("item='" & material("item") & "'")(0)
                    If minQty < material("TO_CONFIRM") Then
                        material("OH_CONF_QTY") = minQty
                        If quantity("on_hand") >= minQty Then
                            quantity("on_hand") = quantity("on_hand") - minQty
                        Else
                            quantity("in_transit") = minQty - quantity("on_hand")
                            quantity("on_hand") = 0
                        End If
                    Else
                        If quantity("on_hand") >= material("TO_CONFIRM") Then
                            material("OH_CONF_QTY") = material("TO_CONFIRM")
                            quantity("on_hand") = quantity("on_hand") - material("TO_CONFIRM")
                        Else
                            material("OH_CONF_QTY") = quantity("on_hand")
                            material("IT_CONF_QTY") = material("TO_CONFIRM") - quantity("on_hand")

                            quantity("in_transit") -= material("IT_CONF_QTY")
                            quantity("on_hand") = 0
                        End If
                    End If
                    quantity("TTL") = quantity("in_transit") + quantity("on_hand")
                Next

            Next
        End If
        GridView1.DataSource = modelVSItem
        GridView1.DataBind()
        GridView2.DataSource = materialQuantity
        GridView2.DataBind()

    End Sub
End Class