sp_configure 'show advanced options', 1
go

reconfigure
go

sp_configure 'clr enabled', 1
go

reconfigure
go

sp_configure 'xp_cmdshell', 1
go

reconfigure
go

if exists (select name from master..sysdatabases where name = 'ClrPerfDB')
	drop database ClrPerfDB
go

create database ClrPerfDB
go

use ClrPerfDB
go

if object_id ('t_Products') is not null
	drop table t_Products
go

create table t_Products( PID int primary key, Pname nvarchar(256), InventoryCost int, StartupCost int)
go

if object_id ('t_SalesForecast') is not null
	drop table t_SalesForecast
go

create table t_SalesForecast( PID int, WeekDate smalldatetime, DemandQty int)
go

if object_id ('clr_ProdSched_MemLow_DataHigh_SO') is not null
	drop procedure clr_ProdSched_MemLow_DataHigh_SO
go

if exists (select * from sys.assemblies where name = 'ProdSchedInproc')
	drop assembly ProdSchedInproc
go

create assembly ProdSchedInproc from 'c:\ProdSchedInproc.dll'
go

create procedure clr_ProdSched_MemLow_DataHigh_SO(@startDate smalldatetime, @numWeeks int, @K int)
as external name ProdSchedInproc.ClrPerf.ProdSched_MemLow_DataHigh_SO
go

--populate Products
declare @i int
set @i = 1
while @i <= 100
begin
	insert t_Products values(@i,'Junk' + cast(@i as nvarchar),1,75)
	set @i = @i+1
end
go

--populate SalesForecast
declare @i int, @j int, @date smalldatetime, @qty int
set @i = 1
set @qty = 100
while @i <= 100
begin
	set @j = 0
	set @date = '04/01/2004'
	while @j < 205
	begin
		insert t_SalesForecast values( @i, @date, @qty % 50)
		set @j = @j+1
		set @date = DATEADD( Week, 1, @date )
		set @qty = @qty + 13
	end
	set @i = @i+1
end
go

DROP PROCEDURE tsql_ProductionScheduling
go

CREATE PROCEDURE tsql_ProductionScheduling
	@startDate DateTime, @numWeeks int, @pid int
AS

CREATE TABLE #Fk (k int, val int, j int)

CREATE TABLE #Fk_final (k int, val int, j int)

CREATE TABLE #CIJ ( pid int, i int, j int, C int )

CREATE TABLE #Forecast ( Product nvarchar(256), Period DateTime, Quantity int )

DECLARE @i int, @j int, @n int, @K int, @h int
--DECLARE @pid int
DECLARE @lastValue int
--These are hard coded, but need to come as paramters to storedproc
SET @n = @numWeeks
------------------------------------------------------------------
SELECT @K = StartupCost, @h = InventoryCost FROM t_Products WHERE PID = @pid
SET @i = 1
WHILE ( @i < @n+1 )
BEGIN
	SET @j = @i+1
	INSERT #CIJ VALUES (@pid,@i,@j,@K)
	SET @lastValue = @K
	SET @j = @j+1
	WHILE( @j <= @n+1 )
	BEGIN
		INSERT #CIJ SELECT PID, @i, @j, @lastValue + (@h * DemandQty * (@j-1-@i) )
			FROM t_SalesForecast
			WHERE PID=@pid AND WeekDate >= @startDate
			AND WeekDate = DATEADD(Week,@j-2,@startDate)

		SELECT @lastValue = C FROM #CIJ
			WHERE i=@i AND j=@j
	SET @j = @j+1
	END
SET @i = @i+1
END
--SELECT * FROM #CIJ
--Calculate Fk
SET @n = @numWeeks
SET @k = @n+1
INSERT #Fk VALUES( @k, 0, @k+1)
SET @k = @k-1
WHILE( @k >= 1  )
BEGIN
	INSERT #Fk
	SELECT @k, MIN( t.C + f.val ), t.j
	FROM #CIJ t, #Fk f
	WHERE t.i = @k AND t.j > @k AND f.k = t.j
	GROUP BY t.j
SET @k = @k-1
END

INSERT #Fk_final
SELECT Z.k, Z.val, Y.j
FROM
	(SELECT k, MIN(val) AS val FROM #Fk GROUP BY k) Z
	JOIN #Fk Y ON Z.k=Y.k AND Z.val=Y.val
--SELECT * FROM #FK_final

DECLARE @product nvarchar(256)
SELECT @product = pName FROM t_Products WHERE PID = @pid
--SET @startIndex = 1
DECLARE @sum int, @optimal_j int
DECLARE @week DateTime
DECLARE @period int
SET @k = 1
SET @n = @numWeeks
WHILE ( @k <= @n )
BEGIN
	SET @period = @k
	SELECT @optimal_j = j FROM #Fk_final WHERE k=@k
	SET @sum = 0
	WHILE( @k < @optimal_j )
	BEGIN
		SELECT @sum = @sum + DemandQty FROM t_SalesForecast 
			WHERE WeekDate = DATEADD(Week,@k-1,@startDate)
			AND PID = @pid
		SELECT @week = WeekDate FROM t_SalesForecast
			WHERE WeekDate = DATEADD(Week,@period-1,@startDate)
			AND PID = @pid
	SET @k = @k+1
	END
	INSERT #Forecast SELECT @product, @week, @sum
END

SELECT * FROM #Forecast
go
