public bool buscarRFC(string rfc)
{
SqlConnection cnn = DBConnectionFactory.GetOpenConnection();
SqlCommand cmd = new SqlCommand("PG_SK_RFC_USUARIO", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PP_RFC", rfc);
SqlDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
return(reader.HasRows);
}
catch (Exception e)
{
logger.Error(e, e.Message);
throw e;
}
finally
{
reader.Close();
cmd.Dispose();
cnn.Dispose();
}
}
public List <Serie> getSeries(int page, int rpp)
{
SqlConnection cnn = DBConnectionFactory.GetOpenConnection();
SqlCommand cmd = new SqlCommand("PG_SK_SERIES", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PP_NUM_PAGINA", page);
cmd.Parameters.AddWithValue("@PP_RPP", rpp);
SqlDataReader reader = cmd.ExecuteReader();
var series = new List <Serie>();
try
{
if (!reader.HasRows)
{
throw new NotFoundException("No se han encontrado series");
}
while (reader.Read())
{
series.Add(getSerieFromReader(reader));
}
return(series);
}
catch (Exception e)
{
logger.Error(e, e.Message);
throw e;
}
finally
{
reader.Close();
cmd.Dispose();
cnn.Close();
}
}
public int ActivarUnidadOperativa(int idUnidadOperativa)
{
SqlConnection cnn = DBConnectionFactory.GetOpenConnection();
SqlCommand cmd = new SqlCommand("PG_AC_UNIDAD_OPERATIVA", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PP_K_UNIDAD_OPERATIVA", idUnidadOperativa);
int resultado;
try
{
resultado = cmd.ExecuteNonQuery();
return(resultado);
}
catch (Exception e)
{
logger.Error(e, e.Message);
throw e;
}
finally
{
cmd.Dispose();
cnn.Dispose();
}
}
public Serie insertSerie(Serie serie)
{
SqlConnection cnn = DBConnectionFactory.GetOpenConnection();
SqlCommand cmd = new SqlCommand("PG_IN_SERIE", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PP_ID_SERIE", 0).Direction = ParameterDirection.InputOutput;
cmd.Parameters.AddWithValue("@PP_K_RAZON_SOCIAL", serie.emisor.unidadOperativa.razonSocial.idRazonSocial);
cmd.Parameters.AddWithValue("@PP_TIPO_VENTA", serie.tipoVenta);
cmd.Parameters.AddWithValue("@PP_SERIE", serie.serie);
try
{
cmd.ExecuteNonQuery();
int id = (int)cmd.Parameters["@PP_ID_SERIE"].Value;
if (id <= 0)
{
throw new Exception("Error al insertar la serie");
}
serie.id = id;
return(serie);
}
catch (Exception e)
{
logger.Error(e);
throw e;
}
finally
{
cmd.Dispose();
cnn.Dispose();
}
}
public int getSeriesCount()
{
SqlConnection cnn = DBConnectionFactory.GetOpenConnection();
SqlCommand cmd = new SqlCommand("PG_SK_SERIES_COUNT", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PP_TOTAL_REG", 0).Direction = ParameterDirection.InputOutput;
int total = 0;
try
{
cmd.ExecuteNonQuery();
total = (int)cmd.Parameters["@PP_TOTAL_REG"].Value;
return(total);
}
catch (Exception e)
{
logger.Error(e, e.Message);
throw e;
}
finally
{
cmd.Dispose();
cnn.Close();
}
}
public int InsertarZona(Zona zona)
{
SqlConnection cnn = DBConnectionFactory.GetOpenConnection();
SqlCommand cmd = new SqlCommand("PG_IN_ZONA", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PP_K_ZONA", 0).Direction = ParameterDirection.InputOutput;
cmd.Parameters.AddWithValue("@PP_ZONA_NOMBRE", zona.zona);
cmd.Parameters.AddWithValue("@PP_DESCRIPCTION", zona.descripcion);
cmd.Parameters.AddWithValue("@PP_IDENTIFICADOR", zona.identificador);
int resultado;
try
{
resultado = cmd.ExecuteNonQuery();
if (resultado > 0)
{
zona.idZona = int.Parse(cmd.Parameters["@PP_K_ZONA"].Value.ToString());
}
return(resultado);
}
catch (Exception e)
{
logger.Error(e, e.Message);
throw e;
}
finally
{
cmd.Dispose();
cnn.Dispose();
}
}
public int EliminarZona(int idZona)
{
SqlConnection cnn = DBConnectionFactory.GetOpenConnection();
SqlCommand cmd = new SqlCommand("PG_DL_ZONA", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PP_K_ZONA", idZona);
int resultado;
try
{
resultado = cmd.ExecuteNonQuery();
return(resultado);
}
catch (Exception e)
{
logger.Error(e, e.Message);
throw e;
}
finally
{
cmd.Dispose();
cnn.Dispose();
}
}
public DoctoRelacionado getDoctoRelacionadoInfo(int folio, string serie)
{
DoctoRelacionado docRelacion = new DoctoRelacionado();
SqlConnection cnn = DBConnectionFactory.GetOpenConnection();
SqlCommand cmd = new SqlCommand("PG_SK_FACTURA_PAGOS_INFO", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PP_L_DEBUG", 0);
cmd.Parameters.AddWithValue("@PP_K_SISTEMA_EXE", 1);
cmd.Parameters.AddWithValue("@PP_SERIE", serie);
cmd.Parameters.AddWithValue("@PP_FOLIO", folio);
SqlDataReader reader = cmd.ExecuteReader();
if (!reader.HasRows)
{
throw new InvalidInvoiceNumberException("Factura no existe o no es de pagos parciales o diferidos");
}
reader.Read();
docRelacion.idDocumento = reader.GetValue(0).ToString();
docRelacion.numParcialidad = int.Parse(reader.GetValue(1).ToString());
docRelacion.impSaldoAnt = double.Parse(reader.GetValue(2).ToString());
docRelacion.idFactura = int.Parse(reader.GetValue(3).ToString());
cnn.Dispose();
reader.Close();
return(docRelacion);
}
public Certificado GetIssuerCertInfo(int idRazonSocial)
{
Certificado cert = new Certificado();
SqlConnection cnn = DBConnectionFactory.GetOpenConnection();
SqlCommand command = new SqlCommand("PG_SK_CERT_INFO_EMISOR", cnn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@PP_L_DEBUG", 0);
command.Parameters.AddWithValue("@PP_K_SISTEMA_EXE", 1);
command.Parameters.AddWithValue("@PP_k_RAZON_SOCIAL", idRazonSocial);
SqlDataReader reader = command.ExecuteReader();
if (!reader.HasRows)
{
throw new InvalidRFCException("RFC proporcionado no es válido o no existe");
}
reader.Read();
cert.cert = reader.GetValue(0).ToString();
cert.key = reader.GetValue(1).ToString();
cert.contrasena = reader.GetValue(2).ToString();
cert.rutaCert = reader.GetValue(3).ToString();
reader.Close();
cnn.Close();
return(cert);
}
public CFDi GetFactura(int idFactura)
{
SqlConnection cnn = DBConnectionFactory.GetOpenConnection();
SqlCommand cmd = new SqlCommand("PG_SK_FACTURA_DETALLE", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PP_ID_FACTURA", idFactura);
SqlDataReader rdr = cmd.ExecuteReader();
var fac = new CFDi();
try
{
if (!rdr.HasRows)
{
throw new NotFoundException("No se han encontrado facturas");
}
while (rdr.Read())
{
fac = getCFDiFromReader(rdr);
}
return(fac);
}
catch (Exception e)
{
logger.Error(e, e.Message);
throw e;
}
finally
{
rdr.Close();
cmd.Dispose();
cnn.Close();
}
}
public int GetMisFacturasCount(int userId)
{
SqlConnection cnn = DBConnectionFactory.GetOpenConnection();
SqlCommand cmd = new SqlCommand("PG_SK_MIS_FACTURAS_TOTAL", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PP_TOTAL_REG", 0).Direction = ParameterDirection.InputOutput;
cmd.Parameters.AddWithValue("@PP_ID_USUARIO", userId);
int total = 0;
try
{
cmd.ExecuteNonQuery();
total = (int)cmd.Parameters["@PP_TOTAL_REG"].Value;
return(total);
}
catch (Exception e)
{
logger.Error(e, e.Message);
throw e;
}
finally
{
cmd.Dispose();
cnn.Close();
}
}
public List <CFDi> GetCFDis(int pagina, int rpp)
{
SqlConnection cnn = DBConnectionFactory.GetOpenConnection();
SqlCommand cmd = new SqlCommand("PG_SK_FACTURAS", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PP_NUM_PAGINA", pagina);
cmd.Parameters.AddWithValue("@PP_RPP", rpp);
SqlDataReader rdr = cmd.ExecuteReader();
var facs = new List <CFDi>();
try
{
if (!rdr.HasRows)
{
throw new NotFoundException("No se han encontrado facturas");
}
while (rdr.Read())
{
facs.Add(getCFDiFromReader(rdr));
}
return(facs);
}
catch (Exception e)
{
logger.Error(e, e.Message);
throw e;
}
finally
{
rdr.Close();
cmd.Dispose();
cnn.Close();
}
}
public void cancelarTimbre(CFDi cfdi)
{
SqlConnection cnn = DBConnectionFactory.GetOpenConnection();
SqlCommand cmd = new SqlCommand("PG_UP_CANCELAR_FACTURA", cnn);
cmd.Transaction = cnn.BeginTransaction("CfdiTransaction");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PP_L_DEBUG", 0);
cmd.Parameters.AddWithValue("@PP_K_SISTEMA_EXE", 1);
cmd.Parameters.AddWithValue("@PP_K_USUARIO", 0);
/////////////////////////////////////////////////
cmd.Parameters.AddWithValue("@PP_SERIE", cfdi.serie);
cmd.Parameters.AddWithValue("@PP_FOLIO", cfdi.folio);
try
{
SqlDataReader reader = cmd.ExecuteReader();
reader.Close();
cmd.Transaction.Commit();
}
catch (Exception e)
{
logger.Error(e);
cmd.Transaction.Rollback();
throw e;
}
finally
{
cnn.Dispose();
cmd.Dispose();
}
}
public int EliminarUsuarioRoles(int idUsuario)
{
SqlConnection cnn = DBConnectionFactory.GetOpenConnection();
SqlCommand cmd = new SqlCommand("PG_RS_USUARIO_ROL", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PP_ID_USUARIO", idUsuario);
int resultado = 0;
try
{
resultado = cmd.ExecuteNonQuery();
return(resultado);
}
catch (Exception e)
{
logger.Error(e);
throw e;
}
finally
{
cmd.Dispose();
cnn.Dispose();
}
}
public Certificado GetCertificado(int idCertificado)
{
SqlConnection cnn = DBConnectionFactory.GetOpenConnection();
SqlCommand cmd = new SqlCommand("PG_SK_CERTIFICADO", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PP_K_CERTIFICADO", idCertificado);
SqlDataReader rdr = null;
try
{
rdr = cmd.ExecuteReader();
if (!rdr.HasRows)
{
throw new NotFoundException("No se ha encontrado el certificado");
}
rdr.Read();
return(getCertificadoFromReader(rdr));
}
catch (Exception e)
{
logger.Error(e, e.Message);
throw e;
}
finally
{
rdr.Close();
cmd.Dispose();
cnn.Dispose();
}
}
public RazonSocial getRazonSocial(int idRazonSocial)
{
SqlConnection cnn = DBConnectionFactory.GetOpenConnection();
SqlCommand cmd = new SqlCommand("PG_SK_RAZON_SOCIAL_V2", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PP_K_RAZON_SOCIAL", idRazonSocial);
SqlDataReader rdr = null;
try
{
rdr = cmd.ExecuteReader();
if (!rdr.HasRows)
{
throw new NotFoundException("No se ha encontrado la Razón Social");
}
rdr.Read();
return(getRazonSocialFromReader(rdr));
}
catch (Exception e)
{
logger.Error(e, e.Message);
throw e;
}
finally
{
rdr.Close();
cmd.Dispose();
cnn.Dispose();
}
}
public Zona getZona(int idZona)
{
SqlConnection cnn = DBConnectionFactory.GetOpenConnection();
SqlCommand cmd = new SqlCommand("PG_SK_ZONA", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PP_K_ZONA", idZona);
SqlDataReader reader = null;
Zona zona = new Zona();
try
{
reader = cmd.ExecuteReader();
if (!reader.HasRows)
{
return(null);
}
reader.Read();
zona = getZonaFromReader(reader);
return(zona);
}
catch (Exception e)
{
logger.Error(e, e.Message);
throw e;
}
finally
{
reader.Close();
cmd.Dispose();
cnn.Dispose();
}
}
public bool ActivarRazonSocial(int idRazonSocial)
{
SqlConnection cnn = DBConnectionFactory.GetOpenConnection();
SqlCommand cmd = new SqlCommand("PG_AC_RAZON_SOCIAL", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PP_K_RAZON_SOCIAL", idRazonSocial);
try
{
int id = cmd.ExecuteNonQuery();
if (id == 0)
{
throw new Exception("No fue posible activar la razón social");
}
return(true);
}
catch (Exception e)
{
logger.Error(e, e.Message);
throw e;
}
finally
{
cmd.Dispose();
cnn.Dispose();
}
}
public int ActualizarZona(Zona zona)
{
SqlConnection cnn = DBConnectionFactory.GetOpenConnection();
SqlCommand cmd = new SqlCommand("PG_UP_ZONA", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PP_K_ZONA", zona.idZona);
cmd.Parameters.AddWithValue("@PP_ZONA_NOMBRE", zona.zona);
cmd.Parameters.AddWithValue("@PP_DESCRIPCTION", zona.descripcion);
cmd.Parameters.AddWithValue("@PP_IDENTIFICADOR", zona.identificador);
int resultado;
try
{
resultado = cmd.ExecuteNonQuery();
return(resultado);
}
catch (Exception e)
{
logger.Error(e, e.Message);
throw e;
}
finally
{
cmd.Dispose();
cnn.Dispose();
}
}
public List <RazonSocial> getRazonesSociales()
{
SqlConnection cnn = DBConnectionFactory.GetOpenConnection();
SqlCommand cmd = new SqlCommand("PG_SK_RAZONES_SOCIALES_TODAS", cnn);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader rdr = null;
List <RazonSocial> rss = new List <RazonSocial>();
try
{
rdr = cmd.ExecuteReader();
if (!rdr.HasRows)
{
throw new NotFoundException("No se han encontrado Razones Sociales");
}
while (rdr.Read())
{
rss.Add(getRazonSocialFromReader(rdr));
}
return(rss);
}
catch (Exception e)
{
logger.Error(e, e.Message);
throw e;
}
finally
{
rdr.Close();
cmd.Dispose();
cnn.Dispose();
}
}
public int ObtenerZonasTotal()
{
SqlConnection cnn = DBConnectionFactory.GetOpenConnection();
SqlCommand cmd = new SqlCommand("PG_SK_ZONAS_TOTAL", cnn);
cmd.CommandType = CommandType.StoredProcedure;
int zonasTotal;
SqlDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
reader.Read();
zonasTotal = reader.GetInt32(0);
return(zonasTotal);
}
catch (Exception e)
{
logger.Error(e, e.Message);
throw e;
}
finally
{
cmd.Dispose();
cnn.Dispose();
}
}
public int getRazonesSocialesTotal()
{
SqlConnection cnn = DBConnectionFactory.GetOpenConnection();
SqlCommand cmd = new SqlCommand("PG_SK_RAZONES_SOCIALES_TOTAL", cnn);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader rdr = null;
try
{
rdr = cmd.ExecuteReader();
if (!rdr.HasRows)
{
throw new NotFoundException("No se han encontrado Razones Sociales");
}
rdr.Read();
return(rdr.GetInt32(0));
}
catch (Exception e)
{
logger.Error(e, e.Message);
throw e;
}
finally
{
rdr.Close();
cmd.Dispose();
cnn.Dispose();
}
}
public void updateSerie(Serie serie)
{
SqlConnection cnn = DBConnectionFactory.GetOpenConnection();
SqlCommand cmd = new SqlCommand("PG_UP_SERIE", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PP_ID_SERIE", serie.id);
cmd.Parameters.AddWithValue("@PP_K_RAZON_SOCIAL", serie.emisor.unidadOperativa.razonSocial.idRazonSocial);
cmd.Parameters.AddWithValue("@PP_TIPO_VENTA", serie.tipoVenta);
cmd.Parameters.AddWithValue("@PP_SERIE", serie.serie);
try
{
int affectedRows = cmd.ExecuteNonQuery();
if (affectedRows == 0)
{
throw new Exception("La serie no ha sido actualizada");
}
}
catch (Exception e)
{
logger.Error(e, e.Message);
}
finally
{
cmd.Dispose();
cnn.Dispose();
}
}
public bool UpdateCertificado(Certificado cert)
{
SqlConnection cnn = DBConnectionFactory.GetOpenConnection();
SqlCommand cmd = new SqlCommand("PG_UP_CERTIFICADO", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PP_ID_CERTIFICADO", cert.idCertificado);
cmd.Parameters.AddWithValue("@PP_D_CERTIFICADO", cert.descripcion);
cmd.Parameters.AddWithValue("@PP_C_CERTIFICADO", cert.identificador);
cmd.Parameters.AddWithValue("@PP_CERTIFICADO", cert.cert);
cmd.Parameters.AddWithValue("@PP_RUTA_CERTIFICADO", cert.rutaCert);
cmd.Parameters.AddWithValue("@PP_LLAVE", cert.key);
cmd.Parameters.AddWithValue("@PP_CONTRASENA", cert.contrasena);
cmd.Parameters.AddWithValue("@PP_LOGO", "logo_" + cert.cert);
cmd.Parameters.AddWithValue("@PP_FECHA_EXPIRACION", cert.fechaExpiracion);
try
{
int id = cmd.ExecuteNonQuery();
if (id == 0)
{
throw new Exception("No fue posible actualizar el certificado");
}
return(true);
}
catch (Exception e)
{
logger.Error(e, e.Message);
throw e;
}
finally
{
cmd.Dispose();
cnn.Dispose();
}
}
public void ActivarSerie(int id)
{
SqlConnection cnn = DBConnectionFactory.GetOpenConnection();
SqlCommand cmd = new SqlCommand("PG_AC_SERIE", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PP_ID_SERIE", id);
try
{
int affectedRows = cmd.ExecuteNonQuery();
if (affectedRows == 0)
{
throw new Exception("La serie no ha sido activada");
}
}
catch (Exception e)
{
logger.Error(e, e.Message);
throw e;
}
finally
{
cmd.Dispose();
cnn.Dispose();
}
}
public bool ActivarCertificado(int idCertificado)
{
SqlConnection cnn = DBConnectionFactory.GetOpenConnection();
SqlCommand cmd = new SqlCommand("PG_AC_CERTIFICADO", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PP_ID_CERTIFICADO", idCertificado);
try
{
int id = cmd.ExecuteNonQuery();
if (id == 0)
{
throw new Exception("No fue posible activar el certificado");
}
return(true);
}
catch (Exception e)
{
logger.Error(e, e.Message);
throw e;
}
finally
{
cmd.Dispose();
cnn.Dispose();
}
}
public Serie getSerie(int id)
{
SqlConnection cnn = DBConnectionFactory.GetOpenConnection();
SqlCommand cmd = new SqlCommand("PG_SK_SERIE", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PP_ID_SERIE", id);
Serie serie = null;
SqlDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
if (!reader.HasRows)
{
throw new NotFoundException("No se ha encontrado la serie con id: " + id);
}
reader.Read();
serie = getSerieFromReader(reader);
return(serie);
}
catch (Exception e)
{
logger.Error(e, e.Message);
throw e;
}
finally
{
reader.Close();
cmd.Dispose();
cnn.Dispose();
}
}
public int getCertificadosTotal()
{
SqlConnection cnn = DBConnectionFactory.GetOpenConnection();
SqlCommand cmd = new SqlCommand("PG_SK_CERTIFICADOS_TOTAL", cnn);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader rdr = null;
try
{
rdr = cmd.ExecuteReader();
rdr.Read();
return(rdr.GetInt32(0));
}
catch (Exception e)
{
logger.Error(e, e.Message);
throw e;
}
finally
{
rdr.Close();
cmd.Dispose();
cnn.Dispose();
}
}
public UnidadOperativa ObtenerUnidadOperativa(int idUnidadOperativa)
{
SqlConnection cnn = DBConnectionFactory.GetOpenConnection();
SqlCommand cmd = new SqlCommand("PG_SK_UNIDAD_OPERATIVA_V2", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PP_ID_UNIDAD_OPERATIVA", idUnidadOperativa);
SqlDataReader reader = null;
UnidadOperativa uos = null;
try
{
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
uos = new UnidadOperativa();
while (reader.Read())
{
uos = GetUnidadOperativaFromReader(reader);
}
}
return(uos);
}
catch (Exception e)
{
logger.Error(e, e.Message);
throw e;
}
finally
{
reader.Close();
cmd.Dispose();
cnn.Dispose();
}
}
public void deleteUsuario(int id)
{
SqlConnection cnn = DBConnectionFactory.GetOpenConnection();
SqlCommand cmd = new SqlCommand("PG_DL_USUARIO_V2", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PP_ID_USUARIO", id);
try
{
int affectedRows = cmd.ExecuteNonQuery();
if (affectedRows == 0)
{
throw new Exception("El usuario no ha sido eliminado");
}
}
catch (Exception e)
{
logger.Error(e, e.Message);
throw e;
}
finally
{
cmd.Dispose();
cnn.Dispose();
}
}
