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(); } }