ModuleMaster/lcm/fair_forecast.aspx.vb - main - ivan/ModMas

ModuleMaster/lcm/fair_forecast.aspx.vb
1 150 строк64 KB
i.krivokon
Initial commit
13 мар 2026, 13:49
13 мар 2026, 13:4900d9fb6
Imports System.Data
Imports System.IO
Imports System.Data.SqlClient
Imports sharedClasses.dbFunctions

Partial Class r
    Inherits System.Web.UI.Page
    Private collection As New Dictionary(Of Integer, String)
    Private sqlf As New SqlFunctions
    Private ExcelDownload As Boolean = False
    Private flag As Boolean = False




    Public Shared Sub ApplyGridViewHeader(ParentId As String, ByRef gv As GridView, ByRef a As Collection)
        'процедура написана таким образом, что в нее всегда должны передаваться индексы с учетом только фактически отображаемых колонок
        'т.е. при вычислении индекса объединяемой колонки (передаваемой в качестве параметра в процедуру) считается,
        'что колонок со свойством Visible = false в таблице как бы нет.
        'индексы (передаваемые в качестве параметров в процедуру) указываются по исходному порядку необъединенных колонок

        Dim AllowSorting As Boolean = gv.AllowSorting
        If gv.Rows.Count > 0 Then
            Dim row = New GridViewRow(-1, -1, DataControlRowType.Header, DataControlRowState.Normal)
            Dim r = gv.HeaderRow
            Dim cell As TableHeaderCell

            Dim i, j As Integer

            Dim invisibleColsCount As Integer = 0
            Dim cnt As Integer = 0

            'i = 1

            For Each rcell As TableCell In r.Cells
                cell = New TableHeaderCell
                If AllowSorting Then
                    Dim lb As New HyperLink
                    Try
                        lb.Style("color") = "White"
                        lb.Text = CType(rcell.Controls(0), LinkButton).Text
                        lb.NavigateUrl = "javascript:__doPostBack('" & ParentId & "$" & gv.ID & "','Sort$" & CType(rcell.Controls(0), LinkButton).CommandArgument & "')"
                        cell.Controls.Add(lb)
                    Catch ex As Exception
                        cell.Text = rcell.Text
                    End Try
                Else
                    cell.Text = rcell.Text
                End If
                cell.Style("text-align") = "center"
                cell.Visible = rcell.Visible

                'If cnt > a(i)(1) And cnt < a(i)(1) + a(i)(2) Then
                ' If Not rcell.Visible Then
                ' invisibleColsCount += 1
                ' End If
                'End If

                cnt += 1

                row.Cells.Add(cell)
                'End If
            Next

            'для нужных ячеек в верхней строке, прописываем тексты объединяющих ячеек и их ColumnSpan
            For i = 1 To a.Count
                row.Cells(a(i)(1)).Text = a(i)(0)
                row.Cells(a(i)(1)).ColumnSpan = a(i)(2) - invisibleColsCount
            Next

            'идем по верхней строке у оставшихся ячеек (не задействованных) в предыдущем цикле ставим им RowSpan = 2
            'одновременно ячееки с таким же индексом в нижней строке помечаем на удаление
            For i = 0 To row.Cells.Count - 1
                If row.Cells(i).Visible Then
                    If row.Cells(i).ColumnSpan < 2 Then
                        row.Cells(i).RowSpan = 2
                        r.Cells(i).ColumnSpan = 100
                    Else
                        'пропускаем ячейки на которые распространяется ColumnSpan
                        i = i + row.Cells(i).ColumnSpan - 1 + invisibleColsCount
                    End If
                End If
            Next

            'удаляем помеченные ячейки в нижней строке
            i = 0
            While i < r.Cells.Count
                If r.Cells(i).ColumnSpan = 100 Then
                    r.Cells.RemoveAt(i)
                Else : i = i + 1
                End If
            End While

            'идем по верхней строке удаляем соответствующее ColumnSpan количество ячеек после ячеек имеющих ColumnSpan > 1 
            i = 0
            While i < row.Cells.Count
                If row.Cells(i).ColumnSpan > 1 Then
                    For j = 1 To row.Cells(i).ColumnSpan - 1 + invisibleColsCount
                        row.Cells.RemoveAt(i + 1)
                    Next
                End If
                i = i + 1
            End While

            CType(gv.Controls(0), Table).Rows.AddAt(0, row)
        End If
    End Sub

    Public Sub CollectHeaderColumn(ByRef gv As GridView, colNumber As Integer, Optional referenceColNumber As Integer = -1)
        Dim curCell As New TableCell With {.Text = "."}
        Dim rowSpan As Integer = 1

        Dim ColNumberToCheckText As Integer
        If referenceColNumber = -1 Then
            ColNumberToCheckText = colNumber
        Else
            ColNumberToCheckText = referenceColNumber
        End If

        Dim curText As String = "."

        For Each dr As GridViewRow In gv.Rows
            If dr.Cells(ColNumberToCheckText).Text <> curText Then 'curCell.Text Then
                If rowSpan > 1 Then
                    curCell.RowSpan = rowSpan
                End If
                curCell = dr.Cells(colNumber)
                curText = dr.Cells(ColNumberToCheckText).Text
                rowSpan = 1
            Else
                dr.Cells(colNumber).Visible = False
                rowSpan += 1
            End If
        Next
        If rowSpan > 1 Then
            curCell.RowSpan = rowSpan
        End If
    End Sub

    Private Sub r_Load(sender As Object, e As EventArgs) Handles Me.Load

        Dim stat_week_from_st, stat_week_end_st, forecast_week_from_st, forecast_week_end_st As String

        If stat_week_from.Value <> "" Then
            stat_week_from_st = stat_week_from.Value.Replace("W", "").Replace("-", "")
        End If

        If stat_week_end.Value <> "" Then
            stat_week_end_st = stat_week_end.Value.Replace("W", "").Replace("-", "")
        End If

        If forecast_week_from.Value <> "" Then
            forecast_week_from_st = forecast_week_from.Value.Replace("W", "").Replace("-", "")
        End If

        If forecast_week_end.Value <> "" Then
            forecast_week_end_st = forecast_week_end.Value.Replace("W", "").Replace("-", "")
        End If





        Dim sql As New StringBuilder
        sql.Clear()
        sql.AppendLine("declare @TMP as nvarchar(max) ")
        If panel.Value <> "" Then
            sql.AppendLine("--ЗАГЛУШКА ")
            sql.AppendLine("set @TMP ='" + panel.Value + "' ")
            sql.AppendLine("--КОНЕЦ ")
        End If
        sql.AppendLine("--Описание временных таблиц для размещения результатов прогноза по умолчанию и по статистике ")
        sql.AppendLine("--Если временная таблица существует - удаляем ")
        sql.AppendLine("--Таблица для сохранения результатов расчёта потребностей по умолчанию ")
        sql.AppendLine("if object_id('tempdb..#temptable_forecast_by_default', 'U') is not null drop table #temptable_forecast_by_default ")
        sql.AppendLine("--Таблица для сохранения результатов расчёта потребностей по статистике ")
        sql.AppendLine("if object_id('tempdb..#temptable_forecast_by_statistic', 'U') is not null drop table #temptable_forecast_by_statistic ")
        sql.AppendLine("--Таблица для сохранения результатов расчёта потребностей по панелям уже в ремонте ")
        sql.AppendLine("if object_id('tempdb..#temptable_sp_current', 'U') is not null drop table #temptable_sp_current ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("--Таблица для объединённой информации плана поставок и текущего стока ")
        sql.AppendLine("if object_id('tempdb..#temptable_plan_stock', 'U') is not null drop table #temptable_plan_stock ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("--Блок описания переменных для расчёта прогноза по статистике ")
        sql.AppendLine("--Если временная таблица существует - удаляем ")
        sql.AppendLine("if object_id('tempdb..#temptable_rma', 'U') is not null drop table #temptable_rma ")
        sql.AppendLine("declare @period_start as nvarchar(6) ")
        sql.AppendLine("declare @period_end as nvarchar(6) ")
        sql.AppendLine("--ЗАГЛУШКА ")
        sql.AppendLine("set @period_start = '" + stat_week_from_st + "' ")
        sql.AppendLine("set @period_end	 = '" + stat_week_end_st + "' ")
        sql.AppendLine("--КОНЕЦ ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("declare @table as table ([year] int, [week] int, vendor_name nvarchar(20), [Panel P/N] char(11), components nvarchar(max), [xml] xml, defects nvarchar(max)) ")
        sql.AppendLine("declare ")
        sql.AppendLine("@Split char(1), ")
        sql.AppendLine("@X xml ")
        sql.AppendLine("set @Split = ',' ")
        sql.AppendLine("---------------- ")
        sql.AppendLine("--Конец Блока описания переменных для расчёта прогноза по статистике ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("--Объединённая таблица плана поставки и текущего стока ")
        sql.AppendLine(" ")
        sql.AppendLine("select * ")
        sql.AppendLine("into #temptable_plan_stock ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine(" ")
        sql.AppendLine("select ")
        sql.AppendLine("item ")
        sql.AppendLine(",supplier ")
        sql.AppendLine(",[plan] ")
        sql.AppendLine(",[week] ")
        sql.AppendLine(",qty ")
        sql.AppendLine("from [UserModules].[user].[PANELS_SUPPLY] as supply ")
        sql.AppendLine("where item like 'BN95%' ")
        sql.AppendLine(" ")
        sql.AppendLine("union all ")
        sql.AppendLine(" ")
        sql.AppendLine("select ")
        sql.AppendLine("	matnr ")
        sql.AppendLine("	,name ")
        sql.AppendLine("	,(select top 1 [plan] from [UserModules].[user].[PANELS_SUPPLY]) as [plan] ")
        sql.AppendLine("	,'STOCK' ")
        sql.AppendLine("	,sum(cast(menge as numeric)) as qty ")
        sql.AppendLine(" ")
        sql.AppendLine("	from [UserModules].[user].[Panels_info] ")
        sql.AppendLine("	 ")
        sql.AppendLine("	left join [usermodules].[user].[Forecast_Vendor_Mapping] ")
        sql.AppendLine("	on vendor_code = code ")
        sql.AppendLine(" ")
        sql.AppendLine("	where matnr like 'BN95%' ")
        sql.AppendLine("	 ")
        sql.AppendLine("	group by matnr, vendor_code, name ")
        sql.AppendLine(") as stock ")
        sql.AppendLine(" ")
        sql.AppendLine("--Конец объединённой таблицы плана поставки и текущего стока ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("--Расчёт потребностей по запчастям для панелей, которые на данный момент в ремонте ")
        sql.AppendLine("select ")
        sql.AppendLine("[Panel P/N] ")
        sql.AppendLine(",Vendor_name ")
        sql.AppendLine(",material ")
        sql.AppendLine(",qty ")
        sql.AppendLine(",sum(cast(stock.VERME as decimal(20,2))) as verme ")
        sql.AppendLine("into #temptable_sp_current ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine("select ")
        sql.AppendLine("[Panel P/N] ")
        sql.AppendLine(",Vendor_name ")
        sql.AppendLine(",material ")
        sql.AppendLine(",sum(qty) as qty ")
        sql.AppendLine(" ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine(" ")
        sql.AppendLine("	select ")
        sql.AppendLine("	SN, ")
        sql.AppendLine("	[Panel P/N] ")
        sql.AppendLine("	,Vendor_Name ")
        sql.AppendLine("	,left(m.n.value('.','varchar(8000)') ,11) as material ")
        sql.AppendLine("	, ")
        sql.AppendLine("	case ")
        sql.AppendLine("		when SUBSTRING(m.n.value('.','varchar(8000)'), 13, 100) <> '' then SUBSTRING(m.n.value('.','varchar(8000)'), 13, 100) ")
        sql.AppendLine("			else 1 ")
        sql.AppendLine("	end as qty ")
        sql.AppendLine(" ")
        sql.AppendLine("	from ")
        sql.AppendLine(" ")
        sql.AppendLine("	( ")
        sql.AppendLine("		SELECT ")
        sql.AppendLine("		t1.sn, ")
        sql.AppendLine("		SUBSTRING(t1.sn, 5,4)+'-'+SUBSTRING(t1.sn, 9,6) as [Panel P/N] ")
        sql.AppendLine("		,vend.VENDOR_NAME ")
        sql.AppendLine("		,case ")
        sql.AppendLine("			when rp_components is null ")
        sql.AppendLine("			then CAST('<XMLRoot><RowData>' + REPLACE(replace(replace(replace(COMPONENTS,'""',''),']',''),'[',''),',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) ")
        sql.AppendLine("			else CAST('<XMLRoot><RowData>' + REPLACE(replace(replace(replace(rp_components,'""',''),'}',''),'{',''),',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) ")
        sql.AppendLine("		end as xml ")
        sql.AppendLine("		FROM UserModules.[user].ON_VERIFICATION_LIST T1 ")
        sql.AppendLine("		left join [UserModules].[user].[IMPORT_VENDORS] vend ")
        sql.AppendLine("		on left(t1.sn, 2) = vend.VENDOR_CODE ")
        sql.AppendLine("		WHERE ")
        sql.AppendLine("		(ISNULL(SN_STATUS, 0)= 4 OR ISNULL(RP_STATUS, 0) = 14) ")
        sql.AppendLine("		AND ISNULL(IS_CLOSED, 0) != '1' ")
        sql.AppendLine("		AND ISNULL(RP_STATUS, 0) != '11' ")
        sql.AppendLine("and RECEIVED_FROM <> 'Service'")
        sql.AppendLine("and RECEIVED_FROM <> 'CLAIM'")
        sql.AppendLine("		--and sn like '%BN9502024A%' ")
        sql.AppendLine("		--and sn='CN09BN9502634DS005J5N0925' ")
        sql.AppendLine("	) as PreTab ")
        sql.AppendLine("	cross APPLY [xml].nodes('/XMLRoot/RowData')m(n) ")
        sql.AppendLine("	--where left(m.n.value('.','varchar(8000)') ,11) = 'BN96-30453A' ")
        sql.AppendLine(") PreFinalTab ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        If panel.Value <> "" Then
            sql.AppendLine("--ЗАГЛУШКА ")
            sql.AppendLine("where [Panel P/N] = @tmp ")
            sql.AppendLine("--КОНЕЦ ")
        End If
        sql.AppendLine("group by ")
        sql.AppendLine("[Panel P/N], VENDOR_NAME, material ")
        sql.AppendLine(") Require ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("left join ")
        sql.AppendLine(" WMSData.[dbo].[TB_sap_z_rfc_stbin_status] as stock ")
        sql.AppendLine(" on material = Stock.MATNR ")
        sql.AppendLine(" and stock.LGNUM = 'R01' and stock.WERKS = 'P901' ")
        sql.AppendLine(" ")
        sql.AppendLine(" group by ")
        sql.AppendLine(" [Panel P/N] ")
        sql.AppendLine(",Vendor_name ")
        sql.AppendLine(",material ")
        sql.AppendLine(",qty ")
        sql.AppendLine("----------------------------------------- ")
        sql.AppendLine("----------------------------------------- ")
        sql.AppendLine("----------------------------------------- ")
        sql.AppendLine("----------------------------------------- ")
        sql.AppendLine("----------------------------------------- ")
        sql.AppendLine("--Конец расчёта текущих потребностей ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("--Расчёт прогноза по умолчанию ")
        sql.AppendLine(" ")
        sql.AppendLine("Select ")
        sql.AppendLine("ITEM ")
        sql.AppendLine(", SUPPLIER ")
        sql.AppendLine(", [PLAN] ")
        sql.AppendLine(", [WEEK] ")
        sql.AppendLine(", isnull([Type], 'Not registered') as [type] ")
        sql.AppendLine(",qty ")
        sql.AppendLine(",child_item ")
        sql.AppendLine(",part_name ")
        sql.AppendLine(",qty / 100 * cast(Minimum_set.default_coef as decimal(20,2)) as Defaule_forecast ")
        sql.AppendLine(" ")
        sql.AppendLine("into #temptable_forecast_by_default ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("	select --distinct item ")
        sql.AppendLine("	Panels_supply.item ")
        sql.AppendLine("	,Panels_supply.supplier ")
        sql.AppendLine("	,Panels_supply.[Plan] ")
        sql.AppendLine("	,Panels_supply.[week] ")
        sql.AppendLine("	,isnull(case ")
        sql.AppendLine("		when Panel_description.material like '%Q%' then Panel_description.PTC_BEZ +'-Q' ")
        sql.AppendLine("		else Panel_description.PTC_BEZ ")
        sql.AppendLine("		end, 'PTC') as [Type] ")
        sql.AppendLine("	,sum (cast(Panels_supply.qty as int)) as qty ")
        sql.AppendLine("	,bom.child_item ")
        sql.AppendLine("	,bom.part_name ")
        sql.AppendLine("	--, sum (cast(Panels_supply.qty as int) * cast(Minimum_set.DEFAULT_COEF as decimal) / 100) as default_forecast ")
        sql.AppendLine("	--ТАБЛИЦА по плану отгрузок ")
        sql.AppendLine("	--заменено на объединённую таблицу плана поставок и текущего стока ")
        sql.AppendLine("	--from [UserModules].[user].[Panels_supply] as Panels_supply ")
        sql.AppendLine("	from #temptable_plan_stock as Panels_supply ")
        sql.AppendLine(" ")
        sql.AppendLine("	--КЛЕИМ таблицу с описанием паниле, из которой понятно к какой группе относится панел (PTC, Bezel-less, Bezel-less-Q) ")
        sql.AppendLine("	left join [UserModules].[user].[Panel_description] as Panel_description ")
        sql.AppendLine("	on Panels_supply.ITEM = Panel_description.COMPONENT ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("	--КЛЕИМ посчитанный БОМ ")
        sql.AppendLine("	left join [UserModules].[user].[lcm_bom] as BOM ")
        sql.AppendLine("	on Panels_supply.ITEM collate Cyrillic_General_CI_AS = BOM.panel collate Cyrillic_General_CI_AS ")
        sql.AppendLine("	and bom.part_name collate Cyrillic_General_CI_AS in (select sp_pattern from [UserModules].[user].[MINIMUM_SP_SET] where PANEL_TYPE collate Cyrillic_General_CI_AS = isnull(case when Panel_description.material like '%Q%' then Panel_description.PTC_BEZ +'-Q' else Panel_description.PTC_BEZ end, 'PTC') collate Cyrillic_General_CI_AS) ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("	where item like 'BN95%' ")
        If panel.Value <> "" Then
            sql.AppendLine("	--ЗАГЛУШКА ")
            sql.AppendLine("	and item = @TMP ")
            sql.AppendLine("	-- КОНЕЦ ")
        End If
        sql.AppendLine("	--and part_name collate Cyrillic_General_CI_AS in (select sp_pattern from [UserModules].[user].[MINIMUM_SP_SET] where PANEL_TYPE collate Cyrillic_General_CI_AS = case when Panel_description.material like '%Q%' then Panel_description.PTC_BEZ +'-Q' else Panel_description.PTC_BEZ end collate Cyrillic_General_CI_AS) ")
        sql.AppendLine(" ")
        sql.AppendLine("	group by item, supplier, [plan], [week], Panel_description.material, Panel_description.PTC_BEZ, bom.child_item, bom.part_name ")
        sql.AppendLine(") as Default_Forecast ")
        sql.AppendLine(" ")
        sql.AppendLine("left join [UserModules].[user].[MINIMUM_SP_SET] as Minimum_set ")
        sql.AppendLine("on Default_Forecast.part_name collate Cyrillic_General_CI_AS = Minimum_set.SP_PATTERN collate Cyrillic_General_CI_AS ")
        sql.AppendLine("and Default_Forecast.[Type] collate Cyrillic_General_CI_AS = Minimum_set.PANEL_TYPE collate Cyrillic_General_CI_AS ")
        sql.AppendLine("------------------------------------------------------------------------------- ")
        sql.AppendLine("------------------------------------------------------------------------------- ")
        sql.AppendLine("------------------------------------------------------------------------------- ")
        sql.AppendLine("------------------------------------------------------------------------------- ")
        sql.AppendLine("------------------------------------------------------------------------------- ")
        sql.AppendLine("--Конец расчёта прогноза по умолчанию ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("--Расчёт прогноза по статистике ")
        sql.AppendLine("--Fill table with defects ")
        sql.AppendLine("insert into @table ")
        sql.AppendLine("--Select defects from main line and before main line repaired the same day ")
        sql.AppendLine("select ")
        sql.AppendLine("datepart(YEAR,date_add) as [year] ")
        sql.AppendLine(",datepart(ISO_WEEK,date_add) as [week] ")
        sql.AppendLine(",VENDOR_NAME ")
        sql.AppendLine(",SUBSTRING(SN,5,4)+'-'+SUBSTRING(SN,9,6) as [Panel P/N] ")
        sql.AppendLine(",RP_COMPONENTS ")
        sql.AppendLine(" ")
        sql.AppendLine(",CAST('<XMLRoot><RowData>' + REPLACE(replace(replace(replace(rp_components,'""',''),'}',''),'{',''),',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS [xml] ")
        sql.AppendLine(",RMA_DEFECTS ")
        sql.AppendLine("from [UserModules].[user].[ON_VERIFICATION_LIST] ")
        sql.AppendLine("left join [UserModules].[user].[IMPORT_VENDORS] ")
        sql.AppendLine("on left(sn, 2) = [UserModules].[user].[IMPORT_VENDORS].[VENDOR_CODE] ")
        sql.AppendLine("where ")
        sql.AppendLine("( ")
        sql.AppendLine("location = (select t_status from [UserModules].[user].[UN_STATUS] where id = 6) ")
        sql.AppendLine("or location = (select t_status from [UserModules].[user].[UN_STATUS] where id = 8) ")
        sql.AppendLine(") ")
        sql.AppendLine("and SN_STATUS = 3 ")
        sql.AppendLine("and RP_COMPONENTS is not null ")
        sql.AppendLine(" ")
        sql.AppendLine("union all ")
        sql.AppendLine("--Select defects from main line and before main line not repaired the same day. Verification took into account ")
        sql.AppendLine("select ")
        sql.AppendLine("datepart(YEAR,date_add) as [year] ")
        sql.AppendLine(",datepart(ISO_WEEK,date_add) as [week] ")
        sql.AppendLine(",VENDOR_NAME ")
        sql.AppendLine(",SUBSTRING(SN,5,4)+'-'+SUBSTRING(SN,9,6) as [Panel P/N] ")
        sql.AppendLine(",COMPONENTS ")
        sql.AppendLine(" ")
        sql.AppendLine(",CAST('<XMLRoot><RowData>' + REPLACE(replace(replace(replace(COMPONENTS,'""',''),']',''),'[',''),',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS [xml] ")
        sql.AppendLine(",DEFECTS_LIST ")
        sql.AppendLine("from [UserModules].[user].[ON_VERIFICATION_LIST] ")
        sql.AppendLine("left join [UserModules].[user].[IMPORT_VENDORS] ")
        sql.AppendLine("on left(sn, 2) = [UserModules].[user].[IMPORT_VENDORS].[VENDOR_CODE] ")
        sql.AppendLine("where ")
        sql.AppendLine("( ")
        sql.AppendLine("location = (select t_status from [UserModules].[user].[UN_STATUS] where id = 6) ")
        sql.AppendLine("or location = (select t_status from [UserModules].[user].[UN_STATUS] where id = 8) ")
        sql.AppendLine(") ")
        sql.AppendLine("and SN_STATUS = 4 ")
        sql.AppendLine("and COMPONENTS is not null ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("--- Calculate panels consuption, panels defect qyt, spare parts usage coef, PDR ")
        sql.AppendLine("select ")
        sql.AppendLine(" ")
        sql.AppendLine("panels_consuption.[year] as [Year] ")
        sql.AppendLine(",panels_consuption.[week] as [Week] ")
        sql.AppendLine(",panels_consuption.[Panel P/N] ")
        sql.AppendLine(",panels_consuption.qty as [Panels consumed] ")
        sql.AppendLine(",panels_consuption.VENDOR_NAME as [Vendor Name] ")
        sql.AppendLine(",panels_defect.defect_qty as [Defected Panels] ")
        sql.AppendLine(",cast(panels_defect.defect_qty as decimal(20,2)) / cast(panels_consuption.qty as decimal(20,2)) * 100 as [PDR %] ")
        sql.AppendLine(",SP_consuption.material as SP ")
        sql.AppendLine(",SP_consuption.defect_qty as [SP consuption] ")
        sql.AppendLine(",cast(SP_consuption.defect_qty as decimal(20,2)) / cast(panels_defect.defect_qty as decimal (20,2)) as [Usage coef] ")
        sql.AppendLine(" ")
        sql.AppendLine("into tempdb..#temptable_rma ")
        sql.AppendLine(" ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine("--Panels consuption ")
        sql.AppendLine("	select ")
        sql.AppendLine("	[year] ")
        sql.AppendLine("	,[week] ")
        sql.AppendLine("	,[Panel P/N] ")
        sql.AppendLine("	, count ([Panel P/N]) as qty ")
        sql.AppendLine("	,VENDOR_NAME ")
        sql.AppendLine("	from ")
        sql.AppendLine(" ")
        sql.AppendLine("	( ")
        sql.AppendLine("		select ")
        sql.AppendLine("		SUBSTRING(cast(WORK_YMD as nvarchar),1,4) as [year] ")
        sql.AppendLine("		,datepart(ISO_WEEK, cast(SUBSTRING(cast(WORK_YMD as nvarchar), 1,4)+'-'+ SUBSTRING(cast(WORK_YMD as nvarchar),5,2)+'-'+ SUBSTRING(cast(WORK_YMD as nvarchar),7,2) as date)) as [week] ")
        sql.AppendLine("		,matr_code as [Panel P/N] ")
        sql.AppendLine("		,VENDOR_NAME ")
        sql.AppendLine("		from usermodules.[user].epass_history ")
        sql.AppendLine("		left join [UserModules].[user].[IMPORT_VENDORS] ")
        sql.AppendLine("		on left(ASSY_NO, 2) = [UserModules].[user].[IMPORT_VENDORS].[VENDOR_CODE] ")
        sql.AppendLine("		where MATR_CODE like 'BN95%' ")
        sql.AppendLine("	) as PreTable1 ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("	group by [year], [week], [Panel P/N], VENDOR_NAME ")
        sql.AppendLine("	--order by [year], [week], [Panel P/N], VENDOR_NAME ")
        sql.AppendLine("	) as panels_consuption ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("	left join ")
        sql.AppendLine("	( ")
        sql.AppendLine("	--Panels defect qty ")
        sql.AppendLine("		select ")
        sql.AppendLine("		[year] ")
        sql.AppendLine("		,[week] ")
        sql.AppendLine("		,vendor_name ")
        sql.AppendLine("		,[Panel P/N] ")
        sql.AppendLine("		, count([Panel P/N]) [defect_qty] ")
        sql.AppendLine("		from @table ")
        sql.AppendLine("		group by [year], [week], [Panel P/N], vendor_name ")
        sql.AppendLine("	 ) as panels_defect ")
        sql.AppendLine("	 on panels_consuption.[year] = panels_defect.[year] ")
        sql.AppendLine("	 and panels_consuption.[week] = panels_defect.[week] ")
        sql.AppendLine("	 and panels_consuption.[Panel P/N] collate Cyrillic_General_CI_AS = panels_defect.[Panel P/N] collate Cyrillic_General_CI_AS ")
        sql.AppendLine("	 and panels_consuption.[VENDOR_NAME] collate Cyrillic_General_CI_AS= [panels_defect].[vendor_name] collate Cyrillic_General_CI_AS ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("	 left join ")
        sql.AppendLine("	 ( ")
        sql.AppendLine("	 --Spare parts usage ")
        sql.AppendLine("		 select ")
        sql.AppendLine("		[year] ")
        sql.AppendLine("		,[week] ")
        sql.AppendLine("		,vendor_name ")
        sql.AppendLine("		,[Panel P/N] ")
        sql.AppendLine("		,[material] ")
        sql.AppendLine("		,sum(qty) as defect_qty ")
        sql.AppendLine("		from ")
        sql.AppendLine("		( ")
        sql.AppendLine("			select ")
        sql.AppendLine("			[year] ")
        sql.AppendLine("			,[week] ")
        sql.AppendLine("			,vendor_name ")
        sql.AppendLine("			,[Panel P/N] ")
        sql.AppendLine("			,left(m.n.value('.','varchar(8000)') ,11) as material ")
        sql.AppendLine("			,case ")
        sql.AppendLine("				when SUBSTRING(m.n.value('.','varchar(8000)'), 13, 100) <> '' then SUBSTRING(m.n.value('.','varchar(8000)'), 13, 100) ")
        sql.AppendLine("					else 1 ")
        sql.AppendLine("					end as qty ")
        sql.AppendLine("			, defects ")
        sql.AppendLine("			, components ")
        sql.AppendLine("			, [xml] ")
        sql.AppendLine("			,m.n.value('.','varchar(8000)') as Parsed ")
        sql.AppendLine("			 from @table ")
        sql.AppendLine("			 cross APPLY [xml].nodes('/XMLRoot/RowData')m(n) ")
        sql.AppendLine("		 ) as PreTable1 ")
        sql.AppendLine("		 group by [year], [week], vendor_name, [Panel P/N], material ")
        sql.AppendLine("	 ) as SP_consuption ")
        sql.AppendLine("	 on panels_consuption.[year] = SP_consuption.[year] ")
        sql.AppendLine("	 and panels_consuption.[week] = SP_consuption.[week] ")
        sql.AppendLine("	 and panels_consuption.[Panel P/N] collate Cyrillic_General_CI_AS = SP_consuption.[Panel P/N] collate Cyrillic_General_CI_AS ")
        sql.AppendLine("	 and panels_consuption.[VENDOR_NAME] collate Cyrillic_General_CI_AS= [SP_consuption].[vendor_name] collate Cyrillic_General_CI_AS ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" order by [year], [week], [Panel P/N], panels_consuption.vendor_name ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" --Build forecast ")
        sql.AppendLine(" select ")
        sql.AppendLine(" T1.[Panel P/N] ")
        sql.AppendLine(" ,t1.[Vendor Name] ")
        sql.AppendLine(" ,supply.qty as [Plan_qty] ")
        sql.AppendLine(" ,t1.period_PDR as [PDR %] ")
        sql.AppendLine(" ,t2.SP ")
        sql.AppendLine(" ,t2.period_usage_coef as [Usage coefficient] ")
        sql.AppendLine(" ,supply.[plan] as [Plan] ")
        sql.AppendLine(" ,supply.[week] as [Week] ")
        sql.AppendLine(" ,t2.period_usage_coef * t1.period_PDR  * qty as Forecast ")
        sql.AppendLine(" ,cast(t1.[Panels consumed] as numeric) as [Panels consumed]")
        sql.AppendLine(" ,cast(t1.[Defected Panels] as numeric) as [Defected Panels] ")
        sql.AppendLine(" ,cast (t2.[SP_used] as numeric) as [SP_used] ")
        sql.AppendLine(" ")
        sql.AppendLine(" into #temptable_forecast_by_statistic ")
        sql.AppendLine(" ")
        sql.AppendLine(" from ")
        sql.AppendLine(" ( ")
        sql.AppendLine("select ")
        sql.AppendLine("[Panel P/N] ")
        sql.AppendLine(",[Vendor Name] ")
        sql.AppendLine(",sum ([Panels consumed]) as [Panels consumed] ")
        sql.AppendLine(",sum([Defected Panels]) as [Defected Panels] ")
        sql.AppendLine(",cast(sum([Defected Panels]) as decimal(20,2)) / cast(sum ([Panels consumed]) as decimal(20,2)) as [period_PDR] ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" from ")
        sql.AppendLine(" ( ")
        sql.AppendLine(" select distinct ")
        sql.AppendLine(" [Panel P/N] ")
        sql.AppendLine(" ,[Vendor Name] ")
        sql.AppendLine("-- ,sum ([PDR %]) / count([Panel P/N]) as [period_PDR] ")
        sql.AppendLine(" ")
        sql.AppendLine(",cast(sum ([Panels consumed]) over (partition by [Panel P/N], [Vendor Name], [week]) as decimal(20,2)) / count ([panels consumed]) over (partition by [Panel P/N], [Vendor Name], [week]) as [Panels consumed] ")
        sql.AppendLine(",cast(sum([Defected Panels]) over (partition by [Panel P/N], [Vendor Name], [week]) as decimal(20,2)) / count ([Defected Panels]) over (partition by [Panel P/N], [Vendor Name], [week]) as [Defected Panels] ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" from tempdb..#temptable_rma ")
        sql.AppendLine("where cast([Year] + case len([week]) when 1 then '0' + cast([week] as char(1)) else cast([week] as char(2)) end as numeric) between cast(@period_start as numeric) and cast(@period_end as numeric) ")
        sql.AppendLine("--and [Panel P/N] = 'BN95-01984A' ")
        sql.AppendLine("and [SP] is not null ")
        sql.AppendLine(") Z ")
        sql.AppendLine("group by [Panel P/N], [Vendor Name] ")
        sql.AppendLine(" ) T1 ")
        sql.AppendLine(" ")
        sql.AppendLine(" left join ")
        sql.AppendLine(" ")
        sql.AppendLine("( ")
        sql.AppendLine(" select ")
        sql.AppendLine(" [Panel P/N] ")
        sql.AppendLine(" ,[Vendor Name] ")
        sql.AppendLine(" ,SP ")
        sql.AppendLine(" --,sum ([Usage coef]) / count([Panel P/N]) as [period_usage_coef] ")
        sql.AppendLine(" ,cast(sum([SP consuption]) as decimal(20,2)) as [SP_used] ")
        sql.AppendLine(" ,cast(sum([SP consuption]) as decimal(20,2)) / cast(sum([Defected Panels]) as decimal (20,2)) as [period_usage_coef] ")
        sql.AppendLine(" from tempdb..#temptable_rma ")
        sql.AppendLine(" ")
        sql.AppendLine("where cast([Year] + case len([week]) when 1 then '0' + cast([week] as char(1)) else cast([week] as char(2)) end as numeric) between cast(@period_start as numeric) and cast(@period_end as numeric) ")
        sql.AppendLine("and [SP] is not null ")
        sql.AppendLine("group by [Panel P/N], [Vendor Name], SP ")
        sql.AppendLine(") T2 ")
        sql.AppendLine("on T1.[Panel P/N] = t2.[Panel P/N] ")
        sql.AppendLine("and t1.[Vendor Name] = t2.[Vendor Name] ")
        sql.AppendLine(" ")
        sql.AppendLine("left join #temptable_plan_stock as [supply] ")
        sql.AppendLine(" on T1.[Panel P/N] = item ")
        sql.AppendLine(" and T1.[Vendor Name] = supply.Supplier ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" where ")
        sql.AppendLine(" supply.[plan] is not null ")
        sql.AppendLine(" and supply.[qty] <> 0 ")
        sql.AppendLine(" and sp <> '' ")
        sql.AppendLine(" and sp <> 'null' ")
        If panel.Value <> "" Then
            sql.AppendLine(" --ЗАГЛУШКА ")
            sql.AppendLine(" and T1.[Panel P/N] = @TMP ")
            sql.AppendLine(" --КОНЕЦ ")
        End If
        sql.AppendLine(" ")
        sql.AppendLine("------------------------------------------------------------------------------- ")
        sql.AppendLine("------------------------------------------------------------------------------- ")
        sql.AppendLine("------------------------------------------------------------------------------- ")
        sql.AppendLine("------------------------------------------------------------------------------- ")
        sql.AppendLine("------------------------------------------------------------------------------- ")
        sql.AppendLine("--Конец расчёта прогноза по статистике ")
        sql.AppendLine(" ")
        sql.AppendLine("------------------------------------------------------------------------------- ")
        sql.AppendLine("------------------------------------------------------------------------------- ")
        sql.AppendLine("------------------------------------------------------------------------------- ")
        sql.AppendLine("------------------------------------------------------------------------------- ")
        sql.AppendLine("------------------------------------------------------------------------------- ")
        sql.AppendLine("--Отсеивание заказов, для которых есть достаточно стока. Причём сумма заказа запчастей ПО ВСЕМ ВЕНДОРАМ должна быть меньше текущего стока ")
        'sql.AppendLine("select ")
        'sql.AppendLine("[Panel P/N] ")
        'sql.AppendLine(",Vendor ")
        'sql.AppendLine(",Plan_date ")
        'sql.AppendLine(",[Panel Type] ")
        'sql.AppendLine(",[Dispatch_plan_qty] ")
        'sql.AppendLine(",stock_req ")
        'sql.AppendLine(", [Spare part] ")
        'sql.AppendLine(",[Description] ")
        'sql.AppendLine(",order_limit ")
        'sql.AppendLine(",Forecast ")
        'sql.AppendLine(",Forecast_stock_req ")
        'sql.AppendLine(",[Current requirement] ")
        'sql.AppendLine(",[Forecast + Current requirement] ")
        'sql.AppendLine(",[Recomended order including order limit] ")
        'sql.AppendLine(",[Flag] ")
        'sql.AppendLine(",[On-hand stock] ")
        'sql.AppendLine(",[PDR %] as [PDR]")
        'sql.AppendLine(",[Usage coefficient] ")
        'sql.AppendLine(",[Panels consumed] ")
        'sql.AppendLine(",[Defected Panels] ")
        'sql.AppendLine(",[SP_used] ")
        'sql.AppendLine("from ")
        'sql.AppendLine("( ")
        'sql.AppendLine(" ")
        'sql.AppendLine("select ")
        'sql.AppendLine("[Panel P/N] ")
        'sql.AppendLine(",Vendor ")
        'sql.AppendLine(",Plan_date ")
        'sql.AppendLine(",[Panel Type] ")
        'sql.AppendLine(",[Dispatch_plan_qty] ")
        'sql.AppendLine(",stock_req ")
        'sql.AppendLine(",[Spare part] ")
        'sql.AppendLine(",[Description] ")
        'sql.AppendLine(",order_limit ")
        'sql.AppendLine(",Forecast ")
        'sql.AppendLine(",Forecast_stock_req ")
        'sql.AppendLine(",[Current requirement] ")
        'sql.AppendLine(",[Forecast + Current requirement] ")
        'sql.AppendLine(",[Recomended order including order limit] ")
        'sql.AppendLine(",sum ([Recomended order including order limit]) over (partition by [Spare part]) as tmp_sum ")
        'sql.AppendLine(",[Flag] ")
        'sql.AppendLine(",[On-hand stock] ")
        'sql.AppendLine(",[PDR %] ")
        'sql.AppendLine(",[Usage coefficient] ")
        'sql.AppendLine(",[Panels consumed] ")
        'sql.AppendLine(",[Defected Panels] ")
        'sql.AppendLine(",[SP_used] ")
        'sql.AppendLine(" ")
        'sql.AppendLine("from ")
        'sql.AppendLine("( ")
        'sql.AppendLine(" ")
        sql.AppendLine("-------Сумма результата за период ")
        sql.AppendLine("select ")
        sql.AppendLine("isnull(result.[Panel P/N], sp_curr.[Panel P/N]) as [Panel P/N] ")
        sql.AppendLine(",isnull([Vendor], sp_curr.vendor_name) as Vendor ")
        sql.AppendLine(",Plan_date ")
        sql.AppendLine(",[Panel Type] ")
        sql.AppendLine(",sum([Dispatch_plan_qty]) as [Dispatch_plan_qty] ")
        sql.AppendLine(",isnull(stock_req, 0) as stock_req ")
        sql.AppendLine(",isnull([Spare part], sp_curr.material) as [Spare part] ")
        sql.AppendLine(",isnull(result.[Description], bom.PART_NAME) as [Description] ")
        sql.AppendLine(",order_limit --from [UserModules].[user].[MINIMUM_SP_SET] tt where tt.PANEL_TYPE = [Panel Type] and SP_PATTERN = [description] ")
        sql.AppendLine(",ceiling(sum(Forecast)) as Forecast ")
        sql.AppendLine(",isnull(Forecast_stock_req, 0) as Forecast_stock_req ")
        sql.AppendLine(",sp_curr.qty as [Current requirement] ")
        sql.AppendLine(",isnull(ceiling(sum(Forecast)),0) + isnull(sp_curr.qty,0) as [Forecast + Current requirement] ")
        sql.AppendLine(",case ")
        sql.AppendLine("	when isnull(ceiling(sum(Forecast)),0) + isnull(sp_curr.qty,0) + isnull(Forecast_stock_req, 0) > ( select order_limit from [UserModules].[user].[MINIMUM_SP_SET] tt where tt.PANEL_TYPE = [Panel Type] and SP_PATTERN = isnull(result.[Description], bom.PART_NAME)) ")
        sql.AppendLine("	then (select order_limit from [UserModules].[user].[MINIMUM_SP_SET] tt where tt.PANEL_TYPE = [Panel Type] and SP_PATTERN = isnull(result.[Description], bom.PART_NAME)) ")
        sql.AppendLine("	else isnull(ceiling(sum(Forecast)),0) + isnull(sp_curr.qty,0) + ceiling(isnull(Forecast_stock_req, 0)) ")
        sql.AppendLine("End")
        sql.AppendLine("+")
        sql.AppendLine("isnull((select cast(add_to_order as numeric) from [UserModules].[user].[MINIMUM_SP_SET] tt where tt.PANEL_TYPE = [Panel Type] And SP_PATTERN = isnull(result.[Description], bom.PART_NAME)),0)")
        sql.AppendLine("As [Recomended order including order limit and additional qty] ")
        sql.AppendLine(",[Flag] ")
        sql.AppendLine(",isnull([On-hand stock], sp_curr.verme) as [On-hand stock]")
        sql.AppendLine(",[PDR %] ")
        sql.AppendLine(",[Usage coefficient] ")
        sql.AppendLine(",[Panels consumed] ")
        sql.AppendLine(",[Defected Panels] ")
        sql.AppendLine(",[SP_used] ")
        sql.AppendLine(" ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("------Результат ")
        sql.AppendLine(" ")
        sql.AppendLine("select ")
        sql.AppendLine("isnull(result_without_stock.[Panel P/N], result_with_stock.[Panel P/N]) as [Panel P/N] ")
        sql.AppendLine(",isnull(result_without_stock.[Vendor], result_with_stock.[Vendor]) as [Vendor] ")
        sql.AppendLine(",isnull(result_without_stock.Plan_date, result_with_stock.Plan_date) as Plan_date ")
        sql.AppendLine(",isnull(result_without_stock.[Week], result_with_stock.[Week]) as [Week] ")
        sql.AppendLine(",isnull(result_without_stock.[Panel Type], result_with_stock.[Panel Type]) as [Panel Type] ")
        sql.AppendLine(",[Dispatch_plan_qty] ")
        sql.AppendLine(",stock_req ")
        sql.AppendLine(",isnull(result_without_stock.[Spare part], result_with_stock.[Spare part]) as [Spare part] ")
        sql.AppendLine(",isnull(result_without_stock.[Description], result_with_stock.[Description]) as [Description] ")
        sql.AppendLine(",Forecast ")
        sql.AppendLine(",Forecast_stock_req ")
        sql.AppendLine(",isnull(result_without_stock.[Flag], result_with_stock.[Flag]) as [Flag] ")
        sql.AppendLine(",isnull(result_without_stock.[On-hand stock], result_with_stock.[On-hand stock]) as [On-hand stock] ")
        sql.AppendLine(",isnull(result_without_stock.[PDR %], result_with_stock.[PDR %])as [PDR %] ")
        sql.AppendLine(",isnull(result_without_stock.[Usage coefficient], result_with_stock.[Usage coefficient]) as [Usage coefficient] ")
        sql.AppendLine(",isnull(result_without_stock.[Panels consumed] , result_with_stock.[Panels consumed]) as [Panels consumed] ")
        sql.AppendLine(",isnull(result_without_stock.[Defected Panels], result_with_stock.[Defected Panels]) as [Defected Panels] ")
        sql.AppendLine(",isnull(result_without_stock.[SP_Used], result_with_stock.[SP_Used]) as [SP_Used] ")
        sql.AppendLine(" ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine("select ")
        sql.AppendLine("isnull(f_d.item, f_s.[Panel P/N]) as [Panel P/N] ")
        sql.AppendLine(",isnull(f_d.supplier, f_s.[Vendor Name]) as [Vendor] ")
        sql.AppendLine(",isnull(f_d.[PLAN], f_s.[Plan]) as Plan_date ")
        sql.AppendLine(",isnull(f_d.[WEEK], f_s.[Week]) as [Week] ")
        sql.AppendLine(",isnull(f_d.[Type],case ")
        sql.AppendLine("		when Descr.material like '%Q%' then Descr.PTC_BEZ +'-Q' ")
        sql.AppendLine("		else Descr.PTC_BEZ ")
        sql.AppendLine("		end) as [Panel Type] ")
        sql.AppendLine(",isnull(f_d.qty, f_s.[Plan_qty]) as [Dispatch_plan_qty] ")
        sql.AppendLine(",isnull(f_d.child_item, f_s.sp) as [Spare part] ")
        sql.AppendLine(",isnull(f_d.part_name, bom.part_name) as [Description] ")
        sql.AppendLine(",isnull(cast(f_s.Forecast as decimal(20,2)), cast(f_d.Defaule_forecast as decimal(20,2))) as Forecast ")
        sql.AppendLine(",case ")
        sql.AppendLine("	when f_s.forecast is null then 'By default' ")
        sql.AppendLine("	else 'By statistic' ")
        sql.AppendLine("end as [Flag] ")
        sql.AppendLine(", sum(cast(stock.verme as numeric)) as [On-hand stock] ")
        sql.AppendLine(", [PDR %] ")
        sql.AppendLine(",[Usage coefficient] ")
        sql.AppendLine(",[Panels consumed] ")
        sql.AppendLine(",[Defected Panels] ")
        sql.AppendLine(",SP_Used ")
        sql.AppendLine(" ")
        sql.AppendLine("from #temptable_forecast_by_default AS f_d ")
        sql.AppendLine("full join ")
        sql.AppendLine(" #temptable_forecast_by_statistic as f_s ")
        sql.AppendLine(" on f_d.ITEM = f_s.[Panel P/N] ")
        sql.AppendLine(" and f_d.child_item collate Cyrillic_General_CI_AS = f_s.SP collate Cyrillic_General_CI_AS ")
        sql.AppendLine(" and f_d.[WEEK] = f_s.[Week] ")
        sql.AppendLine(" and f_d.[PLAN] = f_s.[Plan] ")
        sql.AppendLine(" and f_d.qty = f_s.[Plan_qty] ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" left join [UserModules].[user].[lcm_bom] as BOM ")
        sql.AppendLine(" on isnull(f_d.child_item, f_s.sp) = BOM.child_item collate Cyrillic_General_CI_AS ")
        sql.AppendLine(" and isnull(f_d.item, f_s.[Panel P/N]) collate Cyrillic_General_CI_AS = bom.panel collate Cyrillic_General_CI_AS ")
        sql.AppendLine(" ")
        sql.AppendLine(" left join [UserModules].[user].[PANEL_DESCRIPTION] as Descr ")
        sql.AppendLine(" on isnull(f_d.item, f_s.[Panel P/N]) = Descr.COMPONENT ")
        sql.AppendLine(" ")
        sql.AppendLine(" left join WMSData.[dbo].[TB_sap_z_rfc_stbin_status] as Stock ")
        sql.AppendLine(" on isnull(f_d.child_item, f_s.sp) collate Cyrillic_General_CI_AS = Stock.MATNR collate Cyrillic_General_CI_AS ")
        sql.AppendLine(" and stock.LGNUM = 'R01' and stock.WERKS = 'P901' ")
        sql.AppendLine(" ")
        sql.AppendLine(" where ")
        sql.AppendLine("isnull(f_d.[WEEK], f_s.[Week]) >= isnull(f_d.[PLAN], f_s.[Plan]) ")
        sql.AppendLine("and isnull(f_d.[WEEK], f_s.[Week]) >= '" + forecast_week_from_st + "' ")
        sql.AppendLine("and isnull(f_d.[WEEK], f_s.[Week]) <='" + forecast_week_end_st + "' ")
        sql.AppendLine("and isnull(f_d.[WEEK], f_s.[Week]) <> 'STOCK' ")
        sql.AppendLine(" and isnull(f_d.qty, f_s.[Plan_qty]) <> 0 ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" group by ")
        sql.AppendLine(" isnull(f_d.item, f_s.[Panel P/N]) ")
        sql.AppendLine(",isnull(f_d.supplier, f_s.[Vendor Name]) ")
        sql.AppendLine(",isnull(f_d.[PLAN], f_s.[Plan]) ")
        sql.AppendLine(",isnull(f_d.[WEEK], f_s.[Week]) ")
        sql.AppendLine(",isnull(f_d.[Type],case ")
        sql.AppendLine("		when Descr.material like '%Q%' then Descr.PTC_BEZ +'-Q' ")
        sql.AppendLine("		else Descr.PTC_BEZ ")
        sql.AppendLine("		end) ")
        sql.AppendLine(",isnull(f_d.qty, f_s.[Plan_qty]) ")
        sql.AppendLine(",isnull(f_d.child_item, f_s.sp) ")
        sql.AppendLine(",isnull(f_d.part_name, bom.part_name) ")
        sql.AppendLine(",isnull(cast(f_s.Forecast as decimal(20,2)), cast(f_d.Defaule_forecast as decimal(20,2))) ")
        sql.AppendLine(",case ")
        sql.AppendLine("	when f_s.forecast is null then 'By default' ")
        sql.AppendLine("	else 'By statistic' ")
        sql.AppendLine("end ")
        sql.AppendLine(",[PDR %] ")
        sql.AppendLine(",[Usage coefficient] ")
        sql.AppendLine(",[Panels consumed] ")
        sql.AppendLine(",[Defected Panels] ")
        sql.AppendLine(",[SP_used] ")
        sql.AppendLine(") AS result_without_stock ")
        sql.AppendLine(" ")
        sql.AppendLine("full join ")
        sql.AppendLine("( ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("select ")
        sql.AppendLine("isnull(f_d.item, f_s.[Panel P/N]) as [Panel P/N] ")
        sql.AppendLine(",isnull(f_d.supplier, f_s.[Vendor Name]) as [Vendor] ")
        sql.AppendLine(",isnull(f_d.[PLAN], f_s.[Plan]) as Plan_date ")
        sql.AppendLine(",isnull(f_d.[WEEK], f_s.[Week]) as [Week] ")
        sql.AppendLine(",isnull(f_d.[Type],case ")
        sql.AppendLine("		when Descr.material like '%Q%' then Descr.PTC_BEZ +'-Q' ")
        sql.AppendLine("		else Descr.PTC_BEZ ")
        sql.AppendLine("		end) as [Panel Type] ")
        sql.AppendLine(",isnull(f_d.qty, f_s.[Plan_qty]) as [stock_req] ")
        sql.AppendLine(",isnull(f_d.child_item, f_s.sp) as [Spare part] ")
        sql.AppendLine(",isnull(f_d.part_name, bom.part_name) as [Description] ")
        sql.AppendLine(",isnull(cast(f_s.Forecast as decimal(20,2)), cast(f_d.Defaule_forecast as decimal(20,2))) as Forecast_stock_req ")
        sql.AppendLine(",case ")
        sql.AppendLine("	when f_s.forecast is null then 'By default' ")
        sql.AppendLine("	else 'By statistic' ")
        sql.AppendLine("end as [Flag] ")
        sql.AppendLine(", sum(cast(stock.verme as numeric)) as [On-hand stock] ")
        sql.AppendLine(", [PDR %] ")
        sql.AppendLine(",[Usage coefficient] ")
        sql.AppendLine(",[Panels consumed] ")
        sql.AppendLine(",[Defected Panels] ")
        sql.AppendLine(",[SP_used] ")
        sql.AppendLine(" ")
        sql.AppendLine("from #temptable_forecast_by_default AS f_d ")
        sql.AppendLine("full join ")
        sql.AppendLine(" #temptable_forecast_by_statistic as f_s ")
        sql.AppendLine(" on f_d.ITEM = f_s.[Panel P/N] ")
        sql.AppendLine(" and f_d.child_item collate Cyrillic_General_CI_AS = f_s.SP collate Cyrillic_General_CI_AS ")
        sql.AppendLine(" and f_d.[WEEK] = f_s.[Week] ")
        sql.AppendLine(" and f_d.[PLAN] = f_s.[Plan] ")
        sql.AppendLine(" and f_d.qty = f_s.[Plan_qty] ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" left join [UserModules].[user].[lcm_bom] as BOM ")
        sql.AppendLine(" on isnull(f_d.child_item, f_s.sp) = BOM.child_item collate Cyrillic_General_CI_AS ")
        sql.AppendLine(" and isnull(f_d.item, f_s.[Panel P/N]) collate Cyrillic_General_CI_AS = bom.panel collate Cyrillic_General_CI_AS ")
        sql.AppendLine(" ")
        sql.AppendLine(" left join [UserModules].[user].[PANEL_DESCRIPTION] as Descr ")
        sql.AppendLine(" on isnull(f_d.item, f_s.[Panel P/N]) = Descr.COMPONENT ")
        sql.AppendLine(" ")
        sql.AppendLine(" left join WMSData.[dbo].[TB_sap_z_rfc_stbin_status] as Stock ")
        sql.AppendLine(" on isnull(f_d.child_item, f_s.sp) collate Cyrillic_General_CI_AS = Stock.MATNR collate Cyrillic_General_CI_AS ")
        sql.AppendLine(" and stock.LGNUM = 'R01' and stock.WERKS = 'P901' ")
        sql.AppendLine(" ")
        sql.AppendLine(" where ")
        sql.AppendLine("isnull(f_d.[WEEK], f_s.[Week]) = 'STOCK' ")
        sql.AppendLine("and isnull(f_d.qty, f_s.[Plan_qty]) <> 0 ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" group by ")
        sql.AppendLine(" isnull(f_d.item, f_s.[Panel P/N]) ")
        sql.AppendLine(",isnull(f_d.supplier, f_s.[Vendor Name]) ")
        sql.AppendLine(",isnull(f_d.[PLAN], f_s.[Plan]) ")
        sql.AppendLine(",isnull(f_d.[WEEK], f_s.[Week]) ")
        sql.AppendLine(",isnull(f_d.[Type],case ")
        sql.AppendLine("		when Descr.material like '%Q%' then Descr.PTC_BEZ +'-Q' ")
        sql.AppendLine("		else Descr.PTC_BEZ ")
        sql.AppendLine("		end) ")
        sql.AppendLine(",isnull(f_d.qty, f_s.[Plan_qty]) ")
        sql.AppendLine(",isnull(f_d.child_item, f_s.sp) ")
        sql.AppendLine(",isnull(f_d.part_name, bom.part_name) ")
        sql.AppendLine(",isnull(cast(f_s.Forecast as decimal(20,2)), cast(f_d.Defaule_forecast as decimal(20,2))) ")
        sql.AppendLine(",case ")
        sql.AppendLine("	when f_s.forecast is null then 'By default' ")
        sql.AppendLine("	else 'By statistic' ")
        sql.AppendLine("end ")
        sql.AppendLine(",[PDR %] ")
        sql.AppendLine(",[Usage coefficient] ")
        sql.AppendLine(",[Panels consumed] ")
        sql.AppendLine(",[Defected Panels] ")
        sql.AppendLine(",[SP_used] ")
        sql.AppendLine(") AS result_with_stock ")
        sql.AppendLine(" ")
        sql.AppendLine("on result_without_stock.[Panel P/N] = result_with_stock.[Panel P/N] ")
        sql.AppendLine("and result_without_stock.Vendor = result_with_stock.Vendor ")
        sql.AppendLine("and result_without_stock.[Spare part] = result_with_stock.[Spare part] ")
        sql.AppendLine("-----Конец результата ")
        sql.AppendLine(" ")
        sql.AppendLine(") as Result ")
        sql.AppendLine(" ")
        sql.AppendLine("full join #temptable_sp_current as sp_curr ")
        sql.AppendLine("on Result.[Panel P/N] = sp_curr.[Panel P/N] ")
        sql.AppendLine("and Result.Vendor = sp_curr.VENDOR_NAME ")
        sql.AppendLine("and result.[Spare part] collate Cyrillic_General_CI_AS= sp_curr.material collate Cyrillic_General_CI_AS ")
        sql.AppendLine(" ")
        sql.AppendLine("left join [UserModules].[user].[MINIMUM_SP_SET] as [limit] ")
        sql.AppendLine("on [limit].PANEL_TYPE = result.[Panel Type] ")
        sql.AppendLine("and [limit].SP_PATTERN = result.[description] ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("left join [UserModules].[user].[lcm_bom] as BOM ")
        sql.AppendLine("on isnull([Spare part], sp_curr.material) = BOM.child_item collate Cyrillic_General_CI_AS ")
        sql.AppendLine("and isnull(result.[Panel P/N], sp_curr.[Panel P/N]) collate Cyrillic_General_CI_AS = bom.panel collate Cyrillic_General_CI_AS ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("group by isnull(result.[Panel P/N], sp_curr.[Panel P/N]), isnull([Vendor], sp_curr.vendor_name), Plan_date, [Panel Type], isnull([Spare part], sp_curr.material), isnull(result.[Description], bom.PART_NAME), Flag, isnull([On-hand stock], sp_curr.verme), sp_curr.qty, order_limit, [PDR %], [Usage coefficient], stock_req, Forecast_stock_req,[Panels consumed], [Defected Panels] ,[SP_used] ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("-------Конец суммы результата ")
        'sql.AppendLine(") Sum_filter ")
        'sql.AppendLine(" ")
        'sql.AppendLine(")Sum_filetr_1 ")
        'sql.AppendLine("where tmp_sum >isnull([On-hand stock], 0) ")
        'sql.AppendLine("order by [Panel P/N], Vendor ")
        'sql.AppendLine(" ")
        sql.AppendLine("-----Конец отсеивания заказов, для которых есть достаточный сток ")



        queue.SelectCommand = sql.ToString

        report.DataSource = queue



    End Sub

    Private Sub SqlDataSource_Selecting(sender As Object, e As SqlDataSourceSelectingEventArgs) Handles queue.Selecting
        e.Command.CommandTimeout = 3600
    End Sub


    Protected Sub ibExcelDownload_Click(sender As Object, e As ImageClickEventArgs) Handles ibExcelDownload.Click
        'ExcelDownload = True
        ExcelDownloadSub(report, "LCM spare parts forecast")
    End Sub

    Sub ExcelDownloadSub(ByVal gv As GridView, ByVal filename As String)
        Response.Clear()

        'Response.Buffer = True

        Response.AddHeader("content-disposition", "attachment;filename=" & filename & ".xls")
        Response.ContentType = "application/vnd.xls"
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8")
        EnableViewState = False

        Response.Charset = "utf-8"

        Dim myCItrad As System.Globalization.CultureInfo = New System.Globalization.CultureInfo("en-US", True)

        gv.AllowSorting = False
        gv.AllowPaging = False
        'gv.DataBind()

        Dim sw As StringWriter = New StringWriter(myCItrad)
        Dim hw As HtmlTextWriter = New HtmlTextWriter(sw)

        gv.RenderControl(hw)
        Response.Write(sw.ToString)

        Response.End()
    End Sub

    Private Sub r_PreRender(sender As Object, e As EventArgs) Handles Me.PreRender
        Dim history_dt As DataTable = New DataTable()
        Try
            If ExcelDownload Then
                ExcelDownloadSub(report, "LCM spare parts forecast")
            Else
                If stat_week_from.Value <> "" And stat_week_end.Value <> "" And forecast_week_from.Value <> "" And forecast_week_end.Value <> "" Then
                    flag = True
                    report.DataBind()
                    If panel.Value = "" Then
                        history_dt.Columns.Add("Generation_date")
                        history_dt.Columns.Add("Statistic_from")
                        history_dt.Columns.Add("Statistic_to")
                        history_dt.Columns.Add("Forecast_from")
                        history_dt.Columns.Add("Forecast_to")

                        For Each cell As DataControlFieldCell In report.HeaderRow.Cells
                            history_dt.Columns.Add(cell.Text)
                        Next
                        For Each row As GridViewRow In report.Rows
                            Dim newrow As DataRow = history_dt.NewRow
                            newrow.Item("Generation_date") = DateTime.Now.ToString("yyyy-MM-dd")
                            newrow.Item("Statistic_from") = stat_week_from.Value
                            newrow.Item("Statistic_to") = stat_week_end.Value
                            newrow.Item("Forecast_from") = forecast_week_from.Value
                            newrow.Item("Forecast_to") = forecast_week_end.Value

                            For Each cell In report.HeaderRow.Cells
                                newrow.Item(cell.Text) = row.Cells(report.HeaderRow.Cells.GetCellIndex(cell)).Text.Replace("&nbsp;", "")
                            Next
                            history_dt.Rows.Add(newrow)
                        Next

                        sqlf.NonQuery("delete from [UserModules].[user].[FAIR_FORECAST_HISTORY] where GENERATION_DATE  = '" + DateTime.Now.ToString("yyyy-MM-dd") + "' and STATISTIC_FROM = '" + stat_week_from.Value + "' and STATISTIC_TO = '" + stat_week_end.Value + "' and FORECAST_FROM = '" + forecast_week_from.Value + "' and FORECAST_TO = '" + forecast_week_end.Value + "'")

                        Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SqlFunctions.ConString)
                            bulkCopy.DestinationTableName = "[usermodules].[user].[FAIR_FORECAST_HISTORY]"

                            Dim mapID As New _
                            SqlBulkCopyColumnMapping("Generation_date", "GENERATION_DATE")
                            bulkCopy.ColumnMappings.Add(mapID)
                            mapID = New _
                            SqlBulkCopyColumnMapping("Statistic_from", "STATISTIC_FROM")
                            bulkCopy.ColumnMappings.Add(mapID)
                            mapID = New _
                            SqlBulkCopyColumnMapping("Statistic_to", "STATISTIC_TO")
                            bulkCopy.ColumnMappings.Add(mapID)
                            mapID = New _
                            SqlBulkCopyColumnMapping("Forecast_from", "FORECAST_FROM")
                            bulkCopy.ColumnMappings.Add(mapID)
                            mapID = New _
                            SqlBulkCopyColumnMapping("Forecast_to", "FORECAST_TO")
                            bulkCopy.ColumnMappings.Add(mapID)
                            mapID = New _
                            SqlBulkCopyColumnMapping("Panel P/N", "PANEL")
                            bulkCopy.ColumnMappings.Add(mapID)
                            mapID = New _
                            SqlBulkCopyColumnMapping("Vendor", "VENDOR")
                            bulkCopy.ColumnMappings.Add(mapID)
                            mapID = New _
                            SqlBulkCopyColumnMapping("Plan_date", "PLAN_DATE")
                            bulkCopy.ColumnMappings.Add(mapID)
                            mapID = New _
                            SqlBulkCopyColumnMapping("Panel Type", "PANEL_TYPE")
                            bulkCopy.ColumnMappings.Add(mapID)
                            mapID = New _
                            SqlBulkCopyColumnMapping("Dispatch_plan_qty", "DISPATCH_PLAN_QTY")
                            bulkCopy.ColumnMappings.Add(mapID)
                            mapID = New _
                            SqlBulkCopyColumnMapping("Spare part", "SPARE_PART")
                            bulkCopy.ColumnMappings.Add(mapID)
                            mapID = New _
                            SqlBulkCopyColumnMapping("Description", "DESCRIPTION")
                            bulkCopy.ColumnMappings.Add(mapID)
                            mapID = New _
                            SqlBulkCopyColumnMapping("order_limit", "ORDER_LIMIT")
                            bulkCopy.ColumnMappings.Add(mapID)
                            mapID = New _
                            SqlBulkCopyColumnMapping("Forecast", "FORECAST")
                            bulkCopy.ColumnMappings.Add(mapID)
                            mapID = New _
                            SqlBulkCopyColumnMapping("Current requirement", "CURRENT_REQUIREMENT")
                            bulkCopy.ColumnMappings.Add(mapID)
                            mapID = New _
                            SqlBulkCopyColumnMapping("Forecast + Current requirement", "FORECAST___CURRENT_REQUIREMENT")
                            bulkCopy.ColumnMappings.Add(mapID)
                            mapID = New _
                            SqlBulkCopyColumnMapping("Recomended order including order limit", "RECOMENDED")
                            bulkCopy.ColumnMappings.Add(mapID)
                            mapID = New _
                            SqlBulkCopyColumnMapping("Flag", "FLAG")
                            bulkCopy.ColumnMappings.Add(mapID)
                            mapID = New _
                            SqlBulkCopyColumnMapping("On-hand stock", "ON_HAND_STOCK")
                            bulkCopy.ColumnMappings.Add(mapID)
                            mapID = New _
                            SqlBulkCopyColumnMapping("PDR", "PDR")
                            bulkCopy.ColumnMappings.Add(mapID)
                            mapID = New _
                            SqlBulkCopyColumnMapping("Usage coefficient", "USAGE_COEFFICIENT")
                            bulkCopy.ColumnMappings.Add(mapID)
                            mapID = New _
                            SqlBulkCopyColumnMapping("stock_req", "STOCK_REQ")
                            bulkCopy.ColumnMappings.Add(mapID)
                            mapID = New _
                            SqlBulkCopyColumnMapping("Forecast_stock_req", "FORECAST_STOCK_REQ")
                            bulkCopy.ColumnMappings.Add(mapID)
                            mapID = New _
                            SqlBulkCopyColumnMapping("Panels consumed", "PANELS_CONSUMED")
                            bulkCopy.ColumnMappings.Add(mapID)
                            mapID = New _
                            SqlBulkCopyColumnMapping("Defected Panels", "DEFECTED_PANELS")
                            bulkCopy.ColumnMappings.Add(mapID)
                            mapID = New _
                            SqlBulkCopyColumnMapping("SP_used", "SP_USED")
                            bulkCopy.ColumnMappings.Add(mapID)


                            Try
                                ' Write from the source to the destination.
                                bulkCopy.WriteToServer(history_dt)
                            Catch ex As Exception
                                Console.WriteLine(ex.Message)
                            Finally
                                ' Close the SqlDataReader. The SqlBulkCopy
                                ' object is automatically closed at the end
                                ' of the Using block.
                            End Try
                        End Using
                    End If


                End If
                End If
        Catch ex As Exception
        End Try

    End Sub
    Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)

    End Sub
End Class