private static void CreateTables(string connectionString)
{
FbConnection connection = new FbConnection(connectionString);
connection.Open();
StringBuilder commandText = new StringBuilder();
// Table for general purpouse tests
commandText.Append("CREATE TABLE TEST (");
commandText.Append("INT_FIELD INTEGER DEFAULT 0 NOT NULL PRIMARY KEY,");
commandText.Append("CHAR_FIELD CHAR(30),");
commandText.Append("VARCHAR_FIELD VARCHAR(100),");
commandText.Append("BIGINT_FIELD BIGINT,");
commandText.Append("SMALLINT_FIELD SMALLINT,");
commandText.Append("DOUBLE_FIELD DOUBLE PRECISION,");
commandText.Append("FLOAT_FIELD FLOAT,");
commandText.Append("NUMERIC_FIELD NUMERIC(15,2),");
commandText.Append("DECIMAL_FIELD DECIMAL(15,2),");
commandText.Append("DATE_FIELD DATE,");
commandText.Append("TIME_FIELD TIME,");
commandText.Append("TIMESTAMP_FIELD TIMESTAMP,");
commandText.Append("CLOB_FIELD BLOB SUB_TYPE 1 SEGMENT SIZE 80,");
commandText.Append("BLOB_FIELD BLOB SUB_TYPE 0 SEGMENT SIZE 80,");
commandText.Append("IARRAY_FIELD INTEGER [0:3],");
commandText.Append("SARRAY_FIELD SMALLINT [0:4],");
commandText.Append("LARRAY_FIELD BIGINT [0:5],");
commandText.Append("FARRAY_FIELD FLOAT [0:3],");
commandText.Append("BARRAY_FIELD DOUBLE PRECISION [1:4],");
commandText.Append("NARRAY_FIELD NUMERIC(10,6) [1:4],");
commandText.Append("DARRAY_FIELD DATE [1:4],");
commandText.Append("TARRAY_FIELD TIME [1:4],");
commandText.Append("TSARRAY_FIELD TIMESTAMP [1:4],");
commandText.Append("CARRAY_FIELD CHAR(21) [1:4],");
commandText.Append("VARRAY_FIELD VARCHAR(30) [1:4],");
commandText.Append("BIG_ARRAY INTEGER [1:32767],");
commandText.Append("EXPR_FIELD COMPUTED BY (smallint_field * 1000),");
commandText.Append("CS_FIELD CHAR(1) CHARACTER SET UNICODE_FSS,");
commandText.Append("UCCHAR_ARRAY CHAR(10) [1:10] CHARACTER SET UNICODE_FSS);");
FbCommand command = new FbCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
connection.Close();
}
public int Save(int?COD_MUN_IBGE, int COD_UF_IBGE, string MUNICIPIO)
{
int result = 0;
try
{
//Verificando a existência de um transação aberta
if (dbTransaction != null)
{
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Sav_MUNICIPIOS", dbCnn);
dbCommand.Transaction = ((FbTransaction)(dbTransaction));
}
else
{
if (dbCnn == null)
{
dbCnn = ((FbConnection)GetConnectionDB());
}
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Sav_MUNICIPIOS", dbCnn);
dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted);
}
dbCommand.CommandType = CommandType.StoredProcedure;
if (COD_MUN_IBGE != -1)
{
dbCommand.Parameters.AddWithValue("@COD_MUN_IBGE", COD_MUN_IBGE); //PrimaryKey
}
else
{
dbCommand.Parameters.AddWithValue("@COD_MUN_IBGE", DBNull.Value); //PrimaryKey
}
dbCommand.Parameters.AddWithValue("@COD_UF_IBGE", COD_UF_IBGE); //Coluna
dbCommand.Parameters.AddWithValue("@MUNICIPIO", MUNICIPIO); //Coluna
//Retorno da Procedure
FbParameter returnValue;
returnValue = dbCommand.CreateParameter();
dbCommand.Parameters["@COD_MUN_IBGE"].Direction = ParameterDirection.InputOutput;
//Executando consulta
dbCommand.ExecuteNonQuery();
result = int.Parse(dbCommand.Parameters["@COD_MUN_IBGE"].Value.ToString());
if (dbTransaction == null)
{
dbCommand.Transaction.Commit();
dbCnn.Close();
}
}
catch (Exception ex)
{
if (dbTransaction != null)
{
this.RollbackTransaction();
}
else
{
if (dbCommand.Transaction != null)
{
dbCommand.Transaction.Rollback();
}
if (dbCnn.State == ConnectionState.Open)
{
dbCnn.Close();
}
}
throw ex;
}
return(result);
}
public MUNICIPIOSEntity Read(int COD_MUN_IBGE)
{
FbDataReader reader = null;
try
{
//Verificando a existência de um transação aberta
if (dbTransaction != null)
{
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Rea_MUNICIPIOS", dbCnn);
dbCommand.Transaction = ((FbTransaction)(dbTransaction));
}
else
{
if (dbCnn == null)
{
dbCnn = ((FbConnection)GetConnectionDB());
}
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Rea_MUNICIPIOS", dbCnn);
dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted);
}
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.Parameters.AddWithValue("@COD_MUN_IBGE", COD_MUN_IBGE); //PrimaryKey
reader = dbCommand.ExecuteReader();
MUNICIPIOSEntity entity = null;
if (reader.HasRows)
{
while (reader.Read())
{
entity = FillEntityObject(ref reader);
}
}
// Deleta reader
if (reader != null)
{
reader.Close();
reader.Dispose();
}
// Fecha conexão
if (dbTransaction == null)
{
dbCommand.Transaction.Commit();
if (dbCnn.State == ConnectionState.Open)
{
dbCnn.Close();
}
}
return(entity);
}
catch (Exception ex)
{
// Deleta reader
if (reader != null)
{
reader.Close();
reader.Dispose();
}
if (dbTransaction != null)
{
this.RollbackTransaction();
}
else
{
if (dbCommand.Transaction != null)
{
dbCommand.Transaction.Rollback();
}
if (dbCnn.State == ConnectionState.Open)
{
dbCnn.Close();
}
}
throw ex;
}
}
public int Save(int?IDPRODUTOSMANUT, int IDMANUTEESQUIPAMENTO, int IDPRODUTO, decimal QUANTIDADE, decimal VALORUNITARIO, decimal VALORTOTAL, decimal COMISSAO)
{
int result = 0;
try
{
//Verificando a existência de um transação aberta
if (dbTransaction != null)
{
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Sav_PRODUTOSMANUT", dbCnn);
dbCommand.Transaction = ((FbTransaction)(dbTransaction));
}
else
{
if (dbCnn == null)
{
dbCnn = ((FbConnection)GetConnectionDB());
}
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Sav_PRODUTOSMANUT", dbCnn);
dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted);
}
dbCommand.CommandType = CommandType.StoredProcedure;
//PrimaryKey com valor igual a null, indica um novo registro,
//o valor da chave será fornecido pelo banco. Qualquer outro valor indicará edição do registro.
if (IDPRODUTOSMANUT == -1)
{
dbCommand.Parameters.AddWithValue("@IDPRODUTOSMANUT", DBNull.Value);
}
else
{
dbCommand.Parameters.AddWithValue("@IDPRODUTOSMANUT", IDPRODUTOSMANUT);
}
if (IDMANUTEESQUIPAMENTO != null)
{
dbCommand.Parameters.AddWithValue("@IDMANUTEESQUIPAMENTO", IDMANUTEESQUIPAMENTO); //ForeignKey
}
else
{
dbCommand.Parameters.AddWithValue("@IDMANUTEESQUIPAMENTO", DBNull.Value); //ForeignKey 5
}
if (IDPRODUTO != null)
{
dbCommand.Parameters.AddWithValue("@IDPRODUTO", IDPRODUTO); //ForeignKey
}
else
{
dbCommand.Parameters.AddWithValue("@IDPRODUTO", DBNull.Value); //ForeignKey 5
}
dbCommand.Parameters.AddWithValue("@QUANTIDADE", QUANTIDADE); //Coluna
dbCommand.Parameters.AddWithValue("@VALORUNITARIO", VALORUNITARIO); //Coluna
dbCommand.Parameters.AddWithValue("@VALORTOTAL", VALORTOTAL); //Coluna
dbCommand.Parameters.AddWithValue("@COMISSAO", COMISSAO); //Coluna
//Retorno da Procedure
FbParameter returnValue;
returnValue = dbCommand.CreateParameter();
dbCommand.Parameters["@IDPRODUTOSMANUT"].Direction = ParameterDirection.InputOutput;
//Executando consulta
dbCommand.ExecuteNonQuery();
result = int.Parse(dbCommand.Parameters["@IDPRODUTOSMANUT"].Value.ToString());
if (dbTransaction == null)
{
dbCommand.Transaction.Commit();
dbCnn.Close();
}
}
catch (Exception ex)
{
if (dbTransaction != null)
{
this.RollbackTransaction();
}
else
{
if (dbCommand.Transaction != null)
{
dbCommand.Transaction.Rollback();
}
if (dbCnn.State == ConnectionState.Open)
{
dbCnn.Close();
}
}
throw ex;
}
return(result);
}
public int Save(int?WEBSERVICEID, string CAMINHO, int IDUF, string OBSERVACAO)
{
int result = 0;
try
{
//Verificando a existência de um transação aberta
if (dbTransaction != null)
{
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Sav_WEBSERVICE", dbCnn);
dbCommand.Transaction = ((FbTransaction)(dbTransaction));
}
else
{
if (dbCnn == null)
{
dbCnn = ((FbConnection)GetConnectionDB());
}
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Sav_WEBSERVICE", dbCnn);
dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted);
}
dbCommand.CommandType = CommandType.StoredProcedure;
//PrimaryKey com valor igual a null, indica um novo registro,
//o valor da chave será fornecido pelo banco. Qualquer outro valor indicará edição do registro.
if (WEBSERVICEID == -1)
{
dbCommand.Parameters.AddWithValue("@WEBSERVICEID", DBNull.Value);
}
else
{
dbCommand.Parameters.AddWithValue("@WEBSERVICEID", WEBSERVICEID);
}
dbCommand.Parameters.AddWithValue("@CAMINHO", CAMINHO); //Coluna
if (IDUF != null)
{
dbCommand.Parameters.AddWithValue("@IDUF", IDUF); //ForeignKey
}
else
{
dbCommand.Parameters.AddWithValue("@IDUF", DBNull.Value); //ForeignKey 5
}
dbCommand.Parameters.AddWithValue("@OBSERVACAO", OBSERVACAO); //Coluna
//Retorno da Procedure
FbParameter returnValue;
returnValue = dbCommand.CreateParameter();
dbCommand.Parameters["@WEBSERVICEID"].Direction = ParameterDirection.InputOutput;
//Executando consulta
dbCommand.ExecuteNonQuery();
result = int.Parse(dbCommand.Parameters["@WEBSERVICEID"].Value.ToString());
if (dbTransaction == null)
{
dbCommand.Transaction.Commit();
dbCnn.Close();
}
}
catch (Exception ex)
{
if (dbTransaction != null)
{
this.RollbackTransaction();
}
else
{
if (dbCommand.Transaction != null)
{
dbCommand.Transaction.Rollback();
}
if (dbCnn.State == ConnectionState.Open)
{
dbCnn.Close();
}
}
throw ex;
}
return(result);
}
public int Save(int?IDFUNCIONARIO, string NOME, decimal COMISSAO, DateTime DATAADMISSAO, decimal SALARIO, int CODSTATUS, string FUNCAO, string DEPARTAMENTO, string SETOR, string CARTEIRATRABALHO, string CARTEIRAMOTORISTA, string CPF, string RG, string ENDERECO, string BAIRRO, string CIDADE, string CEP, string UF, string TELEFONE1, string TELEFONE2, string EMAIL, string OBSERVACAO, DateTime DTANIVERSARIO)
{
int result = 0;
try
{
//Verificando a existência de um transação aberta
if (dbTransaction != null)
{
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Sav_FUNCIONARIO", dbCnn);
dbCommand.Transaction = ((FbTransaction)(dbTransaction));
}
else
{
if (dbCnn == null)
{
dbCnn = ((FbConnection)GetConnectionDB());
}
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Sav_FUNCIONARIO", dbCnn);
dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted);
}
dbCommand.CommandType = CommandType.StoredProcedure;
if (IDFUNCIONARIO != -1)
{
dbCommand.Parameters.AddWithValue("@IDFUNCIONARIO", IDFUNCIONARIO); //PrimaryKey
}
else
{
dbCommand.Parameters.AddWithValue("@IDFUNCIONARIO", DBNull.Value); //PrimaryKey
}
dbCommand.Parameters.AddWithValue("@NOME", NOME); //Coluna
dbCommand.Parameters.AddWithValue("@COMISSAO", COMISSAO); //Coluna
dbCommand.Parameters.AddWithValue("@DATAADMISSAO", DATAADMISSAO); //Coluna
dbCommand.Parameters.AddWithValue("@SALARIO", SALARIO); //Coluna
dbCommand.Parameters.AddWithValue("@CODSTATUS", CODSTATUS); //Coluna
dbCommand.Parameters.AddWithValue("@FUNCAO", FUNCAO); //Coluna
dbCommand.Parameters.AddWithValue("@DEPARTAMENTO", DEPARTAMENTO); //Coluna
dbCommand.Parameters.AddWithValue("@SETOR", SETOR); //Coluna
dbCommand.Parameters.AddWithValue("@CARTEIRATRABALHO", CARTEIRATRABALHO); //Coluna
dbCommand.Parameters.AddWithValue("@CARTEIRAMOTORISTA", CARTEIRAMOTORISTA); //Coluna
dbCommand.Parameters.AddWithValue("@CPF", CPF); //Coluna
dbCommand.Parameters.AddWithValue("@RG", RG); //Coluna
dbCommand.Parameters.AddWithValue("@ENDERECO", ENDERECO); //Coluna
dbCommand.Parameters.AddWithValue("@BAIRRO", BAIRRO); //Coluna
dbCommand.Parameters.AddWithValue("@CIDADE", CIDADE); //Coluna
dbCommand.Parameters.AddWithValue("@CEP", CEP); //Coluna
dbCommand.Parameters.AddWithValue("@UF", UF); //Coluna
dbCommand.Parameters.AddWithValue("@TELEFONE1", TELEFONE1); //Coluna
dbCommand.Parameters.AddWithValue("@TELEFONE2", TELEFONE2); //Coluna
dbCommand.Parameters.AddWithValue("@EMAIL", EMAIL); //Coluna
dbCommand.Parameters.AddWithValue("@OBSERVACAO", OBSERVACAO); //Coluna
dbCommand.Parameters.AddWithValue("@DTANIVERSARIO", DTANIVERSARIO); //Coluna
//Retorno da Procedure
FbParameter returnValue;
returnValue = dbCommand.CreateParameter();
dbCommand.Parameters["@IDFUNCIONARIO"].Direction = ParameterDirection.InputOutput;
//Executando consulta
dbCommand.ExecuteNonQuery();
result = int.Parse(dbCommand.Parameters["@IDFUNCIONARIO"].Value.ToString());
if (dbTransaction == null)
{
dbCommand.Transaction.Commit();
dbCnn.Close();
}
}
catch (Exception ex)
{
if (dbTransaction != null)
{
this.RollbackTransaction();
}
else
{
if (dbCommand.Transaction != null)
{
dbCommand.Transaction.Rollback();
}
if (dbCnn.State == ConnectionState.Open)
{
dbCnn.Close();
}
}
throw ex;
}
return(result);
}
private void UcitajDokument()
{
ReSetupDokument();
DataTable dt_Magacin = new DataTable();
DataTable dt_Komercijalno = robaData.Clone();
using (FbConnection con = new FbConnection(M.Baza.connectionString))
{
con.Open();
//=============================================================
using (FbCommand cmd = new FbCommand("SELECT FLAG, PRETVOREN_U_VRDOK, PRETVOREN_U_BRDOK, PRETVOREN_U_VRDOK_KOMERCIJALNO, PRETVOREN_U_BRDOK_KOMERCIJALNO, ZA_MAGACINID, KOMENTAR, PPID FROM DOKUMENT WHERE VRDOK = @VrDok AND BRDOK = @BrDok", con))
{
cmd.Parameters.AddWithValue("@VrDok", vrDok);
cmd.Parameters.AddWithValue("@BrDok", brDok);
FbDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
flag = Convert.ToInt32(dr[0]);
pretvorenUVrDok = (dr[1] is DBNull) ? -1 : Convert.ToInt32(dr[1]);
pretvorenUBrDok = (dr[2] is DBNull) ? -1 : Convert.ToInt32(dr[2]);
pretvorenUVrDokKomercijalno = (dr[3] is DBNull) ? -1 : Convert.ToInt32(dr[3]);
pretvorenUBrDokKomercijalno = (dr[4] is DBNull) ? -1 : Convert.ToInt32(dr[4]);
magacinId = Convert.ToInt32(dr[5]);
komentar = dr[6].ToString();
ppid = (dr[7] is DBNull) ? -1 : Convert.ToInt32(dr[7]);
}
}
//=============================================================
using (FbDataAdapter da = new FbDataAdapter("SELECT STAVKAID, ROBAID, KOLICINA FROM STAVKA WHERE VRDOK = @VrDok AND BRDOK = @BrDok", con))
{
da.SelectCommand.Parameters.AddWithValue("@VrDok", vrDok);
da.SelectCommand.Parameters.AddWithValue("@BrDok", brDok);
da.Fill(dt_Magacin);
}
foreach (DataRow dr in dt_Magacin.Rows)
{
DataRow dataRow = robaData.Select(String.Format("ROBAID = {0}", dr.Field <int>("ROBAID"))).FirstOrDefault();
dt_Komercijalno.Rows.Add(dataRow.ItemArray);
}
dt_Magacin.PrimaryKey = new DataColumn[] { dt_Magacin.Columns["ROBAID"] };
dt_Komercijalno.PrimaryKey = new DataColumn[] { dt_Komercijalno.Columns["ROBAID"] };
dt_Magacin.Merge(dt_Komercijalno);
dataGridView1.DataSource = dt_Magacin;
NamestiDataGridView();
//=============================================================
con.Close();
}
SetupDokument();
}
private void dataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
{
//if (tabela.Equals("ZAMOWIENIA")) isEditing=true;
if (tabela.Equals("UZYTKOWNICY") && dataGridView1.Rows[dataGridView1.CurrentRow.Index].Cells["ID"].Value.ToString() != "")
{
strQuery = "UPDATE " + tabela + " SET UZYTKOWNIK='" + dataGridView1.Rows[dataGridView1.CurrentRow.Index].Cells["UZYTKOWNIK"].Value.ToString() + "' WHERE ID=" + dataGridView1.Rows[dataGridView1.CurrentRow.Index].Cells["ID"].Value + ";";
try
{
FbCommand kom = new FbCommand(strQuery, polaczenie.getConnection());
kom.ExecuteScalar();
}
catch (Exception ex)
{
MessageBox.Show("Błąd update: " + ex.Message);
//throw;
}
}
else if (tabela.Equals("UZYTKOWNICY"))
{
strQuery = "INSERT INTO " + tabela + " (UZYTKOWNIK) VALUES ('" + dataGridView1.Rows[dataGridView1.CurrentRow.Index].Cells["UZYTKOWNIK"].Value.ToString() + "');";
try
{
FbCommand kom = new FbCommand(strQuery, polaczenie.getConnection());
kom.ExecuteScalar();
}
catch (Exception ex)
{
MessageBox.Show("Błąd insert: " + ex.Message);
//throw;
}
użytkownicyToolStripMenuItem.PerformClick();
}
else if (tabela.Equals("ZAMOWIENIA") && dataGridView1.Rows[dataGridView1.CurrentRow.Index].Cells["ID"].Value.ToString() != "")
{
strQuery = "UPDATE " + tabela;
strQuery += " SET ";
strQuery += "DOSTAWCA='" + dataGridView1.Rows[e.RowIndex].Cells["DOSTAWCA"].Value.ToString() + "', ";
strQuery += "PRODUKT='" + dataGridView1.Rows[e.RowIndex].Cells["PRODUKT"].Value.ToString() + "', ";
strQuery += "CENA='" + dataGridView1.Rows[e.RowIndex].Cells["CENA"].Value.ToString() + "', ";
strQuery += "ZALICZKA='" + dataGridView1.Rows[e.RowIndex].Cells["ZALICZKA"].Value.ToString() + "', ";
strQuery += "TELEFON='" + dataGridView1.Rows[e.RowIndex].Cells["TELEFON"].Value.ToString() + "', ";
strQuery += "STATUS='" + dataGridView1.Rows[e.RowIndex].Cells["STATUS"].Value.ToString() + "', ";
strQuery += "PRACOWNIK='" + dataGridView1.Rows[e.RowIndex].Cells["PRACOWNIK"].Value.ToString() + "', ";
strQuery += "TOWAR_NA_MIEJSCU='" + dataGridView1.Rows[e.RowIndex].Cells["TOWAR_NA_MIEJSCU"].Value.ToString() + "'";
strQuery += " WHERE ID=" + dataGridView1.Rows[e.RowIndex].Cells["ID"].Value.ToString() + ";";
try
{
FbCommand kom = new FbCommand(strQuery, polaczenie.getConnection());
kom.ExecuteScalar();
}
catch (Exception ex)
{
MessageBox.Show("Błąd update zamówienie: " + ex.Message);
//throw;
}
}
else if (tabela.Equals("STATUSY") && dataGridView1.CurrentRow != null && dataGridView1.Rows[dataGridView1.CurrentRow.Index].Cells["ID"].Value.ToString() != "")
{
strQuery = "UPDATE " + tabela;
strQuery += " SET ";
strQuery += "STATUS='" + dataGridView1.Rows[e.RowIndex].Cells["STATUS"].Value.ToString() + "' ";
strQuery += ", KOLOR='" + dataGridView1.Rows[e.RowIndex].Cells["KOLOR"].Value.ToString() + "' ";
strQuery += " WHERE ID=" + dataGridView1.Rows[e.RowIndex].Cells["ID"].Value.ToString() + ";";
try
{
FbCommand kom = new FbCommand(strQuery, polaczenie.getConnection());
kom.ExecuteScalar();
}
catch (Exception ex)
{
MessageBox.Show("Błąd update zamówienie: " + ex.Message);
//throw;
}
}
}
private void dataGridView1_RowLeave(object sender, DataGridViewCellEventArgs e)
{
//MessageBox.Show("Kolumna " + e.ColumnIndex + " Wiersz " + e.RowIndex);
if (tabela.Equals("ZAMOWIENIA") && dataGridView1.Rows[dataGridView1.CurrentRow.Index].Cells["ID"].Value != null &&
dataGridView1.Rows[dataGridView1.CurrentRow.Index].Cells["ID"].Value.ToString() == ""
&&
(dataGridView1.Rows[e.RowIndex].Cells["DOSTAWCA"].Value.ToString().Length > 0 ||
dataGridView1.Rows[e.RowIndex].Cells["PRODUKT"].Value.ToString().Length > 0 ||
dataGridView1.Rows[e.RowIndex].Cells["CENA"].Value.ToString().Length > 0 ||
dataGridView1.Rows[e.RowIndex].Cells["ZALICZKA"].Value.ToString().Length > 0 ||
dataGridView1.Rows[e.RowIndex].Cells["TELEFON"].Value.ToString().Length > 0 ||
dataGridView1.Rows[e.RowIndex].Cells["TOWAR_NA_MIEJSCU"].Value.ToString().Length > 0 ||
dataGridView1.Rows[e.RowIndex].Cells["PRACOWNIK"].Value.ToString().Length > 0
)
)
{
strQuery = "INSERT INTO " + tabela;
strQuery += " (DOSTAWCA,PRODUKT,CENA,ZALICZKA,TELEFON,TOWAR_NA_MIEJSCU,STATUS,PRACOWNIK) ";
strQuery += " VALUES (";
strQuery += "'" + dataGridView1.Rows[e.RowIndex].Cells["DOSTAWCA"].Value.ToString() + "', ";
strQuery += "'" + dataGridView1.Rows[e.RowIndex].Cells["PRODUKT"].Value.ToString() + "', ";
strQuery += "'" + dataGridView1.Rows[e.RowIndex].Cells["CENA"].Value.ToString() + "', ";
strQuery += "'" + dataGridView1.Rows[e.RowIndex].Cells["ZALICZKA"].Value.ToString() + "', ";
strQuery += "'" + dataGridView1.Rows[e.RowIndex].Cells["TELEFON"].Value.ToString() + "', ";
strQuery += "'" + dataGridView1.Rows[e.RowIndex].Cells["TOWAR_NA_MIEJSCU"].Value.ToString() + "', ";
strQuery += "'" + dataGridView1.Rows[e.RowIndex].Cells["STATUS"].Value.ToString() + "', ";
strQuery += "'" + dataGridView1.Rows[e.RowIndex].Cells["PRACOWNIK"].Value.ToString() + "'";
strQuery += ") returning id;";
try
{
FbCommand kom = new FbCommand(strQuery, polaczenie.getConnection());
dataGridView1.Rows[e.RowIndex].Cells["ID"].Value = (Int32)kom.ExecuteScalar();
}
catch (Exception ex)
{
MessageBox.Show("Błąd insert zamówienie: " + ex.Message);
//throw;
}
}
else if (tabela.Equals("STATUSY") && dataGridView1.Rows[dataGridView1.CurrentRow.Index].Cells["ID"].Value != null &&
dataGridView1.Rows[dataGridView1.CurrentRow.Index].Cells["ID"].Value.ToString() == "")
{
string kol = "";
if (e.ColumnIndex == 3 && dataGridView1.Rows[e.RowIndex].Cells[3].Value != null)
{
kol = dataGridView1.Rows[e.RowIndex].Cells[3].Value.ToString();
}
else
{
kol = dataGridView1.Rows[e.RowIndex].Cells["KOLOR"].Value.ToString();
}
//MessageBox.Show("Kolor " + kol);
strQuery = "INSERT INTO " + tabela;
strQuery += " (STATUS,KOLOR) ";
strQuery += " VALUES (";
strQuery += "'" + dataGridView1.Rows[e.RowIndex].Cells["STATUS"].Value.ToString() + "', ";
strQuery += "'" + kol + "' ";
strQuery += ") returning id;";
try
{
FbCommand kom = new FbCommand(strQuery, polaczenie.getConnection());
dataGridView1.Rows[e.RowIndex].Cells["ID"].Value = (Int32)kom.ExecuteScalar();
}
catch (Exception ex)
{
MessageBox.Show("Błąd insert status: " + ex.Message);
//throw;
}
}
}
public void atualizar_azure()
{
Global.Login = "******";
string texto2 = " Começando os Trabalhos!! ";
if (this.label1.InvokeRequired)
{
SetTextCallback d = new SetTextCallback(DefinirTexto);
this.Invoke(d, new object[] { texto2 });
}
else
{
this.label1.Text = texto2;
}
//string caminho;
string firebird_ds = "";
string firebird_db = "";
string firebird_user = "";
string firebird_pass = "";
string arqLog = CaminhoDadosXML(caminho) + @"Dados\LOG.sql";
// string arqLog = @"C:\Users\Ivan\Documents\FRIGOBOM DADOS.FDB\LOG.sql";
string nomeArquivo = CaminhoDadosXML(caminho) + @"Dados\" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".sql";
//string nomeArquivo = @"C:\Users\Ivan\Documents\FRIGOBOM DADOS.FDB\" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".sql";
String oLog = ">>> run " + DateTime.Now.ToString("dd'/'MM'/'yyyy HH:mm:ss") + "\r\n" + "Summary \r\n";
caminho = AppDomain.CurrentDomain.BaseDirectory;
// adicionar um try caso o arquivo não exista
var prods = from p in XElement.Load((CaminhoDadosXML(caminho) + @"Dados\Conexoes.xml")).Elements("Conexao")
where p.Element("tipo").Value == "fb"
select new
{
servidor = p.Element("servidor").Value,
usuario = p.Element("usuario").Value,
senha = p.Element("senha").Value,
banco = p.Element("banco").Value,
};
// Executa a consulta
foreach (var produto in prods)
{
firebird_ds = produto.servidor;
firebird_db = produto.banco;
firebird_user = produto.usuario;
firebird_pass = produto.senha;
}
texto2 = " Testado Conexão!! ";
if (this.label1.InvokeRequired)
{
SetTextCallback d = new SetTextCallback(DefinirTexto);
this.Invoke(d, new object[] { texto2 });
}
else
{
this.label1.Text = texto2;
}
if (testar_conexao(firebird_ds, firebird_db, firebird_user, firebird_pass) == false)
{
texto2 = "Conexão ao Firebird Falhou ";
if (this.label1.InvokeRequired)
{
SetTextCallback d = new SetTextCallback(DefinirTexto);
this.Invoke(d, new object[] { texto2 });
}
else
{
this.label1.Text = texto2;
}
Global.gravaLog("Erro de conexão com o FireBird");
Global.Login = "******";
return;
}
texto2 = " Conexão ao Fire OK!";
if (this.label1.InvokeRequired)
{
SetTextCallback d = new SetTextCallback(DefinirTexto);
this.Invoke(d, new object[] { texto2 });
}
else
{
this.label1.Text = texto2;
}
Stopwatch watch = new Stopwatch();
watch.Start();
string strConn = "@DataSource=" + firebird_ds + "; Database=" + firebird_db + "; username= "******"; password = "******";Pooling=false";
FbConnection conn = new FbConnection(strConn);
///pegar ultimo registro no cloud
///
if (TestaAzure(caminho) == "-2146232060")
{
texto2 = " Conexão com o cloud falhou!";
if (this.label1.InvokeRequired)
{
SetTextCallback d = new SetTextCallback(DefinirTexto);
this.Invoke(d, new object[] { texto2 });
}
else
{
this.label1.Text = texto2;
}
Global.gravaLog("Erro de conexão com o cloud");
Global.Login = "******";
return;
}
string LastReg = RetornaUltimo();
if (LastReg == "-1")
{
Global.gravaLog("Erro ao conectar ao cloud e captura última atualização");
Global.Login = "******";
return;
}
Int64 LimMax = Convert.ToInt64(LastReg);
if (string.IsNullOrEmpty(LimMax.ToString()))
{
Global.gravaLog("Não foi possivel achar o último Registro no cloud - linha 353");
Global.Login = "******";
return;
}
LimMax += 10000;
String fb_mysql;
fb_mysql = @" select first 10000
mov_nf.id,
mov_nf.situacao,
mov_nf.id_pessoa,
mov_nf.emitente,
mov_nf.numero,
mov_nf.emissao,
mov_nf.tipo_pagamento,
mov_nf.id_loja,
mov_nf.modelo,
mov_nf.digitacao,
tb_nf_situacao.nome nome_nf_situacao,
tb_pessoas.nome nome_pessoa,
tb_nf_modelos.nome nome_nf,
mov_nf_itens.id_seq,
tb_produtos.nome nome_produto,
mov_nf_itens.id_produto,
mov_nf_itens.id_unidademedida,
mov_nf_itens.quantidade,
mov_nf_itens.precounitario,
mov_nf_itens.valortotal,
tb_produtos_unmedida.nome nome_unidade_medida,
tb_produtos.id_grupos,
tb_produtos_grupos.nome nome_grupo,
tb_produtos.id_subgrupos,
tb_produtos_subgrupos.nome nome_subgrupo
from tb_nf_modelos
right outer
join mov_nf on (tb_nf_modelos.id = mov_nf.modelo)
inner
join mov_nf_itens on (mov_nf.id = mov_nf_itens.id)
left outer join tb_produtos_unmedida on(mov_nf_itens.id_unidademedida = tb_produtos_unmedida.id)
left outer join tb_produtos on(mov_nf_itens.id_produto = tb_produtos.id)
left outer join tb_produtos_subgrupos on(tb_produtos.id_subgrupos = tb_produtos_subgrupos.id)
left outer join tb_produtos_grupos on(tb_produtos.id_grupos = tb_produtos_grupos.id)
left outer join tb_pessoas on(mov_nf.id_pessoa = tb_pessoas.id)
inner join tb_nf_situacao on(mov_nf.situacao = tb_nf_situacao.id)
where
(
(mov_nf.emitente = 'P') and (mov_nf.id> " + LastReg + " and mov_nf.id< " + LimMax + @" )
and
(tb_nf_situacao.nome = 'Documento regular')
) order by mov_nf.id;";
watch.Stop();
//Log log = new Log();
// log.WriteEntry(watch.Elapsed + " | Tempo de processamento na Consulta Firebird " + "\r\n");
oLog += watch.Elapsed + " | Tempo de processamento na montagem SQL " + "\r\n";
///medir o tempo de execução
watch.Restart();
texto2 = " Executando consulta no Fire ";
if (this.label1.InvokeRequired)
{
SetTextCallback d = new SetTextCallback(DefinirTexto);
this.Invoke(d, new object[] { texto2 });
}
else
{
this.label1.Text = texto2;
}
int num_newRegistro = 0;
try
{
FbCommand cmd = new FbCommand(fb_mysql, conn);
// FbDataAdapter DA = new FbDataAdapter(cmd);
FbDataAdapter DA2 = new FbDataAdapter(cmd);
DataSet DS = new DataSet();
DataTable DT = new DataTable();
conn.Open();
// DA.Fill(DS, "teste");
DA2.Fill(DT);
conn.Close();
num_newRegistro = Convert.ToInt16(DT.Rows.Count.ToString());
if (num_newRegistro < 1)
{
texto2 = " Não há dados novos! ";
if (this.label1.InvokeRequired)
{
SetTextCallback d = new SetTextCallback(DefinirTexto);
this.Invoke(d, new object[] { texto2 });
}
else
{
this.label1.Text = texto2;
}
watch.Stop();
//Log log = new Log();
// log.WriteEntry(watch.Elapsed + " | Tempo de processamento na Consulta Firebird " + "\r\n");
oLog += watch.Elapsed + " | Não Há dados novos - aguardar próximo. " + "\r\n";
StreamWriter writer3 = new StreamWriter(arqLog, true);
//Fecha o Log
writer3.WriteLineAsync(oLog);
oLog = null;
writer3.Flush();
writer3.Close();
Global.gravaLogAzure();
Global.Login = "******";
return;
}
watch.Stop();
//Log log = new Log();
// log.WriteEntry(watch.Elapsed + " | Tempo de processamento na Consulta Firebird " + "\r\n");
oLog += watch.Elapsed + " | Tempo de processamento na Consulta Firebird " + "\r\n";
// dataGridView2.DataSource = DS;
// dataGridView2.DataMember = "teste";
//textBox2.Text += "fim conexao";
// textBox2.Text += "GErar SQL";
//comecar o loop
string str = string.Empty;
string Oinsert = string.Empty;
string Ovalues = string.Empty;
string Osql = string.Empty;
string str2 = string.Empty;
watch.Restart();
texto2 = " Montando Arquivo ";
if (this.label1.InvokeRequired)
{
SetTextCallback d = new SetTextCallback(DefinirTexto);
this.Invoke(d, new object[] { texto2 });
}
else
{
this.label1.Text = texto2;
}
Oinsert = @"INSERT INTO [dbo].[vendas_frigobom_full]
([ID]
,[SITUACAO]
,[ID_PESSOA]
,[EMITENTE]
,[NUMERO]
,[EMISSAO]
,[TIPO_PAGAMENTO]
,[ID_LOJA]
,[MODELO]
,[DIGITACAO]
,[NOME_NF_SITUACAO]
,[NOME_PESSOA]
,[NOME_NF]
,[ID_SEQ]
,[NOME_PRODUTO]
,[ID_PRODUTO]
,[ID_UNIDADEMEDIDA]
,[QUANTIDADE]
,[PRECOUNITARIO]
,[VALORTOTAL]
,[NOME_UNIDADE_MEDIDA]
,[ID_GRUPOS]
,[NOME_GRUPO]
,[ID_SUBGRUPOS]
,[NOME_SUBGRUPO])
VALUES ";
int i = 0;
int c = 0;
string b = DT.Rows.Count.ToString();
foreach (DataRow dRow in DT.Rows)
{
i++;
c++;
if (i == 1)
{
Osql += "\r\n" + Oinsert + "(" + TrataNulo(dRow["id"].ToString()) + ",'" + dRow["situacao"].ToString() + "'," + TrataNulo(dRow["id_pessoa"].ToString()) + ",'" + dRow["emitente"].ToString() + "'," + TrataNulo(dRow["numero"].ToString()) + ",'" + ConvertData(dRow["emissao"].ToString()) + "','" + dRow["tipo_pagamento"].ToString() + "',"
+ TrataNulo(dRow["id_loja"].ToString()) + ",'" + dRow["modelo"].ToString() + "','" + ConvertData(dRow["digitacao"].ToString()) + "','" + dRow["nome_nf_situacao"].ToString().Replace("'", "'+char(39)+'") + "','" + dRow["nome_pessoa"].ToString().Replace("'", "'+char(39)+'") + "','" + dRow["nome_nf"].ToString().Replace("'", "'+char(39)+'") + "'," + TrataNulo(dRow["id_seq"].ToString()) + ",'"
+ dRow["nome_produto"].ToString().Replace("'", "'+char(39)+'") + "'," + TrataNulo(dRow["id_produto"].ToString()) + "," + TrataNulo(dRow["id_unidademedida"].ToString()) + "," + dRow["quantidade"].ToString().Replace(",", ".") + "," + dRow["precounitario"].ToString().Replace(",", ".") + "," + dRow["valortotal"].ToString().Replace(",", ".") + ",'" + dRow["nome_unidade_medida"].ToString().Replace("'", "'+char(39)+'") + "',"
+ TrataNulo(dRow["id_grupos"].ToString()) + ",'" + dRow["nome_grupo"].ToString().Replace("'", "'+char(39)+'") + "'," + TrataNulo(dRow["id_subgrupos"].ToString()) + ",'" + dRow["nome_subgrupo"].ToString().Replace("'", "'+char(39)+'") + "')\r\n";
}
else if (i == 999)
{
i = 0;
Osql += ",(" + TrataNulo(dRow["id"].ToString()) + ",'" + dRow["situacao"].ToString() + "'," + TrataNulo(dRow["id_pessoa"].ToString()) + ",'" + dRow["emitente"].ToString() + "'," + TrataNulo(dRow["numero"].ToString()) + ",'" + ConvertData(dRow["emissao"].ToString()) + "','" + dRow["tipo_pagamento"].ToString() + "',"
+ TrataNulo(dRow["id_loja"].ToString()) + ",'" + dRow["modelo"].ToString() + "','" + ConvertData(dRow["digitacao"].ToString()) + "','" + dRow["nome_nf_situacao"].ToString().Replace("'", "'+char(39)+'") + "','" + dRow["nome_pessoa"].ToString().Replace("'", "'+char(39)+'") + "','" + dRow["nome_nf"].ToString().Replace("'", "'+char(39)+'") + "'," + TrataNulo(dRow["id_seq"].ToString()) + ",'"
+ dRow["nome_produto"].ToString().Replace("'", "'+char(39)+'") + "'," + TrataNulo(dRow["id_produto"].ToString()) + "," + TrataNulo(dRow["id_unidademedida"].ToString()) + "," + dRow["quantidade"].ToString().Replace(",", ".") + "," + dRow["precounitario"].ToString().Replace(",", ".") + "," + dRow["valortotal"].ToString().Replace(",", ".") + ",'" + dRow["nome_unidade_medida"].ToString().Replace("'", "'+char(39)+'") + "',"
+ TrataNulo(dRow["id_grupos"].ToString()) + ",'" + dRow["nome_grupo"].ToString().Replace("'", "'+char(39)+'") + "'," + TrataNulo(dRow["id_subgrupos"].ToString()) + ",'" + dRow["nome_subgrupo"].ToString().Replace("'", "'+char(39)+'") + "')\r\n";
}
else
{
Osql += ",(" + TrataNulo(dRow["id"].ToString()) + ",'" + dRow["situacao"].ToString() + "'," + TrataNulo(dRow["id_pessoa"].ToString()) + ",'" + dRow["emitente"].ToString() + "'," + TrataNulo(dRow["numero"].ToString()) + ",'" + ConvertData(dRow["emissao"].ToString()) + "','" + dRow["tipo_pagamento"].ToString() + "',"
+ TrataNulo(dRow["id_loja"].ToString()) + ",'" + dRow["modelo"].ToString() + "','" + ConvertData(dRow["digitacao"].ToString()) + "','" + dRow["nome_nf_situacao"].ToString().Replace("'", "'+char(39)+'") + "','" + dRow["nome_pessoa"].ToString().Replace("'", "'+char(39)+'") + "','" + dRow["nome_nf"].ToString().Replace("'", "'+char(39)+'") + "'," + TrataNulo(dRow["id_seq"].ToString()) + ",'"
+ dRow["nome_produto"].ToString().Replace("'", "'+char(39)+'") + "'," + TrataNulo(dRow["id_produto"].ToString()) + "," + TrataNulo(dRow["id_unidademedida"].ToString()) + "," + dRow["quantidade"].ToString().Replace(",", ".") + "," + dRow["precounitario"].ToString().Replace(",", ".") + "," + dRow["valortotal"].ToString().Replace(",", ".") + ",'" + dRow["nome_unidade_medida"].ToString().Replace("'", "'+char(39)+'") + "',"
+ TrataNulo(dRow["id_grupos"].ToString()) + ",'" + dRow["nome_grupo"].ToString().Replace("'", "'+char(39)+'") + "'," + TrataNulo(dRow["id_subgrupos"].ToString()) + ",'" + dRow["nome_subgrupo"].ToString().Replace("'", "'+char(39)+'") + "')\r\n";
}
}
//limpa a dataset
DT.Clear();
watch.Stop();
//log.WriteEntry(watch.Elapsed + " | Montagemd o Sql para exportação " + "\r\n");
oLog += watch.Elapsed + " | Montagem do Sql para exportação " + "\r\n";
watch.Restart();
texto2 = " Escrevendo Arquivo! ";
if (this.label1.InvokeRequired)
{
SetTextCallback d = new SetTextCallback(DefinirTexto);
this.Invoke(d, new object[] { texto2 });
}
else
{
this.label1.Text = texto2;
}
// Cria um novo arquivo e devolve um StreamWriter para ele
StreamWriter writer = new StreamWriter(nomeArquivo);
/*
* if (!File.Exists(arqLog))
* {
* StreamWriter writer1 = new StreamWriter(arqLog);
* }
* else
* {
*
* }*/
// Agora é só sair escrevendo
writer.WriteLine(Osql);
// Não esqueça de fechar o arquivo ao terminar
writer.Close();
Osql = null;
conn.Close();
watch.Stop();
//log.WriteEntry(watch.Elapsed + " | Montagemd o Sql para exportação " + "\r\n");
oLog += watch.Elapsed + " | Escrevendo consulta SQL " + "\r\n";
}
catch (FbException e)
{
Global.gravaLog(e.Message.ToString() + "linha 618");
Global.Login = "******";
Global.gravaLogAzure();
//MessageBox.Show(e.StackTrace);
//MessageBox.Show(e.TargetSite.ReflectedType.Name + " + " + e.TargetSite.Name);
return;
}
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
watch.Restart();
texto2 = " Conexão ao cloud ";
if (this.label1.InvokeRequired)
{
SetTextCallback d = new SetTextCallback(DefinirTexto);
this.Invoke(d, new object[] { texto2 });
}
else
{
this.label1.Text = texto2;
}
// adicionar um try caso o arquivo não exista
var prodx = from p in XElement.Load((CaminhoDadosXML(caminho) + @"Dados\Conexoes.xml")).Elements("Conexao")
where p.Element("tipo").Value == "azure"
select new
{
servidor = p.Element("servidor").Value,
usuario = p.Element("usuario").Value,
senha = p.Element("senha").Value,
banco = p.Element("banco").Value,
};
// Executa a consulta
foreach (var produto in prodx)
{
string cipherText = produto.senha.Trim();
string decryptedText = CryptorEngine.Decrypt(cipherText, true);
builder.DataSource = produto.servidor;
builder.UserID = produto.usuario;
builder.Password = decryptedText;
//builder.Password = produto.senha;
builder.InitialCatalog = produto.banco;
builder.ConnectTimeout = 600;
}
//limpa memória
prodx = null;
texto2 = " exportando ao cloud! ";
if (this.label1.InvokeRequired)
{
SetTextCallback d = new SetTextCallback(DefinirTexto);
this.Invoke(d, new object[] { texto2 });
}
else
{
this.label1.Text = texto2;
}
using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
{
try
{
connection.Open();
StreamReader texto = new StreamReader(nomeArquivo);
// textBox3.Text = texto.ReadToEnd();
String sql = texto.ReadToEnd();
///fecho e deleto o arquivo.
texto.Close();
/*
* if (File.Exists(nomeArquivo))
* {
* File.Delete(nomeArquivo);
* }*/
//exporto dados.
SqlCommand command = new SqlCommand(sql, connection);
command.CommandTimeout = 600;
command.ExecuteNonQuery();
connection.Close();
//log.WriteEntry(watch.Elapsed + " | Exportação para o cloud concluida " + "\r\n");
sql = null;
}
catch (Exception ex)
{
//log.WriteEntry(ex);
oLog += " | ocorreu um erro " + ex.Message + "\r\n";
Global.gravaLogAzure();
Global.Login = "******";
return;
}
}
watch.Stop();
oLog += watch.Elapsed + " | Exportação para o cloud " + "\r\n";
StreamWriter writer1 = new StreamWriter(arqLog, true);
//Fecha o Log
writer1.WriteLineAsync(oLog);
oLog = null;
writer1.Flush();
writer1.Close();
texto2 = "Aguardando o Próximo!";
if (this.label1.InvokeRequired)
{
SetTextCallback d = new SetTextCallback(DefinirTexto);
this.Invoke(d, new object[] { texto2 });
}
else
{
this.label1.Text = texto2;
}
Global.Login = "******";
if (Global.CtrLog == 10)
{
Global.gravaLogAzure();
Global.CtrLog = 0;
}
else
{
Global.CtrLog++;
}
}
public int Save(int?IDESTOQUELOTE, decimal?QUANTIDADE, int?IDLOTE, int?IDPRODUTO, string NUMERODOC,
DateTime?DATA, string FLAGTIPO, string FLAGATIVO, string OBSERVACAO)
{
int result = 0;
try
{
//Verificando a existência de um transação aberta
if (dbTransaction != null)
{
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Sav_ESTOQUELOTE", dbCnn);
dbCommand.Transaction = ((FbTransaction)(dbTransaction));
}
else
{
if (dbCnn == null)
{
dbCnn = ((FbConnection)GetConnectionDB());
}
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Sav_ESTOQUELOTE", dbCnn);
dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted);
}
dbCommand.CommandType = CommandType.StoredProcedure;
//PrimaryKey com valor igual a null, indica um novo registro,
//o valor da chave será fornecido pelo banco. Qualquer outro valor indicará edição do registro.
if (IDESTOQUELOTE == -1)
{
dbCommand.Parameters.AddWithValue("@IDESTOQUELOTE", DBNull.Value);
}
else
{
dbCommand.Parameters.AddWithValue("@IDESTOQUELOTE", IDESTOQUELOTE);
}
dbCommand.Parameters.AddWithValue("@QUANTIDADE", QUANTIDADE); //Coluna
dbCommand.Parameters.AddWithValue("@IDLOTE", IDLOTE); //Coluna
dbCommand.Parameters.AddWithValue("@IDPRODUTO", IDPRODUTO); //Coluna
dbCommand.Parameters.AddWithValue("@NUMERODOC", NUMERODOC); //Coluna
dbCommand.Parameters.AddWithValue("@DATA", DATA); //Coluna
dbCommand.Parameters.AddWithValue("@FLAGTIPO", FLAGTIPO); //Coluna
dbCommand.Parameters.AddWithValue("@FLAGATIVO", FLAGATIVO); //Coluna
dbCommand.Parameters.AddWithValue("@OBSERVACAO", OBSERVACAO); //Coluna
//Retorno da Procedure
FbParameter returnValue;
returnValue = dbCommand.CreateParameter();
dbCommand.Parameters["@IDESTOQUELOTE"].Direction = ParameterDirection.InputOutput;
//Executando consulta
dbCommand.ExecuteNonQuery();
result = int.Parse(dbCommand.Parameters["@IDESTOQUELOTE"].Value.ToString());
if (dbTransaction == null)
{
dbCommand.Transaction.Commit();
dbCnn.Close();
}
}
catch (Exception ex)
{
if (dbTransaction != null)
{
this.RollbackTransaction();
}
else
{
if (dbCommand.Transaction != null)
{
dbCommand.Transaction.Rollback();
}
if (dbCnn.State == ConnectionState.Open)
{
dbCnn.Close();
}
}
throw ex;
}
return(result);
}
static void EventCounts(object sender, FbRemoteEventEventArgs args)
{
if (args.Counts > 0)
{
string selectFromAccountsWhereModifyU = "";
switch (args.Name)
{
case "acc_insert":
selectFromAccountsWhereModifyU = "Select * from \"accounts\" where MODIFY='I'";
break;
case "acc_update":
selectFromAccountsWhereModifyU = "Select * from \"accounts\" where MODIFY='U'";
break;
}
try
{
FbTransaction ft = connection.BeginTransaction();
FbCommand fb = new FbCommand(selectFromAccountsWhereModifyU, connection, ft);
FbDataReader reader = fb.ExecuteReader();
while (reader.Read())
{
string row = "Record ->";
for (var i = 0; i < reader.FieldCount; i++)
{
row += string.Format(" {0} ", reader.GetValue(i));
}
switch (args.Name)
{
case "acc_insert":
row += " Inserted";
break;
case "acc_update":
row += " Updated";
;
break;
}
Console.WriteLine(row);
}
reader.Close();
switch (args.Name)
{
case "acc_insert":
selectFromAccountsWhereModifyU = "Update \"accounts\" set MODIFY='N' where MODIFY='I'";
break;
case "acc_update":
selectFromAccountsWhereModifyU = "Update \"accounts\" set MODIFY='G' where MODIFY='U'";
break;
}
fb.CommandText = selectFromAccountsWhereModifyU;
fb.ExecuteNonQuery();
ft.Commit();
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
}
private void buttonOK_Click(object sender, EventArgs e)
{
#region oop exkurs
/*
* Auto auto = new Auto("grün");
* Auto auto2 = new Auto("blau");
*
* List<Auto> alleAutos = new List<Auto>();
* alleAutos.Add(auto);
* alleAutos.Add(auto2);
*
* Auto a = alleAutos[1];
*
* MessageBox.Show(a.Farbe);
*
*
* //MessageBox.Show("Farbe Auto 1: " + auto.Farbe);
* //MessageBox.Show("Farbe Auto 2: " + auto2.Farbe);
*
*
* for (int i = 0; i < 50; i++)
* {
* if (i % 3 == 0)
* auto.Beschleunigen(5);
* else
* auto2.Beschleunigen(5);
* }
*
*
*
* //MessageBox.Show("Geschw. Auto 1: " + auto.AktuelleGeschwindigkeit);
* //MessageBox.Show("Geschw Auto 2: " + auto2.AktuelleGeschwindigkeit);
*
*
* return;
*/
#endregion
comboTables.Items.Clear();
string connectionString =
@"User =SYSDBA;" +
@"Password=masterkey;" +
@"Database=D:\5_daten\TimeCounter\Projekt-BLJ\src/TADATA3.FDB;" +
@"ServerType=1";
FbConnection con = new FbConnection(connectionString);
con.Open();
DataTable t = con.GetSchema("Tables");
DataTable dt = new DataTable();
FbCommand cmd = new FbCommand();
cmd.Connection = con;
FbDataAdapter adap = new FbDataAdapter();
adap.SelectCommand = cmd;
AddTableDataToGrid("schedules", cmd, adap);
AddTableDataToGrid("ATTENDANT", cmd, adap);
AddTableDataToGrid("DEPARTMENTS", cmd, adap);
AddTableDataToGrid("DEVICES", cmd, adap);
AddTableDataToGrid("EXTRATIME", cmd, adap);
AddTableDataToGrid("IDENTIFICATION", cmd, adap);
AddTableDataToGrid("PAYCLASS", cmd, adap);
AddTableDataToGrid("PAYRULE", cmd, adap);
AddTableDataToGrid("PLANNING", cmd, adap);
AddTableDataToGrid("USERS", cmd, adap);
AddTableDataToGrid("WORKCODES", cmd, adap);
//GetTableDataToDatabase("USERS", cmd, adap);
/*
* FbCommand cmd2 = new FbCommand("Update users set password = ''");
* cmd2.Connection = con;
* int affectedRecords = cmd2.ExecuteNonQuery();
*/
con.Close();
}
/// <summary>
/// Handles the Click event of the button2 control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>
private void button2_Click(object sender, EventArgs e)
{
// Add a record.
try
{
// The fileds must not be empty and the names must not match.
if (name1 != 0 && name2 != 0 && relationship != 0 && name1 != name2)
{
id = 0;
bool matchFound = false;
// Open a connection to the database.
FbConnection fbSqlConnection = new FbConnection(url);
fbSqlConnection.Open();
// Get the items of the Relations table.
FbCommand fbSqlCommand1 = new FbCommand("SELECT * FROM Relations", fbSqlConnection);
// Execute.
FbDataReader fbSqlDataReader1 = fbSqlCommand1.ExecuteReader();
// Get the number of the existing items.
while (fbSqlDataReader1.Read())
{
id++;
// Search for a deleted mathing item.
if (!matchFound &&
fbSqlDataReader1.GetInt32(1) == name1 &&
fbSqlDataReader1.GetInt32(2) == relationship &&
fbSqlDataReader1.GetInt32(3) == name2 &&
fbSqlDataReader1.GetBoolean(4) == true)
{
matchFound = true;
id = fbSqlDataReader1.GetInt32(0);
break;
}
}
// Close the dataReader.
fbSqlDataReader1.Close();
// Add an item if no deleted found else restore the item.
if (!matchFound)
{
// Add a new item into the Relations table.
FbCommand fbSqlCommand = new FbCommand("INSERT INTO Relations (Id, Person1, Relationship, Person2, IsDeleted) VALUES (@param1, @param2, @param3, @param4, @param5)", fbSqlConnection);
// Fill the values of the command.
fbSqlCommand.Parameters.AddWithValue("@param1", id + 1);
fbSqlCommand.Parameters.AddWithValue("@param2", name1);
fbSqlCommand.Parameters.AddWithValue("@param3", relationship);
fbSqlCommand.Parameters.AddWithValue("@param4", name2);
fbSqlCommand.Parameters.AddWithValue("@param5", false);
// Execute.
fbSqlCommand.ExecuteNonQuery();
}
else
{
// Edit the IsDeleted value to true.
FbCommand fbSqlCommand = new FbCommand("UPDATE Relations SET IsDeleted = @param2 WHERE Id = @param1", fbSqlConnection);
// Fill the values of the command.
fbSqlCommand.Parameters.AddWithValue("@param1", id);
fbSqlCommand.Parameters.AddWithValue("@param2", false);
// Execute.
fbSqlCommand.ExecuteNonQuery();
}
// Close the connection.
fbSqlConnection.Close();
// Refresh.
GetData();
}
}
catch
{ }
}
private static void CreateTriggers(string connectionString)
{
FbConnection connection = new FbConnection(connectionString);
connection.Open();
StringBuilder commandText = new StringBuilder();
// new_row
commandText = new StringBuilder();
commandText.Append("CREATE TRIGGER new_row FOR test ACTIVE\r\n");
commandText.Append("AFTER INSERT POSITION 0\r\n");
commandText.Append("AS\r\n");
commandText.Append("BEGIN\r\n");
commandText.Append("POST_EVENT 'new row';\r\n");
commandText.Append("END");
FbCommand command = new FbCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
// update_row
commandText = new StringBuilder();
commandText.Append("CREATE TRIGGER update_row FOR test ACTIVE\r\n");
commandText.Append("AFTER UPDATE POSITION 0\r\n");
commandText.Append("AS\r\n");
commandText.Append("BEGIN\r\n");
commandText.Append("POST_EVENT 'updated row';\r\n");
commandText.Append("END");
command = new FbCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
connection.Close();
}
public int Save(CLIENTEFASTEntity Entity)
{
int result = 0;
try
{
//Verificando a existência de um transação aberta
if (dbTransaction != null)
{
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("SAV_CLIENTEFAST", dbCnn);
dbCommand.Transaction = ((FbTransaction)(dbTransaction));
}
else
{
if (dbCnn == null)
{
dbCnn = ((FbConnection)GetConnectionDB());
}
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("SAV_CLIENTEFAST", dbCnn);
dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted);
}
dbCommand.CommandType = CommandType.StoredProcedure;
if (Entity.CODIGO_CLIENTE != -1)
{
dbCommand.Parameters.AddWithValue("@CODIGO_CLIENTE", Entity.CODIGO_CLIENTE); //PrimaryKey
}
else
{
dbCommand.Parameters.AddWithValue("@CODIGO_CLIENTE", DBNull.Value); //PrimaryKey
}
dbCommand.Parameters.AddWithValue("@NOME_CLIENTE", Entity.NOME_CLIENTE); //Coluna
dbCommand.Parameters.AddWithValue("@FANTASIA_CLIENTE", Entity.FANTASIA_CLIENTE); //Coluna
dbCommand.Parameters.AddWithValue("@CPF_CLIENTE", Entity.CPF_CLIENTE); //Coluna
dbCommand.Parameters.AddWithValue("@DOC_CLIENTE", Entity.DOC_CLIENTE); //Coluna
dbCommand.Parameters.AddWithValue("@ENDERECO_CLIENTE", Entity.ENDERECO_CLIENTE); //Coluna
dbCommand.Parameters.AddWithValue("@NUMEROEND_CLIENTE", Entity.NUMEROEND_CLIENTE); //Coluna
dbCommand.Parameters.AddWithValue("@COMPLEMENTOEND_CLIENTE", Entity.COMPLEMENTOEND_CLIENTE); //Coluna
dbCommand.Parameters.AddWithValue("@BAIRRO_CLIENTE", Entity.BAIRRO_CLIENTE); //Coluna
dbCommand.Parameters.AddWithValue("@CEP_CLIENTE", Entity.CEP_CLIENTE); //Coluna
dbCommand.Parameters.AddWithValue("@EMAIL_CLIENTE", Entity.EMAIL_CLIENTE); //Coluna
dbCommand.Parameters.AddWithValue("@TELEFONE1_CLIENTE", Entity.TELEFONE1_CLIENTE); //Coluna
dbCommand.Parameters.AddWithValue("@TELEFONE2_CLIENTE", Entity.TELEFONE2_CLIENTE); //Coluna
dbCommand.Parameters.AddWithValue("@CELULAR_CLIENTE", Entity.CELULAR_CLIENTE); //Coluna
dbCommand.Parameters.AddWithValue("@COD_CIDADE", Entity.COD_CIDADE); //Coluna
//Retorno da Procedure
FbParameter returnValue;
returnValue = dbCommand.CreateParameter();
dbCommand.Parameters["@CODIGO_CLIENTE"].Direction = ParameterDirection.InputOutput;
//Executando consulta
dbCommand.ExecuteNonQuery();
result = int.Parse(dbCommand.Parameters["@CODIGO_CLIENTE"].Value.ToString());
if (dbTransaction == null)
{
dbCommand.Transaction.Commit();
dbCnn.Close();
}
}
catch (Exception ex)
{
if (dbTransaction != null)
{
this.RollbackTransaction();
}
else
{
if (dbCommand.Transaction != null)
{
dbCommand.Transaction.Rollback();
}
if (dbCnn.State == ConnectionState.Open)
{
dbCnn.Close();
}
}
throw ex;
}
return(result);
}
/// <summary>
/// Gets the Data.
/// </summary>
private void GetData()
{
try
{
// Clear the existing items.
dataGridView1.Rows.Clear();
comboBox1.Items.Clear();
comboBox2.Items.Clear();
comboBox3.Items.Clear();
// Open a connection to the database.
FbConnection fbSqlConnection = new FbConnection(url);
fbSqlConnection.Open();
// Get the items from the People table.
List <Person> people = new List <Person>();
FbCommand fbSqlCommand1 = new FbCommand("SELECT * FROM People", fbSqlConnection);
// Execute.
FbDataReader fbSqlDataReader1 = fbSqlCommand1.ExecuteReader();
// Store the items in a Person list.
while (fbSqlDataReader1.Read())
{
people.Add(new Person(fbSqlDataReader1.GetInt32(0), fbSqlDataReader1.GetString(1), fbSqlDataReader1.GetString(2),
fbSqlDataReader1.GetString(3), fbSqlDataReader1.GetDateTime(4), fbSqlDataReader1.GetBoolean(5)));
}
// Close the dataReader.
fbSqlDataReader1.Close();
// Get the items from the Relationships table.
List <Relationship> relationships = new List <Relationship>();
FbCommand fbSqlCommand2 = new FbCommand("SELECT * FROM Relationships", fbSqlConnection);
// Execute.
FbDataReader fbSqlDataReader2 = fbSqlCommand2.ExecuteReader();
// Store the items in a Relationship list.
while (fbSqlDataReader2.Read())
{
relationships.Add(new Relationship(fbSqlDataReader2.GetInt32(0), fbSqlDataReader2.GetString(1), fbSqlDataReader2.GetBoolean(2)));
}
// Close the dataReader.
fbSqlDataReader2.Close();
// Get items from the Relations table.
List <Relation> relations = new List <Relation>();
FbCommand fbSqlCommand = new FbCommand("SELECT * FROM Relations WHERE IsDeleted = 0", fbSqlConnection);
// Execute.
FbDataReader fbSqlDataReader = fbSqlCommand.ExecuteReader();
// Store the items in a Relation list.
while (fbSqlDataReader.Read())
{
relations.Add(new Relation(fbSqlDataReader.GetInt32(0), fbSqlDataReader.GetInt32(1), fbSqlDataReader.GetInt32(2),
fbSqlDataReader.GetInt32(3), fbSqlDataReader.GetBoolean(4)));
}
// Close the dataReader and the connection.
fbSqlDataReader.Close();
fbSqlConnection.Close();
// Fill the dataGridView rows with the values of the Relations table.
foreach (var oneItem in relations)
{
DataGridViewRow row = new DataGridViewRow();
DataGridViewCell cell1 = new DataGridViewTextBoxCell();
DataGridViewCell cell2 = new DataGridViewTextBoxCell();
DataGridViewCell cell3 = new DataGridViewTextBoxCell();
DataGridViewCell cell4 = new DataGridViewTextBoxCell();
cell1.Value = oneItem.Id.ToString();
// Substitute the Person1 and Person2 ids with their names.
foreach (var onePerson in people)
{
if (oneItem.Person1 == onePerson.Id)
{
cell2.Value = onePerson.Name;
}
if (oneItem.Person2 == onePerson.Id)
{
cell4.Value = onePerson.Name;
}
}
// Substitute the Relationship ids with their names.
foreach (var oneRelationship in relationships)
{
if (oneItem.Relationship == oneRelationship.Id)
{
cell3.Value = oneRelationship.Name;
}
}
row.Cells.Add(cell1);
row.Cells.Add(cell2);
row.Cells.Add(cell3);
row.Cells.Add(cell4);
dataGridView1.Rows.Add(row);
}
// Fill the comboBox items with the values of the People table.
foreach (var oneItem in people)
{
if (!oneItem.IsDeleted)
{
comboBox1.Items.Add(oneItem.Id);
comboBox3.Items.Add(oneItem.Id);
}
}
// Fill the comboBox items with the values of the Relationships table.
foreach (var oneItem in relationships)
{
if (!oneItem.IsDeleted)
{
comboBox2.Items.Add(oneItem.Id);
}
}
}
catch
{ }
}
/// <summary>
/// Rebuilds all replication triggers based on rules within REPLICATE$TABLES
/// </summary>
/// <returns>Physical SQL File on disk containing all the updates</returns>
private string RebuildReplicationTriggers(string connectionString, bool generateOnly)
{
// get current assembly path
string Result = Path.GetTempFileName();
try
{
//connect to local DB
FbConnection db = new FbConnection(connectionString);
db.Open();
try
{
FbTransaction tran = db.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
StreamWriter updateFile = new StreamWriter(Result, false);
try
{
string tableNames = String.Empty;
string SQL = "SELECT r.TABLE_NAME, r.OPERATION, r.TRIGGER_NAME, r.EXCLUDE_FIELDS, r.LOCAL_ID_COLUMN \n" +
"FROM REPLICATE$TABLES r \nORDER BY r.TABLE_NAME, r.OPERATION ";
FbDataReader rdr = null;
FbCommand cmd = new FbCommand(SQL, db, tran);
try
{
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
if (!tableNames.Contains(rdr.GetString(0).Trim()))
{
if (tableNames.Length > 0)
{
tableNames += String.Format(",'{0}'\n", rdr.GetString(0).Trim());
}
else
{
tableNames += String.Format("'{0}'\n", rdr.GetString(0).Trim());
}
}
}
}
finally
{
CloseAndDispose(ref cmd, ref rdr);
}
// have any tables been removed from the list since the last time this was run?
SQL = String.Format("SELECT TRIM(a.RDB$TRIGGER_NAME) FROM RDB$TRIGGERS a " +
"WHERE ((TRIM(a.RDB$TRIGGER_NAME) LIKE 'REPLICATE$%_ID')) " +
"OR ((TRIM(a.RDB$TRIGGER_NAME) <> 'REPLICATE$PK_CHANGES') AND " +
"TRIM(a.RDB$TRIGGER_NAME) LIKE 'REPLICATE$%' " +
"AND a.RDB$RELATION_NAME NOT IN ( \n" +
tableNames + "\n)) OR a.RDB$TRIGGER_NAME LIKE 'REPLICATE$%_ID';");
cmd = new FbCommand(SQL, db, tran);
try
{
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
updateFile.WriteLine(String.Format("DROP TRIGGER {0};", rdr.GetString(0).Trim()));
}
}
finally
{
CloseAndDispose(ref cmd, ref rdr);
}
SQL = "SELECT DISTINCT r.TABLE_NAME \n" +
"FROM REPLICATE$TABLES r ";
cmd = new FbCommand(SQL, db, tran);
try
{
tableNames = String.Empty;
rdr = cmd.ExecuteReader();
string hashUpdateTables = String.Empty;
while (rdr.Read())
{
hashUpdateTables += ReplicateTableHasReplicateFields(db, tran,
updateFile, rdr.GetString(0).Trim());
}
}
finally
{
CloseAndDispose(ref cmd, ref rdr);
}
SQL = "SELECT r.TABLE_NAME, r.OPERATION, r.TRIGGER_NAME, r.EXCLUDE_FIELDS, r.LOCAL_ID_COLUMN, r.OPTIONS \n" +
"FROM REPLICATE$TABLES r \nORDER BY r.TABLE_NAME, r.OPERATION ";
cmd = new FbCommand(SQL, db, tran);
try
{
tableNames = String.Empty;
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
string triggerCode = String.Empty;
switch (rdr.GetString(1))
{
case "INSERT":
triggerCode = ReplicateCreateTriggerInsert(db, tran, generateOnly,
rdr.GetString(0).Trim(), rdr.GetString(2).Trim(),
rdr.GetString(3), (TableOptions)rdr.GetInt64(5));
break;
case "UPDATE":
triggerCode = ReplicateCreateTriggerUpdate(db, tran, generateOnly,
rdr.GetString(0).Trim(), rdr.GetString(2).Trim(),
rdr.GetString(3), rdr.GetString(4), (TableOptions)rdr.GetInt64(5));
break;
case "DELETE":
triggerCode = ReplicateCreateTriggerDelete(db, tran, generateOnly,
rdr.GetString(0).Trim(), rdr.GetString(2).Trim(),
rdr.GetString(3), (TableOptions)rdr.GetInt64(5));
break;
}
if (!String.IsNullOrEmpty(triggerCode))
{
updateFile.Write(triggerCode);
}
}
}
finally
{
CloseAndDispose(ref cmd, ref rdr);
}
}
finally
{
updateFile.Flush();
updateFile.Close();
updateFile = null;
}
}
finally
{
tran.Rollback();
tran.Dispose();
}
}
finally
{
db.Close();
db.Dispose();
db = null;
}
}
catch (Exception e)
{
Shared.EventLog.Add(e);
throw;
}
return(Result);
}
public int Save(int?IDDESTINOCHEQUE, int IDCHEQUE, int IDCLIENTE, int IDFORNECEDOR, string OBSERVACAO, string TIPORECEBIMENTO, string NOMEDESTINO, DateTime DATA)
{
int result = 0;
try
{
//Verificando a existência de um transação aberta
if (dbTransaction != null)
{
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Sav_DESTINOCHEQUE", dbCnn);
dbCommand.Transaction = ((FbTransaction)(dbTransaction));
}
else
{
if (dbCnn == null)
{
dbCnn = ((FbConnection)GetConnectionDB());
}
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Sav_DESTINOCHEQUE", dbCnn);
dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted);
}
dbCommand.CommandType = CommandType.StoredProcedure;
if (IDDESTINOCHEQUE != -1)
{
dbCommand.Parameters.AddWithValue("@IDDESTINOCHEQUE", IDDESTINOCHEQUE); //PrimaryKey
}
else
{
dbCommand.Parameters.AddWithValue("@IDDESTINOCHEQUE", DBNull.Value); //PrimaryKey
}
dbCommand.Parameters.AddWithValue("@IDCHEQUE", IDCHEQUE); //Coluna
dbCommand.Parameters.AddWithValue("@IDCLIENTE", IDCLIENTE); //Coluna
dbCommand.Parameters.AddWithValue("@IDFORNECEDOR", IDFORNECEDOR); //Coluna
dbCommand.Parameters.AddWithValue("@OBSERVACAO", OBSERVACAO); //Coluna
dbCommand.Parameters.AddWithValue("@TIPORECEBIMENTO", TIPORECEBIMENTO); //Coluna
dbCommand.Parameters.AddWithValue("@NOMEDESTINO", NOMEDESTINO); //Coluna
dbCommand.Parameters.AddWithValue("@DATA", DATA); //Coluna
//Retorno da Procedure
FbParameter returnValue;
returnValue = dbCommand.CreateParameter();
dbCommand.Parameters["@IDDESTINOCHEQUE"].Direction = ParameterDirection.InputOutput;
//Executando consulta
dbCommand.ExecuteNonQuery();
result = int.Parse(dbCommand.Parameters["@IDDESTINOCHEQUE"].Value.ToString());
if (dbTransaction == null)
{
dbCommand.Transaction.Commit();
dbCnn.Close();
}
}
catch (Exception ex)
{
if (dbTransaction != null)
{
this.RollbackTransaction();
}
else
{
if (dbCommand.Transaction != null)
{
dbCommand.Transaction.Rollback();
}
if (dbCnn.State == ConnectionState.Open)
{
dbCnn.Close();
}
}
throw ex;
}
return(result);
}
public string GenerateTriggerRemoveScript(string connectionString, bool generateOnly, DatabaseRemoteUpdate remoteUpdate)
{
// get temp file for triggers
string Result = Path.GetTempFileName();
try
{
_xmlHashUpdates.Clear();
//connect to local DB
FbConnection db = new FbConnection(connectionString);
db.Open();
try
{
FbTransaction tran = db.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
StreamWriter updateFile = new StreamWriter(Result, false);
try
{
// have any tables been removed from the list since the last time this was run?
string SQL = "SELECT TRIM(a.RDB$TRIGGER_NAME) " +
"FROM RDB$TRIGGERS a WHERE ((TRIM(a.RDB$TRIGGER_NAME) LIKE 'REPLICATE$%'));";
FbDataReader rdr = null;
FbCommand cmd = new FbCommand(SQL, db, tran);
try
{
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
updateFile.WriteLine(String.Format("DROP TRIGGER {0};", rdr.GetString(0).Trim()));
string hashDatabase = "D" + Shared.Utilities.HashStringMD5(GetDatabaseName(db));
string hashCode = "C";
string triggerHash = "T" + Shared.Utilities.HashStringMD5(
rdr.GetString(0).Trim().Replace("REPLICATE$", ""));
_xmlHashUpdates.Add(String.Format("{0}${1}${2}", hashDatabase, triggerHash, hashCode));
}
}
finally
{
CloseAndDispose(ref cmd, ref rdr);
}
SQL = "SELECT TRIM(a.RDB$RELATION_NAME) FROM RDB$RELATION_FIELDS a " +
"WHERE a.RDB$FIELD_NAME = 'REPLICATE$HASH'";
cmd = new FbCommand(SQL, db, tran);
try
{
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
updateFile.WriteLine(String.Format("ALTER TABLE {0} DROP REPLICATE$HASH;", rdr.GetString(0)));
}
}
finally
{
CloseAndDispose(ref cmd, ref rdr);
}
}
finally
{
updateFile.Flush();
updateFile.Close();
updateFile = null;
}
if (generateOnly)
{
return(Result);
}
bool tableUpdated = false;
if (remoteUpdate.UpdateDatabase(connectionString, Result, -1, ref tableUpdated))
{
File.Delete(Result);
foreach (string update in _xmlHashUpdates)
{
string[] parts = update.Split('$');
Shared.XML.SetXMLValue(parts[0], parts[1], parts[2]);
}
}
else
{
throw new Exception("Error creating replication triggers");
}
}
finally
{
tran.Rollback();
tran.Dispose();
}
}
finally
{
db.Close();
db.Dispose();
db = null;
}
}
catch (Exception e)
{
Shared.EventLog.Add(e);
throw;
}
return(Result);
}
private void razduziRobuToolStripMenuItem_Click(object sender, EventArgs e)
{
if (flag == 0)
{
MessageBox.Show("Dokument mora biti zakljucan!");
return;
}
if (pretvorenUBrDokKomercijalno > 0)
{
MessageBox.Show("Vec ste razduzili robu po ovom dokumentu!");
return;
}
if (flag != 1)
{
MessageBox.Show("Greska");
return;
}
try
{
DialogResult d = MessageBox.Show("Pretvaranjem ovog dokumenta on ce biti zakljucan, a vas magacin ce biti razduzen. Da li zelite nastaviti?", "Potvrdi", MessageBoxButtons.YesNo);
if (d == DialogResult.Yes)
{
WaitMsg wm = new WaitMsg();
wm.Show();
using (FbConnection con = new FbConnection(M.Baza.connectionKomercijalno2018))
{
con.Open();
using (FbCommand cmd = new FbCommand("NAPRAVIDOKUMENT", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("VRDOK", FbDbType.Integer).Value = 19;
cmd.Parameters.Add("BR_NAR", FbDbType.VarChar).Value = string.Format("M_1_{0}", brDok);
cmd.Parameters.Add("PPID", FbDbType.Integer).Value = null;
cmd.Parameters.Add("NAPOMENA", FbDbType.VarChar).Value = string.Format("{0} --- M_1_{1}", komentar, brDok);
cmd.Parameters.Add("NACUPLID", FbDbType.Integer).Value = 0;
cmd.Parameters.Add("MAGACINID", FbDbType.Integer).Value = 12;
cmd.Parameters.Add("BRDOK", FbDbType.Integer);
cmd.Parameters["BRDOK"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
pretvorenUBrDokKomercijalno = Convert.ToInt32(cmd.Parameters["BRDOK"].Value);
pretvorenUVrDokKomercijalno = 19;
}
using (FbCommand cmd = new FbCommand("NAPRAVISTAVKU", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("VRDOK", FbDbType.Integer).Value = pretvorenUVrDokKomercijalno;
cmd.Parameters.Add("BRDOK", FbDbType.Integer).Value = pretvorenUBrDokKomercijalno;
cmd.Parameters.Add("ROBAID", FbDbType.Integer);
cmd.Parameters.Add("CENA_BEZ_PDV", FbDbType.Numeric).Value = 0;
cmd.Parameters.Add("KOL", FbDbType.Numeric);
cmd.Parameters.Add("RABAT", FbDbType.Numeric).Value = 0;
foreach (DataGridViewRow row in dataGridView1.Rows)
{
cmd.Parameters["ROBAID"].Value = Convert.ToInt32(row.Cells["ROBAID"].Value);
cmd.Parameters["KOL"].Value = Convert.ToInt32(row.Cells["KOLICINA"].Value);
cmd.ExecuteNonQuery();
}
}
using (FbCommand cmd = new FbCommand("UPDATE DOKUMENT SET MAGID = @MagID, ZAPID = @ZapID, REFID = @ZapId WHERE VRDOK = @VrDok AND BRDOK = @BrDok", con))
{
cmd.Parameters.AddWithValue("@MagID", 6);
cmd.Parameters.AddWithValue("@ZapID", 5);
cmd.Parameters.AddWithValue("@BrDok", pretvorenUBrDokKomercijalno);
cmd.Parameters.AddWithValue("@VrDok", pretvorenUVrDokKomercijalno);
cmd.ExecuteNonQuery();
}
MessageBox.Show("Magacin uspesno razduzen po internoj otpremnici br. " + pretvorenUBrDokKomercijalno);
con.Close();
}
using (FbConnection con = new FbConnection(M.Baza.connectionString))
{
con.Open();
using (FbCommand cmd = new FbCommand("UPDATE DOKUMENT SET PRETVOREN_U_VRDOK_KOMERCIJALNO = @PUVRDOK, PRETVOREN_U_BRDOK_KOMERCIJALNO = @PUBRDOK WHERE BRDOK = @BrDok AND VRDOK = @VrDok", con))
{
cmd.Parameters.AddWithValue("@PUVRDOK", pretvorenUVrDokKomercijalno);
cmd.Parameters.AddWithValue("@PUBRDOK", pretvorenUBrDokKomercijalno);
cmd.Parameters.AddWithValue("@VrDok", vrDok);
cmd.Parameters.AddWithValue("@BrDok", brDok);
cmd.ExecuteNonQuery();
}
con.Close();
}
UcitajDokument();
wm.Close();
}
}
catch (FbException ex)
{
MessageBox.Show(ex.ToString());
}
}
/// <summary>
/// Ensures replicated tables have the right columns setup
/// </summary>
/// <param name="_LocalDB"></param>
/// <param name="tran"></param>
/// <param name="updateFile"></param>
/// <param name="tableName"></param>
private string ReplicateTableHasReplicateFields(FbConnection conn, FbTransaction tran,
StreamWriter updateFile, string tableName)
{
string Result = String.Empty;
string SQL = String.Format("SELECT COUNT(a.RDB$FIELD_NAME) FROM RDB$RELATION_FIELDS a " +
"WHERE a.RDB$FIELD_NAME = 'REPLICATE$HASH' AND a.RDB$RELATION_NAME = '{0}'", tableName);
FbDataReader rdr = null;
FbCommand cmd = new FbCommand(SQL, conn, tran);
try
{
rdr = cmd.ExecuteReader();
if (rdr.Read())
{
string activateTriggers = String.Empty;
string deactivateTriggers = String.Empty;
if (rdr.GetInt32(0) == 0)
{
string sqlTriggers = String.Format("SELECT TRIM(a.RDB$TRIGGER_NAME) " +
"FROM RDB$TRIGGERS a WHERE a.RDB$SYSTEM_FLAG = 0 " +
" AND a.RDB$TRIGGER_INACTIVE = 0 AND TRIM(UPPER(a.RDB$RELATION_NAME)) = '{0}'",
tableName.Trim().ToUpper());
FbDataReader rdrTriggers = null;
FbCommand cmdTriggers = new FbCommand(sqlTriggers, conn, tran);
try
{
rdrTriggers = cmdTriggers.ExecuteReader();
while (rdrTriggers.Read())
{
deactivateTriggers += String.Format("ALTER TRIGGER {0} INACTIVE;\r\n", rdrTriggers.GetString(0));
activateTriggers += String.Format("ALTER TRIGGER {0} ACTIVE;\r\n", rdrTriggers.GetString(0));
}
}
finally
{
CloseAndDispose(ref cmd, ref rdrTriggers);
}
updateFile.Write(deactivateTriggers);
updateFile.Write(String.Format("ALTER TABLE {0} ADD REPLICATE$HASH BIGINT;\r\n", tableName));
string hashTriggerSQL = String.Format("SELECT r.TABLE_NAME, r.OPERATION, r.TRIGGER_NAME, " +
"r.EXCLUDE_FIELDS, r.LOCAL_ID_COLUMN \n" +
"FROM REPLICATE$TABLES r WHERE r.OPERATION = 'UPDATE' AND r.TABLE_NAME = '{0}' " +
"\nORDER BY r.TABLE_NAME, r.OPERATION ", tableName);
FbDataReader rdrHashTrigger = null;
FbCommand cmdHashTrigger = new FbCommand(hashTriggerSQL, conn, tran);
try
{
rdrHashTrigger = cmdHashTrigger.ExecuteReader();
if (rdrHashTrigger.Read())
{
updateFile.Write(ReplicateCreateTriggerUpdateHash(conn, tran,
rdrHashTrigger.GetString(0).Trim(), rdrHashTrigger.GetString(2).Trim(),
rdrHashTrigger.GetString(3), rdrHashTrigger.GetString(4)));
}
}
finally
{
CloseAndDispose(ref cmd, ref rdrHashTrigger);
}
updateFile.Write(String.Format("UPDATE {0} SET REPLICATE$HASH = NULL;\r\n", tableName));
updateFile.Write(activateTriggers);
updateFile.WriteLine(String.Empty);
Result += String.Format("{0}:", tableName);
}
}
}
finally
{
CloseAndDispose(ref cmd, ref rdr);
}
return(Result);
}
public int Save(CFOPEntity Entity)
{
int result = 0;
try
{
//Verificando a existência de um transação aberta
if (dbTransaction != null)
{
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Sav_CFOP", dbCnn);
dbCommand.Transaction = ((FbTransaction)(dbTransaction));
}
else
{
if (dbCnn == null)
{
dbCnn = ((FbConnection)GetConnectionDB());
}
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Sav_CFOP", dbCnn);
dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted);
}
dbCommand.CommandType = CommandType.StoredProcedure;
if (Entity.IDCFOP != -1)
{
dbCommand.Parameters.AddWithValue("@IDCFOP", Entity.IDCFOP); //PrimaryKey
}
else
{
dbCommand.Parameters.AddWithValue("@IDCFOP", DBNull.Value); //PrimaryKey
}
dbCommand.Parameters.AddWithValue("@CODCFOP", Entity.CODCFOP); //Coluna
dbCommand.Parameters.AddWithValue("@DESCRICAO", Entity.DESCRICAO); //Coluna
dbCommand.Parameters.AddWithValue("@FLAGBAIXAESTOQUE", Entity.FLAGBAIXAESTOQUE); //Coluna
dbCommand.Parameters.AddWithValue("@OBSERVACAO", Entity.OBSERVACAO); //Coluna
//Retorno da Procedure
FbParameter returnValue;
returnValue = dbCommand.CreateParameter();
dbCommand.Parameters["@IDCFOP"].Direction = ParameterDirection.InputOutput;
//Executando consulta
dbCommand.ExecuteNonQuery();
result = int.Parse(dbCommand.Parameters["@IDCFOP"].Value.ToString());
if (dbTransaction == null)
{
dbCommand.Transaction.Commit();
dbCnn.Close();
}
}
catch (Exception ex)
{
if (dbTransaction != null)
{
this.RollbackTransaction();
}
else
{
if (dbCommand.Transaction != null)
{
dbCommand.Transaction.Rollback();
}
if (dbCnn.State == ConnectionState.Open)
{
dbCnn.Close();
}
}
throw ex;
}
return(result);
}
/// <summary>
/// Creates Update Replication Triggers based on rules table
/// </summary>
/// <param name="conn"></param>
/// <param name="tran"></param>
/// <param name="file"></param>
/// <param name="TableName"></param>
/// <param name="TriggerName"></param>
/// <param name="ExcludeFields"></param>
private string ReplicateCreateTriggerUpdate(FbConnection conn, FbTransaction tran, bool generateOnly,
string tableName, string triggerName, string excludeFields, string localIDColumn, TableOptions options)
{
string Result = String.Empty;
if (!excludeFields.EndsWith(":"))
{
excludeFields += ":";
}
int i = 0;
string Indexes = "";
string updateHash = "NEW.REPLICATE$HASH = HASH(";
string SQL = String.Format("select rc.RDB$RELATION_NAME, ris.rdb$field_name from rdb$relation_constraints rc " +
"join rdb$index_segments ris on ris.rdb$index_name = rc.rdb$index_name where rc.rdb$relation_name = '{0}' " +
"and rc.RDB$CONSTRAINT_TYPE = 'PRIMARY KEY'", tableName);
FbDataReader rdr = null;
FbCommand cmd = new FbCommand(SQL, conn, tran);
try
{
rdr = cmd.ExecuteReader();
bool First = true;
while (rdr.Read())
{
if (First)
{
Indexes += String.Format("'{0}', OLD.{0}", rdr.GetString(1).Trim());
First = false;
}
else
{
Indexes += String.Format(", '{0}', OLD.{0}", rdr.GetString(1).Trim());
}
i++;
}
}
finally
{
CloseAndDispose(ref cmd, ref rdr);
}
if (String.IsNullOrEmpty(Indexes))
{
return(String.Empty);
}
excludeFields += ":REPLICATE$HASH:";
Result += "SET TERM ^ ;\r\n";
Result += String.Format("CREATE OR ALTER TRIGGER REPLICATE${0}_U FOR {1} ACTIVE\r\n", triggerName, tableName);
Result += "BEFORE UPDATE POSITION 32767\r\n";
Result += "AS\r\n";
Result += " DECLARE VARIABLE vOperationLogID BIGINT;\r\n";
Result += " DECLARE VARIABLE vHASH BIGINT;\r\n";
Result += "BEGIN\r\n";
//Result += ReplicateCreateAnyRecordChangedTest(conn, tran, tableName, excludeFields, 3));
while (i < 3)
{
Indexes += ", NULL, NULL";
i++;
}
Result += String.Format(" EXECUTE PROCEDURE REPLICATE$OPERATIONLOG_INSERT ('{0}', 'UPDATE', {1}) " +
"RETURNING_VALUES :vOperationLogID;\r\n", tableName, Indexes);
Result += "\r\n";
SQL = String.Format("select f.rdb$field_name, CASE flds.RDB$FIELD_TYPE WHEN 261 THEN 50000 ELSE " +
"flds.RDB$CHARACTER_LENGTH END from rdb$relation_fields f join rdb$relations r on f.rdb$relation_name = " +
"r.rdb$relation_name and r.rdb$view_blr is null and (r.rdb$system_flag is null or r.rdb$system_flag = 0) " +
"join rdb$fields flds on flds.RDB$FIELD_NAME = f.RDB$FIELD_SOURCE WHERE f.RDB$RELATION_NAME = '{0}' " +
"order by 1, f.rdb$field_position;", tableName);
cmd = new FbCommand(SQL, conn, tran);
try
{
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
int Length = rdr.IsDBNull(1) ? 0 : rdr.GetInt32(1);
if (!excludeFields.Contains(String.Format("{0}:", rdr.GetString(0).Trim())))
{
if (updateHash.Length < 30)
{
updateHash += String.Format("COALESCE(NEW.{0}, '')", rdr.GetString(0).Trim());
}
else
{
updateHash += String.Format(" || COALESCE(NEW.{0}, '')", rdr.GetString(0).Trim());
}
#if LogRowData
if (options.HasFlag(TableOptions.LogRowData))
{
#endif
Result += String.Format(" IF ((OLD.{0} IS DISTINCT FROM NEW.{0})) THEN\r\n", rdr.GetString(0).Trim());
Result += " INSERT INTO REPLICATE$COLUMNLOG (ID, OPERATIONLOG_ID, COLUMN_NAME, OLD_VALUE, " +
"NEW_VALUE, OLD_VALUE_BLOB, NEW_VALUE_BLOB)\r\n";
if (Length < 301)
{
Result += String.Format(" VALUES (GEN_ID(REPLICATE$COLUMNLOG_ID, 1), :vOperationLogID, " +
"'{0}', OLD.{0}, NEW.{0}, NULL, NULL);\r\n", rdr.GetString(0).Trim());
}
else
{
Result += String.Format(" VALUES (GEN_ID(REPLICATE$COLUMNLOG_ID, 1), :vOperationLogID, " +
"'{0}', NULL, NULL, OLD.{0}, NEW.{0});\r\n", rdr.GetString(0).Trim());
}
Result += "\r\n";
#if LogRowData
}
#endif
}
}
}
finally
{
CloseAndDispose(ref cmd, ref rdr);
}
//Result += " END");
Result += "\r\n";
Result += String.Format(" {0});\r\n", updateHash);
Result += "END^\r\n";
Result += "SET TERM ; ^\r\n";
Result += "\r\n";
Result += "\r\n";
if (IncludeTrigger(conn, generateOnly, String.Format("{0}_U", triggerName), Result))
{
return(Result);
}
else
{
return(String.Empty);
}
}
private static MUNICIPIOSCollection ExecuteReader(ref MUNICIPIOSCollection collection, ref FbDataReader dataReader, FbCommand dbCommand)
{
using (dataReader = dbCommand.ExecuteReader())
{
collection = new MUNICIPIOSCollection();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
collection.Add(FillEntityObject(ref dataReader));
}
}
if (!(dataReader.IsClosed))
{
dataReader.Close();
}
dataReader.Dispose();
}
return(collection);
}
/// <summary>
/// Creates Update Replication Triggers based on rules table
/// </summary>
/// <param name="conn"></param>
/// <param name="tran"></param>
/// <param name="file"></param>
/// <param name="TableName"></param>
/// <param name="TriggerName"></param>
/// <param name="ExcludeFields"></param>
private string ReplicateCreateTriggerUpdateHash(FbConnection conn, FbTransaction tran,
string tableName, string triggerName, string excludeFields, string localIDColumn)
{
string Result = String.Empty;
int i = 0;
string updateHash = "NEW.REPLICATE$HASH = HASH(";
string Indexes = "";
string SQL = String.Format("select rc.RDB$RELATION_NAME, ris.rdb$field_name from rdb$relation_constraints rc " +
"join rdb$index_segments ris on ris.rdb$index_name = rc.rdb$index_name where rc.rdb$relation_name = '{0}' " +
"and rc.RDB$CONSTRAINT_TYPE = 'PRIMARY KEY'", tableName);
FbDataReader rdr = null;
FbCommand cmd = new FbCommand(SQL, conn, tran);
try
{
rdr = cmd.ExecuteReader();
bool First = true;
while (rdr.Read())
{
if (First)
{
Indexes += String.Format("'{0}', OLD.{0}", rdr.GetString(1).Trim());
First = false;
}
else
{
Indexes += String.Format(", '{0}', OLD.{0}", rdr.GetString(1).Trim());
}
i++;
}
}
finally
{
CloseAndDispose(ref cmd, ref rdr);
}
if (String.IsNullOrEmpty(Indexes))
{
return(String.Empty);
}
if (!excludeFields.EndsWith(":"))
{
excludeFields += ":";
}
excludeFields += ":REPLICATE$HASH:";
Result += "\r\n\r\nSET TERM ^ ;\r\n";
Result += String.Format("CREATE OR ALTER TRIGGER REPLICATE${0}_U FOR {1} ACTIVE\r\n", triggerName, tableName);
Result += "BEFORE UPDATE POSITION 32767\r\n";
Result += "AS\r\n";
Result += " DECLARE VARIABLE vHASH BIGINT;\r\n";
Result += "BEGIN\r\n";
SQL = String.Format("select f.rdb$field_name, CASE flds.RDB$FIELD_TYPE WHEN 261 THEN 50000 ELSE " +
"flds.RDB$CHARACTER_LENGTH END from rdb$relation_fields f join rdb$relations r on " +
"f.rdb$relation_name = r.rdb$relation_name " +
"and r.rdb$view_blr is null and (r.rdb$system_flag is null or r.rdb$system_flag = 0) join rdb$fields " +
"flds on flds.RDB$FIELD_NAME = f.RDB$FIELD_SOURCE WHERE f.RDB$RELATION_NAME = '{0}' " +
"order by 1, f.rdb$field_position;", tableName);
cmd = new FbCommand(SQL, conn, tran);
try
{
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
int Length = rdr.IsDBNull(1) ? 0 : rdr.GetInt32(1);
if (!excludeFields.Contains(String.Format("{0}:", rdr.GetString(0).Trim())))
{
if (updateHash.Length < 30)
{
updateHash += String.Format("COALESCE(NEW.{0}, '')", rdr.GetString(0).Trim());
}
else
{
updateHash += String.Format(" || COALESCE(NEW.{0}, '')", rdr.GetString(0).Trim());
}
}
}
}
finally
{
CloseAndDispose(ref cmd, ref rdr);
}
Result += String.Format(" {0});\r\n", updateHash);
Result += "END^\r\n";
Result += "SET TERM ; ^\r\n";
Result += "\r\n";
Result += "\r\n";
return(Result);
}
public int Delete(int COD_MUN_IBGE)
{
int result = 0;
try
{
//Verificando a existência de um transação aberta
if (dbTransaction != null)
{
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Del_MUNICIPIOS", dbCnn);
dbCommand.Transaction = ((FbTransaction)(dbTransaction));
}
else
{
if (dbCnn == null)
{
dbCnn = ((FbConnection)GetConnectionDB());
}
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Del_MUNICIPIOS", dbCnn);
dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted);
}
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.Parameters.AddWithValue("@COD_MUN_IBGE", COD_MUN_IBGE); //PrimaryKey
//Executando consulta
dbCommand.ExecuteNonQuery();
result = COD_MUN_IBGE;
if (dbTransaction == null)
{
dbCommand.Transaction.Commit();
dbCnn.Close();
}
}
catch (Exception ex)
{
if (dbTransaction != null)
{
this.RollbackTransaction();
}
else
{
if (dbCommand.Transaction != null)
{
dbCommand.Transaction.Rollback();
}
if (dbCnn.State == ConnectionState.Open)
{
dbCnn.Close();
}
}
throw ex;
}
return(result);
}
private void btConfirmaMedicamento_Click(object sender, EventArgs e)
{
FbConnection fbconn = new FbConnection(frmHome.strConn);
if (txtDataMedicacao.Text == " / /")
{
MessageBox.Show("Preencher a data da aplicação");
}
else if (txtNomeMedicamento.Text == "")
{
MessageBox.Show("Preencher Nome do medicamento");
}
else
{
string query;
int i = 0;
try
{
FbCommand fbcmd = new FbCommand();
fbconn.Open();
FbParameter[] fbprm = new FbParameter[2];
foreach (int num in list)
{
query = string.Format("INSERT INTO VACINACA (ID_GADO,NOME,DATA_APLICACAO) VALUES ({0},@NOME,@DATA_APLICACAO)", num.ToString());
fbprm[0] = new FbParameter("@NOME", txtNomeMedicamento.Text);
fbprm[1] = new FbParameter("@DATA_APLICACAO", Convert.ToDateTime(txtDataMedicacao.Text));
foreach (FbParameter p in fbprm)
{
fbcmd.Parameters.Add(p);
}
fbcmd.Connection = fbconn;
fbcmd.CommandType = CommandType.Text;
fbcmd.CommandText = query;
fbcmd.ExecuteNonQuery();
i++;
}
if (txtDataProxVacina.Text != " / /")
{
FbCommand comando = new FbCommand();
FbParameter[] prm = new FbParameter[2];
string queryInsert = string.Format("INSERT INTO AGENDA (EVENTO,DATA_ALERTA, ALERTADO) VALUES (@EVENTO,@DATA_ALERTA,0)");
prm[0] = new FbParameter("@EVENTO", "Vacina " + txtNomeMedicamento.Text);
prm[1] = new FbParameter("@DATA_ALERTA", Convert.ToDateTime(txtDataProxVacina.Text));
foreach (FbParameter p in prm)
{
comando.Parameters.Add(p);
}
comando.Connection = fbconn;
comando.CommandType = CommandType.Text;
comando.CommandText = queryInsert;
comando.ExecuteNonQuery();
}
}
catch (Exception)
{
throw;
}
MessageBox.Show("Vacina cadastrada com sucesso");
if (rbOutroMedicamentoSim.Checked == true)
{
txtNomeMedicamento.Clear();
txtDataMedicacao.Clear();
rbOutroMedicamentoSim.Checked = false;
rbOutroMedicamentoNao.Checked = false;
}
else
{
Close();
}
}
}
public MUNICIPIOSCollection ReadCollectionByParameter(List <RowsFiltro> RowsFiltro)
{
FbDataReader dataReader = null;
MUNICIPIOSCollection collection = null;
string strSqlCommand = String.Empty;
try
{
if (RowsFiltro != null)
{
if (RowsFiltro.Count > 0)
{
strSqlCommand = "SELECT * FROM MUNICIPIOS WHERE (";
ArrayList _rowsFiltro = new ArrayList();
RowsFiltro.ForEach(delegate(RowsFiltro i)
{
string[] item = { i.Condicao.ToString(), i.Campo.ToString(), i.Tipo.ToString(), i.Operador.ToString(), i.Valor.ToString() };
_rowsFiltro.Add(item);
});
int _count = 1;
foreach (string[] item in _rowsFiltro)
{
strSqlCommand += "(" + item[1] + " " + item[3];
switch (item[2])
{
case ("System.String"):
if (item[3].ToUpper() != "LIKE")
{
strSqlCommand += " '" + item[4] + "')";
}
else
{
strSqlCommand += " '%" + item[4] + "%')";
}
break;
case ("System.Int16"):
if (item[3].ToUpper() != "LIKE")
{
strSqlCommand += " " + item[4] + ")";
}
else
{
strSqlCommand += " '%" + item[4] + "%')";
}
break;
case ("System.Int32"):
if (item[3].ToUpper() != "LIKE")
{
strSqlCommand += " " + item[4] + ")";
}
else
{
strSqlCommand += " '%" + item[4] + "%')";
}
break;
case ("System.Int64"):
if (item[3].ToUpper() != "LIKE")
{
strSqlCommand += " " + item[4] + ")";
}
else
{
strSqlCommand += " '%" + item[4] + "%')";
}
break;
case ("System.Double"):
if (item[3].ToUpper() != "LIKE")
{
strSqlCommand += " " + item[4] + ")";
}
else
{
strSqlCommand += " '%" + item[4] + "%')";
}
break;
case ("System.Decimal"):
if (item[3].ToUpper() != "LIKE")
{
strSqlCommand += " " + item[4] + ")";
}
else
{
strSqlCommand += " '%" + item[4] + "%')";
}
break;
case ("System.Float"):
if (item[3].ToUpper() != "LIKE")
{
strSqlCommand += " " + item[4] + ")";
}
else
{
strSqlCommand += " '%" + item[4] + "%')";
}
break;
case ("System.Byte"):
strSqlCommand += " " + item[4] + ")";
break;
case ("System.SByte"):
strSqlCommand += " " + item[4] + ")";
break;
case ("System.Char"):
if (item[3].ToUpper() != "LIKE")
{
strSqlCommand += " '" + item[4] + "')";
}
else
{
strSqlCommand += " '%" + item[4] + "%')";
}
break;
case ("System.DateTime"):
if (item[3].ToUpper() != "LIKE")
{
strSqlCommand += " '" + item[4] + "')";
}
else
{
strSqlCommand += " '%" + item[4] + "%')";
}
break;
case ("System.Guid"):
if (item[3].ToUpper() != "LIKE")
{
strSqlCommand += " '" + item[4] + "')";
}
else
{
strSqlCommand += " '%" + item[4] + "%')";
}
break;
case ("System.Boolean"):
strSqlCommand += " " + item[4] + ")";
break;
}
if (_rowsFiltro.Count > 1)
{
if (_count < _rowsFiltro.Count)
{
strSqlCommand += " " + item[0] + " ";
}
_count++;
}
}
strSqlCommand += ");";
}
else
{
strSqlCommand = "SELECT * FROM MUNICIPIOS ";
}
}
else
{
strSqlCommand = "SELECT * FROM MUNICIPIOS ";
}
//Verificando a existência de um transação
if (dbTransaction != null)
{
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand(strSqlCommand, dbCnn);
dbCommand.CommandType = CommandType.Text;
dbCommand.Transaction = ((FbTransaction)(dbTransaction));
}
else
{
if (dbCnn == null)
{
dbCnn = new FbConnection(connectionString);
}
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand(strSqlCommand, dbCnn);
dbCommand.CommandType = CommandType.Text;
dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted);
}
collection = ExecuteReader(ref collection, ref dataReader, dbCommand);
if (dataReader != null)
{
dataReader.Close();
dataReader.Dispose();
}
if (dbTransaction == null)
{
dbCommand.Transaction.Commit();
dbCnn.Close();
}
return(collection);
}
catch (Exception ex)
{
// Deleta reader
if (dataReader != null)
{
dataReader.Close();
dataReader.Dispose();
}
if (dbTransaction != null)
{
this.RollbackTransaction();
}
else
{
if (dbCommand.Transaction != null)
{
dbCommand.Transaction.Rollback();
}
if (dbCnn.State == ConnectionState.Open)
{
dbCnn.Close();
}
}
throw ex;
}
}
private void Load_MForm(object sender, EventArgs e)
{
//Настройки для компонента GMap.
gMapControl1.Bearing = 0;
//CanDragMap - Если параметр установлен в True,
//пользователь может перетаскивать карту
///с помощью правой кнопки мыши.
gMapControl1.CanDragMap = true;
//Указываем, что перетаскивание карты осуществляется
//с использованием левой клавишей мыши.
//По умолчанию - правая.
gMapControl1.DragButton = MouseButtons.Left;
gMapControl1.GrayScaleMode = true;
//MarkersEnabled - Если параметр установлен в True,
//любые маркеры, заданные вручную будет показаны.
//Если нет, они не появятся.
gMapControl1.MarkersEnabled = true;
//Указываем значение максимального приближения.
gMapControl1.MaxZoom = 18;
//Указываем значение минимального приближения.
gMapControl1.MinZoom = 2;
//Устанавливаем центр приближения/удаления
//курсор мыши.
gMapControl1.MouseWheelZoomType =
GMap.NET.MouseWheelZoomType.MousePositionAndCenter;
//Отказываемся от негативного режима.
gMapControl1.NegativeMode = false;
//Разрешаем полигоны.
gMapControl1.PolygonsEnabled = true;
//Разрешаем маршруты
gMapControl1.RoutesEnabled = true;
//Скрываем внешнюю сетку карты
//с заголовками.
gMapControl1.ShowTileGridLines = false;
//Указываем, что при загрузке карты будет использоваться
//18ти кратной приближение.
gMapControl1.Zoom = 15;
//Указываем что все края элемента управления
//закрепляются у краев содержащего его элемента
//управления(главной формы), а их размеры изменяются
//соответствующим образом.
gMapControl1.Dock = DockStyle.None;
//Указываем что будем использовать карты Google.
gMapControl1.MapProvider =
GMap.NET.MapProviders.GMapProviders.YandexMap;
GMap.NET.GMaps.Instance.Mode =
GMap.NET.AccessMode.ServerOnly;
//Если вы используете интернет через прокси сервер,
//указываем свои учетные данные.
GMap.NET.MapProviders.GMapProvider.WebProxy =
System.Net.WebRequest.GetSystemWebProxy();
GMap.NET.MapProviders.GMapProvider.WebProxy.Credentials =
System.Net.CredentialCache.DefaultCredentials;
//Устанавливаем координаты центра карты для загрузки.
gMapControl1.Position = new GMap.NET.PointLatLng(55.75393, 37.620795);
//Создаем новый список маркеров, с указанием компонента
//в котором они будут использоваться и названием списка.
markersOverlay = new GMap.NET.WindowsForms.GMapOverlay("metka");
//Устанавливаем свои методы на события.
// gMapControl1.OnMapZoomChanged += new MapZoomChanged(mapControl_OnMapZoomChanged);
// gMapControl1.MouseClick += new MouseEventHandler(mapControl_MouseClick);
gMapControl1.MouseDown += new MouseEventHandler(Кнопка_Нажата);
gMapControl1.MouseUp += new MouseEventHandler(Кнопка_отпущена);
gMapControl1.MouseMove += new MouseEventHandler(Перетаскивание);
gMapControl1.OnMarkerClick += new MarkerClick(клик_по_маркеру);
gMapControl1.OnMarkerEnter += new MarkerEnter(выбор_макркера);
gMapControl1.OnMarkerLeave += new MarkerLeave(mapControl_OnMarkerLeave);
//Добавляем в элемент управления карты
//список маркеров.
gMapControl1.Overlays.Add(markersOverlay);;
//формируем connection string для последующего соединения с нашей базой данных
FbConnection fb_con = new FbConnection(BDConnect.ConnectionStr);
fb_con.Open();
string queryBrand = "select NAME_ from ROUTS";
FbCommand com = new FbCommand(queryBrand, fb_con);
FbDataReader dr = com.ExecuteReader();
while (dr.Read())
{
comboBox1.Items.Add(dr.GetString(5));
// Console.WriteLine(dr.GetString(0));
}
dr.Close();
fb_con.Close();
Console.ReadLine();
}
private static void CreateProcedures(string connectionString)
{
FbConnection connection = new FbConnection(connectionString);
connection.Open();
StringBuilder commandText = new StringBuilder();
// SELECT_DATA
commandText = new StringBuilder();
commandText.Append("CREATE PROCEDURE SELECT_DATA \r\n");
commandText.Append("RETURNS ( \r\n");
commandText.Append("INT_FIELD INTEGER, \r\n");
commandText.Append("VARCHAR_FIELD VARCHAR(100), \r\n");
commandText.Append("DECIMAL_FIELD DECIMAL(15,2)) \r\n");
commandText.Append("AS \r\n");
commandText.Append("begin \r\n");
commandText.Append("FOR SELECT INT_FIELD, VARCHAR_FIELD, DECIMAL_FIELD FROM TEST INTO :INT_FIELD, :VARCHAR_FIELD, :DECIMAL_FIELD \r\n");
commandText.Append("DO \r\n");
commandText.Append("SUSPEND; \r\n");
commandText.Append("end;");
FbCommand command = new FbCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
// GETRECORDCOUNT
commandText = new StringBuilder();
commandText.Append("CREATE PROCEDURE GETRECORDCOUNT \r\n");
commandText.Append("RETURNS ( \r\n");
commandText.Append("RECCOUNT SMALLINT) \r\n");
commandText.Append("AS \r\n");
commandText.Append("begin \r\n");
commandText.Append("for select count(*) from test into :reccount \r\n");
commandText.Append("do \r\n");
commandText.Append("suspend; \r\n");
commandText.Append("end\r\n");
command = new FbCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
// GETVARCHARFIELD
commandText = new StringBuilder();
commandText.Append("CREATE PROCEDURE GETVARCHARFIELD (\r\n");
commandText.Append("ID INTEGER)\r\n");
commandText.Append("RETURNS (\r\n");
commandText.Append("VARCHAR_FIELD VARCHAR(100))\r\n");
commandText.Append("AS\r\n");
commandText.Append("begin\r\n");
commandText.Append("for select varchar_field from test where int_field = :id into :varchar_field\r\n");
commandText.Append("do\r\n");
commandText.Append("suspend;\r\n");
commandText.Append("end\r\n");
command = new FbCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
// GETASCIIBLOB
commandText = new StringBuilder();
commandText.Append("CREATE PROCEDURE GETASCIIBLOB (\r\n");
commandText.Append("ID INTEGER)\r\n");
commandText.Append("RETURNS (\r\n");
commandText.Append("ASCII_BLOB BLOB SUB_TYPE 1)\r\n");
commandText.Append("AS\r\n");
commandText.Append("begin\r\n");
commandText.Append("for select clob_field from test where int_field = :id into :ascii_blob\r\n");
commandText.Append("do\r\n");
commandText.Append("suspend;\r\n");
commandText.Append("end\r\n");
command = new FbCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
// DATAREADERTEST
commandText = new StringBuilder();
commandText.Append("CREATE PROCEDURE DATAREADERTEST\r\n");
commandText.Append("RETURNS (\r\n");
commandText.Append("content VARCHAR(128))\r\n");
commandText.Append("AS\r\n");
commandText.Append("begin\r\n");
commandText.Append("content = 'test';\r\n");
commandText.Append("end\r\n");
command = new FbCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
connection.Close();
}
public LIS_CAIXACollection ReadCollection()
{
FbDataReader dataReader = null;
try
{
LIS_CAIXACollection collection = null;
//Verificando a existência de um transação aberta
if (dbTransaction != null)
{
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("SELECT * FROM LIS_CAIXA", dbCnn);
dbCommand.Transaction = ((FbTransaction)(dbTransaction));
}
else
{
if (dbCnn == null)
{
dbCnn = ((FbConnection)GetConnectionDB());
}
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("SELECT * FROM LIS_CAIXA", dbCnn);
dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted);
}
// Tipo do comando de banco Procedure ou SQL
dbCommand.CommandType = CommandType.Text;
collection = ExecuteReader(ref collection, ref dataReader, dbCommand);
if (dataReader != null)
{
dataReader.Close();
dataReader.Dispose();
}
if (dbTransaction == null)
{
dbCommand.Transaction.Commit();
dbCnn.Close();
}
return(collection);
}
catch (Exception ex)
{
// Deleta reader
if (dataReader != null)
{
dataReader.Close();
dataReader.Dispose();
}
if (dbTransaction != null)
{
this.RollbackTransaction();
}
else
{
if (dbCommand.Transaction != null)
{
dbCommand.Transaction.Rollback();
}
if (dbCnn.State == ConnectionState.Open)
{
dbCnn.Close();
}
}
throw ex;
}
}
private static void InsertTestData(string connectionString)
{
FbConnection connection = new FbConnection(connectionString);
connection.Open();
StringBuilder commandText = new StringBuilder();
commandText.Append("insert into test (int_field, char_field, varchar_field, bigint_field, smallint_field, float_field, double_field, numeric_field, date_field, time_field, timestamp_field, clob_field, blob_field)");
commandText.Append(" values(@int_field, @char_field, @varchar_field, @bigint_field, @smallint_field, @float_field, @double_field, @numeric_field, @date_field, @time_field, @timestamp_field, @clob_field, @blob_field)");
FbTransaction transaction = connection.BeginTransaction();
FbCommand command = new FbCommand(commandText.ToString(), connection, transaction);
try
{
// Add command parameters
command.Parameters.Add("@int_field" , FbDbType.Integer);
command.Parameters.Add("@char_field" , FbDbType.Char);
command.Parameters.Add("@varchar_field" , FbDbType.VarChar);
command.Parameters.Add("@bigint_field" , FbDbType.BigInt);
command.Parameters.Add("@smallint_field" , FbDbType.SmallInt);
command.Parameters.Add("@float_field" , FbDbType.Double);
command.Parameters.Add("@double_field" , FbDbType.Double);
command.Parameters.Add("@numeric_field" , FbDbType.Numeric);
command.Parameters.Add("@date_field" , FbDbType.Date);
command.Parameters.Add("@time_Field" , FbDbType.Time);
command.Parameters.Add("@timestamp_field" , FbDbType.TimeStamp);
command.Parameters.Add("@clob_field" , FbDbType.Text);
command.Parameters.Add("@blob_field" , FbDbType.Binary);
command.Prepare();
for (int i = 0; i < 100; i++)
{
command.Parameters["@int_field"].Value = i;
command.Parameters["@char_field"].Value = "IRow " + i.ToString();
command.Parameters["@varchar_field"].Value = "IRow Number " + i.ToString();
command.Parameters["@bigint_field"].Value = i;
command.Parameters["@smallint_field"].Value = i;
command.Parameters["@float_field"].Value = (float)(i + 10)/5;
command.Parameters["@double_field"].Value = Math.Log(i, 10);
command.Parameters["@numeric_field"].Value = (decimal)(i + 10)/5;
command.Parameters["@date_field"].Value = DateTime.Now;
command.Parameters["@time_field"].Value = DateTime.Now;
command.Parameters["@timestamp_field"].Value= DateTime.Now;
command.Parameters["@clob_field"].Value = "IRow Number " + i.ToString();
command.Parameters["@blob_field"].Value = Encoding.Default.GetBytes("IRow Number " + i.ToString());
command.ExecuteNonQuery();
}
// Commit transaction
transaction.Commit();
}
catch (FbException)
{
transaction.Rollback();
throw;
}
finally
{
command.Dispose();
connection.Close();
}
}
public string Delete(string NUMERO, string CLIENTE, int ITEM)
{
string result = "0";
try
{
//Verificando a existência de um transação aberta
if (dbTransaction != null)
{
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Del_ITEDAV", dbCnn);
dbCommand.Transaction = ((FbTransaction)(dbTransaction));
}
else
{
if (dbCnn == null)
{
dbCnn = ((FbConnection)GetConnectionDB());
}
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Del_ITEDAV", dbCnn);
dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted);
}
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.Parameters.AddWithValue("@NUMERO", NUMERO); //PrimaryKey
dbCommand.Parameters.AddWithValue("@CLIENTE", CLIENTE); //PrimaryKey
dbCommand.Parameters.AddWithValue("@ITEM", ITEM); //PrimaryKey
//Executando consulta
dbCommand.ExecuteNonQuery();
result = NUMERO;
if (dbTransaction == null)
{
dbCommand.Transaction.Commit();
dbCnn.Close();
}
}
catch (Exception ex)
{
if (dbTransaction != null)
{
this.RollbackTransaction();
}
else
{
if (dbCommand.Transaction != null)
{
dbCommand.Transaction.Rollback();
}
if (dbCnn.State == ConnectionState.Open)
{
dbCnn.Close();
}
}
throw ex;
}
return(result);
}
