using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Sdaleo;
using Sdaleo.Systems.SQLServer;
using Pluto.Api;
using System.Data;
using Sdaleo.Systems;
using Yaulw.Win32;
using CrossProduct.Core;
using System.Reflection;
using Yaulw.Tools;

namespace PlutoServer.MSL.Connectors
{
    public static class LytecConnector
    {
        internal static string MAKE_SURE_APOSTROPHY_IS_SQL_READY(string str)
        {
            if (!String.IsNullOrEmpty(str))
            {
                str = str.Replace("'", "''");
                return str;
            }
            return String.Empty;
        }

        internal static bool IS_VALID_FOUR_DIGIT_PIN(string fourdigitpin)
        {
            if (!String.IsNullOrEmpty(fourdigitpin))
            {
                fourdigitpin = fourdigitpin.Trim();
                if (fourdigitpin.Length == 4)
                {
                    foreach (char c in fourdigitpin)
                    {
                        if (!Char.IsDigit(c))
                            return false;
                    }
                    return true;
                }
            }
            return false;
        }

        #region System / Setup Specific 

        /// <summary>
        /// Retrieve the entire Practice List for the given Shared DB Credential * Credential must point to Shared DB *
        /// </summary>
        /// <param name="credential"></param>
        /// <returns></returns>
        public static DBRetVal GetPracticeList(SQLServerCredential credential)
        {
            if (credential != null)
            {
                DB db = DB.Create(credential);
                DBRetVal retVal = db.FillDataTable("SELECT [Practice Name],[Database Name],[UserAPIKey] FROM [Practice List]");
                if (!retVal.IsValid)
                    MSLSpecific.Logger.Error("Lytec. Failed to retrieve Practice List for Server:{0} Instance:{1}", credential.Server, credential.Instance);
                return retVal;
            }
            return new DBRetVal();
        }

        /// <summary>
        /// Retrieve the userapi key for a given practice, given Shared DB Credential * Credential must point to Shared DB *
        /// </summary>
        /// <param name="credential"></param>
        /// <returns></returns>
        public static string GetUserApiKeyForPractice(SQLServerCredential credential, string DataSetOrDataBaseName, out bool bErrorOccured)
        {
            bErrorOccured = true;
            if (credential != null && !String.IsNullOrEmpty(DataSetOrDataBaseName))
            {
                DB db = DB.Create(credential);
                DBRetVal retVal = db.ExecuteScalar(String.Format("SELECT [UserAPIKey] FROM [Practice List] WHERE [Database Name] LIKE '{0}'", MAKE_SURE_APOSTROPHY_IS_SQL_READY(DataSetOrDataBaseName)));
                bErrorOccured = retVal.ErrorOccured;
                if (retVal.IsValid)
                    return retVal.GetScalarRetVal();
            }
            return String.Empty;
        }

        /// <summary>
        /// Use this to Set the User API Key * Credential must point to Shared DB *
        /// </summary>
        /// <param name="credential"></param>
        /// <param name="PracticeName"></param>
        /// <param name="UserApiKey"></param>
        /// <returns></returns>
        public static bool SetUserApiKeyForPractice(SQLServerCredential credential, string DataSetOrDataBaseName, string UserApiKey)
        {
            if (credential != null && !String.IsNullOrEmpty(DataSetOrDataBaseName))
            {
                DB db = DB.Create(credential);
                DBRetVal retVal = db.ExecuteNonQuery(String.Format("UPDATE [Practice List] SET [UserApiKey]='{0}' WHERE [Database Name] LIKE '{1}'", UserApiKey, MAKE_SURE_APOSTROPHY_IS_SQL_READY(DataSetOrDataBaseName)));
                if (!retVal.IsValid)
                    MSLSpecific.Logger.Error("Lytec. Failed to set ApiKey for Server:{0} Instance:{1} Database:{2}", credential.Server, credential.Instance, DataSetOrDataBaseName);
                return retVal.IsValid;
            }
            return false;
        }

        /// <summary>
        /// we must ensure uniqueness amongst all our userapikeys (Lytec and somehow Medisoft get into an
        /// invalid state) where they duplicate them * Credential must point to Shared DB *
        /// </summary>
        /// <param name="credential"></param>
        /// <param name="PracticeName"></param>
        /// <param name="UserApiKey"></param>
        /// <returns></returns>
        public static void CleanUpDuplicateUserApiKeyExists(SQLServerCredential credential)
        {
            DBRetVal retVal = GetPracticeList(credential);
            if (retVal.IsValid)
            {
                Stack<string> userApiStack = new Stack<string>();
                foreach (DataRow row in retVal.GetDataTableRetVal().Rows)
                {
                    string UserApiKey = DataRet.Retrieve(row["UserAPIKey"]);
                    if (!String.IsNullOrEmpty(UserApiKey))
                    {
                        if (!userApiStack.Contains(UserApiKey))
                        {
                            userApiStack.Push(UserApiKey);
                        }                         
                        else
                        {
                            // we found a duplicate!!! (Clear the UserApiKey for that Database - IMP!)
                            string DBName = DataRet.Retrieve(row["Database Name"]);
                            SetUserApiKeyForPractice(credential, DBName, String.Empty);                            
                        }
                    }                        
                }
            }            
        }

        #endregion

        #region User Authentication

        /// <summary>
        /// User Authentication
        /// </summary>
        /// <param name="SystemApiKey"></param>
        /// <param name="User"></param>
        /// <param name="Password"></param>
        /// <returns></returns>
        public static UserInfo1 AuthenticateUserLogin(string SystemApiKey, string User, string Password)
        {
            if (!String.IsNullOrEmpty(User) && !String.IsNullOrEmpty(Password))
            {
                SQLServerCredential credential = DBCache.GetSQLServerConnection(SystemApiKey);
                DB db = DB.Create(credential);
                DBRetVal result = db.FillDataTable(String.Format("SELECT [Code],[Name],[User Permissions],[Password] FROM [NDCLytec User] Where [Code]='{0}'", MAKE_SURE_APOSTROPHY_IS_SQL_READY(User)));
                //if ((result != null) && (result.IsValid)) /* Rameshes fix that should not be needed and didn't make sense */
                if (result.IsValid)
                {
                    DataRow row = result.GetDataTableFirstRow();
                    string dbName = DataRet.Retrieve(row["Name"]);
                    //string dbPwd = DataRet.Retrieve(row["Password"]);

                    // Decrypt the Password from Lytec (also first char is always garbage, a Lytec thing)
                    byte[] pwdField = DataEncryption.DecryptBinary((byte[])row["Password"]);
                    string dbPwd = SimpleStringBinaryConverter.GetString(pwdField).Substring(1);                    

                    // Authenticate! Password must match (case-insensitive) Lytec doesn't care about case
                    if (!String.IsNullOrEmpty(dbName) && (String.Compare(Password.Trim(), dbPwd, true) == 0))
                    {
                        UserInfo1 info = new UserInfo1();
                        info.AuthToken = SystemApiKey;
                        info.Id = DataRet.Retrieve(row["Code"]);
                        info.UserName = info.Id;

                        // Seperate First n' Last name, if possible
                        info.Name = dbName;
                        int nFirst = dbName.IndexOf(' ');
                        if (nFirst != -1)
                        {
                            info.FirstName = dbName.Substring(0, nFirst);
                            info.LastName = dbName.Substring(nFirst + 1);
                        }
                        else
                        {
                            info.FirstName = dbName;
                            info.LastName = "";
                        }

                        // Authenticated, return the object
                        info.IsProvider = true;
                        return info;
                    }
                }
            }
            return null;
        }

        /// <summary>
        /// Pin Authentication
        /// </summary>
        /// <param name="SystemApiKey"></param>
        /// <param name="User"></param>
        /// <param name="pin"></param>
        /// <returns></returns>
        public static UserInfo1 AuthenticateUserPIN(string SystemApiKey, string User, string pin)
        {
            if (!String.IsNullOrEmpty(User) && !String.IsNullOrEmpty(pin))
            {
                SQLServerCredential credential = DBCache.GetSQLServerConnection(SystemApiKey);
                DB db = DB.Create(credential);
                DBRetVal result = db.FillDataTable(String.Format("SELECT [Code],[Name],[MobilePin] FROM [NDCLytec User] Where [Code]='{0}'", MAKE_SURE_APOSTROPHY_IS_SQL_READY(User)));
                if (result.IsValid)
                {
                    DataRow row = result.GetDataTableFirstRow();
                    string dbName = DataRet.Retrieve(row["Name"]);
                    string dbPin = DataRet.Retrieve(row["MobilePin"]);

                    // Authenticate! Password must match (case-sensitive)
                    if (!String.IsNullOrEmpty(dbName) && (String.Compare(pin.Trim(), dbPin, false) == 0))
                    {
                        UserInfo1 info = new UserInfo1();
                        info.AuthToken = SystemApiKey;
                        info.Id = DataRet.Retrieve(row["Code"]);
                        info.UserName = info.Id;

                        // Seperate First n' Last name, if possible
                        info.Name = dbName;
                        int nFirst = dbName.IndexOf(' ');
                        if (nFirst != -1)
                        {
                            info.FirstName = dbName.Substring(0, nFirst);
                            info.LastName = dbName.Substring(nFirst + 1);
                        }
                        else
                        {
                            info.FirstName = dbName;
                            info.LastName = "";
                        }

                        // Authenticated, return the object
                        info.IsProvider = true;
                        return info;
                    }
                }
            }
            return null;
        }

        /// <summary>
        /// Create Pin
        /// </summary>
        /// <param name="SystemApiKey"></param>
        /// <param name="User"></param>
        /// <param name="pin"></param>
        /// <returns></returns>
        public static bool CreatePin(string SystemApiKey, string User, string pin)
        {
            if (!String.IsNullOrEmpty(User) && IS_VALID_FOUR_DIGIT_PIN(pin))
            {
                SQLServerCredential credential = DBCache.GetSQLServerConnection(SystemApiKey);
                DB db = DB.Create(credential);
                string sql = String.Format("UPDATE [NDCLytec User] SET [MobilePin]='{0}' WHERE [Code]='{1}'", MAKE_SURE_APOSTROPHY_IS_SQL_READY(pin), MAKE_SURE_APOSTROPHY_IS_SQL_READY(User));
                DBRetVal result = db.ExecuteNonQuery(sql);
                return result.IsValid;
            }
            return false;            
        }

        /// <summary>
        /// Pin is Set
        /// </summary>
        /// <param name="SystemApiKey"></param>
        /// <param name="User"></param>
        /// <returns></returns>
        public static bool PinIsSet(string SystemApiKey, string User)
        {
            if (!String.IsNullOrEmpty(User))
            {
                SQLServerCredential credential = DBCache.GetSQLServerConnection(SystemApiKey);
                DB db = DB.Create(credential);
                string sql = String.Format("SELECT [MobilePin] FROM [NDCLytec User] Where [Code]='{0}'", MAKE_SURE_APOSTROPHY_IS_SQL_READY(User));
                DBRetVal result = db.ExecuteScalar(sql);
                if (result.IsValid)
                {
                    string currentPin = DataRet.Retrieve(result.GetScalarRetVal());
                    return IS_VALID_FOUR_DIGIT_PIN(currentPin);
                }
            }
            return false;
        }

        #endregion

        #region Public Simple Info Getters

        /// <summary>
        /// Get Providers
        /// </summary>
        /// <param name="SystemApiKey"></param>
        /// <returns></returns>
        public static ProviderInfo1[] GetProviderList(string SystemApiKey)
        {
            SQLServerCredential credential = DBCache.GetSQLServerConnection(SystemApiKey);
            DB db = DB.Create(credential);
            DBRetVal result = db.FillDataTable("SELECT [Code],[First Name],[Middle Name],[Last Name],[Phone],[ID] FROM [Provider]");
            List<ProviderInfo1> providers = new List<ProviderInfo1>();
            if (result.IsValid)
            {
                foreach (DataRow row in result.GetDataTableRetVal().Rows)
                {
                    ProviderInfo1 info = new ProviderInfo1();
                    info.UserId = DataRet.Retrieve(row["Code"]);                    
                    info.FirstName = DataRet.Retrieve(row["First Name"]);
                    info.MiddleName = DataRet.Retrieve(row["Middle Name"]);
                    info.LastName = DataRet.Retrieve(row["Last Name"]);
                    info.Id = DataRet.Retrieve<long>(row["ID"], -1);
                    string phone = DataRet.Retrieve(row["Phone"]);
                    if (!String.IsNullOrEmpty(phone) && phone != "(   )    -")
                    {
                        info.Phone = phone;
                    }
                    providers.Add(info);
                }
                return providers.ToArray();
            }
            return null;
        }

        /// <summary>
        /// Get Diagnosis
        /// </summary>
        /// <param name="SystemApiKey"></param>
        /// <param name="searchStr"></param>
        /// <returns></returns>
        public static TerminologyInfo1[] GetDiagnosisList(string SystemApiKey, string searchStr)
        {
            SQLServerCredential credential = DBCache.GetSQLServerConnection(SystemApiKey);
            DB db = DB.Create(credential);

            string sql = "SELECT [Code],[Description] FROM [Diagnosis] WHERE [Inactive] = 0";
            if (!String.IsNullOrEmpty(searchStr))
                sql += String.Format(" AND ([Code] like '%{0}%' OR [Description] like '%{0}%')", MAKE_SURE_APOSTROPHY_IS_SQL_READY(searchStr));

            DBRetVal result = db.FillDataTable(sql);
            List<TerminologyInfo1> diagnoses = new List<TerminologyInfo1>();
            if (result.IsValid)
            {
                foreach (DataRow row in result.GetDataTableRetVal().Rows)
                {
                    TerminologyInfo1 info = new TerminologyInfo1();
                    info.Code = DataRet.Retrieve(row["Code"]);
                    info.Description = DataRet.Retrieve(row["Description"]);
                    diagnoses.Add(info);
                }
                return diagnoses.ToArray();
            }
            return null;
        }

        /// <summary>
        /// GetProcedures
        /// </summary>
        /// <param name="SystemApiKey"></param>
        /// <param name="chart"></param>
        /// <param name="searchStr"></param>
        /// <returns></returns>
        public static TerminologyInfo1[] GetProceduresList(string SystemApiKey, string chart, string searchStr)
        {
            SQLServerCredential credential = DBCache.GetSQLServerConnection(SystemApiKey);
            DB db = DB.Create(credential);

            string sql = "SELECT [Code],[Description] FROM [Transaction Code] WHERE [Inactive] = 0";
            if (!String.IsNullOrEmpty(searchStr))
                sql += String.Format(" AND ([Code] like '%{0}%' OR [Description] like '%{0}%')", MAKE_SURE_APOSTROPHY_IS_SQL_READY(searchStr));

            // To Do: Implement Fee Schedule for lytec (when time comes...)
            //        In Order to get the modifiers for chart

            DBRetVal result = db.FillDataTable(sql);
            List<TerminologyInfo1> procedures = new List<TerminologyInfo1>();
            if (result.IsValid)
            {
                foreach (DataRow row in result.GetDataTableRetVal().Rows)
                {
                    TerminologyInfo1 info = new TerminologyInfo1();
                    info.Code = DataRet.Retrieve(row["Code"]);
                    info.Description = DataRet.Retrieve(row["Description"]);
                    procedures.Add(info);
                }
                return procedures.ToArray();
            }
            return null;
        }

        /// <summary>
        /// Get Facilities
        /// </summary>
        /// <param name="SystemApiKey"></param>
        /// <returns></returns>
        public static FacilityInfo1[] GetFacilitiesList(string SystemApiKey)
        {
            SQLServerCredential credential = DBCache.GetSQLServerConnection(SystemApiKey);
            DB db = DB.Create(credential);
            DBRetVal result = db.FillDataTable("SELECT [Code],[Name],[Address 1],[Address 2],[City],[State],[Zip Code],[Zip Plus 4] FROM [Address] WHERE [Inactive] = 0 AND [Type] = 2");
            List<FacilityInfo1> facilities = new List<FacilityInfo1>();
            if (result.IsValid)
            {
                foreach (DataRow row in result.GetDataTableRetVal().Rows)
                {
                    FacilityInfo1 info = new FacilityInfo1();
                    info.Code = DataRet.Retrieve(row["Code"]);
                    info.Name = DataRet.Retrieve(row["Name"]);

                    // Address Info
                    info.Address = new AddressInfo1();
                    info.Address.Address1 = DataRet.Retrieve(row["Address 1"]);
                    info.Address.Address2 = DataRet.Retrieve(row["Address 2"]);
                    info.Address.City = DataRet.Retrieve(row["City"]);
                    info.Address.State = DataRet.Retrieve(row["State"]);
                    // Zip Code Plus 4
                    string zipCode = DataRet.Retrieve(row["Zip Code"]);
                    string zipCode4 = DataRet.Retrieve(row["Zip Plus 4"]);
                    if (!String.IsNullOrEmpty(zipCode) && !String.IsNullOrEmpty(zipCode4))
                        info.Address.Zip = String.Format("{0}-{1}", zipCode, zipCode4);
                    else
                        info.Address.Zip = zipCode;

                    facilities.Add(info);
                }
                return facilities.ToArray();
            }
            return null;
        }

        /// <summary>
        /// Get Resources (NOT CALLED)
        /// </summary>
        /// <param name="SystemApiKey"></param>
        /// <returns></returns>
        public static ResourceInfo1[] GetResourcesList(string SystemApiKey)
        {
            SQLServerCredential credential = DBCache.GetSQLServerConnection(SystemApiKey);
            DB db = DB.Create(credential);
            DBRetVal result = db.FillDataTable("SELECT [Resource Key],[Name] FROM [Resource]");
            List<ResourceInfo1> resources = new List<ResourceInfo1>();
            if (result.IsValid)
            {
                foreach (DataRow row in result.GetDataTableRetVal().Rows)
                {
                    ResourceInfo1 info = new ResourceInfo1();
                    info.ID = DataRet.Retrieve<int>(row["Resource Key"], -1);
                    info.Name = DataRet.Retrieve(row["Name"]);
                    resources.Add(info);
                }
                return resources.ToArray();
            }
            return null;
        }

        /// <summary>
        /// Get Patients
        /// </summary>
        /// <param name="SystemApiKey"></param>
        /// <param name="lastName"></param>
        /// <param name="firstName"></param>
        /// <param name="dateOfBirth"></param>
        /// <returns></returns>
        public static PatientInfo1[] GetPatientList(string SystemApiKey, string lastName, string firstName, string dateOfBirth)
        {
            SQLServerCredential credential = DBCache.GetSQLServerConnection(SystemApiKey);
            DB db = DB.Create(credential);

            string sql = "SELECT [Chart Number],[First Name],[Middle Name],[Last Name],[Birth Date],[Sex],[Address 1],[Address 2],[City],[State],[Zip Code],[Zip Plus 4],[Home Phone],[Work Phone],[Work Extension],[Mobile Phone],[Social Security] FROM [Patient]";

            // Allow them to search by patient name or by date of birth,
            // not both that is just too confusing
            bool bWhereIsSet = false;
            if (!String.IsNullOrEmpty(dateOfBirth))
            {
                DateTime dt;
                if (DateTime.TryParse(dateOfBirth, out dt))
                {
                    sql = sql + String.Format(" WHERE CONVERT(VARCHAR(25), [Birth Date], 126) LIKE '{0:D4}-{1:D2}-{2:D2}%' ", dt.Year, dt.Month, dt.Day);
                    bWhereIsSet = true;
                }
            }
            else
            {
                if (!String.IsNullOrEmpty(lastName))
                {
                    sql = sql + String.Format(" WHERE [Last Name] like '%{0}%' {1}", MAKE_SURE_APOSTROPHY_IS_SQL_READY(lastName), !String.IsNullOrEmpty(firstName) ? "OR" : "");
                    bWhereIsSet = true;
                }
                if (!String.IsNullOrEmpty(firstName) && !String.IsNullOrEmpty(lastName))
                {
                    sql = sql + String.Format(" {1} [First Name] like '%{0}%' ", MAKE_SURE_APOSTROPHY_IS_SQL_READY(firstName), String.IsNullOrEmpty(lastName) ? "WHERE" : "");
                }
            }

            // Only query for Active Patients
            if (bWhereIsSet)
                sql = sql + " AND [Inactive] = 0 ";
            else
                sql = sql + " WHERE [Inactive] = 0 "; 
            
            sql = sql + " ORDER BY [Last Name],[First Name],[Middle Name] ";

            DBRetVal result = db.FillDataTable(sql);
            List<PatientInfo1> patients = new List<PatientInfo1>();
            if (result.IsValid)
            {
                foreach (DataRow row in result.GetDataTableRetVal().Rows)
                {
                    PatientInfo1 info = FillInPatientInformation(row, false, true);
                    patients.Add(info);
                }
                return patients.ToArray();
            }
            return null;
        }

        #endregion

        #region Private Static SQL Query Helper

        /// <summary>
        /// Fills in the Patient Info from the Patient Table Columns
        /// </summary>
        /// <param name="row"></param>
        /// <param name="bFillAsMuchPatientInfoAsPossible">If Set to true, will fill in as much patient information as possible, otherwise will on fill basic patient name / chart information</param>
        /// <param name="bFillInPhoneInfo">if set to true will fill try to fill in phone information</param>
        /// <returns></returns>
        private static PatientInfo1 FillInPatientInformation(DataRow row, bool bFillAsMuchPatientInfoAsPossible, bool bFillInPhoneInfo)
        {
            PatientInfo1 info = new PatientInfo1();

            // Basic Patient Info (Always fill that information)
            info.ChartId = DataRet.Retrieve(row["Chart Number"]);
            info.Id = DataRet.Retrieve(row["Chart Number"]);
            info.FirstName = DataRet.Retrieve(row["First Name"]);
            info.MiddleName = DataRet.Retrieve(row["Middle Name"]);
            info.LastName = DataRet.Retrieve(row["Last Name"]);
            info.Weight = ""; // we don't track weight
            
            // Date of Birth &  Age Calculation
            info.DOB = DataRet.Retrieve<DateTime>(row["Birth Date"]);
            if (info.DOB > DateTime.MinValue)
            {
                DateTime now = DateTime.Today;
                int age = now.Year - info.DOB.Year;
                if (info.DOB > now.AddYears(-age)) age--;
                info.Age = age.ToString();
            }

            // Gender
            int nSex = DataRet.Retrieve<int>(row["Sex"], 2);
            if (nSex == 0)
                info.Gender = "Male";
            else if (nSex == 1)
                info.Gender = "Female";
            else
                info.Gender = "Unknown";

            // If we want more details on the Patient, this will be set to false
            if (bFillAsMuchPatientInfoAsPossible)
            {
                // Social Security
                info.SocialSecurity = DataRet.Retrieve(row["Social Security"]);

                // Primary Address
                AddressInfo1 address = new AddressInfo1();
                address.Address1 = DataRet.Retrieve(row["Address 1"]);
                address.Address2 = DataRet.Retrieve(row["Address 2"]);
                address.City = DataRet.Retrieve(row["City"]);
                address.State = DataRet.Retrieve(row["State"]);
                // Zip Code Plus 4
                string zipCode = DataRet.Retrieve(row["Zip Code"]);
                string zipCode4 = DataRet.Retrieve(row["Zip Plus 4"]);
                if (!String.IsNullOrEmpty(zipCode) && !String.IsNullOrEmpty(zipCode4))
                    address.Zip = String.Format("{0}-{1}", zipCode, zipCode4);
                else
                    address.Zip = zipCode;
                info.PrimaryAddress = address;
            }

            // Phone Number may already be filled in for us
            if (bFillInPhoneInfo)
            {
                // Primary Phone
                string Phone = DataRet.Retrieve(row["Mobile Phone"]);
                if (!String.IsNullOrEmpty(Phone) && Phone != "(   )    -")
                {
                    info.PrimaryPhone = Phone;
                }
                else
                {
                    Phone = DataRet.Retrieve(row["Home Phone"]);
                    if (!String.IsNullOrEmpty(Phone) && Phone != "(   )    -")
                    {
                        info.PrimaryPhone = Phone;
                    }
                    else
                    {
                        Phone = DataRet.Retrieve(row["Work Phone"]);
                        if (!String.IsNullOrEmpty(Phone) && Phone != "(   )    -")
                        {
                            info.PrimaryPhone = Phone;
                            string WorkExtension = DataRet.Retrieve(row["Work Extension"]);
                            if (!String.IsNullOrEmpty(WorkExtension))
                                info.PrimaryPhone = String.Concat(info.PrimaryPhone, ",", WorkExtension);
                        }
                    }
                }
            }

            return info;
        }

        /// <summary>
        /// Basically the same query as Appointment Query except with no Appointment information because all we have
        /// is a Chart
        /// </summary>
        /// <param name="PatientChart">Patient Chart</param>
        /// <returns></returns>
        private static string BuildPatientQuery(string PatientChart)
        {
            if (!String.IsNullOrEmpty(PatientChart))
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("SELECT p.[Chart Number] as [Chart Number],p.[Provider Code] as [Provider]");
                sb.Append(",p.[First Name] as [First Name],p.[Middle Name] as [Middle Name],p.[Last Name] as [Last Name],p.[Birth Date],p.[Sex] as [Sex],p.[Address 1] as [Address 1],p.[Address 2] as [Address 2],p.[City] as [City]");
                sb.Append(",p.[State] as [State],p.[Zip Code] as [Zip Code],p.[Zip Plus 4] as [Zip Plus 4],p.[Home Phone] as [Home Phone],p.[Work Phone] as [Work Phone],p.[Work Extension] as [Work Extension],p.[Mobile Phone] as [Mobile Phone]");
                sb.Append(",p.[Copay] as [Patient Copay], p.[Primary Code], p.[Ref Physician Code], p.[Social Security]");
                sb.Append(",i.[Name] as [Primary Insurance Name]");
                sb.Append(",d.[Name] as [Ref Physician Name],d.[Phone] as [Ref Physician Phone],d.[Main Phone] as [Ref Physician Main Phone], d.[Mobile Phone] as [Ref Physician Mobile Phone]");
                sb.Append(" FROM [Patient] p ");
                sb.Append(" LEFT JOIN [Insurance] i ON p.[Primary Code] = i.[Code]");
                sb.Append(" LEFT JOIN [Address] d ON p.[Ref Physician Code] = d.[Code]");
                sb.Append(" WHERE ");
                sb.Append("[Chart Number] = '");
                sb.Append(MAKE_SURE_APOSTROPHY_IS_SQL_READY(PatientChart));
                sb.Append("'");
                return sb.ToString();
            }
            return String.Empty;
        }

        /// <summary>
        /// Build the Appointment / Patient SQL Query as needed, either specify a Date Range or give a specific ID
        /// </summary>
        /// <param name="startDT"></param>
        /// <param name="endDT"></param>
        /// <returns></returns>
        private static string BuildAppointmentQuery(int nApptID, DateTime startDT, DateTime endDT, string[] providers, int[] resources)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT a.[Unique ID],a.[Resource],a.[Appt Date],a.[Status], a.[Appt Time],a.[Slot],a.[Length],a.[Appointment],a.[Chart] as [Chart Number],a.[Provider],a.[Procedure Code],a.[Appointment Note],a.[Text Color],a.[Background Color],a.[Reason]");
            sb.Append(",a.[Home Phone] as [Appt Home Phone],a.[Work Phone] as [Appt Work Phone],a.[Work Extension] as [Appt Work Extension]");
            sb.Append(",p.[First Name],p.[Middle Name],p.[Last Name],p.[Birth Date],p.[Sex] as [Sex],p.[Address 1],p.[Address 2],p.[City]");
            sb.Append(",p.[State],p.[Zip Code],p.[Zip Plus 4],p.[Home Phone],p.[Work Phone],p.[Work Extension],p.[Mobile Phone]");
            sb.Append(",p.[Copay] as [Patient Copay], p.[Primary Code], p.[Ref Physician Code], p.[Social Security]");
            sb.Append(",r.[Description] as [Reason Description]");
            sb.Append(",i.[Name] as [Primary Insurance Name]");
            sb.Append(",d.[Name] as [Ref Physician Name],d.[Phone] as [Ref Physician Phone],d.[Main Phone] as [Ref Physician Main Phone], d.[Mobile Phone] as [Ref Physician Mobile Phone]");
            sb.Append(" FROM [Appointment] a LEFT JOIN");
            sb.Append(" [Patient] p ON p.[Chart Number] = a.[Chart]");
            sb.Append(" LEFT JOIN [Appointment Reason] r ON a.[Reason] = r.[Code]");
            sb.Append(" LEFT JOIN [Insurance] i ON p.[Primary Code] = i.[Code]");
            sb.Append(" LEFT JOIN [Address] d ON p.[Ref Physician Code] = d.[Code]");
            sb.Append(" WHERE ");

            // if ID is specified, we are looking for a single appointment,
            // otherwise we are looking for a range
            if (nApptID >= 0)
            {
                sb.Append(String.Format(" [Unique ID] = {0} ", nApptID));
            }
            else
            {
                //    [Appt Date] between '2010-10-06 00:00:00' and '2010-10-06 00:00:00' AND             
                const string DATE_FORMAT = "{0:D4}-{1:D2}-{2:D2} 00:00:00";
                string StartDate = String.Format(DATE_FORMAT, startDT.Year, startDT.Month, startDT.Day);
                string EndDate = String.Format(DATE_FORMAT, endDT.Year, endDT.Month, endDT.Day);
                sb.Append(String.Format("[Appt Date] between '{0}' AND '{1}' AND ", StartDate, EndDate));

                //    [Appt Time] between '1753-01-01 08:00:00' AND '1753-01-01 12:00:00'
                const string TIME_FORMAT = "1753-01-01 {0:D2}:{1:D2}:{2:D2}";
                string StartTime = String.Format(TIME_FORMAT, startDT.Hour, startDT.Minute, startDT.Second);
                string EndTime = String.Format(TIME_FORMAT, endDT.Hour, endDT.Minute, endDT.Second);
                sb.Append(String.Format("[Appt Time] between '{0}' AND '{1}' ", StartTime, EndTime));

                // Do we have providers?
                // AND [Provider] in ('LAB',)  
                if (providers != null && providers.Length > 0)
                {
                    sb.Append("AND [Provider] in ('',");  // Allow '' blank providers
                    for (int i = 0; i < providers.Length; ++i)
                    {
                        sb.Append("'");
                        sb.Append(MAKE_SURE_APOSTROPHY_IS_SQL_READY(providers[i]));
                        sb.Append("'");
                        if (i != providers.Length - 1)
                            sb.Append(",");
                    }
                    sb.Append(") ");
                }

                // Do we have resources?
                // AND [Resource] in (2, )
                if (resources != null && resources.Length > 0)
                {
                    sb.Append("AND [Resource] in (");
                    for (int i = 0; i < resources.Length; ++i)
                    {
                        sb.Append(resources[i]);
                        if (i != resources.Length - 1)
                            sb.Append(",");
                    }
                    sb.Append(") ");
                }

                // Filter out all Cancelled Appointments
                sb.Append(" AND a.[Status] <> 7 ");

                // Don't forget the order by
                sb.Append("Order By [Appt Date], [Appt Time], [Slot]");
            }

            return sb.ToString();
        }

        #endregion

        #region Public Appointment Methods

        /// <summary>
        /// Retrieve all appointments for the given time period for the given providers or resources
        /// </summary>
        /// <param name="SystemApiKey"></param>
        /// <param name="providerInfo"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        public static AppointmentInfo1[] GetAppointments(string SystemApiKey, ProviderInfo1[] providerInfo, ResourceInfo1[] resourceInfo, DateTime startDate, DateTime endDate)
        {
            SQLServerCredential credential = DBCache.GetSQLServerConnection(SystemApiKey);
            DB db = DB.Create(credential);

            string sql = "";
            List<string> providers = new List<string>();
            List<int> resources = new List<int>();
            if (providerInfo != null)
            {
                foreach (ProviderInfo1 info1 in providerInfo)
                {
                    if (!String.IsNullOrEmpty(info1.UserId))
                        providers.Add(info1.UserId);
                }
            }
            if (resourceInfo != null)
            {
                foreach (ResourceInfo1 info1 in resourceInfo)
                {
                    if (info1.ID >= 0)
                        resources.Add(info1.ID);
                }
            }

            // Build the Appointment QUERY (don't pass in an Appt ID and it will use a range)
            sql = BuildAppointmentQuery(-1, startDate, endDate, providers.ToArray(), resources.ToArray());

            DBRetVal result = db.FillDataTable(sql);
            List<AppointmentInfo1> appointments = new List<AppointmentInfo1>();
            if (result.IsValid)
            {
                foreach (DataRow row in result.GetDataTableRetVal().Rows)
                {
                    AppointmentInfo1 info = new AppointmentInfo1();

                    info.Id = DataRet.Retrieve<long>(row["Unique ID"]);

                    // Retrieving Description for the Appointment Reason Code via join
                    info.Reason = DataRet.Retrieve(row["Reason Description"]);

                    //Retrieving Provider code for the appointment
                    info.ProviderCode = DataRet.Retrieve(row["Provider"]);

                    // Let's figgure out the Appointment DTs
                    // StartDateTime = DateTime.Parse(string.Format("{0} {1}:{2}:00 AM", today.ToShortDateString(), 2, 15)),
                    // EndDateTime = DateTime.Parse(string.Format("{0} {1}:{2}:00 PM", today.ToShortDateString(), 2, 30)),                    
                    DateTime dtDate = DataRet.Retrieve<DateTime>(row["Appt Date"]);
                    DateTime dtTime = DataRet.Retrieve<DateTime>(row["Appt Time"]);
                    int nDuration = DataRet.Retrieve<int>(row["Length"]);

                    info.StartDateTime = DateTime.Parse(string.Format("{0} {1:D2}:{2:D2}:{3:D2}", dtDate.ToShortDateString(), dtTime.Hour, dtTime.Minute, dtTime.Second));
                    if (nDuration == 0)
                    {
                        // Leave it as is (no duration was set)                        
                        info.EndDateTime = info.StartDateTime;                        
                    }
                    else
                    {
                        dtTime = dtTime.AddMinutes(nDuration);
                        info.EndDateTime = DateTime.Parse(string.Format("{0} {1:D2}:{2:D2}:{3:D2}", dtDate.ToShortDateString(), dtTime.Hour, dtTime.Minute, dtTime.Second));
                    }

                    // Appointment Primary Phone
                    bool bAppointmentPhoneFound = false;
                    string Phone = DataRet.Retrieve(row["Appt Home Phone"]);
                    if (!String.IsNullOrEmpty(Phone) && Phone != "(   )    -")
                    {
                        bAppointmentPhoneFound = true;
                    }
                    else
                    {
                        Phone = DataRet.Retrieve(row["Appt Work Phone"]);
                        if (!String.IsNullOrEmpty(Phone) && Phone != "(   )    -")
                        {
                            bAppointmentPhoneFound = true;
                            string WorkExtension = DataRet.Retrieve(row["Appt Work Extension"]);
                            if (!String.IsNullOrEmpty(WorkExtension))
                                Phone = String.Concat(Phone, ",", WorkExtension);
                        }
                    }

                    // Attach Chart Information (Only the most basic information, try to attach Phone Number if none found in appointment)
                    bool bHasChart = (DataRet.Retrieve(row["Chart Number"]) != "");
                    if (bHasChart)
                        info.Patient = FillInPatientInformation(row, false, !bAppointmentPhoneFound);
                    else
                        info.AppointmentDescription = DataRet.Retrieve(row["Appointment"]);

                    // Attach Appointment Phone always, if found
                    if (bAppointmentPhoneFound)
                        info.AppointmentPhone = Phone;

                    //[Background Color]
                    //#define DEFAULT_GRID_TEXT_COLOR    (RGB(0,0,0))
                    //#define DEFAULT_GRID_BKGND_COLOR  (RGB(255,255,255))
                    int ColorRef = DataRet.Retrieve<int>(row["Background Color"], 0);
                    if (ColorRef == 0)
                        info.AppointmentColor = new AppointmentColor1() { Red = 255, Green = 255, Blue = 255 };
                    else
                        info.AppointmentColor = new AppointmentColor1() { Red = Functions.GetRValue(ColorRef), Green = Functions.GetGValue(ColorRef), Blue = Functions.GetBValue(ColorRef) };

                    // Determine the Appointment Status
                    /*
                     * Lytec Status
                        0 – Pending
                        1 – Confirmed
                        2 – Waiting
                        3 – Being Seen
                        4 – Completed
                        5 – Late
                        6 – Missed
                        7 – Cancelled
                        8 – Rescheduled
                        9 – Recall
                     * --------------------------
                     * Lytec To Mobile Mapping
                        0 – Unconfirmed_Pending
                        1 – Confirmed
                        2 – CheckedIn_Waiting
                        3 – Missed
                        4 – Cancelled
                        5 – Being Seen
                        6 – CheckedOut_Completed
                        7 - Rescheduled
                     * */
                    string status = DataRet.Retrieve(row["Status"]);
                    switch (status)
                    {
                        case "0":
                            info.AppointmentStatus = AppointmentStatusEnum.Unconfirmed_Pending;
                            break;
                        case "1":
                            info.AppointmentStatus = AppointmentStatusEnum.Confirmed;
                            break;
                        case "2":
                            info.AppointmentStatus = AppointmentStatusEnum.CheckedIn_Waiting;
                            break;
                        case "3":
                            info.AppointmentStatus = AppointmentStatusEnum.BeingSeen;
                            break;
                        case "4":
                            info.AppointmentStatus = AppointmentStatusEnum.CheckedOut_Completed;
                            break;
                        case "6":
                            info.AppointmentStatus = AppointmentStatusEnum.Missed;
                            break;
                        case "7":
                            info.AppointmentStatus = AppointmentStatusEnum.Cancelled;
                            break;
                        case "8":
                            info.AppointmentStatus = AppointmentStatusEnum.Rescheduled;
                            break;
                        default:
                            break;
                    }

                    // Finally, add the appointment
                    appointments.Add(info);
                }
                return appointments.ToArray();
            }
            return null;
        }

        /// <summary>
        /// Appointment Detail query. Fills in useful information for the patient regarding the Appointment
        /// </summary>
        /// <param name="SystemApiKey"></param>
        /// <param name="ApptID"></param>
        /// <returns></returns>
        public static AppointmentDetail1 GetAppointmentDetail(string SystemApiKey, int ApptID, bool bCalculateBalance)
        {
            SQLServerCredential credential = DBCache.GetSQLServerConnection(SystemApiKey);
            DB db = DB.Create(credential);

            string sql = BuildAppointmentQuery(ApptID, DateTime.MinValue, DateTime.MinValue, null, null);
            DBRetVal result = db.FillDataTable(sql);
            if (result.IsValid)
            {
                DataRow row = result.GetDataTableFirstRow();
                AppointmentDetail1 detail = new AppointmentDetail1();

                // Appointment Stuff
                detail.ApptNote = DataRet.Retrieve(row["Appointment Note"]);
                detail.ApptReason = DataRet.Retrieve(row["Reason Description"]);

                // Chart Info
                bool bHasChart = (DataRet.Retrieve(row["Chart Number"]) != "");
                if (bHasChart)
                    detail.PatientDetail = GetPatientDetail(SystemApiKey, row, String.Empty, bCalculateBalance);

                // Return the Appointment Detail Item 
                return detail;
            }

            return null;
        }

        /// <summary>
        /// Similar to GetAppointmentDetail, except all we have here is only the chart, so no appointment information
        /// </summary>
        /// <param name="SystemApiKey"></param>
        /// <param name="UseThisRowInsteadOfQuery">if this is set to not null (a valid DBRetVal), it will use the DBRetVal and not re-query</param>
        /// <param name="ApptID"></param>
        /// <returns></returns>
        public static PatientDetail1 GetPatientDetail(string SystemApiKey, DataRow UseThisRowInsteadOfQuery, string PatientChart, bool bCalculateBalance)
        {
            SQLServerCredential credential = DBCache.GetSQLServerConnection(SystemApiKey);
            DB db = DB.Create(credential);

            DataRow row = null;
            // Query only if the row has not been passed in
            if (UseThisRowInsteadOfQuery == null)
            {
                string sql = BuildPatientQuery(PatientChart);
                DBRetVal result = db.FillDataTable(sql);
                if (result.IsValid)
                    row = result.GetDataTableFirstRow();
            }
            else
            {
                row = UseThisRowInsteadOfQuery;
            }

            // Fill in the information            
            if (row != null)
            {
                PatientDetail1 detail = new PatientDetail1();

                // Chart Info                
                detail.Patient = FillInPatientInformation(row, true, true);

                // Patient Billing Information
                detail.PatientBilling = new PatientBillingInfo1();

                detail.PatientBilling.CopayAmount = DataRet.Retrieve<decimal>(row["Patient Copay"], 0);
                detail.PatientBilling.PrimaryInsCarrier = DataRet.Retrieve(row["Primary Insurance Name"]);

                // Refering Provider comes from Patient Table also                    
                detail.PatientBilling.RefProvider = DataRet.Retrieve(row["Ref Physician Name"]);

                // Fill in Refering Provider Phone 
                string Phone = DataRet.Retrieve(row["Ref Physician Phone"]);
                if (!String.IsNullOrEmpty(Phone) && Phone != "(   )    -")
                {
                    detail.PatientBilling.RefProviderPhone = Phone;
                }
                else
                {
                    Phone = DataRet.Retrieve(row["Ref Physician Main Phone"]);
                    if (!String.IsNullOrEmpty(Phone) && Phone != "(   )    -")
                    {
                        detail.PatientBilling.RefProviderPhone = Phone;
                    }
                    else
                    {
                        Phone = DataRet.Retrieve(row["Ref Physician Mobile Phone"]);
                        if (!String.IsNullOrEmpty(Phone) && Phone != "(   )    -")
                        {
                            detail.PatientBilling.RefProviderPhone = Phone;
                        }
                    }
                }

                // Calculate Account Balance (May take awhile)
                if (bCalculateBalance)
                {
                    detail.PatientBilling.AccountBalance = CalculatePatientBalance(detail.Patient.ChartId, SystemApiKey);
                }

                // Return the Appointment Detail Item 
                return detail;
            }

            return null;
        }

        /// <summary>
        /// Used to calculate the Patient Balance (stripped out of Lytec)
        /// </summary>
        /// <param name="Chart"></param>
        /// <returns></returns>
        private static decimal CalculatePatientBalance(string Chart, string SystemApiKey)
        {
            if (!String.IsNullOrEmpty(Chart))
            {
                // Copied from Lytec Source Code (CalculateBalancesThread.cpp's GetPatientInsuranceBalance())
                StringBuilder sb = new StringBuilder();
                sb.Append("SELECT\r\n");
                sb.Append("  SUM ([Billing Totals].[Pat Portion]) AS [Pat Portion]\r\n");
                sb.Append("  , SUM ([Billing Totals].[Prim Ins Portion]) AS [Prim Ins Portion]\r\n");
                sb.Append("  , SUM ([Billing Totals].[Sec Ins Portion]) AS [Sec Ins Portion]\r\n");
                sb.Append("  , SUM ([Billing Totals].[Tert Ins Portion]) AS [Tert Ins Portion]\r\n");
                sb.Append("FROM\r\n");
                sb.Append("  (\r\n");
                sb.Append("  SELECT\r\n");
                sb.Append("    [dbo].[view_DetailPortionsSplit].[Billing]\r\n");
                sb.Append("    , MIN ([dbo].[view_DetailPortionsSplit].[Chart]) AS CHART\r\n");
                sb.Append("    , SUM ([dbo].[view_DetailPortionsSplit].[Current Patient Portion]) + MIN (CASE WHEN [dbo].[view_DetailPortionsSplit].[Which Insurance Is Responsible] >= 0 THEN [dbo].[view_HeaderCopay].[Adjusted CoPay] ELSE 0.0 END) AS [Pat Portion]\r\n");
                sb.Append("    , SUM (CASE WHEN [dbo].[view_DetailPortionsSplit].[Which Insurance Is Responsible] = 0 THEN [dbo].[view_DetailPortionsSplit].[Insurance Portion] ELSE 0.0 END) - MIN (CASE WHEN [dbo].[view_DetailPortionsSplit].[Which Insurance Is Responsible] = 0 THEN [dbo].[view_HeaderCopay].[Adjusted CoPay] ELSE 0.0 END) AS [Prim Ins Portion]\r\n");
                sb.Append("    , SUM (CASE WHEN [dbo].[view_DetailPortionsSplit].[Which Insurance Is Responsible] = 1 THEN [dbo].[view_DetailPortionsSplit].[Insurance Portion] ELSE 0.0 END) - MIN (CASE WHEN [dbo].[view_DetailPortionsSplit].[Which Insurance Is Responsible] = 1 THEN [dbo].[view_HeaderCopay].[Adjusted CoPay] ELSE 0.0 END) AS [Sec Ins Portion]\r\n");
                sb.Append("    , SUM (CASE WHEN [dbo].[view_DetailPortionsSplit].[Which Insurance Is Responsible] = 2 THEN [dbo].[view_DetailPortionsSplit].[Insurance Portion] ELSE 0.0 END) - MIN (CASE WHEN [dbo].[view_DetailPortionsSplit].[Which Insurance Is Responsible] = 2 THEN [dbo].[view_HeaderCopay].[Adjusted CoPay] ELSE 0.0 END) AS [Tert Ins Portion]\r\n");
                sb.Append("  FROM\r\n");
                sb.Append("    [dbo].[view_DetailPortionsSplit] INNER JOIN [dbo].[view_HeaderCopay]\r\n");
                sb.Append("      ON ([dbo].[view_DetailPortionsSplit].[Billing] = [dbo].[view_HeaderCopay].[Billing])\r\n");
                sb.Append("  WHERE\r\n");
                sb.Append(String.Format("    [dbo].[view_DetailPortionsSplit].[Chart] = '{0}' \r\n", MAKE_SURE_APOSTROPHY_IS_SQL_READY(Chart)));
                sb.Append("  GROUP BY\r\n");
                sb.Append("    [dbo].[view_DetailPortionsSplit].[Billing]\r\n");
                sb.Append("  ) AS [Billing Totals]\r\n");
                string sql = sb.ToString();

                SQLServerCredential credential = DBCache.GetSQLServerConnection(SystemApiKey);
                DB db = DB.Create(credential);

                DBRetVal result = db.FillDataTable(sql);
                if (result.IsValid)
                {
                    DataRow row = result.GetDataTableFirstRow();
                    decimal PatientBalance = DataRet.Retrieve<decimal>(row[0], 0);
                    decimal PrimaryInsBalance = DataRet.Retrieve<decimal>(row[1], 0);
                    decimal SecondaryInsBalance = DataRet.Retrieve<decimal>(row[2], 0);
                    decimal TertiaryInsBalance = DataRet.Retrieve<decimal>(row[3], 0);

                    // We only return Patient Balance for now
                    return PatientBalance;
                }
            }
            return 0;
        }

        #endregion

        #region Post Billing

        /// <summary>
        /// Post Billing to Pending Transactions
        /// </summary>
        /// <param name="SystemApiKey"></param>
        /// <param name="User"></param>
        /// <param name="billingInfo"></param>
        /// <returns></returns>
        public static bool PostBilling(string SystemApiKey, string User, BillingPost1 billingInfo)
        {            
            // Make sure this is a valid BillingPost we are getting passed in
            if ((billingInfo == null) || (billingInfo.BillingDetails == null) ||
                (billingInfo.BillingDetails.Length == 0))
                return false;
            
            // Generate new Unique ID
            string UniqueID = Guid.NewGuid().ToString();
            UniqueID = UniqueID.Replace("-", "").Substring(0, 32);

            // Create encryption code.
            string sSource = billingInfo.ChartID + billingInfo.PatientLastName + UniqueID;
            string s64Trimmed = CrossProduct.Core.Utils.MD5EncryptMsg(sSource, 20);
            bool bSuccessfulPost = true;

            // Setup DB
            SQLServerCredential credential = DBCache.GetSQLServerConnection(SystemApiKey);
            DB db = DB.Create(credential);

            for (int i = 0; i < billingInfo.BillingDetails.Length && bSuccessfulPost; ++i)
            {
                BillingDetail1 detail = billingInfo.BillingDetails[i];
                
                // billing notes have their own procedure code
                bool bIsNote = !String.IsNullOrEmpty(detail.BillingNote);
                
                StringBuilder ValueList = new StringBuilder();
                ValueList.Append("NEWID(), ");                                                                      // ID
                ValueList.Append("0, ");                                                                            // Post, tinyint
                ValueList.Append("0, ");                                                                            // Transaction status, int
                ValueList.Append("'" + MAKE_SURE_APOSTROPHY_IS_SQL_READY(billingInfo.ChartID) + "', ");	            // varchar15
                ValueList.Append("0, ");                                                                            // Billing, int
                ValueList.Append("'" + MAKE_SURE_APOSTROPHY_IS_SQL_READY(billingInfo.ProviderID) + "', ");	        // varchar 15

                // Make sure valid Date From
                if (billingInfo.DateFrom != null && billingInfo.DateFrom != DateTime.MinValue)
                    ValueList.Append("'" + CrossProduct.Core.Utils.SqlDTString(billingInfo.DateFrom) + "', ");      // datetime
                else
                    ValueList.Append("'" + CrossProduct.Core.Utils.SqlDTString(DateTime.Now) + "', ");              // datetime

                // If it is a Note type (BillingNote is set) don't attach anything but MOBIL0000 as Procedure Code
                if (bIsNote)
                {
                    ValueList.Append("'MOBIL0000', ");	                                                            // varchar 15
                    ValueList.Append("'', ");	                                                                    // varchar 3
                    ValueList.Append("'', ");                                                                       // varchar 3
                    ValueList.Append("'', ");                                                                       // varchar 3
                    ValueList.Append("'', ");                                                                       // varchar 3
                    ValueList.Append("'" + MAKE_SURE_APOSTROPHY_IS_SQL_READY(detail.BillingNote) + "', ");          // Lytec Billing Note!
                }
                else
                {
                    ValueList.Append("'" + MAKE_SURE_APOSTROPHY_IS_SQL_READY(detail.ProcedureCode) + "', ");	    // varchar 15
                    ValueList.Append("'" + MAKE_SURE_APOSTROPHY_IS_SQL_READY(detail.ProcedureModifier1) + "', ");	// varchar 3
                    ValueList.Append("'" + MAKE_SURE_APOSTROPHY_IS_SQL_READY(detail.ProcedureModifier2) + "', ");   // varchar 3
                    ValueList.Append("'" + MAKE_SURE_APOSTROPHY_IS_SQL_READY(detail.ProcedureModifier3) + "', ");   // varchar 3
                    ValueList.Append("'" + MAKE_SURE_APOSTROPHY_IS_SQL_READY(detail.ProcedureModifier4) + "', ");   // varchar 3
                    ValueList.Append("'', ");                                                                       // No Note
                }

                ValueList.Append(detail.Units + ", ");			                                                    // real
                ValueList.Append((i + 1).ToString() + ", ");                                                        // Procedure Order, int
                ValueList.Append("'" + MAKE_SURE_APOSTROPHY_IS_SQL_READY(billingInfo.PatientLastName) + "', ");     // patient last name
                ValueList.Append("'" + MAKE_SURE_APOSTROPHY_IS_SQL_READY(billingInfo.PatientFirstName) + "', ");    // patient first name

                // Don't insert an invalid birthday (Lytec allows nulls)                
                if (billingInfo.BirthDate != null && billingInfo.BirthDate != DateTime.MinValue)
                    ValueList.Append("'" + CrossProduct.Core.Utils.SqlDTString(billingInfo.BirthDate) + "', ");      // datetime
                else
                    ValueList.Append(" NULL, "); // datetime

                ValueList.Append("'" + MAKE_SURE_APOSTROPHY_IS_SQL_READY(billingInfo.PatientSocialSecurity) + "', ");// varchar 11
                ValueList.Append("0, ");                                                                            // Exception	// int
                ValueList.Append("'" + MAKE_SURE_APOSTROPHY_IS_SQL_READY(billingInfo.Facility) + "', ");	        // Facility
                ValueList.Append("0, ");                                                                            // Created By Application.	// int
                ValueList.Append("'" + UniqueID + "', ");               	                                        // varchar 32
                ValueList.Append("'', ");                                                                           // Error msg.	varchar 80
                ValueList.Append("'" + CrossProduct.Core.Utils.SqlDTString(DateTime.Now) + "', ");                  // Created Date, datetime            
                ValueList.Append("NULL, ");                                                                         // mod date, datetime
                ValueList.Append("0,");                                                                             // Viewed, tinyint            
                ValueList.Append("'" + MAKE_SURE_APOSTROPHY_IS_SQL_READY(s64Trimmed) + "',");                       // Encryption Code.            

                /// *******
                /// Attach each Diagnosis Code (we can do up to 12) for each procedure
                /// Always do this last, deals with trailing Comma (,)
                /// *******
                for (int j = 0; j < 12; ++j)
                {
                    try
                    {
                        ValueList.Append("'");
                        
                        // We only add Diagnosis codes, if this is not a billing note
                        if(!bIsNote)
                           ValueList.Append(MAKE_SURE_APOSTROPHY_IS_SQL_READY(detail.DiagnosisCodes[j]));    // Diagnosis 1 - 12, varchar 15

                        if (j < 11)
                            ValueList.Append("', ");
                        else
                            ValueList.Append("'");
                    }
                    catch (IndexOutOfRangeException)
                    {
                        if (j < 11)
                            ValueList.Append("', ");
                        else
                            ValueList.Append("'");
                    }
                    catch (NullReferenceException)
                    {
                        if (j < 11)
                            ValueList.Append("', ");
                        else
                            ValueList.Append("'");
                    }
                }

                // Build our sql statement.
                StringBuilder sb = new StringBuilder("INSERT INTO [Pending Transaction] ");
                sb.Append(" ([Unique ID], Post, [Transaction Status], [Chart Number], [Billing], [Provider], [Date From], [Transaction Code], [Modifier 1], [Modifier 2], [Modifier 3], [Modifier 4], [Detail Note], [Units], [Procedure Order], [Last Name], [First Name], [Birth Date], [Social Security], [Exception], [Facility], [Created By Application], [HL7 Message ID], [Error Message], [Created Date], [Modified Date], [Viewed], [Checksum], [Diagnosis 1], [Diagnosis 2], [Diagnosis 3], [Diagnosis 4], [Diagnosis 5], [Diagnosis 6], [Diagnosis 7], [Diagnosis 8], [Diagnosis 9], [Diagnosis 10], [Diagnosis 11], [Diagnosis 12] )");
                sb.Append(" VALUES (" + ValueList.ToString() + ")");
                string StrSQL = sb.ToString();

                // Post the Billing
                DBRetVal result = db.ExecuteNonQuery(StrSQL);
                bSuccessfulPost = result.IsValid;
            }

            return bSuccessfulPost;
        }

        #endregion
    }
}