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")
                });
            }
        }
Ejemplo n.º 2
0
        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();
        }
Ejemplo n.º 3
0
        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();
        }
Ejemplo n.º 4
0
        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);
        }
Ejemplo n.º 5
0
        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();
        }
Ejemplo n.º 6
0
        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);
        }
Ejemplo n.º 7
0
        public SqlDataSource CreateDataSource(string connectionStringName, SelectQuery query)
        {
            SqlDataSource ds = new SqlDataSource(connectionStringName);

            ds.Queries.Add(query);
            ds.RebuildResultSchema();
            return(ds);
        }
Ejemplo n.º 8
0
        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);
        }
Ejemplo n.º 10
0
        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);
        }
Ejemplo n.º 12
0
        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);
        }
Ejemplo n.º 13
0
        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);
        }
Ejemplo n.º 15
0
        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));
        }
Ejemplo n.º 17
0
        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();
        }
Ejemplo n.º 18
0
        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();
        }
Ejemplo n.º 19
0
 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();
        }
Ejemplo n.º 21
0
        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);
        }
Ejemplo n.º 22
0
        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);
        }
Ejemplo n.º 23
0
        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);
        }
Ejemplo n.º 24
0
        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);
        }
Ejemplo n.º 25
0
        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);
        }
Ejemplo n.º 27
0
        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);
        }
Ejemplo n.º 28
0
        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();
        }
Ejemplo n.º 29
0
        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();
        }
Ejemplo n.º 30
0
        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();
        }
Ejemplo n.º 31
0
        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();
        }