/// <summary>
/// Constructs a user from the output of a datareader. Assumes that there is data
/// ready to be read from the current record 14/12/15
/// </summary>
private static User readUser(OdbcDataReader dataReader)
{
return(new User(dataReader.GetInt16(0), dataReader.GetString(1),
dataReader.GetString(3), dataReader.GetString(4), dataReader.GetString(5),
dataReader.GetBoolean(6), dataReader.GetDateTime(7), dataReader.GetInt16(8),
dataReader.GetInt16(9), dataReader.GetBoolean(10), dataReader.GetBoolean(11),
dataReader.GetInt16(13)));
}
//
// GetUserFromReader
// A helper function that takes the current row from the OdbcDataReader
// and hydrates a MembershiUser from the values. Called by the
// MembershipUser.GetUser implementation.
//
private MembershipUser GetUserFromReader(OdbcDataReader reader)
{
object providerUserKey = reader.GetValue(0);
string username = reader.GetString(1);
string email = reader.GetString(2);
string passwordQuestion = "";
if (reader.GetValue(3) != DBNull.Value)
{
passwordQuestion = reader.GetString(3);
}
string comment = "";
if (reader.GetValue(4) != DBNull.Value)
{
comment = reader.GetString(4);
}
bool isApproved = reader.GetBoolean(5);
bool isLockedOut = reader.GetBoolean(6);
DateTime creationDate = reader.GetDateTime(7);
DateTime lastLoginDate = new DateTime();
if (reader.GetValue(8) != DBNull.Value)
{
lastLoginDate = reader.GetDateTime(8);
}
DateTime lastActivityDate = reader.GetDateTime(9);
DateTime lastPasswordChangedDate = reader.GetDateTime(10);
DateTime lastLockedOutDate = new DateTime();
if (reader.GetValue(11) != DBNull.Value)
{
lastLockedOutDate = reader.GetDateTime(11);
}
MembershipUser u = new MembershipUser(this.Name,
username,
providerUserKey,
email,
passwordQuestion,
comment,
isApproved,
isLockedOut,
creationDate,
lastLoginDate,
lastActivityDate,
lastPasswordChangedDate,
lastLockedOutDate);
return(u);
}
private void cargaConfiguracion()
{
OdbcConnection conexion = ASG_DB.connectionResult();
try
{
string sql = string.Format("SELECT * FROM CONFIGURACION_SISTEMA WHERE IDCONFIGURACION = 111;");
OdbcCommand cmd = new OdbcCommand(sql, conexion);
OdbcDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
checkBox1.Checked = reader.GetBoolean(2);
checkBox2.Checked = reader.GetBoolean(3);
checkBox9.Checked = reader.GetBoolean(4);
checkBox4.Checked = reader.GetBoolean(5);
checkBox6.Checked = reader.GetBoolean(6);
checkBox13.Checked = reader.GetBoolean(7);
checkBox14.Checked = reader.GetBoolean(8);
checkBox15.Checked = reader.GetBoolean(9);
}
else
{
MessageBox.Show("NO EXISTEN CONFIGURACIONES!", "SUCURSALES", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
catch (Exception ex)
{
MessageBox.Show("FALLO LA CONEXION CON LA BASE DE DATOS!" + "\n" + ex.ToString(), "SUCURSALES", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
conexion.Close();
}
public static void Refresh()
{
if (DateTime.Now.Subtract(UpdateTime).TotalMinutes < 1.0)
{
return; // Settings updated recently, so don't bother
}
UpdateTime = DateTime.Now;
using (OdbcConnection connection = new OdbcConnection(AppData.DBConnectionString))
{
connection.Open();
string SQLString = "SELECT ShowResults, ShowPercentage, BM2Ranking, BM2ViewHandRecord, BM2NumberEntryEachRound, BM2NameSource, PollInterval FROM Settings";
OdbcCommand cmd = new OdbcCommand(SQLString, connection);
OdbcDataReader reader = null;
try
{
ODBCRetryHelper.ODBCRetry(() =>
{
reader = cmd.ExecuteReader();
while (reader.Read())
{
ShowResults = reader.GetBoolean(0);
ShowPercentage = reader.GetBoolean(1);
ShowRanking = reader.GetInt32(2);
ShowHandRecord = reader.GetBoolean(3);
NumberEntryEachRound = reader.GetBoolean(4);
NameSource = reader.GetInt32(5);
PollInterval = reader.GetInt32(6);
}
});
}
catch
{
ShowResults = true;
ShowPercentage = true;
ShowRanking = 1;
ShowHandRecord = true;
NumberEntryEachRound = true;
NameSource = 0;
PollInterval = 1000;
}
finally
{
reader.Close();
cmd.Dispose();
}
}
}
//
// GetProfileInfoFromReader
// Takes the current row from the OdbcDataReader
// and populates a ProfileInfo object from the values.
//
private ProfileInfo GetProfileInfoFromReader(OdbcDataReader reader)
{
string username = reader.GetString(0);
DateTime lastActivityDate = new DateTime();
if (reader.GetValue(1) != DBNull.Value)
{
lastActivityDate = reader.GetDateTime(1);
}
DateTime lastUpdatedDate = new DateTime();
if (reader.GetValue(2) != DBNull.Value)
{
lastUpdatedDate = reader.GetDateTime(2);
}
bool isAnonymous = reader.GetBoolean(3);
// ProfileInfo.Size not currently implemented.
ProfileInfo p = new ProfileInfo(username,
isAnonymous, lastActivityDate, lastUpdatedDate, 0);
return(p);
}
/// <summary>
/// Permite valorar si el empleado tiene justificado el día ingresado
/// </summary>
/// <param name="EmpleadoId"></param>
/// <param name="Fecha"></param>
/// <returns></returns>
public bool GetJustificacion(int EmpleadoId, DateTime Fecha)
{
bool DiaJustificado = false;
try
{
string QueryString = @"EXEC stp_GetJustificacionFalta " + EmpleadoId + ", '" + Fecha.ToString("yyyyMMdd") + "'";
System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(QueryString);
using (System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(ConnectionString))
{
command.Connection = connection;
connection.Open();
using (OdbcDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
DiaJustificado = reader.GetBoolean(0);
}
reader.Close();
reader.Dispose();
}
connection.Close();
connection.Dispose();
}
}
catch (Exception ex)
{
Log.EscribeLog("Error: ElsabonDA.GetJustificacion - " + ex.Message);
}
return(DiaJustificado);
}
public Options(OdbcConnectionStringBuilder connectionString)
{
dbConnectionString = connectionString.ToString();
using (OdbcConnection connection = new OdbcConnection(dbConnectionString))
{
string SQLString = $"SELECT ShowResults, ShowPercentage, LeadCard, BM2ValidateLeadCard, BM2Ranking, BM2ViewHandRecord, BM2NumberEntryEachRound, BM2NameSource, EnterResultsMethod, TabletsMove FROM Settings";
OdbcCommand cmd = new OdbcCommand(SQLString, connection);
connection.Open();
OdbcDataReader reader = cmd.ExecuteReader();
reader.Read();
ShowTraveller = reader.GetBoolean(0);
ShowPercentage = reader.GetBoolean(1);
EnterLeadCard = reader.GetBoolean(2);
ValidateLeadCard = reader.GetBoolean(3);
ShowRanking = reader.GetInt32(4);
ShowHandRecord = reader.GetBoolean(5);
NumberEntryEachRound = reader.GetBoolean(6);
NameSource = reader.GetInt32(7);
EnterResultsMethod = reader.GetInt32(8);
if (EnterResultsMethod != 1)
{
EnterResultsMethod = 0;
}
TabletsMove = reader.GetBoolean(9);
reader.Close();
cmd.Dispose();
}
}
private void button10_Click(object sender, EventArgs e)
{
if (textBox3.Text != "")
{
OdbcConnection conexion = TaquillaDB.getDB();
try
{
string sql = string.Format("SELECT * FROM USUARIO WHERE USU_USUARIO = UPPER('{0}')", textBox3.Text);
OdbcCommand cmd = new OdbcCommand(sql, conexion);
OdbcDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
textBox1.Enabled = true;
textBox2.Enabled = true;
textBox3.Enabled = false;
button10.Enabled = false;
button6.Enabled = true;
button7.Enabled = true;
button1.Enabled = false;
checkBox1.Enabled = true;
textBox1.Text = reader.GetString(0);
//textBox2.Text = reader.GetString(1);
checkBox9.Enabled = true; checkBox9.Checked = reader.GetBoolean(2);
checkBox8.Enabled = true; checkBox8.Checked = reader.GetBoolean(3);
checkBox7.Enabled = true; checkBox7.Checked = reader.GetBoolean(4);
checkBox6.Enabled = true; checkBox6.Checked = reader.GetBoolean(5);
}
else
{
MessageBox.Show("USUARIO NO ENCONTRADO!", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
conexion.Close();
}
else
{
MessageBox.Show("ESCRIBA NOMBRE DE USUARIO!", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
textBox3.Focus();
}
}
//overloaded for ODBC
public static bool getBool(OdbcDataReader dr, string cn)
{
try
{
return(dr.GetBoolean(dr.GetOrdinal(cn)));
}
catch (InvalidCastException ice)
{
return(false);
}
}
// - /13>
//
// MembershipProvider.ValidateUser
//
// - 14>
public override bool ValidateUser(string username, string password)
{
bool isValid = false;
OdbcConnection conn = new OdbcConnection(ConnectionString);
OdbcCommand cmd = new OdbcCommand("SELECT Password, IsApproved FROM Users " +
" WHERE Username = ? AND ApplicationName = ?", conn);
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;
OdbcDataReader reader = null;
bool isApproved = false;
string pwd = "";
try
{
conn.Open();
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (reader.HasRows)
{
reader.Read();
pwd = reader.GetString(0);
isApproved = reader.GetBoolean(1);
}
if (isApproved && (password == pwd))
{
isValid = true;
OdbcCommand updateCmd = new OdbcCommand("UPDATE Users SET LastLoginDate = ?" +
" WHERE Username = ? AND ApplicationName = ?", conn);
updateCmd.Parameters.Add("@LastLoginDate", OdbcType.DateTime).Value = DateTime.Now;
updateCmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
updateCmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;
updateCmd.ExecuteNonQuery();
}
}
catch (OdbcException)
{
// Handle exception.
}
finally
{
if (reader != null) { reader.Close(); }
conn.Close();
}
return isValid;
}
public Options(OdbcConnectionStringBuilder connectionString)
{
dbConnectionString = connectionString.ToString();
using (OdbcConnection connection = new OdbcConnection(dbConnectionString))
{
string SQLString = $"SELECT ShowResults, ShowPercentage, BM2Ranking, BM2ViewHandRecord, BM2NumberEntryEachRound, BM2NameSource, PollInterval FROM Settings";
OdbcCommand cmd = new OdbcCommand(SQLString, connection);
connection.Open();
OdbcDataReader reader = cmd.ExecuteReader();
reader.Read();
ShowTraveller = reader.GetBoolean(0);
ShowPercentage = reader.GetBoolean(1);
ShowRanking = reader.GetInt32(2);
ShowHandRecord = reader.GetBoolean(3);
NumberEntryEachRound = reader.GetBoolean(4);
NameSource = reader.GetInt32(5);
PollInterval = reader.GetInt32(6);
reader.Close();
cmd.Dispose();
}
}
/// <summary>
/// Used to login the user. Uses the username and password provided and hashes the
/// password, checks it against the existing password and authenticates, if
/// successful, returns true with UserID out for cookie storage of user session.
/// 14/12/15
/// </summary>
public static bool AuthenticateUser(string username, string password, string sessionID)
{
// Pull the password from the database for the specified user
string query = "SELECT `sahp`, `disabled` FROM `" + userTable + "` WHERE `Username`='" + username + "'";
string sahp = "";
bool disabled = true;
// Creates a database command from the query and existing connection
OdbcCommand cmd = new OdbcCommand(query, connection);
if (connectionOpen())
{
try
{
// Execute the command and open a reader
OdbcDataReader dataReader = cmd.ExecuteReader();
// If the query has returned anything...
if (dataReader.HasRows)
{
// Advance to first row
dataReader.Read();
// Get the salted and hashed password
sahp = dataReader.GetString(0);
disabled = dataReader.GetBoolean(1);
}
dataReader.Close();
}
catch (OdbcException ex)
{
// Displays an error if something bad occurs while executing the command
error = ex.Message;
}
// Checks that the user has not been disabled
if (!disabled)
{
// Takes the salted and hashed password and uses it to validate the provided password
if (PasswordHash.ValidatePassword(password, sahp))
{
// If the password is correct, sets the SessionID in the database to
// the current client's SessionID and returns that authentication was sucessful
ExecuteNonQuery("UPDATE `" + userTable + "` SET `SessionID`='" + sessionID +
"' WHERE `Username`='" + username + "';");
return(true);
}
}
}
// If something didn't pass, authentication was unsucessful
return(false);
}
private void cargaMonedas()
{
OdbcConnection conexion = ASG_DB.connectionResult();
try
{
string sql = string.Format("SELECT * FROM TIPO_CAMBIO;");
OdbcCommand cmd = new OdbcCommand(sql, conexion);
OdbcDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
if (reader.GetString(0) == "MXN")
{
label30.Text = label30.Text + " | Cambio Actual - Q." + "" + reader.GetDouble(1);
checkBox12.Checked = reader.GetBoolean(2);
}
else if (reader.GetString(0) == "QUETZALES")
{
label29.Text = label29.Text + " | Cambio Actual - Q." + "" + reader.GetDouble(1);
checkBox10.Checked = reader.GetBoolean(2);
}
else if (reader.GetString(0) == "DOLARES")
{
label28.Text = label28.Text + " | Cambio Actual - Q." + "" + reader.GetDouble(1);
checkBox11.Checked = reader.GetBoolean(2);
}
while (reader.Read())
{
if (reader.GetString(0) == "MXN")
{
label30.Text = label30.Text + " | Cambio Actual - Q." + "" + reader.GetDouble(1);
checkBox12.Checked = reader.GetBoolean(2);
}
else if (reader.GetString(0) == "QUETZALES")
{
label29.Text = label29.Text + " | Cambio Actual - Q." + "" + reader.GetDouble(1);
checkBox10.Checked = reader.GetBoolean(2);
}
else if (reader.GetString(0) == "DOLARES")
{
label28.Text = label28.Text + " | Cambio Actual - Q." + "" + reader.GetDouble(1);
checkBox11.Checked = reader.GetBoolean(2);
}
}
}
else
{
MessageBox.Show("NO EXISTEN SUCURSALES!", "SUCURSALES", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
catch (Exception ex)
{
MessageBox.Show("FALLO LA CONEXION CON LA BASE DE DATOS!" + "\n" + ex.ToString(), "SUCURSALES", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
conexion.Close();
}
public bool getBool(string cn)
{
try
{
if (Program.dataSource == Program.DataSources.Access)
{
return(dbr.GetBoolean(dbr.GetOrdinal(cn)));
}
else
{
return(Odbr.GetBoolean(Odbr.GetOrdinal(cn)));
}
}
catch (InvalidCastException ice)
{
return(false);
}
}
private void cargaAjustes()
{
OdbcConnection conexion = ASG_DB.connectionResult();
try
{
string sql = string.Format("SELECT IMPRESORA, COPIAS, VISTA FROM CONFIGURACION_SISTEMA WHERE IDCONFIGURACION = 111;");
OdbcCommand cmd = new OdbcCommand(sql, conexion);
OdbcDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
comboBox5.Text = reader.GetString(0);
textBox1.Text = reader.GetString(1);
checkBox1.Checked = reader.GetBoolean(2);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
conexion.Close();
}
private void button1_Click(object sender, EventArgs e)
{
OdbcConnection conexion = ASG_DB.connectionResult();
string sql = string.Format("SELECT NOMBRE_USUARIO, ESTADO_USUARIO, ID_ROL, ID_USUARIO FROM USUARIO WHERE ID_USUARIO = '{0}' AND PASSWORD_USUARIO = '{1}'", textBox1.Text.Trim(), textBox2.Text.Trim());
OdbcCommand cmd = new OdbcCommand(sql, conexion);
OdbcDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
if ((reader.GetBoolean(1) == true))
{
if ((reader.GetString(2) == "ADMINISTRADOR") | (reader.GetString(3) == user))
{
flagSesion = true;
timerActions();
}
else
{
MessageBox.Show("INGRESE LOS CREDENCIALES DE SU CUENTA O DE UN ADMINISTRADOR!", "INICIO DE SESION", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
else
{
MessageBox.Show("TU CUENTA HA SIDO DESACTIVADA!", "INICIO DE SESION", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
else
{
MessageBox.Show("USUARIO O CONTRASEÑA INCORRECTOS!", "INICIO DE SESION", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
textBox1.Text = "USUARIO";
textBox1.ForeColor = Color.DimGray;
textBox2.UseSystemPasswordChar = false;
textBox2.Text = "CONTRASEÑA";
textBox2.ForeColor = Color.DimGray;
checkBox1.Checked = false;
textBox1.Focus();
}
}
private void button1_Click(object sender, EventArgs e)
{
OdbcConnection conexion = ASG_DB.connectionResult();
string sql = string.Format("SELECT NOMBRE_USUARIO, ESTADO_USUARIO, ID_ROL, ID_USUARIO, imageUser FROM USUARIO WHERE ID_USUARIO = '{0}' AND PASSWORD_USUARIO = '{1}'", textBox1.Text.Trim(), textBox2.Text.Trim());
OdbcCommand cmd = new OdbcCommand(sql, conexion);
OdbcDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
if ((reader.GetBoolean(1) == true))
{
nameUser = reader.GetString(0);
rolUser = reader.GetString(2);
userName = reader.GetString(3);
userImage = reader.GetString(4);
getPrivilegios(textBox1.Text.Trim());
timerActions();
conexion.Close();
}
else
{
MessageBox.Show("TU CUENTA HA SIDO DESACTIVADA!", "INICIO DE SESION", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
else
{
MessageBox.Show("USUARIO O CONTRASEÑA INCORRECTOS!", "INICIO DE SESION", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
textBox1.Text = "USUARIO";
textBox1.ForeColor = Color.DimGray;
textBox2.UseSystemPasswordChar = false;
textBox2.Text = "CONTRASEÑA";
textBox2.ForeColor = Color.DimGray;
checkBox1.Checked = false;
textBox1.Focus();
}
}
/// <summary>
/// Gets the value of the column at the specified index as a boolean.
/// </summary>
/// <param name="columnIndex">Index of column to examine.</param>
/// <returns>Value of the column as a boolean.</returns>
public bool GetBool(int columnIndex)
{
return(_reader.GetBoolean(columnIndex));
}
private void getPrivilegios(string id_usuario, string id_rol)
{
OdbcConnection conexion = ASG_DB.connectionResult();
try
{
string sql = string.Format("SELECT * FROM PRIVILEGIOS_USUARIO WHERE USUARIO_ID_USUARIO = '{0}' AND ROL_ID_ROL = '{1}';", id_usuario, id_rol);
OdbcCommand cmd = new OdbcCommand(sql, conexion);
OdbcDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
checkBox34.Checked = reader.GetBoolean(2);
checkBox32.Checked = reader.GetBoolean(3);
checkBox33.Checked = reader.GetBoolean(4);
checkBox31.Checked = reader.GetBoolean(5);
checkBox30.Checked = reader.GetBoolean(6);
checkBox29.Checked = reader.GetBoolean(7);
checkBox28.Checked = reader.GetBoolean(8);
checkBox27.Checked = reader.GetBoolean(9);
checkBox26.Checked = reader.GetBoolean(10);
checkBox25.Checked = reader.GetBoolean(11);
checkBox24.Checked = reader.GetBoolean(12);
checkBox23.Checked = reader.GetBoolean(13);
checkBox22.Checked = reader.GetBoolean(14);
checkBox21.Checked = reader.GetBoolean(15);
checkBox20.Checked = reader.GetBoolean(16);
checkBox19.Checked = reader.GetBoolean(17);
checkBox18.Checked = reader.GetBoolean(18);
}
}
catch (Exception ex)
{
MessageBox.Show("FALLO LA CONEXION CON LA BASE DE DATOS!" + "\n" + ex.ToString(), "GESTION USUARIOS", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
conexion.Close();
}
/// <summary>
/// Constructs an achievement from the output of a datareader. Assumes that there
/// is data ready to be read from the current record 14/12/15
/// </summary>
private static Achievement readAchievement(OdbcDataReader dataReader)
{
return(new Achievement(dataReader.GetInt16(0), dataReader.GetString(1),
dataReader.GetString(2), dataReader.GetString(3), dataReader.GetString(4),
dataReader.GetInt16(5), dataReader.GetDateTime(6), dataReader.GetBoolean(7)));
}
/// <summary>
/// Returns a list of achievements that are of the specified categories. 27/11/15
/// </summary>
public static List <Achievement> GetAchievements(List <string> categories)
{
// Create a list to store the returned achievements
List <Achievement> achievements = new List <Achievement>();
// Get all achievements where the category is...
string query = "SELECT * FROM `" + achievementTable + "` WHERE `Category` IN (";
// ..any of the categories specified.
foreach (string x in categories)
{
query = (query + '"' + x + '"' + ",");
}
// Remove the succeeding comma that is added due to the previous foreach loop,
// and finish the query with a bracket.
query = query.Remove(query.Length - 1);
query = query + ")";
if (connectionOpen())
{
// Create a database command from the query and existing connection.
OdbcCommand cmd = new OdbcCommand(query, connection);
try
{
// Execute the command and open a reader.
OdbcDataReader dataReader = cmd.ExecuteReader();
while (dataReader.Read()) // Read the next record.
{
// Get the id first, and check to make sure it is something.
int id = dataReader.GetInt16(0);
if (id != 0)
{
// Adds the achievement that is read from the database to the
// list of achievements that is to be returned.
achievements.Add(new Achievement(id, dataReader.GetString(1),
dataReader.GetString(2), dataReader.GetString(3),
dataReader.GetString(4), dataReader.GetInt16(5),
dataReader.GetDateTime(6), dataReader.GetBoolean(7)));
}
}
dataReader.Close();
}
catch (OdbcException ex)
{
// Displays an error if something bad occurs while executing the command
error = ex.Message;
}
}
return(achievements);
}
private void ContractReviewCheckList_QA_Load(object sender, EventArgs e)
{
// initialize some textboxes
jobNoTextBox.Text = jobNo;
jobNoTextBox.Enabled = false;
approvalTextBox.Text = Globals.userName;
approvalTextBox.Enabled = false;
for (int i = 1; i < 12; i += 2)
{
rowSizes[i] = tableLayoutPanel1.RowStyles[i].Height;
tableLayoutPanel1.RowStyles[i].Height = 0;
}
// check if it exists in DB and load data
using (OdbcConnection conn = new OdbcConnection(Globals.odbc_connection_string))
{
conn.Open();
string query =
"SELECT\n" +
"[PO_Number]\n" +
",[Description_Of_Change]\n" +
",[Initial]\n" +
",[PO_Date]\n" +
",[Planning]\n" +
",[Question1_Response]\n" +
",[Question2_Response]\n" +
",[Question3_Response]\n" +
",[Question4_Response]\n" +
",[Question5_Response]\n" +
",[Question6_Response]\n" +
",[Question1_Comments]\n" +
",[Question2_Comments]\n" +
",[Question3_Comments]\n" +
",[Question4_Comments]\n" +
",[Question5_Comments]\n" +
",[Question6_Comments]\n" +
"FROM[ATI_Workflow].[dbo].[ContractReview_QA]\n" +
"WHERE Job = '" + jobNo + "' AND Workflow_ID = '" + workflow_ID + "';";
OdbcCommand com = new OdbcCommand(query, conn);
OdbcDataReader reader = com.ExecuteReader();
if (reader.Read())
{
poNoTextBox.Text = reader.IsDBNull(0) ? "" : reader.GetString(0);
descriptionOfChangeTextBox.Text = reader.IsDBNull(1) ? "" : reader.GetString(1);
initialTextBox.Text = reader.IsDBNull(2) ? "" : reader.GetString(2);
dateTextBox.Text = reader.IsDBNull(3) ? "" : reader.GetString(3);
qualityTextBox.Text = reader.IsDBNull(4) ? "" : reader.GetString(4);
question1CheckBox.Checked = reader.IsDBNull(5) ? false : reader.GetBoolean(5);
question2CheckBox.Checked = reader.IsDBNull(6) ? false : reader.GetBoolean(6);
question3CheckBox.Checked = reader.IsDBNull(7) ? false : reader.GetBoolean(7);
question4CheckBox.Checked = reader.IsDBNull(8) ? false : reader.GetBoolean(8);
question5CheckBox.Checked = reader.IsDBNull(9) ? false : reader.GetBoolean(9);
question6CheckBox.Checked = reader.IsDBNull(10) ? false : reader.GetBoolean(10);
question1TextBox.Text = reader.IsDBNull(11) ? "" : reader.GetString(11);
question2TextBox.Text = reader.IsDBNull(12) ? "" : reader.GetString(12);
question3TextBox.Text = reader.IsDBNull(13) ? "" : reader.GetString(13);
question4TextBox.Text = reader.IsDBNull(14) ? "" : reader.GetString(14);
question5TextBox.Text = reader.IsDBNull(15) ? "" : reader.GetString(15);
question6TextBox.Text = reader.IsDBNull(16) ? "" : reader.GetString(16);
}
}
// fix page to left half
this.Left = 0;
this.Top = 0;
this.Height = System.Windows.Forms.Screen.PrimaryScreen.WorkingArea.Height;
}
private void button4_Click(object sender, EventArgs e)
{
try
{
Gramatika = true;
OdbcConnection connection = new OdbcConnection();
connection.ConnectionString = "DSN=PostgreSQL35W;UID=masterwordcounter;PWD=masterwordcounter";
connection.Open();
string q = "SELECT DISTINCT word, COALESCE(root,0), type, irregular,id FROM words ORDER BY word";
OdbcCommand getAllWords = new OdbcCommand(q, connection);
OdbcDataReader odr2 = getAllWords.ExecuteReader();
lista_rijeci_gramatika.Clear();
while (odr2.Read())
{
Rijec t = new Rijec();
t.Tekst = odr2.GetString(0);
t.Korijen = odr2.GetString(1);
t.Tip = odr2.GetInt32(2);
t.Irregular = odr2.GetBoolean(3);
t.Id = odr2.GetInt32(4);
lista_rijeci_gramatika.Add(t);
}
odr2.Close();
connection.Close();
}
catch (Exception er)
{
MessageBox.Show(er.Message);
}
lista_rijeci_poslije_gramatike.Clear();
foreach (Rijec r in lista_rijeci)
{
foreach (Rijec r1 in lista_rijeci_gramatika)
{
if (r.Tekst == r1.Tekst)
{
if (!r1.Korijen.Equals("0"))
{
foreach (Rijec r3 in lista_rijeci_gramatika)
{
if (r3.Id.ToString().Equals(r1.Korijen))
{
foreach (Rijec r4 in lista_rijeci)
{
if (r4.Tekst == r3.Tekst)
{
r4.Ponavljanje += r.Ponavljanje;
r.Irregular = true;
break;
}
}
break;
}
}
break;
}
}
}
}
foreach (Rijec r in lista_rijeci)
{
if (r.Irregular == false)
{
lista_rijeci_poslije_gramatike.Add(r);
}
}
dataGridView1.DataSource = lista_rijeci_poslije_gramatike;
chart1.Series["Rijec"].Points.Clear();
MessageBox.Show(Convert.ToString(dataGridView1.RowCount));
dataGridView1.Sort(dataGridView1.Columns[2], ListSortDirection.Ascending);
for (int i = 0; i < lista_rijeci_poslije_gramatike.Count; i++)
{
chart1.Series["Rijec"].Points.AddXY(lista_rijeci[i].Ponavljanje, i);
}
label1.Text = "Ukupno riječi: " + lista_rijeci_poslije_gramatike.Count;
chart1.Series["Rijec"].Points.Clear();
for (int i = 0; i < lista_rijeci_poslije_gramatike.Count; i++)
{
chart1.Series["Rijec"].Points.AddXY(i + 1, lista_rijeci_poslije_gramatike[i].Ponavljanje);
}
chart1.ChartAreas[0].AxisX.Maximum = 80;
chart1.ChartAreas[0].AxisX.Minimum = 0;
chart1.ChartAreas[0].AxisY.Maximum = Convert.ToDouble(dataGridView1.Rows[0].Cells[2].Value);
chart1.ChartAreas[0].AxisY.Minimum = 0;
label1.Text = "Ukupno riječi: " + lista_rijeci_poslije_gramatike.Count;
}
/// <summary>
///
/// select a row from table t_RBSR_AUFW_u_METADATA_SubprToActivityList.
/// </summary>
/// <param name="ID"></param>
/// <returns>returnGetMETADATA_SubprToActivityList</returns>
public returnGetMETADATA_SubprToActivityList GetMETADATA_SubprToActivityList(int ID)
{
returnGetMETADATA_SubprToActivityList rv = new returnGetMETADATA_SubprToActivityList();
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "select \"c_id\",\"c_u_Sequence\",\"c_u_NodeType\",\"c_u_BOOLisKeyPoint\",\"c_u_Text\",\"c_u_ListIdsBusRoles\",\"c_u_ListIdsApps\",\"c_r_SubProcess\" from \"t_RBSR_AUFW_u_METADATA_SubprToActivityList\" where \"c_id\"= ?";
cmd.Parameters.Add("c_id", OdbcType.Int);
cmd.Parameters["c_id"].Value = (object)ID;
cmd.Connection = _dbConnection;
OdbcDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
if (dr.IsDBNull(0))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'ID'");
}
else
{
rv.ID = dr.GetInt32(0);
}
if (dr.IsDBNull(1))
{
rv.Sequence = null;
}
else
{
rv.Sequence = typeof(float).Equals(dr.GetFieldType(1))? (double)dr.GetFloat(1): dr.GetDouble(1);
}
if (dr.IsDBNull(2))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'NodeType'");
}
else
{
rv.NodeType = dr.GetString(2);
}
if (dr.IsDBNull(3))
{
rv.BOOLisKeyPoint = null;
}
else
{
rv.BOOLisKeyPoint = typeof(short).IsAssignableFrom(dr.GetFieldType(3))? (dr.GetInt16(3) != 0): dr.GetBoolean(3);
}
if (dr.IsDBNull(4))
{
rv.Text = null;
}
else
{
rv.Text = dr.GetString(4);
}
if (dr.IsDBNull(5))
{
rv.ListIdsBusRoles = null;
}
else
{
rv.ListIdsBusRoles = dr.GetString(5);
}
if (dr.IsDBNull(6))
{
rv.ListIdsApps = null;
}
else
{
rv.ListIdsApps = dr.GetString(6);
}
if (dr.IsDBNull(7))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'SubProcessID'");
}
else
{
rv.SubProcessID = dr.GetInt32(7);
}
}
dr.Close();
dr.Dispose();
cmd.Dispose();
DBClose();
return(rv);
}
/// <summary>
///
/// select a set of rows from table t_RBSR_AUFW_u_METADATA_SubprToActivityList.
/// </summary>
/// <param name="maxRowsToReturn">Max number of rows to return. If null or 0 all rows are returned.</param>
/// <param name="SubProcessID"></param>
/// <returns>returnListMETADATA_SubprToActivityListBySubProcess[]</returns>
public returnListMETADATA_SubprToActivityListBySubProcess[] ListMETADATA_SubprToActivityListBySubProcess(int?maxRowsToReturn, int SubProcessID)
{
returnListMETADATA_SubprToActivityListBySubProcess[] rv = null;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
if (maxRowsToReturn.HasValue && maxRowsToReturn.Value > 0)
{
if (_dbConnection.Driver.ToLower().StartsWith("sql"))
{
cmd.CommandText = "SELECT TOP " + maxRowsToReturn.Value + " \"c_id\", \"c_u_Sequence\", \"c_u_NodeType\", \"c_u_BOOLisKeyPoint\", \"c_u_Text\", \"c_u_ListIdsBusRoles\", \"c_u_ListIdsApps\", \"c_r_SubProcess\" FROM \"t_RBSR_AUFW_u_METADATA_SubprToActivityList\" WHERE \"c_r_SubProcess\"=? ORDER BY c_u_Sequence";
}
else
{
cmd.CommandText = "SELECT \"c_id\", \"c_u_Sequence\", \"c_u_NodeType\", \"c_u_BOOLisKeyPoint\", \"c_u_Text\", \"c_u_ListIdsBusRoles\", \"c_u_ListIdsApps\", \"c_r_SubProcess\" FROM \"t_RBSR_AUFW_u_METADATA_SubprToActivityList\" WHERE \"c_r_SubProcess\"=? ORDER BY c_u_Sequence " + " LIMIT " + maxRowsToReturn.Value;
}
}
else
{
cmd.CommandText = "SELECT \"c_id\", \"c_u_Sequence\", \"c_u_NodeType\", \"c_u_BOOLisKeyPoint\", \"c_u_Text\", \"c_u_ListIdsBusRoles\", \"c_u_ListIdsApps\", \"c_r_SubProcess\" FROM \"t_RBSR_AUFW_u_METADATA_SubprToActivityList\" WHERE \"c_r_SubProcess\"=? ORDER BY c_u_Sequence";
}
cmd.Parameters.Add("1_SubProcessID", OdbcType.Int);
cmd.Parameters["1_SubProcessID"].Value = (object)SubProcessID;
OdbcDataReader dr = cmd.ExecuteReader();
List <returnListMETADATA_SubprToActivityListBySubProcess> rvl = new List <returnListMETADATA_SubprToActivityListBySubProcess>();
while (dr.Read())
{
returnListMETADATA_SubprToActivityListBySubProcess cr = new returnListMETADATA_SubprToActivityListBySubProcess();
if (dr.IsDBNull(0))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'ID'");
}
else
{
cr.ID = dr.GetInt32(0);
}
if (dr.IsDBNull(1))
{
cr.Sequence = null;
}
else
{
cr.Sequence = typeof(float).Equals(dr.GetFieldType(1))? (double)dr.GetFloat(1): dr.GetDouble(1);
}
if (dr.IsDBNull(2))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'NodeType'");
}
else
{
cr.NodeType = dr.GetString(2);
}
if (dr.IsDBNull(3))
{
cr.BOOLisKeyPoint = null;
}
else
{
cr.BOOLisKeyPoint = typeof(short).IsAssignableFrom(dr.GetFieldType(3))? (dr.GetInt16(3) != 0): dr.GetBoolean(3);
}
if (dr.IsDBNull(4))
{
cr.Text = null;
}
else
{
cr.Text = dr.GetString(4);
}
if (dr.IsDBNull(5))
{
cr.ListIdsBusRoles = null;
}
else
{
cr.ListIdsBusRoles = dr.GetString(5);
}
if (dr.IsDBNull(6))
{
cr.ListIdsApps = null;
}
else
{
cr.ListIdsApps = dr.GetString(6);
}
if (dr.IsDBNull(7))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'SubProcessID'");
}
else
{
cr.SubProcessID = dr.GetInt32(7);
}
rvl.Add(cr);
}
dr.Close();
dr.Dispose();
rv = rvl.ToArray();
cmd.Dispose();
DBClose();
return(rv);
}
private List <InterestFeature> GetInterestFeature(string Filter)
{
List <InterestFeature> features = new List <InterestFeature> {
};
using (DatabaseConnection conn = GetDatabaseconnection())
{
// Oddity from the way the query works, this covers species and habitat interest
// features, not just species as the query name implies
OdbcCommand cmd = conn.CreateCommand("{CALL Select_all_species_features(?)}");
OdbcParameter prm = cmd.Parameters.Add("prefix", OdbcType.Char, 5);
prm.Value = Filter;
using (OdbcDataReader reader = cmd.ExecuteReader())
{
// Basic Feature List
while (reader.Read())
{
features.Add(new InterestFeature
{
Code = reader.GetString(0),
Name = reader.GetString(1),
LayTitle = reader.GetString(2),
SectionNumber = reader.GetDouble(3),
SectionTitle = reader.GetString(4),
InterestGroup = reader.IsDBNull(5) ? null : reader.GetString(5),
Priority = reader.GetBoolean(6),
Total = reader.GetInt32(8)
});
}
}
}
foreach (var feature in features)
{
using (DatabaseConnection conn = GetDatabaseconnection())
{
OdbcCommand cmd = conn.CreateCommand(String.Format("SELECT FEATURE_DESCRIPTION, EU_STATUS, UK_STATUS, RATIONALE FROM ASP_INTEREST_FEATURES WHERE INT_CODE LIKE '{0}'", feature.Code));
using (OdbcDataReader reader = cmd.ExecuteReader())
{
// Single result expected
reader.Read();
feature.FeatureDescription = Regex.Replace(reader.GetString(0), @"<(font|\/font|FONT|\/FONT)[^>]{0,}>", string.Empty);
feature.EUStatus = Regex.Replace(reader.GetString(1), @"<(font|\/font|FONT|\/FONT)[^>]{0,}>", string.Empty);
feature.UKStatus = reader.GetString(2);
feature.Rationale = reader.IsDBNull(3) ? null : reader.GetString(3);
}
}
using (DatabaseConnection conn = GetDatabaseconnection())
{
OdbcCommand cmd = conn.CreateCommand("{CALL ASP312_feature_detail(?)}");
OdbcParameter prm = cmd.Parameters.Add("FeatureIntCode", OdbcType.Char, 5);
prm.Value = feature.Code;
using (OdbcDataReader reader = cmd.ExecuteReader())
{
reader.Read();
feature.MapData = new InterestFeatureMapData()
{
MapSources = reader.IsDBNull(5) ? null : reader.GetString(5),
MapExplanation = reader.IsDBNull(6) ? null : reader.GetString(6),
Units = reader.IsDBNull(7) ? null : reader.GetString(7),
England = reader.IsDBNull(8) ? null : reader.GetString(8),
Scotland = reader.IsDBNull(9) ? null : reader.GetString(9),
Wales = reader.IsDBNull(10) ? null : reader.GetString(10),
NorthernIreland = reader.IsDBNull(11) ? null : reader.GetString(11),
UKOffshoreWaters = reader.IsDBNull(12) ? null : reader.GetString(12),
TotalUkPopulation = reader.IsDBNull(13) ? null : reader.GetString(13)
};
}
}
using (DatabaseConnection conn = GetDatabaseconnection())
{
// Oddity from the way the query works, this covers species and habitat interest
// features, not just species as the query name implies
OdbcCommand cmd = conn.CreateCommand("{CALL Occurrences_by_Feature_INT_CODE(?)}");
OdbcParameter prm = cmd.Parameters.Add("prefix", OdbcType.Char, 5);
prm.Value = feature.Code;
List <InterestFeatureOccurrence> occurences = new List <InterestFeatureOccurrence> {
};
using (OdbcDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
occurences.Add(new InterestFeatureOccurrence
{
SiteName = reader.GetString(2),
SiteCode = reader.GetString(3),
InterestStatus = reader.GetString(4),
InterestStatusLong = reader.GetString(5),
GlobalGrade = reader.GetString(6),
PrimaryText = reader.IsDBNull(7) ? null : reader.GetString(7),
SecondaryText = reader.IsDBNull(8) ? null : reader.GetString(8),
LocalAuthority = reader.GetString(9)
});
}
}
feature.Occurrences = occurences;
}
}
return(features);
}
static void Main(string[] args)
{
string m_userid, m_password;
bool m_passCheck = false;
bool m_userCheck = false;
int m_count = 0;
List <String> m_userIDList = new List <String>();
OdbcConnection con = new OdbcConnection("Driver={Microsoft Access Driver (*.mdb)};DBQ=KinesisArcade.mdb");
con.Open();
OdbcCommand m_createLoginInstance = con.CreateCommand();
OdbcCommand m_checkPassword = con.CreateCommand();
OdbcCommand m_checkUser = con.CreateCommand();
OdbcCommand m_getCount = con.CreateCommand();
m_getCount.CommandText = "SELECT COUNT(*) FROM [USER]";
m_getCount.Connection = con;
OdbcDataReader readerC = m_getCount.ExecuteReader();
readerC.Read();
m_count = readerC.GetInt32(0);
m_checkUser.CommandText = "SELECT UserID FROM [USER]";
m_checkUser.Connection = con;
OdbcDataReader readerU = m_checkUser.ExecuteReader();
for (int i = 0; i < m_count; i++)
{
readerU.Read();
string user = readerU.GetString(0);
m_userIDList.Add(user);
}
Console.WriteLine("Please enter your login details\n");
Console.WriteLine("User ID: ");
m_userid = Console.ReadLine();
while (!m_userCheck)
{
for (int i = 0; i < m_count; i++)
{
if (String.Compare(m_userIDList[i], m_userid) == 0)
{
m_userCheck = true;
}
}
if (!m_userCheck)
{
Console.WriteLine("UserID does not exist in the database\n");
Console.WriteLine("User ID: ");
m_userid = Console.ReadLine();
}
}
;
Console.WriteLine("Password: "******"SELECT UserID, Password, IsSupervisor FROM [USER] WHERE UserID = '" + m_userid + "'";
m_checkPassword.Connection = con;
OdbcDataReader readerP = m_checkPassword.ExecuteReader();
readerP.Read();
string password = readerP.GetString(1);
while (!m_passCheck)
{
if (String.Compare(password, m_password) == 0)
{
m_passCheck = true;
}
else
{
Console.WriteLine("Incorrect Passsword, Please try again\n");
Console.WriteLine("Password: "******"d/MM/yyyy HH:mm");
m_createLoginInstance.CommandText = "Insert into LOGININSTANCE(UserIDEntered,PasswordCorrect,LoginTime)Values('" + m_userid + "','" + Convert.ToInt32(m_passCheck) + "','" + time + "')";
m_createLoginInstance.Connection = con;
m_createLoginInstance.ExecuteNonQuery();
}
;
if (readerP.GetBoolean(2))
{
con.Close();
ProcessStartInfo wInfo = new ProcessStartInfo("WebUI\\Web\\main.html");
Process.Start(wInfo);
}
else
{
con.Close();
FileStream fs = new FileStream("currentuser.txt", FileMode.Truncate);
Byte[] info = new UTF8Encoding(true).GetBytes(m_userid);
fs.Write(info, 0, info.Length);
ProcessStartInfo gInfo = new ProcessStartInfo("BeatTheScarVR\\KinesisArcade.exe");
Process.Start(gInfo);
}
}
private void getPrivilegios(string NombreUsuario)
{
OdbcConnection conexion = ASG_DB.connectionResult();
try
{
string sql = string.Format("SELECT APERTURA_CAJA, CIERRE_CAJA, ANULAR_FACTURA, DEVOLUCIONES, VER_REPORTES,ELIMINAR_CLIENTE,INGRESAR_CLIENTE,INGRESO_PROVEEDOR,ELIMINAR_PROVEEDOR,BITACORA,CUENTAS_COBRAR,CUENTAS_PAGAR,INGRESO_USUARIOS,ELIMINAR_USUARIOS,CONSULTAR_MERCADERIA,COMPRAS,TRASLADO_MERCADERIA, KARDEX FROM PRIVILEGIOS_USUARIO WHERE USUARIO_ID_USUARIO = '{0}' AND ROL_ID_ROL = '{1}';", NombreUsuario, rolUser);
OdbcCommand cmd = new OdbcCommand(sql, conexion);
OdbcDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
privilegios[0] = reader.GetBoolean(0);
privilegios[1] = reader.GetBoolean(1);
privilegios[2] = reader.GetBoolean(2);
privilegios[3] = reader.GetBoolean(3);
privilegios[4] = reader.GetBoolean(4);
privilegios[5] = reader.GetBoolean(5);
privilegios[6] = reader.GetBoolean(6);
privilegios[7] = reader.GetBoolean(7);
privilegios[8] = reader.GetBoolean(8);
privilegios[9] = reader.GetBoolean(9);
privilegios[10] = reader.GetBoolean(10);
privilegios[11] = reader.GetBoolean(11);
privilegios[12] = reader.GetBoolean(12);
privilegios[13] = reader.GetBoolean(13);
privilegios[14] = reader.GetBoolean(14);
privilegios[15] = reader.GetBoolean(15);
privilegios[16] = reader.GetBoolean(16);
privilegios[17] = reader.GetBoolean(17);
}
else
{
// MessageBox.Show("imposbe obenter priiegios");
}
} catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
conexion.Close();
}
public List <Site> GetFullSACList()
{
List <Site> sacs = new List <Site> {
};
using (DatabaseConnection conn = GetDatabaseconnection())
{
OdbcCommand cmd = conn.CreateCommand("{CALL Select_all_SACs_any_status}");
using (OdbcDataReader reader = conn.RunCommand(cmd))
{
// Basic SAC List
while (reader.Read())
{
sacs.Add(new Site
{
EUCode = reader.GetString(1),
Country = reader.GetString(2),
Name = reader.GetString(3),
CountryFull = reader.GetString(4),
Area = reader.GetDouble(5),
GridReference = reader.IsDBNull(6) ? null : reader.GetString(6),
LocalAuthority = reader.GetString(7),
StatusCode = reader.GetInt32(8),
StatusShort = reader.GetString(9)
});
}
}
}
// More detailed SAC List (no information feature info yet)
foreach (Site site in sacs)
{
using (DatabaseConnection conn = GetDatabaseconnection())
{
OdbcCommand cmd = conn.CreateCommand("{CALL Select_Site_Data_by_EUcode(?)}");
var prm = cmd.Parameters.Add("SAC_EU_CODE", OdbcType.Char, 9);
prm.Value = site.EUCode;
using (OdbcDataReader reader = cmd.ExecuteReader())
{
reader.Read();
site.Latitude = reader.GetDouble(6);
site.Longitude = reader.GetDouble(7);
if (!reader.IsDBNull(8))
{
site.XCoord = reader.GetInt32(8);
}
if (!reader.IsDBNull(9))
{
site.YCoord = reader.GetInt32(9);
}
site.LinkText = reader.IsDBNull(11) ? null : reader.GetString(11);
site.StatusLong = reader.GetString(14);
}
List <SiteFeature> features = new List <SiteFeature> {
};
cmd = conn.CreateCommand("{CALL SAC_Features_occurrences(?)}");
prm = cmd.Parameters.Add("SAC_EU_CODE", OdbcType.Char, 9);
prm.Value = site.EUCode;
// Several results can appear here
using (OdbcDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
features.Add(new SiteFeature
{
Code = reader.GetString(0),
Name = reader.GetString(1),
InterestStatus = reader.GetString(4),
InterestStatusLong = reader.GetString(5),
GlobalGrade = reader.GetString(6),
PrimaryText = reader.IsDBNull(7) ? null : reader.GetString(7),
SecondaryText = reader.IsDBNull(8) ? null : reader.GetString(8),
LocalAuthority = reader.GetString(9),
LayTitle = reader.GetString(10),
Priority = reader.GetBoolean(11)
});
}
}
site.Features = features;
}
using (DatabaseConnection conn = GetDatabaseconnection())
{
OdbcCommand cmd = conn.CreateCommand("{CALL Select_SAC_Site_Character(?)}");
OdbcParameter prm = cmd.Parameters.Add("SAC_EU_CODE", OdbcType.Char, 9);
prm.Value = site.EUCode;
List <SiteCharacter> character = new List <SiteCharacter> {
};
// Several results can appear here
using (OdbcDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
character.Add(new SiteCharacter
{
Character = reader.GetString(1),
Coverage = reader.IsDBNull(2) ? Double.NaN : reader.GetDouble(2)
});
}
}
site.Character = character;
}
}
return(sacs);
}
public Table(int sectionID, int tableNumber)
{
SectionID = sectionID;
TableNumber = tableNumber;
LastBid = new Bid("", 0, "", "", false, "", 0, -1);
LastPlay = new Play("", 0, "", -999);
using (OdbcConnection connection = new OdbcConnection(AppData.DBConnectionString))
{
object queryResult = null;
connection.Open();
// Get the status of this table
string SQLString = $"SELECT CurrentRound, CurrentBoard, BiddingStarted, BiddingComplete, PlayComplete FROM Tables WHERE Section={sectionID} AND [Table]={tableNumber}";
OdbcCommand cmd = new OdbcCommand(SQLString, connection);
OdbcDataReader reader = null;
try
{
ODBCRetryHelper.ODBCRetry(() =>
{
reader = cmd.ExecuteReader();
if (reader.Read())
{
queryResult = reader.GetValue(0);
if (queryResult != DBNull.Value && queryResult != null)
{
RoundNumber = Convert.ToInt32(queryResult);
}
queryResult = reader.GetValue(1);
if (queryResult != DBNull.Value && queryResult != null)
{
BoardNumber = Convert.ToInt32(queryResult);
}
BiddingStarted = reader.GetBoolean(2);
BiddingComplete = reader.GetBoolean(3);
PlayComplete = reader.GetBoolean(4);
}
});
}
finally
{
reader.Close();
cmd.Dispose();
}
// Ensure this table is recorded as logged on
SQLString = $"UPDATE Tables SET LogOnOff=1 WHERE Section={sectionID} AND [Table]={tableNumber}";
cmd = new OdbcCommand(SQLString, connection);
try
{
ODBCRetryHelper.ODBCRetry(() =>
{
cmd.ExecuteNonQuery();
});
}
finally
{
cmd.Dispose();
}
// Get the pair numbers and boards for this round
GetRoundData(connection);
}
// Check for invalid board number and set accordingly
if (BoardNumber < LowBoard || BoardNumber > HighBoard)
{
BoardNumber = LowBoard;
}
}