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