// methods for listing all.
public static List <facturacion> listAllRent()
{
List <facturacion> list = new List <facturacion>();
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand(string.Format("SELECT rentFacturacion.ID, services.cService, Rent.ID AS CasoID, customers.name, customers.lastname, customers.ID, customers.idcard, rentFacturacion.Mensualidad, rentFacturacion.Deposito, rentFacturacion.contratoCant FROM rentFacturacion INNER JOIN customers ON customers.ID = rentFacturacion.clienteID INNER JOIN Rent ON Rent.ID = rentFacturacion.caseID INNER JOIN services ON services.ID = rentFacturacion.serviceID"), con);
SqlDataReader re = comand.ExecuteReader();
while (re.Read())
{
facturacion pFactura = new facturacion();
pFactura.ID = re["ID"].ToString();
pFactura.Abono = "No hay abono registrado";
pFactura.CasoNO = re["CasoID"].ToString();
pFactura.Apellido_Cliente = re["lastname"].ToString();
pFactura.Cedula = re["idcard"].ToString();
pFactura.Nombre_Cliente = re["name"].ToString();
pFactura.Pago_Total = Convert.ToDouble(re["Mensualidad"]).ToString("f2");
pFactura.Servicio = re["cService"].ToString();
pFactura.Deposito = Convert.ToDouble(re["ContratoCant"]).ToString("f2");
list.Add(pFactura);
}
con.Close();
}
return(list);
}
// method for searching ventas.
public static List <ventas> search(string NombreV, string ApellidoV, string NombreC, string ApellidoC, string ID)
{
List <ventas> list = new List <ventas>();
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand(string.Format("SELECT Venta.ID, SERVICIO.cService AS Servicio, VENDEDOR.name AS NombreVe, VENDEDOR.lastname AS ApellidoVe, COMPRADOR.name AS NombreCo, COMPRADOR.lastname AS ApellidoCo, Venta.Descripcion, Venta.Abono, Venta.Honorarios, Venta.MontoVenta FROM Venta INNER JOIN customers as VENDEDOR ON VENDEDOR.ID = Venta.VendedorID INNER JOIN customers AS COMPRADOR ON COMPRADOR.ID = Venta.CompradorID INNER JOIN services AS SERVICIO ON SERVICIO.ID = Venta.ServiceID" +
" WHERE VENDEDOR.name LIKE '{0}%' AND VENDEDOR.lastname LIKE '{1}%' AND COMPRADOR.name LIKE '{2}%' AND COMPRADOR.lastname LIKE '{3}%' AND Venta.ID LIKE '{4}%'",
NombreV, ApellidoV, NombreC, ApellidoC, ID), con);
SqlDataReader re = comand.ExecuteReader();
while (re.Read())
{
ventas pVenta = new ventas();
pVenta.Apellido_comprador = re["ApellidoCo"].ToString();
pVenta.Apellido_vendedor = re["ApellidoVe"].ToString();
pVenta.Detalles = re["Descripcion"].ToString();
pVenta.Honorarios = re["Honorarios"].ToString();
pVenta.ID = re["ID"].ToString();
pVenta.Nombre_comprador = re["NombreCo"].ToString();
pVenta.Nombre_vendedor = re["NombreVe"].ToString();
pVenta.Precio_venta = re["MontoVenta"].ToString();
pVenta.Servicio = re["Servicio"].ToString();
list.Add(pVenta);
}
con.Close();
}
return(list);
}
public static List <factura> searchFacturasVenta(string CasoNO, string CedulaPasaporte, string nombre, string apellido)
{
List <factura> list = new List <factura>();
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand(string.Format("SELECT facturasVenta.ID, facturasVenta.casoID AS NumeroCaso, services.cService, customers.name, customers.lastname, facturasVenta.TotalBalance, facturasVenta.Pago, facturasVenta.NuevoBalance FROM facturasVenta INNER JOIN customers ON customers.ID = facturasVenta.ClienteID INNER JOIN services ON services.ID = facturasVenta.servicioID " +
" WHERE facturasVenta.casoID LIKE '{0}%' AND customers.name LIKE '{1}%' AND customers.lastname LIKE '{2}%' AND customers.idcard LIKE '{3}%'",
CasoNO, nombre, apellido, CedulaPasaporte), con);
SqlDataReader re = comand.ExecuteReader();
while (re.Read())
{
factura pFactura = new factura();
pFactura.ID_Factura = re["ID"].ToString();
pFactura.Apellido_Cliente = re["lastname"].ToString();
pFactura.Balance_Total = re["TotalBalance"].ToString();
pFactura.Caso_NO = re["NumeroCaso"].ToString();
pFactura.Fecha_Proximo_Pago = "No se ha registrado";
pFactura.Nombre_Cliente = re["name"].ToString();
pFactura.Nuevo_Balance = re["NuevoBalance"].ToString();
pFactura.Pago_Realizado = re["Pago"].ToString();
pFactura.Servicio = re["cService"].ToString();
list.Add(pFactura);
}
con.Close();
}
return(list);
}
// method for searching Divorcios.
public static List <divorcios> search(string ID, string pNombreDemandante, string ApellidoDemandante, string NombreDemandado, string ApellidoDemandado)
{
List <divorcios> list = new List <divorcios>();
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand(string.Format("SELECT divorcios.ID, DEMANDANTE.name AS NombreDemandante, DEMANDANTE.lastname AS ApellidoDemandante, DEMANDADO.name AS NombreDemandado, DEMANDADO.lastname AS ApellidoDemandado, divorcios.Pagos, divorcios.Honorarios, divorcios.Abono FROM divorcios INNER JOIN customers AS DEMANDANTE ON DEMANDANTE.ID = divorcios.DemandanteID INNER JOIN customers AS DEMANDADO ON DEMANDADO.ID = divorcios.DemandadoID" +
" WHERE divorcios.ID LIKE '{0}%' AND DEMANDANTE.name LIKE '{1}%' AND DEMANDANTE.lastname LIKE '{2}%' AND DEMANDADO.name LIKE '{3}%' AND DEMANDADO.lastname LIKE '{4}%'",
ID, pNombreDemandante, ApellidoDemandante, NombreDemandado, ApellidoDemandado), con);
SqlDataReader re = comand.ExecuteReader();
while (re.Read())
{
divorcios pDivorcios = new divorcios();
pDivorcios.ID = re["ID"].ToString();
pDivorcios.Nombre_Demandante = re["NombreDemandante"].ToString();
pDivorcios.Apellido_Demandante = re["ApellidoDemandante"].ToString();
pDivorcios.Nombre_Demandado = re["NombreDemandado"].ToString();
pDivorcios.Apellido_Demandado = re["ApellidoDemandado"].ToString();
pDivorcios.Honorarios = re["Honorarios"].ToString();
pDivorcios.Precio = re["Pagos"].ToString();
pDivorcios.Abono = re["Abono"].ToString();
list.Add(pDivorcios);
}
con.Close();
}
return(list);
}
public static List <facturacion> searchDivorciosAccidentes(string cedulaE, string cedulaO)
{
List <facturacion> list = new List <facturacion>();
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand(string.Format("SELECT divorciosFacturacion.ID, services.cService, divorcios.ID AS CasoID, DEMANDANTE.name, DEMANDANTE.lastname, DEMANDANTE.idcard, divorciosFacturacion.TotalPago, divorciosFacturacion.Abono FROM divorcios INNER JOIN divorciosFacturacion ON divorcios.ID = divorciosFacturacion.CaseID INNER JOIN customers AS DEMANDANTE ON DEMANDANTE.ID = divorcios.DemandanteID INNER JOIN services ON services.ID = divorciosFacturacion.serviceID INNER JOIN customers AS DEMANDADO ON divorcios.DemandadoID = DEMANDADO.ID " +
" WHERE DEMANDANTE.idcard LIKE '{0}%' AND DEMANDADO.idcard LIKE '{1}%'", cedulaE, cedulaO), con);
SqlDataReader reader = comand.ExecuteReader();
while (reader.Read())
{
facturacion pFactura = new facturacion();
pFactura.ID = reader["ID"].ToString();
pFactura.Abono = Convert.ToDouble(reader["Abono"]).ToString("f2");
pFactura.CasoNO = reader["CasoID"].ToString();
pFactura.Apellido_Cliente = reader["lastname"].ToString();
pFactura.Cedula = reader["idcard"].ToString();
pFactura.Nombre_Cliente = reader["name"].ToString();
pFactura.Pago_Total = Convert.ToDouble(reader["TotalPago"]).ToString("f2");
pFactura.Servicio = reader["cService"].ToString();
pFactura.Deposito = "No hay deposito registrado";
list.Add(pFactura);
}
con.Close();
}
return(list);
}
public static List <facturacion> listAllVentas()
{
List <facturacion> list = new List <facturacion>();
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand("SELECT ventaFacturacion.ID, services.cService, Venta.ID AS CasoID, customers.name, customers.lastname, customers.idcard, ventaFacturacion.TotalPago, ventaFacturacion.Abono FROM ventaFacturacion INNER JOIN Venta ON Venta.ID = ventaFacturacion.caseID INNER JOIN customers ON customers.ID = ventaFacturacion.clienteID INNER JOIN services ON services.ID = ventaFacturacion.serviceID", con);
SqlDataReader re = comand.ExecuteReader();
while (re.Read())
{
facturacion pFactura = new facturacion();
pFactura.ID = re["ID"].ToString();
pFactura.Abono = Convert.ToDouble(re["Abono"]).ToString("f2");
pFactura.CasoNO = re["CasoID"].ToString();
pFactura.Apellido_Cliente = re["lastname"].ToString();
pFactura.Cedula = re["idcard"].ToString();
pFactura.Nombre_Cliente = re["name"].ToString();
pFactura.Pago_Total = Convert.ToDouble(re["TotalPago"]).ToString("f2");
pFactura.Servicio = re["cService"].ToString();
pFactura.Deposito = "No hay deposito registrado";
list.Add(pFactura);
}
con.Close();
}
return(list);
}
// methods for searching all.
public static List <facturacion> searchRent(string cedulaCli, string cedulaIn, string cedulaGa)
{
List <facturacion> list = new List <facturacion>();
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand(string.Format("SELECT rentFacturacion.ID, services.cService, Rent.ID AS CasoID, CLENTE.name, CLENTE.lastname, CLENTE.ID, CLENTE.idcard, rentFacturacion.Mensualidad, rentFacturacion.Deposito FROM Rent INNER JOIN customers AS CLENTE ON CLENTE.ID = Rent.CLienteID INNER JOIN rentFacturacion ON Rent.ID = rentFacturacion.caseID INNER JOIN services ON services.ID = rentFacturacion.serviceID INNER JOIN customers AS INQUILINO ON INQUILINO.ID = Rent.InquilinoID INNER JOIN customers AS GARANTE ON GARANTE.ID = Rent.GaranteID " +
" WHERE CLENTE.idcard LIKE '{0}%' AND INQUILINO.idcard LIKE '{1}%' AND GARANTE.idcard LIKE '{2}%'", cedulaCli, cedulaIn, cedulaGa), con);
SqlDataReader re = comand.ExecuteReader();
while (re.Read())
{
facturacion pFactura = new facturacion();
pFactura.ID = re["ID"].ToString();
pFactura.Abono = "No hay abono registrado";
pFactura.CasoNO = re["CasoID"].ToString();
pFactura.Apellido_Cliente = re["lastname"].ToString();
pFactura.Cedula = re["idcard"].ToString();
pFactura.Nombre_Cliente = re["name"].ToString();
pFactura.Pago_Total = Convert.ToDouble(re["Mensualidad"]).ToString("f2");
pFactura.Servicio = re["cService"].ToString();
pFactura.Deposito = Convert.ToDouble(re["Deposito"]).ToString("f2");
list.Add(pFactura);
}
con.Close();
}
return(list);
}
// method for listing all rent
public static List <alquiler> listAll()
{
List <alquiler> list = new List <alquiler>();
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand(string.Format("SELECT Rent.ID, Rent.direccion, Cities.City, Rent.detalles, Rent.Mensualidad, Rent.Honorario, Rent.Abono, Rent.Deposito, CLIENTE.name AS NombreCli, CLIENTE.lastname AS ApellidoCli, INQUILINO.name AS NombreInqui, INQUILINO.lastname AS ApellidoInqui, GARANTE.name AS NombreGara, GARANTE.lastname AS ApellidoGara FROM Rent INNER JOIN Cities ON Cities.ID = Rent.City INNER JOIN customers AS CLIENTE ON CLIENTE.ID = Rent.CLienteID INNER JOIN customers AS INQUILINO ON INQUILINO.ID = Rent.InquilinoID INNER JOIN customers AS GARANTE ON GARANTE.ID = Rent.GaranteID"), con);
SqlDataReader re = comand.ExecuteReader();
while (re.Read())
{
alquiler pAlquiler = new alquiler();
pAlquiler.ID = re["ID"].ToString();
pAlquiler.Apellido_Cliente = re["ApellidoCli"].ToString();
pAlquiler.Apellido_Garante = re["ApellidoGara"].ToString();
pAlquiler.Apellido_Inquilino = re["ApellidoInqui"].ToString();
pAlquiler.Deposito = re["Deposito"].ToString();
pAlquiler.Detalles = re["detalles"].ToString();
pAlquiler.Mensualidad = re["Mensualidad"].ToString();
pAlquiler.Nombre_Cliente = re["NombreCli"].ToString();
pAlquiler.Nombre_Garante = re["NombreGara"].ToString();
pAlquiler.Nombre_Inquilino = re["NombreInqui"].ToString();
pAlquiler.Ubicacion = re["direccion"].ToString();
list.Add(pAlquiler);
}
con.Close();
}
return(list);
}
// methods for listing all facturas
public static List <factura> listAllRentFacturas()
{
List <factura> list = new List <factura>();
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand("SELECT facturasAlquiler.ID AS CodigoFactura, facturasAlquiler.casoID AS NumeroCaso, facturasAlquiler.TotalBalance AS BalanceTotal, facturasAlquiler.Pago AS PagoRealizado, facturasAlquiler.NuevoBalance, facturasAlquiler.ProximoPago AS FechaProximoPago, customers.name AS NombreCli, customers.lastname AS ApellidCli, customers.idcard AS CedulaoPasaporte, services.cService AS Servicio, facturasAlquiler.Detalles AS DetallesPago FROM facturasAlquiler INNER JOIN customers ON customers.ID = facturasAlquiler.clienteID INNER JOIN services ON services.ID = facturasAlquiler.servicioID", con);
SqlDataReader re = comand.ExecuteReader();
while (re.Read())
{
factura pFactura = new factura();
pFactura.ID_Factura = re["CodigoFactura"].ToString();
pFactura.Apellido_Cliente = re["ApellidCli"].ToString();
pFactura.Balance_Total = re["BalanceTotal"].ToString();
pFactura.Caso_NO = re["NumeroCaso"].ToString();
pFactura.Fecha_Proximo_Pago = re["FechaProximoPago"].ToString();
pFactura.Nombre_Cliente = re["NombreCli"].ToString();
pFactura.Nuevo_Balance = re["NuevoBalance"].ToString();
pFactura.Pago_Realizado = re["PagoRealizado"].ToString();
pFactura.Servicio = re["Servicio"].ToString();
list.Add(pFactura);
}
con.Close();
}
return(list);
}
public static List <factura> listAllVentafacturas()
{
List <factura> list = new List <factura>();
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand("SELECT facturasVenta.ID, facturasVenta.casoID AS NumeroCaso, services.cService, customers.name, customers.lastname, facturasVenta.TotalBalance, facturasVenta.Pago, facturasVenta.NuevoBalance FROM facturasVenta INNER JOIN customers ON customers.ID = facturasVenta.ClienteID INNER JOIN services ON services.ID = facturasVenta.servicioID", con);
SqlDataReader re = comand.ExecuteReader();
while (re.Read())
{
factura pFactura = new factura();
pFactura.ID_Factura = re["ID"].ToString();
pFactura.Apellido_Cliente = re["lastname"].ToString();
pFactura.Balance_Total = re["TotalBalance"].ToString();
pFactura.Caso_NO = re["NumeroCaso"].ToString();
pFactura.Fecha_Proximo_Pago = "No se ha registrado";
pFactura.Nombre_Cliente = re["name"].ToString();
pFactura.Nuevo_Balance = re["NuevoBalance"].ToString();
pFactura.Pago_Realizado = re["Pago"].ToString();
pFactura.Servicio = re["cService"].ToString();
list.Add(pFactura);
}
con.Close();
}
return(list);
}
// search engine for listing customers.
public static List <clientes> searchCustomers(string ID, string name, string lastName, string cedula, string status, string registerDate)
{
List <clientes> list = new List <clientes>();
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand(string.Format("SELECT * FROM customers INNER JOIN Cities ON customers.CityID = Cities.ID INNER JOIN CustomerStatus ON CustomerStatus.ID = customers.StatusId WHERE customers.ID LIKE '{0}%' AND name LIKE '{1}%' AND lastname LIKE '{2}%' AND idcard LIKE '{3}%' AND status LIKE '{4}%' AND registerdate LIKE '{5}%' ",
ID, name, lastName, cedula, status, registerDate), con);
SqlDataReader re = comand.ExecuteReader();
while (re.Read())
{
clientes pClientes = new clientes();
pClientes.ID = re["ID"].ToString();
pClientes.Nombre = re["name"].ToString();
pClientes.Apellido = re["lastname"].ToString();
pClientes.Cedula = re["idcard"].ToString();
pClientes.Image = re.GetString(5);
pClientes.Status = re["Status"].ToString();
pClientes.Fecha_Registro = re["registerdate"].ToString();
pClientes.Fecha_Registro = re["registerdate"].ToString();
pClientes.Direccion = re["cAddress"].ToString();
pClientes.E_Mail = re["email"].ToString();
pClientes.Ocupacion = re["ocupation"].ToString();
pClientes.Sector = re["City"].ToString();
pClientes.Telefono = re["Phone"].ToString();
pClientes.Celular = re["Cellphone"].ToString();
list.Add(pClientes);
}
con.Close();
}
return(list);
}
// method for getting alquiler.
public static pBaseAlquilerFacturacion getAlquiler(string ID)
{
pBaseAlquilerFacturacion pBase = new pBaseAlquilerFacturacion();
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand(string.Format("SELECT * FROM Rent WHERE ID = '{0}'", ID), con);
SqlDataReader re = comand.ExecuteReader();
while (re.Read())
{
pBase.ID = re["ID"].ToString();
pBase.ClienteID = re["ClienteID"].ToString();
pBase.InquilinoID = re["InquilinoID"].ToString();
pBase.GaranteID = re["GaranteID"].ToString();
pBase.servicioID = re["ServicioID"].ToString();
pBase.Mensualidad = Convert.ToDouble(re["Mensualidad"]).ToString("f2");
pBase.Detalles = re["Detalles"].ToString();
pBase.Direccion = re["Direccion"].ToString();
pBase.City = re["City"].ToString();
pBase.Deposito = Convert.ToDouble(re["Deposito"]).ToString("f2");
pBase.Abono = Convert.ToDouble(re["Abono"]).ToString("f2");
pBase.Alquiler = Convert.ToDouble(re["Alquiler"]).ToString("f2");
pBase.ContratoTotal = Convert.ToDouble(re["ContratoCant"]).ToString("f2");
}
con.Close();
}
return(pBase);
}
// method for listing all customers.
public static List <clientes> listAllCustomers()
{
List <clientes> list = new List <clientes>();
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand("SELECT * FROM customers INNER JOIN Cities ON customers.CityID = Cities.ID INNER JOIN CustomerStatus ON CustomerStatus.ID = customers.StatusId", con);
SqlDataReader re = comand.ExecuteReader();
while (re.Read())
{
clientes pClientes = new clientes();
pClientes.ID = re["ID"].ToString();
pClientes.Nombre = re["name"].ToString();
pClientes.Apellido = re["lastname"].ToString();
pClientes.Cedula = re["idcard"].ToString();
pClientes.Image = re.GetString(5);
pClientes.Status = re["Status"].ToString();
pClientes.Fecha_Registro = re["registerdate"].ToString();
pClientes.Direccion = re["cAddress"].ToString();
pClientes.E_Mail = re["email"].ToString();
pClientes.Ocupacion = re["ocupation"].ToString();
pClientes.Sector = re["City"].ToString();
pClientes.Telefono = re["Phone"].ToString();
pClientes.Celular = re["Cellphone"].ToString();
list.Add(pClientes);
}
con.Close();
}
return(list);
}
public static List <facturacion> searchVentas(string cedulaVe, string cedulaC)
{
List <facturacion> list = new List <facturacion>();
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand(string.Format("SELECT ventaFacturacion.ID, services.cService, Venta.ID AS CasoID, VENDEDOR.name, VENDEDOR.lastname, VENDEDOR.idcard, ventaFacturacion.TotalPago, ventaFacturacion.Abono FROM Venta INNER JOIN ventaFacturacion ON Venta.ID = ventaFacturacion.caseID INNER JOIN customers AS VENDEDOR ON VENDEDOR.ID = Venta.VendedorID INNER JOIN services ON services.ID = ventaFacturacion.serviceID INNER JOIN customers AS COMPRADOR ON COMPRADOR.ID = Venta.CompradorID " +
" WHERE VENDEDOR.idcard LIKE '{0}%' AND COMPRADOR.idcard LIKE '{1}%'", cedulaVe, cedulaC), con);
SqlDataReader reader = comand.ExecuteReader();
while (reader.Read())
{
facturacion pFactura = new facturacion();
pFactura.ID = reader["ID"].ToString();
pFactura.Abono = Convert.ToDouble(reader["Abono"]).ToString("f2");
pFactura.CasoNO = reader["CasoID"].ToString();
pFactura.Apellido_Cliente = reader["lastname"].ToString();
pFactura.Cedula = reader["idcard"].ToString();
pFactura.Nombre_Cliente = reader["name"].ToString();
pFactura.Pago_Total = Convert.ToDouble(reader["TotalPago"]).ToString("f2");
pFactura.Servicio = reader["cService"].ToString();
pFactura.Deposito = "No hay deposito registrado";
list.Add(pFactura);
}
con.Close();
}
return(list);
}
public static facturacionBaseObj getVentasStatus(string ID)
{
facturacionBaseObj pfactura = new facturacionBaseObj();
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand(string.Format("SELECT * FROM ventaFacturacion WHERE ID = '{0}'", ID), con);
SqlDataReader re = comand.ExecuteReader();
if (re.HasRows)
{
while (re.Read())
{
pfactura.ID = re["ID"].ToString();
pfactura.Cliente_ID = re["clienteID"].ToString();
pfactura.Service_ID = re["serviceID"].ToString();
pfactura.Case_ID = re["caseID"].ToString();
pfactura.TotalPago_Mensualidad = Convert.ToDouble(re["TotalPago"]).ToString("f2");
pfactura.Abono_Deposito = Convert.ToDouble(re["Abono"]).ToString("f2");
pfactura.fechaUltimoPago = re["ultimoPago"].ToString();
}
}
else
{
pfactura = null;
}
con.Close();
}
return(pfactura);
}
public static DataTable reporteAbonoVenta(string fechaDes, string fechaHas)
{
DataTable dt = new DataTable();
using (SqlConnection con = DBcomun.getConnection())
{
SqlDataAdapter da = new SqlDataAdapter(string.Format("Select Venta.ID as NumeroCaso, VENDEDOR.name as NombreVendedor, VENDEDOR.lastname as ApellidoVendedor, facturasVenta.ID as CodigoFactura, facturasVenta.FechaPago, facturasVenta.TotalBalance as BalanceTotal, facturasVenta.Pago, facturasVenta.NuevoBalance, facturasVenta.Detalles from Venta INNER join customers as VENDEDOR on VENDEDOR.ID = Venta.VendedorID inner join customers as COMPRADOR on COMPRADOR.ID = Venta.CompradorID inner join ventaFacturacion on ventaFacturacion.caseID = Venta.ID inner join facturasVenta on facturasVenta.casoID = Venta.ID where ventaFacturacion.TotalPago > 0 and facturasVenta.FechaPago between '{0}' and '{1}'", fechaDes, fechaHas), con);
da.Fill(dt);
con.Close();
}
return(dt);
}
// method for getting service ID.
public static string getServiceID(string Servicio)
{
string r = null;
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand(string.Format("SELECT ID FROM services WHERE cService = '{0}'", Servicio), con);
r = comand.ExecuteScalar().ToString();
con.Close();
}
return(r);
}
// methods for updating payments status
public static int updateRentStatus(string ID, string Depositorestante, string fechaPago, string contratoCant, string fechaProximoPago)
{
int r = -1;
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand(string.Format("UPDATE rentFacturacion SET Deposito = '{0}', UltimoPago = '{1}', contratoCant = '{2}', ProximoPago = '{3}' WHERE ID = '{4}'", Depositorestante, fechaPago, contratoCant, fechaProximoPago, ID), con);
r = comand.ExecuteNonQuery();
con.Close();
}
return(r);
}
// end costruct building.
// method for registering Cities.
public static int registerCity(string City)
{
int r = -1;
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand(string.Format("INSERT INTO cities values ('{0}')", City), con);
r = comand.ExecuteNonQuery();
con.Close();
}
return(r);
}
// method for verifying the default document.
public static int verifyDocument(string ID, string cedula)
{
int r;
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand(string.Format("Select pasaporte from customers where ID = '{0}' or idcard = '{1}'", ID, cedula), con);
r = Convert.ToInt32(comand.ExecuteScalar());
con.Close();
}
return(r);
}
public static int updateVentaStatus(string ID, string pagoRestante, string abonoRestante, string ultimoPago)
{
int r = -1;
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand(string.Format("update ventaFacturacion set TotalPago = '{0}', Abono = '{1}', ultimoPago = '{2}' where ID = '{3}'", pagoRestante, abonoRestante, ultimoPago, ID), con);
r = comand.ExecuteNonQuery();
con.Close();
}
return(r);
}
public static int registerVentas(string clienteID, string serviceID, string casoID, string totalPago, string abono)
{
int r = -1;
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand(string.Format("INSERT INTO ventaFacturacion (clienteID, serviceID, caseID, TotalPago, Abono) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}')", clienteID, serviceID, casoID, totalPago, abono), con);
r = comand.ExecuteNonQuery();
con.Close();
}
return(r);
}
public static int updateDivorciosAccidenteStatus(string ID, string pagoRestante, string abonoRestante, string ultimoPago)
{
int r = -1;
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand(string.Format("UPDATE divorciosFacturacion SET TotalPago = '{0}', Abono = '{1}', ultimoPago = '{2}' WHERE ID = '{3}'", pagoRestante, abonoRestante, ultimoPago, ID), con);
r = comand.ExecuteNonQuery();
con.Close();
}
return(r);
}
// ******* end ******* //
// methods for generating reportes for Alquiler
public static DataTable reporteCasoAlquiler(string ID)
{
DataTable dt = new DataTable();
using (SqlConnection con = DBcomun.getConnection())
{
SqlDataAdapter da = new SqlDataAdapter(string.Format("Select Rent.ID as NumeroCaso, CLIENTE.name as NombreCliente, CLIENTE.lastname as ApellidoCliente, INQUILINO.name as NombreInquilino, INQUILINO.lastname as ApellidoInquilino, GARANTE.name as NombreGarante, GARANTE.lastname as ApellidoGarante, Rent.detalles, Rent.direccion, Rent.ContratoCant as TotalContrato, Rent.Mensualidad, rentFacturacion.ContratoCant as BalanceActualContr from Rent INNER join customers as CLIENTE on CLIENTE.ID = Rent.CLienteID inner join customers AS INQUILINO on INQUILINO.ID = Rent.InquilinoID inner join customers as GARANTE on GARANTE.ID = Rent.GaranteID INNER join rentFacturacion on rentFacturacion.caseID = Rent.ID where Rent.ID = '{0}'", ID), con);
da.Fill(dt);
con.Close();
}
return(dt);
}
// end of properties building
// methods for registering facturaciones
public static int registerRent(string clienteID, string serviceID, string casoID, string mensualidad, string deposito, string TotalContrato)
{
int r = -1;
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand(string.Format("INSERT INTO rentFacturacion (clienteID, serviceID, caseID, Mensualidad, Deposito, ContratoCant) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}')", clienteID, serviceID, casoID, mensualidad, deposito, TotalContrato), con);
r = comand.ExecuteNonQuery();
con.Close();
}
return(r);
}
public static DataTable reporteAbonoCasoDivorcio(string fechaDes, string fechaHast)
{
DataTable dt = new DataTable();
using (SqlConnection con = DBcomun.getConnection())
{
SqlDataAdapter da = new SqlDataAdapter(string.Format("SELECT divorcios.ID as NumeroCaso, services.cService as Servicio, DEMANDANTE.name as NombreDemandante, DEMANDANTE.lastname as ApellidoDemandante, DEMANDANTE.Cellphone as CellDemandante, DEMANDANTE.Phone as PhoDemandante, DEMANDADO.name as NombreDemandado, DEMANDADO.lastname as ApellidoDemandado, DEMANDADO.Cellphone as CellDemandado, DEMANDADO.Phone as PhoDemandado, divorciosFacturacion.TotalPago AS BalanceActual, divorciosFacturacion.ultimoPago as FechaUltimoPago, facturasDivorciosAccidente.ID as CodigoFactura, facturasDivorciosAccidente.FechaPago, facturasDivorciosAccidente.TotalBalance, facturasDivorciosAccidente.Pago, facturasDivorciosAccidente.NuevoBalance, facturasDivorciosAccidente.Detalles FROM divorcios INNER JOIN customers AS DEMANDADO ON DEMANDADO.ID = divorcios.DemandadoID INNER JOIN customers AS DEMANDANTE ON DEMANDANTE.ID = divorcios.DemandanteID INNER JOIN services ON services.ID = divorcios.ServiceID INNER JOIN facturasDivorciosAccidente ON facturasDivorciosAccidente.casoID = divorcios.ID INNER JOIN divorciosFacturacion ON divorciosFacturacion.caseID = divorcios.ID where divorciosFacturacion.TotalPago > 0 and facturasDivorciosAccidente.FechaPago between '{0}' and '{1}'", fechaDes, fechaHast), con);
da.Fill(dt);
con.Close();
}
return(dt);
}
public static string getFacturaIDVenta(string Date, string casoID, string Detalles, string clienteID)
{
string r = null;
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand(string.Format("SELECT ID FROM facturasVenta WHERE FechaPago = '{0}' AND casoID = '{1}' AND Detalles = '{2}' AND clienteID = '{3}'", Date, casoID, Detalles, clienteID), con);
r = comand.ExecuteScalar().ToString();
con.Close();
}
return(r);
}
// ******* end ******* //
// methos for getting ventas report
public static DataTable reporteCasoVenta(string ID)
{
DataTable dt = new DataTable();
using (SqlConnection con = DBcomun.getConnection())
{
SqlDataAdapter da = new SqlDataAdapter(string.Format("Select Venta.ID as NumeroCaso, VENDEDOR.name as NombreVendedor, VENDEDOR.lastname as ApellidoVendedor, COMPRADOR.name as NombreComprador, COMPRADOR.lastname as ApellidoComprador, Venta.MontoVenta as TotalVenta, ventaFacturacion.TotalPago as BalanceActual, Venta.Descripcion as Detalles from Venta INNER join customers as VENDEDOR on VENDEDOR.ID = Venta.VendedorID inner join customers as COMPRADOR on COMPRADOR.ID = Venta.CompradorID inner join ventaFacturacion on ventaFacturacion.caseID = Venta.ID where Venta.ID = '{0}'", ID), con);
da.Fill(dt);
con.Close();
}
return(dt);
}
private void Form1_Load(object sender, EventArgs e)
{
try
{
DBcomun.getConnection();
timer1.Start();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public static int registerFacturaVenta(string clienteID, string servicioID, string casoID, string TotalBalance, string pago, string nuevoBalance, string fechaPago, string Detalles)
{
int r = -1;
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand(string.Format("INSERT INTO facturasVenta (clienteID, servicioID, casoID, TotalBalance, Pago, NuevoBalance, fechaPago, Detalles) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}')", clienteID, servicioID, casoID, TotalBalance, pago, nuevoBalance, fechaPago, Detalles), con);
r = comand.ExecuteNonQuery();
con.Close();
}
return(r);
}