ModuleMaster/injection/injection_demands_purchasing.aspx.vb - main - ivan/ModMas

ModuleMaster/injection/injection_demands_purchasing.aspx.vb
640 строк31 KB
i.krivokon
Initial commit
13 мар 2026, 13:49
13 мар 2026, 13:4900d9fb6
Imports System.Data
Imports System.IO

Partial Class capa
    Inherits System.Web.UI.Page
    Private collection As New Dictionary(Of Integer, String)
    Private sqlf As New sharedClasses.dbFunctions.SqlFunctions


    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 capa_Load(sender As Object, e As EventArgs) Handles Me.Load
        Dim sql As New StringBuilder
        sql.Clear()

        sql.AppendLine("declare @bom as table (Assy char(11), parent char(11), child char(11), part_name nvarchar(max), [description] nvarchar(max), unit varchar(10), qty decimal(20,2), [level] int, purchaser varchar(max)) ")
        sql.AppendLine("declare @plan as table (assy char(11), assy_id int, req varchar(10), [week] int) ")
        sql.AppendLine("declare @plan_correct as table (assy char(11), assy_id int, req decimal(20,2), [week] int) ")
        sql.AppendLine(" ")
        sql.AppendLine("declare @bomspecification as table (parent_id int, child_id int, unit varchar(10), qty decimal(20,2), child_pn char(11), child_descr varchar(max), child_purchaser varchar(max)) ")
        sql.AppendLine("declare @result as table (part_name char(11), [description] nvarchar(max), purchaser varchar(max), req decimal(20,2), short decimal(20,2), stock decimal(20,2), unit varchar(10), [week] int) ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("declare @assy as char(11) ")
        sql.AppendLine("declare @component as char(11) ")
        sql.AppendLine(" ")
        sql.AppendLine("declare @assy_cursor_fetch_status as integer ")
        sql.AppendLine("declare @week_cursor_fetch_status as integer ")
        sql.AppendLine("declare @component_cursor_fetch_status as integer ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("declare @assy_correct as char(11) ")
        sql.AppendLine("declare @assy_id_correct as int ")
        sql.AppendLine("declare @req_correct as varchar(10) ")
        sql.AppendLine("declare @week_correct as int ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("declare @assy_result char(11) ")
        sql.AppendLine("declare @assy_id_result int ")
        sql.AppendLine("declare @req_correct_result decimal(20,2) ")
        sql.AppendLine("declare @week_result int ")
        sql.AppendLine("declare @part_name_result char(11) ")
        sql.AppendLine("declare @description_result varchar(max) ")
        sql.AppendLine("declare @qty_result decimal(20,2) ")
        sql.AppendLine("declare @req_result decimal(20,2) ")
        sql.AppendLine("declare @unit_result varchar(10) ")
        sql.AppendLine("declare @short_result decimal(20,2) ")
        sql.AppendLine("declare @purchaser_result varchar(max) ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("declare @tmp as decimal (20,2) ")
        sql.AppendLine("declare @tmp_result as decimal (20,2) ")
        sql.AppendLine("declare @stock as decimal (20,2) ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("insert into @bomspecification ")
        sql.AppendLine("(parent_id, child_id, unit, qty, child_pn, child_descr, child_purchaser) ")
        sql.AppendLine("select parent_id, child_id, t1.unit, cast(qty as decimal(20,2)) as qty, secpn, descr_, PURCHASER ")
        sql.AppendLine("from openquery (rkis, 'select parent_id, child_id, samex.bomspecification.unit, qty, secpn, bom_description ")
        sql.AppendLine("from samex.bomspecification ")
        sql.AppendLine("left join samex.bompartlist ")
        sql.AppendLine("on samex.bomspecification.child_id = samex.bompartlist.id ")
        sql.AppendLine("where secpn is not null') t1 ")
        sql.AppendLine("left join [UserModules].[user].[MATERIAL_MASTER_124] t2 ")
        sql.AppendLine("on t1.secpn collate Cyrillic_General_CI_AI = t2.material collate Cyrillic_General_CI_AI and t2.plant = 'P901' ")
        sql.AppendLine("where t2.[descr_] not like '%COVER-REAR%' and t2.[descr_] not like '%RESIN%'")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("--Формирование плана суммарно по каждой сборке. Одна строка - одна неделя. Соответственно на одну сборку может быть более одной строки! ")
        sql.AppendLine("insert into @plan ")
        sql.AppendLine("(assy, assy_id, req, [week]) ")
        sql.AppendLine("select ")
        sql.AppendLine("assy ")
        sql.AppendLine(", assy_id ")
        sql.AppendLine(", sum (req) as req ")
        sql.AppendLine(", [week] ")
        sql.AppendLine("from ")
        sql.AppendLine("( ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("	select ")
        sql.AppendLine("	secpn as assy ")
        sql.AppendLine("	,bompartlist_id as assy_id ")
        sql.AppendLine("	,sum (cast(w as numeric)) as req ")
        sql.AppendLine("	,datepart(iso_week, SOP_DATE) as [week] ")
        sql.AppendLine("	 from [UserModules].[user].[injection_planning_demands] as i ")
        sql.AppendLine("	 left join (select * from openquery (rkis, ")
        sql.AppendLine("	' ")
        sql.AppendLine("	select distinct ")
        sql.AppendLine("	model ")
        sql.AppendLine("	,secpn ")
        sql.AppendLine("	,bompartlist_id ")
        sql.AppendLine("	,qty ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("	from samex.bommodels ")
        sql.AppendLine("	left join samex.bompartlist ")
        sql.AppendLine("	on samex.bommodels.bompartlist_id = samex.bompartlist.id ")
        sql.AppendLine("	where samex.bompartlist.process_id = 2 ")
        sql.AppendLine("	and eol = ''N'' ")
        sql.AppendLine("	' ")
        sql.AppendLine("	))as t ")
        sql.AppendLine("	on i.material collate Cyrillic_General_CI_AS = t.model collate Cyrillic_General_CI_AS ")
        sql.AppendLine(" ")
        sql.AppendLine("	where ")
        sql.AppendLine("	W is not null ")
        sql.AppendLine("	and W <> 0 ")
        sql.AppendLine("	and model is not null ")
        sql.AppendLine("	--and secpn = 'BN96-45655E' ")
        sql.AppendLine("	group by material, secpn, bompartlist_id, datepart(iso_week, SOP_DATE) ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("	union all ")
        sql.AppendLine(" ")
        sql.AppendLine("	select ")
        sql.AppendLine("	secpn ")
        sql.AppendLine("	,bompartlist_id ")
        sql.AppendLine("	,sum (cast(w_1 as numeric)) as req ")
        sql.AppendLine("	,datepart(iso_week, SOP_DATE)+1 as [week] ")
        sql.AppendLine("	 from [UserModules].[user].[injection_planning_demands] as i ")
        sql.AppendLine("	 left join (select * from openquery (rkis, ")
        sql.AppendLine("	' ")
        sql.AppendLine("	select distinct ")
        sql.AppendLine("	model ")
        sql.AppendLine("	,secpn ")
        sql.AppendLine("	,bompartlist_id ")
        sql.AppendLine("	,qty ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("	from samex.bommodels ")
        sql.AppendLine("	left join samex.bompartlist ")
        sql.AppendLine("	on samex.bommodels.bompartlist_id = samex.bompartlist.id ")
        sql.AppendLine("	where samex.bompartlist.process_id = 2 ")
        sql.AppendLine("	and eol = ''N'' ")
        sql.AppendLine("	' ")
        sql.AppendLine("	))as t ")
        sql.AppendLine("	on i.material collate Cyrillic_General_CI_AS = t.model collate Cyrillic_General_CI_AS ")
        sql.AppendLine(" ")
        sql.AppendLine("	where ")
        sql.AppendLine("	W_1 is not null ")
        sql.AppendLine("	and W_1 <> 0 ")
        sql.AppendLine("	and model is not null ")
        sql.AppendLine("	--and secpn = 'BN96-45655E' ")
        sql.AppendLine("	group by material, secpn, bompartlist_id, datepart(iso_week, SOP_DATE)+1 ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("	union all ")
        sql.AppendLine(" ")
        sql.AppendLine("	select ")
        sql.AppendLine("	secpn ")
        sql.AppendLine("	,bompartlist_id ")
        sql.AppendLine("	,sum (cast(w_2 as numeric)) as req ")
        sql.AppendLine("	,datepart(iso_week, SOP_DATE)+2 as [week] ")
        sql.AppendLine("	 from [UserModules].[user].[injection_planning_demands] as i ")
        sql.AppendLine("	 left join (select * from openquery (rkis, ")
        sql.AppendLine("	' ")
        sql.AppendLine("	select distinct ")
        sql.AppendLine("	model ")
        sql.AppendLine("	,secpn ")
        sql.AppendLine("	,bompartlist_id ")
        sql.AppendLine("	,qty ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("	from samex.bommodels ")
        sql.AppendLine("	left join samex.bompartlist ")
        sql.AppendLine("	on samex.bommodels.bompartlist_id = samex.bompartlist.id ")
        sql.AppendLine("	where samex.bompartlist.process_id = 2 ")
        sql.AppendLine("	and eol = ''N'' ")
        sql.AppendLine("	' ")
        sql.AppendLine("	))as t ")
        sql.AppendLine("	on i.material collate Cyrillic_General_CI_AS = t.model collate Cyrillic_General_CI_AS ")
        sql.AppendLine(" ")
        sql.AppendLine("	where ")
        sql.AppendLine("	W_2 is not null ")
        sql.AppendLine("	and W_2 <> 0 ")
        sql.AppendLine("	and model is not null ")
        sql.AppendLine("	--and secpn = 'BN96-45655E' ")
        sql.AppendLine("	group by material, secpn, bompartlist_id, datepart(iso_week, SOP_DATE)+2 ")
        sql.AppendLine(") t ")
        sql.AppendLine(" ")
        sql.AppendLine("group by assy, assy_id, [week] ")
        sql.AppendLine("order by assy asc, [week] asc ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("----Формирование BOM для каждой сборки ")
        sql.AppendLine("DECLARE assy_CURSOR CURSOR FOR ")
        sql.AppendLine("( ")
        sql.AppendLine("select distinct assy_id ")
        sql.AppendLine("from @plan ")
        sql.AppendLine(") ")
        sql.AppendLine("OPEN assy_CURSOR ")
        sql.AppendLine("	FETCH NEXT FROM assy_CURSOR INTO @assy ")
        sql.AppendLine("	WHILE @@FETCH_STATUS = 0 ")
        sql.AppendLine("	BEGIN ")
        sql.AppendLine("	with recursion (Assy, parent, child, part_name, [description], unit, qty, [level], purchaser) ")
        sql.AppendLine("			as ")
        sql.AppendLine("			( ")
        sql.AppendLine("			SELECT DISTINCT ")
        sql.AppendLine("			d.PARENT_ID Assy ")
        sql.AppendLine("			,d.PARENT_ID ")
        sql.AppendLine("			,d.CHILD_ID ")
        sql.AppendLine("			,d.child_pn ")
        sql.AppendLine("			,d.child_descr ")
        sql.AppendLine("			,d.unit ")
        sql.AppendLine("			,d.qty ")
        sql.AppendLine("			,1 as ""level"" ")
        sql.AppendLine("			,d.child_purchaser ")
        sql.AppendLine("			FROM @bomspecification d ")
        sql.AppendLine("			WHERE d.PARENT_ID = @assy ")
        sql.AppendLine(" ")
        sql.AppendLine("			union ALL ")
        sql.AppendLine(" ")
        sql.AppendLine("			select ")
        sql.AppendLine("			e.Assy ")
        sql.AppendLine("			,d.PARENT_ID ")
        sql.AppendLine("			,d.CHILD_ID ")
        sql.AppendLine("			,d.child_pn ")
        sql.AppendLine("			,d.child_descr ")
        sql.AppendLine("			,d.unit ")
        sql.AppendLine("			,d.qty ")
        sql.AppendLine("			,e.""level"" + 1 as ""level"" ")
        sql.AppendLine("			,d.child_purchaser ")
        sql.AppendLine("			FROM @bomspecification d ")
        sql.AppendLine("			inner join recursion e ")
        sql.AppendLine("			on d.PARENT_ID = e.CHILD ")
        sql.AppendLine("			WHERE d.PARENT_ID = e.CHILD ")
        sql.AppendLine(" ")
        sql.AppendLine("			) ")
        sql.AppendLine("			insert into @bom ")
        sql.AppendLine("			(Assy, parent, child, part_name, [description], unit, qty, [level], purchaser) ")
        sql.AppendLine("			select * ")
        sql.AppendLine("			from recursion ")
        sql.AppendLine("			where [level] <6 ")
        sql.AppendLine("			order by [level], parent, child ")
        sql.AppendLine(" ")
        sql.AppendLine("		FETCH NEXT FROM assy_CURSOR INTO @assy ")
        sql.AppendLine("	END ")
        sql.AppendLine(" ")
        sql.AppendLine("CLOSE assy_CURSOR ")
        sql.AppendLine("DEALLOCATE assy_CURSOR ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("-------------- ")
        sql.AppendLine("-------------- ")
        sql.AppendLine("-------------- ")
        sql.AppendLine("--Корректировка потребностей по сборкам с учётом текущего стока ")
        sql.AppendLine("DECLARE assy_CURSOR CURSOR FOR ")
        sql.AppendLine("( ")
        sql.AppendLine("	select distinct assy from @plan ")
        sql.AppendLine(") ")
        sql.AppendLine(" ")
        sql.AppendLine("OPEN assy_CURSOR ")
        sql.AppendLine("	FETCH NEXT FROM assy_CURSOR INTO @assy ")
        sql.AppendLine("	set @assy_cursor_fetch_status = @@FETCH_STATUS ")
        sql.AppendLine("	WHILE @assy_cursor_fetch_status = 0 ")
        sql.AppendLine("	BEGIN ")
        sql.AppendLine(" ")
        sql.AppendLine("		--Определяем курсор для потребности по каждой неделе для текущей сборки ")
        sql.AppendLine("		declare week_cursor cursor for ")
        sql.AppendLine("		( ")
        sql.AppendLine("			select * from @plan ")
        sql.AppendLine("			where assy = @assy ")
        sql.AppendLine("		) ")
        sql.AppendLine(" ")
        sql.AppendLine("				--Вложенный луп для корректировки потребностей с учётом доступного стока ")
        sql.AppendLine("				--Извлечение доступного стока для текущей сборки ")
        sql.AppendLine("				set @stock = (select isnull(stock,0) as stock from openquery (rkis, 'select i.secpn pn, sum(qty) stock ")
        sql.AppendLine("					from injectionassyinventory i, bompartlist b where i.secpn=b.secpn and b.eol=''N'' and b.process_id =2 and (i.source<>''TP_W'' or i.destination<>''TP_W'') group by pn ")
        sql.AppendLine("					') ")
        sql.AppendLine("					where pn = @assy) ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("				OPEN week_cursor ")
        sql.AppendLine("				FETCH NEXT FROM week_cursor INTO @assy_correct, @assy_id_correct, @req_correct, @week_correct ")
        sql.AppendLine("				set @week_cursor_fetch_status = @@FETCH_STATUS ")
        sql.AppendLine("				 ")
        sql.AppendLine(" ")
        sql.AppendLine("				WHILE @week_cursor_fetch_status = 0 ")
        sql.AppendLine("				BEGIN ")
        sql.AppendLine(" ")
        sql.AppendLine("					set @tmp = @req_correct ")
        sql.AppendLine("					set @req_correct = @req_correct - @stock ")
        sql.AppendLine("					set @stock = @stock - @tmp ")
        sql.AppendLine("					if @stock <= 0 set @stock = 0 ")
        sql.AppendLine("					insert into @plan_correct (assy, assy_id, req, [week]) values (@assy_correct, @assy_id_correct, @req_correct, @week_correct) ")
        sql.AppendLine("				FETCH NEXT FROM week_cursor INTO @assy_correct, @assy_id_correct, @req_correct, @week_correct ")
        sql.AppendLine("				set @week_cursor_fetch_status = @@FETCH_STATUS ")
        sql.AppendLine(" ")
        sql.AppendLine("				END ")
        sql.AppendLine("				CLOSE week_cursor ")
        sql.AppendLine("				DEALLOCATE week_cursor ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("		FETCH NEXT FROM assy_CURSOR INTO @assy ")
        sql.AppendLine("		set @assy_cursor_fetch_status = @@FETCH_STATUS ")
        sql.AppendLine("	END ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("	CLOSE assy_CURSOR ")
        sql.AppendLine("	DEALLOCATE assy_CURSOR ")
        sql.AppendLine("	 ")
        sql.AppendLine(" ")
        sql.AppendLine("--Расчёт потребностей компанентов ")
        sql.AppendLine("--Курсор по компанентам ")
        sql.AppendLine("DECLARE component_CURSOR CURSOR FOR ")
        sql.AppendLine("( ")
        sql.AppendLine("	select distinct part_name from @bom ")
        sql.AppendLine(") ")
        sql.AppendLine(" ")
        sql.AppendLine("OPEN component_CURSOR ")
        sql.AppendLine("	FETCH NEXT FROM component_CURSOR INTO @component ")
        sql.AppendLine("	set @component_cursor_fetch_status = @@FETCH_STATUS ")
        sql.AppendLine("	WHILE @component_cursor_fetch_status = 0 ")
        sql.AppendLine("	BEGIN ")
        sql.AppendLine(" ")
        sql.AppendLine("		--Определяем курсор для потребностей по всем сборкам, где присутствует компанент. Сначала сборки с большим количеством, затем с маленьким в пределах одной и той же недели. ")
        sql.AppendLine("		declare week_cursor cursor for ")
        sql.AppendLine("		 ")
        sql.AppendLine("		select ")
        sql.AppendLine("		b.part_name ")
        sql.AppendLine("		,b.[description] ")
        sql.AppendLine("		,b.purchaser ")
        sql.AppendLine("		,sum(p.req * qty) as req ")
        sql.AppendLine("		,b.unit ")
        sql.AppendLine("		,p.[week] ")
        sql.AppendLine("		from @plan_correct p ")
        sql.AppendLine("		left join @bom b ")
        sql.AppendLine("		on p.assy_id = b.Assy ")
        sql.AppendLine("		where b.part_name = @component ")
        sql.AppendLine("		and cast(p.req as decimal(20,2)) > 0 ")
        sql.AppendLine("		group by b.part_name, b.[description], b.purchaser, p.[week], b.unit ")
        sql.AppendLine("		order by b.part_name, p.[week] asc ")
        sql.AppendLine("			 ")
        sql.AppendLine("		 ")
        sql.AppendLine(" ")
        sql.AppendLine("				--Вложенный луп для корректировки потребностей с учётом доступного стока ")
        sql.AppendLine("				--Извлечение доступного стока для текущего компанента ")
        sql.AppendLine("				set @stock = (select ")
        sql.AppendLine("							isnull(sum(cast(qty as decimal(20,2))),0) as stock ")
        sql.AppendLine("							from [UserModules].[user].[INJECTION_STOCK] ")
        sql.AppendLine("							where material <> '' and material is not null and material = @component) ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("				OPEN week_cursor ")
        sql.AppendLine("				FETCH NEXT FROM week_cursor INTO @part_name_result, @description_result, @purchaser_result, @req_result, @unit_result, @week_result ")
        sql.AppendLine("				set @week_cursor_fetch_status = @@FETCH_STATUS ")
        sql.AppendLine("				 ")
        sql.AppendLine(" ")
        sql.AppendLine("				WHILE @week_cursor_fetch_status = 0 ")
        sql.AppendLine("				BEGIN ")
        sql.AppendLine("					set @short_result = @req_result - @stock ")
        sql.AppendLine("					insert into @result (part_name, [description], purchaser, req, short, stock, unit, [week]) ")
        sql.AppendLine("					values (@part_name_result, @description_result,@purchaser_result, @req_result, @short_result, @stock, @unit_result, @week_result) ")
        sql.AppendLine("					set @stock = @stock - @req_result ")
        sql.AppendLine("					if @stock <= 0 set @stock = 0				 ")
        sql.AppendLine(" ")
        sql.AppendLine("					 ")
        sql.AppendLine("				 ")
        sql.AppendLine("				FETCH NEXT FROM week_cursor INTO @part_name_result, @description_result, @purchaser_result, @req_result, @unit_result, @week_result ")
        sql.AppendLine("				set @week_cursor_fetch_status = @@FETCH_STATUS ")
        sql.AppendLine(" ")
        sql.AppendLine("				END ")
        sql.AppendLine("				CLOSE week_cursor ")
        sql.AppendLine("				DEALLOCATE week_cursor ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("		FETCH NEXT FROM component_CURSOR INTO @component ")
        sql.AppendLine("		set @component_cursor_fetch_status = @@FETCH_STATUS ")
        sql.AppendLine("	END ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("	CLOSE component_CURSOR ")
        sql.AppendLine("	DEALLOCATE component_CURSOR ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine(" select ")
        sql.AppendLine(" part_name as Material ")
        sql.AppendLine(" ,[Description] ")
        sql.AppendLine(" ,Purchaser ")
        sql.AppendLine(" ,[Week] ")
        sql.AppendLine(" ,CAST(CONVERT(varchar, CAST(req AS Money), 1) AS varchar) as Requirement ")
        sql.AppendLine(" ,CAST(CONVERT(varchar, CAST(stock AS Money), 1) AS varchar) as Stock ")
        sql.AppendLine(" ,CAST(CONVERT(varchar, CAST(Short AS Money), 1) AS varchar) as Short ")
        sql.AppendLine(" ,Unit ")
        sql.AppendLine(" ,Comment ")
        sql.AppendLine("	from @result r ")
        sql.AppendLine(" ")
        sql.AppendLine(" ")
        sql.AppendLine("	left join [UserModules].[user].[INJECTION_DEMANDS_COMMENTS_P901] c ")
        sql.AppendLine("	on r.part_name collate Cyrillic_General_CI_AI= c.material collate Cyrillic_General_CI_AI and c.[current_week] = r.[week] ")
        sql.AppendLine(" ")
        sql.AppendLine("	where short > 0 ")
        sql.AppendLine("	order by material, [week] ")
        sql.AppendLine(" ")
        sql.AppendLine("	 ")


        queue.SelectCommand = sql.ToString

        report.DataSource = queue

        Try
            report.DataBind()
        Catch ex As Exception
            Dim debug As Integer = 16
        End Try

        prepare_data()

    End Sub
    Sub prepare_data()


        Try
            Dim dr As Data.DataRow = sqlf.GetRow("select distinct top 1 stock_date as stock_date from [UserModules].[user].[Injection_stock]")
            If Not IsNothing(dr) Then
                'Title = dr("MODULE_NAME")
                Dim stock_date As String = dr("stock_date")
                stock_date_time.Text = "Injection P901 stock last updated on: "
                date_time.Text = stock_date
                HiddenField_stock.Value = stock_date
                date_time.Visible = True
            End If


            dr = sqlf.GetRow("select distinct top 1 sop_date as sop_date from [UserModules].[user].[injection_planning_demands]")
            If Not IsNothing(dr) Then
                'Title = dr("MODULE_NAME")
                Dim sop_date As String = dr("sop_date")
                SOP_date_time.Text = "SOP last updated on: "
                date_time_2.Text = sop_date
                HiddenField_sop.Value = sop_date
                date_time_2.Visible = True
            End If



            Dim bt_stat As Data.DataRow = sqlf.GetRow("select top 1 * from UserModules.[user].WA_JOB_LIST WHERE (id=71 or id=70) and (RUN_IMMEDIATELY = 'Y' or NOW_RUNNING = 'Y') ")
            If Not IsNothing(bt_stat) Then

                If bt_stat("RUN_IMMEDIATELY") = "Y" Or bt_stat("NOW_RUNNING") = "Y" Then
                    'Stock.Text = "The stock data is being extracted "
                    spinner.Visible = True
                    Stock.Enabled = False
                    SOP.Enabled = False
                    stock_date_time.Text = "Stock or SOP data is being extracted at the moment. The page will be reloaded automatically when it's done"
                    SOP_date_time.Visible = False
                    date_time.Visible = False
                    date_time_2.Visible = False
                End If

            End If

        Catch ex As Exception
            Dim debug As Integer = 16
        End Try
    End Sub
    Private Sub report_RowDataBound(sender As Object, e As GridViewRowEventArgs) Handles report.RowDataBound
        Dim i As Integer = 0
        Dim material As String
        Dim week As String
        If e.Row.RowType <> DataControlRowType.Header Then
            material = ""
            week = ""
            For Each cell As TableCell In e.Row.Cells
                If i = 0 Then
                    material = cell.Text
                End If
                If i = 3 Then
                    week = cell.Text
                End If
                If i = 8 Then
                    cell.Attributes.Add("class", "remark")
                    cell.Attributes.Add("material", material)
                    cell.Attributes.Add("week", week)
                End If
                i = i + 1
            Next
        End If

    End Sub

    Private Sub report_RowCommand(sender As Object, e As GridViewCommandEventArgs) Handles report.RowCommand

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

    End Sub

    Private Sub report_PreRender(sender As Object, e As EventArgs) Handles report.PreRender
        'report.Columns(7).Visible = False
        'report.Columns(6).Visible = False
    End Sub
    Protected Sub stock_Click(sender As Object, e As EventArgs) Handles Stock.Click
        Dim sqlf As New sharedClasses.dbFunctions.SqlFunctions
        sqlf.NonQuery("Update UserModules.[user].WA_JOB_LIST Set RUN_IMMEDIATELY='Y' WHERE id=71 ")
        prepare_data()
    End Sub
    Protected Sub SOP_Click(sender As Object, e As EventArgs) Handles SOP.Click
        Dim sqlf As New sharedClasses.dbFunctions.SqlFunctions
        sqlf.NonQuery("Update UserModules.[user].WA_JOB_LIST Set RUN_IMMEDIATELY='Y' WHERE id=70 ")
        prepare_data()
    End Sub
End Class