/// <summary>
/// Main Constructor
/// </summary>
/// <param name="databaseFactory"></param>
public AbstractQueryBuilder(Gale.Db.IDataActions databaseFactory, Type TModel)
{
//Setting the Database Factory Type
this._databaseFactory = databaseFactory;
//Entity Type for the CRUD
Type entityType = TModel;
//Field List
this._reflectedModel = ReflectModel(entityType);
}
/// text
public static Models.Config GetConfiguration(String NameConfiguration, Gale.Db.IDataActions dataActions)
{
using (Gale.Db.DataService svc = new Gale.Db.DataService("PA_MAE_SEL_Configuracion"))
{
svc.Parameters.Add("@CONF_Nombre", NameConfiguration);
Gale.Db.EntityRepository rep = dataActions.ExecuteQuery(svc);
Models.Config result = rep.GetModel <Models.Config>().FirstOrDefault();
return(result);
}
}
/// text
public static Models.Config GetConfiguration(Nullable <Guid> token, Gale.Db.IDataActions dataActions)
{
using (Gale.Db.DataService svc = new Gale.Db.DataService("PA_MAE_SEL_Configuracion"))
{
if (token != null)
{
svc.Parameters.Add("@DOCU_Token", token);
}
svc.Parameters.Add("@CONF_Nombre", DBNull.Value);
Gale.Db.EntityRepository rep = dataActions.ExecuteQuery(svc);
Models.Config result = rep.GetModel <Models.Config>().FirstOrDefault();
return(result);
}
}
public HttpQueryBuilder(Gale.Db.IDataActions databaseFactory, HttpRequestMessage request, GQLConfiguration configuration)
: base(databaseFactory, request, configuration)
{
System.Collections.Specialized.NameValueCollection query = System.Web.HttpUtility.ParseQueryString(request.RequestUri.Query);
//------------------------------------------------------------------------------------------
//--[ ORDER BY PARSER (FOR SQL SERVER WE NEED TO ENGAGE FIRST)
this.RegisterParser <OData.Builders.SQLServer.Parsers.OrderBy>(this.Kql);
//------------------------------------------------------------------------------------------
//------------------------------------------------------------------------------------------
//--[ SELECT PARSER
this.RegisterParser <OData.Builders.SQLServer.Parsers.Select>(this.Kql);
//------------------------------------------------------------------------------------------
//------------------------------------------------------------------------------------------
//--[ FROM PARSER (Send null query , the model , has converted automatically by constraint's)
this.RegisterParser <OData.Builders.SQLServer.Parsers.From>(this.Kql);
//------------------------------------------------------------------------------------------
//------------------------------------------------------------------------------------------
//--[ WHERE PARSER (Filter's)
this.RegisterParser <OData.Builders.SQLServer.Parsers.Where>(this.Kql);
//------------------------------------------------------------------------------------------
//------------------------------------------------------------------------------------------
//--[ APPEND THE ANSI SQL STATEMENTS (SELECT , FROM , WHERE, ORDER BY)
String orderBy = "";
this.OnExecutedParser += new ExecutedParserEventHandler((args) =>
{
#region OVERRIDE FRAGMENT
//ORDER BY APPEND (CAPTURE AND SET NULL)
if (args.Parser.GetType() == typeof(OData.Builders.SQLServer.Parsers.OrderBy))
{
orderBy = args.ResultQueryFragment;
args.ResultQueryFragment = "";
}
//SELECT APPEND
if (args.Parser.GetType() == typeof(OData.Builders.SQLServer.Parsers.Select))
{
//INCLUDE PAGINATOR SQL SENTENCE
String format = String.Format("SELECT ROW_NUMBER() OVER (ORDER BY {0}) AS ROWNUM, {1}",
orderBy,
args.ResultQueryFragment
);
args.ResultQueryFragment = format;;
}
//WHERE APPEND
if (args.Parser.GetType() == typeof(OData.Builders.SQLServer.Parsers.Where))
{
if (args.ResultQueryFragment.Length > 0)
{
args.ResultQueryFragment = String.Concat(" WHERE ", args.ResultQueryFragment);
}
}
#endregion
});
//------------------------------------------------------------------------------------------
//------------------------------------------------------------------------------------------
//--[ REGISTER OPERATOR'S (CAN BE EXECUTED IN ANY PARSER FOR THE "CALLOPERATOR" METHOD)
IEnumerable <Type> operators = (from t in this.GetType().Assembly.GetTypes()
where t.IsSubclassOf(typeof(OData.Builders.SQLServer.Operators.Operator))
select t);
foreach (Type Qoperator in operators)
{
System.Reflection.MethodInfo register = this.GetType().GetMethod("RegisterOperator").MakeGenericMethod(Qoperator);
register.Invoke(this, new object[] { });
}
//------------------------------------------------------------------------------------------
}
private Result _Execute(String query, Gale.Db.IDataActions databaseFactory)
{
System.Diagnostics.Stopwatch timer = new System.Diagnostics.Stopwatch();
timer.Start();
//-------------------------------------------------------------------------------------
//---[ DATABASE CALL
using (Gale.Db.DataService svc = new Gale.Db.DataService(query))
{
System.Data.DataTable db_data = null;
System.Data.DataTable db_pagination = null;
Gale.Db.EntityRepository rep = null;
try
{
//Create the repository
rep = this.DatabaseFactory.ExecuteSql(svc);
db_data = rep.GetRawTable(0);
db_pagination = rep.GetRawTable(1);
}
catch (System.Exception ex)
{
string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
throw new Gale.Exception.GaleException("API_DB_ERROR", message);
}
//--------[ GET ALL DATA IN AN ARRAY
var data = new List <List <Object> >();
SortedList <int, Field> ordinalsFields = new SortedList <int, Field>();
var columns_count = db_data.Columns.Count;
#region ORDINAL COLUMN SEARCH
//SEARCH FOR ORDINAL'S
foreach (Field field in _reflectedModel.SelectedFields)
{
for (var column_index = 0; column_index < columns_count; column_index++)
{
if (field.Key.ToLower() == db_data.Columns[column_index].ColumnName.ToLower())
{
ordinalsFields.Add(db_data.Columns[column_index].Ordinal, field);
break;
}
}
}
#endregion
if (db_pagination.Rows.Count != 1)
{
throw new Gale.Exception.GaleException("API003");
}
int total = Convert.ToInt32(db_pagination.Rows[0]["total"]);
#region TABLE FORMAT
//Save all data from the foreign table for the descriptor's
SortedList <Type, Gale.Db.IEntityTable> _foreignTableDatas = new SortedList <Type, Gale.Db.IEntityTable>();
//Starting Fetching Data
for (var row_index = 0; row_index < db_data.Rows.Count; row_index++)
{
List <Object> item = new List <Object>();
foreach (int ordinal in ordinalsFields.Keys)
{
Field field = ordinalsFields[ordinal];
Object db_value = db_data.Rows[row_index][ordinal];
if (db_value is DateTime)
{
db_value = DateTime.SpecifyKind((DateTime)db_value, DateTimeKind.Local);
}
//If is FK , try to get the Descriptor, if not have descriptor, send Encripted Value :S
if (field.Specification == Field.SpecificationEnum.Fk)
{
Table table = _reflectedModel.Constraints.First(constraint => constraint.ThisField == field).Table;
if (table.Descriptor != null)
{
Gale.Db.IEntityTable tableData;
_foreignTableDatas.TryGetValue(table.Type, out tableData);
#region CREATE DATA TABLE FROM THE CURRENT SOURCE IF NOT EXIST YET
if (tableData == null)
{
//Create Constraint Table Data
System.Reflection.MethodInfo baseMethod = (from t in rep.GetType().GetMethods()
where
t.GetGenericArguments().Count() > 0 &&
t.Name == "GetModel" && t.GetParameters().Count() == 0
select t).FirstOrDefault();
System.Reflection.MethodInfo GetModelMethod = baseMethod.MakeGenericMethod(table.Type);
tableData = (Gale.Db.IEntityTable)GetModelMethod.Invoke(rep, null);
_foreignTableDatas.Add(table.Type, tableData);
}
#endregion
//GET Constraint Function Expression to Get the FK Descriptor
Object _item = tableData.GetType().GetMethod("get_Item").Invoke(tableData, new object[] { row_index });
db_value = table.Descriptor.DynamicInvoke(_item).ToString();
}
}
item.Add(db_value); //Column Value
}
data.Add(item);
}
#endregion
timer.Stop();
var response = new Gale.REST.Queryable.Primitive.Result(total, timer.Elapsed, _reflectedModel.SelectedFields, data);
return(response);
}
//-------------------------------------------------------------------------------------
}
public AbstractQueryBuilder(Gale.Db.IDataActions databaseFactory)
: base(databaseFactory, typeof(TModel))
{
}
public HttpQueryBuilder(Gale.Db.IDataActions databaseFactory, HttpRequestMessage request, GQLConfiguration configuration)
: base(databaseFactory)
{
Request = request;
System.Collections.Specialized.NameValueCollection query = System.Web.HttpUtility.ParseQueryString(Request.RequestUri.Query);
Kql = new GQLConfiguration();
#region OFFSET
if (query.AllKeys.Contains("$offset"))
{
int _offset = 0;
int.TryParse(query["$offset"], out _offset);
Kql.offset = _offset;
}
#endregion
#region LIMIT
if (query.AllKeys.Contains("$limit"))
{
int _limit = 0;
int.TryParse(query["$limit"], out _limit);
if (_limit > 0)
{
Kql.limit = _limit;
}
}
else
{
//default
Kql.limit = 10;
}
#endregion
#region ORDER BY
if (query.AllKeys.Contains("$orderBy"))
{
String[] values = query["$orderBy"].Split(' ');
Exception.GaleException.Guard(() => { return(values.Length != 2); }, "API017");
String fieldName = values[0].ToLower();
String order = values[1].Trim().ToLower();
Exception.GaleException.Guard(() => { return(order != "asc" && order != "desc"); }, "API016");
this.Kql.orderBy = new GQLConfiguration.OrderBy()
{
name = fieldName,
order = (order == "asc") ? GQLConfiguration.OrderBy.orderEnum.asc : GQLConfiguration.OrderBy.orderEnum.desc
};
}
#endregion
#region SELECT
if (query.AllKeys.Contains("$select"))
{
string fields = query["$select"].Trim();
if (fields.Length > 0)
{
List <string> selectedFields = fields.Split(',').ToList();
Exception.GaleException.Guard(() => { return(selectedFields.Count == 0); }, "API011");
foreach (var fieldName in selectedFields)
{
Exception.GaleException.Guard(() => { return(fieldName.Trim().Length == 0); }, "API011");
}
this.Kql.fields = selectedFields;
}
}
#endregion
#region FILTERS
if (query.AllKeys.Contains("$filter"))
{
List <string> filters = query["$filter"].Trim().Split(',').ToList();
foreach (string filterString in filters)
{
string text = filterString;
Func <string> reduceFragment = new Func <string>(() =>
{
string _fragment;
int space_ordinal = text.IndexOf(" ");
//Gale Exception
Exception.GaleException.Guard(() => { return(space_ordinal == 0); }, "API011");
_fragment = text.Substring(0, space_ordinal);
text = text.Substring(space_ordinal + 1); //Reduce Filter
return(_fragment);
});
//FILTER FORMAT => {field} {operatorAlias} {value}
string field = reduceFragment();
string operatorAlias = reduceFragment();
string value = text;
this.Kql.filters.Add(new GQLConfiguration.Filter()
{
field = field,
operatorAlias = operatorAlias,
value = text
});
}
}
#endregion
#region SETUP Manual GQLConfiguration
if (configuration != null)
{
configuration.filters.ForEach((filter) =>
{
this.Kql.filters.Add(filter);
});
configuration.fields.ForEach((fields) =>
{
this.Kql.fields.Add(fields.ToLower());
});
if (configuration.limit > 0)
{
this.Kql.limit = configuration.limit;
}
if (configuration.offset > 0)
{
this.Kql.offset = configuration.offset;
}
if (configuration.orderBy != null && !String.IsNullOrEmpty(configuration.orderBy.name))
{
this.Kql.orderBy = configuration.orderBy;
}
}
#endregion
}