<%@ CodeTemplate Language="C#" TargetLanguage="Text" Description="Template description here." %>
<%@ Property Name="ConnectionString" Type="System.String" Category="Context" Description="Database connection string."%>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Name="SchemaExplorer.SqlSchemaProvider" %>
<%@ Assembly Name="CodeSmith.BaseTemplates" %>
<%@ Assembly Name="System.Data" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Import Namespace="System.Data" %>
// This file was generated from the <%=SourceDatabase.Name%> database at <%=DateTime.Now.ToString()%>.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

namespace Corillian.Corporate.Internal.DatabaseFacade
{
	#region class <%=SourceDatabase.Name%>DbCommands
	public class <%=SourceDatabase.Name%>DbCommands : IDisposable
	{
		SqlConnection _connection = null;
		
		public <%=SourceDatabase.Name%>DbCommands(string connectionString)
		{
			this._connection = new SqlConnection(connectionString);
			this._connection.Open();	
		}
		
		public void Dispose()
		{
			this._connection.Dispose();
		}

<%
foreach (CommandSchema cs in SourceDatabase.Commands)
{
	// Ignore *CCSP* sprocs.
	if (cs.FullName.IndexOf("CCSP") < 0)
	{
		string returnType = GetReturnType(cs);
%>
		public <%=returnType%> <%=GetMethodName(cs)%>(<%=GetMethodParameterList(cs)%>)
		{
			SqlCommand command = new SqlCommand("<%=cs.FullName%>", this._connection);
			command.CommandType = CommandType.StoredProcedure;
		<%
		foreach (ParameterSchema ps in cs.InputParameters)
		{
			if (IsValidParameter(ps))
			{
				//Log("DataType is {0}", ps.DataType);
                if (ps.AllowDBNull)
                {
                    switch (ps.DataType)
                    {
                        case DbType.String:
                        case DbType.StringFixedLength:
                        case DbType.AnsiString:
                        case DbType.AnsiStringFixedLength:
		%>
			command.Parameters.Add(new SqlParameter("<%=ps.Name%>", (<%=GetCSharpParameterName(ps)%> == null) ? System.DBNull.Value : (object) <%=GetCSharpParameterName(ps)%>));
		<%
    				        break;
                    
	    			    case DbType.Date:
                        case DbType.DateTime:
		%>
			command.Parameters.Add(new SqlParameter("<%=ps.Name%>", (<%=GetCSharpParameterName(ps)%> == DateTime.MinValue) ? System.DBNull.Value : (object) <%=GetCSharpParameterName(ps)%>));
		<%
                            break;
                    
                        default:
        %>
            command.Parameters.Add(new SqlParameter("<%=ps.Name%>", <%=GetCSharpParameterName(ps)%>));
        <%
                            break;
                    }
                }
				else
				{
		%>
			command.Parameters.Add(new SqlParameter("<%=ps.Name%>", <%=GetCSharpParameterName(ps)%>));
		<%
				}
			}
		}
		if (cs.AllOutputParameters.Count > 0)
		{
			// For now, only pick up the first output param
			ParameterSchema ps = cs.AllOutputParameters[0];
		%>
			SqlParameter outputParameter = null;
			outputParameter = new SqlParameter("<%=ps.Name%>", new <%=GetCSharpVariableType(ps.DataType)%>());
			outputParameter.Direction = ParameterDirection.Output;
			command.Parameters.Add(outputParameter);
			<%
		}
		if (returnType.Equals("void"))
		{
			%>
			command.ExecuteNonQuery();
			<%
		}
		else if (returnType.Equals("IDataReader"))
		{
			%>
			return command.ExecuteReader();
			<%
		}
		else
		{
			%>
			command.ExecuteNonQuery();
			return (<%=returnType%>) outputParameter.Value;
			<%
		}
		%>
			
		}
		
<%
	}
}
%>
	}
	#endregion
			
	#region Domain objects
	
<%
foreach (CommandSchema cmd in GetDistinctDomainObjects(SourceDatabase.Commands))
{
	//Log("[Schema Objects] {0}", GetDomainObjectName(cmd));

	if (cmd.CommandResults != null && cmd.CommandResults.Count > 0)
	{
		CommandResultSchema ts = cmd.CommandResults[0];
		string tableName = ExtractTableNameFromProcName(cmd);
		%> 
	#region class <%=tableName%>
	public class <%=tableName%>
	{
		#region Fields
		<%
		foreach (CommandResultColumnSchema cs in ts.Columns)
		{
			%>
		private <%=GetCSharpVariableType(cs.DataType)%> <%=GetFieldName(cs)%>;
			<%		
		if (GetCSharpTypeFromDbType(cs.DataType).IsValueType)
		{
			%>
		private bool <%=GetFieldName(cs)%>Specified = false;
			<%
		}
	}
		%> 
		#endregion
		
		#region Properties
		<%
	foreach (CommandResultColumnSchema cs in ts.Columns)
	{
		if (GetCSharpTypeFromDbType(cs.DataType).IsValueType)
		{
			%>
		public <%=GetCSharpVariableType(cs.DataType)%> <%=GetPropertyName(cs)%>
		{
			get
			{
				return this.<%=GetFieldName(cs)%>;
			}
			set
			{
				this.<%=GetFieldName(cs)%> = value;
				this.<%=GetFieldName(cs)%>Specified = true;
			}
		}
		public bool <%=GetPropertyName(cs)%>Specified
		{
			get
			{
				return this.<%=GetFieldName(cs)%>Specified;
			}
		}

			<%
		}
		else
		{
			%>		
		public <%=GetCSharpVariableType(cs.DataType)%> <%=GetPropertyName(cs)%>
		{
			get
			{
				return this.<%=GetFieldName(cs)%>;
			}
			set
			{
				this.<%=GetFieldName(cs)%> = value;
			}
		}
		
			<%		
		}
	}
	%> 
		#endregion
		
		#region Constructors
		public <%=tableName%>()
		{
		}
		#endregion
		
		#region Converters
		public static <%=tableName%> ConvertFromDataReader(IDataReader reader)
		{
			<%=tableName%> item = new <%=tableName%>();
			reader.Read();
			item.ConvertFromRecord(reader as IDataRecord);
			reader.Close();
			return item;
		}
		
		public static IList ConvertMultipleRecordsFromDataReader(IDataReader reader)
		{
			ArrayList result = new ArrayList();
			while (reader.Read())
			{
				<%=tableName%> item = new <%=tableName%>();
				item.ConvertFromRecord(reader as IDataRecord);
				result.Add(item);
			}
			reader.Close();
			return result;
		}
		
		protected virtual void ConvertFromRecord(IDataRecord record)
		{
	<%
	foreach (CommandResultColumnSchema cs in ts.Columns)
	{
		%>
			try
			{
				object val = record["<%=cs.Name%>"];
				if (val != null && val != System.DBNull.Value)
				{
					this.<%=GetPropertyName(cs)%> = (<%=GetCSharpVariableType(cs.DataType)%>) val;
				}
			}
			catch (System.IndexOutOfRangeException)
			{
			}
		<%		
	}
	%> 
		}
		#endregion
	}
	#endregion
	
	<%
	}
}
%>
	#endregion

}

<script runat="template">
//

private DatabaseSchema _sourceDatabase = null;

private DatabaseSchema SourceDatabase
{
	get
	{
		if (_sourceDatabase == null)
		{
			_sourceDatabase = new DatabaseSchema(new SqlSchemaProvider(), ConnectionString);
		}
		return _sourceDatabase;
	}
}

public string GetDataStoreClassName()
{
	return SourceDatabase.Name + "DataStore";
}

public string GetMethodName(CommandSchema cs)
{
	return cs.FullName.Replace("dbo.", "");
}

public string GetDomainObjectName(TableSchema ts)
{
	return ts.FullName.Replace("dbo.", "");
}

public string GetDomainObjectName(CommandSchema cmd)
{
	string name = cmd.Name;
	if (name.StartsWith("Read") || name.StartsWith("List"))
	{
		name = name.Substring(4);
	}
	return name;
}

public string GetDomainObjectName(CommandResultSchema crs)
{
	string name = crs.Command.Name;
	if (name.StartsWith("Read") || name.StartsWith("List"))
	{
		name = name.Substring(4);
	}
	return name;
}

public string GetPropertyName(ColumnSchema cs)
{
	return cs.Name;
}

public string GetFieldName(ColumnSchema cs)
{
	return "_" + GetCamelCaseName(cs.Name);
}

public string GetPropertyName(CommandResultColumnSchema cs)
{
	return cs.Name;
}

public string GetFieldName(CommandResultColumnSchema cs)
{
	return "_" + GetCamelCaseName(cs.Name);
}

public string GetMethodParameterList(CommandSchema cs)
{
	if (cs.InputParameters.Count == 0)
	{
		return String.Empty;
	}
	string s = String.Empty;
	foreach (ParameterSchema ps in cs.InputParameters)
	{
		if (IsValidParameter(ps))
		{
			string format = (s.Length == 0) ? "{0} {1}" : ", {0} {1}";
			s += String.Format(format, GetCSharpVariableType(ps.DataType), GetCSharpParameterName(ps));
		}
	}
	return s;
}

public bool IsValidParameter(ParameterSchema ps)
{
	if (ps.Name.ToLower().Equals("@testmode"))
	{
		return false;
	}
	return true;
}

public string GetCSharpParameterName(ParameterSchema ps)
{
	return GetCamelCaseName(ps.Name.Replace("@", ""));
}

public string GetCamelCaseName(string value)
{
	return value.Substring(0, 1).ToLower() + value.Substring(1);
}

public string GetReturnType(CommandSchema cs)
{
	if (cs.FullName.StartsWith("dbo.Read"))
	{
		return "IDataReader";
	}
	if (cs.FullName.StartsWith("dbo.List"))
	{
		return "IDataReader";
	}
	if (cs.AllOutputParameters.Count == 0)
	{
		return "void";
	}
	if (cs.AllOutputParameters.Count == 1)
	{
		return GetCSharpVariableType(cs.AllOutputParameters[0].DataType);
	}
	return "__ERROR__";
}

public string GetCSharpVariableType(DbType dbType)
{
	switch (dbType)
	{
		case DbType.AnsiString: return "string";
		case DbType.AnsiStringFixedLength: return "string";
		//case DbType.Binary: return "byte[]";
		case DbType.Binary: return "long";
		case DbType.Boolean: return "bool";
		case DbType.Byte: return "byte";
		case DbType.Currency: return "decimal";
		case DbType.Date: return "DateTime";
		case DbType.DateTime: return "DateTime";
		case DbType.Decimal: return "decimal";
		case DbType.Double: return "double";
		case DbType.Guid: return "Guid";
		case DbType.Int16: return "short";
		case DbType.Int32: return "int";
		case DbType.Int64: return "long";
		case DbType.Object: return "object";
		case DbType.SByte: return "sbyte";
		case DbType.Single: return "float";
		case DbType.String: return "string";
		case DbType.StringFixedLength: return "string";
		case DbType.Time: return "TimeSpan";
		case DbType.UInt16: return "ushort";
		case DbType.UInt32: return "uint";
		case DbType.UInt64: return "ulong";
		case DbType.VarNumeric: return "decimal";
		default:
		{
			return "__UNKNOWN__" + dbType.ToString();
		}
	}
}

public Type GetCSharpTypeFromDbType(DbType dbType)
{
	switch (dbType)
	{
		case DbType.AnsiString: return typeof(string);
		case DbType.AnsiStringFixedLength: return typeof(string);
		//case DbType.Binary: return typeof(byte[]);
		case DbType.Binary: return typeof(long);
		case DbType.Boolean: return typeof(bool);
		case DbType.Byte: return typeof(byte);
		case DbType.Currency: return typeof(decimal);
		case DbType.Date: return typeof(DateTime);
		case DbType.DateTime: return typeof(DateTime);
		case DbType.Decimal: return typeof(decimal);
		case DbType.Double: return typeof(double);
		case DbType.Guid: return typeof(Guid);
		case DbType.Int16: return typeof(short);
		case DbType.Int32: return typeof(int);
		case DbType.Int64: return typeof(long);
		case DbType.Object: return typeof(object);
		case DbType.SByte: return typeof(sbyte);
		case DbType.Single: return typeof(float);
		case DbType.String: return typeof(string);
		case DbType.StringFixedLength: return typeof(string);
		case DbType.Time: return typeof(TimeSpan);
		case DbType.UInt16: return typeof(ushort);
		case DbType.UInt32: return typeof(uint);
		case DbType.UInt64: return typeof(ulong);
		case DbType.VarNumeric: return typeof(decimal);
		default:
		{
			return typeof(string);
		}
	}
}

public void Log(string format, params object[] args)
{
	bool debug = false;
	if (debug)
	{
		Response.Write(String.Format("//[Template] " + format + "\n", args));
	}
}

public bool IsNameATable(string name, out TableSchema matchingts)
{
	foreach (TableSchema ts in SourceDatabase.Tables)
	{
		if (name == GetDomainObjectName(ts))
		{
			matchingts = ts;
			return true;
		}
	}
	matchingts = null;
	return false;
}

public bool IsNameAPrimaryKey(string name, TableSchema ts)
{
	if (name != null && name.Length > 0)
		name = name.Substring(1);
	
	//Log("Name is {0}", name);
	foreach (ColumnSchema cs in ts.Columns)
	{
		if (cs.IsPrimaryKeyMember && cs.Name == name)
		{
			return true;
		}
	}
	return false;
}

public bool HasResultSet(CommandSchema cmd)
{
	return (cmd.CommandResults != null && cmd.CommandResults.Count > 0);
}

public string ExtractTableNameFromProcName(CommandSchema cmd)
{
	string procName = GetDomainObjectName(cmd);
	string[] words = new string[] {"By", "For", "Filtered"};
	
	foreach (string word in words)
	{
		int pos = procName.IndexOf(word);
		if (pos > 0) 
		{
			procName = procName.Substring(0, pos);
			return procName;
		}
	}
	
	return procName;
}

//If stored proc has just one parameter and this parameter has the same name as the primary key,
//it is a 'ReadSingle' command
public bool IsReadSingleCommand(CommandSchema cmd)
{
	bool result = false;
	string procName = GetDomainObjectName(cmd);
	TableSchema matchingts = null;

	if (IsNameATable(procName, out matchingts))
	{
		//If it has 1 or more params, the first param should be the primary key
		if (cmd.InputParameters != null && cmd.InputParameters.Count > 0)
		{
			string paramName = cmd.InputParameters[0].Name;
			//Log("ParamName is {0}", paramName);
			if (IsNameAPrimaryKey(paramName, matchingts))
			{
				result = true;
			}
		}		
		else
		{
			result = true;
		}
	}
	return result;
}

public bool IsDistinctDomainObject(CommandSchemaCollection distinct, string procName)
{
	//Log("[IsDistinctDomainObject] ProcName is {0}", procName);
	foreach (CommandSchema cmd in distinct)
	{
		//Log("[IsDistinctDomainObject] Checking against {0}", ExtractTableNameFromProcName(cmd));
		if (procName == ExtractTableNameFromProcName(cmd))
		{
			return false;
		}
	}
	return true;
}

public void AddMissedTables(CommandSchemaCollection distinct)
{
	foreach (CommandSchema cmd in SourceDatabase.Commands)
	{
		if (HasResultSet(cmd) && (cmd.Name.StartsWith("Read") || cmd.Name.StartsWith("List")))
		{
			string tableName = ExtractTableNameFromProcName(cmd);
			//Log("[AddMissedTables] Table {0}: Considering.", tableName);
			
			if (tableName != null && IsDistinctDomainObject(distinct, tableName))
			{
				//Log("[AddMissedTables] Table {0}: adding it now.", tableName);
				distinct.Add(cmd);
			}
		}
	}
}

public CommandSchemaCollection GetDistinctDomainObjects(CommandSchemaCollection csc)
{
	if (csc == null || csc.Count <= 0) return null;
	
	CommandSchemaCollection distinct = new CommandSchemaCollection();
	foreach (CommandSchema cmd in SourceDatabase.Commands)
	{
		string procName = GetDomainObjectName(cmd);
		if (HasResultSet(cmd) && IsReadSingleCommand(cmd) && IsDistinctDomainObject(distinct, procName))
		{
			distinct.Add(cmd);
		}
	}
	
	AddMissedTables(distinct);
	return distinct;
}

</script>