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