public List<Producto> listaProductosFechaEliminados(string AFecha)
{
List<Producto> pResult = new List<Producto>();
Producto pProducto = null;
string pSentencia = "SELECT DISTINCT(PRODUCTO) AS CLAVE, (SELECT DESCRIPCION FROM DRASPROD WHERE CLAVE = PRODUCTO) FROM DRASELIM WHERE FECHA = @FECHA AND CODIGOALTA IS NOT NULL";
FbConnection con = _Conexion.ObtenerConexion();
FbCommand com = new FbCommand(pSentencia, con);
com.Parameters.Add("@FECHA", FbDbType.TimeStamp).Value = AFecha;
try
{
con.Open();
FbDataReader reader = com.ExecuteReader();
while (reader.Read())
{
pProducto = new Producto();
pProducto.Clave = reader["DESCRIPCION"] != DBNull.Value ? (string)reader["DESCRIPCION"] : "";
pResult.Add(pProducto);
}
}
finally
{
if (con.State == System.Data.ConnectionState.Open)
{
con.Close();
}
}
return pResult;
}
public bool ExisteEmpaque(Empaque AEmpaque)
{
string pSentencia = "SELECT ID FROM DRASEMPAQUE WHERE UPPER(TRIM(NOMBRE)) = @NOMBRE";
FbConnection con = _Conexion.ObtenerConexion();
FbCommand com = new FbCommand(pSentencia, con);
com.Parameters.Add("@NOMBRE", FbDbType.VarChar).Value = AEmpaque.Nombre.ToUpper().Trim();
try
{
con.Open();
FbDataReader reader = com.ExecuteReader();
if (reader.Read())
{
if ((int)reader["ID"] == AEmpaque.Clave)
return false;
else return true;
}
}
finally
{
if (con.State == System.Data.ConnectionState.Open)
{
con.Close();
}
}
return false;
}
public List<Empaque> ObtenerEmpaques(string AOrderBy)
{
List<Empaque> pResult = new List<Empaque>();
Empaque pEmpaque = null;
string pSentencia = "SELECT * FROM DRASEMPAQUE ORDER BY "+AOrderBy;
FbConnection con = _Conexion.ObtenerConexion();
FbCommand com = new FbCommand(pSentencia,con);
try
{
con.Open();
FbDataReader reader = com.ExecuteReader();
while (reader.Read()){
pEmpaque = new Empaque();
pEmpaque.Clave = (int)reader["ID"];
pEmpaque.IdTipoEmpaque = (int)reader["ID_TIPOEMPAQUE"];
pEmpaque.Nombre = (reader["NOMBRE"] != DBNull.Value) ? (string)reader["NOMBRE"] : "";
pEmpaque.CodigoSAP = (reader["CODIGOSAP"] != DBNull.Value) ? (string)reader["CODIGOSAP"] : "";
pEmpaque.Costo = (reader["COSTO"] != DBNull.Value) ? (decimal)reader["COSTO"] : 0;
pResult.Add(pEmpaque);
}
}
finally
{
if (con.State == System.Data.ConnectionState.Open)
{
con.Close();
}
}
return pResult;
}
public void PopulaDadosCancelamento(string sCodConhecimento, string sJustificativa)
{
try
{
StringBuilder sQuery = new StringBuilder();
sQuery.Append("Select ");
sQuery.Append("conhecim.cd_chavecte chCTe, ");
sQuery.Append("conhecim.cd_nprotcte nProt ");
sQuery.Append("from conhecim ");
sQuery.Append("where conhecim.cd_conheci ='" + sCodConhecimento + "' ");
sQuery.Append("and conhecim.cd_empresa = '" + belStatic.CodEmpresaCte + "'");
FbCommand fbConn = new FbCommand(sQuery.ToString(), cx.get_Conexao());
cx.Open_Conexao();
fbConn.ExecuteNonQuery();
FbDataReader dr = fbConn.ExecuteReader();
dr.Read();
objBelCancelaCte.versao = "1.03";
objBelCancelaCte.Id = "ID" + dr["chCTe"].ToString();
objBelCancelaCte.tpAmb = belStatic.TpAmb.ToString();
objBelCancelaCte.xServ = "CANCELAR";
objBelCancelaCte.chCTe = dr["chCTe"].ToString();
objBelCancelaCte.nProt = dr["nProt"].ToString();
objBelCancelaCte.xJust = sJustificativa;
}
catch (Exception)
{
throw;
}
finally { cx.Close_Conexao(); }
}
public List<EstimacionEmpaqueM> ObtenerLista()
{
List<EstimacionEmpaqueM> pResult = new List<EstimacionEmpaqueM>();
EstimacionEmpaqueM estimacionEmpM;
string pSentencia = "SELECT * FROM DRASESTIEMPAQUEM";
FbConnection con = _Conexiones.ObtenerConexion();
FbCommand com = new FbCommand(pSentencia, con);
try
{
con.Open();
FbDataReader reader = com.ExecuteReader();
while (reader.Read())
{
estimacionEmpM = new EstimacionEmpaqueM();
estimacionEmpM.Id = (int)reader["ID"];
estimacionEmpM.Descripcion = reader["DESCRIPCION"] != DBNull.Value ? (string)reader["DESCRIPCION"] : "";
estimacionEmpM.FechaHoraSistema = reader["FECHAHORASISTEMA"] != DBNull.Value ? (DateTime?)reader["FECHAHORASISTEMA"] : null;
pResult.Add(estimacionEmpM);
}
}
finally
{
if (con.State == System.Data.ConnectionState.Open)
{
con.Close();
}
}
return pResult;
}
public Equipe buscarCod_Equipe(String cod_equipe)
{
SigletonConexaoFB.carregaStrcnx();
Equipe resultado = new Equipe();
FbCommand command = new FbCommand();
FbDataReader reader;
try
{
command.Connection = SigletonConexaoFB.getConexao();
command.CommandText = "SELECT A.EQUIPE,A.NOME,A.COD_PERFIL_EQUIPE,B.NOME as NOME_PERFIL FROM TB_EQUIPE A " +
"LEFT OUTER JOIN TB_PERFIL_EQUIPE B ON (B.COD_PERFIL_EQUIPE=A.COD_PERFIL_EQUIPE) WHERE EQUIPE='" + cod_equipe + "'";
command.Connection.Open();
reader = command.ExecuteReader();
if (reader.Read())
{
resultado.Cod_Equipe = reader["EQUIPE"].ToString();
resultado.Nome = reader["NOME"].ToString();
resultado.Perfil.Cod_Perfil_Equipe = int.Parse(reader["COD_PERFIL_EQUIPE"].ToString());
resultado.Perfil.Nome = reader["NOME_PERFIL"].ToString();
}
reader.Close();
}
catch (Exception ex)
{
throw new Exception("Erro na busca da equipe pelo código: " + ex.Message);
}
finally
{
command.Connection.Close();
}
return resultado;
}
//SELECT T0.*, T1.DESCRIPCION FROM DRASSOBRANTESD T0 JOIN DRASPROD T1 ON T1.CLAVE = T0.PRODUCTO WHERE ID_SOBRANTE = ?
public List<SobrantesD> obtener_lista(int Aid)
{
List<SobrantesD> pResult = new List<SobrantesD>();
string pSentencia = "SELECT T0.*, T1.DESCRIPCION FROM DRASSOBRANTESD T0 JOIN DRASPROD T1 ON T1.CLAVE = T0.PRODUCTO WHERE ID_SOBRANTE = @ID_SOBRANTE";
FbConnection con = _Conexiones.ObtenerConexion();
FbCommand com = new FbCommand(pSentencia, con);
com.Parameters.Add("@ID_SOBRANTE", FbDbType.Integer).Value = Aid;
try
{
con.Open();
FbDataReader reader = com.ExecuteReader();
while (reader.Read())
{
SobrantesD pSobrandesD = new SobrantesD();
pSobrandesD.id = (reader["ID"] != DBNull.Value) ? (int)reader["ID"] : -1;
pSobrandesD.id_sobrante = (reader["ID_SOBRANTE"] != DBNull.Value) ? (int)reader["ID_SOBRANTE"] : -1;
pSobrandesD.producto = (reader["PRODUCTO"] != DBNull.Value) ? (string)reader["PRODUCTO"] : "";
pSobrandesD.descripcion = (reader["DESCRIPCION"] != DBNull.Value) ? (string)reader["DESCRIPCION"] : "";
pResult.Add(pSobrandesD);
}
}
finally
{
if (con.State == System.Data.ConnectionState.Open)
{
con.Close();
}
}
return pResult;
}
public void InsertGuidTest()
{
FbCommand createTable = new FbCommand("CREATE TABLE GUID_TEST (GUID_FIELD CHAR(16) CHARACTER SET OCTETS)", Connection);
createTable.ExecuteNonQuery();
createTable.Dispose();
Guid newGuid = Guid.Empty;
Guid guidValue = Guid.NewGuid();
// Insert the Guid
FbCommand insert = new FbCommand("INSERT INTO GUID_TEST (GUID_FIELD) VALUES (@GuidValue)", Connection);
insert.Parameters.Add("@GuidValue", FbDbType.Guid).Value = guidValue;
insert.ExecuteNonQuery();
insert.Dispose();
// Select the value
FbCommand select = new FbCommand("SELECT * FROM GUID_TEST", Connection);
using (FbDataReader r = select.ExecuteReader())
{
if (r.Read())
{
newGuid = r.GetGuid(0);
}
}
Assert.AreEqual(guidValue, newGuid);
}
public IModel Select(FbConnection conn, FbTransaction trans, string code)
{
string query = "SELECT * FROM models WHERE modelcode = UPPER(@modelcode)";
IModel ret = null;
try
{
using (FbCommand cmd = new FbCommand(query, conn, trans))
{
cmd.Parameters.AddWithValue("modelcode", code.ToUpper());
using (FbDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
ret = new Model
{
Id = reader.GetInt32(0),
Code = reader.GetString(1),
ObjectIdRoot = reader.GetInt32(2)
};
}
}
}
}
catch (Exception ex)
{
throw new Exception(String.Format("Ошибка SQL запроса. {0}", ex.Message));
}
return ret;
}
public string RetPrestadorEmail()
{
belConnection cx = new belConnection();
try
{
string sMsgPadraoEmail = "{5}Razão Social:{1}{0}{5}E-mail: {2}{0}{5}CCM :{3}{0}{5}CNPJ:{4}{0}{0}";
StringBuilder sQuery = new StringBuilder();
sQuery.Append(" select empresa.cd_cgc, empresa.cd_inscrmu, empresa.cd_email, empresa.nm_empresa from empresa ");
sQuery.Append(" where empresa.cd_empresa = '" + belStatic.codEmpresaNFe + "'");
FbCommand Comand = new FbCommand(sQuery.ToString(), cx.get_Conexao());
cx.Open_Conexao();
Comand.ExecuteNonQuery();
FbDataReader dr = Comand.ExecuteReader();
dr.Read();
objtcIdentificacaoPrestador = new tcIdentificacaoPrestador();
return string.Format(sMsgPadraoEmail, "<br>",
dr["nm_empresa"].ToString(),
dr["cd_email"].ToString(),
dr["cd_inscrmu"].ToString(),
dr["cd_cgc"].ToString(), " ");
}
catch (Exception ex)
{
throw;
}
finally
{
cx.Close_Conexao();
}
}
public bool ExisteLote(int ALote)
{
string pSentencia = "SELECT * FROM LOTES_NO_INVENTARIABLES WHERE LOTE=@LOTE";
FbConnection con = _Conexion.ObtenerConexion();
FbCommand com = new FbCommand(pSentencia, con);
com.Parameters.Add("@LOTE", FbDbType.Integer).Value = ALote;
try
{
con.Open();
FbDataReader reader = com.ExecuteReader();
if (reader.Read())
{
return true;
}
}
finally
{
if (con.State == System.Data.ConnectionState.Open)
{
con.Close();
}
}
return false;
}
//Retorna Datos de Maquila especifica
public CostoMaquilaM ObtenerCostosMaquila(int Id)
{
CostoMaquilaM pResult = new CostoMaquilaM();
string pSentencia = "SELECT * FROM DRASCOSTOSMAQUILAM WHERE ID = @ID ";
FbConnection con = _Conexiones.ObtenerConexion();
FbCommand com = new FbCommand(pSentencia, con);
com.Parameters.Add("@ID", FbDbType.TimeStamp).Value = Id;
try
{
con.Open();
FbDataReader reader = com.ExecuteReader();
if (reader.Read())
{
pResult.Id = reader.GetInt32(0);
pResult.Fecha = reader.GetString(1);
pResult.FechaFinal = reader.GetString(2);
pResult.Descripcion = reader.GetString(3);
pResult.Activo = reader.GetString(4);
pResult.FechaHoraSistema = reader.GetString(5);
}
}
finally
{
if (con.State == System.Data.ConnectionState.Open)
{
con.Close();
}
}
return pResult;
}
public List<string> BuscaCodigoEmpresas()
{
List<string> slCodigos = new List<string>();
belConnection cx = new belConnection();
try
{
using (FbCommand cmd = new FbCommand("Select empresa.cd_empresa from empresa order by empresa.cd_empresa", cx.get_Conexao()))
{
cx.Open_Conexao();
cmd.ExecuteNonQuery();
FbDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
slCodigos.Add(dr["cd_empresa"].ToString());
}
}
}
catch (Exception ex)
{
throw;
}
finally
{
cx.Close_Conexao();
}
return slCodigos;
}
public bool ExisteDestino(Destino ADestino)
{
string pSentencia = "SELECT CLAVE FROM DRASDEST WHERE UPPER(TRIM(DESTINO)) = @DESTINO";
FbConnection con = _Conexion.ObtenerConexion();
FbCommand com = new FbCommand(pSentencia, con);
com.Parameters.Add("@DESTINO", FbDbType.VarChar).Value = ADestino._Destino.ToUpper().Trim();
try
{
con.Open();
FbDataReader reader = com.ExecuteReader();
if (reader.Read())
{
int clave = (int)reader["CLAVE"];
if (clave == ADestino.Clave)
return false;
else return true;
}
}
finally
{
if (con.State == System.Data.ConnectionState.Open)
{
con.Close();
}
}
return false;
}
protected List<Dictionary<string, string>> _executeReader(FbConnection conn, FbTransaction trans, string query, Dictionary<string, object> prms)
{
List<Dictionary<string, string>> ret = new List<Dictionary<string, string>>();
using (FbCommand cmd = new FbCommand(query, conn, trans))
{
foreach (var prm in prms)
cmd.Parameters.AddWithValue(prm.Key, prm.Value);
using (FbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Dictionary<string, string> row = new Dictionary<string, string>();
Object[] values = new Object[reader.FieldCount];
reader.GetValues(values);
for (int jj = 0; jj < reader.FieldCount; jj++)
row.Add(reader.GetName(jj).ToLower(), values[jj].ToString());
ret.Add(row);
}
}
}
return ret;
}
public ClasificacionCorte ClasificacionCorteObtener(int AClave)
{
ClasificacionCorte pResult = null;
string pSentencia = "SELECT ID, DESCRIPCION, LOTES FROM CLASIFICACIONCORTES WHERE ID = @ID";
FbConnection con = _Conexion.ObtenerConexion();
FbCommand com = new FbCommand(pSentencia, con);
com.Parameters.Add("@ID", FbDbType.Integer).Value = AClave;
try
{
con.Open();
FbDataReader reader = com.ExecuteReader();
if (reader.Read())
{
pResult = ReaderToEntidad(reader);
}
}
finally
{
if (con.State == System.Data.ConnectionState.Open)
{
con.Close();
}
}
return pResult;
}
//Retorna la lista de Costos Maquila
public List<CostoMaquilaM> ListaCostosMaquila()
{
List<CostoMaquilaM> pResult = new List<CostoMaquilaM>();
string pSentencia = "SELECT * FROM DRASCOSTOSMAQUILAM";
FbConnection con = _Conexiones.ObtenerConexion();
FbCommand com = new FbCommand(pSentencia, con);
try
{
con.Open();
FbDataReader reader = com.ExecuteReader();
while (reader.Read())
{
CostoMaquilaM pCostoMaquila = new CostoMaquilaM();
pCostoMaquila.Id = reader.GetInt32(0);
pCostoMaquila.Fecha = reader.GetString(1);
pCostoMaquila.FechaFinal = reader.GetString(2);
pCostoMaquila.Descripcion = reader.GetString(3);
pCostoMaquila.Activo = reader.GetString(4);
pCostoMaquila.FechaHoraSistema = reader.GetString(5);
pResult.Add(pCostoMaquila);
}
}
finally
{
if (con.State == System.Data.ConnectionState.Open)
{
con.Close();
}
}
return pResult;
}
public void PopulainfCarga(belinfCte objbelinfCte, FbConnection conn, string sCte)
{
try
{
StringBuilder sQuery = new StringBuilder();
sQuery.Append("Select ");
sQuery.Append("coalesce(conhecim.ds_prodpred,'')proPred, ");
sQuery.Append("coalesce(nfconhec.vl_nf,'')vMerc ");
sQuery.Append("from conhecim ");
sQuery.Append("join nfconhec on conhecim.nr_lanc = nfconhec.nr_lancconhecim ");
sQuery.Append("join empresa on conhecim.cd_empresa = empresa.cd_empresa ");
sQuery.Append("where conhecim.nr_lanc ='" + sCte + "' ");
sQuery.Append("and empresa.cd_empresa ='" + belStatic.CodEmpresaCte + "'");
FbCommand fbConn = new FbCommand(sQuery.ToString(), conn);
fbConn.ExecuteNonQuery();
FbDataReader dr = fbConn.ExecuteReader();
objbelinfCte.infCTeNorm = new belinfCTeNorm();
objbelinfCte.infCTeNorm.infCarga = new belinfCarga();
while (dr.Read())
{
objbelinfCte.infCTeNorm.infCarga.vCarga += Convert.ToDecimal(dr["vMerc"].ToString().Replace(".", ","));
objbelinfCte.infCTeNorm.infCarga.proPred =belUtil.TiraSimbolo( dr["proPred"].ToString(),"");
}
}
catch (Exception ex)
{
throw ex;
}
}
public void DeletaTramite(int idTramite)
{
string query = "select datarecebimento , usuariorecebimento from webtramite where id = " + idTramite;
var connection = Persist.GetConn.getConn();
var cmd = new FbCommand(query, connection);
var datarecebimento = string.Empty;
var usuariorecebimento = string.Empty;
using (connection)
{
connection.Open();
var dr = cmd.ExecuteReader();
while (dr.Read()) {
datarecebimento = dr["datarecebimento"].ToString();
usuariorecebimento = dr["usuariorecebimento"].ToString();
}
}
if (string.IsNullOrEmpty(datarecebimento) && string.IsNullOrEmpty(usuariorecebimento))
{
string sql = "delete from webtramite where id = " + idTramite;
AcessoDados.AcessoDados.executar(sql);
}
}
public Destino DestinoObtener(int AClave){
Destino pResult = null;
string pSentencia = "SELECT CLAVE, DESTINO FROM DRASDEST WHERE CLAVE = @CLAVE";
FbConnection con = _Conexion.ObtenerConexion();
FbCommand com = new FbCommand(pSentencia, con);
com.Parameters.Add("@CLAVE", FbDbType.Integer).Value = AClave;
try
{
con.Open();
FbDataReader reader = com.ExecuteReader();
if (reader.Read()){
pResult = ReaderToEntidad(reader);
}
}
finally
{
if (con.State == System.Data.ConnectionState.Open){
con.Close();
}
}
return pResult;
}
public Row[] Select()
{
FbTransaction transaction = connection.BeginTransaction();
FbCommand cmd = new FbCommand(selectCommand, connection, transaction);
List<Row> rows = new List<Row>();
Row nr;
using (FbDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
nr = new Row();
nr.ID = dr.GetInt32(0);
nr.Name = dr.GetString(1);
nr.Description = dr.GetString(2);
using (System.IO.Stream stream = dr.GetStream(3))
{
using (System.IO.MemoryStream mStream = new System.IO.MemoryStream())
{
stream.CopyTo(mStream);
nr.Image = mStream.ToArray();
}
}
rows.Add(nr);
}
}
transaction.Commit();
return rows.ToArray();
}
public bool ExisteCamaraMismoId(Camara ACamara)
{
string pSentencia = "SELECT ID FROM DRASCAM WHERE ID = @ID";
FbConnection con = _Conexion.ObtenerConexion();
FbCommand com = new FbCommand(pSentencia, con);
com.Parameters.Add("@ID", FbDbType.Integer).Value = ACamara.Clave;
try
{
con.Open();
FbDataReader reader = com.ExecuteReader();
if (reader.Read())
{
return true;
}
}
finally
{
if (con.State == System.Data.ConnectionState.Open)
{
con.Close();
}
}
return false;
}
public void SimpleSelectTest()
{
FbConnectionStringBuilder csb = base.BuildConnectionStringBuilder();
csb.Enlist = true;
using (TransactionScope scope = new TransactionScope())
{
using (FbConnection c = new FbConnection(csb.ToString()))
{
c.Open();
using (FbCommand command = new FbCommand("select * from TEST where (0=1)", c))
{
using (FbDataReader r = command.ExecuteReader())
{
while (r.Read())
{
}
}
}
}
scope.Complete();
}
}
public string fNIP(string sNIP)
{
string sReturn = "0";
string query = "select count(*) from ad_firms where (UPPER(TAXID) = UPPER('" + sNIP + "'));";
//Open connection
if (this.OpenConnection() == true)
{
//Create Command
FbCommand cmd = new FbCommand(query, connection);
//Create a data reader and Execute the command
FbDataReader dataReader = cmd.ExecuteReader();
//MessageBox.Show(query, "qry", MessageBoxButtons.OK);
//Read the data and store them in the list
while (dataReader.Read())
{
sReturn = dataReader[0].ToString() + "";
}
//close Data Reader
dataReader.Close();
//close Connection
this.CloseConnection();
return sReturn;
}
else
{
return sReturn;
}
}
public bool ExisteClasificacionCorte(ClasificacionCorte AClasificacionCorte)
{
string pSentencia = "SELECT ID FROM CLASIFICACIONCORTES WHERE UPPER(TRIM(DESCRIPCION)) = @DESCRIPCION";
FbConnection con = _Conexion.ObtenerConexion();
FbCommand com = new FbCommand(pSentencia, con);
com.Parameters.Add("@DESCRIPCION", FbDbType.VarChar).Value = AClasificacionCorte.Descripcion.ToUpper().Trim();
try
{
con.Open();
FbDataReader reader = com.ExecuteReader();
if (reader.Read())
{
if ((int)reader["ID"] == AClasificacionCorte.Id)
return false;
else return true;
}
}
finally
{
if (con.State == System.Data.ConnectionState.Open)
{
con.Close();
}
}
return false;
}
public tcDadosConstrucaoCivil RettcDadosConstrucaoCivil(FbConnection Conn, string sNota)
{
try
{
StringBuilder sQuery = new StringBuilder();
sQuery.Append("select coalesce(clifor.cd_art,'') Art, coalesce(clifor.cd_obra,'')CodigoObra {0}");
sQuery.Append("from nf inner join clifor on nf.cd_clifor = clifor.cd_clifor {0}");
sQuery.Append("where nf.cd_nfseq = '{1}' and nf.cd_empresa = '{2}' {0}");
string sQueryEnd = string.Format(sQuery.ToString(), Environment.NewLine, sNota, belStatic.codEmpresaNFe);
FbCommand cmd = new FbCommand(sQueryEnd, Conn);
Conn.Open();
FbDataReader dr = cmd.ExecuteReader();
objtcDadosConstrucaoCivil = new tcDadosConstrucaoCivil();
while (dr.Read())
{
objtcDadosConstrucaoCivil.Art = dr["Art"].ToString();
objtcDadosConstrucaoCivil.CodigoObra = dr["CodigoObra"].ToString();
}
}
catch (Exception)
{
throw;
}
finally { Conn.Close(); }
return objtcDadosConstrucaoCivil;
}
public Produto buscarCodmat(String codmat)
{
SigletonConexaoFB.carregaStrcnx();
Produto resultado = new Produto();
FbCommand command = new FbCommand();
FbDataReader reader;
try
{
command.Connection = SigletonConexaoFB.getConexao();
command.CommandText = "SELECT CODMAT,DESCRICAO,UNID FROM TB_MATERIAL " +
"WHERE CODMAT='" + codmat + "'";
command.Connection.Open();
reader = command.ExecuteReader();
if (reader.Read())
{
resultado.Codmat = reader["CODMAT"].ToString();
resultado.Descricao = reader["DESCRICAO"].ToString();
resultado.Unid = reader["UNID"].ToString();
}
reader.Close();
}
catch (Exception ex)
{
throw new Exception("Erro na busca de produto pelo código: " + ex.Message);
}
finally
{
command.Connection.Close();
}
return resultado;
}
public TcPedidoCancelamento BuscaDadosParaCancelamento(FbConnection Conn, string sCodCancelamento, string sSequencia)
{
try
{
StringBuilder sQuery = new StringBuilder();
sQuery.Append("select cidades.cd_municipio, empresa.cd_cgc Cnpj, empresa.cd_inscrmu, ");
sQuery.Append("coalesce(nf.cd_numero_nfse,'')cd_numero_nfse ");
sQuery.Append("from nf inner join empresa on nf.cd_empresa = empresa.cd_empresa ");
sQuery.Append("inner join cidades on (cidades.nm_cidnor = empresa.nm_cidnor) ");
sQuery.Append("where nf.cd_nfseq = '" + sSequencia + "' and ");
sQuery.Append("nf.cd_empresa = '" + belStatic.codEmpresaNFe + "'");
FbCommand Command = new FbCommand(sQuery.ToString(), Conn);
FbDataReader dr = Command.ExecuteReader();
dr.Read();
TcPedidoCancelamento objCancelamento = new TcPedidoCancelamento();
objCancelamento.InfPedidoCancelamento = new tcInfPedidoCancelamento();
objCancelamento.InfPedidoCancelamento.CodigoCancelamento = sCodCancelamento;
objCancelamento.InfPedidoCancelamento.IdentificacaoNfse = new tcIdentificacaoNfse();
objCancelamento.InfPedidoCancelamento.IdentificacaoNfse.CodigoMunicipio = dr["cd_municipio"].ToString();
objCancelamento.InfPedidoCancelamento.IdentificacaoNfse.Numero = dr["cd_numero_nfse"].ToString();
objCancelamento.InfPedidoCancelamento.IdentificacaoNfse.Cnpj = dr["Cnpj"].ToString();
objCancelamento.InfPedidoCancelamento.IdentificacaoNfse.InscricaoMunicipal = dr["cd_inscrmu"].ToString();
return objCancelamento;
}
catch (Exception)
{
throw;
}
}
public int ObtenerCajasAplicadasCorte(string AFecha, int ACamara, string AEmbarcado)
{
string pSentencia = "SELECT COUNT(*) FROM DRASCORT WHERE "+
"FECHA = @FECHA "+
"AND CAMARA = @CAMARA "+
"AND EMBARCADO = @EMBARCADO "+
"AND ENTRADA_APLICADA = 'Si'";
FbConnection con = _Conexion.ObtenerConexion();
FbCommand com = new FbCommand(pSentencia, con);
com.Parameters.Add("@FECHA", FbDbType.TimeStamp).Value = AFecha;
com.Parameters.Add("@CAMARA", FbDbType.Integer).Value = ACamara;
com.Parameters.Add("@EMBARCADO", FbDbType.VarChar).Value = AEmbarcado;
try
{
con.Open();
FbDataReader reader = com.ExecuteReader();
if (reader.Read())
{
return (int)reader["COUNT"];
}
}
finally
{
if (con.State == System.Data.ConnectionState.Open)
{
con.Close();
}
}
return 0;
}
public HistAtualizReg buscarAtualizLicenca(int id_licenca_cliente)
{
SigletonConexaoFB.carregaStrcnx();
HistAtualizReg resultado = new HistAtualizReg();
FbCommand command = new FbCommand();
FbDataReader reader;
try
{
command.Connection = SigletonConexaoFB.getConexao();
command.CommandText = "SELECT ID,ID_LICENCA_CLIENTE,DATA_ENVIO,QTDE_LICENCA,LIBERADO_ATE,VERSAO_ANIEL " +
"FROM TB_ATUALIZ_REG_ANIEL WHERE ID_LICENCA_CLIENTE=" + id_licenca_cliente.ToString() +
" AND LIBERADO_CLIENTE='" + 'S' + "'";
command.Connection.Open();
reader = command.ExecuteReader();
if (reader.Read())
{
resultado.Id = int.Parse(reader["ID"].ToString());
resultado.Licenca_cliente.Id = int.Parse(reader["ID_LICENCA_CLIENTE"].ToString());
resultado.Data_envio = DateTime.Parse(reader["DATA_ENVIO"].ToString());
resultado.Qtde_licenca = int.Parse(reader["QTDE_LICENCA"].ToString());
resultado.Liberado_ate = DateTime.Parse(reader["LIBERADO_ATE"].ToString());
resultado.Versao_aniel = reader["VERSAO_ANIEL"].ToString();
}
reader.Close();
}
catch (Exception ex)
{
throw new Exception("Erro ao carregar dados do registro do Aniel: " + ex.Message);
}
finally
{
command.Connection.Close();
}
return resultado;
}
public override DataTable GetSchemaTable()
{
this.CheckState();
if (this.schemaTable != null)
{
return(this.schemaTable);
}
#region Variables
DataRow schemaRow = null;
int tableCount = 0;
string currentTable = string.Empty;
this.schemaTable = GetSchemaTableStructure();
const Int16 batchLimit = 90; //Could be adjusted as needed.
Int16 paramCounter = 0; //Counter for the whole batch process
Int16 batchRounds = 0; //counter for each batch (limited by batchlimit)
Hashtable relationList = new Hashtable(); //HashTable to store the query's unique Field Tables Names.
List <RDBTableInfo> fieldList = new List <RDBTableInfo>(this.fields.Count + 1); //List to store the whole statement Schema Field Values.
const Int16 metadataColSize = 31; //Firebird MAX Column Size.
Int16 batchID = 0; //Batch marker. When batchlimit reaches its limit it increases by one the value.
StringBuilder sb = new StringBuilder(); //Stores dynamic generated schema query.
#endregion
// Prepare statement for schema fields information
//Asign current active schema command connection and transaccion
FbCommand schemaCmd = new FbCommand();
schemaCmd.Connection = this.command.Connection;
schemaCmd.Transaction = this.command.Connection.InnerConnection.ActiveTransaction;
for (paramCounter = 0; paramCounter < this.FieldCount; paramCounter++)
{
if (batchRounds >= batchLimit) //Process field params until batch limit is reached.
{
batchID++;
batchRounds = 0;
}
RDBTableInfo rdbinfo = new RDBTableInfo();
rdbinfo.Ordinal = paramCounter;
rdbinfo.FieldName = this.fields[paramCounter].Name;
rdbinfo.RelationName = this.fields[paramCounter].Relation;
rdbinfo.BatchID = batchID;
fieldList.Add(rdbinfo);
batchRounds++;
}
//Process batch schema query
for (Int16 i = 0; i <= batchID; i++)
{
sb.Length = 0;
relationList.Clear();
List <RDBTableInfo> rdblBatch = new List <RDBTableInfo>(this.fields.Count + 1);
//Find all RDBTableInfo elements according to batchID
rdblBatch = fieldList.FindAll(rdbti => rdbti.BatchID == i);
//Just add the needed tables according to the fieldnames on the current batch.
for (Int16 j = 0; j < rdblBatch.Count; j++)
{
//Keep a list of unique relation names (tables) from all the fieldlist.
if (!relationList.ContainsValue(rdblBatch[j].RelationName))
{
relationList.Add(relationList.Count, rdblBatch[j].RelationName);
}
}
if (schemaCmd.Parameters.Count > 0) //Clear previous command parameters.
{
schemaCmd.Parameters.Clear();
}
//Get the Base Squema query to start generating Dynamic Schema query
sb.Append(GetSchemaCommandTextBase());
//Perform batch field query against table schema
//Add relation (table names) to schemaCmd
for (int j = 0; j < relationList.Count; j++)
{
if (j > 0) //More than one table in query statement
{
sb.Append(" OR ");
}
List <RDBTableInfo> tmpList = rdblBatch.FindAll(rdbti => rdbti.RelationName.Equals(relationList[j]));
sb.AppendFormat(" (rfr.rdb$field_name in {0} AND rfr.rdb$relation_name='{1}') ", GetParamExpression(tmpList.Count), relationList[j]);
for (int k = 0; k < tmpList.Count; k++)
{
schemaCmd.Parameters.Add("@COLUMN_NAME", FbDbType.Char, metadataColSize).Value = tmpList[k].FieldName;
}
tmpList = null;
}
//set order to schema query
sb.Append(" ORDER BY rfr.rdb$relation_name, rfr.rdb$field_position");
schemaCmd.CommandText = sb.ToString();
schemaCmd.Prepare();
schemaTable.BeginLoadData();
//Reset Column Values
int Ordinal = 0;
int batchCount = 0;
//perform batch query
using (FbDataReader r = schemaCmd.ExecuteReader())
{
batchCount = 0;//reset batch counter
while (r.Read())
{
rdblBatch[batchCount].isReadOnly = (IsReadOnly(r) || IsExpression(r)) ? true : false;
rdblBatch[batchCount].isKeyColumn = (r.GetInt32(2) == 1) ? true : false;
rdblBatch[batchCount].isUnique = (r.GetInt32(3) == 1) ? true : false;
rdblBatch[batchCount].precision = r.IsDBNull(4) ? -1 : r.GetInt32(4);
rdblBatch[batchCount].isExpression = IsExpression(r);
batchCount++;
}
}
for (int j = 0; j < rdblBatch.Count; j++)
{
Ordinal = rdblBatch[j].Ordinal;
// Create new row for the Schema Table
schemaRow = schemaTable.NewRow();
schemaRow["ColumnName"] = this.GetName(Ordinal);
schemaRow["ColumnOrdinal"] = Ordinal;
schemaRow["ColumnSize"] = this.fields[Ordinal].GetSize();
if (fields[Ordinal].IsDecimal())
{
schemaRow["NumericPrecision"] = schemaRow["ColumnSize"];
if (rdblBatch[j].precision > 0)
{
schemaRow["NumericPrecision"] = rdblBatch[j].precision;
}
schemaRow["NumericScale"] = this.fields[Ordinal].NumericScale * (-1);
}
schemaRow["DataType"] = this.GetFieldType(Ordinal);
schemaRow["ProviderType"] = this.GetProviderType(Ordinal);
schemaRow["IsLong"] = this.fields[Ordinal].IsLong();
schemaRow["AllowDBNull"] = this.fields[Ordinal].AllowDBNull();
schemaRow["IsRowVersion"] = false;
schemaRow["IsAutoIncrement"] = false;
schemaRow["IsReadOnly"] = rdblBatch[j].isReadOnly;
schemaRow["IsKey"] = rdblBatch[j].isKeyColumn;
schemaRow["IsUnique"] = rdblBatch[j].isUnique;
schemaRow["IsAliased"] = this.fields[Ordinal].IsAliased();
schemaRow["IsExpression"] = rdblBatch[j].isExpression;
schemaRow["BaseSchemaName"] = DBNull.Value;
schemaRow["BaseCatalogName"] = DBNull.Value;
schemaRow["BaseTableName"] = this.fields[Ordinal].Relation;
schemaRow["BaseColumnName"] = this.fields[Ordinal].Name;
schemaTable.Rows.Add(schemaRow);
if (!String.IsNullOrEmpty(this.fields[Ordinal].Relation) && currentTable != this.fields[Ordinal].Relation)
{
tableCount++;
currentTable = this.fields[Ordinal].Relation;
}
}
schemaTable.EndLoadData();
rdblBatch = null;
}//Finish Batch Round Iteration
schemaCmd.Close();
if (tableCount > 1)
{
foreach (DataRow row in schemaTable.Rows)
{
row["IsKey"] = false;
row["IsUnique"] = false;
}
}
//Dispose command
schemaCmd.Dispose();
relationList = null;
fieldList = null;
return(schemaTable);
}
public override void CreateOrModify_TablesAndFields(string dataTable, DatasetConfig datasetConfig)
{
if (datasetConfig.DatasetTableName != "") //Add the posibility to use a specific table_name (for using the table more then ones)
{
this.dataTable = datasetConfig.DatasetTableName;
}
else
{
this.dataTable = dataTable;
}
this.datasetConfig = datasetConfig;
this.fieldList = datasetConfig.DatasetConfigRows;
//Look if Table exists, when not, create it!
try
{
string sql = "SELECT * FROM " + dataTable + ";";
myCmd = new FbCommand(sql, myDBConn);
myReader = myCmd.ExecuteReader();
}
catch (FbException ex)
{
/*if (ex.ErrorCode == 0)
* {
* try
* {
* string sql = "CREATE TABLE " + dataTable + " (id INTEGER PRIMARY KEY ASC AUTOINCREMENT); ";
*
* myCmd = new FbCommand(sql, myDBConn);
* myCmd.ExecuteNonQuery();
*
* sql = "SELECT * FROM " + dataTable + ";";
* myCmd = new FbCommand(sql, myDBConn);
* myReader = myCmd.ExecuteReader();
* }
* catch (System.Data.SQLite.SQLiteException ex_ex)
* {
* throw ex_ex;
* }
* }
* else
* {
* throw ex;
* }*/
}
//Look for the Fields, create or alter them!
List <String> existDBFelderliste = new List <string>();
for (int n = 0; n < myReader.FieldCount; n++)
{
existDBFelderliste.Add(myReader.GetName(n));
}
myReader.Close();
foreach (DatasetConfigRow myFeld in fieldList)
{
foreach (string existMyFeld in existDBFelderliste)
{
if (myFeld.DatabaseField.ToLower() == existMyFeld.ToLower())
{
goto nextFeld;
}
}
//Feld existiert nicht -> erzeugen
string sql = "ALTER TABLE " + dataTable + " ADD COLUMN " + myFeld.DatabaseField + " " + myFeld.DatabaseFieldType;
try
{
myCmd = new FbCommand(sql, myDBConn);
myCmd.ExecuteNonQuery();
}
catch (FbException ex)
{
throw ex;
}
nextFeld:
//Irgendeine anweisung, da sonst der Sprung nicht geht...
{
}
}
//Create Insert Command
string wertliste = "", felderliste = "";
foreach (DatasetConfigRow myFeld in fieldList)
{
if (wertliste != "")
{
wertliste += ",";
felderliste += ",";
}
felderliste += myFeld.DatabaseField;
wertliste += "@" + myFeld.DatabaseField;
}
insertCommand = "INSERT INTO " + this.dataTable + "(" + felderliste + ") values(" + wertliste + ")";
}
public override DataTable GetSchemaTable()
{
CheckState();
if (_schemaTable != null)
{
return(_schemaTable);
}
DataRow schemaRow = null;
var tableCount = 0;
var currentTable = string.Empty;
_schemaTable = GetSchemaTableStructure();
/* Prepare statement for schema fields information */
var schemaCmd = new FbCommand(GetSchemaCommandText(), _command.Connection, _command.Connection.InnerConnection.ActiveTransaction);
try
{
schemaCmd.Parameters.Add("@TABLE_NAME", FbDbType.Char, 31);
schemaCmd.Parameters.Add("@COLUMN_NAME", FbDbType.Char, 31);
schemaCmd.Prepare();
_schemaTable.BeginLoadData();
for (var i = 0; i < _fields.Count; i++)
{
var isKeyColumn = false;
var isUnique = false;
var isReadOnly = false;
var precision = 0;
var isExpression = false;
/* Get Schema data for the field */
schemaCmd.Parameters[0].Value = _fields[i].Relation;
schemaCmd.Parameters[1].Value = _fields[i].Name;
var reader = schemaCmd.ExecuteReader(CommandBehavior.Default);
try
{
if (reader.Read())
{
isReadOnly = (IsReadOnly(reader) || IsExpression(reader)) ? true : false;
isKeyColumn = (reader.GetInt32(2) == 1) ? true : false;
isUnique = (reader.GetInt32(3) == 1) ? true : false;
precision = reader.IsDBNull(4) ? -1 : reader.GetInt32(4);
isExpression = IsExpression(reader);
}
}
finally
{
#if NET48 || NETSTANDARD2_0
reader.Dispose();
#else
reader.Dispose();
#endif
}
/* Create new row for the Schema Table */
schemaRow = _schemaTable.NewRow();
schemaRow["ColumnName"] = GetName(i);
schemaRow["ColumnOrdinal"] = i;
schemaRow["ColumnSize"] = _fields[i].GetSize();
if (_fields[i].IsDecimal())
{
schemaRow["NumericPrecision"] = schemaRow["ColumnSize"];
if (precision > 0)
{
schemaRow["NumericPrecision"] = precision;
}
schemaRow["NumericScale"] = _fields[i].NumericScale * (-1);
}
schemaRow["DataType"] = GetFieldType(i);
schemaRow["ProviderType"] = GetProviderType(i);
schemaRow["IsLong"] = _fields[i].IsLong();
schemaRow["AllowDBNull"] = _fields[i].AllowDBNull();
schemaRow["IsRowVersion"] = false;
schemaRow["IsAutoIncrement"] = false;
schemaRow["IsReadOnly"] = isReadOnly;
schemaRow["IsKey"] = isKeyColumn;
schemaRow["IsUnique"] = isUnique;
schemaRow["IsAliased"] = _fields[i].IsAliased();
schemaRow["IsExpression"] = isExpression;
schemaRow["BaseSchemaName"] = DBNull.Value;
schemaRow["BaseCatalogName"] = DBNull.Value;
schemaRow["BaseTableName"] = _fields[i].Relation;
schemaRow["BaseColumnName"] = _fields[i].Name;
_schemaTable.Rows.Add(schemaRow);
if (!string.IsNullOrEmpty(_fields[i].Relation) && currentTable != _fields[i].Relation)
{
tableCount++;
currentTable = _fields[i].Relation;
}
schemaCmd.Close();
}
if (tableCount > 1)
{
foreach (DataRow row in _schemaTable.Rows)
{
row["IsKey"] = false;
row["IsUnique"] = false;
}
}
_schemaTable.EndLoadData();
}
finally
{
#if NET48 || NETSTANDARD2_0
schemaCmd.Dispose();
#else
schemaCmd.Dispose();
#endif
}
return(_schemaTable);
}
public override DataTable GetSchemaTable()
{
this.CheckState();
if (this.schemaTable != null)
{
return(this.schemaTable);
}
DataRow schemaRow = null;
int tableCount = 0;
string currentTable = string.Empty;
this.schemaTable = this.GetSchemaTableStructure();
/* Prepare statement for schema fields information */
FbCommand schemaCmd = new FbCommand(
this.GetSchemaCommandText(),
this.command.Connection,
this.command.Connection.InnerConnection.ActiveTransaction);
schemaCmd.Parameters.Add("@TABLE_NAME", FbDbType.Char, 31);
schemaCmd.Parameters.Add("@COLUMN_NAME", FbDbType.Char, 31);
schemaCmd.Prepare();
schemaTable.BeginLoadData();
for (int i = 0; i < this.fields.Count; i++)
{
bool isKeyColumn = false;
bool isUnique = false;
bool isReadOnly = false;
int precision = 0;
bool isExpression = false;
/* Get Schema data for the field */
schemaCmd.Parameters[0].Value = this.fields[i].Relation;
schemaCmd.Parameters[1].Value = this.fields[i].Name;
using (FbDataReader r = schemaCmd.ExecuteReader())
{
if (r.Read())
{
isReadOnly = (this.IsReadOnly(r) || this.IsExpression(r)) ? true : false;
isKeyColumn = (r.GetInt32(2) == 1) ? true : false;
isUnique = (r.GetInt32(3) == 1) ? true : false;
precision = r.IsDBNull(4) ? -1 : r.GetInt32(4);
isExpression = this.IsExpression(r);
}
}
/* Create new row for the Schema Table */
schemaRow = schemaTable.NewRow();
schemaRow["ColumnName"] = this.GetName(i);
schemaRow["ColumnOrdinal"] = i;
schemaRow["ColumnSize"] = this.fields[i].GetSize();
if (fields[i].IsDecimal())
{
schemaRow["NumericPrecision"] = schemaRow["ColumnSize"];
if (precision > 0)
{
schemaRow["NumericPrecision"] = precision;
}
schemaRow["NumericScale"] = this.fields[i].NumericScale * (-1);
}
schemaRow["DataType"] = this.GetFieldType(i);
schemaRow["ProviderType"] = this.GetProviderType(i);
schemaRow["IsLong"] = this.fields[i].IsLong();
schemaRow["AllowDBNull"] = this.fields[i].AllowDBNull();
schemaRow["IsRowVersion"] = false;
schemaRow["IsAutoIncrement"] = false;
schemaRow["IsReadOnly"] = isReadOnly;
schemaRow["IsKey"] = isKeyColumn;
schemaRow["IsUnique"] = isUnique;
schemaRow["IsAliased"] = this.fields[i].IsAliased();
schemaRow["IsExpression"] = isExpression;
schemaRow["BaseSchemaName"] = DBNull.Value;
schemaRow["BaseCatalogName"] = DBNull.Value;
schemaRow["BaseTableName"] = this.fields[i].Relation;
schemaRow["BaseColumnName"] = this.fields[i].Name;
schemaTable.Rows.Add(schemaRow);
if (!String.IsNullOrEmpty(this.fields[i].Relation) && currentTable != this.fields[i].Relation)
{
tableCount++;
currentTable = this.fields[i].Relation;
}
/* Close statement */
schemaCmd.Close();
}
if (tableCount > 1)
{
foreach (DataRow row in schemaTable.Rows)
{
row["IsKey"] = false;
row["IsUnique"] = false;
}
}
schemaTable.EndLoadData();
/* Dispose command */
schemaCmd.Dispose();
return(schemaTable);
}
