public static DataTable ConsultaClientePortatil(int Cliente)
{
SqlCommand cmd = SQLLayer.conexion.CreateCommand();
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "spQJAConsultaClientePortatil";
cmd.Parameters.Clear();
if (Cliente > 0)
{
cmd.Parameters.Add("@Cliente", SqlDbType.Int).Value = Cliente;
da.SelectCommand = cmd;
try
{
da.Fill(dt);
SQLLayer.CierraConexion();
return(dt);
}
catch (SqlException ex)
{
SQLLayer.CierraConexion();
foreach (SqlError er in ex.Errors)
{
MessageBox.Show(er.Message, "SQL Error Level" + er.Class, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
return(null);
}
}
else
{
return(null);
}
}
public static DataTable ConsultaMotivoQuejaArea(short area, short clasificacion)
{
SqlCommand cmd = SQLLayer.conexion.CreateCommand();
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "spQJAConsultaMotivoQuejaArea";
cmd.Parameters.Clear();
cmd.Parameters.Add("@Area", SqlDbType.TinyInt).Value = area;
cmd.Parameters.Add("@Clasificacion", SqlDbType.SmallInt).Value = clasificacion;
da.SelectCommand = cmd;
try
{
da.Fill(dt);
SQLLayer.CierraConexion();
return(dt);
}
catch (SqlException ex)
{
SQLLayer.CierraConexion();
foreach (SqlError er in ex.Errors)
{
MessageBox.Show(er.Message, "SQL Error Level" + er.Class, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
return(null);
}
}
public static void TerminaConsulta(bool abrirConexion, bool cerrarConexion)
{
SqlCommand cmd = new SqlCommand("set transaction isolation level read committed", SQLLayer.conexion);
try
{
if (abrirConexion)
{
SQLLayer.AbreConexion();
}
if (conexion.State == ConnectionState.Open)
{
cmd.ExecuteNonQuery();
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
if (cerrarConexion)
{
SQLLayer.CierraConexion();
}
}
}
//Texis 02/06/2015. Se agrego este metodo para obtener informacion de la nueva tabla llamada ClaseQueja y asi poder llenar elc ombo con las opciones pertinentes
public static DataTable ObtieneClasesQueja()
{
SqlCommand cmd = SQLLayer.conexion.CreateCommand();
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "spQJAObtieneClasesQueja";
cmd.Parameters.Clear();
da.SelectCommand = cmd;
try
{
da.Fill(dt);
SQLLayer.CierraConexion();
return(dt);
}
catch (SqlException ex)
{
SQLLayer.CierraConexion();
foreach (SqlError er in ex.Errors)
{
MessageBox.Show(er.Message, "SQL Error Level" + er.Class, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
return(null);
}
}
public static DataTable ConsultaArea(short Area, short AreaDependencia)
{
SqlCommand cmd = SQLLayer.conexion.CreateCommand();
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "spQJAConsultaArea";
cmd.Parameters.Clear();
if (Area == 0)
{
cmd.Parameters.Add("@Area", SqlDbType.TinyInt).Value = System.DBNull.Value;
}
else
{
cmd.Parameters.Add("@Area", SqlDbType.TinyInt).Value = Area;
}
if (AreaDependencia == 0)
{
cmd.Parameters.Add("@AreaDependencia", SqlDbType.TinyInt).Value = System.DBNull.Value;
}
else
{
cmd.Parameters.Add("@AreaDependencia", SqlDbType.TinyInt).Value = AreaDependencia;
}
da.SelectCommand = cmd;
try
{
da.Fill(dt);
SQLLayer.CierraConexion();
return(dt);
}
catch (SqlException ex)
{
SQLLayer.CierraConexion();
foreach (SqlError er in ex.Errors)
{
MessageBox.Show(er.Message, "SQL Error Level" + er.Class, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
return(null);
}
}
public static DataTable CargaCombo(string IdCombo)
{
SqlDataAdapter da = new SqlDataAdapter();
switch (IdCombo)
{
case "Area":
da = new SqlDataAdapter("SELECT Area, Descripcion FROM Area WHERE Area > 0 AND AreaDependencia IS NOT NULL", conexion);
break;
case "GravedadQueja":
da = new SqlDataAdapter("SELECT GravedadQueja, Descripcion FROM GravedadQueja", conexion);
break;
case "Celula":
if (int.Parse(QuejasLibrary.Public.Global.Parametros.ValorParametro("CelulasUsuario")) == 0)
{
da = new SqlDataAdapter("SELECT Celula, UPPER(Descripcion) AS Descripcion FROM Celula WHERE Comercial = 1 AND CelulaAdmin = 0", conexion);
}
else
{
da = new SqlDataAdapter("spSEGUsuarioCelulaConsulta @Usuario = " + QuejasLibrary.Public.Global.Usuario.IdUsuario, conexion);
}
break;
case "Ruta":
da = new SqlDataAdapter("SELECT Ruta, UPPER(Descripcion) AS Descripcion FROM Ruta", conexion);
break;
}
DataTable dt = new DataTable();
try
{
da.Fill(dt);
SQLLayer.CierraConexion();
}
catch (SqlException ex)
{
foreach (SqlError er in ex.Errors)
{
MessageBox.Show(er.Message, "SQL Error Level" + er.Class, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
return(dt);
}
public static void CancelarTrasaccion(SqlTransaction Trasaccion)
{
try
{
Trasaccion.Rollback();
}
catch (SqlException ex)
{
SQLLayer.CierraConexion();
foreach (SqlError er in ex.Errors)
{
MessageBox.Show(er.Message, "SQL Error Level " + er.Class, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
finally
{
SQLLayer.CierraConexion();
}
}
public static SqlTransaction IniciaTrasaccion()
{
try
{
SQLLayer.AbreConexion();
sqlTransaccion = conexion.BeginTransaction();
//return sqlTransaccion;
}
catch (SqlException ex)
{
SQLLayer.CierraConexion();
foreach (SqlError er in ex.Errors)
{
MessageBox.Show(er.Message, "SQL Error Level " + er.Class, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
sqlTransaccion = null;
}
// finally
// {
return(sqlTransaccion);
// }
}
public static DataTable ExisteQueja(int Cliente, string PersonaQueja, string Telefono, bool EsClientePortatil)
{
SqlCommand cmd = SQLLayer.conexion.CreateCommand();
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "spQJAExisteQueja";
cmd.Parameters.Clear();
if (EsClientePortatil == false)
{
if (Cliente == 0)
{
cmd.Parameters.Add("@Cliente", SqlDbType.Int).Value = System.DBNull.Value;
}
else
{
cmd.Parameters.Add("@Cliente", SqlDbType.Int).Value = Cliente;
}
}
else
{
if (Cliente == 0)
{
cmd.Parameters.Add("@ClientePortatil", SqlDbType.Int).Value = System.DBNull.Value;
}
else
{
cmd.Parameters.Add("@ClientePortatil", SqlDbType.Int).Value = Cliente;
}
}
if (PersonaQueja == string.Empty)
{
cmd.Parameters.Add("@PersonaQueja", SqlDbType.VarChar).Value = System.DBNull.Value;
}
else
{
cmd.Parameters.Add("@PersonaQueja", SqlDbType.VarChar).Value = PersonaQueja;
}
if (Telefono == string.Empty)
{
cmd.Parameters.Add("@Telefono", SqlDbType.VarChar).Value = System.DBNull.Value;
}
else
{
cmd.Parameters.Add("@Telefono", SqlDbType.VarChar).Value = Telefono;
}
da.SelectCommand = cmd;
try
{
da.Fill(dt);
SQLLayer.CierraConexion();
return(dt);
}
catch (SqlException ex)
{
SQLLayer.CierraConexion();
foreach (SqlError er in ex.Errors)
{
MessageBox.Show(er.Message, "SQL Error Level" + er.Class, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
return(null);
}
}
public static DataTable ConsultaQueja(short Area, short AreaDependencia, short SinArea, string Status, string NumeroQueja, DateTime FIniQueja, DateTime FFinQueja, short Celula, short Ruta, short GravedadQueja, short Tipo)
{
SqlCommand cmd = SQLLayer.conexion.CreateCommand();
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "spQJAConsultaQueja";
cmd.Parameters.Clear();
cmd.Parameters.Add("@AREA", SqlDbType.TinyInt).Value = Area;
cmd.Parameters.Add("@AREADEPENDENCIA", SqlDbType.TinyInt).Value = AreaDependencia;
cmd.Parameters.Add("@SINAREA", SqlDbType.TinyInt).Value = SinArea;
if (Status == string.Empty)
{
cmd.Parameters.Add("@STATUS", SqlDbType.VarChar).Value = System.DBNull.Value;
}
else
{
cmd.Parameters.Add("@STATUS", SqlDbType.VarChar).Value = Status;
}
if (NumeroQueja == string.Empty)
{
cmd.Parameters.Add("@NUMEROQUEJA", SqlDbType.VarChar).Value = System.DBNull.Value;
}
else
{
cmd.Parameters.Add("@NUMEROQUEJA", SqlDbType.VarChar).Value = NumeroQueja;
}
cmd.Parameters.Add("@FECHAINIQUEJA", SqlDbType.DateTime).Value = FIniQueja;
cmd.Parameters.Add("@FECHAFINQUEJA", SqlDbType.DateTime).Value = FFinQueja;
if (Celula == -1)
{
cmd.Parameters.Add("@CELULA", SqlDbType.TinyInt).Value = System.DBNull.Value;
}
else
{
cmd.Parameters.Add("@CELULA", SqlDbType.TinyInt).Value = Celula;
}
if (Ruta == -1)
{
cmd.Parameters.Add("@RUTA", SqlDbType.SmallInt).Value = System.DBNull.Value;
}
else
{
cmd.Parameters.Add("@RUTA", SqlDbType.SmallInt).Value = Ruta;
}
if (GravedadQueja == -1)
{
cmd.Parameters.Add("@GravedadQueja", SqlDbType.TinyInt).Value = System.DBNull.Value;
}
else
{
cmd.Parameters.Add("@GravedadQueja", SqlDbType.TinyInt).Value = GravedadQueja;
}
if (Tipo == 0)
{
cmd.Parameters.Add("@tipocliente", SqlDbType.TinyInt).Value = System.DBNull.Value;
}
else
{
cmd.Parameters.Add("@tipocliente", SqlDbType.TinyInt).Value = Tipo;
}
da.SelectCommand = cmd;
try
{
da.Fill(dt);
SQLLayer.CierraConexion();
return(dt);
}
catch (SqlException ex)
{
SQLLayer.CierraConexion();
foreach (SqlError er in ex.Errors)
{
MessageBox.Show(er.Message, "SQL Error Level" + er.Class, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
return(null);
}
}
