private static void AddWhereClauses(IEnumerable <WhereCondition> whereConditions, SelectQueryBuilder query)
{
foreach (var clause in whereConditions)
{
var myWhereClause = new WhereClause($"{clause.WhereLeftTable}.{clause.WhereLeftColumn}", clause.WhereOperator, $"{clause.WhereRightTable}.{clause.WhereRightColumn}", clause.WhereAndOr);
if (clause.SubClauses?.Count > 0)
{
foreach (var subClause in clause.SubClauses)
{
myWhereClause.AddClause(subClause.Connector, subClause.CompareOperator, subClause.WhereRightColumn);
}
query.AddWhere(myWhereClause);
}
else
{
query.AddWhere($"{clause.WhereLeftTable}.{clause.WhereLeftColumn}", clause.WhereOperator,
clause.WhereLiteral ?? clause.WhereRightColumn, clause.WhereAndOr);
}
}
}
/// <summary>
/// AdvancedSearchQuery Method: Used for Basic and Advanced Search
/// </summary>
/// <param name="QueryType"> type 1 = Advanced search, type 2= basic search(String)</param>
public string AdvancedSearchQuery(int QueryType)
{
//used for Basic and Advanced Search
SelectQueryBuilder Query = new SelectQueryBuilder(); //creates the query reference
if (MaxRecords > 0)
{
//Query.TopRecords = MaxRecords;
Query.SelectColumns("Distinct id_in_ref as 'Incident Number'",
"[dmtreporting_dev].[dbo].[incidentdim].inc_created_date as' Date Created'",
"replace(inc_location_address,';','') as 'Location Address'",
"[dmtreporting_dev].[dbo].[incidentdim].borough as 'Borough'",
"[incident_category] AS 'Incident Type'", "incident_stage"); //Select Statement
}
else
{
Query.SelectColumns("Distinct id_in_ref as 'Incident Number'",
"[dmtreporting_dev].[dbo].[incidentdim].inc_created_date as'Date Created'",
"replace(inc_location_address,';','') as 'Location Address'", "incident_category as 'Incident Type'",
"[dmtreporting_dev].[dbo].[incidentdim].borough as 'Borough'", "incident_stage",
"LLCoordString", "id_easting", "id_northing", "ward");
}
Query.SelectFromTable("incidentdim");
if (QueryType == 1)
{
Query.AddJoin(JoinType.LeftJoin, "[dmtreporting_dev].[dbo].[PropertyDim]", "Lup_Pk", Comparison.Equals,
"incidentdim", "Inc_Property_Type");
Query.AddJoin(JoinType.LeftJoin, "[dmtreporting_dev].[dbo].[irs_additional_info]", "Adi_Fk_Inc",
Comparison.Equals, "incidentdim", "Inc_Pk");
Query.AddJoin(JoinType.LeftJoin, "[dmtreporting_dev].[dbo].[vw_IRS_Custom_Library]", "incidentnumber",
Comparison.Equals, "incidentdim", "id_in_ref");
Query.AddJoin(JoinType.LeftJoin, "[dmtreporting_dev].[dbo].[victimdim]", "vic_fk_inc", Comparison.Equals,
"incidentdim", "id_in_ref");
Query.AddJoin(JoinType.LeftJoin, "[dmtreporting_dev].[dbo].[MB_MOBILISATIONS]", "MB_IN_REF",
Comparison.Equals, "incidentdim", "id_in_ref");
Query.AddJoin(JoinType.LeftJoin, "Equipment_Used ", "Equ_Fk_Inc", Comparison.Equals, "incidentdim", "Inc_Pk");
Query.AddJoin(JoinType.LeftJoin, "[threetc_mis].[dbo].[nar_rative_log]", "[nar_inc_id]", Comparison.Equals,
"incidentdim", "id_in_ref");
}
if (TimeCond == "0")
{
TimeCond = "3";
}
else
{
TimeCond = TimeCond;
}
if (PumpCond == "1")
{
Query.AddWhere("num_vehicles_deployed", Comparison.Equals, PumpCond);
}
if (PumpCond == "2")
{
Query.AddWhere("num_vehicles_deployed", Comparison.GreaterOrEquals, "2");
Query.AddWhere("num_vehicles_deployed", Comparison.LessThan, "4");
}
if (PumpCond == "3")
{
Query.AddWhere("num_vehicles_deployed", Comparison.GreaterOrEquals, "3");
Query.AddWhere("num_vehicles_deployed", Comparison.LessThan, "5");
}
if (PumpCond == "4")
{
Query.AddWhere("num_vehicles_deployed", Comparison.GreaterOrEquals, "4");
Query.AddWhere("num_vehicles_deployed", Comparison.LessThan, "6");
}
if (PumpCond == "5")
{
Query.AddWhere("num_vehicles_deployed", Comparison.GreaterOrEquals, "6");
Query.AddWhere("num_vehicles_deployed", Comparison.LessThan, "9");
}
if (PumpCond == "6")
{
Query.AddWhere("num_vehicles_deployed", Comparison.GreaterOrEquals, "9");
}
var clause = new WhereClause();
if (FAGroup_int > 0)
{
Query.AddWhere("fa_group", Comparison.Equals, fagroup_db);
}
if (NarLogText.Length > 2)
{
if (Incidentdt.Date < Controldt.Date)
{
Query.AddWhere("nar_log_text", Comparison.Like, "TEXT");
}
else
{
Query.AddWhere("nar_log_entry", Comparison.Like, "Log Entry");
}
}
else
{
// do nothing
}
if (NarLogText.Length > 1 && NarrtiveSplit.Length < 2)
{
clause = Query.AddWhere("nar_log_text", Comparison.Like, new SqlLiteral("'%" + NarLogText.ToUpper() + "%'"));
}
if (NarLogText.Length > 1 && NarrtiveSplit.Length >= 2)
{
clause = Query.AddWhere("nar_log_text", Comparison.Like, "SQLworkaround");
foreach (string t in NarrtiveSplit)
{
clause.AddClause(LogicOperator.Or, Comparison.Like, "%" + t + "%");
}
}
else
{
if (NarLogText.Length == 0)
{
//clause = Query.AddWhere("nar_log_text", Comparison.Like, new SqlLiteral("'%" + tb_NarLogSearch.Text.ToUpper() + "%'"));
}
}
if (IncidentNumber.Length > 2)
{
Query.AddWhere("id_in_ref", Comparison.Equals, IncidentNumber);
}
if (oicincname.Length > 2)
{
Query.AddWhere("oic_inc_name", Comparison.Like, "%" + oicincname.ToUpper() + "%");
}
if (OfficerCallSign_search.Length > 1)
{
Query.AddWhere("MB_CALL_SIGN", Comparison.Equals, OfficerCallSign_search);
}
if (ADI_search.Length > 4)
{
Query.AddWhere("Adi_General", Comparison.Like, "%" + ADI_search + "%");
}
if (CauseofFire_search.Length > 3)
{
Query.AddWhere("fire_cause", Comparison.Like, "%" + CauseofFire_search + "%");
}
if (PropCat_search.Length > 1)
{
Query.AddWhere("property_category", Comparison.Equals, PropCat_search);
}
if (Propsub_search.Length > 1)
{
Query.AddWhere("lup_name", Comparison.Equals, Propsub_search);
}
if (Address_search.Length > 0)
{
Query.AddWhere("inc_location_address", Comparison.Like, "%" + Address_search + "%");
}
//else
//{
// Query.AddWhere("Revised_Address", Comparison.Like, "%" + RevisedAddress + "%");
//}
if (WatchCond > 0)
{
Query.AddWhere("WatchDesc", Comparison.Equals, Watch_search);
}
if (ward_db.Length > 2)
{
Query.AddWhere("[dmtreporting_dev].[dbo].[incidentdim].ward", Comparison.In, ward_db);
}
if (AreaCond.Length > 1)
{
Query.AddWhere("cluster", Comparison.Equals, AreaCond);
} //12/01/2015
if (RiskCond.Length > 1)
{
Query.AddWhere("[dmtreporting_dev].[dbo].[incidentdim].risk_cat", Comparison.In, RiskCond);
}
if (TargetCond.Length > 1)
{
Query.AddWhere("PFStatus", Comparison.Equals, TargetCond);
}
if (IncidentCat.Length > 3)
{
Query.AddWhere("incident_category", Comparison.Equals, IncidentCat);
}
if (EquipmentSelection.Length > 1)
{
Query.AddWhere("Equip_Description", Comparison.In, EquipmentSelection);
}
if (IRSCond.Length > 1)
{
Query.AddWhere("incident_stage", Comparison.Equals, IRSCond);
}
if (StationCond.Length > 2)
{
Query.AddWhere("[dmtreporting_dev].[dbo].[incidentdim].admin_st_ref", Comparison.Equals, StationCond);
}
if (BoroughCond.Length > 1)
{
Query.AddWhere("[dmtreporting_dev].[dbo].[incidentdim].borough", Comparison.Equals, BoroughCond);
}
if (StartDateCond.Length == 10 && EndDateCond == string.Empty)
{
Query.AddWhere("CONVERT(DATE,[dmtreporting_dev].[dbo].[incidentdim].inc_created_date)", Comparison.Equals,
new SqlLiteral("CONVERT(DATETIME," + "'" + StartDateCond + "'" + ",103)"));
}
if (StartDateCond.Length == 10)
{
Query.AddWhere("CONVERT(DATE,[dmtreporting_dev].[dbo].[incidentdim].inc_created_date)",
Comparison.GreaterOrEquals, new SqlLiteral("CONVERT(DATETIME," + "'" + StartDateCond + "'" + ",103)"));
}
if (EndDateCond == string.Empty)
{
}
else
{
Query.AddWhere("CONVERT(DATE,[dmtreporting_dev].[dbo].[incidentdim].inc_created_date)",
Comparison.LessOrEquals, new SqlLiteral("CONVERT(DATETIME," + "'" + EndDateCond + "'" + ",103)"));
}
if (SSType > 0)
{
Query.AddWhere("[dmtreporting_dev].[dbo].[incidentdim].ss_ter_type", Comparison.Equals, SSType_search);
}
if (SSClass > 0)
{
Query.AddWhere("[dmtreporting_dev].[dbo].[incidentdim].ss_sec_type", Comparison.Equals, SSClass_search);
}
if (TimeCond.Equals("1") && EndDateCond == string.Empty && StartDateCond == string.Empty)
{
Query.AddWhere("[dmtreporting_dev].[dbo].[incidentdim].inc_created_date", Comparison.GreaterOrEquals,
new SqlLiteral("Dateadd(Month, Datediff(Month, 0, DATEADD(m, -35, current_timestamp)), 0)"));
}
if (TimeCond.Equals("2") && EndDateCond == string.Empty && StartDateCond == string.Empty)
{
Query.AddWhere("[dmtreporting_dev].[dbo].[incidentdim].inc_created_date", Comparison.GreaterOrEquals,
new SqlLiteral("Dateadd(Month, Datediff(Month, 0, DATEADD(m, -24, current_timestamp)), 0)"));
}
if (TimeCond.Equals("3") && EndDateCond == string.Empty && StartDateCond == string.Empty)
{
Query.AddWhere("[dmtreporting_dev].[dbo].[incidentdim].inc_created_date", Comparison.GreaterOrEquals,
new SqlLiteral("Dateadd(Month, Datediff(Month, 0, DATEADD(m, -12, current_timestamp)), 0)"));
}
if (TimeCond.Equals("4") && EndDateCond == string.Empty && StartDateCond == string.Empty)
{
Query.AddWhere("[dmtreporting_dev].[dbo].[incidentdim].inc_created_date", Comparison.GreaterOrEquals,
new SqlLiteral("Dateadd(Month, Datediff(Month, 0, DATEADD(m, -6, current_timestamp)), 0)"));
}
if (TimeCond.Equals("5") && EndDateCond == string.Empty && StartDateCond == string.Empty)
{
Query.AddWhere("[dmtreporting_dev].[dbo].[incidentdim].inc_created_date", Comparison.GreaterOrEquals,
new SqlLiteral("Dateadd(Month, Datediff(Month, 0, DATEADD(m, -3, current_timestamp)), 0)"));
}
Query.AddOrderBy("[dmtreporting_dev].[dbo].[incidentdim].inc_created_date", Sorting.Descending);
SQL_Query = Query.BuildQuery();
return(SQL_Query);
}
/// <summary>
/// This request provides claninfo to the bf2server, to set the filter rules
/// for players joining the server.
/// </summary>
/// <queryParam name="type" type="int">The Listype (whitelist, blacklist)</queryParam>
/// <queryParam name ="clantag" type="string">Specified the required clantag</queryParam>
/// <queryParam name="score" type="int">The minimum required score</queryParam>
/// <queryParam name="rank" type="int">The minimum required ranked</queryParam>
/// <queryParam name="time" type="int">The minimum required global time</queryParam>
/// <queryParam name="kdratio" type="float">The minimum required kill/death ratio</queryParam>
/// <queryParam name="country" type="string">The country code (Ex: us, br, no) required, seperated by comma, that is required</queryParam>
/// <queryParam name="banned" type="int">Specified the maximum ban count to be accepted into the list</queryParam>
/// <param name="Client">The HttpClient who made the request</param>
/// <param name="Driver">The Stats Database Driver. Connection errors are handled in the calling object</param>
public GetClanInfo(HttpClient Client, StatsDatabase Database)
{
int Type = 0;
Dictionary<string, string> QueryString = Client.Request.QueryString;
Driver = Database;
// make sure we have a valid player ID
if (!QueryString.ContainsKey("type") || !Int32.TryParse(QueryString["type"], out Type))
{
Client.Response.WriteResponseStart(false);
Client.Response.WriteHeaderLine("asof", "err");
Client.Response.WriteDataLine(DateTime.UtcNow.ToUnixTimestamp(), "Invalid Syntax!");
Client.Response.Send();
return;
}
// Filler Variables
int I = 0;
float F;
string S;
List<DbParameter> Params = new List<DbParameter>();
// Prepare Query
SelectQueryBuilder Query = new SelectQueryBuilder(Driver);
Query.SelectColumns("id", "name");
Query.SelectFromTable("player");
Query.SetWhereOperator(LogicOperator.And);
Query.AddWhere("ip", Comparison.NotEqualTo, "0.0.0.0");
Query.AddOrderBy("id", Sorting.Ascending);
WhereClause Where = null;
switch (Type)
{
// Blacklist
case 0:
int BanLimit = (QueryString.ContainsKey("banned") && Int32.TryParse(QueryString["banned"], out I)) ? I : 100;
Where = new WhereClause("banned", Comparison.GreaterOrEquals, BanLimit);
Where.AddClause(LogicOperator.Or, "permban", Comparison.Equals, 1);
break;
// Whitelist
case 1:
if (QueryString.ContainsKey("clantag"))
{
Where = new WhereClause("clantag", Comparison.Equals, QueryString["clantag"]);
Where.AddClause(LogicOperator.And, "permban", Comparison.Equals, 0);
}
break;
// Greylist
case 2:
// List of possible query's
string[] Queries = new string[] { "score", "rank", "time", "kdratio", "country", "banned" };
foreach (string Param in Queries)
{
if (QueryString.ContainsKey(Param))
{
switch (Param)
{
case "score":
case "time":
case "rank":
if (Int32.TryParse(QueryString[Param], out I))
{
if (Where == null)
Where = new WhereClause(Param, Comparison.GreaterOrEquals, I);
else
Where.AddClause(LogicOperator.And, Param, Comparison.GreaterOrEquals, I);
}
break;
case "kdratio":
if (float.TryParse(QueryString["kdratio"], out F))
{
if (Where == null)
Where = new WhereClause("(kills / deaths)", Comparison.GreaterOrEquals, F);
else
Where.AddClause(LogicOperator.And, "(kills / deaths)", Comparison.GreaterOrEquals, F);
}
break;
case "country":
S = QueryString["country"].Replace(",", "','");
if (Where == null)
Where = new WhereClause(Param, Comparison.In, S.Split(','));
else
Where.AddClause(LogicOperator.And, Param, Comparison.In, S.Split(','));
break;
case "banned":
if (Int32.TryParse(QueryString["banned"], out I))
{
if(Where == null)
Where = new WhereClause("banned", Comparison.LessThan, I);
else
Where.AddClause(LogicOperator.And,"banned", Comparison.LessThan, I);
Where.AddClause(LogicOperator.And, "permban", Comparison.Equals, 0);
}
break;
}
}
}
break;
}
// Pepare 2 output headers
int size = 0;
FormattedOutput Output1 = new FormattedOutput("size", "asof");
FormattedOutput Output2 = new FormattedOutput("pid", "nick");
// Query the database, add each player to Output 2
if(Where != null) Query.AddWhere(Where);
List<Dictionary<string, object>> Players = Driver.ExecuteReader(Query.BuildCommand());
foreach (Dictionary<string, object> P in Players)
{
size++;
Output2.AddRow(P["id"].ToString(), P["name"].ToString());
}
// Send Response
Output1.AddRow(size, DateTime.UtcNow.ToUnixTimestamp());
Client.Response.AddData(Output1);
Client.Response.AddData(Output2);
Client.Response.Send();
}
private void ShowRankingType(MvcRoute Route)
{
// Create our model
RankingsTypeModel Model = new RankingsTypeModel(Client);
Model.UrlName = Route.Action;
string CacheName = $"rankings_{Route.Action}_1";
// Parse our country and page filters based on URL
// Url formats:
// - scoreType/country/pageNumber
// - scoreType/pageNumber
if (Route.Params.Length == 1)
{
if (Int32.TryParse(Route.Params[0], out Model.CurrentPage))
{
// Just a page number provided
CacheName = $"rankings_{Route.Action}_{Model.CurrentPage}";
}
else if (Route.Params[0].Length == 2)
{
// Just a country code provided, default to page 1
Model.Country = Route.Params[0];
CacheName = $"rankings_{Route.Action}_{Model.Country}_1";
}
}
else if (Route.Params.Length == 2 && Int32.TryParse(Route.Params[1], out Model.CurrentPage))
{
if (Route.Params[0].Length == 2) // Check valid country code
{
Model.Country = Route.Params[0];
CacheName = $"rankings_{Route.Action}_{Model.Country}_{Model.CurrentPage}";
}
else
{
CacheName = $"rankings_{Route.Action}_{Model.CurrentPage}";
}
}
// Check the cache file
if (!base.CacheFileExpired(CacheName, 30))
{
base.SendCachedResponse(CacheName);
return;
}
// NOTE: The HttpServer will handle the DbConnectException
using (StatsDatabase Database = new StatsDatabase())
{
// Get our DISTINCT country list from our player pool
SelectQueryBuilder builder = new SelectQueryBuilder(Database);
builder.SelectColumn("country");
builder.Distinct = true;
builder.SelectFromTable("player");
builder.AddWhere("country", Comparison.NotEqualTo, "xx");
foreach (var Row in builder.ExecuteQuery())
{
Model.CountryList.Add(Row["country"].ToString());
}
// Start building our player query
builder = new SelectQueryBuilder(Database);
builder.SelectCount();
builder.SelectFromTable("player");
WhereClause Where = builder.AddWhere("score", Comparison.GreaterOrEquals, 1);
// Add country filter
if (Model.Country.Length == 2)
{
Where.AddClause(LogicOperator.And, "country", Comparison.Equals, Model.Country);
}
// Hpd additional Where
if (Route.Action.Equals("hpd", StringComparison.InvariantCultureIgnoreCase))
{
Where.AddClause(LogicOperator.And, "time", Comparison.GreaterOrEquals, 3600);
}
// Get our total records
Model.TotalRecords = builder.ExecuteScalar <int>();
Model.TotalPages = 1 + (Model.TotalRecords / PlayersPerPage);
Model.ScoreHeader = GetHeaderName(Route.Action);
// Now, Build Query that will select the players, not just the count
bool isDecimal = false;
FinishQuery(Route.Action, builder, out isDecimal);
// Get our players, limiting to 50 and starting by page
builder.Limit(PlayersPerPage, (Model.CurrentPage * PlayersPerPage) - PlayersPerPage);
var Rows = builder.ExecuteQuery();
// Initialize records based on records returned from Database
Model.Records = new List <RankingsTypeModel.PlayerRow>(Rows.Count);
foreach (Dictionary <string, object> Player in Rows)
{
Model.Records.Add(new RankingsTypeModel.PlayerRow()
{
Pid = Int32.Parse(Player["pid"].ToString()),
Name = Player["name"].ToString(),
Rank = Int32.Parse(Player["rank"].ToString()),
Country = Player["country"].ToString(),
Time = Int32.Parse(Player["time"].ToString()),
ScorePerMin = Double.Parse(Player["spm"].ToString()),
KillDeathRatio = Double.Parse(Player["kdr"].ToString()),
WinLossRatio = Double.Parse(Player["wlr"].ToString()),
ScoreValue = (isDecimal)
? String.Format(CultureInfo.InvariantCulture, "{0:n4}", Player["value"])
: String.Format(CultureInfo.InvariantCulture, "{0:n0}", Player["value"])
});
}
}
// Send response
base.SendTemplateResponse("rankings_type", typeof(RankingsTypeModel), Model, CacheName);
}
/// <summary>
/// NarrativeLog Method: using the supplied incident number and the desired filter selected by the user, this method will retrieve the required dataset ready for populating the reports section
/// </summary>
/// <param name="incidentnumber">Requires you to pass the incident number to bring back relevant data (String)</param>
/// /// <param name="FilterIndex">Requires you to pass either 0 or depending on when the incident was (pre or post NWFC (int)</param>
public DataSet NarrativeLog(string incidentnumber, int FilterIndex)
{
//using the supplied incident number and the desired filter selected by the user, this method will retrieve the required dataset ready for populating the reports section
var Query = new SelectQueryBuilder();
string strSQLconnection;
SQLConnection ConnectToDataBase;
Query.SelectColumns("nar_act_time as 'Time'", "nar_log_text as 'Narrative Text'"); //Select Statement
Query.SelectFromTable("incidentdim"); //table name for the FROM section
Query.AddJoin(JoinType.LeftJoin, "[threetc_mis].[dbo].[nar_rative_log]", "nar_inc_id", Comparison.Equals, "incidentdim", "id_in_ref");
string IncidentDate = IncidentData.Tables[0].Rows[0]["Date Created"].ToString();
//string Date = IncidentDate.Substring(0, 10);
DateTime Incidentdt = Convert.ToDateTime(IncidentDate);
const string FireControlDate = "29/05/2014"; //date that we went to new control, used for narrative log formating
DateTime Controldt = Convert.ToDateTime(FireControlDate);
// int results = DateTime.Compare(dt, Fdt);
if (Incidentdt.Date < Controldt.Date && FilterIndex == 1)
{
Query.AddWhere("nar_log_entry", Comparison.Equals, "TEXT");
}
else
{
}
if (Incidentdt.Date > Controldt.Date && FilterIndex == 1)
{
WhereClause clause =
Query.AddWhere("nar_log_text", Comparison.Like, "%Message%");
clause.AddClause(LogicOperator.Or, Comparison.Like, "%INFORMATIVE%");
clause.AddClause(LogicOperator.Or, Comparison.Like, "%RADIO%");
clause.AddClause(LogicOperator.Or, Comparison.Like, "%ASSISTANCE%");
clause.AddClause(LogicOperator.Or, Comparison.Like, "%FROM%");
clause.AddClause(LogicOperator.Or, Comparison.Like, "%OTHER%");
}
else
{
}
Query.AddWhere("id_in_ref", Comparison.Equals, incidentnumber);
Query.AddOrderBy("nar_act_time", Sorting.Ascending);
var statement = Query.BuildQuery();
if (statement == null)
{
throw new ArgumentNullException("incidentnumber");
}
//connect to the database
ConnectToDataBase = new SQLConnection();
strSQLconnection = SQLConnect;
ConnectToDataBase.connection_string = strSQLconnection;
//set the sql statement to bind to the gridview
ConnectToDataBase.Sql = statement;
DataSet narDataset;
using (narDataset = ConnectToDataBase.GetConnection)
{
if (narDataset == null)
{
throw new ArgumentNullException("incidentnumber");
}
return(narDataset);
}
}
/// <summary>
/// AdvancedSearchQuery Method: Used for Basic and Advanced Search
/// </summary>
/// <param name="QueryType"> type 1 = Advanced search, type 2= basic search(String)</param>
public string AdvancedSearchQuery(int QueryType)
{
//used for Basic and Advanced Search
SelectQueryBuilder Query = new SelectQueryBuilder(); //creates the query reference
if (MaxRecords > 0)
{
//Query.TopRecords = MaxRecords;
Query.SelectColumns("Distinct id_in_ref as 'Incident Number'",
"[dmtreporting_dev].[dbo].[incidentdim].inc_created_date as' Date Created'",
"replace(inc_location_address,';','') as 'Location Address'",
"[dmtreporting_dev].[dbo].[incidentdim].borough as 'Borough'",
"[incident_category] AS 'Incident Type'", "incident_stage"); //Select Statement
}
else
{
Query.SelectColumns("Distinct id_in_ref as 'Incident Number'",
"[dmtreporting_dev].[dbo].[incidentdim].inc_created_date as'Date Created'",
"replace(inc_location_address,';','') as 'Location Address'", "incident_category as 'Incident Type'",
"[dmtreporting_dev].[dbo].[incidentdim].borough as 'Borough'", "incident_stage",
"LLCoordString", "id_easting", "id_northing", "ward");
}
Query.SelectFromTable("incidentdim");
if (QueryType == 1)
{
Query.AddJoin(JoinType.LeftJoin, "[dmtreporting_dev].[dbo].[PropertyDim]", "Lup_Pk", Comparison.Equals,
"incidentdim", "Inc_Property_Type");
Query.AddJoin(JoinType.LeftJoin, "[dmtreporting_dev].[dbo].[irs_additional_info]", "Adi_Fk_Inc",
Comparison.Equals, "incidentdim", "Inc_Pk");
Query.AddJoin(JoinType.LeftJoin, "[dmtreporting_dev].[dbo].[vw_IRS_Custom_Library]", "incidentnumber",
Comparison.Equals, "incidentdim", "id_in_ref");
Query.AddJoin(JoinType.LeftJoin, "[dmtreporting_dev].[dbo].[victimdim]", "vic_fk_inc", Comparison.Equals,
"incidentdim", "id_in_ref");
Query.AddJoin(JoinType.LeftJoin, "[dmtreporting_dev].[dbo].[MB_MOBILISATIONS]", "MB_IN_REF",
Comparison.Equals, "incidentdim", "id_in_ref");
Query.AddJoin(JoinType.LeftJoin, "Equipment_Used ", "Equ_Fk_Inc", Comparison.Equals, "incidentdim", "Inc_Pk");
Query.AddJoin(JoinType.LeftJoin, "[threetc_mis].[dbo].[nar_rative_log]", "[nar_inc_id]", Comparison.Equals,
"incidentdim", "id_in_ref");
}
if (TimeCond == "0")
{
TimeCond = "3";
}
else
{
TimeCond = TimeCond;
}
if (PumpCond == "1")
{
Query.AddWhere("num_vehicles_deployed", Comparison.Equals, PumpCond);
}
if (PumpCond == "2")
{
Query.AddWhere("num_vehicles_deployed", Comparison.GreaterOrEquals, "2");
Query.AddWhere("num_vehicles_deployed", Comparison.LessThan, "4");
}
if (PumpCond == "3")
{
Query.AddWhere("num_vehicles_deployed", Comparison.GreaterOrEquals, "3");
Query.AddWhere("num_vehicles_deployed", Comparison.LessThan, "5");
}
if (PumpCond == "4")
{
Query.AddWhere("num_vehicles_deployed", Comparison.GreaterOrEquals, "4");
Query.AddWhere("num_vehicles_deployed", Comparison.LessThan, "6");
}
if (PumpCond == "5")
{
Query.AddWhere("num_vehicles_deployed", Comparison.GreaterOrEquals, "6");
Query.AddWhere("num_vehicles_deployed", Comparison.LessThan, "9");
}
if (PumpCond == "6")
{
Query.AddWhere("num_vehicles_deployed", Comparison.GreaterOrEquals, "9");
}
var clause = new WhereClause();
if (FAGroup_int > 0)
{
Query.AddWhere("fa_group", Comparison.Equals, fagroup_db);
}
if (NarLogText.Length > 2)
{
if (Incidentdt.Date < Controldt.Date)
{
Query.AddWhere("nar_log_text", Comparison.Like, "TEXT");
}
else
{
Query.AddWhere("nar_log_entry", Comparison.Like, "Log Entry");
}
}
else
{
// do nothing
}
if (NarLogText.Length > 1 && NarrtiveSplit.Length < 2)
{
clause = Query.AddWhere("nar_log_text", Comparison.Like, new SqlLiteral("'%" + NarLogText.ToUpper() + "%'"));
}
if (NarLogText.Length > 1 && NarrtiveSplit.Length >= 2)
{
clause = Query.AddWhere("nar_log_text", Comparison.Like, "SQLworkaround");
foreach (string t in NarrtiveSplit)
{
clause.AddClause(LogicOperator.Or, Comparison.Like, "%" + t + "%");
}
}
else
{
if (NarLogText.Length == 0)
{
//clause = Query.AddWhere("nar_log_text", Comparison.Like, new SqlLiteral("'%" + tb_NarLogSearch.Text.ToUpper() + "%'"));
}
}
if (IncidentNumber.Length > 2)
{
Query.AddWhere("id_in_ref", Comparison.Equals, IncidentNumber);
}
if (oicincname.Length > 2)
{
Query.AddWhere("oic_inc_name", Comparison.Like, "%" + oicincname.ToUpper() + "%");
}
if (OfficerCallSign_search.Length > 1)
{
Query.AddWhere("MB_CALL_SIGN", Comparison.Equals, OfficerCallSign_search);
}
if (ADI_search.Length > 4)
{
Query.AddWhere("Adi_General", Comparison.Like, "%" + ADI_search + "%");
}
if (CauseofFire_search.Length > 3)
{
Query.AddWhere("fire_cause", Comparison.Like, "%" + CauseofFire_search + "%");
}
if (PropCat_search.Length > 1)
{
Query.AddWhere("property_category", Comparison.Equals, PropCat_search);
}
if (Propsub_search.Length > 1)
{
Query.AddWhere("lup_name", Comparison.Equals, Propsub_search);
}
if (Address_search.Length > 0)
{
Query.AddWhere("inc_location_address", Comparison.Like, "%" + Address_search + "%");
}
//else
//{
// Query.AddWhere("Revised_Address", Comparison.Like, "%" + RevisedAddress + "%");
//}
if (WatchCond > 0)
{
Query.AddWhere("WatchDesc", Comparison.Equals, Watch_search);
}
if (ward_db.Length > 2)
{
Query.AddWhere("[dmtreporting_dev].[dbo].[incidentdim].ward", Comparison.In, ward_db);
}
if (AreaCond.Length > 1)
{
Query.AddWhere("cluster", Comparison.Equals, AreaCond);
} //12/01/2015
if (RiskCond.Length > 1)
{
Query.AddWhere("[dmtreporting_dev].[dbo].[incidentdim].risk_cat", Comparison.In, RiskCond);
}
if (TargetCond.Length > 1)
{
Query.AddWhere("PFStatus", Comparison.Equals, TargetCond);
}
if (IncidentCat.Length > 3)
{
Query.AddWhere("incident_category", Comparison.Equals, IncidentCat);
}
if (EquipmentSelection.Length > 1)
{
Query.AddWhere("Equip_Description", Comparison.In, EquipmentSelection);
}
if (IRSCond.Length > 1)
{
Query.AddWhere("incident_stage", Comparison.Equals, IRSCond);
}
if (StationCond.Length > 2)
{
Query.AddWhere("[dmtreporting_dev].[dbo].[incidentdim].admin_st_ref", Comparison.Equals, StationCond);
}
if (BoroughCond.Length > 1)
{
Query.AddWhere("[dmtreporting_dev].[dbo].[incidentdim].borough", Comparison.Equals, BoroughCond);
}
if (StartDateCond.Length == 10 && EndDateCond == string.Empty)
{
Query.AddWhere("CONVERT(DATE,[dmtreporting_dev].[dbo].[incidentdim].inc_created_date)", Comparison.Equals,
new SqlLiteral("CONVERT(DATETIME," + "'" + StartDateCond + "'" + ",103)"));
}
if (StartDateCond.Length == 10)
{
Query.AddWhere("CONVERT(DATE,[dmtreporting_dev].[dbo].[incidentdim].inc_created_date)",
Comparison.GreaterOrEquals, new SqlLiteral("CONVERT(DATETIME," + "'" + StartDateCond + "'" + ",103)"));
}
if (EndDateCond == string.Empty)
{
}
else
{
Query.AddWhere("CONVERT(DATE,[dmtreporting_dev].[dbo].[incidentdim].inc_created_date)",
Comparison.LessOrEquals, new SqlLiteral("CONVERT(DATETIME," + "'" + EndDateCond + "'" + ",103)"));
}
if (SSType > 0)
{
Query.AddWhere("[dmtreporting_dev].[dbo].[incidentdim].ss_ter_type", Comparison.Equals, SSType_search);
}
if (SSClass > 0)
{
Query.AddWhere("[dmtreporting_dev].[dbo].[incidentdim].ss_sec_type", Comparison.Equals, SSClass_search);
}
if (TimeCond.Equals("1") && EndDateCond == string.Empty && StartDateCond == string.Empty)
{
Query.AddWhere("[dmtreporting_dev].[dbo].[incidentdim].inc_created_date", Comparison.GreaterOrEquals,
new SqlLiteral("Dateadd(Month, Datediff(Month, 0, DATEADD(m, -35, current_timestamp)), 0)"));
}
if (TimeCond.Equals("2") && EndDateCond == string.Empty && StartDateCond == string.Empty)
{
Query.AddWhere("[dmtreporting_dev].[dbo].[incidentdim].inc_created_date", Comparison.GreaterOrEquals,
new SqlLiteral("Dateadd(Month, Datediff(Month, 0, DATEADD(m, -24, current_timestamp)), 0)"));
}
if (TimeCond.Equals("3") && EndDateCond == string.Empty && StartDateCond == string.Empty)
{
Query.AddWhere("[dmtreporting_dev].[dbo].[incidentdim].inc_created_date", Comparison.GreaterOrEquals,
new SqlLiteral("Dateadd(Month, Datediff(Month, 0, DATEADD(m, -12, current_timestamp)), 0)"));
}
if (TimeCond.Equals("4") && EndDateCond == string.Empty && StartDateCond == string.Empty)
{
Query.AddWhere("[dmtreporting_dev].[dbo].[incidentdim].inc_created_date", Comparison.GreaterOrEquals,
new SqlLiteral("Dateadd(Month, Datediff(Month, 0, DATEADD(m, -6, current_timestamp)), 0)"));
}
if (TimeCond.Equals("5") && EndDateCond == string.Empty && StartDateCond == string.Empty)
{
Query.AddWhere("[dmtreporting_dev].[dbo].[incidentdim].inc_created_date", Comparison.GreaterOrEquals,
new SqlLiteral("Dateadd(Month, Datediff(Month, 0, DATEADD(m, -3, current_timestamp)), 0)"));
}
Query.AddOrderBy("[dmtreporting_dev].[dbo].[incidentdim].inc_created_date", Sorting.Descending);
SQL_Query = Query.BuildQuery();
return SQL_Query;
}
/// <summary>
/// Adds the is after date or equal.
/// </summary>
/// <param name="whereClause">The where clause.</param>
/// <param name="logicOperator">The logic operator.</param>
/// <param name="column">The column.</param>
/// <param name="date">The date.</param>
/// <returns>The clause.</returns>
public static WhereClause AddIsAfterDateOrEqual(this WhereClause whereClause, LogicOperator logicOperator, string column, DateTime date)
{
whereClause.AddClause(logicOperator, column, Comparison.GreaterOrEquals, date.ToString("yyyy/MM/dd", CultureInfo.InvariantCulture));
return(whereClause);
}
/// <summary>
/// Fills the DataGridView with a list of accounts
/// </summary>
private void BuildList()
{
// Define initial variables
int Limit = Int32.Parse(LimitSelect.SelectedItem.ToString());
int Start = (ListPage == 1) ? 0 : (ListPage - 1) * Limit;
string Like = SearchBox.Text.Replace("'", "").Trim();
WhereClause Where = null;
// Build Query
using (GamespyDatabase Driver = new GamespyDatabase())
{
SelectQueryBuilder Query = new SelectQueryBuilder(Driver);
Query.SelectColumns("id", "name", "email", "country", "lastip", "session");
Query.SelectFromTable("accounts");
Query.AddOrderBy(SortedCol.Name, ((SortDir == ListSortDirection.Ascending) ? Sorting.Ascending : Sorting.Descending));
Query.Limit(Limit, Start);
// User entered search
if (!String.IsNullOrWhiteSpace(Like))
{
Where = Query.AddWhere("name", Comparison.Like, "%" + Like + "%");
}
// Online Accounts
if (OnlineAccountsCheckBox.Checked)
{
if (Where == null)
{
Where = Query.AddWhere("session", Comparison.NotEqualTo, 0);
}
else
{
Where.AddClause(LogicOperator.And, "session", Comparison.NotEqualTo, 0);
}
}
// Clear out old junk
DataTable.Rows.Clear();
// Add players to data grid
int RowCount = 0;
foreach (Dictionary <string, object> Row in Driver.QueryReader(Query.BuildCommand()))
{
DataTable.Rows.Add(new string[] {
Row["id"].ToString(),
Row["name"].ToString(),
Row["email"].ToString(),
Row["country"].ToString(),
(Row["session"].ToString() != "0") ? "Yes" : "No",
//(Gamespy.GamespyServer.IsPlayerConnected(Int32.Parse(Row["id"].ToString())) ? "Yes" : "No"),
Row["lastip"].ToString(),
});
RowCount++;
}
// Get Filtered Rows
Query = new SelectQueryBuilder(Driver);
Query.SelectCount();
Query.SelectFromTable("accounts");
if (Where != null)
{
Query.AddWhere(Where);
}
int TotalFilteredRows = Query.ExecuteScalar <int>();
// Get Total Player Count, if the Where clause is null, this will be the same as the Filtered Row Count
int TotalRows = TotalFilteredRows;
if (Where != null)
{
Query = new SelectQueryBuilder(Driver);
Query.SelectCount();
Query.SelectFromTable("accounts");
TotalRows = Query.ExecuteScalar <int>();
}
// Stop Count
int Stop = (ListPage == 1) ? RowCount : ((ListPage - 1) * Limit) + RowCount;
// First / Previous button
if (ListPage == 1)
{
FirstBtn.Enabled = false;
PreviousBtn.Enabled = false;
}
else
{
FirstBtn.Enabled = true;
PreviousBtn.Enabled = true;
}
// Next / Last Button
LastBtn.Enabled = false;
NextBtn.Enabled = false;
// Get total number of pages
if (TotalFilteredRows / (ListPage * Limit) > 0)
{
float total = float.Parse(TotalFilteredRows.ToString()) / float.Parse(Limit.ToString());
TotalPages = Int32.Parse(Math.Floor(total).ToString());
if (TotalFilteredRows % Limit != 0)
{
TotalPages++;
}
LastBtn.Enabled = true;
NextBtn.Enabled = true;
}
// Set page number
PageNumber.Maximum = TotalPages;
PageNumber.Value = ListPage;
// Update Row Count Information
RowCountDesc.Text = String.Format(
"Showing {0} to {1} of {2} account{3}",
++Start,
Stop,
TotalFilteredRows,
((TotalFilteredRows > 1) ? "s " : " ")
);
// Add Total row count
if (!String.IsNullOrWhiteSpace(Like))
{
RowCountDesc.Text += String.Format("(filtered from " + TotalRows + " total account{0})", ((TotalRows > 1) ? "s" : ""));
}
// Update
DataTable.Update();
}
}
public void Test_UpdateManager()
{
IDbHelper dbHelper = DbHelperFactory.GetHelper(BaseSystemInfo.CenterDbConnectionString, BaseSystemInfo.CenterDbType);
UserManager userManager = new UserManager(dbHelper);
UserEntity userEntity = new UserEntity()
{
F1 = DateTime.Now.ToString(),
F2 = "update0",
F3 = "333U",
DateTimeType = DateTime.Now,
NumberType = new Random().Next(1000),
DoubleType = new Random().Next(1000) * 0.21,
FloatType = 1.23456789f,
DecimalType = 300.055M,
NullType = null,
};
userManager.Update(userEntity, 23078);
UserEntity userEntity1 = new UserEntity()
{
F1 = DateTime.Now.ToString(),
F2 = "update1",
F3 = "333U",
DateTimeType = DateTime.Now,
NumberType = new Random().Next(1000),
DoubleType = new Random().Next(1000) * 0.21,
FloatType = 1.23456789f,
DecimalType = 300.055M,
NullType = null,
};
userManager.Update(userEntity1, "2222", userEntity1.FieldF2);
UserEntity userEntity2 = new UserEntity()
{
F1 = DateTime.Now.ToString(),
F2 = "update2",
F3 = "333U2",
DateTimeType = DateTime.Now,
NumberType = 70,
DoubleType = 70.11,
FloatType = 1.23456789f,
DecimalType = 300.055M,
NullType = null,
};
WhereClause clause = new WhereClause(userEntity2.FieldDoubleType, Comparison.LessThan, 70);
clause.AddClause(LogicOperator.And, Comparison.GreaterThan, 40);
userManager.Update(clause, userEntity2);
UserEntity userEntity3 = new UserEntity();
List <KeyValuePair <string, object> > kvps = new List <KeyValuePair <string, object> >();
kvps.Add(new KeyValuePair <string, object>(userEntity3.FieldF1, "update3"));
userManager.Update(kvps, 23075);
UserEntity userEntity4 = new UserEntity();
List <KeyValuePair <string, object> > kvps1 = new List <KeyValuePair <string, object> >();
kvps1.Add(new KeyValuePair <string, object>(userEntity4.FieldF1, "update4"));
userManager.Update(kvps, 864, userEntity4.FieldNumberType);
}
public override void HandleRequest()
{
int Type = 0;
Dictionary <string, string> QueryString = Request.QueryString;
// make sure we have a valid player ID
if (!QueryString.ContainsKey("type") || !Int32.TryParse(QueryString["type"], out Type))
{
Response.WriteResponseStart(false);
Response.WriteHeaderLine("asof", "err");
Response.WriteDataLine(DateTime.UtcNow.ToUnixTimestamp(), "Invalid Syntax!");
Response.Send();
return;
}
// NOTE: The HttpServer will handle the DbConnectException
using (Database = new StatsDatabase())
{
// Filler Variables
int I = 0;
float F;
string S;
List <DbParameter> Params = new List <DbParameter>();
// Prepare Query
SelectQueryBuilder Query = new SelectQueryBuilder(Database);
Query.SelectColumns("id", "name");
Query.SelectFromTable("player");
Query.SetWhereOperator(LogicOperator.And);
Query.AddWhere("ip", Comparison.NotEqualTo, "0.0.0.0");
Query.AddOrderBy("id", Sorting.Ascending);
WhereClause Where = null;
switch (Type)
{
// Blacklist
case 0:
int BanLimit = (QueryString.ContainsKey("banned") && Int32.TryParse(QueryString["banned"], out I)) ? I : 100;
Where = new WhereClause("banned", Comparison.GreaterOrEquals, BanLimit);
Where.AddClause(LogicOperator.Or, "permban", Comparison.Equals, 1);
break;
// Whitelist
case 1:
if (QueryString.ContainsKey("clantag"))
{
Where = new WhereClause("clantag", Comparison.Equals, QueryString["clantag"]);
Where.AddClause(LogicOperator.And, "permban", Comparison.Equals, 0);
}
break;
// Greylist
case 2:
// List of possible query's
string[] Queries = new string[] { "score", "rank", "time", "kdratio", "country", "banned" };
foreach (string Param in Queries)
{
if (QueryString.ContainsKey(Param))
{
switch (Param)
{
case "score":
case "time":
case "rank":
if (Int32.TryParse(QueryString[Param], out I))
{
if (Where == null)
{
Where = new WhereClause(Param, Comparison.GreaterOrEquals, I);
}
else
{
Where.AddClause(LogicOperator.And, Param, Comparison.GreaterOrEquals, I);
}
}
break;
case "kdratio":
if (float.TryParse(QueryString["kdratio"], out F))
{
if (Where == null)
{
Where = new WhereClause("(kills / deaths)", Comparison.GreaterOrEquals, F);
}
else
{
Where.AddClause(LogicOperator.And, "(kills / deaths)", Comparison.GreaterOrEquals, F);
}
}
break;
case "country":
S = QueryString["country"].Replace(",", "','");
if (Where == null)
{
Where = new WhereClause(Param, Comparison.In, S.Split(','));
}
else
{
Where.AddClause(LogicOperator.And, Param, Comparison.In, S.Split(','));
}
break;
case "banned":
if (Int32.TryParse(QueryString["banned"], out I))
{
if (Where == null)
{
Where = new WhereClause("banned", Comparison.LessThan, I);
}
else
{
Where.AddClause(LogicOperator.And, "banned", Comparison.LessThan, I);
}
Where.AddClause(LogicOperator.And, "permban", Comparison.Equals, 0);
}
break;
}
}
}
break;
}
// Pepare 2 output headers
int size = 0;
FormattedOutput Output1 = new FormattedOutput("size", "asof");
FormattedOutput Output2 = new FormattedOutput("pid", "nick");
// Query the database, add each player to Output 2
if (Where != null)
{
Query.AddWhere(Where);
}
List <Dictionary <string, object> > Players = Database.ExecuteReader(Query.BuildCommand());
foreach (Dictionary <string, object> P in Players)
{
size++;
Output2.AddRow(P["id"].ToString(), P["name"].ToString());
}
// Send Response
Output1.AddRow(size, DateTime.UtcNow.ToUnixTimestamp());
Response.AddData(Output1);
Response.AddData(Output2);
Response.Send();
}
}