// Get the last round that has any results entered for it
public static int GetLastRoundWithResults(int sectionID)
{
object queryResult = null;
using (OdbcConnection connection = new OdbcConnection(AppData.DBConnectionString))
{
connection.Open();
string SQLString = $"SELECT MAX(Round) FROM ReceivedData WHERE Section={sectionID}";
OdbcCommand cmd = new OdbcCommand(SQLString, connection);
try
{
ODBCRetryHelper.ODBCRetry(() =>
{
queryResult = cmd.ExecuteScalar();
});
}
finally
{
cmd.Dispose();
}
}
if (queryResult == DBNull.Value || queryResult == null)
{
return(1);
}
else
{
return(Convert.ToInt32(queryResult));
}
}
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, LeadCard, BM2ValidateLeadCard, BM2Ranking, EnterResultsMethod, BM2ViewHandRecord, BM2NumberEntryEachRound, BM2NameSource, TabletsMove 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);
EnterLeadCard = reader.GetBoolean(2);
ValidateLeadCard = reader.GetBoolean(3);
ShowRanking = reader.GetInt32(4);
EnterResultsMethod = reader.GetInt32(5);
ShowHandRecord = reader.GetBoolean(6);
NumberEntryEachRound = reader.GetBoolean(7);
NameSource = reader.GetInt32(8);
TabletDevicesMove = reader.GetBoolean(9);
}
});
}
catch
{
ShowResults = true;
ShowPercentage = true;
EnterLeadCard = true;
ValidateLeadCard = true;
ShowRanking = 1;
EnterResultsMethod = 1;
ShowHandRecord = true;
NumberEntryEachRound = true;
NameSource = 0;
TabletDevicesMove = false;
}
finally
{
reader.Close();
cmd.Dispose();
}
}
}
private static string GetNameFromPlayerNumbersTableIndividual(OdbcConnection conn, TableStatus tableStatus, int playerNo)
{
if (playerNo == 0)
{
return("");
}
string number = "";
string name = "";
DateTime latestTimeLog = new DateTime(2010, 1, 1);
string SQLString = $"SELECT Number, Name, Round, TimeLog FROM PlayerNumbers WHERE Section={tableStatus.SectionID} AND TabScorePairNo={playerNo}";
OdbcCommand cmd = new OdbcCommand(SQLString, conn);
OdbcDataReader reader = null;
try
{
ODBCRetryHelper.ODBCRetry(() =>
{
reader = cmd.ExecuteReader();
while (reader.Read())
{
try
{
int readerRoundNumber = reader.GetInt32(2);
DateTime timeLog;
if (reader.IsDBNull(3))
{
timeLog = new DateTime(2010, 1, 1);
}
else
{
timeLog = reader.GetDateTime(3);
}
if (readerRoundNumber <= tableStatus.RoundNumber && timeLog >= latestTimeLog)
{
number = reader.GetString(0);
name = reader.GetString(1);
latestTimeLog = timeLog;
}
}
catch { } // Record found, but format cannot be parsed
}
});
}
finally
{
cmd.Dispose();
reader.Close();
}
return(FormatName(name, number));
}
public static void RegisterTable(int sectionID, int tableNumber)
{
using (OdbcConnection connection = new OdbcConnection(AppData.DBConnectionString))
{
connection.Open();
string SQLString = $"UPDATE Tables SET LogOnOff=1 WHERE Section={sectionID} AND [Table]={tableNumber}";
OdbcCommand cmd = new OdbcCommand(SQLString, connection);
try
{
ODBCRetryHelper.ODBCRetry(() =>
{
cmd.ExecuteNonQuery();
});
}
finally
{
cmd.Dispose();
}
}
}
private static string GetNameFromExternalDatabase(int playerNumber)
{
string name = "";
OdbcConnectionStringBuilder externalDB = new OdbcConnectionStringBuilder {
Driver = "Microsoft Access Driver (*.mdb)"
};
externalDB.Add("Dbq", @"C:\Bridgemate\BMPlayerDB.mdb");
externalDB.Add("Uid", "Admin");
using (OdbcConnection connection = new OdbcConnection(externalDB.ToString()))
{
object queryResult = null;
string SQLString = $"SELECT Name FROM PlayerNameDatabase WHERE ID={playerNumber}";
OdbcCommand cmd = new OdbcCommand(SQLString, connection);
try
{
connection.Open();
ODBCRetryHelper.ODBCRetry(() =>
{
queryResult = cmd.ExecuteScalar();
if (queryResult == null)
{
name = "Unknown #" + playerNumber;
}
else
{
name = queryResult.ToString();
}
});
}
catch (OdbcException) // If we can't read the external database for whatever reason...
{
name = "#" + playerNumber;
}
finally
{
cmd.Dispose();
}
}
return(name);
}
// Find out how many rounds there are in the event
// Need to re-query database in case rounds are added/removed by scoring program
public static int NumberOfRoundsInEvent(int sectionID)
{
object queryResult = null;
using (OdbcConnection connection = new OdbcConnection(AppData.DBConnectionString))
{
connection.Open();
string SQLString = SQLString = $"SELECT MAX(Round) FROM RoundData WHERE Section={sectionID}";
OdbcCommand cmd = new OdbcCommand(SQLString, connection);
try
{
ODBCRetryHelper.ODBCRetry(() =>
{
queryResult = cmd.ExecuteScalar();
});
}
finally
{
cmd.Dispose();
}
}
return(Convert.ToInt32(queryResult));
}
public static void Refresh()
{
if (DateTime.Now.Subtract(UpdateTime).TotalMinutes < 1.0)
{
return; // Hand records updated recently, so don't bother
}
UpdateTime = DateTime.Now;
HandRecordsList.Clear();
using (OdbcConnection connection = new OdbcConnection(AppData.DBConnectionString))
{
string SQLString = $"SELECT Section, Board, NorthSpades, NorthHearts, NorthDiamonds, NorthClubs, EastSpades, EastHearts, EastDiamonds, EastClubs, SouthSpades, SouthHearts, SouthDiamonds, SouthClubs, WestSpades, WestHearts, WestDiamonds, WestClubs FROM HandRecord";
OdbcCommand cmd = new OdbcCommand(SQLString, connection);
OdbcDataReader reader = null;
connection.Open();
try
{
ODBCRetryHelper.ODBCRetry(() =>
{
reader = cmd.ExecuteReader();
while (reader.Read())
{
HandRecord handRecord = new HandRecord
{
SectionID = reader.GetInt16(0),
BoardNumber = reader.GetInt16(1),
NorthSpades = reader.GetString(2),
NorthHearts = reader.GetString(3),
NorthDiamonds = reader.GetString(4),
NorthClubs = reader.GetString(5),
EastSpades = reader.GetString(6),
EastHearts = reader.GetString(7),
EastDiamonds = reader.GetString(8),
EastClubs = reader.GetString(9),
SouthSpades = reader.GetString(10),
SouthHearts = reader.GetString(11),
SouthDiamonds = reader.GetString(12),
SouthClubs = reader.GetString(13),
WestSpades = reader.GetString(14),
WestHearts = reader.GetString(15),
WestDiamonds = reader.GetString(16),
WestClubs = reader.GetString(17)
};
handRecord.Dealer = Utilities.GetDealerForBoard(handRecord.BoardNumber);
HandRecordsList.Add(handRecord);
}
});
}
catch (OdbcException e)
{
if (e.Errors.Count == 1 && e.Errors[0].SQLState == "42S02") // HandRecord table does not exist
{
return;
}
else
{
throw (e);
}
}
finally
{
reader.Close();
cmd.Dispose();
}
SQLString = $"SELECT Section, Board, NorthSpades, NorthHearts, NorthDiamonds, NorthClubs, NorthNotrump, EastSpades, EastHearts, EastDiamonds, EastClubs, EastNotrump, SouthSpades, SouthHearts, SouthDiamonds, SouthClubs, SouthNotrump, WestSpades, WestHearts, WestDiamonds, WestClubs, WestNoTrump, NorthHcp, EastHcp, SouthHcp, WestHcp FROM HandEvaluation";
cmd = new OdbcCommand(SQLString, connection);
try
{
ODBCRetryHelper.ODBCRetry(() =>
{
reader = cmd.ExecuteReader();
while (reader.Read())
{
HandRecord handRecord = HandRecordsList.Find(x => x.SectionID == reader.GetInt16(0) && x.BoardNumber == reader.GetInt16(1));
if (handRecord != null)
{
if (reader.GetInt16(2) > 6)
{
handRecord.EvalNorthSpades = (reader.GetInt16(2) - 6).ToString();
}
else
{
handRecord.EvalNorthSpades = "";
}
if (reader.GetInt16(3) > 6)
{
handRecord.EvalNorthHearts = (reader.GetInt16(3) - 6).ToString();
}
else
{
handRecord.EvalNorthHearts = "";
}
if (reader.GetInt16(4) > 6)
{
handRecord.EvalNorthDiamonds = (reader.GetInt16(4) - 6).ToString();
}
else
{
handRecord.EvalNorthDiamonds = "";
}
if (reader.GetInt16(5) > 6)
{
handRecord.EvalNorthClubs = (reader.GetInt16(5) - 6).ToString();
}
else
{
handRecord.EvalNorthClubs = "";
}
if (reader.GetInt16(6) > 6)
{
handRecord.EvalNorthNT = (reader.GetInt16(6) - 6).ToString();
}
else
{
handRecord.EvalNorthNT = "";
}
if (reader.GetInt16(7) > 6)
{
handRecord.EvalEastSpades = (reader.GetInt16(7) - 6).ToString();
}
else
{
handRecord.EvalEastSpades = "";
}
if (reader.GetInt16(8) > 6)
{
handRecord.EvalEastHearts = (reader.GetInt16(8) - 6).ToString();
}
else
{
handRecord.EvalEastHearts = "";
}
if (reader.GetInt16(9) > 6)
{
handRecord.EvalEastDiamonds = (reader.GetInt16(9) - 6).ToString();
}
else
{
handRecord.EvalEastDiamonds = "";
}
if (reader.GetInt16(10) > 6)
{
handRecord.EvalEastClubs = (reader.GetInt16(10) - 6).ToString();
}
else
{
handRecord.EvalEastClubs = "";
}
if (reader.GetInt16(11) > 6)
{
handRecord.EvalEastNT = (reader.GetInt16(11) - 6).ToString();
}
else
{
handRecord.EvalEastNT = "";
}
if (reader.GetInt16(12) > 6)
{
handRecord.EvalSouthSpades = (reader.GetInt16(12) - 6).ToString();
}
else
{
handRecord.EvalSouthSpades = "";
}
if (reader.GetInt16(13) > 6)
{
handRecord.EvalSouthHearts = (reader.GetInt16(13) - 6).ToString();
}
else
{
handRecord.EvalSouthHearts = "";
}
if (reader.GetInt16(14) > 6)
{
handRecord.EvalSouthDiamonds = (reader.GetInt16(14) - 6).ToString();
}
else
{
handRecord.EvalSouthDiamonds = "";
}
if (reader.GetInt16(15) > 6)
{
handRecord.EvalSouthClubs = (reader.GetInt16(15) - 6).ToString();
}
else
{
handRecord.EvalSouthClubs = "";
}
if (reader.GetInt16(16) > 6)
{
handRecord.EvalSouthNT = (reader.GetInt16(16) - 6).ToString();
}
else
{
handRecord.EvalSouthNT = "";
}
if (reader.GetInt16(17) > 6)
{
handRecord.EvalWestSpades = (reader.GetInt16(17) - 6).ToString();
}
else
{
handRecord.EvalWestSpades = "";
}
if (reader.GetInt16(18) > 6)
{
handRecord.EvalWestHearts = (reader.GetInt16(18) - 6).ToString();
}
else
{
handRecord.EvalWestHearts = "";
}
if (reader.GetInt16(19) > 6)
{
handRecord.EvalWestDiamonds = (reader.GetInt16(19) - 6).ToString();
}
else
{
handRecord.EvalWestDiamonds = "";
}
if (reader.GetInt16(20) > 6)
{
handRecord.EvalWestClubs = (reader.GetInt16(20) - 6).ToString();
}
else
{
handRecord.EvalWestClubs = "";
}
if (reader.GetInt16(21) > 6)
{
handRecord.EvalWestNT = (reader.GetInt16(21) - 6).ToString();
}
else
{
handRecord.EvalWestNT = "";
}
handRecord.HCPNorth = reader.GetInt16(22).ToString();
handRecord.HCPEast = reader.GetInt16(23).ToString();
handRecord.HCPSouth = reader.GetInt16(24).ToString();
handRecord.HCPWest = reader.GetInt16(25).ToString();
}
}
});
}
catch (OdbcException e)
{
if (e.Errors.Count > 1 || e.Errors[0].SQLState != "42S02") // Error other than HandEvaluation table does not exist
{
throw (e);
}
}
finally
{
reader.Close();
cmd.Dispose();
}
}
return;
}
public RoundsList(int sectionID, int roundNumber)
{
using (OdbcConnection connection = new OdbcConnection(AppData.DBConnectionString))
{
connection.Open();
if (AppData.IsIndividual)
{
string SQLString = $"SELECT [Table], NSPair, EWPair, LowBoard, HighBoard, South, West FROM RoundData WHERE Section={sectionID} AND Round={roundNumber}";
OdbcCommand cmd = new OdbcCommand(SQLString, connection);
OdbcDataReader reader = null;
try
{
ODBCRetryHelper.ODBCRetry(() =>
{
reader = cmd.ExecuteReader();
while (reader.Read())
{
Round round = new Round()
{
TableNumber = reader.GetInt32(0),
NumberNorth = reader.GetInt32(1),
NumberEast = reader.GetInt32(2),
LowBoard = reader.GetInt32(3),
HighBoard = reader.GetInt32(4),
NumberSouth = reader.GetInt32(5),
NumberWest = reader.GetInt32(6)
};
Add(round);
}
});
}
finally
{
reader.Close();
cmd.Dispose();
}
}
else // Not individual
{
string SQLString = $"SELECT [Table], NSPair, EWPair, LowBoard, HighBoard FROM RoundData WHERE Section={sectionID} AND Round={roundNumber}";
OdbcCommand cmd = new OdbcCommand(SQLString, connection);
OdbcDataReader reader = null;
try
{
ODBCRetryHelper.ODBCRetry(() =>
{
reader = cmd.ExecuteReader();
while (reader.Read())
{
Round round = new Round()
{
TableNumber = reader.GetInt32(0),
NumberNorth = reader.GetInt32(1),
NumberEast = reader.GetInt32(2),
LowBoard = reader.GetInt32(3),
HighBoard = reader.GetInt32(4),
};
Add(round);
}
});
}
finally
{
reader.Close();
cmd.Dispose();
}
}
}
}
// Database read constructor
public Result(TableStatus tableStatus, int boardNumber)
{
NumberNorth = tableStatus.RoundData.NumberNorth;
NumberSouth = tableStatus.RoundData.NumberSouth;
NumberEast = tableStatus.RoundData.NumberEast;
NumberWest = tableStatus.RoundData.NumberWest;
BoardNumber = boardNumber;
using (OdbcConnection connection = new OdbcConnection(AppData.DBConnectionString))
{
connection.Open();
string SQLString = $"SELECT [NS/EW], Contract, Result, LeadCard, Remarks FROM ReceivedData WHERE Section={tableStatus.SectionID} AND [Table]={tableStatus.TableNumber} AND Round={tableStatus.RoundNumber} AND Board={BoardNumber}";
OdbcCommand cmd = new OdbcCommand(SQLString, connection);
OdbcDataReader reader = null;
try
{
ODBCRetryHelper.ODBCRetry(() =>
{
reader = cmd.ExecuteReader();
if (reader.Read())
{
if (reader.GetString(4) == "Not played")
{
ContractLevel = -1;
ContractSuit = "";
ContractX = "";
DeclarerNSEW = "";
TricksTakenNumber = -1;
LeadCard = "";
}
else
{
string temp = reader.GetString(1);
Contract = temp; // Sets ContractLevel, etc
if (ContractLevel == 0) // Passed out
{
DeclarerNSEW = "";
TricksTakenNumber = -1;
LeadCard = "";
}
else
{
DeclarerNSEW = reader.GetString(0);
TricksTakenSymbol = reader.GetString(2);
LeadCard = reader.GetString(3);
}
}
}
else // No result in database
{
ContractLevel = -999;
ContractSuit = "";
ContractX = "";
DeclarerNSEW = "";
TricksTakenNumber = -1;
LeadCard = "";
}
});
}
finally
{
reader.Close();
cmd.Dispose();
}
}
}
public void UpdateDB(TableStatus tableStatus)
{
int declarer;
if (ContractLevel <= 0)
{
declarer = 0;
}
else
{
if (DeclarerNSEW == "N" || DeclarerNSEW == "S")
{
declarer = NumberNorth;
}
else
{
declarer = NumberEast;
}
}
string leadcard;
if (LeadCard == null || LeadCard == "" || LeadCard == "SKIP")
{
leadcard = "";
}
else
{
leadcard = LeadCard;
}
using (OdbcConnection connection = new OdbcConnection(AppData.DBConnectionString))
{
// Delete any previous result
connection.Open();
string SQLString = $"DELETE FROM ReceivedData WHERE Section={tableStatus.SectionID} AND [Table]={tableStatus.TableNumber} AND Round={tableStatus.RoundNumber} AND Board={BoardNumber}";
OdbcCommand cmd = new OdbcCommand(SQLString, connection);
try
{
ODBCRetryHelper.ODBCRetry(() =>
{
cmd.ExecuteNonQuery();
});
}
finally
{
cmd.Dispose();
}
// Add new result
string remarks = "";
if (ContractLevel == -1)
{
remarks = "Not played";
}
if (AppData.IsIndividual)
{
SQLString = $"INSERT INTO ReceivedData (Section, [Table], Round, Board, PairNS, PairEW, South, West, Declarer, [NS/EW], Contract, Result, LeadCard, Remarks, DateLog, TimeLog, Processed, Processed1, Processed2, Processed3, Processed4, Erased) VALUES ({tableStatus.SectionID}, {tableStatus.TableNumber}, {tableStatus.RoundNumber}, {BoardNumber}, {NumberNorth}, {NumberEast}, {NumberSouth}, {NumberWest}, {declarer}, '{DeclarerNSEW}', '{Contract}', '{TricksTakenSymbol}', '{leadcard}', '{remarks}', #{DateTime.Now:yyyy-MM-dd}#, #{DateTime.Now:yyyy-MM-dd hh:mm:ss}#, False, False, False, False, False, False)";
}
else
{
SQLString = $"INSERT INTO ReceivedData (Section, [Table], Round, Board, PairNS, PairEW, Declarer, [NS/EW], Contract, Result, LeadCard, Remarks, DateLog, TimeLog, Processed, Processed1, Processed2, Processed3, Processed4, Erased) VALUES ({tableStatus.SectionID}, {tableStatus.TableNumber}, {tableStatus.RoundNumber}, {BoardNumber}, {NumberNorth}, {NumberEast}, {declarer}, '{DeclarerNSEW}', '{Contract}', '{TricksTakenSymbol}', '{leadcard}', '{remarks}', #{DateTime.Now:yyyy-MM-dd}#, #{DateTime.Now:yyyy-MM-dd hh:mm:ss}#, False, False, False, False, False, False)";
}
OdbcCommand cmd2 = new OdbcCommand(SQLString, connection);
try
{
ODBCRetryHelper.ODBCRetry(() =>
{
cmd2.ExecuteNonQuery();
});
}
finally
{
cmd2.Dispose();
}
}
}
public Traveller(int tabletDeviceNumber, TableStatus tableStatus)
{
TabletDeviceNumber = tabletDeviceNumber;
BoardNumber = tableStatus.ResultData.BoardNumber;
NumberNorth = tableStatus.RoundData.NumberNorth;
using (OdbcConnection connection = new OdbcConnection(AppData.DBConnectionString))
{
connection.Open();
string SQLString;
OdbcCommand cmd = null;
OdbcDataReader reader = null;
try
{
if (AppData.IsIndividual)
{
SQLString = $"SELECT PairNS, PairEW, South, West, [NS/EW], Contract, LeadCard, Result FROM ReceivedData WHERE Section={tableStatus.SectionID} AND Board={BoardNumber}";
}
else
{
SQLString = $"SELECT PairNS, PairEW, [NS/EW], Contract, LeadCard, Result FROM ReceivedData WHERE Section={tableStatus.SectionID} AND Board={BoardNumber}";
}
cmd = new OdbcCommand(SQLString, connection);
ODBCRetryHelper.ODBCRetry(() =>
{
reader = cmd.ExecuteReader();
while (reader.Read())
{
Result result = null;
if (AppData.IsIndividual)
{
result = new Result()
{
BoardNumber = BoardNumber,
NumberNorth = reader.GetInt32(0),
NumberEast = reader.GetInt32(1),
NumberSouth = reader.GetInt32(2),
NumberWest = reader.GetInt32(3),
DeclarerNSEW = reader.GetString(4),
Contract = reader.GetString(5),
LeadCard = reader.GetString(6),
TricksTakenSymbol = reader.GetString(7)
};
}
else
{
result = new Result()
{
BoardNumber = BoardNumber,
NumberNorth = reader.GetInt32(0),
NumberEast = reader.GetInt32(1),
DeclarerNSEW = reader.GetString(2),
Contract = reader.GetString(3),
LeadCard = reader.GetString(4),
TricksTakenSymbol = reader.GetString(5)
};
}
if (result.Contract.Length > 2) // Testing for unplayed boards and corrupt ReceivedData table
{
result.CalculateScore();
Add(result);
if (result.NumberNorth == NumberNorth) // Get score for current result for calculating percentages
{
currentScore = result.Score;
}
}
}
});
}
finally
{
reader.Close();
cmd.Dispose();
}
};
// Sort traveller and calculate percentage for current result
Sort((x, y) => y.Score.CompareTo(x.Score));
if (Settings.ShowPercentage)
{
if (Count == 1)
{
PercentageNS = 50;
}
else
{
int currentMP = 2 * FindAll((x) => x.Score < currentScore).Count + FindAll((x) => x.Score == currentScore).Count - 1;
PercentageNS = Convert.ToInt32(50.0 * currentMP / (Count - 1));
}
}
else
{
PercentageNS = -1; // Don't show percentage
}
// Determine if there is a hand record to view
HandRecord = false;
if (Settings.ShowHandRecord && HandRecords.HandRecordsList.Count > 0)
{
HandRecord handRecord = HandRecords.HandRecordsList.Find(x => x.SectionID == tableStatus.SectionID && x.BoardNumber == BoardNumber);
if (handRecord != null)
{
HandRecord = true;
}
else // Can't find matching hand record, so try default SectionID = 1
{
handRecord = HandRecords.HandRecordsList.Find(x => x.SectionID == 1 && x.BoardNumber == BoardNumber);
if (handRecord != null)
{
HandRecord = true;
}
}
}
}
// Database read constructor
public Round(TableStatus tableStatus)
{
using (OdbcConnection connection = new OdbcConnection(AppData.DBConnectionString))
{
connection.Open();
if (AppData.IsIndividual)
{
string SQLString = $"SELECT NSPair, EWPair, South, West, LowBoard, HighBoard FROM RoundData WHERE Section={tableStatus.SectionID} AND Table={tableStatus.TableNumber} AND Round={tableStatus.RoundNumber}";
OdbcCommand cmd = new OdbcCommand(SQLString, connection);
OdbcDataReader reader = null;
try
{
ODBCRetryHelper.ODBCRetry(() =>
{
reader = cmd.ExecuteReader();
if (reader.Read())
{
NumberNorth = reader.GetInt32(0);
NumberEast = reader.GetInt32(1);
NumberSouth = reader.GetInt32(2);
NumberWest = reader.GetInt32(3);
LowBoard = reader.GetInt32(4);
HighBoard = reader.GetInt32(5);
}
});
}
finally
{
reader.Close();
cmd.Dispose();
}
}
else // Not individual
{
string SQLString = $"SELECT NSPair, EWPair, LowBoard, HighBoard FROM RoundData WHERE Section={tableStatus.SectionID} AND Table={tableStatus.TableNumber} AND Round={tableStatus.RoundNumber}";
OdbcCommand cmd = new OdbcCommand(SQLString, connection);
OdbcDataReader reader = null;
try
{
ODBCRetryHelper.ODBCRetry(() =>
{
reader = cmd.ExecuteReader();
if (reader.Read())
{
NumberNorth = NumberSouth = reader.GetInt32(0);
NumberEast = NumberWest = reader.GetInt32(1);
LowBoard = reader.GetInt32(2);
HighBoard = reader.GetInt32(3);
}
});
}
finally
{
reader.Close();
cmd.Dispose();
}
}
}
// Check for use of missing pair in Section table and set player numbers to 0 if necessary
int missingPair = AppData.SectionsList.Find(x => x.SectionID == tableStatus.SectionID).MissingPair;
if (NumberNorth == missingPair)
{
NumberNorth = NumberSouth = 0;
}
if (NumberEast == missingPair)
{
NumberEast = NumberWest = 0;
}
return;
}
public void UpdatePlayer(int sectionID, int tableNumber, Direction direction, int roundNumber, int playerNumber)
{
// First get name, and update the Round
string playerName = "";
string directionLetter = Enum.GetName(typeof(Direction), direction).Substring(0, 1); // Need just N, S, E or W
if (playerNumber == 0)
{
playerName = "Unknown";
}
else
{
switch (Settings.NameSource)
{
case 0:
playerName = AppData.GetNameFromPlayerNamesTable(playerNumber);
break;
case 1:
playerName = GetNameFromExternalDatabase(playerNumber);
break;
case 2:
playerName = "";
break;
case 3:
playerName = AppData.GetNameFromPlayerNamesTable(playerNumber);
if (playerName == "" || playerName.Substring(0, 1) == "#" || (playerName.Length >= 7 && playerName.Substring(0, 7) == "Unknown"))
{
playerName = GetNameFromExternalDatabase(playerNumber);
}
break;
}
}
int pairNumber = 0;
if (AppData.IsIndividual)
{
switch (directionLetter)
{
case "N":
NameNorth = playerName;
pairNumber = NumberNorth;
break;
case "S":
NameSouth = playerName;
pairNumber = NumberSouth;
break;
case "E":
NameEast = playerName;
pairNumber = NumberEast;
break;
case "W":
NameWest = playerName;
pairNumber = NumberWest;
break;
}
}
else
{
switch (directionLetter)
{
case "N":
NameNorth = playerName;
pairNumber = NumberNorth;
break;
case "S":
NameSouth = playerName;
pairNumber = NumberNorth;
break;
case "E":
NameEast = playerName;
pairNumber = NumberEast;
break;
case "W":
NameWest = playerName;
pairNumber = NumberEast;
break;
}
}
// Now update the PlayerNumbers table in the database
// Numbers entered at the start (when round = 1) need to be set as round 0 in the database
if (roundNumber == 1)
{
roundNumber = 0;
}
if (playerNumber == 0)
{
playerName = "";
}
playerName = playerName.Replace("'", "''"); // Deal with apostrophes in names, eg O'Connor
using (OdbcConnection connection = new OdbcConnection(AppData.DBConnectionString))
{
connection.Open();
object queryResult = null;
// Check if PlayerNumbers entry exists already; if it does update it, if not create it
string SQLString = $"SELECT Section FROM PlayerNumbers WHERE Section={sectionID} AND [Table]={tableNumber} AND Round={roundNumber} AND Direction='{directionLetter}'";
OdbcCommand cmd = new OdbcCommand(SQLString, connection);
try
{
ODBCRetryHelper.ODBCRetry(() =>
{
queryResult = cmd.ExecuteScalar();
});
}
finally
{
cmd.Dispose();
}
if (queryResult == null)
{
SQLString = $"INSERT INTO PlayerNumbers (Section, [Table], Direction, [Number], Name, Round, Processed, TimeLog, TabScorePairNo) VALUES ({sectionID}, {tableNumber}, '{directionLetter}', '{playerNumber}', '{playerName}', {roundNumber}, False, #{DateTime.Now:yyyy-MM-dd hh:mm:ss}#, {pairNumber})";
}
else
{
SQLString = $"UPDATE PlayerNumbers SET [Number]='{playerNumber}', [Name]='{playerName}', Processed=False, TimeLog=#{DateTime.Now:yyyy-MM-dd hh:mm:ss}#, TabScorePairNo={pairNumber} WHERE Section={sectionID} AND [Table]={tableNumber} AND Round={roundNumber} AND Direction='{directionLetter}'";
}
OdbcCommand cmd2 = new OdbcCommand(SQLString, connection);
try
{
ODBCRetryHelper.ODBCRetry(() =>
{
cmd2.ExecuteNonQuery();
});
}
finally
{
cmd2.Dispose();
}
}
return;
}
private static string GetNameFromPlayerNumbersTable(OdbcConnection conn, TableStatus tableStatus, int pairNo, string dir)
{
if (pairNo == 0)
{
return("");
}
string number = "";
string name = "";
DateTime latestTimeLog = new DateTime(2010, 1, 1);
// First look for entries in the same direction
string SQLString = $"SELECT Number, Name, Round, TimeLog FROM PlayerNumbers WHERE Section={tableStatus.SectionID} AND TabScorePairNo={pairNo} AND Direction='{dir}'";
OdbcCommand cmd = new OdbcCommand(SQLString, conn);
OdbcDataReader reader = null;
try
{
ODBCRetryHelper.ODBCRetry(() =>
{
reader = cmd.ExecuteReader();
while (reader.Read())
{
try
{
int readerRoundNumber = reader.GetInt32(2);
DateTime timeLog;
if (reader.IsDBNull(3))
{
timeLog = new DateTime(2010, 1, 1);
}
else
{
timeLog = reader.GetDateTime(3);
}
if (readerRoundNumber <= tableStatus.RoundNumber && timeLog >= latestTimeLog)
{
number = reader.GetString(0);
name = reader.GetString(1);
latestTimeLog = timeLog;
}
}
catch { } // Record found, but format cannot be parsed
}
});
}
finally
{
reader.Close();
}
if (AppData.SectionsList.Find(x => x.SectionID == tableStatus.SectionID).Winners == 1) // If a one-winner pairs movement, we also need to check the other direction
{
string otherDir;
switch (dir)
{
case "N":
otherDir = "E";
break;
case "S":
otherDir = "W";
break;
case "E":
otherDir = "N";
break;
case "W":
otherDir = "S";
break;
default:
otherDir = "";
break;
}
SQLString = $"SELECT Number, Name, Round, TimeLog FROM PlayerNumbers WHERE Section={tableStatus.SectionID} AND TabScorePairNo={pairNo} AND Direction='{otherDir}'";
cmd = new OdbcCommand(SQLString, conn);
try
{
ODBCRetryHelper.ODBCRetry(() =>
{
reader = cmd.ExecuteReader();
while (reader.Read())
{
try
{
int readerRoundNumber = reader.GetInt32(2);
DateTime timeLog;
if (reader.IsDBNull(3))
{
timeLog = new DateTime(2010, 1, 1);
}
else
{
timeLog = reader.GetDateTime(3);
}
if (readerRoundNumber <= tableStatus.RoundNumber && timeLog >= latestTimeLog)
{
number = reader.GetString(0);
name = reader.GetString(1);
latestTimeLog = timeLog;
}
}
catch { } // Record found, but format cannot be parsed
}
});
}
finally
{
reader.Close();
}
}
cmd.Dispose();
return(FormatName(name, number));
}
private static void CheckTabScorePairNos(OdbcConnection conn)
{
object queryResult = null;
// Check to see if TabScorePairNo exists (it may get overwritten if the scoring program recreates the PlayerNumbers table)
string SQLString = $"SELECT 1 FROM PlayerNumbers WHERE TabScorePairNo IS NULL";
OdbcCommand cmd1 = new OdbcCommand(SQLString, conn);
try
{
ODBCRetryHelper.ODBCRetry(() =>
{
queryResult = cmd1.ExecuteScalar();
});
}
finally
{
cmd1.Dispose();
}
if (queryResult != null)
{
// TabScorePairNo doesn't exist, so recreate it
SQLString = "SELECT Section, [Table], Direction, Round FROM PlayerNumbers";
OdbcCommand cmd2 = new OdbcCommand(SQLString, conn);
OdbcDataReader reader2 = null;
try
{
ODBCRetryHelper.ODBCRetry(() =>
{
reader2 = cmd2.ExecuteReader();
while (reader2.Read())
{
int tempSectionID = reader2.GetInt32(0);
int tempTable = reader2.GetInt32(1);
string tempDirection = reader2.GetString(2);
int tempRoundNumber = reader2.GetInt32(3);
int queryRoundNumber = tempRoundNumber;
if (queryRoundNumber == 0)
{
queryRoundNumber = 1;
}
if (AppData.IsIndividual)
{
switch (tempDirection)
{
case "N":
SQLString = $"SELECT NSPair FROM RoundData WHERE Section={tempSectionID} AND [Table]={tempTable} AND ROUND={queryRoundNumber}";
break;
case "S":
SQLString = $"SELECT South FROM RoundData WHERE Section={tempSectionID} AND [Table]={tempTable} AND ROUND={queryRoundNumber}";
break;
case "E":
SQLString = $"SELECT EWPair FROM RoundData WHERE Section={tempSectionID} AND [Table]={tempTable} AND ROUND={queryRoundNumber}";
break;
case "W":
SQLString = $"SELECT West FROM RoundData WHERE Section={tempSectionID} AND [Table]={tempTable} AND ROUND={queryRoundNumber}";
break;
}
}
else
{
switch (tempDirection)
{
case "N":
case "S":
SQLString = $"SELECT NSPair FROM RoundData WHERE Section={tempSectionID} AND [Table]={tempTable} AND ROUND={queryRoundNumber}";
break;
case "E":
case "W":
SQLString = $"SELECT EWPair FROM RoundData WHERE Section={tempSectionID} AND [Table]={tempTable} AND ROUND={queryRoundNumber}";
break;
}
}
OdbcCommand cmd3 = new OdbcCommand(SQLString, conn);
try
{
ODBCRetryHelper.ODBCRetry(() =>
{
queryResult = cmd3.ExecuteScalar();
});
}
finally
{
cmd3.Dispose();
}
string TSpairNo = queryResult.ToString();
SQLString = $"UPDATE PlayerNumbers SET TabScorePairNo={TSpairNo} WHERE Section={tempSectionID} AND [Table]={tempTable} AND Direction='{tempDirection}' AND Round={tempRoundNumber}";
OdbcCommand cmd4 = new OdbcCommand(SQLString, conn);
try
{
ODBCRetryHelper.ODBCRetry(() =>
{
cmd4.ExecuteNonQuery();
});
}
finally
{
cmd4.Dispose();
}
}
});
}
finally
{
reader2.Close();
cmd2.Dispose();
}
}
}
private static List <Ranking> CalculateIndividualRankingFromReceivedData(int sectionID)
{
List <Ranking> rankingList = new List <Ranking>();
List <Result> resList = new List <Result>();
using (OdbcConnection connection = new OdbcConnection(AppData.DBConnectionString))
{
connection.Open();
string SQLString = $"SELECT Board, PairNS, PairEW, South, West, [NS/EW], Contract, Result FROM ReceivedData WHERE Section={sectionID}";
OdbcCommand cmd = new OdbcCommand(SQLString, connection);
OdbcDataReader reader = null;
try
{
ODBCRetryHelper.ODBCRetry(() =>
{
reader = cmd.ExecuteReader();
while (reader.Read())
{
Result result = new Result()
{
BoardNumber = reader.GetInt32(0),
NumberNorth = reader.GetInt32(1),
NumberEast = reader.GetInt32(2),
NumberSouth = reader.GetInt32(3),
NumberWest = reader.GetInt32(4),
DeclarerNSEW = reader.GetString(5),
Contract = reader.GetString(6),
TricksTakenSymbol = reader.GetString(7)
};
if (result.Contract.Length > 2) // Testing for unplayed boards and corrupt ReceivedData table
{
result.CalculateScore();
resList.Add(result);
}
}
});
}
catch (OdbcException)
{
return(null);
}
finally
{
reader.Close();
cmd.Dispose();
}
}
// Calculate MPs
List <Result> currentBoardResultList = new List <Result>();
int currentBoard;
int currentScore;
foreach (Result result in resList)
{
currentScore = result.Score;
currentBoard = result.BoardNumber;
currentBoardResultList = resList.FindAll(x => x.BoardNumber == currentBoard);
result.MatchpointsNS = 2 * currentBoardResultList.FindAll(x => x.Score < currentScore).Count + currentBoardResultList.FindAll(x => x.Score == currentScore).Count - 1;
result.MatchpointsMax = 2 * currentBoardResultList.Count - 2;
result.MatchpointsEW = result.MatchpointsMax - result.MatchpointsNS;
}
// Add up MPs for each pair, creating Ranking List entries as we go
foreach (Result result in resList)
{
Ranking rankingListFind = rankingList.Find(x => x.PairNo == result.NumberNorth);
if (rankingListFind == null)
{
Ranking ranking = new Ranking()
{
PairNo = result.NumberNorth,
Orientation = "0",
MP = result.MatchpointsNS,
MPMax = result.MatchpointsMax
};
rankingList.Add(ranking);
}
else
{
rankingListFind.MP += result.MatchpointsNS;
rankingListFind.MPMax += result.MatchpointsMax;
}
rankingListFind = rankingList.Find(x => x.PairNo == result.NumberEast);
if (rankingListFind == null)
{
Ranking ranking = new Ranking()
{
PairNo = result.NumberEast,
Orientation = "0",
MP = result.MatchpointsEW,
MPMax = result.MatchpointsMax
};
rankingList.Add(ranking);
}
else
{
rankingListFind.MP += result.MatchpointsEW;
rankingListFind.MPMax += result.MatchpointsMax;
}
rankingListFind = rankingList.Find(x => x.PairNo == result.NumberSouth);
if (rankingListFind == null)
{
Ranking ranking = new Ranking()
{
PairNo = result.NumberSouth,
Orientation = "0",
MP = result.MatchpointsNS,
MPMax = result.MatchpointsMax
};
rankingList.Add(ranking);
}
else
{
rankingListFind.MP += result.MatchpointsNS;
rankingListFind.MPMax += result.MatchpointsMax;
}
rankingListFind = rankingList.Find(x => x.PairNo == result.NumberWest);
if (rankingListFind == null)
{
Ranking ranking = new Ranking()
{
PairNo = result.NumberWest,
Orientation = "0",
MP = result.MatchpointsEW,
MPMax = result.MatchpointsMax
};
rankingList.Add(ranking);
}
else
{
rankingListFind.MP += result.MatchpointsEW;
rankingListFind.MPMax += result.MatchpointsMax;
}
}
// Calculate percentages
foreach (Ranking ranking in rankingList)
{
if (ranking.MPMax == 0)
{
ranking.ScoreDecimal = 50.0;
}
else
{
ranking.ScoreDecimal = 100.0 * ranking.MP / ranking.MPMax;
}
ranking.Score = ranking.ScoreDecimal.ToString("0.##");
}
// Calculate ranking
rankingList.Sort((x, y) => y.ScoreDecimal.CompareTo(x.ScoreDecimal));
foreach (Ranking ranking in rankingList)
{
double currentScoreDecimal = ranking.ScoreDecimal;
int rank = rankingList.FindAll(x => x.ScoreDecimal > currentScoreDecimal).Count + 1;
ranking.Rank = rank.ToString();
if (rankingList.FindAll(x => x.ScoreDecimal == currentScoreDecimal).Count > 1)
{
ranking.Rank += "=";
}
}
return(rankingList);
}
public RankingList(int tabletDeviceNumber)
{
TabletDeviceNumber = tabletDeviceNumber;
TabletDeviceStatus tabletDeviceStatus = AppData.TabletDeviceStatusList[tabletDeviceNumber];
RoundNumber = tabletDeviceStatus.RoundNumber;
// Set player numbers to highlight appropriate rows of ranking list
if (AppData.SectionsList.Find(x => x.SectionID == tabletDeviceStatus.SectionID).TabletDevicesPerTable == 1)
{
TableStatus tableStatus = AppData.TableStatusList.Find(x => x.SectionID == tabletDeviceStatus.SectionID && x.TableNumber == tabletDeviceStatus.TableNumber);
NumberNorth = tableStatus.RoundData.NumberNorth;
NumberEast = tableStatus.RoundData.NumberEast;
NumberSouth = tableStatus.RoundData.NumberSouth;
NumberWest = tableStatus.RoundData.NumberWest;
}
else // More than one tablet device per table
{
// Only need to highlight one row entry, so use NumberNorth as proxy
NumberNorth = tabletDeviceStatus.PairNumber;
}
using (OdbcConnection connection = new OdbcConnection(AppData.DBConnectionString))
{
connection.Open();
string SQLString = $"SELECT Orientation, Number, Score, Rank FROM Results WHERE Section={tabletDeviceStatus.SectionID}";
OdbcCommand cmd = new OdbcCommand(SQLString, connection);
OdbcDataReader reader1 = null;
try
{
ODBCRetryHelper.ODBCRetry(() =>
{
reader1 = cmd.ExecuteReader();
while (reader1.Read())
{
Ranking ranking = new Ranking
{
Orientation = reader1.GetString(0),
PairNo = reader1.GetInt32(1),
Score = reader1.GetString(2),
Rank = reader1.GetString(3)
};
ranking.ScoreDecimal = Convert.ToDouble(ranking.Score);
Add(ranking);
}
});
}
catch (OdbcException e)
{
if (e.Errors.Count > 1 || e.Errors[0].SQLState != "42S02") // Any error other than results table doesn't exist
{
throw (e);
}
}
finally
{
reader1.Close();
cmd.Dispose();
}
if (Count == 0) // Results table either doesn't exist or contains no entries, so try to calculate rankings
{
if (AppData.IsIndividual)
{
InsertRange(0, CalculateIndividualRankingFromReceivedData(tabletDeviceStatus.SectionID));
}
else
{
InsertRange(0, CalculateRankingFromReceivedData(tabletDeviceStatus.SectionID));
}
}
// Make sure that ranking list is sorted into presentation order
Sort((x, y) =>
{
int sortValue = y.Orientation.CompareTo(x.Orientation); // N's first then E's
if (sortValue == 0)
{
sortValue = y.ScoreDecimal.CompareTo(x.ScoreDecimal);
}
if (sortValue == 0)
{
sortValue = x.PairNo.CompareTo(y.PairNo);
}
return(sortValue);
});
}
}
public ResultsList(int tabletDeviceNumber, TableStatus tableStatus)
{
TabletDeviceNumber = tabletDeviceNumber;
GotAllResults = true;
ShowViewButton = Settings.ShowResults;
using (OdbcConnection connection = new OdbcConnection(AppData.DBConnectionString))
{
connection.Open();
string SQLString;
OdbcCommand cmd = null;
OdbcDataReader reader = null;
try
{
SQLString = $"SELECT Board, [NS/EW], Contract, Result, Remarks FROM ReceivedData WHERE Section={tableStatus.SectionID} AND [Table]={tableStatus.TableNumber} AND Round={tableStatus.RoundNumber} AND Board>={tableStatus.RoundData.LowBoard} AND Board<={tableStatus.RoundData.HighBoard}";
cmd = new OdbcCommand(SQLString, connection);
ODBCRetryHelper.ODBCRetry(() =>
{
reader = cmd.ExecuteReader();
while (reader.Read())
{
Result result = new Result()
{
BoardNumber = reader.GetInt32(0)
};
if (reader.GetString(4) == "Not played")
{
result.ContractLevel = -1;
result.ContractSuit = "";
result.ContractX = "";
result.DeclarerNSEW = "";
result.TricksTakenNumber = -1;
}
else
{
string temp = reader.GetString(2);
result.Contract = temp; // Sets ContractLevel, etc
if (result.ContractLevel == 0) // Passed out
{
result.DeclarerNSEW = "";
result.TricksTakenNumber = -1;
}
else
{
result.DeclarerNSEW = reader.GetString(1);
result.TricksTakenSymbol = reader.GetString(3);
}
}
Add(result);
}
});
}
finally
{
reader.Close();
cmd.Dispose();
}
// Check to see if any boards don't have a result, and add dummies to the list
for (int iBoard = tableStatus.RoundData.LowBoard; iBoard <= tableStatus.RoundData.HighBoard; iBoard++)
{
if (Find(x => x.BoardNumber == iBoard) == null)
{
GotAllResults = false;
Result result = new Result
{
BoardNumber = iBoard,
ContractLevel = -999,
ContractSuit = "",
ContractX = "",
DeclarerNSEW = "",
TricksTakenNumber = -1,
LeadCard = ""
};
Add(result);
}
}
Sort((x, y) => x.BoardNumber.CompareTo(y.BoardNumber));
}
}
public static void Refresh()
{
if (File.Exists(PathToTabScoreDB))
{
// Only do an update if TabScoreStarter has updated TabScoreDB.txt - so complete restart required
DateTime lastWriteTime = File.GetLastWriteTime(PathToTabScoreDB);
if (lastWriteTime > TabScoreDBTime)
{
// Clear table status list and tablet device status list - all tablets will need to re-register
TableStatusList.Clear();
TabletDeviceStatusList.Clear();
DBConnectionString = File.ReadAllText(PathToTabScoreDB);
TabScoreDBTime = lastWriteTime;
if (DBConnectionString != "")
{
using (OdbcConnection connection = new OdbcConnection(DBConnectionString))
{
connection.Open();
// Check if new event is an individual (in which case there will be a field 'South' in the RoundData table)
string SQLString = $"SELECT TOP 1 South FROM RoundData";
OdbcCommand cmd = new OdbcCommand(SQLString, connection);
try
{
ODBCRetryHelper.ODBCRetry(() =>
{
cmd.ExecuteScalar();
IsIndividual = true;
});
}
catch (OdbcException e)
{
if (e.Errors.Count > 1 || e.Errors[0].SQLState != "07002") // Error other than field 'South' doesn't exist
{
throw (e);
}
else
{
IsIndividual = false;
}
}
finally
{
cmd.Dispose();
}
// Create list of sections
SQLString = "SELECT ID, Letter, Tables, MissingPair, ScoringType, Winners FROM Section";
SectionsList.Clear();
cmd = new OdbcCommand(SQLString, connection);
OdbcDataReader reader = null;
try
{
ODBCRetryHelper.ODBCRetry(() =>
{
reader = cmd.ExecuteReader();
while (reader.Read())
{
Section s = new Section
{
SectionID = reader.GetInt32(0),
SectionLetter = reader.GetString(1),
NumTables = reader.GetInt32(2),
MissingPair = reader.GetInt32(3),
ScoringType = reader.GetInt32(4),
Winners = reader.GetInt32(5)
};
SectionsList.Add(s);
}
});
}
finally
{
reader.Close();
cmd.Dispose();
}
// Retrieve global PlayerNames table
SQLString = $"SELECT Name, ID FROM PlayerNames";
PlayerNamesTable.Clear();
cmd = new OdbcCommand(SQLString, connection);
try
{
ODBCRetryHelper.ODBCRetry(() =>
{
reader = cmd.ExecuteReader();
while (reader.Read())
{
PlayerRecord playerRecord = new PlayerRecord
{
Name = reader.GetString(0),
Number = reader.GetInt32(1)
};
PlayerNamesTable.Add(playerRecord);
}
;
});
}
catch (OdbcException e)
{
if (e.Errors.Count > 1 || e.Errors[0].SQLState != "42S02") // Error other than PlayerNames table does not exist
{
throw (e);
}
}
finally
{
reader.Close();
cmd.Dispose();
}
}
}
}
}
}
