public List <Movimiento> GetFacturasByRangoFecha(int IdEmpresa, DateTime FechaIni, DateTime FechaFin)
{
try
{
SiinErpContext context = new SiinErpContext();
List <Movimiento> Lista = (from f in context.Movimientos.Where(x => x.IdEmpresa == IdEmpresa && x.FechaDoc >= FechaIni && x.FechaDoc <= FechaFin && x.CodModulo.Equals(Constantes.ModuloVentas) && x.Estado.Equals(Constantes.EstadoActivo))
join c in context.Terceros on f.IdTercero equals c.IdTercero into LeftJoin
from LJ in LeftJoin.DefaultIfEmpty()
select new Movimiento()
{
IdMovimiento = f.IdMovimiento,
TipoDoc = f.TipoDoc,
NumDoc = f.NumDoc,
IdTercero = f.IdTercero,
IdVendedor = f.IdVendedor,
FechaDoc = f.FechaDoc,
sFechaFormatted = f.FechaDoc.ToString("MM/dd/yyyy"),
ValorNeto = f.ValorNeto,
IdDetAlmacen = f.IdDetAlmacen,
NombreTercero = LJ != null ? LJ.NombreTercero : "",
}).ToList();
return(Lista);
}
catch (Exception ex)
{
errorBusiness.Create("GetFacturasByFecha", ex.Message, null);
throw;
}
}
public List <MovimientoFormaPago> GetMovimientoFormasPago(int IdMovimiento)
{
try
{
SiinErpContext context = new SiinErpContext();
List <MovimientoFormaPago> Lista = (from mfp in context.MovimientosFormasPagos.Where(x => x.IdMovimiento == IdMovimiento)
join fp in context.TablasDetalles on mfp.IdDetFormaDePago equals fp.IdDetalle
join cb in context.TablasDetalles on mfp.IdDetCuenta equals cb.IdDetalle into LeftJoin
from LF in LeftJoin.DefaultIfEmpty()
select new MovimientoFormaPago()
{
IdMovFormaDePago = mfp.IdMovFormaDePago,
IdMovimiento = mfp.IdMovimiento,
IdDetFormaDePago = mfp.IdDetFormaDePago,
Descripcion = fp.Descripcion,
IdDetCuenta = mfp.IdDetCuenta,
DescripcionCuenta = LF == null ? "" : LF.Descripcion,
Valor = mfp.Valor,
Orden = fp.Orden,
}).OrderBy(x => x.Descripcion).OrderBy(x => x.Orden).ToList();
return(Lista);
}
catch (Exception ex)
{
throw;
}
}
public List <Movimiento> GetMovimientosByModificable(int IdEmp)
{
try
{
SiinErpContext context = new SiinErpContext();
List <Movimiento> Lista = (from tip in context.TiposDocumentos
join mov in context.Movimientos.Where(x => x.IdEmpresa == IdEmp && x.CodModulo == Constantes.ModuloVentas && x.Estado.Equals(Constantes.EstadoActivo)) on tip.TipoDoc equals mov.TipoDoc
join ter in context.Terceros on mov.IdTercero equals ter.IdTercero into LeftJoin
from LJ in LeftJoin.DefaultIfEmpty()
select new Movimiento()
{
IdMovimiento = mov.IdMovimiento,
TipoDoc = mov.TipoDoc,
NumDoc = mov.NumDoc,
FechaDoc = mov.FechaDoc,
ValorNeto = mov.ValorNeto,
Estado = mov.Estado,
IdTercero = mov.IdTercero,
IdVendedor = mov.IdVendedor,
IdDetAlmacen = mov.IdDetAlmacen,
NombreTercero = LJ != null ? LJ.NombreTercero : "",
}).OrderByDescending(x => x.FechaDoc).ToList();
return(Lista);
}
catch (Exception ex)
{
errorBusiness.Create("GetMovimientosByModificable", ex.Message, null);
throw;
}
}
/**
* @ 初始化SQL命令，准备执行
* */
protected override bool InitSQLWithCmdText()
{
if (TableName.IsNullOrEmpty())
{
throw new ArgumentNullException("必须设置属性TableName的值，该值为查询的主表名称");
}
if (LeftJoin.IsNotNullOrEmpty() && TableAlias.IsNullOrEmpty())
{
throw new ArgumentNullException("当存在多表连接查询时，必须指定主表的别名，即属性TableAlias的值");
}
if (Fields.IsNullOrEmpty())
{
throw new ArgumentNullException("必须设置要查询的字段fields");
}
string alias = string.Empty;
if (TableAlias.IsNotNullOrEmpty())
{
alias = string.Format("AS {0}", TableAlias);
}
string whereString = GetCondition();
SQLCmdText = string.Format(@"SELECT {0} FROM {1} {2} {3} {4} {5} {6}", Fields.ToJoin(), TableName, alias, LeftJoin, whereString, OrderBy, GroupBy);
Succeed = true;
return(Succeed);
}
public void Throw_Exception_If_Left_Table_Is_Empty()
{
HashMap <string> leftTable = new HashMap <string>(1024);
HashMap <string> rightTable = RightTable();
Assert.Throws <ArgumentException>(() =>
LeftJoin.Join(leftTable, rightTable));
}
public void Left_join_empty_tables()
{
var table1 = new HashTable <string>(10);
var table2 = new HashTable <string>(10);
var join = new LeftJoin(table1, table2);
var result = join.LeftJoinTables();
Assert.Empty(result);
Assert.False(result.Contains(""));
}
public void Left_join_tables_left_joins_tables(string x)
{
var table1 = new HashTable <string>(10);
table1.Add("fond", "enamored");
table1.Add("wrath", "anger");
table1.Add("diligent", "employed");
table1.Add("outfit", "garb");
table1.Add("guide", "usher");
var table2 = new HashTable <string>(10);
table2.Add("fond", "averse");
table2.Add("wrath", "delight");
table2.Add("diligent", "idle");
table2.Add("guide", "follow");
table2.Add("flow", "jam");
var join = new LeftJoin(table1, table2);
var expect = new HashTable <List <string> >(10);
expect.Add("fond", new List <string>()
{
"enamored", "averse"
});;
expect.Add("wrath", new List <string>()
{
"anger", "delight"
});
expect.Add("diligent", new List <string>()
{
"employed", "idle"
});
expect.Add("outfit", new List <string>()
{
"garb"
});
expect.Add("guide", new List <string>()
{
"usher", "follow"
});
var result = join.LeftJoinTables();
Assert.True(expect.TryFind(x, out var y));
Assert.True(result.TryFind(x, out var z));
//Assert.Equal(expect, result);
Assert.Equal(y, z);
Assert.Equal(expect[x], result[x]);
}
public LeftJoin LeftJoin(string table, Condition condition = null, string alias = null)
{
var clause = new LeftJoin(table, alias);
clause.Condition(condition);
if (!_clauses.TryGetValue(typeof(LeftJoin), out var list))
{
list = new List <Clause>();
_clauses.Add(typeof(LeftJoin), list);
}
list.Add(clause);
return(clause);
}
public void LeftJoin_can_left_join()
{
// Arrange
LeftJoin leftJoin = new LeftJoin();
Dictionary <string, string> HT1 = new Dictionary <string, string>();
Dictionary <string, string> HT2 = new Dictionary <string, string>();
HT1.Add("fond", "enamored");
HT1.Add("wrath", "anger");
HT1.Add("diligent", "employed");
HT1.Add("outfit", "garb");
HT1.Add("guide", "usher");
HT2.Add("fond", "averse");
HT2.Add("wrath", "delight");
HT2.Add("diligent", "idle");
HT2.Add("guide", "follow");
HT2.Add("flow", "jam"); //<- Not expected to be in result
// Act
List <string[]> result = leftJoin.LeftJoiner(HT1, HT2);
// Assert
// Expected: [["fond", "enamored", "averse"], ["wrath", "anger", "delight"], ["diligent", "employed", "idle"], ["outfit", "garb", null], ["guide", "usher", "follow"] , ["flow", null, "jam"]]
List <string[]> expected = new List <string[]>();
string[] expectedArray1 = new string[3] {
"fond", "enamored", "averse"
};
string[] expectedArray2 = new string[3] {
"wrath", "anger", "delight"
};
string[] expectedArray3 = new string[3] {
"diligent", "employed", "idle"
};
string[] expectedArray4 = new string[3] {
"outfit", "garb", null
};
string[] expectedArray5 = new string[3] {
"guide", "usher", "follow"
};
expected.Add(expectedArray1);
expected.Add(expectedArray2);
expected.Add(expectedArray3);
expected.Add(expectedArray4);
expected.Add(expectedArray5);
Assert.Equal(expected, result);
}
public void Successfully_Left_Join_The_Shared_Keys_Of_Two_HashMaps()
{
HashMap <string> leftTable = LeftTable();
HashMap <string> rightTable = RightTable();
List <ReturnObject> list = LeftJoin.Join(leftTable, rightTable);
List <string> actual = ObjectListToString(list);
List <string> expected = new List <string>()
{
"fond", "enamored", "averse", "wrath", "anger", "delight", "diligent",
"employed", "idle", "outfit", "garb", null, "guide", "usher", "follow"
};
Assert.Equal(expected, actual);
}
private void runJoin(JoinSetting setting)
{
List <Row> left = helper.DataTable2List(dictDataTable[setting.LeftDTName]);
List <Row> right = helper.DataTable2List(dictDataTable[setting.RightDTName]);
List <JoinCondition> joinConditions = new List <JoinCondition>();
foreach (string[] item in setting.JoinConditions)
{
joinConditions.Add(new JoinCondition(item[0], item[1]));
}
List <Mapping> mappings = new List <Mapping>();
foreach (string[] item in setting.Mappings)
{
mappings.Add(new Mapping(item[0], item[1], item[2], item[3]));
}
BaseJoin join;
switch (setting.Type)
{
case "leftjoin":
join = new LeftJoin();
break;
case "innerjoin":
join = new InnerJoin();
break;
default:
throw new NotImplementedException();
}
join.Left(new GenericEnumerableOperation(left))
.Right(new GenericEnumerableOperation(right));
join.JoinCondition(joinConditions);
join.Mapping(mappings);
join.PrepareForExecution(new SingleThreadedPipelineExecuter());
IEnumerable <Row> result = join.Execute(null);
List <Row> items = new List <Row>(result);
DataTable dtResult = helper.List2DataTable(items);
dictDataTable.Add(setting.OutputName, dtResult);
}
public void WorksAsExpected()
{
MyHashTable <object> tableOne = new MyHashTable <object>(20);
MyHashTable <object> tableTwo = new MyHashTable <object>(20);
tableOne.Add("fond", "enamored");
tableOne.Add("wrath", "anger");
tableOne.Add("diligent", "employed");
tableOne.Add("outift", "garb");
tableOne.Add("guide", "usher");
tableTwo.Add("wrath", "delight");
tableTwo.Add("diligent", "idle");
tableTwo.Add("guide", "follow");
tableTwo.Add("flow", "jam");
tableTwo.Add("fond", "averse");
LeftJoin LJ = new LeftJoin();
LJ.MyLeftJoint(tableOne, tableTwo);
Assert.Equal("poop", tableOne.Get("fond"));
}
static void Main(string[] args)
{
Console.WriteLine("Hello World!");
MyHashTable <object> tableOne = new MyHashTable <object>(20);
MyHashTable <object> tableTwo = new MyHashTable <object>(20);
tableOne.Add("fond", "enamored");
tableOne.Add("wrath", "anger");
tableOne.Add("diligent", "employed");
tableOne.Add("outift", "garb");
tableOne.Add("guide", "usher");
tableTwo.Add("wrath", "delight");
tableTwo.Add("diligent", "idle");
tableTwo.Add("guide", "follow");
tableTwo.Add("flow", "jam");
tableTwo.Add("fond", "averse");
LeftJoin LJ = new LeftJoin();
LJ.MyLeftJoint(tableOne, tableTwo);
}
public void CanLeftJoinTest()
{
var synonyms = new Dictionary <string, string>()
{
{ "can", "able" },
{ "will", "compliant" },
{ "wet", "moist" }
};
var antonyms = new Dictionary <string, string>()
{
{ "can", "cant" },
{ "wet", "dry" },
{ "rise", "fall" }
};
string result = LeftJoin.PerformLeftJoin(synonyms, antonyms);
Assert.Equal("[can, able, cant], \n[will, compliant, NULL], \n[wet, moist, dry]", result);
}
public void Test()
{
var table1 = new Dictionary <string, string>();
table1.Add("fond", "enamored");
table1.Add("wrath", "anger");
table1.Add("diligent", "employed");
table1.Add("outfit", "garb");
table1.Add("guide", "usher");
var table2 = new Dictionary <string, string>();
table2.Add("fond", "averse");
table2.Add("wrath", "delight");
table2.Add("diligent", "idle");
table2.Add("guide", "follow");
table2.Add("flow", "jam");
var result3 = LeftJoin.LeftJoinList(table1, table2);
List <string[]> expect = new List <string[]>();
expect.Add(new string[3] {
"fond", "enamored", "averse"
});
expect.Add(new string[3] {
"wrath", "anger", "delight"
});
expect.Add(new string[3] {
"diligent", "employed", "idle"
});
expect.Add(new string[3] {
"outfit", "garb", null
});
expect.Add(new string[3] {
"guide", "usher", "follow"
});
Assert.Equal(expect, result3);
}
public void LeftJoin_can_left_join_single_key_value_pairs()
{
// Arrange
LeftJoin leftJoin = new LeftJoin();
Dictionary <string, string> HT1 = new Dictionary <string, string>();
Dictionary <string, string> HT2 = new Dictionary <string, string>();
HT1.Add("key", "value1");
HT2.Add("key", "value2");
// Act
List <string[]> result = leftJoin.LeftJoiner(HT1, HT2);
// Assert
List <string[]> expected = new List <string[]>();
string[] expectedArray = new string[3] {
"key", "value1", "value2"
};
expected.Add(expectedArray);
Assert.Equal(expected, result);
}
/// <summary>
/// Adds a LEFT JOIN to the query
/// </summary>
/// <param name="alias">The alias.</param>
/// <param name="onCriteria">The on criteria.</param>
/// <returns></returns>
/// <exception cref="ArgumentNullException">
/// alias is null or alias.table is null or empty.
/// </exception>
public SqlQuery LeftJoin(IAlias alias, ICriteria onCriteria)
{
if (alias == null)
{
throw new ArgumentNullException("alias");
}
if (string.IsNullOrEmpty(alias.Table))
{
throw new ArgumentNullException("alias.table");
}
var join = new LeftJoin(alias.Table, alias.Name, onCriteria);
Join(join);
if (alias as IHaveJoins != null)
{
AliasWithJoins[alias.Name] = alias as IHaveJoins;
}
return(this);
}
public IEnumerable <object> ToList <TReturn>(Expression <Func <TEntity, TReturn> > expression)
{
var code = expression.GetHashCode();
if (SelectHandler == null)
{
SelectHandler = SqlHandler.Select <TEntity>();
}
var type = typeof(TReturn);
if (!JoinExpressionMapping.Contains(code))
{
var nclass = NClass.DefaultDomain().Public();
StringBuilder script = new StringBuilder();
// 获取构造函数参数
var arguments = ((NewExpression)expression.Body).Arguments;
//获取匿名类成员
var members = ((NewExpression)expression.Body).Members;
var joinTypeFlagMapping = new Dictionary <Type, JoinTypeFlag>();
for (int i = 0; i < arguments.Count; i++)
{
// 方法类型参数
if (arguments[i].NodeType == ExpressionType.Call)
{
var methodExpression = (MethodCallExpression)arguments[i];
var methodDeclaringType = methodExpression.Method.DeclaringType;
if (methodDeclaringType.IsGenericType && methodDeclaringType.GetGenericTypeDefinition().Name.Contains("Join"))
{
var joinType = methodDeclaringType.GetGenericArguments()[0];
if (methodExpression.Arguments[0].NodeType == ExpressionType.Quote)
{
var quoteExpression = (UnaryExpression)methodExpression.Arguments[0];
if (quoteExpression.Operand.NodeType == ExpressionType.Lambda)
{
var lambdaExpression = (LambdaExpression)quoteExpression.Operand;
if (lambdaExpression.Body.NodeType == ExpressionType.MemberAccess)
{
var memberExpression = (MemberExpression)lambdaExpression.Body;
nclass.Property(item => item
.Public()
.Type(((PropertyInfo)memberExpression.Member).PropertyType)
.Name(memberExpression.Member.Name));
var definitionType = methodDeclaringType.GetGenericTypeDefinition();
if (definitionType == typeof(InnerJoin <>))
{
joinTypeFlagMapping[joinType] = JoinTypeFlag.Inner;
script.Append(InnerJoin.GetJoinScript(joinType));
}
else if (definitionType == typeof(LeftJoin <>))
{
joinTypeFlagMapping[joinType] = JoinTypeFlag.Left;
script.Append(LeftJoin.GetJoinScript(joinType));
}
else if (definitionType == typeof(RightJoin <>))
{
joinTypeFlagMapping[joinType] = JoinTypeFlag.Right;
script.Append(RightJoin.GetJoinScript(joinType));
}
script.Append($".\"{memberExpression.Member.Name}\" AS \"{members[i].Name}\",");
}
}
}
}
}
else if (arguments[i].NodeType == ExpressionType.MemberAccess)
{
var memberExpression = (MemberExpression)arguments[i];
nclass.Property(item => item
.Public()
.Type(((PropertyInfo)memberExpression.Member).PropertyType)
.Name(memberExpression.Member.Name));
script.Append($"a.\"{memberExpression.Member.Name}\",");
}
//JoinObjectCache<ISelect<T>, TReturn>.GetObjects = NDelegate.RandomDomain().Func<ISelect<T>, object>(builder.ToString());
}
if (script.Length > 1)
{
script.Length -= 1;
var joinScript = script.ToString();
JoinExpressionMapping = JoinExpressionMapping.Add(code);
var tempClass = nclass.GetType();
ProxyCaller <TEntity, TReturn> .Add(code, NDelegate
.DefaultDomain(item => item.LogSyntaxError())
.Func <ISelect <TEntity>, IEnumerable <object> >($"return arg.ToList<{tempClass.GetDevelopName()}>(\"{joinScript.Replace("\"","\\\"")}\");"));
var builder = new StringBuilder();
foreach (var item in joinTypeFlagMapping)
{
var joinFieldCache = OrmNavigate <TEntity> .JoinScriptMapping[item.Key];
string joinAlias = string.Empty;
switch (item.Value)
{
case JoinTypeFlag.Left:
joinAlias = LeftJoin.GetJoinScript(item.Key);
builder.Append($"obj.LeftJoin(\"");
break;
case JoinTypeFlag.Inner:
joinAlias = InnerJoin.GetJoinScript(item.Key);
builder.Append($"obj.InnerJoin(\"");
break;
case JoinTypeFlag.Right:
joinAlias = RightJoin.GetJoinScript(item.Key);
builder.Append($"obj.RightJoin(\"");
break;
default:
break;
}
var joinFieldScript = $"\"{item.Key.Name}\" AS {joinAlias} ON a.\"{joinFieldCache.src}\" = {joinAlias}.\"{joinFieldCache.dst}\"";
builder.Append(joinFieldScript.Replace("\"", "\\\""));
builder.AppendLine("\");");
}
//$"\"{typeof(TJoinEntity).Name}\" AS {InnerJoinHelper<TJoinEntity>.JoinAliasName} ON a.\"{srcFieldName}\" = {InnerJoinHelper<TJoinEntity>.JoinAliasName}.\"{destFieldName}\"")
JoinFiller <TEntity, TReturn> .Add(code, NDelegate
.DefaultDomain()
.Action <ISelect <TEntity> >(builder.ToString()));
}
}
JoinFiller <TEntity, TReturn> .HandlerSelect(code, SelectHandler);
return(ProxyCaller <TEntity, TReturn> .ToList(code, SelectHandler));
}
/// <summary>
/// Gets the Algebra representation of the Graph Pattern
/// </summary>
/// <returns></returns>
public ISparqlAlgebra ToAlgebra()
{
if (_isUnion)
{
// If this Graph Pattern represents a UNION of Graph Patterns turn into a series of UNIONs
ISparqlAlgebra union = new Union(_graphPatterns[0].ToAlgebra(), _graphPatterns[1].ToAlgebra());
if (_graphPatterns.Count > 2)
{
for (int i = 2; i < _graphPatterns.Count; i++)
{
union = new Union(union, _graphPatterns[i].ToAlgebra());
}
}
// Apply Inline Data
if (HasInlineData)
{
union = Join.CreateJoin(union, new Bindings(_data));
}
// If there's a FILTER apply it over the Union
if (_isFiltered && (_filter != null || _unplacedFilters.Count > 0))
{
return(new Filter(union, Filter));
}
return(union);
}
// Terminal graph pattern
if (_graphPatterns.Count == 0)
{
// If there are no Child Graph Patterns then this is a BGP
ISparqlAlgebra bgp = new Bgp(_triplePatterns);
if (_unplacedAssignments.Count > 0)
{
// If we have any unplaced LETs these get Extended onto the BGP
foreach (IAssignmentPattern p in _unplacedAssignments)
{
bgp = new Extend(bgp, p.AssignExpression, p.VariableName);
}
}
if (IsGraph)
{
bgp = Algebra.Graph.ApplyGraph(bgp, GraphSpecifier);
}
else if (IsService)
{
bgp = new Service(GraphSpecifier, this, IsSilent);
}
// Apply Inline Data
if (HasInlineData)
{
bgp = Join.CreateJoin(bgp, new Bindings(_data));
}
if (_isFiltered && (_filter != null || _unplacedFilters.Count > 0))
{
if (_isOptional && !(_isExists || _isNotExists))
{
// If we contain an unplaced FILTER and we're an OPTIONAL then the FILTER
// applies over the LEFT JOIN and will have been added elsewhere in the Algebra transform
return(bgp);
}
// If we contain an unplaced FILTER and we're not an OPTIONAL the FILTER
// applies here
return(new Filter(bgp, Filter));
}
// We're not filtered (or all FILTERs were placed in the BGP) so we're just a BGP
return(bgp);
}
// Create a basic BGP to start with
ISparqlAlgebra complex = new Bgp();
if (_triplePatterns.Count > 0)
{
complex = new Bgp(_triplePatterns);
}
// Apply Inline Data
// If this Graph Pattern had child patterns before this Graph Pattern then we would
// have broken the BGP and not added the Inline Data here so it's always safe to apply this here
if (HasInlineData)
{
complex = Join.CreateJoin(complex, new Bindings(_data));
}
// Then Join each of the Graph Patterns as appropriate
foreach (GraphPattern gp in _graphPatterns)
{
if (gp.IsGraph)
{
// A GRAPH clause means a Join of the current pattern to a Graph clause
ISparqlAlgebra gpAlgebra = gp.ToAlgebra();
complex = Join.CreateJoin(complex, Algebra.Graph.ApplyGraph(gpAlgebra, gp.GraphSpecifier));
}
else if (gp.IsOptional)
{
if (gp.IsExists || gp.IsNotExists)
{
// An EXISTS/NOT EXISTS means an Exists Join of the current pattern to the EXISTS/NOT EXISTS clause
complex = new ExistsJoin(complex, gp.ToAlgebra(), gp.IsExists);
}
else
{
// An OPTIONAL means a Left Join of the current pattern to the OPTIONAL clause
// with a possible FILTER applied over the LeftJoin
if (gp.IsFiltered && gp.Filter != null)
{
// If the OPTIONAL clause has an unplaced FILTER it applies over the Left Join
complex = new LeftJoin(complex, gp.ToAlgebra(), gp.Filter);
}
else
{
complex = new LeftJoin(complex, gp.ToAlgebra());
}
}
}
else if (gp.IsMinus)
{
// Always introduce a Minus here even if the Minus is disjoint since during evaluation we'll choose
// not to execute it if it's disjoint
complex = new Minus(complex, gp.ToAlgebra());
}
else if (gp.IsService)
{
complex = Join.CreateJoin(complex, new Service(gp.GraphSpecifier, gp, gp.IsSilent));
}
else
{
// Otherwise we just join the pattern to the existing pattern
complex = Join.CreateJoin(complex, gp.ToAlgebra());
}
}
if (_unplacedAssignments.Count > 0)
{
// Unplaced assignments get Extended over the algebra so far here
// complex = Join.CreateJoin(complex, new Bgp(this._unplacedAssignments.OfType<ITriplePattern>()));
foreach (IAssignmentPattern p in _unplacedAssignments)
{
complex = new Extend(complex, p.AssignExpression, p.VariableName);
}
}
if (IsGraph)
{
complex = Algebra.Graph.ApplyGraph(complex, GraphSpecifier);
}
if (_isFiltered && (_filter != null || _unplacedFilters.Count > 0))
{
if (_isOptional && !(_isExists || _isNotExists))
{
// If there's an unplaced FILTER and we're an OPTIONAL then the FILTER will
// apply over the LeftJoin and is applied elsewhere in the Algebra transform
return(complex);
}
else
{
if (_filter != null || _unplacedFilters.Count > 0)
{
// If there's an unplaced FILTER and we're not an OPTIONAL pattern we apply
// the FILTER here
return(new Filter(complex, Filter));
}
else
{
return(complex);
}
}
}
else
{
// If no FILTER just return the transform
return(complex);
}
}
protected override void SetupJoinConditions()
{
LeftJoin.Left(_keys).Right(_keys);
}
/**
* @ 初始化SQL命令，准备执行
* */
protected override bool InitSQLWithCmdText()
{
if (TableName.IsNullOrEmpty())
{
throw new ArgumentNullException("必须设置属性TableName的值，该值为查询的主表名称");
}
if (LeftJoin.IsNotNullOrEmpty() && TableAlias.IsNullOrEmpty())
{
throw new ArgumentNullException("当存在多表连接查询时，必须指定主表的别名，即属性TableAlias的值");
}
if (Fields.IsNullOrEmpty())
{
throw new ArgumentNullException("必须设置要查询的字段fields");
}
if (PrimaryKey.IsNullOrEmpty())
{
throw new ArgumentNullException("必须设置属性PrimaryKey的值为查询主表的主键名称");
}
if (pageSize < 1)
{
throw new ArgumentNullException("必须设置属性pageSize的值，且该值必须大于0");
}
if (OrderBy.IsNullOrEmpty())
{
throw new ArgumentNullException("必须调用SetOrderBy方法进行设置排序字段");
}
string alias = string.Empty;
string pk = string.Empty;
if (TableAlias.IsNotNullOrEmpty())
{
alias = string.Format("AS {0}", TableAlias);
pk = string.Format("{0}.{1}", TableAlias, PrimaryKey);
}
else
{
pk = PrimaryKey;
}
string whereString = GetCondition();
string tempTableName = string.Format("{0}{1}", "A", Guid.NewGuid().ToString("N"));
// 如果没有条件，对全表进行统计行数
string sysSql = string.Format(@"dbcc updateusage(0,{0}) with no_infomsgs
SELECT @DataCount =SUM (CASE WHEN (index_id < 2) THEN row_count ELSE 0 END) FROM sys.dm_db_partition_stats
WHERE object_id = object_id('{0}')", TableName);
// 按条件查询
string mSql = string.Format(@"SELECT @DataCount=COUNT(1) FROM {0} {1} {2} {3}", TableName, alias, LeftJoin, whereString);
SQLCmdText = string.Format(@"DECLARE @DataCount int
{13}
SELECT {0},@DataCount as DataCount FROM {1} {2}
{3}
WHERE {12} IN
(
SELECT {4} FROM
(
SELECT TOP {9} {12},ROW_NUMBER() OVER({5}) AS R_NO FROM {1} {2} {3} {6} {5}
){11} WHERE R_NO BETWEEN {8} AND {10}
) {7} {5}", Fields.ToJoin(), TableName, alias, LeftJoin, PrimaryKey, OrderBy, whereString, GroupBy, pageIndex, pageSize, pageIndex * pageSize, tempTableName, pk, whereString.IsNullOrEmpty() ? sysSql : mSql);
Succeed = true;
return(Succeed);
}
/// <summary>
/// Gets the Algebra representation of the Graph Pattern
/// </summary>
/// <returns></returns>
public ISparqlAlgebra ToAlgebra()
{
if (this._isUnion)
{
//If this Graph Pattern represents a UNION of Graph Patterns turn into a series of UNIONs
ISparqlAlgebra union = new Union(this._graphPatterns[0].ToAlgebra(), this._graphPatterns[1].ToAlgebra());
if (this._graphPatterns.Count > 2)
{
for (int i = 2; i < this._graphPatterns.Count; i++)
{
union = new Union(union, this._graphPatterns[i].ToAlgebra());
}
}
//If there's a FILTER apply it over the Union
if (this._isFiltered && (this._filter != null || this._unplacedFilters.Count > 0))
{
return(new Filter(union, this.Filter));
}
else
{
return(union);
}
}
else if (this._graphPatterns.Count == 0)
{
//If there are no Child Graph Patterns then this is a BGP
ISparqlAlgebra bgp = new Bgp(this._triplePatterns);
if (this._unplacedAssignments.Count > 0)
{
//If we have any unplaced LETs these get Joined onto the BGP
bgp = Join.CreateJoin(bgp, new Bgp(this._unplacedAssignments));
}
if (this._isFiltered && (this._filter != null || this._unplacedFilters.Count > 0))
{
if (this._isOptional && !(this._isExists || this._isNotExists))
{
//If we contain an unplaced FILTER and we're an OPTIONAL then the FILTER
//applies over the LEFT JOIN and will have been added elsewhere in the Algebra transform
return(bgp);
}
else
{
ISparqlAlgebra complex = bgp;
//If we contain an unplaced FILTER and we're not an OPTIONAL the FILTER
//applies here
return(new Filter(bgp, this.Filter));
}
}
else
{
//We're not filtered (or all FILTERs were placed in the BGP) so we're just a BGP
return(bgp);
}
}
else
{
//Create a basic BGP to start with
ISparqlAlgebra complex = new Bgp();
if (this._triplePatterns.Count > 0)
{
complex = new Bgp(this._triplePatterns);
}
//Then Join each of the Graph Patterns as appropriate
foreach (GraphPattern gp in this._graphPatterns)
{
if (gp.IsGraph)
{
//A GRAPH clause means a Join of the current pattern to a Graph clause
complex = Join.CreateJoin(complex, new Algebra.Graph(gp.ToAlgebra(), gp.GraphSpecifier));
}
else if (gp.IsOptional)
{
if (gp.IsExists || gp.IsNotExists)
{
//An EXISTS/NOT EXISTS means an Exists Join of the current pattern to the EXISTS/NOT EXISTS clause
complex = new ExistsJoin(complex, gp.ToAlgebra(), gp.IsExists);
}
else
{
//An OPTIONAL means a Left Join of the current pattern to the OPTIONAL clause
//with a possible FILTER applied over the LeftJoin
if (gp.IsFiltered && gp.Filter != null)
{
//If the OPTIONAL clause has an unplaced FILTER it applies over the Left Join
complex = new LeftJoin(complex, gp.ToAlgebra(), gp.Filter);
}
else
{
complex = new LeftJoin(complex, gp.ToAlgebra());
}
}
}
else if (gp.IsMinus)
{
//Always introduce a Minus here even if the Minus is disjoint since during evaluation we'll choose
//not to execute it if it's disjoint
complex = new Minus(complex, gp.ToAlgebra());
}
else if (gp.IsService)
{
complex = Join.CreateJoin(complex, new Service(gp.GraphSpecifier, gp, gp.IsSilent));
}
else
{
//Otherwise we just join the pattern to the existing pattern
complex = Join.CreateJoin(complex, gp.ToAlgebra());
}
}
if (this._unplacedAssignments.Count > 0)
{
//Unplaced assignments get Joined as a BGP here
complex = Join.CreateJoin(complex, new Bgp(this._unplacedAssignments));
}
if (this._isFiltered && (this._filter != null || this._unplacedFilters.Count > 0))
{
if (this._isOptional && !(this._isExists || this._isNotExists))
{
//If there's an unplaced FILTER and we're an OPTIONAL then the FILTER will
//apply over the LeftJoin and is applied elsewhere in the Algebra transform
return(complex);
}
else
{
if (this._filter != null || this._unplacedFilters.Count > 0)
{
//If there's an unplaced FILTER and we're not an OPTIONAL pattern we apply
//the FILTER here
return(new Filter(complex, this.Filter));
}
else
{
return(complex);
}
}
}
else
{
//If no FILTER just return the transform
return(complex);
}
}
}
protected override void SetupJoinConditions()
{
LeftJoin
.Left(_rel.Join.Select(j => j.LeftField).Select(f => f.Alias).ToArray())
.Right(_rel.Join.Select(j => j.RightField).Select(f => f.Alias).ToArray());
}
public void SparqlBgpEvaluation()
{
//Prepare the Store
TripleStore store = new TripleStore();
Graph g = new Graph();
FileLoader.Load(g, "resources\\Turtle.ttl");
store.Add(g);
SparqlQueryParser parser = new SparqlQueryParser();
SparqlQuery q = parser.ParseFromString(@"PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
SELECT * WHERE {?s ?p ?o . ?s rdfs:label ?label}");
Object testResult = store.ExecuteQuery(q);
ISparqlAlgebra testAlgebra = q.ToAlgebra();
if (testResult is SparqlResultSet)
{
SparqlResultSet rset = (SparqlResultSet)testResult;
Console.WriteLine(rset.Count + " Results");
foreach (SparqlResult r in rset)
{
Console.WriteLine(r.ToString());
}
Console.WriteLine();
}
//Create some Triple Patterns
TriplePattern t1 = new TriplePattern(new VariablePattern("?s"), new VariablePattern("?p"), new VariablePattern("?o"));
TriplePattern t2 = new TriplePattern(new VariablePattern("?s"), new NodeMatchPattern(g.CreateUriNode("rdfs:label")), new VariablePattern("?label"));
TriplePattern t3 = new TriplePattern(new VariablePattern("?x"), new VariablePattern("?y"), new VariablePattern("?z"));
TriplePattern t4 = new TriplePattern(new VariablePattern("?s"), new NodeMatchPattern(g.CreateUriNode(":name")), new VariablePattern("?name"));
//Build some BGPs
Bgp selectNothing = new Bgp();
Bgp selectAll = new Bgp(t1);
Bgp selectLabelled = new Bgp(new List <ITriplePattern>()
{
t1, t2
});
Bgp selectAllDisjoint = new Bgp(new List <ITriplePattern>()
{
t1, t3
});
Bgp selectLabels = new Bgp(t2);
Bgp selectNames = new Bgp(t4);
//LeftJoin selectOptionalNamed = new LeftJoin(selectAll, new Optional(selectNames));
LeftJoin selectOptionalNamed = new LeftJoin(selectAll, selectNames);
Union selectAllUnion = new Union(selectAll, selectAll);
Union selectAllUnion2 = new Union(selectAllUnion, selectAll);
Filter selectAllUriObjects = new Filter(selectAll, new UnaryExpressionFilter(new IsUriFunction(new VariableTerm("o"))));
//Test out the BGPs
//Console.WriteLine("{}");
//this.ShowMultiset(selectNothing.Evaluate(new SparqlEvaluationContext(null, store)));
//Console.WriteLine("{?s ?p ?o}");
//this.ShowMultiset(selectAll.Evaluate(new SparqlEvaluationContext(null, store)));
//Console.WriteLine("{?s ?p ?o . ?s rdfs:label ?label}");
//SparqlEvaluationContext context = new SparqlEvaluationContext(null, store);
//this.ShowMultiset(selectLabelled.Evaluate(context));
//SparqlResultSet lvnResult = new SparqlResultSet(context);
//Console.WriteLine("{?s ?p ?o . ?x ?y ?z}");
//this.ShowMultiset(selectAllDisjoint.Evaluate(new SparqlEvaluationContext(null, store)));
//Console.WriteLine("{?s ?p ?o . OPTIONAL {?s :name ?name}}");
//this.ShowMultiset(selectOptionalNamed.Evaluate(new SparqlEvaluationContext(null, store)));
Console.WriteLine("{{?s ?p ?o} UNION {?s ?p ?o}}");
this.ShowMultiset(selectAllUnion.Evaluate(new SparqlEvaluationContext(null, new InMemoryDataset(store))));
Console.WriteLine("{{?s ?p ?o} UNION {?s ?p ?o} UNION {?s ?p ?o}}");
this.ShowMultiset(selectAllUnion2.Evaluate(new SparqlEvaluationContext(null, new InMemoryDataset(store))));
Console.WriteLine("{?s ?p ?o FILTER (ISURI(?o))}");
this.ShowMultiset(selectAllUriObjects.Evaluate(new SparqlEvaluationContext(null, new InMemoryDataset(store))));
}
/// <summary>
/// This function permits to get a ValueObject list by criteria in using SQL Raw.
/// </summary>
/// <remarks>
/// After tests, we have opted for SQL calls directly in the database.
/// </remarks>
/// <param name="datasetId"></param>
/// <param name="select"></param>
/// <param name="where"></param>
/// <param name="sort"></param>
/// <param name="grouping"></param>
/// <param name="page"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public async Task <IEnumerable <ValueObject> > Filter(
string[] select,
string[] where = null,
string[] sort_asc = null,
string[] sort_desc = null,
bool grouping = false,
int?page = null,
int?pageSize = null)
{
// Initialize the entity
DbSet <ValueObject> valueObjectEntity = UnitOfWork.GetDbContext()
.ValueObject;
IEnumerable <ValueObject> valueObjects = valueObjectEntity
.Include(i => i.DataSet)
.AsQueryable();
SqlRawBuilder builder = new SqlRawBuilder();
Field fieldGroup = builder.Select.AddFieldGroup(select);
LeftJoin leftJoin = new LeftJoin();
Table table = new Table();
// Grouping or not
if (grouping)
{
leftJoin = fieldGroup
.AddField("InitialValue", FieldFormatEnum.Sum)
.AddField("CurrentValue", FieldFormatEnum.Sum)
.AddField("FutureValue", FieldFormatEnum.Sum)
.AddLeftJoin("ValueObject", "DataSetId", "DataSet", "Id");
Having clause = leftJoin.AddGroupBy()
.AddFieldGroup(select)
.AddHaving();
BuildClause(clause, where);
}
else
{
leftJoin = fieldGroup
.AddField("InitialValue", FieldFormatEnum.normal)
.AddField("CurrentValue", FieldFormatEnum.normal)
.AddField("FutureValue", FieldFormatEnum.normal)
.AddLeftJoin("ValueObject", "DataSetId", "DataSet", "Id");
Where clause = leftJoin
.AddWhere();
BuildClause(clause, where);
}
// Sorting
if (sort_desc != null && sort_desc.Length > 0)
{
leftJoin.AddOrder(sort_desc, SortingEnum.Desc);
}
else if (sort_asc != null && sort_asc.Length > 0)
{
leftJoin.AddOrder(sort_asc, SortingEnum.Asc);
}
// Execute the script
valueObjects = await UnitOfWork.ValueObjectRepository.ExecuteReader(builder.GetSQL);
// Paging the results
if (page != null && page.Value > 0 && pageSize != null && pageSize.Value > 0)
{
valueObjects = valueObjects.Skip(pageSize.Value * page.Value).Take(pageSize.Value);
}
return(await Task.FromResult(valueObjects));
}
protected override void SetupJoinConditions()
{
LeftJoin.Left("database", "objectid").Right("database", "objectid");
}
public List <DTOs.Empresa_Quartos> PesquisarQuartosSemReserva(string datas, int cdcidade, int cdbairro, string nomehotel, string hospedes, bool arcondicionado)
{
List <DTOs.Empresa_Quartos> ResultadoFinal = new List <DTOs.Empresa_Quartos>();
try
{
var datasSplit = datas.Split(",");
DateTime inicial = DateTime.Parse(datasSplit[0].Substring(4, 11));
DateTime final = DateTime.Parse(datasSplit[1].Substring(4, 11));
int qtdhospede = Int32.Parse(hospedes);
using (HSContext hs = new HSContext())
{
var ceps = hs.Logradouros
.Include(x => x.CdBairroNavigation)
.ThenInclude(x => x.CdCidadeNavigation)
.ThenInclude(x => x.CdUfNavigation)
.Where(x => x.CdBairroNavigation.CdCidade == cdcidade && cdbairro != 0 ? x.CdBairro == cdbairro : x.CdBairroNavigation.CdCidade == cdcidade)
.Select(x => x.NoLogradouroCep).ToList();
if (nomehotel == null)
{
nomehotel = "";
}
var retorno =
(
from quarto in hs.Tbquarto.Include(x => x.IdtipoquartoNavigation).ThenInclude(x => x.IdempresaNavigation)
join reserva in hs.Tbreserva on quarto.Idquarto equals reserva.Idquarto into LeftJoin
from left in LeftJoin.DefaultIfEmpty()
where
((left.Datafinalizacao.HasValue || left == null)
||
(!left.Datafinalizacao.HasValue && left.Datasaida < inicial))
select new DTOs.Quarto()
{
hotel = quarto.IdtipoquartoNavigation.IdempresaNavigation.Nomeempresa,
nomeempresa =
quarto.IdtipoquartoNavigation.IdempresaNavigation.Nomeempresa + " - " + hs.Logradouros
.Include(x => x.CdBairroNavigation)
.ThenInclude(x => x.CdCidadeNavigation)
.First(x => x.NoLogradouroCep == quarto.IdtipoquartoNavigation.IdempresaNavigation.Cep).CdBairroNavigation.DsBairroNome.ToUpper(),
idquarto = quarto.Idquarto,
valor = quarto.IdtipoquartoNavigation.Valor.ToString(),
andar = quarto.Andar,
idtipoquarto = quarto.Idtipoquarto,
quarto = quarto.Quarto,
varanda = quarto.IdtipoquartoNavigation.Varanda,
arcondicionado = quarto.IdtipoquartoNavigation.Arcondicionado,
camacasal = quarto.IdtipoquartoNavigation.Camacasal,
camasolteiro = quarto.IdtipoquartoNavigation.Camasolteiro,
cep = quarto.IdtipoquartoNavigation.IdempresaNavigation.Cep,
status = quarto.Status.Value,
ventilador = quarto.IdtipoquartoNavigation.Ventilador,
idempresa = quarto.IdtipoquartoNavigation.Idempresa,
banheiroprivativo = quarto.IdtipoquartoNavigation.Banheiroprivativo,
tipoquarto = quarto.IdtipoquartoNavigation.Tipoquarto,
endereco = hs.Logradouros
.Include(x => x.CdBairroNavigation)
.ThenInclude(x => x.CdCidadeNavigation)
.First(x => x.NoLogradouroCep == quarto.IdtipoquartoNavigation.IdempresaNavigation.Cep).DsLogradouroNome + " n° " + quarto.IdtipoquartoNavigation.IdempresaNavigation.Numero + " - Complemento " + quarto.IdtipoquartoNavigation.IdempresaNavigation.Complemente,
imagens = hs.Tbquartofoto.Where(x => x.Idquarto == quarto.Idquarto).Select(x => x.Imagem).ToList(),
foto = hs.Tbquartofoto.FirstOrDefault(x => x.Idquarto == quarto.Idquarto) == null ? "" : hs.Tbquartofoto.First(x => x.Idquarto == quarto.Idquarto).Imagem,
checkin = quarto.IdtipoquartoNavigation.IdempresaNavigation.Horachekin,
checkout = quarto.IdtipoquartoNavigation.IdempresaNavigation.Horacheckout,
qtdhospedes = quarto.IdtipoquartoNavigation.Totalpessoas,
banheira = quarto.IdtipoquartoNavigation.Banheira,
vistamar = quarto.IdtipoquartoNavigation.Vistamar,
vistapordosol = quarto.IdtipoquartoNavigation.Vistapordosol
}
)
.Distinct().Where(x => ceps.Contains(x.cep) && x.qtdhospedes == qtdhospede).ToList();
if (arcondicionado)
{
retorno = retorno.Where(x => x.arcondicionado == true).ToList();
}
if (nomehotel == "")
{
ResultadoFinal = (
from agru in retorno
group agru by new { agru.idempresa, agru.nomeempresa } into agrup
select new DTOs.Empresa_Quartos()
{
idempresa = agrup.Key.idempresa,
checkin = hs.Tbempresa.First(x => x.Idempresa == agrup.Key.idempresa).Horachekin,
checkout = hs.Tbempresa.First(x => x.Idempresa == agrup.Key.idempresa).Horacheckout,
nomeempresa = agrup.Key.nomeempresa,
maisbarato = retorno.Where(x => x.idempresa == agrup.Key.idempresa).Min(x => Decimal.Parse(x.valor.Replace(".", ","))),
tipoquartos = retorno
.Where(x => x.idempresa == agrup.Key.idempresa)
.Select(x => new DTOs.TipoQuarto()
{
tipoquarto = x.tipoquarto,
idtipoquarto = x.idtipoquarto,
valor = x.valor.ToString(),
varanda = x.varanda,
arcondicionado = x.arcondicionado,
camacasal = x.camacasal,
camasolteiro = x.camasolteiro,
ventilador = x.ventilador,
banheiroprivativo = x.banheira,
qtdhospedes = x.qtdhospedes,
banheira = x.banheira,
vistamar = x.vistamar,
vistapordosol = x.vistapordosol,
quartos = retorno.Where(ret => ret.idtipoquarto == x.idtipoquarto && ret.qtdhospedes == qtdhospede).OrderBy(ret => Decimal.Parse(ret.valor)).ToList()
}).Distinct().ToList()
}
).ToList();
}
else
{
ResultadoFinal = (
from agru in retorno
group agru by new { agru.idempresa, agru.nomeempresa } into agrup
select new DTOs.Empresa_Quartos()
{
idempresa = agrup.Key.idempresa,
nomeempresa = agrup.Key.nomeempresa,
checkin = hs.Tbempresa.First(x => x.Idempresa == agrup.Key.idempresa).Horachekin,
checkout = hs.Tbempresa.First(x => x.Idempresa == agrup.Key.idempresa).Horacheckout,
maisbarato = retorno.Where(x => x.idempresa == agrup.Key.idempresa).Min(x => Decimal.Parse(x.valor.Replace(".", ","))),
tipoquartos = retorno
.Where(x => x.idempresa == agrup.Key.idempresa)
.Select(x => new DTOs.TipoQuarto()
{
tipoquarto = x.tipoquarto,
idtipoquarto = x.idtipoquarto,
valor = x.valor.ToString(),
varanda = x.varanda,
arcondicionado = x.arcondicionado,
camacasal = x.camacasal,
camasolteiro = x.camasolteiro,
ventilador = x.ventilador,
banheiroprivativo = x.banheira,
qtdhospedes = x.qtdhospedes,
banheira = x.banheira,
vistamar = x.vistamar,
vistapordosol = x.vistapordosol,
quartos = retorno.Where(ret => ret.idtipoquarto == x.idtipoquarto && ret.qtdhospedes == qtdhospede).OrderBy(ret => Decimal.Parse(ret.valor)).ToList()
}).Distinct().ToList()
}
).ToList();
}
return(ResultadoFinal.OrderBy(x => x.maisbarato).ToList());
}
}
catch (Exception ex)
{
erros.Add("Os quartos para reserva não foram pesquisados. Ocorreu um erro.");
erros.Add(ex.Message);
return(null);
}
}
