///<summary>Returns 0 if the extension could not be found.</summary>
public static int GetExtForComputer(string computerName)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetInt(MethodBase.GetCurrentMethod(), computerName));
}
string command = "SELECT PhoneExt FROM phonecomp WHERE ComputerName='" + POut.String(computerName) + "'";
return(Db.GetInt(command));
}
/// <summary>Gets the maximum Terminal Num for the selected patient. Returns 0 if there's no sheets marked to show in terminal.</summary>
public static int GetMaxTerminalNum(long patNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetInt(MethodBase.GetCurrentMethod(), patNum));
}
string command = "SELECT MAX(ShowInTerminal) FROM sheet WHERE PatNum=" + POut.Long(patNum)
+ " AND IsDeleted=0";
return(Db.GetInt(command));
}
///<summary>Using the smaller of max_allowed_packet or INSERT_MAX_BYTES and the max row length for the table with _tableName, returns the max
///number of rows per insert statement. Number returned is rounded down to the nearest thousand, i.e. 15,324.2341 rounds down to 15,000.
///Maximum of 25000 is returned in order to prevent excessive memory usage.</summary>
public static void SetRowsPerBatch()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod());
return;
}
//The SHOW command is used because it was able to run with a user that had no permissions whatsoever.
string command = "SHOW GLOBAL VARIABLES WHERE Variable_name='max_allowed_packet'";
DataTable table = Db.GetTable(command);
int maxAllowedPacket = 0;
if (table.Rows.Count > 0)
{
maxAllowedPacket = PIn.Int(table.Rows[0]["Value"].ToString());
}
maxAllowedPacket = Math.Min(INSERT_MAX_BYTES, maxAllowedPacket);
command = "SELECT TRUNCATE(" + POut.Int(maxAllowedPacket) + "/"//TRUNCATE(x,-3) eliminates anything after the thousands place, i.e. 25765.235412 => 25000
+ "SUM(CASE DATA_TYPE "
+ "WHEN 'tinyint' THEN 1 "
+ "WHEN 'smallint' THEN 2 "
+ "WHEN 'int' THEN 4 "
+ "WHEN 'bigint' THEN 8 "
+ "WHEN 'float' THEN IF(NUMERIC_PRECISION<=24,4,8) "
+ "WHEN 'double' THEN 8 "
+ "WHEN 'decimal' THEN FLOOR((NUMERIC_PRECISION-NUMERIC_SCALE)/9)*4+ROUND((NUMERIC_PRECISION-NUMERIC_SCALE)%9/2) "
+ "+FLOOR(NUMERIC_SCALE/9)*4+ROUND(NUMERIC_SCALE%9/2) "
+ "WHEN 'date' THEN 3 "
+ "WHEN 'time' THEN 3 "
+ "WHEN 'datetime' THEN 8 "
+ "WHEN 'timestamp' THEN 4 "
+ "WHEN 'longblob' THEN 12 "
+ "WHEN 'longtext' THEN 12 "
+ "WHEN 'mediumtext' THEN 11 "
+ "WHEN 'text' THEN 10 "
+ "WHEN 'varchar' THEN CHARACTER_OCTET_LENGTH+IF(CHARACTER_OCTET_LENGTH>255,2,1) "
+ "WHEN 'char' THEN CHARACTER_OCTET_LENGTH+IF(CHARACTER_OCTET_LENGTH>255,2,1) "
+ "ELSE 8 END),-3) AS maxNumRows "
+ "FROM information_schema.Columns c "
+ "WHERE c.TABLE_SCHEMA='" + POut.String(GetCurrentDatabase()) + "' "
+ "AND c.TABLE_NAME='" + POut.String(_tableName) + "'";
//Some extremely large tables get chopped up into huge batches (e.g. 645,000 items) and Open Dental starts to run out of memory when creating
//each individual insert statement. The easiest solution is to arbitrarily limit the batches to a maximum number of items. This does not
//affect the time it takes to execute all of these insert statements because we are going to run them all in parallel regardless.
_rowsPerBatch = Math.Min(Db.GetInt(command), 25000);
}
///<summary>Using the smaller of max_allowed_packet or INSERT_MAX_BYTES and the max row length for the table with _tableName, returns the max
///number of rows per insert statement. Number returned is rounded down to the nearest thousand, i.e. 15,324.2341 rounds down to 15,000.</summary>
public static void SetRowsPerBatch()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod());
return;
}
//The SHOW command is used because it was able to run with a user that had no permissions whatsoever.
string command = "SHOW GLOBAL VARIABLES WHERE Variable_name='max_allowed_packet'";
DataTable table = Db.GetTable(command);
int maxAllowedPacket = 0;
if (table.Rows.Count > 0)
{
maxAllowedPacket = PIn.Int(table.Rows[0]["Value"].ToString());
}
maxAllowedPacket = Math.Min(INSERT_MAX_BYTES, maxAllowedPacket);
command = "SELECT TRUNCATE(" + POut.Int(maxAllowedPacket) + "/"//TRUNCATE(x,-3) eliminates anything after the thousands place, i.e. 25765.235412 => 25000
+ "SUM(CASE DATA_TYPE "
+ "WHEN 'tinyint' THEN 1 "
+ "WHEN 'smallint' THEN 2 "
+ "WHEN 'int' THEN 4 "
+ "WHEN 'bigint' THEN 8 "
+ "WHEN 'float' THEN IF(NUMERIC_PRECISION<=24,4,8) "
+ "WHEN 'double' THEN 8 "
+ "WHEN 'decimal' THEN FLOOR((NUMERIC_PRECISION-NUMERIC_SCALE)/9)*4+ROUND((NUMERIC_PRECISION-NUMERIC_SCALE)%9/2) "
+ "+FLOOR(NUMERIC_SCALE/9)*4+ROUND(NUMERIC_SCALE%9/2) "
+ "WHEN 'date' THEN 3 "
+ "WHEN 'time' THEN 3 "
+ "WHEN 'datetime' THEN 8 "
+ "WHEN 'timestamp' THEN 4 "
+ "WHEN 'longblob' THEN 12 "
+ "WHEN 'longtext' THEN 12 "
+ "WHEN 'mediumtext' THEN 11 "
+ "WHEN 'text' THEN 10 "
+ "WHEN 'varchar' THEN CHARACTER_OCTET_LENGTH+IF(CHARACTER_OCTET_LENGTH>255,2,1) "
+ "WHEN 'char' THEN CHARACTER_OCTET_LENGTH+IF(CHARACTER_OCTET_LENGTH>255,2,1) "
+ "ELSE 8 END),-3) AS maxNumRows "
+ "FROM information_schema.Columns c "
+ "WHERE c.TABLE_SCHEMA='" + POut.String(GetCurrentDatabase()) + "' "
+ "AND c.TABLE_NAME='" + POut.String(_tableName) + "'";
_rowsPerBatch = Db.GetInt(command);
}