ModuleMaster/mm/gr_aging.aspx.vb - main - ivan/ModMas

ModuleMaster/mm/gr_aging.aspx.vb
202 строки11 KB
i.krivokon
Initial commit
13 мар 2026, 13:49
13 мар 2026, 13:4900d9fb6

Imports System.Data
Imports System.IO
Imports System.Web.UI.DataVisualization.Charting

Partial Class r
    Inherits System.Web.UI.Page
    Private line As String


    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        Dim Day_Flag As Boolean = True
        Dim Line_Flag As Boolean = False
        Dim sql2 As New StringBuilder 'sql2 - для таблицы


        line = Request("line")
        If Not String.IsNullOrEmpty(line) And line <> "Total VD" Then
            Line_Flag = True
        End If

        If Not IsPostBack Then
            graph_date.Value = Request("date")
        End If


        If graph_date.Value <> "" Then
            Dim tmp_date As Date = graph_date.Value
            If DateTime.Now.ToString("yyyy/MM/dd") <> Date.Parse(graph_date.Value).ToString("yyyy/MM/dd") Then
                Day_Flag = False
            End If
        End If


        'Строка запроса для формирования таблицы
        sql2.AppendLine("declare @FactoryStock TABLE (line varchar(10), current_Out numeric) ")
        sql2.AppendLine("declare @graph_date datetime ")
        If Day_Flag Then
            sql2.AppendLine("set @graph_date = cast(getdate() as datetime) --Здесь в asp.net устанавливаем реальную дату, за которую хотим посмотреть. Если это не текущий день, вторая часть, которая после union all не нужна ")
        Else
            sql2.AppendLine("set @graph_date = cast('" + Date.Parse(graph_date.Value).ToString("yyyy/MM/dd") + "' as datetime) --Здесь в asp.net устанавливаем реальную дату, за которую хотим посмотреть. Если это не текущий день, вторая часть, которая после union all не нужна ")
        End If
        sql2.AppendLine("declare @mysql_date varchar(max) ")
        sql2.AppendLine("set @mysql_date = cast(datepart(yyyy, @graph_date) as varchar(max)) + right('00' + cast(datepart(mm, @graph_date) as varchar(max)), 2) + right('00'+cast(datepart(dd, @graph_date) as varchar(max)), 2)")
        sql2.AppendLine("declare @mysql_result table (line varchar(max), [out] int, timemark varchar(max)) ")
        sql2.AppendLine(" ")
        sql2.AppendLine(" ")
        sql2.AppendLine("declare @tsql varchar(max) ")
        sql2.AppendLine("set @tsql = ' ")
        sql2.AppendLine("select ")
        sql2.AppendLine("line ")
        sql2.AppendLine(",current_Out ")
        sql2.AppendLine(" from openquery (GMES20,'' ")
        sql2.AppendLine("-- ------------ TOTAL REPORT -------------- ")
        sql2.AppendLine("SELECT ")
        sql2.AppendLine("NVL(NVL(current_PROD.LINE_CODE, current_TEMP.LINE_CODE), current_OUT.LINE_CODE) as LINE ")
        sql2.AppendLine(",current_OUT.QTY as current_Out ")
        sql2.AppendLine("from ")
        sql2.AppendLine("( ")
        sql2.AppendLine("--PROD ")
        sql2.AppendLine("(select ")
        sql2.AppendLine("plant_code ")
        sql2.AppendLine(",LINE_CODE ")
        sql2.AppendLine(",sum (set_qty) as qty ")
        sql2.AppendLine("from TBP_PM_PACK_ACRS ")
        sql2.AppendLine("where ACRS_YMD = '''''+ @mysql_date + ''''' ")
        sql2.AppendLine("and del_yn = ''''N'''' ")
        sql2.AppendLine("and use_yn = ''''Y'''' ")
        sql2.AppendLine("GROUP BY LINE_CODE, plant_code ")
        sql2.AppendLine(") current_PROD ")
        sql2.AppendLine("full join ")
        sql2.AppendLine("(--TEMP ")
        sql2.AppendLine("select ")
        sql2.AppendLine("plant_code ")
        sql2.AppendLine(",line_code ")
        sql2.AppendLine(",sum(set_qty) as qty ")
        sql2.AppendLine("from TBP_PM_PACK_ACRS ")
        sql2.AppendLine("where ACRS_YMD = '''''+ @mysql_date + ''''' ")
        sql2.AppendLine("AND OUT_STATUS_CODE = 40 AND STRG_ID <> ''''FL01'''' ")
        sql2.AppendLine("and del_yn = ''''N'''' ")
        sql2.AppendLine("and use_yn = ''''Y'''' ")
        sql2.AppendLine("GROUP BY line_code, plant_code ")
        sql2.AppendLine(") current_TEMP ")
        sql2.AppendLine("ON current_PROD.PLANT_CODE = current_TEMP.PLANT_CODE and current_PROD.LINE_CODE = current_TEMP.LINE_CODE ")
        sql2.AppendLine("FULL JOIN ")
        sql2.AppendLine("( ")
        sql2.AppendLine("--OUT ")
        sql2.AppendLine("select ")
        sql2.AppendLine("plant_code ")
        sql2.AppendLine(",LINE_CODE ")
        sql2.AppendLine(",sum (set_qty) as qty ")
        sql2.AppendLine("from TBP_PM_PACK_ACRS ")
        sql2.AppendLine("where ACRS_YMD = '''''+ @mysql_date + ''''' ")
        sql2.AppendLine("AND OUT_STATUS_CODE = 50 ")
        sql2.AppendLine("and del_yn = ''''N'''' ")
        sql2.AppendLine("and use_yn = ''''Y'''' ")
        sql2.AppendLine("GROUP BY LINE_CODE, plant_code ")
        sql2.AppendLine(") current_OUT ")
        sql2.AppendLine("ON (nvl(current_PROD.PLANT_CODE,current_TEMP.PLANT_CODE) = current_OUT.PLANT_CODE and NVL(current_PROD.LINE_CODE, current_TEMP.LINE_CODE ) = current_OUT.LINE_CODE) ")
        sql2.AppendLine(") ")
        sql2.AppendLine("'' ")
        sql2.AppendLine(")' ")
        sql2.AppendLine(" ")
        sql2.AppendLine("insert into @FactoryStock exec(@tsql) ")
        sql2.AppendLine(" ")
        sql2.AppendLine("SELECT ")
        sql2.AppendLine("catv.dbo.TB_COM_MAIN_LINE_H.LINE AS Line ")
        sql2.AppendLine(", [PLAN] as [Plan] ")
        sql2.AppendLine(", [Target] as [Target] ")
        sql2.AppendLine(", PROD as Result ")
        sql2.AppendLine(", cast(cast(Prod as numeric) / cast([plan] as numeric) * 100 as decimal) as Rate ")
        sql2.AppendLine(",case when [Plan] is null then null ")
        sql2.AppendLine(" else QC_OK ")
        sql2.AppendLine("end as OQC ")
        sql2.AppendLine(", cast(cast(qc_ok as numeric) / cast([plan] as numeric) * 100 as decimal) as [OCQ Rate] ")
        sql2.AppendLine(",case when [Plan] is null then null ")
        sql2.AppendLine("else FactoryStock.current_Out ")
        sql2.AppendLine("end as [Out] ")
        sql2.AppendLine(",cast(cast(FactoryStock.current_Out as numeric) / cast([plan] as numeric) * 100 as decimal) as [Out Rate] ")
        sql2.AppendLine(" ")
        sql2.AppendLine("FROM catv.dbo.TB_COM_MAIN_LINE_H ")
        sql2.AppendLine("left join [UserModules].[user].[CATV_LINE_LIST] on catv.dbo.TB_COM_MAIN_LINE_H.LINE Collate Cyrillic_General_CI_AS = [UserModules].[user].[CATV_LINE_LIST].LINE ")
        sql2.AppendLine("left join @FactoryStock FactoryStock ")
        sql2.AppendLine("on [UserModules].[user].[CATV_LINE_LIST].LINE_CODE Collate Cyrillic_General_CI_AI = FactoryStock.line ")
        sql2.AppendLine("WHERE catv.dbo.TB_COM_MAIN_LINE_H.LINE <> 'TOTAL' and catv.dbo.TB_COM_MAIN_LINE_H.line <> 'W' ")
        sql2.AppendLine("and cast(updated as date) = cast (@graph_date as date) and updated = (select max(updated) from catv.dbo.TB_COM_MAIN_LINE_H where cast(updated as date) = cast(@graph_date as date)) ")
        sql2.AppendLine(" ")
        sql2.AppendLine("UNION ALL ")
        sql2.AppendLine("SELECT ")
        sql2.AppendLine("'Total VD' as [Line] ")
        sql2.AppendLine(", SUM(CAST([PLAN] as NUMERIC)) as [Plan] ")
        sql2.AppendLine(", SUM(CAST([Target] as NUMERIC)) as [Target] ")
        sql2.AppendLine(", SUM(CAST( PROD as NUMERIC)) as Result ")
        sql2.AppendLine(", cast(SUM(CAST( PROD as NUMERIC)) / SUM(CAST([PLAN] as NUMERIC)) * 100 as decimal) as Production_Rate ")
        sql2.AppendLine(", SUM(CAST(QC_OK as NUMERIC)) as Pass ")
        sql2.AppendLine(", cast(SUM(CAST(QC_OK as NUMERIC)) / SUM(CAST([PLAN] as NUMERIC)) * 100 as decimal) as OQC_Rate ")
        sql2.AppendLine(",sum(cast(FactoryStock.current_Out as numeric)) as [Out] ")
        sql2.AppendLine(",cast(sum(cast(FactoryStock.current_Out as numeric)) / SUM(CAST([PLAN] as NUMERIC)) * 100 as decimal) as Out_Rate ")
        sql2.AppendLine(" ")
        sql2.AppendLine("FROM catv.dbo.TB_COM_MAIN_LINE_H ")
        sql2.AppendLine("left join [UserModules].[user].[CATV_LINE_LIST] on catv.dbo.TB_COM_MAIN_LINE_H.LINE Collate Cyrillic_General_CI_AS = [UserModules].[user].[CATV_LINE_LIST].LINE ")
        sql2.AppendLine("left join @FactoryStock FactoryStock ")
        sql2.AppendLine("on [UserModules].[user].[CATV_LINE_LIST].LINE_CODE Collate Cyrillic_General_CI_AI = FactoryStock.line ")
        sql2.AppendLine("WHERE catv.dbo.TB_COM_MAIN_LINE_H.LINE <> 'TOTAL' and catv.dbo.TB_COM_MAIN_LINE_H.line <> 'W' ")
        sql2.AppendLine("and cast(updated as date) = cast (@graph_date as date) and updated = (select max(updated) from catv.dbo.TB_COM_MAIN_LINE_H where cast(updated as date) = cast(@graph_date as date)) ")
        sql2.AppendLine("order by line")

        SqlDataSource_summary_table.SelectCommand = sql2.ToString
        SqlDataSource_summary_table.Select(DataSourceSelectArguments.Empty)
        'SqlDataSource_summary_table.SelectCommand = 

        'Распихиваем данные по GridView
        Summary_table.DataSource = SqlDataSource_summary_table
        Summary_table.DataBind()

    End Sub

    Private Sub Summary_table_PreRender(sender As Object, e As EventArgs) Handles Summary_table.PreRender
        Dim lastrow As Integer = Summary_table.Rows.Count
        Dim footer As GridViewRow = Summary_table.FooterRow

        'Summary_table.Rows(lastrow - 1).Cells(0).ID = "cellid"
        'Summary_table.Rows(lastrow - 1).Cells(0).ClientIDMode = ClientIDMode.Static

        Summary_table.HeaderRow.CssClass = "headerID"
        Summary_table.Rows(lastrow - 1).CssClass = "cellid"

        Summary_table.Rows(lastrow - 1).Style.Add("background", "#333333")
        Summary_table.Rows(lastrow - 1).Style.Add("color", "white")
        Summary_table.Rows(lastrow - 1).Style.Add("font-weight", "bold")

    End Sub

    Private Sub Summary_table_RowDataBound(sender As Object, e As GridViewRowEventArgs) Handles Summary_table.RowDataBound

        If e.Row.RowType = DataControlRowType.DataRow Then

            'Разукрашивание цифирек
            e.Row.Cells(1).Style.Add("color", "LimeGreen")
            e.Row.Cells(2).Style.Add("color", "#0099ff")
            e.Row.Cells(3).Style.Add("color", "#ccffff")
            e.Row.Cells(5).Style.Add("color", "LimeGreen")
            e.Row.Cells(7).Style.Add("color", "#cc0099")


            If e.Row.Cells(0).Text = line Then
                e.Row.RowState = DataControlRowState.Selected
            End If
            e.Row.Cells(0).Text = "<a href=""?date=" + graph_date.Value + "&line=" + e.Row.Cells(0).Text + """>" + e.Row.Cells(0).Text + "</a>"
            If e.Row.Cells(4).Text <> "&nbsp;" Then
                e.Row.Cells(4).Text = e.Row.Cells(4).Text + "%"
            End If
            If e.Row.Cells(6).Text <> "&nbsp;" Then
                e.Row.Cells(6).Text = e.Row.Cells(6).Text + "%"
            End If
            If e.Row.Cells(8).Text <> "&nbsp;" Then
                e.Row.Cells(8).Text = e.Row.Cells(8).Text + "%"
            End If
        ElseIf e.Row.RowType = DataControlRowType.Header Then
            e.Row.Cells(5).Text = " <a href=""http://serkweb.serk.lan:8080/modulemaster/qc/oqcpendinglist.aspx"">" + e.Row.Cells(5).Text + "</a>"
        End If
    End Sub
End Class