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