public static Proveedor obtenerProveedor(String cuit)
{
string connString = ConfigurationManager.ConnectionStrings["THE_RIGHT_JOIN"].ConnectionString;
SqlConnection conn = new SqlConnection(connString);
String query = "select * from THE_RIGHT_JOIN.obtenerUnProveedor(@cuit)";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.Add("@cuit", SqlDbType.VarChar).Value = cuit;
DataSet ds = ConectorBDD.cargarDataSet(conn, cmd);
String RS = ds.Tables[0].Rows[0]["Provee_RS"].ToString();
String email = ds.Tables[0].Rows[0]["Provee_email"].ToString();
Decimal telefono = Convert.ToDecimal(ds.Tables[0].Rows[0]["Provee_Telefono"]);
String direccion = ds.Tables[0].Rows[0]["Provee_Dom"].ToString();
String ciudad = ds.Tables[0].Rows[0]["Provee_Ciudad"].ToString();
String rubro = ds.Tables[0].Rows[0]["Rubro_Descripcion"].ToString();
int idRubro = Convert.ToInt32(ds.Tables[0].Rows[0]["Provee_Rubro"]);
String contacto = ds.Tables[0].Rows[0]["Provee_contacto"].ToString();
String postal = ds.Tables[0].Rows[0]["Provee_postal"].ToString();
Proveedor provee = new Proveedor(RS, email, telefono, direccion, ciudad, cuit, rubro, idRubro, contacto, postal);
return(provee);
}
public static DataSet obtenerRubros()
{
string connString = ConfigurationManager.ConnectionStrings["THE_RIGHT_JOIN"].ConnectionString;
SqlConnection conn = new SqlConnection(connString);
String query = "SELECT [idRubro],[Rubro_Descripcion] FROM THE_RIGHT_JOIN.Rubro";
SqlCommand cmd = new SqlCommand(query, conn);
return(ConectorBDD.cargarDataSet(conn, cmd));
}
public static DataSet obtenerUsuarios()
{
string connString = ConfigurationManager.ConnectionStrings["THE_RIGHT_JOIN"].ConnectionString;
SqlConnection conn = new SqlConnection(connString);
String query = "SELECT Usuari_idUser, Usuari_Username FROM THE_RIGHT_JOIN.Usuario WHERE Usuari_Habilitado = 1";
SqlCommand cmd = new SqlCommand(query, conn);
return(ConectorBDD.cargarDataSet(conn, cmd));
}
public static DataSet cargarComboBoxRoles()
{
string connString = ConfigurationManager.ConnectionStrings["THE_RIGHT_JOIN"].ConnectionString;
SqlConnection conn = new SqlConnection(connString);
String query = "SELECT [id_Rol],[rol_Name],[rol_habilitado] FROM [GD2C2019].[THE_RIGHT_JOIN].[Rol] WHERE [rol_Name] <> 'ADMIN' and [rol_habilitado] = 1";
SqlCommand cmd = new SqlCommand(query, conn);
return(ConectorBDD.cargarDataSet(conn, cmd));
}
public static DataSet obtenerProveedoresRS()
{
string connString = ConfigurationManager.ConnectionStrings["THE_RIGHT_JOIN"].ConnectionString;
SqlConnection conn = new SqlConnection(connString);
String query = "SELECT TOP 1000 [Provee_CUIT],[Provee_RS] FROM THE_RIGHT_JOIN.Proveedor";
SqlCommand cmd = new SqlCommand(query, conn);
return(ConectorBDD.cargarDataSet(conn, cmd));
}
public static DataSet obtenerProveedores()
{
string connString = ConfigurationManager.ConnectionStrings["THE_RIGHT_JOIN"].ConnectionString;
SqlConnection conn = new SqlConnection(connString);
String query = "SELECT TOP 1000 [Provee_CUIT],[Provee_RS],[Rubro_Descripcion],[Provee_Dom],[Provee_Ciudad],[Provee_postal],[Provee_Telefono],[Provee_contacto],[Provee_email] FROM [GD2C2019].[THE_RIGHT_JOIN].[Proveedor] JOIN [GD2C2019].[THE_RIGHT_JOIN].[Rubro] ON ([Provee_Rubro] = [idRubro])";
SqlCommand cmd = new SqlCommand(query, conn);
return(ConectorBDD.cargarDataSet(conn, cmd));
}
public static DataSet obtenerFuncxRol(int idRol)
{
string connString = ConfigurationManager.ConnectionStrings["THE_RIGHT_JOIN"].ConnectionString;
SqlConnection conn = new SqlConnection(connString);
String query = "SELECT * FROM THE_RIGHT_JOIN.traerFuncDelRol(@rol)";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.Add("@rol", SqlDbType.Int).Value = idRol;
return(ConectorBDD.cargarDataSet(conn, cmd));
}
public static DataSet obtenerRolesPorUsuario(string username)
{
string connString = ConfigurationManager.ConnectionStrings["THE_RIGHT_JOIN"].ConnectionString;
SqlConnection conn = new SqlConnection(connString);
String query = "SELECT * FROM [GD2C2019].[THE_RIGHT_JOIN].[traerRolxUsuario](@username)";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = username;
return(ConectorBDD.cargarDataSet(conn, cmd));
}
public static DataSet obtenerCuponesXCliente(int dni)
{
string connString = ConfigurationManager.ConnectionStrings["THE_RIGHT_JOIN"].ConnectionString;
SqlConnection conn = new SqlConnection(connString);
String query = "select * from THE_RIGHT_JOIN.obtenerCuponesPorCliente(@dni)";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.Add("@dni", SqlDbType.Decimal).Value = Convert.ToDecimal(dni);
return(ConectorBDD.cargarDataSet(conn, cmd));
}
public static DataSet obtenerOfertasDisponibles()
{
string connString = ConfigurationManager.ConnectionStrings["THE_RIGHT_JOIN"].ConnectionString;
SqlConnection conn = new SqlConnection(connString);
String query = "select * from THE_RIGHT_JOIN.traerOfertasDisponibles(@fecha)";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.Add("@fecha", SqlDbType.DateTime).Value = Convert.ToDateTime(ConfigurationManager.AppSettings["Fecha"]);
return(ConectorBDD.cargarDataSet(conn, cmd));
}
public static DataSet obtenerRoles(String nombre)
{
string connString = ConfigurationManager.ConnectionStrings["THE_RIGHT_JOIN"].ConnectionString;
SqlConnection conn = new SqlConnection(connString);
String query = "SELECT [id_Rol],[rol_Name],[rol_habilitado] FROM [GD2C2019].[THE_RIGHT_JOIN].[Rol] WHERE rol_name LIKE '%' + @nombre + '%' AND rol_habilitado = 1";
SqlCommand cmd = new SqlCommand(query, conn);
SqlParameter paramNombre = cmd.Parameters.Add("@nombre", SqlDbType.VarChar);
paramNombre.Value = nombre;
return(ConectorBDD.cargarDataSet(conn, cmd));
}
private void ListadoEstadistico_Load(object sender, EventArgs e)
{
string connString = ConfigurationManager.ConnectionStrings["THE_RIGHT_JOIN"].ConnectionString;
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand("select distinct year(Factura_fecha) as Anio from THE_RIGHT_JOIN.Factura", conn);
cmbAnio.DataSource = ConectorBDD.cargarDataSet(conn, cmd).Tables[0];
cmbAnio.DisplayMember = "Anio";
this.Controls.Add(Form1.MainMenu);
}
public static DataSet obtenerClientesNyA()
{
string connString = ConfigurationManager.ConnectionStrings["THE_RIGHT_JOIN"].ConnectionString;
SqlConnection conn = new SqlConnection(connString);
String query = "SELECT TOP 1000 [Cli_Dni],[Cli_Nombre],[Cli_Apellido] FROM [GD2C2019].[THE_RIGHT_JOIN].[Cliente]";
query += "WHERE Cli_Activo IS NULL OR Cli_Activo = 1";
SqlCommand cmd = new SqlCommand(query, conn);
return(ConectorBDD.cargarDataSet(conn, cmd));
}
public static Decimal obtenerDniDelUsuario(string username)
{
string connString = ConfigurationManager.ConnectionStrings["THE_RIGHT_JOIN"].ConnectionString;
SqlConnection conn = new SqlConnection(connString);
String query = "SELECT Cli_Dni from THE_RIGHT_JOIN.Usuario JOIN THE_RIGHT_JOIN.Cliente ON (Usuari_DNI = Cli_Dni) where Usuari_Username = @username";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = username;
DataSet ds = ConectorBDD.cargarDataSet(conn, cmd);
return(Convert.ToDecimal(ds.Tables[0].Rows[0]["Cli_Dni"]));
}
public static DataSet clientesDelProv(String cuit)
{
string connString = ConfigurationManager.ConnectionStrings["THE_RIGHT_JOIN"].ConnectionString;
SqlConnection conn = new SqlConnection(connString);
String query = "SELECT * FROM THE_RIGHT_JOIN.clientesDelProv(@cuit)";
SqlCommand cmd = new SqlCommand(query, conn);
SqlParameter paramCuit = cmd.Parameters.Add("@cuit", SqlDbType.NVarChar);
paramCuit.Value = cuit;
return(ConectorBDD.cargarDataSet(conn, cmd));
}
public static String obtenerCuitDelUsuario(string username)
{
string connString = ConfigurationManager.ConnectionStrings["THE_RIGHT_JOIN"].ConnectionString;
SqlConnection conn = new SqlConnection(connString);
String query = "SELECT Provee_CUIT from THE_RIGHT_JOIN.Usuario JOIN THE_RIGHT_JOIN.Proveedor ON (Usuari_CUIT = Provee_CUIT) where Usuari_Username = @username";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = username;
DataSet ds = ConectorBDD.cargarDataSet(conn, cmd);
return(ds.Tables[0].Rows[0]["Provee_CUIT"].ToString());
}
public static DataSet obtenerOfertasPorCliente(String cuit, DateTime fechamin, DateTime fechamax, DateTime fechaActual)
{
string connString = ConfigurationManager.ConnectionStrings["THE_RIGHT_JOIN"].ConnectionString;
SqlConnection conn = new SqlConnection(connString);
String query = "select * from THE_RIGHT_JOIN.ofertasAdquiridasPorCliente(@cuit, @fechaMin, @fechaMax, @fechaAct)";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.Add("@cuit", SqlDbType.VarChar).Value = cuit;
cmd.Parameters.Add("@fechaMin", SqlDbType.Date).Value = fechamin;
cmd.Parameters.Add("@fechaMax", SqlDbType.Date).Value = fechamax;
cmd.Parameters.Add("@fechaAct", SqlDbType.Date).Value = fechaActual;
return(ConectorBDD.cargarDataSet(conn, cmd));
}
private void btnVerEst_Click(object sender, EventArgs e)
{
string connString = ConfigurationManager.ConnectionStrings["THE_RIGHT_JOIN"].ConnectionString;
SqlConnection conn = new SqlConnection(connString);
String query = "";
if (cmbAnio.Text != "" && cmbTipoEst.Text != "")
{
DateTime fecha = generarFecha(cmbAnio.Text, rdbSem1.Checked);
if (cmbTipoEst.Text == "Proveedores con mayor facturacion")
{
query = "SELECT * FROM [GD2C2019].[THE_RIGHT_JOIN].[provMaxFact]('" + fecha.ToString() + "')";
}
else
{
query = "SELECT * FROM [GD2C2019].[THE_RIGHT_JOIN].[provMaxDesc]('" + fecha.ToString() + "')";
}
SqlCommand cmd = new SqlCommand(query, conn);
dgvEstadistica.DataSource = ConectorBDD.cargarDataSet(conn, cmd).Tables[0];
}
}
public static Cliente obtenerCliente(Decimal dniCliente)
{
string connString = ConfigurationManager.ConnectionStrings["THE_RIGHT_JOIN"].ConnectionString;
SqlConnection conn = new SqlConnection(connString);
String query = "select * from THE_RIGHT_JOIN.Cliente where Cli_Dni = @dni";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.Add("@dni", SqlDbType.Decimal).Value = dniCliente;
DataSet ds = ConectorBDD.cargarDataSet(conn, cmd);
String nombre = ds.Tables[0].Rows[0]["Cli_Nombre"].ToString();
String apellido = ds.Tables[0].Rows[0]["Cli_Apellido"].ToString();
String mail = ds.Tables[0].Rows[0]["Cli_Mail"].ToString();
String direccion = ds.Tables[0].Rows[0]["Cli_Direccion"].ToString();
String ciudad = ds.Tables[0].Rows[0]["Cli_Ciudad"].ToString();
DateTime fechaNac = Convert.ToDateTime(ds.Tables[0].Rows[0]["Cli_Fecha_Nac"]);
Decimal telefono = Convert.ToDecimal(ds.Tables[0].Rows[0]["Cli_Telefono"]);
String codPostal = ds.Tables[0].Rows[0]["Cli_CodPostal"].ToString();
String localidad = ds.Tables[0].Rows[0]["Cli_Localidad"].ToString();
Cliente cli = new Cliente(dniCliente, nombre, apellido, mail, direccion, ciudad, fechaNac, telefono, codPostal, localidad);
return(cli);
}
public static DataSet generarQuerys(String dni, String nombre, String apellido, String mail)
{
string connString = ConfigurationManager.ConnectionStrings["THE_RIGHT_JOIN"].ConnectionString;
SqlConnection conn = new SqlConnection(connString);
String query = "SELECT [Cli_Dni],[Cli_Nombre],[Cli_Apellido],[Cli_Mail],[Cli_Direccion],[Cli_Ciudad],[Cli_Fecha_Nac],[Cli_Telefono],[Cli_CodPostal],[Cli_Localidad],[Cli_Saldo]";
query += " FROM [GD2C2019].[THE_RIGHT_JOIN].[Cliente]";
query += " WHERE (Cli_Activo IS NULL OR Cli_Activo = 1)";
query += " AND Cli_Apellido LIKE '%' + @apellido + '%'";
query += " AND Cli_Mail LIKE '%' + @mail + '%'";
query += " AND Cli_Nombre LIKE '%' + @nombre + '%'";
if (dni == "")
{
SqlCommand cmd = new SqlCommand(query, conn);
SqlParameter paramNombre = cmd.Parameters.Add("@nombre", SqlDbType.VarChar);
paramNombre.Value = nombre;
SqlParameter paramApellido = cmd.Parameters.Add("@apellido", SqlDbType.VarChar);
paramApellido.Value = apellido;
SqlParameter paramMail = cmd.Parameters.Add("@mail", SqlDbType.VarChar);
paramMail.Value = mail;
return(ConectorBDD.cargarDataSet(conn, cmd));
}
else
{
query += " AND Cli_Dni = @dni";
SqlCommand cmd = new SqlCommand(query, conn);
SqlParameter paramNombre = cmd.Parameters.Add("@nombre", SqlDbType.VarChar);
paramNombre.Value = nombre;
SqlParameter paramApellido = cmd.Parameters.Add("@apellido", SqlDbType.VarChar);
paramApellido.Value = apellido;
SqlParameter paramMail = cmd.Parameters.Add("@mail", SqlDbType.VarChar);
paramMail.Value = mail;
SqlParameter paramDni = cmd.Parameters.Add("@dni", SqlDbType.Decimal);
paramDni.Value = Convert.ToDecimal(dni);
return(ConectorBDD.cargarDataSet(conn, cmd));
}
}
public static DataSet generarQuerys(string RS, string cuit, string email)
{
string connString = ConfigurationManager.ConnectionStrings["THE_RIGHT_JOIN"].ConnectionString;
SqlConnection conn = new SqlConnection(connString);
//--filtro por email
if (RS == "" && cuit == "")
{
String query = "select TOP 1000 [Provee_CUIT],[Provee_RS],[Rubro_Descripcion],[Provee_Dom],[Provee_Ciudad],[Provee_postal],[Provee_Telefono],[Provee_contacto],[Provee_email] from THE_RIGHT_JOIN.Proveedor JOIN THE_RIGHT_JOIN.Rubro ON (Provee_Rubro = idRubro) WHERE Provee_email LIKE '%' + @email+ '%'";
SqlCommand cmd = new SqlCommand(query, conn);
SqlParameter paramEmail = cmd.Parameters.Add("@email", SqlDbType.VarChar);
paramEmail.Value = email;
return(ConectorBDD.cargarDataSet(conn, cmd));
}
//--filtro por RS
if (email == "" && cuit == "")
{
String query = "select TOP 1000 [Provee_CUIT],[Provee_RS],[Rubro_Descripcion],[Provee_Dom],[Provee_Ciudad],[Provee_postal],[Provee_Telefono],[Provee_contacto],[Provee_email] from THE_RIGHT_JOIN.Proveedor JOIN THE_RIGHT_JOIN.Rubro ON (Provee_Rubro = idRubro) WHERE Provee_RS LIKE '%' + @RS+ '%'";
SqlCommand cmd = new SqlCommand(query, conn);
SqlParameter paramRS = cmd.Parameters.Add("@RS", SqlDbType.VarChar);
paramRS.Value = RS;
return(ConectorBDD.cargarDataSet(conn, cmd));
}
//--filtro por CUIT
if (email == "" && RS == "")
{
String query = "select TOP 1000 [Provee_CUIT],[Provee_RS],[Rubro_Descripcion],[Provee_Dom],[Provee_Ciudad],[Provee_postal],[Provee_Telefono],[Provee_contacto],[Provee_email] from THE_RIGHT_JOIN.Proveedor JOIN THE_RIGHT_JOIN.Rubro ON (Provee_Rubro = idRubro) WHERE Provee_CUIT = @cuit";
SqlCommand cmd = new SqlCommand(query, conn);
SqlParameter paramcuit = cmd.Parameters.Add("@cuit", SqlDbType.VarChar);
paramcuit.Value = cuit;
return(ConectorBDD.cargarDataSet(conn, cmd));
}
//--filtro por RS y email
if (cuit == "" && email != "" && RS != "")
{
String query = "select TOP 1000 [Provee_CUIT],[Provee_RS],[Rubro_Descripcion],[Provee_Dom],[Provee_Ciudad],[Provee_postal],[Provee_Telefono],[Provee_contacto],[Provee_email] from THE_RIGHT_JOIN.Proveedor JOIN THE_RIGHT_JOIN.Rubro ON (Provee_Rubro = idRubro) WHERE Provee_RS LIKE '%' + @RS+ '%' AND Provee_email LIKE '%' + @email+ '%'";
SqlCommand cmd = new SqlCommand(query, conn);
SqlParameter paramEmail = cmd.Parameters.Add("@email", SqlDbType.VarChar);
paramEmail.Value = email;
SqlParameter paramRS = cmd.Parameters.Add("@RS", SqlDbType.VarChar);
paramRS.Value = RS;
return(ConectorBDD.cargarDataSet(conn, cmd));
}
//--filtro RS y cuit
if (cuit != "" && email == "" && RS != "")
{
String query = "select TOP 1000 [Provee_CUIT],[Provee_RS],[Rubro_Descripcion],[Provee_Dom],[Provee_Ciudad],[Provee_postal],[Provee_Telefono],[Provee_contacto],[Provee_email] from THE_RIGHT_JOIN.Proveedor JOIN THE_RIGHT_JOIN.Rubro ON (Provee_Rubro = idRubro) WHERE Provee_CUIT = @cuit";
SqlCommand cmd = new SqlCommand(query, conn);
SqlParameter paramcuit = cmd.Parameters.Add("@cuit", SqlDbType.VarChar);
paramcuit.Value = cuit;
return(ConectorBDD.cargarDataSet(conn, cmd));
}
//--filtro por los 3
if (cuit != "" && email != "" && RS != "")
{
String query = "select TOP 1000 [Provee_CUIT],[Provee_RS],[Rubro_Descripcion],[Provee_Dom],[Provee_Ciudad],[Provee_postal],[Provee_Telefono],[Provee_contacto],[Provee_email] from THE_RIGHT_JOIN.Proveedor JOIN THE_RIGHT_JOIN.Rubro ON (Provee_Rubro = idRubro) WHERE Provee_CUIT = @cuit";
SqlCommand cmd = new SqlCommand(query, conn);
SqlParameter paramcuit = cmd.Parameters.Add("@cuit", SqlDbType.VarChar);
paramcuit.Value = cuit;
return(ConectorBDD.cargarDataSet(conn, cmd));
}
return(null);
}