private XElement FieldRef([NotNull] OrderByModel orderBy)
{
var ascending = orderBy.Ascending.ToString().ToUpper();
return(new XElement(Tags.FieldRef,
new XAttribute(Tags.Name, GetFieldInternalName(orderBy.FieldRef)),
new XAttribute(Tags.Ascending, ascending)));
}
/// <summary>
/// 支持用DataTable
/// </summary>
/// <param name="sqlModel"></param>
/// <param name="data"></param>
/// <param name="dtData"></param>
/// <returns></returns>
public JsonResult PagedJson(SqlModel sqlModel, RequestData data, DataTable dtData)
{
int page = this.GetPage(data);
int rows = this.GetRows(data);
OrderByModel om = this.GetOrderBy(data, sqlModel);
PagedDataTable pdt = sqlModel.ExecToPagedTable(page, rows, om, dtData);
dynamic result = pdt.ToDynamic();
return(Json(result));
}
protected OrderByModel GetOrderBy(RequestData data, SqlModel sqlModel)
{
OrderByModel om = new OrderByModel
{
OrderType = data.Get("order") == "desc" ? OrderType.Desc : OrderType.Asc,
FieldModel = data.Get("sort").ToField(sqlModel.FromTable)
};
return(om);
}
public void DataRow_OrderBy()
{
{
var comparer = DataRow.OrderBy(new OrderByModel().Column, SortDirection.Descending);
var dataSet = DataSet <OrderByModel> .Create();
dataSet.AddRow((_, x) => _.Column[x] = 1);
dataSet.AddRow((_, x) => _.Column[x] = 2);
Assert.AreEqual(typeof(OrderByModel), comparer.ModelType);
Assert.AreEqual(1, comparer.Compare(dataSet[0], dataSet[1]));
}
{
var simpleModel = new OrderByModel();
var condition = (simpleModel.Column == 1);
var comparer = DataRow.OrderBy(condition, SortDirection.Descending);
var dataSet = DataSet <OrderByModel> .Create();
dataSet.AddRow((_, x) => _.Column[x] = 1);
dataSet.AddRow((_, x) => _.Column[x] = 2);
Assert.AreEqual(typeof(OrderByModel), comparer.ModelType);
Assert.AreEqual(-1, comparer.Compare(dataSet[0], dataSet[1]));
}
{
var dataSet = DataSet <OrderByModel> .Create();
dataSet.AddRow((_, x) => _.LocalColumn[x] = 1);
dataSet.AddRow((_, x) => _.LocalColumn[x] = 2);
var comparer = DataRow.OrderBy(dataSet._.LocalColumn, SortDirection.Descending);
Assert.AreEqual(typeof(OrderByModel), comparer.ModelType);
Assert.AreEqual(1, comparer.Compare(dataSet[0], dataSet[1]));
}
{
var dataSet = DataSet <OrderByModel> .Create();
var _ = dataSet._;
dataSet.AddRow();
dataSet.AddRow();
_.Column[0] = 1;
_.Column[1] = 1;
_.LocalColumn[0] = 1;
_.LocalColumn[1] = 2;
var comparer = DataRow.OrderBy(_.Column).ThenBy(_.LocalColumn, SortDirection.Descending);
Assert.AreEqual(_.GetType(), comparer.ModelType);
Assert.AreEqual(1, comparer.Compare(dataSet[0], dataSet[1]));
}
}
public IHttpActionResult Fetch([FromUri] PaginationModel pagination = null, [FromUri] OrderByModel orderBy = null)
{
//Recupero le entità
var entities = _deviceErrorLogService.Fetch <DeviceErrorLog>(null, pagination, orderBy);
//Conto i risultati
int count = _deviceErrorLogService.Count <DeviceErrorLog>(null);
//Eseugo la mappatura a Dtos
var dtos = entities.Any() ? entities.Select(e => Mapper.Map <DeviceErrorLogDto>(e)).ToList() : new List <DeviceErrorLogDto>();
//Compongo i risultati di ritorno
var result = new FetchDto(dtos, count);
//Ritorno i risultati
return(Ok(result));
}
/// <summary>
/// ORDER BY clause with syntactic sugar of prefSQL
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
public override PrefSQLModel VisitOrderbyCategory(PrefSQLParser.OrderbyCategoryContext context)
{
string strSQL = "";
//Build CASE ORDER with arguments
string strExpr = context.exprCategory().GetText();
string strColumnName = GetColumnName(context.GetChild(0));
string strTable = GetTableName(context.GetChild(0));
string[] strTemp = Regex.Split(strExpr, @"(==|>>)"); //Split signs are == and >>
string strSQLOrderBy = "";
string strSqlelse = "";
//Define sort order value for each attribute
int iWeight = 0;
for (int i = 0; i < strTemp.GetLength(0); i++)
{
switch (strTemp[i])
{
case ">>":
iWeight += 100; //Gewicht erhöhen, da >> Operator
break;
case "==":
break; //Gewicht bleibt gleich da == Operator
case "OTHERSINCOMPARABLE":
////Add one, so that equal-clause cannot be true with same level-values, but other names
strSqlelse = " ELSE " + (iWeight);
break;
case "OTHERSEQUAL":
//Special word OTHERS EQUAL = all other attributes are defined with this order by value
strSqlelse = " ELSE " + iWeight;
break;
default:
//Check if it contains multiple values
if (strTemp[i].StartsWith("{"))
{
//Multiple values --> construct IN statement
strTemp[i] = strTemp[i].Replace("{", "(").Replace("}", ")");
strSQLOrderBy += " WHEN " + strTable + "." + strColumnName + " IN " + strTemp[i] + " THEN " + iWeight.ToString();
}
else
{
//Single value --> construct = statement
strSQLOrderBy += " WHEN " + strTable + "." + strColumnName + " = " + strTemp[i] + " THEN " + iWeight.ToString();
}
break;
}
//Always Sort ASCENDING
strSQL = "CASE" + strSQLOrderBy + strSqlelse + " END ASC";
}
OrderByModel orderByModel = new OrderByModel();
orderByModel.Start = context.start.StartIndex;
orderByModel.Stop = context.stop.StopIndex + 1;
orderByModel.Text = strSQL;
Model.OrderBy.Add(orderByModel);
return(base.VisitOrderbyCategory(context));
}
public IHttpActionResult Fetch(string name = null, string macAddress = null, Guid?processInstanceId = null, [FromUri] PaginationModel pagination = null, [FromUri] OrderByModel orderBy = null)
{
//Recupero le entità
var entities = _deviceService.Fetch(name, macAddress, processInstanceId, pagination, orderBy);
//Conto i risultati
int count = _deviceService.Count(name, macAddress, processInstanceId);
//Eseugo la mappatura a Dtos
//var dtos = entities.Any() ? entities.Select(e => Mapper.Map<DeviceIndexDto>(e)).ToList() : new List<DeviceIndexDto>();
//Compongo i risultati di ritorno
var result = new FetchDto(entities, count);
//Ritorno i risultati
return(Ok(result));
}
/// <summary>
/// Recupero tutti i device che non sono già stati usati in un processo di monitoraggio
/// </summary>
/// <param name="processInstanceId">Includo il processInstance </param>
/// <param name="pagination"></param>
/// <param name="orderBy"></param>
/// <returns></returns>
public IList <Device> FetchWithoutProcessInstanceId(Guid?processInstanceId, PaginationModel pagination, OrderByModel orderBy)
{
Process processAlias = null;
Parameter parameterAlias = null;
Alarm alarmAlias = null;
var subQueryProcessInstance = QueryOver.Of <ProcessInstance>()
.Select(e => e.Process.Id);
if (processInstanceId.HasValue)
{
subQueryProcessInstance = subQueryProcessInstance.Where(e => e.Id != processInstanceId);
}
var subQueryParameter = QueryOver.Of <Parameter>()
.JoinAlias(e => e.Process, () => processAlias)
.WithSubquery
.WhereProperty(() => processAlias.Id).In(subQueryProcessInstance)
.Select(e => e.Id);
var subQueryAlarm = QueryOver.Of <Alarm>()
.JoinAlias(e => e.Parameter, () => parameterAlias)
.WithSubquery
.WhereProperty(() => parameterAlias.Id).In(subQueryParameter)
.Select(e => e.Id);
var subQueryAlarmMetric = QueryOver.Of <AlarmMetric>()
.JoinAlias(e => e.Alarm, () => alarmAlias)
.WithSubquery
.WhereProperty(() => alarmAlias.Id).In(subQueryAlarm)
.Select(e => e.Device.Id);
var devicesBusy = Session.QueryOver <Device>()
.WithSubquery
.WhereProperty(e => e.Id).In(subQueryAlarmMetric).List();
var queryOver = Session.QueryOver <Device>()
.WhereRestrictionOn(e => e.Id).Not.IsIn(devicesBusy.Select(e => e.Id).ToList());
return(queryOver.Skip(pagination?.StartRowIndex ?? 0).Take(pagination?.MaxRowIndex ?? _maxRowIndexDefault).List());
}
public IList <ProcessMacro> Fetch(string name, string value, Guid?processId, PaginationModel pagination, OrderByModel orderBy)
{
var entities = _processMacroRepository.Fetch(name, value, processId, pagination, orderBy);
return(entities);
}
public IHttpActionResult Fetch(string username = null, string firstname = null, string surname = null, string email = null, [FromUri] PaginationModel pagination = null, [FromUri] OrderByModel orderBy = null)
{
//Recupero le entità
//Compongo il dto in entrata
UserDto dto = new UserDto
{
Username = username,
Firstname = firstname,
Surname = surname,
Email = email,
};
//Recupero le entità
IList <User> entities = _authorizeService.FetchUsers(dto, pagination?.StartRowIndex, pagination?.MaxRowIndex, orderBy?.OrderByProperty, orderBy?.OrderByType);
//Conto i risultati
int count = _authorizeService.CountUsers(dto, null, null);
//Eseugo la mappatura a Dtos
var dtos = entities.Any() ? entities.Select(e => Mapper.Map <UserUpdateDto>(e)).ToList() : new List <UserUpdateDto>();
//Compongo i risultati di ritorno
var result = new FetchDto(dtos, count);
//Ritorno i risultati
return(Ok(result));
}
public IList <ProcessInstance> Fetch(string name, Guid?doctorId, Guid?processId, Guid?patientId, ProcessInstanceState?processInstanceState, PaginationModel pagination, OrderByModel orderBy)
{
var queryOver = BuildQueryOver(name, doctorId, patientId, processId, processInstanceState);
queryOver = orderBy != null && orderBy.IsValid()
? queryOver.OrderByString(orderBy)
: queryOver.OrderBy(e => e.InsertDate).Desc.ThenBy(e => e.Name).Asc;
return(queryOver.Skip(pagination?.StartRowIndex ?? 0).Take(pagination?.MaxRowIndex ?? _maxRowIndexDefault).List());
}
public IList <InviteFriend> Fetch(Guid?processInstanceId, PaginationModel pagination, OrderByModel orderBy)
{
var queryOver = BuildQueryOver(processInstanceId);
queryOver = orderBy != null && orderBy.IsValid()
? queryOver.OrderByString(orderBy)
: queryOver.OrderBy(e => e.Surname).Asc;
return(queryOver.Skip(pagination?.StartRowIndex ?? 0).Take(pagination?.MaxRowIndex ?? _maxRowIndexDefault).List());
}
public IList <DeviceIndexDto> Fetch(string name, string macAddress, Guid?processInstanceId, PaginationModel pagination, OrderByModel orderBy)
{
var entities = _deviceRepository.Fetch(name, macAddress, processInstanceId, pagination, orderBy);
var processInstances = _processInstanceRepository.FetchByDevice(entities.Select(e => e.Id).ToList());
IList <DeviceIndexDto> dtos = new List <DeviceIndexDto>();
foreach (var entity in entities)
{
DeviceIndexDto dto = new DeviceIndexDto();
dto = AutoMapper.Mapper.Map <DeviceIndexDto>(entity);
foreach (var processInstance in processInstances)
{
foreach (var parameters in processInstance.Process.Parameters)
{
foreach (var alarm in parameters.Alarms)
{
foreach (var alarmMetric in alarm.AlarmMetrics)
{
var device = alarmMetric.Device;
if (device != null && device.Id == entity.Id)
{
dto.PatientName = string.Format("{0} {1}", processInstance.Patient.Firstname, processInstance.Patient.Surname);
dto.ProcessInstanceName = processInstance.Process.Name;
}
}
}
}
}
dtos.Add(dto);
}
return(dtos);
}
public IHttpActionResult Fetch(Guid?processInstanceId, [FromUri] PaginationModel pagination = null, [FromUri] OrderByModel orderBy = null)
{
//Recupero le entità
var entities = _inviteFriendService.Fetch(processInstanceId, pagination, orderBy);
var count = _inviteFriendService.Count(processInstanceId);
//Eseugo la mappatura a Dtos
var dtos = entities.Any() ? entities.Select(e => Mapper.Map <InviteFriendDto>(e)).ToList() : new List <InviteFriendDto>();
//Compongo i risultati di ritorno
var result = new FetchDto(dtos, count);
//Ritorno i risultati
return(Ok(result));
}
public IHttpActionResult Fetch(HealthRiskType?type = null, HealthRiskLevel?level = null, string rating = null, DateTime?startDate = null, DateTime?endDate = null, bool?isLast = null, Guid?registryId = null, [FromUri] PaginationModel pagination = null, [FromUri] OrderByModel orderBy = null)
{
if (!registryId.HasValue)
{
return(Ok(new FetchDto(null, 0)));
}
//Recupero le entità
var entities = _healthRiskService.Fetch(type, level, rating, startDate, endDate, isLast, registryId, pagination, orderBy);
//Conto i risultati
int count = _healthRiskService.Count(type, level, rating, startDate, endDate, isLast, registryId);
//Eseugo la mappatura a Dtos
var dtos = entities.Any() ? entities.Select(e => Mapper.Map <HealthRiskIndexDto>(e)).ToList() : new List <HealthRiskIndexDto>();
//Compongo i risultati di ritorno
var result = new FetchDto(dtos, count);
//Ritorno i risultati
return(Ok(result));
}
public IHttpActionResult Fetch(bool?isActive, [FromUri] PaginationModel pagination = null, [FromUri] OrderByModel orderBy = null)
{
//Recupero le entità
var entities = _alarmFiredService.Fetch <AlarmFired>(e => e.IsActive == isActive, pagination, orderBy);
var count = _alarmFiredService.Count <AlarmFired>(e => e.IsActive == isActive);
//Eseugo la mappatura a Dtos
var dtos = entities.Any() ? entities.Select(e => Mapper.Map <AlarmFiredSwiftDto>(e)).ToList() : new List <AlarmFiredSwiftDto>();
//Compongo i risultati di ritorno
var result = new FetchDto(dtos, count);
//Ritorno i risultati
return(Ok(result));
}
public IHttpActionResult Fetch(string firstname = null, string surname = null, SexType?sex = null, RegistryType?registryType = null, [FromUri] PaginationModel pagination = null, [FromUri] OrderByModel orderBy = null)
{
//Recupero le entità
var entities = _registryService.Fetch(firstname, surname, sex, registryType, pagination, orderBy);
//Conto i risultati
int count = _registryService.Count(firstname, surname, sex, registryType);
//Eseugo la mappatura a Dtos
var dtos = entities.Any() ? entities.Select(e => Mapper.Map <RegistryIndexDto>(e)).ToList() : new List <RegistryIndexDto>();
//Compongo i risultati di ritorno
var result = new FetchDto(dtos, count);
//Ritorno i risultati
return(Ok(result));
}
public IList <Device> FetchWithoutProcessInstanceId(Guid?processInstanceId, PaginationModel pagination, OrderByModel orderBy)
{
var entities = _deviceRepository.FetchWithoutProcessInstanceId(processInstanceId, pagination, orderBy);
return(entities);
}
public IHttpActionResult Fetch(string name = null, string description = null, [FromUri] PaginationModel pagination = null, [FromUri] OrderByModel orderBy = null)
{
//Recupero le entità
//Compongo il dto in entrata
RoleDto dto = new RoleDto
{
Name = name,
Description = description
};
//Recupero le entità
IList <Role> entities = _authorizeService.FetchRoles(dto, pagination?.StartRowIndex, pagination?.MaxRowIndex, orderBy?.OrderByProperty, orderBy?.OrderByType);
//Conto i risultati
int count = _authorizeService.CountRoles(dto, null, null);
//Eseugo la mappatura a Dtos
var dtos = entities.Any() ? entities.Select(e => Mapper.Map <RoleUpdateDto>(e)).ToList() : new List <RoleUpdateDto>();
//Compongo i risultati di ritorno
var result = new FetchDto(dtos, count);
//Ritorno i risultati
return(Ok(result));
}
public IList <Process> Fetch(string name, ProcessType?processType, PaginationModel pagination, OrderByModel orderBy)
{
var queryOver = BuildQueryOver(name, processType);
queryOver = orderBy != null && orderBy.IsValid()
? queryOver.OrderByString(orderBy)
: queryOver.OrderBy(e => e.Name).Asc;
return(queryOver.Skip(pagination?.StartRowIndex ?? 0).Take(pagination?.MaxRowIndex ?? _maxRowIndexDefault).List());
}
public IList <Parameter> Fetch(string name, Guid?processId, Guid?deviceId, int?frequency, FrequencyType?frequencyType, PaginationModel pagination, OrderByModel orderBy)
{
var entities = _parameterRepository.Fetch(name, processId, deviceId, frequency, frequencyType, pagination, orderBy);
return(entities);
}
public IList <Device> Fetch(string name, string macAddress, Guid?processInstanceId, PaginationModel pagination, OrderByModel orderBy)
{
var queryOver = BuildQueryOver(name, macAddress, processInstanceId);
queryOver = orderBy != null && orderBy.IsValid()
? queryOver.OrderByString(orderBy)
: queryOver.OrderBy(e => e.Name).Asc;
return(queryOver.Skip(pagination?.StartRowIndex ?? 0).Take(pagination?.MaxRowIndex ?? _maxRowIndexDefault).List());
}
public IHttpActionResult Fetch(string name = null, Guid?doctorId = null, Guid?processId = null, Guid?patientId = null, ProcessInstanceState?processInstanceState = null, [FromUri] PaginationModel pagination = null, [FromUri] OrderByModel orderBy = null)
{
//Recupero le entità
var entities = _processInstanceService.Fetch(name, doctorId, processId, patientId, processInstanceState, pagination, orderBy);
//Conto i risultati
int count = _processInstanceService.Count(name, doctorId, processId, patientId, processInstanceState);
//Eseugo la mappatura a Dtos
var dtos = entities.Any() ? entities.Select(e => Mapper.Map <ProcessInstanceIndexDto>(e)).ToList() : new List <ProcessInstanceIndexDto>();
//Compongo i risultati di ritorno
var result = new FetchDto(dtos, count);
//Ritorno i risultati
return(ResponseMessage(Request.CreateResponse(HttpStatusCode.OK, result)));
}
public IList <InviteFriend> Fetch(Guid?processInstanceId, PaginationModel pagination, OrderByModel orderBy)
{
var entities = _inviteFriendRepository.Fetch(processInstanceId, pagination, orderBy);
return(entities);
}
public IList <HealthRisk> Fetch(HealthRiskType?type, HealthRiskLevel?level, string rating, DateTime?startDate, DateTime?endDate, bool?isLast, Guid?registryId, PaginationModel pagination, OrderByModel orderBy)
{
var queryOver = BuildQueryOver(type, level, rating, startDate, endDate, isLast, registryId);
queryOver = orderBy != null && orderBy.IsValid()
? queryOver.OrderByString(orderBy)
: queryOver.OrderBy(e => e.Type).Asc;
return(queryOver.Skip(pagination?.StartRowIndex ?? 0).Take(pagination?.MaxRowIndex ?? _maxRowIndexDefault).List());
}
public IList <Registry> Fetch(string firstname, string surname, SexType?sex, RegistryType?registryType, PaginationModel pagination, OrderByModel orderBy)
{
var entities = _registryRepository.Fetch(firstname, surname, sex, registryType, pagination, orderBy);
return(entities);
}
/// <summary>
/// Parses a PREFERENE SQL Statement in an ANSI SQL Statement
/// </summary>
/// <param name="strInput"></param>
/// <param name="prefSQLParam"></param>
/// <returns>Return the ANSI SQL Statement</returns>
/// <exception cref="Exception">This is exception is thrown because the String is not a valid PrefSQL Query</exception>
internal string ParsePreferenceSQL(string strInput, PrefSQLModel prefSQLParam)
{
SQLSort sqlSort = new SQLSort();
SQLCriterion sqlCriterion = new SQLCriterion();
string strSQLReturn = ""; //The SQL-Query that is built on the basis of the prefSQL
PrefSQLModel prefSQL = prefSQLParam ?? GetPrefSqlModelFromPreferenceSql(strInput);
try
{
//Check if parse was successful and query contains PrefSQL syntax
if (prefSQL != null) // && strInput.IndexOf(SkylineOf) > 0
{
if (prefSQL.Skyline.Count > 0)
{
//Mark as incomparable if needed (to choose the correct algorithm)
//withIncomparable = prefSQL.WithIncomparable;
//Add all Syntax before the Skyline-Clause
strSQLReturn = strInput.Substring(0, strInput.IndexOf(SkylineOf, StringComparison.OrdinalIgnoreCase) - 1).TrimStart(' ');
//Add Skyline Attributes to select list. This option is i.e. useful to create a dominance graph.
//With help of the skyline values it is easier to create this graph
if (ShowInternalAttributes)
{
//Add the attributes to the existing SELECT clause
string strSQLSelectClause = GetSelectClauseForSkylineAttributes(prefSQL);
string strSQLBeforeFrom = strSQLReturn.Substring(0, strSQLReturn.IndexOf(" FROM ", StringComparison.OrdinalIgnoreCase) + 1);
string strSQLAfterFromShow = strSQLReturn.Substring(strSQLReturn.IndexOf(" FROM ", StringComparison.OrdinalIgnoreCase) + 1);
strSQLReturn = strSQLBeforeFrom + strSQLSelectClause + " " + strSQLAfterFromShow;
}
//Add ORDER BY Clause
string strOrderBy = "";
if (strInput.IndexOf(" ORDER BY ", StringComparison.OrdinalIgnoreCase) > 0)
{
if (prefSQL.Ordering == Ordering.AsIs)
{
string strTmpInput = strInput;
//Replace category clauses
//Start with latest order by (otherwise substring start, stop position are changed)
for (int iIndex = prefSQL.OrderBy.Count - 1; iIndex >= 0; iIndex--)
{
OrderByModel model = prefSQL.OrderBy[iIndex];
strTmpInput = strTmpInput.Substring(0, model.Start) + model.Text + strTmpInput.Substring(model.Stop);
}
strOrderBy = strTmpInput.Substring(strInput.IndexOf(" ORDER BY ", StringComparison.OrdinalIgnoreCase) + 1);
}
else
{
strOrderBy = sqlSort.GetSortClause(prefSQL, prefSQL.Ordering); // sqlSort.getSortClause(prefSQL, _OrderType);
}
//Add space charachter in front of ORDER BY if not already present
if (!strOrderBy.Substring(0, 1).Equals(" "))
{
strOrderBy = " " + strOrderBy;
}
}
////////////////////////////////////////////
//attributes used for native sql algorithm
string strWhere = sqlCriterion.GetCriterionClause(prefSQL, strSQLReturn);
////////////////////////////////////////////
//attributes used for other algorithms
string strOperators;
string strAttributesSkyline = BuildPreferencesBNL(prefSQL, out strOperators);
//Without SELECT
//Remove TOP keyword, expect for the native SQL algorithm
if (prefSQL.NumberOfRecords != 0 && SkylineType.IsNative() == false)
{
//Remove Top Keyword in inner clause
int iPosTop = strSQLReturn.IndexOf(" TOP ", StringComparison.OrdinalIgnoreCase) + 1;
int iPosTopEnd = strSQLReturn.Substring(iPosTop + 3).TrimStart().IndexOf(" ", StringComparison.Ordinal);
string strSQLAfterTop = strSQLReturn.Substring(iPosTop + 3).TrimStart();
strSQLReturn = strSQLReturn.Substring(0, iPosTop) + strSQLAfterTop.Substring(iPosTopEnd + 1);
}
string strAttributesOutput = ", " + strSQLReturn.Substring(7, strSQLReturn.IndexOf(" FROM ", StringComparison.OrdinalIgnoreCase) - 6);
string strSQLAfterFrom = strSQLReturn.Substring(strSQLReturn.IndexOf(" FROM ", StringComparison.OrdinalIgnoreCase) + 1);
string strFirstSQL = "SELECT " + strAttributesSkyline + " " + strAttributesOutput + strSQLAfterFrom;
if (SkylineType.IsNative())
{
strFirstSQL = strSQLReturn;
}
string strOrderByAttributes = sqlSort.GetSortClause(prefSQL, WindowSort);
////////////////////////////////////////////
//attributes used for hexagon
string[] additionalParameters = new string[6];
string strOperatorsHexagon;
string strAttributesSkylineHexagon = BuildSelectHexagon(prefSQL, out strOperatorsHexagon);
//Without SELECT
//Quote quotes because it is a parameter of the stored procedure
string strFirstSQLHexagon = "SELECT " + strAttributesSkylineHexagon + " " + strAttributesOutput + strSQLAfterFrom;
strFirstSQLHexagon = strFirstSQLHexagon.Replace("'", "''");
//Quote quotes because it is a parameter of the stored procedure
//string strSelectDistinctIncomparable = "";
string weightHexagonIncomparable = "";
string strSelectDistinctIncomparable = BuildIncomparableHexagon(prefSQL, ref weightHexagonIncomparable);
strSelectDistinctIncomparable = strSelectDistinctIncomparable.Replace("'", "''");
additionalParameters[0] = strFirstSQLHexagon;
additionalParameters[1] = strOperatorsHexagon;
additionalParameters[2] = strSelectDistinctIncomparable;
additionalParameters[3] = weightHexagonIncomparable;
_skylineType.SortType = (int)prefSQL.Ordering;
_skylineType.RecordAmountLimit = prefSQL.NumberOfRecords;
_skylineType.MultipleSkylineUpToLevel = _skylineUpToLevel;
_skylineType.AdditionParameters = additionalParameters;
_skylineType.HasIncomparablePreferences = prefSQL.WithIncomparable;
//Now create the query depending on the Skyline algorithm
if (!prefSQL.HasSkylineSample)
{
strSQLReturn = _skylineType.GetStoredProcedureCommand(strWhere, strOrderBy, strFirstSQL,
strOperators, strOrderByAttributes);
}
else
{
var skylineSample = new SkylineSampling
{
SubsetCount = prefSQL.SkylineSampleCount,
SubsetDimension = prefSQL.SkylineSampleDimension,
SelectedStrategy = _skylineType
};
strSQLReturn = skylineSample.GetStoredProcedureCommand(strWhere, strOrderBy, strFirstSQL,
strOperators, strOrderByAttributes);
}
}
if (prefSQL.Ranking.Count > 0)
{
if (prefSQL.ContainsOpenPreference)
{
throw new Exception("WeightedSum cannot handle implicit INCOMPARABLE values. Please add the explicit OTHERS EQUAL to the preference");
}
string strSelectExtremas = "";
string strRankingWeights = "";
string strRankingExpressions = "";
string strColumnNames = "";
// Set the decimal seperator, because prefSQL double values are always with decimal separator "."
NumberFormatInfo format = new NumberFormatInfo();
format.NumberDecimalSeparator = ".";
foreach (RankingModel rankingModel in prefSQL.Ranking)
{
strSelectExtremas += rankingModel.SelectExtrema + ";";
strRankingWeights += rankingModel.Weight.ToString(format) + ";";
strRankingExpressions += rankingModel.Expression + ";";
strColumnNames += rankingModel.FullColumnName.Replace(".", "_") + ";";
}
strSelectExtremas = strSelectExtremas.TrimEnd(';');
strRankingWeights = strRankingWeights.TrimEnd(';');
strRankingExpressions = strRankingExpressions.TrimEnd(';');
strColumnNames = strColumnNames.TrimEnd(';');
SPRanking spRanking = new SPRanking();
spRanking.ShowInternalAttributes = ShowInternalAttributes;
//Quoting is done in GetStoredProc Command
//string strExecSQL = strInput.Replace("'", "''");
strSQLReturn = spRanking.GetStoredProcedureCommand(strInput, strSelectExtremas, strRankingWeights, strRankingExpressions, strColumnNames);
}
}
else
{
//Query does not contain a preference --> return original query
strSQLReturn = strInput;
}
}
catch (Exception e)
{
//Parse syntaxerror
throw new Exception(e.Message);
}
return(strSQLReturn);
}
public IList <Parameter> Fetch(string name, Guid?processId, Guid?deviceId, int?frequency, FrequencyType?frequencyType, PaginationModel pagination, OrderByModel orderBy)
{
var queryOver = BuildQueryOver(name, processId, deviceId, frequency, frequencyType);
queryOver = orderBy != null && orderBy.IsValid()
? queryOver.OrderByString(orderBy)
: queryOver.OrderBy(e => e.Name).Asc;
return(queryOver.Skip(pagination?.StartRowIndex ?? 0).Take(pagination?.MaxRowIndex ?? _maxRowIndexDefault).List());
}
public IList <Registry> Fetch(string firstname, string surname, SexType?sex, RegistryType?registryType, PaginationModel pagination, OrderByModel orderBy)
{
var queryOver = BuildQueryOver(firstname, surname, sex, registryType);
queryOver = orderBy != null && orderBy.IsValid()
? queryOver.OrderByString(orderBy)
: queryOver.OrderBy(e => e.Surname).Asc;
return(queryOver.Skip(pagination?.StartRowIndex ?? 0).Take(pagination?.MaxRowIndex ?? _maxRowIndexDefault).List());
}
public IList <ProcessInstance> Fetch(string name, Guid?doctorId, Guid?processId, Guid?patientId, ProcessInstanceState?processInstanceState, PaginationModel pagination, OrderByModel orderBy)
{
var entities = _processInstanceRepository.Fetch(name, doctorId, processId, patientId, processInstanceState, pagination, orderBy);
return(entities);
}
