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

ModuleMaster/purchasing/sj_jit.aspx.vb
382 строки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 dataUpdateRequest As Boolean = False
    Private rowIdToUpdate As String = ""

    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

        If Not IsNothing(Request.QueryString("u")) Then
            userid = Request.QueryString("u")
        End If

        If userid = "" Then
            authorize("SSO")

            userid = a.userId
            edTableToView.Message = "By SSO: " & a.userId
        End If
        Dim viewId As String = ""


        If Not IsNothing(Request.QueryString("viewid")) Then
            viewId = Request.QueryString("viewid")
        End If
        If Not IsNothing(Request.QueryString("v")) Then
            viewId = Request.QueryString("v")
        End If

        If Not IsNothing(Request.QueryString("d")) Then
            'edTableToView.debugMode = (Request.QueryString("d").ToString.ToUpper = "YES")
        End If

        If Not IsPostBack Then
            If Not IsNothing(Request.QueryString("p")) Then
                If IsNumeric(Request.QueryString("p")) Then
                    edTableToView.PageSize = Request.QueryString("p")
                End If
            End If
        End If

        edTableToView.IsReadOnly = True
        edTableToView.UserName = userid.ToUpper

        If Not IsNothing(Request.QueryString("viewonly")) Then
            If Request.QueryString("viewonly").ToString.ToUpper = "YES" Then
                edTableToView.UserName = ""
            End If
        End If

        Try
            If IsNumeric(viewId) Then
                edTableToView.ViewID = viewId
            Else
                edTableToView.ViewID = -1
            End If
        Catch ex As Exception
        End Try






    End Sub




    Protected Sub Page_PreRender(sender As Object, e As System.EventArgs) Handles Me.PreRender
        'ClientScript.RegisterStartupScript(Me.GetType(), "CreateGridHeader", "<script>CreateGridHeader('DataDiv', 'editableGrid1_GridView1', 'HeaderDiv');</script>")


        If Not IsPostBack Then
            Dim datum As String = DateTime.Now.ToString("yyyy-MM-dd")
            edTableToView.Filters.Add("[PLAN_DATE]", " between N'" + datum + "' AND '" + datum + "'")
            BuildList(datum, datum)
        Else

        End If



        'If edTableToView.IsReadOnly Then
        '    edTableToView.CollectHeaderColumn(1)
        'End If
        Title = edTableToView.TableDescription


        'If edTableToView.copyFlag Then
        '    edTableToView.AddFormMode(edTableToView.copyId)
        'End If

        edTableToView.SubReport.Controls.Add(createSubreport(edTableToView.TableId))


        Dim script As String = sqlf.GetScalar("SELECT Gridcommand_handler FROM UserModules.dbo.ModuleTables WHERE id=" & edTableToView.TableId)
        If script <> "" Then
            gridcommandhandlerscript.Controls.Add(New Literal With {.Text = "<script type=""text/javascript"">" & script & "</script>"})
        End If
    End Sub

    Private Function createSubreport(tid As String) As GridView

        Dim gv As New GridView

        Dim query As String = sqlf.GetScalar("SELECT isnull(SUBREPORT_QUERY,'') from usermodules.dbo.moduletables where id = " & tid)
        If query <> "" And Not (query.Contains("update") Or
                               query.Contains("delete") Or
                               query.Contains("exec") Or
                               query.Contains("truncate") Or
                               query.Contains("drop")) Then
            gv.DataSource = sqlf.GetData(query)
            Try
                gv.DataBind()
            Catch ex As Exception
                Return Nothing
            End Try
        End If
        Return gv
    End Function

    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

    Protected Sub filt() Handles edTableToView.GridFilterChanged
        Dim targetfrom As String = edTableToView.Filters("[PLAN_DATE]")
        Dim targetto As String = edTableToView.Filters("[PLAN_DATE]")
        If Not IsNothing(targetfrom) Then
            If targetfrom.Contains("AND") Then
                targetfrom = targetfrom.Split("N")(1).Trim("A").Trim().Trim("'").Replace("-", "")
                targetto = targetto.Split("N")(2).Trim("D").Trim().Trim("'").Replace("-", "")
                BuildList(targetfrom, targetto)
            End If
        End If
    End Sub

    Protected Sub edTableToView_PreRender(sender As Object, e As System.EventArgs) Handles edTableToView.PreRender

    End Sub

    Private Sub BuildList(ByVal targetfrom As String, ByVal targetto As String)
        Dim sql As New StringBuilder
        sql.Clear()
        sql.AppendLine("declare @date_start as date ")
        sql.AppendLine("declare @date_end as date ")
        sql.AppendLine("set @date_start = '" + targetfrom + "' ")
        sql.AppendLine("set @date_end = '" + targetto + "'; ")
        sql.AppendLine(" ")
        sql.AppendLine("with TT as ")
        sql.AppendLine("( ")
        sql.AppendLine("SELECT distinct ")
        sql.AppendLine(" ")
        sql.AppendLine(" 		TPL.PLANT_CODE as [Plant] ")
        sql.AppendLine(" 		,TPL.PO_NO as [PO] ")
        sql.AppendLine(" 		,TPL.TRNK_MODEL_CODE AS MODEL ")
        sql.AppendLine(" 		,TPL.MODEL_VER_NO AS [BOM] ")
        sql.AppendLine(" 		,TLN.LINE_NM AS LINE ")
        sql.AppendLine("		,SUBSTRING (TPL.PLAN_YMD, 1, 4) + '-' + SUBSTRING (TPL.PLAN_YMD, 5, 2) + '-' + SUBSTRING (TPL.PLAN_YMD, 7, 2) AS [PLAN DATE] ")
        sql.AppendLine(" 		,SUBSTRING (TPL.PLAN_START_DT, 9, 2) + ':' + SUBSTRING (TPL.PLAN_START_DT, 11, 2) + ':' + SUBSTRING (TPL.PLAN_START_DT, 13, 2) AS [PLAN START TIME] ")
        sql.AppendLine("		 ")
        sql.AppendLine(" ")
        sql.AppendLine(",cast((select max(plan_ymd) as result ")
        sql.AppendLine("		from [GMESData].[dbo].[TBM_PM_PO_PLAN] ")
        sql.AppendLine("		where plan_ymd <= replace(cast(dateadd(day, cast(Pat.days_delay as integer), cast(SUBSTRING (TPL.PLAN_YMD, 1, 4) + '-' + SUBSTRING (TPL.PLAN_YMD, 5, 2) + '-' + SUBSTRING (TPL.PLAN_YMD, 7, 2) as date)) as varchar), '-','') ")
        sql.AppendLine("		and LINE_CODE = 'M040') as date) as [DELIVERY DATE] ")
        sql.AppendLine("		,Pat.Delivery as [DELIVERY TIME] ")
        sql.AppendLine(" 		,CAST (TPL.PLAN_QTY AS INTEGER) AS [PLAN QTY] ")
        sql.AppendLine("		,TBOM.PEGGEDREQT as ASSY ")
        sql.AppendLine(" 	 ")
        sql.AppendLine("		,SJ_JIT.component as [COMPONENT] ")
        sql.AppendLine("		,cast(sj_jit.QTY as integer) * CAST (TPL.PLAN_QTY AS INTEGER) as [COMPONENT QTY] ")
        sql.AppendLine("		,sj_jit.pattern AS [PATTERN] ")
        sql.AppendLine("		,sj_jit.vendor AS [VENDOR] ")
        sql.AppendLine("		,sj_jit.vendor_name AS [VENDOR NAME] ")
        sql.AppendLine("		,sj_jit.pgr_purch AS [PURCHASER] ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" 	FROM ")
        sql.AppendLine(" 		GMESData.dbo.TBM_PM_PO_PLAN TPL ")
        sql.AppendLine(" ")
        sql.AppendLine(" 	LEFT JOIN GmesData.dbo.TB_TBM_MD_LINE TLN ON TPL.LINE_CODE = TLN.LINE_CODE ")
        sql.AppendLine(" ")
        sql.AppendLine("	LEFT JOIN UserModules.[user].PICK_LIST_MAIN_LINE TBOM ON TPL.PO_NO COLLATE Cyrillic_General_CI_AI = '0' + TBOM.[order] ")
        sql.AppendLine(" 	AND (TBOM.PEGGEDREQT in (select distinct top_model from [UserModules].[user].[SJ_JIT_BOM])) ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("	left join UserModules.[user].[SJ_JIT_BOM] sj_jit ")
        sql.AppendLine("	on TBOM.PEGGEDREQT = sj_jit.top_model ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("	left join [UserModules].[user].[delivery_pattern] as Pat ")
        sql.AppendLine("	on Pat.Pattern = sj_jit.pattern ")
        sql.AppendLine("	and cast(SUBSTRING (TPL.PLAN_START_DT, 9, 2) + ':' + SUBSTRING (TPL.PLAN_START_DT, 11, 2) + ':' + SUBSTRING (TPL.PLAN_START_DT, 13, 2) as time) >= cast(Production_start as time) ")
        sql.AppendLine("	and cast(SUBSTRING (TPL.PLAN_START_DT, 9, 2) + ':' + SUBSTRING (TPL.PLAN_START_DT, 11, 2) + ':' + SUBSTRING (TPL.PLAN_START_DT, 13, 2) as time) < cast(Production_end as time) ")
        sql.AppendLine("	 ")
        sql.AppendLine(" ")
        sql.AppendLine(" 	WHERE ")
        sql.AppendLine(" 	TPL.PLAN_YMD between replace(@date_start, '-','') and replace(@date_end, '-','') ")
        sql.AppendLine(" 	AND ( LEN(TLN.LINE_NM) = 1 or 	TLN.LINE_NM = 'M08') ")
        sql.AppendLine(" 	AND TPL.TECO_DESC NOT LIKE '%TECO%' ")
        sql.AppendLine("	and TBOM.PEGGEDREQT is not null ")
        sql.AppendLine("	) ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("MERGE [UserModules].[user].[SJ_JIT] AS trg USING TT AS src ")
        sql.AppendLine(" ON trg.[PO] collate Cyrillic_General_CI_AS = src.PO collate Cyrillic_General_CI_AS ")
        sql.AppendLine(" and trg.[assy] collate Cyrillic_General_CI_AS = src.assy collate Cyrillic_General_CI_AS ")
        sql.AppendLine(" and trg.[component] collate Cyrillic_General_CI_AS = src.[component] collate Cyrillic_General_CI_AS ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" WHEN NOT MATCHED THEN ")
        sql.AppendLine(" 	INSERT ( ")
        sql.AppendLine(" [PLANT] ")
        sql.AppendLine(" ,[PO] ")
        sql.AppendLine(" ,[MODEL] ")
        sql.AppendLine(" ,[BOM] ")
        sql.AppendLine(" ,[LINE] ")
        sql.AppendLine(" ,[PLAN_DATE] ")
        sql.AppendLine(" ,[PLAN_TIME] ")
        sql.AppendLine(" ,[DELIVERY_DATE] ")
        sql.AppendLine(" ,[DELIVERY_TIME] ")
        sql.AppendLine(" ,[PLAN_QTY] ")
        sql.AppendLine(" ,[ASSY] ")
        sql.AppendLine(" ,[COMPONENT] ")
        sql.AppendLine(" ,[COMPONENT_QTY] ")
        sql.AppendLine(" ,[PATTERN] ")
        sql.AppendLine(" ,[VENDOR] ")
        sql.AppendLine(" ,[VENDOR_NAME] ")
        sql.AppendLine(" ,[PURCHASER] ")
        sql.AppendLine(" 	) ")
        sql.AppendLine(" VALUES ")
        sql.AppendLine(" 	( ")
        sql.AppendLine(" 		src.Plant ")
        sql.AppendLine("		,src.po ")
        sql.AppendLine("		,src.model ")
        sql.AppendLine("		,src.bom ")
        sql.AppendLine("		,src.line ")
        sql.AppendLine("		,src.[Plan date] ")
        sql.AppendLine("		,src.[plan start time] ")
        sql.AppendLine("		,src.[delivery date] ")
        sql.AppendLine("		,src.[delivery time] ")
        sql.AppendLine("		,src.[plan qty] ")
        sql.AppendLine("		,src.[assy] ")
        sql.AppendLine("		,src.[component] ")
        sql.AppendLine("		,src.[component qty] ")
        sql.AppendLine("		,src.[pattern] ")
        sql.AppendLine("		,src.[vendor] ")
        sql.AppendLine("		,src.[vendor name] ")
        sql.AppendLine("		,src.[purchaser] ")
        sql.AppendLine(" 	) ")
        sql.AppendLine(" WHEN MATCHED THEN ")
        sql.AppendLine(" UPDATE SET ")
        sql.AppendLine(" ")
        sql.AppendLine(" [LINE] = src.line ")
        sql.AppendLine(" ,[PLAN_DATE] = src.[Plan date] ")
        sql.AppendLine(" ,[PLAN_TIME] = src.[plan start time] ")
        sql.AppendLine(" ,[DELIVERY_DATE] = src.[delivery date] ")
        sql.AppendLine(" ,[DELIVERY_TIME] = src.[delivery time] ")
        sql.AppendLine(" ,[PLAN_QTY] = src.[plan qty] ")
        sql.AppendLine(" ,[COMPONENT_QTY] = src.[component qty] ")
        sql.AppendLine(" ,[PATTERN] = src.[pattern] ")
        sql.AppendLine(" ,[VENDOR] = src.[vendor] ")
        sql.AppendLine(" ,[VENDOR_NAME] = src.[vendor name] ")
        sql.AppendLine(" ,[PURCHASER] = src.[purchaser]; ")


        sqlf.NonQuery(sql.ToString)

        edTableToView.RefreshData()

    End Sub



    Private Sub ActionRequest(ByVal sender As Object, ByVal e As CommandEventArgs) Handles edTableToView.ActionRequest
        If e.CommandArgument = "BUILDLIST" Then

            Dim targetfrom As String = edTableToView.Filters("[PLAN_DATE]")
            Dim targetto As String = edTableToView.Filters("[PLAN_DATE]")
            If Not IsNothing(targetfrom) Then
                If targetfrom.Contains("AND") Then
                    targetfrom = targetfrom.Split("N")(1).Trim("A").Trim().Trim("'").Replace("-", "")
                    targetto = targetto.Split("N")(2).Trim("D").Trim().Trim("'").Replace("-", "")
                    BuildList(targetfrom, targetto)
                End If
            End If
        End If

    End Sub


    Private Sub RowCommand(ByVal sender As Object, ByVal e As GridViewCommandEventArgs) Handles edTableToView.RowCommand
        If e.CommandName = "ACT" Then

            rowIdToUpdate = e.CommandArgument
            Dim sql As New StringBuilder
            sql.Clear()
            sql.AppendLine("insert into ")
            sql.AppendLine("[UserModules].[user].[SAFETY_INSPECTION] ")
            sql.AppendLine("(PROJECT, MODEL_CODE, TESTING_DATE) ")
            sql.AppendLine("select ")
            sql.AppendLine("project ")
            sql.AppendLine(",model_code ")
            sql.AppendLine(",convert(char(10), getdate(), 120) as [date] ")
            sql.AppendLine("from [usermodules].[user].[SAFETY_INSPECTION_TEMP] ")
            sql.AppendLine("where id = " + rowIdToUpdate)

            sqlf.NonQuery(sql.ToString)


            Dim targetfrom As String = edTableToView.Filters("[PLAN]")
            Dim targetto As String = edTableToView.Filters("[PLAN]")
            If Not IsNothing(targetfrom) Then
                If targetfrom.Contains("AND") Then
                    targetfrom = targetfrom.Split("N")(1).Trim("A").Trim().Trim("'").Replace("-", "")
                    targetto = targetto.Split("N")(2).Trim("D").Trim().Trim("'").Replace("-", "")
                    BuildList(targetfrom, targetto)
                End If
            End If

        End If
    End Sub
    Protected Sub RowDataBoundComplete(ByRef row As GridViewRow) Handles edTableToView.RowDataBoundComplete
        If row.RowType = DataControlRowType.DataRow Then
            If row.Cells(3).Text.Equals("Never") Then
                row.BackColor = Drawing.Color.FromArgb(255, 51, 51)
            End If

            If row.Cells(14).Text.Equals("SRP") Or row.Cells(14).Text.Equals("EC") Then
                row.BackColor = Drawing.Color.FromArgb(204, 255, 255)
            End If

            If row.Cells(3).Text.Contains("Delay") Then
                row.BackColor = Drawing.Color.FromArgb(255, 255, 153)
            End If

            If row.Cells(18).Text.Equals("Y") Then
                row.BackColor = Drawing.Color.FromArgb(153, 204, 153)
            End If

        End If

    End Sub

    Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)

    End Sub


    Private Sub Xml_viewTable_PreRenderComplete(sender As Object, e As EventArgs) Handles Me.PreRenderComplete
        'edTableToView.CollectHeaderColumn(2)
        'edTableToView.CollectHeaderColumn(3)
        'edTableToView.CollectHeaderColumn(4)
        'edTableToView.CollectHeaderColumn(5)
    End Sub
End Class