public FilterExecution GetOrInsertFilterExecution(string controller)
{
FilterExecution filterExecution = null;
Guid idUser = new Guid(base.ApplicationUserCurrent.UserId);
filterExecution = _filterExecutionBusiness.GetFilterExecution(controller, idUser);
if (null == filterExecution)
{
var filterController = _filterControllerBusiness.GetControllerById(controller);
if (null != filterController)
{
try
{
filterExecution = new FilterExecution();
filterExecution.IdUser = idUser;
filterExecution.FilterControllers = filterController;
filterExecution.IdFilterController = filterController.Id;
_filterExecutionBusiness.InsertOrUpdate(filterExecution);
}
catch (ExceptionMardis ex)
{
//logg de aplicacion
_logger.LogError(ex.Message);
filterExecution = null;
}
}
else
{
//logg de aplicacion
}
}
else
{
filterExecution.FilterExecutionDetails = _filterExecutionDetailBusiness.GetExecutionDetailByExecution(filterExecution.Id);
}
return(filterExecution);
}
public List <dynamic> GetResults(string idFilterExecution, string propertyName, string propertyValue)
{
Guid idAccount = base.ApplicationUserCurrent.AccountId;
FilterExecution filterExecution = _filterExecutionBusiness.GetFilterExecution(new Guid(idFilterExecution));
StringBuilder sql = new StringBuilder();
StringBuilder sqlReference = new StringBuilder();
string referencesTablesSelect = "";
var retObject = new List <dynamic>();
if (null != filterExecution)
{
filterExecution.FilterExecutionDetails =
_filterExecutionDetailBusiness.GetExecutionDetailByExecution(filterExecution.Id);
filterExecution.DateInit = DateTime.Now;
string nameTableBase = filterExecution.FilterControllers.NameTable;
int numberParameter = 0;
List <FilterTable> itemTables = _filterTableBusiness.GetFilterTable(filterExecution.FilterControllers.Id);
foreach (var itemReferences in itemTables)
{
if (CFilter.AffirmationHasRelation.Equals(itemReferences.HasRelation))
{
string tableReference = itemReferences.TableInitial;
List <FilterField> lstItemFilterField =
_filterFieldsBusiness.GetFilterFieldByTable(itemReferences.Id);
foreach (var tempField in lstItemFilterField)
{
referencesTablesSelect += "," + itemReferences.TableInitial + "." + tempField.Field + " as " + itemReferences.TableInitial + tempField.Field;
}
var aliasTable = "";
if (itemReferences.FieldMainTable.IndexOf(".", StringComparison.Ordinal) < 0)
{
aliasTable = "tb.";
}
sqlReference.Append(" INNER JOIN ").Append(itemReferences.TableRelation).Append(" as ").Append(tableReference);
sqlReference.Append(" ON ").Append(aliasTable).Append(itemReferences.FieldMainTable)
.Append(" = ").Append(tableReference).Append(".")
.Append(itemReferences.FieldRelationTable);
}
}
sql.Append(" SELECT tb.*")
.Append(referencesTablesSelect)
.Append(" ")
.Append(" FROM ").Append(nameTableBase)
.Append(" as tb ");
sql.Append(sqlReference);
sql.Append(" WHERE 1=1 ");
if (!(string.IsNullOrEmpty(propertyName) || string.IsNullOrEmpty(propertyValue)))
{
sql.Append(" and tb.")
.Append(propertyName)
.Append(" = '")
.Append(propertyValue)
.Append("' ");
}
using (var cmd = Context.Database.GetDbConnection().CreateCommand())
{
if (CFilter.AffirmationHasRelation.Equals(filterExecution.FilterControllers.HasStatus))
{
sql.Append(UtilSqlBuilder.ConditionEqual("tb", "StatusRegister", CStatusRegister.Active, numberParameter++, cmd));
}
if (CFilter.AffirmationHasRelation.Equals(filterExecution.FilterControllers.HasAccount))
{
sql.Append(UtilSqlBuilder.ConditionEqual("tb", "IdAccount", idAccount, numberParameter++, cmd));
}
foreach (var itemReferences in itemTables)
{
if (CFilter.AffirmationHasRelation.Equals(itemReferences.HasRelation))
{
if (CFilter.AffirmationHasRelation.Equals(itemReferences.HasStatus))
{
sql.Append(UtilSqlBuilder.ConditionEqual(itemReferences.TableInitial, "StatusRegister", CStatusRegister.Active, numberParameter++, cmd));
}
if (CFilter.AffirmationHasRelation.Equals(itemReferences.HasAccount))
{
sql.Append(UtilSqlBuilder.ConditionEqual(itemReferences.TableInitial, "IdAccount", idAccount, numberParameter++, cmd));
}
}
}
foreach (var fieldsTemp in filterExecution.FilterExecutionDetails)
{
if (CFilter.AffirmationHasRelation.Equals(fieldsTemp.FilterCriteria.FilterField.FilterTable.HasRelation))
{
sql.Append(
UtilSqlBuilder.AddCondition(fieldsTemp.FilterCriteria.FilterField.FilterTable.TableInitial,
fieldsTemp.FilterCriteria.FilterField.Field,
fieldsTemp.FilterCriteria.TypeFilter.SignFilter,
fieldsTemp.Value,
numberParameter,
cmd)
);
}
else if (CFilter.NegationHasRelation.Equals(fieldsTemp.FilterCriteria.FilterField.FilterTable.HasRelation))
{
sql.Append(
UtilSqlBuilder.AddCondition("tb",
fieldsTemp.FilterCriteria.FilterField.Field,
fieldsTemp.FilterCriteria.TypeFilter.SignFilter,
fieldsTemp.Value,
numberParameter,
cmd)
);
}
numberParameter++;
}
cmd.CommandText = sql.ToString();
if (cmd.Connection.State != ConnectionState.Open)
{
cmd.Connection.Open();
}
using (var dataReader = cmd.ExecuteReader())
{
while (dataReader.Read())
{
var dataRow = new ExpandoObject() as IDictionary <string, object>;
for (var iFiled = 0; iFiled < dataReader.FieldCount; iFiled++)
{
dataRow.Add(
dataReader.GetName(iFiled),
dataReader.IsDBNull(iFiled) ? null : dataReader[iFiled] // use null instead of {}
);
}
retObject.Add((ExpandoObject)dataRow);
}
}
}
}
return(retObject);
}
public void InsertOrUpdate(FilterExecution entity)
{
_filterExecutionDao.InsertOrUpdate(entity);
}
