ModuleMaster/multibin/kitting.ashx - main - ivan/ModMas

ModuleMaster/multibin/kitting.ashx
166 строк7 KB
i.krivokon
Initial commit
13 мар 2026, 13:49
13 мар 2026, 13:4900d9fb6
<%@ WebHandler Language="VB" Class="getsqldata" %>

Imports System
Imports System.Web
Imports Newtonsoft.Json

Public Class getsqldata : Implements IHttpHandler

    Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
        context.Response.ContentType = "text/plain"
        With context.Request


            Dim sql As New StringBuilder
            Dim trolley As String = .QueryString("trolley")
            Dim su As String = .QueryString("su")
            Dim po As String = .QueryString("po")
            Dim qty As String = .QueryString("qty")

            Dim result As Integer

            'Можно сохранить данные в отладочную таблицу
            'sql.AppendLine("insert into [Multi-bin].[dbo].[debug]")
            'sql.AppendLine("(trolley, su, po, qty)")
            'sql.AppendLine("values ('" & trolley & "', '" & su & "', '" & po & "', '" & qty & "')")
            'sql.AppendLine("")


            'sql.AppendLine("declare @su as varchar(50) ")
            'sql.AppendLine("declare @qty as decimal (18,4) ")
            'sql.AppendLine("declare @trolley as varchar(50) ")
            'sql.AppendLine("declare @po as varchar(50) ")
            'sql.AppendLine("set @su = '100000006' ")
            'sql.AppendLine("set @qty = 20 ")
            'sql.AppendLine("set @trolley = 'PAN-001' ")
            'sql.AppendLine("set @po = '34634562362' ")
            'sql.AppendLine(" ")
            'sql.AppendLine(" ")
            sql.AppendLine("if exists (SELECT [SU] FROM [Multi-bin].[dbo].[Stock] where su = '" & su & "' and qty = cast ('" & qty & "' as decimal (18,4)) and zone='Kitting') ")
            sql.AppendLine("begin ")
            sql.AppendLine(" ")
            sql.AppendLine("begin transaction ")
            sql.AppendLine("declare @n1 as int ")
            sql.AppendLine("declare @n2 as int ")
            sql.AppendLine("set @n1 = 0 ")
            sql.AppendLine("set @n2 = 0 ")
            sql.AppendLine(" ")
            sql.AppendLine(" ")
            sql.AppendLine("update [Multi-bin].[dbo].[Stock] ")
            sql.AppendLine("set zone= 'GI' ")
            sql.AppendLine(", bin = '" & trolley & "' ")
            sql.AppendLine("where su = '" & su & "' ")
            sql.AppendLine("set @n1 = @@ROWCOUNT; ")
            sql.AppendLine(" ")
            sql.AppendLine("with ctn as ( ")
            sql.AppendLine("select po = '" & po & "' ")
            sql.AppendLine(",material = (select Material from [Multi-bin].[dbo].[Stock] where su = '" & su & "') ")
            sql.AppendLine(",gi = '" & qty & "' ")
            sql.AppendLine(") ")
            sql.AppendLine(" ")
            sql.AppendLine(" ")
            sql.AppendLine("merge [Multi-bin].[dbo].[Monitoring] as trg ")
            sql.AppendLine("using ctn as src ")
            sql.AppendLine("on trg.po = src.po ")
            sql.AppendLine("and trg.material = src.material ")
            sql.AppendLine("when not matched then ")
            sql.AppendLine("insert (po, material, gi) values ('" & po & "', (select Material from [Multi-bin].[dbo].[Stock] where su = '" & su & "'), '" & qty & "') ")
            sql.AppendLine("when matched then ")
            sql.AppendLine("update set gi = trg.gi + '" & qty & "'; ")
            sql.AppendLine(" ")
            sql.AppendLine("set @n2 = @@ROWCOUNT ")
            sql.AppendLine("if @n1 > 0 and @n2 > 0 commit transaction else rollback transaction ")
            sql.AppendLine(" ")
            sql.AppendLine("end ")
            sql.AppendLine(" ")
            sql.AppendLine(" ")
            sql.AppendLine(" ")
            sql.AppendLine(" ")
            sql.AppendLine(" ")
            sql.AppendLine("else ")
            sql.AppendLine("begin ")
            sql.AppendLine("	begin transaction ")
            sql.AppendLine("	declare @n3 as int ")
            sql.AppendLine("	declare @n4 as int ")
            sql.AppendLine("	declare @n5 as int ")
            sql.AppendLine("	set @n3 = 0 ")
            sql.AppendLine("	set @n4 = 0 ")
            sql.AppendLine("	set @n5 = 0 ")
            sql.AppendLine(" ")
            sql.AppendLine("	update [Multi-bin].[dbo].[Stock] ")
            sql.AppendLine("	set qty = qty - cast ('" & qty & "' as decimal (18,4)) ")
            sql.AppendLine("	where su = '" & su & "' ")
            sql.AppendLine("	set @n3 = @@ROWCOUNT ")
            sql.AppendLine(" ")
            sql.AppendLine("	insert into [Multi-bin].[dbo].[Stock] ")
            sql.AppendLine("	(su, material, qty, bin , zone) ")
            sql.AppendLine("	values (cast (rand() as varchar(50)), (select material from [Multi-bin].[dbo].[Stock] where su = '" & su & "'), '" & qty & "', '" & trolley & "', 'GI') ")
            sql.AppendLine("	set @n4 = @@ROWCOUNT; ")
            sql.AppendLine(" ")
            sql.AppendLine(" ")
            sql.AppendLine("	with ctn as ( ")
            sql.AppendLine("	select po = '" & po & "' ")
            sql.AppendLine("	,material = (select Material from [Multi-bin].[dbo].[Stock] where su = '" & su & "') ")
            sql.AppendLine("	,gi = '" & qty & "' ")
            sql.AppendLine("	) ")
            sql.AppendLine(" ")
            sql.AppendLine(" ")
            sql.AppendLine("	merge [Multi-bin].[dbo].[Monitoring] as trg ")
            sql.AppendLine("	using ctn as src ")
            sql.AppendLine("	on trg.po = src.po ")
            sql.AppendLine("	and trg.material = src.material ")
            sql.AppendLine("	when not matched then ")
            sql.AppendLine("	insert (po, material, gi) values ('" & po & "', (select Material from [Multi-bin].[dbo].[Stock] where su = '" & su & "'), '" & qty & "') ")
            sql.AppendLine("	when matched then ")
            sql.AppendLine("	update set gi = trg.gi + '" & qty & "'; ")
            sql.AppendLine(" ")
            sql.AppendLine("	set @n5 = @@ROWCOUNT ")
            sql.AppendLine(" ")
            sql.AppendLine(" ")
            sql.AppendLine(" ")
            sql.AppendLine(" ")
            sql.AppendLine("	if @n3 > 0 and @n4 > 0 and @n5 > 0 commit transaction else rollback transaction ")
            sql.AppendLine("end ")













            Dim sqlf As New sharedClasses.dbFunctions.SqlFunctions
            Dim dt As Data.DataTable = Nothing


            result = sqlf.NonQuery(sql.ToString)



            Try
                If result > 0 Then
                    context.Response.Write("[{""msg"":""OK""}]")
                Else
                    context.Response.Write("[{""msg"":""Error during kitting""}]")
                End If
            Catch ex As Exception
                context.Response.Write("[{""msg"":""Connection problem. Try-Catch triggered""}]")
            End Try



        End With
    End Sub

    Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
        Get
            Return False
        End Get
    End Property

End Class