//Lista de clientes por ID
public List <Customers> obtenerClientePorID(string id)
{
if (conexion.Conectar() == "True")
{
List <Customers> listaCustomer = new List <Customers>();
Customers customer;
using (SqlCommand comando = new SqlCommand("SELECT CustomerID, CompanyName, ContactName, City, Phone FROM Customers WHERE CustomerID LIKE '%" + id + "%' AND b_logiv = 0", conexion.getConn()))
{
using (IDataReader reader = comando.ExecuteReader())
{
while (reader.Read())
{
customer = new Customers();
customer.CustomerID = reader.IsDBNull(0) ? "" : reader.GetValue(0).ToString();
customer.CompanyName = reader.IsDBNull(1) ? "" : reader.GetValue(1).ToString();
customer.ContactName = reader.IsDBNull(2) ? "" : reader.GetValue(2).ToString();
customer.City = reader.IsDBNull(3) ? "" : reader.GetValue(3).ToString();
customer.Phone = reader.IsDBNull(4) ? "" : reader.GetValue(4).ToString();
listaCustomer.Add(customer);
}
}
}
return(listaCustomer);
}
else
{
return(null);
}
}
public string AgregarAnio(string DESCRIPCION)
{
cn = cnx.Conectar();
string mensaje = string.Empty;
SqlTransaction tr = null;
SqlCommand cmd = new SqlCommand("USP_ANIO_INSERTAR", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@DESCRIPCION", DESCRIPCION);
int q = 0;
try
{
cn.Open();
tr = cn.BeginTransaction(IsolationLevel.Serializable);
cmd.Transaction = tr;
q = cmd.ExecuteNonQuery();
tr.Commit();
mensaje = "Registro creado";
}
catch (Exception ex)
{
tr.Rollback();
mensaje = ex.Message;
}
finally
{
cn.Close();
}
return(mensaje);
}
public string RegistrarAlumno(string dni, string nombre, string apepat, string apetmat, byte[] foto)
{
cn = cnx.Conectar();
string mensaje = string.Empty;
SqlTransaction tr = null;
SqlCommand cmd = new SqlCommand("sp_registraralumno", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@dni", dni);
cmd.Parameters.AddWithValue("@nombre", nombre);
cmd.Parameters.AddWithValue("@apepat", apepat);
cmd.Parameters.AddWithValue("@apetmat", apetmat);
cmd.Parameters.AddWithValue("@foto", foto);
int q = 0;
try
{
cn.Open();
tr = cn.BeginTransaction(IsolationLevel.Serializable);
cmd.Transaction = tr;
q = cmd.ExecuteNonQuery();
tr.Commit();
mensaje = "Registro creado";
}
catch (Exception ex)
{
tr.Rollback();
mensaje = ex.Message;
}
finally
{
cn.Close();
}
return(mensaje);
}
//Consultar Pedidos
public List <Orders> consultarPedidos()
{
List <Orders> listaPedidos = new List <Orders>();
Orders pedido;
if (conexion.Conectar() == "True")
{
StringBuilder builder = new StringBuilder();
builder.Append(" SELECT TOP(50) od.OrderID, od.OrderDate, od.RequiredDate, od.ShippedDate, od.Freight, c.CompanyName ");
builder.Append(" FROM Orders od, Customers c WHERE estado = 0 AND od.CustomerID = c.CustomerID ORDER BY OrderID DESC");
using (SqlCommand comando = new SqlCommand(builder.ToString(), conexion.getConn()))
{
using (IDataReader reader = comando.ExecuteReader())
{
while (reader.Read())
{
pedido = new Orders();
pedido.OrderID = int.Parse(reader.IsDBNull(0) ? "" : reader.GetValue(0).ToString());
pedido.OrderDate = reader.IsDBNull(1) ? new DateTime() : DateTime.Parse(reader.GetValue(1).ToString());
pedido.RequiredDate = reader.IsDBNull(2) ? new DateTime() : DateTime.Parse(reader.GetValue(2).ToString());
pedido.ShippedDate = reader.IsDBNull(3) ? new DateTime() : DateTime.Parse(reader.GetValue(3).ToString());
pedido.Freight = decimal.Parse(reader.IsDBNull(4) ? "0" : reader.GetValue(4).ToString());
pedido.CustomerCompany = reader.IsDBNull(5) ? "" : reader.GetValue(5).ToString();
listaPedidos.Add(pedido);
}
}
}
foreach (Orders order in listaPedidos)
{
StringBuilder builder2 = new StringBuilder();
builder2.Append(" SELECT od.OrderID, od.ProductID, od.UnitPrice, od.Quantity, p.ProductName FROM[Order Details] od, Products p ");
builder2.Append(" WHERE od.OrderID = @id AND od.ProductID = p.ProductID ");
using (SqlCommand comando2 = new SqlCommand(builder2.ToString(), conexion.getConn()))
{
Order_Details lineas;
order.Order_Details = new List <Order_Details>();
comando2.Parameters.AddWithValue("@id", order.OrderID);
using (IDataReader reader2 = comando2.ExecuteReader())
{
while (reader2.Read())
{
lineas = new Order_Details();
lineas.OrderID = int.Parse(reader2.IsDBNull(0) ? "" : reader2.GetValue(0).ToString());
lineas.ProductID = int.Parse(reader2.IsDBNull(1) ? "" : reader2.GetValue(1).ToString());
lineas.UnitPrice = decimal.Parse(reader2.IsDBNull(2) ? "": reader2.GetValue(2).ToString());
lineas.Quantity = short.Parse(reader2.IsDBNull(3) ? "" : reader2.GetValue(3).ToString());
lineas.ProductName = reader2.IsDBNull(4) ? "" : reader2.GetValue(4).ToString();
order.Order_Details.Add(lineas);
}
}
}
}
}
return(listaPedidos);
}
//-----------------------------------------------------------------------------
//LISTADO
public DataTable Listar()
{
cn = cnx.Conectar();
DataTable dtb = new DataTable();
SqlDataAdapter dap = new SqlDataAdapter
("USP_CARPETA_LISTAR", cn);
dap.SelectCommand.CommandType = CommandType.StoredProcedure;
dap.Fill(dtb);
return(dtb);
}
public string IngresarSujetoProcesal(string NOMBRES)
{
cn = cnx.Conectar();
string mensaje = string.Empty;
SqlTransaction tr = null;
SqlCommand cmd = new SqlCommand("USP_SUJETOPROCESAL_INGRESAR", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@NOMBRES", NOMBRES);
int q = 0;
try
{
cn.Open();
tr = cn.BeginTransaction(IsolationLevel.Serializable);
cmd.Transaction = tr;
q = cmd.ExecuteNonQuery();
tr.Commit();
mensaje = "Sujeto Procesal Ingresado";
}
catch (Exception ex)
{
tr.Rollback();
mensaje = ex.Message;
}
finally
{
cn.Close();
}
return(mensaje);
}
/*@NOMBRE VARCHAR (50)
* ,@SIGLAS VARCHAR(9)
* ,@CELULAR VARCHAR(9)
* ,@EMAIL VARCHAR(50)
* ,@CASILLAELECTRONICA VARCHAR(50)
* ,@DISTRITOFISCAL VARCHAR(50)
* ,@FISCALIA VARCHAR(50)
* ,@MODULO VARCHAR(50)
* ,@DESPACHO VARCHAR(50)
* ,@ABREVIATURA VARCHAR(50)
* ,@DIRECCIONPRO VARCHAR(300)*/
public string ActualizarFiscal(
string NOMBRE
, string SIGLAS
, string CELULAR
, string EMAIL
, string CASILLAELECTRONICA
, string DISTRITOFISCAL
, string FISCALIA
, string MODULO
, string DESPACHO
, string ABREVIATURA
, string DIRECCIONPRO
)
{
cn = cnx.Conectar();
string mensaje = string.Empty;
SqlTransaction tr = null;
SqlCommand cmd = new SqlCommand("USP_FISCAL_ACTUALIZAR", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@NOMBRE", NOMBRE);
cmd.Parameters.AddWithValue("@SIGLAS", SIGLAS);
cmd.Parameters.AddWithValue("@CELULAR", CELULAR);
cmd.Parameters.AddWithValue("@EMAIL", EMAIL);
cmd.Parameters.AddWithValue("@CASILLAELECTRONICA", CASILLAELECTRONICA);
cmd.Parameters.AddWithValue("@DISTRITOFISCAL", DISTRITOFISCAL);
cmd.Parameters.AddWithValue("@FISCALIA", FISCALIA);
cmd.Parameters.AddWithValue("@MODULO", MODULO);
cmd.Parameters.AddWithValue("@DESPACHO", DESPACHO);
cmd.Parameters.AddWithValue("@ABREVIATURA", ABREVIATURA);
cmd.Parameters.AddWithValue("@DIRECCIONPRO", DIRECCIONPRO);
int q = 0;
try
{
cn.Open();
tr = cn.BeginTransaction(IsolationLevel.Serializable);
cmd.Transaction = tr;
q = cmd.ExecuteNonQuery();
tr.Commit();
mensaje = "Datos actualizados";
}
catch (Exception ex)
{
tr.Rollback();
mensaje = ex.Message;
}
finally
{
cn.Close();
}
return(mensaje);
}
//Metodos
public string Codigo_Boleta()
{
cn = cnx.Conectar();
string correlativo = string.Empty;
SqlCommand cmd = new SqlCommand
("USP_CODIGO_BOLETA", cn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
cn.Open();
correlativo = cmd.ExecuteScalar().ToString();
}
finally
{
cn.Close();
}
return(correlativo);
}
//Metodos
public string CodigoRequerimiento(string IDCARPETA)
{
cn = cnx.Conectar();
string codigo = string.Empty;
SqlCommand cmd = new SqlCommand
("USP_REQUERIMIENTO_CODIGO", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@IDCARPETA", IDCARPETA);
try
{
cn.Open();
codigo = cmd.ExecuteScalar().ToString();
}
finally
{
cn.Close();
}
return(codigo);
}
//Metodos
public string CodigoDisposicion(string IDCARPETA)
{
cn = cnx.Conectar();
string codigo = string.Empty;
SqlCommand cmd = new SqlCommand
("USP_DISPOSICION_CODIGO", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@IDCARPETA", IDCARPETA);
try
{
cn.Open();
codigo = cmd.ExecuteScalar().ToString();
}
finally
{
cn.Close();
}
return(codigo);
}