public override string ConvertSqlSelectToOrderBy(string sql, ColumnEnumList GroupByParams)
{
if (GroupByParams != null && GroupByParams.Count > 0)
{
return(sql + " ORDER BY " + GroupByParams.getOrderByParams(this));
}
else
{
return(sql);
}
}
/// <summary>
/// Permite obtener los registros de varias tablas filtrando por diferentes campos
/// </summary>
/// <param name="nDataTable">Resultado de la consulta</param>
/// <param name="nDataBaseCod">Base de datos a la que pertenece la tabla</param>
/// <param name="nSchemaName">Esquema al que pertenece la tabla</param>
/// <param name="nDataTableName">Nombre de la tabla</param>
/// <param name="nKeys">Llave primaria del registro</param>
/// <param name="nTableRelationList">Lista de relaciones con otras tablas</param>
/// <param name="nMaxRows">Número máximo de registros a retornar</param>
/// <param name="nResult">Define si la instrucción se ejecutó correctamente o no</param>
/// <param name="nException">Exepción ocurrida al ejecutar la instrucción</param>
public void DBQueryFill(ref DataTable nDataTable, string nDataBaseCod, string nSchemaName, string nDataTableName, List <Parameter> nKeys, TableRelationCollection nTableRelationList, int nMaxRows, ColumnEnumList nOrderByParams)
{
try
{
bool nResult = true;
Exception nException = null;
int index = 0;
//Ejecutar la consulta principal
var primaryRelations = new TableRelationCollection();
while (index < nTableRelationList.Count)
{
if (nTableRelationList[index].ForeignDataBaseCod != nDataBaseCod)
{
break;
}
primaryRelations.Add((TableRelation)nTableRelationList[index].Clone());
index++;
}
ManagerPool[nDataBaseCod].DataBase.DBQueryFill(ref nDataTable, "form", nSchemaName, nDataTableName, nKeys, primaryRelations, nMaxRows, nOrderByParams, out nResult, out nException);
if (!nResult)
{
throw nException;
}
//TODO: Agregar consultas a relaciones a base de datos externas
while (index < nTableRelationList.Count)
{
index++;
}
}
catch (Exception ex)
{
throw new Exception("Error al recuperar los registros de la tabla [" + nDataBaseCod + "] " + nSchemaName + "." + nDataTableName + ", " + ex.Message, ex);
}
}
/// <summary>
/// Construye la sentencia SQL para obtener los registros de varias tablas filtrando por diferentes campos
/// </summary>
/// <param name="nTableAlias">Alias usado para la tabla principal</param>
/// <param name="nSchemaName">Esquema al que pertenece la tabla</param>
/// <param name="nDataTableName">Nombre de la tabla</param>
/// <param name="nKeys">Campos que componen la llave primaria del registro</param>
/// <param name="nTableRelationList">Lista de relaciones con otras tablas</param>
/// <param name="nMaxRows">Número máximo de registros a retornar, 0 para devolver todas la filas</param>
/// <param name="nOrderByParams">Parametros de ordenación, null para no rodenar</param>
/// <returns>Sentencia SQL</returns>
public string SqlQuery(string nTableAlias, string nSchemaName, string nDataTableName, List <Parameter> nKeys, TableRelationCollection nTableRelationList, int nMaxRows, ColumnEnumList nOrderByParams)
{
try
{
string filters = "";
string sql = "";
for (int i = 0; i < nKeys.Count; i++)
{
if (nKeys[i].Value != null && !DBNulls.IsNull(nKeys[i].Value))
{
if (filters == "")
{
filters += " Where ";
}
else
{
filters += " And ";
}
filters += "main." + this.DataBase.FormatToDatabaseColumnName(nKeys[i].Name) + " " + this.DataBase.FormatToComparisonOperator(nKeys[i]) + " " + this.DataBase.FormatToDatabaseStringValue(nKeys[i], true);
}
}
//Crear Joins
var tablesToJoin = new TableRelationCollection();
if (nTableRelationList != null)
{
tablesToJoin = nTableRelationList;
}
var aliasList = new NameValueCollection();
int aliasIndex = 0;
//Crear las lista de alias
aliasList.Set(nTableAlias + nSchemaName + nDataTableName, "main");
foreach (var columnToJoin in nTableRelationList)
{
string alias = aliasList.Get(columnToJoin.ForeignRelationAlias + columnToJoin.ForeignSchemaName + columnToJoin.ForeignTableName);
if (alias == null)
{
alias = " tab" + (++aliasIndex);
aliasList.Set(columnToJoin.ForeignRelationAlias + columnToJoin.ForeignSchemaName + columnToJoin.ForeignTableName, alias);
}
}
var joinList = new NameValueCollection();
var selectColumnList = new StringCollection();
selectColumnList.Add("main.*");
foreach (var columnToJoin in tablesToJoin)
{
string alias = aliasList.Get(columnToJoin.ForeignRelationAlias + columnToJoin.ForeignSchemaName + columnToJoin.ForeignTableName);
string pairJoinAlias = aliasList.Get(columnToJoin.MainRelationAlias + columnToJoin.MainSchemaName + columnToJoin.MainTableName);
if (pairJoinAlias == null)
{
throw new Exception("No se encontró la tabla para crear el join, " + columnToJoin.MainSchemaName + "." + columnToJoin.MainTableName);
}
string strJoin = joinList.Get(columnToJoin.ForeignRelationAlias + columnToJoin.ForeignSchemaName + columnToJoin.ForeignTableName);
if (strJoin == null)
{
strJoin = " " + columnToJoin.GetJoinString() + " " + this.DataBase.FormatToDatabaseTableName(columnToJoin.ForeignSchemaName, columnToJoin.ForeignTableName) + " as " + alias +
" on " + alias + "." + this.DataBase.FormatToDatabaseColumnName(columnToJoin.ForeignColumnName) + " = " + pairJoinAlias + "." + this.DataBase.FormatToDatabaseColumnName(columnToJoin.MainColumnName);
}
else
{
strJoin += " and " + alias + "." + this.DataBase.FormatToDatabaseColumnName(columnToJoin.ForeignColumnName) + " = " + pairJoinAlias + "." + this.DataBase.FormatToDatabaseColumnName(columnToJoin.MainColumnName);
}
if (columnToJoin.ForeignFilterTextValue != "")
{
var columnsToFilter = columnToJoin.ForeignColumnsToResult.Split(',');
foreach (string textColumn in columnsToFilter)
{
strJoin += " and " + alias + "." + this.DataBase.FormatToDatabaseColumnName(textColumn) + " " + this.DataBase.FormatToComparisonOperator(columnToJoin.ForeignFilterTextValue) + " " + this.DataBase.FormatStringToDatabaseStringValue(columnToJoin.ForeignFilterTextValue, true);
}
}
joinList.Set(columnToJoin.ForeignRelationAlias + columnToJoin.ForeignSchemaName + columnToJoin.ForeignTableName, strJoin);
var columnsToSelect = columnToJoin.MainColumnsToResult.Split(',');
foreach (string textColumn in columnsToSelect)
{
if (pairJoinAlias != "main" && textColumn.Trim() != "")
{
string colName = pairJoinAlias + "." + this.DataBase.FormatToDatabaseColumnName(textColumn);
if (!selectColumnList.Contains(", " + colName))
{
selectColumnList.Add(", " + colName);
}
}
}
columnsToSelect = columnToJoin.ForeignColumnsToResult.Split(',');
foreach (string textColumn in columnsToSelect)
{
if (textColumn.Trim() != "")
{
string colName = alias + "." + this.DataBase.FormatToDatabaseColumnName(textColumn);
if (!selectColumnList.Contains(", " + colName))
{
selectColumnList.Add(", " + colName);
}
}
}
}
string joinString = "";
foreach (var join in joinList)
{
joinString += joinList[join.ToString()];
}
string selectString = "";
foreach (var sel in selectColumnList)
{
selectString += sel;
}
sql = "Select " + selectString + " From " + this.DataBase.FormatToDatabaseTableName(nSchemaName, nDataTableName) + " As main " + joinString + filters;
sql = this.DataBase.ConvertSqlSelectToMaxRows(sql, nMaxRows);
sql = this.DataBase.ConvertSqlSelectToOrderBy(sql, nOrderByParams);
this.DataBase.LastQuery = sql;
return(sql);
}
catch (Exception ex)
{
throw new Exception("No fue posible obtener la sentencia de consulta, " + ex.Message);
}
}
/// <summary>
/// Construye la sentencia SQL para obtener los registro de una tabla filtrando por diferentes campos
/// </summary>
/// <param name="nSchemaName">Esquema al que pertenece la tabla</param>
/// <param name="nDataTableName">Nombre de la tabla</param>
/// <param name="nKeys">Campos que componen la llave primaria del registro</param>
/// <param name="nMaxRows">Número máximo de registros a retornar, 0 para devolver todas la filas</param>
/// <param name="nOrderByParams">Parametros de ordenación, null para no rodenar</param>
/// <returns>Sentencia SQL</returns>
public string SqlFilter(string nSchemaName, string nDataTableName, List <Parameter> nKeys, int nMaxRows, ColumnEnumList nOrderByParams)
{
try
{
string filters = "";
string filterGroupString = "";
string sql = "";
int itemsByGroup = 0;
int groupCount = GetGroupCount(nKeys);
for (int group = 1; group <= groupCount; group++)
{
filterGroupString = "";
itemsByGroup = 0;
for (int i = 0; i < nKeys.Count; i++)
{
if (nKeys[i].FilterGroup == group)
{
if (nKeys[i].Value != null && !DBNulls.IsNull(nKeys[i].Value))
{
if (filterGroupString == "")
{
if (filters != "")
{
filterGroupString = " And (";
}
else
{
filterGroupString = "Where (";
}
itemsByGroup++;
}
else
{
filterGroupString += " " + nKeys[i].FilterOption.ToString() + " ";
}
filterGroupString += this.DataBase.FormatToDatabaseColumnName(nKeys[i].Name) + " " + this.DataBase.FormatToComparisonOperator(nKeys[i]) + " " + this.DataBase.FormatToDatabaseStringValue(nKeys[i], true);
}
}
}
if (filterGroupString != "")
{
filters = filters + filterGroupString + ")";
}
}
sql = "Select * From " + this.DataBase.FormatToDatabaseTableName(nSchemaName, nDataTableName) + filters;
sql = this.DataBase.ConvertSqlSelectToMaxRows(sql, nMaxRows);
sql = this.DataBase.ConvertSqlSelectToOrderBy(sql, nOrderByParams);
this.DataBase.LastQuery = sql;
return(sql);
}
catch (Exception ex)
{
throw new Exception("No fue posible obtener la sentencia de consulta, " + ex.Message);
}
}
