// 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);
}
// 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;
}
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 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);
}
// 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);
}
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);
}
public static List<facturacion> listAllDivorciosAccidentes()
{
List<facturacion> list = new List<facturacion>();
using(SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand("SELECT divorciosFacturacion.ID, services.cService, divorcios.ID AS CasoID, customers.name, customers.lastname, customers.idcard, divorciosFacturacion.TotalPago, divorciosFacturacion.Abono FROM divorciosFacturacion INNER JOIN divorcios ON divorcios.ID = divorciosFacturacion.CaseID INNER JOIN customers ON customers.ID = divorciosFacturacion.clienteID INNER JOIN services ON services.ID = divorciosFacturacion.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;
}
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 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;
}
// 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;
}