context: #region License
  
  // Copyright (c) 2013, ClearCanvas Inc.
  // All rights reserved.
  // http://www.ClearCanvas.ca
  //
  // This file is part of the ClearCanvas RIS/PACS open source project.
  //
  // The ClearCanvas RIS/PACS open source project is free software: you can
  // redistribute it and/or modify it under the terms of the GNU General Public
  // License as published by the Free Software Foundation, either version 3 of the
  // License, or (at your option) any later version.
  //
  // The ClearCanvas RIS/PACS open source project is distributed in the hope that it
  // will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
  // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General
  // Public License for more details.
  //
  // You should have received a copy of the GNU General Public License along with
  // the ClearCanvas RIS/PACS open source project.  If not, see
  // <http://www.gnu.org/licenses/>.
  
  #endregion
  
  using System;
  using System.Collections.Generic;
  using System.Data;
  using System.Data.SqlClient;
  using System.IO;
  using System.Reflection;
  using System.Text;
  using System.Xml;
  using Macro.Common;
  using Macro.Enterprise.Core;
  
  namespace Macro.ImageServer.Enterprise.SqlServer
  {
      /// <summary>
      /// Provides base implementation of <see cref="IEntityBroker{TServerEntity,TSearchCriteria,TUpdateColumns}"/>.
      /// </summary>
      /// <remarks>
      /// <para>
      /// This class provides a base implementation for doing a number of SQL related operations against
      /// a <see cref="ServerEntity"/> derived class.  The class implements the interface using
      /// SQL Server 2005 and ADO.NET.  
      /// </para>
      /// <para>
      /// It can generate dynamic SQL queries, updates, and inserts.
      /// It also allows for querying against a <see cref="ServerEntity"/> using a 
      /// <see cref="EntitySelectCriteria"/> derived class.
      /// </para>
      /// </remarks>
      /// <typeparam name="TServerEntity">The ServerEntity derived class to work against.</typeparam>
      /// <typeparam name="TSelectCriteria">The appropriate criteria for selecting against the entity.</typeparam>
      /// <typeparam name="TUpdateColumns">The columns for doing insert or updates.</typeparam>
      public abstract class EntityBroker<TServerEntity, TSelectCriteria, TUpdateColumns> : Broker,
                                                                                           IEntityBroker
                                                                                               <TServerEntity,
                                                                                               TSelectCriteria,
                                                                                               TUpdateColumns>
          where TServerEntity : ServerEntity, new()
          where TSelectCriteria : EntitySelectCriteria
          where TUpdateColumns : EntityUpdateColumns
      {
          #region Private Members
  
          private readonly String _entityName;
  
          #endregion
  
          #region Constructors
  
          protected EntityBroker(String entityName)
          {
              _entityName = entityName;
          }
  
          #endregion
  
          #region Private Static Members
  
          /// <summary>
          /// Gets ORDER BY clauses based on the input search condition.
          /// </summary>
          /// <returns>A string containing the ORDER BY clause.</returns>
          private static string GetSelectOrderBy(String entity, EntitySelectCriteria criteria)
          {
              var sb = new StringBuilder();
  
              // recurse on subCriteria
              foreach (SearchCriteria subCriteria in criteria.EnumerateSubCriteria())
              {
                  string variable = string.Format("{0}.{1}", entity, subCriteria.GetKey());
                  var sc = subCriteria as SearchConditionBase;
                  if (sc != null)
                  {
                      if (sc.SortPosition != -1)
                      {
                          // With the Server, all primary keys end with "Key".  The database implementation itself
                          // names these columns with the name GUID instead of Key.
                          string sqlColumnName = variable.EndsWith("Key") 
                                                     ? variable.Replace("Key", "GUID") 
                                                     : variable;
  
                          if (sb.ToString().Length == 0)
                              sb.AppendFormat("ORDER BY {0} {1}", sqlColumnName, sc.SortDirection ? "ASC" : "DESC");
                          else
                              sb.AppendFormat(", {0} {1}", sqlColumnName, sc.SortDirection ? "ASC" : "DESC");
                      }
                  }
              }
  
              return sb.ToString();
          }
  
          /// <summary>
          /// Gets WHERE clauses based on the input search condition for a specific column.
          /// </summary>
          /// <param name="variable"></param>
          /// <param name="sc"></param>
          /// <param name="command">The SQL Command.</param>
          /// <returns>A string containing the WHERE clause for the column.</returns>
          private static string GetSelectWhereText(string variable, SearchConditionBase sc, SqlCommand command)
          {
              var sb = new StringBuilder();
              object[] values;             
  
              // With the Server, all primary keys end with "Key".  The database implementation itself
              // names these columns with the name GUID instead of Key.
              string sqlColumnName = variable.EndsWith("Key") 
                                         ? variable.Replace("Key", "GUID") : variable;
  
              // We use input parameters to the select statement.  We create a variable name for the 
              // input parameter based on the column name input.  Variable names can't have periods,
              // so we have to remove the "."
              int j = sqlColumnName.IndexOf(".");
              string sqlParmName = j != -1 
                                       ? sqlColumnName.Remove(j, 1) : sqlColumnName;
  
              // Now go through the actual input parameters.  Replace references to ServerEntityKey with
              // the GUID itself for these parameters, and replace ServerEnum derived references with the 
              // value of the enum in the array so the input parameters work properly.
  
              if (sc.Values!=null && sc.Values.Length>0)
              {
                  values = new object[sc.Values.Length];
                  for (int i = 0; i < sc.Values.Length; i++)
                  {
                      var key = sc.Values[i] as ServerEntityKey;
                      if (key != null)
                          values[i] = key.Key;
                      else
                      {
                          var e = sc.Values[i] as ServerEnum;
                          values[i] = e != null ? e.Enum : sc.Values[i];
                      }
                  }
              }
              else
              {
                  values = new object[1];
                  values[0] = DBNull.Value;
              }
              
  
              // Generate the actual WHERE clauses based on the type of condition.
              switch (sc.Test)
              {
                  case SearchConditionTest.Equal:
                      var doc = values[0] as XmlDocument;
                      if (doc != null)
                      {
                          var node = doc.SelectSingleNode("/Select/XPath/@path");
                          string xpath = node == null ? string.Empty : node.Value;
  
                          node = doc.SelectSingleNode("/Select/Select/@value");
                          string val = node == null ? string.Empty : node.Value;
  
                          if (!string.IsNullOrEmpty(xpath) && !string.IsNullOrEmpty(val))
                          {
                              sb.AppendFormat("{0}.value('{1}','text') = @{2}", sqlColumnName, xpath, sqlParmName);
                              command.Parameters.AddWithValue("@" + sqlParmName, val);
                          }
                      }
                      else
                      {
                          sb.AppendFormat("{0} = @{1}", sqlColumnName, sqlParmName);
                          command.Parameters.AddWithValue("@" + sqlParmName, values[0]);
                      }
                      break;
                  case SearchConditionTest.NotEqual:
                      sb.AppendFormat("{0} <> @{1}", sqlColumnName, sqlParmName);
                      command.Parameters.AddWithValue("@" + sqlParmName, values[0]);
                      break;
                  case SearchConditionTest.Like:
                      doc = values[0] as XmlDocument;
                      if (doc != null)
                      {
                          var node = doc.SelectSingleNode("/Select/XPath/@path");
                          string xpath = node == null ? string.Empty : node.Value;
  
                          node = doc.SelectSingleNode("/Select/XPath/@value");
                          string val = node == null ? string.Empty : node.Value;
  
                          if (!string.IsNullOrEmpty(xpath) && !string.IsNullOrEmpty(val))
                          {
                              sb.AppendFormat("{0}.value('{1}','text') like @{2}", sqlColumnName, xpath, sqlParmName);
                              command.Parameters.AddWithValue("@" + sqlParmName, val);
                          }
                      }
                      else
                      {
                          // +'' in this statement was a total hack job to fix a performance issue with SQL.  See this:
                          // http://stackoverflow.com/questions/3495355/sql-server-performance-of-parameterized-queries-with-leading-wildcards
                          sb.AppendFormat("{0}+'' like @{1}", sqlColumnName, sqlParmName);
                          command.Parameters.AddWithValue("@" + sqlParmName, values[0]);
                      }
                      break;
                  case SearchConditionTest.NotLike:
                      sb.AppendFormat("{0}+'' not like @{1}", sqlColumnName, sqlParmName);
                      command.Parameters.AddWithValue("@" + sqlParmName, values[0]);
                      break;
                  case SearchConditionTest.Between:
                      sb.AppendFormat("{0} between @{1}1 and @{1}2", sqlColumnName, sqlParmName);
                      command.Parameters.AddWithValue("@" + sqlParmName + "1", values[0]);
                      command.Parameters.AddWithValue("@" + sqlParmName + "2", values[1]);
                      break;
                  case SearchConditionTest.In:
                      sb.AppendFormat("{0} in (", sqlColumnName); // assume at least one param
                      for (int i = 0; i < values.Length; i++)
                      {
                          if (i == 0)
                              sb.AppendFormat("@{0}{1}", sqlParmName, i + 1);
                          else
                              sb.AppendFormat(", @{0}{1}", sqlParmName, i + 1);
                          command.Parameters.AddWithValue(string.Format("@{0}{1}", sqlParmName, i + 1), values[i]);
                      }
                      sb.Append(")");
                      break;
                  case SearchConditionTest.NotIn:
                      sb.AppendFormat("{0} not in (", sqlColumnName); // assume at least one param
                      for (int i = 0; i < values.Length; i++)
                      {
                          if (i == 0)
                              sb.AppendFormat("@{0}{1}", sqlParmName, i + 1);
                          else
                              sb.AppendFormat(", @{0}{1}", sqlParmName, i + 1);
                          command.Parameters.AddWithValue(string.Format("@{0}{1}", sqlParmName, i + 1), values[i]);
                      }
                      sb.Append(")");
                      break;
                  case SearchConditionTest.LessThan:
                      sb.AppendFormat("{0} < @{1}", sqlColumnName, sqlParmName);
                      command.Parameters.AddWithValue("@" + sqlParmName, values[0]);
                      break;
                  case SearchConditionTest.LessThanOrEqual:
                      sb.AppendFormat("{0} <= @{1}", sqlColumnName, sqlParmName);
                      command.Parameters.AddWithValue("@" + sqlParmName, values[0]);
                      break;
                  case SearchConditionTest.MoreThan:
                      sb.AppendFormat("{0} > @{1}", sqlColumnName, sqlParmName);
                      command.Parameters.AddWithValue("@" + sqlParmName, values[0]);
                      break;
                  case SearchConditionTest.MoreThanOrEqual:
                      sb.AppendFormat("{0} >= @{1}", sqlColumnName, sqlParmName);
                      command.Parameters.AddWithValue("@" + sqlParmName, values[0]);
                      break;
                  case SearchConditionTest.NotNull:
                      sb.AppendFormat("{0} is not null", sqlColumnName);
                      command.Parameters.AddWithValue("@" + sqlParmName, values[0]);
                      break;
                  case SearchConditionTest.Null:
                      sb.AppendFormat("{0} is null", sqlColumnName);
                      command.Parameters.AddWithValue("@" + sqlParmName, values[0]);
                      break;
                  case SearchConditionTest.NotExists:
                      {
                          var rec = sc as RelatedEntityCondition<EntitySelectCriteria>;
                          if (rec == null) throw new PersistenceException("Casting error with RelatedEntityCondition", null);
                          var notExistsSubCriteria = (EntitySelectCriteria)values[0];
  
                          string baseTableColumn = rec.BaseTableColumn;
                          if (baseTableColumn.EndsWith("Key"))
                              baseTableColumn = baseTableColumn.Replace("Key", "GUID");
                          string relatedTableColumn = rec.RelatedTableColumn;
                          if (relatedTableColumn.EndsWith("Key"))
                              relatedTableColumn = relatedTableColumn.Replace("Key", "GUID");
  
                          string sql;
                          if (String.Format("{0}GUID", notExistsSubCriteria.GetKey()).Equals(relatedTableColumn))
                              sql = GetSelectSql(notExistsSubCriteria.GetKey(), command, notExistsSubCriteria, null, null,
                                                 String.Format("{0}.{2} = {1}.GUID", variable, notExistsSubCriteria.GetKey(), baseTableColumn));
                          else
                              sql = GetSelectSql(notExistsSubCriteria.GetKey(), command, notExistsSubCriteria, null, null,
                                                 String.Format("{0}.{2} = {1}.{3}", variable, notExistsSubCriteria.GetKey(), baseTableColumn, relatedTableColumn));
  
                          sb.AppendFormat("NOT EXISTS ({0})", sql);
                          break;
                      }
                      
                  case SearchConditionTest.Exists:
                      {
                          var rec = sc as RelatedEntityCondition<EntitySelectCriteria>;
                          if (rec == null) throw new PersistenceException("Casting error with RelatedEntityCondition", null);
                          var existsSubCriteria = (EntitySelectCriteria)values[0];
  
                          string baseTableColumn = rec.BaseTableColumn;
                          if (baseTableColumn.EndsWith("Key"))
                              baseTableColumn = baseTableColumn.Replace("Key", "GUID");
                          string relatedTableColumn = rec.RelatedTableColumn;
                          if (relatedTableColumn.EndsWith("Key"))
                              relatedTableColumn = relatedTableColumn.Replace("Key", "GUID");
  
                          string existsSql;
                          if (String.Format("{0}GUID",existsSubCriteria.GetKey()).Equals(relatedTableColumn))
                              existsSql = GetSelectSql(existsSubCriteria.GetKey(), command, existsSubCriteria, null, null,
                                                       String.Format("{0}.{2} = {1}.GUID", variable, existsSubCriteria.GetKey(),
                                                                     baseTableColumn));
                          else
                              existsSql = GetSelectSql(existsSubCriteria.GetKey(), command, existsSubCriteria, null, null,
                                                       String.Format("{0}.{2} = {1}.{3}", variable, existsSubCriteria.GetKey(),
                                                                     baseTableColumn, relatedTableColumn));
                          sb.AppendFormat("EXISTS ({0})", existsSql);
                          break;
                      }
  
                  default:
                      throw new ApplicationException(); // invalid
              }
  
              return sb.ToString();
          }
  
          /// <summary>
          /// Get an array of WHERE clauses for all of the search criteria specified.
          /// </summary>
          /// <param name="qualifier">The table name to use for each of where clauses.</param>
          /// <param name="criteria">The actual Search Criteria specified.</param>
          /// <param name="command">The SQL command.</param>
          /// <returns>An array of WHERE clauses.</returns>
          private static String[] GetWhereSearchCriteria(string qualifier, SearchCriteria criteria, SqlCommand command)
          {
              var list = new List<string>();
  
              if (criteria is SearchConditionBase)
              {
                  var sc = (SearchConditionBase) criteria;
                  if (sc.Test != SearchConditionTest.None)
                  {
                      String text = GetSelectWhereText(qualifier, sc, command);
                      list.Add(text);
                  }
              }
              else
              {
                  // recurse on subCriteria
                  foreach (SearchCriteria subCriteria in criteria.EnumerateSubCriteria())
                  {
                      // Note:  this is a bit ugly, but we don't do the <Table>.<Column>
                      // syntax for Subselect type criteria.  Subselects only need 
                      // the table name, and there isn't a real column associated with it.
                      // We could pass the entity down all the way, but decided to do it
                      // this way instead.
                      string subQualifier;
                      if (subCriteria is RelatedEntityCondition<EntitySelectCriteria>)
                      {
                          subQualifier = qualifier;   
                      }
                      else
                          subQualifier = string.Format("{0}.{1}", qualifier, subCriteria.GetKey());
  
                      list.AddRange(GetWhereSearchCriteria(subQualifier, subCriteria, command));
                  }
              }
  
              return list.ToArray();
          }
  
          /// <summary>
          /// Proves a SQL statement based on the supplied input criteria.
          /// </summary>
          /// <param name="entityName">The entity that is being selected from.</param>
          /// <param name="command">The SqlCommand to use.</param>
          /// <param name="criteria">The criteria for the select</param>
          /// <param name="startIndex">The start index from within the result set</param>
          /// <param name="maxRows">The max rows.</param>
          /// <param name="subWhere">If this is being used to generate the SQL for a sub-select, additional where clauses are included here for the select.  Otherwise the parameter is null.</param>
          /// <returns>The SQL string.</returns>
          private static string GetSelectSql(string entityName, SqlCommand command, EntitySelectCriteria criteria, int? startIndex, int? maxRows, String subWhere)
          {
              /*
  			 * Here's the general select format for querying a subset of rows
  			   SELECT WorkQueueDetails.*
  				FROM
  				(SELECT WorkQueue.*, 
  					ROW_NUMBER() OVER(ORDER BY ScheduledTime ASC) as RowNum
  					FROM WorkQueue
  				) AS WorkQueueDetails
  				WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1
  			 */
              string orderBy = GetSelectOrderBy(entityName, criteria);
  
              var sb = new StringBuilder();
              if (maxRows == null || startIndex == null)
                  sb.AppendFormat("SELECT * FROM {0}", entityName);
                  // had a bug at the tail end of the 1.5 release where Web GUI queries w/ paging did not work properly
                  // using the TOP syntax for the selects was not giving the same results as the ROW_NUMBER() syntax.
                  // Since this code is also used extensively when we do a FindOne() call, made it so in that case we
                  // still do the TOP, but use ROW_NUMBER syntax for other situations, which would include the web gui.
              else if (startIndex.Value == 0 && maxRows.Value == 1)
                  sb.AppendFormat("SELECT TOP {0} * FROM {1}", maxRows, entityName);
              else
              {
                  if (orderBy.Length > 0)
                      sb.AppendFormat("SELECT {0}.*,ROW_NUMBER() OVER({1}) as RowNum FROM {0}", entityName, orderBy);
                  else
                  {
                      // no OrderBy clause to sort the list, just assign row number to 1 to keep their order as is
                      sb.AppendFormat("SELECT {0}.*,ROW_NUMBER() OVER( ORDER BY {0}.GUID ) as RowNum FROM {0}", entityName);
                  }
              }
              // Generate an array of the WHERE clauses to be used.
              String[] where = GetWhereSearchCriteria(entityName, criteria, command);
  
              // Add the where clauses on.
              bool first = true;
              if (subWhere != null)
              {
                  first = false;
                  sb.AppendFormat(" WHERE {0}", subWhere);
              }
  
              foreach (String clause in where)
              {
                  if (first)
                  {
                      first = false;
                      sb.AppendFormat(" WHERE {0}", clause);
                  }
                  else
                      sb.AppendFormat(" AND {0}", clause);
              }
  
              if (maxRows == null || startIndex == null)
              {
                  if (orderBy.Length > 0)
                      sb.AppendFormat(" {0}", orderBy);
              }
              else if (startIndex.Value == 0 && maxRows.Value == 1)
              {
                  if (orderBy.Length > 0)
                      sb.AppendFormat(" {0}", orderBy);
              }
              else
              {
                  StringBuilder sb2 = new StringBuilder();
                  sb2.AppendFormat("SELECT {0}Details.* FROM ({1}) AS {0}Details ", entityName, sb);
                  if(startIndex.Value == 0)
                      sb2.AppendFormat("WHERE RowNum BETWEEN @StartRowIndex AND @MaximumRows");
                  else 
                      sb2.AppendFormat("WHERE RowNum BETWEEN @StartRowIndex AND (@StartRowIndex + @MaximumRows) - 1");
                  command.Parameters.AddWithValue("@StartRowIndex", startIndex.Value);
                  command.Parameters.AddWithValue("@MaximumRows", maxRows.Value);
                  return sb2.ToString();
              }
              return sb.ToString();
          }
  
          /// <summary>
          /// Proves a SELECT COUNT(*) SQL statement based on the supplied input criteria.
          /// </summary>
          /// <param name="entityName">The entity that is being selected from.</param>
          /// <param name="command">The SqlCommand to use.</param>
          /// <param name="criteria">The criteria for the select count</param>
          /// <param name="subWhere">If this is being used to generate the SQL for a sub-select, additional where clauses are included here for the select.  Otherwise the parameter is null.</param>
          /// <returns>The SQL string.</returns>
          private static string GetSelectCountSql(string entityName, SqlCommand command, SearchCriteria criteria,
                                                  String subWhere)
          {
              var sb = new StringBuilder();
              sb.AppendFormat("SELECT COUNT(*) FROM {0}", entityName);
  
              // Generate an array of the WHERE clauses to be used.
              String[] where = GetWhereSearchCriteria(entityName, criteria, command);
  
              // Add the where clauses on.
              bool first = true;
              if (subWhere != null)
              {
                  first = false;
                  sb.AppendFormat(" WHERE {0}", subWhere);
              }
  
              foreach (String clause in where)
              {
                  if (first)
                  {
                      first = false;
                      sb.AppendFormat(" WHERE {0}", clause);
                  }
                  else
                      sb.AppendFormat(" AND {0}", clause);
              }
              return sb.ToString();
          }
  
  
          /// <summary>
          /// Proves a WHERE clause based on the supplied input criteria.
          /// </summary>
          /// <param name="entityName">The entity that is being deleted from.</param>
          /// <param name="command">The SqlCommand to use.</param>
          /// <param name="criteria">The criteria for the delete statement</param>
          /// <param name="subWhere">If this is being used to generate the SQL for a sub-where, additional where clauses are included here for the delete.  Otherwise the parameter is null.</param>
          /// <returns>The SQL string.</returns>
          private static string GetDeleteWhereClause(string entityName, SqlCommand command, SearchCriteria criteria, String subWhere)
          {
              var sb = new StringBuilder();
  
              // Generate an array of the WHERE clauses to be used.
              String[] where = GetWhereSearchCriteria(entityName, criteria, command);
  
              // Add the where clauses on.
              bool first = true;
              if (subWhere != null)
              {
                  first = false;
                  sb.AppendFormat(" {0}", subWhere);
              }
  
              foreach (String clause in where)
              {
                  if (first)
                  {
                      first = false;
                      sb.AppendFormat(" {0}", clause);
                  }
                  else
                      sb.AppendFormat(" AND {0}", clause);
              }
  
              return sb.ToString();
          }
          /// <summary>
          /// Resolves the Database column name for a field name
          /// </summary>
          /// <param name="parm"></param>
          /// <returns></returns>
          private static String GetDbColumnName(EntityColumnBase parm)
          {
              String sqlColumnName;
  
              if (parm is EntityUpdateColumn<ServerEntity>)
              {
                  if (parm.FieldName.EndsWith("Key"))
                      sqlColumnName = String.Format("{0}", parm.FieldName.Replace("Key", "GUID"));
  
                  else if (parm.FieldName.EndsWith("GUID"))
                      sqlColumnName = String.Format("{0}", parm.FieldName);
  
                  else
                      sqlColumnName = String.Format("{0}", parm.FieldName);
              }
              else if (parm is EntityUpdateColumn<ServerEntityKey>)
              {
                  if (parm.FieldName.EndsWith("Key"))
                      sqlColumnName = String.Format("{0}", parm.FieldName.Replace("Key", "GUID"));
  
                  else if (parm.FieldName.EndsWith("GUID"))
                      sqlColumnName = String.Format("{0}", parm.FieldName);
  
                  else
  
gt:                     sqlColumnName = String.Format("{0}", parm.FieldName);