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

ModuleMaster/lcm/forecast_defect_recalculated.aspx.vb
1 144 строки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) ")
        sql.AppendLine(" ")
        If panel.Value <> "" Then
            sql.AppendLine(" --ЗАГЛУШКА ")
            sql.AppendLine(" set @TMP ='" + panel.Value + "' ")
            sql.AppendLine(" --КОНЕЦ ")
        End If
        sql.AppendLine(" ")
        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(" 	material as matnr ")
        sql.AppendLine(" 	,name ")
        sql.AppendLine(" 	,(select top 1 [plan] from [UserModules].[user].[PANELS_SUPPLY]) as [plan] ")
        sql.AppendLine(" 	,'STOCK' ")
        sql.AppendLine(" 	,sum(cast(qty as numeric)) as qty ")
        sql.AppendLine(" ")
        sql.AppendLine(" 	from [UserModules].[user].[SU_INFO_TMP] ")
        sql.AppendLine(" 	 ")
        sql.AppendLine(" 	left join [usermodules].[user].[Forecast_Vendor_Mapping] ")
        sql.AppendLine(" 	on vendor_code = code ")
        sql.AppendLine(" ")
        sql.AppendLine(" 	where material like 'BN95%' and mrp = 'R24' and st_loc = 'RC01' ")
        sql.AppendLine(" 	 ")
        sql.AppendLine(" 	group by material, vendor_code, name ")
        sql.AppendLine(" ) as stock ")
        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(" ,Require.material ")
        sql.AppendLine(" ,Require.qty ")
        sql.AppendLine(" ,sum(cast(stock.qty 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(" ")
        sql.AppendLine(" ")
        If panel.Value <> "" Then
            sql.AppendLine(" --ЗАГЛУШКА ")
            sql.AppendLine(" where [Panel P/N] = @tmp ")
            sql.AppendLine(" --КОНЕЦ ")
        End If
        sql.AppendLine(" ")
        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(" [UserModules].[user].[SU_INFO_TMP] as stock ")
        sql.AppendLine(" on Require.material = Stock.material ")
        sql.AppendLine(" and stock.ST_LOC = 'R01' and stock.plant = 'P901' ")
        sql.AppendLine(" ")
        sql.AppendLine(" group by ")
        sql.AppendLine(" [Panel P/N] ")
        sql.AppendLine(" ,Vendor_name ")
        sql.AppendLine(" ,Require.material ")
        sql.AppendLine(" ,Require.qty ")
        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%' ")
        sql.AppendLine(" ")
        If panel.Value <> "" Then
            sql.AppendLine(" 	--ЗАГЛУШКА ")
            sql.AppendLine(" 	and item = @TMP ")
            sql.AppendLine(" 	-- КОНЕЦ ")
        End If
        sql.AppendLine(" ")
        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(" ")
        sql.AppendLine(" select ")
        sql.AppendLine(" datepart(YEAR,LAST_UPDATE) as [year] ")
        sql.AppendLine(" ,datepart(ISO_WEEK,LAST_UPDATE) as [week] ")
        sql.AppendLine(" ,VENDOR_NAME ")
        sql.AppendLine(" ,SUBSTRING(SN,5,4)+'-'+SUBSTRING(SN,9,6) as [Panel P/N] ")
        sql.AppendLine(" ,case when RP_COMPONENTS is not null ")
        sql.AppendLine(" then ")
        sql.AppendLine(" 	RP_COMPONENTS	 ")
        sql.AppendLine(" else ")
        sql.AppendLine(" 	COMPONENTS ")
        sql.AppendLine(" end AS [RP_COMPONENTS	] ")
        sql.AppendLine(" ")
        sql.AppendLine(" ,case when RP_COMPONENTS is not null ")
        sql.AppendLine(" then ")
        sql.AppendLine(" CAST('<XMLRoot><RowData>' + REPLACE(replace(replace(replace(rp_components,'""',''),'}',''),'{',''),',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) ")
        sql.AppendLine(" else ")
        sql.AppendLine(" CAST('<XMLRoot><RowData>' + REPLACE(replace(replace(replace(COMPONENTS,'""',''),']',''),'[',''),',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) ")
        sql.AppendLine(" end 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 = 7) ")
        sql.AppendLine(" or location = (select t_status from [UserModules].[user].[UN_STATUS] where id = 8) ")
        sql.AppendLine(" or location = (select t_status from [UserModules].[user].[UN_STATUS] where id = 10) ")
        sql.AppendLine(" ) ")
        sql.AppendLine(" and ")
        sql.AppendLine(" ( ")
        sql.AppendLine(" RP_COMPONENTS is not null ")
        sql.AppendLine(" or COMPONENTS <> '[]' ")
        sql.AppendLine(" ) ")
        sql.AppendLine(" ")
        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(" --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("		 ,cast (t2.[SP_used] as numeric) / cast(t1.[Defected Panels] as numeric) 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' ")
        sql.AppendLine(" ")
        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] as [Recomended order including order limit and additional qty] ")
        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] ")
        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.qty 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 [UserModules].[user].[SU_INFO_TMP] as Stock ")
        sql.AppendLine(" on isnull(f_d.child_item, f_s.sp) collate Cyrillic_General_CI_AS = Stock.MATERIAL collate Cyrillic_General_CI_AS ")
        sql.AppendLine(" and stock.ST_LOC = 'RC01' and stock.PLANT = '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.qty 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 [UserModules].[user].[SU_INFO_TMP] as Stock ")
        sql.AppendLine(" on isnull(f_d.child_item, f_s.sp) collate Cyrillic_General_CI_AS = Stock.material collate Cyrillic_General_CI_AS ")
        sql.AppendLine(" and stock.ST_LOC = 'RC01' and stock.PLANT = '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].[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].[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