/// <summary>This method adds to the SQL statement any path that appears in a formula</summary>
/// <param name="onSql">This parameter has the current SQL statement</param>
/// <param name="joinType">This parameter has the type of join</param>
/// <param name="facet">First class, the beginning of the path</param>
/// <param name="onPath">Path to add to SQL statement</param>
/// <param name="processedOnPath">Path pocessed until the call of this method</param>
/// <param name="initialClass">Domain of the object valued argument, object valued filter variables or AGENT when it should be necessary</param>
/// <param name="forceLastAlias">Create almost the last alias in the sql</param>
public static string AddPath(ONSqlSelect onSql, JoinType joinType, string facet, ONPath onPath, ONPath processedOnPath, string initialClass, bool forceLastAlias)
{
// initialClass is used for Object-valued arguments, object-valued filter variables, agent instance, ...
ONPath lProcessedOnPath = new ONPath(processedOnPath);
ONPath lOnPath = new ONPath(onPath);
// Calculate processed path
string lRole = lOnPath.RemoveHead() as string;
lProcessedOnPath += lRole;
// Search Path
if (lOnPath.Count == 0)
{
string lAlias = onSql.GetAlias(facet, lProcessedOnPath);
if ((lAlias != "") && (!forceLastAlias))
return (lAlias);
}
// Create path
if (initialClass == "") // Simple paths
{
if (string.Compare(lRole, "NaveNodriza", true) == 0)
return NaveNodrizaData.AddPath(onSql, joinType, facet, lOnPath, lProcessedOnPath, "", forceLastAlias);
if (string.Compare(lRole, "Aeronave", true) == 0)
return AeronaveData.AddPath(onSql, joinType, facet, lOnPath, lProcessedOnPath, "", forceLastAlias);
if (string.Compare(lRole, "Pasajero", true) == 0)
return PasajeroData.AddPath(onSql, joinType, facet, lOnPath, lProcessedOnPath, "", forceLastAlias);
if (string.Compare(lRole, "PasajeroAeronave", true) == 0)
return PasajeroAeronaveData.AddPath(onSql, joinType, facet, lOnPath, lProcessedOnPath, "", forceLastAlias);
if (string.Compare(lRole, "Revision", true) == 0)
return RevisionData.AddPath(onSql, joinType, facet, lOnPath, lProcessedOnPath, "", forceLastAlias);
if (string.Compare(lRole, "RevisionPasajero", true) == 0)
return RevisionPasajeroData.AddPath(onSql, joinType, facet, lOnPath, lProcessedOnPath, "", forceLastAlias);
if (string.Compare(lRole, "Administrador", true) == 0)
return AdministradorData.AddPath(onSql, joinType, facet, lOnPath, lProcessedOnPath, "", forceLastAlias);
}
// Solve path with initialPath
object[] lParameters = new object[6];
lParameters[0] = onSql;
lParameters[1] = facet;
lParameters[2] = lOnPath;
lParameters[3] = lProcessedOnPath;
lParameters[4] = "";
lParameters[5] = forceLastAlias;
return ONContext.InvoqueMethod(ONContext.GetType_Data(initialClass), "AddPath", lParameters) as string;
}
private static string PasajeroAeronaveRoleAddSql(ONSqlSelect onSql, JoinType joinType, string facet, ONPath onPath, ONPath processedPath, string role, bool force, bool isLinkedTo)
{
ONPath lOnPath = new ONPath(processedPath);
lOnPath += role;
//Source table
string lAliasProcessed = onSql.GetAlias("Aeronave", processedPath, isLinkedTo);
if (lAliasProcessed == "")
{
force = false;
lAliasProcessed = onSql.CreateAlias(joinType, lAliasProcessed, CtesBD.TBL_AERONAVE, processedPath, "Aeronave", force, isLinkedTo);
}
//Target table
string lAlias = onSql.GetAlias("PasajeroAeronave", lOnPath, isLinkedTo);
if (lAlias == "")
{
force = false;
lAlias = onSql.CreateAlias(joinType, lAliasProcessed, CtesBD.TBL_PASAJEROAERONAVE, lOnPath, "PasajeroAeronave", force, isLinkedTo);
onSql.AddAliasWhere(lAlias, lAliasProcessed + "." + CtesBD.FLD_AERONAVE_ID_AERONAVE + "=" + lAlias + "." + CtesBD.FLD_PASAJEROAERONAVE_FK_AERONAVE_1);
}
//Target path
if ((((object) onPath == null) || (onPath.Count == 0)) && (string.Compare("PasajeroAeronave", facet, true) == 0) && (!force))
return lAlias;
return PasajeroAeronaveData.AddPath(onSql, joinType, facet, onPath, lOnPath, "", force, isLinkedTo);
}
private static string AeronaveFacetAddSql(JoinType joinType, ONSqlSelect onSql, ONPath onPath, bool force, bool isLinkedTo)
{
//Target table
string lAliasFacet = "";
string lAlias = onSql.CreateAlias(joinType, "", CtesBD.TBL_AERONAVE, onPath, "Aeronave", false, isLinkedTo);
// Load facet from 'Aeronave' to 'Aeronave'
lAliasFacet = onSql.GetAlias("Aeronave", onPath, isLinkedTo);
if ((lAliasFacet == "") || force)
{
if (force)
lAliasFacet = onSql.CreateAlias(joinType, "", CtesBD.TBL_AERONAVE, onPath, "Aeronave", force, isLinkedTo);
else
lAliasFacet = onSql.CreateAlias(joinType, lAlias, CtesBD.TBL_AERONAVE, onPath, "Aeronave", force, isLinkedTo);
onSql.AddAliasWhere(lAliasFacet, lAlias + "." + CtesBD.FLD_AERONAVE_ID_AERONAVE + " = " + lAliasFacet + "." + CtesBD.FLD_AERONAVE_ID_AERONAVE);
}
return lAliasFacet;
}
///<summary> This method adds the order criteria to the SQL statement </summary>
///<param name = "onSql"> This parameter represents the SQL component </param>
///<param name = "comparer"> This parameter has all the information refering to the order criteria to add to SQL statement</param>
/// <param name="startRowOid">This parameter has the OID necessary to start the search</param>
/// <param name="blockSize">This parameter represents the number of instances to be returned</param>
protected override void AddOrderCriteria(ONSqlSelect onSql, ONOrderCriteria comparer, ONOid startRowOid, int blockSize, ONPath initialPath)
{
//Initilizate StartRow
AeronaveInstance lInstance = null;
if (startRowOid != null)
{
lInstance = new AeronaveInstance(OnContext);
lInstance.Oid = startRowOid as AeronaveOid;
}
//Default OrderCriteria
if (comparer == null)
{
string lAlias = onSql.GetAlias("Aeronave", initialPath);
if (lInstance != null)
{
onSql.AddOrderBy(lAlias, CtesBD.FLD_AERONAVE_ID_AERONAVE, OrderByTypeEnumerator.Asc, lInstance.Oid.Id_AeronaveAttr);
}
else
{
onSql.AddOrderBy(lAlias, CtesBD.FLD_AERONAVE_ID_AERONAVE, OrderByTypeEnumerator.Asc, null);
}
return;
}
//Add OrderCriteria
bool lUseStartRow = (!comparer.InMemory);
foreach (ONOrderCriteriaItem lOrderCriteriaItem in comparer.OrderCriteriaSqlItem)
{
ONPath lPath = new ONPath(lOrderCriteriaItem.OnPath);
if((lInstance != null) && (lUseStartRow))
{
ONSimpleType lAttrStartRow = null;
if (lPath.Path == "")
lAttrStartRow = lInstance[lOrderCriteriaItem.Attribute] as ONSimpleType;
else
{
ONCollection lCollection = (lInstance[lPath.Path] as ONCollection);
if((lCollection != null) && (lCollection.Count > 0))
lAttrStartRow = lCollection[0][lOrderCriteriaItem.Attribute] as ONSimpleType;
}
onSql.AddOrderBy(AeronaveData.AddPath(onSql, JoinType.LeftJoin, lOrderCriteriaItem.Facet, lPath, null, lOrderCriteriaItem.DomainArgument, false), lOrderCriteriaItem.Attribute, lOrderCriteriaItem.Type, lAttrStartRow);
lUseStartRow = (lAttrStartRow != null);
}
else
onSql.AddOrderBy(AeronaveData.AddPath(onSql, JoinType.LeftJoin, lOrderCriteriaItem.Facet, lPath, null, lOrderCriteriaItem.DomainArgument, false), lOrderCriteriaItem.Attribute, lOrderCriteriaItem.Type, null);
}
return;
}
/// <summary>This method adds to the SQL statement any path that appears in a formula</summary>
/// <param name="onSql">This parameter has the current SQL statement</param>
/// <param name="joinType">This parameter has the type of join</param>
/// <param name="facet">First class, the beginning of the path</param>
/// <param name="onPath">Path to add to SQL statement</param>
/// <param name="processedOnPath">Path pocessed until the call of this method</param>
/// <param name="initialClass">Domain of the object valued argument, object valued filter variables or AGENT when it should be necessary</param>
/// <param name="forceLastAlias">Create almost the last alias in the sql</param>
/// <param name="isLinkedTo">The alias belongs to a role in a linked To element</param>
public static string AddPath(ONSqlSelect onSql, JoinType joinType, string facet, ONPath onPath, ONPath processedOnPath, string initialClass, bool forceLastAlias, bool isLinkedTo)
{
// initialClass is used for Object-valued arguments, object-valued filter variables, agent instance, ...
ONPath lProcessedOnPath = new ONPath(processedOnPath);
ONPath lOnPath = new ONPath(onPath);
bool lOnPathExist = true;
object[] lParameters = new object[8];
if (initialClass != "")
{
string lRol = lOnPath.RemoveHead();
lProcessedOnPath += lRol;
// Solve path with initialPath
lParameters[0] = onSql;
lParameters[1] = joinType;
lParameters[2] = facet;
lParameters[3] = lOnPath;
lParameters[4] = lProcessedOnPath;
lParameters[5] = "";
lParameters[6] = forceLastAlias;
lParameters[7] = isLinkedTo;
return ONContext.InvoqueMethod(ONContext.GetType_Data(initialClass), "AddPath", lParameters) as string;
}
// Search max solved path
ONPath lMaxSolvedPath = new ONPath(onPath);
string lMaxSolvedPathDomain = facet;
while ((lMaxSolvedPath.Count > 0) && (onSql.GetAlias(lMaxSolvedPathDomain, lProcessedOnPath + lMaxSolvedPath, isLinkedTo) == ""))
{
lMaxSolvedPath.RemoveTail();
lMaxSolvedPathDomain = GetTargetClassName(lMaxSolvedPath);
}
if (lMaxSolvedPath.Count > 0)
{
lProcessedOnPath += lMaxSolvedPath;
for (int i = 0; i < lMaxSolvedPath.Count; i++)
lOnPath.RemoveHead();
lParameters[0] = onSql;
lParameters[1] = joinType;
lParameters[2] = facet;
lParameters[3] = lOnPath;
lParameters[4] = lProcessedOnPath;
lParameters[5] = "";
lParameters[6] = forceLastAlias;
lParameters[7] = isLinkedTo;
return ONContext.InvoqueMethod(ONContext.GetType_Data(lMaxSolvedPathDomain), "AddPath", lParameters) as string;
}
// Create inheritance path
if ((onPath == null) || (onPath.Count == 0))
{
if (forceLastAlias)
return AeronaveFacetAddSql(joinType, onSql, processedOnPath, forceLastAlias, isLinkedTo);
if ((processedOnPath == null) || (processedOnPath.Count == 0))
return (onSql.CreateAlias(joinType, "", CtesBD.TBL_AERONAVE, null, "Aeronave", false, isLinkedTo));
else
return (onSql.CreateAlias(joinType, "", CtesBD.TBL_AERONAVE, processedOnPath, "Aeronave", false, isLinkedTo));
}
// Calculate processed path
string lRole = lOnPath.RemoveHead() as string;
lProcessedOnPath += lRole;
// Search Path
if (lOnPath.Count == 0)
{
string lAlias = onSql.GetAlias(facet, lProcessedOnPath, isLinkedTo);
if ((lAlias != "") && (!forceLastAlias))
return (lAlias);
else
lOnPathExist = false;
}
else
{
string lTargetClass = GetTargetClassName(new ONPath(lRole));
// Agent & OV Argument Control
if ((lTargetClass == "") && (initialClass != ""))
lTargetClass = initialClass;
string lAlias = onSql.GetAlias(lTargetClass, lProcessedOnPath, isLinkedTo);
if (lAlias == "")
lOnPathExist = false;
}
// Create path
if (string.Compare(lRole, "PasajeroAeronave", true) == 0)
{
if (lOnPathExist)
return PasajeroAeronaveData.AddPath(onSql, joinType, facet, lOnPath, lProcessedOnPath, "", forceLastAlias, isLinkedTo);
else
return PasajeroAeronaveRoleAddSql(onSql, joinType, facet, lOnPath, processedOnPath, lRole, forceLastAlias, isLinkedTo);
}
initialClass = "Aeronave";
// Solve path with initialPath
lParameters[0] = onSql;
lParameters[1] = joinType;
lParameters[2] = facet;
lParameters[3] = lOnPath;
lParameters[4] = lProcessedOnPath;
lParameters[5] = "";
lParameters[6] = forceLastAlias;
lParameters[7] = isLinkedTo;
return ONContext.InvoqueMethod(ONContext.GetType_Data(initialClass), "AddPath", lParameters) as string;
}
/// <summary>
/// Generate Select Sql
/// </summary>
/// <returns>Sql string</returns>
public override string GenerateSQL(out ArrayList sqlParameters)
{
StringBuilder lTextBuilder = new StringBuilder();
sqlParameters = new ArrayList();
#region Select
StringBuilder lTextSelect = new StringBuilder();
foreach (string lSelectAttribute in mSelectAttributes)
{
if (lTextSelect.Length > 0)
{
lTextSelect.Append(", ");
}
lTextSelect.Append(lSelectAttribute);
}
sqlParameters.AddRange(mSelectParametersItem);
lTextBuilder.Append("SELECT ");
if (mIsDistinct)
{
lTextBuilder.Append("DISTINCT ");
}
lTextBuilder.Append(lTextSelect);
#endregion
StringBuilder lTextWhereJoin = new StringBuilder();
#region From
bool lGenerateFrom = false;
if (mFrom.Count > 0)
{
foreach (ONSqlAlias lOnSqlAlias in mFrom.Values)
{
if ((lOnSqlAlias.FatherAlias == null) || ((SuperQuery != null) && (SuperQuery.GetAlias(lOnSqlAlias.FatherAlias.Table, lOnSqlAlias.FatherAlias.OnPath) != "")))
{
lGenerateFrom = true;
break;
}
}
}
if (lGenerateFrom)
{
StringBuilder lTextFrom = new StringBuilder();
foreach (ONSqlAlias lOnSqlAlias in mFrom.Values)
{
if ((lOnSqlAlias.FatherAlias == null) || ((SuperQuery != null) && (SuperQuery.GetOnSqlAlias(lOnSqlAlias.FatherAlias.Alias) != null)))
{
foreach (string lConjunction in lOnSqlAlias.Wheres)
{
mWhereConjuntion.Add(lConjunction);
}
if (lTextFrom.Length > 0)
{
lTextFrom.Append(", ");
}
lTextFrom.Append(lOnSqlAlias.Table);
lTextFrom.Append(" ");
lTextFrom.Append(lOnSqlAlias.Alias);
GenerateSQL_Join(lOnSqlAlias, lTextFrom, lTextWhereJoin);
}
}
lTextBuilder.Append(" FROM ");
lTextBuilder.Append(lTextFrom);
}
#endregion
#region Where
if ((mOrderByDisjuntion.Count > 0) || (mWhereConjuntion.Count > 0) || (mWhereDisjunction.Count > 0) || (lTextWhereJoin.Length > 0))
{
StringBuilder lTextWhere = new StringBuilder();
#region Left Join
if (lTextWhereJoin.Length > 0)
{
if (lTextWhere.Length > 0)
{
lTextWhere.Append(" AND ");
}
if ((lTextWhereJoin.Length > 1) || (mOrderByDisjuntion.Count > 0) || (mWhereConjuntion.Count > 0))
{
lTextWhere.Append("(");
}
lTextWhere.Append(lTextWhereJoin);
if ((lTextWhereJoin.Length > 1) || (mOrderByDisjuntion.Count > 0) || (mWhereConjuntion.Count > 0))
{
lTextWhere.Append(")");
}
}
#endregion
#region Order by (where)
if (mOrderByDisjuntion.Count > 0)
{
if (lTextWhere.Length > 0)
{
lTextWhere.Append(" AND ");
}
if ((lTextWhereJoin.Length > 0) || (mWhereConjuntion.Count > 0) || (mWhereDisjunction.Count > 0))
{
lTextWhere.Append("(");
}
StringBuilder lTextWhereTemp = new StringBuilder();
for (int i = 0; i < mOrderByDisjuntion.Count; i++)
{
if (lTextWhereTemp.Length != 0)
{
lTextWhereTemp.Append(" OR ");
}
if (mOrderByDisjuntion.Count > 1)
{
lTextWhereTemp.Append("(");
}
lTextWhereTemp.Append(mOrderByDisjuntion[i]);
if (mOrderByDisjuntion.Count > 1)
{
lTextWhereTemp.Append(")");
}
}
lTextWhere.Append(lTextWhereTemp);
if ((lTextWhereJoin.Length > 0) || (mWhereConjuntion.Count > 0) || (mWhereDisjunction.Count > 0))
{
lTextWhere.Append(")");
}
}
for (int i = 0; i < OrderByParameters.Count; i++)
{
for (int j = 0; j <= i; j++)
{
sqlParameters.Add(OrderByParameters[j]);
}
}
sqlParameters.AddRange(OrderByParameters);
#endregion
#region Where
if (mWhereConjuntion.Count > 0)
{
foreach (string lItem in mWhereConjuntion)
{
if (lTextWhere.Length > 0)
{
lTextWhere.Append(" AND ");
}
if ((lTextWhereJoin.Length > 0) || (mOrderByDisjuntion.Count > 0) || (mWhereConjuntion.Count > 1))
{
lTextWhere.Append("(");
}
lTextWhere.Append(lItem);
if ((lTextWhereJoin.Length > 0) || (mOrderByDisjuntion.Count > 0) || (mWhereConjuntion.Count > 1))
{
lTextWhere.Append(")");
}
}
}
sqlParameters.AddRange(mWhereParametersItem);
#endregion
#region Where (Disjunctions)
if (mWhereDisjunction.Count > 0)
{
StringBuilder lTextWhereDisjunctions = new StringBuilder();
foreach (string lItem in mWhereDisjunction)
{
if (lTextWhereDisjunctions.Length > 0)
{
lTextWhereDisjunctions.Append(" OR ");
}
if (mWhereDisjunction.Count > 1)
{
lTextWhereDisjunctions.Append("(");
}
lTextWhereDisjunctions.Append(lItem);
if (mWhereDisjunction.Count > 1)
{
lTextWhereDisjunctions.Append(")");
}
}
// Add the disjunctions into the sentence as a conjunction
if (lTextWhere.Length > 0)
{
lTextWhere.Append(" AND (").Append(lTextWhereDisjunctions).Append(")");
}
else
{
lTextWhere.Append(lTextWhereDisjunctions);
}
}
sqlParameters.AddRange(mWhereDisjParametersItem);
#endregion
lTextBuilder.Append(" WHERE ");
lTextBuilder.Append(lTextWhere);
}
#endregion
#region Order by
if (mOrderBy.Count > 0)
{
StringBuilder lTextOrderBy = new StringBuilder();
foreach (string lItem in mOrderBy)
{
if (lTextOrderBy.Length > 0)
{
lTextOrderBy.Append(", ");
}
lTextOrderBy.Append(lItem);
}
lTextBuilder.Append(" ORDER BY ");
lTextBuilder.Append(lTextOrderBy);
}
#endregion
// Solve Subqueries
lTextBuilder = GenerateSQL_SubQueries(lTextBuilder, ref sqlParameters);
return(lTextBuilder.ToString());
}
private static string NaveNodrizaFacetAddSql(JoinType joinType, ONSqlSelect onSql, ONPath onPath, bool force, bool isLinkedTo)
{
//Target table
string lAliasFacet = "";
string lAlias = onSql.CreateAlias(joinType, "", CtesBD.TBL_NAVENODRIZA, onPath, "NaveNodriza", false, isLinkedTo);
// Load facet from 'NaveNodriza' to 'NaveNodriza'
lAliasFacet = onSql.GetAlias("NaveNodriza", onPath, isLinkedTo);
if ((lAliasFacet == "") || force)
{
if (force)
lAliasFacet = onSql.CreateAlias(joinType, "", CtesBD.TBL_NAVENODRIZA, onPath, "NaveNodriza", force, isLinkedTo);
else
lAliasFacet = onSql.CreateAlias(joinType, lAlias, CtesBD.TBL_NAVENODRIZA, onPath, "NaveNodriza", force, isLinkedTo);
onSql.AddAliasWhere(lAliasFacet, lAlias + "." + CtesBD.FLD_NAVENODRIZA_ID_NAVENODRIZA + " = " + lAliasFacet + "." + CtesBD.FLD_NAVENODRIZA_ID_NAVENODRIZA);
}
return lAliasFacet;
}
private static string PasajeroFacetAddSql(JoinType joinType, ONSqlSelect onSql, ONPath onPath, bool force, bool isLinkedTo)
{
//Target table
string lAliasFacet = "";
string lAlias = onSql.CreateAlias(joinType, "", CtesBD.TBL_PASAJERO, onPath, "Pasajero", false, isLinkedTo);
// Load facet from 'Pasajero' to 'Pasajero'
lAliasFacet = onSql.GetAlias("Pasajero", onPath, isLinkedTo);
if ((lAliasFacet == "") || force)
{
if (force)
lAliasFacet = onSql.CreateAlias(joinType, "", CtesBD.TBL_PASAJERO, onPath, "Pasajero", force, isLinkedTo);
else
lAliasFacet = onSql.CreateAlias(joinType, lAlias, CtesBD.TBL_PASAJERO, onPath, "Pasajero", force, isLinkedTo);
onSql.AddAliasWhere(lAliasFacet, lAlias + "." + CtesBD.FLD_PASAJERO_ID_PASAJERO + " = " + lAliasFacet + "." + CtesBD.FLD_PASAJERO_ID_PASAJERO);
}
return lAliasFacet;
}
