using System;
using System.Collections;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using AmazonWS.com.amazon.webservices;

	// TVF that returns 6 columns:
	// [Original Release Date] datetime
	// [Title] nvarchar(40)
	// [Amazon List Price] smallmoney
	// [Running Time] smalldatetime
	// [Amazon Used Price] smallmoney
	// [Used Stock] int

	// Amazon Web service SDK: http://www.amazon.com/gp/aws/sdk/002-8042267-1106462?

	// Excellent walkthrough here: http://www.sitepoint.com/article/amazon-web-services-asp-net

	public class Amazon
	{
		AWSECommerceService myaws; // Amazon web service
		ItemSearchRequest myrequest; // Request object (search criteria)
		ItemSearch mysearch; // Search object (contract details)
		ItemSearchResponse myresponse; // Response from a search
		Items myresponsedata; // Data from the response object
		Item[] mydvds; // Collection of individual results
			
		[SqlFunctionAttribute (FillRowMethodName="FillRow",
							   TableDefinition="[Original Release Date] datetime,"+
											   "[Title] nvarchar(40),"+
	                                           "[Amazon List Price] smallmoney,"+
	                                           "[Running Time] int,"+
	                                           "[Amazon Used Price] smallmoney,"+
	                                           "[Used Stock] int")]
		public static IEnumerable SearchForActor (SqlString actor)
		{		
			int mycount;	// # rows left
			Amazon wsc = new Amazon();			
			if (actor.IsNull) {return new Item[0]; }
			else
			{
				wsc.myaws = new AWSECommerceService();
				wsc.myrequest = new ItemSearchRequest();
				wsc.mysearch = new ItemSearch();

				wsc.myrequest.SearchIndex = "DVD";
				wsc.myrequest.Actor = actor.Value; // append user input
				wsc.myrequest.ResponseGroup = new string[] { "Medium" };
				wsc.myrequest.Sort = "salesrank";
				wsc.mysearch.SubscriptionId = "xxxxxxxxxxxxxxxxxxxx"; // Get your subscription id from Amazon.com/webservices
				wsc.mysearch.AssociateTag = ""; // Not Needed
				wsc.mysearch.Request = new ItemSearchRequest[] { wsc.myrequest };

				try
				{
					wsc.myresponse = wsc.myaws.ItemSearch(wsc.mysearch);
					wsc.myresponsedata = wsc.myresponse.Items[0];
					wsc.mydvds = wsc.myresponsedata.Item;
					mycount = wsc.mydvds.Length; // Number of entries we got back (max 10)
				}
				catch (Exception ex)
				{
					return new Item[0];
				}
				if (mycount > 0){return wsc.mydvds;}
				else { return new Item[0]; };
			}
		}

		public static void FillRow(Object obj, out SqlDateTime OriginalReleaseDate,
											   out SqlString Title,
											   out SqlMoney AmazonListPrice,
											   out SqlInt32 RunningTime,
											   out SqlMoney AmazonUsedPrice,
											   out SqlInt32 UsedStock)
		{
			Item mydvd = (Item)obj;
			// [Original Release Date] datetime
			try 
			{
				OriginalReleaseDate = System.Convert.ToDateTime(mydvd.ItemAttributes.TheatricalReleaseDate); 
			}
			catch (Exception ex) 
			{
				OriginalReleaseDate = SqlDateTime.Null;
			}
			// [Title] nvarchar(40)
			try
			{
				if (mydvd.ItemAttributes.Title.Length > 40) 
					Title = mydvd.ItemAttributes.Title.Substring(0, 40);
				else 
					Title = mydvd.ItemAttributes.Title;
			}
			catch (Exception ex) 
			{
				Title = null;
			}
			// [Amazon List Price] smallmoney
			try 
			{
				AmazonListPrice = SqlMoney.Parse(mydvd.ItemAttributes.ListPrice.FormattedPrice); 
			}
			catch (Exception ex) 
			{
				AmazonListPrice = SqlMoney.Null;
			}
			// [Running Time] int
			try 
			{
				RunningTime = System.Convert.ToInt32(mydvd.ItemAttributes.RunningTime.Value);
			}
			catch (Exception ex) 
			{
				RunningTime = SqlInt32.Null;
			}
			// [Amazon Used Price] smallmoney
			try 
			{
				AmazonUsedPrice = SqlMoney.Parse(mydvd.OfferSummary.LowestUsedPrice.FormattedPrice); 
			}
			catch (Exception ex) 
			{
				AmazonUsedPrice = SqlMoney.Null;
			}
			// [Used Stock] int
			try 
			{
				UsedStock = System.Convert.ToInt32(mydvd.OfferSummary.TotalUsed);
			}
			catch (Exception ex) 
			{
				UsedStock = SqlInt32.Null;
			}
		}
	}
