// method for listing all Cities.
private void listCities()
{
cbCities.Items.Clear();
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand("SELECT City FROM Cities", con);
SqlDataReader re = comand.ExecuteReader();
while (re.Read())
{
cbCities.Items.Add(re["City"]);
}
con.Close();
}
}
// getting user object from database
public static usuarios getUser(string id)
{
usuarios u = new usuarios();
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand(string.Format("SELECT users.ID, users.name, users.passwrd, users.picture, UserLevel.levels FROM users INNER JOIN UserLevel ON users.userLevelID = UserLevel.id WHERE users.ID = '{0}'", id), con);
SqlDataReader re = comand.ExecuteReader();
while (re.Read())
{
u.id = re["ID"].ToString();
u.nombre = re["name"].ToString();
u.nivel = re["levels"].ToString();
u.image = re["picture"].ToString();
}
con.Close();
}
return(u);
}
// method for getting service info from database.
public static servicios getServiceInfo(string ID)
{
servicios pServicios = new servicios();
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand(string.Format("SELECT * FROM services WHERE ID = {0}", ID), con);
SqlDataReader re = comand.ExecuteReader();
while (re.Read())
{
pServicios.ID = re["ID"].ToString();
pServicios.Servicio = re["cService"].ToString();
pServicios.Descripcion = re["explanation"].ToString();
pServicios.Precio = re["price"].ToString();
pServicios.Abono = re["pre_bono"].ToString();
}
con.Close();
}
return(pServicios);
}
// end construct //
/// <summary>
/// end constructs estructure
/// </summary>
/// <param name="pU"></param>
/// <returns></returns>
///
//method for login interface.
public static bool checkUser(string name, string passwrd)
{
bool r = false;
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand(string.Format("select * from users where name = '{0}' and passwrd = '{1}'", name, passwrd), con);
SqlDataReader reader = comand.ExecuteReader();
if (reader.HasRows)
{
r = true;
}
else
{
r = false;
}
con.Close();
}
return(r);
}
// method for validating customers.
public static bool verify(string pCedula)
{
bool r;
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand(string.Format("SELECT * FROM customers WHERE idcard = '{0}'", pCedula), con);
SqlDataReader re = comand.ExecuteReader();
if (re.HasRows)
{
r = false;
}
else
{
r = true;
}
con.Close();
}
return(r);
}
// method for verifiing Cities input.
public static bool cityVerification(string City)
{
bool r = false;
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand(string.Format("SELECT * FROM cities WHERE City = '{0}'", City), con);
SqlDataReader re = comand.ExecuteReader();
if (re.HasRows)
{
r = true;
}
else
{
r = false;
}
con.Close();
}
return(r);
}
public static string getUserLevelID(string UserLevel)
{
string r = null;
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand(string.Format("SELECT id FROM userLevel WHERE levels = '{0}'", UserLevel), con);
SqlDataReader re = comand.ExecuteReader();
if (re.HasRows)
{
re.Close();
r = comand.ExecuteScalar().ToString();
}
else
{
r = null;
}
con.Close();
}
return(r);
}
// method for getting status ID.
public static string getStatusID(String Status)
{
string r = null;
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand(string.Format("SELECT ID FROM customerStatus where Status = '{0}'", Status), con);
SqlDataReader re = comand.ExecuteReader();
if (re.HasRows)
{
re.Close();
r = comand.ExecuteScalar().ToString();
}
else
{
r = null;
}
con.Close();
}
return(r);
}
// method which loads all users.
public static List <usuarios> listAllUsers()
{
List <usuarios> list = new List <usuarios>();
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand("SELECT users.ID, users.name, users.passwrd, users.picture, UserLevel.levels FROM users INNER JOIN UserLevel ON users.userLevelID = UserLevel.id", con);
SqlDataReader re = comand.ExecuteReader();
while (re.Read())
{
usuarios u = new usuarios();
u.id = re["ID"].ToString();
u.nombre = re["name"].ToString();
u.nivel = re["levels"].ToString();
u.image = re["picture"].ToString();
list.Add(u);
}
con.Close();
}
return(list);
}
// method for getting divorcios.
public static pBaseDivorcioAccidenteFacturacion getDivorciosAccidente(string ID)
{
pBaseDivorcioAccidenteFacturacion pBase = new pBaseDivorcioAccidenteFacturacion();
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand(string.Format("SELECT * FROM divorcios WHERE ID = '{0}'", ID), con);
SqlDataReader re = comand.ExecuteReader();
while (re.Read())
{
pBase.ID = re["ID"].ToString();
pBase.DemandanteID = re["DemandanteID"].ToString();
pBase.DemandadoID = re["DemandadoID"].ToString();
pBase.serviceID = re["serviceID"].ToString();
pBase.Pagos = Convert.ToDouble(re["Pagos"]).ToString("f2");
pBase.Honorarios = Convert.ToDouble(re["Honorarios"]).ToString("f2");
pBase.Abono = Convert.ToDouble(re["Abono"]).ToString("f2");
}
con.Close();
}
return(pBase);
}
// method for getting Cities ID.
public static string getCityID(string City)
{
string r = null;
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand(string.Format("SELECT ID FROM Cities WHERE City = '{0}'", City), con);
SqlDataReader re = comand.ExecuteReader();
if (re.HasRows)
{
re.Close();
r = comand.ExecuteScalar().ToString();
}
else
{
r = null;
re.Close();
}
con.Close();
}
return(r);
}
// method for getting ID.
public static string getID(string Abono, string pNombreDemandante, string ApellidoDemandante, string NombreDemandado, string ApellidoDemandado, string cedulaDemandante, string cedulaDemandado)
{
string r = null;
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand(string.Format("SELECT divorcios.ID FROM divorcios INNER JOIN customers AS DEMANDANTE ON DEMANDANTE.ID = divorcios.DemandanteID INNER JOIN customers AS DEMANDADO ON DEMANDADO.ID = divorcios.DemandadoID " +
" WHERE DEMANDANTE.name = '{0}' AND DEMANDANTE.lastname = '{1}' AND DEMANDANTE.idcard = '{2}' AND DEMANDADO.name = '{3}' AND DEMANDADO.lastname = '{4}' AND DEMANDADO.idcard = '{5}' AND divorcios.Abono = '{6}'",
pNombreDemandante, ApellidoDemandante, cedulaDemandante, NombreDemandado, ApellidoDemandado, cedulaDemandado, Abono), con);
SqlDataReader re = comand.ExecuteReader();
if (re.HasRows)
{
re.Close();
r = comand.ExecuteScalar().ToString();
}
else
{
r = null;
}
con.Close();
}
return(r);
}
// method for getting ID.
public static string getID(string NombreIn, string ApellidoIn, string Nombrecl, string Apellidocl, string Nombrega, string Apellidoga, string cedulaIn, string cedulaCli, string cedulaGa)
{
string r = null;
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand(string.Format("Select * 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 " +
" WHERE CLIENTE.name = '{0}' AND CLIENTE.lastname = '{1}' AND CLIENTE.idcard = '{2}' AND GARANTE.name = '{3}' AND GARANTE.lastname = '{4}' AND GARANTE.idcard = '{5}' AND INQUILINO.name = '{6}' AND INQUILINO.lastname = '{7}' AND INQUILINO.idcard = '{8}'",
Nombrecl, Apellidocl, cedulaCli, Nombrega, Apellidoga, cedulaGa, NombreIn, ApellidoIn, cedulaIn), con);
SqlDataReader re = comand.ExecuteReader();
if (re.HasRows)
{
re.Close();
r = comand.ExecuteScalar().ToString();
}
else
{
r = null;
}
con.Close();
}
return(r);
}
// method for getting venta
public static pBaseVentaFacturacion getVenta(string ID)
{
pBaseVentaFacturacion pBase = new pBaseVentaFacturacion();
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand(string.Format("SELECT * FROM Venta WHERE ID = '{0}'", ID), con);
SqlDataReader re = comand.ExecuteReader();
while (re.Read())
{
pBase.ID = re["ID"].ToString();
pBase.vendedorID = re["VendedorID"].ToString();
pBase.compradorID = re["CompradorID"].ToString();
pBase.Descripcion = re["Descripcion"].ToString();
pBase.MontoVenta = Convert.ToDouble(re["MontoVenta"]).ToString("f2");
pBase.Honorarios = Convert.ToDouble(re["Honorarios"]).ToString("f2");
pBase.Abono = Convert.ToDouble(re["Abono"]).ToString("f2");
pBase.servicioID = re["serviceID"].ToString();
}
con.Close();
}
return(pBase);
}
// method for getting customers.
public static string getID(string name, string idcard)
{
string r = null;
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand(string.Format("SELECT * FROM customers WHERE name = '{0}' AND idcard = '{1}'", name, idcard), con);
SqlDataReader re = comand.ExecuteReader();
if (re.HasRows)
{
while (re.Read())
{
r = re["ID"].ToString();
}
}
else
{
r = null;
}
con.Close();
}
return(r);
}
// search engine for all users.
public static List <usuarios> SearchEngineUsers(string id, string name, string level, string passwrd)
{
List <usuarios> list = new List <usuarios>();
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand(string.Format("SELECT users.ID, users.name, users.passwrd, users.picture, UserLevel.levels FROM users INNER JOIN UserLevel ON users.userLevelID = UserLevel.id where users.ID like '{0}%' and name like '{1}%' and passwrd like '{2}%' and levels like '{3}%'",
id, name, passwrd, level), con);
SqlDataReader re = comand.ExecuteReader();
while (re.Read())
{
usuarios u = new usuarios();
u.id = re["ID"].ToString();
u.nombre = re["name"].ToString();
u.nivel = re["levels"].ToString();
u.image = re["picture"].ToString();
list.Add(u);
}
con.Close();
}
return(list);
}
// method for listing all services.
public static List <servicios> listAllServices()
{
List <servicios> list = new List <servicios>();
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand("SELECT * FROM services", con);
SqlDataReader re = comand.ExecuteReader();
while (re.Read())
{
servicios pServicios = new servicios();
pServicios.ID = re["ID"].ToString();
pServicios.Servicio = re["cService"].ToString();
pServicios.Descripcion = re["explanation"].ToString();
pServicios.Precio = re["price"].ToString();
pServicios.Abono = re["pre_bono"].ToString();
list.Add(pServicios);
}
con.Close();
}
return(list);
}
// method for getting ID.
public static string getID(string NombreV, string ApellidoV, string NombreC, string ApellidoC, string cedulaV, string cedulaC)
{
string r = null;
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand(string.Format("SELECT Venta.ID 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 = '{0}' AND VENDEDOR.lastname = '{1}' AND VENDEDOR.idcard = '{2}' AND COMPRADOR.name = '{3}' AND COMPRADOR.lastname = '{4}' AND COMPRADOR.idcard = '{5}'",
NombreV, ApellidoV, cedulaV, NombreC, ApellidoC, cedulaC), con);
SqlDataReader re = comand.ExecuteReader();
if (re.HasRows)
{
re.Close();
r = comand.ExecuteScalar().ToString();
}
else
{
r = null;
}
con.Close();
}
return(r);
}
// search engine for services
public static List <servicios> searchServices(string id, string service, string description, string price, string pre_bono)
{
List <servicios> list = new List <servicios>();
using (SqlConnection con = DBcomun.getConnection())
{
SqlCommand comand = new SqlCommand(string.Format("select * from services where ID like '{0}%' and cService like '{1}%' and explanation like '{2}%' and price like '{3}%' and pre_bono like '{4}%'",
id, service, description, price, pre_bono), con);
SqlDataReader re = comand.ExecuteReader();
while (re.Read())
{
servicios pServicios = new servicios();
pServicios.ID = re["ID"].ToString();
pServicios.Servicio = re["cService"].ToString();
pServicios.Descripcion = re["explanation"].ToString();
pServicios.Precio = re["price"].ToString();
pServicios.Abono = re["pre_bono"].ToString();
list.Add(pServicios);
}
con.Close();
}
return(list);
}
// method for getting customers data.
public static clientes getCustomerObject(string ID, string idCard)
{
clientes pClientes = new 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 = '{0}' OR customers.idcard = '{1}'", ID, idCard), con);
SqlDataReader re = comand.ExecuteReader();
if (re.HasRows)
{
while (re.Read())
{
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();
}
}
else
{
pClientes = null;
}
con.Close();
}
return(pClientes);
}