public virtual DataTable GetSchema(FbConnection connection, object[] restrictions)
{
restrictions = this.ParseRestrictions(restrictions);
FbCommand command = this.BuildCommand(connection, restrictions);
FbDataAdapter adapter = new FbDataAdapter(command);
DataSet dataSet = new DataSet(this.schemaName);
try
{
adapter.Fill(dataSet, this.schemaName);
}
catch (Exception ex)
{
throw new FbException(ex.Message);
}
finally
{
adapter.Dispose();
command.Dispose();
}
TrimStringFields(dataSet.Tables[this.schemaName]);
return this.ProcessResult(dataSet.Tables[this.schemaName]);
}
protected FbCommand BuildCommand(FbConnection connection, object[] restrictions)
{
DataView collections = FbMetaDataCollections.GetSchema().DefaultView;
collections.RowFilter = "CollectionName = '" + this.schemaName + "'";
if (collections.Count == 0)
{
throw new NotSupportedException("Unsupported collection name.");
}
if (restrictions != null &&
restrictions.Length > (int)collections[0]["NumberOfRestrictions"])
{
throw new InvalidOperationException("The number of specified restrictions is not valid.");
}
DataView restriction = FbRestrictions.GetSchema().DefaultView;
restriction.RowFilter = "CollectionName = '" + this.schemaName + "'";
if (restriction.Count != (int)collections[0]["NumberOfRestrictions"])
{
throw new InvalidOperationException("Incorrect restriction definitions.");
}
StringBuilder builder = this.GetCommandText(restrictions);
FbCommand schema = connection.CreateCommand();
schema.CommandText = builder.ToString();
if (connection.InnerConnection.HasActiveTransaction)
{
schema.Transaction = connection.InnerConnection.ActiveTransaction;
}
if (restrictions != null && restrictions.Length > 0)
{
// Add parameters
int index = 0;
for (int i = 0; i < restrictions.Length; i++)
{
string rname = restriction[i]["RestrictionDefault"].ToString().ToLower(CultureInfo.CurrentCulture);
if (restrictions[i] != null &&
!rname.EndsWith("_catalog") &&
!rname.EndsWith("_schema") &&
rname != "table_type")
{
string pname = String.Format(CultureInfo.CurrentCulture, "@p{0}", index++);
FbParameter p = schema.Parameters.Add(pname, restrictions[i].ToString());
p.FbDbType = FbDbType.VarChar;
p.Size = 255;
}
}
}
return schema;
}
public virtual void SetUp()
{
string cs = this.BuildConnectionString();
CreateDatabase(cs);
CreateTables(cs);
InsertTestData(cs);
CreateProcedures(cs);
CreateTriggers(cs);
this.connection = new FbConnection(cs);
this.connection.Open();
if (this.withTransaction)
{
this.transaction = this.connection.BeginTransaction();
}
}
public static DataTable GetSchema(
FbConnection connection,
string collectionName,
object[] restrictions)
{
FbDbSchema returnSchema = null;
switch (collectionName.ToLower(CultureInfo.CurrentCulture))
{
case "charactersets":
returnSchema = new FbCharacterSets();
break;
case "checkconstraints":
returnSchema = new FbCheckConstraints();
break;
case "checkconstraintsbytable":
returnSchema = new FbChecksByTable();
break;
case "collations":
returnSchema = new FbCollations();
break;
case "columns":
returnSchema = new FbColumns();
break;
case "columnprivileges":
returnSchema = new FbColumnPrivileges();
break;
case "datatypes":
return FbDataTypes.GetSchema();
case "domains":
returnSchema = new FbDomains();
break;
case "foreignkeys":
returnSchema = new FbForeignKeys();
break;
case "functions":
returnSchema = new FbFunctions();
break;
case "generators":
returnSchema = new FbGenerators();
break;
case "indexes":
returnSchema = new FbIndexes();
break;
case "metadatacollections":
return FbMetaDataCollections.GetSchema();
case "primarykeys":
returnSchema = new FbPrimaryKeys();
break;
case "procedures":
returnSchema = new FbProcedures();
break;
case "procedureparameters":
returnSchema = new FbProcedureParameters();
break;
case "procedureprivileges":
returnSchema = new FbProcedurePrivilegesSchema();
break;
case "restrictions":
return FbRestrictions.GetSchema();
case "roles":
returnSchema = new FbRoles();
break;
case "tables":
returnSchema = new FbTables();
break;
case "tableconstraints":
returnSchema = new FbTableConstraints();
break;
case "tableprivileges":
returnSchema = new FbTablePrivileges();
break;
case "triggers":
returnSchema = new FbTriggers();
break;
case "uniquekeys":
returnSchema = new FbUniqueKeys();
break;
case "viewcolumnusage":
returnSchema = new FbViewColumnUsage();
break;
case "views":
returnSchema = new FbViews();
break;
case "viewprivileges":
returnSchema = new FbViewPrivileges();
break;
default:
throw new NotSupportedException("The specified metadata collection is not supported.");
}
return returnSchema.GetSchema(connection, restrictions);
}
private static DataTable PrepareCollection(FbConnection connection, string collectionName, string[] restrictions)
{
FbSchema returnSchema = null;
switch (collectionName.ToLower(CultureInfo.InvariantCulture))
{
case "charactersets":
returnSchema = new FbCharacterSets();
break;
case "checkconstraints":
returnSchema = new FbCheckConstraints();
break;
case "checkconstraintsbytable":
returnSchema = new FbChecksByTable();
break;
case "collations":
returnSchema = new FbCollations();
break;
case "columns":
returnSchema = new FbColumns();
break;
case "columnprivileges":
returnSchema = new FbColumnPrivileges();
break;
case "domains":
returnSchema = new FbDomains();
break;
case "foreignkeycolumns":
returnSchema = new FbForeignKeyColumns();
break;
case "foreignkeys":
returnSchema = new FbForeignKeys();
break;
case "functions":
returnSchema = new FbFunctions();
break;
case "generators":
returnSchema = new FbGenerators();
break;
case "indexcolumns":
returnSchema = new FbIndexColumns();
break;
case "indexes":
returnSchema = new FbIndexes();
break;
case "primarykeys":
returnSchema = new FbPrimaryKeys();
break;
case "procedures":
returnSchema = new FbProcedures();
break;
case "procedureparameters":
returnSchema = new FbProcedureParameters();
break;
case "procedureprivileges":
returnSchema = new FbProcedurePrivilegesSchema();
break;
case "roles":
returnSchema = new FbRoles();
break;
case "tables":
returnSchema = new FbTables();
break;
case "tableconstraints":
returnSchema = new FbTableConstraints();
break;
case "tableprivileges":
returnSchema = new FbTablePrivileges();
break;
case "triggers":
returnSchema = new FbTriggers();
break;
case "uniquekeys":
returnSchema = new FbUniqueKeys();
break;
case "viewcolumns":
returnSchema = new FbViewColumns();
break;
case "views":
returnSchema = new FbViews();
break;
case "viewprivileges":
returnSchema = new FbViewPrivileges();
break;
default:
throw new NotSupportedException("The specified metadata collection is not supported.");
}
return(returnSchema.GetSchema(connection, collectionName, restrictions));
}
public int Delete(int IDUSUARIO)
{
int result = 0;
try
{
//Verificando a existência de um transação aberta
if (dbTransaction != null)
{
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Del_USUARIO", dbCnn);
dbCommand.Transaction = ((FbTransaction)(dbTransaction));
}
else
{
if (dbCnn == null)
{
dbCnn = ((FbConnection)GetConnectionDB());
}
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Del_USUARIO", dbCnn);
dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted);
}
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.Parameters.AddWithValue("@IDUSUARIO", IDUSUARIO); //PrimaryKey
//Executando consulta
dbCommand.ExecuteNonQuery();
result = IDUSUARIO;
if (dbTransaction == null)
{
dbCommand.Transaction.Commit();
dbCnn.Close();
}
}
catch (Exception ex)
{
if (dbTransaction != null)
{
this.RollbackTransaction();
}
else
{
if (dbCommand.Transaction != null)
{
dbCommand.Transaction.Rollback();
}
if (dbCnn.State == ConnectionState.Open)
{
dbCnn.Close();
}
}
throw ex;
}
return(result);
}
public USUARIOCollection ReadCollectionByParameter(List <RowsFiltro> RowsFiltro, string FieldOrder)
{
FbDataReader dataReader = null;
USUARIOCollection collection = null;
string strSqlCommand = String.Empty;
try
{
if (RowsFiltro != null)
{
if (RowsFiltro.Count > 0)
{
strSqlCommand = "SELECT * FROM USUARIO WHERE (";
ArrayList _rowsFiltro = new ArrayList();
RowsFiltro.ForEach(delegate(RowsFiltro i)
{
string[] item = { i.Condicao.ToString(), i.Campo.ToString(), i.Tipo.ToString(), i.Operador.ToString(), i.Valor.ToString() };
_rowsFiltro.Add(item);
});
int _count = 1;
foreach (string[] item in _rowsFiltro)
{
strSqlCommand += "(" + item[1] + " " + item[3];
switch (item[2])
{
case ("System.String"):
if (item[3].ToUpper() != "LIKE")
{
strSqlCommand += " '" + item[4] + "')";
}
else
{
strSqlCommand += " '%" + item[4] + "%')";
}
break;
case ("System.Int16"):
if (item[3].ToUpper() != "LIKE")
{
strSqlCommand += " " + item[4] + ")";
}
else
{
strSqlCommand += " '%" + item[4] + "%')";
}
break;
case ("System.Int32"):
if (item[3].ToUpper() != "LIKE")
{
strSqlCommand += " " + item[4] + ")";
}
else
{
strSqlCommand += " '%" + item[4] + "%')";
}
break;
case ("System.Int64"):
if (item[3].ToUpper() != "LIKE")
{
strSqlCommand += " " + item[4] + ")";
}
else
{
strSqlCommand += " '%" + item[4] + "%')";
}
break;
case ("System.Double"):
if (item[3].ToUpper() != "LIKE")
{
strSqlCommand += " " + item[4] + ")";
}
else
{
strSqlCommand += " '%" + item[4] + "%')";
}
break;
case ("System.Decimal"):
if (item[3].ToUpper() != "LIKE")
{
strSqlCommand += " " + item[4] + ")";
}
else
{
strSqlCommand += " '%" + item[4] + "%')";
}
break;
case ("System.Float"):
if (item[3].ToUpper() != "LIKE")
{
strSqlCommand += " " + item[4] + ")";
}
else
{
strSqlCommand += " '%" + item[4] + "%')";
}
break;
case ("System.Byte"):
strSqlCommand += " " + item[4] + ")";
break;
case ("System.SByte"):
strSqlCommand += " " + item[4] + ")";
break;
case ("System.Char"):
if (item[3].ToUpper() != "LIKE")
{
strSqlCommand += " '" + item[4] + "')";
}
else
{
strSqlCommand += " '%" + item[4] + "%')";
}
break;
case ("System.DateTime"):
if (item[3].ToUpper() != "LIKE")
{
strSqlCommand += " '" + item[4] + "')";
}
else
{
strSqlCommand += " '%" + item[4] + "%')";
}
break;
case ("System.Guid"):
if (item[3].ToUpper() != "LIKE")
{
strSqlCommand += " '" + item[4] + "')";
}
else
{
strSqlCommand += " '%" + item[4] + "%')";
}
break;
case ("System.Boolean"):
strSqlCommand += " " + item[4] + ")";
break;
}
if (_rowsFiltro.Count > 1)
{
if (_count < _rowsFiltro.Count)
{
strSqlCommand += " " + item[0] + " ";
}
_count++;
}
}
strSqlCommand += ") order by " + FieldOrder;
}
else
{
strSqlCommand = "SELECT * FROM USUARIO order by " + FieldOrder;
}
}
else
{
strSqlCommand = "SELECT * FROM USUARIO order by " + FieldOrder;
}
//Verificando a existência de um transação
if (dbTransaction != null)
{
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand(strSqlCommand, dbCnn);
dbCommand.CommandType = CommandType.Text;
dbCommand.Transaction = ((FbTransaction)(dbTransaction));
}
else
{
if (dbCnn == null)
{
dbCnn = new FbConnection(connectionString);
}
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand(strSqlCommand, dbCnn);
dbCommand.CommandType = CommandType.Text;
dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted);
}
collection = ExecuteReader(ref collection, ref dataReader, dbCommand);
if (dataReader != null)
{
dataReader.Close();
dataReader.Dispose();
}
if (dbTransaction == null)
{
dbCommand.Transaction.Commit();
dbCnn.Close();
}
return(collection);
}
catch (Exception ex)
{
// Deleta reader
if (dataReader != null)
{
dataReader.Close();
dataReader.Dispose();
}
if (dbTransaction != null)
{
this.RollbackTransaction();
}
else
{
if (dbCommand.Transaction != null)
{
dbCommand.Transaction.Rollback();
}
if (dbCnn.State == ConnectionState.Open)
{
dbCnn.Close();
}
}
throw ex;
}
}
public void loadlocal(bool isServer)
{
_strConn = isServer ? connConfig_Server() : connConfig_Client();
_conn = new FbConnection(_strConn);
}
public LIS_COMPOSPRODUTOCollection ReadCollection()
{
FbDataReader dataReader = null;
try
{
LIS_COMPOSPRODUTOCollection collection = null;
//Verificando a existência de um transação aberta
if (dbTransaction != null)
{
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("SELECT * FROM LIS_COMPOSPRODUTO", dbCnn);
dbCommand.Transaction = ((FbTransaction)(dbTransaction));
}
else
{
if (dbCnn == null)
{
dbCnn = ((FbConnection)GetConnectionDB());
}
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("SELECT * FROM LIS_COMPOSPRODUTO", dbCnn);
dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted);
}
// Tipo do comando de banco Procedure ou SQL
dbCommand.CommandType = CommandType.Text;
collection = ExecuteReader(ref collection, ref dataReader, dbCommand);
if (dataReader != null)
{
dataReader.Close();
dataReader.Dispose();
}
if (dbTransaction == null)
{
dbCommand.Transaction.Commit();
dbCnn.Close();
}
return(collection);
}
catch (Exception ex)
{
// Deleta reader
if (dataReader != null)
{
dataReader.Close();
dataReader.Dispose();
}
if (dbTransaction != null)
{
this.RollbackTransaction();
}
else
{
if (dbCommand.Transaction != null)
{
dbCommand.Transaction.Rollback();
}
if (dbCnn.State == ConnectionState.Open)
{
dbCnn.Close();
}
}
throw ex;
}
}
static void Main(string[] args)
{
streamForLog.AutoFlush = true;
Console.SetError(streamForLog);
try
{
byte[] fontdata = Properties.Resources.Verdana_Bold;
IntPtr data = Marshal.AllocCoTaskMem(fontdata.Length);
Marshal.Copy(fontdata, 0, data, fontdata.Length);
pfc.AddMemoryFont(data, fontdata.Length);
}
catch (Exception e)
{
wrLine(e.Message);
}
int delayRepeat = Convert.ToInt32(ConfigurationManager.AppSettings.Get("delayRepeat"));
printerForBracelet = ConfigurationManager.AppSettings.Get("printerForBracelet");
printerForLab = ConfigurationManager.AppSettings.Get("printerForLab");
docaAddr = ConfigurationManager.AppSettings.Get("docaAddr");
bool labEnable = false;
bool braceletEnable = false;
if (ConfigurationManager.AppSettings.Get("labEnable").Equals("1"))
{
labEnable = true;
}
if (ConfigurationManager.AppSettings.Get("braceletEnable").Equals("1"))
{
braceletEnable = true;
}
bool isAvailable = false;
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());
int checkCount = 0;
restartApp:
if (!isAvailable)
{
isAvailable = availableCheck();
checkCount++;
if (checkCount > 3)
{
Thread.Sleep(1000);
}
}
if (isAvailable)
{
zapros:
wrLine("barcode:");
barCode = Console.ReadLine();
wrLine(barCode);
if (barCode.Length == 20)
{
checkCount = 0;
anotherTry:
isAvailable = availableCheck();
string idPat = barCode.Substring(2, 9);
idHsp = barCode.Substring(11, 9);
if (isAvailable)
{
try //connect to db
{
string query = $@"select
pat_list.fam,
pat_list.nam,
pat_list.ots,
depart.name,
depart.phone,
hosp.nom,
dep_hsp.date_in,
pat_list.id_pat,
pat_list.d_bir,
pat_list.sex
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)
{
foreach (DataColumn column in dataTable.Columns)
{
Console.WriteLine(column.ColumnName + " " + dataRow[column]);
}
}
if (dataTable.Rows.Count > 0)
{
strFam = spaceKiller(dataTable.Rows[dataTable.Rows.Count - 1].ItemArray[0].ToString());
strNam = spaceKiller(dataTable.Rows[dataTable.Rows.Count - 1].ItemArray[1].ToString());
strOts = spaceKiller(dataTable.Rows[dataTable.Rows.Count - 1].ItemArray[2].ToString());
strDepart = spaceKiller(dataTable.Rows[dataTable.Rows.Count - 1].ItemArray[3].ToString());
strPhone = spaceKiller(dataTable.Rows[dataTable.Rows.Count - 1].ItemArray[4].ToString());
strIb = spaceKiller(dataTable.Rows[dataTable.Rows.Count - 1].ItemArray[5].ToString());
DateTime dateIn = dateTime.AddSeconds(Convert.ToInt32(spaceKiller(dataTable.Rows[dataTable.Rows.Count - 1].ItemArray[6].ToString())));
DateTime dBir = dateTime.AddSeconds(Convert.ToInt32(spaceKiller(dataTable.Rows[dataTable.Rows.Count - 1].ItemArray[8].ToString())) + 3600);
strSex = dataTable.Rows[dataTable.Rows.Count - 1].ItemArray[9].ToString();
strDateIn = dateIn.ToString("dd.MM.yyyy") + " " + dateIn.ToString("HH:mm");
double age = Math.Truncate(DateTime.Now.Subtract(dBir).TotalDays / 365.25);
string ageSuffix = "лет";
if (!(age < 21 && age > 4))
{
if (age % 10 == 1)
{
ageSuffix = "год";
}
else if (age % 10 > 1 && age % 10 < 5)
{
ageSuffix = "года";
}
}
strDbir = $"{dBir.ToString("dd.MM.yyyy")} ({age} {ageSuffix})";
}
else
{
wrLine($"udefined pacient");
goto zapros;
}
}
catch (Exception e)
{
wrLine(e.Message);
connection.Close();
}
wrLine("start parse");
strFio = $"{strFam}{strNam.Substring(0, 1)}.{strOts.Substring(0, 1)}.";
wrLine(strFio);
if (strFio.Length > 3)
{
if (DateTime.Compare(DateTime.Now, dateTimeLastBar.AddSeconds(delayRepeat)) > 0 || lastBarCode != barCode)
{
lastBarCode = barCode;
dateTimeLastBar = DateTime.Now;
//опредление на неопознанного пациента
if (strFio.ToLower().IndexOf("неизвестный") >= 0)
{
strFirst = $"Неизвестный, поступил: {strDateIn} ({strSex})";
strFio = $"Неизвестный, {strDateIn} ({strSex})";
}
else
{
strFirst = $"{strFio} {strDbir}";
}
wrLine($"idPat: {idPat} |idHsp: {idHsp} | {strFirst}, {strIb} {strDepart}, tel: {strPhone}");
wrLine("end parse");
wrLine("start generating & printing");
//generating and printing
int printOrPreview = Convert.ToInt32(ConfigurationManager.AppSettings.Get("printOrPreview"));
PrintDocument printBracelet = new PrintDocument();
printBracelet.DefaultPageSettings.Landscape = true;
printBracelet.DefaultPageSettings.PaperSize = new PaperSize("new size", 95, 700);
printBracelet.PrinterSettings.PrinterName = printerForBracelet;
printBracelet.DefaultPageSettings.PrinterResolution.Kind = PrinterResolutionKind.High;
printBracelet.PrintPage += PrintPageBracelet;
if (braceletEnable)
{
if (printOrPreview == 2)
{
PrintPreviewDialog ppvw = new PrintPreviewDialog();
ppvw.Document = printBracelet;
ppvw.ShowDialog();
}
if (printOrPreview == 1)
{
printBracelet.Print();
}
}
else
{
wrLine("app.config disabled print bracelet");
}
PrintDocument printLab = new PrintDocument();
printLab.PrinterSettings.PrinterName = printerForLab;
printLab.PrintPage += PrintPageLab;
if (labEnable)
{
if (printOrPreview == 2)
{
PrintPreviewDialog ppvw = new PrintPreviewDialog();
ppvw.Document = printLab;
ppvw.ShowDialog();
}
if (printOrPreview == 1)
{
printLab.Print();
}
}
else
{
wrLine("app.config disabled print lab list");
}
wrLine("end generating & printing");
strFirst = null;
strIb = null;
strDepart = null;
idHsp = null;
strFio = null;
strPhone = null;
strDateIn = null;
}
else
{
wrLine($"this barCode:\"{barCode}\" repeated {DateTime.Now.Subtract(dateTimeLastBar).ToString()} before");
}
goto zapros;
}
}
else
{
isAvailable = false;
wrLine("connection check...");
checkCount++;
if (checkCount < 5)
{
Thread.Sleep(500);
goto anotherTry;
}
}
}
else
{
goto zapros;
}
if (barCode != "exit")
{
goto zapros;
}
}
else
{
goto restartApp;
}
Console.ReadLine();
connection.Close();
Console.WriteLine(connection.State);
Console.Read();
}
internal ConnectionFireBird(string connectionString)
{
Conn = new FbConnection(connectionString);
}
public int Save(int?IDCAMPOSRELATPERSONAZ, int IDCAMPO, int TAMANHO, int ORDEM, string SOMATORIO, int IDRELATORIOPERSONALIZADO)
{
int result = 0;
try
{
//Verificando a existência de um transação aberta
if (dbTransaction != null)
{
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Sav_CamposRelatPers", dbCnn);
dbCommand.Transaction = ((FbTransaction)(dbTransaction));
}
else
{
if (dbCnn == null)
{
dbCnn = ((FbConnection)GetConnectionDB());
}
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Sav_CamposRelatPers", dbCnn);
dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted);
}
dbCommand.CommandType = CommandType.StoredProcedure;
if (IDCAMPOSRELATPERSONAZ != null)
{
dbCommand.Parameters.AddWithValue("@IDCAMPOSRELATPERSONAZ", IDCAMPOSRELATPERSONAZ); //PrimaryKey
}
else
{
dbCommand.Parameters.AddWithValue("@IDCAMPOSRELATPERSONAZ", DBNull.Value); //PrimaryKey
}
dbCommand.Parameters.AddWithValue("@IDCAMPO", IDCAMPO); //Coluna
dbCommand.Parameters.AddWithValue("@TAMANHO", TAMANHO); //Coluna
dbCommand.Parameters.AddWithValue("@ORDEM", ORDEM); //Coluna
dbCommand.Parameters.AddWithValue("@SOMATORIO", SOMATORIO); //Coluna
dbCommand.Parameters.AddWithValue("@IDRELATORIOPERSONALIZADO", IDRELATORIOPERSONALIZADO); //Coluna
//Retorno da Procedure
FbParameter returnValue;
returnValue = dbCommand.CreateParameter();
dbCommand.Parameters["@IDCAMPOSRELATPERSONAZ"].Direction = ParameterDirection.ReturnValue;
//Executando consulta
dbCommand.ExecuteNonQuery();
result = (int)(dbCommand.Parameters["@IDCAMPOSRELATPERSONAZ"].Value);
if (dbTransaction == null)
{
dbCommand.Transaction.Commit();
dbCnn.Close();
}
}
catch (Exception ex)
{
if (dbTransaction != null)
{
this.RollbackTransaction();
}
else
{
if (dbCommand.Transaction != null)
{
dbCommand.Transaction.Rollback();
}
if (dbCnn.State == ConnectionState.Open)
{
dbCnn.Close();
}
}
throw ex;
}
return(result);
}
/// <summary>
/// Constructor to query a database using an existing <see cref="FbConnection"/> to initialize the <paramref name="connection"/>
/// </summary>
/// <param name="connection">An instance of <see cref="FbConnection"/> to use to query a database </param>
public FirebirdClient(FbConnection connection) : base(connection)
{
}
public int Save(int?IDGRUPOAGENDATLMK, string NOME, string OBSERVACAO)
{
int result = 0;
try
{
//Verificando a existência de um transação aberta
if (dbTransaction != null)
{
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Sav_GRUPOAGENDATLMK", dbCnn);
dbCommand.Transaction = ((FbTransaction)(dbTransaction));
}
else
{
if (dbCnn == null)
{
dbCnn = ((FbConnection)GetConnectionDB());
}
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Sav_GRUPOAGENDATLMK", dbCnn);
dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted);
}
dbCommand.CommandType = CommandType.StoredProcedure;
//PrimaryKey com valor igual a null, indica um novo registro,
//o valor da chave será fornecido pelo banco. Qualquer outro valor indicará edição do registro.
if (IDGRUPOAGENDATLMK == -1)
{
dbCommand.Parameters.AddWithValue("@IDGRUPOAGENDATLMK", DBNull.Value);
}
else
{
dbCommand.Parameters.AddWithValue("@IDGRUPOAGENDATLMK", IDGRUPOAGENDATLMK);
}
dbCommand.Parameters.AddWithValue("@NOME", NOME); //Coluna
dbCommand.Parameters.AddWithValue("@OBSERVACAO", OBSERVACAO); //Coluna
//Retorno da Procedure
FbParameter returnValue;
returnValue = dbCommand.CreateParameter();
dbCommand.Parameters["@IDGRUPOAGENDATLMK"].Direction = ParameterDirection.InputOutput;
//Executando consulta
dbCommand.ExecuteNonQuery();
result = int.Parse(dbCommand.Parameters["@IDGRUPOAGENDATLMK"].Value.ToString());
if (dbTransaction == null)
{
dbCommand.Transaction.Commit();
dbCnn.Close();
}
}
catch (Exception ex)
{
if (dbTransaction != null)
{
this.RollbackTransaction();
}
else
{
if (dbCommand.Transaction != null)
{
dbCommand.Transaction.Rollback();
}
if (dbCnn.State == ConnectionState.Open)
{
dbCnn.Close();
}
}
throw ex;
}
return(result);
}
private void butApply_Click(object sender, System.EventArgs e)
{
string Type_OP = "";
if (r_AgToWh.Checked)
{
Type_OP = "R";
}
if (r_WhToAg.Checked)
{
Type_OP = "A";
}
var From = ListFrom.SelectedItem;
var To = ListTo.SelectedItem;
var Item = ItemList.SelectedItem;
if ((Type_OP != "") && (From != null) && (To != null) && (Item != null) && (((checkDelivery.Checked) && (num_auto != "")) || (!checkDelivery.Checked)))
{
int Amount = System.Convert.ToInt32(AmountField.Text);
int Prise = System.Convert.ToInt32(PriseField.Text);
System.TimeSpan Del_time;
if (TimeList.SelectedItem == null)
{
Del_time = new System.TimeSpan(0, 0, 0);
}
else
{
Del_time = System.TimeSpan.Parse(TimeList.SelectedItem.ToString());
}
System.DateTime Del_Date = DateTime.Value.Date;
FbParameter GOODS = new FbParameter("GOODS", FbDbType.Char);
FbParameter AMOUNT = new FbParameter("AMOUNT", FbDbType.Integer);
FbParameter AGENT_NAME = new FbParameter("AGENT_NAME", FbDbType.Char);
FbParameter WH_NAME = new FbParameter("WH_NAME", FbDbType.Char);
FbParameter TYPE_OP = new FbParameter("TYPE_OP", FbDbType.Char);
FbParameter PRICE = new FbParameter("PRICE", FbDbType.Integer);
FbParameter DEL_TIME = new FbParameter("DEL_TIME", FbDbType.Time);
FbParameter DEL_DATE = new FbParameter("DEL_DATE", FbDbType.Date);
FbParameter DELIV = new FbParameter("DELIV", FbDbType.Char);
GOODS.Value = Item;
AMOUNT.Value = Amount;
if (r_AgToWh.Checked)
{
AGENT_NAME.Value = From.ToString();
WH_NAME.Value = To.ToString();
}
if (r_WhToAg.Checked)
{
AGENT_NAME.Value = To.ToString();
WH_NAME.Value = From.ToString();
}
PRICE.Value = Prise;
DEL_TIME.Value = Del_time;
DEL_DATE.Value = Del_Date;
if (checkDelivery.Checked)
{
DELIV.Value = 'Y';
}
else
{
DELIV.Value = 'N';
}
TYPE_OP.Value = Type_OP[0];
Connection = new FbConnection(dbContext.Database.Connection.ConnectionString);
Connection.Open();
dbContext.Database.ExecuteSqlCommand("EXECUTE PROCEDURE ADD_ORDER(@GOODS, @AMOUNT, @AGENT_NAME, @WH_NAME, @TYPE_OP, @PRICE, @DEL_TIME, @DEL_DATE, @DELIV)", GOODS, AMOUNT, AGENT_NAME, WH_NAME, TYPE_OP, PRICE, DEL_TIME, DEL_DATE, DELIV);
string tmp = num_auto;
metroTile2_Click(null, null);
ResultLabel.Visible = true;
ResultLabel.ForeColor = Color.Green;
ResultLabel.Text = "Your order has been successfully submitted. ";
if (tmp != "")
{
ResultLabel.Text += ("Vehicle №" + tmp);
}
}
else
{
ResultLabel.Visible = true;
ResultLabel.ForeColor = Color.Red;
ResultLabel.Text = "Your order has not been submitted. Correct fields and try again.";
}
}
private void logar()
{
FbConnection fbConn = new FbConnection(frmHome.strConn);
string query = "SELECT ID, ID_FAZENDA, USUARIO, SENHA, PERMISSAO FROM USUARIO WHERE ((USUARIO = @USUARIO) AND (SENHA = @SENHA))";
FbCommand fbCmd = new FbCommand(query, fbConn);
try
{
//PARAMETROS
FbParameter[] prmParametro = new FbParameter[3];
prmParametro[0] = new FbParameter("@ID_FAZENDA", Convert.ToInt32(cbListaFazendasCadastradas.SelectedValue));
prmParametro[1] = new FbParameter("@USUARIO", txtUsuario.Text);
prmParametro[2] = new FbParameter("@SENHA", txtSenha.Text);
foreach (FbParameter p in prmParametro)
{
fbCmd.Parameters.Add(p);
}
if (temUsuarios(Convert.ToInt32(cbListaFazendasCadastradas.SelectedValue)))
{
fbConn.Open();
FbDataReader r = fbCmd.ExecuteReader();
if (r.Read())
{
frmHome.admin = (r[4].ToString() == "1");
logado = true;
frmHome.logado = true;
frmHome.IDFazendaSelecionada = Convert.ToInt32(cbListaFazendasCadastradas.SelectedValue);
frmHome.NomeFazendaSelecionada = cbListaFazendasCadastradas.Text;
frmHome.usuarioSelecionado = txtUsuario.Text;
Close();
MessageBox.Show("Você está trabalhando na fazenda: " + frmHome.NomeFazendaSelecionada);
}
else
{
MessageBox.Show(@"Usuário e/ou Senha inválida");
}
}
else
{
if (MessageBox.Show("Não há usuários cadastrados.\nDeseja cadastrar agora?", "Informação", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
frmHome.NomeFazendaSelecionada = cbListaFazendasCadastradas.SelectedText;
frmHome.IDFazendaSelecionada = Convert.ToInt32(cbListaFazendasCadastradas.SelectedValue);
frmCadastroUsuarios frm = new frmCadastroUsuarios();
frm.ShowDialog();
}
}
}
catch (FbException fbex)
{
MessageBox.Show("Erro ao acessar o Banco de Dados: " + fbex.Message, "Erro");
}
finally
{
fbConn.Close();
}
txtSenha.Clear();
txtUsuario.Clear();
txtUsuario.Focus();
}
private static void InsertTestData(string connectionString)
{
FbConnection connection = new FbConnection(connectionString);
connection.Open();
StringBuilder commandText = new StringBuilder();
commandText.Append("insert into test (int_field, char_field, varchar_field, bigint_field, smallint_field, float_field, double_field, numeric_field, date_field, time_field, timestamp_field, clob_field, blob_field)");
commandText.Append(" values(@int_field, @char_field, @varchar_field, @bigint_field, @smallint_field, @float_field, @double_field, @numeric_field, @date_field, @time_field, @timestamp_field, @clob_field, @blob_field)");
FbTransaction transaction = connection.BeginTransaction();
FbCommand command = new FbCommand(commandText.ToString(), connection, transaction);
try
{
// Add command parameters
command.Parameters.Add("@int_field" , FbDbType.Integer);
command.Parameters.Add("@char_field" , FbDbType.Char);
command.Parameters.Add("@varchar_field" , FbDbType.VarChar);
command.Parameters.Add("@bigint_field" , FbDbType.BigInt);
command.Parameters.Add("@smallint_field" , FbDbType.SmallInt);
command.Parameters.Add("@float_field" , FbDbType.Double);
command.Parameters.Add("@double_field" , FbDbType.Double);
command.Parameters.Add("@numeric_field" , FbDbType.Numeric);
command.Parameters.Add("@date_field" , FbDbType.Date);
command.Parameters.Add("@time_Field" , FbDbType.Time);
command.Parameters.Add("@timestamp_field" , FbDbType.TimeStamp);
command.Parameters.Add("@clob_field" , FbDbType.Text);
command.Parameters.Add("@blob_field" , FbDbType.Binary);
command.Prepare();
for (int i = 0; i < 100; i++)
{
command.Parameters["@int_field"].Value = i;
command.Parameters["@char_field"].Value = "IRow " + i.ToString();
command.Parameters["@varchar_field"].Value = "IRow Number " + i.ToString();
command.Parameters["@bigint_field"].Value = i;
command.Parameters["@smallint_field"].Value = i;
command.Parameters["@float_field"].Value = (float)(i + 10)/5;
command.Parameters["@double_field"].Value = Math.Log(i, 10);
command.Parameters["@numeric_field"].Value = (decimal)(i + 10)/5;
command.Parameters["@date_field"].Value = DateTime.Now;
command.Parameters["@time_field"].Value = DateTime.Now;
command.Parameters["@timestamp_field"].Value= DateTime.Now;
command.Parameters["@clob_field"].Value = "IRow Number " + i.ToString();
command.Parameters["@blob_field"].Value = Encoding.Default.GetBytes("IRow Number " + i.ToString());
command.ExecuteNonQuery();
}
// Commit transaction
transaction.Commit();
}
catch (FbException)
{
transaction.Rollback();
throw;
}
finally
{
command.Dispose();
connection.Close();
}
}
public int Save(int?IDIMPRGRID, string NOMETELA, string NOMEGRID, string CAMPOSSELECIONADOS, string FLAGAJUSTA, string FLAGMODOPAISAGEM, string FLAGEXIBIRDATA, int IDFUNCIONARIO)
{
int result = 0;
try
{
//Verificando a existência de um transação aberta
if (dbTransaction != null)
{
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Sav_IMPRGRID", dbCnn);
dbCommand.Transaction = ((FbTransaction)(dbTransaction));
}
else
{
if (dbCnn == null)
{
dbCnn = ((FbConnection)GetConnectionDB());
}
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Sav_IMPRGRID", dbCnn);
dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted);
}
dbCommand.CommandType = CommandType.StoredProcedure;
if (IDIMPRGRID != -1)
{
dbCommand.Parameters.AddWithValue("@IDIMPRGRID", IDIMPRGRID); //PrimaryKey
}
else
{
dbCommand.Parameters.AddWithValue("@IDIMPRGRID", DBNull.Value); //PrimaryKey
}
dbCommand.Parameters.AddWithValue("@NOMETELA", NOMETELA); //Coluna
dbCommand.Parameters.AddWithValue("@NOMEGRID", NOMEGRID); //Coluna
dbCommand.Parameters.AddWithValue("@CAMPOSSELECIONADOS", CAMPOSSELECIONADOS); //Coluna
dbCommand.Parameters.AddWithValue("@FLAGAJUSTA", FLAGAJUSTA); //Coluna
dbCommand.Parameters.AddWithValue("@FLAGMODOPAISAGEM", FLAGMODOPAISAGEM); //Coluna
dbCommand.Parameters.AddWithValue("@FLAGEXIBIRDATA", FLAGEXIBIRDATA); //Coluna
dbCommand.Parameters.AddWithValue("@IDFUNCIONARIO", IDFUNCIONARIO); //Coluna
//Retorno da Procedure
FbParameter returnValue;
returnValue = dbCommand.CreateParameter();
dbCommand.Parameters["@IDIMPRGRID"].Direction = ParameterDirection.InputOutput;
//Executando consulta
dbCommand.ExecuteNonQuery();
result = int.Parse(dbCommand.Parameters["@IDIMPRGRID"].Value.ToString());
if (dbTransaction == null)
{
dbCommand.Transaction.Commit();
dbCnn.Close();
}
}
catch (Exception ex)
{
if (dbTransaction != null)
{
this.RollbackTransaction();
}
else
{
if (dbCommand.Transaction != null)
{
dbCommand.Transaction.Rollback();
}
if (dbCnn.State == ConnectionState.Open)
{
dbCnn.Close();
}
}
throw ex;
}
return(result);
}
public FbConnection GetConnectionDB()
{
FbConnection cnx = new FbConnection();
cnx.ConnectionString = connectionString;
return cnx;
}
private void SilEvet_Click_1(object sender, EventArgs e)
{
try
{
byte sonuc;
FbConnection baglan = new FbConnection(fk.Baglanti_Kodu());
FbConnection baglan2 = new FbConnection(fk.Baglanti_Kodu());
baglan.Open();
FbCommand UrunKategori = new FbCommand("SELECT Urun_id FROM Urunler WHERE Urun_kategori='" + id + "'", baglan);
object Usor = UrunKategori.ExecuteScalar();
if (Usor != null)
{
FbDataReader UrunKategoriId = UrunKategori.ExecuteReader();
string[] urun_id = new string[0]; int i = 0;
while (UrunKategoriId.Read())
{
Array.Resize(ref urun_id, urun_id.Length + 1);
urun_id[i] = UrunKategoriId["Urun_id"].ToString();
i++;
}
foreach (string uid in urun_id)
{
baglan2.Open();
FbCommand SatisTab = new FbCommand("SELECT Satis_id FROM Satis WHERE Satis_urun='" + uid + "'", baglan2);
FbDataReader SatisIdOku = SatisTab.ExecuteReader();
while (SatisIdOku.Read())
{
fk.Sil("Satis", "Satis_id='" + SatisIdOku["Satis_id"].ToString() + "'");
}
baglan2.Close();
baglan2.Open();
FbCommand RaporTab = new FbCommand("SELECT rapor_id FROM Rapor WHERE rapor_satisId='" + uid + "'", baglan2);
FbDataReader RaporIdOku = RaporTab.ExecuteReader();
while (RaporIdOku.Read())
{
fk.Sil("Rapor", "rapor_id='" + RaporIdOku["rapor_id"].ToString() + "'");
}
baglan2.Close();
fk.Sil("Urunler", "Urun_id='" + uid + "'");
}
sonuc = fk.Sil("Urun_kategori", "Kategori_id='" + id + "'");
if (sonuc == 1)
{
MessageBox.Show("Ürün kategorisi başarıyla silinmiştir", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
else
{
MessageBox.Show("Ürün kategorisi silinemedi ( Hata kodu: K-07 )", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("Sistemsel bir hata oluştu, lütfen destek sağlayıcınıza başvurunuz", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
baglan.Close();
}
catch (Exception e1)
{
MessageBox.Show(e1.Message, "Hata", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public int Save(int?IDCOTACAOCOMPRA, string NUMREFERENCIA, int IDFORNECEDOR, DateTime DATAEMISSAO, string FLAGCOTACAO, int IDSTATUS, string OBSERVACAO, decimal TOTALCOTACAO)
{
int result = 0;
try
{
//Verificando a existência de um transação aberta
if (dbTransaction != null)
{
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Sav_COTACAOCOMPRA", dbCnn);
dbCommand.Transaction = ((FbTransaction)(dbTransaction));
}
else
{
if (dbCnn == null)
{
dbCnn = ((FbConnection)GetConnectionDB());
}
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Sav_COTACAOCOMPRA", dbCnn);
dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted);
}
dbCommand.CommandType = CommandType.StoredProcedure;
//PrimaryKey com valor igual a null, indica um novo registro,
//o valor da chave será fornecido pelo banco. Qualquer outro valor indicará edição do registro.
if (IDCOTACAOCOMPRA == -1)
{
dbCommand.Parameters.AddWithValue("@IDCOTACAOCOMPRA", DBNull.Value);
}
else
{
dbCommand.Parameters.AddWithValue("@IDCOTACAOCOMPRA", IDCOTACAOCOMPRA);
}
dbCommand.Parameters.AddWithValue("@NUMREFERENCIA", NUMREFERENCIA); //Coluna
if (IDFORNECEDOR != null)
{
dbCommand.Parameters.AddWithValue("@IDFORNECEDOR", IDFORNECEDOR); //ForeignKey
}
else
{
dbCommand.Parameters.AddWithValue("@IDFORNECEDOR", DBNull.Value); //ForeignKey 5
}
dbCommand.Parameters.AddWithValue("@DATAEMISSAO", DATAEMISSAO); //Coluna
dbCommand.Parameters.AddWithValue("@FLAGCOTACAO", FLAGCOTACAO); //Coluna
if (IDSTATUS != null)
{
dbCommand.Parameters.AddWithValue("@IDSTATUS", IDSTATUS); //ForeignKey
}
else
{
dbCommand.Parameters.AddWithValue("@IDSTATUS", DBNull.Value); //ForeignKey 5
}
dbCommand.Parameters.AddWithValue("@OBSERVACAO", OBSERVACAO); //Coluna
dbCommand.Parameters.AddWithValue("@TOTALCOTACAO", TOTALCOTACAO); //Coluna
//Retorno da Procedure
FbParameter returnValue;
returnValue = dbCommand.CreateParameter();
dbCommand.Parameters["@IDCOTACAOCOMPRA"].Direction = ParameterDirection.InputOutput;
//Executando consulta
dbCommand.ExecuteNonQuery();
result = int.Parse(dbCommand.Parameters["@IDCOTACAOCOMPRA"].Value.ToString());
if (dbTransaction == null)
{
dbCommand.Transaction.Commit();
dbCnn.Close();
}
}
catch (Exception ex)
{
if (dbTransaction != null)
{
this.RollbackTransaction();
}
else
{
if (dbCommand.Transaction != null)
{
dbCommand.Transaction.Rollback();
}
if (dbCnn.State == ConnectionState.Open)
{
dbCnn.Close();
}
}
throw ex;
}
return(result);
}
public void CreateDataBase()
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Файл fdb|*.FDB";
if (saveFileDialog.ShowDialog() == true && saveFileDialog.FileName != null)
{
XMLConfig.SaveXMLConfig(dataBase.Value, loggin.Value, password.Value, "*****@*****.**", "testpassword", System.IO.Path.GetDirectoryName(Process.GetCurrentProcess().MainModule.FileName) + "XMLConfig.xml");
dataBase.Value = saveFileDialog.FileName;
connectionInfo.Login = Loggin;
connectionInfo.Password = Password;
connectionInfo.DB = DataBase;
string conStr = new FbConnectionStringBuilder
{
Database = connectionInfo.DB,
Password = connectionInfo.Password,
UserID = connectionInfo.Login,
Dialect = 3,
}.ToString();
int pageSize = 4096;
bool forcedWrites = true;
bool overwrite = false;
if (!File.Exists(connectionInfo.DB))
{
FbConnection.CreateDatabase(conStr, pageSize, forcedWrites, overwrite);
using (FbConnection con = new FbConnection(conStr))
{
try
{
con.Open();
if (con.State == System.Data.ConnectionState.Open)
{
using (FbTransaction dbtran = con.BeginTransaction())
{
using (FbCommand createTables = new FbCommand())
{
createTables.CommandText = "EXECUTE BLOCK AS BEGIN" +
" EXECUTE STATEMENT 'Create table Faculty (id_faculty integer, name_of_faculty varchar(35), primary key(id_faculty))';" +
" EXECUTE STATEMENT 'CREATE GENERATOR faculty_id';" +
" EXECUTE STATEMENT 'set GENERATOR faculty_id to 0';" +
" EXECUTE STATEMENT 'Create trigger incfaculty_id for Faculty active before insert position 0 as begin if (new.id_faculty is null) then new.id_faculty = gen_id(faculty_id, 1); end';" +
" EXECUTE STATEMENT 'Create table Departments(id_department integer, name_of_department varchar(50), id_faculty integer, primary key(id_department), foreign key(id_faculty) references faculty(id_faculty) ON DELETE CASCADE)';" +
" EXECUTE STATEMENT 'CREATE GENERATOR department_id';" +
" EXECUTE STATEMENT 'set GENERATOR department_id to 0';" +
" EXECUTE STATEMENT 'Create trigger incdepartment_id for Departments active before insert position 0 as begin if (new.id_department is null) then new.id_department = gen_id(department_id, 1); end';" +
" EXECUTE STATEMENT 'Create table Groups(id_group integer, name_of_group varchar(50), term integer, id_department integer, primary key(id_group), foreign key(id_department) references Departments(id_department) ON DELETE CASCADE)';" +
" EXECUTE STATEMENT 'CREATE GENERATOR group_id';" +
" EXECUTE STATEMENT 'set GENERATOR group_id to 0';" +
" EXECUTE STATEMENT 'Create trigger incgroup_id for Groups active before insert position 0 as begin if (new.id_group is null) then new.id_group = gen_id(group_id, 1); end';" +
" EXECUTE STATEMENT 'Create table Subjects(id_subject integer, name_of_subject varchar(200), id_department integer, primary key(id_subject), foreign key(id_department) references Departments(id_department) ON DELETE CASCADE)';" +
" EXECUTE STATEMENT 'CREATE GENERATOR subject_id';" +
" EXECUTE STATEMENT 'set GENERATOR subject_id to 0';" +
" EXECUTE STATEMENT 'Create trigger incsubject_id for Subjects active before insert position 0 as begin if (new.id_subject is null) then new.id_subject = gen_id(subject_id, 1); end';" +
" EXECUTE STATEMENT 'Create table Teachers(id_teacher integer, fio varchar(50), mail varchar(50), isreadlecture integer, primary key(id_teacher))';" +
" EXECUTE STATEMENT 'CREATE GENERATOR teacher_id';" +
" EXECUTE STATEMENT 'set GENERATOR teacher_id to 0';" +
" EXECUTE STATEMENT 'Create trigger incteacher_id for Teachers active before insert position 0 as begin if (new.id_teacher is null) then new.id_teacher = gen_id(teacher_id, 1); end';" +
" EXECUTE STATEMENT 'Create table Classrooms(id_classroom integer, number_of_classroom varchar(10), id_department integer, specific varchar(20), primary key(id_classroom), foreign key(id_department) references Departments(id_department) ON DELETE CASCADE)';" +
" EXECUTE STATEMENT 'CREATE GENERATOR classroom_id';" +
" EXECUTE STATEMENT 'set GENERATOR classroom_id to 0';" +
" EXECUTE STATEMENT 'Create trigger incclassroom_id for Classrooms active before insert position 0 as begin if (new.id_classroom is null) then new.id_classroom = gen_id(classroom_id, 1); end';" +
" EXECUTE STATEMENT 'Create table Classes(nameofschedule varchar(10000), id_teacher integer, id_departmentsteacher integer, id_subject integer, id_classroom integer, id_group integer, specifics varchar(10), daytime varchar(20), pair integer, numerator_denominator integer, keyy varchar(35), typekey varchar(100), foreign key(id_teacher) references Teachers(id_teacher) ON DELETE CASCADE, foreign key(id_group) references Groups(id_group) ON DELETE CASCADE, foreign key(id_classroom) references Classrooms(id_classroom) ON DELETE CASCADE, foreign key(id_subject) references Subjects(id_subject) ON DELETE CASCADE, foreign key(id_departmentsteacher)references departments(id_department))';" +
" EXECUTE STATEMENT 'Create table TeachersAndDepartments(id_teacher integer, id_department integer, post varchar(30), primary key(id_teacher, id_department), foreign key(id_teacher) references Teachers(id_teacher) ON DELETE CASCADE, foreign key(id_department) references Departments(id_department) ON DELETE CASCADE)';" +
" EXECUTE STATEMENT 'Create table GroupsAndSubjects(id_group integer, id_subject integer, lecturehour integer, exercisehour integer, labhour integer, primary key(id_group,id_subject), foreign key(id_group) references Groups(id_group) ON DELETE CASCADE, foreign key(id_subject) references Subjects(id_subject) ON DELETE CASCADE)';" +
" EXECUTE STATEMENT 'Create table TeachersAndSubjects(id_teacher integer, id_department integer,id_subject integer, daylist varchar(150), primary key(id_teacher,id_department,id_subject), foreign key(id_teacher) references Teachers(id_teacher) ON DELETE CASCADE, foreign key(id_department) references Departments(id_department) ON DELETE CASCADE, foreign key(id_subject) references Subjects(id_subject) ON DELETE CASCADE )';" +
" END";
createTables.Connection = con;
createTables.Transaction = dbtran;
int result = createTables.ExecuteNonQuery();
Console.WriteLine(result);
}
dbtran.Commit();
}
}
}
catch (Exception e)
{
MessageBox.Show(e.Message, "Ошибка создания базы данных");
}
}
}
}
}
public string RefreshIndicesAndGetTablename()
{
string cmd_index = IndexSQLStatementsClass.Instance().GetIndiciesByName(_dbReg.Version, IndexName.Trim());
DataFilled = false;
txtIndexName.Text = IndexName.Trim();
string TableName = string.Empty;
try
{
lvFields.Items.Clear();
var con = new FbConnection(ConnectionStrings.Instance().MakeConnectionString(_dbReg));
con.Open();
string pkColumn = string.Empty;
string indexName = string.Empty;
string indexColumnName = string.Empty;
string oldIndexColumnName = string.Empty;
int Unique = 0;
int IndexType = -1;
bool Active = false;
FbCommand fcmd = new FbCommand(cmd_index, con);
var dread = fcmd.ExecuteReader();
lvFields.Items.Clear();
if (dread.HasRows)
{
while (dread.Read())
{
TableName = dread.GetValue(0).ToString().Trim();
indexColumnName = dread.GetValue(2).ToString().Trim();
Unique = StaticFunctionsClass.ToIntDef(dread.GetValue(3).ToString().Trim(), 0);
Active = StaticFunctionsClass.ToIntDef(dread.GetValue(4).ToString().Trim(), 0) != 1;
IndexType = StaticFunctionsClass.ToIntDef(dread.GetValue(5).ToString().Trim(), 0);
cbUnique.Checked = Unique > 0;
ckActive.Checked = Active;
if (IndexType < 0)
{
rbSortNothing.Checked = true;
}
else if (IndexType == 0)
{
rbSortAscending.Checked = true;
}
else if (IndexType == 1)
{
rbSortDescending.Checked = true;
}
string[] lv = new string[1];
lv[0] = indexColumnName;
if (this.oldIndexColumnName != indexColumnName)
{
ListViewItem lvi = new ListViewItem(lv);
lvFields.Items.Add(lvi);
this.oldIndexColumnName = indexColumnName;
}
}
DataFilled = true;
}
con.Close();
}
catch (Exception ex)
{
_localNotify?.AddToERROR($@"{StaticFunctionsClass.DateTimeNowStr()}->IndexForm->RefreshIndicesAndGetTablename()->{ex.Message}");
}
return(TableName);
}
/// <summary>
/// Default constuctor.
/// The connection is created and opened in this constuctor using the connection string returned by GetConnectionString().
/// </summary>
public DatabaseManager()
{
connection = new FbConnection(GetConnectionString());
connection.Open();
}
public int Save(int?IDUSUARIO, int IDFUNCIONARIO, int IDNIVELUSUARIO, string FLAGATIVO, string NOMEUSUARIO, string SENHAUSUARIO, string OBSERVACAO)
{
int result = 0;
try
{
//Verificando a existência de um transação aberta
if (dbTransaction != null)
{
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Sav_USUARIO", dbCnn);
dbCommand.Transaction = ((FbTransaction)(dbTransaction));
}
else
{
if (dbCnn == null)
{
dbCnn = ((FbConnection)GetConnectionDB());
}
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Sav_USUARIO", dbCnn);
dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted);
}
dbCommand.CommandType = CommandType.StoredProcedure;
if (IDUSUARIO != null)
{
dbCommand.Parameters.AddWithValue("@IDUSUARIO", IDUSUARIO); //PrimaryKey
}
else
{
dbCommand.Parameters.AddWithValue("@IDUSUARIO", DBNull.Value); //PrimaryKey
}
dbCommand.Parameters.AddWithValue("@IDFUNCIONARIO", IDFUNCIONARIO); //Coluna
dbCommand.Parameters.AddWithValue("@IDNIVELUSUARIO", IDNIVELUSUARIO); //Coluna
dbCommand.Parameters.AddWithValue("@FLAGATIVO", FLAGATIVO); //Coluna
dbCommand.Parameters.AddWithValue("@NOMEUSUARIO", NOMEUSUARIO); //Coluna
dbCommand.Parameters.AddWithValue("@SENHAUSUARIO", SENHAUSUARIO); //Coluna
dbCommand.Parameters.AddWithValue("@OBSERVACAO", OBSERVACAO); //Coluna
//Retorno da Procedure
FbParameter returnValue;
returnValue = dbCommand.CreateParameter();
dbCommand.Parameters["@IDUSUARIO"].Direction = ParameterDirection.ReturnValue;
//Executando consulta
dbCommand.ExecuteNonQuery();
result = (int)(dbCommand.Parameters["@IDUSUARIO"].Value);
if (dbTransaction == null)
{
dbCommand.Transaction.Commit();
dbCnn.Close();
}
}
catch (Exception ex)
{
if (dbTransaction != null)
{
this.RollbackTransaction();
}
else
{
if (dbCommand.Transaction != null)
{
dbCommand.Transaction.Rollback();
}
if (dbCnn.State == ConnectionState.Open)
{
dbCnn.Close();
}
}
throw ex;
}
return(result);
}
/// <summary>
/// This constuctor is used for testing.
/// </summary>
/// <param name="connectionString">The FireBird connection string to the database.</param>
public DatabaseManager(String connectionString)
{
connection = new FbConnection(connectionString);
connection.Open();
}
public USUARIOEntity Read(int IDUSUARIO)
{
FbDataReader reader = null;
try
{
//Verificando a existência de um transação aberta
if (dbTransaction != null)
{
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Rea_USUARIO", dbCnn);
dbCommand.Transaction = ((FbTransaction)(dbTransaction));
}
else
{
if (dbCnn == null)
{
dbCnn = ((FbConnection)GetConnectionDB());
}
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Rea_USUARIO", dbCnn);
dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted);
}
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.Parameters.AddWithValue("@IDUSUARIO", IDUSUARIO); //PrimaryKey
reader = dbCommand.ExecuteReader();
USUARIOEntity entity = null;
if (reader.HasRows)
{
while (reader.Read())
{
entity = FillEntityObject(ref reader);
}
}
// Deleta reader
if (reader != null)
{
reader.Close();
reader.Dispose();
}
// Fecha conexão
if (dbTransaction == null)
{
dbCommand.Transaction.Commit();
if (dbCnn.State == ConnectionState.Open)
{
dbCnn.Close();
}
}
return(entity);
}
catch (Exception ex)
{
// Deleta reader
if (reader != null)
{
reader.Close();
reader.Dispose();
}
if (dbTransaction != null)
{
this.RollbackTransaction();
}
else
{
if (dbCommand.Transaction != null)
{
dbCommand.Transaction.Rollback();
}
if (dbCnn.State == ConnectionState.Open)
{
dbCnn.Close();
}
}
throw ex;
}
}
public int Save(int?IDAGENDA, int IDSTATUS, DateTime DATA, string HORA, int IDGRUPOAGENDA, int IDEVENTO, string COMENTARIO, int IDFUNCIONARIO, int IDCLIENTE)
{
int result = 0;
try
{
//Verificando a existência de um transação aberta
if (dbTransaction != null)
{
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Sav_AGENDA", dbCnn);
dbCommand.Transaction = ((FbTransaction)(dbTransaction));
}
else
{
if (dbCnn == null)
{
dbCnn = ((FbConnection)GetConnectionDB());
}
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Sav_AGENDA", dbCnn);
dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted);
}
dbCommand.CommandType = CommandType.StoredProcedure;
//PrimaryKey com valor igual a null, indica um novo registro,
//o valor da chave será fornecido pelo banco. Qualquer outro valor indicará edição do registro.
if (IDAGENDA == -1)
{
dbCommand.Parameters.AddWithValue("@IDAGENDA", DBNull.Value);
}
else
{
dbCommand.Parameters.AddWithValue("@IDAGENDA", IDAGENDA);
}
dbCommand.Parameters.AddWithValue("@IDSTATUS", IDSTATUS); //Coluna
dbCommand.Parameters.AddWithValue("@DATA", DATA); //Coluna
dbCommand.Parameters.AddWithValue("@HORA", HORA); //Coluna
dbCommand.Parameters.AddWithValue("@IDGRUPOAGENDA", IDGRUPOAGENDA); //Coluna
dbCommand.Parameters.AddWithValue("@IDEVENTO", IDEVENTO); //Coluna
dbCommand.Parameters.AddWithValue("@COMENTARIO", COMENTARIO); //Coluna
if (IDFUNCIONARIO != null)
{
dbCommand.Parameters.AddWithValue("@IDFUNCIONARIO", IDFUNCIONARIO); //ForeignKey
}
else
{
dbCommand.Parameters.AddWithValue("@IDFUNCIONARIO", DBNull.Value); //ForeignKey 5
}
dbCommand.Parameters.AddWithValue("@IDCLIENTE", IDCLIENTE); //Coluna
//Retorno da Procedure
FbParameter returnValue;
returnValue = dbCommand.CreateParameter();
dbCommand.Parameters["@IDAGENDA"].Direction = ParameterDirection.InputOutput;
//Executando consulta
dbCommand.ExecuteNonQuery();
result = int.Parse(dbCommand.Parameters["@IDAGENDA"].Value.ToString());
if (dbTransaction == null)
{
dbCommand.Transaction.Commit();
dbCnn.Close();
}
}
catch (Exception ex)
{
if (dbTransaction != null)
{
this.RollbackTransaction();
}
else
{
if (dbCommand.Transaction != null)
{
dbCommand.Transaction.Rollback();
}
if (dbCnn.State == ConnectionState.Open)
{
dbCnn.Close();
}
}
throw ex;
}
return(result);
}
public int Save(DIAGMEDIOPEDIDOEntity Entity)
{
int result = 0;
try
{
//Verificando a existência de um transação aberta
if (dbTransaction != null)
{
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Sav_DIAGMEDIOPEDIDO", dbCnn);
dbCommand.Transaction = ((FbTransaction)(dbTransaction));
}
else
{
if (dbCnn == null)
{
dbCnn = ((FbConnection)GetConnectionDB());
}
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Sav_DIAGMEDIOPEDIDO", dbCnn);
dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted);
}
dbCommand.CommandType = CommandType.StoredProcedure;
if (Entity.IDDIAGMEDIOPEDIDO != -1)
{
dbCommand.Parameters.AddWithValue("@IDDIAGMEDIOPEDIDO", Entity.IDDIAGMEDIOPEDIDO); //PrimaryKey
}
else
{
dbCommand.Parameters.AddWithValue("@IDDIAGMEDIOPEDIDO", DBNull.Value); //PrimaryKey
}
dbCommand.Parameters.AddWithValue("@IDPEDIDO", Entity.IDPEDIDO); //Coluna
dbCommand.Parameters.AddWithValue("@DIRESFERICO", Entity.DIRESFERICO); //Coluna
dbCommand.Parameters.AddWithValue("@DIRCILINDRICO", Entity.DIRCILINDRICO); //Coluna
dbCommand.Parameters.AddWithValue("@DIREIXO", Entity.DIREIXO); //Coluna
dbCommand.Parameters.AddWithValue("@DIRADICAO", Entity.DIRADICAO); //Coluna
dbCommand.Parameters.AddWithValue("@DIRDNP", Entity.DIRDNP); //Coluna
dbCommand.Parameters.AddWithValue("@DIRACO", Entity.DIRACO); //Coluna
dbCommand.Parameters.AddWithValue("@ESQESFERICO", Entity.ESQESFERICO); //Coluna
dbCommand.Parameters.AddWithValue("@ESQCILINDRICO", Entity.ESQCILINDRICO); //Coluna
dbCommand.Parameters.AddWithValue("@ESQEIXO", Entity.ESQEIXO); //Coluna
dbCommand.Parameters.AddWithValue("@ESQADICAO", Entity.ESQADICAO); //Coluna
dbCommand.Parameters.AddWithValue("@ESQDNP", Entity.ESQDNP); //Coluna
dbCommand.Parameters.AddWithValue("@ESQACO", Entity.ESQACO); //Coluna
dbCommand.Parameters.AddWithValue("@LENTES", Entity.LENTES); //Coluna
dbCommand.Parameters.AddWithValue("@ARMACAO", Entity.ARMACAO); //Coluna
dbCommand.Parameters.AddWithValue("@DISTANCIAPUPILAR", Entity.DISTANCIAPUPILAR); //Coluna
dbCommand.Parameters.AddWithValue("@DIREITO", Entity.DIREITO); //Coluna
dbCommand.Parameters.AddWithValue("@ESQUERDO", Entity.ESQUERDO); //Coluna
dbCommand.Parameters.AddWithValue("@DPA", Entity.DPA); //Coluna
dbCommand.Parameters.AddWithValue("@MD", Entity.MD); //Coluna
dbCommand.Parameters.AddWithValue("@MV", Entity.MV); //Coluna
//Retorno da Procedure
FbParameter returnValue;
returnValue = dbCommand.CreateParameter();
dbCommand.Parameters["@IDDIAGMEDIOPEDIDO"].Direction = ParameterDirection.InputOutput;
//Executando consulta
dbCommand.ExecuteNonQuery();
result = int.Parse(dbCommand.Parameters["@IDDIAGMEDIOPEDIDO"].Value.ToString());
if (dbTransaction == null)
{
dbCommand.Transaction.Commit();
dbCnn.Close();
}
}
catch (Exception ex)
{
if (dbTransaction != null)
{
this.RollbackTransaction();
}
else
{
if (dbCommand.Transaction != null)
{
dbCommand.Transaction.Rollback();
}
if (dbCnn.State == ConnectionState.Open)
{
dbCnn.Close();
}
}
throw ex;
}
return(result);
}
private static void CreateProcedures(string connectionString)
{
FbConnection connection = new FbConnection(connectionString);
connection.Open();
StringBuilder commandText = new StringBuilder();
// SELECT_DATA
commandText = new StringBuilder();
commandText.Append("CREATE PROCEDURE SELECT_DATA \r\n");
commandText.Append("RETURNS ( \r\n");
commandText.Append("INT_FIELD INTEGER, \r\n");
commandText.Append("VARCHAR_FIELD VARCHAR(100), \r\n");
commandText.Append("DECIMAL_FIELD DECIMAL(15,2)) \r\n");
commandText.Append("AS \r\n");
commandText.Append("begin \r\n");
commandText.Append("FOR SELECT INT_FIELD, VARCHAR_FIELD, DECIMAL_FIELD FROM TEST INTO :INT_FIELD, :VARCHAR_FIELD, :DECIMAL_FIELD \r\n");
commandText.Append("DO \r\n");
commandText.Append("SUSPEND; \r\n");
commandText.Append("end;");
FbCommand command = new FbCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
// GETRECORDCOUNT
commandText = new StringBuilder();
commandText.Append("CREATE PROCEDURE GETRECORDCOUNT \r\n");
commandText.Append("RETURNS ( \r\n");
commandText.Append("RECCOUNT SMALLINT) \r\n");
commandText.Append("AS \r\n");
commandText.Append("begin \r\n");
commandText.Append("for select count(*) from test into :reccount \r\n");
commandText.Append("do \r\n");
commandText.Append("suspend; \r\n");
commandText.Append("end\r\n");
command = new FbCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
// GETVARCHARFIELD
commandText = new StringBuilder();
commandText.Append("CREATE PROCEDURE GETVARCHARFIELD (\r\n");
commandText.Append("ID INTEGER)\r\n");
commandText.Append("RETURNS (\r\n");
commandText.Append("VARCHAR_FIELD VARCHAR(100))\r\n");
commandText.Append("AS\r\n");
commandText.Append("begin\r\n");
commandText.Append("for select varchar_field from test where int_field = :id into :varchar_field\r\n");
commandText.Append("do\r\n");
commandText.Append("suspend;\r\n");
commandText.Append("end\r\n");
command = new FbCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
// GETASCIIBLOB
commandText = new StringBuilder();
commandText.Append("CREATE PROCEDURE GETASCIIBLOB (\r\n");
commandText.Append("ID INTEGER)\r\n");
commandText.Append("RETURNS (\r\n");
commandText.Append("ASCII_BLOB BLOB SUB_TYPE 1)\r\n");
commandText.Append("AS\r\n");
commandText.Append("begin\r\n");
commandText.Append("for select clob_field from test where int_field = :id into :ascii_blob\r\n");
commandText.Append("do\r\n");
commandText.Append("suspend;\r\n");
commandText.Append("end\r\n");
command = new FbCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
// DATAREADERTEST
commandText = new StringBuilder();
commandText.Append("CREATE PROCEDURE DATAREADERTEST\r\n");
commandText.Append("RETURNS (\r\n");
commandText.Append("content VARCHAR(128))\r\n");
commandText.Append("AS\r\n");
commandText.Append("begin\r\n");
commandText.Append("content = 'test';\r\n");
commandText.Append("end\r\n");
command = new FbCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
connection.Close();
}
public int Save(int?ID_ECF, string ECF_MOD, string ECF_FAB, string ECF_CX)
{
int result = 0;
try
{
//Verificando a existência de um transação aberta
if (dbTransaction != null)
{
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Sav_ECF", dbCnn);
dbCommand.Transaction = ((FbTransaction)(dbTransaction));
}
else
{
if (dbCnn == null)
{
dbCnn = ((FbConnection)GetConnectionDB());
}
if (dbCnn.State == ConnectionState.Closed)
{
dbCnn.Open();
}
dbCommand = new FbCommand("Sav_ECF", dbCnn);
dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted);
}
dbCommand.CommandType = CommandType.StoredProcedure;
if (ID_ECF != null)
{
dbCommand.Parameters.AddWithValue("@ID_ECF", ID_ECF); //Coluna
}
else
{
dbCommand.Parameters.AddWithValue("@ID_ECF", DBNull.Value); //Coluna 3
}
dbCommand.Parameters.AddWithValue("@ECF_MOD", ECF_MOD); //Coluna
dbCommand.Parameters.AddWithValue("@ECF_FAB", ECF_FAB); //Coluna
dbCommand.Parameters.AddWithValue("@ECF_CX", ECF_CX); //Coluna
//Retorno da Procedure
FbParameter returnValue;
returnValue = dbCommand.CreateParameter();
dbCommand.Parameters[""].Direction = ParameterDirection.InputOutput;
//Executando consulta
dbCommand.ExecuteNonQuery();
result = int.Parse(dbCommand.Parameters[""].Value.ToString());
if (dbTransaction == null)
{
dbCommand.Transaction.Commit();
dbCnn.Close();
}
}
catch (Exception ex)
{
if (dbTransaction != null)
{
this.RollbackTransaction();
}
else
{
if (dbCommand.Transaction != null)
{
dbCommand.Transaction.Rollback();
}
if (dbCnn.State == ConnectionState.Open)
{
dbCnn.Close();
}
}
throw ex;
}
return(result);
}
private static DataTable SqlCommandSchema(FbConnection connection, string collectionName, string[] restrictions)
{
throw new NotImplementedException();
}
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();
}
private void SatisHaftalik_Load(object sender, EventArgs e)
{
string tarih = DateTime.Now.Year.ToString() + "-" + DateTime.Now.Month.ToString() + "-" + DateTime.Now.Day.ToString();
DateTime tarih22 = DateTime.Now.AddDays(-7);
string tarih2 = tarih22.Year.ToString() + "-" + tarih22.Month.ToString() + "-" + tarih22.Day.ToString();
label1.Text = tarih22.Day.ToString() + " / " + tarih22.Month.ToString() + " / " + tarih22.Year.ToString() + " - " + DateTime.Now.Day.ToString() + " / " + DateTime.Now.Month.ToString() + " / " + DateTime.Now.Year.ToString() + " Raporları";
float top = 0; int i = 0; float top2 = 0;
FbConnection baglan = new FbConnection(fk.Baglanti_Kodu());
FbConnection baglan2 = new FbConnection(fk.Baglanti_Kodu());
FbConnection baglan3 = new FbConnection(fk.Baglanti_Kodu());
baglan.Open();
//FbCommand BugunkuSatis = new FbCommand("SELECT Satis_urun FROM Satis WHERE Satis_tarih='" + tarih + "'", baglan);
FbCommand BugunkuSatis = new FbCommand("SELECT Satis_urun FROM Satis WHERE Satis_tarih between '" + tarih2 + "' and '" + tarih + "'", baglan);
//FbCommand KacAdet = new FbCommand("SELECT COUNT(*) FROM Satis WHERE Satis_tarih='" + tarih + "'", baglan); object SatirSayisi = KacAdet.ExecuteScalar();
FbCommand KacAdet = new FbCommand("SELECT COUNT(*) FROM Satis WHERE Satis_tarih between '" + tarih2 + "' and '" + tarih + "'", baglan); object SatirSayisi = KacAdet.ExecuteScalar();
FbDataReader oku1; object SatisSonuc;
SatisSonuc = BugunkuSatis.ExecuteScalar();
if (SatisSonuc != null)
{
urunler = new string[int.Parse(SatirSayisi.ToString())];
oku1 = BugunkuSatis.ExecuteReader();
while (oku1.Read())
{
urunler[i] = oku1["Satis_urun"].ToString();
i++;
}
oku1.Close();
baglan.Close();
baglan2.Open();
FbDataReader oku2;
for (int j = 0; j <= int.Parse(SatirSayisi.ToString()) - 1; j++)
{
FbCommand UrunFiyat = new FbCommand("SELECT Urun_fiyat FROM Urunler WHERE Urun_id='" + urunler[j] + "'", baglan2);
oku2 = UrunFiyat.ExecuteReader();
oku2.Read();
top += float.Parse(oku2["Urun_fiyat"].ToString());
label5.Text = top.ToString();
oku2.Close();
}
baglan2.Close();
label5.Text += " TL";
baglan2.Open();
FbDataReader say;
for (int j = 0; j <= int.Parse(SatirSayisi.ToString()) - 1; j++)
{
FbCommand UrunFiyat = new FbCommand("SELECT Urun_alisFiyat FROM Urunler WHERE Urun_id='" + urunler[j] + "'", baglan2);
say = UrunFiyat.ExecuteReader();
say.Read();
top2 += float.Parse(say["Urun_alisFiyat"].ToString());
say.Close();
}
baglan2.Close();
// net Kar
label9.Text = (top - top2).ToString() + " TL";
// net Kar
baglan.Open();
FbCommand EncokSatilan = new FbCommand("SELECT Urun_adi FROM Urunler WHERE Urun_id = (SELECT first 1 rapor_satisId FROM Rapor WHERE rapor_tarih between '" + tarih2 + "' and '" + tarih + "' and rapor_sayac = ( SELECT MAX(rapor_sayac) FROM Rapor WHERE rapor_tarih between '" + tarih2 + "' and '" + tarih + "'))", baglan);
FbDataReader EncokStOku = EncokSatilan.ExecuteReader();
EncokStOku.Read();
label6.Text = EncokStOku["Urun_adi"].ToString();
baglan.Close();
baglan.Open();
FbCommand EncokKategori = new FbCommand("SELECT Kategori_adi FROM Urun_kategori WHERE Kategori_id = (SELECT first 1 Urun_kategori FROM Urunler WHERE Urun_id = ( SELECT first 1 rapor_satisId FROM Rapor WHERE rapor_tarih between '" + tarih2 + "' and '" + tarih + "' and rapor_sayac = ( SELECT MAX(rapor_sayac) FROM Rapor WHERE rapor_tarih between '" + tarih2 + "' and '" + tarih + "')))", baglan);
FbDataReader EncokKtOku = EncokKategori.ExecuteReader();
EncokKtOku.Read();
label7.Text = EncokKtOku["Kategori_adi"].ToString();
baglan.Close();
baglan.Open();
FbCommand UrunSayi = new FbCommand("SELECT SUM(rapor_sayac) FROM Rapor WHERE rapor_tarih between '" + tarih2 + "' and '" + tarih + "' GROUP BY rapor_satisKt", baglan);
object SatisAdet = UrunSayi.ExecuteScalar();
baglan.Close();
// Buraya kadar tamamlanmıştır..
int[] SeriesPoint = new int[0];
FbCommand UrunKategori = new FbCommand("SELECT rapor_satisKt FROM Rapor WHERE rapor_tarih between '" + tarih2 + "' and '" + tarih + "' GROUP BY rapor_satisKt", baglan);
FbCommand UrunKategoriSatisSys = new FbCommand("SELECT SUM(rapor_sayac) FROM Rapor WHERE rapor_tarih between '" + tarih2 + "' and '" + tarih + "' GROUP BY rapor_satisKt", baglan3);
// Grafik için sayısal değerler
baglan3.Open();
int sa = 0;
FbDataReader OkuUKSS = UrunKategoriSatisSys.ExecuteReader();
while (OkuUKSS.Read())
{
Array.Resize(ref SeriesPoint, SeriesPoint.Length + 1);
SeriesPoint[sa] = int.Parse(OkuUKSS[0].ToString());
sa++;
}
// kategori satış sayıları diziye atıldı
baglan3.Close();
baglan.Open();
FbDataReader ktOku = UrunKategori.ExecuteReader();
istatislik.Titles.Add("Haftalık Satış Raporu Grafik");
for (int k = 0; k < SeriesPoint.Length; k++)
{
ktOku.Read();
baglan2.Open();
FbCommand Kategori = new FbCommand("SELECT Kategori_adi FROM Urun_kategori WHERE Kategori_id='" + ktOku["rapor_satisKt"].ToString() + "'", baglan2);
FbDataReader Ktad = Kategori.ExecuteReader();
Ktad.Read();
Series series = istatislik.Series.Add(Ktad["Kategori_adi"].ToString());
series.Points.Add(SeriesPoint[k]);
baglan2.Close();
}
baglan.Close();
// Manuelsatışlar
baglan.Open();
FbCommand ManuelSatislar = new FbCommand("SELECT * FROM ManuelSatis WHERE satis_tarih between '" + tarih2 + "' and '" + tarih + "'", baglan);
object sn = ManuelSatislar.ExecuteScalar();
int msay = 0;
if (sn != null)
{
FbDataReader moku = ManuelSatislar.ExecuteReader();
while (moku.Read())
{
top += float.Parse(moku["satis_fiyat"].ToString());
top2 += float.Parse(moku["satis_alisFiyat"].ToString());
msay++;
}
label5.Text = top.ToString();
label9.Text = (top - top2).ToString();
Series series = istatislik.Series.Add("Manuel Satış");
series.Points.Add(msay);
}
baglan.Close();
// Manuelsatışlar
}
else
{
MessageBox.Show("Haftalık olarak şuana kadar satış yapılmamıştır. (R-02)", "Bilgilendirme");
this.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();
}
public static List <Employee> GetListPersonalFromDb(FbConnection db, int id_repair)
{
return(new List <Employee>());
}
internal ConnectionFireBird()
{
Conn = new FbConnection();
}
public int Save(int? IDDATACOMEMORATIVA, int IDCLIENTE, int IDPARENTESCO, int IDOCASIAO, DateTime DATACOM, string OBSERVACAO, string NOMECOMEMORATIVO)
{
int result = 0;
try
{
//Verificando a existência de um transação aberta
if (dbTransaction != null)
{
if (dbCnn.State == ConnectionState.Closed)
dbCnn.Open();
dbCommand = new FbCommand("Sav_DATACOMEMORATIVA", dbCnn);
dbCommand.Transaction = ((FbTransaction)(dbTransaction));
}
else
{
if (dbCnn == null)
dbCnn = ((FbConnection)GetConnectionDB());
if (dbCnn.State == ConnectionState.Closed)
dbCnn.Open();
dbCommand = new FbCommand("Sav_DATACOMEMORATIVA", dbCnn);
dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted);
}
dbCommand.CommandType = CommandType.StoredProcedure;
if(IDDATACOMEMORATIVA!= -1)
dbCommand.Parameters.AddWithValue("@IDDATACOMEMORATIVA", IDDATACOMEMORATIVA); //PrimaryKey
else
dbCommand.Parameters.AddWithValue("@IDDATACOMEMORATIVA", DBNull.Value); //PrimaryKey
dbCommand.Parameters.AddWithValue("@IDCLIENTE", IDCLIENTE); //Coluna
dbCommand.Parameters.AddWithValue("@IDPARENTESCO", IDPARENTESCO); //Coluna
dbCommand.Parameters.AddWithValue("@IDOCASIAO", IDOCASIAO); //Coluna
dbCommand.Parameters.AddWithValue("@DATACOM", DATACOM); //Coluna
dbCommand.Parameters.AddWithValue("@OBSERVACAO", OBSERVACAO); //Coluna
dbCommand.Parameters.AddWithValue("@NOMECOMEMORATIVO", NOMECOMEMORATIVO); //Coluna
//Retorno da Procedure
FbParameter returnValue;
returnValue = dbCommand.CreateParameter();
dbCommand.Parameters["@IDDATACOMEMORATIVA"].Direction = ParameterDirection.InputOutput;
//Executando consulta
dbCommand.ExecuteNonQuery();
result = int.Parse(dbCommand.Parameters["@IDDATACOMEMORATIVA"].Value.ToString());
if (dbTransaction == null)
{
dbCommand.Transaction.Commit();
dbCnn.Close();
}
}
catch (Exception ex)
{
if (dbTransaction != null)
this.RollbackTransaction();
else
{
if (dbCommand.Transaction != null)
dbCommand.Transaction.Rollback();
if (dbCnn.State == ConnectionState.Open)
dbCnn.Close();
}
throw ex;
}
return result;
}
