/trunk/Source/nHydrate.Generator.SQLInstaller/ProjectItemGenerators/Functions/FunctionsTemplate.cs

#region Copyright (c) 2006-2012 nHydrate.org, All Rights Reserved

//--------------------------------------------------------------------- *

//                          NHYDRATE.ORG                                *

//             Copyright (c) 2006-2012 All Rights reserved              *

//                                                                      *

//                                                                      *

//This file and its contents are protected by United States and         *

//International copyright laws.  Unauthorized reproduction and/or       *

//distribution of all or any portion of the code contained herein       *

//is strictly prohibited and will result in severe civil and criminal   *

//penalties.  Any violations of this copyright will be prosecuted       *

//to the fullest extent possible under law.                             *

//                                                                      *

//THE SOURCE CODE CONTAINED HEREIN AND IN RELATED FILES IS PROVIDED     *

//TO THE REGISTERED DEVELOPER FOR THE PURPOSES OF EDUCATION AND         *

//TROUBLESHOOTING. UNDER NO CIRCUMSTANCES MAY ANY PORTION OF THE SOURCE *

//CODE BE DISTRIBUTED, DISCLOSED OR OTHERWISE MADE AVAILABLE TO ANY     *

//THIRD PARTY WITHOUT THE EXPRESS WRITTEN CONSENT OF THE NHYDRATE GROUP *

//                                                                      *

//UNDER NO CIRCUMSTANCES MAY THE SOURCE CODE BE USED IN WHOLE OR IN     *

//PART, AS THE BASIS FOR CREATING A PRODUCT THAT PROVIDES THE SAME, OR  *

//SUBSTANTIALLY THE SAME, FUNCTIONALITY AS THIS PRODUCT                 *

//                                                                      *

//THE REGISTERED DEVELOPER ACKNOWLEDGES THAT THIS SOURCE CODE           *

//CONTAINS VALUABLE AND PROPRIETARY TRADE SECRETS OF NHYDRATE,          *

//THE REGISTERED DEVELOPER AGREES TO EXPEND EVERY EFFORT TO             *

//INSURE ITS CONFIDENTIALITY.                                           *

//                                                                      *

//THE END USER LICENSE AGREEMENT (EULA) ACCOMPANYING THE PRODUCT        *

//PERMITS THE REGISTERED DEVELOPER TO REDISTRIBUTE THE PRODUCT IN       *

//EXECUTABLE FORM ONLY IN SUPPORT OF APPLICATIONS WRITTEN USING         *

//THE PRODUCT.  IT DOES NOT PROVIDE ANY RIGHTS REGARDING THE            *

//SOURCE CODE CONTAINED HEREIN.                                         *

//                                                                      *

//THIS COPYRIGHT NOTICE MAY NOT BE REMOVED FROM THIS FILE.              *

//--------------------------------------------------------------------- *

#endregion

using System;

using System.Linq;

using System.Collections.Generic;

using System.Text;

using nHydrate.Generator.Models;

using nHydrate.Generator.ProjectItemGenerators;

using nHydrate.Generator.Common;



namespace nHydrate.Generator.SQLInstaller.ProjectItemGenerators.Functions

{

	class FunctionsTemplate : BaseDbScriptTemplate

	{

		private StringBuilder sb = new StringBuilder();



		#region Constructors

		public FunctionsTemplate(ModelRoot model)

		{

			_model = model;

		}

		#endregion



		#region BaseClassTemplate overrides

		public override string FileContent

		{

			get

			{

				this.GenerateContent();

				return sb.ToString();

			}

		}



		public override string FileName

		{

			get { return "Functions.sql"; }

		}

		#endregion



		#region GenerateContent

		private void GenerateContent()

		{

			if (_model.Database.AllowZeroTouch) return;

			try

			{

				this.AppendFullTemplate();

			}

			catch (Exception ex)

			{

				throw;

			}

		}



		#endregion



		#region string methods



		protected string BuildSelectList(Function function)

		{

			var output = new StringBuilder();

			int ii = 0;

			foreach (var column in function.GeneratedColumns)

			{

				ii++;

				output.Append(column.DatabaseName.ToLower());

				if (ii != function.GeneratedColumns.Count())

				{

					output.Append("," + Environment.NewLine + "\t");

				}

			}

			return output.ToString();

		}



		protected string BuildParameterList(Function function)

		{

			var output = new StringBuilder();

			var parameterList = function.GetParameters().Where(x => x.Generated && x.SortOrder > 0).OrderBy(x => x.SortOrder).ToList();

			parameterList.AddRange(function.GetParameters().Where(x => x.Generated && x.SortOrder == 0).OrderBy(x => x.Name).ToList());



			int ii = 0;

			foreach (var parameter in parameterList)

			{

				ii++;

				output.Append("\t@" + ValidationHelper.MakeDatabaseScriptIdentifier(parameter.DatabaseName) + " " +

					parameter.DatabaseType.ToLower() +

					(parameter.GetPredefinedSize() == -1 ? "(" + parameter.GetLengthString() + ") " : string.Empty) + (parameter.IsOutputParameter ? " out " : " = null"));



				if (ii != parameterList.Count)

					output.Append(",");

				output.AppendLine();

			}

			return output.ToString();

		}



		#endregion



		public void AppendFullTemplate()

		{

			try

			{

				sb.AppendLine("--DO NOT MODIFY THIS FILE. IT IS ALWAYS OVERWRITTEN ON GENERATION.");

				sb.AppendLine("--Model Version " + _model.Version);

				sb.AppendLine();

				sb.AppendLine("--This SQL is generated for functions");

				sb.AppendLine();

				nHydrate.Generator.GenerationHelper.AppendCopyrightInSQL(sb, _model);



				foreach (var function in _model.Database.Functions.Where(x => x.Generated).OrderBy(x => x.DatabaseName))

				{

					sb.AppendLine("if exists (select * from dbo.sysobjects where id = object_id(N'[" + function.GetSQLSchema() + "].[" + function.PascalName + "]') and [xtype] IN ('FN', 'IF', 'TF'))");

					sb.AppendLine("	drop function [" + function.GetSQLSchema() + "].[" + function.PascalName + "]");

					sb.AppendLine("GO");

					sb.AppendLine();

					sb.AppendLine("SET QUOTED_IDENTIFIER ON ");

					sb.AppendLine("GO");

					sb.AppendLine("SET ANSI_NULLS ON");

					sb.AppendLine("GO");

					sb.AppendLine();

					sb.AppendLine("CREATE FUNCTION [" + function.GetSQLSchema() + "].[" + function.PascalName + "]");



					sb.AppendLine("(");

					if (function.Parameters.Count > 0)

						sb.Append(this.BuildParameterList(function));

					sb.AppendLine(")");



					sb.Append("RETURNS ");



					if (function.IsTable && string.IsNullOrEmpty(function.ReturnVariable))

					{

						//There is NOT a returned table defined. This is a straight select

						sb.AppendLine("TABLE");

					}

					else if (function.IsTable && !string.IsNullOrEmpty(function.ReturnVariable))

					{

						//There is a returned table defined

						sb.Append("@" + function.ReturnVariable + " TABLE (");



						var columnList = function.GetColumns().Where(x => x.Generated).ToList();

						foreach (var column in columnList)

						{

							sb.Append(column.DatabaseName + " " + column.DatabaseType);

							if (columnList.IndexOf(column) < columnList.Count - 1) sb.Append(", ");

						}

						sb.AppendLine(")");

					}

					else

					{

						var column = function.Columns.First().Object as FunctionColumn;

						sb.AppendLine(column.DatabaseType.ToLower());

					}

					sb.AppendLine("AS");



					sb.AppendLine();

					sb.Append(function.SQL);

					sb.AppendLine();

					sb.AppendLine("GO");

					sb.AppendLine();

					if (!string.IsNullOrEmpty(_model.Database.GrantExecUser))

						if (!string.IsNullOrEmpty(_model.Database.GrantExecUser))

						{

							if (function.IsTable) sb.AppendFormat("GRANT SELECT ON [" + function.GetSQLSchema() + "].[{0}] TO [{1}]", function.PascalName, _model.Database.GrantExecUser).AppendLine();

							else sb.AppendFormat("GRANT EXECUTE ON [" + function.GetSQLSchema() + "].[{0}] TO [{1}]", function.PascalName, _model.Database.GrantExecUser).AppendLine();

							sb.AppendLine("GO");

							sb.AppendLine();

						}



				}

			}

			catch (Exception ex)

			{

				throw;

			}

		}



	}

}