public static string[] AutoCorrectGet(string userName)
{
using(var db = new PosEntities())
{
var query = from auto in db.AutoCorrects
where auto.UserName.ToLower() == userName.ToLower()
orderby auto.Name
select auto.Name + ": " + auto.Value;
return query.ToArray();
}
}
public static List<SelectListItem> DoctorsGet()
{
using (var db = new PosEntities())
{
var userQuery = from user in db.Users
orderby user.FirstName, user.LastName
select new SelectListItem
{
Text = user.FirstName + " " + user.LastName,
Value = user.UserName
};
return userQuery.ToList();
}
}
public static List<SelectListItem> ExamLookUpGet()
{
using(var db = new PosEntities())
{
var query = from dbExamLookUp in db.ExamLookUps
orderby dbExamLookUp.ControlName
select new SelectListItem
{
Text = dbExamLookUp.ControlName,
Value = dbExamLookUp.FieldName
};
return query.ToList();
}
}
public static void ExamDataSave(int examID, int patientID, NotesModel notes)
{
PropertyInfo[] notesFields = notes.GetType().GetProperties();
Field field;
PropertyInfo pi;
Dictionary<string, string> dicJson;
ExamData data = new ExamData();
using(var db = new PosEntities())
{
var dataConfigurationQuery = from dbConfig in db.ExamDataConfigurations select dbConfig;
foreach(var config in dataConfigurationQuery)
{
dicJson = new Dictionary<string, string>();
if (config.FieldDataType == (int)PosConstants.FieldDataType.Json)
{
string[] examFields = config.Field.Replace(" ", "") .Split(',');
foreach(string examField in examFields)
{
pi = notesFields.First(f => f.Name == examField);
field = (Field) pi.GetValue(notes);
dicJson.Add(field.Name, field.Value);
}
data = new ExamData()
{
PatientID = patientID,
ExamID = examID,
ExamDataConfigurationID = config.ExamDataConfigurationID,
FieldName = config.Name,
FieldValue = JsonConvert.SerializeObject(dicJson),
FieldDataType = (int)PosConstants.FieldDataType.Json
};
}
db.ExamDatas.Add(data);
}
//finally saving
db.SaveChanges();
}
}
public static ExamDefaultModel ExamDefaultGet(int examDefaultID)
{
using (var db = new PosEntities())
{
var defaultQuery = from dbDefault in db.ExamDefaults
join user in db.Users on dbDefault.DoctorUserID equals user.UserID
where dbDefault.ExamDefaultID == examDefaultID
select new ExamDefaultModel
{
ExamDefaultID = dbDefault.ExamDefaultID,
DefaultName = dbDefault.DefaultName,
AgeStart = dbDefault.AgeStart,
AgeEnd = dbDefault.AgeEnd,
PrematureBirth = dbDefault.PrematureBirth,
DoctorUserID = dbDefault.DoctorUserID,
ExamText = dbDefault.ExamText,
DoctorName = user.FirstName + " " + user.LastName,
DoctorUserName = user.UserName
};
return defaultQuery.First();
}
}
public static bool AddLookUpModel(LookUpModel lookUpModel)
{
bool isSaved = false ;
try
{
using (var db = new PosEntities())
{
if (lookUpModel != null)
{
db.LookUps.Add(new LookUp { FieldDescription = lookUpModel.FieldDescription, FieldName = lookUpModel.FieldName, FieldValue = lookUpModel.FieldValue, LookupID = lookUpModel.LookUpID, SortOrder = lookUpModel.SortOrder });
var result = db.SaveChanges();
isSaved = result > 0 ? true : false ;
}
}
}
catch (Exception)
{
isSaved = false;
//throw;
}
return isSaved;
}
public static bool UpdateLookUpModel(LookUpModel lookUpModel)
{
bool isSaved = false;
try
{
using (var db = new PosEntities())
{
var lookup = db.LookUps.FirstOrDefault(l => l.LookupID == lookUpModel.LookUpID);
if (lookUpModel != null)
{
lookup.FieldDescription = lookUpModel.FieldDescription;
lookup.FieldName = lookUpModel.FieldName;
lookup.FieldValue = lookUpModel.FieldValue;
lookup.SortOrder = lookUpModel.SortOrder;
var result = db.SaveChanges();
isSaved = result > 0 ? true : false;
}
}
}
catch (Exception)
{
isSaved = false;
//throw;
}
return isSaved;
}
public static bool RemoveLookUpModel(int lookUpID)
{
bool isSaved = false;
try
{
using (var db = new PosEntities())
{
var lookUpModel = db.LookUps.FirstOrDefault(l => l.LookupID == lookUpID);
if (lookUpModel != null)
{
db.LookUps.Remove(lookUpModel);
var result = db.SaveChanges();
isSaved = result > 0 ? true : false;
}
}
}
catch (Exception)
{
isSaved = false;
//throw;
}
return isSaved;
}
public static void UpdateLastVisitDate()
{
using(var db = new PosEntities())
{
string sql = @"UPDATE p
SET
p.LastExamDate = e.MaxExamDate
FROM Patient p
INNER JOIN (select e1.PatientID, MaxExamDate = Max(e1.ExamDate) from Exam e1 group by e1.PatientID
)As e ON p.PatientID = e.PatientID
";
db.Database.ExecuteSqlCommand(sql);
}
}
public static bool PatientSave(PatientModel patient)
{
if(PosRepository.PatientExists(patient))
{
throw new ApplicationException(PosMessage.PatientNumberExists);
}
using (var db = new PosEntities())
{
var dbPatient = (from dbPat in db.Patients where dbPat.PatientID == patient.PatientID select dbPat).FirstOrDefault();
if (dbPatient == null)
{
//checking for unique patient number
dbPatient = new Patient();
db.Patients.Add(dbPatient);
}
Mapper.Map<PatientModel, Patient>(patient, dbPatient);
int result = db.SaveChanges();
if (result > 0)
return true;
else
return false;
}
}
public static ProfileModel ProfileGet(LoginModel login)
{
using(var db = new PosEntities())
{
var query = from dbUser in db.Users
where dbUser.UserName == login.UserName && dbUser.Password == login.UserPassword
select new ProfileModel
{
UserID = dbUser.UserID,
FirstName = dbUser.FirstName,
LastName = dbUser.LastName,
UserName = dbUser.UserName,
PhotoUrl = dbUser.PhotoUrl
};
return query.FirstOrDefault();
}
}
public static bool UserSave(UserModel user)
{
using (var db = new PosEntities())
{
var dbUser = (from dbusr in db.Users where dbusr.UserID == user.UserID select dbusr).FirstOrDefault();
if (dbUser == null)
{
dbUser = new User();
db.Users.Add(dbUser);
}
Mapper.Map<UserModel, User>(user, dbUser);
int result = db.SaveChanges();
if (result > 0)
return true;
else
return false;
}
}
public static void PatientDeleteExamData(int patientID)
{
using (var db = new PosEntities())
{
db.Database.ExecuteSqlCommand("DELETE FROM ExamData WHERE PatientID = " + patientID.ToString());
}
}
public static List<SelectListItem> PrintQueueDoctorsGet()
{
using (var db = new PosEntities())
{
var userQuery = from user in db.Users
join queue in db.PrintQueues on user.UserName equals queue.UserName
orderby user.FirstName, user.LastName
select new SelectListItem
{
Text = user.FirstName + " " + user.LastName,
Value = user.UserName
};
return userQuery.Distinct().ToList();
}
}
public static PatientModel PatientGet(int patientID, bool includeHistory)
{
using (var db = new PosEntities())
{
var patientQuery = from dbPatient in db.Patients
where dbPatient.PatientID == patientID
select dbPatient;
PatientModel patientModel = Mapper.Map<Patient, PatientModel>(patientQuery.FirstOrDefault());
if (includeHistory)
{
var historyQuery = from dbExam in db.Exams
where dbExam.PatientID == patientID
orderby dbExam.ExamDate descending, dbExam.ExamID descending
select new PatientHistoryModel
{
ExamID = dbExam.ExamID,
ExamDate = dbExam.ExamDate,
ExamCorrectDate = dbExam.ExamCorrectDate,
CorrectExamID = dbExam.CorrectExamID,
SavedInd = dbExam.SavedInd,
LastUpdatedDate = dbExam.LastUpdatedDate
};
patientModel.History = historyQuery.ToList();
}
return patientModel;
}
}
public static bool PatientExists(PatientModel patient)
{
using (var db = new PosEntities())
{
var dbPatient = (from dbpnt in db.Patients where dbpnt.PatientNumber == patient.PatientNumber && dbpnt.PatientID != patient.PatientID select dbpnt.PatientNumber).FirstOrDefault();
if (dbPatient == null)
return false;
else
return true;
}
}
public static void PrintQueueAdd(PrintQueueItem item)
{
using(var db = new PosEntities())
{
PrintQueue queue = new PrintQueue() { ExamID = item.ExamID, UserName = item.UserName, PrintExamNote = item.PrintExamNote };
db.PrintQueues.Add(queue);
db.SaveChanges();
}
}
public static string ExamDefaultNotesText(string userName, int patientAge, bool prematureBirth)
{
using(var db = new PosEntities())
{
var defaultQuery = from dbDefault in db.ExamDefaults
join dbUser in db.Users on dbDefault.DoctorUserID equals dbUser.UserID
where dbUser.UserName.ToLower() == userName.ToLower()
&& dbDefault.PrematureBirth == prematureBirth
&& dbDefault.AgeStart <= patientAge
&& dbDefault.AgeEnd >= patientAge
select dbDefault.ExamText;
return defaultQuery.FirstOrDefault();
}
}
public static List<int> PatientGetAllIds(int patientUpdateCount)
{
using (var db = new PosEntities())
{
var query = from dbPatient in db.Patients
orderby dbPatient.LastExamDate descending
select dbPatient.PatientID;
if (patientUpdateCount == 0)
return query.ToList();
else
return query.Take(patientUpdateCount).ToList();
}
}
public static List<SearchResultModel> PatientSearch(string criteria)
{
string patientNumber = criteria;
string firstName = criteria;
string lastName = criteria;
if (criteria.IndexOf(' ') > -1)
{
var arr = criteria.Split(' ');
firstName = arr[0];
lastName = arr[1];
using (var db = new PosEntities())
{
var query = from dbPatient in db.Patients
where dbPatient.FirstName.StartsWith(firstName)
&& dbPatient.LastName.StartsWith(lastName)
orderby dbPatient.LastExamDate descending
select new SearchResultModel
{
ID = dbPatient.PatientID,
PatientNumber = dbPatient.PatientNumber,
PatientName = dbPatient.FirstName + " " + dbPatient.LastName,
DateOfBirth = dbPatient.DateOfBirth,
LastExamDate = dbPatient.LastExamDate
};
return query.Take(20).ToList();
}
}
else
{
using (var db = new PosEntities())
{
var query = from dbPatient in db.Patients
where dbPatient.PatientNumber.StartsWith(patientNumber)
|| dbPatient.FirstName.StartsWith(firstName)
|| dbPatient.LastName.StartsWith(lastName)
orderby dbPatient.LastExamDate descending
select new SearchResultModel
{
ID = dbPatient.PatientID,
PatientNumber = dbPatient.PatientNumber,
PatientName = dbPatient.FirstName + " " + dbPatient.LastName,
DateOfBirth = dbPatient.DateOfBirth,
LastExamDate = dbPatient.LastExamDate
};
return query.Take(20).ToList();
}
}
}
public static bool UserExists(UserModel user)
{
using(var db = new PosEntities())
{
var dbUser = (from dbusr in db.Users where dbusr.UserName.ToLower() == user.UserName.ToLower() && dbusr.UserID != user.UserID select dbusr.UserName).FirstOrDefault();
if (dbUser == null)
return false;
else
return true;
}
}
public static List<ExamHistoryDataModel> GetNotesHistory(int patientID)
{
using (var db = new PosEntities())
{
var query = from data in db.ExamDatas
join exam in db.Exams on data.ExamID equals exam.ExamID
where data.PatientID == patientID
&& data.FieldDataType == (int)PosConstants.FieldDataType.Json
orderby data.FieldName, exam.ExamDate descending, exam.ExamID descending
select new ExamHistoryDataModel
{
ExamID = data.ExamID,
ExamDate = exam.ExamDate,
CorrectExamID = exam.CorrectExamID,
ExamCorrectDate = exam.ExamCorrectDate,
FieldName = data.FieldName,
FieldValue = data.FieldValue
};
var history = query.ToList();
foreach (var item in history)
{
item.FieldValue = JsonConvert.DeserializeObject(item.FieldValue);
}
return history;
}
}
public static UserModel UserGet(int userID)
{
using (var db = new PosEntities())
{
var userQuery = from dbUser in db.Users
where dbUser.UserID == userID
select dbUser;
UserModel userModel = Mapper.Map<User, UserModel>(userQuery.FirstOrDefault());
var defaultQuery = from dbDefault in db.ExamDefaults
where dbDefault.DoctorUserID == userID
orderby dbDefault.DefaultName ascending
select new ExamDefaultModel
{
ExamDefaultID = dbDefault.ExamDefaultID,
DefaultName = dbDefault.DefaultName,
AgeStart = dbDefault.AgeStart,
AgeEnd = dbDefault.AgeEnd,
PrematureBirth = dbDefault.PrematureBirth,
DoctorUserID = dbDefault.DoctorUserID,
ExamText = ""
};
userModel.Defaults = defaultQuery.ToList();
return userModel;
}
}
public static void PrintQueueRemove(PrintQueueItem item)
{
using (var db = new PosEntities())
{
var queueItem = (from queue in db.PrintQueues where queue.PrintQueueID == item.PrintQueueID select queue).First();
db.PrintQueues.Remove(queueItem);
db.SaveChanges();
}
}
public static List<SearchResultModel> UserSearch(string criteria)
{
string firstName = criteria;
string lastName = criteria;
string userName = criteria;
if (criteria.IndexOf(' ') > -1)
{
var arr = criteria.Split(' ');
firstName = arr[0];
lastName = arr[1];
using (var db = new PosEntities())
{
var query = from dbUser in db.Users
where dbUser.FirstName.StartsWith(firstName)
&& dbUser.LastName.StartsWith(lastName)
orderby dbUser.FirstName ascending
select new SearchResultModel
{
ID = dbUser.UserID,
FullName = dbUser.FirstName + " " + dbUser.LastName,
UserName = dbUser.UserName,
PhotoUrl = dbUser.PhotoUrl
};
return query.Take(20).ToList();
}
}
else
{
using (var db = new PosEntities())
{
var query = from dbUser in db.Users
where dbUser.UserName.StartsWith(userName)
|| dbUser.FirstName.StartsWith(firstName)
|| dbUser.LastName.StartsWith(lastName)
orderby dbUser.FirstName ascending
select new SearchResultModel
{
ID = dbUser.UserID,
FullName = dbUser.FirstName + " " + dbUser.LastName,
UserName = dbUser.UserName,
PhotoUrl = dbUser.PhotoUrl
};
return query.Take(20).ToList();
}
}
}
public static void PrintQueueRemove(int examID)
{
using(var db = new PosEntities())
{
var queueItems = (from queue in db.PrintQueues where queue.ExamID == examID select queue).ToList();
foreach(var item in queueItems)
{
db.PrintQueues.Remove(item);
}
db.SaveChanges();
}
}
public static PrintQueueModel PrintQueueGet()
{
//first populating the users
using(var db = new PosEntities())
{
PrintQueueModel model = new PrintQueueModel();
model.Doctors = PrintQueueDoctorsGet();
var itemQuery = from queue in db.PrintQueues
join exam in db.Exams on queue.ExamID equals exam.ExamID
join patient in db.Patients on exam.PatientID equals patient.PatientID
join user in db.Users on queue.UserName equals user.UserName
orderby exam.LastUpdatedDate
select new PrintQueueItem
{
PatientName = patient.FirstName + " " + patient.LastName,
ExamDate = exam.ExamDate,
ExamID = exam.ExamID,
PrintQueueID = queue.PrintQueueID,
PrintExamNote = queue.PrintExamNote,
CorrectExamID = exam.CorrectExamID,
ExamCorrectDate = exam.ExamCorrectDate,
DoctorName = user.FirstName + " " + user.LastName,
LastUpdatedDate = exam.LastUpdatedDate,
UserName = queue.UserName
};
model.Items = itemQuery.ToList();
return model;
}
}
public static List<ExamModel> PatientGetExams(int patientID)
{
using (var db = new PosEntities())
{
var examQuery = from exam in db.Exams
where exam.PatientID == patientID
select new ExamModel
{
ExamID = exam.ExamID,
ExamText = exam.ExamText,
ExamDate = exam.ExamDate,
SaveInd = exam.SavedInd.Value,
CorrectExamID = exam.CorrectExamID,
ExamCorrectDate = exam.ExamCorrectDate,
LastUpdatedDate = exam.LastUpdatedDate.Value
};
return examQuery.ToList();
}
}
public static List<LookUpModel> LookUpGet()
{
using(var db = new PosEntities())
{
var query = from dbLookup in db.LookUps
orderby dbLookup.FieldName, dbLookup.SortOrder, dbLookup.LookupID
select new LookUpModel
{
LookUpID = dbLookup.LookupID,
FieldName = dbLookup.FieldName,
FieldValue = dbLookup.FieldValue,
FieldDescription = dbLookup.FieldDescription,
SortOrder = dbLookup.SortOrder
};
return query.ToList();
}
}
public static void ExamSave(ExamModel exam)
{
using (var db = new PosEntities())
{
Exam dbExam;
if (exam.ExamID > 0)
{
dbExam = (from e in db.Exams where e.ExamID == exam.ExamID select e).First();
}
else
{
dbExam = new Exam();
db.Exams.Add(dbExam);
}
dbExam.ExamText = exam.ExamText;
dbExam.ExamDate = exam.ExamDate;
dbExam.PatientID = exam.PatientID;
dbExam.UserName = exam.UserName;
dbExam.SavedInd = exam.SaveInd;
dbExam.LastUpdatedDate = DateTime.Now;
dbExam.ExamCorrectDate = exam.ExamCorrectDate;
dbExam.CorrectExamID = exam.CorrectExamID;
var patient = (from pat in db.Patients where pat.PatientID == exam.PatientID select pat).First();
if (!patient.LastExamDate.HasValue || patient.LastExamDate < exam.ExamDate)
{
patient.LastExamDate = exam.ExamDate;
}
db.SaveChanges();
exam.ExamID = dbExam.ExamID;
}
}