using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public class ClrPerf
{
	//4-year limit on scheduling
	public const int MAXPRODUCTS = 101;
	public const int MAXWEEKS = 210;
	public const int MAXNAME = 256;

	public ClrPerf()
	{
			
	}

	[SqlProcedure(Name="clr_ProdSched_MemLow_DataHigh_SO")]
	public static int ProdSched_MemLow_DataHigh_SO(SqlDateTime startDate, int numWeeks, int K)
	{
		//Arrays are 1-based to make one-to-one comparisons with the algorithm easier
		SqlDateTime[] week = new SqlDateTime[MAXWEEKS];
		int[] quantity;
		int[][] Cij;
		int[] Fk;
		int[] minK = new int[MAXWEEKS];
		int product_id, current_product, product_count = 0;
		int startPeriod;
		int[] h = new int[MAXPRODUCTS];
		SqlChars[] productNames = new SqlChars[MAXPRODUCTS];
		bool moreProducts = true;
		int optimal_j;
		int period;
		int sum;
		SqlPipe pipe = SqlContext.Pipe;
		SqlDataRecord record;
		object[] values = new object[3];
		SqlMetaData[] metadata = new SqlMetaData[3];

		//Initialize algorithm arrays
		Cij = new int[MAXWEEKS][];
		for( int l=0;l<MAXWEEKS;l++)
			Cij[l] = new int[MAXWEEKS];
		Fk = new int[MAXWEEKS];

		//Look up K and h for all products
		SqlConnection conn = new SqlConnection("context connection = true");
		conn.Open();
		SqlCommand cmd = conn.CreateCommand();
		cmd.CommandText = @"SELECT * from t_Products ORDER BY PID";
		SqlDataReader reader = cmd.ExecuteReader();
		while(reader.Read() && product_count < MAXPRODUCTS)
		{
			productNames[product_count] = reader.GetSqlChars(1);
			h[product_count] = reader.GetInt32(2); //holding cost
			product_count++;
		}
		reader.Close();
		product_count = 0;

		//Get the list of product ids;
		cmd = conn.CreateCommand();
		cmd.CommandText = @"select * from t_SalesForecast ORDER BY PID, WeekDate";
		reader = cmd.ExecuteReader();
		reader.Read();
			
		//Set up the record for returning results
		metadata[0] = new SqlMetaData( "Product", SqlDbType.NVarChar,MAXNAME );
		metadata[1] = new SqlMetaData( "Period", SqlDbType.DateTime );
		metadata[2] = new SqlMetaData( "Quantity", SqlDbType.Int );
		record = new SqlDataRecord( metadata );


		while( moreProducts )
		{
			product_id = current_product = reader.GetInt32(0);
			int index = 1;
			quantity = new int[MAXWEEKS];
			while( current_product == product_id )
			{
				week[index] = reader.GetSqlDateTime(1);
				quantity[index] = reader.GetInt32(2);
				index++;

				moreProducts = reader.Read();
				if( !moreProducts )
					break;
				current_product = reader.GetInt32(0);
			}

			//Determine the ordinal start week
			startPeriod = 1;
			/*Going to hack startPeriod to always be one week
			I'm doing this to allow us to control the amount of memory
			allocated for Cij
			while( week[startPeriod] != startDate )
				startPeriod++;
			*/

			//For each product id calculate Cij
			for( int i = startPeriod; i < (startPeriod + numWeeks); i++ )
			{
				for( int j = i+1; j <= (startPeriod + numWeeks+1); j++ )
				{
					Cij[i][j] = GetCij(quantity,i,j,K/*[product_count]*/,h[product_count]);
				}
			}

			//Calculate Fk
			for( int k = startPeriod + numWeeks + 1; k >= startPeriod; k--)
			{
				minK[k] = GetFk_SO(k,startPeriod + numWeeks,Cij,Fk);
			}

			//Send the results
			record.SetSqlChars(0,productNames[product_count]);
			pipe.SendResultsStart(record);

			for( int k = startPeriod; k < startPeriod + numWeeks; )
			{
				period = k;
				optimal_j = minK[k];
				sum = 0;
				while( k < optimal_j )
				{
					sum = sum + quantity[k++];
				}
				values[1] = week[period];
				record.SetValue(1,values[1]);
				values[2] = sum;
				record.SetValue(2,values[2]);
				pipe.SendResultsRow(record);
			}
			pipe.SendResultsEnd();
			product_count++;
		}
		reader.Close();
		return 0;
	}


	private static int GetCij(int[] quantities, int i, int j, int K, int h)
	{
		if( j == i+1 )
			return K;
		else
			return (j-1-i) * h * quantities[j-1] + GetCij(quantities, i, j-1,K,h);

	}

	private static int GetFk_SO(int k,int n,int[][] Cij, int[] Fk)
	{
		int j,min;
		j = k+1;
		min = j;

		if ( k == n+1 )
		{
			Fk[k] = 0;
			return j;
		}

		Fk[k] = Cij[k][j] + Fk[j];
		for(; k <= n ;k++)
		{
			j = k + 1;
			while( j <= n+1 )
			{
				if( Cij[k][j] + Fk[j] < Fk[k] )
				{
					min = j;
					Fk[k] = Cij[k][j] + Fk[j];
				}

				j++;
			}
		}
		return min;
	}

}
