MyMcKesson/TomcatServer/PlutoServer.MSL/Connectors/DBCache.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Sdaleo;
using System.Net;
using Yaulw.Xml;
using Yaulw.File;
using CrossProduct.Core;
using System.Data;
using Sdaleo.Systems.SQLServer;
using Sdaleo.Systems.Advantage;
using Sdaleo.Systems;
using Pluto.Api;

namespace PlutoServer.MSL.Connectors
{
    /// <summary>
    /// Responsible for cachine SystemAPIKeys to physical Databases/Connections
    /// Since we are now seperated from the instance, the practice needs to be looked up
    /// in the practice list table. The Practice List Table will contain UserApiKeys that we
    /// have to convert to SystemApiKeys. This cache will save us a lookup, as well as do the work for us
    /// </summary>
    public static class DBCache
    {
        private static Dictionary<string, SQLServerCredential> _LytecConnetions = new Dictionary<string, SQLServerCredential>();
        private static Dictionary<string, AdvantageCredential> _MedisoftConnections = new Dictionary<string, AdvantageCredential>();

        // Static Instance of the Current Configuration Data
        private static DBCacheDataStore s_DBCredentialCacheDataStore = new DBCacheDataStore();
        private static XSerializer s_xmlserializer = new XSerializer();
        private static ISReadWrite s_ISReadWrite = new ISReadWrite("DBCache.enc");

        #region Construction

        static DBCache()
        {
        }

        #endregion

        #region Mobile Test Settings

        /// <summary>
        /// Is this computer a computer used for mobile testing?
        /// </summary>
        public static bool IsMachine_Used_ForTesting = false;

        /// <summary>
        /// Used for Unit Testing Purposes
        /// </summary>
        public static SQLServerCredential TestLytecUserDBCredential = null;

        /// <summary>
        /// Used for Unit Testing Purposes
        /// </summary>
        public static AdvantageCredential TestMedisoftDBCredential = null;        

        #endregion        

        #region AddSharedDataConnections (Public for Test Framework)

        /// <summary>
        /// Add a new SQL Server Credential to the datastore if it doesn't already exist
        /// </summary>
        /// <param name="bSaveToFile">true to save to the DataStore File</param>
        public static void AddSharedDataConnectionToDataStore(SQLServerCredential credential, bool bSaveToFile)
        {
            // Check if exists
            DBCacheDataStore.Credential[] existing_creds = s_DBCredentialCacheDataStore.SQLServerCredentials.Credentials;
            foreach (DBCacheDataStore.Credential existing_cred in existing_creds)
            {
                if (String.Compare(credential.Server, existing_cred.SqlServer, true) == 0 &&
                    String.Compare(credential.Instance, existing_cred.SqlInstance, true) == 0)
                    return;
            }

            // else Add to persisten store
            DBCacheDataStore.Credential cred = new DBCacheDataStore.Credential();
            cred.SqlServer = credential.Server;
            cred.SqlInstance = credential.Instance;
            cred.SqlUser = credential.User;
            cred.SqlPassword = ConnStr.RetrieveValue("PASSWORD", credential.ConnectionString);
            s_DBCredentialCacheDataStore.SQLServerCredentials.AddCredential(cred);

            // Log this 
            MSLSpecific.Logger.Info("Adding a new SQL Server Credential to the Datastore for Server:{0} Instance:{1}", cred.SqlServer, cred.SqlInstance);

            // Save to File
            if (bSaveToFile)
                SaveDataStore();

            // Load all User Api Keys for new Connection
            IterateSharedConnectionAndLoadUserApiKeyConnections(credential);
        }

        /// <summary>
        /// Add a new Advantage Credential * Shared Credential * to the datastore if it doesn't already exist
        /// </summary>
        /// <param name="bSaveToFile">true to save to the DataStore File</param>
        public static void AddSharedDataConnectionToDataStore(AdvantageCredential credential, bool bSaveToFile)
        {
            // Check if exists
            DBCacheDataStore.Credential[] existing_creds = s_DBCredentialCacheDataStore.AdvantageCredentials.Credentials;
            foreach (DBCacheDataStore.Credential existing_cred in existing_creds)
            {
                if (String.Compare(credential.DataSource, existing_cred.AdvDataSource, true) == 0)
                    return;
            }

            // else Add to persisten store
            DBCacheDataStore.Credential cred = new DBCacheDataStore.Credential();
            cred.AdvDataSource = credential.DataSource;
            cred.AdvIsRemote = (ConnStr.RetrieveValue("SERVERTYPE", credential.ConnectionString) == "REMOTE");
            s_DBCredentialCacheDataStore.AdvantageCredentials.AddCredential(cred);

            // Log this 
            MSLSpecific.Logger.Info("Adding a new Advantage Credential to the Datastore DataSource:{0} Remote:{1}", cred.AdvDataSource, cred.AdvIsRemote);

            // Save to File
            if (bSaveToFile)
                SaveDataStore();

            // Load all User Api Keys for new Connection
            IterateSharedConnectionAndLoadUserApiKeyConnections(credential);
        }

        #endregion

        #region Persistent DBCache File Store (Internal)

        /// <summary>
        /// Read from the DataStore into user connections * Reads in all credentials from a file into SQLServer & Medisoft Connections *
        /// Additionally, pulls out UserApiKeys from the Practice List of the corresponding Databases
        /// </summary>
        /// <returns>true if file doesn't exist or if successfull read occured, false otherwise</returns>
        internal static bool ReadInDataStore()
        {
            try
            {
                string ISFileContent = s_ISReadWrite.ReadFromIS();
                if (!String.IsNullOrEmpty(ISFileContent))
                {                    
                    // Ran into the issue that the data in the DBCache is total nonsense after decrypting,
                    // not sure yet how to doeal with yet * however if that is the case * what else can we
                    // do but ignore the input?
                    try
                    {
                        ISFileContent = Encryption.DecryptText(ISFileContent);
                        s_DBCredentialCacheDataStore = s_xmlserializer.ReadFromString<DBCacheDataStore>(ISFileContent);
                    }
                    catch (Exception e) 
                    { 
                        // error occured parsing the file, ignore the read!
                        PlutoService.AppLogError(String.Format("DBCache File is invalid. Ignoring and erasing the cache: {0}", e.Message));
                        s_ISReadWrite.WriteToIS(String.Empty);
                        return true;
                    }

                    // Test to see if a single successful read occured
                    bool bASuccessFullLytecReadOccured = false;
                    // Iterate thru all the SQL Connections
                    _LytecConnetions.Clear();
                    bool bLytecConnectionsFound = false;
                    int nLytecCount = 0;
                    foreach (DBCacheDataStore.Credential credential in s_DBCredentialCacheDataStore.SQLServerCredentials.Credentials)
                    {
                        if (credential != null)
                        {
                            bLytecConnectionsFound = true;
                            SQLServerCredential tempCred = new SQLServerCredential(credential.SqlServer, credential.SqlInstance, "Lytec SharedData", credential.SqlUser, credential.SqlPassword);
                            bool bSuccess = IterateSharedConnectionAndLoadUserApiKeyConnections(tempCred);
                            if (bSuccess)
                            {
                                bASuccessFullLytecReadOccured = true;
                                nLytecCount++;
                            }
                        }
                    }
                    if (bLytecConnectionsFound && !bASuccessFullLytecReadOccured)
                        PlutoService.AppLogInfo("Not one valid Lytec Connection was found in the DataStore");
                    if(nLytecCount > 0)
                        PlutoService.AppLogInfo(String.Format("Loaded {0} Lytec Shared Connection",nLytecCount));

                    // Test to see if a single successful read occured
                    bool bASuccessFullMedisoftReadOccured = false;
                    // Iterate thru all the Advantage Connections
                    _MedisoftConnections.Clear();
                    bool bMedisoftConnectionsFound = false;
                    int nMedisoftCount = 0;
                    foreach (DBCacheDataStore.Credential credential in s_DBCredentialCacheDataStore.AdvantageCredentials.Credentials)
                    {
                        if (credential != null)
                        {
                            bMedisoftConnectionsFound = true;                            
                            AdvantageCredential tempCred = new AdvantageCredential(credential.AdvDataSource, "SharedDataUser", "AndPassword", credential.AdvIsRemote ? AdvantageCredential.ServerType.REMOTE : AdvantageCredential.ServerType.LOCAL);
                            bool bSuccess = IterateSharedConnectionAndLoadUserApiKeyConnections(tempCred);
                            if (bSuccess)
                            {
                                bASuccessFullMedisoftReadOccured = true;
                                nMedisoftCount++;
                            }
                        }
                    }
                    if (bMedisoftConnectionsFound && !bASuccessFullMedisoftReadOccured)
                        PlutoService.AppLogInfo("Not one valid Medisoft Connection was found in the DataStore");
                    if (nMedisoftCount > 0)
                        PlutoService.AppLogInfo(String.Format("Loaded {0} Medisoft Shared Connection", nMedisoftCount));

                    return (bASuccessFullLytecReadOccured || bASuccessFullMedisoftReadOccured);
                }
            }
            catch (Exception e) 
            { 
                PlutoService.AppLogError(String.Format("Error thrown reading DataStore: {0}", e.Message));
                return false; 
            }

            // No File exists, always return true
            return true;
        }

        /// <summary>
        /// Save user connnections to the DataStore any existing connections * Writes in all SQLServer & Medisoft Connectios to a file *
        /// Note: UserApiKeys are never saved only the paths/credentials of the Connection
        /// </summary>
        internal static void SaveDataStore()
        {
            string ISFileContent = s_xmlserializer.WriteToString<DBCacheDataStore>(s_DBCredentialCacheDataStore);
            ISFileContent = Encryption.EncryptText(ISFileContent);
            s_ISReadWrite.WriteToIS(ISFileContent);
        }
        
        /// <summary>
        /// Load all SQL Server User Credentials from a Shared Connection
        /// </summary>
        /// <param name="credential"></param>
        internal static bool IterateSharedConnectionAndLoadUserApiKeyConnections(SQLServerCredential credential)
        {
            bool bSuccess = false;
            if (credential != null)
            {
                DBRetVal retVal = LytecConnector.GetPracticeList(credential);
                if (retVal.IsValid)
                {
                    bSuccess = true;
                    foreach (DataRow row in retVal.GetDataTableRetVal().Rows)
                    {
                        string UserApiKey = DataRet.Retrieve(row["UserAPIKey"]);
                        if (!String.IsNullOrEmpty(UserApiKey))
                        {
                            SystemAccessVerifier verifier = new SystemAccessVerifier(UserApiKey);
                            if (!String.IsNullOrEmpty(verifier.SystemApiKey))
                            {
                                string UserDB = DataRet.Retrieve(row["Database Name"]);
                                string Password = ConnStr.RetrieveValue("PASSWORD", credential.ConnectionString);                                

                                // We must ensure uniqueness. Somehow both Medisoft & Lytec can create scenarios
                                // where they are not unique amongst themselves! strange, but true
                                // Don't overwrite connections that are already loaded (invalid state)
                                if (!_LytecConnetions.ContainsKey(verifier.SystemApiKey))
                                {
                                    MSLSpecific.Logger.Info("Loading ApiKey Connection for DataBase:{0}", UserDB);
                                    _LytecConnetions[verifier.SystemApiKey] = new SQLServerCredential(credential.Server, credential.Instance, UserDB, credential.User, Password);
                                }                                
                            }
                        }
                    }
                }
            }
            return bSuccess;
        }

        /// <summary>
        /// Load all Advantage User Credentials from a Shared Connection
        /// </summary>
        /// <param name="credential"></param>
        internal static bool IterateSharedConnectionAndLoadUserApiKeyConnections(AdvantageCredential credential)
        {
            bool bSuccess = false;
            if (credential != null)
            {
                DBRetVal retVal = MedisoftConnector.GetPracticeList(credential);
                if (retVal.IsValid)
                {
                    bSuccess = true;
                    foreach (DataRow row in retVal.GetDataTableRetVal().Rows)
                    {
                        string UserApiKey = DataRet.Retrieve(row["UserAPIKey"]);
                        if (!String.IsNullOrEmpty(UserApiKey))
                        {
                            SystemAccessVerifier verifier = new SystemAccessVerifier(UserApiKey);
                            if (!String.IsNullOrEmpty(verifier.SystemApiKey))
                            {
                                string UserDB = DataRet.Retrieve(row["Data Path"]);
                                bool bIsRemote = (ConnStr.RetrieveValue("SERVERTYPE", credential.ConnectionString) == "REMOTE");

                                // We must ensure uniqueness. Somehow both Medisoft & Lytec can create scenarios
                                // where they are not unique amongst themselves! strange, but true
                                // Don't overwrite connections that are already loaded (invalid state)
                                if(!_MedisoftConnections.ContainsKey(verifier.SystemApiKey))
                                {
                                    MSLSpecific.Logger.Info("Loading ApiKey Connection for DataBase:{0}", UserDB);
                                    _MedisoftConnections[verifier.SystemApiKey] = new AdvantageCredential(UserDB + "\\mwddf.add", "user", "password", bIsRemote ? AdvantageCredential.ServerType.REMOTE : AdvantageCredential.ServerType.LOCAL);
                                }
                            }
                        }
                    }
                }
            }
            return bSuccess;
        }

        #endregion

        #region Get Specific Connection

        internal static SQLServerCredential GetSQLServerConnection(string SystemApiKey)
        {
            IConnectDb connection = GetConnectionForSystemApiKey(SystemApiKey);
            if (connection is SQLServerCredential)
                return (SQLServerCredential)connection;
            else
                return null;
        }

        internal static AdvantageCredential GetAdvantageConnection(string SystemApiKey)
        {
            IConnectDb connection = GetConnectionForSystemApiKey(SystemApiKey);
            if (connection is AdvantageCredential)
                return (AdvantageCredential)connection;
            else
                return null;
        }

        internal static AdvantageCredential GetAdvantageConnection(string SystemApiKey, string User)
        {
            AdvantageCredential credentialSystem = GetAdvantageConnection(SystemApiKey);
            if (credentialSystem != null && !String.IsNullOrEmpty(User))
            {
                // First Retrieve the Password for that user                
                string SQL = String.Format("SELECT [Password] FROM [mwSEC] WHERE upper([Code]) = '{0}'", User.Replace("'", "''").ToUpper());
                DB db = DB.Create(credentialSystem);
                DBRetVal retVal = db.ExecuteScalar(SQL);
                if (retVal.IsValid)
                {                    
                    // Now Create a new Connection with the User and Password for Advantage
                    // ~Medisoft attaches 'mwmw' to the username for DB Connections
                    string pwd = retVal.GetScalarRetVal().Trim();
                    AdvantageCredential credentialUser = new AdvantageCredential(credentialSystem.DataSource, (User + "mwmw").ToUpper(), pwd.ToUpper(), credentialSystem.Type);
                    return credentialUser;
                }
            }

            // Something Failed
            return null;
        }
        
        #endregion

        #region Get General Connection

        /// <summary>
        /// Looks up the System string in the Local User DB Connection cache and retrieves it,
        /// if found. otherwise throws System not properly installed error for (release builds,
        /// of this service). On Test machines it will always return something.
        /// </summary>
        /// <param name="SystemApiKey">SystemKey to check</param>
        /// <returns></returns>
        private static IConnectDb GetConnectionForSystemApiKey(string SystemApiKey)
        {
            if (!String.IsNullOrEmpty(SystemApiKey))
            {
                if (_LytecConnetions.ContainsKey(SystemApiKey))
                    return _LytecConnetions[SystemApiKey];

                if (_MedisoftConnections.ContainsKey(SystemApiKey))
                    return _MedisoftConnections[SystemApiKey];

                // * Setup Any Debug/Test Connections * Used Internally Only
                if (IsMachine_Used_ForTesting)
                {
                    if (ProductType.IsKeyLytec(SystemApiKey) && TestLytecUserDBCredential != null)
                        return TestLytecUserDBCredential;
                    else if (ProductType.IsKeyMedisoft(SystemApiKey) && TestMedisoftDBCredential != null)
                        return TestMedisoftDBCredential;
                    else
                        throw new Exception("Failed to retrieve DBCredential for ApiKey. Test Machine not properly configured.");
                }
                else
                {
                    // not internally recognized, something went wrong or just Lytec / Medisoft
                    // calling in the wrong order * either way * nothing to return
                    // returning null will just mean something else blows up, so might as well
                    // throw an error and let the pluto api return false
                    PlutoService.AppLogError("Failed to retrieve ApiKey. System not properly configured.");                    
                    throw new Exception("Failed to retrieve ApiKey. System not properly configured.");
                }
            }
            return null;
        }

        #endregion
    }


}