public List<ChargeEntity> Search(QueryCondition<ChargeEntity> entity)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("ChargeSearch");
using (DynamicSqlBuilder sqlBuilder = new DynamicSqlBuilder(command, entity.PagingInfo, "SysNo Desc"))
{
ChargeEntity query = entity.Condition;
sqlBuilder.Conditions.AddCustomCondition(RelationType.AND, " Status <> -1");
if (query != null)
{
if (!string.IsNullOrEmpty(query.Title))
{
sqlBuilder.Conditions.AddCustomCondition(RelationType.AND, " (Title Like'%'+@Title+'%')");
command.AddInputParameter("@Title", DbType.String, query.Title);
}
if (!string.IsNullOrEmpty(query.InDateCondition) &&
query.InDateCondition.IndexOf('-') > 0)
{
sqlBuilder.Conditions.AddCustomCondition(RelationType.AND, " InDate BETWEEN @BeginInDate AND @EndInDate");
command.AddInputParameter("@BeginInDate", DbType.String, query.InDateCondition.Split('-')[0]);
command.AddInputParameter("@EndInDate", DbType.String, query.InDateCondition.Split('-')[1]);
}
}
command.CommandText = sqlBuilder.BuildQuerySql();
return command.ExecuteEntityList<ChargeEntity>();
}
}
/// <summary>
/// Returns the WHERE portion of an SQL statement contains the fields describing the criteria passed across.
/// </summary>
/// <param name="aircraft"></param>
/// <param name="criteria"></param>
/// <returns></returns>
public static CriteriaAndProperties GetFlightsCriteria(BaseStationAircraft aircraft, SearchBaseStationCriteria criteria)
{
var result = new CriteriaAndProperties();
StringBuilder command = new StringBuilder();
if (aircraft != null)
{
DynamicSqlBuilder.AddWhereClause(command, "[Flights].[AircraftID]", " = @aircraftID");
result.Parameters.Add("aircraftID", aircraft.AircraftID);
}
if (criteria.UseAlternateCallsigns && criteria.Callsign != null && criteria.Callsign.Condition == FilterCondition.Equals && !String.IsNullOrEmpty(criteria.Callsign.Value))
{
GetAlternateCallsignCriteria(command, result.Parameters, criteria.Callsign, "[Flights].[Callsign]");
}
else
{
DynamicSqlBuilder.AddCriteria(command, criteria.Callsign, result.Parameters, "[Flights].[Callsign]", "callsign");
}
DynamicSqlBuilder.AddCriteria(command, criteria.Date, result.Parameters, "[Flights].[StartTime]", "fromStartTime", "toStartTime");
DynamicSqlBuilder.AddCriteria(command, criteria.Operator, result.Parameters, "[Aircraft].[RegisteredOwners]", "registeredOwners");
DynamicSqlBuilder.AddCriteria(command, criteria.Registration, result.Parameters, "[Aircraft].[Registration]", "registration");
DynamicSqlBuilder.AddCriteria(command, criteria.Icao, result.Parameters, "[Aircraft].[ModeS]", "icao");
DynamicSqlBuilder.AddCriteria(command, criteria.Country, result.Parameters, "[Aircraft].[ModeSCountry]", "modeSCountry");
DynamicSqlBuilder.AddCriteria(command, criteria.IsEmergency, "[Flights].[HadEmergency]");
DynamicSqlBuilder.AddCriteria(command, criteria.Type, result.Parameters, "[Aircraft].[ICAOTypeCode]", "modelIcao");
DynamicSqlBuilder.AddCriteria(command, criteria.FirstAltitude, result.Parameters, "[Flights].[FirstAltitude]", "fromFirstAltitude", "toFirstAltitude");
DynamicSqlBuilder.AddCriteria(command, criteria.LastAltitude, result.Parameters, "[Flights].[LastAltitude]", "fromLastAltitude", "toLastAltitude");
result.SqlChunk = command.ToString();
return(result);
}
public List<ErrorLogEntity> Seach(QueryCondition<ErrorLogEntity> entity)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("ErrorLogSearch");
using (DynamicSqlBuilder sqlBuilder = new DynamicSqlBuilder(command, entity.PagingInfo, "SysNo Desc"))
{
ErrorLogEntity query = entity.Condition;
if (query != null)
{
if (!string.IsNullOrEmpty(query.GlobalName))
{
sqlBuilder.Conditions.AddCustomCondition(RelationType.AND, " (GlobalName Like'%'+@GlobalName+'%')");
command.AddInputParameter("@GlobalName", DbType.String, query.GlobalName);
//sqlBuilder.Conditions.AddCondition("UserName", DbType.String, "@UserName", query.UserName);
}
}
command.CommandText = sqlBuilder.BuildQuerySql();
return command.ExecuteEntityList<ErrorLogEntity>();
}
}
public List<BrowsingHistoryEntity> Search(QueryCondition<BrowsingHistoryEntity> entity)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("BrowsingHistorySearch");
using (DynamicSqlBuilder sqlBuilder = new DynamicSqlBuilder(command, entity.PagingInfo, "SysNo Desc"))
{
BrowsingHistoryEntity query = entity.Condition;
sqlBuilder.Conditions.AddCustomCondition(RelationType.AND, " Status <> -1");
if (query != null)
{
if (!string.IsNullOrEmpty(query.PageName))
{
sqlBuilder.Conditions.AddCustomCondition(RelationType.AND, " (PageName Like'%'+@PageName+'%')");
command.AddInputParameter("@PageName", DbType.String, query.PageName);
}
}
command.CommandText = sqlBuilder.BuildQuerySql();
return command.ExecuteEntityList<BrowsingHistoryEntity>();
}
}
/// <summary>
/// Builds up the criteria and properties for all alternate callsigns.
/// </summary>
/// <param name="command"></param>
/// <param name="parameters"></param>
/// <param name="criteria"></param>
/// <param name="callsignField"></param>
private static void GetAlternateCallsignCriteria(StringBuilder command, DynamicParameters parameters, FilterString criteria, string callsignField)
{
if (criteria != null && !String.IsNullOrEmpty(criteria.Value))
{
var callsignParser = Factory.Resolve <ICallsignParser>();
var alternates = callsignParser.GetAllAlternateCallsigns(criteria.Value);
for (var i = 0; i < alternates.Count; ++i)
{
var isFirst = i == 0;
var isLast = i + 1 == alternates.Count;
var callsign = alternates[i];
var parameterName = String.Format("callsign{0}", i + 1);
DynamicSqlBuilder.AddWhereClause(command, callsignField,
String.Format(" {0} @{1}", !criteria.ReverseCondition ? "=" : "<>", parameterName),
useOR: !isFirst && !criteria.ReverseCondition,
openParenthesis: isFirst,
closeParenthesis: isLast);
parameters.Add(parameterName, callsign);
}
}
}
public List<SystemUserEntity> Search(QueryCondition<SystemUserEntity> entity)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("SystemUserSearch");
using (DynamicSqlBuilder sqlBuilder = new DynamicSqlBuilder(command, entity.PagingInfo, "SysNo Desc"))
{
SystemUserEntity query = entity.Condition;
sqlBuilder.Conditions.AddCustomCondition(RelationType.AND, " Status <> -1");
if (query != null)
{
if (!string.IsNullOrEmpty(query.UserName))
{
sqlBuilder.Conditions.AddCustomCondition(RelationType.AND, " (UserName Like'%'+@UserName+'%')");
command.AddInputParameter("@UserName", DbType.String, query.UserName);
//sqlBuilder.Conditions.AddCondition("UserName", DbType.String, "@UserName", query.UserName);
}
if (query.DropRole > 0)
{
sqlBuilder.Conditions.AddCustomCondition(RelationType.AND, " SysNo IN (SELECT SystemUserSysNo FROM SystemUser_RoleMapping WHERE RoleSysNo = RoleSysNo)");
command.AddInputParameter("@RoleSysNo", DbType.Int32, query.DropRole);
}
if (!string.IsNullOrEmpty(query.InDateCondition) &&
query.InDateCondition.IndexOf('-') > 0)
{
sqlBuilder.Conditions.AddCustomCondition(RelationType.AND, " InDate BETWEEN @BeginInDate AND @EndInDate");
command.AddInputParameter("@BeginInDate", DbType.String, query.InDateCondition.Split('-')[0]);
command.AddInputParameter("@EndInDate", DbType.String, query.InDateCondition.Split('-')[1]);
}
}
command.CommandText = sqlBuilder.BuildQuerySql();
return command.ExecuteEntityList<SystemUserEntity>();
}
}
