private Lbl.Charts.Element[] FacturacionMensual(int anio, int mes, int sucursal) { Lbl.Charts.Element[] Elements = new Lbl.Charts.Element[31]; for (int dia = 1; dia <= 31; dia++) { if (mes == System.DateTime.Now.Month && anio == System.DateTime.Now.Year && dia == System.DateTime.Now.Day) { Elements[dia - 1] = null; } else { int anio1 = anio, anio2 = anio; if (anio == 0) { anio1 = 1900; anio2 = 2099; } int mes1 = mes, mes2 = mes; if (mes == 0) { mes1 = 1900; mes2 = 2099; } string Fecha1Sql = anio1.ToString("0000") + "-" + mes1.ToString("00") + "-" + dia.ToString("00") + " 00:00:00"; string Fecha2Sql = anio2.ToString("0000") + "-" + mes2.ToString("00") + "-" + dia.ToString("00") + " 23:59:59"; decimal ValoresSuma = 0, ValoresResta = 0; string WhereSuc = ""; if (sucursal > 0) { WhereSuc = " id_sucursal=" + sucursal.ToString() + " AND "; } //Tomo la facturación ValoresSuma = this.Connection.FieldDecimal("SELECT SUM(total) FROM comprob WHERE " + WhereSuc + " tipo_fac IN ('FA', 'FB', 'FC', 'FE', 'FM', 'NDA', 'NDB', 'NDC', 'NDE', 'NDM') AND impresa>0 AND compra=0 AND anulada=0 AND fecha BETWEEN '" + Fecha1Sql + "' AND '" + Fecha2Sql + "'"); ValoresResta = this.Connection.FieldDecimal("SELECT SUM(total) FROM comprob WHERE " + WhereSuc + " tipo_fac IN ('NCA', 'NCB', 'NCC', 'NCE', 'NCM') AND impresa>0 AND compra=0 AND anulada=0 AND fecha BETWEEN '" + Fecha1Sql + "' AND '" + Fecha2Sql + "'"); if (ValoresSuma != 0) { Elements[dia - 1] = new Lbl.Charts.Element(); Elements[dia - 1].Value = ValoresSuma - ValoresResta; } else { Elements[dia - 1] = null; } } } return(Elements); }
private Lbl.Charts.Element[] FacturacionAnual(int anio, bool costo, int sucursal) { Lbl.Charts.Element[] Elements = new Lbl.Charts.Element[12]; for (int mes = 1; mes <= 12; mes++) { if (mes == System.DateTime.Now.Month && anio == System.DateTime.Now.Year) { Elements[mes - 1] = null; } else { string Fecha1Sql = anio.ToString("0000") + "-" + mes.ToString("00") + "-01"; string Fecha2Sql = anio.ToString("0000") + "-" + mes.ToString("00") + "-31"; decimal ValoresSuma = 0, ValoresResta = 0; string WhereSuc = ""; if (sucursal > 0) { WhereSuc = " id_sucursal=" + sucursal.ToString() + " AND "; } if (costo == false) { //Tomo la facturación ValoresSuma = this.Connection.FieldDecimal("SELECT SUM(total) FROM comprob WHERE " + WhereSuc + " tipo_fac IN ('FA', 'FB', 'FC', 'FE', 'FM', 'NDA', 'NDB', 'NDC', 'NDE', 'NDM') AND impresa>0 AND compra=0 AND anulada=0 AND fecha BETWEEN '" + Fecha1Sql + "' AND '" + Fecha2Sql + "'"); ValoresResta = this.Connection.FieldDecimal("SELECT SUM(total) FROM comprob WHERE " + WhereSuc + " tipo_fac IN ('NCA', 'NCB', 'NCC', 'NCE', 'NCM') AND impresa>0 AND compra=0 AND anulada=0 AND fecha BETWEEN '" + Fecha1Sql + "' AND '" + Fecha2Sql + "'"); } else { //Tomo el costo de la facturación ValoresSuma = this.Connection.FieldDecimal("SELECT SUM(costo*cantidad) FROM comprob, comprob_detalle WHERE " + WhereSuc + " comprob.id_comprob=comprob_detalle.id_comprob AND comprob.tipo_fac IN ('FA', 'FB', 'FC', 'FE', 'FM', 'NDA', 'NDB', 'NDC', 'NDE', 'NDM') AND comprob.impresa>0 AND comprob.compra=0 AND comprob.anulada=0 AND comprob_detalle.precio>0 AND comprob.fecha BETWEEN '" + Fecha1Sql + "' AND '" + Fecha2Sql + "'"); ValoresResta = this.Connection.FieldDecimal("SELECT SUM(costo*cantidad) FROM comprob, comprob_detalle WHERE " + WhereSuc + " comprob.id_comprob=comprob_detalle.id_comprob AND comprob.tipo_fac IN ('NCA', 'NCB', 'NCC', 'NCE', 'NCM') AND comprob.impresa>0 AND comprob.compra=0 AND comprob.anulada=0 AND comprob.fecha BETWEEN '" + Fecha1Sql + "' AND '" + Fecha2Sql + "'"); } if (ValoresSuma != 0) { Elements[mes - 1] = new Lbl.Charts.Element(); Elements[mes - 1].Value = ValoresSuma - ValoresResta; } else { Elements[mes - 1] = null; } } } return(Elements); }
private Lbl.Charts.Element[] FacturacionMensual(int anio, int mes, int sucursal) { Lbl.Charts.Element[] Elements = new Lbl.Charts.Element[31]; for (int dia = 1; dia <= 31; dia++) { if (mes == System.DateTime.Now.Month && anio == System.DateTime.Now.Year && dia == System.DateTime.Now.Day) { Elements[dia - 1] = null; } else { int anio1 = anio, anio2 = anio; if (anio == 0) { anio1 = 1900; anio2 = 2099; } int mes1 = mes, mes2 = mes; if (mes == 0) { mes1 = 1900; mes2 = 2099; } string Fecha1Sql = anio1.ToString("0000") + "-" + mes1.ToString("00") + "-" + dia.ToString("00") + " 00:00:00"; string Fecha2Sql = anio2.ToString("0000") + "-" + mes2.ToString("00") + "-" + dia.ToString("00") + " 23:59:59"; decimal ValoresSuma = 0, ValoresResta = 0; string WhereSuc = ""; if (sucursal > 0) WhereSuc = " id_sucursal=" + sucursal.ToString() + " AND "; //Tomo la facturación ValoresSuma = this.Connection.FieldDecimal("SELECT SUM(total) FROM comprob WHERE " + WhereSuc + " tipo_fac IN ('FA', 'FB', 'FC', 'FE', 'FM', 'NDA', 'NDB', 'NDC', 'NDE', 'NDM') AND impresa>0 AND compra=0 AND anulada=0 AND fecha BETWEEN '" + Fecha1Sql + "' AND '" + Fecha2Sql + "'"); ValoresResta = this.Connection.FieldDecimal("SELECT SUM(total) FROM comprob WHERE " + WhereSuc + " tipo_fac IN ('NCA', 'NCB', 'NCC', 'NCE', 'NCM') AND impresa>0 AND compra=0 AND anulada=0 AND fecha BETWEEN '" + Fecha1Sql + "' AND '" + Fecha2Sql + "'"); if (ValoresSuma != 0) { Elements[dia - 1] = new Lbl.Charts.Element(); Elements[dia - 1].Value = ValoresSuma - ValoresResta; } else { Elements[dia - 1] = null; } } } return Elements; }
private Lbl.Charts.Element[] FacturacionAnual(int anio, bool costo, int sucursal) { Lbl.Charts.Element[] Elements = new Lbl.Charts.Element[12]; for (int mes = 1; mes <= 12; mes++) { if (mes == System.DateTime.Now.Month && anio == System.DateTime.Now.Year) { Elements[mes - 1] = null; } else { string Fecha1Sql = anio.ToString("0000") + "-" + mes.ToString("00") + "-01"; string Fecha2Sql = anio.ToString("0000") + "-" + mes.ToString("00") + "-31"; decimal ValoresSuma = 0, ValoresResta = 0; string WhereSuc = ""; if (sucursal > 0) WhereSuc = " id_sucursal=" + sucursal.ToString() + " AND "; if (costo == false) { //Tomo la facturación ValoresSuma = this.Connection.FieldDecimal("SELECT SUM(total) FROM comprob WHERE " + WhereSuc + " tipo_fac IN ('FA', 'FB', 'FC', 'FE', 'FM', 'NDA', 'NDB', 'NDC', 'NDE', 'NDM') AND impresa>0 AND compra=0 AND anulada=0 AND fecha BETWEEN '" + Fecha1Sql + "' AND '" + Fecha2Sql + "'"); ValoresResta = this.Connection.FieldDecimal("SELECT SUM(total) FROM comprob WHERE " + WhereSuc + " tipo_fac IN ('NCA', 'NCB', 'NCC', 'NCE', 'NCM') AND impresa>0 AND compra=0 AND anulada=0 AND fecha BETWEEN '" + Fecha1Sql + "' AND '" + Fecha2Sql + "'"); } else { //Tomo el costo de la facturación ValoresSuma = this.Connection.FieldDecimal("SELECT SUM(costo*cantidad) FROM comprob, comprob_detalle WHERE " + WhereSuc + " comprob.id_comprob=comprob_detalle.id_comprob AND comprob.tipo_fac IN ('FA', 'FB', 'FC', 'FE', 'FM', 'NDA', 'NDB', 'NDC', 'NDE', 'NDM') AND comprob.impresa>0 AND comprob.compra=0 AND comprob.anulada=0 AND comprob_detalle.precio>0 AND comprob.fecha BETWEEN '" + Fecha1Sql + "' AND '" + Fecha2Sql + "'"); ValoresResta = this.Connection.FieldDecimal("SELECT SUM(costo*cantidad) FROM comprob, comprob_detalle WHERE " + WhereSuc + " comprob.id_comprob=comprob_detalle.id_comprob AND comprob.tipo_fac IN ('NCA', 'NCB', 'NCC', 'NCE', 'NCM') AND comprob.impresa>0 AND comprob.compra=0 AND comprob.anulada=0 AND comprob.fecha BETWEEN '" + Fecha1Sql + "' AND '" + Fecha2Sql + "'"); } if (ValoresSuma != 0) { Elements[mes - 1] = new Lbl.Charts.Element(); Elements[mes - 1].Value = ValoresSuma - ValoresResta; } else { Elements[mes - 1] = null; } } } return Elements; }
public void RentabilidadAnual(int anio, Lui.Forms.Chart Chrt) { if (Chrt.Series == null) Chrt.Series = new System.Collections.Generic.List<Lbl.Charts.Serie>(); Lbl.Charts.Element[] ElFacturacion = new Lbl.Charts.Element[12]; Lbl.Charts.Element[] ElCosto = new Lbl.Charts.Element[12]; Lbl.Charts.Element[] ElGastos = new Lbl.Charts.Element[12]; Lbl.Charts.Element[] ElRentabilidad = new Lbl.Charts.Element[12]; for (int mes = 1; mes <= 12; mes++) { if (anio > DateTime.Now.Year || (anio == DateTime.Now.Year && mes > DateTime.Now.Month)) { //Nada ? } else { ElFacturacion[mes - 1] = new Lbl.Charts.Element(); ElCosto[mes - 1] = new Lbl.Charts.Element(); ElGastos[mes - 1] = new Lbl.Charts.Element(); ElRentabilidad[mes - 1] = new Lbl.Charts.Element(); string Fecha1Sql = anio.ToString("0000") + "-" + mes.ToString("00") + "-01"; string Fecha2Sql = anio.ToString("0000") + "-" + mes.ToString("00") + "-31"; decimal Facturas = this.Connection.FieldDecimal("SELECT SUM(total) FROM comprob WHERE tipo_fac IN ('FA', 'FB', 'FC', 'FE', 'FM', 'NDA', 'NDB', 'NDC', 'NDE', 'NDM') AND impresa>0 AND compra=0 AND anulada=0 AND fecha BETWEEN '" + Fecha1Sql + "' AND '" + Fecha2Sql + "'"); decimal NotasCredito = this.Connection.FieldDecimal("SELECT SUM(total) FROM comprob WHERE tipo_fac IN ('NCA', 'NCB', 'NCC', 'NCE', 'NCM') AND impresa>0 AND compra=0 AND anulada=0 AND fecha BETWEEN '" + Fecha1Sql + "' AND '" + Fecha2Sql + "'"); decimal Costo = this.Connection.FieldDecimal("SELECT SUM(costo*cantidad) FROM comprob, comprob_detalle WHERE comprob.id_comprob=comprob_detalle.id_comprob AND comprob.tipo_fac IN ('FA', 'FB', 'FC', 'FE', 'FM', 'NDA', 'NDB', 'NDC', 'NDE', 'NDM') AND comprob.compra=0 AND comprob.numero>0 AND comprob.anulada=0 AND comprob_detalle.precio>0 AND comprob.fecha BETWEEN '" + Fecha1Sql + "' AND '" + Fecha2Sql + "'"); decimal CostoNotasCredito = this.Connection.FieldDecimal("SELECT SUM(costo*cantidad) FROM comprob, comprob_detalle WHERE comprob.id_comprob=comprob_detalle.id_comprob AND comprob.tipo_fac IN ('NCA', 'NCB', 'NCC', 'NCE', 'NCM') AND comprob.impresa>0 AND comprob.compra=0 AND comprob.anulada=0 AND comprob.fecha BETWEEN '" + Fecha1Sql + "' AND '" + Fecha2Sql + "'"); //decimal CostoCapital = this.Connection.FieldDecimal("SELECT SUM(importe) FROM cajas_movim WHERE id_concepto IN (SELECT id_concepto FROM conceptos WHERE grupo=220) AND fecha BETWEEN '" + Fecha1Sql + "' AND '" + Fecha2Sql + "'"); decimal GastosFijos = this.Connection.FieldDecimal("SELECT SUM(importe) FROM cajas_movim WHERE id_concepto IN (SELECT id_concepto FROM conceptos WHERE grupo=230) AND fecha BETWEEN '" + Fecha1Sql + "' AND '" + Fecha2Sql + "'"); decimal GastosVariables = this.Connection.FieldDecimal("SELECT SUM(importe) FROM cajas_movim WHERE id_concepto IN (SELECT id_concepto FROM conceptos WHERE grupo=240) AND fecha BETWEEN '" + Fecha1Sql + "' AND '" + Fecha2Sql + "'"); decimal OtrosEgresos = this.Connection.FieldDecimal("SELECT SUM(importe) FROM cajas_movim WHERE importe<0 AND id_concepto IN (SELECT id_concepto FROM conceptos WHERE grupo NOT IN (110, 210, 220, 230, 240, 300)) AND id_concepto<>26030 AND fecha BETWEEN '" + Fecha1Sql + "' AND '" + Fecha2Sql + "'"); ElFacturacion[mes - 1].Value = Facturas - NotasCredito; ElCosto[mes - 1].Value = Costo - CostoNotasCredito; ElGastos[mes - 1].Value = Math.Abs(GastosFijos + GastosVariables + OtrosEgresos); ElRentabilidad[mes - 1].Value = ElFacturacion[mes - 1].Value - ElCosto[mes - 1].Value - ElGastos[mes - 1].Value; } } Lbl.Charts.Serie Serie1 = new Lbl.Charts.Serie("Facturación"); Lbl.Charts.Serie Serie2 = new Lbl.Charts.Serie("Gastos"); Lbl.Charts.Serie Serie3 = new Lbl.Charts.Serie("Rentabilidad"); Serie1.Elements = ElFacturacion; Serie2.Elements = ElGastos; Serie3.Elements = ElRentabilidad; if (anio == DateTime.Now.Year) { Serie1.Color = System.Drawing.Color.Goldenrod; Serie2.Color = System.Drawing.Color.Red; Serie3.Color = System.Drawing.Color.Green; } else { Serie1.Color = System.Drawing.Color.LightGoldenrodYellow; Serie2.Color = System.Drawing.Color.Pink; Serie3.Color = System.Drawing.Color.LightGreen; } Chrt.Series.Add(Serie1); Chrt.Series.Add(Serie2); Chrt.Series.Add(Serie3); }
public void RentabilidadAnual(int anio, Lui.Forms.Chart Chrt) { if (Chrt.Series == null) { Chrt.Series = new System.Collections.Generic.List <Lbl.Charts.Serie>(); } Lbl.Charts.Element[] ElFacturacion = new Lbl.Charts.Element[12]; Lbl.Charts.Element[] ElCosto = new Lbl.Charts.Element[12]; Lbl.Charts.Element[] ElGastos = new Lbl.Charts.Element[12]; Lbl.Charts.Element[] ElRentabilidad = new Lbl.Charts.Element[12]; for (int mes = 1; mes <= 12; mes++) { if (anio > DateTime.Now.Year || (anio == DateTime.Now.Year && mes > DateTime.Now.Month)) { //Nada ? } else { ElFacturacion[mes - 1] = new Lbl.Charts.Element(); ElCosto[mes - 1] = new Lbl.Charts.Element(); ElGastos[mes - 1] = new Lbl.Charts.Element(); ElRentabilidad[mes - 1] = new Lbl.Charts.Element(); string Fecha1Sql = anio.ToString("0000") + "-" + mes.ToString("00") + "-01"; string Fecha2Sql = anio.ToString("0000") + "-" + mes.ToString("00") + "-31"; decimal Facturas = this.Connection.FieldDecimal("SELECT SUM(total) FROM comprob WHERE tipo_fac IN ('FA', 'FB', 'FC', 'FE', 'FM', 'NDA', 'NDB', 'NDC', 'NDE', 'NDM') AND impresa>0 AND compra=0 AND anulada=0 AND fecha BETWEEN '" + Fecha1Sql + "' AND '" + Fecha2Sql + "'"); decimal NotasCredito = this.Connection.FieldDecimal("SELECT SUM(total) FROM comprob WHERE tipo_fac IN ('NCA', 'NCB', 'NCC', 'NCE', 'NCM') AND impresa>0 AND compra=0 AND anulada=0 AND fecha BETWEEN '" + Fecha1Sql + "' AND '" + Fecha2Sql + "'"); decimal Costo = this.Connection.FieldDecimal("SELECT SUM(costo*cantidad) FROM comprob, comprob_detalle WHERE comprob.id_comprob=comprob_detalle.id_comprob AND comprob.tipo_fac IN ('FA', 'FB', 'FC', 'FE', 'FM', 'NDA', 'NDB', 'NDC', 'NDE', 'NDM') AND comprob.compra=0 AND comprob.numero>0 AND comprob.anulada=0 AND comprob_detalle.precio>0 AND comprob.fecha BETWEEN '" + Fecha1Sql + "' AND '" + Fecha2Sql + "'"); decimal CostoNotasCredito = this.Connection.FieldDecimal("SELECT SUM(costo*cantidad) FROM comprob, comprob_detalle WHERE comprob.id_comprob=comprob_detalle.id_comprob AND comprob.tipo_fac IN ('NCA', 'NCB', 'NCC', 'NCE', 'NCM') AND comprob.impresa>0 AND comprob.compra=0 AND comprob.anulada=0 AND comprob.fecha BETWEEN '" + Fecha1Sql + "' AND '" + Fecha2Sql + "'"); //decimal CostoCapital = this.Connection.FieldDecimal("SELECT SUM(importe) FROM cajas_movim WHERE id_concepto IN (SELECT id_concepto FROM conceptos WHERE grupo=220) AND fecha BETWEEN '" + Fecha1Sql + "' AND '" + Fecha2Sql + "'"); decimal GastosFijos = this.Connection.FieldDecimal("SELECT SUM(importe) FROM cajas_movim WHERE id_concepto IN (SELECT id_concepto FROM conceptos WHERE grupo=230) AND fecha BETWEEN '" + Fecha1Sql + "' AND '" + Fecha2Sql + "'"); decimal GastosVariables = this.Connection.FieldDecimal("SELECT SUM(importe) FROM cajas_movim WHERE id_concepto IN (SELECT id_concepto FROM conceptos WHERE grupo=240) AND fecha BETWEEN '" + Fecha1Sql + "' AND '" + Fecha2Sql + "'"); decimal OtrosEgresos = this.Connection.FieldDecimal("SELECT SUM(importe) FROM cajas_movim WHERE importe<0 AND id_concepto IN (SELECT id_concepto FROM conceptos WHERE grupo NOT IN (110, 210, 220, 230, 240, 300)) AND id_concepto<>26030 AND fecha BETWEEN '" + Fecha1Sql + "' AND '" + Fecha2Sql + "'"); ElFacturacion[mes - 1].Value = Facturas - NotasCredito; ElCosto[mes - 1].Value = Costo - CostoNotasCredito; ElGastos[mes - 1].Value = Math.Abs(GastosFijos + GastosVariables + OtrosEgresos); ElRentabilidad[mes - 1].Value = ElFacturacion[mes - 1].Value - ElCosto[mes - 1].Value - ElGastos[mes - 1].Value; } } Lbl.Charts.Serie Serie1 = new Lbl.Charts.Serie("Facturación"); Lbl.Charts.Serie Serie2 = new Lbl.Charts.Serie("Gastos"); Lbl.Charts.Serie Serie3 = new Lbl.Charts.Serie("Rentabilidad"); Serie1.Elements = ElFacturacion; Serie2.Elements = ElGastos; Serie3.Elements = ElRentabilidad; if (anio == DateTime.Now.Year) { Serie1.Color = System.Drawing.Color.Goldenrod; Serie2.Color = System.Drawing.Color.Red; Serie3.Color = System.Drawing.Color.Green; } else { Serie1.Color = System.Drawing.Color.LightGoldenrodYellow; Serie2.Color = System.Drawing.Color.Pink; Serie3.Color = System.Drawing.Color.LightGreen; } Chrt.Series.Add(Serie1); Chrt.Series.Add(Serie2); Chrt.Series.Add(Serie3); }