Esempio n. 1
0
        ///<summary>This does not actually delete the procedure, but just changes the status to deleted.  Throws exception if not allowed to delete.</summary>
        public static int Delete(int procNum)
        {
            //Test to see if any payment at all has been received for this proc
            string command = "SELECT COUNT(*) FROM claimproc WHERE ProcNum=" + POut.PInt(procNum)
                             + " AND InsPayAmt > 0 AND Status != " + POut.PInt((int)ClaimProcStatus.Preauth);
            DataConnection dcon = new DataConnection();

            if (dcon.GetCount(command) != "0")
            {
                throw new Exception(Lan.g("Procedures", "Not allowed to delete a procedure that is attached to a payment."));
            }
            //delete adjustments
            command = "DELETE FROM adjustment WHERE ProcNum='" + POut.PInt(procNum) + "'";
            dcon.NonQ(command);
            //delete claimprocs
            command = "DELETE from claimproc WHERE ProcNum = '" + POut.PInt(procNum) + "'";
            dcon.NonQ(command);
            //set the procedure deleted
            command = "UPDATE procedurelog SET ProcStatus = " + POut.PInt((int)ProcStat.D) + ", "
                      + "AptNum=0, "
                      + "PlannedAptNum=0 "
                      + "WHERE ProcNum = '" + POut.PInt(procNum) + "'";
            int rowsChanged = dcon.NonQ(command);

            //Recalls.Synch(ProcCur.PatNum);//later
            return(rowsChanged);
        }
Esempio n. 2
0
        private static bool KeyInUse(string tablename, string field, int keynum)
        {
            string         command = "SELECT COUNT(*) FROM " + tablename + " WHERE " + field + "=" + keynum.ToString();
            DataConnection dcon    = new DataConnection();

            if (dcon.GetCount(command) == "0")
            {
                return(false);
            }
            return(true);           //already in use
        }
Esempio n. 3
0
        ///<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)}."                    );
                }
            }
        }
Esempio n. 4
0
        ///<summary>This does not actually delete the procedure, but just changes the status to deleted.  Throws exception if not allowed to delete.</summary>
        public static int Delete(int procNum)
        {
            //Test to see if any payment at all has been received for this proc
            string command = "SELECT COUNT(*) FROM claimproc WHERE ProcNum=" + POut.PInt(procNum)
                             + " AND InsPayAmt > 0 AND Status != " + POut.PInt((int)ClaimProcStatus.Preauth);
            DataConnection dcon = new DataConnection();

            if (dcon.GetCount(command) != "0")
            {
                throw new Exception(Lan.g("Procedures", "Not allowed to delete a procedure that is attached to a payment."));
            }
            //delete adjustments
            command = "DELETE FROM adjustment WHERE ProcNum='" + POut.PInt(procNum) + "'";
            dcon.NonQ(command);
            //delete claimprocs
            command = "DELETE from claimproc WHERE ProcNum = '" + POut.PInt(procNum) + "'";
            dcon.NonQ(command);
            //resynch appointment description-------------------------------------------------------------------------------------
            command = "SELECT AptNum,PlannedAptNum FROM procedurelog WHERE ProcNum = " + POut.PInt(procNum);
            DataTable table         = dcon.GetTable(command);
            string    aptnum        = table.Rows[0][0].ToString();
            string    plannedaptnum = table.Rows[0][1].ToString();
            string    procdescript;

            if (aptnum != "0")
            {
                command = @"SELECT AbbrDesc FROM procedurecode,procedurelog
					WHERE procedurecode.CodeNum=procedurelog.CodeNum
					AND ProcNum != "                     + POut.PInt(procNum)
                          + " AND procedurelog.AptNum=" + aptnum;
                table        = dcon.GetTable(command);
                procdescript = "";
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    if (i > 0)
                    {
                        procdescript += ", ";
                    }
                    procdescript += table.Rows[i]["AbbrDesc"].ToString();
                }
                command = "UPDATE appointment SET ProcDescript='" + POut.PString(procdescript) + "' "
                          + "WHERE AptNum=" + aptnum;
                dcon.NonQ(command);
            }
            if (plannedaptnum != "0")
            {
                command = @"SELECT AbbrDesc FROM procedurecode,procedurelog
					WHERE procedurecode.CodeNum=procedurelog.CodeNum
					AND ProcNum != "                     + POut.PInt(procNum)
                          + " AND procedurelog.PlannedAptNum=" + plannedaptnum;
                table        = dcon.GetTable(command);
                procdescript = "";
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    if (i > 0)
                    {
                        procdescript += ", ";
                    }
                    procdescript += table.Rows[i]["AbbrDesc"].ToString();
                }
                command = "UPDATE appointment SET ProcDescript='" + POut.PString(procdescript) + "' "
                          + "WHERE NextAptNum=" + plannedaptnum;
                dcon.NonQ(command);
            }
            //set the procedure deleted-----------------------------------------------------------------------------------------
            command = "UPDATE procedurelog SET ProcStatus = " + POut.PInt((int)ProcStat.D) + ", "
                      + "AptNum=0, "
                      + "PlannedAptNum=0 "
                      + "WHERE ProcNum = '" + POut.PInt(procNum) + "'";
            int rowsChanged = dcon.NonQ(command);

            //Recalls.Synch(ProcCur.PatNum);//later
            return(rowsChanged);
        }