public static void GroupQueryTest()
{
MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString);
object ret = null;
var q = context.Query <User>();
var r = q.GroupBy(a => a.Id).Having(a => a.Id > 1).Select(a => new { a.Id, Count = AggregateFunctions.Count(), Sum = AggregateFunctions.Sum(a.Id), Max = AggregateFunctions.Max(a.Id), Min = AggregateFunctions.Min(a.Id), Avg = AggregateFunctions.Average(a.Id) }).ToList();
q.GroupBy(a => a.Age).Having(a => a.Age > 1).Select(a => new { a.Age, Count = AggregateFunctions.Count(), Sum = AggregateFunctions.Sum(a.Age), Max = AggregateFunctions.Max(a.Age), Min = AggregateFunctions.Min(a.Age), Avg = AggregateFunctions.Average(a.Age) }).ToList();
var r1 = q.GroupBy(a => a.Age).Having(a => AggregateFunctions.Count() > 0).Select(a => new { a.Age, Count = AggregateFunctions.Count(), Sum = AggregateFunctions.Sum(a.Age), Max = AggregateFunctions.Max(a.Age), Min = AggregateFunctions.Min(a.Age), Avg = AggregateFunctions.Average(a.Age) }).ToList();
var g = q.GroupBy(a => a.Gender);
//g = g.ThenBy(a => a.Name);
//g = g.Having(a => a.Id > 0);
//g = g.Having(a => a.Name.Length > 0);
var gq = g.Select(a => new { Count = AggregateFunctions.Count() });
//gq = gq.Skip(1);
//gq = gq.Take(100);
//gq = gq.Where(a => a > -1);
ret = gq.ToList();
var c = gq.Count();
ConsoleHelper.WriteLineAndReadKey();
}
protected void PageInit()
{
try
{
if (userInfo == null)
{
MessageBox.Show(this, "system_alert", "用户未绑定");
return;
}
else
{
if (hotelInfo == null)
{
MessageBox.Show(this, "system_alert", "酒店信息异常");
return;
}
else
{
var layout = context.Query <CabinetLayout>().FirstOrDefault(o => o.hotel_id == hotelInfo.id);
if (layout == null)
{
MessageBox.Show(this, "system_alert", "模板商品未配置");
return;
}
var productCount = layout.products.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).Count();
var roomList = context.Query <Cabinet>().Where(o => o.hotel == hotelInfo.id)
.LeftJoin <Cell>((a, b) => a.mac.Equals(b.mac))
.Where((a, b) => b.part == 0 && b.product_id == null && b.pos <= productCount)
.Select((a, b) => new
{
a.mac,
a.online,
a.room
}).GroupBy(o => o.mac)
.Select(o => new { mac = AggregateFunctions.Max(o.mac), online = AggregateFunctions.Max(o.online), room = AggregateFunctions.Max(o.room) }).ToList();
rooms_rp.DataSource = roomList;
rooms_rp.DataBind();
if (roomList.Count() > 0)
{
roomSelectDiv.Visible = true;
empty_div.Visible = false;
}
else
{
roomSelectDiv.Visible = false;
empty_div.Visible = true;
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(this, "system_alert", "数据异常");
}
}
public static void AggregateQuery()
{
IQuery <User> q = context.Query <User>();
q.Select(a => AggregateFunctions.Count()).First();
/*
* SELECT COUNT(1) AS `C` FROM `Users` AS `Users` LIMIT 0,1
*/
q.Select(a => new { Count = AggregateFunctions.Count(), LongCount = AggregateFunctions.LongCount(), Sum = AggregateFunctions.Sum(a.Age), Max = AggregateFunctions.Max(a.Age), Min = AggregateFunctions.Min(a.Age), Average = AggregateFunctions.Average(a.Age) }).First();
/*
* SELECT COUNT(1) AS `Count`,COUNT(1) AS `LongCount`,CAST(SUM(`Users`.`Age`) AS SIGNED) AS `Sum`,MAX(`Users`.`Age`) AS `Max`,MIN(`Users`.`Age`) AS `Min`,AVG(`Users`.`Age`) AS `Average` FROM `Users` AS `Users` LIMIT 0,1
*/
var count = q.Count();
/*
* SELECT COUNT(1) AS `C` FROM `Users` AS `Users`
*/
var longCount = q.LongCount();
/*
* SELECT COUNT(1) AS `C` FROM `Users` AS `Users`
*/
var sum = q.Sum(a => a.Age);
/*
* SELECT CAST(SUM(`Users`.`Age`) AS SIGNED) AS `C` FROM `Users` AS `Users`
*/
var max = q.Max(a => a.Age);
/*
* SELECT MAX(`Users`.`Age`) AS `C` FROM `Users` AS `Users`
*/
var min = q.Min(a => a.Age);
/*
* SELECT MIN(`Users`.`Age`) AS `C` FROM `Users` AS `Users`
*/
var avg = q.Average(a => a.Age);
/*
* SELECT AVG(`Users`.`Age`) AS `C` FROM `Users` AS `Users`
*/
ConsoleHelper.WriteLineAndReadKey();
}
public static void AggregateQuery()
{
IQuery <User> q = context.Query <User>();
q.Select(a => AggregateFunctions.Count()).First();
/*
* SELECT TOP (1) COUNT(1) AS [C] FROM [Users] AS [Users]
*/
q.Select(a => new { Count = AggregateFunctions.Count(), LongCount = AggregateFunctions.LongCount(), Sum = AggregateFunctions.Sum(a.Age), Max = AggregateFunctions.Max(a.Age), Min = AggregateFunctions.Min(a.Age), Average = AggregateFunctions.Average(a.Age) }).First();
/*
* SELECT TOP (1) COUNT(1) AS [Count],COUNT_BIG(1) AS [LongCount],CAST(SUM([Users].[Age]) AS INT) AS [Sum],MAX([Users].[Age]) AS [Max],MIN([Users].[Age]) AS [Min],CAST(AVG([Users].[Age]) AS FLOAT) AS [Average] FROM [Users] AS [Users]
*/
var count = q.Count();
/*
* SELECT COUNT(1) AS [C] FROM [Users] AS [Users]
*/
var longCount = q.LongCount();
/*
* SELECT COUNT_BIG(1) AS [C] FROM [Users] AS [Users]
*/
var sum = q.Sum(a => a.Age);
/*
* SELECT CAST(SUM([Users].[Age]) AS INT) AS [C] FROM [Users] AS [Users]
*/
var max = q.Max(a => a.Age);
/*
* SELECT MAX([Users].[Age]) AS [C] FROM [Users] AS [Users]
*/
var min = q.Min(a => a.Age);
/*
* SELECT MIN([Users].[Age]) AS [C] FROM [Users] AS [Users]
*/
var avg = q.Average(a => a.Age);
/*
* SELECT CAST(AVG([Users].[Age]) AS FLOAT) AS [C] FROM [Users] AS [Users]
*/
ConsoleHelper.WriteLineAndReadKey();
}
public static void AggregateQuery()
{
IQuery <User> q = context.Query <User>();
q.Select(a => AggregateFunctions.Count()).First();
/*
* SELECT COUNT(1) AS "C" FROM "USERS" "USERS" WHERE ROWNUM < 2
*/
q.Select(a => new { Count = AggregateFunctions.Count(), LongCount = AggregateFunctions.LongCount(), Sum = AggregateFunctions.Sum(a.Age), Max = AggregateFunctions.Max(a.Age), Min = AggregateFunctions.Min(a.Age), Average = AggregateFunctions.Average(a.Age) }).First();
/*
* SELECT COUNT(1) AS "COUNT",COUNT(1) AS "LONGCOUNT",SUM("USERS"."AGE") AS "SUM",MAX("USERS"."AGE") AS "MAX",MIN("USERS"."AGE") AS "MIN",AVG("USERS"."AGE") AS "AVERAGE" FROM "USERS" "USERS" WHERE ROWNUM < 2
*/
var count = q.Count();
/*
* SELECT COUNT(1) AS "C" FROM "USERS" "USERS"
*/
var longCount = q.LongCount();
/*
* SELECT COUNT(1) AS "C" FROM "USERS" "USERS"
*/
var sum = q.Sum(a => a.Age);
/*
* SELECT SUM("USERS"."AGE") AS "C" FROM "USERS" "USERS"
*/
var max = q.Max(a => a.Age);
/*
* SELECT MAX("USERS"."AGE") AS "C" FROM "USERS" "USERS"
*/
var min = q.Min(a => a.Age);
/*
* SELECT MIN("USERS"."AGE") AS "C" FROM "USERS" "USERS"
*/
var avg = q.Average(a => a.Age);
/*
* SELECT AVG("USERS"."AGE") AS "C" FROM "USERS" "USERS"
*/
ConsoleHelper.WriteLineAndReadKey();
}
public CustomizeViewForm(IViewContext viewContext, ViewSpec viewSpec)
{
InitializeComponent();
ViewContext = viewContext;
ParentColumn = viewContext.ParentColumn;
_strikeThroughFont = new Font(listViewColumns.Font, FontStyle.Strikeout);
availableFieldsTreeColumns.RootColumn = ParentColumn;
ViewSpec = OriginalViewSpec = viewSpec;
tbxViewName.Text = viewSpec.Name;
ExistingCustomViewSpec =
viewContext.CustomViewSpecs.FirstOrDefault(customViewSpec => viewSpec.Name == customViewSpec.Name);
listViewColumns.SmallImageList = AggregateFunctions.GetSmallIcons();
}
public static void GroupQuery()
{
object ret = null;
var q = context.Query <TestEntity>();
var gq = q.GroupBy(a => a.F_Int32);
gq = gq.Having(a => a.F_Int32 > 1 && AggregateFunctions.Count() > 1);
ret = gq.Select(a => new { a.F_Int32, Count = AggregateFunctions.Count(), Sum = AggregateFunctions.Sum(a.F_Int32), Max = AggregateFunctions.Max(a.F_Int32), Min = AggregateFunctions.Min(a.F_Int32), Avg = AggregateFunctions.Average(a.F_Int32) }).ToList();
ConsoleHelper.WriteLineAndReadKey();
}
public static void GroupQuery()
{
IQuery <User> q = context.Query <User>();
IGroupingQuery <User> g = q.Where(a => a.Id > 0).GroupBy(a => a.Age);
g = g.Having(a => a.Age > 1 && AggregateFunctions.Count() > 0);
g.Select(a => new { a.Age, Count = AggregateFunctions.Count(), Sum = AggregateFunctions.Sum(a.Age), Max = AggregateFunctions.Max(a.Age), Min = AggregateFunctions.Min(a.Age), Avg = AggregateFunctions.Average(a.Age) }).ToList();
/*
* SELECT `Users`.`Age` AS `Age`,COUNT(1) AS `Count`,CAST(SUM(`Users`.`Age`) AS SIGNED) AS `Sum`,MAX(`Users`.`Age`) AS `Max`,MIN(`Users`.`Age`) AS `Min`,AVG(`Users`.`Age`) AS `Avg` FROM `Users` AS `Users` WHERE `Users`.`Id` > 0 GROUP BY `Users`.`Age` HAVING (`Users`.`Age` > 1 AND COUNT(1) > 0)
*/
ConsoleHelper.WriteLineAndReadKey();
}
public static void GroupQuery()
{
IQuery <User> q = context.Query <User>();
IGroupingQuery <User> g = q.Where(a => a.Id > 0).GroupBy(a => a.Age);
g = g.Having(a => a.Age > 1 && AggregateFunctions.Count() > 0);
g.Select(a => new { a.Age, Count = AggregateFunctions.Count(), Sum = AggregateFunctions.Sum(a.Age), Max = AggregateFunctions.Max(a.Age), Min = AggregateFunctions.Min(a.Age), Avg = AggregateFunctions.Average(a.Age) }).ToList();
/*
* SELECT [Users].[Age] AS [Age],COUNT(1) AS [Count],CAST(SUM([Users].[Age]) AS INT) AS [Sum],MAX([Users].[Age]) AS [Max],MIN([Users].[Age]) AS [Min],CAST(AVG([Users].[Age]) AS FLOAT) AS [Avg] FROM [Users] AS [Users] WHERE [Users].[Id] > 0 GROUP BY [Users].[Age] HAVING ([Users].[Age] > 1 AND COUNT(1) > 0)
*/
ConsoleHelper.WriteLineAndReadKey();
}
public static void GroupQuery()
{
IQuery <User> q = context.Query <User>();
IGroupingQuery <User> g = q.Where(a => a.Id > 0).GroupBy(a => a.Age);
g = g.Having(a => a.Age > 1 && AggregateFunctions.Count() > 0);
g.Select(a => new { a.Age, Count = AggregateFunctions.Count(), Sum = AggregateFunctions.Sum(a.Age), Max = AggregateFunctions.Max(a.Age), Min = AggregateFunctions.Min(a.Age), Avg = AggregateFunctions.Average(a.Age) }).ToList();
/*
* SELECT "USERS"."AGE" AS "AGE",COUNT(1) AS "COUNT",SUM("USERS"."AGE") AS "SUM",MAX("USERS"."AGE") AS "MAX",MIN("USERS"."AGE") AS "MIN",AVG("USERS"."AGE") AS "AVG" FROM "USERS" "USERS" WHERE "USERS"."ID" > 0 GROUP BY "USERS"."AGE" HAVING ("USERS"."AGE" > 1 AND COUNT(1) > 0)
*/
ConsoleHelper.WriteLineAndReadKey();
}
protected void PageInit()
{
try
{
if (userInfo == null)
{
MessageBox.Show(this, "system_alert", "用户未绑定");
return;
}
else
{
if (hotelInfo == null)
{
MessageBox.Show(this, "system_alert", "酒店信息异常");
return;
}
else
{
var roomList = context.Query <Cabinet>().Where(o => o.hotel == hotelInfo.id).LeftJoin <Cell>((a, b) => a.mac.Equals(b.mac))
.Where((a, b) => b.part == 0 && b.product_id == null)
.Select((a, b) => new
{
a.mac,
a.online,
a.room
}).GroupBy(o => o.mac)
.Select(o => new { mac = AggregateFunctions.Max(o.mac), online = AggregateFunctions.Max(o.online), room = AggregateFunctions.Max(o.room) }).ToList();
rooms_rp.DataSource = roomList;
rooms_rp.DataBind();
if (roomList.Count() > 0)
{
roomSelectDiv.Visible = true;
empty_div.Visible = false;
}
else
{
roomSelectDiv.Visible = false;
empty_div.Visible = true;
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(this, "system_alert", "数据异常");
}
}
protected List <SaleProduct> Query(Expression <Func <OrderInfo, bool> > _where, out decimal daySale, out decimal totalAmount)
{
var cabinetList = context.Query <Cabinet>().Where(o => o.hotel == hotelInfo.id).ToList();
var query = context.Query <OrderInfo>().Where(_where).LeftJoin <Product>((a, b) => a.product == b.id)
.Select((a, b) => new SaleProduct
{
name = b.name,
code = b.code,
price1 = a.price1,
id = a.product,
saleTime = a.date
});
var groupQuery = query.GroupBy(o => o.id).Select(o => new SaleProduct
{
minTime = AggregateFunctions.Min(o.saleTime),
maxTime = AggregateFunctions.Max(o.saleTime),
name = AggregateFunctions.Max(o.name),
code = AggregateFunctions.Max(o.code),
salesCount = AggregateFunctions.Count(),
totalAmount = AggregateFunctions.Sum(o.price1)
});
var list = groupQuery.ToList();
if (list.Count != 0)
{
totalAmount = list.Sum(o => o.totalAmount).ObjToInt(0).CentToRMB(0);
var totalDay = list.Max(o => o.maxTime).Subtract(list.Min(o => o.minTime)).Days;
if (totalDay != 0 && cabinetList.Count != 0)
{
daySale = Math.Round(totalAmount / totalDay / cabinetList.Count);
}
else
{
daySale = 0;
}
}
else
{
daySale = 0;
totalAmount = 0;
}
return(list);
}
public static void AggregateFunctionTest()
{
MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString);
var q = context.Query <User>();
q = q.Where(a => a.Id > 0);
var xxx = q.Select(a => AggregateFunctions.Count()).First();
q.Select(a => new { Count = AggregateFunctions.Count(), LongCount = AggregateFunctions.LongCount(), Sum = AggregateFunctions.Sum(a.Age), Max = AggregateFunctions.Max(a.Age), Min = AggregateFunctions.Min(a.Age), Average = AggregateFunctions.Average(a.Age) }).First();
var count = q.Count();
var longCount = q.LongCount();
var sum = q.Sum(a => a.Age);
var max = q.Max(a => a.Age);
var min = q.Min(a => a.Age);
var avg = q.Average(a => a.Age);
Console.WriteLine(1);
}
protected void PageInit(string sort = "down")
{
var list = context.Query <Cabinet>().Where(o => o.hotel == hotelInfo.id).LeftJoin <OrderInfo>((a, b) => a.mac.Equals(b.cabinet_mac))
.Select((a, b) => new CabinetQuery()
{
mac = a.mac,
room = a.room,
last_offline_date = a.last_offline_date,
online = a.online,
salesAmount = b.price1
})
.GroupBy(o => o.mac)
.Select(o => new {
mac = AggregateFunctions.Max(o.mac),
room = AggregateFunctions.Max(o.room),
offline = AggregateFunctions.Max(o.last_offline_date),
online = AggregateFunctions.Max(o.online),
salesAmount = AggregateFunctions.Sum(o.salesAmount)
}).ToList()
.Select(
o => new {
mac = o.mac,
room = o.room,
online = o.online,
offline = (o.online.HasValue && o.online.Value) ? "--" : o.offline.HasValue ? (DateTime.Now - o.offline.Value).Hours + " H" : "999 H",
salesAmount = o.salesAmount
}
).ToList();
room_count.InnerHtml = list.Count(o => o.salesAmount > 0).ObjToStr();
if (sort.Equals("up"))
{
psy_rp.DataSource = list.OrderBy(o => o.salesAmount);
psy_rp.DataBind();
}
else if (sort.Equals("down"))
{
psy_rp.DataSource = list.OrderByDescending(o => o.salesAmount);
psy_rp.DataBind();
}
}
protected void PageInit()
{
var layout = context.Query <CabinetLayout>().FirstOrDefault(o => o.hotel_id == hotelInfo.id);
if (layout == null)
{
MessageBox.Show(this, "system_alert", "酒店未设置商品");
return;
}
else
{
var products = layout.products.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).Select(o => o.ObjToInt(0)).ToList();
var query = context.Query <Product>()
.LeftJoin <OrderInfo>((a, b) => a.id == b.product)
.LeftJoin <Cabinet>((a, b, c) => c.mac == b.cabinet_mac)
.Where((a, b, c) => (c.hotel == hotelInfo.id || c.hotel == null) && products.Contains(a.id))
.Select((a, b, c) => new {
id = a.id,
image = a.image,
price1 = a.price1,
code = a.code,
name = a.name,
salePrice = b.price1
}).GroupBy(o => o.id)
.Select(o => new {
id = o.id,
name = o.name,
code = o.code,
image = o.image,
price1 = o.price1,
salesAmount = AggregateFunctions.Sum(o.salePrice)
});
var list = query.ToList();
goods_rp.DataSource = list;
goods_rp.DataBind();
goods_count.InnerText = (list.Count).ObjToStr();
}
}
protected List <SaleProduct> Query(Expression <Func <OrderInfo, bool> > _where, out int totalSum, out decimal totalAmount)
{
var query = context.Query <OrderInfo>().Where(_where).LeftJoin <Product>((a, b) => a.product == b.id)
.Select((a, b) => new SaleProduct
{
name = b.name,
code = b.code,
price1 = a.price1,
id = a.product,
saleTime = a.date
});
var groupQuery = query.GroupBy(o => o.id).Select(o => new SaleProduct
{
minTime = AggregateFunctions.Min(o.saleTime),
maxTime = AggregateFunctions.Max(o.saleTime),
name = AggregateFunctions.Max(o.name),
code = AggregateFunctions.Max(o.code),
salesCount = AggregateFunctions.Count(),
totalAmount = AggregateFunctions.Sum(o.price1)
});
var list = groupQuery.ToList();
if (list.Count != 0)
{
totalAmount = list.Sum(o => o.totalAmount).ObjToInt(0).CentToRMB(0);
totalSum = list.Sum(o => o.salesCount).ObjToInt(0);
}
else
{
totalSum = 0;
totalAmount = 0;
}
return(list);
}
public bool IsOKPeriod(AddPeriodTimeInput p)
{
return(this.DbContext.Query <PeriodTime>().Where(a => a.SeveraWeeks == p.SeveraWeeks && a.StratTime == p.StratTime && a.EndTime == p.EndTime).Select(a => AggregateFunctions.Count()).First() == 0);
}
public int SelectNumforKeyName(string Name)
{
return(this.DbContext.Query <MALU_DefaultSetting>().Where(a => a.KeyName == Name).Select(a => AggregateFunctions.Count()).First());
}
public AggregateField(string field, AggregateFunctions aggregateFunction)
{
this.Field = field;
this.AggregateFunction = aggregateFunction;
}
public AppointmentData Add(AddAppointmentDataInput input)
{
AppointmentData entity = new AppointmentData();
string _Code = (this.DbContext.Query <AppointmentData>().Where(a => a.BusinessID == input.BusinessID).Select(a => AggregateFunctions.Count()).First() + 1).ToString();
while (_Code.Length < 4)
{
_Code = "0" + _Code;
}
entity.Id = IdHelper.CreateGuid();
entity.CreateTime = DateTime.Now;
entity.AppointmentDate = input.AppointmentDate;
entity.BusinessID = input.BusinessID;
entity.FileID = input.FileID;
entity.MALU_Code = input.Code + _Code;
entity.MamberID = input.MamberID;
entity.State = input.State;
return(this.DbContext.Insert(entity));
}
/// <summary>
/// Initializes a new instance of a <see cref="AggregateField"/>.
/// </summary>
/// <param name="function">The <see cref="AggregateFunctions">Aggregate Function</see></param>
/// <param name="name">The Field Name</param>
/// <param name="tableAlias">The Table Alias</param>
/// <param name="alternateName">An alternative name to give to the field</param>
public AggregateField(AggregateFunctions function, string name, string tableAlias, string alternateName) : this(function, name, tableAlias)
{
AlternateName = alternateName;
}
/// <summary>
/// Initializes a new instance of a <see cref="AggregateField"/>.
/// </summary>
/// <param name="function">The <see cref="AggregateFunctions">Aggregate Function</see></param>
/// <param name="name">The Field Name</param>
/// <param name="tableAlias">The Table Alias</param>
public AggregateField(AggregateFunctions function, string name, string tableAlias) : this(function, name)
{
TableAlias = tableAlias;
}
/// <summary>
/// Initializes a new instance of a <see cref="AggregateField"/>.
/// </summary>
/// <param name="function">The <see cref="AggregateFunctions">Aggregate Function</see></param>
/// <param name="name">The Field Name</param>
public AggregateField(AggregateFunctions function, string name) : base(name)
{
Function = function;
base.Type = FieldTypes.Aggregate;
}
protected override IReadOnlyList<OracleColumn> BuildColumns()
{
var columns = new List<OracleColumn>();
var pivotColumns = new List<OracleSelectListColumn>();
var pivotForColumnList = PivotClause[NonTerminals.PivotForClause, NonTerminals.IdentifierOrParenthesisEnclosedIdentifierList];
if (pivotForColumnList != null)
{
var aggregatedColumnNames =
SourceReferenceContainer.ColumnReferences
.Where(c => AggregateFunctions.Any(f => f.SourcePosition.Contains(c.RootNode.SourcePosition)))
.Select(c => c.NormalizedName);
var groupingColumns = pivotForColumnList
.GetDescendants(Terminals.Identifier)
.Select(i => i.Token.Value.ToQuotedIdentifier())
.Concat(aggregatedColumnNames)
.ToHashSet();
switch (PivotClause.Id)
{
case NonTerminals.PivotClause:
var sourceColumns = SourceReference.Columns
.Where(c => !groupingColumns.Contains(c.Name))
.Select(c => c.Clone());
columns.AddRange(sourceColumns);
var withXmlTransformation = PivotClause[Terminals.Xml] != null;
if (withXmlTransformation)
{
var xmlColumnName = String.Join("_", groupingColumns.Select(c => c.Trim('"')));
if (xmlColumnName.Length > 26)
{
xmlColumnName = xmlColumnName.Substring(0, 26);
}
var xmlColumn =
new OracleColumn
{
Name = $"\"{xmlColumnName}_XML\"",
DataType = OracleDataType.XmlType,
Nullable = true
};
columns.Add(xmlColumn);
}
else
{
pivotColumns.AddRange(ResolvePivotColumns());
columns.AddRange(pivotColumns.Select(c => c.ColumnDescription));
}
break;
case NonTerminals.UnpivotClause:
var unpivotColumnSources = new List<StatementGrammarNode>();
var unpivotedColumns = new HashSet<string>();
var unpivotColumnSelectorValues = new List<StatementGrammarNode>();
var columnTransformations = PivotClause[NonTerminals.UnpivotInClause].GetDescendants(NonTerminals.UnpivotValueToColumnTransformationList);
var groupingColumnsNullable = false;
foreach (var columnTransformation in columnTransformations)
{
unpivotedColumns.UnionWith(columnTransformation.GetDescendants(Terminals.Identifier).Select(t => t.Token.Value.ToQuotedIdentifier()));
var columnSelectorValue = columnTransformation[NonTerminals.UnpivotValueSelector, NonTerminals.NullOrStringOrNumberLiteralOrParenthesisEnclosedStringOrIntegerLiteralList];
if (columnSelectorValue != null)
{
unpivotColumnSelectorValues.Add(columnSelectorValue);
groupingColumnsNullable |= columnSelectorValue.TerminalCount == 1 || String.Equals(columnSelectorValue.FirstTerminalNode.Id, Terminals.Null);
}
unpivotColumnSources.AddIfNotNull(columnTransformation[NonTerminals.IdentifierOrParenthesisEnclosedIdentifierList]);
}
var unpivotColumnDataTypes = OracleDataType.FromUnpivotColumnSelectorValues(unpivotColumnSelectorValues);
AreUnpivotColumnSelectorValuesValid = unpivotColumnDataTypes != null;
UnpivotColumnSelectorValues = unpivotColumnSelectorValues.AsReadOnly();
UnpivotColumnSources = unpivotColumnSources.AsReadOnly();
columns.AddRange(SourceReference.Columns
.Where(c => !unpivotedColumns.Contains(c.Name))
.Select(c => c.Clone()));
columns.AddRange(groupingColumns.Select(
(c, i) =>
new OracleColumn
{
Name = c,
Nullable = groupingColumnsNullable,
DataType = groupingColumns.Count == unpivotColumnDataTypes?.Count
? unpivotColumnDataTypes[i]
: OracleDataType.Empty
}));
var unpivotColumnsNullable = PivotClause[NonTerminals.UnpivotNullsClause, NonTerminals.IncludeOrExclude, Terminals.Include] != null;
var unpivotColumns = PivotClause[NonTerminals.IdentifierOrParenthesisEnclosedIdentifierList].GetDescendants(Terminals.Identifier);
columns.AddRange(
unpivotColumns.Select(
i => new OracleColumn
{
Name = i.Token.Value.ToQuotedIdentifier(),
DataType = OracleDataType.Empty,
Nullable = unpivotColumnsNullable
}));
break;
}
}
PivotColumns = pivotColumns.AsReadOnly();
return columns.AsReadOnly();
}
public List <SelBusinessInput> GetBusListByPlace(string PlaceId)
{
var q = this.DbContext.Query <MALU_Business>().LeftJoin(this.DbContext.Query <PlaceInfo>(), (b, p) => b.PlaceId == p.Id)
.LeftJoin(this.DbContext.Query <PeriodTime>(), (b, p, per) => b.PeriodTimeID == per.Id).
LeftJoin(this.DbContext.Query <TransactionInfo>(), (b, p, per, t) => b.TransactionID == t.Id);
List <SelBusinessInput> view = q.Select((b, p, per, t) => new SelBusinessInput
{
Id = b.Id,
AppointmentNum = b.AppointmentNum,
TransactionID = b.TransactionID,
PlaceName = p.PlaceName,
PlaceAdderss = p.Address,
PlaceId = b.PlaceId,
TranName = t.TransactionName,
PeriodTimeId = per.Id,
PeriodTime = per.StratTime + "-" + per.EndTime,
}).Where(a => a.PlaceId == PlaceId).ToList();
for (int i = 0; i < view.Count; i++)
{
string BiD = view[i].Id;
view[i].LineUpNumber = this.DbContext.Query <AppointmentData>().Where(a => a.BusinessID == BiD && a.State != -1).Select(a => AggregateFunctions.Count()).First();
try
{
view[i].NowAppCode = this.DbContext.Query <AppointmentData>().Where(a => a.BusinessID == BiD && a.State == 1).Select(a => a.MALU_Code).First();
}
catch {
view[i].NowAppCode = "";
}
}
return(view);
}
