public object FindForId(int id)
{
var pagar = new List <ClientesPagarViewModels>();
string query = "SELECT a.nombre, a.apellido, b.id FROM Cliente a, Venta b WHERE a.id = b.cliente_id AND a.id = @id";
using (var connection = PostConnection.Connection())
{
using (var command = db.Command(query))
{
try
{
connection.Open();
command.Connection = connection;
command.Parameters.AddWithValue("@id", id);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
pagar.Add(new ClientesPagarViewModels()
{
nombre = Convert.ToString(reader["nombre"]),
apellido = Convert.ToString(reader["apellido"]),
idventa = Convert.ToInt32(reader["id"])
});
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
return(pagar);
}
public object FindAll()
{
var lista = new List <ManzanaLoteViewModels>();
string query = "SELECT id, manzana FROM manzana";
using (var connection = PostConnection.Connection())
{
using (var command = db.Command(query))
{
try
{
connection.Open();
command.Connection = connection;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
lista.Add(new ManzanaLoteViewModels()
{
id = Convert.ToInt32(reader["id"]),
manzana = reader["manzana"].ToString(),
});
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
return(lista);
}
public object FindAll()
{
var lista = new List <EstadoCliViewModels>();
string query = "SELECT id, estado FROM estado_cliente";
using (var connection = PostConnection.Connection())
{
using (var command = db.Command(query))
{
try
{
connection.Open();
command.Connection = connection;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
lista.Add(new EstadoCliViewModels()
{
id = Convert.ToInt16(reader["id"]),
estado = reader["estado"].ToString(),
});
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
return(lista);
}
// public int Delete(int id)
public object FindAll()
{
var lista = new List <CargoEmpViewModels>();
string query = "SELECT id, nombrecargo, sueldo FROM cargo";
using (var connection = PostConnection.Connection())
{
using (var command = db.Command(query))
{
try
{
connection.Open();
command.Connection = connection;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
lista.Add(new CargoEmpViewModels()
{
id = Convert.ToInt16(reader["id"]),
nombrecargo = reader["nombrecargo"].ToString(),
sueldo = Convert.ToDouble(reader["sueldo"]),
});
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
return(lista);
}
public object FindAll()
{
string query = "SELECT id, descripcion FROM tipo_venta";
var tipo_venta = new List <TipoVentaViewModels>();
using (var connection = PostConnection.Connection())
{
using (var command = db.Command(query))
{
try
{
connection.Open();
command.Connection = connection;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
tipo_venta.Add(new TipoVentaViewModels()
{
Id = Convert.ToInt16(reader["id"]),
Descripcion = reader["descripcion"].ToString(),
});
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
return(tipo_venta);
}
public int Save(object data)
{
int estado = -1;
var venta = (DescuentoViewModels)data;
string query = "INSERT INTO descuentos(cantidad, venta_id) " +
"VALUES(@cantidad, @venta_id)";
var command = db.Command(query);
command.Parameters.AddWithValue("@cantidad", venta.Cantidad);
command.Parameters.AddWithValue("@venta_id", venta.VentaId);
estado = db.Command(command);
return(estado);
}
public object FindForId(int id)
{
var listaMoras = new List <MoraViewModels>();
string query = "SELECT monto, fecha, estado FROM moras WHERE venta_id = @id";
using (var connection = PostConnection.Connection())
{
using (var command = db.Command(query))
{
try
{
connection.Open();
command.Connection = connection;
command.Parameters.AddWithValue("@id", id);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
listaMoras.Add(new MoraViewModels()
{
Monto = Convert.ToDouble(reader["monto"]),
Fecha = Convert.ToDateTime(reader["fecha"]),
Estado = Convert.ToInt16(reader["estado"])
});
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
return(listaMoras);
}
public object FindAll()
{
string query = "SELECT id FROM venta ORDER BY id DESC LIMIT 1";
int ventaId = 0;
using (var connection = PostConnection.Connection())
{
using (var command = db.Command(query))
{
try
{
connection.Open();
command.Connection = connection;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
ventaId = Convert.ToInt32(reader["id"]);
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
return(ventaId);
}
public object FindForId(int id)
{
string query = "SELECT cuota FROM finan_venta WHERE venta_id = @id";
double cuota = 0;
using (var connection = PostConnection.Connection())
{
using (var command = _database.Command(query))
{
try
{
connection.Open();
command.Connection = connection;
command.Parameters.AddWithValue("@id", id);
using (var reader = command.ExecuteReader())
{
cuota = Convert.ToDouble(reader["cuota"]);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
return(cuota);
}
public int Save(object data)
{
int estado = -1;
var venta = (VentaViewModels)data;
string query = "INSERT INTO venta(fecha, cliente_id, empventa_id, empingreso_id, tipoventa_id) " +
"VALUES(now(), @cliente_id, @empventa_id, @tipoventa_id)";
var command = db.Command(query);
command.Parameters.AddWithValue("@cliente_id", venta.ClienteId);
command.Parameters.AddWithValue("@empventa_id", venta.EmpVentaId);
command.Parameters.AddWithValue("@tipoventa_id", venta.TipoVentaId);
estado = db.Command(command);
return(estado);
}
public int Save(object data)
{
int estado = -1;
var venta = (DetalleVentaViewModels)data;
string query = "INSERT INTO detalle_venta(precio_inicial, precio_final, lote_id, venta_id) " +
"VALUES(@precio_inicial,@precio_final,@lote_id,@venta_id)";
var command = db.Command(query);
command.Parameters.AddWithValue("@precio_inicial", venta.PrecioInicial);
command.Parameters.AddWithValue("@precio_final", venta.PrecioFinal);
command.Parameters.AddWithValue("@lote_id", venta.LoteId);
command.Parameters.AddWithValue("@venta_id", venta.VentaId);
estado = db.Command(command);
return(estado);
}
public int Save(object data)
{
int estado = -1;
var finan_venta = (FinanVentaViewModels)data;
string query = "INSERT INTO finan_venta(tiempo, cuota, tasa_interes, mora, venta_id)" +
"VALUES(@tiempo,@cuota,@tasa_interes,@mora,@venta_id)";
var command = db.Command(query);
command.Parameters.AddWithValue("@tiempo", finan_venta.Tiempo);
command.Parameters.AddWithValue("@cuota", finan_venta.Cuota);
command.Parameters.AddWithValue("@tasa_interes", finan_venta.TasaInteres);
command.Parameters.AddWithValue("@mora", finan_venta.Mora);
command.Parameters.AddWithValue("@venta_id", finan_venta.VentaId);
estado = db.Command(command);
return(estado);
}
public object FindForId(int id)
{
var listaMoras = new List <PagosSelectViewModels>();
string query = "SELECT saldo_anterior, saldo_actual, monto, fecha_a_pagar, fecha_cancelada " +
"FROM pagos WHERE venta_id = @id";
using (var connection = PostConnection.Connection())
{
using (var command = db.Command(query))
{
try
{
connection.Open();
command.Connection = connection;
command.Parameters.AddWithValue("@id", id);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
listaMoras.Add(new PagosSelectViewModels()
{
SaldoAnterior = Convert.ToDouble(reader["saldo_anterior"]),
SaldoActual = Convert.ToDouble(reader["saldo_actual"]),
Monto = Convert.ToDouble(reader["monto"]),
FechaPagar = Convert.ToDateTime(reader["fecha_a_pagar"]),
FechaCancelada = Convert.ToDateTime(reader["fecha_cancelada"])
});
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
return(listaMoras);
}
public object FindForId(int id)
{
var paramsMora = new CalcularMoraViewModels();
string query = "SELECT saldo, fecha, cuota, mora FROM calc_demorados WHERE venta_id = @venta_id"
+ " ORDER BY fecha DESC LIMIT 1";
using (var connection = PostConnection.Connection())
{
using (var command = db.Command(query))
{
try
{
connection.Open();
command.Parameters.AddWithValue("@venta_id", id);
command.Connection = connection;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
paramsMora.Saldo = Convert.ToDouble(reader["saldo"]);
paramsMora.Fecha = Convert.ToDateTime(reader["fecha"]);
paramsMora.Cuota = Convert.ToDouble(reader["cuota"]);
paramsMora.TasaMora = Convert.ToDouble(reader["mora"]);
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
DateTime fechaUltimaMora = FechaUltimaMora(id);
paramsMora.Fecha = paramsMora.Fecha < fechaUltimaMora ? fechaUltimaMora : paramsMora.Fecha;
return(paramsMora);
}
public int Save(Object data)
{
int estado = 1;
String query = "INSERT INTO referencia (nombre, apellido, telefono, direccion, id_cliente) " +
"values (@nombre, @apellido, @telefono, @direccion, @id_cliente)";
var datos = (ClienteReferenciaViewModels)data;
var command = db.Command(query);
command.Parameters.AddWithValue("@nombre", datos.Nombre);
command.Parameters.AddWithValue("@apellido", datos.Apellido);
command.Parameters.AddWithValue("@telefono", datos.Telefono);
command.Parameters.AddWithValue("@direccion", datos.Direccion);
command.Parameters.AddWithValue("@id_cliente", datos.ClienteId);
estado = db.Command(command);
return(estado);
}
public object FindAll()
{
var lista = new List <LoteViewModels>();
string query = "select id, numero, largo, ancho, mts_cuadrados, precio, manzana, lotificadora, estado from viewlote";
// string query = "SELECT no_lote, largo, ancho, mts_cuadrados, precio_lote, manzana_id, lotificadora_id, estado_id from lote ";
using (var connection = PostConnection.Connection())
{
using (var command = db.Command(query))
{
try
{
connection.Open();
command.Connection = connection;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
lista.Add(new LoteViewModels()
{
id = Convert.ToInt32(reader["id"]),
numero = Convert.ToInt32(reader["numero"]),
largo = Convert.ToDouble(reader["largo"]),
ancho = Convert.ToDouble(reader["ancho"]),
mts_cuadrados = Convert.ToDouble(reader["mts_cuadrados"]),
precio = Convert.ToDouble(reader["precio"]),
manzana = Convert.ToString(reader["manzana"]),
lotificadora = Convert.ToString(reader["lotificadora"]),
estado = Convert.ToString(reader["estado"]),
// @no_lote,@largo,@ancho,@mts_cuadrados,@precio_lote,@manzana_id,@interes_id,@estado_id
});
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
return(lista);
}
public object FindAll()
{
var lista = new List <EmpleadosIngresoViewModels>();
string query = "SELECT id, nombre, apellido, dpi, telefono, sexo, fecha_nac, direccion, id_cargo, id_estadoemp FROM empleados";
using (var connection = PostConnection.Connection())
{
using (var command = db.Command(query))
{
try
{
connection.Open();
command.Connection = connection;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
lista.Add(new EmpleadosIngresoViewModels()
{
id = Convert.ToInt16(reader["id"]),
nombre_e = reader["nombre"].ToString(),
apellido_e = reader["apellido"].ToString(),
dpi_e = Convert.ToInt64(reader["dpi"]),
tel_e = Convert.ToInt32(reader["telefono"]),
sexo_e = Convert.ToInt16(reader["sexo"]),
fecha_nac_e = Convert.ToDateTime(reader["fecha_nac"]),
dire_e = reader["direccion"].ToString(),
cargo_e = Convert.ToInt16(reader["id_cargo"]),
estado_e = Convert.ToInt16(reader["id_estadoemp"]),
});
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
return(lista);
}
public int Save(Object data)
{
int estado = 1;
String query = " insert into benefIciario (nombre, apellido, dpi, telefono, direccion, sexo, id_cliente) " +
"values (@nombre, @apellido, @dpi, @telefono, @direccion, @sexo, @id_cliente )";
// nombre, apellido, dpi, telefono, direccion, sexo, id_cliente
var datos = (BeneficiarioViewModels)data;
var command = db.Command(query);
command.Parameters.AddWithValue("@nombre", datos.Nombre);
command.Parameters.AddWithValue("@apellido", datos.Apellido);
command.Parameters.AddWithValue("@dpi", datos.Dpi);
command.Parameters.AddWithValue("@telefono", datos.Telefono);
command.Parameters.AddWithValue("@direccion", datos.Direccion);
command.Parameters.AddWithValue("@sexo", datos.Sexo);
command.Parameters.AddWithValue("@id_cliente", datos.ClienteId);
estado = db.Command(command);
return(estado);
}
public object FindAll()
{
var lista = new List <ClienteInsertViewModels>();
string query = "SELECT id, nombre, apellido, dpi, telefono, fecha,sexo, direccion, estado_id FROM Cliente";
using (var conection = PostConnection.Connection())
{
using (var command = db.Command(query))
{
try
{
conection.Open();
command.Connection = conection;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
lista.Add(new ClienteInsertViewModels()
{
id = Convert.ToInt32(reader["id"]),
nombre = Convert.ToString(reader["nombre"]),
apellido = Convert.ToString(reader["apellido"]),
dpi = Convert.ToString(reader["dpi"]),
telefono = Convert.ToString(reader["telefono"]),
fecha = Convert.ToDateTime(reader["fecha"]),
sexo = Convert.ToString(reader["sexo"]),
direccion = Convert.ToString(reader["direccion"]),
estado = Convert.ToInt32(reader["estado_id"]),
});
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
return(lista);
}
public object FindAll()
{
var lista = new List <VistaGastoViewModels>();
string query = "SELECT id, fecha, cantidad, descripcion, lotificadora, nombre, apellido FROM viewgastos";
//string query = "SELECT id, fecha, cantidad, descripcion, id_lotifi, id_empleado FROM gastos";
using (var connection = PostConnection.Connection())
{
using (var command = db.Command(query))
{
try
{
connection.Open();
command.Connection = connection;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
lista.Add(new VistaGastoViewModels()
{
id = Convert.ToInt16(reader["id"]),
Fecha = Convert.ToDateTime(reader["fecha"]),
cantidad = Convert.ToDouble(reader["cantidad"]),
descripcion = reader["descripcion"].ToString(),
lotificadora = reader["lotificadora"].ToString(),
nombre = reader["nombre"].ToString(),
apellido = reader["apellido"].ToString(),
});
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
return(lista);
}
// public int Delete(int id)
public object FindAll()
{
var lista = new List <LotificadoraViewModels>();
string query = "SELECT id, nombre,direccion, telefono, tasa_interes, tasa_mora FROM lotificadora";
using (var connection = PostConnection.Connection())
{
using (var command = db.Command(query))
{
try
{
connection.Open();
command.Connection = connection;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
lista.Add(new LotificadoraViewModels()
{
id = Convert.ToInt16(reader["id"]),
nombre = reader["nombre"].ToString(),
direccion = reader["direccion"].ToString(),
telefono = Convert.ToInt32(reader["telefono"]),
tasa_interes = Convert.ToDouble(reader["tasa_interes"]),
tasa_mora = Convert.ToDouble(reader["tasa_mora"]),
});
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
return(lista);
}
public object FindAll()
{
var lista = new List <ReferenciaEmpViewModels>();
string query = "SELECT id, nombre, apellido, telefono, direccion, id_emp FROM referenciaemp";
using (var connection = PostConnection.Connection())
{
using (var command = db.Command(query))
{
try
{
connection.Open();
command.Connection = connection;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
lista.Add(new ReferenciaEmpViewModels()
{
id = Convert.ToInt16(reader["id"]),
nombre = reader["nombre"].ToString(),
apellido = reader["apellido"].ToString(),
tel = Convert.ToInt32(reader["telefono"]),
direccion = reader["direccion"].ToString(),
id_emp = Convert.ToInt16(reader["id_emp"]),
});
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
return(lista);
}
public object FindAll()
{
var promociones = new List <PromocionViewModels>();
string query = "SELECT id, cantidad, descripcion, fecha_inicio, fecha_fin, loti_id " +
"FROM promociones WHERE now() <= fecha_fin+1";
//select* from promociones WHERE loti_id = 1 AND(now() BETWEEN fecha_inicio AND fecha_fin + 1
using (var connection = PostConnection.Connection())
{
using (var command = _database.Command(query))
{
try
{
connection.Open();
command.Connection = connection;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
promociones.Add(new PromocionViewModels()
{
Id = Convert.ToInt16(reader["id"]),
Cantidad = Convert.ToDouble(reader["cantidad"]),
Descripcion = reader["descripcion"].ToString(),
FechaInicio = Convert.ToDateTime(reader["fecha_inicio"]),
FechaFin = Convert.ToDateTime(reader["fecha_fin"]),
LotificadoraId = Convert.ToInt16(reader["loti_id"])
});
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
return(promociones);
}