///<summary>NO LONGER USED.
///Leaving function here in case we want to reuse the code in future. We only support connecting to archive DB directly.</summary>
public static T RunFuncOnArchiveDatabase <T>(Func <T> f)
{
//Makes a connection to the archive database, validates that the version of the database is the same as the current database version,
//executes the func passed in, and then sets the connection back to the original database before returning the results of the func passed in.
//Optionally pass in connection settings to override the archive preferences. Throws exceptions.
if (RemotingClient.RemotingRole.In(RemotingRole.ClientWeb, RemotingRole.ServerWeb)) //May already be behind a remoting role check.
//This method will eventually invoke SetDB() which is unacceptable for the Middle Tier.
{
throw new ApplicationException(Lans.g(nameof(MiscData), "Archive databases are not available when using a Middle Tier connection.") + "\r\n" +
Lans.g(nameof(MiscData), "Archive databases may only be created or accessed on a direct database connection."));
}
string connectionStrOrig = DataConnection.GetCurrentConnectionString();
DatabaseType dbTypeOrig = DataConnection.DBtype;
DataConnection dcon = new DataConnection();
try {
//Keep track of the original connection settings so that we can revert back to them once finished archiving.
Version versionDbOrig = new Version(PrefC.GetString(PrefName.DataBaseVersion));
string archiveServerName = PrefC.GetString(PrefName.ArchiveServerName);
string archiveUserName = PrefC.GetString(PrefName.ArchiveUserName);
string decryptedPass;
CDT.Class1.Decrypt(PrefC.GetString(PrefName.ArchivePassHash), out decryptedPass);
//Connect to the archive database. This can throw many exceptions.
dcon.SetDb(archiveServerName, MiscData.GetArchiveDatabaseName(), archiveUserName, decryptedPass, "", "", dbTypeOrig);
#region Validate archive database version
//At this point there is an active connection to the archive database, validate the DataBaseVersion.
string version = PrefC.GetStringNoCache(PrefName.DataBaseVersion);
if (string.IsNullOrEmpty(version))
{
//Preference table does not have version information. Somehow they have a database with proper structure but no data.
//This archive database can't be trusted and we have no idea what version the schema is at.
//They need to call support so that we can take a look or they need to delete the invalid archive (or remove it from the data dir)
//so that a new archive database can be made from scratch.
throw new ApplicationException("Invalid archive database detected.");
}
Version versionDbArchive = new Version(version);
if (versionDbOrig > versionDbArchive)
{
//The archive database needs to be updated before funcs can be invoked against it.
throw new ApplicationException("Archive database is at a lower version than the current database."
+ " Run the Archive tool in order to update the database.");
}
else if (versionDbArchive > versionDbOrig)
{
throw new ApplicationException("Archive database version is higher than the current database. Process cannot continue.");
}
#endregion
//Invoke the func passed in.
return(f());
}
finally { //Always put the connection back to the original no matter what happened above when trying to make an archive.
dcon.SetDb(connectionStrOrig, "", dbTypeOrig); //It is acceptable to crash the program if this fails.
}
}
///<summary>Returns true if it is time to retrieve reports.</summary>
private static bool IsTimeToRetrieveReports(bool isAutomaticMode, out string errorMessage, IODProgressExtended progress = null)
{
progress = progress ?? new ODProgressExtendedNull();
DateTime timeLastReport = PIn.DateT(PrefC.GetStringNoCache(PrefName.ClaimReportReceiveLastDateTime));
double timeReceiveInternal = PIn.Double(PrefC.GetStringNoCache(PrefName.ClaimReportReceiveInterval)); //Interval in minutes.
DateTime timeToRecieve = DateTime.Now.Date + PrefC.GetDateT(PrefName.ClaimReportReceiveTime).TimeOfDay;
double timeDiff = DateTime.Now.Subtract(timeLastReport).TotalMinutes;
errorMessage = "";
if (isAutomaticMode)
{
if (timeReceiveInternal != 0) //preference is set instead of pref for specific time.
{
if (timeDiff < timeReceiveInternal)
{
//Automatically retrieving reports from this computer and the report interval has not passed yet.
return(false);
}
}
else //pref is set for specific time, not interval
{
if (DateTime.Now.TimeOfDay < timeToRecieve.TimeOfDay || //We haven't reach to the time to retrieve
timeLastReport.Date == DateTime.Today) //Or we have already retrieved today
{
//Automatically retrieving reports and the time has not come to pass yet
return(false);
}
}
}
else if (timeDiff < 1)
{
//When the user presses the Get Reports button manually we allow them to get reports up to once per minute
errorMessage = Lans.g(progress.LanThis, "Reports can only be retrieved once per minute.");
progress.UpdateProgress(Lans.g(progress.LanThis, "Reports can only be retrieved once per minute. Attempting to import manually downloaded reports."));
return(false);
}
return(true);
}
///<summary>Backs up the database to the same directory as the original just in case the user did not have sense enough to do a backup first.
///Does not work for Oracle, due to some MySQL specific commands inside.</summary>
public static void MakeABackup(string serverName = "", string user = "", string pass = "", bool doVerify = false)
{
//This function should always make the backup on the server itself, and since no directories are
//referred to (all handled with MySQL), this function will always be referred to the server from
//client machines.
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), serverName, user, pass, doVerify);
return;
}
//UpdateStreamLinePassword is purposefully named poorly and used in an odd fashion to sort of obfuscate it from our users.
//GetStringNoCache() will return blank if pref does not exist.
if (PrefC.GetStringNoCache(PrefName.UpdateStreamLinePassword) == "abracadabra")
{
return;
}
string currentServerName = DataConnection.GetServerName().ToLower();
bool useSameServer = string.IsNullOrWhiteSpace(serverName) || currentServerName.Equals(serverName, StringComparison.CurrentCultureIgnoreCase);
if (!string.IsNullOrWhiteSpace(serverName) && currentServerName == "localhost" && serverName.ToLower() != "localhost") //there could be a mismatch but technically the same server
{
useSameServer = serverName.Equals(Environment.MachineName, StringComparison.CurrentCultureIgnoreCase);
}
if (serverName.ToLower() == "localhost" && currentServerName != "localhost") //there could be a mismatch but technically the same server
{
useSameServer = currentServerName.Equals(Environment.MachineName, StringComparison.CurrentCultureIgnoreCase);
}
//only used in two places: upgrading version, and upgrading mysql version.
//Both places check first to make sure user is using mysql.
//we have to be careful to throw an exception if the backup is failing.
DataConnection dcon = new DataConnection();
//if they provided a different server where they want their backup to be, we need a separate connection for that
DataConnection dconBackupServer = useSameServer ? new DataConnection() : new DataConnection(serverName, "", user, pass, DatabaseType.MySql);
//Check that the backup server does not already contain this database
string command = "SELECT database()";
DataTable table = dcon.GetTable(command);
string oldDb = PIn.String(table.Rows[0][0].ToString());
string newDb = oldDb + "backup_" + DateTime.Today.ToString("MM_dd_yyyy");
command = "SHOW DATABASES";
table = dconBackupServer.GetTable(command);
string[] databases = new string[table.Rows.Count];
for (int i = 0; i < table.Rows.Count; i++)
{
databases[i] = table.Rows[i][0].ToString();
}
if (Contains(databases, newDb)) //if the new database name already exists
//find a unique one
{
int uniqueID = 1;
string originalNewDb = newDb;
do
{
newDb = originalNewDb + "_" + uniqueID.ToString();
uniqueID++;
}while(Contains(databases, newDb));
}
command = "CREATE DATABASE `" + newDb + "` CHARACTER SET utf8";
dconBackupServer.NonQ(command); //create the backup db on the backup server
//But get the tables from the current, not the backup server
command = "SHOW FULL TABLES WHERE Table_type='BASE TABLE'"; //Tables, not views. Does not work in MySQL 4.1, however we test for MySQL version >= 5.0 in PrefL.
table = dcon.GetTable(command);
//Set the connection to the new database now that it has been created
dconBackupServer = useSameServer?new DataConnection(newDb):new DataConnection(serverName, newDb, user, pass, DatabaseType.MySql);
foreach (DataRow row in table.Rows)
{
string tableName = row[0].ToString();
//First create the table on the new db
MiscDataEvent.Fire(ODEventType.MiscData, $"Backing up table: {tableName}");
//also works with views. Added backticks around table name for unusual characters.
command = $"SHOW CREATE TABLE `{oldDb}`.`{tableName}`";
DataTable dtCreate = dcon.GetTable(command);
command = PIn.ByteArray(dtCreate.Rows[0][1]);
//The backup database tables will be MyISAM because it is significantly faster at doing bulk inserts.
command = command.Replace("ENGINE=InnoDB", "ENGINE=MyISAM");
dconBackupServer.NonQ(command);
//Then copy the data into the new table
if (useSameServer)
{
//If on the same server we can select into directly, which is faster
command = $"INSERT INTO `{newDb}`.`{tableName}` SELECT * FROM `{oldDb}`.`{tableName}`"; //Added backticks around table name for unusual characters.
dconBackupServer.NonQ(command);
}
else
{
long count = PIn.Long(dcon.GetCount($"SELECT COUNT(*) FROM `{oldDb}`.`{tableName}`"));
int limit = 10000;
if (tableName == "documentmisc") //This table can have really large rows so just to be safe, handle the backup one row at a time
{
limit = 1;
}
int offset = 0;
while (count > offset)
{
DataTable dtOld = dcon.GetTable($" SELECT * FROM `{oldDb}`.`{tableName}` LIMIT {limit} OFFSET {offset}");
offset += dtOld.Rows.Count;
dconBackupServer.BulkCopy(dtOld, tableName);
}
}
}
//Verify that the old database and the new backup have the same number of rows
if (doVerify)
{
List <string> listTablesFailed = new List <string>();
foreach (DataRow dbTable in table.Rows)
{
string tableName = dbTable[0].ToString();
MiscDataEvent.Fire(ODEventType.MiscData, $"Verifying backup: {tableName}");
int ctOld = PIn.Int(dcon.GetCount($"SELECT COUNT(*) FROM `{oldDb}`.`{tableName}`"));
int ctNew = PIn.Int(dconBackupServer.GetCount($"SELECT COUNT(*) FROM `{newDb}`.`{tableName}`"));
if (ctOld != ctNew)
{
listTablesFailed.Add(tableName);
}
}
if (listTablesFailed.Count > 0)
{
throw new Exception($@"Failed to create database backup because the following tables contained a different number of rows than expected:
{string.Join(", ",listTablesFailed)}." );
}
}
}
public static DataTable GetData(long patNum, bool isFamily, bool isDetailed)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), patNum, isFamily, isDetailed));
}
int payPlanVersion = PIn.Int(PrefC.GetStringNoCache(PrefName.PayPlansVersion));
if (payPlanVersion == 0)
{
payPlanVersion = 1;
}
string command = $@"
/*top layer gets final columns for display*/
SELECT
IF(display.TranDate != '', '',display.ProcDate) AS 'Date',
display.TranDate AS 'Trans Date',
display.Patient,
display.Reference,
display.Charge,
display.Credit,
display.Pvdr,
display.InsBal,
display.AcctBal,
display.Type
FROM(
/*cases out data from rows that we need, performing aggregate functions for day and overall totals.*/
SELECT
dup.Num,
IF(dup.Num = 2, 0,core.ProcNum) AS ProcNum,
(CASE dup.Num
WHEN 1
THEN IF(core.Type IN ('Proc','Adj-Att.','PatPay Att.','WriteOff-Att.','InsPay-Att.','PayPlan Charge Att.','PatPay Att. PayPlan','Unallocated'),1,2) -- get allocated items ordered before unallocated ones
WHEN 2
THEN IF(core.Type = 'Unallocated',1,2)
WHEN 3
THEN 4
WHEN 4
THEN 5
END) AS OrderingNum,
(CASE dup.Num
WHEN 1
THEN core.Type
WHEN 2
THEN 'Unallocated'
WHEN 3
THEN 'Day Total'
WHEN 4
THEN 'Overall Total'
END) AS 'Type',
(CASE dup.Num
WHEN 1
THEN core.ProcDate
WHEN 2
THEN core.ProcDate
WHEN 3
THEN core.ProcDate
WHEN 4
THEN NULL
END) AS 'ProcDate',
(CASE dup.Num
WHEN 1
THEN IF(core.Type = 'Proc', '',core.TranDate)
WHEN 2
THEN ''
WHEN 3
THEN ''
WHEN 4
THEN NULL
END) AS 'TranDate',
(CASE dup.Num
WHEN 1
THEN core.Patient
WHEN 2
THEN '' -- intentionaly blank
WHEN 3
THEN ''
WHEN 4
THEN '' -- intentionally blank
END) AS 'Patient',
(CASE dup.Num
WHEN 1
THEN IF(core.Type IN ('Adj-Att.','PatPay Att.','WriteOff-Att.','InsPay-Att.','PayPlan Charge Att.','PatPay Att. PayPlan'),CONCAT(' ',core.Reference),core.Reference)
WHEN 2
THEN 'Unallocated Trans'
WHEN 3
THEN CONCAT(REPEAT(' ',2),'Total for Date')
WHEN 4
THEN {(isFamily ? "'Family Total:'" : "'Patient Total:'" )}
END) AS 'Reference',
(CASE dup.Num
WHEN 1
THEN FORMAT(SUM(core.Charge),2)
WHEN 2
THEN '' -- intentionaly blank
WHEN 3
THEN FORMAT(SUM(core.Charge),2) -- total for date
WHEN 4
THEN FORMAT(SUM(core.Charge),2) -- overall total
END) AS 'Charge',
(CASE dup.Num
WHEN 1
THEN FORMAT(SUM(core.Credit),2)
WHEN 2
THEN '' -- intentionaly blank
WHEN 3
THEN FORMAT(SUM(core.Credit),2) -- total for date
WHEN 4
THEN FORMAT(SUM(core.Credit),2) -- overall total
END) AS 'Credit',
(CASE dup.Num
WHEN 1
THEN core.Prov
WHEN 2
THEN '' -- intentionaly blank
WHEN 3
THEN '' -- intentionally blank
WHEN 4
THEN '' -- intentionally blank
END) AS 'Pvdr',
(CASE dup.Num
WHEN 1
THEN {(isDetailed ? "IF(core.Type = 'Proc', FORMAT(core.InsCredits,2),'')": "''")}
WHEN 2
THEN '' -- intentionally blank
WHEN 3
THEN FORMAT(SUM(IF(core.Type = 'Proc', core.InsCredits,0)),2) -- total for date
WHEN 4
THEN FORMAT(SUM(IF(core.Type = 'Proc', core.InsCredits,0)),2) -- overall total
END) AS 'InsBal',
(CASE dup.Num
WHEN 1
THEN {(isDetailed ? "IF(core.Type = 'Proc', FORMAT(core.Charge - (core.ProcCredits + core.InsCredits),2),'')" : "''")}
WHEN 2
THEN '' -- intentionally blank
WHEN 3
THEN FORMAT(SUM(core.Charge - core.Credit - core.InsCredits),2) -- total for date
WHEN 4
THEN FORMAT(SUM(core.Charge - core.Credit - core.InsCredits),2) -- overall total
END) AS 'AcctBal'
FROM(
{GetCoreQuery(patNum,isFamily,payPlanVersion)}
) core
INNER JOIN(
/*add 2 extra rows to every transaction so we can use the data more than once for per day and overall totals without running the query again*/
SELECT 1 AS Num
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
) dup
ON IF(dup.Num = 2 AND (core.Type LIKE '%Att.' || core.Type LIKE 'Proc'),FALSE,TRUE) -- only join on the 2 row when there are unallocated transactions on a day.
GROUP BY (CASE WHEN dup.Num = 1 -- per transaction
THEN CONCAT(core.TranNum,'|',core.ProcDate,'|',core.Type) -- unique identifier for transaction rows
WHEN dup.Num IN (2,3) -- per date of service, including an extra row per date for unallocated transactions identifier
THEN CONCAT(dup.Num,'-',core.ProcDate) -- unique identifier for date of service row
WHEN dup.Num = 4 -- ending total for family/patient
THEN dup.Num -- only need to have one row here
END)
) display
/*ensure final order is what we need it to be*/
ORDER BY
ISNULL(display.ProcDate),
display.ProcDate,
display.OrderingNum,
display.Patient,
display.ProcNum DESC,
display.TranDate,
FIELD(display.Type,'Proc','Adj-Att.','PatPay Att.','WriteOff-Att.','InsPay-Att.','PayPlan Charge Att.','PatPay Att. PayPlan','Unallocated','PatPay','WriteOff','Adj','InsPay','PayPlan Credit','PayPlan Charge','PatPay PayPlan','Day Total','Overall Total')
" ;
return(ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command)));
}
///<summary>Backs up the database to the same directory as the original just in case the user did not have sense enough to do a backup first.
///Does not work for Oracle, due to some MySQL specific commands inside.</summary>
public static long MakeABackup()
{
//This function should always make the backup on the server itself, and since no directories are
//referred to (all handled with MySQL), this function will always be referred to the server from
//client machines.
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetLong(MethodBase.GetCurrentMethod()));
}
//UpdateStreamLinePassword is purposefully named poorly and used in an odd fashion to sort of obfuscate it from our users.
//GetStringNoCache() will return blank if pref does not exist.
if (PrefC.GetStringNoCache(PrefName.UpdateStreamLinePassword) == "abracadabra")
{
return(0);
}
//only used in two places: upgrading version, and upgrading mysql version.
//Both places check first to make sure user is using mysql.
//we have to be careful to throw an exception if the backup is failing.
DataConnection dcon = new DataConnection();
string command = "SELECT database()";
DataTable table = dcon.GetTable(command);
string oldDb = PIn.String(table.Rows[0][0].ToString());
string newDb = oldDb + "backup_" + DateTime.Today.ToString("MM_dd_yyyy");
command = "SHOW DATABASES";
table = dcon.GetTable(command);
string[] databases = new string[table.Rows.Count];
for (int i = 0; i < table.Rows.Count; i++)
{
databases[i] = table.Rows[i][0].ToString();
}
if (Contains(databases, newDb)) //if the new database name already exists
//find a unique one
{
int uniqueID = 1;
string originalNewDb = newDb;
do
{
newDb = originalNewDb + "_" + uniqueID.ToString();
uniqueID++;
}while(Contains(databases, newDb));
}
command = "CREATE DATABASE `" + newDb + "` CHARACTER SET utf8";
dcon.NonQ(command);
command = "SHOW FULL TABLES WHERE Table_type='BASE TABLE'"; //Tables, not views. Does not work in MySQL 4.1, however we test for MySQL version >= 5.0 in PrefL.
table = dcon.GetTable(command);
string[] tableName = new string[table.Rows.Count];
for (int i = 0; i < table.Rows.Count; i++)
{
tableName[i] = table.Rows[i][0].ToString();
}
//switch to using the new database
DataConnection newDcon = new DataConnection(newDb);
for (int i = 0; i < tableName.Length; i++)
{
//Alert anyone that cares that we are backing up this table.
ODEvent.Fire(new ODEventArgs("BackupProgress", Lans.g("MiscData", "Backing up table") + ": " + tableName[i]));
command = "SHOW CREATE TABLE `" + oldDb + "`.`" + tableName[i] + "`"; //also works with views. Added backticks around table name for unusual characters.
table = newDcon.GetTable(command);
command = PIn.ByteArray(table.Rows[0][1]);
newDcon.NonQ(command); //this has to be run using connection with new database
command = "INSERT INTO `" + newDb + "`.`" + tableName[i] + "` "
+ "SELECT * FROM `" + oldDb + "`.`" + tableName[i] + "`"; //Added backticks around table name for unusual characters.
newDcon.NonQ(command);
}
return(0);
}
