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