ModuleMaster/sj_vend/prodplan.aspx.vb - main - ivan/ModMas

ModuleMaster/sj_vend/prodplan.aspx.vb
393 строки20 KB
i.krivokon
Initial commit
13 мар 2026, 13:49
13 мар 2026, 13:4900d9fb6
Imports System.Data
Imports System.Data.SqlClient
Imports sharedClasses.dbFunctions
Imports System.Net
Imports System.IO

Partial Class detail
    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

    Dim tableId 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
            edTableToView.Message = "-"
        End If


        If Not IsNothing(Request.QueryString("tableId")) Then
            tableId = Request.QueryString("tableId")
        End If
        If Not IsNothing(Request.QueryString("t")) Then
            tableId = Request.QueryString("t")
        End If
        edTableToView.IsReadOnly = True
        edTableToView.UserName = userid.ToUpper
        If IsNumeric(tableId) Then
            edTableToView.TableId = tableId
        End If
        edTableToView.SubReport.Controls.Add(createSubreport(tableId))
    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
        Title = edTableToView.TableDescription
        edTableToView.HeaderRow.Cells(edTableToView.HeaderRow.Cells.Count - 2).Controls.Add(New CheckBox With {.ID = "selectall", .ClientIDMode = UI.ClientIDMode.Static, .ToolTip = "@@@"})

        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

    Protected Sub AddCheckbox(ByRef r As GridViewRow) Handles edTableToView.RowDataBoundComplete
        If r.RowType = DataControlRowType.DataRow Then
            r.Cells(r.Cells.Count - 2).Controls.Add(New CheckBox With {.ID = r.Cells(0).Text,
                                                    .ClientIDMode = UI.ClientIDMode.Static,
                                                    .CssClass = "check",
                                                    .ToolTip = "Check to print this row"})
        End If
    End Sub

    Private Function createSubreport(tid As String) As GridView

        Dim gv As New GridView
        Dim sqlf As New sharedClasses.dbFunctions.SqlFunctions
        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()

        edTableToView.CollectHeaderColumn(4, 6)
        edTableToView.CollectHeaderColumn(5, 6)
        edTableToView.CollectHeaderColumn(6)
        edTableToView.CollectHeaderColumn(7, 6)
        edTableToView.CollectHeaderColumn(8, 6)
        edTableToView.CollectHeaderColumn(9, 6)
        edTableToView.CollectHeaderColumn(11, 6)
        'edTableToView.CollectHeaderColumn(10)
    End Sub

    Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)

    End Sub

    Protected Sub action(sender As Object, e As CommandEventArgs) Handles edTableToView.ActionRequest

        Dim targetDate As String = edTableToView.Filters("[MAIN_PROD_DATE]")
        If Not IsNothing(targetDate) Then
            targetDate = targetDate.Split("N")(1).Trim("A").Trim().Trim("'")

            edTableToView.Message = "Updated data for " & targetDate
            Select Case e.CommandArgument
                Case "BUILD_LIST"
                    Dim sql As New StringBuilder
                    'sql.AppendLine("WITH LIST as ( ")
                    'sql.AppendLine("SELECT ")
                    'sql.AppendLine("SUBSTRING(TPL.PLAN_YMD,1,4)+'-'+ SUBSTRING(TPL.PLAN_YMD,5,2)+'-'+SUBSTRING(TPL.PLAN_YMD,7,2) as PLAN_YMD ")
                    'sql.AppendLine(", TPL.PLANT_CODE ")
                    'sql.AppendLine(", TPL.PO_NO ")
                    'sql.AppendLine(", TPL.TRNK_MODEL_CODE as MODEL ")
                    'sql.AppendLine(", TPL.MODEL_VER_NO as BOM_VER ")
                    'sql.AppendLine(", TLN.LINE_NM as LINE ")
                    '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_DT ")
                    'sql.AppendLine(", CAST(TPL.PLAN_QTY as INTEGER ) AS PLAN_QTY ")
                    'sql.AppendLine(", TBOM.MATERIAL AS MATERIAL ")
                    'sql.AppendLine(", TBOM.MATERIAL_DESCRIPTION as DESCRIPTIOM ")
                    'sql.AppendLine(", TBOM.MRP_CONTR as DISPO ")
                    'sql.AppendLine(", JIT.DO ")
                    'sql.AppendLine(", JIT.DO_DATE ")
                    'sql.AppendLine(", JIT.DO_TIME ")
                    'sql.AppendLine(", JIT.VENDOR ")
                    'sql.AppendLine(", JIT.DO_QTY ")
                    'sql.AppendLine(", JIT.MATERIAL as DO_MATERIAL ")
                    'sql.AppendLine(", JIT.MATERIAL_DESC ")
                    ''To-DO - поменять на компоненты по бому
                    'sql.AppendLine(", (SELECT COUNT(*) FROM UserModules.[user].SUB_CONTRACT_COMPONENT C WHERE C.DO_NO = JIT.DO AND C.MATERIAL = JIT.MATERIAL) as COMP_COUT ")

                    'sql.AppendLine("FROM ")
                    'sql.AppendLine("GMESData.dbo.TBM_PM_PO_PLAN TPL ")
                    'sql.AppendLine("LEFT JOIN GmesData.dbo.TB_TBM_MD_LINE TLN ")
                    'sql.AppendLine("ON TPL.LINE_CODE = TLN.LINE_CODE ")
                    'sql.AppendLine("LEFT JOIN UserModules.[user].PICK_LIST_MAIN_LINE TBOM ")
                    'sql.AppendLine("ON TPL.PO_NO Collate Cyrillic_General_CI_AI = '0' + TBOM.[order] ")

                    'sql.AppendLine("AND (TBOM.MRP_CONTR = 'H06' OR TBOM.MRP_CONTR = 'J01' OR TBOM.MRP_CONTR = 'J07' OR TBOM.MRP_CONTR = 'WJ4') ")
                    ''sql.AppendLine("AND TBOM.MRP_CONTR = 'H06' ")

                    'sql.AppendLine("LEFT JOIN UserModules.[user].jit JIT ")
                    'sql.AppendLine("on TPL.PO_NO Collate Cyrillic_General_CI_AI = JIT.PROD_ORDER ")
                    'sql.AppendLine("AND JIT.VENDOR = 'DOZ1' ")
                    'sql.AppendLine(" AND JIT.MATERIAL = TBOM.MATERIAL ")

                    'sql.AppendLine("WHERE TPL.PLAN_YMD = '" & targetDate.Replace("-", "") & "' ")
                    'sql.AppendLine("And LEN(TLN.LINE_NM) = 1 ")
                    'sql.AppendLine(" AND TPL.TECO_DESC NOT LIKE '%TECO%' ")

                    ''sql.AppendLine("AND TBOM.MATERIAL IS NOT NULL ")
                    ''sql.AppendLine("AND JIT.DO IS NOT NULL ")

                    'sql.AppendLine(") ")

                    'sql.AppendLine("MERGE UserModules.[user].[MAIN_PLAN_VS_DO] as trg ")
                    'sql.AppendLine("USING LIST AS src ")
                    'sql.AppendLine("ON trg.[MAIN_PO] = src.PO_NO Collate Cyrillic_General_CI_AS ")
                    'sql.AppendLine("AND isnull(trg.DO,'') = isnull(SRC.DO,'') Collate Cyrillic_General_CI_AS ")
                    'sql.AppendLine("AND isnull(trg.PICK_LIST_ASSY,'') = isnull(SRC.MATERIAL,'') Collate Cyrillic_General_CI_AS ")

                    'sql.AppendLine("when NOT MATCHED then ")
                    'sql.AppendLine("INSERT ")
                    'sql.AppendLine("([MAIN_PROD_DATE] ")
                    'sql.AppendLine(",[PLANT] ")
                    'sql.AppendLine(",[MAIN_PO] ")
                    'sql.AppendLine(",[MAIN_MODEL] ")
                    'sql.AppendLine(",[BOM_VER] ")
                    'sql.AppendLine(",[LINE] ")
                    'sql.AppendLine(",[START_TIME] ")
                    'sql.AppendLine(",[PLAN_QTY] ")
                    'sql.AppendLine(",[PICK_LIST_ASSY] ")
                    'sql.AppendLine(",[DESCRIPTION] ")
                    'sql.AppendLine(",[MRP] ")
                    'sql.AppendLine(",[DO] ")
                    'sql.AppendLine(",[DO_DATE] ")
                    'sql.AppendLine(",[DO_TIME] ")
                    'sql.AppendLine(",[VENDOR] ")
                    'sql.AppendLine(",[DO_QTY] ")
                    'sql.AppendLine(",[DO_MATERIAL] ")
                    'sql.AppendLine(",[DO_MATERIAL_DESCRIPTION] ")
                    'sql.AppendLine(",[COMPONENT_COUNT] ")
                    'sql.AppendLine(",[SJ_PO] ")
                    'sql.AppendLine(") VALUES ( ")
                    'sql.AppendLine("src.PLAN_YMD ")
                    'sql.AppendLine(", src.PLANT_CODE ")
                    'sql.AppendLine(", src.PO_NO ")
                    'sql.AppendLine(", src.MODEL ")
                    'sql.AppendLine(", src.BOM_VER ")
                    'sql.AppendLine(", src.LINE ")
                    'sql.AppendLine(", src.PLAN_START_DT ")
                    'sql.AppendLine(", src.PLAN_QTY ")
                    'sql.AppendLine(", src.MATERIAL ")
                    'sql.AppendLine(", src.DESCRIPTIOM ")
                    'sql.AppendLine(", src.DISPO ")
                    'sql.AppendLine(", src.DO ")
                    'sql.AppendLine(", src.DO_DATE ")
                    'sql.AppendLine(", src.DO_TIME ")
                    'sql.AppendLine(", src.VENDOR ")
                    'sql.AppendLine(", src.DO_QTY ")
                    'sql.AppendLine(", src.DO_MATERIAL ")
                    'sql.AppendLine(", src.MATERIAL_DESC ")
                    'sql.AppendLine(", src.COMP_COUT ")
                    'sql.AppendLine(", 'SJ'+src.PO_NO+'_' + src.material); ")

                    sql.AppendLine(" WITH LIST AS ")
                    sql.AppendLine(" ( ")
                    sql.AppendLine(" 	SELECT ")
                    sql.AppendLine(" 		SUBSTRING (TPL.PLAN_YMD, 1, 4) + '-' + SUBSTRING (TPL.PLAN_YMD, 5, 2) + '-' + SUBSTRING (TPL.PLAN_YMD, 7, 2) AS PLAN_YMD, ")
                    sql.AppendLine(" 		TPL.PLANT_CODE, ")
                    sql.AppendLine(" 		TPL.PO_NO, ")
                    sql.AppendLine(" 		TPL.TRNK_MODEL_CODE AS MODEL, ")
                    sql.AppendLine(" 		TPL.MODEL_VER_NO AS BOM_VER, ")
                    sql.AppendLine(" 		TLN.LINE_NM AS LINE, ")
                    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_DT, ")
                    sql.AppendLine(" 		CAST (TPL.PLAN_QTY AS INTEGER) AS PLAN_QTY, ")
                    sql.AppendLine(" 		TBOM.MATERIAL AS MATERIAL, ")
                    sql.AppendLine(" 		TBOM.MATERIAL_DESCRIPTION AS DESCRIPTIOM, ")
                    sql.AppendLine(" 		TBOM.MRP_CONTR AS DISPO, ")
                    sql.AppendLine(" 		JIT.DO, ")
                    sql.AppendLine(" 		JIT.DO_DATE, ")
                    sql.AppendLine(" 		JIT.DO_TIME, ")
                    sql.AppendLine(" 		JIT.VENDOR, ")
                    sql.AppendLine(" 		JIT.DO_QTY, ")
                    sql.AppendLine(" 		JIT.MATERIAL AS DO_MATERIAL, ")
                    sql.AppendLine(" 		JIT.MATERIAL_DESC, ")
                    sql.AppendLine(" 		( ")
                    sql.AppendLine(" 			SELECT ")
                    sql.AppendLine(" 				COUNT (*) ")
                    sql.AppendLine(" 			FROM ")
                    sql.AppendLine(" 				UserModules.[user].SUB_CONTRACT_COMPONENT C ")
                    sql.AppendLine(" 			WHERE ")
                    sql.AppendLine(" 				C.DO_NO = JIT.DO ")
                    sql.AppendLine(" 			AND C.MATERIAL = JIT.MATERIAL ")
                    sql.AppendLine(" 		) AS COMP_COUT ")
                    sql.AppendLine(" 	FROM ")
                    sql.AppendLine(" 		GMESData.dbo.TBM_PM_PO_PLAN TPL ")
                    sql.AppendLine(" 	LEFT JOIN GmesData.dbo.TB_TBM_MD_LINE TLN ON TPL.LINE_CODE = TLN.LINE_CODE ")
                    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 ( ")
                    sql.AppendLine(" 		TBOM.MRP_CONTR = 'H06' ")
                    sql.AppendLine(" 		OR TBOM.MRP_CONTR = 'J01' ")
                    sql.AppendLine(" 		OR TBOM.MRP_CONTR = 'J07' ")
                    sql.AppendLine(" 		OR TBOM.MRP_CONTR = 'J08' ")
                    sql.AppendLine(" 		OR TBOM.MRP_CONTR = 'WJ4' ")
                    sql.AppendLine(" 	) ")
                    sql.AppendLine(" 	LEFT JOIN UserModules.[user].jit JIT ON TPL.PO_NO COLLATE Cyrillic_General_CI_AI = JIT.PROD_ORDER ")
                    sql.AppendLine(" 	AND JIT.VENDOR = 'DOZ1' ")
                    sql.AppendLine(" 	AND ISNULL(JIT.MATERIAL, '') = TBOM.MATERIAL ")
                    sql.AppendLine(" ")
                    sql.AppendLine(" 	WHERE ")
                    sql.AppendLine(" 	TPL.PLAN_YMD = '" & targetDate.Replace("-", "") & "' ")
                    sql.AppendLine("    AND (TLN.LINE_NM like 'M0%' or TLN.LINE_NM = 'W') ")
                    sql.AppendLine(" 	AND TPL.TECO_DESC NOT LIKE '%TECO%' ")
                    sql.AppendLine(" ) ")

                    sql.AppendLine(" MERGE UserModules.[user].[MAIN_PLAN_VS_DO] AS trg USING LIST AS src ")
                    sql.AppendLine(" ON trg.[MAIN_PO] = src.PO_NO COLLATE Cyrillic_General_CI_AS ")
                    sql.AppendLine(" AND isnull(trg.PICK_LIST_ASSY, '') = isnull(SRC.MATERIAL, '') COLLATE Cyrillic_General_CI_AS ")
                    sql.AppendLine(" ")
                    sql.AppendLine(" WHEN NOT MATCHED THEN ")
                    sql.AppendLine(" 	INSERT ( ")
                    sql.AppendLine(" 		[MAIN_PROD_DATE], ")
                    sql.AppendLine(" 		[PLANT], ")
                    sql.AppendLine(" 		[MAIN_PO], ")
                    sql.AppendLine(" 		[MAIN_MODEL], ")
                    sql.AppendLine(" 		[BOM_VER], ")
                    sql.AppendLine(" 		[LINE], ")
                    sql.AppendLine(" 		[START_TIME], ")
                    sql.AppendLine(" 		[PLAN_QTY], ")
                    sql.AppendLine(" 		[PICK_LIST_ASSY], ")
                    sql.AppendLine(" 		[DESCRIPTION], ")
                    sql.AppendLine(" 		[MRP], ")
                    sql.AppendLine(" 		[DO], ")
                    sql.AppendLine(" 		[DO_DATE], ")
                    sql.AppendLine(" 		[DO_TIME], ")
                    sql.AppendLine(" 		[VENDOR], ")
                    sql.AppendLine(" 		[DO_QTY], ")
                    sql.AppendLine(" 		[DO_MATERIAL], ")
                    sql.AppendLine(" 		[DO_MATERIAL_DESCRIPTION], ")
                    sql.AppendLine(" 		[COMPONENT_COUNT], ")
                    sql.AppendLine(" 		[SJ_PO] ")
                    sql.AppendLine(" 	) ")
                    sql.AppendLine(" VALUES ")
                    sql.AppendLine(" 	( ")
                    sql.AppendLine(" 		src.PLAN_YMD, ")
                    sql.AppendLine(" 		src.PLANT_CODE, ")
                    sql.AppendLine(" 		src.PO_NO, ")
                    sql.AppendLine(" 		src.MODEL, ")
                    sql.AppendLine(" 		src.BOM_VER, ")
                    sql.AppendLine(" 		src.LINE, ")
                    sql.AppendLine(" 		src.PLAN_START_DT, ")
                    sql.AppendLine(" 		src.PLAN_QTY, ")
                    sql.AppendLine(" 		src.MATERIAL, ")
                    sql.AppendLine(" 		src.DESCRIPTIOM, ")
                    sql.AppendLine(" 		src.DISPO, ")
                    sql.AppendLine(" 		src.DO, ")
                    sql.AppendLine(" 		src.DO_DATE, ")
                    sql.AppendLine(" 		src.DO_TIME, ")
                    sql.AppendLine(" 		src.VENDOR, ")
                    sql.AppendLine(" 		src.DO_QTY, ")
                    sql.AppendLine(" 		src.DO_MATERIAL, ")
                    sql.AppendLine(" 		src.MATERIAL_DESC, ")
                    sql.AppendLine(" 		src.COMP_COUT, ")
                    sql.AppendLine(" 		'SJ' + src.PO_NO + '_' + src.material ")
                    sql.AppendLine(" 	) ")
                    sql.AppendLine(" WHEN MATCHED THEN ")
                    sql.AppendLine(" UPDATE SET ")
                    sql.AppendLine(" [DO] = src.do, ")
                    sql.AppendLine(" 		[DO_DATE] = src.do_date, ")
                    sql.AppendLine(" 		[DO_TIME] = src.do_time, ")
                    sql.AppendLine(" 		[VENDOR] = src.vendor, ")
                    sql.AppendLine(" 		[DO_QTY] = src.do_qty, ")
                    sql.AppendLine(" 		[DO_MATERIAL] = src.do_material, ")
                    sql.AppendLine(" 		[DO_MATERIAL_DESCRIPTION] = src.material_desc, ")
                    sql.AppendLine(" 		[COMPONENT_COUNT]= src.comp_cout; ")

                    sqlf.NonQuery(sql.ToString)
                    'edTableToView.Message = sql.ToString

                    'UPDate BOM info
                    Dim params As New Hashtable
                    orchestraSql(893, params) ' Update USE YN flag by ASSY LIST
                    'Get list of assy for prod date
                    sql.Clear()
                    sql.AppendLine("select t1.PICK_LIST_ASSY, isnull(t2.dispo,'') as dispo from ( ")
                    sql.AppendLine("SELECT DISTINCT PICK_LIST_ASSY ")
                    sql.AppendLine("from UserModules.[user].[MAIN_PLAN_VS_DO] ")
                    sql.AppendLine("WHERE main_prod_date = '" & targetDate & "' ")
                    sql.AppendLine(") t1 left join WMSData.[dbo].TB_MATERIAL_MASTER t2 ")
                    sql.AppendLine("on t1.PICK_LIST_ASSY collate Cyrillic_General_CI_AI = t2.matnr collate Cyrillic_General_CI_AI ")
                    'edTableToView.Message = sql.ToString
                    Dim assyList As DataTable = sqlf.GetData(sql.ToString)
                    For Each assy As DataRow In assyList.Rows
                        params.Clear()
                        params.Add("MATR", assy("PICK_LIST_ASSY"))
                        'Delte and insert new rows from GMES
                        If assy("DISPO").ToString.Trim = "J07" Then
                            orchestraSql(894, params)
                        Else
                            orchestraSql(685, params)
                        End If
                    Next
                    'Update USE_Y_N 
                    params.Clear()
                    orchestraSql(895, params)
                    edTableToView.RefreshData()

            End Select
        End If
    End Sub

    Private Function orchestraSql(queryId As Integer, params As Hashtable) As DataTable
        Try
            'Dim url As String = "http://serkweb.serk.lan:8080/modulemaster/Handlers/getsqldata.ashx?id=" & queryId
            Dim url As String = "http://localhost:61349/Handlers/getsqldata.ashx?id=" & queryId

            Dim f As Boolean = True
            For Each k As DictionaryEntry In params
                url &= "&"
                url &= k.Key & "=" & k.Value
            Next
            Dim wb As WebRequest
            wb = WebRequest.Create(url)
            Dim res As WebResponse = wb.GetResponse
            Dim sr As New StreamReader(res.GetResponseStream)
            Return Newtonsoft.Json.JsonConvert.DeserializeObject(Of DataTable)(sr.ReadToEnd)
        Catch ex As Exception
            Return Nothing
        End Try

    End Function
End Class