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); }
private void BotonActualizar_Click(object sender, EventArgs e) { this.ReporteAMostrar.ExpandGroups = EntradaExpandirGrupos.TextKey == "1"; Lazaro.Pres.Spreadsheet.Sheet ReportSheet = this.ReporteAMostrar.ToWorkbookSheet(); ReportSheet.Sort(2, false); ListViewReporte.FromSheet(ReportSheet); }
public static Lui.Forms.ListView NewListViewFromSheet(Lazaro.Pres.Spreadsheet.Sheet sheet) { Lui.Forms.ListView Result = new Lui.Forms.ListView(); Result.HeaderStyle = System.Windows.Forms.ColumnHeaderStyle.Nonclickable; Result.LabelEdit = false; Result.LabelWrap = false; Result.FromSheet(sheet); return(Result); }
public ImpresorListado(Lazaro.Pres.Spreadsheet.Sheet sheet, IDbTransaction transaction) : base(transaction) { this.Sheet = sheet; // Calculo el ancho de todas las columnas HeaderTotalWidth = 0; for (int i = 0; i < Sheet.ColumnHeaders.Count; i++) { if (i != this.Sheet.ColumnHeaders.DetailColumn && this.Sheet.ColumnHeaders[i].Printable) HeaderTotalWidth += Sheet.ColumnHeaders[i].Width; } this.DefaultPageSettings.Landscape = HeaderTotalWidth > 1600; this.DefaultPageSettings.Margins = new System.Drawing.Printing.Margins((int)(10 * mm), (int)(10 * mm), (int)(10 * mm), (int)(10 * mm)); }
public ImpresorListado(Lazaro.Pres.Spreadsheet.Sheet sheet, IDbTransaction transaction) : base(transaction) { this.Sheet = sheet; // Calculo el ancho de todas las columnas HeaderTotalWidth = 0; for (int i = 0; i < Sheet.ColumnHeaders.Count; i++) { if (i != this.Sheet.ColumnHeaders.DetailColumn && this.Sheet.ColumnHeaders[i].Printable) { HeaderTotalWidth += Sheet.ColumnHeaders[i].Width; } } this.DefaultPageSettings.Landscape = HeaderTotalWidth > 1600; this.DefaultPageSettings.Margins = new System.Drawing.Printing.Margins((int)(10 * mm), (int)(10 * mm), (int)(10 * mm), (int)(10 * mm)); }
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); }
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 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); }
public void FromSheet(Lazaro.Pres.Spreadsheet.Sheet sheet) { this.SuspendLayout(); this.BeginUpdate(); this.Items.Clear(); this.View = System.Windows.Forms.View.Details; this.FullRowSelect = true; this.GridLines = true; this.Groups.Clear(); if (sheet.BackColor != System.Drawing.Color.Empty) { this.BackColor = sheet.BackColor; } if (sheet.ForeColor != System.Drawing.Color.Empty) { this.ForeColor = sheet.ForeColor; } if (sheet.ColumnHeaders != null) { this.Columns.Clear(); foreach (Lazaro.Pres.Spreadsheet.ColumnHeader ch in sheet.ColumnHeaders) { System.Windows.Forms.ColumnHeader Col = this.Columns.Add(ch.Text, ch.Width); switch (ch.TextAlignment) { case Lfx.Types.StringAlignment.Near: Col.TextAlign = System.Windows.Forms.HorizontalAlignment.Left; break; case Lfx.Types.StringAlignment.Far: Col.TextAlign = System.Windows.Forms.HorizontalAlignment.Right; break; } } } System.Windows.Forms.ListViewGroup LastGroup = null; foreach (Lazaro.Pres.Spreadsheet.Row rw in sheet.Rows) { if (rw is Lazaro.Pres.Spreadsheet.HeaderRow) { LastGroup = new System.Windows.Forms.ListViewGroup(rw.Cells[0].Content.ToString()); this.Groups.Add(LastGroup); } else { System.Windows.Forms.ListViewItem Itm = new System.Windows.Forms.ListViewItem(); if (rw is Lazaro.Pres.Spreadsheet.AggregationRow) { //Itm.BackColor = System.Drawing.Color.LightGray; Itm.Font = new System.Drawing.Font(Itm.Font, System.Drawing.FontStyle.Bold); } if (LastGroup != null) { Itm.Group = LastGroup; } if (rw.BackColor != System.Drawing.Color.Empty) { Itm.BackColor = rw.BackColor; } if (rw.ForeColor != System.Drawing.Color.Empty) { Itm.ForeColor = rw.ForeColor; } int i = 0; foreach (Lazaro.Pres.Spreadsheet.Cell cl in rw.Cells) { string CellString = ""; if (cl.Content != null) { switch (cl.Content.GetType().ToString()) { case "System.Single": case "System.Double": case "System.Decimal": CellString += Lfx.Types.Formatting.FormatNumber(System.Convert.ToDecimal(cl.Content), 4); break; case "System.Integer": case "System.Int16": case "System.Int32": case "System.Int64": CellString += cl.Content.ToString(); break; case "System.DateTime": DateTime clContent = (DateTime)cl.Content; if (clContent.Hour == 0 && clContent.Minute == 0 && clContent.Second == 0) { CellString += clContent.ToString(Lfx.Types.Formatting.DateTime.ShortDatePattern); } else { CellString += clContent.ToString(Lfx.Types.Formatting.DateTime.FullDateTimePattern); } break; case "System.String": CellString += cl.Content.ToString(); break; } } if (i == 0) { Itm.Text = CellString; } else { Itm.SubItems.Add(CellString); } i++; } this.Items.Add(Itm); } } this.EndUpdate(); this.ResumeLayout(); }
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 virtual 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 = Lfx.Data.Field.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()); foreach (System.Data.DataRow DtRow in Tabla.Rows) { Lfx.Data.Row Registro = (Lfx.Data.Row)DtRow; string NombreCampoId = Lfx.Data.Field.GetNameOnly(this.Definicion.KeyColumn.Name); int ItemId = Registro.Fields[NombreCampoId].ValueInt; Lazaro.Pres.Spreadsheet.Row Reng = this.FormatRow(ItemId, Registro, Sheet, useFields); 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; }