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

ModuleMaster/multibin/picking.ashx
96 строк4 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 material As String = .QueryString("material")
            Dim prodDate As String = .QueryString("prodDate")
            Dim line As String = .QueryString("line")


            sql.AppendLine("with p as ( ")
            sql.AppendLine("SELECT ")
            sql.AppendLine("[material] ")
            sql.AppendLine(",[delivery_date] ")
            sql.AppendLine(",sum(cast([qty] as decimal(18,2))) as picked ")
            sql.AppendLine(",[line] ")
            sql.AppendLine(",[plant] ")
            sql.AppendLine("FROM [Multi-bin].[dbo].[picking] ")
            sql.AppendLine("where line = '" & line & "' ")
            sql.AppendLine("and delivery_date = '" & prodDate & "' ")
            sql.AppendLine("group by material, delivery_date, line, plant ")
            sql.AppendLine(") ")
            sql.AppendLine(" ")
            sql.AppendLine(" ")
            sql.AppendLine("select ")
            sql.AppendLine("l.line ")
            sql.AppendLine(",'" & prodDate & "' as prodDate")
            sql.AppendLine(",l.material ")
            sql.AppendLine(",[description] ")
            sql.AppendLine("--,l.plant ")
            sql.AppendLine(",requested ")
            sql.AppendLine(",picked ")
            sql.AppendLine(",requested - picked as remain ")
            sql.AppendLine(",stock.bin ")
            sql.AppendLine("from ")
            sql.AppendLine("( ")
            sql.AppendLine("	select ")
            sql.AppendLine("	line ")
            sql.AppendLine("	,delivery_date ")
            sql.AppendLine("	,material ")
            sql.AppendLine("	,[description] ")
            sql.AppendLine("	,plant ")
            sql.AppendLine("	, sum(cast([qty] as decimal(18,2))) as requested ")
            sql.AppendLine("	from [Multi-bin].[dbo].[LTS] ")
            sql.AppendLine("	group by line, material, [description], delivery_date, plant ")
            sql.AppendLine("	) as L ")
            sql.AppendLine(" ")
            sql.AppendLine("left join p ")
            sql.AppendLine("on l.line = p.line ")
            sql.AppendLine("and l.delivery_date = p.delivery_date ")
            sql.AppendLine("and l.material = p.material ")
            sql.AppendLine("and l.plant = p.plant ")
            sql.AppendLine(" ")
            sql.AppendLine(" ")
            sql.AppendLine("left join [Multi-bin].[dbo].[stock] as stock ")
            sql.AppendLine("on l.material = stock.material ")
            sql.AppendLine(" ")
            sql.AppendLine(" ")
            sql.AppendLine("where stock.zone = 'Floor' ")
            sql.AppendLine("and l.delivery_date = '" & prodDate & "' ")
            If material <> "" Then
                sql.AppendLine("and l.material = '" & material & "' ")
            End If



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


            dt = sqlf.GetData(sql.ToString)

            Try
                context.Response.Write(JsonConvert.SerializeObject(dt, Formatting.None))
            Catch ex As Exception
                context.Response.Write("")
            End Try
        End With
    End Sub

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

End Class