public void Clone()
{
var connString = new NpgsqlConnectionStringBuilder(ConnectionString)
{
Pooling = false
}.ToString();
using (var conn = new NpgsqlConnection(connString))
{
ProvideClientCertificatesCallback callback1 = certificates => { };
conn.ProvideClientCertificatesCallback = callback1;
RemoteCertificateValidationCallback callback2 = (sender, certificate, chain, errors) => true;
conn.UserCertificateValidationCallback = callback2;
conn.Open();
#if NETCOREAPP1_1
using (var conn2 = conn.Clone())
#else
using (var conn2 = (NpgsqlConnection)((ICloneable)conn).Clone())
#endif
{
Assert.That(conn2.ConnectionString, Is.EqualTo(conn.ConnectionString));
Assert.That(conn2.ProvideClientCertificatesCallback, Is.SameAs(callback1));
Assert.That(conn2.UserCertificateValidationCallback, Is.SameAs(callback2));
conn2.Open();
}
}
}
static void BeginConvert(dynamic conf)
{
NpgsqlConnection pgConnection = new NpgsqlConnection(conf.pgConnection);
pgConnection.Open();
Console.WriteLine("pgServer:\r\nHost: {0} ({1})", pgConnection.Host, pgConnection.Port);
Console.WriteLine("Версия: {0}\r\nБаза данных: {1}", pgConnection.ServerVersion, pgConnection.Database);
foreach (var table in conf.DBFTables)
{
ProcessConvert(table, pgConnection);
}
if (conf.skipVacuum == null)
{
var vacuum = pgConnection.CreateCommand();
Console.WriteLine("VACUUM FULL! Может занять продолжительное время");
vacuum.CommandText = "VACUUM FULL;";
vacuum.CommandTimeout = 60 * 60;//1 hour
vacuum.ExecuteNonQuery();
}
if (conf.execAfter != null)
{
foreach (var script in conf.execAfter)
{
var command = pgConnection.CreateCommand();
Console.WriteLine("SCRIPT: {0}", script);
command.CommandText = script;
command.CommandTimeout = 60 * 60;//1 hour
command.ExecuteNonQuery();
}
}
pgConnection.Clone();
}
//Get a connection
public static void ReturnPgsConn(ref NpgsqlConnection pPgsConn)
{
try
{
pPgsConn = m_pPgsConn.Clone();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "ReturnPgsConn");
}
}
internal ReadOnlyCollection <NpgsqlDbColumn> GetColumnSchema()
{
var fields = _rowDescription.Fields;
if (fields.Count == 0)
{
return(new List <NpgsqlDbColumn>().AsReadOnly());
}
var result = new List <NpgsqlDbColumn>(fields.Count);
for (var i = 0; i < fields.Count; i++)
{
result.Add(null);
}
var populatedColumns = 0;
// We have two types of fields - those which correspond to actual database columns
// and those that don't (e.g. SELECT 8). For the former we load lots of info from
// the backend (if fetchAdditionalInfo is true), for the latter we only have the RowDescription
var columnFieldFilter = _rowDescription.Fields
.Where(f => f.TableOID != 0) // Only column fields
.Select(c => $"(attr.attrelid={c.TableOID} AND attr.attnum={c.ColumnAttributeNumber})")
.Join(" OR ");
if (_fetchAdditionalInfo && columnFieldFilter != "")
{
var query = _connection.PostgreSqlVersion >= new Version(8, 2)
? GenerateColumnsQuery(columnFieldFilter)
: GenerateOldColumnsQuery(columnFieldFilter);
#if NETSTANDARD1_3
using (var connection = _connection.Clone())
#else
using (new TransactionScope(TransactionScopeOption.Suppress))
using (var connection = (NpgsqlConnection)((ICloneable)_connection).Clone())
#endif
{
connection.Open();
using (var cmd = new NpgsqlCommand(query, connection))
using (var reader = cmd.ExecuteReader())
{
for (; reader.Read(); populatedColumns++)
{
var column = LoadColumnDefinition(reader, _connection.Connector.TypeHandlerRegistry);
var ordinal = fields.FindIndex(f => f.TableOID == column.TableOID && f.ColumnAttributeNumber - 1 == column.ColumnAttributeNumber);
Debug.Assert(ordinal >= 0);
// The column's ordinal is with respect to the resultset, not its table
column.ColumnOrdinal = ordinal;
result[ordinal] = column;
}
}
if (populatedColumns == fields.Count)
{
// All columns were regular table columns that got loaded, we're done
Debug.Assert(result.All(c => c != null));
return(result.AsReadOnly());
}
}
}
// We had some fields which don't correspond to regular table columns (or fetchAdditionalInfo is false).
// Fill in whatever info we have from the RowDescription itself
for (var i = 0; i < fields.Count; i++)
{
if (result[i] != null)
{
continue;
}
var column = SetUpNonColumnField(fields[i]);
column.ColumnOrdinal = i;
result[i] = column;
populatedColumns++;
}
if (populatedColumns != fields.Count)
{
throw new NpgsqlException("Could not load all columns for the resultset");
}
return(result.AsReadOnly());
}
private void button1_Click(object sender, EventArgs e)
{
Dados_nfe DadosNfe = new Dados_nfe();
string html = null;
//STRING DE CONEXÃO
NpgsqlConnection conn = new NpgsqlConnection(connstring);
try
{
conn.Open();
}
catch (Exception msg)
{
MessageBox.Show("Não foi possivel conectar com o Banco de Dados ! \n" + msg.ToString(), "Erro", MessageBoxButtons.OK, ErrorIcon);
return;
}
//VERIFICAÇÃO PARA SABE SE EXISTEM DADOS A SEREM IMPORTADOS
if (webBrowser1.Url.ToString() != "http://www.nfe.fazenda.gov.br/portal/consulta.aspx?tipoConsulta=completa&tipoConteudo=XbSeqxE8pl8=")
{
try
{
webBrowser1.Navigate("http://www.nfe.fazenda.gov.br/portal/consultaImpressao.aspx?tipoConsulta=completa");
while (webBrowser1.ReadyState != WebBrowserReadyState.Complete)
{
Application.DoEvents();
}
//CORREÇÃO DE ENCODING DO CONTEUDO HTML RETORNADO
Stream documentStream = webBrowser1.DocumentStream;
StreamReader reader = new StreamReader(documentStream, Encoding.GetEncoding(28591));
documentStream.Position = 0;
html = reader.ReadToEnd();
//Metodo para ler os valores da Nota fiscal utilizando a Classe Dados_Nfe
DadosNfe.LerValoresNota(html);
NpgsqlCommand valida = new NpgsqlCommand("SELECT cli_nm_cliente, cli_id_cliente FROM " + schema + "bpm_cli_cliente where cli_cnpj = '" + DadosNfe._CnpjDest + "' AND cli_id_cliente NOT LIKE '%NFS%' ", conn);
//NpgsqlDataReader sel = valida.ExecuteReader();
string sel;
try //BLOCO PARA VALIDAR SE O CLIENTE E/OU A NOTA FISCAL ESTÃO CADASTRADOS NO BANCO DE DADOS
{
sel = (string)valida.ExecuteScalar();
if (sel == null) //SE O RETORNO DO SELECT QUE VERIFICA O CNPJ DO DESTINATARIO FOR NULO, ENTÃO A EMPRESA NÃO ESTÁ CADASTRADA NA BASE
// E NÃO REALIZO O INSERT.
{
webBrowser1.GoBack();
InternetSetOption(IntPtr.Zero, INTERNET_OPTION_END_BROWSER_SESSION, IntPtr.Zero, 0); //COM ESSE COMANDO EU ENCERRO A SESSÃO DO NAVEGADOR, SE MANTER A SESSÃO ABERTA, OCORRE ERROS NA CHAVE.
MessageBox.Show("Empresa não cadastrada", "Aviso", MessageBoxButtons.OK, ErrorIcon);
//Resetando dados da Classe para realizar parse para as proximas notas.
DadosNfe.ResetaDados();
}
else
{
NpgsqlDataReader sel_dat = valida.ExecuteReader();
while (sel_dat.Read()) //REALIZO NOVAMENTE O SELECT DESSA VEZ COM O ExecuteReader() QUE TRARA OS DOIS CAMPOS NUMERO DA EMPRESA E NOME
//USO ESSES CAMPOS PARA VALIDAÇÃO E PARA INSERT NO BANCO.
{
string nr_empresa = sel_dat[0].ToString().Trim();
string nm_empresa = sel_dat[1].ToString().Trim();
NpgsqlCommand valida_nf = new NpgsqlCommand("SELECT HNFE_NR_NF FROM " + schema + "header_nfe_thor WHERE BPM_ID_CLIENTE = '" + sel_dat[1].ToString().Trim() + "' AND HNFE_CHAVE = '" + DadosNfe._NfChave + "'", conn);
conn.Close(); //É NECESSARIO FECHAR A CONEXÃO E REABRIR PARA REALIZAR UM SELECT DENTRO DO OUTRO;
conn.Open();
string sel_nf = (string)valida_nf.ExecuteScalar();
conn.Close();
if (sel_nf == null) //SE O RETORNO DO SELECT FOR NULO, A NOTA FISCAL NÃO EXISTE NA BASE, E COM ISSO EU REALIZO OS INSERTS
{
string Parametro_cfop = null;
string Parametro_data = null;
if (chbx_cfop.Checked != true && txt_cfop.Text.Length != 0)
{
Parametro_cfop = txt_cfop.Text;
}
if (txt_data_ref.Text != " /" && txt_data_ref.Text != " -")
{
Parametro_data = txt_data_ref.Text;
}
string InsertHeaderCommand = DadosNfe.SQLInsertHeader(html, Parametro_data, schema, nr_empresa, nm_empresa);
string InsertDetailCommand = DadosNfe.SQLInsertDetail(html, Parametro_cfop, schema, nr_empresa, Parametro_data, nm_empresa);
string InsertFornecedorCommand = DadosNfe.SQLInsertFornecedor(html, schema, nr_empresa, nm_empresa);
string[,] SQLInsertProdutosCommand = DadosNfe.SQLInsertProdutos(html, schema, nr_empresa, nm_empresa);
using (TransactionScope Transaction = new TransactionScope()){
//Abrinco conexão com o Banco
conn.Open();
//Inserindo Header
NpgsqlCommand InsertHeader = new NpgsqlCommand(InsertHeaderCommand, conn);
object resp_InsertHeader = InsertHeader.ExecuteScalar();
//Antes de Inserir o Fornecedore, verifico se o mesmo já existe na Base.
NpgsqlCommand valida_forn = new NpgsqlCommand("SELECT forn_cgc FROM " + schema + "CAD_FORNEC WHERE BPM_ID_CLIENTE = '" + nm_empresa + "' AND forn_cod_cli = '" + DadosNfe._CnpjEmit + "'", conn);
string sel_forn = (string)valida_forn.ExecuteScalar();
if (sel_forn == null)
{
NpgsqlCommand InsertFornecedor = new NpgsqlCommand(InsertFornecedorCommand, conn);
object resp_InsertFornecedor = InsertFornecedor.ExecuteScalar();
}
//Inserindo Detail
NpgsqlCommand InsertDetail = new NpgsqlCommand(InsertDetailCommand, conn);
object resp_InsertDetail = InsertDetail.ExecuteScalar();
//Antes de inserir os produtos com a query retornada pelo metodo da classe, verifico se o mesmo já exste.
for (int x = 0; x < (SQLInsertProdutosCommand.Length / 2); x++)
{
NpgsqlCommand valida_prod = new NpgsqlCommand("SELECT prod_cod FROM " + schema + "cad_produtos WHERE BPM_ID_CLIENTE = '" + nm_empresa + "' AND prod_cod = '" + SQLInsertProdutosCommand[x, 0] + "'", conn);
string select_prod = (string)valida_prod.ExecuteScalar();
if (select_prod == null)
{
NpgsqlCommand InsertProdutos = new NpgsqlCommand(SQLInsertProdutosCommand[x, 1], conn);
object resp_InsertProdutos = InsertProdutos.ExecuteScalar();
}
}
//Resetando os dados da Classe para realizar o parse para outras Notas.
DadosNfe.ResetaDados();
webBrowser1.GoBack();
while (webBrowser1.ReadyState != WebBrowserReadyState.Complete)
{
Application.DoEvents();
}
InternetSetOption(IntPtr.Zero, INTERNET_OPTION_END_BROWSER_SESSION, IntPtr.Zero, 0);
MessageBox.Show("Nota inserida corretamente", "Aviso", MessageBoxButtons.OK, InfoIcon);
Transaction.Complete();
}
}
else //CASO O SELECT RETORNE ALGO, EU INFORMO QUE A NOTA JÁ EXISTE NA BASE DE DADOS.
{
webBrowser1.GoBack();
while (webBrowser1.ReadyState != WebBrowserReadyState.Complete)
{
Application.DoEvents();
}
MessageBox.Show("Nota já existe na base de dados", "Aviso", MessageBoxButtons.OK, WarningIcon);
InternetSetOption(IntPtr.Zero, INTERNET_OPTION_END_BROWSER_SESSION, IntPtr.Zero, 0);
conn.Close();
//Resetando dados da Classe para realizar parse para as proximas notas.
DadosNfe.ResetaDados();
}
}
}
}
finally
{
conn.Clone();
}
}
catch (Exception)
{
MessageBox.Show("Erro ao tentar ler os dados do site da receita,\n tente novamente ou entre em contato com o administrador!", "Erro", MessageBoxButtons.OK, ErrorIcon);
webBrowser1.GoBack();
InternetSetOption(IntPtr.Zero, INTERNET_OPTION_END_BROWSER_SESSION, IntPtr.Zero, 0);
conn.Close();
}
}
else
{
MessageBox.Show("Não foram encontrados dados para importar, por favor insira a chave e o captcha para importar os dados", "Aviso", MessageBoxButtons.OK, WarningIcon);
return;
}
webBrowser1.Focus();
}
internal ReadOnlyCollection <NpgsqlDbColumn> GetColumnSchema()
{
var fields = _rowDescription.Fields;
if (fields.Count == 0)
{
return(new List <NpgsqlDbColumn>().AsReadOnly());
}
var result = new List <NpgsqlDbColumn>(fields.Count);
for (var i = 0; i < fields.Count; i++)
{
result.Add(null);
}
var populatedColumns = 0;
// We have two types of fields - those which correspond to actual database columns
// and those that don't (e.g. SELECT 8). For the former we load lots of info from
// the backend, for the latter we only have the RowDescription
var columnFieldFilter = _rowDescription.Fields
.Where(f => f.TableOID != 0) // Only column fields
.Select(c => $"(attr.attrelid={c.TableOID} AND attr.attnum={c.ColumnAttributeNumber})")
.Join(" OR ");
if (columnFieldFilter != "")
{
var query = string.Format(GetColumnsQuery, columnFieldFilter);
#if NET45 || NET451
using (var connection = (NpgsqlConnection)((ICloneable)_connection).Clone())
#else
using (var connection = _connection.Clone())
#endif
{
connection.Open();
using (var cmd = new NpgsqlCommand(query, connection))
using (var reader = cmd.ExecuteReader())
{
for (; reader.Read(); populatedColumns++)
{
var column = LoadColumnDefinition(reader);
var ordinal = fields.FindIndex(f => f.TableOID == column.TableOID && f.ColumnAttributeNumber - 1 == column.ColumnAttributeNumber);
Contract.Assert(ordinal >= 0);
var field = fields[ordinal];
Contract.Assert(field.Name == column.ColumnName);
// The column's ordinal is with respect to the resultset, not its table
column.ColumnOrdinal = ordinal;
// Overwrite the column's DataType because the field provides more information about it
column.DataType = field.FieldType;
result[ordinal] = column;
}
}
if (populatedColumns == fields.Count)
{
// All columns were regular table columns that got loaded, we're done
Contract.Assert(result.All(c => c != null));
return(result.AsReadOnly());
}
}
}
// We had some fields which don't correspond to regular table columns
// Fill in whatever info we have from the RowDescription itself
for (var i = 0; i < fields.Count; i++)
{
if (result[i] != null)
{
continue;
}
var column = SetUpNonColumnField(fields[i]);
column.ColumnOrdinal = i;
result[i] = column;
populatedColumns++;
}
if (populatedColumns != fields.Count)
{
throw new NpgsqlException("Could not load all columns for the resultset");
}
return(result.AsReadOnly());
}
private void btnInstall_Click(object sender, EventArgs e)
{
if (ValidateInfo())
{
pbDBInstall.Select();
btnInstall.Visible = false;
btnBack.Visible = false;
lblWaitingtxt.Visible = true;
pbDBInstall.Visible = true;
Application.DoEvents();
Thread.Sleep(1000);
if (CreateNg)
{
pbDBInstall.Maximum = 11;
}
try
{
string superDBConnectionString = Config.GetSuperConnectionString();
string sysDBConnectionString = Config.GetSysDBConnectionString();
string projectDBConnectionString = Config.GetProjectDBConnectionString(ProjectDBName);
string changeOwnerConnectionString = Config.GetChangeOwnerConnectionString(ProjectDBName);
//create role
DataTable dtRole = _dbInstall.CheckRoleExist(superDBConnectionString, ProjectDBName.ToUpper());
if (dtRole.Rows.Count <= 0)
{
_dbInstall.CreateRole(superDBConnectionString, ProjectDBName.ToUpper());
}
ProgressBarUpdate();
LogWrite("User Role Installed.");
//create database
bool createDb = true;
DataTable dtDB = _dbInstall.CheckDatabaseExist(superDBConnectionString, ProjectDBName.ToUpper());
if (dtDB.Rows.Count > 0)
{
if (Config.Host == "37.34.53.43")
{
MessageBox.Show("Database already exist");
pbDBInstall.Value = 0;
pbDBInstall.Visible = false;
lblWaitingtxt.Visible = false;
btnInstall.Visible = true;
btnBack.Visible = true;
return;
}
if (MessageBox.Show("Database already exist. Sure to replace it?", "Replace", MessageBoxButtons.YesNo) == System.Windows.Forms.DialogResult.Yes)
{
if (!_dbInstall.DeleteDB(Config.GetSuperConnectionString(), ProjectDBName.ToUpper()))
{
success = false;
ErrorMsg = string.Format("Can not delete database: {0}. Please delete the database manually and run installer again.", ProjectDBName);
OpenSummaryWindow();
return;
}
}
else
{
createDb = false;
}
}
if (createDb)
{
_dbInstall.CreateDatabase(superDBConnectionString, ProjectDBName.ToUpper(), Config.TemplateDBName);
}
ProgressBarUpdate();
LogWrite("Database Created.");
//chnage owner
_dbInstall.ChangeOwner(changeOwnerConnectionString, ProjectDBName.ToUpper());
ProgressBarUpdate();
LogWrite("Owner is changed.");
using (NpgsqlConnection pgConn = new NpgsqlConnection(sysDBConnectionString))
{
pgConn.Open();
using (NpgsqlTransaction tran = pgConn.BeginTransaction())
{
// Sys
_dbInstall.Sys_Delete_Prev_Data(sysDBConnectionString, "Sys_BGT_DB\\1BGT_Sys_DB_Script_DELETE.sql", ProjectDBName, sysSuperUserId, TeamleadUserID, GuestUserID);
ProgressBarUpdate();
LogWrite("Sys table is clean.");
_dbInstall.Sys_Install_Role_Independent_Data(sysDBConnectionString, "Sys_BGT_DB\\2BGT_Sys_DB_Script_Role_Independend_Entry-Reports.sql", ProjectDBName, Config.Port);
LogWrite("Sys Role independent entry-reports data installed.");
ProgressBarUpdate();
_dbInstall.Sys_Install_Role_Independent_Data(sysDBConnectionString, "Sys_BGT_DB\\3BGT_Sys_DB_Script_Role_Independend-Report_Functions.sql", ProjectDBName, Config.Port);
LogWrite("Role independent report functions installed.");
ProgressBarUpdate();
_dbInstall.Sys_Install_Role_Independent_Data(sysDBConnectionString, "Sys_BGT_DB\\4BGT_Sys_DB_Script_Role_Independend-Metadata.sql", ProjectDBName, Config.Port);
ProgressBarUpdate();
LogWrite("Role independent metadata installed.");
_dbInstall.Sys_Install_SuperRole(sysDBConnectionString, "Sys_BGT_DB\\5BGT_Sys_DB_Script_SUPER.sql", ProjectDBName, sysSuperUserName, sysSuperUserId, sysSuperUserPassword);
LogWrite("Super Role Installed.");
_dbInstall.Sys_Install_TeamleadRole(sysDBConnectionString, "Sys_BGT_DB\\5BGT_Sys_DB_Script_Teamlead.sql", ProjectDBName, TeamleadUserName, TeamleadUserID, TeamleadUserPassword);
LogWrite("Teamlead Role Installed.");
_dbInstall.Sys_Install_GuestRole(sysDBConnectionString, "Sys_BGT_DB\\5BGT_Sys_DB_Script_Guest.sql", ProjectDBName, GuestUserName, GuestUserID, GuestUserPassword);
LogWrite("Guest Role Installed.");
//dsah-board entry
_dbInstall.Sys_Install_Role_Independent_Data(sysDBConnectionString, "Sys_BGT_DB\\6BGT_Sys_DB_Script_Dashboard.sql", ProjectDBName, Config.Port);
LogWrite("Dashboard installed.");
ProgressBarUpdate();
tran.Commit();
pgConn.Clone();
}
}
// Project
_dbInstall.Install_ProjectData(projectDBConnectionString, "AOXX_DB\\1Tables.sql", ProjectDBName, Organization, MethodIndex, MethodName, Precision, Date);
_dbInstall.Install_ProjectData(projectDBConnectionString, "AOXX_DB\\2Tables_Index.sql", ProjectDBName, Organization, MethodIndex, MethodName, Precision, Date);
_dbInstall.Install_ProjectData(projectDBConnectionString, "AOXX_DB\\3Tables_Trigger.sql", ProjectDBName, Organization, MethodIndex, MethodName, Precision, Date);
_dbInstall.Install_ProjectData(projectDBConnectionString, "AOXX_DB\\4Tables_values.sql", ProjectDBName, Organization, MethodIndex, MethodName, Precision, Date,
Bronhouder, ApplicationName, Administration, ReferencePrefix, IdentificationPrefix, MsgToelichting);
_dbInstall.Install_ProjectData(projectDBConnectionString, "AOXX_DB\\5Functions.sql", ProjectDBName, Organization, MethodIndex, MethodName, Precision, Date);
_dbInstall.Install_ProjectData(projectDBConnectionString, "AOXX_DB\\6Views.sql", ProjectDBName, Organization, MethodIndex, MethodName, Precision, Date);
_dbInstall.Install_ProjectData(projectDBConnectionString, "AOXX_DB\\7GT_Views.sql", ProjectDBName, Organization, MethodIndex, MethodName, Precision, Date);
ProgressBarUpdate();
LogWrite("Project data installed.");
//run ned graphics db script
if (CreateNg)
{
using (NpgsqlConnection pgConn = new NpgsqlConnection(sysDBConnectionString))
{
pgConn.Open();
using (NpgsqlTransaction tran = pgConn.BeginTransaction())
{
_dbInstall.Install_NedGraphics_Sys(sysDBConnectionString, "NG\\NedGraphics_ChaloIS_SYS_TABLES_SuperUser.sql", ProjectDBName, Config.Port);
ProgressBarUpdate();
LogWrite("Ned Graphics Sys data installed.");
_dbInstall.Install_NedGraphics_Projects(projectDBConnectionString, "NG\\NedGraphics_Script.sql", ProjectDBName);
ProgressBarUpdate();
LogWrite("Ned Graphics Project data installed.");
tran.Commit();
pgConn.Close();
}
}
}
success = true;
LogWrite("Installation is successful.");
}
catch (Exception ex)
{
LogWrite(ex.Message.ToString());
success = false;
ErrorMsg = ex.Message;
RollBack();
}
OpenSummaryWindow();
pbDBInstall.Value = 0;
}
}
internal NpgsqlConnection GetConnection()
{
return(Connection.Clone());
}
public NpgsqlConnection GetConnection()
{
return(Connection.Clone());
}
public void Desconectar()
{
conexao.Clone();
conexao.Dispose();
}
