public async Task <bool> Init(ParametersRequest parametersRequest, ShowText showText)
{
_con?.Close();
_con = await Connect(parametersRequest, showText);
return(_con != null);
}
private List <Dictionary <string, object> > GetDataRowsFromSql(string sqlQuery, string connectionString)
{
List <Dictionary <string, object> > dataList = new List <Dictionary <string, object> >();
FbConnection connection = null;
FbDataReader reader = null;
FbCommand command = null;
try
{
connection = new FbConnection(connectionString);
connection.Open();
command = new FbCommand(sqlQuery, connection);
reader = command.ExecuteReader();
while (reader.Read())
{
var dataItem = new Dictionary <string, object>();
for (int i = 0; i < reader.FieldCount; i++)
{
var columnName = reader.GetName(i);
var columnValue = reader[i];
dataItem.Add(columnName, columnValue);
}
dataList.Add(dataItem);
}
}
catch (Exception ex)
{
throw;
}
finally
{
//command?.Dispose();
//reader?.Close();
connection?.Close();
connection?.Dispose();
}
return(dataList);
}
public void Run()
{
if (isRunning)
{
return;
}
FbConnection connection = null;
try
{
isRunning = true;
logToFile.Log($"Serviço em execução.");
//Autentica na API
this.accessToken = Autenticacao().Result;
RunIntegracaoFormulas();
RunIntegracaoOrcamentos();
isRunning = false;
logToFile.Log($"Fim da execução.");
}
catch (Exception ex)
{
logToFile.Log($"Erro ao executar a integração. Exception: {ex.Message} {ex.InnerException?.Message}");
isRunning = false;
//Encerrando a conexão
connection?.Close();
connection?.Dispose();
ex.Data.Add("idFarmaciaIntegracao", idFarmaciaIntegracao);
sentry.Capture(new SharpRaven.Data.SentryEvent(ex));
}
}
public void SatisIptali()
{
try
{
SatisId.Text = dataGridView1.CurrentRow.Cells[0].Value.ToString();
string[] ads = SatisId.Text.Split(' ');
if (ads[0] == "S")
{
SatisId.Text = ads[1].ToString();
FbConnection b1 = new FbConnection(fk.Baglanti_Kodu());
b1.Open();
FbCommand SatisUrun = new FbCommand("SELECT Satis_urun FROM Satis WHERE Satis_id='" + SatisId.Text + "'", b1);
FbDataReader Oku = SatisUrun.ExecuteReader();
Oku.Read();
string urunid = Oku["Satis_urun"].ToString();
Oku.Close();
b1.Close();
DialogResult m = MessageBox.Show(dataGridView1.CurrentRow.Cells[1].Value.ToString() + " Satışı iptal edilecek. Emin misiniz ? ", "Onaylama", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (DialogResult.Yes == m)
{
fk.Sil("Satis", "Satis_id='" + SatisId.Text + "' and Satis_tarih='" + tarih + "'");
b1.Open();
FbCommand RaporSor = new FbCommand("SELECT * FROM Rapor WHERE rapor_satisId='" + urunid + "' and rapor_tarih='" + tarih + "'", b1);
FbDataReader cek = RaporSor.ExecuteReader();
cek.Read(); int say = int.Parse(cek["rapor_sayac"].ToString()); cek.Close();
b1.Close();
if (say > 1)
{
byte sonuc = fk.Guncelle("Rapor", "rapor_sayac=rapor_sayac-1", "rapor_satisId='" + urunid + "' and rapor_tarih='" + tarih + "'");
if (sonuc == 1)
{
MessageBox.Show("Satış başarıyla silinmiştir", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Information);
GridGuncelle();
FormLoad();
}
else
{
MessageBox.Show("Satış silinemedi", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
byte sonuc = fk.Sil("Rapor", "rapor_satisId='" + urunid + "' and rapor_tarih='" + tarih + "'");
if (sonuc == 1)
{
MessageBox.Show("Satış başarıyla silinmiştir", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Information);
GridGuncelle();
FormLoad();
}
else
{
MessageBox.Show("Satış silinemedi", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}
else
{
SatisId.Text = ads[1].ToString();
DialogResult m = MessageBox.Show(dataGridView1.CurrentRow.Cells[1].Value.ToString() + " Satışı iptal edilecek. Emin misiniz ? ", "Onaylama", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (DialogResult.Yes == m)
{
byte sonuc = fk.Sil("ManuelSatis", "satis_id='" + SatisId.Text + "'");
if (sonuc == 1)
{
MessageBox.Show("Satış başarıyla silinmiştir", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("Satış silinemedi", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}
catch (Exception e)
{
MessageBox.Show(e.Message, "Hata", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
static void Main(string[] args)
{
var databasefilename = System.IO.Path.GetFullPath("demo.fdb");
Console.WriteLine("databasefilename: {0}", databasefilename);
var firebirdNativeClientLibraryFilename = System.IO.Path.GetFullPath(@"Firebird-3.0.4.33054-x64-embedded\fbclient.dll");
Console.WriteLine("firebirdNativeClientLibraryFilename: {0}", firebirdNativeClientLibraryFilename);
if (File.Exists(firebirdNativeClientLibraryFilename) == false)
{
throw new Exception("Firebird Embedded Not Found At Expected Location!");
}
var csb = new FbConnectionStringBuilder();
csb.ServerType = FbServerType.Embedded;
csb.ClientLibrary = firebirdNativeClientLibraryFilename;
csb.Database = databasefilename;
csb.UserID = "ThisCanBeAnything";
csb.Password = "******";
var connectionstring = csb.ToString();
if (File.Exists(databasefilename) == false)
{
Console.WriteLine("Creating database {0}", databasefilename);
FbConnection.CreateDatabase(connectionstring, false);
Console.WriteLine("Created database {0}", databasefilename);
}
Console.WriteLine("Connecting");
using (var connection = new FbConnection(csb.ToString()))
{
connection.StateChange += Connection_StateChange;
connection.InfoMessage += Connection_InfoMessage;
connection.Disposed += Connection_Disposed;
connection.Open();
using (var trn = connection.BeginTransaction())
{
using (var cmd = connection.CreateCommand())
{
cmd.Transaction = trn;
cmd.CommandText = @"
RECREATE TABLE ATABLE
(
PK INTEGER NOT NULL,
NAME VARCHAR( 128),
CONSTRAINT PK_ATABLE PRIMARY KEY (PK)
);";
cmd.ExecuteNonQuery();
}
trn.Commit();
}
using (var trn = connection.BeginTransaction())
{
using (var cmd = connection.CreateCommand())
{
cmd.Transaction = trn;
cmd.CommandText = "INSERT INTO ATABLE (PK,NAME) VALUES (@PK,@NAME)";
cmd.Parameters.Add("PK", 1);
cmd.Parameters.Add("NAME", "Adam");
cmd.ExecuteNonQuery();
}
using (var cmd = connection.CreateCommand())
{
cmd.Transaction = trn;
cmd.CommandText = "INSERT INTO ATABLE (PK,NAME) VALUES (@PK,@NAME)";
cmd.Parameters.Add("PK", 2);
cmd.Parameters.Add("NAME", "Eve");
cmd.ExecuteNonQuery();
}
using (var cmd = connection.CreateCommand())
{
cmd.Transaction = trn;
cmd.CommandText = "SELECT * from ATABLE";
using (var reader = cmd.ExecuteReader())
{
int RowIndex = 0;
while (reader.Read())
{
RowIndex += 1;
var PK = reader.GetFieldValue <int>(0);
var Name = reader.GetString(1);
Console.WriteLine("Row #{0} PK={1} Name={2}", RowIndex, PK, Name);
}
}
}
trn.Commit();
}
connection.Close();
}
Console.WriteLine("Press any key to exit");
Console.ReadKey();
}
public static void Save(DicomFile fil, string filn)
{
string strConnectionStringM = System.Configuration.ConfigurationManager.AppSettings["MSSQL_CONN_STRING"];
string strConnectionString = System.Configuration.ConfigurationManager.AppSettings["DB_CONN_STRING"];
FbConnection db = new FbConnection(strConnectionString);
Console.WriteLine(strConnectionString);
DicomFile _dicomFile = null;
DicomDataset _dicomDataset = null;
var dcf = fil;
Console.WriteLine(filn);
using (var memStream = new MemoryStream())
{
_dicomFile = dcf;
_dicomDataset = dcf.Dataset;
Console.WriteLine(dcf);
string _patientID = _dicomDataset.Get <string>(DicomTag.PatientID);
string _studyID = _dicomDataset.Get <string>(DicomTag.StudyID);
string _patientSex = _dicomDataset.Get <string>(DicomTag.PatientSex);
Console.WriteLine(_patientID);
string _studyInstanceUID = _dicomDataset.Get <string>(DicomTag.StudyInstanceUID);
Console.WriteLine(_dicomDataset.Get <string>(DicomTag.StudyInstanceUID));
string _seriesInstanceUID = _dicomDataset.Get <string>(DicomTag.SeriesInstanceUID);
string _modality = _dicomDataset.Get <string>(DicomTag.Modality);
Console.WriteLine(_modality);
string _seriesDescription = _dicomDataset.Get <string>(DicomTag.SeriesDescription);
string _patientName = _dicomDataset.Get <string>(DicomTag.PatientName).Replace('^', ' ');
Console.WriteLine(_patientName);
DateTime _personBirthDate = _dicomDataset.Get <DateTime>(DicomTag.PatientBirthDate);
Console.WriteLine(_personBirthDate);
string _comments = _dicomDataset.Get <string>(DicomTag.PatientComments);
Console.WriteLine(_comments);
Guid _id = Guid.NewGuid();
Guid _persid = Guid.NewGuid();
Guid _aeTitleId = Guid.NewGuid();
DateTime dateTime = _dicomDataset.Get <DateTime>(DicomTag.StudyDate);
Console.WriteLine(dateTime);
_dicomFile.Save(memStream);
using (SqlConnection myC = new SqlConnection(strConnectionStringM))
{
string vsql4 = string.Format("Insert Into Millennium.dbo.ApplicationEntity" + "(ID, AeTitle, Description) Values (@ID, @AeTitle, @Description)");
string vsql1 = string.Format("IF NOT EXISTS(Select * from Millennium.dbo.Studies where StudyInstanceUID = @StudyInstanceUID)" + " Insert Into Millennium.dbo.Studies" + "(ID, Date, PersonID, Note , Result, MeanDose, Dose, iheClassEHRCode, Prophylaxis, IsResulted,IsPrinted, X_rayProcedureID, AeTitleId, StudyInstanceUID, Comment) Values (@ID, @Date, @PersonID, @Note, @Result, @MeanDose, @Dose, @iheClassEHRCode, @Prophylaxis, @IsResulted, @IsPrinted, @X_rayProcedureID, @AeTitleId, @StudyInstanceUID, @Comment)" + "ELSE Update Millennium.dbo.Studies set Date = @Date where StudyInstanceUID = @StudyInstanceUID");
string vsql3 = string.Format("IF NOT EXISTS(Select ID from Millennium.dbo.Studies where StudyInstanceUID = @StudyInstanceUID)" + "Insert Into Millennium.dbo.Person" + "(ID, Name, Birthday, iheGenderCode) Values (@ID, @Name, @Birthday,@iheGenderCode)" + "ELSE Update Millennium.dbo.Person set Birthday = @Birthday, Name = @Name where ID = (Select PersonID from Millennium.dbo.Studies where StudyInstanceUID = @StudyInstanceUID)");
string vsql2 = "IF NOT EXISTS(Select * from Millennium.dbo.Studies INNER JOIN Millennium.dbo.StudiesData ON Millennium.dbo.Studies.ID = Millennium.dbo.StudiesData.StudiesID where Millennium.dbo.Studies.StudyInstanceUID= @StudyInstanceUID)"
+ "Insert Into Millennium.dbo.StudiesData"
+ "(ID,StudiesID, FileId, Data, TransferSyntaxUID, Invert, SeriesInstanceUID, StudyInstanceUID, InstanceNumber) Values (@ID,@StudiesID, @FileId, @Data, @TransferSyntaxUID, @Invert, @SeriesInstanceUID, @StudyInstanceUID, @InstanceNumber)"
+ "ELSE Insert Into Millennium.dbo.StudiesData"
+ "(ID,StudiesID, FileId, Data, TransferSyntaxUID, Invert, SeriesInstanceUID, StudyInstanceUID, InstanceNumber) Values (@ID, (Select ID from Millennium.dbo.Studies where StudyInstanceUID = @StudyInstanceUID), @FileId, @Data, @TransferSyntaxUID, @Invert, @SeriesInstanceUID, @StudyInstanceUID, @InstanceNumber)";
SqlCommand cmd3 = new SqlCommand(vsql4, myC);
SqlCommand cmd2 = new SqlCommand(vsql3, myC);
SqlCommand cmd1 = new SqlCommand(vsql1, myC);
SqlCommand cmd = new SqlCommand(vsql2, myC);
cmd2.Parameters.Add("@StudyInstanceUID", SqlDbType.NVarChar).Value = _studyInstanceUID;
cmd3.Parameters.Add("@ID", SqlDbType.UniqueIdentifier).Value = _aeTitleId;
cmd3.Parameters.Add("@AeTitle", SqlDbType.NVarChar).Value = "ARCHIMED";
cmd3.Parameters.Add("@Description", SqlDbType.NVarChar).Value = "Создан";
cmd2.Parameters.Add("@ID", SqlDbType.UniqueIdentifier).Value = _persid;
//Kart_Pac
try
{
db.Open();
string SqlString3 = "SELECT FIO FROM KART_PAC WHERE NUMBERKART = " + filn;
var ourkart_pac = (List <kart_pac>)db.Query <kart_pac>(SqlString3);
Console.WriteLine(ourkart_pac.ElementAt(0).FIO);
if (ourkart_pac != null)
{
if (ourkart_pac.ElementAt(0).FIO != null)
{
string fiio = ourkart_pac.ElementAt(0).FIO;
Console.WriteLine(fiio);
cmd2.Parameters.Add("@Name", SqlDbType.NVarChar).Value = fiio;
}
else
{
cmd2.Parameters.Add("@Name", SqlDbType.NVarChar).Value = _patientName;
}
}
else
{
Console.WriteLine("***************");
}
db.Close();
}
catch (Exception e)
{
Console.WriteLine("нет соотоветствия в базе CLD, plain Dicom");
cmd2.Parameters.Add("@Name", SqlDbType.NVarChar).Value = _patientName;
db.Close();
// Console.WriteLine("The process failed: {0}", e.ToString());
//logger.Warn(e);
}
try
{
db.Open();
string SqlString4 = "SELECT NUMBERKART, OPISANIE, SAKL FROM PROTOCOL WHERE NUMBERKART = " + filn;
var out_protocol = (IEnumerable <protocol>)db.Query <protocol>(SqlString4);
if (out_protocol != null)
{
if (out_protocol.ElementAt(1).OPISANIE != null)
{
string protoc = StripRTF(out_protocol.ElementAt(1).OPISANIE);
cmd1.Parameters.Add("@Note", SqlDbType.NVarChar).Value = protoc;
Console.WriteLine(protoc);
}
else
{
cmd1.Parameters.Add("@Note", SqlDbType.NVarChar).Value = " ";
}
}
else
{
Console.WriteLine("************");
}
db.Close();
}
catch (Exception e)
{
Console.WriteLine("нет соотоветствующей базы CLD, файлы загружаются без протоколов");
cmd1.Parameters.Add("@Note", SqlDbType.NVarChar).Value = " ";
db.Close();
// Console.WriteLine("The process failed: {0}", e.ToString());
//logger.Warn(e);
}
// birthday and null excepеtion
DateTime etalon = new DateTime(1753, 1, 1, 12, 0, 0);
DateTime nothing = new DateTime();
Console.WriteLine(etalon);
if (_personBirthDate != nothing && _personBirthDate >= etalon)
{
cmd2.Parameters.Add("@Birthday", SqlDbType.DateTime).Value = _personBirthDate;
}
else
{
cmd2.Parameters.Add("@Birthday", SqlDbType.DateTime).Value = dateTime;
}
// patient sex
if (_patientSex != null)
{
if (_patientSex.Equals("M"))
{
cmd2.Parameters.Add("@iheGenderCode", SqlDbType.TinyInt).Value = 1;
}
else if (_patientSex.Equals("F"))
{
cmd2.Parameters.Add("@iheGenderCode", SqlDbType.TinyInt).Value = 2;
}
else
{
cmd2.Parameters.Add("@iheGenderCode", SqlDbType.TinyInt).Value = 3;
}
}
else
{
cmd2.Parameters.Add("@iheGenderCode", SqlDbType.TinyInt).Value = 3;
}
cmd1.Parameters.Add("@ID", SqlDbType.UniqueIdentifier).Value = _id;
cmd1.Parameters.Add("@Date", SqlDbType.DateTime).Value = dateTime;
cmd1.Parameters.Add("@PersonID", SqlDbType.UniqueIdentifier).Value = _persid;
// cmd1.Parameters.Add("@Note", SqlDbType.NVarChar).Value = "protoc";
/*
*
* if (out_protocol.ElementAt(1).OPISANIE != null)
* {
* string protoc = StripRTF(out_protocol.ElementAt(1).OPISANIE);
* cmd1.Parameters.Add("@Note", SqlDbType.NVarChar).Value = protoc;
* Console.WriteLine(protoc);
* }
* else
* {
* Console.WriteLine(" нет протокола");
* }*/
cmd1.Parameters.Add("@Result", SqlDbType.NVarChar).Value = " ";
/*
* if (out_protocol.ElementAt(0).SAKL != null)
* {
* string sakl = StripRTF(out_protocol.ElementAt(0).SAKL);
* Console.WriteLine(sakl);
* cmd1.Parameters.Add("@Result", SqlDbType.NVarChar).Value = sakl;
* }
* .else
* {
* cmd1.Parameters.Add("@Result", SqlDbType.NVarChar).Value = null;
* Console.WriteLine("no sakl");
* }*/
cmd1.Parameters.Add("@IsResulted", SqlDbType.Bit).Value = 0;
cmd1.Parameters.Add("@Prophylaxis", SqlDbType.Bit).Value = 0;
cmd1.Parameters.Add("@MeanDose", SqlDbType.Decimal).Value = 0.0;
cmd1.Parameters.Add("@Dose", SqlDbType.Decimal).Value = 0.0;
cmd1.Parameters.Add("@iheClassEHRCode", SqlDbType.NVarChar).Value = 2;
cmd1.Parameters.Add("@IsPrinted", SqlDbType.Bit).Value = 0;
// modality
if (_modality.Equals("CR"))
{
cmd1.Parameters.Add("@X_rayProcedureID", SqlDbType.TinyInt).Value = 4;
}
else if (_modality.Equals("CT"))
{
cmd1.Parameters.Add("@X_rayProcedureID", SqlDbType.TinyInt).Value = 6;
}
else if (_modality.Equals("MG"))
{
cmd1.Parameters.Add("@X_rayProcedureID", SqlDbType.TinyInt).Value = 4;
}
else if (_modality.Equals("MR"))
{
cmd1.Parameters.Add("@X_rayProcedureID", SqlDbType.TinyInt).Value = 9;
}
else if (_modality.Equals("US"))
{
cmd1.Parameters.Add("@X_rayProcedureID", SqlDbType.TinyInt).Value = 10;
}
else if (_modality.Equals("RF"))
{
cmd1.Parameters.Add("@X_rayProcedureID", SqlDbType.TinyInt).Value = 1;
}
else if (_modality.Equals("DF"))
{
cmd1.Parameters.Add("@X_rayProcedureID", SqlDbType.TinyInt).Value = 2;
}
else
{
cmd1.Parameters.Add("@X_rayProcedureID", SqlDbType.TinyInt).Value = 8;
}
// Studies
cmd1.Parameters.Add("@AeTitleId", SqlDbType.UniqueIdentifier).Value = _aeTitleId;
cmd1.Parameters.Add("@StudyInstanceUID", SqlDbType.NVarChar).Value = _dicomDataset.Get <string>(DicomTag.StudyInstanceUID);
// Comment
if (_comments != null)
{
cmd1.Parameters.Add("@Comment", SqlDbType.NVarChar).Value = _comments;
}
else
{
cmd1.Parameters.Add("@Comment", SqlDbType.NVarChar).Value = "1";
}
// studies_data
cmd.Parameters.Add("@ID", SqlDbType.UniqueIdentifier).Value = Guid.NewGuid();
cmd.Parameters.Add("@data", SqlDbType.VarBinary).Value = memStream.GetBuffer();
cmd.Parameters.Add("@invert", SqlDbType.Bit).Value = 0;
cmd.Parameters.Add("@transferSyntaxUID", SqlDbType.NVarChar).Value = _dicomDataset.InternalTransferSyntax.ToString();
// cmd.Parameters.Add("@windowsWidth", SqlDbType.Decimal).Value = dicomImage.WindowWidth;
// cmd.Parameters.Add("@windowsCenter", SqlDbType.Decimal).Value = dicomImage.WindowCenter;
cmd.Parameters.Add("@studiesID", SqlDbType.UniqueIdentifier).Value = _id;
cmd.Parameters.Add("@fileId", SqlDbType.UniqueIdentifier).Value = Guid.NewGuid();
cmd.Parameters.Add("@SeriesInstanceUID", SqlDbType.NVarChar).Value = _dicomDataset.Get <string>(DicomTag.SeriesInstanceUID);
cmd.Parameters.Add("@StudyInstanceUID", SqlDbType.NVarChar).Value = _dicomDataset.Get <string>(DicomTag.StudyInstanceUID);
cmd.Parameters.Add("@InstanceNumber", SqlDbType.SmallInt).Value = _dicomDataset.Get <string>(DicomTag.InstanceNumber);
myC.Open();
cmd3.ExecuteNonQuery();
cmd3 = null;
cmd2.ExecuteNonQuery();
cmd2 = null;
cmd1.ExecuteNonQuery();
cmd1 = null;
cmd.ExecuteNonQuery();
cmd = null;
myC.Close();
}
}
}
protected void btnGravar_Click(object sender, EventArgs e)
{
try
{
UsuarioWeb objUsuario = (UsuarioWeb)Session["ObjetoUsuario"];
string sST_DESC = objUsuario.oTabelas.hlpDbFuncoes.qrySeekValue("EMPRESA", "ST_DESC", "CD_EMPRESA = '" + objUsuario.oTabelas.sEmpresa + "'");
string sTipoDocumentoPadrao = cbxDS_TPDOCWEB.SelectedValue.ToString();
string Doc = objUsuario.oTabelas.hlpDbFuncoes.qrySeekValue("TPDOC", "TP_DOC", "CD_TIPODOC='" + sTipoDocumentoPadrao + "'");
string sGenerator = "PEDIDO" + objUsuario.oTabelas.sEmpresa + Doc;
if (!objUsuario.oTabelas.hlpDbFuncoes.VerificaExistenciaGenerator(sGenerator))
{
int iIni = 0;
switch (Doc)
{
case "PE": iIni = 0;
break;
case "NE": iIni = 1000000;
break;
case "NS": iIni = 1000000;
break;
case "CF": iIni = 2000000;
break;
case "PC": iIni = 9000000;
break;
case "OR": iIni = 8000000;
break;
default:
throw new Exception("O generator " + sGenerator + " não existe");
}
objUsuario.oTabelas.hlpDbFuncoes.CreateGenerator(sGenerator, iIni);
}
string sCD_PEDIDO = objUsuario.oTabelas.hlpDbFuncoes.RetornaProximoValorGenerator(sGenerator).PadLeft(7, '0');
string strConn = ConfigurationManager.ConnectionStrings["ConnectionStringFB"].ConnectionString.ToString();
FbConnection Conn = new FbConnection(strConn);
FbCommand cmdSelectPed = new FbCommand();
cmdSelectPed.CommandText = "SP_INCLUI_PEDIDO_WEB";
cmdSelectPed.CommandType = CommandType.StoredProcedure;
cmdSelectPed.Connection = Conn;
Conn.Open();
cmdSelectPed.Parameters.Add("@SCD_EMPRESA", FbDbType.VarChar, 3).Value = objUsuario.oTabelas.sEmpresa.ToString();
cmdSelectPed.Parameters.Add("@SCD_VEND1", FbDbType.VarChar, 7).Value = objUsuario.CodigoVendedor.ToString();
cmdSelectPed.Parameters.Add("@SCD_PEDIDO", FbDbType.VarChar, 7).Value = sCD_PEDIDO;
string strRetPedido = cmdSelectPed.ExecuteScalar().ToString();
Conn.Close();
if (strRetPedido != null)
{
StringBuilder strUpDatePed = new StringBuilder();
string sVL_TOTALPED = GridViewNovo.FooterRow.Cells[8].Text.Replace(".", "");
strUpDatePed.Append(" UPDATE PEDIDO SET CD_PEDIDO = '" + strRetPedido.Trim());
strUpDatePed.Append("', VL_TOTALPED = '" + sVL_TOTALPED.Replace(",", "."));
strUpDatePed.Append("', CD_CLIENTE = '" + txtCodCli.Text.Trim());
strUpDatePed.Append("', NM_GUERRA = '" + ((CliforDAO)(Session["ObjetoClienteDetalhado"])).RegistroAtual["NM_GUERRA"].ToString());
strUpDatePed.Append("', ST_DESC = '" + sST_DESC);
strUpDatePed.Append("', NM_CLIFOR = '" + ((CliforDAO)(Session["ObjetoClienteDetalhado"])).RegistroAtual["NM_CLIFOR"].ToString());
strUpDatePed.Append("', DS_ENDNOR = '" + ((CliforDAO)(Session["ObjetoClienteDetalhado"])).RegistroAtual["DS_ENDNOR"].ToString());
strUpDatePed.Append("', NR_ENDNOR = '" + ((CliforDAO)(Session["ObjetoClienteDetalhado"])).RegistroAtual["NR_ENDNOR"].ToString());
strUpDatePed.Append("', NM_BAIRRONOR = '" + ((CliforDAO)(Session["ObjetoClienteDetalhado"])).RegistroAtual["NM_BAIRRONOR"].ToString());
strUpDatePed.Append("', NM_CIDNOR = '" + ((CliforDAO)(Session["ObjetoClienteDetalhado"])).RegistroAtual["NM_CIDNOR"].ToString());
strUpDatePed.Append("', CD_UFNOR = '" + ((CliforDAO)(Session["ObjetoClienteDetalhado"])).RegistroAtual["CD_UFNOR"].ToString());
strUpDatePed.Append("', ST_PESSOAJ = '" + ((CliforDAO)(Session["ObjetoClienteDetalhado"])).RegistroAtual["ST_PESSOAJ"].ToString());
if (((CliforDAO)(Session["ObjetoClienteDetalhado"])).RegistroAtual["ST_PESSOAJ"].ToString() == "S")
{
strUpDatePed.Append("', CD_CGCCPF = '" + ((CliforDAO)(Session["ObjetoClienteDetalhado"])).RegistroAtual["CD_CGC"].ToString());
strUpDatePed.Append("', CD_INSEST_RG = '" + ((CliforDAO)(Session["ObjetoClienteDetalhado"])).RegistroAtual["cd_insest"].ToString());
}
else
{
strUpDatePed.Append("', CD_CGCCPF = '" + ((CliforDAO)(Session["ObjetoClienteDetalhado"])).RegistroAtual["CD_CPF"].ToString());
strUpDatePed.Append("', CD_INSEST_RG = '" + ((CliforDAO)(Session["ObjetoClienteDetalhado"])).RegistroAtual["CD_RG"].ToString());
}
strUpDatePed.Append("', CD_CEPNOR = '" + ((CliforDAO)(Session["ObjetoClienteDetalhado"])).RegistroAtual["CD_CEPNOR"].ToString());
strUpDatePed.Append("', CD_FONENOR = '" + ((CliforDAO)(Session["ObjetoClienteDetalhado"])).RegistroAtual["CD_FONENOR"].ToString());
strUpDatePed.Append("', CD_FAXNOR = '" + ((CliforDAO)(Session["ObjetoClienteDetalhado"])).RegistroAtual["CD_FAXNOR"].ToString());
strUpDatePed.Append("', DT_PEDIDO = '" + txtDataPedido.Text.Replace("/", "."));
//strUpDatePed.Append("', DT_PEDIDO = '" + DateTime.Now.ToString().Replace("/", "."));
strUpDatePed.Append("', CD_TIPODOC = '" + sTipoDocumentoPadrao);
strUpDatePed.Append("', CD_PRAZO = '" + cbxCD_PRAZO.SelectedItem.Value.Trim());
strUpDatePed.Append("', CD_TRANS = '" + objUsuario.oTabelas.TRANSP.ToString().Trim());
strUpDatePed.Append("', DS_OBS_WEB = '" + txtObs.Text.ToUpper().Trim());
strUpDatePed.Append("', DT_ABER = '" + txtDataPedido.Text.Replace("/", "."));
strUpDatePed.Append("', CD_USUINC ='" + objUsuario.oTabelas.CdUsuarioAtual);
strUpDatePed.Append("', CD_VEND1 = '" + objUsuario.oTabelas.CdVendedorAtual + "'");
strUpDatePed.Append(" WHERE (CD_PEDIDO = '" + strRetPedido.Trim() + "') AND (CD_EMPRESA = '" + objUsuario.oTabelas.sEmpresa.Trim() + "')");
FbCommand cmdUpDatePedido = new FbCommand(strConn);
cmdUpDatePedido.CommandText = strUpDatePed.ToString();
cmdUpDatePedido.CommandType = CommandType.Text;
cmdUpDatePedido.Connection = Conn;
Conn.Open();
int Ret = cmdUpDatePedido.ExecuteNonQuery();
Conn.Close();
if (Ret > 0)
{
Ret = 0;
int iCountSeq = 1;
foreach (GridViewRow row in GridViewNovo.Rows)
{
FbCommand cmdSelectMoviPend = new FbCommand();
cmdSelectMoviPend.CommandText = "SP_INCLUI_MOVITEM_WEB";
cmdSelectMoviPend.CommandType = CommandType.StoredProcedure;
cmdSelectMoviPend.Connection = Conn;
Conn.Open();
cmdSelectMoviPend.Parameters.Add("@SCD_EMPRESA", FbDbType.VarChar, 3).Value = objUsuario.oTabelas.sEmpresa.ToString();
cmdSelectMoviPend.Parameters.Add("@SCD_PEDIDO", FbDbType.VarChar, 7).Value = strRetPedido.Trim();
string strRetMoviPend = cmdSelectMoviPend.ExecuteScalar().ToString();
Conn.Close();
if (strRetMoviPend != null)
{
strUpDatePed.Length = 0;
string sCD_LISTA = row.Cells[1].Text.Split('-')[1].ToString().Trim();
string sCD_PROD = row.Cells[1].Text.Split('-')[0].ToString().Trim();
TextBox txtQtde = (TextBox)row.Cells[8].FindControl("txtQtde");
string qtde = txtQtde.Text.Trim();
string CF = objUsuario.oTabelas.hlpDbFuncoes.qrySeekValue("PRODUTO", "CD_CF", "CD_PROD = '" + sCD_PROD + "'");
string UNID = objUsuario.oTabelas.hlpDbFuncoes.qrySeekValue("PRODUTO", "CD_TPUNID", "CD_PROD = '" + sCD_PROD + "'");
string CODICMS = objUsuario.oTabelas.hlpDbFuncoes.qrySeekValue("PRODUTO", "CD_ALIICMS", "CD_PROD = '" + sCD_PROD + "'");
string SITTRIB = objUsuario.oTabelas.hlpDbFuncoes.qrySeekValue("PRODUTO", "CD_SITTRIB", "CD_PROD = '" + sCD_PROD + "'");
string scdOper = objUsuario.oTabelas.hlpDbFuncoes.qrySeekValue("tpdoc", "substring(tpdoc.cd_operval from 1 for 3) cd_operval", "cd_tipodoc = '" + sTipoDocumentoPadrao + "'");
// OS. 28381 - Mário
string scd_sittribOPEREVE = objUsuario.oTabelas.hlpDbFuncoes.qrySeekValue("OPEREVE", "cd_sittrib", "CD_OPER = '" + scdOper + "'");
// SITTRIB já está com o valor definido na tabela produto...
if (!String.IsNullOrEmpty(scd_sittribOPEREVE))
{
SITTRIB = scd_sittribOPEREVE;
}
//FIM OS. 28381
string sSittribipi = objUsuario.oTabelas.hlpDbFuncoes.qrySeekValue("OPEREVE", "cd_sittribipi", "CD_OPER = '" + scdOper + "'");
string sSittribpis = objUsuario.oTabelas.hlpDbFuncoes.qrySeekValue("OPEREVE", "cd_sittribpis", "CD_OPER = '" + scdOper + "'");
string sSittribcofins = objUsuario.oTabelas.hlpDbFuncoes.qrySeekValue("OPEREVE", "cd_sittribcof", "CD_OPER = '" + scdOper + "'");
string sTipoPrazo = objUsuario.oTabelas.hlpDbFuncoes.qrySeekValue("PRAZOS", "DS_FORMULA", "CD_PRAZO = '" + cbxCD_PRAZO.SelectedItem.Value.Trim() + "'");
#region Busca Valor % de comissão
decimal dVL_PERCOMI1 = 0;
dVL_PERCOMI1 = Convert.ToDecimal(objUsuario.oTabelas.hlpDbFuncoes.qrySeekValue("LISTAPRE", "COALESCE(LISTAPRE.VL_PERCOMI,0)", "CD_LISTA = '" + sCD_LISTA + "'"));
#endregion
#region Calcula Desconto Item
string RecebeCoefDesc = objUsuario.oTabelas.hlpDbFuncoes.qrySeekValue("PRAZOS", "VL_COEF", "(CD_PRAZO = '" + cbxCD_PRAZO.SelectedItem.Value.Trim() + "')");
decimal dCOEFDESC = Convert.ToDecimal((RecebeCoefDesc.Equals(String.Empty) ? "1.0" : RecebeCoefDesc));
decimal dVL_UNIPROD = 0;
decimal dVL_DESCONTO_VALOR = 0;
decimal VlTotLiq = 0;
dVL_UNIPROD = (Convert.ToDecimal(row.Cells[4].Text));
#endregion
VlTotLiq = Decimal.Round((Convert.ToDecimal(qtde) * dVL_UNIPROD), 2);
string sDS_PROD = objUsuario.oTabelas.hlpDbFuncoes.qrySeekValue("PRODUTO", "DS_PROD", "CD_EMPRESA = '" + objUsuario.oTabelas.sEmpresa.Trim() + "' AND CD_PROD ='" + sCD_PROD + "'");
string sCD_ALTER = objUsuario.oTabelas.hlpDbFuncoes.qrySeekValue("PRODUTO", "CD_ALTER", "CD_EMPRESA = '" + objUsuario.oTabelas.sEmpresa.Trim() + "' AND CD_PROD ='" + sCD_PROD + "'");
strUpDatePed.Append("UPDATE MOVITEM SET NR_LANC = '" + strRetMoviPend.Trim() + "', CD_PEDIDO = '" + strRetPedido.Trim() + "', DS_PROD = '" + sDS_PROD + "', ");
strUpDatePed.Append("cd_clifor = '" + txtCodCli.Text.Trim() + "', ");
strUpDatePed.Append("CD_VEND1 = '" + objUsuario.oTabelas.CdVendedorAtual + "', ");
strUpDatePed.Append("CD_LISTA = '" + sCD_LISTA + "', CD_PROD = '" + sCD_PROD + "', CD_ALTER = '" + sCD_ALTER + "', ");
strUpDatePed.Append("QT_SALDOEN = '" + qtde.Replace(".", "").Replace(",", ".") + "', ");
strUpDatePed.Append("QT_PROD = '" + qtde.Replace(".", "").Replace(",", ".") + "', VL_UNIPROD_SEM_DESC = '" + dVL_UNIPROD.ToString().Replace(".", "").Replace(",", "."));
strUpDatePed.Append("', VL_TOTLIQ = '" + VlTotLiq.ToString().Replace(".", "").Replace(",", ".") + "'"); // adicionado o campo VL_UNIPROD_SEM_DESC
strUpDatePed.Append(", VL_TOTBRUTO = '" + VlTotLiq.ToString().Replace(".", "").Replace(",", ".") + "'");
strUpDatePed.Append(", CD_SEQPED = '" + iCountSeq.ToString().PadLeft(2, '0') + "'");
strUpDatePed.Append(", VL_DESCONTO_VALOR = '" + dVL_DESCONTO_VALOR.ToString().Replace(".", "").Replace(",", ".") + "'");
strUpDatePed.Append(", VL_UNIPROD = '" + dVL_UNIPROD.ToString().Replace(".", "").Replace(",", ".") + "'");
strUpDatePed.Append(", CD_USUINC = '" + objUsuario.oTabelas.CdUsuarioAtual.ToString() + "', dt_doc ='" + txtDataPedido.Text.Replace("/", ".") + "', DT_LANC = '" + txtDataPedido.Text.Replace("/", ".") + "', CD_USUALT = '" + objUsuario.oTabelas.CdUsuarioAtual.ToString() + "', ");
strUpDatePed.Append("CD_CF = '" + (CF.Equals(String.Empty) ? "0000000" : CF.Trim()) + "', CD_TPUNID = '" + UNID.Trim() + "', CD_ALIICMS = '" + CODICMS.Trim() + "', CD_SITTRIB = '" + SITTRIB.Trim() + "', VL_COEF = " + dCOEFDESC.ToString().Replace(",", ".") + ", "); //COEF.ToString().Replace(",", ".") + ", ");
strUpDatePed.Append("VL_PERCOMI1 = '" + dVL_PERCOMI1.ToString().Replace(".", "").Replace(",", ".") + "', DT_PRAZOEN = '" + txtDataPedido.Text.Replace("/", ".") + "', ");
strUpDatePed.Append("VL_ALIIPI = " + HlpFuncoesFaturamento.GetAliquotaIPI(objUsuario.oTabelas.GetOperacaoDefault("CD_OPER"), txtCodCli.Text.Trim(), CF, objUsuario.oTabelas) + ", ");
strUpDatePed.Append("CD_OPER = '" + scdOper);//+ "', VL_PERENTR = " + 100 + " ");
strUpDatePed.Append("' , cd_sittribipi = '" + sSittribipi + "', cd_sittribpis = '" + sSittribpis + "', cd_sittribcof = '" + sSittribcofins + "' ");
strUpDatePed.Append(" WHERE (NR_LANC = '" + strRetMoviPend.Trim() + "') AND (CD_EMPRESA = '" + objUsuario.oTabelas.sEmpresa.Trim() + "')");
FbCommand cmdUpDateMoviPend = new FbCommand();
cmdUpDateMoviPend.CommandText = strUpDatePed.ToString();
cmdUpDateMoviPend.CommandType = CommandType.Text;
cmdUpDateMoviPend.Connection = Conn;
Conn.Open();
Ret = cmdUpDateMoviPend.ExecuteNonQuery();
Conn.Close();
}
}
if (Ret > 0)
{
//EnviarEmailPedido(strRetPedido);
LimparSession();
Response.Redirect("~/Informativo.aspx?PARAMETERCODIGO=" + strRetPedido);
}
else
{
MessageHLP.ShowPopUpMsg("Cadastro não foi concluído!", this.Page);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
private PLFUnitClass LoadLogBook(READCYCLE.readcycle readcycle)
{
PLFUnitClass plf = new PLFUnitClass();
plf.START_PERIOD.systemTime = readcycle.TIMEFROM.ToString("yy:MM:dd HH:mm:ss");
plf.END_PERIOD.systemTime = readcycle.TIMETO.ToString("yy:MM:dd HH:mm:ss");
plf.TIME_STEP = readcycle.TIMESTEP.ToString();
plf.installedSensors = readcycle.installedSensors;
fb_con.Open();
string sql = "SELECT * FROM LOGBOOK WHERE READCYCLEID=@READCYCLEID ORDER BY ID";
FbCommand cmd = new FbCommand(sql, fb_con);
cmd.Parameters.AddWithValue("@READCYCLEID", readcycle.ID);
int i = 0;
PLFRecord onerecord = new PLFRecord();
DateTime previewDate = new DateTime();
string gettedValue;
using (FbDataReader r = cmd.ExecuteReader())
{
while (r.Read())
{
onerecord = new PLFRecord();
if (plf.installedSensors.SYSTEM_TIME.systemTime != null)
{
gettedValue = r["READTIME"].ToString();
if (previewDate == new DateTime())
{
onerecord.SYSTEM_TIME.systemTime = DateTime.Parse(gettedValue).ToString("yy:MM:dd HH:mm:ss");
previewDate = DateTime.Parse(gettedValue);
}
else
{
if (DateTime.Parse(gettedValue) == previewDate)
{
onerecord.SYSTEM_TIME.systemTime = (DateTime.Parse(gettedValue).AddSeconds(Convert.ToInt32(plf.TIME_STEP))).ToString("yy:MM:dd HH:mm:ss");
previewDate = DateTime.Parse(gettedValue);
}
else
{
onerecord.SYSTEM_TIME.systemTime = DateTime.Parse(gettedValue).ToString("yy:MM:dd HH:mm:ss");
previewDate = DateTime.Parse(gettedValue);
}
}
}
if (plf.installedSensors.FUEL_CONSUMPTION != null)
{
onerecord.FUEL_CONSUMPTION = r["FUEL0"].ToString();
}
if (plf.installedSensors.FUEL_VOLUME1 != null)
{
onerecord.FUEL_VOLUME1 = r["FUEL1"].ToString();
}
if (plf.installedSensors.FUEL_VOLUME2 != null)
{
onerecord.FUEL_VOLUME2 = r["FUEL2"].ToString();
}
//FUELPH
if (plf.installedSensors.VOLTAGE != null)
{
onerecord.VOLTAGE = r["BATTERY"].ToString();
}
if (plf.installedSensors.ENGINE_RPM != null)
{
onerecord.ENGINE_RPM = r["ENGINERPM"].ToString();
}
if (plf.installedSensors.TEMPERATURE1 != null)
{
onerecord.TEMPERATURE1 = r["TEMPERATURE1"].ToString();
}
if (plf.installedSensors.TEMPERATURE2 != null)
{
onerecord.TEMPERATURE2 = r["TEMPERATURE2"].ToString();
}
if (plf.installedSensors.WEIGHT1 != null)
{
onerecord.WEIGHT1 = r["WEIGHT1"].ToString();
}
if (plf.installedSensors.WEIGHT2 != null)
{
onerecord.WEIGHT2 = r["WEIGHT2"].ToString();
}
if (plf.installedSensors.WEIGHT3 != null)
{
onerecord.WEIGHT3 = r["WEIGHT3"].ToString();
}
if (plf.installedSensors.WEIGHT4 != null)
{
onerecord.WEIGHT4 = r["WEIGHT4"].ToString();
}
if (plf.installedSensors.WEIGHT5 != null)
{
onerecord.WEIGHT5 = r["WEIGHT5"].ToString();
}
if (plf.installedSensors.ADDITIONAL_SENSORS != null)
{
onerecord.ADDITIONAL_SENSORS = r["SENSOR1"].ToString();
}
if (plf.installedSensors.RESERVED_3 != null)
{
onerecord.RESERVED_3 = r["SENSOR3"].ToString();
}
if (plf.installedSensors.RESERVED_4 != null)
{
onerecord.RESERVED_4 = r["SENSOR4"].ToString();
}
if (plf.installedSensors.RESERVED_5 != null)
{
onerecord.RESERVED_5 = r["SENSOR5"].ToString();
}
//IGNITION
//ENGWORKTIME
//REFUEL
//FUELDELTA
//REFUELTIME
//REFUELTIME
if (plf.installedSensors.SPEED != null)
{
onerecord.SPEED = r["SPEED"].ToString();
}
if (plf.installedSensors.DISTANCE_COUNTER != null)
{
onerecord.DISTANCE_COUNTER = r["RACE"].ToString();
}
//MOVETIME
//IDLETIME
if (plf.installedSensors.LATITUDE != null)
{
onerecord.LATITUDE = r["LATITUDE"].ToString();
}
if (plf.installedSensors.LONGITUDE != null)
{
onerecord.LONGITUDE = r["LONGITUDE"].ToString();
}
if (plf.installedSensors.ALTITUDE != null)
{
onerecord.ALTITUDE = r["ALTITUDE"].ToString();
}
if (plf.installedSensors.FUEL_COUNTER != null)
{
onerecord.FUEL_COUNTER = r["FUEL"].ToString();
}
plf.Records.Add(onerecord);
}
}
fb_con.Close();
return(plf);
}
private static bool IsExistBarCode(string barCode)
{
Boolean pr = false;
string id = null;
//Console.WriteLine($"UpdateRowBd");
//Log.Write($"UpdateRowBd)");
//Console.WriteLine($"UpdateRowBd - {QueryGet(query).Query}");
//Log.Write($"UpdateRowBd - {QueryGet(query).Query}");
Thread.Sleep(100);
FbConnection conn = GetConnection();
FbTransaction fbt = conn.BeginTransaction();
try
{
//Console.WriteLine(query.Query);
//string queryBd = QueryGetGem(query).Query;
string queryBd = "select first 1 R.ID " +
"from JOR_CHECKS_DT D " +
"inner join JOR_CHECKS C on C.ID = D.HD_ID " +
"inner join JOR_RESULTS_DT R on R.HD_ID = D.ID " +
"left join DIC_NO_OPPORT_TO_RES N on N.ID = D.DIC_NO_OPPORT_TO_RES_ID " +
"where (R.HD_ID = D.ID) and(D.DATE_DONE is null) and(D.IS_REFUSE = 0) " +
"and(D.BULB_NUM_CODE = cast('" + barCode + "' as NAME)) " +
//"and(R.CODE_NAME = cast(('PLT') as MIDDLE_NAME)) " +
"and((D.DIC_NO_OPPORT_TO_RES_ID is null) or(N.IS_IN_WORK = 1))";
using (FbCommand cmd = new FbCommand(queryBd, conn))
{
cmd.Transaction = fbt;
FbDataReader reader = cmd.ExecuteReader();
if (reader.HasRows) // если есть данные
{
// выводим названия столбцов
// Console.WriteLine("{0}\t{1}\t{2}", reader.GetName(0), reader.GetName(1), reader.GetName(2));
while (reader.Read()) // построчно считываем данные
{
id = reader.GetString(0);
}
if (!String.IsNullOrWhiteSpace(id))
{
pr = true;
Console.ForegroundColor = ConsoleColor.DarkGreen;
Console.WriteLine($"Exists BarCode - {id}");
Console.ForegroundColor = ConsoleColor.White;
}
else
{
pr = false;
}
}
else
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine($"Not exixsts BarCode");
Console.ForegroundColor = ConsoleColor.White;
pr = false;
}
//fbt.Commit();
}
}
catch (Exception ex)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine(ex.Message);
Log.Write_ex(ex);
Console.ForegroundColor = ConsoleColor.White;
fbt.Rollback();
}
finally
{
fbt.Commit();
conn.Close();
}
return(pr);
}
public MainForm(string login, string password)
{
InitializeComponent();
// Тут можно изменить настройки и залогиниться под другой учетной записью, если надо
var config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
var connectionStringsSection = (ConnectionStringsSection)config.GetSection("connectionStrings");
string appPath = Application.StartupPath;
string dbPath = appPath + "\\db\\MUS_DB.FDB";
FbConnectionStringBuilder cs = new FbConnectionStringBuilder();
cs.DataSource = "localhost";
cs.Database = dbPath;
cs.UserID = login;
cs.Password = password;
cs.Charset = "WIN1251";
cs.Pooling = false;
//cs.Role = "SELLER";
bool onClosing = false;
FbConnection connection = new FbConnection(cs.ToString());
try
{
connection.Open();
connection.Close();
connectionLabel.Text = "Статус: " + dbPath + "; Пользователь: " + login;
this.connectionLabel.Image = global::MusicalInstruments.Properties.Resources.ok;
objectsToolStripMenuItem.Enabled = true;
controlToolStripMenuItem.Enabled = true;
reportsToolStripMenuItem.Enabled = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ошибка соединения с базой данных", MessageBoxButtons.OK, MessageBoxIcon.Error);
connectionLabel.Text = "Статус: соединение не установлено";
this.connectionLabel.Image = global::MusicalInstruments.Properties.Resources.error;
objectsToolStripMenuItem.Enabled = false;
controlToolStripMenuItem.Enabled = false;
reportsToolStripMenuItem.Enabled = false;
onClosing = true;
}
if (onClosing)
{
Close();
}
else
{
var cn = new FbConnection(cs.ToString());
var cmd = new FbCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = @"select r_name from m_roles
join m_users on u_role = r_id
where u_name = '" + login + "'";
cmd.Connection = cn;
DataTable dt = GetTable(cmd);
string role = dt.Rows[0].ItemArray[0].ToString();
user = new User();
user.Login = login;
user.Password = password;
user.Role = role;//roleRow.R_NAME;
checkAccess();
if (!cs.UserID.ToUpper().Equals("SYSDBA"))
{
cs.Role = user.Role;
}
config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
connectionStringsSection = (ConnectionStringsSection)config.GetSection("connectionStrings");
connectionStringsSection.ConnectionStrings["MusicalInstruments.Properties.Settings.ConnectionString"].ConnectionString =
cs.ToString();
config.Save();
ConfigurationManager.RefreshSection("connectionStrings");
}
}
public void ExecutarProcedimento(params object[] args)
{
RecordLog r = new RecordLog();
MySqlCommand log = new MySqlCommand();
string MySQL = $@"server = {args[1]}; user id = {args[2]}; database = {args[0]}; password = {args[7]};";
MySqlConnection conn = new MySqlConnection(MySQL);
conn.Open();
log.Connection = conn;
string FbConn = $@"DataSource = {args[4]}; Database = {args[3]}; username = {args[5]}; password = {args[6]}; CHARSET = NONE;";
FbConnection conn2 = new FbConnection(FbConn);
conn2.Open();
try
{
DataTable dtable = new DataTable();
FbCommand MySelect = new FbCommand(@"SELECT (C.unidade || C.codigo) as codcurso,
max(C.descricao) as dsccurso,
max(C.resumo) as dscabreviada,
max(0) as cargahoraria,
max('S') as ativo,
max(IIF(c.tipo = 'L', 3, IIF(c.descricao like '%FUNDAM%', 1,2))) as codnivel,
1 as formulamediaetapa
FROM sigcurso C
group BY C.unidade, C.codigo;", conn2);
FbDataAdapter adapter = new FbDataAdapter(MySelect);
adapter.Fill(dtable);
StringBuilder queryBuilder = new StringBuilder();
queryBuilder.Append("SET FOREIGN_KEY_CHECKS = 0; " +
"DELETE FROM curso;" +
"INSERT INTO curso (codcurso,dsccurso,dscabreviada,cargahoraria,ativo,codnivel,formulamediaetapa) VALUES ");
for (int i = 0; i < dtable.Rows.Count; i++)
{
queryBuilder.Append($@"('{dtable.Rows[i]["codcurso"]}' , '{dtable.Rows[i]["dsccurso"]}' , '{dtable.Rows[i]["dscabreviada"]}' , '{dtable.Rows[i]["cargahoraria"]}' , '{dtable.Rows[i]["ativo"]}' , '{dtable.Rows[i]["codnivel"]}' , '{dtable.Rows[i]["formulamediaetapa"]}'), ");
}
queryBuilder.Remove(queryBuilder.Length - 2, 2);
MySqlCommand query = new MySqlCommand(queryBuilder.ToString(), conn);
query.ExecuteNonQuery();
MessageBox.Show("Importação concluída com sucesso!");
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
finally
{
log.CommandText = "select count(1) from curso;";
var qtd = Convert.ToInt32(log.ExecuteScalar());
r.Record(args[8].ToString(), qtd);
conn2.Close();
conn.Close();
}
}
public void ExecutarProcedimento(params object[] args)
{
int anoMenor = 0;
int anoMaior = 0;
RecordLog r = new RecordLog();
MySqlCommand log = new MySqlCommand();
string MySQL = $@"server = {args[1]}; user id = {args[2]}; database = {args[0]}; password = {args[7]};";
MySqlConnection conn = new MySqlConnection(MySQL);
conn.Open();
log.Connection = conn;
string FbConn = $@"DataSource = {args[4]}; Database = {args[3]}; username = {args[5]}; password = {args[6]}; CHARSET = NONE;";
FbConnection conn2 = new FbConnection(FbConn);
conn2.Open();
try
{
ResetaValor.Resetar(out anoMenor, out anoMaior, FbConn);
while (anoMenor <= anoMaior)
{
MySqlCommand liquidacaoBoleto = new MySqlCommand($@"SET FOREIGN_KEY_CHECKS = 0;
DELETE FROM liquidacaoboleto;
DELETE FROM pagamento;
DELETE FROM pagamentoforma;
-- LIQUIDACAOBOLETO
INSERT INTO liquidacaoboleto(codreceita ,valorpago ,dtpagamento ,codaluno ,anoletivo ,parcela ,conta ,vencimento ,nossonumero,
valorapagar ,desconto ,valorparcela, dtprocessamento ,codrec , usuario)
(
select c.codreceita, ifnull(c.valorpago,0), c.dtpagamento, c.codaluno, c.anoletivo, c.parcela,
c.codconta, c.vencimento, c.nossonumero,
c.valorparcela, (c.desconto) as desconto, c.valorparcela, c.dtpagamento, c.codrec, 'MIGRACAO'
from contasreceber c
where c.dtpagamento is not null
and c.anoletivo = {anoMenor});
-- pagamento
INSERT INTO pagamento(codpagamento ,dtpagamento ,dtregistro ,registradopor ,codunidaderec ,totalpago ,obs ,codaluno ,cpfresp)
select l.codliquidacao as codpagamento, l.dtpagamento, l.dtprocessamento , l.usuario, c.codunidade, l.valorpago, l.obspagamento, l.codaluno, c.cpf
from liquidacaoboleto l
join contasreceber c ON c.codrec = l.codrec
where l.codpagamento is null
and l.anoletivo = {anoMenor};
-- pagamentoforma
INSERT INTO pagamentoforma(codpagamento ,codconta ,valorpago ,codmeiopagamentoitem ,codforma)
select l.codliquidacao as codpagamento, ifnull(c.codconta,(SELECT max(c.codconta) FROM conta c WHERE c.dscconta = 'CONTA MIGRACAO')) AS 'codconta', l.valorpago, null,
(CASE l.formapagamento
WHEN 'DI' THEN 1
WHEN 'LI' THEN 2
WHEN 'CD' THEN 4
WHEN 'CC' THEN 5
ELSE 1 END) AS codforma
from liquidacaoboleto l
join contasreceber c ON c.codrec = l.codrec
where l.codpagamento is NULL
and l.anoletivo = {anoMenor};", conn);
liquidacaoBoleto.ExecuteNonQuery();
anoMenor++;
}
ResetaValor.Resetar(out anoMenor, out anoMaior, FbConn);
while (anoMenor <= anoMaior)
{
MySqlCommand update = new MySqlCommand($@"
-- seta codpagamento na liquidacaoboleto
update liquidacaoboleto l
set l.codpagamento = l.codliquidacao
where l.codpagamento is null
and l.anoletivo = {anoMenor};", conn);
update.ExecuteNonQuery();
anoMenor++;
}
MessageBox.Show("Importação concluída com sucesso!");
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
finally
{
log.CommandText = "select count(1) from liquidacaoboleto;";
var qtd = Convert.ToInt32(log.ExecuteScalar());
r.Record(args[8].ToString(), qtd);
conn2.Close();
conn.Close();
}
}
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();
}
}
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();
}
private void ButonEkle_Click(object sender, EventArgs e)
{
MusAdi.Text = MusAdi.Text.Replace("'", "’");
MusSoyadi.Text = MusSoyadi.Text.Replace("'", "’");
MusAdresi.Text = MusAdresi.Text.Replace("'", "’");
byte kayit;
MusAdi.Text = fk.IlkHarfleriBuyut(MusAdi.Text);
MusSoyadi.Text = fk.IlkHarfleriBuyut(MusSoyadi.Text);
if (MusAdi.Text != "" && MusSoyadi.Text != "" && MusAdresi.Text != "" && MusTelefonu.Text != "")
{
string adi, soyadi, adresi, kategori, tarih;
int kategori_id = 0;
string telefonu;
adi = MusAdi.Text;
soyadi = MusSoyadi.Text;
adresi = MusAdresi.Text;
telefonu = MusTelefonu.Text;
kategori = Musteri_kategori.SelectedItem.ToString();
tarih = DateTime.Now.Year.ToString(); tarih += '-' + DateTime.Now.Month.ToString(); tarih += '-' + DateTime.Now.Day.ToString();
// kategori id çekme
FbConnection baglan = new FbConnection(fk.Baglanti_Kodu());
FbDataReader oku;
object sonuc;
FbCommand kategori_cek = new FbCommand("SELECT Kategori_id FROM Musteri_kategori where Kategori_adi='" + kategori + "'");
kategori_cek.Connection = baglan;
baglan.Open();
sonuc = kategori_cek.ExecuteScalar();
if (sonuc != null)
{
oku = kategori_cek.ExecuteReader();
while (oku.Read())
{
kategori_id = int.Parse(oku[0].ToString());
}
}
baglan.Close();
// kategor id çekme
baglan.Open();
FbCommand MusteriKontrol = new FbCommand("SELECT * FROM MUSTERILER WHERE Musteri_adi='" + adi + "' and Musteri_soyadi='" + soyadi + "'", baglan);
object kontrol = MusteriKontrol.ExecuteScalar();
if (kontrol == null)
{
kayit = fk.Ekle("Musteriler", "Musteri_adi,Musteri_soyadi,Musteri_adres,Musteri_telefon,Musteri_kayitTarihi,Musteri_kategori,Musteri_bakiye", "'" + adi + "','" + soyadi + "','" + fk.IlkHarfleriBuyut(adresi) + "','" + telefonu + "','" + tarih + "','" + kategori_id.ToString() + "','0'");
if (kayit == 1)
{
MessageBox.Show("Müşteri başarıyla eklenmiştir", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Information);
MusAdi.Text = ""; MusAdresi.Text = ""; MusSoyadi.Text = ""; MusTelefonu.Text = "";
}
else
{
MessageBox.Show("Müşteri eklenemedi ( Hata kodu: M-03 )", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("Eklemek istediğiniz müşteri sistemde kayıtlıdır.", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
baglan.Close();
}
else
{
MessageBox.Show("Boş alanları doldurun! ( Hata kodu: M-04 )", "Hata", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
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();
}
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();
}
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();
}
}
/// <summary>
/// внесение данных в бд
/// </summary>
/// <param name="query"></param>
private static void UpdateRowBd(Model query)
{
//Console.WriteLine($"UpdateRowBd");
//Log.Write($"UpdateRowBd)");
//Console.WriteLine($"UpdateRowBd - {QueryGet(query).Query}");
//Log.Write($"UpdateRowBd - {QueryGet(query).Query}");
Thread.Sleep(100);
FbConnection conn = GetConnection();
FbTransaction fbt = conn.BeginTransaction();
try
{
//Console.WriteLine(query.Query);
//string queryBd = QueryGetGem(query).Query;
string queryBd = query.Query;
using (FbCommand cmd = new FbCommand(queryBd, conn))
{
cmd.Transaction = fbt;
int res = cmd.ExecuteNonQuery();
// Console.ForegroundColor = ConsoleColor.Yellow;
//if (res == 0)
//{
// Beep(1, query);
// Console.ForegroundColor = ConsoleColor.Red;
//}
if (res == 0)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine($"\n{DateTime.Now} Update count: {res}, BarCode: {query.Code}, TypeGoods: {query.Goods}, TypeGoods: {query.TypeGoods}, Value1: {query.Value01}, Value2: {query.Value02}");
Beep(1, query);
}
else
{
Console.ForegroundColor = ConsoleColor.Yellow;
Console.WriteLine($"\n{DateTime.Now} Update count: {res}, BarCode: {query.Code}, TypeGoods: {query.Goods}, TypeGoods: {query.TypeGoods}, Value1: {query.Value01}, Value2: {query.Value02}");
}
Console.ForegroundColor = ConsoleColor.White;
Log.Write($"\n{DateTime.Now} Update count: {res}, BarCode: {query.Code}, TypeGoods: {query.Goods}, TypeGoods: {query.TypeGoods}, Value1: {query.Value01}, Value2: {query.Value02}");
fbt.Commit();
}
}
catch (Exception ex)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine(ex.Message);
Log.Write_ex(ex);
Console.ForegroundColor = ConsoleColor.White;
fbt.Rollback();
}
finally
{
// fbt.Commit();
conn.Close();
}
}
private FbConnection CreateConnection(string sqlCmd, FbConnection fbConn)
{
/*
* CONNECT 'LOCALHOST:D:\Data\test.fdb' USER 'SYSDBA' PASSWORD 'masterkey';
*/
string sql = sqlCmd.ToUpper();
string location = "D:\\Data\\test111.FDB";
string user = "******";
string password = "******";
int inx = sql.IndexOf("CONNECT ", StringComparison.Ordinal);
if (inx >= 0)
{
string cmd3 = sqlCmd.Substring(inx + 8);
int inx2 = cmd3.IndexOf(" ", StringComparison.Ordinal);
string arg = cmd3.Substring(0, inx2);
int inx3 = arg.IndexOf(":\\", StringComparison.Ordinal);
int inx4 = arg.IndexOf(":", StringComparison.Ordinal);
if (inx4 < inx3)
{
//server
location = arg.Substring(inx4 + 1);
location = location.Replace("'", "");
}
else
{
//nur dateipfad
location = arg.Replace("'", "");
}
}
inx = sql.IndexOf("USER ", StringComparison.Ordinal);
if (inx >= 0)
{
var cmd3 = sqlCmd.Substring(inx + 5);
int inx2 = cmd3.IndexOf(" ", StringComparison.Ordinal);
var arg = cmd3.Substring(0, inx2);
user = arg.Replace("'", "");
}
inx = sql.IndexOf("PASSWORD ", StringComparison.Ordinal);
if (inx >= 0)
{
var cmd3 = sqlCmd.Substring(inx + 9);
var arg = cmd3.Substring(0);
password = arg.Replace("'", "");
}
var drc = new DBRegistrationClass
{
DatabasePath = location,
Dialect = 3,
Password = password,
User = user,
Alias = "ConScript",
CharSet = "UTF8",
Server = "localhost",
ConnectionType = eConnectionType.server
};
string connstr = ConnectionStrings.Instance().MakeConnectionString(drc);
if (fbConn?.State == System.Data.ConnectionState.Open)
{
fbConn.Close();
}
fbConn = new FbConnection(connstr);
fbConn.Open();
_parentNotifies?.AddToINFO(StaticFunctionsClass.DateTimeNowStr() + " ...Opening database via script" + drc);
_parentNotifies?.AddToINFO(StaticFunctionsClass.DateTimeNowStr() + " ...Database state:" + fbConn.State.ToString());
return(fbConn);
}
void connectToDoca(string idHsp)
{
FbConnectionStringBuilder cs = new FbConnectionStringBuilder();
cs.DataSource = docaAddr;
cs.Database = "/opt/firebird/database/docaplus.gdb";
cs.UserID = "SYSDBA";
cs.Password = "******";
cs.Charset = "UTF8";
cs.Pooling = false;
FbConnection connection = new FbConnection(cs.ToString());
try //connect to db
{
string query = $@"select
pat_list.fam,
pat_list.nam,
pat_list.ots,
depart.name,
pat_list.d_bir
from dep_hsp
inner join pat_list on(dep_hsp.id_pat = pat_list.id_pat)
inner join depart on(dep_hsp.id_dep = depart.id)
inner join hosp on(dep_hsp.id_pat = hosp.id_pat) and (dep_hsp.id_hsp = hosp.id_hsp)
where
(
(dep_hsp.id_hsp = {idHsp})
)";
connection.Open();
//wrLine(connection.State.ToString());
FbCommand fbCommand = new FbCommand(query, connection);
FbDataAdapter fbDataAdapter = new FbDataAdapter(fbCommand);
DataTable dataTable = new DataTable();
fbDataAdapter.Fill(dataTable);
connection.Close();
//wrLine(connection.State.ToString());
foreach (DataRow dataRow in dataTable.Rows)
{
Console.WriteLine(dataTable.Rows.GetEnumerator());
foreach (DataColumn column in dataTable.Columns)
{
Console.WriteLine(column.ColumnName + " " + dataRow[column]);
}
}
if (dataTable.Rows.Count > 0)
{
string strFam = spaceKiller(dataTable.Rows[dataTable.Rows.Count - 1].ItemArray[0].ToString());
string strNam = spaceKiller(dataTable.Rows[dataTable.Rows.Count - 1].ItemArray[1].ToString());
string strOts = spaceKiller(dataTable.Rows[dataTable.Rows.Count - 1].ItemArray[2].ToString());
this.depart = spaceKiller(dataTable.Rows[dataTable.Rows.Count - 1].ItemArray[3].ToString());
this.dBirth = dateTime.AddSeconds(Convert.ToInt32(spaceKiller(dataTable.Rows[dataTable.Rows.Count - 1].ItemArray[4].ToString())));
this.fio = strFam + strNam + strOts;
}
//else
//{
// wrLine($"udefined pacient");
// goto zapros;
//}
}
catch (Exception e)
{
System.Windows.MessageBox.Show(e.ToString());
connection.Close();
}
string spaceKiller(string s)
{
Regex regex = new Regex(@"\s+");
return(regex.Replace(s, " "));
}
bool availableCheck()
{
try
{
Ping pingSender = new Ping();
string data = "ismyserverpingable";
byte[] buffer = Encoding.ASCII.GetBytes(data);
int timeout = 5;
PingReply reply = pingSender.Send(docaAddr, timeout, buffer);
if (reply.Status == IPStatus.Success)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception e)
{
return(false);
}
}
}
public void FbCierraConectUpdate()
{
_Conect.Close();
}
public void LoadAllReadCycles()
{
fb_con.Open();
string sql = "SELECT * FROM READCYCLE ORDER BY ID";
FbCommand cmd = new FbCommand(sql, fb_con);
readcycle onecycle;
int intTemp;
DateTime datetemp = new DateTime();
using (FbDataReader r = cmd.ExecuteReader())
{
int i;
while (r.Read())
{
if (cycles.Count == 121)
{
i = 0;
}
i = 0;
onecycle = new readcycle();
onecycle.ID = r.GetInt32(i++);
if (int.TryParse(r.GetString(i++), out intTemp))
{
onecycle.DEVICEID = intTemp;
}
if (int.TryParse(r.GetString(i++), out intTemp))
{
onecycle.CARID = intTemp;
}
if (int.TryParse(r.GetString(i++), out intTemp))
{
onecycle.WORKER1ID = intTemp;
}
if (int.TryParse(r.GetString(i++), out intTemp))
{
onecycle.WORKER2ID = intTemp;
}
onecycle.CREATEWHO = r.GetString(i++);
onecycle.CREATEWHEN = r.GetString(i++);
onecycle.CHANGEWHO = r.GetString(i++);
onecycle.CHANGEWHEN = r.GetString(i++);
if (DateTime.TryParse(r.GetString(i++), out datetemp))
{
onecycle.TIMEFROM = datetemp;
}
if (DateTime.TryParse(r.GetString(i++), out datetemp))
{
onecycle.TIMETO = datetemp;
}
if (int.TryParse(r.GetString(i++), out intTemp))
{
onecycle.CMAXCONSUMPTPH = intTemp;
}
if (int.TryParse(r.GetString(i++), out intTemp))
{
onecycle.CSTABILITYTIME = intTemp;
}
//sensors
onecycle.installedSensors = new PLFUnit.PLFRecord();
onecycle.installedSensors.SetNForAllParams();
if (int.TryParse(r.GetString(i++), out intTemp))
{
if (intTemp == 1)
{
onecycle.installedSensors.SYSTEM_TIME = new PLFUnit.PLFSystemTime();
onecycle.installedSensors.SYSTEM_TIME.systemTime = "Y";
}
}
if (int.TryParse(r.GetString(i++), out intTemp))
{
if (intTemp == 1)
{
onecycle.installedSensors.FUEL_VOLUME1 = "Y";
}
}
if (int.TryParse(r.GetString(i++), out intTemp))
{
if (intTemp == 1)
{
onecycle.installedSensors.FUEL_VOLUME2 = "Y";
}
}
if (int.TryParse(r.GetString(i++), out intTemp))
{
if (intTemp == 1)
{
onecycle.installedSensors.FUEL_COUNTER = "Y";
}
}
if (int.TryParse(r.GetString(i++), out intTemp))
{
if (intTemp == 1)
{
onecycle.installedSensors.FUEL_CONSUMPTION = "Y";
}
}
if (int.TryParse(r.GetString(i++), out intTemp))
{
if (intTemp == 1)
{
onecycle.installedSensors.DISTANCE_COUNTER = "Y";
}
}
if (int.TryParse(r.GetString(i++), out intTemp))
{
if (intTemp == 1)
{
onecycle.installedSensors.VOLTAGE = "Y";
}
}
if (int.TryParse(r.GetString(i++), out intTemp))
{
if (intTemp == 1)
{
onecycle.installedSensors.ENGINE_RPM = "Y";
}
}
if (int.TryParse(r.GetString(i++), out intTemp))
{
if (intTemp == 1)
{
onecycle.installedSensors.LATITUDE = "Y";
}
}
if (int.TryParse(r.GetString(i++), out intTemp))
{
if (intTemp == 1)
{
onecycle.installedSensors.LONGITUDE = "Y";
}
}
if (int.TryParse(r.GetString(i++), out intTemp))
{
if (intTemp == 1)
{
onecycle.installedSensors.ALTITUDE = "Y";
}
}
if (int.TryParse(r.GetString(i++), out intTemp))
{
if (intTemp == 1)
{
onecycle.installedSensors.TEMPERATURE1 = "Y";
}
}
if (int.TryParse(r.GetString(i++), out intTemp))
{
if (intTemp == 1)
{
onecycle.installedSensors.TEMPERATURE2 = "Y";
}
}
if (int.TryParse(r.GetString(i++), out intTemp))
{
if (intTemp == 1)
{
onecycle.installedSensors.WEIGHT1 = "Y";
}
}
if (int.TryParse(r.GetString(i++), out intTemp))
{
if (intTemp == 1)
{
onecycle.installedSensors.WEIGHT2 = "Y";
}
}
if (int.TryParse(r.GetString(i++), out intTemp))
{
if (intTemp == 1)
{
onecycle.installedSensors.WEIGHT3 = "Y";
}
}
if (int.TryParse(r.GetString(i++), out intTemp))
{
if (intTemp == 1)
{
onecycle.installedSensors.WEIGHT4 = "Y";
}
}
if (int.TryParse(r.GetString(i++), out intTemp))
{
if (intTemp == 1)
{
onecycle.installedSensors.WEIGHT5 = "Y";
}
}
if (int.TryParse(r.GetString(i++), out intTemp))
{
if (intTemp == 1)
{
onecycle.installedSensors.ADDITIONAL_SENSORS = "Y";
}
}
if (int.TryParse(r.GetString(i++), out intTemp))
{
if (intTemp == 1)
{
onecycle.installedSensors.RESERVED_3 = "Y";
}
}
if (int.TryParse(r.GetString(i++), out intTemp))
{
if (intTemp == 1)
{
onecycle.installedSensors.RESERVED_4 = "Y";
}
}
if (int.TryParse(r.GetString(i++), out intTemp))
{
if (intTemp == 1)
{
onecycle.installedSensors.RESERVED_5 = "Y";
}
}
i++;//ADD_SENS5
if (int.TryParse(r.GetString(i++), out intTemp))
{
onecycle.TIMESTEP = intTemp;
}
if (int.TryParse(r.GetString(i++), out intTemp))
{
if (intTemp == 1)
{
onecycle.installedSensors.SPEED = "Y";
}
}
//
cycles.Add(onecycle);
}
}
fb_con.Close();
}
//========================================================================================================
//
// S U P E R S C R I P T D E C O N S U L T A F I R E B I R D E O R A C L E D A T A B A S E
//
//========================================================================================================
//
// Consulta os valores de vendas por centro na base do Retail e servidores de loja um a um e retorna
// os valores sumarizados comparando-os afim de identificar diferenças entre eles.
//
//========================================================================================================
public void Processamento()
{
DataTable Tbl = new DataTable();
Tbl.Columns.Add("Data e Hora", typeof(string));
Tbl.Columns.Add("Data Venda", typeof(string));
Tbl.Columns.Add("Centro", typeof(string));
Tbl.Columns.Add("Loja", typeof(string));
Tbl.Columns.Add("VendaRetail", typeof(string));
Tbl.Columns.Add("VendaFirebird", typeof(string));
Tbl.Columns.Add("Diferença", typeof(string));
Tbl.Columns.Add("Exceção", typeof(string));
Tbl.Columns.Add("IpServidor", typeof(string));
DataRow Linha;
//Devido aos controles estarem em outra thread, devem ser chamados via BeginInvoke
btnConsultar.BeginInvoke(
new Action(() =>
{
btnConsultar.Enabled = false;
}
));
txtData.BeginInvoke(
new Action(() =>
{
maskedTextBox1.Enabled = false;
maskedTextBox2.Enabled = false;
}
));
dataGridView1.BeginInvoke(
new Action(() =>
{
dataGridView1.Visible = false;
}
));
txtPrimeiroCentro.BeginInvoke(
new Action(() =>
{
txtPrimeiroCentro.Text = lojasArray[0];
}
));
txtUltimoCentro.BeginInvoke(
new Action(() =>
{
txtUltimoCentro.Text = lojasArray[lojasArray.Length - 1];
}
));
System.IO.StreamWriter venda = new System.IO.StreamWriter("VENDA.txt", true); //Log da venda
//System.IO.StreamWriter log = new System.IO.StreamWriter("log.txt", true); //Log do Visualizador de logs
//============================
// INICIO DO LAÇO FOR
//============================
for (int i = 0; i < centrosArray.Length; i++)
{
progresso = ((i + 1) * 100) / centrosArray.Length;
progressoBar = Convert.ToInt32(progresso);
this.backgroundWorker1.ReportProgress(progressoBar);
lblPorcentagem.BeginInvoke(
new Action(() =>
{
lblPorcentagem.Text = progressoBar + "%";
}
));
txtCentroAtual.BeginInvoke(
new Action(() =>
{
txtCentroAtual.Text = lojasArray[i];
}
));
valor = "0.00";
vendaOracle = "0.00";
diferenca = 0;
host_servidor = "";
//PEGANDO OS DADOS DAS LOJAS A SEREM CONSULTADAS
OracleConnection conn1 = new OracleConnection("Data Source=" + localBanco + "/INTEGRACAOSP;User Id=pdvuser;Password=pdv1234;Integrated Security=no");
OracleCommand oCmd1 = new OracleCommand();
string query1 = "SELECT s.loja_sap_pk CENTRO, " +
"u.tund_fantasia LOJA, " +
"s.host_servidor " +
"FROM tloja_sap s " +
"join TUND_UNIDADE u on s.loja_proton_uk = u.tund_unidade_pk " +
"where s.loja_sap_pk = :CENTROORA";
oCmd1.CommandText = query1;
oCmd1.CommandType = CommandType.Text;
oCmd1.Parameters.AddWithValue(":CENTROORA", centrosArray[i]);
oCmd1.Connection = conn1;
try
{
conn1.Open();
OracleDataReader ler1 = oCmd1.ExecuteReader();
while (ler1.Read())
{
centro = ler1.GetValue(0).ToString();
loja = ler1.GetValue(1).ToString();
host_servidor = ler1.GetValue(2).ToString();
}
conn1.Close();
}
catch (Exception ex)
{
MessageBox.Show("Ocorreu um erro ao tentar conectar na base de dados.\nVerifique a conexão com o Oracle e tente novamente.\n\n"
+ ex.Message, "Erro", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
//CONSULTA NO ORACLE
OracleConnection conn = new OracleConnection("Data Source=" + localBanco + "/INTEGRACAOSP;User Id=pdvuser;Password=pdv1234;Integrated Security=no");
OracleCommand oCmd = new OracleCommand();
string query = "SELECT sum(t.valor_liquido) venda " +
"FROM TTRANSACAO t " +
"join tloja_sap s " +
"on t.numero_loja_uk = s.loja_proton_uk " +
"WHERE TRUNC(t.data_hora_transacao_uk) between :DATAORAINI and :DATAORAFIM " +
"and t.tipo in ('P', 'N', 'S', 'M') " +
"and t.cancelada = 'N' " +
"and not exists " +
"(select * " +
"from ttransacao tt " +
"where tt.numero_loja_uk = t.numero_loja_uk " +
"and tt.numero_transacao_uk = t.numero_transacao_uk " +
"and tt.numero_pdv_uk = t.numero_pdv_uk " +
"and tt.numero_serie_impressora_uk = t.numero_serie_impressora_uk " +
"and tt.data_hora_transacao_uk = t.data_hora_transacao_uk " +
"and tt.tipo = t.tipo " +
"and tt.cancelada = 'S') " +
"and s.loja_sap_pk = :CENTROORA";
oCmd.CommandText = query;
oCmd.CommandType = CommandType.Text;
oCmd.Parameters.AddWithValue(":DATAORAINI", maskedTextBox1.Text);
oCmd.Parameters.AddWithValue(":DATAORAFIM", maskedTextBox2.Text);
oCmd.Parameters.AddWithValue(":CENTROORA", centrosArray[i]);
oCmd.Connection = conn;
try
{
conn.Open();
OracleDataReader ler = oCmd.ExecuteReader();
while (ler.Read())
{
vendaOracle = ler.GetValue(0).ToString();
}
//::::::::::::::::::::::::::::::::::::::::::::::::::::
// TRATAMENTO DE EXCEÇÃO DO ORACLE
//::::::::::::::::::::::::::::::::::::::::::::::::::::
//Caso não tenha havido venda no dia consultado
//na loja consultada, o Oracle retorna 'null' e
//dessa forma na conversão para decimal abaixo irá,
//ocorrer uma exceção. Para isso, o if abaixo irá
//converter o valor para 0 caso retorne 'null' na
//consulta do firebird.
//::::::::::::::::::::::::::::::::::::::::::::::::::::
if (vendaOracle == "")
{
vendaOracle = "0";
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Ocorreu um erro ao tentar conectar na base de dados.\nVerifique a conexão com o Oracle e tente novamente.\n\n"
+ ex.Message, "Erro", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
//CONSULTA NO FIREBIRD
try
{
FbConnection fbConn = new FbConnection("DataSource=" + host_servidor + "; Database=" + "C:\\proton\\pdv-server\\dat\\DBSRV.gdb" + "; User=SYSDBA; Password=masterkey; Connection lifetime=15");
FbCommand cmd = new FbCommand();
fbConn.Open();
cmd.Connection = fbConn;
cmd.CommandText = "select sum(a.tped_valor_liquido_pedido) " +
"from tped_pedido_venda a " +
"where a.tped_data_pedido between '" + maskedTextBox1.Text + "' and '" + maskedTextBox2.Text + "' " +
"and a.tped_status_pedido <> 'CA' " +
"and a.tped_tipo_venda in ('N','P','S','M')";
FbDataReader ler = cmd.ExecuteReader();
while (ler.Read())
{
valor = ler.GetValue(0).ToString();
}
//::::::::::::::::::::::::::::::::::::::::::::::::::::
// TRATAMENTO DE EXCEÇÃO DO FIREBIRD
//::::::::::::::::::::::::::::::::::::::::::::::::::::
//Caso não tenha havido venda no dia consultado
//na loja consultada, o firebird retorna 'null' e
//dessa forma na conversão para decimal abaixo irá,
//ocorrer uma exceção. Para isso, o if abaixo irá
//converter o valor para 0 caso retorne 'null' na
//consulta do firebird.
//::::::::::::::::::::::::::::::::::::::::::::::::::::
if (valor == "")
{
valor = "0";
}
fbConn.Close();
diferenca = Convert.ToDecimal(valor) - Convert.ToDecimal(vendaOracle);
}
catch (Exception ex)
{
excecao = ex.Message;
diferenca = 0; //Caso não consiga conexão com o firebird, não pode exibir diferença negativa
}
Linha = Tbl.NewRow();
Linha["Data e Hora"] = DateTime.Now;
Linha["Data Venda"] = maskedTextBox1.Text + " a " + maskedTextBox2.Text;
Linha["Centro"] = centrosArray[i];
Linha["Loja"] = loja;
Linha["VendaRetail"] = vendaOracle;// Math.Truncate(Convert.ToDecimal(valor));
Linha["VendaFirebird"] = Math.Round(Convert.ToDecimal(valor), 2);
Linha["Diferença"] = Math.Round(diferenca, 2);
Linha["Exceção"] = excecao;
Linha["IpServidor"] = host_servidor;
Tbl.Rows.Add(Linha);
totalRetail = totalRetail + Convert.ToDecimal(vendaOracle);
totalFirebird = totalFirebird + Convert.ToDecimal(valor);
diferencaLabel = diferencaLabel + diferenca;
venda.WriteLine(DateTime.Now + " | " + centrosArray[i] + " | " + valor);
System.IO.StreamWriter log = new System.IO.StreamWriter("log.txt", true);
log.WriteLine(DateTime.Now + " | " + centrosArray[i] + " | " + diferenca + " | " + excecao);
log.Dispose();
richTextLog.BeginInvoke(
new Action(() =>
{
richTextLog.Text = System.IO.File.ReadAllText("log.txt");
}
));
excecao = "OK";
txtTotalFirebird.BeginInvoke(
new Action(() =>
{
txtTotalFirebird.Text = "R$" + Math.Round(totalFirebird, 2).ToString();
}
));
txtTotalRetail.BeginInvoke(
new Action(() =>
{
txtTotalRetail.Text = "R$" + Math.Round(totalRetail, 2).ToString();
}
));
txtDiferenca.BeginInvoke(
new Action(() =>
{
txtDiferenca.Text = "R$" + Math.Round(diferencaLabel, 2).ToString();
}
));
lblPercentDiferenca.BeginInvoke(
new Action(() =>
{
lblPercentDiferenca.Text = Convert.ToString((diferenca / totalFirebird) * 100);
}
));
}//Fim For
dataGridView1.BeginInvoke(
new Action(() =>
{
dataGridView1.DataSource = Tbl;
}
));
venda.Dispose();
btnExcel.BeginInvoke(
new Action(() =>
{
btnExcel.Enabled = true;
}
));
btnConsultar.BeginInvoke(
new Action(() =>
{
btnConsultar.Enabled = true;
}
));
txtData.BeginInvoke(
new Action(() =>
{
maskedTextBox1.Enabled = true;
maskedTextBox2.Enabled = true;
}
));
pictureBox2.BeginInvoke(
new Action(() =>
{
pictureBox2.Visible = false;
}
));
dataGridView1.BeginInvoke(
new Action(() =>
{
dataGridView1.Visible = true;
}
));
}//Fim metódo Processamento()
public Prontuario(int Codigo, int CodigoClinica)
{
using (FbConnection Con = new FbConnection(Firebird.StringConexao))
{
try
{
string TxtSQL = @" SELECT
P1.*,
P2.P_CODIGO AS CODIGO_PACIENTE,
P2.*,
E.*
FROM
PRONTUARIO P1
INNER JOIN PACIENTE P2
ON (P1.P_CODIGO = P2.P_CODIGO)
LEFT JOIN EVOLUCAO E
ON (P1.P_CODIGO = E_CODIGO)
WHERE
P1.P_CODIGO =@P_CODIGO AND
P1.P_CLINICA =@P_CLINICA";
Con.Open();
using (FbCommand cmdSelect = new FbCommand(TxtSQL, Con))
{
cmdSelect.Parameters.AddWithValue("P_CODIGO", Codigo);
cmdSelect.Parameters.AddWithValue("P_CLINICA", CodigoClinica);
using (FbDataReader drSelect = cmdSelect.ExecuteReader())
{
if (drSelect.Read())
{
this.Paciente = new Paciente()
{
Codigo = drSelect.GetInt32(drSelect.GetOrdinal("CODIGO_PACIENTE")),
Nome = drSelect.GetString(drSelect.GetOrdinal("P_NOME")),
Sexo = drSelect.GetString(drSelect.GetOrdinal("P_SEXO")),
DataNascimento = drSelect.GetDateTime(drSelect.GetOrdinal("P_DATANASCIMENTO")),
CPF = drSelect.GetString(drSelect.GetOrdinal("P_CPF")),
Endereco = drSelect.GetString(drSelect.GetOrdinal("P_ENDERECO")),
NumeroEndereco = drSelect.GetString(drSelect.GetOrdinal("P_NUMEROENDERECO")),
Bairro = drSelect.GetString(drSelect.GetOrdinal("P_BAIRRO")),
ComplementoEndereco = drSelect.GetString(drSelect.GetOrdinal("P_COMPENDERECO")),
RG = drSelect.GetString(drSelect.GetOrdinal("P_RG")),
TelefoneResidencial = drSelect.GetString(drSelect.GetOrdinal("P_TELRESIDENCIAL")),
TelefoneComercial = drSelect.GetString(drSelect.GetOrdinal("P_TELCOMERCIAL")),
Celular = drSelect.GetString(drSelect.GetOrdinal("P_CELULAR")),
Email = drSelect.GetString(drSelect.GetOrdinal("P_EMAIL")),
CodigoConvenio = !drSelect.IsDBNull(drSelect.GetOrdinal("P_CONVENIO")) ? drSelect.GetInt32(drSelect.GetOrdinal("P_CONVENIO")) : 0,
CodigoCidade = drSelect.GetInt32(drSelect.GetOrdinal("P_CIDADE")),
};
this.CodigoEmpresaProntuario = !drSelect.IsDBNull(drSelect.GetOrdinal("P_CODIGOINTERNO"))
? drSelect.GetInt32(drSelect.GetOrdinal("P_CODIGOINTERNO")) : 0;
this.Anotacoes = drSelect.GetString(drSelect.GetOrdinal("P_ANOTACOES"));
}
}
}
}
finally
{
Con.Close();
}
}
}
public List <Convenio> ObterConvenios(int CodigoClinica, string Descricao = "", string Status = "")
{
List <Convenio> Convenios = new List <Convenio>();
using (FbConnection Con = new FbConnection(Firebird.StringConexao))
{
try
{
Con.Open();
string TxtSQL = @" SELECT
C_CODIGO,
C_DESCRICAO,
C_STATUS
FROM
CONVENIO
WHERE
C_CLINICA =@C_CLINICA ";
if (Status != "")
{
TxtSQL += "C_STATUS =@C_STATUS ";
}
if (Descricao != "")
{
TxtSQL = string.Concat(TxtSQL, "AND UPPER(", Firebird.CHARSET, "C_DESCRICAO) LIKE @C_DESCRICAO ");
}
using (FbCommand cmdSelect = new FbCommand(TxtSQL, Con))
{
cmdSelect.Parameters.AddWithValue("C_CLINICA", CodigoClinica);
if (Status != "")
{
cmdSelect.Parameters.AddWithValue("C_STATUS", Status);
}
if (Descricao != "")
{
cmdSelect.Parameters.Add("C_DESCRICAO", string.Concat("%", Descricao.ToUpper(), "%"));
}
using (FbDataReader drSelect = cmdSelect.ExecuteReader())
{
while (drSelect.Read())
{
Convenios.Add(new Convenio()
{
Codigo = drSelect.GetInt32(drSelect.GetOrdinal("C_CODIGO")),
Descricao = drSelect.GetString(drSelect.GetOrdinal("C_DESCRICAO")),
Status = ObterStatusVisaoUsuario(drSelect.GetString(drSelect.GetOrdinal("C_STATUS")))
});
}
}
}
}
finally
{
Con.Close();
}
}
return(Convenios);
}
private void KHACHHANG_FormClosing(object sender, FormClosingEventArgs e)
{
// Closing 2 connection
loadConnection.Close();
migrateConnection.Close();
}
public ViewExam(int examid)
{
InitializeComponent();
this.examid = examid;
string[] foo;
string bar = "";
if (fb.State == ConnectionState.Closed) //SELECT CURRENT EXAM
{
fb.Open();
}
FbTransaction fbt = fb.BeginTransaction();
FbCommand SelectSQL = new FbCommand("SELECT exams.*, users.name, users.surname, users.patronymic, course.name, block.name FROM exams, users, course, block WHERE exams.id = " + examid + " AND users.id = exams.user_id AND course.id = block.course_id AND block.id = exams.block_id ", fb);
SelectSQL.Transaction = fbt;
FbDataReader reader = SelectSQL.ExecuteReader();
reader.Read();
for (int i = 0; i < 23; i++)
{
examinfo[i] = reader[i].ToString();
}
reader.Close();
SelectSQL.Dispose();
fbt.Commit();
string questionlist = examinfo[3] + "," + examinfo[5] + "," + examinfo[7] + "," + examinfo[9] + "," + examinfo[11];
FbTransaction fbt1 = fb.BeginTransaction(); //SELECT CURRENT QUESTIONS
FbCommand SelectSQL1 = new FbCommand("SELECT * FROM question WHERE id IN (" + questionlist + ")", fb);
SelectSQL1.Transaction = fbt1;
FbDataReader reader1 = SelectSQL1.ExecuteReader();
while (reader1.Read())
{
bar = bar + reader1[0].ToString() + "|" + reader1[1].ToString() + "|" + reader1[2].ToString() + "|" + reader1[3].ToString() + "|" + reader1[4].ToString() + "|" + reader1[5].ToString() + "|" + reader1[6].ToString() + "|" + reader1[7].ToString() + "|" + reader1[8].ToString() + "\n";
}
reader1.Close();
SelectSQL1.Dispose();
fbt1.Commit();
fb.Close();
bar = bar.Substring(0, bar.Length - 1);
foo = bar.Split('\n');
string[][] qList = new string[foo.Length][];
for (int i = 0; i < foo.Length; i++)
{
qList[i] = foo[i].Split('|');
}
this.questionlist = qList;
int min = 0;
int sec = 0;
for (int i = 13; i < 18; i++)
{
string[] time = examinfo[i].Split(':');
if (time.Count() > 1)
{
min = min + int.Parse(time[0]);
sec = sec + int.Parse(time[1]);
}
}
ExamTimeLabel.Text = ExamTimeLabel.Text + min.ToString() + ":" + sec.ToString();
PartComboBox.SelectedIndex = 0;
ResultLabel.Text = ResultLabel.Text + " " + calculate.Percent(examid, false) + "%";
UserNameLabel.Text = examinfo[18] + " " + examinfo[19] + " " + examinfo[20];
}
public void Close()
{
connection.Close();
}
/// <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 void FormLoad()
{
DataRow satir;
label1.Text = DateTime.Now.Day.ToString() + " / " + DateTime.Now.Month.ToString() + " / " + DateTime.Now.Year.ToString() + " - Günü Satışları";
tarih = DateTime.Now.ToString("yyyy-MM-dd");
FbConnection b1 = new FbConnection(fk.Baglanti_Kodu());
FbConnection b2 = new FbConnection(fk.Baglanti_Kodu());
b1.Open();
FbCommand SecilenTarihSatis = new FbCommand("SELECT * FROM Satis WHERE Satis_tarih='" + tarih + "'", b1);
object kontrol = SecilenTarihSatis.ExecuteScalar();
if (kontrol != null)
{
fk.dt.Clear();
dataGridView1.Refresh();
FbDataReader SatisOku = SecilenTarihSatis.ExecuteReader();
while (SatisOku.Read())
{
satir = fk.dt.NewRow();
satir["Satış Id"] = "S " + SatisOku["Satis_id"].ToString();
b2.Open();
FbCommand UrunAdi = new FbCommand("SELECT Urun_adi FROM Urunler WHERE Urun_id='" + SatisOku["Satis_urun"].ToString() + "'", b2);
FbDataReader UrunAdiOku = UrunAdi.ExecuteReader();
UrunAdiOku.Read();
satir["Satış Ürün"] = UrunAdiOku["Urun_adi"].ToString();
b2.Close();
if (SatisOku["Satis_musteri"].ToString() == "")
{
satir["Satış Müşteri"] = "Standart Satış";
}
else
{
b2.Open();
FbCommand MusteriAdi = new FbCommand("SELECT Musteri_adi,Musteri_soyadi FROM Musteriler WHERE Musteri_id='" + SatisOku["Satis_musteri"].ToString() + "'", b2);
FbDataReader MusteriAdiOku = MusteriAdi.ExecuteReader();
MusteriAdiOku.Read();
satir["Satış Müşteri"] = MusteriAdiOku["Musteri_adi"].ToString() + " " + MusteriAdiOku["Musteri_soyadi"].ToString();
b2.Close();
}
satir["Satış Tarihi"] = SatisOku["Satis_tarih"].ToString();
if (SatisOku["Satis_not"].ToString() == "")
{
satir["Satış Notu"] = "Not Eklenmemiş";
}
else
{
satir["Satış Notu"] = SatisOku["Satis_not"].ToString();
}
fk.dt.Rows.Add(satir);
}
}
else
{
MessageBox.Show("Seçilen tarihte satış yapılmamıştır", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
b1.Close();
b1.Open();
FbCommand ManuelSatis = new FbCommand("SELECT * FROM ManuelSatis WHERE satis_tarih='" + tarih + "'", b1);
object sn = ManuelSatis.ExecuteScalar();
if (sn != null)
{
FbDataReader okuu = ManuelSatis.ExecuteReader();
while (okuu.Read())
{
satir = fk.dt.NewRow();
satir["Satış Id"] = "M " + okuu["satis_id"].ToString();
satir["Satış Ürün"] = okuu["satis_urunadi"].ToString();
if (okuu["satis_musteri"].ToString() == "")
{
satir["Satış Müşteri"] = "Standart Satış";
}
else
{
b2.Open();
FbCommand MusteriAdi = new FbCommand("SELECT Musteri_adi,Musteri_soyadi FROM Musteriler WHERE Musteri_id='" + okuu["satis_musteri"].ToString() + "'", b2);
FbDataReader MusteriAdiOku = MusteriAdi.ExecuteReader();
MusteriAdiOku.Read();
satir["Satış Müşteri"] = MusteriAdiOku["Musteri_adi"].ToString() + " " + MusteriAdiOku["Musteri_soyadi"].ToString();
b2.Close();
}
satir["Satış Tarihi"] = okuu["satis_tarih"].ToString();
if (okuu["Satis_not"].ToString() == "")
{
satir["Satış Notu"] = "Not Eklenmemiş";
}
else
{
satir["Satış Notu"] = okuu["satis_not"].ToString();
}
fk.dt.Rows.Add(satir);
}
}
b1.Close();
}
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);
}
public void EndTransaction()
{
try
{
// Comita a transação
if (dbTransaction != null)
{
if (dbTransaction.Connection != null)
{
dbTransaction.Commit();
dbTransaction = null;
}
}
}
catch (Exception e)
{
this.RollbackTransaction();
throw e;
}
try
{
// Fecha a conexão
if (dbCnn != null)
{
if (dbCnn.State != ConnectionState.Closed)
{
dbCnn.Close();
}
}
}
catch (Exception e)
{
throw e;
}
}
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();
}
