1224 lines
61 KiB
C#
1224 lines
61 KiB
C#
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
|
||
}
|
||
}
|