static Customer() { SqlDataSource ds = new SqlDataSource("NWindConnectionString"); SelectQuery query = SelectQueryFluentBuilder .AddTable("Customers") .SelectAllColumns() .Build("Customers"); ds.Queries.Add(query); ds.RebuildResultSchema(); ds.Fill(); ITable src = ds.Result["Customers"]; foreach (var row in src) { customers.Add(new Customer() { CustomerID = row.GetValue <string>("CustomerID"), Address = row.GetValue <string>("Address"), CompanyName = row.GetValue <string>("CompanyName"), ContactName = row.GetValue <string>("ContactName"), ContactTitle = row.GetValue <string>("ContactTitle"), Country = row.GetValue <string>("Country"), City = row.GetValue <string>("City"), Fax = row.GetValue <string>("Fax"), Phone = row.GetValue <string>("Phone"), PostalCode = row.GetValue <string>("PostalCode"), Region = row.GetValue <string>("Region") }); } }
public CarteraVencimientosReport(IContextService user, string primarykey) { var server = ConfigurationManager.AppSettings["Server"]; var usuario = ConfigurationManager.AppSettings["User"]; var password = ConfigurationManager.AppSettings["Password"]; DataSource = new SqlDataSource("Report", new MsSqlConnectionParameters(server, user.BaseDatos, usuario, password, MsSqlAuthorizationType.SqlServer)); DataSource.Name = "Report"; var mainQuery = new CustomSqlQuery("CarteraVencimientos", "SELECT * FROM [CarteraVencimientos] "); if (!string.IsNullOrEmpty(primarykey)) { mainQuery.Parameters.Add(new QueryParameter("empresa", typeof(string), user.Empresa)); mainQuery.Parameters.Add(new QueryParameter("referencia", typeof(string), primarykey)); mainQuery.Sql = "SELECT * FROM [CarteraVencimientos] where empresa=@empresa and referencia=@referencia"; } DataSource.Queries.Add(mainQuery); DataSource.Queries.Add(new CustomSqlQuery("Cuentas", "SELECT * FROM [Cuentas]")); DataSource.Relations.Add("CarteraVencimientos", "Cuentas", new[] { new RelationColumnInfo("empresa", "empresa"), new RelationColumnInfo("fkcuentas", "id") }); DataSource.RebuildResultSchema(); }
public BundleReport(IContextService user, string primarykey) { var server = ConfigurationManager.AppSettings["Server"]; var usuario = ConfigurationManager.AppSettings["User"]; var password = ConfigurationManager.AppSettings["Password"]; DataSource = new SqlDataSource("Report", new MsSqlConnectionParameters(server, user.BaseDatos, usuario, password, MsSqlAuthorizationType.SqlServer)); DataSource.Name = "Report"; var mainQuery = new CustomSqlQuery("Bundle", "SELECT *,'' as [ZonaDescripcion],'' as [OperarioDescripcion],'' as [AlmacenDescripcion] FROM [Bundle] "); if (!string.IsNullOrEmpty(primarykey)) { mainQuery.Parameters.Add(new QueryParameter("empresa", typeof(string), user.Empresa)); mainQuery.Parameters.Add(new QueryParameter("referencia", typeof(string), primarykey)); mainQuery.Sql = "SELECT k.*,a.descripcion as [AlmacenDescripcion], az.descripcion as [ZonaDescripcion],c.descripcion as [OperarioDescripcion] FROM [Bundle] as k " + " inner join almacenes as a on a.empresa=k.empresa and a.id=k.fkalmacen " + " left join almaceneszona as az on az.empresa=a.empresa and az.fkalmacenes=a.id and az.id=k.fkzonaalmacen " + " left join cuentas as c on c.empresa=k.empresa and c.id=k.fkoperarios " + " where k.empresa=@empresa and concat(k.lote,k.id)=@referencia"; } DataSource.Queries.Add(mainQuery); DataSource.Queries.Add(new CustomSqlQuery("Bundlelin", "SELECT * FROM [BundleLin]")); DataSource.Relations.Add("Bundle", "Bundlelin", new[] { new RelationColumnInfo("empresa", "empresa"), new RelationColumnInfo("lote", "fkbundlelote"), new RelationColumnInfo("id", "fkbundle") }); DataSource.RebuildResultSchema(); }
public object GetDataSource(NameValueCollection args, bool designTime) { var query = new CustomSqlQuery(); query.Name = "Vouchers"; if (designTime) { query.Sql = "SELECT top(5) * FROM Voucher"; } else { query.Sql = "SELECT * FROM Voucher"; } //var mssqlConn = new MsSqlConnectionParameters("localhost", "nwind.mdf", "username", "password", MsSqlAuthorizationType.SqlServer); //var mysqlConn = new MySqlConnectionParameters("localhost", "db name", "username", "password", "port"); var ds = new SqlDataSource("iPro"); ds.Queries.Add(query); ds.RebuildResultSchema(); return(ds); }
public AlbaranesReport(IContextService user, string primarykey) { var server = ConfigurationManager.AppSettings["Server"]; var usuario = ConfigurationManager.AppSettings["User"]; var password = ConfigurationManager.AppSettings["Password"]; DataSource = new SqlDataSource("Report", new MsSqlConnectionParameters(server, user.BaseDatos, usuario, password, MsSqlAuthorizationType.SqlServer)); DataSource.Name = "Report"; var mainQuery = new CustomSqlQuery("Albaranes", "SELECT * FROM [Albaranes] "); if (!string.IsNullOrEmpty(primarykey)) { mainQuery.Parameters.Add(new QueryParameter("empresa", typeof(string), user.Empresa)); mainQuery.Parameters.Add(new QueryParameter("referencia", typeof(string), primarykey)); mainQuery.Sql = "SELECT * FROM [Albaranes] where empresa=@empresa and referencia=@referencia"; } DataSource.Queries.Add(new CustomSqlQuery("clientes", string.Format("SELECT c.*,d.direccion as [Direccioncliente],d.poblacion as [Poblacioncliente],d.cp as [Cpcliente],d.telefono as [Telefonocliente] FROM [Clientes] as c left join direcciones as d on d.empresa=c.empresa and d.tipotercero={0} and d.fkentidad=c.fkcuentas", (int)TiposCuentas.Clientes))); DataSource.Queries.Add(new CustomSqlQuery("empresa", "SELECT e.*,d.direccion as [Direccionempresa],d.poblacion as [Poblacionempresa],d.cp as [Cpempresa],d.telefono as [Telefonoempresa] FROM [Empresas] as e left join direcciones as d on d.empresa=e.id and d.tipotercero=-1 and d.fkentidad=e.id")); DataSource.Queries.Add(mainQuery); DataSource.Queries.Add(new CustomSqlQuery("Albaraneslin", "SELECT al.*,u.textocorto as [Unidadesdescripcion] FROM [AlbaranesLin] as al" + " inner join Familiasproductos as fp on fp.empresa=al.empresa and fp.id=substring(al.fkarticulos,0,3)" + " left join unidades as u on fp.fkunidadesmedida=u.id")); DataSource.Queries.Add(new CustomSqlQuery("Albaranestotales", "SELECT * FROM [AlbaranesTotales]")); DataSource.Queries.Add(new CustomSqlQuery("Formaspago", "SELECT * FROM [formaspago]")); DataSource.Queries.Add(new CustomSqlQuery("Articulos", "SELECT * FROM Articulos")); // Create a master-detail relation between the queries. DataSource.Relations.Add("Albaranes", "Albaraneslin", new[] { new RelationColumnInfo("empresa", "empresa"), new RelationColumnInfo("id", "fkalbaranes") }); DataSource.Relations.Add("Albaraneslin", "Articulos", new[] { new RelationColumnInfo("empresa", "empresa"), new RelationColumnInfo("fkarticulos", "id") }); DataSource.Relations.Add("Albaranes", "Formaspago", new[] { new RelationColumnInfo("fkformaspago", "id") }); DataSource.Relations.Add("Albaranes", "Albaranestotales", new[] { new RelationColumnInfo("empresa", "empresa"), new RelationColumnInfo("id", "fkalbaranes") }); DataSource.Relations.Add("Albaranes", "clientes", new[] { new RelationColumnInfo("empresa", "empresa"), new RelationColumnInfo("fkclientes", "fkcuentas") }); DataSource.Relations.Add("Albaranes", "empresa", new[] { new RelationColumnInfo("empresa", "id") }); DataSource.RebuildResultSchema(); }
private static SqlDataSource GetDetailData() { SqlDataSource ds = new SqlDataSource(new Access2007ConnectionParameters(@"..\..\App_Data\nwind.mdb", "")); ds.Queries.Add(new CustomSqlQuery("Products", "select * from Products")); ds.RebuildResultSchema(); return(ds); }
public SqlDataSource CreateDataSource(string connectionStringName, SelectQuery query) { SqlDataSource ds = new SqlDataSource(connectionStringName); ds.Queries.Add(query); ds.RebuildResultSchema(); return(ds); }
SqlDataSource CreateSqlDataSource() { SqlDataSource ds = new SqlDataSource("ConnectionString"); ds.Name = "TarasolDB"; //ds.Queries.Add(new CustomSqlQuery("CUSTOMER", "SELECT * FROM [CUSTOMER]")); ds.RebuildResultSchema(); return(ds); }
SqlDataSource CreateSqlDataSource() { SqlDataSource ds = new SqlDataSource("nwindConnectionString"); ds.Name = "NWind"; ds.Queries.Add(new CustomSqlQuery("Categories", "SELECT * FROM [Categories]")); ds.RebuildResultSchema(); return(ds); }
XtraReport CreateReport() { // Create a new report instance. XtraReport report = new XtraReport(); // Create a data source. SqlDataSource ds = new SqlDataSource(new XmlFileConnectionParameters("../../Cars.xml")); // Create a query to access fields of the Cars data table. SelectQuery query = SelectQueryFluentBuilder .AddTable("Cars") .SelectColumns("ID", "Trademark", "Model", "Category", "Description") .Build("MyQuery"); ds.Queries.Add(query); // Make the data source structure displayed // in the Field List of an End-User Report Designer. ds.RebuildResultSchema(); // Assign the data source to the report. report.DataSource = ds; report.DataMember = "MyQuery"; // Add a detail band to the report. DetailBand detailBand = new DetailBand(); report.Bands.Add(detailBand); // Add a label to the detail band. XRLabel label = new XRLabel { WidthF = 300 }; label.Text = "[MyQuery.Trademark] [MyQuery.Model]"; label.LeftF = 30; detailBand.Controls.Add(label); detailBand.Height = (int)label.HeightF; // Create a group header band and add it to the report. GroupHeaderBand ghBand = new GroupHeaderBand(); report.Bands.Add(ghBand); // Create a grouping criteria. ghBand.GroupFields.Add(new GroupField("Category")); // Display the grouping criteria in the group header. XRLabel labelGroup = new XRLabel(); labelGroup.Text = "[MyQuery.Category]"; ghBand.Controls.Add(labelGroup); ghBand.Height = (int)labelGroup.HeightF; return(report); }
Dictionary <string, object> GetAvailableDataSources() { var dataSources = new Dictionary <string, object>(); SqlDataSource ds = new SqlDataSource("Northwind_Connection"); var query = SelectQueryFluentBuilder.AddTable("Products").SelectAllColumns().Build("Products"); ds.Queries.Add(query); ds.RebuildResultSchema(); dataSources.Add("SqlDataSource", ds); return(dataSources); }
private void BindToData() { SqlDataSource ds = new SqlDataSource("Northwind"); CustomSqlQuery query = new CustomSqlQuery(); query.Name = "Products"; query.Sql = "SELECT * FROM Products"; ds.Queries.Add(query); ds.RebuildResultSchema(); ASPxReportDesigner1.DataSources.Add("Northwind", ds); }
public ActionResult Designer() { Models.ReportDesignerModel model = new Models.ReportDesignerModel(); // Create a SQL data source with the specified connection string. SqlDataSource ds = new SqlDataSource("NWindConnectionString"); // Create a SQL query to access the Products data table. SelectQuery query = SelectQueryFluentBuilder.AddTable("Products").SelectAllColumnsFromTable().Build("Products"); ds.Queries.Add(query); ds.RebuildResultSchema(); model.DataSources = new Dictionary <string, object>(); model.DataSources.Add("Northwind", ds); return(View(model)); }
Dictionary <string, object> GetAvailableDataSources() { //SqlConnection connection = new SqlConnection("data source=(localdb)/mssqllocaldb;initial catalog=aspnet-DXWebApplication1-20200900153700;integrated security=SSPI"); //connection.Open(); var dataSources = new Dictionary <string, object>(); SqlDataSource ds = new SqlDataSource("Northwind_Connection"); var productsQuery = SelectQueryFluentBuilder.AddTable("Products").SelectAllColumns().Build("Products"); var customersQuery = SelectQueryFluentBuilder.AddTable("Customers").SelectAllColumns().Build("Customers"); ds.Queries.Add(productsQuery); ds.Queries.Add(customersQuery); ds.RebuildResultSchema(); dataSources.Add("SqlDataSource", ds); return(dataSources); }
public static SqlDataSource CreateSqlDataSource() { MsSqlConnectionParameters connectionParameters = new MsSqlConnectionParameters(".", "NorthWind", null, null, MsSqlAuthorizationType.Windows); var sqlDataSource = new SqlDataSource(connectionParameters) { Name = "Sql_Categories" }; var categoriesQuery = SelectQueryFluentBuilder.AddTable("Categories"). SelectAllColumnsFromTable(). Build("Categories"); sqlDataSource.Queries.Add(categoriesQuery); sqlDataSource.RebuildResultSchema(); return(sqlDataSource); }
public object GetReportDesignerModel([FromForm] string reportUrl) { Dictionary <string, object> dataSources = new Dictionary <string, object>(); SqlDataSource ds = new SqlDataSource("SevenR"); // Create a SQL query to access the Products data table. SelectQuery query = SelectQueryFluentBuilder.AddTable("GN_EMPRE").SelectAllColumnsFromTable().Build("GN_EMPRE"); ds.Queries.Add(query); ds.RebuildResultSchema(); dataSources.Add("Northwind", ds); string modelJsonScript = new ReportDesignerClientSideModelGenerator(HttpContext.RequestServices).GetJsonModelScript(reportUrl, dataSources, "/DXXRD", "/DXXRDV", "/DXXQB"); return(new JavaScriptSerializer().Deserialize <object>(modelJsonScript)); }
public TransformacioneslotesReport(IContextService user, string primarykey) { var server = ConfigurationManager.AppSettings["Server"]; var usuario = ConfigurationManager.AppSettings["User"]; var password = ConfigurationManager.AppSettings["Password"]; DataSource = new SqlDataSource("Report", new MsSqlConnectionParameters(server, user.BaseDatos, usuario, password, MsSqlAuthorizationType.SqlServer)); DataSource.Name = "Report"; var mainQuery = new CustomSqlQuery("Transformacioneslotes", "SELECT *,'' as [CodigoTrabajo],'' as [Trabajo],'' as [CodigoAcabadoInicial],'' as [CodigoAcabadoFinal],'' as [AcabadoInicial],'' as [AcabadoFinal] FROM [Transformacioneslotes] "); if (!string.IsNullOrEmpty(primarykey)) { mainQuery.Parameters.Add(new QueryParameter("empresa", typeof(string), user.Empresa)); mainQuery.Parameters.Add(new QueryParameter("referencia", typeof(string), primarykey)); mainQuery.Sql = "SELECT t.*,tr.id as [CodigoTrabajo],tr.descripcion as [Trabajo],tr.fkacabadoinicial as [CodigoAcabadoInicial],tr.fkacabadofinal as [CodigoAcabadoFinal],aini.descripcion as [AcabadoInicial],afin.descripcion as [AcabadoFinal] FROM [Transformacioneslotes] as t " + " inner join trabajos as tr on tr.empresa= t.empresa and tr.id=t.fktrabajos " + " left join acabados as aini on aini.id = tr.fkacabadoinicial " + " left join acabados as afin on aini.id = tr.fkacabadofinal " + " where t.empresa=@empresa and t.referencia=@referencia"; } DataSource.Queries.Add(new CustomSqlQuery("empresa", "SELECT e.*,d.direccion as [Direccionempresa],d.poblacion as [Poblacionempresa],d.cp as [Cpempresa],d.telefono as [Telefonoempresa] FROM [Empresas] as e left join direcciones as d on d.empresa=e.id and d.tipotercero=-1 and d.fkentidad=e.id")); DataSource.Queries.Add(mainQuery); DataSource.Queries.Add(new CustomSqlQuery("Transformacionesloteslin", "SELECT pr.*,u.textocorto as [Unidadesdescripcion] FROM [TransformacioneslotesLin] as pr" + " inner join Familiasproductos as fp on fp.empresa=pr.empresa and fp.id=substring(pr.fkarticulos,0,3) " + " left join unidades as u on fp.fkunidadesmedida=u.id")); // Create a master-detail relation between the queries. DataSource.Relations.Add("Transformacioneslotes", "Transformacionesloteslin", new[] { new RelationColumnInfo("empresa", "empresa"), new RelationColumnInfo("id", "fkTransformacioneslotes") }); DataSource.Relations.Add("Transformacioneslotes", "empresa", new[] { new RelationColumnInfo("empresa", "id") }); DataSource.Relations.Add("Transformacioneslotes", "Formaspago", new[] { new RelationColumnInfo("fkformaspago", "id") }); DataSource.RebuildResultSchema(); }
public AlbaranesComprasReport(IContextService user, string primarykey) { var server = ConfigurationManager.AppSettings["Server"]; var usuario = ConfigurationManager.AppSettings["User"]; var password = ConfigurationManager.AppSettings["Password"]; DataSource = new SqlDataSource("Report", new MsSqlConnectionParameters(server, user.BaseDatos, usuario, password, MsSqlAuthorizationType.SqlServer)); DataSource.Name = "Report"; var mainQuery = new CustomSqlQuery("Albaranes", "SELECT * FROM [AlbaranesCompras] "); if (!string.IsNullOrEmpty(primarykey)) { mainQuery.Parameters.Add(new QueryParameter("empresa", typeof(string), user.Empresa)); mainQuery.Parameters.Add(new QueryParameter("referencia", typeof(string), primarykey)); mainQuery.Sql = "SELECT * FROM [AlbaranesCompras] where empresa=@empresa and referencia=@referencia"; } DataSource.Queries.Add(new CustomSqlQuery("clientes", "SELECT * FROM [Proveedores]")); DataSource.Queries.Add(new CustomSqlQuery("empresa", "SELECT * FROM [Empresas]")); DataSource.Queries.Add(mainQuery); DataSource.Queries.Add(new CustomSqlQuery("Albaraneslin", "SELECT * FROM [AlbaranesComprasLin]")); DataSource.Queries.Add(new CustomSqlQuery("Albaranestotales", "SELECT * FROM [AlbaranesComprasTotales]")); // Create a master-detail relation between the queries. DataSource.Relations.Add("Albaranes", "Albaraneslin", new[] { new RelationColumnInfo("empresa", "empresa"), new RelationColumnInfo("id", "fkalbaranes") }); DataSource.Relations.Add("Albaranes", "Albaranestotales", new[] { new RelationColumnInfo("empresa", "empresa"), new RelationColumnInfo("id", "fkalbaranes") }); DataSource.Relations.Add("Albaranes", "clientes", new[] { new RelationColumnInfo("empresa", "empresa"), new RelationColumnInfo("fkproveedores", "fkcuentas") }); DataSource.Relations.Add("Albaranes", "empresa", new[] { new RelationColumnInfo("empresa", "id") }); DataSource.RebuildResultSchema(); }
static Customer() { try { SqlDataSource ds = new SqlDataSource("NWindConnectionString"); SelectQuery query = SelectQueryFluentBuilder .AddTable("Customers") .SelectAllColumns() .Build("Customers"); ds.Queries.Add(query); ds.RebuildResultSchema(); ds.Fill(); ITable src = ds.Result["Customers"]; foreach (var row in src) { currentCustomers.Add(new Customer() { CustomerID = row.GetValue <string>("CustomerID"), Address = row.GetValue <string>("Address"), CompanyName = row.GetValue <string>("CompanyName"), ContactName = row.GetValue <string>("ContactName"), ContactTitle = row.GetValue <string>("ContactTitle"), Country = row.GetValue <string>("Country"), City = row.GetValue <string>("City"), Fax = row.GetValue <string>("Fax"), Phone = row.GetValue <string>("Phone"), PostalCode = row.GetValue <string>("PostalCode"), Region = row.GetValue <string>("Region") }); } } catch { currentCustomers.Add(new Customer() { Address = "Obere Str. 57", City = "Berlin", CompanyName = "Alfreds Futterkiste", ContactName = "Maria Anders", ContactTitle = "Sales Representative", Country = "Germany", CustomerID = "ALFKI", Fax = "030-0076545", Phone = "030-0074321", PostalCode = "12209" }); } }
public TraspasosalmacenReport(IContextService user, string primarykey) { var server = ConfigurationManager.AppSettings["Server"]; var usuario = ConfigurationManager.AppSettings["User"]; var password = ConfigurationManager.AppSettings["Password"]; DataSource = new SqlDataSource("Report", new MsSqlConnectionParameters(server, user.BaseDatos, usuario, password, MsSqlAuthorizationType.SqlServer)); DataSource.Name = "Report"; var mainQuery = new CustomSqlQuery("Traspasosalmacen", "SELECT *,'' as [Almacenorigen], '' as [Almacendestino],'' as [Zonadestino] FROM [Traspasosalmacen] "); if (!string.IsNullOrEmpty(primarykey)) { mainQuery.Parameters.Add(new QueryParameter("empresa", typeof(string), user.Empresa)); mainQuery.Parameters.Add(new QueryParameter("referencia", typeof(string), primarykey)); mainQuery.Sql = "SELECT t.*, a.descripcion as [Almacenorigen], a2.descripcion as [Almacendestino], az.descripcion as [Zonadestino] FROM [Traspasosalmacen] as t " + " inner join almacenes as a on a.empresa=t.empresa and a.id=t.fkalmacen " + " inner join almacenes as a2 on a2.empresa = t.empresa and a2.id=t.fkalmacendestino " + " left join almaceneszona as az on az.empresa= a2.empresa and az.fkalmacenes=a2.id and CONVERT(varchar(3), az.id) =t.fkzonas" + " where t.empresa=@empresa and t.referencia=@referencia"; } DataSource.Queries.Add(new CustomSqlQuery("proveedores", "SELECT * FROM [Proveedores]")); DataSource.Queries.Add(new CustomSqlQuery("empresa", "SELECT * FROM [Empresas]")); DataSource.Queries.Add(mainQuery); DataSource.Queries.Add(new CustomSqlQuery("Traspasosalmacenlin", "SELECT * FROM [TraspasosalmacenLin]")); // Create a master-detail relation between the queries. DataSource.Relations.Add("Traspasosalmacen", "Traspasosalmacenlin", new[] { new RelationColumnInfo("empresa", "empresa"), new RelationColumnInfo("id", "fktraspasosalmacen") }); DataSource.Relations.Add("Traspasosalmacen", "proveedores", new[] { new RelationColumnInfo("empresa", "empresa"), new RelationColumnInfo("fkproveedores", "fkcuentas") }); DataSource.Relations.Add("Traspasosalmacen", "empresa", new[] { new RelationColumnInfo("empresa", "id") }); DataSource.RebuildResultSchema(); }
public SqlDataSource CreateSqlDataSource() { SqlDataSource dataSource = new SqlDataSource() { Name = this.Name, ConnectionName = this.Connection.ConnectionName, ConnectionParameters = this.Connection.ConnectionParameters }; if (Queries != null) { foreach (QueryWrapper query in Queries) { dataSource.Queries.Add(query.CreateCustomSqlQuery()); } } dataSource.RebuildResultSchema(); return(dataSource); }
internal static SqlDataSource BindToData(string queryComand) { //BUSCAR LA CADENA DE CONEXXION DEL CONTENEDOR DE DATOS SELLECCIONADO StoredProcQuery var cnnContext = new SiroDb(); var cnn = cnnContext.Database.Connection.ConnectionString.Split(';'); //// Create a data source with the required connection parameters. MsSqlConnectionParameters connectionParameters = new MsSqlConnectionParameters(cnn[0].Split('=')[1], cnn[1].Split('=')[1], cnn[3].Split('=')[1], cnn[4].Split('=')[1], MsSqlAuthorizationType.SqlServer); SqlDataSource ds = new SqlDataSource(connectionParameters); CustomSqlQuery query = new CustomSqlQuery(); query.Name = @"customQuery"; query.Sql = queryComand; // Add the query to the collection. ds.Queries.Add(query); // Make the data source structure displayed // in the Field List of an End-User Report Designer. ds.RebuildResultSchema(); return(ds); }
string ValidateSql() { if (string.IsNullOrEmpty(this.Sql)) { return("You cannot leave the Sql empty"); } string sql = Sql; var testDS = new SqlDataSource { ConnectionName = this.OwnerDataSource.Connection.ConnectionName, ConnectionParameters = this.OwnerDataSource.Connection.ConnectionParameters }; try { testDS.Queries.Add(new CustomSqlQuery(this.Name, sql)); testDS.RebuildResultSchema(); } catch (Exception e) { return(e.Message); } return(string.Empty); }
private SqlDataSource BindToData() { // Create a data source with the required connection parameters. Access97ConnectionParameters connectionParameters = new Access97ConnectionParameters("../../nwind.mdb", "", ""); SqlDataSource ds = new SqlDataSource(connectionParameters); // Create an SQL query to access the Products table. CustomSqlQuery query = new CustomSqlQuery(); query.Name = "customQuery"; query.Sql = "SELECT * FROM Products"; // Add the query to the collection and return the data source. ds.Queries.Add(query); // Make the data source structure displayed // in the Field List of an End-User Report Designer. ds.RebuildResultSchema(); return(ds); }
public object GetDataSource(NameValueCollection args, bool designTime) { var query = new CustomSqlQuery(); query.Name = "Vouchers"; if (designTime) { query.Sql = "SELECT top(5) * FROM Voucher"; } else { query.Sql = "SELECT * FROM Voucher"; } //var mssqlConn = new MsSqlConnectionParameters("localhost", "nwind.mdf", "username", "password", MsSqlAuthorizationType.SqlServer); //var mysqlConn = new MySqlConnectionParameters("localhost", "db name", "username", "password", "port"); var ds = new SqlDataSource("iPro"); ds.Queries.Add(query); ds.RebuildResultSchema(); return ds; }
private XtraReport CreateReport() { Access97ConnectionParameters parameters = new Access97ConnectionParameters(@"|DataDirectory|\nwind.mdb", "", ""); SqlDataSource ds = new SqlDataSource(parameters); SelectQuery query = SelectQueryFluentBuilder.AddTable("Products").SelectAllColumns().Build("Products"); ds.Queries.Add(query); ds.RebuildResultSchema(); XtraReport report = new XtraReport() { DataSource = ds, DataMember = query.Name }; float actualPageWidth = report.PageWidth - (report.Margins.Left + report.Margins.Right); int colCount = 3; float colWidth = actualPageWidth / colCount; XRTable tableDetail = new XRTable() { HeightF = 25f, WidthF = actualPageWidth }; tableDetail.BeginInit(); XRTableRow detailRow = new XRTableRow(); detailRow.WidthF = tableDetail.WidthF; tableDetail.Rows.Add(detailRow); XRTableCell detailCell = new XRTableCell() { WidthF = colWidth }; detailCell.ExpressionBindings.Add(new ExpressionBinding("BeforePrint", "Text", "[ProductName]")); detailRow.Cells.Add(detailCell); detailCell = new XRTableCell() { WidthF = colWidth }; detailCell.ExpressionBindings.Add(new ExpressionBinding("BeforePrint", "Text", "[QuantityPerUnit]")); detailRow.Cells.Add(detailCell); detailCell = new XRTableCell() { WidthF = colWidth }; detailCell.ExpressionBindings.Add(new ExpressionBinding("BeforePrint", "Text", "[UnitPrice]")); detailRow.Cells.Add(detailCell); tableDetail.EndInit(); report.Bands.Add(new DetailBand() { HeightF = 25f }); report.Bands[BandKind.Detail].Controls.Add(tableDetail); return(report); }
public XtraReport CreateDataGroupingReport() { // Create a report. XtraReport report = new XtraReport(); // Create a data source with the required connection parameters. Access97ConnectionParameters connectionParameters = new Access97ConnectionParameters("../../nwind.mdb", "", ""); SqlDataSource ds = new SqlDataSource(connectionParameters); CustomSqlQuery query = new CustomSqlQuery(); query.Name = "customQuery"; query.Sql = "SELECT * FROM CategoryProducts"; ds.Queries.Add(query); ds.RebuildResultSchema(); // Assign the data source to the report. report.DataSource = ds; report.DataMember = "customQuery"; // Create a detail band and add it to the report. DetailBand detail = new DetailBand { HeightF = 40 }; report.Bands.Add(detail); // Create a group header band and add it to the report. GroupHeaderBand ghBand = new GroupHeaderBand { HeightF = 40 }; report.Bands.Add(ghBand); // Create a group field and assign it to the group header band. GroupField groupField = new GroupField("CategoryName"); ghBand.GroupFields.Add(groupField); // Create new labels. XRLabel labelGroup = new XRLabel { ForeColor = System.Drawing.Color.Blue }; XRLabel labelDetail = new XRLabel { LocationF = new System.Drawing.PointF(30, 0) }; // Specify labels' bindings depending on the report's data binding mode. if (Settings.Default.UserDesignerOptions.DataBindingMode == DataBindingMode.Bindings) { labelGroup.DataBindings.Add("Text", null, "customQuery.CategoryName"); labelDetail.DataBindings.Add("Text", null, "customQuery.ProductName"); } else { labelGroup.ExpressionBindings.Add(new ExpressionBinding("BeforePrint", "Text", "[CategoryName]")); labelDetail.ExpressionBindings.Add(new ExpressionBinding("BeforePrint", "Text", "[ProductName]")); } // Add these labels to the report's bands. ghBand.Controls.Add(labelGroup); detail.Controls.Add(labelDetail); return(report); }
public DiarioReport(IContextService user, Dictionary <string, object> dictionary = null) { var server = ConfigurationManager.AppSettings["Server"]; var usuario = ConfigurationManager.AppSettings["User"]; var password = ConfigurationManager.AppSettings["Password"]; DataSource = new SqlDataSource("Report", new MsSqlConnectionParameters(server, user.BaseDatos, usuario, password, MsSqlAuthorizationType.SqlServer)); DataSource.Name = "Report"; var mainQuery = new CustomSqlQuery("Movs", "select m.referencia as doc, " + "m.fecha as fecha, " + "(CASE WHEN l.esdebe = 1 THEN l.fkcuentas ELSE '' END) AS cuenta_debe, " + "(CASE WHEN l.esdebe = -1 THEN l.fkcuentas ELSE '' END) AS cuenta_haber, " + "c.descripcion AS descripcion, " + "l.comentario AS comentario, " + "(CASE WHEN l.esdebe = 1 THEN l.importe ELSE NULL END) AS debe, " + "(CASE WHEN l.esdebe = -1 THEN l.importe ELSE NULL END) AS haber, " + "l.orden as orden " + "from movs as m, movslin as l, " + "cuentas as c " + "where m.empresa = l.empresa and m.id = l.fkmovs and m.empresa = c.empresa and c.id = l.fkcuentas"); if (dictionary != null) { var serie = dictionary["Serie"].ToString(); var documentoDesde = dictionary["DocumentoDesde"].ToString(); var documentoHasta = dictionary["DocumentoHasta"].ToString(); var fechaDesde = dictionary["FechaDesde"]; var fechaHasta = dictionary["FechaHasta"]; var tipoAsiento = dictionary["TipoAsiento"].ToString(); switch (tipoAsiento) { case "Regularización existencias": tipoAsiento = "R3"; break; case "Simulación": tipoAsiento = "F2"; break; case "Asiento vinculado": tipoAsiento = "F3"; break; case "Normal": tipoAsiento = "F1"; break; case "Regularización grupos 6 y 7": tipoAsiento = "R4"; break; case "Cierre": tipoAsiento = "R5"; break; case "Apertura": tipoAsiento = "R2"; break; case "Apertura provisional": tipoAsiento = "R1"; break; } var canalContable = dictionary["CanalContable"].ToString(); var SumaAnteriorDebe = float.Parse(dictionary["SumaAnteriorDebe"].ToString()); var SumaAnteriorHaber = float.Parse(dictionary["SumaAnteriorHaber"].ToString()); mainQuery.Parameters.Add(new QueryParameter("empresa", typeof(string), user.Empresa)); mainQuery.Sql += " and m.empresa=@empresa"; if (!string.IsNullOrEmpty(serie)) { mainQuery.Parameters.Add(new QueryParameter("serie", typeof(string), serie)); mainQuery.Sql += " AND m.fkseriescontables=@serie"; } if (!string.IsNullOrEmpty(documentoDesde)) { mainQuery.Parameters.Add(new QueryParameter("documentoDesde", typeof(string), documentoDesde)); mainQuery.Sql += " AND m.referencia>=@documentoDesde"; } if (!string.IsNullOrEmpty(documentoHasta)) { mainQuery.Parameters.Add(new QueryParameter("documentoHasta", typeof(string), documentoHasta)); mainQuery.Sql += " AND m.referencia<=@documentoHasta"; } if (fechaDesde != null) { mainQuery.Parameters.Add(new QueryParameter("fechaDesde", typeof(DateTime), fechaDesde)); mainQuery.Sql += " AND m.fecha>=@fechaDesde"; } if (fechaHasta != null) { mainQuery.Parameters.Add(new QueryParameter("fechaHasta", typeof(DateTime), fechaHasta)); mainQuery.Sql += " AND m.fecha<=@fechaHasta"; } if (!string.IsNullOrEmpty(tipoAsiento)) { mainQuery.Parameters.Add(new QueryParameter("tipoAsiento", typeof(string), tipoAsiento)); mainQuery.Sql += " AND m.tipoasiento=@tipoAsiento"; } if (!string.IsNullOrEmpty(canalContable)) { mainQuery.Parameters.Add(new QueryParameter("canalContable", typeof(string), canalContable)); mainQuery.Sql += " AND m.canalcontable=@canalContable"; } if (SumaAnteriorDebe > 0 || SumaAnteriorHaber > 0) { mainQuery.Parameters.Add(new QueryParameter("SumaAnteriorDebe", typeof(float), SumaAnteriorDebe)); mainQuery.Parameters.Add(new QueryParameter("SumaAnteriorHaber", typeof(float), SumaAnteriorHaber)); mainQuery.Sql += " UNION "; mainQuery.Sql += " SELECT '' AS [Doc.], NULL AS[Fecha], '' AS[Cuenta Debe], '' AS[Cuenta Haber], '' AS[Descripcion], "; mainQuery.Sql += " 'SUMA ANTERIOR' AS[Comentario], @SumaAnteriorDebe AS[Debe], @SumaAnteriorHaber AS[Haber], 0 AS [Orden] "; } } DataSource.Queries.Add(new CustomSqlQuery("Empresa", "SELECT id, nombre FROM Empresas WHERE id = '" + user.Empresa + "'")); DataSource.Queries.Add(new CustomSqlQuery("Ejercicios", "SELECT empresa, id, descripcion FROM Ejercicios WHERE id = '" + user.Ejercicio + "'")); DataSource.Queries.Add(mainQuery); DataSource.Relations.Add("Movs", "Empresa", new[] { new RelationColumnInfo("empresa", "id") }); DataSource.Relations.Add("Movs", "Ejercicios", new[] { new RelationColumnInfo("empresa", "empresa"), new RelationColumnInfo("fkejercicio", "id") }); DataSource.RebuildResultSchema(); }
public SumasYSaldosReport(IContextService user, Dictionary <string, object> dictionary = null) { var server = ConfigurationManager.AppSettings["Server"]; var usuario = ConfigurationManager.AppSettings["User"]; var password = ConfigurationManager.AppSettings["Password"]; DataSource = new SqlDataSource("Report", new MsSqlConnectionParameters(server, user.BaseDatos, usuario, password, MsSqlAuthorizationType.SqlServer)); DataSource.Name = "Report"; var mainQuery = new CustomSqlQuery("Cuentas", "select m.fkcuentas as Cuenta, c.descripcion as Descripcion, m.debe as Debe, m.haber as Haber ," + "(case when m.saldo >= 0 THEN m.saldo else null END) AS Deudor ," + "(case when m.saldo < 0 THEN(m.saldo * -1) else null END) AS Acreedor from cuentas as c left join maes as m on c.id = m.fkcuentas " + "where c.empresa = @empresa and(m.fkejercicio = @fkejercicio or m.fkejercicio is null) "); mainQuery.Parameters.Add(new QueryParameter("fkejercicio", typeof(string), user.Ejercicio)); mainQuery.Parameters.Add(new QueryParameter("empresa", typeof(string), user.Empresa)); if (dictionary != null) { var CuentaDesde = dictionary["CuentaDesde"].ToString(); var CuentaHasta = dictionary["CuentaHasta"].ToString(); var PorGrupos = Convert.ToBoolean(dictionary["PorGrupos"]); var PorSubgrupos = Convert.ToBoolean(dictionary["PorSubgrupos"]); var PorMayor = Convert.ToBoolean(dictionary["PorMayor"]); var PorSubmayor = Convert.ToBoolean(dictionary["PorSubmayor"]); var PorNivelCinco = Convert.ToBoolean(dictionary["PorNivelCinco"]); var PorSubcuenta = Convert.ToBoolean(dictionary["PorSubcuenta"]); var MostrarCuentasSinSaldo = Convert.ToBoolean(dictionary["MostrarCuentasSinSaldo"]); var MostrarCuentasSinMovimientos = Convert.ToBoolean(dictionary["MostrarCuentasSinMovimientos"]); var FechaInforme = dictionary["FechaInforme"]; //Obtenemos el numero de digitos de la cuenta que tiene la empresa var service = FService.Instance.GetService(typeof(EmpresaModel), user); var empresaModel = service.get(user.Empresa) as EmpresaModel; int digitos = Funciones.Qint(empresaModel.DigitosCuentas).Value; CuentaDesde = (CuentaDesde + "00000000000000000000000000000000000").Substring(0, digitos); CuentaHasta = (CuentaHasta + "00000000000000000000000000000000000").Substring(0, digitos); var cuentaDesdeAux1 = CuentaDesde.ToString().Substring(0, 1); var cuentaDesdeAux2 = CuentaDesde.ToString().Substring(0, 2); var cuentaDesdeAux3 = CuentaDesde.ToString().Substring(0, 3); var cuentaDesdeAux4 = CuentaDesde.ToString().Substring(0, 4); mainQuery.Sql += ("AND ( "); if (PorGrupos) { mainQuery.Sql += ("((c.id='" + cuentaDesdeAux1 + "') or (c.id>='" + CuentaDesde + "' and c.id<='" + CuentaHasta + "' and c.nivel=1)) "); } if (PorSubgrupos) { if (PorGrupos) { mainQuery.Sql += (" or "); } mainQuery.Sql += ("((c.id='" + cuentaDesdeAux2 + "') or (c.id>='" + CuentaDesde + "' and c.id<='" + CuentaHasta + "' and c.nivel=2)) "); } if (PorMayor) { if (PorGrupos || PorSubgrupos) { mainQuery.Sql += (" or "); } mainQuery.Sql += ("((c.id='" + cuentaDesdeAux3 + "') or (c.id>='" + CuentaDesde + "' and c.id<='" + CuentaHasta + "' and c.nivel=3)) "); } if (PorSubmayor) { if (PorGrupos || PorSubgrupos || PorMayor) { mainQuery.Sql += (" or "); } mainQuery.Sql += ("((c.id='" + cuentaDesdeAux4 + "') or (c.id>='" + CuentaDesde + "' and c.id<='" + CuentaHasta + "' and c.nivel=4)) "); } if (PorSubcuenta) { if (PorGrupos || PorSubgrupos || PorMayor || PorSubmayor) { mainQuery.Sql += (" or "); } mainQuery.Sql += ("(c.id>='" + CuentaDesde + "' and c.id<='" + CuentaHasta + "' and c.nivel=0) "); } /* * if (PorSubcuenta) * { * mainQuery.Sql += ("(c.id>='"+CuentaDesde+ "' and c.id<='"+CuentaHasta+"' and c.nivel=0) "); * } * * if (PorGrupos) * { * if(PorSubcuenta || PorGrupos || PorSubgrupos || PorMayor || PorSubmayor) * { * mainQuery.Sql += (" or "); * } * mainQuery.Sql += ("((c.id='"+cuentaDesdeAux1+ "') or (c.id>='"+CuentaDesde+ "' and c.id<='"+CuentaHasta+"' and c.nivel=1)) "); * } * * if (PorSubgrupos) * { * if (PorSubcuenta || PorGrupos || PorSubgrupos || PorMayor || PorSubmayor) * { * mainQuery.Sql += (" or "); * } * mainQuery.Sql += ("((c.id='"+cuentaDesdeAux2+ "') or (c.id>='"+CuentaDesde+ "' and c.id<='"+CuentaHasta+"' and c.nivel=2)) "); * } * * if (PorMayor) * { * if (PorSubcuenta || PorGrupos || PorSubgrupos || PorMayor || PorSubmayor) * { * mainQuery.Sql += (" or "); * } * mainQuery.Sql += ("((c.id='"+cuentaDesdeAux3+ "') or (c.id>='"+CuentaDesde+ "' and c.id<='"+CuentaHasta+"' and c.nivel=3)) "); * } * * if (PorSubmayor) * { * if (PorSubcuenta || PorGrupos || PorSubgrupos || PorMayor || PorSubmayor) * { * mainQuery.Sql += (" or "); * } * mainQuery.Sql += ("((c.id='"+cuentaDesdeAux4+ "') or (c.id>='"+CuentaDesde+ "' and c.id<='"+CuentaHasta+"' and c.nivel=4)) "); * } */ mainQuery.Sql += (")"); //En caso de que el usuario quiera mostrar cuentas sin saldo, se mostraran las de con saldo y la de sin saldo if (!MostrarCuentasSinSaldo) { mainQuery.Sql += (" and (m.debe<>m.haber)"); } if (!MostrarCuentasSinMovimientos) { mainQuery.Sql += ("AND (m.id is not null)"); } } DataSource.Queries.Add(new CustomSqlQuery("Empresa", "SELECT id, nombre FROM Empresas WHERE id = '" + user.Empresa + "'")); DataSource.Queries.Add(new CustomSqlQuery("Ejercicios", "SELECT empresa, id, descripcion FROM Ejercicios WHERE id = '" + user.Ejercicio + "'")); DataSource.Queries.Add(mainQuery); DataSource.RebuildResultSchema(); }
public BalancePedidosPe(IContextService user, Dictionary <string, object> dictionary = null) { var server = ConfigurationManager.AppSettings["Server"]; var usuario = ConfigurationManager.AppSettings["User"]; var password = ConfigurationManager.AppSettings["Password"]; DataSource = new SqlDataSource("Report", new MsSqlConnectionParameters(server, user.BaseDatos, usuario, password, MsSqlAuthorizationType.SqlServer)); DataSource.Name = "Report"; var mainQuery = new CustomSqlQuery("Pedidos", "SELECT p.referencia AS Referencia, (p.fkclientes + ' ' + p.nombrecliente) AS Cliente," + " p.fechadocumento AS Fecha, (l.fkarticulos + ' ' + l.descripcion) AS Código," + " l.cantidad AS Piezas, l.metros AS Metros, u.textocorto AS Unidad," + " (CASE WHEN l.largo > 0 AND l.ancho > 0 THEN(l.cantidad * l.largo * l.ancho) ELSE NULL END) AS M2," + " l.precio AS Precio, l.porcentajedescuento AS[% Dto], l.importe AS Total" + " FROM Pedidos AS p" + " LEFT JOIN PedidosLin AS l ON l.empresa = p.empresa AND l.fkpedidos = p.id" + " LEFT JOIN Unidades AS u ON u.empresa = p.empresa AND u.id = l.fkunidades"); var albaranesQuery = new CustomSqlQuery("Albaranes", "SELECT a.referencia AS Referencia, a.fechadocumento AS Fecha, (l.fkarticulos + ' ' + l.descripcion) AS Código," + " l.cantidad AS Cantidad, l.metros AS Metros, u.textocorto AS Unidad," + " (CASE WHEN l.largo > 0 AND l.ancho > 0 THEN(l.cantidad * l.largo * l.ancho) ELSE NULL END) AS M2," + " l.precio AS Precio, l.importe AS Total" + " FROM Albaranes AS a " + " LEFT JOIN Pedidos AS p ON p.empresa = a.empresa AND p.referencia = a.fkpedidos " + " LEFT JOIN AlbaranesLin AS l ON l.empresa = a.empresa AND l.fkalbaranes = a.id " + " LEFT JOIN Unidades AS u ON u.empresa = a.empresa AND u.id = l.fkunidades"); var costesQuery = new CustomSqlQuery("PedidosCostesFabricacion", "SELECT(t.id + ' ' + t.descripcion) AS[Tarea realizada]," + " c.cantidad AS Cantidad, c.precio AS Precio, c.total AS Total " + " FROM PedidosCostesFabricacion c " + " LEFT JOIN Pedidos AS p ON p.empresa = c.empresa AND p.id = c.fkpedido " + " LEFT JOIN Tareas AS t ON t.empresa = c.empresa AND t.id = c.fktarea"); if (dictionary != null) { var Series = dictionary["Series"].ToString(); var Estado = dictionary["Estado"].ToString(); var PedidoDesde = dictionary["PedidoDesde"].ToString(); var PedidoHasta = dictionary["PedidoHasta"].ToString(); var fechaDesde = dictionary["FechaDesde"]; var fechaHasta = dictionary["FechaHasta"]; var ClienteDesde = dictionary["ClienteDesde"].ToString(); var ClienteHasta = dictionary["ClienteHasta"].ToString(); var FechaInforme = dictionary["FechaInforme"]; // Condiciones.Clear(); mainQuery.Parameters.Add(new QueryParameter("empresa", typeof(string), user.Empresa)); mainQuery.Sql += " WHERE p.empresa = '" + user.Empresa + "'"; albaranesQuery.Parameters.Add(new QueryParameter("empresa", typeof(string), user.Empresa)); albaranesQuery.Sql += " WHERE p.empresa = '" + user.Empresa + "'" + "AND tipoalbaran = 4"; costesQuery.Parameters.Add(new QueryParameter("empresa", typeof(string), user.Empresa)); costesQuery.Sql += " WHERE p.empresa = '" + user.Empresa + "'"; if (!string.IsNullOrEmpty(PedidoDesde)) { //mainQuery mainQuery.Sql += (" AND "); mainQuery.Parameters.Add(new QueryParameter("pedidodesde", typeof(string), PedidoDesde)); mainQuery.Sql += (" p.referencia>=@pedidodesde "); //albaranesQuery albaranesQuery.Sql += (" AND "); albaranesQuery.Parameters.Add(new QueryParameter("pedidodesde", typeof(string), PedidoDesde)); albaranesQuery.Sql += (" p.referencia>=@pedidodesde "); //costesQuery costesQuery.Sql += (" AND "); costesQuery.Parameters.Add(new QueryParameter("pedidodesde", typeof(string), PedidoDesde)); costesQuery.Sql += (" p.referencia>=@pedidodesde "); } if (!string.IsNullOrEmpty(PedidoHasta)) { //mainQuery mainQuery.Sql += (" AND "); mainQuery.Parameters.Add(new QueryParameter("pedidohasta", typeof(string), PedidoHasta)); mainQuery.Sql += (" p.referencia<=@pedidohasta "); //albaranesQuery albaranesQuery.Sql += (" AND "); albaranesQuery.Parameters.Add(new QueryParameter("pedidohasta", typeof(string), PedidoHasta)); albaranesQuery.Sql += (" p.referencia<=@pedidohasta "); //costesQuery costesQuery.Sql += (" AND "); costesQuery.Parameters.Add(new QueryParameter("pedidohasta", typeof(string), PedidoHasta)); costesQuery.Sql += (" p.referencia<=@pedidohasta "); } if (fechaDesde != null) { //mainQuery mainQuery.Parameters.Add(new QueryParameter("fechaDesde", typeof(DateTime), fechaDesde)); mainQuery.Sql += " AND p.fechadocumento>=@fechaDesde"; //albaranesQuery albaranesQuery.Parameters.Add(new QueryParameter("fechaDesde", typeof(DateTime), fechaDesde)); albaranesQuery.Sql += " AND p.fechadocumento>=@fechaDesde"; //costesQuery costesQuery.Parameters.Add(new QueryParameter("fechaDesde", typeof(DateTime), fechaDesde)); costesQuery.Sql += " AND p.fechadocumento>=@fechaDesde"; } if (fechaHasta != null) { //mainQuery mainQuery.Parameters.Add(new QueryParameter("fechaHasta", typeof(DateTime), fechaHasta)); mainQuery.Sql += " AND p.fechadocumento<=@fechaHasta"; //albaranesQuery albaranesQuery.Parameters.Add(new QueryParameter("fechaHasta", typeof(DateTime), fechaHasta)); albaranesQuery.Sql += " AND p.fechadocumento<=@fechaHasta"; //costesQuery costesQuery.Parameters.Add(new QueryParameter("fechaHasta", typeof(DateTime), fechaHasta)); costesQuery.Sql += " AND p.fechadocumento<=@fechaHasta"; } if (!string.IsNullOrEmpty(ClienteDesde)) { //mainQuery mainQuery.Sql += (" AND "); mainQuery.Parameters.Add(new QueryParameter("clientedesde", typeof(string), ClienteDesde)); mainQuery.Sql += (" p.fkclientes>=@clientedesde "); //albaranesQuery albaranesQuery.Sql += (" AND "); albaranesQuery.Parameters.Add(new QueryParameter("clientedesde", typeof(string), ClienteDesde)); albaranesQuery.Sql += (" p.fkclientes>=@clientedesde "); //costesQuery costesQuery.Sql += (" AND "); costesQuery.Parameters.Add(new QueryParameter("clientedesde", typeof(string), ClienteDesde)); costesQuery.Sql += (" p.fkclientes>=@clientedesde "); } if (!string.IsNullOrEmpty(ClienteHasta)) { //mainQuery mainQuery.Sql += (" AND "); mainQuery.Parameters.Add(new QueryParameter("clientehasta", typeof(string), ClienteHasta)); mainQuery.Sql += (" p.fkclientes<=@clientehasta "); //albaranesQuery albaranesQuery.Sql += (" AND "); albaranesQuery.Parameters.Add(new QueryParameter("clientehasta", typeof(string), ClienteHasta)); albaranesQuery.Sql += (" p.fkclientes<=@clientehasta "); //costesQuery costesQuery.Sql += (" AND "); costesQuery.Parameters.Add(new QueryParameter("clientehasta", typeof(string), ClienteHasta)); costesQuery.Sql += (" p.fkclientes<=@clientehasta "); } //mainQuery.Sql += ")"; } else { // mainQuery.Sql += (" GROUP BY c.empresa, c.id, c.descripcion"); // mainQuery.Sql += (")t"); } DataSource.Queries.Add(new CustomSqlQuery("Empresa", "SELECT id, nombre FROM Empresas WHERE id = '" + user.Empresa + "'")); DataSource.Queries.Add(new CustomSqlQuery("Ejercicios", "SELECT empresa, id, descripcion FROM Ejercicios WHERE id = '" + user.Ejercicio + "'")); DataSource.Queries.Add(mainQuery); DataSource.Queries.Add(albaranesQuery); DataSource.Queries.Add(costesQuery); //DataSource.Relations.Add("Pedidos", "Empresa", new[] { // new RelationColumnInfo("empresa", "id")}); DataSource.RebuildResultSchema(); }
public FacturasReport(IContextService user, string primarykey) { var server = ConfigurationManager.AppSettings["Server"]; var usuario = ConfigurationManager.AppSettings["User"]; var password = ConfigurationManager.AppSettings["Password"]; DataSource = new SqlDataSource("Report", new MsSqlConnectionParameters(server, user.BaseDatos, usuario, password, MsSqlAuthorizationType.SqlServer)); DataSource.Name = "Report"; var mainQuery = new CustomSqlQuery("Facturas", "SELECT * FROM [Facturas] "); if (!string.IsNullOrEmpty(primarykey)) { mainQuery.Parameters.Add(new QueryParameter("empresa", typeof(string), user.Empresa)); mainQuery.Parameters.Add(new QueryParameter("referencia", typeof(string), primarykey)); mainQuery.Sql = "SELECT * FROM [Facturas] where empresa=@empresa and referencia=@referencia"; } DataSource.Queries.Add(new CustomSqlQuery("clientes", string.Format("SELECT c.*,d.direccion as [Direccioncliente],d.poblacion as [Poblacioncliente],d.cp as [Cpcliente],d.telefono as [Telefonocliente] FROM [Clientes] as c left join direcciones as d on d.empresa=c.empresa and d.tipotercero={0} and d.fkentidad=c.fkcuentas", (int)TiposCuentas.Clientes))); DataSource.Queries.Add(new CustomSqlQuery("empresa", "SELECT e.*,d.direccion as [Direccionempresa],d.poblacion as [Poblacionempresa],d.cp as [Cpempresa],d.telefono as [Telefonoempresa] FROM [Empresas] as e left join direcciones as d on d.empresa=e.id and d.tipotercero=-1 and d.fkentidad=e.id")); DataSource.Queries.Add(mainQuery); DataSource.Queries.Add(new CustomSqlQuery("Facturaslin", "SELECT fl.*,u.textocorto as [Unidadesdescripcion], (fl.ancho * 100) AS ancho_cm, (fl.largo * 100) AS largo_cm, (fl.grueso * 100) AS grueso_cm FROM [FacturasLin] as fl " + " inner join Familiasproductos as fp on fp.empresa=fl.empresa and fp.id=substring(fl.fkarticulos,0,3)" + " left join unidades as u on fp.fkunidadesmedida=u.id")); DataSource.Queries.Add(new CustomSqlQuery("Facturastotales", "SELECT * FROM [FacturasTotales]")); DataSource.Queries.Add(new CustomSqlQuery("Facturasvencimientos", "SELECT * FROM [Facturasvencimientos]")); DataSource.Queries.Add(new CustomSqlQuery("Formaspago", "SELECT * FROM [formaspago]")); DataSource.Queries.Add(new CustomSqlQuery("Monedas", "SELECT DISTINCT e.id, m.id, abreviatura FROM Monedas m, Facturas f, Empresas e WHERE m.id = f.fkmonedas")); // BANCOSMANDATOS -> Para acceder a los datos bancarios tanto del cliente como de la misma empresa emisora (LL) DataSource.Queries.Add(new CustomSqlQuery("BancosMandatos", "SELECT bm.empresa, bm.fkcuentas, bm.descripcion, bm.iban, bm.bic FROM BancosMandatos bm")); // Create a master-detail relation between the queries. DataSource.Relations.Add("Facturas", "Facturaslin", new[] { new RelationColumnInfo("empresa", "empresa"), new RelationColumnInfo("id", "fkfacturas") }); DataSource.Relations.Add("Facturas", "Facturastotales", new[] { new RelationColumnInfo("empresa", "empresa"), new RelationColumnInfo("id", "fkfacturas") }); DataSource.Relations.Add("Facturas", "Facturasvencimientos", new[] { new RelationColumnInfo("empresa", "empresa"), new RelationColumnInfo("id", "fkfacturas") }); DataSource.Relations.Add("Facturas", "clientes", new[] { new RelationColumnInfo("empresa", "empresa"), new RelationColumnInfo("fkclientes", "fkcuentas") }); DataSource.Relations.Add("Facturas", "empresa", new[] { new RelationColumnInfo("empresa", "id") }); DataSource.Relations.Add("Facturas", "Formaspago", new[] { new RelationColumnInfo("fkformaspago", "id") }); DataSource.Relations.Add("Facturas", "Monedas", new[] { new RelationColumnInfo("empresa", "empresa"), new RelationColumnInfo("fkmonedas", "id") }); DataSource.Relations.Add("Facturaslin", "Articulos", new[] { new RelationColumnInfo("empresa", "empresa"), new RelationColumnInfo("fkarticulos", "id") }); // EMPRESA <-> BANCOSMANDATOS (LL) DataSource.Relations.Add("empresa", "BancosMandatos", new[] { new RelationColumnInfo("id", "empresa") }); // CLIENTES <-> BANCOSMANDATOS (LL) DataSource.Relations.Add("clientes", "BancosMandatos", new[] { new RelationColumnInfo("empresa", "empresa"), new RelationColumnInfo("cuentatesoreria", "fkcuentas") }); DataSource.RebuildResultSchema(); }