private void PrintRow(PrintPageEventArgs e, Lazaro.Pres.Spreadsheet.Row row)
{
Graphics g = e.Graphics;
float CursorX = BodyRect.Left;
float ThisRowHeight = RowHeight;
// Imprimo primero la columna de detalle principal
if (this.Sheet.ColumnHeaders.DetailColumn >= 0)
{
ThisRowHeight = RowHeight / 2;
RectangleF ChRect = new RectangleF(CursorX, CursorY, BodyRect.Width, RowHeight / 2);
g.DrawString(row.Cells[this.Sheet.ColumnHeaders.DetailColumn].ToString(), new Font(RowFont, FontStyle.Bold), Brushes.Black, ChRect, ColumnFormats[this.Sheet.ColumnHeaders.DetailColumn]);
CursorY += ThisRowHeight;
}
for (int i = 0; i < row.Cells.Count; i++)
{
if (i != this.Sheet.ColumnHeaders.DetailColumn && this.Sheet.ColumnHeaders[i].Printable)
{
RectangleF ChRect = new RectangleF(CursorX, CursorY, ColumnWidths[i], ThisRowHeight);
g.DrawString(row.Cells[i].ToString(), RowFont, Brushes.Black, ChRect, ColumnFormats[i]);
g.DrawLine(Pens.LightGray, ChRect.X, ChRect.Bottom, ChRect.Right, ChRect.Bottom);
CursorX += ColumnWidths[i];
}
}
CursorY += ThisRowHeight;
}
private Lazaro.Pres.Spreadsheet.Row FormatRowSum(Lazaro.Pres.Spreadsheet.Sheet sheet, Lazaro.Pres.FieldCollection useFields)
{
Lazaro.Pres.Spreadsheet.Row Reng = new Lazaro.Pres.Spreadsheet.Row(sheet);
if (this.Definicion.KeyColumn != null && this.Definicion.KeyColumn.Printable)
{
Lazaro.Pres.Spreadsheet.Cell KeyCell = Reng.Cells.Add();
KeyCell.Content = "-->";
}
for (int FieldNum = 0; FieldNum < useFields.Count; FieldNum++)
{
if (useFields[FieldNum].Printable)
{
string FieldName = Lazaro.Orm.Data.ColumnValue.GetNameOnly(useFields[FieldNum].Name);
if (FieldNum >= 0)
{
Lazaro.Pres.Spreadsheet.Cell NewCell = Reng.Cells.Add();
if (FieldName == "total")
{
NewCell.Content = totalRenglon;
}
else
{
NewCell.Content = "";
}
}
}
}
return(Reng);
}
protected override Lazaro.Pres.Spreadsheet.Row FormatRow(int itemId, Lfx.Data.Row row, Lazaro.Pres.Spreadsheet.Sheet sheet, Lazaro.Pres.FieldCollection useFields)
{
Lazaro.Pres.Spreadsheet.Row Res = base.FormatRow(itemId, row, sheet, useFields);
switch (row.Fields["tipo_fac"].ValueString)
{
case "NCA":
case "NCB":
case "NCC":
case "NCE":
case "NCM":
row.Fields["gravado"].Value = -row.Fields["gravado"].ValueDecimal;
row.Fields["total"].Value = -row.Fields["total"].ValueDecimal;
break;
}
if (row.Fields["anulada"].ValueInt != 0)
{
Res.Cells[4].Content = "ANULADA";
}
return(Res);
}
protected virtual Lazaro.Pres.Spreadsheet.Row FormatRow(int itemId, Lfx.Data.Row row, Lazaro.Pres.Spreadsheet.Sheet sheet, Lazaro.Pres.FieldCollection useFields)
{
Lazaro.Pres.Spreadsheet.Row Reng = new Lazaro.Pres.Spreadsheet.Row(sheet);
if (this.Definicion.KeyColumn != null && this.Definicion.KeyColumn.Printable) {
Lazaro.Pres.Spreadsheet.Cell KeyCell = Reng.Cells.Add();
KeyCell.Content = itemId;
}
for (int FieldNum = 0; FieldNum < useFields.Count; FieldNum++) {
if (useFields[FieldNum].Printable) {
string FieldName = Lfx.Data.Field.GetNameOnly(useFields[FieldNum].Name);
if (FieldNum >= 0) {
Lazaro.Pres.Spreadsheet.Cell NewCell = Reng.Cells.Add();
switch (useFields[FieldNum].DataType) {
case Lfx.Data.InputFieldTypes.Integer:
case Lfx.Data.InputFieldTypes.Serial:
if (row[FieldName] == null || row[FieldName] is DBNull)
NewCell.Content = null;
else if (useFields[FieldNum].Format != null)
NewCell.Content = System.Convert.ToInt32(row[FieldName]).ToString(useFields[FieldNum].Format);
else
NewCell.Content = row[FieldName].ToString();
break;
case Lfx.Data.InputFieldTypes.Relation:
case Lfx.Data.InputFieldTypes.Text:
case Lfx.Data.InputFieldTypes.Memo:
if (row[FieldName] == null)
NewCell.Content = null;
else if (row[FieldName] is System.Byte[])
NewCell.Content = System.Text.Encoding.Default.GetString(((System.Byte[])(row[FieldName])));
else
NewCell.Content = row.Fields[FieldName].Value.ToString();
break;
case Lfx.Data.InputFieldTypes.Currency:
double ValorCur = (row[FieldName] == null || row[FieldName] is DBNull) ? 0 : System.Convert.ToDouble(row[FieldName]);
NewCell.Content = ValorCur;
break;
case Lfx.Data.InputFieldTypes.Numeric:
if (row[FieldName] == null || row[FieldName] is DBNull)
NewCell.Content = null;
else
NewCell.Content = System.Convert.ToDouble(row[FieldName]);
break;
case Lfx.Data.InputFieldTypes.Date:
if (row.Fields[FieldName].Value != null)
NewCell.Content = row.Fields[FieldName].ValueDateTime;
break;
case Lfx.Data.InputFieldTypes.DateTime:
NewCell.Content = row[FieldName];
break;
case Lfx.Data.InputFieldTypes.Bool:
if (System.Convert.ToBoolean(row[FieldName]))
NewCell.Content = "Sí";
else
NewCell.Content = "No";
break;
case Lfx.Data.InputFieldTypes.Set:
int SetValue = System.Convert.ToInt32(row[FieldName]);
if (useFields[FieldNum] != null && useFields[FieldNum].SetValues != null & useFields[FieldNum].SetValues.ContainsKey(SetValue))
NewCell.Content = useFields[FieldNum].SetValues[SetValue];
else
NewCell.Content = "???";
break;
default:
NewCell.Content = row[FieldName];
break;
}
}
}
}
return Reng;
}
public Lazaro.Pres.Spreadsheet.Sheet Ejecutar(Dictionary <string, object> valores)
{
string ConsultaSelect = this.ConstruirConsulta(valores);
// Remplazamos los {parametros} por los valores proporcionados por el usuario
if (valores != null)
{
foreach (string ValorNombre in valores.Keys)
{
object ValorValor = valores[ValorNombre];
string ValorFormateado;
if (ValorValor == null)
{
ValorFormateado = "NULL";
}
else if (ValorValor is decimal || ValorValor is double || ValorValor is Single)
{
ValorFormateado = ((decimal)(ValorValor)).ToString("#.00000000");
}
else if (ValorValor is DateTime)
{
ValorFormateado = ((DateTime)(ValorValor)).ToString(Lfx.Types.Formatting.DateTime.SqlDateTimeFormat);
}
else
{
ValorFormateado = ValorValor.ToString();
}
ConsultaSelect.Replace("{" + ValorNombre + "}", ValorFormateado);
}
}
DataTable Resultados = this.Connection.Select(ConsultaSelect);
Lazaro.Pres.Spreadsheet.Sheet Res = new Lazaro.Pres.Spreadsheet.Sheet();
// Creo los encabezados de columna
foreach (DataColumn Columna in Resultados.Columns)
{
Lazaro.Pres.Spreadsheet.ColumnHeader NuevaColumna = new Lazaro.Pres.Spreadsheet.ColumnHeader(Columna.ColumnName);
switch (Columna.DataType.ToString())
{
case "System.Int32":
case "System.Int64":
case "System.Decimal":
case "System.Double":
case "System.Single":
NuevaColumna.TextAlignment = Lfx.Types.StringAlignment.Far;
NuevaColumna.Width = 120;
break;
case "System.DateTime":
NuevaColumna.TextAlignment = Lfx.Types.StringAlignment.Far;
NuevaColumna.Width = 120;
break;
default:
NuevaColumna.TextAlignment = Lfx.Types.StringAlignment.Near;
NuevaColumna.Width = 320;
break;
}
Res.ColumnHeaders.Add(NuevaColumna);
}
// Lleno la planilla con los registros
foreach (DataRow Reg in Resultados.Rows)
{
Lazaro.Pres.Spreadsheet.Row Renglon = new Lazaro.Pres.Spreadsheet.Row();
foreach (DataColumn Columna in Resultados.Columns)
{
switch (Columna.DataType.ToString())
{
case "System.Decimal":
case "System.Double":
case "System.Single":
Renglon.Cells.AddWithValue(System.Convert.ToDecimal(Reg[Columna.ColumnName]));
break;
case "System.DateTime":
Renglon.Cells.AddWithValue(System.Convert.ToDateTime(Reg[Columna.ColumnName]));
break;
default:
Renglon.Cells.AddWithValue(System.Convert.ToString(Reg[Columna.ColumnName]));
break;
}
}
Res.Rows.Add(Renglon);
}
return(Res);
}
private Lfx.Types.OperationResult MostrarNormal(string Filtros)
{
string TextoSql = null;
string ColumnaTotal = "total";
// Filtros = "comprob.id_comprob=comprob_detalle.id_comprob AND comprob_detalle.id_articulo=articulos.id_articulo AND " & Filtros
switch (m_Agrupar) {
case "":
TextoSql = "SELECT comprob.*";
break;
default:
TextoSql = "SELECT comprob.*, DAYOFWEEK(comprob.fecha), DAYOFMONTH(comprob.fecha), MONTH(comprob.fecha), SUM(comprob.total) AS sumtotal";
break;
}
TextoSql += " FROM comprob WHERE " + Filtros;
if (m_Agrupar.Length > 0)
TextoSql += " GROUP BY " + m_Agrupar;
else
TextoSql += " GROUP BY comprob.id_comprob";
TextoSql += " ORDER BY ";
if (m_Agrupar.Length > 0) {
TextoSql += "sumtotal DESC, ";
ColumnaTotal = "sumtotal";
}
TextoSql += "RIGHT(comprob.tipo_fac, 1), comprob.pv, comprob.numero";
System.Data.DataTable Comprobs = this.Connection.Select(TextoSql);
decimal Total = 0, SubTotal = 0;
//double Diferencia = 0;
string UltimoValorAgrupar = "slfadf*af*df*asdf";
ReportSheet = new Lazaro.Pres.Spreadsheet.Sheet("Listado de Comprobantes - Fecha " + m_Fechas.From + " al " + m_Fechas.To);
if (m_Agrupar.Length == 0) {
ReportSheet.ColumnHeaders.Add(new Lazaro.Pres.Spreadsheet.ColumnHeader("Fecha", 100));
ReportSheet.ColumnHeaders.Add(new Lazaro.Pres.Spreadsheet.ColumnHeader("Tipo", 48));
ReportSheet.ColumnHeaders.Add(new Lazaro.Pres.Spreadsheet.ColumnHeader("Número", 120));
ReportSheet.ColumnHeaders.Add(new Lazaro.Pres.Spreadsheet.ColumnHeader("Cliente", 240));
ReportSheet.ColumnHeaders.Add(new Lazaro.Pres.Spreadsheet.ColumnHeader(Lbl.Sys.Config.Pais.ClavePersonasJuridicas.Nombre, 120));
ReportSheet.ColumnHeaders.Add(new Lazaro.Pres.Spreadsheet.ColumnHeader("Importe", 160, Lfx.Types.StringAlignment.Far));
ReportSheet.ColumnHeaders.Add(new Lazaro.Pres.Spreadsheet.ColumnHeader("Cancelado", 160, Lfx.Types.StringAlignment.Far));
} else {
ReportSheet.ColumnHeaders.Add(new Lazaro.Pres.Spreadsheet.ColumnHeader("Item", 480));
ReportSheet.ColumnHeaders.Add(new Lazaro.Pres.Spreadsheet.ColumnHeader("Importe", 160, Lfx.Types.StringAlignment.Far));
ReportSheet.ColumnHeaders.Add(new Lazaro.Pres.Spreadsheet.ColumnHeader("Cancelado", 160, Lfx.Types.StringAlignment.Far));
}
string NombreGrupo = null;
foreach (System.Data.DataRow Comprob in Comprobs.Rows) {
Lazaro.Pres.Spreadsheet.Row Reng = new Lazaro.Pres.Spreadsheet.Row();
if (m_Agrupar.Length > 0 && Comprob[Lfx.Data.Field.GetNameOnly(m_Agrupar)].ToString() != UltimoValorAgrupar) {
UltimoValorAgrupar = Comprob[Lfx.Data.Field.GetNameOnly(m_Agrupar)].ToString();
if (SubTotal > 0) {
Lazaro.Pres.Spreadsheet.Row SubTotal1 = new Lazaro.Pres.Spreadsheet.Row();
SubTotal1.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(NombreGrupo));
SubTotal1.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(Lbl.Sys.Config.Moneda.Simbolo + " " + Lfx.Types.Formatting.FormatCurrency(SubTotal, Lfx.Workspace.Master.CurrentConfig.Moneda.Decimales)));
ReportSheet.Rows.Add(SubTotal1);
SubTotal = 0;
}
switch (m_Agrupar) {
case "comprob.id_vendedor":
case "comprob.id_cliente":
if (UltimoValorAgrupar.Length > 0)
NombreGrupo = this.Connection.FieldString("SELECT nombre_visible FROM personas WHERE id_persona=" + UltimoValorAgrupar);
else
NombreGrupo = "(Sin especificar)";
break;
case "comprob.id_formapago":
if (UltimoValorAgrupar.Length > 0)
NombreGrupo = this.Connection.FieldString("SELECT nombre FROM formaspago WHERE id_formapago=" + UltimoValorAgrupar);
else
NombreGrupo = "(Sin especificar)";
break;
case "DAYOFWEEK(comprob.fecha)":
switch (System.Convert.ToInt32(Comprob[Lfx.Data.Field.GetNameOnly(m_Agrupar)])) {
case 1:
NombreGrupo = "Domingo";
break;
case 2:
NombreGrupo = "Lunes";
break;
case 3:
NombreGrupo = "Martes";
break;
case 4:
NombreGrupo = "Miércoles";
break;
case 5:
NombreGrupo = "Jueves";
break;
case 6:
NombreGrupo = "Viernes";
break;
case 7:
NombreGrupo = "Sábado";
break;
}
break;
case "DAYOFMONTH(comprob.fecha)":
NombreGrupo = System.Convert.ToDateTime(Comprob["fecha"]).ToString("dd-MM-yyyy");
break;
case "MONTH(comprob.fecha)":
NombreGrupo = System.Convert.ToDateTime(Comprob["fecha"]).ToString("MMMM");
break;
default:
NombreGrupo = UltimoValorAgrupar;
break;
}
}
Reng.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(Lfx.Types.Formatting.FormatDate(Comprob["fecha"])));
Reng.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(System.Convert.ToString(Comprob["tipo_fac"]).PadRight(3).Substring(0, 3)));
Reng.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(System.Convert.ToInt32(Comprob["pv"]).ToString("0000") + "-" + System.Convert.ToInt32(Comprob["numero"]).ToString("00000000")));
if (System.Convert.ToInt32(Comprob["anulada"]) != 0) {
Reng.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell("ANULADA"));
Reng.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell((double)0));
// No suma al total
} else {
Lfx.Data.Row Cliente = this.Connection.Row("personas", "id_persona", System.Convert.ToInt32(Comprob["id_cliente"]));
Reng.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(Cliente["nombre_visible"].ToString()));
Reng.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(Cliente["cuit"].ToString()));
switch (System.Convert.ToString(Comprob["tipo_fac"])) {
case "NCA":
case "NCB":
case "NCC":
case "NCE":
case "NCM":
Reng.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(-System.Convert.ToDouble(Comprob[ColumnaTotal])));
Reng.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(System.Convert.ToDouble(Comprob["cancelado"])));
Total -= System.Convert.ToDecimal(Comprob[ColumnaTotal]);
SubTotal -= System.Convert.ToDecimal(Comprob[ColumnaTotal]);
break;
default:
Reng.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(System.Convert.ToDouble(Comprob[ColumnaTotal])));
Reng.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(System.Convert.ToDouble(Comprob["cancelado"])));
Total += System.Convert.ToDecimal(Comprob[ColumnaTotal]);
SubTotal += System.Convert.ToDecimal(Comprob[ColumnaTotal]);
break;
}
}
if (m_Agrupar.Length == 0)
ReportSheet.Rows.Add(Reng);
}
if (m_Agrupar.Length > 0 && SubTotal > 0) {
Lazaro.Pres.Spreadsheet.Row SubTotal2 = new Lazaro.Pres.Spreadsheet.Row();
SubTotal2.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(NombreGrupo));
SubTotal2.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(Lbl.Sys.Config.Moneda.Simbolo + " " + Lfx.Types.Formatting.FormatCurrency(SubTotal, Lfx.Workspace.Master.CurrentConfig.Moneda.Decimales)));
ReportSheet.Rows.Add(SubTotal2);
SubTotal = 0;
}
Lazaro.Pres.Spreadsheet.Row Total1 = new Lazaro.Pres.Spreadsheet.Row();
Total1.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(""));
Total1.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(""));
Total1.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(""));
Total1.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell("Total"));
Total1.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(""));
Total1.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(Lbl.Sys.Config.Moneda.Simbolo + " " + Lfx.Types.Formatting.FormatCurrency(Total, Lfx.Workspace.Master.CurrentConfig.Moneda.Decimales)));
ReportSheet.Rows.Add(Total1);
ReportListView.FromSheet(ReportSheet);
this.Report = new Lazaro.Pres.Spreadsheet.Workbook();
this.Report.Sheets.Add(ReportSheet);
return new Lfx.Types.SuccessOperationResult();
}
private Lfx.Types.OperationResult MostrarPorMarcaOProveedor(string Filtros)
{
string TextoSql = null;
string FiltrosCompletos = null;
FiltrosCompletos = "comprob.id_comprob=comprob_detalle.id_comprob AND comprob_detalle.id_articulo=articulos.id_articulo AND " + Filtros;
TextoSql = "SELECT SUM(comprob_detalle.costo*cantidad) AS totalcosto, COUNT(comprob.id_comprob) AS cantfact, SUM(comprob_detalle.importe*(1-comprob.descuento/100)*(1+comprob.interes/100)) AS total, SUM(comprob_detalle.cantidad) AS cantart, articulos.id_marca, articulos.id_proveedor, articulos.id_articulo, articulos.id_categoria, DAYOFWEEK(comprob.fecha), DAYOFMONTH(comprob.fecha), MONTH(comprob.fecha)";
TextoSql += " FROM comprob, comprob_detalle, articulos WHERE " + FiltrosCompletos;
TextoSql += " GROUP BY " + m_Agrupar;
TextoSql += " ORDER BY total DESC";
System.Data.DataTable Comprobs = this.Connection.Select(TextoSql);
ReportSheet = new Lazaro.Pres.Spreadsheet.Sheet("Listado de Comprobantes - Fecha " + m_Fechas.From + " al " + m_Fechas.To);
ReportSheet.ColumnHeaders.Add(new Lazaro.Pres.Spreadsheet.ColumnHeader("Detalle", 320));
ReportSheet.ColumnHeaders.Add(new Lazaro.Pres.Spreadsheet.ColumnHeader("Artículos", 80, Lfx.Types.StringAlignment.Far));
ReportSheet.ColumnHeaders.Add(new Lazaro.Pres.Spreadsheet.ColumnHeader("Facturas", 80, Lfx.Types.StringAlignment.Far));
ReportSheet.ColumnHeaders.Add(new Lazaro.Pres.Spreadsheet.ColumnHeader("Costo", 120, Lfx.Types.StringAlignment.Far));
ReportSheet.ColumnHeaders.Add(new Lazaro.Pres.Spreadsheet.ColumnHeader("Importe", 120, Lfx.Types.StringAlignment.Far));
ReportSheet.ColumnHeaders.Add(new Lazaro.Pres.Spreadsheet.ColumnHeader("Pendiente", 120, Lfx.Types.StringAlignment.Far));
ReportSheet.ColumnHeaders.Add(new Lazaro.Pres.Spreadsheet.ColumnHeader("Diferencia", 120, Lfx.Types.StringAlignment.Far));
decimal Total = 0, TotalCosto = 0;
decimal TotalNC = 0, TotalNCCosto = 0;
decimal Diferencia = 0;
foreach (System.Data.DataRow Comrob in Comprobs.Rows) {
Lazaro.Pres.Spreadsheet.Row Reng = new Lazaro.Pres.Spreadsheet.Row();
FiltrosCompletos = "comprob.tipo_fac IN('NCA', 'NCB', 'NCC', 'NCE', 'NCM') AND comprob.id_comprob=comprob_detalle.id_comprob AND comprob_detalle.id_articulo=articulos.id_articulo AND " + Filtros;
switch (m_Agrupar) {
case "articulos.id_marca":
FiltrosCompletos += " AND " + m_Agrupar + "=" + Lfx.Data.Connection.ConvertDBNullToZero(Comrob["id_marca"]);
break;
case "articulos.id_proveedor":
FiltrosCompletos += " AND " + m_Agrupar + "=" + Lfx.Data.Connection.ConvertDBNullToZero(Comrob["id_proveedor"]);
break;
case "articulos.id_articulo":
FiltrosCompletos += " AND " + m_Agrupar + "=" + Lfx.Data.Connection.ConvertDBNullToZero(Comrob["id_articulo"]);
break;
case "articulos.id_categoria":
FiltrosCompletos += " AND " + m_Agrupar + "=" + Lfx.Data.Connection.ConvertDBNullToZero(Comrob["id_categoria"]);
break;
}
TotalNC = this.Connection.FieldDecimal("SELECT SUM(comprob_detalle.importe*(1-comprob.descuento/100)*(1+comprob.interes/100)) AS total FROM comprob, comprob_detalle, articulos WHERE " + FiltrosCompletos + " GROUP BY comprob.id_comprob");
TotalNCCosto = this.Connection.FieldDecimal("SELECT SUM(comprob_detalle.costo) AS total FROM comprob, comprob_detalle, articulos WHERE " + FiltrosCompletos + " GROUP BY " + m_Agrupar);
string Detalle = null;
switch (m_Agrupar) {
case "articulos.id_proveedor":
Detalle = this.Connection.FieldString("SELECT nombre_visible FROM personas WHERE id_persona=" + Lfx.Data.Connection.ConvertDBNullToZero(Comrob["id_proveedor"]).ToString());
break;
case "articulos.id_marca":
Detalle = this.Connection.FieldString("SELECT nombre FROM marcas WHERE id_marca=" + Lfx.Data.Connection.ConvertDBNullToZero(Comrob["id_marca"]).ToString());
break;
case "articulos.id_articulo":
Detalle = this.Connection.FieldString("SELECT nombre FROM articulos WHERE id_articulo=" + Lfx.Data.Connection.ConvertDBNullToZero(Comrob["id_articulo"]).ToString());
break;
case "articulos.id_categoria":
Detalle = this.Connection.FieldString("SELECT nombre FROM articulos_categorias WHERE id_categoria=" + Lfx.Data.Connection.ConvertDBNullToZero(Comrob["id_categoria"]).ToString());
break;
}
decimal ComprobTotal = System.Convert.ToDecimal(Comrob["total"]) - TotalNC;
decimal ComprobTotalCosto = System.Convert.ToDecimal(Comrob["totalcosto"]) - TotalNCCosto;
decimal ComprobDiferencia = ComprobTotal - ComprobTotalCosto;
if (Detalle == null || Detalle.Length == 0)
Detalle = "(Sin especificar)";
Reng.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(Detalle));
Reng.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(Lfx.Types.Formatting.FormatNumber(System.Convert.ToDecimal(Comrob["cantart"]), Lbl.Sys.Config.Articulos.Decimales)));
Reng.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(System.Convert.ToInt32(Comrob["cantfact"])));
Reng.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(Lfx.Types.Formatting.FormatCurrency(ComprobTotalCosto, Lfx.Workspace.Master.CurrentConfig.Moneda.Decimales)));
Reng.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(Lfx.Types.Formatting.FormatCurrency(ComprobTotal, Lfx.Workspace.Master.CurrentConfig.Moneda.Decimales)));
Reng.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(Lfx.Types.Formatting.FormatCurrency(ComprobDiferencia, Lfx.Workspace.Master.CurrentConfig.Moneda.Decimales)));
Total += ComprobTotal;
TotalCosto += ComprobTotalCosto;
ReportSheet.Rows.Add(Reng);
Diferencia += ComprobDiferencia;
}
Lazaro.Pres.Spreadsheet.Row RengTotal = new Lazaro.Pres.Spreadsheet.Row();
RengTotal.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell("Total"));
RengTotal.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(" "));
RengTotal.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(" "));
RengTotal.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(TotalCosto));
RengTotal.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(Total));
RengTotal.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(Diferencia));
ReportSheet.Rows.Add(RengTotal);
ReportListView.FromSheet(ReportSheet);
this.Report = new Lazaro.Pres.Spreadsheet.Workbook();
this.Report.Sheets.Add(ReportSheet);
return new Lfx.Types.SuccessOperationResult();
}
protected Lazaro.Pres.Spreadsheet.Row FormatArt(Lazaro.Pres.Spreadsheet.Sheet sheet, Lbl.Comprobantes.DetalleArticulo useField)
{
Lazaro.Pres.Spreadsheet.Row Reng = new Lazaro.Pres.Spreadsheet.Row(sheet);
int id_articulo = useField.Articulo == null ? 0 : useField.Articulo.Id;
Lazaro.Pres.Spreadsheet.Cell NewCell = Reng.Cells.Add();
ExcelReg e = excReg.Find(t => t.name == "id_articulo");
NewCell = GetContent(NewCell, e, id_articulo);
string nombre = useField.Nombre;
NewCell = Reng.Cells.Add();
e = excReg.Find(t => t.name == "nombre");
NewCell = GetContent(NewCell, e, nombre);
string descripcion = useField.Descripcion;
NewCell = Reng.Cells.Add();
e = excReg.Find(t => t.name == "descripcion");
NewCell = GetContent(NewCell, e, descripcion);
decimal cantidad = useField.Cantidad;
NewCell = Reng.Cells.Add();
e = excReg.Find(t => t.name == "cantidad");
NewCell = GetContent(NewCell, e, cantidad);
decimal costo = useField.Costo;
NewCell = Reng.Cells.Add();
e = excReg.Find(t => t.name == "costo");
NewCell = GetContent(NewCell, e, costo);
decimal precio = useField.ImporteUnitario;
NewCell = Reng.Cells.Add();
e = excReg.Find(t => t.name == "precio");
NewCell = GetContent(NewCell, e, precio);
decimal importe = useField.ImporteUnitarioFinal;
NewCell = Reng.Cells.Add();
e = excReg.Find(t => t.name == "importe");
NewCell = GetContent(NewCell, e, importe);
decimal recargo = useField.Recargo;
NewCell = Reng.Cells.Add();
e = excReg.Find(t => t.name == "recargo");
NewCell = GetContent(NewCell, e, recargo);
decimal total = useField.ImporteAImprimir;
NewCell = Reng.Cells.Add();
e = excReg.Find(t => t.name == "total");
NewCell = GetContent(NewCell, e, total);
return(Reng);
}
public virtual Lazaro.Pres.Spreadsheet.Workbook ToWorkbookDetalle(int IDComprobante)
{
Lbl.Comprobantes.Factura selComprobante = new Lbl.Comprobantes.Factura(this.Connection, IDComprobante);
Lazaro.Pres.Spreadsheet.Workbook Res = new Lazaro.Pres.Spreadsheet.Workbook();
Lazaro.Pres.Spreadsheet.Sheet Sheet = new Lazaro.Pres.Spreadsheet.Sheet(this.Text + " Comprobante");
Res.Sheets.Add(Sheet);
int OrderColumn = -1;
if (selComprobante != null)
{
for (int i = 0; i < 9; i++)
{
ExcelReg nexc = new ExcelReg();
switch (i)
{
case 0:
nexc.name = "id_articulo";
nexc.desc = "Código";
nexc.alig = Lfx.Types.StringAlignment.Near;
nexc.width = 100;
nexc.fieldtype = Lfx.Data.InputFieldTypes.Integer;
break;
case 1:
nexc.name = "nombre";
nexc.desc = "Nombre";
nexc.alig = Lfx.Types.StringAlignment.Near;
nexc.width = 300;
nexc.fieldtype = Lfx.Data.InputFieldTypes.Text;
break;
case 2:
nexc.name = "descripcion";
nexc.desc = "Descripción";
nexc.alig = Lfx.Types.StringAlignment.Near;
nexc.width = 150;
nexc.fieldtype = Lfx.Data.InputFieldTypes.Text;
break;
case 3:
nexc.name = "cantidad";
nexc.desc = "Cantidad";
nexc.alig = Lfx.Types.StringAlignment.Near;
nexc.width = 80;
nexc.fieldtype = Lfx.Data.InputFieldTypes.Numeric;
break;
case 4:
nexc.name = "costo";
nexc.desc = "Costo";
nexc.alig = Lfx.Types.StringAlignment.Near;
nexc.width = 90;
nexc.fieldtype = Lfx.Data.InputFieldTypes.Currency;
break;
case 5:
nexc.name = "precio";
nexc.desc = "Precio Uni.";
nexc.alig = Lfx.Types.StringAlignment.Near;
nexc.width = 120;
nexc.fieldtype = Lfx.Data.InputFieldTypes.Currency;
break;
case 6:
nexc.name = "importe";
nexc.desc = "Importe";
nexc.alig = Lfx.Types.StringAlignment.Near;
nexc.width = 120;
nexc.fieldtype = Lfx.Data.InputFieldTypes.Currency;
break;
case 7:
nexc.name = "recargo";
nexc.desc = "Recargo";
nexc.alig = Lfx.Types.StringAlignment.Near;
nexc.width = 100;
nexc.fieldtype = Lfx.Data.InputFieldTypes.Currency;
break;
case 8:
nexc.name = "total";
nexc.desc = "Total";
nexc.alig = Lfx.Types.StringAlignment.Near;
nexc.width = 120;
nexc.fieldtype = Lfx.Data.InputFieldTypes.Currency;
break;
}
Lazaro.Pres.Spreadsheet.ColumnHeader ColHead = new Lazaro.Pres.Spreadsheet.ColumnHeader(nexc.desc, nexc.width);
ColHead.Name = Lazaro.Orm.Data.ColumnValue.GetNameOnly(nexc.name);
ColHead.TextAlignment = nexc.alig; ColHead.DataType = nexc.fieldtype; ColHead.Format = nexc.format; ColHead.Printable = true;
if (i == 8)
{
ColHead.TotalFunction = Lazaro.Pres.Spreadsheet.QuickFunctions.Sum;
}
Sheet.ColumnHeaders.Add(ColHead);
excReg.Add(nexc);
}
}
// Exporto los renglones
Lbl.Comprobantes.ColeccionDetalleArticulos detArt = selComprobante.Articulos;
foreach (Lbl.Comprobantes.DetalleArticulo DtRow in detArt)
{
Lazaro.Pres.Spreadsheet.Row Reng = this.FormatArt(Sheet, DtRow);
Sheet.Rows.Add(Reng);
}
if (OrderColumn >= 0)
{
if (m_GroupingColumnName != null)
{
Sheet.SortByGroupAndColumn(OrderColumn, true);
}
else
{
if (OrderColumn >= 0)
{
Sheet.Sort(OrderColumn, true);
}
}
}
return(Res);
}
public Lazaro.Pres.Spreadsheet.Sheet Ejecutar(Dictionary<string, object> valores)
{
string ConsultaSelect = this.ConstruirConsulta(valores);
// Remplazamos los {parametros} por los valores proporcionados por el usuario
if (valores != null) {
foreach (string ValorNombre in valores.Keys) {
object ValorValor = valores[ValorNombre];
string ValorFormateado;
if (ValorValor == null) {
ValorFormateado = "NULL";
} else if (ValorValor is decimal || ValorValor is double || ValorValor is Single) {
ValorFormateado = ((decimal)(ValorValor)).ToString("#.00000000");
} else if (ValorValor is DateTime) {
ValorFormateado = ((DateTime)(ValorValor)).ToString(Lfx.Types.Formatting.DateTime.SqlDateTimeFormat);
} else {
ValorFormateado = ValorValor.ToString();
}
ConsultaSelect.Replace("{" + ValorNombre + "}", ValorFormateado);
}
}
DataTable Resultados = this.Connection.Select(ConsultaSelect);
Lazaro.Pres.Spreadsheet.Sheet Res = new Lazaro.Pres.Spreadsheet.Sheet();
// Creo los encabezados de columna
foreach(DataColumn Columna in Resultados.Columns) {
Lazaro.Pres.Spreadsheet.ColumnHeader NuevaColumna = new Lazaro.Pres.Spreadsheet.ColumnHeader(Columna.ColumnName);
switch(Columna.DataType.ToString()) {
case "System.Int32":
case "System.Int64":
case "System.Decimal":
case "System.Double":
case "System.Single":
NuevaColumna.TextAlignment = Lfx.Types.StringAlignment.Far;
NuevaColumna.Width = 120;
break;
case "System.DateTime":
NuevaColumna.TextAlignment = Lfx.Types.StringAlignment.Far;
NuevaColumna.Width = 120;
break;
default:
NuevaColumna.TextAlignment = Lfx.Types.StringAlignment.Near;
NuevaColumna.Width = 320;
break;
}
Res.ColumnHeaders.Add(NuevaColumna);
}
// Lleno la planilla con los registros
foreach (DataRow Reg in Resultados.Rows) {
Lazaro.Pres.Spreadsheet.Row Renglon = new Lazaro.Pres.Spreadsheet.Row();
foreach (DataColumn Columna in Resultados.Columns) {
switch (Columna.DataType.ToString()) {
case "System.Decimal":
case "System.Double":
case "System.Single":
Renglon.Cells.AddWithValue(System.Convert.ToDecimal(Reg[Columna.ColumnName]));
break;
case "System.DateTime":
Renglon.Cells.AddWithValue(System.Convert.ToDateTime(Reg[Columna.ColumnName]));
break;
default:
Renglon.Cells.AddWithValue(System.Convert.ToString(Reg[Columna.ColumnName]));
break;
}
}
Res.Rows.Add(Renglon);
}
return Res;
}
private Lfx.Types.OperationResult MostrarNormal(string Filtros)
{
string TextoSql = null;
string ColumnaTotal = "total";
// Filtros = "comprob.id_comprob=comprob_detalle.id_comprob AND comprob_detalle.id_articulo=articulos.id_articulo AND " & Filtros
switch (m_Agrupar)
{
case "":
TextoSql = "SELECT comprob.*";
break;
default:
TextoSql = "SELECT comprob.*, DAYOFWEEK(comprob.fecha), DAYOFMONTH(comprob.fecha), MONTH(comprob.fecha), SUM(comprob.total) AS sumtotal";
break;
}
TextoSql += " FROM comprob WHERE " + Filtros;
if (m_Agrupar.Length > 0)
{
TextoSql += " GROUP BY " + m_Agrupar;
}
else
{
TextoSql += " GROUP BY comprob.id_comprob";
}
TextoSql += " ORDER BY ";
if (m_Agrupar.Length > 0)
{
TextoSql += "sumtotal DESC, ";
ColumnaTotal = "sumtotal";
}
TextoSql += "RIGHT(comprob.tipo_fac, 1), comprob.pv, comprob.numero";
System.Data.DataTable Comprobs = this.Connection.Select(TextoSql);
decimal Total = 0, SubTotal = 0;
//double Diferencia = 0;
string UltimoValorAgrupar = "slfadf*af*df*asdf";
ReportSheet = new Lazaro.Pres.Spreadsheet.Sheet("Listado de Comprobantes - Fecha " + m_Fechas.From + " al " + m_Fechas.To);
if (m_Agrupar.Length == 0)
{
ReportSheet.ColumnHeaders.Add(new Lazaro.Pres.Spreadsheet.ColumnHeader("Fecha", 100));
ReportSheet.ColumnHeaders.Add(new Lazaro.Pres.Spreadsheet.ColumnHeader("Tipo", 48));
ReportSheet.ColumnHeaders.Add(new Lazaro.Pres.Spreadsheet.ColumnHeader("Número", 120));
ReportSheet.ColumnHeaders.Add(new Lazaro.Pres.Spreadsheet.ColumnHeader("Cliente", 240));
ReportSheet.ColumnHeaders.Add(new Lazaro.Pres.Spreadsheet.ColumnHeader(Lbl.Sys.Config.Pais.ClavePersonasJuridicas.Nombre, 120));
ReportSheet.ColumnHeaders.Add(new Lazaro.Pres.Spreadsheet.ColumnHeader("Importe", 160, Lfx.Types.StringAlignment.Far));
ReportSheet.ColumnHeaders.Add(new Lazaro.Pres.Spreadsheet.ColumnHeader("Cancelado", 160, Lfx.Types.StringAlignment.Far));
}
else
{
ReportSheet.ColumnHeaders.Add(new Lazaro.Pres.Spreadsheet.ColumnHeader("Item", 480));
ReportSheet.ColumnHeaders.Add(new Lazaro.Pres.Spreadsheet.ColumnHeader("Importe", 160, Lfx.Types.StringAlignment.Far));
ReportSheet.ColumnHeaders.Add(new Lazaro.Pres.Spreadsheet.ColumnHeader("Cancelado", 160, Lfx.Types.StringAlignment.Far));
}
string NombreGrupo = null;
foreach (System.Data.DataRow Comprob in Comprobs.Rows)
{
Lazaro.Pres.Spreadsheet.Row Reng = new Lazaro.Pres.Spreadsheet.Row();
if (m_Agrupar.Length > 0 && Comprob[Lazaro.Orm.Data.ColumnValue.GetNameOnly(m_Agrupar)].ToString() != UltimoValorAgrupar)
{
UltimoValorAgrupar = Comprob[Lazaro.Orm.Data.ColumnValue.GetNameOnly(m_Agrupar)].ToString();
if (SubTotal > 0)
{
Lazaro.Pres.Spreadsheet.Row SubTotal1 = new Lazaro.Pres.Spreadsheet.Row();
SubTotal1.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(NombreGrupo));
SubTotal1.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(Lbl.Sys.Config.Moneda.Simbolo + " " + Lfx.Types.Formatting.FormatCurrency(SubTotal, Lfx.Workspace.Master.CurrentConfig.Moneda.Decimales)));
ReportSheet.Rows.Add(SubTotal1);
SubTotal = 0;
}
switch (m_Agrupar)
{
case "comprob.id_vendedor":
case "comprob.id_cliente":
if (UltimoValorAgrupar.Length > 0)
{
NombreGrupo = this.Connection.FieldString("SELECT nombre_visible FROM personas WHERE id_persona=" + UltimoValorAgrupar);
}
else
{
NombreGrupo = "(Sin especificar)";
}
break;
case "comprob.id_formapago":
if (UltimoValorAgrupar.Length > 0)
{
NombreGrupo = this.Connection.FieldString("SELECT nombre FROM formaspago WHERE id_formapago=" + UltimoValorAgrupar);
}
else
{
NombreGrupo = "(Sin especificar)";
}
break;
case "DAYOFWEEK(comprob.fecha)":
switch (System.Convert.ToInt32(Comprob[Lazaro.Orm.Data.ColumnValue.GetNameOnly(m_Agrupar)]))
{
case 1:
NombreGrupo = "Domingo";
break;
case 2:
NombreGrupo = "Lunes";
break;
case 3:
NombreGrupo = "Martes";
break;
case 4:
NombreGrupo = "Miércoles";
break;
case 5:
NombreGrupo = "Jueves";
break;
case 6:
NombreGrupo = "Viernes";
break;
case 7:
NombreGrupo = "Sábado";
break;
}
break;
case "DAYOFMONTH(comprob.fecha)":
NombreGrupo = System.Convert.ToDateTime(Comprob["fecha"]).ToString("dd-MM-yyyy");
break;
case "MONTH(comprob.fecha)":
NombreGrupo = System.Convert.ToDateTime(Comprob["fecha"]).ToString("MMMM");
break;
default:
NombreGrupo = UltimoValorAgrupar;
break;
}
}
Reng.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(Lfx.Types.Formatting.FormatDate(Comprob["fecha"])));
Reng.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(System.Convert.ToString(Comprob["tipo_fac"]).PadRight(3).Substring(0, 3)));
Reng.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(System.Convert.ToInt32(Comprob["pv"]).ToString("0000") + "-" + System.Convert.ToInt32(Comprob["numero"]).ToString("00000000")));
if (System.Convert.ToInt32(Comprob["anulada"]) != 0)
{
Reng.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell("ANULADA"));
Reng.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell((double)0));
// No suma al total
}
else
{
Lfx.Data.Row Cliente = this.Connection.Row("personas", "id_persona", System.Convert.ToInt32(Comprob["id_cliente"]));
Reng.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(Cliente["nombre_visible"].ToString()));
Reng.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(Cliente["cuit"].ToString()));
switch (System.Convert.ToString(Comprob["tipo_fac"]))
{
case "NCA":
case "NCB":
case "NCC":
case "NCE":
case "NCM":
Reng.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(-System.Convert.ToDouble(Comprob[ColumnaTotal])));
Reng.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(System.Convert.ToDouble(Comprob["cancelado"])));
Total -= System.Convert.ToDecimal(Comprob[ColumnaTotal]);
SubTotal -= System.Convert.ToDecimal(Comprob[ColumnaTotal]);
break;
default:
Reng.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(System.Convert.ToDouble(Comprob[ColumnaTotal])));
Reng.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(System.Convert.ToDouble(Comprob["cancelado"])));
Total += System.Convert.ToDecimal(Comprob[ColumnaTotal]);
SubTotal += System.Convert.ToDecimal(Comprob[ColumnaTotal]);
break;
}
}
if (m_Agrupar.Length == 0)
{
ReportSheet.Rows.Add(Reng);
}
}
if (m_Agrupar.Length > 0 && SubTotal > 0)
{
Lazaro.Pres.Spreadsheet.Row SubTotal2 = new Lazaro.Pres.Spreadsheet.Row();
SubTotal2.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(NombreGrupo));
SubTotal2.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(Lbl.Sys.Config.Moneda.Simbolo + " " + Lfx.Types.Formatting.FormatCurrency(SubTotal, Lfx.Workspace.Master.CurrentConfig.Moneda.Decimales)));
ReportSheet.Rows.Add(SubTotal2);
SubTotal = 0;
}
Lazaro.Pres.Spreadsheet.Row Total1 = new Lazaro.Pres.Spreadsheet.Row();
Total1.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(""));
Total1.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(""));
Total1.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(""));
Total1.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell("Total"));
Total1.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(""));
Total1.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(Lbl.Sys.Config.Moneda.Simbolo + " " + Lfx.Types.Formatting.FormatCurrency(Total, Lfx.Workspace.Master.CurrentConfig.Moneda.Decimales)));
ReportSheet.Rows.Add(Total1);
ReportListView.FromSheet(ReportSheet);
this.Report = new Lazaro.Pres.Spreadsheet.Workbook();
this.Report.Sheets.Add(ReportSheet);
return(new Lfx.Types.SuccessOperationResult());
}
private Lfx.Types.OperationResult MostrarPorMarcaOProveedor(string Filtros)
{
string TextoSql = null;
string FiltrosCompletos = null;
FiltrosCompletos = "comprob.id_comprob=comprob_detalle.id_comprob AND comprob_detalle.id_articulo=articulos.id_articulo AND " + Filtros;
TextoSql = "SELECT SUM(comprob_detalle.costo*cantidad) AS totalcosto, COUNT(comprob.id_comprob) AS cantfact, SUM(comprob_detalle.importe*(1-comprob.descuento/100)*(1+comprob.interes/100)) AS total, SUM(comprob_detalle.cantidad) AS cantart, articulos.id_marca, articulos.id_proveedor, articulos.id_articulo, articulos.id_categoria, DAYOFWEEK(comprob.fecha), DAYOFMONTH(comprob.fecha), MONTH(comprob.fecha)";
TextoSql += " FROM comprob, comprob_detalle, articulos WHERE " + FiltrosCompletos;
TextoSql += " GROUP BY " + m_Agrupar;
TextoSql += " ORDER BY total DESC";
System.Data.DataTable Comprobs = this.Connection.Select(TextoSql);
ReportSheet = new Lazaro.Pres.Spreadsheet.Sheet("Listado de Comprobantes - Fecha " + m_Fechas.From + " al " + m_Fechas.To);
ReportSheet.ColumnHeaders.Add(new Lazaro.Pres.Spreadsheet.ColumnHeader("Detalle", 320));
ReportSheet.ColumnHeaders.Add(new Lazaro.Pres.Spreadsheet.ColumnHeader("Artículos", 80, Lfx.Types.StringAlignment.Far));
ReportSheet.ColumnHeaders.Add(new Lazaro.Pres.Spreadsheet.ColumnHeader("Facturas", 80, Lfx.Types.StringAlignment.Far));
ReportSheet.ColumnHeaders.Add(new Lazaro.Pres.Spreadsheet.ColumnHeader("Costo", 120, Lfx.Types.StringAlignment.Far));
ReportSheet.ColumnHeaders.Add(new Lazaro.Pres.Spreadsheet.ColumnHeader("Importe", 120, Lfx.Types.StringAlignment.Far));
ReportSheet.ColumnHeaders.Add(new Lazaro.Pres.Spreadsheet.ColumnHeader("Pendiente", 120, Lfx.Types.StringAlignment.Far));
ReportSheet.ColumnHeaders.Add(new Lazaro.Pres.Spreadsheet.ColumnHeader("Diferencia", 120, Lfx.Types.StringAlignment.Far));
decimal Total = 0, TotalCosto = 0;
decimal TotalNC = 0, TotalNCCosto = 0;
decimal Diferencia = 0;
foreach (System.Data.DataRow Comrob in Comprobs.Rows)
{
Lazaro.Pres.Spreadsheet.Row Reng = new Lazaro.Pres.Spreadsheet.Row();
FiltrosCompletos = "comprob.tipo_fac IN('NCA', 'NCB', 'NCC', 'NCE', 'NCM') AND comprob.id_comprob=comprob_detalle.id_comprob AND comprob_detalle.id_articulo=articulos.id_articulo AND " + Filtros;
switch (m_Agrupar)
{
case "articulos.id_marca":
FiltrosCompletos += " AND " + m_Agrupar + "=" + Lfx.Data.Connection.ConvertDBNullToZero(Comrob["id_marca"]);
break;
case "articulos.id_proveedor":
FiltrosCompletos += " AND " + m_Agrupar + "=" + Lfx.Data.Connection.ConvertDBNullToZero(Comrob["id_proveedor"]);
break;
case "articulos.id_articulo":
FiltrosCompletos += " AND " + m_Agrupar + "=" + Lfx.Data.Connection.ConvertDBNullToZero(Comrob["id_articulo"]);
break;
case "articulos.id_categoria":
FiltrosCompletos += " AND " + m_Agrupar + "=" + Lfx.Data.Connection.ConvertDBNullToZero(Comrob["id_categoria"]);
break;
}
TotalNC = this.Connection.FieldDecimal("SELECT SUM(comprob_detalle.importe*(1-comprob.descuento/100)*(1+comprob.interes/100)) AS total FROM comprob, comprob_detalle, articulos WHERE " + FiltrosCompletos + " GROUP BY comprob.id_comprob");
TotalNCCosto = this.Connection.FieldDecimal("SELECT SUM(comprob_detalle.costo) AS total FROM comprob, comprob_detalle, articulos WHERE " + FiltrosCompletos + " GROUP BY " + m_Agrupar);
string Detalle = null;
switch (m_Agrupar)
{
case "articulos.id_proveedor":
Detalle = this.Connection.FieldString("SELECT nombre_visible FROM personas WHERE id_persona=" + Lfx.Data.Connection.ConvertDBNullToZero(Comrob["id_proveedor"]).ToString());
break;
case "articulos.id_marca":
Detalle = this.Connection.FieldString("SELECT nombre FROM marcas WHERE id_marca=" + Lfx.Data.Connection.ConvertDBNullToZero(Comrob["id_marca"]).ToString());
break;
case "articulos.id_articulo":
Detalle = this.Connection.FieldString("SELECT nombre FROM articulos WHERE id_articulo=" + Lfx.Data.Connection.ConvertDBNullToZero(Comrob["id_articulo"]).ToString());
break;
case "articulos.id_categoria":
Detalle = this.Connection.FieldString("SELECT nombre FROM articulos_categorias WHERE id_categoria=" + Lfx.Data.Connection.ConvertDBNullToZero(Comrob["id_categoria"]).ToString());
break;
}
decimal ComprobTotal = System.Convert.ToDecimal(Comrob["total"]) - TotalNC;
decimal ComprobTotalCosto = System.Convert.ToDecimal(Comrob["totalcosto"]) - TotalNCCosto;
decimal ComprobDiferencia = ComprobTotal - ComprobTotalCosto;
if (Detalle == null || Detalle.Length == 0)
{
Detalle = "(Sin especificar)";
}
Reng.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(Detalle));
Reng.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(Lfx.Types.Formatting.FormatNumber(System.Convert.ToDecimal(Comrob["cantart"]), Lbl.Sys.Config.Articulos.Decimales)));
Reng.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(System.Convert.ToInt32(Comrob["cantfact"])));
Reng.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(Lfx.Types.Formatting.FormatCurrency(ComprobTotalCosto, Lfx.Workspace.Master.CurrentConfig.Moneda.Decimales)));
Reng.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(Lfx.Types.Formatting.FormatCurrency(ComprobTotal, Lfx.Workspace.Master.CurrentConfig.Moneda.Decimales)));
Reng.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(Lfx.Types.Formatting.FormatCurrency(ComprobDiferencia, Lfx.Workspace.Master.CurrentConfig.Moneda.Decimales)));
Total += ComprobTotal;
TotalCosto += ComprobTotalCosto;
ReportSheet.Rows.Add(Reng);
Diferencia += ComprobDiferencia;
}
Lazaro.Pres.Spreadsheet.Row RengTotal = new Lazaro.Pres.Spreadsheet.Row();
RengTotal.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell("Total"));
RengTotal.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(" "));
RengTotal.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(" "));
RengTotal.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(TotalCosto));
RengTotal.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(Total));
RengTotal.Cells.Add(new Lazaro.Pres.Spreadsheet.Cell(Diferencia));
ReportSheet.Rows.Add(RengTotal);
ReportListView.FromSheet(ReportSheet);
this.Report = new Lazaro.Pres.Spreadsheet.Workbook();
this.Report.Sheets.Add(ReportSheet);
return(new Lfx.Types.SuccessOperationResult());
}
protected override Lazaro.Pres.Spreadsheet.Row FormatRow(int itemId, Lfx.Data.Row row, Lazaro.Pres.Spreadsheet.Sheet sheet, Lazaro.Pres.FieldCollection useFields)
{
Lazaro.Pres.Spreadsheet.Row Reng = new Lazaro.Pres.Spreadsheet.Row(sheet);
if (this.Definicion.KeyColumn != null && this.Definicion.KeyColumn.Printable)
{
Lazaro.Pres.Spreadsheet.Cell KeyCell = Reng.Cells.Add();
KeyCell.Content = itemId;
}
for (int FieldNum = 0; FieldNum < useFields.Count; FieldNum++)
{
if (useFields[FieldNum].Printable)
{
string FieldName = Lazaro.Orm.Data.ColumnValue.GetNameOnly(useFields[FieldNum].Name);
if (FieldNum >= 0)
{
Lazaro.Pres.Spreadsheet.Cell NewCell = Reng.Cells.Add();
switch (useFields[FieldNum].DataType)
{
case Lfx.Data.InputFieldTypes.Integer:
case Lfx.Data.InputFieldTypes.Serial:
if (row[FieldName] == null || row[FieldName] is DBNull)
{
NewCell.Content = null;
}
else if (useFields[FieldNum].Format != null)
{
NewCell.Content = System.Convert.ToInt32(row[FieldName]).ToString(useFields[FieldNum].Format);
}
else
{
NewCell.Content = row[FieldName].ToString();
}
break;
case Lfx.Data.InputFieldTypes.Relation:
case Lfx.Data.InputFieldTypes.Text:
case Lfx.Data.InputFieldTypes.Memo:
if (row[FieldName] == null)
{
NewCell.Content = null;
}
else if (FieldName == "comprob.id_formapago")
{
Lfx.Data.Row formPago = Lfx.Workspace.Master.Tables["formaspago"].FastRows[System.Convert.ToInt32(row[FieldName])];
if (formPago != null)
{
NewCell.Content = formPago.Fields["nombre"].Value.ToString();
}
else
{
NewCell.Content = row.Fields[FieldName].Value.ToString();
}
}
else if (row[FieldName] is System.Byte[])
{
NewCell.Content = System.Text.Encoding.Default.GetString(((System.Byte[])(row[FieldName])));
}
else
{
NewCell.Content = row.Fields[FieldName].Value.ToString();
}
break;
case Lfx.Data.InputFieldTypes.Currency:
double ValorCur = (row[FieldName] == null || row[FieldName] is DBNull) ? 0 : System.Convert.ToDouble(row[FieldName]);
NewCell.Content = ValorCur;
if (FieldName == "total")
{
totalRenglon += ValorCur;
}
break;
case Lfx.Data.InputFieldTypes.Numeric:
if (row[FieldName] == null || row[FieldName] is DBNull)
{
NewCell.Content = null;
}
else
{
NewCell.Content = System.Convert.ToDouble(row[FieldName]);
}
break;
case Lfx.Data.InputFieldTypes.Date:
if (row.Fields[FieldName].Value != null)
{
NewCell.Content = row.Fields[FieldName].ValueDateTime;
}
break;
case Lfx.Data.InputFieldTypes.DateTime:
NewCell.Content = row[FieldName];
break;
case Lfx.Data.InputFieldTypes.Bool:
if (System.Convert.ToBoolean(row[FieldName]))
{
NewCell.Content = "Sí";
}
else
{
NewCell.Content = "No";
}
break;
case Lfx.Data.InputFieldTypes.Set:
int SetValue = System.Convert.ToInt32(row[FieldName]);
if (useFields[FieldNum] != null && useFields[FieldNum].SetValues != null & useFields[FieldNum].SetValues.ContainsKey(SetValue))
{
NewCell.Content = useFields[FieldNum].SetValues[SetValue];
}
else
{
NewCell.Content = "???";
}
break;
default:
NewCell.Content = row[FieldName];
break;
}
}
}
}
return(Reng);
}
public override Lazaro.Pres.Spreadsheet.Workbook ToWorkbook(Lazaro.Pres.FieldCollection useFields)
{
Lazaro.Pres.Spreadsheet.Workbook Res = new Lazaro.Pres.Spreadsheet.Workbook();
Lazaro.Pres.Spreadsheet.Sheet Sheet = new Lazaro.Pres.Spreadsheet.Sheet(this.Text);
Res.Sheets.Add(Sheet);
// Exporto los encabezados de columna
if (this.Definicion.KeyColumn.Printable)
{
Sheet.ColumnHeaders.Add(new Lazaro.Pres.Spreadsheet.ColumnHeader(this.Definicion.KeyColumn.Label, this.Definicion.KeyColumn.Width));
Sheet.ColumnHeaders[0].DataType = this.Definicion.KeyColumn.DataType;
Sheet.ColumnHeaders[0].Format = this.Definicion.KeyColumn.Format;
Sheet.ColumnHeaders[0].Printable = this.Definicion.KeyColumn.Printable;
}
int OrderColumn = -1;
if (useFields != null)
{
for (int i = 0; i <= useFields.Count - 1; i++)
{
if (useFields[i].Printable)
{
Lazaro.Pres.Spreadsheet.ColumnHeader ColHead = new Lazaro.Pres.Spreadsheet.ColumnHeader(useFields[i].Label, useFields[i].Width);
ColHead.Name = Lazaro.Orm.Data.ColumnValue.GetNameOnly(useFields[i].Name);
ColHead.TextAlignment = useFields[i].Alignment;
ColHead.DataType = useFields[i].DataType;
ColHead.Format = useFields[i].Format;
ColHead.TotalFunction = useFields[i].TotalFunction;
ColHead.Printable = useFields[i].Printable;
Sheet.ColumnHeaders.Add(ColHead);
if (ColHead.Name == this.Definicion.OrderBy)
{
OrderColumn = Sheet.ColumnHeaders.Count - 1;
}
if (ColHead.Name == this.GroupingColumnName)
{
Sheet.ColumnHeaders.GroupingColumn = Sheet.ColumnHeaders.Count - 1;
}
}
}
}
// Exporto los renglones
System.Data.DataTable Tabla = this.Connection.Select(this.SelectCommand());
int lastItemId = -1;
foreach (System.Data.DataRow DtRow in Tabla.Rows)
{
Lfx.Data.Row Registro = (Lfx.Data.Row)DtRow;
string NombreCampoId = Lazaro.Orm.Data.ColumnValue.GetNameOnly(this.Definicion.KeyColumn.Name);
int ItemId = Registro.Fields[NombreCampoId].ValueInt;
lastItemId = ItemId;
Lazaro.Pres.Spreadsheet.Row Reng = this.FormatRow(ItemId, Registro, Sheet, useFields);
Sheet.Rows.Add(Reng);
}
Lazaro.Pres.Spreadsheet.Row RengSum = this.FormatRowSum(Sheet, useFields);
Sheet.Rows.Add(RengSum);
if (OrderColumn >= 0)
{
if (m_GroupingColumnName != null)
{
Sheet.SortByGroupAndColumn(OrderColumn, true);
}
else
{
if (OrderColumn >= 0)
{
Sheet.Sort(OrderColumn, true);
}
}
}
//Sheet.Workbook.Sheets.Find("").Rows.tot
return(Res);
}
