public List <EcrOrderLineData> GetOrderLineDetails(string orderId) { var paramList = new List <SqlParameter> { new SqlParameter("orderid", orderId) }; var orderLineData = QueryFunctions.DataTableFromStoredProcedure("SP_Order_GetOrderLineEcrVersions", paramList); var returnedItems = new List <EcrOrderLineData>(); if (orderLineData == null || orderLineData.Rows == null || orderLineData.Rows.Count <= 0) { return(returnedItems); } returnedItems.AddRange(from DataRow row in orderLineData.Rows select new EcrOrderLineData { MicrositeId = row["Microsite_Id"].ToString(), MicrositeName = row["MicrositeName"].ToString(), NewCheckoutEcrProductCode = (row["NCEcrProductCode"] != DBNull.Value)? row["NCEcrProductCode"].ToString() : string.Empty, NewCheckoutVersionId = (row["NewCKEcrVersionId"] != DBNull.Value) ? Convert.ToInt32(row["NewCKEcrVersionId"]) : 1, OrderLineId = row["Id"].ToString(), UseQrCode = Convert.ToBoolean(row["UseQR"]) }); return(returnedItems); }
public virtual List <TestTicket> GetTestTickets() { var dataTable = QueryFunctions.DataTableFromStoredProcedure("GetEcrTicketList"); if (dataTable == null || dataTable.Rows == null || dataTable.Rows.Count < 1) { return(null); } var ticketList = new List <TestTicket>(); foreach (DataRow row in dataTable.Rows) { ticketList.Add( new TestTicket { TicketId = row["Id"].ToString(), TicketName = row["Name"].ToString(), TicketType = row["TicketType"].ToString(), MicroSiteId = row["Microsite_Id"].ToString(), CurrencyCode = row["CurrencyCode"].ToString(), CurrencySymbol = row["CurrencySymbol"].ToString(), EcrProductCode = row["NCEcrProductCode"].ToString(), AdditionalDetailsCsv = row["DetailsCsv"].ToString(), MicroSiteEcrVersionId = Convert.ToInt32(row["MSEcrVersionid"]), } ); } return(ticketList); }
public void GetByWrongIdTest() { var id = Guid.NewGuid(); var result = QueryFunctions.GetByIdFunction <Ingredient>()(CocktailsContext.Ingredients, id).ToArray(); Assert.AreEqual(0, result.Length); }
public void GetIngredientsByWrongCategoryIdTest() { var categoryId = Guid.NewGuid(); var result = QueryFunctions.IngredientsByCategoryIdFunction(CocktailsContext.Ingredients, categoryId).ToArray(); Assert.AreEqual(0, result.Length); }
public void GetCocktailsByWrongNameTest() { const string nameKeyword = "Name "; var result = QueryFunctions.CocktailsByNameFunction(CocktailsContext.Cocktails, nameKeyword).ToArray(); Assert.AreEqual(0, result.Length); }
public override IEnumerable <dynamic> DefineViewList(IQueryable <Person> q) { var tagid = DbUtil.Db.PopulateTemporaryTag(q.Select(vv => vv.PeopleId)).Id; sql = sql.Replace("@p1", tagid.ToString()); var qf = new QueryFunctions(DbUtil.Db); return(qf.QuerySql($"{sql}\n{orderBy}", tagid)); }
public void ContributionCountTest() { var q = new QueryFunctions(db); var count = q.ContributionCount(7, "1"); count.ShouldBe(db.Contributions .Where(c => c.ContributionStatusId == ContributionStatusCode.Recorded) .Where(c => new[] { 6, 7, 8 }.Contains(c.ContributionTypeId) == false) .Where(c => c.CreatedDate > DateTime.Now.AddDays(-7).Date&& c.FundId == 1) .Count()); }
public void GetIngredientsByCategoryIdTest() { var rnd = new Random(); var index = rnd.Next(CocktailsContext.Ingredients.Count() - 1); var item = CocktailsContext.Ingredients.ToArray()[index]; var categoryId = item.CategoryId; var result = QueryFunctions.IngredientsByCategoryIdFunction(CocktailsContext.Ingredients, categoryId).ToArray(); Assert.AreEqual(1, result.Length); Assert.Contains(item, result); }
public ActionResult VitalStats() { var script = DbUtil.Db.ContentOfTypePythonScript("VitalStats"); if (!script.HasValue()) { script = System.IO.File.ReadAllText(Server.MapPath("/Content/VitalStats.py")); } ViewBag.table = script.Contains("class VitalStats") ? QueryFunctions.OldVitalStats(DbUtil.Db, script) : PythonModel.RunScript(DbUtil.Db.Host, script); return(View()); }
public void GetCocktailsByNameTest() { const string nameKeyword = "Name "; var item1 = CocktailsContext.Cocktails.ToArray()[0]; var item2 = CocktailsContext.Cocktails.ToArray()[1]; item1.Name = nameKeyword + 1; item2.Name = nameKeyword + 2; CocktailsContext.SaveChanges(); var result = QueryFunctions.CocktailsByNameFunction(CocktailsContext.Cocktails, nameKeyword).ToArray(); Assert.AreEqual(2, result.Length); Assert.Contains(item1, result); Assert.Contains(item2, result); }
public async Task <List <T> > DynamicQuery(List <string> filter, params Expression <Func <T, object> >[] includes) { IQueryable <T> query = this.Set; var builder = QueryFunctions.PredicateBuilder(filter); if (builder != null) { try { return(await query.Where(builder.Item1, builder.Item2).ToListAsync()); } catch (Exception) { throw; } } return(null); }
public Currency GetCurrencyFromMicrositeId(string micrositeId) { var row = QueryFunctions.DataRowFromStoredProcedure("GetMicrositeCurrency", "micrositeid", micrositeId); return((row != null)? new Currency { Id = (Guid)row["Id"], ISOCode = row["IsoCode"].ToString(), IsoNumericCode = row["IsoNumericCode"].ToString(), Name = row["Name"].ToString(), QrId = Convert.ToInt32(row["QrId"]), Symbol = row["Symbol"].ToString(), ConversionRate = Convert.ToDecimal(row["ConversionRate"]) } : null); }
public ActionResult VitalStats() { if (Util2.OrgLeadersOnly) { return(Redirect("/Home")); } var script = CurrentDatabase.ContentOfTypePythonScript("VitalStats"); if (!script.HasValue()) { script = System.IO.File.ReadAllText(Server.MapPath("/Content/VitalStats.py")); } ViewBag.table = script.Contains("class VitalStats") ? QueryFunctions.OldVitalStats(CurrentDatabase, script) : PythonModel.RunScript(CurrentDatabase.Host, script); return(View()); }
private bool IsMatch(LogItem item) { var match = _pattern.Match(item.Message); if (match.Success) { if (match.Captures.Length > 0) { // store captured field values for (var i = 0; i < _pattern.Captures.Length; i++) { var type = _fields[_pattern.Captures[i]]; if (type == typeof(DateTime?)) { item.Fields[_pattern.Captures[i]] = QueryFunctions.time(match.Captures[i].Value, _dateTimeFormat); } else { try { item.Fields[_pattern.Captures[i]] = Convert.ChangeType(match.Captures[i].Value, type, CultureInfo.InvariantCulture); } catch { return(false); } } } } // rewrite the message if requested if (!string.IsNullOrEmpty(_markerField) && !string.IsNullOrEmpty(_markerValue)) { item.Fields[_markerField] = _markerValue; } } return(match.Success); }
public List <FrontEndNavigationItem> GetNavigationBySiteAndSection(string site, string section, string languageid) { var paramsCollection = new List <SqlParameter> { new SqlParameter("micrositeid", site), new SqlParameter("section", section), new SqlParameter("languageid", languageid) }; var table = QueryFunctions.DataTableFromStoredProcedure("SP_NavigationItem_By_SiteSectionLanguage", paramsCollection); if (table == null || table.Rows == null || table.Rows.Count < 1) { return(null); } return((from DataRow row in table.Rows select new FrontEndNavigationItem { Text = row["Text"].ToString(), Url = row["URL"].ToString() }).ToList()); }
public void Like() { Assert.Throws <InvalidOperationException>(() => QueryFunctions.Like(@"\w+", "string")); }
public ActionResult Import(string text, string name) { int id = QueryFunctions.Import(DbUtil.Db, text, name); return(Redirect("/QueryBuilder/Main/" + id)); }
internal string AddParametersForSql(string parameter, string sql, DynamicParameters p, dynamic ViewBag) { if (sql.Contains("@qtagid", ignoreCase: true)) { var id = Db.FetchLastQuery().Id; var tag = Db.PopulateSpecialTag(id, DbUtil.TagTypeId_Query); int?qtagid = tag.Id; p.Add("@qtagid", qtagid); ViewBag.Type = "SqlReport"; } if (sql.Contains("@BlueToolbarTagId", ignoreCase: true)) { var id = Db.FetchLastQuery().Id; var tag = Db.PopulateSpecialTag(id, DbUtil.TagTypeId_Query); int?qtagid = tag.Id; p.Add("@BlueToolbarTagId", qtagid); ViewBag.Type = "SqlReport"; } else if (sql.Contains("@CurrentOrgId", ignoreCase: true)) { var oid = Db.CurrentSessionOrgId; p.Add("@CurrentOrgId", oid); if (oid > 0) { var name = Db.LoadOrganizationById(oid).FullName2; ViewBag.Name2 = name; ViewBag.Type = "SqlReport"; } } else if (sql.Contains("@OrgIds", ignoreCase: true)) { var oid = Db.CurrentSessionOrgId; if (oid != 0 || !p.Contains("@OrgIds")) { p.Add("@OrgIds", oid.ToString()); } ViewBag.Type = "OrgSearchSqlReport"; if (sql.Contains("--class=StartEndReport")) { p.Add("@MeetingDate1", DateTime.Now.AddDays(-90)); p.Add("@MeetingDate2", DateTime.Now); } } else if (sql.Contains("--class=TotalsByFund")) { ViewBag.Type = "TotalsByFundSqlReport"; p.Add("@StartDate", dbType: DbType.DateTime); p.Add("@EndDate", dbType: DbType.DateTime); p.Add("@CampusId", dbType: DbType.Int32); p.Add("@Online", dbType: DbType.Boolean); p.Add("@TaxNonTax", dbType: DbType.Boolean); p.Add("@FundSet", dbType: DbType.String); p.Add("@IncludeUnclosedBundles", dbType: DbType.Boolean); p.Add("@ActiveTagFilter", dbType: DbType.Int64); } else { ViewBag.Type = "SqlReport"; } if (sql.Contains("@StartDt")) { p.Add("@StartDt", new DateTime(DateTime.Now.Year, 1, 1)); } if (sql.Contains("@EndDt")) { p.Add("@EndDt", DateTime.Today); } if (sql.Contains("@userid", ignoreCase: true)) { p.Add("@userid", Util.UserId); } #if DEBUG foreach (var name in p.ParameterNames) { sql = QueryFunctions.RemoveDeclaration(name, sql); } #endif sql = QueryFunctions.AddP1Parameter(sql, parameter, p); return(sql); }
internal static string RunScriptSql(string parameter, string body, DynamicParameters p, dynamic ViewBag) { if (!CanRunScript(body)) { return("Not Authorized to run this script"); } if (body.Contains("@qtagid", ignoreCase: true)) { var id = DbUtil.Db.FetchLastQuery().Id; var tag = DbUtil.Db.PopulateSpecialTag(id, DbUtil.TagTypeId_Query); int?qtagid = tag.Id; p.Add("@qtagid", qtagid); ViewBag.Type = "SqlReport"; } if (body.Contains("@BlueToolbarTagId", ignoreCase: true)) { var id = DbUtil.Db.FetchLastQuery().Id; var tag = DbUtil.Db.PopulateSpecialTag(id, DbUtil.TagTypeId_Query); int?qtagid = tag.Id; p.Add("@BlueToolbarTagId", qtagid); ViewBag.Type = "SqlReport"; } else if (body.Contains("@CurrentOrgId", ignoreCase: true)) { var oid = DbUtil.Db.CurrentSessionOrgId; p.Add("@CurrentOrgId", oid); if (oid > 0) { var name = DbUtil.Db.LoadOrganizationById(oid).FullName2; ViewBag.Name2 = name; ViewBag.Type = "SqlReport"; } } else if (body.Contains("@OrgIds", ignoreCase: true)) { var oid = DbUtil.Db.CurrentSessionOrgId; p.Add("@OrgIds", oid.ToString()); ViewBag.Type = "OrgSearchSqlReport"; if (body.Contains("--class=StartEndReport")) { p.Add("@MeetingDate1", DateTime.Now.AddDays(-90)); p.Add("@MeetingDate2", DateTime.Now); } } else if (body.Contains("--class=TotalsByFund")) { ViewBag.Type = "TotalsByFundSqlReport"; p.Add("@StartDate", dbType: DbType.DateTime); p.Add("@EndDate", dbType: DbType.DateTime); p.Add("@CampusId", dbType: DbType.Int32); p.Add("@Online", dbType: DbType.Boolean); p.Add("@TaxNonTax", dbType: DbType.Boolean); p.Add("@FundSet", dbType: DbType.String); p.Add("@IncludeUnclosedBundles", dbType: DbType.Boolean); p.Add("@ActiveTagFilter", dbType: DbType.Int64); } else { ViewBag.Type = "SqlReport"; } if (body.Contains("@StartDt")) { p.Add("@StartDt", new DateTime(DateTime.Now.Year, 1, 1)); } if (body.Contains("@EndDt")) { p.Add("@EndDt", DateTime.Today); } if (body.Contains("@userid", ignoreCase: true)) { p.Add("@userid", Util.UserId); } body = QueryFunctions.AddP1Parameter(body, parameter, p); return(body); }
public ActionResult VitalStats() { ViewData["table"] = QueryFunctions.VitalStats(DbUtil.Db); return(View()); }
public ActionResult Import(string text, string name) { int id = QueryFunctions.Import(DbUtil.Db, text, name); return(Redirect("/Search/Advanced/Main/" + id)); }
public ActionResult RunScript(string script) { return(Content(QueryFunctions.RunScript(DbUtil.Db, script))); }
private static string FunctionToString(QueryFunctions function) { switch (function) { case QueryFunctions.Sum: return "SUM"; case QueryFunctions.Min: return "MIN"; case QueryFunctions.Max: return "MAX"; case QueryFunctions.Count: return "COUNT"; case QueryFunctions.Average: return "AVERAGE"; case QueryFunctions.ToLowerCase: return "LCASE"; case QueryFunctions.ToUpperCase: return "UCASE"; case QueryFunctions.StringLength: return "LENGTH"; } return String.Empty; }
public QueryFunction(string field, QueryFunctions function, string alias) { this.field = field; this.function = function; this.alias = alias; }
public QueryFunction(string field, QueryFunctions function) { this.field = field; this.function = function; }